Creating a new LabxDB database

This tutorial uses the example of the Order database to detail the design of a new database in LabxDB.

The Order database aims to help tracking ordering (purchases) in a laboratory. Multiple members of the laboratory can enter new items to purchase while members (same members or not) order them. Since in most labs, members who need new items don’t often have the authorization to order, the database keeps track of which items are needed and the current state of orders. It also tracks spending over time.

The files created during this tutorial are:

Purpose	Path
SQL schemas	contrib/databases/sql/create_order_tables.sql
Back-end	handlers/order.py
Front-end	static/js/order/board.js
Front-end	static/js/order/addon.js

Creating the tables in PostgreSQL

Each database is stored in a different schema. For this database, the purchase schema will be used.

CREATE SCHEMA purchase;

The main table for this database contains the items to be bought. The item_id column is required in each LabxDB table to identify uniquely each record. Other columns do not have any restriction.

CREATE TABLE purchase.item (
    item_id serial not null,
    item varchar(255),
    item_ref varchar(255),
    item_size varchar(255),
    provider varchar(255),
    provider_stockroom boolean,
    quantity integer,
    unit_price numeric(8,2),
    total_price numeric(8,2),
    status varchar(50),
    recipient varchar(50),
    date_insert date default CURRENT_DATE,
    date_order date,
    manufacturer varchar(255),
    manufacturer_ref varchar(255),
    funding varchar(255),
    PRIMARY KEY (item_id));

Some columns are stored as text but only a limited number of options are available to users (with a drop-down menu; see below). To store these options, a dedicated option table is required. All 3 columns are required.

CREATE TABLE purchase.option (
    option_id serial not null,
    group_name varchar(50) not null,
    option varchar(50) not null,
    UNIQUE (group_name, option),
    PRIMARY KEY (option_id));

Finally, permissions to edit the database are given to the lab user used by the back-end.

GRANT USAGE ON SCHEMA purchase TO lab;
GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE purchase.item TO lab;
GRANT SELECT,UPDATE ON TABLE purchase.item_item_id_seq TO lab;
GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE purchase.option TO lab;
GRANT SELECT,UPDATE ON TABLE purchase.option_option_id_seq TO lab;

Back-end

BaseHandler

The back-end defines a BaseHandler class containing the database meta-information in common between all handlers. All handlers inherit this class.

The class variables defined in the BaseHandler are:

Variable	Description
name	The main name of the database
schema	The name of the schema in PostgreSQL storing the data
levels	Level(s) used by the handler (identified by order in level_infos)
level_infos	Description of each level with `label`, `url` and `column_id` (serial ID) fields
column_infos	Description of columns (see table below)
column_titles	List of columns used as title in the tree view
default_search_criterions	Default search criterions applied after client page load
default_sort_criterions	Default sort criterions applied after client page load
default_limits	Default limits applied after client page load
columns	List of displayed columns
form	Definition of edit form (see below)
board_class	The front-end board class to use for board view
form_class	The front-end form class to use for form view

Applied to the Order database, OrderBaseHandler inherits from BaseHandler:

