sqlobject-discuss — All things SQLObject-related

[SQLObject] TypeError: default __new__ takes no parameters

[Matt M. <ma...@mo...>]

Hello,

I have just checked out SQLObject from CVS and installed it on my
debian system (python2.3). I get the following when I attempt to run
the test program.

>>> p = Person.new(firstName="John", lastName="Doe", username="johnd")
Traceback (most recent call last):
  File "people.py", line 161, in ?
    runTest(test1)
  File "people.py", line 159, in runTest
    exec line[4:]
  File "<string>", line 1, in ?
  File "/usr/lib/python2.3/site-packages/SQLObject/SQLObject.py", line 665, in new
    inst = cls(None)
  File "/usr/lib/python2.3/site-packages/SQLObject/SQLObject.py", line 292, in __new__
    inst = object.__new__(cls, id)
TypeError: default __new__ takes no parameters

Any idea what could be causing this?

Thanks,
Matt

[SQLObject] Importing, and Globally-Unique Object Names

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

Hi all -

Well, we've identified a few things that are merely
'irritations' at this point, but will become serious
trouble for our use of SQLObject in the future.

First problem: The inability of classRegistry/needSet to
import classes that aren't explicitly imported. For
example, if we have three classes "ProductSaved", "Product"
and "ProductType", and we're only dealing with
"ProductSaved" instances in a given class, accessing
"savedProductInstance.product.type" throws an exception (no
attribute _SO_class_ProductType), because ProductType is
not imported by either ProductSaved or Product (just
referenced by name as a foreignKey in Product).

Is the best solution to this just to do an import of all
classes referred to in a class (even if just by name) in
the class file? ie, Product would import ProductType? This
seems to be working for us, but should probably be
explicitly mentioned in the docs if it's the best way.
(Yes, I know such access breaks Law of Demeter or whatever,
but I believe the above example is a fairly reasonable one.
:)


Second problem, and one I don't have a solution for right
now: the interpreter-global uniqueness required of class
names for classRegistry. We don't run many instances of
Webware/Webkit for multiple sites, just run them as
multiple contexts. This makes two sites who have separate
"Product" objects conflict, but it seems to be a very
realistic problem...

Possible high-level solutions I can think of right now:
making class registry be keyed by the class modules'
__file__ attribute or similar (not fully thought through, I
realize this won't work because of needSet's now-ambiguous
names....)

Or having the ability to create specific classRegistrys,
sort of like having multiple object Stores. Haven't fully
thought this through, but my first thought is to simply add
another class-level or module-level attribute called
"_registry". The SQLObject classRegistry/needset would now
become dictionaries of dicationaries/lists, with a default
registry key called perhaps "__global" or some other
unlikely name. So needSet/setNeedSet would check the class
for the _registry variable, and search within the specified
sub-keyed areas only.

Not sure I explained that too well. classRegistry would now
look like (for my multiple Product example):

{
  '__global': {},
  'site1': {'Product': ...},
  'site2': {'Product': ...}
}

based on the two Product classes specifying site1 or site2
as their registry.

Thoughts?

- Luke

[SQLObject] request (info or feature): two hooks for implementing higher-level types

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

Ian,

I thought more about my "higher-level" types and believe what I really
need are two hooks:

* afterLoad() that is called just after the object has been created from
  data from the DBMS

* beforeStore() that is called just before storing data in the DBMS

By default, these don't do anything and need to be overloaded if one
wants to use them.

The following example shows how to apply this to my problem:

assume _columns contains
  ...
  FloatCol('_ptX'),
  FloatCol('_ptY'),
  ...

so with 

def afterLoad(self):
  point = Point(self._ptX, self._ptY)

I could really treat my data as being of type Point...

To make sure that storage is consistent, I'd need

def beforeStore(self):
  self._ptX = self.point.x()
  self._ptY = self.point.y()

The same approach obviously also solves the phoneNumber type
example...

