Line data Source code
1 : /**
2 : Copyright (c) 2021-2022 Roman Katuntsev <sbkarr@stappler.org>
3 : Copyright (c) 2023-2024 Stappler LLC <admin@stappler.dev>
4 :
5 : Permission is hereby granted, free of charge, to any person obtaining a copy
6 : of this software and associated documentation files (the "Software"), to deal
7 : in the Software without restriction, including without limitation the rights
8 : to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
9 : copies of the Software, and to permit persons to whom the Software is
10 : furnished to do so, subject to the following conditions:
11 :
12 : The above copyright notice and this permission notice shall be included in
13 : all copies or substantial portions of the Software.
14 :
15 : THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
16 : IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
17 : FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
18 : AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
19 : LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
20 : OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
21 : THE SOFTWARE.
22 : **/
23 :
24 : #include "SPSqliteHandle.h"
25 : #include "SPSqliteDriver.h"
26 : #include "SPSqliteDriverHandle.h"
27 : #include "sqlite3.h"
28 :
29 : namespace STAPPLER_VERSIONIZED stappler::db::sqlite {
30 :
31 : class SqliteQuery : public db::sql::SqlQuery {
32 : public:
33 13775 : virtual ~SqliteQuery() = default;
34 :
35 13775 : SqliteQuery(db::QueryInterface *q, const sql::Driver *d)
36 13775 : : SqlQuery(q, d) { }
37 :
38 250 : virtual void writeFullTextWhere(WhereContinue &whi, db::Operator op, const db::Scheme &scheme,
39 : const db::Query::Select &sel, StringView ftsQuery) override {
40 250 : auto functionCall = toString("sp_ts_query_valid(\"", scheme.getName(), "\".\"", sel.field, "\", '", ftsQuery, "')");
41 250 : whi.where(op,
42 250 : SqlQuery::Field(StringView(functionCall), SqlQuery::Field::PlainText),
43 250 : Comparation::Equal, RawStringView{StringView("1")});
44 250 : }
45 : };
46 :
47 13775 : SqliteQueryInterface::SqliteQueryInterface(const sql::Driver *d, const sql::QueryStorageHandle *s, Driver::Handle h)
48 13775 : : driver(d), storage(s), handle(h) { }
49 :
50 1325 : size_t SqliteQueryInterface::push(String &&val) {
51 1325 : auto &it = params.emplace_back(BindingData());
52 1325 : it.data.assign_strong((uint8_t *)val.data(), val.size() + 1);
53 1325 : it.idx = params.size();
54 1325 : it.type = Type::Text;
55 1325 : return it.idx;
56 : }
57 :
58 3500 : size_t SqliteQueryInterface::push(const StringView &val) {
59 3500 : auto &it = params.emplace_back(BindingData());
60 3500 : it.data.assign_strong((uint8_t *)val.data(), val.size() + 1);
61 3500 : it.data.data()[val.size()] = 0;
62 3500 : it.idx = params.size();
63 3500 : it.type = Type::Text;
64 3500 : return it.idx;
65 : }
66 :
67 12850 : size_t SqliteQueryInterface::push(Bytes &&val) {
68 12850 : auto &it = params.emplace_back(BindingData());
69 12850 : it.data = std::move(val);
70 12850 : it.idx = params.size();
71 12850 : it.type = Type::Bytes;
72 12850 : return it.idx;
73 : }
74 :
75 26575 : size_t SqliteQueryInterface::push(StringStream &query, const Value &val, bool force, bool compress) {
76 26575 : if (!force || val.getType() == Value::Type::EMPTY) {
77 18025 : switch (val.getType()) {
78 50 : case Value::Type::EMPTY: query << "NULL"; break;
79 3300 : case Value::Type::BOOLEAN: query << (val.asBool() ? "TRUE" : "FALSE"); break;
80 8025 : case Value::Type::INTEGER: query << val.asInteger(); break;
81 1750 : case Value::Type::DOUBLE:
82 1750 : if (std::isnan(val.asDouble())) {
83 0 : query << "'NaN'";
84 1750 : } else if (val.asDouble() == std::numeric_limits<double>::infinity()) {
85 0 : query << "'-Infinity'";
86 1750 : } else if (-val.asDouble() == std::numeric_limits<double>::infinity()) {
87 0 : query << "'Infinity'";
88 : } else {
89 1750 : query << std::setprecision(std::numeric_limits<double>::max_digits10 + 1) << val.asDouble();
90 : }
91 1750 : break;
92 3100 : case Value::Type::CHARSTRING:
93 3100 : query << "?" << push(val.getString());
94 3100 : break;
95 1800 : case Value::Type::BYTESTRING:
96 1800 : query << "?" << push(val.asBytes());
97 1800 : break;
98 0 : case Value::Type::ARRAY:
99 : case Value::Type::DICTIONARY:
100 0 : query << "?" << push(data::write<Interface>(val, EncodeFormat(EncodeFormat::Cbor,
101 0 : compress ? EncodeFormat::LZ4HCCompression : EncodeFormat::DefaultCompress)));
102 0 : break;
103 0 : default: break;
104 : }
105 : } else {
106 25650 : query << "?" << push(data::write<Interface>(val, EncodeFormat(EncodeFormat::Cbor,
107 17100 : compress ? EncodeFormat::LZ4HCCompression : EncodeFormat::DefaultCompress)));
108 : }
109 26575 : return params.size();
110 : }
111 :
112 5500 : void SqliteQueryInterface::bindInt(db::Binder &, StringStream &query, int64_t val) {
113 5500 : query << val;
114 5500 : }
115 4725 : void SqliteQueryInterface::bindUInt(db::Binder &, StringStream &query, uint64_t val) {
116 4725 : query << val;
117 4725 : }
118 0 : void SqliteQueryInterface::bindDouble(db::Binder &, StringStream &query, double val) {
119 0 : query << std::setprecision(std::numeric_limits<double>::max_digits10 + 1) << val;
120 0 : }
121 0 : void SqliteQueryInterface::bindString(db::Binder &, StringStream &query, const String &val) {
122 0 : if (auto num = push(String(val))) {
123 0 : query << "?" << num;
124 : }
125 0 : }
126 1225 : void SqliteQueryInterface::bindMoveString(db::Binder &, StringStream &query, String &&val) {
127 1225 : if (auto num = push(std::move(val))) {
128 1225 : query << "?" << num;
129 : }
130 1225 : }
131 350 : void SqliteQueryInterface::bindStringView(db::Binder &, StringStream &query, const StringView &val) {
132 350 : if (auto num = push(val)) {
133 350 : query << "?" << num;
134 : }
135 350 : }
136 50 : void SqliteQueryInterface::bindBytes(db::Binder &, StringStream &query, const Bytes &val) {
137 50 : if (auto num = push(Bytes(val))) {
138 50 : query << "?" << num;
139 : }
140 50 : }
141 175 : void SqliteQueryInterface::bindMoveBytes(db::Binder &, StringStream &query, Bytes &&val) {
142 175 : if (auto num = push(std::move(val))) {
143 175 : query << "?" << num;
144 : }
145 175 : }
146 475 : void SqliteQueryInterface::bindCoderSource(db::Binder &, StringStream &query, const stappler::CoderSource &val) {
147 475 : if (auto num = push(Bytes(val.data(), val.data() + val.size()))) {
148 475 : query << "?" << num;
149 : }
150 475 : }
151 2250 : void SqliteQueryInterface::bindValue(db::Binder &, StringStream &query, const Value &val) {
152 2250 : push(query, val, false);
153 2250 : }
154 24325 : void SqliteQueryInterface::bindDataField(db::Binder &, StringStream &query, const db::Binder::DataField &f) {
155 24325 : if (f.field && f.field->getType() == db::Type::Custom) {
156 5200 : auto c = f.field->getSlot<db::FieldCustom>();
157 5200 : if (auto info = driver->getCustomFieldInfo(c->getDriverTypeName())) {
158 5200 : if (!info->writeToStorage(*c, *this, query, f.data)) {
159 0 : query << "NULL";
160 : }
161 : } else {
162 0 : query << "NULL";
163 : }
164 : } else {
165 19125 : push(query, f.data, f.force, f.compress);
166 : }
167 24325 : }
168 50 : void SqliteQueryInterface::bindTypeString(db::Binder &, StringStream &query, const db::Binder::TypeString &type) {
169 50 : if (auto num = push(type.str)) {
170 50 : query << "?" << num;
171 : }
172 50 : }
173 :
174 1800 : void SqliteQueryInterface::bindFullText(db::Binder &, StringStream &query, const db::Binder::FullTextField &d) {
175 1800 : auto slot = d.field->getSlot<FieldFullTextView>();
176 1800 : auto result = slot->searchConfiguration->encodeSearchVectorData(d.data);
177 1800 : if (auto num = push(move(result))) {
178 1800 : query << "?" << num;
179 : }
180 :
181 1800 : storage->data->emplace(d.field->getName(), &d.data);
182 1800 : }
183 :
184 250 : void SqliteQueryInterface::bindFullTextFrom(db::Binder &, StringStream &query, const db::Binder::FullTextFrom &d) {
185 250 : auto tableName = toString(d.scheme, "_f_", d.field->getName());
186 250 : auto fieldId = toString(d.scheme, "_id");
187 :
188 250 : auto &storageData = *storage->data;
189 :
190 250 : auto it = storageData.find(d.query);
191 250 : if (it != storageData.end()) {
192 250 : auto q = (TextQueryData *)it->second;
193 250 : query << " INNER JOIN (SELECT DISTINCT \"" << fieldId << "\" as id FROM \"" << tableName << "\" WHERE word IN (";
194 :
195 250 : bool first = true;
196 525 : for (auto &w : q->pos) {
197 275 : if (first) { first = false; } else { query << ","; }
198 275 : query << w;
199 : }
200 :
201 0 : query << ")) AS \"__" << d.scheme << "_" << d.field->getName() << "\""
202 250 : " ON (\"" << d.scheme << "\".__oid=\"__" << d.scheme << "_" << d.field->getName() << "\".id)";
203 : }
204 250 : }
205 :
206 250 : void SqliteQueryInterface::bindFullTextRank(db::Binder &, StringStream &query, const db::Binder::FullTextRank &d) {
207 250 : auto slot = d.field->getSlot<FieldFullTextView>();
208 250 : query << " sp_ts_rank(" << d.scheme << ".\"" << d.field->getName() << "\", '" << d.query << "', " << toInt(slot->normalization) << ")";
209 250 : }
210 :
211 250 : void SqliteQueryInterface::bindFullTextQuery(db::Binder &, StringStream &query, const db::Binder::FullTextQueryRef &d) {
212 250 : query << d.scheme << "." << d.field->getName();
213 :
214 250 : auto it = storage->data->find(query.weak());
215 250 : while (it != storage->data->end()) {
216 0 : query << "_";
217 0 : it = storage->data->find(query.weak());
218 : }
219 :
220 250 : auto q = new TextQueryData;
221 250 : q->query = &d.query;
222 250 : q->query->decompose([&, this] (StringView pos) {
223 275 : emplace_ordered(q->pos, ((const Driver *)driver)->insertWord(handle, pos));
224 275 : }, [&, this] (StringView neg) {
225 0 : emplace_ordered(q->neg, ((const Driver *)driver)->insertWord(handle, neg));
226 0 : });
227 :
228 250 : auto str = StringView(query.weak());
229 250 : storage->data->emplace(str.pdup(), q);
230 250 : }
231 :
232 150 : void SqliteQueryInterface::bindIntVector(Binder &, StringStream &query, const Vector<int64_t> &vec) {
233 150 : query << "(";
234 150 : bool start = true;
235 325 : for (auto &it : vec) {
236 175 : if (start) { start = false; } else { query << ","; }
237 175 : query << it;
238 : }
239 150 : query << ")";
240 150 : }
241 :
242 0 : void SqliteQueryInterface::bindDoubleVector(Binder &b, StringStream &query, const Vector<double> &vec) {
243 0 : query << "(";
244 0 : bool start = true;
245 0 : for (auto &it : vec) {
246 0 : if (start) { start = false; } else { query << ","; }
247 0 : bindDouble(b, query, it);
248 : }
249 0 : query << ")";
250 0 : }
251 :
252 0 : void SqliteQueryInterface::bindStringVector(Binder &b, StringStream &query, const Vector<StringView> &vec) {
253 0 : query << "(";
254 0 : bool start = true;
255 0 : for (auto &it : vec) {
256 0 : if (start) { start = false; } else { query << ","; }
257 0 : bindStringView(b, query, it);
258 : }
259 0 : query << ")";
260 0 : }
261 :
262 5450 : void SqliteQueryInterface::clear() {
263 5450 : params.clear();
264 5450 : }
265 :
266 5323 : Handle::Handle(const Driver *d, Driver::Handle h) : SqlHandle(d), driver(d), handle(h) {
267 5321 : _profile = stappler::sql::Profile::Sqlite;
268 5321 : if (h.get()) {
269 5322 : auto c = d->getConnection(h);
270 5322 : if (c.get()) {
271 5322 : conn = c;
272 5322 : dbName = driver->getDbName(h);
273 : }
274 : }
275 5323 : }
276 :
277 0 : Handle::operator bool() const {
278 0 : return conn.get() != nullptr;
279 : }
280 :
281 0 : Driver::Handle Handle::getHandle() const {
282 0 : return handle;
283 : }
284 :
285 0 : Driver::Connection Handle::getConnection() const {
286 0 : return conn;
287 : }
288 :
289 0 : void Handle::close() {
290 0 : conn = Driver::Connection(nullptr);
291 0 : }
292 :
293 13775 : void Handle::makeQuery(const stappler::Callback<void(sql::SqlQuery &)> &cb, const sql::QueryStorageHandle *storage) {
294 13775 : SqliteQueryInterface interface(_driver, storage, handle);
295 13775 : SqliteQuery query(&interface, _driver);
296 13775 : query.setProfile(_profile);
297 13775 : cb(query);
298 13775 : }
299 :
300 14950 : bool Handle::selectQuery(const sql::SqlQuery &query, const stappler::Callback<bool(sql::Result &)> &cb,
301 : const Callback<void(const Value &)> &errCb) {
302 14950 : if (!conn.get() || getTransactionStatus() == db::TransactionStatus::Rollback) {
303 0 : return false;
304 : }
305 :
306 14950 : auto queryInterface = static_cast<SqliteQueryInterface *>(query.getInterface());
307 :
308 14950 : auto queryString = query.getQuery().weak();
309 :
310 14950 : sqlite3_stmt *stmt = nullptr;
311 14950 : auto err = sqlite3_prepare_v3((sqlite3 *)conn.get(), queryString.data(), queryString.size(), 0, &stmt, nullptr);
312 14950 : if (err != SQLITE_OK) {
313 0 : auto info = driver->getInfo(conn, err);
314 0 : info.setString(query.getQuery().str(), "query");
315 : #if DEBUG
316 0 : log::debug("pq::Handle", EncodeFormat::Pretty, info);
317 : #endif
318 0 : if (errCb) {
319 0 : errCb(info);
320 : }
321 0 : driver->getApplicationInterface()->debug("Database", "Fail to perform query", std::move(info));
322 0 : driver->getApplicationInterface()->error("Database", "Fail to perform query");
323 0 : cancelTransaction();
324 0 : return false;
325 0 : }
326 :
327 : // bind
328 32625 : for (auto &it : queryInterface->params) {
329 17675 : switch (it.type) {
330 4825 : case Type::Text:
331 4825 : sqlite3_bind_text(stmt, it.idx, (const char *)it.data.data(), it.data.size() - 1, SQLITE_STATIC);
332 4825 : break;
333 12850 : case Type::Bytes:
334 12850 : sqlite3_bind_blob(stmt, it.idx, it.data.data(), it.data.size(), SQLITE_STATIC);
335 12850 : break;
336 0 : default:
337 0 : break;
338 : }
339 : }
340 :
341 14950 : err = sqlite3_step(stmt);
342 14950 : if (err != SQLITE_OK && err != SQLITE_DONE && err != SQLITE_ROW) {
343 0 : auto info = driver->getInfo(conn, err);
344 0 : info.setString(query.getQuery().str(), "query");
345 : #if DEBUG
346 0 : log::debug("pq::Handle", EncodeFormat::Pretty, info);
347 : #endif
348 0 : if (errCb) {
349 0 : errCb(info);
350 : }
351 0 : driver->getApplicationInterface()->debug("Database", "Fail to perform query", std::move(info));
352 0 : driver->getApplicationInterface()->error("Database", "Fail to perform query");
353 0 : sqlite3_finalize(stmt);
354 0 : cancelTransaction();
355 0 : return false;
356 0 : }
357 :
358 29900 : ResultCursor cursor(driver, conn, Driver::Result(stmt), err);
359 14950 : db::sql::Result ret(&cursor);
360 14950 : cb(ret);
361 14950 : return true;
362 14950 : }
363 :
364 12196 : bool Handle::performSimpleQuery(const StringView &query, const Callback<void(const Value &)> &errCb) {
365 12196 : if (getTransactionStatus() == db::TransactionStatus::Rollback) {
366 0 : return false;
367 : }
368 :
369 12196 : StringView queryData = query;
370 :
371 12196 : const char *outPtr = query.data();
372 12196 : bool success = true;
373 29921 : while (outPtr && *outPtr != 0 && success) {
374 17946 : auto size = queryData.size() - (outPtr - queryData.data());
375 17945 : StringView nextQuery(outPtr, size);
376 17944 : nextQuery.skipChars<StringView::WhiteSpace>();
377 17944 : if (nextQuery.empty()) {
378 225 : break;
379 : }
380 :
381 17721 : sqlite3_stmt *stmt = nullptr;
382 17721 : auto err = sqlite3_prepare_v3((sqlite3 *)conn.get(), nextQuery.data(), nextQuery.size(), 0, &stmt, &outPtr);
383 17724 : if (err != SQLITE_OK) {
384 0 : auto len = outPtr - nextQuery.data();
385 0 : StringView performedQuery(nextQuery.data(), len);
386 0 : auto info = driver->getInfo(conn, err);
387 0 : info.setString(performedQuery, "query");
388 : #if DEBUG
389 0 : log::debug("pq::Handle", EncodeFormat::Pretty, info);
390 : #endif
391 0 : if (errCb) {
392 0 : errCb(info);
393 : }
394 0 : driver->getApplicationInterface()->debug("Database", "Fail to perform query", std::move(info));
395 0 : driver->getApplicationInterface()->error("Database", "Fail to perform query");
396 0 : cancelTransaction();
397 0 : return false;
398 0 : }
399 :
400 17724 : err = sqlite3_step(stmt);
401 :
402 17724 : if (err != SQLITE_OK && err != SQLITE_DONE && err != SQLITE_ROW) {
403 0 : auto info = driver->getInfo(conn, err);
404 0 : info.setString(nextQuery, "query");
405 : #if DEBUG
406 0 : log::debug("pq::Handle", EncodeFormat::Pretty, info);
407 : #endif
408 0 : if (errCb) {
409 0 : errCb(info);
410 : }
411 0 : driver->getApplicationInterface()->debug("Database", "Fail to perform query", std::move(info));
412 0 : driver->getApplicationInterface()->error("Database", "Fail to perform query");
413 0 : sqlite3_finalize(stmt);
414 0 : cancelTransaction();
415 0 : return false;
416 0 : }
417 :
418 17724 : success = ResultCursor::statusIsSuccess(err);
419 17720 : sqlite3_finalize(stmt);
420 : }
421 12200 : return success;
422 : }
423 :
424 1650 : bool Handle::performSimpleSelect(const StringView &query, const stappler::Callback<void(sql::Result &)> &cb,
425 : const Callback<void(const Value &)> &errCb) {
426 1650 : if (getTransactionStatus() == db::TransactionStatus::Rollback) {
427 0 : return false;
428 : }
429 :
430 1650 : sqlite3_stmt *stmt = nullptr;
431 1650 : auto err = sqlite3_prepare_v3((sqlite3 *)conn.get(), query.data(), query.size(), 0, &stmt, nullptr);
432 1650 : if (err != SQLITE_OK) {
433 0 : auto info = driver->getInfo(conn, err);
434 0 : info.setString(query, "query");
435 : #if DEBUG
436 0 : log::debug("pq::Handle", EncodeFormat::Pretty, info);
437 : #endif
438 0 : if (errCb) {
439 0 : errCb(info);
440 : }
441 0 : driver->getApplicationInterface()->debug("Database", "Fail to perform query", std::move(info));
442 0 : driver->getApplicationInterface()->error("Database", "Fail to perform query");
443 0 : cancelTransaction();
444 0 : return false;
445 0 : }
446 :
447 1650 : err = sqlite3_step(stmt);
448 :
449 1650 : ResultCursor cursor(driver, conn, Driver::Result(stmt), err);
450 1650 : db::sql::Result ret(&cursor);
451 1650 : cb(ret);
452 1650 : return true;
453 1650 : }
454 :
455 0 : bool Handle::isSuccess() const {
456 0 : return ResultCursor::statusIsSuccess(lastError);
457 : }
458 :
459 5949 : bool Handle::beginTransaction() {
460 5949 : if (transactionStatus != db::TransactionStatus::None) {
461 50 : return false;
462 : }
463 :
464 5899 : if (_driver->getApplicationInterface()) {
465 5900 : driver->setUserId(handle, _driver->getApplicationInterface()->getUserIdFromContext());
466 : }
467 :
468 5900 : switch (level) {
469 4125 : case TransactionLevel::Deferred:
470 4125 : if (performSimpleQuery("BEGIN DEFERRED"_weak)) {
471 4122 : level = TransactionLevel::Deferred;
472 4122 : transactionStatus = db::TransactionStatus::Commit;
473 4122 : return true;
474 : }
475 0 : break;
476 0 : case TransactionLevel::Immediate:
477 0 : if (performSimpleQuery("BEGIN IMMEDIATE"_weak)) {
478 0 : level = TransactionLevel::Immediate;
479 0 : transactionStatus = db::TransactionStatus::Commit;
480 0 : return true;
481 : }
482 0 : break;
483 1775 : case TransactionLevel::Exclusive:
484 1775 : if (performSimpleQuery("BEGIN EXCLUSIVE"_weak)) {
485 1775 : level = TransactionLevel::Exclusive;
486 1775 : transactionStatus = db::TransactionStatus::Commit;
487 1775 : return true;
488 : }
489 0 : break;
490 0 : default:
491 0 : break;
492 : }
493 0 : return false;
494 : }
495 :
496 5949 : bool Handle::endTransaction() {
497 5949 : switch (transactionStatus) {
498 5899 : case db::TransactionStatus::Commit:
499 5899 : transactionStatus = db::TransactionStatus::None;
500 5899 : if (performSimpleQuery("COMMIT"_weak)) {
501 5900 : finalizeBroadcast();
502 5898 : return true;
503 : }
504 0 : break;
505 0 : case db::TransactionStatus::Rollback:
506 0 : transactionStatus = db::TransactionStatus::None;
507 0 : if (performSimpleQuery("ROLLBACK"_weak)) {
508 0 : finalizeBroadcast();
509 0 : return false;
510 : }
511 0 : break;
512 50 : default:
513 50 : break;
514 : }
515 50 : return false;
516 : }
517 :
518 : }
|
