1 /**
2  * UGENE - Integrated Bioinformatics Tools.
3  * Copyright (C) 2008-2021 UniPro <ugene@unipro.ru>
4  * http://ugene.net
5  *
6  * This program is free software; you can redistribute it and/or
7  * modify it under the terms of the GNU General Public License
8  * as published by the Free Software Foundation; either version 2
9  * of the License, or (at your option) any later version.
10  *
11  * This program is distributed in the hope that it will be useful,
12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14  * GNU General Public License for more details.
15  *
16  * You should have received a copy of the GNU General Public License
17  * along with this program; if not, write to the Free Software
18  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
19  * MA 02110-1301, USA.
20  */
21 
22 #include "SQLiteDbi.h"
23 #include <3rdparty/sqlite3/sqlite3.h>
24 
25 #include <QFile>
26 
27 #include <U2Core/GUrl.h>
28 #include <U2Core/Log.h>
29 #include <U2Core/U2SafePoints.h>
30 #include <U2Core/U2SqlHelpers.h>
31 #include <U2Core/Version.h>
32 
33 #include "SQLiteAssemblyDbi.h"
34 #include "SQLiteAttributeDbi.h"
35 #include "SQLiteFeatureDbi.h"
36 #include "SQLiteModDbi.h"
37 #include "SQLiteMsaDbi.h"
38 #include "SQLiteObjectDbi.h"
39 #include "SQLiteObjectRelationsDbi.h"
40 #include "SQLiteSequenceDbi.h"
41 #include "SQLiteUdrDbi.h"
42 #include "SQLiteVariantDbi.h"
43 #include "util/SqliteUpgraderFrom_0_To_1_13.h"
44 #include "util/SqliteUpgraderFrom_1_13_To_1_25.h"
45 
46 namespace U2 {
47 
48 const int SQLiteDbi::BIND_PARAMETERS_LIMIT = 999;
49 
50 const Version SQLiteDbi::MIN_COMPATIBLE_UGENE_VERSION = Version(1, 25);
51 
SQLiteDbi()52 SQLiteDbi::SQLiteDbi()
53     : U2AbstractDbi(SQLiteDbiFactory::ID) {
54     db = new DbRef();
55     objectDbi = new SQLiteObjectDbi(this);
56     objectRelationsDbi = new SQLiteObjectRelationsDbi(this);
57     sequenceDbi = new SQLiteSequenceDbi(this);
58     modDbi = new SQLiteModDbi(this);
59     msaDbi = new SQLiteMsaDbi(this);
60     assemblyDbi = new SQLiteAssemblyDbi(this);
61     crossDbi = new SQLiteCrossDatabaseReferenceDbi(this);
62     attributeDbi = new SQLiteAttributeDbi(this);
63     variantDbi = new SQLiteVariantDbi(this);
64     featureDbi = new SQLiteFeatureDbi(this);
65     udrDbi = new SQLiteUdrDbi(this);
66 
67     upgraders << new SqliteUpgraderFrom_0_To_1_13(this);
68     upgraders << new SqliteUpgraderFrom_1_13_To_1_25(this);
69 }
70 
~SQLiteDbi()71 SQLiteDbi::~SQLiteDbi() {
72     SAFE_POINT(nullptr == db->handle, "Invalid DB handle detected!", );
73 
74     delete udrDbi;
75     delete objectDbi;
76     delete objectRelationsDbi;
77     delete sequenceDbi;
78     delete msaDbi;
79     delete variantDbi;
80     delete assemblyDbi;
81     delete crossDbi;
82     delete attributeDbi;
83     delete featureDbi;
84     delete modDbi;
85     delete db;
86 }
87 
getObjectDbi()88 U2ObjectDbi *SQLiteDbi::getObjectDbi() {
89     return objectDbi;
90 }
91 
getObjectRelationsDbi()92 U2ObjectRelationsDbi *SQLiteDbi::getObjectRelationsDbi() {
93     return objectRelationsDbi;
94 }
95 
getSequenceDbi()96 U2SequenceDbi *SQLiteDbi::getSequenceDbi() {
97     return sequenceDbi;
98 }
99 
getMsaDbi()100 U2MsaDbi *SQLiteDbi::getMsaDbi() {
101     return msaDbi;
102 }
103 
getAssemblyDbi()104 U2AssemblyDbi *SQLiteDbi::getAssemblyDbi() {
105     return assemblyDbi;
106 }
107 
getCrossDatabaseReferenceDbi()108 U2CrossDatabaseReferenceDbi *SQLiteDbi::getCrossDatabaseReferenceDbi() {
109     return crossDbi;
110 }
111 
getAttributeDbi()112 U2AttributeDbi *SQLiteDbi::getAttributeDbi() {
113     return attributeDbi;
114 }
115 
getVariantDbi()116 U2VariantDbi *SQLiteDbi::getVariantDbi() {
117     return variantDbi;
118 }
119 
getFeatureDbi()120 U2FeatureDbi *SQLiteDbi::getFeatureDbi() {
121     return featureDbi;
122 }
123 
getModDbi()124 U2ModDbi *SQLiteDbi::getModDbi() {
125     return modDbi;
126 }
127 
getUdrDbi()128 UdrDbi *SQLiteDbi::getUdrDbi() {
129     return udrDbi;
130 }
131 
getSQLiteObjectDbi() const132 SQLiteObjectDbi *SQLiteDbi::getSQLiteObjectDbi() const {
133     return objectDbi;
134 }
135 
getSQLiteObjectRelationsDbi() const136 SQLiteObjectRelationsDbi *SQLiteDbi::getSQLiteObjectRelationsDbi() const {
137     return objectRelationsDbi;
138 }
139 
getSQLiteMsaDbi() const140 SQLiteMsaDbi *SQLiteDbi::getSQLiteMsaDbi() const {
141     return msaDbi;
142 }
143 
getSQLiteSequenceDbi() const144 SQLiteSequenceDbi *SQLiteDbi::getSQLiteSequenceDbi() const {
145     return sequenceDbi;
146 }
147 
getSQLiteModDbi() const148 SQLiteModDbi *SQLiteDbi::getSQLiteModDbi() const {
149     return modDbi;
150 }
151 
getSQLiteUdrDbi() const152 SQLiteUdrDbi *SQLiteDbi::getSQLiteUdrDbi() const {
153     return udrDbi;
154 }
155 
getSQLiteFeatureDbi() const156 SQLiteFeatureDbi *SQLiteDbi::getSQLiteFeatureDbi() const {
157     return featureDbi;
158 }
159 
getProperty(const QString & name,const QString & defaultValue,U2OpStatus & os)160 QString SQLiteDbi::getProperty(const QString &name, const QString &defaultValue, U2OpStatus &os) {
161     SQLiteReadQuery q("SELECT value FROM Meta WHERE name = ?1", db, os);
162     q.bindString(1, name);
163     bool found = q.step();
164     if (os.hasError()) {
165         return QString();
166     }
167     if (found) {
168         return q.getString(0);
169     }
170     return defaultValue;
171 }
172 
setProperty(const QString & name,const QString & value,U2OpStatus & os)173 void SQLiteDbi::setProperty(const QString &name, const QString &value, U2OpStatus &os) {
174     if (os.hasError()) {
175         return;
176     }
177     SQLiteWriteQuery q1("DELETE FROM Meta WHERE name = ?1", db, os);
178     q1.bindString(1, name);
179     q1.execute();
180 
181     SQLiteWriteQuery q2("INSERT INTO Meta(name, value) VALUES (?1, ?2)", db, os);
182     q2.bindString(1, name);
183     q2.bindString(2, value);
184     q2.execute();
185 }
186 
startOperationsBlock(U2OpStatus & os)187 void SQLiteDbi::startOperationsBlock(U2OpStatus &os) {
188     db->useCache = true;
189     operationsBlockTransactions.push(new SQLiteTransaction(this->db, os));
190 }
191 
stopOperationBlock(U2OpStatus & os)192 void SQLiteDbi::stopOperationBlock(U2OpStatus &os) {
193     SAFE_POINT_EXT(!operationsBlockTransactions.isEmpty(), os.setError("There is no transaction to delete"), );
194     delete operationsBlockTransactions.pop();
195 
196     if (operationsBlockTransactions.isEmpty()) {
197         db->useCache = false;
198     }
199 }
200 
getDbMutex() const201 QMutex *SQLiteDbi::getDbMutex() const {
202     return &db->lock;
203 }
204 
isReadOnly() const205 bool SQLiteDbi::isReadOnly() const {
206     return SQLiteUtils::isDatabaseReadOnly(db, "main") == 1;
207 }
208 
isTransactionActive() const209 bool SQLiteDbi::isTransactionActive() const {
210     return !db->transactionStack.isEmpty();
211 }
212 
isEmptyCallback(void * o,int argc,char **,char **)213 static int isEmptyCallback(void *o, int argc, char ** /*argv*/, char ** /*column*/) {
214     int *res = (int *)o;
215     *res = argc;
216     return 0;
217 }
218 
isInitialized(U2OpStatus & os)219 bool SQLiteDbi::isInitialized(U2OpStatus &os) {
220     QByteArray showTablesQuery = "SELECT * FROM sqlite_master WHERE type='table';";
221     int nTables = 0;
222     char *err;
223     int rc = sqlite3_exec(db->handle, showTablesQuery.constData(), isEmptyCallback, &nTables, &err);
224     if (rc != SQLITE_OK) {
225         os.setError(U2DbiL10n::tr("Error checking SQLite database: %1!").arg(err));
226         sqlite3_free(err);
227         return false;
228     }
229     return nTables != 0;
230 }
231 
232 #define CT(table, fields) \
233     { \
234         char *err = nullptr; \
235         QByteArray query = QByteArray("CREATE TABLE ") + (table) + " (" + (fields) + ");"; \
236         int rc = sqlite3_exec(db->handle, query, nullptr, nullptr, &err); \
237         if (rc != SQLITE_OK) { \
238             os.setError(U2DbiL10n::tr("Error creating table: %1, error: %2").arg(table).arg(err)); \
239             sqlite3_free(err); \
240             return; \
241         } \
242     }
243 
populateDefaultSchema(U2OpStatus & os)244 void SQLiteDbi::populateDefaultSchema(U2OpStatus &os) {
245     // meta table, stores general db info
246     SQLiteWriteQuery("CREATE TABLE Meta(name TEXT NOT NULL, value TEXT NOT NULL)", db, os).execute();
247 
248     objectDbi->initSqlSchema(os);
249     objectRelationsDbi->initSqlSchema(os);
250     sequenceDbi->initSqlSchema(os);
251     msaDbi->initSqlSchema(os);
252     assemblyDbi->initSqlSchema(os);
253     crossDbi->initSqlSchema(os);
254     attributeDbi->initSqlSchema(os);
255     variantDbi->initSqlSchema(os);
256     featureDbi->initSqlSchema(os);
257     modDbi->initSqlSchema(os);
258     udrDbi->initSqlSchema(os);
259 
260     setVersionProperties(MIN_COMPATIBLE_UGENE_VERSION, os);
261 }
262 
internalInit(const QHash<QString,QString> & props,U2OpStatus & os)263 void SQLiteDbi::internalInit(const QHash<QString, QString> &props, U2OpStatus &os) {
264     if (isInitialized(os)) {
265         const QString appVersionText = getProperty(U2DbiOptions::APP_MIN_COMPATIBLE_VERSION, "", os);
266         CHECK_OP(os, );
267 
268         if (appVersionText.isEmpty()) {
269             // Not an error since other databases might be opened with this interface
270             coreLog.info(U2DbiL10n::tr("Not a %1 SQLite database: %2").arg(U2_PRODUCT_NAME).arg(url));
271         } else {
272             Version dbAppVersion = Version::parseVersion(appVersionText);
273             Version currentVersion = Version::appVersion();
274             if (dbAppVersion > currentVersion) {
275                 coreLog.info(U2DbiL10n::tr("Warning! The database was created with a newer %1 version: "
276                                            "%2. Not all database features may be supported! Current %1 version: %3.")
277                                  .arg(U2_PRODUCT_NAME)
278                                  .arg(dbAppVersion.text)
279                                  .arg(currentVersion.text));
280             }
281         }
282 
283         foreach (const QString &key, props.keys()) {
284             if (key.startsWith("sqlite-")) {
285                 setProperty(key, props.value(key), os);
286             }
287         }
288     }
289 
290     // set up features list
291     features.insert(U2DbiFeature_ReadSequence);
292     features.insert(U2DbiFeature_ReadMsa);
293     features.insert(U2DbiFeature_ReadAssembly);
294     features.insert(U2DbiFeature_WriteSequence);
295     features.insert(U2DbiFeature_WriteMsa);
296     features.insert(U2DbiFeature_WriteAssembly);
297     features.insert(U2DbiFeature_AssemblyReadsPacking);
298     features.insert(U2DbiFeature_AssemblyCoverageStat);
299     features.insert(U2DbiFeature_RemoveObjects);
300     features.insert(U2DbiFeature_ChangeFolders);
301     features.insert(U2DbiFeature_ReadCrossDatabaseReferences);
302     features.insert(U2DbiFeature_WriteCrossDatabaseReferences);
303     features.insert(U2DbiFeature_ReadAttributes);
304     features.insert(U2DbiFeature_WriteAttributes);
305     features.insert(U2DbiFeature_ReadProperties);
306     features.insert(U2DbiFeature_WriteProperties);
307     features.insert(U2DbiFeature_ReadVariant);
308     features.insert(U2DbiFeature_WriteVariant);
309     features.insert(U2DbiFeature_ReadFeatures);
310     features.insert(U2DbiFeature_WriteFeatures);
311     features.insert(U2DbiFeature_ReadModifications);
312     features.insert(U2DbiFeature_WriteModifications);
313     features.insert(U2DbiFeature_ReadUdr);
314     features.insert(U2DbiFeature_WriteUdr);
315 }
316 
setState(U2DbiState s)317 void SQLiteDbi::setState(U2DbiState s) {
318     state = s;
319 }
320 
getLastErrorMessage(int rc)321 QString SQLiteDbi::getLastErrorMessage(int rc) {
322     QString err = db->handle == nullptr ? QString(" error-code: %1").arg(rc) : QString(sqlite3_errmsg(db->handle));
323     return err;
324 }
325 
init(const QHash<QString,QString> & props,const QVariantMap &,U2OpStatus & os)326 void SQLiteDbi::init(const QHash<QString, QString> &props, const QVariantMap &, U2OpStatus &os) {
327     if (db->handle != nullptr) {
328         os.setError(U2DbiL10n::tr("Database is already opened!"));
329         return;
330     }
331     if (state != U2DbiState_Void) {
332         os.setError(U2DbiL10n::tr("Illegal database state: %1").arg(state));
333         return;
334     }
335     setState(U2DbiState_Starting);
336     url = props.value(U2DbiOptions::U2_DBI_OPTION_URL);
337     if (url.isEmpty()) {
338         os.setError(U2DbiL10n::tr("URL is not specified"));
339         setState(U2DbiState_Void);
340         return;
341     }
342     do {
343         int flags = SQLITE_OPEN_READWRITE;
344         bool create = props.value(U2DbiOptions::U2_DBI_OPTION_CREATE, "0").toInt() > 0;
345         if (create) {
346             flags |= SQLITE_OPEN_CREATE;
347         }
348         QByteArray file = url.toUtf8();
349         int rc = sqlite3_open_v2(file.constData(), &db->handle, flags, nullptr);
350         if (rc != SQLITE_OK) {
351             QString err = getLastErrorMessage(rc);
352             os.setError(U2DbiL10n::tr("Error opening SQLite database: %1!").arg(err));
353             break;
354         }
355 
356         SQLiteWriteQuery("PRAGMA synchronous = OFF", db, os).execute();
357         QString lockingMode = props.value(U2DbiOptions::U2_DBI_LOCKING_MODE, "exclusive");
358         if (lockingMode == "normal") {
359             SQLiteWriteQuery("PRAGMA main.locking_mode = NORMAL", db, os).execute();
360         } else {
361             SQLiteWriteQuery("PRAGMA main.locking_mode = EXCLUSIVE", db, os).execute();
362         }
363         SQLiteWriteQuery("PRAGMA temp_store = MEMORY", db, os).execute();
364         SQLiteWriteQuery("PRAGMA journal_mode = MEMORY", db, os).execute();
365         SQLiteWriteQuery("PRAGMA cache_size = 50000", db, os).execute();
366         SQLiteWriteQuery("PRAGMA recursive_triggers = ON", db, os).execute();
367         SQLiteWriteQuery("PRAGMA foreign_keys = ON", db, os).execute();
368         // SQLiteQuery("PRAGMA page_size = 4096", db, os).execute();
369         // TODO: int sqlite3_enable_shared_cache(int);
370         // TODO: read_uncommitted
371         // TODO: incremental_vacuum
372         // TODO: temp_store_directory
373 
374         // check if the opened database is valid sqlite dbi
375         initProperties = props;
376         if (!isInitialized(os) && create) {
377             populateDefaultSchema(os);
378         } else {
379             upgrade(os);
380         }
381         if (os.hasError()) {
382             break;
383         }
384 
385         dbiId = url;
386         internalInit(props, os);
387         // OK, initialization complete
388         if (!os.hasError()) {
389             ioLog.trace(QString("SQLite: initialized: %1\n").arg(url));
390         }
391     } while (0);
392 
393     if (os.hasError()) {
394         sqlite3_close(db->handle);
395         db->handle = nullptr;
396         setState(U2DbiState_Void);
397         return;
398     }
399     setState(U2DbiState_Ready);
400 }
401 
shutdown(U2OpStatus & os)402 QVariantMap SQLiteDbi::shutdown(U2OpStatus &os) {
403     if (db == nullptr) {
404         os.setError(U2DbiL10n::tr("Database is already closed!"));
405         return QVariantMap();
406     }
407     if (state != U2DbiState_Ready) {
408         os.setError(U2DbiL10n::tr("Illegal database state %1!").arg(state));
409         return QVariantMap();
410     }
411 
412     if (!flush(os)) {
413         CHECK_OP(os, QVariantMap());
414         os.setError(U2DbiL10n::tr("Can't synchronize database state"));
415         return QVariantMap();
416     }
417 
418     udrDbi->shutdown(os);
419     objectDbi->shutdown(os);
420     sequenceDbi->shutdown(os);
421     msaDbi->shutdown(os);
422     assemblyDbi->shutdown(os);
423     crossDbi->shutdown(os);
424     attributeDbi->shutdown(os);
425     variantDbi->shutdown(os);
426     featureDbi->shutdown(os);
427     modDbi->shutdown(os);
428 
429     setState(U2DbiState_Stopping);
430     int rc = sqlite3_close(db->handle);
431 
432     if (rc != SQLITE_OK) {
433         ioLog.error(U2DbiL10n::tr("Failed to close database: %1, err: %2").arg(url).arg(getLastErrorMessage(rc)));
434     }
435 
436     ioLog.trace(QString("SQLite: shutting down: %1\n").arg(url));
437 
438     db->handle = nullptr;
439     url.clear();
440     initProperties.clear();
441 
442     setState(U2DbiState_Void);
443     return QVariantMap();
444 }
445 
flush(U2OpStatus &)446 bool SQLiteDbi::flush(U2OpStatus &) {
447     return true;
448 }
449 
getDbiId() const450 QString SQLiteDbi::getDbiId() const {
451     return url;
452 }
453 
getDbiMetaInfo(U2OpStatus &)454 QHash<QString, QString> SQLiteDbi::getDbiMetaInfo(U2OpStatus &) {
455     QHash<QString, QString> res;
456     res["url"] = url;
457     return res;
458 }
459 
getEntityTypeById(const U2DataId & id) const460 U2DataType SQLiteDbi::getEntityTypeById(const U2DataId &id) const {
461     return U2DbiUtils::toType(id);
462 }
463 
464 // SQLiteDbiFactory
465 
SQLiteDbiFactory()466 SQLiteDbiFactory::SQLiteDbiFactory()
467     : U2DbiFactory() {
468 }
469 
createDbi()470 U2Dbi *SQLiteDbiFactory::createDbi() {
471     return new SQLiteDbi();
472 }
473 
getId() const474 U2DbiFactoryId SQLiteDbiFactory::getId() const {
475     return ID;
476 }
477 
isValidDbi(const QHash<QString,QString> & properties,const QByteArray & rawData,U2OpStatus &) const478 FormatCheckResult SQLiteDbiFactory::isValidDbi(const QHash<QString, QString> &properties, const QByteArray &rawData, U2OpStatus &) const {
479     QString surl = properties.value(U2DbiOptions::U2_DBI_OPTION_URL);
480     GUrl url(surl);
481     if (!url.isLocalFile()) {
482         return FormatDetection_NotMatched;
483     }
484     if (rawData.startsWith("SQLite format 3")) {
485         return FormatDetection_Matched;
486     }
487     return FormatDetection_NotMatched;
488 }
489 
isDbiExists(const U2DbiId & id) const490 bool SQLiteDbiFactory::isDbiExists(const U2DbiId &id) const {
491     return QFile::exists(id);
492 }
493 
494 const U2DbiFactoryId SQLiteDbiFactory::ID = SQLITE_DBI_ID;
495 
496 }  // namespace U2
497