State storage

The Python libraries for DataTables provide a StateRestore class, which is used as the end point for Ajax requests from the StateRestore extension. StateRestore provides the end user with state management ability for DataTables - i.e. the ability to set up a table in a specific state, such as with complex search terms, column visibility, ordering and more. States can be loaded, created, edited and removed by the end user, with the states being stored permanently, per user, on the server-side through this class.

Core concepts

The StateRestore class is similar to the Editor class in that you create a new instance with the configuration needed and then pass the submitted data to the .process() method. This method will correctly handle any database interaction based on the data submitted from the client-side, and then make the response available through the .data() method, which you can return to the client-side as JSON.

The class will automatically handle the following requests from the client-side:

• Load - get all states (state-read)
• Create - write a new state to a database (state-create)
• Edit - update the properties of a state (state-edit)
• Delete - delete one or more states (state-remove)

A single database table is used to store the states, which can be addressed per DataTable and per user (see the sections below).

A StateRestore instance is initialised with the following constructor:

StateRestore(db: Connection = None, table: str = None, pkey: str = None)

Where:

• db is a SQLAlchemy Core database connection. You can use the same one as you do for the DataTable or Editor classes.
• table is the name of the database table that will hold the saved states (can also be set with .table()).
• pkey is the name of the primary key column (must be an auto value - e.g. a serial or autoincrement). Default id and can also be set with .column_id().

User management

You will most likely wish to provide state storage per user, so each person sees only their own states (and any that are generally shared). This is done by providing the StateRestore instance with the unique identifier for the user. What form this value is will depend upon your user management system - e.g. it could be a username, a UUID, an integer or something else.

The unique id for the user should be given to the instance using the .user() method - e.g.:

new StateRestore(db, 'states')
    .user(session.get('user_id'));

Again, the location of where you read the user ID from will be dependent upon your user / session management.

This value will be written to the database table when creating a state (based on the .column_user() value - see below), and used as a condition when reading, editing and removing states.

It is worth noting that linking states with a specific user is optional, but it is very strongly recommended!

Table separation

As well as allowing states to be separated by user, you will almost certainly wish to provide different state storage per client-side DataTable (state storage for one DataTable is unlikely to apply directly to a different DataTable, which could be showing different data and using a different configuration).

This is handled by the StateRestore Ajax requests including the following parameters:

• path - The relative URL of the host page (i.e. window.location.path)
• table - the ID of the host DataTable

These two properties are written to the database when creating a state (based on .column_path() and .column_table(), respectively), and then used as conditions when reading, editing and removing states (just as with the user value above).

Table columns

The database table for state storage with StateRestore requires a number of columns to be defined. The StateRestore class defines a default for the name of each column that it uses, but they can all be configured to use different names if you require. The following table details the columns used, their default name and their control method:

Column default	Set method	Description
defaultState	.column_default()	Stores the flag to indicate if the state is a default state (default states are unique per user, per table)
id	.column_id()	The primary key column. This is expected to be a simple, single, value for states (i.e. an integer or UUID).
name	.column_name()	The column where the state name is stored
path	.column_path()	Relative URL of where the host DataTable is
shared	.column_shared()	Column that stores the flag to indicate if the state is shared with other users
state	.column_state()	Column for where the state JSON string is stored (note that this is expected to be a string, not a JSON data type).
table	.column_table()	The column where the ID of the DataTable that the state applies to is stored
user	.column_user()	The column for the value of the unique user id

Example SQL

The following SQL is an example for Postgres to create a state storage table for StateRestore that uses the default column names.

CREATE TABLE states (
    id           serial,
    defaultState boolean NOT NULL default '0',
    name         text    NOT NULL default '',
    path         text    NOT NULL default '',
    shared       boolean NOT NULL default '0',
    state        text    NOT NULL default '',
    table        text    NOT NULL default '',
    user         text    NOT NULL default '',
    PRIMARY KEY (id)
);

The SQL files for the example apps include a states table for each of the supported database engines.

Example

In the following, Flask will be used to provide the routing; however, you can use any router you wish (or custom handling, if you are so inclined!).

Basic use case

This first example is an Ajax end point for StateRestore's Ajax requests that makes use of the default column names for state storage (see above). As you will see the only particularly significant part is on line 3 where the user identifier is given to the instance. Everything else, such as handling read, create, edit and delete, is performed automatically.

@bp.route("/api/states", methods=["POST"])
def staff():
    with get_db() as conn:
        sr = (
            StateRestore( conn, "states" )
            .user(session.get("user_id"))
        )

        sr.process(request.get_json(silent=True) or request.form)
        return jsonify(sr.data())

Line-by-line:

• 1 - Set up an /api/states route (change to suit your app), handling POST requests (which StateRestore sends by default).
• 3 - Get the database connection (in this case from an imported function)
• 5 - Create a new StateRestore instance, setting the database connection and database table name.
• 6 - Set the end user's session identifier, so only states belonging to them will be acted upon.
• 9 - Process the data coming from the client-side.
• 10 - Send the result back to the client-side.

Custom columns

As noted above, you don't have to use the default column names. The StateRestore class can be configured to use different column names for each property in the state that it stores. The following example demonstrates this:

@bp.route("/api/states", methods=["GET", "POST", "PUT", "DELETE"])
def staff():
    with get_db() as conn:
        sr = (
            StateRestore( conn, "states" )
            .column_default("userDefault")
            .column_name("stateName")
            .column_path("page")
            .column_shared("share")
            .column_state("state")
            .column_table("datatable")
            .column_user("userId")
            .user(session.get("user_id"))
        )

        sr.process(request.get_json(silent=True) or request.form)
        return jsonify(sr.data())

Live examples

The demo app includes StateRestore examples, alongside all of the other DataTables and Editor examples. You can see the examples running here.