Database Lab 6

Due 5 days after the second day of this 2 week lab

In lab 4 you set up your own database, and Set up the tables you think are appropriate for an organization that takes orders and sells or distributes something. (Or something else of comparable complexity.) It could be a store selling fruits and vegetables, a food bank, a business selling computer parts, pottery, space ships, towels, or digital watches; it could even be a group distributing open-source software.
Not into the profit motive? How about a food bank that distributes donated groceries to clients, who have expressed a need for certain items (not everyone likes broccoli.) Or assigning tasks, such as lawn mowing, to condominium residents, and keeping a count of assignments made. Or making observations on trees in your woodlot.

In any case, you have some clients, or customers, who order things. Often they will order multiple quantities of several items (For example, 10 apples, 7 oranges, and 1 bottle of milk).

You have an inventory of your stock, and naturally, you can't sell or give away what you don't have, and also you don't want to be caught short. Thus, you should not accept an order for 10 apples unless you have at least that many, and if your stock goes below some threshold, you should get some more from your supplier (or make some more.) So, for each item in your inventory, you will need to know a short description, perhaps a part number, how many you have, the threshold amount (if you go below, time to reorder) amount to reorder, and (if you are selling them) the price.

Each order by a client should include the date, and several "line items" one for each different kind of thing. Each line item must identify the kind of thing and the amount (quantity) ordered. An "order" can have an explicit order number, or consist of all items for that client and date.


Documentation links

PostgreSQL documentation https://www.postgresql.org/docs/current/index.html .
Look for:
V.   Server Programming
      - Triggers
      - the Rule System
      - PL/pgSQL procedural Language
VI. Reference
    I. SQL Reference

Checks, constraints, triggers and rules:

Now that you have your basic tables (4 at least), the fun begins. In the first place, we don't want any negative amounts in this database. After all, you can't have -34 oranges, or give away -2 snowballs. Please prevent this from happening in your database.

The date on an order should be 'now' (current_date).

Whenever a line item is created (INSERT statement),  use a postgres RULE to subtract the amount from inventory.
Or, when a new observation is entered for a tree, add 1 to the number of observations for that tree.

When an inventory item goes below its threshold, a TRIGGER (or RULE) should insert a row in the MUST ORDER table (ha, yet another table). The attributes of this table should be the date, key of the item, and the quantity to order (from the inventory table)

Note: a RULE works silently. A trigger can RAISE INFO, NOTICE, WARN or EXCEPTION (aborts the transaction) , informing you of the low inventory, for example.

Somebody else's problem:

We will assume that somebody else in your business periodically looks at the MUST ORDER table, orders the stuff, and when it comes in, they add to the inventory and remove the must-order entry.

A function please ..

I want a function. If you create  a trigger, that requires that a function be created (in language 'plpgsql') The trigger should take some action, such as inserting a row in a "Must restock" table, and perhaps raising a NOTICE.
But if you use a RULE for restocking, then write some other function in plpgsql.

Please do the following with your function (to be called directly,  for instance select arrived('snow shovel',16); , or as a trigger, your choice):
We said that ordering more stock was "Somebody else's problem." Well, let's assume that the shipment of Snow Shovels has arrived. We count them, and there are not necessarily exactly the 25 we ordered. We have to either delete or modify the 'snow shovel' row of the "Must order" table, and we have to increase the quantity in the "Inventory" table by however many snow shovels actually arrived. It may or may not be necessary to order more snow shovels, this might be handled by your previous rules or triggers, if not, the function can insert a new "must order". An appropriate return value might be the new quantity.

Suppose we make a new observation on a tree. Compare it with the last observation. Something is wrong if it is smaller, or now alive after being reported dead. A notice would be in order.

So, Do either of the following:

  1. A TRIGGER to insert a row in "Must Restock," calling a Trigger function that raises a NOTICE, -- OR
  2. A RULE to insert a row in "Must Restock" and a function such as arrived (that can be called from a SELECT statement).

Back up your database

 The utility pg_dump writes a text file that will capture everything about a database, as SQL statements that will completely recreate it, with all its data. This file can be  used in the future, perhaps even on other hardware. Use terminal commands to save your database (same name as your username)

Do this at the end of today's lab, and submit the file

What to submit:

What I expect to find there:

Constraints, triggers and rules for tables of inventory, orders and must-order. (Command to show all of this for table mesa is: \d mesa)
    1. Constraint on quantity, cannot be negative
    2. foreign keys must be present in related table
    3. A rule for insert of an order
    4. Any trigger you created.


