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
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
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
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 Clientsthe Application Programming
Interface
19
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
Whats 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
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) Admis
sions 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 HoursMatt
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
• lt?php ?gt
• lt? ?gt
• lt gt
• ltscript languagephpgt lt/scriptgt

30
welcome.php

• lthtmlgtlth1gt6th ITSSClt/h1gt
• lt?php echo Hello ITSSC delegates!
• ?gt
• lthrgt
• lt?php time date(His)
• printf(The time is now s,time)
• ?gt
• lt/htmlgt

31
MySQL functions

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

32
Connecting to MySQL

• mysql_connect(hostname, username, pw)
• link mysql_connect(localhost, webuser,
  mypassword)
• or
• die (Oops - couldnt connect)

33
Selecting a database

• mysql_select_db(database_name , link_id)
• mysql_select_db(admissions)
• or
• die (Cant 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, rowsurname,
  rowfirstname)

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
ltform action "process.php"gtPlease type your
nameltinput type text name "user"gtlt/formgt
process.php
lt?phpecho Hello user!?gt
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 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

42
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)or
  in 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 PHPSESSID8d8e5a520c56e0a2e5751ae7b8c8
273e path/ Cache-Control no-store, no-cache,
must-revalidate, post-check0, pre-check0 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?PHPSESSIDxyz123

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
lt? phpsession_start() // initialise a
sessionsession_register(counter) // register
variable counter // increment counter echo
(You have visited this page counter times) ?gt
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.

51
Linux Client in C

• Install mysqlclient-3.2.23-1.i386.rpm
• Enter and compile code (add error checking etc.)

include ltstdio.hgt include ltmysql.hgt 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 /
52
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 lt nrows
ii) row mysql_fetch_row(res) printf(5
d s\n, atoi(row0), row2) mysql_free_re
sult(res) mysql_close(mdb) return 0

• Compile gcc -o sample sample.c -lmysqlclient
• Ready to run!

53
ExampleJesus 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

54
Ex System Overview
Web Server
UNIX Server
MS Access (admin)
MySQL Server
UNIX Server
FIREWALL
UNIX Server
MS Access (admin)
55
Ex MS Access front end
56
Ex Web integration
57
Ex UNIX client at login
58
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. OReilly, WROX Press)
• andrew.slater_at_phon.ox.ac.uk
• john.ireland_at_jesus.ox.ac.uk