Warn

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

Mojolicious::Plugin::RoutesAuthDBI::DB

¡ ¡ ¡ ALL GLORY TO GLORIA ! ! !

NAME

Mojolicious::Plugin::RoutesAuthDBI::DB - DB schema (PostgreSQL).

DB DESIGN DIAGRAM

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

DB design

Schema

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 table

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 table

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 table

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 table

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 table

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 table

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 table

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

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

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