class OrderBaseHandler(base.BaseHandler):
    name = 'order'        # The main name of the database
    schema = 'purchase'   # The name of the schema in PostgreSQL storing the data

    levels = [0]                       # This database has a single level `item`.
    levels_json = json.dumps(levels)   # Metainformation as JSON. Used in the template to communicate to the client.

    level_infos = [{'label': 'Order', 'url': 'order', 'column_id': 'item_id'}] # Each level `label`, `url` and `column_id`.
    level_infos_json = json.dumps(level_infos)

    column_infos = [{'item_id': {'search_type': 'equal_number', 'gui_type': 'text', 'required': True, 'label': 'Order ID', 'tooltip': ''},
                     'item': {'search_type': 'ilike', 'gui_type': 'text', 'required': True, 'label': 'Item', 'tooltip': ''},
                     'item_ref': {'search_type': 'ilike', 'gui_type': 'text', 'required': False, 'label': 'Item ref', 'tooltip': 'Item reference or Catalog number'},
                     'item_size': {'search_type': 'ilike', 'gui_type': 'text', 'required': False, 'label': 'Size', 'tooltip': ''},
                     'provider': {'search_type': 'ilike', 'gui_type': 'select_option_none', 'required': False, 'label': 'Provider', 'tooltip': 'Where to order?'},
                     'provider_stockroom': {'search_type': 'equal_bool', 'gui_type': 'select_bool_none', 'required': True, 'label': 'Skr', 'tooltip': 'Is this item available in the stockroom?'},
                     'quantity': {'search_type': 'equal_number', 'gui_type': 'text', 'required': False, 'label': 'Qty', 'tooltip': ''},
                     'unit_price': {'search_type': 'equal_number', 'gui_type': 'text', 'required': False, 'label': 'Price', 'tooltip': 'Price per unit'},
                     'total_price': {'search_type': 'equal_number', 'gui_type': 'text', 'required': False, 'label': 'Total', 'tooltip': '', 'button': {'label': 'Update total', 'click': 'order_total'}},
                     'status': {'search_type': 'ilike', 'gui_type': 'select_option_none', 'required': True, 'label': 'Status', 'tooltip': '', 'default':'init_status'},
                     'recipient': {'search_type': 'ilike', 'gui_type': 'select_option_none', 'required': True, 'label': 'Recipient', 'tooltip': ''},
                     'date_insert': {'search_type': 'equal_date', 'gui_type': 'text', 'required': True, 'pattern': '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]', 'label': 'Date', 'tooltip': 'Order creation date', 'button': {'label': 'Today', 'click': 'order_today'}, 'default':'init_date'},
                     'date_order': {'search_type': 'equal_date', 'gui_type': 'text', 'required': False, 'pattern': '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]', 'label': 'Order', 'tooltip': '', 'button': {'label': 'Today', 'click': 'order_today_status'}},
                     'manufacturer': {'search_type': 'ilike', 'gui_type': 'text', 'required': False, 'label': 'Manufacturer', 'tooltip': ''},
                     'manufacturer_ref': {'search_type': 'ilike', 'gui_type': 'text', 'required': False, 'label': 'Manufacturer ref', 'tooltip': ''},
                     'funding': {'search_type': 'ilike', 'gui_type': 'text', 'required': False, 'label': 'Funding', 'tooltip': ''}}]
    column_infos_json = json.dumps(column_infos)

    column_titles = []
    column_titles_json = json.dumps(column_titles)

    default_search_criterions = [];
    default_search_criterions_json = json.dumps(default_search_criterions)

    default_sort_criterions = []
    default_sort_criterions_json = json.dumps(default_sort_criterions)

    default_limits = [['All', 'ALL', False], ['10', '10', False], ['100', '100', True], ['500', '500', False]]
    default_limits_json = json.dumps(default_limits)

    columns = []
    columns_json = json.dumps(columns)

    form = []
    form_json = json.dumps(form)

    board_class = 'Table'          # The default front-end board class to use for board view
    form_class = 'TableForm'       # The default front-end form class to use for form view

The meta-information about columns is defined in the column_infos class variable using the keys:

Key	Definition
search_type	ilike or equal_number (ILIKE or = SQL operators to search within this column)
gui_type	text, select_option_none (drop-down menu with options) or select_bool_none (drop-down menu with yes/no/None options)
required	HTML5 required: True or False
label	text
tooltip	text
pattern	HTML5 pattern to restrict user input.
onload	Define the function to start when edit field is loaded.
button	Define the button to add next to edit field. Button defined by label and onclick.

The meta-information about forms is defined in the form class variable, containing a list of sections to include in the form.

Key	Definition
label	Label of form section. None if no label
columns	List of columns to edit in this section

DefaultHandler

The DefaultHandler defined the default view of the database in default_url. It inherits from GenericDefaultHandler and OrderBaseHandler.

@routes.view('/order')
class OrderDefaultHandler(generic.GenericDefaultHandler, OrderBaseHandler):
    default_url = 'order/table'

GenericHandler

OrderHandler handles the main table of the Order database. Defaults defined in the OrderBaseHandler are adapted to this view. The specific class variables are:

Variable	Definition
queries	List of template SQL queries for each level of the database. Each level has a search_query_level and sort_query_level.
queries_search_prefixes	List of prefix for each level. Only defined as WHERE for first level and AND for subsequent level(s) in existing databases.

@routes.view('/order/table')
class OrderHandler(generic.GenericHandler, OrderBaseHandler):
    tpl = 'generic_table.jinja'
    board_class = 'OrderTable'

    levels = [0]
    levels_json = json.dumps(levels)

    default_sort_criterions = [[0, 'date_order', 'DESC', 'Order'], [0, 'provider_stockroom', 'ASC', 'Skr'], [0, 'provider', 'ASC', 'Provider']]
    default_sort_criterions_json = json.dumps(default_sort_criterions)

    columns = [[{'name':'item'}, {'name':'item_ref'}, {'name':'item_size'}, {'name':'provider'}, {'name':'provider_stockroom'}, {'name':'unit_price'}, {'name':'quantity'}, {'name':'total_price'}, {'name':'status'}, {'name':'recipient'}, {'name':'date_insert'}, {'name':'date_order'}, {'name':'manufacturer'}, {'name':'manufacturer_ref'}, {'name':'funding'}]]
    columns_json = json.dumps(columns)

    queries = ["SELECT COALESCE(array_to_json(array_agg(row_to_json(r))), '[]') FROM (SELECT * FROM %s.item {search_query_level0} {sort_query_level0} LIMIT {limit}) r;"%OrderBaseHandler.schema]
    queries_search_prefixes = [[' WHERE ']]

