The Basics

There are four main steps in using DBD::AnyData in a script:

 1. Specify that you want to use the DBI module
 2. Create a database handle
 3. Specify the tables, files, and formats you want
 4. Use DBI/SQL commands to access and/or modify the data
    

Steps #1, #2, and #3 can be as little as a single line of code each.

Steps #3 and #4 can be omitted in some situations, see the sections below on "Working with In-Memory Data" and "Converting Data"

Step #1 : Specify that you want to use the DBI module

This step is always the same: just put this at the top of your script:

  use DBI;
    

Step #2 Create a Database Handle

This step can vary slightly depending on your needs but is usually this:

  my $dbh = DBI->connect('dbi:AnyData(RaiseError=>1):');
    

See the section below on "Connection Options" for other forms of connecting. See the section below on "Using Multiple Databases" for cases in which you may be creating more than one database handle.

Step #3 : Specify the tables, files, and formats

This step makes use of one of several methods unique to DBD::AnyData. These methods use the database handle created in step #2 to make a func() call and specify the name of the method as the last parameter. For example the 'ad_catalog' method would be called like this:

  $dbh->func( ..., 'ad_catalog')
    

The ad_catalog() method takes three required parameters and one optional parameter:

  # $table  = the name you will use to refer to the table in SQL commands
  # $format = the format of the data ('XML', 'CSV', 'Fixed', etc.)
  # $file   = the name of a local or remote file holding the data
  # $flags  = an optional hash of flags required by some data formats
  $dbh->func( $table, $format, $file, $flags, 'ad_catalog')

  # For example:
  $dbh->func( 'cars', 'XML', 'cars.xml', 'ad_catalog' )
    

This specifies that the table name 'cars' will be used to access XML data stored in the file 'cars.xml'.

Once you have issued a catalog command, you can use the name $table in SQL commands to access or modify the data in $file. The catalog only needs to be specified once for a table/file/format combination and can then be used for an unlimited number of processing commands.

Step #4 : Use DBI/SQL commands to access and/or modify data

DBD::AnyData supports all standard DBI methods and a subset of SQL syntax. See the section below "SQL Syntax" for a description of the supported SQL commands. See the DBI documentation for detailed description of DBI commands.

