Internet Engineering Web Application development models Introduction n

  • Slides: 98
Download presentation
Internet Engineering Web Application development models

Internet Engineering Web Application development models

Introduction n Company needs to provide various web services q q q n Hosting

Introduction n Company needs to provide various web services q q q n Hosting intranet applications Company web site Various internet applications Therefore there is a need to develop applications q q We should select web application model to use Discuss on different types of applications

Content n Application development models: q SSI q CGI with Perl q LAMP/WAMP q

Content n Application development models: q SSI q CGI with Perl q LAMP/WAMP q J 2 EE q. Net n Typical web applications in an organization q CMS q DMS q Groupware q WIKI q Workflow

SSI n n n Server Side Includes This is very simple model, not really

SSI n n n Server Side Includes This is very simple model, not really an application development model suitable for middle to large size applications Web server processes such instructions and generate dynamic content Directives that are placed in HTML pages, and evaluated on the server while the pages are being served. Let you add dynamically generated content to an existing HTML page, without having to serve the entire page via a CGI program, or other dynamic technology.

SSI n SSI directives have the following syntax: <!--#element attribute=value. . . --> n

SSI n SSI directives have the following syntax: <!--#element attribute=value. . . --> n n It is formatted like an HTML comment so if you don't have SSI correctly enabled, the browser will ignore it

SSI examples n Commonly used to obtain and display environment variables from the OS:

SSI examples n Commonly used to obtain and display environment variables from the OS: <!--#echo var="DATE_LOCAL" --> n Modification date of the file This document last modified <!--#flastmod file="index. html" --> n Including the results of a CGI program: <!--#include virtual="/cgi-bin/counter. pl" --> n Including a standard footer: <!--#include virtual="/footer. html" -->

SSI examples (cont. ) n Executing commands: <pre> <!--#exec cmd="ls" --> </pre> n Conditional

SSI examples (cont. ) n Executing commands: <pre> <!--#exec cmd="ls" --> </pre> n Conditional expressions: <!--#if expr="${Mac} && ${Internet. Explorer}" --> Apologetic text goes here <!--#else --> Cool Java. Script code goes here <!--#endif -->

SSI conclusion n n Minimize code duplications SSI for site management Creates dynamic content

SSI conclusion n n Minimize code duplications SSI for site management Creates dynamic content Can be a security problem

CGI n n n n Common Gateway Interface Invented in 1993 by NCSA for

CGI n n n n Common Gateway Interface Invented in 1993 by NCSA for HTTPd web server q Client requests program to be run on server-side q Web server passes parameters to program through UNIX shell environment variables q Program spawned as separate process via fork q Program's output => Results q Server passes back results (usually in form of HTML) Good for interfacing external applications with information servers Frequently mistaken for a language. In fact it is a standard that enables clients and servers to exchange data. Although the basis of all web applications, it is most often considered when a file is executed rather than read. it is language independent CGI programs are most often written in PERL, C/C++, VB, Java, or UNIX shell scripts.

CGI Request service HEADERS BODY Run CGI program … … … print $result

CGI Request service HEADERS BODY Run CGI program … … … print $result

CGI with Perl n n n Write a standard Perl Program's output (to stdout)

CGI with Perl n n n Write a standard Perl Program's output (to stdout) is sent back as HTTP Response You must write out everything q q q Headers Blank Space Body

CGI with Perl n n n Some CGI programs are in machine code, but

CGI with Perl n n n Some CGI programs are in machine code, but Perl programs are usually kept in source form, so Perl must be run on them A source file can be made to be “executable” by adding a line at their beginning that specifies that a language processing program be run on them first For Perl programs, if the perl system is stored in /usr/local/bin/perl, as is often is in UNIX systems, this is: q #!/usr/local/bin/perl The file extension. cgi is sometimes used for Perl CGI programs An HTML document specifies a CGI program with the hypertext reference attribute, href, of an anchor tag, <a>, as in q <a href = “. /cgi-bin/reply. cgi>" Click here to run the CGI program, reply. pl </a>

Perl n n n Practical Extension and Reporting Language. Originally developed as a utility

Perl n n n Practical Extension and Reporting Language. Originally developed as a utility language for UNIX. Particularly well suited to manipulate patterns, especially text. Popular because it is free, available for most operating systems, and relatively easy to learn Exceedingly powerful, but ugly, noisy, and prone to errors.

Perl – a simple example n “Hello World” in PERL #! /usr/bin/perl print "Content-type:

Perl – a simple example n “Hello World” in PERL #! /usr/bin/perl print "Content-type: text/htmlnn"; print "<html><body><h 1>Hello World!"; print "</h 1></body></html>n"; n Simple concept -- the program executes, and the output is sent to the browser that called it.

