(not logged in) | Login

Chinook Demo / Part 2.5 - Virtual Columns

2.4 - CRUD Options Back to List

  Download video file:  chinook_part_2.5_virtual_columns.mp4



Commands
cmd_history.sh#L229-L241
Commits
e49bd2a  –  (8) - created "full_name" virtual column
c8fe469  –  (9) - full_name virtual column writable + display_column
02_rapiddbic_basics

Try it yourself:

# install/upgrade RapidApp:
cpanm RapidApp

# clone the repo:
git clone http://github.com/IntelliTree/RA-ChinookDemo.git
cd RA-ChinookDemo/

# checkout the code as of the end of this video -
#  ("Part 2.5 - Virtual Columns"):
git checkout 02_rapiddbic_basics

# start the test server:
script/ra_chinookdemo_server.pl

Video Transcript

VIRTUAL COLUMNS (Config)

One of RapidApp's particularly powerful and flexible backend API hooks are virtual columns. These are special, additional columns that you can add to your DBIC result sources that behave just like physical columns, but with custom-defined calculations as values.

Virtual_columns are set up in their own section in the RapidDbic config, just like grid_params and TableSpecs, and is similarly subdivided by result source.

For a simple example, lets add a virtual column called "full_name" to the Employee source as a concatination of the existing columns firstname and lastname.

Virtual column definitions take the same options as the standard DBIC 'add_columns' function, such as data_type, nullability, and size, plus the special 'sql' parameter which defines the SQL expression used compute the column value for each row.

Here we are simply joining firstname and lastname, with a space in the middle, using the SQLite pipe-pipe concatenation operator. The special pseudo-name 'self' is substituted with the alias to the current row at runtime.

This is evaluated by the database itself, so the SQL must be compatible with the syntax of the server, which may differ from vendor to vendor. Many times the syntax will the same, but other times not. It all depends on what you're trying to do. String manipulation is one of those things that works differently on different servers, so this virtual_column wouldn't work as-is with another backend. For MySQL, for example, you would need to use the CONCAT function instead of the double-pipes.

Virtual columns are implemented in this vendor-specific manner for the sake of performance, so the calculations can be done within the same query that fetches the rest of the data for a page of results. Virtual Columns will always still be many times slower than ordinary columns because they have to be calculated on every request and for every row considered by the query.

So, for tables with lots of rows, it usually makes sense to disable sortability of virtual columns, just like for multi-relationship columns, which is also a kind of virtual column.

VIRTUAL COLUMNS (Demo)

If we open the Employee grid we can see that our new virtual full_name column is showing up as expected.

Since it doesn't really exist in the table, but is instead based on other columns, changes to those underlying columns are reflected instantly, just as we would want and expect.

But the column is still searchable, sortable, and so on, like any ordinary column, unless you decide to disable any of those features for the sake of performance.

Quick Search is however automatically disabled for virtual columns, because it is too easy to forget and then fire off slow queries without meaning to. If you want to search on a virtual column, you just need to use the filters.

The virtual column value is obviously read-only, but can actually be made editable if you tell the system how, that is, how to persist a new value when changed.

EDIT VIRTUAL COLUMNS (Config)

To make a virtual column editable, you need to supply a custom *write* function that reverses the logic of the custom *read* function - the SQL expression.

This is done by supplying the special set_function parameter within the virtual column config. Since edits happen on an individual basis, this is done with normal perl-code instead of SQL, since there aren't the same performance restrictions.

This CodeRef is called whenever the virtual column is modified, with the DBIC row object and new column value supplied as arguments, and is expected to handle persisting the value in whatever way is required.

In this case, we need split the full name string back into its first and last name components, and then update those physical columns accordingly. These new values will in turn be reflected in the virtual column value, and completing the loop.

The logic of either side is entirely up to you, which you can use to accomplish all sorts of different things. The sql and set_function don't even necessarily have to agree. For instance, you can do things like use 'SELECT NULL' for the sql, or read side, and then define a set_function to perform some arbitrary, independant operation, updating data elsewhere. So, with virtual columns, you can adapt the already well-defined database editing capabilities for the purposes of arbitrary data submission.

Virtual columns are also very useful as display columns, which as we saw, are used as the short label for rows in places like dropdowns and relationship links. When there is no single column that is descriptive enough alone, a virtual column can be used to format and show several columns together, like full_name does.

Now let's see how this works in the interface.

EDIT VIRTUAL COLUMNS (Demo)

Let me reload, and make some column changes so its easier to see the new functionality...

Now that we've supplied a set_function, the full_name virtual column is editable. Assuming our logic is sane, edits will appear to work like any other column, even though custom code is at work behind the scenes.

And this appears to be working correctly, with changes to full_name updating the real columns firstname and lastname.

The Employee table points to itself with the 'reportsto' foreign key, so we can see the virtual column in use as the display column.

We can also see both sides of the relationship in the same grid, with the multi-relationship column 'employees' showing the other side.

We can see who each employee reports to and how many employees report to them. For instance, we can count 3 employee rows that report to nancy, which agree with the employees column in nancy's row.

And since full_name is the display column, this is all very straightforward and human-readable.

PART 2 CONCLUSION

This is the end of part 2, RapidDbic basics, so I'm going to create a tag in the repo to mark this spot, and push it to Github.

In part 3 I'll introduce some other turn-key RapidApp plugins to password-protect the application, and setup users with saved views which allow the state of the grids we've seen throughout part 2 to be saved and retrieved later.

We'll also explore some other out-of-the-box database features that haven't been covered so far, including Batch Modify, Excel Export, and Frozen Conditions for permission-restricted views.


For support, or to learn more about the RapidApp project, please visit the #rapidapp IRC channel on irc.perl.org