/* 
 * Copyright (c) 2003 Aftab Jahan Subedar
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions
 * are met:
 * 1. Redistributions of source code must retain the above copyright
 *    notice, this list of conditions and the following disclaimer.
 * 2. Redistributions in binary form must reproduce the above copyright
 *    notice, this list of conditions and the following disclaimer in the
 *    documentation and/or other materials provided with the distribution.
 *
 * THIS SOFTWARE IS PROVIDED BY Aftab Jahan Subedar AND CONTRIBUTORS ``AS IS'' AND
 * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED.  IN NO EVENT SHALL Aftab Jahan Subedar OR CONTRIBUTORS BE LIABLE
 * FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
 * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS
 * OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
 * HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
 * LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
 * OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
 * SUCH DAMAGE.
 *
 
 	Aftab Jahan Subedar 
	Software Engineer
	Subedar Technologies
	Subedar Baag
	Bibir Bagicha #1
	81/1-A North Jatrbari
	Dhaka 1204
	Bangladesh
	sms://+447765341890 
	sms://+880171859159
	http://www.ceobangladesh.com
	http://www.DhakaStockExchangeGame.com
	http://www.geocities.com/jahan.geo  -- source code found here
    +880171859159 sms +447765341890 jahan@geocities.com jahan@bol-online.com jahan@scientist.com



   mysql_last_value() Version 3.2
   ------------------------------

   Replaces NULL column(s) with value from  last available column value.

   Scenario
   --------

   Table to be operated on.
	   table_a
	   -------
	   record		id		color

	   1            001     BLACK
	   2			NULL	PINK
	   NULL			002		WHITE
	   3			NULL	BLUE
	   NULL			NULL	GREEN
	   NULL			003		YELLOW
	   4			004		BALCK

   Table that is converted to.
	   table_b
	   -------
	   record		id		color

	   1			001		BLACK
	   2			001		PINK
	   2			002		WHITE
	   3			002		BLUE
	   3			002		GREEN
	   3			003		YELLOW
	   4			004		BALCK


  


  compile instruction:

  cc -o mysql_last_value mysql_last_value.c -I/usr/local/include/mysql -L/usr/local/lib/mysql -lmysqlclient

  usage:
  ./mysql_last_value -u user -h host -d last_value_test -f table_from -t table_to -r "replace_field1 replace_fieldn" -v -p secretpassword

  Parameters:
             -d		database
			 -f		from which table to copy from 
			 -t		to which table to be copied to
			 -r		replace field names separated with space. upto 20 fields.
			 -v		display verbose
			 -v -v	display insert statement ;(

  example:
  ./mysql_last_value -d last_value_test -f table_a -t table_b -r "record id"  -v
  ./mysql_last_value -d last_value_test -f table_a -t table_b -r "record"  -v


  Training available on C/C++, CGI, Unix , MySQL (or other API) in Bangladesh and abroad.

*/ 

#include <stdio.h>
#include <fcntl.h>
#include <stdlib.h>
#include <unistd.h>
#include <string.h>
#include <ctype.h>
#include <mysql.h>


/* for freeing easily*/

char	*host = NULL;
char    *user = NULL;
char    *passwd = NULL;
char	*database = NULL;
char	*sql_insert_to = NULL;
char	*sql_insert_from = NULL;
int		verbose = 1;
int		use_supplied = 0;
char    *last_value=NULL;
char    *replace_field_name=NULL;
char    *criterion=NULL;
char            *insert_statement=NULL;
char            *replace_value[20];
unsigned int    *puiQuotes=NULL;
char            *pcQuery=NULL;
MYSQL		    mysql;
MYSQL_RES		*pResult=NULL;
unsigned int    *puiIndexOfReplaceField=NULL;
unsigned int    uiNumOfReplaceField=0;
unsigned long   *pulFieldLengths;
unsigned int uiReplaceIndex=0;

