Creating the Database Objects

Domains

Table of Contents

BOOLEAN Type

First, we define some domains that we will use in column definitions.

CREATE DOMAIN D_BOOLEAN AS
SMALLINT
CHECK (VALUE IN (0, 1));

COMMENT ON DOMAIN D_BOOLEAN IS
'Boolean type. 0 - FALSE, 1- TRUE';

CREATE DOMAIN D_MONEY AS
NUMERIC(15,2);

CREATE DOMAIN D_ZIPCODE AS
CHAR(10) CHARACTER SET UTF8
CHECK (TRIM(TRAILING FROM VALUE) SIMILAR TO '[0-9]+');

COMMENT ON DOMAIN D_ZIPCODE IS
'Zip code';

BOOLEAN Type

In Firebird 3.0, there is a native BOOLEAN type. Some drivers do not support it, due to its relatively recent appearance in Firebird's SQL lexicon. With that in mind,, our applications will be built on a database that will work with either Firebird 2.5 or Firebird 3.0.

Important

Before Firebird 3, servers could connect clients to databases that were created under older Firebird versions. Firebird 3 can connect only to databases that were created on or restored under Firebird 3.

Primary Tables

Now let us proceed to the primary tables. The first will be the CUSTOMER table. We will create a sequence (a generator) for its primary key and a corresponding trigger for implementing it as an auto-incrementing column. We will do the same for each of the tables.

CREATE GENERATOR GEN_CUSTOMER_ID;

CREATE TABLE CUSTOMER (
  CUSTOMER_ID INTEGER NOT NULL,
  NAME VARCHAR(60) NOT NULL,
  ADDRESS VARCHAR(250),
  ZIPCODE D_ZIPCODE,
  PHONE VARCHAR(14),
  CONSTRAINT PK_CUSTOMER PRIMARY KEY (CUSTOMER_ID)
);

SET TERM ^ ;

CREATE OR ALTER TRIGGER CUSTOMER_BI FOR CUSTOMER
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.CUSTOMER_ID IS NULL) THEN
    NEW.CUSTOMER_ID = NEXT VALUE FOR GEN_CUSTOMER_ID;
END
^

SET TERM ; ^

COMMENT ON TABLE CUSTOMER IS
'Customers';

COMMENT ON COLUMN CUSTOMER.CUSTOMER_ID IS
'Customer Id';

COMMENT ON COLUMN CUSTOMER.NAME IS
'Name';

COMMENT ON COLUMN CUSTOMER.ADDRESS IS
'Address';

COMMENT ON COLUMN CUSTOMER.ZIPCODE IS
'Zip Code';

COMMENT ON COLUMN CUSTOMER.PHONE IS
'Phone';

Note

In Firebird 3.0, you can use IDENTITY columns as auto-incremental fields. The script for creating the table would then be as follows:

CREATE TABLE CUSTOMER (
  CUSTOMER_ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
  NAME VARCHAR(60) NOT NULL,
  ADDRESS VARCHAR(250),
  ZIPCODE D_ZIPCODE,
  PHONE VARCHAR(14),
  CONSTRAINT PK_CUSTOMER PRIMARY KEY (CUSTOMER_ID)
);

In Firebird 3.0, you need the USAGE privilege to use a sequence (generator) so you will have to add the following line to the script:
```
GRANT USAGE ON SEQUENCE GEN_CUSTOMER_ID TO TRIGGER CUSTOMER_BI;
                
```

Now we construct a script for creating the PRODUCT table:

CREATE GENERATOR GEN_PRODUCT_ID;

CREATE TABLE PRODUCT (
  PRODUCT_ID INTEGER NOT NULL,
  NAME VARCHAR(100) NOT NULL,
  PRICE D_MONEY NOT NULL,
  DESCRIPTION BLOB SUB_TYPE 1 SEGMENT SIZE 80,
  CONSTRAINT PK_PRODUCT PRIMARY KEY (PRODUCT_ID)
);

SET TERM ^;

CREATE OR ALTER TRIGGER PRODUCT_BI FOR PRODUCT
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.PRODUCT_ID IS NULL) THEN
    NEW.PRODUCT_ID = NEXT VALUE FOR GEN_PRODUCT_ID;
