Solution 1 isn't really viable because you'd be growing the number of
columns in program_param indefinitely, and Solution 2 probably isn't a good
idea either, as you will keep adding new tables for every program. I assume
you want either a table per program or one massive table with columns for
each program's parameters so you can get a SQLFORM to enter the parameters
for each program. There are better ways to do that (though involving a bit
more programming).
For example, to generate a form for entering the parameters for a given
program, query the program_ax table to get a list of parameters for the
program. Then loop through those parameters and generate a Field object for
each one (specifying the appropriate type, default, and any validators).
Finally, generate a SQLFORM.factory form using those Field objects.
Once the form has been submitted and validated, instead of storing each
individual parameter in a separate field of some database table, store the
entire set of parameters in a single JSON field. Not tested, but something
like this:
db.define_table('program_param',
Field('program', 'reference program'),
Field('params', 'json'))
def run_program():
program = request.args(0, cast=int)
params = db(db.program.id == program).select()
fields = [Field(p.param_header, p.data_type, default=p.default_value,comment
=p.help)
for p in params]
form = SQLFORM.factory(*fields)
if form.process().accepted:
db.program_param.insert(program=program, params=form.vars)
return dict(form=form)
You would need some additional logic to convert the default values to the
appropriate data type (for example, for integer parameters, you would have
to cast the default value to an integer, given that you store all defaults
as strings in the program_ax table). Also, instead of storing form.vars as
is in the params field, you could first remove the _formkey and _formname
hidden field variables so they don't get stored with the actual program
parameters.
Anthony
On Friday, February 7, 2014 3:00:48 PM UTC-5, Andrey K wrote:
>
> Thanks Anthony.
> Based on your anser I can say that my problem comes from my design of the
> db. As the requirements have just changed and I have to provide ability to
> each user to add columns - so it would need to be adopted for regular
> basis. In addition I have checked limit of most db in max table column
> number and was shocked with figure equals to only 1600 fields. So I need to
> redesign db.
> I really appreciate your help, it would great if you could comment on the
> following issue.
>
> Maybe you know what would be the best way to tackle following task:
> I have a list of programs that will grows over time. Each program has
> different number of parameters with afferent value types and default
> values. Program runs by user who specify parameters or leave default ones.
> I would like to create as small number of tables as possible to make it
> happen as the number of programs can be indefinite.
> In addition to the task I would like to use w2p db field value validation.
>
>
> I had two main db structure solutions for the above task:
>
> Solution #1 (I used before and i need to change now):
> program - stores name and description of the tools
> db.define_table('program',
> Field('name', 'string'),
> Field('description', 'text'))
>
> tool_ax - stores param properties for the UI. it also contains
> param_header field that stores reference to program_param fields such as
> programA1, programA2,programB2 and enable linking 3 tables together.
>
> db.define_table('program_ax',
> Field('program', 'reference program'),
> Field('param_header', 'string'),
> Field('default_value', 'string')
> Field('data_type', 'string'),
> Field('comment', 'string'),
> Field('help', 'text'))
>
> tool_param - stores parameters of the program in a way of default value
> and data type which can be used in w2p validation.
>
> db.define_table('program_param',
> Field('program', 'reference program'),
> Field('programA1'),
> Field('programA2'),
> Field('programA3'),
> Field('programB1'),
> Field('programB2'),
> Field('programC1'))
>
> Using this approach I can easily stores as many program and parameters I
> want in 3 tables plus I can use web2py db validation.
>
> Solution #2 - I had refused to take before:
> -----------------
> db.define_table('program',
> Field('name', 'string'),
> Field('description', 'text'))
>
> I have separate table for each program which would contain its own
> parameters and reference to the program table:
>
> db.define_table('program1',
> Field('program', 'reference program'),
> Field('param1', 'string', default = d1, comment=c1,
> label=l1),
> Field('param2', 'integer', default = d2, comment=c2,
> label=l2),
> )
> db.define_table('program2',
> Field('program', 'reference program'),
> Field('param1', 'double', default = d11, comment=c11,
> label=l11),
> Field('param2', 'integer', default = d22, comment=c22,
> label=l22),
> Field('param3', 'integer', default = d33, comment=c33,
> label=l33),
> )
>
> I would really appreciate any comment from you.
>
> On Friday, February 7, 2014 9:39:00 PM UTC+3, Anthony wrote:
>>
>> db = DAL('sqlite://se2.sqlite',check_reserved=['postgres',
>>> 'postgres_nonreserved'],pool_size=10,lazy_tables=False, migrate=True,
>>> fake_migrate=False)
>>> now I got right results:
>>> >>> db.ta._fields
>>>
>>>
>>> ['id', 'f1', 'newfield']
>>>
>>> *BUT it does not work in the web app*. session.flash = db.ta._fields -
>>> giving me right result (['id', 'f1', 'newfield']) BUT in the table (app
>>> /appadmin) and SQLFORM.grid I don't have any change. Any idea why it is
>>> not working in the app?
>>>
>>
>> A DAL model exists only during a single request. If you create a model in
>> one request, web2py won't know anything about that model in a subsequent
>> request, unless that model is redefined on each request (of course the
>> database table itself will persist, but on each request, you still need to
>> generate a model of that table so web2py knows how to interact with the
>> database).
>>
>> Do you really need to allow database tables to be changed arbitrarily on
>> a continual basis, or is there just some initial setup process to define
>> the table structure? If the latter, you might consider an alternative for
>> specifying a custom but fixed table definition on a one-time basis.
>>
>> If you really need dynamic definitions, a better approach would probably
>> be to store the metadata needed for the table definitions in a Python data
>> structure (e.g., a dictionary of dictionaries). Then, to define the table
>> in a model, parse the data structure to generate the arguments to
>> db.define_table(). To get an idea what this might look like, check out the
>> output of db.sometable.as_dict(). Note, the data structure could be stored
>> in a JSON table in the database.
>>
>> Another option would be to programmatically generate an actual model file
>> (you could even use the web2py template language to do so). Whenever the
>> table definition needs to change, re-generate the relevant model file. This
>> avoids the need to retrieve and parse the table metadata in order to define
>> the table on each request.
>>
>> Anthony
>>
>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
