sqlobject-discuss — All things SQLObject-related

[SQLObject] Re: SQLObject

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

On Thu, 2003-04-10 at 16:10, Steve Holden wrote:
> > > 4. The whole business of using "id" as a default name for a primary key
> is
> > > somewhat contrived, and increases the difficulty of mapping SQLObject
> onto
> > > existing database schemas.
> >
> > I don't have a lot of experience with pre-existing schemas, and I've
> > just adopted the style that I was first introduced to.  I realize it's
> > common to use tablename_id or tablenameid for the primary key, though I
> > don't see any particular advantage besides some sort of implicit column
> > matching between tables.
> >
> I see. Generally you might find that many database designers and users
> aren't terribly (as) methodical (as you seem to be) in naming their data
> structures, so the more flexibility the better here. Would it be possible
> (e.g.) to define a subclass of Col called PK that had the additional
> semantics of identifying the named column as key. Alternatively an extra
> Col.__init__() argument PK=False that flags a Col as being a key column?

There is a KeyCol which I need to fill out a bit more, which will fill
that role.

As far as methodology in naming, I'd rather everyone was methodical. 
Having a style object could help that, because then you could make
explicit your style and when you create new tables or new columns it'll
be easier to stick with that style than otherwise.

> > At some point I'll probably make some sort of Style object which you can
> > use to give your default method/database mapping functions.  Then people
> > who use different conventions don't have to go through too much trouble.
> >
> Style is a nice idea, but again this implies method in the DB madness. I
> personally would be OK with some designs, since I do have naming
> conventions, but I'm not rigorous about their use (yet). Let's see, a
> 3-character table prefix, hmmm, ...

As far as methodology in naming, I'd rather everyone was methodical. 
Having a style object could help that, because then you could make
explicit your style and when you create new tables or new columns it'll
be easier to stick with that style than otherwise.

> > > 7. I don't see any support for tables with composite primary keys. Is
> this
> > > an omission you plan to rectify?
> >
> > I don't know.  I like single, simple primary keys.  They work well.  If
> > the table is really so minimal that it can't stand to have an extra
> > field for the id, then I suspect a SQLObject class is too heavy for it
> > (like with the join tables).  Of course, when adapting to a pre-existing
> > schema you might not have as many options.  Maybe it wouldn't be that
> > hard to fudge it (maybe make the ID into a tuple), but SQLObject really
> > expects for each object to have a single ID.
> >
> Well, I think I'd like the id to be a tuple, please. Refactoring would
> presumably consist of bracketing all expressions assigned to the ID value in
> the existing code?

On the Python side a tuple wouldn't be that big a deal, dynamic typing
and all, but the SQL generation would have to change.  Maybe it wouldn't
be that hard, _idName becomes a tuple too and you're WHERE clauses just
become slightly more complex.  I dunno...

  Ian

Re: [SQLObject] question on SQL schemas

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

On Thu, 2003-04-10 at 09:18, Bud P.Bruegger wrote:
> > As far as UNIQUE, that requires another option to Col.  But that's
> > reasonable.
> 
> So far, I understand "alternateID" to be equivalent to UNIQUE.  And I
> suppose it is possible to have several alternateID columns per table.
> So I see no need to introduce a new keyword argument.  

I dunno... alternateID creates a new class method, and any ol' UNIQUE
shouldn't imply that.  Especially since this is also used for schema
creation, you may want a UNIQUE where alternateID isn't appropriate. 
Though maybe I'm wrong... an example isn't occuring to me.

> It would be kind of nice to make sure that primary key columns and
> unique columns have an index (see
> http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=indexes.html).
> This would make lookups much faster...  So at least in backends that
> support it, the id column and alternateID columns should trigger the
> following SQL clause:
> 
> CREATE INDEX tableName_colName_index ON tableName (colName);

Yes, even if Postgres makes automatic indexes, not all do, so I'd
agree.  Or rather, there should be an index option to Col, and it should
default to True when the column is unique (or alternateID), and False
otherwise.

> Also, it would be nice to be able to control the creation of indices
> even if they are not unique.  For that purpose, an additional keyword
> argument for Col called "index" could be added.  It could possibly
> imply a class method that returns a list of objects (since it is not
> unique).  

You can already get a list of objects with selectBy, so I don't think
another method is called for.

> To cover the multi-column indices and unique constraints, similarly to
> _joins, _compositeIndex and _altCompositeID could be added.  This
> could trigger the following SQL (from PostgreSQL doc):
> 
> CREATE [UNIQUE] INDEX test2_mm_idx ON test2 (major, minor);
> 
> CREATE TABLE example (
>   a integer,
>   b integer,
>   c integer,
>   UNIQUE (a, c)
> );

I still have to think about this before I would implement it.  It's
unclear to me what the effects will be throughout the code.

