Author: hugo

In some cases, after running one action on the component, you’d like to execute another action on the same or different component immediately, just like a trigger in database. For example, after a new member has registered, which corresponds to the insert action on the member table, you’d like to run another insert on table sponsor, to credit who is the reference of the new member.

Trigger is just an easy way to chain existing actions of different components together. Those actions can be either RESTful ones or the ones you have developed.

To use trigger, define the following nextpages in component.json:

{
  ...
  "nextpages" : {
    "insert" : [ 
      {
        "model"  : "Sponsor",
        "action" : "insert", 
        "relate_item" : {"member_id":"member_id", "sponsor_id":"sponsor_id"},
        "manual" : {"credits" : 30 }
      },
      { ... }
    ],
    "topics" : [
    ]
  }
  ...
}

So to trigger an action, create an object with keys

• model: the name of the other Model
• action: the action on the other Model
• relate_item: defining a column mapping between the two tables. Their values will be passes correspondingly.
• manual: you can manually pass some variables.

As you could see, one action could trigger multiple Models.

In Genelet, the main data resulted is an array. If relate_item exists, Genelet will scan all the items in the array, and run one trigger for each item. The data returned from the trigger will be saved in the new key named by model (e.g. “Sponsor”) in the item. In the Model class, this is achieved in method call_next.

If relate_item does not exists, the other model will take only the original query variables as inputs. Then the resultant data are assigned to the key named by model (e.g. “Sponsor”) in OTHER, which is further delivered to the client in “relationships”. In the Model class, this is achieved in method call_once. Please consult each language manual for specific implementations of call_next, call_once and OTHER.

For relational database, a search usually requires multiple table joins. You can achieve this by using

{
...
  "current_tables" : [
    {"name": "sales",    "alias": "s"},
    {"name": "customer", "alias": "c", "type": "INNER", "using": "customer_id"},
    {"name": "product",  "alias": "p", "type": "INNER", "using": "product_id"},
    {"name": "coupon",   "alias": "n", "type": "LEFT", "on": "p_product_id=n.product_id"}
  ],
  "topics_hash" : {
    "s.sales_id": "sales_id",
    "s.customer_id": "customer_id",
    "c.firstname" : "firstname",
    "c.lastname" : "lastname",
    "p.title" : "title",
    "p.price" : "price",
    "n.discount" : "discount"
  },
  ...
}

 

Step 1: Add current_tables to component.json

This is a list of joint tables which are represented as objects, having the following key-values:

• name: the table name
• alias: optional, the alias of the table
• type: one of join types – INNER or LEFT
• using: to use USING clause in joining the table
• on: to use ON clause in joining the table

The first item in current_tables is for the main table, so it has only name and alias.

 

Step 2: Define topics_hash

By default, the resultant fields will use their table names (or table alias) as prefixes. You can assign labels to these cumbersome fields, by topics_hash.

 

Example

The above current_tables and topics_hash will result in the follow SQL statement:

SELECT s.sales_id AS sales_id, s.customer_id AS customer_id,
  c.firstname AS firstname, c.lastname AS lastname, 
  p.title AS title, p.price AS price, n.discount AS discount
FROM sales s 
INNER JOIN customer c USING (customer_id)
INNER JOIN product p USING (product_id)
LEFT JOIN coupon n ON (p.product_id=n.product_id)

 

The advantage to use this JOIN solution over an overridden topics is that you can continuously use the built-in fks, pagination and trigger without any extra programming.

The following variables are used in the Model class. You can setup their values in component.json.

{
...
  "current_table": "product",
  "current_key"  : "product_id",
  "current_id_auto" : "product_id",
  "key_in"       : {"table1" : "fk", "table2" : "fk"},
  "insert_pars"  : ["title", "description", "price", "category_id"],
  "topics_pars"  : ["product_id", "title", "price", "category_id"],
  "edit_pars"    : ["product_id", "title", "description", "price", "category_id"],
  "update_pars"  : ["product_id", "title", "description", "price", "category_id"],
  "empties"      : ["description"],
...
}

Here is the list of attributes for the database table.

• current_table: the table name.
• current_key: the primary key (PK). If it consists of several fields,  assign them to current_keys as an array.
• current_id_auto: if there is an auto increment field, put it here.
• key_in: if to delete an item using the PK, and if this PK exists in other tables as foreign key, Genelet will stop the deletion. This object defines those tables and the column names of the PK there.

Here is the list of attributes for RESTful or CRUD.

