DBIx-PgLink
view release on metacpan or search on metacpan
lib/DBIx/PgLink/Accessor/BaseAccessor.pm view on Meta::CPAN
return $class->new( %{$data}, connector => $p->{connector} );
};
method delete_metadata_by_id => positional(
{isa=>'Int', required=>1},
) => sub {
my ($self, $object_id) = @_;
# delete base row by id
# foreign key cascade to child metadata (columns, queries, etc)
pg_dbh->do(<<'END_OF_SQL',
DELETE FROM dbix_pglink.objects
WHERE object_id = $1
END_OF_SQL
{types=>[qw/INT4/]},
$object_id,
);
};
sub load_metadata_by_local_name {
lib/DBIx/PgLink/Accessor/Query.pm view on Meta::CPAN
END_OF_SQL
{no_cursor=>1, types=>[qw/INT4 TEXT/] }
);
},
);
sub save {
my $self = shift;
# delete old query (cascade to query_params)
pg_dbh->do(<<'END_OF_SQL',
DELETE
FROM dbix_pglink.queries
WHERE object_id = $1
and action = $2
END_OF_SQL
{no_cursor=>1, types=>[qw/INT4 TEXT/]},
$self->parent->object_id,
$self->action,
);
lib/DBIx/PgLink/Accessor/Table.pm view on Meta::CPAN
$self->create_rules;
return 1;
};
sub drop_local_objects {
my $self = shift;
for my $obj (
['VIEW', $self->view_quoted], # cascade to rules
['TABLE', $self->shadow_table_quoted], # cascade to triggers
['FUNCTION', $self->function0_quoted_sign],
['FUNCTION', $self->function3_quoted_sign],
['FUNCTION', $self->function_set_filter_quoted_sign],
['FUNCTION', $self->function_reset_filter_quoted_sign],
['TYPE', $self->rowtype_quoted],
) {
pg_dbh->do("DROP $obj->[0] IF EXISTS $obj->[1]");
}
};
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();
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,
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
local_object text not null, --local object name, not quoted
mod_stamp t_mod_stamp,
constraint pk_objects primary key (object_id),
constraint fk_objects_connections foreign key (conn_name) references connections(conn_name)
on update cascade on delete cascade
);
comment on table objects is $$Metadata for mapped objects$$;
/*
one remote object can have many local links in different schemas
*/
create unique index objects_unique_name on objects(
conn_name,
dbix_pglink.object_type_class(remote_object_type), --don't allow table and view with same name
remote_catalog,
create table queries (
object_id int4 not null,
action text check (action in ('S','I','U','D')), --SELECT/INSERT/UPDATE/DELETE
query_text text not null,
mod_stamp t_mod_stamp,
constraint pk_queries primary key (object_id, action),
constraint fk_queries_objects
foreign key (object_id)
references objects(object_id)
on update cascade on delete cascade
);
comment on table queries is $$SQL queries for retrieving or modification of remote table.$$;
create trigger queries_mod_stamp before insert or update
on queries
for each row execute procedure mod_stamp_trg();
create table query_params (
object_id int4 not null,
action text not null,
param_position int4 not null,
column_name text not null, -- shadow table column
local_type text not null,
remote_type text not null,
conv_to_remote text null,
constraint pk_query_params primary key (object_id, action, param_position),
constraint fk_query_params_queries
foreign key (object_id, action)
references queries(object_id, action)
on update cascade on delete cascade
);
comment on table query_params is
$$List of query parameters passed to query.
column_name is shadow table column name for modification query.
conv_to_remote is a name of Perl method of Adapter class associated with connection.$$;
create table columns (
object_id int4 not null,
updatable bool not null default true,
--
conv_to_local text null, --remote to PostgreSQL
conv_to_remote text null, --PostgreSQL to remote
--
constraint pk_columns primary key (object_id, column_name),
constraint ak_columns unique (object_id, column_position),
constraint fk_columns_objects
foreign key (object_id)
references objects(object_id)
on update cascade on delete cascade
);
comment on table columns is $$Column metadata for mapped objects$$;
--1:1 extension of objects table
create table routines (
object_id int4 not null,
local_sign_arg text not null, --part of local function signature (arg types in parenthesis)
remote_routine_name text not null, --without arguments, not quoted
returns_set boolean not null, --setof function (or else scalar)
rowtype text null, --unique local rowtype name for setof-functions
mod_stamp t_mod_stamp,
constraint pk_routines primary key (object_id),
constraint fk_routines_objects
foreign key (object_id)
references objects(object_id)
on update cascade on delete cascade
);
comment on table routines is $$Metadata for mapped routines$$;
create trigger routines_mod_stamp before insert or update
on routines
for each row execute procedure mod_stamp_trg();
create view v_routines
object_id int4 not null,
arg_position int4 not null,
arg_name text null,
remote_type text not null,
local_type text not null,
conv_to_remote text null,
constraint pk_routine_arguments primary key (object_id, arg_position),
constraint fk_routine_arguments_routines
foreign key (object_id)
references routines(object_id)
on update cascade on delete cascade
);
comment on table routine_arguments is $$Arguments of mapped routine$$;
----------------------------------------------role configuration
create table init_session (
conn_name text not null,
local_user text not null default '', --'' for all users
init_seq int4 not null,
init_query text not null,
mod_stamp t_mod_stamp,
constraint pk_init_session primary key (conn_name, local_user, init_seq),
constraint fk_init_session_connections foreign key (conn_name) references connections(conn_name)
on update cascade on delete cascade
);
comment on table init_session is
$$Initialization SQL scripts executed on remote database when connection established$$;
create trigger init_session_mod_stamp before insert or update
on init_session
for each row execute procedure mod_stamp_trg();
create table safe (
conn_name text not null,
local_user text not null, --'' for all users
safe_kind text not null check (safe_kind in ('command', 'regex', 'attribute')),
safe_text text not null,
safe_perm text not null check (safe_perm in ('allow','unallow','deny','undeny')),
mod_stamp t_mod_stamp,
constraint pk_safe primary key (conn_name, safe_kind, safe_text),
constraint fk_safe_connections foreign key (conn_name) references connections(conn_name)
on update cascade on delete cascade,
constraint ck_safe_perm check (safe_perm in ('allow','unallow') or safe_kind='regex')
);
comment on table safe is $$Configuration of Safe role (optional)$$;
create trigger safe_mod_stamp before insert or update
on safe
for each row execute procedure mod_stamp_trg();
( run in 0.567 second using v1.01-cache-2.11-cpan-49f99fa48dc )