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,
      order_by int null
    );

• 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
    );

• 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