sqlobject-discuss

RE: [SQLObject] API Redesign for 0.6

[Ian S. <Ian...@et...>]

>Ian Bicking wrote on Monday, January 19, 2004 10:38 PM :
>By using descriptors, introspection should become a bit easier -- or
>at least more uniform with respect to other new-style classes.
>Various class-wide indexes of columns will still be necessary, but
>these should be able to remain mostly private.

Sorry to pull out just this one part of your plan but its not clear to =
me how you do introspection on current table classes to say, find out =
what columns a table has. This would be useful for generating editing =
web-pages for SO Objects for instance. Being able to walk joins would =
also be helpful so that you could have master/detail relationships or =
populate combo-boxes of all possible values for a field.





-----Original Message-----
From: Ian Bicking [mailto:ia...@co...]
Sent: Monday, January 19, 2004 10:38 PM
To: sql...@li...
Subject: [SQLObject] API Redesign for 0.6


SQLObject 0.6
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

*A tentative plan, 20 Jan 2004*

Introduction
------------

During vacation I thought about some changes that I might like to make
to SQLObject.  Several of these change the API, but not too
drastically, and I think they change the API for the better.  And we'd
not at 1.0 yet, changes are still allowed!  Here's my ideas...

Editing Context
---------------

Taken from Modeling, the "editing context" is essentially a
transaction, though it also encompasses some other features.
Typically it is used to distinguish between separate contexts in a
multi-threaded program.

This is intended to separate several distinct concepts:

* The database backend (MySQL, PostgreSQL, etc), coupled with the
  driver (MySQLdb, psycopg, etc).  (Should the driver be part of the
  connection parameters?)
* The connection parameters.  Typically these are the server host,
  username, and password, but they could also be a filename or other
  path.  Perhaps this could be represented with a URI, ala PEAK, but
  I also dislike taking structured data and destructuring it (i.e.,
  packing it into a string).  OTOH, URLs are structured, even if they
  require some parsing.  Serialization of URLs is free and highly
  transparent.  Python syntax is well structured and
  *programmatically* considerably more transparent (in a robust
  fashion), but also programmatically fairly read-only (because it is
  embedded in the structure of Python source code).  We can also have
  both.
* The database transactional context.
* The application transactional context (preferably these two would
  be seemless, but they still represent somewhat distinct entities,
  and a portability layer might be nice).  The application's
  transactional context may include other transactions -- e.g.,
  multiple databases, a ZODB transaction, etc.
* The cache policy.  There are many different kinds of caches
  potentially involved, include write batching, and per-object and
  per-table caches, connection pooling, and so on.
* Classes, which on the database side are typically tables.  (This
  proposal does not attempt to de-couple classes and tables)

Example::

    from SQLObject import EditingContext
    ec =3D EditingContext()
    # every editing context automatically picks up all the SQLObject
    # classes, all magic like.
    person =3D ec.Person.get(1) # by ID
    ec2 =3D EditingContext() # separate transaction
    person2 =3D ec.Person.get(1)
    assert person is not person2
    assert person.id =3D=3D person2.id
    assert person.fname =3D=3D 'Guy'
    person.fname =3D 'Gerald'
    assert person2.fname =3D=3D 'Guy'
    ec.commit() # SQL is not sent to server
    assert person2.fname =3D=3D 'Guy' # Doesn't see changes
    person2.fname =3D 'Norm'
    # raises exception if locking is turned on; overwrites if locking
    # is not turned on.  (Locking enabled on a per-class level)

I'm not at all sure about that example.  Mostly the confusing parts
relate to locking and when the database lookup occurs (and how late a
conflict exception may be raised).

