Line data Source code
1 : /**
2 : Copyright (c) 2016-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 "SPPqHandle.h"
25 :
26 : namespace STAPPLER_VERSIONIZED stappler::db::pq {
27 :
28 : using RemovePolicy = db::RemovePolicy;
29 :
30 : struct ConstraintRec {
31 : enum Type {
32 : Unique,
33 : Reference,
34 : };
35 :
36 : Type type;
37 : Vector<String> fields;
38 : String reference;
39 : db::RemovePolicy remove = db::RemovePolicy::Null;
40 :
41 0 : ConstraintRec(Type t) : type(t) { }
42 0 : ConstraintRec(Type t, std::initializer_list<String> il) : type(t), fields(il) { }
43 825 : ConstraintRec(Type t, const String &col, StringView ref = StringView(), db::RemovePolicy r = db::RemovePolicy::Null)
44 1650 : : type(t), fields{col}, reference(ref.str<Interface>()), remove(r) { }
45 : };
46 :
47 : struct ColRec {
48 : using Type = BackendInterface::StorageType;
49 :
50 : Type type = Type::Unknown;
51 : String custom;
52 : bool notNull = false;
53 : bool serial = false;
54 : int64_t oid = 0;
55 :
56 2550 : ColRec(Type t, bool notNull = false, bool serial = false) : type(t), notNull(notNull), serial(serial) { }
57 375 : ColRec(Type t, int64_t oid, bool notNull = false, bool serial = false) : type(t), notNull(notNull), serial(serial), oid(oid) { }
58 0 : ColRec(const StringView &t, bool notNull = false) : custom(t.str<Interface>()), notNull(notNull) { }
59 150 : ColRec(const StringView &t, int64_t oid, bool notNull = false) : custom(t.str<Interface>()), notNull(notNull), oid(oid) { }
60 : };
61 :
62 : struct TableRec {
63 : using Scheme = db::Scheme;
64 :
65 : static Map<StringView, TableRec> parse(const Driver *driver, const BackendInterface::Config &cfg,
66 : const Map<StringView, const Scheme *> &s, const Vector<Pair<StringView, int64_t>> &);
67 : static Map<StringView, TableRec> get(Handle &h, StringStream &stream);
68 :
69 : static void writeCompareResult(StringStream &stream,
70 : Map<StringView, TableRec> &required, Map<StringView, TableRec> &existed,
71 : const Map<StringView, const db::Scheme *> &s);
72 :
73 : TableRec();
74 : TableRec(const Driver *d, const BackendInterface::Config &cfg, const db::Scheme *scheme,
75 : const Vector<Pair<StringView, int64_t>> &customs);
76 :
77 : Map<String, ColRec> cols;
78 : Map<String, ConstraintRec> constraints;
79 : Map<String, String> indexes;
80 : Vector<String> pkey;
81 : Set<String> triggers;
82 : bool objects = true;
83 :
84 : bool exists = false;
85 : bool valid = false;
86 :
87 : const db::Scheme *viewScheme = nullptr;
88 : const db::FieldView *viewField = nullptr;
89 : };
90 :
91 :
92 775 : constexpr static uint32_t getDefaultFunctionVersion() { return 10; }
93 :
94 : constexpr static const char * DATABASE_DEFAULTS = R"Sql(
95 : CREATE TABLE IF NOT EXISTS __objects (
96 : __oid bigserial NOT NULL,
97 : CONSTRAINT __objects_pkey PRIMARY KEY (__oid)
98 : ) WITH ( OIDS=FALSE );
99 :
100 : CREATE TABLE IF NOT EXISTS __removed (
101 : __oid bigint NOT NULL,
102 : CONSTRAINT __removed_pkey PRIMARY KEY (__oid)
103 : ) WITH ( OIDS=FALSE );
104 :
105 : CREATE TABLE IF NOT EXISTS __sessions (
106 : name bytea NOT NULL,
107 : mtime bigint NOT NULL,
108 : maxage bigint NOT NULL,
109 : data bytea,
110 : CONSTRAINT __sessions_pkey PRIMARY KEY (name)
111 : ) WITH ( OIDS=FALSE );
112 :
113 : CREATE TABLE IF NOT EXISTS __broadcasts (
114 : id bigserial NOT NULL,
115 : date bigint NOT NULL,
116 : msg bytea,
117 : CONSTRAINT __broadcasts_pkey PRIMARY KEY (id)
118 : ) WITH ( OIDS=FALSE );
119 : CREATE INDEX IF NOT EXISTS __broadcasts_date ON __broadcasts ("date" DESC);
120 :
121 : CREATE TABLE IF NOT EXISTS __login (
122 : id bigserial NOT NULL,
123 : "user" bigint NOT NULL,
124 : name text NOT NULL,
125 : password bytea NOT NULL,
126 : date bigint NOT NULL,
127 : success boolean NOT NULL,
128 : addr inet,
129 : host text,
130 : path text,
131 : CONSTRAINT __login_pkey PRIMARY KEY (id)
132 : ) WITH ( OIDS=FALSE );
133 : CREATE INDEX IF NOT EXISTS __login_user ON __login ("user");
134 : CREATE INDEX IF NOT EXISTS __login_date ON __login (date);
135 :
136 : CREATE EXTENSION IF NOT EXISTS intarray;
137 : CREATE EXTENSION IF NOT EXISTS pg_trgm;
138 : )Sql";
139 :
140 : constexpr static const char * INDEX_QUERY = R"Sql(
141 : WITH tables AS (SELECT table_name AS name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE')
142 : SELECT pg_class.relname as table_name, i.relname as index_name, array_to_string(array_agg(a.attname), ', ') as column_names
143 : FROM pg_class INNER JOIN tables ON (tables.name = pg_class.relname), pg_class i, pg_index ix, pg_attribute a
144 : WHERE pg_class.oid = ix.indrelid
145 : AND i.oid = ix.indexrelid
146 : AND a.attrelid = pg_class.oid
147 : AND a.attnum = ANY(ix.indkey)
148 : AND pg_class.relkind = 'r'
149 : GROUP BY pg_class.relname, i.relname ORDER BY pg_class.relname, i.relname;)Sql";
150 :
151 : constexpr static const char * COL_QUERY = R"Sql(
152 : SELECT table_name, column_name, is_nullable::text, data_type, atttypid::integer as col_oid, pg_class.oid::integer as table_oid, attname
153 : FROM information_schema.columns
154 : INNER JOIN pg_class ON (table_name = relname)
155 : INNER JOIN pg_attribute ON (attrelid = pg_class.oid AND pg_attribute.attname = column_name)
156 : WHERE table_schema='public';)Sql";
157 :
158 250 : static void writeFileUpdateTrigger(StringStream &stream, const db::Scheme *s, const db::Field &obj) {
159 0 : stream << "\t\tIF (NEW.\"" << obj.getName() << "\" IS NULL OR OLD.\"" << obj.getName() << "\" <> NEW.\"" << obj.getName() << "\") THEN\n"
160 0 : << "\t\t\tIF (OLD.\"" << obj.getName() << "\" IS NOT NULL) THEN\n"
161 0 : << "\t\t\t\tINSERT INTO __removed (__oid) VALUES (OLD.\"" << obj.getName() << "\");\n"
162 250 : << "\t\t\tEND IF;\n\t\tEND IF;\n";
163 250 : }
164 :
165 250 : static void writeFileRemoveTrigger(StringStream &stream, const db::Scheme *s, const db::Field &obj) {
166 500 : stream << "\t\tIF (OLD.\"" << obj.getName() << "\" IS NOT NULL) THEN\n"
167 0 : << "\t\t\tINSERT INTO __removed (__oid) VALUES (OLD.\"" << obj.getName() << "\");\n"
168 250 : << "\t\tEND IF;\n";
169 250 : }
170 :
171 50 : static void writeObjectSetRemoveTrigger(StringStream &stream, const db::Scheme *s, const db::FieldObject *obj) {
172 50 : auto source = s->getName();
173 50 : auto target = obj->scheme->getName();
174 :
175 : stream << "\t\tDELETE FROM " << target << " WHERE __oid IN (SELECT " << target << "_id FROM "
176 50 : << s->getName() << "_f_" << obj->name << " WHERE "<< source << "_id=OLD.__oid);\n";
177 50 : }
178 :
179 0 : static void writeObjectUpdateTrigger(StringStream &stream, const db::Scheme *s, const db::FieldObject *obj) {
180 0 : auto target = obj->scheme->getName();
181 :
182 0 : stream << "\t\tIF (NEW.\"" << obj->getName() << "\" IS NULL OR OLD.\"" << obj->getName() << "\" <> NEW.\"" << obj->getName() << "\") THEN\n"
183 0 : << "\t\t\tIF (OLD.\"" << obj->getName() << "\" IS NOT NULL) THEN\n"
184 0 : << "\t\t\t\tDELETE FROM " << target << " WHERE __oid=OLD." << obj->getName() << ";\n";
185 0 : stream << "\t\t\tEND IF;\n\t\tEND IF;\n";
186 0 : }
187 :
188 0 : static void writeObjectRemoveTrigger(StringStream &stream, const db::Scheme *s, const db::FieldObject *obj) {
189 0 : auto target = obj->scheme->getName();
190 :
191 0 : stream << "\t\tIF (OLD.\"" << obj->getName() << "\" IS NOT NULL) THEN\n"
192 0 : << "\t\t\tDELETE FROM " << target << " WHERE __oid=OLD." << obj->getName() << ";\n";
193 0 : stream << "\t\tEND IF;\n";
194 0 : }
195 :
196 100 : static void writeAfterTrigger(StringStream &stream, const db::Scheme *s, const String &triggerName) {
197 100 : auto &fields = s->getFields();
198 :
199 75 : auto writeInsertDelta = [&] (DeltaAction a) {
200 75 : if (a == DeltaAction::Create || a == DeltaAction::Update) {
201 50 : stream << "\t\tINSERT INTO " << Handle::getNameForDelta(*s) << "(\"object\",\"action\",\"time\",\"user\")"
202 100 : "VALUES(NEW.__oid," << stappler::toInt(a) << ",current_setting('serenity.now')::bigint,current_setting('serenity.user')::bigint);\n";
203 : } else {
204 25 : stream << "\t\tINSERT INTO " << Handle::getNameForDelta(*s) << "(\"object\",\"action\",\"time\",\"user\")"
205 25 : "VALUES(OLD.__oid," << stappler::toInt(a) << ",current_setting('serenity.now')::bigint,current_setting('serenity.user')::bigint);\n";
206 : }
207 75 : };
208 :
209 : stream << "CREATE OR REPLACE FUNCTION " << triggerName << "_func() RETURNS TRIGGER AS $" << triggerName
210 100 : << "$ BEGIN\n\tIF (TG_OP = 'INSERT') THEN\n";
211 100 : if (s->hasDelta()) {
212 25 : writeInsertDelta(DeltaAction::Create);
213 : }
214 100 : stream << "\tELSIF (TG_OP = 'UPDATE') THEN\n";
215 875 : for (auto &it : fields) {
216 775 : if (it.second.isFile()) {
217 250 : writeFileUpdateTrigger(stream, s, it.second);
218 525 : } else if (it.second.getType() == db::Type::Object) {
219 25 : const db::FieldObject *objSlot = static_cast<const db::FieldObject *>(it.second.getSlot());
220 25 : if (objSlot->onRemove == db::RemovePolicy::StrongReference) {
221 0 : writeObjectUpdateTrigger(stream, s, objSlot);
222 : }
223 : }
224 : }
225 100 : if (s->hasDelta()) {
226 25 : writeInsertDelta(DeltaAction::Update);
227 : }
228 100 : stream << "\tELSIF (TG_OP = 'DELETE') THEN\n";
229 875 : for (auto &it : fields) {
230 775 : if (it.second.isFile()) {
231 250 : writeFileRemoveTrigger(stream, s, it.second);
232 525 : } else if (it.second.getType() == db::Type::Object) {
233 25 : const db::FieldObject *objSlot = static_cast<const db::FieldObject *>(it.second.getSlot());
234 25 : if (objSlot->onRemove == db::RemovePolicy::StrongReference) {
235 0 : writeObjectRemoveTrigger(stream, s, objSlot);
236 : }
237 : }
238 : }
239 100 : if (s->hasDelta()) {
240 25 : writeInsertDelta(DeltaAction::Delete);
241 : }
242 100 : stream << "\tEND IF;\n\tRETURN NULL;\n";
243 100 : stream << "\nEND; $" << triggerName << "$ LANGUAGE plpgsql;\n";
244 :
245 0 : stream << "CREATE TRIGGER " << triggerName << " AFTER INSERT OR UPDATE OR DELETE ON \"" << s->getName()
246 100 : << "\" FOR EACH ROW EXECUTE PROCEDURE " << triggerName << "_func();\n";
247 100 : }
248 :
249 50 : static void writeBeforeTrigger(StringStream &stream, const db::Scheme *s, const String &triggerName) {
250 50 : auto &fields = s->getFields();
251 :
252 : stream << "CREATE OR REPLACE FUNCTION " << triggerName << "_func() RETURNS TRIGGER AS $" << triggerName
253 50 : << "$ BEGIN\n\tIF (TG_OP = 'DELETE') THEN\n";
254 :
255 675 : for (auto &it : fields) {
256 625 : if (it.second.getType() == db::Type::Set) {
257 100 : const db::FieldObject *objSlot = static_cast<const db::FieldObject *>(it.second.getSlot());
258 100 : if (objSlot->onRemove == db::RemovePolicy::StrongReference) {
259 50 : writeObjectSetRemoveTrigger(stream, s, objSlot);
260 : }
261 : }
262 : }
263 :
264 50 : stream << "\tEND IF;\n\tRETURN OLD;\n";
265 50 : stream << "\nEND; $" << triggerName << "$ LANGUAGE plpgsql;\n";
266 :
267 100 : stream << "CREATE TRIGGER " << triggerName << " BEFORE DELETE ON \"" << s->getName()
268 50 : << "\" FOR EACH ROW EXECUTE PROCEDURE " << triggerName << "_func();\n";
269 50 : }
270 :
271 25 : static void writeDeltaTrigger(StringStream &stream, const StringView &name, const TableRec &s, const StringView &triggerName) {
272 25 : String deltaName = toString(name.sub(0, name.size() - 5), "_delta");
273 25 : String tagField = toString(s.viewScheme->getName(), "_id");
274 25 : String objField = toString(s.viewField->scheme->getName(), "_id");
275 :
276 : stream << "CREATE OR REPLACE FUNCTION " << triggerName << "_func() RETURNS TRIGGER AS $" << triggerName
277 : << "$ BEGIN\n"
278 25 : "\tIF (TG_OP = 'INSERT') THEN\n";
279 :
280 :
281 : stream << "\tINSERT INTO " << deltaName << " (\"tag\", \"object\", \"time\", \"user\") VALUES("
282 : "NEW.\"" << tagField << "\",NEW.\"" << objField << "\","
283 25 : "current_setting('serenity.now')::bigint,current_setting('serenity.user')::bigint);\n";
284 :
285 :
286 25 : stream << "\tELSIF (TG_OP = 'UPDATE') THEN\n";
287 :
288 :
289 : stream << "\tINSERT INTO " << deltaName << " (\"tag\", \"object\", \"time\", \"user\") VALUES("
290 : "OLD.\"" << tagField << "\",OLD.\"" << objField << "\","
291 25 : "current_setting('serenity.now')::bigint,current_setting('serenity.user')::bigint);\n";
292 :
293 :
294 25 : stream << "\tELSIF (TG_OP = 'DELETE') THEN\n";
295 :
296 :
297 : stream << "\tINSERT INTO " << deltaName << " (\"tag\", \"object\", \"time\", \"user\") VALUES("
298 : "OLD.\"" << tagField << "\",OLD.\"" << objField << "\","
299 25 : "current_setting('serenity.now')::bigint,current_setting('serenity.user')::bigint);\n";
300 :
301 :
302 : stream << "\tEND IF;\n"
303 : "\tRETURN NULL;\n"
304 25 : "END; $" << triggerName << "$ LANGUAGE plpgsql;\n";
305 : stream << "CREATE TRIGGER " << triggerName << " AFTER INSERT OR UPDATE OR DELETE ON \"" << name
306 25 : << "\" FOR EACH ROW EXECUTE PROCEDURE " << triggerName << "_func();\n";
307 25 : }
308 :
309 25 : void TableRec::writeCompareResult(StringStream &stream,
310 : Map<StringView, TableRec> &required, Map<StringView, TableRec> &existed,
311 : const Map<StringView, const db::Scheme *> &s) {
312 150 : for (auto &ex_it : existed) {
313 125 : auto req_it = required.find(ex_it.first);
314 125 : if (req_it != required.end()) {
315 0 : auto &req_t = req_it->second;
316 0 : auto &ex_t = ex_it.second;
317 0 : req_t.exists = true;
318 :
319 0 : for (auto &ex_idx_it : ex_t.indexes) {
320 0 : auto req_idx_it = req_t.indexes.find(ex_idx_it.first);
321 0 : if (req_idx_it == req_t.indexes.end()) {
322 : // index is not required any more, drop it
323 0 : stream << "DROP INDEX IF EXISTS \"" << ex_idx_it.first << "\";\n";
324 : } else {
325 0 : req_t.indexes.erase(req_idx_it);
326 : }
327 : }
328 :
329 0 : for (auto &ex_cst_it : ex_t.constraints) {
330 0 : auto req_cst_it = req_t.constraints.find(ex_cst_it.first);
331 0 : if (req_cst_it == req_t.constraints.end()) {
332 : // constraint is not required any more, drop it
333 0 : stream << "ALTER TABLE " << ex_it.first << " DROP CONSTRAINT IF EXISTS \"" << ex_cst_it.first << "\";\n";
334 : } else {
335 0 : req_t.constraints.erase(req_cst_it);
336 : }
337 : }
338 :
339 0 : for (auto &ex_col_it : ex_t.cols) {
340 0 : auto req_col_it = req_t.cols.find(ex_col_it.first);
341 0 : if (req_col_it == req_t.cols.end()) {
342 0 : stream << "ALTER TABLE " << ex_it.first << " DROP COLUMN IF EXISTS \"" << ex_col_it.first << "\";\n";
343 : } else {
344 0 : auto &req_col = req_col_it->second;
345 0 : auto &ex_col = ex_col_it.second;
346 :
347 0 : auto req_type = req_col.type;
348 :
349 0 : if (req_type != ex_col.type) {
350 0 : stream << "ALTER TABLE " << ex_it.first << " DROP COLUMN IF EXISTS \"" << ex_col_it.first << "\";\n";
351 0 : } else if (ex_col.type == ColRec::Type::Unknown && req_type == ColRec::Type::Unknown
352 0 : && ((req_col.oid && ex_col.oid != req_col.oid) || (!req_col.oid && ex_col.custom != req_col.custom))) {
353 0 : stream << "ALTER TABLE " << ex_it.first << " DROP COLUMN IF EXISTS \"" << ex_col_it.first << "\";\n";
354 : } else {
355 0 : if (ex_col.notNull != req_col.notNull) {
356 0 : if (ex_col.notNull) {
357 0 : stream << "ALTER TABLE " << ex_it.first << " ALTER COLUMN \"" << ex_col_it.first << "\" DROP NOT NULL;\n";
358 : } else {
359 0 : stream << "ALTER TABLE " << ex_it.first << " ALTER COLUMN \"" << ex_col_it.first << "\" SET NOT NULL;\n";
360 : }
361 : }
362 0 : req_t.cols.erase(req_col_it);
363 : }
364 : }
365 : }
366 :
367 0 : for (auto &ex_tgr_it : ex_t.triggers) {
368 0 : auto req_tgr_it = req_t.triggers.find(ex_tgr_it);
369 0 : if (req_tgr_it == req_t.triggers.end()) {
370 0 : stream << "DROP TRIGGER IF EXISTS \"" << ex_tgr_it << "\" ON \"" << ex_it.first << "\";\n";
371 0 : stream << "DROP FUNCTION IF EXISTS \"" << ex_tgr_it << "_func\"();\n";
372 : } else {
373 0 : req_t.triggers.erase(ex_tgr_it);
374 : }
375 : }
376 : }
377 : }
378 :
379 : // write table structs
380 600 : for (auto &it : required) {
381 575 : auto &t = it.second;
382 575 : if (!it.second.exists) {
383 575 : stream << "CREATE TABLE IF NOT EXISTS " << it.first << " (\n";
384 :
385 575 : bool first = true;
386 3250 : for (auto cit = t.cols.begin(); cit != t.cols.end(); cit ++) {
387 2675 : if (first) { first = false; } else { stream << ",\n"; }
388 2675 : stream << "\t\"" << cit->first << "\" ";
389 2675 : if (cit->second.serial) {
390 125 : stream << "bigserial";
391 : } else {
392 2550 : switch(cit->second.type) {
393 125 : case ColRec::Type::Unknown: stream << cit->second.custom; break;
394 125 : case ColRec::Type::Bool: stream << "boolean"; break;
395 0 : case ColRec::Type::Char: stream << "\"char\""; break;
396 0 : case ColRec::Type::Float4: stream << "real"; break;
397 25 : case ColRec::Type::Float8: stream << "double precision"; break;
398 0 : case ColRec::Type::Int2: stream << "smallint"; break;
399 0 : case ColRec::Type::Int4: stream << "integer"; break;
400 1250 : case ColRec::Type::Int8: stream << "bigint"; break;
401 675 : case ColRec::Type::Text: stream << "text"; break;
402 0 : case ColRec::Type::VarChar: stream << "varchar"; break;
403 0 : case ColRec::Type::Numeric: stream << "numeric"; break;
404 325 : case ColRec::Type::Bytes: stream << "bytea"; break;
405 25 : case ColRec::Type::TsVector:stream << "tsvector"; break;
406 0 : default: break;
407 : }
408 : }
409 :
410 2675 : if (cit->second.notNull) {
411 625 : stream << " NOT NULL";
412 : }
413 : }
414 :
415 575 : first = true;
416 575 : if (!t.pkey.empty()) {
417 575 : stream << ",\n\tPRIMARY KEY (";
418 1225 : for (auto &key : t.pkey) {
419 650 : if (first) { first = false; } else { stream << ", "; }
420 650 : stream << "\"" << key << "\"";
421 : }
422 575 : stream << ")";
423 : }
424 :
425 575 : stream << "\n)";
426 575 : if (it.second.objects) {
427 375 : stream << " INHERITS (__objects)";
428 : }
429 575 : stream << " WITH ( OIDS=FALSE );\n\n";
430 : } else {
431 0 : for (auto cit : t.cols) {
432 0 : if (cit.first != "__oid") {
433 0 : stream << "ALTER TABLE " << it.first << " ADD COLUMN \"" << cit.first << "\" ";
434 0 : if (cit.second.serial) {
435 0 : stream << "bigserial";
436 : } else {
437 0 : switch(cit.second.type) {
438 0 : case ColRec::Type::Unknown: stream << cit.second.custom; break;
439 0 : case ColRec::Type::Bool: stream << "boolean"; break;
440 0 : case ColRec::Type::Char: stream << "\"char\""; break;
441 0 : case ColRec::Type::Float4: stream << "real"; break;
442 0 : case ColRec::Type::Float8: stream << "double precision"; break;
443 0 : case ColRec::Type::Int2: stream << "smallint"; break;
444 0 : case ColRec::Type::Int4: stream << "integer"; break;
445 0 : case ColRec::Type::Int8: stream << "bigint"; break;
446 0 : case ColRec::Type::Text: stream << "text"; break;
447 0 : case ColRec::Type::VarChar: stream << "varchar"; break;
448 0 : case ColRec::Type::Numeric: stream << "numeric"; break;
449 0 : case ColRec::Type::Bytes: stream << "bytea"; break;
450 0 : case ColRec::Type::TsVector:stream << "tsvector"; break;
451 0 : default: break;
452 : }
453 : }
454 0 : if (cit.second.notNull) {
455 0 : stream << " NOT NULL";
456 : }
457 0 : stream << ";\n";
458 : }
459 0 : }
460 : }
461 : }
462 :
463 : // write constraints
464 600 : for (auto &it : required) {
465 1400 : for (auto & cit : it.second.constraints) {
466 825 : stream << "ALTER TABLE " << it.first << " ADD CONSTRAINT \"" << cit.first << "\" ";
467 :
468 825 : bool first = true;
469 825 : switch (cit.second.type) {
470 200 : case ConstraintRec::Unique:
471 200 : stream << " UNIQUE ( ";
472 400 : for (auto &key : cit.second.fields) {
473 200 : if (first) { first = false; } else { stream << ", "; }
474 200 : stream << "\"" << key << "\"";
475 : }
476 200 : stream << " )";
477 200 : break;
478 625 : case ConstraintRec::Reference:
479 625 : stream << " FOREIGN KEY (";
480 1250 : for (auto &key : cit.second.fields) {
481 625 : if (first) { first = false; } else { stream << ", "; }
482 625 : stream << "\"" << key << "\"";
483 : }
484 625 : stream << ") REFERENCES " << cit.second.reference << " ( \"__oid\" )";
485 625 : switch (cit.second.remove) {
486 300 : case db::RemovePolicy::Cascade:
487 300 : stream << " ON DELETE CASCADE";
488 300 : break;
489 0 : case db::RemovePolicy::Restrict:
490 0 : stream << " ON DELETE RESTRICT";
491 0 : break;
492 325 : case db::RemovePolicy::Null:
493 : case db::RemovePolicy::Reference:
494 : case db::RemovePolicy::StrongReference:
495 325 : stream << " ON DELETE SET NULL";
496 325 : break;
497 : }
498 625 : break;
499 : }
500 :
501 825 : stream << ";\n";
502 : }
503 : }
504 :
505 : // indexes
506 600 : for (auto &it : required) {
507 1600 : for (auto & cit : it.second.indexes) {
508 1025 : if (cit.second.back() != ')') {
509 400 : stream << "CREATE INDEX IF NOT EXISTS \"" << cit.first
510 400 : << "\" ON " << it.first << " ( \"" << cit.second << "\" );\n";
511 : } else {
512 625 : stream << "CREATE INDEX IF NOT EXISTS \"" << cit.first
513 625 : << "\" ON " << it.first << " " << cit.second << ";\n";
514 : }
515 : }
516 :
517 575 : if (!it.second.triggers.empty()) {
518 125 : auto scheme_it = s.find(it.first);
519 125 : if (scheme_it != s.end()) {
520 250 : for (auto & tit : it.second.triggers) {
521 150 : if (StringView(tit).starts_with("_tr_a_")) {
522 100 : writeAfterTrigger(stream, scheme_it->second, tit);
523 : } else {
524 50 : writeBeforeTrigger(stream, scheme_it->second, tit);
525 : }
526 : }
527 25 : } else if (it.second.viewField) {
528 50 : for (auto & tit : it.second.triggers) {
529 25 : writeDeltaTrigger(stream, it.first, it.second, tit);
530 : }
531 : }
532 : }
533 : }
534 25 : }
535 :
536 25 : Map<StringView, TableRec> TableRec::parse(const Driver *driver, const BackendInterface::Config &cfg,
537 : const Map<StringView, const db::Scheme *> &s, const Vector<Pair<StringView, int64_t>> &customs) {
538 25 : Map<StringView, TableRec> tables;
539 400 : for (auto &it : s) {
540 375 : auto scheme = it.second;
541 375 : tables.emplace(scheme->getName(), TableRec(driver, cfg, scheme, customs));
542 :
543 : // check for extra tables
544 2700 : for (auto &fit : scheme->getFields()) {
545 2325 : auto &f = fit.second;
546 2325 : auto type = fit.second.getType();
547 :
548 2325 : if (type == db::Type::Set) {
549 150 : auto ref = static_cast<const db::FieldObject *>(f.getSlot());
550 150 : if (ref->onRemove == db::RemovePolicy::Reference || ref->onRemove == db::RemovePolicy::StrongReference) {
551 75 : String name = toString(it.first, "_f_", fit.first);
552 75 : auto & source = it.first;
553 75 : auto target = ref->scheme->getName();
554 75 : TableRec table;
555 75 : table.cols.emplace(toString(source, "_id"), ColRec(ColRec::Type::Int8, true));
556 75 : table.cols.emplace(toString(target, "_id"), ColRec(ColRec::Type::Int8, true));
557 :
558 75 : table.constraints.emplace(toString(name, "_ref_", source), ConstraintRec(
559 150 : ConstraintRec::Reference, toString(source, "_id"), source, db::RemovePolicy::Cascade));
560 150 : table.constraints.emplace(toString(name, "_ref_", ref->getName()), ConstraintRec(
561 225 : ConstraintRec::Reference, toString(target, "_id"), target.str<Interface>(), db::RemovePolicy::Cascade));
562 :
563 75 : table.indexes.emplace(toString(name, "_idx_", source), toString(source, "_id"));
564 75 : table.indexes.emplace(toString(name, "_idx_", target), toString(target, "_id"));
565 :
566 75 : table.pkey.emplace_back(toString(source, "_id"));
567 75 : table.pkey.emplace_back(toString(target, "_id"));
568 75 : tables.emplace(StringView(stappler::string::tolower<Interface>(name)).pdup(), std::move(table));
569 75 : }
570 2175 : } else if (type == db::Type::Array) {
571 25 : auto slot = static_cast<const db::FieldArray *>(f.getSlot());
572 25 : if (slot->tfield && slot->tfield.isSimpleLayout()) {
573 :
574 25 : String name = toString(it.first, "_f_", fit.first);
575 25 : string::apply_tolower_c(name);
576 25 : auto & source = it.first;
577 :
578 25 : TableRec table;
579 25 : table.cols.emplace("id", ColRec(ColRec::Type::Int8, true, true));
580 25 : table.cols.emplace(toString(source, "_id"), ColRec(ColRec::Type::Int8));
581 :
582 25 : auto type = slot->tfield.getType();
583 25 : switch (type) {
584 0 : case db::Type::Float:
585 0 : table.cols.emplace("data", ColRec(ColRec::Type::Float8));
586 0 : break;
587 0 : case db::Type::Boolean:
588 0 : table.cols.emplace("data", ColRec(ColRec::Type::Bool));
589 0 : break;
590 25 : case db::Type::Text:
591 25 : table.cols.emplace("data", ColRec(ColRec::Type::Text));
592 25 : break;
593 0 : case db::Type::Data:
594 : case db::Type::Bytes:
595 : case db::Type::Extra:
596 0 : table.cols.emplace("data", ColRec(ColRec::Type::Bytes));
597 0 : break;
598 0 : case db::Type::Integer:
599 0 : table.cols.emplace("data", ColRec(ColRec::Type::Int8));
600 0 : break;
601 0 : default:
602 0 : break;
603 : }
604 :
605 25 : table.constraints.emplace(toString(name, "_ref_", source), ConstraintRec (
606 50 : ConstraintRec::Reference, toString(source, "_id"), source, db::RemovePolicy::Cascade));
607 25 : table.pkey.emplace_back("id");
608 :
609 25 : if (f.hasFlag(db::Flags::Unique)) {
610 0 : table.constraints.emplace(name + "_unique", ConstraintRec(ConstraintRec::Unique, {toString(source, "_id"), "data"}));
611 : }
612 :
613 25 : table.indexes.emplace(toString(name, "_idx_", source), toString(source, "_id"));
614 25 : tables.emplace(StringView(name).pdup(), std::move(table));
615 25 : }
616 2150 : } else if (type == db::Type::View) {
617 50 : auto slot = static_cast<const db::FieldView *>(f.getSlot());
618 :
619 50 : String name = toString(it.first, "_f_", fit.first, "_view");
620 50 : auto & source = it.first;
621 50 : auto target = slot->scheme->getName();
622 :
623 50 : TableRec table;
624 50 : table.viewScheme = it.second;
625 50 : table.viewField = slot;
626 50 : table.cols.emplace("__vid", ColRec(ColRec::Type::Int8, true, true));
627 50 : table.cols.emplace(toString(source, "_id"), ColRec(ColRec::Type::Int8, true));
628 50 : table.cols.emplace(toString(target, "_id"), ColRec(ColRec::Type::Int8, true));
629 :
630 50 : table.constraints.emplace(toString(name, "_ref_", source), ConstraintRec(
631 100 : ConstraintRec::Reference, toString(source, "_id"), source, db::RemovePolicy::Cascade));
632 100 : table.constraints.emplace(toString(name, "_ref_", slot->getName()), ConstraintRec(
633 150 : ConstraintRec::Reference, toString(target, "_id"), target.str<Interface>(), db::RemovePolicy::Cascade));
634 :
635 50 : table.indexes.emplace(toString(name, "_idx_", source), toString(source, "_id"));
636 50 : table.indexes.emplace(toString(name, "_idx_", target), toString(target, "_id"));
637 :
638 50 : table.pkey.emplace_back("__vid");
639 50 : auto tblIt = tables.emplace(StringView(name).pdup(), std::move(table)).first;
640 :
641 50 : if (slot->delta) {
642 25 : StringStream hashStream;
643 25 : hashStream << getDefaultFunctionVersion() << tblIt->first << "_delta";
644 :
645 25 : size_t id = std::hash<String>{}(hashStream.weak());
646 25 : hashStream.clear();
647 25 : hashStream << "_trig_" << tblIt->first << "_" << id;
648 25 : tblIt->second.triggers.emplace(StringView(hashStream.weak()).sub(0, 56).str<Interface>());
649 :
650 25 : String name = toString(it.first, "_f_", fit.first, "_delta");
651 25 : table.cols.emplace("id", ColRec(ColRec::Type::Int8, true, true));
652 25 : table.cols.emplace("tag", ColRec(ColRec::Type::Int8, true));
653 25 : table.cols.emplace("object", ColRec(ColRec::Type::Int8, true));
654 25 : table.cols.emplace("time", ColRec(ColRec::Type::Int8, true));
655 25 : table.cols.emplace("user", ColRec(ColRec::Type::Int8));
656 :
657 25 : table.pkey.emplace_back("id");
658 25 : table.indexes.emplace(name + "_idx_tag", "tag");
659 25 : table.indexes.emplace(name + "_idx_object", "object");
660 25 : table.indexes.emplace(name + "_idx_time", "time");
661 25 : tables.emplace(StringView(name).pdup(), std::move(table));
662 25 : }
663 50 : }
664 :
665 2325 : if (scheme->hasDelta()) {
666 25 : auto name = Handle::getNameForDelta(*scheme);
667 25 : TableRec table;
668 25 : table.cols.emplace("id", ColRec(ColRec::Type::Int8, true, true));
669 25 : table.cols.emplace("object", ColRec(ColRec::Type::Int8, true));
670 25 : table.cols.emplace("time", ColRec(ColRec::Type::Int8, true));
671 25 : table.cols.emplace("action", ColRec(ColRec::Type::Int8, true));
672 25 : table.cols.emplace("user", ColRec(ColRec::Type::Int8));
673 :
674 25 : table.pkey.emplace_back("id");
675 25 : table.indexes.emplace(name + "_idx_object", "object");
676 25 : table.indexes.emplace(name + "_idx_time", "time");
677 25 : tables.emplace(StringView(name).pdup(), std::move(table));
678 25 : }
679 : }
680 : }
681 25 : return tables;
682 0 : }
683 :
684 25 : Map<StringView, TableRec> TableRec::get(Handle &h, StringStream &stream) {
685 25 : Map<StringView, TableRec> ret;
686 :
687 25 : h.performSimpleSelect("SELECT table_name FROM information_schema.tables "
688 : "WHERE table_schema='public' AND table_type='BASE TABLE';",
689 25 : [&] (db::sql::Result &tables) {
690 150 : for (auto it : tables) {
691 125 : ret.emplace(it.at(0).pdup(), TableRec());
692 125 : stream << "TABLE " << it.at(0) << "\n";
693 : }
694 25 : tables.clear();
695 25 : });
696 :
697 25 : h.performSimpleSelect(COL_QUERY, [&] (db::sql::Result &columns) {
698 475 : for (auto it : columns) {
699 450 : auto tname = it.at(0).str<Interface>();
700 450 : auto f = ret.find(tname);
701 450 : if (f != ret.end()) {
702 450 : auto &table = f->second;
703 450 : bool isNullable = (it.at(2) == "YES");
704 450 : auto type = it.at(3);
705 450 : if (it.at(1) != "__oid") {
706 400 : auto storageType = h.getDriver()->getTypeById(it.toInteger(4));
707 400 : switch (storageType) {
708 25 : case BackendInterface::StorageType::Unknown:
709 25 : table.cols.emplace(it.at(1).str<Interface>(), ColRec(type, it.toInteger(4), !isNullable));
710 25 : break;
711 375 : default:
712 375 : table.cols.emplace(it.at(1).str<Interface>(), ColRec(storageType, it.toInteger(4), !isNullable));
713 375 : break;
714 : }
715 : }
716 450 : stream << "COLUMNS " << it.at(0) << " " << it.at(1) << " " << it.at(2) << " " << it.at(3) << " (" << it.toInteger(4) << ")\n";
717 : }
718 450 : }
719 25 : columns.clear();
720 25 : });
721 :
722 25 : h.performSimpleSelect("SELECT table_name, constraint_name, constraint_type FROM information_schema.table_constraints "
723 : "WHERE table_schema='public' AND constraint_schema='public';"_weak,
724 25 : [&] (db::sql::Result &constraints) {
725 475 : for (auto it : constraints) {
726 450 : auto tname = it.at(0).str<Interface>();
727 450 : auto f = ret.find(tname);
728 450 : if (f != ret.end()) {
729 450 : auto &table = f->second;
730 450 : if (it.at(2) == "UNIQUE") {
731 0 : table.constraints.emplace(it.at(1).str<Interface>(), ConstraintRec(ConstraintRec::Unique));
732 0 : stream << "CONSTRAINT " << it.at(0) << " " << it.at(1) << " " << it.at(2) << "\n";
733 450 : } else if (it.at(2) == "FOREIGN KEY") {
734 0 : table.constraints.emplace(it.at(1).str<Interface>(), ConstraintRec(ConstraintRec::Reference));
735 0 : stream << "CONSTRAINT " << it.at(0) << " " << it.at(1) << " " << it.at(2) << "\n";
736 : }
737 : }
738 450 : }
739 25 : constraints.clear();
740 25 : });
741 :
742 25 : h.performSimpleSelect(String::make_weak(INDEX_QUERY),
743 25 : [&] (db::sql::Result &indexes) {
744 225 : for (auto it : indexes) {
745 200 : auto tname = it.at(0).str<Interface>();
746 200 : auto f = ret.find(tname);
747 200 : if (f != ret.end()) {
748 200 : auto &table = f->second;
749 200 : auto name = it.at(1);
750 200 : name.readUntilString("_idx_");
751 200 : if (name.is("_idx_")) {
752 0 : table.indexes.emplace(it.at(1).str<Interface>(), it.at(2).str<Interface>());
753 0 : stream << "INDEX " << it.at(0) << " " << it.at(1) << " " << it.at(2) << "\n";
754 : }
755 : }
756 200 : }
757 25 : indexes.clear();
758 25 : });
759 :
760 25 : h.performSimpleSelect("SELECT event_object_table, trigger_name FROM information_schema.triggers "
761 : "WHERE trigger_schema='public';"_weak,
762 25 : [&] (db::sql::Result &triggers) {
763 25 : for (auto it : triggers) {
764 0 : auto tname = it.at(0).str<Interface>();
765 0 : auto f = ret.find(tname);
766 0 : if (f != ret.end()) {
767 0 : auto &table = f->second;
768 0 : table.triggers.emplace(it.at(1).str<Interface>());
769 0 : stream << "TRIGGER " << it.at(0) << " " << it.at(1) << "\n";
770 : }
771 0 : }
772 25 : triggers.clear();
773 25 : });
774 :
775 25 : return ret;
776 0 : }
777 :
778 300 : TableRec::TableRec() : objects(false) { }
779 375 : TableRec::TableRec(const Driver *driver, const BackendInterface::Config &cfg, const db::Scheme *scheme,
780 375 : const Vector<Pair<StringView, int64_t>> &customs) {
781 375 : StringStream hashStreamAfter; hashStreamAfter << getDefaultFunctionVersion();
782 375 : StringStream hashStreamBefore; hashStreamBefore << getDefaultFunctionVersion();
783 :
784 375 : bool hasAfterTrigger = false;
785 375 : bool hasBeforeTrigger = false;
786 375 : auto name = scheme->getName();
787 375 : pkey.emplace_back("__oid");
788 :
789 375 : if (scheme->hasDelta()) {
790 25 : hasAfterTrigger = true;
791 25 : hashStreamAfter << ":delta:";
792 : }
793 :
794 2700 : for (auto &it : scheme->getFields()) {
795 2325 : bool emplaced = false;
796 2325 : auto &f = it.second;
797 2325 : auto type = it.second.getType();
798 :
799 2325 : if (type == db::Type::File || type == db::Type::Image) {
800 250 : hasAfterTrigger = true;
801 250 : hashStreamAfter << it.first << stappler::toInt(type);
802 : }
803 :
804 2325 : switch (type) {
805 125 : case db::Type::None:
806 : case db::Type::Array:
807 : case db::Type::View:
808 : case db::Type::Virtual:
809 125 : break;
810 :
811 25 : case db::Type::Float:
812 25 : cols.emplace(it.first, ColRec(ColRec::Type::Float8, f.hasFlag(db::Flags::Required)));
813 25 : emplaced = true;
814 25 : break;
815 :
816 125 : case db::Type::Boolean:
817 125 : cols.emplace(it.first, ColRec(ColRec::Type::Bool, f.hasFlag(db::Flags::Required)));
818 125 : emplaced = true;
819 125 : break;
820 :
821 650 : case db::Type::Text:
822 650 : cols.emplace(it.first, ColRec(ColRec::Type::Text, f.hasFlag(db::Flags::Required)));
823 650 : emplaced = true;
824 650 : break;
825 :
826 325 : case db::Type::Data:
827 : case db::Type::Bytes:
828 : case db::Type::Extra:
829 325 : cols.emplace(it.first, ColRec(ColRec::Type::Bytes, f.hasFlag(db::Flags::Required)));
830 325 : emplaced = true;
831 325 : break;
832 :
833 675 : case db::Type::Integer:
834 : case db::Type::File:
835 : case db::Type::Image:
836 675 : cols.emplace(it.first, ColRec(ColRec::Type::Int8, f.hasFlag(db::Flags::Required)));
837 675 : emplaced = true;
838 675 : break;
839 :
840 25 : case db::Type::FullTextView:
841 25 : cols.emplace(it.first, ColRec(ColRec::Type::TsVector, f.hasFlag(db::Flags::Required)));
842 25 : emplaced = true;
843 25 : break;
844 :
845 100 : case db::Type::Object:
846 100 : cols.emplace(it.first, ColRec(ColRec::Type::Int8, f.hasFlag(db::Flags::Required)));
847 100 : if (f.isReference()) {
848 0 : auto objSlot = static_cast<const db::FieldObject *>(f.getSlot());
849 0 : if (objSlot->onRemove == db::RemovePolicy::StrongReference) {
850 0 : hasAfterTrigger = true;
851 0 : hashStreamAfter << it.first << stappler::toInt(type);
852 : }
853 : }
854 100 : emplaced = true;
855 100 : break;
856 :
857 150 : case db::Type::Set:
858 150 : if (f.isReference()) {
859 75 : auto objSlot = static_cast<const db::FieldObject *>(f.getSlot());
860 75 : if (objSlot->onRemove == db::RemovePolicy::StrongReference) {
861 50 : hasBeforeTrigger = true;
862 50 : hashStreamBefore << it.first << stappler::toInt(type);
863 : }
864 : }
865 150 : break;
866 :
867 125 : case db::Type::Custom:
868 125 : if (auto objSlot = f.getSlot<db::FieldCustom>()) {
869 125 : if (auto info = driver->getCustomFieldInfo(objSlot->getDriverTypeName())) {
870 125 : int64_t oid = 0;
871 350 : for (auto &it : customs) {
872 350 : if (it.first == info->typeName) {
873 125 : oid = it.second;
874 125 : break;
875 : }
876 : }
877 125 : if (oid) {
878 125 : cols.emplace(it.first, ColRec(info->typeName, oid, f.hasFlag(db::Flags::Required)));
879 : } else {
880 0 : cols.emplace(it.first, ColRec(info->typeName, f.hasFlag(db::Flags::Required)));
881 : }
882 125 : emplaced = true;
883 : }
884 : }
885 125 : break;
886 : }
887 :
888 2325 : if (emplaced) {
889 2050 : if (type == db::Type::Object) {
890 100 : auto ref = static_cast<const db::FieldObject *>(f.getSlot());
891 100 : auto target = ref->scheme->getName();
892 100 : StringStream cname; cname << name << "_ref_" << it.first << "_" << target;
893 :
894 100 : switch (ref->onRemove) {
895 25 : case RemovePolicy::Cascade: cname << "_csc"; break;
896 0 : case RemovePolicy::Restrict: cname << "_rst"; break;
897 0 : case RemovePolicy::Reference: cname << "_ref"; break;
898 0 : case RemovePolicy::StrongReference: cname << "_sref"; break;
899 75 : case RemovePolicy::Null: break;
900 : }
901 :
902 100 : constraints.emplace(cname.str(), ConstraintRec(ConstraintRec::Reference, it.first, target.str<Interface>(), ref->onRemove));
903 100 : indexes.emplace(toString(name, "_idx_", it.first), toString("( \"", it.first, "\" )"));
904 2050 : } else if (type == db::Type::File || type == db::Type::Image) {
905 250 : auto ref = cfg.fileScheme;
906 250 : auto cname = toString(name, "_ref_", it.first);
907 250 : auto target = ref->getName();
908 250 : constraints.emplace(cname, ConstraintRec(ConstraintRec::Reference, it.first, target.str<Interface>(), db::RemovePolicy::Null));
909 250 : }
910 :
911 3975 : if ((type == db::Type::Text && f.getTransform() == db::Transform::Alias) ||
912 1925 : f.hasFlag(db::Flags::Unique)) {
913 200 : constraints.emplace(toString(name, "_unique_", it.first), ConstraintRec(ConstraintRec::Unique, it.first));
914 : }
915 :
916 650 : if ((type == db::Type::Text && f.getTransform() == db::Transform::Alias)
917 2700 : || (f.hasFlag(db::Flags::Indexed) && !f.hasFlag(db::Flags::Unique))) {
918 475 : if (type == db::Type::Custom) {
919 25 : auto c = f.getSlot<db::FieldCustom>();
920 25 : if (auto info = driver->getCustomFieldInfo(c->getDriverTypeName())) {
921 25 : if (info->isIndexable) {
922 25 : indexes.emplace(toString(name, "_idx_", info->getIndexName(*c)), info->getIndexDefinition(*c));
923 : }
924 : }
925 450 : } else if (type == db::Type::FullTextView) {
926 0 : indexes.emplace(toString(name, "_idx_", it.first), toString("USING GIN ( \"", it.first, "\" )"));
927 : } else {
928 450 : indexes.emplace(toString(name, "_idx_", it.first), toString("( \"", it.first, "\" )"));
929 : }
930 : }
931 :
932 2050 : if (type == db::Type::Text) {
933 650 : if (f.hasFlag(db::Flags::PatternIndexed)) {
934 25 : indexes.emplace(toString(name, "_idx_", it.first, "_pattern"), toString("USING btree ( \"", it.first, "\" text_pattern_ops)"));
935 : }
936 650 : if (f.hasFlag(db::Flags::TrigramIndexed)) {
937 25 : indexes.emplace(toString(name, "_idx_", it.first, "_trgm"), toString("USING GIN ( \"", it.first, "\" gin_trgm_ops)"));
938 : }
939 : }
940 : }
941 : }
942 :
943 375 : for (auto &it : scheme->getUnique()) {
944 0 : auto &c = constraints.emplace(it.name.str<Interface>(), ConstraintRec(ConstraintRec::Unique)).first->second;
945 0 : for (auto &f : it.fields) {
946 0 : c.fields.emplace_back(f->getName().str<Interface>());
947 : }
948 : }
949 :
950 375 : if (scheme->isDetouched()) {
951 0 : cols.emplace("__oid", ColRec(ColRec::Type::Int8, true, true));
952 0 : objects = false;
953 : }
954 :
955 375 : if (hasAfterTrigger) {
956 100 : size_t id = std::hash<String>{}(hashStreamAfter.weak());
957 :
958 100 : hashStreamAfter.clear();
959 100 : hashStreamAfter << "_tr_a_" << scheme->getName() << "_" << id;
960 100 : triggers.emplace(StringView(hashStreamAfter.weak()).sub(0, 56).str<Interface>());
961 : }
962 :
963 375 : if (hasBeforeTrigger) {
964 50 : size_t id = std::hash<String>{}(hashStreamBefore.weak());
965 :
966 50 : hashStreamBefore.clear();
967 50 : hashStreamBefore << "_tr_b_" << scheme->getName() << "_" << id;
968 50 : triggers.emplace(StringView(hashStreamBefore.weak()).sub(0, 56).str<Interface>());
969 : }
970 375 : }
971 :
972 125 : static void Handle_insert_sorted(Vector<Pair<StringView, int64_t>> & vec, StringView type) {
973 125 : auto it = std::upper_bound(vec.begin(), vec.end(), type, [] (const StringView &l, const Pair<StringView, int64_t> &r) -> bool {
974 175 : return l < r.first;
975 125 : });
976 125 : vec.emplace(it, type, 0);
977 125 : }
978 :
979 25 : bool Handle::init(const BackendInterface::Config &cfg, const Map<StringView, const Scheme *> &s) {
980 25 : if (!performSimpleQuery(StringView(DATABASE_DEFAULTS))) {
981 0 : return false;
982 : }
983 :
984 25 : if (!performSimpleQuery("START TRANSACTION; LOCK TABLE __objects;")) {
985 0 : return false;
986 : }
987 :
988 25 : StringStream tables;
989 25 : tables << "Server: " << cfg.name << "\n";
990 :
991 25 : Vector<Pair<StringView, int64_t>> customFields;
992 :
993 400 : for (auto &it : s) {
994 2700 : for (auto &f : it.second->getFields()) {
995 2325 : if (f.second.getType() == Type::Custom) {
996 125 : auto objSlot = f.second.getSlot<db::FieldCustom>();
997 125 : if (auto info = driver->getCustomFieldInfo(objSlot->getDriverTypeName())) {
998 125 : Handle_insert_sorted(customFields, info->typeName);
999 : }
1000 : }
1001 : }
1002 : }
1003 :
1004 25 : if (!customFields.empty()) {
1005 25 : StringStream tempTable;
1006 25 : tempTable << "CREATE TEMPORARY TABLE custom_fields (\n\tid integer primary key";
1007 25 : size_t idx = 0;
1008 150 : for (auto &it : customFields) {
1009 125 : tempTable << ",\n\tfield" << idx << " " << it.first;
1010 125 : ++ idx;
1011 : }
1012 25 : tempTable << "\n);";
1013 :
1014 25 : performSimpleQuery(tempTable.weak());
1015 :
1016 25 : performSimpleSelect("SELECT attname, atttypid::integer FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'custom_fields');",
1017 25 : [&] (db::sql::Result &result) {
1018 325 : for (auto it : result) {
1019 300 : auto n = it.at(0);
1020 300 : if (n.starts_with("field")) {
1021 125 : n += "field"_len;
1022 125 : auto idx = n.readInteger(10).get(0);
1023 125 : customFields[idx].second = it.toInteger(1);
1024 : }
1025 : }
1026 25 : tables.clear();
1027 25 : });
1028 :
1029 25 : performSimpleQuery("DROP TABLE custom_fields;");
1030 25 : }
1031 :
1032 25 : auto requiredTables = TableRec::parse(driver, cfg, s, customFields);
1033 25 : auto existedTables = TableRec::get(*this, tables);
1034 :
1035 25 : StringStream stream;
1036 25 : TableRec::writeCompareResult(stream, requiredTables, existedTables, s);
1037 :
1038 25 : if (stream.size() > 3) {
1039 25 : bool success = true;
1040 25 : if (performSimpleQuery(stream.weak(), [&] (const Value &errInfo) {
1041 0 : stream << "Server: " << cfg.name << "\n";
1042 0 : stream << "\nErrorInfo: " << EncodeFormat::Pretty << errInfo << "\n";
1043 0 : })) {
1044 25 : performSimpleQuery("COMMIT;"_weak);
1045 : } else {
1046 0 : log::error("Database", "Fail to perform database update");
1047 0 : stream << "\nError: " << driver->getStatusMessage(lastError) << "\n";
1048 0 : performSimpleQuery("ROLLBACK;"_weak);
1049 0 : success = false;
1050 : }
1051 :
1052 25 : tables << "\n" << stream;
1053 25 : _driver->getApplicationInterface()->reportDbUpdate(tables.weak(), success);
1054 25 : if (!success) {
1055 0 : return false;
1056 : }
1057 : } else {
1058 0 : performSimpleQuery("COMMIT;"_weak);
1059 : }
1060 :
1061 25 : beginTransaction_pg(TransactionLevel::ReadCommited);
1062 25 : StringStream query;
1063 25 : query << "DELETE FROM __login WHERE \"date\" < " << Time::now().toSeconds() - config::STORAGE_DEFAULT_INTERNAL_INTERVAL.toSeconds() << ";";
1064 25 : performSimpleQuery(query.weak());
1065 25 : query.clear();
1066 :
1067 25 : auto iit = existedTables.find(StringView("__error"));
1068 25 : if (iit != existedTables.end()) {
1069 0 : query << "DELETE FROM __error WHERE \"time\" < " << Time::now().toMicros() - config::STORAGE_DEFAULT_INTERNAL_INTERVAL.toMicros() << ";";
1070 0 : performSimpleQuery(query.weak());
1071 0 : query.clear();
1072 : }
1073 :
1074 25 : endTransaction_pg();
1075 25 : return true;
1076 25 : }
1077 :
1078 : }
|
