Re: [Openledger-developer] The correct way to db?

[Tony F. <to...@sy...>]

On Fri, 2005-03-04 at 08:23, Klavs Klavsen wrote:
> I can see SL uses AutoCommit (not good when it's doing several
> transactions - as in post_transaction) :(

Only when there's no harm in doing so. GL::post_transaction() doesn't
use AutoCommit and only commits once it is finished all its statements.

The transaction support in SL is actually quite robust IMHO. I think
that is a big part of the reason why it is so popular and "Just Works".
When is the last time you heard anything on sql-ledger-users about data
corruption that wasn't caused by hardware failure or PostgreSQL
misconfiguration?

> I'd suggest using commit/rollback - and (it was news to me) using ? in
> the prepare statements, to replace values, which is then given in the
> execute($var1,$var2,...) call when executing.

Definitely, SL is a bit of a mixed bag on the placeholder front. There
is newer code (audit trail, and POS for instance) that uses placeholders
and there is older (like the stuff in GL.pm) that doesn't.

While we're on the topic of placeholders, my preference is for
positional placeholders. ie.

INSERT INTO blah (foo, bar, baz) VALUES (:1, :2, :3)

> Also - I'm used mysql being able to tell me the ID of the last_insert_id
> (ie. resulting ID of last INSERT), which is appearently a unique feature
> of MySQL :)

Yup, specifically added to cope with MySQL's lack of separate sequences
in combination with the lack of transaction support in MyISAM tables. To
the best of my knowledge, auto_increment fields are not part of the ANSI
SQL standard either. They are however a relatively common RDBMS specific
extension though.

> I see SL (ie. Dieter) is generating it's own unique ID and then
> replacing on that :(
> 
> I'd prefer using sequences like this:
> 
> ~    # first generate a unique id for this table
> ~    id = dbh.select_one("SELECT nextval('"id"').first
> 
> ~    # now insert row
> ~    dbh.do("INSERT INTO test (id, name, phone) VALUES (?,?,?)", id,
> "foo", "42")
> 
> The thing is, that currently the SL db has 3 sequences, id, invoiceid
> and orderitemsid - and I'm not a 100% sure that I can use id for the gl
> table?

invoiceid and orderitemsid are strictly for audit trails. As far as I'm
aware they are _NEVER_ used for referential integrity. Also they are
only ever used in the table that their name corresponds to.

> If I can use it - then isn't that the correct way to do it?

I'm not really a DB guy but the way SL does it is very common in my
experience and I believe it is considered to be "the RDBMS independent
way".


-- 
Tony Fraser
to...@sy...
Sybaspace Internet Solutions                        System Administrator
phone: (250) 246-5368                                fax: (250) 246-5398