2 public import static "ecere"
3 public import static "EDA"
15 static void UnusedFunction()
23 a.OnEdit(null,null,0,0,0,0,0);
24 a.OnDisplay(null,0,0,0,0,0,0);
25 a.OnGetDataFromString(null);
26 a.OnUnserialize(null);
31 extern int __ecereVMethodID_class_OnGetString;
32 extern int __ecereVMethodID_class_OnGetDataFromString;
33 extern int __ecereVMethodID_class_OnCompare;
34 extern int __ecereVMethodID_class_OnSerialize;
35 extern int __ecereVMethodID_class_OnUnserialize;
36 extern int __ecereVMethodID_class_OnFree;
39 int CollationCompare(Class type, int count1, void * data1, int count2, void * data2)
41 if(type.type == normalClass || type.type == noHeadClass)
43 Instance inst1, inst2;
45 SerialBuffer buffer1 { size = count1, count = count1, buffer = data1 };
46 SerialBuffer buffer2 { size = count2, count = count2, buffer = data2 };
48 type._vTbl[__ecereVMethodID_class_OnUnserialize](type, &inst1, buffer1);
49 type._vTbl[__ecereVMethodID_class_OnUnserialize](type, &inst2, buffer2);
51 result = type._vTbl[__ecereVMethodID_class_OnCompare](type, inst1, inst2);
53 buffer1.buffer = null;
54 buffer2.buffer = null;
61 else if(type.type == structClass)
63 void * inst1, * inst2;
65 SerialBuffer buffer1 { size = count1, count = count1, buffer = data1 };
66 SerialBuffer buffer2 { size = count2, count = count2, buffer = data2 };
68 inst1 = new0 byte[type.structSize];
69 inst2 = new0 byte[type.structSize];
70 type._vTbl[__ecereVMethodID_class_OnUnserialize](type, inst1, buffer1);
71 type._vTbl[__ecereVMethodID_class_OnUnserialize](type, inst2, buffer2);
73 result = type._vTbl[__ecereVMethodID_class_OnCompare](type, inst1, inst2);
75 buffer1.buffer = null;
76 buffer2.buffer = null;
84 return type._vTbl[__ecereVMethodID_class_OnCompare](type, data1, data2);
87 public class SQLiteStaticLink { } // Until .imp generation is fixed
89 class SQLiteDataSource : DataSourceDriver
91 class_property(name) = "SQLite";
93 OldList listDatabases;
96 String BuildLocator(DataSource ds)
98 return CopyString(ds.host);
101 uint GetDatabasesCount()
103 return databasesCount;
111 bool Connect(const String locator)
114 path = CopyString(locator);
115 // TODO, use user name and password for local security?
116 // TODO, open ds in read or write mode
120 FileListing listing { path, "sqlite" };
122 while(listing.Find())
129 bool RenameDatabase(const String name, const String rename)
131 if(name && rename && path && FileExists(path))
134 path = MakeDatabasePath(name);
139 repath = MakeDatabasePath(rename);
140 renamed = RenameFile(path, repath);
150 bool DeleteDatabase(const String name)
152 if(path && FileExists(path))
155 String path = MakeDatabasePath(name);
156 deleted = DeleteFile(path); // delete file seems to return true even if the file does not exist
164 virtual String MakeDatabasePath(const String name)
168 char build[MAX_LOCATION];
169 strcpy(build, path ? path : "");
170 PathCat(build, name);
171 ChangeExtension(build, "sqlite", build);
172 return CopyString(build);
177 Database OpenDatabase(const String name, CreateOptions createOptions, DataSource ds)
179 Database result = null;
182 String path = MakeDatabasePath(name);
185 // sqlite3_open(path, &db);
186 // sqlite3_open_v2(path, &db, SQLITE_OPEN_READONLY /*SQLITE_OPEN_READWRITE*/ /*SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE*/, null );
188 if(sqlite3_open_v2(path, &db, (createOptions == readOnly) ? SQLITE_OPEN_READONLY :
189 (SQLITE_OPEN_READWRITE | ((createOptions == create) ? SQLITE_OPEN_CREATE : 0)), null))
191 // fprintf(stderr, "%s\n", s); // interesting
192 printf($"EDASQLite: Can't open database (%s): %s\n", path, sqlite3_errmsg(db));
198 sprintf(command, "CREATE TABLE eda_table_fields(Table_Name TEXT, Name TEXT, Type TEXT, Length INT);");
199 sqlite3_exec(db, command, null, null, null);
201 result = SQLiteDatabase { db = db };
209 class SQLiteField : Field
214 public LinkElement<SQLiteField> link;
232 int GetLength() { return length; }
247 class SQLiteDatabase : Database
250 AVLTree<String> collations { };
257 uint ObjectsCount(ObjectType type)
263 bool RenameObject(ObjectType type, const String name, const String rename)
269 bool DeleteObject(ObjectType type, const String name)
275 Table OpenTable(const String name, OpenOptions options)
279 int nRows = 0, nCols = 0;
281 SQLiteTable table = null;
282 if(options.type == tablesList)
285 strcpy(command, "SELECT name FROM sqlite_master WHERE type='table' AND name!='eda_table_fields';");
286 table = SQLiteTable { db = this, specialStatement = CopyString(command) };
287 field = { tbl = table, name = CopyString("Name"), type = class(String), num = -1, sqliteType = SQLITE_TEXT };
290 table.fields.Add(field);
292 else if(options.type == fieldsList)
296 sprintf(command, "SELECT Name, Type, Length FROM eda_table_fields WHERE Table_Name='%s';", name);
297 table = SQLiteTable { db = this, specialStatement = CopyString(command) };
299 field = { tbl = table, name = CopyString("Name"), type = class(String), num = -1, sqliteType = SQLITE_TEXT };
301 table.fields.Add(field);
302 field = { tbl = table, name = CopyString("Type"), type = class(Class), num = 0, sqliteType = SQLITE_TEXT };
304 table.fields.Add(field);
305 field = { tbl = table, name = CopyString("Length"), type = class(int), num = 1, sqliteType = SQLITE_INTEGER };
307 table.fields.Add(field);
309 else if(options.type == tableRows)
311 bool addFields = false;
313 sprintf(command, "SELECT Name FROM eda_table_fields WHERE Table_Name='%s';", name);
314 result = sqlite3_get_table(db, command, &t, &nRows, &nCols, null);
318 sqlite3_free_table(t);
320 sprintf(command, "SELECT sql FROM sqlite_master WHERE type='table' AND name='%s';", name);
321 nCols = 0, nRows = 0;
322 result = sqlite3_get_table(db, command, &t, &nRows, &nCols, null);
324 if((nCols || nRows) || options.create)
326 table = SQLiteTable { db = this, name = CopyString(name) };
329 table.mustCreate = true;
335 for(r = 1; r <= nRows; r++) // There should be only 1 row here
337 char * sql = t[nCols * r];
338 char * bracket = strchr(sql, '(');
350 int sqliteType = SQLITE_BLOB;
351 Class type = class(int);
355 while((ch = bracket[c++]))
357 if(ch == ',' || ch == ')')
360 for(d = c-1; d >= 0 && bracket[d] != ' '; d--);
362 memcpy(fieldName, bracket + start, d - start);
363 fieldName[d - start] = 0;
365 memcpy(dataType, bracket + d + 1, c - d - 2);
366 dataType[c - d - 2] = 0;
368 while(ch && bracket[c] == ' ') c++;
370 if(!strcmp(dataType, "REAL")) { sqliteType = SQLITE_FLOAT; type = class(double); }
371 else if(!strcmp(dataType, "TEXT")) { sqliteType = SQLITE_TEXT; type = class(String); }
372 else if(!strcmp(dataType, "INTEGER")) { sqliteType = SQLITE_INTEGER; type = class(int); }
373 else if(!strcmp(dataType, "BLOB")) { sqliteType = SQLITE_BLOB; type = class(char *); } //class(byte *);
375 sprintf(command, "INSERT INTO eda_table_fields (Table_Name, Name, Type, Length) VALUES ('%s', '%s', '%s', %d);", name,
376 fieldName, type.name, 0);
377 result = sqlite3_exec(db, command, null, null, null);
380 SQLiteField field { tbl = table, name = CopyString(fieldName), type = type, num = table.fields.count, sqliteType = sqliteType };
382 table.fields.Add(field);
385 if(!ch || ch == ')') break;
392 sqlite3_stmt * statement;
394 sprintf(command, "SELECT Name, Type, Length FROM eda_table_fields WHERE Table_Name='%s';", name);
395 result = sqlite3_prepare_v2(db, command, -1, &statement, null);
397 while(sqlite3_step(statement) != SQLITE_DONE)
399 char * fieldName = sqlite3_column_text(statement, 0);
400 char * typeName = sqlite3_column_text(statement, 1);
401 int length = sqlite3_column_int(statement, 2);
403 int sqliteType = SQLITE_BLOB;
405 ((Class)(&type)).OnGetDataFromString(typeName); // TODO: THIS REQUIRES A FIX SOMEWHERE ELSE
409 if(!strcmp(type.dataTypeString, "int") || !strcmp(type.dataTypeString, "unsigned int") ||
410 !strcmp(type.dataTypeString, "long") || !strcmp(type.dataTypeString, "long int") ||
411 !strcmp(type.dataTypeString, "uint") || !strcmp(type.dataTypeString, "uint32") ||
412 !strcmp(type.dataTypeString, "int64") || !strcmp(type.dataTypeString, "unsigned int64") || !strcmp(type.dataTypeString, "uint64") ||
413 !strcmp(type.dataTypeString, "short") || !strcmp(type.dataTypeString, "unsigned short") || !strcmp(type.dataTypeString, "uint16") ||
414 !strcmp(type.dataTypeString, "char") || !strcmp(type.dataTypeString, "unsigned char") || !strcmp(type.dataTypeString, "byte"))
415 sqliteType = SQLITE_INTEGER;
416 else if(!strcmp(type.dataTypeString, "double") || !strcmp(type.dataTypeString, "float"))
417 sqliteType = SQLITE_FLOAT;
418 else if(!strcmp(type.dataTypeString, "String") || !strcmp(type.dataTypeString, "char *"))
419 sqliteType = SQLITE_TEXT;
422 if(strcmp(type.fullName, "CIString") && !collations.Find(type.fullName))
424 collations.Add(type.fullName);
425 sqlite3_create_collation_v2(table.db.db, type.fullName, SQLITE_UTF8, type, CollationCompare, null);
427 sqliteType = SQLITE_BLOB;
432 SQLiteField field { tbl = table, name = CopyString(fieldName), type = type, length = length, num = table.fields.count, sqliteType = sqliteType };
434 table.fields.Add(field);
437 sqlite3_finalize(statement);
441 sqlite3_free_table(t);
450 sprintf(command, "BEGIN;");
451 result = sqlite3_exec(db, command, null, null, null);
453 PrintLn($"BEGIN FAILED!");
454 return result == SQLITE_OK;
461 sprintf(command, "COMMIT;");
462 result = sqlite3_exec(db, command, null, null, null);
464 PrintLn($"COMMIT FAILED!");
465 return result == SQLITE_OK;
468 bool CreateCustomFunction(char * name, SQLCustomFunction customFunction)
470 int result = sqlite3_create_function(db, name, 1, SQLITE_UTF8, customFunction, SQLiteFunctionProcessor, null, null);
471 return result == SQLITE_OK;
475 void SQLiteFunctionProcessor(sqlite3_context* context, int n, sqlite3_value** value)
477 SQLCustomFunction sqlFunction = sqlite3_user_data(context);
478 char * text = sqlite3_value_text(*value);
479 sqlFunction.array.size = 1;
480 sqlFunction.array[0] = 0;
481 sqlFunction.Process(text);
482 sqlite3_result_text(context, sqlFunction.array.array, sqlFunction.array.count ? sqlFunction.array.count - 1 : 0, SQLITE_TRANSIENT);
485 class SQLiteTable : Table
490 LinkList<SQLiteField> fields { };
491 char * specialStatement;
492 SQLiteField primaryKey;
493 FieldIndex * indexFields;
494 int indexFieldsCount;
497 Field AddField(const String fieldName, Class type, int length)
504 Table refTable = null;
505 Field idField = null;
508 if(FindField(fieldName)) return null;
510 if(!strcmp(type.dataTypeString, "int") || !strcmp(type.dataTypeString, "unsigned int") ||
511 !strcmp(type.dataTypeString, "long") || !strcmp(type.dataTypeString, "long int") ||
512 !strcmp(type.dataTypeString, "uint") || !strcmp(type.dataTypeString, "uint32") ||
513 !strcmp(type.dataTypeString, "int64") || !strcmp(type.dataTypeString, "unsigned int64") || !strcmp(type.dataTypeString, "uint64") ||
514 !strcmp(type.dataTypeString, "short") || !strcmp(type.dataTypeString, "unsigned short") || !strcmp(type.dataTypeString, "uint16") ||
515 !strcmp(type.dataTypeString, "char") || !strcmp(type.dataTypeString, "unsigned char") || !strcmp(type.dataTypeString, "byte"))
517 strcpy(dataType, "INTEGER");
518 sqliteType = SQLITE_INTEGER;
520 else if(!strcmp(type.dataTypeString, "double") || !strcmp(type.dataTypeString, "float"))
522 strcpy(dataType, "REAL");
523 sqliteType = SQLITE_FLOAT;
525 else if(!strcmp(type.name, "CIString"))
527 strcpy(dataType, "TEXT");
528 sqliteType = SQLITE_BLOB;
530 else if(!strcmp(type.dataTypeString, "String") || !strcmp(type.dataTypeString, "char *"))
532 strcpy(dataType, "TEXT");
533 sqliteType = SQLITE_TEXT;
537 //strcpy(dataType, "BLOB");
538 strcpy(dataType, "TEXT");
539 sqliteType = SQLITE_BLOB;
541 if(!db.collations.Find(type.fullName))
543 db.collations.Add(type.fullName);
544 result = sqlite3_create_collation_v2(db.db, type.fullName, SQLITE_UTF8, type, CollationCompare, null);
547 if(sqliteType != SQLITE_BLOB && eClass_IsDerived(type, class(eda::Id)))
549 Table * table = (Table *)eClass_GetProperty(type, "table");
550 if(table) refTable = *table;
553 if(primaryKey || refTable != this)
555 for(idField = refTable.firstField; idField; idField = idField.next)
556 if(eClass_IsDerived(type, idField.type)) break;
559 PrintLn("WARNING: field not yet created for class ", (String)type.name);
562 idField = primaryKey;
566 PrintLn($"WARNING: Table not yet created for class ", (String)type.name);
572 if(sqliteType == SQLITE_BLOB)
574 if(!strcmp(type.name, "CIString"))
575 sprintf(command, "CREATE TABLE `%s`(%s %s COLLATE NOCASE);", name, fieldName, dataType);
577 sprintf(command, "CREATE TABLE `%s`(%s %s COLLATE '%s');", name, fieldName, dataType, type.fullName);
581 if(!idField && refTable == this)
582 sprintf(command, "CREATE TABLE `%s`(`%s` %s PRIMARY KEY);", name, fieldName, dataType);
584 sprintf(command, "CREATE TABLE `%s`(`%s` %s REFERENCES `%s`(`%s`));", name, fieldName, dataType, refTable.name, idField.name);
587 sprintf(command, "CREATE TABLE `%s`(`%s` %s);", name, fieldName, dataType);
588 result = sqlite3_exec(db.db, command, null, null, null);
589 if(result) return null;
594 if(sqliteType == SQLITE_BLOB)
596 if(!strcmp(type.name, "CIString"))
597 sprintf(command, "ALTER TABLE `%s` ADD `%s` %s COLLATE NOCASE;", name, fieldName, dataType);
599 sprintf(command, "ALTER TABLE `%s` ADD `%s` %s COLLATE `%s`;", name, fieldName, dataType, type.fullName);
603 if(!idField && refTable == this)
605 PrintLn($"WARNING: ALTER TABLE DOESN'T WORK WITH PRIMARY KEY FOR ", (String)name);
606 sprintf(command, "ALTER TABLE `%s` ADD `%s` %s PRIMARY KEY;", name, fieldName, dataType);
609 sprintf(command, "ALTER TABLE `%s` ADD `%s` %s REFERENCES `%s`(`%s`);", name, fieldName, dataType, refTable.name, idField.name);
612 sprintf(command, "ALTER TABLE `%s` ADD `%s` %s;", name, fieldName, dataType);
613 result = sqlite3_exec(db.db, command, null, null, null);
614 if(result) return null;
617 sprintf(command, "INSERT INTO eda_table_fields (Table_Name, Name, Type, Length) VALUES ('%s', '%s', '%s', %d);", name,
618 fieldName, type.name, length);
619 result = sqlite3_exec(db.db, command, null, null, null);
621 field = { name = CopyString(fieldName), type = type, num = fields.count, sqliteType = sqliteType };
624 if(!primaryKey && refTable == this)
629 Field FindField(const String name)
631 for(f : fields; !strcmp(f.name, name))
635 if(f.sqliteType != SQLITE_BLOB && eClass_IsDerived(f.type, class(eda::Id)))
638 Table * tablePtr = (Table *)eClass_GetProperty(f.type, "table");
639 if(tablePtr && *tablePtr == this)
648 bool GenerateIndex(int count, FieldIndex * fieldIndexes, bool init)
653 char indexName[4096];
656 indexFieldsCount = count;
657 indexFields = new FieldIndex[count];
658 memcpy(indexFields, fieldIndexes, count * sizeof(FieldIndex));
660 // TODO: USE CODED INDEX NAME INSTEAD?
661 strcpy(indexName, "index_");
662 strcat(indexName, name);
663 strcat(indexName, "_");
664 for(c = 0; c<count; c++)
666 if(fieldIndexes[c].field)
668 if(count == 1 && fieldIndexes[c].field == primaryKey)
670 strcat(indexName, fieldIndexes[c].field.name);
671 if(fieldIndexes[c].memberField)
673 strcat(indexName, ".");
674 strcat(indexName, fieldIndexes[c].memberField.name);
676 strcat(indexName, (fieldIndexes[c].order == ascending) ? "+" : "-");
682 sprintf(command, "CREATE INDEX IF NOT EXISTS `%s` ON `%s` (", indexName, name);
683 for(c = 0; c<count; c++)
685 char columnName[1024];
686 sprintf(columnName, "`%s` %s", fieldIndexes[c].field.name, (fieldIndexes[c].order == ascending) ? "ASC" : "DESC");
687 if(c > 0) strcat(command, ", ");
688 strcat(command, columnName);
690 strcat(command, ");");
691 result = sqlite3_exec(db.db, command, null, null, null);
693 return result == SQLITE_OK;
701 Field GetFirstField()
706 uint GetFieldsCount()
718 sprintf(command, "SELECT COUNT(*) FROM `%s`;", name);
719 result = sqlite3_get_table(db.db, command, &t, &nRows, &nCols, null);
720 if(result == SQLITE_OK)
722 rowCount = atoi(t[1]);
723 sqlite3_free_table(t);
728 // Returns true if not ordered by row ID
729 bool GetIndexOrder(char * fullOrder, bool flip)
731 if(!flip && (!indexFields || (indexFieldsCount == 1 && indexFields[0].field == primaryKey && indexFields[0].order == ascending)))
733 strcpy(fullOrder, " ORDER BY ROWID");
739 strcpy(fullOrder, " ORDER BY ");
740 for(c = flip ? indexFieldsCount-1 : 0; flip ? (c >= 0) : (c < indexFieldsCount); flip ? c-- : c++)
743 FieldIndex * fIndex = &indexFields[c];
745 if(c) strcat(order, ", ");
747 strcat(order, fIndex->field.name);
749 if(fIndex->order == (flip ? ascending : descending)) strcat(order, " DESC");
750 strcat(fullOrder, order);
756 DriverRow CreateRow()
759 sqlite3_stmt * statement;
760 sqlite3_stmt * sysIDStmt = null, * insertStmt = null, * deleteStmt = null, * selectRowIDsStmt = null, * setRowIDStmt = null;
761 sqlite3_stmt * prevStmt = null, * nextStmt = null, * lastStmt = null, * insertIDStmt = null;
764 strcpy(command, specialStatement);
768 /*sprintf(command, "SELECT ROWID, * FROM `%s` WHERE ? = ?;", name);
769 sqlite3_prepare_v2(db.db, command, -1, &findStmt, null);*/
770 sprintf(command, "SELECT ROWID, * FROM `%s` WHERE ROWID = ?;", name);
771 sqlite3_prepare_v2(db.db, command, -1, &sysIDStmt, null);
773 sprintf(command, "INSERT INTO `%s` DEFAULT VALUES;", name);
774 sqlite3_prepare_v2(db.db, command, -1, &insertStmt, null);
776 sprintf(command, "INSERT INTO `%s` (ROWID) VALUES(?);", name);
777 sqlite3_prepare_v2(db.db, command, -1, &insertIDStmt, null);
779 sprintf(command, "DELETE FROM `%s` WHERE ROWID = ?;", name);
780 sqlite3_prepare_v2(db.db, command, -1, &deleteStmt, null);
782 sprintf(command, "SELECT ROWID, * FROM `%s` WHERE ROWID < ? ORDER BY ROWID DESC LIMIT 1;", name);
783 sqlite3_prepare_v2(db.db, command, -1, &prevStmt, null);
785 sprintf(command, "SELECT ROWID, * FROM `%s` WHERE ROWID > ? ORDER BY ROWID LIMIT 1;", name);
786 sqlite3_prepare_v2(db.db, command, -1, &nextStmt, null);
788 sprintf(command, "SELECT MAX(ROWID), * FROM `%s`", name);
789 sqlite3_prepare_v2(db.db, command, -1, &lastStmt, null);
791 /*sprintf(command, "UPDATE `%s` SET ? = ? WHERE ROWID = ?;", name);
793 sqlite3_prepare_v2(db.db, command, -1, &updateStmt, null);*/
795 GetIndexOrder(order, false);
796 sprintf(command, "SELECT ROWID, * FROM `%s`%s;", name, order);
798 sqlite3_prepare_v2(db.db, command, -1, &statement, null);
800 sprintf(command, "SELECT ROWID FROM `%s` WHERE ROWID > ?", name);
801 sqlite3_prepare_v2(db.db, command, -1, &selectRowIDsStmt, null);
803 sprintf(command, "UPDATE `%s` SET ROWID = ? WHERE ROWID = ?", name);
804 sqlite3_prepare_v2(db.db, command, -1, &setRowIDStmt, null);
807 { tbl = this, defaultStatement = statement, curStatement = statement, sysIDStatement = sysIDStmt,
808 insertStatement = insertStmt, deleteStatement = deleteStmt, selectRowIDsStmt = selectRowIDsStmt, setRowIDStmt = setRowIDStmt,
809 previousStatement = prevStmt, nextStatement = nextStmt, lastStatement = lastStmt, insertIDStatement = insertIDStmt };
815 delete specialStatement;
821 class SQLiteRow : DriverRow
824 sqlite3_stmt * curStatement;
826 sqlite3_stmt * defaultStatement;
827 sqlite3_stmt * findStatement;
828 sqlite3_stmt * prevFindStatement, * lastFindStatement;
829 sqlite3_stmt * nextFindStatement;
830 sqlite3_stmt * sysIDStatement;
831 sqlite3_stmt * queryStatement;
832 sqlite3_stmt * selectRowIDsStmt;
833 sqlite3_stmt * setRowIDStmt;
834 sqlite3_stmt * lastStatement;
835 sqlite3_stmt * previousStatement;
836 sqlite3_stmt * nextStatement;
838 sqlite3_stmt * insertStatement;
839 sqlite3_stmt * deleteStatement;
840 sqlite3_stmt * updateStatement;
841 sqlite3_stmt * insertIDStatement;
845 // Because we use GoToSysID() and the sysIDStatement when searching for a primary key with Find(),
846 // this flag is used to distinguish between a Find() and a GoToSysID() for Select(next) purposes:
857 if(defaultStatement) sqlite3_finalize(defaultStatement);
858 if(findStatement) sqlite3_finalize(findStatement);
859 if(prevFindStatement)sqlite3_finalize(prevFindStatement);
860 if(lastFindStatement)sqlite3_finalize(lastFindStatement);
861 if(nextFindStatement)sqlite3_finalize(nextFindStatement);
862 if(sysIDStatement) sqlite3_finalize(sysIDStatement);
863 if(insertStatement) sqlite3_finalize(insertStatement);
864 if(deleteStatement) sqlite3_finalize(deleteStatement);
865 if(updateStatement) sqlite3_finalize(updateStatement);
866 if(queryStatement) sqlite3_finalize(queryStatement);
867 if(selectRowIDsStmt) sqlite3_finalize(selectRowIDsStmt);
868 if(setRowIDStmt) sqlite3_finalize(setRowIDStmt);
869 if(previousStatement)sqlite3_finalize(previousStatement);
870 if(nextStatement) sqlite3_finalize(nextStatement);
871 if(lastStatement) sqlite3_finalize(lastStatement);
872 if(insertIDStatement) sqlite3_finalize(insertIDStatement);
875 bool Select(MoveOptions move)
878 bool stepping = curStatement == previousStatement || curStatement == nextStatement || curStatement == lastStatement;
880 curStatement = defaultStatement;
885 sqlite3_reset(curStatement);
886 result = sqlite3_step(curStatement);
887 done = result == SQLITE_DONE || (result && result != SQLITE_ROW);
888 if(done) { rowID = 0; sqlite3_reset(curStatement); return false; }
889 rowID = sqlite3_column_int64(curStatement, 0);
894 sqlite3_reset(curStatement);
895 curStatement = lastStatement;
896 result = sqlite3_step(curStatement);
897 done = result == SQLITE_DONE || (result && result != SQLITE_ROW);
898 if(done) { rowID = 0; sqlite3_reset(curStatement); return false; }
899 rowID = sqlite3_column_int64(curStatement, 0);
907 // For sysID statement, for a Find() we want to go through next/previous in order, otherwise we just go to nil
908 if((move == next && curStatement != prevFindStatement && curStatement != lastFindStatement && !stepping && (curStatement != sysIDStatement || findSysID)) ||
909 (move == previous && (curStatement == prevFindStatement || curStatement == lastFindStatement)))
911 result = sqlite3_step(curStatement);
912 done = result == SQLITE_DONE || (result && result != SQLITE_ROW);
913 if(done) { rowID = 0; sqlite3_reset(curStatement); return false; }
914 rowID = sqlite3_column_int64(curStatement, 0);
916 else if(curStatement == prevFindStatement || curStatement == findStatement || curStatement == nextFindStatement || curStatement == lastFindStatement)
920 int bindId = findBindId;
921 sqlite3_reset((move == next) ? nextFindStatement : prevFindStatement);
922 BindCursorData((move == next) ? nextFindStatement : prevFindStatement, move,
923 (move == next && (!tbl.indexFields || (tbl.indexFieldsCount == 1 && tbl.indexFields[0].field == tbl.primaryKey && tbl.indexFields[0].order == ascending))) ? false : true, &bindId);
924 sqlite3_reset(curStatement);
925 curStatement = (move == next) ? nextFindStatement : prevFindStatement;
926 result = sqlite3_step(curStatement);
927 done = result == SQLITE_DONE || (result && result != SQLITE_ROW);
928 if(done) { rowID = 0; sqlite3_reset(curStatement); return false; }
929 rowID = sqlite3_column_int64(curStatement, 0);
933 int bindId = findBindId;
934 sqlite3_reset((move == next) ? findStatement : lastFindStatement);
935 sqlite3_reset(curStatement);
936 curStatement = (move == next) ? findStatement : lastFindStatement;
937 result = sqlite3_step(curStatement);
938 done = result == SQLITE_DONE || (result && result != SQLITE_ROW);
939 if(done) { rowID = 0; sqlite3_reset(curStatement); return false; }
940 rowID = sqlite3_column_int64(curStatement, 0);
945 sqlite3_reset(curStatement);
946 curStatement = (move == previous) ? (rowID ? previousStatement : lastStatement) : (rowID ? nextStatement : defaultStatement);
947 sqlite3_bind_int64(curStatement, 1, (sqlite3_int64)rowID);
948 result = sqlite3_step(curStatement);
949 done = result == SQLITE_DONE || (result && result != SQLITE_ROW);
950 if(done) { rowID = 0; sqlite3_reset(curStatement); return false; }
951 rowID = sqlite3_column_int64(curStatement, 0);
956 sqlite3_reset(curStatement);
966 bool Query(char * queryString)
972 sqlite3_reset(curStatement);
975 sqlite3_finalize(queryStatement);
976 queryStatement = null;
981 result = sqlite3_prepare_v2(tbl.db.db, queryString, -1, &queryStatement, null);
982 curStatement = queryStatement;
983 if(!strchr(queryString, '?'))
985 result = sqlite3_step(queryStatement);
987 done = result == SQLITE_DONE || (result && result != SQLITE_ROW);
988 if(done) { rowID = 0; sqlite3_reset(queryStatement); return false; }
990 rowID = sqlite3_column_int64(queryStatement, 0);
998 void BindData(sqlite3_stmt * statement, int pos, SQLiteField fld, typed_object data, SerialBuffer * bufferOut)
1000 Class dataType = fld.type;
1001 SerialBuffer buffer = null;
1002 switch(fld.sqliteType)
1004 case SQLITE_INTEGER:
1006 switch(dataType.typeSize)
1009 sqlite3_bind_int64(statement, pos, (sqlite3_int64)*(int64 *)data);
1012 sqlite3_bind_int(statement, pos, *(int *)data);
1018 value = (int)*(short *)data;
1020 value = (int)*(uint16 *)data;
1021 sqlite3_bind_int(statement, pos, value);
1028 value = (int)*(char *)data;
1030 value = (int)*(byte *)data;
1031 sqlite3_bind_int(statement, pos, value);
1039 if(dataType.typeSize == 8)
1040 sqlite3_bind_double(statement, pos, *(double *)data);
1042 sqlite3_bind_double(statement, pos, (double)*(float *)data);
1048 sqlite3_bind_text(statement, pos, (char *)data, strlen((char *)data), SQLITE_TRANSIENT);
1050 sqlite3_bind_text(statement, pos, null, 0, SQLITE_TRANSIENT);
1056 buffer = SerialBuffer { };
1057 dataType._vTbl[__ecereVMethodID_class_OnSerialize](dataType, data, buffer);
1058 sqlite3_bind_text(statement, pos, buffer._buffer, buffer.count, SQLITE_TRANSIENT);
1063 *bufferOut = buffer;
1068 void AddCursorWhereClauses(char * command, MoveOptions move, bool useIndex)
1070 if(move == next || move == previous)
1072 // Where clauses for index
1076 bool gotPrimaryKey = false;
1078 strcatf(command, " AND (");
1079 for(c = ((move == next) ? 0 : tbl.indexFieldsCount-1); (move == next) ? c < tbl.indexFieldsCount : c >= 0; (move == next) ? c++ : c--)
1082 FieldIndex * fIndex = &tbl.indexFields[c];
1086 strcat(where, fIndex->field.name);
1087 strcat(where, "` ");
1088 strcat(where, (fIndex->order == ((move == next) ? descending : ascending)) ? "<" : ">");
1089 strcat(where, " ? OR (");
1090 strcat(where, fIndex->field.name);
1091 if(fIndex->field == tbl.primaryKey)
1092 gotPrimaryKey = true;
1093 strcat(where, " = ? AND (");
1094 strcat(command, where);
1096 strcat(command, gotPrimaryKey ? "1)" : ((move == next) ? "ROWID > ?)" : "ROWID < ?)"));
1097 for(c = 0; c < tbl.indexFieldsCount; c++)
1098 strcat(command, "))");
1101 strcatf(command, (move == next) ? " AND ROWID > ?" : " AND ROWID < ?");
1105 void BindCursorData(sqlite3_stmt * stmt, MoveOptions move, bool useIndex, int * bindId)
1107 if(move == next || move == previous)
1109 // The binds for the Extra ordering Where clauses
1113 for(c = ((move == next) ? 0 : tbl.indexFieldsCount-1); (move == next) ? c < tbl.indexFieldsCount : c >= 0; (move == next) ? c++ : c--)
1115 FieldIndex * fIndex = &tbl.indexFields[c];
1117 SQLiteField fld = (SQLiteField)fIndex->field;
1118 Class type = fld.type;
1120 SerialBuffer buffer;
1122 if(type.type == unitClass && !type.typeSize)
1124 Class dataType = eSystem_FindClass(type.module, type.dataTypeString);
1128 if(type.type == structClass)
1130 data = (int64)new0 byte[type.structSize];
1131 dataPtr = (void *) data;
1133 ((bool (*)())(void *)this.GetData)(this, fld, type, (type.type == structClass) ? (void *)data : &data);
1134 if(type.type == normalClass || type.type == noHeadClass)
1135 dataPtr = (void *) data;
1138 ((void (*)())(void *)this.BindData)(this, stmt, (*bindId)++, fld, type, dataPtr, &buffer);
1139 // Reuse the buffer for Blobs...
1140 if(fld.sqliteType == SQLITE_BLOB || fld.sqliteType == SQLITE_NULL)
1142 sqlite3_bind_text(stmt, (*bindId)++, buffer._buffer, buffer.count, SQLITE_TRANSIENT);
1146 ((void (*)())(void *)this.BindData)(this, stmt, (*bindId)++, fld, type, dataPtr, null);
1148 type._vTbl[__ecereVMethodID_class_OnFree](type, dataPtr);
1152 // Bind for the rowid
1153 sqlite3_bind_int64(stmt, (*bindId)++, (sqlite3_int64)rowID);
1157 bool Find(Field fld, MoveOptions move, MatchOptions match, typed_object data)
1159 char order[1024], command[2048];
1162 sqlite3_stmt * stmt = null;
1165 if(curStatement) { sqlite3_reset(curStatement); curStatement = null; }
1166 if(findStatement) { sqlite3_finalize(findStatement); findStatement = null; }
1167 if(nextFindStatement) { sqlite3_finalize(nextFindStatement); nextFindStatement = null; }
1168 if(prevFindStatement) { sqlite3_finalize(prevFindStatement); prevFindStatement = null; }
1169 if(lastFindStatement) { sqlite3_finalize(lastFindStatement); lastFindStatement = null; }
1171 if(fld == tbl.primaryKey)
1173 result = GoToSysID(*(int *)data);
1179 useIndex = tbl.GetIndexOrder(order, false);
1181 sprintf(command, "SELECT ROWID, * FROM `%s` WHERE `%s` = ?", tbl.name, fld.name);
1182 AddCursorWhereClauses(command, move, useIndex);
1183 strcat(command, order);
1184 strcat(command, ";");
1185 result = sqlite3_prepare_v2(tbl.db.db, command, -1, &stmt, null);
1186 BindData(stmt, bindId++, (SQLiteField)fld, data, null);
1187 BindCursorData(stmt, move, useIndex, &bindId);
1188 curStatement = findStatement = stmt;
1189 findBindId = bindId;
1191 // For going back to forward find
1193 sprintf(command, "SELECT ROWID, * FROM `%s` WHERE `%s` = ?", tbl.name, fld.name);
1194 AddCursorWhereClauses(command, next, useIndex);
1195 strcat(command, order);
1196 strcat(command, ";");
1197 result = sqlite3_prepare_v2(tbl.db.db, command, -1, &stmt, null);
1198 BindData(stmt, bindId++, (SQLiteField)fld, data, null);
1199 nextFindStatement = stmt;
1202 tbl.GetIndexOrder(order, true);
1203 // For tracing back finds
1205 sprintf(command, "SELECT ROWID, * FROM `%s` WHERE `%s` = ?", tbl.name, fld.name);
1206 AddCursorWhereClauses(command, previous, true);
1207 strcat(command, order);
1208 strcat(command, ";");
1209 result = sqlite3_prepare_v2(tbl.db.db, command, -1, &stmt, null);
1210 BindData(stmt, bindId++, (SQLiteField)fld, data, null);
1211 prevFindStatement = stmt;
1213 // For tracing back from last
1215 sprintf(command, "SELECT ROWID, * FROM `%s` WHERE `%s` = ?", tbl.name, fld.name);
1216 strcat(command, order);
1217 strcat(command, ";");
1218 result = sqlite3_prepare_v2(tbl.db.db, command, -1, &stmt, null);
1219 BindData(stmt, bindId++, (SQLiteField)fld, data, null);
1220 lastFindStatement = stmt;
1222 result = sqlite3_step(findStatement);
1224 done = result == SQLITE_DONE || (result && result != SQLITE_ROW);
1228 sqlite3_reset(findStatement);
1231 rowID = sqlite3_column_int64(findStatement, 0);
1235 bool FindMultiple(FieldFindData * findData, MoveOptions move, int numFields)
1238 for(c = 0; c < numFields; c++) \
1240 FieldFindData * fieldFind = &findData[c]; \
1241 SQLiteField sqlFld = (SQLiteField)findData->field; \
1242 Class dataType = sqlFld.type; \
1243 BindData(stmt, bindId++, sqlFld, (dataType.type == structClass || dataType.type == noHeadClass || dataType.type == normalClass) ? fieldFind->value.p : &fieldFind->value.i, null); \
1248 char criterias[4096], command[4096], order[1024];
1252 sqlite3_stmt * stmt = null;
1255 if(curStatement) { sqlite3_reset(curStatement); curStatement = null; }
1256 if(findStatement) { sqlite3_finalize(findStatement); findStatement = null; }
1257 if(nextFindStatement) { sqlite3_finalize(nextFindStatement); nextFindStatement = null; }
1258 if(prevFindStatement) { sqlite3_finalize(prevFindStatement); prevFindStatement = null; }
1259 if(lastFindStatement) { sqlite3_finalize(lastFindStatement); lastFindStatement = null; }
1262 sprintf(criterias, "SELECT ROWID, * FROM `%s` WHERE `", tbl.name);
1263 for(c = 0; c < numFields; c++)
1265 FieldFindData * fieldFind = &findData[c];
1267 if(c) strcat(criterias, " AND `");
1268 strcat(criterias, fieldFind->field.name);
1269 strcat(criterias, "` = ?");
1272 useIndex = tbl.GetIndexOrder(order, false);
1273 // Basic Find (multiple)
1274 strcpy(command, criterias);
1275 AddCursorWhereClauses(command, move, useIndex);
1276 strcat(command, order);
1277 strcat(command, ";");
1278 result = sqlite3_prepare_v2(tbl.db.db, command, -1, &stmt, null);
1280 BindCursorData(stmt, move, useIndex, &bindId);
1281 curStatement = findStatement = stmt;
1282 findBindId = bindId;
1284 // For tracing back forward finds
1286 strcpy(command, criterias);
1287 AddCursorWhereClauses(command, previous, true);
1288 strcat(command, order);
1289 strcat(command, ";");
1290 result = sqlite3_prepare_v2(tbl.db.db, command, -1, &stmt, null);
1292 nextFindStatement = stmt;
1295 tbl.GetIndexOrder(order, true);
1296 // For tracing back finds
1298 strcpy(command, criterias);
1299 AddCursorWhereClauses(command, next, useIndex);
1300 strcat(command, order);
1301 strcat(command, ";");
1302 result = sqlite3_prepare_v2(tbl.db.db, command, -1, &stmt, null);
1304 prevFindStatement = stmt;
1306 // For tracing back from last
1308 strcpy(command, criterias);
1309 strcat(command, order);
1310 strcat(command, ";");
1311 result = sqlite3_prepare_v2(tbl.db.db, command, -1, &stmt, null);
1313 lastFindStatement = stmt;
1315 result = sqlite3_step(findStatement);
1316 done = result == SQLITE_DONE || (result && result != SQLITE_ROW);
1320 sqlite3_reset(findStatement);
1323 rowID = sqlite3_column_int64(findStatement, 0);
1329 bool Synch(DriverRow to)
1331 SQLiteRow rowTo = (SQLiteRow)to;
1332 if(tbl && rowTo.tbl && !strcmp(tbl.name, rowTo.tbl.name))
1333 return GoToSysID((uint)rowTo.rowID);
1340 //char command[1024];
1341 //sprintf(command, "INSERT INTO `%s` DEFAULT VALUES;", tbl.name);
1342 //result = sqlite3_exec(tbl.db.db, command, null, null, null);
1345 sqlite3_bind_int64(insertIDStatement, 1, (sqlite3_int64)id);
1346 result = sqlite3_step(insertIDStatement);
1349 result = sqlite3_step(insertStatement);
1350 if(result == SQLITE_DONE) // if(result == SQLITE_OK)
1352 rowID = sqlite3_last_insert_rowid(tbl.db.db);
1353 if(rowID > MAXDWORD)
1355 int64 lastID = tbl.lastID;
1357 sqlite3_bind_int64(selectRowIDsStmt, 1, (sqlite3_int64)lastID);
1361 result = sqlite3_step(selectRowIDsStmt);
1362 if(result == SQLITE_DONE || result != SQLITE_ROW) break;
1363 id = sqlite3_column_int64(selectRowIDsStmt, 0);
1364 if(id - lastID > 1) break;
1367 sqlite3_reset(selectRowIDsStmt);
1369 sqlite3_bind_int64(setRowIDStmt, 2, (sqlite3_int64)rowID);
1372 sqlite3_bind_int64(setRowIDStmt, 1, (sqlite3_int64)rowID);
1373 result = sqlite3_step(setRowIDStmt);
1374 sqlite3_reset(setRowIDStmt);
1376 sqlite3_reset(id ? insertIDStatement : insertStatement);
1377 curStatement = sysIDStatement;
1379 sqlite3_reset(curStatement);
1380 sqlite3_bind_int64(sysIDStatement, 1, (sqlite3_int64)rowID);
1381 result = sqlite3_step(curStatement);
1384 sqlite3_reset(insertStatement);
1391 //char command[1024];
1392 //sprintf(command, "DELETE FROM `%s` WHERE ROWID = %d;", tbl.name, rowID);
1393 //result = sqlite3_exec(tbl.db.db, command, null, null, null);
1394 sqlite3_bind_int64(deleteStatement, 1, (sqlite3_int64)rowID);
1395 result = sqlite3_step(deleteStatement);
1396 sqlite3_reset(deleteStatement);
1398 return result == SQLITE_OK || result == SQLITE_DONE;
1401 bool GetData(Field fld, typed_object &data)
1403 SQLiteField sqlFld = (SQLiteField)fld;
1404 int num = sqlFld.num + 1;
1405 Class dataType = sqlFld.type;
1408 switch(sqlFld.sqliteType)
1410 case SQLITE_INTEGER:
1412 switch(dataType.typeSize)
1415 if(fld == tbl.primaryKey)
1416 *(int64 *)data = rowID;
1418 *(int64 *)data = sqlite3_column_int64(curStatement, num);
1421 if(fld == tbl.primaryKey)
1422 *(int *)data = (int)(uint)rowID;
1424 *(int *)data = sqlite3_column_int(curStatement, num);
1429 if(fld == tbl.primaryKey)
1430 value = (int)(uint)rowID;
1432 value = sqlite3_column_int(curStatement, num);
1434 *(short *)data = (short)value;
1436 *(uint16 *)data = (uint16)value;
1442 if(fld == tbl.primaryKey)
1443 value = (int)(uint)rowID;
1445 value = sqlite3_column_int(curStatement, num);
1447 *(char *)data = (char)value;
1449 *(byte *)data = (byte)value;
1457 double d = sqlite3_column_double(curStatement, num);
1458 if(dataType.typeSize == 8)
1459 *(double *)data = d;
1461 *(float *)data = (float)d;
1466 int numBytes = sqlite3_column_bytes(curStatement, num);
1467 char * text = sqlite3_column_text(curStatement, num);
1468 *(char **)data = text ? new byte[numBytes+1] : null;
1470 memcpy(*(char **)data, text, numBytes+1);
1475 SerialBuffer buffer { };
1476 //buffer._buffer = sqlite3_column_blob(curStatement, num);
1477 buffer._size = sqlite3_column_bytes(curStatement, num);
1478 buffer._buffer = sqlite3_column_text(curStatement, num);
1479 buffer.count = buffer._size;
1481 dataType._vTbl[__ecereVMethodID_class_OnUnserialize](dataType, data, buffer);
1483 buffer._buffer = null;
1491 bool SetData(Field fld, typed_object data)
1493 SQLiteField sqlFld = (SQLiteField)fld;
1495 int num = sqlFld.num + 1;
1499 sqlite3_finalize(updateStatement);
1500 sprintf(command, "UPDATE `%s` SET `%s` = ? WHERE ROWID = ?;", tbl.name, sqlFld.name);
1501 result = sqlite3_prepare_v2(tbl.db.db, command, -1, &updateStatement, null);
1502 sqlite3_bind_int64(updateStatement, 2, (sqlite3_int64)rowID);
1503 BindData(updateStatement, 1, (SQLiteField)fld, data, null);
1504 result = sqlite3_step(updateStatement);
1505 sqlite3_reset(updateStatement);
1506 if(fld == tbl.primaryKey)
1507 rowID = *(uint *)data;
1508 return result == SQLITE_DONE;
1513 return (int)(uint)rowID;
1516 bool GoToSysID(uint id)
1518 //char command[1024];
1522 //sqlite3_finalize(statement);
1523 //sprintf(command, "SELECT ROWID, * FROM `%s` WHERE ROWID = ?;", tbl.name);
1524 //result = sqlite3_prepare_v2(tbl.db.db, command, -1, &statement, null);
1528 sqlite3_reset(curStatement);
1530 curStatement = sysIDStatement;
1531 sqlite3_reset(sysIDStatement);
1532 sqlite3_bind_int64(curStatement, 1, (sqlite_int64)rowID);
1533 result = sqlite3_step(curStatement);
1534 done = result == SQLITE_DONE || (result && result != SQLITE_ROW);
1535 if(done) { rowID = 0; sqlite3_reset(curStatement); return false; }
1539 bool SetQueryParam(int paramID, int value)
1542 if(curStatement != queryStatement)
1544 if(curStatement) sqlite3_reset(curStatement);
1545 curStatement = queryStatement;
1547 sqlite3_reset(queryStatement);
1548 result = sqlite3_bind_int(queryStatement, paramID, value);
1552 bool SetQueryParam64(int paramID, int64 value)
1555 if(curStatement != queryStatement)
1557 if(curStatement) sqlite3_reset(curStatement);
1558 curStatement = queryStatement;
1560 sqlite3_reset(queryStatement);
1561 result = sqlite3_bind_int64(queryStatement, paramID, (sqlite_int64)value);
1565 bool SetQueryParamText(int paramID, char * data)
1568 if(curStatement != queryStatement)
1570 if(curStatement) sqlite3_reset(curStatement);
1571 curStatement = queryStatement;
1573 sqlite3_reset(queryStatement);
1575 result = sqlite3_bind_text(queryStatement, paramID, (char *)data, strlen((char *)data), SQLITE_TRANSIENT);
1577 result = sqlite3_bind_text(queryStatement, paramID, null, 0, SQLITE_TRANSIENT);
1581 bool SetQueryParamObject(int paramID, void * data, Class type)
1584 if(curStatement != queryStatement)
1586 if(curStatement) sqlite3_reset(curStatement);
1587 curStatement = queryStatement;
1589 sqlite3_reset(queryStatement);
1591 SerialBuffer buffer { };
1592 type._vTbl[__ecereVMethodID_class_OnSerialize](type, data, buffer);
1593 result = sqlite3_bind_text(queryStatement, paramID, buffer._buffer, buffer.count, SQLITE_TRANSIENT);
1599 /*char * GetExtraColumn(int paramID)
1601 SQLiteField lastFld = tbl.fields.last;
1602 return sqlite3_column_text(curStatement, lastFld.num + 1 + paramID);
1604 char * GetColumn(int paramID)
1606 return sqlite3_column_text(curStatement, paramID);