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.richard.vez...@gmail.com
> wrote:
> I will show you some code tomorrow.
>
> :)
>
> Richard
>
>
> On Tue, Mar 12, 2013 at 7:51 PM, Alex <mrauc...@gmail.com> 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+unsubscr...@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+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
