The Perl DataBase Interface DBI module provides an API for accessing relational databases. There are many different database products available, with different capabilities.
Fortunately these databases share a common language, SQL - Structured Query Language. Although the databases have widely varying features and a significant range of SQL dialects there is sufficient commonality to provide a uniform API in Perl.
Perl DBI provides uniform access to each database isolating the Perl programmer from the details of specific products. DBI has a simple interface for constructing database queries and handling the results. DBI doesn't know how to talk to specific databases but it knows how to locate and load a DataBase Driver or DBD module. The DBD modules have the vendor libraries in them so that they can interface to the specific database. There is a DBD module for each type of database.
DBI effectively isolates Perl applications from the idiosyncrasies of differing databases. In doing so it provides code level portability across and between differing databases. Migrating data between databases becomes a relatively simple task. Used in conjunction with the CGI module the data can also be published over the inter/intra/extra net.
The most widely used open source database at present is MySQL. It's popularity is not merely a result of it being free (support costs money) but a result of it's design. MySQL has no bells or whistles, these would compromise performance. What it lacks in features and functionality it certainly makes up in performance. MySQL is a highly scalable, high speed, multi threaded, robust multi user relational database management system (RDBMS). All the essential features of a relational database are included but it is not a transaction engine. Transactions can be implemented but that is not its intended application area (no record locking or rollback). It is however an industrial strength data logging and distribution server ideally suited to networked applications and web based applications.
MySQL supports the following SQL:
This is not a complete list but covers the most relevant commands. What is not included is information about the WHERE clause. There are plenty of online resources to help.
MySQL can be run from the command line by typing the command:
bukowski ->mysql -h studb.cms.gre.ac.uk -u zz999 -p mdb_zz999 Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3009 to server version: 3.23.53 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show tables; +---------------------+ | Tables_in_mdb_mkg01 | +---------------------+ | CD | | book | | course | | foo | | mailbase | | mlist | | recommend | +---------------------+ 7 rows in set (0.00 sec) mysql> select * from mlist; +----+----------------+------------+----------+----------+-----------------------------+------------------+ | id | dtstamp | title | initials | surname | email | platforms | +----+----------------+------------+----------+----------+-----------------------------+------------------+ | 1 | 20021110224919 | Mr | L | Torvalds | torvalds@transmeta.com | Unix | | 2 | 20021110225007 | Mr | G W | Bush | gwb@whitehouse.gov | WinME | | 3 | 20021110225037 | Mr | H | Janus | hugh@microsoft.com | WinXP | | 4 | 20021110225145 | Rt Hon Sir | B | Tufton | bufton@toryparty.gov.uk | Win95 | | 5 | 20021110225229 | Dr | G | McMuppet | gonzo@bzort.net | Unix Win95 Win2k | | 6 | 20021110225259 | Mr | L | Wall | larry@oreilly.com | Unix | | 7 | 20021111010016 | Dr | F N | Stein | frank.n.stien@monster.co.uk | Unix | | 8 | 20021111080120 | Mr | R J | Yarger | randy@oreilly.com | MacOS | | 10 | 20021111080433 | Dr | C | Pancake | cherry@icase.nasa.gov | MacOSX | +----+----------------+------------+----------+----------+-----------------------------+------------------+ 10 rows in set (0.00 sec) mysql> show columns from mlist; +-----------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------+------+-----+---------+----------------+ | id | int(11) | | PRI | NULL | auto_increment | | dtstamp | timestamp(14) | YES | | NULL | | | title | tinyblob | YES | | NULL | | | initials | tinyblob | YES | | NULL | | | surname | tinyblob | YES | | NULL | | | email | tinyblob | YES | | NULL | | | platforms | tinyblob | YES | | NULL | | +-----------+---------------+------+-----+---------+----------------+ 7 rows in set (0.00 sec) mysql> quit Bye bukowski -->
...all of which would arguably be better run from the MySQL web interface - why?.
Note that a MySQL user in not the same as a Unix user (MySQL runs on a wide range of operating systems). MySQL supports it's own set of users and passwords.
In addition to running from the mysql command prompt there are a number of command line tools, third party tools, CGI interfaces, client applications, programming interfaces and mysql interfaces into other tools. Which makes MySQL one of the better supported databases currently available. One tool that should be familiar at that start is mysqldump (even though mysql can dump itself).
mysqldump -h studb.cms.gre.ac.uk -u mkg01 -p mdb_mkg01 > dump20021110.sql
This will dump the database mdb_mkg01 in SQL format to the file dump20021110.sql. Note the use of an ISO standard date and time string. The SQL file is in ASCII text and may be safely edited amd transferred between MySQL installations (home -> university -> home). To restore from a dump, or a partial dump a SQL file can be read into MySQL
mysql -h studb.cms.gre.ac.uk -u mkg01 -p mdb_mkg01 < dump20021110.sql
If you forget the syntax for mysql, mysqldump, mysqladmin or any other commands then try the man pages or the -h flag (same as --help).
.
.
