The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
Decl-0.11
River stage zero No dependents
++
/ Decl::Semantics::Table

NAME

Decl::Semantics::Table - implements a table in a database.

VERSION

Version 0.01

SYNOPSIS

When working with databases, it is always true that we make certain assumptions about its structure (what tables it has, what fields they have). The table tag is how we define what in a table we intend to use, and how we expect it to be formatted. When developing a system from scratch, the table tag can even create the table directly, and if we continue to give it authoritative status, it can also modify the existing tables to meet the specifications of the script. It does so by generating SQL CREATE and ALTER TABLE statements and running them against its database.

A set of tables can also be told to generate a full SQL schema without talking to the database directly. This is the default if no database handle is defined.

The semantics of the table tag, as you'll not be surprised to hear, correspond closely to SQL semantics. There are just a few differences, and as usual, you don't have to use them if you don't want to, but I find them useful. Note that at the moment, these semantics are a subset of full SQL - don't expect to do your DBA work through the table tag. The point of this exercise right now is to provide more of a quick sketch tool that generates simple SQL that can be refined as needed; my own SQL work is pretty superficial, so to do a better job, I'll eventually have to put a lot more work into refining the semantics of database management.

Basic types

Each column in a table has a type. I'm arbitrarily calling these types int, float, char, text, bool, and date (the last being a timestamp). You can, of course, use anything else you want as a type, and the table tag will assume it's SQL and pass it through, for better or worse; this allows you to use anything specifically defined for your own database.

A field specification in the table tag is backwards from SQL - the type comes first. I'm really not doing this to mess with your head; tags are better suited for expression of type, so this matches C::Decl semantics better. If you really hate it, use the sql subtag - this passes whatever it sees through as SQL without trying to get cute at all. It doesn't even try to parse it, actually, except to strip out the first word as the field name. So this will work fine:

   table mytable
      sql key integer not null
      sql field varchar(200) not null default 'George'

This is your best bet when you start to offload checks to the database instead of just hacking something together.

As always with Decl, the idea is to make it easy to slap something together while making it possible to be careful later. Nowhere is this attitude more evident than here in my glossing over the vast and troubled territory that is SQL. Did you know the 'S' is for 'standard'? Have you ever seen a more ironic acronym?

One more "basic" type: a key is always an integer that autoincrements. You can have a character key by saying e.g. char myfield (key); a character key is simply declared PRIMARY KEY.

Structural types

To represent relationships between tables, I'm using ref <tablename> (defines a field named after the table with the same type as the table's key), ref field <tablename> (in case you want to name it something else; maybe you have two such fields, for example), and "list". The list actually defines a subtable, and there are two variants: list <tablename>/list field <tablename> (defines an n-to-n relationship to the other table by means of an anonymous linking table), and a multilined variant:

   list <field>
      int field1
      int field2

This actually creates an new table called <table>_<field> and gives it those two fields, plus the key of the master table.

All of this makes it simpler and quicker to set up a normalized database and build queries against it that can be called from code.

Data dictionary

The data dictionary is a quick and easy way to define new "types" - a title may be standardized throughout your database as a char (100), for example. So:

  data-dictionary
     char title (100)

Now we can use the title as a field type anywhere:

   table
      title

or

   table
      title
      title subtitle

If a field is not named, the type name will also be used as the default field name. (This seems pretty reasonable.)

FUTURE POSSIBILITIES

Variant SQL data dictionaries

The tags used in the data dictionary are always standard SQL, whatever that might mean for your own database. If you need to work with multiple databases then you can define database-specific data dictionaries like this:

   data-dictionary (msaccess)
      char title (100)

and so on. If you leave one data dictionary unadorned with a database type, then it will serve as the default for any fields that don't have to be defined differently between the different databases. I wrestled with this setup, but I think it's the cleanest way to represent these things - plus it gives the added benefit that if you move from database A to database B, you can simply take your data dictionary and work down the list deciding which datatypes correspond to what in the new regime, then use the new data dictionary with no other format changes.

How often will this come up, though? No idea. I just worry, that's all.

At any rate, you can think of the special definitions for generic datatypes defined by this code as a default data dictionary. Any of those types may be overridden. Clear? Of course it is.

More refined DBA facilities

Defining indices would be nice, wouldn't it?

Building a spec based on existing SQL table definitions

This would be useful for introspection as well.

FUNCTIONS DEFINED

defines(), tags_defined()

build_payload, build_table, build_ddict

We connect to the default database - unless we are actually in a database object, or given a specific database by name.

We first write SQL to define the table, and query the database to see what structure it thinks that table has (if it has that table), and of course, we do the same for any subtables. If there's a mismatch, we generate SQL to alter the table(s).

If we have authority, we then execute any SQL already generated. There should probably be some kind of workflow step to allow this authority to be delegated or deferred, but man, that's a can of worms that can be opened another day.

The table tag thus doesn't actually have a payload per se.

Helper functions sql_single_table() and sql_single_field

These functions just spin out some SQL based on our data structures.

dictionary_lookup

This is called by a table on its dictionary to see if the dictionary knows about a given field. If the dictionary doesn't know, and if there is a higher-level data dictionary, then it gets called, and so on.

default_key, add_default_key, get_table_key, get_table_field, add_field

Table access functions.

AUTHOR

Michael Roberts, <michael at vivtek.com>

BUGS

Please report any bugs or feature requests to bug-decl at rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=Decl. I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.

LICENSE AND COPYRIGHT

Copyright 2010 Michael Roberts.

This program is free software; you can redistribute it and/or modify it under the terms of either: the GNU General Public License as published by the Free Software Foundation; or the Artistic License.

See http://dev.perl.org/licenses/ for more information.