Dancer - Table Edit
Table Edit lets you edit database data. It uses DBIx::Class models for database metadata.
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.
You can use your existing DBIx schema or let schema loader make one for you.
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: 1Make sure that you are using the appropriate UTF-8 flag for Dancer::Plugin::DBIC, as this plugin doesn't set it automatically.
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/deployTE can be updated via git from GitHub or from TE with API.
/api/updateGUI link in menu can be enabled in config file
TableEditor:
menu_settings:
update: 1Table Edit uses Angular and Bootstrap for the frontend. It is easy to change the Bootstrap theme to get a different look for Table Edit.
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.
TE lets you perform all basic operations with records in table.
Support for belongs_to, has_many and many_to_many relations for easy adding, removing and assigning records.
For this example we will use following model and config file.
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;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: 1TableEditor 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.ymlYou can also use an absolute path:
settings_file: /home/tedit/TableEdit/myte.yml
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],
});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: 50Number of items per page by default
page_size: 10Order and selection of classes that show up in menu. Default are all classes in alphabetical order.
menu_classes: [user, product]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: 0Under classes you can specify class properties.
Custom table lable. Default is generated from table name.
label: EmployeesDefault 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: itemRow 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";Autocomplete searches all columns by default. This can be altered with this attribute.
search_columns: [name, description] 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]By default list is not sorted. You can specify a default column.
grid_sort: username
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: 1You can set a column to be readonly in form view.
readonly = 1;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';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: categoryYou 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.
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:
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.
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 imagePlease 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:
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
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 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]Same thing just under certain column. Available levels are restricted, read, update.
classes:
Users:
columns:
salary:
restrict: [analyst, secretary]
update: [boss]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
New functionalities and customization can be achieved by adding plugins.
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
|-viewsLink 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.
Active plugins are listed in config. Order is important as first will have priority over second.
plugins: [ "SomePlugin", "SomeOtherPlugin" ]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);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;
};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;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;
}