Mapping

Defining entitites

As with all RingoJS storage implementation entities are defined by calling the store's method defineEntity(), passing the name of the entity as first argument. In addition SQLstore requires an object containing the mapping definition as second argument. The purpose of this object is to define the type of each property and in which column the property is stored in. To start right away, in it's simplest form a mapping definition looks like this:

var Author = store.defineEntity("Author", {
    "properties": {
        "name": "string"
    }
});

Table Name Definition

Note that the above definition doesn't specify a table, the ID column or the column the "name" property is stored in, so relying on defaults provided by SQLstore.

If no table name is defined, SQLstore will create or expect a table with the same name and capitalization as the entity name. The above example would therefor create or expect a table named "Author" in your database. You can override this by adding a property "table" to the mapping definition:

var Author = store.defineEntity("Author", {
    "table": "t_author",
    "properties": {
        "name": "string"
    }
});

ID Definition

Our example lacks a definition for the unique ID too, so SQLstore will create a column named "id" in this table. Again this can be overridden:

var Author = store.defineEntity("Author", {
    "table": "t_author",
    "id": {
        "column": "author_id"
    },
    "properties": {
        "name": "string"
    }
});

Note that the ID is not part of the "properties" definition. IDs are handled differently than normal properties, although all entity instances will have an "id" property. For transient entity instances this property is null until the instance is persisted.

ID generation

SQLstore has two different ways of generating IDs for persisted entity instances: auto-incrementation of the primary key value by the underlying database or explicitly using a sequence. The default is auto-incrementation if the database supports it, you can however explicitly set the autoIncrement property to true:

"id": {
    "column": "author_id"
}

// is the same as
"id": {
    "column": "author_id",
    "autoIncrement": true
}

Note that Oracle doesn't support auto-incrementation, so in this case you'll need to go with sequences. If the ID mapping contains a sequence name, SQLstore will utilize it for retrieving ID values whenever an entity instance is persisted. Note that if the sequence doesn't exist SQLstore will create it if the underlying database supports sequences (which MySQL doesn't):

"id": {
    "column": "author_id",
    "sequence": "seq_author_id"
}

Properties

Our example entity mapping definition contains the simplest way of defining properties: specify the name of the property and it's type. The following types are supported by SQLstore:

• binary
• boolean
• byte
• character
• date
• double
• integer
• long
• short
• string
• time
• timestamp
• text

In addition SQLstore supports the json and jsonb datatypes if a PostgreSQL database version supporting theses data types is used.

Data type options

Column mappings can be further defined using optional properties. Regardless of the data type you can set unique (defaults to false) and nullable (defaults to true) in any column mapping specification.

SQLstore mapping supports different options for various datatypes, again depending on the underlying database used. Options that are not supported by the database are ignored.

Datatype	H2	PostgreSQL	MySQL	Oracle
binary	length
boolean
byte
character	length	length	length	length
date
double	precision/scale	precision/scale	precision/scale	precision/scale
integer			length	length
long			length	length
short			length	length
string	length	length	length	length
text	length	length
time
timestamp

// length limit for a string mapping
"name": {
    "column": "author_name",
    "type": "string",
    "length": 200
}

// non-null column mapping
"name": {
    "column": "author_name",
    "type": "string",
    "length": 200,
    "nullable": false
}

One-To-One Mappings

Besides properties entity mappings can contain any number of one-to-one mappings (aka "object mappings"). For each of these mappings you need to at least define the mapped entity type. So the minimal version of a one-to-one mapping looks like this:

var Book = store.defineEntity("Book", {
    "properties": {
        "author": {
            "type": "object",
            "entity": "Author"
        }
    }
});

Again, when creating the table for the above "Book" entity SQLstore would create a column "author" to store the ID of the author of a Book instance. As with normal properties, this can be overridden:

var Book = store.defineEntity("Book", {
    "properties": {
        "author": {
            "type": "object",
            "entity": "Author",
            "column": "book_f_author"
        }
    }
});

For those rare cases where the object mapping uses a different identifier than the ID of the mapped entity, you can also specify the property whose value should be stored in the book's column book_f_author by adding the property containing the desired key column in a foreignProperty property:

var Book = store.defineEntity("Book", {
    "properties": {
        "author": {
            "type": "object",
            "entity": "Author",
            "column": "book_f_author",
            "foreignProperty": "author_ssn"
        }
    }
});

Note that one-to-one mappings are always loaded lazily, so when retrieving a Book instance the author property would contain only an Author instance without any data (besides it's ID). The values of the author would be retrieved using a second SQL query when you access a property of it for the first time.

One-To-Many Relations

SQLstore also supports one-to-many relations (aka "collections"). A collection definition consists of two properties: type (which is always collection) and query:

var Author = store.defineEntity("Author", {
    "properties": {
        "name": "string",
        "books": {
            "type": "collection",
            "query": "from Book where id = :id"
        }
    }
});

When constructing the collection the query will be translated to SQL and used to retrieve the IDs of the Objects contained in the collection (more about queries in Ringo SQLstore).

Note the :id in the query: this is a "named parameter", and by default you can reference all properties of the instance containing the collection this way.

A "collection" nearly behaves like a normal Javascript array. The major difference is that you can't access an object in a collection using the index position notation collection[idx]. Instead you need to call a collection's method get(idx) to retrieve an object at a given index position. But collections have a length property and you can iterate over using either forEach, for each or for (let idx = 0; idx < collection.length; i += 1). You can also use all the other iteration methods like map, filter, some etc.

Collections are effecively read-only, but can be rebuild at any time by calling invalidate(). The effect of this is that the ID index of a collection is removed from the store's cache, and rebuilt at next access.

// open a new transaction
store.beginTransaction();
try {
    // create new book instances and save them
    (new Book({…})).save();
    (new Book({…})).save();
    // invalidate the author's books collection. other threads will still
    // see the old version of this collection, but inside this transaction
    // it will contain the above created books.
    author.books.invalidate();
    // commit the transaction - this will make the changes done visible
    // to other threads, including the invalidated collection
    store.commitTransaction();
} catch (e) {
    store.abortTransaction();
}

Many-To-Many Relations

In addition to one-to-many collections SQLstore also supports many-to-many mappings. To use these SQLstore expects a relation entity to be defined in the model:

store.defineEntity("RelAuthorBook", {
    "table": "t_relation",
    "author": {
        "type": "object",
        "entity": "Author"
        "column": "rel_author"
    },
    "book": {
        "type": "object",
        "entity": "Book",
        "column": "rel_book"
    }
});

So basically this relation entity consists of two properties "author" and "book", which in our example are one-to-one mappings. Note that SQLstore will add an additional "id" column if you don't specify one since it currently doesn't support combined keys. With this relation entity in place it's easy to create a many-to-many collection using a join query:

"books": {
    "type": "collection",
    "query": "from Book b join RelAuthorBook r on b.id = r.book where r.author = :id"
}

Aggressive Collection Loading

By default collections are loaded lazily, which means SQLstore will retrieve the list of entity IDs when first accessing a collection property or method, and load the entities themselves one by one when a property of them is accessed. Depending on the collection size this can lead to a massive amount of SQL statements (imagine iterating over a huge collection), therefor you can define eager or aggressive loading of collections by using a different query:

"books": {
    "type": "collection",
    "query": "select * from Book where author = :id"
}