> Here are some examples for the SQL syntax from the PostgreSQL doc.
> (Simple cut and paste from
> http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=ddl-constraints.html#DDL-CONSTRAINTS-FK)

Thanks, I'll make note of that.

  Ian

Re: [SQLObject] Re: SQLObject

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

On 04/10/03 01:06, Ian Bicking wrote:
> On Thu, 2003-04-10 at 00:38, Edmund Lian wrote:
> > Composite keys are used an awful lot in any schema of moderate complexity,
> > and they are important (when used with foreign key constraints) for
> > enforcing relational integrity. If the aim is for SQLObject to support
> > complex projects, then composite key support would be pretty important.
> 
> Can you describe some situations where they'd be used?  For instance, if
> either key was modified at any time that'd cause a lot of problems for
> SQLObject.  If not, then what would be the problem with a third simple
> ID/PRIMARY KEY column?

I use concatenated keys all the time.

e.g.

Site
site_id PK
...

User
user_id PK
...

UserSite
user_id	FK
site_id FK 
(together they're the PK)
...

or

Project
project_id PK
...

Worker
worker_id PK
...

ProjectWorker
project_id FK
worker_id  FK
(together they're the PK)
hourly_rate
...

and so on.

The problem with not having complex primary key support is not that it
makes it impossible to write a certain kind of application but that it
creates extra unnecessary, boring work for the programmer to have to
hardcode the validation checks (and the unit tests for them) to ensure
-- taking from one example above -- that a user hasn't been granted
access to a site twice.

-- 
Brad Bollenbach
BBnet.ca

[SQLObject] Re: SQLObject

[Steve H. <sh...@ho...>]

[Ian Bicking]


> I hope you don't mind me copying you to the list.
>
Not really, it's not like we were discussing my medical record or anything
:-)

> On Wed, 2003-04-09 at 04:15, Steve Holden wrote:
> > Ian:
> >
> > I've just spent a little time looking at SQLObject, as I'm always
interested
> > in object-relational mapping frameworks. It's a nice idea, and please
bear
> > in mind these remarks relate to my own use of it. Whether you take them
> > seriously enough to think about modifying something that clearly works
for
> > you is entirely up to you, and this isn't intended as criticism of your
> > efforts. At leat you know that someone's taken ther trouble to review
> > SQLObject. Specifically:
> >
> > 1. You document the magic _idName, but people like me who only read the
code
> > samples end up using _id instead, and getting and "Unknown column id"
SQL
> > error.
>
> Yes, you weren't the only one caught by this.
>
Just something that needed fixing: no biggie.

> > 2. I found the name translations imposed by your scheme a little
confusing.
> > Is this because you've had a lot of experience with bizarre databases
where
> > people have taken liberties with the names (like including spaces in
table
> > and/or attribute names)?
>
> No, I haven't particularly.  Right now reasonable names should all work,
> but spaces in attribute names won't.  I suppose it wouldn't be hard to
> add the proper quotes in to make that possible (just backquotes,
> right?), but I resist such forcefully bad names :-/
>
Right. I think you should perhaps in that case state that normal
non-tranlsated names are the default. I was fooled into thinking I *had* to
use a single intial cap to access the existing schema, which is probably
wrong.

> > Perhaps it might be better to avoid discussion of
> > these issues until after the simple examples. Personally I think I'd
prefer
> > it if you used some other mechanism to map object names to database
names
> > (the one you use for columns is better than the one you use for tables)
in
> > those cases where python names can't be used for database objects.
>
> Yes, it would probably make sense to start with automatic schema
> generation (which is fast to get working with), and then have a section
> on ways to adapt your class to a pre-existing table.
>
There you go. Concentrate on benefits, not features ;-)

> > 3. You talk about "relations" when I suspect you really mean
> > *relationships*. Formally, a relation is what a table in a relational
> > database represents, and it contains occurrences of a single entity-type
> > identified by primary key value. A relationship is a mapping from
> > occurrences of one entity-type to occurrences of another entity-type.
>
> Noted and changed.
>
> > 4. The whole business of using "id" as a default name for a primary key
is
> > somewhat contrived, and increases the difficulty of mapping SQLObject
onto
> > existing database schemas.
>
> I don't have a lot of experience with pre-existing schemas, and I've
> just adopted the style that I was first introduced to.  I realize it's
> common to use tablename_id or tablenameid for the primary key, though I
> don't see any particular advantage besides some sort of implicit column
> matching between tables.
>
I see. Generally you might find that many database designers and users
aren't terribly (as) methodical (as you seem to be) in naming their data
structures, so the more flexibility the better here. Would it be possible
(e.g.) to define a subclass of Col called PK that had the additional
semantics of identifying the named column as key. Alternatively an extra
Col.__init__() argument PK=False that flags a Col as being a key column?

> At some point I'll probably make some sort of Style object which you can
> use to give your default method/database mapping functions.  Then people
> who use different conventions don't have to go through too much trouble.
>
Style is a nice idea, but again this implies method in the DB madness. I
personally would be OK with some designs, since I do have naming
conventions, but I'm not rigorous about their use (yet). Let's see, a
3-character table prefix, hmmm, ...

> > 5. Your assertion that MySQL does not support transactions is out of
date,
> > since they can be supported by databases built from InnoDB tables, I
believe
> > (or have I imagined this?).
>
> That's true.  But I've yet to meet someone actually using transactions,
> and my own (admittedly brief) attempts to get transactions working were
> unsuccessful.  I suppose it's possible, and there's no particular
> barriers in place to keep someone from using transactions with MySQL (if
> MySQLdb supports them...?), but until someone does it I find MySQL's
> claim less than convincing.
>
I guess it's a YAGNI then.

> > 6. Under "Creating and Dropping Tables" you discuss creating a
*database*
> > rather than a *table*.
>
> FileMaker has infected me with its nefarious terminology!
>
Wash your mouth out with SOAP :-)

