SQL-Based Relational Databases and Perl


1.  To use ANY database on Perl, you must download the DBI module.  This
    is the module that allows you to use all the database calls in Perl.


2.  If you wish to use the simple CSV database (which is the DB we will use
    in this course and is already on Voyager), you must download the following
    Perl modules IN THE ORDER GIVEN:

       SQL::Statement
       Text::CSV_XS
       DBD::CSV

3.  If you are working on any computer other than Voyager you must get the
    above-mentioned modules.  If you are a Windows user, you use ActiveState's
    Perl Package manager by typing ppm in your DOS window.  Then you get the
    "ppm>" prompt.  Here's how you'll do the above installs (prompt shown):

       ppm> install DBI
       ppm> install SQL::Statement
       ppm> install Text::CSV_XS
       ppm> install DBD::CSV

    The installs will all cause a delay followed by the ppm telling you the
    zillions of files it is installing.

4.  If you are going to use a Unix/Linux system that is NOT Voyager, then
    you must get these modules from www.cpan.org.  The Unix/Linux downloads
    are zipped-and-tarred files.  You must unzip and then untar them (I
    certainly assume you know how to do this if you are Unix-savvy!!).
    The readme file associated with each download tells you how to install
    the module but it is virtually always the following sequence:

       perl Makefile.PL
       make
       make test
       make install

5.  The advantage of the CSV database is that IT IS NOT A REAL DATABASE.
    Therefore, it requires no administration, no starting of a database
    daemon at login time -- but it WILL allow you to learn SQL and do
    simple table creations, data insertions, data queries, data deletions,
    and other simple operations. 

    If you are a brave soul and want a REAL database the website www.mysql.com
    has Unix and Windows versions of the MySQL database.  If you really want
    to learn how to use a real industrial-strength DB then I advise you to get
    it.  However, there will be no instruction on how to administer and use
    this or any other commercial database. 


                SQL: What It Looks Like Inside of Perl 
      
1.  Connecting to the CSV database inside of a Perl program.

       use DBI;
       $db = DBI->connect("DBI:CSV:f_dir=dirname") || die "Error message\n";

    You will replace "dirname" with the directory which will contain your
    database file(s).

2.  Creating the definition of a table in a Perl program.

       $cmd = $db->prepare("CREATE TABLE filename (
                             fieldname datatype,
                             ...)");
       $cmd->execute() || die "Cannot create table!\n";

    You will replace "fieldname" with the name you wish to give the field
    and datatype with one of the following:

       FLOAT REAL INTEGER or VARCHAR(N)

    where "N" is some integer length.  If a field is not allowed to have
    a NULL value, you will follow the datatype with "not null" such as:

       name varchar(32) not null

3.  If you are using a commercial database, you are advised to create the
    table definition IN THE DATABASE ITSELF, NOT IN YOUR PERL PROGRAM.
          
4.  To populate tables with data, you do this:

        $row = $db->prepare("INSERT INTO filename (field1,field2,...)
                             VALUES ('value1', 'value2',...)");
        $row->execute();

    You will replace "field1" and so on with the actual field names in
    the file and "value1" (and so on) with values (or Perl variable
    names containing those values!) with which you are going to put in
    the fields.

5.  To get data from tables:

       $query = $db->prepare("SELECT field1,...
                              FROM filename
                              WHERE condition");
       $query->execute();

    You will replace "field1,..." with the fields you wish to retrieve.
    If you want ALL fields, enter a "*" after the SELECT.  The WHERE
    clause is optional if you want to retrieve the selected fields from
    ALL records.  The "condition" involves comparing fields with 
    =, <> (not equal), <, >, IN, or LIKE with certain values.  AND, OR,
    and NOT allow multiple conditions or negation.  The IN boolean operator
    works like this:

        SELECT *
        FROM myfile
        WHERE animal IN ('Giraffe', 'Lion', 'Bear')

    In other words, it is like asking if the animal field of any record
    contains one of the three given animals.

    The LIKE boolean operator allows % (multi-character) and _ (single
    character) wildcard matching.  Example:

        SELECT *
        FROM myfile
        WHERE animal LIKE 'G%'

    Will get rows where the animal field starts with capital "G".

6.  After you have executed a SELECT query, you must use the reference
    returned by the "prepare" statement to look at the rows.  There are
    several ways to do so.  Here are examples:

        while (($name, $address, $email) = $query->fetchrow_array)

    This loop keeps iterating until there are no more rows.  Of course,
    the fields you fetch should be equal in number to what you asked
    for on the SELECT line!  Here's another method of getting the
    query result set:

        while ($rowref = $query->fetchrow_hashref)

    If the fields fetched are "name" and "email" then $rowref->{'name'}
    and $rowref->{'email'} will contain the fetched values.

7.  Deleting rows from a table:

        DELETE FROM filename WHERE condition

    The WHERE clause works just like it did in SELECT.  Of course, you
    will run this (and all other SQL statements!) with $db->prepare
    and execute as shown with the SELECT.

8.  Editing data in a table:

        UPDATE filename SET field = 'value' ,...
        WHERE condition

    "Condition" is just like SELECT's WHERE clause.  You may change as
    many fields as you wish after SET via a comma separator as shown.
 
9.  Disconnecting from the database:

        $db->disconnect 
 

10. The definitive reference for the DBI module:

        Programming the Perl DBI by Alligator Descartes and Tim Bunce,
        an O'Reilly and Associates publication.