sqlobject-discuss

[SQLObject] PostgreSQL and Serial, foreign keys for self-reference

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

I just started a project and would like to use SQLObject.  May I ask
two questions:

1.  Does SQLObject support self-references back to the same class,
such as they occur in parent/child relationships (or hierarchies)?
Can I use the foreignKey keyword argument for that?

2. While I don't have much experience in Postgresql, I read up on the
CREATE SEQUENCE statement.  It seems there is an easier way to do this
as illustrated in the following example from
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=datatype.html#DATATYPE-SERIAL

CREATE TABLE tablename (colname SERIAL);

It seems that prior to version 7.3, SERIAL implied UNIQUE.  I checked
in older documentation (version 6.5), and SERIAL seems to be supported
even back then. 

Is there a reason for using SEQUENCE instead of SERIAL?

many thanks for your help

--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 and Serial, foreign keys for self-reference

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

On Mon, 2003-03-31 at 10:43, Bud P.Bruegger wrote:
> 1.  Does SQLObject support self-references back to the same class,
> such as they occur in parent/child relationships (or hierarchies)?
> Can I use the foreignKey keyword argument for that?

I can't see any reason it would be a problem, though I guess I haven't
thought about it specifically.   

> 2. While I don't have much experience in Postgresql, I read up on the
> CREATE SEQUENCE statement.  It seems there is an easier way to do this
> as illustrated in the following example from
> http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=datatype.html#DATATYPE-SERIAL
> 
> CREATE TABLE tablename (colname SERIAL);
> 
> It seems that prior to version 7.3, SERIAL implied UNIQUE.  I checked
> in older documentation (version 6.5), and SERIAL seems to be supported
> even back then. 
> 
> Is there a reason for using SEQUENCE instead of SERIAL?

AFAIK (but I'm not a Postgres expert), SERIAL is equivalent to creating
a sequence in the same way SQLObject expects.  Maybe it'll even work. 
It's easier for SQLObject to use the sequence directly rather than let
the ID be generated implicitly by the default, but obviously you don't
have to use the default.

  Ian

Re: [SQLObject] PostgreSQL and Serial, foreign keys for self-reference

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

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

> On Mon, 2003-03-31 at 10:43, Bud P.Bruegger wrote:

> > Is there a reason for using SEQUENCE instead of SERIAL?
> 
> AFAIK (but I'm not a Postgres expert), SERIAL is
> equivalent to creating
> a sequence in the same way SQLObject expects.  Maybe
> it'll even work.
> It's easier for SQLObject to use the sequence directly
> rather than let
> the ID be generated implicitly by the default, but
> obviously you don't
> have to use the default.

Yes, in Postgres the preferred way is to use SERIAL, and all
of our tables are generated that way. Sorry I haven't
mentioned this before Ian, and it makes some changes to your
PostgresConnection class.

When you use SERIAL in Postgres, it creates a sequence named
slightly differently than SQLObject expects now, so there
either needs to be a way to pass the sequence name, or just
change it to the SERIAL format: tablname_idname_seq (ie,
product_id_seq), slightly different from the current
"product_seq". So the postgresconnection could pull table
name and idfield name, and both request and generate the
appropriate sequence name. I'll create a patch sometime
today for this.

As far as SQLObject using this, no change is necessary
(beyond referring to the appropriate sequence mentioned
above). Calling NEXTVAL and inserting (instead of letting
the default made by SERIAL be used) works completely fine,
and works if you have both SQLObject and other DB tools
creating records.

- Luke

Re: [SQLObject] PostgreSQL and Serial, foreign keys for self-reference

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

On 31 Mar 2003 11:12:16 -0600
Ian Bicking <ia...@co...> wrote:

> AFAIK (but I'm not a Postgres expert), SERIAL is equivalent to creating
> a sequence in the same way SQLObject expects.  Maybe it'll even work. 
> It's easier for SQLObject to use the sequence directly rather than let
> the ID be generated implicitly by the default, but obviously you don't
> have to use the default.

I thought of just trying it in the CREATE statement:

CREATE TABLE person(
  id INT PRIMARY KEY SERIAL,
  ....

and will let you know if it works the same as when creating a sequence
explicitly.    (Or did I misunderstand the doc and SQLObject actually
generates the CREATE statements?).

thanks

--b

[SQLObject] possible documentation improvement

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

[I'm just starting to play with SQLObject and thought taking note of
some points may help improve the documentation.  I'm using version
0.2.1.  Maybe some of what I note is already done in CVS?]

DateTime:  
Would it help to add the library to the Requirements section?

hope this is helpful

--b

Re: [SQLObject] possible documentation improvement

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

On Tue, 2003-04-01 at 03:47, Bud P.Bruegger wrote:
> [I'm just starting to play with SQLObject and thought taking note of
> some points may help improve the documentation.  I'm using version
> 0.2.1.  Maybe some of what I note is already done in CVS?]

I haven't changed the documentation much since 0.2.1, or fixed many bugs
(I dunno if that's good or bad :), mostly been adding features.  So lots
of things will still apply. 

> DateTime:  
> Would it help to add the library to the Requirements section?

That shouldn't have been a requirement, just something that gets used if
available.  I've updated the code in CVS to be more tollerant, thanks.

  Ian