Category=Database Maintenance
Name=02.2. Migrate- Document
Description=Grabs data from current VMR tables and imports it into the data model

OutputLevel=3

Heading=Import Document table data from HSSListe table data

# Dubious documents have had their GA number pushed into Olim in the old
# system.  This converts to DOCUMENT.FORMERGANUMBER
# this allows us to search for DUBIOUS and FORMERGANUMBER to find defunct
# GA NUMBERS
ImportSQL=SELECT 									\
ObjID, 												\
min(InstID), 										\
GA, 												\
Jh, 												\
COALESCE(Datiert_1, 0),											\
COALESCE(Datiert_2, 0),											\
Blattzahl,											\
CAST(COALESCE(Blattzahl_Int, 'null') AS CHAR),										\
Dub,												\
Olim,												\
CAST(COALESCE( Inhalt2, Inhalt ) AS CHAR) CONTENTOVERVIEW, 		\
Sprache,											\
CAST((case WHEN Pal = 'unten' THEN 'true' WHEN Pal is null THEN 'null' WHEN Pal = 'oben' THEN 'false' ELSE 'null' END) AS CHAR) PALIMPSETUNDER,	\
PalB,												\
(case (`Textträger`) WHEN 1 THEN true ELSE false END) FORMATSCROLL,									\
CAST((case WHEN INSTR(Spalten, '.') > 0 THEN CAST(SUBSTR(Spalten, 1, INSTR(Spalten, '.')-1) as SIGNED)	\
	  WHEN INSTR(Spalten, '-') > 0 THEN CAST(SUBSTR(Spalten, 1, INSTR(Spalten, '-')-1) as SIGNED)	\
	  WHEN INSTR(Spalten, '/') > 0 THEN CAST(SUBSTR(Spalten, 1, INSTR(Spalten, '/')-1) as SIGNED)	\
	  ELSE CAST(Spalten as SIGNED) END) AS CHAR) COLUMNS,	\
CAST((case WHEN INSTR(Spalten, '.') > 0 THEN CAST(SUBSTR(Spalten, INSTR(Spalten, '.')+1, LENGTH(Spalten)-INSTR(Spalten, '.')) as SIGNED)				\
	  WHEN INSTR(Spalten, '-') > 0 THEN CAST(SUBSTR(Spalten, INSTR(Spalten, '-')+1, LENGTH(Spalten)-INSTR(Spalten, '-')) as SIGNED)				\
	  WHEN INSTR(Spalten, '/') > 0 THEN CAST(SUBSTR(Spalten, INSTR(Spalten, '/')+1, LENGTH(Spalten)-INSTR(Spalten, '/')) as SIGNED)				\
	  ELSE 'null' END) AS CHAR) COLUMNSMAX,							\
Zeilen LINECOUNTDESCRIPTION,							\
CAST((case WHEN INSTR(COALESCE(`Zeilen_R`, `Zeilen`), _utf8'.' COLLATE utf8_general_ci) > 0 THEN CAST(SUBSTR(COALESCE(`Zeilen_R`, `Zeilen`), 1, INSTR(COALESCE(`Zeilen_R`, `Zeilen`), _utf8'.' COLLATE utf8_general_ci)-1) as SIGNED)				\
	  WHEN INSTR(COALESCE(`Zeilen_R`, `Zeilen`), _utf8'-' COLLATE utf8_general_ci) > 0 THEN CAST(SUBSTR(COALESCE(`Zeilen_R`, `Zeilen`), 1, INSTR(COALESCE(`Zeilen_R`, `Zeilen`), _utf8'-' COLLATE utf8_general_ci)-1) as SIGNED)				\
	  WHEN INSTR(COALESCE(`Zeilen_R`, `Zeilen`), _utf8'/' COLLATE utf8_general_ci) > 0 THEN CAST(SUBSTR(COALESCE(`Zeilen_R`, `Zeilen`), 1, INSTR(COALESCE(`Zeilen_R`, `Zeilen`), _utf8'/' COLLATE utf8_general_ci)-1) as SIGNED)				\
	  WHEN INSTR(COALESCE(`Zeilen_R`, `Zeilen`), _utf8'+' COLLATE utf8_general_ci) > 0 THEN CAST(SUBSTR(COALESCE(`Zeilen_R`, `Zeilen`), 1, INSTR(COALESCE(`Zeilen_R`, `Zeilen`), _utf8'+' COLLATE utf8_general_ci)-1) as SIGNED)				\
	  ELSE COALESCE(CAST(COALESCE(`Zeilen_R`, `Zeilen`) as SIGNED), 'null') END) AS CHAR) LINECOUNT,		\
\
\
CAST((case WHEN INSTR(COALESCE(`Zeilen_R`, `Zeilen`), _utf8'.' COLLATE utf8_general_ci) > 0 THEN CAST(SUBSTR(COALESCE(`Zeilen_R`, `Zeilen`), INSTR(COALESCE(`Zeilen_R`, `Zeilen`), _utf8'.' COLLATE utf8_general_ci)+1, LENGTH(COALESCE(`Zeilen_R`, `Zeilen`))-INSTR(COALESCE(`Zeilen_R`, `Zeilen`), _utf8'.' COLLATE utf8_general_ci)) as SIGNED)				\
	  WHEN INSTR(COALESCE(`Zeilen_R`, `Zeilen`), _utf8'-' COLLATE utf8_general_ci) > 0 THEN CAST(SUBSTR(COALESCE(`Zeilen_R`, `Zeilen`), INSTR(COALESCE(`Zeilen_R`, `Zeilen`), _utf8'-' COLLATE utf8_general_ci)+1, LENGTH(COALESCE(`Zeilen_R`, `Zeilen`))-INSTR(COALESCE(`Zeilen_R`, `Zeilen`), _utf8'-' COLLATE utf8_general_ci)) as SIGNED)				\
	  WHEN INSTR(COALESCE(`Zeilen_R`, `Zeilen`), _utf8'/' COLLATE utf8_general_ci) > 0 THEN CAST(SUBSTR(COALESCE(`Zeilen_R`, `Zeilen`), INSTR(COALESCE(`Zeilen_R`, `Zeilen`), _utf8'/' COLLATE utf8_general_ci)+1, LENGTH(COALESCE(`Zeilen_R`, `Zeilen`))-INSTR(COALESCE(`Zeilen_R`, `Zeilen`), _utf8'/' COLLATE utf8_general_ci)) as SIGNED)				\
	  WHEN INSTR(COALESCE(`Zeilen_R`, `Zeilen`), _utf8'+' COLLATE utf8_general_ci) > 0 THEN CAST(SUBSTR(COALESCE(`Zeilen_R`, `Zeilen`), 1, INSTR(COALESCE(`Zeilen_R`, `Zeilen`), _utf8'+' COLLATE utf8_general_ci)-1) as SIGNED)+3				\
	  ELSE 'null' END) AS CHAR) LINECOUNTMAX,							\
`Höhe` HEIGHTDESCRIPTION,							\
CAST((case WHEN INSTR(COALESCE(`Höhe_R`, `Höhe`), _utf8'.' COLLATE utf8_general_ci) > 0 THEN CAST(SUBSTR(COALESCE(`Höhe_R`, `Höhe`), 1, INSTR(COALESCE(`Höhe_R`, `Höhe`), _utf8'.' COLLATE utf8_general_ci)-1) as SIGNED)*10				\
	  WHEN INSTR(COALESCE(`Höhe_R`, `Höhe`), _utf8'-' COLLATE utf8_general_ci) > 0 THEN CAST(SUBSTR(COALESCE(`Höhe_R`, `Höhe`), 1, INSTR(COALESCE(`Höhe_R`, `Höhe`), _utf8'-' COLLATE utf8_general_ci)-1) as SIGNED)*10				\
	  WHEN INSTR(COALESCE(`Höhe_R`, `Höhe`), _utf8'/' COLLATE utf8_general_ci) > 0 THEN CAST(SUBSTR(COALESCE(`Höhe_R`, `Höhe`), 1, INSTR(COALESCE(`Höhe_R`, `Höhe`), _utf8'/' COLLATE utf8_general_ci)-1) as SIGNED)*10				\
	  WHEN INSTR(COALESCE(`Höhe_R`, `Höhe`), _utf8',' COLLATE utf8_general_ci) > 0 THEN CAST(REPLACE(COALESCE(`Höhe_R`, `Höhe`), _utf8',' COLLATE utf8_general_ci, _utf8'' COLLATE utf8_general_ci) as SIGNED)				\
	  ELSE COALESCE(CAST(COALESCE(`Höhe_R`, `Höhe`) as SIGNED)*10, 'null') END) AS CHAR) HEIGHT,		\
\
\
CAST((case WHEN INSTR(COALESCE(`Höhe_R`, `Höhe`), _utf8'.' COLLATE utf8_general_ci) > 0 THEN CAST(SUBSTR(COALESCE(`Höhe_R`, `Höhe`), INSTR(COALESCE(`Höhe_R`, `Höhe`), _utf8'.' COLLATE utf8_general_ci)+1, LENGTH(COALESCE(`Höhe_R`, `Höhe`))-INSTR(COALESCE(`Höhe_R`, `Höhe`), _utf8'.' COLLATE utf8_general_ci)) as SIGNED)*10				\
	  WHEN INSTR(COALESCE(`Höhe_R`, `Höhe`), _utf8'-' COLLATE utf8_general_ci) > 0 THEN CAST(SUBSTR(COALESCE(`Höhe_R`, `Höhe`), INSTR(COALESCE(`Höhe_R`, `Höhe`), _utf8'-' COLLATE utf8_general_ci)+1, LENGTH(COALESCE(`Höhe_R`, `Höhe`))-INSTR(COALESCE(`Höhe_R`, `Höhe`), _utf8'-' COLLATE utf8_general_ci)) as SIGNED)*10				\
	  WHEN INSTR(COALESCE(`Höhe_R`, `Höhe`), _utf8'/' COLLATE utf8_general_ci) > 0 THEN CAST(SUBSTR(COALESCE(`Höhe_R`, `Höhe`), INSTR(COALESCE(`Höhe_R`, `Höhe`), _utf8'/' COLLATE utf8_general_ci)+1, LENGTH(COALESCE(`Höhe_R`, `Höhe`))-INSTR(COALESCE(`Höhe_R`, `Höhe`), _utf8'/' COLLATE utf8_general_ci)) as SIGNED)*10				\
	  ELSE 'null' END) AS CHAR) HEIGHTMAX,							\
\
\
Breite WIDTHDESCRIPTION,							\
CAST((case WHEN INSTR(COALESCE(`Breite_R`, `Breite`), _utf8'.' COLLATE utf8_general_ci) > 0 THEN CAST(SUBSTR(COALESCE(`Breite_R`, `Breite`), 1, INSTR(COALESCE(`Breite_R`, `Breite`), _utf8'.' COLLATE utf8_general_ci)-1) as SIGNED)*10				\
	  WHEN INSTR(COALESCE(`Breite_R`, `Breite`), _utf8'-' COLLATE utf8_general_ci) > 0 THEN CAST(SUBSTR(COALESCE(`Breite_R`, `Breite`), 1, INSTR(COALESCE(`Breite_R`, `Breite`), _utf8'-' COLLATE utf8_general_ci)-1) as SIGNED)*10				\
	  WHEN INSTR(COALESCE(`Breite_R`, `Breite`), _utf8'/' COLLATE utf8_general_ci) > 0 THEN CAST(SUBSTR(COALESCE(`Breite_R`, `Breite`), 1, INSTR(COALESCE(`Breite_R`, `Breite`), _utf8'/' COLLATE utf8_general_ci)-1) as SIGNED)*10				\
	  WHEN INSTR(COALESCE(`Breite_R`, `Breite`), _utf8',' COLLATE utf8_general_ci) > 0 THEN CAST(REPLACE(COALESCE(`Breite_R`, `Breite`), _utf8',' COLLATE utf8_general_ci, _utf8'' COLLATE utf8_general_ci) as SIGNED)				\
	  ELSE COALESCE(CAST(COALESCE(`Breite_R`, `Breite`) as SIGNED)*10, 'null') END) AS CHAR) WIDTH,	\