• insert_pars: array, defining which fields will be inserted.
• topics_pars: array, defining which fields will be searched for in the “list_all” RESTful request.
• edit_pars: array, defining which fields will be selected for specific item.
• update_pars: array, defining which fields will be updated.
• empties: array. During update, fields without input will be untouched. By defining those fields in empties, they will be force to be NULL instead.
• insupd_pars: array, defining unique column names, whose existence will make Genelet’s special verb insupd, to be update, while non existence will make it to be insert. 

These are names for the query variables that client use to send as queries. For example sortby is the variable that controls how the returned data are sorted. A topics request with sortby=firstname will expected the data sorted by customer’s first name.

For topics

• sortyby: default “sortby”, to define which field would be used for sorting the return data.
• sortreverse: default “sortreverse”, to define if the sorting is reversed.
• fields: default “fields”, to ask for specific columns’ values in the table.

 

Pagination

If rowcount is found in the topics request, Genelet will turn on pagination and display searched results in pages:

• rowcount: default “rowcount”, to define how many record in each page
• totalno: default “totalno”, to tell how many records totally in the table
• pageno : default “pageno”, to search specific page by number
• maxpageno: default “maxpageno”, to tell the total pages in the table.

This variable should be setup in component.json for pagination:

• total_force: to decide if the total number of records should be calculated. Default 0, means no calculation.

 

The web flow of running pagination is:

1. Sending a request with rowcount=20 , or manually adding it in Filter, will turn on pagination.
2. If no pageno is sent, pageno=1 will be assumed as the default.
3. If total_force=1 in the model, the total number of rows and the total number of pages, totalno and maxpageno respectively, will be calculated for pageno=1.
4. Use pageno=2 for the second page, and so on.
5. sortby should be specifically defined, since the order of rows returned would depend on how they are sorted.
6. If totalno is constantly changing in real-time, you should arrange pagination in such a way that the first page, case 2 above, could be clicked often, so as to refresh totalno and maxpageno.

There are many situations in database development where Row Level Security (RLS) becomes a security concern. The fks block is introduced to solve the problem.

{
  "fks" : {
    "member" : ["purchase_id", "purchase_sha1", "item_id", "item_sha1"],
    "vendor" : [ ... ]
     ...
   }
}

Here is a simple SQL data model for a shopping cart website:

• table member for registered members, with the primary key (PK) member_id
• table product for products with PK production_id
• table purchase for members’ purchase history with PK purchase_id
• table item for the product list in each purchase with PK item_id

The SQL statements to create the tables in MySQL are:

create table member (
  member_id int not null auto_increment primary key,
  email varchar(32) not null,
  passwd varchar(32) not null,
  member_name varchar(255) not null,
  unique key (email)
);

create table product (
 product_id not null auto_increment primary key,
 product_name varchar(255),
 unit_price double
);
 
create table purchase (
  purchase_id int not null auto_increment primary key,
  member_id int not null,
  purchase_time datetime not null,
  total_price double not null,
  index (member_id)
);

create table item (
  item_id int not null auto_increment primary key,
  purchase_id int not null,
  product_id int not null,
  number_product int default 1,
  index (product_id),
  index (purchase_id)
);

They are mapped to 4 components Member, Product, Purchase, Item in your Genelet project.

 

Problem

Now, a member logs in. She is looking for her purchase history. This is the RESTful action topics on Purchase which is granted to the member role in ACL. To make sure that she only views her own purchase history, not other members, you assign member_id as a constraint in the search. Since member_id is decrypted from the login cookie, its integrity is guaranteed.

Up to now, the authentication, ACL and the constraint work well. Next, she’d like to look for the list of products in a specific purchase. Normally, this is implemented by the RESTful topics on Item. Client sends you purchase_id in URL, which you could use as a constraint in the Item search. The SQL would look like: SELECT * FROM item WHERE purchase_id=?

Now problem arises. A malicious user could send any purchase_id. It she sends one belong to somebody self, she would see that person’s purchase history! This is a typical case for RLS, discussion by Oracle here.

There are two solutions. The first one is to add member_id to the Item table, so you can use both it and purchase_id as constraints. Because member_id is retrieved from the login cookie, the searched results are guaranteed to belong to her. However, because it requires the redundant member_id, the table design is against the Normalization rule of RDMS.

The second solution is to join table Purchase. So you can use Purchase‘s member_id and Item‘s purchase_id as the constraints. While the solution looks to be trivial in the current example, for a large database it could become tedious and easily mistaken because of more deeper relationships.

 

Genelet’s Solution

It starts with a table containing the user id. In the above example, this is Purchase.

For every record searched from topics on the table, you digitally signed the PK with user id and a secret word (and optionally, a time stamp). In the example, this is to calculate SHA1 of purchase_id with  member_id.

