#
#				ITSV GmbH
#	CCDB - Command and Control Database
#
#	FILE:				dquerymfile_SAVESELECTBIGFILE.txt
#	DESCRIPTION:	DQUERY definition for DQUERY SAVESELECTBIGFILE
#					saves a big result from a SELECT into an external file using the databases "save-to-file" mechanism
#
#	ATTENTION: MYSQL must be configured for allowing SELECT ... INTO OUTFILE
#				see: https://computingforgeeks.com/how-to-solve-mysql-server-is-running-with-the-secure-file-priv-error/
#
#
@querytitle				SQL-Select direkt in Datei exportieren 
@querydescription		exportiert das Ergebnis eines SQL-SELECT direkt mit Mitteln der Datenbank in ein managed file in CSV-Format
@group					DATAFILE
@attributenames mftype:mfiletype:{{mftypeoptions}},mfilename:string:{{mfilenameoptions}},mfilelpath:mfilelpath:{{mfilelpathoptions}},mfileattribs:string:{{mfileattribsoptions}},cfgname:dbconfigname:{{cfgnameoptions}},select:string:{{selectoptions}},tabname:string:{{tabnameoptions}},impl:string:{{imploptions}},linesep:linesep:{{linesepoptions}},fieldsep:string:{{fieldsepoptions}},headfirstline:yesno:{{headfirstlineoptions}}
@mftypeoptions {
	labeltext:		"Managed File Type",
	typedesc:		"Type des Managed File, in das das Ergebnis des SELECT exportiert werden soll"	}
@mfilenameoptions {
	labeltext:		"Dateiname",
	typedesc:		"Original-Name der Datei, wenn sie downloaded wird"
	}
@mfilelpathoptions {
	labeltext:		"Logischer Pfad",
	typedesc:		"Logischer managed File Pfad, an dem die Datei angelegt werden soll" }
@mfileattribsoptions {
	labeltext:		"Attribute",
	typedesc:		"JSON-String mit Attributen, die zum Managed File gespeichert werden sollen",
	is_optional:	true }
@cfgnameoptions	{ 
	labeltext:			"Datenbank",
	typedesc:			"Name der Datenbank-Konfiguration, in der das SELECT ausgeführt bzw. die Tabelle gelesen werden soll",
	is_optional:		true }
@selectoptions { 
	labeltext:		"Select-Statement",
	typedesc:		"SQL-SELECT-Statement, dass in der Datenbank ausgeführt werden soll. Wird hier nichts angegeben, so muss eine Tabelle angegeben werden",
	is_optional:	true }
@tabnameoptions {
	labeltext:		"Tabelle",
	typedesc:		"Name der Tabelle, die in die Datei exportiert werden soll. Diese Angabe wird nur berücksichtigt, wenn kein Select-Statement angegeben wird.",
	is_optional:	true }
@imploptions {
	type:		{	structure:	"scalar.enum",
					vlist:	"DEFAULT:Vorgabe:benutze die Vorgabe (saveoutfile),"+
							"SAVEOUT:SaveToOutFile:Verwende SQL-Kommando SELECT INTO OUTFILE,"+
							"CSVTAB:CSVTableEngine:Verwende CSV-Engine-Tabelle"
				},
	labeltext:		"Implementierung",
	typedesc:		"Wählt die Art aus, mit der die CCDB die Ausgabedatei erzeugt",
	is_optional:	true	}
@linesepoptions	{
	type:		{	structure: "scalar.enum", 
					vlist:	"DEFAULT:Vorgabe:benutze die Vorgabe (CRLF),"+
							"CR:CarriageReturn:Carriage Return,"+
							"LF:LineFeed:Line Feed,"+
							"CRLF:CR+LF:CarriageReturn dann Line Feed,"+
							"LFCR:LF+CR:Line Feed dann Carriage Return" 
				},
	labeltext:	"Zeilentrenner",
	typedesc:	"Zeichen(folge), durch die Zeilen (Datensätze) in der Datei getrennt sein sollen",
	is_optional: true }
@fieldsepoptions		{ 
	type:		{	structure:	"scalar.enum",
					vlist:	"DEFAULT:Vorgabe:benutze die Vorgabe - Semikolon/Strichpunkt (;),"+
							"SEMICOLON:Semikolon:Strichpunkt (;),"+
							"COMMA:Komma:Beistrich,"+
							"TAB:Tabulator:Tabulator-Zeichen (Zeichencode 09),"+
							"COLON:Doppelpunkt:Doppelpunkt(:),"
				},
	labeltext:	"Feldtrennzeichen",
	typetext:	"Zeichen(folge), mit dem(der) Felder untereinander getrennt sein sollen",
	is_optional: true
	}