Notes:


Rules and triggers

are described in the PostgreSQL  manual, section V, Server Programming.

A rule has the form:
CREATE [ OR REPLACE] RULE name_the_rule AS 
ON event
TO object [WHERE rule_qualification]
DO [INSTEAD] [action | (actions) | NOTHING];
So you can do one or more actions (insert, update, delete, etc.) instead of, or in addition to, the event that invoked the rule. Event takes the form, update to tablename

Triggers (V. 35.) invoke a function, written in a procedural language, we will have to look at that first.

plpgsql

See manual, section V. 37. PL/pgSQL, Procedural languages. See simple example below.

It uses SQL syntax, with the addition of variables and IF statements.
(functions can also be written in sql, but not for use with triggers)


A sample function:

plpgsql is a language resembling SQL, it also has things like IF statements. This example could be used by a shortsighted administrator who stored ages instead of birth dates or birth years, in order to add some number of years to all the ages, when we realize that the data is that many years out of date. $1 refers to the first argument. The "info" is a string that will be output to the user of the function. You would use it like this:
select future(3);
create or replace function future(integer) returns integer as $$
begin
raise info 'People set to future ages in % years',$1;
update people set age = age + $1;
return (select avg(age) from people);
end
$$ language plpgsql;

A trigger function example, restricting updates to enroll

create or replace function onlyself() returns trigger as $$
declare
user TEXT; -- will only allow user to register or deregister herself
begin
select into user current_user;
IF TG_OP = 'INSERT' THEN -- may be triggered on various operations
IF (new.linux <> user) THEN
RAISE NOTICE 'Let % register herself', new.linux;
RETURN NULL;
END IF;
ELSIF old.linux <> user THEN -- update or delete
RAISE NOTICE '% is not authorized to to change %''s enrollment',
user, old.linux;
RETURN NULL;
END IF;
IF TG_OP = 'DELETE' THEN
return old; -- proceed with delete
ELSE
return new; -- proceed with update (or insert)
END IF;
return null; -- should not happen
end;
$$ language plpgsql;

create trigger self before insert or update or delete
on enroll for each row execute procedure onlyself();

How to get information, and debugging functions

To connect to timetable and see the above function for example, you do this:
timetable=> \d    	-- see list of tables  (note you aren't asked for password)
timetable=> \d enroll -- see details, including Triggers
timetable=> \df+ onlyself -- see the function
Now that the trigger is implemented, you can delete courses for yourself, but not others.


How to create a trigger

  1. First write your trigger procedure (create or replace function) on a file, and use \i to create and later replace it.
  2. Write the CREATE TRIGGER statement
  3. Do
    1. Test your trigger using a statement that should fire it
    2. Only then will the function be interpreted, and any syntax or logic errors detected
    3. Revise your file, and use \i again. You do not have to recreate the trigger
  4. Until you are happy with the result. (Start debugging - try some table modifications. At this point you will start discovering syntax and then logic errors.)

plpgsql

This language resembles Pascal. It includes SQL statements, assignment, IF, WHILE and FOR constructs, and RAISE for output messages
Important points:

What's special about a Trigger procedure

The create trigger statement

You may want a trigger to fire on insert, update, delete or some combination. The function will be called for each row of the relation (examples in italics)
CREATE TRIGGER care
     
BEFORE INSERT OR UPDATE ON test
FOR EACH ROW EXECUTE PROCEDURE careful();
See complete  example

Backing up a database

Database software is very reliable, and able to recover from a variety of failures. However, you should back up anything important, just in case you delete something by accident. The utility pg_dump writes a text file that will capture everything about a database, as SQL statements that will completely recreate it, with all its data. This file can be  used in the future, perhaps even on other hardware.
These terminal commands save the database
with the same name as your username to a file with a name of your choosing, and then submit it as usual.
pg_dump -Oxf yourdatabasename.sql		#note, big-Oh, not a hex number!
submit cs307 yourdatabasename.sql 

It is also a very good practice to do this frequently, just in case....

There are several ways to use the file created by pg_dump. You can delete all the rows from a table, and then use the COPY statement for that table to restore the data, for example.

You could also drop a table, and use the create table statement to restore the previous structure, and then the COPY.

Tell me what you did

Your Name:
Your Username:
Tell me what you did:

and submit your pg_dump file submit cs307 you.sql