460 likes | 571 Views
PostgreSQL Customer Order Example. S511. SLIS Postgresql server. PHP PGAdmin https://ella.slis.indiana.edu/adm/phppgadmin/ Get your username and password from SLIS IT department. Create Schema. Create Schema. Create Table Schema. Create Tables. M. 1. M. 1. CUSTOMER. order.
E N D
SLIS Postgresql server • PHP PGAdmin • https://ella.slis.indiana.edu/adm/phppgadmin/ • Get your username and password from SLIS IT department
Create Tables M 1 M 1 CUSTOMER order ORDERINFO has ORDERLINE 1 has 1 M 1 STOCK has ITEM M has 1 BARCODE
Create Tables create table customer ( customer_id serial , title char(4) , fnamevarchar(32) , lnamevarchar(32) not null, addresslinevarchar(64) , town varchar(32) , zipcode char(10) not null, phone varchar(16) , CONSTRAINT customer_pk PRIMARY KEY(customer_id) );
Create Tables create table item ( item_id serial , description varchar(64) not null, cost_price numeric(7,2) , sell_price numeric(7,2) , CONSTRAINT item_pk PRIMARY KEY(item_id) ); create table orderinfo ( orderinfo_id serial , customer_id integer not null, date_placed date not null, date_shipped date , shipping numeric(7,2) , CONSTRAINT orderinfo_pk PRIMARY KEY(orderinfo_id) );
Create Tables create table stock ( item_id integer not null, quantity integer not null, CONSTRAINT stock_pk PRIMARY KEY(item_id) ); create table orderline ( orderinfo_id integer not null, item_id integer not null, quantity integer not null, CONSTRAINT orderline_pk PRIMARY KEY(orderinfo_id, item_id) ); create table barcode ( barcode_ean char(13) not null, item_id integer not null, CONSTRAINT barcode_pk PRIMARY KEY(barcode_ean) );
Insert values -- Customers insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Miss','Jenny','Stones','27 Rowan Avenue','Hightown','NT2 1AQ','023 9876'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Andrew','Stones','52 The Willows','Lowtown','LT5 7RA','876 3527'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Miss','Alex','Matthew','4 The Street','Nicetown','NT2 2TX','010 4567'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Adrian','Matthew','The Barn','Yuleville','YV67 2WR','487 3871'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Simon','Cozens','7 Shady Lane','Oakenham','OA3 6QW','514 5926'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Neil','Matthew','5 Pasture Lane','Nicetown','NT3 7RT','267 1232'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Richard','Stones','34 Holly Way','Bingham','BG4 2WE','342 5982'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mrs','Ann','Stones','34 Holly Way','Bingham','BG4 2WE','342 5982'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mrs','Christine','Hickman','36 Queen Street','Histon','HT3 5EM','342 5432'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Mike','Howard','86 Dysart Street','Tibsville','TB3 7FG','505 5482'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Dave','Jones','54 Vale Rise','Bingham','BG3 8GD','342 8264'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Richard','Neill','42 Thatched Way','Winnersby','WB3 6GQ','505 6482'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mrs','Laura','Hardy','73 Margarita Way','Oxbridge','OX2 3HX','821 2335'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Bill','O\'Neill','2 Beamer Street','Welltown','WT3 8GM','435 1234'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','David','Hudson','4 The Square','Milltown','MT2 6RT','961 4526');
Insert values -- Items insert into item(description, cost_price, sell_price) values('Wood Puzzle', 15.23, 21.95); insert into item(description, cost_price, sell_price) values('Rubik Cube', 7.45, 11.49); insert into item(description, cost_price, sell_price) values('Linux CD', 1.99, 2.49); insert into item(description, cost_price, sell_price) values('Tissues', 2.11, 3.99); insert into item(description, cost_price, sell_price) values('Picture Frame', 7.54, 9.95); insert into item(description, cost_price, sell_price) values('Fan Small', 9.23, 15.75); insert into item(description, cost_price, sell_price) values('Fan Large', 13.36, 19.95); insert into item(description, cost_price, sell_price) values('Toothbrush', 0.75, 1.45); insert into item(description, cost_price, sell_price) values('Roman Coin', 2.34, 2.45); insert into item(description, cost_price, sell_price) values('Carrier Bag', 0.01, 0.0); insert into item(description, cost_price, sell_price) values('Speakers', 19.73, 25.32);
Insert values -- Barcodes insert into barcode(barcode_ean, item_id) values('6241527836173', 1); insert into barcode(barcode_ean, item_id) values('6241574635234', 2); insert into barcode(barcode_ean, item_id) values('6264537836173', 3); insert into barcode(barcode_ean, item_id) values('6241527746363', 3); insert into barcode(barcode_ean, item_id) values('7465743843764', 4); insert into barcode(barcode_ean, item_id) values('3453458677628', 5); insert into barcode(barcode_ean, item_id) values('6434564564544', 6); insert into barcode(barcode_ean, item_id) values('8476736836876', 7); insert into barcode(barcode_ean, item_id) values('6241234586487', 8); insert into barcode(barcode_ean, item_id) values('9473625532534', 8); insert into barcode(barcode_ean, item_id) values('9473627464543', 8); insert into barcode(barcode_ean, item_id) values('4587263646878', 9); insert into barcode(barcode_ean, item_id) values('9879879837489', 11); insert into barcode(barcode_ean, item_id) values('2239872376872', 11);
Insert values -- Stock insert into stock(item_id, quantity) values(1,12); insert into stock(item_id, quantity) values(2,2); insert into stock(item_id, quantity) values(4,8); insert into stock(item_id, quantity) values(5,3); insert into stock(item_id, quantity) values(7,8); insert into stock(item_id, quantity) values(8,18); insert into stock(item_id, quantity) values(10,1); -- Order info insert into orderinfo(customer_id, date_placed, date_shipped, shipping) values(3,'03-13-2004','03-17-2004', 2.99); insert into orderinfo(customer_id, date_placed, date_shipped, shipping) values(8,'06-23-2004','06-24-2004', 0.00); insert into orderinfo(customer_id, date_placed, date_shipped, shipping) values(15,'09-02-2004','09-12-2004', 3.99); insert into orderinfo(customer_id, date_placed, date_shipped, shipping) values(13,'09-03-2004','09-10-2004', 2.99); insert into orderinfo(customer_id, date_placed, date_shipped, shipping) values(8,'07-21-2004','07-24-2004', 0.00);
Insert values -- Order line insert into orderline(orderinfo_id, item_id, quantity) values(1, 4, 1); insert into orderline(orderinfo_id, item_id, quantity) values(1, 7, 1); insert into orderline(orderinfo_id, item_id, quantity) values(1, 9, 1); insert into orderline(orderinfo_id, item_id, quantity) values(2, 1, 1); insert into orderline(orderinfo_id, item_id, quantity) values(2, 10, 1); insert into orderline(orderinfo_id, item_id, quantity) values(2, 7, 2); insert into orderline(orderinfo_id, item_id, quantity) values(2, 4, 2); insert into orderline(orderinfo_id, item_id, quantity) values(3, 2, 1); insert into orderline(orderinfo_id, item_id, quantity) values(3, 1, 1); insert into orderline(orderinfo_id, item_id, quantity) values(4, 5, 2); insert into orderline(orderinfo_id, item_id, quantity) values(5, 1, 1); insert into orderline(orderinfo_id, item_id, quantity) values(5, 3, 1);
SQL Queries • select town, lname AS "Last Name" from customer order by town DESC, lname ASC; • select distinct town from customer; • select description, cost_price*100 as "Cost Price" from item;
SQL Queries • select title, fname, lname, town from customer where title <> 'Mr' and (town='Bingham' or town='Nicetown'); • select title, fname, lname, town from customer where title <> 'Mr' and town in ('Bingham','Nicetown');
SQL Queries • select distinct town from customer where town between 'B' and 'N'; • select distinct town from customer where town between 'B' and 'Nz';
SQL Queries • select fname, lname from customer where fname like '_a%'; • select customer_id, town from customer limit 5 offset 2;
SQL Queries • select * from orderinfo where date_part('month', date_placed)=9; • select * from orderinfo where date_placed>=cast('2004 07 21' as date); • select date_shipped - date_placed from orderinfo;
More tables • select customer.fname, orderinfo.date_placed from customer, orderinfo where customer.fname='Ann' and customer.lname='Stones' and customer.customer_id=orderinfo.customer_id; • select description, cost_price, barcode_ean from item, barcode where barcode.item_id=item.item_id order by cost_price;
More tables • select cu.fname, oi.date_placed from customer cu, orderinfo oi where cu.fname='Ann' and cu.lname='Stones' and cu.customer_id=oi.customer_id;
More tables • select customer.fname, customer.lname, orderinfo.date_placed, item.description, orderline.quantity from customer, orderinfo, orderline, item where customer.customer_id=orderinfo.customer_id and orderinfo.orderinfo_id=orderline.orderinfo_id and orderline.item_id=item.item_id and customer.fname='Ann' and customer.lname='Stones';
More tables select customer.fname, customer.lname, orderinfo.date_placed, item.description, orderline.quantity from customer join orderinfo on customer.customer_id=orderinfo.customer_id join orderline on orderinfo.orderinfo_id=orderline.orderinfo_id join item on orderline.item_id=item.item_id where customer.fname='Ann' and customer.lname='Stones';
SQL Queries • select count(*) from customer where town='Bingham'; • select count(*), lname, town from customer group by town, lname; • select count(*), lname, town from customer group by town, lname order by town, lname;
SQL Queries • select count(*), town from customer group by town having count(*)>1; • select count(*), lname, town from customer where town <> 'Lincoln' group by lname, town having count(*)>1; • select count(*), lname, town from customer where town <> 'Lincoln' group by lname, town order by town;
SQL queries • select customer_id from customer where phone IS NULL; • select count(phone) from customer; • select count(distinct town) as "distinct", count(town) as "all" from customer;
SQL Queries • select avg(shipping) from orderinfo; • select avg(distinct shipping) from orderinfo;
Subquery • select * from item where cost_price>cast(7.249 as numeric(7,2)); • select * from item where cost_price> (select avg(cost_price) from item); • select * from item where cost_price> (select avg(cost_price) from item) and sell_price < (select avg(sell_price) from item);
Subquery • select * from stock where item_id in (select item_id from item where cost_price > cast(10.0 as numeric(7,2))); • select oi.date_placed from orderinfo oi where oi.customer_id= (select c.customer_id from customer c where c.customer_id=oi.customer_id and town='Bingham');
Subquery • select i.item_id from item i where i.item_id not in (select i.item_id from item i, stock s where i.item_id=s.item_id);
Join • select i.item_id, s.quantity from item i left outer join stock s on i.item_id=s.item_id; • select i.item_id, i.cost_price, s. quantity from item i left outer join stock s on i.item_id=s.item_id and s.quantity >2 where i.cost_price > cast(5.0 as numeric(7,2));
View • create view item_price as select item_id, description::varchar(10), sell_price as price from item where sell_price <=20.0; • select * from item_price; • Drop view item_price;
Transactions Initial State BEGIN First SQL Second SQL Rollback Initial State BEGIN First SQL Second SQL COMMIT …
Transactions create table ttest1 ( ival1 integer, sval1 varchar(64) ); create table ttest2 ( ival2 integer, sval2 varchar(64) );
Transactions • insert into ttest1(ival1, sval1) values(1, 'David'); • begin; update ttest1 set sval1='Dave' where ival1=1; select sval1 from ttest1 where ival1=1; rollback; • select sval1 from ttest1 where ival1=1;
Transactions delete from ttest1; delete from ttest2; insert into ttest1(ival1, sval1) values (1, 'David'); begin; insert into ttest2(ival2, sval2) values (42, 'Arthur'); update ttest1 set sval1='Robert' where ival1=1; select * from ttest1; select * from ttest2; ------------------- Rollback; select * from ttest1; select * from ttest2;
Functions • create language plpgsql; • create function add_one(int4) returns int4 as 'begin return $1+1; end; 'language 'plpgsql'; • select add_one(2) as answer; • Drop function add_one(int4);
Procedure create table reorders ( item_id integer, message text ); -- reorders -- scan the stock table to raise reorders of item low on stock create function reorders(min_stock int4) returns integer as $$ declare reorder_item integer; reorder_count integer; stock_row stock%rowtype; msg text;
Procedure begin select count(*) into reorder_count from stock where quantity <= min_stock; for stock_row in select * from stock where quantity <= min_stock loop declare item_row item%rowtype; begin select * into item_row from item where item_id = stock_row.item_id; msg = 'order more ' || item_row.description || 's at ' || to_char(item_row.cost_price,'99.99'); insert into reorders values (stock_row.item_id, msg); end; end loop; return reorder_count; end; $$ language plpgsql; select reorders(3); select * from reorders;
Triggers • Trigger: can be used to execute a stored procedure when certain actions are taken, like INSERT, DELETE, UPDATE in a table. • The combination of stored procedures and triggers gives us the power to enforce quite sophisticated business rules (such as defined constraints) • To use a trigger, we need to first define a trigger procedure, then create the trigger which will execute the trigger procedure.
Triggers • A trigger procedure is created with the CREATE FUNCTION command, declaring it as a function with no arguments and a return type of trigger. • The function must be declared with no arguments even if it expects to receive arguments specified in CREATE TRIGGER — trigger, arguments are passed via TG_ARGV,
Triggers create function reorder_trigger() returns trigger AS $$ declare mq integer; item_record record; begin mq := tg_argv[0]; raise notice 'in trigger, mq is %', mq; if new.quantity <= mq then select * into item_record from item where item_id = new.item_id; insert into reorders values (new.item_id, item_record.description); end if; return NULL; end; $$ language plpgsql; create trigger trig_reorder after insert or update ON stock for each row execute procedure reorder_trigger(3); update stock set quantity=3 where item_id=1; select * from reorders;
Triggers • Drop triggers • drop trigger trig_reorder4 on stock • drop function reorder_trigger3()
Triggers create function customer_trigger() returns trigger AS $$ declare order_record record; begin -- about to delete a customer -- disallow if orders pending select * into order_record from orderinfo where customer_id = old.customer_id and date_shipped is NULL; if not found then -- all OK, delete of customer can proceed raise notice 'deletion allowed: no outstanding orders'; raise notice 'old.customer_id is %', old.customer_id; return NULL; -- for referential integrity we have to tidy up -- we will need to delete all completed orders -- but first delete the information about the orders
Triggers for order_record in select * from orderinfo where customer_id = old.customer_id loop delete from orderline where orderinfo_id = order_record.orderinfo_id; end loop; -- now delete the order records delete from orderinfo where customer_id = old.customer_id; -- return the old record to allow customer to be deleted return old; else -- orders present return NULL to prevent deletion raise notice 'deletion aborted: outstanding orders present'; return NULL; end if; end; $$ language plpgsql;
Triggers create trigger trig_customer before delete on customer for each row execute procedure customer_trigger(); update orderinfo set date_shipped=NULL where orderinfo_id=3; select * from orderinfo; delete from customer where customer_id=15; select * from orderinfo; delete from customer where customer_id=3; select * from orderinfo;