DB Development in SQL

by K. Yue

1. Introduction

Read Chapters 4, 5 and 13 of EN.
Cover some topics useful in DB development in SQL.
Use MySQL as examples.

2. System Catalog

Named collections meta data in SQL DBMS.
Important for DB administrator and developer to know.
Usually stored in relations.
Common terms with similar meaning: data dictionary, catalog, etc.
Thee major databases come with MySQL: mysql, information_schema and performance_schema.
There is also an example database: Sakila.

Example:

mysql database

Store information about the MySQL user.
For example, the user table stores user, password (encrypted) and privileges.

Example:

Try to add a new user by inserting into the user table: http://dev.mysql.com/doc/refman/5.6/en/adding-users.html. There are problems.

use mysql;

INSERT INTO user
VALUES('localhost','abcde',PASSWORD('IloveDBMS'),
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
'','','','',0,0,0,0);

FLUSH PRIVILEGES;

Try:

describe user;

What do we learn?

Try:

INSERT INTO user (Host,User,Password,select_priv, insert_priv, update_priv,ssl_cipher,x509_issuer,x509_subject)
VALUES ('localhost', 'abcde', PASSWORD('IloveDBMS'), 'Y', 'Y', 'Y', 'NULL', 'NULL', 'NULL');

FLUSH PRIVILEGES;

information_schema database

Store information about all databases in MySQL.
Contents are views: cannot be updated. Triggers cannot be defined.

Example:

It is more flexible than the SHOW command.

performance_schema database

contain performance related server data.

sakila database

A good toy example for learning MySQL and practising SQL: http://dev.mysql.com/doc/sakila/en/sakila.html#sakila-structure-tables-film.

3. Views

Views are virtual table derived from other tables.
Some advantages of using views:
- Better data abstraction
- Better consistency
- Possible security control
- Can be more efficient.
Some disadvantages:
- Cab be less efficient.
- More work.
- Complicated especially when views are updated.

Example:

The Sakila database comes preloaded with MySQL installation using XAMPP. Refer to the documentation at: http://dev.mysql.com/doc/sakila/en/sakila.html. Download the database at downloads.mysql.com/docs/sakila-db.zip to review the SQL statements for declaring and populating the database.

Define a view to provide information of all paying customers: thier id, the total sum of their payment, and the total sum of their payment towards film rentals.

DROP VIEW customer_payment;

CREATE DEFINER=CURRENT_USER SQL SECURITY INVOKER VIEW customer_payment
AS
SELECT
   p.customer_id AS customer,
   sum(p.amount) AS "Total Payment",
   sum(IF(p.rental_id IS NULL, 0, p.amount)) AS "File Rental Payment"
FROM payment as p
GROUP BY p.customer_id;

SELECT * FROM customer_payment LIMIT 20;

Note:

The use of SECURITY INVOKE for security control. The privilege of the invoker is used as the privilege executing the view.
The use of the IF function.
The output for customer with id 16.
Only one table is needed to define the view.

4. Stored Procedures

Sub-routines stored by the DBMS that can be called.
Sub-routines can be
- functions: return a value, or
- procedures
Known as SQL/PSM (SQL/Persistent Stored Modules).
- Can execute SQL statements
- Include general programming constructs
Some advantages of stored procedures
- Enforcing application constraints and requirements.
- Providing consistency and security control.
- Possible performance optimization by both the developers and the DBMS.
- Sharing among DB applications.
Some disadvantages of stored procedures:
- Use up DB server's resources.
- Potentially inefficient because of limitations in language constructs.
- Potentially harder to develop because of relative lack of libraries and difficulty in debugging.
- Can be vendor specific.
In general, stored procedures should be used more often.
MySQL stored procedures:
- http://dev.mysql.com/doc/refman/5.6/en/stored-routines.html
- http://dev.mysql.com/doc/refman/5.6/en/faqs-stored-procs.html#qandaitem-B-4-1-2

Example:

DROP PROCEDURE AllSuppliers;

-- A very simple stored procedure
DELIMITER //

CREATE PROCEDURE AllSuppliers()
BEGIN
SELECT * FROM Supplier;
END //

DELIMITER ;

-- Call
CALL AllSuppliers;

Note:

Use of the DELIMITER command to redefine // as the delimiter indicating the end of the stored procedure. Otherwise, the default ; is the delimiter indicating the end of the stored procedure.
No parameters for the procedure.
A procedure does not return any value and accomplish its goal through side effects.
The result is displayed.

Example:

DROP FUNCTION GetAllSuppliers;

-- A very simple stored function
DELIMITER //

CREATE FUNCTION GetAllSuppliers() RETURNS INT
BEGIN
   DECLARE count INT DEFAULT 0;
   SET count = 0;    -- Not really needed.

   SELECT COUNT(*) INTO count FROM Supplier;

   -- The following is not allowed as result set in function.
   -- SELECT * FROM Supplier;

   RETURN count;
END //

DELIMITER ;

-- Call
SELECT GetAllSuppliers();

Note:

A function returns a value and does not accomplish its goal through side effect.
Note variable declaration and assignment.
The scope of the variable is within the function.
On the other hand, a session variable (starts with a @) has the session as its scope. Use them very carefully.

Example:

-- Parts supplied by a supplier

DROP PROCEDURE PartSupplied;
DELIMITER //

CREATE PROCEDURE PartSupplied(
   IN snum VARCHAR(9),
   IN max INT,                  -- Maximum number of records shown
   OUT numberPartsSupplied INT -- Number of different parts supplied
)
BEGIN
   SELECT COUNT(*) INTO numberPartsSupplied
   FROM Supply S
   WHERE S.SNUM = snum;

   SELECT P.PNUM, P.PNAME, S.Quantity
   FROM Supply S, Part P
   WHERE S.SNUM = snum
   AND S.PNUM = P.PNUM
   LIMIT max;

END //

DELIMITER ;

-- Call

SET @numberPartsSupplied = 0;
CALL PartSupplied('S2', 2, @numberPartsSupplied);
SELECT @numberPartsSupplied;

Note:

Parameter passing mode: IN, OUT and IN OUT. This is similar to Ada.
Use of the parameter max in the LIMIT clause. There are limitations on where parameters can be used in SQL statements.
Use of the session variable @numberPartsSupplied.

5. Triggers

Triggers allow event drive programming.
A trigger is activated when certain events occur. Unlike stored sub-routines, trigger is not called directly.
Advantages of triggers:
- event driven model stiffing certain kind of tasks better.
- implementation of consistency check.
- implementation of business logic and integrity check.
Disadvantages:
- Relatively invisible and possibly overlook by developers.
- Potential performance issues.
- Potential complicated interactions.

Example:

Using the Sakila database, consider the tables inventory and rental.

inventory (inventory_id, film_id, store_id, last_update)
rental (rental_id, rental-date, inventory_id, customer_id, return_date, staff_id, last_update)

The stored function inventory_in_stock(p_inventory_id INT) checks whether an inventory item is in stock. Its logic:

"AN ITEM IS IN-STOCK IF THERE ARE EITHER NO ROWS IN THE rental TABLE FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED."

This can be inefficient.

We consider modify the table inventory to speed up performance.

inventory (inventory_id, film_id, store_id, lost, in_stock, last_update)

Two columns are added:

lost: whether the inventory item has been lost; default FALSE.
in_stock: whether the inventory item is in stock; default TRUE.

We may consider that the column in_stock as a derived column. Its value can be derived from other columns and tables in the database. Maintaining data consistency of derived columns is a popular use of triggers.

ALTER TABLE inventory
ADD COLUMN lost BOOLEAN DEFAULT FALSE AFTER store_id;

ALTER TABLE inventory
ADD COLUMN in_stock BOOLEAN DEFAULT TRUE AFTER lost;

To reverse the operations:

ALTER TABLE inventory DROP COLUMN lost;
ALTER TABLE inventory DROP COLUMN in_stock;

To populate the columns initially:

UPDATE inventory
SET lost = FALSE;

UPDATE inventory
SET in_stock = inventory_in_stock(inventory_id);

-- checking
select * from inventory limit 5;

We now need to define triggers when we insert or update a rental row. We assume that a rental row will never be deleted.

The trigger after inserting a rental row is shown below. There may be an error. Can you spot it?

DELIMITER $$
CREATE TRIGGER insert_rental AFTER INSERT ON rental FOR EACH ROW
BEGIN
   IF (new.return_date IS NULL)
   THEN
      UPDATE inventory
      SET in_stock = FALSE
      WHERE inventory.inventory_id = rental.inventory_id;
   END IF;
END$$
DELIMITER ;

Note:

The trigger name, which can be used later.
The event that activates the trigger: "AFTER INSERT ON rental".
The "FOR EACH ROW" clause to indicate the action will be conducted for each new rental row insertion.
The keyword "new" is used to indicate the row after the activating event. The keyword old is used to indicate the row before the activating event

Checking:

-- Checking insert trigger.
SELECT * FROM rental WHERE inventory_id = 1;
SELECT * FROM inventory WHERE inventory_id = 1;

-- Rent inventory_id 1 out.
INSERT INTO rental
VALUES (16050,'2011-11-11 22:53:30',1,130,NULL,1,'2011-11-11 23:30:53');

SELECT * FROM rental WHERE inventory_id = 1;
SELECT * FROM inventory WHERE inventory_id = 1;

For the update trigger:

DELIMITER $$
CREATE TRIGGER update_rental AFTER UPDATE ON rental FOR EACH ROW
BEGIN
   IF ((old.return_date IS NULL) and (new.return_date IS NOT NULL))
   THEN
      UPDATE inventory
      SET in_stock = TRUE
      WHERE inventory.inventory_id = new.inventory_id;
   END IF;
END$$
DELIMITER ;

To check:

-- Checking insert trigger.
SELECT * FROM rental WHERE inventory_id = 1;
SELECT * FROM inventory WHERE inventory_id = 1;

UPDATE rental
SET return_date = '2011-11-11 23:30:53'
WHERE rental_id = 16050;

SELECT * FROM rental WHERE inventory_id = 1;
SELECT * FROM inventory WHERE inventory_id = 1;

-- House keeping.
DELETE FROM rental WHERE rental_id = 16050;