improving DAL for normalized DB

[Val K]

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!

[Richard Vézina]

UP, never get any answer...

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

[Dave S]

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

 

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.

[Val K]

It doesn't differ from reference fields. it is about automation of 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'

[Richard Vézina]

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> wrote:

It doesn't differ from reference fields. it is about automation distributed transaction. 

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscribe@googlegroups.com.

[Val K]

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

In fact, it would be great to  fix this bug:

db.define_table('a_tbl', Field('id', 'integer'),  Field('name'), primarykey=['id'] ) # one keyed table

db.define_table('b_tbl', Field('id', 'reference a_tbl.id'), Field('name'), primarykey=['id']) # another keyed table

<class 'sqlite3.OperationalError'> near "name": syntax error

and this:

db.define_table('a_tbl', Field('name'))

db.define_table('b_tbl', Field('id', 'reference a_tbl') ,Field('name'), primarykey=['id'])

db.define_table('c_tbl', Field('b_tbl_id', 'reference b_tbl') ,Field('name'),  primarykey=['b_tbl_id'])

<type 'exceptions.KeyError'> 'foreign_key'

[Richard Vézina]

Note you can do such query :

db(db.table.id > 0).select("sql_field_name AS new_sql_field_name") but I don't recall how you access value of a field query like that (plain sql passed into select...) You can experiment... There is a presentation from Anthony Bastardi about these undocumented feature from deplaul web2py con from 2015 I think... Or maybe Anthony can jump in here to clarify my explanations.

There is also .with_alias('field alias'), but I am not sure exactly what need you try to fill with them...

About db.executesql(), I think you can use it for DDL purpose, but I prefer drop into pdAdmin for such type of work... And to be frank there is not much value added to do it in db.executesql(), I think that having "view definition" in DAL could be greate it would have to create the view if it is not already create in the backend... I would avoid me to use the hacky road that I had used and detailed above by defining them as web2py table after havnig created them in the backend...

For many-to-many, it would be greate, but I guess it get complicated rapidly when you start to allow such thing as every you don't have anymore a one to one relation between DAL methodes and a table... You start to have code that need to be specific for many-to-many relation which involve 3 table at least, other part of web2py need to support that, like sqlform... How such table get defined?? Do you have to define these tables all together or one at a time as now... If there still exist as a single piece, do you have to make further check at each request to know if the reference is a many-to-many "real" type... I never try to evaluate the impact over DAL actual code of such things, I guess Massimo's did such assessment. His input would enlighten us...

Richard

On Fri, Dec 9, 2016 at 3:37 PM, Val K <valq...@gmail.com> wrote:

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

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscribe@googlegroups.com.