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.
