Warn

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

Mojolicious::Plugin::RoutesAuthDBI::POS::Access

¡ ¡ ¡ ALL GLORY TO GLORIA ! ! !

NAME

Mojolicious::Plugin::RoutesAuthDBI::POS::Access - POS-dict for access statements Mojolicious::Plugin::RoutesAuthDBI::Access.

DB DESIGN DIAGRAM

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

SYNOPSIS

my $pos = Mojolicious::Plugin::RoutesAuthDBI::POS::Access->new(template=>{tables=>{...}});

my $sth = $dbh->prepare($pos->{'foo'});

Methods

One new()

new()

Input args for new:

template - hashref

Vars for template system of POS-statements.

SEE ALSO

DBIx::POS::Template

SQL definitions

profile

Load auth profile

{cached=>1}

select p.*, l.login, l.pass
from "{% $schema %}"."{% $tables{profiles} %}" p
left join (
  select l.*, r.id1
  from "{% $schema %}"."{% $tables{refs} %}" r 
    join "{% $schema %}"."{% $tables{logins} %}" l on l.id=r.id2
) l on p.id=l.id1

where p.id=? or l.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 %}"."{% $tables{routes} %}" r
  join "{% $schema %}"."{% $tables{refs} %}" rf on r.id=rf.id1
  join 
  (
    select a.*, c.*
    from "{% $schema %}"."{% $tables{actions} %}" a 
    left join (
      select r.id2 as _id, c.controller, c.id as controller_id, n.namespace, n.id as namespace_id
      from 
        "{% $schema %}"."{% $tables{refs} %}" r
        join "{% $schema %}"."{% $tables{controllers} %}" c on r.id1=c.id
        left join "{% $schema %}"."{% $tables{refs} %}" r2 on c.id=r2.id2
        left join "{% $schema %}"."{% $tables{namespaces} %}" n on n.id=r2.id1
    ) c on a.id=c._id
  ) ac on rf.id2=ac.id
order by r.ts - (coalesce(r.interval_ts, 0::int)::varchar || ' second')::interval;

profile roles

Роли пользователя(профиля)

{cached=>1}

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

cnt refs

check if ref between [IDs1] and [IDs2] exists

{cached=>1}

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

access action

доступ к действию в контроллере (действие-каллбак - доступ проверяется по его ID)

{cached=>1}

select count(r.*)
from
  "{% $schema %}"."{% $tables{refs} %}" rc 
  join "{% $schema %}"."{% $tables{actions} %}" a on a.id=rc.id2
  join "{% $schema %}"."{% $tables{refs} %}" r on a.id=r.id1
  ---join "{% $schema %}"."{% $tables{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

доступ ко всем действиям по имени спейса

{cached=>1}

select count(n.*)
from 
  "{% $schema %}"."{% $tables{namespaces} %}" n
  join "{% $schema %}"."{% $tables{refs} %}" r on n.id=r.id1
  ---join "{% $schema %}"."{% $tables{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

Доступ по роли

{cached=>1}

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

namespaces

select *
from "{% $schema %}"."{% $tables{namespaces} %}"
{% $where %}
{% $order %};

controller

Не пустой namespace - четко привязанный контроллер, пустой - обязательно не привязанный контроллер

{cached=>1}

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

cpanm Mojolicious::Plugin::RoutesAuthDBI

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