Editor: how do I automatically fill an auto-increment sequence default field on record creation?

WaWJohn

I'm using PostgreSQL and the latest Editor PHP libraries, but the question should be similar for mySQL with serial fields.

I have a sequence defined to generate a global ID for my application. This sequence is not bound to any particular column (OWNED BY NONE). I can call the SQL function nextval() with the sequence name, I get the next available ID.

CREATE SEQUENCE test_gid_sequence
    INCREMENT BY 1
    MINVALUE 0
    MAXVALUE 2147483647
    START WITH 1000
    CACHE 1
    NO CYCLE
    OWNED BY NONE;

I have defined a table with three fields: an integer gid field with a default value of the next value in the global sequence, and two text fields.

CREATE TABLE test_editor_create(
    gid smallint NOT NULL DEFAULT nextval('test_gid_sequence'::regclass),
    first_name text,
    last_name text,
    CONSTRAINT pk_test_editor_create PRIMARY KEY (gid)
);

When I connect directly to the database, I can insert data directly into the database three different ways:

1) by specifying the column names I want to be inserted, but NOT the gid column. The database system automatically fills in the gid field when the record is inserted.

test_editor_create=> insert into test_editor_create (first_name,last_name) values ('Kitty','Litter');
INSERT 0 1

2) by specifying all column names, and using the SQL keyword default as a placeholder for the gid field. The string 'default' does not work.

test_editor_create=> insert into test_editor_create (gid,first_name,last_name) values (default,'Hairy','Potter');
INSERT 0 1

3) by specifying the nextval('test_gid_sequence') function call in an SQL statement

test_editor_create=> insert into test_editor_create (gid,first_name,last_name) values (nextval('test_gid_sequence'),'John','Smith');
INSERT 0 1

But how do I do this in Editor ?

I have created a html document to create an editor instance and initialize a datatable. I also created a PHP file as an Ajax data source and processor. I can view and edit the records without problem.

My problem lies when I try to create a new record. The form appears, all fields blank. When I fill in the two text fields and leave the gid field blank then submit the form, I get the error

SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: ""

which is pretty logical for an empty string, but is frustrating when I want the default. Trying to use default or 'default' gives the same error, because they're not integers either. I think they're just bound as string values, not evaluated as SQL keywords.

How can I tell Editor to either:

1) not include the gid field in the submission, at which point the database will add it as the automatic default
2) or tell Editor to send the SQL keyword default when the gid field is empty on record creation.

In the interest of completeness, I'm including the relevant code of each part.

Here's my table:

        <table id="test_editor_create_table" class="display" cellspacing="0" width="100%">
            <thead>
                <tr>
                    <th>GlobalID</th>
                    <th>First name</th>
                    <th>Last name</th>
                </tr>
            </thead>
            <tfoot>
                <tr>
                    <th>GlobalID</th>
                    <th>First name</th>
                    <th>Last name</th>
                </tr>
            </tfoot>
        </table>

This is my document initialisation function:

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "php/test_editor_create-data.php",
        table: "#test_editor_create_table",
        fields: [ {
                label: "GlobalID:",
                name: "gid"
            }, {
                label: "First Name:",
                name: "first_name"
            }, {
                label: "Last name:",
                name: "last_name"
            }
        ]
    } );

    $('#test_editor_create_table').DataTable( {
        dom: "Blfrtip",
        ajax: "php/test_editor_create-data.php",
        columns: [
            { data: "gid" },
            { data: "first_name" },
            { data: "last_name" }
        ],
        select: true,
        buttons: [
            { extend: "create", editor: editor },
            { extend: "edit",   editor: editor },
            { extend: "remove", editor: editor }
        ]
    } );
} );

And finally the PHP Ajax datasource:

// DataTables PHP library
include( "DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
$editor = Editor::inst( $db, 'test_editor_create', 'gid' )
    ->fields(
        Field::inst( 'gid' ),
        Field::inst( 'first_name' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'last_name' )->validator( 'Validate::notEmpty' )
    );
    
// we've broken the line to make sure where clauses come before processing
$editor
    ->process( $_POST )
    ->json();

allan

1) not include the gid field in the submission, at which point the database will add it as the automatic default

This one. Simply don't include it in the fields on the client-side. i.e. remove:

            {
                label: "GlobalID:",
                name: "gid"
            },

You don't want or need to submit it.

For security you probably also want to mark that field as not settable on the server-side:

Field::inst( 'gid' )->set( false )

That way, the value is still read, but the PHP will never try to set it on the database - thus allowing the default to run. This isn't strictly required (just removing it from the client Editor form will cause it not to be submitted), but its probably a good idea so no one can try to get clever with your database...!

