sqlobject-discuss — All things SQLObject-related

Re: [SQLObject] thoughts on structure and components

[Bud P. B. <bu...@si...>]

On 15 May 2003 10:45:20 -0500
Ian Bicking <ia...@co...> wrote:

> Declaring a connection for a class is easy.  Why is it a problem?

It is easy once you know.  When I first used it, I didn't understand
why it wouldn't work and found out that I need the connection first.
At least for me personally, decoupling connection and definition of
the physical representation makes it easier to use, flattens the
learning curve..  But I may be alone on this...

> The other way would be to make a table-driven columnsFromSchema that was
> attached to your connection, and let that create the columns for you.

If I understand this correctly, ie, that you store the "object schema"
that defines the mapping from python to SQL in tables, it comes very
close to how I do it.  Just that I am at a level where I represent the
mapping as python objects--but it would be possible to write them out
to a dbms and get them back into an object form..

cheers
--b

Re: [SQLObject] a third way of accessing attributes

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

On Thu, 2003-05-15 at 07:38, Bud P.Bruegger wrote:
> * Adding methods and properties (attributes) to a class at runtime 
>   (sans metaclasses).  

SQLObject does this in the class method SQLObject.addColumn.  The
metaclass simply searches _columns and the defined attributes to run
addColumn on class creation.

The metaclass does other things too, though I've tried to decrease its
work as I'm able.  Some things, like keeping some class variables from
being inherited, still need to be done in the metaclass.  The metaclass
is really just the equivalent of __init__ for the class -- it handles
the class instantiation, where the real __init__ (or __new__/_init)
handle instance instantiation.

  Ian

Re: [SQLObject] thoughts on structure and components

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

Just looking at your example below, doesn't this seem like a lot more
work than SQLObject?  What was two classes (Person and PhoneNumber),
becomes seven (Person, PhoneNumber, StorablePerson, StorablePhoneNumber,
a Person mapper, a PhoneNumber mapper, and a relation).

I like that SQLObject's UML diagram (which doesn't exist, but let us
imagine it) is a boring diagram, and that each class you use requires
one class definition.

On Tue, 2003-05-13 at 02:15, Bud P.Bruegger wrote:
> So assume your app has a class Person, a class PhoneNumber, and a
> 1:many relationship between them.  So there would be the following
> steps to make this storable:
> 
> * make StorablePerson and StorablePhoneNumber with multiple
>   inheritance from Person and Storable, and PhoneNumber and Storable,
>   respectively.  This would add methods to communicate with the
>   backend.  Not sure yet, how I will do this--but hope to have some
>   initial code in a couple of days.
> 
> * make a mapping objects that contains (aggregation) three major
>   subobjects:
>   - an ObjMap that basically represents the Person table
>   - an ObjMap that represents the PhoneNumber table
>   - a relationship object that defines the relationship between them
>   So a mapping has a set of "table definitions" and a set of
>   relationship definitions.  "TableDefinitions" consist of a set of
>   attributes and attributes of a set of columns (in most cases just
>   one).
> 
> * create a backend object by assembling a dbDriver, a dbConnection,
>   and the above mapping object.  The dbDriver takes information
>   expressed in terms of mapping entities to create SQL statements
>   (strings).  The connection sends these off...  The backend object
>   manages all this.  
> 
> * storablePerson and storablePhoenNumber objects know about the
>   backend object and ask it to be stored, updated, deleted.  The
>   backend first checks the class of the object and uses it to find the
>   according mapping (name match).  Mapping and Driver produce the SQL
>   with variables for values, as well as marshal the python values of
>   the object for use in sql statments.
> 
> * backend also provides finder methods that basically correspond to
>   select queries..
> 
> * backend shoudl also manage a cache and do some concurrancy
>   control...
> 
> Hope this clarifies my ideas a little.
> 
> best cheers
> --b
> 
> 
> On 12 May 2003 17:10:40 -0500
> Ian Bicking <ia...@co...> wrote:
> 
> > On Mon, 2003-05-12 at 10:39, Bud P.Bruegger wrote:
> > > > Now, if I'm reading it right, this would be the most significant
> > > > difference with SQLObject -- a mapping object is a separate from the
> > > > class.  But why?  
> > > 
> > > Ian, I made some progress and I can more clearly answer to the
> > > question above:
> > > 
> > > In this approach, relationships are instances managed by Mapping, not
> > > part of the "class" (of the storable application object).  This way,
> > > to keep object references from the relationship object to the
> > > contained (from, to..) "table objects" (that I call ObjMap).  I can
> > > also keep back-references from ObjMap to all the relations that
> > > reference it.  
> > > 
> > > This way, I avoid (at least it seems at the moment) all problems of
> > > circular dependencies, referencing to objects only by name, etc.  
> > 
> > I'm still not really clear what you're thinking.  Can you show how you
> > might define a class, using a mapping?
> > 
> >   Ian
> > 
> > 
> > 
> 
> 
> /-----------------------------------------------------------------
> | Bud P. Bruegger, Ph.D. 
> | Sistema (www.sistema.it)
> | Via U. Bassi, 54
> | 58100 Grosseto, Italy
> | +39-0564-411682 (voice and fax)
> \-----------------------------------------------------------------
> 

Re: [SQLObject] a third way of accessing attributes

[Bud P. B. <bu...@si...>]

On 15 May 2003 10:23:14 -0500
Nick <ni...@dd...> wrote:

> On Thu, 2003-05-15 at 10:19, Nick wrote:
> > On Thu, 2003-05-15 at 07:38, Bud P.Bruegger wrote:
> > > Some time ago, Nick and Ian (and others?) discussed about two ways of
> > > exposing SQL data through object attributes:
> > > 
> > > * metaclasses (current SQLObject) that set properties
> > > * __getattr__ and __setattr__
> > > 
> > > There seems to be a third way:
> > > 
> > > * Adding methods and properties (attributes) to a class at runtime 
> > >   (sans metaclasses).  
> > 
> > The problem with this approach is that you won't be able to
> > automatically resolve foreign keys at access time unless the method for
> > accessing foreign keys is only handled through methods, which both
> > __getattr__ and properties do.
> 
> Sorry, I forgot to say what my point was :)  My point is that it's more
> convenient to treat objects as persistent using that kind of model than
> have set() and get() functions a la C (java, etc.).  You're doing nearly
> the exact same thing as properties, so why not use them.

Hi Nick,

I think there must be a misunderstanding.  I _DO_ use properties, just
that I set them at runtime  (in backend.__init__) instead of from a
metaclass.  So for all that I'm concerned, I don't see a difference to
how the property behaves  (No matter at what point of time you added
it to the class, it is there...).

Maybe some more concrete example helps:

Assume I have application classes Person and Role.  My Backend
instance contains a Mapping instance contains ClassMaps for both 
Person and Role, as well as a Many2Many Relationship between Person
and Role.  

The Person and Role role classes don't know anything about
storage--they are written as if there was no persistence at all.

Now I create my Backend instance that runs its __init__.  For each
ClassMap in its Mapping instance (i.e., ['Person', 'Role']), it does
something along the following lines:

* add a class attribute called "backendInstance" that is the python
  object reference of the backend.  This is necessary to make sure
  application objects know who to talk to in order to get their
  persistence needs satisfied.   (this is where I am at the moment)

* add methods for controlling persistence.  I'm currently thinking of 
  - insert  (new in database)
  - update  (update values in database)
  - synch   (update valued from database)
  - del
  - as well as functions to control transactions.  I was thinking of
    wrapping the record-oriented methods of the DB-API with
    object-oriented ones...  Gotta think about that one...

* add properties to traverse relationships.  There seem to be two
  options here: lazy or automatic.  This can be controlled by the
  Relationship object in the Mapping.  I haven't looked into this
  much and probably I can rip off some of SQLObject's code here..

So in summary, the touch and feel (for application programmer) should
be very similar to SQLObject except that the application class does
not subclass any persistence related class and that mappings are
defined externally to the app class much rather than inside..

later
--b


/-----------------------------------------------------------------
| Bud P. Bruegger, Ph.D. 
| Sistema (www.sistema.it)
| Via U. Bassi, 54
| 58100 Grosseto, Italy
| +39-0564-411682 (voice and fax)
\-----------------------------------------------------------------

Re: [SQLObject] thoughts on structure and components

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

Comments on at least a couple of your thoughts..

