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