Re: New DBD::File feature - can we do more with it?

On Wed, 30 Jun 2010 08:37:33 +0200, "H.Merijn Brand"
<[email protected]> wrote:

> On Tue, 29 Jun 2010 19:12:19 +0000, Jens Rehsack
> <[email protected]> wrote:
> 
> > Hi,
> > 
> > because of a bogus implementation for PRECISION, NULLABLE etc.
> > attributes in DBD::CSV (forced by limitations of SQL::Statement)
> > we have a new attribute for tables in DBD::File meta data:
> > 'table_defs'. This is filled when a 'CREATE TABLE ...' is executed
> > and copies the $stmt->{table_defs} structure (containing column
> > name and some more information - what ever could be specified
> > using (ANSI) SQL to create tables).
> > 
> > Could it makes sense to have a DBD::File supported way to store
> > and load this meta-data (serialized, of course)?
> 
> I'm all in favor of saving data-dictionary info in some persistent
> way, but as Tim said, not by default.
> 
> It should be a user option, and the interface should be configurable
> DBD::File should support what is installed, but only *if* it is
> installed and available. I personally would prefer JSON, with YAML
> on second position both very well fit the bill for DBD dict storage
> YAML is available for CPAN
> 
>   my $dbh = DBI->connect ("dbi:CSV:", undef, undef, {
>       f_dir      => "data",
>       f_ext      => ".csv/r",
>       f_encoding => "utf8",
>       f_schema   => undef,
>       f_dict     => $dict,
>       }) or die DBI->errstr;
> 
> where $dict is
> 1. A hash           see below
> 2. A hashref        ref to 1.
> 3. A filename       filename
> 4. A listref        [ filename, storage type ]
> 
> The hash/ref from the DDD can be read from or written to file in case
> of 3 or 4. this way we are backward compatible and support more than
> ever before. The content of the hash should be very well documented and
> all errors in it should optionally be ignored.
> 
> "storage type" can be any means of persistence: "JSON", "YAML",
> "Storable", "Freeze", where "Storable" is the default as it is
> available in CORE since ages.
> 
> The hash could be something like ...
> 
> my $hash = {
>     foo  => [
>         [ "bar",    [ "numeric", 4 ], [ "not null", "primary key" ] ],
>         [ "baz",    [ "integer"    ],                               ],
>         [ "drz",    [ "char",   60 ],                               ],
>         [ "fbg",    [ "numeric", 2, 2 ], [ ], [ "default", 0.0 ],   ],
>         [ "c_base", [ "numeric", 4 ], [ "not null" ]                ],
>         ],
>     base => [
>         [ "c_base", [ "numeric", 4 ], [ "not null", "primary key" ] ],
>         [ ...
>     :
>     "\cA:links" => [
>         [ "foo.bar" => "base.c_base" ],
>         :

A bit more thought leads to better portability ...

my $hash = {

    meta => {
        f_dir       => "data",
        f_schema    => undef,
        f_ext       => ".csv/r",
        f_encoding  => "utf8",
        f_lock      => 1,
        },
        
    tables => {

        foo => {
            meta => {
                file  => "foo.csv",
                key   => [ "c_foo" ],
                },
            fields => [
                [ c_foo => {
                      NAME       => "c_foo",
                      NAME_lc    => "c_foo",
                      NAME_uc    => "C_FOO",
                      NULLABLE   => 0,
                      PRECISION  => 2,
                      SCALE      => undef,
                      TYPE       => 5,
                      TYPE_NAME  => "NUMERIC"
                      }],

                [ foo => {
                      NAME       => "foo",
                      NAME_lc    => "foo",
                      NAME_uc    => "FOO",
                      NULLABLE   => 1,
                      PRECISION  => 40,
                      SCALE      => undef,
                      TYPE       => 12,
                      TYPE_NAME  => "VARCHAR"
                      }],
                :
                :
                ],
            },
        :
        :
        },

    links => {
        "bar.c_foo"     => "foo.c_foo",
        :
        },

    indices => {
        foo => [
            [ "c_foo", "foo DESC" ],
            :
            ],
        :

-- 
H.Merijn Brand  http://tux.nl      Perl Monger  http://amsterdam.pm.org/
using 5.00307 through 5.12 and porting perl5.13.x on HP-UX 10.20, 11.00,
11.11, 11.23, and 11.31, OpenSuSE 10.3, 11.0, and 11.1, AIX 5.2 and 5.3.
http://mirrors.develooper.com/hpux/           http://www.test-smoke.org/
http://qa.perl.org      http://www.goldmark.org/jeff/stupid-disclaimers/