Richard,
thanks for sharing! Although I don't see how that would help in my case
(I'd still have to comment the field in my db.py file) it's good to see
some new approaches.
Alex
Am Donnerstag, 14. März 2013 15:08:39 UTC+1 schrieb Richard:
>
> Hello Alex,
>
> Here we go...
>
> *1) Get all the tables :
>
> *
> *I create view in Pg for that :*
>
> CREATE OR REPLACE VIEW v_dict_table AS
> ( ( SELECT pg_class.relname, pg_class.oid
> FROM pg_class
> WHERE pg_class.relnamespace = 2200::oid AND
> pg_class.relname ~~ 'prefix1_%'::text AND pg_class.relname !~~
> '%_id_seq'::text AND pg_class.relname !~~ '%_idx'::text AND
> pg_class.relname !~~ '%_pkey'::text AND pg_class.relname !~ '_id$'::text
> UNION
> SELECT pg_class.relname, pg_class.oid
> FROM pg_class
> WHERE pg_class.relnamespace = 2200::oid AND
> pg_class.relname ~~ 'prefix2_%'::text AND pg_class.relname !~~
> '%_id_seq'::text AND pg_class.relname !~~ '%_idx'::text AND
> pg_class.relname !~~ '%_pkey'::text AND pg_class.relname !~ '_id$'::text)
>
> *-- NOTE : Add more UNION if you have multiple tables prefix*
>
> ORDER BY 1;
>
> ALTER TABLE v_dict_table
> OWNER TO owner;
>
> *2) Get all the table and fields :*
> *
> *
> *I create an other view for this :*
>
> CREATE OR REPLACE VIEW v_dict_database AS
> SELECT v_dict_table.relname, v_dict_table.oid, pg_attribute.attname
> FROM pg_attribute
> JOIN v_dict_table ON pg_attribute.attrelid = v_dict_table.oid AND
> pg_attribute.attname !~~ '........pg.dropped.%'::text AND NOT
> pg_attribute.attname = 'tableoid'::name AND NOT pg_attribute.attname =
> 'ctid'::name AND NOT pg_attribute.attname = 'xmin'::name AND NOT
> pg_attribute.attname = 'cmin'::name AND NOT pg_attribute.attname =
> 'xmax'::name AND NOT pg_attribute.attname = 'cmax'::name
> ORDER BY v_dict_table.relname, pg_attribute.attname;
>
> ALTER TABLE v_dict_database
> OWNER TO owner;
>
>
> *Now we have an autogenerating list of every tables and fields contains
> in or database, as long as the prefix domain is finished, if you keep
> adding new prefix you will have to update your views.*
>
> *3) Then we need to create or dict_database table it can't be view since
> we want to write stuff in in*
> *
> *
> *Here we make a backup of the actual dict_database to keep ord added
> stuff (the first time this command should not be used)*
>
> *-- The lines below are part of a SQL script that you run each time you
> add tables or columns in your table to update your dict_database table
> (recreate it and import the description you gave about your tables and
> fields, labels, comments, etc.)*
>
> DROP TABLE IF EXISTS dict_database_backup;
> CREATE TABLE dict_database_backup AS SELECT * FROM dict_database;
>
> -- Drop old version of "dict_*" tables
> DROP TABLE dict_database;
>
> CREATE TABLE dict_database
> (
> database_id serial NOT NULL,
> oid oid,
> table_name character varying NOT NULL,
> table_name_fr_report character varying,
> table_name_en_report character varying,
> table_name_fr_ui character varying,
> table_name_en_ui character varying,
> table_description_fr text,
> table_description_en text,
> column_name character varying NOT NULL,
> column_name_fr_ui character varying,
> column_name_en_ui character varying,
> column_user_help_text_fr text,
> column_user_help_text_en text,
> column_placeholder_text_fr text,
> column_placeholder_text_en text,
> column_help_bubble_fr text,
> column_help_bubble_en text,
> unit_id integer,
> column_type integer,
> column_description_fr text,
> column_description_en text,
> column_alias1 character varying,
> column_alias2 character varying,
> column_alias3 character varying,
> CONSTRAINT database_id PRIMARY KEY (database_id),
> CONSTRAINT unit_id FOREIGN KEY (unit_id)
> REFERENCES ref_unit (id) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION
> )
> WITH (
> OIDS=FALSE
> );
> ALTER TABLE dict_database OWNER TO owner;
>
> *-- You populate dict_database with table and column base on the views in
> Step 1 and 2*
>
> INSERT INTO dict_database (table_name, oid, column_name)
> SELECT relname AS table_name, oid, attname AS column_name
> FROM public.v_dict_database
> ORDER BY 1,3;
>
> *-- Then you get back your added stuff (the first time you can't use it
> obviously) *
>
> UPDATE dict_database
> SET table_name_fr_report = (SELECT table_name_fr_report FROM
> dict_database_backup WHERE
> dict_database.column_name=dict_database_backup.column_name AND
> dict_database.table_name=dict_database_backup.table_name),
> table_name_en_report = (SELECT table_name_en_report FROM
> dict_database_backup WHERE
> dict_database.column_name=dict_database_backup.column_name AND
> dict_database.table_name=dict_database_backup.table_name),
> table_name_fr_ui = (SELECT table_name_fr_ui FROM dict_database_backup
> WHERE dict_database.column_name=dict_database_backup.column_name AND
> dict_database.table_name=dict_database_backup.table_name),
> table_name_en_ui = (SELECT table_name_en_ui FROM dict_database_backup
> WHERE dict_database.column_name=dict_database_backup.column_name AND
> dict_database.table_name=dict_database_backup.table_name),
> table_description_fr = (SELECT table_description_fr FROM
> dict_database_backup WHERE
> dict_database.column_name=dict_database_backup.column_name AND
> dict_database.table_name=dict_database_backup.table_name),
> table_description_en = (SELECT table_description_en FROM
> dict_database_backup WHERE
> dict_database.column_name=dict_database_backup.column_name AND
> dict_database.table_name=dict_database_backup.table_name),
> column_name_fr_ui = (SELECT column_name_fr_ui FROM
> dict_database_backup WHERE
> dict_database.column_name=dict_database_backup.column_name AND
> dict_database.table_name=dict_database_backup.table_name),
> column_name_en_ui = (SELECT column_name_en_ui FROM
> dict_database_backup WHERE
> dict_database.column_name=dict_database_backup.column_name AND
> dict_database.table_name=dict_database_backup.table_name),
> column_alias1 = (SELECT column_alias1 FROM dict_database_backup WHERE
> dict_database.column_name=dict_database_backup.column_name AND
> dict_database.table_name=dict_database_backup.table_name),
> column_alias2 = (SELECT column_alias2 FROM dict_database_backup WHERE
> dict_database.column_name=dict_database_backup.column_name AND
> dict_database.table_name=dict_database_backup.table_name),
> column_alias3 = (SELECT column_alias3 FROM dict_database_backup WHERE
> dict_database.column_name=dict_database_backup.column_name AND
> dict_database.table_name=dict_database_backup.table_name),
> column_description_fr = (SELECT column_description_fr FROM
> dict_database_backup WHERE
> dict_database.column_name=dict_database_backup.column_name AND
> dict_database.table_name=dict_database_backup.table_name),
> column_description_en = (SELECT column_description_en FROM
> dict_database_backup WHERE
> dict_database.column_name=dict_database_backup.column_name AND
> dict_database.table_name=dict_database_backup.table_name),
> column_user_help_text_fr = (SELECT column_user_help_text_fr FROM
> dict_database_backup WHERE
> dict_database.column_name=dict_database_backup.column_name AND
> dict_database.table_name=dict_database_backup.table_name),
> column_user_help_text_en = (SELECT column_user_help_text_en FROM
> dict_database_backup WHERE
> dict_database.column_name=dict_database_backup.column_name AND
> dict_database.table_name=dict_database_backup.table_name),
> unit_id = (SELECT unit_id FROM dict_database_backup WHERE
> dict_database.column_name=dict_database_backup.column_name AND
> dict_database.table_name=dict_database_backup.table_name),
> column_placeholder_text_fr = (SELECT column_placeholder_text_fr FROM
> dict_database_backup WHERE
> dict_database.column_name=dict_database_backup.column_name AND
> dict_database.table_name=dict_database_backup.table_name),
> column_placeholder_text_en = (SELECT column_placeholder_text_en FROM
> dict_database_backup WHERE
> dict_database.column_name=dict_database_backup.column_name AND
> dict_database.table_name=dict_database_backup.table_name),
> column_help_bubble_fr = (SELECT column_help_bubble_fr FROM
> dict_database_backup WHERE
> dict_database.column_name=dict_database_backup.column_name AND
> dict_database.table_name=dict_database_backup.table_name),
> column_help_bubble_en = (SELECT column_help_bubble_en FROM
> dict_database_backup WHERE
> dict_database.column_name=dict_database_backup.column_name AND
> dict_database.table_name=dict_database_backup.table_name);
>
> *4) You build you r python dict in models of modules in web2py*
>
> *4a) First you need to define you dict_database model*
>
> db.define_table('dict_database',
> Field('database_id','id', writable=False),
> Field('table_name','string', notnull=True, writable=False),
> Field('table_name_fr_report','string'),
> Field('table_name_en_report','string'),
> Field('table_name_fr_ui','string'),
> Field('table_name_en_ui','string'),
> Field('table_description_fr','text'),
> Field('table_description_en','text'),
> Field('column_name','string', notnull=True, writable=False),
> Field('column_name_fr_ui','string'),
> Field('column_name_en_ui','string'),
> Field('column_description_fr','text'),
> Field('column_description_en','text'),
> Field('column_user_help_text_fr','text'),
> Field('column_user_help_text_en','text'),
> Field('column_placeholder_text_fr','text'),
> Field('column_placeholder_text_en','text'),
> Field('column_help_bubble_fr','text'),
> Field('column_help_bubble_en','text'),
> Field('column_type','integer', writable=False),
> Field('unit_id','integer',
> requires=IS_EMPTY_OR(IS_IN_DB(db, 'ref_unit.id',
> db.ref_unit._format))
> ),
> migrate=False,
> sequence_name='dict_database_database_id_seq')
>
> *4b) Then cached python dict*
>
> en_ui_tables_names = cache.ram('en_ui_tables_names',
> lambda: dict([(r.table_name,r.table_name_en_ui)\
> for r in db().select(db.dict_database.table_name,
> db.dict_database.table_name_en_ui)]),
> time_expire=3600)
>
> *5) You use these information to create your models *
> *
> *
> *I use this in controller that way it not get it on each request only
> when the table is required :*
> *
> *
> import common_functions
> if request.args(0) in tables_groups_list_dict['prefix1_tables_list']:
> [ common_functions.set_labels_comments_placeholders_tooltips(db,
> dblabels_en, dbhelp_comments, request.args(0), field, T,
> dbplaceholders,
> dbtooltips, dbunit, request, app_unit_widget=app_unit_widget) for
> field in db[request.args(0)].fields ]
>
> *The set_labels_comments_placeholders_tooltips() goes like that :*
> def set_labels_comments_placeholders_tooltips(db, dblabels,
> dbhelp_comments,
> table, field, T, dbplaceholders, dbtooltips, dbunit, request,
> app_unit_widget):
> concat = table+field
> if dblabels[concat]!='' and dblabels[concat]!=None:
> if request.function == 'create' \
> or request.function == 'update' :
> if dbtooltips[concat]!='' and dbtooltips[concat]!=None:
> db[table][field].label = CAT(T(dblabels[concat]), SPAN('
> '),
> A(I(_class='icon-info-sign'), _href='##',
> _rel='popover',
> **{'_data-placement': 'right',
> '_data-content':
> xmlescape(T(dbtooltips[concat]))}))
> else:
> db[table][field].label = T(dblabels[concat])
> else:
> db[table][field].label = T(dblabels[concat])
> if dbhelp_comments[concat]!='' and dbhelp_comments[concat]!=None:
> db[table][field].comment = T(dbhelp_comments[concat])
> if db[table][field].type == 'text':
> if dbplaceholders[concat]!='' and dbplaceholders[concat]!=None:
> db[table][field].widget = lambda field, value: \
> SQLFORM.widgets.text.widget(field, value,
> _placeholder=T(dbplaceholders[concat]))
> if db[table][field].type == 'string':
> if dbplaceholders[concat]!='' and dbplaceholders[concat]!=None:
> db[table][field].widget = lambda field, value: \
> SQLFORM.widgets.string.widget(field, value,
> _placeholder=T(dbplaceholders[concat]))
> if dbunit[concat]!=""''"" and dbunit[concat]!=None:
> db[table][field].widget = \
> lambda field, value: app_unit_widget(field, value,
> unit_add_on=dbunit[concat])
>
>
> *I also use this to update web2py translation file automatically so I
> manage all the labels, help comments, etc. at the level of the database. *
> *
> *
> *I also interfaced the dict_database table with crud and crud.search so I
> can easily change the label of a field or a table. All this is really handy
> in my case during developping because my project is a kind of really Agile
> one and I never have the field label and the rest of the information I
> require when I create the models, so it allow me to react really rapidly
> without having to go back in the code when the client want to change a
> field label or help comment, etc. I can also let a non-developper manage
> this part of the project after a little training (event the client).*
> *
> *
> *In the future I plan to make my dict_database update automatically, now
> I have to execute the script manually. Of course, you can add the columns
> you want in dict_database. I know the design is not perfect in case of
> aliasing for a given field label for instance (you need to keep adding
> columns and refactor you code), but it works.*
> *
> *
> *I would appreciate if you improve this recipe that you share here your
> experience so I could improve also mine...*
> *
> *
> *Any comments is appreciate.*
>
> :)
>
> Richard
>
>
> On Tue, Mar 12, 2013 at 9:37 PM, Richard Vézina
> <ml.richa...@gmail.com<javascript:>
> > wrote:
>
>> I will show you some code tomorrow.
>>
>> :)
>>
>> Richard
>>
>>
>> On Tue, Mar 12, 2013 at 7:51 PM, Alex <mrau...@gmail.com <javascript:>>wrote:
>>
>>> Richard,
>>>
>>> this sounds interesting but I did not fully understand what you're
>>> doing. Some code would probably help.
>>>
>>> thanks,
>>> Alex
>>>
>>> Am Dienstag, 12. März 2013 16:24:06 UTC+1 schrieb Richard:
>>>>
>>>> Alex,
>>>>
>>>> Maybe you could be interested to know how manage labels, comments, etc.
>>>>
>>>> I create a table name database_dict that contain the list of all my
>>>> fields and table where I create all the columns I want to store anything I
>>>> want concerning fields and tables, like : table_description,
>>>> field_description, ui_table_name, ui_table_name_fr, ui_table_name_en,
>>>> field_comment_fr, field_comment_en, field_label_fr, field_label_en, etc.,
>>>> etc.
>>>>
>>>> I made a update script that recreate this table each time I want and
>>>> copy the older descriptions, labels, comments in the new table with the
>>>> new
>>>> fields and tables that I create in my database.
>>>>
>>>> Then in web2py I create a cached dict(s) that contains all the
>>>> information that is available and that I need to create my models, so in
>>>> my
>>>> models I am refering to the python dict(s) that contains the things I want.
>>>>
>>>> That way I can manage field label translation at the database level and
>>>> document my field and table there too...
>>>>
>>>>
>>>> If you are interrested I can elaborate more and provide some code for
>>>> Postgres...
>>>>
>>>> Richard
>>>>
>>>>
>>>>
>>>> On Tue, Mar 12, 2013 at 11:06 AM, Alex <mrau...@gmail.com> wrote:
>>>>
>>>>> something like a dbcomment argument would be really nice. Of course
>>>>> the implementation has to be db engine specific. In case the db does not
>>>>> support column comments then it should be ignored. For now I'll keep on
>>>>> updating the comments manually, hopefully it will be supported in the
>>>>> future :)
>>>>>
>>>>> Am Dienstag, 12. März 2013 15:37:02 UTC+1 schrieb Niphlod:
>>>>>
>>>>>> correct. Unless someone scripts something specific (shouldn't be
>>>>>> hard), but as far as I know it would have to be pretty specific to every
>>>>>> db
>>>>>> engine (e.g. I don't think mongo has column comments ^_^)
>>>>>>
>>>>>> On Tuesday, March 12, 2013 3:22:22 PM UTC+1, Alex wrote:
>>>>>>>
>>>>>>> actually I don't need the comment in the backend but I want to
>>>>>>> comment my code. Often it is useful for me to have the comment in the
>>>>>>> db as
>>>>>>> well.
>>>>>>> When I add or change a comment in my code I also have to update the
>>>>>>> db (comment on column ...). This means additional work and can easily
>>>>>>> be
>>>>>>> forgotten (and thus leads to outdated comments in my code or db).
>>>>>>>
>>>>>>> if I understood you correctly there is no way to set the db column
>>>>>>> comment with web2py at the moment?
>>>>>>>
>>>>>>> thanks,
>>>>>>> Alex
>>>>>>>
>>>>>>> --
>>>>>
>>>>> ---
>>>>> 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+un...@googlegroups.com.
>>>>>
>>>>> For more options, visit https://groups.google.com/groups/opt_out.
>>>>>
>>>>>
>>>>>
>>>>
>>>> --
>>>
>>> ---
>>> 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+un...@googlegroups.com <javascript:>.
>>> For more options, visit https://groups.google.com/groups/opt_out.
>>>
>>>
>>>
>>
>>
>
--
---
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.