> > 7. I don't see any support for tables with composite primary keys. Is
this
> > an omission you plan to rectify?
>
> I don't know.  I like single, simple primary keys.  They work well.  If
> the table is really so minimal that it can't stand to have an extra
> field for the id, then I suspect a SQLObject class is too heavy for it
> (like with the join tables).  Of course, when adapting to a pre-existing
> schema you might not have as many options.  Maybe it wouldn't be that
> hard to fudge it (maybe make the ID into a tuple), but SQLObject really
> expects for each object to have a single ID.
>
Well, I think I'd like the id to be a tuple, please. Refactoring would
presumably consist of bracketing all expressions assigned to the ID value in
the existing code?

> > Anyway, thanks for an interesting piece of software!
>
> And thank you for your careful reading of the documentation.
>
Well, not careful enough, since I still haven' treally delved into
many-to-many relationships.

I'd also like to hear ideas about a possible "web form rendering" mixin that
allows a collection of columns to be input from, or populate, a form in a
web page.

regards
--
Steve Holden                                  http://www.holdenweb.com/
Python Web Programming                 http://pydish.holdenweb.com/pwp/
Did you miss PyCon DC 2003?            Would you come to PyCOn DC 2004?

Re: More troubles [was Re: [SQLObject] Installation trouble...]

[Frank B. <fb...@fo...>]

Hallo,
Ian Bicking hat gesagt: // Ian Bicking wrote:

> On Thu, 2003-04-10 at 13:44, Frank Barknecht wrote:
> > I do, too, but this webserver currently has only 2.2.0 installed.
> > I'll see, if we can update this without breaking other things. I'm the
> > only one using python there, the webmaster is a perl guy...
> > 
> > Changing connection to a class variable (_connection = ...) leads me
> > past this error, but another one comes along:
> 
> That's a bug bug, not a version bug.  It's fixed in CVS.

I remember now another failure with my 2.2.0 installation: I had to
add "True,False=..." to Cache.py and DBConnection.py, because
otherwise the bools aren't defined.

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] Newbie Problem w/ PostgreSQL

[Frank B. <fb...@fo...>]

Hallo,
Mike Hostetler hat gesagt: // Mike Hostetler wrote:

> 
> I'm not only new to SQLObject, but to the whole DB realm in general.
> 
> I made a table in PostgreSQL called Site, then I created a class not too
> dissimilar from the example in the documentation:

SQLObject can create tables on its own, and in fact that is one of the
coolest features, IMO. Somehow I alsways hated writing "create
table..." by hand, now I don't need to anymore ;)

Just call:

Site.dropTable()
Site.createTable()

 
> >>> class Site(SQLObject):
> ...    _columns = [StringCol('url'),
> ...     DateTimeCol('modified'),
> ...     StringCol('etag')]
> ...
> 
> But, when I tried to make a new object, it crashed:
> 
> >>> s = Site.new(url=lst[0],modified=lst[1],etag=lst[2])
> Traceback (most recent call last):
>   File "<stdin>", line 1, in ?
>   File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line
> 713, in new
>     inst._SO_finishCreate()
>   File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line
> 732, in _SO_finishCreate
>     id = self._connection.queryInsertID(self._table, self._idName,
> AttributeError: 'Site' object has no attribute '_connection'
> >>> Site._connection = c

What is c? Is it a PostgresConnection to your database looking like
the one in examples/people.py?

> >>> s = Site.new(url=lst[0],modified=lst[1],etag=lst[2])
> Traceback (most recent call last):
>   File "<stdin>", line 1, in ?
>   File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line
> 713, in new
>     inst._SO_finishCreate()
>   File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line
> 733, in _SO_finishCreate
>     names, values)
>   File "/usr/lib/python2.2/site-packages/SQLObject/DBConnection.py", line
> 126, in queryInsertID
>     return self._runWithConnection(self._queryInsertID, table, idName,
> names, values)
>   File "/usr/lib/python2.2/site-packages/SQLObject/DBConnection.py", line
> 72, in _runWithConnection
>     val = meth(conn, *args)
>   File "/usr/lib/python2.2/site-packages/SQLObject/DBConnection.py", line
> 437, in _queryInsertID
>     c.execute('SELECT nextval(\'%s_id_seq\')' % table)
> psycopg.ProgrammingError: ERROR:  Relation "site_id_seq" does not exist
> 
> SELECT nextval('site_id_seq')
> 
> Can anyone shed some light on a poor newbie?

