Previous weeks Database-enabled web technology DB Programming - PDF document

Previously in this course . . . Previous weeks Database-enabled web technology DB Programming ◮ Introductions on ◮ PHP ◮ git Instructor: C ¸a˘ grı C ¸¨ oltekin ◮ A summary of DB design and SQL. c.coltekin@rug.nl ◮ An introduction to web programming, Information science/Informatiekunde ◮ Background: HTTP, CGI, networking, . . . ◮ Interacting with user in PHP: handling form input. Fall 2011/12 C ¸. C ¸¨ oltekin, Informatiekunde Databases & Web 1/35 Overview N-tier system: some theory Today The multi-tier (or 3-tier) architecture Application Presentation Data Application Database Client Web server server server ◮ A short discussion of the N-tier software architecture. ◮ Stored procedures. Presentation tier interacts with the user (e.g., ask the seat preference in an airline online check-in system). ◮ Accessing databases from PHP using Pear DB library. Application tier implements the ‘business logic’ (e.g., check and ◮ Transactions. reserve a seat, possibly using multiple queries and ◮ Triggers (a short introduction). updates). Data tier stores the data (e.g., retrieve and/or update the relevant data records). In practice, division may not match the figure above. However separating presentation from application is always a good idea. C ¸. C ¸¨ oltekin, Informatiekunde Databases & Web 2/35 C ¸. C ¸¨ oltekin, Informatiekunde Databases & Web 3/35 N-tier system: some theory Stored procedures The N-tier architecture Stored procedures Stored procedures are general purpose programming procedures Presentation Application Data on a DBMS. ◮ Stored procedures support all typical general purpose ◮ Sometimes application (business logic) tasks can be shifted programming constructs (variables, conditional execution, towards the database (after all, the database design is based loops, . . . ) on the ‘business logic’). ◮ They are database objects, and stored in the database. ◮ Often, the presentation and application tasks reside in a single application (e.g., your PHP code). create procedure get_books () ◮ Even if the system will not have another interface, separating begin presentation and the application tasks logically is a good idea. select * from book; end ◮ In typical web-based application development, the presentation tasks are shared between the client (web browser) call get_books; and the server side programs (you PHP/HTML code). C ¸. C ¸¨ oltekin, Informatiekunde Databases & Web 4/35 C ¸. C ¸¨ oltekin, Informatiekunde Databases & Web 5/35 Stored procedures Stored procedures Why (not) use stored procedures? Stored procedure implementations + You put all your ‘business logic’ into one place. ◮ ANSI standard for stored procedure language is called + They are (typically) faster than individual SQL queries. SQL/PSM. + They reduce the network usage. ◮ Many vendors implemented their own languages, e.g., Oracle + They may provide convenient ways of access control, and may PL/SQL. Even if they do, the level standard compliance tends be useful to prevent some security problems. to be varied. ◮ Many DBMS systems support stored procedures written in − Syntax is incompatible between different DBMSes. more common languages as well: Java, C, perl, . . . , even PHP − Typically SPs are more difficult to debug. (PostgreSQL). − Puts a bigger burden on DBMS. ◮ We will go through basics of SQL/PSM as implemented by MySQL (version 5+). Note: The issue of stored procedures vs. inline SQL code may easily get into a heated discussion. Use when it makes sense. C ¸. C ¸¨ oltekin, Informatiekunde Databases & Web 6/35 C ¸. C ¸¨ oltekin, Informatiekunde Databases & Web 7/35

Stored procedures Stored procedures Stored procedures in MySQL SP variables delimiter $$ ◮ You can use local variables in an stored procedure. create procedure get_books () ◮ You have to declare all local variables before the actual code begin starts. For examaple: select * from book; end $$ declare customer_id int; delimiter ; ◮ The keyword set is used for variable assignments. ◮ call get_books(); calls the procedure. set customer_id = 10; ◮ show procedure status; lists the stored procedures in the ◮ You can define or use so-called session variables which are database. accessible throughout the same database connection. Session ◮ show create procedure get_books(); lists the procedure variables start with a ‘ @ ’. code. set @update_status = ’success; ◮ drop procedure get_books; drops it. ... ◮ Change of delimiter is a trick to be able to use multiple select @update_status ; statements with the default statement delimiter ‘ ; ’. C ¸. C ¸¨ oltekin, Informatiekunde Databases & Web 8/35 C ¸. C ¸¨ oltekin, Informatiekunde Databases & Web 9/35 Stored procedures Stored procedures SP arguments SP control structures Stored procedures support basic control structures. ◮ if-then-else: ◮ As expected, stored procedures can take arguments, if x = 0 then create procedure set @status = ’x = 0’; confirm_order (in cid int , out status varchar (10)) elsif x < 10 then ◮ The arguments are defined to be one of set @status = ’0 < x < 10’; else in arguments are read-only. set @status = ’x > 10’; out arguments are set inside the procedure, they do end if; not have to be defined before. inout arguments are read, and modified by the stored ◮ case procedure. case x when 0 then set @status = ’x = 0’; call confirm_order (10, @status ); when 1 then set @status = ’x = 1’; select @status; else set @status = ’not 0 or 1’; end case; C ¸. C ¸¨ oltekin, Informatiekunde Databases & Web 10/35 C ¸. C ¸¨ oltekin, Informatiekunde Databases & Web 11/35 Stored procedures Stored procedures SP loops SP: cursors ◮ while while <condition > do ◮ A cursor is a pointer to a row of a table, or a query result. ... end while; ◮ Like local variables, you need to declare the cursor before ◮ repeat using it: repeat declare cur cursor for select * from book; ... ◮ To start using it, you need to use the statement open . until <condition > end repeat; ◮ fetch reads the row, and moves the cursor to the next row, ◮ loop fetch cur into isbn , author , title; <loop_label >: loop (assuming isbn, author and title are previously defined ... variables) if <condition > then leave <loop_label >: end if; end loop <loop_label >; C ¸. C ¸¨ oltekin, Informatiekunde Databases & Web 12/35 C ¸. C ¸¨ oltekin, Informatiekunde Databases & Web 13/35 Stored procedures Stored procedures SP in MySQL an example Stored procedures: access control 1 drop procedure if exists confirm_order; 2 delimiter $$ 3 create procedure confirm_order(in cust_id int , out nitems int) 4 begin ◮ Stored procedures can be used to restrict direct access to 5 declare isbn_tmp varchar (13) default null; 6 declare customer , quantity int; database tables. 7 declare more_rows bool default true; 8 declare cur cursor for ◮ The stored procedures are run with the database user who 9 select cID , ISBN , qty from basket where cID = cust_id; created them. 10 declare continue handler for not found set more_rows = false; 11 set nitems = 0; ◮ The other users can execute a stored procedure even if they 12 open cur; 13 have no rights to access the tables used by the stored fetch cur into customer , isbn_tmp , quantity; 14 while more_rows do procedures. 15 set nitems = nitems + quantity; 16 insert into orders (cID , ISBN , qty , order_date , status) ◮ The rights are granted (and taken away) as in any other 17 values (customer , isbn_tmp , quantity , now(), ’N’); 18 database object, using grant and revoke SQL statements. fetch cur into customer , isbn_tmp , quantity; 19 end while; 20 end $$ 21 delimiter ; call confirm_order(10, @nbooks); select @nbooks; C ¸. C ¸¨ oltekin, Informatiekunde Databases & Web 14/35 C ¸. C ¸¨ oltekin, Informatiekunde Databases & Web 15/35