Maybe it is just me, but I look at perl code with SQL queries in it and cringe. I don't know what it is about them, maybe just the raw "not perl" look they have.
In some recent projects, I've attemped to modulize all my mySQL calls. I would avoid direct queries at all costs. I'd make up weird subs and stuck them in a "SQL" package so they would not muck up my real perl code.
So sitting back, looking at all of this from the distance, I've come to the conclusion that there are two possible conclusions:
DBI is tremendously powerful and cannot be leashed.
or
DBI could be effectively "leashed", or at least the more simplistic usage.
Now maybe I'm crazy (and if so, please tell me and I'll just keep telling myself that SQL queries in perl code is not a bad thing.) I am no DBI Power User, as you can probably tell, so this is a shot in the dark for me: I really do not know what kind of response to expect.
It call comes down the question of Is it feasable to write a reusable module that controls DBI? Is there something similar that has been already written that I have missed?
Just off the top of my head, an example interface (quasi-code):
$db = connect(HOST => $host, PASSWORD => $pass, ..);
$result = $db->select(TABLE => $table_name, FIELDS => ["one","two","th
+ree"],ORDERBY => "two",DESCENDING => 1);
print $result->[0]->{"one"};
Again, thats quasi-code, just an example of what I'm thinking of for an "interface." Personally, I would love that sort interface, and I would assume others would too .. ? Would a module like this be worth throwing together for personal use, or am I moron for not already knowing about something similar to this? Or should I suck it up and leave my raw queries in my code? :-D
I'd appreciate any feedback. Thanks.
-billyak |
Re: Leashing DBI
by Abigail (Deacon) on Jun 27, 2001 at 03:56 UTC
|
I dunno. Grabbing some queries I've written, I find:
select db_name (u.dbid),
sum (2 * u.size),
sum (2 * curunreservedpgs (u.dbid, u.lstart, unreservedpgs
+))
from sysusages u, sysdevices d, sysdatabases db
where d.low <= u.vstart
and d.high >= u.vstart + u.size - 1
and d.status & 2 = 2
and u.segmap & 4 = 4
and not (u.segmap & 2 = 2 or u.segmap & 1 = 1)
and u.dbid = db.dbid
and db.name not like "dbcc_%"
group by u.dbid
I wonder whether that's really going to look better using
some kind of interface to an SQL query builder. I'm afraid
it would get worse.
You know, when I started working with SQL, I made myself
such a function, I'd pass it a table name, fields, possible
other information, and it build an SQL query for me. But soon
I found that for anything that isn't a trivial select,
writing the SQL yourself was far easier and gave cleaner
code.
-- Abigail | [reply]
[d/l] |
Re: Leashing DBI
by Masem (Monsignor) on Jun 27, 2001 at 02:08 UTC
|
It might be useful but you'll run into problems as soon as you try to use more complex SQL statements that your reining logic can't easily handle. I've found to harness the DBI mess, I'll move all my DBI statements to constants at the start of the program (with placeholds in place), such that my calls will look like:
use constant SELECT_USER_QUERY => q(
SELECT userid, username, password
FROM user_table,
WHERE userid=?
);
#...much later...
my $sth = $dbh->prepare( SELECT_USER_QUERY ) or die DBI::errstr;
$sth->execute( $userid ) or die DBI::errstr;
my @data = $sth->fetchrow_array( $sth );
Dr. Michael K. Neylon - mneylon-pm@masemware.com
||
"You've left the lens cap of your mind on again, Pinky" - The Brain
| [reply]
[d/l] |
|
|
Ah. Nice approach.
I would like to mention the possibility to use functions
instead of constants, and move all these functions to
a separate module.
This all in the spirit of McConnell's Code Complete.
Paraphrased: If you don't like/ grog/ find strange/ consider
to be difficult a certain library or structure or whatever,
interface it in a separate module. That way, you don't clutter
up your main code with things that are unclear and hard to
maintain. As a spin-off, you can reuse that module.
In this case, you could write functions with very descriptive
names just as wrappers for the SQL:
package SQL_functions;
sub select_picture_from_userID{
my $userID = shift;
"SELECT pict FROM users WHERE ID = $userID;"
}
sub select_some_nifty_case_with_contstraints{
... #hehe, perl6 only
}
#and in the "main" code:
use SQL_functions qw/:all/;
my $sth = $dbh->prepare( select_picture_from_userID( 666 ) ) or die...
Hope this helps,
Jeroen | [reply]
[d/l] |
|
|
package SQL_Helpers;
my %sql_statements = (
SELECT_USER_INFO => q/SELECT userid, username, password FROM user_
+table WHERE userid=?/,
UPDATE_USER_INFO => q/UPDATE user_table set password=? WHERE useri
+d=?/,
...yada yada yada...
);
sub sql {
my ( $dbh, $sql, @data ) = @_;
return undef if !exists( $sql_statements{ $sql } );
my $sth = $dbh->prepare( $sql_statements{ $sql } )
or die DBI::errstr;
$sth->execute( @data ) or die DBI::errstr;
return $sth;
}
...
package MyPackage;
use SQL_Helpers qw/:all/;
my $sth = sql( $dbh, UPDATE_USER_INFO, $password, $userid );
I would even go a step farther, possibly being able to class-ify this so that one can make methods that are created at runtime that correspond to the various SQL statements:
my $sth = $sql_helper->UPDATE_USER_INFO( $password, $userid );
As well as incorporating prepare_cached details into this class such that things can be speeded up in the SQL side. Notice that there are no dies in the main block of code, and to me this is even more readable that typical cases.
Now, these ideas seem rather simple that I would figure there's already a CPAN module for this, but I thought the same of both my Game::Life and Tie::Hash::Stack too...Maybe I'll take a look into developing such a class if nothing currently exists.
The only major problem with this is that sometimes the SQL statement and the code that calls it are closely nit. For example, one might try to do a SELECT listing all specific fields (more than 5, say), and using fetchrow_array with a long my() statement to collect them. If one then wanted to add another field, both the SQL statement and the perl code that called it would have to be changed at the same time; too much of a separation between the SQL statement and perl code could be confusing. But another option is to have pseudo variables in the SQL statement as well, and pass parameters by a hash; the class would be able to determine what order of parameters to pass based on placement of the placeholders in teh SQL statements.
Example SQL: UPDATE user_table SET password=?password WHERE userid=?us
+erid
Example call to sql:
my $sth = $sql_helper->UPDATE_USER_DATA(
{ userid=>$userid,
password=>$password }
);
The only problem going this far is you are now losing some speed aspects for 'beauty' of code, which is always a plausable tradeoff. I might simply try this to see how bad the time difference is, though I suspect most of it is buried in the SQL server details.
Dr. Michael K. Neylon - mneylon-pm@masemware.com
||
"You've left the lens cap of your mind on again, Pinky" - The Brain
| [reply]
[d/l]
[select] |
|
|
|
|
Re: Leashing DBI
by spudzeppelin (Pilgrim) on Jun 27, 2001 at 00:20 UTC
|
Check out some of the new DBIx:: modules (SearchBuilder, Easy, Abstract, CGITables, etc.) on CPAN. I was going through them a couple months ago, many of them appeared to be headed in exactly the same direction you're describing.
Spud Zeppelin * spud@spudzeppelin.com
| [reply] |
Re: Leashing DBI
by Mungbeans (Pilgrim) on Jun 27, 2001 at 13:29 UTC
|
I consider this a judgement call.
Problems with SQL in code:
- Your code has a dependancy on SQL, if you change to a different flavour of SQL you'll have to troll through all your code to fix any non portable SQL.
- If your database table structure changes (e.g. new not null columns in a table into which you are inserting data) your code may fall over at run time - having all the SQL in one place makes it easier to test.
Problems with abstracting SQL:
My preference is that, for a large application, there should be a single module dealing with each data entity in your database (e.g. invoice.pm for invoices) and this module should provide interfaces to SQL to access data from that table. The perl and SQL are combined in the same module.
No outside module should access the data structures other than through this module. This is database centric however and can be tricky to map onto OO.
"The future will be better tomorrow." | [reply]
[d/l] |
Re: Leashing DBI
by thpfft (Chaplain) on Jun 27, 2001 at 03:52 UTC
|
i think what you describe is exactly the motive behind the DBIx:: set of modules: more perl, less sql, thank you very much. It has adherents and detractors, as will already be apparent. Sometimes they meet peacefully. Sometimes it gets a little more heated.
that crunchy texture you may have noticed underfoot and the napalm-in-the-morning smell are residues of last time this topic was aired.
| [reply] |
Re: Leashing DBI
by entropy (Sexton) on Jun 27, 2001 at 05:31 UTC
|
My problem with DBI is the opposite of Masem's problem...
Masem hides his SQL statements by defining them elsewhere, whereas my problem is with the surrounding DBI code. Every time I want to look something up I have to do something like:
$sth=$dbh->prepare("select whatever from thetable where col=?");
$sth->execute($val);
my @list;
while(my $hashref=$sth->fetchrow_hashref) {
push @list,$hashref;
}
$sth->finish;
return \@list;
DBI provides a few methods such as selectall_arrayref or whatever... but these methods are not cached, and they only return a few data types (there is no selectall_hashref). What I would like to do is this:
my $hashref=$dbh->getall_hashref("select * from abc where col=?",$val)
+;
my $arrayref=$dbh->getrow_arrayref("select * from abc where col=?",$va
+l);
etc...
All the statements would be compiled with prepare_cached.
Does anyone know off hand if any of those DBIx modules support this sort of thing? | [reply]
[d/l]
[select] |
|
|
What do you mean selectall_arrayref is not
cached?!?
my $res = $DBH->selectall_arrayref("
SELECT employees.name, skills.name
FROM employees,skills,employee_skills
WHERE employees.id = ?
AND skills.id = employee_skill.skill_id
",undef,$emp_id);
Correct me if i am wrong, but that statement is cached.
At any rate, please read (dkubb) Re: (2) Outputing data from mySQL query into format for use with HTML::Template if you are tired of
building that hash data structure. :)
Jeff
R-R-R--R-R-R--R-R-R--R-R-R--R-R-R--
L-L--L-L--L-L--L-L--L-L--L-L--L-L--
| [reply]
[d/l]
[select] |
|
|
This may or may not be useful, depending on what you
want to do with the data once you get it. But you
may want to look at the module Data::Table:
my $table = Data::Table::fromSQL($dbh,
"select whatever from table where col
+ = ?",
$val);
And the query result is stored in a Data::Table object.
From there it can be extracted or manipulated at will.
In a CGI program you can print it out as an HTML table
easily: print $table->html;
buckaduck | [reply]
[d/l]
[select] |
Re: Leashing DBI
by jorg (Friar) on Jun 27, 2001 at 02:33 UTC
|
I kindof see where you're coming from, as SQL is a different language it 'pollutes' your cleanly written lines of perl. And whether you're putting them at the beginning of your program as constants (like Masem points out) or you wrap some interface around it there is always some sort of SQL dependency left in the code. My preferred way of doing things is to stick the SQL in the database as stored procedures (if the database supports it) or in .sql files which we can invoke through a commandline system call.
Jorg
"Do or do not, there is no try" -- Yoda | [reply] |
Re: Leashing DBI
by mattr (Curate) on Jun 27, 2001 at 16:48 UTC
|
my $oldpoints = &getfield("users",$id,"arigatopoints");
$ret = &updatefields($table,$id,\@fields,\@vals);
but it depends on what I'm trying to do. One report I generate
is built around parsing a single complex query. But in
a web site I tend to do lots of little things like the
code above and the bulk of the code is site logic.
I put the getfield and updatefields routines in my library
module out of the way so I can get at all the SQL at once
if I need to, and I can include that module in other
programs too.
If you are doing lots of little things it
might pay to abstract it out of the center of things and
try to see how few subs you can have that are written with
SQL. But fact is, I recently sped up a script from 3 minutes to
<10 seconds just by doing a complex query instead of
little ones interspersed by Perl. SQL engines are fast. | [reply]
[d/l] |
Re: Leashing DBI
by mugwumpjism (Hermit) on Jun 27, 2001 at 18:09 UTC
|
Let's step back a bit and look at what you're really trying to do.
Databases are about storing "things" in persistent storage. Those "things" are "rows", which loosely represent something.
But in OO programming, all "things" are Objects, so what we would like to do would be to store objects, not rows, in databases. There are a couple of approaches: use an OODBMS, or have some way of converting your objects into an RDBMS format.
For the latter approach, I strongly recommend the Tangram library in CPAN.
Here is a simple "movie database" in Tangram...
#!/usr/bin/perl
use LazyObject;
use warnings;
# "LazyObject" just gives you simple "new", "get", "set" members
package Movie; @ISA = (LazyObject);
package Person; @ISA = (LazyObject);
package Job; @ISA = (LazyObject);
package Credit; @ISA = (LazyObject);
package Location; @ISA = (LazyObject);
package main;
use strict;
use Tangram;
use Tangram::Schema;
use Tangram::RawDateTime;
use Tangram::IntrSet;
use Tangram::mysql;
my $schema = new Tangram::Schema
{
classes =>
[
'Credit' => {
fields => {
# nothing, this is an association class with no data.
},
},
'Movie' => {
fields => {
string => [ qw(title) ],
int => [ qw(release_year) ],
# this means there is a set of 'Credit' objects
# related to this 'Movie' object.
iset =>
{
credits => 'Credit',
},
},
},
'Person' => {
fields => {
string => [ qw(name) ],
rawdatetime => [ qw(birthdate) ],
ref => [ qw(birth_location) ],
# This person also has a set of credits
iset =>
{
credits => 'Credit',
},
},
},
'Job' => {
fields => {
string => [ qw(job_title) ],
# As does this job
iset =>
{
credits => 'Credit',
},
},
},
'Location' => {
fields => {
string => [ qw(location) ],
ref => [ qw(parent_location) ],
},
},
],
};
my ($dsn, $user, $pass) =
("dbi:mysql:database=movies", "root", "");
print "Connecting to the database\n";
my $dbh = DBI->connect($dsn, $user, $pass);
print "Creating tables with SQL command:\n";
Tangram::mysql->deploy($schema);
print "Now creating tables...\n";
Tangram::mysql->deploy($schema, $dbh);
print "Disconnecting...\n";
$dbh->disconnect() or warn $DBI::errstr;;
# now connect to it as if we were a normal program
print "Connecting to Storage...\n";
my $storage = Tangram::Storage->connect($schema, $dsn, $user, $pass);
# Insert some data
do {
print "Building data objects...\n";
my @locations =
(
new Location( location => "Grappenhall",
parent_location => new Location
( location => "Warrington",
parent_location => new Location
( location => "Cheshire",
parent_location => new Location
( location => "England",
parent_location => new Location
( location => "United Kingdom" ) ) ) ) ),
new Location( location => "Dallas",
parent_location => new Location
( location => "Texas",
parent_location => new Location
( location => "United States" ) ) ),
);
my @credits = ( map { new Credit } (1..5) );
my @jobs =
(
new Job( job_title => "Dr. Frank-N-Furter",
credits => Set::Object->new( $credits[0] ) ),
new Job( job_title => "Wadsworth",
credits => Set::Object->new( $credits[1] ) ),
new Job( job_title => "Prosecutor",
credits => Set::Object->new( $credits[2] ) ),
new Job( job_title => "Long John Silver",
credits => Set::Object->new( $credits[3] ) ),
new Job( job_title => "Dr. Scott",
credits => Set::Object->new( $credits[4] ) ),
);
my @movies =
(
new Movie( title => "Rocky Horror Picture Show",
release_year => 1975,
credits => Set::Object->new( @credits[0, 4] ) ),
new Movie( title => "Clue",
release_year => 1985,
credits => Set::Object->new( $credits[1] ) ),
new Movie( title => "The Wall: Live in Berlin",
release_year => 1990,
credits => Set::Object->new( $credits[2] ) ),
new Movie( title => "Muppet Treasure Island",
release_year => 1996,
credits => Set::Object->new( $credits[3] ) ),
);
my @actors =
(
new Person( name => "Tim Curry",
birthdate => "1946-04-19 12:00:00",
birth_location => $locations[0],
credits =>
Set::Object->new( @credits[0..3] ) ),
new Person( name => "Marvin Lee Aday",
birthdate => "1947-09-27 12:00:00",
birth_location => $locations[1],
credits =>
Set::Object->new( $credits[4] ) ),
);
$|=1;
print "Inserting data objects into storage...";
print "movies"; $storage->insert(@movies);
print ", jobs"; $storage->insert(@jobs);
print ", credits"; $storage->insert(@credits);
print ", actors"; $storage->insert(@actors);
print ", done!\n";
};
# first get the person in question
print "Getting Remote objects...\n";
my ($r_person, $r_movie, $r_job, $r_credit, $r_location) =
$storage->remote( qw(Person Movie Job Credit Location) );
# turn on tracing of SQL
$Tangram::TRACE = \*STDOUT;
for my $name ("Tim Curry", "Marvin Lee Aday") {
print "Selecting...\n";
my ($who) = $storage->select($r_person, $r_person->{name} eq $name
+);
# print the same header; but let's throw in the birth location too
+,
# because it's so easy.
print "Printing...\n";
print ($who->{name}, ", born in ", $who->{birth_location}->{locati
+on},
" was in the following films:\n\n");
# now iterate through the credits desired
for my $credit ($who->{credits}->members) {
my ($movie) =
$storage->select($r_movie,
$r_movie->{credits}->includes($credit) );
my ($job)
= $storage->select($r_job,
$r_job->{credits}->includes($credit) );
if ($movie) {
print( $movie->title, " released in ",
$movie->release_year, "\n");
} else {
print "Um, an unknown movie :-}\n";
}
if ($job) {
print ' ', $job->job_title, "\n";
} else {
print " Oh dear, no job\n";
}
}
}
# Select all people from a suburb of Warrington, who were in the movie
+ "Clue"
my ($clue) = $storage->select($r_movie, $r_movie->{title} eq "Clue");
my ($person) = $storage->select($r_person, ($r_person->{birth_location
+}->{parent_location}->{location} eq "Warrington"));
print $person->{name}, "\n";
As you can see, there is no SQL here and I'm still doing some quite complex queries.
A brief investigation of UML is probably worth the effort, too - so you can figure out how to think of concepts such as foreign keys in OO terms. | [reply]
[d/l] |
Re: Leashing DBI
by Matt S Trout (Initiate) on Jun 27, 2001 at 20:32 UTC
|
My personal preference would be to enclose each table we're working with (and hence its associated SQL code) in a functional class. This then makes the main Perl code a *hodload* cleaner, and means any changes to data sources/field names/whatever can be made in the class modules without having to tweak the logical flow of the main program. | [reply] |
|
|
