NAME

Dancer - Table Edit

SYNOPSIS

Table Edit lets you edit database data. It uses DBIx::Class models for database metadata.

CONFIGURATION

You need a database and DBIx::Class models for this module to work. You can write your own DBIx::Class models, or use schema loader.

DBIx schema loader

You can use your existing DBIx schema or let schema loader make one for you.

Database config

You also have to set the DBIC connection details in the Dancer configuration (usually in the environments/*.yml files as this differs between systems).

plugins: 
   DBIC:
     default:
        dsn: dbi:Pg:dbname=__DATABASE_NAME__;host=localhost;port=__PORT__
        schema_class: TableEdit::Schema
        user: __USERNAME__
        pass: __PASSWORD__
        options:
               pg_enable_utf8: 1

Make sure that you are using the appropriate UTF-8 flag for Dancer::Plugin::DBIC, as this plugin doesn't set it automatically.

Schema deployment

If you have a schema and no db tables you want to use schema deploy to create them for you. After setting DB settings you simpli goto followning URL and wait for it.

/api/schema/deploy

Update

TE can be updated via git from GitHub or from TE with API.

/api/update

GUI link in menu can be enabled in config file

TableEditor:
        menu_settings: 
                update: 1

FRONTEND

Table Edit uses Angular and Bootstrap for the frontend. It is easy to change the Bootstrap theme to get a different look for Table Edit.

USE

With basic configuration done you can start using Table Edit. You will probably want to fine tune it a bit though. Configuration can also be set in schema classes.

CRUD

TE lets you perform all basic operations with records in table.

Relationships

Support for belongs_to, has_many and many_to_many relations for easy adding, removing and assigning records.

=for html

FINE TUNE

For this example we will use following model and config file.

Model

use utf8;
package TableEdit::Schema::Result::User;

use strict;
use warnings;

use base 'DBIx::Class::Core';

__PACKAGE__->table("user");

__PACKAGE__->add_columns(
  "id",
  { data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
  "username",
  { data_type => "varchar", is_nullable => 0, size => 45 },
  "email",
  { data_type => "varchar", is_nullable => 1, size => 90 },
  "description",
  { data_type => "text", is_nullable => 1 },
  "birthday",
  { data_type => "timestamp with time zone", is_nullable => 1 },
  "internal_code",
  { data_type => "integer", is_nullable => 1 },
  "created_date",
  {
    data_type     => "timestamp with time zone",
    default_value => \"current_timestamp",
    is_nullable   => 0,
    original      => { default_value => \"now()" },
  },
);

__PACKAGE__->set_primary_key("id");

__PACKAGE__->belongs_to(
  "company",
  "TableEdit::Schema::Result::Company",
  { id => "company_id" },
  { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" },
);

__PACKAGE__->has_many(
  "user_items",
  "TableEdit::Schema::Result::UserItem",
  { "foreign.approval_id" => "self.approval_id" },
  { cascade_copy => 0, cascade_delete => 0 },
);
        
__PACKAGE__->many_to_many("items", "user_items", "id", {class=>"Item",});

# Created by DBIx::Class::Schema::Loader v0.07033
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:g5NE5itWUoKXqfEKXj/8Rg

# You can replace this text with custom code or comments, and it will be preserved on regeneration
1;

Config file

TableEditor:
  dropdown_threshold: 50
  page_size: 10
  
  classes:
    User: 
      label: Employees
      grid_columns: [users_id, username]
      grid_sort: username
      form_columns: [users_id, username, email]
      to_string: | 
                 my $company = $self->company || "";
                 return "$self->username ($self->id) $company";
      columns:
             last_name:
                  label: 'Family name'                  
                first_name:
                  label: 'Name'
                created_date:
                  readonly: 1
                  column_type = 'date'
                internal_code:
                  hidden: 1
                description:
                  column_type = 'html'
    Order:
      columns:
        payment_method: 
          hidden: 1
        payment_status: 
          readonly: 1

TableEditor loads a specific configuration file in addition to the Dancer configuration files. You can specify the file name in the Dancer configuration with settings_file:

settings_file: myte.yml

You can also use an absolute path:

settings_file: /home/tedit/TableEdit/myte.yml
          

Configuration in schema classes

Setting can also be set in schema classes in similar fashion. So for example column properties are set like so (just add them to column definition):

"upload",
  {
    data_type => "varchar",
    default_value => \"null",
    is_nullable => 1,
    size => 255,
    column_type => 'image_upload'
  },

Class wide setting are in __PACKAGE__->resultset_attributes();

__PACKAGE__->resultset_attributes({ 
        label => 'Employees',
        grid_columns => [users_id, username],
});

Global properties

Dropdown threshold

You can set number of rows that are still ok for dropdown. Tables with more than that will show items ID with autocomplete search instead of dropdown by default.

dropdown_threshold: 50

Page size

Number of items per page by default

page_size: 10

Classes in menu

Order and selection of classes that show up in menu. Default are all classes in alphabetical order.

menu_classes: [user, product]

Concurrent active users

By default TE checks every 30 sec if for active users, to warn other users about it. You can set interval in sec or disable it (0)

active_users_interval: 0

Class properties

Under classes you can specify class properties.

Label

Custom table lable. Default is generated from table name.

label: Employees

Redirect after new item save

Default redirect is to list, but in some cases you may prefer to be redirected to newly created item, so you can manipulate relationships immediately. This can be set as follows:

redirect_after_create: item

Row in string representation

Row often has to be represented as string (titles, drop-down selectors, ...) so it is a good idea to define a custom, human readable stringification method. For example users username, his id in parentheses and company if he / she has one. It could be just username or something much complicated.

variable $row is passed to script and it represents a row as DBIx::Class::Row object. Snippet should return string.

to_string: | 
         my $company = $row->company || "";
         return "$row->username ($row->id) $company";

Class search columns

Autocomplete searches all columns by default. This can be altered with this attribute.

search_columns: [name, description]     

Grid / form visible columns

Often you don't care about all columns when you browse though rows or there are simply to many. You can specify a list of columns that will appear on grid or form. You have to be careful not to omit required columns and similar on form view.

grid_columns: [users_id, username]
form_columns: [users_id, username, email]

Grid sort

By default list is not sorted. You can specify a default column.

grid_sort: username

Column properties

Label

You can override column label by specifying it

label: 'Family name'

Hidden columns

Some columns are used only internally and you never want to see them in TableEdit. You can hide them.

hidden: 1

Readonly columns

You can set a column to be readonly in form view.

readonly = 1;

column data type

Columns have basic column types based on data types in db. You can override them to use different form element. Available widgets are: boolean, date, datetime, dropdown, html, image_upload, integer, text, varchar

column_type = 'text';

Dropdown values

For foreign keys of the belongs_to or might_have where the number of related records doesn't exceed dropdown_threshold we switch column type automatically to dropdown, otherwise we use autocomplete.

You can also specify dropdown values in the configuration:

type:
  column_type: dropdown
  dropdown_options:
    -
     option_label: Collection
     value: collection
    -
     option_label: Category
     value: category

Prefill column

You can set a value to fill field on "new" form.

prefill = '<strong>Important</strong>';

These column types are used on detail view and on list search.

Autocomplete foreign key

Automatic search on all columns on foreign class, to find foreign objects ID. You can also set which columns are searched on class.

column_type = 'autocomplete';

Example of image column conf:

HTML / WYSIWYG editor

Summernote editor is used. To use it just change column_type.

column_type = 'html';

The editor is included with the TableEditor sources:

Summernote

Included version is 0.7.1, downloaded from https://github.com/summernote/summernote/releases.

Angular Summernote

Included version is 0.7.0, downloaded from https://github.com/summernote/angular-summernote/releases.

TinyMCE HTML / WYSIWYG editor

You can also use more powerful and customizable editor TinyMCE.

column_type = 'tinymce';

TinyMCE settings can be set in this (or any other TE) config. Here is a sample:

TableEditor:      
  tiny_mce:
    plugins: print textcolor image
    toolbar: undo redo styleselect bold italic print forecolor backcolor image

Please note:

With TinyMCE you use "Enter" for a paragraph (<p>) and "Ctrl-Enter" for a linebreak (<br>).

To enable HTML source code view, add code both to plugins and toolbar.

Example of image column conf:

Image upload

Example of image column conf:

column_type: 'image_upload';
upload_dir: 'lib_item_img/'; # default is "images/upload/$class/$column/"
upload_max_size: 1000000; # in bytes, default is unlimited
upload_extensions: ['jpg', 'jpeg', 'gif', 'PNG']; # case insensitive
 

Permissions

Roles can have limited access to database. By default all roles are without rights except for special 'admin' role, which have all. You can define global right to role by adding it in appropriate array.

Following example illustrates scenario where admin has full (all of them), analyst and secretary have reading permission and boss has delete (and inclusive update and read). Role can be in multiple arrays.

read: [analyst, secretary]
update: []
        delete: [boss]
create: []
full: [admin]
 

Levels are hierarchical and they include:

read: []
update: [read]
delete: [update]
create: [update]
all: [*]
 

Table permission

Table specific permissions are similar to global ones, they are just nested under certain table. Also we can restrict it and overide global permission.

classes:
    Users:
        restrict: [analyst]
        delete: [boss]
        create: [secretary]

Column permission

Same thing just under certain column. Available levels are restricted, read, update.

classes:
    Users:
        columns:
            salary:
                restrict: [analyst, secretary]
                update: [boss]

Result subset permissions

If you want to limit records to some role, you can set conditions. For example showing only active (where active = 1) users to boss and secretary only non-admin users. The usual operators ( '=', '!=', -in, -not_in, etc.) are supported.

classes:
    Users:
        columns:
            active:
                subset:
                    boss: 1
            role:
                subset:
                    secretary: 
                        '!=': administrator
                            
                        
 

PLUGINS

New functionalities and customization can be achieved by adding plugins.

Adding plugin

All plugins are located in /Plugins/name_of_plugin dir. Inside they usually have following stucture:

Plugins
|-Some_plugin
        |-api.pm
        |-config.yml
        |-public
                |-images
                |-js
                        |-app.js
                |-views

Link to public dir is: api/plugins/<plugin name>/public/

Views will also be found in plugins view dir, if they don't exist in main views dir.

api.pm is required.

Activating plugin

Active plugins are listed in config. Order is important as first will have priority over second.

plugins: [ "SomePlugin", "SomeOtherPlugin" ]

Configuration file

Any plugin might have a configuration file, config.yml in the main directory of the plugin. For example:

TableEditor:
  classes:
    MessageType:
      to_string: |
         my $name = $row->name; $name =~ s/_/ /g; $name = ucfirst($name);

Custom pages

You can add new or redefine any page by adding custom route and redirect it to custom controller (or leave default) and custom template (or default). In js/app.js.

custom_routes['/User/edit/:id'] = { templateUrl: '/api/plugins/SomePlugin/public/views/user-form.html', controller: 'UserEditCtrl' };

var UserEditCtrl = function ($scope, $rootScope, $routeParams, Item, ClassItem, Url) {
        $scope.item = Item.read.get({
                class: 'User', 
                id: $routeParams.id}
        );
        $scope.data = ClassItem.get({class: 'User'});

        $scope.save = Item.update;
        $scope.related = Item.related_link;
};

Custom API

In api.pm routes can be overridden. This is useful for data manipulation before saving, data validation, custom information or similar.

Example usage:

package TableEdit::Plugins::SomePlugin::API;
use Dancer ':syntax';


prefix '/api';

# Data validation
post '/User' => sub {           
        my $post_data = from_json request->body;
        my $values = $post_data->{item}->{values};
        unless ($values->{last_name} eq 'Smith') {
                return to_json {error => {last_name => "Family only!"}};
        }
        
        pass;
};


get '/User' => require_login sub {
        my (@languages, $errorMessage);
        my $class = 'User';
        $schema_info ||= TableEdit::SchemaInfo->new(schema => schema);
        my $columns = [map {$_->hashref} @{TableEdit::Routes::API::columns_info($class, TableEdit::Routes::API::class_form_columns($class))}];
        my $relationships = [map {$_->hashref} $schema_info->relationships($class)];

        return to_json({ 
                custom_information => 'this is custom!',
                columns => $columns,
                class => $class,
                class_label => $schema_info->{$class}->{label},
                relations => $relationships,
        }, {allow_unknown => 1}); 
};

1;

Menu hook

In api.pm you can implement sub 'menu' that recieves menu hash and has to return modified verison. Example:

sub menu {
        my ($self, $menu) = @_;
        $menu->{MyNewBlock} = {sort => 120, items => [{name => 'Dashboard', url => '/myPluign/dashboard'}]};
        return $menu;
}