2009-04-30 16:24:01.752664+00 by Dan Lyke 6 comments
I've been liking coding in Perl with Moose, but KiokuDB seemed like a damned lot of complexity for a distinct lack of usability and there didn't seem to be other options that worked easily. I just want something to toss objects into and pull 'em from an SQL database that I can use from other contexts and write SQL to query (or write your create statements separately).
Continued in the comments...
[ related topics: Perl Open Source Software Engineering Databases ]
comments in ascending chronological order (reverse):
#Comment Re: made: 2009-04-30 16:25:45.345903+00 by: Dan Lyke [edit history]
So I threw together some simple code to do
SQL database stuff with Moose. I need to document it, but basically, create a
set of Moose objects that have an id member:
package Person;
use Moose;
has 'id' => (is => 'rw', isa => 'Int');
has 'name' => (is => 'rw', isa => 'Str');
no Moose;
__PACKAGE__->meta->make_immutable;
set up a connection:
use FbyDB;
my $db = FbyDB::Connection->new(debug=>1);
$db->connect("dbi:SQLite:dbname=./db.sqlite3");
You can get SQL to create tables from a Moose class by doing something like:
$db->do($db->create_statement('Person')
Load or create objects with:
my $person = $db->load_or_create('Person', name => 'Dan');
or, of course, just use Person->new(name=>'Dan') if you know don't
want an initial query. Also available is load_one and
load which returns an array. Write objects with
$db->write($person);
If the 'id' member is defined, this does an UPDATE, otherwise it
does an INSERT. Email me if this is useful to you and we'll set up a
source repository somewhere and document it. Doesn't (yet) work with Mouse 'cause
the introspection stuff is different.
#Comment Re: made: 2009-04-30 17:50:57.336543+00 by: other_todd [edit history]
What's your underlying SQL behind the insert-or-update logic? In my home-grown DAO, I have a function like this:
# smart_update($table, \%params, @keys)
# Does an update or an insert as needed.
# The keys - the additional parameters at the end - are first selected,
# using the values of those params from contents. If a record is found
# for those keys, it is updated; if not, the contents are inserted.
# An example is probably helpful:
# smart_update("SW_WORKS",
# { "WORK_ID" => 15518,
# "WORK_TITLE" => "Project Plan",
# "WORK_DESC" => "Design for something really cool" },
# "WORK_ID")
# specifies WORK_ID as the keyfield. So it looks up the value given
# for WORK_ID in the hash (15518) and checks to see if there is already
# a record. (This is, obviously, designed to be used with unique keys
# or unique multi-column keys.) If record 15518 exists, all the values
# given in the hash replace the existing ones in the record. If it
# doesn't, then the hash values are inserted as a new record.
But I am hearing from a number of sources that I really should remove my smart update (I already have straight wrapper functions for UPDATE and INSERT) and instead write a relatively straight wrapper for the MERGE statement, which became legal from Oracle 9i. The problem of course is that if I have to adapt my DAO to use MySQL, which I sometimes so, I have to keep smart_update() anyhow, and its syntax works out to less convoluted SQL than MERGE, so ....
#Comment Re: made: 2009-04-30 18:00:01.723543+00 by: Dan Lyke [edit history]
I just check for if (defined($obj->id)), as I've been using id INTEGER PRIMARY KEY for my default record identifier for a long time. I've always been targeting one of PostgreSQL, MySQL or SQLite, so generally I try for a solution that works in those three (though I will sometimes just go with the first, a good portion of Flutterby depends on triggers in it).
#Comment Re: made: 2009-05-01 11:06:14.675182+00 by: John Anderson
You seem to be headed towards reinventing DBIx::Class as a Moose Role?
I'm mildly curious to see what you're doing but not sure it's something I'd be using, so the source repo thing may be overkill...
#Comment Re: made: 2009-05-01 12:26:45.428323+00 by: Dan Lyke
Yeah, probably. Nobody seemed to be dealing with Moose objects for DBIx::Class, and DBIx::Class seems to me to be one of those things that's interested in completely abstracting out the SQL to the point where it's impossible to actually understand what's going on.
#Comment Re: made: 2009-05-02 11:32:39.346009+00 by: meuon
"completely abstracting out the SQL to the point where it's impossible to actually understand what's going on" - That's the issue I have with a lot of OO/Class mentality. It's elegant when it's all in your head, but when you have to work with it 6 months later I ask myself:"How the ?!? does this work?"
I love being the nitty gritty guy.. a system I am working with was not scaling well, the bandwidth used between systems (SOAP Services) was insane and slow. Yesterday we realize the "The Java Dude's" magic SOAP/XML classes moved a .5+ mb wsdl for every transaction and we needed lots and lots of transactions quickly. He didn't actually understand what was going on behind the scenes when his class was created for every transaction.
Argh. A little abstraction layer (db server independance) can be good, a lot of it is just insane.