Hi all,
I've encountered problem to add a (sqlite) collation function
to my tables using sqlobject. Introduction + questions at the bottom.
Using plain sqlite you can add a collation function as follows:
----------
import sqlite3
def collate_reverse(string1, string2):
return -cmp(string1, string2)
con = sqlite3.connect(":memory:")
con.create_collation("reverse", collate_reverse)
cur = con.cursor()
cur.execute("create table test(x)")
cur.executemany("insert into test(x) values (?)", [("a",), ("b",)])
cur.execute("select x from test order by x collate reverse")
for row in cur:
print row
con.close()
-----------
I figured I can do the same from the sqlobject level. My idea is to
get the sqlite connection and adding the collate function directly to
it:
conn = sql.connectionForURI('sqlite:memory:/')
# we add a proper collation to compare versions
# only sqlite
if conn.dbName == 'sqlite':
conn.getConnection().create_collation("debian", mycollate)
where mycollate is a simple comparison function. I'm still not sure if this
is possible or not...
The second step would be to add the collation to the table. I've tried to add
an alter statement in as :
class sqlmeta:
createSQL = { 'sqlite' : [
'ALTER TABLE version ALTER COLUMN number COLLATE debian'] }
but this doesn't work because of a sqlite limitation:
http://chensheng.net/p/sqlite_docs_3_5_4/lang_altertable.html
My __questions__ are:
- Can I use the sqlite3 low level statement (create_collation) with the
sqlobejct
connection ? Is this going to work or there no way to do it ?
- How can I add the COLLATE contrains to one of my column while I'm creating
the
table ? I haven't found anything like StringCol(collate='debian') or
similar...
Is this possible, or I've to create the table 'manually' ?
thanks :)
pp
-------------------------------------------------------------------------
Check out the new SourceForge.net Marketplace.
It's the best place to buy or sell services for
just about anything Open Source.
http://sourceforge.net/services/buy/index.php
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
