This project is read-only.
Install

https://www.nuget.org/packages/jSQL.js/

Project Description

jSQL facilitates the query, update and delete of items, in an array of plain objects, using JavaScript and SQL like syntax.

Features
  • SQL like syntax
  • SQL feature implementation: Join, Left Join, Select [Distinct] [Top], Where, Group By, Having, Aggregate functions, Order By [Desc], Update and Delete
  • Cascading delete
  • User defined aggregate functions
  • Lazy evaluation
  • Ability to build a partial query and then extend it as a new independent query
  • Full visual studio intellisense support
  • Comprehensive parse-time and optional execution-time error checking
jSQL is not LINQ and is not a full implementation of SQL featuring the creation of tables, views etc. It works with the following:
  • An array of plain (key/value pair) objects
  • An object that implements a getItems() method that returns an array of plain (key/value pair) objects

Contents

Examples

The most effective way to demonstrate the features is by example. For further details, see the non-minimized code or the visual studio intellisense prompts.

All examples will use tables of the following structure:

  orders = [{ id: 1, address: '1 street', name: 'Jim' }, ... ]
  orderItems = [{ id: 10, orderId: 1, price: 10.5, qty: 2 }, ...]

All example use the following alias for brevity:

q = jSQL.qry; 


1. Quickies

Select query

q().from(orders, "o").join(orderItems, "oi", "x.oi.orderId === x.o.id").select("x.o.id", "x.oi.price * x.oi.qty[total]").where("x.o.id > 10").toArray();

id total
... ...

Group by query

q().from(orders, "o").join(orderItems, "oi", "x.oi.orderId === x.o.id").where("x.oi.qty > 10").groupBy("x.o.id", "x.o.name").sum("x.oi.price * x.oi.qty[total]").avg("x.oi.price").having("x.total > 100").toArray();

id name total avg_price
... ... ... ...

Update

q().from(orders, "o").join(orderItems, "oi", "x.oi.orderId === x.o.id").where("x.oi.qty > 100").update("x.o.name += 'Important!'");

Delete

q().from(orderItems, "oi").leftjoin(orders, "o", "x.o.id === x.oi.orderId").where("x.o.id === null").remove("oi");


2. Build the query

.where()

q().from(orders).where("x.id > 10")
q().from(orders).where("x => x.id > 10")
q().from(orders).where(function (x) { return x.id > 10; })

Note: Any of the above filter expression formats can be used in the following examples wherever you see a having method or on parameter of a join method.

.select()

q().from(orders).select("x.id", "x.address").where("x.id > 10")
q().from(orders).select("x.id[id]", "x.address[address]").where("x.id > 10")
q().from(orders).select("x => x.id", "x => x.address").where("x.id > 10")
q().from(orders).select("x => x.id[id]", "x => x.address[address]").where("x.id > 10")
q().from(orders).select({ id: "x.id", address: "x.address" }).where("x.id > 10")
q().from(orders).select(function (x) { return { id: x.id, address: x.address } }).where("x.id > 10")

Note: Any of the above selector expression formats can be used in the following examples wherever you see a selectDistinct, groupBy or aggregate method.

Select all items:
q().from(orders)
q().from(orders).select()
q().from(orders).select("*")

Note: The selector ('*') can also be specified with the selectDistinct and count methods.

Select all items from a particular table:
q().from(orders, "o").select("o.*", ...)

Note: The selector ('[alias].*') can also be specified with the selectDistinct method.

.selectDistinct()

q().from(orders, "o").join(orderItems, "oi", "x.oi.orderId === x.o.id").selectDistinct("x.o.id", "x.oi.price").where("x.o.id > 5")

Note: Aggregate methods can't be used with selectDistinct; instead, use groupBy.

.join()

Note: Specifying and alias with a single table is optional but is a requirement on all tables when joining.

q().from(orders, "o").join(orderItems, "oi", "x.oi.orderId === x.o.id").where("x.o.id > 5")


.leftjoin()

Select all order items that aren't attached to an order:
q().from(ordersItems, "oi").leftjoin(orders, "o", "x.o.id === x.oi.orderId").where("x.o.id === null")


Select all orders that don't have any order items:
q().from(orders, "o").leftjoin(orderItems, "oi", "x.o.id === x.oi.orderId").where("x.oi.id === null")


Whole table aggregates and .having()

