Data Sources

Data sources provide tabular data, made of columns and rows, to the user interface objects. A data source is identified by a unique id, and can be shared by several objects, in one or more projects.

The following objects can display data from a data source, setting their Data Mode attribute to datasource and their Datasource ID attribute to the data source id:

• Table, including its edit popup, the foreign key columns and the select fields of its columns
• Form and the select fields of its items
• List
• Tree
• Map, for the markers
• Calendar, for the events
• Chart, for each chart set
• Tags
• Field
• Radio Group

Objects like the Table and the Form can also insert, update and delete rows of the data source.

There are two kinds of data sources:

• SQL data sources: bound to a table of HSYCO's database, or of another JDBC database, with the dataSourceSQL() and dataSourceOption() functions. HSYCO automatically builds and executes the SQL statements to query and modify the data
• custom data sources: their data is provided by your code, on the server or in the client

The Status Browser lists the data sources, with the time of their last refresh and their options.

Refresh

When the data of a data source changes, call dataSourceRefresh(), in Java or JavaScript, to notify the clients. The objects that are displaying the data source will then request the data again.

The refresh can be global, for all the clients, or limited to a single session, passing the session id as the first parameter.

Insert, update and delete operations that complete successfully automatically trigger a global refresh of the data source, or a session refresh if the data source's refresh option is set to false.

Custom Data Sources

Any data source id that is not bound to an SQL table with dataSourceSQL() is a custom data source. There is no need to declare it: when an object requests its data, HSYCO calls the data source callbacks of your code.

Server Callbacks

Custom data sources are implemented with the following callbacks, available in Java plugins and in JavaScript:

Callback	Called when	Java	JavaScript
dataSourceQuery	an object requests the data	Java	JavaScript
dataSourceInsert	an object inserts new rows	Java	JavaScript
dataSourceUpdate	an object modifies existing rows	Java	JavaScript
dataSourceDelete	an object deletes rows	Java	JavaScript

The callbacks are called for every custom data source, so they should check the data source id, and return null for the data sources they don't handle. In Java, you can also use the annotations, like @dataSourceQuery("rooms"), to have a method called only for a specific data source id.

All the Java callbacks are called, and the first non-null result is used. The JavaScript callbacks are called after the Java ones, and their result is only used when no Java callback returned a result.

If no callback returns a result, the object receives a "not found" error.

You only need to implement the callbacks for the operations you want to support. For example, a data source with only the dataSourceQuery callback is read-only.

A callback can also be used together with an SQL data source:

• the callback.before and callback.after options call the insert, update and delete callbacks before or after the SQL statements
• the query.callback option calls the dataSourceQuery callback before the SQL query; if the callback returns null, the SQL query is executed

See dataSourceOption() for all the options.

Query Results

The dataSourceQuery callback returns a JSON object with the columns and rows keys. Keep in mind that:

• every row must have exactly one value for each returned column, in the same order. If a row has a different number of values, all rows are discarded
• the columns parameter lists the columns requested by the object, or * for all columns. Returning more columns than requested is allowed, and you can always return all the columns
• objects that modify rows, like the Table and the Form, need a key column, with "key": 1. Always return the key column, even when it is not requested
• set the nullable and readonly column properties to 1, not true
• the query.limit option, and the row limits of the objects, are not applied to custom data sources: the callback should return all rows
• columns whose name starts with _ can be used by the objects without being returned by the data source, for example to pass additional fields to the insert and update callbacks

The insert, update and delete callbacks return the number of processed rows, in the inserted, updated and deleted keys. A result of zero processed rows is reported as an error by the object.

Example

The following EVENTS code implements a read-only data source named rooms, that lists the temperature of two rooms, and is refreshed when the temperatures change:

function dataSourceQuery(session, userid, dataSourceId, columns) : {
  if (dataSourceId != "rooms") return null;
  return {
    columns: [
      { name: "id", type: "varchar", key: 1 },
      { name: "label", type: "varchar" },
      { name: "temp", type: "decimal" }
    ],
    rows: [
      ["kitchen", "Kitchen", ioGet("k.temp1")],
      ["living", "Living Room", ioGet("k.temp2")]
    ]
  };
}

IO k.temp1 : { dataSourceRefresh("rooms"); }
IO k.temp2 : { dataSourceRefresh("rooms"); }

The same data source in a Java plugin, using an annotation:

@dataSourceQuery("rooms")
public static JSONObject roomsQuery(String session, String userid, String datasourceid, String[] columns) {
  JSONArray cols = new JSONArray()
    .put(new JSONObject().put("name", "id").put("type", "varchar").put("key", 1))
    .put(new JSONObject().put("name", "label").put("type", "varchar"))
    .put(new JSONObject().put("name", "temp").put("type", "decimal"));
  JSONArray rows = new JSONArray()
    .put(new JSONArray().put("kitchen").put("Kitchen").put(ioGet("k.temp1")))
    .put(new JSONArray().put("living").put("Living Room").put(ioGet("k.temp2")));
  return new JSONObject().put("columns", cols).put("rows", rows);
}

Then set the Data Mode attribute of a Table object to datasource, and its Datasource ID attribute to rooms.

Client Data Sources

Custom data sources can also be implemented in the browser, in the project's index.js file, using the Client JavaScript Extension API. Client data sources are local to each client: their data is not shared with other clients, and the access control and scope rules of the server don't apply.

When an object requests a data source, the client first calls the dataSourceQuery, dataSourceInsert, dataSourceUpdate and dataSourceDelete functions defined in index.js, with the same results of the server callbacks. If the function is not defined, or returns null, the request is sent to the server.

After a successful insert, update or delete, the data source is automatically refreshed in the client. If the data changes for other reasons, call dataSourceRefresh(dataSourceId) in index.js to refresh it. Note that this function only refreshes the data source in the current client.

var favorites = {
  columns: [
    { name: "id", type: "varchar", key: 1 },
    { name: "name", type: "varchar" }
  ],
  rows: []
};

function dataSourceQuery(dataSourceId, columns) {
  if (dataSourceId != "favorites") return null;
  return favorites;
}

function dataSourceInsert(dataSourceId, columns, data) {
  if (dataSourceId != "favorites") return null;
  data.rows.forEach(function (row) {
    favorites.rows.push([String(Date.now()) + favorites.rows.length, row[columns.indexOf("name")] || ""]);
  });
  return { inserted: data.rows.length };
}

function dataSourceDelete(dataSourceId, data) {
  if (dataSourceId != "favorites") return null;
  var count = favorites.rows.length;
  favorites.rows = favorites.rows.filter(function (row) {
    return data.rows.indexOf(row[0]) == -1;
  });
  return { deleted: count - favorites.rows.length };
}