Perl – a simple counter #! /usr/bin/perl open (INPUT, ”count. txt”); @inline= <INPUT>; $count

Perl – a simple counter #! /usr/bin/perl open (INPUT, ”count. txt”); @inline= <INPUT>; $count = $inline[0] + 1; close INPUT; open (OUT, ”>count. txt”); print OUT “$countn”; close OUT; print "Content-type: text/htmlnn"; print "<html><body>”; print “<h 1>Let’s Count! "</h 1>"; print “This page accessed $count times<p>”; print “</body></html>n";

Perl – Basic syntax n Perl statements end in a semi-colon: n Comments start

Perl – Basic syntax n Perl statements end in a semi-colon: n Comments start with a hash symbol and run to the end of the line n Whitespace is irrelevant:

Perl – Basic syntax (cont. ) n Variable types: q Scalars: q Arrays:

Perl – Basic syntax (cont. ) n Variable types: q Scalars: q Arrays:

Perl – Basic syntax (cont. ) n Variable types: q Arrays:

Perl – Basic syntax (cont. ) n Variable types: q Arrays:

Perl – Basic syntax (cont. ) n Variable types: q Hashes:

Perl – Basic syntax (cont. ) n Variable types: q Hashes:

Perl – Basic syntax (cont. ) n Variable scoping: n Conditional constructs:

Perl – Basic syntax (cont. ) n Variable scoping: n Conditional constructs:

Perl – Basic syntax (cont. ) n Conditional constructs: n While:

Perl – Basic syntax (cont. ) n Conditional constructs: n While:

Perl – Basic syntax (cont. ) n for: n foreach:

Perl – Basic syntax (cont. ) n for: n foreach:

Perl – Basic syntax (cont. ) n Operators: q Arithmetic: q Numeric comparison:

Perl – Basic syntax (cont. ) n Operators: q Arithmetic: q Numeric comparison:

Perl – Basic syntax (cont. ) n Operators: q String comparison: q Boolean logic:

Perl – Basic syntax (cont. ) n Operators: q String comparison: q Boolean logic:

Perl – Basic syntax (cont. ) n Files and IO:

Perl – Basic syntax (cont. ) n Files and IO:

Perl – Basic syntax (cont. ) n String matching:

Perl – Basic syntax (cont. ) n String matching:

Perl – Basic syntax (cont. ) n String matching:

Perl – Basic syntax (cont. ) n String matching:

Perl – Basic syntax (cont. ) n Subroutines:

Perl – Basic syntax (cont. ) n Subroutines:

CGI environment variables (%ENV)

CGI environment variables (%ENV)

CGI environment variables (%ENV) n example- Printing environment variables: #!/usr/bin/perl use strict; use CGI

CGI environment variables (%ENV) n example- Printing environment variables: #!/usr/bin/perl use strict; use CGI qw(: standard); use CGI: : Carp qw(warnings. To. Browser fatals. To. Browser); print header; print start_html("Environment"); foreach my $key (sort(keys(%ENV))) { print "$key = $ENV{$key} n"; } print end_html;

CGI environment variables (%ENV) n Example- Referrer: #!/usr/bin/perl use strict; use CGI qw(: standard);

CGI environment variables (%ENV) n Example- Referrer: #!/usr/bin/perl use strict; use CGI qw(: standard); use CGI: : Carp qw(warnings. To. Browser fatals. To. Browser); print header; print start_html("Referring Page"); print "Welcome, I see you've just come from $ENV{HTTP_REFERER}!<p>n"; print end_html;

CGI environment variables (%ENV) n Example- Browser detection: #!/usr/bin/perl use strict; use CGI qw(:

CGI environment variables (%ENV) n Example- Browser detection: #!/usr/bin/perl use strict; use CGI qw(: standard); use CGI: : Carp qw(warnings. To. Browser fatals. To. Browser); print start_html("Browser Detect"); my($ua) = $ENV{HTTP_USER_AGENT}; print "User-agent: $ua<p>n"; if (index($ua, "MSIE") > -1) { print "Your browser is Internet Explorer. <p>n"; } elsif (index($ua, "Netscape") > -1) { print "Your browser is Netscape. <p>n"; } elsif (index($ua, "Safari") > -1) { print "Your browser is Safari. <p>n"; } elsif (index($ua, "Opera") > -1) { print "Your browser is Opera. <p>n"; } elsif (index($ua, "Mozilla") > -1) { print "Your browser is probably Mozilla. <p>n"; } else { print "I give up, I can't tell what browser you're using!<p>n"; } print end_html;

Form processing

Form processing

Form processing (cont. ) #!/usr/bin/perl use CGI qw(: standard); use CGI: : Carp qw(warnings.

