sqlobject-discuss

[SQLObject] question on SQL schemas

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

Ian and all,

I am playing with ther person.py example with postgres and would like
to ask some questions to better understand what's happening.

For reference, see the sql for creating the tables [that I dumped using
the following postgresql command: pg_dump -F p -s <database>]
(see attached file)

The person table:

CREATE TABLE person (
    id serial NOT NULL,
    username character varying(20) NOT NULL,
    first_name character varying(30) NOT NULL,
    middle_initial character varying(1),
    last_name character varying(50) NOT NULL
);

What I am surprised about is the lack of use of UNIQUE and PRIMARY KEY
that I would have expected for username (since the alternateID option
is set) and id, respectively.  Note that in PostgreSQL, serial does
not automatically set UNIQUE (see
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=datatype.html#DATATYPE-SERIAL)
Also, in the phone_number table, I would have expected person_id to
have a "REFERENCES person" option to indicate that it is a foreign
key...  

I'm not 100% sure, but wouldn't the use of UNIQUE and PRIMARY KEY also
imply that the dbms automatically creates indices and thus queries run
much faster?

thanks for the explanations!

--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] question on SQL schemas

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

On Wed, 2003-04-09 at 12:59, Bud P.Bruegger wrote:
> The person table:
> 
> CREATE TABLE person (
>     id serial NOT NULL,
>     username character varying(20) NOT NULL,
>     first_name character varying(30) NOT NULL,
>     middle_initial character varying(1),
>     last_name character varying(50) NOT NULL
> );
> 
> What I am surprised about is the lack of use of UNIQUE and PRIMARY KEY
> that I would have expected for username (since the alternateID option
> is set) and id, respectively.  Note that in PostgreSQL, serial does
> not automatically set UNIQUE (see
> http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=datatype.html#DATATYPE-SERIAL)
> Also, in the phone_number table, I would have expected person_id to
> have a "REFERENCES person" option to indicate that it is a foreign
> key...  

Quite true, quite true.  I thought SERIAL implied PRIMARY KEY, but
apparently not.  I'll change the ID column to be SERIAL PRIMARY KEY.

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

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.

  Ian

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

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