update SYSPARAMETERS set VALUESTRING = '1'  where CONTEXT = 'SYSTEMINFO' and name = 'DBVersionHigh';
update SYSPARAMETERS set VALUESTRING = '187' where CONTEXT = 'SYSTEMINFO' and name = 'DBVersionLow';
update SYSPARAMETERS set VALUESTRING = '2007-03-01' where CONTEXT = 'SYSTEMINFO' and name = 'DBCreationDate';


/* referential tables for note and descriptions */
create table NOTES(
	noteId INTEGER NOT NULL,
	objectId INTEGER,
	sourceTable varchar(255),
	successorId INTEGER,
	text varchar(4000),
CONSTRAINT PK_NOTES PRIMARY KEY (noteId)
);
 
create generator Notes_SEQ;
set generator NOTES_SEQ to 1;

insert into NOTES(noteId,objectId,text) select (select GEN_ID(NOTES_SEQ, 1) FROM RDB$DATABASE), documentId, description from Documents WHERE Description is not null and Description<>'';
update notes set sourceTable='DOCUMENTS' where sourceTable is null;
insert into NOTES(noteId,objectId,text) select  (select GEN_ID(NOTES_SEQ, 1) FROM RDB$DATABASE),categoryId, description from Categories WHERE Description is not null and Description<>'';
update notes set sourceTable='CATEGORIES' where sourceTable is null;
insert into NOTES(noteId,objectId,text) select  (select GEN_ID(NOTES_SEQ, 1) FROM RDB$DATABASE),newsId, newsText from news WHERE newsText is not null and newsText<>'';
update notes set sourceTable='NEWS' where sourceTable is null;

ALTER TABLE documents DROP  description;
ALTER TABLE Categories DROP  description;
ALTER TABLE NEWS DROP  NewsText;

ALTER TABLE documents ADD noteId INTEGER;
ALTER TABLE Categories ADD noteId INTEGER;
ALTER TABLE NEWS ADD noteId INTEGER;

update documents set noteId=
(select noteId from notes where notes.sourceTable='DOCUMENTS' and notes.objectId=documents.documentId );
update Categories set noteId=
(select noteId from notes where notes.sourceTable='CATEGORIES' and notes.objectId=categories.categoryId );
update NEWS set noteId=
(select noteId from notes where notes.sourceTable='NEWS' and notes.objectId=news.newsId );
commit;