Can I use MySQL 'views' as Fabrik tables?

[cheesegrits]

NOTE - this was written and tested on Fabrik 2.0, but should work on 1.0.x as well.

Yes, you can use MySQL 'views' as Fabrik tables. A MySQL 'view' is essentially a 'fake' table, that allows you to add additional columns which (for instance) can be calculations performed on other columns in the table.

For this example, I'm basing the view on an existing Fabrik table called 'pies', which has the following structure:

fabrik_internal_id
date_time
pecan
apple
chess
punkin

The four pie types are simple text elements, set as integers. They represent the fictional amount of each pie type I sold on a given day. For those of you not from the Southern US, Chess Pies are a Southern Thang, as is the contraction of the word Pumpkin to Punkin.

Now, lets say I want a table view in Fabrik which has an extra column, showing the total amount of pies sold on each day. I could achieve this with a form submit script that does the calculations, or a table plugin (in 2.0).

But a much simpler way to do it is to create a 'view' in MySQL. So fire up phpMyAdmin, or a command line mysql client, and create a 'view' as follows:

mysql> CREATE VIEW pieview AS SELECT fabrik_internal_id, time_date, pecan, apple, chess, punkin, pecan+apple+chess+punkin AS totpies FROM pies;
Query OK, 0 rows affected (0.03 sec)

The important thing to notice there is the "pecan+apple+chess+punkin AS totpies".

You can now see this new 'view' as if it was a normal MySQL table. You don't need to do the following, it just shows you that MySQL now has an extra 'table', which shows your new 'totpies' column:

mysql> describe pieview;
+--------------------+------------+------+-----+---------+-------+
| Field              | Type       | Null | Key | Default | Extra |
+--------------------+------------+------+-----+---------+-------+
| fabrik_internal_id | int(6)     | NO   |     | 0       |       |
| time_date          | datetime   | YES  |     | NULL    |       |
| pecan              | int(4)     | YES  |     | NULL    |       |
| apple              | int(4)     | YES  |     | NULL    |       |
| chess              | int(4)     | YES  |     | NULL    |       |
| punkin             | int(4)     | YES  |     | NULL    |       |
| totpies            | bigint(14) | YES  |     | NULL    |       |
+--------------------+------------+------+-----+---------+-------+
7 rows in set (0.05 sec)

mysql> select * from pieview;
+--------------------+---------------------+-------+-------+-------+--------+---------+
| fabrik_internal_id | time_date           | pecan | apple | chess | punkin | totpies |
+--------------------+---------------------+-------+-------+-------+--------+---------+
|                  2 | 2008-09-23 00:00:00 |    34 |    24 |     2 |      5 |   65    |
|                  3 | 2008-09-23 00:00:00 |     6 |    13 |     4 |      2 |   25    |
|                  4 | 2008-09-23 00:00:00 |    34 |    15 |    12 |      5 |   66    |
|                  5 | 2008-09-23 00:00:00 |     6 |    14 |     4 |      4 |   28    |
|                  6 | 2008-09-23 00:00:00 |     9 |     4 |    12 |      3 |   28    |
|                  7 | 2008-10-06 00:00:00 |    13 |     7 |     7 |      8 |   35    |
+--------------------+---------------------+-------+-------+-------+--------+---------+
6 rows in set (0.00 sec)

Now go to your Fabrik backend, Table tab, and create a New table. Fill out the usual details. Under the Data tab, you'll now see your new 'pieview' showing on the table dropdown, just as if it was a normal table. Select it, and set the primary key to fabrik_internal_id (or whatever the PK was on your original table).

NOTE that Fabrik tables built on MySQL views should be treated as 'read only'. So make sure you set the Access levels so nobody can add, edit or delete rows. You can always use a custom 'detail view' link to point to your original table if you want.

You may also need to edit the newly created elements, to set labels properly, turn off 'show in table' for some elements, etc.

And that's it. You can now link your newly created Fabrik table view into your menu structure, and it'll look like the attached image.

For Fabrik 2.0 users, views can be useful when combined with visualizations, such as charting. Using the 'pieview' table we created, it would then be easy to build a chart featuring total pies sold, without needing to do anything else.

NOTE - in principle, views could be writeable, i.e. you could actually edit and submit forms based on a 'view', as long as the view meets MySQL's requirements for an 'updateable view', which means things like no joined tables. But we haven't tested this!

-- hugh