view release on metacpan or  search on metacpan

schema.sql  view on Meta::CPAN

      raise EXCEPTION 'Modification of stamp fields is not allowed';
    end if;
    ns.updated_at := current_timestamp;
    ns.updated_by := current_user;
  end if;
  NEW.mod_stamp := ns;
  return NEW;
end;
$body$;


create table adapters (
  adapter_class   d_perl_class_name not null,
  dbms_name       text not null, --canonic name
  constraint pk_adapters primary key (adapter_class)
);
comment on table adapters is
  $$Available adapters$$;

insert into adapters values ('DBIx::PgLink::Adapter', 'generic');
insert into adapters values ('DBIx::PgLink::Adapter::Pg', 'PostgreSQL');
insert into adapters values ('DBIx::PgLink::Adapter::SQLite', 'SQLite');
insert into adapters values ('DBIx::PgLink::Adapter::SybaseASE', 'Sybase');
insert into adapters values ('DBIx::PgLink::Adapter::MSSQL', 'MSSQL');
insert into adapters values ('DBIx::PgLink::Adapter::XBase', 'XBase');


create table dbms_adapters (
  adapter_class   d_perl_class_name not null,
  dbms_name       text not null,
  constraint pk_dbms_adapters primary key (dbms_name)
);
comment on table dbms_adapters is 
  $$Adapter lookup by name of DBMS.$$;

insert into dbms_adapters values ('DBIx::PgLink::Adapter::Pg', 'Pg');
insert into dbms_adapters values ('DBIx::PgLink::Adapter::Pg', 'PostgreSQL');
insert into dbms_adapters values ('DBIx::PgLink::Adapter::Pg', 'Postgres');
insert into dbms_adapters values ('DBIx::PgLink::Adapter::SQLite', 'SQLite');
insert into dbms_adapters values ('DBIx::PgLink::Adapter::SybaseASE', 'Sybase');
insert into dbms_adapters values ('DBIx::PgLink::Adapter::SybaseASE', 'Sybase ASE');
insert into dbms_adapters values ('DBIx::PgLink::Adapter::SybaseASE', 'Sybase Adaptive Server Enterprise');
insert into dbms_adapters values ('DBIx::PgLink::Adapter::MSSQL', 'MSSQL');
insert into dbms_adapters values ('DBIx::PgLink::Adapter::MSSQL', 'Miscrosoft SQL Server');
insert into dbms_adapters values ('DBIx::PgLink::Adapter::XBase', 'XBase');


create table connections (
  conn_name     text not null,
  data_source   text not null,
  adapter_class d_perl_class_name not null default 'DBIx::PgLink::Adapter',
  logon_mode    d_logon_mode,
  comment       text null,
  use_libs      text[],
  mod_stamp     t_mod_stamp,
  constraint pk_connections primary key (conn_name),
  constraint fk_connections_adapters foreign key (adapter_class) references adapters(adapter_class)
    on update restrict on delete restrict
);
comment on table connections is 
  $$Connection metadata and default login credentials$$;
comment on column connections.conn_name is 
  $$Connection name. Any string.$$;
comment on column connections.data_source is 
  $$Data source name for DBI. Example: 'dbi:Pg:host=127.0.0.1;port=5432;db=postgres'$$;
comment on column connections.adapter_class is 
  $$Subclass of DBIx::PgLink::Adapter that handles connection. Perl class name.$$;
comment on column connections.logon_mode is 
  $$Used when not exist mapping between local and remote login
    'empty' - Connect with empty user name and empty password
    'current' - Connect as current user without password
    'default' - Connect as default user (users.local_user='') with default password
    'deny' - Connection refused$$;
comment on column connections.use_libs is 
  $$Text array of directories, append to Perl @INC$$;

create trigger connections_mod_stamp before insert or update
  on connections
  for each row execute procedure mod_stamp_trg();

insert into connections (conn_name, data_source, comment) values ('', '', 'Default connection');

create function connections_trg_protect_default() returns trigger language plpgsql as $body$
begin
  if NEW.conn_name = '' then
    raise EXCEPTION 'Default connection can not be changed or deleted';
  end if;
  return NEW;
end;
$body$;

create trigger connections_trg_protect_default before insert or update on connections
  for each row execute procedure connections_trg_protect_default();


create table attributes (
  conn_name       text not null,
  local_user      text not null default '', --'' for all users
  attr_name       text not null,
  attr_value      text null, --scalar value
  mod_stamp       t_mod_stamp,
  constraint pk_attributes primary key (conn_name, local_user, attr_name),
  constraint fk_attributes_connections foreign key (conn_name) references connections(conn_name)
    on update cascade  on delete cascade
);
comment on table attributes is $$Connection attributes passed to Adapter and DBI objects$$;

