Indices/constraints and the DAL

[billf]

I think it would be good to start a discussion to try to identify
what, if any, functionality is missing from the DAL in the context of
indices and constraints and whether it is worth filling the gap(s).

Terminology is not always consistent but I think that the following
definitions are worthwhile:
constraint: a mechanism to restrict the content of the database
index: a mechanism to allow speedier retrieval of content from the
database

From a personal point of view, I consider the 'index' requirement
secondary to the 'constraint' requirement. So taking constraint
first:
PRIMARY: catered for as the primary key on web2py tables is always
'id'
UNIQUE: single column unique constraints are handled by the SQLField
'unique' attribute
Multi-column unique constraints are not handled
currently.
FOREIGN: handled by the 'references' attribute linking tables by 'id'
columns
Is it necessary to support other foreign constraints
(other columns, multiple columns)? Personally, I don't think so.
Other views?
CHECK: a database-level version of requires - not supported by MySQL
yet, I don't know about other dbs

Personally, I would vote to implement multi-column unique constraints
so that data integrity can be specified completely using DAL syntax
without the need for native SQL. I don't have a requirement for
different foreign keys and 'check' is perhaps sparsely supported.

RE indices:
I guess the basic requirement is to be be able to specify one or more
columns and for each column to specify ascending or descending
sequence.
The next step is to be able to specify each column as a substring.

Personally, I don't have an urgent need to support indices (other than
by executesql).

In retrospect, that seems like a long post to propose adding multi-
column unique constraints to the DAL but I'd be interested to read
other people's views.

[Fran]

On Dec 16, 10:47 am, billf <billferr...@blueyonder.co.uk> wrote:
> Personally, I would vote to implement multi-column unique constraints
> so that data integrity can be specified completely using DAL syntax
> without the need for native SQL.

I am at the stage of my app where I need to do a delete on an item
which has records in many tables.
I'd love a DAL-way of doing this without manually having to list each
of the tables which may contain an associated record.
(Since there's a large number of tables which could have such a
record)

I'm not sure what would be required to allow this?

F

[Timothy Farrell]

Fran what you're talking about is proper ON DELETE CASCADE functionality.  I've seen code to this effect in sql.py and it is mentioned on mentioned briefly on page 138 of the manual.  All you should have to do it setup your references appropriately.

-tim

-- 
Timothy Farrell <tfar...@swgen.com>
Computer Guy
Statewide General Insurance Agency (www.swgen.com)

[Timothy Farrell]

Bill,

I'm with you.  Complex Primary keys is tops on my wishlist right now.  Part of the reason I wanted a web2py wiki so much is so we could do stuff like this in PEP style.

For those unfamiliar with PEPs:
http://www.python.org/dev/peps/pep-0001/

I'm over at the wiki, starting a WEP section.

-tim

[DenesL]

Tim, wasn't this resolved in a recent thread?.
Is your definition of "Complex Primary keys" different from that?.
Just trying to understand...

[mdipierro]

Denes, the difference is that IS_NOT_IN_DB solution you refer to,
forces the constraint at the web2py label, not at the DB level.

Massimo

[Timothy Farrell]

In the thread on validating multiple fields, I was trying to use validators to get around the problem of lack of complex primary key support.  The better solution is to have true complex primary key support rather than a validator that scaffolds this restriction only when using SQLHTML helpers.

My design philosophy difference with Massimo that I mentioned is that validators should be used in the DAL for updating and inserting rather than creating the impression of a true restriction that only really exists if you use the SQLHTML helpers. 

For a DAL to be a true DAL, it needs to support a reasonable subset of the databases it abstracts.  That's what Bill and I are proposing here (Bill stop me if I'm putting words in your mouth).  Complex primary keys and the multi-field uniqueness restriction go hand-in-hand.  They are very nearly the same thing but more importantly they pave the way for restrictions to be handled by the DAL and not (necessarily only) the SQLHTML helpers.

Is that more clear?

-tim

[Timothy Farrell]

It's that "fundamental change-of-mind" that you speak of that I'm advocating.

=)

billf wrote:

In SQL, a table can only have one primary key.  Web2py insists that
the primary key of every table is 'id' and 'id' alone.
So without a fundamental change-of-mind I don't see how a complex
primary key can be supported in web2py.

That said, SQL supports multiple UNIQUE keys, both single and multi
column.  The PRIMARY key is just a special case of UNIQUE.
I believe that web2py already supports single column unique
constraints.  It is multi-column unique constraints that I want to add
to the DAL
(although they can already be created by native SQL and executesql()).

