radiantmatrix has asked for the wisdom of the Perl Monks concerning the following question:
I'd first like to extend a hearty thank-you to the Monks' help with my last project. Thank you for helping to make my life easier!
I've moved to a new database project using DBI and DBD::ODBC. I'm in the design phase now, and I've run up against an odd requirement. I'm well aware of the value of abstracting SQL from the Perl that calls it -- but I now have the requirement not to use Stored Procedures. I'm not allowed to change this requirement.
So, what I can't figure out is: what's the best way, barring stored procedure use, to abstract the SQL as much as possible away from my perl code?
Update 2004-09.Sep-30:
I have solved the problem using ideas given by repliers combined with some of my own. I thought the Monastery might be interested.
The methodology is simple:
- Put SQL into separate files, named with query_name.sql.
- Run this:
use strict;
use warnings;
my %queries;
for (@ARGV) {
open my $FH, '<', $_ or die("failed opening $_ :$!");
s'\.sql$'';
while (chomp($queries{$_}.=<$FH>)) {}
}
# the PERLINST env var holds the location of our private
# Perl Module store
open my $MODULE, '>', "$ENV{PERLINST}/MyProject/SQL.pm"
or die("Unable to open SQL module");
print $MODULE q(
package Private::MyProject::SQL;
require Exporter;
use vars (@ISA, @EXPORT);
);
print $MODULE '@EXPORT = qw(',join(' ',keys %queries),");\n";
for (keys %queries) {
print $MODULE 'use constant '.$_.' => q('.$queries{$_}.");\n";
}
print $MODULE '1;\n';
close $MODULE;
- Pack code using PAR
The end result is that the SQL files get drafted into a module, which can be included in the solid .exe that PAR generates. The SQL maintainer updates the .sql files and runs a batch file which calls the above script, then packs with PAR. No perl is ever apparent to the SQL maintainer.
Things to consider:
- This is Win32
- The final product and all resources must compile into a single .exe using PAR or similar. (Does this rule out the use of .sql files?)
- the SQL must be sufficiently abstracted that someone with no knowledge of perl can update it.
I'm currently thinking of putting it in our in-house Private:: module namespace with something like Private::ProjectName::SQL containing something like:
package Private::ProjectName::SQL;
require Exporter;
use warnings;
use strict;
use vars qw($VERSION @ISA @EXPORT);
$VERSION = 0.01;
@ISA = qw(Exporter);
@EXPORT = qw(sql_queryname1 sql_queryname2 ...);
use constant sql_queryname1 = qq(
SELECT RecordID FROM Management
WHERE IQ > 40
AND Salary < (SELECT UngodlyAmount FROM References)
); #don't trim this line!
use constant sql_queryname2 =....
1;
Will something like the above work? Is there a better way? Is that enough abstraction?
Thank you, wise monks, for bestowing wisdom upon me.
Re: Abstracting SQL without Stored Procedures
by jZed (Prior) on Sep 30, 2004 at 00:32 UTC
|
Personally I keep my SQL in a config-like file that lets me load the statements into a hash and keeps the SQL *completely* separate from the perl. My config files look something like this:
[drop]
DROP TABLE cbwb;
DROP TABLE cbwb_topic;
[insert]
INSERT INTO cbwb (id,puser,ptopic) VALUES (?,?,?);
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. | [reply] [d/l] |
|
| [reply] |
Re: Abstracting SQL without Stored Procedures
by TrekNoid (Pilgrim) on Sep 29, 2004 at 21:43 UTC
|
I'm already tensing myself for the downvotes :)
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...
and then, in my script: parse_notes.pl, I used:
require ('/prod/parse/queries.pl');
After that, it's just a matter of using the $Q_ variable names to refer to the queries.
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
| [reply] [d/l] [select] |
|
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
| [reply] |
Re: Abstracting SQL without Stored Procedures
by dragonchild (Archbishop) on Sep 29, 2004 at 18:53 UTC
|
Class::DBI
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
| [reply] |
|
| [reply] |
|
| [reply] |
|
| [reply] |
|
|
| [reply] |
Re: Abstracting SQL without Stored Procedures
by TedPride (Priest) on Sep 29, 2004 at 23:54 UTC
|
Set up routines to:
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>
ALL TABLE=PENPALS
<tr><td><<NAME>></td><td><<EMAIL>></td></tr>
It would extract the field names, feed them to the database in a query, take the results, and display them. The first line could specify which or how many records of which table, and the rest of the lines would be the format to display in. Probably wouldn't be too hard to abstract. | [reply] [d/l] [select] |
|
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;
| [reply] |
Re: Abstracting SQL without Stored Procedures
by dextius (Monk) on Sep 30, 2004 at 01:43 UTC
|
If I ever get some free time to CPAN, Sql::Simple is pretty nifty, as it has a fairly nice OO approach now. I've been using it quite a bit in mod_perl now, which forces you to think a little differently in terms of performance and memory management. Fun stuff.. Good luck :-) | [reply] |
|
|