void usage(void);
void free_all(void);
char *strupr(char *str);
/*int strcmpp(const char *p1, const char *p2);*/
void  append_insert(unsigned long length,char *value);
unsigned int get_replace_index(unsigned int uiCurrentIndex);
void replace_field(MYSQL_ROW pTuple,unsigned int uiFieldIndex,unsigned int uiReplaceIndex);
void copy_field(MYSQL_ROW pTuple,unsigned int uiFieldIndex);

void append_insert(unsigned long  length,char *value)
{

		    unsigned long old_length=0;
			char *p;
			
			old_length=strlen(insert_statement);
			/*printf("\nRaw after strlen:%s",insert_statement);*/

			/*if ((p = (char *)realloc(insert_statement,old_length+length+1)) == NULL) */
			if ((p = (char *)malloc(old_length+length+1)) == NULL)
			{
				printf("\n Error requesting memory");
				free_all();
				exit(1);
			}

			strcpy(p,insert_statement);
			free(insert_statement);
			insert_statement=p;

			/*printf("\nB4 null:%s",insert_statement);*/

			insert_statement[old_length]='\0';

			/*printf("\nB4:%s",insert_statement);*/

			insert_statement= strcat(insert_statement, value);
			/*printf("\nAfter:%s\n\n",insert_statement);*/

}



char *strupr(char *str)
{
  char *s;

  for(s = str; *s; s++)
    *s = toupper(*s);
  return str;
}


void free_all()
{
	unsigned int i;
	if(host)
		free(host);
	if(user)
		free(user);
	if(passwd)
		free(passwd);
    if(database)
		free(database);
	if(sql_insert_to)
		free(sql_insert_to);
	if(sql_insert_from)
		free(sql_insert_from);
	if(last_value)
		free(last_value);
	if(replace_field_name)
		free(replace_field_name);
	if(criterion)
		free(criterion);
	if(insert_statement)
		free(insert_statement);
	for(i=0;i<uiNumOfReplaceField;i++)
	if(replace_value[i])
		free(replace_value[i]);
	if(puiQuotes)
		free(puiQuotes);
	if(pcQuery)
		free(pcQuery);
	if(pResult)
	    mysql_free_result(pResult);
	mysql_close(&mysql);
	if(puiIndexOfReplaceField)
		free(puiIndexOfReplaceField);


}

void usage(void) {
	printf("\n\n\tmysql_last_value %s Copyright Aftab Jahan Subedar \n\t\tjahan@geocities.com \n\t\tsms:+447765341890\n",VERSION);
	printf("\n\nusage:\n\t\tmysql_last_value [-h host] [-u user] [-p password] \n");
	printf("\t\t-d database -f from_table -t to_table -r replace_field [-v]\n");
}

