/* tsql2mysql.c
*/

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <ctype.h>

#define TRUE 1
#define FALSE 0
#define BOOLEAN short
#define MAX_BUFFER_LENGTH 40096
#define MAX_TOKENS 10000
#define DELIMCHARS " (),=<>-/'\t"
#define SPACE ' '
#define TAB '\t'
#define HEADER "DELIMITER $$\nSET SQL_MODE = ANSI$$\n"
#define FOOTER "\n$$\nDELIMITER ;$$\n"
#define INDENT_POS 2
#define MAX_NEST_LEVEL 20
#define END 0
#define END_WHILE 1
#define END_IF 2
#define DECLARATION 1
#define CURSOR 2
#define INSTRUCTION 3
#define strequal(a,b) (_stricmp(a,b) ==0)
struct line {
	char *str;
	short indent;
	line *nxt;
};
line *declarations = new line();
line *cursors = new line();
line *instructions = new line();
static void rem_chars(char *str);
static void convert_token(char *in);
static int check_statement(const char *in);
static char *make_string(char *src, size_t len);
static char *make_string(char *src);
static char *format_object_name(char *in);
static void add_comment(char *str);
static void add_statement();
static void print_indent();
static BOOLEAN has_token(const char *str);
void add_line(line *list, char *str, short indent)
{
	while(list->nxt != NULL)  
		list = list->nxt;
	list->nxt = new line();
	list->nxt->str = make_string(str);
	list->nxt->indent = indent;
	list->nxt->nxt = NULL;
}
void remove_last_line(line *list)
{
	line *ptr = list->nxt;
	line *oldptr = list;
	while(ptr->nxt != NULL)
	{
		oldptr = ptr;
		ptr = ptr->nxt;
	}
	free(ptr->str);
	delete ptr;
	oldptr->nxt = NULL;
}
void print_lines(line *list)
{
	int i;
	line *ptr = list->nxt;
	line* oldptr;
    while(ptr != NULL)
	{
		for(i=0;i<INDENT_POS*ptr->indent;i++)
			putchar(SPACE);
		if(strlen(ptr->str)<80)
			puts(ptr->str);
		else
		{
			char* tmp= ptr->str;
			while(strlen(tmp)>80)
			{
				char* pos = strchr(tmp+70,',');
				if(pos == NULL)
					pos = strchr(tmp+50,',');
				if(pos == NULL)
					pos = strchr(tmp+50,SPACE);
				if(pos != NULL)
				{
					char t = pos[1];
					pos[1] = '\0';
					puts(tmp);
					pos[1] = t;
					tmp = pos+1;
					for(i=0;i<INDENT_POS*(ptr->indent+1);i++)
						putchar(SPACE);

				}
				else
				{
					puts(tmp);
					tmp+=strlen(tmp);
				}
			}
			if(strlen(tmp)>0)
				puts(tmp);
		}
		free(ptr->str);
		oldptr = ptr;
		ptr = ptr->nxt;
		delete oldptr;
	}
	list->nxt = NULL;
}

static char *statements[] = { "SELECT", "INSERT", "UPDATE", "DELETE", "CREATE",
                              "IF", "ELSE", "WHILE", "CALL", "BEGIN", "END",
							  "DECLARE", "OPEN", "FETCH", "CLOSE", "DEALLOCATE",
							  "COMMIT", "ROLLBACK", "RETURN", "PRINT",
							  "SET", "SETUSER", "GO", "GOTO", "WAITFOR", ""}; 
static char *reserved_words[] = { "INTO", "WHERE", "FROM", "SET", "GROUP", "ORDER",
                              "BY", "HAVING", "JOIN", "ON", "AS", "AND", "INNER", "OUTER",
                              "CASE","WHEN", "THEN", "LEFT", "RIGHT", "UNION", 
                              "Max", "Sum", "Avg", "Min", ""}; 
static char *from[] = {/* data types */
	                   "nvarchar", "identity","smalldatetime","money","smallmoney",
					   "uniqueidentifier",
					   /* system variables */
             "@@identity", "@@rowcount",
					   /* functions */
					   "getdate","getutcdate","len","replicate","isnull",
					   /* other */
					   "proc","exec","insensitive",
                       ""};
static char *to[] =   {"VARCHAR", "AUTO_INCREMENT","DATETIME","DECIMAL(19,4)","DECIMAL(10,4)",
					   "BINARY(16)",
                       "Last_Insert_Id()", "Row_Count()",
					   "Now","Utc_Timestamp","Length","Repeat","Coalesce",
					   "PROCEDURE","CALL",""};
static char *tokens[MAX_TOKENS];
static int nest_level[MAX_NEST_LEVEL];
static int indent = 0;
static int count = 0;
/* prototypes*/

int main(int argc, char* argv[])
{
	char line[MAX_BUFFER_LENGTH];
	char word[MAX_BUFFER_LENGTH];
	size_t pos, len;
	int i;
  int case_level = 0;
  int parenthesis_level = 0;
	BOOLEAN in_comment = FALSE;
	BOOLEAN in_procedure = FALSE;
	BOOLEAN first_token = FALSE;
	BOOLEAN add_right_paranthesis = FALSE;
	count=0;
	while(gets(line) != NULL)
	{
		pos = 0;
		first_token = TRUE;
		while((line[pos] == SPACE) || (line[pos] == TAB))
		{
			pos++;
		}
		while(pos < strlen(line))
		{
			if(in_comment == TRUE)
			{
				/* find closing comment */
				len = strcspn(line + pos, "*");
				if(line[pos+len] == '*')
					if( line[pos+len+1] == '/')
					{
						len++;
						in_comment = FALSE;
					}
				tokens[count++]=make_string(line+pos,len);
				pos += len;
			}
			else if(line[pos] == '-' && line[pos+1] == '-')
			{
				add_comment(line+pos);
				pos = strlen(line);
			}
			else if (line[pos] == '/' && line[pos+1] == '*')
			{
				/* start of new comment */
				tokens[count++]=make_string("/*");
				pos+=2;
				in_comment = TRUE;
			}
			else if ((line[pos] == '\'') || (line[pos] == 'N' && line[pos+1]  == '\''))
			{
				/* text constant */
				if(line[pos] == 'N')
					pos++;
				len = strcspn(line + pos +1, "'");
				len+=2;
				tokens[count++]=make_string(line+pos,len);
				pos+=len;
			}
			else
			{
				len = strcspn(line + pos , DELIMCHARS);
				if(len == 0)
					len++;
				strncpy(word,line + pos, len);
				word[len] = '\0';
				convert_token(word);
        if(strequal(word,"("))
          parenthesis_level++;
        else if (strequal(word,")"))
          parenthesis_level--;
        else if (strequal(word,"CASE"))
          case_level++;
        else if (case_level > 0 &&  strequal(word,"END"))
          case_level--;

				pos += len;
				while((line[pos] == SPACE) || (line[pos] == TAB))
					pos++;
				/* no empty tokens */
				if(strlen(word) >0)
				{
					if((first_token == TRUE) &&count>0 && check_statement(word)>-1)
					{
						/* sub-selects and cursors */
						if((case_level == 0 && parenthesis_level == 0)
              && (!strequal(tokens[count-1],"FOR"))
							&& (!strequal(tokens[count-1],"UNION"))
							&&(!strequal(tokens[0],"IF") || !strequal(word,"GOTO"))
							&&(!strequal(tokens[0],"UPDATE") || !strequal(word,"SET")))
						{
							add_statement();
							count=0;
						}
					}
					if(strequal(word,",") && strequal(tokens[0],"DECLARE") && 
						(has_token("CURSOR") != TRUE))
					{
						add_statement();
						count=0;
						strcpy(word,"DECLARE");
					}
					tokens[count]=make_string(word);
					count++;
					first_token = FALSE;
				}
			}
		}
	}
	add_statement();
	
	return 0;
}


