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 "SQLiteUdrDbi.h"
23
24 #include <U2Core/AppContext.h>
25 #include <U2Core/RawDataUdrSchema.h>
26 #include <U2Core/U2DbiPackUtils.h>
27 #include <U2Core/U2SafePoints.h>
28 #include <U2Core/UdrSchemaRegistry.h>
29
30 #include "SQLiteBlobInputStream.h"
31 #include "SQLiteBlobOutputStream.h"
32 #include "SQLiteObjectDbi.h"
33
34 namespace U2 {
35
36 namespace {
37 const QString TABLE_PREFIX = "UdrSchema_";
38
tableName(const UdrSchemaId & schemaId)39 QString tableName(const UdrSchemaId &schemaId) {
40 return TABLE_PREFIX + schemaId;
41 }
42 } // namespace
43
SQLiteUdrDbi(SQLiteDbi * dbi)44 SQLiteUdrDbi::SQLiteUdrDbi(SQLiteDbi *dbi)
45 : UdrDbi(dbi), SQLiteChildDBICommon(dbi) {
46 }
47
undo(const U2SingleModStep & modStep,U2OpStatus & os)48 void SQLiteUdrDbi::undo(const U2SingleModStep &modStep, U2OpStatus &os) {
49 SAFE_POINT_EXT(modStep.modType == U2ModType::udrUpdated, os.setError("Unknown modStep"), );
50
51 QByteArray oldData;
52 QByteArray newData;
53 bool ok = U2DbiPackUtils::unpackUdr(modStep.details, oldData, newData);
54 CHECK_EXT(ok, os.setError(U2DbiL10n::tr("An error occurred during updating UDR")), );
55
56 RawDataUdrSchema::writeContent(oldData, U2EntityRef(getRootDbi()->getDbiRef(), modStep.objectId), os);
57 }
58
redo(const U2SingleModStep & modStep,U2OpStatus & os)59 void SQLiteUdrDbi::redo(const U2SingleModStep &modStep, U2OpStatus &os) {
60 SAFE_POINT_EXT(modStep.modType == U2ModType::udrUpdated, os.setError("Unknown modStep"), );
61
62 QByteArray oldData;
63 QByteArray newData;
64 bool ok = U2DbiPackUtils::unpackUdr(modStep.details, oldData, newData);
65 CHECK_EXT(ok, os.setError(U2DbiL10n::tr("An error occurred during updating UDR")), );
66
67 RawDataUdrSchema::writeContent(newData, U2EntityRef(getRootDbi()->getDbiRef(), modStep.objectId), os);
68 }
69
addRecord(const UdrSchemaId & schemaId,const QList<UdrValue> & data,U2OpStatus & os)70 UdrRecordId SQLiteUdrDbi::addRecord(const UdrSchemaId &schemaId, const QList<UdrValue> &data, U2OpStatus &os) {
71 UdrRecordId result("", "");
72 const UdrSchema *schema = udrSchema(schemaId, os);
73 CHECK_OP(os, result);
74 CHECK_EXT(data.size() == schema->size(), os.setError("Size mismatch"), result);
75
76 SQLiteWriteQuery q(insertDef(schema, os), db, os);
77 CHECK_OP(os, result);
78
79 bindData(data, schema, q, os);
80 CHECK_OP(os, result);
81
82 U2DataId recordId = q.insert(U2Type::UdrRecord);
83 return UdrRecordId(schemaId, recordId);
84 }
85
updateRecord(const UdrRecordId & recordId,const QList<UdrValue> & data,U2OpStatus & os)86 void SQLiteUdrDbi::updateRecord(const UdrRecordId &recordId, const QList<UdrValue> &data, U2OpStatus &os) {
87 const UdrSchema *schema = udrSchema(recordId.getSchemaId(), os);
88 CHECK_OP(os, );
89 CHECK_EXT(data.size() == schema->size(), os.setError("Size mismatch"), );
90
91 SQLiteWriteQuery q(updateDef(schema, os), db, os);
92 CHECK_OP(os, );
93
94 bindData(data, schema, q, os);
95 CHECK_OP(os, );
96
97 q.bindDataId(schema->size() + 1, recordId.getRecordId());
98
99 q.update();
100 }
101
getRecord(const UdrRecordId & recordId,U2OpStatus & os)102 UdrRecord SQLiteUdrDbi::getRecord(const UdrRecordId &recordId, U2OpStatus &os) {
103 UdrRecord result(recordId, QList<UdrValue>(), os);
104 const UdrSchema *schema = udrSchema(recordId.getSchemaId(), os);
105 CHECK_OP(os, result);
106
107 SQLiteReadQuery q(selectDef(schema, os), db, os);
108 CHECK_OP(os, result);
109
110 q.bindDataId(1, recordId.getRecordId());
111
112 bool ok = q.step();
113 CHECK_EXT(ok, os.setError("Unknown record id"), result);
114
115 QList<UdrValue> data;
116 retreiveData(data, schema, q, os);
117 CHECK_OP(os, result);
118 q.ensureDone();
119 CHECK_OP(os, result);
120
121 return UdrRecord(recordId, data, os);
122 }
123
createObject(const UdrSchemaId & schemaId,U2Object & udrObject,const QString & folder,U2OpStatus & os)124 void SQLiteUdrDbi::createObject(const UdrSchemaId &schemaId, U2Object &udrObject, const QString &folder, U2OpStatus &os) {
125 const UdrSchema *schema = udrSchema(schemaId, os);
126 CHECK_OP(os, );
127 SAFE_POINT_EXT(schema->hasObjectReference(), os.setError("No object reference"), );
128
129 dbi->getSQLiteObjectDbi()->createObject(udrObject, folder, U2DbiObjectRank_TopLevel, os);
130 }
131
getObjectRecordIds(const UdrSchema * schema,const U2DataId & objectId,U2OpStatus & os)132 QList<U2DataId> SQLiteUdrDbi::getObjectRecordIds(const UdrSchema *schema, const U2DataId &objectId, U2OpStatus &os) {
133 QList<U2DataId> result;
134 SAFE_POINT_EXT(schema->hasObjectReference(), os.setError("No object reference"), result);
135
136 SQLiteReadQuery q("SELECT " + UdrSchema::RECORD_ID_FIELD_NAME + " FROM " + tableName(schema->getId()) + " WHERE " + UdrSchema::OBJECT_FIELD_NAME + " = ?1", db, os);
137 q.bindDataId(1, objectId);
138
139 while (q.step()) {
140 result << q.getDataId(0, U2Type::UdrRecord);
141 }
142 return result;
143 }
144
getObjectRecords(const UdrSchemaId & schemaId,const U2DataId & objectId,U2OpStatus & os)145 QList<UdrRecord> SQLiteUdrDbi::getObjectRecords(const UdrSchemaId &schemaId, const U2DataId &objectId, U2OpStatus &os) {
146 QList<UdrRecord> result;
147 const UdrSchema *schema = udrSchema(schemaId, os);
148 CHECK_OP(os, result);
149
150 const QList<U2DataId> ids = getObjectRecordIds(schema, objectId, os);
151 CHECK_OP(os, result);
152
153 foreach (const U2DataId &id, ids) {
154 result << getRecord(UdrRecordId(schemaId, id), os);
155 CHECK_OP(os, result);
156 }
157
158 return result;
159 }
160
getRecords(const UdrSchemaId & schemaId,U2OpStatus & os)161 QList<UdrRecord> SQLiteUdrDbi::getRecords(const UdrSchemaId &schemaId, U2OpStatus &os) {
162 QList<UdrRecord> result;
163 const UdrSchema *schema = udrSchema(schemaId, os);
164 CHECK_OP(os, result);
165
166 SQLiteReadQuery q(selectAllDef(schema, os), db, os);
167 CHECK_OP(os, result);
168
169 while (q.step()) {
170 QList<UdrValue> data;
171 retreiveData(data, schema, q, os);
172 CHECK_OP(os, result);
173 U2DataId dataId = q.getDataId(0, U2Type::UdrRecord);
174 result << UdrRecord(UdrRecordId(schemaId, dataId), data, os);
175 }
176
177 return result;
178 }
179
removeRecord(const UdrRecordId & recordId,U2OpStatus & os)180 void SQLiteUdrDbi::removeRecord(const UdrRecordId &recordId, U2OpStatus &os) {
181 SQLiteWriteQuery q("DELETE FROM " + tableName(recordId.getSchemaId()) + " WHERE " + UdrSchema::RECORD_ID_FIELD_NAME + " = ?1", db, os);
182 q.bindDataId(1, recordId.getRecordId());
183 q.execute();
184 }
185
createInputStream(const UdrRecordId & recordId,int fieldNum,U2OpStatus & os)186 InputStream *SQLiteUdrDbi::createInputStream(const UdrRecordId &recordId, int fieldNum, U2OpStatus &os) {
187 UdrSchema::FieldDesc field = getBlobField(recordId.getSchemaId(), fieldNum, os);
188 CHECK_OP(os, nullptr);
189
190 return new SQLiteBlobInputStream(db, tableName(recordId.getSchemaId()).toLatin1(), field.getName(), recordId.getRecordId(), os);
191 }
192
createOutputStream(const UdrRecordId & recordId,int fieldNum,qint64 size,U2OpStatus & os)193 OutputStream *SQLiteUdrDbi::createOutputStream(const UdrRecordId &recordId, int fieldNum, qint64 size, U2OpStatus &os) {
194 CHECK_EXT(size >= 0, os.setError("Negative stream size"), nullptr);
195 CHECK_EXT(size <= INT_MAX, os.setError("Too big stream size"), nullptr);
196 UdrSchema::FieldDesc field = getBlobField(recordId.getSchemaId(), fieldNum, os);
197 CHECK_OP(os, nullptr);
198
199 return new SQLiteBlobOutputStream(db, tableName(recordId.getSchemaId()).toLatin1(), field.getName(), recordId.getRecordId(), (int)size, os);
200 }
201
getModificationAction(const U2DataId & id)202 ModificationAction *SQLiteUdrDbi::getModificationAction(const U2DataId &id) {
203 return new SQLiteModificationAction(dbi, id);
204 }
205
206 /************************************************************************/
207 /* SQL initialization */
208 /************************************************************************/
initSqlSchema(U2OpStatus & os)209 void SQLiteUdrDbi::initSqlSchema(U2OpStatus &os) {
210 UdrSchemaRegistry *udrRegistry = AppContext::getUdrSchemaRegistry();
211 SAFE_POINT_EXT(nullptr != udrRegistry, os.setError("NULL UDR registry"), );
212
213 foreach (const UdrSchemaId &id, udrRegistry->getRegisteredSchemas()) {
214 const UdrSchema *schema = udrSchema(id, os);
215 CHECK_OP(os, );
216 initSchema(schema, os);
217 CHECK_OP(os, );
218 }
219 }
220
initSchema(const UdrSchema * schema,U2OpStatus & os)221 void SQLiteUdrDbi::initSchema(const UdrSchema *schema, U2OpStatus &os) {
222 CHECK_EXT(nullptr != schema, os.setError("NULL schema"), );
223 createTable(schema, os);
224 CHECK_OP(os, );
225
226 foreach (const QStringList index, indexes(schema, os)) {
227 createIndex(schema->getId(), index, os);
228 CHECK_OP(os, );
229 }
230 }
231
createTable(const UdrSchema * schema,U2OpStatus & os)232 void SQLiteUdrDbi::createTable(const UdrSchema *schema, U2OpStatus &os) {
233 CHECK_EXT(schema->size() > 0, os.setError("Empty schema"), );
234
235 QString query = tableStartDef(schema->getId());
236 CHECK_OP(os, );
237 for (int i = 0; i < schema->size(); i++) {
238 UdrSchema::FieldDesc field = schema->getField(i, os);
239 CHECK_OP(os, );
240 query += ", " + fieldDef(field);
241 CHECK_OP(os, );
242 }
243 query += foreignKeysDef(schema, os);
244 CHECK_OP(os, );
245 query += ")";
246
247 SQLiteWriteQuery(query, db, os).execute();
248 }
249
createIndex(const UdrSchemaId & schemaId,const QStringList & fields,U2OpStatus & os)250 void SQLiteUdrDbi::createIndex(const UdrSchemaId &schemaId, const QStringList &fields, U2OpStatus &os) {
251 QString query = "CREATE INDEX " + tableName(schemaId) + "_" + fields.join("_") + " " + "on " + tableName(schemaId) + "(" + fields.join(", ") + ")";
252
253 SQLiteWriteQuery(query, db, os).execute();
254 }
255
256 /************************************************************************/
257 /* Utilities */
258 /************************************************************************/
udrSchema(const UdrSchemaId & schemaId,U2OpStatus & os)259 const UdrSchema *SQLiteUdrDbi::udrSchema(const UdrSchemaId &schemaId, U2OpStatus &os) {
260 UdrSchemaRegistry *udrRegistry = AppContext::getUdrSchemaRegistry();
261 SAFE_POINT_EXT(nullptr != udrRegistry, os.setError("NULL UDR registry"), nullptr);
262
263 const UdrSchema *schema = udrRegistry->getSchemaById(schemaId);
264 SAFE_POINT_EXT(nullptr != schema, os.setError("NULL UDR schema"), nullptr);
265 return schema;
266 }
267
insertDef(const UdrSchema * schema,U2OpStatus & os)268 QString SQLiteUdrDbi::insertDef(const UdrSchema *schema, U2OpStatus &os) {
269 QStringList nums;
270 for (int i = 0; i < schema->size(); i++) {
271 nums << QString("?%1").arg(i + 1);
272 }
273
274 return "INSERT INTO " + tableName(schema->getId()) + "(" + UdrSchema::fieldNames(schema, os).join(", ") + ") " + "VALUES(" + nums.join(", ") + ")";
275 }
276
updateDef(const UdrSchema * schema,U2OpStatus & os)277 QString SQLiteUdrDbi::updateDef(const UdrSchema *schema, U2OpStatus &os) {
278 QStringList assignments;
279 for (int i = 0; i < schema->size(); i++) {
280 UdrSchema::FieldDesc field = schema->getField(i, os);
281 CHECK_OP(os, "");
282 assignments << QString("%1 = ?%2").arg(field.getName().constData()).arg(i + 1);
283 }
284
285 return "UPDATE " + tableName(schema->getId()) + " SET " + assignments.join(", ") + " WHERE " + UdrSchema::RECORD_ID_FIELD_NAME + QString(" = ?%1").arg(schema->size() + 1);
286 }
287
selectAllDef(const UdrSchema * schema,U2OpStatus & os)288 QString SQLiteUdrDbi::selectAllDef(const UdrSchema *schema, U2OpStatus &os) {
289 QList<int> directFields = UdrSchema::notBinary(schema, os);
290 CHECK_OP(os, "");
291
292 const bool isObjectReferenced = schema->hasObjectReference();
293
294 return "SELECT " + UdrSchema::RECORD_ID_FIELD_NAME + ", " + UdrSchema::fieldNames(schema, os, directFields).join(", ") + (isObjectReferenced ? ", o.type" : "") + " FROM " + tableName(schema->getId()) + (isObjectReferenced ? " AS udr INNER JOIN Object AS o ON o.id = udr." + UdrSchema::OBJECT_FIELD_NAME : "");
295 }
296
selectDef(const UdrSchema * schema,U2OpStatus & os)297 QString SQLiteUdrDbi::selectDef(const UdrSchema *schema, U2OpStatus &os) {
298 return selectAllDef(schema, os) + " WHERE " + UdrSchema::RECORD_ID_FIELD_NAME + " = ?1";
299 }
300
tableStartDef(const UdrSchemaId & schemaId)301 QString SQLiteUdrDbi::tableStartDef(const UdrSchemaId &schemaId) {
302 return "CREATE TABLE " + tableName(schemaId) + " (" +
303 UdrSchema::RECORD_ID_FIELD_NAME + " INTEGER PRIMARY KEY AUTOINCREMENT";
304 }
305
fieldDef(const UdrSchema::FieldDesc & field)306 QString SQLiteUdrDbi::fieldDef(const UdrSchema::FieldDesc &field) {
307 QString def = field.getName() + " ";
308 switch (field.getDataType()) {
309 case UdrSchema::INTEGER:
310 def += "INTEGER";
311 break;
312 case UdrSchema::DOUBLE:
313 def += "REAL";
314 break;
315 case UdrSchema::STRING:
316 def += "TEXT";
317 break;
318 case UdrSchema::BLOB:
319 def += "BLOB";
320 break;
321 case UdrSchema::ID:
322 def += "INTEGER NOT NULL";
323 break;
324 default:
325 FAIL("Unknown UDR data type detected!", QString());
326 }
327 return def;
328 }
329
foreignKeysDef(const UdrSchema * schema,U2OpStatus & os)330 QString SQLiteUdrDbi::foreignKeysDef(const UdrSchema *schema, U2OpStatus &os) {
331 QString result;
332
333 for (int i = 0; i < schema->size(); i++) {
334 const UdrSchema::FieldDesc field = schema->getField(i, os);
335 CHECK_OP(os, "");
336
337 if (UdrSchema::ID == field.getDataType()) {
338 result += ", FOREIGN KEY(" + field.getName() + ") REFERENCES Object(id) ON DELETE CASCADE";
339 }
340 }
341
342 return result;
343 }
344
indexes(const UdrSchema * schema,U2OpStatus & os)345 QList<QStringList> SQLiteUdrDbi::indexes(const UdrSchema *schema, U2OpStatus &os) {
346 QList<QStringList> result;
347
348 // single column indexes
349 for (int i = 0; i < schema->size(); i++) {
350 UdrSchema::FieldDesc field = schema->getField(i, os);
351 CHECK_OP(os, result);
352 if (UdrSchema::INDEXED == field.getIndexType()) {
353 QStringList index;
354 index << field.getName();
355 result << index;
356 }
357 }
358
359 // multi column indexes
360 foreach (const QList<int> &multiIndex, schema->getMultiIndexes()) {
361 result << UdrSchema::fieldNames(schema, os, multiIndex);
362 CHECK_OP(os, result);
363 }
364
365 return result;
366 }
367
bindData(const QList<UdrValue> & data,const UdrSchema * schema,SQLiteQuery & q,U2OpStatus & os)368 void SQLiteUdrDbi::bindData(const QList<UdrValue> &data, const UdrSchema *schema, SQLiteQuery &q, U2OpStatus &os) {
369 for (int i = 0; i < data.size(); i++) {
370 const UdrValue &value = data[i];
371 UdrSchema::FieldDesc field = schema->getField(i, os);
372 CHECK_OP(os, );
373
374 switch (field.getDataType()) {
375 case UdrSchema::INTEGER:
376 q.bindInt64(i + 1, value.getInt(os));
377 break;
378 case UdrSchema::DOUBLE:
379 q.bindDouble(i + 1, value.getDouble(os));
380 break;
381 case UdrSchema::STRING:
382 q.bindString(i + 1, value.getString(os));
383 break;
384 case UdrSchema::BLOB:
385 q.bindBlob(i + 1, "");
386 break;
387 case UdrSchema::ID:
388 q.bindDataId(i + 1, value.getDataId(os));
389 break;
390 default:
391 FAIL("Unknown UDR data type detected!", );
392 }
393 CHECK_OP(os, );
394 }
395 }
396
retreiveData(QList<UdrValue> & data,const UdrSchema * schema,SQLiteQuery & q,U2OpStatus & os)397 void SQLiteUdrDbi::retreiveData(QList<UdrValue> &data, const UdrSchema *schema, SQLiteQuery &q, U2OpStatus &os) {
398 QList<int> fields = UdrSchema::notBinary(schema, os);
399 CHECK_OP(os, );
400
401 for (int i = 0; i < schema->size(); i++) {
402 UdrSchema::FieldDesc field = schema->getField(i, os);
403 CHECK_OP(os, );
404 int colNum = -1;
405 if (UdrSchema::BLOB != field.getDataType()) {
406 colNum = fields.lastIndexOf(i) + 1;
407 }
408 switch (field.getDataType()) {
409 case UdrSchema::INTEGER:
410 data << UdrValue(q.getInt64(colNum));
411 break;
412 case UdrSchema::DOUBLE:
413 data << UdrValue(q.getDouble(colNum));
414 break;
415 case UdrSchema::STRING:
416 data << UdrValue(q.getString(colNum));
417 break;
418 case UdrSchema::BLOB:
419 data << UdrValue();
420 break;
421 case UdrSchema::ID:
422 const U2DataType objectType = q.getInt32(schema->size() + 1); // type is selected in the additional column
423 data << UdrValue(q.getDataId(colNum, objectType));
424 break;
425 }
426 CHECK_OP(os, );
427 }
428 }
429
getBlobField(const UdrSchemaId & schemaId,int fieldNum,U2OpStatus & os)430 UdrSchema::FieldDesc SQLiteUdrDbi::getBlobField(const UdrSchemaId &schemaId, int fieldNum, U2OpStatus &os) {
431 const UdrSchema *schema = udrSchema(schemaId, os);
432 CHECK_OP(os, UdrSchema::FieldDesc("", UdrSchema::INTEGER));
433 UdrSchema::FieldDesc field = schema->getField(fieldNum, os);
434 CHECK_OP(os, field);
435
436 if (UdrSchema::BLOB != field.getDataType()) {
437 os.setError("Only BLOB fields can be used");
438 }
439 return field;
440 }
441
442 } // namespace U2
443