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

Jens Rehsack wrote:
> 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 would really like to do this - this would bring us a big step
> in the right direction.
> 
> DBD::DBM could store it in it's meta-data (instead of saving column
> names it could safe the entire table_defs structure), but what should
> DBD::CSV do?

You have several possible options and the short answer is "let the user tell you".

For backwards or sideways compatibility, don't store any extra metadata by 
default; the only metadata is what can be gleaned from the normal CSV file itself.

Then, if the user tells you to via extra DBI config args, then retrieve or store 
extra metadata in accordance with those args.  For example, the extra args may 
give you a second file name in which the meta data is / is to be stored.  Or the 
extra args may indicate that one of the first rows in the CSV file contains 
metadata rather than normal data.

For example, if all of your metadata is column-specific, the CSV file could 
contain 2 heading rows instead of the usual one (or none)?  The first heading 
row would be the name of the column as usual.  The new second heading row would 
be the encoded type/constraint/etc definition that says how to interpret the 
data of the column, and then the third-plus columns are the data.

But the point is, let the user tell you how to interpret the particular CSV 
files they throw at you, and so DBD::CSV is then more flexible and compatible.

I will be taking a similar approach in the near future when implementing my 
Muldis D language over legacy systems such as SQL databases, whose metadata 
generally aren't as rich as mine.  Over a SQL database, I would provide at least 
3 options to users that they specify when connecting to a SQL database using 
Muldis D:  1. There is no extra metadata and just reverse-engineer the SQL 
metadata; this is also the degrade gracefully approach.  2. The extra metadata 
is stored in tables of another SQL schema of the same database and the user 
names that schema when connecting; this way, users can name their own stuff 
anything they want and they pick where my metadata goes, out of their way.  3. 
The extra metadata is stored outside the database in a local file; this file is 
named by the user when connecting.  I think that #2 is the best option for that.

-- Darren Duncan