Okay - after some seriously thinking about the issue of last-insert-id & side effects, below are my ideas and would love some feedbacks:<br><br><b>Side Effect Data Structure <br></b><br>There are 3 options here: <br><ol>
<li>return the underlying side-effect (and re-export the necessary bindings) for each of the drivers</li><li>provide my own unified side-effect object and convert the underling side-effect object onto the first one <br></li>
<li>convert the side effect into a record set structure (so you do not need to test whether it's a side effect struct)<br></li></ol>The first option's advantage is that if you already have extensive side effect testing code you do not have to change much. The drawback is that you'll have more headache/effort to switch between the databases. <br>
<br>The second option's advantage is the reverse of the first one - you'll break current code, but you reduce one effort to switch between databases. <br><br>The third option unifies the split between record set & side effects. Its advantage is that it removes your need to test the type of the return, but the disadvantage is the access of the result is a bit harder. <br>
<br>I can of course provide all 3 approaches, but would be nice to reduce it down. Any thoughts on which one is the best? My personal thought is #2 or #3 (I love the unification aspect of #3 but know this is not generally how it is done). <br>
<br><b>Last Inserted ID <br></b><br>There are some general issues with last-inserted-id <br><ol><li>it doesn't work for multiple record inserts at the database level <br></li><ol><li>all databases only return a single value even if you insert multiple records into a table at once <br>
</li><li>mysql returns the id for the first record inserted in such case (this feels incorrect) <br></li><li>sqlite returns the id for the last record inserted in such case <br></li></ol><li>postgresql is difficult requires you to know the name of the particular sequence object to get the value <br>
</li></ol>Although it does not always work, the fact that single record insert is the majority of the usage makes it useful, and in that case, I think the design employed by jaz/mysql is the correct one - i.e. <b>return the value as part of the side effect</b>, regardless which particular options chosen above.<br>
<br>Both jaz/mysql & jaymccarthy/sqlite provides the api to allow this, but not schematics/spgsql. This is for the reason stated above that you'll need to know (or derive the sequence) the sequence name in order to determine the value, and that this needs to be done with a separate query (which can slow things down if you do not need the id). <br>
<br>My question here: should I provide the last-inserted-id for spgsql given the constraints? Or just jazmysql & jsqlite? <br><br>I plan on making the changes by next week - so any feedback prior to that would be welcome. <br>
<br>Thanks,<br>yc<br><br><div class="gmail_quote">On Thu, Oct 1, 2009 at 5:47 PM, YC <span dir="ltr"><<a href="mailto:yinso.chen@gmail.com" target="_blank">yinso.chen@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<br><div class="gmail_quote"><div>On Thu, Oct 1, 2009 at 5:36 PM, Jon Zeppieri <span dir="ltr"><<a href="mailto:zeppieri@gmail.com" target="_blank">zeppieri@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
I'll have to think about how to approach this since each database returns different side effects. If anyone have thoughts into this matter please let me know - I would love to hear it. <br><div class="gmail_quote"><div>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class="gmail_quote"><div><br></div></div></blockquote>Both Perl's DBI and Java's JDBC have DB-neutral mechanisms for this, but they're very different.</div>
<div><br></div><div>Perl DBI: <a href="http://search.cpan.org/%7Etimb/DBI/DBI.pm#last_insert_id" target="_blank">http://search.cpan.org/~timb/DBI/DBI.pm#last_insert_id</a></div>
<div>JDBC: <a href="http://www.ibm.com/developerworks/java/library/j-jdbcnew/#keys" target="_blank">http://www.ibm.com/developerworks/java/library/j-jdbcnew/#keys</a></div><div></div></div></blockquote></div><div><br>Thanks Jon for the links - I'll take a look at them. <br>
<br>yc<br><br></div><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class="gmail_quote"><div></div></div></blockquote></div>
</blockquote></div><br>