You might want to try to let SQLObject create the table, than look at
the created schema with for example pg_dump.

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: More troubles [was Re: [SQLObject] Installation trouble...]

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

On Thu, 2003-04-10 at 13:44, Frank Barknecht wrote:
> Hallo,
> 
> Ian Bicking hat gesagt: // Ian Bicking wrote:
> > That's weird.  It's like it isn't doing the metaclass thing right.  As
> > long as you don't use __connection__ that line shouldn't be executed,
> > for a quick fix (or test)... I wonder if 2.2 has problems (I've been
> > doing my testing on 2.2.2).
> 
> I do, too, but this webserver currently has only 2.2.0 installed.
> I'll see, if we can update this without breaking other things. I'm the
> only one using python there, the webmaster is a perl guy...
> 
> Changing connection to a class variable (_connection = ...) leads me
> past this error, but another one comes along:

That's a bug bug, not a version bug.  It's fixed in CVS.

  Ian

[SQLObject] Newbie Problem w/ PostgreSQL

[Mike H. <th...@bi...>]

I'm not only new to SQLObject, but to the whole DB realm in general.

I made a table in PostgreSQL called Site, then I created a class not too
dissimilar from the example in the documentation:

>>> class Site(SQLObject):
...    _columns = [StringCol('url'),
...     DateTimeCol('modified'),
...     StringCol('etag')]
...

But, when I tried to make a new object, it crashed:

>>> s = Site.new(url=lst[0],modified=lst[1],etag=lst[2])
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line
713, in new
    inst._SO_finishCreate()
  File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line
732, in _SO_finishCreate
    id = self._connection.queryInsertID(self._table, self._idName,
AttributeError: 'Site' object has no attribute '_connection'
>>> Site._connection = c
>>> s = Site.new(url=lst[0],modified=lst[1],etag=lst[2])
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line
713, in new
    inst._SO_finishCreate()
  File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line
733, in _SO_finishCreate
    names, values)
  File "/usr/lib/python2.2/site-packages/SQLObject/DBConnection.py", line
126, in queryInsertID
    return self._runWithConnection(self._queryInsertID, table, idName,
names, values)
  File "/usr/lib/python2.2/site-packages/SQLObject/DBConnection.py", line
72, in _runWithConnection
    val = meth(conn, *args)
  File "/usr/lib/python2.2/site-packages/SQLObject/DBConnection.py", line
437, in _queryInsertID
    c.execute('SELECT nextval(\'%s_id_seq\')' % table)
psycopg.ProgrammingError: ERROR:  Relation "site_id_seq" does not exist

SELECT nextval('site_id_seq')

Can anyone shed some light on a poor newbie?

-- mikeh
-- 

Re: More troubles [was Re: [SQLObject] Installation trouble...]

[Frank B. <fb...@fo...>]

Hallo,

Ian Bicking hat gesagt: // Ian Bicking wrote:
> That's weird.  It's like it isn't doing the metaclass thing right.  As
> long as you don't use __connection__ that line shouldn't be executed,
> for a quick fix (or test)... I wonder if 2.2 has problems (I've been
> doing my testing on 2.2.2).

I do, too, but this webserver currently has only 2.2.0 installed.
I'll see, if we can update this without breaking other things. I'm the
only one using python there, the webmaster is a perl guy...

Changing connection to a class variable (_connection = ...) leads me
past this error, but another one comes along:

$ python fragen.py
[...]
Me OR You OR freeForm OR You OR freeForm
-- Ah, no, I guess, "Me" is not a good answer...
Traceback (most recent call last):
  File "fragen.py", line 92, in ?
    a1.destroy()
  File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 831, in destroy
    self._connection.cache.purge(self.id)
AttributeError: 'CacheSet' object has no attribute 'purge'

So, I cannot destroy. :(

I guess, the requirements of SQLObject should be raised up 2 minor
numbers...

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] higher-level types for attributes..

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

On Thu, 2003-04-10 at 11:42, Bud P.Bruegger wrote:
> Two examples to illustrate what I mean by higher-level types:
> 
> * (single) home phone number as an attribute in a person table:
> 
> (note that this is different from the example in people.py--just can't
> think of anything better right now...)
> 
> At a low level, they are implemented as a string.  At a higher level,
> it may be a class with various constructors etc, with a distinction of
> area code and number, etc. and maybe with consistency constraints...
> 
> Now in the database, it makes sense to represent it as a single
> string, but in the object world, it would be nice if it was an
> instance of the class PhoneNumber.  
> 
> It would be nice if SQLObject could store something like __rep__
> (different from __repr__) in the DBMS but type cast the result (maybe
> as does the pickling module?). 

Right now you'll have to do method overloading, somewhat like the
example in the docs.  In the future the Constraints code should also
make conversion possible, so you'll be able to use that to construct the
phone number object out of the database string (and vice versa).

