I have a problem using sqlite user-defined aggregate with sqlobject 0.7.1b1. The problem also happens with last revision from the trunk but it does not happen with r1457.
In short, I want to use the "group_concat" aggregate (from MySQL) in a view. I thus have to first create the aggregate with a "pysqlite connection", then create my view with raw SQL.
The problem is the aggregate function belongs to the "pysqlite connection" and that this connection is not reused when creating the view, or doing other DML with sqlobject. So it always ends with a " pysqlite2.dbapi2.OperationalError: no such function: group_concat".
I attached a small piece of code that shows the problem. On sqlobject r1457 the output is what I expect it to be:
[<color 1 name='red'>, <color 2 name='green'>]
[<color 2 name='green'>, <color 3 name='blue'>]
<view_item 2 name='paper' colors='blue, green'>
<view_item 1 name='pencil' colors='green, red'>
Is this a bug in r1457 or a bug in newer revision?
How can I make this work on newer version of sqlobject?
Thanks in advance,
--
jt
import os import sqlobject as so from sqlobject.sqlite.sqliteconnection import SQLiteConnection
class item(so.SQLObject):
name = so.StringCol(default='')
color = so.RelatedJoin('color')
class color(so.SQLObject):
name = so.StringCol(default='black')
item = so.RelatedJoin('item')
class view_item(so.SQLObject):
name = so.StringCol()
colors = so.StringCol()
@classmethod
def createTable(cls, ifNotExists=False, connection=None):
conn = connection or cls._connection
if ifNotExists and conn.tableExists(cls.sqlmeta.table):
return
sql = cls.createTableSQL()
return sql
@classmethod
def createTableSQL(cls, connection=None):
sql = """CREATE VIEW view_item as
SELECT item.id as id,
item.name as name,
group_concat(DISTINCT color.name) as colors
FROM item, color
JOIN color_item ci ON ci.color_id=color.id AND ci.item_id=item.id
GROUP BY item.id
ORDER BY item.name
;"""
return sql
def create_aggregate(pysqlitecon):
class group_concat:
def __init__(self):
self.acc = []
def step(self, value):
if isinstance(value, basestring):
self.acc.append(value)
else:
self.acc.append(str(value))
def finalize(self):
self.acc.sort()
return ", ".join(self.acc)
pysqlitecon.create_aggregate("group_concat", 1, group_concat)
dbname = "data.db"
con = SQLiteConnection(os.path.realpath(dbname), autoCommit=0, debug=0)
so.sqlhub.processConnection = con
item.createTable(ifNotExists=True)
color.createTable(ifNotExists=True)
create_aggregate(con.getConnection())
con.query(view_item.createTable(ifNotExists=True))
i1 = item(name="pencil")
i2 = item(name="paper")
c1 = color(name="red")
c2 = color(name="green")
c3 = color(name="blue")
i1.addColor(c1)
i1.addColor(c2)
i2.addColor(c2)
i2.addColor(c3)
print i1.color
print i2.color
for v in view_item.select():
print v
# vim: set et foldlevel=0:
_______________________________________________ sqlobject-discuss mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
