Forum OpenACS Q&A: Purpose of acs_tables created by ACS in PostgreSQL

Hello everyone,

I have been searching the docs to find a explanation of all the acs_xxx
tables that are created in Postgres during the OpenACS installation.  I have been unsuccessful.  Can someone please let me know where I can get this information.  Thanks in advance.

Collapse
Posted by Torben Brosten on

Are you aware of the schema browser package?

Hopefully soon there will be a developer doc that identifies useful tools like this..

Once installed, Schema Browser is usually mounted at /schema and provides general info about each table including comments that one usually finds in the files[1] that create the tables. Since tables are listed in alphabetical order, all the acs_* tables will be grouped together. However, schema browser doesn't identify where the table is created. I believe all acs_* tables are created in the kernel packages. Kernel packages are identified primarily with a package key beginning with acs-.

To identify where a single table is created, you can search the packages. This query should catch most of them:

Computer:/var/lib/aolserver/service0 bohr$ grep -r 'create table acs_' packages | grep postgresql
packages/acs-events/sql/postgresql/acs-events-create.sql:create table acs_events (
packages/acs-events/sql/postgresql/acs-events-create.sql:create table acs_event_party_map (
packages/acs-events/sql/postgresql/activity-create.sql:create table acs_activities (
packages/acs-events/sql/postgresql/activity-create.sql:create table acs_activity_object_map (
packages/acs-kernel/sql/postgresql/acs-create.sql:create table acs_magic_objects (
packages/acs-kernel/sql/postgresql/acs-logs-create.sql:create table acs_logs (
packages/acs-kernel/sql/postgresql/acs-metadata-create.sql:create table acs_object_types (
packages/acs-kernel/sql/postgresql/acs-metadata-create.sql:create table acs_object_type_tables (
packages/acs-kernel/sql/postgresql/acs-metadata-create.sql:create table acs_datatypes (
packages/acs-kernel/sql/postgresql/acs-metadata-create.sql:--create table acs_input_types (
packages/acs-kernel/sql/postgresql/acs-metadata-create.sql:create table acs_attributes (
packages/acs-kernel/sql/postgresql/acs-metadata-create.sql:create table acs_enum_values (
packages/acs-kernel/sql/postgresql/acs-metadata-create.sql:create table acs_attribute_descriptions (
packages/acs-kernel/sql/postgresql/acs-objects-create.sql:create table acs_objects (
packages/acs-kernel/sql/postgresql/acs-objects-create.sql:create table acs_object_context_index (
packages/acs-kernel/sql/postgresql/acs-objects-create.sql:create table acs_attribute_values (
packages/acs-kernel/sql/postgresql/acs-objects-create.sql:create table acs_static_attr_values (
packages/acs-kernel/sql/postgresql/acs-permissions-create.sql:create table acs_privileges (
packages/acs-kernel/sql/postgresql/acs-permissions-create.sql:create table acs_privilege_hierarchy (
packages/acs-kernel/sql/postgresql/acs-permissions-create.sql:create table acs_privilege_hierarchy_index (
packages/acs-kernel/sql/postgresql/acs-permissions-create.sql:create table acs_privilege_descendant_map (
packages/acs-kernel/sql/postgresql/acs-permissions-create.sql:create table acs_permissions (
packages/acs-kernel/sql/postgresql/acs-permissions-create.sql:create table acs_permissions_lock (
packages/acs-kernel/sql/postgresql/acs-relationships-create.sql:create table acs_rel_roles (
packages/acs-kernel/sql/postgresql/acs-relationships-create.sql:create table acs_rel_types (
packages/acs-kernel/sql/postgresql/acs-relationships-create.sql:create table acs_rels (
packages/acs-kernel/sql/postgresql/postgresql.sql:create table acs_function_args (
packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6-4.6.1.sql:create table acs_privilege_descendant_map (
packages/acs-mail/sql/postgresql/acs-mail-create.sql:create table acs_mail_gc_objects (
packages/acs-mail/sql/postgresql/acs-mail-create.sql:create table acs_mail_bodies (
packages/acs-mail/sql/postgresql/acs-mail-create.sql:create table acs_mail_body_headers (
packages/acs-mail/sql/postgresql/acs-mail-create.sql:create table acs_mail_multiparts (
packages/acs-mail/sql/postgresql/acs-mail-create.sql:create table acs_mail_multipart_parts (
packages/acs-mail/sql/postgresql/acs-mail-create.sql:create table acs_mail_links (
packages/acs-mail/sql/postgresql/acs-mail-queue-create.sql:create table acs_mail_queue_messages (
packages/acs-mail/sql/postgresql/acs-mail-queue-create.sql:create table acs_mail_queue_incoming (
packages/acs-mail/sql/postgresql/acs-mail-queue-create.sql:create table acs_mail_queue_outgoing (
packages/acs-mail/sql/postgresql/upgrade/upgrade-4.6-4.6.1.sql:create table acs_mail_o_tmp as select * from acs_mail_queue_outgoing;
packages/acs-mail/sql/postgresql/upgrade/upgrade-4.6-4.6.1.sql:create table acs_mail_queue_outgoing (
packages/acs-mail-lite/sql/postgresql/acs-mail-lite-create.sql:create table acs_mail_lite_queue (
packages/acs-mail-lite/sql/postgresql/acs-mail-lite-create.sql:create table acs_mail_lite_mail_log (
packages/acs-mail-lite/sql/postgresql/acs-mail-lite-create.sql:create table acs_mail_lite_bounce (
packages/acs-mail-lite/sql/postgresql/acs-mail-lite-create.sql:create table acs_mail_lite_bounce_notif (
packages/acs-mail-lite/sql/postgresql/upgrade/acs-mail-lite-upgrade-0.1d-0.2a.sql:create table acs_mail_lite_mail_log (
packages/acs-mail-lite/sql/postgresql/upgrade/acs-mail-lite-upgrade-0.1d-0.2a.sql:create table acs_mail_lite_bounce (
packages/acs-mail-lite/sql/postgresql/upgrade/acs-mail-lite-upgrade-0.1d-0.2a.sql:create table acs_mail_lite_bounce_notif (
packages/acs-messaging/sql/postgresql/acs-messaging-create.sql:create table acs_messages (     -- extends cr_items
packages/acs-messaging/sql/postgresql/acs-messaging-create.sql:create table acs_messages_outgoing (
packages/acs-messaging/sql/postgresql/upgrade/upgrade-4.0-4.0.1a.sql:create table acs_mess_up (
packages/acs-messaging/sql/postgresql/upgrade/upgrade-4.0-4.0.1a.sql:create table acs_messages_outgoing (
packages/acs-reference/sql/postgresql/acs-reference-create.sql:create table acs_reference_repositories (
packages/acs-service-contract/sql/postgresql/acs-sc-msg-types-create.sql:create table acs_sc_msg_types (
packages/acs-service-contract/sql/postgresql/acs-sc-msg-types-create.sql:create table acs_sc_msg_type_elements (
packages/acs-service-contract/sql/postgresql/acs-sc-tables-create.sql:create table acs_sc_contracts (
packages/acs-service-contract/sql/postgresql/acs-sc-tables-create.sql:create table acs_sc_operations (
packages/acs-service-contract/sql/postgresql/acs-sc-tables-create.sql:create table acs_sc_impls (
packages/acs-service-contract/sql/postgresql/acs-sc-tables-create.sql:create table acs_sc_impl_aliases (
packages/acs-service-contract/sql/postgresql/acs-sc-tables-create.sql:create table acs_sc_bindings (
packages/categories/sql/postgresql/categories-init.sql:create table acs_named_objects (
Computer:/var/lib/aolserver/service0 bohr$ 
1. /package-key/sql/postgresql/*-create.sql
most of them belongs to the acs-core packages, a good start on understanding some of the tables will be to check what an acs-object is:
https://openacs.org/doc/openacs-5-1/objects.html