dabo Commit
Revision 6626
Date: 2011-06-16 05:37:40 -0700 (Thu, 16 Jun 2011)
Author: Ed
Trac: http://trac.dabodev.com/changeset/6626
Changed:
U trunk/dabo/biz/dBizobj.py
U trunk/dabo/db/dCursorMixin.py
Log:
Added basic methods for handling many-to-many (M-M) relationships. This is a
first pass, as it limits a bizobj to a single M-M relationship. My plan is to
move all of the MM-specific stuff into a separate class, so a bizobj can have
more than one such relation in the same way that it can have more than one
child bizobj. I've named the methods that are going to be refactored with an
'mm' prefix.
Here are the primary methods of the bizobj that are involved; most are simply
pass-through to similar methods in the cursor layer.
createAssociation(self, mmOtherTable, mmOtherPKCol, assocTable,
assocPKColThis, assocPKColOther):
This establishes the MM relationship. It takes several parameters:
mmOtherTable: the name of the DataSource (table) for the other half
of the MM relation
mmOtherPKCol: the name of the PK column in the mmOtherTable
assocTable: the name of the table holding the association between
the two
assocPKColThis: the name of the column in the association table for
this PK
assocPKColOther: the name of the column in the association table
for the other PK
lookupPKWithAdd(self, field, val):
Runs a lookup in the specified field for the desired value. If found,
returns the PK for that record. If not found, a new record is created with the
'field' column populated with 'val', and the new record's PK is returned. None
of this affects the current dataset.
mmAssociateValue(self, otherField, otherVal):
This accepts a column and value in the "other" table, and creates an
association to that record for the current record of the bizobj. If the value
doesn't exist in the "other" table, it is added.
mmDisssociateValue(self, otherField, otherVal):
Removes any association between the current record of the bizobj and
the record in the "other" table whose 'otherField' column matches 'otherVal'.
If there is no such association, nothing happens.
mmDisssociateAll(self):
Removes all associations between the current record and the associated
M-M table.
mmSetFullAssociation(self, otherField, listOfValues):
Establishes the full association between the current record of the
bizobj and the "other" table. Any existing relationships that are not in
'listOfValues' are removed.
mmAddToBoth(self, thisField, thisVal, otherField, otherVal):
Creates an association in a M-M relationship. If the relationship
already exists, nothing changes. Otherwise, this will ensure that both values
exist in their respective tables, and will create the entry in the association
table.
Diff:
Modified: trunk/dabo/biz/dBizobj.py
===================================================================
--- trunk/dabo/biz/dBizobj.py 2011-06-12 13:31:07 UTC (rev 6625)
+++ trunk/dabo/biz/dBizobj.py 2011-06-16 12:37:40 UTC (rev 6626)
@@ -1803,6 +1803,15 @@
setValues = setFieldVals ## deprecate setValues in future version
+ def lookupPKWithAdd(self, field, val):
+ """Runs a lookup in the specified field for the desired value.
If
+ found, returns the PK for that record. If not found, a new
record is
+ created with the 'field' column populated with 'val', and the
new
+ record's PK is returned. None of this affects the current
dataset.
+ """
+ return self._CurrentCursor.lookupPKWithAdd(field, val)
+
+
def dataToXML(self):
"""
Returns XML representing the data set. If there are child
bizobjs,
@@ -2036,16 +2045,63 @@
return self._CurrentCursor.oldVal(fieldName, row)
+ def mmAssociateValue(self, otherField, otherVal):
+ """
+ Associates the value in the 'other' table of a M-M relationship
with the
+ current record in the bizobj. If that value doesn't exist in
the other
+ table, it is added.
+ """
+ self._CurrentCursor.mmAssociateValue(otherField, otherVal)
+
+
+ def mmDisssociateValue(self, otherField, otherVal):
+ """
+ Removes the association between the current record and the
specified value
+ in the 'other' table of a M-M relationship. If no such
association exists,
+ nothing happens.
+ """
+ self._CurrentCursor.mmDisssociateValue(otherField, otherVal)
+
+
+ def mmDisssociateAll(self):
+ """
+ Removes all associations between the current record and the
associated
+ M-M table.
+ """
+ self._CurrentCursor.mmDisssociateAll()
+
+
+ def mmSetFullAssociation(self, otherField, listOfValues):
+ """
+ Adds and/or removes association records so that the current
record in this
+ bizobj is associated with every item in listOfValues, and none
other.
+ """
+ self._CurrentCursor.mmSetFullAssociation(otherField,
listOfValues)
+
+
+ def mmAddToBoth(self, thisField, thisVal, otherField, otherVal):
+ """
+ Creates an association in a M-M relationship. If the
relationship
+ already exists, nothing changes. Otherwise, this will ensure
that
+ both values exist in their respective tables, and will create
the
+ entry in the association table.
+ """
+ return self._CurrentCursor.mmAddToBoth(thisField, thisVal,
otherField, otherVal)
+
+
########## SQL Builder interface section ##############
def addField(self, exp, alias=None):
"""Add a field to the field clause."""
return self._CurrentCursor.addField(exp, alias)
+
def addFrom(self, exp, alias=None):
"""
- Add a table to the sql statement. For joins, use
- the addJoin() method.
+ Add a table to the sql statement. For 1-M joins, use
+ the addJoin() method. For M-M joins, use the
+ createAssociation() method.
"""
return self._CurrentCursor.addFrom(exp, alias)
+
def addJoin(self, tbl, exp, joinType=None):
"""
Add SQL JOIN clause.
@@ -2055,44 +2111,71 @@
:param joinType: examples: "LEFT", "RIGHT", "INNER", "OUTER"
"""
return self._CurrentCursor.addJoin(tbl, exp, joinType)
+
+ def createAssociation(self, mmOtherTable, mmOtherPKCol, assocTable,
assocPKColThis,
+ assocPKColOther):
+ """
+ Create a many-to-many association.
+
+ :param mmOtherTable: the name of the table for the other half
of the MM relation
+ :param mmOtherPKCol: the name of the PK column in the
mmOtherTable
+ :param assocTable: the name of the table holding the
association between the two
+ :param assocPKColThis: the name of the column in the
association table for this PK
+ :param assocPKColOther: the name of the column in the
association table for the other PK
+ """
+ return self._CurrentCursor.createAssociation(mmOtherTable,
mmOtherPKCol, assocTable,
+ assocPKColThis, assocPKColOther)
+
def addGroupBy(self, exp):
"""Add an expression to the group-by clause."""
return self._CurrentCursor.addGroupBy(exp)
+
def addOrderBy(self, exp):
"""Add an expression to the order-by clause."""
return self._CurrentCursor.addOrderBy(exp)
+
def addWhere(self, exp, comp="and"):
"""Add a filter expression to the where clause."""
return self._CurrentCursor.addWhere(exp, comp=comp)
+
def getSQL(self):
"""Returns the SQL statement currently set in the backend."""
return self._CurrentCursor.getSQL()
+
def setFieldClause(self, clause):
"""Explicitly set the field clause. Replaces any existing field
settings."""
return self._CurrentCursor.setFieldClause(clause)
+
def setFromClause(self, clause):
"""Explicitly set the from clause. Replaces any existing from
settings."""
return self._CurrentCursor.setFromClause(clause)
+
def setJoinClause(self, clause):
"""Explicitly set the join clauses. Replaces any existing join
settings."""
return self._CurrentCursor.setJoinClause(clause)
+
def setGroupByClause(self, clause):
"""Explicitly set the group-by clause. Replaces any existing
group-by settings."""
return self._CurrentCursor.setGroupByClause(clause)
+
def getLimitClause(self):
"""Returns the current limit clause set in the backend."""
return self._CurrentCursor.getLimitClause()
+
def setLimitClause(self, clause):
"""Explicitly set the limit clause. Replaces any existing limit
settings."""
return self._CurrentCursor.setLimitClause(clause)
# For simplicity's sake, create aliases
setLimit, getLimit = setLimitClause, getLimitClause
+
def setOrderByClause(self, clause):
"""Explicitly set the order-by clause. Replaces any existing
order-by settings."""
return self._CurrentCursor.setOrderByClause(clause)
+
def setWhereClause(self, clause):
"""Explicitly set the where clause. Replaces any existing where
settings."""
return self._CurrentCursor.setWhereClause(clause)
+
def prepareWhere(self, clause):
"""
Calls the backend's pre-processing routine for improving
efficiency
@@ -2100,29 +2183,33 @@
nothing is done.
"""
return self._CurrentCursor.prepareWhere(clause)
+
def getFieldClause(self):
"""Returns the current field clause set in the backend."""
return self._CurrentCursor.getFieldClause()
+
def getFromClause(self):
"""Returns the current from clause set in the backend."""
return self._CurrentCursor.getFromClause()
+
def getJoinClause(self):
"""Returns the current join clause set in the backend."""
return self._CurrentCursor.getJoinClause()
+
def getWhereClause(self):
"""Returns the current where clause set in the backend."""
return self._CurrentCursor.getWhereClause()
+
def getGroupByClause(self):
"""Returns the current group-by clause set in the backend."""
return self._CurrentCursor.getGroupByClause()
+
def getOrderByClause(self):
"""Returns the current order-by clause set in the backend."""
return self._CurrentCursor.getOrderByClause()
########## END - SQL Builder interface section ##############
-
-
def _makeHookMethod(name, action, mainDoc=None, additionalDoc=None):
mode = name[:5]
if mode == "befor":
Modified: trunk/dabo/db/dCursorMixin.py
===================================================================
--- trunk/dabo/db/dCursorMixin.py 2011-06-12 13:31:07 UTC (rev 6625)
+++ trunk/dabo/db/dCursorMixin.py 2011-06-16 12:37:40 UTC (rev 6626)
@@ -29,6 +29,13 @@
self._initProperties()
if sql and isinstance(sql, basestring) and len(sql) > 0:
self.UserSQL = sql
+ # Attributes used for M-M relationships
+ # Temporary! until the refactoring
+ self._mmOtherTable = None
+ self._mmOtherPKCol = None
+ self._assocTable = None
+ self._assocPKColThis = None
+ self._assocPKColOther = None
#self.super()
#super(dCursorMixin, self).__init__()
@@ -475,6 +482,12 @@
ac.IsPrefCursor = self.IsPrefCursor
ac.KeyField = self.KeyField
ac.Table = self.Table
+ # Temporary! until the refactoring
+ ac._mmOtherTable = self._mmOtherTable
+ ac._mmOtherPKCol = self._mmOtherPKCol
+ ac._assocTable = self._assocTable
+ ac._assocPKColThis = self._assocPKColThis
+ ac._assocPKColOther = self._assocPKColOther
def requery(self, params=None):
@@ -1117,6 +1130,93 @@
return True
+ def lookupPKWithAdd(self, field, val, tbl=None):
+ """Runs a lookup in the specified field for the desired value.
If
+ found, returns the PK for that record. If not found, a record is
+ inserted into the table, with its 'field' column populated with
'val',
+ and the new PK is returned. None of this affects the current
dataset.
+ """
+ aux = self.AuxCursor
+ if tbl is None:
+ tbl = self.Table
+ sql = "select %s from %s where %s = ?" % (self.KeyField, tbl,
field)
+ aux.execute(sql, (val,))
+ if aux.RowCount:
+ return aux.getPK()
+ else:
+ # Add the record
+ sql = "insert into %s (%s) values (?)" % (tbl, field)
+ aux.execute(sql, (val,))
+ return aux.getLastInsertID()
+
+
+ def mmAssociateValue(self, otherField, otherVal):
+ """
+ Associates the value in the 'other' table of a M-M relationship
with the
+ current record. If that value doesn't exist in the other table,
it is added.
+ """
+ return self.mmAddToBoth(self.KeyField, self.getPK(),
otherField, otherVal)
+
+
+ def mmDisssociateValue(self, otherField, otherVal):
+ """
+ Removes the association between the current record and the
specified value
+ in the 'other' table of a M-M relationship. If no such
association exists,
+ nothing happens.
+ """
+ thisTable = self.Table
+ otherTable = self._mmOtherTable
+ thisPK = self.lookupPKWithAdd(thisField, thisVal, thisTable)
+ otherPK = self.lookupPKWithAdd(otherField, otherVal, otherTable)
+ aux = self.AuxCursor
+ sql = "delete from %s where %s = ? and %s = ?" %
(self._assocTable,
+ self._assocPKColThis, self._assocPKColOther)
+ aux.execute(sql, (thisPK, otherPK))
+
+
+ def mmDisssociateAll(self):
+ """
+ Removes all associations between the current record and the
associated
+ M-M table.
+ """
+ aux = self.AuxCursor
+ sql = "delete from %s where %s = ?" % (self._assocTable,
self._assocPKColThis)
+ aux.execute(sql, (self.getPK(),))
+
+
+ def mmSetFullAssociation(self, otherField, listOfValues):
+ """
+ Adds and/or removes association records so that the current
record
+ is associated with every item in listOfValues, and none other.
+ """
+ self.mmDisssociateAll()
+ keyField = self.KeyField
+ pk = self.getPK()
+ for val in listOfValues:
+ self.mmAddToBoth(keyField, pk, otherField, val)
+
+
+ def mmAddToBoth(self, thisField, thisVal, otherField, otherVal):
+ """
+ Creates an association in a M-M relationship. If the
relationship
+ already exists, nothing changes. Otherwise, this will ensure
that
+ both values exist in their respective tables, and will create
the
+ entry in the association table.
+ """
+ thisTable = self.Table
+ otherTable = self._mmOtherTable
+ thisPK = self.lookupPKWithAdd(thisField, thisVal, thisTable)
+ otherPK = self.lookupPKWithAdd(otherField, otherVal, otherTable)
+ aux = self.AuxCursor
+ sql = "select * from %s where %s = ? and %s = ?" %
(self._assocTable,
+ self._assocPKColThis, self._assocPKColOther)
+ aux.execute(sql, (thisPK, otherPK))
+ if not aux.RowCount:
+ sql = "insert into %s (%s, %s) values (?, ?)" %
(self._assocTable,
+ self._assocPKColThis,
self._assocPKColOther)
+ aux.execute(sql, (thisPK, otherPK))
+
+
def getRecordStatus(self, row=None, pk=None):
"""
Returns a dictionary containing an element for each changed
@@ -2318,7 +2418,28 @@
return self.sqlManager._joinClause
+ def createAssociation(self, mmOtherTable, mmOtherPKCol, assocTable,
assocPKColThis, assocPKColOther):
+ """Create a many-to-many association."""
+ # Save locally
+ # Temporary! until the refactoring
+ self._mmOtherTable = mmOtherTable
+ self._mmOtherPKCol = mmOtherPKCol
+ self._assocTable = assocTable
+ self._assocPKColThis = assocPKColThis
+ self._assocPKColOther = assocPKColOther
+ if self.sqlManager.BackendObject:
+ thisJoin = "%s.%s = %s.%s" % (self.Table,
self.KeyField, assocTable, assocPKColThis)
+ otherJoin = "%s.%s = %s.%s" % (mmOtherTable,
mmOtherPKCol, assocTable, assocPKColOther)
+ self.sqlManager._joinClause =
self.sqlManager.BackendObject.addJoin(assocTable,
+ thisJoin, self.sqlManager._joinClause,
"LEFT",
+ autoQuote=self.AutoQuoteNames)
+ self.sqlManager._joinClause =
self.sqlManager.BackendObject.addJoin(mmOtherTable,
+ otherJoin, self.sqlManager._joinClause,
"LEFT",
+ autoQuote=self.AutoQuoteNames)
+ return self.sqlManager._joinClause
+
+
def getWhereClause(self):
"""Get the where clause of the sql statement."""
return self.sqlManager._whereClause
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev
Searchable Archives: http://leafe.com/archives/search/dabo-dev
This message:
http://leafe.com/archives/byMID/[email protected]
