I want to use sql views to simplify querying a schema. I have to join with a dozen of tables and this raise problem with the mandatory id field. I illustrate it with the following code (sqlobject-0.7.1dev_r1788-py2.4 , pysqlite-2.2.2):
# ----------------------------------------------------------------------------
import os
import sqlobject as so
from sqlobject.sqlite.sqliteconnection import SQLiteConnection
con = SQLiteConnection(':memory:')
so.sqlhub.processConnection = con
class item(so.SQLObject):
name = so.StringCol(default='')
provider = so.RelatedJoin('provider', intermediateTable='item_provider')
class provider(so.SQLObject):
name = so.StringCol(default='')
item = so.RelatedJoin('item', intermediateTable='item_provider')
item.createTable()
provider.createTable()
class supplies(so.SQLObject ):
item_name = so.StringCol()
provider_name = so.StringCol()
@classmethod
def createTable(cls, connection=None):
return cls.createTableSQL()
@classmethod
def createTableSQL(cls):
return """CREATE VIEW supplies AS
SELECT i.id + p.id AS id,
i.name AS item_name,
p.name AS provider_name
FROM item i, provider p
INNER JOIN item_provider ip ON i.id=ip.item_id AND p.id=ip.provider_id
"""
sql = supplies.createTable ()
con.query(sql)
i1 = item(name='paper')
i2 = item(name='tape')
p1 = provider(name='John')
p2 = provider(name='Pam')
p1.addItem(i1)
p1.addItem(i2)
p2.addItem(i2)
p2.addItem(i2)
for r in con.queryAll("select * from supplies"):
print r
for r in supplies.select():
print r
# ----------------------------------------------------------------------------
In the view definition I have to explicitly define an id but, as it is the result of a select, it cannot be an autoincrement / serial.
So the results of select() can be wrong (uncomment the line p2.addItem(i1) to see it) since there could be different rows with actually the same id. In my schema I also have explicit intermediate tables with association attributes: in the example above it translates in calling addItem several times on the same provider and item.
I cannot find a elegant and generic way to do this. The best way I can think of is to find bounds to the number of rows of the tables implied in the joins and use them as a basis (in the sense of linear algebra) for my view's id space. But it is difficult to predict such bounds and it is not generic (7 tables with max 1024 rows and you are already out of the 64 bit integer space; or just overflow the predicted bound).
Any idea / advice / best practice?
--
jt
_______________________________________________ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
