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] -
[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
