C20.0046- Database Management SystemsLecture #22.ppt

1、M.P. Johnson, DBMS, Stern/NYU, Spring 2005,1,C20.0046: Database Management Systems Lecture #22,M.P. Johnson Stern School of Business, NYU Spring, 2005,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,2,Homework,Project part 5 Topic: web interface + any remaining loose ends Up now Due: end of semesterWill

2、return proj3 today Remind me!,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,3,Agenda,Programming for SQL: DB-conn from web scripting languages DBI/DBDs in Perl, PHPTransactionsNext: Security Secrecy Integrity Availability Web issues,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,4,Goals: after this week,Af

3、ter Today: Have all the tools for building a DB-backed website in Perl or PHP (but will it be secure?),M.P. Johnson, DBMS, Stern/NYU, Spring 2005,5,Review: PHP,Image from http:/www.scit.wlv.ac.uk/jphb/cp3024/,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,6,Form example,On clicking Send, we go to the sa

4、me page, but with “name=99&sumbit=OK”,http:/pages.stern.nyu.edu/mjohnson/dbms/perl/input.cgi, Enter a number:,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,7,Review: dynamic webpages,First option: for each request: run program, produce whole page, send back CGI for each response, fill in the wholes and

5、 send back Embedded scripting PHP and others PHP = Personal Home Page or= PHP Hypertext Processor,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,8,hello.php,http:/pages.stern.nyu.edu/mjohnson/dbms/php/hello.phpQ: What the difference between and n?, Hello from PHPHere is the PHP part: n“; ? Thats it! ,M.

6、P. Johnson, DBMS, Stern/NYU, Spring 2005,9,hello2.php,Script errors, w/ and w/o display_errors on: http:/pages.stern.nyu.edu/mjohnson/dbms/perl/hello2.php http:/pages.stern.nyu.edu/mjohnson/dbms/php/hello2.phpLocal dir must contain .htaccess:Automatically load GET/POST params as vars http:/pages.ste

7、rn.nyu.edu/mjohnson/dbms/php/.htaccess,php_flag display_errors on php_flag register_globals on,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,10,More on PHP,Somewhat C-like, somewhat Perl-like Case-sensitive Strings: Concatenation op: . Single, double quotes similar to Perl Comments: # Unix shell-style

8、/* */ C-style / C+-style Output: echo(“hi there”); print(“hi there”); Cs printf,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,11,PHP vars,Similar to those of Perl, except no “my” http:/pages.stern.nyu.edu/mjohnson/dbms/php/math.php,“;print “Second number “ . $num2 . “;$total = $num1 + $num2;print “The

9、sum is “ . $total . “; ?,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,12,Combining PHP and HTML,http:/pages.stern.nyu.edu/mjohnson/dbms/php/combine.php,Iteration number ,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,13,PHP info,PHP does not have both string and number ops like Perl Number ops treat (numb

10、er) strings as numbers, regular strings as strings http:/pages.stern.nyu.edu/mjohnson/dbms/php/test.phpInfo function displays lots of server info: http:/pages.stern.nyu.edu/mjohnson/dbms/php/info.php,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,14,PHP & MySQL,PHP 5 has a DBI/JDBC-like interface Our ve

11、rsion/setup uses a proprietary lib:Open a connection and open our DB:Run query:,$db = mysql_connect(“mysql2.stern.nyu.edu:3306“, user, pass); mysql_select_db(“test“, $db);,$result = mysql_query($query,$db);,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,15,PHP & MySQL,Extract next row of data from state

