Developing MySQL Database Applications

1. Developing MySQL Database Applications 6th IT Support Staff Conference Andrew Slater (IT Support Officer: Phonetics & Modern Languages) and John Ireland (Computing Manager, Jesus College)

2. Workshop Synopsis • Introduction to MySQL • Open Database Connectivity (ODBC) • Home-cooking: writing clients • Interacting with the web / PHP • Other APIs explained • Illustration: college noticeboard • Links and references

3. What IS MySQL?

4. Welcome to MySQL • Relational Database Management System (RDBMS) • Open source (GNU Public License) • MySQL server: Windows 9x/NT/2000, Linux, Solaris, OS/2, BSD… • Clients can be different platforms, both via legacy interfaces and open standards (e.g. ODBC) www.mysql.com

5. Features and Compliance • ANSI SQL92 (almost!) except: • Sub-select SELECT * FROM table1 WHERE id IN (SELECT id FROM table2); • SELECT INTO table… • Multi-threaded (good multi-processor performance) • Handles large files (e.g. 200GB) efficiently • Flexible security model • Highly optimised JOINs

6. Performance / Benchmarks • Comparison of competing DBMSs • Identical hardware for each test • Same platform / OS for each test • Graphs shown are summary from MySQL web site

7. MySQL / PostgreSQL

8. MySQL / Access 2000

9. Smart Datatypes • AUTONUMBER fields are available: a non-revisiting incremental field. • In MySQL you can set the value of an AUTONUMBER field (but beware the consequences). • First TIMESTAMP field is automatically set to current date/time whenever record is updated • Last change time can be a very useful per-record property. • Format is ‘YYYYmmddHHMMSS’, e.g. 20010621142532

10. Security (1) • Username / password (and optionally client hostname) checked before any commands are accepted; • Different access for each operation (SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, …) • Access is allow / deny at a global, database, table or column level MySQL CLIENT HOST DATABASE.TABLE USERNAME PASSWORD

11. PER-REQUEST PRIVILEGE Security (2) • For given SQL statement, permissions are sum of: • global ‘user’ permissions; • permissions specific to table or column; • database (i.e. ‘all tables’) restricted by host. PER-DATABASE PER-TABLE PER-COLUMN PER-USER PER-HOST

12. Open Database Connectivity

13. ODBC Overview APPLICATION • ODBC provides the application with a standard interface to different DBMS ODBC Driver ODBC MySQL MySQL DATA

14. ODBC • ODBC gives access to data held elsewhere, with benefits such as information-centric locking and security NETWORK REMOTE DATABASE ODBC Centralised Data • Local application has access to data via operating system (e.g. local files, shared drives, UNC path) LOCAL APPN

15. Using MyODBC • Small, free download from www.mysql.com; • Install adds ‘MySQL’ to options in control panel (ODBC applet); • Create a ‘Data Source Name’ for each MySQL database.

16. Link tables from external databases (e.g. other Access *.mdb files or ODBC source) Linked data appears as just another table (even DLookup) MS Access Connectivity

17. MS Access and MySQL • Generally good, fast integration (especially compared to Access with data on shared drive) • Occasional (documented) caveats, e.g. saving a new record can show all fields as #DELETED (use TIMESTAMP) • ‘Find first’ operation can be very slow • Transaction support and roll-back recently added • No direct OLE support, but simple work around available

18. Home-cooked Clients:the Application Programming Interface

19. Choice of tools to generate the UI: this choice should not affect the user; • Each tool has (subtly) different emphasis; • Clients can interact directly with MySQL for speed / memory benefits. Tools to write clients VBA / Access Foxpro Crystal reports ASP / PHP / CGI C / C++ / PERL Java Why Write Clients? A client is simply the user interface: we already write these!

20. Application ProgrammingInterface Key features MySQL functions Form processing Session handling Demos

21. What’s PHP? PHP: Hypertext Pre-processor • “A scripting language that generates dynamic content for the web.” • Developed by Rasmus Lerdorf (1994)

22. Key features • server-side scripting language • tight integration with MySQL • available as an Apache module • cross-platform • open source and free!

23. Total (domains): 6,156,321 IP addresses: 914,146 Usage stats (source Netcraft, April 2001) Number of web sites using PHP

24. Apache Module Usage(Source: E-soft Inc., April 2001) Number of Apache web servers

25. PHP, Apache and MySQL 1 6 Browser

26. Applications: Speech Database (Phonetics) Admissions Database (Modern Languages)

