RE: [sqlite] GUID/UUID in sqlite.
Thanks, This actually solves another problem also where the uid generation algorithm fails to generate a true uuid. Since table u has uuid column unique, my database will never have a uuid repeated. But the number of uuids I can have will now be dependent on what integer can accommodate (I am sure it's a large number and I hope my database will not consume all of it). Moreover, when the database no longer uses a uuid, it can be removed from this table too, which might mean a considerable work for application developer :-). Regards, Prakash Reddy Bande Altair Engg. Inc, Troy, MI -Original Message- From: Scott Hess [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 29, 2007 1:47 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] GUID/UUID in sqlite. In either case, if you use the UUID as a primary key in more than one table, you should consider having one table to convert the UUID to a 64-bit id, and use that as the primary keys on the other tables. If you have UUID as a primary key, your table will have 2 b-trees, one for the index of UUID to rowid, the other to map the rowid to row data. Once you have 2 such tables, it can be more efficient to break out the mapping of the UUID to an internal id you use elsewhere. Example, instead of: CREATE TABLE t ( uuid TEXT PRIMARY KEY, ... ); INSERT INTO t (uuid, ...) VALUES (?, ...); Do: CREATE TABLE u ( uuid TEXT UNIQUE, internalid INTEGER PRIMARY KEY ); CREATE TABLE t ( fk_internalid INTEGER PRIMARY KEY, ... ); BEGIN; INSERT INTO u (uuid, internalid) VALUES (?, NULL); INSERT INTO t (fk_internalid, ...) VALUES (LAST_INSERT_ROWID(), ...); COMMIT; -scott On 8/29/07, John Stanton <[EMAIL PROTECTED]> wrote: > You could experiment with making your 128 bit entity a BLOB. The > alternative would be to represent it it in ASCII. Changing its radix > would probably be the significant overhead, not the Sqlite storage. > > Prakash Reddy Bande wrote: > > Hi, > > > > I am designing a database where-in my column data is UUID. > > I am trying to figure out which is the best way to handle UUID since if > > stored as text the length would be 32 characters (though UUIDs are 128 > > bit size) and select query based on UUIDs might not be really fast (I > > might be wrong here.) > > > > Regards, > > > > Prakash Reddy Bande > > Altair Engg. Inc, > > Troy, MI > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] GUID/UUID in sqlite.
In either case, if you use the UUID as a primary key in more than one table, you should consider having one table to convert the UUID to a 64-bit id, and use that as the primary keys on the other tables. If you have UUID as a primary key, your table will have 2 b-trees, one for the index of UUID to rowid, the other to map the rowid to row data. Once you have 2 such tables, it can be more efficient to break out the mapping of the UUID to an internal id you use elsewhere. Example, instead of: CREATE TABLE t ( uuid TEXT PRIMARY KEY, ... ); INSERT INTO t (uuid, ...) VALUES (?, ...); Do: CREATE TABLE u ( uuid TEXT UNIQUE, internalid INTEGER PRIMARY KEY ); CREATE TABLE t ( fk_internalid INTEGER PRIMARY KEY, ... ); BEGIN; INSERT INTO u (uuid, internalid) VALUES (?, NULL); INSERT INTO t (fk_internalid, ...) VALUES (LAST_INSERT_ROWID(), ...); COMMIT; -scott On 8/29/07, John Stanton <[EMAIL PROTECTED]> wrote: > You could experiment with making your 128 bit entity a BLOB. The > alternative would be to represent it it in ASCII. Changing its radix > would probably be the significant overhead, not the Sqlite storage. > > Prakash Reddy Bande wrote: > > Hi, > > > > I am designing a database where-in my column data is UUID. > > I am trying to figure out which is the best way to handle UUID since if > > stored as text the length would be 32 characters (though UUIDs are 128 > > bit size) and select query based on UUIDs might not be really fast (I > > might be wrong here.) > > > > Regards, > > > > Prakash Reddy Bande > > Altair Engg. Inc, > > Troy, MI > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] GUID/UUID in sqlite.
Thanks, I will try both (ASCII and BLOB) approaches and see speed vs. storage trade-offs. Any more ideas are welcome. Regards, Prakash Reddy Bande Altair Engg. Inc, Troy, MI -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 29, 2007 11:26 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] GUID/UUID in sqlite. You could experiment with making your 128 bit entity a BLOB. The alternative would be to represent it it in ASCII. Changing its radix would probably be the significant overhead, not the Sqlite storage. Prakash Reddy Bande wrote: > Hi, > > I am designing a database where-in my column data is UUID. > I am trying to figure out which is the best way to handle UUID since if > stored as text the length would be 32 characters (though UUIDs are 128 > bit size) and select query based on UUIDs might not be really fast (I > might be wrong here.) > > Regards, > > Prakash Reddy Bande > Altair Engg. Inc, > Troy, MI > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] GUID/UUID in sqlite.
You could experiment with making your 128 bit entity a BLOB. The alternative would be to represent it it in ASCII. Changing its radix would probably be the significant overhead, not the Sqlite storage. Prakash Reddy Bande wrote: Hi, I am designing a database where-in my column data is UUID. I am trying to figure out which is the best way to handle UUID since if stored as text the length would be 32 characters (though UUIDs are 128 bit size) and select query based on UUIDs might not be really fast (I might be wrong here.) Regards, Prakash Reddy Bande Altair Engg. Inc, Troy, MI - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] GUID/UUID in sqlite.
Hi, I am designing a database where-in my column data is UUID. I am trying to figure out which is the best way to handle UUID since if stored as text the length would be 32 characters (though UUIDs are 128 bit size) and select query based on UUIDs might not be really fast (I might be wrong here.) Regards, Prakash Reddy Bande Altair Engg. Inc, Troy, MI - To unsubscribe, send email to [EMAIL PROTECTED] -
