/*
 * Copyright (C) 2005 Walter Garrote
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public License
 * as published by the Free Software Foundation; either version 2.1,
 * or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; see the file COPYING.LIB.  If
 * not, write to the Free Software Foundation, Inc., 59 Temple Place,
 * Suite 330, Boston, MA 02111-1307 USA
 */
//---------------------------------------------------------------------------
// Implemented by Walter Garrote
// Brazil - Goiania-GO
//---------------------------------------------------------------------------
#include "ncurses.h"
#include "htcoblib.h"
#include "filesql.h"

struct fields_sql *mount_fields;
struct fd_sql *mount_sql;
struct file_sql *mount_files;

int ALL_INDEX_FILES_TO_GATEWAY = 0;

// read /etc/tinysql and create struct
int tcob_read_tinysql(void) {
	char linha[1025], linhar[1025], *var, *val, *cmp;
//	struct file_sql *rf;
//	struct file_sql *rf_new;
	FILE *file;

	file = fopen("/etc/tinysql", "r"); // try open /etc/tinysql
	if(!file) {
#ifdef DEBUG_RTS
		fprintf(stderr, "Failed to open /etc/tinysql\n");
#endif
		return 0;
	}

	fgets(linha, 1024, file);
	while(!feof(file)) {
		clean_read_line(linha, linhar, 0); 
		if(strlen(linhar)) {
			var = NULL; val = NULL; cmp = NULL;
			var = strtok(linhar, " ");
			if(var && strncmp(var, "#", 1) != 0) {
				val = strtok(NULL, " ");
				cmp = strtok(NULL, " ");
			}
			if(var && val) {
				if(strcmp(var, "sql_server")==0) { // define server
					sql_server = 0;
					if(strcmp(val, "mysql")==0)
						sql_server = ORG_MYSQL;
					if(strcmp(val, "pgsql")==0)
						sql_server = ORG_PGSQL;
				}
				if(strcmp(var, "sql_db")==0) { // define database
					if(sql_db)
						free(sql_db);
					sql_db = malloc(sizeof(char) * (strlen(val) + 1));
					strcpy(sql_db, val);
				}
				if(strcmp(var, "sql_host")==0) { // define host
					if(sql_host)
						free(sql_host);
					sql_host = malloc(sizeof(char) * (strlen(val) + 1));
					strcpy(sql_host, val);
				}
				if(strcmp(var, "sql_passwd")==0) { // define password
					if(sql_passwd)
						free(sql_passwd);
					sql_passwd = malloc(sizeof(char) * (strlen(val) + 1));
					strcpy(sql_passwd, val);
				}
				if(strcmp(var, "sql_user")==0) { // define user
					if(sql_user)
						free(sql_user);
					sql_user =  malloc(sizeof(char) * (strlen(val) + 1));
					strcpy(sql_user, val);
				}
				if(strcmp(var, "fdsql")==0) { // define path for descriptions
					if(sql_fdsql)
						free(sql_fdsql);
					sql_fdsql = malloc(sizeof(char) * (strlen(val) + 1));
					strcpy(sql_fdsql, val);
				}
				if(strcmp(var, "file")==0) { // define files
					if(strcmp(val, "all")==0)
						ALL_INDEX_FILES_TO_GATEWAY = 1;
					 else
						create_mount_files(val, cmp);
/*					 else {
						rf = mount_files;
						if(!rf) {
							mount_files = malloc(sizeof(struct file_sql));
							mount_files->original_name = malloc(sizeof(char) * strlen(val));
							memmove(mount_files->original_name, val, strlen(val));
							if(cmp) {
								mount_files->table_name = malloc(sizeof(char) * strlen(cmp));
								memmove(mount_files->table_name, cmp, strlen(cmp));
							}
							mount_files->next = NULL;
						} else {
							while(rf->next != NULL)
								rf = rf->next;
							rf_new = malloc(sizeof(struct file_sql));
							rf_new->original_name = malloc(sizeof(char) * strlen(val));
							memmove(rf_new->original_name, val, strlen(val));
							if(cmp) {
								rf_new->table_name = malloc(sizeof(char) * strlen(cmp));
								memmove(rf_new->table_name, cmp, strlen(cmp));
							}
							rf_new->next = NULL;
							rf->next = rf_new;
						} 
					} */
				}
			}
		}
		fgets(linha, 1024, file);
	}

	fclose(file); // close /etc/tinysql

	if(!sql_db) { // sql_db not specified in /etc/tiny
#ifdef DEBUG_RTS
		fprintf(stderr, "Please specify sql_db in /etc/tinysql\n");
#endif
		return 0;
	}
	if(!sql_fdsql) { // sql_fdsql not specified in /etc/tiny
#ifdef DEBUG_RTS
		fprintf(stderr, "Please specify sql_fdsql in /etc/tinysql\n");
#endif
		return 0;
	}
	if(!sql_server) // sql_server not specified int /etc/tiny, use default sql
		sql_server = ORG_MYSQL;
	if(!sql_host) // sql_host not specified in /etc/tiny, use default host
		strcpy(sql_host, "localhost");
	if(!sql_user) // sql_user not specified in /etc/tiny, use default user
		strcpy(sql_user, "root");

	return 1;
}

// put the file name in struct
void create_mount_files(char *file_name, char *tb_name) {
	struct file_sql *rf;
	struct file_sql *rf_new;
	char *p, *table_name;

	if(tb_name) // table name was passed ?
		table_name = tb_name;
	 else { // create table name using filename
		p = malloc(sizeof(char) * strlen(file_name) + 1);
		strcpy(p, file_name);
		p = strtok(p, "/");
		while(p) {
			table_name = p;
			p = strtok(NULL, "/");
		}
		if(strchr(table_name, '.'))
			table_name = strtok(table_name, ".");
	}

	rf = mount_files; // verify if the file was previous open and didn't closed
	while(rf != NULL) {
		if(strcmp(rf->original_name, file_name)==0)
			return;
		rf = rf->next;
	}

	rf = mount_files;
	if(!rf) {
		mount_files = malloc(sizeof(struct file_sql));
		mount_files->original_name = malloc(sizeof(char) * strlen(file_name) + 1);
		strcpy(mount_files->original_name, file_name);
		mount_files->table_name = NULL;
		if(table_name) {
			mount_files->table_name = malloc(sizeof(char) * strlen(table_name) + 1);
			strcpy(mount_files->table_name, table_name);
		}
		mount_files->next = NULL;
	} else {
		while(rf->next != NULL)
			rf = rf->next;
		rf_new = malloc(sizeof(struct file_sql));
		rf_new->original_name = malloc(sizeof(char) * strlen(file_name) + 1);
		strcpy(rf_new->original_name, file_name);
		rf_new->table_name = NULL;
		if(table_name) {
			rf_new->table_name = malloc(sizeof(char) * strlen(table_name) + 1);
			strcpy(rf_new->table_name, table_name);
		}
		rf_new->next = NULL;
		rf->next = rf_new;
	}
}

// campos que compoe a chave
void campos_chave(char *campos, struct fields_sql *fld, int level) {
	int tem;

	if(fld->size > 0 || fld->decimals > 0) { // field has no subfields
		strcat(campos, fld->name);
		return;
	}
	fld = fld->next;
	tem = 0;
	while(fld != NULL && fld->level > level) {
		if(tem)
			strcat(campos, ", ");
		if(fld->size > 0 || fld->decimals > 0) {
			strcat(campos, fld->name);
			tem = 1;
		}
		fld = fld->next;
	}
	return;
}

// read fd and select of archive and create an struct for use
int tcob_sql_read_def(char *fd, struct file_desc *f) {
	char *table, *path, *fname, linha[1025], linhar[1025], *nivel, *campo, *picture, *tamanho, *decimais;
	struct fd_sql *pfd;
	struct fd_sql *pfd_new;
	struct fields_sql *fld;
	struct fields_sql *fld_new;
	struct fields_sql *fld_aux;
	struct keys_sql *fdkey;
	struct keys_sql *fdkey_new;
	FILE *file;
	int def_campo, level, offset;
	struct altkey_desc *akd;

	if(sql_fdsql == NULL) {
#ifdef DEBUG_RTS
		fprintf(stderr, "Please, define the environment variable FdSQL\n");
#endif
		return 0;
	}
	path = malloc(sizeof(char) * 127); *path = 0;
	strcpy(path, sql_fdsql);
	strcat(path, "/");

	table = tcob_sql_table_name(fd);

	pfd = mount_sql; /* point to the begin of table */
	if(pfd == NULL) { // first table
		mount_sql = malloc(sizeof(struct fd_sql));
		mount_sql->sql_result = NULL;
		mount_sql->file = f;
		mount_sql->name = malloc(sizeof(char) * (strlen(table) + 1));
		strcpy(mount_sql->name, table);
		mount_sql->fields = NULL;
		mount_sql->keys = NULL;
		mount_sql->next = NULL;
		pfd = mount_sql;
	} else { // find
		while(pfd != NULL && strcmp(pfd->name, table)) // verify if the table is present
			pfd = pfd->next;
		if(pfd != NULL) { /* present */
			free_sql_db(pfd);
			return 1;
		}
		pfd = mount_sql;
		while(pfd->next != NULL) // search the end
			pfd = pfd->next;
		pfd_new = malloc(sizeof(struct fd_sql));
		pfd_new->sql_result = NULL;
		pfd_new->file = f;
		pfd_new->name = malloc(sizeof(char) * (strlen(table) + 1));
		strcpy(pfd_new->name, table);
		pfd_new->fields = NULL;
		pfd_new->keys = NULL;
		pfd_new->next = NULL;
		pfd->next = pfd_new;
		pfd = pfd_new;
	}

	fname = malloc(sizeof(char) * (strlen(path) + strlen(table) + 9)); *fname = 0;
	strcat(fname, path);
	strcat(fname, table);
	strcat(fname, ".sql_def");

	file = fopen(fname, "r");
	if(file == NULL) {
		if(!tcob_sql_read_fd(path, table, f)) {
			free(path);
			free(fname);
			return 0;
		}
		file = fopen(fname, "r");
	}

	fgets(linha, 1024, file);
	while(!feof(file)) {
		clean_read_line(linha, linhar, 1);
		if(strlen(linhar)) {
			def_campo = ((strncmp(linhar, ">key", 4) == 0)?0:1);
			if(def_campo) {
				nivel = NULL; campo = NULL;
				picture = NULL; tamanho = NULL;
				decimais = NULL;
				nivel = strtok(linhar, " ");
				campo = strtok(NULL, " ");
				picture = strtok(NULL, " ");
				tamanho = strtok(NULL, " ");
				decimais = strtok(NULL, " ");
				fld = pfd->fields;
				if(fld == NULL) {
					mount_fields = malloc(sizeof(struct fields_sql));
					mount_fields->alt = NULL;
					mount_fields->level = tcob_sql_int(nivel);
					mount_fields->name = malloc(sizeof(char) * (strlen(campo) + 1));
					strcpy(mount_fields->name, campo);
					mount_fields->type = *picture;
					mount_fields->size = tcob_sql_int(tamanho);
					mount_fields->decimals = tcob_sql_int(decimais);
					mount_fields->index = 0;
					mount_fields->unique = 0;
					mount_fields->next = NULL;
					pfd->fields = mount_fields;
				} else {
					while(fld->next != NULL)
						fld = fld->next;
					fld_new = malloc(sizeof(struct fields_sql));
					fld_new->alt = NULL;
					fld_new->level = tcob_sql_int(nivel);
					fld_new->name = malloc(sizeof(char) * (strlen(campo) + 1));
					strcpy(fld_new->name, campo);
					fld_new->type = *picture;
					fld_new->size = tcob_sql_int(tamanho);
					fld_new->decimals = tcob_sql_int(decimais);
					fld_new->index = 0;
					fld_new->unique = 0;
					fld_new->next = NULL;
					fld->next = fld_new;
				}
			} else {
				strtok(linhar, " ");
				campo = strtok(NULL, " ");
				picture = strtok(NULL, " "); // index
				tamanho = strtok(NULL, " "); // alternate
				fld = pfd->fields;
				offset = 0;
				while(fld != NULL && strcmp(fld->name, campo)) { // verify if the table is present
					fld = fld->next;
					offset += (fld->size + fld->decimals);
				}
				if(fld == NULL) /* not present */
					return 0;
				offset -= (fld->size + fld->decimals);
				for (akd = (struct altkey_desc *)(f+1); akd->offset != -1; akd++) { // who is alternate ?
					if(akd->offset == offset)
						fld->alt = akd;
				}
				level = fld->level;


				if(pfd->keys == NULL) {
					pfd->keys = malloc(sizeof(struct keys_sql));
					fdkey = pfd->keys;
					fdkey->next = NULL;
				} else {
					fdkey_new = malloc(sizeof(struct keys_sql));
					fdkey_new->next = NULL;
					fdkey->next = fdkey_new;
					fdkey = fdkey_new;
				}
				fdkey->name = malloc(sizeof(char) * strlen(campo) + 1);
				strcpy(fdkey->name, campo);
				fdkey->type = tcob_sql_int(picture) + tcob_sql_int(tamanho);
            fdkey->fields = malloc(sizeof(char) * 4096); *fdkey->fields = 0;
				fld_aux = fld;
				campos_chave(fdkey->fields, fld_aux, level);
				
				do {
					fld->index = tcob_sql_int(picture);
					fld->unique = tcob_sql_int(tamanho);
					fld = fld->next;
				} while(fld != NULL && fld->level > level);
			}
		}
		fgets(linha, 1024, file);
	}
	fclose(file);
	free(fname);
	free(path);
	return 1;
}

// return integer
int tcob_sql_int(char *campo) {
	int i, size;

	size = 0;
	for(i=0; i<strlen(campo); i++) {
		if(isdigit(campo[i])) {
			size *= 10;
			size += (campo[i] - 48);
		}
	}
	return size;
}

// Mount the table name
char * tcob_sql_table_name(char *fd) {
	struct file_sql *mf;

	mf = mount_files;
	while(mf != NULL && !strncmp(mf->original_name, fd, strlen(fd))==0) {
		mf = mf->next;
	}
	if(mf)
		return mf->table_name;
	 else
		return NULL;

}

// Le definicao do arquivo
int tcob_sql_read_fd(char *path, char *table, struct file_desc *f) {
	char *file_name_input, *file_name_output, linaux[1025], linha[1025], linhar[1025], *nivel, *campo, *picture, *tamanho, *decimais;
	FILE *file_input;
	FILE *file_output;
	int dup, nivelant, redefines, pic, filler;

	file_name_input  = malloc(sizeof(char) * (strlen(path) + strlen(table) + 5)); *file_name_input  = 0;
	file_name_output = malloc(sizeof(char) * (strlen(path) + strlen(table) + 9)); *file_name_output = 0;
	strcat(file_name_input, path);
	strcat(file_name_input, table);
	strcat(file_name_output, file_name_input);
	strcat(file_name_input, ".rg");
	strcat(file_name_output, ".sql_def");
			  
	if((file_input = fopen(file_name_input, "r")) == NULL) {
#ifdef DEBUG_RTS
		fprintf(stderr, "ERROR opening %s\n", file_name_input);
#endif
		free(file_name_input);
		free(file_name_output);
		return 0;
	}

	if((file_output = fopen(file_name_output, "w")) == NULL) {
#ifdef DEBUG_RTS
		fprintf(stderr, "ERROR creating %s\n", file_name_output);
#endif
		fclose(file_input);
		free(file_name_input);
		free(file_name_output);
		return 0;
	}

	redefines = 0;
	filler = 0;
	fgets(linha, 1024, file_input);
	while(!feof(file_input)) {
		while(!feof(file_input) && !strstr(linha, ".")) {
			fgets(linaux, 1024, file_input);
			strcat(linha, " ");
			strcat(linha, linaux);
		}
		clean_read_line(linha, linhar, 1);
		if(strlen(linhar) && (isdigit(linhar[0]) || linhar[0] == '>')) {
			if(strstr(linhar, " redefines "))
				redefines = 1;
			if(strstr(linhar, " occurs ") && !redefines) { // occurs in redefines, no problem
#ifdef DEBUG_RTS
				fprintf(stderr, "No support for occurs\n");
#endif
				fclose(file_input);
				fclose(file_output);
				free(file_name_input);
				free(file_name_output);
				return 0;
			}
			if(!strstr(linhar, " redefines ") && !strstr(linhar, " occurs ")) {
				nivel = NULL; campo = NULL;
				picture = NULL; tamanho = NULL;
				decimais = NULL;
				pic = (strstr(linhar, " pic ")?1:0);
				nivel = strtok(linhar, " ");
				campo = strtok(NULL, " ");
				if(strcmp(campo, "filler")==0)
					sprintf(campo, "filler%i", filler++);
				if(!redefines || (redefines && tcob_sql_int(nivel) <= nivelant)) {
					nivelant = tcob_sql_int(nivel);
					redefines = 0;
					if(pic) {
						strtok(NULL, " ");
						picture = strtok(NULL, "(");
						if(picture == "s9")
							picture = "s";
						tamanho = strtok(NULL, ")");
						strtok(NULL, "(");
						decimais = strtok(NULL, ")");
						fprintf(file_output,"%s,%s,%s,%s,%s\n",nivel,campo,picture,tamanho,decimais);
					} else
						fprintf(file_output,"%s,%s,0,0,0\n",nivel,campo);
				}
			}
		}
		fgets(linha, 1024, file_input);
	}
	fclose(file_input);
	strcpy(file_name_input, path);
	strcat(file_name_input, table);
	strcat(file_name_input, ".sel");
			  
	if((file_input = fopen(file_name_input, "r")) == NULL) {
		fclose(file_output);
#ifdef DEBUG_RTS
		fprintf(stderr, "ERROR opening %s\n", file_name_input);
#endif
		free(file_name_input);
		free(file_name_output);
		return 0;
	}

	fgets(linha, 1024, file_input);
	while(!feof(file_input)) {
		clean_read_line(linha, linhar, 1);
		if(strlen(linhar)) {
			if(strstr(linhar, "alternate record key ")) {
				dup = 0;
				if(strstr(linhar, "with duplicates"))
					dup = 1;
				strtok(linhar, " ");
				strtok(NULL, " ");
				strtok(NULL, " ");
				campo = strtok(NULL, " ");
				if(strcmp(campo, "is")==0)
					campo = strtok(NULL, " ");
				fprintf(file_output,">key %s,2,%i\n", campo, dup);
			} else {
				if(strstr(linhar, "record key ")) {
					strtok(linhar, " ");
					strtok(NULL, " ");
//					strtok(NULL, " ");
					campo = strtok(NULL, " ");
					if(strcmp(campo,"is")==0)
						campo = strtok(NULL, " ");
					fprintf(file_output,">key %s,1,0\n", campo);
				}
			}
		}
		fgets(linha, 1024, file_input);
	}

	fclose(file_input);
	fclose(file_output);
	free(file_name_input);
	free(file_name_output);
	return 1;
}

// clean the readed line
void clean_read_line(char *linha, char *linhar, int ponto) {
	int i, bg, ed;
	bg = 0; ed = 0;

	for(i=0; i<strlen(linha); i++) {
		linha[i] = tolower(linha[i]);
		if(ponto && linha[i] == '.')
			linha[i] = ' ';
		switch(linha[i]) {
			case '=':
			case '\'':
			case ',':
			case '\r':
			case '\n':
			case '\t':	linha[i] = ' ';
			case ' ':	if(bg > 1) {
								if(ed == 0) {
									ed = 1;
								linhar[bg++] = ' ';
								}
							}
							break;
			default:	if(linha[i] == '-') linha[i]='_';
						linhar[bg++] = linha[i];
						ed = 0;
						break;
		}
	}
	linhar[bg++] = 0;
	return;
}

// create verb
int create_sql_db(char *fd) {
	char *table, *query, bufs[10], bufd[10];
	int tem;
	struct fd_sql *pfd;
	struct fields_sql *fld;
	struct keys_sql *keys;

	table = tcob_sql_table_name(fd);
	pfd = mount_sql;
	while(pfd != NULL && strcmp(pfd->name, table))
		pfd = pfd->next;
	if(pfd == NULL) // no table
		return 0;
	fld = pfd->fields;
	if(fld == NULL) // no fields
		return 0;

	query = malloc(sizeof(char) * 4096); *query = 0;

	free_sql_db(pfd);

	strcpy(query, "drop table ");
	strcat(query, table);
	strcat(query, ";");
	query_sql_db(NULL, query, 0);

	strcpy(query, "create table ");
	strcat(query, table);
	strcat(query, " ( ");

	tem = 0;
	while(fld != NULL) {
		if(fld->size > 0 || fld->decimals > 0) {
			if(tem)
				strcat(query, ", ");
			strcat(query, fld->name);
			sprintf(bufs, "%i", fld->size);
			sprintf(bufd, "%i", fld->decimals);
			switch(fld->type) {
				case 'a':
				case 'x':	if((fld->size + fld->decimals) > 255)
									strcat(query, " text");
								 else {
									strcat(query, " char(");
									strcat(query, bufs);
									strcat(query, ")");
								}
								break;
				case 's':
				case '9':
#ifdef USE_MYSQL_GATEWAY
								if(fld->decimals)
									strcat(query, " float(");
								 else
									if(fld->size > 9)
										strcat(query, " bigint(");
									 else
										strcat(query, " int(");
								strcat(query, bufs);
								if(fld->decimals) {
									strcat(query, ",");
									strcat(query, bufd);
								}
#endif
#ifdef USE_PGSQL_GATEWAY
								strcat(query, " numeric(");
								strcat(query, bufs);
								if(fld->decimals) {
									strcat(query, ",");
									strcat(query, bufd);
								}
#endif
								strcat(query, ")");
								break;
			}
			tem = 1;
		}
		fld = fld->next;
	}
	strcat(query, " );");
//printf("%s\n",query); getchar();

// structure
	if(!query_sql_db(NULL, query, 0))
		return 0;

// monta chaves
	keys = pfd->keys;
	while(keys) {
		switch(keys->type) {
			case 1:	// primary
#ifdef USE_MYSQL_GATEWAY
				strcpy(query, "alter table ");
				strcat(query, table);
				strcat(query, " add primary key ( ");
				strcat(query, keys->fields);
				strcat(query, " );");
#endif
#ifdef USE_PGSQL_GATEWAY
				strcpy(query, "create unique index _");
				strcat(query, key->name);
				strcat(query, " on ");
				strcat(query, table);
				strcat(query, " ( ");
				strcat(query, keys->fields);
				strcat(query, " );");
#endif
				//printf("%s\n", query); getchar();
				if(!query_sql_db(NULL, query, 0))
					return 0;
				break;
			case 2:	// unique key
#ifdef USE_MYSQL_GATEWAY
				strcpy(query, "alter table ");
				strcat(query, table);
				strcat(query, " add unique _");
				strcat(query, keys->name);
				strcat(query, " ( ");
				strcat(query, keys->fields);
				strcat(query, " );");
#endif
#ifdef USE_PGSQL_GATEWAY
				strcpy(query, "create unique index _");
				strcat(query, key->name);
				strcat(query, " on ");
				strcat(query, table);
				strcat(query, " ( ");
				strcat(query, keys->fields);
				strcat(query, " );");
#endif
				//printf("%s\n", query); getchar();
				if(!query_sql_db(NULL, query, 0))
					return 0;
				break;
			case 3:	// alternate with duplicates	
#ifdef USE_MYSQL_GATEWAY
				strcpy(query, "alter table ");
				strcat(query, table);
				strcat(query, " add index _");
				strcat(query, keys->name);
				strcat(query, " ( ");
				strcat(query, keys->fields);
				strcat(query, " );");
#endif
#ifdef USE_PGSQL_GATEWAY
				strcpy(query, "create index _");
				strcat(query, keys->name);
				strcat(query, " on ");
				strcat(query, table);
				strcat(query, " ( ");
				strcat(query, keys->fields);
				strcat(query, " );");
#endif
				//printf("%s\n", query); getchar();
				if(!query_sql_db(NULL, query, 0))
					return 0;
				break;
		}
		keys = keys->next;
	}
//
	free(query);
	return 1;
}

// search for the table
struct fd_sql * search_table(struct file_desc *fd) {
	struct fd_sql *pfd;

	pfd = mount_sql;
	while(pfd != NULL && pfd->file != fd)
		pfd = pfd->next;
	return pfd;
}

// put value in query
int put_value_query(struct fields_sql *fld, char *record, int p, char *query) {
	struct fld_desc *porg, *pdst;
	char *org, *dst, *pnt;
	int s, piclen;
   char cDecimal = (bDecimalComma) ? ',' : '.';

	s = fld->size + fld->decimals; // total bytes

	// alphanumeric and unsigned integer
	if(fld->decimals == 0 && fld->type != 's') {
		if(*(record + p) == 0 && fld->type == '9')
			strcat(query, "0");
		 else
			strncat(query, (record + p), s);
		p += s;
		return p;
	}

	// signed numeric or with decimals
	porg = malloc(sizeof(struct fld_desc));
	pdst = malloc(sizeof(struct fld_desc));
	org = malloc(sizeof(char) * (s + 1));// *org = 0;
	dst = malloc(sizeof(char) * (s + 3));// *dst = 0;
	memset(org, 0, (s+1));
	memset(dst, 0, (s+3));

	porg->len = s;
	porg->type = DTYPE_DISPLAY;
	porg->pscale = 0;
	porg->decimals = fld->decimals;
	porg->pic = NULL;
	*pdst = *porg;
	pdst->type = DTYPE_EDITED;

	piclen = tcob_picReqLen(4);
	porg->pic = malloc(sizeof(char) * piclen);
	pdst->pic = malloc(sizeof(char) * piclen);
	tcob_picCreate(porg->pic, piclen, NULL);
	tcob_picCreate(pdst->pic, piclen, NULL);
	if(fld->type == 's') {
		tcob_picAppend(porg->pic, piclen, 'S', 1, NULL);
		tcob_picAppend(pdst->pic, piclen, '-', 1, NULL);
		pdst->len++;
	}
	if(fld->size) {
		tcob_picAppend(porg->pic, piclen, '9', fld->size, NULL);
		tcob_picAppend(pdst->pic, piclen, '9', fld->size, NULL);
	}
	if(fld->decimals) {
		tcob_picAppend(porg->pic, piclen, 'V', 1, '9', fld->decimals, NULL);
		tcob_picAppend(pdst->pic, piclen, cDecimal, 1, '9', fld->decimals, NULL);
		pdst->len++;
	}
	strncpy(org, (record + p), s);
	tcob_move(porg, org, pdst, dst);
	if(fld->decimals) { // change this after, remenber to see how to configure mysql or pgsql to accept "," in float fields
		pnt = dst;
		while(*pnt) {
			if(*pnt == ',')
				*pnt = '.';
			pnt++;
		}
	}
	strcat(query, dst);
	p += s;
	free(org);
	free(dst);
	free(porg->pic);
	free(pdst->pic);

	return p;
}

// insert data into table
int insert_sql_db(struct file_desc *fd, char *record) {
	struct fd_sql *pfd;
	struct fields_sql *fld;
	char *query;
	int tem, p;

	if(!(pfd = search_table(fd))) // no table
		return 0;
	if(!(fld = pfd->fields)) // no fields
		return 0;

	query = malloc(sizeof(char) * (strlen(record) + 4096)); *query = 0;
	strcpy(query, "insert into ");
	strcat(query, pfd->name);
	strcat(query, " ( ");
	fld = pfd->fields;
	tem = 0;
	while(fld != NULL) {
		if(fld->size > 0 || fld->decimals > 0) {
			if(tem)
				strcat(query, ", ");
			strcat(query, fld->name);
			tem = 1;
		}
		fld = fld->next;
	}
	strcat(query, " ) values (");

	fld = pfd->fields;
	tem = 0;
	p = 0;
	while(fld != NULL) {
		if(fld->size > 0 || fld->decimals > 0) {
			if(tem)
				strcat(query, "', ");

			strcat(query, "'");
			p = put_value_query(fld, record, p, query);
			tem = 1;
		}
		fld = fld->next;
	}
	strcat(query, "' );");
//printf("%s\n", query); getchar();

	if(!query_sql_db(pfd, query, 0)) {
		free(query);
		return 0;
	}

	free(query);

	return 1;
}

// start into table
int start_sql_db(struct file_desc *fd, char *record, int cond, char *key_ptr) {
	struct fd_sql *pfd;
	struct fields_sql *fld;
	struct fields_sql *xfld;
	char *query, comp[3], new_record[fd->reclen], *name_index;
	int level, tem, p;

	if(!(pfd = search_table(fd))) // no table
		return 0;
	if(!(fld = pfd->fields)) // no fields
		return 0;

	free_sql_db(pfd); // free results
	if(fd->start_record) { // free before
		free(fd->start_record);
		fd->start_record = NULL;
	}

	switch(cond) {
		case 1:	strcpy(comp, "="); break;
		case 2:	strcpy(comp, "<"); break;
		case 3:	strcpy(comp, "<="); break;
		case 4:	strcpy(comp, ">"); break;
		case 5:	strcpy(comp, ">="); break;
	}

	query = malloc(sizeof(char) * (strlen(record) + 4096)); *query = 0;
	name_index = malloc(sizeof(char) * 1024); *name_index = 0;
	strcpy(query, "select * from ");
	strcat(query, pfd->name);
	strcat(query, " where ");

	p = 0;
	if(pfd->file->key_in_use) // begin of alternate field
		while(fld != NULL && fld->alt != pfd->file->key_in_use) {
			p += (fld->size + fld->decimals);
			fld = fld->next;
		}

	level = 0;
	while(fld != NULL) { // index ?
		if(fld->index && !level)
			level = fld->level;
		if(fld->type != '0') // index ?
			break;
		 else
			fld = fld->next;
	}

	if(key_ptr) // start verb
		p = 0;

	tem = 0;
	do {
		if(fld->size > 0 || fld->decimals > 0) {
			if(tem) {
				strcat(query, "' && ");
				strcat(name_index, ", ");
			}
			strcat(name_index, fld->name);
			strcat(query, fld->name);
			strcat(query, " ");
			strcat(query, comp);
			switch(cond) {
				case 2:
				case 4:	xfld = fld->next;
							if(xfld != NULL && xfld->level > level) // exist another fild for key
								strcat(query,"=");
			}
			strcat(query, " '");
			if(key_ptr) // start verb
				p = put_value_query(fld, key_ptr, p, query);
          else
				p = put_value_query(fld, record, p, query);
			tem = 1;
		}
		fld = fld->next;
	} while(fld != NULL && fld->level > level);
   strcat(query, "' order by ");
	strcat(query, name_index);
	strcat(query, ";");
//	strcat(query, " limit 1;");
//printf("%s\n",query); getchar();
//mvaddstr(20,0,query); clrtobot(); refresh(); getch();

	if(!query_sql_db(pfd, query, 1)) {
		free(query);
		return 0;
	}

	free(query);

	pfd->row = 1;

	memset(new_record, 0, fd->reclen);
	if(key_ptr) {
		if(read_offset_db(pfd, new_record)) { // store in start_record
//			if(fd->start_record) { // free before
//				free(fd->start_record);
//				fd->start_record = NULL;
//			}
			fd->start_record = malloc(sizeof(char) * fd->reclen);
			memmove(fd->start_record, new_record, fd->reclen);
			pfd->row = 2;
		}
	}

	return 1;
}

// read from query result
int read_sql_db(struct file_desc *fd, char *record) {
	struct fd_sql *pfd;
	struct fields_sql *fld;

	if(!(pfd = search_table(fd))) // no table
		return 0;
	if(!(fld = pfd->fields)) // no fields
		return 0;

//	if(!pfd->sql_result)
	if(!start_sql_db(fd, record, 1, NULL)) // start the table
		return 0;

	if(!read_offset_db(pfd, record))
		return 0;

	return 1;
}

// read next from query result
int readnext_sql_db(struct file_desc *fd, char *record) {
	struct fd_sql *pfd;
	struct fields_sql *fld;

	if(!(pfd = search_table(fd))) // no table
		return 0;
	if(!(fld = pfd->fields)) // no fields
		return 0;

	if(!pfd->sql_result) // start greater than low-value (first record on table)
		memset(record, 0, (int) fd->reclen);

#ifdef USE_MYSQL_GATEWAY
   if(pfd->sql_result) {
		unsigned long long nrows;
		nrows = mysql_num_rows(pfd->sql_result);
		if((pfd->row == 1 && nrows == 1) || (pfd->row > nrows)) // no rows to read
			free_sql_db(pfd);
	}
#endif
#ifdef USE_PGSQL_GATEWAY
   if(pfd->sql_result && pfd->row >= PQntuples(pfd->sql_result)) // no rows to read
		free_sql_db(pfd);
#endif

	if(!pfd->sql_result)
	if(!start_sql_db(fd, record, 4, NULL)) // start the table
		return 0;

	memset(record, 0, fd->reclen);
	if(!read_offset_db(pfd, record))
		return 0;
	pfd->row++; // next row

	return 1;
}

// read previous from query result
int readprevious_sql_db(struct file_desc *fd, char *record) {
	struct fd_sql *pfd;
	struct fields_sql *fld;

	if(!(pfd = search_table(fd))) // no table
		return 0;
	if(!(fld = pfd->fields)) // no fields
		return 0;

	if(!pfd->sql_result) // no start before
		memset(record, 0, (int) fd->reclen);

   if(pfd->row == 0) // no rows to read
		free_sql_db(pfd);
//	free_sql_db(pfd);

	if(!pfd->sql_result) // no start before
	if(!start_sql_db(fd, record, 2, NULL)) // start the table
		return 0;

	memset(record, 0, fd->reclen);
	if(!read_offset_db(pfd, record))
		return 0;
	pfd->row--;
	
	return 1;
}

// read offset pointer by fld and put values in record
int read_offset_db(struct fd_sql *pfd, char *record) {
	struct fields_sql *fld;
	int p, i;
	unsigned long long offset;
#ifdef USE_MYSQL_GATEWAY
	MYSQL_ROW row;
#endif
#ifdef USE_PGSQL_GATEWAY
	char *row;
#endif

	if(!(fld = pfd->fields)) // no fields
		return 0;

	p = 0; i = 0; // move row to record
#ifdef USE_MYSQL_GATEWAY
	offset = (long long) pfd->row - 1;
	mysql_data_seek(pfd->sql_result, offset); // seek table
	row = mysql_fetch_row(pfd->sql_result); // fetch fields
	if(!row) // no rows, return with error
		return 0;
#endif
#ifdef USE_PGSQL_GATEWAY
	int nfield=0;
	int maxfield = PQnfields(pfd->sql_result);
	while (nfield < maxfield && fld != NULL) {
		row = PQgetvalue(pfd->sql_result, 0, nfield++);
#endif
		while(fld != NULL) {
			if(fld->size > 0 || fld->decimals > 0) {
#ifdef USE_MYSQL_GATEWAY
				put_value_record(fld, record, p, row[i++]);
#endif
#ifdef USE_PGSQL_GATEWAY
				put_value_record(fld, record, p, row);
#endif
				p += (fld->size + fld->decimals);
#ifdef USE_PGSQL_GATEWAY
				fld = fld->next;
				break;
#endif
			}
			fld = fld->next;
		}
#ifdef USE_PGSQL_GATEWAY
	}
#endif
	return 1;

}

// delete into table
int delete_sql_db(struct file_desc *fd, char *record) {
	struct fd_sql *pfd;
	struct fields_sql *fld;
	char *query;
	int level, tem, p;

	if(!(pfd = search_table(fd))) // no table
		return 0;
	if(!(fld = pfd->fields)) // no fields
		return 0;

	//free_sql_db(pfd); // free sql_result

	query = malloc(sizeof(char) * (strlen(record) + 4096)); *query = 0;
	strcpy(query, "delete from ");
	strcat(query, pfd->name);
	strcat(query, " where ");

	p = 0;
	level = 0;
	while(fld != NULL) { // index ?
		if(fld->index && !level)
			level = fld->level;
		if(fld->type != '0') // index ?
			break;
		 else
			fld = fld->next;
	}

	tem = 0;
	do {
		if(fld->size > 0 || fld->decimals > 0) {
			if(tem)
				strcat(query, "' && ");
			strcat(query, fld->name);
			strcat(query, " = '");
			p = put_value_query(fld, record, p, query);
			tem = 1;
		}
		fld = fld->next;
	} while(fld != NULL && fld->level > level);
   strcat(query, "';");
//printf("%s\n", query); getchar();

	if(!query_sql_db(pfd, query, 0)) {
		free(query);
		return 0;
	}

	free(query);

	return 1;
}

// insert data into table
int update_sql_db(struct file_desc *fd, char *record) {
	struct fd_sql *pfd;
	struct fields_sql *fld;
	char *query;
	int level, tem, p;

	if(!(pfd = search_table(fd))) // no table
		return 0;
	if(!(fld = pfd->fields)) // no fields
		return 0;

	query = malloc(sizeof(char) * (strlen(record) + 4096)); *query = 0;
	strcpy(query, "update ");
	strcat(query, pfd->name);
	strcat(query, " set ");
	fld = pfd->fields;
	tem = 0;
	p = 0;
	while(fld != NULL) {
		if(fld->size > 0 || fld->decimals > 0) {
			if(tem)
				strcat(query, "', ");
			strcat(query, fld->name);
			strcat(query, " = '");
			p = put_value_query(fld, record, p, query);
			tem = 1;
		}
		fld = fld->next;
	}
	strcat(query, "' where ");

	fld = pfd->fields;
	p = 0;
	level = 0;
	while(fld != NULL) { // index ?
		if(fld->index && !level)
			level = fld->level;
		if(fld->type != '0') // index ?
			break;
		 else
			fld = fld->next;
	}

	tem = 0;
	do {
		if(fld->size > 0 || fld->decimals > 0) {
			if(tem)
				strcat(query, "' && ");
			strcat(query, fld->name);
			strcat(query, " = '");
			p = put_value_query(fld, record, p, query);
			tem = 1;
		}
		fld = fld->next;
	} while(fld != NULL && fld->level > level);
	strcat(query, "' limit 1;");

	if(!query_sql_db(pfd, query, 0)) {
		free(query);
		return 0;
	}

	free(query);

	return 1;
}

// put value in record
void put_value_record(struct fields_sql *fld, char *record, int p, char *row) {
	struct fld_desc *porg, *pdst;
	char *dst, *str1;
	int s, piclen;
   char cDecimal = (bDecimalComma) ? ',' : '.';

	s = fld->size + fld->decimals; // total bytes

	// alphanumeric and unsigned integer
	if(fld->type == 'x') {
		memset((record + p), 32, s);
		memmove((record + p), row, strlen(row));
		return;
	}

	// signed numeric or with decimals
	porg = malloc(sizeof(struct fld_desc));
	pdst = malloc(sizeof(struct fld_desc));
	dst = malloc(sizeof(char) * (s + 3)); *dst = 0;

	porg->len = strlen(row);
	porg->type = DTYPE_ALPHANUMERIC;
	porg->decimals = 0;
	porg->pscale = 0;
	porg->all = 0;
	porg->just_r = 0;
	porg->separate_sign = 0;
	porg->leading_sign = 0;
	porg->blank = 0;
	porg->reserved = 0;
	porg->pic = NULL;
	*pdst = *porg;
	pdst->type = DTYPE_DISPLAY;
	pdst->len = s;
	pdst->decimals = fld->decimals;

	piclen = tcob_picReqLen(4);
	porg->pic = malloc(sizeof(char) * piclen);
	pdst->pic = malloc(sizeof(char) * piclen);
	tcob_picCreate(porg->pic, piclen, 'X', porg->len, NULL);
	tcob_picCreate(pdst->pic, piclen, NULL);
	if(fld->type == 's')
		tcob_picAppend(pdst->pic, piclen, 'S', 1, NULL);
	if(fld->size)
		tcob_picAppend(pdst->pic, piclen, '9', fld->size, NULL);
	if(fld->decimals)
		tcob_picAppend(pdst->pic, piclen, 'V', 1, '9', fld->decimals, NULL);

	switch(cDecimal) {
		case '.':	str1 = strstr(row, ",");
						break;
		case ',':	str1 = strstr(row, ".");
						break;
	}
	if(str1)
		*str1 = cDecimal;

	tcob_move(porg, row, pdst, dst);
	memcpy((record + p), dst, s);
	free(dst);
	free(porg->pic);
	free(pdst->pic);

	return;
}

// connect to sql data base
int connect_sql_db(void) {

	if(sql_conn != NULL) // already connect
		return 1;

#ifdef USE_MYSQL_GATEWAY
	sql_conn = (MYSQL *) malloc(sizeof(MYSQL));
	if(!mysql_init(sql_conn))
		return 0;
	if(!mysql_real_connect(sql_conn, sql_host, sql_user, sql_passwd, sql_db, 0, NULL, 0)) {
#ifdef DEBUG_RTS
		fprintf(stderr, "Conectando %s, %s, %s, %s\n", sql_host, sql_user, sql_passwd, sql_db);
		fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(sql_conn));
#endif
		return 0;
	}
#endif

#ifdef USE_PGSQL_GATEWAY
	int status;
	sql_conn = PQsetdbLogin(NULL, NULL, NULL, NULL, sql_db, sql_user, sql_passwd);
	status = PQstatus(sql_conn);
	if(status) {
#ifdef DEBUG_RTS
		fprintf(stderr, "Conectando %s, %s, %s, %s\n", sql_host, sql_user, sql_passwd, sql_db);
		fprintf(stderr, "Failed to connect to database: Error: %i\n", status);
#endif
		return 0;
	}
#endif

	return 1;
}

// execute the query command
int query_sql_db(struct fd_sql *pfd, char *query, int store_result) {

	if(sql_conn == NULL) // not connected, try first
		if(!connect_sql_db())
			return 0;

//mvaddstr(24, 0, query); clrtobot(); refresh(); getch();
#ifdef USE_MYSQL_GATEWAY
	if(mysql_query(sql_conn, query) != 0) {
#ifdef DEBUG_RTS
		fprintf(stderr, "Failed to query database: Error: %s\n", mysql_error(sql_conn));
#endif
//mvaddstr(20, 0, mysql_error(sql_conn));
//refresh(); getch();
		return 0;
	}
	if(pfd && store_result) { // use for select only
		if(!(pfd->sql_result = mysql_store_result(sql_conn))) { // store result
			free_sql_db(pfd);
			return 0;
		}
		if(mysql_num_rows(pfd->sql_result) == 0) { // no rows affected
			free_sql_db(pfd);
			return 0;
		}
	}
#endif

#ifdef USE_PGSQL_GATEWAY
	char *error_message;
	if(store_result)// {
		pfd->sql_result = PQexec(sql_conn, query);
//		status = PQresultStatus(pfd->sql_result);
//	} else {
	else
		PQexec(sql_conn, query);
//		status = PQstatus(sql_conn);
//	}
//	status = PQresultStatus(pfd->sql_result);
//	if(!store_result)
//		free_sql_db(pfd);
	error_message = PQerrorMessage(sql_conn);
//printf("query -> %s\nPqerrorMessage %s\n%i\n", query, error_message, *error_message);
	if(*error_message)
		return 0;
/*	switch(status) {
		case PGRES_EMPTY_QUERY:		printf("empty_query\n"); getchar(); break;
		case PGRES_COPY_OUT:			printf("copy_out\n"); getchar(); break;
		case PGRES_COPY_IN:			printf("copy_in\n"); getchar(); break;
		case PGRES_BAD_RESPONSE:	printf("bad_response\n"); getchar(); break;
		case PGRES_COMMAND_OK:		printf("comand_ok\n"); getchar(); break;
		case PGRES_TUPLES_OK:		printf("tuples_ok\n"); getchar(); break;
		case PGRES_NONFATAL_ERROR:	printf("nonfatal_error\n"); getchar(); break;
		case PGRES_FATAL_ERROR:		printf("fatal_error\n"); getchar(); break;
		default:							//fprintf(stderr, "Failed to query database: Error: %i\n", status);
											printf("sei la %i\n", status); getchar(); break;
											return 0;
											break;
	}*/
	if(pfd && store_result) { // use for select only
		if(!pfd->sql_result) { // store result
			free_sql_db(pfd);
			return 0;
		}
		if(PQntuples(pfd->sql_result) == 0) { // no rows affected
			free_sql_db(pfd);
			return 0;
		}
	}
#endif

	return 1;
}

// free sql_result
void free_sql_db(struct fd_sql *pfd) {

	if(!pfd) // no descriptor
		return;

	if(pfd->sql_result) // free sql_result before
#ifdef USE_MYSQL_GATEWAY
		mysql_free_result(pfd->sql_result);
#endif

#ifdef USE_PGSQL_GATEWAY
		PQclear(pfd->sql_result);
#endif

	pfd->sql_result = NULL;
	pfd->row = 0;

	return;

}

// close sql table
int close_sql_table(struct file_desc *f) {
	struct fd_sql *pfd=mount_sql;
	struct fd_sql *before=mount_sql;
	struct fd_sql *next=NULL;

	if(mount_sql)
		next = mount_sql->next;
	while(mount_sql != NULL && mount_sql->file != f) { // search for the file
		before = mount_sql;
		mount_sql = mount_sql->next;
		next = NULL;
		if(mount_sql)
			next = mount_sql->next;
	}
	if(!mount_sql || mount_sql->file != f) { // no pointer, return error
		mount_sql = pfd;
		return 0;
	}

	free_sql_db(mount_sql); // release sql_result

	free(mount_sql); // release the actual pointer

	if(mount_sql == pfd) // first was closed
		mount_sql = next;
	 else { // points to begin again
		mount_sql = before;
		mount_sql->next = next;
		mount_sql = pfd;
	}

	if(!mount_sql) // close connection if we don't have open "table"
		close_sql_db();

	return 1;
}

// close sql data base
int close_sql_db(void) {

#ifdef USE_MYSQL_GATEWAY
	mysql_close(sql_conn);
	free(sql_conn); // free memory
#endif

#ifdef USE_PGSQL_GATEWAY
	PQfinish(sql_conn);
#endif

	sql_conn = NULL; // force to point NULL

	return 1;
}
/* EOF filesql.c */