The do() method can be used to create or drop a table and insert, delete, or update rows:

  $dbh->do("CREATE TABLE ... )
  $dbh->do("DROP TABLE ... )
  $dbh->do("INSERT INTO ... )
  $dbh->do("UPDATE ... )
  $dbh->do("DELETE ... )
    

A combination of the prepare(), execute(), and fetch() methods can be used to access data:

  my $sth = $dbh->prepare("SELECT * FROM cars WHERE make = 'Honda'");
  $sth->execute();
  while (my $row = $sth->fetchrow_hashref){
      print $row->{model};
  }
    

Putting it all together

This is the complete script needed to access data stored in CSV format in a file called "cars.csv". It prints all data from the "make" and "model" columns of the database.

  # specifies that you will use the DBI module.
  use DBI;
  # creates a database handle
  my $dbh = DBI->connect('dbi:AnyData(RaiseError=>1):');
  # specifies the table, format, and file holding the data
  $dbh->func( 'cars', 'CSV', 'cars.csv' 'ad_catalog');
  # through 8 use DBI and SQL to access data in the file
  my $sth = $dbh->prepare("SELECT make, model FROM cars");
  $sth->execute();
  while (my $row = $sth->fetch) {
      print "@$row\n";
  }
    

Customizing table structure

You can specify this additional information in the $flags parameter of the ad_catalog and other DBD::AnyData methods. $flags is always a reference to a hash, i.e. one or more key value pairs joined with a =>, separated by commas, and delimited by curly braces:

  $flags = { key1 => value1, key2 => value2 ... }

  # or in the method call:
  $dbh->func( $table, $format, $file, { key1=>,val1 ... }, 'ad_catalog');

Column Names

Some formats have pre-defined column names:

    Passwd  username
            passwd
            UID
            GID
            fullname
            homedir
            shell

    Weblog  remotehost
            usernname
            authuser
            date
            request
            status
            bytes
            referer
            client

    Mp3     song
            artist
            album
            year
            genre
            filename
            filesize
    

Column names for the other formats can either be specified in the database itself or supplied by you in the $flags parameter.

If the column names are specified in the database, they are taken from the first record in the database. For example in a CSV (Comma Separated Values) file or a Fixed Length file, the default is to treat the first line of the table as the list of column names. In an HTMLtable file, the default is to look for the first <tr> in the first table. In an XML file, the default is to use the names of all attributes and all CDATA and PCDATA elements contained within the first non-initial tag.

In most cases, this first record that defines the column names is in the same format as the rest of the table e.g. a CSV string in a CSV file, a tab delimited string in a Tab delimited file, etc. The one exception to this is that in a Fixed Length file the first row of the file can contain a *comma-separated* list of column names, not a fixed length list. HTMLtable and XML also use other flags to select the column names (e.g. the number of the table or the depth in the tree to examine). Please see the documentation for these formats for further details of how defaults are selected.

For most formats, if the column names are not contained in the first record in the file, then you can specify them as a comma separated list in the $flags parameter, for example:

  $dbh->func(
      'cars',
      'Tab',
      'data.tab',
      { col_names => 'make,model,year' },
  'ad_catalog')
    

CSV, Tab, Pipe, Ini, Paragraph

Fixed

Secondly, there is a mandatory flag called 'pattern' that you must use whenever you use the Fixed length format. This flag specifies the widths of the columns. It uses the standard Perl pack/unpack syntax to specify the pattern. See the Perl documentation for those commands for further details. In most cases simply using a capital 'A' followed by the length of the field suffices:

  { pattern => 'A10 A12 A4' }

This specifies that the table contains three fields with widths of 10, 12, and 14 characters.

XML

HTMLtable

DBI

Use the format name 'DBI', and instead of a filename, pass the ad_import call a connection in whatever database you are using, and specify a SQL SELECT statement:

  my $dbh = DBI->connect('dbi:AnyData:(RaiseError=>1)');
  $dbh->func(
      'table1',
      'DBI',
      DBI->connect('dbi:mysql:database=test:(RaiseError=>1)'),
      {sql=>"SELECT make, model FROM cars WHERE make = 'honda'"},
  'ad_import');

That snippet imports a view from a MySQL database (selecting only the named columns and the selected rows) into an AnyData in-memory table. It can then be queried and/or modified in memory and then either displayed or stored to a file in some other format such as XML.

You may also use a bind_parameters form for the SQL call by passing an additional flag with an arrayref of the parameters:

  {
      sql     => "SELECT make,model FROM CARS WHERE make = ?"
      params  => ['honda']
  }

To convert from a DBI accessible database such as ORACLE or MySQL to one of the AnyData formats such as XML you must also include a flag with the table_name within the database:

  my $dbh = DBI->connect('dbi:AnyData:(RaiseError=>1)');
  $dbh->func(
      'DBI',
      DBI->connect('dbi:mysql:database=test:(RaiseError=>1)'),
      'XML',
      'cars.xml',
      {table_name=>'cars'},
  'ad_convert');

Or to print out the same data as an HTML table without storing it:

  my $dbh = DBI->connect('dbi:AnyData:(RaiseError=>1)');
  print $dbh->func(
      'DBI',
      DBI->connect('dbi:mysql:database=test:(RaiseError=>1)'),
      'HTMLtable',
      undef,
      {table_name=>'cars'},
  'ad_convert');

The ad_convert() method works on the entire database. If you need to convert only a selected portion of the databse, use ad_import() with a SELECT clause and then ad_export() it to the new format.

The ad_import method by default closes the connection for the imported database. If you need to continue using the handle for the other datbase, pass the flag {keep_connection=>1}:

  my $dbh       = DBI->connect('dbi:AnyData:(RaiseError=>1)');
  my $mysql_dbh = DBI->connect('dbi:mysql:database=test:(RaiseError=>1)'),
  $dbh->func(
      'cars',
      'DBI',
      $mysql_dbh,
      { keep_connection=>1 },
  'ad_import');
  #...
  $mysql_dbh->disconnect;

Passwd, Weblog, Mp3

Other Formats

Converting between formats

Using remote files

This will fetch the remote file, parse its XML, and provide you with an in-memory table which you can query with DBI/SQL or save to a local file:

  $dbh->func(
      'news',
      'XML',
      'http://www.somewhere.org/files/news.xml',
  'ad_import');

This will fetch the remote file, parse its XML, and print it out as an HTML table:

  print $dbh->func(
      'XML',
      'http://www.somewhere.org/files/news.xml',
      'HTMLtable',
  'ad_convert');

If the remote file requires authorization, you can include values for "user" and "pass" in the $flags parameter:

  $dbh->func(
      'news',
      'XML',
      'http://www.somewhere.org/news.xml',
      { user => 'fred', passwd => 'x9y77d' },
  'ad_import');

Working with in-memory tables

Prototyping

quickly create a database from a string, an array, or the DATA section of a script without needing any file access or rdbms.

Creating Views

pull selected columns and selected rows from an ORACLE or MySQL database en masse and work with them in memory rather than having to use the full database.

Combining Data from multiple formats

create a single in-memory table by importing selected columns and rows from e.g. an XML file, an Oracle database, and a CSV file.

Rollback/Commit

You can make multiple changes to the in-memory database and then, depending on the sucess or failure of those changes either commit by using export to save the changes to disk or skip export which effectively rolls back the database to its state before the import.

In-memory tables may be modified with DBI/SQL commands and can then be either printed to the screen or saved as a file in any of the AnyData formats. (see the ad_export method below)

In-memory tables may be created in several ways:

 1. Create and populate the table from one or more local or remote files
 2. Create and populate the table from a string
 3. Create and populate the table from an array
 4. Use DBI/SQL commands to create & populate the table

Creating in-memory tables from local or remote files

You can create an in-memory table from a string in a specified format, Note: the string should be enclosed in square brackets.

This reads a CSV file into an in-memory table. Further access and modification takes place in-memory without further file access unless you specifically use ad_export to save the table to a file.

  # CREATE A TABLE FROM A LOCAL FILE
  $dbh->func( 'test2', 'CSV', $filename, 'ad_import');

  # CREATE A TABLE FROM A REMOTE FILE
  $dbh->func( 'test2', 'CSV', $url, 'ad_import');
    

See the section on "Remote File Access" for further details of using remote Files.

Creating an in-memory table from Strings

You can create an in-memory table from a string in a specified format, Note: the string should be enclosed in square brackets.

This example creates an in-memory table from a CSV string:

  # CREATE A TABLE FROM A CSV STRING
  $dbh->func( 'test2', 'CSV',
       ["id,phrase\n1,foo\n2,bar"],
  'ad_import');
    

Creating an in-memory table from the DATA section of a script

Perl has the really cool feature that if you put text after the marker __END__, you can access that text as if it were from a file using the DATA array. This can be great for quick prototyping.

For example this is a complete script to build and access a small table and print out "Just Another Perl Hacker":

  use DBI;
  my $dbh=DBI->connect('dbi:AnyData(RaiseError=>1):');
  $dbh->func( 'test', 'XML',  [<DATA>],  'ad_import');
  print $dbh->selectcol_arrayref(qq{
      SELECT phrase FROM test WHERE id = 2
  })->[0];
  __END__
  <phrases>
      <phrase id="1">Hello World!</phrase>
      <phrase id="2">Just Another Perl Hacker!</phrase>
  </phrases>
    

The same idea can be used with DATA sections of any size in any of the supported formats.

Creating an in-memory table from Arrays

In-memory tables may also be created from arrays. Or, more technically, from references to arrays. The array should consist of rows which are themselves references to arrays of the row values. The first row should be column names.

For example:

 # CREATE A TABLE FROM AN ARRAY
 $dbh->func( 'test3', 'ARRAY',
             [
                ['id','phrase'],
                [1,'foo'],
                [2,'bar']
             ],
 'ad_import');
    

Creating an in-memory table from DBI/SQL commands

If you do not use ad_catalog or ad_import to associate a table name with a file, then the table will be an in-memory table, so you can just start right out by using it in DBI/SQL commands:

  # CREATE & POPULATE A TABLE FROM DBI/SQL COMMANDS
  use DBI;
  my $dbh = DBI->connect('dbi:AnyData(RaiseError=>1):');
  $dbh->do("CREATE TABLE test (id TEXT,phrase TEXT)");
  $dbh->do("INSERT INTO test VALUES (1,'foo')");
  $dbh->do("INSERT INTO test VALUES (2,'bar')");
  $dbh->do("UPDATE test SET phrase='baz' WHERE id = '2'");
  $dbh->do("DELETE FROM test WHERE id = '1'");
    

Using Multiple Databases, Simulating Joins

For example, this creates two in-memory tables from two different data formats

  $dbh->func( 'classes', 'CSV', 'classes.csv' 'ad_import');
  $dbh->func( 'profs',   'XML', 'profs.xml',  'ad_import');

You can also import columns from several different formats into a single table. For example this imports data from an XML file, a CSV file and a Pipe delimited file into a single in-memory database. Note that the $table parameter is the same in each call so the data from each import will be appended into that one table.

  $dbh->func( 'test', 'XML',  [$xmlStr],  'ad_import');
  $dbh->func( 'test', 'CSV',  [$csvStr],  'ad_import');
  $dbh->func( 'test', 'Pipe', [$pipeStr], 'ad_import');

When you import more than one table into a single table like this, the resulting table will be a cross join unless you supply a lookup_key flag. If a lookup_key is supplied, then a the resulting table will be a full outer join on that key column. This feature is experimental for the time being but should work as expected unless there are columns other than the key column with the same names in the various tables. You can specify that the joined table will only contain certain columns by creating a blank empty table before doing the imports. You can specify only certain rows with the sql flag. For example:

  $dbh->func('test','ARRAY',[],{col_names=>'foo,bar'baz'}, 'ad_import');
  $dbh->func('test','XML',$file1,{lookup_key=>'baz'},'ad_import');
  $dbh->func('test','CSV',$file1,{lookup_key=>'baz'},'ad_import');

DBD::AnyData does not currently support using multiple tables in a single SQL statement. However it does support using multiple tables and querying them separately with different SQL statements. This means you can simulate joins by creating two statement handles and using the values from the first handle as a lookup key for the second handle. Like this:

  $dbh->func( 'classes', 'CSV', 'classes.csv' 'ad_import');
  $dbh->func( 'profs',   'XML', 'profs.xml',  'ad_import');
  my $classes_sth = $dbh->prepare( "SELECT pid,title FROM classes" );
  my $profs_sth   = $dbh->prepare( "SELECT name FROM profs WHERE pid = ?" );
  $classes_sth->execute;
  while (my($pid,$class_title) = $classes_sth->fetchrow_array) {
      $profs_sth->execute($pid);
      my $row = $profs_sth->fetchrow_arrayref;
      my $prof_name = $row ? $row->[0] : '';
      print "$class_title : $prof_name\n";
  }

  # That will produce the same results as:
  SELECT classes.title,profs.name FROM classes,profs WHERE pid = pid

Overview of DBD::AnyData Methods

ad_catalog

specifies a file to be used for DBI/SQL continuous file access

ad_import

imports data into an in-memory table

ad_export

exports data from an in-memory table to a file

ad_clear

clears an in-memory table (deletes it from memory)

ad_convert

converts data from one format to another and either saves it in a new file or returns it as a string

These methods are called using DBI func(), for example:

  $dbh->func( $table, $format, 'ad_export');

  # Here are the parameters for the various methods:
  $dbh->func( $table, $format, $file, $flags, 'ad_catalog');
  $dbh->func( $table, $format, $data, $flags, 'ad_import');

  $dbh->func( $source_format, $source_data,
              $target_format, $target_file,
              $source_flags,  $target_flags,
  'ad_convert');

  $dbh->func( $table, $format, $file, $flags, 'ad_export');
  $dbh->func( $table, 'ad_clear' );
     
  # $table is a valid SQL table name
  # $format is one of the AnyData formats ('XML','CSV',etc.)
  # $file is a valid file name (relative or absolute) on the local computer
  # $flags is a hashref containing key/value pairs, e.g.
  { col_names => 'make,model,year', pattern => 'A10 A12 A4' }

  # $data is one of:
  # * a valid file name (relative or absolute) on the local computer
  # * a valid absolute FTP or HTTP URL
  # * an arrayref containing arrayrefs of rows with column names first
  #     [
  #       ['make','model'],
  #       ['Honda','Odyssy'],
  #       ['Ford','Suburban'],
  #     ]

  # * an arrayref containing a string in a specified format
  #     CSV  :  ["id,phrase\n1,foo\n2,bar"]
  #     Pipe :  ["id|phrase\n1|foo\n2|bar"]

  # * a reference to the DATA section of a file
  #      [<DATA>]

  # * a DBI Database handle
  #      DBI->connect('dbi:mysql:database=...)

The ad_catalog method is the standard way to treat files as databases. Each time you access data, it is read from the file and each time you modify data, it is written to the file. The entire file is never read en masse into memory unless you explicitly request it.

The ad_import method can import data from local or remote files, from any other DBI accessible database, from perl data structures such as arrays and strings. You may import an entire table or only the columns and rows you specify. If the data is imported from a file, all of the data you select is read into memory when you call ad_import so this should not be done with selections larger than will fit in your memory. :-). All accessing and modification is done in memory. If you want to save the results of any changes, you will need to call ad_export explicitly.

Not all formats and data sources will work with all methods. Here is a summary of what will work. "all sources" includes local files, remote files, any DBI accessible database, perl arrayrefs, perl strings.

 Import From   all formats, all sources
 Convert From  all formats, all sources
 Convert To    all formats except DBI, local files, arrays or strings only
 Export To     all formats except DBI, local files, arrays or strings only
 Catalog       all formats except DBI, XML, HTMLtable, Mp3, ARRAY,
               local files only

connect

ad_catalog

 PURPOSE:

    Creates an association betweeen a table name, a data format, and a file.

 SYNTAX:

     $dbh->func( $table, $format, $file, $flags, 'ad_catalog' )

 PARAMETERS:

     $table  = the name of the table to be used in SQL commands

     $format = an AnyData format ('XML','CSV', etc.)

     $file   = the name of a local file (either full path or relative)

     $flags  = a optional hashref of column names or other values

 EXAMPLE:

    This specifies that any DBI/SQL statements to the table
    'cars' will access and/or modify XML data in the file 
    '/users/me/data.xml'

       $dbh->func( 'cars', 'XML', '/usrs/me/data.xml', 'ad_catalog' )

 REMARKS:

    The format may be any AnyData format *except* DBI, XML, HTMLtable,
    and MP3.

ad_import

 PURPOSE:

     Imports data from any source and any format into an in-memory table.

 SYNTAX:

     $dbh->func( $table, $format, $data_source, $flags, 'ad_import' )

 PARAMETERS:

     $table       = the name of the table to be used in SQL commands

     $format      = an AnyData format ('XML','CSV', etc.)

     $data_source = $file_name
                 or $url
                 or [$string]
                 or [<DATA>]
                 or $reference_to_an array of arrays
                 or $DBI_database_handle

     (See section "Data Sources" for more specifics of $data_source)

 EXAMPLES:

     $dbh->func( 'cars', 'XML', '/usrs/me/data.xml', 'ad_import' )

     For further examples, see sections on "In-Memory Tables",
     "Remote Files", "DBI databases".

ad_export

 PURPOSE:

     Converts an in-memory table into a specified format and either saves
     it to a file or returns it as a string.

 SYNTAX:

     $dbh->func( $table, $format, $file, $flags, 'ad_export' )

     OR

     my $string = $dbh->func( $table, $format, $flags, 'ad_export' )

 PARAMETERS:

     $table  = the name of the in-memory table to export

     $format = an AnyData format ('XML','CSV', etc.)

     $file   = the name of a local file (either full path or relative)

 EXAMPLES:

     Save a table as an XML file:

        $dbh->func( 'cars', 'XML', '/usrs/me/data.xml', 'ad_export' )

     Print a table as an HTML table

         print $dbh->func( 'cars', 'HTMLtable', 'ad_export' )

ad_convert

 PURPOSE:

     Converts data from one format into another and either returns it
     as a string in the new format or saves it to a file in the new
     format.

 SYNTAX:

   my $str = $dbh->func(
       $source_format,
       $data_source
       $target_format,
       $source_flags,
       $target_flags,
   'ad_convert' );

   OR

   $dbh->func(
       $source_format,
       $data_source
       $target_format,
       $target_file,
       $source_flags,
       $target_flags,
   'ad_convert' );

 PARAMETERS:

     $source_format = AnyData format ('XML','CSV', etc.) of the source db

     $target_format = AnyData format ('XML','CSV', etc.) of the target db

     $target_file  = name of file to store converted data in

     $data_source = $file_name
                 or $url
                 or [$string]
                 or [<DATA>]
                 or $reference_to_an array of arrays
                 or $DBI_database_handle

     (See section "Data Sources" for more specifics of $data_source)

 EXAMPLES:

 # CONVERT A CSV FILE TO AN XML FILE
 #
 $dbh->func( 'CSV', 'data.csv', 'XML', 'data.xml', 'ad_convert');

 # CONVERT AN ARRAYREF TO AN HTML TABLE AND PRINT IT
 #
 print $dbh->func( 'ARRAY', $aryref, 'HTMLtable', 'ad_convert');

 # CONVERT AN ARRAYREF TO XML AND SAVE IT IN A FILE
 #
 $dbh->func( 'ARRAY', $aryref, 'XML', 'data.xml', 'ad_convert');

 # CONVERT A SELECTION FROM A MySQL DATABASE TO XML
 # AND SAVE IT IN A FILE
 #
 $dbh->func(
     'DBI',
     $mysql_dbh,
     'XML',
     'data.xml',
     {sql=>"SELECT make,model FROM CARS where year > 1996"}
 'ad_convert');

 REMARKS

 The format 'DBI' (any DBI accessible database) may be used as the
 source of a conversion, but not as the target of a conversion.

 The format 'ARRAY' may be used to indicate that the source of the
 conversion is a reference to an array.  Or that the result of the
 conversion should be returned as an array reference.  (See above, 
 working with in-memory database for information on the structure of
 the array reference).

Data Sources

 The ad_import and ad_convert methods can take data from many
 sources, including local files, remote files, strings, arrays,
 any DBI accessible database, the DATA section of a script.

 The $data_source parameter to ad_import and ad_convert will
 vary depending on the specific data source, see below.

 Local Files

     A string containing the name of a local file.  It may either
     be a full path, or a path or file relative to the currently
     defined f_dir (see ?);

     e.g. '/users/me/data.xml'

 Remote Files

     A string containing the url of the data.  Must start with
     'ftp://' or 'http://'

     e.g. 'http://www.somewhere.org/misc/news.xml'

 Arrays of Arrays

     A reference to an array of data.  Each row of the data is
     a reference to an array of values.  The first row is the
     column names. E.G.:

        [
          ['make','model'],
          ['Honda','Odyssy'],
          ['Ford','Suburban'],
        ]

  Strings

     A string in the specified format including all field and record
     separators.  The string should be the only row in an array reference
     (i.e. it should be enclosed in square brackets)

     e.g. a CSV string

         ["id,phrase\n1,foo\n2,bar"]

     or in Pipe Delimited string

         ["id|phrase\n1|foo\n2|bar"]

  The DATA section of a file

      A reference to the array obtained from the lines after 
      __END__ in a script.

         [<DATA>]

  DBI Databases

      A database handle for a specified rdbms.

      DBI->connect('dbi:mysql:database=...)

ad_clear

 PURPOSE:

     Clears an in-memory table (deletes it from memory)

 SYNTAX:

     $dbh->func( $table, 'ad_clear' )

 PARAMETERS:

     $table  = the name of the in-memory table to clear

 REMARKS:

 In-memory tables will be deleted from memory automatically when the
 database handle used to create them goes out of scope.  They will also
 be deleted if you call $dbh->disconnect() on the database handle
 used to create them.  The ad_clear method is a way to free up memory
 if you intend to keep using the database handle but no longer need a
 given table.  As with other (all?) Perl memory operations, this frees
 memory for the remainder of your perl script to use but does not decrease
 the total amount of system memory used by the script.

SQL Syntax

 Currently only a limited subset of SQL commands are supported.
 Only a single table may be used in each command.  This means
 That there are *no joins*, but see the section above on simulating
 joins.  In coming months additional SQL capabilities will be added,
 so keep your eyes out for ANNOUNCE message on usenet or the dbi-users
 mailing list (see below "Getting More Help").

 Here is a brief synopsis, please see the documentation for
 SQL::Statement for a more complete description of these commands.

       CREATE  TABLE $table
                     ( $col1 $type1, ..., $colN $typeN,
                     [ PRIMARY KEY ($col1, ... $colM) ] )

        DROP  TABLE  $table

        INSERT  INTO $table
                     [ ( $col1, ..., $colN ) ]
                     VALUES ( $val1, ... $valN )

        DELETE  FROM $table
                     [ WHERE $wclause ]

             UPDATE  $table
                     SET $col1 = $val1, ... $colN = $valN
                     [ WHERE $wclause ]

  SELECT  [DISTINCT] $col1, ... $colN
                     FROM $table
                     [ WHERE $wclause ] 
                     [ ORDER BY $ocol1 [ASC|DESC], ... $ocolM [ASC|DESC] ]

           $wclause  [NOT] $col $op $val|$col
                     [ AND|OR $wclause2 ... AND|OR $wclauseN ]

                $op  = |  <> |  < | > | <= | >= 
                     | IS NULL | IS NOT NULL | LIKE | CLIKE

 The "CLIKE" operator works exactly like "LIKE" but is case insensitive.