Reference a table before its definition

[Paolo Valleri]

See https://github.com/web2py/web2py/issues/699

Using lazy_tables=False, it isn't possible to referee a table before its definition.

If I remember correctly it worked in older web2py versions.

Paolo

[Niphlod]

previous version didn't take into ANY account foreign keys.
The problem popped up when working at the rname feature, and frankly got me and mcm pretty scared.

The problem is quite known, and people continue to reiterate on "it worked in previous versions".... well, it worked, but silently failed to create FK, without telling a soul (not even a warning).
IMHO now it's better than before, and I'm glad something that isn't supported is clearly reported as impossible to work with.

small recap of the issue:

you start from

db.define_table('abc', Field('name'))
db.define_table('cde', Field('name2', 'reference 'abc'))

perfectly fine. Now, for some weird reason, you want to

db.define_table('abc', Field('name'), Field('guesswhat', 'reference cde'))
db.define_table('cde', Field('name2', 'reference 'abc'))

Could be wonderful, but there's a catch.......in the sense that if you start with a model and finish with another one, SOME migration paths will happily alter the 'abc' table adding a guesswhat column that references a table that is already there.......

But then, you move to another (fresh) db. Guess what ? Your model now is

db.define_table('abc', Field('name'), Field('guesswhat', 'reference cde'))
db.define_table('cde', Field('name2', 'reference 'abc'))

from the beginning. Of course it fails BADLY: the migration logic does steps atomically from table to table, so at the moment that 'abc' is created, there is no 'cde'.

What "older" web2py did ? It created an 'abc' table with a 'guesswhat' column without FK, and happily lived ever after thinking that a IS_IN_DB(db, 'cde.id') would suffice.


Solutions ? Already proposed, but never implemented, delay on creating FK relations (and/or unique constraint, that solve another related issue) after the last table is defined. In this way, define_table() creates tables without enforcing FK up until (supposedly) all tables are created. This would fix also auto_import, that takes table definitions in alphabetical order.
One small hiccup with all of this is that there isn't a place in DAL that tells that there won't be more tables added to the model....
 

[Paolo Valleri]

Now the problems are clear. The first idea I had is the following:

- create all FK after all modules has been executed

- create a new metodo db.create_fk(), called explicitly by the developer to generate FK in those situations where tables are defined elsewhere (such controller, modules etc).

Is it visible ?

 Paolo

--
-- mail from:GoogleGroups "web2py-developers" mailing list
make speech: web2py-d...@googlegroups.com
unsubscribe: web2py-develop...@googlegroups.com
details : http://groups.google.com/group/web2py-developers
the project: http://code.google.com/p/web2py/
official : http://www.web2py.com/
---
You received this message because you are subscribed to the Google Groups "web2py-developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py-develop...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Richard Vézina]

Hello,

Funny because, this morning I actually fix a answer on SO where the model was not define using 'reference tablename'...

Everything is in Simone email... If it can be fixed rapidly, just do it as Paolo suggest... But if there is more consideration to take inton account, here what I would do...

1) Fix the doc, stop pretend that 'reference tablename' will create FK for you in every situation (I can fix the doc about that and make a PR on the book)

    Something like : It is prefered to use Field('fieldname', 'reference tablename') instead of Field('fieldname', db.tablename) because it solves alaphabetical blabla model definition... But in some situation you may be fooled to think that backend FK have been enforced, because if a refered table is not define at the moment your model is first migrated web2py won't be able to add the FK later... Blabla... Then the workaround that at least prevent an null input and a wrong value "IS_IN_DB(...)"... Advised developper should make sure his FK are actually defined checking at backend table definition....

2) We can even talk about the issue and say that the gold is to make sure FK get defined properly in the future if we use 'reference tablename'...

So we don't induce users into error and we have time to workaround the issue or even let it at won't fix if it comes to the point where it can't be solved or if we solved we overly complicate thing...

What do you think?

Richard

[Niphlod]

On Tuesday, March 10, 2015 at 1:24:56 AM UTC+1, Richard wrote:

Hello,

Funny because, this morning I actually fix a answer on SO where the model was not define using 'reference tablename'...

Everything is in Simone email... If it can be fixed rapidly, just do it as Paolo suggest... But if there is more consideration to take inton account, here what I would do...

1) Fix the doc, stop pretend that 'reference tablename' will create FK for you in every situation (I can fix the doc about that and make a PR on the book)

it'll work, as long as the table you reference is already there

 

    Something like : It is prefered to use Field('fieldname', 'reference tablename') instead of Field('fieldname', db.tablename) because it solves alaphabetical blabla model definition... But in some situation you may be fooled to think that backend FK have been enforced, because if a refered table is not define at the moment your model is first migrated web2py won't be able to add the FK later... Blabla... Then the workaround that at least prevent an null input and a wrong value "IS_IN_DB(...)"... Advised developper should make sure his FK are actually defined checking at backend table definition....

Don't start to stir up and mix things.... although I'd strip the whole Field('abc', db.something) notation, the fact that you can't use db.something before actually defining something has to do with python, not DAL.

As it is right now DAL enforces FK everywhere (to my knowledge), else it breaks. It doesn't get more honest than this.

[Richard Vézina]

So? I fix the doc?

Richard

--

[Niphlod]

what's to fix ?

[Richard Vézina]

The doc...

[Niphlod]

ok, I'll rephrase: is there anything in the docs that states something wrong or you just want to add something ?

[Richard Vézina]

Doc review :

DAL chapter, Field type section :

Can we say that :

field type default field validators

reference <table>

IS_IN_DB(db,table.field,format)

In this section (still DAL chapter) :

One to many relation

To illustrate how to implement one to many relations with the web2py DAL, define another table "thing" that refers to the table "person" which we redefine here:

>>> db.define_table('person',
                    Field('name'),
                    format='%(name)s')
>>> db.define_table('thing',
                    Field('name'),
                    Field('owner_id', 'reference person'),
                    format='%(name)s')

Table "thing" has two fields, the name of the thing and the owner of the thing. The "owner_id" field id a reference field. A reference type can be specified in two equivalent ways:

Field('owner_id', 'reference person')
Field('owner_id', db.person)

The latter is always converted to the former. They are equivalent except in the case of lazy tables, self references or other types of cyclic references where the former notation is the only allowed notation.

When a field type is another table, it is intended that the field reference the other table by its id. In fact, you can print the actual type value and get:

>>> print db.thing.owner_id.type
reference person

Now, insert three things, two owned by Alex and one by Bob:

>>> db.thing.insert(name='Boat', owner_id=1)
1
>>> db.thing.insert(name='Chair', owner_id=1)
2
>>> db.thing.insert(name='Shoes', owner_id=2)
3

You can select as you did for any other table:

>>> for row in db(db.thing.owner_id==1).select():
        print row.name
Boat
Chair

Because a thing has a reference to a person, a person can have many things, so a record of table person now acquires a new attribute thing, which is a Set, that defines the things of that person. This allows looping over all persons and fetching their things easily:

>>> for person in db().select(db.person.ALL):
        print person.name
        for thing in person.thing.select():
            print '    ', thing.name
Alex
     Boat
     Chair
Bob
     Shoes
Carl




There is no mention of the possible issues you describe... Nor which issue reference is intent to resolve (python alphanumeric module load and models order in models files)... It used to be explained before if I recall...




So why : They are equivalent except in the case of lazy tables, self references or other types of cyclic references where the former notation is the only allowed notation.

I would add a couples of lines starting from there... Better explain why reference is required in those cases... And how it failed sometimes because the actual DAL implementation.

What do you think?

Richard

On Tue, Mar 10, 2015 at 4:34 PM, Niphlod <nip...@gmail.com> wrote:

ok, I'll rephrase: is there anything in the docs that states something wrong or you just want to add something ?

--

[Niphlod]

I don't see any errors on it. Show what you'd like to put in it so we can comment.

[Paolo Valleri]

I'm moving the discussion we have on https://github.com/web2py/web2py/issues/699 here.

To sum up, I've implemented a 'deferred FK creation'. The idea is simple. if a table has a FK to a non already defined table, the FK definition is removed from the sql CREATE TABLE (...). As soon as the referenced table is created, a migration for the former table is triggered (aka ALTER TABLE). Everything seems to work well, see: https://github.com/ilvalle/pydal/tree/deferred-references

Issues:

- sqlite:memory doesn't work because it doesn't support migration

- FK pointing to a non-existing table (aka typo or other reasons), the user'll get an exception when he tries to write something on that field instead of at table definition.

 Paolo

2015-03-12 9:04 GMT+01:00 Niphlod <nip...@gmail.com>:

I don't see any errors on it. Show what you'd like to put in it so we can comment.

--