Note that some of the attributes are probably not for direct use.  I
indicated that by preceding their name with an underscore...  But
maybe there is a way of having a keyword argument to Col called
'hidden' or similar such that I can only access it from the above
hook routines???

My intuition says that implementing these two hooks may be quite
straight forward or that they already exist.  Can you give me some
more insight on this?

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

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

Okay, I got all confused because both of your first and last names start
in B, and when I was first skimming through this I thought one of you
was arguing with yourself.  But no... anyway, to interject, I still
don't feel there's a good use case for multiple keys (except legacy
schemas)

On Fri, 2003-04-11 at 08:44, Bud P.Bruegger wrote:
> If this is your comments table, there are no problems.  However, the
> problem I try to illustrate uses the following table:
> 
> CREATE TABLE ProjectWorkerComment(
>   project_id INT,
>   worker_id INT,
>   comment_id INT,
>   PRIMARY KEY (project_id, worker_id, comment_id),
>   FOREIGN KEY (project_id, worker_id) REFERENCES ProjectWorker);

In this example you should have a join, in the spirit of RelatedJoin. 
Project would have a workerComment method that would return a list of
(worker, comment) tuples, and Worker and Comment would have similar
methods.

So long as a table only contains references to other tables, it should
be represented by a Join of some sort, the rows of that table should not
be Python objects.  After looking through examples, I feel increasingly
confident that this intuition is correct -- rows without content of
their own are derivative, and should be treated as such.

  Ian

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

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

On Thu, 2003-04-10 at 10:02, Bud P.Bruegger wrote:
> Another thought on the relationship of alternateID and notNull:
> 
> Doesn't the former imply the latter?  If so, maybe the interface can
> be simplified.

Yes, the two should go together.

> 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"?

No, to me NOT NULL is not the same as required.  "Required" can be
interpreted both as if the column has a default, and if NULL is
allowed.  And then it could get confused with other requirements, like a
requirement like first_name can't be empty ('').  The empty string in
many cases is just as ambiguous as NULL.

I know NULL is special in SQL, but I don't treat it as all that special,
and I know many other people don't either.  notNone would be okay,
though I don't mind the SQL analog that comes through in notNull.  I
*don't* feel NULL is the absence of a value in a general sense -- what
it means is contextual, just like None.

> 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).  

I'm reluctant to add direct hooks for table creation.  If you want close
control over your schemas it's still better to construct them manually
-- maybe at some point SQLObject will be more complete, but I don't want
to add things simply to force it to be complete.

In this case the check argument is an opaque hook to the schema creation
-- SQLObject can't understand what your asking, it can only pass it on
to the database.  That's something I want to avoid.

  Ian

Re: Globally Unique IDs, was Re: [SQLObject] Re: SQLObject

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

I second Edmund's comments: GlobalIDs have a few specific
purposes (which I personally try to avoid :)), but in the
discussion of SQLObject being specially aware / making
internal use of them, I'm opposed.

- Luke

Quoting Edmund Lian <el...@in...>:

>
> On 04/11/2003 01:47:59 PM Bud P.Bruegger wrote:
>
> >But what do you think of Globally Unique IDs?
>
> Seems unavoidable in certain situations--e.g.,
> implementing an access
> control mechanism where you want to grant/revoke rights
> to all sorts of
> data scattered across all sorts of tables in a consistent
> way. I've had to
> do this myself, and I use triggers and rules to
> generate/delete the global
> IDs.
>
> I've yet to see a need to have global IDs in any other
> situation though.
> Use of global IDs seems to arise mostly when one tries to
> force an RDBMS to
> behave like an object store.
>
> ...Edmund.
>
>
>
> -------------------------------------------------------
> 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
>


--
 Many people are hamstrung by things like affection for
 fellow employees, honesty, desire to appear to be a
 "nice person," and other crippling limitations not
 suffered by the truly powerful and successful.

Re: Globally Unique IDs, was Re: [SQLObject] Re: SQLObject

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

