/**
Copyright 2000 - 2003 LogicMatrix. All rights reserved.
This software is distributed under the LogicMatrix Free Software License. This software may be used for any purpose, personal or commercial. Redistributions in binary /source code form are permitted. Commercial redistribution of larger works derived from, or works which bundle this software requires a "Commercial Redistribution License" which can be purchased from LogicMatrix. Contact LogicMatrix for details
Redistributions qualify as Free and non-commercial under one of the following terms:
1) Redistributions are made at no charge beyond the reasonable cost of materials and delivery.
2) Redistributions in binary/source code form must reproduce this Copyright Notice,these license terms, and the disclaimer/limitation of liability set forth as below, in the documentation and/or other materials
provided with the distribution.
Disclaimer
==========
The Software is provided on an "AS IS" basis. No warranty is
provided that the Software is free of defects, or fit for a
particular purpose.
Limitation of Liability
=======================
LogicMatrix shall not be liable for any damages suffered by the Licensee or any third party resulting from use of the Software.
**/
/**
sql_utils.cpp
Purpose: Defines some utility functions
Author: Vijay Mathew Pandyalakal
Date: 15/11/2003
Copyright: logicmatrix
**/
#include <string>
#include <vector>
#include <ctime>
using namespace std;
#include "datastore.h"
#include "mydate.h"
#include "mytime.h"
using namespace openutils;
#include "dsql_m_structs.h"
#include "bridge.h"
#include "autoid.h"
#include "sql_utils.h"
using namespace dsqlm;
#define MIN_SMALLINT -32768
#define MAX_SMALLINT 32767
#define MIN_INT (-2147483647 - 1)
#define MAX_INT 2147483647
#define MIN_BIGINT (-2147483647L - 1)
#define MAX_BIGINT 2147483647L
#define SMALLINT_SIZE 6
#define INT_SIZE 11
#define BIGINT_SIZE 11
#define FLOAT_SIZE 20 // 11 digits with 7 digit decimal precision
#define FLOAT_PREC 7
#define DOUBLE_SIZE 27 // 12 digits with 14 digit decimal precision
#define DOUBLE_PREC 14
#define DATE_SIZE 10
#define TIME_SIZE 8
#define TIMESTAMP_SIZE 20
#define UID_SIZE 14
#define CHAR_SIZE 1
#define AUTOID_SIZE 14
#define BOOL_SIZE 1
string SQLUtils::getTypeName(DsqlType type) {
if(type == SMALLINT) {
return "SMALLINT";
}else if(type == INT) {
return "INT";
}else if(type == BIGINT) {
return "BIGINT";
}else if(type ==FLOAT) {
return "FLOAT";
}else if(type == DOUBLE) {
return "DOUBLE";
}else if(type == CHAR) {
return "CHAR";
}else if(type ==VARCHAR) {
return "VARCHAR";
}else if(type == DATE) {
return "DATE";
}else if(type == TIME) {
return "TIME";
}else if(type == TIMESTAMP) {
return "TIMESTAMP";
}else if(type == BOOL){
return "BOOL";
}else if(type == AUTOID) {
return "AUTOID";
}else if(type == UID) {
return "UID";
}else {
throw DsqlMException("Unknown type");
}
}
void SQLUtils::setType(const char* type,Column& col) {
if(strcmpi(type,"INT") == 0) {
col.setType(INT);
col.setSize(INT_SIZE);
}else if(strcmpi(type,"SMALLINT") == 0) {
col.setType(SMALLINT);
col.setSize(SMALLINT_SIZE);
}else if(strcmpi(type,"BIGINT") == 0) {
col.setType(BIGINT);
col.setSize(BIGINT_SIZE);
}else if(strcmpi(type,"FLOAT") == 0) {
col.setType(FLOAT);
col.setSize(FLOAT_SIZE);
}else if(strcmpi(type,"DOUBLE") == 0) {
col.setType(DOUBLE);
col.setSize(DOUBLE_SIZE);
}else if(strcmpi(type,"CHAR") == 0) {
col.setType(CHAR);
col.setSize(CHAR_SIZE);
}else if(strcmpi(type,"VARCHAR") == 0) {
col.setType(VARCHAR);
col.setSize(CHAR_SIZE);
}else if(strcmpi(type,"DATE") == 0) {
col.setType(DATE);
col.setSize(DATE_SIZE);
}else if(strcmpi(type,"TIME") == 0) {
col.setType(TIME);
col.setSize(TIME_SIZE);
}else if(strcmpi(type,"TIMESTAMP") == 0) {
col.setType(TIMESTAMP);
col.setSize(TIMESTAMP_SIZE);
}else if(strcmpi(type,"BOOL") == 0) {
col.setType(BOOL);
col.setSize(BOOL_SIZE);
}else if(strcmpi(type,"AUTOID") == 0) {
col.setType(AUTOID);
col.setSize(AUTOID_SIZE);
}else if(strcmpi(type,"UID") == 0) {
col.setType(UID);
col.setSize(UID_SIZE);
}else {
char *err_buff = new char[strlen(type) + 30];
sprintf(err_buff,"Undefined type %s",type);
string tmp = err_buff;
delete[] err_buff;
throw DsqlMException(tmp.c_str());
}
}
int SQLUtils::getSize(DsqlType type) {
if(type == INT) {
return INT_SIZE;
}else if(type == SMALLINT) {
return SMALLINT_SIZE;
}else if(type == BIGINT) {
return BIGINT_SIZE;
}else if(type == FLOAT) {
return FLOAT_SIZE;
}else if(type == DOUBLE) {
return DOUBLE_SIZE;
}else if(type == CHAR) {
return -1;
}else if(type == VARCHAR) {
return -1;
}else if(type == DATE) {
return DATE_SIZE;
}else if(type == TIME) {
return TIME_SIZE;
}else if(type == TIMESTAMP) {
return TIMESTAMP_SIZE;
}else if(type == BOOL) {
return BOOL_SIZE;
}else if(type == AUTOID) {
return AUTOID_SIZE;
}else if(type == UID) {
return UID_SIZE;
}else {
throw DsqlMException("Invalid type for size check");
}
}
void SQLUtils::checkFieldData(const char* table,
Column col,const char* data,
char* buff) {
int sz = getSize(col.getType());
if(sz < 0) {
sz = col.getSize();
}
int len = strlen(data);
if(col.getType() == CHAR || col.getType() == VARCHAR
|| col.getType() == DATE || col.getType() == TIME) {
if(strcmpi(data,"NULL") != 0) {
if(data[0] != '\'' || data[len-1] != '\'') {
throw DsqlMException("String not terminated");
}
int j = 0;
for(int i=1;i<(len-1);i++) {
buff[j] = data[i];
j++;
}
buff[j] = '\0';
}
}
if(strcmpi(data,"NULL") == 0) {
if(col.isNullable() == FALSE) {
char err_buff[80];
sprintf(err_buff,"%s cannot be NULL",col.getTitle().c_str());
throw DsqlMException(err_buff);
}else {
return;
}
}
len = strlen(buff);
if(len > sz) {
char err_buff[80];
sprintf(err_buff,"%s - data too long",col.getTitle().c_str());
throw DsqlMException(err_buff);
}
if(col.getType() == BOOL) {
if(strcmpi(data,"true") == 0) {
strcpy(buff,"1");
}else if(strcmpi(data,"false") == 0) {
strcpy(buff,"0");
}else if((strcmp(data,"1") == 0 )|| (strcmp(data,"0") == 0)) {
strcpy(buff,data);
}else {
throw DsqlMException("Value not valid for bool");
}
}else if(col.getType() == INT || col.getType() == SMALLINT) {
int val = atoi(data);
if(col.getType() == INT) {
if(val > MAX_INT) {
throw DsqlMException("Value exceeds MAX_INT");
}
}else if(col.getType() == SMALLINT) {
if(val > MAX_SMALLINT) {
throw DsqlMException("Value exceeds MAX_SMALLINT");
}
}
sprintf(buff,"%d",val);
}else if(col.getType() == BIGINT) {
long val = atol(data);
if(val > MAX_BIGINT) {
throw DsqlMException("Value exceeds MAX_BIGINT");
}
sprintf(buff,"%d",val);
}else if(col.getType() == FLOAT) {
validateFloat(data);
float val = atof(data);
sprintf(buff,"%.7f",val);
}else if(col.getType() == DOUBLE) {
validateDouble(data);
double val = (double)atof(data);
sprintf(buff,"%.14f",val);
}else if(col.getType() == DATE) {
char tmp[16];
strcpy(tmp,buff);
if(strcmpi(tmp,"sysdate") == 0) {
openutils::MyDate date;
strcpy(tmp,date.getDate().c_str());
}
try {
openutils::MyDate date(tmp);
strcpy(tmp,date.getDate().c_str());
}catch(openutils::MyDateException ex) {
throw DsqlMException(ex.getMessage().c_str());
}
strcpy(buff,tmp);
}else if(col.getType() == UID) {
struct tm *newtime;
time_t long_time;
time(&long_time);
sprintf(buff,"%d",long_time);
}else if(col.getType() == TIMESTAMP) {
openutils::MyDate date;
openutils::MyTime time;
sprintf(buff,"%s,%s",date.getDate().c_str(),time.getTime().c_str());
}else if(col.getType() == TIME){
char tmp[10];
strcpy(tmp,buff);
if(strcmpi(tmp,"systime") == 0) {
openutils::MyTime time;
strcpy(tmp,time.getTime().c_str());
}
try {
openutils::MyTime time(tmp);
strcpy(tmp,time.getTime().c_str());
}catch(openutils::MyTimeException ex) {
throw DsqlMException(ex.getMessage().c_str());
}
strcpy(buff,tmp);
}else if(col.getType() == AUTOID) {
char filnm[101];
sprintf(filnm,"%s_%s_aid",table,col.getTitle().c_str());
DataStore<AutoId> ds(filnm,db->getDBName().c_str());
if(!ds.Open(true)) {
throw DsqlMException(filnm);
}
AutoId id;
long recs = ds.GetRecordCount();
if(recs > 0) {
id = ds.FindRecord(0);
id.setId(id.getId()+1);
ds.ModifyRecord(id,0);
}else {
id.setId(id.getId()+1);
ds.AddRecord(id);
}
ds.Close();
sprintf(buff,"%d",id.getId());
}
}
int SQLUtils::findPos(vector<string> vct,const char* srch) {
for(int i=0;i<vct.size();i++) {
if(strcmpi(vct[i].c_str(),srch) == 0) {
return i;
}
}
return -1;
}
ComparissonOperator SQLUtils::createComparissonOperator(const char* opr) {
if(strcmp(opr,"=") == 0) {
return EQUALS;
}else if(strcmp(opr,"<>") == 0) {
return NOT_EQUALS;
}else if(strcmp(opr,">=") == 0) {
return GREATER_THAN_OR_EQUAL_TO;
}else if(strcmp(opr,"<=") == 0) {
return LESSER_THAN_OR_EQUAL_TO;
}else if(strcmp(opr,">") == 0) {
return GREATER_THAN;
}else if(strcmp(opr,"<") == 0) {
return LESSER_THAN;
}else if(strcmpi(opr,"LIKE") == 0) {
return LIKE;
}else {
throw DsqlMException("Invalid comparison operator");
}
}
LogicalOperator SQLUtils::createLogicalOperator(const char* opr) {
if(strcmpi(opr,"AND") == 0) {
return AND;
}else if(strcmpi(opr,"OR") == 0) {
return OR;
}else {
throw DsqlMException("Invalid logical operator");
}
}
void SQLUtils::validateFloat(const char* data) {
int len = strlen(data);
if(len > FLOAT_SIZE) {
throw DsqlMException("Data too long");
}
int d = 0;
int p = 0;
bool p_found = false;
for(int i=0;i<len;i++) {
if(data[i] == '.') {
p_found = true;
continue;
}
if(p_found) {
p++;
}else {
d++;
}
}
if(d > 11) {
throw DsqlMException("Digits too large for float");
}
if(p > 7) {
throw DsqlMException("Precision too large for float");
}
}
void SQLUtils::validateDouble(const char* data) {
int len = strlen(data);
if(len > DOUBLE_SIZE) {
throw DsqlMException("Data too long");
}
int d = 0;
int p = 0;
bool p_found = false;
for(int i=0;i<len;i++) {
if(data[i] == '.') {
p_found = true;
continue;
}
if(p_found) {
p++;
}else {
d++;
}
}
if(d > 12) {
throw DsqlMException("Digits too large for double");
}
if(p > 14) {
throw DsqlMException("Precision too large for double");
}
}