[SQLObject] Impressions so far

[Edmund L. <el...@in...>]

Ian,

I've been banging away at SQLObject all day, trying to jam a small 
portion of my data model into it, or else rewrite it so it would fit. 
Here are my thoughts so far, each worth $0.02, and in no particular order.

I have appended + and minus signs to indicate strength of feeling. More 
of either = stronger positive or negative reation. +- = no reaction.

1. Being able to autogenerate schema is nice, and this is how I've been 
using testing SQLObject (SO). ++

2. Support for multiple DBs, particularly PostgreSQL, is good. +++

3. There needs to be an object.delete() method that does not require you 
to supply the object ID. When not supplied, it deletes the current 
object. There is a bug in this right now. When you do 
object.destroySelf(), the corresponding row is deleted from the DB, but 
the instance data is not invalidated. +-

4. Being able to supply a callable object for the default value of a 
column is good. +++

5. Being able to override table name is important because when you have 
lots of tables floating around, you often want to control their names so 
that you group them together is a specific way when the table names are 
sorted. ++++

6. Need to have some way of specifying unique constraints for single and 
  groups of columns for auto schema generation. --- See (7) below.

7. I don't use enums at all. For table driven applications, I use 
primary keys coupled with foreign key constraints. This allows you to 
change the allowed values of a column by varying the contents of another 
table. This is one argument against using synthetic primary keys. But 
using synthetic primary keys is still OK so long as unique columns can 
be specified, and the foreign key constraint can take the name of a column.

8. There is no way to specify (at least not that I can see), outer 
joins. These are important. ---

9. Method of specifying joins seems a tad clumsy. Object Relational 
Membrane's method is cleaner.

10. There needs to be a way to specify a not null constraint.

11. As an extension of (7), it is clumsy to have to pass in an object 
(A) when creating another object (B) that has a foreign key constraint 
refering to a column in (A). Here's an example that assumes I can add 
unique (6), not null constraints (10), and name a column for a foreign 
key (7) to a column:

class Gender(SQLObject):
     _columns = [
         StringCol("name", unique=1, nullable=0)]

class Person(SQLObject):
     _columns = [
         StringCol("name"),
         StringCol("gender", nullable=0, foreignKey="Gender.name")]

With this schema, one should be able to do:

Gender.new(name="Male") # Done once
Gender.new(name="Female") # Done once
man = Person(name="Geoff", gender="Male")  # somewhere else in the code

If one has to pass in an instance of Gender, then it becomes very clumsy 
and slow, because you have to instantiate the object you want first. i.e.:

male = Gender.select(Gender.q.name=="Male")
man = Person(name="Geoff", gender=male)

A big yuk. Makes table driven applications awful.  ----

12. Similarly, if a class/table is just being used for integrity checks, 
returning an object rather than the referenced column within the row is 
clumsy. Assuming the schema in (11), one would like:

target = Person.select(Person.q.name=="Geoff")
print target.gender
"Male"

When an object is returned, one would have to know the name of the 
column and then dereference it:

target = Person.select(Person.q.name=="Geoff")
print target.gender.name
"Male"

13. Being able to add and drop columns at runtime is great. This removes 
the need to regenerate the tables in a live database. +++

14. Modeling (which seems rather hard to get into) seems to have very 
expressive ways to get complex joins. I haven't tried it at all, but 
have browsed the docs. Might be worth stealing some ideas from them. But 
gee, theyReallyLikeLongMethodNames! Or maybe I'm sensitive to them due 
to RSI...

...Edmund.