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
