/*** 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' ) insert into apm_package_file_types ( file_type_key, pretty_name ) values ( 'data_model_create' , 'Data model installation' ) insert into apm_package_file_types ( file_type_key, pretty_name ) values ( 'data_model_drop' , 'Data model deinstallation' ) insert into apm_package_file_types ( file_type_key, pretty_name ) values ( 'data_model_upgrade' , 'Data model upgrade' ) insert into apm_package_file_types ( file_type_key, pretty_name ) values ( 'java_code' , 'Java code' ) insert into apm_package_file_types ( file_type_key, pretty_name ) values ( 'java_archive' , 'Java archive' ) insert into apm_package_file_types ( file_type_key, pretty_name ) values ( 'query_file' , 'Query file' ) insert into apm_package_file_types ( file_type_key, pretty_name ) values ( 'template' , 'Template file' ) insert into apm_package_file_types ( file_type_key, pretty_name ) values ( 'shell' , 'Shell utility' ) insert into apm_package_file_types ( file_type_key, pretty_name ) values ( 'sqlj_code' , 'SQLJ library' ) RETURN 0 END -- stored proc GO dbo.inline_3 GO drop proc inline_3 GO create table apm_package_db_types ( db_type_key varchar(50) constraint apm_package_db_types_pk primary key, pretty_db_name varchar(200) constraint apm_package_db_types_name_nn not null ) GO GO create table apm_package_files ( file_id integer constraint apm_package_files_id_pk primary key, version_id integer constraint apm_package_files_ver_id_fk references apm_package_versions constraint apm_package_files_ver_id_nn not null, path varchar(1500) constraint apm_package_files_path_nn not null, file_type varchar(50) constraint apm_package_files_type_fk references apm_package_file_types, db_type varchar(50) constraint apm_package_files_db_type_fk references apm_package_db_types, constraint apm_package_files_un unique(version_id, path) ) GO GO create index apm_pkg_files_file_type_idx on apm_package_files (file_type) GO create index apm_pkg_files_db_type_idx on apm_package_files (db_type) GO create view apm_file_info as select f.* , p.package_key , 'packages/' + p.package_key + '/' + f.path as full_path from apm_package_files f, apm_package_versions v, apm_package_types p WHERE f.version_id = v.version_id and v.package_key = p.package_key GO create table apm_parameters ( parameter_id integer constraint apm_parameters_fk references acs_objects(object_id) constraint apm_parameters_pk primary key, package_key varchar(100) constraint apm_pack_param_pack_key_nn not null constraint apm_pack_param_type_fk references apm_package_types (package_key), parameter_name varchar(100) constraint apm_pack_params_name_nn not null, description varchar(2000), section_name varchar(200), datatype varchar(100) not null constraint apm_parameter_datatype_ck check(datatype in ('number', 'string')), default_value text, min_n_values integer default 1 not null constraint apm_paramters_min_n_ck check (min_n_values >= 0), max_n_values integer default 1 not null constraint apm_paramters_max_n_ck check (max_n_values >= 0), constraint apm_paramters_attr_name_un unique (parameter_name, package_key), constraint apm_paramters_n_values_ck check (min_n_values <= max_n_values) ) GO GO create index apm_parameters_package_idx on apm_parameters (package_key) GO create table apm_parameter_values ( value_id integer constraint apm_parameter_values_fk references acs_objects(object_id) constraint apm_parameter_values_pk primary key, package_id integer constraint apm_pack_values_obj_id_fk references apm_packages (package_id) , parameter_id integer constraint apm_pack_values_parm_id_fk references apm_parameters (parameter_id), attr_value text, constraint apm_parameter_values_un unique (package_id, parameter_id) ) GO GO create index apm_par_vals_parameter_idx on apm_parameter_values (parameter_id) GO CREATE PROC inline_4 @ms_return_value integer = 0 OUTPUT AS BEGIN declare @attr_id int EXEC dbo.acs_object_type__create_type 'apm_parameter' , 'Package Parameter' , 'Package Parameters' , 'acs_object' , 'APM_PARAMETERS' , 'parameter_id' , 'apm_parameter' , 'f' , null , null , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_parameter' , '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_parameter' , 'parameter_name' , 'string' , 'Parameter Name' , 'Parameter Name' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_parameter' , 'datatype' , 'string' , 'Datatype' , 'Datatypes' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_parameter' , 'default_value' , 'string' , 'Default Value' , 'Default Values' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_parameter' , 'min_n_values' , 'number' , 'Minimum Number of Values' , 'Minimum Numer of Values Settings' , null , null , 1 , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_parameter' , 'max_n_values' , 'string' , 'Maximum Number of Values' , 'Maximum Number of Values Settings' , null , null , 1 , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT RETURN 0 END -- stored proc GO dbo.inline_4 GO drop proc inline_4 GO CREATE PROC inline_5 @ms_return_value integer = 0 OUTPUT AS BEGIN declare @attr_id int EXEC dbo.acs_object_type__create_type 'apm_parameter_value' , 'APM Package Parameter Value' , 'APM Package Parameter Values' , 'acs_object' , 'apm_parameter_values' , 'value_id' , 'apm_parameter_value' , 'f' , null , null , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_parameter_value' , 'package_id' , 'number' , 'Package ID' , 'Package IDs' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_parameter_value' , 'parameter_id' , 'number' , 'Parameter ID' , 'Parameter IDs' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT EXEC dbo.acs_attribute__create_attribute 'apm_parameter_value' , 'attr_value' , 'string' , 'Parameter Value' , 'Parameter Values' , null , null , null , 1 , 1 , null , 'type_specific' , 'f' , @ms_return_value = @attr_id OUTPUT RETURN 0 END -- stored proc GO dbo.inline_5 GO drop proc inline_5 GO create table apm_package_dependencies ( dependency_id integer constraint apm_package_deps_id_pk primary key, version_id integer constraint apm_package_deps_version_id_fk references apm_package_versions constraint apm_package_deps_version_id_nn not null, dependency_type varchar(20) constraint apm_package_deps_type_nn not null constraint apm_package_deps_type_ck check(dependency_type in ('provides','requires')), service_uri varchar(1500) constraint apm_package_deps_uri_nn not null, service_version varchar(100) constraint apm_package_deps_ver_name_nn not null, constraint apm_package_deps_un unique(version_id, service_uri) ) GO GO create table apm_applications ( application_id integer constraint applications_application_id_fk references apm_packages(package_id) constraint applications_pk primary key ) GO GO create table apm_services ( service_id integer constraint services_service_id_fk references apm_packages(package_id) constraint services_pk primary key ) GO GO CREATE PROC inline_6 @ms_return_value integer = 0 OUTPUT AS BEGIN declare @dummy integer EXEC dbo.acs_object_type__create_type 'apm_application' , 'Application' , 'Applications' , 'apm_package' , 'apm_applications' , 'application_id' , 'apm_application' , 'f' , null , null , @ms_return_value = @dummy OUTPUT RETURN 0 END -- stored proc GO dbo.inline_6 GO drop proc inline_6 GO CREATE PROC inline_7 @ms_return_value integer = 0 OUTPUT AS BEGIN declare @dummy integer EXEC dbo.acs_object_type__create_type 'apm_service' , 'Service' , 'Services' , 'apm_package' , 'apm_services' , 'service_id' , 'apm_service' , 'f' , null , null , @ms_return_value = @dummy OUTPUT RETURN 0 END -- stored proc GO dbo.inline_7 GO drop proc inline_7 GO CREATE PROC apm__register_package @package_key varchar(8000), @pretty_name varchar(8000), @pretty_plural varchar(8000), @package_uri varchar(8000), @package_type varchar(8000), @initial_install_p boolean, @singleton_p boolean, @spec_file_path varchar(8000), @spec_file_mtime integer, @ms_return_value integer = 0 OUTPUT AS BEGIN EXEC dbo.apm_package_type__create_type @package_key , @pretty_name , @pretty_plural , @package_uri , @package_type , @initial_install_p , @singleton_p , @spec_file_path , @spec_file_mtime RETURN 0 END -- stored proc GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Parameter '@ms_return_value' has an invalid data type. *******************************************/ CREATE PROC apm__update_package @package_key varchar(8000), @pretty_name varchar(8000), @pretty_plural varchar(8000), @package_uri varchar(8000), @package_type varchar(8000), @initial_install_p boolean, @singleton_p boolean, @spec_file_path varchar(8000), @spec_file_mtime integer, @ms_return_value varchar = OUTPUT AS BEGIN SET @ms_return_value = dbo.apm_package_type__update_type ( @package_key , @pretty_name , @pretty_plural , @package_uri , @package_type , @initial_install_p , @singleton_p , @spec_file_path , @spec_file_mtime ) RETURN @ms_return_value END -- stored proc GO CREATE PROC apm__unregister_package @package_key varchar(8000), @cascade_p boolean, @ms_return_value integer = 0 OUTPUT AS BEGIN EXEC dbo.apm_package_type__drop_type @package_key , @cascade_p RETURN 0 END -- stored proc GO CREATE PROC apm__register_application @package_key varchar(8000), @pretty_name varchar(8000), @pretty_plural varchar(8000), @package_uri varchar(8000), @initial_install_p boolean, @singleton_p boolean, @spec_file_path varchar(8000), @spec_file_mtime integer, @ms_return_value integer = 0 OUTPUT AS BEGIN EXEC dbo.apm__register_package @package_key , @pretty_name , @pretty_plural , @package_uri , 'apm_application' , @initial_install_p , @singleton_p , @spec_file_path , @spec_file_mtime RETURN 0 END -- stored proc GO CREATE PROC apm__unregister_application @package_key varchar(8000), @cascade_p boolean, @ms_return_value integer = 0 OUTPUT AS BEGIN EXEC dbo.apm__unregister_package @package_key , @cascade_p RETURN 0 END -- stored proc GO CREATE PROC apm__register_service @package_key varchar(8000), @pretty_name varchar(8000), @pretty_plural varchar(8000), @package_uri varchar(8000), @initial_install_p boolean, @singleton_p boolean, @spec_file_path varchar(8000), @spec_file_mtime integer, @ms_return_value integer = 0 OUTPUT AS BEGIN EXEC dbo.apm__register_package @package_key , @pretty_name , @pretty_plural , @package_uri , 'apm_service' , @initial_install_p , @singleton_p , @spec_file_path , @spec_file_mtime RETURN 0 END -- stored proc GO CREATE PROC apm__unregister_service @package_key varchar(8000), @cascade_p boolean, @ms_return_value integer = 0 OUTPUT AS BEGIN EXEC dbo.apm__unregister_package @package_key , @cascade_p RETURN 0 END -- stored proc GO CREATE PROC apm__unregister_parameter @unregister_parameter__parameter_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN delete from apm_parameter_values WHERE parameter_id = @unregister_parameter__parameter_id delete from apm_parameters WHERE parameter_id = @unregister_parameter__parameter_id EXEC dbo.acs_object__delete @unregister_parameter__parameter_id RETURN 0 END -- stored proc GO CREATE FUNCTION apm__id_for_name(@id_for_name__parameter_name varchar(8000), @id_for_name__package_key varchar(8000)) RETURNS integer AS BEGIN declare @a_parameter_id int select @a_parameter_id = parameter_id from apm_parameters p WHERE p.parameter_name = @id_for_name__parameter_name and p.package_key = @id_for_name__package_key RETURN CONVERT (integer, @a_parameter_id ) RETURN NULL -- placeholder required by tsql END -- function GO CREATE FUNCTION apm__get_value(@get_value__parameter_id integer, @get_value__package_id integer) RETURNS varchar AS BEGIN declare @value varchar(8000) select @value = attr_value from apm_parameter_values v WHERE v.package_id = @get_value__package_id and parameter_id = @get_value__parameter_id RETURN CONVERT (varchar, @value ) RETURN NULL -- placeholder required by tsql END -- function GO CREATE FUNCTION apm__get_value_2(@get_value__package_id integer, @get_value__parameter_name varchar(8000)) RETURNS varchar AS BEGIN declare @v_parameter_id int select @v_parameter_id = package_key from apm_parameters WHERE parameter_name = @get_value__parameter_name and package_key = ( select package_key from apm_packages WHERE package_id = @get_value__package_id ) RETURN CONVERT (varchar, dbo.apm__get_value ( @v_parameter_id , @get_value__package_id ) ) RETURN NULL -- placeholder required by tsql END -- function GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Line 29: Incorrect syntax near '.'. *******************************************/ CREATE FUNCTION apm_package__initialize_parameters(@ip__package_id integer, @ip__package_key varchar(8000)) RETURNS integer AS BEGIN declare @cur_val__parameter_id int declare @v_value_id int declare @cur_val__default_value varchar(8000) DECLARE cursor_1 CURSOR FOR select parameter_id , default_value from apm_parameters WHERE package_key = @ip__package_key OPEN cursor_1 FETCH NEXT FROM cursor_1 INTO @cur_val__parameter_id , @cur_val__default_value WHILE (@@FETCH_STATUS = 0) BEGIN EXEC dbo.apm_parameter_value__new null , @ip__package_id , cur_val.parameter_id , cur_val.default_value , @ms_return_value = @v_value_id OUTPUT FETCH NEXT FROM cursor_1 INTO @cur_val__parameter_id , @cur_val__default_value END --while CLOSE cursor_1 DEALLOCATE cursor_1 RETURN 0 RETURN NULL -- placeholder required by tsql END -- function GO CREATE PROC apm_package__new @new__package_id integer, @new__instance_name varchar(8000), @new__package_key varchar(8000), @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_singleton_p integer declare @v_package_id int declare @v_package_type varchar(8000) declare @v_instance_name varchar(8000) declare @perform integer declare @v_num_instances integer SET @v_singleton_p = dbo.apm_package__singleton_p ( @new__package_key ) SET @v_num_instances = dbo.apm_package__num_instances ( @new__package_key ) IF @v_singleton_p = 1 and @v_num_instances >= 1 BEGIN select @v_package_id = package_id from apm_packages WHERE package_key = @new__package_key SET @ms_return_value = @v_package_id RETURN @ms_return_value END ELSE BEGIN EXEC dbo.acs_object__new @new__package_id , @new__object_type , @new__creation_date , @new__creation_user , @new__creation_ip , @new__context_id , @ms_return_value = @v_package_id OUTPUT IF @new__instance_name is null or @new__instance_name = '' BEGIN SET @v_instance_name = @new__package_key + ' ' + @v_package_id END ELSE BEGIN SET @v_instance_name = @new__instance_name END --IF select @v_package_type = package_type from apm_package_types WHERE package_key = @new__package_key insert into apm_packages ( package_id, package_key, instance_name ) values ( @v_package_id , @new__package_key , @v_instance_name ) IF @v_package_type = 'apm_application' BEGIN insert into apm_applications ( application_id ) values ( @v_package_id ) END ELSE BEGIN insert into apm_services ( service_id ) values ( @v_package_id ) END --IF SELECT @perform = dbo.apm_package__initialize_parameters ( @v_package_id , @new__package_key ) SET @ms_return_value = @v_package_id RETURN @ms_return_value END --IF END -- stored proc GO CREATE PROC apm_package__delete @delete__package_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN declare @cur_val__node_id int declare @cur_val__value_id int DECLARE cursor_1 CURSOR FOR select value_id from apm_parameter_values WHERE package_id = @delete__package_id OPEN cursor_1 FETCH NEXT FROM cursor_1 INTO @cur_val__value_id WHILE (@@FETCH_STATUS = 0) BEGIN EXEC dbo.apm_parameter_value__delete @cur_val__value_id FETCH NEXT FROM cursor_1 INTO @cur_val__value_id END --while CLOSE cursor_1 DEALLOCATE cursor_1 delete from apm_applications WHERE application_id = @delete__package_id delete from apm_services WHERE service_id = @delete__package_id delete from apm_packages WHERE package_id = @delete__package_id DECLARE cursor_2 CURSOR FOR select node_id from site_nodes WHERE object_id = @delete__package_id OPEN cursor_2 FETCH NEXT FROM cursor_2 INTO @cur_val__node_id WHILE (@@FETCH_STATUS = 0) BEGIN EXEC dbo.site_node__delete @cur_val__node_id FETCH NEXT FROM cursor_2 INTO @cur_val__node_id END --while CLOSE cursor_2 DEALLOCATE cursor_2 EXEC dbo.acs_object__delete @delete__package_id RETURN 0 END -- stored proc GO CREATE FUNCTION apm_package__initial_install_p(@initial_install_p__package_key varchar(8000)) RETURNS integer AS BEGIN declare @v_initial_install_p integer select @v_initial_install_p = 1 from apm_package_types WHERE package_key = @initial_install_p__package_key and initial_install_p = 't' IF @@ROWCOUNT = 0 BEGIN RETURN 0 END ELSE BEGIN RETURN CONVERT (integer, @v_initial_install_p ) END --IF RETURN NULL -- placeholder required by tsql END -- function GO CREATE FUNCTION apm_package__singleton_p(@singleton_p__package_key varchar(8000)) RETURNS integer AS BEGIN declare @v_singleton_p integer select @v_singleton_p = 1 from apm_package_types WHERE package_key = @singleton_p__package_key and singleton_p = 't' IF @@ROWCOUNT = 0 BEGIN RETURN 0 END ELSE BEGIN RETURN CONVERT (integer, @v_singleton_p ) END --IF RETURN NULL -- placeholder required by tsql END -- function GO CREATE FUNCTION apm_package__num_instances(@num_instances__package_key varchar(8000)) RETURNS integer AS BEGIN declare @v_num_instances integer select @v_num_instances = count ( * ) from apm_packages WHERE package_key = @num_instances__package_key IF @@ROWCOUNT = 0 BEGIN RETURN 0 END ELSE BEGIN RETURN CONVERT (integer, @v_num_instances ) END --IF RETURN NULL -- placeholder required by tsql END -- function GO CREATE FUNCTION apm_package__name(@name__package_id integer) RETURNS varchar AS BEGIN declare @v_result varchar(8000) select @v_result = instance_name from apm_packages WHERE package_id = @name__package_id RETURN CONVERT (varchar, @v_result ) RETURN NULL -- placeholder required by tsql END -- function GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'v' does not match with a table name or alias name used in the query. *******************************************/ CREATE FUNCTION apm_package__highest_version(@highest_version__package_key varchar(8000)) RETURNS integer AS BEGIN declare @v_version_id int select @v_version_id = max ( dbo.apm_package_version__sortable_version_name ( v.version_name ) ) from apm_package_version_info i WHERE dbo.apm_package_version__sortable_version_name ( version_name ) = ( select max ( dbo.apm_package_version__sortable_version_name ( v.version_name ) ) from apm_package_version_info v WHERE v.package_key = @highest_version__package_key ) and package_key = @highest_version__package_key IF @@ROWCOUNT = 0 BEGIN RETURN 0 END ELSE BEGIN RETURN CONVERT (integer, @v_version_id ) END --IF RETURN NULL -- placeholder required by tsql END -- function GO CREATE PROC apm_package_version__new @apm_pkg_ver__version_id integer, @apm_pkg_ver__package_key varchar(8000), @apm_pkg_ver__version_name varchar(8000), @apm_pkg_ver__version_uri varchar(8000), @apm_pkg_ver__summary varchar(8000), @apm_pkg_ver__description_format varchar(8000), @apm_pkg_ver__description varchar(8000), @apm_pkg_ver__release_date varchar(50), @apm_pkg_ver__vendor varchar(8000), @apm_pkg_ver__vendor_uri varchar(8000), @apm_pkg_ver__installed_p boolean, @apm_pkg_ver__data_model_loaded_p boolean, @ms_return_value integer = 0 OUTPUT AS BEGIN declare @retval int declare @v_version_id int IF @apm_pkg_ver__version_id = '' or @apm_pkg_ver__version_id is null BEGIN exec acs_object_id_seq__nextval @ms_return_value = @retval OUTPUT select @v_version_id = @retval END ELSE BEGIN SET @v_version_id = @apm_pkg_ver__version_id END --IF EXEC dbo.acs_object__new @v_version_id , 'apm_package_version' , getdate , null , null , null , @ms_return_value = @v_version_id OUTPUT insert into apm_package_versions ( version_id, package_key, version_name, version_uri, summary, description_format, description, release_date, vendor, vendor_uri, installed_p, data_model_loaded_p ) values ( @v_version_id , @apm_pkg_ver__package_key , @apm_pkg_ver__version_name , @apm_pkg_ver__version_uri , @apm_pkg_ver__summary , @apm_pkg_ver__description_format , @apm_pkg_ver__description , @apm_pkg_ver__release_date , @apm_pkg_ver__vendor , @apm_pkg_ver__vendor_uri , @apm_pkg_ver__installed_p , @apm_pkg_ver__data_model_loaded_p ) SET @ms_return_value = @v_version_id RETURN @ms_return_value END -- stored proc GO CREATE PROC apm_package_version__delete @delete__version_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN delete from apm_package_owners WHERE version_id = @delete__version_id delete from apm_package_files WHERE version_id = @delete__version_id delete from apm_package_dependencies WHERE version_id = @delete__version_id delete from apm_package_versions WHERE version_id = @delete__version_id EXEC dbo.acs_object__delete @delete__version_id RETURN 0 END -- stored proc GO CREATE PROC apm_package_version__copy @copy__version_id integer, @copy__new_version_id integer, @copy__new_version_name varchar(8000), @copy__new_version_uri varchar(8000), @ms_return_value integer = 0 OUTPUT AS BEGIN declare @retval int declare @v_version_id integer EXEC dbo.acs_object__new @copy__new_version_id , 'apm_package_version' , getdate , null , null , null , @ms_return_value = @v_version_id OUTPUT insert into apm_package_versions ( version_id, package_key, version_name, version_uri, summary, description_format, description, release_date, vendor, vendor_uri ) select @v_version_id , package_key , @copy__new_version_name , @copy__new_version_uri , summary , description_format , description , release_date , vendor , vendor_uri from apm_package_versions WHERE version_id = @copy__version_id insert into apm_package_dependencies ( dependency_id, version_id, dependency_type, service_uri, service_version ) exec acs_object_id_seq__nextval @ms_return_value = @retval OUTPUT select @retval , @v_version_id , dependency_type , service_uri , service_version from apm_package_dependencies WHERE version_id = @copy__version_id insert into apm_package_files ( file_id, version_id, path, file_type ) select @retval , @v_version_id , path , file_type from apm_package_files WHERE version_id = @copy__version_id insert into apm_package_owners ( version_id, owner_uri, owner_name, sort_key ) select @v_version_id , owner_uri , owner_name , sort_key from apm_package_owners WHERE version_id = @copy__version_id SET @ms_return_value = @v_version_id RETURN @ms_return_value END -- stored proc GO CREATE PROC apm_package_version__add_file @add_file__file_id integer, @add_file__version_id integer, @add_file__path varchar(8000), @add_file__file_type varchar(8000), @add_file__db_type varchar(8000), @ms_return_value integer = 0 OUTPUT AS BEGIN declare @retval int declare @v_file_exists_p integer declare @v_file_id int select @v_file_id = [file_id] from apm_package_files WHERE version_id = @add_file__version_id and path = @add_file__path IF @@ROWCOUNT = 0 BEGIN IF @add_file__file_id is null BEGIN exec acs_object_id_seq__nextval @ms_return_value = @retval OUTPUT select @v_file_id = @retval END ELSE BEGIN SET @v_file_id = @add_file__file_id END --IF insert into apm_package_files ( [file_id], version_id, path, file_type, db_type ) values ( @v_file_id , @add_file__version_id , @add_file__path , @add_file__file_type , @add_file__db_type ) END --IF SET @ms_return_value = @v_file_id RETURN @ms_return_value END -- stored proc GO CREATE PROC apm_package_version__remove_file @remove_file__version_id integer, @remove_file__path varchar(8000), @ms_return_value integer = 0 OUTPUT AS BEGIN delete from apm_package_files WHERE version_id = @remove_file__version_id and path = @remove_file__path RETURN 0 END -- stored proc GO CREATE PROC apm_package_version__add_interface @add_interface__interface_id integer, @add_interface__version_id integer, @add_interface__interface_uri varchar(8000), @add_interface__interface_version varchar(8000), @ms_return_value integer = 0 OUTPUT AS BEGIN declare @retval int declare @v_dep_id int IF @add_interface__interface_id is null BEGIN exec acs_object_id_seq__nextval @ms_return_value = @retval OUTPUT select @v_dep_id = @retval END ELSE BEGIN SET @v_dep_id = @add_interface__interface_id END --IF insert into apm_package_dependencies ( dependency_id, version_id, dependency_type, service_uri, service_version ) values ( @v_dep_id , @add_interface__version_id , 'provides' , @add_interface__interface_uri , @add_interface__interface_version ) SET @ms_return_value = @v_dep_id RETURN @ms_return_value END -- stored proc GO CREATE PROC apm_package_version__remove_interface @remove_interface__interface_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN delete from apm_package_dependencies WHERE dependency_id = @remove_interface__interface_id RETURN 0 END -- stored proc GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'interface_version'. *******************************************/ CREATE PROC apm_package_version__remove_interface_3 @remove_interface__interface_uri varchar(8000), @remove_interface__interface_version varchar(8000), @remove_interface__version_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN declare @v_dep_id int select @v_dep_id = dependency_id from apm_package_dependencies WHERE service_uri = @remove_interface__interface_uri and interface_version = @remove_interface__interface_version EXEC dbo.apm_package_version__remove_interface @v_dep_id RETURN 0 END -- stored proc GO CREATE PROC apm_package_version__add_dependency @add_dependency__dependency_id integer, @add_dependency__version_id integer, @add_dependency__dependency_uri varchar(8000), @add_dependency__dependency_version varchar(8000), @ms_return_value integer = 0 OUTPUT AS BEGIN declare @retval int declare @v_dep_id int IF @add_dependency__dependency_id is null BEGIN exec acs_object_id_seq__nextval @ms_return_value = @retval OUTPUT select @v_dep_id = @retval END ELSE BEGIN SET @v_dep_id = @add_dependency__dependency_id END --IF insert into apm_package_dependencies ( dependency_id, version_id, dependency_type, service_uri, service_version ) values ( @v_dep_id , @add_dependency__version_id , 'requires' , @add_dependency__dependency_uri , @add_dependency__dependency_version ) SET @ms_return_value = @v_dep_id RETURN @ms_return_value END -- stored proc GO CREATE PROC apm_package_version__remove_dependency @remove_dependency__dependency_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN delete from apm_package_dependencies WHERE dependency_id = @remove_dependency__dependency_id RETURN 0 END -- stored proc GO CREATE PROC apm_package_version__remove_dependency_3 @remove_dependency__dependency_uri varchar(8000), @remove_dependency__dependency_version varchar(8000), @remove_dependency__version_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN declare @v_dep_id int select @v_dep_id = dependency_id from apm_package_dependencies WHERE service_uri = @remove_dependency__dependency_uri and service_version = @remove_dependency__dependency_version EXEC dbo.apm_package_version__remove_dependency @v_dep_id RETURN 0 END -- stored proc GO CREATE FUNCTION apm_package_version__version_name_greater(@version_name_one varchar(8000), @version_name_two varchar(8000)) RETURNS integer AS BEGIN declare @a_order_b varchar ( 250 ) declare @a_order_a varchar ( 250 ) SET @a_order_a = dbo.apm_package_version__sortable_version_name ( @version_name_one ) SET @a_order_b = dbo.apm_package_version__sortable_version_name ( @version_name_two ) IF @a_order_a < @a_order_b BEGIN RETURN CONVERT (integer, -1 ) END ELSE BEGIN IF @a_order_a > @a_order_b BEGIN RETURN CONVERT (integer, 1 ) END --IF END --IF RETURN 0 RETURN NULL -- placeholder required by tsql END -- function GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]An insufficient number of arguments were supplied for the procedure or function dbo.instr. *******************************************/ CREATE FUNCTION apm_package_version__upgrade_p(@upgrade_p__path varchar(8000), @upgrade_p__initial_version_name varchar(8000), @upgrade_p__final_version_name varchar(8000)) RETURNS integer AS BEGIN declare @v_version_to varchar(8000) declare @v_tmp varchar(8000) declare @v_version_from varchar(8000) declare @v_pos1 integer declare @v_path varchar(8000) declare @v_pos2 integer SET @v_path = substring ( @upgrade_p__path , dbo.instr( @upgrade_p__path , '/' , -1 ) + 1 , datalength ( @upgrade_p__path ) ) SET @v_pos1 = charindex ( '.' , @v_path ) IF @v_pos1 > 0 and substring ( @v_path , @v_pos1 , datalength ( @v_path ) ) = '.sql' BEGIN SET @v_path = substring ( @v_path , 1 , @v_pos1 - 1 ) END --IF SET @v_pos1 = dbo.instr( @v_path , '-' , -1 , 2 ) SET @v_pos2 = dbo.instr( @v_path , '-' , -1 ) IF @v_pos1 = 0 or @v_pos2 = 0 BEGIN RETURN 0 END --IF SET @v_version_from = substring ( @v_path , @v_pos1 + 1 , @v_pos2 - @v_pos1 - 1 ) SET @v_version_to = substring ( @v_path , @v_pos2 + 1 , datalength ( @v_path ) ) IF dbo.apm_package_version__version_name_greater ( @upgrade_p__initial_version_name , @v_version_from ) <= 0 and dbo.apm_package_version__version_name_greater ( @upgrade_p__final_version_name , @v_version_to ) >= 0 BEGIN RETURN CONVERT (integer, 1 ) END --IF RETURN 0 RETURN NULL -- placeholder required by tsql END -- function GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]An insufficient number of arguments were supplied for the procedure or function dbo.instr. *******************************************/ CREATE PROC apm_package_type__create_type @create_type__package_key varchar(8000), @create_type__pretty_name varchar(8000), @create_type__pretty_plural varchar(8000), @create_type__package_uri varchar(8000), @create_type__package_type varchar(8000), @create_type__initial_install_p boolean, @create_type__singleton_p boolean, @create_type__spec_file_path varchar(8000), @create_type__spec_file_mtime integer, @ms_return_value integer = 0 OUTPUT AS BEGIN insert into apm_package_types ( package_key, pretty_name, pretty_plural, package_uri, package_type, spec_file_path, spec_file_mtime, initial_install_p, singleton_p ) values ( @create_type__package_key , @create_type__pretty_name , @create_type__pretty_plural , @create_type__package_uri , @create_type__package_type , @create_type__spec_file_path , @create_type__spec_file_mtime , @create_type__initial_install_p , @create_type__singleton_p ) RETURN 0 END -- stored proc GO CREATE PROC apm_package_type__drop_type @drop_type__package_key varchar(8000), @drop_type__cascade_p boolean, @ms_return_value integer = 0 OUTPUT AS BEGIN declare @cur_val__parameter_id int declare @cur_val__package_id int declare @cur_val__version_id int IF @drop_type__cascade_p = 't' BEGIN DECLARE cursor_1 CURSOR FOR select package_id from apm_packages WHERE package_key = @drop_type__package_key OPEN cursor_1 FETCH NEXT FROM cursor_1 INTO @cur_val__package_id WHILE (@@FETCH_STATUS = 0) BEGIN EXEC dbo.apm_package__delete @cur_val__package_id FETCH NEXT FROM cursor_1 INTO @cur_val__package_id END --while CLOSE cursor_1 DEALLOCATE cursor_1 DECLARE cursor_2 CURSOR FOR select parameter_id from apm_parameters WHERE package_key = @drop_type__package_key OPEN cursor_2 FETCH NEXT FROM cursor_2 INTO @cur_val__parameter_id WHILE (@@FETCH_STATUS = 0) BEGIN EXEC dbo.apm__unregister_parameter @cur_val__parameter_id FETCH NEXT FROM cursor_2 INTO @cur_val__parameter_id END --while CLOSE cursor_2 DEALLOCATE cursor_2 DECLARE cursor_3 CURSOR FOR select version_id from apm_package_versions WHERE package_key = @drop_type__package_key OPEN cursor_3 FETCH NEXT FROM cursor_3 INTO @cur_val__version_id WHILE (@@FETCH_STATUS = 0) BEGIN EXEC dbo.apm_package_version__delete @cur_val__version_id FETCH NEXT FROM cursor_3 INTO @cur_val__version_id END --while CLOSE cursor_3 DEALLOCATE cursor_3 END --IF delete from apm_package_types WHERE package_key = @drop_type__package_key RETURN 0 END -- stored proc GO CREATE FUNCTION apm_package_type__num_parameters(@num_parameters__package_key varchar(8000)) RETURNS integer AS BEGIN declare @v_count integer select @v_count = count ( * ) from apm_parameters WHERE package_key = @num_parameters__package_key RETURN CONVERT (integer, @v_count ) RETURN NULL -- placeholder required by tsql END -- function GO CREATE PROC apm_parameter_value__new @new__value_id integer, @new__package_id integer, @new__parameter_id integer, @new__attr_value varchar(8000), @ms_return_value integer = 0 OUTPUT AS BEGIN declare @v_value_id int EXEC dbo.acs_object__new @new__value_id , 'apm_parameter_value' , getdate , null , null , null , @ms_return_value = @v_value_id OUTPUT insert into apm_parameter_values ( value_id, package_id, parameter_id, attr_value ) values ( @v_value_id , @new__package_id , @new__parameter_id , @new__attr_value ) SET @ms_return_value = @v_value_id RETURN @ms_return_value END -- stored proc GO CREATE PROC apm_parameter_value__delete @delete__value_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN delete from apm_parameter_values WHERE value_id = @delete__value_id EXEC dbo.acs_object__delete @delete__value_id RETURN 0 END -- stored proc GO CREATE PROC apm_application__new @application_id integer, @instance_name varchar(8000), @package_key varchar(8000), @object_type varchar(8000), @creation_date varchar(50), @creation_user integer, @creation_ip varchar(8000), @context_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN declare @v_application_id integer EXEC dbo.apm_package__new @application_id , @instance_name , @package_key , @object_type , @creation_date , @creation_user , @creation_ip , @context_id , @ms_return_value = @v_application_id OUTPUT SET @ms_return_value = @v_application_id RETURN @ms_return_value END -- stored proc GO CREATE PROC apm_application__delete @delete__application_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN delete from apm_applications WHERE application_id = @delete__application_id EXEC dbo.apm_package__delete @delete__application_id RETURN 0 END -- stored proc GO CREATE PROC apm_service__new @service_id integer, @instance_name varchar(8000), @package_key varchar(8000), @object_type varchar(8000), @creation_date varchar(50), @creation_user integer, @creation_ip varchar(8000), @context_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN declare @v_service_id integer EXEC dbo.apm_package__new @service_id , @instance_name , @package_key , @object_type , @creation_date , @creation_user , @creation_ip , @context_id , @ms_return_value = @v_service_id OUTPUT SET @ms_return_value = @v_service_id RETURN @ms_return_value END -- stored proc GO CREATE PROC apm_service__delete @delete__service_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN delete from apm_services WHERE service_id = @delete__service_id EXEC dbo.apm_package__delete @delete__service_id RETURN 0 END -- stored proc GO /*** packages/acs-kernel/sql/postgresql/acs-create.sql *******************************************/ create table acs_magic_objects ( name varchar(100) constraint acs_magic_objects_pk primary key, object_id integer not null constraint acs_magic_objects_object_id_fk references acs_objects(object_id) ) GO GO create index acs_mo_object_id_idx on acs_magic_objects (object_id) GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Line 44: Incorrect syntax near '.'. *******************************************/ CREATE PROC acs__add_user @user_id integer, @object_type varchar(8000), @creation_date varchar(50), @creation_user integer, @creation_ip varchar(8000), @email varchar(8000), @url varchar(8000), @first_names varchar(8000), @last_name varchar(8000), @password char, @salt char, @password_question varchar(8000), @password_answer varchar(8000), @screen_name varchar(8000), @email_verified_p boolean, @member_state varchar(8000), @ms_return_value integer = 0 OUTPUT AS BEGIN declare @v_rel_id int declare @v_user_id int EXEC dbo.acs_user__new @user_id , @object_type , @creation_date , @creation_user , @creation_ip , @email , @url , @first_names , @last_name , @password , @salt , @password_question , @password_answer , @screen_name , @email_verified_p , null , @ms_return_value = @v_user_id OUTPUT EXEC dbo.membership_rel__new null , 'membership_rel' , dbo.acs__magic_object_id ( 'registered_users' ) , @v_user_id , @member_state , null , null , @ms_return_value = @v_rel_id OUTPUT EXEC dbo.acs_permission__grant_permission @v_user_id , @v_user_id , 'read' EXEC dbo.acs_permission__grant_permission @v_user_id , @v_user_id , 'write' SET @ms_return_value = @v_user_id RETURN @ms_return_value END -- stored proc GO CREATE PROC acs__remove_user @remove_user__user_id integer, @ms_return_value integer = 0 OUTPUT AS BEGIN delete from users WHERE user_id = @remove_user__user_id RETURN 0 END -- stored proc GO CREATE FUNCTION acs__magic_object_id(@magic_object_id__name varchar(8000)) RETURNS integer AS BEGIN declare @magic_object_id__object_id int select @magic_object_id__object_id = object_id from acs_magic_objects WHERE name = @magic_object_id__name RETURN CONVERT (integer, @magic_object_id__object_id ) RETURN NULL -- placeholder required by tsql END -- function GO create view registered_users as select p.email , p.url , pe.first_names , pe.last_name , u.* , mr.member_state from parties p, persons pe, users u, group_member_map m, membership_rels mr WHERE party_id = person_id and person_id = user_id and u.user_id = m.member_id and m.rel_id = mr.rel_id and m.group_id = dbo.acs__magic_object_id ( 'registered_users' ) and mr.member_state = 'approved' and u.email_verified_p = 't' GO create view cc_users as select o.* , pa.* , pe.* , u.* , mr.member_state , mr.rel_id from acs_objects o, parties pa, persons pe, users u, group_member_map m, membership_rels mr WHERE o.object_id = pa.party_id and pa.party_id = pe.person_id and pe.person_id = u.user_id and u.user_id = m.member_id and m.group_id = dbo.acs__magic_object_id ( 'registered_users' ) and m.rel_id = mr.rel_id and m.container_id = m.group_id GO CREATE PROC inline_0 @ms_return_value integer = 0 OUTPUT AS BEGIN declare @root_id integer EXEC dbo.acs_object__new 0 , 'acs_object' , getdate , null , null , null , @ms_return_value = @root_id OUTPUT insert into acs_magic_objects ( name, object_id ) values ( 'security_context_root' , 0 ) SET @ms_return_value = @root_id RETURN @ms_return_value END -- stored proc GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string. *******************************************/ dbo.inline_0 GO drop proc inline_0 GO dbo.acs_privilege__create_privilege 'read' , null , null GO dbo.acs_privilege__create_privilege 'write' , null , null GO dbo.acs_privilege__create_privilege 'create' , null , null GO dbo.acs_privilege__create_privilege 'delete' , null , null GO dbo.acs_privilege__create_privilege 'admin' , null , null GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the trigger 'acs_priv_hier_ins_del_tr', because it does not exist in the system catalog. *******************************************/ drop trigger acs_priv_hier_ins_del_tr GO dbo.acs_privilege__add_child 'admin' , 'read' GO dbo.acs_privilege__add_child 'admin' , 'write' GO dbo.acs_privilege__add_child 'admin' , 'create' GO dbo.acs_privilege__add_child 'admin' , 'delete' GO CREATE PROC inline_2 @ms_return_value integer = 0 OUTPUT AS BEGIN declare @v_object_id integer insert into acs_objects ( object_id, object_type ) values ( -1 , 'party' ) insert into parties ( party_id ) values ( -1 ) insert into acs_magic_objects ( name, object_id ) values ( 'the_public' , -1 ) RETURN 0 END -- stored proc GO dbo.inline_2 GO drop proc inline_2 GO CREATE PROC inline_3 @ms_return_value integer = 0 OUTPUT AS BEGIN declare @group_id integer EXEC dbo.acs_group__new -2 , 'group' , getdate , null , null , null , null , 'Registered Users' , null , null , @ms_return_value = @group_id OUTPUT insert into acs_magic_objects ( name, object_id ) values ( 'registered_users' , -2 ) RETURN 0 END -- stored proc GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'dbo.party__new'. *******************************************/ dbo.inline_3 GO drop proc inline_3 GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax near ')'. *******************************************/ dbo.acs_object__new -3 , 'acs_object' , getdate() , null , null , null GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'acs_magic_objects_object_id_fk'. The conflict occurred in database 'openacs', table 'acs_objects', column 'object_id'. *******************************************/ insert into acs_magic_objects ( name, object_id ) values ( 'default_context' , -3 ) GO /*** [Microsoft][ODBC SQL Server Driver][SQL Server]INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'acs_magic_objects_object_id_fk'. The conflict occurred in database 'openacs', table 'acs_objects', column 'object_id'. *******************************************/