CONNECT TO JIRADB;

-- Begin Notification table
rename table NOTIFICATION to TMP_NOTIFICATION;

CREATE TABLE NOTIFICATION (
  ID    DECIMAL(18,0)   NOT NULL,
  SCHEME        DECIMAL(18,0),
  EVENT VARCHAR(60),
  EVENT_TYPE_ID DECIMAL(18,0),
  TEMPLATE_ID   DECIMAL(18,0),
  NOTIF_TYPE    VARCHAR(60),
  NOTIF_PARAMETER       VARCHAR(60)
);
  

ALTER TABLE NOTIFICATION
  ADD CONSTRAINT PK_NOTIFICATION PRIMARY KEY
    (ID);

insert into NOTIFICATION (select ID, SCHEME, EVENT, EVENT_TYPE_ID, TEMPLATE_ID, TYPE, PARAMETER from TMP_NOTIFICATION);

drop table TMP_NOTIFICATION;
-- End Notification table 

-- Begin MailServer table
rename table MAILSERVER to TMP_MAILSERVER;

CREATE TABLE MAILSERVER (
  ID	DECIMAL(18,0)	NOT NULL,
  NAME	VARCHAR(255),
  DESCRIPTION	CLOB(1M)	LOGGED	NOT COMPACT,
  MAILFROM	VARCHAR(255),
  PREFIX	VARCHAR(60),
  SMTP_PORT	VARCHAR(60),
  SERVER_TYPE	VARCHAR(60),
  SERVERNAME	VARCHAR(255),
  JNDILOCATION	VARCHAR(255),
  MAILUSERNAME	VARCHAR(255),
  MAILPASSWORD	VARCHAR(255)
) ;
  

ALTER TABLE MAILSERVER
  ADD CONSTRAINT PK_MAILSERVER PRIMARY KEY
    (ID);

insert into MAILSERVER (select ID, NAME, DESCRIPTION, MAILFROM, PREFIX, SMTP_PORT, TYPE, SERVERNAME, JNDILOCATION, MAILUSERNAME, MAILPASSWORD from TMP_MAILSERVER);

drop table TMP_MAILSERVER;
-- End MailServer table 

-- Begin JiraEventType table
rename table JIRAEVENTTYPE to TMP_JIRAEVENTTYPE;

CREATE TABLE JIRAEVENTTYPE (
  ID	DECIMAL(18,0)	NOT NULL,
  TEMPLATE_ID	DECIMAL(18,0),
  NAME	VARCHAR(255),
  DESCRIPTION	CLOB(1M)	LOGGED	NOT COMPACT,
  EVENT_TYPE	VARCHAR(60)
);
  

ALTER TABLE JIRAEVENTTYPE
  ADD CONSTRAINT PK_JIRAEVENTTYP PRIMARY KEY
    (ID);

insert into JIRAEVENTTYPE (select ID, TEMPLATE_ID, NAME, DESCRIPTION, TYPE from TMP_JIRAEVENTTYPE);

drop table TMP_JIRAEVENTTYPE;
-- End JiraEventType table

-- Begin SchemePermissions table
rename table SCHEMEPERMISSIONS to TMP_SCHEMEPERMISSIONS;

CREATE TABLE SCHEMEPERMISSIONS (
  ID	DECIMAL(18,0)	NOT NULL,
  SCHEME	DECIMAL(18,0),
  PERMISSION	DECIMAL(18,0),
  PERM_TYPE	VARCHAR(255),
  PERM_PARAMETER	VARCHAR(255)
);
  

ALTER TABLE SCHEMEPERMISSIONS
  ADD CONSTRAINT PK_SCHEMEPERMIS PRIMARY KEY
    (ID);

insert into SCHEMEPERMISSIONS (select ID, SCHEME, PERMISSION, TYPE, PARAMETER from TMP_SCHEMEPERMISSIONS);

drop table TMP_SCHEMEPERMISSIONS;
-- End SchemePermissions table

-- Begin FieldLayout table
rename table FIELDLAYOUT to TMP_FIELDLAYOUT;

CREATE TABLE FIELDLAYOUT (
  ID	DECIMAL(18,0)	NOT NULL,
  NAME	VARCHAR(255),
  DESCRIPTION	VARCHAR(255),
  LAYOUT_TYPE	VARCHAR(255),
  LAYOUTSCHEME	DECIMAL(18,0)
);
  

ALTER TABLE FIELDLAYOUT
  ADD CONSTRAINT PK_FIELDLAYOUT PRIMARY KEY
    (ID);

insert into FIELDLAYOUT (select ID, NAME, DESCRIPTION, TYPE, LAYOUTSCHEME from TMP_FIELDLAYOUT);

drop table TMP_FIELDLAYOUT;
-- End FieldLayout table

-- Begin SchemeIssueSecurities table
rename table SCHEMEISSUESECURITIES to TMP_SCHEMEISSUESECURITIES;

CREATE TABLE SCHEMEISSUESECURITIES (
  ID	DECIMAL(18,0)	NOT NULL,
  SCHEME	DECIMAL(18,0),
  SECURITY	DECIMAL(18,0),
  SEC_TYPE	VARCHAR(255),
  SEC_PARAMETER	VARCHAR(255)
);
  

ALTER TABLE SCHEMEISSUESECURITIES
  ADD CONSTRAINT PK_SCHEMEISSUES PRIMARY KEY
    (ID);

insert into SCHEMEISSUESECURITIES (select ID, SCHEME, SECURITY, TYPE, PARAMETER from TMP_SCHEMEISSUESECURITIES);

drop table TMP_SCHEMEISSUESECURITIES;
-- End SchemeIssueSecurities table


-- Begin PortletConfiguration table
rename table PORTLETCONFIGURATION to TMP_PORTLETCONFIGURATION;

CREATE TABLE PORTLETCONFIGURATION (
  ID	DECIMAL(18,0)	NOT NULL,
  PORTALPAGE	DECIMAL(18,0),
  PORTLET_ID	VARCHAR(255),
  COLUMN_NUMBER	INTEGER,
  POSITIONSEQ	INTEGER
);

ALTER TABLE PORTLETCONFIGURATION
  ADD CONSTRAINT PK_PORTLETCONFI PRIMARY KEY
    (ID);

insert into PORTLETCONFIGURATION (select ID, PORTALPAGE, PORTLET_ID, COLUMN_NUMBER, POSITION from TMP_PORTLETCONFIGURATION);

drop table TMP_PORTLETCONFIGURATION;
-- End PortletConfiguration table

-- Rename the version table
rename table VERSION to PROJECTVERSION;

COMMIT;

CONNECT RESET;

TERMINATE;