very interesting read, thanks.
-transami
On Thursday 02 January 2003 10:35 pm, Holden Glova wrote:
AFAIK there isn’t a database independent way of doing what you want. Scott
Ambler has written a paper describing some approaches that you might find
useful. The part about OIDs is the bit I am referring to.
David,
thanks for the rebuttle. i will continue to use auto generated ids. for now i
will just worry about the postgresql and come back to this later b/c i think
you are absolutly right about your interface implementation. the thing about
it though is that’s exactly what DBI/DBD is supposed to do for us. so this
really needs to be a modification to it to be done correctly.
who is the maintainer of DBI/DBD? i would like to suggest this addition. it
could be done in one of two ways:
a new function, like get_key(table_name). this then would be outfitted by all
the DBDs to implement. differences would be handled by the them, for example
in Oracle it looks like according to your (David’s) example the argument
would be ignored.
the other appraoch would be to catch a foo SQL Statement: “SELECT KEY AS keyid
FROM table;” such that DBD would do like wise as above, rather than pass it
on the the database (obviosuly), but return the result as a [ Row ].
i like the last approach as it sets a precedence. but either way, this would
be a great addition to DBI. i imagine some wrinkles might need working out
but surely David’s right. this can be done.
-transami
gavin, you were quite correct!
while the actual database exectuon amounted to about the same, the
pereperation time showed about 4x difference in favor of prepare.
inserting 10000 records with code shown below perp finished in 103.35 secs,
while sending a slew of sql amounted to 448.01 secs. again, most of that time
was taken up building the giant string.
so now its time to refactor my databse routines…
thanks,
transami
p.s. if you look at the code and wonder what the hell a DBI::Factory is, don’t
sweat it. i added that to DBI to do simple connection pooling under mod_ruby.
it just returns a DatabaseHandle.
CODE:
def stat_go(dbh)
sql = ''
10000.times do |i|
sql += %Q{INSERT INTO acct (header, number, name, type, gifi) values
(false, ‘1001’, ‘Sales Tax’, ‘Liability’, ‘#{i}’);\n}
end
dbh.do(sql)
dbh.disconnect
end
def prep_go(dbh)
recs = []
10000.times do |i|
recs << [false, ‘1001’, ‘Sales Tax’, ‘Liability’, “#{i}”]
end
prp = "INSERT INTO acct (header, number, name, type, gifi) VALUES (?, ?, ?,
?, ?)"
dbh.prepare(prp) do |sth|
recs.each do |r|
sth.execute(r[0], r[1], r[2], r[3], r[4])
end
dbh.commit
end
dbh.disconnect
end
dbh = DBI::Factory.make(DB_DSN, DB_USER, DB_PASS)
stat_go(dbh)
#prep_go(dbh)
Hi Transami,
As far as the best implementation, it is probably better to leave your
id column of the insert sql and leave their insertion to the sql. The
reason is that mysql requires you to grab the id after the data is
inserted. If I remember correctly, MS Sql Server performs in a similar
way (thank goodness I haven’t used MS Sql for several years).
Thus, if you have a method that takes the sql as an argument and an
optional argument for id that defaults to ‘id’, then the method should
parse the id into the sql statement as necessary get the id into the
insertSQL (in the case of mysql, this is nothing) and then return the
insert value. For example:
def executeInsert(sql, id=‘id’)
…
end
This convention effectively masks the grimy details of any database’s
auto increment process from programmers, which is precisely what we
want. Moreover, users that did not wish to follow this convention
could simply go about glibly creating their database specific SQL with
the existing methods.
Best,
Dave
PGP.sig (186 Bytes)
PGP.sig (186 Bytes)
On Friday, January 3, 2003, at 02:26 AM, Tom Sawyer wrote:
David,
thanks for the rebuttle. i will continue to use auto generated ids.
for now i
will just worry about the postgresql and come back to this later b/c i
think
you are absolutly right about your interface implementation. the thing
about
it though is that’s exactly what DBI/DBD is supposed to do for us. so
this
really needs to be a modification to it to be done correctly.who is the maintainer of DBI/DBD? i would like to suggest this
addition. it
could be done in one of two ways:a new function, like get_key(table_name). this then would be outfitted
by all
the DBDs to implement. differences would be handled by the them, for
example
in Oracle it looks like according to your (David’s) example the
argument
would be ignored.the other appraoch would be to catch a foo SQL Statement: “SELECT KEY
AS keyid
FROM table;” such that DBD would do like wise as above, rather than
pass it
on the the database (obviosuly), but return the result as a [ Row ].i like the last approach as it sets a precedence. but either way, this
would
be a great addition to DBI. i imagine some wrinkles might need working
out
but surely David’s right. this can be done.-transami
David King Landrith
(w) 617.227.4469x213
(h) 617.696.7133
public key available upon request