-
Notifications
You must be signed in to change notification settings - Fork 0
JSQL
The JSQL API Script creates an interface to treat Roll20's state object like a SQL database. The specification for this "database" is based on SQLite, although not everything from SQLite is implemented, and some features from SQLite are implemented slightly differently. There are also a few "obvious" added functions above and beyond what SQLite has available.
JSQL creates the following objects in the global scope:
| Object | Description |
|---|---|
| bshields.jsql |
Should only be necessary if another script already defines jsql and $. The meat of the script's consumer-oriented functions are in bshields.jsql.sql, available through those other two objects.
|
| jsql |
Not created if another variable or function with the same name exists. This is equal to bshields.jsql.sql
|
| $ |
Not created if another variable or function with the same name exists. This is similar to bshields.jsql.sql
|
| $$ |
Not created if another variable or function with the same name exists. This is similar to bshields.jsql.sql.expr
|
See createTable for more detailed information.
Creates a new table in the database named tableName. You may include a schema in the name (eg, schema.table); if you do not, the table will be stored in the "default" schema.
execute will return true if the table was created, or false if it was not.
jsql.createTable('my_table')
.field('id', Number, true)
.field('name', String)
.execute();
See alterTable for more detailed information.
Allows renaming the table itself, as well as adding, removing, or renaming fields and changing the type of fields. Like createTable, you can specify the schema of the table you want to use.
execute will return true.
jsql.alterTable('my_table')
.rename('example')
.field('new_field', Object)
.drop('old_field')
.renameField('field1', 'field2')
.incrementField('id', true)
.resetFieldIndex('id', 5)
.changeFieldType('join_date', String)
.execute();
See dropTable for more detailed information.
Deletes a table from the database. Like createTable, you can specify the schema of the table you want to use. If the schema has any triggers watching the table, those triggers will be dropped.
execute will return true if the table was dropped, or false if it was not.
jsql.dropTable('my_table').execute();
See delete for more detailed information.
Deletes one or more records from the table. Like createTable, you can specify the schema of the table you want to use.
execute will return the number of records that were deleted.
jsql.delete('my_table')
.where(expression)
.execute();
See insert for more detailed information.
Inserts a record into the table. Like createTable, you can specify the schema of the table you want to use.
execute will return 1.
jsql.insert('my_table')
.fields({
name: 'Brian',
join_date: Date.now()
}).execute();
See select for more detailed information.
Selects one or more records from one or more tables. Like createTable, you can specify the schema of the table you want to use with from. Additionally, you can specify a table alias by either adding it after a space in the first parameter to from (eg, schema.table alias), or as a second parameter to from. Table aliases are required in most situations selecting from multiple tables.
execute will return an array of records.
jsql.select()
.from('my_table')
.where(expression)
.orderBy('name')
.execute();
See update for more detailed information.
Updates one or more records from the table. Like createTable, you can specify the schema of the table you want to use.
execute will return the number of records changed.
jsql.update('my_table')
.fields({
name: 'Sarah',
join_date: Date.now()
}).where(expression)
.execute()
See transaction for more detailed information.
Creates a Transaction object which can be used to execute multiple queries in sequence. This does not offer any real performance gains as in an actual SQL database, but it will guarantee that either all queries will execute without error, or none will execute.
commit will return true.
var transaction = jsql.transaction();
jsql.insert('my_table2', { useTransaction: transaction })
.sub(jsql.select()
.from('my_table1')
.where(expression))
.execute();
jsql.delete('my_table1', { useTransaction: transaction })
.where(expression)
.execute();
transaction.commit();
See createTrigger for more detailed information.
Creates a trigger that will fire before, instead of, or after an insert, update, or delete operation of the table. Like createTable, you can specify the schema you want to use with the trigger's name. A trigger can only watch a table in the same schema, so if you create schema_a.my_trigger, it cannot watch schema_b.my_table.
execute will return true if the trigger was created, or false if it was not.
jsql.createTrigger('my_trigger', 'my_table')
.beforeDelete()
.function(myTriggerFunction)
.execute();
See dropTrigger for more detailed information.
Deletes a trigger that was created by createTrigger. Like createTable, you can specify the schema you want to use with the trigger's name.
execute will return true if the trigger was dropped, or false if it was not.
jsql.dropTrigger('my_trigger').execute();
See expr for more detailed information.
Creates an expression that can be used in a number of functions.
var expression = jsql.expr().equals('name', 'Brian');
jsql.delete('my_table')
.where(expression)
.execute();
toJSON is called automatically by Roll20's log function. Normally, it is meant to return a JavaScript Object Notation representation of the object, however in the case of jsql it behaves more like a toString function. There is little need for this, but it does allow log(jsql) to print somewhat useful information to the log.
Stores the string JSQL Version ${version}, where ${version} is the current version number of the script.
Stores a reference to the cls object, which contains a number of classes, objects, and functions used internally by the script. The following properties and functions may be of use to client code:
| Property or Function | Description |
|---|---|
| DefaultOptions |
Stores a set of default options. You can change values in this object in order to avoid repeatedly passing the same options to jsql functions. See options for more information on query options.
|
| registerTypeHandler(type, handler) |
type should be a constructor function or string. handler should be a function which accepts a single parameter and returns a value. You can call cls.registerTypeHandler in order to add new recognized types for creating and altering tables. Any values inserted into the table will be passed to the handler function for the type of their column. The types Number, String, Boolean, Object, and Date are already registered. User-registered types will not persist between game sessions, so calls to this function should be made in on('ready', ...).
|
| uuid() |
This will generate a UUID string. The database uses this to uniquely identify records if they do not contain an autoincrement column, but unique IDs can be useful for a variety of purposes. This is also useful if you want to have a string column which contains UUIDs, or if you want to use cls.registerTypeHandler to create a UUID type recognized by the database.
|
| evaluate(expr, tables) |
Evaluates an Expression object. tables must be a function which accepts a parameter in the form of schema.table and returns a table object. However, if no part of the expression references any table columns, you can simply return a dummy table: cls.evaluate(expression, () => ({ fields: [], rows: [] }))
|
| deepCopy(source[, maxDepth]) |
Creates a deep copy of an object. If maxDepth is not specified, it will be 256.
|
| JoinBlock.Type |
An enumeration of the various types of joins which can be made for a select query. May be passed to the `join` function in lieu of a string. Values and their string equivalents are:
|
| TriggerEventBlock.When |
An enumeration for triggers. May be passed to the functions insert, update, and delete in lieu of a string. Values and their string equivalents are:
|
| TriggerEventBlock.Action |
An enumeration for triggers. May be passed to the functions before, instead, and after in lieu of a string. Values and their string equivalents are:
|
cls also has a toJSON function which has been written to be used like a toString function, just like jsql.toJSON.
You can call any of the functions available to jsql as functions of $, as well as access the VERSION and cls properties. The only difference between jsql and $ (other than being two keystrokes shorter) is that you can call $ like a function. This is equivalent to jsql.expr or $.expr.
// these expressions are all equivalent
jsql.expr().equals('name', 'Brian');
$.expr().equals('name', 'Brian');
$().equals('name', 'Brian');
// also equivalent
jsql.expr('name').equals('Brian');
$.expr('name').equals('Brian');
$('name').equals('Brian');
Reducing keystrokes for writing expressions by another hair, you can use $$ as a shortcut to creating a new Expression object. $$ has a function for each of the Expression builder functions. For example:
// these expressions are all equivalent
jsql.expr().equals('name', 'Brian');
$.expr().equals('name', 'Brian');
$().equals('name', 'Brian');
$$.equals('name', 'Brian');
You can use whatever you feel most comfortable with!
Do note that with the $$ option, you cannot provide an initial/identity value as with jsql.expr(val), $.expr(val), or $(val). The first function call from $$ must include all optional expression parameters, but subsequent chained calls can safely omit the last one, just like other uses of expr.
- options
- examples
- known bugs/missing features
- Table Functions
- C.R.U.D. Functions
- Meta Functions