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