| 1 |
////////////////////////////////////////////////////////////////////////////// |
| 2 |
////////////////////////////////////////////////////////////////////////////// |
| 3 |
// |
| 4 |
// copyright : (C) 2014 Eran Ifrah |
| 5 |
// file name : MySqlDbAdapter.cpp |
| 6 |
// |
| 7 |
// ------------------------------------------------------------------------- |
| 8 |
// A |
| 9 |
// _____ _ _ _ _ |
| 10 |
// / __ \ | | | | (_) | |
| 11 |
// | / \/ ___ __| | ___| | _| |_ ___ |
| 12 |
// | | / _ \ / _ |/ _ \ | | | __/ _ ) |
| 13 |
// | \__/\ (_) | (_| | __/ |___| | || __/ |
| 14 |
// \____/\___/ \__,_|\___\_____/_|\__\___| |
| 15 |
// |
| 16 |
// F i l e |
| 17 |
// |
| 18 |
// This program is free software; you can redistribute it and/or modify |
| 19 |
// it under the terms of the GNU General Public License as published by |
| 20 |
// the Free Software Foundation; either version 2 of the License, or |
| 21 |
// (at your option) any later version. |
| 22 |
// |
| 23 |
////////////////////////////////////////////////////////////////////////////// |
| 24 |
////////////////////////////////////////////////////////////////////////////// |
| 25 |
|
| 26 |
#include "MySqlDbAdapter.h" |
| 27 |
#include "constraint.h" |
| 28 |
#include "database.h" |
| 29 |
#include "dbconnection.h" |
| 30 |
#include "table.h" |
| 31 |
#include "view.h" |
| 32 |
#include <wx/regex.h> |
| 33 |
|
| 34 |
MySqlDbAdapter::MySqlDbAdapter() |
| 35 |
{ |
| 36 |
this->m_serverName = wxT(""); |
| 37 |
this->m_userName = wxT(""); |
| 38 |
this->m_password = wxT(""); |
| 39 |
this->m_adapterType = atMYSQL; |
| 40 |
this->m_pDbLayer = NULL; |
| 41 |
} |
| 42 |
MySqlDbAdapter::MySqlDbAdapter(const wxString& serverName, const wxString& userName, const wxString& password) |
| 43 |
{ |
| 44 |
this->m_serverName = serverName; |
| 45 |
this->m_userName = userName; |
| 46 |
this->m_password = password; |
| 47 |
this->m_adapterType = atMYSQL; |
| 48 |
this->m_pDbLayer = NULL; |
| 49 |
} |
| 50 |
|
| 51 |
MySqlDbAdapter::~MySqlDbAdapter() {} |
| 52 |
|
| 53 |
void MySqlDbAdapter::CloseConnection() |
| 54 |
{ |
| 55 |
if(this->m_pDbLayer) |
| 56 |
this->m_pDbLayer->Close(); |
| 57 |
} |
| 58 |
|
| 59 |
DatabaseLayerPtr MySqlDbAdapter::GetDatabaseLayer(const wxString& dbName) |
| 60 |
{ |
| 61 |
DatabaseLayer* dbLayer = NULL; |
| 62 |
|
| 63 |
#ifdef DBL_USE_MYSQL |
| 64 |
if(!CanConnect()) |
| 65 |
return new MysqlDatabaseLayer(); |
| 66 |
dbLayer = new MysqlDatabaseLayer(this->m_serverName, wxT(""), this->m_userName, this->m_password); |
| 67 |
#endif |
| 68 |
this->m_pDbLayer = dbLayer; |
| 69 |
|
| 70 |
return dbLayer; |
| 71 |
} |
| 72 |
|
| 73 |
bool MySqlDbAdapter::IsConnected() |
| 74 |
{ |
| 75 |
if(this->m_pDbLayer) |
| 76 |
return this->m_pDbLayer->IsOpen(); |
| 77 |
else |
| 78 |
return false; |
| 79 |
} |
| 80 |
|
| 81 |
wxString MySqlDbAdapter::GetCreateTableSql(Table* tab, bool dropTable) |
| 82 |
{ |
| 83 |
// TODO:SQL: |
| 84 |
wxString str = wxT(""); |
| 85 |
if(dropTable) |
| 86 |
str = wxString::Format( |
| 87 |
wxT("SET FOREIGN_KEY_CHECKS = 0;\nDROP TABLE IF EXISTS `%s` ;\nSET FOREIGN_KEY_CHECKS = 1; \n"), |
| 88 |
tab->GetName().c_str()); |
| 89 |
str.append(wxString::Format(wxT("CREATE TABLE `%s` (\n"), tab->GetName().c_str())); |
| 90 |
|
| 91 |
SerializableList::compatibility_iterator node = tab->GetFirstChildNode(); |
| 92 |
while(node) { |
| 93 |
Column* col = NULL; |
| 94 |
if(node->GetData()->IsKindOf(CLASSINFO(Column))) |
| 95 |
col = (Column*)node->GetData(); |
| 96 |
if(col) |
| 97 |
str.append(wxString::Format(wxT("\t`%s` %s"), col->GetName().c_str(), col->GetType()->ReturnSql().c_str())); |
| 98 |
|
| 99 |
Constraint* constr = wxDynamicCast(node->GetData(), Constraint); |
| 100 |
if(constr) { |
| 101 |
if(constr->GetType() == Constraint::primaryKey) |
| 102 |
str.append(wxString::Format(wxT("\tPRIMARY KEY (`%s`) \n"), constr->GetLocalColumn().c_str())); |
| 103 |
} |
| 104 |
|
| 105 |
node = node->GetNext(); |
| 106 |
if(node) { |
| 107 |
if(wxDynamicCast(node->GetData(), Column)) |
| 108 |
str.append(wxT(",\n ")); |
| 109 |
else if((constr = wxDynamicCast(node->GetData(), Constraint))) { |
| 110 |
if(constr->GetType() == Constraint::primaryKey) |
| 111 |
str.append(wxT(",\n ")); |
| 112 |
} |
| 113 |
} |
| 114 |
// else str.append(wxT("\n ")) ; |
| 115 |
} |
| 116 |
|
| 117 |
/* Column* col = tab->GetFristColumn(); |
| 118 |
while (col) { |
| 119 |
str.append(wxString::Format(wxT("\t`%s` %s"),col->getName().c_str(), col->getPType()->ReturnSql().c_str())); |
| 120 |
col = wxDynamicCast(col->GetSibbling(),Column); |
| 121 |
if (col) str.append(wxT(",\n ")) ; |
| 122 |
else str.append(wxT("\n ")) ; |
| 123 |
}*/ |
| 124 |
|
| 125 |
str.append(wxT("\n) ENGINE=INNODB;\n")); |
| 126 |
str.append(wxT("-- -------------------------------------------------------------\n")); |
| 127 |
return str; |
| 128 |
} |
| 129 |
|
| 130 |
IDbType* MySqlDbAdapter::GetDbTypeByName(const wxString& typeName) |
| 131 |
{ |
| 132 |
IDbType* type = NULL; |
| 133 |
if(typeName == wxT("INT")) { |
| 134 |
type = new MySqlType(wxT("INT"), IDbType::dbtAUTO_INCREMENT | IDbType::dbtNOT_NULL | IDbType::dbtSIZE, |
| 135 |
IDbType::dbtTYPE_INT); |
| 136 |
} else if(typeName == wxT("VARCHAR")) { |
| 137 |
type = new MySqlType(wxT("VARCHAR"), IDbType::dbtUNIQUE | IDbType::dbtNOT_NULL | IDbType::dbtSIZE, |
| 138 |
IDbType::dbtTYPE_TEXT); |
| 139 |
} else if(typeName == wxT("DOUBLE")) { |
| 140 |
type = new MySqlType(wxT("DOUBLE"), IDbType::dbtAUTO_INCREMENT | IDbType::dbtNOT_NULL | IDbType::dbtSIZE, |
| 141 |
IDbType::dbtTYPE_DECIMAL); |
| 142 |
} else if(typeName == wxT("FLOAT")) { |
| 143 |
type = new MySqlType(wxT("FLOAT"), IDbType::dbtUNIQUE | IDbType::dbtNOT_NULL, IDbType::dbtTYPE_FLOAT); |
| 144 |
} else if(typeName == wxT("DECIMAL")) { |
| 145 |
type = new MySqlType(wxT("DECIMAL"), |
| 146 |
IDbType::dbtUNIQUE | IDbType::dbtNOT_NULL | IDbType::dbtSIZE | IDbType::dbtSIZE_TWO, |
| 147 |
IDbType::dbtTYPE_DECIMAL); |
| 148 |
} else if(typeName == wxT("BOOL")) { |
| 149 |
type = new MySqlType(wxT("BOOL"), 0, IDbType::dbtTYPE_BOOLEAN); |
| 150 |
} else if(typeName == wxT("DATETIME")) { |
| 151 |
type = new MySqlType(wxT("DATETIME"), IDbType::dbtUNIQUE | IDbType::dbtNOT_NULL, IDbType::dbtTYPE_DATE_TIME); |
| 152 |
} else if(typeName == wxT("TINYINT")) { |
| 153 |
type = new MySqlType(wxT("TINYINT"), IDbType::dbtAUTO_INCREMENT | IDbType::dbtNOT_NULL | IDbType::dbtSIZE, |
| 154 |
IDbType::dbtTYPE_INT); |
| 155 |
} else if(typeName == wxT("BIGINT")) { |
| 156 |
type = new MySqlType(wxT("BIGINT"), IDbType::dbtAUTO_INCREMENT | IDbType::dbtNOT_NULL | IDbType::dbtSIZE, |
| 157 |
IDbType::dbtTYPE_INT); |
| 158 |
} else if(typeName == wxT("SMALLINT")) { |
| 159 |
type = new MySqlType(wxT("SMALLINT"), IDbType::dbtAUTO_INCREMENT | IDbType::dbtNOT_NULL | IDbType::dbtSIZE, |
| 160 |
IDbType::dbtTYPE_INT); |
| 161 |
} else if(typeName == wxT("CHAR")) { |
| 162 |
type = new MySqlType(wxT("CHAR"), IDbType::dbtNOT_NULL | IDbType::dbtSIZE, IDbType::dbtTYPE_TEXT); |
| 163 |
} else if(typeName == wxT("TIMESTAMP") || typeName == wxT("TIME")) { |
| 164 |
type = new MySqlType(wxT("TIMESTAMP"), 0, IDbType::dbtTYPE_DATE_TIME); |
| 165 |
} else if(typeName == wxT("ENUM")) { |
| 166 |
type = new MySqlType(wxT("ENUM"), 0, IDbType::dbtTYPE_OTHER); |
| 167 |
} else if(typeName == wxT("SET")) { |
| 168 |
type = new MySqlType(wxT("SET"), 0, IDbType::dbtTYPE_OTHER); |
| 169 |
} else if(typeName == wxT("LONGBLOB")) { |
| 170 |
type = new MySqlType(wxT("LONGBLOB"), 0, IDbType::dbtTYPE_OTHER); |
| 171 |
} else if(typeName == wxT("BLOB")) { |
| 172 |
type = new MySqlType(wxT("BLOB"), 0, IDbType::dbtTYPE_OTHER); |
| 173 |
} else if(typeName == wxT("MEDIUMTEXT")) { |
| 174 |
type = new MySqlType(wxT("MEDIUMTEXT"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_TEXT); |
| 175 |
} else if(typeName == wxT("TEXT")) { |
| 176 |
type = new MySqlType(wxT("TEXT"), 0, IDbType::dbtTYPE_TEXT); |
| 177 |
} else if(typeName == wxT("LONGTEXT")) { |
| 178 |
type = new MySqlType(wxT("LONGTEXT"), 0, IDbType::dbtTYPE_TEXT); |
| 179 |
} |
| 180 |
// wxASSERT(type); |
| 181 |
return type; |
| 182 |
} |
| 183 |
|
| 184 |
wxArrayString* MySqlDbAdapter::GetDbTypes() |
| 185 |
{ |
| 186 |
wxArrayString* pNames = new wxArrayString(); |
| 187 |
pNames->Add(wxT("INT")); |
| 188 |
pNames->Add(wxT("SMALLINT")); |
| 189 |
pNames->Add(wxT("BIGINT")); |
| 190 |
pNames->Add(wxT("TINYINT")); |
| 191 |
pNames->Add(wxT("VARCHAR")); |
| 192 |
pNames->Add(wxT("DOUBLE")); |
| 193 |
pNames->Add(wxT("FLOAT")); |
| 194 |
pNames->Add(wxT("DECIMAL")); |
| 195 |
pNames->Add(wxT("BOOL")); |
| 196 |
pNames->Add(wxT("DATETIME")); |
| 197 |
pNames->Add(wxT("CHAR")); |
| 198 |
pNames->Add(wxT("TIMESTAMP")); |
| 199 |
pNames->Add(wxT("ENUM")); |
| 200 |
pNames->Add(wxT("SET")); |
| 201 |
pNames->Add(wxT("LONGBLOB")); |
| 202 |
pNames->Add(wxT("BLOB")); |
| 203 |
pNames->Add(wxT("MEDIUMTEXT")); |
| 204 |
pNames->Add(wxT("TEXT")); |
| 205 |
pNames->Add(wxT("LONGTEXT")); |
| 206 |
|
| 207 |
return pNames; |
| 208 |
} |
| 209 |
wxString MySqlDbAdapter::GetDefaultSelect(const wxString& dbName, const wxString& tableName) |
| 210 |
{ |
| 211 |
// TODO:SQL: |
| 212 |
wxString ret = wxString::Format(wxT("SELECT * FROM `%s`.`%s`"), dbName.c_str(), tableName.c_str()); |
| 213 |
return ret; |
| 214 |
} |
| 215 |
wxString MySqlDbAdapter::GetDefaultSelect(const wxString& cols, const wxString& dbName, const wxString& tableName) |
| 216 |
{ |
| 217 |
// TODO:SQL: |
| 218 |
wxString ret = wxString::Format(wxT("SELECT %s FROM `%s`.`%s`"), cols.c_str(), dbName.c_str(), tableName.c_str()); |
| 219 |
return ret; |
| 220 |
} |
| 221 |
bool MySqlDbAdapter::GetColumns(Table* pTab) |
| 222 |
{ |
| 223 |
DatabaseLayerPtr dbLayer = this->GetDatabaseLayer(wxT("")); |
| 224 |
|
| 225 |
if(!dbLayer->IsOpen()) |
| 226 |
return false; |
| 227 |
// loading columns |
| 228 |
// TODO:SQL: |
| 229 |
DatabaseResultSet* database = dbLayer->RunQueryWithResults( |
| 230 |
wxString::Format(wxT("SHOW COLUMNS IN `%s`.`%s`"), pTab->GetParentName().c_str(), pTab->GetName().c_str())); |
| 231 |
while(database->Next()) { |
| 232 |
IDbType* pType = parseTypeString(database->GetResultString(2)); |
| 233 |
if(pType) { |
| 234 |
Column* pCol = new Column(database->GetResultString(1), pTab->GetName(), pType); |
| 235 |
pTab->AddChild(pCol); |
| 236 |
} |
| 237 |
} |
| 238 |
dbLayer->CloseResultSet(database); |
| 239 |
|
| 240 |
// TODO:SQL: |
| 241 |
wxString constrSql = |
| 242 |
wxT("SELECT K.CONSTRAINT_SCHEMA, " |
| 243 |
"K.CONSTRAINT_NAME,K.TABLE_NAME,K.COLUMN_NAME,K.REFERENCED_TABLE_NAME,K.REFERENCED_COLUMN_NAME,R.UPDATE_" |
| 244 |
"RULE, R.DELETE_RULE FROM information_schema.KEY_COLUMN_USAGE K LEFT JOIN " |
| 245 |
"information_schema.REFERENTIAL_CONSTRAINTS R ON R.CONSTRAINT_NAME = K.CONSTRAINT_NAME AND " |
| 246 |
"K.CONSTRAINT_SCHEMA = R.CONSTRAINT_SCHEMA WHERE K.CONSTRAINT_SCHEMA = '%s' AND K.TABLE_NAME = '%s'"); |
| 247 |
database = dbLayer->RunQueryWithResults( |
| 248 |
wxString::Format(constrSql, pTab->GetParentName().c_str(), pTab->GetName().c_str())); |
| 249 |
while(database->Next()) { |
| 250 |
Constraint* constr = new Constraint(); |
| 251 |
constr->SetName(database->GetResultString(wxT("CONSTRAINT_NAME"))); |
| 252 |
constr->SetLocalColumn(database->GetResultString(wxT("COLUMN_NAME"))); |
| 253 |
constr->SetType(Constraint::primaryKey); |
| 254 |
if(database->GetResultString(wxT("REFERENCED_TABLE_NAME")) != wxT("")) { |
| 255 |
constr->SetType(Constraint::foreignKey); |
| 256 |
constr->SetRefTable(database->GetResultString(wxT("REFERENCED_TABLE_NAME"))); |
| 257 |
constr->SetRefCol(database->GetResultString(wxT("REFERENCED_COLUMN_NAME"))); |
| 258 |
|
| 259 |
wxString onDelete = database->GetResultString(wxT("UPDATE_RULE")); |
| 260 |
if(onDelete == wxT("RESTRICT")) |
| 261 |
constr->SetOnUpdate(Constraint::restrict); |
| 262 |
if(onDelete == wxT("CASCADE")) |
| 263 |
constr->SetOnUpdate(Constraint::cascade); |
| 264 |
if(onDelete == wxT("SET NULL")) |
| 265 |
constr->SetOnUpdate(Constraint::setNull); |
| 266 |
if(onDelete == wxT("NO ACTION")) |
| 267 |
constr->SetOnUpdate(Constraint::noAction); |
| 268 |
|
| 269 |
wxString onUpdate = database->GetResultString(wxT("DELETE_RULE")); |
| 270 |
if(onUpdate == wxT("RESTRICT")) |
| 271 |
constr->SetOnDelete(Constraint::restrict); |
| 272 |
if(onUpdate == wxT("CASCADE")) |
| 273 |
constr->SetOnDelete(Constraint::cascade); |
| 274 |
if(onUpdate == wxT("SET NULL")) |
| 275 |
constr->SetOnDelete(Constraint::setNull); |
| 276 |
if(onUpdate == wxT("NO ACTION")) |
| 277 |
constr->SetOnDelete(Constraint::noAction); |
| 278 |
} |
| 279 |
pTab->AddChild(constr); |
| 280 |
} |
| 281 |
dbLayer->CloseResultSet(database); |
| 282 |
dbLayer->Close(); |
| 283 |
return true; |
| 284 |
} |
| 285 |
|
| 286 |
IDbType* MySqlDbAdapter::parseTypeString(const wxString& typeString) |
| 287 |
{ |
| 288 |
static wxRegEx reType(wxT("([a-zA-Z]+)(\\([0-9]+\\))?")); |
| 289 |
IDbType* type(NULL); |
| 290 |
if(reType.Matches(typeString)) { |
| 291 |
wxString typeName = reType.GetMatch(typeString, 1); |
| 292 |
wxString strSize = reType.GetMatch(typeString, 2); |
| 293 |
typeName.MakeUpper(); |
| 294 |
|
| 295 |
type = this->GetDbTypeByName(typeName); |
| 296 |
if(type) { |
| 297 |
strSize.Trim().Trim(false); |
| 298 |
if(strSize.StartsWith(wxT("("))) { |
| 299 |
strSize.Remove(0, 1); |
| 300 |
} |
| 301 |
if(strSize.EndsWith(wxT(")"))) { |
| 302 |
strSize.RemoveLast(); |
| 303 |
} |
| 304 |
|
| 305 |
long size = 0; |
| 306 |
if(strSize.ToLong(&size)) { |
| 307 |
type->SetSize(size); |
| 308 |
} |
| 309 |
} |
| 310 |
} |
| 311 |
return type; |
| 312 |
} |
| 313 |
|
| 314 |
bool MySqlDbAdapter::CanConnect() { return ((m_serverName != wxT("")) && (m_userName != wxT(""))); } |
| 315 |
void MySqlDbAdapter::GetDatabases(DbConnection* dbCon) |
| 316 |
{ |
| 317 |
if(dbCon) { |
| 318 |
DatabaseLayerPtr dbLayer = this->GetDatabaseLayer(wxT("")); |
| 319 |
if(dbLayer) { |
| 320 |
if(!dbLayer->IsOpen()) |
| 321 |
return; |
| 322 |
|
| 323 |
// loading databases |
| 324 |
// TODO:SQL: |
| 325 |
DatabaseResultSet* databaze = dbLayer->RunQueryWithResults(wxT("SHOW DATABASES")); |
| 326 |
while(databaze->Next()) { |
| 327 |
dbCon->AddChild(new Database(this, databaze->GetResultString(1))); |
| 328 |
} |
| 329 |
dbLayer->CloseResultSet(databaze); |
| 330 |
dbLayer->Close(); |
| 331 |
} |
| 332 |
} |
| 333 |
return; |
| 334 |
} |
| 335 |
|
| 336 |
void MySqlDbAdapter::GetTables(Database* db, bool includeViews) |
| 337 |
{ |
| 338 |
if(db) { |
| 339 |
DatabaseLayerPtr dbLayer = this->GetDatabaseLayer(wxT("")); |
| 340 |
if(dbLayer) { |
| 341 |
if(!dbLayer->IsOpen()) |
| 342 |
return; |
| 343 |
// lading tables for database |
| 344 |
// TODO:SQL: |
| 345 |
|
| 346 |
// DatabaseResultSet *tabulky = dbLayer->RunQueryWithResults(wxString::Format(wxT("SHOW TABLES IN `%s`"), |
| 347 |
// db->getName().c_str()) ); |
| 348 |
|
| 349 |
DatabaseResultSet* tabulky = NULL; |
| 350 |
if(!includeViews) { |
| 351 |
tabulky = dbLayer->RunQueryWithResults( |
| 352 |
wxString::Format(wxT("SELECT * FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = '%s' AND " |
| 353 |
"`TABLE_TYPE` = 'BASE TABLE'"), |
| 354 |
db->GetName().c_str())); |
| 355 |
} else { |
| 356 |
tabulky = dbLayer->RunQueryWithResults( |
| 357 |
wxString::Format(wxT("SELECT * FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = '%s' AND " |
| 358 |
"(`TABLE_TYPE` = 'BASE TABLE' OR `TABLE_TYPE` = 'VIEW')"), |
| 359 |
db->GetName().c_str())); |
| 360 |
} |
| 361 |
if(tabulky) { |
| 362 |
while(tabulky->Next()) { |
| 363 |
db->AddChild(new Table(this, tabulky->GetResultString(wxT("TABLE_NAME")), db->GetName(), |
| 364 |
tabulky->GetResultString(wxT("TABLE_TYPE")).Contains(wxT("VIEW")))); |
| 365 |
} |
| 366 |
dbLayer->CloseResultSet(tabulky); |
| 367 |
} |
| 368 |
dbLayer->Close(); |
| 369 |
} |
| 370 |
} |
| 371 |
return; |
| 372 |
} |
| 373 |
wxString MySqlDbAdapter::GetCreateDatabaseSql(const wxString& dbName) |
| 374 |
{ |
| 375 |
return wxString::Format(wxT("CREATE DATABASE `%s`"), dbName.c_str()); |
| 376 |
} |
| 377 |
wxString MySqlDbAdapter::GetDropTableSql(Table* pTab) |
| 378 |
{ |
| 379 |
return wxString::Format(wxT("SET FOREIGN_KEY_CHECKS = 0;\nDROP TABLE IF EXISTS `%s`;\nSET FOREIGN_KEY_CHECKS = 1;"), |
| 380 |
pTab->GetName().c_str()); |
| 381 |
} |
| 382 |
wxString MySqlDbAdapter::GetAlterTableConstraintSql(Table* tab) |
| 383 |
{ |
| 384 |
// TODO:SQL: |
| 385 |
wxString str = wxString::Format(wxT("-- ---------- CONSTRAINTS FOR TABLE `%s` \n"), tab->GetName().c_str()); |
| 386 |
str.append(wxT("-- -------------------------------------------------------------\n")); |
| 387 |
wxString prefix = wxString::Format(wxT("ALTER TABLE `%s` "), tab->GetName().c_str()); |
| 388 |
|
| 389 |
SerializableList::compatibility_iterator node = tab->GetFirstChildNode(); |
| 390 |
while(node) { |
| 391 |
Constraint* constr = NULL; |
| 392 |
constr = wxDynamicCast(node->GetData(), Constraint); |
| 393 |
if(constr) { |
| 394 |
if(constr->GetType() == Constraint::foreignKey) { |
| 395 |
str.append(prefix + |
| 396 |
wxString::Format(wxT("ADD CONSTRAINT `%s` FOREIGN KEY (`%s`) REFERENCES `%s`(`%s`) "), |
| 397 |
constr->GetName().c_str(), constr->GetLocalColumn().c_str(), |
| 398 |
constr->GetRefTable().c_str(), constr->GetRefCol().c_str())); |
| 399 |
str.append(wxT("ON UPDATE ")); |
| 400 |
switch(constr->GetOnUpdate()) { |
| 401 |
case Constraint::restrict: |
| 402 |
str.append(wxT("RESTRICT ")); |
| 403 |
break; |
| 404 |
case Constraint::cascade: |
| 405 |
str.append(wxT("CASCADE ")); |
| 406 |
break; |
| 407 |
case Constraint::setNull: |
| 408 |
str.append(wxT("SET NULL ")); |
| 409 |
break; |
| 410 |
case Constraint::noAction: |
| 411 |
str.append(wxT("NO ACTION ")); |
| 412 |
break; |
| 413 |
} |
| 414 |
str.append(wxT("ON DELETE ")); |
| 415 |
switch(constr->GetOnDelete()) { |
| 416 |
case Constraint::restrict: |
| 417 |
str.append(wxT("RESTRICT ")); |
| 418 |
break; |
| 419 |
case Constraint::cascade: |
| 420 |
str.append(wxT("CASCADE ")); |
| 421 |
break; |
| 422 |
case Constraint::setNull: |
| 423 |
str.append(wxT("SET NULL ")); |
| 424 |
break; |
| 425 |
case Constraint::noAction: |
| 426 |
str.append(wxT("NO ACTION ")); |
| 427 |
break; |
| 428 |
} |
| 429 |
str.append(wxT("; \n")); |
| 430 |
} |
| 431 |
} // if (constr->GetType() == Constraint::primaryKey) str.append(prefix + wxString::Format(wxT("ADD CONSTRAINT |
| 432 |
// `%s` PRIMARY KEY (`%s`); \n"), constr->GetName().c_str(), constr->GetLocalColumn().c_str())); |
| 433 |
|
| 434 |
node = node->GetNext(); |
| 435 |
} |
| 436 |
str.append(wxT("-- -------------------------------------------------------------\n")); |
| 437 |
return str; |
| 438 |
} |
| 439 |
wxString MySqlDbAdapter::GetDropDatabaseSql(Database* pDb) |
| 440 |
{ |
| 441 |
return wxString::Format(wxT("DROP DATABASE `%s`"), pDb->GetName().c_str()); |
| 442 |
} |
| 443 |
wxString MySqlDbAdapter::GetUseDb(const wxString& dbName) { return wxString::Format(wxT("USE `%s`"), dbName.c_str()); } |
| 444 |
void MySqlDbAdapter::GetViews(Database* db) |
| 445 |
{ |
| 446 |
DatabaseLayerPtr dbLayer = this->GetDatabaseLayer(wxT("")); |
| 447 |
|
| 448 |
if(!dbLayer->IsOpen()) |
| 449 |
return; |
| 450 |
// loading columns |
| 451 |
// TODO:SQL: |
| 452 |
DatabaseResultSet* database = dbLayer->RunQueryWithResults(wxString::Format( |
| 453 |
wxT("SELECT * FROM `INFORMATION_SCHEMA`.`VIEWS` WHERE TABLE_SCHEMA = '%s'"), db->GetName().c_str())); |
| 454 |
while(database->Next()) { |
| 455 |
View* pView = new View(this, database->GetResultString(wxT("TABLE_NAME")), db->GetName(), |
| 456 |
database->GetResultString(wxT("VIEW_DEFINITION"))); |
| 457 |
db->AddChild(pView); |
| 458 |
} |
| 459 |
dbLayer->CloseResultSet(database); |
| 460 |
} |
| 461 |
|
| 462 |
wxString MySqlDbAdapter::GetCreateViewSql(View* view, bool dropView) |
| 463 |
{ |
| 464 |
wxString str = wxT(""); |
| 465 |
if(view) { |
| 466 |
if(dropView) { |
| 467 |
str.append(wxString::Format(wxT("DROP VIEW IF EXISTS `%s`;\n"), view->GetName().c_str())); |
| 468 |
} |
| 469 |
str.append( |
| 470 |
wxString::Format(wxT("CREATE VIEW `%s` AS %s ;\n"), view->GetName().c_str(), view->GetSelect().c_str())); |
| 471 |
} |
| 472 |
str.append(wxT("-- -------------------------------------------------------------\n")); |
| 473 |
return str; |
| 474 |
} |
| 475 |
void MySqlDbAdapter::ConvertTable(Table* pTab) |
| 476 |
{ |
| 477 |
SerializableList::compatibility_iterator node = pTab->GetFirstChildNode(); |
| 478 |
while(node) { |
| 479 |
if(node->GetData()->IsKindOf(CLASSINFO(Column))) { |
| 480 |
Column* col = (Column*)node->GetData(); |
| 481 |
col->SetType(ConvertType(col->GetType())); |
| 482 |
} |
| 483 |
node = node->GetNext(); |
| 484 |
} |
| 485 |
} |
| 486 |
|
| 487 |
IDbType* MySqlDbAdapter::ConvertType(IDbType* pType) |
| 488 |
{ |
| 489 |
IDbType* newType = wxDynamicCast(pType, MySqlType); |
| 490 |
if(!newType) { |
| 491 |
newType = GetDbTypeByUniversalName(pType->GetUniversalType()); |
| 492 |
delete pType; |
| 493 |
} |
| 494 |
return newType; |
| 495 |
} |
| 496 |
|
| 497 |
IDbType* MySqlDbAdapter::GetDbTypeByUniversalName(IDbType::UNIVERSAL_TYPE type) |
| 498 |
{ |
| 499 |
IDbType* newType = NULL; |
| 500 |
switch(type) { |
| 501 |
case IDbType::dbtTYPE_INT: |
| 502 |
newType = GetDbTypeByName(wxT("INT")); |
| 503 |
break; |
| 504 |
case IDbType::dbtTYPE_BOOLEAN: |
| 505 |
newType = GetDbTypeByName(wxT("BOOL")); |
| 506 |
break; |
| 507 |
case IDbType::dbtTYPE_DATE_TIME: |
| 508 |
newType = GetDbTypeByName(wxT("DATETIME")); |
| 509 |
break; |
| 510 |
case IDbType::dbtTYPE_DECIMAL: |
| 511 |
newType = GetDbTypeByName(wxT("DECIMAL")); |
| 512 |
break; |
| 513 |
case IDbType::dbtTYPE_FLOAT: |
| 514 |
newType = GetDbTypeByName(wxT("FLOAT")); |
| 515 |
break; |
| 516 |
case IDbType::dbtTYPE_TEXT: |
| 517 |
newType = GetDbTypeByName(wxT("TEXT")); |
| 518 |
break; |
| 519 |
case IDbType::dbtTYPE_OTHER: |
| 520 |
newType = GetDbTypeByName(wxT("BLOB")); |
| 521 |
break; |
| 522 |
} |
| 523 |
return newType; |
| 524 |
} |
| 525 |
IDbAdapter* MySqlDbAdapter::Clone() { return new MySqlDbAdapter(m_serverName, m_userName, m_password); } |
| 526 |
|
| 527 |
wxString MySqlDbAdapter::GetDropViewSql(View* pView) |
| 528 |
{ |
| 529 |
return wxString::Format(wxT("DROP VIEW IF EXISTS `%s`;"), pView->GetName().c_str()); |
| 530 |
} |