Warn

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

Mojolicious::Plugin::RoutesAuthDBI::Schema

¡ ¡ ¡ ALL GLORY TO GLORIA ! ! !

NAME

Mojolicious::Plugin::RoutesAuthDBI::Schema - 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 search_path = "{% $schema %}";

Sequence

-- you may change schema name for PostgreSQL objects

CREATE SEQUENCE {% $sequence %};-- one sequence for all tables id

Routes table

CREATE TABLE "{% $schema %}"."{% $tables{routes} %}" (
  id integer default nextval('{% $sequence %}'::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 %}"."{% $tables{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 %}"."{% $tables{routes} %}" rename column order_by to interval_ts;
);

Namespaces table

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

Controllers table

create table "{% $schema %}"."{% $tables{controllers} %}" (
  id integer default nextval('{% $sequence %}'::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 %}"."{% $tables{actions} %}" (
  id integer default nextval('{% $sequence %}'::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
);

Logins table

Its logins table

create table "{% $schema %}"."{% $tables{logins} %}" (
  id int default nextval('{% $sequence %}'::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)
);

Profiles table

create table "{% $schema %}"."{% $tables{profiles} %}" (
  id int default nextval('{% $sequence %}'::regclass) not null  primary key,
  ts timestamp without time zone default now() not null,
  names text[] not null,
  disable bit(1)
);

Roles table

create table "{% $schema %}"."{% $tables{roles} %}" (
  id int default nextval('{% $sequence %}'::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 %}"."{% $tables{refs} %}" (
  id int default nextval('{% $sequence %}'::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);

Oauth sites

Конфиг внешних сайтов, используемых в проекте

create table IF NOT EXISTS "{% $schema %}"."{% $tables{oauth_sites} %}"  (
  id integer not null DEFAULT nextval('{% $sequence %}'::regclass) primary key,-- sequence!
  name varchar not null unique,
  conf jsonb not null -- тут ключи приложений
);

Oauth users

Oauth пользователи

create table IF NOT EXISTS "{% $schema %}"."{% $tables{oauth_users} %}" (
  id integer NOT NULL DEFAULT nextval('{% $sequence %}'::regclass) primary key,
  ts timestamp without time zone NOT NULL DEFAULT now(),
  site_id int not null,
  user_id varchar not null, --
  profile jsonb,
  profile_ts timestamp without time zone NOT NULL DEFAULT now(),
  unique (site_id, user_id)
);

Drop

drop table "{% $schema %}"."{% $tables{refs} %}";
drop table "{% $schema %}"."{% $tables{logins} %}";
drop table "{% $schema %}"."{% $tables{profiles} %}";
drop table "{% $schema %}"."{% $tables{roles} %}";
drop table "{% $schema %}"."{% $tables{routes} %}";
drop table "{% $schema %}"."{% $tables{controllers} %}";
drop table "{% $schema %}"."{% $tables{actions} %}";
drop table "{% $schema %}"."{% $tables{namespaces} %}";
drop table "{% $schema %}"."{% $tables{oauth_sites} %}";
drop table "{% $schema %}"."{% $tables{oauth_users} %}";
drop sequence {% $sequence %};

Flush

delete from "{% $schema %}"."{% $tables{refs} %}";
delete from "{% $schema %}"."{% $tables{logins} %}";
delete from "{% $schema %}"."{% $tables{profiles} %}";
delete from "{% $schema %}"."{% $tables{roles} %}";
delete from "{% $schema %}"."{% $tables{routes} %}";
delete from "{% $schema %}"."{% $tables{controllers} %}";
delete from "{% $schema %}"."{% $tables{namespaces} %}";
delete from "{% $schema %}"."{% $tables{actions} %}";
delete from "{% $schema %}"."{% $tables{oauth_sites} %}";
delete from "{% $schema %}"."{% $tables{oauth_users} %}";

cpanm Mojolicious::Plugin::RoutesAuthDBI

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