| 1 |
////////////////////////////////////////////////////////////////////////////// |
| 2 |
////////////////////////////////////////////////////////////////////////////// |
| 3 |
// |
| 4 |
// copyright : (C) 2014 Eran Ifrah |
| 5 |
// file name : PostgreSqlDbAdapter.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 "PostgreSqlDbAdapter.h" |
| 27 |
#include "constraint.h" |
| 28 |
#include "database.h" |
| 29 |
#include "dbconnection.h" |
| 30 |
#include "table.h" |
| 31 |
#include "view.h" |
| 32 |
|
| 33 |
PostgreSqlDbAdapter::PostgreSqlDbAdapter() |
| 34 |
{ |
| 35 |
this->m_serverName = wxT(""); |
| 36 |
this->m_userName = wxT(""); |
| 37 |
this->m_password = wxT(""); |
| 38 |
this->m_adapterType = atPOSTGRES; |
| 39 |
this->m_pDbLayer = NULL; |
| 40 |
} |
| 41 |
PostgreSqlDbAdapter::PostgreSqlDbAdapter(const wxString& serverName, const int port, const wxString& defaultDb, |
| 42 |
const wxString& userName, const wxString& password) |
| 43 |
{ |
| 44 |
this->m_serverName = serverName; |
| 45 |
this->m_port = port; |
| 46 |
this->m_userName = userName; |
| 47 |
this->m_password = password; |
| 48 |
this->m_defaultDb = defaultDb; |
| 49 |
this->m_adapterType = atPOSTGRES; |
| 50 |
this->m_pDbLayer = NULL; |
| 51 |
} |
| 52 |
|
| 53 |
PostgreSqlDbAdapter::~PostgreSqlDbAdapter() {} |
| 54 |
|
| 55 |
void PostgreSqlDbAdapter::CloseConnection() |
| 56 |
{ |
| 57 |
if(this->m_pDbLayer) |
| 58 |
this->m_pDbLayer->Close(); |
| 59 |
} |
| 60 |
|
| 61 |
DatabaseLayerPtr PostgreSqlDbAdapter::GetDatabaseLayer(const wxString& dbName) |
| 62 |
{ |
| 63 |
DatabaseLayer* dbLayer = NULL; |
| 64 |
|
| 65 |
#ifdef DBL_USE_POSTGRES |
| 66 |
if(!CanConnect()) |
| 67 |
return new PostgresDatabaseLayer(); |
| 68 |
if(m_port == 0) |
| 69 |
m_port = 5432; |
| 70 |
if(!dbName.IsEmpty()) |
| 71 |
dbLayer = |
| 72 |
new PostgresDatabaseLayer(this->m_serverName, this->m_port, dbName, this->m_userName, this->m_password); |
| 73 |
else |
| 74 |
dbLayer = new PostgresDatabaseLayer(this->m_serverName, this->m_port, this->m_defaultDb, this->m_userName, |
| 75 |
this->m_password); |
| 76 |
#endif |
| 77 |
this->m_pDbLayer = dbLayer; |
| 78 |
|
| 79 |
return dbLayer; |
| 80 |
} |
| 81 |
|
| 82 |
bool PostgreSqlDbAdapter::IsConnected() |
| 83 |
{ |
| 84 |
if(this->m_pDbLayer) |
| 85 |
return this->m_pDbLayer->IsOpen(); |
| 86 |
else |
| 87 |
return false; |
| 88 |
} |
| 89 |
|
| 90 |
wxString PostgreSqlDbAdapter::GetCreateTableSql(Table* tab, bool dropTable) |
| 91 |
{ |
| 92 |
// TODO:SQL: |
| 93 |
wxString str = wxT(""); |
| 94 |
if(dropTable) |
| 95 |
str = wxString::Format(wxT("DROP TABLE IF EXISTS %s CASCADE; \n"), tab->GetName().c_str()); |
| 96 |
str.append(wxString::Format(wxT("CREATE TABLE %s (\n"), tab->GetName().c_str())); |
| 97 |
|
| 98 |
SerializableList::compatibility_iterator node = tab->GetFirstChildNode(); |
| 99 |
while(node) { |
| 100 |
Column* col = NULL; |
| 101 |
if(node->GetData()->IsKindOf(CLASSINFO(Column))) |
| 102 |
col = (Column*)node->GetData(); |
| 103 |
if(col) |
| 104 |
str.append(wxString::Format(wxT("\t%s %s"), col->GetName().c_str(), col->GetType()->ReturnSql().c_str())); |
| 105 |
|
| 106 |
Constraint* constr = wxDynamicCast(node->GetData(), Constraint); |
| 107 |
if(constr) { |
| 108 |
if(constr->GetType() == Constraint::primaryKey) |
| 109 |
str.append(wxString::Format(wxT("\tCONSTRAINT %s PRIMARY KEY (%s) \n"), constr->GetName().c_str(), |
| 110 |
constr->GetLocalColumn().c_str())); |
| 111 |
} |
| 112 |
|
| 113 |
node = node->GetNext(); |
| 114 |
if(node) { |
| 115 |
if(wxDynamicCast(node->GetData(), Column)) |
| 116 |
str.append(wxT(",\n ")); |
| 117 |
else if((constr = wxDynamicCast(node->GetData(), Constraint))) { |
| 118 |
if(constr->GetType() == Constraint::primaryKey) |
| 119 |
str.append(wxT(",\n ")); |
| 120 |
} |
| 121 |
} |
| 122 |
// else str.append(wxT("\n ")) ; |
| 123 |
} |
| 124 |
|
| 125 |
/* Column* col = tab->GetFristColumn(); |
| 126 |
while (col) { |
| 127 |
str.append(wxString::Format(wxT("\t`%s` %s"),col->getName().c_str(), col->getPType()->ReturnSql().c_str())); |
| 128 |
col = wxDynamicCast(col->GetSibbling(),Column); |
| 129 |
if (col) str.append(wxT(",\n ")) ; |
| 130 |
else str.append(wxT("\n ")) ; |
| 131 |
}*/ |
| 132 |
|
| 133 |
str.append(wxT("\n);\n")); |
| 134 |
str.append(wxT("-- -------------------------------------------------------------\n")); |
| 135 |
return str; |
| 136 |
} |
| 137 |
|
| 138 |
IDbType* PostgreSqlDbAdapter::GetDbTypeByName(const wxString& typeName) |
| 139 |
{ |
| 140 |
IDbType* type = NULL; |
| 141 |
// numeric types |
| 142 |
if(typeName == wxT("SMALLINT")) { |
| 143 |
type = new PostgreSqlType(wxT("SMALLINT"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_INT); |
| 144 |
} else if(typeName == wxT("INTEGER")) { |
| 145 |
type = new PostgreSqlType(wxT("INTEGER"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_INT); |
| 146 |
} else if(typeName == wxT("BIGINT")) { |
| 147 |
type = new PostgreSqlType(wxT("BIGINT"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_INT); |
| 148 |
} else if(typeName == wxT("DECIMAL")) { |
| 149 |
type = new PostgreSqlType(wxT("DECIMAL"), IDbType::dbtNOT_NULL | IDbType::dbtSIZE | IDbType::dbtSIZE_TWO, |
| 150 |
IDbType::dbtTYPE_DECIMAL); |
| 151 |
} else if(typeName == wxT("NUMERIC")) { |
| 152 |
type = new PostgreSqlType(wxT("NUMERIC"), IDbType::dbtNOT_NULL | IDbType::dbtSIZE | IDbType::dbtSIZE_TWO, |
| 153 |
IDbType::dbtTYPE_DECIMAL); |
| 154 |
} else if(typeName == wxT("REAL")) { |
| 155 |
type = new PostgreSqlType(wxT("REAL"), IDbType::dbtNOT_NULL | IDbType::dbtSIZE | IDbType::dbtSIZE_TWO, |
| 156 |
IDbType::dbtTYPE_FLOAT); |
| 157 |
} else if(typeName == wxT("DOUBLE PRECISION")) { |
| 158 |
type = |
| 159 |
new PostgreSqlType(wxT("DOUBLE PRECISION"), IDbType::dbtNOT_NULL | IDbType::dbtSIZE | IDbType::dbtSIZE_TWO, |
| 160 |
IDbType::dbtTYPE_FLOAT); |
| 161 |
} else if(typeName == wxT("SERIAL")) { |
| 162 |
type = new PostgreSqlType(wxT("SERIAL"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_INT); |
| 163 |
} else if(typeName == wxT("BIGSERIAL")) { |
| 164 |
type = new PostgreSqlType(wxT("BIGSERIAL"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_INT); |
| 165 |
|
| 166 |
// Monetary types |
| 167 |
} else if(typeName == wxT("CHARACTER VARYING")) { |
| 168 |
type = new PostgreSqlType(wxT("CHARACTER VARYING"), IDbType::dbtNOT_NULL | IDbType::dbtSIZE, |
| 169 |
IDbType::dbtTYPE_TEXT); |
| 170 |
// Character types |
| 171 |
} else if(typeName == wxT("VARCHAR")) { |
| 172 |
type = new PostgreSqlType(wxT("VARCHAR"), IDbType::dbtNOT_NULL | IDbType::dbtSIZE, IDbType::dbtTYPE_TEXT); |
| 173 |
} else if(typeName == wxT("CHARACTER")) { |
| 174 |
type = new PostgreSqlType(wxT("CHARACTER"), IDbType::dbtNOT_NULL | IDbType::dbtSIZE, IDbType::dbtTYPE_TEXT); |
| 175 |
} else if(typeName == wxT("CHAR")) { |
| 176 |
type = new PostgreSqlType(wxT("CHAR"), IDbType::dbtNOT_NULL | IDbType::dbtSIZE, IDbType::dbtTYPE_TEXT); |
| 177 |
} else if(typeName == wxT("TEXT")) { |
| 178 |
type = new PostgreSqlType(wxT("TEXT"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_TEXT); |
| 179 |
|
| 180 |
// Binary types |
| 181 |
} else if(typeName == wxT("BYTEA")) { |
| 182 |
type = new PostgreSqlType(wxT("BYTEA"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_OTHER); |
| 183 |
|
| 184 |
// Date/Time types |
| 185 |
} else if(typeName == wxT("TIMESTAMP")) { |
| 186 |
type = |
| 187 |
new PostgreSqlType(wxT("TIMESTAMP"), IDbType::dbtNOT_NULL | IDbType::dbtSIZE, IDbType::dbtTYPE_DATE_TIME); |
| 188 |
} else if(typeName == wxT("DATE")) { |
| 189 |
type = new PostgreSqlType(wxT("DATE"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_DATE_TIME); |
| 190 |
} else if(typeName == wxT("TIME")) { |
| 191 |
type = new PostgreSqlType(wxT("TIME"), IDbType::dbtNOT_NULL | IDbType::dbtSIZE, IDbType::dbtTYPE_DATE_TIME); |
| 192 |
} else if(typeName == wxT("INTERVAL")) { |
| 193 |
type = new PostgreSqlType(wxT("INTERVAL"), IDbType::dbtNOT_NULL | IDbType::dbtSIZE, IDbType::dbtTYPE_DATE_TIME); |
| 194 |
|
| 195 |
// Boolean types |
| 196 |
} else if(typeName == wxT("BOOLEAN")) { |
| 197 |
type = new PostgreSqlType(wxT("BOOLEAN"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_BOOLEAN); |
| 198 |
|
| 199 |
// Geometric types |
| 200 |
} else if(typeName == wxT("POINT")) { |
| 201 |
type = new PostgreSqlType(wxT("POINT"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_OTHER); |
| 202 |
} else if(typeName == wxT("LINE")) { |
| 203 |
type = new PostgreSqlType(wxT("LINE"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_OTHER); |
| 204 |
} else if(typeName == wxT("LSEG")) { |
| 205 |
type = new PostgreSqlType(wxT("LSEG"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_OTHER); |
| 206 |
} else if(typeName == wxT("BOX")) { |
| 207 |
type = new PostgreSqlType(wxT("BOX"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_OTHER); |
| 208 |
} else if(typeName == wxT("PATH")) { |
| 209 |
type = new PostgreSqlType(wxT("PATH"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_OTHER); |
| 210 |
} else if(typeName == wxT("POLYGON")) { |
| 211 |
type = new PostgreSqlType(wxT("POLYGON"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_OTHER); |
| 212 |
} else if(typeName == wxT("CIRCLE")) { |
| 213 |
type = new PostgreSqlType(wxT("CIRCLE"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_OTHER); |
| 214 |
|
| 215 |
// Network address types |
| 216 |
} else if(typeName == wxT("CIDR")) { |
| 217 |
type = new PostgreSqlType(wxT("CIDR"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_OTHER); |
| 218 |
} else if(typeName == wxT("INET")) { |
| 219 |
type = new PostgreSqlType(wxT("INET"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_OTHER); |
| 220 |
} else if(typeName == wxT("MACADDR")) { |
| 221 |
type = new PostgreSqlType(wxT("MACADDR"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_OTHER); |
| 222 |
|
| 223 |
// Bit String types |
| 224 |
} else if(typeName == wxT("BIT")) { |
| 225 |
type = new PostgreSqlType(wxT("BIT"), IDbType::dbtNOT_NULL | IDbType::dbtSIZE, IDbType::dbtTYPE_OTHER); |
| 226 |
} else if(typeName == wxT("BIT VARYING")) { |
| 227 |
type = new PostgreSqlType(wxT("BIT VARYING"), IDbType::dbtNOT_NULL | IDbType::dbtSIZE, IDbType::dbtTYPE_OTHER); |
| 228 |
|
| 229 |
// UUID type |
| 230 |
} else if(typeName == wxT("UUID")) { |
| 231 |
type = new PostgreSqlType(wxT("UUID"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_OTHER); |
| 232 |
|
| 233 |
// XML type |
| 234 |
} else if(typeName == wxT("XML")) { |
| 235 |
type = new PostgreSqlType(wxT("XML"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_OTHER); |
| 236 |
|
| 237 |
// Other types |
| 238 |
} else if(typeName == wxT("OID")) { |
| 239 |
type = new PostgreSqlType(wxT("OID"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_OTHER); |
| 240 |
} else if(typeName == wxT("XID")) { |
| 241 |
type = new PostgreSqlType(wxT("XID"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_OTHER); |
| 242 |
} else if(typeName == wxT("ARRAY")) { |
| 243 |
type = new PostgreSqlType(wxT("ARRAY"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_OTHER); |
| 244 |
} else if(typeName == wxT("REGPROX")) { |
| 245 |
type = new PostgreSqlType(wxT("REGPROX"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_OTHER); |
| 246 |
} |
| 247 |
|
| 248 |
wxASSERT(type); |
| 249 |
return type; |
| 250 |
} |
| 251 |
|
| 252 |
wxArrayString* PostgreSqlDbAdapter::GetDbTypes() |
| 253 |
{ |
| 254 |
wxArrayString* pNames = new wxArrayString(); |
| 255 |
|
| 256 |
pNames->Add(wxT("SMALLINT")); |
| 257 |
pNames->Add(wxT("INTEGER")); |
| 258 |
pNames->Add(wxT("BIGINT")); |
| 259 |
pNames->Add(wxT("DECIMAL")); |
| 260 |
pNames->Add(wxT("NUMERIC")); |
| 261 |
pNames->Add(wxT("REAL")); |
| 262 |
pNames->Add(wxT("DOUBLE PRECISION")); |
| 263 |
pNames->Add(wxT("SERIAL")); |
| 264 |
pNames->Add(wxT("BIGSERIAL")); |
| 265 |
|
| 266 |
// Monetary types |
| 267 |
pNames->Add(wxT("CHARACTER VARYING")); |
| 268 |
// Character types |
| 269 |
pNames->Add(wxT("VARCHAR")); |
| 270 |
pNames->Add(wxT("CHARACTER")); |
| 271 |
pNames->Add(wxT("CHAR")); |
| 272 |
pNames->Add(wxT("TEXT")); |
| 273 |
|
| 274 |
// Binary types |
| 275 |
pNames->Add(wxT("BYTEA")); |
| 276 |
|
| 277 |
// Date/Time types |
| 278 |
pNames->Add(wxT("TIMESTAMP")); |
| 279 |
pNames->Add(wxT("DATE")); |
| 280 |
pNames->Add(wxT("TIME")); |
| 281 |
pNames->Add(wxT("INTERVAL")); |
| 282 |
|
| 283 |
// Boolean types |
| 284 |
pNames->Add(wxT("BOOLEAN")); |
| 285 |
|
| 286 |
// Geometric types |
| 287 |
pNames->Add(wxT("POINT")); |
| 288 |
pNames->Add(wxT("LINE")); |
| 289 |
pNames->Add(wxT("LSEG")); |
| 290 |
pNames->Add(wxT("BOX")); |
| 291 |
pNames->Add(wxT("PATH")); |
| 292 |
pNames->Add(wxT("POLYGON")); |
| 293 |
pNames->Add(wxT("CIRCLE")); |
| 294 |
|
| 295 |
// Network address types |
| 296 |
pNames->Add(wxT("CIDR")); |
| 297 |
pNames->Add(wxT("INET")); |
| 298 |
pNames->Add(wxT("MACADDR")); |
| 299 |
|
| 300 |
// Bit String types |
| 301 |
pNames->Add(wxT("BIT")); |
| 302 |
pNames->Add(wxT("BIT VARYING")); |
| 303 |
|
| 304 |
// UUID type |
| 305 |
pNames->Add(wxT("UUID")); |
| 306 |
|
| 307 |
// XML type |
| 308 |
pNames->Add(wxT("XML")); |
| 309 |
|
| 310 |
// OTHER TYPES |
| 311 |
pNames->Add(wxT("OID")); |
| 312 |
pNames->Add(wxT("XID")); |
| 313 |
pNames->Add(wxT("ARRAY")); |
| 314 |
pNames->Add(wxT("REGPROX")); |
| 315 |
|
| 316 |
return pNames; |
| 317 |
} |
| 318 |
wxString PostgreSqlDbAdapter::GetDefaultSelect(const wxString& dbName, const wxString& tableName) |
| 319 |
{ |
| 320 |
// TODO:SQL: |
| 321 |
wxString ret = wxString::Format(wxT("SELECT * FROM %s"), tableName.c_str()); |
| 322 |
return ret; |
| 323 |
} |
| 324 |
wxString PostgreSqlDbAdapter::GetDefaultSelect(const wxString& cols, const wxString& dbName, const wxString& tableName) |
| 325 |
{ |
| 326 |
// TODO:SQL: |
| 327 |
wxString ret = wxString::Format(wxT("SELECT %s FROM %s"), cols.c_str(), tableName.c_str()); |
| 328 |
return ret; |
| 329 |
} |
| 330 |
|
| 331 |
bool PostgreSqlDbAdapter::GetColumns(Table* pTab) |
| 332 |
{ |
| 333 |
if(pTab) { |
| 334 |
// SetDatabase(pTab->GetParentName()); |
| 335 |
DatabaseLayerPtr dbLayer = this->GetDatabaseLayer(pTab->GetParentName()); |
| 336 |
|
| 337 |
if(!dbLayer->IsOpen()) { |
| 338 |
return false; |
| 339 |
} |
| 340 |
// loading columns |
| 341 |
// TODO:SQL: |
| 342 |
// DatabaseResultSet *database = dbLayer->RunQueryWithResults(wxString::Format(wxT("SHOW COLUMNS IN |
| 343 |
// `%s`.`%s`"),pTab->getParentName().c_str(),pTab->getName().c_str())); |
| 344 |
DatabaseResultSet* database = dbLayer->RunQueryWithResults(wxString::Format( |
| 345 |
wxT("SELECT * FROM information_schema.columns WHERE table_name = '%s'"), pTab->GetName().c_str())); |
| 346 |
|
| 347 |
while(database->Next()) { |
| 348 |
IDbType* pType = parseTypeString(database->GetResultString(wxT("data_type"))); |
| 349 |
if(pType) { |
| 350 |
pType->SetSize(database->GetResultInt(wxT("numeric_precision"))); |
| 351 |
pType->SetSize2(database->GetResultInt(wxT("numeric_precision_radix"))); |
| 352 |
pType->SetNotNull(database->GetResultString(wxT("is_nullable")) == wxT("NO")); |
| 353 |
Column* pCol = new Column(database->GetResultString(wxT("column_name")), pTab->GetName(), pType); |
| 354 |
pTab->AddChild(pCol); |
| 355 |
} |
| 356 |
} |
| 357 |
dbLayer->CloseResultSet(database); |
| 358 |
|
| 359 |
// wxT("SELECT tc.constraint_name, tc.constraint_type, tc.table_name, kcu.column_name, tc.is_deferrable, |
| 360 |
// tc.initially_deferred, rc.match_option AS match_type, rc.update_rule AS on_update, rc.delete_rule AS |
| 361 |
// on_delete, ccu.table_name AS references_table, ccu.column_name AS references_field FROM |
| 362 |
// information_schema.table_constraints tc LEFT JOIN information_schema.key_column_usage kcu ON |
| 363 |
// tc.constraint_catalog = kcu.constraint_catalog AND tc.constraint_schema = kcu.constraint_schema AND |
| 364 |
// tc.constraint_name = kcu.constraint_name LEFT JOIN information_schema.referential_constraints rc ON |
| 365 |
// tc.constraint_catalog = rc.constraint_catalog AND tc.constraint_schema = rc.constraint_schema AND |
| 366 |
// tc.constraint_name = rc.constraint_name LEFT JOIN information_schema.constraint_column_usage ccu ON |
| 367 |
// rc.unique_constraint_catalog = ccu.constraint_catalog AND rc.unique_constraint_schema = ccu.constraint_schema |
| 368 |
// AND rc.unique_constraint_name = ccu.constraint_name WHERE tc.table_name = '%s'"); |
| 369 |
|
| 370 |
// TODO:SQL: |
| 371 |
wxString constrSql = |
| 372 |
wxT("SELECT tc.constraint_name, tc.constraint_type, tc.table_name, kcu.column_name, tc.is_deferrable, " |
| 373 |
"tc.initially_deferred, rc.match_option AS match_type, rc.update_rule AS on_update, rc.delete_rule AS " |
| 374 |
"on_delete, ccu.table_name AS references_table, ccu.column_name AS references_field FROM " |
| 375 |
"information_schema.table_constraints tc LEFT JOIN information_schema.key_column_usage kcu ON " |
| 376 |
"tc.constraint_catalog = kcu.constraint_catalog AND tc.constraint_schema = kcu.constraint_schema AND " |
| 377 |
"tc.constraint_name = kcu.constraint_name LEFT JOIN information_schema.referential_constraints rc ON " |
| 378 |
"tc.constraint_catalog = rc.constraint_catalog AND tc.constraint_schema = rc.constraint_schema AND " |
| 379 |
"tc.constraint_name = rc.constraint_name LEFT JOIN information_schema.constraint_column_usage ccu ON " |
| 380 |
"rc.unique_constraint_catalog = ccu.constraint_catalog AND rc.unique_constraint_schema = " |
| 381 |
"ccu.constraint_schema AND rc.unique_constraint_name = ccu.constraint_name WHERE tc.table_name = '%s'"); |
| 382 |
|
| 383 |
database = dbLayer->RunQueryWithResults(wxString::Format(constrSql, pTab->GetName().c_str())); |
| 384 |
while(database->Next()) { |
| 385 |
if((database->GetResultString(wxT("constraint_type")) == wxT("PRIMARY KEY")) || |
| 386 |
(database->GetResultString(wxT("constraint_type")) == wxT("FOREIGN KEY"))) { |
| 387 |
Constraint* constr = new Constraint(); |
| 388 |
constr->SetName(database->GetResultString(wxT("constraint_name"))); |
| 389 |
constr->SetLocalColumn(database->GetResultString(wxT("column_name"))); |
| 390 |
constr->SetType(Constraint::primaryKey); |
| 391 |
if(database->GetResultString(wxT("references_table")) != wxT("")) { |
| 392 |
constr->SetType(Constraint::foreignKey); |
| 393 |
constr->SetRefTable(database->GetResultString(wxT("references_table"))); |
| 394 |
constr->SetRefCol(database->GetResultString(wxT("references_field"))); |
| 395 |
|
| 396 |
wxString onDelete = database->GetResultString(wxT("on_update")); |
| 397 |
if(onDelete == wxT("RESTRICT")) |
| 398 |
constr->SetOnUpdate(Constraint::restrict); |
| 399 |
if(onDelete == wxT("CASCADE")) |
| 400 |
constr->SetOnUpdate(Constraint::cascade); |
| 401 |
if(onDelete == wxT("SET NULL")) |
| 402 |
constr->SetOnUpdate(Constraint::setNull); |
| 403 |
if(onDelete == wxT("NO ACTION")) |
| 404 |
constr->SetOnUpdate(Constraint::noAction); |
| 405 |
|
| 406 |
wxString onUpdate = database->GetResultString(wxT("on_delete")); |
| 407 |
if(onUpdate == wxT("RESTRICT")) |
| 408 |
constr->SetOnDelete(Constraint::restrict); |
| 409 |
if(onUpdate == wxT("CASCADE")) |
| 410 |
constr->SetOnDelete(Constraint::cascade); |
| 411 |
if(onUpdate == wxT("SET NULL")) |
| 412 |
constr->SetOnDelete(Constraint::setNull); |
| 413 |
if(onUpdate == wxT("NO ACTION")) |
| 414 |
constr->SetOnDelete(Constraint::noAction); |
| 415 |
} |
| 416 |
pTab->AddChild(constr); |
| 417 |
} |
| 418 |
} |
| 419 |
dbLayer->CloseResultSet(database); |
| 420 |
dbLayer->Close(); |
| 421 |
} |
| 422 |
return true; |
| 423 |
} |
| 424 |
IDbType* PostgreSqlDbAdapter::parseTypeString(const wxString& typeString) |
| 425 |
{ |
| 426 |
wxString text = typeString.Upper().Trim(); |
| 427 |
IDbType* type = this->GetDbTypeByName(text); |
| 428 |
return type; |
| 429 |
} |
| 430 |
|
| 431 |
bool PostgreSqlDbAdapter::CanConnect() { return ((m_serverName != wxT("")) && (m_userName != wxT(""))); } |
| 432 |
void PostgreSqlDbAdapter::GetDatabases(DbConnection* dbCon) |
| 433 |
{ |
| 434 |
if(dbCon) { |
| 435 |
DatabaseLayerPtr dbLayer = this->GetDatabaseLayer(wxT("")); |
| 436 |
if(dbLayer) { |
| 437 |
if(!dbLayer->IsOpen()) |
| 438 |
return; |
| 439 |
|
| 440 |
// loading databases |
| 441 |
// TODO:SQL: |
| 442 |
DatabaseResultSet* databaze = |
| 443 |
dbLayer->RunQueryWithResults(wxT("SELECT datname FROM pg_database WHERE datallowconn = 't' ")); |
| 444 |
while(databaze->Next()) { |
| 445 |
dbCon->AddChild(new Database(this, databaze->GetResultString(1))); |
| 446 |
} |
| 447 |
dbLayer->CloseResultSet(databaze); |
| 448 |
dbLayer->Close(); |
| 449 |
} |
| 450 |
} |
| 451 |
return; |
| 452 |
} |
| 453 |
|
| 454 |
void PostgreSqlDbAdapter::GetTables(Database* db, bool includeViews) |
| 455 |
{ |
| 456 |
if(db) { |
| 457 |
// SetDatabase(db->GetName()); |
| 458 |
DatabaseLayerPtr dbLayer = this->GetDatabaseLayer(db->GetName()); |
| 459 |
if(dbLayer) { |
| 460 |
if(!dbLayer->IsOpen()) |
| 461 |
return; |
| 462 |
// lading tables for database |
| 463 |
// TODO:SQL: |
| 464 |
|
| 465 |
// DatabaseResultSet *tabulky = dbLayer->RunQueryWithResults(wxString::Format(wxT("SHOW TABLES IN `%s`"), |
| 466 |
// db->getName().c_str()) ); DatabaseResultSet *tabulky = |
| 467 |
// dbLayer->RunQueryWithResults(wxString::Format(wxT("SELECT * FROM `INFORMATION_SCHEMA`.`TABLES` WHERE |
| 468 |
// `TABLE_SCHEMA` = '%s' AND `TABLE_TYPE` = 'BASE TABLE'"), db->getName().c_str()) ); |
| 469 |
DatabaseResultSet* tabulky = NULL; |
| 470 |
if(includeViews) { |
| 471 |
tabulky = dbLayer->RunQueryWithResults( |
| 472 |
wxString::Format(wxT("SELECT * FROM information_schema.tables WHERE table_schema = 'public' AND " |
| 473 |
"(table_type = 'BASE TABLE' OR table_type = 'VIEW')"))); |
| 474 |
} else { |
| 475 |
tabulky = dbLayer->RunQueryWithResults( |
| 476 |
wxString::Format(wxT("SELECT * FROM information_schema.tables WHERE table_schema = 'public' AND " |
| 477 |
"table_type = 'BASE TABLE'"))); |
| 478 |
} |
| 479 |
while(tabulky->Next()) { |
| 480 |
db->AddChild(new Table(this, tabulky->GetResultString(wxT("TABLE_NAME")), db->GetName(), |
| 481 |
tabulky->GetResultString(wxT("TABLE_TYPE")).Contains(wxT("VIEW")))); |
| 482 |
} |
| 483 |
dbLayer->CloseResultSet(tabulky); |
| 484 |
dbLayer->Close(); |
| 485 |
} |
| 486 |
} |
| 487 |
return; |
| 488 |
} |
| 489 |
wxString PostgreSqlDbAdapter::GetCreateDatabaseSql(const wxString& dbName) |
| 490 |
{ |
| 491 |
return wxString::Format(wxT("CREATE DATABASE %s"), dbName.c_str()); |
| 492 |
} |
| 493 |
wxString PostgreSqlDbAdapter::GetDropTableSql(Table* pTab) |
| 494 |
{ |
| 495 |
return wxString::Format(wxT("DROP TABLE IF EXISTS %s;"), pTab->GetName().c_str()); |
| 496 |
} |
| 497 |
wxString PostgreSqlDbAdapter::GetAlterTableConstraintSql(Table* tab) |
| 498 |
{ |
| 499 |
// TODO:SQL: |
| 500 |
wxString str = wxString::Format(wxT("-- ---------- CONSTRAINTS FOR TABLE %s \n"), tab->GetName().c_str()); |
| 501 |
str.append(wxT("-- -------------------------------------------------------------\n")); |
| 502 |
wxString prefix = wxString::Format(wxT("ALTER TABLE %s "), tab->GetName().c_str()); |
| 503 |
|
| 504 |
SerializableList::compatibility_iterator node = tab->GetFirstChildNode(); |
| 505 |
while(node) { |
| 506 |
Constraint* constr = NULL; |
| 507 |
constr = wxDynamicCast(node->GetData(), Constraint); |
| 508 |
if(constr) { |
| 509 |
if(constr->GetType() == Constraint::foreignKey) { |
| 510 |
str.append(prefix + wxString::Format(wxT("ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s(%s) "), |
| 511 |
constr->GetName().c_str(), constr->GetLocalColumn().c_str(), |
| 512 |
constr->GetRefTable().c_str(), constr->GetRefCol().c_str())); |
| 513 |
str.append(wxT("ON UPDATE ")); |
| 514 |
switch(constr->GetOnUpdate()) { |
| 515 |
case Constraint::restrict: |
| 516 |
str.append(wxT("RESTRICT ")); |
| 517 |
break; |
| 518 |
case Constraint::cascade: |
| 519 |
str.append(wxT("CASCADE ")); |
| 520 |
break; |
| 521 |
case Constraint::setNull: |
| 522 |
str.append(wxT("SET NULL ")); |
| 523 |
break; |
| 524 |
case Constraint::noAction: |
| 525 |
str.append(wxT("NO ACTION ")); |
| 526 |
break; |
| 527 |
} |
| 528 |
str.append(wxT("ON DELETE ")); |
| 529 |
switch(constr->GetOnDelete()) { |
| 530 |
case Constraint::restrict: |
| 531 |
str.append(wxT("RESTRICT ")); |
| 532 |
break; |
| 533 |
case Constraint::cascade: |
| 534 |
str.append(wxT("CASCADE ")); |
| 535 |
break; |
| 536 |
case Constraint::setNull: |
| 537 |
str.append(wxT("SET NULL ")); |
| 538 |
break; |
| 539 |
case Constraint::noAction: |
| 540 |
str.append(wxT("NO ACTION ")); |
| 541 |
break; |
| 542 |
} |
| 543 |
str.append(wxT("; \n")); |
| 544 |
} |
| 545 |
} // if (constr->GetType() == Constraint::primaryKey) str.append(prefix + wxString::Format(wxT("ADD CONSTRAINT |
| 546 |
// `%s` PRIMARY KEY (`%s`); \n"), constr->GetName().c_str(), constr->GetLocalColumn().c_str())); |
| 547 |
|
| 548 |
node = node->GetNext(); |
| 549 |
} |
| 550 |
str.append(wxT("-- -------------------------------------------------------------\n")); |
| 551 |
return str; |
| 552 |
} |
| 553 |
wxString PostgreSqlDbAdapter::GetDropDatabaseSql(Database* pDb) |
| 554 |
{ |
| 555 |
return wxString::Format(wxT("DROP DATABASE %s"), pDb->GetName().c_str()); |
| 556 |
} |
| 557 |
wxString PostgreSqlDbAdapter::GetUseDb(const wxString& dbName) { return wxT(""); } |
| 558 |
void PostgreSqlDbAdapter::GetViews(Database* db) |
| 559 |
{ |
| 560 |
if(db) { |
| 561 |
// SetDatabase(db->GetName()); |
| 562 |
DatabaseLayerPtr dbLayer = this->GetDatabaseLayer(db->GetName()); |
| 563 |
if(dbLayer) { |
| 564 |
if(!dbLayer->IsOpen()) |
| 565 |
return; |
| 566 |
// lading tables for database |
| 567 |
// TODO:SQL: |
| 568 |
|
| 569 |
// DatabaseResultSet *tabulky = dbLayer->RunQueryWithResults(wxString::Format(wxT("SHOW TABLES IN `%s`"), |
| 570 |
// db->getName().c_str()) ); DatabaseResultSet *tabulky = |
| 571 |
// dbLayer->RunQueryWithResults(wxString::Format(wxT("SELECT * FROM `INFORMATION_SCHEMA`.`TABLES` WHERE |
| 572 |
// `TABLE_SCHEMA` = '%s' AND `TABLE_TYPE` = 'BASE TABLE'"), db->getName().c_str()) ); |
| 573 |
DatabaseResultSet* tabulky = dbLayer->RunQueryWithResults( |
| 574 |
wxString::Format(wxT("SELECT * FROM pg_views WHERE schemaname = 'public'"))); |
| 575 |
|
| 576 |
while(tabulky->Next()) { |
| 577 |
db->AddChild(new View(this, tabulky->GetResultString(wxT("viewname")), db->GetName(), |
| 578 |
tabulky->GetResultString(wxT("definition")))); |
| 579 |
} |
| 580 |
dbLayer->CloseResultSet(tabulky); |
| 581 |
dbLayer->Close(); |
| 582 |
} |
| 583 |
} |
| 584 |
return; |
| 585 |
} |
| 586 |
wxString PostgreSqlDbAdapter::GetCreateViewSql(View* view, bool dropView) |
| 587 |
{ |
| 588 |
wxString str = wxT(""); |
| 589 |
if(view) { |
| 590 |
if(dropView) { |
| 591 |
str.append(wxString::Format(wxT("DROP VIEW IF EXISTS %s;\n"), view->GetName().c_str())); |
| 592 |
} |
| 593 |
str.append( |
| 594 |
wxString::Format(wxT("CREATE VIEW %s AS %s ;\n"), view->GetName().c_str(), view->GetSelect().c_str())); |
| 595 |
} |
| 596 |
str.append(wxT("-- -------------------------------------------------------------\n")); |
| 597 |
return str; |
| 598 |
} |
| 599 |
void PostgreSqlDbAdapter::ConvertTable(Table* pTab) |
| 600 |
{ |
| 601 |
SerializableList::compatibility_iterator node = pTab->GetFirstChildNode(); |
| 602 |
while(node) { |
| 603 |
if(node->GetData()->IsKindOf(CLASSINFO(Column))) { |
| 604 |
Column* col = (Column*)node->GetData(); |
| 605 |
col->SetType(ConvertType(col->GetType())); |
| 606 |
} |
| 607 |
node = node->GetNext(); |
| 608 |
} |
| 609 |
} |
| 610 |
|
| 611 |
IDbType* PostgreSqlDbAdapter::ConvertType(IDbType* pType) |
| 612 |
{ |
| 613 |
IDbType* newType = wxDynamicCast(pType, PostgreSqlType); |
| 614 |
if(!newType) { |
| 615 |
newType = GetDbTypeByUniversalName(pType->GetUniversalType()); |
| 616 |
delete pType; |
| 617 |
} |
| 618 |
return newType; |
| 619 |
} |
| 620 |
|
| 621 |
IDbType* PostgreSqlDbAdapter::GetDbTypeByUniversalName(IDbType::UNIVERSAL_TYPE type) |
| 622 |
{ |
| 623 |
IDbType* newType = NULL; |
| 624 |
switch(type) { |
| 625 |
case IDbType::dbtTYPE_INT: |
| 626 |
newType = GetDbTypeByName(wxT("INTEGER")); |
| 627 |
break; |
| 628 |
case IDbType::dbtTYPE_BOOLEAN: |
| 629 |
newType = GetDbTypeByName(wxT("BOOLEAN")); |
| 630 |
break; |
| 631 |
case IDbType::dbtTYPE_DATE_TIME: |
| 632 |
newType = GetDbTypeByName(wxT("DATE")); |
| 633 |
break; |
| 634 |
case IDbType::dbtTYPE_DECIMAL: |
| 635 |
newType = GetDbTypeByName(wxT("DECIMAL")); |
| 636 |
break; |
| 637 |
case IDbType::dbtTYPE_FLOAT: |
| 638 |
newType = GetDbTypeByName(wxT("DOUBLE PRECISION")); |
| 639 |
break; |
| 640 |
case IDbType::dbtTYPE_TEXT: |
| 641 |
newType = GetDbTypeByName(wxT("TEXT")); |
| 642 |
break; |
| 643 |
case IDbType::dbtTYPE_OTHER: |
| 644 |
newType = GetDbTypeByName(wxT("BYTEA")); |
| 645 |
break; |
| 646 |
} |
| 647 |
return newType; |
| 648 |
} |
| 649 |
IDbAdapter* PostgreSqlDbAdapter::Clone() |
| 650 |
{ |
| 651 |
return new PostgreSqlDbAdapter(m_serverName, m_port, m_defaultDb, m_userName, m_password); |
| 652 |
} |
| 653 |
|
| 654 |
wxString PostgreSqlDbAdapter::GetDropViewSql(View* pView) |
| 655 |
{ |
| 656 |
return wxString::Format(wxT("DROP VIEW IF EXISTS %s;"), pView->GetName().c_str()); |
| 657 |
} |