int main(int argc, char **argv) 
{
	extern char     *optarg;
	MYSQL_FIELD		*pField;
	unsigned int    uiNumOfFields,uiCount,uiIterator;
	my_ulonglong    mulNumOfRows;
	MYSQL_ROW		pTuple;
	char *save;

	int i;

	/*optind = 0;*/
	while ((i = getopt(argc, argv, "h:u:p:d:f:t:r:vl:c:")) != -1) 
	{

		switch(i)
		{
			case 'h':
					host =(char *)strdup(optarg);
					break;
		    case 'u':
			        user = (char *)strdup(optarg);
					break;
		    case 'p':
					passwd =(char *) strdup(optarg);
					break;
			case 'd':
					database =(char *) strdup(optarg);
					break;
			case 'f':
					sql_insert_from = (char *)strdup(optarg);
					break;
			case 't':
					sql_insert_to = (char *)strdup(optarg);
					break;
			case 'v':
					verbose++;
					break;

			case 'l':
					last_value = (char *)strdup(optarg);
					use_supplied++;
					break;
			case 'r':
				    replace_field_name=(char *)strdup(optarg);
					replace_field_name=strupr(replace_field_name);

					break;
			case 'c':
					criterion = (char *)strdup(optarg);
					break;
			case '?':
					usage();
					free_all();
					exit(1);
					break;
			default:
			
				break;
		}
	}



	if (argc ==1) 
	{
		usage();
		printf("\n\tHint:Supply some arguments!!!%d\n",argc);
		free_all();
		exit(1);
	}
	if (verbose ==2) 
	{
		printf("\nChecking passed values");
	}

	if ((database == NULL) || (sql_insert_from == NULL) ||(sql_insert_to == NULL) ||(replace_field_name==NULL)) 
	{
		usage();
		printf("\n\tHint:Database, From Table, INTO Table and Replace Field must be provided *must* be provided!\n");
		free_all();
		exit(1);
	}
	if (verbose ==2) 
	{
		printf("\nChecking user values");
	}

	if ((use_supplied > 1) && (last_value== NULL)) 
	{
		if(verbose==2)
			printf("\nSince you did not provide last_value, I will use the previous record value. {:-p");
			use_supplied=0;
	}

	if (verbose ==2) 
	{
		printf("\nPreparing to initate MySQL connection");
	}

	if(mysql_init(&mysql)==NULL)
	{
		printf("\nFailed to initate MySQL connection");
		free_all();
		exit(1);
	}
	if (verbose ==2) 
	{
		printf("\t .....Done MySQL connection");
	}
	if (verbose ==2) 
	{
		printf("\nLogging into MySQL server");
	}

	if (!mysql_real_connect(&mysql,host,user,passwd,database,0,NULL,0))
	{
		printf("\nFailed to connect to MySQL server: Error: %s\n",
			  mysql_error(&mysql));
		free_all();
		exit(1);

	}
	if (verbose ==2) 
	{
		printf("\t .....Logged into  MySQL server");
	}

	if (verbose ==2) 
	{
		printf("\nAllocating some memory from OS");
	}

	if(criterion==NULL)
		uiCount=  14/* select * from+space*/+strlen(sql_insert_from) +1/*null*/;
	else
		uiCount=  14/*select * from+space*/+strlen(sql_insert_from)+1/*space*/+6/*where+space*/+strlen(criterion)+1/*null*/;

    if ((pcQuery = (char *)malloc(uiCount)) == NULL)
	{
		printf("\n Error requesting memory");
		free_all();
		exit(1);
	}
	if (verbose ==2) 
	{
		printf("\n\t .....Received memory from OS");
	}
	if (verbose ==2) 
	{
		printf("\n Opening Table to be inserted from..");
	}
	if(criterion==NULL)
	    sprintf(pcQuery,"SELECT * FROM %s",sql_insert_from);
	else
		sprintf(pcQuery,"SELECT * FROM %s WHERE %s",sql_insert_from,criterion);

	if (verbose ==2) 
	{
		printf("\nDoing Query %s",pcQuery);
	}

	if (mysql_query(&mysql,pcQuery))
	{
		printf("\nFailed to open table. Error: %s\n",mysql_error(&mysql));
		free_all();
		exit(1);

	}
	else /* query succeeded, process any data returned by it*/
	{
		if (verbose ==2) 
		{
			printf("\nQuery succeeded.\nDoing initial calls");
		}

        pResult = mysql_store_result(&mysql);

		if (pResult)  /* there are rows*/
		{
			uiNumOfFields = mysql_num_fields(pResult);
            mulNumOfRows = mysql_affected_rows(&mysql);
			if (verbose ==2) 
			{
				printf("\nIntial Calls Received: \n\t\tNoOfFields:\t%d\n\t\tNumOfRows:\t%lu",uiNumOfFields,mulNumOfRows);
			}
		}
		else  /* mysql_store_result() returned nothing; should it have?*/
		{
			printf("\nFailed to open table. Error: %s\n",mysql_error(&mysql));
			free_all();
			exit(1);
		}
	}
	if(mulNumOfRows <=0)
	{
			printf("\nEmpty Source Table. ");
			free_all();
			exit(1);
	}

	if (verbose ==2) 
	{
		printf("\n\t .....Table opened");
	}

	if (verbose ==2) 
	{
	  printf("\nCounting the required length for insert statement");
	}

    if ((puiQuotes = (unsigned int*) malloc(sizeof(unsigned int) *uiNumOfFields )) == NULL)/* a 'or space and a null and both side*/
	{
		printf("\n Error requesting memory");
		free_all();
		exit(1);
	}


	uiCount=0L;/* insert length */
	strupr(replace_field_name);
	if ((puiIndexOfReplaceField =(unsigned int *) malloc(sizeof(unsigned int) * 20)) == NULL)/*warning:support of 20 fields only*/
	{
		printf("\nError requesting memory for IndexOfReplaceField");
		free_all();
		exit(1);
	}

    uiNumOfReplaceField=0;
	uiCount=0;
	while ((pField = mysql_fetch_field(pResult))) 
	{
		  strupr(pField->name);/*its ok,its not const*/
		  save=replace_field_name;
		  if(strstr(save,pField->name)!=NULL)
		  {
				 puiIndexOfReplaceField[uiNumOfReplaceField]=uiCount ;/*building replace index*/
				 uiNumOfReplaceField++;
				if ((replace_value[uiNumOfReplaceField] = (char *)malloc(pField->length+1l)) == NULL)
				{
					printf("\n Error requesting memory");
					free_all();
					exit(1);
				}

		  }

		  switch(pField->type)
		  {
					/*case FIELD_TYPE_CHAR :*/
					case FIELD_TYPE_TINY :
					case FIELD_TYPE_SHORT :
					case FIELD_TYPE_LONG :
					case FIELD_TYPE_INT24 :
					case FIELD_TYPE_LONGLONG :
					case FIELD_TYPE_FLOAT  :
					case FIELD_TYPE_DOUBLE :
					case FIELD_TYPE_DECIMAL :
									  puiQuotes[uiCount]=0;
									  break;
					default:
									  puiQuotes[uiCount]=1;
									  break;
			  }
		  	uiCount++;

	}

	if(uiNumOfReplaceField==0 || uiNumOfReplaceField>20)
	{
	    printf("\nError: Replace Field Name not found in the table.");
		free_all();
		exit(1);
	}

    /*malloc according to uiCount for insert_statement + insert and other commands*/
    uiCount=12/*INSERT INTO */+strlen(sql_insert_to)+1/*space*/;
    uiCount+=8;/*VALUES()*/
	uiCount+=uiNumOfFields *2;/*single quotes+space ' '*/
	uiCount+=uiNumOfFields -1 ;/*comma ,*/
	uiCount+=10;/*null terminator+secured buffer*/

	if (verbose ==2) 
	{
		printf("\nAllocating some memory from OS");
	}


	if (verbose ==2) 
	{
		printf("\nDoing the big loop\n");
	}

	if (mulNumOfRows>0L) 
	{

	  /*sprintf(insert_statement,"INSERT INTO %s VALUES(*/
	  /*strcat(insert_statement,sql_insert_to);
	  strcat(insert_statement," VALUES(");*/

      insert_statement=NULL;
	  while ((pTuple = mysql_fetch_row(pResult)) != NULL) 
	  {
		    if(insert_statement!=NULL)
				free(insert_statement);
			if ((insert_statement =(char *) malloc(uiCount)) == NULL)
			{
				printf("\nError requesting memory for Insert Buffer");
				free_all();
				exit(1);
			}

			pulFieldLengths=mysql_fetch_lengths(pResult);
	        sprintf(insert_statement,"INSERT INTO %s VALUES(",sql_insert_to);

			if (verbose ==2 ) 
			{
				printf("\nInitialized fetch done.");
			}

			/*initiation*/

			uiReplaceIndex=get_replace_index(0);

			if(uiReplaceIndex>0) /*if this is the replace field*/
				replace_field(pTuple,0,uiReplaceIndex-1);
			else
				copy_field(pTuple,0);
		
			for (uiIterator = 1; uiIterator < uiNumOfFields; uiIterator++) 
				{
					insert_statement=strcat(insert_statement, ","); /*Comma n has been malloced above*/
					uiReplaceIndex=get_replace_index(uiIterator);

					if(uiReplaceIndex>0) /*if this is the replace field*/
						replace_field(pTuple,uiIterator,uiReplaceIndex-1);
					else
						copy_field(pTuple,uiIterator);

				}/* for*/

				insert_statement=strcat(insert_statement,")");/*values close*/
				if (verbose ==3) 
				{
					printf("\n%s\n",insert_statement);
				}

				if (verbose ==2) 
				{
					printf("\nInserting\n");
				}

				if (mysql_query(&mysql,insert_statement))
				{
					printf("\nFailed to replace fields. Error: %s\n",mysql_error(&mysql));
					free_all();
					exit(1);

				}



			}/*while*/
	}/*if has rows */
		

	printf("Done\n");
			
	free_all();          
	exit(0);
}			

