File indexing completed on 2024-04-28 16:30:31
0001 /*************************************************************************** 0002 * SPDX-FileCopyrightText: 2022 S. MANKOWSKI stephane@mankowski.fr 0003 * SPDX-FileCopyrightText: 2022 G. DE BURE support@mankowski.fr 0004 * SPDX-License-Identifier: GPL-3.0-or-later 0005 ***************************************************************************/ 0006 /** @file 0007 * This file implements classes SKGDocument. 0008 * 0009 * @author Stephane MANKOWSKI / Guillaume DE BURE 0010 */ 0011 0012 #include "qregularexpression.h" 0013 0014 #include "skgdocument.h" 0015 0016 SKGError SKGDocument::refreshViewsIndexesAndTriggers(bool iForce) const 0017 { 0018 Q_UNUSED(iForce) 0019 SKGError err; 0020 SKGTRACEINFUNCRC(5, err) 0021 /** 0022 * This constant is used to initialized the data model (trigger creation) 0023 */ 0024 QString rd_node_id; 0025 QStringList attsOfNodes; 0026 err = getAttributesList(QStringLiteral("node"), attsOfNodes); 0027 rd_node_id = (attsOfNodes.contains(QStringLiteral("rd_node_id")) ? QStringLiteral("rd_node_id") : QStringLiteral("r_node_id")); 0028 0029 QStringList InitialDataModelTrigger; 0030 InitialDataModelTrigger 0031 << DELETECASCADEPARAMETER("parameters") 0032 << DELETECASCADEPARAMETER("node") 0033 0034 // Compute fullname 0035 << QStringLiteral("DROP TRIGGER IF EXISTS cpt_node_fullname3") 0036 /*<< "CREATE TRIGGER cpt_node_fullname1 " // This trigger must be the first 0037 "AFTER UPDATE OF t_fullname ON node BEGIN " 0038 "UPDATE node SET t_name=t_name WHERE rd_node_id=new.id;" 0039 "END"*/ 0040 0041 << QStringLiteral("DROP TRIGGER IF EXISTS cpt_node_fullname1") 0042 << "CREATE TRIGGER cpt_node_fullname1 " 0043 "AFTER INSERT ON node BEGIN " 0044 "UPDATE node SET t_fullname=" 0045 "CASE WHEN new.rd_node_id IS NULL OR new." % rd_node_id % "='' OR new." % rd_node_id % "=0 THEN new.t_name ELSE (SELECT c.t_fullname from node c where c.id=new." % rd_node_id % ")||'" % OBJECTSEPARATOR % "'||new.t_name END " 0046 "WHERE id=new.id;" 0047 "END" 0048 0049 << QStringLiteral("DROP TRIGGER IF EXISTS cpt_node_fullname2") 0050 << "CREATE TRIGGER cpt_node_fullname2 " 0051 "AFTER UPDATE OF t_name, " % rd_node_id % " ON node BEGIN " 0052 "UPDATE node SET t_fullname=" 0053 "CASE WHEN new." % rd_node_id % " IS NULL OR new." % rd_node_id % "='' OR new." % rd_node_id % "=0 THEN new.t_name ELSE (SELECT c.t_fullname from node c where c.id=new." % rd_node_id % ")||'" % OBJECTSEPARATOR % "'||new.t_name END " 0054 "WHERE id=new.id;" 0055 "UPDATE node SET t_name=t_name WHERE " % rd_node_id % "=new.id;" 0056 "END" 0057 0058 << QStringLiteral("DROP TRIGGER IF EXISTS fkdc_node_parent_id_node_id"); 0059 0060 /** 0061 * This constant is used to initialized the data model (index creation) 0062 */ 0063 QStringList InitialDataModelIndex; 0064 InitialDataModelIndex << QStringLiteral("CREATE UNIQUE INDEX uidx_parameters_uuid_parent_name ON parameters (t_uuid_parent, t_name)") 0065 0066 << "CREATE UNIQUE INDEX uidx_node_parent_id_name ON node(t_name," % rd_node_id % ")" 0067 << QStringLiteral("CREATE INDEX idx_node_fullname ON node(t_fullname)") 0068 0069 << QStringLiteral("CREATE INDEX idx_doctransaction_parent ON doctransaction (i_parent)") 0070 << QStringLiteral("CREATE INDEX idx_doctransactionitem_i_object_id ON doctransactionitem (i_object_id)") 0071 << QStringLiteral("CREATE INDEX idx_doctransactionitem_t_object_table ON doctransactionitem (t_object_table)") 0072 << QStringLiteral("CREATE INDEX idx_doctransactionitem_t_action ON doctransactionitem (t_action)") 0073 << QStringLiteral("CREATE INDEX idx_doctransactionitem_rd_doctransaction_id ON doctransactionitem (rd_doctransaction_id)") 0074 << QStringLiteral("CREATE INDEX idx_doctransactionitem_optimization ON doctransactionitem (rd_doctransaction_id, i_object_id, t_object_table, t_action, t_sqlorder, id)"); 0075 0076 /** 0077 * This constant is used to initialized the data model (view creation) 0078 */ 0079 QStringList InitialDataModelView; 0080 InitialDataModelView << QStringLiteral("CREATE VIEW v_node AS SELECT * from node") 0081 << QStringLiteral("CREATE VIEW v_node_displayname AS SELECT *, t_fullname AS t_displayname from node") 0082 << QStringLiteral("CREATE VIEW v_parameters_displayname AS SELECT *, t_name AS t_displayname from parameters") 0083 << QStringLiteral("CREATE VIEW v_doctransaction_displayname AS SELECT *, t_name AS t_displayname from doctransaction"); 0084 0085 IFOKDO(err, dropViewsAndIndexes(QStringList() << QStringLiteral("node") << QStringLiteral("parameters") << QStringLiteral("doctransactionitem") << QStringLiteral("doctransaction"))) 0086 IFOKDO(err, executeSqliteOrders(InitialDataModelIndex)) 0087 IFOKDO(err, executeSqliteOrders(InitialDataModelView)) 0088 IFOKDO(err, executeSqliteOrders(InitialDataModelTrigger)) 0089 IFOK(err) { 0090 // Refresh dynamic triggers 0091 QRegularExpression rx_rd(QStringLiteral("rd_([^_]+)_([^_]+).*")); 0092 QRegularExpression rx_rc(QStringLiteral("rc_([^_]+)_([^_]+).*")); 0093 QRegularExpression rx_r(QStringLiteral("r_([^_]+)_([^_]+).*")); 0094 QStringList dbTables; 0095 err = this->getDistinctValues(QStringLiteral("sqlite_master"), QStringLiteral("name"), QStringLiteral("type='table'"), dbTables); 0096 int nb = dbTables.count(); 0097 for (int i = 0; !err && i < nb; ++i) { 0098 const QString& table = dbTables.at(i); 0099 SKGStringListList attributes; 0100 err = executeSelectSqliteOrder("PRAGMA table_info(" % table % ");", attributes); 0101 int nb2 = attributes.count(); 0102 for (int j = 1; !err && j < nb2; ++j) { // Header is ignored 0103 auto att = attributes.at(j).at(1); 0104 auto rx_rd_match = rx_rd.match(att); 0105 if (rx_rd_match.hasMatch()) { 0106 // Get parameters 0107 QString tab2 = rx_rd_match.captured(1); 0108 QString att2 = rx_rd_match.captured(2); 0109 QStringList sqlOrders; 0110 sqlOrders << FOREIGNCONSTRAINTCASCADE(tab2, att2, table, att); 0111 err = executeSqliteOrders(sqlOrders); 0112 } else { 0113 auto rx_rc_match = rx_rc.match(att); 0114 if (rx_rc_match.hasMatch()) { 0115 // Get parameters 0116 QString tab2 = rx_rc_match.captured(1); 0117 QString att2 = rx_rc_match.captured(2); 0118 QStringList sqlOrders; 0119 sqlOrders << FOREIGNCONSTRAINT(tab2, att2, table, att); 0120 err = executeSqliteOrders(sqlOrders); 0121 } else { 0122 auto rx_r_match = rx_r.match(att); 0123 if (rx_r_match.hasMatch()) { 0124 // Get parameters 0125 QString tab2 = rx_r_match.captured(1); 0126 QString att2 = rx_r_match.captured(2); 0127 QStringList sqlOrders; 0128 sqlOrders << FOREIGNCONSTRAINTUPDATE(tab2, att2, table, att); 0129 err = executeSqliteOrders(sqlOrders); 0130 // The following sql order error is not caught because this repair order is not mandatory 0131 executeSqliteOrder("UPDATE " % table % " SET " % att % "=0 WHERE " % att % "!=0 AND " % att % " NOT IN (SELECT DISTINCT(" % att2 % ") FROM " % tab2 % ')'); 0132 } 0133 } 0134 } 0135 } 0136 } 0137 } 0138 IFOKDO(err, executeSqliteOrder(QStringLiteral("ANALYZE"))) 0139 0140 return err; 0141 }