create trigger attributes_mod_stamp before insert or update
  on attributes
  for each row execute procedure mod_stamp_trg();

create table roles (
  conn_name       text not null,
  local_user      text not null default '', --'' for all users
  role_kind       text check (role_kind in ('Adapter', 'Connector')) default 'Adapter', 
  role_seq        int4 not null,
  role_name       d_perl_class_name not null,
  mod_stamp       t_mod_stamp,
  constraint pk_roles primary key (conn_name, local_user, role_kind, role_seq),
  constraint ak_roles unique (conn_name, local_user, role_name),
  constraint fk_roles_connections foreign key (conn_name) references connections(conn_name)
    on update cascade  on delete cascade
);
comment on table roles is $$Roles applied to Adapter or Connector objects and affected its behaviour.
'role_name' must be a name of Perl module.$$;

create trigger roles_mod_stamp before insert or update
  on roles
  for each row execute procedure mod_stamp_trg();


create table environment (
  conn_name       text not null,
  local_user      text not null default '', --'' for all users
  env_action      text check (env_action in ('set','append')) default 'set',
  env_name        text not null,
  env_value       text null, --null = unset
  mod_stamp       t_mod_stamp,
  constraint pk_environment primary key (conn_name, local_user, env_name),
  constraint fk_environment_connections foreign key (conn_name) references connections(conn_name)
    on update cascade  on delete cascade
);
comment on table environment is 
  $$Operating system environment variables set before connection$$;

create trigger environment_mod_stamp before insert or update
  on environment
  for each row execute procedure mod_stamp_trg();


create table users (
  conn_name       text not null,
  local_user      text not null, -- = session_user | '' for default
  remote_user     text null,
  remote_password text null,
  mod_stamp       t_mod_stamp,
  constraint pk_users primary key (conn_name, local_user),
  constraint fk_users_connections foreign key (conn_name) references connections(conn_name)
    on update cascade  on delete cascade
);
comment on table users is
  $$Mapping between local and remote logins. Default credentials can be added as local_user = '' (empty string).
See comment for connection.logon_mode column$$;

create trigger users_mod_stamp before insert or update
  on users
  for each row execute procedure mod_stamp_trg();


create table data_type_map (
  conn_name       text default '' not null,
  adapter_class   d_perl_class_name not null default 'DBIx::PgLink::Adapter',
  remote_type     text not null,
  local_type      text not null,
  standard_type   text not null,
  insertable      bool not null default true,
  updatable       bool not null default true,
  conv_to_local   text null,
  conv_to_remote  text null,
  quote_literal   bool not null,
  constraint pk_types primary key (conn_name, adapter_class, remote_type),
  constraint fk_types_connections foreign key (conn_name) references connections(conn_name)
    on update cascade  on delete cascade
);
comment on table data_type_map is
  $$Mapping of remote data type$$;
comment on column data_type_map.conv_to_local  is $$Adapter method name to convert value from remote db to PostgreSQL$$;
comment on column data_type_map.conv_to_remote is $$Adapter method name to convert value from PostgreSQL to remote db$$;


create table object_types (
  object_type  text not null,
  object_class text not null,
  constraint pk_object_types primary key (object_type)
);
comment on table object_types is
  $$Remote object can possibly change its type to another of the same class.
For example TABLE can be replace by VIEW or vice versa.
Local accessor for TABLE or VIEW is the same.$$;

insert into object_types values ('TABLE',     'TABLE');
insert into object_types values ('VIEW',      'TABLE');
insert into object_types values ('FUNCTION',  'ROUTINE');
insert into object_types values ('PROCEDURE', 'ROUTINE');

create or replace function object_type_class(_type text) returns text 
language sql immutable --actually it is very _stable_ function
as $body$
SELECT object_class FROM dbix_pglink.object_types WHERE object_type = $1
$body$;


create sequence object_id_sequence;
create table objects (
  object_id            int4 default nextval('object_id_sequence') not null,
  conn_name            text not null,
  remote_object_type   text not null, --remote object type (TABLE/VIEW/etc)
  remote_catalog       text null,     --remote catalog name, not quoted
  remote_schema        text null,     --remote schema name, not quoted
  remote_object        text not null, --remote object name, not quoted
    -- (for routine can be contain id or full function signature)
  local_schema         text not null, --local schema name, not quoted