SQLFORM vs 3rd normal form...

[technic...@googlemail.com]

Hi All,

I must be missing something, I've read the book's chapter on SQLFORM
and the bit about "Links to referencing records" but I still can't get
my head round how to make a compound form, i.e. one with fields from
multiple tables. Here's are my tables...

db.define_table('CONTACTS',
SQLField('contact_name','string',length=255, required=True),
SQLField('date_added','datetime', required=True)
)
db.CONTACTS.contact_name.requires=[ IS_NOT_EMPTY(), IS_LENGTH
(255,error_message=T('name too long, max(255)')) ]
db.CONTACTS.date_added.requires=[ IS_DATETIME() ]


db.define_table('EMAIL_ADDRESSES',
SQLField('contact_id',db.CONTACTS, required=True),
SQLField('email_address','string',length=255, required=True),
SQLField('the_default','boolean', default=False, required=True)
)
db.EMAIL_ADDRESSES.contact_id.requires=[ IS_IN_DB(db,
db.CONTACTS.id) ]
db.EMAIL_ADDRESSES.email_address.requires=IS_EMAIL()


This is my view...

def index():
cf1 = SQLFORM( db.CONTACTS )
if cf1.accepts(request.vars,session):
response.flash="Contact added."
return dict( form=cf1 )

The natural thing seemed to be to try:
cf1 = SQLFORM( db.CONTACTS, db.EMAIL_ADDRESSES )
but that barfed, so next I tried...
cf1 = SQLFORM( db.CONTACTS, db.EMAIL_ADDRESSES, fields=
["contact_name","email_address"])
which barfs also. I also tried...
cf1 = SQLFORM( db.CONTACTS.id==db.EMAIL_ADDRESSES.contact_id,
fields="contact_name" )
with the same result.

Am I missing something small or am I going about it entirely the wrong
way?

Thanks,

Roger.

[mdipierro]

what you say could be implemented but it would not be general enough
to satisfy every user.
1) why be limited to two tables
2) is this a one-to-one or one-to-many?
3) in which order the fields should be listed
4) what if the two tables have fiends with the same name
...

there are many issue that make this a non-trivial problem. Eventually
it will be done but requires some work.

Massimo

On Apr 4, 9:15 am, "web...@technicalbloke.com"

[web2py <<<at>>> technicalbloke.com]

I see that it's non-trivial but maybe 1,3 and 4 could be negated by
using the following syntax (2 I'm not so sure about)...

COMPOUNDFORM( db.TABLE1,db.TABLE2, db.TABLE3, fields=
[ db.TABLE1.name, db.TABLE2.email, db.TABLE3.phone ], relations
[ db.TABLE1.id==db.TABLE2.contact_id,
db.TABLE1.id==db.TABLE3.contact_id ] )

Anyway, if I can't use SQLFORM the issue for me now is how to achieve
this by other means. Is there a way to generate individual form fields
from the model such that the model's constraints are imposed on A) the
submitted values and B) the client side javascript? Or do I have to
duplicate these conditions at the controller level manually? Would it
be a good idea to use form_factory to build such a form?

Thanks,

Roger.

[web2py <<<at>>> technicalbloke.com]

Or is there a way to create something akin to an old fashioned
'database view' in the model, get SQLFORM to render that and then in
the controller validate the fields and insert/update the fields
manually?

Roger.


On Apr 4, 12:23 pm, "web2py <<<at>>> technicalbloke.com"

[web2py <<<at>>> technicalbloke.com]

Or maybe an addition to the INPUT helper could turn...

INPUT(_type="text", name="contact_name", requires=[ IS_NOT_EMPTY(),
IS_LENGTH(255,error_message=T('name too long, max(255)')) ]

into

INPUT(from_model="db.CONTACTS.contact_name")

?

Sorry for blathering on BTW, I'm very excited by web2py - I've wanted
to ditch PHP for Python for years and I'm hoping this is the framework
that will allow me to do that! :)

Roger.


On Apr 4, 12:39 pm, "web2py <<<at>>> technicalbloke.com"

[mdipierro]

You can do this already.

from gluon.sqlhml import form_factory
form=form_factory(...)

arguments can be SQLField, db.mytable.myfield (to clone a field in the
form), db.mytable (to clone an entire table).

