CREATE SCHEMA MANTISDB;

CREATE TABLE MANTISDB.mantis_config_table (
config_id                VARCHAR(64) NOT NULL,
project_id               INTEGER DEFAULT 0 NOT NULL,
user_id                  INTEGER DEFAULT 0 NOT NULL,
access_reqd              INTEGER DEFAULT 0,
type                     INTEGER DEFAULT 90,
value                    CLOB NOT NULL,
                 PRIMARY KEY (config_id, project_id, user_id)
);

CREATE TABLE MANTISDB.mantis_bug_file_table (
id                       INTEGER GENERATED ALWAYS AS IDENTITY,
bug_id                   INTEGER DEFAULT 0 NOT NULL,
title                    VARCHAR(250) DEFAULT '' NOT NULL,
description              VARCHAR(250) DEFAULT '' NOT NULL,
diskfile                 VARCHAR(250) DEFAULT '' NOT NULL,
filename                 VARCHAR(250) DEFAULT '' NOT NULL,
folder                   VARCHAR(250) DEFAULT '' NOT NULL,
filesize                 INTEGER DEFAULT 0 NOT NULL,
file_type                VARCHAR(250) DEFAULT '' NOT NULL,
date_added               TIMESTAMP NOT NULL,
content                  BLOB NOT NULL,
                 PRIMARY KEY (id)
);

CREATE INDEX MANTISDB.idx_bug_file_bug_id ON MANTISDB.mantis_bug_file_table (bug_id);

CREATE TABLE MANTISDB.mantis_bug_history_table (
id                       INTEGER GENERATED ALWAYS AS IDENTITY,
user_id                  INTEGER DEFAULT 0 NOT NULL,
bug_id                   INTEGER DEFAULT 0 NOT NULL,
date_modified            TIMESTAMP NOT NULL,
field_name               VARCHAR(32) DEFAULT '' NOT NULL,
old_value                VARCHAR(128) DEFAULT '' NOT NULL,
new_value                VARCHAR(128) DEFAULT '' NOT NULL,
type                     SMALLINT DEFAULT 0 NOT NULL,
                 PRIMARY KEY (id)
);

CREATE INDEX MANTISDB.idx_bug_history_bug_id ON MANTISDB.mantis_bug_history_table (bug_id);

CREATE INDEX MANTISDB.idx_history_user_id ON MANTISDB.mantis_bug_history_table (user_id);

CREATE TABLE MANTISDB.mantis_bug_monitor_table (
user_id                  INTEGER DEFAULT 0 NOT NULL,
bug_id                   INTEGER DEFAULT 0 NOT NULL,
                 PRIMARY KEY (user_id, bug_id)
);

CREATE TABLE MANTISDB.mantis_bug_relationship_table (
id                       INTEGER GENERATED ALWAYS AS IDENTITY,
source_bug_id            INTEGER DEFAULT 0 NOT NULL,
destination_bug_id       INTEGER DEFAULT 0 NOT NULL,
relationship_type        SMALLINT DEFAULT 0 NOT NULL,
                 PRIMARY KEY (id)
);

CREATE INDEX MANTISDB.idx_relationship_source ON MANTISDB.mantis_bug_relationship_table (source_bug_id);

CREATE INDEX MANTISDB.idx_relationship_destination ON MANTISDB.mantis_bug_relationship_table (destination_bug_id);

CREATE TABLE MANTISDB.mantis_bug_table (
id                       INTEGER GENERATED ALWAYS AS IDENTITY,
project_id               INTEGER DEFAULT 0 NOT NULL,
reporter_id              INTEGER DEFAULT 0 NOT NULL,
handler_id               INTEGER DEFAULT 0 NOT NULL,
duplicate_id             INTEGER DEFAULT 0 NOT NULL,
priority                 SMALLINT DEFAULT 30 NOT NULL,
severity                 SMALLINT DEFAULT 50 NOT NULL,
reproducibility          SMALLINT DEFAULT 10 NOT NULL,
status                   SMALLINT DEFAULT 10 NOT NULL,
resolution               SMALLINT DEFAULT 10 NOT NULL,
projection               SMALLINT DEFAULT 10 NOT NULL,
category                 VARCHAR(64) DEFAULT '' NOT NULL,
date_submitted           TIMESTAMP DEFAULT '19700101000000' NOT NULL,
last_updated             TIMESTAMP DEFAULT '19700101000000' NOT NULL,
eta                      SMALLINT DEFAULT 10 NOT NULL,
bug_text_id              INTEGER DEFAULT 0 NOT NULL,
os                       VARCHAR(32) DEFAULT '' NOT NULL,
os_build                 VARCHAR(32) DEFAULT '' NOT NULL,
platform                 VARCHAR(32) DEFAULT '' NOT NULL,
version                  VARCHAR(64) DEFAULT '' NOT NULL,
fixed_in_version         VARCHAR(64) DEFAULT '' NOT NULL,
build                    VARCHAR(32) DEFAULT '' NOT NULL,
profile_id               INTEGER DEFAULT 0 NOT NULL,
view_state               SMALLINT DEFAULT 10 NOT NULL,
summary                  VARCHAR(128) DEFAULT '' NOT NULL,
sponsorship_total        INTEGER DEFAULT 0 NOT NULL,
sticky                   SMALLINT DEFAULT 0 NOT NULL,
                 PRIMARY KEY (id)
);


CREATE INDEX MANTISDB.idx_bug_sponsorship_total ON MANTISDB.mantis_bug_table (sponsorship_total);

CREATE INDEX MANTISDB.idx_bug_fixed_in_version ON MANTISDB.mantis_bug_table (fixed_in_version);

CREATE INDEX MANTISDB.idx_bug_status ON MANTISDB.mantis_bug_table (status);

CREATE INDEX MANTISDB.idx_project ON MANTISDB.mantis_bug_table (project_id);

CREATE TABLE MANTISDB.mantis_bug_text_table (
id                       INTEGER GENERATED ALWAYS AS IDENTITY,
description              CLOB NOT NULL,
steps_to_reproduce       CLOB NOT NULL,
additional_information   CLOB NOT NULL,
                 PRIMARY KEY (id)
);

CREATE TABLE MANTISDB.mantis_bugnote_table (
id                       INTEGER GENERATED ALWAYS AS IDENTITY,
bug_id                   INTEGER DEFAULT 0 NOT NULL,
reporter_id              INTEGER DEFAULT 0 NOT NULL,
bugnote_text_id          INTEGER DEFAULT 0 NOT NULL,
view_state               SMALLINT DEFAULT 10 NOT NULL,
date_submitted           TIMESTAMP DEFAULT '19700101000000' NOT NULL,
last_modified            TIMESTAMP DEFAULT '19700101000000' NOT NULL,
note_type                INTEGER DEFAULT 0,
note_attr                VARCHAR(250) DEFAULT '',
                 PRIMARY KEY (id)
);


CREATE INDEX MANTISDB.idx_bug ON MANTISDB.mantis_bugnote_table (bug_id);

CREATE INDEX MANTISDB.idx_last_mod ON MANTISDB.mantis_bugnote_table (last_modified);

CREATE TABLE MANTISDB.mantis_bugnote_text_table (
id                       INTEGER GENERATED ALWAYS AS IDENTITY,
note                     CLOB NOT NULL,
                 PRIMARY KEY (id)
);

CREATE TABLE MANTISDB.mantis_custom_field_project_table (
field_id                 INTEGER DEFAULT 0 NOT NULL,
project_id               INTEGER DEFAULT 0 NOT NULL,
sequence                 SMALLINT DEFAULT 0 NOT NULL,
                 PRIMARY KEY (field_id, project_id)
);

CREATE TABLE MANTISDB.mantis_custom_field_string_table (
field_id                 INTEGER DEFAULT 0 NOT NULL,
bug_id                   INTEGER DEFAULT 0 NOT NULL,
value                    VARCHAR(255) DEFAULT '' NOT NULL,
                 PRIMARY KEY (field_id, bug_id)
);


CREATE INDEX MANTISDB.idx_custom_field_bug ON MANTISDB.mantis_custom_field_string_table (bug_id);

CREATE TABLE MANTISDB.mantis_custom_field_table (
id                       INTEGER GENERATED ALWAYS AS IDENTITY,
name                     VARCHAR(64) DEFAULT '' NOT NULL,
type                     SMALLINT DEFAULT 0 NOT NULL,
possible_values          VARCHAR(255) DEFAULT '' NOT NULL,
default_value            VARCHAR(255) DEFAULT '' NOT NULL,
valid_regexp             VARCHAR(255) DEFAULT '' NOT NULL,
access_level_r           SMALLINT DEFAULT 0 NOT NULL,
access_level_rw          SMALLINT DEFAULT 0 NOT NULL,
length_min               INTEGER DEFAULT 0 NOT NULL,
length_max               INTEGER DEFAULT 0 NOT NULL,
advanced                 SMALLINT DEFAULT 0 NOT NULL,
require_report           SMALLINT DEFAULT 0 NOT NULL,
require_update           SMALLINT DEFAULT 0 NOT NULL,
display_report           SMALLINT DEFAULT 0 NOT NULL,
display_update           SMALLINT DEFAULT 1 NOT NULL,
require_resolved         SMALLINT DEFAULT 0 NOT NULL,
display_resolved         SMALLINT DEFAULT 0 NOT NULL,
display_closed           SMALLINT DEFAULT 0 NOT NULL,
require_closed           SMALLINT DEFAULT 0 NOT NULL,
                 PRIMARY KEY (id)
);

CREATE INDEX MANTISDB.idx_custom_field_name ON MANTISDB.mantis_custom_field_table (name);

CREATE TABLE MANTISDB.mantis_filters_table (
id                       INTEGER GENERATED ALWAYS AS IDENTITY,
user_id                  INTEGER DEFAULT 0 NOT NULL,
project_id               INTEGER DEFAULT 0 NOT NULL,
is_public                SMALLINT DEFAULT NULL,
name                     VARCHAR(64) DEFAULT '' NOT NULL,
filter_string            CLOB NOT NULL,
                 PRIMARY KEY (id)
);

CREATE TABLE MANTISDB.mantis_news_table (
id                       INTEGER GENERATED ALWAYS AS IDENTITY,
project_id               INTEGER DEFAULT 0 NOT NULL,
poster_id                INTEGER DEFAULT 0 NOT NULL,
date_posted              TIMESTAMP DEFAULT '19700101000000' NOT NULL,
last_modified            TIMESTAMP DEFAULT '19700101000000' NOT NULL,
view_state               SMALLINT DEFAULT 10 NOT NULL,
announcement             SMALLINT DEFAULT 0 NOT NULL,
headline                 VARCHAR(64) DEFAULT '' NOT NULL,
body                     CLOB NOT NULL,
                 PRIMARY KEY (id)
);

CREATE TABLE MANTISDB.mantis_project_category_table (
project_id               INTEGER DEFAULT 0 NOT NULL,
category                 VARCHAR(64) DEFAULT '' NOT NULL,
user_id                  INTEGER DEFAULT 0 NOT NULL,
                 PRIMARY KEY (project_id, category)
);

CREATE TABLE MANTISDB.mantis_project_file_table (
id                       INTEGER GENERATED ALWAYS AS IDENTITY,
project_id               INTEGER DEFAULT 0 NOT NULL,
title                    VARCHAR(250) DEFAULT '' NOT NULL,
description              VARCHAR(250) DEFAULT '' NOT NULL,
diskfile                 VARCHAR(250) DEFAULT '' NOT NULL,
filename                 VARCHAR(250) DEFAULT '' NOT NULL,
folder                   VARCHAR(250) DEFAULT '' NOT NULL,
filesize                 INTEGER DEFAULT 0 NOT NULL,
file_type                VARCHAR(250) DEFAULT '' NOT NULL,
date_added               TIMESTAMP DEFAULT '19700101000000' NOT NULL,
content                  BLOB NOT NULL,
                 PRIMARY KEY (id)
);

CREATE TABLE MANTISDB.mantis_project_hierarchy_table (
child_id                 INTEGER NOT NULL,
parent_id                INTEGER NOT NULL
);

CREATE TABLE MANTISDB.mantis_project_table (
id                       INTEGER GENERATED ALWAYS AS IDENTITY,
name                     VARCHAR(128) DEFAULT '' NOT NULL,
status                   SMALLINT DEFAULT 10 NOT NULL,
enabled                  SMALLINT DEFAULT 1 NOT NULL,
view_state               SMALLINT DEFAULT 10 NOT NULL,
access_min               SMALLINT DEFAULT 10 NOT NULL,
file_path                VARCHAR(250) DEFAULT '' NOT NULL,
description              CLOB NOT NULL,
                 PRIMARY KEY (id)
);

CREATE INDEX MANTISDB.idx_project_id ON MANTISDB.mantis_project_table (id);

CREATE UNIQUE INDEX MANTISDB.idx_project_name ON MANTISDB.mantis_project_table (name);

CREATE INDEX MANTISDB.idx_project_view ON MANTISDB.mantis_project_table (view_state);

CREATE TABLE MANTISDB.mantis_project_user_list_table (
project_id               INTEGER DEFAULT 0 NOT NULL,
user_id                  INTEGER DEFAULT 0 NOT NULL,
access_level             SMALLINT DEFAULT 10 NOT NULL,
                 PRIMARY KEY (project_id, user_id)
);

CREATE INDEX MANTISDB.idx_project_user ON MANTISDB.mantis_project_user_list_table (user_id);

CREATE TABLE MANTISDB.mantis_project_version_table (
id                       INTEGER GENERATED ALWAYS AS IDENTITY,
project_id               INTEGER DEFAULT 0 NOT NULL,
version                  VARCHAR(64) DEFAULT '' NOT NULL,
date_order               TIMESTAMP DEFAULT '19700101000000' NOT NULL,
description              CLOB NOT NULL,
released                 SMALLINT DEFAULT 1 NOT NULL,
                 PRIMARY KEY (id)
);

CREATE UNIQUE INDEX MANTISDB.idx_project_version ON MANTISDB.mantis_project_version_table (project_id, version);

CREATE TABLE MANTISDB.mantis_sponsorship_table (
id                       INTEGER GENERATED ALWAYS AS IDENTITY,
bug_id                   INTEGER DEFAULT 0 NOT NULL,
user_id                  INTEGER DEFAULT 0 NOT NULL,
amount                   INTEGER DEFAULT 0 NOT NULL,
logo                     VARCHAR(128) DEFAULT '' NOT NULL,
url                      VARCHAR(128) DEFAULT '' NOT NULL,
paid                     SMALLINT DEFAULT 0 NOT NULL,
date_submitted           TIMESTAMP DEFAULT '19700101000000' NOT NULL,
last_updated             TIMESTAMP DEFAULT '19700101000000' NOT NULL,
                 PRIMARY KEY (id)
);

CREATE INDEX MANTISDB.idx_sponsorship_bug_id ON MANTISDB.mantis_sponsorship_table (bug_id);

CREATE INDEX MANTISDB.idx_sponsorship_user_id ON MANTISDB.mantis_sponsorship_table (user_id);

CREATE TABLE MANTISDB.mantis_tokens_table (
id                       INTEGER GENERATED ALWAYS AS IDENTITY,
owner                    INTEGER NOT NULL,
type                     INTEGER NOT NULL,
timestamp                TIMESTAMP NOT NULL,
expiry                   TIMESTAMP,
value                    CLOB NOT NULL,
                 PRIMARY KEY (id)
);

CREATE TABLE MANTISDB.mantis_user_pref_table (
id                       INTEGER GENERATED ALWAYS AS IDENTITY,
user_id                  INTEGER DEFAULT 0 NOT NULL,
project_id               INTEGER DEFAULT 0 NOT NULL,
default_profile          INTEGER DEFAULT 0 NOT NULL,
default_project          INTEGER DEFAULT 0 NOT NULL,
advanced_report          SMALLINT DEFAULT 0 NOT NULL,
advanced_view            SMALLINT DEFAULT 0 NOT NULL,
advanced_update          SMALLINT DEFAULT 0 NOT NULL,
refresh_delay            INTEGER DEFAULT 0 NOT NULL,
redirect_delay           SMALLINT DEFAULT 0 NOT NULL,
bugnote_order            VARCHAR(4) DEFAULT 'ASC' NOT NULL,
email_on_new             SMALLINT DEFAULT 0 NOT NULL,
email_on_assigned        SMALLINT DEFAULT 0 NOT NULL,
email_on_feedback        SMALLINT DEFAULT 0 NOT NULL,
email_on_resolved        SMALLINT DEFAULT 0 NOT NULL,
email_on_closed          SMALLINT DEFAULT 0 NOT NULL,
email_on_reopened        SMALLINT DEFAULT 0 NOT NULL,
email_on_bugnote         SMALLINT DEFAULT 0 NOT NULL,
email_on_status          SMALLINT DEFAULT 0 NOT NULL,
email_on_priority        SMALLINT DEFAULT 0 NOT NULL,
email_on_priority_min_severity SMALLINT DEFAULT 10 NOT NULL,
email_on_status_min_severity SMALLINT DEFAULT 10 NOT NULL,
email_on_bugnote_min_severity SMALLINT DEFAULT 10 NOT NULL,
email_on_reopened_min_severity SMALLINT DEFAULT 10 NOT NULL,
email_on_closed_min_severity SMALLINT DEFAULT 10 NOT NULL,
email_on_resolved_min_severity SMALLINT DEFAULT 10 NOT NULL,
email_on_feedback_min_severity SMALLINT DEFAULT 10 NOT NULL,
email_on_assigned_min_severity SMALLINT DEFAULT 10 NOT NULL,
email_on_new_min_severity SMALLINT DEFAULT 10 NOT NULL,
email_bugnote_limit      SMALLINT DEFAULT 0 NOT NULL,
language                 VARCHAR(32) DEFAULT 'english' NOT NULL,
                 PRIMARY KEY (id)
);

CREATE TABLE MANTISDB.mantis_user_print_pref_table (
user_id                  INTEGER DEFAULT 0 NOT NULL,
print_pref               VARCHAR(27) DEFAULT '' NOT NULL,
                 PRIMARY KEY (user_id)
);

CREATE TABLE MANTISDB.mantis_user_profile_table (
id                       INTEGER GENERATED ALWAYS AS IDENTITY,
user_id                  INTEGER DEFAULT 0 NOT NULL,
platform                 VARCHAR(32) DEFAULT '' NOT NULL,
os                       VARCHAR(32) DEFAULT '' NOT NULL,
os_build                 VARCHAR(32) DEFAULT '' NOT NULL,
description              CLOB NOT NULL,
                 PRIMARY KEY (id)
);

CREATE TABLE MANTISDB.mantis_user_table (
id                       INTEGER GENERATED ALWAYS AS IDENTITY,
username                 VARCHAR(32) DEFAULT '' NOT NULL,
realname                 VARCHAR(64) DEFAULT '' NOT NULL,
email                    VARCHAR(64) DEFAULT '' NOT NULL,
password                 VARCHAR(32) DEFAULT '' NOT NULL,
date_created             TIMESTAMP DEFAULT '19700101000000' NOT NULL,
last_visit               TIMESTAMP DEFAULT '19700101000000' NOT NULL,
enabled                  SMALLINT DEFAULT 1 NOT NULL,
protected                SMALLINT DEFAULT 0 NOT NULL,
access_level             SMALLINT DEFAULT 10 NOT NULL,
login_count              INTEGER DEFAULT 0 NOT NULL,
lost_password_request_count SMALLINT DEFAULT 0 NOT NULL,
failed_login_count       SMALLINT DEFAULT 0 NOT NULL,
cookie_string            VARCHAR(64) DEFAULT '' NOT NULL,
                 PRIMARY KEY (id)
);

CREATE UNIQUE INDEX MANTISDB.idx_user_cookie_string ON MANTISDB.mantis_user_table (cookie_string);

CREATE UNIQUE INDEX MANTISDB.idx_user_username ON MANTISDB.mantis_user_table (username);

CREATE INDEX MANTISDB.idx_enable ON MANTISDB.mantis_user_table (enabled);

CREATE INDEX MANTISDB.idx_access ON MANTISDB.mantis_user_table (access_level);

INSERT INTO MANTISDB.mantis_user_table(username, realname, email, password, date_created, last_visit, enabled, protected, access_level, login_count, lost_password_request_count, failed_login_count, cookie_string) VALUES
        ('administrator', '', 'root@localhost', '63a9f0ea7bb98050796b649e85481845', '20130104172927', '20130104172927', '1', '0', 90, 3, 0, 0, '43db152a2f4a60a8cc3b2da68fae0df3bc79da57b3ea83c2eb577c613b5d4df0');

CREATE TABLE MANTISDB.mantis_email_table (
email_id                 INTEGER GENERATED ALWAYS AS IDENTITY,
email                    VARCHAR(64) DEFAULT '' NOT NULL,
subject                  VARCHAR(250) DEFAULT '' NOT NULL,
submitted                TIMESTAMP DEFAULT '19700101000000' NOT NULL,
metadata                 CLOB NOT NULL,
body                     CLOB NOT NULL,
                 PRIMARY KEY (email_id)
);

CREATE INDEX MANTISDB.idx_email_id ON MANTISDB.mantis_email_table (email_id);

ALTER TABLE MANTISDB.mantis_bug_table ADD target_version VARCHAR(64) DEFAULT '' NOT NULL;

ALTER TABLE MANTISDB.mantis_bugnote_table ADD time_tracking INTEGER DEFAULT 0 NOT NULL;

CREATE INDEX MANTISDB.idx_diskfile ON MANTISDB.mantis_bug_file_table (diskfile);

CREATE TABLE MANTISDB.mantis_tag_table (
id                       INTEGER GENERATED ALWAYS AS IDENTITY,
user_id                  INTEGER DEFAULT 0 NOT NULL,
name                     VARCHAR(100) DEFAULT '' NOT NULL,
description              CLOB NOT NULL,
date_created             TIMESTAMP DEFAULT '19700101000000' NOT NULL,
date_updated             TIMESTAMP DEFAULT '19700101000000' NOT NULL,
                 PRIMARY KEY (id, name)
);

CREATE TABLE MANTISDB.mantis_bug_tag_table (
bug_id                   INTEGER DEFAULT 0 NOT NULL,
tag_id                   INTEGER DEFAULT 0 NOT NULL,
user_id                  INTEGER DEFAULT 0 NOT NULL,
date_attached            TIMESTAMP DEFAULT '19700101000000' NOT NULL,
                 PRIMARY KEY (bug_id, tag_id)
);

CREATE INDEX MANTISDB.idx_typeowner ON MANTISDB.mantis_tokens_table (type, owner);

CREATE TABLE MANTISDB.mantis_plugin_table (
basename                 VARCHAR(40) NOT NULL,
enabled                  SMALLINT DEFAULT 0 NOT NULL,
                 PRIMARY KEY (basename)
);

CREATE TABLE MANTISDB.mantis_category_table (
id                       INTEGER GENERATED ALWAYS AS IDENTITY,
project_id               INTEGER DEFAULT 0 NOT NULL,
user_id                  INTEGER DEFAULT 0 NOT NULL,
name                     VARCHAR(128) DEFAULT '' NOT NULL,
status                   INTEGER DEFAULT 0 NOT NULL,
                 PRIMARY KEY (id)
);


CREATE UNIQUE INDEX MANTISDB.idx_category_project_name ON MANTISDB.mantis_category_table (project_id, name);

INSERT INTO MANTISDB.mantis_category_table
	( project_id, user_id, name, status ) VALUES
	( '0', '0', 'General', '0' ) ;

ALTER TABLE MANTISDB.mantis_bug_table ADD category_id INTEGER DEFAULT 1 NOT NULL;

DROP TABLE MANTISDB.mantis_project_category_table;

ALTER TABLE MANTISDB.mantis_project_table ADD category_id INTEGER DEFAULT 1 NOT NULL;

INSERT INTO MANTISDB.mantis_plugin_table
	( basename, enabled ) VALUES
	( 'MantisCoreFormatting', '1' );

	
ALTER TABLE MANTISDB.mantis_project_table ADD inherit_global INTEGER DEFAULT 0 NOT NULL;

ALTER TABLE MANTISDB.mantis_project_hierarchy_table ADD inherit_parent INTEGER DEFAULT 0 NOT NULL;

ALTER TABLE MANTISDB.mantis_plugin_table ADD protected SMALLINT DEFAULT 0 NOT NULL;

ALTER TABLE MANTISDB.mantis_plugin_table ADD priority INTEGER DEFAULT 3 NOT NULL;

ALTER TABLE MANTISDB.mantis_project_version_table ADD obsolete SMALLINT DEFAULT 0 NOT NULL;

ALTER TABLE MANTISDB.mantis_bug_table ADD due_date TIMESTAMP DEFAULT '19700101000000' NOT NULL;

ALTER TABLE MANTISDB.mantis_custom_field_table ADD filter_by SMALLINT DEFAULT 1 NOT NULL;

CREATE TABLE MANTISDB.mantis_bug_revision_table (
id                       INTEGER GENERATED ALWAYS AS IDENTITY,
bug_id                   INTEGER NOT NULL,
bugnote_id               INTEGER DEFAULT 0 NOT NULL,
user_id                  INTEGER NOT NULL,
timestamp                TIMESTAMP DEFAULT '19700101000000' NOT NULL,
type                     INTEGER NOT NULL,
value                    CLOB NOT NULL,
                 PRIMARY KEY (id)
);


CREATE INDEX MANTISDB.idx_bug_rev_id_time ON MANTISDB.mantis_bug_revision_table (bug_id, timestamp);

CREATE INDEX MANTISDB.idx_bug_rev_type ON MANTISDB.mantis_bug_revision_table (type);

cl: ADDRPYLE SEQNBR(1500) MSGID(CPA32B2) RPY('I'); 
cl: CHGJOB INQMSGRPY(*SYSRPYL); 

ALTER TABLE MANTISDB.mantis_bug_table DROP date_submitted;
ALTER TABLE MANTISDB.mantis_bug_table ADD date_submitted INTEGER DEFAULT 1 NOT NULL;

ALTER TABLE MANTISDB.mantis_bug_table DROP due_date;
ALTER TABLE MANTISDB.mantis_bug_table ADD due_date INTEGER DEFAULT 1 NOT NULL;

ALTER TABLE MANTISDB.mantis_bug_table DROP last_updated;
ALTER TABLE MANTISDB.mantis_bug_table ADD last_updated INTEGER DEFAULT 1 NOT NULL;

ALTER TABLE MANTISDB.mantis_bugnote_table DROP last_modified;
ALTER TABLE MANTISDB.mantis_bugnote_table ADD last_modified INTEGER DEFAULT 1 NOT NULL;

ALTER TABLE MANTISDB.mantis_bugnote_table DROP date_submitted;
ALTER TABLE MANTISDB.mantis_bugnote_table ADD date_submitted INTEGER DEFAULT 1 NOT NULL;

CREATE INDEX MANTISDB.idx_last_mod ON MANTISDB.mantis_bugnote_table (last_modified);

ALTER TABLE MANTISDB.mantis_bug_file_table DROP date_added;
ALTER TABLE MANTISDB.mantis_bug_file_table ADD date_added INTEGER DEFAULT 1 NOT NULL;

ALTER TABLE MANTISDB.mantis_project_file_table DROP date_added;
ALTER TABLE MANTISDB.mantis_project_file_table ADD date_added INTEGER DEFAULT 1 NOT NULL;

ALTER TABLE MANTISDB.mantis_bug_history_table DROP date_modified;
ALTER TABLE MANTISDB.mantis_bug_history_table ADD date_modified INTEGER DEFAULT 1 NOT NULL;

ALTER TABLE MANTISDB.mantis_user_table DROP last_visit;
ALTER TABLE MANTISDB.mantis_user_table ADD last_visit INTEGER DEFAULT 1 NOT NULL;

ALTER TABLE MANTISDB.mantis_user_table DROP date_created;
ALTER TABLE MANTISDB.mantis_user_table ADD date_created INTEGER DEFAULT 1 NOT NULL;

ALTER TABLE MANTISDB.mantis_email_table DROP submitted;
ALTER TABLE MANTISDB.mantis_email_table ADD submitted INTEGER DEFAULT 1 NOT NULL;

ALTER TABLE MANTISDB.mantis_tag_table DROP date_created;
ALTER TABLE MANTISDB.mantis_tag_table ADD date_created INTEGER DEFAULT 1 NOT NULL;

ALTER TABLE MANTISDB.mantis_tag_table DROP date_updated;
ALTER TABLE MANTISDB.mantis_tag_table ADD date_updated INTEGER DEFAULT 1 NOT NULL;

ALTER TABLE MANTISDB.mantis_bug_tag_table DROP date_attached;
ALTER TABLE MANTISDB.mantis_bug_tag_table ADD date_attached INTEGER DEFAULT 1 NOT NULL;

ALTER TABLE MANTISDB.mantis_tokens_table DROP COLUMN timestamp;
ALTER TABLE MANTISDB.mantis_tokens_table ADD COLUMN timestamp INTEGER DEFAULT 1 NOT NULL;

ALTER TABLE MANTISDB.mantis_tokens_table DROP COLUMN expiry;
ALTER TABLE MANTISDB.mantis_tokens_table ADD COLUMN expiry INTEGER DEFAULT 1 NOT NULL;

ALTER TABLE MANTISDB.mantis_news_table DROP last_modified;
ALTER TABLE MANTISDB.mantis_news_table ADD last_modified INTEGER DEFAULT 1 NOT NULL;

ALTER TABLE MANTISDB.mantis_news_table DROP date_posted;
ALTER TABLE MANTISDB.mantis_news_table ADD date_posted INTEGER DEFAULT 1 NOT NULL;

ALTER TABLE MANTISDB.mantis_bug_revision_table DROP COLUMN timestamp;
ALTER TABLE MANTISDB.mantis_bug_revision_table ADD COLUMN timestamp INTEGER DEFAULT 1 NOT NULL;

CREATE INDEX MANTISDB.idx_bug_rev_id_time ON MANTISDB.mantis_bug_revision_table (bug_id, timestamp);

ALTER TABLE MANTISDB.mantis_user_pref_table ADD timezone VARCHAR(32) DEFAULT '' NOT NULL;

ALTER TABLE MANTISDB.mantis_project_version_table DROP date_order;
ALTER TABLE MANTISDB.mantis_project_version_table ADD date_order INTEGER DEFAULT 1 NOT NULL;

ALTER TABLE MANTISDB.mantis_sponsorship_table DROP date_submitted;
ALTER TABLE MANTISDB.mantis_sponsorship_table ADD date_submitted INTEGER DEFAULT 1 NOT NULL;

ALTER TABLE MANTISDB.mantis_sponsorship_table DROP last_updated;
ALTER TABLE MANTISDB.mantis_sponsorship_table ADD last_updated INTEGER DEFAULT 1 NOT NULL;

ALTER TABLE MANTISDB.mantis_project_file_table ADD user_id INTEGER DEFAULT 0 NOT NULL;

ALTER TABLE MANTISDB.mantis_bug_file_table ADD user_id INTEGER DEFAULT 0 NOT NULL;

CREATE INDEX MANTISDB.idx_project_hierarchy_child_id ON MANTISDB.mantis_project_hierarchy_table (child_id);

CREATE INDEX MANTISDB.idx_project_hierarchy_parent_id ON MANTISDB.mantis_project_hierarchy_table (parent_id);

CREATE INDEX MANTISDB.idx_tag_name ON MANTISDB.mantis_tag_table (name);

CREATE INDEX MANTISDB.idx_bug_tag_tag_id ON MANTISDB.mantis_bug_tag_table (tag_id);

INSERT INTO MANTISDB.mantis_config_table ( value, type, access_reqd, config_id, project_id, user_id ) VALUES ('183', 1, 90, 'database_version', 0, 0 );