# JSDataSource

## Overview

The `JSDataSource` represents a data source in the Servoy environment, encompassing both database sources (`datasources.db.myserver.mytable`) and in-memory sources (`datasources.mem['dsname']`). It provides a comprehensive API for interacting with data, enabling querying, record manipulation, and foundset management.

This class supports creating new foundsets for a data source or retrieving named foundsets based on specific configurations. It allows the execution of queries through query builders (`QBSelect`) or raw SQL strings. Records can be loaded dynamically using various input formats, including primary keys, datasets, or queries. The ability to retrieve individual records by primary key ensures precise and efficient data access when needed.

In addition to record management, `JSDataSource` facilitates retrieving metadata such as column names and table information for a data source. It supports the creation of query builders, optionally with table aliases, allowing complex query construction and interaction with outer tables in nested SQL scenarios.

## Methods Summarized

| Type                                                                                           | Name                                                 | Summary                                                                             |
| ---------------------------------------------------------------------------------------------- | ---------------------------------------------------- | ----------------------------------------------------------------------------------- |
| [QBSelect](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbselect)     | [createSelect()](#createselect)                      | Create a query builder for a data source.                                           |
| [QBSelect](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbselect)     | [createSelect(tableAlias)](#createselect-tablealias) | Create a query builder for a data source with given table alias.                    |
| [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array)                     | [getColumnNames()](#getcolumnnames)                  | Get the column names of a datasource.                                               |
| [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string)                   | [getDataSource()](#getdatasource)                    | Get the datasource string.                                                          |
| [JSFoundSet](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jsfoundset) | [getFoundSet()](#getfoundset)                        | Returns a foundset object for a specified datasource or server and tablename.       |
| [JSFoundSet](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jsfoundset) | [getFoundSet(select)](#getfoundset-select)           | Returns a foundset object for a specified pk base query.                            |
| [JSFoundSet](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jsfoundset) | [getFoundSet(name)](#getfoundset-name)               | An existing foundset under that name will be returned, or created.                  |
| [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array)                     | [getLoadedFoundSets()](#getloadedfoundsets)          | Get all currently foundsets for this datasource.                                    |
| [JSRecord](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jsrecord)     | [getRecord(pk)](#getrecord-pk)                       | Get a single record from a datasource.                                              |
| [JSTable](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jstable)       | [getTable()](#gettable)                              | Get the table of a datasource.                                                      |
| [JSFoundSet](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jsfoundset) | [loadRecords(dataSet)](#loadrecords-dataset)         | get a new foundset containing records based on a dataset of pks.                    |
| [JSFoundSet](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jsfoundset) | [loadRecords(qbSelect)](#loadrecords-qbselect)       | get a new foundset containing records based on a QBSelect query that is given.      |
| [JSFoundSet](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jsfoundset) | [loadRecords(query)](#loadrecords-query)             | get a new foundset containing records based on an SQL query string.                 |
| [JSFoundSet](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jsfoundset) | [loadRecords(query, args)](#loadrecords-query-args)  | get a new foundset containing records based on an SQL query string with parameters. |

## Methods Detailed

### createSelect()

Create a query builder for a data source.

**Returns:** [QBSelect](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbselect) query builder

**Sample**

```js
var q = datasources.db.example_data.book_nodes.createSelect()
 q.result.addPk()
 q.where.add(q.columns.label_text.not.isin(null))
 datasources.db.example_data.book_nodes.getFoundSet().loadRecords(q)
```

### createSelect(tableAlias)

Create a query builder for a data source with given table alias.\
 The alias can be used inside custom queries to bind to the outer table.

**Parameters**

* [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **tableAlias** the table alias to use

**Returns:** [QBSelect](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbselect) query builder

**Sample**

```js
var q = datasources.db.example_data.book_nodes.createSelect('b')
 q.result.addPk()
 q.where.add(q.columns.label_text.isin('select comment_text from book_text t where t.note_text = ? and t.node_id = b.node_id', ['test']))
 datasources.db.example_data.book_nodes.getFoundSet().loadRecords(q)
```

### getColumnNames()

Get the column names of a datasource.

**Returns:** [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array) String\[] column names

### getDataSource()

Get the datasource string.

**Returns:** [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) String datasource

**Sample**

```js
datasources.db.example_data.orders.getDataSource() // returns 'db:/example_data/orders'
```

### getFoundSet()

Returns a foundset object for a specified datasource or server and tablename.\
It is important to note that this is a FACTORY method, it constantly creates new foundsets.

**Returns:** [JSFoundSet](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jsfoundset) A new JSFoundset for the datasource.

**Sample**

```js
var fs = datasources.db.example_data.orders.getFoundSet()
var ridx = fs.newRecord()
var record = fs.getRecord(ridx)
record.emp_name = 'John'
databaseManager.saveData()
```

### getFoundSet(select)

```
Returns a foundset object for a specified pk base query. This creates a filtered "view" on top of the database table based on that query.

This foundset is different then when doing foundset.loadRecords(query) or datasources.db.server.table.loadRecords(query) because this is generated as a "view"
Which means that the foundset will always have this query as its  base, even when doing foundset.loadAllRecords() afterwards. Because this query is set as its "creation query"
JSFoundset.loadRecords(query) does set that query on the current foundset as a a "search" condition. which will be removed when doing a loadAllRecords().

 So doing a clear() on a foundse created by this call will just add a "search" condition that results in no records found ( 1 = 2) and then loadAllRecords() will go back to this query.
 But in a foundset.loadRecord(query) then clear() will overwrite the "search" condition which is the given query so the query will be lost after that so loadAllRecords() will go back to all records in the table)
```

**Parameters**

* [QBSelect](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbselect) **select** The query to get the JSFoundset for.

**Returns:** [JSFoundSet](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jsfoundset) A new JSFoundset with that query as its base query.

**Sample**

```js
var qb = datasources.db.example_data.orders.createSelect();
qb.result.addPk();
qb.where.add(qb.columns.product_id.eq(1))
foundset.loadRecords(qb);
```

### getFoundSet(name)

An existing foundset under that name will be returned, or created.\
If there is a definition (there is a form with a named foundset property with that name), the initial sort from that form will be used.\
If named foundset datasource does not match current datasource will not be returned (will return null instead).

**Parameters**

* [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **name** The named foundset to get for this datasource.

**Returns:** [JSFoundSet](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jsfoundset) An existing named foundset for the datasource.

**Sample**

```js
var fs = datasources.db.example_data.orders.getFoundSet('myname')
var ridx = fs.newRecord()
var record = fs.getRecord(ridx)
record.emp_name = 'John'
databaseManager.saveData()
```

### getLoadedFoundSets()

Get all currently foundsets for this datasource.\
\</br> This method can be used to loop over foundset and programatically dispose them to clean up resources quickly.

**Returns:** [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array) An array of foundsets loaded for this datasource.

**Sample**

```js
var fslist = datasources.db.example_data.orders.getLoadedFoundSets()
fslist.forEach(function(fs) {
  if (shouldDispose(fs)) {
		fs.dispose()
  }
})
```

### getRecord(pk)

Get a single record from a datasource.\
For the sake of performance, if more records are needed,\
don't call this method in a loop but try using other methods instead.

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **pk** The primary key of the record to be retrieved. Can be an array, in case of a composite pk.

**Returns:** [JSRecord](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jsrecord) a record

**Sample**

```js
var detailsRecord = datasources.db.example_data.order_details.getRecord([10248, 11])
var orderRecord = datasources.db.example_data.orders.getRecord(10248)
var customerRecord = datasources.db.example_data.customers.getRecord('ANATR')
```

### getTable()

Get the table of a datasource.

**Returns:** [JSTable](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jstable) JSTable table

### loadRecords(dataSet)

get a new foundset containing records based on a dataset of pks.

**Parameters**

* [JSDataSet](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jsdataset) **dataSet** ;

**Returns:** [JSFoundSet](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jsfoundset) a new JSFoundset

**Sample**

```js
var fs = datasources.db.example_data.customers.loadRecords(pkDataSet)
```

### loadRecords(qbSelect)

get a new foundset containing records based on a QBSelect query that is given.

This is just a shotcut for datasources.db.server.table.getFoundset() and then calling loadRecords(qbSelect) on the resulting foundset.\
So it has the same behavior as JSFoundset.loadRecords(qbselect) that is that the given query is set as a "search" condition on the existing query of the foundset.\
This means that if you do loadAllRecords() or calling clear() on it the qbselect conditon will also be removed.\
loadAllRecords() will revert back to the foundsets original query (see #getFoundSet(QBSelect)\
clear() will revert back to the original foundset query and add a "clear" condition to the query ( resulting in 1 = 2)

**Parameters**

* [QBSelect](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbselect) **qbSelect** a query builder object

**Returns:** [JSFoundSet](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jsfoundset) a new JSFoundset

**Sample**

```js
var qb = datasources.db.example_data.orders.createSelect();
qb.result.addPk();
qb.where.add(q.columns.product_id.eq(1))
var fs = datasources.db.example_data.orders.loadRecords(qb);
```

### loadRecords(query)

get a new foundset containing records based on an SQL query string.

**Parameters**

* [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **query** an SQL query

**Returns:** [JSFoundSet](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jsfoundset) a new JSFoundset

**Sample**

```js
var query = "SELECT * FROM public.orders WHERE customerid = 'ROMEY' ORDER BY orderid ASC";
var fs = datasources.db.example_data.orders.loadRecords(query);
```

### loadRecords(query, args)

get a new foundset containing records based on an SQL query string with parameters.

**Parameters**

* [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **query** an SQL query string with parameter placeholders
* [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array) **args** an array of arguments for the query string

**Returns:** [JSFoundSet](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jsfoundset) a new JSFoundset

**Sample**

```js
var query = "SELECT * FROM public.orders WHERE customerid = ? OR customerid = ? order by orderid asc";
var args = ['ROMEY', 'BERGS'];
var fs = datasources.db.example_data.orders.loadRecords(query, args);
```

***