Creating Table With Empty SQL File

In this chapter we will create an example demo application for a small bakery shop, to this purpose we need to create some kind of database.
Because we want users to administrate and access the database we need to have GUI interfacing it.

On this page:

Creating Demo Database

In the demo we will create tables in the database schema demo in the existing PostgreSQL database which is installed together with the MasonSQL framework.
In our installation the existing MasonSQL database is named masonsql thus when we refer to the masonsql we mean the database which is installed with the MasonSQL framework.

In the examples three tables are sufficient - we create these tables inside the demo schema:

• orders,
• employees,
• commissions

The orders table relies on the information from the employees and commissions tables so we will make it a parent table.

PostgreSLQ Serial Types

Note the PostgreSQL Lexical Structure. Although uppercase characters or blank spaces are allowed they must be enclosed in double quotes in the query statements.
In other words the cleanest looking PostgreSQL queries are when uppercase letters and blanks are avoided in the schema names, table names, column names, etc.

For the primary keys we will use special serial types. MasonSQL checks the existence of a sequence by searching for its default name <table_name>_<key_name>_seq.

This is done by SERIAL_SEQ method:

<%method SERIAL_SEQ><& SELF:SCHEMA_FROM &>_<& SELF:KEY_NAME &>_seq</%method>

In some extraordinary case when we would like to create sequences with different names we would have to declare the name of the sequence in the .mql file overloading the default value of the SERIAL_SEQ method.

Creating Child Tables

First we create child tables, the employees table has columns:

• id (primary key),
• name,
• surname,
• phone,
• address,
• city,
• country,
• postal_code

The SQL command that creates the table:

CREATE TABLE demo.employees
(
  id SERIAL,
  name text,
  surname text,
  phone integer,
  address text,
  city text,
  country text,
  postal_code integer,
  CONSTRAINT pk_employees PRIMARY KEY (id)
);

The commissions table has columns:

• id (primary key),
• type,
• description,
• percentage

The SQL command that creates the table:

CREATE TABLE demo.commissions
(
  id SERIAL,
  type text,
  description text,
  percentage integer,
  CONSTRAINT pk_comissions PRIMARY KEY (id)
);

Creating Parent Table

At the end we create the orders table with columns:

• id (primary key),
• price,
• commission (foreign key),
• employee (foreign key),
• description

The SQL command that creates the table:

CREATE TABLE demo.orders
(
  id SERIAL,
  price numeric(8,2),
  commission integer,
  employee integer,
  CONSTRAINT pk_orders PRIMARY KEY (id),
  CONSTRAINT commission FOREIGN KEY (commission)
      REFERENCES demo.commissions (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT employee FOREIGN KEY (employee)
      REFERENCES demo.employees (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

Granting Schema Permissions

On the demo schema we grant permissions for the postgres, masonsql and report users:

GRANT ALL ON SCHEMA demo TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE ON SCHEMA demo TO masonsql;
GRANT SELECT ON SCHEMA demo TO report;

Granting Table Permissions

On the orders, commissions and employees tables we grant the same permissions:

GRANT ALL ON TABLE demo.employees TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE demo.employees TO masonsql;
GRANT SELECT ON TABLE demo.employees TO report;

GRANT ALL ON TABLE demo.commissions TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE demo.commissions TO masonsql;
GRANT SELECT ON TABLE demo.commissions TO report;

GRANT ALL ON TABLE demo.orders TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE demo.orders TO masonsql;
GRANT SELECT ON TABLE demo.orders TO report;

We must additionally grant permissions to the sequences:

GRANT ALL ON TABLE demo.employees_id_seq TO postgres;
GRANT SELECT, UPDATE ON TABLE demo.employees_id_seq TO masonsql;
GRANT SELECT ON TABLE demo.employees_id_seq TO report;

GRANT ALL ON TABLE demo.commissions_id_seq TO postgres;
GRANT SELECT, UPDATE ON TABLE demo.commissions_id_seq TO masonsql;
GRANT SELECT ON TABLE demo.commissions_id_seq TO report;

GRANT ALL ON TABLE demo.orders_id_seq TO postgres;
GRANT SELECT, UPDATE ON TABLE demo.orders_id_seq TO masonsql;
GRANT SELECT ON TABLE demo.orders_id_seq TO report;

Querying Table

A schema.table pair corresponds to the /opt/demo_masonsql/htdocs/data/schema/table.mql file.
If we want to be able to query the orders table we must consequently create an empty file orders.mql in the /opt/demo_masonsql/htdocs/data/demo/ directory.

Now we can successfully make the query:

https://www.mysite.com/frame.html?from=demo/orders

The default result may be changed using many methods placed in the .mql files, which will be shown in the next examples.
The current example works without the two child tables which will also be used in the next examples.

Adding Table To Menu

Before adding the first demo menu let's make its father menu titled Demo Examples:

A new table can be added as a menu option in the web application even with empty .mql file because the default values for FUNZIONE and SCHEMA_FROM are already defined in the /opt/masonsql/htdocs/lib/dbms_library.comp file:

<%method FUNZIONE><&SELF:SCHEMA_FROM&></%method>
<%method SCHEMA_FROM><& SELF:SCHEMA &>.<& SELF:FROM &></%method>

To reference the table we must make reference demo.orders directly into the Nome: field in the public.funzioni table:

Pay attention to the permissions table in both pictures, notice that both tables have Menu authorizations enabled. A user without enabled Menu authorization can't see the corresponding menu.

We get a basic display of table records: