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