Massimo

On Apr 4, 12:00 pm, "web2py <<<at>>> technicalbloke.com"

[web2py <<<at>>> technicalbloke.com]

OK, so I just cobbled this together and it seems to work though I'm
sure not in all cases...

def DBINPUT(table_field):
types = { "string":"text", "blob":"file", "boolean":"checkbox",
"integer":"text", "double":"text", "date":"text",
"time":"text", "datetime":"text",
"password":"password", "upload":"file", "reference":"file" }
return INPUT(_type=types[table_field.type],
_name=table_field.name, requires=table_field.requires)

This enables me to write this in my controller and have it pick up the
validators from the model...
form = FORM(TABLE(
TR( "Name:", DBINPUT(db.CONTACTS.contact_name) ),
TR( "Email:", DBINPUT(db.EMAIL_ADDRESSES.email_address),
DBINPUT(db.EMAIL_ADDRESSES.the_default), "(set as default)" ),
TR( "", INPUT(_type='submit',_value='SUBMIT') )
) )


Is there an easier/better way I can do this? If not, comments and
suggestions how I can improve this method would me warmly welcomed :)

Roger.


On Apr 4, 1:00 pm, "web2py <<<at>>> technicalbloke.com"

[web2py <<<at>>> technicalbloke.com]

Race condition, whoops!

Thanks for that Massimo, I knew that should be in there somewhere! :-)

On Apr 4, 3:23 pm, "web2py <<<at>>> technicalbloke.com"

[Steve Shepherd]

@Roger

Does this address any hidden fields you may want updating. ie modified date, modfied by, ID?

I like where your going...

The only problem I have with this area is we are putting visual UI into controllers.
The MVC model is supposed to put UI in the V portion.

However web2py seems to be moving more and more to constructing view elements in the controllers are with simple placement formating being defined in the views...
Just my ramblings

[mdipierro]

> However web2py seems to be moving more and more to constructing view
> elements in the controllers are with simple placement formating being
> defined in the views...

what makes you think so?

[Steve Shepherd]

Well I guess my particular need is in the ajax direction.
I guess because I am new to the framework I am still trying to establish the best place to put code.

[web2py <<<at>>> technicalbloke.com]

Hi Steve,

Erm, I'm not sure, I'm manually updating each field after verification
so I guess you can update whatever you like there, I'm pretty new to
this though so apologies if I'm missing your point! One of the issues
that arises when trying to do this, as Massimo correctly predicted
above, is the namespace clash when you have the same field name in
multiple tables. I guess the way to deal with that is to prepend the
table name onto the html input's name but for the time being I've just
made sure all the field names I might need to stick in a form are
unique in my model.

I agree, generating significant html in the controller is something to
be avoided longterm, ideally the controller would just pass all the
constituent parts up to the view. I'm pretty new to web2py but I
gather for a controller to be able to understand the returned values
from a form the form has to be generated in that controller as it also
generates other stuff like a nonce that it needs to matchup again
after submission.

I think the thing to do is to have a function just like form_factory
except that it would return a dictionary of constituent parts rather
than raw html. Something like...

return { '_labels' : [ ('Enter Name','contact_name'),
('Enter Email','email_address') ],
'email_address' : '<input class="string"
id="no_table_email_address" name="email_address" type="text" value="" /
>',
'contact_name' : '<input class="string"
id="no_table_contact_name" name="contact_name" type="text" value="" /
>',
'_form_start' : '<form action="" enctype="multipart/
form-data" method="post">',
'_form_end' : '<input name="_formkey" type="hidden"
value="afae447e-fb27-4bd1-b89c-d5bb6c130401" /></form>' }

So you could write something like this in the view...

{{ =form_bits['_form_start'] }}
Please enter a name: {{=form_bits["contact_name"]}}<br>
And an Email: {{=form_bits["email_address"]}}<br>
{{ =form_bits['_form_end'] }}

Or iterate through the "_labels" list and generate it
programatically.

I might have a look at the code later, see if it would be practical.
The promise of autogenerated autoverified forms was one of the things
that attracted me to web2py but there's no way I can use it longterm
if it's going to insist on embedding them in tables!

:)

Roger.