**************************************************************************************
* Bemærk at denne fil kun er til orientering om databaseændringer til denne release. *
* Filen MÅ IKKE afvikles!                                                            *
**************************************************************************************

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'SydslesvigBevis' AND cname = 'webUdstederBrugerId') THEN
  ALTER TABLE SydslesvigBevis ADD webUdstederBrugerId varchar(80);
END IF;
-- da sydslesvig ikke tideligere har anvendt Ludus web giver denne konvertering måske ikke noget
--UPDATE SydslesvigBevis b SET b.webUdstederBrugerId =  (Select max(w.brugerId) from WebBruger w where w.ludusBrugerId = b.brugerId and w.brugerType = 'ADMINISTRATION'); COMMIT;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'SydslesvigBevis' AND cname = 'provekommisionen') THEN
  ALTER TABLE SydslesvigBevis ADD provekommisionen varchar(255);
END IF;


IF EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'SydslesvigBevis' and role = 'udsteder' ) THEN
  ALTER TABLE SydslesvigBevis DROP FOREIGN KEY udsteder;
END IF;
IF EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'SydslesvigBevis' AND cname = 'brugerId') THEN
  ALTER TABLE SydslesvigBevis MODIFY brugerId char(20) NULL;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'KursistStamdataSydslesvig' AND cname = 'fag1Niveau') THEN
  ALTER TABLE KursistStamdataSydslesvig ADD fag1Niveau varchar(10);
END IF;
IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'KursistStamdataSydslesvig' AND cname = 'fag2Niveau') THEN
  ALTER TABLE KursistStamdataSydslesvig ADD fag2Niveau varchar(10);
END IF;
IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'KursistStamdataSydslesvig' AND cname = 'fag3Niveau') THEN
  ALTER TABLE KursistStamdataSydslesvig ADD fag3Niveau varchar(10);
END IF;
IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'KursistStamdataSydslesvig' AND cname = 'fag4Niveau') THEN
  ALTER TABLE KursistStamdataSydslesvig ADD fag4Niveau varchar(10);
END IF;

truncate table EventQueue;commit;

IF NOT EXISTS(select 1 from LUDUSWebProperty where propertyName = 'ACTIVE_LUDUS_MOBILE') THEN
    INSERT INTO LUDUSWebProperty (propertyName, value) VALUES ('ACTIVE_LUDUS_MOBILE', 'true'); COMMIT;
END IF;


DROP TABLE IF EXISTS SaerligeProevevilkaar;
CREATE TABLE SaerligeProevevilkaar
(
     id INTEGER NOT NULL DEFAULT AUTOINCREMENT,
    kode varchar(10) NOT NULL UNIQUE,
    tekst varchar(80), 
    udgaaet BIT DEFAULT 0,
	version INTEGER,
    PRIMARY KEY (id)
);

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'EksamenTilmelding' AND cname = 'saerligeProevevilkaar_id') 
THEN
    ALTER TABLE EksamenTilmelding ADD saerligeProevevilkaar_id INTEGER;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'EksamenTilmelding' AND cname = 'saerligeProevevilkaarBemaerkning')
THEN
    ALTER TABLE EksamenTilmelding ADD saerligeProevevilkaarBemaerkning VARCHAR(255);
END IF;

IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'EksamenTilmelding' and role = 'SaerligeProevevilkaar' ) THEN
   ALTER TABLE EksamenTilmelding ADD FOREIGN KEY saerligeProevevilkaar(saerligeProevevilkaar_id) REFERENCES saerligeproevevilkaar(id) ON UPDATE CASCADE;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'karakter' AND cname = 'saerligeProevevilkaar_id') 
THEN
    ALTER TABLE karakter ADD saerligeProevevilkaar_id INTEGER;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'karakter' AND cname = 'saerligeProevevilkaarBemaerkning')
THEN
    ALTER TABLE karakter ADD saerligeProevevilkaarBemaerkning VARCHAR(255);
END IF;

IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'karakter' and role = 'SaerligeProevevilkaar' ) THEN
   ALTER TABLE karakter ADD FOREIGN KEY saerligeProevevilkaar(saerligeProevevilkaar_id) REFERENCES saerligeproevevilkaar(id) ON UPDATE CASCADE;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'KarakterSlettet' AND cname = 'saerligeProevevilkaar_id') 
THEN
    ALTER TABLE KarakterSlettet  ADD saerligeProevevilkaar_id INTEGER;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'KarakterSlettet' AND cname = 'saerligeProevevilkaarBemaerkning')
THEN
    ALTER TABLE KarakterSlettet  ADD saerligeProevevilkaarBemaerkning VARCHAR(255);
END IF;

IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'KarakterSlettet' and role = 'SaerligeProevevilkaar' ) THEN
   ALTER TABLE KarakterSlettet ADD FOREIGN KEY saerligeProevevilkaar(saerligeProevevilkaar_id) REFERENCES saerligeproevevilkaar(id) ON UPDATE CASCADE;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'KarakterAendret' AND cname = 'gammelSaerligeProevevilkaar_id') 
THEN
    ALTER TABLE KarakterAendret ADD gammelSaerligeProevevilkaar_id INTEGER;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'KarakterAendret' AND cname = 'nySaerligeProevevilkaar_id') 
THEN
    ALTER TABLE KarakterAendret  ADD nySaerligeProevevilkaar_id INTEGER;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'KarakterAendret' AND cname = 'gammelSaerligeProevevilkaarBemaerkning')
THEN
    ALTER TABLE KarakterAendret  ADD gammelSaerligeProevevilkaarBemaerkning VARCHAR(255);
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'KarakterAendret' AND cname = 'nySaerligeProevevilkaarBemaerkning')
THEN
    ALTER TABLE KarakterAendret  ADD nySaerligeProevevilkaarBemaerkning VARCHAR(255);
END IF;

IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'KarakterAendret' and role = 'gammelSaerligeProevevilkaar' ) THEN
   ALTER TABLE KarakterAendret ADD FOREIGN KEY gammelSaerligeProevevilkaar(gammelSaerligeProevevilkaar_id) REFERENCES saerligeproevevilkaar(id) ON UPDATE CASCADE;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'KarakterAendret' and role = 'nySaerligeProevevilkaar' ) THEN
   ALTER TABLE KarakterAendret ADD FOREIGN KEY nySaerligeProevevilkaar(nySaerligeProevevilkaar_id) REFERENCES saerligeproevevilkaar(id) ON UPDATE CASCADE;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'ArkivKarakter' AND cname = 'saerligeProevevilkaar') 
THEN
    ALTER TABLE ArkivKarakter ADD saerligeProevevilkaar VARCHAR(10);
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'ArkivKarakter' AND cname = 'saerligeProevevilkaarBemaerkning')
THEN
    ALTER TABLE ArkivKarakter ADD saerligeProevevilkaarBemaerkning VARCHAR(255);
END IF;

;COMMIT;QUIT;