Developing mysql database applications
Download
1 / 58

Developing MySQL Database Applications - PowerPoint PPT Presentation


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)

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

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


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-selectSELECT * 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

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


Open Database Connectivity


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.


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


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


  • 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!


Application ProgrammingInterface

Key features

MySQL 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 MySQL

  • available as an Apache module

  • cross-platform

  • open source and free!


Total (domains): 6,156,321

IP addresses: 914,146

Usage stats

(source Netcraft, April 2001)

Number of web sites using PHP


Apache Module Usage(Source: E-soft Inc., April 2001)

Number of Apache web servers


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:

    <?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!


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


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 idor

  • 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.


Linux Client in C

  • Install mysqlclient-3.2.23-1.i386.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: 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


Ex: System Overview

Web

Server

UNIX

Server

MS Access

(admin)

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)

andrew.slater@phon.ox.ac.uk

john.ireland@jesus.ox.ac.uk


ad
  • Login