END
^

SET TERM ;^

COMMENT ON TABLE PRODUCT IS
'Goods';

COMMENT ON COLUMN PRODUCT.PRODUCT_ID IS
'Product Id';

COMMENT ON COLUMN PRODUCT.NAME IS
'Name';

COMMENT ON COLUMN PRODUCT.PRICE IS
'Price';

COMMENT ON COLUMN PRODUCT.DESCRIPTION IS
'Description';

Note

In Firebird 3.0, you need to add the command for granting the USAGE privilege for a sequence (generator) to the script:

GRANT USAGE ON SEQUENCE GEN_PRODUCT_ID TO TRIGGER PRODUCT_BI;

Secondary Tables

The script for creating the INVOICE table:

CREATE GENERATOR GEN_INVOICE_ID;

CREATE TABLE INVOICE (
  INVOICE_ID INTEGER NOT NULL,
  CUSTOMER_ID INTEGER NOT NULL,
  INVOICE_DATE TIMESTAMP,
  TOTAL_SALE D_MONEY,
  PAID D_BOOLEAN DEFAULT 0 NOT NULL,
  CONSTRAINT PK_INVOICE PRIMARY KEY (INVOICE_ID)
);

ALTER TABLE INVOICE ADD CONSTRAINT FK_INVOCE_CUSTOMER
FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID);

CREATE INDEX INVOICE_IDX_DATE ON INVOICE (INVOICE_DATE);

SET TERM ^;

CREATE OR ALTER TRIGGER INVOICE_BI FOR INVOICE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.INVOICE_ID IS NULL) THEN
    NEW.INVOICE_ID = GEN_ID(GEN_INVOICE_ID,1);
END
^

SET TERM ;^

COMMENT ON TABLE INVOICE IS
'Invoices';

COMMENT ON COLUMN INVOICE.INVOICE_ID IS
'Invoice number';

COMMENT ON COLUMN INVOICE.CUSTOMER_ID IS
'Customer Id';

COMMENT ON COLUMN INVOICE.INVOICE_DATE IS
'The date of issuance invoices';

COMMENT ON COLUMN INVOICE.TOTAL_SALE IS
'Total sum';

COMMENT ON COLUMN INVOICE.PAID IS
'Paid';

The INVOICE_DATE column is indexed because we will be filtering invoices by date to enable the records to be selected by a work period that will be application-defined by a start date and an end date.

Note

In Firebird 3.0, you need to add the command for granting the USAGE privilege for a sequence (generator) to the script:

GRANT USAGE ON SEQUENCE GEN_INVOICE_ID TO TRIGGER INVOICE_BI;

The script for creating the INVOICE_LINE table:

CREATE GENERATOR GEN_INVOICE_LINE_ID;

CREATE TABLE INVOICE_LINE (
INVOICE_LINE_ID INTEGER NOT NULL,
INVOICE_ID INTEGER NOT NULL,
PRODUCT_ID INTEGER NOT NULL,
QUANTITY NUMERIC(15,0) NOT NULL,
SALE_PRICE D_MONEY NOT NULL,
CONSTRAINT PK_INVOICE_LINE PRIMARY KEY (INVOICE_LINE_ID)
);

ALTER TABLE INVOICE_LINE ADD CONSTRAINT FK_INVOICE_LINE_INVOICE
FOREIGN KEY (INVOICE_ID) REFERENCES INVOICE (INVOICE_ID);

ALTER TABLE INVOICE_LINE ADD CONSTRAINT FK_INVOICE_LINE_PRODUCT
FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT (PRODUCT_ID);

SET TERM ^;

CREATE OR ALTER TRIGGER INVOICE_LINE_BI FOR INVOICE_LINE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.INVOICE_LINE_ID IS NULL) THEN
    NEW.INVOICE_LINE_ID = NEXT VALUE FOR GEN_INVOICE_LINE_ID;
END
^

SET TERM ;^

COMMENT ON TABLE INVOICE_LINE IS
'Invoice lines';

COMMENT ON COLUMN INVOICE_LINE.INVOICE_LINE_ID IS
'Invoice line Id';