q().from(ordersItems, "oi").count("*").sum("x.oi.price[total_price]", "x.oi.qty").avg("x.oi.qty[avg_qty]").having("x.total_price > 10 && x.avg_qty < 5")

Note: The select method can't be specified when aggregating the whole table. If you want to specify it, for readability purposes, include an empty select():

q().from(ordersItems, "oi").select().count("*").sum("x.oi.price[total_price]", "x.oi.qty").avg("x.oi.qty[avg_qty]").having("x.total_price > 10 && x.avg_qty < 5")


.groupBy(), aggregates and .having()

q().from(orders, "o").join(orderItems, "oi", "x.oi.orderId === x.o.id").groupBy("x.o.id", "x.o.name").sum("x.oi.price[total_price]", "x.oi.qty[total_qty]").having("x.total_price > 10")


.agg() (User defined aggregate function)

A user defined function is passed an object containing the following properties:
{ values: Array, count: Number, total: Number, min: Number, max: Number }

The following implements a function that calculates the average price and quantity:

q().from(ordersItems, "oi").agg("x.total / x.count", "x.oi.price", "x.oi.qty") 
q().from(ordersItems, "oi").agg("x => x.total / x.count", "x.oi.price", "x.oi.qty") 
q().from(ordersItems, "oi").agg(function (x) { return x.total / x.count; }, "x.oi.price", "x.oi.qty") 


.orderBy() & .orderByDesc()

q().from(orders, "o").join(orderItems, "oi", "x.oi.orderId === x.o.id").groupBy("x.o.id", "x.o.name").sum("x.oi.price[total_price]").orderBy("x.name").orderByDesc("x.total_price")
q().from(orders, "o").join(orderItems, "oi", "x.oi.orderId === x.o.id").groupBy("x.o.id", "x.o.name").sum("x.oi.price[total_price]").orderBy("x => x.name").orderByDesc("x => x.total_price", true)
q().from(orders, "o").join(orderItems, "oi", "x.oi.orderId === x.o.id").groupBy("x.o.id", "x.o.name").sum("x.oi.price[total_price]").orderBy(function (x) { return x.name; }).orderByDesc(function(x) { return x.total_price; } }, true)

Note: In the order by, we refer to x.name, not x.o.name because the groupBy selector will automatically rename that column to name.

3. Execute the query

.toArray()

var items = q().from(orders).select("x.id", "x.address").toArray();


.iterate()

q().from(orders).select("x.id", "x.address")
    .iterate(function (item, index) { // do something with each item }, 
                function () { // do something when the iteration is complete } );


.exists()

Executes the query and returns true when the first item is found; otherwise false. Execution is halted as soon as an item is found. If an orderBy clause is specified, it is ignored. If no having clause is specified, a groupBy clause, select clause, selectDistinct clause and any aggregates are also ignored.

var exists = q().from(orders).where("x.id === 10").exists();


.find()

Executes the query and returns the first item, if found; otherwise null. Execution is halted as soon as an item is found. If an 'orderBy' clause is specified, it is ignored. If you want the first item based on a particular order then use top(1).

var item = q().from(orders).where("x.id === 10").find();

An optional parameter can be passed to indicate a scalar result.

Specify true to return the value of the first property in the item, if an item was found; otherwise null:
var id = q().from(orders).where("x.id === 10").find(true);

Specify a property name to return the property value, if an item was found; otherwise null. An error is thrown if the property doesn't exist on the returned item:
var name = q().from(orders).where("x.id === 10").find("name");


.skipTake()

The following example executes the query and returns 4 results starting from the 3rd:
var items = q().from(orders).select("x.id", "x.address").orderBy("x.id").skiptake(2, 4);

An optional callback can be specified if you'd like to iterate the results instead of receiving an item array:
q().from(orders).select("x.id", "x.address").orderBy("x.id")
    .skiptake(2, 4, function (item, index) { // do something with each item }, 
                           function () { // do something when the iteration is complete });


.top()

The following example executes the query and returns the first 5 results:
var items = q().from(orders).select("x.id", "x.address").orderBy("x.id").top(5);

An optional callback can be specified if you'd like to iterate the results instead of receiving an item array:
q().from(orders).select("x.id", "x.address").orderBy("x.id")
    .top(5, function (item, index) { // do something with each item }, 
                function () { // do something when the iteration is complete });


.bottom()

The following example executes the query and returns the last 5 results:
var items = q().from(orders).select("x.id", "x.address").orderBy("x.id").bottom(5);

An optional callback can be specified if you'd like to iterate the results instead of receiving an item array:
q().from(orders).select("x.id", "x.address").orderBy("x.id")
    .bottom(5, function (item, index) { // do something with each item }, 
                      function () { // do something when the iteration is complete });


.update()

Update individual properties:
var q1 = q().from(orders).where("x.id === 10");
console.log("To update: " + q1.toArray());
q1.update("x.name = 'changed_name'");
q1.update("x => x.name = 'changed_name'");
q1.update(function (x) { x.name = 'changed_name'; } );

Replace a whole item:
q().from(orders).where("x.id === 10").update({ id: 10, name: 'changed_name' });

Replace whole item(s) in a join:
q().from(orders, "o").join(orderItems, "oi", "x.oi.orderId === x.o.id").where("x.id === 10")
    .update({ o: { id: 10, name: 'changed_name' }, oi: { ... }, ... });

.remove()

var q1 = q().from(orders).where("x.id === 10");
console.log("To delete: " + q1.toArray());
q1.remove();

var q1 = q().from(orders, "o").where("x.id === 10");
console.log("To delete: " + q1.toArray());
q1.remove("o");

Delete order items that aren't attached to an order:
q().from(orderItems, "oi").leftjoin(orders, "o", "x.o.id === x.oi.orderId").where("x.o.id === null").remove("oi");

Specify multiple aliases for a cascading delete. In the following example, the orders will deleted along with their related order items:
jSQL.qry().from(orders, "o").join(orderItems, "oi", "x.oi.orderId === x.o.id").where(...).remove("o", "oi");

4. Other

.extend()

You can build a partial query and then extend it with the addition of further clauses. After extending, the original query will not be effected. Adding additional clauses to the original query will not effect the extended one:

var q1 = q().from(orders, "o").join(orderItems, "oi", "x.oi.orderId === x.o.id");
var q2 = q().extend().where("x.o.id === 10");
q1 = q1.where("x.o.id === 1);

Column/Property Names

Naming

1. Where a selector is in the form of an object or a function that returns an object, the specified names are used:

.select({ id: "o.x.id", name: "o.x.name" })
.select(function (x) { id: o.x.id, name: o.x.name })


2 (a). Where a selector is in the form of a string expression, a column name can be optionally specified in square brackets. If not, the column name is determined from the expression, where possible; otherwise it is auto-generated. In the following example, the names will be id, name, total, Column 1 and Column 2:

.select("x.o.id", "x.o.name", "x.oi.price * x.oi.qty[total]", "x.oi.price * x.oi.qty", "x.oi.price / x.oi.qty")
.select("x => x.o.id", "x => x.o.name", "x => x.oi.price * x.oi.qty[total]", "x => x.oi.price * x.oi.qty", "x => x.oi.price / x.oi.qty")


2 (b). If the selector is for an aggregate function then the name depends on the setting of options.aggregateColumnName:
  • When set to true, the same rules as 2 (a) are applied.
  • When set to false, the name is always auto-generated.
  • When set to agg_name and the name isn't specified in square brackets then it is determined, where possible, from the expression and prefixed with the aggregate method name. In the following example, the names will be sum_qty, sum_price, avg_qty and avg_price:
.sum("x.oi.qty", "x.oi.price").avg("x.oi.qty", "x.oi.price")
.sum("x => x.oi.qty", "x => x.oi.price").avg("x => x.oi.qty", "x => x.oi.price")


3. Where a selector is in the form of a function that returns a value, the name will be auto-generated. In the following example, the names will be Column 1 and Column 2:

.select(function(x) { return x.o.id; }, function(x) { return x.oi.price * x.oi.qty; })


Referencing

The order of execution and whether a table alias is specified is important when determining how to reference a column in a query.
  • When an alias is specified, the row is transformed as in the following example (assuming an alias of t1):
{ id: 10, name: "Bob" } becomes: { t1: { id: 10, name: "Bob" }  }
  • When a select, selectDistinct, groupBy or aggregate method is specified, the column names are again transformed:
.select({ id: "x.t1.id", name: "x.t1.name" }) resulting row: { id: 10, name: "Bob" }
  • A where clause is executed first and so should always refer to the aliased column names even if select, selectDistinct, groupBy or aggregate methods have been specified:
.from(order, "o").select("x.o.id[Id]", "x.o.name[Name]").where("x.o.id === 1")
  • A having clause is executed after the groupBy and aggregate methods and should, therefore, refer to those names:
.from(orders, "o").groupBy("x.o.id", "x.o.name").sum("x.o.price").having("x.sum_price > 100")
  • An orderBy is executed last and so it's references will depend on whether select, selectDistinct, groupBy or aggregate methods have been specified:
.from(orders, "o").orderBy("x.o.id");
.from(orders, "o").select("x.o.id").orderBy("x.id");
.from(orders, "o").groupBy("x.o.id").sum("x.o.price").orderBy("x.id", "x.sum_price");

Tips

  • To increase performance, if selecting a single result and there is no orderBy clause, use find, not top(1).
  • To increase performance, if performing an exists or find on a single table, don't specify an alias and don't include a select.
  • If you're selecting all the records, you can exclude the select method, which is the same as specifying select() or select('*') .
  • If you have multiple queries that are based on the same tables, joins or clauses, then use the extend method to create new independent queries based on a base.

Options

Options can be specified at the global level via jSQL.options or passed to an individual query as: jSQL.qry(options)

validate

Type Default Value
Boolean false

When true:
  • Ensure that a where, having and join on filter expression returns a Boolean value.
  • Ensure that a selector expression (select, selectDistinct, groupBy and aggregates), an update function and a user defined aggregate function returns a non-undefined value. Additionally a check may be made for NaN and Infinity depending on the setting of options.allowNaN and options.allowInfinity.
  • Ensure that an update function, an orderBy expression and a user defined aggregate function doesn't return an Object.
  • Ensure that a selector expression doesn't result in a duplicate property name. Note that if the expression contains an Object with a duplicate property name then this error will be missed since JavaScript will override the previous property.
  • Ensure that a Function type selector doesn't contain a sub-function.
When false, some of the above validation will still be performed, where possible, at pre-execution time.

validateTableItems

Type Default Value Description
Boolean false If true, a check is made to ensure that all table items are objects and that they all have the same property names; if false, a check is still made on the first item to ensure it is an object.

lambdaArg

Type Default Value Description
String 'x' Specifies the argument to use when full lambda syntax is not employed.

aggregateColumnName

Type Default Value Description
Boolean | String 'agg_name' Specifies how to generate the name for an aggregate column if a name is not specified either through an Object property name or square brackets in a String expression.


Value Description
false The next available automatic name, such as 'Column 1', 'Column 2' etc. is used.
true If the name can be determined from a simple string expression, that name is used; otherwise the column name is auto generated (see above). e.g. For sum("x.t1.price") , the name will be 'price'. For sum("x.t1.price * x.t1.qty") , the name will be 'Column (n)'.
'agg_name' If the name can be determined from a simple string expression, the aggregate method name plus an underscore plus the property name is used; otherwise the column name is auto generated (see above). e.g. For sum("x.t1.price") , the name will be 'sum_price'.

allowNaN

Type Default Value Description
Boolean false Specifies whether the result of a function can be NaN (see options.validate).

allowInfinity

Type Default Value Description
Boolean false Specifies whether the result of a function can be plus or minus Infinity (see options.validate).



Important difference to T-SQL

Note: These differences may also apply to other variants of SQL.
  • If a left join is performed and table 1 has data but the join table is empty, the resulting row will only contain columns from table 1. Unlike T-SQL where the tables are predefined, it's impossible to return the join table columns with null values. Instead, assuming a join alias of "t2", the resulting row will look something like: { t1: { id: 10, name: "Jim" }, t2: null } . If the join table has at least one row, then the columns will be included in the row, with null values e.g. { t1: { id: 10, name: "Jim" }, t2: { price: null, qty: null } } .
  • In T-SQL, if an aggregate is made of the whole table with a where clause that is not satisfied, then the row will be returned with default values for each column (zero for count, null for the rest). In jSQL, null will be returned.

Release Notes

1.0.1

  • onComplete callback added to iterator methods.

1.0.2

  • Added an overload of the update method so that a whole row can be replaced instead of updating the properties individually.

1.0.3

  • Bug fix: A remove with a join sometimes removed too many items from T1.
  • Bug fix: The update method was interpreting a string expression as a replaceWith object.
  • New feature: The table specified in a from or join method can be an object that implements a getItems() method.
  • New feature: Multiple aliases can be specified in a remove to allow cascading deletes in a join.

1.0.4

  • Pass the item index as the second parameter of iterator methods.

Last edited Feb 22, 2015 at 7:52 AM by JayTeeAitch, version 29