Wednesday 25 June 2014

Improve Performance of Calculated Fields with Dynamic Tables

The next version (2.1) of Xataface includes a new simple, but powerful, feature that can drastically improve performance on tables that include grafted fields - especially when those grafted fields include expensive calculations.   The feature is called "dynamic tables".

A dynamic table is a MySQL table that is generated with the results of a query.  It is dependent on the tables included in the query, so that if any of them are modified, the dynamic table will be deleted and rebuilt.  They are very similar to MySQL views, except that they can perform much better in environments where updates are rare relative to reads.

Let's motivate dynamic tables by way of an example.  Consider a database with a table of contacts, called "current_call_list".  There is also a "calls" table to record all phone calls made to each contact.
The current_call_list itself only contains data pertaining to the contact (e.g. name, phone, email, etc...), but we would like the list view to also include some calculated information such as how many calls have been made to the contact, when the last call was made, what some recent call comments were, and what some of their responses were to questions asked during the calls.

Xataface makes it relatively easy to add these fields as grafted fields using the __sql__ directive of the fields.ini file (for the current_call_list table).  Our first attempt is as follows:



This works.  But it has brought with it some hefty performance baggage that we feel in a big way as the database grows beyond a few thousand records.

The problem is that all of the calculated fields require subqueries and aggregate calculations to be performed on the calls table.

Using a MySQL View


 One solution is to create a view that produces this information, and then use that view instead to obtain our grafted fields.  We could define the view as follows:



Then we could change the __sql__ directive in our fields.ini file to:



Notice that our SQL query has become much more efficient.  Rather than performing aggregate calculations on the calls table, it does a simple left join on our new view.  Generally joins on key fields in MySQL are *very* fast.  In this case, we're cheating a bit by using a view, so we will likely lose a little performance there.

Playing around with the database a little bit more, shows that the performance is marginally better, but still sluggish on larger databases.  It appears that the view still has to perform some intense calculations each time.

Using a Dynamic Table


In our application, the calls table is updated relatively infrequently compared to the number of read requests.   We should be able to take advantage of this fact to cache our calculations whenever the calls table is changed.  This is precisely what a Dynamic table is for.  The Dynamic table generates itself as necessary with the results of an SQL query.  In each HTTP request, it checks the modification of all dependent tables to see if they have changed since it was last generated.  If they have changed, then the dynamic table is cleared out and repopulated.

Let's modify our example to use a dynamic table instead of a mysql view:



In this example, we make use of the xf\db\DynamicTable class.  The constructor takes 3 parameters:

  1. The name of the table to create.
  2. An array of SQL statements that should be executed to create the table and populate it.  The first statement is usually a CREATE TABLE statement.  Subsequent statements are the queries that populate the table.
  3. An array of table names that this table depends on.  If any of these tables are changed, the dynamic table should be deleted and regenerated.
The update() method is where the actual work is performed.

Where do I place this code?


The best place to for this code is somewhere that will be executed in every request, and before the query is sent to the database.  Xataface 2.1 has supports the beforeLoadResultSet() exactly for this purpose.  It is executed just before the result set is loaded, so it gives us a chance to generate the table before it is used.


Since we have named this table the same as the View in our last example (note: we need to drop the view before doing this),  we don't need to make any changes to the __sql__ directive in our fields.ini file.

Since the SQL query that Xataface has to perform is just a simple join between two *real* tables, the application is much more responsive.  Actually an order of magnitude faster.




Friday 6 June 2014

Getting Started: Most Common Mistakes

This article is dedicated to future new users of Xataface who have difficulty getting their first app up and running.  I have created a number of "getting started" tutorials for Xataface, and have posted a couple of getting started videos.  Some of these include:

These tutorials demonstrate how to create your first Xataface application using one of three methods:
  1. Using the makesite shell script.
  2. Using the web-based install tool.
  3. Creating the application manually.
All three of these approaches *should* work, but I personally favour the manual approach for the following reasons:

  1. It is really easy.  2 lines of PHP,  5 or 6 lines in the conf.ini file, and copy the .htaccess file. and you're done.
  2. It helps you to understand the application structure from the beginning.  You're going to have to start customizing your app by hand sooner or later, so why now make it sooner.

Common Mistakes

The Web-based Installer Cannot Connect to the Database

If the web-based installer can't connect to the database, it is likely that your database is located on a different domain than localhost.  The Xataface installer script has the database host hard-coded into the script as "localhost".   If your database is located on a different server, as it may be on some shared hosting environments, you may need to modify the "installer.php" file and change the DB_HOST constant to the correct value for your server.

The Web-based Installer Keeps Prompting for Username and Password