12、ment, if available:What this means: myrow is an array that can then be accessed Other options, but this should suffice In general, to scroll through results, do:,$myrow = mysql_fetch_row($result),while ($myrow = mysql_fetch_row($result)# print rows data,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,16,

13、Limit: PHP webpages that do something,Semi-interesting Perl script: http:/pages.stern.nyu.edu/mjohnson/dbms/php/lookup.php Non-trivial but not huge: 60 lines, but much of its plain html Works with two-column (a,b) table Takes input from user Returns rows whose a field contains value If no/empty inpu

14、t, returns all rows Bad idea in general!,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,17,lookup.php: port of lookup.cgi,Two possible situations for running script: Page opened for the first time User entered parameter and pressed button Structure of file: Print input box and button for next search On

15、button click, parameter is sent to this pages url (Try to) read input parameter Open MySQL connection Run query Print results in a table Disconnect from MySQL,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,18,Insert/delete Perl/PHP example,Similar to search exampleNB: form has two buttonshttp:/pages.ste

16、rn.nyu.edu/mjohnson/dbms/perl/update.cgi http:/pages.stern.nyu.edu/mjohnson/dbms/perl/updatecgi.txthttp:/pages.stern.nyu.edu/mjohnson/dbms/php/update.php http:/pages.stern.nyu.edu/mjohnson/dbms/php/updatephp.txt,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,19,Master-detail Perl/PHP example,Idea: displ

17、ay list of regions; When region clicked on, display its countriesMechanism: pass GET param in link, not with a FORMhttp:/pages.stern.nyu.edu/mjohnson/dbms/php/cia.php?id= http:/pages.stern.nyu.edu/mjohnson/dbms/php/ciaphp.txthttp:/pages.stern.nyu.edu/mjohnson/dbms/perl/cia.cgi http:/pages.stern.nyu.

18、edu/mjohnson/dbms/perl/cia.pl,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,20,Tutorials on PHP,Some material drawn from the following good tutorials: http:/PHP introduction and examples: http:/www.scit.wlv.ac.uk/jphb/sst/php/ Interactive PHP with database access: http:/www.scit.wlv.ac.uk/jphb/sst/php/

19、gazdb.html Longer PHP/MySQL Tutorial from webmonkey: http:/ insert/update/delete example from webmonkey: http:/ MySQL/Perl/PHP page from U-Wash: http:/www.washington.edu/computing/web/publishing/mysql-script.html,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,21,Pros & cons,PHP v. Perl v. Java servlets

20、v. : http:/ is fast Perl has JDBC-like DBI/DBD interface PHP is fast Perl is good for much more than web dev,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,22,Advice for use of novel languages,Rerun often Dont write the whole thing and then try to runUse frequent prints to be sure of var vals (While deb

21、ugging)When stuck, picture continuum from your current program to some other program other prog. works but doesnt do what you want change either/both, step by step, until they meet in the middleGoogle is your friend Search for error messages, situations,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,23,

22、Thats really all, folks!,Q: Is this enough to get a job coding PHP? A: Again, probably not. But: most jobs are just programming-in-PHP or administering-Oracle Being able to acquire new skills when needed is a good thingBut: again pretty easy to produce a semi-interested site with a few copies of loo

23、kup.php and cia.php.Dont like PHP either? Lots of other choices, but again, youre strongly discouraged from using something else for your project unless you know what youre doing.,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,24,New-old topic: Transactions,So far, have simply issued commands Ignored xa

24、ctsRecall, though: an xact is an operation/set of ops executed atomically In one instant ACID test: Xacts are atomic Each xact (not each statement) must leave the DB consistent,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,25,Default xact behavior,An xact begins upon login By default, xact lasts until

25、logoff Except for DDL statements They automatically commitExamples with two views of emp,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,26,Direct xact instructions,At any point, may explicitly COMMIT: SQL COMMIT; Saves all statements entered up to now Begins new xactConversely, can ROLLBACK SQL ROLLBACK

26、 Cancels all statements entered since start of xactExample: delete from emp; or delete junk;,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,27,Direct xact instructions,Remember, DDL statements are auto-committed They cannot be rollbackedExamples:Q: Why doesnt rollback “work”?,drop table junk; rollback;

27、truncate table junk; rollback;,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,28,Savepoints,Xacts are atomic Can rollback to beginning of current xactBut might want to rollback only part wayMake 10 changes, make one bad change Want to: roll back to before last changeDont have Word-like multiple undo Bu

28、t do have savepoints,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,29,Savepoints,Create a savepoint:emp example:,-changes SAVEPOINT sp1; -changes SAVEPOINT sp2; -changes SAVEPOINT sp3 -changes ROLLBACK TO sp2; ROLLBACK TO sp1;,SAVEPOINT savept_name;,Can skip savepoints But can ROLLBACK only backwards C

29、an ROLLBACK only to last COMMIT,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,30,AUTOCOMMIT,Finally, can turn AUTOCOMMIT on: SQL SET AUTOCOMMIT ON; Can put this in your config file Can specify through JDBC, etc.Then each statement is auto-committed as its own xact Not just DDL statements,M.P. Johnson, DBMS, Stern/NYU, Spring 2005,31,For next time,Read chapter 21 Lots of interesting security topicsStart proj5!,