[web2py] lazy_tables + 'reference tablename' = referenced table not lazy

[Richard]

Hello,

I am analysing my app and found that many tables that I thought were lazy (lazy_tables = True) are actually not... Investigating why was that... I found (I think) that 'reference table_name' cause a table to be not be lazy anymore as mostly all table that are referenced are not lazy... There is no insight about that in the book, or I didn't find any... 

Is referenced tables can be lazy?

I use web2py 2.14.6 build...

There is old issue in pyDAL repo about reference and lazyness but they are closed.

Thanks

Richard

[Richard Vézina]

UP

Need help

Richard

--
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.

[Leonel Câmara]

Referenced tables should be lazy yes as long as you use the string version "reference anothertable".

[Richard Vézina]

But they are not. I can't understand why as the fact that they are referenced seems to be the only thing that trigger them to be defined at each request. I mean if I comment the reference field they stop to be defined they goes in the lazy array in response.toolbar db.tables...

So, I don't think that it comes from a table call somewhere else in the app (I have check for that as much as I can anyway).

Any idea?

I think I will try trunk to see if it fixed... Hope trunk is stable enough...

Richard

On Tue, Mar 14, 2017 at 11:35 AM, Leonel Câmara <leonel...@gmail.com> wrote:

Referenced tables should be lazy yes as long as you use the string version "reference anothertable".

--

[Richard Vézina]

:(

Same thing with master...

[Leonel Câmara]

Can you provide a minimal app where I see this issue?

[Richard Vézina]

Will try...

Will see at the same time if I can reproduce...

Richard

On Tue, Mar 14, 2017 at 12:15 PM, Leonel Câmara <leonel...@gmail.com> wrote:

Can you provide a minimal app where I see this issue?

--

[Richard Vézina]

I notice a strange thing with trunk... When looking at db stats, I got duplicated sql calls... For instance I use to do an insert at each request to log what user are doing and this insert was perform twice not sure why though...

Richard

[Richard Vézina]

I reproduce it...

Will remove as much things as possible as it a bit dirty for such kind of dummy app.

Richard

[Richard Vézina]

Here,

Try access the default/index and click over the db tables button, you will see many of table defined in db.py in the defined table, basically most of not all the referenced tables.

Thanks for your help.

Richard

[Leonel Câmara]

Ok I've found the problem.

You have 2 IS_IN_DB validator calls where you use db.address.id and db.phone_number_kind.id instead of "address.id" and "phone_number_kind.id"

But the bigger problem is that you are enabling record versioning for all tables which pretty much makes them all load.

Instead of this you can add an on_define to each table where you turn record versioning on for that specific table.

def toggle_versioning(table):
    table._enable_record_versioning()

db.define_table('my_versioned_table',
    Field('name'),
    on_define=toggle_versioning
)

By the way why are you defining auth tables in your model and then calling auth.define_tables to define them again, also consider using auth.signature instead of tables_generic_fields.

[Richard Vézina]

Thanks for the look up... Too many questions at the same time, will review my code base on your pin point...

:)

Richard

[Richard Vézina]

Good catch for the IS_IN_DB() I wrote this part fast (address book part) long time ago in a pet app and copy the code in production without too much double check...

I am not sure I understand why you say that I record versioning the whole tables as far as I understand this version only auth_user, no??

db.auth_user._enable_record_versioning(archive_db=db,

                                                                  archive_name='auth_user_archive',

                                                                  current_record='current_record',

                                                                  is_active='is_active')

Or you talk about another part of the code?

Thanks

Richard

[Richard Vézina]

About redefine auth tables, I don't know another way to set username=True than :

auth.define_tables(username=True)

I had in my todo-list to refactor my code to use the new way of customizing auth tables, maybe it would solve this issue if it really is one??

Richard

[Richard Vézina]

As far as I can understand I am using it properly and not all the tables are versionned :

http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Record-versioning

