HISinOne-Doku: SP-Extrakt- Changelog:20160501 - KDV-FH

HISinOne-Doku: SP-ExtraktChangelog:20160501
Inhaltsverzeichnis

1 Änderungen an den Extraktionsskripten
o 1.1 Überblick
o 1.2 Hinweise
o 1.3 HZBArt (Satzart) erweitern
o 1.4 HZBSchulart (Satzart) erzeugen
o 1.5 HZBTyp (Satzart) erzeugen
o 1.6 allgemeine Änderungen
Änderungen an den Extraktionsskripten
(15.05.2015 bis 01.05.2016)
Überblick
Neuerungen:




neue Spalten HZBArt.csv
neue Datei HZBSchulart.csv
neue Datei HZBTyp.csv
allgemeine Änderungen
Hinweise
Bisher sollte die Tabelle HZB-Art-Gruppe die verschiedenen HZB-Arten genauer gruppieren.
Da diese Gruppierungen jedoch von jeder Hochschule frei geschaffen werden können, soll
dies nun vereinheitlicht werden. Dazu werden 2 neue Tabellen eingeführt die
hochschulübergreifend die verschieden HZB-Arten genauer erklären. Diese sind HZBSchulart und HZB-Typ. HZB-Schulart ist ähnlich zur alten Tabelle HZB-Art-Gruppe, nur das
die Bezeichnungen allgemein für alle Hochschulen gelten. Sie enthält Daten zur Schulart, z.B.
Gymnasium, FOS, BOS. Da in den verschiedenen Schularten verschiedene
Hochschulzulassungsberechtigungen erworben werden können beinhaltet die Tabelle HZBTyp die genauen Abschlüsse wie z.B. allgemeine Hochschulreife, fachbundene
Hochschulreife, BachelorUni, BachelorHS, MasterUni, MasterHS usw. Neben den beiden
neuen Tabellen muss die vorhandenen Tabelle HZBArt erweitert werden.
HZBArt (Satzart) erweitern
Damit der KDV-Extraktor die neue CSV-Datei erzeugen kann muss das Kdv-ExtraktorSchema erweitert werden. Dies passiert durch das Preprocessing-SQL im Extraktor. Bitte
ersetzen Sie das alte SQL mit dem Folgenden:
/*
* Tabelle für HZBArtMapping vorbereiten, wenn sie noch nicht existiert.
*/
CREATE OR REPLACE FUNCTION kdv_extraktor.kdv_create_extraktor_hzbartmapping()
RETURNS VARCHAR(256) AS $$
BEGIN
IF NOT EXISTS(SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_NAME =
'mapping_hzbart_hzbartgruppe') THEN
CREATE TABLE kdv_extraktor.mapping_hzbart_hzbartgruppe(HZBArtGruppe_KNZ
VARCHAR(10), HZBArt_KNZ VARCHAR(10) NOT NULL, HZBArt_KURZBEZ VARCHAR(256), PRIMARY
KEY (HZBArt_KNZ));
INSERT INTO kdv_extraktor.mapping_hzbart_hzbartgruppe SELECT DISTINCT NULL AS
HZBArtGruppe_KNZ, k_hzbart.hzbart AS HZBArt_KNZ, k_hzbart.ktxt AS HZBArt_KURZBEZ
FROM k_hzbart;
END IF;
IF NOT EXISTS(SELECT column_name FROM information_schema.COLUMNS WHERE column_name
= 'hzbtyp_knz' AND TABLE_NAME = 'mapping_hzbart_hzbartgruppe') THEN
ALTER TABLE kdv_extraktor.mapping_hzbart_hzbartgruppe ADD hzbtyp_knz
VARCHAR(10);
ALTER TABLE kdv_extraktor.mapping_hzbart_hzbartgruppe ADD hzbschulart_knz
VARCHAR(10);
/* Default-Werte-Zuordnung "HZBSchulart_KNZ" */
UPDATE kdv_extraktor.mapping_hzbart_hzbartgruppe SET hzbschulart_knz = 'AUS'
WHERE hzbart_knz IN ('79','39','59');
UPDATE kdv_extraktor.mapping_hzbart_hzbartgruppe SET hzbschulart_knz = 'BOS'
WHERE hzbart_knz IN ('21','47','65','44');
UPDATE kdv_extraktor.mapping_hzbart_hzbartgruppe SET hzbschulart_knz = 'BQU'
WHERE hzbart_knz IN ('53','73','71','34');
UPDATE kdv_extraktor.mapping_hzbart_hzbartgruppe SET hzbschulart_knz = 'FOS'
WHERE hzbart_knz IN ('67','66','48','28');
UPDATE kdv_extraktor.mapping_hzbart_hzbartgruppe SET hzbschulart_knz = 'GYM'
WHERE hzbart_knz IN ('18','17','64','60','27','70','43','03');
UPDATE kdv_extraktor.mapping_hzbart_hzbartgruppe SET hzbschulart_knz = 'SON'
WHERE hzbart_knz IN
('95','72','29','46','98','99','15','35','51','91','75','86','87','83','85','81','8
2','84','68','94',
'76','09','30','49','55','24','33','12','45','78','93','92','74','52','77','37','06
','31','62','96');
/* Default-Werte-Zuordnung "HZBTyp_KNZ" */
UPDATE kdv_extraktor.mapping_hzbart_hzbartgruppe SET hzbtyp_knz = 'aHR' WHERE
hzbart_knz IN
('28','17','03','09','24','33','12','37','06','15','35','91','21','34','27','18','3
1','29');
UPDATE kdv_extraktor.mapping_hzbart_hzbartgruppe SET hzbtyp_knz = 'FHR' WHERE
hzbart_knz IN
('73','71','67','70','62','66','64','68','76','78','93','74','77','75','72','65','6
0');
UPDATE kdv_extraktor.mapping_hzbart_hzbartgruppe SET hzbtyp_knz = 'fgHR' WHERE
hzbart_knz IN ('44','43','46','51','48','49','55','45','47','92','52','53');
UPDATE kdv_extraktor.mapping_hzbart_hzbartgruppe SET hzbtyp_knz = 'fgHR Ausld'
WHERE hzbart_knz IN ('59');
UPDATE kdv_extraktor.mapping_hzbart_hzbartgruppe SET hzbtyp_knz = 'FHR Ausld'
WHERE hzbart_knz IN ('79');
UPDATE kdv_extraktor.mapping_hzbart_hzbartgruppe SET hzbtyp_knz = 'aHR Ausld'
WHERE hzbart_knz IN ('39');
UPDATE kdv_extraktor.mapping_hzbart_hzbartgruppe SET hzbtyp_knz = 'FHR o.A.'
WHERE hzbart_knz IN ('96');
UPDATE kdv_extraktor.mapping_hzbart_hzbartgruppe SET hzbtyp_knz = 'fgHR o.A.'
WHERE hzbart_knz IN ('95');
UPDATE kdv_extraktor.mapping_hzbart_hzbartgruppe SET hzbtyp_knz = 'aHR o.A.'
WHERE hzbart_knz IN ('94');
UPDATE kdv_extraktor.mapping_hzbart_hzbartgruppe SET hzbtyp_knz = 'bachelorU'
WHERE hzbart_knz IN ('82');
UPDATE kdv_extraktor.mapping_hzbart_hzbartgruppe SET hzbtyp_knz = 'masterU'
WHERE hzbart_knz IN ('84');
UPDATE kdv_extraktor.mapping_hzbart_hzbartgruppe
WHERE hzbart_knz IN ('86');
UPDATE kdv_extraktor.mapping_hzbart_hzbartgruppe
WHERE hzbart_knz IN ('81');
UPDATE kdv_extraktor.mapping_hzbart_hzbartgruppe
WHERE hzbart_knz IN ('83');
UPDATE kdv_extraktor.mapping_hzbart_hzbartgruppe
WHERE hzbart_knz IN ('85');
UPDATE kdv_extraktor.mapping_hzbart_hzbartgruppe
hzbart_knz IN ('99');
UPDATE kdv_extraktor.mapping_hzbart_hzbartgruppe
WHERE hzbart_knz IN ('87');
UPDATE kdv_extraktor.mapping_hzbart_hzbartgruppe
WHERE hzbart_knz IN ('98');
UPDATE kdv_extraktor.mapping_hzbart_hzbartgruppe
WHERE hzbart_knz IN ('30');
SET hzbtyp_knz = 'diplomU'
SET hzbtyp_knz = 'bachelorFH'
SET hzbtyp_knz = 'masterFH'
SET hzbtyp_knz = 'diplomFH'
SET hzbtyp_knz = 'o.A.' WHERE
SET hzbtyp_knz = 'allgHS'
SET hzbtyp_knz = 'Stb.o.HR'
SET hzbtyp_knz = 'Eig.Pr.MHS'
END IF;
RETURN 'SUCCESS';
END;
$$ LANGUAGE plpgsql;
SELECT kdv_extraktor.kdv_create_extraktor_hzbartmapping();
DROP FUNCTION IF EXISTS kdv_extraktor.kdv_create_extraktor_hzbartmapping();
Anschließend ersetzen sie das Extraktions-SQL mit folgendem SQL oder fügen Sie die Zeilen
die am Ende mit "neu" markiert sind, dazu:
SELECT '@@MANDANT' AS Mandant,
k_hzbart.hzbart AS HZBArt_KNZ,
TRIM(k_hzbart.ktxt) AS HZBArt_KURZBEZ,
TRIM(k_hzbart.ltxt) AS HZBArt_LANGBEZ,
k_hzbart.astat AS HZBArt_AMT_ID,
mapping.HZBArtGruppe_KNZ AS HZBArtGruppe_KNZ,
mapping.HZBSchulart_KNZ AS HZBSchulart_KNZ, -- neu
mapping.HZBTyp_KNZ AS HZBTyp_KNZ --neu
FROM k_hzbart
LEFT JOIN kdv_extraktor.mapping_hzbart_hzbartgruppe AS mapping
ON k_hzbart.hzbart=mapping.HZBArt_KNZ
HZBSchulart (Satzart) erzeugen
Um die HZBSchulart.csv generieren zu können muss ein neuer Extraktor angelget werden,
mit folgendem Extraktions-SQL und Preprocessing-SQL.
Extraktionss-SQL:
SELECT '@@MANDANT' AS Mandant,
hzbschulart_knz, hzbschulart_kurzbez, hzbschulart_langbez
FROM kdv_extraktor.custom_hzbschulart;
Preprocessing-SQL:
/*
* Tabelle für HZBArtMapping vorbereiten, wenn sie noch nicht existiert.
*/
CREATE OR REPLACE FUNCTION kdv_extraktor.kdv_create_extraktor_custom_hzbschulart()
RETURNS VARCHAR(256) AS $$
BEGIN
IF NOT EXISTS(SELECT TABLE_NAME FROM information_schema.TABLES WHERE
LOWER(TABLE_NAME) = LOWER('custom_hzbschulart')) THEN
CREATE TABLE IF NOT EXISTS kdv_extraktor.custom_hzbschulart
(hzbschulart_knz VARCHAR(10) NOT NULL,
hzbschulart_kurzbez VARCHAR(256),
hzbschulart_LANGBEZ VARCHAR(256), PRIMARY KEY (hzbschulart_KNZ));
INSERT INTO kdv_extraktor.custom_hzbschulart VALUES
('AUS', 'Ausland', 'HZB im Ausland'),
('BOS', 'BOS', 'Berufsoberschule'),
('BQU', 'BQU', 'Beruflich Qualifizierte'),
('FOS', 'FOS', 'Fachoberschule'),
('GYM', 'GYM', 'Gymnasium'),
('SON', 'Sonstige', 'Sonstige');
RETURN 'SUCCESS';
END IF;
RETURN 'NOTHING_DONE';
END;
$$ LANGUAGE plpgsql;
SELECT kdv_extraktor.kdv_create_extraktor_custom_hzbschulart();
DROP FUNCTION IF EXISTS kdv_extraktor.kdv_create_extraktor_custom_hzbschulart();
HZBTyp (Satzart) erzeugen
Um die HZBTyp.csv generieren zu können muss ein neuer Extraktor angelegt werden, mit
folgendem Extraktions-SQL und Preprocessing-SQL.
Extraktionss-SQL:
SELECT '@@MANDANT' AS Mandant,
hzbtyp_knz, hzbtyp_kurzbez, hzbtyp_langbez
FROM kdv_extraktor.custom_hzbtyp;
Preprocessing-SQL:
/*
* Tabelle für custom_hzbtyp vorbereiten, wenn sie noch nicht existiert.
*/
CREATE OR REPLACE FUNCTION kdv_extraktor.kdv_create_extraktor_custom_hzbtyp()
RETURNS VARCHAR(256) AS $$
BEGIN
IF NOT EXISTS(SELECT TABLE_NAME FROM information_schema.TABLES WHERE
LOWER(TABLE_NAME) = LOWER('custom_hzbtyp')) THEN
CREATE TABLE IF NOT EXISTS kdv_extraktor.custom_hzbtyp
(hzbtyp_knz VARCHAR(10) NOT NULL,
hzbtyp_kurzbez VARCHAR(256),
hzbtyp_LANGBEZ VARCHAR(256), PRIMARY KEY (hzbtyp_knz));
INSERT INTO kdv_extraktor.custom_hzbtyp VALUES
('Stb.o.HR', 'Stb. ohne Hochschulreife', 'Studienberechtigung ohne
Hochschulreife'),('o.A.', 'ohne Angaben', 'ohne Angaben'),('masterU', 'Master Uni',
'Master Uni'),
('masterFH', 'Master FH', 'Master FH'),('FHR o.A.', 'FHR ohne Angaben',
'Fachhochschulreife ohne Angaben'),('FHR Ausld', 'FHR Ausland', 'Fachhochschulreife
Ausland'),
('FHR', 'FHR', 'Fachhochschulreife'),('fgHR o.A.', 'fgHR ohne Angaben',
'fachgebundene Hochschulreife ohne Angaben'),('fgHR Ausld', 'fgHR Ausland',
'fachgebundene Hochschulreife Ausland'),
('fgHR', 'fgHR', 'fachgebundene Hochschulreife'),('Eig.Pr.MHS',
'Eignprfg.Kunst-u.MusikHS.', 'Eignungsprüfung Kunst- und
Musikhochschulen'),('diplomU', 'dipl. UNI', 'diplom UNI'),
('diplomFH', 'dipl FH', 'diplom FH'),('bachelorU', 'Bachelor UNI', 'Bachelor
UNI'),('bachelorFH', 'Bachelor FH', 'Bachelor FH'),
('allgHS', 'allg. Hochschulabschluss', 'allgemeiner Hochschulabschluss'),('aHR
o.A.', 'aHR ohne Angaben', 'allgemeine Hochschulreife ohne Angaben'),
('aHR Ausld', 'aHR Ausland', 'allgemeine Hochschulreife Ausland'),('aHR',
'aHR', 'allgemeine Hochschulreife');
RETURN 'SUCCESS';
END IF;
RETURN 'NOTHING_DONE';
END;
$$ LANGUAGE plpgsql;
SELECT kdv_extraktor.kdv_create_extraktor_custom_hzbtyp();
DROP FUNCTION IF EXISTS kdv_extraktor.kdv_create_extraktor_custom_hzbtyp();
allgemeine Änderungen
Unabhängig von den Neuerungen die HZB-Art usw. betreffen, könnte bei einer ungünstigen
Kombination von veralterten Extraktionsskripten ein Fehler auftreten. Bestimmte SQLBefehle werden dabei in falscher Reihenfolge ausgeführt. Falls bei Ihnen ein Fehler beim
extrahieren der Praktikum.csv auftritt, so laden Sie entweder die aktuellen Extraktionsskripte
vom Server oder bauen Sie die Skripte manuell um. Dabei muss der folgende SQL-Block aus
der Prüfung.csv (Preprocessing)entnommen werden und in die Praktikum.csv
(Preprocessing)eingefügt werden:
/*
* aus Student.csv hierher verschoben, da sich die Sortierreihenfolge der Jobs
geändert hat
* siehe Bugzilla Ticket 671
* Aufbau einer Funktion, die initial eine Tabelle zur Verwaltung der
verschlüsselten
* Matrikelnummern generiert und dann bei weiteren Vorgängen immer um die neu
hinzugekommenen
* erweitert.
*/
CREATE OR REPLACE FUNCTION kdv_extraktor.kdv_prefetch_mtknr() RETURNS void AS
$$
BEGIN
DROP TABLE IF EXISTS kdv_extraktor.studenten_enc_mtknr_prefetch;
CREATE TABLE kdv_extraktor.studenten_enc_mtknr_prefetch
(
mtknr INTEGER,
mtknr_md5 text,
nachname text,
vorname text,
gebdat DATE,
gebort CHARACTER(25),
geschl CHARACTER(1),
semester SMALLINT
)
WITH (
OIDS=FALSE
);
IF NOT EXISTS( SELECT * FROM information_schema.TABLES
WHERE table_schema = 'kdv_extraktor' AND TABLE_NAME = 'studenten_enc_mtknr' )
THEN
/*
* Initiale Generierung der Tabellen studenten_enc_mtknr_prefetch und
studenten_enc_mtknr
*/
INSERT INTO kdv_extraktor.studenten_enc_mtknr_prefetch
SELECT mtknr,
md5(TRIM(to_char(mtknr, '999999999')) ||
TRIM(to_char(trunc(random()*100000), '9999999'))) AS mtknr_md5,
TRIM(nachname) AS nachname,
TRIM(vorname) AS vorname,
gebdat,
gebort,
geschl,
semester -- Semester des SOS-Satzes
FROM sos;
/*
* Index generieren um Lesezugriff zu beschleunigen
*/
CREATE INDEX studenten_enc_mtknr_prefetch_idx
ON kdv_extraktor.studenten_enc_mtknr_prefetch (nachname, vorname, gebdat,
gebort, geschl);
CREATE INDEX studenten_enc_mtknr_prefetch_idx2
ON kdv_extraktor.studenten_enc_mtknr_prefetch (nachname, vorname, gebdat,
gebort, mtknr, geschl);
CREATE INDEX studenten_enc_mtknr_prefetch_idx3
ON kdv_extraktor.studenten_enc_mtknr_prefetch (mtknr);
ELSE
/*
* Fall Tabelle existiert bereits:
* Befüllen der Tabelle studenten_enc_mtknr mit fehlenden Werten
*/
/*
* Befüllen der Tabelle studenten_enc_mtknr_prefetch
*/
INSERT INTO kdv_extraktor.studenten_enc_mtknr_prefetch
SELECT mtknr,
md5(TRIM(to_char(mtknr, '999999999')) ||
TRIM(to_char(trunc(random()*100000), '9999999'))) AS mtknr_md5,
TRIM(nachname) AS nachname,
TRIM(vorname) AS vorname,
gebdat,
gebort,
geschl,
semester -- imma semester
FROM sos;
-- Bereits verwendete Matrikelnummern rauslöschen
DELETE FROM kdv_extraktor.studenten_enc_mtknr_prefetch p
WHERE p.mtknr IN (SELECT mtknr FROM kdv_extraktor.studenten_enc_mtknr WHERE
mtknr = p.mtknr) OR p.mtknr IS NULL;
-- Rest mit den bereits verwendeten Matrikelnummern und deren verschlüsselten
Werten auffüllen
INSERT INTO kdv_extraktor.studenten_enc_mtknr_prefetch
SELECT mtknr, mtknr_md5, nachname, vorname, gebdat, gebort, geschl, semester2
AS semester
FROM kdv_extraktor.studenten_enc_mtknr;
END IF;
END;
$$
LANGUAGE 'plpgsql';
/*
* Ausführen der Funktion kdv_update_enc_mtknr()
*/
SELECT kdv_extraktor.kdv_prefetch_mtknr();
-- Löschen, denn die Inhalte sind jetzt ja in studenten_enc_mtknr_prefetch
gesichert ???
DROP TABLE IF EXISTS kdv_extraktor.studenten_enc_mtknr;
CREATE TABLE kdv_extraktor.studenten_enc_mtknr
(
mtknr INTEGER,
mtknr_md5 text,
mtknr_md5_short text,
nachname VARCHAR(256),
vorname VARCHAR(256),
gebdat DATE,
gebort CHARACTER(25),
geschl CHARACTER(1),
semester1 SMALLINT,
semester2 SMALLINT
)
WITH (
OIDS=FALSE
);
CREATE INDEX studenten_enc_mtknr_idx
ON kdv_extraktor.studenten_enc_mtknr (mtknr);
/*
* Eigentliche Mapping-Tabelle studenten_enc_mtknr generieren
*/
CREATE OR REPLACE FUNCTION kdv_extraktor.kdv_update_enc_mtknr() RETURNS void AS
$$
BEGIN
IF '1' = '@@MAP_STUDENTS' THEN
-- Kopieren der Daten aus Prefetch (mit Matrikelnummer-Mapping)
WITH cte_studenten_min_mtknr (nachname, vorname, gebdat, gebort, geschl, mtknr)
AS
(
SELECT nachname, vorname, gebdat, gebort, geschl, MIN(mtknr) AS minsemester
FROM kdv_extraktor.studenten_enc_mtknr_prefetch
GROUP BY nachname, vorname, gebdat, gebort, geschl
)
INSERT INTO kdv_extraktor.studenten_enc_mtknr
SELECT b.mtknr, a.mtknr_md5, substr(a.mtknr_md5, 0, 15) AS mtknr_md5_short,
b.nachname, b.vorname, b.gebdat, b.gebort, b.geschl, a.semester AS semester1,
b.semester AS semester2
FROM kdv_extraktor.studenten_enc_mtknr_prefetch a
INNER JOIN cte_studenten_min_mtknr cmin
ON a.nachname = cmin.nachname
AND a.vorname = cmin.vorname
AND a.gebdat = cmin.gebdat
AND a.gebort = cmin.gebort
AND a.mtknr = cmin.mtknr
AND a.geschl = cmin.geschl
INNER JOIN kdv_extraktor.studenten_enc_mtknr_prefetch b
ON a.nachname = b.nachname
AND a.vorname = b.vorname
AND a.gebdat = b.gebdat
AND a.gebort = b.gebort
AND a.geschl = b.geschl
ORDER BY nachname, vorname, gebdat, gebort, geschl;
ELSE
-- Kopieren der Daten aus Prefetch (ohne Matrikelnummer-Mapping)
INSERT INTO kdv_extraktor.studenten_enc_mtknr
SELECT mtknr, mtknr_md5, substr(mtknr_md5, 0, 15) AS mtknr_md5_short,
nachname, vorname, gebdat, gebort, geschl, semester AS semester1, semester AS
semester2
FROM kdv_extraktor.studenten_enc_mtknr_prefetch;
END IF;
END;
$$
LANGUAGE 'plpgsql';
SELECT kdv_extraktor.kdv_update_enc_mtknr();