Allan

WaWJohn

Many thanks, Allan, for your extremely rapid response! I wrote my question at around 3AM and then next day, before noon, I had a choice of two elegant solutions.

The first one, simply omitting the gid field from the javascript within the document ready function, which creates the editor object:

    editor = new $.fn.dataTable.Editor( {
        ajax: "php/test_editor_create-data.php",
        table: "#test_editor_create_table",
        fields: [  {
                label: "First Name:",
                name: "first_name"
            }, {
                label: "Last name:",
                name: "last_name"
            }
        ]
    } );

The results of this are quite simple: the field does not appear on the edit or create form, and record creation works as expected.

The second solution, involves changing the code of the PHP ajax data source for that auto-index field, changing the set property to false:

// excerpt from test_editor_create-data.php 
$editor = Editor::inst( $db, 'test_editor_create', 'gid' )
    ->fields(
        Field::inst( 'gid' )->set(false),
        Field::inst( 'first_name' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'last_name' )->validator( 'Validate::notEmpty' )
    );

In this case, using the original javascript editor initialisation as before with the gid field defined, the results are quite interesting. The contents of the gid field appear on the form, and can be modified. However, any changes to the field are silently discarded when the form is submitted, while any updates to other fields are made to the record with the original, unchanged gid.

On record create, the field is blank. When the form is submitted, any content entered into the gid field is silently ignored. No complaints about integer mismatch, no chance for Little Bobby Tables to stick malicious SQL code in there!

I'm a pretty old-school database programmer, and I'm trying to get used to object oriented PHP and JavaScript. Conceptually, in my mind all fields defined for the editor object attached to a given HTML-defined #table-id had to be the same as the $('#table-id').DataTable( {...}) initialisation, which had to correspond to the fields returned by the $editor PHP object referred to in the ajax: property of both the DataTables objects and the Editor objects using that same #table-id.

Your simple example shows that I had misunderstood which fields are required where.

As I now understand it, there are four parts to the setup (please feel free to correct or extend my understanding):

1. The PHP script used as a data source. I must define all fields that I want to return to ALL callers of my ajax in my json reply, so if I'm calling this from the ajax: property of a Javascript DataTables object or an Editor object, I need to return all the columns requested by each caller. I can use all the properties in the PHP libraries to define my WHERE clause, for example.
2. The HTML <table> defining the <thead> and <tfoot> with their <th> cells and column header text. This HTML table has an attribute, usually id="whatever" that the jQuery selectors of the DataTables and Editor instances can use to attach themselves to this particular table. The number and order of the columns defined must match the javascript DataTables definitions in the document ready initialisation function.
3. The Editor initialisation javascript defines a variable called editor with its own fields:, which may be a subset of the json data supplied by the PHP script referenced in its ajax: property. In addition, the fields used in the editor may or may not appear in the attached DataTables using the same id attribute. I think this editor variable name is used in the DataTables buttons editor extensions as the editor: property.
4. The DataTables initialisation javascript uses a jQuery selector with the same id as my HTML table, and which is the same id as that used for the table: property of the editor object. Here I define a series of options, like dom: and ajax:, plus any columns: I want to display. My PHP ajax: script must return the columns used for that particular DataTable, but may also return other unused columns. The buttons: options and the select: option extend the base DataTable to use the editor variable defined above to update a selected row. The New button creates a new record according to the fields defined for the editor variable. It is unclear to me if I must define a different editor variable for each different Datatable I have on the page.

I use the same ajax: PHP script option for the editor as for the DataTables option, I don't know if that is required, good practice, not important, or discouraged.

And maybe this should be another, separate question: how do I present a default value for a field based on both run-time client-side data, and a server-side auto-increment sequence on new record creation? Let's say a doc_code text field with a text prefix plus a numeric suffix taken from the SQL database sequence, like DOC-1234, VIDEO-1235 and PHOTO-1236.

I'd like to take a javascript variable and combine it with a SELECT nextval('test_gid_sequence') SQL statement to have a pre-filled key field on record create, depending on the type of document they're creating at the time (that's the client-side javascript variable value), while using the database SQL sequence to ensure a key totally unique to the application.

I've looked into the def: option, but I don't see a way to combine client side data with server-side SQL, mainly because I'm weak in understanding closures. If someone could extend my knowledge, it would be most gratefully appreciated.

allan

Hi,

Thank you for your excellent reply. The understanding you've explained in your four points is absolutely spot on. To clarify the first point about the PHP - the Editor PHP instance there will define all of the fields that can be get or set for the target table. It need not include all columns in the SQL table if that is not required (I only mentioned as I've seen others wonder about that).

