/******************************************************************************/ #include "stdafx.h" #include "../../../ThirdPartyLibs/begin.h" #if SUPPORT_SQLITE #include "../../../ThirdPartyLibs/SQLite/sqlite3.h" #endif #if SUPPORT_ODBC #if !WINDOWS // not needed on Windows platform #define _IODBCUNIX_H // will disable some compilation errors on Unix #define UNICODE // will force SQL command names to be Unicode #define ULONG SQL_ULONG // will disable typedef conflict error on Apple #endif #if MAC #include "../../../ThirdPartyLibs/iODBC/sqlext.h" #else #include #endif #endif #include "../../../ThirdPartyLibs/end.h" namespace EE{ #define SQLITE_VFS_NAME "Esenthel" /******************************************************************************/ enum SQL_VAL_TYPE { SVT_RAW, SVT_STR, SVT_BIN, SVT_UID, }; /******************************************************************************/ SQLValues& SQLValues::New(C Str &name, Int value ) {_values.New().set(name, S+value ).type=SVT_RAW; return T;} SQLValues& SQLValues::New(C Str &name, Long value ) {_values.New().set(name, S+value ).type=SVT_RAW; return T;} SQLValues& SQLValues::New(C Str &name, Dbl value ) {_values.New().set(name, S+value ).type=SVT_RAW; return T;} SQLValues& SQLValues::New(C Str &name, C Str &value ) {_values.New().set(name, value ).type=SVT_STR; return T;} SQLValues& SQLValues::New(C Str &name, C DateTime &value ) {_values.New().set(name, S+'\''+value.asText(true)+'\'' ).type=SVT_RAW; return T;} SQLValues& SQLValues::New(C Str &name, CPtr value, Int value_size) {_values.New().set(name, TextHexMem( value, value_size , false)).type=SVT_BIN; return T;} SQLValues& SQLValues::New(C Str &name, File &file ) {_values.New().set(name, TextHexMem( file , false)).type=SVT_BIN; return T;} SQLValues& SQLValues::New(C Str &name, C UID &value ) {_values.New().set(name, TextHexMem(&value, SIZE(value), false)).type=SVT_UID; return T;} /******************************************************************************/ SQL::SQL() { _type=NONE; _env=_conn=_statement=_sqlite=null; _rows_pos=0; } SQL& SQL::del() { #if SUPPORT_ODBC if(_statement){ SQLFreeHandle(SQL_HANDLE_STMT, _statement); _statement=null;} if(_conn ){SQLDisconnect(_conn); SQLFreeHandle(SQL_HANDLE_DBC , _conn ); _conn =null;} if(_env ){ SQLFreeHandle(SQL_HANDLE_ENV , _env ); _env =null;} #endif #if SUPPORT_SQLITE if(_sqlite ){ sqlite3_close( (sqlite3*&)_sqlite ); _sqlite =null;} #endif _type=NONE; _rows .del(); _cols .del(); _params.del(); return T; } /******************************************************************************/ Bool SQL::connectODBC(C Str ¶ms, Str *messages, Int *error, Int sql_type) { #if SUPPORT_ODBC if(messages)messages->clear(); if(error )*error=0; del(); if(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &_env)==SQL_SUCCESS) if(SQLSetEnvAttr (_env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0)==SQL_SUCCESS) if(SQLAllocHandle(SQL_HANDLE_DBC, _env, &_conn)==SQL_SUCCESS) { T._type=sql_type; SQLWCHAR conn_str[1024]; if(CChar *p=params())switch(SQLDriverConnect(_conn, null, (SQLWCHAR*)p, SQL_NTS, conn_str, Elms(conn_str), null, SQL_DRIVER_NOPROMPT)) { case SQL_SUCCESS_WITH_INFO: getStatus(messages, error, false); break; case SQL_INVALID_HANDLE: case SQL_ERROR: getStatus(messages, error, false); del(); return false; } if(SQLAllocHandle(SQL_HANDLE_STMT, _conn, &_statement)==SQL_SUCCESS)return true; } #else if(messages)*messages="ODBC SQL is not supported on this platform"; if(error )*error=0; #endif del(); return false; } Bool SQL::connectODBC(C Str &server_name, C Str &database, C Str &user, C Str &password, Str *messages, Int *error, Int port, C Str &driver_name, Int sql_type) { #if SUPPORT_ODBC Str params=S+"DRIVER={"+driver_name+"};SERVER="+server_name+";DATABASE="+database+";"; switch(_type=sql_type) { case MSSQL: params+=S+"Trusted_Connection=Yes;UID="+user+";PWD="+password+";"; break; case MYSQL: params+=S+"User="+user+";Password="+password+";"; break; case PGSQL: params+=S+"Port="+port+";UID="+user+";PWD="+password+";"; break; } #else Str params; #endif return connectODBC(params, messages, error, sql_type); } Bool SQL::connectMSSQL(C Str &server_name, C Str &database, C Str &user, C Str &password, Str *messages, Int *error ) {return connectODBC(server_name, database, user, password, messages, error, 0, "SQL Server" , MSSQL);} Bool SQL::connectMySQL(C Str &server_name, C Str &database, C Str &user, C Str &password, Str *messages, Int *error, C Str &mysql_driver_name) {return connectODBC(server_name, database, user, password, messages, error, 0, mysql_driver_name, MYSQL);} Bool SQL::connectPgSQL(C Str &server_name, C Str &database, C Str &user, C Str &password, Str *messages, Int *error, Int port, C Str &pgsql_driver_name) {return connectODBC(server_name, database, user, password, messages, error, port, pgsql_driver_name, PGSQL);} Bool SQL::dsnConnectMSSQL(C Str &dsn, Str *messages, Int *error) {return connectODBC(S+"DSN="+dsn+";", messages, error, MSSQL);} Bool SQL::dsnConnectMySQL(C Str &dsn, Str *messages, Int *error) {return connectODBC(S+"DSN="+dsn+";", messages, error, MYSQL);} Bool SQL::dsnConnectPgSQL(C Str &dsn, Str *messages, Int *error) {return connectODBC(S+"DSN="+dsn+";", messages, error, PGSQL);} #if SUPPORT_SQLITE static SyncLock SQLiteCipherLock; static Cipher *SQLiteCipher; #endif Bool SQL::connectSQLite(C Str &database_file_name, const_mem_addr Cipher *cipher) { #if SUPPORT_SQLITE del(); Bool from_pak=(FExist(database_file_name) && !FExistSystem(database_file_name)); if(cipher // custom cipher needed || from_pak) // file exists only in project data, try to connect in read-only mode using callbacks { SyncLocker lock(SQLiteCipherLock); SQLiteCipher=cipher; if(sqlite3_open_v2(UTF8(database_file_name), &(sqlite3*&)_sqlite, from_pak ? SQLITE_OPEN_READONLY : SQLITE_OPEN_READWRITE, SQLITE_VFS_NAME)==SQLITE_OK){_type=SQLITE; return true;} // encode to UTF8 and later decode from it, because SQLite callbacks operate on char }else { if(sqlite3_open16(PLATFORM(database_file_name, UnixPath(database_file_name))(), &(sqlite3*&)_sqlite)==SQLITE_OK){_type=SQLITE; return true;} } #endif del(); return false; } /******************************************************************************/ Bool SQL::command(C Str &command, Str *messages, Int *error) { if(messages)messages->clear(); if(error )*error=0; switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: { SQLCloseCursor(_statement); // cursor needs to be closed if there was already a command executed (without closing it, executing new commands wouldn't succeed) if(CChar *c=command())if(SQLExecDirect(_statement, (SQLWCHAR*)c, SQL_NTS)!=SQL_SUCCESS) { getStatus(messages, error); return false; } }return true; #endif #if SUPPORT_SQLITE case SQLITE: { _rows_pos=-1; _rows.clear(); _cols.clear(); if(_sqlite) { SQLiteMutexEnter((sqlite3*&)_sqlite); sqlite3_stmt *stmt=null; Bool ok=(sqlite3_prepare16_v2((sqlite3*&)_sqlite, command(), -1, &stmt, null)==SQLITE_OK); if(ok && stmt) { _cols.setNum(sqlite3_column_count(stmt)); FREPA(_cols) { Col &col=_cols[i]; col.name=(CChar*)sqlite3_column_name16(stmt, i); col.type=SDT_UNKNOWN; } for(; sqlite3_step(stmt)==SQLITE_ROW; ) { Row &row=_rows.New(); row.cols.setNum(_cols.elms()); FREPA(_cols) { Col & col= _cols[i]; Row::Col &rcol=row.cols[i]; switch(sqlite3_column_type(stmt, i)) { case SQLITE_INTEGER: rcol.type=SDT_LONG ; break; case SQLITE_FLOAT : rcol.type=SDT_DBL ; break; case SQLITE_TEXT : rcol.type=SDT_STR ; break; case SQLITE_BLOB : rcol.type=SDT_BINARY ; break; default : rcol.type=SDT_UNKNOWN; break; } if(col.type==SDT_UNKNOWN)col.type=rcol.type; switch(rcol.type) { case SDT_LONG : rcol.i= sqlite3_column_int64 (stmt, i); break; case SDT_DBL : rcol.d= sqlite3_column_double(stmt, i); break; case SDT_STR : rcol.s=(CChar*)sqlite3_column_text16(stmt, i); break; case SDT_BINARY: { const Byte *src=(const Byte*)sqlite3_column_blob (stmt, i); // 'sqlite3_column_blob' must be called before 'sqlite3_column_bytes' rcol.b.setNum( sqlite3_column_bytes(stmt, i)); rcol.b.copyFrom(src); }break; } } } } if(stmt){ok&=(sqlite3_finalize(stmt)==SQLITE_OK); stmt=null;} if(!ok) { if(messages)*messages=(CChar*)sqlite3_errmsg16((sqlite3*&)_sqlite); if(error )*error = sqlite3_errcode ((sqlite3*&)_sqlite); } SQLiteMutexLeave((sqlite3*&)_sqlite); return ok; } }break; #endif } if(messages)*messages="SQL not connected"; return false; } /******************************************************************************/ Bool SQL::commandPrepare(C Str &command, Str *messages, Int *error) { if(messages)messages->clear(); if(error )*error=0; switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: { SQLCloseCursor(_statement); // cursor needs to be closed if there was already a command executed (without closing it, executing new commands wouldn't succeed) CChar *c=command(); if(SQLPrepare(_statement, (SQLWCHAR*)c, SQL_NTS)==SQL_SUCCESS)return true; getStatus(messages, error); }return false; #endif case SQLITE: if(messages)*messages="'commandPrepare' is currently not supported on SQLite"; return false; } if(messages)*messages="SQL not connected"; return false; } Bool SQL::commandParamRaw(Int i, CPtr value, Int size, Int c_type, Int sql_type) { switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: { Memc ¶m=_params(i); param.setNum(size); Copy(param.data(), value, size); return SQLBindParameter(_statement, i+1, SQL_PARAM_INPUT, c_type, sql_type, 0, 0, param.data(), 0, null)==SQL_SUCCESS; }break; #endif } return false; } Bool SQL::commandParamRaw2(Int i, CPtr value, Int size, Int c_type, Int sql_type) { switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: { Memc ¶m=_params(i); param.setNum(size+SIZE(SQLLEN)); Copy(param.data(), value, size); SQLLEN &length=*(SQLLEN*)(param.data()+size); length=size; return SQLBindParameter(_statement, i+1, SQL_PARAM_INPUT, c_type, sql_type, 0, 0, param.data(), 0, &length)==SQL_SUCCESS; }break; #endif } return false; } #if SUPPORT_ODBC SQL& SQL::commandParam(Int i, Bool value ) {commandParamRaw (i, &value , SIZE (value) , SQL_C_BIT , SQL_BIT ); return T;} SQL& SQL::commandParam(Int i, Int value ) {commandParamRaw (i, &value , SIZE (value) , SQL_C_SLONG , SQL_INTEGER ); return T;} SQL& SQL::commandParam(Int i, UInt value ) {commandParamRaw (i, &value , SIZE (value) , SQL_C_ULONG , SQL_INTEGER ); return T;} SQL& SQL::commandParam(Int i, Long value ) {commandParamRaw (i, &value , SIZE (value) , SQL_C_SBIGINT, SQL_BIGINT ); return T;} SQL& SQL::commandParam(Int i, ULong value ) {commandParamRaw (i, &value , SIZE (value) , SQL_C_UBIGINT, SQL_BIGINT ); return T;} SQL& SQL::commandParam(Int i, Flt value ) {commandParamRaw (i, &value , SIZE (value) , SQL_C_FLOAT , SQL_REAL ); return T;} SQL& SQL::commandParam(Int i, Dbl value ) {commandParamRaw (i, &value , SIZE (value) , SQL_C_DOUBLE , SQL_DOUBLE ); return T;} SQL& SQL::commandParam(Int i, CChar8 *value ) {commandParamRaw2(i, value , Length(value) , SQL_C_CHAR , SQL_VARCHAR ); return T;} SQL& SQL::commandParam(Int i, CChar *value ) {commandParamRaw2(i, value , Length(value) *2, SQL_C_WCHAR , SQL_WVARCHAR); return T;} SQL& SQL::commandParam(Int i, C Str8 &value ) {commandParamRaw2(i, value(), value.length() , SQL_C_CHAR , SQL_VARCHAR ); return T;} SQL& SQL::commandParam(Int i, C Str &value ) {commandParamRaw2(i, value(), value.length()*2, SQL_C_WCHAR , SQL_WVARCHAR); return T;} SQL& SQL::commandParam(Int i, CPtr value, Int size) {commandParamRaw2(i, value , size , SQL_C_BINARY , SQL_BINARY ); return T;} SQL& SQL::commandParam(Int i, C DateTime &value ) { switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: { TIMESTAMP_STRUCT ts; ts.year =value.year ; ts.month =value.month ; ts.day =value.day ; ts.hour =value.hour ; ts.minute =value.minute; ts.second =value.second; ts.fraction= 0; commandParamRaw(i, &ts, SIZE(ts), SQL_C_TIMESTAMP, SQL_TIMESTAMP); }break; #endif } return T; } SQL& SQL::commandParam(Int i, C DateTimeMs &value) { switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: { TIMESTAMP_STRUCT ts; ts.year =value.year ; ts.month =value.month ; ts.day =value.day ; ts.hour =value.hour ; ts.minute =value.minute; ts.second =value. second; ts.fraction=value.millisecond*1000000; // 1ms is 1,000,000 units according to - https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/c-data-types commandParamRaw(i, &ts, SIZE(ts), SQL_C_TIMESTAMP, SQL_TIMESTAMP); }break; #endif } return T; } #else SQL& SQL::commandParam(Int i, Bool value ) {return T;} SQL& SQL::commandParam(Int i, Int value ) {return T;} SQL& SQL::commandParam(Int i, UInt value ) {return T;} SQL& SQL::commandParam(Int i, Long value ) {return T;} SQL& SQL::commandParam(Int i, ULong value ) {return T;} SQL& SQL::commandParam(Int i, Flt value ) {return T;} SQL& SQL::commandParam(Int i, Dbl value ) {return T;} SQL& SQL::commandParam(Int i, CChar8 *value ) {return T;} SQL& SQL::commandParam(Int i, CChar *value ) {return T;} SQL& SQL::commandParam(Int i, C Str8 &value ) {return T;} SQL& SQL::commandParam(Int i, C Str &value ) {return T;} SQL& SQL::commandParam(Int i, CPtr value, Int size) {return T;} SQL& SQL::commandParam(Int i, C DateTime &value ) {return T;} SQL& SQL::commandParam(Int i, C DateTimeMs &value ) {return T;} #endif SQL& SQL::commandParam(Int i, C UID &value) { switch(_type) { #if SUPPORT_ODBC case MSSQL: case PGSQL: commandParamRaw(i, &value, SIZE(value), SQL_C_GUID, SQL_GUID); break; case MYSQL: commandParamRaw2(i, &value, SIZE(value), SQL_C_BINARY, SQL_BINARY); break; // MySQL does not support UID #endif } return T; } SQL& SQL::commandParam(Int i, C MemPtr &value) { switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: { Memc ¶m=_params(i); param.setNum(value.elms()+SIZE(SQLLEN)); value.copyTo(param.data()); SQLLEN &length=*(SQLLEN*)(param.data()+value.elms()); length=value.elms(); Bool ok=(SQLBindParameter(_statement, i+1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_BINARY, 0, 0, param.data(), 0, &length)==SQL_SUCCESS); }break; #endif } return T; } Bool SQL::commandExecute(Str *messages, Int *error) { if(messages)messages->clear(); if(error )*error=0; switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: { SQLCloseCursor(_statement); // cursor needs to be closed if there was already a command executed (without closing it, executing new commands wouldn't succeed) if(SQLExecute(_statement)==SQL_SUCCESS)return true; getStatus(messages, error); }return false; #endif case SQLITE: if(messages)*messages="'commandExecute' is currently not supported on SQLite"; return false; } if(messages)*messages="SQL not connected"; return false; } /******************************************************************************/ Bool SQL::getTables(MemPtr table_names, Str *messages, Int *error) { table_names.clear(); CChar8 *cmd=null; switch(_type) { default : if(messages)*messages="SQL not connected"; if(error)*error=0; return false; case MSSQL : cmd="SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES"; break; // "INFORMATION_SCHEMA.TABLES" has 4 columns //case MSSQL : cmd="SELECT name FROM sys.tables"; break; // "sys.tables" has 27 columns //case MSSQL : cmd="SELECT Name FROM SysObjects"; break; // "SysObjects" has 25 columns and returns more elements than it should, probably would require some type filtering? case MYSQL : cmd="SHOW TABLES"; break; case PGSQL : cmd="SELECT table_name FROM information_schema.tables WHERE table_schema='public'"; break; case SQLITE: cmd="SELECT name FROM sqlite_master WHERE type='table'"; break; } if(command(cmd, messages, error)) { for(; getNextRow(); )getCol(0, table_names.New()); return true; } return false; } Bool SQL::delTable(C Str &table_name, Str *messages, Int *error) { return command(S+"DROP TABLE "+token(table_name), messages, error); } Bool SQL::createTable(C Str &table_name, C MemPtr &columns, Str *messages, Int *error) { if(messages)messages->clear(); if(error )*error=0; if(!columns.elms()) { if(messages)*messages="Can't create an empty table"; return false; } Str cmd=S+"CREATE TABLE "+token(table_name)+" (", desc; FREPA(columns){if(i)cmd+=", "; if(!colDesc(columns[i], desc, messages))return false; cmd+=desc;} cmd+=')'; return command(cmd, messages, error); } Bool SQL::appendTable(C Str &table_name, C MemPtr &columns, Str *messages, Int *error) { if(messages)messages->clear(); if(error )*error=0; if(columns.elms()) { Str cmd=S+"ALTER TABLE "+token(table_name)+" ADD ", desc; FREPA(columns){if(i)cmd+=", "; if(!colDesc(columns[i], desc, messages))return false; cmd+=desc;} return command(cmd, messages, error); } return true; //if(del_cols.elms())cmd+="DROP COLUMN "; FREPA(del_cols){if(i)cmd+=", "; cmd+=S+'['+del_cols[i]+']';} //FREPA(modify_cols)cmd+=S+"ALTER TABLE "+token(table_name)+" ALTER COLUMN "+colDesc(modify_cols[i])+" ;\n"; } Bool SQL::existsTable(C Str &table_name, Str *messages, Int *error) { switch(_type) { default : if(messages)*messages="SQL not connected"; return false; case MSSQL : if(command(S+"SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=" +string(table_name), messages, error))return getNextRow(); break; //case MYSQL : if(command(S+"SHOW TABLES LIKE " +string(table_name), messages, error))return getNextRow(); break; // this fails for 'table_name' - "\'\\a" case MYSQL : return command(S+"SELECT 1 FROM "+token(table_name)+" LIMIT 1", messages, error); case PGSQL : if(command(S+"SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name=" +string(table_name), messages, error))return getNextRow(); break; case SQLITE: if(command(S+"SELECT name FROM sqlite_master WHERE type='table' AND name="+string(table_name), messages, error))return getNextRow(); break; } return false; } /******************************************************************************/ Bool SQL::delRow(C Str &table_name, C Str &condition, Str *messages, Int *error) { return command(S+"DELETE FROM "+token(table_name)+" WHERE "+condition, messages, error); } Bool SQL::newRow(C Str &table_name, C SQLValues &values, Str *messages, Int *error) { Str cmd=S+"INSERT INTO "+token(table_name)+" ("; FREPA(values._values){if(i)cmd+=", "; cmd+=token(values._values[i].name);} cmd+=") VALUES ("; FREPA(values._values){if(i)cmd+=", "; cmd+=value(values._values[i]);} cmd+=")"; return command(cmd, messages, error); } Bool SQL::setRow(C Str &table_name, C Str &condition, C SQLValues &values, Str *messages, Int *error) { Str cmd=S+"UPDATE "+token(table_name)+" SET "; FREPA(values._values){if(i)cmd+=", "; cmd+=token(values._values[i].name)+'='+value(values._values[i]);} cmd+=" WHERE "; cmd+=condition; return command(cmd, messages, error); } /******************************************************************************/ Bool SQL::getAllRows(C Str &table_name, Str *messages, Int *error) { return command(S+"SELECT * FROM "+token(table_name), messages, error); } Bool SQL::getRows(C Str &table_name, C Str &condition, Str *messages, Int *error) { if(!condition.is())return getAllRows(table_name, messages, error); return command(S+"SELECT * FROM "+token(table_name)+" WHERE "+condition, messages, error); } Bool SQL::getAllRowsCols(C Str &table_name, C MemPtr &columns, Str *messages, Int *error) { Str cmd="SELECT "; FREPA(columns){if(i)cmd+=", "; cmd+=token(columns[i]);} cmd+=S+" FROM "+token(table_name); return command(cmd, messages, error); } Bool SQL::getRowsCols(C Str &table_name, C Str &condition, C MemPtr &columns, Str *messages, Int *error) { if(!condition.is())return getAllRowsCols(table_name, columns, messages, error); Str cmd="SELECT "; FREPA(columns){if(i)cmd+=", "; cmd+=token(columns[i]);} cmd+=S+" FROM "+token(table_name)+" WHERE "; cmd+=condition; return command(cmd, messages, error); } /******************************************************************************/ Int SQL::getAllRowsNum(C Str &table_name, Str *messages, Int *error) { if(command(S+"SELECT COUNT(*) FROM "+token(table_name), messages, error))if(getNextRow()) { Int rows=0; getCol(0, rows); return rows; } return -1; } Int SQL::getRowsNum(C Str &table_name, C Str &condition, Str *messages, Int *error) { if(!condition.is())return getAllRowsNum(table_name, messages, error); if(command(S+"SELECT COUNT(*) FROM "+token(table_name)+" WHERE "+condition, messages, error))if(getNextRow()) { Int rows=0; getCol(0, rows); return rows; } return -1; } Int SQL::getUniqueValuesNum(C Str &table_name, C Str &column_name, Str *messages, Int *error) { if(command(S+"SELECT COUNT(DISTINCT "+token(column_name)+") FROM "+token(table_name), messages, error))if(getNextRow()) { Int rows=0; getCol(0, rows); return rows; } return -1; } /******************************************************************************/ Str SQL::string(C Str &s)C { switch(_type) { case MSSQL: { Str out="N'"; FREPA(s){Char c=s[i]; if(c=='\'')out+="''";else if(c=='\n')out+="'+CHAR(10)+N'";else if(c=='\r')out+="'+CHAR(13)+N'";else out+=c;} out+='\''; // "some text" -> "'some text'", "I'm a text" -> "'I''m a text'", (' must be replaced with '') return out; } case PGSQL: case SQLITE: { Str out='\''; FREPA(s){Char c=s[i]; if(c=='\'')out+="''";else out+=c;} out+='\''; // "some text" -> "'some text'", "I'm a text" -> "'I''m a text'", (' must be replaced with '') return out; } case MYSQL: return S+'\''+Replace(Replace(s, "\\", "\\\\"), "'", "\\'")+'\''; // replace \ into \\ and ' into \' default : return s; } } /******************************************************************************/ Bool SQL::getNextRow() { switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: return SQLFetch(_statement)==SQL_SUCCESS; #endif case SQLITE: { if(InRange(_rows_pos+1, _rows)){_rows_pos++; return true;} }return false; default: return false; } } Int SQL::getCols() { switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: {SQLSMALLINT cols=0; SQLNumResultCols(_statement, &cols); return cols;} #endif case SQLITE: return _cols.elms(); default: return 0; } } Bool SQL::getColDesc(Int i, Str &name, SQL_DATA_TYPE &type, Int &size) { switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: { SQLWCHAR temp[1024]; SQLSMALLINT t; SQLULEN s; if(SQLDescribeCol(_statement, i+1, temp, Elms(temp), null, &t, &s, null, null)==SQL_SUCCESS) { name=(Char*)temp; switch(t) { case SQL_CHAR : size=s ; type=SDT_STR8 ; break; case SQL_VARCHAR : size=s ; type=SDT_STR8 ; break; case SQL_LONGVARCHAR : size=-1 ; type=SDT_STR8 ; break; case SQL_WCHAR : size=s*2 ; type=SDT_STR ; break; case SQL_WVARCHAR : size=s*2 ; type=SDT_STR ; break; case SQL_WLONGVARCHAR : size=-1 ; type=SDT_STR ; break; case SQL_BIT : size=1 ; type=SDT_BOOL ; break; case SQL_TINYINT : size=1 ; type=SDT_BYTE ; break; case SQL_SMALLINT : size=2 ; type=SDT_SHORT ; break; case SQL_INTEGER : size=4 ; type=SDT_INT ; break; case SQL_BIGINT : size=8 ; type=SDT_LONG ; break; case SQL_REAL : size=4 ; type=SDT_FLT ; break; // SQL_REAL is C++ float case SQL_FLOAT : size=8 ; type=SDT_DBL ; break; // SQL_FLOAT is C++ double case SQL_DOUBLE : size=8 ; type=SDT_DBL ; break; // encountered in MySQL case SQL_GUID : size=16 ; type=SDT_UID ; break; case SQL_BINARY : size=s ; type=SDT_BINARY ; break; case SQL_VARBINARY : size=s ; type=SDT_BINARY ; break; case SQL_LONGVARBINARY : size=-1 ; type=SDT_BINARY ; break; case SQL_TYPE_TIMESTAMP: size=SIZE(DateTime); type=SDT_DATE_TIME; break; default : size=s ; type=SDT_UNKNOWN ; break; } return true; } }break; #endif case SQLITE: { if(InRange(i, _cols)) { Col &col=_cols[i]; name=col.name; switch(type=col.type) { default: size=-1; break; case SDT_INT : case SDT_LONG: size=SIZE(Long); break; case SDT_FLT: case SDT_DBL: size=SIZE(Dbl); break; } return true; } }break; } name.clear(); type=SDT_UNKNOWN; size=0; return false; } /******************************************************************************/ Bool SQL::getCol(Int i, Bool &value) { switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: if(SQLGetData(_statement, i+1, SQL_C_BIT, &value, SIZE(value), null)==SQL_SUCCESS)return true; break; #endif case SQLITE: if(InRange(i, _cols) && InRange(_rows_pos, _rows)) { C Row::Col &col=_rows[_rows_pos].cols[i]; switch( col.type) { case SDT_LONG : value= (col.i!=0); return true; case SDT_DBL : value= (col.d!=0); return true; case SDT_STR : value=TextBool(col.s ); return true; case SDT_BINARY: value=false; REPA(col.b)if(col.b[i]){value=true; break;} return true; } }break; } value=false; return false; } Bool SQL::getCol(Int i, Byte &value) { switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: if(SQLGetData(_statement, i+1, SQL_C_UTINYINT, &value, SIZE(value), null)==SQL_SUCCESS)return true; break; #endif case SQLITE: if(InRange(i, _cols) && InRange(_rows_pos, _rows)) { C Row::Col &col=_rows[_rows_pos].cols[i]; switch( col.type) { case SDT_LONG : value= col.i ; return true; case SDT_DBL : value= RoundU(col.d); return true; case SDT_STR : value=TextUInt(col.s); return true; case SDT_BINARY: value=(col.b.elms() ? col.b[0] : 0); return true; } }break; } value=0; return false; } Bool SQL::getCol(Int i, Int &value) { switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: if(SQLGetData(_statement, i+1, SQL_C_SLONG, &value, SIZE(value), null)==SQL_SUCCESS)return true; break; #endif case SQLITE: if(InRange(i, _cols) && InRange(_rows_pos, _rows)) { C Row::Col &col=_rows[_rows_pos].cols[i]; switch( col.type) { case SDT_LONG : value= col.i ; return true; case SDT_DBL : value= Round(col.d); return true; case SDT_STR : value=TextInt(col.s); return true; case SDT_BINARY: value=0; FREP(Min(col.b.elms(), SIZEI(value)))value|=(col.b[i]<<(i*8)); return true; } }break; } value=0; return false; } Bool SQL::getCol(Int i, UInt &value) { switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: if(SQLGetData(_statement, i+1, SQL_C_ULONG, &value, SIZE(value), null)==SQL_SUCCESS)return true; break; #endif case SQLITE: if(InRange(i, _cols) && InRange(_rows_pos, _rows)) { C Row::Col &col=_rows[_rows_pos].cols[i]; switch( col.type) { case SDT_LONG : value= col.i ; return true; case SDT_DBL : value= RoundU(col.d); return true; case SDT_STR : value=TextUInt(col.s); return true; case SDT_BINARY: value=0; FREP(Min(col.b.elms(), SIZEI(value)))value|=(col.b[i]<<(i*8)); return true; } }break; } value=0; return false; } Bool SQL::getCol(Int i, Long &value) { switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: if(SQLGetData(_statement, i+1, SQL_C_SBIGINT, &value, SIZE(value), null)==SQL_SUCCESS)return true; break; #endif case SQLITE: if(InRange(i, _cols) && InRange(_rows_pos, _rows)) { C Row::Col &col=_rows[_rows_pos].cols[i]; switch( col.type) { case SDT_LONG : value= col.i ; return true; case SDT_DBL : value= col.d ; return true; case SDT_STR : value=TextLong(col.s); return true; case SDT_BINARY: value=0; FREP(Min(col.b.elms(), SIZEI(value)))value|=(ULong(col.b[i])<<(i*8)); return true; } }break; } value=0; return false; } Bool SQL::getCol(Int i, ULong &value) { switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: if(SQLGetData(_statement, i+1, SQL_C_UBIGINT, &value, SIZE(value), null)==SQL_SUCCESS)return true; break; #endif case SQLITE: if(InRange(i, _cols) && InRange(_rows_pos, _rows)) { C Row::Col &col=_rows[_rows_pos].cols[i]; switch( col.type) { case SDT_LONG : value= col.i ; return true; case SDT_DBL : value= col.d ; return true; case SDT_STR : value=TextULong(col.s); return true; case SDT_BINARY: value=0; FREP(Min(col.b.elms(), SIZEI(value)))value|=(ULong(col.b[i])<<(i*8)); return true; } }break; } value=0; return false; } Bool SQL::getCol(Int i, Flt &value) { switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: if(SQLGetData(_statement, i+1, SQL_C_FLOAT, &value, SIZE(value), null)==SQL_SUCCESS)return true; break; #endif case SQLITE: if(InRange(i, _cols) && InRange(_rows_pos, _rows)) { C Row::Col &col=_rows[_rows_pos].cols[i]; switch( col.type) { case SDT_LONG : value= col.i ; return true; case SDT_DBL : value= col.d ; return true; case SDT_STR : value=TextFlt(col.s); return true; case SDT_BINARY: value=0; FREP(Min(col.b.elms(), SIZEI(value)))(U32&)value|=(col.b[i]<<(i*8)); return true; } }break; } value=0; return false; } Bool SQL::getCol(Int i, Dbl &value) { switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: if(SQLGetData(_statement, i+1, SQL_C_DOUBLE, &value, SIZE(value), null)==SQL_SUCCESS)return true; break; #endif case SQLITE: if(InRange(i, _cols) && InRange(_rows_pos, _rows)) { C Row::Col &col=_rows[_rows_pos].cols[i]; switch( col.type) { case SDT_LONG : value= col.i ; return true; case SDT_DBL : value= col.d ; return true; case SDT_STR : value=TextDbl(col.s); return true; case SDT_BINARY: value=0; FREP(Min(col.b.elms(), SIZEI(value)))(ULong&)value|=(ULong(col.b[i])<<(i*8)); return true; } }break; } value=0; return false; } Bool SQL::getCol(Int i, UID &value) { switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: if(SQLGetData(_statement, i+1, (_type==MYSQL) ? SQL_C_BINARY : SQL_C_GUID, &value, SIZE(value), null)==SQL_SUCCESS)return true; break; #endif case SQLITE: if(InRange(i, _cols) && InRange(_rows_pos, _rows)) { C Row::Col &col=_rows[_rows_pos].cols[i]; switch( col.type) { case SDT_LONG : value.set (col.i, 0); return true; case SDT_DBL : value.set (col.d, 0); return true; case SDT_STR : value.fromHex(col.s ); return true; case SDT_BINARY: value.zero(); REP(Min(col.b.elms(), Elms(value.b)))value.b[i]=col.b[i]; return true; } }break; } value.zero(); return false; } Bool SQL::getCol(Int i, Char *value, Int max_length) { switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: if(SQLGetData(_statement, i+1, SQL_C_WCHAR, value, max_length, null)==SQL_SUCCESS) { if(_type==PGSQL)ReplaceSelf(value, '\r', '\0'); // PostgreSql will add '\r' characters to new lines return true; }break; #endif case SQLITE: if(InRange(i, _cols) && InRange(_rows_pos, _rows)) { C Row::Col &col=_rows[_rows_pos].cols[i]; switch( col.type) { case SDT_LONG : Set(value, TextInt (col.i ), max_length); return true; case SDT_DBL : Set(value, TextDbl (col.d ), max_length); return true; case SDT_STR : Set(value, col.s , max_length); return true; case SDT_BINARY: Set(value, TextHexMem(col.b.data(), col.b.elms(), true), max_length); return true; } }break; } if(value && max_length>0)value[0]='\0'; return false; } Bool SQL::getCol(Int i, Char8 *value, Int max_length) { switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: if(SQLGetData(_statement, i+1, SQL_C_CHAR, value, max_length, null)==SQL_SUCCESS) { if(_type==PGSQL)ReplaceSelf(value, '\r', '\0'); // PostgreSql will add '\r' characters to new lines return true; }break; #endif case SQLITE: if(InRange(i, _cols) && InRange(_rows_pos, _rows)) { C Row::Col &col=_rows[_rows_pos].cols[i]; switch( col.type) { case SDT_LONG : Set(value, TextInt (col.i ), max_length); return true; case SDT_DBL : Set(value, TextDbl (col.d ), max_length); return true; case SDT_STR : Set(value, col.s , max_length); return true; case SDT_BINARY: Set(value, TextHexMem(col.b.data(), col.b.elms(), true), max_length); return true; } }break; } if(value && max_length>0)value[0]='\0'; return false; } Bool SQL::getCol(Int i, Str &value) { switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: { Char temp[1024]; SQLLEN len=0; switch(SQLGetData(_statement, i+1, SQL_C_WCHAR, temp, SIZE(temp), &len)) { case SQL_SUCCESS: { SQLSMALLINT type; if(_type==MYSQL && len==1 && (temp[0]==0 || temp[0]==1) && SQLDescribeCol(_statement, i+1, null, 0, null, &type, null, null, null)==SQL_SUCCESS_WITH_INFO)if(type==SQL_BIT){value=(temp[0] ? "1" : "0"); return true;} // MySQL returns incorrectly 0 or 1 for SQL_BIT instead of "0" or "1" if(len>0) // 'len' can be -1 for null { value=temp; if(_type==PGSQL)value.replace('\r', '\0'); // PostgreSql will add '\r' characters to new lines }else value.clear(); }return true; case SQL_SUCCESS_WITH_INFO: { if(len>0) { len/=2; value.clear().reserve(len); // make room for remaining data, call this before "value=temp" value=temp ; // copy what was already received value.reserve (len); // make room for remaining data, call this after "value=temp" (in case that would somehow make the buffer smaller) if(SQLGetData(_statement, i+1, SQL_C_WCHAR, (Ptr)(value()+value.length()), (value._d.elms()-value.length())*2, null)==SQL_SUCCESS) { value._length=Length(value()); if(_type==PGSQL)value.replace('\r', '\0'); // PostgreSql will add '\r' characters to new lines return true; } value._length=0; } }break; } }break; #endif case SQLITE: if(InRange(i, _cols) && InRange(_rows_pos, _rows)) { C Row::Col &col=_rows[_rows_pos].cols[i]; switch( col.type) { case SDT_LONG : value= col.i ; return true; case SDT_DBL : value= col.d ; return true; case SDT_STR : value= col.s ; return true; case SDT_BINARY: value=TextHexMem(col.b.data(), col.b.elms(), true); return true; } }break; } value.clear(); return false; } Bool SQL::getCol(Int i, Str8 &value) { switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: { Char8 temp[1024]; SQLLEN len=0; switch(SQLGetData(_statement, i+1, SQL_C_CHAR, temp, SIZE(temp), &len)) { case SQL_SUCCESS: { SQLSMALLINT type; if(_type==MYSQL && len==1 && (temp[0]==0 || temp[0]==1) && SQLDescribeCol(_statement, i+1, null, 0, null, &type, null, null, null)==SQL_SUCCESS_WITH_INFO)if(type==SQL_BIT){value=(temp[0] ? "1" : "0"); return true;} // MySQL returns incorrectly 0 or 1 for SQL_BIT instead of "0" or "1" if(len>0) // 'len' can be -1 for null { value=temp; if(_type==PGSQL)value.replace('\r', '\0'); // PostgreSql will add '\r' characters to new lines }else value.clear(); }return true; case SQL_SUCCESS_WITH_INFO: { if(len>0) { value.clear().reserve(len); // make room for remaining data, call this before "value=temp" value=temp ; // copy what was already received value.reserve (len); // make room for remaining data, call this after "value=temp" (in case that would somehow make the buffer smaller) if(SQLGetData(_statement, i+1, SQL_C_CHAR, (Ptr)(value()+value.length()), value._d.elms()-value.length(), null)==SQL_SUCCESS) { value._length=Length(value()); if(_type==PGSQL)value.replace('\r', '\0'); // PostgreSql will add '\r' characters to new lines return true; } value._length=0; } }break; } }break; #endif case SQLITE: if(InRange(i, _cols) && InRange(_rows_pos, _rows)) { C Row::Col &col=_rows[_rows_pos].cols[i]; switch( col.type) { case SDT_LONG : value= col.i ; return true; case SDT_DBL : value= col.d ; return true; case SDT_STR : value= col.s ; return true; case SDT_BINARY: value=TextHexMem(col.b.data(), col.b.elms(), true); return true; } }break; } value.clear(); return false; } Bool SQL::getCol(Int i, MemPtr value) { switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: { Byte temp[1024]; SQLLEN len=0; switch(SQLGetData(_statement, i+1, SQL_C_BINARY, temp, SIZE(temp), &len)) { case SQL_SUCCESS: if(len>0)value.setNum(len).copyFrom(temp);else value.clear(); return true; // 'len' can be -1 for null case SQL_SUCCESS_WITH_INFO: { if(len>0) { Memt full; full.setNum(len); CopyFast(full.data(), temp, SIZE(temp)); // copy what was already received if(SQLGetData(_statement, i+1, SQL_C_BINARY, full.data()+SIZE(temp), full.elms()-SIZE(temp), null)==SQL_SUCCESS) // get the remaining part { value.setNum(full.elms()).copyFrom(full.data()); // copy everything to destination return true; } } }break; } }break; #endif case SQLITE: if(InRange(i, _cols) && InRange(_rows_pos, _rows)) { C Row::Col &col=_rows[_rows_pos].cols[i]; switch( col.type) { case SDT_LONG : value.setNum(SIZE(col.i) ).copyFrom((Byte*)&col.i ); return true; case SDT_DBL : value.setNum(SIZE(col.d) ).copyFrom((Byte*)&col.d ); return true; case SDT_STR : value.setNum( (1+col.s.length())*SIZE(Char)).copyFrom((Byte*) col.s()); return true; case SDT_BINARY: value=col.b; return true; } }break; } value.clear(); return false; } Bool SQL::getCol(Int i, File &file) { Memt value; if(getCol(i, value))return file.put(value.data(), value.elms()); return false; } Bool SQL::getCol(Int i, DateTime &value) { switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: { TIMESTAMP_STRUCT ts; if(SQLGetData(_statement, i+1, SQL_C_TIMESTAMP, &ts, SIZE(ts), null)==SQL_SUCCESS) { value.year =ts.year ; value.month =ts.month ; value.day =ts.day ; value.hour =ts.hour ; value.minute=ts.minute; value.second=ts.second; return true; } }break; #endif case SQLITE: if(InRange(i, _cols) && InRange(_rows_pos, _rows)) { C Row::Col &col=_rows[_rows_pos].cols[i]; switch( col.type) { case SDT_STR : value.fromText(col.s); return true; case SDT_BINARY: { value.zero(); if(col.b.elms()>=1)value.second=col.b[0]; if(col.b.elms()>=2)value.minute=col.b[1]; if(col.b.elms()>=3)value.hour =col.b[2]; if(col.b.elms()>=4)value.day =col.b[3]; if(col.b.elms()>=5)value.month =col.b[4]; if(col.b.elms()>=6){Int ofs=5; FREP(Min(col.b.elms()-ofs, SIZEI(value.year)))value.year|=(col.b[ofs+i]<<(i*8));} }return true; } }break; } value.zero(); return false; } Bool SQL::getCol(Int i, DateTimeMs &value) { switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: { TIMESTAMP_STRUCT ts; if(SQLGetData(_statement, i+1, SQL_C_TIMESTAMP, &ts, SIZE(ts), null)==SQL_SUCCESS) { value.year =ts.year ; value.month =ts.month ; value.day =ts.day ; value.hour =ts.hour ; value.minute =ts.minute; value. second=ts.second; value.millisecond=ts.fraction/1000000; // 1ms is 1,000,000 units according to - https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/c-data-types return true; } }break; #endif case SQLITE: if(InRange(i, _cols) && InRange(_rows_pos, _rows)) { C Row::Col &col=_rows[_rows_pos].cols[i]; switch( col.type) { // TODO: 'millisecond' is ignored here case SDT_STR : value.millisecond=0; value.fromText(col.s); return true; case SDT_BINARY: { value.zero(); if(col.b.elms()>=1)value.second=col.b[0]; if(col.b.elms()>=2)value.minute=col.b[1]; if(col.b.elms()>=3)value.hour =col.b[2]; if(col.b.elms()>=4)value.day =col.b[3]; if(col.b.elms()>=5)value.month =col.b[4]; if(col.b.elms()>=6){Int ofs=5; FREP(Min(col.b.elms()-ofs, SIZEI(value.year)))value.year|=(col.b[ofs+i]<<(i*8));} }return true; } }break; } value.zero(); return false; } Bool SQL::getCol(Int i, Ptr value, Int &size) { if(value)switch(_type) { #if SUPPORT_ODBC case MSSQL: case MYSQL: case PGSQL: { SQLLEN len=0; if(SQLGetData(_statement, i+1, SQL_C_BINARY, value, size, &len)==SQL_SUCCESS) { size=len; return true; } }break; #endif case SQLITE: if(InRange(i, _cols) && InRange(_rows_pos, _rows)) { C Row::Col &col=_rows[_rows_pos].cols[i]; switch( col.type) { case SDT_LONG : MIN(size, SIZEI(col.i) ); CopyFast(value, &col.i , size); return true; case SDT_DBL : MIN(size, SIZEI(col.d) ); CopyFast(value, &col.d , size); return true; case SDT_STR : MIN(size, (1+col.s.length())*SIZEI(Char)); CopyFast(value, col.s (), size); return true; case SDT_BINARY: MIN(size, col.b.elms () ); CopyFast(value, col.b.data(), size); return true; } }break; } size=0; return false; } /******************************************************************************/ Str SQL::value(C SQLValues::Value &value)C { switch(value.type) { default: return value.value; // SVT_RAW case SVT_STR: return string(value.value); case SVT_BIN: bin: switch(_type) { case MSSQL : return S+"0x"+value.value; // 0x 0x01 0x0102 case MYSQL : return value.value.is() ? S+"0x"+value.value : "''"; // '' 0x01 0x0102 case PGSQL : return S+"E'\\\\x"+value.value+'\''; // E'\\x' E'\\x01' E'\\x0102' case SQLITE: return S+"x'"+value.value+'\''; // x'' x'01' x'0102' }break; case SVT_UID: switch(_type) { default : goto bin; case PGSQL: { Str temp; temp.reserve(32+4+2); temp='\''; // order of characters was specified to match the output in 'getCol(Int i, UID &value)' for(Int i= 8-2; i>= 0; i-=2){temp+=value.value[i]; temp+=value.value[i+1];} temp+='-'; for(Int i=12-2; i>= 8; i-=2){temp+=value.value[i]; temp+=value.value[i+1];} temp+='-'; for(Int i=16-2; i>=12; i-=2){temp+=value.value[i]; temp+=value.value[i+1];} temp+='-'; for(Int i=16 ; i< 20; i+=2){temp+=value.value[i]; temp+=value.value[i+1];} temp+='-'; for(Int i=20 ; i< 32; i+=2){temp+=value.value[i]; temp+=value.value[i+1];} temp+='\''; return temp; } }break; } return S; } Str SQL::token(C Str &token)C { switch(_type) { case MSSQL: return S+'['+Replace(token, "]", "]]")+']'; case MYSQL : case SQLITE: return S+'`'+Replace(token, "`", "``")+'`'; case PGSQL: return S+'"'+Replace(token, "\"", "\"\"")+'"'; default: return token; } } Bool SQL::colDesc(C SQLColumn &col, Str &desc, Str *messages) { desc=token(col.name)+' '; switch(_type) { default: if(messages)*messages="SQL not connected"; return false; case MSSQL: { switch(col.type) { case SDT_BOOL : desc+="BIT " ; break; case SDT_BYTE : desc+="TINYINT " ; break; case SDT_SHORT : desc+="SMALLINT " ; break; case SDT_INT : desc+="INT " ; break; case SDT_LONG : desc+="BIGINT " ; break; case SDT_FLT : desc+="REAL " ; break; case SDT_DBL : desc+="FLOAT " ; break; case SDT_UID : desc+="UNIQUEIDENTIFIER "; break; case SDT_DATE_TIME: desc+="DATETIME " ; break; case SDT_STR : desc+=((col. str_len <=0) ? "NVARCHAR(MAX) " : S+"NVARCHAR("+col. str_len +") "); break; case SDT_STR8 : desc+=((col. str_len <=0) ? "VARCHAR(MAX) " : S+ "VARCHAR("+col. str_len +") "); break; case SDT_BINARY : desc+=((col.binary_size<=0) ? "VARBINARY(MAX) " : S+ "BINARY("+col.binary_size+") "); break; default : if(messages)*messages=S+"Can't create table with column \""+col.name+"\" of UNKNOWN type"; return false; } switch(col.mode) { case SQLColumn::UNIQUE : desc+="UNIQUE "; break; case SQLColumn::PRIMARY : desc+="PRIMARY KEY "; break; case SQLColumn::PRIMARY_AUTO: desc+="PRIMARY KEY IDENTITY(0,1) "; break; } }break; case MYSQL: { switch(col.type) { case SDT_BOOL : desc+="BIT " ; break; case SDT_BYTE : desc+="TINYINT " ; break; case SDT_SHORT : desc+="SMALLINT " ; break; case SDT_INT : desc+="INT " ; break; case SDT_LONG : desc+="BIGINT " ; break; case SDT_FLT : desc+="FLOAT " ; break; case SDT_DBL : desc+="DOUBLE " ; break; case SDT_UID : desc+="BINARY(16) "; break; case SDT_DATE_TIME: desc+="DATETIME " ; break; case SDT_STR : desc+=((col. str_len <=0) ? "LONGTEXT CHARACTER SET utf8 " : S+"VARCHAR("+col. str_len +") CHARACTER SET utf8 "); break; case SDT_STR8 : desc+=((col. str_len <=0) ? "LONGTEXT " : S+"VARCHAR("+col. str_len +") " ); break; case SDT_BINARY : desc+=((col.binary_size<=0) ? "LONGBLOB " : S+ "BINARY("+col.binary_size+") " ); break; default : if(messages)*messages=S+"Can't create table with column \""+col.name+"\" of UNKNOWN type"; return false; } if(col.mode==SQLColumn::PRIMARY_AUTO)desc+="AUTO_INCREMENT "; }break; case PGSQL: { if(col.mode==SQLColumn::PRIMARY_AUTO)switch(col.type) { case SDT_BYTE : desc+=S+"smallserial PRIMARY KEY "; break; // PostgreSQL doesn't have a 'byte' type case SDT_SHORT: desc+=S+"smallserial PRIMARY KEY "; break; case SDT_INT : desc+=S+ "serial PRIMARY KEY "; break; case SDT_LONG : desc+=S+ "bigserial PRIMARY KEY "; break; default : if(messages)*messages=S+"Can't create table with column \""+col.name+"\" of invalid type for PRIMARY_AUTO"; return false; }else switch(col.type) { case SDT_BOOL : desc+="boolean " ; break; case SDT_BYTE : desc+="smallint " ; break; // PostgreSQL doesn't have a 'byte' type case SDT_SHORT : desc+="smallint " ; break; case SDT_INT : desc+="integer " ; break; case SDT_LONG : desc+="bigint " ; break; case SDT_FLT : desc+="real " ; break; case SDT_DBL : desc+="double precision "; break; case SDT_UID : desc+="uuid " ; break; case SDT_DATE_TIME: desc+="timestamp " ; break; case SDT_STR : desc+=((col.str_len<=0) ? "text " : S+"varchar("+col.str_len+") "); break; case SDT_STR8 : desc+=((col.str_len<=0) ? "text " : S+"varchar("+col.str_len+") "); break; case SDT_BINARY : desc+="bytea "; break; default : if(messages)*messages=S+"Can't create table with column \""+col.name+"\" of UNKNOWN type"; return false; } switch(col.mode) { case SQLColumn::PRIMARY: desc+=S+"PRIMARY KEY "; break; case SQLColumn::UNIQUE : desc+=S+"UNIQUE "; break; } }break; case SQLITE: { if(col.mode==SQLColumn::PRIMARY_AUTO)desc+="INTEGER PRIMARY KEY ";else // PRIMARY_AUTO must use INTEGER switch(col.type) { case SDT_BOOL : desc+="BIT " ; break; case SDT_BYTE : desc+="TINYINT " ; break; case SDT_SHORT : desc+="SMALLINT " ; break; case SDT_INT : desc+="INT " ; break; case SDT_LONG : desc+="BIGINT " ; break; case SDT_FLT : desc+="FLOAT " ; break; case SDT_DBL : desc+="DOUBLE " ; break; case SDT_UID : desc+="BINARY(16) "; break; case SDT_DATE_TIME: desc+="DATETIME " ; break; case SDT_STR : desc+=((col. str_len <=0) ? "TEXT " : S+"NVARCHAR("+col. str_len +") "); break; case SDT_STR8 : desc+=((col. str_len <=0) ? "TEXT " : S+ "VARCHAR("+col. str_len +") "); break; case SDT_BINARY : desc+=((col.binary_size<=0) ? "BLOB " : S+ "BINARY("+col.binary_size+") "); break; default : if(messages)*messages=S+"Can't create table with column \""+col.name+"\" of UNKNOWN type"; return false; } switch(col.mode) { case SQLColumn::UNIQUE : desc+="UNIQUE "; break; case SQLColumn::PRIMARY: desc+="PRIMARY KEY "; break; } }break; } desc+=(col.allow_nulls ? "NULL " : "NOT NULL "); if(col.default_val.is()) { if(col.type==SDT_STR || col.type==SDT_STR8) { desc+="DEFAULT "; desc+=string(col.default_val); }else { desc+="DEFAULT "; desc+=col.default_val; } } if(_type==MYSQL) { if(col.mode==SQLColumn::PRIMARY || col.mode==SQLColumn::PRIMARY_AUTO)desc+=S+", PRIMARY KEY ("+token(col.name )+")"; if(col.mode!=SQLColumn::DEFAULT )desc+=S+", UNIQUE INDEX "+token(col.name+"_UNIQUE")+" ("+token(col.name)+" ASC)"; } return true; } #if SUPPORT_ODBC void SQL::getStatus(Str *messages, Int *error, Bool statement) { if(messages || error) { if(messages)messages->clear(); if(error )*error=0; #if DEBUG SQLWCHAR text[1024], state[1024]; SQLINTEGER sql_error; if(SQLGetDiagRec(statement ? SQL_HANDLE_STMT : SQL_HANDLE_DBC, statement ? T._statement : T._conn, 1, state, &sql_error, text, Elms(text), null)==SQL_SUCCESS){if(messages)*messages=(Char*)text; if(error)*error=sql_error;} #else SQLWCHAR text[1024]; SQLINTEGER sql_error; if(SQLGetDiagRec(statement ? SQL_HANDLE_STMT : SQL_HANDLE_DBC, statement ? T._statement : T._conn, 1, null, &sql_error, text, Elms(text), null)==SQL_SUCCESS){if(messages)*messages=(Char*)text; if(error)*error=sql_error;} #endif } } #endif /******************************************************************************/ // SQLite VFS /******************************************************************************/ #if SUPPORT_SQLITE struct SQLiteFile : sqlite3_file // 'sqlite3_file' base must be first { File file; SQLiteFile() {Zero(SCAST(sqlite3_file, T));} }; static int SQLiteClose(sqlite3_file *pFile) { SQLiteFile &f=*(SQLiteFile*)pFile; int rc=(f.file.flush() ? SQLITE_OK : SQLITE_IOERR_WRITE); f.file.del(); DTOR(f); return rc; } static int SQLiteRead(sqlite3_file *pFile, void *zBuf, int iAmt, sqlite_int64 iOfst) { SQLiteFile &f=*(SQLiteFile*)pFile; if(!f.file.pos(iOfst))return SQLITE_IOERR_SEEK; // TODO: this could trigger data loss if flush failed, however how to handle this error? Int read=f.file.getReturnSize(zBuf, iAmt); if( read>=iAmt )return SQLITE_OK; // read all if( read<=0 && !f.file.end())return SQLITE_IOERR_READ; // read nothing but still have some left (not at the end) // partial read Zero((Byte*)zBuf+read, iAmt-read); // zero remaining data (this is required by SQLite) return SQLITE_IOERR_SHORT_READ; } static int SQLiteWrite(sqlite3_file *pFile, const void *zBuf, int iAmt, sqlite_int64 iOfst) { SQLiteFile &f=*(SQLiteFile*)pFile; if(!f.file.pos(iOfst))return SQLITE_IOERR_SEEK; // TODO: this could trigger data loss if flush failed, however how to handle this error? return f.file.put(zBuf, iAmt) ? SQLITE_OK : SQLITE_IOERR_WRITE; } static int SQLiteTruncate(sqlite3_file *pFile, sqlite_int64 size) { SQLiteFile &f=*(SQLiteFile*)pFile; return f.file.size(size) ? SQLITE_OK : SQLITE_IOERR_TRUNCATE; } static int SQLiteSync(sqlite3_file *pFile, int flags) { SQLiteFile &f=*(SQLiteFile*)pFile; if(!f.file.flush())return SQLITE_IOERR_WRITE; return f.file.sync() ? SQLITE_OK : SQLITE_IOERR_FSYNC; } static int SQLiteFileSize(sqlite3_file *pFile, sqlite_int64 *pSize) { SQLiteFile &f=*(SQLiteFile*)pFile; *pSize=f.file.size(); return SQLITE_OK; } static int SQLiteLock (sqlite3_file *pFile, int eLock) {return SQLITE_OK;} static int SQLiteUnlock (sqlite3_file *pFile, int eLock) {return SQLITE_OK;} static int SQLiteCheckReservedLock (sqlite3_file *pFile, int *pResOut) {*pResOut=0; return SQLITE_OK;} static int SQLiteFileControl (sqlite3_file *pFile, int op, void *pArg) {return SQLITE_NOTFOUND;} static int SQLiteSectorSize (sqlite3_file *pFile) {return 4096;} // use some default based on SQLite SQLITE_DEFAULT_SECTOR_SIZE static int SQLiteDeviceCharacteristics(sqlite3_file *pFile) {return (WINDOWS ? SQLITE_IOCAP_UNDELETABLE_WHEN_OPEN : 0);} static const sqlite3_io_methods SQLiteIO= { 1, /* iVersion */ SQLiteClose, /* xClose */ SQLiteRead, /* xRead */ SQLiteWrite, /* xWrite */ SQLiteTruncate, /* xTruncate */ SQLiteSync, /* xSync */ SQLiteFileSize, /* xFileSize */ SQLiteLock, /* xLock */ SQLiteUnlock, /* xUnlock */ SQLiteCheckReservedLock, /* xCheckReservedLock */ SQLiteFileControl, /* xFileControl */ SQLiteSectorSize, /* xSectorSize */ SQLiteDeviceCharacteristics, /* xDeviceCharacteristics */ }; static int SQLiteOpen(sqlite3_vfs *pVfs, const char *zName, sqlite3_file *pFile, int flags, int *pOutFlags) { if(pOutFlags)*pOutFlags=flags; SQLiteFile &f=*(SQLiteFile*)pFile; CTOR(f); f.pMethods=&SQLiteIO; Str name=FromUTF8(zName); if((flags&SQLITE_OPEN_READONLY) ? f.file. readTry(name, SQLiteCipher) : f.file.appendTry(name, SQLiteCipher))return SQLITE_OK; return SQLITE_CANTOPEN; // 'SQLiteClose' will be called upon 'SQLITE_CANTOPEN' } static int SQLiteDelete(sqlite3_vfs *pVfs, const char *zPath, int dirSync) { Str name=FromUTF8(zPath); return (!FExistSystem(name) || FDelFile(name)) ? SQLITE_OK : SQLITE_IOERR_DELETE; } static int SQLiteAccess(sqlite3_vfs *pVfs, const char *zPath, int flags, int *pResOut) { FileInfo fi; Bool exists=fi.get(FromUTF8(zPath)); switch(flags) { case SQLITE_ACCESS_EXISTS : *pResOut=(exists && fi.size>0 ); break; // SQLite requires check for size>0 case SQLITE_ACCESS_READ : *pResOut= exists ; break; case SQLITE_ACCESS_READWRITE: *pResOut=(exists && !(fi.attrib&FATTRIB_READ_ONLY)); break; default : return SQLITE_ERROR; } return SQLITE_OK; } static int SQLiteFullPathname(sqlite3_vfs *pVfs, const char *zPath, int nPathOut, char *zPathOut) { Set(zPathOut, zPath, nPathOut); // just copy return SQLITE_OK; } static void *SQLiteDlOpen (sqlite3_vfs *pVfs, const char *zPath) {return null;} static void SQLiteDlError(sqlite3_vfs *pVfs, int nByte, char *zErrMsg) {Set(zErrMsg, "Loadable extensions are not supported", nByte);} static void (*SQLiteDlSym (sqlite3_vfs *pVfs, void *pH, const char *z))(void) {return null;} static void SQLiteDlClose(sqlite3_vfs *pVfs, void *pHandle) {} /******************************************************************************/ static struct SQLiteInitializer // thanks to this class, we don't need to call a function such as 'InitSQL' (now removed), if we would call it from other CPP file, then we would always link with this CPP file, and forced inclusion of SQLite libraries which would increase the executable file size even if the app would not use it { static sqlite3_vfs SQLiteVFS; SQLiteInitializer() { sqlite3_initialize(); if(sqlite3_vfs *default_vfs=sqlite3_vfs_find(null)) { SQLiteVFS.xRandomness =default_vfs->xRandomness; SQLiteVFS.xSleep =default_vfs->xSleep; SQLiteVFS.xCurrentTime =default_vfs->xCurrentTime; SQLiteVFS.xGetLastError =null; SQLiteVFS.xCurrentTimeInt64=default_vfs->xCurrentTimeInt64; sqlite3_vfs_register(&SQLiteVFS, false); } } ~SQLiteInitializer() { sqlite3_vfs_unregister(&SQLiteVFS); //sqlite3_shutdown(); don't call this in case there are still open SQL connections, as the SQLite header says: "All open [database connections] must be closed and all other SQLite resources must be deallocated prior to invoking 'sqlite3_shutdown' } }SQLiteInit; // this object is needed to force calling constructor at the start of the Application sqlite3_vfs SQLiteInitializer::SQLiteVFS= { 2, /* iVersion */ SIZE(SQLiteFile), /* szOsFile */ MAX_UTF_PATH, /* mxPathname */ null, /* pNext */ SQLITE_VFS_NAME, /* zName */ null, /* pAppData */ SQLiteOpen, /* xOpen */ SQLiteDelete, /* xDelete */ SQLiteAccess, /* xAccess */ SQLiteFullPathname, /* xFullPathname */ SQLiteDlOpen, /* xDlOpen */ SQLiteDlError, /* xDlError */ SQLiteDlSym, /* xDlSym */ SQLiteDlClose, /* xDlClose */ }; #endif /******************************************************************************/ } /******************************************************************************/