/*** packages/acs-kernel/sql/postgresql/postgresql.sql *******************************************/ /*** packages/acs-kernel/sql/postgresql/lob.sql *******************************************/ 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 GO create table lobs ( lob_id integer not null primary key, refcount integer not null default 0 ) GO GO 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) ) GO create index lob_data_index on lob_data(lob_id) GO 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 GO /*** packages/acs-kernel/sql/postgresql/acs-logs-create.sql *******************************************/ 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 GO 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 GO 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 GO 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 GO 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 GO 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 GO /*** packages/acs-kernel/sql/postgresql/acs-metadata-create.sql *******************************************/ 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 GO create index acs_obj_types_supertype_idx on acs_object_types (supertype) GO create index acs_obj_types_tree_skey_idx on acs_object_types (tree_sortkey) GO 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 + '%' ) GO 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 GO create index acs_objtype_tbls_objtype_idx on acs_object_type_tables (object_type) GO 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 GO 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 GO dbo.inline_0 GO drop proc inline_0 GO 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 GO 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 GO 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) GO create index acs_attrs_tbl_name_idx on acs_attributes (table_name) GO create index acs_attrs_datatype_idx on acs_attributes (datatype) GO 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 GO create index acs_enum_values_attr_id_idx on acs_enum_values (attribute_id) GO 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 GO create index acs_attr_desc_obj_type_idx on acs_attribute_descriptions (object_type) GO create index acs_attr_desc_attr_name_idx on acs_attribute_descriptions (attribute_name) GO 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 GO 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 GO 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 GO 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 GO 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 GO 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 GO 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 GO 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 GO 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 GO /*** packages/acs-kernel/sql/postgresql/acs-objects-create.sql *******************************************/ 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 GO dbo.inline_0 GO drop proc inline_0 GO 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 GO 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 GO create index acs_objects_context_object_idx on acs_objects (context_id, object_id) GO create index acs_objs_tree_skey_idx on acs_objects (tree_sortkey) GO create index acs_objects_creation_user_idx on acs_objects (creation_user) GO create index acs_objects_modify_user_idx on acs_objects (modifying_user) GO create index acs_objects_object_type_idx on acs_objects (object_type) GO 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 GO create index acs_obj_ctx_idx_ancestor_idx on acs_object_context_index (ancestor_id) GO create view acs_object_paths as select object_id , ancestor_id , n_generations from acs_object_context_index GO create view acs_object_contexts as select object_id , ancestor_id , n_generations from acs_object_context_index WHERE object_id != ancestor_id GO 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 GO 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 GO create index acs_attr_values_attr_id_idx on acs_attribute_values (attribute_id) GO 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 GO create index acs_stat_attrs_attr_id_idx on acs_static_attr_values (attribute_id) GO 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 GO 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 GO 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 GO 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 GO 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 GO 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 GO /*** packages/acs-kernel/sql/postgresql/acs-object-util.sql *******************************************/ 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 GO 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 GO /*** packages/acs-kernel/sql/postgresql/acs-relationships-create.sql *******************************************/ 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 GO 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 GO create index acs_rel_types_objtypeone_idx on acs_rel_types (object_type_one) GO create index acs_rel_types_role_one_idx on acs_rel_types (role_one) GO create index acs_rel_types_objtypetwo_idx on acs_rel_types (object_type_two) GO create index acs_rel_types_role_two_idx on acs_rel_types (role_two) GO 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 GO 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 GO 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 GO 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 GO 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 GO 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 GO 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 GO 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 GO 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 GO 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 GO create index acs_rels_object_id_one_idx on acs_rels (object_id_one) GO create index acs_rels_object_id_two_idx on acs_rels (object_id_two) GO CREATE PROC acs_rel__new @new__rel_id integer, @new__rel_type varchar(8000), @new__object_id_one integer, @new__object_id_two integer, @context_id integer, @creation_user integer, @creation_ip varchar(8000), @ms_return_value integer = 0 OUTPUT AS BEGIN declare @v_rel_id int EXEC dbo.acs_object__new @new__rel_id , @new__rel_type , getdate , @creation_user , @creation_ip , @context_id , @ms_return_value = @v_rel_id OUTPUT insert into acs_rels ( rel_id, rel_type, object_id_one, object_id_two ) values ( @v_rel_id , @new__rel_type , @new__object_id_one , @new__object_id_two ) SET @ms_return_value = @v_rel_id RETURN @ms_return_value END -- stored proc GO CREATE PROC acs_rel__delete @rel_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN EXEC dbo.acs_object__delete @rel_id RETURN 0 END -- stored proc GO create view rel_types_valid_obj_one_types as select rt.rel_type , th.object_type from acs_rel_types rt, ( select object_type , ancestor_type from acs_object_type_supertype_map UNION ALL select object_type , object_type as ancestor_type from acs_object_types ) th WHERE rt.object_type_one = th.ancestor_type GO create view rel_types_valid_obj_two_types as select rt.rel_type , th.object_type from acs_rel_types rt, ( select object_type , ancestor_type from acs_object_type_supertype_map UNION ALL select object_type , object_type as ancestor_type from acs_object_types ) th WHERE rt.object_type_two = th.ancestor_type GO /*** packages/acs-kernel/sql/postgresql/utilities-create.sql *******************************************/ CREATE FUNCTION util__logical_negation(@true_or_false boolean) RETURNS boolean AS BEGIN IF @true_or_false is null BEGIN RETURN null END ELSE BEGIN IF @true_or_false = 'f' BEGIN RETURN 't' END ELSE BEGIN RETURN 'f' END --IF END --IF RETURN NULL -- placeholder required by tsql END -- function GO /*** packages/acs-kernel/sql/postgresql/community-core-create.sql *******************************************/ CREATE PROC inline_0 @ms_return_value integer = 0 OUTPUT AS BEGIN declare @attr_id int EXEC dbo.acs_object_type__create_type 'party' , 'Party' , 'Parties' , 'acs_object' , 'parties' , 'party_id' , 'party' , 'f' , null , 'party.name' EXEC dbo.acs_attribute__create_attribute 'party' , 'email' , 'string' , 'Email Address' , 'Email Addresses' , null , null , null , 0 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'party' , 'url' , 'string' , 'URL' , 'URLs' , null , null , null , 0 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_object_type__create_type 'person' , 'Person' , 'People' , 'party' , 'persons' , 'person_id' , 'person' , 'f' , null , 'person.name' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'person' , 'first_names' , 'string' , 'First Names' , 'First Names' , null , null , null , 0 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'person' , 'last_name' , 'string' , 'Last Name' , 'Last Names' , null , null , null , 0 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_object_type__create_type 'user' , 'User' , 'Users' , 'person' , 'users' , 'user_id' , 'acs_user' , 'f' , null , null , @ms_return_value = @attr_id OUTPUT RETURN 0 END -- stored proc GO dbo.inline_0 GO drop proc inline_0 GO create table parties ( party_id integer not null constraint parties_party_id_fk references acs_objects (object_id) constraint parties_pk primary key, email varchar(100) constraint parties_email_un unique, url varchar(200) ) GO GO CREATE PROC party__delete @party_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN EXEC dbo.acs_object__delete @party_id RETURN 0 END -- stored proc GO CREATE FUNCTION party__name(@party_id integer) RETURNS varchar AS BEGIN IF @party_id = -1 BEGIN RETURN CONVERT (varchar, 'The Public' ) END ELSE BEGIN RETURN CONVERT (varchar, null ) END --IF RETURN NULL -- placeholder required by tsql END -- function GO create table persons ( person_id integer not null constraint persons_person_id_fk references parties (party_id) constraint persons_pk primary key, first_names varchar(100) not null, last_name varchar(100) not null ) GO GO CREATE PROC person__new @new__person_id integer, @new__object_type varchar(8000), @new__creation_date varchar(50), @new__creation_user integer, @new__creation_ip varchar(8000), @new__email varchar(8000), @new__url varchar(8000), @new__first_names varchar(8000), @new__last_name varchar(8000), @new__context_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN declare @v_person_id int EXEC dbo.party__new @new__person_id , @new__object_type , @new__creation_date , @new__creation_user , @new__creation_ip , @new__email , @new__url , @new__context_id , @ms_return_value = @v_person_id OUTPUT insert into persons ( person_id, first_names, last_name ) values ( @v_person_id , @new__first_names , @new__last_name ) SET @ms_return_value = @v_person_id RETURN @ms_return_value END -- stored proc GO CREATE PROC person__delete @delete__person_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN delete from persons WHERE person_id = @delete__person_id EXEC dbo.party__delete @delete__person_id RETURN 0 END -- stored proc GO CREATE FUNCTION person__name(@name__person_id integer) RETURNS varchar AS BEGIN declare @person_name varchar ( 200 ) select @person_name = first_names + ' ' + last_name from persons WHERE person_id = @name__person_id RETURN CONVERT (varchar, @person_name ) RETURN NULL -- placeholder required by tsql END -- function GO create table users ( user_id integer not null constraint users_user_id_fk references persons (person_id) constraint users_pk primary key, password char(40), salt char(40), screen_name varchar(100) constraint users_screen_name_un unique, priv_name integer default 0 not null, priv_email integer default 5 not null, email_verified_p boolean default 't', email_bouncing_p boolean default 'f' not null, no_alerts_until datetime, last_visit datetime, second_to_last_visit datetime, n_sessions integer default 1 not null, password_question varchar(1000), password_answer varchar(1000) ) GO GO create table user_preferences ( user_id integer constraint user_prefs_user_id_fk references users (user_id) constraint user_preferences_pk primary key, prefer_text_only_p boolean default 'f', language_preference char(2) default 'en', dont_spam_me_p boolean default 'f', email_type varchar(64) ) GO GO CREATE PROC inline_1 @ms_return_value integer = 0 OUTPUT AS BEGIN insert into acs_object_type_tables ( object_type, table_name, id_column ) values ( 'user' , 'user_preferences' , 'user_id' ) RETURN 0 END -- stored proc GO dbo.inline_1 GO drop proc inline_1 GO alter table acs_objects add constraint acs_objects_creation_user_fk foreign key (creation_user) references users(user_id) GO alter table acs_objects add constraint acs_objects_modifying_user_fk foreign key (modifying_user) references users(user_id) GO CREATE PROC acs_user__new @new__user_id integer, @new__object_type varchar(8000), @new__creation_date varchar(50), @new__creation_user integer, @new__creation_ip varchar(8000), @new__email varchar(8000), @new__url varchar(8000), @new__first_names varchar(8000), @new__last_name varchar(8000), @new__password char, @new__salt char, @new__password_question varchar(8000), @new__password_answer varchar(8000), @new__screen_name varchar(8000), @new__email_verified_p boolean, @new__context_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN declare @v_user_id int EXEC dbo.person__new @new__user_id , @new__object_type , @new__creation_date , @new__creation_user , @new__creation_ip , @new__email , @new__url , @new__first_names , @new__last_name , @new__context_id , @ms_return_value = @v_user_id OUTPUT insert into users ( user_id, password, salt, password_question, password_answer, screen_name, email_verified_p ) values ( @v_user_id , @new__password , @new__salt , @new__password_question , @new__password_answer , @new__screen_name , @new__email_verified_p ) insert into user_preferences ( user_id ) values ( @v_user_id ) SET @ms_return_value = @v_user_id RETURN @ms_return_value END -- stored proc GO CREATE PROC acs_user__new_5 @email varchar(8000), @fname varchar(8000), @lname varchar(8000), @pword char, @salt char, @ms_return_value integer = 0 OUTPUT AS BEGIN SET @ms_return_value = dbo.acs_user__new ( null , 'user' , getdate() , null , null , @email , null , @fname , @lname , @pword , @salt , null , null , null , 't' , null ) RETURN @ms_return_value END -- stored proc GO CREATE PROC acs_user__delete @delete__user_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN delete from user_preferences WHERE user_id = @delete__user_id delete from users WHERE user_id = @delete__user_id EXEC dbo.person__delete @delete__user_id RETURN 0 END -- stored proc GO /*** packages/acs-kernel/sql/postgresql/groups-create.sql *******************************************/ create table composition_rels ( rel_id integer constraint composition_rel_rel_id_fk references acs_rels (rel_id) constraint composition_rel_rel_id_pk primary key ) GO GO create table membership_rels ( rel_id integer constraint membership_rel_rel_id_fk references acs_rels (rel_id) constraint membership_rel_rel_id_pk primary key, member_state varchar(20) constraint membership_rel_mem_ck check (member_state in ('approved', 'needs approval', 'banned', 'rejected', 'deleted')) ) GO GO CREATE PROC inline_0 @ms_return_value integer = 0 OUTPUT AS BEGIN declare @attr_id int EXEC dbo.acs_object_type__create_type 'group' , 'Group' , 'Groups' , 'party' , 'groups' , 'group_id' , 'acs_group' , 'f' , 'group_types' , 'acs_group.name' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'group' , 'group_name' , 'string' , 'Group name' , 'Group names' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_rel_type__create_role 'composite' , 'Composite' , 'Composites' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_rel_type__create_role 'component' , 'Component' , 'Components' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_rel_type__create_type 'composition_rel' , 'Composition Relation' , 'Composition Relationships' , 'relationship' , 'composition_rels' , 'rel_id' , 'composition_rel' , 'group' , 'composite' , 0 , null , 'group' , 'component' , 0 , null , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_rel_type__create_role 'member' , 'Member' , 'Members' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_rel_type__create_type 'membership_rel' , 'Membership Relation' , 'Membership Relationships' , 'relationship' , 'membership_rels' , 'rel_id' , 'membership_rel' , 'group' , null , 0 , null , 'person' , 'member' , 0 , null , @ms_return_value = @attr_id OUTPUT RETURN 0 END -- stored proc GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'abstract_p', table 'openacs.dbo.acs_object_types'; column does not allow nulls. INSERT fails. *******************************************/ dbo.inline_0 GO drop proc inline_0 GO create table group_types ( group_type varchar(100) not null constraint group_types_pk primary key constraint group_types_obj_type_fk references acs_object_types (object_type), default_join_policy varchar(30) default 'open' not null constraint group_types_join_policy_ck check (default_join_policy in ('open', 'needs approval', 'closed')) ) GO GO create table groups ( group_id integer not null constraint groups_group_id_fk references parties (party_id) constraint groups_pk primary key, group_name varchar(100) not null, join_policy varchar(30) default 'open' not null constraint groups_join_policy_ck check (join_policy in ('open', 'needs approval', 'closed')) ) GO GO create table group_type_rels ( group_rel_type_id integer constraint gtr_group_rel_type_id_pk primary key, rel_type varchar(100) not null constraint gtr_rel_type_fk references acs_rel_types (rel_type) , group_type varchar(100) not null constraint gtr_group_type_fk references acs_object_types (object_type) , constraint gtr_group_rel_types_un unique (group_type, rel_type) ) GO GO create index group_type_rels_rel_type_idx on group_type_rels(rel_type) GO create table group_rels ( group_rel_id integer constraint group_rels_group_rel_id_pk primary key, rel_type varchar(100) not null constraint group_rels_rel_type_fk references acs_rel_types (rel_type) , group_id integer not null constraint group_rels_group_id_fk references groups (group_id) , constraint group_rels_group_rel_type_un unique (group_id, rel_type) ) GO GO create index group_rels_rel_type_idx on group_rels(rel_type) GO create table group_element_index ( group_id integer not null constraint group_element_index_grp_id_fk references groups (group_id), element_id integer not null constraint group_element_index_elem_id_fk references parties (party_id), rel_id integer not null constraint group_element_index_rel_id_fk references acs_rels (rel_id), container_id integer not null constraint group_element_index_cont_id_fk references groups (group_id), rel_type varchar(100) not null constraint group_elem_index_rel_type_fk references acs_rel_types (rel_type), ancestor_rel_type varchar(100) not null constraint grp_el_idx_ancstr_rel_type_ck check (ancestor_rel_type in ('composition_rel','membership_rel')), constraint group_element_index_pk primary key (element_id, group_id, rel_id) ) GO GO create index group_elem_idx_group_idx on group_element_index (group_id) GO create index group_elem_idx_element_idx on group_element_index (element_id) GO create index group_elem_idx_rel_id_idx on group_element_index (rel_id) GO create index group_elem_idx_container_idx on group_element_index (container_id) GO create index group_elem_idx_rel_type_idx on group_element_index (rel_type) GO create view group_element_map as select group_id , element_id , rel_id , container_id , rel_type , ancestor_rel_type from group_element_index GO create view group_component_map as select group_id , element_id as component_id, rel_id , container_id , rel_type from group_element_map WHERE ancestor_rel_type = 'composition_rel' GO create view group_member_map as select group_id , element_id as member_id, rel_id , container_id , rel_type from group_element_map WHERE ancestor_rel_type = 'membership_rel' GO create view group_approved_member_map as select gm.group_id , gm.member_id , gm.rel_id , gm.container_id , gm.rel_type from group_member_map gm, membership_rels mr WHERE gm.rel_id = mr.rel_id and mr.member_state = 'approved' GO create view group_distinct_member_map as select DISTINCT group_id , member_id from group_approved_member_map GO create view group_component_index as select * from group_component_map GO create view group_member_index as select * from group_member_map GO CREATE FUNCTION group_contains_p(@group_contains_p__group_id integer, @group_contains_p__component_id integer, @group_contains_p__rel_id integer) RETURNS boolean AS BEGIN declare @map__group_id int IF @group_contains_p__group_id = @group_contains_p__component_id BEGIN RETURN 't' END ELSE BEGIN IF @group_contains_p__rel_id is null BEGIN DECLARE cursor_1 CURSOR FOR select group_id from group_component_map WHERE component_id = @group_contains_p__component_id and group_id = container_id OPEN cursor_1 FETCH NEXT FROM cursor_1 INTO @map__group_id WHILE (@@FETCH_STATUS = 0) BEGIN IF dbo.group_contains_p ( @group_contains_p__group_id , @map__group_id , null ) = 't' BEGIN RETURN 't' END --IF FETCH NEXT FROM cursor_1 INTO @map__group_id END --while CLOSE cursor_1 DEALLOCATE cursor_1 END ELSE BEGIN DECLARE cursor_2 CURSOR FOR select group_id from group_component_map WHERE component_id = @group_contains_p__component_id and rel_id = @group_contains_p__rel_id and group_id = container_id OPEN cursor_2 FETCH NEXT FROM cursor_2 INTO @map__group_id WHILE (@@FETCH_STATUS = 0) BEGIN IF dbo.group_contains_p ( @group_contains_p__group_id , @map__group_id , null ) = 't' BEGIN RETURN 't' END --IF FETCH NEXT FROM cursor_2 INTO @map__group_id END --while CLOSE cursor_2 DEALLOCATE cursor_2 END --IF RETURN 'f' END --IF RETURN NULL -- placeholder required by tsql END -- function GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@retval'. *******************************************/ exec acs_object_id_seq__nextval @ms_return_value = @retval OUTPUT insert into group_type_rels ( group_rel_type_id, rel_type, group_type ) values ( @retval , 'membership_rel' , 'group' ) GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@retval'. *******************************************/ exec acs_object_id_seq__nextval @ms_return_value = @retval OUTPUT insert into group_type_rels ( group_rel_type_id, rel_type, group_type ) values ( @retval , 'composition_rel' , 'group' ) GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@retval'. *******************************************/ /*** packages/acs-kernel/sql/postgresql/rel-segments-create.sql ******************************************/ CREATE PROC inline_0 @ms_return_value integer = 0 OUTPUT AS BEGIN EXEC dbo.acs_object_type__create_type 'rel_segment' , 'Relational Party Segment' , 'Relational Party Segments' , 'party' , 'rel_segments' , 'segment_id' , 'rel_segment' , 'f' , 'rel_segment' , 'rel_segment.name' RETURN 0 END -- stored proc GO dbo.inline_0 GO drop proc inline_0 GO create table rel_segments ( segment_id integer not null constraint rel_segments_segment_id_fk references parties (party_id) constraint rel_segments_pk primary key, segment_name varchar(230) not null, group_id integer not null constraint rel_segments_group_id_fk references groups (group_id), rel_type varchar(100) not null constraint rel_segments_rel_type_fk references acs_rel_types (rel_type), constraint rel_segments_grp_rel_type_uq unique(group_id, rel_type) ) GO GO create index rel_segments_rel_type_idx on rel_segments(rel_type) GO create view rel_segment_party_map as select rs.segment_id , gem.element_id as party_id, gem.rel_id , gem.rel_type , gem.group_id , gem.container_id , gem.ancestor_rel_type from rel_segments rs, group_element_map gem WHERE gem.group_id = rs.group_id and rs.rel_type in ( select o2.object_type from acs_object_types o1, acs_object_types o2 WHERE o1.object_type = gem.rel_type and o2.tree_sortkey <= o1.tree_sortkey and o1.tree_sortkey like ( o2.tree_sortkey + '%' ) ) GO create view rel_segment_distinct_party_map as select DISTINCT segment_id , party_id , ancestor_rel_type from rel_segment_party_map GO create view rel_segment_member_map as select segment_id , party_id as member_id, rel_id , rel_type , group_id , container_id from rel_segment_party_map WHERE ancestor_rel_type = 'membership_rel' GO create view rel_seg_approved_member_map as select rs.segment_id , gem.element_id as member_id, gem.rel_id , gem.rel_type , gem.group_id , gem.container_id from membership_rels mr, group_element_map gem, rel_segments rs WHERE rs.group_id = gem.group_id and rs.rel_type in ( select o2.object_type from acs_object_types o1, acs_object_types o2 WHERE o1.object_type = gem.rel_type and o2.tree_sortkey <= o1.tree_sortkey and o1.tree_sortkey like ( o2.tree_sortkey + '%' ) ) and mr.rel_id = gem.rel_id and mr.member_state = 'approved' GO create view rel_seg_distinct_member_map as select DISTINCT segment_id , member_id from rel_seg_approved_member_map GO create view party_member_map as select segment_id as party_id, member_id from rel_seg_distinct_member_map union select group_id as party_id, member_id from group_distinct_member_map union select party_id , party_id as member_id from parties GO create view party_approved_member_map as select DISTINCT segment_id as party_id, member_id from rel_seg_approved_member_map union select DISTINCT group_id as party_id, member_id from group_approved_member_map union select party_id , party_id as member_id from parties GO create view party_element_map as select DISTINCT group_id as party_id, element_id from group_element_map union select DISTINCT segment_id as party_id, party_id as element_id from rel_segment_party_map union select party_id , party_id as element_id from parties GO create view rel_segment_group_reltype_map as select s.segment_id , gcm.component_id as group_id, acs_rel_types.rel_type as rel_type from rel_segments s, ( select group_id , component_id from group_component_map UNION ALL select group_id , group_id as component_id from groups ) gcm, acs_rel_types WHERE s.group_id = gcm.group_id and s.rel_type in ( select o2.object_type from acs_object_types o1, acs_object_types o2 WHERE o1.object_type = acs_rel_types.rel_type and o2.tree_sortkey <= o1.tree_sortkey and o1.tree_sortkey like ( o2.tree_sortkey + '%' ) ) GO /*** packages/acs-kernel/sql/postgresql/rel-constraints-create.sql *******************************************/ CREATE PROC inline_0 @ms_return_value integer = 0 OUTPUT AS BEGIN EXEC dbo.acs_object_type__create_type 'rel_constraint' , 'Relational Constraint' , 'Relational Constraints' , 'acs_object' , 'rel_constraints' , 'constraint_id' , 'rel_constraint' , 'f' , null , null RETURN 0 END -- stored proc GO dbo.inline_0 GO drop proc inline_0 GO create table rel_constraints ( constraint_id integer constraint rel_constraints_pk primary key constraint rc_constraint_id_fk references acs_objects(object_id), constraint_name varchar(100) not null, rel_segment integer not null constraint rc_rel_segment_fk references rel_segments (segment_id), rel_side char(3) default 'two' not null constraint rc_rel_side_ck check (rel_side in ('one', 'two')), required_rel_segment integer not null constraint rc_required_rel_segment references rel_segments (segment_id), constraint rel_constraints_uq unique (rel_segment, rel_side, required_rel_segment) ) GO GO create index rel_constraint_req_rel_seg_idx on rel_constraints(required_rel_segment) GO create view constrained_rels1 as select rel.constraint_id , rel.constraint_name , r.rel_id , r.container_id , r.party_id , r.rel_type , rel.rel_segment , rel.rel_side , rel.required_rel_segment from rel_constraints rel, rel_segment_party_map r WHERE rel.rel_side = 'one' and rel.rel_segment = r.segment_id GO create view rel_constraints_violated_one as select c.* from constrained_rels1 c left outer join rel_segment_party_map rspm on ( rspm.segment_id = c.required_rel_segment and rspm.party_id = c.container_id ) WHERE rspm.party_id is null GO create view constrained_rels2 as select rel.constraint_id , rel.constraint_name , r.rel_id , r.container_id , r.party_id , r.rel_type , rel.rel_segment , rel.rel_side , rel.required_rel_segment from rel_constraints rel, rel_segment_party_map r WHERE rel.rel_side = 'two' and rel.rel_segment = r.segment_id GO create view rel_constraints_violated_two as select c.* from constrained_rels2 c left outer join rel_segment_party_map rspm on ( rspm.segment_id = c.required_rel_segment and rspm.party_id = c.party_id ) WHERE rspm.party_id is null GO create view rc_all_constraints as select group_rel_types.group_id , group_rel_types.rel_type , rel_constraints.rel_segment , rel_constraints.rel_side , required_rel_segment from rel_constraints , rel_segment_group_rel_type_map group_rel_types, rel_segments req_seg WHERE rel_constraints.rel_segment = group_rel_types.segment_id and rel_constraints.required_rel_segment = req_seg.segment_id GO create view rc_all_distinct_constraints as select DISTINCT group_id , rel_type , rel_segment , rel_side , required_rel_segment from rc_all_constraints GO create view rc_required_rel_segments as select DISTINCT group_id , rel_type , required_rel_segment from rc_all_constraints WHERE rel_side ='two' GO create view comp_or_member_rel_types as select object_type as rel_type from acs_object_types WHERE tree_sortkey like ( select o.tree_sortkey + '%' from acs_object_types o WHERE o.object_type = 'composition_rel' ) or tree_sortkey like ( select o.tree_sortkey + '%' from acs_object_types o WHERE o.object_type = 'membership_rel' ) GO create view group_rel_type_combos as select groups.group_id , comp_or_member_rel_types.rel_type from groups , comp_or_member_rel_types GO create view parties_in_required_segs as select required_segs.group_id , required_segs.rel_type , seg_parties.party_id , count ( * ) as num_matching_segs from rc_required_rel_segments required_segs, rel_segment_party_map seg_parties WHERE required_segs.required_rel_segment = seg_parties.segment_id group by required_segs.group_id , required_segs.rel_type , seg_parties.party_id GO create view total_num_required_segs as select group_id , rel_type , count ( * ) as total from rc_required_rel_segments group by group_id , rel_type GO create view rc_parties_in_required_segs as select parties_in_required_segs.group_id , parties_in_required_segs.rel_type , parties_in_required_segs.party_id from parties_in_required_segs , total_num_required_segs WHERE parties_in_required_segs.group_id = total_num_required_segs.group_id and parties_in_required_segs.rel_type = total_num_required_segs.rel_type and parties_in_required_segs.num_matching_segs = total_num_required_segs.total UNION ALL select group_rel_type_combos.group_id , group_rel_type_combos.rel_type , parties.party_id from ( rc_required_rel_segments right outer join group_rel_type_combos on ( rc_required_rel_segments.group_id = group_rel_type_combos.group_id and rc_required_rel_segments.rel_type = group_rel_type_combos.rel_type ) ), parties WHERE rc_required_rel_segments.group_id is null GO create view side_one_constraints as select required_segs.group_id , required_segs.rel_type , count ( * ) as num_satisfied from rc_all_constraints required_segs, rel_segment_party_map map WHERE required_segs.rel_side = 'one' and required_segs.required_rel_segment = map.segment_id and required_segs.group_id = map.party_id group by required_segs.group_id , required_segs.rel_type GO create view total_side_one_constraints as select group_id , rel_type , count ( * ) as total from rc_all_constraints WHERE rel_side = 'one' group by group_id , rel_type GO create view rc_all_constraints_view as select * from rc_all_constraints WHERE rel_side = 'one' GO create view rc_valid_rel_types as select side_one_constraints.group_id , side_one_constraints.rel_type from side_one_constraints , total_side_one_constraints WHERE side_one_constraints.group_id = total_side_one_constraints.group_id and side_one_constraints.rel_type = total_side_one_constraints.rel_type and side_one_constraints.num_satisfied = total_side_one_constraints.total UNION ALL select group_rel_type_combos.group_id , group_rel_type_combos.rel_type from rc_all_constraints_view right outer join group_rel_type_combos on ( rc_all_constraints_view.group_id = group_rel_type_combos.group_id and rc_all_constraints_view.rel_type = group_rel_type_combos.rel_type ) WHERE rc_all_constraints_view.group_id is null GO create view rc_violations_by_removing_rel as select r.rel_type as viol_rel_type, r.rel_id as viol_rel_id, r.object_id_one as viol_object_id_one, r.object_id_two as viol_object_id_two, s.rel_id , cons.constraint_id , cons.constraint_name , map.segment_id , map.party_id , map.group_id , map.container_id , map.ancestor_rel_type from acs_rels r, rel_segment_party_map map, rel_constraints cons, ( select s.segment_id , r.rel_id , r.object_id_two from rel_segments s, acs_rels r WHERE r.object_id_one = s.group_id and r.rel_type = s.rel_type ) s WHERE map.party_id = r.object_id_two and map.rel_id = r.rel_id and r.object_id_two = s.object_id_two and cons.rel_segment = map.segment_id and cons.required_rel_segment = s.segment_id GO create table rc_segment_required_seg_map ( rel_segment integer not null constraint rc_segment_required_rel_segment_fk references rel_segments (segment_id), rel_side char(3) not null constraint rc_segment_rel_side_ck check (rel_side in ('one', 'two')), required_rel_segment integer not null constraint rc_segment_required_rel_segment references rel_segments (segment_id), constraint rc_segment_required_seg_map_uq unique (rel_segment, rel_side, required_rel_segment) ) GO create index rc_segment_required_seg_idx on rc_segment_required_seg_map(required_rel_segment) GO create view rc_segment_dependency_levels as select rel_segment as segment_id, count ( * ) as dependency_level from rc_segment_required_seg_map WHERE rel_side = 'two' group by rel_segment GO /*** packages/acs-kernel/sql/postgresql/groups-body-create.sql *******************************************/ /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the trigger 'membership_rels_in_tr', because it does not exist in the system catalog. *******************************************/ drop trigger membership_rels_in_tr GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the procedure 'membership_rels_in_tr', because it does not exist in the system catalog. *******************************************/ drop proc membership_rels_in_tr GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the procedure 'membership_rels_in_tr', because it does not exist in the system catalog. *******************************************/ /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the procedure 'membership_rels_in_tr', because it does not exist in the system catalog. *******************************************/ /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the trigger 'composition_rels_in_tr', because it does not exist in the system catalog. *******************************************/ drop trigger composition_rels_in_tr GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the procedure 'composition_rels_in_tr', because it does not exist in the system catalog. *******************************************/ drop proc composition_rels_in_tr GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the procedure 'composition_rels_in_tr', because it does not exist in the system catalog. *******************************************/ /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the procedure 'composition_rels_in_tr', because it does not exist in the system catalog. *******************************************/ /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the procedure 'composition_rels_in_tr', because it does not exist in the system catalog. *******************************************/ /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the procedure 'composition_rels_in_tr', because it does not exist in the system catalog. *******************************************/ /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the procedure 'composition_rels_in_tr', because it does not exist in the system catalog. *******************************************/ /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the procedure 'composition_rels_in_tr', because it does not exist in the system catalog. ******************************************/ CREATE PROC composition_rel__new @new__rel_id integer, @rel_type varchar(8000), @object_id_one integer, @object_id_two integer, @creation_user integer, @creation_ip varchar(8000), @ms_return_value integer = 0 OUTPUT AS BEGIN declare @v_rel_id integer EXEC dbo.acs_rel__new @new__rel_id , @rel_type , @object_id_one , @object_id_two , @object_id_one , @creation_user , @creation_ip , @ms_return_value = @v_rel_id OUTPUT insert into composition_rels ( rel_id ) values ( @v_rel_id ) SET @ms_return_value = @v_rel_id RETURN @ms_return_value END -- stored proc GO CREATE PROC composition_rel__new_2 @object_id_one integer, @object_id_two integer, @ms_return_value integer = 0 OUTPUT AS BEGIN SET @ms_return_value = dbo.composition_rel__new ( null , 'composition_rel' , @object_id_one , @object_id_two , null , null ) RETURN @ms_return_value END -- stored proc GO CREATE PROC composition_rel__delete @rel_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN EXEC dbo.acs_rel__delete @rel_id RETURN 0 END -- stored proc GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Line 7: Incorrect syntax near '.'. *******************************************/ CREATE FUNCTION composition_rel__check_path_exists_p(@component_id integer, @container_id integer) RETURNS boolean AS BEGIN declare @row__r.object_id_one as parent_id int IF @component_id = @container_id BEGIN RETURN 't' END --IF DECLARE cursor_1 CURSOR FOR select r.object_id_one as parent_id from acs_rels r, composition_rels c WHERE r.rel_id = c.rel_id and r.object_id_two = @component_id OPEN cursor_1 FETCH NEXT FROM cursor_1 INTO @row__r.object_id_one as parent_id WHILE (@@FETCH_STATUS = 0) BEGIN IF dbo.composition_rel__check_path_exists_p ( row.parent_id , @container_id ) = 't' BEGIN RETURN 't' END --IF FETCH NEXT FROM cursor_1 INTO @row__r.object_id_one as parent_id END --while CLOSE cursor_1 DEALLOCATE cursor_1 RETURN 'f' RETURN NULL -- placeholder required by tsql END -- function GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Line 7: Incorrect syntax near '.'. *******************************************/ /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Line 59: Incorrect syntax near '+'. *******************************************/ CREATE PROC composition_rel__check_representation @check_representation__rel_id integer, @ms_return_value boolean = OUTPUT AS BEGIN declare @result boolean declare @row__object_id_one int declare @component_id int declare @row__object_id_two int declare @container_id int SET @result = 't' IF dbo.acs_object__check_representation ( @check_representation__rel_id ) = 'f' BEGIN SET @result = 'f' END --IF select @container_id = object_id_one , @component_id = object_id_two from acs_rels WHERE rel_id = @check_representation__rel_id IF dbo.composition_rel__check_index ( @component_id , @container_id ) = 'f' BEGIN SET @result = 'f' END --IF DECLARE cursor_1 CURSOR FOR select * from group_component_index WHERE rel_id = @check_representation__rel_id OPEN cursor_1 FETCH NEXT FROM cursor_1 INTO @row__object_id_one , @row__object_id_two WHILE (@@FETCH_STATUS = 0) BEGIN IF dbo.composition_rel__check_path_exists_p ( row.component_id , row.group_id ) = 'f' BEGIN SET @result = 'f' EXEC dbo.acs_log__error 'composition_rel.check_representation' , 'Extraneous row in group_component_index: ' + 'group_id = ' + rw.group_id + ', ' + 'component_id = ' + row.component_id + ', ' + 'rel_id = ' + row.rel_id + ', ' + 'container_id = ' + row.container_id + '.' END --IF FETCH NEXT FROM cursor_1 INTO @row__object_id_one , @row__object_id_two END --while CLOSE cursor_1 DEALLOCATE cursor_1 SET @ms_return_value = @result RETURN @ms_return_value END -- stored proc GO CREATE PROC membership_rel__new @new__rel_id integer, @rel_type varchar(8000), @object_id_one integer, @object_id_two integer, @new__member_state varchar(8000), @creation_user integer, @creation_ip varchar(8000), @ms_return_value integer = 0 OUTPUT AS BEGIN declare @v_rel_id integer EXEC dbo.acs_rel__new @new__rel_id , @rel_type , @object_id_one , @object_id_two , @object_id_one , @creation_user , @creation_ip , @ms_return_value = @v_rel_id OUTPUT insert into membership_rels ( rel_id, member_state ) values ( @v_rel_id , @new__member_state ) SET @ms_return_value = @v_rel_id RETURN @ms_return_value END -- stored proc GO CREATE PROC membership_rel__new_2 @object_id_one integer, @object_id_two integer, @ms_return_value integer = 0 OUTPUT AS BEGIN SET @ms_return_value = dbo.membership_rel__new ( null , 'membership_rel' , @object_id_one , @object_id_two , 'approved' , null , null ) RETURN @ms_return_value END -- stored proc GO CREATE PROC membership_rel__delete @rel_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN EXEC dbo.acs_rel__delete @rel_id RETURN 0 END -- stored proc GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Line 11: Incorrect syntax near '('. *******************************************/ CREATE PROC membership_rel__check_index @check_index__group_id integer, @check_index__member_id integer, @check_index__container_id integer, @ms_return_value boolean = OUTPUT AS BEGIN declare @result boolean declare @n_rows integer declare @row__count ( * ) varchar(8000) select @n_rows = count ( * ) from group_member_index WHERE group_id = @check_index__group_id and member_id = @check_index__member_id and container_id = @check_index__container_id IF @n_rows = 0 BEGIN SET @result = 'f' EXEC dbo.acs_log__error 'membership_rel.check_representation' , 'Row missing from group_member_index: ' + 'group_id = ' + @check_index__group_id + ', ' + 'member_id = ' + @check_index__member_id + ', ' + 'container_id = ' + @check_index__container_id + '.' END --IF DECLARE cursor_1 CURSOR FOR select r.object_id_one as container_id from acs_rels r, composition_rels c WHERE r.rel_id = c.rel_id and r.object_id_two = @check_index__group_id OPEN cursor_1 FETCH NEXT FROM cursor_1 INTO @row__count ( * ) WHILE (@@FETCH_STATUS = 0) BEGIN IF dbo.membership_rel__check_index ( row.container_id , @check_index__member_id , @check_index__container_id ) = 'f' BEGIN SET @result = 'f' END --IF FETCH NEXT FROM cursor_1 INTO @row__count ( * ) END --while CLOSE cursor_1 DEALLOCATE cursor_1 SET @ms_return_value = @result RETURN @ms_return_value END -- stored proc GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Line 7: Incorrect syntax near '.'. *******************************************/ CREATE PROC membership_rel__check_representation @check_representation__rel_id integer, @ms_return_value boolean = OUTPUT AS BEGIN declare @row__r.object_id_two int declare @result boolean declare @row__r.object_id_one int declare @member_id int declare @group_id int SET @result = 't' IF dbo.acs_object__check_repesentation ( @check_representation__rel_id ) = 'f' BEGIN SET @result = 'f' END --IF select @group_id = r.object_id_one , @member_id = r.object_id_two from acs_rels r, membership_rels m WHERE r.rel_id = m.rel_id and m.rel_id = @check_representation__rel_id IF dbo.membership_rel__check_index ( @group_id , @member_id , @group_id ) = 'f' BEGIN SET @result = 'f' END --IF DECLARE cursor_1 CURSOR FOR select * from group_member_index WHERE rel_id = @check_representation__rel_id OPEN cursor_1 FETCH NEXT FROM cursor_1 INTO @row__r.object_id_one , @row__r.object_id_two WHILE (@@FETCH_STATUS = 0) BEGIN IF dbo.composition_rel__check_path_exists_p ( row.container_id , row.group_id ) = 'f' BEGIN SET @result = 'f' EXEC dbo.acs_log__error 'membership_rel.check_representation' , 'Extra row in group_member_index: ' + 'group_id = ' + row.group_id + ', ' + 'member_id = ' + row.member_id + ', ' + 'container_id = ' + row.container_id + '.' END --IF FETCH NEXT FROM cursor_1 INTO @row__r.object_id_one , @row__r.object_id_two END --while CLOSE cursor_1 DEALLOCATE cursor_1 SET @ms_return_value = @result RETURN @ms_return_value END -- stored proc GO CREATE PROC acs_group__new @new__group_id integer, @new__object_type varchar(8000), @new__creation_date varchar(50), @new__creation_user integer, @new__creation_ip varchar(8000), @new__email varchar(8000), @new__url varchar(8000), @new__group_name varchar(8000), @new__join_policy varchar(8000), @new__context_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN declare @v_join_policy varchar(8000) declare @v_group_type_exists_p integer declare @v_group_id int declare @retval int EXEC dbo.party__new @new__group_id , @new__object_type , @new__creation_date , @new__creation_user , @new__creation_ip , @new__email , @new__url , @new__context_id , @ms_return_value = @v_group_id OUTPUT SET @v_join_policy = @new__join_policy IF @v_join_policy is null or @v_join_policy = '' BEGIN select @v_group_type_exists_p = count ( * ) from group_types WHERE group_type = @new__object_type IF @v_group_type_exists_p = 1 BEGIN select @v_join_policy = default_join_policy from group_types WHERE group_type = @new__object_type END ELSE BEGIN SET @v_join_policy = 'open' END --IF END --IF insert into groups ( group_id, group_name, join_policy ) values ( @v_group_id , @new__group_name , @v_join_policy ) insert into group_rels ( group_rel_id, group_id, rel_type ) exec acs_object_id_seq__nextval @ms_return_value = @retval OUTPUT select @retval , @v_group_id , g.rel_type from group_type_rels g WHERE g.group_type = @new__object_type SET @ms_return_value = @v_group_id RETURN @ms_return_value END -- stored proc GO CREATE PROC acs_group__new_1 @gname varchar(8000), @ms_return_value integer = 0 OUTPUT AS BEGIN SET @ms_return_value = dbo.acs_group__new ( null , 'group' , getdate() , null , null , null , null , @gname , null , null ) RETURN @ms_return_value END -- stored proc GO CREATE FUNCTION acs_group__name(@name__group_id integer) RETURNS varchar AS BEGIN declare @name__group_name varchar ( 200 ) select @name__group_name = group_name from groups WHERE group_id = @name__group_id RETURN CONVERT (varchar, @name__group_name ) RETURN NUL -- placeholder required by tsql END -- function GO CREATE FUNCTION acs_group__member_p(@party_id integer) RETURNS boolean AS BEGIN RETURN 't' RETURN NULL -- placeholder required by tsql END -- function GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Line 8: Incorrect syntax near '.'. *******************************************/ CREATE PROC acs_group__check_representation @group_id integer, @ms_return_value boolean = OUTPUT AS BEGIN declare @res boolean declare @memb__m.rel_id int declare @comp__c.rel_id int SET @res = 't' EXEC dbo.acs_log__notice 'acs_group.check_representation' , 'Running check_representation on group ' + @group_id IF dbo.acs_object__check_representation ( @group_id ) = 'f' BEGIN SET @res = 'f' END --IF DECLARE cursor_1 CURSOR FOR select c.rel_id from acs_rels r, composition_rels c WHERE r.rel_id = c.rel_id and r.object_id_one = @group_id OPEN cursor_1 FETCH NEXT FROM cursor_1 INTO @comp__c.rel_id WHILE (@@FETCH_STATUS = 0) BEGIN IF dbo.composition_rel__check_representation ( comp.rel_id ) = 'f' BEGIN SET @res = 'f' END --IF FETCH NEXT FROM cursor_1 INTO @comp__c.rel_id END --while CLOSE cursor_1 DEALLOCATE cursor_1 DECLARE cursor_2 CURSOR FOR select m.rel_id from acs_rels r, membership_rels m WHERE r.rel_id = m.rel_id and r.object_id_one = @group_id OPEN cursor_2 FETCH NEXT FROM cursor_2 INTO @memb__m.rel_id WHILE (@@FETCH_STATUS = 0) BEGIN IF dbo.membership_rel__check_representation ( memb.rel_id ) = 'f' BEGIN SET @res = 'f' END --IF FETCH NEXT FROM cursor_2 INTO @memb__m.rel_id END --while CLOSE cursor_2 DEALLOCATE cursor_2 EXEC dbo.acs_log__notice 'acs_group.check_representation' , 'Done running check_representation on group ' + @group_id SET @ms_return_value = @res RETURN @ms_return_value END -- stored proc GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Line 8: Incorrect syntax near '.'. *******************************************/ /*** packages/acs-kernel/sql/postgresql/rel-segments-body-create.sql *******************************************/ CREATE PROC rel_segment__new @new__segment_id integer, @object_type varchar(8000), @creation_date varchar(50), @creation_user integer, @creation_ip varchar(8000), @email varchar(8000), @url varchar(8000), @new__segment_name varchar(8000), @new__group_id integer, @new__rel_type varchar(8000), @context_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN declare @v_segment_id int EXEC dbo.party__new @new__segment_id , @object_type , @creation_date , @creation_user , @creation_ip , @email , @url , @context_id , @ms_return_value = @v_segment_id OUTPUT insert into rel_segments ( segment_id, segment_name, group_id, rel_type ) values ( @v_segment_id , @new__segment_name , @new__group_id , @new__rel_type ) SET @ms_return_value = @v_segment_id RETURN @ms_return_value END -- stored proc GO CREATE PROC rel_segment__delete @delete__segment_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN declare @row__constraint_id int DECLARE cursor_1 CURSOR FOR select constraint_id from rel_constraints WHERE rel_segment = @delete__segment_id OPEN cursor_1 FETCH NEXT FROM cursor_1 INTO @row__constraint_id WHILE (@@FETCH_STATUS = 0) BEGIN EXEC dbo.rel_constraint__delete @row__constraint_id FETCH NEXT FROM cursor_1 INTO @row__constraint_id END --while CLOSE cursor_1 DEALLOCATE cursor_1 EXEC dbo.party__delete @delete__segment_id RETURN 0 END -- stored proc GO CREATE FUNCTION rel_segment__get(@get__group_id integer, @get__rel_type varchar(8000)) RETURNS integer AS BEGIN declare @v_segment_id int select @v_segment_id = min ( segment_id ) from rel_segments WHERE group_id = @get__group_id and rel_type = @get__rel_type RETURN CONVERT (integer, @v_segment_id ) RETURN NULL -- placeholder required by tsql END -- function GO CREATE PROC rel_segment__get_or_new @gid integer, @typ varchar(8000), @ms_return_value integer = 0 OUTPUT AS BEGIN SET @ms_return_value = dbo.rel_segment__get_or_new ( @gid , @typ , null ) RETURN @ms_return_value END -- stored proc GO CREATE PROC rel_segment__get_or_new_3 @get_or_new__group_id integer, @get_or_new__rel_type varchar(8000), @segment_name varchar(8000), @ms_return_value integer = 0 OUTPUT AS BEGIN declare @v_segment_id int declare @v_segment_name varchar(8000) SET @v_segment_id = dbo.rel_segment__get ( @get_or_new__group_id , @get_or_new__rel_type ) IF @v_segment_id is null BEGIN IF @segment_name is not null BEGIN SET @v_segment_name = @segment_name END ELSE BEGIN select @v_segment_name = groups.group_name + ' - ' + acs_object_types.pretty_name + ' segment' from groups , acs_object_types WHERE groups.group_id = @get_or_new__group_id and acs_object_types.object_type = @get_or_new__rel_type END --IF EXEC dbo.rel_segment__new null , 'rel_segment' , getdate , null , null , null , null , @v_segment_name , @get_or_new__group_id , @get_or_new__rel_type , @get_or_new__group_id , @ms_return_value = @v_segment_id OUTPUT END --IF SET @ms_return_value = @v_segment_id RETURN @ms_return_value END -- stored proc GO CREATE FUNCTION rel_segment__name(@name__segment_id integer) RETURNS varchar AS BEGIN declare @name__segment_name varchar ( 200 ) select @name__segment_name = segment_name from rel_segments WHERE segment_id = @name__segment_id RETURN CONVERT (varchar, @name__segment_name ) RETURN NULL -- placeholder required by tsql END -- function GO /*** packages/acs-kernel/sql/postgresql/rel-constraints-body-create.sql *******************************************/ CREATE PROC rel_constraint__new @nam varchar(8000), @sid1 integer, @side varchar(8000), @sid2 integer, @ms_return_value integer = 0 OUTPUT AS BEGIN SET @ms_return_value = dbo.rel_constraint__new ( null , 'rel_constraint' , @nam , @sid1 , @side , @sid2 , null , null , null ) RETURN @ms_return_value END -- stored proc GO CREATE PROC rel_constraint__new_9 @new__constraint_id integer, @new__constraint_type varchar(8000), @new__constraint_name varchar(8000), @new__rel_segment integer, @new__rel_side char, @new__required_rel_segment integer, @new__context_id integer, @new__creation_user integer, @new__creation_ip varchar(8000), @ms_return_value integer = 0 OUTPUT AS BEGIN declare @v_constraint_id int EXEC dbo.acs_object__new @new__constraint_id , @new__constraint_type , getdate , @new__creation_user , @new__creation_ip , @new__context_id , @ms_return_value = @v_constraint_id OUTPUT insert into rel_constraints ( constraint_id, constraint_name, rel_segment, rel_side, required_rel_segment ) values ( @v_constraint_id , @new__constraint_name , @new__rel_segment , @new__rel_side , @new__required_rel_segment ) SET @ms_return_value = @v_constraint_id RETURN @ms_return_value END -- stored proc GO CREATE PROC rel_constraint__elete @constraint_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN EXEC dbo.acs_object__delete @constraint_id RETURN 0 END -- stored proc GO CREATE FUNCTION rel_constraint__get_constraint_id(@get_constraint_id__rel_segment integer, @get_constraint_id__rel_side char, @get_constraint_id__required_rel_segment integer) RETURNS integer AS BEGIN declare @v_constraint_id int select @v_constraint_id = constraint_id from rel_constraints WHERE rel_segment = @get_constraint_id__rel_segment and rel_side = @get_constraint_id__rel_side and required_rel_segment = @get_constraint_id__required_rel_segment RETURN CONVERT (integer, @v_constraint_id ) RETURN NULL -- placeholder required by tsql END -- function GO CREATE FUNCTION rel_constraint__violation(@violation__rel_id integer) RETURNS varchar AS BEGIN declare @v_error varchar(8000) declare @constraint_violated__constraint_name varchar(8000) declare @constraint_violated__constraint_id int SET @v_error = null DECLARE cursor_1 CURSOR FOR select TOP 1 constraint_id , constraint_name from rel_constraints_violated_one WHERE rel_id = @violation__rel_id OPEN cursor_1 FETCH NEXT FROM cursor_1 INTO @constraint_violated__constraint_id , @constraint_violated__constraint_name WHILE (@@FETCH_STATUS = 0) BEGIN SET @v_error = coalesce ( @v_error , '' ) + 'Relational Constraint Violation: ' + @constraint_violated__constraint_name + ' (constraint_id=' + @constraint_violated__constraint_id + '). ' RETURN CONVERT (varchar, @v_error ) FETCH NEXT FROM cursor_1 INTO @constraint_violated__constraint_id , @constraint_violated__constraint_name END --while CLOSE cursor_1 DEALLOCATE cursor_1 DECLARE cursor_2 CURSOR FOR select TOP 1 constraint_id , constraint_name from rel_constraints_violated_two WHERE rel_id = @violation__rel_id OPEN cursor_2 FETCH NEXT FROM cursor_2 INTO @constraint_violated__constraint_id , @constraint_violated__constraint_name WHILE (@@FETCH_STATUS = 0) BEGIN SET @v_error = coalesce ( @v_error , '' ) + 'Relational Constraint Violation: ' + @constraint_violated__constraint_name + ' (constraint_id=' + @constraint_violated__constraint_id + '). ' RETURN CONVERT (varchar, @v_error ) FETCH NEXT FROM cursor_2 INTO @constraint_violated__constraint_id , @constraint_violated__constraint_name END --while CLOSE cursor_2 DEALLOCATE cursor_2 RETURN CONVERT (varchar, @v_error ) RETURN NULL -- placeholder required by tsql END -- function GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'constraint_violated' does not match with a table name or alias name used in the query. *******************************************/ CREATE FUNCTION rel_constraint__violation_if_removed(@violation_if_removed__rel_id integer) RETURNS varchar AS BEGIN declare @constraint_violated__1 varchar(8000) declare @v_error varchar(8000) declare @v_count integer SET @v_error = null select @v_count = 1 WHERE exists ( select 1 from rc_violations_by_removing_rel r WHERE r.rel_id = @violation_if_removed__rel_id ) IF @v_count > 0 BEGIN DECLARE cursor_1 CURSOR FOR select constraint_id , constraint_name from rc_violations_by_removing_rel r WHERE r.rel_id = @violation_if_removed__rel_id OPEN cursor_1 FETCH NEXT FROM cursor_1 INTO @constraint_violated__1 WHILE (@@FETCH_STATUS = 0) BEGIN SET @v_error = @v_error + 'Relational Constraint Violation: ' + constraint_violated.constraint_name + ' (constrain_id=' + constraint_violated.constraint_id + '). ' FETCH NEXT FROM cursor_1 INTO @constraint_violated__1 END --while CLOSE cursor_1 DEALLOCATE cursor_1 END --IF RETURN CONVERT (varchar, @v_error ) RETURN NULL -- placeholder required by tsql END -- function GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'constraint_violated' does not match with a table name or alias name used in the query. *******************************************/ /*** packages/acs-kernel/sql/postgresql/acs-permissions-create.sql *******************************************/ create table acs_privileges ( privilege varchar(100) not null constraint acs_privileges_pk primary key, pretty_name varchar(100), pretty_plural varchar(100) ) GO GO create table acs_privilege_hierarchy ( privilege varchar(100) not null constraint acs_priv_hier_priv_fk references acs_privileges (privilege), child_privilege varchar(100) not null constraint acs_priv_hier_child_priv_fk references acs_privileges (privilege), constraint acs_privilege_hierarchy_pk primary key (privilege, child_privilege) ) GO GO create index acs_priv_hier_child_priv_idx on acs_privilege_hierarchy (child_privilege) GO create table acs_privilege_hierarchy_index ( privilege varchar(100) not null constraint acs_priv_hier_ndx_priv_fk references acs_privileges (privilege), child_privilege varchar(100) not null constraint acs_priv_hier_ndx_child_priv_fk references acs_privileges (privilege), tree_sortkey varchar(4000) ) GO create index priv_hier_sortkey_idx on acs_privilege_hierarchy_index (tree_sortkey) GO CREATE PROC acs_privilege__create_privilege @create_privilege__privilege varchar(8000), @create_privilege__pretty_name varchar(8000), @create_privilege__pretty_plural varchar(8000), @ms_return_value integer = 0 OUTPUT AS BEGIN insert into acs_privileges ( privilege, pretty_name, pretty_plural ) values ( @create_privilege__privilege , @create_privilege__pretty_name , @create_privilege__pretty_plural ) RETURN 0 END -- stored proc GO CREATE PROC acs_privilege__create_privilege_1 @create_privilege__privilege varchar(8000), @ms_return_value integer = 0 OUTPUT AS BEGIN SET @ms_return_value = dbo.acs_privilege__create_privilege ( @create_privilege__privilege , null , null ) RETURN @ms_return_value END -- stored proc GO CREATE PROC acs_privilege__drop_privilege @drop_privilege__privilege varchar(8000), @ms_return_value integer = 0 OUTPUT AS BEGIN delete from acs_privileges WHERE privilege = @drop_privilege__privilege RETURN 0 END -- stored proc GO CREATE PROC acs_privilege__add_child @add_child__privilege varchar(8000), @add_child__child_privilege varchar(8000), @ms_return_value integer = 0 OUTPUT AS BEGIN insert into acs_privilege_hierarchy ( privilege, child_privilege ) values ( @add_child__privilege , @add_child__child_privilege ) RETURN 0 END -- stored proc GO CREATE PROC acs_privilege__remove_child @remove_child__privilege varchar(8000), @remove_child__child_privilege varchar(8000), @ms_return_value integer = 0 OUTPUT AS BEGIN delete from acs_privilege_hierarchy WHERE privilege = @remove_child__privilege and child_privilege = @remove_child__child_privilege RETURN 0 END -- stored proc GO create table acs_permissions ( object_id integer not null constraint acs_permissions_on_what_id_fk references acs_objects (object_id), grantee_id integer not null constraint acs_permissions_grantee_id_fk references parties (party_id), privilege varchar(100) not null constraint acs_permissions_priv_f references acs_privileges (privilege), constraint acs_permissions_pk primary key (object_id, grantee_id, privilege) ) GO GO create index acs_permissions_grantee_idx on acs_permissions (grantee_id) GO create index acs_permissions_privilege_idx on acs_permissions (privilege) GO create view acs_privilege_descendant_map as select p1.privilege , p2.privilege as descendant from acs_privileges p1, acs_privileges p2 WHERE exists ( select h2.child_privilege from acs_privilege_hierarchy_index h1, acs_privilege_hierarchy_index h2 WHERE h1.privilege = p1.privilege and h2.privilege = p2.privilege and h2.tree_sortkey like h1.tree_sortkey + '%' ) GO create view acs_permissions_all as select op.object_id , p.grantee_id , p.privilege from acs_object_paths op, acs_permissions p WHERE op.ancestor_id = p.object_id GO create view acs_object_grantee_priv_map as select a.object_id , a.grantee_id , m.descendant as privilege from acs_permissions_all a, acs_privilege_descendant_map m WHERE a.privilege = m.privilege GO create view acs_object_party_privilege_map as select ogpm.object_id , gmm.member_id as party_id, ogpm.privilege from acs_object_grantee_priv_map ogpm, group_approved_member_map gmm WHERE ogpm.grantee_id = gmm.group_id union select ogpm.object_id , rsmm.member_id as party_id, ogpm.privilege from acs_object_grantee_priv_map ogpm, rel_seg_approved_member_map rsmm WHERE ogpm.grantee_id = rsmm.segment_id union select object_id , grantee_id as party_id, privilege from acs_object_grantee_priv_map union select object_id , u.user_id as party_id, privilege from acs_object_grantee_priv_map m, users u WHERE m.grantee_id = -1 union select object_id , 0 as party_id, privilege from acs_object_grantee_priv_map WHERE grantee_id = -1 GO create view acs_grantee_party_map as select -1 as grantee_id, 0 as party_id union all select -1 as grantee_id, user_id as party_id from users union all select party_id as grantee_id, party_id from parties union all select segment_id as grantee_id, member_id from rel_seg_approved_member_map union all select group_id as grantee_id, member_id as party_id from group_approved_member_map GO create view all_object_party_privilege_map as select op.object_id , pdm.descendant as privilege, gpm.party_id as party_id from acs_object_paths op, acs_permissions p, acs_privilege_descendant_map pdm, acs_grantee_party_map gpm WHERE op.ancestor_id = p.object_id and pdm.privilege = p.privilege and gpm.grantee_id = p.grantee_id GO create table acs_permissions_lock ( lck integer ) GO GO CREATE FUNCTION acs_permission__permission_p(@permission_p__object_id integer, @permission_p__party_id integer, @permission_p__privilege varchar(8000)) RETURNS boolean AS BEGIN declare @exists_p boolean select @exists_p = 1 WHERE exists ( select 1 from acs_object_grantee_priv_map ogpm, group_approved_member_map gmm WHERE object_id = @permission_p__object_id and gmm.member_id = @permission_p__party_id and privilege = @permission_p__privilege and ogpm.grantee_id = gmm.group_id ) IF @@ROWCOUNT > 0 BEGIN RETURN 't' END --IF select @exists_p = 1 WHERE exists ( select 1 from acs_object_grantee_priv_map ogpm, rel_seg_approved_member_map rsmm WHERE object_id = @permission_p__object_id and rsmm.member_id = @permission_p__party_id and privilege = @perission_p__privilege and ogpm.grantee_id = rsmm.segment_id ) IF @@ROWCOUNT > 0 BEGIN RETURN 't' END --IF select @exists_p = 1 WHERE exists ( select 1 from acs_object_grantee_priv_map WHERE object_id = @permission_p__object_id and grantee_id = @permission_p__party_id and privilege = @permission_p__privilege ) IF @@ROWCOUNT > 0 BEGIN RETURN 't' END --IF select @exists_p = 1 WHERE exists ( select 1 from acs_object_grantee_priv_map m, users u WHERE object_id = @permission_p__object_id and u.user_id = @permission_p__party_id and privilege = @permission_p__privilege and m.grantee_id = -1 ) IF @@ROWCOUNT > 0 BEGIN RETURN 't' END --IF select @exists_p = 1 WHERE exists ( select 1 from acs_object_grantee_priv_map WHERE object_id = @permission_p__object_id and 0 = @permission_p__party_id and privilege = @permission_p__privilege and grantee_id = -1 ) IF @@ROWCOUNT > 0 BEGIN RETURN 't' END ELSE BEGIN RETURN 'f' END --IF RETURN NULL -- placeholder required by tsql END -- function GO /*** packages/acs-kernel/sql/postgresql/security-create.sql *******************************************/ create table sec_session_properties ( session_id integer constraint sec_session_prop_session_id_nn not null, module varchar(50) constraint sec_session_prop_module_nn not null, property_name varchar(50) constraint sec_session_prop_prop_name_nn not null, property_value text, secure_p boolean, last_hit integer constraint sec_session_date_nn not null, primary key(session_id, module, property_name) ) GO GO create index sec_property_names on sec_session_properties(property_name) GO create table secret_tokens ( token_id integer constraint secret_tokens_token_id_pk primary key, token char(40), datetime datetime ) GO GO CREATE TABLE t_sec_security_token_id_seq ( sequence int identity(1,1), dummy bit default 0 ); GO CREATE PROC sec_security_token_id_seq__nextval @ms_return_value int = 0 OUTPUT as INSERT INTO t_sec_security_token_id_seq VALUES (default) SET @ms_return_value = @@IDENTITY RETURN @ms_return_value GO CREATE TABLE t_sec_id_seq ( sequence int identity(1,1), dummy bit default 0 ); GO CREATE PROC sec_id_seq__nextval @ms_return_value int = 0 OUTPUT as INSERT INTO t_sec_id_seq VALUES (default) SET @ms_return_value = @@IDENTITY RETURN @ms_return_value GO /*** packages/acs-kernel/sql/postgresql/journal-create.sql *******************************************/ CREATE PROC inline_0 @ms_return_value integer = 0 OUTPUT AS BEGIN EXEC dbo.acs_object_type__create_type 'journal_entry' , 'Journal Entry' , 'Journal Entries' , 'acs_object' , 'journal_entries' , 'journal_id' , 'journal_entry' , 'f' , null , null RETURN 0 END -- stored proc GO dbo.inline_0 GO drop proc inline_0 GO create table journal_entries ( journal_id integer constraint journal_entries_journal_id_fk references acs_objects (object_id) constraint journal_entries_pk primary key, object_id integer constraint journal_entries_object_fk references acs_objects , action varchar(100), action_pretty text, msg text ) GO GO create index journal_entries_object_idx on journal_entries (object_id) GO CREATE PROC journal_entry__new @new__journal_id integer, @new__object_id integer, @new__action varchar(8000), @new__action_pretty varchar(8000), @new__creation_date varchar(50), @new__creation_user integer, @new__creation_ip varchar(8000), @new__msg varchar(8000), @ms_return_value integer = 0 OUTPUT AS BEGIN declare @v_journal_id int EXEC dbo.acs_object__new @new__journal_id , 'journal_entry' , @new__creation_date , @new__creation_user , @new__creation_ip , @new__object_id , @ms_return_value = @v_journal_id OUTPUT insert into journal_entries ( journal_id, object_id, action, action_pretty, msg ) values ( @v_journal_id , @new__object_id , @new__action , @new__action_pretty , @new__msg ) SET @ms_return_value = @v_journal_id RETURN @ms_return_value END -- stored proc GO CREATE PROC journal_entry__delete @delete__journal_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN delete from journal_entries WHERE journal_id = @delete__journal_id EXEC dbo.acs_object__delete @delete__journal_id RETURN 0 END -- stored proc GO CREATE PROC journal_entry__delete_for_object @delete_for_object__object_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN declare @journal_rec__journal_id int DECLARE cursor_1 CURSOR FOR select journal_id from journal_entries WHERE object_id = @delete_for_object__object_id OPEN cursor_1 FETCH NEXT FROM cursor_1 INTO @journal_rec__journal_id WHILE (@@FETCH_STATUS = 0) BEGIN EXEC dbo.journal_entry__delete @journal_rec__journal_id FETCH NEXT FROM cursor_1 INTO @journal_rec__journal_id END --while CLOSE cursor_1 DEALLOCATE cursor_1 RETURN 0 END -- stored proc GO /*** packages/acs-kernel/sql/postgresql/site-nodes-create.sql *******************************************/ CREATE PROC inline_0 @ms_return_value integer = 0 OUTPUT AS BEGIN declare @dummy integer EXEC dbo.acs_object_type__create_type 'site_node' , 'Site Node' , 'Site Nodes' , 'acs_object' , 'site_nodes' , 'node_id' , 'site_node' , 'f' , null , null RETURN 0 END -- stored proc GO dbo.inline_0 GO drop proc inline_0 GO create table site_nodes ( node_id integer constraint site_nodes_node_id_fk references acs_objects (object_id) constraint site_nodes_node_id_pk primary key, parent_id integer constraint site_nodes_parent_id_fk references site_nodes (node_id), name varchar(100) constraint site_nodes_name_ck check (name not like '%/%'), constraint site_nodes_un unique (parent_id, name), directory_p boolean not null, pattern_p boolean default 'f' not null, object_id integer constraint site_nodes_object_id_fk references acs_objects (object_id), tree_sortkey varchar(4000) ) GO GO create index site_nodes_object_id_idx on site_nodes (object_id) GO create index site_nodes_tree_skey_idx on site_nodes (tree_sortkey) GO CREATE PROC site_node__delete @delete__node_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN delete from site_nodes WHERE node_id = @delete__node_id EXEC dbo.acs_object__delete @delete__node_id RETURN 0 END -- stored proc GO /*** packages/acs-kernel/sql/postgresql/apm-create.sql *******************************************/ create table apm_package_types ( package_key varchar(100) constraint apm_package_types_p_key_pk primary key, pretty_name varchar(100) constraint apm_package_types_pretty_n_nn not null constraint apm_package_types_pretty_n_un unique, pretty_plural varchar(100) constraint apm_package_types_pretty_pl_un unique, package_uri varchar(1500) constraint apm_packages_types_p_uri_nn not null constraint apm_packages_types_p_uri_un unique, package_type varchar(300) constraint apm_packages_pack_type_ck check (package_type in ('apm_application', 'apm_service')), spec_file_path varchar(1500), spec_file_mtime integer, initial_install_p boolean default 'f' not null, singleton_p boolean default 'f' not null ) GO GO CREATE PROC inline_0 @ms_return_value integer = 0 OUTPUT AS BEGIN EXEC dbo.acs_object_type__create_type 'apm_package' , 'Package' , 'Packages' , 'acs_object' , 'APM_PACKAGES' , 'package_id' , 'apm_package' , 'f' , 'apm_package_types' , 'apm_package.name' RETURN 0 END -- stored proc GO dbo.inline_0 GO drop proc inline_0 GO CREATE PROC inline_1 @ms_return_value integer = 0 OUTPUT AS BEGIN declare @attr_id int EXEC dbo.acs_attribute__create_attribute 'apm_package' , 'package_key' , 'string' , 'Package Key' , 'Package Keys' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_package' , 'package_uri' , 'string' , 'Package URI' , 'Package URIs' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_package' , 'spec_file_path' , 'string' , 'Specification File Path' , 'Specification File Paths' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_package' , 'spec_file_mtime' , 'number' , 'Specification File Modified Time' , 'Specification File Modified Times' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_package' , 'initial_install_p' , 'boolean' , 'Initial Install' , 'Initial Installs' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_package' , 'singleton_p' , 'boolean' , 'Singleton' , 'Singletons' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT RETURN 0 END -- stored proc GO dbo.inline_1 GO drop proc inline_1 GO create table apm_packages ( package_id integer constraint apm_packages_package_id_fk references acs_objects(object_id) constraint apm_packages_pack_id_pk primary key, package_key varchar(100) constraint apm_packages_package_key_fk references apm_package_types(package_key), instance_name varchar(300) constraint apm_packages_inst_name_nn not null, enabled_p boolean default 'f' ) GO GO create index apm_packages_package_key_idx on apm_packages (package_key) GO create table apm_package_versions ( version_id integer constraint apm_package_vers_id_pk primary key constraint apm_package_vers_id_fk references acs_objects(object_id), package_key varchar(100) constraint apm_package_vers_pack_key_nn not null constraint apm_package_vers_pack_key_fk references apm_package_types(package_key), version_name varchar(100) constraint apm_package_vers_ver_name_nn not null, version_uri varchar(1500) constraint apm_package_vers_ver_uri_nn not null constraint apm_package_vers_ver_uri_un unique, summary varchar(3000), description_format varchar(100) constraint apm_package_vers_desc_for_ck check (description_format in ('text/html', 'text/plain')), description text, release_date datetime, vendor varchar(500), vendor_uri varchar(1500), enabled_p boolean default 'f' constraint apm_package_vers_enabled_p_nn not null, installed_p boolean default 'f' constraint apm_package_vers_inst_p_nn not null, tagged_p boolean default 'f' constraint apm_package_vers_tagged_p_nn not null, imported_p boolean default 'f' constraint apm_package_vers_imp_p_nn not null, data_model_loaded_p boolean default 'f' constraint apm_package_vers_dml_p_nn not null, cvs_import_results text, activation_date datetime, deactivation_date datetime, item_id integer, content_length integer, distribution_uri varchar(1500), distribution_date datetime, constraint apm_package_vers_id_name_un unique(package_key, version_name) ) GO GO CREATE PROC inline_2 @ms_return_value integer = 0 OUTPUT AS BEGIN declare @attr_id int EXEC dbo.acs_object_type__create_type 'apm_package_version' , 'Package Version' , 'Package Versions' , 'acs_object' , 'APM_PACKAGE_VERSIONS' , 'version_id' , 'APM_PACKAGE_VERSION' , 'f' , null , null , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_package_version' , 'package_key' , 'string' , 'Package Key' , 'Package Keys' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_package_version' , 'version_name' , 'string' , 'Version Name' , 'Version Names' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_package_version' , 'version_uri' , 'string' , 'Version URI' , 'Version URIs' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_package_version' , 'summary' , 'string' , 'Summary' , 'Summaries' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_package_version' , 'description_format' , 'string' , 'Description Format' , 'Description Formats' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_package_version' , 'description' , 'string' , 'Description' , 'Descriptions' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_package_version' , 'vendor' , 'string' , 'Vendor' , 'Vendors' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_package_version' , 'vendor_uri' , 'string' , 'Vendor URI' , 'Vendor URIs' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_package_version' , 'enabled_p' , 'string' , 'Enabled' , 'Enabled' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_package_version' , 'activation_date' , 'date' , 'Activation Date' , 'Activation Dates' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_package_version' , 'deactivation_date' , 'string' , 'Deactivation Date' , 'Deactivation Dates' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_package_version' , 'distribution_uri' , 'string' , 'Distribution URI' , 'Distribution URIs' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_package_version' , 'distribution_date' , 'date' , 'Distribution Date' , 'Distribution Dates' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT RETURN 0 END -- stored proc GO dbo.inline_2 GO drop proc inline_2 GO create table apm_package_owners ( version_id integer constraint apm_package_owners_ver_id_fk references apm_package_versions , owner_uri varchar(1500), owner_name varchar(200) constraint apm_package_owners_name_nn not null, sort_key integer ) GO GO create index apm_pkg_owners_version_idx on apm_package_owners (version_id) GO create view apm_package_version_info as select v.package_key , t.package_uri , t.pretty_name , t.singleton_p , t.initial_install_p , v.version_id , v.version_name , v.version_uri , v.summary , v.description_format , v.description , v.release_date , v.vendor , v.vendor_uri , v.enabled_p , v.installed_p , v.tagged_p , v.imported_p , v.data_model_loaded_p , v.activation_date , v.deactivation_date , coalesce ( v.content_length , 0 ) as tarball_length, distribution_uri , distribution_date from apm_package_types t, apm_package_versions v WHERE v.package_key = t.package_key GO create view apm_enabled_package_versions as select * from apm_package_version_info WHERE enabled_p = 't' GO create table apm_package_file_types ( file_type_key varchar(50) constraint apm_package_file_types_pk primary key, pretty_name varchar(200) constraint apm_package_file_types_name_nn not null ) GO GO CREATE PROC inline_3 @ms_return_value integer = 0 OUTPUT AS BEGIN insert into apm_package_file_types ( file_type_key, pretty_name ) values ( 'documentation' , 'Documentation' ) insert into apm_package_file_types ( file_type_key, pretty_name ) values ( 'tcl_procs' , 'Tcl procedure library' ) insert into apm_package_file_types ( file_type_key, pretty_name ) values ( 'tcl_init' , 'Tcl initialization' ) insert into apm_package_file_types ( file_type_key, pretty_name ) values ( 'tcl_util' , 'Tcl utility script' ) insert into apm_package_file_types ( file_type_key, pretty_name ) values ( 'content_page' , 'Content page' ) insert into apm_package_file_types ( file_type_key, pretty_name ) values ( 'package_spec' , 'Package specification' ) insert into apm_package_file_types ( file_type_key, pretty_name ) values ( 'data_model' , 'Data model' ) inse