@headfirstlineoptions {
	labeltext:		"Kopf in erster Zeile",
	typetext:		"gibt an, ob eine Kopfzeile mit Spaltenkopfnamen erstellt werden soll",
	is_optional:	true,
	default:		true }
@querytype				function
@function				seqtrans.seqtrans

~query.tsteps

#
# <<TSTEPNUM:0>>: prepare parameters
#
qexpression
  proc: {
	this.implementation = "saveoutfile";
	if (this.query.impl) {
		switch (this.query.impl) {
			case "DEFAULT":		this.implementation = "saveoutfile";		break;
			case "SAVEOUT":		this.implementation = "saveoutfile";		break;
			case "CSVTAB":		this.implementation = "csvtable";			break;
			default:			this.implementation = "saveoutfile";		break;
		}
	} else {
		this.implementation = "saveoutfile";
	}
	if (this.query.mfiletype) {
		this.mfiletype = this.query.mfiletype;
	} else {
		this.mfiletype = "gencsv";
	}
	if (this.query.mfilename) {
		this.mfilename = this.query.mfilename;
	} else {
		this.mfilename = mfiletype + "_" + aux.syncUniqueNowId() + ".csv";
	}
	if (this.query.mfilelpath) {
		this.mfilelpath = this.query.mfilelpath;
	} else {
		this.mfilelpath = "/";
	}
	if (this.query.hasOwnProperty("mfileattribs")) {
		this.mfileattribs = JSON.parse(this.query.mfileattribs);
	} else {
		this.mfileattribs = {};
	}
	if (this.query.fieldsep) {
		this.fieldsep = ";";
		switch (this.query.fieldsep) {
			case "DEFAULT":		this.fieldsep = ";";	break;		/* default is semicolon */
			case "SEMICOLON":	this.fieldsep = ";";	break;
			case "COLON":		this.fieldsep = ":";	break;
			case "TAB":			this.fieldsep = "\t";	break;
			case "COMMA":		this.fieldsep = ",";	break;
			default:			this.errcoll.collect(null,"Illegal token \""+this.query.fieldsep+"\" for field separator",this.query);
		}
	} else {
		this.fieldsep = ";";
	}
	if (this.query.linesep) {
		this.linesep = "\\r\\n";
		switch (this.query.linesep) {
			case "DEFAULT":	this.linesep = "\\r\\n";	break;		/* default is CRLF */
			case "CR":		this.linesep = "\\r"; 		break;
			case "LF":		this.linesep = "\\n"; 		break;
			case "CRLF":	this.linesep = "\\r\\n";	break;
			case "LFCR":	this.linesep = "\\n\\r";	break;
			default:		this.errcoll.collect(null,"Illegal token \""+this.query.linesep+"\" for line separator",this.query);
		}
	} else {
		this.linesep = "\\r\\n";
	}
	if (this.query.cfgname) {
		this.cfgname = this.query.cfgname;
	} else {
		this.cfgname = db.getCurrentConfig().getName();
	}
	this.dbconfig = db.getConfigByName(this.cfgname);
	if (this.query.select) {
		this.select = this.query.select;
	} else if (this.query.tabname) {
		this.tabname = this.query.tabname;
		this.select = "SELECT * FROM "+this.tabname;
	} else {
		this.errcoll.collect(null,"Either select or tabname must be given",this.query);
		break proc;
	}
	this.mfileattribs.SELECT = this.select;
	this.headfirstline = aux.getBoolOption(this.query,"headfirstline",true);
  }

#
# <<TSTEPNUM:1>>: get mysql base path and path to command line client executable
#
sql					select @@basedir
result_varname		sqlres
post_qexpression
	this.mysql_basedir = this.sqlres.getCellValue(0,0);
	this.mysql_bindir = this.mysql_basedir + "\\bin";
	this.mysql_clientexe = this.mysql_bindir + "\\mysql.exe";

