=encoding utf8
=pod
=head1 NAME
Dancer - Table Edit
=head1 SYNOPSIS
Table Edit lets you edit database data. It uses L
=for html
=head1 CONFIGURATION
You need a database and L
=head2 DBIx schema loader
You can use your existing DBIx schema or let schema loader make one for you.
=head2 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 L
=head2 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
=head2 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
=head1 FRONTEND
Table Edit uses L<Angular|http://angularjs.org/Angular> and L<Bootstrap|http://getbootstrap.com/> for the frontend. It is easy to change the Bootstrap theme to get a different look for Table Edit.
=head1 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.
=head2 CRUD
TE lets you perform all basic operations with records in table.
=for html
=head2 Relationships
Support for belongs_to, has_many and many_to_many relations for easy adding, removing and assigning records.
=for html
=for html=head1 FINE TUNE
For this example we will use following model and config file.
=head2 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;
=head2 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 C
settings_file: myte.yml
You can also use an absolute path:
settings_file: /home/tedit/TableEdit/myte.yml
=head2 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],
});
=head2 Global properties
=head3 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
=head3 Page size
Number of items per page by default
page_size: 10
=head3 Classes in menu
Order and selection of classes that show up in menu. Default are all classes in alphabetical order.
menu_classes: [user, product]
=head3 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
=head2 Class properties
Under classes you can specify class properties.
=head3 Label
Custom table lable. Default is generated from table name.
label: Employees
=head3 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
=head3 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";
=head3 Class search columns
Autocomplete searches all columns by default. This can be altered with this attribute.
search_columns: [name, description]
=head3 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]
=head3 Grid sort
By default list is not sorted. You can specify a default column.
grid_sort: username
=head2 Column properties
=for html
=head3 Label
You can override column label by specifying it
label: 'Family name'
=head3 Hidden columns
Some columns are used only internally and you never want to see them in TableEdit. You can hide them.
hidden: 1
=head3 Readonly columns
You can set a column to be readonly in form view.
readonly = 1;
=head3 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';
=head3 Dropdown values
For foreign keys of the C
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
=head3 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.
=head4 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:
=head4 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:
=over 4
=item Summernote
Included version is 0.7.1, downloaded from Lhttps://github.com/summernote/summernote/releases.
=item Angular Summernote
Included version is 0.7.0, downloaded from Lhttps://github.com/summernote/angular-summernote/releases.
=back
=head4 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 (C<E
To enable HTML source code view, add C both to plugins and toolbar.
Example of image column conf:
=head4 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
=head2 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: [*]
=head3 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]
=head3 Column permission
Same thing just under certain column. Available levels are restricted, read, update.
classes:
Users:
columns:
salary:
restrict: [analyst, secretary]
update: [boss]
=head3 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
=head1 PLUGINS
New functionalities and customization can be achieved by adding plugins.
=head2 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/
Views will also be found in plugins view dir, if they don't exist in main views dir.
api.pm is required.
=head2 Activating plugin
Active plugins are listed in config. Order is important as first will have priority over second.
plugins: [ "SomePlugin", "SomeOtherPlugin" ]
=head2 Configuration file
Any plugin might have a configuration file, F
TableEditor: classes: MessageType: tostring: | my $name = $row->name; $name =~ s// /g; $name = ucfirst($name);
=head2 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;
};
=head2 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;
=head3 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;
}
=cut