infinity.database¶

The infinity.database module provides various database operation possibilities and lets you work with classic SQL-queries, as well as with functions like find(), get(), setParamValue() and others to enhance your comfort in certain scenarios and unify several interactions. Support for ORM and threadsafe NoSQL is offered. Many database providers are supported, including Advantage Database Server, DB2, DBF, InterBase/Firebird, Microsoft Access MySQL, ODBC, Oracle, PostgreSQL, SAP Sybase Adaptive Server Enterprise, SQL Server and SQLite, while the connection can be customized through the specificOptions property.

Module: infinity.database

database¶

The class for operations on databases in INFINITY.JS.

Example:

infinity.loadModule('infinity.database');

let myDB = new infinity.database();
//setting database-specific options
myDB.specificOptions['Direct'] = true;
myDB.specificOptions['UseUnicode'] = true;
//connecting to the database
myDB.connect(infinity.database.provider.mysql, 'localhost', 3306, 'exampleDB', 'root');

//creating a database table
myDB.create('testTable');

//creating table columns
myDB.index('testTable', 'username', infinity.database.indexType.string, true);
myDB.index('testTable', 'email', infinity.database.indexType.string, false);
myDB.index('testTable', 'modified', infinity.database.indexType.int);

//inserting data (note the different notations)
myDB.insert('testTable', { "username": "TheUserName1", "email": "theusername@email.com", "rights": ["read", "write", "create", "delete"] });
myDB.insert('testTable', { "username": "AnotherUserName2", "email": "someother@email.net", "rights": ["read"] });
myDB.insert('testTable', { username: "aUserName3", email: "yetanother@email.com" });

//reading from the table
myDB.execute('SELECT * FROM testTable');
let result = myDB.result();
console.table(result);

//| (index) | 0  | 1                                                 ...|
//+---------+----+---------------------------------------------------...+
//| 0       | id | json                                              ...|
//| 1       | 1  | {"email":"theusername@email.com","rights":["read",...|
//| 2       | 2  | {"email":"someother@email.net","rights":["read"],"...|
//| 3       | 3  | {"email":"yetanother@email.com","username":"aUserN...|

//searching with infinity.database.find()
let ids = myDB.find('testTable', [['email', 'endswith', 'm']], '', infinity.database.orderDirection.ascending, 0, 0, infinity.database.outputMode.ids);
console.debug(ids);
//[1,3]

//updating data with infinity.database.update()
myDB.update('testTable', { id: 2, username: "aUserName", email: "yetanother@email.com", modified: Math.floor(Date.now() * 0.001) });

//reading two rows
let twoRows = myDB.get('testTable', [1, 2]);
console.debug(twoRows);

//[{"email":"theusername@email.com","id":1,"rights":["read","write","create","delete"],"username":"TheUserName1"},{"email":"someother@email.net","id":2,"modified":1613741246,"username":"Somenickname"}]

Properties

autoCommit¶

Type: boolean

Turns on or off auto-committing database modifications.

connectionLifetime¶

Type: number

Specifies how long a database-connection lives before it is killed and recreated.

fieldCount¶

Type: number

Gets the number of columns in the current row.

lastInsertId¶

Type: number

Gets the value of the ID field after an executed INSERT statement.

maxPoolSize¶

Type: number

Gets or sets the maximum number of connections allowed in the pool.

minPoolSize¶

Type: number

Gets or sets the minimum number of connections allowed in the pool.

pooling¶

Type: boolean

Gets or sets pooling mode. Not supported by some databases.

recordCount¶

Type: number

Gets number of rows in the result set.

rowsAffected¶

Type: number

Specifies the number of rows which were inserted, updated, or deleted during the last query operation.

specificOptions¶

Provides extended settings for each database provider. See

• Advantage Database Server

• Amazon Redshift

• DB2

• DBF

• InterBase/Firebird

• Microsoft Access

• MongoDB

• MySQL

• ODBC

• Oracle

• PostgreSQL

• SAP Sybase Adaptive Server Enterprise

• SQLite

• SQL Server

Type: object

Example:

myDB.specificOptions['UseUnicode'] = true;

sql¶

Type: string

Takes a database-query, which then can be executed with execute().

Methods

constructor()¶

Lets you create an INFINITY.JS database object instance. Doesn't accept parameters.

Signature:

constructor()

Example:

infinity.loadModule('infinity.database');
let myDB = new infinity.database();

bof()¶

Returns true, if the cursor is pointing to the beginning of the current result set.

Signature:

bof(): boolean

Return type: boolean

Example:

while( !myDB.bof() ) {
  myDB.prev();
  //...
}

clear()¶

Clears all data from the specified table.

Signature:

clear( table: string ): void

Parameters:

• table: string

  The table to clear data from.

Example:

myDB.clear('testTable');

commit()¶

Commits the previously set up database transaction.

Signature:

commit(): void

Example:

myDB.autoCommit = false;
myDB.startTransaction();
try {
  myDB.execute('INSERT...');
  myDB.execute('UPDATE...');
  myDB.commit();
} catch {
  myDB.rollback();
}

connect()¶

Establishes a connection to the specified database using the provided parameters.

Signature:

connect( provider: infinity.database.provider, server: string, port: number, database: string, username?: string, password?: string ): void

Parameters:

• provider: infinity.database.provider

  The database provider to be used according to the infinity.database.provider enums.

• server: string

  Server address.

• port: number

  Connection port.

• database: string

  The name of the database.

• username: string, optional

  The username for logging in.

• password: string, optional

  The password for logging in.

Example:

infinity.loadModule('infinity.database');
let myDB = new infinity.database();
myDB.connect(infinity.database.provider.mysql, 'localhost', 3306, 'exampleDB', 'root');

count()¶

Returns the number of rows present inside the specified table.

Signature:

count( table: string ): number

Parameters:

• table: string

  The table to examine.

Return type: number

Example:

let rowsInTable = myDB.count('testTable');

create()¶

Creates a table with the specified name inside the connected database.

Signature:

create( table: string ): void

Parameters:

• table: string

  The name of the table to create.

Example:

myDB.create('testTable');

disconnect()¶

Disconnects from the current database.

Signature:

disconnect(): void

Example:

myDB.disconnect();
myDB.free();

drop()¶

Deletes the specified table from the connected database.

Signature:

drop( table: string ): void

Parameters:

• table: string

  The name of the table to delete.

Example:

myDB.drop('testTable');

eof()¶

Returns true, if the cursor is pointing to the end of the current result set.

Signature:

eof(): boolean

Return type: boolean

Example:

while( !myDB.eof() ) {
  myDB.next();
  //...
}

escape()¶

Returns escaped query parameters to prevent SQL-injection-attacks.

Signature:

escape( value: any, asLikeString?: boolean ): string

Parameters:

• value: any

  The parameters to escape.

• asLikeString: boolean, optional

  Whether to use special escaping for statements with the LIKE operator.

Return type: string

Example:

db.execute('select i_id, c_title from documents where i_id = ' myDB.escape( id) + ';');
//different notation:
myDB.execute(`SELECT i_id, c_title FROM documents WHERE i_id = ${myDB.escape(id)};`);
//escaping for strings:
myDB.execute(`SELECT i_id, c_title FROM documents WHERE i_id = "${myDB.escape(text)}";`);

execute()¶

Executes the database query and returns a boolean value according to success or failure.

Signature:

execute( sql?: string ): boolean

Parameters:

• sql: string, optional

  The database query to execute.

Return type: boolean

Example:

if( myDB.execute('SELECT i_id, c_title FROM documents ORDER by i_id desc limit 10;') ) {
  //...
}

//Different notations possible:
let id = 1;
myDB.execute('select i_id, c_title from documents where i_id = ' + id + ';');
myDB.execute(`select i_id, c_title from documents where i_id = ${id};`);

//Usage of the sql-property:
myDb.sql = 'select count(*) from documents;';
myDB.execute();

exists()¶

Checks whether a table with the specified name exists inside the connected database.

Signature:

exists( table: string ): boolean

Parameters:

• table: string

  The table name to check for.

Return type: boolean

Example:

if( myDB.exists('testTable') ){
  //...
}

find()¶

Allows querying a database in a more structured way using an sql-like query language.

Signature:

find( table: string, query: infinity.database.queryArray, orderBy?: string, orderDirection?: infinity.database.orderDirection, offset?: number, limit?: number, outputMode?: infinity.database.outputMode ): infinity.database.findResultArray

Parameters:

• table: string

  The name of the table to be queried.

