Debian Codegraph @

DatabaseExplorer/SqlCommandPanel.cpp

raw
1 //////////////////////////////////////////////////////////////////////////////
2 //////////////////////////////////////////////////////////////////////////////
3 //
4 // copyright : (C) 2014 Eran Ifrah
5 // file name : SqlCommandPanel.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 "SqlCommandPanel.h"
27
28 #include "DbViewerPanel.h"
29 #include "bitmap_loader.h"
30 #include "clKeyboardManager.h"
31 #include "clStatusBarMessage.h"
32 #include "cl_aui_tool_stickness.h"
33 #include "cl_defs.h"
34 #include "db_explorer_settings.h"
35 #include "editor_config.h"
36 #include "globals.h"
37 #include "imanager.h"
38 #include "lexer_configuration.h"
39
40 #include <algorithm>
41 #include <set>
42 #include <wx/busyinfo.h>
43 #include <wx/file.h>
44 #include <wx/textfile.h>
45 #include <wx/wupdlock.h>
46 #include <wx/xrc/xmlres.h>
47
48 #if CL_USE_NATIVEBOOK
49 #ifdef __WXGTK20__
50 // We need this ugly hack to workaround a gtk2-wxGTK name-clash
51 // See http://trac.wxwidgets.org/ticket/10883
52 #define GSocket GlibGSocket
53 #include <gtk/gtk.h>
54 #undef GSocket
55 #endif
56 #endif
57
58 const wxEventType wxEVT_EXECUTE_SQL = XRCID("wxEVT_EXECUTE_SQL");
59
60 BEGIN_EVENT_TABLE(SQLCommandPanel, _SqlCommandPanel)
61 EVT_COMMAND(wxID_ANY, wxEVT_EXECUTE_SQL, SQLCommandPanel::OnExecuteSQL)
62 END_EVENT_TABLE()
63
64 SQLCommandPanel::SQLCommandPanel(wxWindow* parent, IDbAdapter* dbAdapter, const wxString& dbName,
65 const wxString& dbTable)
66 : _SqlCommandPanel(parent)
67 {
68 LexerConf::Ptr_t lexerSQL = EditorConfigST::Get()->GetLexer("SQL");
69 if(lexerSQL) {
70 lexerSQL->Apply(m_scintillaSQL, true);
71
72 // determine how an operator and a comment are styled
73 const auto& lexerProperties = lexerSQL->GetLexerProperties();
74 auto operatorStyle = std::find_if(lexerProperties.begin(), lexerProperties.end(),
75 [](const StyleProperty& prop) { return prop.GetName() == "Operator"; });
76
77 auto commentStyle = std::find_if(lexerProperties.begin(), lexerProperties.end(),
78 [](const StyleProperty& prop) { return prop.GetName() == "Comment block"; });
79
80 if(std::end(lexerProperties) != operatorStyle) {
81 m_OperatorStyle = operatorStyle->GetId();
82 }
83 if(std::end(lexerProperties) != commentStyle) {
84 m_CommentStyle = commentStyle->GetId();
85 }
86 } else {
87 DbViewerPanel::InitStyledTextCtrl(m_scintillaSQL);
88 }
89
90 m_pDbAdapter = dbAdapter;
91 m_dbName = dbName;
92 m_dbTable = dbTable;
93
94 m_editHelper.Reset(new clEditEventsHandler(m_scintillaSQL));
95 m_scintillaSQL->AddText(wxString::Format(_(" -- selected database %s\n"), m_dbName.c_str()));
96 if(!dbTable.IsEmpty()) {
97 m_scintillaSQL->AddText(m_pDbAdapter->GetDefaultSelect(m_dbName, m_dbTable));
98 wxCommandEvent event(wxEVT_EXECUTE_SQL);
99 GetEventHandler()->AddPendingEvent(event);
100 }
101
102 m_toolbar = new clToolBarGeneric(this);
103 auto images = m_toolbar->GetBitmapsCreateIfNeeded();
104 m_toolbar->AddTool(wxID_OPEN, _("Load SQL Script"), images->Add("file_open"));
105 m_toolbar->AddTool(wxID_EXECUTE, _("Execute SQL"), images->Add("execute"));
106 m_toolbar->Realize();
107 GetSizer()->Insert(0, m_toolbar, 0, wxEXPAND);
108
109 // Bind events
110 m_toolbar->Bind(wxEVT_TOOL, &SQLCommandPanel::OnExecuteClick, this, wxID_EXECUTE);
111 m_toolbar->Bind(wxEVT_TOOL, &SQLCommandPanel::OnLoadClick, this, wxID_OPEN);
112 }
113
114 SQLCommandPanel::~SQLCommandPanel() { wxDELETE(m_pDbAdapter); }
115
116 void SQLCommandPanel::OnExecuteClick(wxCommandEvent& event) { ExecuteSql(); }
117
118 void SQLCommandPanel::OnScintilaKeyDown(wxKeyEvent& event)
119 {
120 if((event.ControlDown()) && (event.GetKeyCode() == WXK_RETURN || event.GetKeyCode() == WXK_NUMPAD_ENTER)) {
121 ExecuteSql();
122 }
123 event.Skip();
124 }
125
126 void SQLCommandPanel::ExecuteSql()
127 {
128 clStatusBarMessage message(_("Executing SQL..."));
129
130 clWindowUpdateLocker locker(this);
131 std::set<int> textCols;
132 std::set<int> blobCols;
133 DatabaseLayerPtr m_pDbLayer = m_pDbAdapter->GetDatabaseLayer(m_dbName);
134 if(m_pDbLayer->IsOpen()) {
135 // build string of SQL statements with comments removed
136 wxArrayString sqls = ParseSql();
137 wxString sqlStmt = "";
138 for(size_t i = 0; i < sqls.GetCount(); i++) {
139 sqlStmt += sqls[i];
140 }
141
142 // save the history
143 SaveSqlHistory(sqls);
144
145 if(!sqls.IsEmpty()) {
146 try {
147 m_colsMetaData.clear();
148 if(!m_pDbAdapter->GetUseDb(m_dbName).IsEmpty())
149 m_pDbLayer->RunQuery(m_pDbAdapter->GetUseDb(m_dbName));
150 // run query
151 DatabaseResultSet* pResultSet = m_pDbLayer->RunQueryWithResults(sqlStmt);
152
153 m_table->ClearAll();
154 if(!pResultSet) {
155 wxMessageBox(_("Unknown SQL error."), _("DB Error"), wxOK | wxICON_ERROR);
156 return;
157 }
158
159 int cols = pResultSet->GetMetaData()->GetColumnCount();
160 m_colsMetaData.resize(cols);
161
162 // create table header
163 wxArrayString columns;
164 for(int i = 1; i <= pResultSet->GetMetaData()->GetColumnCount(); i++) {
165 columns.Add(pResultSet->GetMetaData()->GetColumnName(i));
166 m_colsMetaData.at(i - 1) = ColumnInfo(pResultSet->GetMetaData()->GetColumnType(i),
167 pResultSet->GetMetaData()->GetColumnName(i));
168 }
169 m_table->SetColumns(columns);
170
171 // fill table data
172 std::vector<wxArrayString> data;
173 while(pResultSet->Next()) {
174 data.push_back(wxArrayString());
175 wxArrayString& row = data.back();
176 wxString value;
177 for(int i = 1; i <= pResultSet->GetMetaData()->GetColumnCount(); i++) {
178
179 switch(pResultSet->GetMetaData()->GetColumnType(i)) {
180 case ResultSetMetaData::COLUMN_INTEGER:
181 if(m_pDbAdapter->GetAdapterType() == IDbAdapter::atSQLITE) {
182 value = pResultSet->GetResultString(i);
183
184 } else {
185 value = wxString::Format(wxT("%i"), pResultSet->GetResultInt(i));
186 }
187 break;
188
189 case ResultSetMetaData::COLUMN_STRING:
190 value = pResultSet->GetResultString(i);
191 break;
192
193 case ResultSetMetaData::COLUMN_UNKNOWN:
194 value = pResultSet->GetResultString(i);
195 break;
196
197 case ResultSetMetaData::COLUMN_BLOB: {
198 if(textCols.find(i) != textCols.end()) {
199 // this column should be displayed as TEXT rather than BLOB
200 value = pResultSet->GetResultString(i);
201
202 } else if(blobCols.find(i) != blobCols.end()) {
203 // this column should be displayed as BLOB
204 wxMemoryBuffer buffer;
205 pResultSet->GetResultBlob(i, buffer);
206 value = wxString::Format(wxT("BLOB (Size:%u)"), buffer.GetDataLen());
207
208 } else {
209 // first time
210 wxString strCol = pResultSet->GetResultString(i);
211 if(IsBlobColumn(strCol)) {
212 blobCols.insert(i);
213 wxMemoryBuffer buffer;
214 pResultSet->GetResultBlob(i, buffer);
215 value = wxString::Format(wxT("BLOB (Size:%u)"), buffer.GetDataLen());
216
217 } else {
218 textCols.insert(i);
219 value = strCol;
220 }
221 }
222 break;
223 }
224 case ResultSetMetaData::COLUMN_BOOL:
225 value = wxString::Format(wxT("%b"), pResultSet->GetResultBool(i));
226 break;
227
228 case ResultSetMetaData::COLUMN_DATE: {
229 wxDateTime dt = pResultSet->GetResultDate(i);
230 if(dt.IsValid()) {
231 value = dt.Format();
232 } else {
233 value.Clear();
234 }
235 } break;
236
237 case ResultSetMetaData::COLUMN_DOUBLE:
238 value = wxString::Format(wxT("%f"), pResultSet->GetResultDouble(i));
239 break;
240
241 case ResultSetMetaData::COLUMN_NULL:
242 value = wxT("NULL");
243 break;
244
245 default:
246 value = pResultSet->GetResultString(i);
247 break;
248 }
249 row.Add(value);
250 }
251 }
252 m_pDbLayer->CloseResultSet(pResultSet);
253
254 // Popuplate the data
255 m_table->SetData(data);
256 GetSizer()->Layout();
257 Layout();
258
259 } catch(DatabaseLayerException& e) {
260 // for some reason an exception is thrown even if the error code is 0...
261 if(e.GetErrorCode() != 0) {
262 wxString errorMessage =
263 wxString::Format(_("Error (%d): %s"), e.GetErrorCode(), e.GetErrorMessage().c_str());
264 wxMessageDialog dlg(this, errorMessage, _("DB Error"), wxOK | wxCENTER | wxICON_ERROR);
265 dlg.ShowModal();
266 }
267
268 } catch(...) {
269 wxMessageDialog dlg(this, _("Unknown error."), _("DB Error"), wxOK | wxCENTER | wxICON_ERROR);
270 dlg.ShowModal();
271 }
272 }
273
274 } else {
275 wxMessageBox(_("Cant connect!"));
276 }
277 }
278
279 void SQLCommandPanel::OnLoadClick(wxCommandEvent& event)
280 {
281 wxFileDialog dlg(this, _("Choose a file"), wxT(""), wxT(""), wxT("Sql files(*.sql)|*.sql"),
282 wxFD_OPEN | wxFD_FILE_MUST_EXIST);
283 m_scintillaSQL->ClearAll();
284 if(dlg.ShowModal() == wxID_OK) {
285 wxTextFile file(dlg.GetPath());
286 file.Open();
287 if(file.IsOpened()) {
288 for(wxString str = file.GetFirstLine(); !file.Eof(); str = file.GetNextLine()) {
289 m_scintillaSQL->AddText(str);
290 m_scintillaSQL->AddText(wxT("\n"));
291 }
292 }
293 }
294 }
295
296 void SQLCommandPanel::OnSaveClick(wxCommandEvent& event)
297 {
298 wxFileDialog dlg(this, _("Chose a file"), wxT(""), wxT(""), wxT("Sql files(*.sql)|*.sql"),
299 wxFD_SAVE | wxFD_OVERWRITE_PROMPT);
300 if(dlg.ShowModal() == wxID_OK) {
301
302 wxFile file(dlg.GetPath(), wxFile::write);
303 if(file.IsOpened()) {
304 file.Write(m_scintillaSQL->GetText());
305 file.Close();
306 }
307 }
308 }
309
310 void SQLCommandPanel::OnTeplatesLeftDown(wxMouseEvent& event) {}
311 void SQLCommandPanel::OnTemplatesBtnClick(wxAuiToolBarEvent& event)
312 {
313 wxMenu menu;
314 menu.Append(XRCID("IDR_SQLCOMMAND_SELECT"), _("Insert SELECT SQL template"),
315 _("Insert SELECT SQL statement template into editor."));
316 menu.Append(XRCID("IDR_SQLCOMMAND_INSERT"), _("Insert INSERT SQL template"),
317 _("Insert INSERT SQL statement template into editor."));
318 menu.Append(XRCID("IDR_SQLCOMMAND_UPDATE"), _("Insert UPDATE SQL template"),
319 _("Insert UPDATE SQL statement template into editor."));
320 menu.Append(XRCID("IDR_SQLCOMMAND_DELETE"), _("Insert DELETE SQL template"),
321 _("Insert DELETE SQL statement template into editor."));
322 menu.Connect(wxEVT_COMMAND_MENU_SELECTED, (wxObjectEventFunction)&SQLCommandPanel::OnPopupClick, NULL, this);
323
324 wxAuiToolBar* auibar = dynamic_cast<wxAuiToolBar*>(event.GetEventObject());
325 if(auibar) {
326 clAuiToolStickness ts(auibar, event.GetToolId());
327 wxRect rect = auibar->GetToolRect(event.GetId());
328 wxPoint pt = auibar->ClientToScreen(rect.GetBottomLeft());
329 pt = ScreenToClient(pt);
330 PopupMenu(&menu, pt);
331 }
332 }
333
334 void SQLCommandPanel::OnPopupClick(wxCommandEvent& evt)
335 {
336 wxString text = m_scintillaSQL->GetText();
337 text.Trim().Trim(false);
338
339 text.Append(wxT("\n"));
340
341 if(evt.GetId() == XRCID("IDR_SQLCOMMAND_SELECT")) {
342 text << wxT("SELECT * FROM TableName\n");
343
344 } else if(evt.GetId() == XRCID("IDR_SQLCOMMAND_INSERT")) {
345 text << wxT("INSERT INTO TableName (ColumnA, ColumnB) VALUES (1,'Test text')\n");
346
347 } else if(evt.GetId() == XRCID("IDR_SQLCOMMAND_UPDATE")) {
348 text << wxT("UPDATE TableName SET ColumnA = 2, ColumnB = 'Second text' WHERE ID = 1\n");
349
350 } else if(evt.GetId() == XRCID("IDR_SQLCOMMAND_DELETE")) {
351 text << wxT("DELETE FROM TableName WHERE ID = 1\n");
352 }
353
354 m_scintillaSQL->SetText(text);
355 m_scintillaSQL->SetSelectionStart(m_scintillaSQL->GetLength() - 1);
356 m_scintillaSQL->SetSelectionEnd(m_scintillaSQL->GetLength() - 1);
357 m_scintillaSQL->SetFocus();
358 }
359
360 void SQLCommandPanel::OnExecuteSQL(wxCommandEvent& e)
361 {
362 wxUnusedVar(e);
363 ExecuteSql();
364 }
365
366 void SQLCommandPanel::OnCopyCellValue(wxCommandEvent& e)
367 {
368 if(m_cellValue.IsEmpty() == false) {
369 CopyToClipboard(m_cellValue);
370 }
371 }
372
373 bool SQLCommandPanel::IsBlobColumn(const wxString& str)
374 {
375 for(size_t i = 0; i < str.Len(); i++) {
376 if(!wxIsprint(str.GetChar(i))) {
377 return true;
378 }
379 }
380 return false;
381 }
382
383 void SQLCommandPanel::SetDefaultSelect()
384 {
385 m_scintillaSQL->ClearAll();
386 m_scintillaSQL->AddText(wxString::Format(_(" -- selected database %s\n"), m_dbName.c_str()));
387 if(!m_dbTable.IsEmpty()) {
388 m_scintillaSQL->AddText(m_pDbAdapter->GetDefaultSelect(m_dbName, m_dbTable));
389 CallAfter(&SQLCommandPanel::ExecuteSql);
390 }
391 }
392
393 void SQLCommandPanel::OnHistoryToolClicked(wxAuiToolBarEvent& event)
394 {
395 wxAuiToolBar* auibar = dynamic_cast<wxAuiToolBar*>(event.GetEventObject());
396 if(auibar) {
397 clAuiToolStickness ts(auibar, event.GetToolId());
398 wxRect rect = auibar->GetToolRect(event.GetId());
399 wxPoint pt = auibar->ClientToScreen(rect.GetBottomLeft());
400 pt = ScreenToClient(pt);
401
402 DbExplorerSettings settings;
403 clConfig conf(DBE_CONFIG_FILE);
404 conf.ReadItem(&settings);
405 settings.GetRecentFiles();
406
407 wxArrayString sqls = settings.GetSqlHistory();
408 wxMenu menu;
409 for(size_t i = 0; i < sqls.GetCount(); ++i) {
410 menu.Append(wxID_HIGHEST + i, sqls.Item(i));
411 }
412
413 int pos = GetPopupMenuSelectionFromUser(menu, pt);
414 if(pos == wxID_NONE)
415 return;
416
417 size_t index = pos - wxID_HIGHEST;
418 if(index > sqls.GetCount())
419 return;
420
421 m_scintillaSQL->SetText(sqls.Item(index));
422 CallAfter(&SQLCommandPanel::ExecuteSql);
423 }
424 }
425
426 wxArrayString SQLCommandPanel::ParseSql() const
427 {
428 const char SEMICOLON = ';';
429 const char SPACE = ' ';
430
431 wxMemoryBuffer styledText = m_scintillaSQL->GetStyledText(0, m_scintillaSQL->GetLength());
432 auto bufSize = styledText.GetDataLen();
433 char* pStyledTextBuf = static_cast<char*>(styledText.GetData());
434
435 int startPos = 0;
436 int stopPos = 0;
437 wxString currStmt = "";
438
439 char currChar;
440 char currStyle;
441
442 wxArrayString sqls;
443 bool bAdded = true;
444
445 for(size_t index = 0; index < bufSize; index += 2) {
446
447 currChar = pStyledTextBuf[index];
448 currStyle = pStyledTextBuf[index + 1];
449
450 // eat comments
451 if(m_CommentStyle == currStyle) {
452
453 // copy the string previous to the comments
454 currStmt += m_scintillaSQL->GetTextRange(startPos, stopPos);
455 // replace the comments with a space
456 currStmt += SPACE;
457 while((m_CommentStyle == currStyle || std::isspace(currChar)) && index < bufSize) {
458 index += 2;
459 currChar = pStyledTextBuf[index];
460 currStyle = pStyledTextBuf[index + 1];
461 stopPos++;
462 }
463 startPos = stopPos;
464 }
465
466 // non-comment, valid character
467 if(m_CommentStyle != currStyle && 0 != currChar) {
468 stopPos++;
469 bAdded = false;
470 }
471
472 // found an operator semi-colon to mark end of statement
473 if(m_OperatorStyle == currStyle && SEMICOLON == currChar) {
474 currStmt += m_scintillaSQL->GetTextRange(startPos, stopPos);
475
476 currStmt.Trim(false);
477 currStmt.Trim();
478 if(currStmt.length() != 0) {
479 sqls.Add(currStmt);
480 currStmt.clear();
481 bAdded = true;
482 }
483 startPos = stopPos;
484 stopPos = startPos;
485 }
486 }
487
488 // in case the last statement did not end in a semicolon
489 if(!bAdded) {
490 currStmt += m_scintillaSQL->GetTextRange(startPos, stopPos);
491
492 currStmt.Trim(false);
493 currStmt.Trim();
494 if(currStmt.length() != 0) {
495 sqls.Add(currStmt);
496 }
497 }
498 return sqls;
499 }
500
501 void SQLCommandPanel::SaveSqlHistory(wxArrayString sqls)
502 {
503 if(sqls.IsEmpty())
504 return;
505
506 DbExplorerSettings s;
507 clConfig conf(DBE_CONFIG_FILE);
508 conf.ReadItem(&s);
509 const wxArrayString& history = s.GetSqlHistory();
510
511 // Append the current history to the new sqls (exclude dups)
512 for(size_t i = 0; i < history.GetCount(); ++i) {
513 if(sqls.Index(history.Item(i)) == wxNOT_FOUND) {
514 sqls.Add(history.Item(i));
515 }
516 }
517
518 // Truncate the buffer
519 while(sqls.GetCount() > 15) {
520 sqls.RemoveAt(sqls.GetCount() - 1);
521 }
522
523 s.SetSqlHistory(sqls);
524 conf.WriteItem(&s);
525 }