As I set it for auth_user only and not use .enable_record_versioning() but ._enable_record_versioning()... Can someone confirm that?

Thanks

Richard

[Richard Vézina]

It seems related to as when I comment it all tables are lazy :

db.auth_user._enable_record_versioning(archive_db=db,

                                       archive_name='auth_user_archive',

                                       current_record='current_record',

                                       is_active='is_active')

So I guess you are right and it apply to all table even with this syntax : db.tablename._enable_record_versioning(...)

Richard

[Richard Vézina]

And there must be something else involve as in my app (not the dummy app I packaged) commenting the above not make the defined table become lazy as it does in not_lazy app...

:(

Richard

[Richard Vézina]

My app predate auth.signature that why I am not using it, I would had to refactor alot of table. But would be good to do it as I would have the chance to harmonize, I sometimes don't use signature for less important table and my custom signature fields names are not very "clean code" proof or sure.

Richard

[Richard Vézina]

Found couples other issue with IS_IN_DB() declarations in other controllers...

But I use my own version of lazy_option (https://github.com/scubism/sqlabs/blob/master/controllers/plugin_lazy_options_widget.py) and the way field attribute has to be pass is another source of unlazy table...

Except if refactor it, I guess the only other option is to move the requires in controller which is not wonderful as it split apart the models/validators definitions...

I also use to define db set that I use in requires latter that way :

some_set = db(db.tablename.fieldname > 1)

But I am not sure if they are responsible for the unlazyness of the tables as it difficult to test properly because of the size of my app 150+ tables and multiples models files... Will make a test case...

At least, I know where to look now, thanks to your help!

Thanks Leonel

Richard

[Richard Vézina]

We can even use a set inside an IS_IN_DB() requires inside model definition something like that prevent model from being lazy :

db.define_table('tablename',

                             Field('fk_id_field', 'reference othertable',
                                       requires=IS_IN_DB(db(db.othertable.id > 0), 'othertable.id', '%(represent_field)s')
                                       )
                             )

This is sad...

I confirm that

some_set = db(db.tablename.fieldname > 1)

put in model outside of table definition prevent the table over which the set point from being lazy...

And use of set inside IS_IN_DB() also prevent table from being lazy

:(

Richard

[Anthony]

On Wednesday, March 15, 2017 at 12:03:52 PM UTC-4, Richard wrote:

We can even use a set inside an IS_IN_DB() requires inside model definition something like that prevent model from being lazy :

db.define_table('tablename',

                             Field('fk_id_field', 'reference othertable',
                                       requires=IS_IN_DB(db(db.othertable.id > 0), 'othertable.id', '%(represent_field)s')
                                       )
                             )

This is sad...

I confirm that

some_set = db(db.tablename.fieldname > 1)

put in model outside of table definition prevent the table over which the set point from being lazy...

And use of set inside IS_IN_DB() also prevent table from being lazy

The point of lazy tables is that they are not fully defined until you reference them (i.e., via db.mytable or db['mytable']). Of course, once you reference them, they must be defined. If something like db.tablename.fieldname > 1 would not trigger a table to be defined, what exactly would you expect to trigger the definition?

Anyway, the exact case you mention is covered by the on_define argument.

If you want to improve performance related to model instantiation, you should also consider conditional models as well as moving some model definitions to modules.

Anthony

[Richard Vézina]

Yes, thanks, Simone cue me about the on_define argument... I wasn't know about that shame on me...

:)

Richard

--

[Richard Vézina]

Saddly I can't use on_define for generic field define like so :

generic_fields_sign_id = \

    db.Table(db, 'generic_fields_sign_id',

             Field('sign_id', 'reference ref_sign',

                   # requires=IS_EMPTY_OR(IS_IN_DB(db(db.ref_sign.sign != '='), 'ref_sign.id', '%(sign)s')),

                   represent=lambda id, row: db.ref_sign(id).sign if id is not None else T('N/A')

                   ),

             on_define=lambda table: [table.sign_id.set_attributes(

                 requires=IS_EMPTY_OR(IS_IN_DB(db(db.ref_sign.sign != '='), 'ref_sign.id', '%(sign)s')))]

             )

Will have to add it to every table that use generic_fields_sign_id...

Richard

[Joe Barnhart]

Just a couple of philosophical comments...

Whenever I find that the web2py platform is "forcing" me to code in large volumes and produce ugly, quirky code which I know will be hard to maintain, I stop myself and take another look at how I'm trying to accomplish my task.  I have this philosophy that using a platform like web2py (or Python, or Linux, or anything) involves a certain mindset.  Those who created and maintain the platform envisioned working with it in a certain way.  I call that the "swimming downstream" way of working.  When I find I am "swimming upstream" I realize that I'm not using the platform as intended.

In this case, for instance, you want to instantiate one table but NOT another table which is linked to this table.  That seems like a logic error to me.  It looks like a FEATURE that lazy table instantiation will create BOTH tables in this case -- certainly the linked table and its definition should be available wherever its linker table exists.  Standing on your head to get around this is "swimming upstream".

There are numerous ways to make your site more efficient, if efficiency turns out to be a problem.  Believe me, I get it.  I, too, am guilty of "premature optimization" more often that I like to admit.  But the best course of action is to steam ahead and get your application working the easiest way possible (downstream swimming).  Then, and only then, locate any performance trouble spots and apply optimization.  Lazy tables are only one such optimization.

You heard mention of putting tables in a module.  I have about 50 tables in my app, some with 50 fields.  To instantiate all of them all of the time would be somewhat of a burden.  So I have opted to put table creation in modules.  If that isn't enough, I'll worry about that when I have a problem.

Swim downstream.  It's the only way!

-- Joe

[Anthony]

In this case, for instance, you want to instantiate one table but NOT another table which is linked to this table.  That seems like a logic error to me.

Generally good thoughts, though in this particular case, the issue is the the linked table would be fully defined even if the linking table is not defined (and therefore neither of the tables really needs to be defined on this particular request). The table definition is triggered by referencing one table in the validator of a field of another (lazily defined) table.

Anthony

[Richard Vézina]

Thanks for your answer Joe...

My problem though is not quite that I don't want to define linked table, but avoid define tables that should be lazy as they are not required in the context of a given html request... I have over 150 tables in my app (some should be merged as one as they exist cause of some original design flaw but time and ressources had always prevent me to do it). But many IS_IN_DB() validator involve db set which trigger linked table definition fine, but trigger also table definition at every request, on_define define_table() attribute is in part a solution to this problem... I suggest that we could use smart_query() to parse "string set" to prevent triggering table definition when someone need db set inside an IS_IN_DB() validator. But this would only work for this particular case, on_define would remain usefull for other cases, so it make sens to keep using it and not refactor IS_IN_DB(), though considering that I never needed on_define callback before but for db set IS_IN_DB() validator, and that it make code more readable and maintainable to be able to create db_set variable inside models files to be reused in differents validators definition for differents tables I guess it could worth it at least in my case.

But as it not possible to define a db_set vars and reuse it I make my app less DRY to improve performance. If such feature come avaible later, I would refactor again to bring back DRYness that I had before...

But you are right it always good to step back and figure out what the big picture.

--

[Richard Vézina]

I want also share I little tricks I found... I had some globals id represent variables that I reuse in many part of the app... I know globals vars is bad idea, but it the only way I found to make things DRY and fast (caching those vars in redis) instead of redoing the same query over and over to revocer ID representation.

So, the trick is to by pass the DAL with db.executesql() as I only need 2 fields, slicing remain managable and don't degrad to much the readability, but it prevent many tables definitions at every request.

I could prevent those globals var, but create them only in controller that need them, but I need those python dict also in many model definition, so...

Richard