1 #ifndef SQLITEDB_H 2 #define SQLITEDB_H 3 4 #include "sql/ObjectIdentifier.h" 5 #include "sql/sqlitetypes.h" 6 7 #include <condition_variable> 8 #include <memory> 9 #include <mutex> 10 #include <functional> 11 #include <vector> 12 #include <map> 13 14 #include <QObject> 15 #include <QByteArray> 16 #include <QStringList> 17 18 struct sqlite3; 19 class CipherSettings; 20 21 enum LogMessageType 22 { 23 kLogMsg_User, 24 kLogMsg_App, 25 kLogMsg_ErrorLog 26 }; 27 28 using objectMap = std::multimap<std::string, sqlb::ObjectPtr>; // Maps from object type (table, index, view, trigger) to a pointer to the object representation 29 using schemaMap = std::map<std::string, objectMap>; // Maps from the schema name (main, temp, attached schemas) to the object map for that schema 30 31 int collCompare(void* pArg, int sizeA, const void* sA, int sizeB, const void* sB); 32 33 namespace sqlb 34 { 35 QString escapeIdentifier(const QString& id); 36 QString escapeString(const QString& literal); 37 } 38 39 /// represents a single SQLite database. except when noted otherwise, 40 /// all member functions are to be called from the main UI thread 41 /// only. 42 class DBBrowserDB : public QObject 43 { 44 Q_OBJECT 45 46 private: 47 /// custom unique_ptr deleter releases database for further use by others 48 struct DatabaseReleaser 49 { pParentDatabaseReleaser50 explicit DatabaseReleaser(DBBrowserDB * pParent_ = nullptr) : pParent(pParent_) {} 51 52 DBBrowserDB * pParent; 53 operatorDatabaseReleaser54 void operator() (sqlite3 * db) const 55 { 56 if(!db || !pParent) 57 return; 58 59 std::unique_lock<std::mutex> lk(pParent->m); 60 pParent->db_used = false; 61 lk.unlock(); 62 emit pParent->databaseInUseChanged(false, QString()); 63 pParent->cv.notify_one(); 64 } 65 }; 66 67 public: 68 69 explicit DBBrowserDB(); 70 ~DBBrowserDB () override = default; 71 72 bool open(const QString& db, bool readOnly = false); 73 bool attach(const QString& filename, QString attach_as = QString()); 74 bool create ( const QString & db); 75 bool close(); 76 77 // This returns the SQLite version as well as the SQLCipher if DB4S is compiled with encryption support 78 static void getSqliteVersion(QString& sqlite, QString& sqlcipher); 79 80 using db_pointer_type = std::unique_ptr<sqlite3, DatabaseReleaser>; 81 82 /** 83 borrow exclusive address to the currently open database, until 84 releasing the returned unique_ptr. 85 86 the intended use case is that the main UI thread can call this 87 any time, and then optionally pass the obtained pointer to a 88 background worker, or release it after doing work immediately. 89 90 if database is currently used by somebody else, opens a dialog 91 box and gives user the opportunity to sqlite3_interrupt() the 92 operation of the current owner, then tries again. 93 94 \param user a string that identifies the new user, and which 95 can be displayed in the dialog box. 96 97 \param force_wait if set to true we won't ask the user to cancel 98 the running query but just wait until it is done. 99 100 \returns a unique_ptr containing the SQLite database handle, or 101 nullptr in case no database is open. 102 **/ 103 db_pointer_type get (const QString& user, bool force_wait = false); 104 105 bool setSavepoint(const std::string& pointname = "RESTOREPOINT"); 106 bool releaseSavepoint(const std::string& pointname = "RESTOREPOINT"); 107 bool revertToSavepoint(const std::string& pointname = "RESTOREPOINT"); 108 bool releaseAllSavepoints(); 109 bool revertAll(); 110 111 bool dump(const QString& filename, const std::vector<std::string>& tablesToDump, bool insertColNames, bool insertNew, bool exportSchema, bool exportData, bool keepOldSchema) const; 112 113 enum ChoiceOnUse 114 { 115 Ask, 116 Wait, 117 CancelOther 118 }; 119 // Callback to get results from executeSQL(). It is invoked for 120 // each result row coming out of the evaluated SQL statements. If 121 // a callback returns true (abort), the executeSQL() method 122 // returns false (error) without invoking the callback again and 123 // without running any subsequent SQL statements. The 1st argument 124 // is the number of columns in the result. The 2nd argument to the 125 // callback is the text representation of the values, one for each 126 // column. The 3rd argument is a list of strings where each entry 127 // represents the name of corresponding result column. 128 using execCallback = std::function<bool(int, std::vector<QByteArray>, std::vector<QByteArray>)>; 129 bool executeSQL(const std::string& statement, bool dirtyDB = true, bool logsql = true, execCallback callback = nullptr); 130 bool executeMultiSQL(QByteArray query, bool dirty = true, bool log = false); 131 QByteArray querySingleValueFromDb(const std::string& sql, bool log = true, ChoiceOnUse choice = Ask) const; 132 lastError()133 const QString& lastError() const { return lastErrorMessage; } 134 135 /** 136 * @brief getRow Executes a sqlite statement to get the rowdata(columns) 137 * for the given rowid. 138 * @param schemaName Name of the database schema. 139 * @param sTableName Table to query. 140 * @param rowid The rowid to fetch. 141 * @param rowdata A list of QByteArray containing the row data. 142 * @return true if statement execution was ok, else false. 143 */ 144 bool getRow(const sqlb::ObjectIdentifier& table, const QString& rowid, std::vector<QByteArray>& rowdata) const; 145 146 /** 147 * @brief Interrupts the currenty running statement as soon as possible. 148 */ 149 void interruptQuery(); 150 151 private: 152 /** 153 * @brief max Queries the table t for the max value of field. 154 * @param tableName Table to query 155 * @param field Name of the field to get the max value 156 * @return the max value of the field or 0 on error 157 */ 158 unsigned long max(const sqlb::ObjectIdentifier& tableName, const std::string& field) const; 159 160 static int callbackWrapper (void* callback, int numberColumns, char** values, char** columnNames); 161 162 public: 163 void updateSchema(); // Please don't call this from threads other than the main thread. 164 165 private: 166 /** 167 * @brief Creates an empty insert statement. 168 * @param schemaName The name of the database schema in which to find the table 169 * @param pk_value This optional parameter can be used to manually set a specific value for the primary key column 170 * @return An sqlite conform INSERT INTO statement with empty values. (NULL,'',0) 171 */ 172 std::string emptyInsertStmt(const std::string& schemaName, const sqlb::Table& t, const QString& pk_value = QString()) const; 173 174 public: 175 QString addRecord(const sqlb::ObjectIdentifier& tablename); 176 bool deleteRecords(const sqlb::ObjectIdentifier& table, const std::vector<QString>& rowids, const sqlb::StringVector& pseudo_pk = {}); 177 bool updateRecord(const sqlb::ObjectIdentifier& table, const std::string& column, const QString& rowid, const QByteArray& value, int force_type = 0, const sqlb::StringVector& pseudo_pk = {}); 178 179 bool createTable(const sqlb::ObjectIdentifier& name, const sqlb::FieldVector& structure); 180 bool renameTable(const std::string& schema, const std::string& from_table, const std::string& to_table); 181 bool addColumn(const sqlb::ObjectIdentifier& tablename, const sqlb::Field& field); 182 183 /** 184 * @brief This type maps from old column names to new column names. Given the old and the new table definition, this suffices to 185 * track fields between the two. 186 * USE CASES: 187 * 1) Don't specify a column at all or specify equal column names: Keep its name as-is. 188 * 2) Specify different column names: Rename the field. 189 * 3) Map from an existing column name to a Null string: Delete the column. 190 * 4) Map from a Null column name to a new column name: Add the column. 191 */ 192 using AlterTableTrackColumns = std::map<QString, QString>; 193 194 /** 195 * @brief alterTable Can be used to rename, modify or drop existing columns of a given table 196 * @param tablename Specifies the schema and name of the table to edit 197 * @param new_table Specifies the new table schema. This is exactly how the new table is going to look like. 198 * @param track_columns Maps old column names to new column names. This is used to copy the data from the old table to the new one. 199 * @param newSchema Set this to a non-empty string to move the table to a new schema 200 * @return true if renaming was successful, false if not. In the latter case also lastErrorMessage is set 201 */ 202 bool alterTable(const sqlb::ObjectIdentifier& tablename, const sqlb::Table& new_table, AlterTableTrackColumns track_columns, std::string newSchemaName = std::string()); 203 204 template<typename T = sqlb::Object> getObjectByName(const sqlb::ObjectIdentifier & name)205 const std::shared_ptr<T> getObjectByName(const sqlb::ObjectIdentifier& name) const 206 { 207 for(auto& it : schemata.at(name.schema())) 208 { 209 if(it.second->name() == name.name()) 210 return std::dynamic_pointer_cast<T>(it.second); 211 } 212 return std::shared_ptr<T>(); 213 } 214 215 bool isOpen() const; encrypted()216 bool encrypted() const { return isEncrypted; } readOnly()217 bool readOnly() const { return isReadOnly; } 218 bool getDirty() const; currentFile()219 QString currentFile() const { return curDBFilename; } 220 221 /// log an SQL statement [thread-safe] 222 void logSQL(const QString& statement, LogMessageType msgtype) const; 223 224 QString getPragma(const std::string& pragma) const; 225 bool setPragma(const std::string& pragma, const QString& value); 226 bool setPragma(const std::string& pragma, const QString& value, QString& originalvalue); 227 bool setPragma(const std::string& pragma, int value, int& originalvalue); 228 229 bool loadExtension(const QString& filename); 230 void loadExtensionsFromSettings(); 231 232 static QStringList Datatypes; 233 234 private: 235 std::vector<std::pair<std::string, std::string> > queryColumnInformation(const std::string& schema_name, const std::string& object_name) const; 236 237 public: 238 std::string generateSavepointName(const std::string& identifier = std::string()) const; 239 240 // This function generates the name for a temporary table. It guarantees that there is no table with this name yet 241 std::string generateTemporaryTableName(const std::string& schema) const; 242 243 schemaMap schemata; 244 245 signals: 246 void sqlExecuted(QString sql, int msgtype) const; 247 void dbChanged(bool dirty); 248 void structureUpdated(); 249 void requestCollation(QString name, int eTextRep); 250 void databaseInUseChanged(bool busy, QString user); 251 252 private: 253 /// external code needs to go through get() to obtain access to the database 254 sqlite3 * _db; 255 mutable std::mutex m; 256 mutable std::condition_variable cv; 257 bool db_used; 258 QString db_user; 259 260 /// wait for release of the DB locked through a previous get(), 261 /// giving users the option to discard running task through a 262 /// message box. 263 void waitForDbRelease(ChoiceOnUse choice = Ask) const; 264 265 QString curDBFilename; 266 mutable QString lastErrorMessage; 267 std::vector<std::string> savepointList; 268 bool isEncrypted; 269 bool isReadOnly; 270 271 sqlb::StringVector primaryKeyForEditing(const sqlb::ObjectIdentifier& table, const sqlb::StringVector& pseudo_pk) const; 272 273 // SQLite Callbacks 274 void collationNeeded(void* pData, sqlite3* db, int eTextRep, const char* sCollationName); 275 void errorLogCallback(void* user_data, int error_code, const char* message); 276 277 bool tryEncryptionSettings(const QString& filename, bool* encrypted, CipherSettings*& cipherSettings) const; 278 279 bool dontCheckForStructureUpdates; 280 281 class NoStructureUpdateChecks 282 { 283 public: NoStructureUpdateChecks(DBBrowserDB & db)284 explicit NoStructureUpdateChecks(DBBrowserDB& db) : m_db(db) { m_db.dontCheckForStructureUpdates = true; } ~NoStructureUpdateChecks()285 ~NoStructureUpdateChecks() { m_db.dontCheckForStructureUpdates = false; } 286 287 private: 288 DBBrowserDB& m_db; 289 }; 290 }; 291 292 #endif 293