> * the coordinates of a location
> 
> Assume that--at a low level--I want the latitude and longitude to be
> attributes of a city table.  But really, I would like to get them out
> together and treat them as instances of the "point" class.  To make it
> a table of its own with a 1:1 relationship to city would be rather
> awkward...
> 
> Since a point adds several fields, this could be considered some kind
> of an "inline table".  The columns of this table to be inlined could
> be defined by _columns in the appropriate class...

Hmmm... I'm not clear here.  You mean that the fields have subfields,
like some_table.some_point.x ?  You could add something to SQLBuilder to
use this in queries fairly easily.  For actual retrieval, it's up to
either some (not-yet-implemented) Constraint system to handle it, or the
database adapter.  And the sqlRepr method I mentioned in the other
email.

  Ian

Re: [SQLObject] how to handle boolean and other types provided by DBMS?

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

On Thu, 2003-04-10 at 12:14, Bud P.Bruegger wrote:
> Maybe, it would be possible to add yet another subclass to Col that
> has an "sqlType" keyword attribute and simply passes on what is
> written there to the DBMS.  This would allow to take advantage of
> "advanced" types that some DBMS provide.

If your class has a sqlRepr() method, then that will be called to get
the SQL representation.  I should name that __sql_repr__ or something,
though...

Anyway, that's the hook you want.

  Ian

Re: More troubles [was Re: [SQLObject] Installation trouble...]

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

On Thu, 2003-04-10 at 10:01, Frank Barknecht wrote:
> $ python fragen.py
> Traceback (most recent call last):
>   File "fragen.py", line 15, in ?
>     class Answer(SQLObject):
>   File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 124, in __new__
>     mod = sys.modules[dict['__module__']]
> KeyError: __module__
> 
> What does that mean and how to fix? Python is 2.2-final.

That's weird.  It's like it isn't doing the metaclass thing right.  As
long as you don't use __connection__ that line shouldn't be executed,
for a quick fix (or test)... I wonder if 2.2 has problems (I've been
doing my testing on 2.2.2).

  Ian

[SQLObject] bugFix: isFloat() missing in Constraints.py

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

Well, against promise. Here is a bug fix:

The module Constraints seems to fail to implement isFloat(...).  I
added the following


def isFloat(obj, col, value):
    if type(value) is not type(1.1):
        raise BadValue("only allows floating point numbers", obj, col, value)

and it seems to fix the problem.

In detail, here is my test

#================================================
#!/usr/local/bin/python
from SQLObject import *

class Sight(SQLObject):
    _columns = [ FloatCol('lat'),]
#================================================

and the error message:


#================================================
Traceback (most recent call last):
  File "./test.py", line 6, in ?
    class Sight(SQLObject):
  File "./test.py", line 8, in Sight
   _columns = [ FloatCol('lat'),]
  File "/usr/local/lib/python2.2/site-packages/SQLObject/Col.py", 
    line 59, in __init__
    constraints = self.autoConstraints() + constraints
  File "/usr/local/lib/python2.2/site-packages/SQLObject/Col.py",
   line 186, in autoConstraints
   return [Constraints.isFloat]
  AttributeError: 'module' object has no attribute 'isFloat'
#================================================

cheers
--b

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

[SQLObject] how to handle boolean and other types provided by DBMS?

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

Oops, another question--am I pushing my luck too far?

How do I best represent booleans with SQLObject?  PostgreSQL has a
boolean type (see
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=datatype-boolean.html)...

And there are many other highly interesting types such as geometric
types that I likely use in my current app.  [Yes, I marry PostgreSQL
for this app--no need to ease migration to other dbms].  

Maybe, it would be possible to add yet another subclass to Col that
has an "sqlType" keyword attribute and simply passes on what is
written there to the DBMS.  This would allow to take advantage of
"advanced" types that some DBMS provide.

Well, I'll better try to stop for today before you drop me off the
list...

--b


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

[SQLObject] higher-level types for attributes..

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

Hi Ian,

I suppose I'm bombing you with too much--but the ideas keep flowing
while I work on things...

I was wondering about how to provide higher-level data types for
attributes than just those offered by SQL (corresponding to subclasses
of Col).  If it is already possible, I would like to ask how.  Else,
here are ideas of what could be done:

Two examples to illustrate what I mean by higher-level types:

* (single) home phone number as an attribute in a person table:

(note that this is different from the example in people.py--just can't
think of anything better right now...)

At a low level, they are implemented as a string.  At a higher level,
it may be a class with various constructors etc, with a distinction of
area code and number, etc. and maybe with consistency constraints...

Now in the database, it makes sense to represent it as a single
string, but in the object world, it would be nice if it was an
instance of the class PhoneNumber.  

It would be nice if SQLObject could store something like __rep__
(different from __repr__) in the DBMS but type cast the result (maybe
as does the pickling module?). 


* the coordinates of a location

