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