Warn

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

Mojolicious::Plugin::RoutesAuthDBI::POS::Pg

¡ ¡ ¡ ALL GLORY TO GLORIA ! ! !

NAME

Mojolicious::Plugin::RoutesAuthDBI::POS::Pg - POS for PostgreSQL.

DB DESIGN DIAGRAM

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

SYNOPSIS

use DBIx::POS::Template;

my @path = split(/\//, __FILE__ );
my $file = join('/', @path[0 .. $#path -1], 'POS/Pg.pm');
my $pos = DBIx::POS::Template->new($file, enc => 'utf8');
my $sth = $dbh->prepare($pos->{'user'});

SEE ALSO

DBIx::POS::Template

SQL definitions

For access methods

• user

  select *
  from {% $schema %}users
  where id = ? or login=?

• apply routes

  select r.*, ac.controller, ac.namespace, ac.action, ac.callback, ac.id as action_id, ac.controller_id, ac.namespace_id
  from {% $schema %}routes r
    join {% $schema %}refs rf on r.id=rf.id1
    join 
    (select a.*, c.controller, c.id as controller_id, n.namespace, n.id as namespace_id
      from {% $schema %}actions a 
      left join {% $schema %}refs r on a.id=r.id2
      left join {% $schema %}controllers c on c.id=r.id1
      left join {% $schema %}refs r2 on c.id=r2.id2
      left join {% $schema %}namespaces n on n.id=r2.id1
    ) ac on rf.id2=ac.id
  order by r.order_by, r.ts;

• user roles

  select g.*
  from
    {% $schema %}roles g
    join {% $schema %}refs r on g.id=r.id1
  where r.id2=?;
  --and coalesce(g.disable, 0::bit) <> 1::bit

• cnt refs

  select count(*)
  from {% $schema %}refs
  where id1 = any(?) and id2 = ANY(?);

• access action

  select count(r.*)
  from
    {% $schema %}refs rc 
    join {% $schema %}actions a on a.id=rc.id2
    join {% $schema %}refs r on a.id=r.id1
    ---join {% $schema %}roles o on o.id=r.id2
  where
    rc.id1=? ---controller id
    and a.action=?
    and r.id2=any(?) --- roles ids
    ---and coalesce(o.disable, 0::bit) <> 1::bit
  ;

• access namespace

  select count(n.*)
  from 
    {% $schema %}namespaces n
    join {% $schema %}refs r on n.id=r.id1
    ---join {% $schema %}roles o on r.id2=o.id
  where
    n.namespace=?
    and r.id2=any(?) --- roles ids
    ---and coalesce(o.disable, 0::bit) <> 1::bit
  ;

• access role

  select count(*)
  from {% $schema %}roles
  where (id = ? or name = ?)
    and id = any(?)
    and coalesce(disable, 0::bit) <> 1::bit
  ;

For administration actions (controller)

• new user

  insert into {% $schema %}users (login, pass) values (?,?)
  returning *;

• role

  select *
  from {% $schema %}roles
  where id=? or lower(name)=?

• new role

  insert into {% $schema %}roles (name) values (?)
  returning *;

• dsbl/enbl role

  update {% $schema %}roles set disable=?::bit where id=? or lower(name)=?
  returning *;

• ref

  select *
  from {% $schema %}refs
  where id1=? and id2=?;

• new ref

  insert into {% $schema %}refs (id1,id2) values (?,?)
  returning *;

• del ref

  delete from {% $schema %}refs
  where id1=? and id2=?
  returning *;

• controller

  select c.*, n.namespace, n.id as namespace_id, n.descr as namespace_descr
  from
    {% $schema %}controllers c
    left join {% $schema %}refs r on c.id=r.id2
    left join {% $schema %}namespaces n on n.id=r.id1
  
  where
    c.controller=?
    and (n.namespace=? or (?::varchar is null and n.id is null))
    

• new controller

  insert into {% $schema %}controllers (controller, descr)
  values (?,?)
  returning *;

• new route

  insert into {% $schema %}routes (request, name, auth, descr, disable, order_by)
  values (?,?,?,?,?,?,?,?,?)
  returning *;

• role users

  select u.*
  from
    {% $schema %}users u
    join {% $schema %}refs r on u.id=r.id2
  where r.id1=?;

• role routes

  select t.*
  from
    {% $schema %}routes t
    join {% $schema %}refs r on t.id=r.id1
  where r.id2=?;

• controllers

  select c.*, n.namespace, n.id as namespace_id, n.descr as namespace_descr
    from {% $schema %}controllers c
    left join {% $schema %}refs r on c.id=r.id2
    left join {% $schema %}namespaces n on n.id=r.id1
    ;

• namespaces

  select *
  from {% $schema %}namespaces;

• namespace

  select *
  from {% $schema %}namespaces
  where namespace = ?;

• new namespace

  insert into {% $schema %}namespaces (namespace, descr) values (?,?)
  returning *;

• actions

  select a.*, c.namespace, c.controller
  from {% $schema %}actions a
    left join {% $schema %}refs r on a.id=r.id2
    left join {% $schema %}controllers c on c.id=r.id1

• тест

  ыудусе * акщь тест!ж