/*==============================================================*/
/* Database name:  axyl                                         */
/* DBMS name:      PostgreSQL 7                                 */
/* Created on:     15/10/2005 7:13:11 p.m.                      */
/*==============================================================*/


create sequence seq_block_id
increment 1
minvalue 1
maxvalue 2147483647
start 1
cache 1;

create sequence seq_blocklet_id
increment 1
minvalue 1
maxvalue 2147483647
start 1
cache 1;

create sequence seq_cat_id
increment 1
minvalue 1
maxvalue 2147483647
start 1
cache 1;

create sequence seq_category_id
increment 1
minvalue 1
maxvalue 2147483647
start 1
cache 1;

create sequence seq_contactinfo_id
increment 1
minvalue 1
maxvalue 2147483647
start 1
cache 1;

create sequence seq_element_id
increment 1
minvalue 1
maxvalue 2147483647
start 1
cache 1;

create sequence seq_enc_scheme_id
increment 1
minvalue 1
maxvalue 2147483647
start 1
cache 1;

create sequence seq_forum_id
increment 1
minvalue 1
maxvalue 2147483647
start 1
cache 1;

create sequence seq_group_id
increment 1
minvalue 1
maxvalue 2147483647
start 1
cache 1;

create sequence seq_lang_id
increment 1
minvalue 1
maxvalue 2147483647
start 1
cache 1;

create sequence seq_layout_id
increment 1
minvalue 1
maxvalue 2147483647
start 1
cache 1;

create sequence seq_location_id
increment 1
minvalue 1
maxvalue 2147483647
start 1
cache 1;

create sequence seq_menu_id
increment 1
minvalue 1
maxvalue 2147483647
start 1
cache 1;

create sequence seq_menuoption_id
increment 1
minvalue 1
maxvalue 2147483647
start 1
cache 1;

create sequence seq_microsite_page_id
increment 1
minvalue 1
maxvalue 2147483647
start 1
cache 1;

create sequence seq_microsite_template_id
increment 1
minvalue 1
maxvalue 2147483647
start 1
cache 1;

create sequence seq_msg_id
increment 1
minvalue 1
maxvalue 2147483647
start 1
cache 1;

create sequence seq_org_id
increment 1
minvalue 1
maxvalue 2147483647
start 1
cache 1;

create sequence seq_page_id
increment 1
minvalue 1
maxvalue 2147483647
start 1
cache 1;

create sequence seq_plugin_id
increment 1
minvalue 1
maxvalue 2147483647
start 1
cache 1;

create sequence seq_query_resource_id
increment 1
minvalue 1
maxvalue 2147483647
start 1
cache 1;

create sequence seq_story_id
increment 1
minvalue 1
maxvalue 2147483647
start 1
cache 1;

/*==============================================================*/
/* Table: ax_block                                              */
/*==============================================================*/
create table ax_block (
block_id             INT4                 not null,
layout_id            INT4                 not null,
background_img       INT4                 null,
lang_id              INT4                 not null default 0,
block_desc           TEXT                 null,
block_style          TEXT                 null,
block_type           TEXT                 null,
cols                 INT4                 not null default 1,
gutter_width         INT4                 not null default 0,
gutter_colour        TEXT                 null,
blocklet_sep         INT4                 not null default 0,
border_width         INT4                 not null default 0,
border_colour        TEXT                 null,
background_colour    TEXT                 null,
justify              TEXT                 not null default '' 
      constraint CKC_JUSTIFY_AX_BLOCK check (justify in ('','left','center','right')),
valign               TEXT                 not null default '' 
      constraint CKC_VALIGN_AX_BLOCK check (valign in ('','top','middle','bottom')),
exportable           BOOL                 not null default false,
cache                TEXT                 null,
cache_updated        TIMESTAMP WITH TIME ZONE null,
last_modified        TIMESTAMP WITH TIME ZONE not null default CURRENT_TIMESTAMP,
constraint ax_block_pkey primary key (block_id)
);

comment on column ax_block.lang_id is
'A unique text code for this language';

/*==============================================================*/
/* Index: ax_block_layout_FK                                    */
/*==============================================================*/
create  index ax_block_layout_FK on ax_block (
layout_id
);

/*==============================================================*/
/* Table: ax_block_blocklet                                     */
/*==============================================================*/
create table ax_block_blocklet (
blocklet_id          INT4                 not null,
block_id             INT4                 not null,
display_order        INT4                 not null default 0,
visible              BOOL                 not null default true,
constraint ax_block_blocklet_pkey primary key (blocklet_id, block_id)
);

/*==============================================================*/
/* Index: ax_block_blocklet_FK                                  */
/*==============================================================*/
create  index ax_block_blocklet_FK on ax_block_blocklet (
blocklet_id
);

/*==============================================================*/
/* Index: ax_block_blocklet_FK2                                 */
/*==============================================================*/
create  index ax_block_blocklet_FK2 on ax_block_blocklet (
block_id
);

