Thank you for your reply, Richard!
" a normalized way to represent web2py list:reference field with a proper
many-to-many relation table " - yes! this is what I meant, 'alias_name' -
just very bad example of field name - it isn't about aliasing - sorry for
the mess
About views
I think I found the acceptable way (for me at least) that is:
1. Get query string by _select()
2. Search string and add aliases to fields (if required)
3. Wrap string in "CREATE VIEW ... AS ..." and create view at db level by
db.executesql( )
4. Define corresponding table for created view using
db.some_table.any_field.clone(name = alias_for_some_table_any_field)
all could be wrapped in the function( view_query, alias_map)
Parsing sql string (item 2) is not a better way, but DAL doesn't support
aliases for fields :(
P.S. db.table.field.clone() - very useful function, but still undocumented
On Friday, December 9, 2016 at 4:31:48 AM UTC+3, Richard wrote:
>
> Hello Val K,
>
> I am not sure I understand fully what your are talking about. On one hand,
> I do understand that you would like a proper way to define SQL VIEW in
> web2py and you propose to add a switch/flag/argument to define_table() to
> do so, which would result in a select only table or something like that...
> It could make sens, but I am not sure it would be accept base on my
> experience it wouldn't pass as it seems a bit hacky and Massimo would not
> implement it that way in DAL... Actually, I think there is no proper way to
> implement view in DAL, and it a shame (we could have care more and answer
> this need) that we don't have it... But you can define a view as a table in
> web2py and use all the available feature regarding selection with this
> "false" table (except create/read/update/delete obviously). You can also,
> write a plain SQL SELECT and embeded it into a db.executesql() function.
> Disavantage of the later method is that you can't access field with dot
> notation db.table_name.field_name as web2py doesn't know about field and
> even table name in this case, as db.executesql() return rows...
>
> On the other hand, I heard you talk about "storedin" field and aliasing
> but I don't really understand to which common pratice you refer there...
> Are you talking of a normalized way to represent web2py list:reference
> field with a proper many-to-many relation table or are you talking about
> something else? Which level of normalization are you trying to achieve with
> aliasing and storedin... I don't understand what you refering to here.
>
>
> Thanks
>
> Richard
>
> On Thu, Dec 8, 2016 at 5:49 PM, Val K <valq...@gmail.com <javascript:>>
> wrote:
>
>> It doesn't differ from reference fields. it is about automation
>> distributed transaction.
>> Table 'person' hasn't Field('alias_name' ) at db level, it's fake Field
>> with reverse reference declaration ( 'storedin alias_opt.alias_name' ) that
>> defines table.field that would be really involved in CRUD-process of table
>> 'person'
>>
>> On Wednesday, December 7, 2016 at 5:29:28 AM UTC+3, Dave S wrote:
>>>
>>>
>>> On Tuesday, December 6, 2016 at 3:41:38 PM UTC-8, Richard wrote:
>>>>
>>>> UP, never get any answer...
>>>>
>>>
>>> How would this differ from reference fields?
>>>
>>> /dps
>>>
>>>
>>>>
>>>> On Sat, Jan 23, 2016 at 4:08 PM, Val K <valq...@gmail.com> wrote:
>>>>
>>>>> Hi guys!
>>>>> I have an idea to improve DAL in scope of work with normalized DB.
>>>>> As known It's a common practice to avoid NULL value by creating
>>>>> separate (option) table(s) to store non required fields.
>>>>> So, it would be great to have a field type like "storedin
>>>>> table_name.field_name"
>>>>> For example:
>>>>>
>>>>> db.define_table('alias_opt', Field('name'), Field('alias_name',
>>>>> 'reference
>>>>> person'))
>>>>> db.define_table('person', Field('name'), Field('alias_name', 'storedin
>>>>> alias_opt.alias_name'))
>>>>>
>>>>> #INSERT:
>>>>> db.person.insert(name='Alex', alias_name='Macedonian')
>>>>> # means:
>>>>> id=db.person.insert(name='Alex')
>>>>> db.alias_opt.insert(id=id, alias_name='Macedonian')
>>>>>
>>>>> #UPDATE:
>>>>> db(db.person.id==id).update(... , alias_name=None)
>>>>> # means:
>>>>> # update person
>>>>> ...
>>>>> # update option table
>>>>> update_opt_args = filter_storedin_fields(update_args)
>>>>> opt_rec = db.alias_opt(id)
>>>>> opt_rec.update(update_opt_args)
>>>>> if not any(opt_rec.values()): # - all fields of option table
>>>>> record is None
>>>>> del db.alias_opt(id)
>>>>> else:
>>>>> db.alias_opt.update_or_insert(id==id, **update_opt_args)
>>>>>
>>>>>
>>>>> #DELETE:
>>>>> del db.person(id) also means del db.alias_opt(id), like ondelete=
>>>>> 'CASCADE'
>>>>>
>>>>> #SELECT:
>>>>> rows = db(db.person).select()
>>>>> # means:
>>>>> rows = db(db.person).select( left=[ db.alias_opt.on( db.alias_opt.id
>>>>> == db.person.id ) ] )
>>>>> but only "storedin" fields should be selected from db.alis_opt
>>>>> and they should be accessed by row.alias_name (not only by row.
>>>>> joined_table.field_name )
>>>>>
>>>>> Considering, that table person could be a VIEW (i.e. JOIN is already
>>>>> performed at DB level), there is no need to make join at web2py level,
>>>>> it could be fixed by passing an option arg like is_view=True to
>>>>> define_table()
>>>>> I know, that behavior of insert/update/delete could be easy realized
>>>>> by custom class based on Table
>>>>> with a little hacking Field-class to intercept field type to fix it to
>>>>> web2py type ( considering person.alias_name.type ==
>>>>> alias_opt.alias_name.type ).
>>>>> But it's hard for me to change select() behavior, because there is
>>>>> only common_filter, but there isn't common_join/common_left with
>>>>> providing of maping (aliasing) joined table fields to 'storedin'
>>>>> fields.
>>>>>
>>>>> In fact, I dream of common_join depends on discriminator field, that
>>>>> will switch tables to be joined depend on discriminator value specified
>>>>> in
>>>>> the query (something like db.object.type_id==type_id),
>>>>> if descriminator is not specified or couldn't be resolved at web2py
>>>>> level, it performs left join all tables (from a list containing possible
>>>>> common_join tables)
>>>>>
>>>>> P.S. May be I try to reinvent the wheel, so feel free to shoot me!
>>>>>
>>>>> --
>>>>> 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+un...@googlegroups.com.
>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>
>>>>
>>>> --
>> 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+un...@googlegroups.com <javascript:>.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
--
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/d/optout.