CAST((case WHEN INSTR(COALESCE(`Breite_R`, `Breite`), _utf8'.' COLLATE utf8_general_ci) > 0 THEN CAST(SUBSTR(COALESCE(`Breite_R`, `Breite`), INSTR(COALESCE(`Breite_R`, `Breite`), _utf8'.' COLLATE utf8_general_ci)+1, LENGTH(COALESCE(`Breite_R`, `Breite`))-INSTR(COALESCE(`Breite_R`, `Breite`), _utf8'.' COLLATE utf8_general_ci)) as SIGNED)*10				\
	  WHEN INSTR(COALESCE(`Breite_R`, `Breite`), _utf8'-' COLLATE utf8_general_ci) > 0 THEN CAST(SUBSTR(COALESCE(`Breite_R`, `Breite`), INSTR(COALESCE(`Breite_R`, `Breite`), _utf8'-' COLLATE utf8_general_ci)+1, LENGTH(COALESCE(`Breite_R`, `Breite`))-INSTR(COALESCE(`Breite_R`, `Breite`), _utf8'-' COLLATE utf8_general_ci)) as SIGNED)*10				\
	  WHEN INSTR(COALESCE(`Breite_R`, `Breite`), _utf8'/' COLLATE utf8_general_ci) > 0 THEN CAST(SUBSTR(COALESCE(`Breite_R`, `Breite`), INSTR(COALESCE(`Breite_R`, `Breite`), _utf8'/' COLLATE utf8_general_ci)+1, LENGTH(COALESCE(`Breite_R`, `Breite`))-INSTR(COALESCE(`Breite_R`, `Breite`), _utf8'/' COLLATE utf8_general_ci)) as SIGNED)*10				\
	  ELSE 'null' END) AS CHAR) WIDTHMAX,							\
Anmerkung LISTEFOOTNOTES,							\
Bemerkung COMMENTS,									\
(CASE WHEN Zer is null THEN false WHEN Zer=1 THEN true ELSE false END) DESTROYED,		\
COALESCE(GA_post, 'null') 												\
	\
 FROM HSSListe group by ObjID order by ObjID, InstID|									\
 INSERT INTO DOCUMENT (								\
 	\
 DOCUMENTID, 										\
 GANUMBER, 											\
 ORIGINYEARDESCRIPTION,								\
 ORIGINYEAREARLY,									\
 ORIGINYEARLATE,									\
 LEAVESDESCRIPTION,									\
 LEAVES,											\
 DUBIOUS,											\
 FORMERGANUMBER,									\
 CONTENTOVERVIEW,									\
 LANGUAGE,											\
 PALEMPSESTUNDER,									\
 PALEMPSESTOTHER,									\
 FORMATSCROLL,										\
 COLUMNS,											\
 COLUMNSMAX,										\
 LINECOUNTDESCRIPTION,								\
 LINECOUNT,											\
 LINECOUNTMAX,										\
 HEIGHTDESCRIPTION,									\
 HEIGHT,											\
 HEIGHTMAX,											\
 WIDTHDESCRIPTION,									\
 WIDTH,												\
 WIDTHMAX,											\
 KLFOOTNOTES,										\
 COMMENTS,											\
 DESTROYED,											\
 GAPOST 											\
 ) VALUES ( {0},  '{2}', '{3}', {4}, {5},		\
		   '{6}', {7}, '{8}', '{9}','{10}',		\
		  '{11}',{12},'{13}','{14}',{15},		\
		  {16},'{17}',{18},{19},'{20}',		\
		  {21},{22},'{23}',{24},{25},		\
		  '{26}','{27}', '{28}', '{29}');