[Dbix-class] creating a row that's empty but for an auto-incremented key?

George Hartzell hartzell at alerce.com
Fri Jul 21 00:43:06 CEST 2006


John Siracusa writes:
 > On 7/8/06 12:35 AM, David Kamholz wrote:
 > > -- there doesn't seem to be a single "right syntax" for it. "INSERT
 > > INTO foo;" doesn't work for all databases. Some allow "INSERT INTO
 > > foo DEFAULT VALUES;", but I believe that produces an error in mysql.
 > > So we haven't found a general solution yet which is the right way of
 > > handling it.
 > 
 > Another alternative is:
 > 
 >     INSERT INTO foo (anycol) VALUES (DEFAULT);
 > 
 > That works in MySQL and Postgres, where anycol is literally any column in
 > the table.
 > 
 > That said, so far, I haven't found a syntax that works "everywhere."  This
 > type of thing has to be constructed on a case-by-case basis for each db.

For now, I'm going to go with the DBIx hack that Ash mentioned in his
followup to this note, something like:

  $rs->create({ anycol => \'DEFAULT' });

I'd like to see this feature added and would be happy to work on it a
bit and contribute back.  Would anyone like to sketch the high-value
way to do it (extend SQL::Abstract and then use it, or???)

I only have easy access to sqlite and postgresql.

The syntax mentioned by John above does indeed work for
postgresql8.1.4.

The only syntax that I could figure out for sqlite3 is this:

  insert into foo values (null,null,...); 

where there is a null for each column in the table.

That does not seem to work in postgresql, although the similar

  insert into foo values (default,null,...);

does seem to work.

Why am I doing this, you might ask?  David Kamholz points out that
it's probably bad design, but I think I'm stuck with it.  I'm
creating a relational DB out of data that's being handed to me as an
XML file.  For illustration, imagine that there's a top level elememt
<foo> which optionally has some attributes and optionally encloses
some <moose> elements, e.g.:

  <foo>     
    <moose name="one" value="poodle" />
    <moose name="two" value="noodle" />
  </foo>
  <foo type="extended">
    <moose name="three" value="doodle"/>
  </foo>
  
What I set up was a Foo->has_many('moose' => '..::DB::Moose') and a
Moose->belongs_to('foo' => '..::DB::Foo') to describe the
relationship.

As I'm parsing through a file (using XML::Twig), one foo at a time, I
get a foo, check for it's attributes, and add a row for it to the foo
table.  Then I walk through the moosen, adding rows to the moose table
and setting their foreign key column (foo) to the id of the foo
element that they're part of.

I'm not using the magic 'add_to_moose' method because in reality a
<moose> also has some deeper structure and I'm walking down through it
depth-firstedly and linking the rows into their has-one's as I recurse
back up.

I'm stuck with the XML I'm getting, it's not even all that well
documented.  Fortunately I have a *lot* of it to look at... :o I've
never seen this:

   <foo>
   </foo>
   <foo>
   </foo>

but it wouldn't surprise me if that was considered a valid thing to
have in the file....

So, anyway, would it be evil to add support to DBIx::Class for
something like this?

g.



More information about the Dbix-class mailing list