1 /**
2 * @file Database.cpp
3 * @ingroup SQLiteCpp
4 * @brief Management of a SQLite Database Connection.
5 *
6 * Copyright (c) 2012-2020 Sebastien Rombauts (sebastien.rombauts@gmail.com)
7 *
8 * Distributed under the MIT License (MIT) (See accompanying file LICENSE.txt
9 * or copy at http://opensource.org/licenses/MIT)
10 */
11 #include <SQLiteCpp/Database.h>
12
13 #include <SQLiteCpp/Assertion.h>
14 #include <SQLiteCpp/Backup.h>
15 #include <SQLiteCpp/Exception.h>
16 #include <SQLiteCpp/Statement.h>
17
18 #include <sqlite3.h>
19 #include <fstream>
20 #include <string.h>
21
22 #ifndef SQLITE_DETERMINISTIC
23 #define SQLITE_DETERMINISTIC 0x800
24 #endif // SQLITE_DETERMINISTIC
25
26
27 namespace SQLite
28 {
29
30 const int OPEN_READONLY = SQLITE_OPEN_READONLY;
31 const int OPEN_READWRITE = SQLITE_OPEN_READWRITE;
32 const int OPEN_CREATE = SQLITE_OPEN_CREATE;
33 const int OPEN_URI = SQLITE_OPEN_URI;
34 const int OPEN_MEMORY = SQLITE_OPEN_MEMORY;
35 const int OPEN_NOMUTEX = SQLITE_OPEN_NOMUTEX;
36 const int OPEN_FULLMUTEX = SQLITE_OPEN_FULLMUTEX;
37 const int OPEN_SHAREDCACHE = SQLITE_OPEN_SHAREDCACHE;
38 const int OPEN_PRIVATECACHE = SQLITE_OPEN_PRIVATECACHE;
39 #if SQLITE_VERSION_NUMBER >= 3031000
40 const int OPEN_NOFOLLOW = SQLITE_OPEN_NOFOLLOW;
41 #else
42 const int OPEN_NOFOLLOW = 0;
43 #endif
44
45 const int OK = SQLITE_OK;
46
47 const char* VERSION = SQLITE_VERSION;
48 const int VERSION_NUMBER = SQLITE_VERSION_NUMBER;
49
50 // Return SQLite version string using runtime call to the compiled library
getLibVersion()51 const char* getLibVersion() noexcept
52 {
53 return sqlite3_libversion();
54 }
55
56 // Return SQLite version number using runtime call to the compiled library
getLibVersionNumber()57 int getLibVersionNumber() noexcept
58 {
59 return sqlite3_libversion_number();
60 }
61
62
63 // Open the provided database UTF-8 filename with SQLite::OPEN_xxx provided flags.
Database(const char * apFilename,const int aFlags,const int aBusyTimeoutMs,const char * apVfs)64 Database::Database(const char* apFilename,
65 const int aFlags /* = SQLite::OPEN_READONLY*/,
66 const int aBusyTimeoutMs /* = 0 */,
67 const char* apVfs /* = nullptr*/) :
68 mFilename(apFilename)
69 {
70 sqlite3* handle;
71 const int ret = sqlite3_open_v2(apFilename, &handle, aFlags, apVfs);
72 mSQLitePtr.reset(handle);
73 if (SQLITE_OK != ret)
74 {
75 throw SQLite::Exception(handle, ret);
76 }
77 if (aBusyTimeoutMs > 0)
78 {
79 setBusyTimeout(aBusyTimeoutMs);
80 }
81 }
82
83 // Deleter functor to use with smart pointers to close the SQLite database connection in an RAII fashion.
operator ()(sqlite3 * apSQLite)84 void Database::Deleter::operator()(sqlite3* apSQLite)
85 {
86 const int ret = sqlite3_close(apSQLite); // Calling sqlite3_close() with a nullptr argument is a harmless no-op.
87
88 // Avoid unreferenced variable warning when build in release mode
89 (void) ret;
90
91 // Only case of error is SQLITE_BUSY: "database is locked" (some statements are not finalized)
92 // Never throw an exception in a destructor :
93 SQLITECPP_ASSERT(SQLITE_OK == ret, "database is locked"); // See SQLITECPP_ENABLE_ASSERT_HANDLER
94 }
95
96 /**
97 * @brief Set a busy handler that sleeps for a specified amount of time when a table is locked.
98 *
99 * This is useful in multithreaded program to handle case where a table is locked for writting by a thread.
100 * Any other thread cannot access the table and will receive a SQLITE_BUSY error:
101 * setting a timeout will wait and retry up to the time specified before returning this SQLITE_BUSY error.
102 * Reading the value of timeout for current connection can be done with SQL query "PRAGMA busy_timeout;".
103 * Default busy timeout is 0ms.
104 *
105 * @param[in] aBusyTimeoutMs Amount of milliseconds to wait before returning SQLITE_BUSY
106 *
107 * @throw SQLite::Exception in case of error
108 */
setBusyTimeout(const int aBusyTimeoutMs)109 void Database::setBusyTimeout(const int aBusyTimeoutMs)
110 {
111 const int ret = sqlite3_busy_timeout(getHandle(), aBusyTimeoutMs);
112 check(ret);
113 }
114
115 // Shortcut to execute one or multiple SQL statements without results (UPDATE, INSERT, ALTER, COMMIT, CREATE...).
exec(const char * apQueries)116 int Database::exec(const char* apQueries)
117 {
118 const int ret = tryExec(apQueries);
119 check(ret);
120
121 // Return the number of rows modified by those SQL statements (INSERT, UPDATE or DELETE only)
122 return sqlite3_changes(getHandle());
123 }
124
tryExec(const char * apQueries)125 int Database::tryExec(const char* apQueries) noexcept
126 {
127 return sqlite3_exec(getHandle(), apQueries, nullptr, nullptr, nullptr);
128 }
129
130 // Shortcut to execute a one step query and fetch the first column of the result.
131 // WARNING: Be very careful with this dangerous method: you have to
132 // make a COPY OF THE result, else it will be destroy before the next line
133 // (when the underlying temporary Statement and Column objects are destroyed)
134 // this is an issue only for pointer type result (ie. char* and blob)
135 // (use the Column copy-constructor)
execAndGet(const char * apQuery)136 Column Database::execAndGet(const char* apQuery)
137 {
138 Statement query(*this, apQuery);
139 (void)query.executeStep(); // Can return false if no result, which will throw next line in getColumn()
140 return query.getColumn(0);
141 }
142
143 // Shortcut to test if a table exists.
tableExists(const char * apTableName)144 bool Database::tableExists(const char* apTableName)
145 {
146 Statement query(*this, "SELECT count(*) FROM sqlite_master WHERE type='table' AND name=?");
147 query.bind(1, apTableName);
148 (void)query.executeStep(); // Cannot return false, as the above query always return a result
149 return (1 == query.getColumn(0).getInt());
150 }
151
152 // Get the rowid of the most recent successful INSERT into the database from the current connection.
getLastInsertRowid() const153 long long Database::getLastInsertRowid() const noexcept
154 {
155 return sqlite3_last_insert_rowid(getHandle());
156 }
157
158 // Get total number of rows modified by all INSERT, UPDATE or DELETE statement since connection.
getTotalChanges() const159 int Database::getTotalChanges() const noexcept
160 {
161 return sqlite3_total_changes(getHandle());
162 }
163
164 // Return the numeric result code for the most recent failed API call (if any).
getErrorCode() const165 int Database::getErrorCode() const noexcept
166 {
167 return sqlite3_errcode(getHandle());
168 }
169
170 // Return the extended numeric result code for the most recent failed API call (if any).
getExtendedErrorCode() const171 int Database::getExtendedErrorCode() const noexcept
172 {
173 return sqlite3_extended_errcode(getHandle());
174 }
175
176 // Return UTF-8 encoded English language explanation of the most recent failed API call (if any).
getErrorMsg() const177 const char* Database::getErrorMsg() const noexcept
178 {
179 return sqlite3_errmsg(getHandle());
180 }
181
182 // Attach a custom function to your sqlite database. Assumes UTF8 text representation.
183 // Parameter details can be found here: http://www.sqlite.org/c3ref/create_function.html
createFunction(const char * apFuncName,int aNbArg,bool abDeterministic,void * apApp,void (* apFunc)(sqlite3_context *,int,sqlite3_value **),void (* apStep)(sqlite3_context *,int,sqlite3_value **),void (* apFinal)(sqlite3_context *),void (* apDestroy)(void *))184 void Database::createFunction(const char* apFuncName,
185 int aNbArg,
186 bool abDeterministic,
187 void* apApp,
188 void (*apFunc)(sqlite3_context *, int, sqlite3_value **),
189 void (*apStep)(sqlite3_context *, int, sqlite3_value **) /* = nullptr */,
190 void (*apFinal)(sqlite3_context *) /* = nullptr */, // NOLINT(readability/casting)
191 void (*apDestroy)(void *) /* = nullptr */)
192 {
193 int textRep = SQLITE_UTF8;
194 // optimization if deterministic function (e.g. of nondeterministic function random())
195 if (abDeterministic)
196 {
197 textRep = textRep | SQLITE_DETERMINISTIC;
198 }
199 const int ret = sqlite3_create_function_v2(getHandle(), apFuncName, aNbArg, textRep,
200 apApp, apFunc, apStep, apFinal, apDestroy);
201 check(ret);
202 }
203
204 // Load an extension into the sqlite database. Only affects the current connection.
205 // Parameter details can be found here: http://www.sqlite.org/c3ref/load_extension.html
loadExtension(const char * apExtensionName,const char * apEntryPointName)206 void Database::loadExtension(const char* apExtensionName, const char *apEntryPointName)
207 {
208 #ifdef SQLITE_OMIT_LOAD_EXTENSION
209 // Unused
210 (void)apExtensionName;
211 (void)apEntryPointName;
212
213 throw SQLite::Exception("sqlite extensions are disabled");
214 #else
215 #ifdef SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION // Since SQLite 3.13 (2016-05-18):
216 // Security warning:
217 // It is recommended that the SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION method be used to enable only this interface.
218 // The use of the sqlite3_enable_load_extension() interface should be avoided to keep the SQL load_extension()
219 // disabled and prevent SQL injections from giving attackers access to extension loading capabilities.
220 // (NOTE: not using nullptr: cannot pass object of non-POD type 'std::__1::nullptr_t' through variadic function)
221 int ret = sqlite3_db_config(getHandle(), SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION, 1, NULL); // NOTE: not using nullptr
222 #else
223 int ret = sqlite3_enable_load_extension(getHandle(), 1);
224 #endif
225 check(ret);
226
227 ret = sqlite3_load_extension(getHandle(), apExtensionName, apEntryPointName, 0);
228 check(ret);
229 #endif
230 }
231
232 // Set the key for the current sqlite database instance.
key(const std::string & aKey) const233 void Database::key(const std::string& aKey) const
234 {
235 int passLen = static_cast<int>(aKey.length());
236 #ifdef SQLITE_HAS_CODEC
237 if (passLen > 0)
238 {
239 const int ret = sqlite3_key(getHandle(), aKey.c_str(), passLen);
240 check(ret);
241 }
242 #else // SQLITE_HAS_CODEC
243 if (passLen > 0)
244 {
245 throw SQLite::Exception("No encryption support, recompile with SQLITE_HAS_CODEC to enable.");
246 }
247 #endif // SQLITE_HAS_CODEC
248 }
249
250 // Reset the key for the current sqlite database instance.
rekey(const std::string & aNewKey) const251 void Database::rekey(const std::string& aNewKey) const
252 {
253 #ifdef SQLITE_HAS_CODEC
254 int passLen = aNewKey.length();
255 if (passLen > 0)
256 {
257 const int ret = sqlite3_rekey(getHandle(), aNewKey.c_str(), passLen);
258 check(ret);
259 }
260 else
261 {
262 const int ret = sqlite3_rekey(getHandle(), nullptr, 0);
263 check(ret);
264 }
265 #else // SQLITE_HAS_CODEC
266 static_cast<void>(aNewKey); // silence unused parameter warning
267 throw SQLite::Exception("No encryption support, recompile with SQLITE_HAS_CODEC to enable.");
268 #endif // SQLITE_HAS_CODEC
269 }
270
271 // Test if a file contains an unencrypted database.
isUnencrypted(const std::string & aFilename)272 bool Database::isUnencrypted(const std::string& aFilename)
273 {
274 if (aFilename.empty())
275 {
276 throw SQLite::Exception("Could not open database, the aFilename parameter was empty.");
277 }
278
279 std::ifstream fileBuffer(aFilename.c_str(), std::ios::in | std::ios::binary);
280 char header[16];
281 if (fileBuffer.is_open())
282 {
283 fileBuffer.seekg(0, std::ios::beg);
284 fileBuffer.getline(header, 16);
285 fileBuffer.close();
286 }
287 else
288 {
289 throw SQLite::Exception("Error opening file: " + aFilename);
290 }
291
292 return strncmp(header, "SQLite format 3\000", 16) == 0;
293 }
294
295 // Parse header data from a database.
getHeaderInfo(const std::string & aFilename)296 Header Database::getHeaderInfo(const std::string& aFilename)
297 {
298 Header h;
299 unsigned char buf[100];
300 char* pBuf = reinterpret_cast<char*>(&buf[0]);
301 char* pHeaderStr = reinterpret_cast<char*>(&h.headerStr[0]);
302
303 if (aFilename.empty())
304 {
305 throw SQLite::Exception("Filename parameter is empty");
306 }
307
308 {
309 std::ifstream fileBuffer(aFilename.c_str(), std::ios::in | std::ios::binary);
310 if (fileBuffer.is_open())
311 {
312 fileBuffer.seekg(0, std::ios::beg);
313 fileBuffer.read(pBuf, 100);
314 fileBuffer.close();
315 if (fileBuffer.gcount() < 100)
316 {
317 throw SQLite::Exception("File " + aFilename + " is too short");
318 }
319 }
320 else
321 {
322 throw SQLite::Exception("Error opening file " + aFilename);
323 }
324 }
325
326 // If the "magic string" can't be found then header is invalid, corrupt or unreadable
327 memcpy(pHeaderStr, pBuf, 16);
328 pHeaderStr[15] = '\0';
329 if (strncmp(pHeaderStr, "SQLite format 3", 15) != 0)
330 {
331 throw SQLite::Exception("Invalid or encrypted SQLite header in file " + aFilename);
332 }
333
334 h.pageSizeBytes = (buf[16] << 8) | buf[17];
335 h.fileFormatWriteVersion = buf[18];
336 h.fileFormatReadVersion = buf[19];
337 h.reservedSpaceBytes = buf[20];
338 h.maxEmbeddedPayloadFrac = buf[21];
339 h.minEmbeddedPayloadFrac = buf[22];
340 h.leafPayloadFrac = buf[23];
341
342 h.fileChangeCounter =
343 (buf[24] << 24) |
344 (buf[25] << 16) |
345 (buf[26] << 8) |
346 (buf[27] << 0);
347
348 h.databaseSizePages =
349 (buf[28] << 24) |
350 (buf[29] << 16) |
351 (buf[30] << 8) |
352 (buf[31] << 0);
353
354 h.firstFreelistTrunkPage =
355 (buf[32] << 24) |
356 (buf[33] << 16) |
357 (buf[34] << 8) |
358 (buf[35] << 0);
359
360 h.totalFreelistPages =
361 (buf[36] << 24) |
362 (buf[37] << 16) |
363 (buf[38] << 8) |
364 (buf[39] << 0);
365
366 h.schemaCookie =
367 (buf[40] << 24) |
368 (buf[41] << 16) |
369 (buf[42] << 8) |
370 (buf[43] << 0);
371
372 h.schemaFormatNumber =
373 (buf[44] << 24) |
374 (buf[45] << 16) |
375 (buf[46] << 8) |
376 (buf[47] << 0);
377
378 h.defaultPageCacheSizeBytes =
379 (buf[48] << 24) |
380 (buf[49] << 16) |
381 (buf[50] << 8) |
382 (buf[51] << 0);
383
384 h.largestBTreePageNumber =
385 (buf[52] << 24) |
386 (buf[53] << 16) |
387 (buf[54] << 8) |
388 (buf[55] << 0);
389
390 h.databaseTextEncoding =
391 (buf[56] << 24) |
392 (buf[57] << 16) |
393 (buf[58] << 8) |
394 (buf[59] << 0);
395
396 h.userVersion =
397 (buf[60] << 24) |
398 (buf[61] << 16) |
399 (buf[62] << 8) |
400 (buf[63] << 0);
401
402 h.incrementalVaccumMode =
403 (buf[64] << 24) |
404 (buf[65] << 16) |
405 (buf[66] << 8) |
406 (buf[67] << 0);
407
408 h.applicationId =
409 (buf[68] << 24) |
410 (buf[69] << 16) |
411 (buf[70] << 8) |
412 (buf[71] << 0);
413
414 h.versionValidFor =
415 (buf[92] << 24) |
416 (buf[93] << 16) |
417 (buf[94] << 8) |
418 (buf[95] << 0);
419
420 h.sqliteVersion =
421 (buf[96] << 24) |
422 (buf[97] << 16) |
423 (buf[98] << 8) |
424 (buf[99] << 0);
425
426 return h;
427 }
428
backup(const char * apFilename,BackupType aType)429 void Database::backup(const char* apFilename, BackupType aType)
430 {
431 // Open the database file identified by apFilename
432 Database otherDatabase(apFilename, SQLite::OPEN_READWRITE | SQLite::OPEN_CREATE);
433
434 // For a 'Save' operation, data is copied from the current Database to the other. A 'Load' is the reverse.
435 Database& src = (aType == Save ? *this : otherDatabase);
436 Database& dest = (aType == Save ? otherDatabase : *this);
437
438 // Set up the backup procedure to copy between the "main" databases of each connection
439 Backup bkp(dest, src);
440 bkp.executeStep(); // Execute all steps at once
441
442 // RAII Finish Backup an Close the other Database
443 }
444
445 } // namespace SQLite
446