Debian Codegraph @

DatabaseExplorer/SqliteDbAdapter.cpp

raw
1 //////////////////////////////////////////////////////////////////////////////
2 //////////////////////////////////////////////////////////////////////////////
3 //
4 // copyright : (C) 2014 Eran Ifrah
5 // file name : SqliteDbAdapter.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 "SqliteDbAdapter.h"
27 #include "database.h"
28 #include "dbconnection.h"
29 #include "table.h"
30
31 SQLiteDbAdapter::SQLiteDbAdapter()
32 {
33 m_sFileName = wxT("");
34 m_adapterType = atSQLITE;
35 }
36 SQLiteDbAdapter::SQLiteDbAdapter(const wxString& fileName)
37 {
38 m_sFileName = fileName;
39 m_adapterType = atSQLITE;
40 }
41
42 SQLiteDbAdapter::~SQLiteDbAdapter() {}
43
44 void SQLiteDbAdapter::CloseConnection() {}
45 DatabaseLayerPtr SQLiteDbAdapter::GetDatabaseLayer(const wxString& dbName)
46 {
47 DatabaseLayer* pDatabase = NULL;
48
49 #ifdef DBL_USE_SQLITE
50 pDatabase = new SqliteDatabaseLayer(m_sFileName);
51 #endif
52
53 return pDatabase;
54 }
55
56 IDbType* SQLiteDbAdapter::GetDbTypeByName(const wxString& typeName)
57 {
58 IDbType* type = NULL;
59 wxString typeNameString = typeName.Upper();
60 if(typeNameString == wxT("NULL")) {
61 type = new SqliteType(wxT("NULL"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_OTHER);
62
63 } else if(typeNameString == wxT("INTEGER")) {
64 type = new SqliteType(wxT("INTEGER"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_INT);
65 } else if(typeNameString == wxT("INT")) {
66 type = new SqliteType(wxT("INT"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_INT);
67 } else if(typeNameString == wxT("TINYINT")) {
68 type = new SqliteType(wxT("TINYINT"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_INT);
69 } else if(typeNameString == wxT("SMALLINT")) {
70 type = new SqliteType(wxT("SMALLINT"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_INT);
71 } else if(typeNameString == wxT("MEDIUMINT")) {
72 type = new SqliteType(wxT("MEDIUMINT"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_INT);
73 } else if(typeNameString == wxT("BIGINT")) {
74 type = new SqliteType(wxT("BIGINT"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_INT);
75
76 } else if(typeNameString == wxT("BOOLEAN")) {
77 type = new SqliteType(wxT("BOOLEAN"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_BOOLEAN);
78
79 } else if(typeNameString == wxT("REAL")) {
80 type = new SqliteType(wxT("REAL"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_FLOAT);
81 } else if(typeNameString == wxT("FLOAT")) {
82 type = new SqliteType(wxT("FLOAT"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_FLOAT);
83 } else if(typeNameString == wxT("DOUBLE")) {
84 type = new SqliteType(wxT("DOUBLE"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_FLOAT);
85
86 } else if(typeNameString == wxT("TEXT")) {
87 type = new SqliteType(wxT("TEXT"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_TEXT);
88 } else if(typeNameString == wxT("CHARACTER")) {
89 type = new SqliteType(wxT("CHARACTER"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_TEXT);
90 } else if(typeNameString == wxT("VARCHAR")) {
91 type = new SqliteType(wxT("VARCHAR"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_TEXT);
92 } else if(typeNameString == wxT("DATETIME")) {
93 type = new SqliteType(wxT("DATETIME"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_TEXT);
94
95 } else if(typeNameString == wxT("BLOB")) {
96 type = new SqliteType(wxT("BLOB"), IDbType::dbtNOT_NULL, IDbType::dbtTYPE_OTHER);
97 } else
98 type = new SqliteType(typeNameString, IDbType::dbtNOT_NULL, IDbType::dbtTYPE_TEXT);
99
100 wxASSERT(type);
101 return type;
102 }
103 wxArrayString* SQLiteDbAdapter::GetDbTypes()
104 {
105 wxArrayString* pNames = new wxArrayString();
106 pNames->Add(wxT("NULL"));
107 pNames->Add(wxT("INT"));
108 pNames->Add(wxT("INTEGER"));
109 pNames->Add(wxT("REAL"));
110 pNames->Add(wxT("TEXT"));
111 pNames->Add(wxT("BLOB"));
112 return pNames;
113 }
114
115 bool SQLiteDbAdapter::IsConnected() { return CanConnect(); }
116 wxString SQLiteDbAdapter::GetDefaultSelect(const wxString& dbName, const wxString& tableName)
117 {
118 return wxString::Format(wxT("SELECT * FROM '%s'.'%s' LIMIT 0, 100;"), dbName.c_str(), tableName.c_str());
119 }
120 wxString SQLiteDbAdapter::GetDefaultSelect(const wxString& cols, const wxString& dbName, const wxString& tableName)
121 {
122 return wxString::Format(wxT("SELECT %s FROM '%s'.'%s' LIMIT 0, 100;"), cols.c_str(), dbName.c_str(),
123 tableName.c_str());
124 }
125 bool SQLiteDbAdapter::GetColumns(Table* pTab)
126 {
127 int i = 0;
128 DatabaseLayerPtr dbLayer = this->GetDatabaseLayer(wxT(""));
129 if(dbLayer) {
130 if(!dbLayer->IsOpen())
131 return false;
132
133 // loading columns
134 // TODO:SQL:
135 DatabaseResultSet* database =
136 dbLayer->RunQueryWithResults(wxString::Format(wxT("PRAGMA table_info('%s')"), pTab->GetName().c_str()));
137 while(database->Next()) {
138 IDbType* pType = GetDbTypeByName(database->GetResultString(3));
139 if(pType) {
140 pType->SetNotNull(database->GetResultInt(4) == 1);
141 Column* pCol = new Column(database->GetResultString(2), pTab->GetName(), pType);
142 pTab->AddChild(pCol);
143 if(database->GetResultInt(6) == 1) {
144 Constraint* constr = new Constraint();
145 constr->SetName(wxString::Format(wxT("PK_%s"), pTab->GetName().c_str()));
146 constr->SetLocalColumn(pCol->GetName());
147 constr->SetType(Constraint::primaryKey);
148 pTab->AddChild(constr);
149 }
150 }
151 }
152 dbLayer->CloseResultSet(database);
153
154 database = dbLayer->RunQueryWithResults(
155 wxString::Format(wxT("PRAGMA foreign_key_list('%s')"), pTab->GetName().c_str()));
156 while(database->Next()) {
157 Constraint* constr = new Constraint();
158 constr->SetName(wxString::Format(wxT("FK_%s_%i"), pTab->GetName().c_str(), i++));
159 constr->SetLocalColumn(database->GetResultString(4));
160 constr->SetType(Constraint::foreignKey);
161 constr->SetRefTable(database->GetResultString(3));
162 constr->SetRefCol(database->GetResultString(5));
163
164 wxString onDelete = database->GetResultString(6);
165 if(onDelete == wxT("RESTRICT"))
166 constr->SetOnUpdate(Constraint::restrict);
167 if(onDelete == wxT("CASCADE"))
168 constr->SetOnUpdate(Constraint::cascade);
169 if(onDelete == wxT("SET NULL"))
170 constr->SetOnUpdate(Constraint::setNull);
171 if(onDelete == wxT("NO ACTION"))
172 constr->SetOnUpdate(Constraint::noAction);
173
174 wxString onUpdate = database->GetResultString(7);
175 if(onUpdate == wxT("RESTRICT"))
176 constr->SetOnDelete(Constraint::restrict);
177 if(onUpdate == wxT("CASCADE"))
178 constr->SetOnDelete(Constraint::cascade);
179 if(onUpdate == wxT("SET NULL"))
180 constr->SetOnDelete(Constraint::setNull);
181 if(onUpdate == wxT("NO ACTION"))
182 constr->SetOnDelete(Constraint::noAction);
183
184 pTab->AddChild(constr);
185 }
186 dbLayer->CloseResultSet(database);
187
188 dbLayer->Close();
189 }
190 return true;
191 }
192 wxString SQLiteDbAdapter::GetCreateTableSql(Table* tab, bool dropTable)
193 {
194 wxString str = wxT("");
195 // if (dropTable) str = wxString::Format(wxT("DROP TABLE IF EXISTS '%s'.'%s'; \n"), tab->GetParentName().c_str(),
196 // tab->GetName().c_str()); str.append(wxString::Format(wxT("CREATE TABLE '%s'.'%s' (\n"),
197 // tab->GetParentName().c_str(), tab->GetName().c_str()));
198 if(dropTable)
199 str = wxString::Format(wxT("DROP TABLE IF EXISTS '%s'; \n"), tab->GetName().c_str());
200 str.append(wxString::Format(wxT("CREATE TABLE '%s' (\n"), tab->GetName().c_str()));
201
202 SerializableList::compatibility_iterator node = tab->GetFirstChildNode();
203 while(node) {
204 Column* col = NULL;
205 if(node->GetData()->IsKindOf(CLASSINFO(Column)))
206 col = (Column*)node->GetData();
207 if(col)
208 str.append(wxString::Format(wxT("\t'%s' %s"), col->GetName().c_str(), col->GetType()->ReturnSql().c_str()));
209
210 node = node->GetNext();
211 if(node) {
212 Column* pc = wxDynamicCast(node->GetData(), Column);
213 if(pc)
214 str.append(wxT(",\n "));
215 }
216 }
217
218 bool start = true;
219 node = tab->GetFirstChildNode();
220 while(node) {
221 Constraint* constr = wxDynamicCast(node->GetData(), Constraint);
222 if(constr) {
223 if(start) {
224 // str.append(wxT(",\n "));
225 start = false;
226 }
227 if(constr->GetType() == Constraint::primaryKey)
228 str.append(wxString::Format(wxT("\tPRIMARY KEY ('%s')"), constr->GetLocalColumn().c_str()));
229 if(constr->GetType() == Constraint::foreignKey)
230 str.append(wxString::Format(wxT("\tFOREIGN KEY('%s') REFERENCES %s('%s')"),
231 constr->GetLocalColumn().c_str(), constr->GetRefTable().c_str(),
232 constr->GetRefCol().c_str()));
233 }
234
235 node = node->GetNext();
236 if(node) {
237 constr = wxDynamicCast(node->GetData(), Constraint);
238 if(constr) {
239 str.append(wxT(",\n "));
240 }
241 }
242 }
243
244 str.append(wxT(");\n"));
245 str.append(wxT("-- -------------------------------------------------------------\n"));
246 return str;
247 }
248
249 bool SQLiteDbAdapter::CanConnect() { return m_sFileName != wxT(""); }
250
251 void SQLiteDbAdapter::GetDatabases(DbConnection* dbCon)
252 {
253 DatabaseLayerPtr dbLayer = this->GetDatabaseLayer(wxT(""));
254 if(dbLayer) {
255 if(!dbLayer->IsOpen())
256 return;
257
258 // TODO:SQL:
259 DatabaseResultSet* databaze = dbLayer->RunQueryWithResults(wxT("PRAGMA database_list;"));
260 while(databaze->Next()) {
261 dbCon->AddChild(new Database(this, databaze->GetResultString(2)));
262 }
263 dbLayer->CloseResultSet(databaze);
264 dbLayer->Close();
265 }
266 }
267
268 void SQLiteDbAdapter::GetTables(Database* db, bool includeViews)
269 {
270 DatabaseLayerPtr dbLayer = this->GetDatabaseLayer(wxT(""));
271 if(dbLayer) {
272 if(!dbLayer->IsOpen())
273 return;
274 // TODO:SQL:
275
276 DatabaseResultSet* tabulky = NULL;
277 if(includeViews) {
278 tabulky = dbLayer->RunQueryWithResults(wxString::Format(
279 wxT("SELECT * FROM '%s'.sqlite_master WHERE type='table' OR type='view'"), db->GetName().c_str()));
280 } else {
281 tabulky = dbLayer->RunQueryWithResults(
282 wxString::Format(wxT("SELECT * FROM '%s'.sqlite_master WHERE type='table'"), db->GetName().c_str()));
283 }
284
285 while(tabulky->Next()) {
286 db->AddChild(new Table(this, tabulky->GetResultString(2), db->GetName(),
287 tabulky->GetResultString(wxT("type")).Contains(wxT("view"))));
288 }
289 dbLayer->CloseResultSet(tabulky);
290
291 tabulky = dbLayer->RunQueryWithResults(
292 wxString::Format(wxT("SELECT * FROM '%s'.sqlite_master WHERE type='view'"), db->GetName().c_str()));
293 while(tabulky->Next()) {
294 wxString select = tabulky->GetResultString(5);
295 select = select.Mid(select.Find(wxT("SELECT")));
296 db->AddChild(new View(this, tabulky->GetResultString(2), db->GetName(), select));
297 }
298 dbLayer->CloseResultSet(tabulky);
299
300 dbLayer->Close();
301 }
302 }
303 wxString SQLiteDbAdapter::GetCreateDatabaseSql(const wxString& dbName) { return wxT(""); }
304 wxString SQLiteDbAdapter::GetDropTableSql(Table* pTab)
305 {
306 // return wxString::Format(wxT("DROP TABLE IF EXISTS '%s'.'%s';"), pTab->GetParentName().c_str(),
307 // pTab->GetName().c_str());
308 return wxString::Format(wxT("DROP TABLE IF EXISTS '%s';"), pTab->GetName().c_str());
309 }
310 wxString SQLiteDbAdapter::GetAlterTableConstraintSql(Table* tab) { return wxT(""); }
311 wxString SQLiteDbAdapter::GetDropDatabaseSql(Database* pDb) { return wxT(""); }
312 wxString SQLiteDbAdapter::GetUseDb(const wxString& dbName) { return wxT(""); }
313 void SQLiteDbAdapter::GetViews(Database* db) {}
314 wxString SQLiteDbAdapter::GetCreateViewSql(View* view, bool dropView)
315 {
316 wxString str = wxT("");
317 if(view) {
318 if(dropView) {
319 str.append(wxString::Format(wxT("DROP VIEW IF EXISTS '%s';\n"), view->GetName().c_str()));
320 }
321 str.append(
322 wxString::Format(wxT("CREATE VIEW '%s' AS %s;\n"), view->GetName().c_str(), view->GetSelect().c_str()));
323 }
324 str.append(wxT("-- -------------------------------------------------------------\n"));
325 return str;
326 }
327 void SQLiteDbAdapter::ConvertTable(Table* pTab)
328 {
329 SerializableList::compatibility_iterator node = pTab->GetFirstChildNode();
330 while(node) {
331 if(node->GetData()->IsKindOf(CLASSINFO(Column))) {
332 Column* col = (Column*)node->GetData();
333 col->SetType(ConvertType(col->GetType()));
334 }
335 node = node->GetNext();
336 }
337 }
338
339 IDbType* SQLiteDbAdapter::ConvertType(IDbType* pType)
340 {
341 SqliteType* newType = wxDynamicCast(pType, SqliteType);
342 if(!newType) {
343 newType = (SqliteType*)GetDbTypeByUniversalName(pType->GetUniversalType());
344 delete pType;
345 }
346 return newType;
347 }
348
349 IDbType* SQLiteDbAdapter::GetDbTypeByUniversalName(IDbType::UNIVERSAL_TYPE type)
350 {
351 IDbType* newType = NULL;
352 switch(type) {
353 case IDbType::dbtTYPE_INT:
354 newType = GetDbTypeByName(wxT("INTEGER"));
355 break;
356 case IDbType::dbtTYPE_BOOLEAN:
357 newType = GetDbTypeByName(wxT("INTEGER"));
358 break;
359 case IDbType::dbtTYPE_DATE_TIME:
360 newType = GetDbTypeByName(wxT("TEXT"));
361 break;
362 case IDbType::dbtTYPE_DECIMAL:
363 newType = GetDbTypeByName(wxT("REAL"));
364 break;
365 case IDbType::dbtTYPE_FLOAT:
366 newType = GetDbTypeByName(wxT("REAL"));
367 break;
368 case IDbType::dbtTYPE_TEXT:
369 newType = GetDbTypeByName(wxT("TEXT"));
370 break;
371 case IDbType::dbtTYPE_OTHER:
372 newType = GetDbTypeByName(wxT("BLOB"));
373 break;
374 }
375 return newType;
376 }
377 IDbAdapter* SQLiteDbAdapter::Clone() { return new SQLiteDbAdapter(m_sFileName); }
378
379 wxString SQLiteDbAdapter::GetDropViewSql(View* pView)
380 {
381 return wxString::Format(wxT("DROP VIEW IF EXISTS '%s';"), pView->GetName().c_str());
382 }