view release on metacpan or  search on metacpan

lib/Mojolicious/Plugin/RoutesAuthDBI/Schema.pm  view on Meta::CPAN

-- роли
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,
  descr text,
  disable boolean
);

@@ refs
-- Связи
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 %}"."{%= $tables->{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)
);

@@ guests
-- Гостевые пользователи
create table IF NOT EXISTS "{%= $schema %}"."{%= $tables->{guests} %}" (
  id integer NOT NULL DEFAULT nextval('{%= $sequence %}'::regclass) primary key,
  ts timestamp without time zone NOT NULL DEFAULT now(),
  data jsonb null
);

@@ logs
-- auth || guest user only
create table IF NOT EXISTS "{%= $schema %}"."{%= $tables->{logs} %}" (
  ts timestamp without time zone NOT NULL DEFAULT now(),
  user_id int not null,  --- and profile or guest id
  route_id int,
  url text, --- if none route_id
  status int not null, --- http
  elapsed numeric not null ---seconds  from mojo.timer
);

@@ drop
--drop table "{%= $schema %}"."{%= $tables->{refs} %}" cascade;
--drop table "{%= $schema %}"."{%= $tables->{logins} %}" cascade;
--drop table "{%= $schema %}"."{%= $tables->{profiles} %}" cascade;
--drop table "{%= $schema %}"."{%= $tables->{roles} %}" cascade;
--drop table "{%= $schema %}"."{%= $tables->{routes} %}" cascade;
--drop table "{%= $schema %}"."{%= $tables->{controllers} %}" cascade;
--drop table "{%= $schema %}"."{%= $tables->{actions} %}" cascade;
--drop table "{%= $schema %}"."{%= $tables->{namespaces} %}" cascade;
--drop table "{%= $schema %}"."{%= $tables->{oauth_sites} %}" cascade;
--drop table "{%= $schema %}"."{%= $tables->{oauth_users} %}" cascade;

drop schema "{%= $schema %}" cascade;
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} %}";
delete from "{%= $schema %}"."{%= $tables->{guests} %}";
delete from "{%= $schema %}"."{%= $tables->{logs} %}";