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