Table Oriented Programming

[Michael Hobbs]

Hey, not too bad! I guess that demonstrates that converting an
arithmetic expression to SQL isn't impossible. However, there are a few
issues with the system that I haven't entirely resolved yet.

1) Name conflict. If DepartmentTable and EmployeeTable both have a
column named "name" and I have this statement:
   empDepts = (deptTbl * empTbl) / (deptTbl.dept == empTbl.dept)
How would you access the department name vs. the employee name in the
"empDepts" result? Some ideas that I've come up with:
1a) empDepts.deptTbl.name vs. empDepts.empTbl.name. This isn't so great
since you need to keep track of the names of the variables or tables
that were used to generate the result. It could also lead to some rather
deep nesting if empDepts is later used in another query.
1b) Throw an exception. This will force the developer to rename at least
one of the columns in the result. This solution is probably better of
the two, even though it isn't convenient for the developer.

2) No provision for GROUP BY. Even though summary information can be
calculated by the client just as easily as it can be calculated by the
DB engine, there are some calculations that are done *much* faster by
the DB engine if it has appropriate indexes. For example, this query can
be calculated fairly quickly by a DB server, no matter how many rows are
in the tables:
  SELECT department.dept, MAX(salary)
  FROM department, employee
  WHERE department.dept = employee.dept
  GROUP BY department.dept
I'm not sure how such a query might be represented arithmetically. Here
are a couple of ideas using ">>" to signify GROUP BY:
  (deptTbl * empTbl) / (deptTbl.dept = empTbl.dept) 
      >> deptTbl.dept % (deptTbl.dept, empTbl.salary.max)
  (deptTbl * empTbl) / (deptTbl.dept = empTbl.dept)
      % (deptTbl.dept, empTbl.salary) >> deptTbl.dept(identity, max)
The first statement specifies the grouping first and then states how the
columns should be extracted from the groupings. The second statement
specifies which columns to return first and then specifies how to
summarize the columns in a grouping. I think I like the second way
better, because it lets you decompose the statement easier. For example:
  deptSalaries = (deptTbl * empTbl) / (deptTbl.dept = empTbl.dept)
      % (deptTbl.dept, empTbl.salary)
  maxDeptSalaries = deptSalaries >> deptSalaries.dept(identity, max)
Each line in the above example makes sense. However, if we try to
decompose it like this, the result of the first line makes no sense
because it specifies a grouping but doesn't say how to summarize all of
the columns in the result:
  grpDeptSalaries = (deptTbl * empTbl) / (deptTbl.dept = empTbl.dept)
      >> deptTbl.dept
  maxDeptSalaries = grpDeptSalaries % (deptTbl.dept, empTbl.salary.max)


Just brainstorming,
- Michael Hobbs


> -----Original Message-----
> From: python-list-bounces+michael=hobbshouse.org at python.org
> [mailto:python-list-bounces+michael=hobbshouse.org at python.org] On
Behalf
> Of Yermat
> Sent: Friday, February 13, 2004 05:32
> To: python-list at python.org
> Subject: Re: Table Oriented Programming
> 
> 
> What about the following code ?
> I think there is not too much to make it work...
> 
> 
> """
> http://www.geocities.com/tablizer/top.htm
>
http://groups.google.fr/groups?q=Table+oriented++group:comp.lang.python.
*&
> hl=fr&lr=&ie=UTF-
> 8&group=comp.lang.python.*&selm=mailman.2.1076613028.31398.python-
> list%40python.org&rnum=1
> """
> 
> 
> class TOP(object):
>     def __div__(self,other):
>         return Selection(self,other)
> 
>     def __mul__(self,other):
>         return CartesianProduct(self,other)
> 
>     def __eq__(self,other):
>         return Equality(self,other)
> 
>     def __mod__(self,other):
>         return Projection(self,other)
> 
>     def execute(self,connexion):
>         self._cursor = connexion.cursor()
>         self._cursor.execute(ToSql().toSql(self))
> 
>     def __getitem__(self,item):
>         if not hasattr(self,'_result'):
>             self._result = self._cursor.fetchall()
>         return self._result[item]
> 
>     def __iter__(self):
>         return self._iter
> 
>     def _iter(self):
>         set = self._cursor.nextset()
>         while set:
>             yield set
>             set = self._cursor.nextset()
> 
> 
> class Table(TOP):
>     def __init__(self,name):
>         self._name = name
> 
>     def __getattr__(self,attr):
>         return Field(self,attr)
> 
>     def __str__(self):
>         return self._name
> 
> class Field(TOP):
>     def __init__(self,table,fieldName):
>         self._table = table
>         self._name = fieldName
> 
>     def __str__(self):
>         return "%s.%s" % (self._table,self._name)
> 
> class Equality(TOP):
>     def __init__(self,a,b):
>         self.a = a
>         self.b = b
> 
> class Selection(TOP):
>     def __init__(self,a,b):
>         self.a = a
>         self.b = b
> 
> class CartesianProduct(TOP):
>     def __init__(self,a,b):
>         self.a = a
>         self.b = b
> 
> class Projection(TOP):
>     def __init__(self,a,b):
>         self.a = a
>         self.b = b
> 
> 
> class ToSql:
>     def __init__(self):
>         pass
> 
>     def toSql(self,obj):
>         methodName = "toSql" + obj.__class__.__name__
>         if hasattr(self,methodName):
>             toSql = getattr(self,methodName)
>             return toSql(obj)
>         else:
>             #print methodName
>             return '"%s"' % str(obj)
> 
>     def toSqltuple(self,tuple):
>         return ",".join(map(lambda o: self.toSql(o),tuple))
> 
>     def toSqlTable(self,table):
>         return str(table)
> 
>     def toSqlField(self,field):
>         return str(field)
> 
>     def toSqlCartesianProduct(self,prod):
>         return "%s,%s" % (self.toSql(prod.a),self.toSql(prod.b))
> 
>     def toSqlEquality(self,eq):
>         return "%s == %s" % (self.toSql(eq.a),self.toSql(eq.b))
> 
>     def toSqlSelection(self,sel):
>         if isinstance(sel.a,Selection):
>             return "%s AND %s" % (self.toSql(sel.a),self.toSql(sel.b))
>         else:
>             return "%s WHERE %s" %
> (self.toSql(sel.a),self.toSql(sel.b))
> 
>     def toSqlProjection(self,proj):
>         if isinstance(proj.a,Projection):
>             return "?"
>         else:
>             return "SELECT %s FROM %s" % (self.toSql(proj.b),
> self.toSql(proj.a))
> 
> translator = ToSql()
> 
> departments = Table("DepartmentTable")
> employees = Table("EmployeeTable")
> 
> johnsDepartment = ( departments * employees) / (employees.dept ==
> departments.dept)  / ( employees.name == "John") % departments.name
> johnsDepartmentBis = ( departments * employees) / (employees.dept ==
> departments.dept)  / ( employees.name == "John") %
> (departments.name,employees.surname)
> 
> print translator.toSql(johnsDepartment)
> print translator.toSql(johnsDepartmentBis)
> 
> ##johnsDepartment.execute(myBddConnexion)
> ##print johnsDepartment[0]
> 
> ##johnsDepartmentBis.execute(myBddConnexion)
> ##for item in johnsDepartmentBis:
> ##    print item
> --
> http://mail.python.org/mailman/listinfo/python-list