Somewhere in here, process-level transactions might fit in.  That is,
even on a backend that doesn't support transactions, we can still
delay SQL statements until a commit/rollback is performed.  In turn,
we can create temporary "memory" objects, which is any object which
hasn't been committed to the database in any way.  To do this we'll
need sequences -- to preallocate IDs -- which MySQL and SQLite don't
really provide :(

Nested transactions...?  Maybe they'd fall out of this fairly easily,
especially if we define a global context, with global caches etc.,
then further levels of context will come for free.

We still need to think about an auto-commit mode.  Maybe the global
context would be auto-commit.

Caching
-------

Really doing transactions right means making caching significantly
more complex.  If the cache is purely transaction-specific, then we'll
really be limiting the effectiveness of the cache.  With that in mind,
a copy-on-write style of object is really called for -- when you fetch
an object in a transaction, you can use the globally cached instance
until you write to the object.

Really this isn't copy-on-write, it's more like a proxy object.  Until
the object is changed, it can delegate all its columns to its global
object for which it is a proxy.  Of course, traversal via foreign keys
or joins must also return proxied objects.  As the object is changed
-- perhaps on a column-by-column basis, or as a whole on the first
change -- the object takes on the personality of a full SQLObject
instance.

When the transaction is committed, this transactional object copies
itself to the global object, and becomes a full proxy.  These
transactional caches themselves should be pooled -- so that when
another transaction comes along you have a potentially useful set of
proxy objects already created for you.  This is a common use case for
web applications, which have lots of short transactions, which are
often very repetitive.

In addition to this, there should be more cache control.  This means
explicit ways to control things like:

1. Caching of instances:
   + Application/process-global definition.
   + Database-level definition.
   + Transaction/EditingContext-level definition.
   + Class-level definition.
2. Caching of columns:
   + Class-level.
3. Cache sweep frequency:
   + Application/process-global.
   + Database-level.
   + Class-level.
   + Doesn't need to be as complete as 1; maybe on the class level you
     could only indicate that a certain class should not be sweeped.
   + Sweep during a fetch (e.g., every 100 fetches), by time or fetch
     frequency, or sweep with an explicit call (e.g., to do sweeps in
     a separate thread).
4. Cache sweep policy:
   + Maximum age.
   + Least-recently-used (actually, least-recently-fetched).
   + Random (the current policy).
   + Multi-level (randomly move objects to a lower-priority cache,
     raise level when the object is fetched again).
   + Target cache size (keep trimming until the cache is small
     enough).
   + Simple policy (if enough objects qualify, cache can be of any
     size).
   + Percentage culling (e.g., kill 33% of objects for each sweep;
     this is the current policy).
5. Batching of updates (whether updates should immediately go to the
   database, or whether it would be batched until a commit or other
   signal).
6. Natural expiring of objects.  Even if an object must persist
   because there are still references, we could expire it so that
   future accesses re-query the database.  To avoid stale data.

Expose some methods of the cache, like getting all objects currently
in memory.  These would probably be exposed on a class level, e.g.,
all the Addresses currently in memory via
``Address.cache.current()`` or something.  What about when there's a
cached instance in the parent context, but not in the present
transaction?

Columns as Descriptors
----------------------

Each column will become a descriptor.  That is, ``Col`` and subclasses
will return an object with ``__get__`` and ``__set__`` methods.  The
metaclass will not itself generate methods.

A metaclass will still be used so that the descriptor can be tied to
its name, e.g., that with ``fname =3D StringCol()``, the resultant
descriptor will know that it is bound to ``fname``.

By using descriptors, introspection should become a bit easier -- or
at least more uniform with respect to other new-style classes.
Various class-wide indexes of columns will still be necessary, but
these should be able to remain mostly private.

To customize getters or setters (which you currently do by defining a
``_get_columnName`` or ``_set_columnName`` method), you will pass
arguments to the ``Col`` object, like::

    def _get_name(self, dbGetter):
        return dbGetter().strip()

    name =3D StringCol(getter=3D_get_name)

This gets rid of ``_SO_get_columnName`` as well.  We can
transitionally add something to the metaclass to signal an error if a
spurious ``_get_columnName`` method is sitting around.

Construction and Fetching
-------------------------

Currently you fetch an object with class instantiation, e.g.,
``Address(1)``.  This may or may not create a new instance, and does
not create a table row.  If you want to create a table row, you do
something like ``Address.new(city=3D'New York', ...)``.  This is
somewhat in contrast to normal Python, where class instantiation
(calling a class) will create a new object, while objects are fetched
otherwise (with no particular standard interface).

To make SQLObject classes more normal in this case, ``new`` will
become ``__init__`` (more or less), and classes will have a ``get``
method that gets an already-existant row.  E.g., ``Address.get(1)``
vs. ``Address(city=3D'New York', ...)``.  This is perhaps the most
significant change in SQLObject usage.  Because of the different
signatures, if you forget to make a change someplace you will get an
immediate exception, so updating code should not be too hard.

Extra Table Information
-----------------------

People have increasingly used SQLObject to create tables, and while it
can make a significant number of schemas, there are several extensions
of table generation that people occasionally want.  Since these occur
later in development, it would be convenient if SQLObject could grow
as the complexity of the programs using it grow.  Some of these
extensions are:

* Table name (``_table``).
* Table type for MySQL (e.g., MyISAM vs. InnoDB).
* Multi-column unique constraints. (Other constraints?)
* Indexes.  (Function or multi-column indexes?)
* Primary key type.  (Primary key generation?)
* Primary key sequence names (for Postgres, Firebird, Oracle, etc).
* Multi-column primary keys.
* Naming scheme.
* Permissions.
* Locking (e.g., optimistic locking).
* Inheritance (see Daniel Savard's recent patch).
* Anything else?

Some of these may be globally defined, or defined for an entire
database.  For example, typically you'll want to use a common MySQL
table type for your entire database, even though its defined on a
per-table basis.  And while MySQL allows global permission
declarations, Postgres does not and requires tedious repetitions of
the permissions for each table -- so while it's applied on a per-table
basis, it's likely that (at least to some degree) a per-database
declaration is called for.  Naming schemes are also usually
database-wide.

As these accumulate -- and by partitioning this list differently, the
list could be even longer -- it's messy to do these all as special
class variables (``_idName``, etc).  It also makes the class logic and
its database implementation details difficult to distinguish.  Some
of these can be handled elegantly like ``id =3D StringCol()`` or ``id
=3D ("fname", "lname")``.  But the others perhaps should be put into a
single instance variable, perhaps itself a class::

    class Address(SQLObject):
        class SQLMeta:
            mysqlType =3D 'InnoDB'
            naming =3D Underscore
            permission =3D {'bob': ['select', 'insert'],
                          'joe': ['select', 'insert', 'update'],
                          'public': ['select']}
        street =3D StringCol()
        ....

The metadata is found by its name (``SQLMeta``), and is simply a
container.  The class syntax is easier to write and read than a
dictionary-like syntax.  Or, it could be a proper class/instance and
provide a partitioned way to handle introspection.  E.g.,
``Address.SQLMeta.permission.get('bob')`` or
``Address.SQLMeta.columns``.  In this case values that weren't
overridden would be calculated from defaults (like the default naming
scheme and so on).=20

I'm not at all certain about how this should look, or if there are
other things that should go into the class-meta-data object.

Joins, Foreign Keys
-------------------

First, the poorly-named ``MultipleJoin`` and ``RelatedJoin`` (which
are rather ambiguous) will be renamed ``ManyToOneJoin`` and
``ManyToManyJoin``.  ``OneToOneJoin`` will also be added, while
``ForeignKey`` remains the related column type.  (Many2Many?
Many2many?  many2many?)

ForeignKey will be driven by a special validator/converter. (But will
this make ID access more difficult?)

Joins will return smart objects which can be iterated across.  These
smart objects will be related to ``SelectResults``, and allow the
same features like ordering.  In both cases, an option to retrieve
IDs instead of objects will be allowed.

These smarter objects will allow, in the case of ManyToManyJoin,
``Set`` like operations to relate (or unrelate) objects.  For
ManyToOneJoin the list/set operations are not really appropriate,
because they would reassign the relation, not just add or remove
relations.

It would be nice to make the Join protocol more explicit and public,
so other kinds of joins (e.g., three-way) could be more accessible.





-------------------------------------------------------
The SF.Net email is sponsored by EclipseCon 2004
Premiere Conference on Open Tools Development and Integration
See the breadth of Eclipse activity. February 3-5 in Anaheim, CA.
http://www.eclipsecon.org/osdn
_______________________________________________
sqlobject-discuss mailing list
sql...@li...
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

Re: [SQLObject] API Redesign for 0.6

[Ian B. <ia...@co...>]

Brad Bollenbach wrote:
> [Gah, very annoying that I can't reply on list. I'll try to get in touc=
h=20
> with somebody at SourceForge today to figure out what's going wrong her=
e.]
>=20
> Le mardi, 20 jan 2004, =E0 12:23 Canada/Eastern, Ian Bicking a =E9crit =
:
>> Sidnei da Silva wrote:
> [snip]
>>> * Enforcing constraints in python. Brad B. was chatting to me on irc
>>> yesterday and we came to agree on a api. He's writing a proposal (wit=
h
>>> a patch) and its going to present it soon. Basically, when you create
>>> a column you would provide a callable object as a keyword 'constraint=
'
>>> parameter. This constraint would then be used to enforce some
>>> restrictions.
>>> def foo_constraint(obj, name, value, values=3DNone):
>>>     # name is the column name
>>>     # value is the value to be set for this column
>>>     # values is a dict of the values to be set for other columns
>>>     # in the case you are creating an object or modifying more than
>>>     # one column at a time
>>>     # returns True or False
>>> age =3D IntCol(constraint=3Dfoo_constraint)
>>> class Col:
>>>   def __init__(self, name, constraint):
>>>       self.name =3D name
>>>       self.constraint =3D constraint
>>>   def __set__(self, obj, value):
>>>       if not self.constraint(obj, name, value):
>>>          raise ValueError, value
>>>       # Set the value otherwise
>>
>>
>> We already have Python constraints available through the=20
>> validator/converter interface, which I hope to fill out some more, and=
=20
>> provide some more documentation and examples.
>=20
>=20
> These constraints are only useful in trivial cases though. I have at=20
> least one specific case where I need to cross-reference column values i=
n=20
> the object, which may currently be set, or about to be changed to a new=
=20
> value. So, there are other parameters that must be supplied to the=20
> callback.

Well, what we need is a schema-level/instance level validation.  The=20
validator interface allows for this, but SQLObject doesn't currently=20
call a validator for the entire instance (so there would have to be some=20
changes).

So, if DBI has the values: new_value, target_object, name_of_column,=20
all_new_values, then the validator would look something like:

class MyValidator(Validator):
     def validate(self, fields, state):
         # we don't know the new value or name_of_column, which doesn't
         # really apply in this case
         target_object =3D state.soObject
         all_new_values =3D fields

Through state.soObject you can check a single column for consistency=20
with other columns, but in the case of a .set() call you won't see all=20
of the new values; in that case you may want to have symmetric=20
validators, so if A and B are dependent, then when A is changed it=20
checks B, and when B is changed it checks A.  Or use an instance=20
validator, which should

So, somewhere in .set() (or probably a new method, called by both .set()=20
and ._SO_setValue()) we'd check any instance validators, probably being=20
more careful that they don't see the object while it's in the middle of=20
having values set (i.e., convert and collect all the values, then set=20
them all at once).

I want to use validators more heavily, and have them translate into=20
database-side constraints as well.  So, for instance, ForeignKey would=20
become a validator/converter, and would also create the "REFERENCES ..."=20
portion of the SQL.  An example of an instance validator might be a=20
multi-column unique constraint, which again could create the necessary SQ=
L.

> Essentially, I need an interface like Perl's Class::DBI:
>=20
> http://search.cpan.org/~tmtm/Class-DBI-0.95/lib/Class/DBI.pm#CONSTRAINT=
S
>=20
> Class::DBI is excellent, but I have little knowledge of its internals,=20
> and so it may suffer from the same performance problems inherent in=20
> SQLObject, but it's definitely a project every SQLObject developer=20
> should be well aware of for a 0.6 redesign, because we might as well do=
=20
> what everybody else does and steal ideas and improve upon them.

DBI does seem like a good system.  Maybe because (besides being in=20
Perl), it actually reminds me a lot of SQLObject ;)  Though SQLObject=20
actually seems to be significantly larger in scope than DBI, which=20
doesn't seem to address transactions or caching in any way.  This seems=20
particularly significant with respect to transactions, as transactions=20
are one of the things 0.6 tries to resolve more cleanly, and it's not a=20
trivial addition.

