NAME

Mojolicious::Plugin::RoutesAuthDBI::PgSQL - is a SQL hub for Mojolicious::Plugin::RoutesAuthDBI.

SYNOPSIS

my $sql = bless [$dbh, {}], 'Mojolicious::Plugin::RoutesAuthDBI::PgSQL';
my $r = $dbh->selectrow_hashref($sql->sth('foo key'));

DESCRIPTION

A whole class DBI statement cache.

SQL schema

Refs between three tables in order: route -> role -> user

• Pg sequence for column id on all tables

  create sequence ID;

• Table of routes

  create table routes(
  id integer default nextval('ID'::regclass) not null primary key,
  ts timestamp without time zone default now() not null,
  request character varying null,
  namespace character varying null,
  controller character varying not null,
  action character varying null,
  name character varying not null,
  descr text,
  auth bit(1),
  disable bit(1),
  order_by int
  );

• Table of users

  create table users(
      id int default nextval('ID'::regclass) not null  primary key,
      ts timestamp without time zone default now() not null,
      login varchar not null unique,
      pass varchar not null
  );

• Table of users roles

  create table roles(
      id int default nextval('ID'::regclass) not null  primary key,
      ts timestamp without time zone default now() not null,
      name varchar not null unique
  );

• Table of references between routes, users and roles

  create table refs(
      id int default nextval('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 refs (id2);

  where: id1 - primary id of reference, id2 - secondary id of reference