GenericQueriesHandler

The GenericQueriesHandler is designed to handle new data input. Multiple records can be input at the same time. Class variables includes the form (see above) and insert_queries. The template queries must include columns and query_values variables that will contain the list of columns included in the form and list of values edited by the user respectively.

@routes.view('/order/new')
class OrderNewHandler(generic.GenericQueriesHandler, OrderBaseHandler):
    tpl = 'generic_edit.jinja'

    form = [{'label':'Order', 'columns':[{'name':'item'}, {'name':'item_ref'}, {'name':'item_size'}, {'name':'date_insert'}, {'name':'recipient'}, {'name':'funding'}]},
            {'label':None, 'columns':[{'name':'unit_price'}, {'name':'quantity'}, {'name':'total_price'}]},
            {'label':'Status', 'columns':[{'name':'status'}, {'name':'date_order'}]},
            {'label':'Provider', 'columns':[{'name':'provider'}, {'name':'provider_stockroom'}, {'name':'manufacturer'}, {'name':'manufacturer_ref'}]}]
    form_json = json.dumps(form)

    insert_queries = ["INSERT INTO %s.item ({columns}) VALUES ({query_values});"%OrderBaseHandler.schema]

GenericRecordHandler

The GenericRecordHandler is designed to handle data editing of a single record. Class variables includes the form (see above) and update_queries. This handler reuses the form from OrderNewHandler. The template queries must include update_query and record_id variables.

@routes.view('/order/edit/{record_id}')
class OrderEditHandler(generic.GenericRecordHandler, OrderBaseHandler):
    tpl = 'generic_edit.jinja'
    form_class = 'TableForm'

    form = OrderNewHandler.form
    form_json = OrderNewHandler.form_json

    update_queries = ["UPDATE %s.item SET {update_query} WHERE item_id={record_id};"%OrderBaseHandler.schema]

GenericGetHandler

The GenericRecordHandler is designed to handle getting a single record. The template queries must include record_id variable.

@routes.view('/order/get/{record_id}')
class OrderGetHandler(generic.GenericGetHandler, OrderBaseHandler):
    queries = ["SELECT COALESCE(array_to_json(array_agg(row_to_json(r))), '[]') FROM (SELECT * FROM %s.item WHERE item_id={record_id}) r;"%OrderBaseHandler.schema]

GenericRemoveHandler

The GenericRecordHandler is designed to handle removal of a single record. The template queries must include record_id variable.

@routes.view('/order/remove/{record_id}')
class OrderRemoveHandler(generic.GenericRemoveHandler, OrderBaseHandler):
    queries = ["DELETE FROM %s.item WHERE item_id={record_id};"%OrderBaseHandler.schema]

Front-end

The main view handled by OrderHandler uses the Table class. To allow users to easily duplicate an order (to reorder the same item again), a new button has been added. The getControlElements method is overloaded to add this button in OrderTable (set in board_class), which inherits from the Table class. This new class is defined in order_board.js. The code to load this file in the client is automatically added by generic_table.jinja template.

class OrderTable extends Table {
    getControlElements(record) {
        var cts = []
        // Edit, Remove, and Duplicate
        cts.push(this.getControlEdit(record))
        cts.push(this.getControlRemove(record))
        cts.push(this.getControlDuplicate(record))
        // Ordered
        var button = createElement("BUTTON", "button", "Ordered")
        button.type = "submit"
        button.action = this.getActionURL("edit", record)
        button.onclick = function (e) {
            var xhr = new XMLHttpRequest()
            // Post
            xhr.open("POST", e.target.action, true)
            xhr.responseType = "text"
            xhr.onload = function() {
                if (this.status == 200) {
                    var status = this.getResponseHeader("Query-Status")
                    if (status == "OK") {
                        window.location.reload()
                    } else {
                        alert("Query failed: " + status)
                    }
                } else {
                    alert("Request failed: " + this.statusText)
                }
            }
            // Prepare
            var data = [{"status": "ordered", "date_order": getDate()}]
            // Send
            xhr.send(JSON.stringify(data))
        }
        cts.push(button)
        // Return
        return cts
    }
}

In OrderBaseHandler, click and default actions are defined for controls in the editing form. They are coded in order/addon.js. The code to load this file in the client is automatically added by generic_edit.jinja template.

function addonStart(op, t, e) {
    let r
    switch (op) {
        case 'init_date':
            r = initDate(t)
            break
        case 'init_status':
            r = initStatus(t)
            break
        case 'order_today_status':
            r = orderTodayStatus(t)
            break
        case 'order_today':
            r = orderToday(t)
            break
        case 'order_total':
            r = orderTotal(t)
            break
        default:
            alert('Unknown operation: '+op)
    }
    return r
}

function initDate(input) {
    return getDate()
}

function initStatus(input) {
    return 'to order'
}

Finally, add a new entry in the main.jinja template.