static void rem_chars(char *str)
{
	int i = 0;
	int j = 0;
	for(i=0;str[i] != '\0';i++)
	{
		if(str[i]!= '@' && str[i]!= '[' && str[i] != ']' && str[i] != '$')
		{
			if(j<i) 
				str[j] = str[i];
			j++;
		}
	}
	str[j] = '\0';
}
static void convert_token(char *in)
{
	int i;
  /* translattion */
	for(i=0;from[i][0] != '\0';i++)
		if(_stricmp(in,from[i]) == 0)
		{
			strcpy(in, to[i]);
			return;
		}
  /* toupper for statements */
	for(i=0;statements[i][0] != '\0';i++)
		if(_stricmp(in,statements[i]) == 0)
		{
			strcpy(in, statements[i]);
			return;
		}
  /* convert case for reserved words */
	for(i=0;reserved_words[i][0] != '\0';i++)
		if(_stricmp(in,reserved_words[i]) == 0)
		{
			strcpy(in, reserved_words[i]);
			return;
		}
	/* no match - remove unallowed chars and return */
	rem_chars(in);
}

static int check_statement(const char *in)
{
	int i;
	for(i=0;statements[i][0] != '\0';i++)
		if(_stricmp(in,statements[i]) == 0)
			return i;
	if(in[strlen(in)-1] == ':') /* check for label */
		return 99;
	/* no match - return -1 */
	return -1;
}
static char *make_string(char *src, size_t len)
{
	char *res = (char *)calloc(len +1,sizeof(char));
	strncpy(res,src,len);
	return res;
}
static char *make_string(char *src)
{	
	return make_string(src, strlen(src));
}
/* 
 * convert from MS format [server.][database.][owner.]object
 *           to MySQL  [owner.]object
 */
static char *format_object_name(char *in)
{
	char *pos =	strchr(in, '.');
	if(pos == NULL) /* no dot in name, it is unqualified */
		return(in);
	char *start = in;
	char *pos2 = strchr(pos+1, '.');
	while(pos2!=NULL)
	{
		start = pos+1;
		pos=pos2+1;
		pos2 = strchr(pos, '.');
	}
	return(start);
}