The web installer uses the password that you provide to connect to the database.  It must be a valid mysql username and password that will have access to at least the database on which you want to create your app.  You may also enter your MySQL root password here - as it will only be used to create the app - you will have another opportunity to specify the user account that the app will use for connecting to the database.  If it keeps prompting you for the password that means that you are entering incorrect credentials.  If you're sure that you're entering the correct information here, I wouldn't spend too much time trying to solve it here.  Just skip straight to a manual installation.

Xataface Says "Installed Correctly" but I can't access my app

Likely you are following the Getting Started Tutorial, and you have just reached the end of the "Xataface Installation" section.  Installing Xataface is actually easy, it just involves copying it to your web server.  However, xataface being installed is different than your app being installed.  You still need to proceed to the next section of the getting started tutorial on creating your first app.  Better yet, just follow the manual installation instructions so you don't have to futz with any app creation scripts.

Blank White Screen

The single most common "help" request I get on the forum is the blank white screen issue.  If you get a blank white screen when you try to access your app, it means that there is an error that caused PHP execution to fail.  Likely it is an issue with a require statement pointing to the wrong path, but, technically it could be *anything*.  You need to check your PHP error log to see what the actual error is.  This is a good opportunity to get acquainted with your PHP error log because it will be your best friend going forward.  If you post to the forum without knowing where your error log is or where errors are being written, you'll likely just be sent on a mission to find it.  Some tips on solving the "blank white screen issue".

No Such File or Directory "xataface"

The following error is common for first time users:

Warning: require_once(../xataface-git/public-api.php): failed to open stream: No such file or directory in /Applications/XAMPP/xamppfiles/htdocs/index.php on line 5

Fatal error: require_once(): Failed opening required '../xataface-git/public-api.php' (include_path='.:/Applications/XAMPP/xamppfiles/lib/php') in/Applications/XAMPP/xamppfiles/htdocs/index.php on line 5

This error will occur if the require_once statement at the beginning of your index.php file is pointing to the wrong place, or if the place it is pointing is not readable by the web server process.  The first line of your index.php file is likely something like:

<?php
require_once 'xataface/public-api.php';


This should point to your xataface/public-api.php file.  Make sure that it points to the correct place, and that the permissions on the file allow reading from the web server process.

Application Loads But All images and styles are missing


If your application loads but it looks weird (e.g. missing images, no style, broken page formatting, etc...), it means that the 2nd parameter of your df_init() function (inside your index.php file) is pointing to the wrong place.

In your index.php file, you'll have a line like:

df_init(__FILE__, 'xataface')->display();

The 2nd parameter (in this case 'xataface') should be the web-accessible URL to the xataface directory.  It may be an absolute URL (e.g. http://example.com/path/to/xataface), or a relative URL (e.g. 'xataface').  This parameter is used as a base for loading all images and CSS stylesheets in the Xataface directory.  If it is wrong, you'll get no style.  After you fix this, you should see the page formatting correctly.

Wednesday 4 June 2014

Using mysqli in Xataface

Xataface now supports the new mysqli drivers.  Support has been added on GitHub and it will be incorporated into the next release.  For now (for upgrade compatibility), it will still use the default mysql driver, but you can explicitly tell it to use mysqli by adding the following to the [_database] section of your application's conf.ini file:

driver=mysqli

E.g.

[_database]
    host=localhost

    user=steve
    password=mypass
    name=dbname
    driver=mysqli

New Database Compatibility Layer


Adding support for mysqli whilst maintaining backward compatibility with the mysql driver was achieved by adding a new compatibility layer.  If you look through the changes to the Xataface source, you'll notice that all of the calls to mysql_xxx have been changed to xf_db_xxx.  E.g.

mysql_connect() was changed to xf_db_connect(), mysql_query() changed to xf_db_query(), etc..

These new functions are defined inside the xf/db/drivers directory.  Xataface loads the correct driver at the beginning of each request.

Automatic Conversion Tool


This change was made via this ANT build script that is now a part of the Xataface distribution.  It scours the entire source base looking for instances of the old mysql_xxx() functions, and it replaces them with the corresponding xf_db_xxx() function.

Example usage:

ant -f fix-deprecated-mysql-ant-task.xml

This will scour the current directory and all subdirectories.  If you want to run it on a particular base directory, you can use the -Dbasedir=PATH parameter.  E.g.

ant -Dbasedir=/Users/matt/files/myapp -f fix-deprecated-mysql-ant-task.xml

If you are using Xataface in an existing application that makes mysql_xxx() calls at any point, and you want to use the mysqli driver, then you'll need to update your application accordingly, as you cannot use both mysqli_xxx() functions and mysql_xxx() functions on the same database connection.

I'll be going through the existing Xataface modules in the next while to port them over to the new compatibility layer, but if you run into issues with a module before I get to it, you should be able to just run the ANT task on the module's directory and it will be updated automatically.