Form processing (cont. ) #!/usr/bin/perl use CGI qw(: standard); use CGI: : Carp qw(warnings. To. Browser fatals. To. Browser); use strict; print header; print start_html("Thank You"); print h 2("Thank You"); my %form; foreach my $p (param()) { $form{$p} = param($p); print "$p = $form{$p} n"; } print end_html;

Form processing (cont. ) #!/usr/bin/perl use CGI qw(: standard); use CGI: : Carp qw(warnings.

Form processing (cont. ) #!/usr/bin/perl use CGI qw(: standard); use CGI: : Carp qw(warnings. To. Browser fatals. To. Browser); use strict; print header; print start_html("Results"); # Set the PATH environment variable to the same path # where sendmail is located: $ENV{PATH} = "/usr/sbin"; # open the pipe to sendmail open (MAIL, "|/usr/sbin/sendmail -oi -t") or &dienice("Can't fork for sendmail: $!n"); # change this to your own e-mail address my $recipient = '[email protected] 101. com';

Form processing (cont. ) # Start printing the mail headers # You must specify

Form processing (cont. ) # Start printing the mail headers # You must specify who it's to, or it won't be delivered: print MAIL "To: $recipientn"; # From should probably be the webserver. print MAIL "From: [email protected] 101. comn"; # print a subject line so you know it's from your form cgi. print MAIL "Subject: Form Datann"; # Now print the body of your mail message. foreach my $p (param()) { print MAIL "$p = ", param($p), "n"; } # Be sure to close the MAIL input stream so that the # message actually gets mailed. close(MAIL);

Form processing (cont. ) # Now print a thank-you page print <<End. HTML; <h

Form processing (cont. ) # Now print a thank-you page print <<End. HTML; <h 2>Thank You</h 2> <p>Thank you for writing!</p> <p>Return to our <a href="index. html">home page</a>. </p> End. HTML print end_html; # The dienice subroutine handles errors. sub dienice { my($errmsg) = @_; print "<h 2>Error</h 2>n"; print "<p>$errmsg</p>n"; print end_html; exit; }

Setting cookies #!/usr/bin/perl use strict; my $cid = int(rand(1000000)); print "Set-Cookie: NAME=$cidn"; print "Content-type:

Setting cookies #!/usr/bin/perl use strict; my $cid = int(rand(1000000)); print "Set-Cookie: NAME=$cidn"; print "Content-type: text/htmlnn"; print <<End. Of. HTML; <html><head><title>Welcome</title></head> <body> <h 2>Welcome!</h 2> Your cookie is $cid. <p> </body></html> End. Of. HTML ;

Reading cookies #!/usr/bin/perl use CGI qw(: standard); use CGI: : Carp qw(warnings. To. Browser

Reading cookies #!/usr/bin/perl use CGI qw(: standard); use CGI: : Carp qw(warnings. To. Browser fatals. To. Browser); use strict; print header(); print start_html("Cookie"); print h 2("Welcome!"); if (my $cookie = cookie('mycookie')) { print "Your cookie is $cookie. "; } else { print "You don't have a cookie named `mycookie'. "; } print end_html;

References n n n http: //httpd. apache. org/docs/1. 3/howto/ssi. html http: //learn. perl. org/

References n n n http: //httpd. apache. org/docs/1. 3/howto/ssi. html http: //learn. perl. org/ http: //www. stanford. edu/class/cs 193 i/handouts. Sum 2004/ 21 CGI. pdf http: //www. stanford. edu/class/cs 193 i/handouts. Sum 2004/ 22 CGI 2. pdf http: //www. stanford. edu/class/cs 193 i/handouts. Sum 2004/ 23 CGI 3. pdf

LAMP

LAMP

What is LAMP? n LAMP refers to a set of tools: q q n

What is LAMP? n LAMP refers to a set of tools: q q n n Linux Apache My. SQL PHP It allows for rapid deployment of software applications It can be defined as Open Source platform We have already discussed on Linux and Apache We should talk more about PHP and My. SQL

PHP overview n n n n n Open Source server-side scripting language designed specifically

PHP overview n n n n n Open Source server-side scripting language designed specifically for the web. In-line scripting Conceived in 1994, now used on +10 million web sites. Now in version 5. 0 Outputs not only HTML but can output XML, images (JPG & PNG), PDF files and even Flash movies (using libswf and Ming) all generated on the fly. Can write these files to the filesystem. Supports a wide-range of databases (inherently or via ODBC). PHP also has support for talking to other services using protocols such as LDAP, IMAP, SNMP, POP 3, HTTP. Supports OO programming Perl- and C-like syntax. Relatively easy to learn. Website @ http: //www. php. net/

Why use PHP n n n n If you like free software or need

