[drop]
    DROP TABLE cbwb;
    DROP TABLE cbwb_topic;

[insert]
    INSERT INTO cbwb (id,puser,ptopic) VALUES (?,?,?);
[download]

Note that a hash elements are separated by double-newline, the hash keys are on the first line of the element in square brackets, and the values can contain multiple SQL statements separated by a semicolon+newline combination. For hash values that contain multiple statements, I run them like this: $dbh->do($_) for split /;\n/, $sql{drop};

Another thought is to use dbish (DBI::Shell) which allows you to call SQL statements from named files. That also lets you keep a straight SQL file that has *no* perl in it.

updateOh, I forgot: these days I don't use the square brackets, I put the hash keys in as SQL comments so that the config file is actually all SQL.

Using a hash is a good idea, regardless of how you store it, because it allows you to dynamically switch between statements, for things like different sort orders, or different inserts. ( My app inserts a row in either a queue table or a log table depending on outside data. By constructing the inserts to use the same parameter list, I can just use the appropriate hash key. )

Another way to store the statements would be with Data::Dumper.

--
Spring: Forces, Coiled Again!

A few years ago, when I didn't know any better, I actually did something similar to what you're suggesting.

But I did it with the dreaded 'require'

I haven't done it since, as my skills have developed a tad since then, but it might just be a bad idea that works in your case.

What I did was this:

1.) Created a file called: queries.pl

In this file, I put things like:

$Q_FINDMAX = 'select max(note_id) from pat_notes';
$Q_FINDMIN = 'select min(note_id) from pat_notes';
.
.
etc...
[download]

require ('/prod/parse/queries.pl');
[download]

The advantage of doing it this way is that it allowed the SQL folks to update/tune SQL without having to go into the actual Perl script to find them.

It's probably not the *best* way to do this sort of thing, but maybe it's good enough for what you're after? The syntax might be a little off (like I said, I haven't done this in a while, and I don't have any examples in front of me to refer to

Trek

I like this approach. And I also like the comment by Kevin Meltzer and/or Brent Michalski in "Writing CGI Applicatins in Perl": "I know many people who cringe at the require() function. They would rather have everything be a module. My philosphy is that require() is quick and simple, and if Larry is going to leave it in the language, I am going to use it."

I used require for exactly the same purpose as suggested above - so that someone who doesn't know perl can modify the meta-data. Worked a treat

And/or SQL::Abstract and its close cousin Class::DBI::AbstractSearch.

-- Randal L. Schwartz, Perl hacker
Be sure to read my standard disclaimer if this is a reply.

Class::DBI allows me to abstract from the POV of not needing to know much about SQL myself. However, one of the cited goals is to allow someone who knows SQL but not Perl to modify the SQL queries.

I don't see how Class::DBI (or SQL::Abstract) can accomplish that. What am I missing?

require General::Disclaimer;

All code, unless otherwise noted, is untested

"All it will give you though, are headaches after headaches as it misinterprets your instructions in the most innovative yet useless ways." - Maypole and I - Tales from the Frontier of a Relationship (by Corion)

Then, your Private:: idea is about the best you're going to get. SQL::Catalog might be of some service.

Frankly, it might even be best to store your queries in the database itself and pre-load them when the app starts up. *shrugs*

Being right, does not endow the right to be rude; politeness costs nothing.
Being unknowing, is not the same as being stupid.
Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

I shouldn't have to say this, but any code, unless otherwise stated, is untested

Use Class::DBI. It will save you enough time on the mundane create/update/search/retrieve logic that you can put more time into this customizable requirement.

While it will set up defaults for standard create/update/delete/search and *-to-* relationships, you have the ability to change the SQL wherever you see fit.

Class::DBI provides many ways to create your own constructors and custom SQL statements. Read Class::DBI -- Defining SQL Statements to learn more about this functionality.

a) Display x lines of table x starting at id x (or all the lines if nothing but table name given)
b) Make backup of table x to filename x
c) Restore table x from backup file x (or default backup, see below)

Then just include something which takes mySQL commands from a textarea and feeds them to the database, displaying any errors which occur as a result. Have the mySQL table automatically back itself up to another file before every set of commands, so the user can restore the database if he ruins it by mistake.

This should give someone who knows mySQL all the functionality he needs to maintain the database without complicated programming. As to actually using the database, that''s another story - you'll need something that people can include in their pages which lets them specify which fields they want displayed how:

ID=4 TABLE=PENPALS
My name is <<NAME>>
My email is <a href="mailto:<<EMAIL>>"><<EMAIL>></a>
[download]

ALL TABLE=PENPALS
<tr><td><<NAME>></td><td><<EMAIL>></td></tr>
[download]

This would be great, if the SQL were entered each execute, or if I was interacting with a web page in any way. However, it is not and I am not.

radiantmatrix
require General::Disclaimer;