RE: [sqlite] How to generate Unique ID?
Hi how about the following: CREATE TABLE puids (ID INTEGER PRIMARY KEY AUTOINCREMENT) In python: Def GetUniquePUID(): #OPTIONAL, if you already have a transaction _Conn.cursor().execute("BEGIN EXCLUSIVE") Try: _Conn.cursor().execute("INSERT INTO PUIDS (id) values(null)"); Return _Conn.cursor().execute("select last_insert_rowid()").fetchone()[0] _Conn.cursor().execute("COMMIT") Except: _Conn.cursor().execute("ROLLBACK") raise -Original Message- From: B V, Phanisekhar [mailto:[EMAIL PROTECTED] Sent: 31 August 2007 08:54 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] How to generate Unique ID? Assume I have a table Create table T1 (id INTEGER PRIMARY KEY not null, puid UNIQUE INTEGER not null, format INTEGER not null); Now some values given below Id puidformat 1 8000123 2 9000169 3 8001178 4 8002165 5 9001180 6 8003123 What I wanted was categorize the format values. Format 123, 178, 165, 190, 118, 623, 789, and 234 likewise other values to be categorized into one group. Similarly another category of another set of different formats. Likewise many categories. Now if I want to retrieve all objects of category 1, I can't do where format = 123 or format = 178, or format = 190 ... Hence I wanted to categorize them using puid, all those that belong to category 1 will have puid's from 8000-9000, Likewise others. That's why I wanted to use some generator which will produce a unique puid. Since after reaching the max value 9000; I don't have a method to generate puid that have been deleted. Regards, Phani -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Thursday, August 30, 2007 9:00 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to generate Unique ID? Why do you have a unique primary key as an integer to hold your other unique integer? Why not just use the unique integer as a primary key? If you want to have a limit on the maximum unique ID you can store your next to allocate and next to replace keys in another table. B V, Phanisekhar wrote: > Assume I have a table: > > Create table YYY (id Interger PRIMARY KEY, puid Unique integer) > > Id is the primary key. > > Puid is an unsque interger, whose values needs to be assigned by the > user. > > > > Currently my approach is get the maximum value of puid stored in the > table; add 1 to it and uses this value as puid for any new row that > needs to be added. The problem occurs when I reach the max value. > Meanwhile, some rows might have been deleted. In case, when I reach the > maximum value I want to reuse the puids of the deleted rows for new rows > that are to be added. Currently SQLite uses some algorithm to generate a > unique rowid (even when it reaches the limit). I want to use the same > algorithm here also. I tried to understand the algorithm but couldn't. I > need a simple way by which I can generate a unique puid without writing > the algorithm. > > > > > > Regards, > > Phani > > > > > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How to generate Unique ID?
On Fri, 31 Aug 2007 12:28:49 +0530, "B V, Phanisekhar" <[EMAIL PROTECTED]> wrote: > Hi Gerhard, > > I am finding your code really tough to understand. Can you > please provide some comments? try: from pysqlite2 import dbapi2 as sqlite except ImportError: import sqlite3 as sqlite def init_tables(con): """ This function has to be run immediately after schema creation. It fills the internal SQLite table sqlite_sequence. This is necessary because SQLite creates entries for the sequences only on first use of the sequence, but we don't want to use the sequence via autoincrement fields, but using our own function that gets explicit id ranges. con: connection object """ con.execute(""" insert into sqlite_sequence(name, seq) select name, 1 from sqlite_master where type='table' and name not like 'sqlite%' """) def get_id_range(con, table, n): """ Retrieves a tuple with an id range that can be used for the primary key of the table `table`. con: connection object table: name of the table to get the id range for n: number of usable ids to be allocated """ isolation_level = con.isolation_level start, end = None, None try: con.isolation_level = None # autocommit mode con.execute("BEGIN EXCLUSIVE") start = con.execute("SELECT SEQ FROM SQLITE_SEQUENCE WHERE NAME=?", (table,)).fetchone()[0] end = start + n - 1 con.execute("UPDATE SQLITE_SEQUENCE SET SEQ=? WHERE NAME=?", (end, table)) con.execute("COMMIT") finally: con.isolation_level = isolation_level return start, end if __name__ == "__main__": # Test code, manually look wether the output makes sense ;-) con = sqlite.connect(":memory:") con.execute("create table test(id integer primary key autoincrement, name text)") init_tables(con) print get_id_range(con, "test", 1000) print get_id_range(con, "test", 1000) print get_id_range(con, "test", 1000) con.execute("insert into test(name) values ('foo')") con.execute("insert into test(name) values ('foo')") con.execute("insert into test(name) values ('foo')") - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How to generate Unique ID?
Hi Gerhard, I am finding your code really tough to understand. Can you please provide some comments? Regards, Phani -Original Message- From: Gerhard Haering [mailto:[EMAIL PROTECTED] Sent: Friday, August 31, 2007 12:31 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to generate Unique ID? On Thu, 30 Aug 2007 13:06:38 +0100, "Simon Davies" <[EMAIL PROTECTED]> wrote: > On 30/08/2007, B V, Phanisekhar <[EMAIL PROTECTED]> wrote: >> Simon, >>Yeah you can term the problem like that. Can't I use the >> function which is assigning a unique id for INTEGER PRIMARY KEY column >> inside sql? If yes, how to use it? >> >> Regards, >> Phani >> > > Phani, > > With the whole of the sqlite codebase available you are free to use > any of it as you wish ;-) > But what you are suggesting above is not an approach that I would > choose to get involved with. (I don't know how sqlite assigns its > unique ids for INTEGER PRIMARY KEY columns, but I would be surprised > if it caters for specific subranges). I recently produced sample code that gets id ranges. I once did something similar with Oracle SEQUENCEs, and my sample code emulates sequences as good as it can. It's a rough sketch, and could most probably be improved upon: http://initd.org/tracker/pysqlite/wiki/IdRange import sqlite3 as sqlite import os def init_tables(con): for row in con.execute("select name from sqlite_master where type='table' and name not like 'sqlite%'"): column = None for r in con.execute("pragma table_info (%s)" % row[0]): if r[-1] == 0: column = r[1] break con.execute("insert into %s(%s) values ('xx')" % (row[0], column)) con.execute("delete from %s" % row[0]) def get_id_range(con, table, n): isolation_level = con.isolation_level start, end = None, None try: con.isolation_level = None con.execute("BEGIN EXCLUSIVE") start = con.execute("SELECT SEQ FROM SQLITE_SEQUENCE WHERE NAME=?", (table,)).fetchone()[0] end = start + n - 1 con.execute("UPDATE SQLITE_SEQUENCE SET SEQ=? WHERE NAME=?", (end, table)) con.execute("COMMIT") finally: con.isolation_level = isolation_level return start, end con = sqlite.connect(":memory:") con.execute("create table test(id integer primary key autoincrement, name text)") init_tables(con) print get_id_range(con, "test", 1000) print get_id_range(con, "test", 1000) print get_id_range(con, "test", 1000) con.execute("insert into test(name) values ('foo')") con.execute("insert into test(name) values ('foo')") con.execute("insert into test(name) values ('foo')") print con.execute("select * from test").fetchall() -- Gerhard - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How to generate Unique ID?
Assume I have a table Create table T1 (id INTEGER PRIMARY KEY not null, puid UNIQUE INTEGER not null, format INTEGER not null); Now some values given below Id puidformat 1 8000123 2 9000169 3 8001178 4 8002165 5 9001180 6 8003123 What I wanted was categorize the format values. Format 123, 178, 165, 190, 118, 623, 789, and 234 likewise other values to be categorized into one group. Similarly another category of another set of different formats. Likewise many categories. Now if I want to retrieve all objects of category 1, I can't do where format = 123 or format = 178, or format = 190 ... Hence I wanted to categorize them using puid, all those that belong to category 1 will have puid's from 8000-9000, Likewise others. That's why I wanted to use some generator which will produce a unique puid. Since after reaching the max value 9000; I don't have a method to generate puid that have been deleted. Regards, Phani -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Thursday, August 30, 2007 9:00 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to generate Unique ID? Why do you have a unique primary key as an integer to hold your other unique integer? Why not just use the unique integer as a primary key? If you want to have a limit on the maximum unique ID you can store your next to allocate and next to replace keys in another table. B V, Phanisekhar wrote: > Assume I have a table: > > Create table YYY (id Interger PRIMARY KEY, puid Unique integer) > > Id is the primary key. > > Puid is an unsque interger, whose values needs to be assigned by the > user. > > > > Currently my approach is get the maximum value of puid stored in the > table; add 1 to it and uses this value as puid for any new row that > needs to be added. The problem occurs when I reach the max value. > Meanwhile, some rows might have been deleted. In case, when I reach the > maximum value I want to reuse the puids of the deleted rows for new rows > that are to be added. Currently SQLite uses some algorithm to generate a > unique rowid (even when it reaches the limit). I want to use the same > algorithm here also. I tried to understand the algorithm but couldn't. I > need a simple way by which I can generate a unique puid without writing > the algorithm. > > > > > > Regards, > > Phani > > > > > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to generate Unique ID?
On Thu, 30 Aug 2007 13:06:38 +0100, "Simon Davies" <[EMAIL PROTECTED]> wrote: > On 30/08/2007, B V, Phanisekhar <[EMAIL PROTECTED]> wrote: >> Simon, >>Yeah you can term the problem like that. Can't I use the >> function which is assigning a unique id for INTEGER PRIMARY KEY column >> inside sql? If yes, how to use it? >> >> Regards, >> Phani >> > > Phani, > > With the whole of the sqlite codebase available you are free to use > any of it as you wish ;-) > But what you are suggesting above is not an approach that I would > choose to get involved with. (I don't know how sqlite assigns its > unique ids for INTEGER PRIMARY KEY columns, but I would be surprised > if it caters for specific subranges). I recently produced sample code that gets id ranges. I once did something similar with Oracle SEQUENCEs, and my sample code emulates sequences as good as it can. It's a rough sketch, and could most probably be improved upon: http://initd.org/tracker/pysqlite/wiki/IdRange import sqlite3 as sqlite import os def init_tables(con): for row in con.execute("select name from sqlite_master where type='table' and name not like 'sqlite%'"): column = None for r in con.execute("pragma table_info (%s)" % row[0]): if r[-1] == 0: column = r[1] break con.execute("insert into %s(%s) values ('xx')" % (row[0], column)) con.execute("delete from %s" % row[0]) def get_id_range(con, table, n): isolation_level = con.isolation_level start, end = None, None try: con.isolation_level = None con.execute("BEGIN EXCLUSIVE") start = con.execute("SELECT SEQ FROM SQLITE_SEQUENCE WHERE NAME=?", (table,)).fetchone()[0] end = start + n - 1 con.execute("UPDATE SQLITE_SEQUENCE SET SEQ=? WHERE NAME=?", (end, table)) con.execute("COMMIT") finally: con.isolation_level = isolation_level return start, end con = sqlite.connect(":memory:") con.execute("create table test(id integer primary key autoincrement, name text)") init_tables(con) print get_id_range(con, "test", 1000) print get_id_range(con, "test", 1000) print get_id_range(con, "test", 1000) con.execute("insert into test(name) values ('foo')") con.execute("insert into test(name) values ('foo')") con.execute("insert into test(name) values ('foo')") print con.execute("select * from test").fetchall() -- Gerhard - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to generate Unique ID?
I just need the answer for my original question. Regards, Phanisekhar Maybe you "need" an answer, but others do not "need" to give it to you. If you want others to do your thinking and reading for you, don't demand, ask politely. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to generate Unique ID?
Why do you have a unique primary key as an integer to hold your other unique integer? Why not just use the unique integer as a primary key? If you want to have a limit on the maximum unique ID you can store your next to allocate and next to replace keys in another table. B V, Phanisekhar wrote: Assume I have a table: Create table YYY (id Interger PRIMARY KEY, puid Unique integer) Id is the primary key. Puid is an unsque interger, whose values needs to be assigned by the user. Currently my approach is get the maximum value of puid stored in the table; add 1 to it and uses this value as puid for any new row that needs to be added. The problem occurs when I reach the max value. Meanwhile, some rows might have been deleted. In case, when I reach the maximum value I want to reuse the puids of the deleted rows for new rows that are to be added. Currently SQLite uses some algorithm to generate a unique rowid (even when it reaches the limit). I want to use the same algorithm here also. I tried to understand the algorithm but couldn't. I need a simple way by which I can generate a unique puid without writing the algorithm. Regards, Phani - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to generate Unique ID?
Hi Phani, On Thu, 30 Aug 2007 16:51:27 +0530, you wrote: >Simon, > Yeah you can term the problem like that. Can't I use the >function which is assigning a unique id for INTEGER PRIMARY KEY column >inside sql? If yes, how to use it? > >Regards, >Phani SQLite will do the job for you, it's all in the documentation. http://www.sqlite.org/lang_createtable.html : Specifying a PRIMARY KEY normally just creates a UNIQUE index on the corresponding columns. [snip] Additional detail on automatic B-Tree key generation is available separately. -> http://www.sqlite.org/autoinc.html : When a new row is inserted into an SQLite table, the ROWID can either be specified as part of the INSERT statement or it can be assigned automatically by the database engine. To specify a ROWID manually [snip] If no ROWID is specified on the insert, an appropriate ROWID is created [snip] Note: this solution is not standard SQL and not portable. For more or less protable solution you would have to use smart trigger code. Regards, -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to generate Unique ID?
On 30/08/2007, B V, Phanisekhar <[EMAIL PROTECTED]> wrote: > Simon, >Yeah you can term the problem like that. Can't I use the > function which is assigning a unique id for INTEGER PRIMARY KEY column > inside sql? If yes, how to use it? > > Regards, > Phani > Phani, With the whole of the sqlite codebase available you are free to use any of it as you wish ;-) But what you are suggesting above is not an approach that I would choose to get involved with. (I don't know how sqlite assigns its unique ids for INTEGER PRIMARY KEY columns, but I would be surprised if it caters for specific subranges). Rgds, Simon - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How to generate Unique ID?
Simon, Yeah you can term the problem like that. Can't I use the function which is assigning a unique id for INTEGER PRIMARY KEY column inside sql? If yes, how to use it? Regards, Phani -Original Message- From: Simon Davies [mailto:[EMAIL PROTECTED] Sent: Thursday, August 30, 2007 4:35 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to generate Unique ID? On 30/08/2007, B V, Phanisekhar <[EMAIL PROTECTED]> wrote: > Simon, > > > Without knowing your design, it looks like you wish to be able to > > determine type from the id. This is creating a problem. Can you not > > store the type separately from the id? > > Why is this creating a problem? > No it's not possible to store the type separately. > > I just need the answer for my original question. > > Regards, > Phanisekhar > Phani, Without the requirement to have unique ids within fixed ranges dependant on type, you could just declare a column as 'INTEGER PRIMARY KEY' and let sqlite take care of assigning your ids. As it is you have to come up with a way to generate these unique ids yourself, hence your question. This is what I have termed a problem. Rgds, Simon - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to generate Unique ID?
On 30/08/2007, B V, Phanisekhar <[EMAIL PROTECTED]> wrote: > Simon, > > > Without knowing your design, it looks like you wish to be able to > > determine type from the id. This is creating a problem. Can you not > > store the type separately from the id? > > Why is this creating a problem? > No it's not possible to store the type separately. > > I just need the answer for my original question. > > Regards, > Phanisekhar > Phani, Without the requirement to have unique ids within fixed ranges dependant on type, you could just declare a column as 'INTEGER PRIMARY KEY' and let sqlite take care of assigning your ids. As it is you have to come up with a way to generate these unique ids yourself, hence your question. This is what I have termed a problem. Rgds, Simon - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How to generate Unique ID?
Simon, > Without knowing your design, it looks like you wish to be able to > determine type from the id. This is creating a problem. Can you not > store the type separately from the id? Why is this creating a problem? No it's not possible to store the type separately. I just need the answer for my original question. Regards, Phanisekhar -Original Message- From: Simon Davies [mailto:[EMAIL PROTECTED] Sent: Thursday, August 30, 2007 4:13 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to generate Unique ID? On 30/08/2007, B V, Phanisekhar <[EMAIL PROTECTED]> wrote: > Hi Simon, >The problem is I have rowid which is the PRIMARY Key, and > another column puid which should be unique. Now when I do vacuum rowid > changes, but puid doesn't change and I don't want puid to change. Now it > is possible to make puid itself as PRIMARY KEY, but the problem is I > assign the puid's to the objects (row entries) based upon their type. > For some type of objects I need values from x to x + 1000, for other x + > 1000 to x + 2000 and likewise. Hence I need a generator which can > generate a number from the given range (x, x + 1000) which is not > assigned. > > Regards, > Phani > Hi Phani, Implicit rowid's might be modified by VACUUM, but values in a column declared 'INTGER PRIMARY KEY' will not. Without knowing your design, it looks like you wish to be able to determine type from the id. This is creating a problem. Can you not store the type separately from the id? Rgds, Simon - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to generate Unique ID?
On 30/08/2007, B V, Phanisekhar <[EMAIL PROTECTED]> wrote: > Hi Simon, >The problem is I have rowid which is the PRIMARY Key, and > another column puid which should be unique. Now when I do vacuum rowid > changes, but puid doesn't change and I don't want puid to change. Now it > is possible to make puid itself as PRIMARY KEY, but the problem is I > assign the puid's to the objects (row entries) based upon their type. > For some type of objects I need values from x to x + 1000, for other x + > 1000 to x + 2000 and likewise. Hence I need a generator which can > generate a number from the given range (x, x + 1000) which is not > assigned. > > Regards, > Phani > Hi Phani, Implicit rowid's might be modified by VACUUM, but values in a column declared 'INTGER PRIMARY KEY' will not. Without knowing your design, it looks like you wish to be able to determine type from the id. This is creating a problem. Can you not store the type separately from the id? Rgds, Simon - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How to generate Unique ID?
Hi Simon, The problem is I have rowid which is the PRIMARY Key, and another column puid which should be unique. Now when I do vacuum rowid changes, but puid doesn't change and I don't want puid to change. Now it is possible to make puid itself as PRIMARY KEY, but the problem is I assign the puid's to the objects (row entries) based upon their type. For some type of objects I need values from x to x + 1000, for other x + 1000 to x + 2000 and likewise. Hence I need a generator which can generate a number from the given range (x, x + 1000) which is not assigned. Regards, Phani -Original Message- From: Simon Davies [mailto:[EMAIL PROTECTED] Sent: Thursday, August 30, 2007 3:21 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to generate Unique ID? Hi Phani, What is the point of generating a unique integer separate from the (unique) integer primary key? If you use the primary key as your unique identifier, sqlite will take care of locating unused (deleted) ids when the maximum value is reached (according to the documentation; I have not tried it) Rgds, Simon On 30/08/2007, B V, Phanisekhar <[EMAIL PROTECTED]> wrote: > Sreedhar, > > I think u didn't read my mail properly. I want to generate unique id for > puid not for id (which is the PRIMARY KEY). Anyone knows how to generate > a undique ID for a column which is not PRIMARY KEY? Also, whenever I > reach the limit (max value), I should be able to use the values of > deleted rows. > > Regards, > Phani > > -Original Message- > From: Sreedhar.a [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 30, 2007 2:22 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] How to generate Unique ID? > > Phani, > > Read the "auto increment" in Sqlite FAQ s it is the first one in the > list, > it will solve your problem. > > > Best Regards, > A.Sreedhar. > > -Original Message- > From: B V, Phanisekhar [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 30, 2007 2:11 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] How to generate Unique ID? > > Assume I have a table: > > Create table YYY (id Interger PRIMARY KEY, puid Unique integer) > > Id is the primary key. > > Puid is an unsque interger, whose values needs to be assigned by the > user. > > > > Currently my approach is get the maximum value of puid stored in the > table; > add 1 to it and uses this value as puid for any new row that needs to be > added. The problem occurs when I reach the max value. > Meanwhile, some rows might have been deleted. In case, when I reach the > maximum value I want to reuse the puids of the deleted rows for new rows > that are to be added. Currently SQLite uses some algorithm to generate a > unique rowid (even when it reaches the limit). I want to use the same > algorithm here also. I tried to understand the algorithm but couldn't. I > need a simple way by which I can generate a unique puid without writing > the > algorithm. > > > > > > Regards, > > Phani > > > > > > > > > > - > 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] How to generate Unique ID?
Hi Phani, What is the point of generating a unique integer separate from the (unique) integer primary key? If you use the primary key as your unique identifier, sqlite will take care of locating unused (deleted) ids when the maximum value is reached (according to the documentation; I have not tried it) Rgds, Simon On 30/08/2007, B V, Phanisekhar <[EMAIL PROTECTED]> wrote: > Sreedhar, > > I think u didn't read my mail properly. I want to generate unique id for > puid not for id (which is the PRIMARY KEY). Anyone knows how to generate > a undique ID for a column which is not PRIMARY KEY? Also, whenever I > reach the limit (max value), I should be able to use the values of > deleted rows. > > Regards, > Phani > > -Original Message- > From: Sreedhar.a [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 30, 2007 2:22 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] How to generate Unique ID? > > Phani, > > Read the "auto increment" in Sqlite FAQ s it is the first one in the > list, > it will solve your problem. > > > Best Regards, > A.Sreedhar. > > -Original Message- > From: B V, Phanisekhar [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 30, 2007 2:11 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] How to generate Unique ID? > > Assume I have a table: > > Create table YYY (id Interger PRIMARY KEY, puid Unique integer) > > Id is the primary key. > > Puid is an unsque interger, whose values needs to be assigned by the > user. > > > > Currently my approach is get the maximum value of puid stored in the > table; > add 1 to it and uses this value as puid for any new row that needs to be > added. The problem occurs when I reach the max value. > Meanwhile, some rows might have been deleted. In case, when I reach the > maximum value I want to reuse the puids of the deleted rows for new rows > that are to be added. Currently SQLite uses some algorithm to generate a > unique rowid (even when it reaches the limit). I want to use the same > algorithm here also. I tried to understand the algorithm but couldn't. I > need a simple way by which I can generate a unique puid without writing > the > algorithm. > > > > > > Regards, > > Phani > > > > > > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How to generate Unique ID?
Sreedhar, I think u didn't read my mail properly. I want to generate unique id for puid not for id (which is the PRIMARY KEY). Anyone knows how to generate a undique ID for a column which is not PRIMARY KEY? Also, whenever I reach the limit (max value), I should be able to use the values of deleted rows. Regards, Phani -Original Message- From: Sreedhar.a [mailto:[EMAIL PROTECTED] Sent: Thursday, August 30, 2007 2:22 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] How to generate Unique ID? Phani, Read the "auto increment" in Sqlite FAQ s it is the first one in the list, it will solve your problem. Best Regards, A.Sreedhar. -Original Message- From: B V, Phanisekhar [mailto:[EMAIL PROTECTED] Sent: Thursday, August 30, 2007 2:11 PM To: sqlite-users@sqlite.org Subject: [sqlite] How to generate Unique ID? Assume I have a table: Create table YYY (id Interger PRIMARY KEY, puid Unique integer) Id is the primary key. Puid is an unsque interger, whose values needs to be assigned by the user. Currently my approach is get the maximum value of puid stored in the table; add 1 to it and uses this value as puid for any new row that needs to be added. The problem occurs when I reach the max value. Meanwhile, some rows might have been deleted. In case, when I reach the maximum value I want to reuse the puids of the deleted rows for new rows that are to be added. Currently SQLite uses some algorithm to generate a unique rowid (even when it reaches the limit). I want to use the same algorithm here also. I tried to understand the algorithm but couldn't. I need a simple way by which I can generate a unique puid without writing the algorithm. Regards, Phani - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How to generate Unique ID?
Phani, Read the "auto increment" in Sqlite FAQ s it is the first one in the list, it will solve your problem. Best Regards, A.Sreedhar. -Original Message- From: B V, Phanisekhar [mailto:[EMAIL PROTECTED] Sent: Thursday, August 30, 2007 2:11 PM To: sqlite-users@sqlite.org Subject: [sqlite] How to generate Unique ID? Assume I have a table: Create table YYY (id Interger PRIMARY KEY, puid Unique integer) Id is the primary key. Puid is an unsque interger, whose values needs to be assigned by the user. Currently my approach is get the maximum value of puid stored in the table; add 1 to it and uses this value as puid for any new row that needs to be added. The problem occurs when I reach the max value. Meanwhile, some rows might have been deleted. In case, when I reach the maximum value I want to reuse the puids of the deleted rows for new rows that are to be added. Currently SQLite uses some algorithm to generate a unique rowid (even when it reaches the limit). I want to use the same algorithm here also. I tried to understand the algorithm but couldn't. I need a simple way by which I can generate a unique puid without writing the algorithm. Regards, Phani - To unsubscribe, send email to [EMAIL PROTECTED] -