Why use PHP n n n n If you like free software or need a free solution If you need a solution that’s portable across multiple platforms (e. g. Red Hat Linux to Windows 2000) If you want to add dynamic content to your pages If you want to make your pages easier to maintain There a lot of open source/free packages/libraries available in PHP. Many mailing lists/sites are dedicated to it. Examples of uses of PHP : q Surveys - Polls q Small/Medium Portals q Small/Medium Web-Mails q Content Management

What is in a php file n PHP files may contain text, HTML tags

What is in a php file n PHP files may contain text, HTML tags and scripts PHP files are returned to the browser as plain HTML PHP files have a file extension of ". php", ". php 3", or “. phtml“ n Embedding PHP in HTML: n n <html> <body> <strong>Hello World!</strong> <? echo ‘This is a PHP introductory course!’; ? > </body> </html>

Include mechanism <? php include '. . /includes/header. html'; ? > <center> content of

Include mechanism <? php include '. . /includes/header. html'; ? > <center> content of your web page </center> <? php include 'http: //cs. ucy. ac. cy/php/footer. html'; ? > n Content can be included from a local or remote source via such protocols as HTTP, HTTPS, FTP, and FTPS

Types n Scalar types q q n Boolean Integer Float String Compound types q

Types n Scalar types q q n Boolean Integer Float String Compound types q q Array Object

Variables n Variables all start with a $ q q n Case-sensitive Must start

Variables n Variables all start with a $ q q n Case-sensitive Must start with a letter or underscore, followed by any number of letters, numbers, or underscores q Variables are not explicitly typed n n Type of value is inferred on operator application Uninitialised variables have value undef q What undef means depends on context n n Numeric context it is 0 String context it is empty string “”

Variables n To assign values to variables: q q n Data Types are automatically

Variables n To assign values to variables: q q n Data Types are automatically assigned though you can force a data type by type casting. For example: q q q n $foo = ‘bar’; Data Type: String $foo = 1; Data Type: integer $foo = 5. 34; Data Type: Double $foo = array(“bar”, ”united”); Data Type: Array $foo = ‘Hello’; $bar = (int)$foo; $bar now equals 0 Almost all variables are local (page). Globals include $_Session

Example <html> <body> <p> <? php $temperature = 5; $conversion. Factor. C 2 K

Example <html> <body> <p> <? php $temperature = 5; $conversion. Factor. C 2 K = 273; print("$temperature ° C"); echo " is "; print($temperature+$conversion. Factor. C 2 K. "° K"); ? > </p> </body> </html>

Associative arrays Java arrays are index by number, e. g. a[0] n PHP arrays

Associative arrays Java arrays are index by number, e. g. a[0] n PHP arrays can also be indexed by association, e. g. : $phone['mark']='3497'; $phone['ian']='3098'; $people=array_keys($phone); foreach ($people as $person) echo "$person is on ext ". $phone[$person]. " "; n

Arrays n Actually an ordered map q n May contain other arrays q n

Arrays n Actually an ordered map q n May contain other arrays q n n So you can use it like a vector, hashtable, dictionary, stack, queue etc. You could build trees with them In fact, they can contain any PHP type They are dynamic (their size is changing at runtime)

