Using an External SQL Query file?

Spidy has asked for the wisdom of the Perl Monks concerning the following question:

Greetings, fellow monks!

I've recently been trying to create something that has a ... rather large assortment of MySQL tables. Now, generating all of them within the install file is an option, but I've recently been look at something called Mediawiki, and while inspecting their (PHP) source, I found something called "tables.sql". It would appear to be a file that is run to generate all of their tables for them, along with keeping all their table generating information in a single file, which makes it easier to track.

However, Mediawiki is built in PHP. And I want to do this with Perl. However, a few quick google searches have not revealed anything, so I'm here to ask you, my fellow monks: how do I use an external file to perform all of my SQL table generating queries?

Thanks,
Spidy

My Website
Get Eated!

Comment on Using an External SQL Query file?

Replies are listed 'Best First'.
Re: Using an External SQL Query file? by samtregar (Abbot) on Jun 14, 2006 at 21:55 UTC
Normally you'll call out to your database's shell program to load the file for you. For example, Krang's DB creation code looks like this: `# load all sql files as the DBUser my @sql_dirs = pkg('File')->find_all('sql'); print STDERR "Loading SQL for '$name' from " . join(', ', @sql_dir +s) . "...\n" if $verbose; $cmd = "cat " . join(' ', map { catfile($_, '*.sql') } @sql_dirs) +. " \| mysql -u$user $name "; $cmd .= " -p$pass" if $pass; $cmd .= " -h$hostname" if defined $hostname; $cmd .= " -v" if $verbose > 1; print "Running '$cmd'\n" if $verbose > 1; system($cmd) && die "Couldn't load SQL: $?: $!";` [download] That loads all the .sql files contained in an SQL directory using the mysql shell. The call in the first line is part of Krang's addon system which allows plugins to define their own SQL. -sam	[reply] [d/l]
Re: Using an External SQL Query file? by cowboy (Friar) on Jun 14, 2006 at 21:45 UTC
Generally, that file is created using mysqldump, and can be fed into mysql to re-create the tables. The documentation for mysqldump should provide everything you need.	[reply]
Re: Using an External SQL Query file? by stonecolddevin (Parson) on Jun 14, 2006 at 22:42 UTC
I agree with samtregar. Cheat. Use the mysql binary to open and load the files so that you don't have to bother with SQL parsing and/or tokenizing. meh.	[reply]