• query: infinity.database.queryArray

  An array of options for the query, usable with the following operators: =, <>, !=, >, <, >=, <=, beginswith, endswith, contains.

• orderBy: string, optional

  Specifies by which column the result has to be ordered.

• orderDirection: infinity.database.orderDirection, optional

  Specifies the order direction.

• offset: number, optional

  Specifies, how many row have to be skipped.

• limit: number, optional

  Constrains the number of rows to return.

• outputMode: infinity.database.outputMode, optional

  Specifies how the data is returned.

Return type: infinity.database.findResultArray

Example:

let ids = myDB.find('users', [['email', 'endswith', 'e']], '', infinity.database.orderDirection.ascending, 0, 0, infinity.database.outputMode.ids);

first()¶

Places the cursor at the first row of the current result set.

Signature:

first(): void

Example:

myDB.execute('SELECT * FROM testTable');

console.debug(myDB.row());
myDB.next();
console.debug(myDB.row());
myDB.first();
console.debug(myDB.row());

free()¶

Frees the memory previously occupied by the database object instance.

Signature:

free(): void

Example:

myDB.disconnect();
myDB.free();

get()¶

Returns a row with the given ID out of the specified table.

Signature:

get( table: string, id: number ): object

Parameters:

• table: string

  The name of the table to be queried.

• id: number

  The ID of the row to be returned.

Return type: object

Example:

let secondRow = myDB.get('testTable', 2);

get()¶

Returns multiple rows with the given IDs out of the specified table.

Signature:

get( table: string, ids: infinity.database.idsArray ): infinity.database.getResultArray

Parameters:

• table: string

  The name of the table to be queried.

• ids: infinity.database.idsArray

  An array containing the IDs of the rows to be returned.

Return type: infinity.database.getResultArray

Example:

let someRows = myDB.get('testTable', [2, 3]);

getDatabaseNames()¶

Returns an array of the names of databases on the connected server.

Signature:

getDatabaseNames(): infinity.database.stringArray

Return type: infinity.database.stringArray

Example:

let dbNames = myDB.getDatabaseNames();

getFieldNames()¶

Returns an array of the field names of the specified table.

Signature:

getFieldNames( table: string ): infinity.database.stringArray

Parameters:

• table: string

  The table to be queried.

Return type: infinity.database.stringArray

Example:

let fieldNames = myDB.getFieldNames('testTable');

getStoredProcNames()¶

Returns an array of stored procedures from the server.

Signature:

getStoredProcNames(): infinity.database.stringArray

Return type: infinity.database.stringArray

Example:

let storedProcNames = myDB.getStoredProcNames();

getTableNames()¶

Returns an array of the names of the tables on the connected database.

Signature:

getTableNames(): infinity.database.stringArray

Return type: infinity.database.stringArray

Example:

let tableNames = myDB.getTableNames();

has()¶

Indicates whether the specified table has a row with the given ID.

Signature:

has( table: string, id: number ): boolean

Parameters:

• table: string

  The name of the table to be checked.

• id: number

  The row to be checked.

Return type: boolean

Example:

if( myDB.has('testTable', 2) ){
  //...
}

index()¶

Creates an index column inside the given table and specifies the data type to be stored in. Can optionally specify the column to act as an index of the given type.

Signature:

index( table: string, key: string, indexType: infinity.database.indexType, columnType?: infinity.database.columnType, unique?: boolean ): void

Parameters:

• table: string

  The name of the table to be altered.

• key: string

  The column to act as an index.

• indexType: infinity.database.indexType

  Specifies the type of data that the index will hold.

• columnType: infinity.database.columnType, optional

  Defines whether the column is virtual or persistent.

• unique: boolean, optional

  Indicates whether the index should enforce uniqueness.

Example:

myDB.index('testTable', 'username', infinity.database.indexType.string, true);

insert()¶

Inserts new data into a database table. The data has to be passed inside an object.

Signature:

insert( table: string, document: object ): number

Parameters:

• table: string

  The name of the table to be altered.

• document: object

  An object containing the data to be inserted.

Return type: number

Example:

myDB.insert('testTable', { "username": "TheUserName1", "email": "theusername@email.com", "rights": ["read", "write", "create", "delete"] });

last()¶

Places the cursor at the last row of the current result set.

Signature:

last(): void

Example:

myDB.execute('SELECT * FROM testTable');

