Exemple #1
0
void TableSettings::OnKeyChanged(wxDataViewEvent& event)
{
    Constraint *key = reinterpret_cast<Constraint*>( m_dvKeys->GetItemData( event.GetItem() ) );
    if( key ) {
        wxVariant val;
        event.GetModel()->GetValue( val, event.GetItem(), event.GetColumn() );
        if( ! val.IsNull() ) {
            key->SetName( val.GetString() );
        }
    }

    event.Skip();
}
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;
}
Exemple #3
0
void TableSettings::OnColumnChanged(wxDataViewEvent& event)
{
    Column *col = reinterpret_cast<Column*>(m_dvColumns->GetItemData( event.GetItem() ) );
    if( col ) {
        wxVariant val;
        event.GetModel()->GetValue( val, event.GetItem(), event.GetColumn() );
        if( ! val.IsNull() ) {
            switch( event.GetColumn() ) {
            case 0: {
                // rename local columns in keys
                SerializableList keys;
                GetConstraints( keys, col->GetName() );
                for(SerializableList::iterator it = keys.begin(); it != keys.end(); ++it ) {
                    Constraint *key = (Constraint*) *it;
                    if( key->GetType() == Constraint::primaryKey ) key->SetName( wxT("PK_") + val.GetString() );
                    key->SetLocalColumn( val.GetString() );
                }
                // rename table column
                col->SetName( val.GetString() );
                break;
            }
            case 1: {
                col->SetType( m_pDbAdapter->GetDbTypeByName( val.GetString() ) );
                break;
            }
            case 2: {
                long s1, s2;
                s1 = s2 = 0;
                wxSscanf( val.GetString(), wxT("%ld,%ld"), &s1, &s2 );
                IDbType *type = col->GetType();
                if( type->HaveSize() ) type->SetSize( s1 );
                else {
                    m_infobar->ShowMessage( wxT("This data type doesn't support size definition."), wxICON_WARNING );
                    Refresh();
                }
                if( type->HaveSize2() ) type->SetSize2( s1 );
                else { 
                    m_infobar->ShowMessage( wxT("This data type doesn't support size definition."), wxICON_WARNING );
                    Refresh();
                }
                break;
            }
            case 3: {
                IDbType *type = col->GetType();
                if( type->HaveNotNull() ) type->SetNotNull( val.GetBool() );
                else {
                    m_infobar->ShowMessage( wxT("This data type doesn't support NOT NULL feature."), wxICON_WARNING );
                    Refresh();
                }
                break;
            }
            case 4: {
                IDbType *type = col->GetType();
                if( type->HaveAutoIncrement() ) type->SetAutoIncrement( val.GetBool() );
                else { 
                    m_infobar->ShowMessage( wxT("This data type doesn't support AUTOINCREMENT feature."), wxICON_WARNING );
                    Refresh();
                }
                break;
            }
            case 5: {
                Constraint *key = GetConstraint( Constraint::primaryKey, col->GetName() );
                if( key ) {
                    // remove primary key if exists
                    m_lstKeys.DeleteObject( key );
                    delete key;
                } else {
                    // create new primary key
                    key = new Constraint( wxT("PK_") + col->GetName(),
                                          col->GetName(),
                                          Constraint::primaryKey,
                                          Constraint::noAction,
                                          Constraint::noAction );

                    m_lstKeys.Append( key );
                }
                break;
            }
            }
        }
    }

    event.Skip();

    UpdateView();
}
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;
}