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. Databases User Client software RDBMS

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?

Oracle Free (BSD) Free (GPL)/Commercial Commercial Just about any platform Many platforms Full transaction support Full transaction support Stored procedures since 5.0 Stored procedure support Full user/group privilege support PostgreSQL MySQL Unix, including Linux or MacOS X. Windows since v8.1 Transactions supported on newer table types Full subqueries and updatable views Support for SQL-mandated subqueries and views since 4.1 Full subqueries and updatable views Stored procedures via plugin modules Supports groups, table-level user privileges Fine-grained user privileges, no group support PgAdminIII or pgpPgAdmin are the best development tools

• around. TOra support is in the

pipeline. phpMyAdmin (web based) TOra (a free tool originally for Oracle) Also MySQL administrator Comprehensive range of commercial software support. Administration/development via TOra Now well established with a large developer community, high compatibility and many advanced features. Well established, especially in web development. Fast and lightweight, but still has some rough edges. Very well established. Oracle market their software as 'unbreakable', and it is highly 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