Database Software

1. Database Software James Brucker

2. 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 SOLD

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

4. 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. Server (mysqld) "mysql" utility Java appusing JDBC server controls access to database Excel client Client side Server side

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

6. 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 derby.jar Software in JAR manages database as plain files. No server process. include the database software (jar) with your application.

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

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

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

10. 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. SOLD

11. "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

12. Commercial Databases Databases ranked by 2006 revenue (million US$). Source: Gartner Research (www.gartner.com) Name that database!

13. Install and Admin MySQL

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

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

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

17. MySQL Administrator Easy to use GUI interface. Connection Dialog Main Window, "Catalogs" view

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

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

20. 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;

21. 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;

22. Adding Data to Tables • Text file containing SQL "INSERT" commands. • Comma-delimited (CSV) file. Can be created using Excel.

23. 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","jb@yahoo.com",1234 "George","Bush","president@whitehouse.gov",1111 "Santa","","claus@northpole.org",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

24. 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) hacker@10.1.2.3

25. 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';

26. 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;

27. GRANT / REVOKE Syntax GRANTprivilege[(column_list)] [, ... ] ON { table_name | * | *.* | db_name.* } TOuser [IDENTIFIEDBY [PASSWORD] 'password'] [, ... ] [WITHwith_option ... ] privilege: SELECT, INSERT, UPDATE, DELETE, REFERENCES,CREATE, ALTER, DROP, INDEX,CREATE_VIEW, SHOW_VIEW with_option GRANT OPTIONMAX_USER_CONNECTIONS countMAX_CONNECTIONS_PER_HOUR countMAX_QUERIES_PER_HOUR count

28. GRANT / REVOKE Example GRANTselect,insert,update,deleteONworld.* TOstudent@'%'IDENTIFIEDBY'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.

29. GRANT / REVOKE Example GRANTALLONwiki.* TO 'wikiadmin'@'localhost' IDENTIFIEDBY'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.

30. Privileges you can GRANT and REVOKE Type of Operation Statement View table data SELECT Add rows to a table INSERT Modify data in a table UPDATE Delete rows DELETE Reference a table from another REFERENCES Drop tables DROP Create or Alter tables CREATE, ALTER Index a table by an expression INDEX All privileges ALL

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

32. Resources MySQL • http://dev.mysql.com/tech-resources/articles/dotnet/ Learning SQL • http://www.w3schools.com/sql/ nice tutorial and command reference