Array Syntax Creation with array() <? php $arr = array("foo" => "bar", 12 =>

Array Syntax Creation with array() <? php $arr = array("foo" => "bar", 12 => true); echo $arr["foo"]; echo $arr[12]; ? > n

Array of array <? php $arr = array( "somearray" => array(6 => 5, 13

Array of array <? php $arr = array( "somearray" => array(6 => 5, 13 => 9, "a" => 42)); echo $arr["somearray"][6]; // 5 echo $arr["somearray"][13]; // 9 echo $arr["somearray"]["a"]; // 42 ? >

Parameters n Pass by value is deafult <? php function add. Nothing($string) { $string.

Parameters n Pass by value is deafult <? php function add. Nothing($string) { $string. = ‘with a cherry on top. ’; } $dessert = ‘Ice cream ’; add. Nothing($dessert); echo $dessert; ? >

Parameters n By reference <? php function add. Topping(&$string) { $string. = ‘with a

Parameters n By reference <? php function add. Topping(&$string) { $string. = ‘with a cherry on top. ’; } $dessert = ‘Ice cream ’; add. Topping($dessert); echo $dessert; ? >

Servers variable array n n $_SERVER is an array containing information such as q

Servers variable array n n $_SERVER is an array containing information such as q q q n Headers Paths Script locations The entries in this array are created by the webserver. There is no guarantee that every webserver will provide any of these; servers may omit some, or provide others

Servers variable array

Servers variable array

Server variables n 'argv' q n Array of arguments passed to the script. When

Server variables n 'argv' q n Array of arguments passed to the script. When the script is run on the command line, this gives C-style access to the command line parameters. When called via the GET method, this will contain the query string. 'argc' q Contains the number of command line parameters passed to the script (if run on the command line).

Server variables n 'REMOTE_ADDR' q n 'REMOTE_HOST' q n The IP address from which

Server variables n 'REMOTE_ADDR' q n 'REMOTE_HOST' q n The IP address from which the user is viewing the current page. The Host name from which the user is viewing the current page. The reverse dns lookup is based off the REMOTE_ADDR of the user. 'REMOTE_PORT' q The port being used on the user's machine to communicate with the web server.

HTML Forms n When a form is submitted to a PHP script, the information

HTML Forms n When a form is submitted to a PHP script, the information from that form is automatically made available to the script q q There’s a few ways to do this Example: <form action="foo. php" method="POST"> Name: <input type="text" name="username"> Email: <input type="text" name="email"> <input type="submit" name="submit" value="Submit"> </form>

n n n n <html><body><p> <? php print $_POST['username']; ? > </p></body></html> $_GET $_REQUEST

n n n n <html><body><p> <? php print $_POST['username']; ? > </p></body></html> $_GET $_REQUEST $_FILES: An associative array of items uploaded to the current script via the HTTP POST method.

Session n $_SESSION q q n An associative array containing session variables available to

Session n $_SESSION q q n An associative array containing session variables available to the current script. A way to preserve certain data across subsequent accesses Loads of session handling functions q name, lifetime, cache etc.

Session n n n The idea of a session is to track a user

Session n n n The idea of a session is to track a user during a single session on a web site. This enables customized web pages, single login during a session, shopping cart applications, and tracking users behavior Cryptographically generated to be a unique session id Session ID is stored as a cookie on the client box or passed along through URL's. Session variable values are stored in the 'superglobal‘ associative array '$_SESSION. ' The values are actually stored at the server and are accessed via the session id from your cookie. On the client side the session ID expires when connection is broken.

Session handling example Page 1 <? php session_start(); $_SESSION[‘FName'] = $_Get[‘FName']; $_SESSION[‘LName'] = $_Get[‘LName'];

Session handling example Page 1 <? php session_start(); $_SESSION[‘FName'] = $_Get[‘FName']; $_SESSION[‘LName'] = $_Get[‘LName']; include '. . /includes/header. html'; ? > n Page 2 <? php session_start(); echo $_SESSION[‘FName']. “ “. $_SESSION[‘LName']; ? > n

Cookies n n Cookies are little text file that a web site stores in

Cookies n n Cookies are little text file that a web site stores in the client’s computer to maintain information about that client Cookies are sent along with the rest of the HTTP headers Like other headers, cookies must be sent before any output from your script (this is a protocol restriction). This requires that you place calls to this function prior to any output, including <html> and <head> tags

Cookies n Setting a cookie q n Setting a cookie with expiration q n

Cookies n Setting a cookie q n Setting a cookie with expiration q n setcookie("Test. Cookie", “lng=en”, time()+3600); /* expire in 1 hour */ Access and print a cookie q n setcookie(“Test. Cookie", “lng=en”); echo $_COOKIE[‘Test. Cookie’] Delete a cookie q q setcookie ("Test. Cookie", "", time() - 3600); set the expiration time to an hour ago

PHP and My. SQL n n PHP and My. SQL are a perfect companion

PHP and My. SQL n n PHP and My. SQL are a perfect companion Largely because they are both free and they have numerous capabilities PHP as of version 3 supports inherently My. SQL i. e. specialized build-in functions handle the database interactions Same goes with ORACLE but not with Microsoft databases (Access, SQL Server)

Example <html> <body> <h 1>A List of Users Who Have Signed Up For ….

Example <html> <body> <h 1>A List of Users Who Have Signed Up For …. </h 1> <? $dbh = mysql_connect("localhost", “dbusername", “dbpassword") or die(“Couldn't connect to database. "); $db = mysql_select_db(“dbname", $dbh) or die(“Couldn't select database. "); $sql = “SELECT username, email FROM userspool”; $result = mysql_query($sql) or die(“Something is wrong with your SQL statement. "); while ($row = mysql_fetch_array($result)) { $username = $row[‘username’]; $email = $row[‘email’]; echo ‘<a href=“mailto: ’. $email. ’”>’. $username. ’</a> ’; } ? > </body> </html>

My. SQL

My. SQL

What is My. SQL? SQL – Structured Query Language My. SQL is a open-source,

What is My. SQL? SQL – Structured Query Language My. SQL is a open-source, multithreaded, multi-user, SQL (Structured Query Language) relational database server My. SQL works on many different platforms—including Free. BSD, UNIX, Linux, Mac OS X, OS/2 Warp, Solaris, Sun. OS, SCO, Windows, and other OS’s. ¹ My. SQL is used by companies like The Associated Press, Google, NASA, Sabre Holdings, American Greetings, and Suzuki. ²

Relational Database Basically information organized in a structure. Top level: Database (Excel File) ->

Relational Database Basically information organized in a structure. Top level: Database (Excel File) -> Tables (Excel Sheet) -> Columns and Rows -> Data Therefore data is organized into categories which can be stored and retrieved in an efficient manner as long as you know where to look.

Programming Languages Programming languages which can access My. SQL databases include: C, C++, Eiffel,

Programming Languages Programming languages which can access My. SQL databases include: C, C++, Eiffel, Smalltalk, Java, Lisp, Perl, PHP, Python, Ruby, and Tcl. ² You need to either compile in My. SQL support when installing these languages or have access to My. SQL libraries respective to the programming language (i. e. lib. My. SQL. dll)

Installation and Setup Download installation package or source/rpms from www. mysql. com. Run setup

Installation and Setup Download installation package or source/rpms from www. mysql. com. Run setup utility/install rpms/compile from source. Setup administrator access using mysqladmin. This command allows you to setup root access and run administrative commands from the command prompt. Login to My. SQL daemon: Windows: c: /mysql/bin/mysql –u username –p –h host *nix: mysql –u username –p –h host Use root user to add new users with privileges.

Setup Users Privileges can be granted at four levels: Global level Global privileges apply

Setup Users Privileges can be granted at four levels: Global level Global privileges apply to all databases on a given server. These privileges are stored in the mysql. user table. GRANT ALL ON *. * and REVOKE ALL ON *. * grant and revoke only global privileges. Database level Database privileges apply to all tables in a given database. These privileges are stored in the mysql. db and mysql. host tables. GRANT ALL ON db_name. * and REVOKE ALL ON db_name. * grant and revoke only database privileges. ² Table level Table privileges apply to all columns in a given table. These privileges are stored in the mysql. tables_priv table. GRANT ALL ON db_name. tbl_name and REVOKE ALL ON db_name. tbl_name grant and revoke only table privileges. Column level Column privileges apply to single columns in a given table. These privileges are stored in the mysql. columns_priv table. When using REVOKE, you must specify the same columns that were granted. ²

Setup Users So a command to grant user to database test: GRANT ALL PRIVILEGES

Setup Users So a command to grant user to database test: GRANT ALL PRIVILEGES ON test. * TO ‘user’@'localhost' IDENTIFIED BY ‘password’; Then we need to reload the privileges: FLUSH PRIVILEGES; Thus ‘user’ may access the My. SQL database from ‘localhost’ (server) on all tables in the ‘test’ database. mysql –u user –p password: password

Creating Databases Syntax: create databasename; create database library; Then we can see the database:

Creating Databases Syntax: create databasename; create database library; Then we can see the database: show databases; +----------+ | library | | test | +----------+ Then we need to access/use the databse: use library;

Creating Tables Syntax: CREATE TABLE tablename ( column 1 attributes, column 2 attributes, column

Creating Tables Syntax: CREATE TABLE tablename ( column 1 attributes, column 2 attributes, column 3 attributes); CREATE TABLE books ( book_id INT(4) PRIMARY KEY NOT NULL AUTO_INCREMENT UNSIGNED, title VARCHAR(255), author VARCHAR(255), location VARCHAR(32), ISBN VARCHAR(16), quantity INT DEFAULT ‘ 0’);

Creating Tables Cont… We can then see what tables are in a database: Show

Creating Tables Cont… We can then see what tables are in a database: Show tables; +----------+ | Tables_in_library | +----------+ | books | +----------+ Then we can see what columns are in a table. DESCRIBE (DESC) tablename; DESC TABLE books; +--------------+------+-----+--------+ | Field | Type | Null | Key | Default | Extra | +--------------+------+-----+--------+ | book_id | int(4) unsigned | | PRI | NULL | auto_increment | | title | varchar(255) | YES | | NULL | | | author | varchar(255) | YES | | NULL | | | location | varchar(32) | YES | | NULL | | | ISBN | varchar(16) | YES | | NULL | | | quantity | int(11) | YES | | 0 | | +--------------+------+-----+--------+

Insert Rows Syntax: INSERT (IGNORE) INTO tablename (columns to insert) VALUES (values to insert);

Insert Rows Syntax: INSERT (IGNORE) INTO tablename (columns to insert) VALUES (values to insert); INSERT INTO books (title, author, location) VALUES ("The Hobbit", "JRR Tolkien", “F Tolkien, J”); INSERT INTO books (title, author, location, ISBN, quantity) VALUES (“Windows XP", “William Gates", “ 115. 4”, “ 1282105242142943”, 1); Notice I did not specify a book_id, this is because it is an auto incrementing row. Therefore The Hobbit will be book_id 1, and Windows XP will be book_id 2.

Selecting Rows Syntax: SELECT columns FROM tablename WHERE condition SELECT title, author FROM books

Selecting Rows Syntax: SELECT columns FROM tablename WHERE condition SELECT title, author FROM books WHERE book_id = 1; +-------------+ | title | author | +-------------+ | The Hobbit | JRR Tolkien | +-------------+ If you do not specify a WHERE clause, it will select EVERYTHING: SELECT title, author FROM books; +---------------+ | title | author | +---------------+ | The Hobbit | JRR Tolkien | | Windows XP | William Gates | +---------------+

Selecting Rows Cont… If you want to rename the returned column, use an as:

Selecting Rows Cont… If you want to rename the returned column, use an as: SELECT title as what, author as who FROM books; +---------------+ | what | who | +---------------+ | The Hobbit | JRR Tolkien | | Windows XP | William Gates | +---------------+ The wildcard ‘*’ can be used to select all columns: SELECT * FROM books; +------------+--------------+---------+-----+ | book_id | title | author | location | ISBN | quantity | +------------+--------------+---------+-----+ | 1 | The Hobbit | JRR Tolkien | F Tolkien, J | NULL | 0 | | 2 | Windows XP | William Gates | 115. 4 | 1282105242142943 | 1 | +------------+--------------+---------+-----+ ISBN is NULL for The Hobbit since we did not specify, and the quantity is 0 since that is the default value we set.

Selecting Count is a special syntax which returns a numerical amount of a select

Selecting Count is a special syntax which returns a numerical amount of a select statement: SELECT COUNT(*) FROM books; +-----+ | COUNT(*) | +-----+ | 2 | +-----+ Distinct is another syntax which returns unique values of a select statement: SELECT DISTINCT author FROM books;

Updating Rows Updating rows changes values of data within rows. Syntax: UPDATE tablename SET

Updating Rows Updating rows changes values of data within rows. Syntax: UPDATE tablename SET attribute 1 = value 1, attribue 2 = value 2 WHERE conditions; UPDATE books SET quantity = 5 WHERE book_id = 1; SELECT * FROM books WHERE book_id = 1; +------------+--------------+------+ | book_id | title | author | location | ISBN | quantity | +------------+--------------+------+ | 1 | The Hobbit | JRR Tolkien | F Tolkien, J | NULL | 5 | +------------+--------------+------+ WARNING! Be careful of update statements and to specify a WHERE clause. Without a WHERE clause, all rows will be updated with quantity of 5.

Deleting Rows Syntax: DELETE FROM tablename WHERE conditions; DELETE FROM books WHERE book_id =

Deleting Rows Syntax: DELETE FROM tablename WHERE conditions; DELETE FROM books WHERE book_id = 2; SELECT * FROM books; +------------+--------------+------+ | book_id | title | author | location | ISBN | quantity | +------------+--------------+------+ | 1 | The Hobbit | JRR Tolkien | F Tolkien, J | NULL | 5 | +------------+--------------+------+ WARNING! Be careful of delete statements and to specify a WHERE clause. Without a WHERE clause, all rows will be deleted. If you need to delete all the contents of a table: DELETE FROM books; However, this does not reset the indexes of a table, therefore a better command is: TRUNCATE books;

Where Clauses Where clauses are very important to select, update, and delete statements. Where

Where Clauses Where clauses are very important to select, update, and delete statements. Where clauses can be in multiple formats and contain multiple parameters: WHERE number (NOT) IN (0, 1, 2); (book_id = 1 AND ISBN IS (NOT) NULL) OR (date > 10212004) AND (date < 11222004); (quantity + 2) = 4; location = 0; Where clauses can be quite long and complex, as the programmer you need to know how to read them as well as script them.

Altering Tables Altering tables enables you to change the structure of a table, whether

Altering Tables Altering tables enables you to change the structure of a table, whether it be changing default values to removing or adding columns. ALTER (IGNORE) TABLE tablename alter_specification; ALTER TABLE books ADD date timestamp; ALTER TABLE books RENAME catalog; ALTER TABLE books MODIFY author INT(11) NOT NULL; ALTER TABLE books CHANGE ISBN price float; ALTER TABLE books DROP COLUMN quantity;

Drop Statements Drop syntax deletes entire tables or databases. DROP TABLE tablename; DROP TABLE

Drop Statements Drop syntax deletes entire tables or databases. DROP TABLE tablename; DROP TABLE books; DROP databasename; DROP library; This is for deleting databases and tables, not just clearing the entries!

More on Selects Select Statements can be used for multiple tables: Example: SELECT author_id

More on Selects Select Statements can be used for multiple tables: Example: SELECT author_id FROM authors WHERE lastname LIKE “%Rawling%”; +------+ | author_id | +------+ | 1029 | +------+ SELECT title FROM books WHERE author_id = 1029; +---------------------+ | Title | +---------------------+ | Harry Potter and the Sorcerer's Stone | | Harry Potter and the Chamber of Secrets | +---------------------+

More on Selects Instead of doing two queries, combine them: SELECT b. title FROM

More on Selects Instead of doing two queries, combine them: SELECT b. title FROM authors a, books b WHERE a. author_id AND a. lastname LIKE “%Rawling%”; b. author_id = +---------------------+ | Title | +---------------------+ | Harry Potter and the Sorcerer's Stone | | Harry Potter and the Chamber of Secrets | +---------------------+ Even better, we can use a ‘left join’: SELECT b. title FROM books LEFT JOIN authors ON b. author_id = a. author_id WHERE a. lastname LIKE “%Rawling%”; Join’s are generally better. This can also be applied to multiple UPDATES and DELETES.

Indexes What are indexes? When talking about databases, indexing is a technique used by

Indexes What are indexes? When talking about databases, indexing is a technique used by most current database management systems to speed up particular kinds of queries (usually by internally generating and storing redundant information to more quickly locate table entries). For integers, strings, and text, this is invaluable! Especially if the size of the table is large!

Creating Indexes Syntax: CREATE (UNIQUE|FULLTEXT|SPATIAL) INDEX index_name (index_type) ON tablename (column 1, column 2,

Creating Indexes Syntax: CREATE (UNIQUE|FULLTEXT|SPATIAL) INDEX index_name (index_type) ON tablename (column 1, column 2, …); CREATE INDEX isbn_index ON books (ISBN); You can specify a certain amount to index on, especially helpful with strings: CREATE INDEX title_index ON books (title(11)); Unique indexes help prevent duplicate entries: CREATE UNIQUE INDEX author_lastname ON author (lastname(11)); You can assign an index to multiple columns to prevent duplicate entries: CREATE UNIQUE INDEX author_ref ON books (title, authorid);

Creating Tables With Indexes Syntax: CREATE TABLE tablename ( column 1 attributes, … KEY

Creating Tables With Indexes Syntax: CREATE TABLE tablename ( column 1 attributes, … KEY index_name (column(size)) ); CREATE TABLE books ( book_id INT(4) PRIMARY KEY NOT NULL AUTO_INCREMENT UNSIGNED, title VARCHAR(255), author VARCHAR(255), KEY title_index (title(11)), KEY author_index (author(11)), ); Note: book_id does not need an index since primary key attribute takes care of it.

Dropping Indexes Syntax: DROP INDEX index_name ON tablename; DROP INDEX author_index ON books;

Dropping Indexes Syntax: DROP INDEX index_name ON tablename; DROP INDEX author_index ON books;

My. SQLdump is a command which dumps defined contests of a database. This is

My. SQLdump is a command which dumps defined contests of a database. This is handy in terms of backups: Syntax: mysqldump –u username –p (databasename) (tablename) Useful flags: -A|--all-databases – dumps all databases -a|--all – same as previous -c|--complete-insert – use complete insert statements -e|--extended-insert – use extended insert statements --add-drop-table – add a drop table before each create table syntax -h|--host – define a host -n|--no-create-db – do not specify a create database command -d|--no-data – do not include the data -P|--port --tables

My. SQLdump A method of backing up a entire database in *nix is: mysqldump

My. SQLdump A method of backing up a entire database in *nix is: mysqldump –u username –p database > date. sql Mysqldump exports to the console screen, so it is useful to pipe it into a file, no idea how to do this in MSDOS. There are two ways to restore a backup to mysql: 1. Pipe data back into mysql server: mysql –u username –p databasename < file mysql –u user –p library < date. sql 2. Source data from within mysql server: source filename source date. sql

My. SQL limitations n n n If My. ISAM table types are used (default),

My. SQL limitations n n n If My. ISAM table types are used (default), transactional support is disabled. To enable transactions you will need to set up My. SQL to use Inno. DB tables and create the database tables as Inno. DB type. No views, Triggers, Stored procedures support User defined functions only possible with external C/C++ code Foreign keys supported only for Inno. DB tables No support for cursors No nested select statement

References n n n http: //php. net http: //mysql. com http: //www. wikipedia. net

References n n n http: //php. net http: //mysql. com http: //www. wikipedia. net http: //www. mysql. com https: //ims. ecn. purdue. edu/documentation. html