#
# <<TSTEPNUM:2>>: create the MFILE entry and create proper file path
#
aexpression
	mfile.saveToMfile(	null,				/* no session needed */
						this.mfiletype,
						null,				/* no file data as we don't write here */
						{	donotwrite: 	true,
							mfileattribs:	JSON.stringify(this.mfileattribs),
							filename:		this.mfilename,
							mfilelpath:		this.mfilelpath
						},
						function(err,res) {
							logger.debug(this.phead("MFILECREATED")+".ERR: "+aux.objTxt(err));
							if (err) {
								this.errcoll.collect(err,"Error in saveToMfile/creating managed file entry and path",res);
								this();
								return;
							}
							if (res.resulttype!='object') {
								this.errcoll.collect(null,"Illegal result type \""+res.resulttype+"\" from saveToMfile",res);
								this();
								return;
							}
							logger.debug(this.phead("MFILECREATED")+".RES: "+aux.objTxt(res.resultobject));
							this.mfilepath = res.resultobject.mfilepath;
							this.mfilesqlpath = this.mfilepath.replace(/\\/g,"\\\\");
							this.mfilewinpath = this.mfilepath.replace(/\//g,"\\");
							this.mfileid = res.resultobject.mfileid;
							this.ppush([this.phead("MFILECREATED"),"mfileid="+this.mfileid,"",""]);
							this();
						}.bind(this)
					);

#
# <<TSTEPNUM:3>>: make a single row select to obtain column names and build a string list from them
#
pre_qexpression
	if (this.select.startsWith("SELECT ") || this.select.startsWith("select ")) {
		this.select = this.select.substr(7);
	}
	if (this.select.indexOf(" limit ")>=0) {
		/* in this case the select already contains a "limit" clause, 
		 * replace it temporarily with "limit 1,1"
		 */
		this.hselect = this.select.replace(/\s*limit\s*[0-9,]+/," limit 1,1")
		logger.debug(this.phead("UDBG.HSELECTLIMITREPLACE")+"HEADERSELECT=\""+this.hselect+"\"");
	} else {
		/* the select does not contain a "limit" clause, simply temporarily append a "limit 1,1" 
		 */
		this.hselect = this.select + " limit 1,1"
	}
sql				select @@hselect@@
result_varname	hres
post_qexpression
	let cnames = new Array();
	for (let ci=0; ci<this.hres.metaData.length; ci++) {
		cnames.push(this.hres.metaData[ci].name);
	}
	this.headerlineclause = "'"+cnames.join("','")+"'";

#
# <<TSTEPNUM:4>>: branch to specific implementation
#
pre_qexpression
	proc: {
		this.implab = "imp_"+this.implementation;
		if (!this.labels[this.implab]) {
			this.errcoll.collect(null,"Cannot execute Implementation \""+this.implementation+"\" of CCDB-Query \""+this.query.dataname+"\", seqtrans-label \""+this.implab+"\" does not exist");
			break proc;
		}
	}
goto	@@implab@@

#
# ------------------------------------------------------------------------------
#
# <<TSTEPNUM:5>>: 	implementation "saveoutfile"
#					create SQL command file and invocation windows console command
#
imp_saveoutfile:
qexpression
	/* variable read_buffer_size limits maximum data read by SELECT INTO OUTFILE: 
	 * see also: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_select_into_disk_sync 
	 * so we have to crank it up
	 */
	this.setbuffercommand = "SET GLOBAL read_buffer_size = 2000000000;";
	let fsepclause = " FIELDS TERMINATED BY '"+this.fieldsep+"' ";
	let quotclause = " OPTIONALLY ENCLOSED BY '\"' ";
	if (this.headfirstline) {
		/* when headers in first line do 2 selects in a UNION ALL, the first selecting only literal strings
		 * and the second the real data. The second is enclosed in parantheses to correctly apply an optional "limit" clause only to the second select
		 */
		this.dataselect = "SELECT "+this.headerlineclause+" UNION ALL (SELECT "+this.select+")";
	} else {
		this.dataselect = "SELECT "+this.select;
	}
	let lsepclause = " LINES TERMINATED BY '"+this.linesep+"' ";
	this.selectsqlcmd = this.dataselect + " INTO OUTFILE '"+this.mfilesqlpath+"' "+
						" "+fsepclause+
						" "+quotclause+
						" "+lsepclause;
	this.sqlfilename = aux.makeUniqueTempFileName() + "_SAVESELECTBIGFILE.sql";
	fs.writeFileSync(this.sqlfilename,this.setbuffercommand+"\r\n"+this.selectsqlcmd);
	this.batcmd = this.mysql_clientexe + " -u"+this.dbconfig.getUser()+" -p"+this.dbconfig.getPassword()+" -Dccdb --local-infile=1 <"+this.sqlfilename;
	this.ppush([this.phead("CMDGENERATED"),"SQLFILE="+this.sqlfilename,"",""]);

#
# <<TSTEPNUM:6>>: execute select command for windows by invoking Shell command in a child process
#
aexpression
	childprocess.exec(this.batcmd, { timeout: 30000000 }, 
					function(err,stdout,stderr) {
						logger.debug(this.phead("EXEC_CALLBACK"));
						if (err) {
							this.errcoll.collect(err,"Error in select/export to MFILEID=\""+this.query.mfid+"\", executing \""+this.batcmd+"\"", { stdout: stdout, stderr: stderr });
							this();
							return;
						}
						this.ppush([	this.phead("cmdlineexecuted"),
										{ rawhtml: "Managed File <a href=\"/dquery?dataname=SHOWFILE&mfileid="+this.mfileid+"\">"+this.mfileid+"</a> generated"},
										{ rawhtml: "STDOUT:<br/><pre>"+stdout+"</pre>" },
										{ rawhtml: "STDERR:<br/><pre>"+stderr+"</pre>" }
									]);
						this();
					}.bind(this)
					);

#
# <<TSTEPNUM:7>>: end of implementation "saveoutfile"
#
end
					
#
# ------------------------------------------------------------------------------
#
#	<<TSTEPNUM:8>>: implementation "csvtable"
#		this implementation creates a table with the output data handled by MySQL engine "CSV"
#		and then moves the thereby created csv file to the location of the managed file
#
imp_csvtable:
sql					show global variables like 'datadir'
result_varname		ddres
post_qexpression
	proc: {
		if (this.ddres.getRowCount()!=1) {
			this.errcoll.collect(null,"Illegal row count when getting datadir location",this.ddres);
			break proc;
		}
		this.datadir = this.ddres.getCellValue(0,1).replace(/\//g,"\\");
	}

#
# <<TSTEPNUM:9>>: generate SQL and DOS-CMD commands
#
qexpression
	this.ttabname = "SSTT_" + aux.syncUniqueNowID() + "_CSV";
	this.cretabclause = "CREATE TABLE " + this.ttabname + " ENGINE=csv ";	
	if (this.headfirstline) {
		this.dataselect = "SELECT "+this.headerlineclause+" UNION ALL (SELECT "+this.select+")";
	} else {
		this.dataselect = "SELECT "+this.select;
	}
	this.selectsqlcmd = this.cretabclause + " " + this.dataselect;
	this.sqlfilename = aux.makeUniqueTempFileName() + "_SAVESELECTBIGFILE.sql";
	fs.writeFileSync(this.sqlfilename,this.selectsqlcmd);
	this.batfilename = aux.makeUniqueTempFileName() + "_SAVESELECTBIGFILE.bat";
	this.outfilename = aux.makeUniqueTempFileName() + "_SAVESELECTBIGFILE_OUT.txt";
	this.batscript = this.mysql_clientexe + " -u"+this.dbconfig.getUser()+" -p"+this.dbconfig.getPassword()+" -Dccdb --local-infile=1 <"+this.sqlfilename + "\r\n" +
					 "dir  " + this.datadir + "ccdb\\" + this.ttabname + ".csv" + "\r\n" +
	                 "copy " + this.datadir + "ccdb\\" + this.ttabname + ".csv " + this.mfilewinpath + "\r\n";
	fs.writeFileSync(this.batfilename,this.batscript);
	this.batcmd = this.batfilename + " >" + this.outfilename;
	this.ppush([this.phead("CMDGENERATED"),"SQLFILE="+this.sqlfilename,"BATFILE="+this.batfilename,"BATCMD="+this.batcmd]);

#
# <<TSTEPNUM:9>>: execute select command for windows
#
aexpression
	childprocess.exec(this.batcmd, { timeout: 30000000 }, 
					function(err,stdout,stderr) {
						logger.debug(this.phead("EXEC_CALLBACK"));
						if (err) {
							this.errcoll.collect(err,"Error in select/export to MFILEID=\""+this.query.mfid+"\", executing \""+this.batcmd+"\"", { stdout: stdout, stderr: stderr });
							this();
							return;
						}
						this.ppush([	this.phead("cmdlineexecuted"),
										{ rawhtml: "Managed File <a href=\"/dquery?dataname=SHOWFILE&mfileid="+this.mfileid+"\">"+this.mfileid+"</a> generated"},
										{ rawhtml: "STDOUT:<br/><pre>"+stdout+"</pre>" },
										{ rawhtml: "STDERR:<br/><pre>"+stderr+"</pre>" }
									]);
						this();
					}.bind(this)
					);

#
# <<TSTEPNUM:10>>: now remove to CSV-Table again
#
sql				drop table @@ttabname@@
result_varname	dtres
post_qexpression
	this.ppush([this.phead("TEMPTABLEDROPPED"),"dropped_temptable="+this.ttabname,"",this.dtres]);
 
#
# <<TSTEPNUM:10>>: end of implementation "csvtable"
#
end