Esempio n. 1
0
//! 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;
}
Esempio n. 2
0
//! 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;
}
Esempio n. 4
0
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);
}
Esempio n. 5
0
//! 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();
}
Esempio n. 6
0
//! 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;
}
Esempio n. 7
0
//! 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;
}
Esempio n. 8
0
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);
}