Warn

POD ERRORS here is normal because DBIx::POS::Template used.

Mojolicious::Plugin::RoutesAuthDBI::Install

¡ ¡ ¡ ALL GLORY TO GLORIA ! ! !

NAME

Mojolicious::Plugin::RoutesAuthDBI::Install - is a Mojolicious::Controller for installation instructions. DB schema (PostgreSQL) and sample app.

DB DESIGN DIAGRAM

See https://github.com/mche/Mojolicious-Plugin-RoutesAuthDBI/blob/master/Diagram.svg

Manual

$ perl -e "use Mojo::Base 'Mojolicious'; __PACKAGE__->new()->start(); sub startup {shift->routes->route('/')->to('install#manual', namespace=>'Mojolicious::Plugin::RoutesAuthDBI');}" get / 2>/dev/null

DB schema (postgresql)

View schema (define the postgresql schema name)

$ perl -e "use Mojo::Base 'Mojolicious'; __PACKAGE__->new()->start(); sub startup {shift->routes->route('/schema/:schema')->to('install#schema', namespace=>'Mojolicious::Plugin::RoutesAuthDBI');}" get /schema/<name> 2>/dev/null #  get /schema/foo || get /schema/public

Apply schema (define the postgresql schema name)

$ perl -e "use Mojo::Base 'Mojolicious'; __PACKAGE__->new()->start(); sub startup {shift->routes->route('/schema/:schema')->to('install#schema', namespace=>'Mojolicious::Plugin::RoutesAuthDBI');}" get /schema/<name> 2>/dev/null | psql -d <dbname> # get /schema/foo || get /schema/public

Sample test-app.pl

$ perl -e "use Mojo::Base 'Mojolicious'; __PACKAGE__->new()->start(); sub startup {shift->routes->route('/')->to('install#test_app', namespace=>'Mojolicious::Plugin::RoutesAuthDBI');}" get / 2>/dev/null > test-app.pl

Define DBI->connect(.........) and some plugin options in test-app.pl

Check list of admin routes:

$ perl test-app.pl routes

Start app

$ perl test-app.pl daemon

Trust url for admin-user creation:

$ perl test-app.pl get /<pluginconf->{admin}{prefix}>/<pluginconf->{admin}{trust}>/user/new/<new admin login>/<admin pass> 2>/dev/null

Sign in by browser:

Go to http://127.0.0.1:3000/sign/in/<new admin login>/<admin pass>

Admin index:

Go to http://127.0.0.1:3000/<pluginconf->{admin}{prefix}>

Administration of system ready!

DB design

• Schema name

  CREATE SCHEMA IF NOT EXISTS "{% $schema %}";
  set local search_path = "{% $schema %}";

• Sequence

  -- you may change schema name for PostgreSQL objects
  
  CREATE SEQUENCE {% $schema %}ID;-- one sequence for all tables id

• Routes

  CREATE TABLE {% $schema %}routes (
    id integer default nextval('{% $schema %}ID'::regclass) not null primary key,
    ts timestamp without time zone default now() not null,
    request character varying not null,
    name character varying not null unique,
    descr text null,
    auth varchar null,-- was bit(1): alter table {% $schema %}routes alter column auth type varchar;
    disable bit(1) null,
    -- interval_ts - смещение ts (seconds) для приоритета маршрута, т.е. влияет на сортровку маршрутов
    interval_ts int null -- was order_by int null; alter table {% $schema %}routes rename column order_by to interval_ts;
  );

• Namespaces

  create table {% $schema %}namespaces (
    id integer default nextval('{% $schema %}ID'::regclass) not null primary key,
    ts timestamp without time zone default now() not null,
    namespace character varying not null unique,
    descr text null,
    app_ns bit(1) null, -- alter table {% $schema %}namespaces add column app_ns bit(1) null;
    -- interval_ts - смещение ts (seconds) для приоритета namespace
    interval_ts int null -- alter table {% $schema %}namespaces add column interval_ts int null;
  );

• Controllers

  create table {% $schema %}controllers (
    id integer default nextval('{% $schema %}ID'::regclass) not null primary key,
    ts timestamp without time zone default now() not null,
    controller character varying not null,
    descr text null
  );

• Actions

  create table {% $schema %}actions (
    id integer default nextval('{% $schema %}ID'::regclass) not null primary key,
    ts timestamp without time zone default now() not null,
    action character varying not null,
    callback text null,
    descr text null
  );

• Users

  create table {% $schema %}users (
    id int default nextval('{% $schema %}ID'::regclass) not null  primary key,
    ts timestamp without time zone default now() not null,
    login varchar not null unique,
    pass varchar not null,
    disable bit(1)
  );

• Roles

  create table {% $schema %}roles (
    id int default nextval('{% $schema %}ID'::regclass) not null  primary key,
    ts timestamp without time zone default now() not null,
    name varchar not null unique,
    disable bit(1)
  );

• Refs

  create table {% $schema %}refs (
    id int default nextval('{% $schema %}ID'::regclass) not null  primary key,
    ts timestamp without time zone default now() not null,
    id1 int not null,
    id2 int not null,
    unique(id1, id2)
  );
  create index on {% $schema %}refs (id2);

Drop schema

drop table {% $schema %}refs;
drop table {% $schema %}users;
drop table {% $schema %}roles;
drop table {% $schema %}routes;
drop table {% $schema %}controllers;
drop table {% $schema %}actions;
drop table {% $schema %}namespaces;
drop sequence {% $schema %}ID;

Flush schema

delete from {% $schema %}refs;
delete from {% $schema %}users;
delete from {% $schema %}roles;
delete from {% $schema %}routes;
delete from {% $schema %}controllers;
delete from {% $schema %}namespaces;
delete from {% $schema %}actions;

cpanm Mojolicious::Plugin::RoutesAuthDBI

perl -MCPAN -e shell
install Mojolicious::Plugin::RoutesAuthDBI