UpviseJS Database & Storage Guide

UpviseJS provides the following Database API and Services:

Defining a Table Data Structure

Use the Config.tables array property

Config.tables["mytable"] = "id;name;date DATE;price REAL;status INTEGER";
Config.tables["anothertable"] = "..."

How Database synchronization work

Reserved Column Names

id and owner column names (string) should be defined for each table. id specifies the primary key and owner allows you to defined record level access rights

Primary Keys

Because UpviseJS is designed to work offline and later sync data in the cloud, it is essential for primary keys to be unique in a distributed world. So when you do not specify a id primary key, the framework generates a random GUID string, for example "753643D05EB714E7993BF1E2B106F3", which is a random number based on your user token and device id.

Automatic Table Schema Upgrade

UpviseJS supports automatic database table schema upgrades as part as the application upgrade while preserving the existing data based on the following rules:

When you upgrade the application table schema, always increase the Config.version statement and then redeploy your app.
Config.version = "1";
Config.tables["invoices"] = "id;name;date DATE;price REAL;status INTEGER";

// In this new version
// 1. new paid column is added to invocies  with a default value of 0
// 2. status column is deleted from invoices table
// 3. ew leads table
Config.version =  "2";
Config.tables["invoices"] = "id;name;date DATE;price REAL;paid DATE";
Config.tables["leads"] = "id;name;source";

Making Select Queries

To return a single record matching its primary key for a given table, use the Query.selectId(table, id) method. It returns a javascript object containing all column values. Record value is accessible as object properties. string columns are mapped to javascript string and INTEGER, REAL and DATE to javascript numbers. 

Config.tables["leads"] = "id;name;date DATE"
....
var lead = Query.selectId("leads", myid);
if (lead != null) {
    var id = lead.id;
    var name = lead.name;
    var date = lead.date;
}

To return a list of records matching a where clause, use Query.select(table, columns, where, orderby). The columns parameter is a string containing the list of columns to be returned separated by a semi-column ;. where is standard SQL WHERE clause. The return value is a javascript array of objects, each object representing a record. Only the specified columns are be returned for each object.

Config.tables["leads"] = "id;name;status INTEGER;date DATE"
....
// Returns all leads
var leads = Query.select("leads", "id;name");

// Return all leads sorted by name
var leads = Query.select("leads", "id;name", null, "name");

// Return all leads older than the current month where status=0 and sorted by date
var monthStart = Date.month();
var leads = Query.select("leads", "id;name", "date<{monthStart} AND status=0", "date");
for (var i = 0; i < leads.length; i++) {
    var lead = leads[i];
    List.addItem(lead.name, "view");
}

Inserting Data

Use the Query.insert(table, values) method. values is a javascript object containing the values for the column to insert.

It returns the primary key of the newly inserted records. 
Config.tables["leads"] = "id;name;date DATE"
...
var values = new Object();
values.name = "John";
values.date = Date.now();
var id = Query.insert("leads", values);

// or a shorter version in one line using JSON notation
var id = Query.insert("leads", {name:"John", date:Date.now()});

// Insert an empty record
var id = Query.insert("leads", {});
Inserting a null values will result in a exception.

Updating Data

To update one field of one record, use Query.updateId(table, recordid, columnName, value)

var id="someid";
Query.updateId('leads', id, 'name', "John");

To update multiple columns for multiple records, use Query.update(table, values, where)

var groupid = "some id";
Query.update("leads", {name:"Hello", status:0}, "groupid={groupid}");

Working with Dates

To store date in a table use the DATE type when defining the column in the Config.tables property.

Config.tables["mytable"] = "id;name;startdate DATE;column2"

date values are stored internaly in the database in epoch format (number of milliseconds since Jan 1st 1970 UTC.). When you query a record containing a date column, the value in javacript is a number in Epoch format. You can then manipulate it by creating a Date object in Javascript using the new Date(millisecond) constructor.

....
Config.tables["leads"] = "id;name;date DATE"
....
var lead = Query.select("leads", myid);
if (lead != null) {
    var milisec = lead.date;
    var date  = new Date(milisec);
    // now use date as a regular Javascript data object
    App.alert(date.toLocaleString());
}

In addition to using the standard javascript Date class to manipulate dates, UpviseJS adds some convience static methods to the Date class to easily format and work with dates. 

Config.tables["leads"] = "id;name;date DATE"
....
var lead = Query.select("leads", myid);
// Compute the time duration since the lead was created
var nbMinutes = (Date.now() - lead.date)/Date.MINUTE;    
App.alert("This lead was created" + Format.duration(nbMinutes) + "ago");

Useful Convenience functions

you can use Query.count(table, where) to return the number of record as a number

....
var openCount = Query.count("Tasks.tasks", "status=0");
var completedCount = Query.count("Tasks.tasks", "status=1");
List.addItem("Open Tasks", "viewOpenTasks()", {count : openCount});
List.addItem("Completed Tasks", "viewCompletedTasks()", {count : completedCount});
List.show();

Managing Record Access rights

UpviseJS defines 2 levels of users : standard users and managers/admin.

Access other application tables

You can access all other tables in all other application by prefixing the table name by the applicationid. For example, to select the all records from the companies table of the contacts app, perform a query on the table named "contacts.companies" 

var companies = Query.select("contacts.companies", "id;name;email", null, "name");
var openTasks = Query.select("tasks.tasks", "id;name;duedate;owner", "status=1", "duedate DESC");

System Tables

Upvise has a set a predefied system tables, prefixed by system keyword: