NAME

MKDoc::SQL - Database abstraction layer

IMPORTANT NOTE

This module is quite old since it was written back in 2000 - so don't expect state-of-the art Perl code. However it's been in use in production environments for a long time - hence it should be fairly stable - at least in places :).

SYNOPSIS

In your Perl code:

  # civilization.pl
  use MKDoc::SQL::Table;
  MKDoc::SQL::Table->load_state ("/path/to/def/dir");
    
  # let's populate the table...
  $cities_t->insert (
      City_Name => 'Bordeaux',
      Country   => 'France',
  );
  
  # ...more inserts here...
  
  # Fetch all Japanese cities
  my $cities_t  = MKDoc::SQL::Table->table ('Cities');
  my $query     = $cities_t->search (Country => 'Japan');
  my @jp_cities = $query->fetch_all();

  # Oh no! the Brits rise once again!
  $bordeaux = $cities_t->get (City_Name => 'Bordeaux'. Country => 'France');
  $bordeaux->{Country} = 'United Kingdom';
  $cities_t->modify ($bordeaux);
  
  # Frenchies go berserk! Launch the nukes!
  $cities_t->delete (Country => 'United Kingdom');
  
  # Global nuke war! Civilization is destroyed!
  $cities_t->erase();
  
  __END__

SUMMARY

MKDoc::SQL is a simple database abstraction layer. It features a database driver so that (in theory) multiples database can be supported, however I only ever got around to writing the MySQL driver.

OVERVIEW

MKDoc::SQL works with a schema which you define somewhere in your code. Once the schema is on disk, the following operations become possible:

Storing the schema on disk for later retrieval

Deploying the schema (creating the tables)

Do common operations on the database

Furthermore, MKDoc::SQL offers more than one table type:

MKDoc::SQL::Table - Simple table object

MKDoc::SQL::IndexedTable - Table with reverse index of wheighted keywords

MKDoc::SQL::Category - Hierarchical structure table

MKDoc::SQL also offers the ability to optionally define a Perl class associated with each table, so that records fetched from the database are automatically blessed into objects rather than simple hash references.

The goal of MKDoc::SQL is to let you use relational databases (which are a proven, robust technology) while getting rid of as much SQL as possible from your code - because well, SQL is ugly.

GETTING STARTED

Choosing a definition directory

If you're writing an application which uses a database, most likely you will have to have an install script since it is necessary to deploy the database - i.e. create all the tables and maybe populate the database a little.

Your schema will be written into a definition directory which will contain:

a driver.pl file which contains the database connection object

one or more .def files - one per table

For the sake of the example, we'll assume the following:

The schema will live in /opt/yourapp/schema

The database will be MySQL

The database name will be 'test'

The database user will be 'root'

The password will be undefined

The driver.pl file:

This is what the /opt/yourapp/schema/driver.pl file should look like. Ideally, you'd want your install script to generate this file.

  use MKDoc::SQL;
  
  MKDoc::SQL::DBH->spawn (
      database => 'test',
      user     => 'root',
  );
  MKDoc::SQL::Table->driver ('MySQL');

  __END__
  

Deploying the schema:

In order to write the .def files, we need to define the schema by instanciating a bunch of objects and then calling the save_state() method. This is done as follows:

  use MKDoc::SQL;
  
  # define the database schema
  new MKDoc::SQL::Table (
      bless_into => 'YourApp::Object::City', # optional
      name       => 'Cities',
      pk         => [ qw /ID/ ],                            # primary key
      ai         => 1,                                      # auto_increment the primary key
      unique     => { country_unique => [ qw /Country/ ] }  # unique constraint
      cols       => [
          { name => 'ID',        type => new MKDoc::SQL::Type::Int  ( not_null => 1 )              },
          { name => 'City_Name', type => new MKDoc::SQL::Type::Char ( size => 255, not_null => 1 ) },
          { name => 'Country',   type => new MKDoc::SQL::Type::Char ( size => 255, not_null => 1 ) }
      ] );

  # write the schema onto disk
  MKDoc::SQL::Table->save_state ('/opt/yourapp/schema');

  __END__
  

Note that you are not limited to defining MKDoc::SQL::Table objects. If you need to implement weighted keyword searches, you can define MKDoc::SQL::IndexedTable objects in your schema. If you need hierarchical structures, use MKDoc::SQL::Category objects instead.

Also, there are many column types which you can define. See MKDoc::SQL::Type::ALL for a reference.

Deploying the database

Now that your schema directory is correctly set up, it's time to actually create the database.

  use MKDoc::SQL;
  MKDoc::SQL::Table->load_driver ('/opt/yourapp/schema');
  MKDoc::SQL::Table->create_all();
  MKDoc::SQL::DBH->disconnect();
  
  __END__
  

Once you've done that, and if everything went well, you can start performing operations on the database using MKDoc::SQL.

Using the database

Look at MKDoc::SQL::Table, MKDoc::SQL::IndexedTable and MKDoc::SQL::Category.

EXPORTS

None.

AUTHOR

Copyright 2000 - MKDoc Holdings Ltd.

Author: Jean-Michel Hiver <jhiver@mkdoc.com>

This module free software and is distributed under the same license as Perl itself. Use it at your own risk.

cpanm MKDoc::SQL

perl -MCPAN -e shell
install MKDoc::SQL