console.debug(myDB.row());
myDB.last();
console.debug(myDB.row());

next()¶

Places the cursor at the next row of the current result set.

Signature:

next(): void

Example:

myDB.execute('SELECT * FROM testTable');

console.debug(myDB.row());
myDB.next();
console.debug(myDB.row());

prev()¶

Places the cursor at the previous row of the current result set.

Signature:

prev(): void

Example:

myDB.execute('SELECT * FROM testTable');

console.debug(myDB.row());
myDB.next();
console.debug(myDB.row());
myDB.prev();
console.debug(myDB.row());

remove()¶

Removes rows from the specified table. Takes query parameters as an array. Returns the number of rows affected by the query.

Signature:

remove( table: string, query: infinity.database.queryArray ): number

Parameters:

• table: string

  The name of the table from which the row will be removed.

• query: infinity.database.queryArray

  An array containing the query parameters.

Return type: number

Example:

let rowsAffected = myDB.remove('testTable', [['id', '=',  2]]);

remove()¶

Removes a row from the specified table based on the provided row ID. Returns the number of rows affected by the operation.

Signature:

remove( table: string, id: number ): number

Parameters:

• table: string

  The name of the table from which the row will be removed.

• id: number

  The unique identifier (usually the primary key) of the row to be removed.

Return type: number

Example:

myDB.remove('testTable', 2);

result()¶

Returns the value at the current cursor position from the specified column of the result set.

Signature:

result( field: number|string ): any

Parameters:

• field: number|string

  The column of which the field has to be returned. Can be the column name or a numeric index.

Return type: any

Example:

myDB.execute('SELECT * FROM testTable');
let firstRowSecondColumn = myDB.result(2);
let firstRowOfTitleColumn = myDB.result('email');

result()¶

Returns the result of the last query in the JSON-format.

Signature:

result(): infinity.database.resultArray

Return type: infinity.database.resultArray

Example:

myDB.execute('SELECT * FROM testTable');
let result = myDB.result();
console.table(result);

rollback()¶

The Rollback method is equivalent to the SQL ROLLBACK statement. The transaction can only be rolled back from a pending state (after infinity.database.startTransaction, but before infinity.database.commit has been called).

Signature:

rollback(): void

Example:

myDB.autoCommit = false;
myDB.startTransaction();
try {
  myDB.execute('INSERT...');
  myDB.execute('UPDATE...');
  myDB.commit();
} catch {
  myDB.rollback();
}

row()¶

Returns a row out of the current result set from the current cursor position as an array.

Signature:

row(): infinity.database.rowArray

Return type: infinity.database.rowArray

Example:

myDB.execute('SELECT * FROM testTable');
console.debug(myDB.row());

rowObject()¶

Returns a row out of the current result set from the current cursor position as an object.

Signature:

rowObject(): object

Return type: object

Example:

myDB.execute('SELECT * FROM testTable')
console.debug(myDB.rowObject());

setParamValue()¶

Sets identifiers and values for parameters, which can be used in infinity.database.execute() through a preceding colon and without quotation marks. The parameters passed this way are being escaped automatically.

Signature:

setParamValue( name: string, value: any ): void

Parameters:

• name: string

  The name of the parameter.

• value: any

  The value of the parameter.

Example:

myDB.setParamValue('id', '2');
myDB.setParamValue('text', 'replacement');
myDB.execute('select i_id, c_title from documents where i_id = :id and c_text = :text;');

startTransaction()¶

Begins a new user transaction against the database server.

Signature:

startTransaction( isolationLevel?: infinity.database.isolationLevel, readOnly?: boolean ): void

Parameters:

• isolationLevel: infinity.database.isolationLevel, optional

  Specifies the transaction locking behavior.

• readOnly: boolean, optional

  Sets the transaction to read only.

Example:

myDB.startTransaction();
try {
  myDB.execute('INSERT...');
  myDB.execute('UPDATE...');
  myDB.commit();
} catch {
  myDB.rollback();
}

update()¶

Updates values in an existing row identified by the provided row id, inside the specified database table. The data has to passed inside an object. Returns the number of rows affected by the query.

Signature:

update( table: string, document: object ): number

Parameters:

• table: string

  The name of the table to be updated.

• document: object

  An object containing the data to be written.

Return type: number

Example:

myDB.update('testTable', { id: 2, username: "aUserName", email: "yetanother@email.com", modified: Math.floor(Date.now() * 0.001) });

update()¶

Updates values in an existing row identified by the provided row id, inside the specified database table. The data has to passed inside an object. Returns the number of rows affected by the query.

Signature:

update( table: string, id: number, update: object ): number

Parameters:

• table: string

  The name of the table to be updated.

• id: number

  The unique identifier of the row to be updated.

• update: object

  An object representing the new values for the specified row.

Return type: number

Example:

myDB.update('testTable', 2, { username: "newUserName", email: "new@email.com" });

update()¶

Updates values in an existing row identified by the provided query parameters, inside the specified database table. The data has to passed inside an object. Returns the number of rows affected by the query.

Signature:

update( table: string, query: infinity.database.queryArray, update: object ): number

Parameters:

• table: string

  The name of the table to be updated.

• query: infinity.database.queryArray

  An array containing the query parameters.

• update: object

  An object containing the data to be written.

Return type: number

Example:

myDB.update('testTable', [['username', '=', 'TheUserName1']], { id: 2, username: "aUserName", email: "yetanother@email.com", modified: Math.floor(Date.now() * 0.001) });

findResultArray¶

Extends: Array<object|number>

An array of objects or numbers.

getResultArray¶

Extends: Array<object>

An array of objects.

idsArray¶

Extends: Array<number>

An array of numbers.

queryArray¶

Extends: Array<string|Array<string|number|boolean>>

An array of strings or of arrays containing strings, numbers or boolean values.

resultArray¶

Extends: Array<Array<any>>

An array of arrays, containing any datatype.

rowArray¶

Extends: Array<any>

An array of any datatypes.

stringArray¶

Extends: Array<string>

An array of strings.

infinity.database.columnType¶

Used to specify the type of a column within a database context.

Values:

• virtual: 0

• persistent: 1

Example:

infinity.loadModule('infinity.database');
let columnType = infinity.database.columnType.virtual;

infinity.database.indexType¶

Used for setting the data type for the index column.

Values:

• boolean: 0

• string: 1

• int: 2

• bigint: 3

• float: 4

• double: 5

Example:

infinity.loadModule('infinity.database');
let indexType = infinity.database.indexType.boolean;

infinity.database.isolationLevel¶

Specifies the transaction locking behavior for a connection.

Values:

• readCommitted: 0

  Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data.

• readUncommitted: 1

  A dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored.

• repeatableRead: 2

  Locks are placed on all data that is used in a query, preventing other users from updating the data. Prevents non-repeatable reads but phantom rows are still possible.

• isolated: 3

  A range lock is placed on the dataset, preventing other users from updating or inserting rows into the dataset until the transaction is complete.

• snapshot: 4

  Reduces blocking by storing a version of data that one application can read while another is modifying the same data. Indicates that from one transaction you cannot see changes made in other transactions, even if you requery.

Example:

infinity.loadModule('infinity.database');
let isolationLevel = infinity.database.isolationLevel.readCommitted;

infinity.database.orderDirection¶

Specifies the order direction of the returned result set.

Values:

• none: 0

• ascending: 1

• descending: 2

Example:

infinity.loadModule('infinity.database');
let orderDirection = infinity.database.orderDirection.ascending;

infinity.database.outputMode¶

Specifies how the result data has to be returned.

Values:

• objects: 0

  As objects.

• ids: 1

  As row IDs in a comma-separated string.

• count: 2

  As a total count number.

Example:

infinity.loadModule('infinity.database');
let outputMode = infinity.database.outputMode.objects;

infinity.database.provider¶

Specifies the database provider to be used. See also specificOptions for extended setup possibilities.

Values:

• access: Access

  Microsoft Access

• advantage: Advantage

  Advantage Database Server

• ase: ASE

  SAP Sybase Adaptive Server Enterprise

• db2: DB2

  DB2

• dbf: DBF

  DBF

• interbase: InterBase

  InterBase/Firebird

• mysql: MySQL

  MySQL

• odbc: ODBC

  ODBC

• oracle: Oracle

  Oracle

• postgresql: PostgreSQL

  PostgreSQL

• sqlserver: SQL Server

  SQL Server

• sqlite: SQLite

  SQLite

Example:

infinity.loadModule('infinity.database');
let provider = infinity.database.provider.access;