On Fri, 2003-05-09 at 04:28, Bud P.Bruegger wrote:
> > > Storable:
> > >   A superclass that makes application objects storable.  
> > >   It adds methods that interact with the Backend in order to
> > >   - store
> > >   - update
> > >   - delete
> > >   the object
> > >   In addition, it provided methods to easily access related objects
> > 
> > I can see how this might be stylistically different from the SQLObject
> > class, but how is it otherwise different?  (Well, there's SQLObject and
> > MetaSQLObject, but those are tightly coupled, and I think justifiably
> > so).
> 
> Maybe this is not at all different from SQLObject???  Can I write a
> class and then make what I call a storable application class that
> inherits from both the business-logic-only class and SQLObject?

Certainly.

> > SQLObject is tightly coupled to DBConnection (i.e., they chat a lot,
> > there's no well defined interface between the two, and information
> > that's available between the two of them isn't necessarily otherwise
> > public).  I can imagine them being decoupled, but only after SQLObject
> > had a long time to mature.
> 
> I believe that my thoughts kind of split up DBConnection into several
> different entities (connection, dbdriver, and maybe also backend?).
> This way, I can for example define what I call mappings before opening
> an actual connection to the dbms.  I see this as a sort of decoupling
> of components..

I would expect mappings to be the equivalent of your SQLObject
subclasses, which can be made independently of a database connection, or
can use a simple connection like DBM when you are first writing things
out.

> > But maybe you are really thinking of a super-superclass, i.e., something
> > that SQLObject would inherit from, as well as other significantly
> > different kinds of storage mechanisms.
> 
> I don't think so, but I don't understand the super-super concept
> well...

By that I mean that the SQLObject class, i.e., SQLObject inherits from
Storable, and perhaps something ZODB-like would also inherit from
Storable.  Storable would implement a common interface for certain kinds
of introspection necessary for persistence.

> > BConnection is something of a misnomer -- DBDriver would probably be a
> > better name (especially for the poorly named DBMConnection, which
> > doesn't connect to anything).  Well, in the beginning DBConnection was
> > really just a primitive form of connection pool, but that part is
> > relatively small now.  There's nothing particularly important about the
> > way they are joined.  If they weren't, though, the interface would look
> > pretty much the same (since the type of connection you make implies
> > which DBDriver you would use).  Separating DBConnection from DBDriver
> > isn't a very significant change, since they are naturally coupled.
> 
> I separated the two since it makes it possible to define mappings
> before having the connection in place.  Trying to devide and conquer,
> I tried to separate the creation of SQL (strings) and actually
> communicating with the dmbs..

Declaring a connection for a class is easy.  Why is it a problem?

Perhaps you're thinking of things like dbName, which map the Python
columns to a particular schema.  That's reasonable, but then if you are
mapping to a legacy database then you probably know that from the
beginning.

> > Right now you could hack something together to do this by using a Style
> > subclass that fed off a mapping table (instead of using algorithms to
> > map names).
> 
> This surprises me.  I see style and mapping as reasonably independent
> things.  Style just defines some defaults (such as converting names
> form case sensitive to case insensitive) and if I chose an appropriate
> style, it saves me some typing.  

A hacked Style could handle changes to your dbNames, that's about it. 
Though when I think of a mapping, that's a large part.

The other way would be to make a table-driven columnsFromSchema that was
attached to your connection, and let that create the columns for you.

  Ian

Re: [SQLObject] a third way of accessing attributes

[Nick <ni...@dd...>]

On Thu, 2003-05-15 at 10:19, Nick wrote:
> On Thu, 2003-05-15 at 07:38, Bud P.Bruegger wrote:
> > Some time ago, Nick and Ian (and others?) discussed about two ways of
> > exposing SQL data through object attributes:
> > 
> > * metaclasses (current SQLObject) that set properties
> > * __getattr__ and __setattr__
> > 
> > There seems to be a third way:
> > 
> > * Adding methods and properties (attributes) to a class at runtime 
> >   (sans metaclasses).  
> 
> The problem with this approach is that you won't be able to
> automatically resolve foreign keys at access time unless the method for
> accessing foreign keys is only handled through methods, which both
> __getattr__ and properties do.

Sorry, I forgot to say what my point was :)  My point is that it's more
convenient to treat objects as persistent using that kind of model than
have set() and get() functions a la C (java, etc.).  You're doing nearly
the exact same thing as properties, so why not use them.

Nick

Re: [SQLObject] a third way of accessing attributes

[Nick <ni...@dd...>]

