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 puid format
1 8000 123
2 9000 169
3 8001 178
4 8002 165
5 9001 180
6 8003 123
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]
-----------------------------------------------------------------------------
