Xref: utzoo comp.object:3314 comp.lang.smalltalk:2897
Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!zaphod.mps.ohio-state.edu!mips!spool.mu.edu!uunet!mcsun!hp4nl!media03!pkr
From: pkr@media03.UUCP (Peter Kriens)
Newsgroups: comp.object,comp.lang.smalltalk
Subject: Objects versus SQL
Keywords: sql database object smalltalk
Message-ID: <2272@media03.UUCP>
Date: 23 Apr 91 08:57:17 GMT
Organization: Mediasystemen, Netherlands
Lines: 60


Objects vs SQL 

I have been working on a Smalltalk SQL interface. I found a working solution
but I am not thrilled. It seems very hard to link those two worlds
into one paradigm. 

The power of objects, cq havings direct links to other objects, seems hard
to translate into the power of tuples where links are made symbolically. 

I found that there are at least two  different kind of tables. One
table is a direct representation of an object, containing rows that are
a direct match for the instance variables. The other table links objects,
and are represented in Smalltalk as pointers to objects (mostly 
collections).

For the first table it is not very hard to find a good solution. I created
a SQLTable object that links a class to a table. Because you can ask the
instance variable names to a class and can access the variables through their
respective index, it is possible to build a select statement for a certain
object without having to specify special hard coded routines in that class. 
Any class is automatically converted into a select statement by the SQLTable
class.

To abstract this, I made a Record class which contain some service routines
like fields which gives the names of the instance variables and values which
gives the values in the same sequence as the previous fields.

e.g.
	Record				abstract class for database objects
		Client
		Order
		OrderLine

	SQLTable			links Smalltalk objects to a table

	Client	-> instance var info + values -> aSQLTable -> select statement
											 <table name>
	aClient <-  setting of values		  <<-          <- table 	

This seems to work and it is very easy to create new classes which are 
stored in the database. What I really like about this method is the
1:1 relationship instance variables : columns without having code
which knows about the semantics of a certain table. This makes it very easy to
write save and select routines.

Ok, this works. But now the second kind of table. This should link the
objects. For example I have a Client record and each client can follow many
classes (school!) and each class is attended by many pupils. (n:m). In
Smalltalk this is very easy to express by using object pointers and
collections. In SQL you can do it by having an extra table that has the
Client id and the class. I haven't yet found an elegant solution to this
problem yet. I do not want to hard codie the semantics of a certain table into
Smalltalk code.

Basically, my question is: How do other people solve the clash between
Smalltalk (or any object oriented language) and SQL? 

peter kriens
pkr@media01.uucp