| File packages/acs-kernel/sql/postgresql/postgresql.sql |
| File packages/acs-kernel/sql/postgresql/lob.sql |
[PG] create sequence lob_sequence |
[MSSQL] CREATE TABLE lob_sequence ( sequence int identity(1,1), dummy bit default 0 );
GO
CREATE PROC lob_sequence__nextval @ms_return_value int = 0 OUTPUT
as
INSERT INTO lob_sequence VALUES (default)
SET @ms_return_value = @@IDENTITY
RETURN @ms_return_value
|
|
[PG] create table lobs (
lob_id integer not null primary key,
refcount integer not null default 0
) |
[MSSQL] create table lobs ( lob_id integer not null primary key,
refcount integer not null default 0
)
GO
|
|
[PG] create function on_lobs_delete() returns opaque as '
begin
delete from lob_data where lob_id = old.lob_id;
return old;
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create trigger lobs_delete_trig before delete on lobs
for each row execute procedure on_lobs_delete() |
[MSSQL] /* Skipped */ |
|
[PG] create table lob_data (
lob_id integer not null references lobs,
segment integer not null,
byte_len integer not null,
data bytea not null,
primary key (lob_id, segment)
) |
[MSSQL] create table lob_data ( lob_id integer not null references lobs,
segment integer not null,
byte_len integer not null,
data image not null,
primary key (lob_id, segment)
)
|
|
[PG] create index lob_data_index on lob_data(lob_id) |
[MSSQL] create index lob_data_index on lob_data(lob_id) |
|
[PG] create function on_lob_ref() returns opaque as '
begin
if TG_OP = 'UPDATE' then
if new.lob = old.lob then
return new;
end if;
end if;
if TG_OP = 'INSERT' or TG_OP = 'UPDATE' then
if new.lob is not null then
insert into lobs select new.lob, 0
where 0 = (select count(*) from lobs where lob_id = new.lob);
update lobs set refcount = refcount + 1 where lob_id = new.lob;
end if;
end if;
if TG_OP <> 'INSERT' then
if old.lob is not null then
update lobs set refcount = refcount - 1 where lob_id = old.lob;
delete from lobs where lob_id = old.lob and refcount = 0;
end if;
end if;
if TG_OP = 'INSERT' or TG_OP = 'UPDATE' then return new;
else return old;
end if;
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create function empty_lob() returns integer as '
begin
return nextval('lob_sequence');
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create function lob_get_data(integer) returns text as '
declare
lob_id integer;
v_rec record;
v_data text default '';
begin
for v_rec in select data, segment from lob_data order by segment;
v_data := v_data + v_rec.data;
end if;
return v_data;
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create function lob_copy(integer, integer) returns integer as '
declare
from_id alias for $1;
to_id alias for $2;
begin
insert into lobs (lob_id,refcount) values (to_id,0);
insert into lob_data
select to_id as lob_id, segment, byte_len, data
from lob_data
where lob_id = from_id;
return null;
end;' language 'plpgsql' |
[MSSQL]
CREATE PROC lob_copy
@from_id integer,
@to_id integer,
@ms_return_value integer = 0 OUTPUT
AS
BEGIN
insert into lobs ( lob_id, refcount ) values ( @to_id , 0 )
insert into lob_data
select @to_id as lob_id, segment , byte_len , data
from lob_data
WHERE lob_id = @from_id
SET @ms_return_value = null
RETURN @ms_return_value
END -- stored proc
|
|
[PG] create function lob_length(integer) returns integer as '
declare
id alias for $1;
begin
return sum(byte_len) from lob_data where lob_id = id;
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
| [PG] |
[MSSQL] /* Skipped */ |
|
| File packages/acs-kernel/sql/postgresql/acs-logs-create.sql |
[PG] create sequence t_acs_log_id_seq |
[MSSQL] CREATE TABLE t_acs_log_id_seq ( sequence int identity(1,1), dummy bit default 0 );
GO
CREATE PROC acs_log_id_seq__nextval @ms_return_value int = 0 OUTPUT
as
INSERT INTO t_acs_log_id_seq VALUES (default)
SET @ms_return_value = @@IDENTITY
RETURN @ms_return_value
|
|
[PG] create view acs_log_id_seq as
select nextval('t_acs_log_id_seq') as nextval |
[MSSQL] /* Skipped */ |
|
[PG] create table acs_logs (
log_id integer
constraint acs_logs_pk
primary key,
log_date timestamp default now() not null,
log_level varchar(20) not null
constraint acs_logs_log_level_ck
check (log_level in ('notice', 'warn', 'error',
'debug')),
log_key varchar(100) not null,
message text not null
) |
[MSSQL] create table acs_logs ( log_id integer
constraint acs_logs_pk
primary key,
log_date datetime default getdate() not null,
log_level varchar(20) not null
constraint acs_logs_log_level_ck
check (log_level in ('notice', 'warn', 'error',
'debug')),
log_key varchar(100) not null,
message text not null
)
GO
|
|
[PG] create function acs_log__notice (varchar,varchar)
returns integer as '
declare
notice__log_key alias for $1;
notice__message alias for $2;
begin
insert into acs_logs
(log_id, log_level, log_key, message)
values
(acs_log_id_seq.nextval, 'notice', notice__log_key, notice__message);
return 0;
end;' language 'plpgsql' |
[MSSQL]
CREATE PROC acs_log__notice
@notice__log_key varchar(8000),
@notice__message varchar(8000),
@ms_return_value integer = 0 OUTPUT
AS
BEGIN
declare @retval int
exec acs_log_id_seq__nextval @ms_return_value = @retval OUTPUT
insert into acs_logs ( log_id, log_level, log_key, message ) values ( @retval , 'notice' , @notice__log_key , @notice__message )
RETURN 0
END -- stored proc
|
|
[PG] create function acs_log__warn (varchar,varchar)
returns integer as '
declare
warn__log_key alias for $1;
warn__message alias for $2;
begin
insert into acs_logs
(log_id, log_level, log_key, message)
values
(acs_log_id_seq.nextval, 'warn', warn__log_key, warn__message);
return 0;
end;' language 'plpgsql' |
[MSSQL]
CREATE PROC acs_log__warn
@warn__log_key varchar(8000),
@warn__message varchar(8000),
@ms_return_value integer = 0 OUTPUT
AS
BEGIN
declare @retval int
exec acs_log_id_seq__nextval @ms_return_value = @retval OUTPUT
insert into acs_logs ( log_id, log_level, log_key, message ) values ( @retval , 'warn' , @warn__log_key , @warn__message )
RETURN 0
END -- stored proc
|
|
[PG] create function acs_log__error (varchar,varchar)
returns integer as '
declare
error__log_key alias for $1;
error__message alias for $2;
begin
insert into acs_logs
(log_id, log_level, log_key, message)
values
(acs_log_id_seq.nextval, 'error', error__log_key, error__message);
return 0;
end;' language 'plpgsql' |
[MSSQL]
CREATE PROC acs_log__error
@error__log_key varchar(8000),
@error__message varchar(8000),
@ms_return_value integer = 0 OUTPUT
AS
BEGIN
declare @retval int
exec acs_log_id_seq__nextval @ms_return_value = @retval OUTPUT
insert into acs_logs ( log_id, log_level, log_key, message ) values ( @retval , 'error' , @error__log_key , @error__message )
RETURN 0
END -- stored proc
|
|
[PG] create function acs_log__debug (varchar,varchar)
returns integer as '
declare
debug__log_key alias for $1;
debug__message alias for $2;
begin
insert into acs_logs
(log_id, log_level, log_key, message)
values
(acs_log_id_seq.nextval, 'debug', debug__log_key, debug__message);
return 0;
end;' language 'plpgsql' |
[MSSQL]
CREATE PROC acs_log__debug
@debug__log_key varchar(8000),
@debug__message varchar(8000),
@ms_return_value integer = 0 OUTPUT
AS
BEGIN
declare @retval int
exec acs_log_id_seq__nextval @ms_return_value = @retval OUTPUT
insert into acs_logs ( log_id, log_level, log_key, message ) values ( @retval , 'debug' , @debug__log_key , @debug__message )
RETURN 0
END -- stored proc
|
|
| [PG] |
[MSSQL] /* Skipped */ |
|
| File packages/acs-kernel/sql/postgresql/acs-metadata-create.sql |
[PG] create table acs_object_types (
object_type varchar(100) not null
constraint acs_object_types_pk primary key,
supertype varchar(100) constraint acs_object_types_supertype_fk
references acs_object_types (object_type),
abstract_p boolean default 'f' not null,
pretty_name varchar(100) not null
constraint acs_obj_types_pretty_name_un
unique,
pretty_plural varchar(100) not null
constraint acs_obj_types_pretty_plural_un
unique,
table_name varchar(30) not null
constraint acs_object_types_tbl_name_un unique,
id_column varchar(30) not null,
package_name varchar(30) not null
constraint acs_object_types_pkg_name_un unique,
name_method varchar(30),
type_extension_table varchar(30),
dynamic_p boolean default 'f',
tree_sortkey varchar(4000)
) |
[MSSQL] create table acs_object_types ( object_type varchar(100) not null
constraint acs_object_types_pk primary key,
supertype varchar(100) constraint acs_object_types_supertype_fk
references acs_object_types (object_type),
abstract_p boolean default 'f' not null,
pretty_name varchar(100) not null
constraint acs_obj_types_pretty_name_un
unique,
pretty_plural varchar(100) not null
constraint acs_obj_types_pretty_plural_un
unique,
table_name varchar(30) not null
constraint acs_object_types_tbl_name_un unique,
id_column varchar(30) not null,
package_name varchar(30) not null
constraint acs_object_types_pkg_name_un unique,
name_method varchar(30),
type_extension_table varchar(30),
dynamic_p boolean default 'f',
tree_sortkey varchar(4000)
)
GO
|
|
[PG] create index acs_obj_types_supertype_idx on acs_object_types (supertype) |
[MSSQL] create index acs_obj_types_supertype_idx on acs_object_types (supertype) |
|
[PG] create index acs_obj_types_tree_skey_idx on acs_object_types (tree_sortkey) |
[MSSQL] create index acs_obj_types_tree_skey_idx on acs_object_types (tree_sortkey) |
|
[PG] create function acs_object_type_insert_tr () returns opaque as '
declare
v_parent_sk varchar;
max_key varchar;
begin
select max(tree_sortkey) into max_key
from acs_object_types
where supertype = new.supertype;
select coalesce(max(tree_sortkey),'') into v_parent_sk
from acs_object_types
where object_type = new.supertype;
new.tree_sortkey := v_parent_sk + '/' + tree_next_key(max_key);
return new;
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create trigger acs_object_type_insert_tr before insert
on acs_object_types for each row
execute procedure acs_object_type_insert_tr () |
[MSSQL] /* Skipped */ |
|
[PG] create function acs_object_type_update_tr () returns opaque as '
declare
v_parent_sk varchar;
max_key varchar;
v_rec record;
clr_keys_p boolean default 't';
begin
if new.object_type = old.object_type and
((new.supertype = old.supertype) or
(new.supertype is null and old.supertype is null)) then
return new;
end if;
for v_rec in select object_type
from acs_object_types
where tree_sortkey like new.tree_sortkey + '%'
order by tree_sortkey
LOOP
if clr_keys_p then
update acs_object_types set tree_sortkey = null
where tree_sortkey like new.tree_sortkey + '%';
clr_keys_p := 'f';
end if;
select max(tree_sortkey) into max_key
from acs_object_types
where supertype = (select supertype
from acs_object_types
where object_type = v_rec.object_type);
select coalesce(max(tree_sortkey),'') into v_parent_sk
from acs_object_types
where object_type = (select supertype
from acs_object_types
where object_type = v_rec.object_type);
update acs_object_types
set tree_sortkey = v_parent_sk + '/' + tree_next_key(max_key)
where object_type = v_rec.object_type;
end LOOP;
return new;
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create trigger acs_object_type_update_tr after update
on acs_object_types
for each row
execute procedure acs_object_type_update_tr () |
[MSSQL] /* Skipped */ |
|
[PG] create view acs_object_type_supertype_map
as select ot1.object_type, ot2.object_type as ancestor_type
from acs_object_types ot1,
acs_object_types ot2
where ot1.object_type <> ot2.object_type
and ot2.tree_sortkey <= ot1.tree_sortkey
and ot1.tree_sortkey like (ot2.tree_sortkey + '%') |
[MSSQL] create view acs_object_type_supertype_map as
select ot1.object_type , ot2.object_type as ancestor_type
from acs_object_types ot1,
acs_object_types ot2
WHERE ot1.object_type <> ot2.object_type and ot2.tree_sortkey <= ot1.tree_sortkey and ot1.tree_sortkey like ( ot2.tree_sortkey + '%' )
|
|
[PG] create table acs_object_type_tables (
object_type varchar(100) not null
constraint acs_obj_type_tbls_obj_type_fk
references acs_object_types (object_type),
table_name varchar(30) not null,
id_column varchar(30),
constraint acs_object_type_tables_pk
primary key (object_type, table_name)
) |
[MSSQL] create table acs_object_type_tables ( object_type varchar(100) not null
constraint acs_obj_type_tbls_obj_type_fk
references acs_object_types (object_type),
table_name varchar(30) not null,
id_column varchar(30),
constraint acs_object_type_tables_pk
primary key (object_type, table_name)
)
GO
|
|
[PG] create index acs_objtype_tbls_objtype_idx on acs_object_type_tables (object_type) |
[MSSQL] create index acs_objtype_tbls_objtype_idx on acs_object_type_tables (object_type) |
|
[PG] create table acs_datatypes (
datatype varchar(50) not null
constraint acs_datatypes_pk primary key,
max_n_values integer default 1
constraint acs_datatypes_max_n_ck
check (max_n_values > 0)
) |
[MSSQL] create table acs_datatypes ( datatype varchar(50) not null
constraint acs_datatypes_pk primary key,
max_n_values integer default 1
constraint acs_datatypes_max_n_ck
check (max_n_values > 0)
)
GO
|
|
[PG] create function inline_0 ()
returns integer as '
begin
insert into acs_datatypes
(datatype, max_n_values)
values
('string', null);
insert into acs_datatypes
(datatype, max_n_values)
values
('boolean', 1);
insert into acs_datatypes
(datatype, max_n_values)
values
('number', null);
insert into acs_datatypes
(datatype, max_n_values)
values
('integer', 1);
insert into acs_datatypes
(datatype, max_n_values)
values
('money', null);
insert into acs_datatypes
(datatype, max_n_values)
values
('date', null);
insert into acs_datatypes
(datatype, max_n_values)
values
('timestamp', null);
insert into acs_datatypes
(datatype, max_n_values)
values
('time_of_day', null);
insert into acs_datatypes
(datatype, max_n_values)
values
('enumeration', null);
return 0;
end;' language 'plpgsql' |
[MSSQL]
CREATE PROC inline_0
@ms_return_value integer = 0 OUTPUT
AS
BEGIN
insert into acs_datatypes ( datatype, max_n_values ) values ( 'string' , null )
insert into acs_datatypes ( datatype, max_n_values ) values ( 'boolean' , 1 )
insert into acs_datatypes ( datatype, max_n_values ) values ( 'number' , null )
insert into acs_datatypes ( datatype, max_n_values ) values ( 'integer' , 1 )
insert into acs_datatypes ( datatype, max_n_values ) values ( 'money' , null )
insert into acs_datatypes ( datatype, max_n_values ) values ( 'date' , null )
insert into acs_datatypes ( datatype, max_n_values ) values ( 'timestamp' , null )
insert into acs_datatypes ( datatype, max_n_values ) values ( 'time_of_day' , null )
insert into acs_datatypes ( datatype, max_n_values ) values ( 'enumeration' , null )
RETURN 0
END -- stored proc
|
|
[PG] select inline_0 () |
[MSSQL] dbo.inline_0 |
|
[PG] drop function inline_0 () |
[MSSQL] drop proc inline_0 |
|
[PG] create sequence t_acs_attribute_id_seq |
[MSSQL] CREATE TABLE t_acs_attribute_id_seq ( sequence int identity(1,1), dummy bit default 0 );
GO
CREATE PROC acs_attribute_id_seq__nextval @ms_return_value int = 0 OUTPUT
as
INSERT INTO t_acs_attribute_id_seq VALUES (default)
SET @ms_return_value = @@IDENTITY
RETURN @ms_return_value
|
|
[PG] create view acs_attribute_id_seq as
select nextval('t_acs_attribute_id_seq') as nextval |
[MSSQL] /* Skipped */ |
|
[PG] create table acs_attributes (
attribute_id integer not null
constraint acs_attributes_pk
primary key,
object_type varchar(100) not null
constraint acs_attributes_object_type_fk
references acs_object_types (object_type),
table_name varchar(30),
constraint acs_attrs_obj_type_tbl_name_fk
foreign key (object_type, table_name)
references acs_object_type_tables,
attribute_name varchar(100) not null,
pretty_name varchar(100) not null,
pretty_plural varchar(100),
sort_order integer not null,
datatype varchar(50) not null
constraint acs_attributes_datatype_fk
references acs_datatypes (datatype),
default_value text,
min_n_values integer default 1 not null
constraint acs_attributes_min_n_ck
check (min_n_values >= 0),
max_n_values integer default 1 not null
constraint acs_attributes_max_n_ck
check (max_n_values >= 0),
storage varchar(13) default 'type_specific'
constraint acs_attributes_storage_ck
check (storage in ('type_specific',
'generic')),
static_p boolean default 'f',
column_name varchar(30),
constraint acs_attributes_attr_name_un
unique (attribute_name, object_type),
constraint acs_attributes_pretty_name_un
unique (pretty_name, object_type),
constraint acs_attributes_sort_order_un
unique (attribute_id, sort_order),
constraint acs_attributes_n_values_ck
check (min_n_values <= max_n_values)
) |
[MSSQL] create table acs_attributes ( attribute_id integer not null
constraint acs_attributes_pk
primary key,
object_type varchar(100) not null
constraint acs_attributes_object_type_fk
references acs_object_types (object_type),
table_name varchar(30),
constraint acs_attrs_obj_type_tbl_name_fk
foreign key (object_type, table_name)
references acs_object_type_tables,
attribute_name varchar(100) not null,
pretty_name varchar(100) not null,
pretty_plural varchar(100),
sort_order integer not null,
datatype varchar(50) not null
constraint acs_attributes_datatype_fk
references acs_datatypes (datatype),
default_value text,
min_n_values integer default 1 not null
constraint acs_attributes_min_n_ck
check (min_n_values >= 0),
max_n_values integer default 1 not null
constraint acs_attributes_max_n_ck
check (max_n_values >= 0),
storage varchar(13) default 'type_specific'
constraint acs_attributes_storage_ck
check (storage in ('type_specific',
'generic')),
static_p boolean default 'f',
column_name varchar(30),
constraint acs_attributes_attr_name_un
unique (attribute_name, object_type),
constraint acs_attributes_pretty_name_un
unique (pretty_name, object_type),
constraint acs_attributes_sort_order_un
unique (attribute_id, sort_order),
constraint acs_attributes_n_values_ck
check (min_n_values <= max_n_values)
)
GO
|
|
[PG] create index acs_attrs_obj_type_idx on acs_attributes (object_type) |
[MSSQL] create index acs_attrs_obj_type_idx on acs_attributes (object_type)
GO
create unique index acs_attrs_obj_type_attr_name_idx on acs_attributes (object_type, attribute_name)
|
|
[PG] create index acs_attrs_tbl_name_idx on acs_attributes (table_name) |
[MSSQL] create index acs_attrs_tbl_name_idx on acs_attributes (table_name) |
|
[PG] create index acs_attrs_datatype_idx on acs_attributes (datatype) |
[MSSQL] create index acs_attrs_datatype_idx on acs_attributes (datatype) |
|
[PG] create table acs_enum_values (
attribute_id integer not null
constraint asc_enum_values_attr_id_fk
references acs_attributes (attribute_id),
enum_value varchar(1000),
pretty_name varchar(100) not null,
sort_order integer not null,
constraint acs_enum_values_pk
primary key (attribute_id, enum_value),
constraint acs_enum_values_pretty_name_un
unique (attribute_id, pretty_name),
constraint acs_enum_values_sort_order_un
unique (attribute_id, sort_order)
) |
[MSSQL] create table acs_enum_values ( attribute_id integer not null
constraint asc_enum_values_attr_id_fk
references acs_attributes (attribute_id),
enum_value varchar(1000),
pretty_name varchar(100) not null,
sort_order integer not null,
constraint acs_enum_values_pk
primary key (attribute_id, enum_value),
constraint acs_enum_values_pretty_name_un
unique (attribute_id, pretty_name),
constraint acs_enum_values_sort_order_un
unique (attribute_id, sort_order)
)
GO
|
|
[PG] create index acs_enum_values_attr_id_idx on acs_enum_values (attribute_id) |
[MSSQL] create index acs_enum_values_attr_id_idx on acs_enum_values (attribute_id) |
|
[PG] create table acs_attribute_descriptions (
object_type varchar(100) not null constraint acs_attr_descs_obj_type_fk
references acs_object_types (object_type),
attribute_name varchar(100) not null,
constraint acs_attr_descs_ob_tp_at_na_fk
foreign key (object_type, attribute_name)
references acs_attributes (object_type, attribute_name),
description_key varchar(100),
constraint acs_attribute_descriptions_pk
primary key (object_type, attribute_name, description_key),
description text not null
) |
[MSSQL] create table acs_attribute_descriptions ( object_type varchar(100) not null constraint acs_attr_descs_obj_type_fk
references acs_object_types (object_type),
attribute_name varchar(100) not null,
constraint acs_attr_descs_ob_tp_at_na_fk
foreign key (object_type, attribute_name)
references acs_attributes (object_type, attribute_name),
description_key varchar(100),
constraint acs_attribute_descriptions_pk
primary key (object_type, attribute_name, description_key),
description text not null
)
GO
|
|
[PG] create index acs_attr_desc_obj_type_idx on acs_attribute_descriptions (object_type) |
[MSSQL] create index acs_attr_desc_obj_type_idx on acs_attribute_descriptions (object_type) |
|
[PG] create index acs_attr_desc_attr_name_idx on acs_attribute_descriptions (attribute_name) |
[MSSQL] create index acs_attr_desc_attr_name_idx on acs_attribute_descriptions (attribute_name) |
|
[PG] create view acs_object_type_attributes as
select all_types.object_type, all_types.ancestor_type,
attr.attribute_id, attr.table_name, attr.attribute_name,
attr.pretty_name, attr.pretty_plural, attr.sort_order,
attr.datatype, attr.default_value, attr.min_n_values,
attr.max_n_values, attr.storage, attr.static_p, attr.column_name
from acs_attributes attr,
(select map.object_type, map.ancestor_type
from acs_object_type_supertype_map map, acs_object_types t
where map.object_type=t.object_type
UNION ALL
select t.object_type, t.object_type as ancestor_type
from acs_object_types t) all_types
where attr.object_type = all_types.ancestor_type |
[MSSQL] create view acs_object_type_attributes as
select all_types.object_type , all_types.ancestor_type , attr.attribute_id , attr.table_name , attr.attribute_name , attr.pretty_name , attr.pretty_plural , attr.sort_order , attr.datatype , attr.default_value , attr.min_n_values , attr.max_n_values , attr.storage , attr.static_p , attr.column_name
from acs_attributes attr,
(
select map.object_type , map.ancestor_type
from acs_object_type_supertype_map map,
acs_object_types t
WHERE map.object_type = t.object_type
UNION ALL
select t.object_type , t.object_type as ancestor_type
from acs_object_types t
) all_types
WHERE attr.object_type = all_types.ancestor_type
|
|
[PG] create function acs_object_type__create_type (varchar,varchar,varchar,varchar,varchar,varchar,varchar,boolean,varchar,varchar)
returns integer as '
declare
create_type__object_type alias for $1;
create_type__pretty_name alias for $2;
create_type__pretty_plural alias for $3;
create_type__supertype alias for $4;
create_type__table_name alias for $5;
create_type__id_column alias for $6;
create_type__package_name alias for $7;
create_type__abstract_p alias for $8;
create_type__type_extension_table alias for $9;
create_type__name_method alias for $10;
v_package_name acs_object_types.package_name%TYPE;
v_name_method varchar;
v_idx integer;
begin
v_idx := position('.' in create_type__name_method);
if v_idx <> 0 then
v_name_method := substr(create_type__name_method,1,v_idx - 1) +
'__' + substr(create_type__name_method, v_idx + 1);
else
v_name_method := create_type__name_method;
end if;
if create_type__package_name is null or create_type__package_name = '' then
v_package_name := create_type__object_type;
else
v_package_name := create_type__package_name;
end if;
insert into acs_object_types
(object_type, pretty_name, pretty_plural, supertype, table_name,
id_column, abstract_p, type_extension_table, package_name,
name_method)
values
(create_type__object_type, create_type__pretty_name,
create_type__pretty_plural, create_type__supertype,
create_type__table_name, create_type__id_column,
create_type__abstract_p, create_type__type_extension_table,
v_package_name, v_name_method);
return 0;
end;' language 'plpgsql' |
[MSSQL]
CREATE PROC acs_object_type__create_type
@create_type__object_type varchar(8000),
@create_type__pretty_name varchar(8000),
@create_type__pretty_plural varchar(8000),
@create_type__supertype varchar(8000),
@create_type__table_name varchar(8000),
@create_type__id_column varchar(8000),
@create_type__package_name varchar(8000),
@create_type__abstract_p boolean,
@create_type__type_extension_table varchar(8000),
@create_type__name_method varchar(8000),
@ms_return_value integer = 0 OUTPUT
AS
BEGIN
declare @v_package_name varchar(8000)
declare @v_idx integer
declare @v_name_method varchar(8000)
SET @v_idx = charindex ( '.' , @create_type__name_method )
IF @v_idx <> 0 BEGIN
SET @v_name_method = substring ( @create_type__name_method , 1 , @v_idx - 1 ) + '__' + substring ( @create_type__name_method , @v_idx + 1, datalength(@create_type__name_method) )
END
ELSE BEGIN
SET @v_name_method = @create_type__name_method
END --IF
IF @create_type__package_name is null or @create_type__package_name = '' BEGIN
SET @v_package_name = @create_type__object_type
END
ELSE BEGIN
SET @v_package_name = @create_type__package_name
END --IF
insert into acs_object_types ( object_type, pretty_name, pretty_plural, supertype, table_name, id_column, abstract_p, type_extension_table, package_name, name_method ) values ( @create_type__object_type , @create_type__pretty_name , @create_type__pretty_plural , @create_type__supertype , @create_type__table_name , @create_type__id_column , @create_type__abstract_p , @create_type__type_extension_table , @v_package_name , @v_name_method )
RETURN 0
END -- stored proc
|
|
[PG] create function acs_object_type__drop_type (varchar,boolean)
returns integer as '
declare
drop_type__object_type alias for $1;
drop_type__cascade_p alias for $2;
row record;
begin
for row in select attribute_name
from acs_attributes
where object_type = drop_type__object_type
loop
PERFORM acs_attribute__drop_attribute (drop_type__object_type,
row.attribute_name);
end loop;
delete from acs_attributes
where object_type = drop_type__object_type;
delete from acs_object_types
where object_type = drop_type__object_type;
return 0;
end;' language 'plpgsql' |
[MSSQL]
CREATE PROC acs_object_type__drop_type
@drop_type__object_type varchar(8000),
@drop_type__cascade_p boolean,
@ms_return_value integer = 0 OUTPUT
AS
BEGIN
declare @row__attribute_name varchar(8000)
DECLARE cursor_1 CURSOR FOR
select attribute_name
from acs_attributes
WHERE object_type = @drop_type__object_type
OPEN cursor_1
FETCH NEXT FROM cursor_1 INTO @row__attribute_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC dbo.acs_attribute__drop_attribute @drop_type__object_type ,
@row__attribute_name
FETCH NEXT FROM cursor_1 INTO @row__attribute_name
END --while
CLOSE cursor_1
DEALLOCATE cursor_1
delete from acs_attributes WHERE object_type = @drop_type__object_type
delete from acs_object_types WHERE object_type = @drop_type__object_type
RETURN 0
END -- stored proc
|
|
[PG] create function acs_object_type__pretty_name (varchar)
returns varchar as '
declare
pretty_name__object_type alias for $1;
v_pretty_name acs_object_types.pretty_name%TYPE;
begin
select t.pretty_name into v_pretty_name
from acs_object_types t
where t.object_type = pretty_name__object_type;
return v_pretty_name;
end;' language 'plpgsql' |
[MSSQL]
CREATE FUNCTION acs_object_type__pretty_name(@pretty_name__object_type varchar(8000))
RETURNS varchar
AS
BEGIN
declare @v_pretty_name varchar(8000)
select @v_pretty_name = t.pretty_name
from acs_object_types t
WHERE t.object_type = @pretty_name__object_type
RETURN CONVERT (varchar, @v_pretty_name )
RETURN NULL -- placeholder required by tsql
END -- function
|
|
[PG] create function acs_object_type__is_subtype_p (varchar,varchar)
returns boolean as '
declare
is_subtype_p__object_type_1 alias for $1;
is_subtype_p__object_type_2 alias for $2;
v_result integer;
begin
select count(*) into v_result
where exists (select 1
from acs_object_types t
where t.object_type = is_subtype_p__object_type_2
and tree_sortkey like
(select tree_sortkey + '%'
from acs_object_types
where object_type = is_subtype_p__object_type_1 ));
if v_result > 0 then
return 't';
end if;
return 'f';
end;' language 'plpgsql' |
[MSSQL] CREATE FUNCTION acs_object_type__is_subtype_p(@is_subtype_p__object_type_1 varchar(8000),
@is_subtype_p__object_type_2 varchar(8000))
RETURNS boolean
AS
BEGIN
declare @v_result integer
IF exists (
select 1
from acs_object_types t
WHERE t.object_type = @is_subtype_p__object_type_2 and tree_sortkey like (
select tree_sortkey + '%'
from acs_object_types
WHERE object_type = @is_subtype_p__object_type_1
)
)
BEGIN
RETURN 't'
END --IF
RETURN 'f'
END -- function
|
|
[PG] create function acs_attribute__create_attribute (varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,integer,integer,integer,varchar,boolean)
returns integer as '
declare
create_attribute__object_type alias for $1;
create_attribute__attribute_name alias for $2;
create_attribute__datatype alias for $3;
create_attribute__pretty_name alias for $4;
create_attribute__pretty_plural alias for $5;
create_attribute__table_name alias for $6;
create_attribute__column_name alias for $7;
create_attribute__default_value alias for $8;
create_attribute__min_n_values alias for $9;
create_attribute__max_n_values alias for $10;
create_attribute__sort_order alias for $11;
create_attribute__storage alias for $12;
create_attribute__static_p alias for $13;
v_sort_order acs_attributes.sort_order%TYPE;
v_attribute_id acs_attributes.attribute_id%TYPE;
begin
if create_attribute__sort_order is null then
select coalesce(max(sort_order), 1) into v_sort_order
from acs_attributes
where object_type = create_attribute__object_type
and attribute_name = create_attribute__attribute_name;
else
v_sort_order := create_attribute__sort_order;
end if;
select acs_attribute_id_seq.nextval into v_attribute_id;
insert into acs_attributes
(attribute_id, object_type, table_name, column_name, attribute_name,
pretty_name, pretty_plural, sort_order, datatype, default_value,
min_n_values, max_n_values, storage, static_p)
values
(v_attribute_id, create_attribute__object_type,
create_attribute__table_name, create_attribute__column_name,
create_attribute__attribute_name, create_attribute__pretty_name,
create_attribute__pretty_plural, v_sort_order,
create_attribute__datatype, create_attribute__default_value,
create_attribute__min_n_values, create_attribute__max_n_values,
create_attribute__storage, create_attribute__static_p);
return v_attribute_id;
end;' language 'plpgsql' |
[MSSQL]
CREATE PROC acs_attribute__create_attribute
@create_attribute__object_type varchar(8000),
@create_attribute__attribute_name varchar(8000),
@create_attribute__datatype varchar(8000),
@create_attribute__pretty_name varchar(8000),
@create_attribute__pretty_plural varchar(8000),
@create_attribute__table_name varchar(8000),
@create_attribute__column_name varchar(8000),
@create_attribute__default_value varchar(8000),
@create_attribute__min_n_values integer,
@create_attribute__max_n_values integer,
@create_attribute__sort_order integer,
@create_attribute__storage varchar(8000),
@create_attribute__static_p boolean,
@ms_return_value integer = 0 OUTPUT
AS
BEGIN
declare @retval int
declare @v_sort_order varchar(8000)
declare @v_attribute_id int
IF @create_attribute__sort_order is null BEGIN
select @v_sort_order = coalesce ( max ( sort_order ) ,
1 )
from acs_attributes
WHERE object_type = @create_attribute__object_type and attribute_name = @create_attribute__attribute_name
END
ELSE BEGIN
SET @v_sort_order = @create_attribute__sort_order
END --IF
exec acs_attribute_id_seq__nextval @ms_return_value = @retval OUTPUT
select @v_attribute_id = @retval
insert into acs_attributes ( attribute_id, object_type, table_name, column_name, attribute_name, pretty_name, pretty_plural, sort_order, datatype, default_value, min_n_values, max_n_values, storage, static_p ) values ( @v_attribute_id , @create_attribute__object_type , @create_attribute__table_name , @create_attribute__column_name , @create_attribute__attribute_name , @create_attribute__pretty_name , @create_attribute__pretty_plural , @v_sort_order , @create_attribute__datatype , @create_attribute__default_value , @create_attribute__min_n_values , @create_attribute__max_n_values , @create_attribute__storage , @create_attribute__static_p )
SET @ms_return_value = @v_attribute_id
RETURN @ms_return_value
END -- stored proc
|
|
[PG] create function acs_attribute__drop_attribute (varchar,varchar)
returns integer as '
declare
drop_attribute__object_type alias for $1;
drop_attribute__attribute_name alias for $2;
begin
delete from acs_enum_values
where attribute_id in (select a.attribute_id
from acs_attributes a
where a.object_type = drop_attribute__object_type
and a.attribute_name = drop_attribute__attribute_name);
delete from acs_attributes
where object_type = drop_attribute__object_type
and attribute_name = drop_attribute__attribute_name;
return 0;
end;' language 'plpgsql' |
[MSSQL]
CREATE PROC acs_attribute__drop_attribute
@drop_attribute__object_type varchar(8000),
@drop_attribute__attribute_name varchar(8000),
@ms_return_value integer = 0 OUTPUT
AS
BEGIN
delete from acs_enum_values WHERE attribute_id in (
select a.attribute_id
from acs_attributes a
WHERE a.object_type = @drop_attribute__object_type and a.attribute_name = @drop_attribute__attribute_name
)
delete from acs_attributes WHERE object_type = @drop_attribute__object_type and attribute_name = @drop_attribute__attribute_name
RETURN 0
END -- stored proc
|
|
[PG] create function acs_attribute__add_description (varchar,varchar,varchar,text)
returns integer as '
declare
add_description__object_type alias for $1;
add_description__attribute_name alias for $2;
add_description__description_key alias for $3;
add_description__description alias for $4;
begin
insert into acs_attribute_descriptions
(object_type, attribute_name, description_key, description)
values
(add_description__object_type, add_description__attribute_name,
add_description__description_key, add_description__description);
return 0;
end;' language 'plpgsql' |
[MSSQL]
CREATE PROC acs_attribute__add_description
@add_description__object_type varchar(8000),
@add_description__attribute_name varchar(8000),
@add_description__description_key varchar(8000),
@add_description__description text,
@ms_return_value integer = 0 OUTPUT
AS
BEGIN
insert into acs_attribute_descriptions ( object_type, attribute_name, description_key, description ) values ( @add_description__object_type , @add_description__attribute_name , @add_description__description_key , @add_description__description )
RETURN 0
END -- stored proc
|
|
[PG] create function acs_attribute__drop_description (varchar,varchar,varchar)
returns integer as '
declare
drop_description__object_type alias for $1;
drop_description__attribute_name alias for $2;
drop_description__description_key alias for $3;
begin
delete from acs_attribute_descriptions
where object_type = drop_description__object_type
and attribute_name = drop_description__attribute_name
and description_key = drop_description__description_key;
return 0;
end;' language 'plpgsql' |
[MSSQL]
CREATE PROC acs_attribute__drop_description
@drop_description__object_type varchar(8000),
@drop_description__attribute_name varchar(8000),
@drop_description__description_key varchar(8000),
@ms_return_value integer = 0 OUTPUT
AS
BEGIN
delete from acs_attribute_descriptions WHERE object_type = @drop_description__object_type and attribute_name = @drop_description__attribute_name and description_key = @drop_description__description_key
RETURN 0
END -- stored proc
|
|
| [PG] |
[MSSQL] /* Skipped */ |
|
| File packages/acs-kernel/sql/postgresql/acs-objects-create.sql |
[PG] create function inline_0 ()
returns integer as '
declare
attr_id acs_attributes.attribute_id%TYPE;
begin
PERFORM acs_object_type__create_type (
'acs_object',
'Object',
'Objects',
null,
'acs_objects',
'object_id',
'acs_object',
'f',
null,
'acs_object.default_name'
);
attr_id := acs_attribute__create_attribute (
'acs_object',
'object_type',
'string',
'Object Type',
'Object Types',
null,
null,
null,
1,
1,
null,
'type_specific',
'f'
);
attr_id := acs_attribute__create_attribute (
'acs_object',
'creation_date',
'date',
'Created Date',
null,
null,
null,
null,
1,
1,
null,
'type_specific',
'f'
);
attr_id := acs_attribute__create_attribute (
'acs_object',
'creation_ip',
'string',
'Creation IP Address',
null,
null,
null,
null,
1,
1,
null,
'type_specific',
'f'
);
attr_id := acs_attribute__create_attribute (
'acs_object',
'last_modified',
'date',
'Last Modified On',
null,
null,
null,
null,
1,
1,
null,
'type_specific',
'f'
);
attr_id := acs_attribute__create_attribute (
'acs_object',
'modifying_ip',
'string',
'Modifying IP Address',
null,
null,
null,
null,
1,
1,
null,
'type_specific',
'f'
);
attr_id := acs_attribute__create_attribute (
'acs_object',
'creation_user',
'integer',
'Creation user',
'Creation users',
null,
null,
null,
0,
1,
null,
'type_specific',
'f'
);
attr_id := acs_attribute__create_attribute (
'acs_object',
'context_id',
'integer',
'Context ID',
'Context IDs',
null,
null,
null,
0,
1,
null,
'type_specific',
'f'
);
return 0;
end;' language 'plpgsql' |
[MSSQL]
CREATE PROC inline_0
@ms_return_value integer = 0 OUTPUT
AS
BEGIN
declare @attr_id int
EXEC dbo.acs_object_type__create_type 'acs_object' ,
'Object' ,
'Objects' ,
null ,
'acs_objects' ,
'object_id' ,
'acs_object' ,
'f' ,
null ,
'acs_object.default_name'
EXEC dbo.acs_attribute__create_attribute 'acs_object' ,
'object_type' ,
'string' ,
'Object Type' ,
'Object Types' ,
null ,
null ,
null ,
1 ,
1 ,
null ,
'type_specific' ,
'f' , @ms_return_value = @attr_id OUTPUT
EXEC dbo.acs_attribute__create_attribute 'acs_object' ,
'creation_date' ,
'date' ,
'Created Date' ,
null ,
null ,
null ,
null ,
1 ,
1 ,
null ,
'type_specific' ,
'f' , @ms_return_value = @attr_id OUTPUT
EXEC dbo.acs_attribute__create_attribute 'acs_object' ,
'creation_ip' ,
'string' ,
'Creation IP Address' ,
null ,
null ,
null ,
null ,
1 ,
1 ,
null ,
'type_specific' ,
'f' , @ms_return_value = @attr_id OUTPUT
EXEC dbo.acs_attribute__create_attribute 'acs_object' ,
'last_modified' ,
'date' ,
'Last Modified On' ,
null ,
null ,
null ,
null ,
1 ,
1 ,
null ,
'type_specific' ,
'f' , @ms_return_value = @attr_id OUTPUT
EXEC dbo.acs_attribute__create_attribute 'acs_object' ,
'modifying_ip' ,
'string' ,
'Modifying IP Address' ,
null ,
null ,
null ,
null ,
1 ,
1 ,
null ,
'type_specific' ,
'f' , @ms_return_value = @attr_id OUTPUT
EXEC dbo.acs_attribute__create_attribute 'acs_object' ,
'creation_user' ,
'integer' ,
'Creation user' ,
'Creation users' ,
null ,
null ,
null ,
0 ,
1 ,
null ,
'type_specific' ,
'f' , @ms_return_value = @attr_id OUTPUT
EXEC dbo.acs_attribute__create_attribute 'acs_object' ,
'context_id' ,
'integer' ,
'Context ID' ,
'Context IDs' ,
null ,
null ,
null ,
0 ,
1 ,
null ,
'type_specific' ,
'f' , @ms_return_value = @attr_id OUTPUT
RETURN 0
END -- stored proc
|
|
[PG] select inline_0 () |
[MSSQL] dbo.inline_0 |
|
[PG] drop function inline_0 () |
[MSSQL] drop proc inline_0 |
|
[PG] create sequence t_acs_object_id_seq |
[MSSQL] CREATE TABLE t_acs_object_id_seq ( sequence int identity(1,1), dummy bit default 0 );
GO
CREATE PROC acs_object_id_seq__nextval @ms_return_value int = 0 OUTPUT
as
INSERT INTO t_acs_object_id_seq VALUES (default)
SET @ms_return_value = @@IDENTITY
RETURN @ms_return_value
|
|
[PG] create view acs_object_id_seq as
select nextval('t_acs_object_id_seq') as nextval |
[MSSQL] /* Skipped */ |
|
[PG] create table acs_objects (
object_id integer not null
constraint acs_objects_pk primary key,
object_type varchar(100) not null
constraint acs_objects_object_type_fk
references acs_object_types (object_type),
context_id integer constraint acs_objects_context_id_fk
references acs_objects(object_id),
security_inherit_p boolean default 't' not null,
creation_user integer,
creation_date timestamp default now() not null,
creation_ip varchar(50),
last_modified timestamp default now() not null,
modifying_user integer,
modifying_ip varchar(50),
tree_sortkey varchar(4000),
constraint acs_objects_context_object_un
unique (context_id, object_id)
) |
[MSSQL] create table acs_objects ( object_id integer not null
constraint acs_objects_pk primary key,
object_type varchar(100) not null
constraint acs_objects_object_type_fk
references acs_object_types (object_type),
context_id integer constraint acs_objects_context_id_fk
references acs_objects(object_id),
security_inherit_p boolean default 't' not null,
creation_user integer,
creation_date datetime default getdate() not null,
creation_ip varchar(50),
last_modified datetime default getdate() not null,
modifying_user integer,
modifying_ip varchar(50),
tree_sortkey varchar(4000),
constraint acs_objects_context_object_un
unique (context_id, object_id)
)
GO
|
|
[PG] create index acs_objects_context_object_idx on
acs_objects (context_id, object_id) |
[MSSQL] create index acs_objects_context_object_idx on
acs_objects (context_id, object_id) |
|
[PG] create index acs_objs_tree_skey_idx on acs_objects (tree_sortkey) |
[MSSQL] create index acs_objs_tree_skey_idx on acs_objects (tree_sortkey) |
|
[PG] create index acs_objects_creation_user_idx on acs_objects (creation_user) |
[MSSQL] create index acs_objects_creation_user_idx on acs_objects (creation_user) |
|
[PG] create index acs_objects_modify_user_idx on acs_objects (modifying_user) |
[MSSQL] create index acs_objects_modify_user_idx on acs_objects (modifying_user) |
|
[PG] create index acs_objects_object_type_idx on acs_objects (object_type) |
[MSSQL] create index acs_objects_object_type_idx on acs_objects (object_type) |
|
[PG] create function acs_objects_mod_ip_insert_tr () returns opaque as '
begin
new.modifying_ip := new.creation_ip;
return new;
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create trigger acs_objects_mod_ip_insert_tr before insert on acs_objects
for each row execute procedure acs_objects_mod_ip_insert_tr () |
[MSSQL] /* Skipped */ |
|
[PG] create function acs_objects_last_mod_update_tr () returns opaque as '
begin
new.last_modified := now();
return new;
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create trigger acs_objects_last_mod_update_tr before update on acs_objects
for each row execute procedure acs_objects_last_mod_update_tr () |
[MSSQL] /* Skipped */ |
|
[PG] create function acs_objects_insert_tr () returns opaque as '
declare
v_parent_sk varchar;
max_key varchar;
begin
if new.context_id is null then
select max(tree_sortkey) into max_key
from acs_objects
where context_id is null;
v_parent_sk := '';
else
select max(tree_sortkey) into max_key
from acs_objects
where context_id = new.context_id;
select coalesce(max(tree_sortkey),'') into v_parent_sk
from acs_objects
where object_id = new.context_id;
end if;
new.tree_sortkey := v_parent_sk + '/' + tree_next_key(max_key);
return new;
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create trigger acs_objects_insert_tr before insert
on acs_objects for each row
execute procedure acs_objects_insert_tr () |
[MSSQL] /* Skipped */ |
|
[PG] create function acs_objects_update_tr () returns opaque as '
declare
v_parent_sk varchar;
max_key varchar;
ctx_id integer;
v_rec record;
clr_keys_p boolean default 't';
begin
if new.object_id = old.object_id and
((new.context_id = old.context_id) or
(new.context_id is null and old.context_id is null)) then
return new;
end if;
for v_rec in select object_id
from acs_objects
where tree_sortkey like new.tree_sortkey + '%'
order by tree_sortkey
LOOP
if clr_keys_p then
update acs_objects set tree_sortkey = null
where tree_sortkey like new.tree_sortkey + '%';
clr_keys_p := 'f';
end if;
select context_id into ctx_id
from acs_objects
where object_id = v_rec.object_id;
if ctx_id is null then
select max(tree_sortkey) into max_key
from acs_objects
where context_id is null;
v_parent_sk := '';
else
select max(tree_sortkey) into max_key
from acs_objects
where context_id = ctx_id;
select coalesce(max(tree_sortkey),'') into v_parent_sk
from acs_objects
where object_id = ctx_id;
end if;
update acs_objects
set tree_sortkey = v_parent_sk + '/' + tree_next_key(max_key)
where object_id = v_rec.object_id;
end LOOP;
return new;
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create trigger acs_objects_update_tr after update
on acs_objects
for each row
execute procedure acs_objects_update_tr () |
[MSSQL] /* Skipped */ |
|
[PG] create table acs_object_context_index (
object_id integer not null
constraint acs_obj_context_idx_obj_id_fk
references acs_objects(object_id),
ancestor_id integer not null
constraint acs_obj_context_idx_anc_id_fk
references acs_objects(object_id),
n_generations integer not null
constraint acs_obj_context_idx_n_gen_ck
check (n_generations >= 0),
constraint acs_object_context_index_pk
primary key (object_id, ancestor_id)
) |
[MSSQL] create table acs_object_context_index ( object_id integer not null
constraint acs_obj_context_idx_obj_id_fk
references acs_objects(object_id),
ancestor_id integer not null
constraint acs_obj_context_idx_anc_id_fk
references acs_objects(object_id),
n_generations integer not null
constraint acs_obj_context_idx_n_gen_ck
check (n_generations >= 0),
constraint acs_object_context_index_pk
primary key (object_id, ancestor_id)
)
GO
|
|
[PG] create index acs_obj_ctx_idx_ancestor_idx on acs_object_context_index (ancestor_id) |
[MSSQL] create index acs_obj_ctx_idx_ancestor_idx on acs_object_context_index (ancestor_id) |
|
[PG] create view acs_object_paths
as select object_id, ancestor_id, n_generations
from acs_object_context_index |
[MSSQL] create view acs_object_paths as
select object_id , ancestor_id , n_generations
from acs_object_context_index
|
|
[PG] create view acs_object_contexts
as select object_id, ancestor_id, n_generations
from acs_object_context_index
where object_id != ancestor_id |
[MSSQL] create view acs_object_contexts as
select object_id , ancestor_id , n_generations
from acs_object_context_index
WHERE object_id != ancestor_id
|
|
[PG] create function acs_objects_context_id_in_tr () returns opaque as '
begin
insert into acs_object_context_index
(object_id, ancestor_id, n_generations)
values
(new.object_id, new.object_id, 0);
if new.context_id is not null and new.security_inherit_p = 't' then
insert into acs_object_context_index
(object_id, ancestor_id, n_generations)
select
new.object_id as object_id, ancestor_id,
n_generations + 1 as n_generations
from acs_object_context_index
where object_id = new.context_id;
else if new.object_id != 0 then
insert into acs_object_context_index
(object_id, ancestor_id, n_generations)
values
(new.object_id, 0, 1);
end if; end if;
return new;
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create trigger acs_objects_context_id_in_tr after insert on acs_objects
for each row execute procedure acs_objects_context_id_in_tr () |
[MSSQL] /* Skipped */ |
|
[PG] create function acs_objects_context_id_up_tr () returns opaque as '
declare
pair record;
begin
if new.object_id = old.object_id and
new.context_id = old.context_id and
new.security_inherit_p = old.security_inherit_p then
return new;
end if;
delete from acs_object_context_index
where object_id in (select object_id
from acs_object_contexts
where ancestor_id = old.object_id)
and ancestor_id in (select ancestor_id
from acs_object_contexts
where object_id = old.object_id);
delete from acs_object_context_index
where object_id = old.object_id;
insert into acs_object_context_index
(object_id, ancestor_id, n_generations)
values
(new.object_id, new.object_id, 0);
if new.context_id is not null and new.security_inherit_p = 't' then
for pair in select *
from acs_object_context_index
where ancestor_id = new.object_id
LOOP
insert into acs_object_context_index
(object_id, ancestor_id, n_generations)
select
pair.object_id, ancestor_id,
n_generations + pair.n_generations + 1 as n_generations
from acs_object_context_index
where object_id = new.context_id;
end loop;
else if new.object_id != 0 then
for pair in select *
from acs_object_context_index
where ancestor_id = new.object_id
LOOP
insert into acs_object_context_index
(object_id, ancestor_id, n_generations)
values
(pair.object_id, 0, pair.n_generations + 1);
end loop;
end if; end if;
return new;
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create trigger acs_objects_context_id_up_tr after update on acs_objects
for each row execute procedure acs_objects_context_id_up_tr () |
[MSSQL] /* Skipped */ |
|
[PG] create function acs_objects_context_id_del_tr () returns opaque as '
begin
delete from acs_object_context_index
where object_id = old.object_id;
return old;
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create trigger acs_objects_context_id_del_tr before delete on acs_objects
for each row execute procedure acs_objects_context_id_del_tr () |
[MSSQL] /* Skipped */ |
|
[PG] create sequence t_acs_attribute_value_id_seq |
[MSSQL] CREATE TABLE t_acs_attribute_value_id_seq ( sequence int identity(1,1), dummy bit default 0 );
GO
CREATE PROC acs_attribute_value_id_seq__nextval @ms_return_value int = 0 OUTPUT
as
INSERT INTO t_acs_attribute_value_id_seq VALUES (default)
SET @ms_return_value = @@IDENTITY
RETURN @ms_return_value
|
|
[PG] create view acs_attribute_value_id_seq as
select nextval('t_acs_attribute_value_id_seq') as nextval |
[MSSQL] /* Skipped */ |
|
[PG] create table acs_attribute_values (
object_id integer not null
constraint acs_attr_values_obj_id_fk
references acs_objects (object_id) on delete cascade,
attribute_id integer not null
constraint acs_attr_values_attr_id_fk
references acs_attributes (attribute_id),
attr_value text,
constraint acs_attribute_values_pk primary key
(object_id, attribute_id)
) |
[MSSQL] create table acs_attribute_values ( object_id integer not null
constraint acs_attr_values_obj_id_fk
references acs_objects (object_id) ,
attribute_id integer not null
constraint acs_attr_values_attr_id_fk
references acs_attributes (attribute_id),
attr_value text,
constraint acs_attribute_values_pk primary key
(object_id, attribute_id)
)
GO
|
|
[PG] create index acs_attr_values_attr_id_idx on acs_attribute_values (attribute_id) |
[MSSQL] create index acs_attr_values_attr_id_idx on acs_attribute_values (attribute_id) |
|
[PG] create table acs_static_attr_values (
object_type varchar(100) not null
constraint acs_static_a_v_obj_id_fk
references acs_object_types (object_type) on delete cascade,
attribute_id integer not null
constraint acs_static_a_v_attr_id_fk
references acs_attributes (attribute_id),
attr_value text,
constraint acs_static_a_v_pk primary key
(object_type, attribute_id)
) |
[MSSQL] create table acs_static_attr_values ( object_type varchar(100) not null
constraint acs_static_a_v_obj_id_fk
references acs_object_types (object_type) ,
attribute_id integer not null
constraint acs_static_a_v_attr_id_fk
references acs_attributes (attribute_id),
attr_value text,
constraint acs_static_a_v_pk primary key
(object_type, attribute_id)
)
GO
|
|
[PG] create index acs_stat_attrs_attr_id_idx on acs_static_attr_values (attribute_id) |
[MSSQL] create index acs_stat_attrs_attr_id_idx on acs_static_attr_values (attribute_id) |
|
[PG] create function acs_object__initialize_attributes (integer)
returns integer as '
declare
initialize_attributes__object_id alias for $1;
v_object_type acs_objects.object_type%TYPE;
begin
insert into acs_attribute_values
(object_id, attribute_id, attr_value)
select
initialize_attributes__object_id, a.attribute_id, a.default_value
from acs_attributes a, acs_objects o
where a.object_type = o.object_type
and o.object_id = initialize_attributes__object_id
and a.storage = 'generic'
and a.static_p = 'f';
select object_type into v_object_type from acs_objects
where object_id = initialize_attributes__object_id;
insert into acs_static_attr_values
(object_type, attribute_id, attr_value)
select
v_object_type, a.attribute_id, a.default_value
from acs_attributes a, acs_objects o
where a.object_type = o.object_type
and o.object_id = initialize_attributes__object_id
and a.storage = 'generic'
and a.static_p = 't'
and not exists (select 1 from acs_static_attr_values
where object_type = a.object_type);
return 0;
end;' language 'plpgsql' |
[MSSQL]
CREATE PROC acs_object__initialize_attributes
@initialize_attributes__object_id integer,
@ms_return_value integer = 0 OUTPUT
AS
BEGIN
declare @v_object_type varchar(8000)
insert into acs_attribute_values ( object_id, attribute_id, attr_value )
select @initialize_attributes__object_id , a.attribute_id , a.default_value
from acs_attributes a,
acs_objects o
WHERE a.object_type = o.object_type and o.object_id = @initialize_attributes__object_id and a.storage = 'generic' and a.static_p = 'f'
select @v_object_type = object_type
from acs_objects
WHERE object_id = @initialize_attributes__object_id
insert into acs_static_attr_values ( object_type, attribute_id, attr_value )
select @v_object_type , a.attribute_id , a.default_value
from acs_attributes a,
acs_objects o
WHERE a.object_type = o.object_type and o.object_id = @initialize_attributes__object_id and a.storage = 'generic' and a.static_p = 't' and not exists (
select 1
from acs_static_attr_values
WHERE object_type = a.object_type
)
RETURN 0
END -- stored proc
|
|
[PG] create function acs_object__new (integer,varchar,timestamp,integer,varchar,integer)
returns integer as '
declare
new__object_id alias for $1;
new__object_type alias for $2;
new__creation_date alias for $3;
new__creation_user alias for $4;
new__creation_ip alias for $5;
new__context_id alias for $6;
v_object_id acs_objects.object_id%TYPE;
begin
if new__object_id is null then
select acs_object_id_seq.nextval
into v_object_id from dual;
else
v_object_id := new__object_id;
end if;
insert into acs_objects
(object_id, object_type, context_id,
creation_date, creation_user, creation_ip)
values
(v_object_id, new__object_type, new__context_id,
new__creation_date, new__creation_user, new__creation_ip);
PERFORM acs_object__initialize_attributes(v_object_id);
return v_object_id;
end;' language 'plpgsql' |
[MSSQL]
CREATE PROC acs_object__new
@new__object_id integer,
@new__object_type varchar(8000),
@new__creation_date varchar(50),
@new__creation_user integer,
@new__creation_ip varchar(8000),
@new__context_id integer,
@ms_return_value integer = 0 OUTPUT
AS
BEGIN
declare @v_object_id int
declare @retval int
IF @new__object_id is null BEGIN
exec acs_object_id_seq__nextval @ms_return_value = @retval OUTPUT
select @v_object_id = @retval
END
ELSE BEGIN
SET @v_object_id = @new__object_id
END --IF
insert into acs_objects ( object_id, object_type, context_id, creation_date, creation_user, creation_ip ) values ( @v_object_id , @new__object_type , @new__context_id , @new__creation_date , @new__creation_user , @new__creation_ip )
EXEC dbo.acs_object__initialize_attributes @v_object_id
SET @ms_return_value = @v_object_id
RETURN @ms_return_value
END -- stored proc
|
|
[PG] create function acs_object__new (integer,varchar) returns integer as '
declare
object_id alias for $1;
object_type alias for $2;
begin
return acs_object__new(object_id,object_type,now(),null,null,null);
end;' language 'plpgsql' |
[MSSQL]
CREATE PROC acs_object__new_2
@object_id integer,
@object_type varchar(8000),
@ms_return_value integer = 0 OUTPUT
AS
BEGIN
SET @ms_return_value = dbo.acs_object__new ( @object_id ,
@object_type ,
getdate() ,
null ,
null ,
null )
RETURN @ms_return_value
END -- stored proc
|
|
[PG] create function acs_object__delete (integer)
returns integer as '
declare
delete__object_id alias for $1;
obj_type record;
begin
delete from acs_attribute_values where object_id = delete__object_id;
for obj_type
in select o2.table_name, o2.id_column
from acs_object_types o1, acs_object_types o2
where o1.object_type = (select object_type
from acs_objects o
where o.object_id = delete__object_id)
and o2.tree_sortkey <= o1.tree_sortkey
and o1.tree_sortkey like (o2.tree_sortkey + '%')
order by o2.tree_sortkey desc
loop
execute 'delete from ' + obj_type.table_name +
' where ' + obj_type.id_column + ' = ' + delete__object_id;
end loop;
return 0;
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create function acs_object__name (integer)
returns varchar as '
declare
name__object_id alias for $1;
object_name varchar;
v_object_id integer;
obj_type record;
obj record;
begin
for obj_type
in select o2.name_method
from acs_object_types o1, acs_object_types o2
where o1.object_type = (select object_type
from acs_objects o
where o.object_id = name__object_id)
and o2.tree_sortkey <= o1.tree_sortkey
and o1.tree_sortkey like (o2.tree_sortkey + '%')
order by o2.tree_sortkey desc
loop
if obj_type.name_method != '' and obj_type.name_method is NOT null then
for obj in execute 'select ' + obj_type.name_method + '(' + name__object_id + ')::varchar as object_name' loop
object_name := obj.object_name;
exit;
end loop;
exit;
end if;
end loop;
return object_name;
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create function acs_object__default_name (integer)
returns varchar as '
declare
default_name__object_id alias for $1;
object_type_pretty_name acs_object_types.pretty_name%TYPE;
begin
select ot.pretty_name
into object_type_pretty_name
from acs_objects o, acs_object_types ot
where o.object_id = default_name__object_id
and o.object_type = ot.object_type;
return object_type_pretty_name + ' ' + default_name__object_id;
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create function acs_object__get_attribute_storage (integer,varchar)
returns text as '
declare
object_id_in alias for $1;
attribute_name_in alias for $2;
v_column varchar;
v_table_name varchar;
v_key_sql text;
v_object_type acs_attributes.object_type%TYPE;
v_static acs_attributes.static_p%TYPE;
v_attr_id acs_attributes.attribute_id%TYPE;
v_storage acs_attributes.storage%TYPE;
v_attr_name acs_attributes.attribute_name%TYPE;
v_id_column varchar(200);
v_sql text;
v_return text;
v_rec record;
begin
select
a.attribute_id, a.static_p, a.storage, a.table_name, a.attribute_name,
a.object_type, a.column_name, t.id_column
into
v_attr_id, v_static, v_storage, v_table_name, v_attr_name,
v_object_type, v_column, v_id_column
from
acs_attributes a,
(select o2.object_type, o2.id_column
from acs_object_types o1, acs_object_types o2
where o1.object_type = (select object_type
from acs_objects o
where o.object_id = object_id_in)
and o2.tree_sortkey <= o1.tree_sortkey
and o1.tree_sortkey like (o2.tree_sortkey + '%')
) t
where
a.attribute_name = attribute_name_in
and
a.object_type = t.object_type;
if NOT FOUND then
raise EXCEPTION '-20000: No such attribute %::% in acs_object.get_attribute_storage.', v_object_type, attribute_name_in;
end if;
if v_table_name is null or v_table_name = '' then
if v_storage = 'generic' then
v_column := 'attr_value';
if v_static = 'f' then
v_table_name := 'acs_attribute_values';
v_key_sql := '(object_id = ' + object_id_in + ' and ' +
'attribute_id = ' + v_attr_id + ')';
else
v_table_name := 'acs_static_attr_values';
v_key_sql := '(object_type = ''' + v_object_type + ''' and ' +
'attribute_id = ' + v_attr_id + ')';
end if;
else
if v_static = 'f' then
select
table_name, id_column
into
v_table_name, v_id_column
from
acs_object_types
where
object_type = v_object_type;
if NOT FOUND then
raise EXCEPTION '-20000: No data found for attribute %::% in acs_object.get_attribute_storage', v_object_type, attribute_name_in;
end if;
else
raise EXCEPTION '-20000: No table name specified for storage specific static attribute %::% in acs_object.get_attribute_storage.',v_object_type, attribute_name_in;
end if;
end if;
else
select id_column into v_id_column from acs_object_type_tables
where object_type = v_object_type
and table_name = v_table_name;
if NOT FOUND then
raise EXCEPTION '-20000: No data found for attribute %::% in acs_object.get_attribute_storage', v_object_type, attribute_name_in;
end if;
end if;
if v_column is null or v_column = '' then
if v_storage = 'generic' then
v_column := 'attr_value';
else
v_column := v_attr_name;
end if;
end if;
if v_key_sql is null or v_key_sql = '' then
if v_static = 'f' then
v_key_sql := v_id_column + ' = ' + object_id_in ;
else
v_key_sql := v_id_column + ' = ''' + v_object_type + '''';
end if;
end if;
return v_column + ',' + v_table_name + ',' + v_key_sql;
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create function acs_object__get_attr_storage_column(text)
returns text as '
declare
v_vals alias for $1;
v_idx integer;
begin
v_idx := strpos(v_vals,',');
if v_idx = 0 then
raise exception 'invalid storage format: acs_object.get_attr_storage_column';
end if;
return substr(v_vals,1,v_idx - 1);
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create function acs_object__get_attr_storage_table(text)
returns text as '
declare
v_vals alias for $1;
v_idx integer;
v_tmp varchar;
begin
v_idx := strpos(v_vals,',');
if v_idx = 0 then
raise exception 'invalid storage format: acs_object.get_attr_storage_table';
end if;
v_tmp := substr(v_vals,v_idx + 1);
v_idx := strpos(v_tmp,',');
if v_idx = 0 then
raise exception 'invalid storage format: acs_object.get_attr_storage_table';
end if;
return substr(v_tmp,1,v_idx - 1);
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create function acs_object__get_attr_storage_sql(text)
returns text as '
declare
v_vals alias for $1;
v_idx integer;
v_tmp varchar;
begin
v_idx := strpos(v_vals, ',');
if v_idx = 0 then
raise exception 'invalid storage format: acs_object.get_attr_storage_sql';
end if;
v_tmp := substr(v_vals, v_idx + 1);
v_idx := strpos(v_tmp, ',');
if v_idx = 0 then
raise exception 'invalid storage format: acs_object.get_attr_storage_sql';
end if;
return substr(v_tmp, v_idx + 1);
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create function acs_object__get_attribute (integer,varchar)
returns text as '
declare
object_id_in alias for $1;
attribute_name_in alias for $2;
v_table_name varchar(200);
v_column varchar(200);
v_key_sql text;
v_return text;
v_storage text;
v_rec record;
begin
v_storage := acs_object__get_attribute_storage(object_id_in, attribute_name_in);
v_column := acs_object__get_attr_storage_column(v_storage);
v_table_name := acs_object__get_attr_storage_table(v_storage);
v_key_sql := acs_object__get_attr_storage_sql(v_storage);
for v_rec in execute 'select ' + quote_ident(v_column) + '::text as return from ' + quote_ident(v_table_name) + ' where ' + v_key_sql
LOOP
v_return := v_rec.return;
exit;
end loop;
if not FOUND then
return null;
end if;
return v_return;
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create function acs_object__set_attribute (integer,varchar,varchar)
returns integer as '
declare
object_id_in alias for $1;
attribute_name_in alias for $2;
value_in alias for $3;
v_table_name varchar;
v_column varchar;
v_key_sql text;
v_return text;
v_storage text;
begin
v_storage := acs_object__get_attribute_storage(object_id_in, attribute_name_in);
v_column := acs_object__get_attr_storage_column(v_storage);
v_table_name := acs_object__get_attr_storage_table(v_storage);
v_key_sql := acs_object__get_attr_storage_sql(v_storage);
execute 'update ' + v_table_name + ' set ' + quote_ident(v_column) + ' = ' + quote_literal(value_in) + ' where ' + v_key_sql;
return 0;
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create function acs_object__check_context_index (integer,integer,integer)
returns boolean as '
declare
check_context_index__object_id alias for $1;
check_context_index__ancestor_id alias for $2;
check_context_index__n_generations alias for $3;
n_rows integer;
n_gens integer;
begin
select case when count(*) = 0 then 0 else 1 end into n_rows
from acs_object_context_index
where object_id = check_context_index__object_id
and ancestor_id = check_context_index__ancestor_id;
if n_rows = 1 then
select n_generations into n_gens
from acs_object_context_index
where object_id = check_context_index__object_id
and ancestor_id = check_context_index__ancestor_id;
if n_gens != check_context_index__n_generations then
PERFORM acs_log__error('acs_object.check_representation',
'Ancestor ' +
check_context_index__ancestor_id + ' of object ' +
check_context_index__object_id +
' reports being generation ' + n_gens +
' when it is actually generation ' +
check_context_index__n_generations +
'.');
return 'f';
else
return 't';
end if;
else
PERFORM acs_log__error('acs_object.check_representation',
'Ancestor ' +
check_context_index__ancestor_id +
' of object ' + check_context_index__object_id
+ ' is missing an entry in acs_object_context_index.');
return 'f';
end if;
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create function acs_object__check_object_ancestors (integer,integer,integer)
returns boolean as '
declare
check_object_ancestors__object_id alias for $1;
check_object_ancestors__ancestor_id alias for $2;
check_object_ancestors__n_generations alias for $3;
check_object_ancestors__context_id acs_objects.context_id%TYPE;
check_object_ancestors__security_inherit_p acs_objects.security_inherit_p%TYPE;
n_rows integer;
n_gens integer;
result boolean;
begin
result := 't';
select context_id, security_inherit_p
into check_object_ancestors__context_id,
check_object_ancestors__security_inherit_p
from acs_objects
where object_id = check_object_ancestors__ancestor_id;
if check_object_ancestors__ancestor_id = 0 then
if check_object_ancestors__context_id is null then
result := 't';
else
PERFORM acs_log__error('acs_object.check_representation',
'Object 0 doesn''t have a null context_id');
result := 'f';
end if;
else
if check_object_ancestors__context_id is null or
check_object_ancestors__security_inherit_p = 'f'
THEN
check_object_ancestors__context_id := 0;
end if;
if acs_object__check_context_index(check_object_ancestors__object_id,
check_object_ancestors__ancestor_id,
check_object_ancestors__n_generations) = 'f' then
result := 'f';
end if;
if acs_object__check_object_ancestors(check_object_ancestors__object_id,
check_object_ancestors__context_id,
check_object_ancestors__n_generations + 1) = 'f' then
result := 'f';
end if;
end if;
return result;
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create function acs_object__check_object_descendants (integer,integer,integer)
returns boolean as '
declare
object_id alias for $1;
descendant_id alias for $2;
n_generations alias for $3;
result boolean;
obj record;
begin
result := 't';
if acs_object__check_context_index(descendant_id, object_id, n_generations) = 'f' then
result := 'f';
end if;
for obj in select *
from acs_objects
where context_id = descendant_id
and security_inherit_p = 't' loop
if acs_object__check_object_descendants(object_id, obj.object_id,
n_generations + 1) = 'f' then
result := 'f';
end if;
end loop;
return result;
end;' language 'plpgsql' |
[MSSQL]
CREATE PROC acs_object__check_object_descendants
@object_id integer,
@descendant_id integer,
@n_generations integer,
@ms_return_value boolean = OUTPUT
AS
BEGIN
declare @result boolean
declare @obj__object_id int
SET @result = 't'
IF dbo.acs_object__check_context_index ( @descendant_id ,
@object_id ,
@n_generations ) = 'f' BEGIN
SET @result = 'f'
END --IF
DECLARE cursor_1 CURSOR FOR
select object_id
from acs_objects
WHERE context_id = @descendant_id and security_inherit_p = 't'
OPEN cursor_1
FETCH NEXT FROM cursor_1 INTO @obj__object_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF dbo.acs_object__check_object_descendants ( @object_id ,
@obj__object_id ,
@n_generations + 1 ) = 'f' BEGIN
SET @result = 'f'
END --IF
FETCH NEXT FROM cursor_1 INTO @obj__object_id
END --while
CLOSE cursor_1
DEALLOCATE cursor_1
SET @ms_return_value = @result
RETURN @ms_return_value
END -- stored proc
|
|
[PG] create function acs_object__check_path (integer,integer)
returns boolean as '
declare
check_path__object_id alias for $1;
check_path__ancestor_id alias for $2;
check_path__context_id acs_objects.context_id%TYPE;
check_path__security_inherit_p acs_objects.security_inherit_p%TYPE;
begin
if check_path__object_id = check_path__ancestor_id then
return 't';
end if;
select context_id, security_inherit_p
into check_path__context_id, check_path__security_inherit_p
from acs_objects
where object_id = check_path__object_id;
if check_path__object_id = 0 and check_path__context_id is null then
return 'f';
end if;
if check_path__context_id is null or check_path__security_inherit_p = 'f'
then
check_path__context_id := 0;
end if;
return acs_object__check_path(check_path__context_id,
check_path__ancestor_id);
end;' language 'plpgsql' |
[MSSQL]
CREATE FUNCTION acs_object__check_path(@check_path__object_id integer,
@check_path__ancestor_id integer)
RETURNS boolean
AS
BEGIN
declare @check_path__context_id int
declare @check_path__security_inherit_p varchar(8000)
IF @check_path__object_id = @check_path__ancestor_id BEGIN
RETURN 't'
END --IF
select @check_path__context_id = context_id ,
@check_path__security_inherit_p = security_inherit_p
from acs_objects
WHERE object_id = @check_path__object_id
IF @check_path__object_id = 0 and @check_path__context_id is null BEGIN
RETURN 'f'
END --IF
IF @check_path__context_id is null or @check_path__security_inherit_p = 'f' BEGIN
SET @check_path__context_id = 0
END --IF
RETURN dbo.acs_object__check_path ( @check_path__context_id ,
@check_path__ancestor_id )
RETURN NULL -- placeholder required by tsql
END -- function
|
|
[PG] create function acs_object__check_representation (integer)
returns boolean as '
declare
check_representation__object_id alias for $1;
result boolean;
check_representation__object_type acs_objects.object_type%TYPE;
n_rows integer;
v_rec record;
row record;
begin
result := 't';
PERFORM acs_log__notice('acs_object.check_representation',
'Running acs_object.check_representation on object_id = '
+ check_representation__object_id + '.');
select object_type into check_representation__object_type
from acs_objects
where object_id = check_representation__object_id;
PERFORM acs_log__notice('acs_object.check_representation',
'OBJECT STORAGE INTEGRITY TEST');
for v_rec in select t.object_type, t.table_name, t.id_column
from acs_object_type_supertype_map m, acs_object_types t
where m.ancestor_type = t.object_type
and m.object_type = check_representation__object_type
union
select object_type, table_name, id_column
from acs_object_types
where object_type = check_representation__object_type
LOOP
for row in execute 'select case when count(*) = 0 then 0 else 1 end as n_rows from ' + quote_ident(v_rec.table_name) + ' where ' + quote_ident(v_rec.id_column) + ' = ' + check_representation__object_id
LOOP
n_rows := row.n_rows;
exit;
end LOOP;
if n_rows = 0 then
result := 'f';
PERFORM acs_log__error('acs_object.check_representation',
'Table ' + v_rec.table_name +
' (primary storage for ' +
v_rec.object_type +
') doesn''t have a row for object ' +
check_representation__object_id + ' of type ' +
check_representation__object_type + '.');
end if;
end loop;
PERFORM acs_log__notice('acs_object.check_representation',
'OBJECT CONTEXT INTEGRITY TEST');
if acs_object__check_object_ancestors(check_representation__object_id,
check_representation__object_id, 0) = 'f' then
result := 'f';
end if;
if acs_object__check_object_descendants(check_representation__object_id,
check_representation__object_id, 0) = 'f' then
result := 'f';
end if;
for row in select object_id, ancestor_id, n_generations
from acs_object_context_index
where object_id = check_representation__object_id
or ancestor_id = check_representation__object_id
LOOP
if acs_object__check_path(row.object_id, row.ancestor_id) = 'f' then
PERFORM acs_log__error('acs_object.check_representation',
'acs_object_context_index contains an extraneous row: '
+ 'object_id = ' + row.object_id +
', ancestor_id = ' + row.ancestor_id +
', n_generations = ' + row.n_generations + '.');
result := 'f';
end if;
end loop;
PERFORM acs_log__notice('acs_object.check_representation',
'Done running acs_object.check_representation ' +
'on object_id = ' + check_representation__object_id + '.');
return result;
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create table general_objects (
object_id integer not null
constraint general_objects_object_id_fk
references acs_objects (object_id)
constraint general_objects_pk
primary key,
on_which_table varchar(30) not null,
on_what_id integer not null,
constraint general_objects_un
unique (on_which_table, on_what_id)
) |
[MSSQL] create table general_objects ( object_id integer not null
constraint general_objects_object_id_fk
references acs_objects (object_id)
constraint general_objects_pk
primary key,
on_which_table varchar(30) not null,
on_what_id integer not null,
constraint general_objects_un
unique (on_which_table, on_what_id)
)
GO
|
|
| [PG] |
[MSSQL] /* Skipped */ |
|
| File packages/acs-kernel/sql/postgresql/acs-object-util.sql |
[PG] create function acs_object_util__object_type_exist_p (varchar)
returns boolean as '
declare
p_object_type alias for $1;
v_exist_p boolean := 't';
begin
select (case when count(*)=1 then 't' else 'f' end) into v_exist_p
from acs_object_types
where object_type = p_object_type;
return v_exist_p;
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create function acs_object_util__get_object_type (integer)
returns varchar as '
declare
p_object_id alias for $1;
v_object_type varchar(100);
begin
select object_type into v_object_type
from acs_objects
where object_id = p_object_id;
return v_object_type;
if not found then
raise exception 'Invalid Object id: % ', p_object_id;
end if;
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create function acs_object_util__type_ancestor_type_p (varchar,varchar)
returns boolean as '
declare
p_object_type1 alias for $1;
p_object_type2 alias for $2;
v_exist_p boolean := 'f';
v_count integer := 0;
begin
v_exist_p := acs_object_util__object_type_exist_p(p_object_type1);
if v_exist_p = 'f' then
raise exception 'Object type % does not exist', p_object_type1;
end if;
v_exist_p := acs_object_util__object_type_exist_p(p_object_type2);
if v_exist_p = 'f' then
raise exception 'Object type % does not exist', p_object_type2;
end if;
select count(*) into v_count
from dual
where p_object_type2 in (select o2.object_type
from acs_object_types o1, acs_object_types o2
where o1.object_type = p_object_type1
and o2.tree_sortkey <= o1.tree_sortkey
and o1.tree_sortkey like (o2.tree_sortkey + '%'));
select (case when v_count=1 then 't' else 'f' end) into v_exist_p;
return v_exist_p;
end;' language 'plpgsql' |
[MSSQL] /* Skipped */ |
|
[PG] create function acs_object_util__object_ancestor_type_p (integer,varchar)
returns boolean as '
declare
p_object_id alias for $1;
p_object_type alias for $2;
v_exist_p boolean := 'f';
v_object_type varchar(100);
begin
v_object_type := acs_object_util__get_object_type (p_object_id);
v_exist_p := acs_object_util__type_ancestor_type_p (v_object_type, p_object_type);
return v_exist_p;
end;' language 'plpgsql' |
[MSSQL]
CREATE FUNCTION acs_object_util__object_ancestor_type_p(@p_object_id integer,
@p_object_type varchar(8000))
RETURNS boolean
AS
BEGIN
declare @v_object_type varchar ( 100 )
declare @v_exist_p boolean
SET @v_object_type = dbo.acs_object_util__get_object_type ( @p_object_id )
SET @v_exist_p = dbo.acs_object_util__type_ancestor_type_p ( @v_object_type ,
@p_object_type )
RETURN @v_exist_p
RETURN NULL -- placeholder required by tsql
END -- function
|
|
[PG] create function acs_object_util__object_type_p (integer,varchar)
returns boolean as '
declare
p_object_id alias for $1;
p_object_type alias for $2;
v_exist_p boolean := 'f';
begin
v_exist_p := acs_object_util__object_ancestor_type_p(p_object_id, p_object_type);
return v_exist_p;
end;' language 'plpgsql' |
[MSSQL]
CREATE FUNCTION acs_object_util__object_type_p(@p_object_id integer,
@p_object_type varchar(8000))
RETURNS boolean
AS
BEGIN
declare @v_exist_p boolean
SET @v_exist_p = dbo.acs_object_util__object_ancestor_type_p ( @p_object_id ,
@p_object_type )
RETURN @v_exist_p
RETURN NULL -- placeholder required by tsql
END -- function
|
|
| [PG] |
[MSSQL] /* Skipped */ |
|
| File packages/acs-kernel/sql/postgresql/acs-relationships-create.sql |
[PG] create table acs_rel_roles (
role varchar(100) not null
constraint acs_rel_roles_pk primary key,
pretty_name varchar(100) not null,
pretty_plural varchar(100) not null
) |
[MSSQL] create table acs_rel_roles ( role varchar(100) not null
constraint acs_rel_roles_pk primary key,
pretty_name varchar(100) not null,
pretty_plural varchar(100) not null
)
GO
|
|
[PG] create table acs_rel_types (
rel_type varchar(100) not null
constraint acs_rel_types_pk primary key
constraint acs_rel_types_rel_type_fk
references acs_object_types(object_type),
object_type_one varchar(100) not null
constraint acs_rel_types_obj_type_1_fk
references acs_object_types (object_type),
role_one varchar(100) constraint acs_rel_types_role_1_fk
references acs_rel_roles (role),
min_n_rels_one integer default 0 not null
constraint acs_rel_types_min_n_1_ck
check (min_n_rels_one >= 0),
max_n_rels_one integer
constraint acs_rel_types_max_n_1_ck
check (max_n_rels_one >= 0),
object_type_two varchar(100) not null
constraint acs_rel_types_obj_type_2_fk
references acs_object_types (object_type),
role_two varchar(100) constraint acs_rel_types_role_2_fk
references acs_rel_roles (role),
min_n_rels_two integer default 0 not null
constraint acs_rel_types_min_n_2_ck
check (min_n_rels_two >= 0),
max_n_rels_two integer
constraint acs_rel_types_max_n_2_ck
check (max_n_rels_two >= 0),
constraint acs_rel_types_n_rels_one_ck
check (min_n_rels_one <= max_n_rels_one),
constraint acs_rel_types_n_rels_two_ck
check (min_n_rels_two <= max_n_rels_two)
) |
[MSSQL] create table acs_rel_types ( rel_type varchar(100) not null
constraint acs_rel_types_pk primary key
constraint acs_rel_types_rel_type_fk
references acs_object_types(object_type),
object_type_one varchar(100) not null
constraint acs_rel_types_obj_type_1_fk
references acs_object_types (object_type),
role_one varchar(100) constraint acs_rel_types_role_1_fk
references acs_rel_roles (role),
min_n_rels_one integer default 0 not null
constraint acs_rel_types_min_n_1_ck
check (min_n_rels_one >= 0),
max_n_rels_one integer
constraint acs_rel_types_max_n_1_ck
check (max_n_rels_one >= 0),
object_type_two varchar(100) not null
constraint acs_rel_types_obj_type_2_fk
references acs_object_types (object_type),
role_two varchar(100) constraint acs_rel_types_role_2_fk
references acs_rel_roles (role),
min_n_rels_two integer default 0 not null
constraint acs_rel_types_min_n_2_ck
check (min_n_rels_two >= 0),
max_n_rels_two integer
constraint acs_rel_types_max_n_2_ck
check (max_n_rels_two >= 0),
constraint acs_rel_types_n_rels_one_ck
check (min_n_rels_one <= max_n_rels_one),
constraint acs_rel_types_n_rels_two_ck
check (min_n_rels_two <= max_n_rels_two)
)
GO
|
|
[PG] create index acs_rel_types_objtypeone_idx on acs_rel_types (object_type_one) |
[MSSQL] create index acs_rel_types_objtypeone_idx on acs_rel_types (object_type_one) |
|
[PG] create index acs_rel_types_role_one_idx on acs_rel_types (role_one) |
[MSSQL] create index acs_rel_types_role_one_idx on acs_rel_types (role_one) |
|
[PG] create index acs_rel_types_objtypetwo_idx on acs_rel_types (object_type_two) |
[MSSQL] create index acs_rel_types_objtypetwo_idx on acs_rel_types (object_type_two) |
|
[PG] create index acs_rel_types_role_two_idx on acs_rel_types (role_two) |
[MSSQL] create index acs_rel_types_role_two_idx on acs_rel_types (role_two) |
|
[PG] create function acs_rel_type__create_role (varchar,varchar,varchar)
returns integer as '
declare
create_role__role alias for $1;
create_role__pretty_name alias for $2;
create_role__pretty_plural alias for $3;
begin
insert into acs_rel_roles
(role, pretty_name, pretty_plural)
values
(create_role__role, coalesce(create_role__pretty_name,create_role__role), coalesce(create_role__pretty_plural,create_role__role));
return 0;
end;' language 'plpgsql' |
[MSSQL]
CREATE PROC acs_rel_type__create_role
@create_role__role varchar(8000),
@create_role__pretty_name varchar(8000),
@create_role__pretty_plural varchar(8000),
@ms_return_value integer = 0 OUTPUT
AS
BEGIN
insert into acs_rel_roles ( role, pretty_name, pretty_plural ) values ( @create_role__role , coalesce ( @create_role__pretty_name ,
@create_role__role ) , coalesce ( @create_role__pretty_plural ,
@create_role__role ) )
RETURN 0
END -- stored proc
|
|
[PG] create function acs_rel_type__create_role (varchar)
returns integer as '
declare
create_role__role alias for $1;
begin
perform acs_rel_type__create_role(create_role__role, NULL, NULL);
return 0;
end;' language 'plpgsql' |
[MSSQL]
CREATE PROC acs_rel_type__create_role_1
@create_role__role varchar(8000),
@ms_return_value integer = 0 OUTPUT
AS
BEGIN
EXEC dbo.acs_rel_type__create_role @create_role__role ,
NULL ,
NULL
RETURN 0
END -- stored proc
|
|
[PG] create function acs_rel_type__drop_role (varchar)
returns integer as '
declare
drop_role__role alias for $1;
begin
delete from acs_rel_roles
where role = drop_role__role;
return 0;
end;' language 'plpgsql' |
[MSSQL]
CREATE PROC acs_rel_type__drop_role
@drop_role__role varchar(8000),
@ms_return_value integer = 0 OUTPUT
AS
BEGIN
delete from acs_rel_roles WHERE role = @drop_role__role
RETURN 0
END -- stored proc
|
|
[PG] create function acs_rel_type__role_pretty_name (varchar)
returns varchar as '
declare
role_pretty_name__role alias for $1;
v_pretty_name acs_rel_roles.pretty_name%TYPE;
begin
select r.pretty_name into v_pretty_name
from acs_rel_roles r
where r.role = role_pretty_name__role;
return v_pretty_name;
end;' language 'plpgsql' |
[MSSQL]
CREATE FUNCTION acs_rel_type__role_pretty_name(@role_pretty_name__role varchar(8000))
RETURNS varchar
AS
BEGIN
declare @v_pretty_name varchar(8000)
select @v_pretty_name = r.pretty_name
from acs_rel_roles r
WHERE r.role = @role_pretty_name__role
RETURN CONVERT (varchar, @v_pretty_name )
RETURN NULL -- placeholder required by tsql
END -- function
|
|
[PG] create function acs_rel_type__role_pretty_plural (varchar)
returns varchar as '
declare
role_pretty_plural__role alias for $1;
v_pretty_plural acs_rel_roles.pretty_plural%TYPE;
begin
select r.pretty_plural into v_pretty_plural
from acs_rel_roles r
where r.role = role_pretty_plural__role;
return v_pretty_plural;
end;' language 'plpgsql' |
[MSSQL]
CREATE FUNCTION acs_rel_type__role_pretty_plural(@role_pretty_plural__role varchar(8000))
RETURNS varchar
AS
BEGIN
declare @v_pretty_plural varchar(8000)
select @v_pretty_plural = r.pretty_plural
from acs_rel_roles r
WHERE r.role = @role_pretty_plural__role
RETURN CONVERT (varchar, @v_pretty_plural )
RETURN NULL -- placeholder required by tsql
END -- function
|
|
[PG] create function acs_rel_type__create_type (varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,integer,integer,varchar,varchar,integer,integer)
returns integer as '
declare
create_type__rel_type alias for $1;
create_type__pretty_name alias for $2;
create_type__pretty_plural alias for $3;
create_type__supertype alias for $4;
create_type__table_name alias for $5;
create_type__id_column alias for $6;
create_type__package_name alias for $7;
create_type__object_type_one alias for $8;
create_type__role_one alias for $9;
create_type__min_n_rels_one alias for $10;
create_type__max_n_rels_one alias for $11;
create_type__object_type_two alias for $12;
create_type__role_two alias for $13;
create_type__min_n_rels_two alias for $14;
create_type__max_n_rels_two alias for $15;
type_extension_table acs_object_types.type_extension_table%TYPE default null;
abstract_p acs_object_types.abstract_p%TYPE default 'f';
name_method acs_object_types.name_method%TYPE default null;
begin
PERFORM acs_object_type__create_type(
create_type__rel_type,
create_type__pretty_name,
create_type__pretty_plural,
create_type__supertype,
create_type__table_name,
create_type__id_column,
create_type__package_name,
abstract_p,
type_extension_table,
name_method
);
insert into acs_rel_types
(rel_type,
object_type_one, role_one,
min_n_rels_one, max_n_rels_one,
object_type_two, role_two,
min_n_rels_two, max_n_rels_two)
values
(create_type__rel_type,
create_type__object_type_one, create_type__role_one,
create_type__min_n_rels_one, create_type__max_n_rels_one,
create_type__object_type_two, create_type__role_two,
create_type__min_n_rels_two, create_type__max_n_rels_two);
return 0;
end;' language 'plpgsql' |
[MSSQL]
CREATE PROC acs_rel_type__create_type
@create_type__rel_type varchar(8000),
@create_type__pretty_name varchar(8000),
@create_type__pretty_plural varchar(8000),
@create_type__supertype varchar(8000),
@create_type__table_name varchar(8000),
@create_type__id_column varchar(8000),
@create_type__package_name varchar(8000),
@create_type__object_type_one varchar(8000),
@create_type__role_one varchar(8000),
@create_type__min_n_rels_one integer,
@create_type__max_n_rels_one integer,
@create_type__object_type_two varchar(8000),
@create_type__role_two varchar(8000),
@create_type__min_n_rels_two integer,
@create_type__max_n_rels_two integer,
@ms_return_value integer = 0 OUTPUT
AS
BEGIN
declare @abstract_p varchar(8000)
declare @type_extension_table varchar(8000)
declare @name_method varchar(8000)
EXEC dbo.acs_object_type__create_type @create_type__rel_type ,
@create_type__pretty_name ,
@create_type__pretty_plural ,
@create_type__supertype ,
@create_type__table_name ,
@create_type__id_column ,
@create_type__package_name ,
@abstract_p ,
@type_extension_table ,
@name_method
insert into acs_rel_types ( rel_type, object_type_one, role_one, min_n_rels_one, max_n_rels_one, object_type_two, role_two, min_n_rels_two, max_n_rels_two ) values ( @create_type__rel_type , @create_type__object_type_one , @create_type__role_one , @create_type__min_n_rels_one , @create_type__max_n_rels_one , @create_type__object_type_two , @create_type__role_two , @create_type__min_n_rels_two , @create_type__max_n_rels_two )
RETURN 0
END -- stored proc
|
|
[PG] create function acs_rel_type__create_type (varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,integer,integer,varchar,integer,integer)
returns integer as '
declare
create_type__rel_type alias for $1;
create_type__pretty_name alias for $2;
create_type__pretty_plural alias for $3;
create_type__supertype alias for $4;
create_type__table_name alias for $5;
create_type__id_column alias for $6;
create_type__package_name alias for $7;
create_type__type_extension_table alias for $8;
create_type__object_type_one alias for $9;
create_type__min_n_rels_one alias for $10;
create_type__max_n_rels_one alias for $11;
create_type__object_type_two alias for $12;
create_type__min_n_rels_two alias for $13;
create_type__max_n_rels_two alias for $14;
abstract_p acs_object_types.abstract_p%TYPE default 'f';
name_method acs_object_types.name_method%TYPE default null;
create_type__role_one acs_rel_types.role_one%TYPE default null;
create_type__role_two acs_rel_types.role_two%TYPE default null;
begin
PERFORM acs_object_type__create_type(
create_type__rel_type,
create_type__pretty_name,
create_type__pretty_plural,
create_type__supertype,
create_type__table_name,
create_type__id_column,
create_type__package_name,
abstract_p,
create_type__type_extension_table,
name_method
);
insert into acs_rel_types
(rel_type,
object_type_one, role_one,
min_n_rels_one, max_n_rels_one,
object_type_two, role_two,
min_n_rels_two, max_n_rels_two)
values
(create_type__rel_type,
create_type__object_type_one, create_type__role_one,
create_type__min_n_rels_one, create_type__max_n_rels_one,
create_type__object_type_two, create_type__role_two,
create_type__min_n_rels_two, create_type__max_n_rels_two);
return 0;
end;' language 'plpgsql' |
[MSSQL]
CREATE PROC acs_rel_type__create_type_14
@create_type__rel_type varchar(8000),
@create_type__pretty_name varchar(8000),
@create_type__pretty_plural varchar(8000),
@create_type__supertype varchar(8000),
@create_type__table_name varchar(8000),
@create_type__id_column varchar(8000),
@create_type__package_name varchar(8000),
@create_type__type_extension_table varchar(8000),
@create_type__object_type_one varchar(8000),
@create_type__min_n_rels_one integer,
@create_type__max_n_rels_one integer,
@create_type__object_type_two varchar(8000),
@create_type__min_n_rels_two integer,
@create_type__max_n_rels_two integer,
@ms_return_value integer = 0 OUTPUT
AS
BEGIN
declare @abstract_p varchar(8000)
declare @create_type__role_two varchar(8000)
declare @create_type__role_one varchar(8000)
declare @name_method varchar(8000)
EXEC dbo.acs_object_type__create_type @create_type__rel_type ,
@create_type__pretty_name ,
@create_type__pretty_plural ,
@create_type__supertype ,
@create_type__table_name ,
@create_type__id_column ,
@create_type__package_name ,
@abstract_p ,
@create_type__type_extension_table ,
@name_method
insert into acs_rel_types ( rel_type, object_type_one, role_one, min_n_rels_one, max_n_rels_one, object_type_two, role_two, min_n_rels_two, max_n_rels_two ) values ( @create_type__rel_type , @create_type__object_type_one , @create_type__role_one , @create_type__min_n_rels_one , @create_type__max_n_rels_one , @create_type__object_type_two , @create_type__role_two , @create_type__min_n_rels_two , @create_type__max_n_rels_two )
RETURN 0
END -- stored proc
|
|
[PG] create function acs_rel_type__drop_type (varchar,boolean)
returns integer as '
declare
drop_type__rel_type alias for $1;
drop_type__cascade_p alias for $2;
begin
delete from acs_rel_types
where rel_type = drop_type__rel_type;
PERFORM acs_object_type__drop_type(drop_type__rel_type,
drop_type__cascade_p);
return 0;
end;' language 'plpgsql' |
[MSSQL]
CREATE PROC acs_rel_type__drop_type
@drop_type__rel_type varchar(8000),
@drop_type__cascade_p boolean,
@ms_return_value integer = 0 OUTPUT
AS
BEGIN
delete from acs_rel_types WHERE rel_type = @drop_type__rel_type
EXEC dbo.acs_object_type__drop_type @drop_type__rel_type ,
@drop_type__cascade_p
RETURN 0
END -- stored proc
|
|
[PG] select acs_rel_type__create_type (
'relationship',
'Relationship',
'Relationships',
'acs_object',
'acs_rels',
'rel_id',
'acs_rel',
'acs_rel_types',
'acs_object',
0,
null::integer,
'acs_object',
0,
null::integer
) |
[MSSQL] /* Skipped */ |
|
[PG] create sequence t_acs_rel_id_seq |
[MSSQL] CREATE TABLE t_acs_rel_id_seq ( sequence int identity(1,1), dummy bit default 0 );
GO
CREATE PROC acs_rel_id_seq__nextval @ms_return_value int = 0 OUTPUT
as
INSERT INTO t_acs_rel_id_seq VALUES (default)
SET @ms_return_value = @@IDENTITY
RETURN @ms_return_value
|
|
[PG] create view acs_rel_id_seq as
select nextval('t_acs_rel_id_seq') as nextval |
[MSSQL] /* Skipped */ |
|
[PG] create table acs_rels (
rel_id integer not null
constraint acs_rels_rel_id_fk
references acs_objects (object_id)
constraint acs_rels_pk primary key,
rel_type varchar(100) not null
constraint acs_rels_rel_type_fk
references acs_rel_types (rel_type),
object_id_one integer not null
constraint acs_object_rels_one_fk
references acs_objects (object_id),
object_id_two integer not null
constraint acs_object_rels_two_fk
references acs_objects (object_id),
constraint acs_object_rels_un unique
(rel_type, object_id_one, object_id_two)
) |
[MSSQL] create table acs_rels ( rel_id integer not null
constraint acs_rels_rel_id_fk
references acs_objects (object_id)
constraint acs_rels_pk primary key,
rel_type varchar(100) not null
constraint acs_rels_rel_type_fk
references acs_rel_types (rel_type),
object_id_one integer not null
constraint acs_object_rels_one_fk
references acs_objects (object_id),
object_id_two integer not null
constraint acs_object_rels_two_fk
references acs_objects (object_id),
constraint acs_object_rels_un unique
(rel_type, object_id_one, object_id_two)
)
GO
|
|
[PG] create index acs_rels_object_id_one_idx on acs_rels (object_id_one) |
[MSSQL] create index acs_rels_object_id_one_idx on acs_rels (object_id_one) |
|
[PG] create index acs_rels_object_id_two_idx on acs_rels (object_id_two) |
[MSSQL] create index acs_rels_object_id_two_idx on acs_rels (object_id_two) |
|
[PG] create function acs_rels_in_tr () returns opaque as '
declare
dummy integer;
target_object_type_one acs_object_types.object_type%TYPE;
target_object_type_two acs_object_types.object_type%TYPE;
actual_object_type_one acs_object_types.object_type%TYPE;
actual_object_type_two acs_object_types.object_type%TYPE;
begin
select 1 into dummy
from acs_rel_types rt,
acs_objects o1,
acs_objects o2
where exists (select 1
from acs_object_types t
where t.object_type = o1.object_type
and t.tree_sortkey
like (select o.tree_sortkey + '%'
from acs_object_types o
where o.object_type = rt.object_type_one))
and exists (select 1
fro |