Inserting a relation into a database with Class::DBI

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

#!/usr/local/bin/perl

use strict;
use DBI;

my $db_file = 'database.db';

package MyDbase::DBI;
use base 'Class::DBI';

sub connect {
        DBI->connect("dbi:SQLite:dbname=$db_file","","");
}

if (!-e $db_file) {
        my $db = &connect;
        $db->do(q{
                CREATE TABLE user (
                        handle          STRING PRIMARY KEY,
                        password        STRING,
                        session         INTEGER
        )});

        $db->do(q{
                CREATE TABLE session (
                        id              STRING PRIMARY KEY,
                        timestamp       INTEGER
        )});
}

package MyDbase::Session;
use base 'MyDbase::DBI';
MyDbase::Session->table('session');
MyDbase::Session->columns(All => qw/id timestamp/);
MyDbase::DBI->set_db('Main',"dbi:SQLite:dbname=$db_file","","");

package MyDbase::User;
use base 'MyDbase::DBI';
MyDbase::User->table('user');
MyDbase::User->columns(All => qw/handle password session/);
MyDbase::User->has_a(session => 'MyDbase::Session');
MyDbase::DBI->set_db('Main',"dbi:SQLite:dbname=$db_file","","");

package main;

MyDbase::DBI->connect;
my $session_id = 'aaa';
my $user_rec = {
        'handle'        => 'a',
        'password'      => crypt('a','a'),
};
# is this a requirement or can both records be created at once?
#my $session_rec = MyDbase::Session->insert({
my $session_rec = {
        'id'            => $session_id,
        'timestamp'     => time,
};
#});

$user_rec->{'session'} = $session_rec;
MyDbase::User->insert($user_rec);
[download]

The error produced is:

Can't deflate session: HASH(0x84ee954) is not a MyDbase::Session at /u
+sr/local/lib/perl5/site_perl/5.8.5/Class/DBI/Relationship/HasA.pm lin
+e 86
[download]

This error goes away if I used the commented out code (using a MyDbase::Session object instead of a hash).
However, doing it that way hits the database twice instead of once.

My question is how to do this insert with a single database access.
Thanks in advance :)

Comment on Inserting a relation into a database with Class::DBI Select or Download Code

Replies are listed 'Best First'.
Re: Inserting a relation into a database with Class::DBI by FunkyMonk (Bishop) on May 29, 2007 at 08:56 UTC
`# is this a requirement or can both records be created at once? #my $session_rec = MyDbase::Session->insert({ my $session_rec = { 'id' => $session_id, 'timestamp' => time, }; #});` [download] You create a hashref called `$session_rec` which probably should be passed to `insert` like so `my $session_rec = MyDbase::Session->insert( my $session_rec = { 'id' => $session_id, 'timestamp' => time, }; );` [download] Notice that I've removed a pair of braces from your called to `insert` that were attempting to create a hashref from `$session_rec` which is already a hashref. When I try to do something similar here, I get the error I expected. `my $hr2 = { my $hr1 = { A => 1, B => 2 } }; #Odd number of elements in anonymous hash at /home/funky/play line 46.` [download] Standard Disclaimer.txt I haven't used DBI::Class in a long while, so I may be a mile off the mark.	[reply] [d/l] [select]
Re^2: Inserting a relation into a database with Class::DBI by wojtyk (Friar) on May 29, 2007 at 16:39 UTC
Though I appreciate the response, this doesn't really address my primary question. Namely, I only want a single insert taking place in this code (instead of one insert each for $session_rec and $user_rec). Since there's no need to touch the database twice, it feels dirty to me to issue two queries for an action that could be done in parallel. Is there a way to compress the inserts into a single call (or at least a single database access)? Since $session_rec has a has_a() relationship with $user_rec, I don't see why DBI::Class wouldn't be smart enough to handle this.	[reply]
Re^3: Inserting a relation into a database with Class::DBI by agianni (Hermit) on May 29, 2007 at 17:54 UTC
I don't know of any easy way to do what you want in one query in C::DBI. Generally, if you are concerned about performance, particularly the number of queries being executed, you should probably be looking at an alternative to C::DBI, as it regularly executes lots of queries where a smaller number would suffice. I don't have any specific recommendations for alternatives, but I know there are a few others out there. `perl -e 'split//,q{john hurl, pest caretaker}and(map{print @_[$_]}(joi +n(q{},map{sprintf(qq{%010u},$_)}(2*23074993,51016415261,75979 +36997,13177145131,3*26789167*181))=~/\d{2}/g));'` [download]	[reply] [d/l]
Re: Inserting a relation into a database with Class::DBI by andreas1234567 (Vicar) on May 29, 2007 at 19:37 UTC
`MyDbase::Session->insert(...) MyDbase::User->insert(...)` [download] My question is how to do this insert with a single database access. What are your reasons for doing it in a single operation? Discomfort? Speed? (If yes, did you benchmark the code?) Code elegance? I am unfamiliar with the features of SQLlite. Most databases, such as Oracle, Microsoft SQL Server, Postgres and MySQL (as of version 5.0), support stored procedures. It allows you to call a procedure inside the database much like you would call a Perl sub. That way you can combine multiple operations in a single call like you requested. Andreas --	[reply] [d/l]
Re^2: Inserting a relation into a database with Class::DBI by wojtyk (Friar) on May 29, 2007 at 20:35 UTC
What are your reasons for doing it in a single operation? Scalable performance/speed, as well as elegance. I may take a look at the stored procedures you recommended. I was trying to avoid having to use actual SQL in my code, as it appears kludgy to me. Stored procedures appears to be a happy medium.	[reply]