It is unclear to me if I must define a different editor variable for each different Datatable I have on the page.

Typically, yes. The create, edit and remove buttons need to be told what Editor instance they should act on, that is why you need to set the editor parameter for them. As such, you can have multiple Editor instances attached to the same table, activated by different buttons.

how do I present a default value for a field based on both run-time client-side data, and a server-side auto-increment sequence on new record creation?

This one is a bit more tricky - you would almost certainly need to use a database trigger (or an UPDATE statement immediately after the INSERT).

The client-side can't know what the next sequence number would be, so your trigger would need to take the value submitted by the client-side (VIDEO for example) and append the next sequence number to that in an INSERT trigger.

Given that the knowledge of the sequence is only in the database, I think this is the only way to do it.

Regards,
Allan

WaWJohn

Thanks, Allan for these clarifications.

To go into more detail about the client+server values to create an ID field, I tried a variant of this in my first attempts to set a default value for the gid field in my editor definition.

Since I'm creating the HTML page with a PHP script, it's an easy matter to stick in a PHP variable value $next_gid into the def: property:

<?php
require_once('php/reader-db-config.php');
$next_gid = db_get_next_gid();

<?php
>

var editor; 
$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "php/test_editor_create-data.php",
        table: "#test_editor_create_table",
        fields: [ {
                label: "GlobalID:",
                name: "gid",
                def: <?php echo $next_gid; ?>
            }, {
                label: "First Name:",
                name: "first_name"
            }, {
                label: "Last name:",
                name: "last_name"
            }
        ]
    } );

```
?>


The data source is quite simple, non-object-oriented PHP script, reader-db-config.php, and it looks like this:

```php
// database parameters defined in a global
global $CONNECTSTR ;
$CONNECTSTR="host=$host dbname=$readerdb user=$user password=$pass" ;

function db_get_next_gid() {
    global $CONNECTSTR;
    $db = pg_connect($CONNECTSTR);
    $sql_result = pg_query ($db,"SELECT nextval('gid_sequence')");
    if (!$sql_result) {error_log("ERROR!!! in db_get_next_gid");return FALSE;}
    $next_gid = pg_fetch_result($sql_result,0,0);
    return $next_gid;
}

This actually worked as I expected: when I clicked on the New button, the gid field had the next sequence value pre-filled in, so I just have to enter the first and last name data. On submission, the record, with the key field, is accepted in the database.

This technique breaks down as soon as I want to create another record, without refreshing the HTML page. Since the def: value was set on page load, the next record I try to create uses the same gid value for each new record. Obviously, once the value has been used, the next submission is rejected by the back-end because the key value is not unique. Reloading the page 'fixes' the problem, but then the user experience misses out on the juicy JavaScript Ajax wonderfulness of Editor.

Thus, your solutions of key-field non-submission or telling the editor PHP to not set the value work perfectly as long as everything involving the key field is handled by the back-end database alone.

But I want something that dynamically uses a fresh code, after each ajax submission. The user must be able to choose the code prefix, and when creating the new record, have a user-modifiable key with the numeric part from the database. I want to be able to access, within my client JavaScript, a new sequence value, which I will be able to concatenate with the Javascript variable, to have the key field filled in when they edit the record.

That's why I mentioned closures, where a javascript function bound to the def: property could query the database and grab a new value each time "create" is called.

This problem perfectly illustrates the fundamental difficulty that I've been facing in the last 10 years building web-based database applications: the client JavaScript and server PHP are really in two different worlds. I've always worked around this by generating my JavaScript from my PHP code on page load, but this does not work when I want to go in the opposite direction, and have my JavaScript grab a value from my PHP server environment.

I guess I need a general way to grab a simple database value using Ajax, but I don't really know how to do that. Well, actually I have done that using jQuery Ajax calls and my kinda primitive procedural JavaScript, but not as a closure within elegant object-oriented code like Editor. And this is where I could really use suggestions from the JavaScript gurus out there...

allan

This technique breaks down as soon as I want to create another record

You can specify a function for def so you can specify some logic for the value that populates the field. From there, you have two options:

1. Keep a local counter that just increments after each new record.
2. Make a synchronous call to the server to get the next value (not async!)

The second method would address multiple people using the system at the same time (for the most part), but it would mean a small delay when loading a new form.

You would use the jQuery.ajax method to make the Ajax call to a PHP script that is specifically designed to get the next value and return it.

A trigger in the db is the other way to approach it and I think would be the most compatible and probably the least code. You wouldn't see the sequence number as you are creating the new record tho.

Regards,
Allan