Debian Codegraph @

DatabaseExplorer/PostgreSqlDbAdapter.cpp

raw
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 }