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 <valq7...@gmail.com> 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+unsubscr...@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+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
