Table Oriented Programming

[Michael Hobbs]

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

 

[John Roth]

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

It's an intriguing idea; I've had the occasional thought of
wanting an in-memory relational algebra implementation (which
is what he's actually specifying once you get him down off of
the hobby-horse he's riding.)

Put together a package and see whether it flies for real world
problems. That's the usual path for inclusion into Python - put a
pure Python package together, see if you can get people excited
about actually using it, and then propose it for inclusion into the
standard library.

John Roth

 

[Cameron Laird]

.
.
.

It's an intriguing idea; I've had the occasional thought of
wanting an in-memory relational algebra implementation (which
is what he's actually specifying once you get him down off of
the hobby-horse he's riding.)

Put together a package and see whether it flies for real world
problems. That's the usual path for inclusion into Python - put a
pure Python package together, see if you can get people excited
about actually using it, and then propose it for inclusion into the
standard library.

John Roth

Notable contributor Jean-Claude Wippler is just now releasing some
<URL: http://www.equi4.com/pipermail/starkit/2004-February/001829.html >
of his implementations in the area of relational algebra.

 

[Yermat]

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/[email protected]&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

 

[John Roth]

.
.
.

Notable contributor Jean-Claude Wippler is just now releasing some
<URL: http://www.equi4.com/pipermail/starkit/2004-February/001829.html >
of his implementations in the area of relational algebra.

It's in TCL. Sigh.

John Roth

 

[Brian Kelley]

It's in TCL. Sigh.

Be assured that Wippler targets every language he can. I can't give too
many details because I promised not to, but python will work on the
finished system and, in fact, will be a first-class language target.
I'm including Jean-Claude in this discussion, btw, incase he wants to
add anything.

Metakit itself is relational algebra based on columns, it just happens
to be more database like. I'm have been using it to perform most of the
rattcl operations mentioned above, I just have a thin wrapper to create
the table structure around metakit. Gordon McMillan even has support to
"wrap" any python sequence as a metakit view and viola - you have all
the relational algebra support you want. Set operations, ordering,
joins, selects, filters... I've been so impressed by the underlying
technology that I'm thinking about writing a small book/tutorial on
metakit-specific relational algebra.

On top of that, Wippler and I have a metakit-server project using the
metakit structure as a wire protocol to send full or partial database
information across a socket. This is language independent so you can
send databases to python/C++/java etc. You can think of it as a sort of
a cross-platform database marshaller. If any one is interested, gunniea
pigs are always welcome.

Metakit is incredibly powerful, by the way. I have successfully used
metakit to form cross-database sql queries using it as the relational
algebra engine to combine SQL queries from Oracle and Mysql for
instance. So if you want to dip your toes in relational algebra, start
playing with metakit, it is way more than a database.

Brian
Whitehead Institute for Biomedical Research