static void add_comment(char *str)
{
	/*
	* print a comment to end-of-line
	*/
	int i;
	if(strlen(str)>2 && str[2] != ' ')
	{
		/* MySQL comments should have a space in 3rd position */
		//putchar('-');
		char newstr[MAX_BUFFER_LENGTH];
		newstr[0] = '-';
		newstr[1] = NULL;
		strcat(newstr,str);
		newstr[2] = ' ';
		if(instructions->nxt == NULL)
			add_line(declarations,newstr,1);
		else
			add_line(instructions,newstr,indent);
	}
	else
	{
		if(instructions->nxt == NULL)
			add_line(declarations,str,1);
		else
			add_line(instructions,str,indent);
	}

}
static void add_statement()
{
	int i;
	short statement_type;
	char line[MAX_BUFFER_LENGTH];
	char tmp[MAX_BUFFER_LENGTH];
	BOOLEAN add_right_paranthesis = FALSE;
	BOOLEAN removed = FALSE;
	BOOLEAN add_semi = TRUE;
	BOOLEAN add_end = FALSE;
	BOOLEAN increase_indent = FALSE;
	static BOOLEAN in_procedure = FALSE;
	static BOOLEAN skip_begin = FALSE;
	/* only print lines with tokens */
	if(count==0)
		return;
	/* check for create procedure/function */
	if(strequal(tokens[0],"CREATE") &&
		(strequal(tokens[1],"FUNCTION") || strequal(tokens[1],"PROCEDURE")))
	{
		puts(HEADER);
		in_procedure = TRUE;
		printf("DROP %s IF EXISTS %s $$\n\n",tokens[1],format_object_name(tokens[2]));
		printf("CREATE %s %s(\n",tokens[1],format_object_name(tokens[2]));
		if(!strequal(tokens[3],"("))
		{
			//no left paranthesis in procedure header, insert one
			add_right_paranthesis = TRUE;
		}
		line[0] = '\0';
		for(i=0;i<count;i++)
		{
			if (strequal(tokens[i],"AS"))
			{
				printf("%s",line);
				if(add_right_paranthesis == TRUE)
					putchar(')');
				printf("\nBEGIN\n");
			}
			else if(i>2)
			{
				if(strequal(tokens[i],"OUT") || strequal(tokens[i],"IN"))
					printf("%s ",tokens[i]);
				else if (strequal(tokens[i],","))
				{
					printf("%s%s\n",line,tokens[i]);
					line[0] = '\0';
				}
        else if	(strequal(tokens[i],"(") && (i == 3))
        {
          /* ignore */
        }
				else
				{
          if(strlen(line) < 10)
            strcat(line,"           ");
					strcat(line," ");
					strcat(line,tokens[i]);
					/* add length to varchar if not specified*/
					if(strequal(tokens[i],"VARCHAR") && !strequal(tokens[i+1],"("))
						strcat(line,"(2000)");
				}
			}
			free(tokens[i]);
			tokens[i] = NULL;
		}
		indent=1;
		nest_level[indent] = END;
		return;
	}
	if(strequal(tokens[0],"GO")) 
	{
		if(in_procedure == TRUE)
		{
			print_lines(declarations);
			if(cursors->nxt != NULL)
			{
				indent = 1;
				print_indent();
				puts("DECLARE fetch_status INTEGER default 0;");
				print_lines(cursors);
				print_indent();
				puts("DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetch_status = 100;");
			}
			print_lines(instructions);
		   indent = 0;
		   puts("END;");
		   puts(FOOTER);
		   in_procedure = FALSE;
		}
		return;
	}
	if (strequal(tokens[0],"END"))
	{
		switch(nest_level[indent-1]){
			case END_WHILE:
				tokens[count++] = make_string("WHILE");
				break;
			case END_IF:
				tokens[count++] = make_string("IF");
				break;
		}
		nest_level[indent-1] = END;
	}
	if(skip_begin == TRUE) 
	{
		skip_begin = FALSE;
		if(strequal(tokens[0],"BEGIN") && count == 1)
			return;
		else
			add_end = TRUE;
	}

	/* statements ignored with warning*/
	if( strequal(tokens[0],"WAITFOR") ||strequal(tokens[0],"PRINT") ||
		strequal(tokens[0],"GOTO") || (tokens[0][strlen(tokens[0])-1] == ':')
		||(strequal(tokens[0],"IF") && has_token("GOTO")==TRUE))
		removed = TRUE;
	/* statements silently ignored */
	else if(strequal(tokens[0],"SETUSER") || strequal(tokens[0],"DEALLOCATE"))
		return;
	else if(strequal(tokens[0],"SET") && (count>1) &&
		(strequal(tokens[1],"QUOTED_IDENTIFIER") || strequal(tokens[1],"ANSI_NULLS") || strequal(tokens[1],"NOCOUNT")))
		return;
	else if(strequal(tokens[0],"BEGIN"))
	{
		if( count==1)
		{
			add_semi = FALSE; /* no semi-colon after BEGIN */
			indent;
		}
		else if (strequal(tokens[1],"TRANSACTION"))
		{
			free(tokens[0]);
			tokens[0] = NULL;
			tokens[0] = make_string("START");
		}
	}
	else if(strequal(tokens[0],"WHILE"))
	{
		tokens[count++] = make_string("DO");
		add_semi = FALSE; /* no semi-colon after WHILE DO */
		skip_begin = TRUE;
		nest_level[indent] = END_WHILE;
	}
	else if(strequal(tokens[0],"IF"))
	{
		tokens[count++] = make_string("THEN");
		add_semi = FALSE; /* no semi-colon after IF THEN */
		skip_begin = TRUE;
		nest_level[indent] = END_IF;
	}
	else if(strequal(tokens[0],"ELSE"))
	{
		remove_last_line(instructions); /* remove end if */
		add_semi = FALSE; /* no semi-colon after ELSE */
		skip_begin = TRUE;
		nest_level[indent-1] = END_IF;
	}

	else if(strequal(tokens[0],"COMMIT") || strequal(tokens[0],"ROLLBACK"))
	{
		if(count==2 && strequal(tokens[1],"TRANSACTION"))
		{
			/* remove "transaction" */
			free(tokens[1]);
			tokens[1] = NULL;
			count=1;
		}

	}

	line[0]='\0';
	if(removed == TRUE)
	{
		strcat(line,"-- Can not be converted ");
		add_semi = FALSE;
	}
	/* default statement printout */
	if(strequal(tokens[0],"DECLARE")) 		    
	{
		if(has_token("CURSOR")== TRUE)
			statement_type = CURSOR;
		else
			statement_type = DECLARATION;
	}
	else
		statement_type = INSTRUCTION;
	if(strequal(tokens[0],"END") && (removed == FALSE))
		indent--;

	int actualindent = indent;
	if(strequal(tokens[0],"ELSE") && (removed == FALSE))
		actualindent--;
	if((strequal(tokens[0],"BEGIN") || strequal(tokens[0],"IF") ||
		strequal(tokens[0],"WHILE")) && (removed == FALSE))
		increase_indent = TRUE;

	for(i=0;i<count;i++)
	{

		if(i>0)
		{   
			if(strequal(tokens[i],"SELECT") || strequal(tokens[i],"FROM") ||
        strequal(tokens[i],"WHERE") || strequal(tokens[i],"INNER") ||
        strequal(tokens[i],"LEFT") || strequal(tokens[i],"RIGHT") ||
        strequal(tokens[i],"HAVING") || strequal(tokens[i],"GROUP") ||
        strequal(tokens[i],"UNION") || strequal(tokens[i],"GROUP") ||
        strequal(tokens[i],"ORDER") || strequal(tokens[i],"SET")||
        (strequal(tokens[i],"JOIN") && !strequal(tokens[i-1],"INNER") && !strequal(tokens[i-1],"OUTER")))
			{
				/* new line + indent */
				if(statement_type == CURSOR)
					add_line(cursors,line,1);
				else
					add_line(instructions,line,actualindent);
				line[0]=NULL;
				if(actualindent == indent)
					actualindent++;
			}/* if the token is not a delim character we add a space */
      else if(tokens[i][0] == '\'' || (strchr(DELIMCHARS, tokens[i][0])== NULL) ) 
				strcat(line," ");
		}
		if(strequal(tokens[i],"with"))
		{
			strcat(line," /*"); // comment out with clause
			strcat(line,tokens[i++]);
			while(!strequal(tokens[i],")"))
				strcat(line,tokens[i++]);
			strcat(line,tokens[i]);
			strcat(line, "*/");
		}
    else if(strequal(tokens[i],"INTO") && tokens[i+1][0] == '#')
    {
      strcpy(tmp,line);
      strcpy(line,"CREATE TEMPORARY TABLE ");
      strcat(line,tokens[i+1]+1);
      strcat(line," AS ");
      strcat(line,tmp);
      i++;
    }
		else if(tokens[i][0] == '#')
		{
			strcat(line,tokens[i]+1);
		}
		else
		{
			strcat(line,tokens[i]);
		}
	}
	if(increase_indent == TRUE)
		indent++;

	if(add_semi == TRUE)
		strcat(line,";");
	switch(statement_type) 		    
	{
	case DECLARATION:
		add_line(declarations,line,1);
		break;
	case CURSOR:
		add_line(cursors,line,1);
		break;
	case INSTRUCTION:
		add_line(instructions,line,actualindent);
		break;
	}
	if(add_end == TRUE)
	{
		switch(nest_level[indent]){
			case END_WHILE:
				add_line(instructions,"END WHILE;",indent--);
				break;
			case END_IF:
				add_line(instructions,"END IF;",indent--);
				break;
		}
		nest_level[indent] = END;
	}
	// free used memory
	for(i=0;i<count;i++)
	{
		free(tokens[i]);
		tokens[i] = NULL;
	}

}
static void print_indent()
{
	for(int i=0;i<indent*INDENT_POS;i++)
		putchar(SPACE);
}

static BOOLEAN has_token(const char *str)
{
	for( int i=0;i<count;i++)
		if(strequal(tokens[i],str))
			return TRUE;
	return FALSE;
}