dabo Commit
Revision 1512
Date: 2005-11-03 13:43:57 -0800 (Thu, 03 Nov 2005)
Author: ed
Changed:
U trunk/dabo/db/dCursorMixin.py
Log:
Implemented joins between two DataSets. Right now you can specify left, right
and inner joins; I still have to work on outer joins. I also want to add the
ability to alias a field name, and to automatically munge a duplicate field
name (i.e., if both DataSets have a field named 'city', the field in the
DataSet being joined will be changed to 'city_alias', where 'alias' is the
alias specified for that DataSet.
This is going to make programming with Dabo so much more powerful, with much of
the power that Fox brings: being able to get data from backend data stores, and
also being able to manipulate local cursors without having to hit the backend.
And by making the syntax as SQL-like as possible, it will be a no-brainer for
developers to use.
Diff:
Modified: trunk/dabo/db/dCursorMixin.py
===================================================================
--- trunk/dabo/db/dCursorMixin.py 2005-11-03 05:57:46 UTC (rev 1511)
+++ trunk/dabo/db/dCursorMixin.py 2005-11-03 21:43:57 UTC (rev 1512)
@@ -1760,3 +1760,126 @@
return DataSet(resultSet)
+ def join(self, target, sourceAlias, targetAlias, condition,
+ sourceFields=None, targetFields=None, where=None,
+ orderBy=None, joinType=None):
+ """This method joins the current DataSet and the target
+ DataSet, based on the specified condition. The 'joinType'
+ parameter will determine the type of join (inner, left, right,
full).
+ Where and orderBy will affect the result of the join, and so
they
+ should reference fields in the result set without alias
qualifiers.
+ """
+ if joinType is None:
+ joinType = "inner"
+ joinType = joinType.lower().strip()
+ if joinType == "outer":
+ # This is the same as 'left outer'
+ joinType = "left"
+ if "outer" in joinType.split():
+ tmp = joinType.split()
+ tmp.remove("outer")
+ joinType = tmp[0]
+
+ leftDS = self
+ rightDS = target
+ leftAlias = sourceAlias
+ rightAlias = targetAlias
+ leftFields = sourceFields
+ rightFields = targetFields
+ if joinType == "right":
+ # Same as left; we just need to reverse things
+ (leftDS, rightDS, leftAlias, rightAlias, leftFields,
+ rightFields) = (rightDS, leftDS,
rightAlias, leftAlias,
+ rightFields, leftFields)
+
+ # Parse the condition. It should have an '==' in it. If not,
+ # raise an error.
+ condList = condition.split("==")
+ if len(condList) == 1:
+ # No equality specified
+ errMsg = _("Bad join: no '==' in join condition: %s") %
condition
+ raise dException.QueryException, errMsg
+
+ leftCond = None
+ rightCond = None
+ leftPat = "(.*)(\\b%s\\b)(.*)" % leftAlias
+ rightPat = "(.*)(\\b%s\\b)(.*)" % rightAlias
+
+ mtch = re.match(leftPat, condList[0])
+ if mtch:
+ leftCond = condList[0].strip()
+ else:
+ mtch = re.match(leftPat, condList[1])
+ if mtch:
+ leftCond = condList[1].strip()
+ mtch = re.match(rightPat, condList[0])
+ if mtch:
+ rightCond = condList[0].strip()
+ else:
+ mtch = re.match(rightPat, condList[1])
+ if mtch:
+ rightCond = condList[1].strip()
+ condError = ""
+ if leftCond is None:
+ condError += _("No join condition specified for alias
'%s'") % leftAlias
+ if rightCond is None:
+ if condError:
+ condError += "; "
+ condError += _("No join condition specified for alias
'%s'") % rightAlias
+ if condError:
+ raise dException.QueryException, condError
+
+ # OK, we now know how to do the join. The plan is this:
+ # create an empty result list
+ # scan through all the left records
+ # if leftFields, run a select to get only those
fields.
+ # find all the matching right records using select
+ # if matches, update each with the left select
and add
+ # to the result.
+ # if no matches:
+ # if inner join:
+ # skip to next
+ # else:
+ # get dict.fromkeys() for right
select
+ # update left with fromkeys and
add to result
+ #
+ # We'll worry about full joins later.
+
+ resultSet = []
+ for leftRec in leftDS:
+ if leftFields:
+ leftSelect =
DataSet([leftRec]).select(fields=leftFields)[0]
+ else:
+ leftSelect = leftRec
+ tmpLeftCond = leftCond.replace(leftAlias, "leftRec")
+ tmpLeftCond = "%s['%s']" % tuple(tmpLeftCond.split("."))
+ leftVal = eval(tmpLeftCond)
+
+ if isinstance(leftVal, basestring):
+ leftVal = "'%s'" % leftVal
+ rightWhere = rightCond.replace(rightAlias + ".", "") +
"== %s" % leftVal
+ rightRecs = rightDS.select(fields=rightFields,
where=rightWhere)
+
+ if rightRecs:
+ for rightRec in rightRecs:
+ rightRec.update(leftSelect)
+ resultSet.append(rightRec)
+ else:
+ if not joinType == "inner":
+ rightKeys =
rightDS.select(fields=rightFields)[0].keys()
+
leftSelect.update(dict.fromkeys(rightKeys))
+ resultSet.append(leftSelect)
+
+ resultSet = DataSet(resultSet)
+ if where or orderBy:
+ resultSet = resultSet.select(where=where,
orderBy=orderBy)
+ return resultSet
+
+
+
+
+
+
+
+
+
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev
