The SQL functional approach:
var db = new Database("/path/to/db.sqlite");
class Database {
/**
* Creates a new database object given the sqlite file
*
* If file is not found, a file is created
*
* @param path path of sqlite file
* @return Database. null on failure
*/
function Database(path);
var tables; // Array of Strings
/**
* A function to make an empty query or a query with
* given SQL query.
*
* @param sql SQL query
* @return Query
*/
function query([sql]);
/**
* Gives the number of rows in given table
*
* @param table tablename
* @return Number with number of rows
*/
function countAll(table);
/**
* Truncates given table
*
* @param table Tablename
* @return true on success, false on failure
*/
function truncate(table);
function createTable(table,fields);
function dropTable(table);
//function renameTable(table,newName);
//function addTableKey(table)
//function addColumn()
//function dropColumn()
//function modifyColumn()
}
class Query {
/**
* Adds column selection. If never specified, '*' is used.
*
* @param columns 1) An array of column names, 2) a column name. Multiple possible.
* @return Query
*/
function select(columns, ...);
/**
* SELECT MAX(field) portion of the query
*
* @param name of the column
* @return Query
*/
function selectMax(column);
/**
* SELECT MIN(field) portion of the query
*
* @param name of the column
* @return Query
*/
function selectMin(column);
/**
* SELECT AVG(field) portion of the query
*
* @param name of the column
* @return Query
*/
function selectAvg(column);
/**
* SELECT SUM(field) portion of the query
*
* @param name of the column
* @return Query
*/
function selectSum(column);
/**
* Runs the selection query and returns query result
*
* @param table Name of the table. Can be omitted when from() is used.
* @param limit Limited number of rows
* @param offset Offset within the result
* @return Result
*/
function get([table [, limit [, offset]]]);
/**
* Specify the table to select from
*
* @param table Name of the table
* @return Query
*/
function from(table);
/**
* Adds a where condition
*
* Using multiple where() calls results in an AND concatenation.
*
* @param key column name
* @param value value of the column
* @param object Object containing multiple column/value combinations
* @param custom_query A custom where query such as "name='Joe' OR score='15'"
* @return Query
*/
function where(key, value);
function where(object);
function where(custom_query);
/**
* Same as above but with OR concatenation
*/
function orWhere(...); // Same as above
/**
* Adds a join portion to the query
*
* @param table name of the table to join
* @param condition Join condition. If none specified, a natural join is used.
* @param type Type of join. Possible values are left, right,
* outer, inner, left outer, right outer.
* If none specified, defaults to SQLite default.
* @return Query
*/
function join(table[, condition[, type]]);
/**
* Adds a WHERE field IN (item, item) portion joined using AND
*
* @param column Column to compare to
* @param values Array of values
* @return Query
*/
function whereIn(column, values);
/**
* Adds a WHERE field IN (item, item) portion but joined using OR
*
* @param column Column to compare to
* @param values Array of values
* @return Query
*/
function whereIn(column, values);
/**
* Adds a WHERE field NOT IN (item, item) portion joined using AND
*
* @param column Column to compare to
* @param values Array of values
* @return Query
*/
function whereNotIn(column, values);
/**
* Adds a WHERE field NOT IN (item, item) portion joined using OR
*
* @param column Column to compare to
* @param values Array of values
* @return Query
*/
function orWhereNotIn(column, values);
/**
* Adds a LIKE clause, joined with AND
*
* @param column name of column
* @param value value to match for
* @param wildcard Wild card option: before, after, both (default)
* @return Query
*/
function like(column, value [, wildcard]);
/**
* Same as above, but with column-value object
*/
function like(object);
/**
* Like the like() function, but joined with OR
*/
function orLike(...);
/**
* Like the like() function, but as NOT LIKE statement
*/
function notLike(...);
/**
* Like the notLike() function, but joined together with OR:
*/
function orNotLike(...);
/**
* Adds a GROUP BY portion
*
* @param column name of a column
* @param columns array of column names
* @return Query
*/
function groupBy(column);
function groupBy(columns);
/**
* Adds DISTINCT keyword to the query
*
* @return Query
*/
function distinct();
/**
* Like the where() function, but then HAVING clause
*/
function having(...);
/**
* Like the having() function, but joined together with OR
*/
function orHaving(...);
/**
* Adds an ORDER BY portion
*
* @param column Name of column
* @param direction ORDER BY direction: either asc or desc
* @return Query
*/
function orderBy(column[, direction]);
/**
* Adds LIMIT and OFFSET clause to query
*
* @param limit Number of rows to return
* @param offset Offset to start from in result set
* @return Query
*/
function limit(limit[, offset]);
/**
* Performs an INSERT query
*
* @param table name of table
* @param data Key-value object containing the data in column-value order. If not supplied, set() is neccesary
* @return Number with insert id, or 0 if no insert id, -1 on failure
*/
function insert(table[, data]);
/**
* Sets a value for insertion or update
*
* @param column Name of column
* @param value value for column
* @param object key-value store
* @return Query
*/
function set(column, value);
function set(object);
/**
* Executes an UPDATE query.
*
* Can be used with WHERE etc clauses, and with set()
*
* @param table name of table
* @param data key-value store of data
* @return Number of affected rows, 0 on no change, -1 on failure
*/
function update(table[, data]);
/**
* Executes a DELETE query
*
* Can be used with the WHERE etc clauses
*
* @param table Name of the table
* @return Number of deleted rows, 0 on no deletion, -1 on failure
*/
function delete(table);
}
class Result {
/**
* Get the full dataset in an array
*
* Array-item per row. A row is a key-value object.
*
* @return Array of objects or null on failure
*/
function result();
/**
* Returns the result as a single row. If result has more than
* one row, only first row is returned.
*
* @param row Number of the row to return, default 0
* @return Object with key-value data
*/
function row([row]);
/**
* Gets the number of rows in the result
*
* @return Number of rows
*/
function numRows();
/**
* Gets the number of fields (columns) in the result
*
* @return Number of fields
*/
function numFields();
}
Comments so far?
// Rahkiin