> In other news, the patch is necessarily on hold until we resolve the=20
> database backend versions vs. SQLObject issue. I've got tons of tests=20
> that have errors in them. The next thing that should be checked into th=
e=20
> repository is something that makes those tests pass, but that will=20
> depend on what the consensus is among the users about how to handle=20
> those versioning problems.

Re: [SQLObject] API Redesign for 0.6

[Ian B. <ia...@co...>]

Ian Sparks wrote:
>> Ian Bicking wrote on Monday, January 19, 2004 10:38 PM : By using
>> descriptors, introspection should become a bit easier -- or at
>> least more uniform with respect to other new-style classes. Various
>> class-wide indexes of columns will still be necessary, but these
>> should be able to remain mostly private.
> 
> Sorry to pull out just this one part of your plan but its not clear
> to me how you do introspection on current table classes to say, find
> out what columns a table has. This would be useful for generating
> editing web-pages for SO Objects for instance. Being able to walk
> joins would also be helpful so that you could have master/detail
> relationships or populate combo-boxes of all possible values for a
> field.

Well, right now you can look at _columns, which should have most of the 
stuff you'd want.  Ideally the SQLMeta object (in this redesign) would 
have a clear set of methods for doing introspection.

For this case, I think it's better for the object to introspect itself, 
and that you add a method to it like .fields() or something.  Of course, 
you still need some form of introspection, even if the object is 
introspecting itself.

I think for joins, you are really thinking of ForeignKey (or maybe 
RelatedJoin, I suppose), where you want to find all the possible objects 
that this object could point to.  In this case I think you want to look 
at the ForeignKey object (which should be available through _columns), 
and do something like 
SQLObject.findClass(ForeignKeyColObj.foreignKey).search() to get the 
possible options.

   Ian