--
-- The root table for the acs object heirarchy.  It all starts here folks.
-- 
CREATE TABLE acs_objects (
	object_id	 integer PRIMARY KEY NOT NULL,
	object_type	 varchar(100) NOT NULL,
	--
	--  Title of the object if applicable.
	--  Please note that in mid-term this column will replace all
	--  titles or object_names of package specific tables.
	-- 
	title	 varchar(1000) DEFAULT NULL::character varying,
	--
	--  Which package instance this object belongs to.
	--  Please note that in mid-term this column will replace all
	--  package_ids of package specific tables.
	-- 
	package_id	 integer,
	--
	--  The context_id column points to an object that provides a context for
	--  this object. Often this will reflect an observed hierarchy in a site,
	--  for example a bboard message would probably list a bboard topic as
	--  it's context, and a bboard topic might list a sub-site as it's
	--  context. Whenever we ask a question of the form "can user X perform
	--  action Y on object Z", the acs security model will defer to an
	--  object's context if there is no information about user X's
	--  permission to perform action Y on object Z.
	-- 
	context_id	 integer,
	security_inherit_p	 bool DEFAULT true NOT NULL,
	--
	--  Who created the object; may be null since objects can be created by
	--  automated processes
	-- 
	creation_user	 integer,
	creation_date	 timestamptz DEFAULT now() NOT NULL,
	creation_ip	 varchar(50),
	last_modified	 timestamptz DEFAULT now() NOT NULL,
	--
	--  Who last modified the object
	-- 
	modifying_user	 integer,
	modifying_ip	 varchar(50),
	tree_sortkey	 varbit(-5) NOT NULL,
	max_child_sortkey	 varbit(-5),
	CONSTRAINT acs_objects_context_id_fk REFERENCES acs_objects (),
	CONSTRAINT acs_objects_creation_user_fk REFERENCES users (),
	CONSTRAINT acs_objects_modifying_user_fk REFERENCES users (),
	CONSTRAINT acs_objects_object_type_fk REFERENCES acs_object_types () ON DELETE SET NULL,
	CONSTRAINT acs_objects_package_id_fk REFERENCES apm_packages ()
);

CREATE UNIQUE INDEX acs_objects_context_object_un ON acs_objects (context_id, object_id);
CREATE INDEX acs_objects_creation_user_idx ON acs_objects (creation_user);
CREATE INDEX acs_objects_modify_user_idx ON acs_objects (modifying_user);
CREATE INDEX acs_objects_object_type_idx ON acs_objects (object_type);
CREATE INDEX acs_objects_package_object_idx ON acs_objects (package_id, object_id);
CREATE INDEX acs_objects_title_idx ON acs_objects (title);
CREATE UNIQUE INDEX acs_objects_tree_sortkey_un ON acs_objects (tree_sortkey);

CREATE TRIGGER acs_objects_context_id_del_tr BEFORE DELETE FOR EACH ROW EXECUTE PROCEDURE acs_objects_context_id_del_tr (disabled)
CREATE TRIGGER acs_objects_context_id_in_tr AFTER INSERT FOR EACH ROW EXECUTE PROCEDURE acs_objects_context_id_in_tr (disabled)
CREATE TRIGGER acs_objects_context_id_up_tr AFTER UPDATE FOR EACH ROW EXECUTE PROCEDURE acs_objects_context_id_up_tr (disabled)
CREATE TRIGGER acs_objects_insert_tr BEFORE INSERT FOR EACH ROW EXECUTE PROCEDURE acs_objects_insert_tr (disabled)
CREATE TRIGGER acs_objects_last_mod_update_tr BEFORE UPDATE FOR EACH ROW EXECUTE PROCEDURE acs_objects_last_mod_update_tr (disabled)
CREATE TRIGGER acs_objects_mod_ip_insert_tr BEFORE INSERT FOR EACH ROW EXECUTE PROCEDURE acs_objects_mod_ip_insert_tr (disabled)
CREATE TRIGGER acs_objects_update_tr AFTER UPDATE FOR EACH ROW EXECUTE PROCEDURE acs_objects_update_tr (disabled)