27. How do I get it? http://uk.php.net/downloads.php • complete source code • win32 binaries [linux RPMs from Redhat] • excellent on-line documentation • FAQs, recommended books etc. • “Teach Yourself PHP4 in 24 Hours”Matt Zandstra,SAMS publishing, 1999

28. Configure the web server Changes to httpd.conf: • AddType application/x-httpd-php .php • DirectoryIndex index.html index.php Restart the web server, and check it works!

29. Syntax • syntax resembles C • some elements borrowed from Java, perl • user defined functions / include files • choice of tag styles: <?php … ?> <? … ?> <% … %> <script language=“php”> …</script>

30. welcome.php <html><h1>6th ITSSC</h1> <?php echo “Hello ITSSC delegates!”; ?> <hr> <?php $time = date(“H:i:s”); printf(“The time is now %s”,$time); ?> </html>

31. MySQL functions PHP has functions that allow you to: • connect to the database server • runqueries • process query results • handle errors etc.

32. Connecting to MySQL mysql_connect(hostname, username, pw) $link = mysql_connect(“localhost”, “webuser”, “mypassword”); or die (“Oops - couldn’t connect”);

33. Selecting a database mysql_select_db(database_name [, link_id]) mysql_select_db(“admissions”) or die (“Can’t select admissions!”);

34. Running a query mysql_query(query) $result = mysql_query("SELECT lcode from languages where lname =’French’ "); N.B. A successful query says nothing about number of rows returned!

35. Processing query results • mysql_fetch_row(result_id) • mysql_fetch_array (result_id) while ($row = mysql_fetch_array ($result)) { printf (“%s %s\n”, $row[“surname”], $row[“firstname”]); }

36. Form processing • Web databases often use forms as part of the user interface • Form data variables automatically generate PHP variables of the same name

37. Simple form myform.html <form action = "process.php">Please type your name:<input type = text name ="user"></form> process.php <?phpecho “Hello $user!”;?>

38. Speech database Demo 1 “English Intonation in the British Isles” Grabe, Nolan, Post (ESRC grant) • 40 hours of speech • 9 dialects of British English

39. What are sessions? HTTP: a stateless protocol Client 1 Web server Client 2 Joe Mary 1 2 3 4 5 6

40. Why do we need sessions? A SESSION associates DATA with a USER for duration of their entire visit • e-commerce • CUSTOM web pages for different users(users can log in to web database)

41. How to store session info 1. Cookies Cookies store client-specific dataon the client N.B. client may reject cookie!Security issues?Max cookie size 4k 2. Session files • Sessions store client-specific data on the server • Sessions are tagged with a unique session id

42. 37 37 95 95 Session ids Web server Joe Mary Session ID Session ID

43. Session management • PHP4 includes functions to: • manage session data on the server • generate random session ID to identify user • saves session ID: • either with a cookie (N.B. session ID only)orin the query string

44. Starting a session session_start() 1. create session file in /tmp on the server 2. sends a cookie called PHPSESSID to the client (client may refuse it) Set-Cookie: PHPSESSID=8d8e5a520c56e0a2e5751ae7b8c8273e; path=/ Cache-Control: no-store, no-cache, must-revalidate, post-check=0, pre-check=0 Pragma: no-cache

45. Resuming a session session_start() • An existing session is resumed if: • client sends cookie with session id or • session id was passed in the query string • http://mysite.com/mypage.html?PHPSESSID=xyz123

46. Registering variables session_register(variable_name) • session_register registers the variable for use in the current session • changes are automatically reflected in the session file session_register(“college_name”); session_register(“product1”);

47. Ending sessions session_destroy() Ends current session (Gotcha: variables remain available in current script, until the script reloaded) session_unset() Wipes all currently registered variables

48. Sessions example <? phpsession_start(); // initialise a sessionsession_register(‘counter’); // register variable $counter++; // increment counter echo (“You have visited this page $counter times”); ?>

49. Demo 2 • Modern Languages Admissions Database Andrew Slater, Chris Turner, 2000 Used by colleges-based ML tutors to track admissions process Sessions used to provide college-specific views of candidates / access rights

50. Other MySQL APIs • MySQL ships with APIs for several common languages: • C / C++ • PERL / PHP • Java; • Each API provides the same core functions such as: • connect(), select_db(), query(), store_result(), close(); • Data types heavily dependent on language: • garbage collection in Java • query results returned as associative array in PERL • lots of pointers in C.