Transcript 2-Database Software

Database Software
Free Relational Databases
MySQL - one of the most popular

the "M" in "LAMP" and "XAMP" stacks

"free" for non-commercial use only

many useful tools:

MySQL Administrator (Windows or Linux)

MySQL Workbench & Query Browser

phpMyAdmin

Owned by Oracle

http://www.mysql.com
Free Relational Databases
PostgreSQL

Based on Berkeley Postgres

Open Source, can be used in commercial apps without
a licence

Reputation as very stable and reliable

Included with Linux distros

Has some O-O features

http://www.postgresql.org
Client - Server Databases



Database Server is a separate process running on a
host.
Clients can run on any machine.
Many programs may be clients using a standard API.
"mysql" utility
Java app
using JDBC
Excel client
Client side
Server
(mysqld)
server controls
access to
database
Server side
Why the "d" in mysqld



The MySQL server is named "mysqld".
Other programs ending in "d":
 ftpd - ftp server
 httpd - HTTP server
 sshd - Secure Shell server
Why "d"?
Server
(mysqld)
database
Lightweight & Embedded Databases
"Light" - they don't consume much cpu or memory.
"Embedded" - database manager is included
(embedded) into your application.

Called "embedded mode".
MyApp
Connection
include the database
software (jar) with
your application.
derby.jar
Software in JAR manages
database as plain files.
No server process.
Hypersonic SQL
HSQLDB - lightweight, fast database written in Java

database can be stored in memory or on disk.

embed in Java app - no separate server

don't need to install database server or disk-based
database

can also run in client-server mode

useful for development and "demo" systems

http://hsqldb.org
Derby
Derby - lightweight, pure Java database

formerly "Cloudscape", donated to Apache foundation

only 1 user can connect to database at a time

embed in Java applications - no separate server

similar to HSQLDB

can also run in client-server mode

included with JavaEE as "Java DB"

http://db.apache.org/derby
SQLite
World's most widely distributed database

written in C

very small: 350KB binary

used on Android

3rd party JDBC drivers:

http://code.google.com/p/sqlite-jdbc/

http://www.ch-werner.de/javasqlite/

http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC
Berkeley DB
Berkeley DB - sleepycat.com (RIP)

libraries for embedded database using the OS's file system.

No db manager, No network access, No query language.

used as data tier for LDAP, sendmail, and many other apps

very small and fast -- faster than any relational DB w/ manager

C and pure Java version

language bindings for C++, Perl, Python, Ruby, and more

bought by Oracle in 2006:
http://www.oracle.com/database/berkeley-db/index.html

still Open Source under the "Sleepycat Public License" and
"Sleepycat Commercial License",

not required to distribute the source code with your app.
"Community Edition" Databases
IBM DB2 Express-C - relational DB with XML support
 free edition of IBM DB2
 good documentation and learning tools:
http://www.ibm.com/university
 http://www.ibm.com/db2/express
Oracle 11g Express Edition (XE)
 leading market share among commercial databases
 XE is easier to administer than full Oracle
 http://www.oracle.com
Commercial Databases
Databases ranked by 2006 revenue (million US$).
Source: Gartner Research (www.gartner.com)
8000
7000
6000
5000
4000
3000
2000
1000
0
Oracle
IBM
Microsoft Teradata Sybase
Other
Install and Admin MySQL
Getting MySQL
http://dev.mysql.com/downloads/
Server and client
Query Browser,
Admin Tool
Java, ODBC, .Net, PHP
Many platforms:
Windows, Linux,
Solaris, Mac OS-X
MySQL Software
Server and Client
Download MySQL "Community Edition" from www.mysql.com
Ubuntu can install using package manager or Synaptic
GUI Tools
mysql-gui-tools-5.2r6-platform
mysql-query-browser-1.1.17-win.msi
Connectors
Connector/J
Java JDBC
For CPE, download from http://se.cpe.ku.ac.th/download/mysql
How to Administer MySQL
To manage a MySQL server, you need an administrator
account (root) and administration tool:

mysqladmin - command line tool (included)

MySQL Administrator - part of MySQL Workbench
Other Tools:

phpMyAdmin - Web-based admin tool, open source

Webmin - another Web-based admin tool, for Linux
MySQL Administrator
Easy to use GUI interface.
Connection Dialog
Main Window, "Catalogs" view
mysqladmin and mysql
Useful command line tools.
 Change MySQL administrator password.
 Create new database.
 Import data / export data. Backup a database.
 Modify privilege tables.