COMMENT ON COLUMN INVOICE_LINE.INVOICE_ID IS
'Invoice number';

COMMENT ON COLUMN INVOICE_LINE.PRODUCT_ID IS
'Product Id';

COMMENT ON COLUMN INVOICE_LINE.QUANTITY IS
'Quantity';

COMMENT ON COLUMN INVOICE_LINE.SALE_PRICE IS
'Price';

Note

In Firebird 3.0, you need to add the command for granting the USAGE privilege for a sequence (generator) to the script:

GRANT USAGE ON SEQUENCE GEN_INVOICE_LINE_ID TO TRIGGER INVOICE_LINE_BI;

Stored Procedures

Table of Contents

Adding a new invoice
Editing an invoice
Deleting an invoice
Paying an invoice
Invoice Line Items

Some parts of the business logic will be implemented by means of stored procedures.

Adding a new invoice

The procedure for adding a new invoice is quite simple:

SET TERM ^;

CREATE OR ALTER PROCEDURE SP_ADD_INVOICE (
  INVOICE_ID INTEGER,
  CUSTOMER_ID INTEGER,
  INVOICE_DATE TIMESTAMP = CURRENT_TIMESTAMP)
AS
BEGIN
  INSERT INTO INVOICE (
    INVOICE_ID,
    CUSTOMER_ID,
    INVOICE_DATE,
    TOTAL_SALE,
    PAID
  )
  VALUES (
    :INVOICE_ID,
    :CUSTOMER_ID,
    :INVOICE_DATE,
    0,
    0
  );
END
^

SET TERM ;^

COMMENT ON PROCEDURE SP_ADD_INVOICE IS
'Adding Invoice';

COMMENT ON PARAMETER SP_ADD_INVOICE.INVOICE_ID IS
'Invoice number';

COMMENT ON PARAMETER SP_ADD_INVOICE.CUSTOMER_ID IS
'Customer Id';

COMMENT ON PARAMETER SP_ADD_INVOICE.INVOICE_DATE IS
'Date';

GRANT INSERT ON INVOICE TO PROCEDURE SP_ADD_INVOICE;

Editing an invoice

The procedure for editing an invoice is a bit more complicated. We will include a rule to block further editing of an invoice once it is paid. We will create an exception that will be raised if an attempt is made to modify a paid invoice.

CREATE EXCEPTION E_INVOICE_ALREADY_PAYED 'Change is impossible, invoice paid.';
The stored procedure for editing an invoice:
SET TERM ^;

CREATE OR ALTER PROCEDURE SP_EDIT_INVOICE (
  INVOICE_ID INTEGER,
  CUSTOMER_ID INTEGER,
  INVOICE_DATE TIMESTAMP)
AS
BEGIN
  IF (EXISTS(SELECT *
             FROM INVOICE
             WHERE INVOICE_ID = :INVOICE_ID
               AND PAID = 1)) THEN
    EXCEPTION E_INVOICE_ALREADY_PAYED;

  UPDATE INVOICE
  SET CUSTOMER_ID = :CUSTOMER_ID,
  INVOICE_DATE = :INVOICE_DATE
  WHERE INVOICE_ID = :INVOICE_ID;
END
^

SET TERM ;^

COMMENT ON PROCEDURE SP_EDIT_INVOICE IS
'Editing invoice';

COMMENT ON PARAMETER SP_EDIT_INVOICE.INVOICE_ID IS
'Invoice number';

COMMENT ON PARAMETER SP_EDIT_INVOICE.CUSTOMER_ID IS
'Customer Id';

COMMENT ON PARAMETER SP_EDIT_INVOICE.INVOICE_DATE IS
'Date';

GRANT SELECT,UPDATE ON INVOICE TO PROCEDURE SP_EDIT_INVOICE;

Note

In Firebird 3.0, the USAGE privilege is required for exceptions so we need to add the following line:

GRANT USAGE ON EXCEPTION E_INVOICE_ALREADY_PAYED TO PROCEDURE SP_EDIT_INVOICE;

Deleting an invoice

The procedure SP_DELETE_INVOICE procedure checks whether the invoice is paid and raises an exception if it is:

SET TERM ^ ;

