database-js-sqlparser

Common functionality for database-js drivers that operate on non-database backends

About

Database-js-sqlparser is a database-js driver that parses SQL statements and passes requests and commands
to an underlying class which does the storage mechanism interaction. On its own it accomplishes nothing.

The sql parser supports the following SQL:

#### Tables
```SQL
CREATE TABLE ( ,...)
``
Where the column type can be one of:
* CHARACTER(n) - String of n length. Always padded or truncated to n length.
* VARCHAR(n) - String of up to n length. Always truncated to n length.
* BOOLEAN - Boolean (true or false)
* INTEGER, SMALLINT, BIGINT - Integer numeric values
* DECIMAL, NUMERIC, FLOAT, REAL, DOUBLE - Floating point numeric values, limited by Javascript's floating point implementation.
* DATE, TIME, TIMESTAMP - Date values
* TEXT - String values of arbitrary length

``SQL
DROP TABLE
``

#### Queries
``SQL
SELECT [
*,
[AS ],
SUM|COUNT(]
] FROM
[[INNER,LEFT,RIGHT] JOIN ON ]
[GROUP BY ]
[WHERE ]
[ORDER BY ]
[LIMIT [row_offset,]]
``
##### Joins:
Inner, left and right joins are supported. Full or outer joins are not supported.

##### Aggregate Functions:
Sum and count are currently supported. Sum will not fail on non-numeric columns, but the return is undefined.

#### Inserts
``SQL
INSERT INTO (,,...) VALUES(,,...)
``
It's best to use paramaterized SQL:
``SQL
INSERT INTO (,,...) VALUES(?,?,...)
``

#### Updates
``SQL
UPDATE SET = , = ,...
[WHERE ]
``
Using parameterized SQL:
``SQL
UPDATE SET = ?, = ?,...
[WHERE ]
``

#### Deletes
``SQL
DELETE FROM [WHERE ]
``

Implementation in an extending class

A class extending the database-js-sqlparser class needs to override seven methods.
Each method needs to return a Promise to allow for asynchronous implementations.

#### ready() : Promise<boolean>
Indicates that the underlying storage mechanism is loaded and ready to receive
reads and writes.

To implement an always ready driver, use the following signature:
``javascript
ready() {
return Promise.resolve(true);
}
``

#### close() : Promse<boolean>
Allows the underlying storage mechanism to close if necessary.

#### load(table: string) : Promise<Array<{[key:string]:any}>>
Loads the rows from for a given table from the underlying storage and returns them
via the Promise.

The resolved value of the Promise needs to be an array of table rows, where each
row is a JSON like object with the column names as keys for the row values:

``JSON
[
{
"id": 1,
"name": "Me",
"age": 32
},
{
"id": 2,
"name": "You",
"age": 27
}
]
``

#### store(table: string, index: string|number, row: any) : Promise<string|number>
Updates or inserts a row into the underlying storage system. If index is a string of number,
then the action is an update, if index is null or undefined this is an insert. Resolves the
promise with the updated or inserted index.

#### remove(table: string, index: string|number) : Promise<string|number>
Removes a row from the underlying storage system. Resolves the promise with the
index that was removed.

#### create(table: string, definition: Array<column_definition>) : Promise<boolean>
Creates a new table according to the passed definition, resolves with true if successful.

The column definition is as follows:
``javascript
{
"name": string, // The column name
"index": number, // The column index, can be ignored
"type": "string"|"integer"|"float"|"date",
"length"?: number, // For VARCHAR(n) or CHARACTER(n) the string length limit
"pad"?: " ", // For CHARACTER(n) the string to pad short strings with
}
```

#### drop(table: string) : Promise<boolean>
Drops the table from the underlying storage system. The user will expect the
table data to be removed as well.