-- Tables with foreign keys that refer to acs_objects:
--iv_default_objects(iv_default_objects_invoice_fk)
--ams_lists(ams_lists_list_id_fk)
--apm_package_versions(apm_package_vers_id_fk)
--telecom_numbers(telecom_number_id_fk)
--workflows(wfs_workflow_id_fk)
--cr_child_rels(cr_child_rels_rel_id_fk)
--acs_data_links(acs_data_links_obj_one_fk)
--acs_events(acs_events_fk)
--auth_authorities(auth_authorities_search_impl_id_fk)
--site_node_object_mappings(snom_object_id_fk)
--acs_object_context_index(acs_obj_context_idx_anc_id_fk)
--acs_rels(acs_rels_object_id_one_fk)
--apm_parameters(apm_parameters_parameter_id_fk)
--contact_complaint_track(contact_complaint_track_complaint_object_id_fk)
--logger_project_var_map(logger_project_var_map_pid_fk)
--calendars(calendars_calendar_id_fk)
--journal_entries(journal_entries_object_id_fk)
--ams_attribute_values(ams_attribute_values_object_id_fk)
--acs_named_objects(acs_named_objs_object_id_fk)
--cr_item_rels(cr_item_rels_rel_obj_fk)
--ams_option_types(ams_options_option_id_fk)
--acs_mail_log(acs_mail_log_owner_id_fk)
--auth_authorities(auth_authorities_pwd_impl_fk)
--category_trees(cat_trees_tree_id_fk)
--logger_projects(logger_projects_pid_fk)
--acs_rels(acs_rels_rel_id_fk)
--iv_default_objects(iv_default_objects_offer_fk)
--cr_item_rels(cr_item_rels_rel_id_fk)
--auth_authorities(auth_authorities_procdoc_ipl_fk)
--contact_message_items(contact_message_items_owner_id_fk)
--logger_entries(logger_entries_mid_fk)
--acs_activity_object_map(acs_act_obj_mp_object_id_fk)
--acs_rels(acs_rels_object_id_two_fk)
--ams_attribute_items(ams_attribute_items_ams_attribute_id_fk)
--notification_requests(notification_requests_id_fk)
--cr_revisions(cr_revisions_revision_id_fk)
--logger_projections(logger_projections_pid_fk)
--attachments(attachments_object_id_fk)
--notifications(notifications_notification_id_fk)
--iv_costs(im_costs_object_fk)
--acs_reference_repositories(arr_repository_id_fk)
--auth_authorities(auth_authorities_auth_id_fk)
--auth_authorities(auth_authorities_auth_impl_fk)
--postal_addresses(postal_address_id_fk)
--acs_sc_msg_types(acs_sc_msg_types_id_fk)
--logger_project_pkg_map(logger_project_pkg_map_pr_fk)
--template_demo_notes(template_demo_notes_note_id_fk)
--notifications(notifications_object_id_fk)
--ratings(ratings_rating_id_fk)
--iv_default_objects(iv_default_objects_offer_item_fk)
--journal_entries(journal_entries_journal_id_fk)
--parties(parties_party_id_fk)
--rel_constraints(rc_constraint_id_fk)
--acs_objects(acs_objects_context_id_fk)
--acs_permissions(acs_permissions_object_id_fk)
--acs_sc_contracts(acs_sc_contracts_id_fk)
--attachments(attachments_item_id_fk)
--acs_activities(acs_activities_fk)
--iv_default_objects(iv_default_objects_price_fk)
--site_nodes(site_nodes_object_id_fk)
--forums_forums(forums_forum_id_fk)
--apm_packages(apm_packages_package_id_fk)
--notifications(notifications_response_id_fk)
--acs_object_context_index(acs_obj_context_idx_obj_id_fk)
--logger_variables(logger_variables_pid_fk)
--ratings(ratings_object_id_fk)
--auth_authorities(auth_authorities_getdoc_ipl_fk)
--contact_message_log(contact_message_log_message_id_fk)
--notification_delivery_methods(notification_delivery_methods_fk)
--rss_gen_subscrs(rss_gen_subscrs_id_fk)
--general_objects(general_objects_object_id_fk)
--category_tree_map(cat_tree_map_object_id_fk)
--dtype_widget_templates(dtype_wdgt_tmpl_tid_fk)
--site_nodes(site_nodes_node_id_fk)
--iv_default_objects(iv_default_objects_payment_fk)
--acs_data_links(acs_data_links_obj_two_fk)
--workflow_cases(wf_cases_object_id_fk)
--contact_deleted_history(contact_deleted_history_object_id_fk)
--cr_items(cr_items_item_id_fk)
--site_nodes_selection(site_nodes_sel_id_fk)
--notification_intervals(notification_intervals_interval_id_fk)
--rating_dimensions(rating_dims_object_id_fk)
--syndication(syndication_object_id_fk)
--auth_authorities(auth_authorities_reg_impl_fk)
--contact_lists(contact_lists_list_id_fk)
--iv_default_objects(iv_default_objects_offer_title_fk)
--acs_sc_operations(acs_sc_operations_opid_fk)
--category_object_map(cat_object_map_object_id_fk)
--notification_replies(notif_repl_repl_id_fk)
--acs_attribute_values(acs_attr_values_obj_id_fk)
--iv_default_objects(iv_default_objects_invoice_item_fk)
--notification_requests(notifications_request_object_id_fk)
--categories(cat_category_id_fk)
--contact_searches(contact_searches_owner_id_fk)
--acs_magic_objects(acs_magic_objects_object_id_fk)
--cr_items(cr_items_parent_id_fk)
--iv_default_objects(iv_default_objects_list_fk)
--logger_entries(logger_entries_pid_fk)
--rating_aggregates(ratings_object_id_fk)
--workflows(wfs_object_id_fk)
--auth_driver_params(auth_driver_params_impl_id_fk)
--general_comments(general_comments_object_id_fk)
--iv_default_objects(iv_default_objects_cost_fk)
--apm_parameter_values(apm_parameter_values_fk)
--forums_messages(forums_message_id_fk)
--notification_replies(notif_repl_obj_id_fk)
--rss_gen_subscrs(rss_gen_subscrs_ctx_fk)
--xowiki_autonames(xowiki_autonames_parent_id_fkey)
--acs_sc_impls(acs_sc_impls_impl_id_fk)
--acs_mail_tracking_request(acs_mail_request_object_id_fk)
--notification_types(notification_types_type_id_fk)
--auth_authorities(auth_authorities_urinf_ipl_fk)
--bt_patches(bt_patches_pid_fk)
--contact_searches(contact_searches_id_fk)

-- Table size: 491,520 bytes
-- Table rows: 3,236

Tables: