View Issue Details

WikiJump to Notes
IDProjectCategoryView StatusDate SubmittedLast Update
0014852mantisbtinstallationpublic2012-10-23 00:132014-12-08 00:33
Reporterniki Assigned Todregad  
PrioritynormalSeverityminorReproducibilityhave not tried
Status closedResolutionfixed 
Platformx86_64OSArchLinux 
Product Version1.2.11 
Target Version1.3.0-beta.1Fixed in Version1.3.0-beta.1 
Summary0014852: Installation fails during install with postgres as DB
Description

During installation of mantis in a PostgresDB, I got this error during install:

ERROR: column "redirect_delay" cannot be cast automatically to type integer
SQL state: 42804
Hint: Specify a USING expression to perform the conversion.

To resolve this, I had to make the following chnages to the SQL to get the install to finish correctly:

diff -Naur mantis-0.sql mantis-1.sql
--- mantis-0.sql 2012-10-23 05:47:41.824642741 +0200
+++ mantis-1.sql 2012-10-23 05:59:24.236579980 +0200
@@ -300,7 +300,7 @@
advanced_view BOOLEAN DEFAULT '0' NOT NULL,
advanced_update BOOLEAN DEFAULT '0' NOT NULL,
refresh_delay INTEGER DEFAULT 0 NOT NULL,
-redirect_delay BOOLEAN DEFAULT '0' NOT NULL,
+redirect_delay INTEGER DEFAULT 0 NOT NULL,
bugnote_order VARCHAR(4) DEFAULT 'ASC' NOT NULL,
email_on_new BOOLEAN DEFAULT '0' NOT NULL,
email_on_assigned BOOLEAN DEFAULT '0' NOT NULL,
@@ -442,12 +442,6 @@
PRIMARY KEY (basename)
);

-ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay TYPE INTEGER;

-ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay SET DEFAULT 0;

-ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay SET NOT NULL;

ALTER TABLE mantis_custom_field_table ALTER COLUMN possible_values TYPE TEXT;

ALTER TABLE mantis_custom_field_table ALTER COLUMN possible_values SET DEFAULT '';

Steps To Reproduce

During normal install choose to run the SQL manually, and copy/paste the listed SQL in to PGAdmin (or use the commandline), and you get the above mentioned error during execution.

TagsNo tags attached.
Attached Files
mantis.err (322 bytes)
mantis-0.sql (33,393 bytes)    
begin;

CREATE TABLE 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                    TEXT NOT NULL,
                 PRIMARY KEY (config_id, project_id, user_id)
);

CREATE INDEX idx_config ON mantis_config_table (config_id);

CREATE TABLE mantis_bug_file_table (
id                       SERIAL,
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 DEFAULT '1970-01-01 00:00:01' NOT NULL,
content                  BYTEA NOT NULL,
                 PRIMARY KEY (id)
);

CREATE INDEX idx_bug_file_bug_id ON mantis_bug_file_table (bug_id);

CREATE TABLE mantis_bug_history_table (
id                       SERIAL,
user_id                  INTEGER DEFAULT 0 NOT NULL,
bug_id                   INTEGER DEFAULT 0 NOT NULL,
date_modified            TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
field_name               VARCHAR(32) DEFAULT '' NOT NULL,
old_value                VARCHAR(128) DEFAULT '' NOT NULL,
new_value                VARCHAR(128) DEFAULT '' NOT NULL,
type                     INT2 DEFAULT 0 NOT NULL,
                 PRIMARY KEY (id)
);

CREATE INDEX idx_bug_history_bug_id ON mantis_bug_history_table (bug_id);

CREATE INDEX idx_history_user_id ON mantis_bug_history_table (user_id);

CREATE TABLE 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 mantis_bug_relationship_table (
id                       SERIAL,
source_bug_id            INTEGER DEFAULT 0 NOT NULL,
destination_bug_id       INTEGER DEFAULT 0 NOT NULL,
relationship_type        INT2 DEFAULT 0 NOT NULL,
                 PRIMARY KEY (id)
);

CREATE INDEX idx_relationship_source ON mantis_bug_relationship_table (source_bug_id);

CREATE INDEX idx_relationship_destination ON mantis_bug_relationship_table (destination_bug_id);

CREATE TABLE mantis_bug_table (
id                       SERIAL,
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                 INT2 DEFAULT 30 NOT NULL,
severity                 INT2 DEFAULT 50 NOT NULL,
reproducibility          INT2 DEFAULT 10 NOT NULL,
status                   INT2 DEFAULT 10 NOT NULL,
resolution               INT2 DEFAULT 10 NOT NULL,
projection               INT2 DEFAULT 10 NOT NULL,
category                 VARCHAR(64) DEFAULT '' NOT NULL,
date_submitted           TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
last_updated             TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
eta                      INT2 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               INT2 DEFAULT 10 NOT NULL,
summary                  VARCHAR(128) DEFAULT '' NOT NULL,
sponsorship_total        INTEGER DEFAULT 0 NOT NULL,
sticky                   BOOLEAN DEFAULT '0' NOT NULL,
                 PRIMARY KEY (id)
);

CREATE INDEX idx_bug_sponsorship_total ON mantis_bug_table (sponsorship_total);

CREATE INDEX idx_bug_fixed_in_version ON mantis_bug_table (fixed_in_version);

CREATE INDEX idx_bug_status ON mantis_bug_table (status);

CREATE INDEX idx_project ON mantis_bug_table (project_id);

CREATE TABLE mantis_bug_text_table (
id                       SERIAL,
description              TEXT NOT NULL,
steps_to_reproduce       TEXT NOT NULL,
additional_information   TEXT NOT NULL,
                 PRIMARY KEY (id)
);

CREATE TABLE mantis_bugnote_table (
id                       SERIAL,
bug_id                   INTEGER DEFAULT 0 NOT NULL,
reporter_id              INTEGER DEFAULT 0 NOT NULL,
bugnote_text_id          INTEGER DEFAULT 0 NOT NULL,
view_state               INT2 DEFAULT 10 NOT NULL,
date_submitted           TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
last_modified            TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
note_type                INTEGER DEFAULT 0,
note_attr                VARCHAR(250) DEFAULT '',
                 PRIMARY KEY (id)
);

CREATE INDEX idx_bug ON mantis_bugnote_table (bug_id);

CREATE INDEX idx_last_mod ON mantis_bugnote_table (last_modified);

CREATE TABLE mantis_bugnote_text_table (
id                       SERIAL,
note                     TEXT NOT NULL,
                 PRIMARY KEY (id)
);

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

CREATE TABLE 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 idx_custom_field_bug ON mantis_custom_field_string_table (bug_id);

CREATE TABLE mantis_custom_field_table (
id                       SERIAL,
name                     VARCHAR(64) DEFAULT '' NOT NULL,
type                     INT2 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           INT2 DEFAULT 0 NOT NULL,
access_level_rw          INT2 DEFAULT 0 NOT NULL,
length_min               INTEGER DEFAULT 0 NOT NULL,
length_max               INTEGER DEFAULT 0 NOT NULL,
advanced                 BOOLEAN DEFAULT '0' NOT NULL,
require_report           BOOLEAN DEFAULT '0' NOT NULL,
require_update           BOOLEAN DEFAULT '0' NOT NULL,
display_report           BOOLEAN DEFAULT '0' NOT NULL,
display_update           BOOLEAN DEFAULT '1' NOT NULL,
require_resolved         BOOLEAN DEFAULT '0' NOT NULL,
display_resolved         BOOLEAN DEFAULT '0' NOT NULL,
display_closed           BOOLEAN DEFAULT '0' NOT NULL,
require_closed           BOOLEAN DEFAULT '0' NOT NULL,
                 PRIMARY KEY (id)
);

CREATE INDEX idx_custom_field_name ON mantis_custom_field_table (name);

CREATE TABLE mantis_filters_table (
id                       SERIAL,
user_id                  INTEGER DEFAULT 0 NOT NULL,
project_id               INTEGER DEFAULT 0 NOT NULL,
is_public                BOOLEAN DEFAULT NULL,
name                     VARCHAR(64) DEFAULT '' NOT NULL,
filter_string            TEXT NOT NULL,
                 PRIMARY KEY (id)
);

CREATE TABLE mantis_news_table (
id                       SERIAL,
project_id               INTEGER DEFAULT 0 NOT NULL,
poster_id                INTEGER DEFAULT 0 NOT NULL,
date_posted              TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
last_modified            TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
view_state               INT2 DEFAULT 10 NOT NULL,
announcement             BOOLEAN DEFAULT '0' NOT NULL,
headline                 VARCHAR(64) DEFAULT '' NOT NULL,
body                     TEXT NOT NULL,
                 PRIMARY KEY (id)
);

CREATE TABLE 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 mantis_project_file_table (
id                       SERIAL,
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 '1970-01-01 00:00:01' NOT NULL,
content                  BYTEA NOT NULL,
                 PRIMARY KEY (id)
);

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

CREATE TABLE mantis_project_table (
id                       SERIAL,
name                     VARCHAR(128) DEFAULT '' NOT NULL,
status                   INT2 DEFAULT 10 NOT NULL,
enabled                  BOOLEAN DEFAULT '1' NOT NULL,
view_state               INT2 DEFAULT 10 NOT NULL,
access_min               INT2 DEFAULT 10 NOT NULL,
file_path                VARCHAR(250) DEFAULT '' NOT NULL,
description              TEXT NOT NULL,
                 PRIMARY KEY (id)
);

CREATE INDEX idx_project_id ON mantis_project_table (id);

CREATE UNIQUE INDEX idx_project_name ON mantis_project_table (name);

CREATE INDEX idx_project_view ON mantis_project_table (view_state);

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

CREATE INDEX idx_project_user ON mantis_project_user_list_table (user_id);

CREATE TABLE mantis_project_version_table (
id                       SERIAL,
project_id               INTEGER DEFAULT 0 NOT NULL,
version                  VARCHAR(64) DEFAULT '' NOT NULL,
date_order               TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
description              TEXT NOT NULL,
released                 BOOLEAN DEFAULT '1' NOT NULL,
                 PRIMARY KEY (id)
);

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

CREATE TABLE mantis_sponsorship_table (
id                       SERIAL,
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                     BOOLEAN DEFAULT '0' NOT NULL,
date_submitted           TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
last_updated             TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
                 PRIMARY KEY (id)
);

CREATE INDEX idx_sponsorship_bug_id ON mantis_sponsorship_table (bug_id);

CREATE INDEX idx_sponsorship_user_id ON mantis_sponsorship_table (user_id);

CREATE TABLE mantis_tokens_table (
id                       SERIAL,
owner                    INTEGER NOT NULL,
type                     INTEGER NOT NULL,
timestamp                TIMESTAMP NOT NULL,
expiry                   TIMESTAMP,
value                    TEXT NOT NULL,
                 PRIMARY KEY (id)
);

CREATE TABLE mantis_user_pref_table (
id                       SERIAL,
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          BOOLEAN DEFAULT '0' NOT NULL,
advanced_view            BOOLEAN DEFAULT '0' NOT NULL,
advanced_update          BOOLEAN DEFAULT '0' NOT NULL,
refresh_delay            INTEGER DEFAULT 0 NOT NULL,
redirect_delay           BOOLEAN DEFAULT '0' NOT NULL,
bugnote_order            VARCHAR(4) DEFAULT 'ASC' NOT NULL,
email_on_new             BOOLEAN DEFAULT '0' NOT NULL,
email_on_assigned        BOOLEAN DEFAULT '0' NOT NULL,
email_on_feedback        BOOLEAN DEFAULT '0' NOT NULL,
email_on_resolved        BOOLEAN DEFAULT '0' NOT NULL,
email_on_closed          BOOLEAN DEFAULT '0' NOT NULL,
email_on_reopened        BOOLEAN DEFAULT '0' NOT NULL,
email_on_bugnote         BOOLEAN DEFAULT '0' NOT NULL,
email_on_status          BOOLEAN DEFAULT '0' NOT NULL,
email_on_priority        BOOLEAN DEFAULT '0' NOT NULL,
email_on_priority_min_severity INT2 DEFAULT 10 NOT NULL,
email_on_status_min_severity INT2 DEFAULT 10 NOT NULL,
email_on_bugnote_min_severity INT2 DEFAULT 10 NOT NULL,
email_on_reopened_min_severity INT2 DEFAULT 10 NOT NULL,
email_on_closed_min_severity INT2 DEFAULT 10 NOT NULL,
email_on_resolved_min_severity INT2 DEFAULT 10 NOT NULL,
email_on_feedback_min_severity INT2 DEFAULT 10 NOT NULL,
email_on_assigned_min_severity INT2 DEFAULT 10 NOT NULL,
email_on_new_min_severity INT2 DEFAULT 10 NOT NULL,
email_bugnote_limit      INT2 DEFAULT 0 NOT NULL,
language                 VARCHAR(32) DEFAULT 'english' NOT NULL,
                 PRIMARY KEY (id)
);

CREATE TABLE 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 mantis_user_profile_table (
id                       SERIAL,
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              TEXT NOT NULL,
                 PRIMARY KEY (id)
);

CREATE TABLE mantis_user_table (
id                       SERIAL,
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 '1970-01-01 00:00:01' NOT NULL,
last_visit               TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
enabled                  BOOLEAN DEFAULT '1' NOT NULL,
protected                BOOLEAN DEFAULT '0' NOT NULL,
access_level             INT2 DEFAULT 10 NOT NULL,
login_count              INTEGER DEFAULT 0 NOT NULL,
lost_password_request_count INT2 DEFAULT 0 NOT NULL,
failed_login_count       INT2 DEFAULT 0 NOT NULL,
cookie_string            VARCHAR(64) DEFAULT '' NOT NULL,
                 PRIMARY KEY (id)
);

CREATE UNIQUE INDEX idx_user_cookie_string ON mantis_user_table (cookie_string);

CREATE UNIQUE INDEX idx_user_username ON mantis_user_table (username);

CREATE INDEX idx_enable ON mantis_user_table (enabled);

CREATE INDEX idx_access ON mantis_user_table (access_level);

INSERT INTO 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', '2012-10-23 00:04:34', '2012-10-23 00:04:34', '1', '0', 90, 3, 0, 0, 'f9d294695143d7a069e33c88cfa87bb5ca06ce422f58f5da6fc55f07d297e7e5');

ALTER TABLE mantis_bug_history_table ALTER COLUMN old_value TYPE VARCHAR(255) ;

ALTER TABLE mantis_bug_history_table ALTER COLUMN old_value SET NOT NULL;

ALTER TABLE mantis_bug_history_table ALTER COLUMN new_value TYPE VARCHAR(255) ;

ALTER TABLE mantis_bug_history_table ALTER COLUMN new_value SET NOT NULL;

CREATE TABLE mantis_email_table (
email_id                 SERIAL,
email                    VARCHAR(64) DEFAULT '' NOT NULL,
subject                  VARCHAR(250) DEFAULT '' NOT NULL,
submitted                TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
metadata                 TEXT NOT NULL,
body                     TEXT NOT NULL,
                 PRIMARY KEY (email_id)
);

CREATE INDEX idx_email_id ON mantis_email_table (email_id);

ALTER TABLE mantis_bug_table ADD COLUMN target_version VARCHAR(64)  ;

UPDATE mantis_bug_table SET target_version='';

ALTER TABLE mantis_bug_table ALTER COLUMN target_version SET DEFAULT '';

ALTER TABLE mantis_bug_table ALTER COLUMN target_version SET NOT NULL;

ALTER TABLE mantis_bugnote_table ADD COLUMN time_tracking INTEGER  ;

UPDATE mantis_bugnote_table SET time_tracking=0;

ALTER TABLE mantis_bugnote_table ALTER COLUMN time_tracking SET DEFAULT 0;

ALTER TABLE mantis_bugnote_table ALTER COLUMN time_tracking SET NOT NULL;

CREATE INDEX idx_diskfile ON mantis_bug_file_table (diskfile);

ALTER TABLE mantis_user_print_pref_table ALTER COLUMN print_pref TYPE VARCHAR(64) ;

ALTER TABLE mantis_user_print_pref_table ALTER COLUMN print_pref SET NOT NULL;

ALTER TABLE mantis_bug_history_table ALTER COLUMN field_name TYPE VARCHAR(64) ;

ALTER TABLE mantis_bug_history_table ALTER COLUMN field_name SET NOT NULL;

CREATE TABLE mantis_tag_table (
id                       SERIAL,
user_id                  INTEGER DEFAULT 0 NOT NULL,
name                     VARCHAR(100) DEFAULT '' NOT NULL,
description              TEXT NOT NULL,
date_created             TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
date_updated             TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
                 PRIMARY KEY (id, name)
);

CREATE TABLE 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 '1970-01-01 00:00:01' NOT NULL,
                 PRIMARY KEY (bug_id, tag_id)
);

CREATE INDEX idx_typeowner ON mantis_tokens_table (type, owner);

CREATE TABLE mantis_plugin_table (
basename                 VARCHAR(40) NOT NULL,
enabled                  BOOLEAN DEFAULT '0' NOT NULL,
                 PRIMARY KEY (basename)
);

ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay TYPE INTEGER;

ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay SET DEFAULT 0;

ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay SET NOT NULL;

ALTER TABLE mantis_custom_field_table ALTER COLUMN possible_values TYPE TEXT;

ALTER TABLE mantis_custom_field_table ALTER COLUMN possible_values SET DEFAULT '';

ALTER TABLE mantis_custom_field_table ALTER COLUMN possible_values SET NOT NULL;

CREATE TABLE mantis_category_table (
id                       SERIAL,
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 idx_category_project_name ON mantis_category_table (project_id, name);

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

ALTER TABLE mantis_bug_table ADD COLUMN category_id INTEGER  ;

UPDATE mantis_bug_table SET category_id=1;

ALTER TABLE mantis_bug_table ALTER COLUMN category_id SET DEFAULT 1;

ALTER TABLE mantis_bug_table ALTER COLUMN category_id SET NOT NULL;

ALTER TABLE mantis_bug_table DROP COLUMN category;

DROP TABLE mantis_project_category_table CASCADE;

ALTER TABLE mantis_project_table ADD COLUMN category_id INTEGER  ;

UPDATE mantis_project_table SET category_id=1;

ALTER TABLE mantis_project_table ALTER COLUMN category_id SET DEFAULT 1;

ALTER TABLE mantis_project_table ALTER COLUMN category_id SET NOT NULL;

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

ALTER TABLE mantis_project_table ADD COLUMN inherit_global INTEGER  ;

UPDATE mantis_project_table SET inherit_global=0;

ALTER TABLE mantis_project_table ALTER COLUMN inherit_global SET DEFAULT 0;

ALTER TABLE mantis_project_table ALTER COLUMN inherit_global SET NOT NULL;

ALTER TABLE mantis_project_hierarchy_table ADD COLUMN inherit_parent INTEGER  ;

UPDATE mantis_project_hierarchy_table SET inherit_parent=0;

ALTER TABLE mantis_project_hierarchy_table ALTER COLUMN inherit_parent SET DEFAULT 0;

ALTER TABLE mantis_project_hierarchy_table ALTER COLUMN inherit_parent SET NOT NULL;

ALTER TABLE mantis_plugin_table ADD COLUMN protected BOOLEAN  ;

UPDATE mantis_plugin_table SET protected='0';

ALTER TABLE mantis_plugin_table ALTER COLUMN protected SET DEFAULT '0';

ALTER TABLE mantis_plugin_table ALTER COLUMN protected SET NOT NULL;

ALTER TABLE mantis_plugin_table ADD COLUMN priority INTEGER  ;

UPDATE mantis_plugin_table SET priority=3;

ALTER TABLE mantis_plugin_table ALTER COLUMN priority SET DEFAULT 3;

ALTER TABLE mantis_plugin_table ALTER COLUMN priority SET NOT NULL;

ALTER TABLE mantis_project_version_table ADD COLUMN obsolete BOOLEAN  ;

UPDATE mantis_project_version_table SET obsolete='0';

ALTER TABLE mantis_project_version_table ALTER COLUMN obsolete SET DEFAULT '0';

ALTER TABLE mantis_project_version_table ALTER COLUMN obsolete SET NOT NULL;

ALTER TABLE mantis_bug_table ADD COLUMN due_date TIMESTAMP  ;

UPDATE mantis_bug_table SET due_date='1970-01-01 00:00:01';

ALTER TABLE mantis_bug_table ALTER COLUMN due_date SET DEFAULT '1970-01-01 00:00:01';

ALTER TABLE mantis_bug_table ALTER COLUMN due_date SET NOT NULL;

ALTER TABLE mantis_custom_field_table ADD COLUMN filter_by BOOLEAN  ;

UPDATE mantis_custom_field_table SET filter_by='1';

ALTER TABLE mantis_custom_field_table ALTER COLUMN filter_by SET DEFAULT '1';

ALTER TABLE mantis_custom_field_table ALTER COLUMN filter_by SET NOT NULL;

CREATE TABLE mantis_bug_revision_table (
id                       SERIAL,
bug_id                   INTEGER NOT NULL,
bugnote_id               INTEGER DEFAULT 0 NOT NULL,
user_id                  INTEGER NOT NULL,
timestamp                TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
type                     INTEGER NOT NULL,
value                    TEXT NOT NULL,
                 PRIMARY KEY (id)
);

CREATE INDEX idx_bug_rev_id_time ON mantis_bug_revision_table (bug_id, timestamp);

CREATE INDEX idx_bug_rev_type ON mantis_bug_revision_table (type);

ALTER TABLE mantis_bug_table ADD COLUMN date_submitted_int INTEGER  ;

UPDATE mantis_bug_table SET date_submitted_int=1;

ALTER TABLE mantis_bug_table ALTER COLUMN date_submitted_int SET DEFAULT 1;

ALTER TABLE mantis_bug_table ALTER COLUMN date_submitted_int SET NOT NULL;

ALTER TABLE mantis_bug_table ADD COLUMN due_date_int INTEGER  ;

UPDATE mantis_bug_table SET due_date_int=1;

ALTER TABLE mantis_bug_table ALTER COLUMN due_date_int SET DEFAULT 1;

ALTER TABLE mantis_bug_table ALTER COLUMN due_date_int SET NOT NULL;

ALTER TABLE mantis_bug_table ADD COLUMN last_updated_int INTEGER  ;

UPDATE mantis_bug_table SET last_updated_int=1;

ALTER TABLE mantis_bug_table ALTER COLUMN last_updated_int SET DEFAULT 1;

ALTER TABLE mantis_bug_table ALTER COLUMN last_updated_int SET NOT NULL;

ALTER TABLE mantis_bug_table DROP COLUMN date_submitted;

ALTER TABLE mantis_bug_table RENAME COLUMN date_submitted_int TO date_submitted;

ALTER TABLE mantis_bug_table DROP COLUMN due_date;

ALTER TABLE mantis_bug_table RENAME COLUMN due_date_int TO due_date;

ALTER TABLE mantis_bug_table DROP COLUMN last_updated;

ALTER TABLE mantis_bug_table RENAME COLUMN last_updated_int TO last_updated;

ALTER TABLE mantis_bugnote_table ADD COLUMN last_modified_int INTEGER  ;

UPDATE mantis_bugnote_table SET last_modified_int=1;

ALTER TABLE mantis_bugnote_table ALTER COLUMN last_modified_int SET DEFAULT 1;

ALTER TABLE mantis_bugnote_table ALTER COLUMN last_modified_int SET NOT NULL;

ALTER TABLE mantis_bugnote_table ADD COLUMN date_submitted_int INTEGER  ;

UPDATE mantis_bugnote_table SET date_submitted_int=1;

ALTER TABLE mantis_bugnote_table ALTER COLUMN date_submitted_int SET DEFAULT 1;

ALTER TABLE mantis_bugnote_table ALTER COLUMN date_submitted_int SET NOT NULL;

ALTER TABLE mantis_bugnote_table DROP COLUMN last_modified;

ALTER TABLE mantis_bugnote_table RENAME COLUMN last_modified_int TO last_modified;

CREATE INDEX idx_last_mod ON mantis_bugnote_table (last_modified);

ALTER TABLE mantis_bugnote_table DROP COLUMN date_submitted;

ALTER TABLE mantis_bugnote_table RENAME COLUMN date_submitted_int TO date_submitted;

ALTER TABLE mantis_bug_file_table ADD COLUMN date_added_int INTEGER  ;

UPDATE mantis_bug_file_table SET date_added_int=1;

ALTER TABLE mantis_bug_file_table ALTER COLUMN date_added_int SET DEFAULT 1;

ALTER TABLE mantis_bug_file_table ALTER COLUMN date_added_int SET NOT NULL;

ALTER TABLE mantis_bug_file_table DROP COLUMN date_added;

ALTER TABLE mantis_bug_file_table RENAME COLUMN date_added_int TO date_added;

ALTER TABLE mantis_project_file_table ADD COLUMN date_added_int INTEGER  ;

UPDATE mantis_project_file_table SET date_added_int=1;

ALTER TABLE mantis_project_file_table ALTER COLUMN date_added_int SET DEFAULT 1;

ALTER TABLE mantis_project_file_table ALTER COLUMN date_added_int SET NOT NULL;

ALTER TABLE mantis_project_file_table DROP COLUMN date_added;

ALTER TABLE mantis_project_file_table RENAME COLUMN date_added_int TO date_added;

ALTER TABLE mantis_bug_history_table ADD COLUMN date_modified_int INTEGER  ;

UPDATE mantis_bug_history_table SET date_modified_int=1;

ALTER TABLE mantis_bug_history_table ALTER COLUMN date_modified_int SET DEFAULT 1;

ALTER TABLE mantis_bug_history_table ALTER COLUMN date_modified_int SET NOT NULL;

ALTER TABLE mantis_bug_history_table DROP COLUMN date_modified;

ALTER TABLE mantis_bug_history_table RENAME COLUMN date_modified_int TO date_modified;

ALTER TABLE mantis_user_table ADD COLUMN last_visit_int INTEGER  ;

UPDATE mantis_user_table SET last_visit_int=1;

ALTER TABLE mantis_user_table ALTER COLUMN last_visit_int SET DEFAULT 1;

ALTER TABLE mantis_user_table ALTER COLUMN last_visit_int SET NOT NULL;

ALTER TABLE mantis_user_table ADD COLUMN date_created_int INTEGER  ;

UPDATE mantis_user_table SET date_created_int=1;

ALTER TABLE mantis_user_table ALTER COLUMN date_created_int SET DEFAULT 1;

ALTER TABLE mantis_user_table ALTER COLUMN date_created_int SET NOT NULL;

ALTER TABLE mantis_user_table DROP COLUMN date_created;

ALTER TABLE mantis_user_table RENAME COLUMN date_created_int TO date_created;

ALTER TABLE mantis_user_table DROP COLUMN last_visit;

ALTER TABLE mantis_user_table RENAME COLUMN last_visit_int TO last_visit;

ALTER TABLE mantis_email_table ADD COLUMN submitted_int INTEGER  ;

UPDATE mantis_email_table SET submitted_int=1;

ALTER TABLE mantis_email_table ALTER COLUMN submitted_int SET DEFAULT 1;

ALTER TABLE mantis_email_table ALTER COLUMN submitted_int SET NOT NULL;

ALTER TABLE mantis_email_table DROP COLUMN submitted;

ALTER TABLE mantis_email_table RENAME COLUMN submitted_int TO submitted;

ALTER TABLE mantis_tag_table ADD COLUMN date_created_int INTEGER  ;

UPDATE mantis_tag_table SET date_created_int=1;

ALTER TABLE mantis_tag_table ALTER COLUMN date_created_int SET DEFAULT 1;

ALTER TABLE mantis_tag_table ALTER COLUMN date_created_int SET NOT NULL;

ALTER TABLE mantis_tag_table ADD COLUMN date_updated_int INTEGER  ;

UPDATE mantis_tag_table SET date_updated_int=1;

ALTER TABLE mantis_tag_table ALTER COLUMN date_updated_int SET DEFAULT 1;

ALTER TABLE mantis_tag_table ALTER COLUMN date_updated_int SET NOT NULL;

ALTER TABLE mantis_tag_table DROP COLUMN date_created;

ALTER TABLE mantis_tag_table RENAME COLUMN date_created_int TO date_created;

ALTER TABLE mantis_tag_table DROP COLUMN date_updated;

ALTER TABLE mantis_tag_table RENAME COLUMN date_updated_int TO date_updated;

ALTER TABLE mantis_bug_tag_table ADD COLUMN date_attached_int INTEGER  ;

UPDATE mantis_bug_tag_table SET date_attached_int=1;

ALTER TABLE mantis_bug_tag_table ALTER COLUMN date_attached_int SET DEFAULT 1;

ALTER TABLE mantis_bug_tag_table ALTER COLUMN date_attached_int SET NOT NULL;

ALTER TABLE mantis_bug_tag_table DROP COLUMN date_attached;

ALTER TABLE mantis_bug_tag_table RENAME COLUMN date_attached_int TO date_attached;

ALTER TABLE mantis_tokens_table ADD COLUMN timestamp_int INTEGER  ;

UPDATE mantis_tokens_table SET timestamp_int=1;

ALTER TABLE mantis_tokens_table ALTER COLUMN timestamp_int SET DEFAULT 1;

ALTER TABLE mantis_tokens_table ALTER COLUMN timestamp_int SET NOT NULL;

ALTER TABLE mantis_tokens_table ADD COLUMN expiry_int INTEGER  ;

UPDATE mantis_tokens_table SET expiry_int=1;

ALTER TABLE mantis_tokens_table ALTER COLUMN expiry_int SET DEFAULT 1;

ALTER TABLE mantis_tokens_table ALTER COLUMN expiry_int SET NOT NULL;

ALTER TABLE mantis_tokens_table DROP COLUMN timestamp;

ALTER TABLE mantis_tokens_table RENAME COLUMN timestamp_int TO timestamp;

ALTER TABLE mantis_tokens_table DROP COLUMN expiry;

ALTER TABLE mantis_tokens_table RENAME COLUMN expiry_int TO expiry;

ALTER TABLE mantis_news_table ADD COLUMN last_modified_int INTEGER  ;

UPDATE mantis_news_table SET last_modified_int=1;

ALTER TABLE mantis_news_table ALTER COLUMN last_modified_int SET DEFAULT 1;

ALTER TABLE mantis_news_table ALTER COLUMN last_modified_int SET NOT NULL;

ALTER TABLE mantis_news_table ADD COLUMN date_posted_int INTEGER  ;

UPDATE mantis_news_table SET date_posted_int=1;

ALTER TABLE mantis_news_table ALTER COLUMN date_posted_int SET DEFAULT 1;

ALTER TABLE mantis_news_table ALTER COLUMN date_posted_int SET NOT NULL;

ALTER TABLE mantis_news_table DROP COLUMN last_modified;

ALTER TABLE mantis_news_table RENAME COLUMN last_modified_int TO last_modified;

ALTER TABLE mantis_news_table DROP COLUMN date_posted;

ALTER TABLE mantis_news_table RENAME COLUMN date_posted_int TO date_posted;

ALTER TABLE mantis_bug_revision_table ADD COLUMN timestamp_int INTEGER  ;

UPDATE mantis_bug_revision_table SET timestamp_int=1;

ALTER TABLE mantis_bug_revision_table ALTER COLUMN timestamp_int SET DEFAULT 1;

ALTER TABLE mantis_bug_revision_table ALTER COLUMN timestamp_int SET NOT NULL;

ALTER TABLE mantis_bug_revision_table DROP COLUMN timestamp;

ALTER TABLE mantis_bug_revision_table RENAME COLUMN timestamp_int TO timestamp;

CREATE INDEX idx_bug_rev_id_time ON mantis_bug_revision_table (bug_id, timestamp);

ALTER TABLE mantis_user_pref_table ADD COLUMN timezone VARCHAR(32)  ;

UPDATE mantis_user_pref_table SET timezone='';

ALTER TABLE mantis_user_pref_table ALTER COLUMN timezone SET DEFAULT '';

ALTER TABLE mantis_user_pref_table ALTER COLUMN timezone SET NOT NULL;

ALTER TABLE mantis_project_version_table ADD COLUMN date_order_int INTEGER  ;

UPDATE mantis_project_version_table SET date_order_int=1;

ALTER TABLE mantis_project_version_table ALTER COLUMN date_order_int SET DEFAULT 1;

ALTER TABLE mantis_project_version_table ALTER COLUMN date_order_int SET NOT NULL;

ALTER TABLE mantis_project_version_table DROP COLUMN date_order;

ALTER TABLE mantis_project_version_table RENAME COLUMN date_order_int TO date_order;

ALTER TABLE mantis_sponsorship_table ADD COLUMN date_submitted_int INTEGER  ;

UPDATE mantis_sponsorship_table SET date_submitted_int=1;

ALTER TABLE mantis_sponsorship_table ALTER COLUMN date_submitted_int SET DEFAULT 1;

ALTER TABLE mantis_sponsorship_table ALTER COLUMN date_submitted_int SET NOT NULL;

ALTER TABLE mantis_sponsorship_table ADD COLUMN last_updated_int INTEGER  ;

UPDATE mantis_sponsorship_table SET last_updated_int=1;

ALTER TABLE mantis_sponsorship_table ALTER COLUMN last_updated_int SET DEFAULT 1;

ALTER TABLE mantis_sponsorship_table ALTER COLUMN last_updated_int SET NOT NULL;

ALTER TABLE mantis_sponsorship_table DROP COLUMN last_updated;

ALTER TABLE mantis_sponsorship_table RENAME COLUMN last_updated_int TO last_updated;

ALTER TABLE mantis_sponsorship_table DROP COLUMN date_submitted;

ALTER TABLE mantis_sponsorship_table RENAME COLUMN date_submitted_int TO date_submitted;

ALTER TABLE mantis_project_file_table ADD COLUMN user_id INTEGER  ;

UPDATE mantis_project_file_table SET user_id=0;

ALTER TABLE mantis_project_file_table ALTER COLUMN user_id SET DEFAULT 0;

ALTER TABLE mantis_project_file_table ALTER COLUMN user_id SET NOT NULL;

ALTER TABLE mantis_bug_file_table ADD COLUMN user_id INTEGER  ;

UPDATE mantis_bug_file_table SET user_id=0;

ALTER TABLE mantis_bug_file_table ALTER COLUMN user_id SET DEFAULT 0;

ALTER TABLE mantis_bug_file_table ALTER COLUMN user_id SET NOT NULL;

ALTER TABLE mantis_custom_field_table DROP COLUMN advanced;

ALTER TABLE mantis_user_pref_table DROP COLUMN advanced_report;

ALTER TABLE mantis_user_pref_table DROP COLUMN advanced_view;

ALTER TABLE mantis_user_pref_table DROP COLUMN advanced_update;

CREATE INDEX idx_project_hierarchy_child_id ON mantis_project_hierarchy_table (child_id);

CREATE INDEX idx_project_hierarchy_parent_id ON mantis_project_hierarchy_table (parent_id);

CREATE INDEX idx_tag_name ON mantis_tag_table (name);

CREATE INDEX idx_bug_tag_tag_id ON mantis_bug_tag_table (tag_id);

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

commit;
mantis-0.sql (33,393 bytes)   
mantis-1.sql (33,156 bytes)    
begin;

CREATE TABLE 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                    TEXT NOT NULL,
                 PRIMARY KEY (config_id, project_id, user_id)
);

CREATE INDEX idx_config ON mantis_config_table (config_id);

CREATE TABLE mantis_bug_file_table (
id                       SERIAL,
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 DEFAULT '1970-01-01 00:00:01' NOT NULL,
content                  BYTEA NOT NULL,
                 PRIMARY KEY (id)
);

CREATE INDEX idx_bug_file_bug_id ON mantis_bug_file_table (bug_id);

CREATE TABLE mantis_bug_history_table (
id                       SERIAL,
user_id                  INTEGER DEFAULT 0 NOT NULL,
bug_id                   INTEGER DEFAULT 0 NOT NULL,
date_modified            TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
field_name               VARCHAR(32) DEFAULT '' NOT NULL,
old_value                VARCHAR(128) DEFAULT '' NOT NULL,
new_value                VARCHAR(128) DEFAULT '' NOT NULL,
type                     INT2 DEFAULT 0 NOT NULL,
                 PRIMARY KEY (id)
);

CREATE INDEX idx_bug_history_bug_id ON mantis_bug_history_table (bug_id);

CREATE INDEX idx_history_user_id ON mantis_bug_history_table (user_id);

CREATE TABLE 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 mantis_bug_relationship_table (
id                       SERIAL,
source_bug_id            INTEGER DEFAULT 0 NOT NULL,
destination_bug_id       INTEGER DEFAULT 0 NOT NULL,
relationship_type        INT2 DEFAULT 0 NOT NULL,
                 PRIMARY KEY (id)
);

CREATE INDEX idx_relationship_source ON mantis_bug_relationship_table (source_bug_id);

CREATE INDEX idx_relationship_destination ON mantis_bug_relationship_table (destination_bug_id);

CREATE TABLE mantis_bug_table (
id                       SERIAL,
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                 INT2 DEFAULT 30 NOT NULL,
severity                 INT2 DEFAULT 50 NOT NULL,
reproducibility          INT2 DEFAULT 10 NOT NULL,
status                   INT2 DEFAULT 10 NOT NULL,
resolution               INT2 DEFAULT 10 NOT NULL,
projection               INT2 DEFAULT 10 NOT NULL,
category                 VARCHAR(64) DEFAULT '' NOT NULL,
date_submitted           TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
last_updated             TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
eta                      INT2 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               INT2 DEFAULT 10 NOT NULL,
summary                  VARCHAR(128) DEFAULT '' NOT NULL,
sponsorship_total        INTEGER DEFAULT 0 NOT NULL,
sticky                   BOOLEAN DEFAULT '0' NOT NULL,
                 PRIMARY KEY (id)
);

CREATE INDEX idx_bug_sponsorship_total ON mantis_bug_table (sponsorship_total);

CREATE INDEX idx_bug_fixed_in_version ON mantis_bug_table (fixed_in_version);

CREATE INDEX idx_bug_status ON mantis_bug_table (status);

CREATE INDEX idx_project ON mantis_bug_table (project_id);

CREATE TABLE mantis_bug_text_table (
id                       SERIAL,
description              TEXT NOT NULL,
steps_to_reproduce       TEXT NOT NULL,
additional_information   TEXT NOT NULL,
                 PRIMARY KEY (id)
);

CREATE TABLE mantis_bugnote_table (
id                       SERIAL,
bug_id                   INTEGER DEFAULT 0 NOT NULL,
reporter_id              INTEGER DEFAULT 0 NOT NULL,
bugnote_text_id          INTEGER DEFAULT 0 NOT NULL,
view_state               INT2 DEFAULT 10 NOT NULL,
date_submitted           TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
last_modified            TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
note_type                INTEGER DEFAULT 0,
note_attr                VARCHAR(250) DEFAULT '',
                 PRIMARY KEY (id)
);

CREATE INDEX idx_bug ON mantis_bugnote_table (bug_id);

CREATE INDEX idx_last_mod ON mantis_bugnote_table (last_modified);

CREATE TABLE mantis_bugnote_text_table (
id                       SERIAL,
note                     TEXT NOT NULL,
                 PRIMARY KEY (id)
);

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

CREATE TABLE 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 idx_custom_field_bug ON mantis_custom_field_string_table (bug_id);

CREATE TABLE mantis_custom_field_table (
id                       SERIAL,
name                     VARCHAR(64) DEFAULT '' NOT NULL,
type                     INT2 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           INT2 DEFAULT 0 NOT NULL,
access_level_rw          INT2 DEFAULT 0 NOT NULL,
length_min               INTEGER DEFAULT 0 NOT NULL,
length_max               INTEGER DEFAULT 0 NOT NULL,
advanced                 BOOLEAN DEFAULT '0' NOT NULL,
require_report           BOOLEAN DEFAULT '0' NOT NULL,
require_update           BOOLEAN DEFAULT '0' NOT NULL,
display_report           BOOLEAN DEFAULT '0' NOT NULL,
display_update           BOOLEAN DEFAULT '1' NOT NULL,
require_resolved         BOOLEAN DEFAULT '0' NOT NULL,
display_resolved         BOOLEAN DEFAULT '0' NOT NULL,
display_closed           BOOLEAN DEFAULT '0' NOT NULL,
require_closed           BOOLEAN DEFAULT '0' NOT NULL,
                 PRIMARY KEY (id)
);

CREATE INDEX idx_custom_field_name ON mantis_custom_field_table (name);

CREATE TABLE mantis_filters_table (
id                       SERIAL,
user_id                  INTEGER DEFAULT 0 NOT NULL,
project_id               INTEGER DEFAULT 0 NOT NULL,
is_public                BOOLEAN DEFAULT NULL,
name                     VARCHAR(64) DEFAULT '' NOT NULL,
filter_string            TEXT NOT NULL,
                 PRIMARY KEY (id)
);

CREATE TABLE mantis_news_table (
id                       SERIAL,
project_id               INTEGER DEFAULT 0 NOT NULL,
poster_id                INTEGER DEFAULT 0 NOT NULL,
date_posted              TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
last_modified            TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
view_state               INT2 DEFAULT 10 NOT NULL,
announcement             BOOLEAN DEFAULT '0' NOT NULL,
headline                 VARCHAR(64) DEFAULT '' NOT NULL,
body                     TEXT NOT NULL,
                 PRIMARY KEY (id)
);

CREATE TABLE 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 mantis_project_file_table (
id                       SERIAL,
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 '1970-01-01 00:00:01' NOT NULL,
content                  BYTEA NOT NULL,
                 PRIMARY KEY (id)
);

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

CREATE TABLE mantis_project_table (
id                       SERIAL,
name                     VARCHAR(128) DEFAULT '' NOT NULL,
status                   INT2 DEFAULT 10 NOT NULL,
enabled                  BOOLEAN DEFAULT '1' NOT NULL,
view_state               INT2 DEFAULT 10 NOT NULL,
access_min               INT2 DEFAULT 10 NOT NULL,
file_path                VARCHAR(250) DEFAULT '' NOT NULL,
description              TEXT NOT NULL,
                 PRIMARY KEY (id)
);

CREATE INDEX idx_project_id ON mantis_project_table (id);

CREATE UNIQUE INDEX idx_project_name ON mantis_project_table (name);

CREATE INDEX idx_project_view ON mantis_project_table (view_state);

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

CREATE INDEX idx_project_user ON mantis_project_user_list_table (user_id);

CREATE TABLE mantis_project_version_table (
id                       SERIAL,
project_id               INTEGER DEFAULT 0 NOT NULL,
version                  VARCHAR(64) DEFAULT '' NOT NULL,
date_order               TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
description              TEXT NOT NULL,
released                 BOOLEAN DEFAULT '1' NOT NULL,
                 PRIMARY KEY (id)
);

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

CREATE TABLE mantis_sponsorship_table (
id                       SERIAL,
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                     BOOLEAN DEFAULT '0' NOT NULL,
date_submitted           TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
last_updated             TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
                 PRIMARY KEY (id)
);

CREATE INDEX idx_sponsorship_bug_id ON mantis_sponsorship_table (bug_id);

CREATE INDEX idx_sponsorship_user_id ON mantis_sponsorship_table (user_id);

CREATE TABLE mantis_tokens_table (
id                       SERIAL,
owner                    INTEGER NOT NULL,
type                     INTEGER NOT NULL,
timestamp                TIMESTAMP NOT NULL,
expiry                   TIMESTAMP,
value                    TEXT NOT NULL,
                 PRIMARY KEY (id)
);

CREATE TABLE mantis_user_pref_table (
id                       SERIAL,
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          BOOLEAN DEFAULT '0' NOT NULL,
advanced_view            BOOLEAN DEFAULT '0' NOT NULL,
advanced_update          BOOLEAN DEFAULT '0' NOT NULL,
refresh_delay            INTEGER DEFAULT 0 NOT NULL,
redirect_delay           INTEGER DEFAULT 0 NOT NULL,
bugnote_order            VARCHAR(4) DEFAULT 'ASC' NOT NULL,
email_on_new             BOOLEAN DEFAULT '0' NOT NULL,
email_on_assigned        BOOLEAN DEFAULT '0' NOT NULL,
email_on_feedback        BOOLEAN DEFAULT '0' NOT NULL,
email_on_resolved        BOOLEAN DEFAULT '0' NOT NULL,
email_on_closed          BOOLEAN DEFAULT '0' NOT NULL,
email_on_reopened        BOOLEAN DEFAULT '0' NOT NULL,
email_on_bugnote         BOOLEAN DEFAULT '0' NOT NULL,
email_on_status          BOOLEAN DEFAULT '0' NOT NULL,
email_on_priority        BOOLEAN DEFAULT '0' NOT NULL,
email_on_priority_min_severity INT2 DEFAULT 10 NOT NULL,
email_on_status_min_severity INT2 DEFAULT 10 NOT NULL,
email_on_bugnote_min_severity INT2 DEFAULT 10 NOT NULL,
email_on_reopened_min_severity INT2 DEFAULT 10 NOT NULL,
email_on_closed_min_severity INT2 DEFAULT 10 NOT NULL,
email_on_resolved_min_severity INT2 DEFAULT 10 NOT NULL,
email_on_feedback_min_severity INT2 DEFAULT 10 NOT NULL,
email_on_assigned_min_severity INT2 DEFAULT 10 NOT NULL,
email_on_new_min_severity INT2 DEFAULT 10 NOT NULL,
email_bugnote_limit      INT2 DEFAULT 0 NOT NULL,
language                 VARCHAR(32) DEFAULT 'english' NOT NULL,
                 PRIMARY KEY (id)
);

CREATE TABLE 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 mantis_user_profile_table (
id                       SERIAL,
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              TEXT NOT NULL,
                 PRIMARY KEY (id)
);

CREATE TABLE mantis_user_table (
id                       SERIAL,
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 '1970-01-01 00:00:01' NOT NULL,
last_visit               TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
enabled                  BOOLEAN DEFAULT '1' NOT NULL,
protected                BOOLEAN DEFAULT '0' NOT NULL,
access_level             INT2 DEFAULT 10 NOT NULL,
login_count              INTEGER DEFAULT 0 NOT NULL,
lost_password_request_count INT2 DEFAULT 0 NOT NULL,
failed_login_count       INT2 DEFAULT 0 NOT NULL,
cookie_string            VARCHAR(64) DEFAULT '' NOT NULL,
                 PRIMARY KEY (id)
);

CREATE UNIQUE INDEX idx_user_cookie_string ON mantis_user_table (cookie_string);

CREATE UNIQUE INDEX idx_user_username ON mantis_user_table (username);

CREATE INDEX idx_enable ON mantis_user_table (enabled);

CREATE INDEX idx_access ON mantis_user_table (access_level);

INSERT INTO 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', '2012-10-23 00:04:34', '2012-10-23 00:04:34', '1', '0', 90, 3, 0, 0, 'f9d294695143d7a069e33c88cfa87bb5ca06ce422f58f5da6fc55f07d297e7e5');

ALTER TABLE mantis_bug_history_table ALTER COLUMN old_value TYPE VARCHAR(255) ;

ALTER TABLE mantis_bug_history_table ALTER COLUMN old_value SET NOT NULL;

ALTER TABLE mantis_bug_history_table ALTER COLUMN new_value TYPE VARCHAR(255) ;

ALTER TABLE mantis_bug_history_table ALTER COLUMN new_value SET NOT NULL;

CREATE TABLE mantis_email_table (
email_id                 SERIAL,
email                    VARCHAR(64) DEFAULT '' NOT NULL,
subject                  VARCHAR(250) DEFAULT '' NOT NULL,
submitted                TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
metadata                 TEXT NOT NULL,
body                     TEXT NOT NULL,
                 PRIMARY KEY (email_id)
);

CREATE INDEX idx_email_id ON mantis_email_table (email_id);

ALTER TABLE mantis_bug_table ADD COLUMN target_version VARCHAR(64)  ;

UPDATE mantis_bug_table SET target_version='';

ALTER TABLE mantis_bug_table ALTER COLUMN target_version SET DEFAULT '';

ALTER TABLE mantis_bug_table ALTER COLUMN target_version SET NOT NULL;

ALTER TABLE mantis_bugnote_table ADD COLUMN time_tracking INTEGER  ;

UPDATE mantis_bugnote_table SET time_tracking=0;

ALTER TABLE mantis_bugnote_table ALTER COLUMN time_tracking SET DEFAULT 0;

ALTER TABLE mantis_bugnote_table ALTER COLUMN time_tracking SET NOT NULL;

CREATE INDEX idx_diskfile ON mantis_bug_file_table (diskfile);

ALTER TABLE mantis_user_print_pref_table ALTER COLUMN print_pref TYPE VARCHAR(64) ;

ALTER TABLE mantis_user_print_pref_table ALTER COLUMN print_pref SET NOT NULL;

ALTER TABLE mantis_bug_history_table ALTER COLUMN field_name TYPE VARCHAR(64) ;

ALTER TABLE mantis_bug_history_table ALTER COLUMN field_name SET NOT NULL;

CREATE TABLE mantis_tag_table (
id                       SERIAL,
user_id                  INTEGER DEFAULT 0 NOT NULL,
name                     VARCHAR(100) DEFAULT '' NOT NULL,
description              TEXT NOT NULL,
date_created             TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
date_updated             TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
                 PRIMARY KEY (id, name)
);

CREATE TABLE 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 '1970-01-01 00:00:01' NOT NULL,
                 PRIMARY KEY (bug_id, tag_id)
);

CREATE INDEX idx_typeowner ON mantis_tokens_table (type, owner);

CREATE TABLE mantis_plugin_table (
basename                 VARCHAR(40) NOT NULL,
enabled                  BOOLEAN DEFAULT '0' NOT NULL,
                 PRIMARY KEY (basename)
);

ALTER TABLE mantis_custom_field_table ALTER COLUMN possible_values TYPE TEXT;

ALTER TABLE mantis_custom_field_table ALTER COLUMN possible_values SET DEFAULT '';

ALTER TABLE mantis_custom_field_table ALTER COLUMN possible_values SET NOT NULL;

CREATE TABLE mantis_category_table (
id                       SERIAL,
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 idx_category_project_name ON mantis_category_table (project_id, name);

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

ALTER TABLE mantis_bug_table ADD COLUMN category_id INTEGER  ;

UPDATE mantis_bug_table SET category_id=1;

ALTER TABLE mantis_bug_table ALTER COLUMN category_id SET DEFAULT 1;

ALTER TABLE mantis_bug_table ALTER COLUMN category_id SET NOT NULL;

ALTER TABLE mantis_bug_table DROP COLUMN category;

DROP TABLE mantis_project_category_table CASCADE;

ALTER TABLE mantis_project_table ADD COLUMN category_id INTEGER  ;

UPDATE mantis_project_table SET category_id=1;

ALTER TABLE mantis_project_table ALTER COLUMN category_id SET DEFAULT 1;

ALTER TABLE mantis_project_table ALTER COLUMN category_id SET NOT NULL;

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

ALTER TABLE mantis_project_table ADD COLUMN inherit_global INTEGER  ;

UPDATE mantis_project_table SET inherit_global=0;

ALTER TABLE mantis_project_table ALTER COLUMN inherit_global SET DEFAULT 0;

ALTER TABLE mantis_project_table ALTER COLUMN inherit_global SET NOT NULL;

ALTER TABLE mantis_project_hierarchy_table ADD COLUMN inherit_parent INTEGER  ;

UPDATE mantis_project_hierarchy_table SET inherit_parent=0;

ALTER TABLE mantis_project_hierarchy_table ALTER COLUMN inherit_parent SET DEFAULT 0;

ALTER TABLE mantis_project_hierarchy_table ALTER COLUMN inherit_parent SET NOT NULL;

ALTER TABLE mantis_plugin_table ADD COLUMN protected BOOLEAN  ;

UPDATE mantis_plugin_table SET protected='0';

ALTER TABLE mantis_plugin_table ALTER COLUMN protected SET DEFAULT '0';

ALTER TABLE mantis_plugin_table ALTER COLUMN protected SET NOT NULL;

ALTER TABLE mantis_plugin_table ADD COLUMN priority INTEGER  ;

UPDATE mantis_plugin_table SET priority=3;

ALTER TABLE mantis_plugin_table ALTER COLUMN priority SET DEFAULT 3;

ALTER TABLE mantis_plugin_table ALTER COLUMN priority SET NOT NULL;

ALTER TABLE mantis_project_version_table ADD COLUMN obsolete BOOLEAN  ;

UPDATE mantis_project_version_table SET obsolete='0';

ALTER TABLE mantis_project_version_table ALTER COLUMN obsolete SET DEFAULT '0';

ALTER TABLE mantis_project_version_table ALTER COLUMN obsolete SET NOT NULL;

ALTER TABLE mantis_bug_table ADD COLUMN due_date TIMESTAMP  ;

UPDATE mantis_bug_table SET due_date='1970-01-01 00:00:01';

ALTER TABLE mantis_bug_table ALTER COLUMN due_date SET DEFAULT '1970-01-01 00:00:01';

ALTER TABLE mantis_bug_table ALTER COLUMN due_date SET NOT NULL;

ALTER TABLE mantis_custom_field_table ADD COLUMN filter_by BOOLEAN  ;

UPDATE mantis_custom_field_table SET filter_by='1';

ALTER TABLE mantis_custom_field_table ALTER COLUMN filter_by SET DEFAULT '1';

ALTER TABLE mantis_custom_field_table ALTER COLUMN filter_by SET NOT NULL;

CREATE TABLE mantis_bug_revision_table (
id                       SERIAL,
bug_id                   INTEGER NOT NULL,
bugnote_id               INTEGER DEFAULT 0 NOT NULL,
user_id                  INTEGER NOT NULL,
timestamp                TIMESTAMP DEFAULT '1970-01-01 00:00:01' NOT NULL,
type                     INTEGER NOT NULL,
value                    TEXT NOT NULL,
                 PRIMARY KEY (id)
);

CREATE INDEX idx_bug_rev_id_time ON mantis_bug_revision_table (bug_id, timestamp);

CREATE INDEX idx_bug_rev_type ON mantis_bug_revision_table (type);

ALTER TABLE mantis_bug_table ADD COLUMN date_submitted_int INTEGER  ;

UPDATE mantis_bug_table SET date_submitted_int=1;

ALTER TABLE mantis_bug_table ALTER COLUMN date_submitted_int SET DEFAULT 1;

ALTER TABLE mantis_bug_table ALTER COLUMN date_submitted_int SET NOT NULL;

ALTER TABLE mantis_bug_table ADD COLUMN due_date_int INTEGER  ;

UPDATE mantis_bug_table SET due_date_int=1;

ALTER TABLE mantis_bug_table ALTER COLUMN due_date_int SET DEFAULT 1;

ALTER TABLE mantis_bug_table ALTER COLUMN due_date_int SET NOT NULL;

ALTER TABLE mantis_bug_table ADD COLUMN last_updated_int INTEGER  ;

UPDATE mantis_bug_table SET last_updated_int=1;

ALTER TABLE mantis_bug_table ALTER COLUMN last_updated_int SET DEFAULT 1;

ALTER TABLE mantis_bug_table ALTER COLUMN last_updated_int SET NOT NULL;

ALTER TABLE mantis_bug_table DROP COLUMN date_submitted;

ALTER TABLE mantis_bug_table RENAME COLUMN date_submitted_int TO date_submitted;

ALTER TABLE mantis_bug_table DROP COLUMN due_date;

ALTER TABLE mantis_bug_table RENAME COLUMN due_date_int TO due_date;

ALTER TABLE mantis_bug_table DROP COLUMN last_updated;

ALTER TABLE mantis_bug_table RENAME COLUMN last_updated_int TO last_updated;

ALTER TABLE mantis_bugnote_table ADD COLUMN last_modified_int INTEGER  ;

UPDATE mantis_bugnote_table SET last_modified_int=1;

ALTER TABLE mantis_bugnote_table ALTER COLUMN last_modified_int SET DEFAULT 1;

ALTER TABLE mantis_bugnote_table ALTER COLUMN last_modified_int SET NOT NULL;

ALTER TABLE mantis_bugnote_table ADD COLUMN date_submitted_int INTEGER  ;

UPDATE mantis_bugnote_table SET date_submitted_int=1;

ALTER TABLE mantis_bugnote_table ALTER COLUMN date_submitted_int SET DEFAULT 1;

ALTER TABLE mantis_bugnote_table ALTER COLUMN date_submitted_int SET NOT NULL;

ALTER TABLE mantis_bugnote_table DROP COLUMN last_modified;

ALTER TABLE mantis_bugnote_table RENAME COLUMN last_modified_int TO last_modified;

CREATE INDEX idx_last_mod ON mantis_bugnote_table (last_modified);

ALTER TABLE mantis_bugnote_table DROP COLUMN date_submitted;

ALTER TABLE mantis_bugnote_table RENAME COLUMN date_submitted_int TO date_submitted;

ALTER TABLE mantis_bug_file_table ADD COLUMN date_added_int INTEGER  ;

UPDATE mantis_bug_file_table SET date_added_int=1;

ALTER TABLE mantis_bug_file_table ALTER COLUMN date_added_int SET DEFAULT 1;

ALTER TABLE mantis_bug_file_table ALTER COLUMN date_added_int SET NOT NULL;

ALTER TABLE mantis_bug_file_table DROP COLUMN date_added;

ALTER TABLE mantis_bug_file_table RENAME COLUMN date_added_int TO date_added;

ALTER TABLE mantis_project_file_table ADD COLUMN date_added_int INTEGER  ;

UPDATE mantis_project_file_table SET date_added_int=1;

ALTER TABLE mantis_project_file_table ALTER COLUMN date_added_int SET DEFAULT 1;

ALTER TABLE mantis_project_file_table ALTER COLUMN date_added_int SET NOT NULL;

ALTER TABLE mantis_project_file_table DROP COLUMN date_added;

ALTER TABLE mantis_project_file_table RENAME COLUMN date_added_int TO date_added;

ALTER TABLE mantis_bug_history_table ADD COLUMN date_modified_int INTEGER  ;

UPDATE mantis_bug_history_table SET date_modified_int=1;

ALTER TABLE mantis_bug_history_table ALTER COLUMN date_modified_int SET DEFAULT 1;

ALTER TABLE mantis_bug_history_table ALTER COLUMN date_modified_int SET NOT NULL;

ALTER TABLE mantis_bug_history_table DROP COLUMN date_modified;

ALTER TABLE mantis_bug_history_table RENAME COLUMN date_modified_int TO date_modified;

ALTER TABLE mantis_user_table ADD COLUMN last_visit_int INTEGER  ;

UPDATE mantis_user_table SET last_visit_int=1;

ALTER TABLE mantis_user_table ALTER COLUMN last_visit_int SET DEFAULT 1;

ALTER TABLE mantis_user_table ALTER COLUMN last_visit_int SET NOT NULL;

ALTER TABLE mantis_user_table ADD COLUMN date_created_int INTEGER  ;

UPDATE mantis_user_table SET date_created_int=1;

ALTER TABLE mantis_user_table ALTER COLUMN date_created_int SET DEFAULT 1;

ALTER TABLE mantis_user_table ALTER COLUMN date_created_int SET NOT NULL;

ALTER TABLE mantis_user_table DROP COLUMN date_created;

ALTER TABLE mantis_user_table RENAME COLUMN date_created_int TO date_created;

ALTER TABLE mantis_user_table DROP COLUMN last_visit;

ALTER TABLE mantis_user_table RENAME COLUMN last_visit_int TO last_visit;

ALTER TABLE mantis_email_table ADD COLUMN submitted_int INTEGER  ;

UPDATE mantis_email_table SET submitted_int=1;

ALTER TABLE mantis_email_table ALTER COLUMN submitted_int SET DEFAULT 1;

ALTER TABLE mantis_email_table ALTER COLUMN submitted_int SET NOT NULL;

ALTER TABLE mantis_email_table DROP COLUMN submitted;

ALTER TABLE mantis_email_table RENAME COLUMN submitted_int TO submitted;

ALTER TABLE mantis_tag_table ADD COLUMN date_created_int INTEGER  ;

UPDATE mantis_tag_table SET date_created_int=1;

ALTER TABLE mantis_tag_table ALTER COLUMN date_created_int SET DEFAULT 1;

ALTER TABLE mantis_tag_table ALTER COLUMN date_created_int SET NOT NULL;

ALTER TABLE mantis_tag_table ADD COLUMN date_updated_int INTEGER  ;

UPDATE mantis_tag_table SET date_updated_int=1;

ALTER TABLE mantis_tag_table ALTER COLUMN date_updated_int SET DEFAULT 1;

ALTER TABLE mantis_tag_table ALTER COLUMN date_updated_int SET NOT NULL;

ALTER TABLE mantis_tag_table DROP COLUMN date_created;

ALTER TABLE mantis_tag_table RENAME COLUMN date_created_int TO date_created;

ALTER TABLE mantis_tag_table DROP COLUMN date_updated;

ALTER TABLE mantis_tag_table RENAME COLUMN date_updated_int TO date_updated;

ALTER TABLE mantis_bug_tag_table ADD COLUMN date_attached_int INTEGER  ;

UPDATE mantis_bug_tag_table SET date_attached_int=1;

ALTER TABLE mantis_bug_tag_table ALTER COLUMN date_attached_int SET DEFAULT 1;

ALTER TABLE mantis_bug_tag_table ALTER COLUMN date_attached_int SET NOT NULL;

ALTER TABLE mantis_bug_tag_table DROP COLUMN date_attached;

ALTER TABLE mantis_bug_tag_table RENAME COLUMN date_attached_int TO date_attached;

ALTER TABLE mantis_tokens_table ADD COLUMN timestamp_int INTEGER  ;

UPDATE mantis_tokens_table SET timestamp_int=1;

ALTER TABLE mantis_tokens_table ALTER COLUMN timestamp_int SET DEFAULT 1;

ALTER TABLE mantis_tokens_table ALTER COLUMN timestamp_int SET NOT NULL;

ALTER TABLE mantis_tokens_table ADD COLUMN expiry_int INTEGER  ;

UPDATE mantis_tokens_table SET expiry_int=1;

ALTER TABLE mantis_tokens_table ALTER COLUMN expiry_int SET DEFAULT 1;

ALTER TABLE mantis_tokens_table ALTER COLUMN expiry_int SET NOT NULL;

ALTER TABLE mantis_tokens_table DROP COLUMN timestamp;

ALTER TABLE mantis_tokens_table RENAME COLUMN timestamp_int TO timestamp;

ALTER TABLE mantis_tokens_table DROP COLUMN expiry;

ALTER TABLE mantis_tokens_table RENAME COLUMN expiry_int TO expiry;

ALTER TABLE mantis_news_table ADD COLUMN last_modified_int INTEGER  ;

UPDATE mantis_news_table SET last_modified_int=1;

ALTER TABLE mantis_news_table ALTER COLUMN last_modified_int SET DEFAULT 1;

ALTER TABLE mantis_news_table ALTER COLUMN last_modified_int SET NOT NULL;

ALTER TABLE mantis_news_table ADD COLUMN date_posted_int INTEGER  ;

UPDATE mantis_news_table SET date_posted_int=1;

ALTER TABLE mantis_news_table ALTER COLUMN date_posted_int SET DEFAULT 1;

ALTER TABLE mantis_news_table ALTER COLUMN date_posted_int SET NOT NULL;

ALTER TABLE mantis_news_table DROP COLUMN last_modified;

ALTER TABLE mantis_news_table RENAME COLUMN last_modified_int TO last_modified;

ALTER TABLE mantis_news_table DROP COLUMN date_posted;

ALTER TABLE mantis_news_table RENAME COLUMN date_posted_int TO date_posted;

ALTER TABLE mantis_bug_revision_table ADD COLUMN timestamp_int INTEGER  ;

UPDATE mantis_bug_revision_table SET timestamp_int=1;

ALTER TABLE mantis_bug_revision_table ALTER COLUMN timestamp_int SET DEFAULT 1;

ALTER TABLE mantis_bug_revision_table ALTER COLUMN timestamp_int SET NOT NULL;

ALTER TABLE mantis_bug_revision_table DROP COLUMN timestamp;

ALTER TABLE mantis_bug_revision_table RENAME COLUMN timestamp_int TO timestamp;

CREATE INDEX idx_bug_rev_id_time ON mantis_bug_revision_table (bug_id, timestamp);

ALTER TABLE mantis_user_pref_table ADD COLUMN timezone VARCHAR(32)  ;

UPDATE mantis_user_pref_table SET timezone='';

ALTER TABLE mantis_user_pref_table ALTER COLUMN timezone SET DEFAULT '';

ALTER TABLE mantis_user_pref_table ALTER COLUMN timezone SET NOT NULL;

ALTER TABLE mantis_project_version_table ADD COLUMN date_order_int INTEGER  ;

UPDATE mantis_project_version_table SET date_order_int=1;

ALTER TABLE mantis_project_version_table ALTER COLUMN date_order_int SET DEFAULT 1;

ALTER TABLE mantis_project_version_table ALTER COLUMN date_order_int SET NOT NULL;

ALTER TABLE mantis_project_version_table DROP COLUMN date_order;

ALTER TABLE mantis_project_version_table RENAME COLUMN date_order_int TO date_order;

ALTER TABLE mantis_sponsorship_table ADD COLUMN date_submitted_int INTEGER  ;

UPDATE mantis_sponsorship_table SET date_submitted_int=1;

ALTER TABLE mantis_sponsorship_table ALTER COLUMN date_submitted_int SET DEFAULT 1;

ALTER TABLE mantis_sponsorship_table ALTER COLUMN date_submitted_int SET NOT NULL;

ALTER TABLE mantis_sponsorship_table ADD COLUMN last_updated_int INTEGER  ;

UPDATE mantis_sponsorship_table SET last_updated_int=1;

ALTER TABLE mantis_sponsorship_table ALTER COLUMN last_updated_int SET DEFAULT 1;

ALTER TABLE mantis_sponsorship_table ALTER COLUMN last_updated_int SET NOT NULL;

ALTER TABLE mantis_sponsorship_table DROP COLUMN last_updated;

ALTER TABLE mantis_sponsorship_table RENAME COLUMN last_updated_int TO last_updated;

ALTER TABLE mantis_sponsorship_table DROP COLUMN date_submitted;

ALTER TABLE mantis_sponsorship_table RENAME COLUMN date_submitted_int TO date_submitted;

ALTER TABLE mantis_project_file_table ADD COLUMN user_id INTEGER  ;

UPDATE mantis_project_file_table SET user_id=0;

ALTER TABLE mantis_project_file_table ALTER COLUMN user_id SET DEFAULT 0;

ALTER TABLE mantis_project_file_table ALTER COLUMN user_id SET NOT NULL;

ALTER TABLE mantis_bug_file_table ADD COLUMN user_id INTEGER  ;

UPDATE mantis_bug_file_table SET user_id=0;

ALTER TABLE mantis_bug_file_table ALTER COLUMN user_id SET DEFAULT 0;

ALTER TABLE mantis_bug_file_table ALTER COLUMN user_id SET NOT NULL;

ALTER TABLE mantis_custom_field_table DROP COLUMN advanced;

ALTER TABLE mantis_user_pref_table DROP COLUMN advanced_report;

ALTER TABLE mantis_user_pref_table DROP COLUMN advanced_view;

ALTER TABLE mantis_user_pref_table DROP COLUMN advanced_update;

CREATE INDEX idx_project_hierarchy_child_id ON mantis_project_hierarchy_table (child_id);

CREATE INDEX idx_project_hierarchy_parent_id ON mantis_project_hierarchy_table (parent_id);

CREATE INDEX idx_tag_name ON mantis_tag_table (name);

CREATE INDEX idx_bug_tag_tag_id ON mantis_bug_tag_table (tag_id);

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

commit;
mantis-1.sql (33,156 bytes)   
mantis.diff (1,049 bytes)    
--- mantis-0.sql	2012-10-23 05:47:41.824642741 +0200
+++ mantis-1.sql	2012-10-23 05:59:24.236579980 +0200
@@ -300,7 +300,7 @@
 advanced_view            BOOLEAN DEFAULT '0' NOT NULL,
 advanced_update          BOOLEAN DEFAULT '0' NOT NULL,
 refresh_delay            INTEGER DEFAULT 0 NOT NULL,
-redirect_delay           BOOLEAN DEFAULT '0' NOT NULL,
+redirect_delay           INTEGER DEFAULT 0 NOT NULL,
 bugnote_order            VARCHAR(4) DEFAULT 'ASC' NOT NULL,
 email_on_new             BOOLEAN DEFAULT '0' NOT NULL,
 email_on_assigned        BOOLEAN DEFAULT '0' NOT NULL,
@@ -442,12 +442,6 @@
                  PRIMARY KEY (basename)
 );
 
-ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay TYPE INTEGER;
-
-ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay SET DEFAULT 0;
-
-ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay SET NOT NULL;
-
 ALTER TABLE mantis_custom_field_table ALTER COLUMN possible_values TYPE TEXT;
 
 ALTER TABLE mantis_custom_field_table ALTER COLUMN possible_values SET DEFAULT '';
mantis.diff (1,049 bytes)   

Activities

niki

niki

2012-10-23 00:18

reporter   ~0033300

mantis-0.sql is the original SQL pasted from the install, just added begin/commit to it, and mantis-1.sql is the modified version used to install, and mantis.diff is the difference between the two.
niki

niki

2012-10-23 00:19

reporter   ~0033301

Last edited: 2012-10-23 00:21

But I was wondering why the installe is firest adding a table, and the later adds/modifies colums, instead of just creating the final one from the beginning.

So besides that, seemes like a smooth install :-)
dregad

dregad

2012-10-28 06:01

developer   ~0033318

There are known issues with PostgreSQL, related to the handling of booleans. On principle you shouldn't manually alter the schema, as it could break the ability to upgrade.

Would you be able and willing to test the installation process using the latest nightly build (1.2.12dev - download from http://www.mantisbt.org/builds/) and report the results here ?

I was wondering why the installe is firest adding a table, and the later
adds/modifies colums, instead of just creating the final one from the beginning.

This is to enable upgrades. Knowing the current schema version, the installer knows what changes need to be applied to bring it to the new version.
dregad

dregad

2014-07-07 11:19

developer   ~0040896

Can't reproduce this with latest master