Table Oriented Programming

[Yermat]

"Michael Hobbs" <michael at hobbshouse.org> wrote in message news:<mailman.2.1076613028.31398.python-list at python.org>...
> Yeah, yeah, another X-Oriented paradigm, but please hear me out.
> 
> I have recently been studying up on EJB's in order to extend my resume.
> I have looked at J2EE several times over the past years, but have never
> been impressed by it. Now that I've studied it in detail, I know why. A
> strongly typed language such as Java is poorly suited to model tables
> and relations. 
> 
> I got to thinking that a more dynamically typed language, such as
> Python, would be a more natural fit. I've always been fond of the
> table-oriented programming ideas described in
> http://www.geocities.com/tablizer/top.htm, so I'm thinking that if
> Python could be extended to handle table-oriented programming as
> seamlessly as it handles object-oriented programming, it could become a
> really valuable language for writing enterprise components, a la EJB.
> 
> Does anyone know if anyone has already walked this path?
> 
> I've given it a little thought myself, and here are some of my
> [incomplete] ideas:
> 
> - Tables would be first-class values, just as classes are.
> - A table may be stored completely in memory, or it may represent a
> table stored in a database engine.
> - A table may represent the result of a query.
> - Queries are performed using a sequence of operators, rather than
> relying on SQL.
> 
> Consider this example code:
>   departments = DepartmentTable
>   employees = EmployeeTable
>   johnsDepartment = (departments * employees) / 
>     (employees.dept == departments.dept) /
>     (employees.name == 'John') %
>     departments.name
>   print johnsDepartment[0].name
> 
> In the code above, the "*" operator performs a cartesian product, the
> "/" operator performs a selection, and the "%" operator performs a
> projection. If the DepartmentTable and the EmployeeTable are stored in a
> database engine, the expression above would generate a SQL query similar
> to "select department.name from (select * from department, employee
> where employee.dept = department.dept) where employee.name = 'John'". If
> the expression had been written "(departments * employees) /
> (employees.dept == departments.dept and employees.name == 'John') %
> departments.name", the resulting SQL would be similar to "select
> department.name from department, employee where employee.dept =
> department.dept and employee.name = 'John'", that is, without the nested
> SELECT.
> 
> Of course, the entire code above could be reduced to a single line:
>   print ((DepartmentTable * EmployeeTable) / (EmployeeTable.dept ==
> DepartmentTable.dept and EmployeeTable.name == 'John') %
> DepartmentTable.name)[0].name
> 
> Perhaps the list-comprehension syntax may be better suited than
> arithmetic operators. Like I said, my ideas are still a little
> incomplete.
> 
> Please forgive me if this has already been discussed to death,
> - Michael Hobbs



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