unsigned int get_replace_index(unsigned int uiCurrentIndex)
{
	unsigned int i;
	for(i=0;i<uiNumOfReplaceField;i++)
	{
		if(puiIndexOfReplaceField[i]==uiCurrentIndex) /*if this is the replace field*/
		{
			/*printf("\n%u",puiIndexOfReplaceField[i]);*/
			return ++i;
		}
	}
	return 0;
}

void replace_field(MYSQL_ROW pTuple,unsigned int uiFieldIndex,unsigned int uiReplaceIndex)
{
				
						char temp[2];
						temp[1]='\0';
						temp[0]=puiQuotes[uiFieldIndex]?'\'':' ';

						if (pTuple[uiFieldIndex] != 0) /*if filed has value*/
						{
							/*free n malloc*/
							if(replace_value[uiReplaceIndex]!=NULL)
								free(replace_value[uiReplaceIndex]);
							if ((replace_value[uiReplaceIndex] = (char *)malloc(pulFieldLengths[uiFieldIndex]+1l)) == NULL)
							{
								printf("\n Error requesting memory");
								free_all();
								exit(1);
							}

							strcpy(replace_value[uiReplaceIndex],pTuple[uiFieldIndex]);
							insert_statement=strcat(insert_statement,temp); /*Quotes Open*/
								
							append_insert(pulFieldLengths[uiFieldIndex],pTuple[uiFieldIndex])	;
							insert_statement=strcat(insert_statement, temp);/*Quotes Close*/

						} 
						else /*else field has no value*/
						{
							if(replace_value[uiReplaceIndex]!=NULL)/*if replace value has value*/
							{		
								insert_statement=strcat(insert_statement, temp); /*Quotes Open*/
								append_insert(strlen(replace_value[uiReplaceIndex]),replace_value[uiReplaceIndex])	;
								insert_statement=strcat(insert_statement, temp);/*Quotes Close*/

							}
							else/*else replace valeu has no value*/
							{
								append_insert(4,"NULL")	;

							}
						}

}

void copy_field(MYSQL_ROW pTuple,unsigned int uiFieldIndex)
{
	                    char temp[2];
						temp[1]='\0';
						temp[0]=puiQuotes[uiFieldIndex]?'\'':' ';

						if (pTuple[uiFieldIndex] != 0) /*if filed has value*/
						{

		                    insert_statement=strcat(insert_statement,temp); /*Quotes Open*/
							append_insert(pulFieldLengths[uiFieldIndex],pTuple[uiFieldIndex])	;
							insert_statement=strcat(insert_statement, temp);/*Quotes Close*/

						} 
						else /*else filed has no value*/
						{
								append_insert(4,"NULL")	;

						}

}