/*
	This program reads Sybase SQL requests from a file, transforms them in MySQL dialect and executes
	the result on a MySQL server.
*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <errno.h>
#include <tlmplib.h>
#include "lasuite.h"
#include "tdslib.h"
#include <tlmpsql.h>
#include <string>
#include <map>

using namespace std;


struct OPTIONS{
	bool verbose;
	bool showstruct;
	bool othererror;
	bool showparseerror;
	bool noif;
	OPTIONS(){
		verbose = false;
		showstruct = false;
		othererror = false;
		showparseerror = false;
		noif = false;
	}
};

struct STATUS {
	int error;
	int parse_error;
	STATUS(){
		error = 0;
		parse_error = 0;
	}
};

<mod>
static int syb2my_parseexec (const char *line, const OPTIONS &opts, STATUS &status, bool onlyprint)
{
	glocal int error = 0;
	PARSE p (line);
	EXPR expr;
	int parse_error = 0;
	if (f_main(p,expr)==-1){
		if (opts.showparseerror){
			tlmp_error ("Can't parse this SQL: %s\n",line);
			p.showlast();
		}
		parse_error++;
	}else{
		if (opts.showstruct) expr.dump();
		glocal const OPTIONS *opts = &opts;
		glocal vector<ONESQL> sql_mys;	// SQL in MySQL syntax
		glocal string sql_syb = str_skip(line);
		if (opts.verbose) printf ("SYBASE: %s\n",line);
		SQL_OPTS sopts;
		if (parser_expr_reformat (expr,glocal.sql_mys,sopts,0) != -1){
			glocal bool if_is_true = false;		// The result of the if_statement is true or false
			glocal bool if_sequence = false;	// We are in a if sequence (multiple statements)
			glocal bool if_statement = false;	// We are processing the condition part of the if
			glocal bool skip = false;		// We must not process the current statement
			for (unsigned i=0; i<glocal.sql_mys.size(); i++){
				glocal const char *sql = glocal.sql_mys[i].sql.c_str();
				glocal.sql = str_skip(glocal.sql);
				if (glocal.sql[0] != '\0'){
					if (opts.verbose || onlyprint){
						printf ("MYSQL[%u]: %s\n",i,glocal.sql);
					}
					if (onlyprint) continue;
					if (strncmp(glocal.sql,"IF ",3)==0){
						// IF::reformat() putting this IF token so we know we have
						// to control the logic ourself since mysql do not support this
						glocal.sql += 3;
						glocal.if_sequence = true;
						glocal.if_statement = true;
					}
					if (strncmp(glocal.sql,"print ",6)==0){
						if (!glocal.if_sequence || !opts.noif){
							if (!glocal.skip){
								printf ("PRINT %s\n",glocal.sql+6);
							}
						}
					}else if (strcmp(glocal.sql,"END")==0){
						glocal.if_sequence = false;
						glocal.skip = false;
					}else if (strcmp(glocal.sql,"ELSE")==0){
						if (glocal.if_sequence){
							glocal.skip = glocal.if_is_true;
						}
					}else if (glocal.skip){
						printf ("SKIP %s\n",glocal.sql);
					}else if (!glocal.if_sequence || !opts.noif){
						<call sql_query>("%s;",glocal.sql);
						<f onerow>
							//printf ("row[0]=%s if_sequence %d if_statement %d\n",row[0],glocal.if_sequence,glocal.if_statement);
							if (glocal.if_sequence && glocal.if_statement){
								glocal.if_is_true = atoi(row[0]) != 0;
								glocal.skip = !glocal.if_is_true;
							}
						</f>
						<f error>
							glocal.error++;
							if (errcode == 1046 || errcode == 1064 || errcode == 1582){
								if (!glocal.opts->verbose){
									printf ("SYBASE: %s\n",glocal.sql_syb.c_str());
									printf ("MYSQL: %s\n",glocal.sql);
								}
								tlmp_error ("\tMysql syntax: %s\n",msg);
							}else{
								if (glocal.opts->othererror){
									tlmp_error ("\tMysql error: %d %s\n",errcode,msg);
								}
							}
							if (glocal.if_sequence && glocal.if_statement){
								glocal.if_is_true = false;
								glocal.skip = true;
							}
						</f>
						</call>
					}
					glocal.if_statement = false;
				}
			}
		}else{
			printf ("MYSQL:  Not done\n");
			for (unsigned i=0; i<glocal.sql_mys.size(); i++){
				printf ("\t%s\n",glocal.sql_mys[i].sql.c_str());
			}
			glocal.error++;
		}	
	}
	status.parse_error += parse_error;
	status.error += glocal.error;
	return glocal.error == 0 && parse_error == 0 ? 0 : -1;
}
</mod>

static bool line_is_filled(const string &buf)
{
	bool ret = false;
	for (unsigned i=0; i<buf.size(); i++){
		if (!isspace(buf[i])){
			ret = true;
			break;
		}
	}
	return ret;
}

static void syb2my_set_renamedb(const vector<string> &renamedb)
{
	bool some_errors = false;
	for (unsigned i=0; i<renamedb.size(); i++){
		const char *s = renamedb[i].c_str();
		const char *pt = strchr(s,'=');
		if (pt == NULL){
			tlmp_error ("Option --renamedb, = sign missing: %s\n",s);
			some_errors = true;
		}else{
			string old = string(s,pt-s);
			parser_set_renamedb (old.c_str(),pt+1);
		}
	}
	if (some_errors){
		tlmp_error ("Aborting\n");
		exit (-1);
	}
}
<mod>
static int syb2my_print_bcpconvert (const char *table)
{
	glocal int ret = -1;
	<call sql_query>("describe %s",table);
	<f onerow>
		if (strcmp(row[1],"timestamp")==0
			|| strcmp(row[1],"datetime")==0){
			printf (" -c%d,sybdatetime,0",rownum+1);
		}else if (strcmp(row[1],"date")==0){
			printf (" -c%d,sybdate,0",rownum+1);
		}else if (strncmp(row[1],"decimal(",8)==0){
			const char *pt = row[1]+8;
			while (isdigit(*pt)) pt++;
			if (*pt != ','){
				tlmp_error ("Definition de champs inconnue/invalide: %s\n",row[1]);
			}else{
				int decimals = atoi(pt+1);
				printf (" -c%d,num,%d",rownum+1,decimals);
			}
		}else if (strncmp(row[1],"smallint(",9)==0
			|| strncmp(row[1],"tinyint(",8)==0
			|| strncmp(row[1],"bigint(",7)==0
			|| strncmp(row[1],"int(",4)==0){
			printf (" -c%d,num,0",rownum+1);
		}else if (strncmp(row[1],"char(",5)==0
			|| strncmp(row[1],"varchar(",8)==0
			|| strncmp(row[1],"longtext",4)==0
			|| strncmp(row[1],"text",4)==0){
			//fprintf (stderr,"%s row[4]=%s\n",row[0],row[4]);
			if (strcmp(row[2],"YES")==0){
				printf (" -c%d,charnull,0",rownum+1);
			}else{
				printf (" -c%d,escape,0",rownum+1);
			}
		}else if (strncmp(row[1],"binary(",7)==0){
			printf (" -c%d,binary,0",rownum+1);
		}else if (strncmp(row[1],"bit(",4)==0){
			printf (" -c%d,bit,0",rownum+1);
		}
		//printf ("%s %s\n",row[0],row[1]);
		glocal.ret = 0;
	</f>
	</call>
	return glocal.ret;
}
</mod>
static string toupper(const char *s)
{
	string ret;
	while (*s != '\0'){
		ret += toupper(*s);
		s++;
	}
	return ret;
}
struct SYSID{
	string table;
	string field;
	SYSID(const string &_table, const string &_field){
		table = _table;
		field = _field;
	}
	SYSID(const char *_table, const char *_field){
		table = _table;
		field = _field;
	}
	bool operator < (const SYSID &s) const {
		bool ret = false;
		int cmp = strcmp(table.c_str(),s.table.c_str());
		if (cmp < 0){
			ret = true;
		}else if (cmp == 0){
			ret = field < s.field;
		}
		return ret;
	}
};

<mod>
static int syb2my_print_syscolumns (const char *sysids_file)
{
	glocal int ret = -1;
	glocal vector<string> tables;
	glocal map<string,int> type2num;
	glocal map<SYSID,int> sysids;
	glocal.type2num["char"] = 1;
	glocal.type2num["varchar"] = 2;
	glocal.type2num["smallint"] = 6;
	glocal.type2num["text"] = 19;
	glocal.type2num["datetime"] = 22;
	glocal.type2num["bigint"] = 10;
	glocal.type2num["tinyint"] = 5;
	glocal.type2num["bit"] = 16;
	glocal.type2num["binary"] = 3;
	glocal.type2num["decimal"] = 10;
	glocal.type2num["int"] = 7;
	glocal.type2num["mediumblob"] = 20;
	if (sysids_file != NULL){
		<call loadfile>(sysids_file,true);
		<f oneline>
			if (noline > 1){
				vector<string> words;
				int n = str_splitline(line,' ',words);
				if (n == 3){
					glocal.sysids[SYSID(words[0],words[1])] = atoi(words[2].c_str());
				}
			}
			return 0;
		</f>
		</call>
	}
	<call sql_query>("show tables");
	<f onerow>
		glocal.tables.push_back(row[0]);
	</f>
	</call>
	FILE *fout = fopen ("id2name.h","w");
	if (fout == NULL){
		tlmp_error ("Can't open id2name.h\n");
		exit (-1);
	}
	for (unsigned i=0; i<glocal.tables.size(); i++){
		glocal int id = i;
		const char *table = glocal.tables[i].c_str();
		glocal string TABLE = toupper(table);
		fprintf (fout,"\tid2name[%d] = \"%s\";\n",i,glocal.TABLE.c_str());
		fprintf (fout,"\tname2id[\"%s\"] = %d;\n",glocal.TABLE.c_str(),i);
		printf ("insert into sysobjects (name,uid,type,id) values ('%s',1,'U',%d);\n",glocal.TABLE.c_str(),glocal.id);
		//fprintf (stderr,"describe %s\n",table);
		<call sql_query>("describe `%s`",table);
		<f onerow>
			// Analyse the type to retrieve the length and the precision
			string type = row[1];
			int length=0;
			string prec("NULL");
			string scale("NULL");
			const char *pt = strchr(row[1],'(');
			if (pt != NULL){
				type = string(row[1],pt-row[1]);
				pt++;
				length = atoi(pt);
				if (type != "char"
					&& type != "varchar"
					&& type != "text"
					&& type != "smallint"
					&& type != "tinyint"
					&& type != "bit"
					&& type != "datetime"){
					if (type != "int" || length != 11){
						char tmp[20];
						snprintf (tmp,sizeof(tmp)-1,"%d",length);
						prec = tmp;
						pt = strchr(pt,',');
						int scaleval = 0;
						if (pt != NULL) scaleval = atoi(pt+1);
						snprintf (tmp,sizeof(tmp)-1,"%d",scaleval);
						scale = tmp;
					}
				}
			}
			if (type == "smallint"){
				length = 2;
			}else if (type == "tinyint"){
				length = 1;
			}else if (type == "int"){
				length = 4;
			}else if (type == "mediumblob"){
				length = 16;
			}else if (type == "datetime"){
				length = 8;
			}else if (type == "decimal"){
				extern unsigned char tbstorage[];
				length = tbstorage[length];
			}
			int usertype = glocal.type2num[type];
			const char *name = row[0];
			if (strncmp(name,"_CF_",4)==0) name +=4;	// Unpatch the _CF_ to show the original field name
			string NAME = name; // toupper(name);
			int status = strcmp(row[2],"YES")==0 ? 8 : 0;
			if (strcmp(row[3],"PRI")==0 && strcmp(row[5],"auto_increment")==0) status += 128;
			map<SYSID,int>::iterator it = glocal.sysids.find(SYSID(glocal.TABLE,NAME));
			int cdefault = 0;
			if (it != glocal.sysids.end()) cdefault = it->second;
			printf ("insert into syscolumns (id,name,usertype,length,prec,scale,status,cdefault) values (%d,'%s',%d,%d,%s,%s,%d,%d);\n"
				,glocal.id,NAME.c_str(),usertype,length,prec.c_str(),scale.c_str(),status,cdefault);
		</f>
		</call>
	}
	fclose (fout);
	return 0;
}

</mod>	
<mod>
int main (int argc, char *argv[])
{
	glocal int ret = -1;
	glocal const char *server = "localhost";
	glocal const char *db = "test";
	glocal const char *user = "test";
	glocal const char *passwd = NULL;
	glocal const char *snifferfile = NULL;
	glocal const char *sqlfile = NULL;
	glocal bool  onlyprint = false;
	glocal bool  nocreateindex = false;
	glocal const char *bcpconvert = NULL;
	glocal bool syscolumns = false;
	glocal bool donotstop = false;
	glocal vector<string> renamedb;
	glocal vector<string> usertypes;
	glocal const char *fusertypes = NULL;
	glocal vector<string> createonly;	// Only create tables in that list, or all if empty
	glocal const char *engine = "innodb";
	glocal const char *sysidsfile = NULL;
	glocal OPTIONS opts;
	parser_initfunctions();
	parser_inittokens();
	glocal.ret = <call tlmpprogram>(argc,argv,"tlmpsql");
	<f init>
		setproginfo ("syb2my",VERSION
			,"Convert Sybase SQL syntax into MySql syntax\n");
		setgrouparg ("Actions");
		setarg (' ',"sqlfile","File to execute",glocal.sqlfile,false);
		setarg (' ',"donotstop","Stop even if SQL errors",glocal.donotstop,false);
		setgrouparg ("tests");
		setarg ('u',"user","mysql user",glocal.user,false);
		setarg ('p',"passwd","mysql password",glocal.passwd,false);
		setarg ('s',"server","Database server",glocal.server,false);
		setarg (' ',"db","Database name",glocal.db,false);
		setarg (' ',"snifferfile","File produced by lasuite",glocal.snifferfile,false);
		setgrouparg ("Misc.");
		setarg ('v',"verbose","Show more stuff",glocal.opts.verbose,false);
		setarg (' ',"showstruct","Show parser structure",glocal.opts.showstruct,false);
		setarg (' ',"showparseerror","Show invalid sybase SQL",glocal.opts.showparseerror,false);
		setarg (' ',"othererror","Other SQL errors",glocal.opts.othererror,false);
		setarg (' ',"renamedb","Rename database old=new",glocal.renamedb,false);
		setarg (' ',"nocreateindex","Do not create index",glocal.nocreateindex,false);
		setarg (' ',"noif","Do not execute if statements",glocal.opts.noif,false);
		setarg (' ',"bcpconvert","Generate the bcpconvert option for a table",glocal.bcpconvert,false);
		setarg (' ',"usertype","Define a usertype sybase=mysql",glocal.usertypes,false);
		setarg (' ',"usertypes","CSV file for user defined types(user -> sybase)",glocal.fusertypes,false);
		setarg (' ',"createtb","Skip table creation for table no in this list",glocal.createonly,false);
		setarg (' ',"mysqlengine","Engine for create table",glocal.engine,false);
		setarg (' ',"syscolumns","Populate table syscolumns",glocal.syscolumns,false);
		setarg (' ',"sysidsfile","cdefault extracted from sybase",glocal.sysidsfile,false);
		setarg (' ',"onlyprint","With --sqlfile, only print MySQL query",glocal.onlyprint,false);
	</f>
	<f main_noarg>
		int ret = -1;
		query_setdefaultdb (glocal.server,glocal.db,glocal.user,glocal.passwd);
		syb2my_set_renamedb (glocal.renamedb);
		parser_set_createindex (glocal.nocreateindex);
		parser_set_createtb (glocal.createonly);
		parser_set_engine(glocal.engine);
		tdslib_setusertypes(glocal.usertypes);
		tdslib_loadusertypes(glocal.fusertypes);
		if (glocal.bcpconvert != NULL){
			ret = syb2my_print_bcpconvert(glocal.bcpconvert);
		}else if (glocal.syscolumns){
			ret = syb2my_print_syscolumns(glocal.sysidsfile);
		}else if (glocal.snifferfile != NULL){
			STATUS status;
			glocal int nbline = 0;
			FILE *fin = fopen (glocal.snifferfile,"r");
			if (fin == NULL){
				tlmp_error ("Can't open file %s (%s)\n",glocal.snifferfile,strerror(errno));
			}else{
				char line[1000000];
				bool snifferfile = false;
				while (fgets(line,sizeof(line),fin)!=NULL){
					strip_end (line);
					const char *start = str_skip(line);
					if (*start == '-'){
						// do nothing, sybase comment
					}else if (strncmp(start,"print ",6)==0){
						// Do nothing
					}else if (strncmp(line,"NBALIKE=",8)==0){
						snifferfile = true;
					}else if (line[0] == ' ' || !snifferfile){
						glocal.nbline++;
						syb2my_parseexec (line,glocal.opts,status,glocal.onlyprint);
					}
				}
				fclose (fin);
			}
			printf ("nbline=%d parse_error=%d mysql_error=%d\n",glocal.nbline,status.parse_error,status.error);
			ret = 0;
		}else if (glocal.sqlfile != NULL){
			STATUS status;
			int nbsql = 0;
			FILE *fin = fopen (glocal.sqlfile,"r");
			if (fin == NULL){
				tlmp_error ("Can't open file %s (%s)\n",glocal.sqlfile,strerror(errno));
			}else{
				int noline = 0;
				string buf;
				char line[1000000];
				while (fgets(line,sizeof(line),fin)!=NULL){
					noline++;
					strip_end (line);
					const char *start = str_skip(line);
					if (*start == '-'){
						// do nothing, sybase comment
					}else if(strcasecmp(start,"go")==0){
						if (line_is_filled(buf)){
							start = str_skip(buf.c_str());
							if (strncasecmp(start,"sp_placeobject",14)==0){
								// Do nothing
							}else{
								nbsql++;
								if (syb2my_parseexec (buf.c_str(),glocal.opts,status,glocal.onlyprint) == -1
									&& !glocal.donotstop){
									tlmp_error ("Error seen, aborting\n");
									break;
								}
							}
						}
						buf.clear();
					}else{
						buf += line;
						buf += " ";
					}
				}
				fclose (fin);
			}
			printf ("nbsql=%d parse_error=%d mysql_error=%d\n",nbsql,status.parse_error,status.error);
			ret = 0;
		}else{
			tlmp_error ("Option --sqlfile, --snifferfile or --bcpconvert must be used\n");
			usage();
		}
		return ret;
	</f>
	<f main>
		int ret = -1;
		query_setdefaultdb (glocal.server,glocal.db,glocal.user,glocal.passwd);
		syb2my_set_renamedb (glocal.renamedb);
		parser_set_createindex (glocal.nocreateindex);
		parser_set_createtb (glocal.createonly);
		parser_set_engine(glocal.engine);
		tdslib_setusertypes(glocal.usertypes);
		tdslib_loadusertypes(glocal.fusertypes);
		string onesql;
		for (int i=0; i<argc; i++){
			onesql += " ";
			onesql += argv[i];
		}
		STATUS status;
		if (syb2my_parseexec (onesql.c_str(),glocal.opts,status,glocal.onlyprint) != -1){
			ret = 0;
		}
		#if 0
		PARSE p (onesql.c_str());
		EXPR expr;
		if (f_main(p,expr)==-1){
			tlmp_error ("Error\n");
		}else{
			printf ("ok\n");
			expr.dump();
			printf ("\n");
			vector<string> mys;	// SQL in MySQL syntax
			if (parser_expr_reformat (expr,mys) != -1){
				for (unsigned i=0; i<mys.size(); i++){
					glocal const char *sql = mys[i].c_str();
					printf ("MYSQL[%u]: %s\n",i,glocal.sql);
					if (strncmp(glocal.sql,"IF ",3)==0){
						// IF::reformat() putting this IF token so we know we have
						// to control the logic ourself since mysql do not support this
						glocal.sql += 3;
					}
					<call sql_query>("%s;",glocal.sql);
					<f onerow>
					</f>
					<f error>
						if (errcode == 1046 || errcode == 1064 || errcode == 1582){
							tlmp_error ("Mysql syntax: %s\n",msg);
						}else{
							tlmp_error ("Mysql autre: %d %s\n",errcode,msg);
						}
					</f>
					</call>
				}
			}else{
				printf ("MYSQL:  Not done\n");
			}
		}
		#endif
		return ret;
	</f>
	</call>
	return glocal.ret;
}
</mod>