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)
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
More Related Content
Presentation Transcript
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) • Home-cooking: writing clients • Interacting with the web / PHP • Other APIs explained • Illustration: college noticeboard • Links and references
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
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
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
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
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
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
ODBC Overview APPLICATION • ODBC provides the application with a standard interface to different DBMS ODBC Driver ODBC MySQL MySQL DATA
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
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.
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
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!
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
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!
Syntax • syntax resembles C • some elements borrowed from Java, perl • user defined functions / include files • choice of tag styles: <?php … ?> <? … ?> <% … %> <script language=“php”> …</script>
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>
MySQL functions PHP has functions that allow you to: • connect to the database server • runqueries • process query results • handle errors etc.
Connecting to MySQL mysql_connect(hostname, username, pw) $link = mysql_connect(“localhost”, “webuser”, “mypassword”); or die (“Oops - couldn’t connect”);
Selecting a database mysql_select_db(database_name [, link_id]) mysql_select_db(“admissions”) or die (“Can’t select admissions!”);
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!
Form processing • Web databases often use forms as part of the user interface • Form data variables automatically generate PHP variables of the same name
Simple form myform.html <form action = "process.php">Please type your name:<input type = text name ="user"></form> process.php <?phpecho “Hello $user!”;?>
Speech database Demo 1 “English Intonation in the British Isles” Grabe, Nolan, Post (ESRC grant) • 40 hours of speech • 9 dialects of British English
What are sessions? HTTP: a stateless protocol Client 1 Web server Client 2 Joe Mary 1 2 3 4 5 6
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)
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
37 37 95 95 Session ids Web server Joe Mary Session ID Session ID
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
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
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
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”);
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
Sessions example <? phpsession_start(); // initialise a sessionsession_register(‘counter’); // register variable $counter++; // increment counter echo (“You have visited this page $counter times”); ?>
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
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.