CREATE OR ALTER PROCEDURE SP_DELETE_INVOICE (
  INVOICE_ID INTEGER)
AS
BEGIN
  IF (EXISTS(SELECT * FROM INVOICE
             WHERE INVOICE_ID = :INVOICE_ID
               AND PAID = 1)) THEN
    EXCEPTION E_INVOICE_ALREADY_PAYED;

  DELETE FROM INVOICE WHERE INVOICE_ID = :INVOICE_ID;
END
^

SET TERM ;^

COMMENT ON PROCEDURE SP_DELETE_INVOICE IS
'Deleting invoices';

GRANT SELECT,DELETE ON INVOICE TO PROCEDURE SP_DELETE_INVOICE;

Note

In Firebird 3.0, the USAGE privilege is required for exceptions so we need to add the following line:

GRANT USAGE ON EXCEPTION E_INVOICE_ALREADY_PAYED TO PROCEDURE SP_DELETE_INVOICE;

Paying an invoice

We will add one more procedure for paying an invoice:

SET TERM ^;

CREATE OR ALTER PROCEDURE SP_PAY_FOR_INVOICE (
INVOICE_ID INTEGER)
AS
BEGIN
  IF (EXISTS(SELECT *
             FROM INVOICE
             WHERE INVOICE_ID = :INVOICE_ID
               AND PAID = 1)) THEN
EXCEPTION E_INVOICE_ALREADY_PAYED;
UPDATE INVOICE
SET PAID = 1
WHERE INVOICE_ID = :INVOICE_ID;
END
^

SET TERM ;^

COMMENT ON PROCEDURE SP_PAY_FOR_INVOICE IS
'Payment of invoices';

COMMENT ON PARAMETER SP_PAY_FOR_INVOICE.INVOICE_ID IS
'Invoice number';

GRANT SELECT,UPDATE ON INVOICE TO PROCEDURE SP_PAY_FOR_INVOICE;

Note

In Firebird 3.0, the USAGE privilege is required for exceptions so we need to add the following line:

GRANT USAGE ON EXCEPTION E_INVOICE_ALREADY_PAYED TO PROCEDURE SP_PAY_FOR_INVOICE;

Invoice Line Items

Procedures for managing invoice items will check whether the invoice is paid and block any attempt to alter the line items of paid invoices. They will also correct the invoice total according to the amount of the product sold and its price.

Adding line items

The procedure for adding a line item to an invoice:

SET TERM ^;

CREATE OR ALTER PROCEDURE SP_ADD_INVOICE_LINE (
  INVOICE_ID INTEGER,
  PRODUCT_ID INTEGER,
  QUANTITY INTEGER)
AS
  DECLARE sale_price D_MONEY;
  DECLARE paid D_BOOLEAN;
BEGIN
  SELECT
    paid
  FROM
    invoice
  WHERE
    invoice_id = :invoice_id
  INTO :paid;

  -- It does not allow you to edit already paid invoice.
  IF (paid = 1) THEN
    EXCEPTION E_INVOICE_ALREADY_PAYED;

  SELECT
    price
  FROM
    product
  WHERE
    product_id = :product_id
  INTO :sale_price;

INSERT INTO invoice_line (
  invoice_line_id,
  invoice_id,
  product_id,
  quantity,
  sale_price)
VALUES (
  NEXT VALUE FOR gen_invoice_line_id,
  :invoice_id,
  :product_id,
  :quantity,
  :sale_price);

  -- Increase the amount of the account.
  UPDATE invoice
  SET total_sale = COALESCE(total_sale, 0) + :sale_price * :quantity
  WHERE invoice_id = :invoice_id;

END
^
SET TERM ;^

COMMENT ON PROCEDURE SP_ADD_INVOICE_LINE IS
'Adding line invoices';

COMMENT ON PARAMETER SP_ADD_INVOICE_LINE.INVOICE_ID IS
'Invoice number';

COMMENT ON PARAMETER SP_ADD_INVOICE_LINE.PRODUCT_ID IS
'Product Id';

COMMENT ON PARAMETER SP_ADD_INVOICE_LINE.QUANTITY IS
'Quantity';

