Database Lab 6

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

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

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:

A function please ..

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

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

What to submit:

• A brief description of your business, Do the Assignment if you haven't yet done so! (Else it's done.)
  
• A brief account of what you did and accomplished in this week's lab. In particular, what rules you defined, and what your function is named, and what it does. submit using form below.
  
• A pg_dump of your database, as of after you have done all the lab work (use .sql extension)
  Submit as usual, with submit cs307 you.sql

What I expect to find there:

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

CREATE [ OR REPLACE] RULE name_the_rule AS 
    ON event
    TO object [WHERE rule_qualification]
    DO [INSTEAD] [action | (actions) | NOTHING];

plpgsql

A sample function:

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

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

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

• The entire body of the function is a string, surrounded by single quotes or $$. Since you may have single quotes in the function, if you use '   you need to double them, as in: IF NEW.linux = ''jensen'' THEN   ... so use the superquote $$ as above instead.
  
• It will be executed on the server, so it doesn't know about your home directory on the client
• If you RAISE EXCEPTION, the entire SQL statement will be aborted (transaction rolled back). The same is true of any syntax error.
  

What's special about a Trigger procedure

• The argument list is empty, and it RETURNS TRIGGER
• You have access to special variables OLD and NEW, as well as TG_OP (either 'INSERT', 'UPDATE' or 'DELETE') and some others.
• You must RETURN NEW; (or RETURN NULL;)

The create trigger statement

CREATE TRIGGER care
     
BEFORE INSERT OR UPDATE ON test 
FOR EACH ROW EXECUTE PROCEDURE careful();

Backing up a database

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....

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