On 04/11/2003 01:47:59 PM Bud P.Bruegger wrote:

>But what do you think of Globally Unique IDs?

Seems unavoidable in certain situations--e.g., implementing an access
control mechanism where you want to grant/revoke rights to all sorts of
data scattered across all sorts of tables in a consistent way. I've had to
do this myself, and I use triggers and rules to generate/delete the global
IDs.

I've yet to see a need to have global IDs in any other situation though.
Use of global IDs seems to arise mostly when one tries to force an RDBMS to
behave like an object store.

...Edmund.

Re: Globally Unique IDs, was Re: [SQLObject] Re: SQLObject

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

On Fri, 11 Apr 2003 13:37:49 -0400
"Edmund Lian" <el...@in...> wrote:

> >> > 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...
> 
> This is a really bad idea. OIDs are not preserved across db dumps, and are
> optional in PG 7.3 and above, I think.
> 
> ...Edmund.

Edmund,  point taken.  I felt it was too implementation dependent
myself when I thought of it.

But what do you think of Globally Unique IDs?

--b

Re: [SQLObject] Re: SQLObject

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

On 04/11/2003 04:45:37 AM Ian wrote:

>Oh, I forgot to reply to this too.  This is definitely on my mind.  I
>also wrote FunFormKit (funformkit.sf.net), which is a form
>rendering/validation library.  I'll be the first to admit it's too
>complex, and I've wanted to reimplement it for some time... now my focus
>is on reimplementing it with SQLObject in mind (though without any
>actual ties).  It probably wouldn't be that hard to do this rendering
>right now with FFK -- Luke has mentioned at least using some stuff from
>FFK with SQLObject -- but SQLObject's design makes me happy, and I want
>the entire system to make me happy, and FFK doesn't do that for me.

Amen. Definitely needs to be refactored/rewritten! It's very hard to
extend/maintain in its current form. But, the interface to the Webware
servlet is simple (at least for v0.4 and above), which is what made me use
it instead of FormKit.

...Edmund.

Re: Globally Unique IDs, was Re: [SQLObject] Re: SQLObject

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

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

>> > 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...

This is a really bad idea. OIDs are not preserved across db dumps, and are
optional in PG 7.3 and above, I think.

...Edmund.

Re: [SQLObject] Re: SQLObject

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

On 04/10/2003 02:06:44 AM 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?

Hmmm... Here's a contrived example that typifies how I use them.

create table category (
    name     varchar(100),
    --
    constraint category_pk
        primary key (name)
);

create table item (
    name    varchar(100),
    --
    constraint item_pk
        primary key (name)
);

create table category_item_map (
    category    varchar(100),
    item        varchar (100),
    --
    constraint category_item_pk
        primary key (category, item),
    constraint category_item_category_fk
        foreign key (category) references category (name)
        on update cascade
        on delete cascade,
     constraint category_item_item_fk
        foreign key (item) references item (name)
        on update cascade
        on delete cascade
);


The argument against using real rather than surrogate primary keys (which
is what you are doing) is typically that primary keys  have business
meaning, and that changing them becomes difficult because the changes in
one table must be somehow cascade through all the other tables that use the
primary keys of one table as foreign keys.

There are really two separate issues here. Let's look at the first
one--that it is bad to have primary keys which have business meaning. Yes,
they do have business meaning, because they are integrity constraints.
That's what you want. Without integrity constraints, the data in the DB has
no meaning.

Constraints are to data as are grammar is to words. Without grammar, a page
of written text would be just a jumble of words. You derive meaning from
written text only if the reader and writer agree to certain rules of
grammar, which constrains the words to have certain order, roles, etc. To
push the point further, consider three words: "eat", "I", and "meat".
Without grammatical constraints, and of the following is possible:

meat eat I
eat meat I
meat I eat
meat eat I
I meat eat
I eat meat

Now suppose we agree to some constraints--grammatical rules. The rules we
agree to are:

Phrases must be in subject-verb-object order
Action words are verbs
Pronouns are subject nouns
+ a few other rules which I won't bore you with.

Once we constrain the words with grammar, the only valid combination is: I
eat meat

So, it is critically important to have constraints in the DB. They
determine the semantics of the data almost completely. To use surrogate
keys instead of real keys weakens the integrity constraints substantially.


The second issue--that real primary keys are troublesome because when the
business meaning changes, they have to be changed, and this is tough to do.
This argument might be true in Oracle, but it is not true in PostgreSQL and
a few other databases that support the "on update cascade" clause in
foreign key constraints.

With the table definitions I have above, an update or deletion to a primary
key in the category or item table cascade through to all the tables that
use the updated primary key as a foreign key. This is so very nice, and is
one reason why I say that Oracle is actually more backward than PostgreSQL
in some respects.

...Edmund.

[SQLObject] some changes to SQL for postgres

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

Ian, 

just ran a quick test for the check constraint that I added for
EnumCol for postgres and it seems to work.

all changes that I made are in the attached diff and listed in the
changes file.

(It seems that you changed two lines in Col.py between two of my
checkouts from CVS--but you'll recognize those, I'm sure)

let me know how these changes look

--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 Fri, 11 Apr 2003 08:23:51 -0500
Brad Bollenbach <br...@bb...> wrote:

> In either case, this will not affect anything in comments, because
> this is a table that would probably look like:
> 
> Comments
> comment_id
> comment_desc
> ...
> etc.

If this is your comments table, there are no problems.  However, the
problem I try to illustrate uses the following table:

CREATE TABLE ProjectWorkerComment(
  project_id INT,
  worker_id INT,
  comment_id INT,
  PRIMARY KEY (project_id, worker_id, comment_id),
  FOREIGN KEY (project_id, worker_id) REFERENCES ProjectWorker);

It may not be a realistic example, but I was attempting to quickly
think of something to add additional levels to the hierarchy of
composite foreign key relationships.  If you have such a hierachy,
changing relationships at a higher level (for business logic reasons)
implies that you change the composite keys of all related tables at
lower levels.  

--b

Re: [SQLObject] Re: SQLObject

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

On 04/11/03 15:08, Bud P. Bruegger wrote:
> On Fri, 11 Apr 2003 07:58:45 -0500
> Brad Bollenbach <br...@bb...> wrote:
> 
> > There's no implication in there that they do carry business
> > meaning. I.e. project_id and worker_id could easily be integers (and
> > each table could later have a name column, e.g. project_name,
> > worker_name, etc.)
> 
> Hmmm.  you're right!
> 
> But assume there are other tables referencing ProjectWorker; say
> ProjectWorkerComment.  Assume, they also use composite keys:
> 
> project_id 
> worker_id 
> comment_id 
> 
> (where (project_id, worker_id) is a foreign key referencing
> ProjectWorker
> 
> Then, if you reassign the project to another worker, you also have to
> change the composite key of the related comments.  
> 
> So you could say that the business meaning is not in the key itself
> but in the relationships you define in terms of the keys...

Hmmmm, I don't see the problem here. You have two options when
assigning a different worker to the project:

1. A simple update on worker_id, in which case all previous comments
in your comments table will now look like they refer to that new
worker_id (which may or may not be what you want).

2. Inserting a new row into ProjectWorker, which provides separation
between the comments related to each worker who's done work on the
project.

In either case, this will not affect anything in comments, because
this is a table that would probably look like:

Comments
comment_id
comment_desc
...
etc.

It doesn't care what kind of update-fu you do on the composite key of
ProjectWorker (and ProjectWorker probably doesn't care either, unless
-- of course -- the update tries to duplicate an existing
project/worker pair).

-- 
Brad Bollenbach
BBnet.ca

Re: [SQLObject] Re: SQLObject

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

On Fri, 11 Apr 2003 07:58:45 -0500
Brad Bollenbach <br...@bb...> wrote:

> There's no implication in there that they do carry business
> meaning. I.e. project_id and worker_id could easily be integers (and
> each table could later have a name column, e.g. project_name,
> worker_name, etc.)

Hmmm.  you're right!

But assume there are other tables referencing ProjectWorker; say
ProjectWorkerComment.  Assume, they also use composite keys:

project_id 
worker_id 
comment_id 

(where (project_id, worker_id) is a foreign key referencing
ProjectWorker

Then, if you reassign the project to another worker, you also have to
change the composite key of the related comments.  

So you could say that the business meaning is not in the key itself
but in the relationships you define in terms of the keys...

--b

Re: [SQLObject] Re: SQLObject

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

On 04/11/03 09:45, Bud P. Bruegger wrote:
> On Thu, 10 Apr 2003 18:22:11 -0500
> Brad Bollenbach <br...@bb...> wrote:
> 
> > 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.
> 
> But in my experience, it gets hard if these keys have to change--and
> since they carry business meaning, it is not always inevitable (people
> even in rare cases change social security numbers...).  For example,
> assume you reassign a project to another worker...  If there is a key
> without any business meaning whatsoever, changing is MUCH easier...

There's no implication in there that they do carry business
meaning. I.e. project_id and worker_id could easily be integers (and
each table could later have a name column, e.g. project_name,
worker_name, etc.)

-- 
Brad Bollenbach
BBnet.ca

[SQLObject] Re: SQLObject

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

On Thu, 2003-04-10 at 16:10, Steve Holden wrote:
> 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.

Oh, I forgot to reply to this too.  This is definitely on my mind.  I
also wrote FunFormKit (funformkit.sf.net), which is a form
rendering/validation library.  I'll be the first to admit it's too
complex, and I've wanted to reimplement it for some time... now my focus
is on reimplementing it with SQLObject in mind (though without any
actual ties).  It probably wouldn't be that hard to do this rendering
right now with FFK -- Luke has mentioned at least using some stuff from
FFK with SQLObject -- but SQLObject's design makes me happy, and I want
the entire system to make me happy, and FFK doesn't do that for me.

It's hard, though, because FFK is fairly advanced -- more advanced than
any other form processor I've seen (though I haven't done a survey of
alternatives in some time).  I want my new system to be just as
advanced, but a high bar can feel stifling (excuse the mixed metaphor).

  Ian

No more troubles [was Re: [SQLObject] More troubles]

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

Hallo,
Ian Bicking hat gesagt: // Ian Bicking wrote:
> That's a bug bug, not a version bug.  It's fixed in CVS.

Thanks a lot Ian, now with current CVS everything (that I use) seems
to be working fine on python 2.2.0 except the installation. This might
be a broken Suse-rpm, but "cp -r build/lib/SQLObject
/usr/lib/python2.2/site-packages" was all that's needed.

I'm really impressed with the ease of use that SQLObject gives.  It
saves a h*ll of a lot of typing and JustWorks(tm) for my project.
This is a survey application with Webware, where website users get
asked how they like the website. So far only the "add questions and
answers" editing interface is done, but it just took me a couple of
hours to hack this together thanks to SQLObject. I'm sure, the real
survey will be done just as fast.

In this project, performance will be not that important, because we
only have a handful of data, but I wonder how good the performance
with really big databaseses would be? Doesn't SO issue rather plenty
of selects so that performance might drop if lots of data is involved?

Also what would be a preferred way of pooling database connections for
SO in Webware? Does Webware's datapool make sense in the context of
SQLObject? I fear, that every SQLObject I use creates and drops its
own database connection, which would be bad for performance.

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

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

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

On 10 Apr 2003 13:26:27 -0500
Ian Bicking <ia...@co...> wrote:

> 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

great!  I'll have to look into this more closely...

thanks

--b

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

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

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

> 
> Hmmm... I'm not clear here.  You mean that the fields have subfields,
> like some_table.some_point.x ?  

Yes.  Say I have the attribute "location" of type "Point" in the say
"Office" class.  A Point is basically specified by a coordinate pair
(x/y or lat/long or whatever).  Currently, I could define Point as a
subclass of SQLObject and join Point and Office--but since an office
is always in a single location, this would degenerate into 1:1
relationships that are rather inefficient...  So if one could define
1:1 relationships that cause an "inlining" of the attributes or the
related object, that would be great.

> 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.

/-----------------------------------------------------------------
| 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 11 Apr 2003 01:37:10 -0500
Ian Bicking <ia...@co...> wrote:

> > Site
> > site_id PK
> > ...
> > 
> > User
> > user_id PK
> > ...
> > 
> > UserSite
> > user_id	FK
> > site_id FK 
> > (together they're the PK)
> > ...
> 
> This seems like a many-to-many relation, which SQLObject doesn't
> represent as a Python object, and which doesn't require any ID.  

The difference to many-to-many relationships (in my understanding) is
that the "breakup" table has many more columns than just necessary for
linking.  

--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 Thu, 10 Apr 2003 18:22:11 -0500
Brad Bollenbach <br...@bb...> wrote:

> 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.

But in my experience, it gets hard if these keys have to change--and
since they carry business meaning, it is not always inevitable (people
even in rare cases change social security numbers...).  For example,
assume you reassign a project to another worker...  If there is a key
without any business meaning whatsoever, changing is MUCH easier...

--b

Globally Unique IDs, was Re: [SQLObject] Re: SQLObject

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

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

> > 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).  
> 
> You mean add OIDs onto the databases that don't have it.  MiddleKit does
> this, and it's one thing that annoyed me about it, though I can
> understand the motivation.  It's an interesting idea, but I'm not sure
> how to make it work within the bounds of SQL, and in a way that's both
> useful and transparent.

Nope, the second paragraph I wrote above is totally independent of the
first...  To reword it in a more clear way (hopefully), I thought that
instead of a relatively short integer for id that makes it unique in
the table (or maybe the database?), to use a much longer id that is
globally unique (at least with a very high probability).  This is
called different names by different people...  There are algorithms
around to do it.  They typically take things like the MAC address of
the machine and the time into account...

The benefit of globally unique id's is that it makes future database
mergers and restructuring in organizations MUCH easier...

--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

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

On Thu, 2003-04-10 at 18:22, Brad Bollenbach wrote:
> 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)
> ...