GRANT SELECT, UPDATE ON INVOICE TO PROCEDURE SP_ADD_INVOICE_LINE;
GRANT SELECT ON PRODUCT TO PROCEDURE SP_ADD_INVOICE_LINE;
GRANT INSERT ON INVOICE_LINE TO PROCEDURE SP_ADD_INVOICE_LINE;
-- only Firebird 3.0 and above
GRANT USAGE ON EXCEPTION E_INVOICE_ALREADY_PAYED TO PROCEDURE SP_ADD_INVOICE_LINE;
GRANT USAGE ON SEQUENCE GEN_INVOICE_LINE_ID TO PROCEDURE SP_ADD_INVOICE_LINE;

Editing line items

The procedure for editing an invoice line item:

SET TERM ^;

CREATE OR ALTER PROCEDURE SP_EDIT_INVOICE_LINE (
  INVOICE_LINE_ID INTEGER,
  QUANTITY INTEGER)
AS
  DECLARE invoice_id INT;
  DECLARE price D_MONEY;
  DECLARE paid D_BOOLEAN;
BEGIN
  SELECT
    product.price,
    invoice.invoice_id,
    invoice.paid
  FROM
    invoice_line
  JOIN invoice ON invoice.invoice_id = invoice_line.invoice_id
  JOIN product ON product.product_id = invoice_line.product_id
  WHERE
    invoice_line.invoice_line_id = :invoice_line_id
  INTO
    :price,
    :invoice_id,
    :paid;

  -- It does not allow you to edit an already paid invoice.
  IF (paid = 1) THEN
    EXCEPTION E_INVOICE_ALREADY_PAYED;

  -- Update price and quantity.
  UPDATE invoice_line
  SET sale_price = :price,
      quantity = :quantity
  WHERE invoice_line_id = :invoice_line_id;
  -- Now update the amount of the account.
  MERGE INTO invoice
  USING (
    SELECT
      invoice_id,
      SUM(sale_price * quantity) AS total_sale
    FROM invoice_line
    WHERE invoice_id = :invoice_id
    GROUP BY invoice_id) L
  ON invoice.invoice_id = L.invoice_id
  WHEN MATCHED THEN
    UPDATE SET total_sale = L.total_sale;
END
^

SET TERM ;^

COMMENT ON PROCEDURE SP_EDIT_INVOICE_LINE IS
'Editing invoice line';

COMMENT ON PARAMETER SP_EDIT_INVOICE_LINE.INVOICE_LINE_ID IS
'Invoice line id';

COMMENT ON PARAMETER SP_EDIT_INVOICE_LINE.QUANTITY IS
'Quantity';

GRANT SELECT,UPDATE ON INVOICE_LINE TO PROCEDURE SP_EDIT_INVOICE_LINE;
GRANT SELECT,UPDATE ON INVOICE TO PROCEDURE SP_EDIT_INVOICE_LINE;
GRANT SELECT ON PRODUCT TO PROCEDURE SP_EDIT_INVOICE_LINE;
-- only Firebird 3.0 and above
GRANT USAGE ON EXCEPTION E_INVOICE_ALREADY_PAYED TO PROCEDURE SP_EDIT_INVOICE_LINE;

Deleting line items

The procedure for deleting an invoice line item from an invoice:

SET TERM ^;

CREATE OR ALTER PROCEDURE SP_DELETE_INVOICE_LINE (
  INVOICE_LINE_ID INTEGER)
AS
  DECLARE invoice_id INT;
  DECLARE price D_MONEY;
  DECLARE quantity INT;
BEGIN
  IF (EXISTS(SELECT *
             FROM invoice_line
             JOIN invoice ON invoice.invoice_id = invoice_line.invoice_id
             WHERE invoice.paid = 1
               AND invoice_line.invoice_line_id = :invoice_line_id)) THEN
    EXCEPTION E_INVOICE_ALREADY_PAYED;

  DELETE FROM invoice_line
  WHERE invoice_line.invoice_line_id = :invoice_line_id
  RETURNING invoice_id, quantity, sale_price
  INTO invoice_id, quantity, price;

  -- Reduce the amount of the account.
  UPDATE invoice
  SET total_sale = total_sale - :quantity * :price
  WHERE invoice_id = :invoice_id;
END
^

SET TERM ;^

COMMENT ON PROCEDURE SP_DELETE_INVOICE_LINE IS
'Deleting invoice item';

COMMENT ON PARAMETER SP_DELETE_INVOICE_LINE.INVOICE_LINE_ID IS
'Code invoice item';
Privileges for Procedures
GRANT SELECT,DELETE ON INVOICE_LINE TO PROCEDURE SP_DELETE_INVOICE_LINE;
GRANT SELECT,UPDATE ON INVOICE TO PROCEDURE SP_DELETE_INVOICE_LINE;
-- only Firebird 3.0 and above
GRANT USAGE ON EXCEPTION E_INVOICE_ALREADY_PAYED TO PROCEDURE SP_DELETE_INVOICE_LINE;

Roles and Privileges for Users

Now we need to create roles and grant the corresponding privileges. We will create two roles: MANAGER and SUPERUSER. MANAGER will have a limited set of privileges while SUPERUSER will have access to practically everything in the database that is used by the project application.

CREATE ROLE MANAGER;
CREATE ROLE SUPERUSER;
The MANAGER role can read any table and use the corresponding procedures to manage invoices:
GRANT SELECT ON CUSTOMER TO MANAGER;
GRANT SELECT ON INVOICE TO MANAGER;
GRANT SELECT ON INVOICE_LINE TO MANAGER;
GRANT SELECT ON PRODUCT TO MANAGER;
GRANT EXECUTE ON PROCEDURE SP_ADD_INVOICE TO MANAGER;
GRANT EXECUTE ON PROCEDURE SP_ADD_INVOICE_LINE TO MANAGER;
GRANT EXECUTE ON PROCEDURE SP_DELETE_INVOICE TO MANAGER;
GRANT EXECUTE ON PROCEDURE SP_DELETE_INVOICE_LINE TO MANAGER;
GRANT EXECUTE ON PROCEDURE SP_EDIT_INVOICE TO MANAGER;
GRANT EXECUTE ON PROCEDURE SP_EDIT_INVOICE_LINE TO MANAGER;
GRANT EXECUTE ON PROCEDURE SP_PAY_FOR_INVOICE TO MANAGER;
GRANT USAGE ON SEQUENCE GEN_INVOICE_ID TO MANAGER;
The SUPERUSER role can read any table, edit the primary tables directly and use the procedures to manage invoices:
GRANT SELECT, INSERT, UPDATE, DELETE ON CUSTOMER TO SUPERUSER;
GRANT SELECT ON INVOICE TO SUPERUSER;
GRANT SELECT ON INVOICE_LINE TO SUPERUSER;
GRANT SELECT, INSERT, UPDATE, DELETE ON PRODUCT TO SUPERUSER;
GRANT EXECUTE ON PROCEDURE SP_ADD_INVOICE TO SUPERUSER;
GRANT EXECUTE ON PROCEDURE SP_ADD_INVOICE_LINE TO SUPERUSER;
GRANT EXECUTE ON PROCEDURE SP_DELETE_INVOICE TO SUPERUSER;
GRANT EXECUTE ON PROCEDURE SP_DELETE_INVOICE_LINE TO SUPERUSER;
GRANT EXECUTE ON PROCEDURE SP_EDIT_INVOICE TO SUPERUSER;
GRANT EXECUTE ON PROCEDURE SP_EDIT_INVOICE_LINE TO SUPERUSER;
GRANT EXECUTE ON PROCEDURE SP_PAY_FOR_INVICE TO SUPERUSER;
GRANT USAGE ON SEQUENCE GEN_CUSTOMER_ID TO SUPERUSER;
GRANT USAGE ON SEQUENCE GEN_INVOICE_ID TO SUPERUSER;
GRANT USAGE ON SEQUENCE GEN_PRODUCT_ID TO SUPERUSER;

These statements create some users and assign roles to them:

CREATE USER IVAN PASSWORD 'z12a';
CREATE USER ANNA PASSWORD 'lh67';

GRANT MANAGER TO ANNA;
GRANT MANAGER TO IVAN WITH ADMIN OPTION;
GRANT SUPERUSER TO IVAN;

The user IVAN can assign the MANAGER role to other users.