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