Assume that--at a low level--I want the latitude and longitude to be
attributes of a city table.  But really, I would like to get them out
together and treat them as instances of the "point" class.  To make it
a table of its own with a 1:1 relationship to city would be rather
awkward...

Since a point adds several fields, this could be considered some kind
of an "inline table".  The columns of this table to be inlined could
be defined by _columns in the appropriate class...

I hope this made it clear what I'm thinking of.  Now some ideas for
the implementation:

For the former case, one of the existing subclasses of Col could be
used to specify the representation type.  An additional keyword
attribute would specify the target class for the value.  For example,
class=PhoneNumber.  The target class would probably have to provide
two functions:  one to "serialize" it to the representation type
(maybe called __rep__ or __sqlRep__), and one to construct an instance
back from that.   Note that any kind of object that you don't want to
be used in SQL queries could be simply pickled to represent...

How difficult would this be to add?  Is this easy to integrate into
caching?  

BTW, I believe to remember that the Gnosis XML package does some
similar type of serialization/unserialization of objects...

For the latter case of "inline tables", a specific subclass of Col may
be appropriate.  It would specify the class to inline--everything else
would be specified there.  

Looking forward to hearing what you think of this.  Also, if you give
me guidance on where to lie hands, I may attempt to try something
out...

best cheers
--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] PostgreSQL question: Unique and Indices?

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

On 04/10/2003 09:49:40 AM Bud wrote:

>PostgresSQL people out there,
>
>does anyone know whether using UNIQUE and PRIMARY KEY automatically
>creates a unique index?

Yes.

...Edmund.

[SQLObject] rename notNull -> required, SQL check constraints

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

Another thought on the relationship of alternateID and notNull:

Doesn't the former imply the latter?  If so, maybe the interface can
be simplified.

Also, considering that SQLObject kind of hides the SQL and attempts a
pythonic touch and feel, I don't like the term "notNull" since Null
really comes from SQL world.  NotNone would be more pythonic (but
ugly), but what about "required"?

And a change of topic:
Just for completeness, it would be nice to also add Col keyword
argument check, for example check="price > 0",  and a special
attribute _checks, for example _checks=[check("marriageDate >
birthDate")], to access Check constraints of SQL.  (See
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=ddl-constraints.html#AEN1793).  

--b

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

More troubles [was Re: [SQLObject] Installation trouble...]

[Frank B. <fb...@fo...>]

Hallo,

Frank Barknecht hat gesagt: // Frank Barknecht wrote:

> I'm having problems installing SQLObject on a webserver running
> SusE-Linux system. I normally use Debian, so I don't know what exactly
> seems to go wrong here.

I still don't know, what's wrong with the install script, but after I
installed SQLObject by hand, I now have a more serious problem.
Runnings the test script, I posted here earlier (see post regarding
_defaultOrder), I get this error:

$ python fragen.py
Traceback (most recent call last):
  File "fragen.py", line 15, in ?
    class Answer(SQLObject):
  File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 124, in __new__
    mod = sys.modules[dict['__module__']]
KeyError: __module__

What does that mean and how to fix? Python is 2.2-final.

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] Installation trouble...

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

Not sure whether this is the problem, but if setup.py attempts to
download the URL given below, it will come to a "dispatch" page where
you have to chose a mirror, not to the actual software.  

The URL for the SWITCH mirror in Zurich (this should be close to
you?) is
http://prdownloads.sourceforge.net/sqlobject/SQLObject-0.3.tar.gz?use_mirror=switch

that replaces "?download" in the URL listed below with
"?use_mirror=switch"...  Not sure where to change this, but likely you
can edit setup.py...???

hope this works


--b

On Thu, 10 Apr 2003 15:16:05 +0200
Frank Barknecht <fb...@fo...> wrote:

> Hallo,
> 
> I'm having problems installing SQLObject on a webserver running
> SusE-Linux system. I normally use Debian, so I don't know what exactly
> seems to go wrong here.
> 
> Using SO-0.3.0 the error I get is this:
> 
> # python2.2 setup.py install
> running install
> Traceback (most recent call last):
>   File "setup.py", line 32, in ?
>     download_url="http://prdownloads.sourceforge.net/sqlobject/SQLObject-0.3.
>     tar.gz?download")
>   File "/usr/lib/python2.2/distutils/core.py", line 138, in setup
>     dist.run_commands()
>   File "/usr/lib/python2.2/distutils/dist.py", line 893, in
> run_commands
>     self.run_command(cmd)
>   File "/usr/lib/python2.2/distutils/dist.py", line 912, in
> run_command
>     cmd_obj.ensure_finalized()
>   File "/usr/lib/python2.2/distutils/cmd.py", line 112, in
> ensure_finalized
>     self.finalize_options()
>   File "/usr/lib/python2.2/distutils/command/install.py", line 268, in
> finalize_options
>     (prefix, exec_prefix) = get_config_vars('prefix', 'exec_prefix')
>   File "/usr/lib/python2.2/distutils/sysconfig.py", line 408, in
> get_config_vars
>     func()
>   File "/usr/lib/python2.2/distutils/sysconfig.py", line 313, in
> _init_posix
>     raise DistutilsPlatformError(my_msg)
> distutils.errors.DistutilsPlatformError: invalid Python installation:
> unable to open /usr/lib/python2.2/config/Makefile (No such file or
> directory)
> 
> python package is python-2.2-166 with this version:
> 
> Python 2.2 (#1, May 14 2002, 18:23:13) 
> [GCC 2.95.3 20010315 (SuSE)] on linux2
> 
> Do I miss to install a package?
> 
> ciao
> -- 
>  Frank Barknecht                               _ ______footils.org__
> 
> 
> -------------------------------------------------------
> This SF.net email is sponsored by: Etnus, makers of TotalView, The debugger 
> for complex code. Debugging C/C++ programs can leave you feeling lost and 
> disoriented. TotalView can help you find your way. Available on major UNIX 
> and Linux platforms. Try it free. www.etnus.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] Re: PostgreSQL question: Unique and Indices?

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

Just played around with psql to answer to myself:

Yes, both UNIQUE and PRIMARY KEY imply the definition of an index:

cico=# create table bud(
cico(# id INT PRIMARY KEY);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
'bud_pkey' for table 'bud'

cico=# create table bbb(
cico(# id INT UNIQUE);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 'bbb_id_key'
for table 'bbb'

--b

On Thu, 10 Apr 2003 15:49:40 +0200
Bud P. Bruegger <bu...@si...> wrote:

> PostgresSQL people out there,
> 
> does anyone know whether using UNIQUE and PRIMARY KEY automatically
> creates a unique index?
> 
> thanks
> --b
> 
> /-----------------------------------------------------------------
> | Bud P. Bruegger, Ph.D. 
> | Sistema (www.sistema.it)
> | Via U. Bassi, 54
> | 58100 Grosseto, Italy
> | +39-0564-411682 (voice and fax)
> \-----------------------------------------------------------------


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

Re: [SQLObject] question on SQL schemas

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

On 09 Apr 2003 23:49:32 -0500
Ian Bicking <ia...@co...> wrote:

> As far as UNIQUE, that requires another option to Col.  But that's
> reasonable.

So far, I understand "alternateID" to be equivalent to UNIQUE.  And I
suppose it is possible to have several alternateID columns per table.
So I see no need to introduce a new keyword argument.  

It would be kind of nice to make sure that primary key columns and
unique columns have an index (see
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=indexes.html).
This would make lookups much faster...  So at least in backends that
support it, the id column and alternateID columns should trigger the
following SQL clause:

CREATE INDEX tableName_colName_index ON tableName (colName);


Also, it would be nice to be able to control the creation of indices
even if they are not unique.  For that purpose, an additional keyword
argument for Col called "index" could be added.  It could possibly
imply a class method that returns a list of objects (since it is not
unique).  

To cover the multi-column indices and unique constraints, similarly to
_joins, _compositeIndex and _altCompositeID could be added.  This
could trigger the following SQL (from PostgreSQL doc):

CREATE [UNIQUE] INDEX test2_mm_idx ON test2 (major, minor);

CREATE TABLE example (
  a integer,
  b integer,
  c integer,
  UNIQUE (a, c)
);



> For foreign keys, the KeyCol needs to be expanded.  I'm used to MySQL,
> which has very simple (primitive?) joins, where it's entirely implied. 
> Postgres goes further, but I'm not familiar with the syntax.  KeyCol
> needs to be expanded in general, because its arguments should be
> different than Col's arguments.

Here are some examples for the SQL syntax from the PostgreSQL doc.
(Simple cut and paste from
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=ddl-constraints.html#DDL-CONSTRAINTS-FK)

CREATE TABLE products (
  product_no integer PRIMARY KEY,
  name text,
  price numeric
);

CREATE TABLE orders (
  order_id integer PRIMARY KEY,
  product_no integer REFERENCES products (product_no),
  quantity integer
);

and for a multi-field foreign key (that probably isn't needed):

CREATE TABLE t1 (
   a integer PRIMARY KEY,
   b integer,
   c integer,
   FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);


Here also an example for the use of a composite primary key in a table
that breaks up a many-to-many relationship.  I didn't think of this
before and here probably a composite primary key makes a lot of sense.

CREATE TABLE products (
  product_no integer PRIMARY KEY,
  name text,
  price numeric
);
 
CREATE TABLE orders (
  order_id integer PRIMARY KEY,
  shipping_address text,
  ...
);
   
CREATE TABLE order_items (
  product_no integer REFERENCES products, 
  order_id integer REFERENCES orders,
  quantity integer,
  PRIMARY KEY (product_no, order_id)
 );


And finally, here is a variation that manages how deletes cascade:

CREATE TABLE order_items (
  product_no integer REFERENCES products ON DELETE RESTRICT,
  order_id integer REFERENCES orders ON DELETE CASCADE,
  quantity integer,
  PRIMARY KEY (product_no, order_id)
);

You may want to use this (the CASCADE...) for tables in many-to-many
relationships...

While I'm using PostgreSQL for the first time, and thus am not an
expert, let me know if I can help in any way to get the SQL right.  

best cheers
--bud


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

[SQLObject] PostgreSQL question: Unique and Indices?

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

PostgresSQL people out there,

does anyone know whether using UNIQUE and PRIMARY KEY automatically
creates a unique index?

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] Re: SQLObject

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

On 10 Apr 2003 00:23:11 -0500
Ian Bicking <ia...@co...> wrote:

> I hope you don't mind me copying you to the list.
> 
> On Wed, 2003-04-09 at 04:15, Steve Holden wrote:

....

> > 4. The whole business of using "id" as a default name for a primary key is
> > somewhat contrived, and increases the difficulty of mapping SQLObject onto
> > existing database schemas.

For what it's worth, here are some thoughts on the topic.  

I personally believe having id's is a great thing.  Using primary keys
with any sort of business meaning will get you into trouble sooner or
later.  So if SQLObject didn't add it, I would...  (BTW, this argument
is also relevant for composite keys--they usually have business
meaning...).  The only problem may occur if one uses SQLObject with
pre-existing databases that don't have a comparable field...

Correct me if I'm wrong, but it seems that SQLObject needs an id
column of type integer.  Could that be relaxed to be another type (a
string).

In this case, one possible solution would be to use an option
"primaryKey=True" for Col.  If a primary key is defined in _columns,
then it is used, else, id is implied as is currently the case.  

Another thought in this context is that PostgreSQL already inserts an
object id column for every table.  But maybe it's not a good idea to
use since other dbms don't have equivalents...

One thing on my wishlist would be to (optionally) create a globally
unique id for the object.  That could be controlled by an additional
option "globallyUnique=True" that would go together with PrimaryKey,
Unique, or alternateID (i.e., everything that implies Unique).  

> > 7. I don't see any support for tables with composite primary keys. Is this
> > an omission you plan to rectify?
> 
> I don't know.  I like single, simple primary keys.  They work well.  If
> the table is really so minimal that it can't stand to have an extra
> field for the id, then I suspect a SQLObject class is too heavy for it
> (like with the join tables).  Of course, when adapting to a pre-existing
> schema you might not have as many options.  Maybe it wouldn't be that
> hard to fudge it (maybe make the ID into a tuple), but SQLObject really
> expects for each object to have a single ID.

For the above considerations, I think a single object id is usually a
good idea.  In one project I started out with composite keys and as
the data structure evolved added an autoincrement key to keep it
simpler and more changeable.  

However, it may be nice to have composite field indices on tables and
have an equivalent to alternateID that creates a method with multiple
arguments.  

cheers

-bud

[SQLObject] Installation trouble...

[Frank B. <fb...@fo...>]

Hallo,

I'm having problems installing SQLObject on a webserver running
SusE-Linux system. I normally use Debian, so I don't know what exactly
seems to go wrong here.

Using SO-0.3.0 the error I get is this:

# python2.2 setup.py install
running install
Traceback (most recent call last):
  File "setup.py", line 32, in ?
    download_url="http://prdownloads.sourceforge.net/sqlobject/SQLObject-0.3.tar.gz?download")
  File "/usr/lib/python2.2/distutils/core.py", line 138, in setup
    dist.run_commands()
  File "/usr/lib/python2.2/distutils/dist.py", line 893, in
run_commands
    self.run_command(cmd)
  File "/usr/lib/python2.2/distutils/dist.py", line 912, in
run_command
    cmd_obj.ensure_finalized()
  File "/usr/lib/python2.2/distutils/cmd.py", line 112, in
ensure_finalized
    self.finalize_options()
  File "/usr/lib/python2.2/distutils/command/install.py", line 268, in
finalize_options
    (prefix, exec_prefix) = get_config_vars('prefix', 'exec_prefix')
  File "/usr/lib/python2.2/distutils/sysconfig.py", line 408, in
get_config_vars
    func()
  File "/usr/lib/python2.2/distutils/sysconfig.py", line 313, in
_init_posix
    raise DistutilsPlatformError(my_msg)
distutils.errors.DistutilsPlatformError: invalid Python installation:
unable to open /usr/lib/python2.2/config/Makefile (No such file or
directory)

python package is python-2.2-166 with this version:

Python 2.2 (#1, May 14 2002, 18:23:13) 
[GCC 2.95.3 20010315 (SuSE)] on linux2

Do I miss to install a package?

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] SQLObject 0.3: people.py example

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

On Wed, 2003-04-09 at 11:49, Bud P.Bruegger wrote:
> I slighly modified person.py such that it runs "out of the box"
> (attached).  

Thanks.  I'll have to work this over a little (I'd like it to have the
same command-line interface as test.py), but it looks good, and it
should replace people.py in CVS soon.  Simpler than it was before, to be
sure. 

  Ian