Re: Emulating a sequence

I agree about the sequence, the issue here is that the underlying
database, SQLite just doesn't have a sequences, so you are sort of
committed to rolling your own. It is somewhat the price we pay for using
SQLite. I use sqlite all the time for a plethora of simple tasks. The
real question to me always becomes, when do you move to a different
database, everything that makes Sqlite attractive, easy to use, pretty
fast, and can easily be imbedded in an app, is also summed up in its
name sqlite is not meant to be the replacement for your primary Oracle
DBMS :)

Thanks,

Greg Givler
Information Technology
Lockwood®
10 Valley Stream Parkway
Malvern, PA 19355
Phone: (484) 605-4826
Email: ggivler-zdgUFu1MqnQ@xxxxxxxxxxxxxxxx


                                                                                
                                                       
                      David Pratt                                               
                                                       
                      <fairwinds@eastli        To:       
ggivler-zdgUFu1MqnQ@xxxxxxxxxxxxxxxx                                            
                  
                      nk.ca>                   cc:       
pysqlite-Y+ulVh6ByXJeoWH0uzbU5w@xxxxxxxxxxxxxxxxxxxxx                           
                            
                                               Subject:  Re: [pysqlite] 
Emulating a sequence                                           
                      05/02/2006 07:19                                          
                                                       
                      PM                                                        
                                                       
                                                                                
                                                       
                                                                                
                                                       




Hi Greg. Thanks for this suggestion. I guess here I'd still need a
separate field in a table to increment. I have two scenarios in my code
where the sequence is needed on a field that is not an auto-incremented
primary field.

One thing about a real sequence it that once you ask for the next value
it has a contract not to give that value out again so you are assured
integrity which is what i really need.

Regards
David

ggivler-zdgUFu1MqnQ@xxxxxxxxxxxxxxxx wrote:
> You could do a Max(column_name) + 1 in your select, this will get the
> Max value of the column. Don't know if that will work for you. Just
> something simple to try.
>
> Thanks,
>
> Greg Givler
> Information Technology
> Lockwood®
> 10 Valley Stream Parkway
> Malvern, PA 19355
> Phone: (484) 605-4826
> Email: ggivler-zdgUFu1MqnQ@xxxxxxxxxxxxxxxx
>
>
>

>                       David Pratt

>                       <fairwinds@xxxxxxxxxx        To:       Tim
Golden <mail-5j+KzFhapbd+shrj4D7q1g@xxxxxxxxxxxxxxxx>

>                       a>                           cc:
pysqlite-Y+ulVh6ByXLJKv9F57nDJg@xxxxxxxxxxxxxxxx

>                       Sent by:                     Subject:  Re:
[pysqlite] Emulating a sequence

>                       pysqlite-bounces@list

>                       s.initd.org

>

>

>                       05/02/2006 09:55 AM

>

>

>
>
>
>
>
> Tim Golden wrote:
>> David Pratt wrote:
>>> I was thinking about faking a sequence. My first thought was to
>>> create a sequence table with a single row where a field is a
sequence
>
>>> value and you just increment the value and retreive it as anything
> else.
>>> It's simple but particulary good.
>
> Eesh! I meant to say not particularly good. I guess it was late when I
> wrote this.
>
>> No comment as such on the sequence class (never
>> tried it myself). I just wanted to make sure
>> you did know about sqlite's automatic autoincrement
>> value. I know it's not the same as a sequence, but
>> in case you were simply trying to achieve the same
>> effect...
>
> Hi Tim. I am aware of autoincrementing and have been using it (see tid
> below). I still need something to emulate a sequence. The field I need
a
>
> sequence for is not the primary key. It is a field that keeps track of
> the commit_order for a type of transaction.
>
> ie.
> create table ztransaction (
>    tid integer not null primary key,
>    packed  boolean not null default false,
>    commit_order integer not null unique,
>    more fields ...
> );
>
> Hopefully there are a few ideas out there. I'll have to finalize on
one
> today in any case :-)
>
> Regards
> David
> _______________________________________________
> pysqlite mailing list
> pysqlite-IAPFreCvJWPBWskQ1e/+sw@xxxxxxxxxxxxxxxx
> http://lists.initd.org/mailman/listinfo/pysqlite
>
> ______________________________________________________________________
> This email has been scanned by the MessageLabs Email Security System.
> For more information please call the help desk at ext 4850
> ______________________________________________________________________
>
>
>
>
> ______________________________________________________________________
> This email has been scanned by the MessageLabs Email Security System.
> For more information please visit http://www.messagelabs.com/email
> ______________________________________________________________________
>

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please call the help desk at ext 4850
______________________________________________________________________




______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
______________________________________________________________________