/* 
 * migration script to update the db fro working with the new converter server architecture
 */
 
/* 
 * Domain for server status
 */
create domain SERVER_STATUS
	AS VARCHAR(4) not null
	CHECK (VALUE IN ('up', 'down'));

/* 
 * Domain for conversion status
 */
create domain CONVERSION_STATUS
	AS VARCHAR(11)
	DEFAULT 'undone' not null
	CHECK (VALUE IN ('outstanding', 'started', 'done', 'suspended'));

/*
 * Table for Converter timing
 */
create table CONVERTERTIMING (
	ID 						INTEGER not null,
	POLLING_PERIOD_MINUTES 	INTEGER default 30 not null,
	TIME_FRAME_START 		VARCHAR(5),
	TIME_FRAME_END 			VARCHAR(5),	
	constraint PK_CONVERTERTIMING primary key (ID)
);

insert into CONVERTERTIMING
            (ID, POLLING_PERIOD_MINUTES, TIME_FRAME_START, TIME_FRAME_END)
     values (1, 1, '00:00', '23:59');

/*
 * Table for conversion stati
 */
create table CONVERSIONSTATI (
     ID						INTEGER not null,
     STATUS 				CONVERSION_STATUS not null,
     STARTED 				TIMESTAMP,
     FINISHED 				TIMESTAMP,
     REITERATIONS 			INTEGER default 0 not null,
     COMMENT 				VARCHAR(255),
  constraint PK_CONVERSIONSTATI primary key (ID)
);

/*
 * Table for converters
 */
create table CONVERTER (
     ID 					INTEGER not null,
     MAX_REITERATIONS 		INTEGER default 1 not null,
     DOCUMENTTYPES 			VARCHAR(255),
     LOADFACTOR 			INTEGER default 1 not null,
     HOSTNAME 				VARCHAR(30) not null,
     PORT 					INTEGER not null,
     STATUS 				SERVER_STATUS,
     JOBS_IN_EXECUTION 		INTEGER default 0 not null,
     TIMINGID 				INTEGER default 1 not null,
  constraint PK_CONVERTER primary key (ID)
);

/*
 * Table for VKC Server
 */
create table VKCSERVER (
     ID 					INTEGER not null,
     LOADFACTOR 			INTEGER default 1 not null,
     HOSTNAME 				VARCHAR(30) not null,
     PORT 					INTEGER not null,
     JOBS_REQUESTED 		INTEGER default 0 not null,
     STATUS 				SERVER_STATUS,
  constraint PK_VKCSERVER primary key (ID)
);


/*
 * Structural changes
 */
alter table DOCUMENTS
	add CONVERSION_STATUS_ID INTEGER;

alter table DOCUMENTTYPES
	add CONVERTIBLE INTEGER;

update DOCUMENTTYPES set CONVERTIBLE = 0 WHERE DOCTYPEID = 0; /* unbekannt */
update DOCUMENTTYPES set CONVERTIBLE = 1 WHERE DOCTYPEID = 1; /* txt */
update DOCUMENTTYPES set CONVERTIBLE = 1 WHERE DOCTYPEID = 2; /* pdf */
update DOCUMENTTYPES set CONVERTIBLE = 1 WHERE DOCTYPEID = 3; /* doc */
update DOCUMENTTYPES set CONVERTIBLE = 1 WHERE DOCTYPEID = 4; /* html */
update DOCUMENTTYPES set CONVERTIBLE = 1 WHERE DOCTYPEID = 5; /* ps */
update DOCUMENTTYPES set CONVERTIBLE = 1 WHERE DOCTYPEID = 6; /* ppt */
update DOCUMENTTYPES set CONVERTIBLE = 1 WHERE DOCTYPEID = 7; /* xls */
update DOCUMENTTYPES set CONVERTIBLE = 1 WHERE DOCTYPEID = 8; /* rtf */
update DOCUMENTTYPES set CONVERTIBLE = 1 WHERE DOCTYPEID = 9; /* wpd */
update DOCUMENTTYPES set CONVERTIBLE = 0 WHERE DOCTYPEID = 10; /* tdf */
update DOCUMENTTYPES set CONVERTIBLE = 0 WHERE DOCTYPEID = 11; /* note */
update DOCUMENTTYPES set CONVERTIBLE = 0 WHERE DOCTYPEID = 12; /* link */
update DOCUMENTTYPES set CONVERTIBLE = 0 WHERE DOCTYPEID = 13; /* gif */
update DOCUMENTTYPES set CONVERTIBLE = 0 WHERE DOCTYPEID = 14; /* jpg */
update DOCUMENTTYPES set CONVERTIBLE = 0 WHERE DOCTYPEID = 15; /* bmp */
update DOCUMENTTYPES set CONVERTIBLE = 0 WHERE DOCTYPEID = 16; /* png */
update DOCUMENTTYPES set CONVERTIBLE = 0 WHERE DOCTYPEID = 17; /* exe */
update DOCUMENTTYPES set CONVERTIBLE = 0 WHERE DOCTYPEID = 18; /* zip */
update DOCUMENTTYPES set CONVERTIBLE = 0 WHERE DOCTYPEID = 19; /* xml */
	
/* 
 * Constraints 
 */
alter table DOCUMENTS
   add constraint FK_CONVERSIONSTATI foreign key (CONVERSION_STATUS_ID)
      references CONVERSIONSTATI (ID);
      
alter table CONVERTER
   add constraint FK_CONVERTERTIMING foreign key (TIMINGID)
      references CONVERTERTIMING (ID);
      
           
/*
 * Clean up
 */
delete from SYSPARAMETERS WHERE CONTEXT = 'CONVERTER';

update SYSPARAMETERS set VALUESTRING = '32' where CONTEXT = 'SYSTEMINFO' and name = 'DBVersionLow';
update SYSPARAMETERS set VALUESTRING = '2004-02-21' where CONTEXT = 'SYSTEMINFO' and name = 'DBCreationDate';
commit;