This seems like a many-to-many relation, which SQLObject doesn't
represent as a Python object, and which doesn't require any ID.  

> 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.

But you can still declare the composite columns to be unique with them
being the primary key.  If it's a table that's really as derivitive as
these, I don't think the rows should be represented as Python objects. 
If RelatedJoin doesn't work like you'd need, maybe a different join
should be made, but I think that's where tables like UserSite belong.

  Ian

Re: [SQLObject] Re: SQLObject

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

On Thu, 2003-04-10 at 08:31, Bud P.Bruegger 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).

Right now I'm sure I have made the assumption it is an integer in
several places (though probably all such assumptions have been moved to
DBConnection now, which makes it easier).  It wouldn't be hard to relax
that.

> 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.  

I dunno... the object ID is special, and SQLObject treats it as
special.  It is assumed to be immutable, for instance, as any good ID
should be.  It's not a column like the others are, and I'm comfortable
with it being a special 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).  

You mean add OIDs onto the databases that don't have it.  MiddleKit does
this, and it's one thing that annoyed me about it, though I can
understand the motivation.  It's an interesting idea, but I'm not sure
how to make it work within the bounds of SQL, and in a way that's both
useful and transparent.

> > > 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.  

If it's just the method, that's trivial enough to create on your own
that it doesn't seem like a big deal.  For instance, if a and b are your
composite fields, the method would look like:

def selectAB(self, a, b): return self.selectBy(a=a, b=b)[0]

  Ian