>> Could you elaborate on what you'd expect from
>> join expressions aren't automatically created based on the DAL definition
1) - answer to your question
2) - idea to deleting integrity - I think this could be very useful
and easy to add to web2py core
1)
SQL language is very clever, but I always wondered, why millions of
developer always thousand times have to write:
JOIN dog ON dog.person_id=person.id
or in web2y:
db(db.dog.person_id==person.id)
if they want records from both tables. Because this "connecting"
expression is already known from the database definition (from
definition of foreign keys).
So, it would be nice, if we have tool, which can create such
expressions automatically, when we ask for fields/records from linked
tables.
I don't know, if this idea is implemented somewhere in Java,
Microsofts, python,.. utilities.
I have implemented it partially in Visual FoxPro, where my ideas were
following:
Each SQL command has its "basic" table (in FROM..) and "linked" tables
(in JOINs). Inside linked tables one table from database can however
occur more times (we need more than one table alias for table), from 2
reasons:
-- table can have 2 or more foreign keys pointing to same table,
-- through more joins we can come back to an already used table /I
know you don't believe this much :-)/
So, I decided to find possibility, how I can name all possible table
aliases in automatic way. I had a metadata table of all joins in
database, so there were primary keys join.id
I created table aliases like: dog ("basic" table), dog_15 ("linked"
table person", join.id==15 was primary key of the foreign key
dogs.preson_id in metadata).
That way I can created all possible table aliases, and the name isn't
very long, f.e. dog_15_4_8 is shorter as
dog__dog_person_id__person_city_id__city_country_id
>From metadata table I can then take all what I need to automatically
create join expressions: table1.foreignkey==table2.primarykey
So I can create query designers, where user can choose everything in
database (every field in every table) - he can always choose from
fields in current table + from joins outgoing from current table (list
of joins I can get easy, when I select records (means joins) from
metadata table WHERE table=currenttable). And whatever user will
choose, SQL SELECT will be created automaticaly to receive required
data.
But I stopped this work in Visual FoxPro and switched to web2py. I
would like to continue sometimes later with this in web2py, to create
a plugin for this. Maybe after 6 months, but now I'm new in python and
web2py to do such work. Or maybe somebody here can try go in this way,
if he is interested...
If in web2py, maybe there is not necessary to use metadata table of
joins.
Maybe this can be implemented as properties of foreign key, f.e.:
db.dog.person_id.joinname='15' # just a symbol to create aliases;
target table we know from 'reference..' field type, target key is
always primary key .id
2)
Similar it would be nice to implement the deleting integrity at the
application level:
db.dog.person_id.ondelete='setnull' # 'setnull' (default?),
'cascade', 'restrict' - says what to do when person (dog owner) is to
be deleted
