This shows you the differences between two versions of the page.
— |
mycolex-1.4_1.5_database-update [2017/06/09 12:59] (current) Stefan Bürer |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== myColex-1.4_1.5_database-update ====== | ||
+ | < | ||
+ | |||
+ | # =============Update myColex 1.4 -> 1.5.1============= | ||
+ | # | ||
+ | # There are some changes in the data structure of the database, some new tables and the like | ||
+ | # Please migrate your data with the following instructions and drop the tables | ||
+ | |||
+ | |||
+ | -- -------------------------------------------------------- | ||
+ | -- ---------BACKUP YOUR DATA!!!!!!!!!!!!!------------------ | ||
+ | -- -------------------------------------------------------- | ||
+ | DROP TABLE IF EXISTS export; | ||
+ | CREATE TABLE export ( | ||
+ | ExID int(11) NOT NULL AUTO_INCREMENT, | ||
+ | SuUser varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | ExLang varchar(2) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | ExStartDate date DEFAULT NULL, | ||
+ | ExPlanDate date DEFAULT NULL, | ||
+ | ExPortDate date DEFAULT NULL, | ||
+ | ExTitel varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | ExTarget varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | ExBemerkung varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | ExSysDat datetime DEFAULT ' | ||
+ | ExSysAut varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | ExSysChDat datetime DEFAULT NULL, | ||
+ | ExSysChAut varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | ExSysTS timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, | ||
+ | PRIMARY KEY (ExID), | ||
+ | KEY xf_Export_SuUser (SuUser), | ||
+ | KEY xf_Export_Titel (ExTitel) | ||
+ | ) ENGINE=MyISAM | ||
+ | |||
+ | DROP TABLE IF EXISTS exportmedium; | ||
+ | CREATE TABLE exportmedium ( | ||
+ | EmID int(11) NOT NULL AUTO_INCREMENT, | ||
+ | ExID int(11) DEFAULT ' | ||
+ | MdID int(11) DEFAULT ' | ||
+ | EmUID varchar(24) COLLATE utf8_unicode_ci DEFAULT '', | ||
+ | EmStatus tinyint(4) DEFAULT ' | ||
+ | EmTitel varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | EmText text COLLATE utf8_unicode_ci, | ||
+ | EmNr tinyint(4) DEFAULT NULL, | ||
+ | EmBemerkung varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | EmSysDat datetime DEFAULT NULL, | ||
+ | EmSysAut varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | EmSysChDat datetime DEFAULT NULL, | ||
+ | EmSysChAut varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | EmSysTS timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, | ||
+ | PRIMARY KEY (EmID), | ||
+ | KEY xf_em_MdID (MdID), | ||
+ | KEY xf_em_ExID (ExID), | ||
+ | KEY xn_em_Status (EmStatus), | ||
+ | KEY xn_em_Titel (EmTitel) | ||
+ | ) ENGINE=MyISAM | ||
+ | |||
+ | DROP TABLE IF EXISTS exportobjekt; | ||
+ | CREATE TABLE exportobjekt ( | ||
+ | EoID int(11) NOT NULL AUTO_INCREMENT, | ||
+ | ExID int(11) DEFAULT ' | ||
+ | ObID int(11) DEFAULT ' | ||
+ | EoUID varchar(24) COLLATE utf8_unicode_ci DEFAULT '', | ||
+ | EoOwner varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | EoGewichtung int(11) NOT NULL DEFAULT ' | ||
+ | EoStatus tinyint(4) DEFAULT ' | ||
+ | EoTitel varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | EoData text COLLATE utf8_unicode_ci, | ||
+ | EoText text COLLATE utf8_unicode_ci, | ||
+ | EoTarget varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | EoGruppe varchar(129) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | EoBemerkung varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | EoSysDat datetime DEFAULT NULL, | ||
+ | EoSysAut varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | EoSysChDat datetime DEFAULT NULL, | ||
+ | EoSysChAut varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | EoSysTS timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, | ||
+ | PRIMARY KEY (EoID), | ||
+ | KEY xf_eo_ObID (ObID), | ||
+ | KEY xf_eo_ExID (ExID), | ||
+ | KEY xn_eo_Status (EoStatus), | ||
+ | KEY xn_eo_Titel (EoTitel), | ||
+ | KEY EoOwner (EoOwner) | ||
+ | ) ENGINE=MyISAM | ||
+ | |||
+ | DROP TABLE IF EXISTS alarm; | ||
+ | CREATE TABLE alarm ( | ||
+ | AlID int(11) NOT NULL AUTO_INCREMENT, | ||
+ | ObID int(11) DEFAULT NULL, | ||
+ | PeID int(11) DEFAULT NULL, | ||
+ | AuID int(11) DEFAULT NULL, | ||
+ | ErID int(11) DEFAULT NULL, | ||
+ | AlDate date DEFAULT NULL, | ||
+ | SuUser varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | SuGroup int(11) DEFAULT NULL, | ||
+ | AlPublic tinyint(4) DEFAULT ' | ||
+ | AlStatus tinyint(4) NOT NULL DEFAULT ' | ||
+ | AlAlarm varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AlAntwort varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AlBemerkung varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AlSysDat datetime DEFAULT NULL, | ||
+ | AlSysAut varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AlSysChDat datetime DEFAULT NULL, | ||
+ | AlSysChAut varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AlSysTS timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, | ||
+ | PRIMARY KEY (AlID), | ||
+ | KEY xf_Alarm_ObID (ObID), | ||
+ | KEY xf_Alarm_PeID (PeID), | ||
+ | KEY xf_Alarm_AfID (AuID), | ||
+ | KEY xf_Alarm_ErID (ErID), | ||
+ | KEY xf_Alarm_SuUser (SuUser), | ||
+ | KEY xf_Alarm_AlOK (AlStatus), | ||
+ | KEY xf_Alarm_AlPublic (AlPublic), | ||
+ | KEY xn_Alarm_AlDate (AlDate) | ||
+ | ) ENGINE=MyISAM | ||
+ | |||
+ | ALTER TABLE `person` ADD `PeParentID` INT NULL AFTER `PeID`; | ||
+ | |||
+ | DROP TABLE IF EXISTS ausstellung; | ||
+ | CREATE TABLE ausstellung ( | ||
+ | AuID int(11) NOT NULL AUTO_INCREMENT, | ||
+ | AfGUID varchar(48) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | PeID int(11) DEFAULT ' | ||
+ | AuAuftrag varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AuOrt varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AuRaum varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AuProjektleitung varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AuKuerzel varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AuAuftragnehmer varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AuAnlass varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AuStartD date DEFAULT NULL, | ||
+ | AuEndeD date DEFAULT NULL, | ||
+ | AuEroeffnungD date DEFAULT NULL, | ||
+ | AuSchlussD date DEFAULT NULL, | ||
+ | AuTerminD date DEFAULT NULL, | ||
+ | AuVorbescheidD date DEFAULT NULL, | ||
+ | AuGesuchD date DEFAULT NULL, | ||
+ | AuUmlaufD date DEFAULT NULL, | ||
+ | AuKommissionD date DEFAULT NULL, | ||
+ | AuAntwortD date DEFAULT NULL, | ||
+ | AuVertragD date DEFAULT NULL, | ||
+ | AuVertragZurueckD date DEFAULT NULL, | ||
+ | AuObAusgabe date DEFAULT NULL, | ||
+ | AuObZurueck date DEFAULT NULL, | ||
+ | AuVersicherung varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AuTransport varchar(254) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AuKurier tinyint(4) DEFAULT NULL, | ||
+ | AuVertragszusatz text COLLATE utf8_unicode_ci, | ||
+ | AuRechdatum date DEFAULT NULL, | ||
+ | AuRechbetrag int(11) DEFAULT NULL, | ||
+ | AuBezahltD date DEFAULT NULL, | ||
+ | AuErledigtD date DEFAULT NULL, | ||
+ | AuErledigt tinyint(1) DEFAULT NULL, | ||
+ | AuBelegD date DEFAULT NULL, | ||
+ | AuAusleihe tinyint(1) DEFAULT NULL, | ||
+ | AuKommission tinyint(1) DEFAULT NULL, | ||
+ | AuDirektion varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AuBemerkung text COLLATE utf8_unicode_ci, | ||
+ | AuSysDat datetime DEFAULT NULL, | ||
+ | AuSysAut varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AuSysChDat datetime DEFAULT NULL, | ||
+ | AuSysChAut varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AuSysTS timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, | ||
+ | PRIMARY KEY (AuID), | ||
+ | KEY xf_Auftrag_PeID (PeID) | ||
+ | ) ENGINE=MyISAM | ||
+ | |||
+ | -- -------------------------------------------------------- | ||
+ | |||
+ | -- | ||
+ | -- create table ' | ||
+ | -- | ||
+ | |||
+ | DROP TABLE IF EXISTS ausstellungsobjekt; | ||
+ | CREATE TABLE ausstellungsobjekt ( | ||
+ | AoID int(11) NOT NULL AUTO_INCREMENT, | ||
+ | AoGUID varchar(48) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AuID int(11) DEFAULT NULL, | ||
+ | ObID int(11) DEFAULT NULL, | ||
+ | AoBemerkung varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AoText text COLLATE utf8_unicode_ci, | ||
+ | AoZustaendigkeit varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AoKosten decimal(9, | ||
+ | AoAusleihe tinyint(4) DEFAULT NULL, | ||
+ | AoRestAusleihe tinyint(4) DEFAULT NULL, | ||
+ | AoBericht text COLLATE utf8_unicode_ci, | ||
+ | AoVerzicht tinyint(4) DEFAULT NULL, | ||
+ | AoTransport varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AoAusgabeD date DEFAULT NULL, | ||
+ | AoZurueckD date DEFAULT NULL, | ||
+ | AoKontrolle varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AoSysDat datetime DEFAULT NULL, | ||
+ | AoSysAut varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AoSysChDat datetime DEFAULT NULL, | ||
+ | AoSysChAut varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AoSysTS timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, | ||
+ | PRIMARY KEY (AoID), | ||
+ | KEY xf_Auftragsposition_AfID (AuID), | ||
+ | KEY xf_Auftragsposition_ObID (ObID) | ||
+ | ) ENGINE=MyISAM | ||
+ | |||
+ | |||
+ | # migration from auftrag -> ausstellung | ||
+ | |||
+ | INSERT INTO ausstellung ( AuID, PeID, AuAuftrag, AuOrt, AuRaum, AuProjektleitung, | ||
+ | SELECT auftrag.AfID, | ||
+ | FROM auftrag; | ||
+ | |||
+ | # migration from auftragsposition -> ausstellungsobjekt | ||
+ | |||
+ | INSERT INTO ausstellungsobjekt ( AoID, AuID, ObID, AoBemerkung, | ||
+ | SELECT auftragsposition.ApID, | ||
+ | FROM auftragsposition; | ||
+ | |||
+ | -- | ||
+ | -- create table ' | ||
+ | -- | ||
+ | |||
+ | DROP TABLE IF EXISTS aufgabe; | ||
+ | CREATE TABLE aufgabe ( | ||
+ | AgID int(11) NOT NULL AUTO_INCREMENT, | ||
+ | AgAufgabe varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AgBemerkung text COLLATE utf8_unicode_ci, | ||
+ | AgSysDat datetime DEFAULT ' | ||
+ | AgSysAut varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AgSysChDat datetime DEFAULT NULL, | ||
+ | AgSysChAut varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AgSysTS timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, | ||
+ | PRIMARY KEY (AgID) | ||
+ | ) ENGINE=MyISAM | ||
+ | |||
+ | -- -------------------------------------------------------- | ||
+ | |||
+ | -- | ||
+ | -- drop and create table ' | ||
+ | -- | ||
+ | |||
+ | DROP TABLE IF EXISTS auftrag; | ||
+ | CREATE TABLE auftrag ( | ||
+ | AfID int(11) NOT NULL AUTO_INCREMENT, | ||
+ | AfGeber varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AfNehmer varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AfAuftrag varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AfDatum date DEFAULT NULL, | ||
+ | AfTermin date DEFAULT NULL, | ||
+ | AfErledigt date DEFAULT NULL, | ||
+ | AfBeschreibung text COLLATE utf8_unicode_ci, | ||
+ | AfBemerkung text COLLATE utf8_unicode_ci, | ||
+ | AfSysDat datetime DEFAULT ' | ||
+ | AfSysAut varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AfSysChDat datetime DEFAULT NULL, | ||
+ | AfSysChAut varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | AfSysTS timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, | ||
+ | PRIMARY KEY (AfID), | ||
+ | KEY xf_AfGeber (AfGeber), | ||
+ | KEY xf_AfNehmer (AfNehmer) | ||
+ | ) ENGINE=MyISAM | ||
+ | |||
+ | -- -------------------------------------------------------- | ||
+ | |||
+ | -- | ||
+ | -- drop and create table ' | ||
+ | -- | ||
+ | |||
+ | DROP TABLE IF EXISTS auftragsposition; | ||
+ | CREATE TABLE auftragsposition ( | ||
+ | ApID int(11) NOT NULL AUTO_INCREMENT, | ||
+ | AfID int(11) NOT NULL DEFAULT ' | ||
+ | ApPerson varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | ApAufgabe varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | ApDatum date DEFAULT NULL, | ||
+ | ApTermin date DEFAULT NULL, | ||
+ | ApErledigt date DEFAULT NULL, | ||
+ | ApBeschreibung text COLLATE utf8_unicode_ci, | ||
+ | ApBemerkung text COLLATE utf8_unicode_ci, | ||
+ | ApSysDat datetime DEFAULT ' | ||
+ | ApSysAut varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | ApSysChDat datetime DEFAULT NULL, | ||
+ | ApSysChAut varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, | ||
+ | ApSysTS timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, | ||
+ | PRIMARY KEY (ApID), | ||
+ | KEY xf_AfID (AfID), | ||
+ | KEY xf_ApPerson (ApPerson) | ||
+ | ) ENGINE=MyISAM | ||
+ | </ | ||