Will tink about 1.
about 2. You can do
db.define_table(...,Field('name','reference
blabla',ondelete='cascade'))
cascade is default.
On Nov 26, 8:39 am, Mirek Zvolský <zvol...@seznam.cz> wrote:
> >> 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