When the user navigates to the next down-level table that has no user id, e.g. Item, you verify if the searched key has the correctly signed signature. You only let is continue if the signature is correct. In the example, you check if the SHA1 of purchase_id is correct.

Genelet make the whole flow automatically from up levels to down levels. To use it, define the fks (Foreign Keys):

"fks" : {
  "ROLE" : ["in_id", "in_sha1", "out_id", "out_sha1"],
  ...
}

where ROLE is a login role name; in_id is key to use as constraint, usually a FK in the current table. in_sha1 is its digital signature, convoluted with user id, secret word and time stamp. The client sends both in_id and in_sha1 in request. out_id is usually the PK of the current table.

After the search is finished, Genelet internally scans out_id in the output data, and calculate the signature out_sha1. So out_id and out_sha1 can be used new incoming contraint parameters for the next down-level pages.

The signature process could be repeated all the way down to the last bottom-level table.

Note that while in_id and out_id are table’s field names which you can’t change, you are free to choose names for in_sha1 and  out_sha1.

 

Special Cases

If user id is used as constraint, you don’t need its signature because it is already proofed in the login cookie. So fks will look like:

"ROLE" : ["role_id", false, "out_id", "out_sha1"]

If you don’t need out_id, use:

"ROLE" : ["in_id", "in_sha1"]

If you only want to generate out_id:

"ROLE" : [false, false, "out_id", "out_sha1"]

The actions block consists of key-values pairs with key the action names:

  "actions" : {
    "insert" : {
      "groups"  : ["role1", "role2", ...], 
      "aliases" : ["name1", "name2", ...], 
      "validate": ["field1", "field2, ...], 
      "options  : ["no_db", "no_method"],
      "upload"  : {
        "html_field1" : {"filename1", "directory1"},
        "html_field2" : {"filename2", "directory2", "renamed2"}
      }
    }
    "topics" : { ... }
    ... 
}

Here is their explanations:

• groups: array of roles,  which are allowed to run this action i.e. Access Control List (ACL).
  • Those roles who are not permitted to run the action will trigger error 404.
  • Roles having administrative privilege can run any action, not restricted by ACL.
• aliases: array of alias names that the action may have.
• validate: array of request variables which must exist. If empty, it will result in failure in the database operation.
• options: may contain no_db or no_method. Genelet opens a database handler for each request, which comes with costs in speed and resource. If an action does not need database, just add no_db. If an action does not need to run the model, add no_method.

upload

Let’s start with an example. On HTML page, there are two files to upload, one image and one music. The form that is responsible for the uploading has the web action insert:

<input type=hidden name="action" value="insert" />
Title is: <input type=text name="title" />
...
Your image: <input type=file name="field1" />
Your MP3: <input type=file name="field2" />

 

To receive the uploading files, there needs to be an upload object in insert which follows:

"upload" : {
  "field1" : ["image_file", "/home/www/ht_docs/images"],
  "field2" : ["music_file", "/home/www/ht_docs/music", "kids.mp4"]
}

 

where the object keys are HTML input’s name, values are 3-element arrays.

After the server has received the uploading, the exact file names are saved in the incoming variables image_file and music_file, in the same way that the title input is saved in the incoming variable title. (Note that the incoming variables are collected in a hash map, named ARGS, which is language-dependent.)

The second element defines the directory for the file. In the above example, the image file is put under /home/www/ht_docs/images, and the music under /home/www/ht_docs/music.

You may override the server-assigned file name by the third element, like kids.mp4 in the music case. The element is optional.

Contents:

1. Actions
2. Foreign keys: fks
3. Query Names and Pagination
4. Model Variables
5. Triggers: nextpages

 

Each component has a configuration file, component.json, to define the initial parameters for the Model and Filter classes. It is located in the same directory as the class files.

This is an example:

{
  "actions" : {
    "topics"  :{"groups": ["public"]},
    "startnew":{"options":["no_method", "no_db"]},
    "insert"  :{"validate":["question_id","choice_text","votes"]},
    "delete"  :{"validate":["id"]},
    "edit"    :{"validate":["id"]},
    "update"  :{"groups":["public"],"validate":["id"]}
  },
  "current_table":"polls_choice",
  "current_key"  :"id",
  "insert_pars"  :["question_id","choice_text","votes"],
  "update_pars"  :["question_id","choice_text","votes","id"],
  "topics_pars"  :["question_id","choice_text","votes","id"],
  "edit_pars"    :["question_id","choice_text","votes","id"],
  "current_id_auto" :"id"
}

The actions and fks blocks are used for Filter,  and the rest blocks for Model.