void MySqlDbAdapter::GetTables(Database* db, bool includeViews)
{
if (db) {
DatabaseLayerPtr dbLayer = this->GetDatabaseLayer(wxT(""));
if (dbLayer) {
if (!dbLayer->IsOpen()) return;
// lading tables for database
//TODO:SQL:
//DatabaseResultSet *tabulky = dbLayer->RunQueryWithResults(wxString::Format(wxT("SHOW TABLES IN `%s`"), db->getName().c_str()) );
DatabaseResultSet *tabulky = NULL;
if (!includeViews) {
tabulky = dbLayer->RunQueryWithResults(wxString::Format(wxT("SELECT * FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = '%s' AND `TABLE_TYPE` = 'BASE TABLE'"), db->GetName().c_str()) );
} else {
tabulky = dbLayer->RunQueryWithResults(wxString::Format(wxT("SELECT * FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = '%s' AND (`TABLE_TYPE` = 'BASE TABLE' OR `TABLE_TYPE` = 'VIEW')"), db->GetName().c_str()) );
}
if (tabulky) {
while (tabulky->Next()) {
db->AddChild(new Table(this, tabulky->GetResultString(wxT("TABLE_NAME")), db->GetName(), tabulky->GetResultString(wxT("TABLE_TYPE")).Contains(wxT("VIEW"))));
}
dbLayer->CloseResultSet(tabulky);
}
dbLayer->Close();
}
}
return;
}
void MySqlDbAdapter::GetViews(Database* db)
{
DatabaseLayerPtr dbLayer = this->GetDatabaseLayer(wxT(""));
if (!dbLayer->IsOpen()) return;
// loading columns
//TODO:SQL:
DatabaseResultSet *database = dbLayer->RunQueryWithResults(wxString::Format(wxT("SELECT * FROM `INFORMATION_SCHEMA`.`VIEWS` WHERE TABLE_SCHEMA = '%s'"),db->GetName().c_str()));
while (database->Next()) {
View* pView = new View(this,database->GetResultString(wxT("TABLE_NAME")),db->GetName(),database->GetResultString(wxT("VIEW_DEFINITION")));
db->AddChild(pView);
}
dbLayer->CloseResultSet(database);
}
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;
}
void PostgreSqlDbAdapter::GetDatabases(DbConnection* dbCon) {
if (dbCon) {
DatabaseLayerPtr dbLayer = this->GetDatabaseLayer(wxT(""));
if (dbLayer) {
if (!dbLayer->IsOpen()) return;
// loading databases
//TODO:SQL:
DatabaseResultSet *databaze = dbLayer->RunQueryWithResults(wxT("SELECT datname FROM pg_database WHERE datallowconn = 't' "));
while (databaze->Next()) {
dbCon->AddChild( new Database(this, databaze->GetResultString(1)) );
}
dbLayer->CloseResultSet(databaze);
dbLayer->Close();
}
}
return;
}
void MySqlDbAdapter::GetDatabases(DbConnection* dbCon)
{
if (dbCon) {
DatabaseLayerPtr dbLayer = this->GetDatabaseLayer(wxT(""));
if (dbLayer) {
if (!dbLayer->IsOpen()) return;
// loading databases
//TODO:SQL:
DatabaseResultSet *databaze = dbLayer->RunQueryWithResults(wxT("SHOW DATABASES"));
while (databaze->Next()) {
dbCon->AddChild( new Database(this, databaze->GetResultString(1)) );
}
dbLayer->CloseResultSet(databaze);
dbLayer->Close();
}
}
return;
}
void PostgreSqlDbAdapter::GetViews(Database* db) {
if (db) {
//SetDatabase(db->GetName());
DatabaseLayerPtr dbLayer = this->GetDatabaseLayer(db->GetName());
if (dbLayer) {
if (!dbLayer->IsOpen()) return;
// lading tables for database
//TODO:SQL:
//DatabaseResultSet *tabulky = dbLayer->RunQueryWithResults(wxString::Format(wxT("SHOW TABLES IN `%s`"), db->getName().c_str()) );
//DatabaseResultSet *tabulky = dbLayer->RunQueryWithResults(wxString::Format(wxT("SELECT * FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = '%s' AND `TABLE_TYPE` = 'BASE TABLE'"), db->getName().c_str()) );
DatabaseResultSet *tabulky = dbLayer->RunQueryWithResults(wxString::Format(wxT("SELECT * FROM pg_views WHERE schemaname = 'public'")) );
while (tabulky->Next()) {
db->AddChild(new View(this, tabulky->GetResultString(wxT("viewname")),db->GetName(), tabulky->GetResultString(wxT("definition"))));
}
dbLayer->CloseResultSet(tabulky);
dbLayer->Close();
}
}
return;
}
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;
}