cmd> mysqladmin
Usage: mysqladmin [OPTIONS] command command....
Where command is a one or more of:
create databasename
Create a new database
drop databasename
Delete a database and all its tables
flush-tables
Flush all tables
password new-password Change old password to new-password
reload
Reload grant tables
shutdown
Take server down
status
Gives a short status message from server
version
Get version info from server
Create a database
cmd> mysql -h hostname -u root -p
Password: ********
mysql> create database Students;
Query OK, 1 row affected
mysql> use Students;
Database changed
mysql> show tables;
Empty set
Create a table in batch mode
It is easier and more repeatable to put SQL commands in a text file
and process the file using a MySQL client.
(1) create a text file ("student-schema.sql") containing commands:
DROP TABLE IF EXISTS 'Students';
CREATE TABLE 'Students' (
`ID` int(11) PRIMARY KEY NOT NULL auto_increment,
`Name` varchar(60) NOT NULL DEFAULT '',
`Telephone` char(12),
`Birthday` date
) DEFAULT CHARSET=utf8;
(2) use mysql command tool to source the text file:
cmd>
mysql -u root -p
mysql> use Student;
mysql> source student-schema.sql;
Another Batch Example
-- create table for student data
-- use the 'UTF8' character set for Thai names
-- Jim Brucker, Jan 2006
USE test;
-- this will discard any existing data!!!
DROP TABLE IF EXISTS students;
CREATE TABLE students (
id CHAR(8) PRIMARY KEY NOT NULL,
prefix VARCHAR(24) NOT NULL DEFAULT '',
firstname VARCHAR(40) NOT NULL DEFAULT '',
lastname VARCHAR(40) NOT NULL DEFAULT '',
enfirstname VARCHAR(40) NOT NULL DEFAULT '',
enlastname VARCHAR(40) NOT NULL DEFAULT ''
) DEFAULT CHARSET=utf8;
Adding Data to Tables
1. Text file containing SQL "INSERT" commands.
2. Comma-delimited (CSV) file. Can be created using Excel.
Comma Separated Values files
Comma Separated Values (CSV) is a common interchange format
for text data. Used by Excel, Yahoo AddressBook, ... many apps.
"James","Brucker","[email protected]",1234
"George","Bush","[email protected]",1111
"Santa","","[email protected]",001
cmd>
mysql -h hostname -u root -p
Password: ********
mysql> LOAD DATA INFILE '/path/filename'
INTO TABLE tbl_name
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' ;
Query OK, 499 rows affected
MySQL Users
What identifies a user?

"student" on local machine may not be the same
person as "student" on pirates.com

even if "student" is the same, you may want to assign
different privileges to local and network logins.
MySQL identifies users as: username@hostname
student@localhost
student@% (any host)
[email protected]
Creating a User

Easy way: use MySQLAdmin or phpMyAdmin.

Command line (MySQL 5.0):
mysql> CREATE USER 'user1'@'localhost'
IDENTIFIED BY 'secret';
mysql> CREATE USER 'user1'@'%.ku.ac.th'
IDENTIFIED BY 'secret2';
mysql> CREATE USER 'user1'@'%'
IDENTIFIED BY 'hackme';
Managing Users and Permissions

Give "guest" permission to view the World database:
sql> GRANT SELECT ON world.* TO guest;

Allow "student" to insert/edit records in the City table:
sql> GRANT INSERT,UPDATE ON world.City TO student;

All "student" to modify the population field of existing
countries (but not add new countries):
sql> GRANT UPDATE(population) on world.Country
TO student;

Deny all privileges to everything to "hacker":
sql> REVOKE ALL on *.* TO Hacker;
GRANT / REVOKE Syntax
GRANT privilege[(column_list)] [, ... ]
ON { table_name | * | *.* | db_name.* }
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, ... ]
[WITH with_option ... ]
privilege:
SELECT, INSERT, UPDATE, DELETE, REFERENCES,
CREATE, ALTER, DROP, INDEX,
CREATE_VIEW, SHOW_VIEW
with_option
GRANT OPTION
MAX_USER_CONNECTIONS count
MAX_CONNECTIONS_PER_HOUR count
MAX_QUERIES_PER_HOUR count
GRANT / REVOKE Example
GRANT select,insert,update,delete ON world.*
TO student@'%' IDENTIFIED BY 'secret' ;
student can query, insert, update, and delete records in
the world database, but he can't change the database
schema or indexing, can't grant privileges to others.
This command also creates a student user with
password secret.
GRANT / REVOKE Example
GRANT ALL ON wiki.*
TO 'wikiadmin'@'localhost'
IDENTIFIED BY 'secret' ;
Create an admin user for the "wiki" database so that you
can create tables, indices, etc.
Access allowed only on local machine, not over network.
Typical way of setting database permissions for a web
application.
Privileges you can GRANT and REVOKE
Type of Operation
View table data
Add rows to a table
Modify data in a table
Delete rows
Reference a table from another
Drop tables
Create or Alter tables
Index a table by an expression
All privileges
Statement
SELECT
INSERT
UPDATE
DELETE
REFERENCES
DROP
CREATE, ALTER
INDEX
ALL
Exercise

Create a user named "hacker".

Give hacker permission to view data in world.Country
and world.City, but not world.CountryLanguage.

Give hacker permission to view, insert, update, and
delete rows in all tables in the test database.
Resources
MySQL
 http://dev.mysql.com/tech-resources/articles/dotnet/
Learning SQL
 http://www.w3schools.com/sql/
nice tutorial and command reference