Developing My SQL Database Applications 6 th IT

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

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

What IS My. SQL?

Welcome to My. SQL • Relational Database Management System (RDBMS) • Open source (GNU Public License) • My. SQL server: Windows 9 x/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 SQL 92 (almost!) except: – Sub-select SELECT * FROM table 1 WHERE id IN (SELECT id FROM table 2); – SELECT INTO table… • • Multi-threaded (good multi-processor performance) Handles large files (e. g. 200 GB) 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 My. SQL web site

My. SQL / Postgre. SQL

My. SQL / Access 2000

Smart Datatypes • AUTONUMBER fields are available: a non-revisiting incremental field. – In My. SQL 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 ‘YYYYmmdd. HHMMSS’, 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 My. SQL CLIENT HOST USERNAME PASSWORD DATABASE. TABLE

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-USER PER-HOST PER-REQUEST PRIVILEGE PER-TABLE PER-COLUMN

Open Database Connectivity

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

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

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

MS Access Connectivity 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 and My. SQL • 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

Home-cooked Clients: the Application Programming Interface

Why Write Clients? A client is simply the user interface: we already write these! • 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 My. SQL for speed / memory benefits. Tools to write clients VBA / Access Foxpro Crystal reports ASP / PHP / CGI C / C++ / PERL Java

Application Programming Interface • • • Key features My. SQL functions Form processing Session handling Demos

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

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

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

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

PHP, Apache and My. SQL 1 6 Browser

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

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

Configure the web server Changes to httpd. conf: • Add. Type application/x-httpd-php. php • Directory. Index 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> <h 1>6 th ITSSC</h 1> <? php echo “Hello ITSSC delegates!”; ? > <hr> <? php $time = date(“H: i: s”); printf(“The time is now %s”, $time); ? > </html>

My. SQL functions PHP has functions that allow you to: • connect to the database server • run queries • process query results • handle errors etc.

Connecting to My. SQL 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!

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

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 <? php echo “Hello $user!”; ? >

Speech database “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 3 5 2 4 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 store client-specific data on the client N. B. client may reject cookie! Security issues? Max cookie size 4 k 2. Session files • Sessions store client-specific data on the server • Sessions are tagged with a unique session id

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

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

Starting a 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=8 d 8 e 5 a 520 c 56 e 0 a 2 e 5751 ae 7 b 8 c 8273 e; path=/ Cache-Control: no-store, no-cache, must-revalidate, post-check=0, pre-check=0 Pragma: no-cache

Resuming a 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=xyz 123

Registering variables session_register(variable_name) • session_registers the variable for use in the current session • changes are automatically reflected in the session file session_register(“college_name”); session_register(“product 1”);

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 <? php session_start(); session_register(‘counter’); // initialise a session // register variable $counter++; // increment counter echo (“You have visited this page $counter times”); ? >

• 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 My. SQL APIs • My. SQL 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.

Linux Client in C • Install mysqlclient-3. 2. 23 -1. i 386. rpm • Enter and compile code (add error checking etc. ) #include <stdio. h> #include <mysql. h> int main() { MYSQL mdbi, *mdb = &mdbi; MYSQL_RESULT *res; MYSQL_ROW *row; const char *host, *db, *user, *pwd; int ii, nrows; /*. . . Input values for host, user, pwd, db */

Linux Client: My. SQL Core mdb = mysql_connect(mdb, host, user, pwd); mysql_select_db(mdb, db); mysql_query(mdb, “SELECT * FROM Table”); res = mysql_store_result(mdb); nrows = mysql_num_rows(res); for (ii = 0; ii < nrows; ii++) { row = mysql_fetch_row(res); printf(“%5 d: %sn”, atoi(row[0]), row[2]); } mysql_free_result(res); mysql_close(mdb); return 0; } • Compile: gcc • Ready to run! -o sample. c -lmysqlclient

Example: Jesus College Noticeboard • Central My. SQL database of ‘articles’ – ‘Message Of The Day’ articles – Announcements (read-once) – News articles (with automatic index listing) • MS Access front-end: familiar to administrative staff • Integrated into website (notices and news board) • Client added to system-wide UNIX login scripts • Plans for Windows login client

Ex: System Overview Web Server UNIX Server MS Access (admin) My. SQL Server UNIX Server FIREWALL MS Access (admin) UNIX Server

Ex: MS Access front end

Ex: Web integration

Ex: UNIX client at login

More Information. . . Extensive My. SQL documentation at www. mysql. com Teach yourself PHP in 24 hours, Matt Zandstra, SAMS 1999 My. SQL manual, Paul Du. Bois, New. Riders 1999 Other PHP books (e. g. O’Reilly, WROX Press) andrew. slater@phon. ox. ac. uk john. ireland@jesus. ox. ac. uk