NEBC Database Course 2008 Database Servers Database Interfaces Tim - PowerPoint PPT Presentation

NEBC Database Course 2008 Database Servers Database Interfaces Tim Booth : tbooth@ceh.ac.uk

What is an RDBMS? A PostgreSQL database is not just kept in a big file, like a ● spreadsheet. A program called the database server, or RDBMS, manages all ● the data for us. We access the data via a client application which connects to ● the RDBMS. RDBMS Client software User Databases

Why use an RDBMS? With RDBMS: Multiple concurrent users ● Transaction support (aka A.C.I.D.) ● Advanced security ● Remote access ● Reliability ● You can create a web interface in, eg., Perl or PHP. ●

Which RDBMS to choose? PostgreSQL MySQL Oracle Free (BSD) Free (GPL)/Commercial Commercial Unix, including Linux or Just about any platform Many platforms MacOS X. Windows since v8.1 Transactions supported on Full transaction support Full transaction support newer table types Full subqueries and updatable Support for SQL-mandated Full subqueries and updatable views subqueries and views since 4.1 views Stored procedures via plugin Stored procedures since 5.0 Stored procedure support modules Supports groups, table-level user Fine-grained user privileges, no Full user/group privilege support privileges group support PgAdminIII or pgpPgAdmin are Comprehensive range of phpMyAdmin (web based) the best development tools commercial software support. TOra (a free tool originally for Oracle) around. TOra support is in the Administration/development via Also MySQL administrator pipeline. TOra Now well established with a large Well established, especially in Very well established. Oracle developer community, high web development. market their software as compatibility and many Fast and lightweight, but still has 'unbreakable', and it is highly advanced features. some rough edges. regarded.

Databases on Bio-Linux PostgreSQL: ● – Installed and running on Bio-Linux 5. – Even on the Live system you can start it manually: sudo pg_createcluster 8.3 main sudo /etc/init.d/postgresql-8.3 start MySQL: ● – Runs by default on installed BL5 systems. To access: sudo mysql – Gives you an administrator login at the console.

Database Clients Any program which lets a user communicate with a database. ● Custom – designed for end-users, masks the database ● internals. – Specific to database, could be web-based or standalone – eg Google Search, Ensembl web interface Generic – you can execute SQL directly ● – Used by database developers and administrators – May be command-line based, graphical, web based – Connect to any database: eg. Microsoft Access, PGAdmin, psql

PgAdminIII Used on this course ● Runs on a variety of platforms ● Only for PostgreSQL ● Graphical interface to create tables, manage privileges etc. ● SQL editor with syntax highlighting and online help ● Graphical query optimizer ●

Command-line clients Every RDBMS has a basic command-line client – eg psql: ● psql -h ivgfs Type SQL commands – don't forget to end with a semicolon Backslash commands to show table names and descriptions: \d - List all tables \d tablename - Describe a table \q - To quit This interface may look basic, but like the Linux shell it has ● some useful features, for example tab completion of table/column names. You might also want to look at 'dbish', a command-line ● interface to any database.

phpPgAdmin Has most of the features of pgAdmin3 ● Access is via a web browser. ● You need to set up the scripts and run Apache on the machine. ● http://phppgadmin.sourceforge.net/

Hooking up with Microsoft Access You can view and manipulate the data in your PostgreSQL or ● MySQL databases using Access on a Windows PC. Access has a very flexible GUI, but the actual database ● component is weak. If the database is on Linux, you first need to setup remote ● connections to PostgreSQL. (More on that tomorrow) You will also need some extra software on the Windows box - ● this is outlined here: – http://pgfoundry.org/projects/psqlodbc/

Hooking up with OpenOffice The BASE program in OpenOffice is broadly equivalent to MS ● Access. On Bio-Linux, you need to install the package: ● – openoffice.org-sdbc-postgresql Now see the instructions here: ● http://dba.openoffice.org/drivers/postgresql/index.html#install_2.0 A typical conection string would be ● – “dbname=test host=localhost”

Some NEBC software which uses PostgreSQL and MySQL The omixed server system runs on a MySQL database backend. ● http://omixed.org The GenQuery tool can produce web interfaces on any RDB. ● http://nebc.nox.ac.uk/projects/genquery The maxdLoad2 software enables storage of microarray ● experiment data into various databases, including PostgreSQL. The software comes pre-installed on Bio-Linux. http://nebc.nox.ac.uk/maxd.html PartiGene is an EST clustering application. It can save results ● into a PostgreSQL database. Also pre-installed on Bio-Linux. http://nebc.nox.ac.uk/est.html