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 bool BindData(sqlite3_stmt * statement, int pos, SQLiteField fld, typed_object data, SerialBuffer * bufferOut)
1001 Class dataType = fld.type;
1002 SerialBuffer buffer = null;
1003 switch(fld.sqliteType)
1005 case SQLITE_INTEGER:
1007 switch(dataType.typeSize)
1010 result = sqlite3_bind_int64(statement, pos, (sqlite3_int64)*(int64 *)data);
1013 result = sqlite3_bind_int(statement, pos, *(int *)data);
1019 value = (int)*(short *)data;
1021 value = (int)*(uint16 *)data;
1022 result = sqlite3_bind_int(statement, pos, value);
1029 value = (int)*(char *)data;
1031 value = (int)*(byte *)data;
1032 result = sqlite3_bind_int(statement, pos, value);
1040 if(dataType.typeSize == 8)
1041 result = sqlite3_bind_double(statement, pos, *(double *)data);
1043 result = sqlite3_bind_double(statement, pos, (double)*(float *)data);
1049 result = sqlite3_bind_text(statement, pos, (char *)data, strlen((char *)data), SQLITE_TRANSIENT);
1051 result = sqlite3_bind_text(statement, pos, null, 0, SQLITE_TRANSIENT);
1057 buffer = SerialBuffer { };
1058 dataType._vTbl[__ecereVMethodID_class_OnSerialize](dataType, data, buffer);
1059 result = sqlite3_bind_text(statement, pos, buffer._buffer, buffer.count, SQLITE_TRANSIENT);
1064 *bufferOut = buffer;
1070 void AddCursorWhereClauses(char * command, MoveOptions move, bool useIndex)
1072 if(move == next || move == previous)
1074 // Where clauses for index
1078 bool gotPrimaryKey = false;
1080 strcatf(command, " AND (");
1081 for(c = ((move == next) ? 0 : tbl.indexFieldsCount-1); (move == next) ? c < tbl.indexFieldsCount : c >= 0; (move == next) ? c++ : c--)
1084 FieldIndex * fIndex = &tbl.indexFields[c];
1088 strcat(where, fIndex->field.name);
1089 strcat(where, "` ");
1090 strcat(where, (fIndex->order == ((move == next) ? descending : ascending)) ? "<" : ">");
1091 strcat(where, " ? OR (");
1092 strcat(where, fIndex->field.name);
1093 if(fIndex->field == tbl.primaryKey)
1094 gotPrimaryKey = true;
1095 strcat(where, " = ? AND (");
1096 strcat(command, where);
1098 strcat(command, gotPrimaryKey ? "1)" : ((move == next) ? "ROWID > ?)" : "ROWID < ?)"));
1099 for(c = 0; c < tbl.indexFieldsCount; c++)
1100 strcat(command, "))");
1103 strcatf(command, (move == next) ? " AND ROWID > ?" : " AND ROWID < ?");
1107 void BindCursorData(sqlite3_stmt * stmt, MoveOptions move, bool useIndex, int * bindId)
1109 if(move == next || move == previous)
1111 // The binds for the Extra ordering Where clauses
1115 /* // Code to not rely on curStatement being set up
1116 SQLiteRow dataRow = (SQLiteRow)tbl.CreateRow();
1117 dataRow.GoToSysID((uint)rowID);
1119 for(c = ((move == next) ? 0 : tbl.indexFieldsCount-1); (move == next) ? c < tbl.indexFieldsCount : c >= 0; (move == next) ? c++ : c--)
1121 FieldIndex * fIndex = &tbl.indexFields[c];
1123 SQLiteField fld = (SQLiteField)fIndex->field;
1124 Class type = fld.type;
1126 SerialBuffer buffer;
1128 if(type.type == unitClass && !type.typeSize)
1130 Class dataType = eSystem_FindClass(type.module, type.dataTypeString);
1134 if(type.type == structClass)
1136 data = (int64)new0 byte[type.structSize];
1137 dataPtr = (void *) data;
1139 // ((bool (*)())(void *)dataRow.GetData)(dataRow, fld, type, (type.type == structClass) ? (void *)data : &data);
1140 ((bool (*)())(void *)this.GetData)(this, fld, type, (type.type == structClass) ? (void *)data : &data);
1141 if(type.type == normalClass || type.type == noHeadClass)
1142 dataPtr = (void *) data;
1145 ((void (*)())(void *)this.BindData)(this, stmt, (*bindId)++, fld, type, dataPtr, &buffer);
1146 // Reuse the buffer for Blobs...
1147 if(fld.sqliteType == SQLITE_BLOB || fld.sqliteType == SQLITE_NULL)
1149 sqlite3_bind_text(stmt, (*bindId)++, buffer._buffer, buffer.count, SQLITE_TRANSIENT);
1153 ((void (*)())(void *)this.BindData)(this, stmt, (*bindId)++, fld, type, dataPtr, null);
1155 type._vTbl[__ecereVMethodID_class_OnFree](type, dataPtr);
1160 // Bind for the rowid
1161 sqlite3_bind_int64(stmt, (*bindId)++, (sqlite3_int64)rowID);
1165 bool Find(Field fld, MoveOptions move, MatchOptions match, typed_object data)
1167 char order[1024], command[2048];
1170 sqlite3_stmt * stmt = null;
1173 if(fld == tbl.primaryKey)
1175 if(curStatement) { sqlite3_reset(curStatement); curStatement = null; }
1176 if(findStatement) { sqlite3_finalize(findStatement); findStatement = null; }
1177 if(nextFindStatement) { sqlite3_finalize(nextFindStatement); nextFindStatement = null; }
1178 if(prevFindStatement) { sqlite3_finalize(prevFindStatement); prevFindStatement = null; }
1179 if(lastFindStatement) { sqlite3_finalize(lastFindStatement); lastFindStatement = null; }
1180 result = GoToSysID(*(int *)data);
1186 useIndex = tbl.GetIndexOrder(order, false);
1188 sprintf(command, "SELECT ROWID, * FROM `%s` WHERE `%s` = ?", tbl.name, fld.name);
1189 AddCursorWhereClauses(command, move, useIndex);
1190 strcat(command, order);
1191 strcat(command, ";");
1192 result = sqlite3_prepare_v2(tbl.db.db, command, -1, &stmt, null);
1193 BindData(stmt, bindId++, (SQLiteField)fld, data, null);
1194 BindCursorData(stmt, move, useIndex, &bindId);
1196 // Currently, we can't reset curStatement until after BindCursorData, as current data is read from it
1197 if(curStatement) { sqlite3_reset(curStatement); curStatement = null; }
1198 if(findStatement) { sqlite3_finalize(findStatement); findStatement = null; }
1199 if(nextFindStatement) { sqlite3_finalize(nextFindStatement); nextFindStatement = null; }
1200 if(prevFindStatement) { sqlite3_finalize(prevFindStatement); prevFindStatement = null; }
1201 if(lastFindStatement) { sqlite3_finalize(lastFindStatement); lastFindStatement = null; }
1203 curStatement = findStatement = stmt;
1204 findBindId = bindId;
1206 // For going back to forward find
1208 sprintf(command, "SELECT ROWID, * FROM `%s` WHERE `%s` = ?", tbl.name, fld.name);
1209 AddCursorWhereClauses(command, next, useIndex);
1210 strcat(command, order);
1211 strcat(command, ";");
1212 result = sqlite3_prepare_v2(tbl.db.db, command, -1, &stmt, null);
1213 BindData(stmt, bindId++, (SQLiteField)fld, data, null);
1214 nextFindStatement = stmt;
1217 tbl.GetIndexOrder(order, true);
1218 // For tracing back finds
1220 sprintf(command, "SELECT ROWID, * FROM `%s` WHERE `%s` = ?", tbl.name, fld.name);
1221 AddCursorWhereClauses(command, previous, true);
1222 strcat(command, order);
1223 strcat(command, ";");
1224 result = sqlite3_prepare_v2(tbl.db.db, command, -1, &stmt, null);
1225 BindData(stmt, bindId++, (SQLiteField)fld, data, null);
1226 prevFindStatement = stmt;
1228 // For tracing back from last
1230 sprintf(command, "SELECT ROWID, * FROM `%s` WHERE `%s` = ?", tbl.name, fld.name);
1231 strcat(command, order);
1232 strcat(command, ";");
1233 result = sqlite3_prepare_v2(tbl.db.db, command, -1, &stmt, null);
1234 BindData(stmt, bindId++, (SQLiteField)fld, data, null);
1235 lastFindStatement = stmt;
1237 result = sqlite3_step(findStatement);
1239 done = result == SQLITE_DONE || (result && result != SQLITE_ROW);
1243 sqlite3_reset(findStatement);
1246 rowID = sqlite3_column_int64(findStatement, 0);
1250 bool FindMultiple(FieldFindData * findData, MoveOptions move, int numFields)
1253 for(c = 0; c < numFields; c++) \
1255 FieldFindData * fieldFind = &findData[c]; \
1256 SQLiteField sqlFld = (SQLiteField)findData->field; \
1257 Class dataType = sqlFld.type; \
1258 BindData(stmt, bindId++, sqlFld, (dataType.type == structClass || dataType.type == noHeadClass || dataType.type == normalClass) ? fieldFind->value.p : &fieldFind->value.i, null); \
1263 char criterias[4096], command[4096], order[1024];
1267 sqlite3_stmt * stmt = null;
1271 sprintf(criterias, "SELECT ROWID, * FROM `%s` WHERE `", tbl.name);
1272 for(c = 0; c < numFields; c++)
1274 FieldFindData * fieldFind = &findData[c];
1276 if(c) strcat(criterias, " AND `");
1277 strcat(criterias, fieldFind->field.name);
1278 strcat(criterias, "` = ?");
1281 useIndex = tbl.GetIndexOrder(order, false);
1282 // Basic Find (multiple)
1283 strcpy(command, criterias);
1284 AddCursorWhereClauses(command, move, useIndex);
1285 strcat(command, order);
1286 strcat(command, ";");
1287 result = sqlite3_prepare_v2(tbl.db.db, command, -1, &stmt, null);
1289 BindCursorData(stmt, move, useIndex, &bindId);
1291 // Currently, we can't reset curStatement until after BindCursorData, as current data is read from it
1292 if(curStatement) { sqlite3_reset(curStatement); curStatement = null; }
1293 if(findStatement) { sqlite3_finalize(findStatement); findStatement = null; }
1294 if(nextFindStatement) { sqlite3_finalize(nextFindStatement); nextFindStatement = null; }
1295 if(prevFindStatement) { sqlite3_finalize(prevFindStatement); prevFindStatement = null; }
1296 if(lastFindStatement) { sqlite3_finalize(lastFindStatement); lastFindStatement = null; }
1298 curStatement = findStatement = stmt;
1299 findBindId = bindId;
1301 // For tracing back forward finds
1303 strcpy(command, criterias);
1304 AddCursorWhereClauses(command, previous, true);
1305 strcat(command, order);
1306 strcat(command, ";");
1307 result = sqlite3_prepare_v2(tbl.db.db, command, -1, &stmt, null);
1309 nextFindStatement = stmt;
1312 tbl.GetIndexOrder(order, true);
1313 // For tracing back finds
1315 strcpy(command, criterias);
1316 AddCursorWhereClauses(command, next, useIndex);
1317 strcat(command, order);
1318 strcat(command, ";");
1319 result = sqlite3_prepare_v2(tbl.db.db, command, -1, &stmt, null);
1321 prevFindStatement = stmt;
1323 // For tracing back from last
1325 strcpy(command, criterias);
1326 strcat(command, order);
1327 strcat(command, ";");
1328 result = sqlite3_prepare_v2(tbl.db.db, command, -1, &stmt, null);
1330 lastFindStatement = stmt;
1332 result = sqlite3_step(findStatement);
1333 done = result == SQLITE_DONE || (result && result != SQLITE_ROW);
1337 sqlite3_reset(findStatement);
1340 rowID = sqlite3_column_int64(findStatement, 0);
1346 bool Synch(DriverRow to)
1348 SQLiteRow rowTo = (SQLiteRow)to;
1349 if(tbl && rowTo.tbl && !strcmp(tbl.name, rowTo.tbl.name))
1350 return GoToSysID((uint)rowTo.rowID);
1357 //char command[1024];
1358 //sprintf(command, "INSERT INTO `%s` DEFAULT VALUES;", tbl.name);
1359 //result = sqlite3_exec(tbl.db.db, command, null, null, null);
1362 sqlite3_bind_int64(insertIDStatement, 1, (sqlite3_int64)id);
1363 result = sqlite3_step(insertIDStatement);
1366 result = sqlite3_step(insertStatement);
1367 if(result == SQLITE_DONE) // if(result == SQLITE_OK)
1369 rowID = sqlite3_last_insert_rowid(tbl.db.db);
1370 if(rowID > MAXDWORD)
1372 int64 lastID = tbl.lastID;
1374 sqlite3_bind_int64(selectRowIDsStmt, 1, (sqlite3_int64)lastID);
1378 result = sqlite3_step(selectRowIDsStmt);
1379 if(result == SQLITE_DONE || result != SQLITE_ROW) break;
1380 id = sqlite3_column_int64(selectRowIDsStmt, 0);
1381 if(id - lastID > 1) break;
1384 sqlite3_reset(selectRowIDsStmt);
1386 sqlite3_bind_int64(setRowIDStmt, 2, (sqlite3_int64)rowID);
1389 sqlite3_bind_int64(setRowIDStmt, 1, (sqlite3_int64)rowID);
1390 result = sqlite3_step(setRowIDStmt);
1391 sqlite3_reset(setRowIDStmt);
1393 sqlite3_reset(id ? insertIDStatement : insertStatement);
1394 curStatement = sysIDStatement;
1396 sqlite3_reset(curStatement);
1397 sqlite3_bind_int64(sysIDStatement, 1, (sqlite3_int64)rowID);
1398 result = sqlite3_step(curStatement);
1401 sqlite3_reset(insertStatement);
1408 //char command[1024];
1409 //sprintf(command, "DELETE FROM `%s` WHERE ROWID = %d;", tbl.name, rowID);
1410 //result = sqlite3_exec(tbl.db.db, command, null, null, null);
1411 sqlite3_bind_int64(deleteStatement, 1, (sqlite3_int64)rowID);
1412 result = sqlite3_step(deleteStatement);
1413 sqlite3_reset(deleteStatement);
1415 return result == SQLITE_OK || result == SQLITE_DONE;
1418 bool GetData(Field fld, typed_object &data)
1420 SQLiteField sqlFld = (SQLiteField)fld;
1421 int num = sqlFld.num + 1;
1422 Class dataType = sqlFld.type;
1425 switch(sqlFld.sqliteType)
1427 case SQLITE_INTEGER:
1429 switch(dataType.typeSize)
1432 if(fld == tbl.primaryKey)
1433 *(int64 *)data = rowID;
1435 *(int64 *)data = sqlite3_column_int64(curStatement, num);
1438 if(fld == tbl.primaryKey)
1439 *(int *)data = (int)(uint)rowID;
1441 *(int *)data = sqlite3_column_int(curStatement, num);
1446 if(fld == tbl.primaryKey)
1447 value = (int)(uint)rowID;
1449 value = sqlite3_column_int(curStatement, num);
1451 *(short *)data = (short)value;
1453 *(uint16 *)data = (uint16)value;
1459 if(fld == tbl.primaryKey)
1460 value = (int)(uint)rowID;
1462 value = sqlite3_column_int(curStatement, num);
1464 *(char *)data = (char)value;
1466 *(byte *)data = (byte)value;
1474 double d = sqlite3_column_double(curStatement, num);
1475 if(dataType.typeSize == 8)
1476 *(double *)data = d;
1478 *(float *)data = (float)d;
1483 int numBytes = sqlite3_column_bytes(curStatement, num);
1484 char * text = sqlite3_column_text(curStatement, num);
1485 *(char **)data = text ? new byte[numBytes+1] : null;
1487 memcpy(*(char **)data, text, numBytes+1);
1492 SerialBuffer buffer { };
1493 //buffer._buffer = sqlite3_column_blob(curStatement, num);
1494 buffer._size = sqlite3_column_bytes(curStatement, num);
1495 buffer._buffer = sqlite3_column_text(curStatement, num);
1496 buffer.count = buffer._size;
1498 dataType._vTbl[__ecereVMethodID_class_OnUnserialize](dataType, data, buffer);
1500 buffer._buffer = null;
1508 bool SetData(Field fld, typed_object data)
1510 SQLiteField sqlFld = (SQLiteField)fld;
1512 int num = sqlFld.num + 1;
1516 sqlite3_finalize(updateStatement);
1517 sprintf(command, "UPDATE `%s` SET `%s` = ? WHERE ROWID = ?;", tbl.name, sqlFld.name);
1518 result = sqlite3_prepare_v2(tbl.db.db, command, -1, &updateStatement, null);
1519 sqlite3_bind_int64(updateStatement, 2, (sqlite3_int64)rowID);
1520 BindData(updateStatement, 1, (SQLiteField)fld, data, null);
1521 result = sqlite3_step(updateStatement);
1522 sqlite3_reset(updateStatement);
1523 if(fld == tbl.primaryKey)
1524 rowID = *(uint *)data;
1525 return result == SQLITE_DONE;
1530 return (int)(uint)rowID;
1533 bool GoToSysID(uint id)
1535 //char command[1024];
1539 //sqlite3_finalize(statement);
1540 //sprintf(command, "SELECT ROWID, * FROM `%s` WHERE ROWID = ?;", tbl.name);
1541 //result = sqlite3_prepare_v2(tbl.db.db, command, -1, &statement, null);
1545 sqlite3_reset(curStatement);
1547 curStatement = sysIDStatement;
1548 sqlite3_reset(sysIDStatement);
1549 sqlite3_bind_int64(curStatement, 1, (sqlite_int64)rowID);
1550 result = sqlite3_step(curStatement);
1551 done = result == SQLITE_DONE || (result && result != SQLITE_ROW);
1552 if(done) { rowID = 0; sqlite3_reset(curStatement); return false; }
1556 bool SetQueryParam(int paramID, int value)
1559 if(curStatement != queryStatement)
1561 if(curStatement) sqlite3_reset(curStatement);
1562 curStatement = queryStatement;
1564 sqlite3_reset(queryStatement);
1565 result = sqlite3_bind_int(queryStatement, paramID, value);
1569 bool SetQueryParam64(int paramID, int64 value)
1572 if(curStatement != queryStatement)
1574 if(curStatement) sqlite3_reset(curStatement);
1575 curStatement = queryStatement;
1577 sqlite3_reset(queryStatement);
1578 result = sqlite3_bind_int64(queryStatement, paramID, (sqlite_int64)value);
1582 bool SetQueryParamText(int paramID, char * data)
1585 if(curStatement != queryStatement)
1587 if(curStatement) sqlite3_reset(curStatement);
1588 curStatement = queryStatement;
1590 sqlite3_reset(queryStatement);
1592 result = sqlite3_bind_text(queryStatement, paramID, (char *)data, strlen((char *)data), SQLITE_TRANSIENT);
1594 result = sqlite3_bind_text(queryStatement, paramID, null, 0, SQLITE_TRANSIENT);
1598 bool SetQueryParamObject(int paramID, void * data, Class type)
1601 if(curStatement != queryStatement)
1603 if(curStatement) sqlite3_reset(curStatement);
1604 curStatement = queryStatement;
1606 sqlite3_reset(queryStatement);
1608 SerialBuffer buffer { };
1609 type._vTbl[__ecereVMethodID_class_OnSerialize](type, data, buffer);
1610 result = sqlite3_bind_text(queryStatement, paramID, buffer._buffer, buffer.count, SQLITE_TRANSIENT);
1616 bool BindQueryData(int pos, SQLiteField fld, typed_object data)
1618 if(curStatement != queryStatement)
1620 if(curStatement) sqlite3_reset(curStatement);
1621 curStatement = queryStatement;
1623 sqlite3_reset(queryStatement);
1624 return BindData(queryStatement, pos, fld, data, null);
1627 /*char * GetExtraColumn(int paramID)
1629 SQLiteField lastFld = tbl.fields.last;
1630 return sqlite3_column_text(curStatement, lastFld.num + 1 + paramID);
1632 char * GetColumn(int paramID)
1634 return sqlite3_column_text(curStatement, paramID);