Dataface__view

[silma]

Hello Steve & Xataface users,

I've got an application who is already working online with the 1.1.5r2 984 version of xataface . Actually, i'm testing it on my localhost with the last xataface version.

Everything works very well : Thank you for all the work that you made !

I'm just wondering : I' ve noticed that some views were created in the database. They're called "dataface__view_mytablename_variousnumber".

I tried to search the forum and website about that, but i did'nt find anything.

Could you explain how it works ?
Are theses view deleted after sometime , or should i do it manually ?

Thanks a lot for you help, and please excuse my english.

[shannah]

Any time you define a custom query for a table using the __sql__ directive of the fields.ini file, Xataface will create a view for this query for performance reasons. You can delete these views at any time without consequence. Xataface will just recreate them.

[silma]

Ok, thanks a lot for your response.

I'll se how much view it create per week.

[shannah]

The number of views it creates should be stable. It only creates one per __sql__ query. If you modify the query, it will make another one, but other than that, just one per __sql__ query in the fields.ini file.

[silma]

Ok, thank you very much for your explanations !

[silma]

Now that my app work with the upgrade (thanks!) i re-discover things that are different from the old version i used.

Most of the things are really great but I'm a bit annoyed with the Dataface__view function :

My __sql__ function look like this, for permissions reasons ( http://xataface.com/forum/viewtopic.php?f=4&t=4834 )

Code: Select all

    function __sql__(){
      $auth =& Dataface_AuthenticationTool::getInstance();
      $user =& $auth->getLoggedInUser();
      $username = '' ;

        if ( $user ) $username = $user->val("Username");

        return "select compteur.*,roles.Role FROM compteur LEFT JOIN roles  ON (compteur.MouvrageID=roles.record_id and roles.Username='".$username."')"; 
   }
      


So, each time a user log in and visit a table, a view is created.
Should i desactivate it ?
In the way it's work now, i'll soon have more than 1000 views

Thanks a lot for your help,
Silma.

[shannah]

The dataface__view is a performance improvement to encapsulate subqueries into a view. This produces drastically improved performance especially on large databases. It is used only when __sql__ is used to redefine the SQL query for a table.
Only one view will be created per custom __sql__ statement. Since your SQL statement is dynamically generated depending on the logged in user, then there may be up to one view per user. These views can be deleted at any time without affecting the system. It will just recreate them as necessary.

If you want to disable this feature you can add

Code: Select all

define('XATAFACE_DISABLE_PROXY_VIEWS',true);


to the beginning of your index.php file.

-Steve

[silma]

So, in your opinion I should rather make a cron to delete view with dataface__view prefix from time to time, or desactivate it ?

Thanks again for your help & advices,

Silma.

[shannah]

I would leave it on as it does improve performance. If you are using a user with all permissions you should see an action in the "Control Panel", to clear the views. This will clear out all of the views. This will be necessary if you add or remove columns from any of the tables that are used in the views.

You may also periodically clear the views - or just ignore them. phpMyAdmin automatically collapses multiple views with the same prefix like this so it doesn't clutter up the view. Generally I'm not bothered by having a large amount of views.

Only in rare cases have I chosen to turn off the views.

-Steve

[silma]

Ok, I'll do that then !

Thanks again,

Silma.