Transcript MySQL: Part II - Oxford University ICT Forum
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
What IS MySQL?
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
MySQL / PostgreSQL
MySQL / Access 2000
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
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-TABLE PER-COLUMN PER-HOST PER-REQUEST PRIVILEGE
Open Database Connectivity
ODBC Overview
• ODBC provides the application with a standard interface to different DBMS
APPLICATION
ODBC
ODBC Driver
MySQL
MySQL 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 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 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 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
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 MySQL for speed / memory benefits.
Tools to write clients
VBA / Access Foxpro Crystal reports ASP / PHP / CGI C / C++ / PERL Java
• Key features • MySQL functions • Form processing • Session handling • Demos A pplication P rogramming I nterface
What’s PHP?
P HP: H ypertext P re-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 MySQL • available as an Apache module • cross-platform • open source and free!
Number of web sites using PHP
Usage stats
(source Netcraft, April 2001)
Total (domains): 6,156,321 IP addresses: 914,146
Number of Apache web servers
Apache Module Usage
(Source: E-soft Inc., April 2001)
PHP, Apache and MySQL
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 – 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: <% … %>
welcome.php
6th ITSSC
MySQL functions
PHP has functions that allow you to: • connect to the database server • run queries • 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!
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 ”]); }
Form processing
• Web databases often use forms as part of the user interface • Form data variables automatically generate PHP variables of the same name
myform.html
Simple form
process.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 Joe 1 3 5 Web server 2 4 6 Mary Client 2
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 data on 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
Joe
Session ID
Session ids
Web server Mary 37 37 95 95
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 or
with a cookie (N.B. session ID only) in 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; Cache-Control: no-store, no-cache, must-revalidate, post-check=0, pre-check=0 Pragma: no-cache path=/
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
// 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 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.
Linux Client in C
• Install mysqlclient-3.2.23-1.i386.rpm
• Enter and compile code (add error checking etc.) #include
Linux Client: MySQL 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(“%5d: %s\n”, atoi(row[0]), row[2]); } mysql_free_result(res); mysql_close(mdb); return 0; } • Compile:
gcc -o sample sample.c -lmysqlclient
• Ready to run!
Example: Jesus College Noticeboard
• Central MySQL 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
MS Access (admin)
Ex: System Overview
Web Server UNIX Server MySQL 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 MySQL documentation at www.mysql.com
Teach yourself PHP in 24 hours,
Matt Zandstra
, SAMS 1999 MySQL manual,
Paul DuBois
, NewRiders 1999 Other PHP books (e.g. O’Reilly, WROX Press)