abstraction on "create table" statement

Hi dbi-users list,

I'm coding a tool which should eventually be able to connect to as many
DBMS as Perl DBI let do. I have a generic graphical interface where I
set a list of columns and their types. I would like to give this list
of columns and to get back a "create table" SQL statement.

In the following example, I would like to find a CPAN module with the
get_create_table function.


my $columns = [
{
name => 'shop_code',
key => 1,
type => 'integer',
len => undef,
null => 0,
default => '',
comment => 'Code of the shop',
},
{
name => 'ean',
key => 1,
type => 'string',
len => undef,
null => 0,
default => '',
comment => 'European Article Number',
},
{
name => 'sales',
key => 0,
type => 'integer',
len => undef,
null => 1,
default => undef,
comment => 'how much was sold',
},
{
name => 'quantity',
key => 0,
type => 'integer',
len => undef,
null => 1,
default => undef,
comment => 'how many were sold',
},
];

my $query = get_create_table(
table => 'article',
dbtype => 'mysql', # Oracle, PostgreSQL, DB2...
columns => $columns,
);

# $query should contain something like this
#
# CREATE TABLE `article` (
# `shop_code` int(11) NOT NULL COMMENT 'Code of the shop',
# `ean` varchar(255) NOT NULL COMMENT 'European Article Number',
# `sales` int(11) default NULL COMMENT 'how much was sold',
# `quantity` int(11) default NULL COMMENT 'how many were sold',
# PRIMARY KEY (`shop_code`,`ean`)
# )


Do you have an idea which CPAN modules can do that? I've seen Alzabo
should be able to do it, any other suggestion?

--
Pierrick LE GALL
R&D engineer at Talend, Open data solution
http://talend.com

Re: abstraction on "create table" statement

On Wed, 2006-09-06 at 14:59 +0200, Pierrick LE GALL wrote:
> Hi dbi-users list,
>=20
> I'm coding a tool which should eventually be able to connect to as many
> DBMS as Perl DBI let do. I have a generic graphical interface where I
> set a list of columns and their types. I would like to give this list
> of columns and to get back a "create table" SQL statement.
>=20
> In the following example, I would like to find a CPAN module with the
> get_create_table function.

> Do you have an idea which CPAN modules can do that? I've seen Alzabo
> should be able to do it, any other suggestion?

SQLFairy could do this for you.
http://search.cpan.org/~kclark/SQL-Translator-0.07/lib/SQL/T ranslator.pm

mark
--
=20





MARK ADDISON
WEB DEVELOPER

200 GRAY'S INN ROAD
LONDON
WC1X 8XZ
UNITED KINGDOM
T +44 (0)20 7430 4678
F=20
E MARK.ADDISON@ITN.CO.UK
WWW.ITN.CO.UK
Please Note:

=20

Any views or opinions are solely those of the author and do not necessarily=
represent=20
those of Independent Television News Limited unless specifically stated.=20
This email and any files attached are confidential and intended solely for =
the use of the individual
or entity to which they are addressed.=20
If you have received this email in error, please notify postmaster@itn.co.u=
k=20

Please note that to ensure regulatory compliance and for the protection of =
our clients and business,
we may monitor and read messages sent to and from our systems.

Thank You.

Re: [SPAM] - Re: abstraction on "create table" statement - Foundword(s) list error in the

On Thu, 07 Sep 2006 16:12:00 +0100
"mark addison" wrote:

> SQLFairy could do this for you.

You're right, SQL::Translator is really a great tool. It's a bit slow
to parse my "create table" queries but it work fine.

The only drawback I see is that you can't just install SQL::Translator
translator engine and only parser/producer you need. Just as with DBI
and DBD modules installed separately.

Bye

--
Pierrick LE GALL
R&D engineer at Talend, Open data solution
http://talend.com