Hello Hans -
On Mon, Mar 23, 2009 at 5:23 PM, Hans <
[email protected]> wrote:
>
> Massimo and web2py.experts,
>
> After initial successful tests with web2py we are trying to get the
> first 'little' enterprise application done with it. And like
> enterprises do we need to life with extensive use of multi column
> unique keys. Despite reading through various threads on this we could
> not get it working already with a 'simple' 2 column unique key. Either
> we loose the "drop box" option/functionality or we can't use it as one
> column of a multi column unique key. See the last 3 lines of the
> following code example. I'm sure someone here can share a working
> example of how to implement such a basic enterprise requirement in
> web2py code.
>
> Beyond that I would appreciate help on how a good code for a 3 column
> unique key would be implemented (a good example would be shapename
> +colorname+month below).
>
> Thanks,
> Hans
>
> db=SQLDB('sqlite://test.db')
>
> db.define_table('shape',
> SQLField('name'),
> migrate='shape.table')
>
> db.define_table('color',
> SQLField('name'),
> migrate='color.table')
>
> db.define_table('shapecolor',
> SQLField('shapename'),
> SQLField('colorname'),
> SQLField('month','date',requires=IS_DATE('%Y-%m')),
> migrate='shapecolor.table')
You probably mean to have these be a table holding many-to-many
relationships. If so, these should be references (that is, they should
implement foreign keys), something like this:
db.define_table('shapecolor', # note every web2py table by default
creates a unique field, 'id'
SQLField('shape_id', db.shape),
SQLField('color_id', db.color ))
# I leave the date off just because the way you have it is ok...
# I only want to show correction where I think there is a problem
>
> db.shapecolor.shapename.requires=IS_IN_DB(db,db.shape.name,'%(name)
> s')
>
Now, since a reference to another table is a reference to a row id, here is
how
this should look:
db.shapecolor.shape_id.requires=IS_IN_DB(db, db.shape.id, %(name)s )
Here is how this "reads":
shape_id must be the same as shape.id, and shape.id must exist
(but, from that row, display db.shape.name as a string, instead of
shape.id)
Try to re-phrase all of the following in a similar way, recognizing that the
foreign
key is a reference to another table's 'id' field (and so the IS_IN_DB test
will also be
against the id of that table) --- display the name for selection, as you
have.
Let us know how it goes.
Regards,
Yarko
> # 1st try
> db.shapecolor.colorname.requires=IS_IN_DB(db,db.color.name,'%(name)
> s'),IS_NOT_IN_DB(db
> (db.shapecolor.shapename==request.vars.shapename),'shapecolor.colorname')
> #<<< this breaks db.color.name dropdown
> # 2nd try
> db.shapecolor.colorname.requires=[IS_IN_DB(db,db.color.name,'%(name)
> s'),IS_NOT_IN_DB(db
> (db.shapecolor.shapename==request.vars.shapename),'shapecolor.colorname')]
> #<<< this breaks db.color.name dropdown as well
> # 3rd try
> db.shapecolor.colorname.requires=IS_IN_DB(db,db.color.name,'%(name)
> s') #<<< dropdown working but no multi column unique key!
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py Web Framework" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
