示例#1
0
bool MySqlDbAdapter::GetColumns(Table* pTab)
{
	DatabaseLayerPtr dbLayer = this->GetDatabaseLayer(wxT(""));

	if (!dbLayer->IsOpen()) return NULL;
	// loading columns
	//TODO:SQL:
	DatabaseResultSet *database = dbLayer->RunQueryWithResults(wxString::Format(wxT("SHOW COLUMNS IN `%s`.`%s`"),pTab->GetParentName().c_str(),pTab->GetName().c_str()));
	while (database->Next()) {
		IDbType* pType = parseTypeString(database->GetResultString(2));
		if (pType) {
			Column* pCol = new Column(database->GetResultString(1),pTab->GetName(), pType);
			pTab->AddChild(pCol);
		}
	}
	dbLayer->CloseResultSet(database);

	//TODO:SQL:
	wxString constrSql = wxT("SELECT K.CONSTRAINT_SCHEMA, K.CONSTRAINT_NAME,K.TABLE_NAME,K.COLUMN_NAME,K.REFERENCED_TABLE_NAME,K.REFERENCED_COLUMN_NAME,R.UPDATE_RULE, R.DELETE_RULE FROM information_schema.KEY_COLUMN_USAGE K LEFT JOIN information_schema.REFERENTIAL_CONSTRAINTS R ON R.CONSTRAINT_NAME = K.CONSTRAINT_NAME AND K.CONSTRAINT_SCHEMA = R.CONSTRAINT_SCHEMA WHERE K.CONSTRAINT_SCHEMA = '%s' AND K.TABLE_NAME = '%s'");
	database = dbLayer->RunQueryWithResults(wxString::Format(constrSql, pTab->GetParentName().c_str(),pTab->GetName().c_str()));
	while (database->Next()) {
		Constraint* constr = new Constraint();
		constr->SetName(database->GetResultString(wxT("CONSTRAINT_NAME")));
		constr->SetLocalColumn(database->GetResultString(wxT("COLUMN_NAME")));
		constr->SetType(Constraint::primaryKey);
		if (database->GetResultString(wxT("REFERENCED_TABLE_NAME")) != wxT("") ) {
			constr->SetType(Constraint::foreignKey);
			constr->SetRefTable(database->GetResultString(wxT("REFERENCED_TABLE_NAME")));
			constr->SetRefCol(database->GetResultString(wxT("REFERENCED_COLUMN_NAME")));

			wxString onDelete = database->GetResultString(wxT("UPDATE_RULE"));
			if (onDelete == wxT("RESTRICT")) constr->SetOnUpdate(Constraint::restrict);
			if (onDelete == wxT("CASCADE")) constr->SetOnUpdate(Constraint::cascade);
			if (onDelete == wxT("SET NULL")) constr->SetOnUpdate(Constraint::setNull);
			if (onDelete == wxT("NO ACTION")) constr->SetOnUpdate(Constraint::noAction);

			wxString onUpdate = database->GetResultString(wxT("DELETE_RULE"));
			if (onUpdate == wxT("RESTRICT")) constr->SetOnDelete(Constraint::restrict);
			if (onUpdate == wxT("CASCADE")) constr->SetOnDelete(Constraint::cascade);
			if (onUpdate == wxT("SET NULL")) constr->SetOnDelete(Constraint::setNull);
			if (onUpdate == wxT("NO ACTION")) constr->SetOnDelete(Constraint::noAction);


		}
		pTab->AddChild(constr);
	}
	dbLayer->CloseResultSet(database);
	dbLayer->Close();
	return true;
}
示例#2
0
void DatabaseCanvas::CreateFKConstraint(const DatabaseTable *fkTable, const std::vector<FKField *> &foreignKeyField)
{
    Constraint* pConstr = NULL;
    bool found = false;
    for( std::vector<MyErdTable *>::iterator it = m_displayedTables.begin(); it < m_displayedTables.end() && !found; it ++ )
    {
        if( const_cast<DatabaseTable &>( (*it)->GetTable() ).GetTableName() == foreignKeyField.at( 0 )->GetReferencedTableName() )
        {
            found = true;
            for( std::vector<FKField *>::const_iterator it4 = foreignKeyField.begin(); it4 < foreignKeyField.end(); it4++ )
            {
                if( ((DrawingView *) m_view)->GetViewType() == DatabaseView )
                {
                    pConstr = new DatabaseConstraint( (*it4)->GetFKName() );
                    pConstr->SetLocalColumn( (*it4)->GetOriginalFieldName() );
                    pConstr->SetRefColumn( (*it4)->GetReferencedFieldName() );
                    pConstr->SetRefTable( (*it4)->GetReferencedTableName() );
                    pConstr->SetType( Constraint::foreignKey );
                    pConstr->SetFKDatabaseTable( fkTable );
                    pConstr->SetPGMatch( (*it4)->GetMatchOPtion() );
                    switch( foreignKeyField.at( 0 )->GetOnUpdateConstraint() )
                    {
                        case RESTRICT_UPDATE:
                            pConstr->SetOnUpdate( Constraint::restrict );
                            break;
                        case SET_NULL_UPDATE:
                            pConstr->SetOnUpdate( Constraint::setNull );
                            break;
                        case SET_DEFAULT_UPDATE:
                        case CASCADE_UPDATE:
                            pConstr->SetOnUpdate( Constraint::cascade );
                            break;
                        case NO_ACTION_UPDATE:
                            pConstr->SetOnUpdate( Constraint::noAction );
                            break;
                    }
                    switch( foreignKeyField.at( 0 )->GetOnDeleteConstraint() )
                    {
                        case RESTRICT_DELETE:
                            pConstr->SetOnUpdate( Constraint::restrict );
                            break;
                        case SET_NULL_DELETE:
                            pConstr->SetOnUpdate( Constraint::setNull );
                            break;
                        case SET_DEFAULT_DELETE:
                        case CASCADE_DELETE:
                            pConstr->SetOnUpdate( Constraint::cascade );
                            break;
                        case NO_ACTION_DELETE:
                            pConstr->SetOnUpdate( Constraint::noAction );
                            break;
                    }
                    (*it)->GetShapeManager()->CreateConnection( (*it)->GetId(), dynamic_cast<DrawingDocument *>( m_view->GetDocument() )->GetReferencedTable( foreignKeyField.at( 0 )->GetReferencedTableName() )->GetId(), new ErdForeignKey( pConstr, ((DrawingView *) m_view)->GetViewType(), GetDiagramManager() ), sfDONT_SAVE_STATE );
                }
            }
        }
    }
}
示例#3
0
void DatabaseCanvas::DisplayTables(std::vector<wxString> &selections, wxString &query)
{
    std::vector<MyErdTable *> tables = ((DrawingDocument *)m_view->GetDocument())->GetTables();
    for( std::vector<MyErdTable *>::iterator it = tables.begin(); it < tables.end(); it++ ) 
    {
        if( !IsTableDisplayed( (*it)->GetTableName() ) )
        {
            std::vector<Field *> fields = const_cast<DatabaseTable &>( (*it)->GetTable() ).GetFields();
            m_pManager.AddShape( (*it), NULL, startPoint, sfINITIALIZE, sfDONT_SAVE_STATE );
            if( (*it) == tables.back() && dynamic_cast<DrawingView *>( m_view )->GetViewType() == DatabaseView )
                (*it)->Select( true );
            (*it)->UpdateTable();
            if( dynamic_cast<DrawingView *>( m_view )->GetViewType() == QueryView )
            {
                for( std::vector<Field *>::iterator it1 = fields.begin(); it1 < fields.end(); it1++ )
                {
                    dynamic_cast<DrawingView *>( m_view )->GetWherePage()->AppendField( (*it)->GetTableName() + L"." + (*it1)->GetFieldName() );
                    dynamic_cast<DrawingView *>( m_view )->GetHavingPage()->AppendField( (*it)->GetTableName() + L"." + (*it1)->GetFieldName() );
                }
            }
            wxRect rect = (*it)->GetBoundingBox();
            startPoint.x += 200 + rect.GetWidth();
            m_displayedTables.push_back( (*it) );
        }
    }
    for( std::vector<MyErdTable *>::iterator it1 = m_displayedTables.begin(); it1 < m_displayedTables.end(); it1++ )
    {
        wxString name = const_cast<DatabaseTable &>( (*it1)->GetTable() ).GetTableName();
        if( std::find( selections.begin(), selections.end(), name ) == selections.end() )
            selections.push_back( name );
        if( dynamic_cast<DrawingView *>( m_view )->GetViewType() == QueryView )
        {
            query += (*it1)->GetTableName();
            if( it1 != m_displayedTables.end() - 1 )
                query += ", ";
        }
    }
    if( dynamic_cast<DrawingView *>( m_view )->GetViewType() == QueryView )
        query += "\n";
//    Refresh();
    Constraint *pConstr = NULL;
    bool found = false, secondIteration = false;
    for( std::vector<MyErdTable *>::iterator it2 = tables.begin(); it2 < tables.end(); it2++ )
    {
        std::map<int, std::vector<FKField *> > foreignKeys = const_cast<DatabaseTable &>( (*it2)->GetTable() ).GetForeignKeyVector();
        for( std::map<int, std::vector<FKField *> >::iterator it3 = foreignKeys.begin(); it3 != foreignKeys.end(); it3++ )
        {
//            if( ((DrawingView *) m_view)->GetViewType() == DatabaseView )
//                pConstr = new DatabaseConstraint( (*it3).second );
            for( std::vector<FKField *>::iterator it4 = (*it3).second.begin(); it4 < (*it3).second.end(); it4++ )
            {
                wxString referencedTableName = (*it4)->GetReferencedTableName();
                if( std::find( selections.begin(), selections.end(), referencedTableName ) != selections.end() )
                {
                    if( found )
                        secondIteration = true;
                    if( !found )
                    {
                        query += "WHERE ";
                        found = true;
                    }
                    if( ((DrawingView *) m_view)->GetViewType() == QueryView )
                    {
                        pConstr = new QueryConstraint( ((DrawingView *) m_view)->GetViewType() );
                        pConstr->SetLocalColumn( (*it4)->GetOriginalFieldName() );
                        pConstr->SetRefColumn( (*it4)->GetReferencedFieldName() );
                        pConstr->SetRefTable( referencedTableName );
                        pConstr->SetType( QueryConstraint::foreignKey );
                        pConstr->SetFKDatabaseTable( &(*it2)->GetTable() );
                        dynamic_cast<QueryConstraint *>( pConstr )->SetSign( 0 );
                    }
                    if( ((DrawingView *) m_view)->GetViewType() == DatabaseView )
                    {
                        pConstr = new DatabaseConstraint( (*it4)->GetFKName() );
                        pConstr->SetLocalColumns( (*it4)->GetOriginalFields() );
                        pConstr->SetRefColumns( (*it4)->GetReferencedFields() );
                        pConstr->SetLocalColumn( (*it4)->GetOriginalFieldName() );
                        pConstr->SetRefColumn( (*it4)->GetReferencedFieldName() );
                        pConstr->SetRefTable( referencedTableName );
                        pConstr->SetType( QueryConstraint::foreignKey );
                        pConstr->SetFKDatabaseTable( &(*it2)->GetTable() );
                        pConstr->SetPGMatch( (*it4)->GetMatchOPtion() );
                    }
                    if( dynamic_cast<DrawingView *>( m_view )->GetViewType() == QueryView )
                    {
                        if( secondIteration )
                            query += " AND ";
                        query += wxString::Format( "%s.%s = %s.%s", (*it2)->GetTableName(), (*it4)->GetOriginalFieldName(), referencedTableName, (*it4)->GetReferencedFieldName() );
                    }
                    switch( (*it4)->GetOnUpdateConstraint() )
                    {
                        case RESTRICT_UPDATE:
                            pConstr->SetOnUpdate( Constraint::restrict );
                            break;
                        case SET_NULL_UPDATE:
                            pConstr->SetOnUpdate( Constraint::setNull );
                            break;
                        case SET_DEFAULT_UPDATE:
                        case CASCADE_UPDATE:
                            pConstr->SetOnUpdate( Constraint::cascade );
                            break;
                        case NO_ACTION_UPDATE:
                            pConstr->SetOnUpdate( Constraint::noAction );
                            break;
                    }
                    switch( (*it4)->GetOnDeleteConstraint() )
                    {
                        case RESTRICT_DELETE:
                            pConstr->SetOnUpdate( Constraint::restrict );
                            break;
                        case SET_NULL_DELETE:
                            pConstr->SetOnUpdate( Constraint::setNull );
                            break;
                        case SET_DEFAULT_DELETE:
                        case CASCADE_DELETE:
                            pConstr->SetOnUpdate( Constraint::cascade );
                            break;
                        case NO_ACTION_DELETE:
                            pConstr->SetOnUpdate( Constraint::noAction );
                            break;
                    }
                    (*it2)->GetShapeManager()->CreateConnection( (*it2)->GetId(), dynamic_cast<DrawingDocument *>( m_view->GetDocument() )->GetReferencedTable( referencedTableName )->GetId(), new ErdForeignKey( pConstr, ((DrawingView *) m_view)->GetViewType(), m_pManager ), sfDONT_SAVE_STATE );
                }
            }
        }
    }
    query += ";";
    Refresh();
}
示例#4
0
bool PostgreSqlDbAdapter::GetColumns(Table* pTab) {
	if (pTab) {
//		SetDatabase(pTab->GetParentName());
		DatabaseLayerPtr dbLayer = this->GetDatabaseLayer(pTab->GetParentName());

		if (!dbLayer->IsOpen()) return NULL;
		// loading columns
		//TODO:SQL:
		//DatabaseResultSet *database = dbLayer->RunQueryWithResults(wxString::Format(wxT("SHOW COLUMNS IN `%s`.`%s`"),pTab->getParentName().c_str(),pTab->getName().c_str()));
		DatabaseResultSet *database = dbLayer->RunQueryWithResults(wxString::Format(wxT("SELECT * FROM information_schema.columns WHERE table_name = '%s'"),pTab->GetName().c_str()));


		while (database->Next()) {
			IDbType* pType = parseTypeString(database->GetResultString(wxT("data_type")));
			if (pType) {
				pType->SetSize(database->GetResultInt(wxT("numeric_precision")));
				pType->SetSize2(database->GetResultInt(wxT("numeric_precision_radix")));
				pType->SetNotNull(database->GetResultString(wxT("is_nullable")) == wxT("NO"));
				Column* pCol = new Column(database->GetResultString(wxT("column_name")),pTab->GetName(), pType);
				pTab->AddChild(pCol);
			}
		}
		dbLayer->CloseResultSet(database);



//wxT("SELECT tc.constraint_name, tc.constraint_type, tc.table_name, kcu.column_name, tc.is_deferrable, tc.initially_deferred, rc.match_option AS match_type, rc.update_rule AS on_update, rc.delete_rule AS on_delete, ccu.table_name AS references_table, ccu.column_name AS references_field FROM information_schema.table_constraints tc LEFT JOIN information_schema.key_column_usage kcu ON tc.constraint_catalog = kcu.constraint_catalog AND tc.constraint_schema = kcu.constraint_schema AND tc.constraint_name = kcu.constraint_name LEFT JOIN information_schema.referential_constraints rc ON tc.constraint_catalog = rc.constraint_catalog AND tc.constraint_schema = rc.constraint_schema AND tc.constraint_name = rc.constraint_name LEFT JOIN information_schema.constraint_column_usage ccu ON rc.unique_constraint_catalog = ccu.constraint_catalog AND rc.unique_constraint_schema = ccu.constraint_schema AND rc.unique_constraint_name = ccu.constraint_name WHERE tc.table_name = '%s'");



		//TODO:SQL:
		wxString constrSql = wxT("SELECT tc.constraint_name, tc.constraint_type, tc.table_name, kcu.column_name, tc.is_deferrable, tc.initially_deferred, rc.match_option AS match_type, rc.update_rule AS on_update, rc.delete_rule AS on_delete, ccu.table_name AS references_table, ccu.column_name AS references_field FROM information_schema.table_constraints tc LEFT JOIN information_schema.key_column_usage kcu ON tc.constraint_catalog = kcu.constraint_catalog AND tc.constraint_schema = kcu.constraint_schema AND tc.constraint_name = kcu.constraint_name LEFT JOIN information_schema.referential_constraints rc ON tc.constraint_catalog = rc.constraint_catalog AND tc.constraint_schema = rc.constraint_schema AND tc.constraint_name = rc.constraint_name LEFT JOIN information_schema.constraint_column_usage ccu ON rc.unique_constraint_catalog = ccu.constraint_catalog AND rc.unique_constraint_schema = ccu.constraint_schema AND rc.unique_constraint_name = ccu.constraint_name WHERE tc.table_name = '%s'");

		database = dbLayer->RunQueryWithResults(wxString::Format(constrSql, pTab->GetName().c_str()));
		while (database->Next()) {
			if ((database->GetResultString(wxT("constraint_type")) == wxT("PRIMARY KEY"))||(database->GetResultString(wxT("constraint_type")) == wxT("FOREIGN KEY"))) {
				Constraint* constr = new Constraint();
				constr->SetName(database->GetResultString(wxT("constraint_name")));
				constr->SetLocalColumn(database->GetResultString(wxT("column_name")));
				constr->SetType(Constraint::primaryKey);
				if (database->GetResultString(wxT("references_table")) != wxT("") ) {
					constr->SetType(Constraint::foreignKey);
					constr->SetRefTable(database->GetResultString(wxT("references_table")));
					constr->SetRefCol(database->GetResultString(wxT("references_field")));

					wxString onDelete = database->GetResultString(wxT("on_update"));
					if (onDelete == wxT("RESTRICT")) constr->SetOnUpdate(Constraint::restrict);
					if (onDelete == wxT("CASCADE")) constr->SetOnUpdate(Constraint::cascade);
					if (onDelete == wxT("SET NULL")) constr->SetOnUpdate(Constraint::setNull);
					if (onDelete == wxT("NO ACTION")) constr->SetOnUpdate(Constraint::noAction);

					wxString onUpdate = database->GetResultString(wxT("on_delete"));
					if (onUpdate == wxT("RESTRICT")) constr->SetOnDelete(Constraint::restrict);
					if (onUpdate == wxT("CASCADE")) constr->SetOnDelete(Constraint::cascade);
					if (onUpdate == wxT("SET NULL")) constr->SetOnDelete(Constraint::setNull);
					if (onUpdate == wxT("NO ACTION")) constr->SetOnDelete(Constraint::noAction);


				}
				pTab->AddChild(constr);
			}
		}
		dbLayer->CloseResultSet(database);
		dbLayer->Close();
	}
	return true;
}