On Thu, 2003-05-15 at 07:38, Bud P.Bruegger wrote:
> Some time ago, Nick and Ian (and others?) discussed about two ways of
> exposing SQL data through object attributes:
> 
> * metaclasses (current SQLObject) that set properties
> * __getattr__ and __setattr__
> 
> There seems to be a third way:
> 
> * Adding methods and properties (attributes) to a class at runtime 
>   (sans metaclasses).  

The problem with this approach is that you won't be able to
automatically resolve foreign keys at access time unless the method for
accessing foreign keys is only handled through methods, which both
__getattr__ and properties do.

Nick

[SQLObject] a third way of accessing attributes

[Bud P. B. <bu...@si...>]

Some time ago, Nick and Ian (and others?) discussed about two ways of
exposing SQL data through object attributes:

* metaclasses (current SQLObject) that set properties
* __getattr__ and __setattr__

There seems to be a third way:

* Adding methods and properties (attributes) to a class at runtime 
  (sans metaclasses).  

The advantage I see (yet to be verified) is that this approach allows
a very weak coupling of application objects with the backend
implementation:  

There doesn't even seem to be the need to inherit from some "storable"
class or similar (SQLObject).  Instead, when the Backend is
initialized, it simply loops through all ObjMaps in the Mapping and
adds attributes and methods accordingly.  I kind of like this...

For who is interested in more details, I attach a little test that
demonstrates this.

--bud


/-----------------------------------------------------------------
| Bud P. Bruegger, Ph.D. 
| Sistema (www.sistema.it)
| Via U. Bassi, 54
| 58100 Grosseto, Italy
| +39-0564-411682 (voice and fax)
\-----------------------------------------------------------------

Re: [SQLObject] two postgres questions

[Bud P. B. <bu...@si...>]

Dear Edmund,

many thanks for the help.  Below some comments..

On Wed, 14 May 2003 14:19:23 -0400
Edmund Lian <el...@in...> wrote:

