1 / 58

Developing MySQL Database Applications

Developing MySQL Database Applications. 6th IT Support Staff Conference Andrew Slater (IT Support Officer: Phonetics & Modern Languages) and John Ireland (Computing Manager, Jesus College). Workshop Synopsis. Introduction to MySQL Open Database Connectivity (ODBC)

farren
Download Presentation

Developing MySQL Database Applications

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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.

More Related