Friday 16th December, 2016

Editor 1.6

I'm very pleased to announce the release of Editor 1.6, the sixth major update to Editor in its 1.x series. It is a free upgrade to all existing license holders and adds a number of exciting new features that greatly increase Editor's capabilities, particularly on the client-side.

In this post I'll run through a summary of the new features in 1.6 and in future posts explore how we can utilise them to enhance the applications developed with Editor further.

Upgrading

Editor 1.6 can be downloaded from its download page as both a free trial if you haven't tried Editor out yet, or if you have a license, the fully licensed version will automatically be downloaded.

The are no special considerations for upgrading with this release, it is fully backwards compatible with 1.5 and should simply drop in and replace your existing library. If you are upgrading from 1.4 or earlier, it would be worth reading over the 1.5 upgrade documentation as there were a number of changes that you should be aware of with that release.

Client-side

Editor is primarily a client-side piece of software (with supporting PHP and .NET libraries to make getting started easier!), so let's focus on the client-side first and detail the new features you can make use of when creating editable tables for your clients.

Templates

One of the most requested features in Editor has been the ability to show the forms in a more complex layout than the simple linear list. Previously you could use CSS to achieve a multi-column layout, but that itself was limiting. To address this Editor 1.6 introduces its new template option. It allows you to point at an element that Editor will display inside its main editing form and through an HTML5 custom tag (<editor-field>) lets you specify where you want each field in your form to be shown.

The idea here is to allow you to specify your own HTML structure, whatever that may be, for the form's layout. As always, an example is worth a thousand words, so there is one available here.

Editor form template image

This template is a relatively simple example. For more complex layouts can be achieved including using tabs to separate fields. That will be explored further in a future blog post.

No Ajax

Although the majority of Editor applications will send an Ajax request to the server to update a database, it is not uncommon to also simply want to be able to update a DataTables' data locally without immediately saving it to the server. You might then submit a number of edits in a batch or write to a local database. Previously in Editor this meant using the ajax option and providing your own implementation of Editor's client / server data - not a particularly trivial task!

Editing a table's data on the client-side only is now trivial - simply don't include the ajax option (which used to be mandatory - it no longer is). Editor will automatically update the table for you.

An editable table on the client-side might now be as simple the following, where we define the fields, and use Buttons to present a way to trigger the editing:

$(document).ready( function () {
  var editor = new $.fn.dataTable.Editor( {
    table: '#example',
    idSrc: 0,
    fields: [
      { label: 'Name',       name: 0 },
      { label: 'Position',   name: 1 },
      { label: 'Office',     name: 2 },
      { label: 'Age',        name: 3 },
      { label: 'Start date', name: 4, type: 'datetime' },
      { label: 'Salary',     name: 5 }
    ]
  } );

  var table = $('#example').DataTable( {
    dom: "Bfrtip",
    select: true,
    buttons: [
        { extend: "create", editor: editor },
        { extend: "edit",   editor: editor },
        { extend: "remove", editor: editor }
    ]
  } );
} );

Semantic UI

Editor has long enjoyed direct integration for Bootstrap, Foundation and jQuery UI, now Semantic UI joins the ranks! Semantic UI is a modern CSS framework that focuses, as it name suggests, on providing features and styling through CSS / class name semantics. An example of Semantic UI with Editor is available in the Editor examples.

The DataTables download builder doesn't yet include Semantic UI as a styling option, but it will be updated to do so in the coming days.

Server-side

The fun doesn't stop when we consider the PHP and .NET server-side libraries for Editor, each seeing a number of new features as well. Both libraries are developed in parallel, keeping their feature sets and basic APIs the same, while allowing divergence for each platform's own conventions as appropriate. As such, if you see a feature in one, it will likely be in the other.

Debug mode

Editor makes use of a database abstraction layer so that it can communicate with various different databases. That abstraction layer will build suitable SQL statements based on how the instance has been configured and what data has been set to it. That works great until something goes wrong. Knowing what SQL was being run was the basic starting point for most debugging with Editor, but seeing exactly what statement was being used was quite difficult. Now the libraries have a debug mode.

Editor::inst( $db, 'myTable' )
    ->debug( true )
    ->fields( ... )
    ...;
