<div class="gmail_quote">On Fri, Dec 4, 2009 at 5:31 PM, Jamu Kakar <span dir="ltr"><<a href="mailto:jkakar@kakar.ca" target="_blank">jkakar@kakar.ca</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Hi Jason,<br>
<div><div></div><div><br>
On Fri, Dec 4, 2009 at 2:43 PM, Jason Baker <<a href="mailto:jbaker@zeomega.com" target="_blank">jbaker@zeomega.com</a>> wrote:<br>
> One thing we'd like to have in storm is the ability to specify a sequence<br>
> that we can use by default on primary keys. For instance, suppose I have a<br>
> class like this:<br>
> class Foo(object):<br>
> __storm_table__ = 'foo'<br>
> foo_pk = Int(primary=True)<br>
> If we want foo_pk to autoincrement on Oracle, our only option is to define a<br>
> trigger that will call nextval on the sequence. It would be nice to be able<br>
> to do something like this:<br>
> class Foo(object):<br>
> __storm_table__ = 'foo'<br>
> foo_pk = Int(primary=True, sequence_name='foo_seq')<br>
> This way, when I insert a new Foo, it will automatically pull from foo_seq<br>
> and I don't have to define a trigger. Of course for this to be useful,<br>
> other platforms would need to ignore that keyword argument. I'd be willing<br>
> to work on this if someone could give me some feedback into the best way to<br>
> implement this.<br>
<br>
</div></div>I don't really understand how Oracle works</blockquote><div><br></div><div>One thing that's important to understand is that Oracle doesn't have any way to define an IDENTITY/AUTOINCREMENT field. You may or may not already know this, I just thought it would be good to mention it in case you didn't. :-)</div>
<div> </div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> In other words,<br>
do you need Storm to issue a particular query whenever you perform<br>
an insert to get autoincrement behaviour or do you just need to<br>
define a trigger as part of your schema?<br></blockquote><div> </div><div>In Oracle circles, a trigger is considered to be a hacky workaround for applications that aren't able to work with sequences. Here's how it would work. Suppose I define a table and sequence like this:</div>
<div><br></div><div> CREATE TABLE foo(</div><div> foo_pk INT PRIMARY KEY);</div><div><br></div><div> CREATE SEQUENCE foo_seq;</div><div><br></div><div>I can make the table foo use foo_seq one of two ways:</div><div>
<br></div><div> 1. I can define a trigger that will automatically get a value from foo_seq every time a row is inserted</div><div> 2. I can write the INSERT query like this:</div><div><br></div><div> INSERT INTO foo(foo_pk) VALUES (foo_seq.nextval);</div>
<div><br></div><div>Of course, Storm has a way of compiling sequences. But unless I'm missing something, the only real way to take advantage of that would be something like this:</div><div><br></div><div><br></div><div>
foo_row = Foo()</div><div> if db_platform == 'oracle':</div><div> foo_row.foo_pk = Sequence('foo_seq')</div><div> store.add(foo_row)</div><div><br></div><div>But it would be nice to have a way of defining a column that will get its value from a particular sequence automatically.</div>
<div><br></div><div>Another idea that just occurred to me is that most of the time, sequences will be defined by giving the table name plus some kind of suffix like "_seq". So it would save a *lot* of tedium if I could do something like this:</div>
<div><br></div><div>SequenceProperty = sequencemaker(suffix='_seq')</div><div><br></div><div>class Foo(object):</div><div> __storm_table__ = 'foo'</div><div><br></div><div> # assume the sequence will be named 'foo_seq' since the table is named foo</div>
<div> foo_pk = SequenceProperty(primary=True, datatype=Int)</div><div><br></div><div>And have backends like MySQL, SQL Server, and SQLite ignore the fact that it's a Sequence property</div></div>