Skip to content

Database Methods

Jump to bottom Edit New page
Daniel Tsui edited this page Jun 17, 2015 · 15 revisions

###Table of Contents

Types of Methods

There are four types of methods in this ORM.

  • Statement Methods may be chained. You must use one (and only one) starter per chain.
  • Caboose Methods may be added to the end of statement chains that end with fetch.
  • Query Methods override statements and cabooses and may not be combined with anything other than save and fetch.
  • Data Methods must be used to save or retrieve data.

Tables

SQL.Collection.createTable

Type: Query Method Parameters: tableObj (field, [data type, constraints])

CREATE TABLE field data type constraint

The createTable method is required for all SQL collections. Data types and constraints must be passed as keywords.

Data Types:

  • $number -> integer
  • $string -> varchar(255)
  • $json -> json
  • $datetime -> date
  • $float -> decimal
  • $seq -> serial
  • $bool -> boolean

Table Constraints:

  • $unique -> unique
  • $check -> check*
  • $exclude -> exclude
  • $notnull -> not null
  • $default -> default*
  • $primary -> primary key

*Check and default should be passed as objects inside the field-data type/constraint array. The key is $check or $default and the value would be the check or default value.

SQL.Collection.dropTable

Type: Query Method
Parameters: none

DROP TABLE table

Deletes cascade.

Editing Data

SQL.Collection.insert

Type: Query Method
Parameters: insertObj (field, value)

SQL: INSERT INTO table (fields) VALUES (values)

SQL.Collection.update

Type: Statement Starter Method
Parameters: updateObj (field, value)

UPDATE table SET (fields) = (values)

SQL.Collection.remove

Type: Statement Starter Method
Parameters: none

DELETE FROM table

If not chained with where, will delete all rows in the table.

Finding Data

Pre-Built Find Methods

SQL.Collection.findOne

Type: Query Method
Parameters: id (optional)

SELECT * FROM table WHERE table.id = id LIMIT 1; SELECT * FROM table LIMIT 1;

If no parameters provided, will return one random result.

SQL.Collection.first

Type: Query Method
Parameters: limit (optional, default is 1)

SELECT * FROM table ORDER BY table.id ASC LIMIT 1;

SQL.Collection.last

Type: Query Method
Parameters: limit (optional, default is 1)

SELECT * FROM table ORDER BY table.id DESC LIMIT 1;

SQL.Collection.take

Type: Query Method
Parameters: limit (optional, default is 1)

SELECT * FROM table LIMIT 1;

Custom Find Methods

SQL.Collection.select

Type: Statement Starter Method
Parameters: fields (optional)

SELECT fields FROM table, SELECT * FROM table

If no parameters provided, will return all fields.

SQL.Collection.join

Type: Statement Method
Parameters: join type, fields (array), join table

JOIN TYPE joinTable ON field = field

SQL.Collection.where

Type: Statement Method
Parameters: command string (with ?'s for values), values as separate arguments, if an array is passed as a value it will be treated as a where..in (values in array) statement

WHERE field operator comparator, WHERE field1 operator1 comparator1 AND/OR field2 operator2 comparator2, WHERE field IN (value_set)

SQL.Collection.order

Type: Caboose Method
Parameters: fields (can include DESC at end of string, defaults to ASC)

ORDER BY fields

SQL.Collection.limit

Type: Caboose Method
Parameters: limit

LIMIT 1

SQL.Collection.offset

Type: Caboose Method
Parameters: offset

OFFSET 10

SQL.Collection.group

Type: Caboose Method
Parameters: fields

GROUP BY fields

Saving and Retrieving Data

All collection methods must be completed with save or fetch. Save makes changes to the database only. Fetch returns data.

Details on Joins

There are currently two ways to handle joins. The first one is to do it server-side:

  tasks.publish('tasks', function(){
    return tasks.select('tasks.id as id', 'tasks.text', 'tasks.checked', 'tasks.createdat', 'username.id as usernameid', 'username.name')
                .join(['INNER JOIN'], ["usernameid"], [["username", 'id']])
                .order('createdat DESC')
                .limit(100);
  });

SELECT tasks.id as id, tasks.text, tasks.checked, tasks.createdat, username.id as usernameid, username.name FROM tasks INNER JOIN username on usernameid ORDER BY createdat DESC LIMIT 100;

In this method, your client side tasks table would look like this:

  var taskTable = {
    id: ['$number'],
    text: ['$string', '$notnull'],
    checked: ['$bool'],
    usernameid: ['$number']
    name: ['$string']
  };

The data on the client side is a flat view, and so the select statement would look like this:

 return tasks.select('id', 'text', 'checked', 'createdat', 'name', 'usernameid')
   .where("name = ?", newUser)
   .fetch();

SELECT id, text, checked, createdat, name, usernameid FROM task WHERE name = someUsername;

This gives you the benefit of having all of your data in one place on the client side, reducing data redundancy. In order to use this implementation, when doing an insert you need to differentiate between the data present on the client and not on the server. In this case it would be name. This is what it should look like:

    var text = event.target.text.value;
    var user = event.target.user.value;
    tasks.insert({
      text:text,
      checked:false,
      usernameid: user.id
    }, {name: user.name}).save();

The second method is to implement the join client-side. You have to seperatly publish the 2 tables you are joining.

tasks.publish('tasks', function(){
  return tasks.select('id', 'text', 'checked', 'createdat', 'usernameid')
              .order('createdat DESC')
              .limit(100);
});

username.publish('username', function(){
  return username.select('id', 'name')
                 .order('createdat DESC')
                 .limit(100);
});

Your select client-side tables would look like this:

  var taskTable = {
    id: ['$number'],
    text: ['$string', '$notnull'],
    checked: ['$bool'],
    usernameid: ['$number']
  };

  tasks.createTable(taskTable);

  var usersTable = {
    id: ['$number'],
    name: ['$string', '$notnull']
  };
  username.createTable(usersTable);

And your select statement would look like this:

 return tasks.select('tasks.id', 'tasks.text', 'tasks.checked', 'tasks.createdat', 'username.name')
   .join(['OUTER JOIN'], ['usernameid'], [['username', ['id']]])
   .where("name = ?", newUser)
   .fetch();

When inserting or deleting, you do not need to specify what data is being saved on the client versus the server. Please note that client-side joins currently have an issue with inner join so we recommend using outer join whenever possible. This is a limitation of AlaSQL, a package we are relying on.

Additional Notes on SQL

  • Alter table is not available as you cannot alter tables after data has been inserted. Drop the table and recreate it.
  • Underscores are not permitted in table or field names.
Add a custom footer
Add a custom sidebar
Clone this wiki locally