//! reads checks info from database
void Table::loadCheckConstraints()
{
if (checkConstraintsLoadedM)
return;
checkConstraintsM.clear();
DatabasePtr db = getDatabase();
wxMBConv* conv = db->getCharsetConverter();
MetadataLoader* loader = db->getMetadataLoader();
// first start a transaction for metadata loading, then lock the table
// when objects go out of scope and are destroyed, table will be unlocked
// before the transaction is committed - any update() calls on observers
// can possibly use the same transaction
MetadataLoaderTransaction tr(loader);
SubjectLocker lock(this);
IBPP::Statement& st1 = loader->getStatement(
"select r.rdb$constraint_name, t.rdb$trigger_source, d.rdb$field_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'"
" join rdb$triggers t on c.rdb$trigger_name=t.rdb$trigger_name and t.rdb$trigger_type = 1 "
" left join rdb$dependencies d on t.rdb$trigger_name = d.rdb$dependent_name "
" and d.rdb$depended_on_name = r.rdb$relation_name "
" and d.rdb$depended_on_type = 0 "
" where r.rdb$relation_name=? "
" order by 1 "
);
st1->Set(1, wx2std(getName_(), conv));
st1->Execute();
CheckConstraint *cc = 0;
while (st1->Fetch())
{
std::string s;
st1->Get(1, s);
wxString cname(std2wxIdentifier(s, conv));
if (!cc || cname != cc->getName_()) // new constraint
{
wxString source;
readBlob(st1, 2, source, conv);
CheckConstraint c;
c.setParent(this);
c.setName_(cname);
c.sourceM = source;
checkConstraintsM.push_back(c);
cc = &checkConstraintsM.back();
}
if (!st1->IsNull(3))
{
st1->Get(3, s);
wxString fname(std2wxIdentifier(s, conv));
cc->columnsM.push_back(fname);
}
}
checkConstraintsLoadedM = true;
}
//! reads uniques from database
void Table::loadUniqueConstraints()
{
if (uniqueConstraintsLoadedM)
return;
uniqueConstraintsM.clear();
DatabasePtr db = getDatabase();
wxMBConv* conv = db->getCharsetConverter();
MetadataLoader* loader = db->getMetadataLoader();
// first start a transaction for metadata loading, then lock the table
// when objects go out of scope and are destroyed, table will be unlocked
// before the transaction is committed - any update() calls on observers
// can possibly use the same transaction
MetadataLoaderTransaction tr(loader);
SubjectLocker lock(this);
IBPP::Statement& st1 = loader->getStatement(
"select r.rdb$constraint_name, i.rdb$field_name, r.rdb$index_name "
"from rdb$relation_constraints r, rdb$index_segments i "
"where r.rdb$relation_name=? and r.rdb$index_name=i.rdb$index_name and "
"(r.rdb$constraint_type='UNIQUE') order by r.rdb$constraint_name, i.rdb$field_position"
);
st1->Set(1, wx2std(getName_(), conv));
st1->Execute();
UniqueConstraint *cc = 0;
while (st1->Fetch())
{
std::string s;
st1->Get(1, s);
wxString cname(std2wxIdentifier(s, conv));
st1->Get(2, s);
wxString fname(std2wxIdentifier(s, conv));
st1->Get(3, s);
wxString ixname(std2wxIdentifier(s, conv));
if (cc && cc->getName_() == cname)
cc->columnsM.push_back(fname);
else
{
UniqueConstraint c;
uniqueConstraintsM.push_back(c);
cc = &uniqueConstraintsM.back();
cc->indexNameM = ixname;
cc->setName_(cname);
cc->columnsM.push_back(fname);
cc->setParent(this);
}
}
uniqueConstraintsLoadedM = true;
}
bool FieldPropertiesDialog::getNotNullConstraintName(const wxString& fieldName,
wxString& constraintName)
{
if (DatabasePtr db = tableM->getDatabase())
{
wxMBConv* conv = db->getCharsetConverter();
MetadataLoader* loader = db->getMetadataLoader();
MetadataLoaderTransaction tr(loader);
IBPP::Statement& st1 = loader->getStatement(
"SELECT rc.RDB$CONSTRAINT_NAME FROM RDB$RELATION_CONSTRAINTS rc "
"JOIN RDB$CHECK_CONSTRAINTS cc "
"ON rc.RDB$CONSTRAINT_NAME = cc.RDB$CONSTRAINT_NAME "
"WHERE rc.RDB$CONSTRAINT_TYPE = 'NOT NULL' "
"AND rc.RDB$RELATION_NAME = ?"
"AND cc.RDB$TRIGGER_NAME = ?");
st1->Set(1, wx2std(tableM->getName_(), conv));
st1->Set(2, wx2std(fieldName, conv));
st1->Execute();
if (st1->Fetch())
{
std::string s;
st1->Get(1, s);
constraintName = std2wxIdentifier(s, conv);
return true;
}
}
return false;
}
void Exceptions::load(ProgressIndicator* progressIndicator)
{
DatabasePtr db = getDatabase();
MetadataLoader* loader = db->getMetadataLoader();
MetadataLoaderTransaction tr(loader);
wxMBConv* converter = db->getCharsetConverter();
IBPP::Statement& st1 = loader->getStatement(
Exception::getLoadStatement(true));
CollectionType exceptions;
st1->Execute();
checkProgressIndicatorCanceled(progressIndicator);
while (st1->Fetch())
{
if (!st1->IsNull(1))
{
std::string s;
st1->Get(1, s);
wxString name(std2wxIdentifier(s, converter));
ExceptionPtr exception = findByName(name);
if (!exception)
{
exception.reset(new Exception(db, name));
initializeLockCount(exception, getLockCount());
}
exceptions.push_back(exception);
exception->loadProperties(st1, converter);
}
checkProgressIndicatorCanceled(progressIndicator);
}
setItems(exceptions);
}
//! 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();
}
//! reads indices from database
void Table::loadIndices()
{
if (indicesLoadedM)
return;
indicesM.clear();
DatabasePtr db = getDatabase();
wxMBConv* conv = db->getCharsetConverter();
MetadataLoader* loader = db->getMetadataLoader();
// first start a transaction for metadata loading, then lock the table
// when objects go out of scope and are destroyed, table will be unlocked
// before the transaction is committed - any update() calls on observers
// can possibly use the same transaction
MetadataLoaderTransaction tr(loader);
SubjectLocker lock(this);
IBPP::Statement& st1 = loader->getStatement(
"SELECT i.rdb$index_name, i.rdb$unique_flag, i.rdb$index_inactive, "
" i.rdb$index_type, i.rdb$statistics, "
" s.rdb$field_name, rc.rdb$constraint_name, i.rdb$expression_source "
" from rdb$indices i "
" left join rdb$index_segments s on i.rdb$index_name = s.rdb$index_name "
" left join rdb$relation_constraints rc "
" on rc.rdb$index_name = i.rdb$index_name "
" where i.rdb$relation_name = ? "
" order by i.rdb$index_name, s.rdb$field_position "
);
st1->Set(1, wx2std(getName_(), conv));
st1->Execute();
Index* i = 0;
while (st1->Fetch())
{
std::string s;
st1->Get(1, s);
wxString ixname(std2wxIdentifier(s, conv));
short unq, inactive, type;
if (st1->IsNull(2)) // null = non-unique
unq = 0;
else
st1->Get(2, unq);
if (st1->IsNull(3)) // null = active
inactive = 0;
else
st1->Get(3, inactive);
if (st1->IsNull(4)) // null = ascending
type = 0;
else
st1->Get(4, type);
double statistics;
if (st1->IsNull(5)) // this can happen, see bug #1825725
statistics = -1;
else
st1->Get(5, statistics);
st1->Get(6, s);
wxString fname(std2wxIdentifier(s, conv));
wxString expression;
readBlob(st1, 8, expression, conv);
if (i && i->getName_() == ixname)
i->getSegments()->push_back(fname);
else
{
Index x(
unq == 1,
inactive == 0,
type == 0,
statistics,
!st1->IsNull(7),
expression
);
indicesM.push_back(x);
i = &indicesM.back();
i->setName_(ixname);
i->getSegments()->push_back(fname);
i->setParent(this);
}
}
indicesLoadedM = true;
}
//! reads foreign keys info from database
void Table::loadForeignKeys()
{
if (foreignKeysLoadedM)
return;
foreignKeysM.clear();
DatabasePtr db = getDatabase();
wxMBConv* conv = db->getCharsetConverter();
MetadataLoader* loader = db->getMetadataLoader();
// first start a transaction for metadata loading, then lock the table
// when objects go out of scope and are destroyed, table will be unlocked
// before the transaction is committed - any update() calls on observers
// can possibly use the same transaction
MetadataLoaderTransaction tr(loader);
SubjectLocker lock(this);
IBPP::Statement& st1 = loader->getStatement(
"select r.rdb$constraint_name, i.rdb$field_name, c.rdb$update_rule, "
" c.rdb$delete_rule, c.RDB$CONST_NAME_UQ, r.rdb$index_name "
"from rdb$relation_constraints r, rdb$index_segments i, rdb$ref_constraints c "
"where r.rdb$relation_name=? and r.rdb$index_name=i.rdb$index_name "
"and r.rdb$constraint_name = c.rdb$constraint_name "
"and (r.rdb$constraint_type='FOREIGN KEY') order by 1, i.rdb$field_position"
);
IBPP::Statement& st2 = loader->getStatement(
"select r.rdb$relation_name, i.rdb$field_name"
" from rdb$relation_constraints r"
" join rdb$index_segments i on i.rdb$index_name = r.rdb$index_name "
" where r.rdb$constraint_name = ?"
" order by i.rdb$field_position "
);
st1->Set(1, wx2std(getName_(), conv));
st1->Execute();
ForeignKey *fkp = 0;
while (st1->Fetch())
{
std::string s;
st1->Get(1, s);
wxString cname(std2wxIdentifier(s, conv));
st1->Get(2, s);
wxString fname(std2wxIdentifier(s, conv));
st1->Get(3, s);
wxString update_rule(std2wxIdentifier(s, conv));
st1->Get(4, s);
wxString delete_rule(std2wxIdentifier(s, conv));
std::string ref_constraint;
st1->Get(5, ref_constraint);
st1->Get(6, s);
wxString ixname(std2wxIdentifier(s, conv));
if (fkp && fkp->getName_() == cname) // add column
fkp->columnsM.push_back(fname);
else
{
ForeignKey fk;
foreignKeysM.push_back(fk);
fkp = &foreignKeysM.back();
fkp->setName_(cname);
fkp->setParent(this);
fkp->updateActionM = update_rule;
fkp->deleteActionM = delete_rule;
fkp->indexNameM = ixname;
st2->Set(1, ref_constraint);
st2->Execute();
std::string rtable;
while (st2->Fetch())
{
st2->Get(1, rtable);
st2->Get(2, s);
fkp->referencedColumnsM.push_back(std2wxIdentifier(s, conv));
}
fkp->referencedTableM = std2wxIdentifier(rtable, conv);
fkp->columnsM.push_back(fname);
}
}
foreignKeysLoadedM = true;
}
void Domain::loadProperties(IBPP::Statement& statement, wxMBConv* converter)
{
setPropertiesLoaded(false);
statement->Get(2, &datatypeM);
if (statement->IsNull(3))
subtypeM = 0;
else
statement->Get(3, &subtypeM);
// determine the (var)char field length
// - system tables use field_len and char_len is null
// - computed columns have field_len/bytes_per_char, char_len is 0
// - view columns have field_len/bytes_per_char, char_len is null
// - regular table columns and SP params have field_len/bytes_per_char
// they also have proper char_len, but we don't use it now
statement->Get(4, &lengthM);
int bpc = 0; // bytes per char
if (!statement->IsNull(14))
statement->Get(14, &bpc);
if (bpc && (!statement->IsNull(8) || !statement->IsNull(13)))
lengthM /= bpc;
if (statement->IsNull(5))
precisionM = 0;
else
statement->Get(5, &precisionM);
if (statement->IsNull(6))
scaleM = 0;
else
statement->Get(6, &scaleM);
if (statement->IsNull(7))
charsetM = "";
else
{
std::string s;
statement->Get(7, s);
charsetM = std2wxIdentifier(s, converter);
}
bool notNull = false;
if (!statement->IsNull(9))
{
statement->Get(9, notNull);
}
nullableM = !notNull;
hasDefaultM = !statement->IsNull(10);
if (hasDefaultM)
{
readBlob(statement, 10, defaultM, converter);
defaultM = trimDefaultValue(defaultM);
}
else
defaultM = wxEmptyString;
if (statement->IsNull(11))
collationM = wxEmptyString;
else
{
std::string s;
statement->Get(11, s);
collationM = std2wxIdentifier(s, converter);
}
readBlob(statement, 12, checkM, converter);
setPropertiesLoaded(true);
}