/*==============================================================*/
/* Table: ax_blocklet                                           */
/*==============================================================*/
create table ax_blocklet (
blocklet_id          INT4                 not null,
blocklet_desc        TEXT                 null,
blocklet_type        TEXT                 not null default 'text' 
      constraint CKC_BLOCKLET_TYPE_AX_BLOCKLET check (blocklet_type in ('text','list','ordered','bullets','table','raw')),
blocklet_width       INT4                 not null default 100 
      constraint CKC_BLOCKLET_WIDTH_AX_BLOCKLET check (blocklet_width between 0 and 100),
blocklet_style       TEXT                 null,
justify              TEXT                 not null default '' 
      constraint CKC_JUSTIFY_AX_BLOCKLET check (justify in ('','left','center','right')),
heading              TEXT                 null,
heading_level        INT4                 not null default 3,
heading_colour       TEXT                 null,
ruler                TEXT                 not null default 'none' 
      constraint CKC_RULER_AX_BLOCKLET check (ruler in ('none','top','bottom')),
ruler_width          INT4                 not null default 100 
      constraint CKC_RULER_WIDTH_AX_BLOCKLET check (ruler_width between 0 and 100),
ruler_size           INT4                 not null default 1,
ruler_colour         TEXT                 null,
content              TEXT                 null,
content_size         FLOAT4               not null default '1.0',
content_colour       TEXT                 null,
table_style          TEXT                 null,
table_autojustify    BOOL                 not null default false,
table_rowstripes     BOOL                 not null default false,
constraint ax_blocklet_pkey primary key (blocklet_id)
);

/*==============================================================*/
/* Table: ax_catalog                                            */
/*==============================================================*/
create table ax_catalog (
cat_id               INT4                 not null,
cat_name             TEXT                 not null,
cat_desc             TEXT                 null,
mime_type            TEXT                 not null,
mime_category        TEXT                 not null,
upload_timestamp     TIMESTAMP WITH TIME ZONE not null default CURRENT_TIMESTAMP,
category             TEXT                 null,
keywords             TEXT                 null,
filesize             INT4                 not null default 0,
filepath             TEXT                 not null,
width                INT4                 not null default 0,
height               INT4                 not null default 0,
constraint ax_catalog_pkey primary key (cat_id)
);

/*==============================================================*/
/* Table: ax_config                                             */
/*==============================================================*/
create table ax_config (
config_name          TEXT                 not null,
config_fields        TEXT                 null,
constraint ax_config_pkey primary key (config_name)
);

/*==============================================================*/
/* Table: ax_configuration                                      */
/*==============================================================*/
create table ax_configuration (
config_name          TEXT                 not null,
config_id            TEXT                 not null,
config_set           TEXT                 null,
constraint ax_configuration_pkey primary key (config_name, config_id)
);

/*==============================================================*/
/* Index: ax_configuration_config_FK                            */
/*==============================================================*/
create  index ax_configuration_config_FK on ax_configuration (
config_name
);

/*==============================================================*/
/* Table: ax_contactinfo                                        */
/*==============================================================*/
create table ax_contactinfo (
contactinfo_id       INT4                 not null,
addr_postal          TEXT                 null,
addr_street          TEXT                 null,
addr_suburb          TEXT                 null,
addr_city            TEXT                 null,
addr_country         TEXT                 null,
addr_code            TEXT                 null,
addr_other           TEXT                 null,
emails               TEXT                 null,
phone                TEXT                 null,
phone_fax            TEXT                 null,
phone_work           TEXT                 null,
phone_mobile         TEXT                 null,
urls                 TEXT                 null,
constraint ax_contactinfo_pkey primary key (contactinfo_id)
);

/*==============================================================*/
/* Table: ax_content_location                                   */
/*==============================================================*/
create table ax_content_location (
location_id          INT4                 not null,
location_name        TEXT                 not null,
location_desc        TEXT                 null,
enabled              BOOL                 not null default true,
constraint PK_AX_CONTENT_LOCATION primary key (location_id)
);

comment on table ax_content_location is
'Contains unique logical locations on the website for content. These are not necessarily physical pages, but can be used in your application code to present the story in the locations required.';

comment on column ax_content_location.enabled is
'If true, then items in this location can be displayed in an order determined by the user, else display by default method (usually reverse date order)';

/*==============================================================*/
/* Table: ax_control                                            */
/*==============================================================*/
create table ax_control (
app_prefix           TEXT                 null,
site_axyl_version    TEXT                 null,
site_docroot         TEXT                 null
);

comment on table ax_control is
'Details which are registered for any Axyl website, on first access, or on upgrade.';

comment on column ax_control.app_prefix is
'The code name of this website - often used as a prefix for messaging etc.';

comment on column ax_control.site_axyl_version is
'The version of Axyl that this website was installed under';

comment on column ax_control.site_docroot is
'The full physical path to this website when installed';

/*==============================================================*/
/* Table: ax_element_set_enc                                    */
/*==============================================================*/
create table ax_element_set_enc (
schema_name          TEXT                 not null,
element_id           INT4                 not null,
enc_scheme_id        INT4                 not null,
constraint PK_AX_ELEMENT_SET_ENC primary key (schema_name, element_id, enc_scheme_id)
);

/*==============================================================*/
/* Table: ax_enc_qualifier                                      */
/*==============================================================*/
create table ax_enc_qualifier (
enc_scheme_id        INT4                 not null,
qual_name            TEXT                 not null,
qual_label           TEXT                 null,
comments             TEXT                 null,
default_value        TEXT                 null,
list_of_values       TEXT                 null,
display_order        INT4                 not null default 999,
constraint PK_AX_ENC_QUALIFIER primary key (enc_scheme_id, qual_name)
);

/*==============================================================*/
/* Table: ax_enc_scheme                                         */
/*==============================================================*/
create table ax_enc_scheme (
enc_scheme_id        INT4                 not null,
enc_scheme_name      TEXT                 not null,
label                TEXT                 null,
tag_name             TEXT                 not null,
description          TEXT                 null,
datasrc_uri          TEXT                 null,
reference_url        TEXT                 null,
enabled              BOOL                 not null default true,
constraint PK_AX_ENC_SCHEME primary key (enc_scheme_id)
);

comment on table ax_enc_scheme is
'This table holds encoding schemes, which are basically lists of values. An example would be an encoding scheme for Media Types, which is essentially a list of mime-types. Other examples include Language Name Codes, and even Medical Subject Headings.';

comment on column ax_enc_scheme.enc_scheme_name is
'Short name-tag - often an code like ''RFC3066''.';

comment on column ax_enc_scheme.label is
'Full name of the scheme.';

comment on column ax_enc_scheme.datasrc_uri is
'Location of downloadable scheme data.';

comment on column ax_enc_scheme.reference_url is
'Web-page where scheme is described/defined.';

/*==============================================================*/
/* Table: ax_enc_value                                          */
/*==============================================================*/
create table ax_enc_value (
enc_scheme_id        INT4                 not null,
enc_value            TEXT                 not null,
enc_label            TEXT                 null,
constraint PK_AX_ENC_VALUE primary key (enc_scheme_id, enc_value)
);

/*==============================================================*/
/* Table: ax_forum                                              */
/*==============================================================*/
create table ax_forum (
forum_id             INT4                 not null,
moderator            TEXT                 null,
forum_name           TEXT                 not null,
forum_desc           TEXT                 null,
enabled              BOOL                 not null default true,
private              BOOL                 not null default false,
last_author          TEXT                 null,
threadlast_author    INT4                 not null default 0,
datelast_author      DATE                 null,
posts                INT4                 not null default 0,
constraint ax_forum_pkey primary key (forum_id)
);

/*==============================================================*/
/* Table: ax_forum_member                                       */
/*==============================================================*/
create table ax_forum_member (
user_id              TEXT                 not null,
forum_id             INT4                 not null,
constraint ax_forum_mbr_pkey primary key (user_id, forum_id)
);

/*==============================================================*/
/* Index: ax_forum_member_FK                                    */
/*==============================================================*/
create  index ax_forum_member_FK on ax_forum_member (
user_id
);

/*==============================================================*/
/* Index: ax_forum_member_FK2                                   */
/*==============================================================*/
create  index ax_forum_member_FK2 on ax_forum_member (
forum_id
);

/*==============================================================*/
/* Table: ax_forum_msg                                          */
/*==============================================================*/
create table ax_forum_msg (
msg_id               INT4                 not null,
forum_id             INT4                 not null,
msg_subject          TEXT                 not null,
msg_text             TEXT                 not null,
msg_author           TEXT                 not null,
msg_votes            INT4                 not null default 0,
last_modified        TIMESTAMP WITH TIME ZONE not null default CURRENT_TIMESTAMP,
parent_thread_id     INT4                 null,
sticky               BOOL                 not null default false,
locked               BOOL                 not null default false,
views                INT4                 not null default 0,
enabled              BOOL                 not null default true,
replies              INT4                 not null default 0,
constraint ax_forum_msg_pkey primary key (msg_id)
);

/*==============================================================*/
/* Index: ax_fk_forum_msg_forum_FK                              */
/*==============================================================*/
create  index ax_fk_forum_msg_forum_FK on ax_forum_msg (
forum_id
);

/*==============================================================*/
/* Table: ax_group                                              */
/*==============================================================*/
create table ax_group (
group_id             INT4                 not null,
group_desc           TEXT                 not null,
constraint ax_group_pkey primary key (group_id)
);

/*==============================================================*/
/* Table: ax_language                                           */
/*==============================================================*/
create table ax_language (
lang_id              INT4                 not null,
lang_desc            TEXT                 not null,
char_encoding        TEXT                 not null,
direction            TEXT                 not null default 'LTR' 
      constraint CKC_DIRECTION_AX_LANGU check (direction in ('LTR','RTL')),
is_default           BOOL                 not null default false,
display_order        INT4                 not null default 999,
enabled              BOOL                 not null default true,
constraint PK_AX_LANGUAGE primary key (lang_id)
);

comment on table ax_language is
'Contains the languages that Axyl supports.';

comment on column ax_language.lang_id is
'A unique text code for this language';

comment on column ax_language.lang_desc is
'Nice descriptive word or phrase for this language.';

comment on column ax_language.char_encoding is
'The character encoding code to use for this language. This code may appear in various webpage tags to guide browsers.';

comment on column ax_language.direction is
'The direction the language is written in: LTR (left to right) or RTL (right to left.';

comment on column ax_language.is_default is
'Whether this language is the default one.';

/*==============================================================*/
/* Table: ax_layout                                             */
/*==============================================================*/
create table ax_layout (
layout_id            INT4                 not null,
layout_name          TEXT                 not null,
lang_id              INT4                 not null default 0,
index_category       TEXT                 null,
layout_table         TEXT                 null,
show_last_modified   BOOL                 not null default false,
format_last_modified TEXT                 null,
prefix_last_modified TEXT                 null,
layout_style         TEXT                 null,
cache                TEXT                 null,
cache_updated        TIMESTAMP WITH TIME ZONE null,
last_modified        TIMESTAMP WITH TIME ZONE not null default CURRENT_TIMESTAMP,
constraint ax_layout_pkey primary key (layout_id)
);

/*==============================================================*/
/* Index: ax_layout_set_FK                                      */
/*==============================================================*/
create  index ax_layout_set_FK on ax_layout (
layout_name
);

/*==============================================================*/
/* Table: ax_layout_metadata                                    */
/*==============================================================*/
create table ax_layout_metadata (
layout_id            INT4                 not null,
element_id           INT4                 not null,
schema_name          TEXT                 not null,
meta_tag_value       TEXT                 null,
enc_scheme_id        INT4                 null,
linked_uri           BOOL                 not null default false,
language             TEXT                 null,
constraint PK_AX_LAYOUT_METADATA primary key (layout_id, element_id, schema_name)
);

comment on table ax_layout_metadata is
'This is the actual container of metadata values for the given layout. A webpage might contain multiple layouts, therefore the metadata to be entered for the page is the unique combination of all layout metadata elements.';

/*==============================================================*/
/* Table: ax_layout_set                                         */
/*==============================================================*/
create table ax_layout_set (
layout_name          TEXT                 not null,
page_id              INT4                 null,
constraint ax_layout_set_pkey primary key (layout_name)
);

/*==============================================================*/
/* Index: ax_layout_set_sitepage_FK                             */
/*==============================================================*/
create  index ax_layout_set_sitepage_FK on ax_layout_set (
page_id
);

/*==============================================================*/
/* Table: ax_layout_set_group                                   */
/*==============================================================*/
create table ax_layout_set_group (
layout_name          TEXT                 not null,
cm_privilege         TEXT                 not null 
      constraint CKC_CM_PRIVILEGE_AX_LAYOU check (cm_privilege in ('editor','author','entry')),
group_id             INT4                 not null,
constraint PK_AX_LAYOUT_SET_GROUP primary key (layout_name, cm_privilege, group_id)
);

/*==============================================================*/
/* Table: ax_menu                                               */
/*==============================================================*/
create table ax_menu (
menu_id              INT4                 not null,
menu_name            TEXT                 not null,
lang_id              INT4                 not null default 0,
menu_desc            TEXT                 null,
menu_user_groups     TEXT                 null,
active               BOOL                 not null default true,
last_modified        TIMESTAMP WITH TIME ZONE not null default CURRENT_TIMESTAMP,
constraint ax_menu_pkey primary key (menu_id)
);

/*==============================================================*/
/* Index: menu_language                                         */
/*==============================================================*/
create unique index menu_language on ax_menu (
menu_name,
lang_id
);

/*==============================================================*/
/* Table: ax_menuoption                                         */
/*==============================================================*/
create table ax_menuoption (
menuoption_id        INT4                 not null,
menu_id              INT4                 not null,
parent_id            INT4                 null default 0,
user_groups          TEXT                 null,
user_type            TEXT                 null,
menu_level           INT4                 not null default 0,
label                TEXT                 null,
description          TEXT                 null,
display_order        INT4                 not null default 0,
action               TEXT                 null,
sitepage             TEXT                 null,
sitepage_parms       TEXT                 null,
auth_code            BOOL                 not null default false,
active               BOOL                 not null default true,
width                INT4                 not null default 80,
height               INT4                 not null default 20,
is_parent            BOOL                 not null default false,
target               TEXT                 null,
last_modified        TIMESTAMP WITH TIME ZONE not null default CURRENT_TIMESTAMP,
constraint ax_menuoption_pkey primary key (menuoption_id)
);

/*==============================================================*/
/* Index: ax_menuoption_menu_FK                                 */
/*==============================================================*/
create  index ax_menuoption_menu_FK on ax_menuoption (
menu_id
);

/*==============================================================*/
/* Table: ax_meta_element                                       */
/*==============================================================*/
create table ax_meta_element (
element_id           INT4                 not null,
label                TEXT                 null,
tag_name             TEXT                 not null,
definition           TEXT                 null,
parent_element       INT4                 null,
parent_schema        TEXT                 null,
enabled              BOOL                 not null default true,
constraint PK_AX_META_ELEMENT primary key (element_id)
);

comment on table ax_meta_element is
'A metadata element is the basic description of a template for an actual container of metadata. It is usually first ''born'' in a particular meta schema, but can thenceforth be picked up and used as part of other schemas - this is the function of the foreign key field ''parent_schema''.';

/*==============================================================*/
/* Table: ax_meta_element_set                                   */
/*==============================================================*/
create table ax_meta_element_set (
schema_name          TEXT                 not null,
element_id           INT4                 not null,
preferred_enc_scheme INT4                 null,
comments             TEXT                 null,
obligation           TEXT                 not null default 'o' 
      constraint CKC_OBLIGATION_AX_META_ check (obligation in ('m','c','r','o')),
default_value        TEXT                 null,
list_of_values       TEXT                 null,
display_order        INT4                 not null default 999,
enabled              BOOL                 not null default true,
constraint PK_AX_META_ELEMENT_SET primary key (schema_name, element_id)
);

comment on table ax_meta_element_set is
'This table represents a list of metadata elements which comprise the given schema. The elements might originate from other schemas, but all together they comprise a logical set for this schema. Each schema can also flag it''s elements as either mandatory or not, and can define a default display (data entry) order.';

/*==============================================================*/
/* Table: ax_meta_schema                                        */
/*==============================================================*/
create table ax_meta_schema (
schema_name          TEXT                 not null,
description          TEXT                 null,
namespace            TEXT                 null,
namespace_uri        TEXT                 null,
constraint PK_AX_META_SCHEMA primary key (schema_name)
);

comment on table ax_meta_schema is
'A meta schema is simply an umbrella entity which describes a metadata element set. Examples would be ''Dublin Core'', ''AGLS'', or ''NZGLS''.';

/*==============================================================*/
/* Table: ax_microsite                                          */
/*==============================================================*/
create table ax_microsite (
microsite_name       TEXT                 not null,
menu_id              INT4                 null,
microsite_desc       TEXT                 null,
microsite_domain     TEXT                 null,
css                  TEXT                 null,
css_ie               TEXT                 null,
css_ns               TEXT                 null,
req_microsite_publish BOOL                 not null default false,
req_microsite_remove BOOL                 not null default false,
currently_installed  BOOL                 not null default false,
last_installed       TIMESTAMP            null,
last_modified        TIMESTAMP WITH TIME ZONE not null default CURRENT_TIMESTAMP,
constraint PK_AX_MICROSITE primary key (microsite_name)
);

/*==============================================================*/
/* Table: ax_microsite_media                                    */
/*==============================================================*/
create table ax_microsite_media (
microsite_name       TEXT                 not null,
cat_id               INT4                 not null,
constraint PK_AX_MICROSITE_MEDIA primary key (microsite_name, cat_id)
);

/*==============================================================*/
/* Table: ax_microsite_page                                     */
/*==============================================================*/
create table ax_microsite_page (
microsite_page_id    INT4                 not null,
microsite_name       TEXT                 not null,
page_id              INT4                 null,
menuoption_id        INT4                 null,
microsite_template_id INT4                 null,
page_title           TEXT                 not null,
corepage             BOOL                 not null default false,
microsite_homepage   BOOL                 not null default false,
menuoption_label     TEXT                 not null,
display_order        INT4                 not null default 999,
cache_seconds        INT4                 not null default 0,
enabled              BOOL                 not null default true,
constraint PK_AX_MICROSITE_PAGE primary key (microsite_page_id)
);

/*==============================================================*/
/* Table: ax_microsite_page_plugin                              */
/*==============================================================*/
create table ax_microsite_page_plugin (
plugin_id            INT4                 not null,
microsite_page_id    INT4                 not null,
plugin_pattern       TEXT                 not null,
plugin_content       TEXT                 not null,
constraint PK_AX_MICROSITE_PAGE_PLUGIN primary key (plugin_id)
);

/*==============================================================*/
/* Table: ax_microsite_story                                    */
/*==============================================================*/
create table ax_microsite_story (
microsite_name       TEXT                 not null,
story_id             INT4                 not null,
constraint PK_AX_MICROSITE_STORY primary key (microsite_name, story_id)
);

/*==============================================================*/
/* Table: ax_microsite_template                                 */
/*==============================================================*/
create table ax_microsite_template (
microsite_template_id INT4                 not null,
microsite_name       TEXT                 not null,
template_name        TEXT                 not null,
template_type        TEXT                 not null,
template_content     TEXT                 null,
constraint PK_AX_MICROSITE_TEMPLATE primary key (microsite_template_id)
);

/*==============================================================*/
/* Table: ax_organisation                                       */
/*==============================================================*/
create table ax_organisation (
org_id               INT4                 not null,
contactinfo_id       INT4                 null,
org_name             TEXT                 not null,
org_desc             TEXT                 null,
org_contact          TEXT                 null,
enabled              BOOL                 not null default true,
constraint ax_org_pkey primary key (org_id)
);

/*==============================================================*/
/* Index: ax_org_contactinfo_FK                                 */
/*==============================================================*/
create  index ax_org_contactinfo_FK on ax_organisation (
contactinfo_id
);

/*==============================================================*/
/* Table: ax_plugin_area                                        */
/*==============================================================*/
create table ax_plugin_area (
plugin_pattern       TEXT                 not null,
plugin_desc          TEXT                 null,
constraint PK_AX_PLUGIN_AREA primary key (plugin_pattern)
);

/*==============================================================*/
/* Table: ax_plugin_content                                     */
/*==============================================================*/
create table ax_plugin_content (
plugin_content       TEXT                 not null,
plugin_content_desc  TEXT                 not null,
generator            TEXT                 not null,
generator_type       TEXT                 not null default 'defer' 
      constraint CKC_GENERATOR_TYPE_AX_PLUGI check (generator_type in ('cm','func','defer','lit','file')),
replace_content      BOOL                 not null default false,
constraint PK_AX_PLUGIN_CONTENT primary key (plugin_content)
);

/*==============================================================*/
/* Table: ax_query_resource                                     */
/*==============================================================*/
create table ax_query_resource (
quid                 INT4                 not null,
q_desc               TEXT                 null,
q_query              TEXT                 null,
q_script             TEXT                 null,
enabled              BOOL                 not null default true,
constraint ax_query_key primary key (quid)
);

/*==============================================================*/
/* Table: ax_site_meta_element                                  */
/*==============================================================*/
create table ax_site_meta_element (
element_id           INT4                 not null,
schema_name          TEXT                 not null,
label                TEXT                 null,
description          TEXT                 null,
indexed              BOOL                 not null default false,
searchable           BOOL                 not null default false,
obligation           TEXT                 not null default 'o' 
      constraint CKC_OBLIGATION_AX_SITE_ check (obligation in ('m','c','r','o')),
default_value        TEXT                 null,
list_of_values       TEXT                 null,
display_order        INT4                 not null default 999,
enabled              BOOL                 null default true,
constraint PK_AX_SITE_META_ELEMENT primary key (element_id, schema_name)
);

comment on table ax_site_meta_element is
'This table holds the list of metadata elements which are going to be used for the website. These can be assembled from any of the defined metadata elements.';

/*==============================================================*/
/* Table: ax_sitepage                                           */
/*==============================================================*/
create table ax_sitepage (
page_id              INT4                 not null,
page_title           TEXT                 not null,
page_path            TEXT                 not null,
managed              BOOL                 not null default true,
enabled              BOOL                 not null default true,
constraint ax_sitepage_pkey primary key (page_id)
);

/*==============================================================*/
/* Table: ax_sitepage_group                                     */
/*==============================================================*/
create table ax_sitepage_group (
group_id             INT4                 not null,
page_id              INT4                 not null,
constraint PK_AX_SITEPAGE_GROUP primary key (group_id, page_id)
);

comment on table ax_sitepage_group is
'Indicates mandatory group membership for sitepage access.';

/*==============================================================*/
/* Table: ax_story                                              */
/*==============================================================*/
create table ax_story (
story_id             INT4                 not null,
lang_id              INT4                 not null default 0,
category_id          INT4                 null,
story_author         TEXT                 null,
story_icon           INT4                 null,
story_icon_url       TEXT                 null,
story_headline       TEXT                 null,
story_precis         TEXT                 null,
story_content        TEXT                 null,
story_type           TEXT                 null,
story_url            TEXT                 null,
story_date           TIMESTAMP WITH TIME ZONE not null default CURRENT_TIMESTAMP,
expiry_date          TIMESTAMP WITH TIME ZONE null,
last_modified        TIMESTAMP WITH TIME ZONE not null default CURRENT_TIMESTAMP,
deleted              BOOL                 not null default false,
visible              BOOL                 not null default true,
constraint PK_AX_STORY primary key (story_id)
);

comment on column ax_story.story_url is
'If the story is on a remote website, or a filesystem then the URL or physical path to the file is stored here.';

/*==============================================================*/
/* Table: ax_story_category                                     */
/*==============================================================*/
create table ax_story_category (
category_id          INT4                 not null,
category_desc        TEXT                 not null,
has_media            BOOL                 not null default false,
has_precis           BOOL                 not null default true,
has_expiry           BOOL                 not null default false,
has_multilang        BOOL                 not null default true,
has_multimedia       BOOL                 not null default false,
constraint PK_AX_STORY_CATEGORY primary key (category_id)
);

comment on table ax_story_category is
'The story can be used to split stories into groups, or subject areas.';

comment on column ax_story_category.has_media is
'True if this category can have media (images, movies etc.) associated with it.';

comment on column ax_story_category.has_precis is
'True if this category requires a precis or lead-in.';

comment on column ax_story_category.has_expiry is
'True if this category has an expiry date associated with it.';

comment on column ax_story_category.has_multilang is
'True if this category is for stories which could be translated into multiple languages.';

comment on column ax_story_category.has_multimedia is
'If true then the category will support more than one item of associated media.';

/*==============================================================*/
/* Table: ax_story_category_locs                                */
/*==============================================================*/
create table ax_story_category_locs (
category_id          INT4                 not null,
location_id          INT4                 not null,
constraint PK_AX_STORY_CATEGORY_LOCS primary key (category_id, location_id)
);

comment on table ax_story_category_locs is
'The default content locations for a story category.';

/*==============================================================*/
/* Table: ax_story_location                                     */
/*==============================================================*/
create table ax_story_location (
story_id             INT4                 not null,
location_id          INT4                 not null,
display_order        INT4                 not null default 999,
constraint PK_AX_STORY_LOCATION primary key (story_id, location_id)
);

comment on table ax_story_location is
'Contains lists of stories which should be displayed in a particular location on the website. Also determines the order that the stories should be displayed in.';

/*==============================================================*/
/* Table: ax_story_media                                        */
/*==============================================================*/
create table ax_story_media (
story_id             INT4                 not null,
cat_id               INT4                 not null,
caption              TEXT                 null,
width                INT4                 not null default 0,
height               INT4                 not null default 0,
justify              TEXT                 not null default '' 
      constraint CKC_JUSTIFY_AX_STORY check (justify in ('','left','center','right')),
display_order        INT4                 not null default 999,
constraint PK_AX_STORY_MEDIA primary key (story_id, cat_id)
);

comment on table ax_story_media is
'Media items associated with the story.';

/*==============================================================*/
/* Table: ax_story_translation                                  */
/*==============================================================*/
create table ax_story_translation (
story_id             INT4                 not null,
translated_story_id  INT4                 not null,
constraint PK_AX_STORY_TRANSLATION primary key (story_id, translated_story_id)
);

comment on table ax_story_translation is
'Used to link translations of the same story together.';

/*==============================================================*/
/* Table: ax_user                                               */
/*==============================================================*/
create table ax_user (
user_id              TEXT                 not null,
org_id               INT4                 not null default 0,
password             TEXT                 null,
full_name            TEXT                 null,
honorific_prefix     TEXT                 null,
first_name           TEXT                 null,
mid_names            TEXT                 null,
last_name            TEXT                 null,
user_type            TEXT                 not null default 'user' 
      constraint CKC_USER_TYPE_AX_USER check (user_type in ('user','sys','remote')),
email                TEXT                 null,
address              TEXT                 null,
phone                TEXT                 null,
fax                  TEXT                 null,
mobile               TEXT                 null,
last_login           TIMESTAMP WITH TIME ZONE null,
total_logins         INT4                 not null default 0,
limit_logins         INT4                 not null default 0,
auth_code            TEXT                 null,
passwd_forever       BOOL                 not null default false,
passwd_expiry        TIMESTAMP WITH TIME ZONE null,
passwd_history       TEXT                 null,
passwd_failures      INT4                 not null default 0,
locked               BOOL                 not null default false,
enabled              BOOL                 not null default true,
constraint ax_user_pkey primary key (user_id)
);

/*==============================================================*/
/* Index: ax_user_org_FK                                        */
/*==============================================================*/
create  index ax_user_org_FK on ax_user (
org_id
);

/*==============================================================*/
/* Table: ax_user_group                                         */
/*==============================================================*/
create table ax_user_group (
group_id             INT4                 not null,
user_id              TEXT                 not null,
constraint ax_ugroup_pkey primary key (group_id, user_id)
);

/*==============================================================*/
/* Index: ax_user_group_FK                                      */
/*==============================================================*/
create  index ax_user_group_FK on ax_user_group (
group_id
);

/*==============================================================*/
/* Index: ax_user_group_FK2                                     */
/*==============================================================*/
create  index ax_user_group_FK2 on ax_user_group (
user_id
);

/*==============================================================*/
/* Table: ax_user_ip                                            */
/*==============================================================*/
create table ax_user_ip (
user_id              TEXT                 not null,
ip                   INET                 not null,
constraint ax_user_ip_pkey primary key (user_id, ip)
);

/*==============================================================*/
/* Index: ax_user_ip_user_FK                                    */
/*==============================================================*/
create  index ax_user_ip_user_FK on ax_user_ip (
user_id
);

/*==============================================================*/
/* Table: ax_wwwsession                                         */
/*==============================================================*/
create table ax_wwwsession (
session_id           TEXT                 not null,
user_id              TEXT                 not null,
menuoption_id        INT4                 null,
lang_id              INT4                 not null default 0,
menu_status          TEXT                 null,
user_groups          TEXT                 null,
login_type           INT4                 not null default 0 
      constraint CKC_LOGIN_TYPE_AX_WWWSE check (login_type between 0 and 4),
login_datetime       TIMESTAMP WITH TIME ZONE not null default CURRENT_TIMESTAMP,
constraint wwwsession_pkey primary key (session_id)
);

/*==============================================================*/
/* Index: ax_wwwsession_user_FK                                 */
/*==============================================================*/
create  index ax_wwwsession_user_FK on ax_wwwsession (
user_id
);

alter table ax_block
   add constraint FK_AX_BLOCK_AX_BLOCK__AX_CATAL foreign key (background_img)
      references ax_catalog (cat_id)
      on delete set null on update restrict
      deferrable initially deferred;

alter table ax_block
   add constraint fk_ax_block_language foreign key (lang_id)
      references ax_language (lang_id)
      on delete restrict on update restrict
      deferrable initially deferred;

alter table ax_block
   add constraint ax_block_layout foreign key (layout_id)
      references ax_layout (layout_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_block_blocklet
   add constraint ax_block_blocklet_block foreign key (block_id)
      references ax_block (block_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_block_blocklet
   add constraint ax_block_blocklet_blocklet foreign key (blocklet_id)
      references ax_blocklet (blocklet_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_configuration
   add constraint ax_configuration_config foreign key (config_name)
      references ax_config (config_name)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_element_set_enc
   add constraint fk_ax_elemset_enc_enc foreign key (enc_scheme_id)
      references ax_enc_scheme (enc_scheme_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_element_set_enc
   add constraint fk_ax_elemset_enc_set foreign key (schema_name, element_id)
      references ax_meta_element_set (schema_name, element_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_enc_qualifier
   add constraint fk_ax_enc_qual_scheme foreign key (enc_scheme_id)
      references ax_enc_scheme (enc_scheme_id)
      on delete restrict on update restrict
      deferrable initially deferred;

alter table ax_enc_value
   add constraint fk_ax_enc_schema_value foreign key (enc_scheme_id)
      references ax_enc_scheme (enc_scheme_id)
      on delete restrict on update restrict
      deferrable initially deferred;

alter table ax_forum
   add constraint FK_AX_FORUM_MODERATOR foreign key (moderator)
      references ax_user (user_id)
      on delete restrict on update restrict
      deferrable initially deferred;

alter table ax_forum_member
   add constraint ax_forum_mbr_forum foreign key (forum_id)
      references ax_forum (forum_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_forum_member
   add constraint ax_forum_mbr_user foreign key (user_id)
      references ax_user (user_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_forum_msg
   add constraint ax_forum_msg_forum foreign key (forum_id)
      references ax_forum (forum_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_forum_msg
   add constraint FK_AX_FORUM_PARENT_TH_AX_FORUM foreign key (parent_thread_id)
      references ax_forum_msg (msg_id)
      on delete cascade on update cascade;

alter table ax_layout
   add constraint fk_ax_layout_language foreign key (lang_id)
      references ax_language (lang_id)
      on delete restrict on update restrict
      deferrable initially deferred;

alter table ax_layout
   add constraint ax_layout_set_layout foreign key (layout_name)
      references ax_layout_set (layout_name)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_layout_metadata
   add constraint fk_ax_lay_metadata_elem foreign key (element_id, schema_name)
      references ax_site_meta_element (element_id, schema_name)
      on delete restrict on update restrict
      deferrable initially deferred;

alter table ax_layout_metadata
   add constraint fk_ax_lay_metadata_scheme foreign key (enc_scheme_id)
      references ax_enc_scheme (enc_scheme_id)
      on delete set null on update restrict
      deferrable initially deferred;

alter table ax_layout_metadata
   add constraint fk_ax_layout_meta_data foreign key (layout_id)
      references ax_layout (layout_id)
      on delete cascade
      deferrable initially deferred;

alter table ax_layout_set
   add constraint ax_layout_set_sitepage foreign key (page_id)
      references ax_sitepage (page_id)
      on delete set null on update restrict
      deferrable initially deferred;

alter table ax_layout_set_group
   add constraint fk_group_layout_set foreign key (layout_name)
      references ax_layout_set (layout_name)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_layout_set_group
   add constraint fk_layout_set_group foreign key (group_id)
      references ax_group (group_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_menu
   add constraint fk_ax_menu_language foreign key (lang_id)
      references ax_language (lang_id)
      on delete restrict on update restrict
      deferrable initially deferred;

alter table ax_menuoption
   add constraint fk_ax_menuoption_menu foreign key (menu_id)
      references ax_menu (menu_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_meta_element
   add constraint fk_ax_meta_elem_parent_schema foreign key (parent_schema)
      references ax_meta_schema (schema_name)
      on delete restrict on update restrict
      deferrable initially deferred;

alter table ax_meta_element
   add constraint fk_ax_parent_element foreign key (parent_element)
      references ax_meta_element (element_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_meta_element_set
   add constraint fk_ax_meta_elem_set_elem foreign key (element_id)
      references ax_meta_element (element_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_meta_element_set
   add constraint fk_ax_meta_elem_set_schema foreign key (schema_name)
      references ax_meta_schema (schema_name)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_meta_element_set
   add constraint fk_ax_pref_enc_scheme foreign key (preferred_enc_scheme)
      references ax_enc_scheme (enc_scheme_id)
      on delete restrict on update restrict;

alter table ax_microsite
   add constraint fk_ax_microsite_menu foreign key (menu_id)
      references ax_menu (menu_id)
      on delete set null on update restrict
      deferrable initially deferred;

alter table ax_microsite_media
   add constraint fk_ax_microsite_media foreign key (cat_id)
      references ax_catalog (cat_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_microsite_media
   add constraint fk_ax_microsite_media_page foreign key (microsite_name)
      references ax_microsite (microsite_name)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_microsite_page
   add constraint fk_ax_microsite_page foreign key (microsite_name)
      references ax_microsite (microsite_name)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_microsite_page
   add constraint fk_ax_microsite_page_template foreign key (microsite_template_id)
      references ax_microsite_template (microsite_template_id)
      on delete restrict on update restrict
      deferrable initially deferred;

alter table ax_microsite_page
   add constraint fk_ax_microsite_sitepage foreign key (page_id)
      references ax_sitepage (page_id)
      on delete set null on update restrict
      deferrable initially deferred;

alter table ax_microsite_page
   add constraint fk_microsite_page_menuoption foreign key (menuoption_id)
      references ax_menuoption (menuoption_id)
      on delete set null on update restrict
      deferrable initially deferred;

alter table ax_microsite_page_plugin
   add constraint fk_microsite_page_plugin foreign key (microsite_page_id)
      references ax_microsite_page (microsite_page_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_microsite_page_plugin
   add constraint fk_plugin_area foreign key (plugin_pattern)
      references ax_plugin_area (plugin_pattern)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_microsite_page_plugin
   add constraint fk_plugin_content foreign key (plugin_content)
      references ax_plugin_content (plugin_content)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_microsite_story
   add constraint fk_microsite_story foreign key (story_id)
      references ax_story (story_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_microsite_story
   add constraint fk_story_microsite foreign key (microsite_name)
      references ax_microsite (microsite_name)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_microsite_template
   add constraint fk_ax_microsite_template foreign key (microsite_name)
      references ax_microsite (microsite_name)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_organisation
   add constraint ax_org_contactinfo foreign key (contactinfo_id)
      references ax_contactinfo (contactinfo_id)
      on delete restrict on update restrict
      deferrable initially deferred;

alter table ax_site_meta_element
   add constraint fk_ax_site_meta_element foreign key (schema_name, element_id)
      references ax_meta_element_set (schema_name, element_id)
      on delete restrict on update restrict
      deferrable initially deferred;

alter table ax_sitepage_group
   add constraint fk_ax_sitepage_gp_group foreign key (group_id)
      references ax_group (group_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_sitepage_group
   add constraint fk_ax_sitepage_gp_page foreign key (page_id)
      references ax_sitepage (page_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_story
   add constraint fk_ax_story_author foreign key (story_author)
      references ax_user (user_id)
      on delete set null on update restrict
      deferrable initially deferred;

alter table ax_story
   add constraint fk_ax_story_category foreign key (category_id)
      references ax_story_category (category_id)
      on delete restrict on update restrict
      deferrable initially deferred;

alter table ax_story
   add constraint fk_ax_story_language foreign key (lang_id)
      references ax_language (lang_id)
      on delete restrict on update restrict
      deferrable initially deferred;

alter table ax_story
   add constraint fk_story_icon foreign key (story_icon)
      references ax_catalog (cat_id)
      on delete set null on update restrict
      deferrable initially deferred;

alter table ax_story_category_locs
   add constraint fk_ax_story_categ_loc foreign key (category_id)
      references ax_story_category (category_id)
      on delete restrict on update restrict
      deferrable initially deferred;

alter table ax_story_category_locs
   add constraint fk_ax_story_categ_loc_loc foreign key (location_id)
      references ax_content_location (location_id)
      on delete restrict on update restrict
      deferrable initially deferred;

alter table ax_story_location
   add constraint fk_ax_content_location foreign key (location_id)
      references ax_content_location (location_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_story_location
   add constraint fk_ax_story_location foreign key (story_id)
      references ax_story (story_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_story_media
   add constraint fk_ax_story_media_cat_id foreign key (cat_id)
      references ax_catalog (cat_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_story_media
   add constraint fk_ax_story_media_story foreign key (story_id)
      references ax_story (story_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_story_translation
   add constraint fk_ax_original_story foreign key (story_id)
      references ax_story (story_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_story_translation
   add constraint fk_ax_translated_story foreign key (translated_story_id)
      references ax_story (story_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_user
   add constraint ax_user_organisation foreign key (org_id)
      references ax_organisation (org_id)
      on delete restrict on update restrict
      deferrable initially deferred;

alter table ax_user_group
   add constraint ax_user_group_group foreign key (group_id)
      references ax_group (group_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_user_group
   add constraint ax_user_group_user foreign key (user_id)
      references ax_user (user_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_user_ip
   add constraint ax_user_ip_user foreign key (user_id)
      references ax_user (user_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_wwwsession
   add constraint fk_ax_session_language foreign key (lang_id)
      references ax_language (lang_id)
      on delete restrict on update restrict
      deferrable initially deferred;

alter table ax_wwwsession
   add constraint ax_wwwsession_user foreign key (user_id)
      references ax_user (user_id)
      on delete cascade on update restrict
      deferrable initially deferred;

alter table ax_wwwsession
   add constraint FK_AX_WWWSE_SESSION_M_AX_MENUO foreign key (menuoption_id)
      references ax_menuoption (menuoption_id)
      on delete set null on update set null;