> Bud P.Bruegger wrote:
> 
> > <quote src=http://www.phpbuilder.com/columns/tim20000705.php3:> 
> > At this point, I ran into another small issue with Postgres - its
> > "serial" data type (the equivalent of MySQL's auto_increment) creates
> > a "sequence" which does not get dropped when its parent table is
> > dropped. So if you try to re-create the table, you'll get a name
> > conflict for this sequence. A lot of new users would be confused by
> > this, so Postgres loses a couple points for that.
> 
> This depends on which version of PostgreSQL you are using. The latest 
> versions drop this automatically.

So you seem to confirm that SQLObject is fine as long as it runs with
Postgres7.3 or higher...  

> > Also, MySQL is
> > "smart" enough to increment its auto_increment value when you import
> > data, whereas Postgres' sequence does not get reset when you import
> > data, causing all new inserts to fail.  
> 
> This depends on how you import the data. Are you using the copy command? 
> If so, then rather than importing every column of a table that has an 
> autosequence, you should just import the columns you really want. Leave 
> the autoincrementing column out of the import statement, and it will be 
> autoincremented as you expect.

I actually just quoted the whole paragraph, I'm not currently in need
for this.  But thanks for clarifying the issue.  Surely I'll use it
one day soon.

> > I was thinking of having IDs that are unique across tables--something
> > useful when thinking of implementing inheritance etc.  So I tried to
> > create a global sequence and have the id columns of several tables use
> > it for their default values (DEFAULT nextval('global_oid_seq')).  But
> > that seems to fail.  Can there be only a single column (of a single
> > table) associated with a sequence???
> 
> You should be using triggers for this. I do this now and then, no problems.

My test didn't work since I used the type BIGSERIAL for the id
columns and set the DEFAULT to nextval of my global seqence.  This
way, BIGSERIAL implied a local sequence and default value and I
explicitly also set a global one:  two values assigned as defaults and
an obvious problem.    When I used BIGINT instead as type, it all
worked as expected...  [I did other funny things yesterday to make my
own life harder--I'm hoping for a better day today..]

many thanks

-b

[SQLObject] Backreference a 1-to-1join

[J-P L. <sql...@si...>]

Hi All,

Is it possible to backreference a 1-to-1 join using the _joins 
attribute?  The documentation only mentions one-to-many and 
many-to-many.  Foreign keys enable a one way reference, but I'd like to 
get the reverse implemented as well.

Thanks!

[SQLObject] SQLObject at EuroPython

[Brad B. <br...@bb...>]

My proposal to speak about SQLObject at EuroPython has been accepted.

A summary of the talk (and an embarrassingly long speaker bio :) can
be found here:

    http://www.europython.org/sessions/talks/talk?itemId=03108161107

Hope to see some SQLObject geeks there! :)

-- 
Brad Bollenbach
BBnet.ca

Re: [SQLObject] two postgres questions

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

Bud P.Bruegger wrote:

> <quote src=http://www.phpbuilder.com/columns/tim20000705.php3:> 
> At this point, I ran into another small issue with Postgres - its
> "serial" data type (the equivalent of MySQL's auto_increment) creates
> a "sequence" which does not get dropped when its parent table is
> dropped. So if you try to re-create the table, you'll get a name
> conflict for this sequence. A lot of new users would be confused by
> this, so Postgres loses a couple points for that.

This depends on which version of PostgreSQL you are using. The latest 
versions drop this automatically.


> Also, MySQL is
> "smart" enough to increment its auto_increment value when you import
> data, whereas Postgres' sequence does not get reset when you import
> data, causing all new inserts to fail.  

This depends on how you import the data. Are you using the copy command? 
If so, then rather than importing every column of a table that has an 
autosequence, you should just import the columns you really want. Leave 
the autoincrementing column out of the import statement, and it will be 
autoincremented as you expect.


> I was thinking of having IDs that are unique across tables--something
> useful when thinking of implementing inheritance etc.  So I tried to
> create a global sequence and have the id columns of several tables use
> it for their default values (DEFAULT nextval('global_oid_seq')).  But
> that seems to fail.  Can there be only a single column (of a single
> table) associated with a sequence???

You should be using triggers for this. I do this now and then, no problems.

...Edmund.

Re: [SQLObject] Update: Postgres drops sequence when table is dropped..

[Bud P. B. <bu...@si...>]

And the answer to my second question:

A global (db-wide) SEQUENCE is possible.  I made a mistake and didn't
give the column type as bigint...

--b


On Wed, 14 May 2003 18:47:56 +0200
"Bud P. Bruegger" <bu...@si...> wrote:

> On the first issue, I just verified and PostgreSQL 7.3 also drops the
> sequence when dropping the table.  
> 
> On Wed, 14 May 2003 18:43:17 +0200
> "Bud P. Bruegger" <bu...@si...> wrote:
> 
> > I would like to bring up two Postgres questions/issues:
> > 
> > Q1:  Table Dropping and left-over sequences:
> > --------------------------------------------
> > 
> > I found the following 
> > 
> > <quote src=http://www.phpbuilder.com/columns/tim20000705.php3:> 
> > At this point, I ran into another small issue with Postgres - its
> > "serial" data type (the equivalent of MySQL's auto_increment) creates
> > a "sequence" which does not get dropped when its parent table is
> > dropped. So if you try to re-create the table, you'll get a name
> > conflict for this sequence. A lot of new users would be confused by
> > this, so Postgres loses a couple points for that. Also, MySQL is
> > "smart" enough to increment its auto_increment value when you import
> > data, whereas Postgres' sequence does not get reset when you import
> > data, causing all new inserts to fail.  
> > </quote>
> > 
> > Does that mean that the dropTable method of SQLObject should also drop
> > the sequences?
> > 
> > Q2: Global sequence possible?
> > -----------------------------
> > 
> > I was thinking of having IDs that are unique across tables--something
> > useful when thinking of implementing inheritance etc.  So I tried to
> > create a global sequence and have the id columns of several tables use
> > it for their default values (DEFAULT nextval('global_oid_seq')).  But
> > that seems to fail.  Can there be only a single column (of a single
> > table) associated with a sequence???
> > 
> > many thanks
> > 
> > --b
> > 
> > /-----------------------------------------------------------------
> > | Bud P. Bruegger, Ph.D. 
> > | Sistema (www.sistema.it)
> > | Via U. Bassi, 54
> > | 58100 Grosseto, Italy
> > | +39-0564-411682 (voice and fax)
> > \-----------------------------------------------------------------
> > 
> > 
> > -------------------------------------------------------
> > Enterprise Linux Forum Conference & Expo, June 4-6, 2003, Santa Clara
> > The only event dedicated to issues related to Linux enterprise solutions
> > www.enterpriselinuxforum.com
> > 
> > _______________________________________________
> > sqlobject-discuss mailing list
> > sql...@li...
> > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
> > 
> 
> 
> /-----------------------------------------------------------------
> | Bud P. Bruegger, Ph.D. 
> | Sistema (www.sistema.it)
> | Via U. Bassi, 54
> | 58100 Grosseto, Italy
> | +39-0564-411682 (voice and fax)
> \-----------------------------------------------------------------
> 
> 
> -------------------------------------------------------
> Enterprise Linux Forum Conference & Expo, June 4-6, 2003, Santa Clara
> The only event dedicated to issues related to Linux enterprise solutions
> www.enterpriselinuxforum.com
> 
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
> 


/-----------------------------------------------------------------
| Bud P. Bruegger, Ph.D. 
| Sistema (www.sistema.it)
| Via U. Bassi, 54
| 58100 Grosseto, Italy
| +39-0564-411682 (voice and fax)
\-----------------------------------------------------------------

[SQLObject] Update: Postgres drops sequence when table is dropped..

[Bud P. B. <bu...@si...>]

On the first issue, I just verified and PostgreSQL 7.3 also drops the
sequence when dropping the table.  

On Wed, 14 May 2003 18:43:17 +0200
"Bud P. Bruegger" <bu...@si...> wrote:

> I would like to bring up two Postgres questions/issues:
> 
> Q1:  Table Dropping and left-over sequences:
> --------------------------------------------
> 
> I found the following 
> 
> <quote src=http://www.phpbuilder.com/columns/tim20000705.php3:> 
> At this point, I ran into another small issue with Postgres - its
> "serial" data type (the equivalent of MySQL's auto_increment) creates
> a "sequence" which does not get dropped when its parent table is
> dropped. So if you try to re-create the table, you'll get a name
> conflict for this sequence. A lot of new users would be confused by
> this, so Postgres loses a couple points for that. Also, MySQL is
> "smart" enough to increment its auto_increment value when you import
> data, whereas Postgres' sequence does not get reset when you import
> data, causing all new inserts to fail.  
> </quote>
> 
> Does that mean that the dropTable method of SQLObject should also drop
> the sequences?
> 
> Q2: Global sequence possible?
> -----------------------------
> 
> I was thinking of having IDs that are unique across tables--something
> useful when thinking of implementing inheritance etc.  So I tried to
> create a global sequence and have the id columns of several tables use
> it for their default values (DEFAULT nextval('global_oid_seq')).  But
> that seems to fail.  Can there be only a single column (of a single
> table) associated with a sequence???
> 
> many thanks
> 
> --b
> 
> /-----------------------------------------------------------------
> | Bud P. Bruegger, Ph.D. 
> | Sistema (www.sistema.it)
> | Via U. Bassi, 54
> | 58100 Grosseto, Italy
> | +39-0564-411682 (voice and fax)
> \-----------------------------------------------------------------
> 
> 
> -------------------------------------------------------
> Enterprise Linux Forum Conference & Expo, June 4-6, 2003, Santa Clara
> The only event dedicated to issues related to Linux enterprise solutions
> www.enterpriselinuxforum.com
> 
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
> 


/-----------------------------------------------------------------
| Bud P. Bruegger, Ph.D. 
| Sistema (www.sistema.it)
| Via U. Bassi, 54
| 58100 Grosseto, Italy
| +39-0564-411682 (voice and fax)
\-----------------------------------------------------------------

[SQLObject] two postgres questions

[Bud P. B. <bu...@si...>]

I would like to bring up two Postgres questions/issues:

Q1:  Table Dropping and left-over sequences:
--------------------------------------------

I found the following 

<quote src=http://www.phpbuilder.com/columns/tim20000705.php3:> 
At this point, I ran into another small issue with Postgres - its
"serial" data type (the equivalent of MySQL's auto_increment) creates
a "sequence" which does not get dropped when its parent table is
dropped. So if you try to re-create the table, you'll get a name
conflict for this sequence. A lot of new users would be confused by
this, so Postgres loses a couple points for that. Also, MySQL is
"smart" enough to increment its auto_increment value when you import
data, whereas Postgres' sequence does not get reset when you import
data, causing all new inserts to fail.  
</quote>

Does that mean that the dropTable method of SQLObject should also drop
the sequences?

Q2: Global sequence possible?
-----------------------------

I was thinking of having IDs that are unique across tables--something
useful when thinking of implementing inheritance etc.  So I tried to
create a global sequence and have the id columns of several tables use
it for their default values (DEFAULT nextval('global_oid_seq')).  But
that seems to fail.  Can there be only a single column (of a single
table) associated with a sequence???

many thanks

--b

/-----------------------------------------------------------------
| Bud P. Bruegger, Ph.D. 
| Sistema (www.sistema.it)
| Via U. Bassi, 54
| 58100 Grosseto, Italy
| +39-0564-411682 (voice and fax)
\-----------------------------------------------------------------

Re: [SQLObject] Expanding classRegistry

[Luke O. <lu...@me...>]

(Actually, the implementation of this snuck into the copy of
SQLObject.py I put up for Transaction v2. :)

So feel free to review there. It's very straightforward, only affects
classRegistry, needSet, setNeedSet, and findClass. (and the 3 or so
places in SQLObject that use needSet and findClass.)

- Luke

Quoting Ian Bicking <ia...@co...>:

> On Tue, 2003-05-13 at 03:25, Luke Opperman wrote:
> > My proposal: expand classRegistry/needSet to be a dict of
> > dictionaries, first keyed by a 'registry name' to be optionally
> set
> > in the SQLObject classes, and then as usual by class name.
>
> Sure, sounds fine.
>
>   Ian
>
>
>
>
> -------------------------------------------------------
> Enterprise Linux Forum Conference & Expo, June 4-6, 2003, Santa
> Clara
> The only event dedicated to issues related to Linux enterprise
> solutions
> www.enterpriselinuxforum.com
>
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>


--
 i find your contempt for naked feet curious.

Re: [SQLObject] Expanding classRegistry

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

On Tue, 2003-05-13 at 03:25, Luke Opperman wrote:
> My proposal: expand classRegistry/needSet to be a dict of
> dictionaries, first keyed by a 'registry name' to be optionally set
> in the SQLObject classes, and then as usual by class name.

Sure, sounds fine.

  Ian

[SQLObject] Transaction Implementation v2

[Luke O. <lu...@me...>]

Hello all -

Wow. Following Ian's suggestions yesterday, I was able to trim all of
this transaction support down to a one-line addition to SQLObject,
plus the now-combined MemoryTransaction class.

New interface:

m = MemoryTransaction()
a = Person.new(connection=m, ...)
b = Person(2).copyToConnection(m)
m.commit()

Changes to SQLObject necessary:

Same changes as before (in new() and joins), but __new__ no longer
needs useCache argument.

New method:

    def copyToConnection(self, conn):
        return self.__class__(self.id, connection=conn)

That's it. :)

Cache.py:

Cache.clear() does not behave as I would expect, still leaving
weakrefs around. It's not used at all within SQLObject that I can
find, so I'm assuming I'm safe to change the behavior to be an
overall purge:

    def clear(self):
        self.lock.acquire()
-       for key, value in self.cache.items():
-           self.expiredCache[key] = ref(value)
+       self.expiredCache = {}
        self.cache = {}
        self.lock.release()

(Otherwise my MemoryTransaction cache gets a ref to the new persisted
objects with the wrong connections inside them... could fix this by
making a no-op cache for Memory Transaction, but I have a feeling
this is the expected behavior for Cache.clear())


New version at http://www.amoebagod.com/SQLObject-T2.zip

- Luke

Re: [SQLObject] Sort order in MultipleJoin

[Luke O. <lu...@me...>]

> so far so good, but i was wondering, the result
> phonenumber list is unordered. is there a way to specifiy an order
> of such list? i've seen
> a _defaultOrder in the manual but seems related to Person.q or
> Person.select. i've tried to specify a _defaultOrder in PhoneNumber
> with:
>
> class PhoneNumber(SQLObject):
>      """Phone number"""
>
>      _defaultOrder = "PhoneType"
>

Yes, I highly support this modification. I've suggested it a time or
two, but we've been holding back to determine whether it's worthwhile
to use database-side ORDER BY (which requires a somewhat noticeable
change to the join SQL). For a while now, we've been using
python-side sorts without noticeable issues.

at the end of each performJoin function, replace:

  return [cls(id) for (id,) in ids]

with:

  objs = [cls(id) for (id,) in ids]
  if cls._defaultOrder:
    order = cls._defaultOrder
    objs.sort(lambda x,y: cmp(getattr(x, order), getattr(y, order)))
  return objs

Ugh, but functional.

Re: [SQLObject] SoCol and Col

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

On Tue, 2003-05-13 at 13:45, Luke Opperman wrote:
> could you quickly explain the purpose behind having SoCol and Col
> classes in Col.py? For now, just curious what the mechanism for
> creating new Columns should be, do I need one of each?

SOCol objects (and subclasses) cannot be shared between classes,
including between a class and its subclass.  But, specifically for
subclasses, *something* has to be shared, because inherited columns will
use the same object to specify the column as the superclass uses.  So
Col (and subclasses) exist as a sort of placeholder, which get turned
into a SOCol object for each class instance.

So yes, you need one of each.  The non-SO* classes all look pretty much
the same.

  Ian

Re: [SQLObject] Sort order in MultipleJoin

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

On Tue, 2003-05-13 at 14:52, deelan wrote:
> with this code i was able to grab such list and format an HTML table 
> with cheetah,  so far so good, but i was wondering, the result 
> phonenumber list is unordered. is there a way to specifiy an order of 
> such list? i've seen
> a _defaultOrder in the manual but seems related to Person.q or 
> Person.select. i've tried to specify a _defaultOrder in PhoneNumber with:

No, ordering doesn't apply to joins.  It would be nice if it did, I just
haven't done that.

  Ian

[SQLObject] Sort order in MultipleJoin

[deelan <de...@in...>]

hi there,

am i the only one having such basic requests, browsing the docs
and found something related try stuff and achieve nothing?

to recap: today i managed to get things work as expected with webware,
cheetah and sqlobject. remember my little Person/PhoneNumber example 
used in a previos messages of mine? it was a simple MultipleJoin test.
i instantiated a Person obj with:

p = Person(1) #look up personId = 1 in db
p.phoneNumbers #grab a list of PhoneNumber obj for person

with this code i was able to grab such list and format an HTML table 
with cheetah,  so far so good, but i was wondering, the result 
phonenumber list is unordered. is there a way to specifiy an order of 
such list? i've seen
a _defaultOrder in the manual but seems related to Person.q or 
Person.select. i've tried to specify a _defaultOrder in PhoneNumber with:

class PhoneNumber(SQLObject):
     """Phone number"""

     _defaultOrder = "PhoneType"

     ...

where PhoneType is a mysql enum column. i've done that to group result 
by phone type (mobile, home, office) but i didn't get any visible 
results. any hint? i would like to be able to grab a list of numbers via 
the "phoneNumbers" attribute, since the syntax is very clear and most of 
the time it would be enough.

thanks in advance

later,
deelan

[SQLObject] SoCol and Col

[Luke O. <lu...@me...>]

Ian -

could you quickly explain the purpose behind having SoCol and Col
classes in Col.py? For now, just curious what the mechanism for
creating new Columns should be, do I need one of each?

- Luke

Re: [SQLObject] Transaction Implmentation

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

On Tue, 2003-05-13 at 03:00, deelan wrote:
> somewhat related: new transaction support is working for mysql
> too? i've been told that recent mysql versions support
> transactions natively, older ones do not. does sqlobject ask
> rdbms system for transaction support and act conseguently or mysql trans
> support is yet to be written?

The database-level transaction support is pretty thin (and maybe that's
enough... I haven't gotten much feedback on it).  So if your MySQL
database supports transaction (and so does MySQLdb), then SQLObject
should.

That said, my understanding is that MySQL only has transaction support
with a propriety/commercial backend, and I've certainly never seen
anyone using transactions with MySQL in the wild.  IMHO, it's a feature
of MySQL meant to address marketing concerns more than anything, at
least for now.

  Ian

[SQLObject] Expanding classRegistry

[Luke O. <lu...@me...>]

I mentioned this a while back, but it's now upon me, and I need a way
to create distinct classRegistry's within the same interpreter
instance, so that I can have classes with the same name (which never
interact).

My proposal: expand classRegistry/needSet to be a dict of
dictionaries, first keyed by a 'registry name' to be optionally set
in the SQLObject classes, and then as usual by class name.

SQLObjects would add another class-level variable, _registry or
_registryName, if not specified then they would go in the default
'__global' name.

Thoughts? I'll quick implement this sometime tomorrow, but would
prefer any yay/nay and naming of variables feedback before I do so.
:)

- Luke