Re: Database setup

[Leo Lapworth]

I did something ages ago to convert an XML formatted file
into the relevant SQL (was for postgress and I got it working
for MySQL once as well). The nice thing was it also spat
out the HTML documentation for the table as well.

http://leo.cuckoo.org/projects/xml_db_schema/

It's very rough and I wouldn't use it today.

Ken Clark contacted me - he does SQL Translator

http://search.cpan.org/author/KCLARK/SQL-Translator-0.02/

It's not quite what your after but he said he'll get it doing
XML input sometime.

Hmm, so this might not be of use, but I thought I'd
mention it.

Leo

Re: Database setup

[Kate L Pugh]

On Wed 17 Sep 2003, Jonathan Peterson [EMAIL PROTECTED] wrote:
 Really? I just write the SQL in text files and run them through the 
 database's built in client.

I don't want the hassle of having to work out where to install these
text files.

Kake

Database setup

[Kate L Pugh]

So you're starting a new project, and you've designed a database
schema, and you want to write some code to set up the tables in the
database.  You know this won't be a one-off, since you'll want to
initialise a test database every time you run your tests, plus you
plan to deploy your application in several places.

So you write the following module:

--

package Foo::Setup::SQLite;
use strict;

use Carp;
use DBI;

my %create_sql = __PACKAGE__-_get_create_sql;

sub _get_create_sql {
my $class = shift;
return ( table1 = CREATE TABLE table1 ( uid integer, ... ),
 ...
   );
}

sub setup {
my ($class, %args) = @_;
my $dbh = DBI-connect(dbi:SQLite:dbname=$args{dbname}, , ,
   { PrintError = 1, RaiseError = 1,
 AutoCommit = 1 } )
  or croak DBI::errstr;

# Check whether tables exist, set them up if not.
my $sql = SELECT name FROM sqlite_master
   WHERE type='table' AND name in (
. join( ,, map { $dbh-quote($_) } keys %create_sql ) . );
my $sth = $dbh-prepare($sql) or croak $dbh-errstr;
$sth-execute;
my %tables;
while ( my $table = $sth-fetchrow_array ) {
$tables{$table} = 1;
  }

foreach my $required ( keys %create_sql ) {
  if ( $tables{$required} ) {
print Table $required already exists... skipping...\n;
  } else {
print Creating table $required... done\n;
$dbh-do($create_sql{$required}) or croak $dbh-errstr;
}
}

$dbh-disconnect;
}

--

(Except you write it better, without the $dbh-quote and with the
 facility to execute more than one SQL statement per table if you're
 using a database that wants the primary key defined separately and
 blah blah blah, my DBI skillz are not the question here.)

Now I hate doing things over and over again.  I just found myself
copying and pasting this code for the second time this month, and it
makes me feel dirty.  Surely there is a simple module on CPAN that I
can pass a big pile of CREATE TABLE statements to, and it'll check
whether the tables exist, set them up if not, be silent or not as I
require, force clearing of existing tables at my whim.  What is this
module called?

Kake

Re: Database setup

[Roger Burton West]

On Wed, Sep 17, 2003 at 02:46:16PM +0100, Jonathan Peterson wrote:
[Kake]
 So you write the following module:
Really? I just write the SQL in text files and run them through the 
database's built in client.

I write the table definitions in a different format and run them through
a converter to get drop/create statements for SQLite, MySQL and Postgres
(Oracle if I ever bother to install it and work out how to make it
DTRT).

That way the table definitions also form part of the documentation.

R

Re: Database setup

[alex]

At 14:10 17/09/03, Kate L Pugh wrote:
Now I hate doing things over and over again.  I just found myself
copying and pasting this code for the second time this month, and it
makes me feel dirty.  Surely there is a simple module on CPAN that I
can pass a big pile of CREATE TABLE statements to, and it'll check
whether the tables exist, set them up if not, be silent or not as I
require, force clearing of existing tables at my whim.  What is this
module called?
I'm not sure if it exists but in a different forum (Class::DBI mailing 
list) I was chatting about this very issue and bemoaning the lack of a perl 
equivalent to Apache Torque. It is essentially a means of creating 
databases and database access code from an XML description of the schema.

I know it isn't exactly what you want - it being java and not perl, but it 
might help. I can't remember if anyone directed me to a perl version.

Alex

Re: Database setup

[Richard Clamp]

On Wed, Sep 17, 2003 at 02:51:28PM +0100, alex wrote:
 I know it isn't exactly what you want - it being java and not perl, but it 
 might help. I can't remember if anyone directed me to a perl version.

Seems like it went through the typical sourceforge lifecycle.

 Mailing list post:
 http://www.mail-archive.com/[EMAIL PROTECTED]/msg00691.html

 Sourceforge project:
 http://sourceforge.net/projects/perl-torque/

 Conclusion:
 file:///dev/zero

-- 
Richard Clamp [EMAIL PROTECTED]

Re: Database setup

[Andy Wardley]

Kate L Pugh wrote:
 So you're starting a new project, and you've designed a database
 schema, and you want to write some code to set up the tables in the
 database.  

I use a template to generate the setup script for me.

A