SQL Statements Builder

SQL Statements Builder API Provides a fluent API for building SQL statements. The operations in the API gather the necessary parameters for a builder function for a statement. It's up to you to make sure you have supplied all necessary parameters before trying to build a query. Builders can be redefined as appropriate.

Inspired by Squel.

Getting a query builder instance: require('daoism/statements').get().builder()

Examples:

Note: A full set of examples is available at statements_tests.js

INSERT:

var qb = require('daoism/statements').get().builder()
        .insert().into('TBL_A')
		.set({
			name: 'id',
			dbName:'ID',
			type: 'Int'
		})
		.set({
			name: 'text',
			dbName:'TEXT',
			type: 'String'
		}, 'abc');

qb.toString()' produces INSERT INTO TBL_A(ID, TEXT) VALUES(?, 'abc')`

qb.toParams().parameters produces [{name:"id", dbName:"ID", type:"Int"}]

SELECT

qb = require('daoism/statements').get().builder().builder()
	.select().from('TBL_A')
	.field('ID', 'id')
	.field('NAME')
	.where('TEXT LIKE %%?', [{name:'text', dbName:'TEXT'}])
	.where('TEXT IS NOT NULL')
	.order('TEXT')
	.order('NAME', false)
	.left_join('ID', 'd', 'd.id=b.id')
	.left_join('OTHER_ID', 'c', 'c.id=b.id')	
	.limit(10).offset(0);

qb.toString() produces SELECT ID AS id, NAME FROM TBL_A LEFT JOIN ID 'd' ON d.id=b.id LEFT JOIN OTHER_ID 'c' ON c.id=b.id WHERE TEXT LIKE %? AND TEXT IS NOT NULL ORDER BY TEXT ASC, NAME DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY

qb.toParams().parameters produces [{name:"text", dbName:"TEXT"}]

Getting the default builder functions: require('daoism/statements').get().builder().builders

Getting the insert builder function: require('daoism/statements').get().builder().builders.insert

The available builder functions out-of-the-box are: createtable, droptable, insert, update, select, delete. Each produces a valid sql string that can be a parameterized statement, based on the parameters gathered beforehand by apporpriate api operation on this query builder instance.

The toString() operation returns an sql (parameterized) string. The toParams returns an object with two properties: sql, containing the result of toString() and parameters - an array of parametric fields in the order they are read form the sql statement. Calling toParams is useful for executing statement.toXXX methods in correct order on a parametric statement.

API

• insert() - signals an INSERT sql statement is being constructed

  Example: qb.insert().into('TBL_A');

• into(tableName)- used in context of insert operation. Sets the table name in the insert statement. The parameter tableName must be a valid sql string for table name.

  Example: qb.insert().into('TBL_A');

• set(fieldDef, value) - used in context of createtable, insert and update operation. Defines a filed used in UPDATE SET field part of statement or filed list in INSERT. The fieldDef parameter must contain dbName property, which is a valid string for an sql field name. The type property is required if the statement under construciton is createtable or this QueryBuilder will be used in the statements.execute operation

  Example:

  qb.insert().into('TBL_A')
  .set({
  name: 'id',
  dbName:'ID',
  type: 'Int'
  })
  .set({
  name: 'text',
  dbName:'TEXT',
  type: 'String'
  `}, 'abc')

• update() - signals an UPDATE sql statement is being constructed

Example: qb.update().table('TBL_A');

• delete() - signals an DELETE sql statement is being constructed

Example: qb.delete().from('TBL_A').where('ID=?', [{name: 'id', dbName: 'ID', type: 'Int'}]);

• **from(tableName) - denotes the table name in a SELECT or DELETE statements

• select() - signals an SELECT sql statement is being constructed

  Example:

  qb.select().from('TBL_A') .field('ID', 'id') .field('NAME') .where('TEXT LIKE %%?', [{name:'text', dbName:'TEXT'}]) .where('TEXT IS NOT NULL') .order('TEXT') .order('NAME', false) .left_join('ID', 'd', 'd.id=b.id') .left_join('OTHER_ID', 'c', 'c.id=b.id')	 .limit(10).offset(0)

• field(fieldName, alias) - specifies a filed in a SELECT statement.

• where(condition, parametricFieldDefinitions) - specifies a WHERE condition in statement. Can be invoked multiple times which will result in multiple conditions chained with a boolean AND. If you want to specify OR condition or something more elaborate do this in the first argument and provide as second argument as many parametric fields as you have specified in your filter.

Example: qb.field("TEXT IS NULL OR TEXT.LENGTH()<1 OR TEXT='?'", [{name: 'text', dbName:'TEXT'}])

• left_join(table, tableAlias, joinStatement, parameterizedFields) - adds a left join statement to the query

• createTable(tableName) - starts a CREATE TABLE statement. Table name is optional but if not specified you must call table(tableName) to ensure that it's specified before invoking toString();

• dropTable(tableName) - starts a DROP TABLE statement. Table name is optional but if not specified you must call table(tableName) to ensure that it's specified before invoking toString();

• fieldDef(fieldDef) - defines a field for a CREATE TABLE statement. That parameter must supply basic information about the field: valid database name string, valid database type string. Optionally it can supply a size, optional that can be true or false (i.e. allow NULLs or not), pk which can be true or false (i.e. is this a primary key) and defaultValue. The primary key statement is not used right now because setting this constraint is very database specific.