new Editor( db, "myTable" )
    .Debug( true )
    .Model( ... )
    ...;

When the debug mode is enabled a debugSql parameter is returned in the JSON that the server sends back to the client on each request (read, create, edit and delete). That shows the queries that were run and any parameters that were bound to the query. For example, on a simple update we might get the following UPDATE and SELECT statements that Editor has run:

{
    "debugSql": [{
        "query": "UPDATE  `datatables_demo` SET  `first_name` = :first_name, `last_name` = :last_name, `position` = :position, `office` = :office WHERE `id` = :where_0 ",
        "bindings": [{
            "name": ":first_name",
            "value": "Fiona",
            "type": null
        }, {
            "name": ":last_name",
            "value": "Green",
            "type": null
        }, {
            "name": ":position",
            "value": "Chief Executive Officer (CEO)",
            "type": null
        }, {
            "name": ":office",
            "value": "San Francisco",
            "type": null
        }]
    }, {
        "query": "SELECT  `id` as 'id', `first_name` as 'first_name', `last_name` as 'last_name', `position` as 'position', `email` as 'email', `office` as 'office' FROM  `datatables_demo` WHERE `id` = :where_0 ",
        "bindings": [{
            "name": ":where_0",
            "value": "29",
            "type": null
        }]
    }]
}

Improved Options

When using information from joined tables, its very common to want to get a list of the options that the end user can select from. Editor provides this through its Field->option() / Field.Option() method. Previously this method simply accepted a list of parameters, which wasn't particularly extensible. Now it accepts an instance of the new Options class which can be configured using the same style of chaining API as the other Editor classes, making it much easier to use.

Additionally the new Options class provides options to control the ordering of records and limiting the number of results, as well as providing the existing conditional operators and renderers.

Field::inst( 'users.site' )
    ->options( Options::inst()
        ->table( 'sites' )
        ->value( 'id' )
        ->label( 'name' )
    );
new Field("users.site")
    .Options(new Options()
        .Table("sites")
        .Value("id")
        .Label("name")
    );

This new class also provides scope for adding new features such as automatic dependent fields in future versions of Editor without complicating the interface! See the documentation for each platform for more information: PHP | .NET.

Cancellable events

The server-side events that are triggered by the libraries (PHP | .NET) when certain actions are performed allows customisation of the Editor configuration for that specific action, logging or other notification services of the update.

The pre* events that are triggered were particularly useful for adding or performing validation, but there was no option to cancel the action that Editor would perform at that point from the function (for example if the custom validation failed). This feature is now available in 1.6 where all of the pre* events that are triggered can be cancelled.

In the following examples a check is made on a session variable to confirm that the user has permission to update a table before writing it to the database. If they do not have permission, the action is cancelled.

$editor->on( 'preEdit', function ( $editor, $id, $values ) {
    if ( ! $_SESSION['access-edit'] ) {
        return false;
    }
} );
editor.PreEdit += ((sender, args) =>
{
    if (!Session["AccessEdit"])
    {
        args.Cancel = true;
    }
});

Further information about the server-side events is available in the documentation for each library: PHP | .NET.

Compound keys

In order to be able to edit and delete data in a DataTable, Editor needs to be able to address each row uniquely. This is done using a unique key. Previously only the values from a single column in the database table could be used for this, but with 1.6 it is now possible to use a compound key (i.e. a key made up of multiple columns). This can be particularly useful when working with a table that joins multiple others and you want to ensure uniqueness, working with dates and other scenarios.

Support for this is done in the PHP and .NET libraries by simply passing an array of column names that make up the compound key in as the primary key parameter when creating a new Editor instance:

Editor::inst( $db, 'visitors', array('visitor_id', 'visit_date') );
new Editor( db, "visitors", new []{"visitor_id", "visit_date"} );

It is worth noting one important consideration when using compound keys: when creating new rows you must submit the data for the columns that make up the compound key (an error will be shown otherwise). Editor cannot currently read information that is generated by the database. If you need to set a server-side computed value (e.g. current time), use the Field->setValue() / Field.SetValue() methods to set the value.

Full release notes

There are of course many other smaller new features and a number of bug fixes in Editor 1.6 such as:

Full informations and details of the additions, changes and fixes in 1.6 can be found in the 1.6.0 release notes.