Requires/validators work as form validation rather than database
configuration but I suppose there is is nothing to stop IS_NOT_IN_DB
statements
being used to create db constraints in addition to specifying a
validator.

On Dec 16, 2:59 pm, Timothy Farrell <tfarr...@swgen.com> wrote:
  

In the thread on validating multiple fields, I was trying to use validators to get around the problem of lack of complex primary key support.  The better solution is to have true complex primary key support rather than a validator that scaffolds this restriction only when using SQLHTML helpers.
My design philosophy difference with Massimo that I mentioned is that validators should be used in the DAL for updating and inserting rather than creating the impression of a true restriction that only really exists if you use the SQLHTML helpers. 
For a DAL to be a true DAL, it needs to support a reasonable subset of the databases it abstracts.  That's what Bill and I are proposing here (Bill stop me if I'm putting words in your mouth).  Complex primary keys and the multi-field uniqueness restriction go hand-in-hand.  They are very nearly the same thing but more importantly they pave the way for restrictions to be handled by the DAL and not (necessarily only) the SQLHTML helpers.
Is that more clear?
-tim
DenesL wrote:Tim, wasn't this resolved in a recent thread?. Is your definition of "Complex Primary keys" different from that?. Just trying to understand... On Dec 16, 9:19 am, Timothy Farrell<tfarr...@swgen.com>wrote:Bill, I'm with you. Complex Primary keys is tops on my wishlist right now. Part of the reason I wanted a web2py wiki so much is so we could do stuff like this in PEP style. For those unfamiliar with PEPs:http://www.python.org/dev/peps/pep-0001/I'm over at the wiki, starting a WEP section. -tim-- Timothy Farrell<tfar...@swgen.com>Computer Guy Statewide General Insurance Agency (www.swgen.com)
    

[billf]

In SQL, a table can only have one primary key. Web2py insists that
the primary key of every table is 'id' and 'id' alone.
So without a fundamental change-of-mind I don't see how a complex

SQL only suppports one primary key per table. WEb2py insists that the
primary key for every table is 'id' and 'id' alone.
Consequently, without a fundemental change-of-mind, there is no way to
have a different primary key,
complex or otherwise, in web2py.

That said, SQL supports multiple UNIQUE keys, both single and multi
column. The PRIMARY key is just a special case of UNIQUE.
I believe that web2py already supports single column unique
constraints. It is multi-column unique constraints that I want to add
to the DAL (although they can already be created by native SQL
and executesql()).

Requires/validators work as form validation rather than database
configuration but I suppose there is is nothing to stop IS_NOT_IN_DB
statements being used to create db constraints in addition to
specifying a
validator.

In fact, although (I think I am right in saying) the CHECK constraint
is not
widely supported by database engines at present, using 'requires'
to generate constraints could, in the future, be more widely useful.

On Dec 16, 2:59 pm, Timothy Farrell <tfarr...@swgen.com> wrote:

> In the thread on validating multiple fields, I was trying to use validators to get around the problem of lack of complex primary key support.  The better solution is to have true complex primary key support rather than a validator that scaffolds this restriction only when using SQLHTML helpers.
> My design philosophy difference with Massimo that I mentioned is that validators should be used in the DAL for updating and inserting rather than creating the impression of a true restriction that only really exists if you use the SQLHTML helpers. 
> For a DAL to be a true DAL, it needs to support a reasonable subset of the databases it abstracts.  That's what Bill and I are proposing here (Bill stop me if I'm putting words in your mouth).  Complex primary keys and the multi-field uniqueness restriction go hand-in-hand.  They are very nearly the same thing but more importantly they pave the way for restrictions to be handled by the DAL and not (necessarily only) the SQLHTML helpers.
> Is that more clear?
> -tim

> DenesL wrote:Tim, wasn't this resolved in a recent thread?. Is your definition of "Complex Primary keys" different from that?. Just trying to understand... On Dec 16, 9:19 am, Timothy Farrell<tfarr...@swgen.com>wrote:Bill, I'm with you. Complex Primary keys is tops on my wishlist right now. Part of the reason I wanted a web2py wiki so much is so we could do stuff like this in PEP style. For those unfamiliar with PEPs:http://www.python.org/dev/peps/pep-0001/I'm over at the wiki, starting a WEP section. -tim-- Timothy Farrell<tfar...@swgen.com>Computer Guy Statewide General Insurance Agency (www.swgen.com)