Line data Source code
1 : /**
2 : Copyright (c) 2018-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 "SPSqlQuery.h"
25 : #include "SPSqlHandle.h"
26 : #include "SPSqlDriver.h"
27 : #include "SPDbScheme.h"
28 :
29 : namespace STAPPLER_VERSIONIZED stappler::db::sql {
30 :
31 17650 : SqlQuery::SqlQuery(db::QueryInterface *iface, const Driver *driver) {
32 17650 : binder.setInterface(iface);
33 17650 : _driver = driver;
34 17650 : }
35 :
36 7375 : void SqlQuery::clear() {
37 7375 : stream.clear();
38 7375 : binder.clear();
39 7375 : }
40 :
41 2450 : static inline bool SqlQuery_comparationIsValid(const Driver *driver, const Field &f, Comparation comp) {
42 2450 : if (f.isIndexed() || comp == Comparation::IsNull || comp == Comparation::IsNotNull) {
43 2450 : if (f.getType() == Type::Custom) {
44 200 : auto c = f.getSlot<FieldCustom>();
45 200 : if (auto info = driver->getCustomFieldInfo(c->getDriverTypeName())) {
46 200 : return info->isComparationAllowed(*c, comp);
47 : }
48 : } else {
49 2250 : return db::checkIfComparationIsValid(f.getType(), comp, f.getFlags());
50 : }
51 : }
52 0 : return false;
53 : }
54 :
55 0 : static inline auto SqlQuery_makeSoftLimitWith(SqlQuery::Context &ictx,
56 : bool isSubField = false, const StringView &lName = StringView(), uint64_t oid = 0) {
57 0 : return [ctx = &ictx, isSubField, lName, oid] (SqlQuery::GenericQuery &subq) {
58 0 : SqlQuery &query = *ctx->_this;
59 0 : const Scheme &scheme = *ctx->scheme;
60 :
61 0 : auto writeFullTextSel = [&] (SqlQuery::GenericQuery &subQ) {
62 0 : auto sel = subQ.select(SqlQuery::Field(scheme.getName(), "__oid"));
63 0 : auto ftsQuery = ctx->getAlt(ctx->softLimitField);
64 0 : if (!ftsQuery.empty()) {
65 0 : if (sel.state == SqlQuery::State::Some) {
66 0 : sel.query->getStream() << ", ";
67 : }
68 0 : sel.query->writeBind(db::Binder::FullTextRank{scheme.getName(), scheme.getField(ctx->softLimitField), ftsQuery});
69 0 : sel.query->getStream() << " AS __ts_rank_" << ctx->softLimitField;
70 0 : sel.state = SqlQuery::State::Some;
71 : }
72 0 : return sel;
73 0 : };
74 :
75 0 : auto sel = (ctx->softLimitIsFts)
76 0 : ? writeFullTextSel(subq)
77 0 : : subq.select(SqlQuery::Field(scheme.getName(), "__oid"), SqlQuery::Field(scheme.getName(), ctx->softLimitField));
78 0 : auto s = isSubField ? sel.from(scheme.getName()).innerJoinOn("s", [&] (SqlQuery::WhereBegin &q) {
79 0 : q.where(SqlQuery::Field(scheme.getName(), "__oid"), Comparation::Equal, SqlQuery::Field("s", "id"));
80 0 : }) : sel.from(scheme.getName());
81 :
82 0 : if (auto &val = ctx->query->getSoftLimitValue()) {
83 0 : if (ctx->softLimitIsFts) {
84 0 : auto ftsQuery = ctx->getAlt(ctx->softLimitField);
85 :
86 0 : s.query->getStream() << " WHERE(";
87 0 : s.query->writeBind(db::Binder::FullTextRank{ctx->scheme->getName(), ctx->scheme->getField(ctx->softLimitField), ftsQuery});
88 0 : s.query->getStream() << ((ctx->query->getOrdering() == Ordering::Ascending) ? '>' : '<');
89 0 : s.query->writeBind(val);
90 0 : s.query->getStream() << ')';
91 :
92 0 : SqlQuery::SelectWhere w(s.query, SqlQuery::State::Some);
93 0 : if (!lName.empty()) {
94 0 : w.where(Operator::And, SqlQuery::Field(scheme.getName(), lName), Comparation::Equal, oid);
95 : }
96 0 : query.writeWhere(w, Operator::And, scheme, *ctx->query);
97 :
98 : } else {
99 0 : auto w = s.where(SqlQuery::Field(scheme.getName(), ctx->softLimitField),
100 0 : ctx->query->getOrdering() == Ordering::Ascending ? Comparation::GreatherThen : Comparation::LessThen, val);
101 0 : if (!lName.empty()) {
102 0 : w.where(Operator::And, SqlQuery::Field(scheme.getName(), lName), Comparation::Equal, oid);
103 : }
104 0 : query.writeWhere(w, Operator::And, scheme, *ctx->query);
105 : }
106 0 : } else if (ctx->query->hasSelect() || !lName.empty()) {
107 0 : auto w = lName.empty() ? s.where() : s.where(SqlQuery::Field(scheme.getName(), lName), Comparation::Equal, oid);
108 0 : query.writeWhere(w, Operator::And, scheme, *ctx->query);
109 : }
110 :
111 0 : query.writeOrdering(s, scheme, *ctx->query);
112 0 : };
113 : }
114 :
115 : template <typename Clause>
116 8275 : static void SqlQuery_makeCustomFrom(const Driver *driver, SqlQuery &q, Clause &tmp, const Query &query, const Scheme &scheme) {
117 9800 : for (auto &it : query.getSelectList()) {
118 1525 : auto f = scheme.getField(it.field);
119 1525 : switch (f->getType()) {
120 100 : case db::Type::Custom: {
121 100 : auto c = f->getSlot<FieldCustom>();
122 100 : if (auto info = driver->getCustomFieldInfo(c->getDriverTypeName())) {
123 100 : if (info->writeFrom) {
124 100 : info->writeFrom(*c, scheme, tmp, it.compare, it.value1, it.value2);
125 : }
126 : }
127 100 : break;
128 : }
129 300 : case db::Type::FullTextView:
130 300 : q.writeFullTextFrom(tmp, scheme, f, it);
131 300 : break;
132 1125 : default:
133 1125 : break;
134 : }
135 : }
136 8275 : }
137 :
138 : template <typename Clause>
139 6350 : static inline auto SqlQuery_makeWhereClause(const Driver *driver, SqlQuery::Context &ctx, Clause &tmp, const StringView &lName = StringView(), uint64_t oid = 0) {
140 6350 : SqlQuery_makeCustomFrom(driver, *ctx._this, tmp, *ctx.query, *ctx.scheme);
141 :
142 6350 : bool isAsc = ctx.query->getOrdering() == Ordering::Ascending;
143 6350 : if (ctx.query->hasSelect() || !ctx.softLimitField.empty() || !lName.empty()) {
144 6025 : if (ctx.softLimitField == "__oid" || !ctx.hasAltLimit) {
145 6025 : if (auto &val = ctx.query->getSoftLimitValue()) {
146 250 : auto w = tmp.where(SqlQuery::Field(ctx.scheme->getName(), ctx.softLimitField),
147 250 : isAsc ? Comparation::GreatherThen : Comparation::LessThen, val.asInteger());
148 250 : if (!lName.empty()) {
149 0 : w.where(Operator::And, SqlQuery::Field(ctx.scheme->getName(), lName), Comparation::Equal, oid);
150 : }
151 250 : ctx._this->writeWhere(w, Operator::And, *ctx.scheme, *ctx.query);
152 5775 : } else if (ctx.query->hasSelect() || !lName.empty()) {
153 5775 : auto w = lName.empty() ? tmp.where() : tmp.where(SqlQuery::Field(ctx.scheme->getName(), lName), Comparation::Equal, oid);
154 5775 : ctx._this->writeWhere(w, Operator::And, *ctx.scheme, *ctx.query);
155 : }
156 0 : } else if (ctx.softLimitField.empty()) {
157 0 : if (ctx.query->hasSelect() || !lName.empty()) {
158 0 : auto whi = lName.empty() ? tmp.where() : tmp.where(SqlQuery::Field(ctx.scheme->getName(), lName), Comparation::Equal, oid);
159 0 : ctx._this->writeWhere(whi, db::Operator::And, *ctx.scheme, *ctx.query);
160 : }
161 : } else {
162 0 : auto softLimitFieldStr = (ctx.softLimitIsFts
163 0 : ? toString("__ts_rank_", ctx.softLimitField)
164 : : ctx.softLimitField.str<Interface>());
165 :
166 : // write soft limit query like WHERE __oid IN (SELECT __oid FROM u) OR (field = (SELECT MAX(mtime) FROM u))
167 0 : tmp.where(SqlQuery::Field(ctx.scheme->getName(), "__oid"), Comparation::In, Callback<void(SqlQuery::Select &)>([&] (SqlQuery::Select &subQ) {
168 0 : subQ.field(SqlQuery::Field("u", "__oid")).from("u").finalize();
169 0 : })).parenthesis(Operator::Or, [&] (SqlQuery::WhereBegin &whi) {
170 0 : if (ctx.softLimitIsFts) {
171 0 : auto ftsQuery = ctx.getAlt(ctx.softLimitField);
172 :
173 0 : whi.query->getStream() << '(';
174 0 : whi.query->writeBind(db::Binder::FullTextRank{ctx.scheme->getName(), ctx.scheme->getField(ctx.softLimitField), ftsQuery});
175 0 : whi.query->getStream() << "=(SELECT " << (isAsc ? "MAX" : "MIN") << "(u.\"__ts_rank_" << ctx.softLimitField << "\") FROM u))";
176 :
177 0 : SqlQuery::WhereContinue w(whi.query, SqlQuery::State::Some);
178 0 : if (!lName.empty()) {
179 0 : w.where(Operator::And, SqlQuery::Field(ctx.scheme->getName(), lName), Comparation::Equal, oid);
180 : }
181 0 : ctx._this->writeWhere(w, Operator::And, *ctx.scheme, *ctx.query);
182 : } else {
183 0 : auto w = whi.where(SqlQuery::Field(ctx.scheme->getName(), ctx.softLimitField), Comparation::Equal,
184 0 : Callback<void(SqlQuery::Select &)>([&] (SqlQuery::Select &subQ) {
185 0 : subQ.aggregate(isAsc ? "MAX" : "MIN", SqlQuery::Field("u", softLimitFieldStr)).from("u").finalize();
186 : }));
187 0 : if (!lName.empty()) {
188 0 : w.where(Operator::And, SqlQuery::Field(ctx.scheme->getName(), lName), Comparation::Equal, oid);
189 : }
190 0 : ctx._this->writeWhere(w, Operator::And, *ctx.scheme, *ctx.query);
191 : }
192 : });
193 0 : }
194 : }
195 6350 : }
196 :
197 6200 : bool SqlQuery::writeQuery(Context &ctx) {
198 6200 : auto sel = (ctx.hasAltLimit)
199 0 : ? with("u", SqlQuery_makeSoftLimitWith(ctx, false)).select()
200 6200 : : select();
201 6200 : auto s = writeSelectFrom(sel, ctx);
202 :
203 6200 : SqlQuery_makeWhereClause(_driver, ctx, s);
204 :
205 6200 : writeOrdering(s, *ctx.scheme, *ctx.query, ctx.hasAltLimit);
206 6200 : if (ctx.query->isForUpdate()) { s.forUpdate(); }
207 6200 : s.finalize();
208 :
209 6200 : return true;
210 : }
211 :
212 150 : bool SqlQuery::writeQuery(Context &ctx, const db::Scheme &scheme, uint64_t oid, const db::Field &f) {
213 150 : StringView lName;
214 150 : if (f.getType() == Type::Set && !f.isReference()) {
215 25 : if (auto l = scheme.getForeignLink(f)) {
216 25 : lName = l->getName();
217 : }
218 : }
219 :
220 150 : auto writeFields = [&, this] (Select &sel) {
221 150 : writeFullTextRank(sel, *ctx.scheme, *ctx.query);
222 150 : if (ctx.shouldIncludeAll()) {
223 0 : sel = sel.field(SqlQuery::Field(ctx.scheme->getName(), "*"));
224 : } else {
225 150 : ctx.readFields([&] (const StringView &name, const db::Field *) {
226 150 : sel = sel.field(SqlQuery::Field(ctx.scheme->getName(), name));
227 150 : });
228 : }
229 150 : };
230 :
231 300 : auto writeSelect = [&, this] () -> Select {
232 150 : if (f.getType() == Type::View || (f.getType() == Type::Set && f.isReference())) {
233 125 : auto wtmp = with("s", [&] (SqlQuery::GenericQuery &q) {
234 125 : q.select(SqlQuery::Distinct::Distinct, SqlQuery::Field(toString(ctx.scheme->getName(), "_id")).as("id"))
235 250 : .from(toString(scheme.getName(), "_f_", f.getName(), (f.getType() == Type::View ? "_view" : "")))
236 250 : .where(toString(scheme.getName(), "_id"), Comparation::Equal, oid);
237 125 : });
238 :
239 125 : if (ctx.hasAltLimit) {
240 0 : wtmp.with("u", SqlQuery_makeSoftLimitWith(ctx, true, lName, oid));
241 : }
242 :
243 125 : return wtmp.select();
244 : } else {
245 25 : return (ctx.hasAltLimit)
246 25 : ? with("u", SqlQuery_makeSoftLimitWith(ctx, false, lName, oid)).select()
247 25 : : select();
248 : }
249 150 : };
250 :
251 150 : auto sel = writeSelect();
252 150 : writeFields(sel);
253 :
254 225 : auto tmp = (f.getType() == Type::View || (f.getType() == Type::Set && f.isReference()))
255 200 : ? sel.from(ctx.scheme->getName()).innerJoinOn("s", [&] (SqlQuery::WhereBegin &q) {
256 125 : q.where(SqlQuery::Field(ctx.scheme->getName(), "__oid"), Comparation::Equal, SqlQuery::Field("s", "id"));
257 125 : })
258 150 : : sel.from(ctx.scheme->getName());
259 :
260 150 : SqlQuery_makeWhereClause(_driver, ctx, tmp, lName, oid);
261 :
262 150 : writeOrdering(tmp, *ctx.scheme, *ctx.query, ctx.hasAltLimit);
263 150 : if (ctx.query->isForUpdate()) { tmp.forUpdate(); }
264 150 : finalize();
265 :
266 150 : return true;
267 : }
268 :
269 7775 : void SqlQuery::writeWhere(SqlQuery::SelectWhere &w, db::Operator op, const db::Scheme &scheme, const db::Query &q) {
270 7775 : SqlQuery::WhereContinue iw(w.query, w.state);
271 7775 : writeWhere(iw, op, scheme, q);
272 7775 : }
273 :
274 7775 : void SqlQuery::writeWhere(SqlQuery::WhereContinue &w, db::Operator op, const db::Scheme &scheme, const db::Query &q) {
275 7775 : if (q.getSingleSelectId()) {
276 5900 : w.where(op, "__oid", db::Comparation::Equal, q.getSingleSelectId());
277 1875 : } else if (!q.getSelectIds().empty()) {
278 125 : w.where(op, SqlQuery::Field(scheme.getName(), "__oid"), db::Comparation::In, q.getSelectIds());
279 1750 : } else if (!q.getSelectAlias().empty()) {
280 300 : w.parenthesis(op, [&] (SqlQuery::WhereBegin &wh) {
281 300 : auto whi = wh.where();
282 4175 : for (auto &it : scheme.getFields()) {
283 3875 : if (it.second.getType() == db::Type::Text && it.second.getTransform() == db::Transform::Alias) {
284 300 : whi.where(db::Operator::Or, SqlQuery::Field(scheme.getName(), it.first), db::Comparation::Equal, q.getSelectAlias());
285 : }
286 : }
287 300 : });
288 1450 : } else if (q.hasSelectName()) {
289 : // failsafe
290 0 : w.parenthesis(op, [&] (SqlQuery::WhereBegin &wh) {
291 0 : auto whi = wh.where();
292 0 : whi.where(db::Operator::Or, SqlQuery::Field(scheme.getName(), "__oid"), db::Comparation::Equal, Value(0));
293 0 : });
294 1450 : } else if (q.getSelectList().size() > 0) {
295 1425 : w.parenthesis(op, [&, this] (SqlQuery::WhereBegin &wh) {
296 1425 : auto whi = wh.where();
297 2950 : for (auto &it : q.getSelectList()) {
298 1525 : writeWhereItem(whi, db::Operator::And, scheme, it);
299 : }
300 1425 : });
301 : }
302 7775 : }
303 :
304 1225 : static void SqlQuery_writeWhereData(const Driver *driver, SqlQuery::WhereContinue &whi, db::Operator op, const db::Scheme &scheme, const db::Field &f,
305 : Comparation compare, const Value &value1, const Value &value2) {
306 1225 : if (SqlQuery_comparationIsValid(driver, f, compare)) {
307 1225 : auto type = f.getType();
308 1225 : if (type == Type::Custom) {
309 100 : auto c = f.getSlot<FieldCustom>();
310 100 : if (auto info = driver->getCustomFieldInfo(c->getDriverTypeName())) {
311 100 : info->writeQuery(*c, scheme, whi, op, f.getName(), compare, value1, value2);
312 : }
313 : } else {
314 750 : if ((compare == Comparation::Equal || compare == db::Comparation::NotEqual)
315 375 : && (type == Type::Integer || type == Type::Float || type == Type::Object || type == Type::Text)
316 2250 : && value1.isArray()) {
317 0 : switch (type) {
318 0 : case Type::Integer:
319 : case Type::Object: {
320 0 : Vector<int64_t> vec;
321 0 : for (auto &it : value1.asArray()) {
322 0 : vec.emplace_back(it.getInteger());
323 : }
324 0 : whi.where(op, SqlQuery::Field(scheme.getName(), f.getName()),
325 : (compare == Comparation::Equal) ? Comparation::In : Comparation::NotIn, vec);
326 0 : break;
327 0 : }
328 0 : case Type::Float: {
329 0 : Vector<double> vec;
330 0 : for (auto &it : value1.asArray()) {
331 0 : vec.emplace_back(it.getDouble());
332 : }
333 0 : whi.where(op, SqlQuery::Field(scheme.getName(), f.getName()),
334 : (compare == Comparation::Equal) ? Comparation::In : Comparation::NotIn, vec);
335 0 : break;
336 0 : }
337 0 : case Type::Text: {
338 0 : Vector<StringView> vec;
339 0 : for (auto &it : value1.asArray()) {
340 0 : vec.emplace_back(it.getString());
341 : }
342 0 : whi.where(op, SqlQuery::Field(scheme.getName(), f.getName()),
343 : (compare == Comparation::Equal) ? Comparation::In : Comparation::NotIn, vec);
344 0 : break;
345 0 : }
346 0 : default:
347 0 : break;
348 : }
349 : } else {
350 1125 : whi.where(op, SqlQuery::Field(scheme.getName(), f.getName()), compare, value1, value2);
351 : }
352 : }
353 : } else {
354 0 : driver->getApplicationInterface()->error("Sql", "Condition is not applicable", Value({
355 0 : stappler::pair("scheme", Value(scheme.getName())),
356 0 : stappler::pair("field", Value(f.getName())),
357 0 : stappler::pair("cmp", Value(encodeComparation(compare).first)),
358 0 : }));
359 : }
360 1225 : }
361 :
362 1525 : void SqlQuery::writeWhereItem(SqlQuery::WhereContinue &whi, db::Operator op, const db::Scheme &scheme, const db::Query::Select &sel) {
363 1525 : if (auto f = scheme.getField(sel.field)) {
364 1525 : auto type = f->getType();
365 1525 : if (type == db::Type::FullTextView && sel.compare != db::Comparation::IsNull && sel.compare != db::Comparation::IsNotNull) {
366 300 : auto ftsQuery = getFullTextQuery(scheme, *f, sel);
367 300 : if (!ftsQuery.empty()) {
368 300 : writeFullTextWhere(whi, op, scheme, sel, ftsQuery);
369 : }
370 1525 : } else if (SqlQuery_comparationIsValid(_driver, *f, sel.compare)) {
371 1225 : SqlQuery_writeWhereData(_driver, whi, op, scheme, *f, sel.compare, sel.value1, sel.value2);
372 : }
373 : }
374 1525 : }
375 :
376 0 : void SqlQuery::writeWhereCond(SqlQuery::WhereContinue &whi, db::Operator op, const db::Scheme &scheme, const db::Worker::ConditionData &sel) {
377 0 : SqlQuery_writeWhereData(_driver, whi, op, scheme, *sel.field, sel.compare, sel.value1, sel.value2);
378 0 : }
379 :
380 8325 : void SqlQuery::writeOrdering(SqlQuery::SelectFrom &s, const db::Scheme &scheme, const db::Query &q, bool dropLimits) {
381 8325 : if (q.hasOrder() || q.hasLimit() || q.hasOffset()) {
382 1175 : auto ordering = q.getOrdering();
383 1175 : String orderField;
384 1175 : String schemeName = scheme.getName().str<Interface>();
385 1175 : if (q.hasOrder()) {
386 925 : if (auto f = scheme.getField(q.getOrderField())) {
387 925 : if (f->getType() == db::Type::FullTextView) {
388 275 : orderField = toString("__ts_rank_", q.getOrderField());
389 275 : schemeName.clear();
390 : } else {
391 650 : orderField = q.getOrderField();
392 : }
393 : } else {
394 0 : return;
395 : }
396 250 : } else if (q.getSelectList().size() == 1) {
397 125 : orderField = q.getSelectList().back().field;
398 125 : if (!scheme.getField(orderField)) {
399 0 : return;
400 : }
401 : } else {
402 125 : orderField = "__oid";
403 : }
404 :
405 1175 : SelectOrder o = s.order(ordering, schemeName.empty() ? SqlQuery::Field(orderField) : SqlQuery::Field(scheme.getName(), orderField),
406 : ordering == db::Ordering::Descending ? stappler::sql::Nulls::Last : stappler::sql::Nulls::None);
407 :
408 1175 : if (!dropLimits) {
409 1175 : if (q.hasLimit() && q.hasOffset()) {
410 50 : o.limit(q.getLimitValue(), q.getOffsetValue());
411 1125 : } else if (q.hasLimit()) {
412 825 : o.limit(q.getLimitValue());
413 300 : } else if (q.hasOffset()) {
414 0 : o.offset(q.getOffsetValue());
415 : }
416 : }
417 1175 : }
418 : }
419 :
420 1975 : void SqlQuery::writeQueryReqest(SqlQuery::SelectFrom &s, const db::QueryList::Item &item) {
421 1975 : auto &q = item.query;
422 1975 : if (!item.all && !item.query.empty()) {
423 1550 : SqlQuery_makeCustomFrom(_driver, *this, s, item.query, *item.scheme);
424 :
425 1550 : auto w = s.where();
426 1550 : writeWhere(w, db::Operator::And, *item.scheme, q);
427 : }
428 :
429 1975 : writeOrdering(s, *item.scheme, q);
430 1975 : }
431 :
432 150 : static void SqlQuery_writeJoin(SqlQuery::SelectFrom &s, const StringView &sqName, const StringView &schemeName, const db::QueryList::Item &item) {
433 150 : s.innerJoinOn(sqName, [&] (SqlQuery::WhereBegin &w) {
434 275 : StringView fieldName = item.ref
435 125 : ? ( item.ref->getType() == db::Type::Set ? StringView("__oid") : item.ref->getName() )
436 225 : : StringView("__oid");
437 150 : w.where(SqlQuery::Field(schemeName, fieldName), db::Comparation::Equal, SqlQuery::Field(sqName, "id"));
438 150 : });
439 150 : }
440 :
441 1975 : auto SqlQuery::writeSelectFrom(GenericQuery &q, const db::QueryList::Item &item, bool idOnly,
442 : const StringView &schemeName, const StringView &fieldName, bool isSimpleGet) -> SelectFrom {
443 1975 : if (idOnly) {
444 900 : auto sel = q.select();
445 900 : sel.field(SqlQuery::Field(schemeName, fieldName).as("id"));
446 900 : writeFullTextRank(sel, *item.scheme, item.query);
447 900 : return sel.from(schemeName);
448 : }
449 :
450 1075 : auto sel = q.select();
451 1075 : writeFullTextRank(sel, *item.scheme, item.query);
452 1075 : FieldResolver resv(*item.scheme, item.query, item.getQueryFields());
453 1075 : resv.readFields([&] (const StringView &name, const db::Field *) {
454 1400 : sel = sel.field(SqlQuery::Field(schemeName, name));
455 1400 : }, isSimpleGet);
456 1075 : return sel.from(schemeName);
457 1075 : }
458 :
459 6200 : auto SqlQuery::writeSelectFrom(Select &sel, Context &ctx) -> SelectFrom {
460 6200 : writeFullTextRank(sel, *ctx.scheme, *ctx.query);
461 6200 : ctx.readFields([&] (const StringView &name, const db::Field *) {
462 18700 : sel = sel.field(SqlQuery::Field(name));
463 18700 : });
464 6200 : return sel.from(ctx.scheme->getName());
465 : }
466 :
467 1975 : void SqlQuery::writeQueryListItem(GenericQuery &q, const db::QueryList &list, size_t idx, bool idOnly, const db::Field *field, bool forSubquery) {
468 1975 : auto &items = list.getItems();
469 1975 : const db::QueryList::Item &item = items.at(idx);
470 1975 : const db::Field *sourceField = nullptr;
471 1975 : const db::FieldView *viewField = nullptr;
472 1975 : String refQueryTag;
473 1975 : if (idx > 0) {
474 150 : sourceField = items.at(idx - 1).field;
475 : }
476 :
477 1975 : if (idx > 0 && !item.ref && sourceField && sourceField->getType() != db::Type::Object) {
478 25 : String prevSq = toString("sq", idx - 1);
479 25 : const db::QueryList::Item &prevItem = items.at(idx - 1);
480 :
481 25 : if (sourceField->getType() == db::Type::View) {
482 0 : viewField = static_cast<const db::FieldView *>(sourceField->getSlot());
483 : }
484 : String tname = viewField
485 25 : ? toString(prevItem.scheme->getName(), "_f_", prevItem.field->getName(), "_view")
486 25 : : toString(prevItem.scheme->getName(), "_f_", prevItem.field->getName());
487 :
488 25 : String targetIdField = toString(item.scheme->getName(), "_id");
489 25 : String sourceIdField = toString(prevItem.scheme->getName(), "_id");
490 :
491 25 : if (idOnly && item.query.empty()) { // optimize id-only empty request
492 0 : q.select(SqlQuery::Field(targetIdField).as("id"))
493 0 : .from(tname)
494 0 : .innerJoinOn(prevSq, [&] (WhereBegin &w) {
495 0 : w.where(sourceIdField, stappler::sql::Comparation::Equal, SqlQuery::Field(prevSq, "id"));
496 0 : });
497 0 : return;
498 : }
499 :
500 25 : refQueryTag = toString("sq", idx, "_ref");
501 25 : q.with(refQueryTag, [&] (GenericQuery &sq) {
502 25 : sq.select(SqlQuery::Field(targetIdField).as("id"))
503 50 : .from(tname).innerJoinOn(prevSq, [&] (WhereBegin &w) {
504 50 : w.where(SqlQuery::Field(sourceIdField), stappler::sql::Comparation::Equal, SqlQuery::Field(prevSq, "id"));
505 25 : });
506 25 : });
507 25 : }
508 :
509 1975 : const db::Field * f = field?field:item.field;
510 :
511 1975 : StringView schemeName(item.scheme->getName());
512 675 : StringView fieldName( (f && (
513 850 : (f->getType() == db::Type::Object && (forSubquery || !idOnly || idx + 1 == items.size()))
514 600 : || f->isFile()))
515 2050 : ? f->getName()
516 1975 : : StringView("__oid") );
517 :
518 1975 : auto s = writeSelectFrom(q, item, idOnly, schemeName, fieldName, list.hasFlag(db::QueryList::SimpleGet));
519 1975 : if (idx > 0) {
520 150 : if (refQueryTag.empty()) {
521 125 : SqlQuery_writeJoin(s, toString("sq", idx - 1), item.scheme->getName(), item);
522 : } else {
523 25 : SqlQuery_writeJoin(s, refQueryTag, item.scheme->getName(), item);
524 : }
525 : }
526 1975 : writeQueryReqest(s, item);
527 1975 : }
528 :
529 1900 : void SqlQuery::writeQueryList(const db::QueryList &list, bool idOnly, size_t count) {
530 1900 : const db::QueryList::Item &item = list.getItems().back();
531 1900 : if (item.query.hasDelta() && list.isDeltaApplicable()) {
532 75 : if (!list.isView()) {
533 50 : writeQueryDelta(*item.scheme, stappler::Time::microseconds(item.query.getDeltaToken()), item.fields.getResolves(), false);
534 : } else {
535 25 : writeQueryViewDelta(list, stappler::Time::microseconds(item.query.getDeltaToken()), item.fields.getResolves(), false);
536 : }
537 75 : return;
538 1825 : } else if (item.query.hasDelta()) {
539 0 : list.getApplicationInterface()->error("Query", "Delta is not applicable for this query");
540 : }
541 :
542 1825 : auto &items = list.getItems();
543 1825 : count = std::min(items.size(), count);
544 :
545 1825 : GenericQuery q(this);
546 1825 : size_t i = 0;
547 1825 : if (count > 0) {
548 1975 : for (; i < count - 1; ++ i) {
549 150 : q.with(toString("sq", i), [&, this] (GenericQuery &sq) {
550 150 : writeQueryListItem(sq, list, i, true, nullptr, true);
551 150 : });
552 : }
553 : }
554 :
555 1825 : writeQueryListItem(q, list, i, idOnly, nullptr, false);
556 : }
557 :
558 0 : void SqlQuery::writeQueryFile(const ApplicationInterface *app, const db::QueryList &list, const db::Field *field) {
559 0 : auto &items = list.getItems();
560 0 : auto count = items.size();
561 0 : GenericQuery q(this);
562 0 : for (size_t i = 0; i < count - 1; ++ i) {
563 0 : q.with(toString("sq", i), [&, this] (GenericQuery &sq) {
564 0 : writeQueryListItem(sq, list, i, true);
565 0 : });
566 : }
567 :
568 0 : q.with(toString("sq", count - 1), [&, this] (GenericQuery &sq) {
569 0 : writeQueryListItem(sq, list, count - 1, true, field);
570 0 : });
571 :
572 0 : auto fileScheme = app->getFileScheme();
573 0 : q.select(SqlQuery::Field::all(fileScheme->getName()))
574 0 : .from(fileScheme->getName())
575 0 : .innerJoinOn(toString("sq", count - 1), [&] (SqlQuery::WhereBegin &w) {
576 0 : w.where(SqlQuery::Field(fileScheme->getName(), "__oid"), db::Comparation::Equal, SqlQuery::Field(toString("sq", count - 1), "id"));
577 0 : });
578 0 : }
579 :
580 0 : void SqlQuery::writeQueryArray(const db::QueryList &list, const db::Field *field) {
581 0 : auto &items = list.getItems();
582 0 : auto count = items.size();
583 0 : GenericQuery q(this);
584 0 : for (size_t i = 0; i < count; ++ i) {
585 0 : q.with(toString("sq", i), [&, this] (GenericQuery &sq) {
586 0 : writeQueryListItem(sq, list, i, true);
587 0 : });
588 : }
589 :
590 0 : auto scheme = items.back().scheme;
591 :
592 0 : q.select(SqlQuery::Field("t", "data"))
593 0 : .from(SqlQuery::Field(toString(scheme->getName(), "_f_", field->getName())).as("t"))
594 0 : .innerJoinOn(toString("sq", count - 1), [&] (SqlQuery::WhereBegin &w) {
595 0 : w.where(SqlQuery::Field("t", toString(scheme->getName(), "_id")), db::Comparation::Equal, SqlQuery::Field(toString("sq", count - 1), "id"));
596 0 : });
597 0 : }
598 :
599 75 : void SqlQuery::writeQueryDelta(const db::Scheme &scheme, const stappler::Time &time, const Set<const db::Field *> &fields, bool idOnly) {
600 75 : GenericQuery q(this);
601 75 : auto s = q.with("d", [&] (SqlQuery::GenericQuery &sq) {
602 75 : sq.select()
603 75 : .aggregate("max", Field("time").as("time"))
604 75 : .aggregate("max", Field("action").as("action"))
605 75 : .field("object")
606 75 : .from(SqlHandle::getNameForDelta(scheme))
607 75 : .where("time", db::Comparation::GreatherThen, time.toMicroseconds())
608 75 : .group("object")
609 75 : .order(db::Ordering::Descending, "time");
610 150 : }).select();
611 75 : if (!idOnly) {
612 75 : FieldResolver resv(scheme, fields);
613 75 : resv.readFields([&] (const StringView &name, const db::Field *field) {
614 75 : s.field(SqlQuery::Field("t", name));
615 75 : });
616 75 : } else {
617 0 : s.field(Field("t", "__oid"));
618 : }
619 75 : s.fields(Field("d", "action").as("__d_action"), Field("d", "time").as("__d_time"), Field("d", "object").as("__d_object"))
620 75 : .from(Field(scheme.getName()).as("t"))
621 75 : .rightJoinOn("d", [&] (SqlQuery::WhereBegin &w) {
622 75 : w.where(Field("d", "object"), db::Comparation::Equal, Field("t", "__oid"));
623 75 : });
624 75 : }
625 :
626 50 : void SqlQuery::writeQueryViewDelta(const db::QueryList &list, const stappler::Time &time, const Set<const db::Field *> &fields, bool idOnly) {
627 50 : auto &items = list.getItems();
628 50 : const db::QueryList::Item &item = items.back();
629 50 : auto prevScheme = items.size() > 1 ? items.at(items.size() - 2).scheme : nullptr;
630 50 : auto viewField = items.size() > 1 ? items.at(items.size() - 2).field : items.back().field;
631 50 : auto view = static_cast<const db::FieldView *>(viewField->getSlot());
632 :
633 50 : GenericQuery q(this);
634 50 : const db::Scheme &scheme = *item.scheme;
635 50 : String deltaName = toString(prevScheme->getName(), "_f_", view->name, "_delta");
636 50 : String viewName = toString(prevScheme->getName(), "_f_", view->name, "_view");
637 50 : auto s = q.with("dv", [&] (SqlQuery::GenericQuery &sq) {
638 50 : uint64_t id = 0;
639 50 : String sqName;
640 : // optimize id-only
641 50 : if (items.size() != 2 || items.front().query.getSingleSelectId() == 0) {
642 0 : size_t i = 0;
643 0 : for (; i < items.size() - 1; ++ i) {
644 0 : sq.with(toString("sq", i), [&, this] (GenericQuery &sq) {
645 0 : writeQueryListItem(sq, list, i, true);
646 0 : });
647 : }
648 0 : sqName = toString("sq", i - 1);
649 : } else {
650 50 : id = items.front().query.getSingleSelectId();
651 : }
652 :
653 50 : sq.with("d", [&] (SqlQuery::GenericQuery &sq) {
654 50 : if (id) {
655 50 : sq.select()
656 50 : .aggregate("max", Field("time").as("time"))
657 50 : .field("object")
658 50 : .field("tag")
659 50 : .from(deltaName)
660 50 : .where(SqlQuery::Field("tag"), db::Comparation::Equal, id)
661 100 : .where(db::Operator::And, "time", db::Comparation::GreatherThen, time.toMicroseconds())
662 50 : .group("object").field("tag");
663 : } else {
664 0 : sq.select()
665 0 : .aggregate("max", Field("time").as("time"))
666 0 : .field("object")
667 0 : .field(Field(sqName, "id").as("tag"))
668 0 : .from(deltaName)
669 0 : .innerJoinOn(sqName, [&] (SqlQuery::WhereBegin &w) {
670 0 : w.where(SqlQuery::Field(deltaName, "tag"), db::Comparation::Equal, SqlQuery::Field(sqName, "id"));
671 0 : })
672 0 : .where("time", db::Comparation::GreatherThen, time.toMicroseconds())
673 0 : .group("object").field(SqlQuery::Field(sqName, "id"));;
674 : }
675 100 : }).select().fields(Field("d", "time"), Field("d", "object"), Field("__vid"))
676 100 : .from(viewName).rightJoinOn("d", [&] (SqlQuery::WhereBegin &w) {
677 50 : w.where(SqlQuery::Field("d", "tag"), db::Comparation::Equal, SqlQuery::Field(viewName, toString(prevScheme->getName(), "_id")))
678 100 : .where(db::Operator::And, SqlQuery::Field("d", "object"), db::Comparation::Equal, SqlQuery::Field(viewName, toString(scheme.getName(), "_id")));
679 50 : });
680 100 : }).select();
681 :
682 50 : if (!idOnly) {
683 50 : FieldResolver resv(scheme, fields);
684 50 : resv.readFields([&] (const StringView &name, const db::Field *field) {
685 50 : s.field(SqlQuery::Field("t", name));
686 50 : });
687 50 : } else {
688 0 : s.field(Field("t", "__oid"));
689 : }
690 50 : s.fields(Field("dv", "time").as("__d_time"), Field("dv", "object").as("__d_object"), Field("dv", "__vid"))
691 50 : .from(Field(view->scheme->getName()).as("t"))
692 50 : .rightJoinOn("dv", [&] (SqlQuery::WhereBegin &w) {
693 50 : w.where(Field("dv", "object"), Comparation::Equal, Field("t", "__oid"));
694 50 : });
695 50 : }
696 :
697 18850 : const StringStream &SqlQuery::getQuery() const {
698 18850 : return stream;
699 : }
700 :
701 22750 : db::QueryInterface * SqlQuery::getInterface() const {
702 22750 : return binder.getInterface();
703 : }
704 :
705 300 : void SqlQuery::writeFullTextFrom(SelectFrom &sel, const Scheme &scheme, const db::Field *f, const db::Query::Select &it) {
706 300 : auto ftsQuery = getFullTextQuery(scheme, *f, it);
707 300 : if (!ftsQuery.empty()) {
708 300 : sel.query->writeBind(db::Binder::FullTextFrom{scheme.getName(), f, ftsQuery});
709 : }
710 300 : }
711 :
712 8325 : void SqlQuery::writeFullTextRank(Select &sel, const db::Scheme &scheme, const db::Query &q) {
713 8325 : Set<const db::Field *> fields;
714 9600 : for (auto &it : q.getSelectList()) {
715 1275 : if (auto f = scheme.getField(it.field)) {
716 1275 : if (f->getType() == db::Type::FullTextView) {
717 300 : if (fields.find(f) == fields.end()) {
718 300 : fields.emplace(f);
719 300 : auto ftsQuery = getFullTextQuery(scheme, *f, it);
720 300 : if (!ftsQuery.empty()) {
721 300 : if (sel.state == SqlQuery::State::Some) {
722 0 : sel.query->getStream() << ", ";
723 : }
724 300 : sel.query->writeBind(db::Binder::FullTextRank{scheme.getName(), f, ftsQuery});
725 300 : sel.query->getStream() << " AS __ts_rank_" << it.field;
726 300 : sel.state = SqlQuery::State::Some;
727 : }
728 : }
729 : }
730 : }
731 : }
732 8325 : }
733 :
734 50 : void SqlQuery::writeFullTextWhere(WhereContinue &whi, db::Operator op, const db::Scheme &scheme, const db::Query::Select &sel, StringView ftsQuery) {
735 50 : whi.where(op, SqlQuery::Field(scheme.getName(), sel.field), Comparation::Includes, RawStringView{ftsQuery});
736 50 : }
737 :
738 900 : StringView SqlQuery::getFullTextQuery(const Scheme &scheme, const db::Field &f, const db::Query::Select &it) {
739 900 : if (f.getType() != Type::FullTextView) {
740 0 : return StringView();
741 : }
742 :
743 900 : String key = toString(scheme.getName(), ":", f.getName());
744 :
745 900 : auto fit = _fulltextQueries.find(key);
746 900 : if (fit != _fulltextQueries.end()) {
747 600 : return fit->second;
748 : }
749 :
750 300 : if (!it.textQuery.empty()) {
751 275 : StringStream queryFrom;
752 275 : binder.writeBind(queryFrom, Binder::FullTextQueryRef{scheme.getName(), &f, it.textQuery});
753 275 : return _fulltextQueries.emplace(std::move(key), queryFrom.str()).first->second;
754 300 : } else if (it.value1) {
755 25 : auto d = f.getSlot<db::FieldFullTextView>();
756 25 : auto q = d->parseQuery(it.value1);
757 25 : if (!q.empty()) {
758 25 : auto &it = _parsedQueries.emplace_front(move(q));
759 25 : StringStream queryFrom;
760 25 : binder.writeBind(queryFrom, Binder::FullTextQueryRef{scheme.getName(), &f, it});
761 25 : return _fulltextQueries.emplace(std::move(key), queryFrom.str()).first->second;
762 25 : }
763 25 : }
764 :
765 0 : return StringView();
766 900 : }
767 :
768 :
769 6350 : SqlQuery::Context::Context(SqlQuery &sql, const Scheme &s, const Worker &w, const db::Query &q)
770 6350 : : FieldResolver(s, w, q) {
771 6350 : _this = &sql;
772 :
773 6350 : hasAltLimit = false;
774 6350 : if (query->isSoftLimit()) {
775 425 : auto &field = query->getOrderField();
776 425 : auto f = scheme->getField(field);
777 425 : if (field == "__oid") {
778 300 : softLimitField = field;
779 125 : } else if (f) {
780 125 : softLimitField = f->getName();
781 125 : hasAltLimit = (f->getType() == Type::FullTextView || !f->hasFlag(Flags::Unique));
782 125 : softLimitIsFts = (f->getType() == Type::FullTextView);
783 : } else {
784 0 : sql._driver->getApplicationInterface()->error("SqlQuery", "Invalid soft limit field", Value(field));
785 : }
786 : }
787 6350 : }
788 :
789 0 : StringView SqlQuery::Context::getAlt(StringView key) {
790 0 : auto f = scheme->getField(key);
791 0 : for (auto &it : query->getSelectList()) {
792 0 : if (it.field == key) {
793 0 : return _this->getFullTextQuery(*scheme, *f, it);
794 : }
795 : }
796 0 : return StringView();
797 : }
798 :
799 : }
|
