bool PropertiesHandler::handleURI(URI& uri) { if (uri.action != "properties") return false; MetadataItemPropertiesPanel* parent = dynamic_cast< MetadataItemPropertiesPanel*>(getParentWindow(uri)); if (!parent) return true; DatabasePtr db = parent->getObservedObject()->getDatabase(); if (!db) return true; NodeType n = getTypeByName(uri.getParam("object_type")); MetadataItem* object = db->findByNameAndType(n, uri.getParam("object_name")); if (!object) { ::wxMessageBox( _("Cannot find destination object\nThis should never happen."), _("Error"), wxICON_ERROR); return true; } if (uri.getParam("target") == "new_tab") { MetadataItemPropertiesFrame::openNewPropertyPageInTab(object, parent->getParentFrame()); } else if (uri.getParam("target") == "new") MetadataItemPropertiesFrame::openNewPropertyPageInFrame(object); else MetadataItemPropertiesFrame::showPropertyPage(object); return true; }
// find all tables from "tables" which have foreign keys with "table" // and return them in "list" bool Table::tablesRelate(const std::vector<wxString>& tables, Table* table, std::vector<ForeignKey>& list) { // see if "table" references some of the "tables" std::vector<ForeignKey> *fks = table->getForeignKeys(); for (std::vector<ForeignKey>::iterator it = fks->begin(); it != fks->end(); ++it) { ForeignKey& fk = (*it); for (std::vector<wxString>::const_iterator i2 = tables.begin(); i2 != tables.end(); ++i2) if ((*i2) == fk.referencedTableM) list.push_back(fk); } // see if some of the "tables" reference the "table" std::vector<Dependency> deplist; table->getDependencies(deplist, false); for (std::vector<Dependency>::iterator it = deplist.begin(); it != deplist.end(); ++it) { if ((*it).getType() == ntTable) { for (std::vector<wxString>::const_iterator i2 = tables.begin(); i2 != tables.end(); ++i2) { if ((*i2) == (*it).getName_()) { // find foreign keys for that table DatabasePtr db = table->getDatabase(); Table* other_table = dynamic_cast<Table*>(db->findByNameAndType(ntTable, (*i2))); if (!other_table) break; std::vector<ForeignKey>* fks = other_table->getForeignKeys(); for (std::vector<ForeignKey>::iterator it = fks->begin(); it != fks->end(); ++it) { ForeignKey& fk = (*it); if (table->getName_() == fk.referencedTableM) { list.push_back(fk); break; // no need for more } } } } } } return !list.empty(); }
//! ofObject = true => returns list of objects this object depends on //! ofObject = false => returns list of objects that depend on this object void MetadataItem::getDependencies(std::vector<Dependency>& list, bool ofObject) { DatabasePtr d = getDatabase(); int mytype = -1; // map DBH type to RDB$DEPENDENT TYPE NodeType dep_types[] = { ntTable, ntView, ntTrigger, ntUnknown, ntUnknown, ntProcedure,ntUnknown, ntException,ntUnknown, ntUnknown, ntUnknown, ntUnknown, ntUnknown, ntUnknown, ntGenerator, ntFunction }; int type_count = sizeof(dep_types)/sizeof(NodeType); for (int i = 0; i < type_count; i++) if (typeM == dep_types[i]) mytype = i; // system tables should be treated as tables if (typeM == ntSysTable) mytype = 0; int mytype2 = mytype; // views count as relations(tables) when other object refer to them if (mytype == 1 && !ofObject) mytype2 = 0; if (typeM == ntUnknown || mytype == -1) throw FRError(_("Unsupported type")); IBPP::Database& db = d->getIBPPDatabase(); IBPP::Transaction tr1 = IBPP::TransactionFactory(db, IBPP::amRead); tr1->Start(); IBPP::Statement st1 = IBPP::StatementFactory(db, tr1); wxString o1 = (ofObject ? "DEPENDENT" : "DEPENDED_ON"); wxString o2 = (ofObject ? "DEPENDED_ON" : "DEPENDENT"); wxString sql = "select RDB$" + o2 + "_TYPE, RDB$" + o2 + "_NAME, RDB$FIELD_NAME \n " " from RDB$DEPENDENCIES \n " " where RDB$" + o1 + "_TYPE in (?,?) and RDB$" + o1 + "_NAME = ? \n "; int params = 1; if ((typeM == ntTable || typeM == ntSysTable || typeM == ntView) && ofObject) // get deps for computed columns { // view needed to bind with generators sql += " union \n" " SELECT DISTINCT d.rdb$depended_on_type, d.rdb$depended_on_name, d.rdb$field_name \n" " FROM rdb$relation_fields f \n" " LEFT JOIN rdb$dependencies d ON d.rdb$dependent_name = f.rdb$field_source \n" " WHERE d.rdb$dependent_type = 3 AND f.rdb$relation_name = ? \n"; params++; } if (!ofObject) // find tables that have calculated columns based on "this" object { sql += "union \n" " SELECT distinct cast(0 as smallint), f.rdb$relation_name, f.rdb$field_name \n" " from rdb$relation_fields f \n" " left join rdb$dependencies d on d.rdb$dependent_name = f.rdb$field_source \n" " where d.rdb$dependent_type = 3 and d.rdb$depended_on_name = ? "; params++; } // get the exact table and fields for views // rdb$dependencies covers deps. for WHERE clauses in SELECTs in VIEW body // but we also need mapping for column list in SELECT. These 2 queries cover it: if (ofObject && typeM == ntView) { sql += " union \n" " select distinct cast(0 as smallint), vr.RDB$RELATION_NAME, f.RDB$BASE_FIELD \n" " from RDB$RELATION_FIELDS f \n" " join RDB$VIEW_RELATIONS vr on f.RDB$VIEW_CONTEXT = vr.RDB$VIEW_CONTEXT \n" " and f.RDB$RELATION_NAME = vr.RDB$VIEW_NAME \n" " where f.rdb$relation_name = ? \n"; params++; } // views can depend on other views as well // we might need to add procedures here one day when Firebird gains support for it if (!ofObject && (typeM == ntView || typeM == ntTable || typeM == ntSysTable)) { sql += " union \n" " select distinct cast(0 as smallint), f.RDB$RELATION_NAME, f.RDB$BASE_FIELD \n" " from RDB$RELATION_FIELDS f \n" " join RDB$VIEW_RELATIONS vr on f.RDB$VIEW_CONTEXT = vr.RDB$VIEW_CONTEXT \n" " and f.RDB$RELATION_NAME = vr.RDB$VIEW_NAME \n" " where vr.rdb$relation_name = ? \n"; params++; } sql += " order by 1, 2, 3"; st1->Prepare(wx2std(sql, d->getCharsetConverter())); st1->Set(1, mytype); st1->Set(2, mytype2); for (int i = 0; i < params; i++) st1->Set(3 + i, wx2std(getName_(), d->getCharsetConverter())); st1->Execute(); MetadataItem* last = 0; Dependency* dep = 0; while (st1->Fetch()) { int object_type; st1->Get(1, &object_type); if (object_type > type_count) // some system object, not interesting for us continue; NodeType t = dep_types[object_type]; if (t == ntUnknown) // ditto continue; std::string objname_std; st1->Get(2, objname_std); wxString objname(std2wxIdentifier(objname_std, d->getCharsetConverter())); MetadataItem* current = d->findByNameAndType(t, objname); if (!current) { if (t == ntTable) { // maybe it's a view masked as table current = d->findByNameAndType(ntView, objname); // or possibly a system table if (!current) current = d->findByNameAndType(ntSysTable, objname); } if (!ofObject && t == ntTrigger) { // system trigger dependent of this object indicates possible check constraint on a table // that references this object. So, let's check if this trigger is used for check constraint // and get that table's name IBPP::Statement st2 = IBPP::StatementFactory(db, tr1); st2->Prepare( "select r.rdb$relation_name from rdb$relation_constraints r " " join rdb$check_constraints c on r.rdb$constraint_name=c.rdb$constraint_name " " and r.rdb$constraint_type = 'CHECK' where c.rdb$trigger_name = ? " ); st2->Set(1, objname_std); st2->Execute(); if (st2->Fetch()) // table using that trigger found { std::string s; st2->Get(1, s); wxString tablecheck(std2wxIdentifier(s, d->getCharsetConverter())); if (getName_() != tablecheck) // avoid self-reference current = d->findByNameAndType(ntTable, tablecheck); } } if (!current) continue; } if (current != last) // new object { Dependency de(current); list.push_back(de); dep = &list.back(); last = current; } if (!st1->IsNull(3)) { std::string s; st1->Get(3, s); dep->addField(std2wxIdentifier(s, d->getCharsetConverter())); } } // TODO: perhaps this could be moved to Table? // call MetadataItem::getDependencies() and then add this if ((typeM == ntTable || typeM == ntSysTable) && ofObject) // foreign keys of this table + computed columns { Table *t = dynamic_cast<Table *>(this); std::vector<ForeignKey> *f = t->getForeignKeys(); for (std::vector<ForeignKey>::const_iterator it = f->begin(); it != f->end(); ++it) { MetadataItem *table = d->findByNameAndType(ntTable, (*it).getReferencedTable()); if (!table) { throw FRError(wxString::Format(_("Table %s not found."), (*it).getReferencedTable().c_str())); } Dependency de(table); de.setFields((*it).getReferencedColumns()); list.push_back(de); } // Add check constraints here (CHECKS are checked via system triggers), example: // table1::check( table1.field1 > select max(field2) from table2 ) // So, table vs any object from this ^^^ select // Algorithm: 1.find all system triggers bound to that CHECK constraint // 2.find dependencies for those system triggers // 3.display those dependencies as deps. of this table st1->Prepare("select distinct c.rdb$trigger_name from rdb$relation_constraints r " " join rdb$check_constraints c on r.rdb$constraint_name=c.rdb$constraint_name " " and r.rdb$constraint_type = 'CHECK' where r.rdb$relation_name= ? " ); st1->Set(1, wx2std(getName_(), d->getCharsetConverter())); st1->Execute(); std::vector<Dependency> tempdep; while (st1->Fetch()) { std::string s; st1->Get(1, s); Trigger t(d->shared_from_this(), std2wxIdentifier(s, d->getCharsetConverter())); t.getDependencies(tempdep, true); } // remove duplicates, and self-references from "tempdep" while (true) { std::vector<Dependency>::iterator to_remove = tempdep.end(); for (std::vector<Dependency>::iterator it = tempdep.begin(); it != tempdep.end(); ++it) { if ((*it).getDependentObject() == this) { to_remove = it; break; } to_remove = std::find(it + 1, tempdep.end(), (*it)); if (to_remove != tempdep.end()) break; } if (to_remove == tempdep.end()) break; else tempdep.erase(to_remove); } list.insert(list.end(), tempdep.begin(), tempdep.end()); } // TODO: perhaps this could be moved to Table? if ((typeM == ntTable || typeM == ntSysTable) && !ofObject) // foreign keys of other tables { st1->Prepare( "select r1.rdb$relation_name, i.rdb$field_name " " from rdb$relation_constraints r1 " " join rdb$ref_constraints c on r1.rdb$constraint_name = c.rdb$constraint_name " " join rdb$relation_constraints r2 on c.RDB$CONST_NAME_UQ = r2.rdb$constraint_name " " join rdb$index_segments i on r1.rdb$index_name=i.rdb$index_name " " where r2.rdb$relation_name=? " " and r1.rdb$constraint_type='FOREIGN KEY' " ); st1->Set(1, wx2std(getName_(), d->getCharsetConverter())); st1->Execute(); wxString lasttable; Dependency* dep = 0; while (st1->Fetch()) { std::string s; st1->Get(1, s); wxString table_name(std2wxIdentifier(s, d->getCharsetConverter())); st1->Get(2, s); wxString field_name(std2wxIdentifier(s, d->getCharsetConverter())); if (table_name != lasttable) // new { MetadataItem* table = d->findByNameAndType(ntTable, table_name); if (!table) continue; // dummy check Dependency de(table); list.push_back(de); dep = &list.back(); lasttable = table_name; } dep->addField(field_name); } } tr1->Commit(); }