Database Configuration

Database configuration is used for configuring:

  1. permissions to tables and fields,
  2. filters for tables,
  3. authentication definitions,
  4. database write rules.

Following is the bare minimum of a database configuration (third parameter) that makes the contents of the whole database readable.

zazler.db("name", "uri...", { read: "*" })

Permissions

The permissions allow to grant reading and writing rights to tables or specific table fields and can also be used to hide or protect tables or specific table fields with password. If the permission is granted to the whole table then these permissions apply to all table fields. All permission parameters have similar syntax. The names of tables and fields may include an asterisk which stands for zero or more symbols. A minus sign in front of a table name stands for "excluding". No minus sign stands for "including".

Syntax: "[-]tablename[(fieldname1[ ...])] [ ...]"
Example 1: "table1 table2" -- table1 and table2 are readable/writeable/protected or hidden.
Example 2: "* -table2" -- all tables expect talbe2 are readable
Example 3: "table1 -table1(field1 field2)" -- all fields from table1 expect field1 and field2
Example 4: "-*(updated)" in all tables, fields named ''updated'' (you may want hide fields like this)

Read This parameter defines the permissions to read tables and fields of the database. If at least one table field is granted permission to be read then the whole table is not hidden, unless specified separately using the hide parameter.

Write This parameter defines the permissions to write to tables and fields in the database. If at least one table field is granted permission to write then the whole table is not hidden, unless specified separately using the hide parameter. See also write-rules.

Hide This parameter allows to hide tables and fields in the database. It means they are not shown at dashboard. Also, if selecting all fields then hidden are not included. If you are concerned about security, use hide: "*". In this case you must always specify select parameter.

Tables and fields are hidden if permissions to read or write are missing.

Protect This parameter is used to define which tables and fields require username and password to read from them. This parameter needs also auth parameter.

Filters

Filters allow to filter out the content of tables without the user knowing it. One table can have only one filter. Filters are useful for filtering data based on user authentication.

Zazler’s expressions are queries that also work in URLs. All Zazler’s expressions in filters can use a virtual table called "req" (as request).

Queries in database configuration may include any fields in the database, even those that are not readable or writable.

Table This parameter specifies the table that is filtered. It should include text that corresponds to the particular name of a table in the database.

Where This parameter allows to set conditions for which rows should be shown to the user.

Join This parameter can be used to filter which rows should be shown to the user based on conditions from other table or tables, including those that are hidden from users. The fields from joined tables will not be available in the query result. Different types of joins can be used as described under section "Variables".

Order This parameter can be used to sort the returned rows in the specified order by default.

Examples

Show rows from table "comments" where the value of field "IDpresident" is greater than 3 and the value of field "isdeleted" is "false":

 { filters: [ {
    table: "comments",
    where: "IDpresident>3:isdeleted=false()",
    order: "IDComment"
    } ]
  }

Show only those rows from table "presidents" where the values on field "IDpresident" correspond to the values on field "IDpresident" in table "comments" (with alias "c") and if the length of the field "comment" in table "comments" is greater than 0:

 { filters: [ {
      table: "presidents",
      "join.comments@c": "presidents.IDpresident=c.IDpresident",
      where: "len(c.comment)>0"
   } ]
 }

Let an authenticated user see the content of the row in table "mytable" which has field "owner" that corresponds to the user's ID in the field "users.id":

  { filters: [
     { table: "mytable",
       "join.users": "users.id=mytable.owner",
       where: "users.login=req.user"
     } ]
   }

Write rules

Writing to database has to be defined as a write rule. Zazler determines the action based on incoming data, not on direct command to delete, change or add. The purpose of such design is to allow importing remote data without writing a separate program to manipulate the data.

Write rules are defined in the database configuration file parameter "write-rules" separately for every table. There can be many write rules for one table.

A write rule says what to do and on which conditions (the "on" parameter) with which incoming data. Write rule can also be used to define fields that are filled with values irrespective of user’s input or fields that have default values.

Only one write rule applies for every incoming row (if multiple rows were sent, then different write rules may apply for different rows). If no rules are found then the incoming row is ignored (this can be prevented by creating a rule with "true", action: error, message: "No rule…").

Zazler’s expressions are queries that also work in URLs. All Zazler’s expressions in write rules can use two virtual tables - "new" and "req". Write rules can be configured to check for user authentication when writing to database.

Queries in database configuration file may include any fields in the database, even those that are not readable or writable.

Table This parameter specifies the table that the write rule applies to.

Action This parameter determines the command that is sent to the database. Possible commands are "insert", "delete" and "update". Command "error" is used for displaying error to users.

On This parameter includes Zazler’s expression in which case the write rule is applied.

Where This parameter is Zazler’s expression for “update” and “delete” rules.

Message If the "action" parameter is "error" and "on" is true then this parameter defines the error message that has to be returned.

Set This is an object that has field names as parameters with Zazler’s expressions as values that will be inserted into the corresponding field(s) that the user cannot overwrite.

Defaults This is an object that has field names as parameters with Zazler’s expressions as values that will be inserted into the corresponding field(s) if the incoming data is missing the particular field(s).

Examples

Insert values to "mytable" field "id" if the incoming data is missing this value, e.g. if the table "mytable" includes fields "id", "name" and "age" and incoming data is [{age:30,name:"Mike"}] then SQL is:

INSERT INTO mytable (age,name) VALUES (30, 'Mike');

In this case the write rule should be defined as follows:

 { "write-rules": [
    {  table: "mytable",
       on:    "isnull(new.id)",
       action: "insert"
    } ]
  }  

If the incoming data is [{id:1,name:"John",surname:"Doe"}] then SQL is:

UPDATE mytable SET id=1, name="John" WHERE id = 1;

Note that the incoming value of "surname" is omitted in SQL because this field is missing in the "mytable" table. In this case the write rule should be defined as follows:

   { table: "mytable",
     on:    "notnull(new.id)",
     where: "id=new.id"
   }

In addition you could add a "set" object to define in which fields the data about the time of update and about the user who updated it should be stored.

  { table: "mytable", on: "notnull(new.id)", where: "id=new.id",
    set: {
       updatedtime: "now()",
       updatedby: "req.user"
    }
  }

If the user is authenticated then insert into table "mytable".

  { "write-rules": [
    { table "mytable", on: "notnull(req.user)", action: "insert" }
  ] }

Returning row

The parameter "returning" under "write-rules" object for a specific table can be used to get feedback when sending data (using "insert" or "update"), e.g. for finding out an automatically incremented ID of an added row. The returned row will always be in the same format as the incoming data.

When sending data an "insert" or "update" is done followed immediately by a "select" query before the next "insert" or "update".

Select, where and join query rules apply to the SELECT queries in SQL. If data is sent and a parameter "select=id" is in the URL then the query is made only for "id" and the corresponding row is returned.

MySQL and SQLite For MySQL and SQLite databases the configuration syntax for returning row is as follows:

  { "write-rules": [
    { table "mytable", on: "notnull(req.user)", action: "insert",
      returning: "autonum fieldname"
     }
  ] }

that will make the following query in SQL:

SELECT * FROM mytable WHERE fieldname = last_insert_id()

PostgreSQL

For PostgreSQL database the configuration syntax for returning row is as follows:

    returning: "seq fieldname seq_name"

that will make the following query in SQL:

SELECT * FROM mytable WHERE fieldname = currval('seq_name');

Custom expression

Zazler's custom expressions might be useful for doing bulk updates and returning all updated rows:

    returning: expression

Examples

Configuration for returning rows in PostgreSQL table "mytable" including fields "a", "b" and "c":

 { "write-rules": [
    { table: "mytable",
      on: "notnull(a)",
      action: "insert",
      returning: "seq a a_seq"
    } ]
  }

When sending data [{b:"test"}] for the second time the returned row will be [{"a":38,"b":"test","c":null}].

Update all rows in table "people" where the field "age" is the data sent by user and return all rows immediately after updates:

 { "write-rules": [
    { table:     "people",
      action:    "update",
      on:        "notnull(new.age)",
      where:     "age=new.age",
      returning: "age=new.age"
    }]
  }

Virtual tables

Zazler uses two constructs that are called "virtual tables" - "new" that is used for storing sent data and "req" that contains usernames and passwords.

Virtual Table ''"new"''

Virtual table "new" stores one row of data that is being sent. This virtual table is used in write rules.

If the value for "new.field" is not given then the value will be NULL (which is also the case for example if the value is null in the JSON data). This virtual table contains all fields that are being sent (including those that do not exist in the database table where the data is sent to).

Virtual Table ''"req"''

Virtual table "req" contains following fields: "req.user" for storing usernames, "req.pass" for storing plain passwords and "req.passMd5" for storing md5 hash values of passwords. These fields can be used in filters, authentication control and write rules.

If the user is not authenticated then data manipulation is interrupted and basic authentication prompt is returned. If there is a need for authentication control but no need for returning authentication prompt then fields "req.userN" and "req.passMd5N" have to be used.

Authentication

Authentication works using HTTP basic authentication. Authentication control is done in order to check whether the username and password that were sent are eligible or not. The SQL query response should have at least one row in order to confirm a user. If the response doesn’t have any rows then username and password will be asked again.

Authentication is initiated only if filters or write rules contain fields "req.user", "req.pass" or "req.passMd5" or if the table or field is protected. Authentication is not initiated for tables that don't have this defined, i.e. they are open for everybody to read from. Database configuration file should include protect: "*" if all tables have to be protected with a password.

The authentication control parameter "auth" in database configuration is a YAML object where "table" parameter is required and other parameters can be used that are supported in URL query.

Basic authentication realm can be set by parameter "realm".

Queries in database configuration file may include any tables and fields in the database, even those that are not readable or writable.

Table This parameter defines the table that includes usernames.

Where This parameter defines the condition how authentication control works.

Join Joins can be used for retrieving values from other tables.

Examples

Look inside the table "users" and check if the field "login" contains the inserted username and if the field "pw" contains md5 hash value of the inserted password.

  { realm: "My database secure zone",
    auth: {  table: "users",   where: "users.login=req.user:users.pw=req.passMd5" }
  }

Index page

The default index page in Zazler is "_schema.dashboard". An "index" parameter in database configuration can be used to configure another page to be served as index page.

Example

Make http://localhost/example/presidents.html work as an index page when http://localhost/example/ is requested:

  { index: "presidents.html" }