Merge pull request #8260 from lbartoletti/dbmanager_add_save_sql_file

[FEATURE][needs-docs]Add buttons to load and save an external SQL file
qgis · Nov 19, 2018 · 4e20972 · 4e20972
2 parents 50e2b76 + 03d53fb
commit 4e20972
Show file tree

Hide file tree

Showing 2 changed files with 69 additions and 5 deletions.
diff --git a/python/plugins/db_manager/dlg_sql_window.py b/python/plugins/db_manager/dlg_sql_window.py
@@ -26,8 +26,10 @@
 from builtins import str
 from hashlib import md5
 
-from qgis.PyQt.QtCore import Qt, pyqtSignal
-from qgis.PyQt.QtWidgets import QDialog, QWidget, QAction, QApplication, QInputDialog, QStyledItemDelegate, QTableWidgetItem
+import os
+
+from qgis.PyQt.QtCore import Qt, pyqtSignal, QDir
+from qgis.PyQt.QtWidgets import QDialog, QWidget, QAction, QApplication, QInputDialog, QStyledItemDelegate, QTableWidgetItem, QFileDialog
 from qgis.PyQt.QtGui import QKeySequence, QCursor, QClipboard, QIcon, QStandardItemModel, QStandardItem
 from qgis.PyQt.Qsci import QsciAPIs
 
@@ -116,6 +118,8 @@ def __init__(self, iface, db, parent=None):
         self.btnClear.clicked.connect(self.clearSql)
 
         self.presetStore.clicked.connect(self.storePreset)
+        self.presetSaveAsFile.clicked.connect(self.saveAsFilePreset)
+        self.presetLoadFile.clicked.connect(self.loadFilePreset)
         self.presetDelete.clicked.connect(self.deletePreset)
         self.presetCombo.activated[str].connect(self.loadPreset)
         self.presetCombo.activated[str].connect(self.presetName.setText)
@@ -224,6 +228,47 @@ def storePreset(self):
         else:
             self.presetCombo.setCurrentIndex(index)
 
+    def saveAsFilePreset(self):
+        settings = QgsSettings()
+        lastDir = settings.value('DB_Manager/lastDirSQLFIle', "")
+
+        query = self._getSqlQuery()
+        if query == "":
+            return
+
+        filename, _ = QFileDialog.getSaveFileName(
+            self,
+            self.tr('Save SQL Query'),
+            lastDir,
+            self.tr("SQL File (*.sql, *.SQL)"))
+
+        if filename:
+            if not filename.lower().endswith('.sql'):
+                filename += ".sql"
+
+            with open(filename, 'w') as f:
+                f.write(query)
+                lastDir = os.path.dirname(filename)
+                settings.setValue('DB_Manager/lastDirSQLFile', lastDir)
+
+    def loadFilePreset(self):
+        settings = QgsSettings()
+        lastDir = settings.value('DB_Manager/lastDirSQLFIle', "")
+
+        filename, _ = QFileDialog.getOpenFileName(
+            self,
+            self.tr("Load SQL Query"),
+            lastDir,
+            self.tr("SQL File (*.sql, *.SQL)"))
+
+        if filename:
+            with open(filename, 'r') as f:
+                self.editSql.clear()
+                for line in f:
+                    self.editSql.insertText(line)
+                lastDir = os.path.dirname(filename)
+                settings.setValue('DB_Manager/lastDirSQLFile', lastDir)
+
     def deletePreset(self):
         name = self.presetCombo.currentText()
         QgsProject.instance().removeEntry('DBManager', 'savedQueries/' + self.getQueryHash(name))

diff --git a/python/plugins/db_manager/ui/DlgSqlWindow.ui b/python/plugins/db_manager/ui/DlgSqlWindow.ui
@@ -25,7 +25,7 @@
      <property name="orientation">
       <enum>Qt::Vertical</enum>
      </property>
-     <widget class="QWidget" name="">
+     <widget class="QWidget" name="layoutWidget">
       <layout class="QVBoxLayout" name="verticalLayout_2">
        <item>
         <layout class="QHBoxLayout" name="horizontalLayout">
@@ -100,6 +100,26 @@
            </property>
           </widget>
          </item>
+         <item>
+          <widget class="QPushButton" name="presetLoadFile">
+           <property name="toolTip">
+            <string>&lt;html&gt;&lt;head/&gt;&lt;body&gt;&lt;p&gt;Load SQL file&lt;/p&gt;&lt;/body&gt;&lt;/html&gt;</string>
+           </property>
+           <property name="text">
+            <string>Load File</string>
+           </property>
+          </widget>
+         </item>
+         <item>
+          <widget class="QPushButton" name="presetSaveAsFile">
+           <property name="toolTip">
+            <string>&lt;html&gt;&lt;head/&gt;&lt;body&gt;&lt;p&gt;Save the query as SQL file&lt;/p&gt;&lt;/body&gt;&lt;/html&gt;</string>
+           </property>
+           <property name="text">
+            <string>Save As File</string>
+           </property>
+          </widget>
+         </item>
         </layout>
        </item>
        <item>
@@ -226,7 +246,7 @@
        </item>
       </layout>
      </widget>
-     <widget class="QWidget" name="">
+     <widget class="QWidget" name="layoutWidget">
       <layout class="QVBoxLayout" name="verticalLayout">
        <item>
         <widget class="QTableView" name="viewResult">
@@ -498,7 +518,6 @@ unique values</string>
   <tabstop>queryBuilderBtn</tabstop>
   <tabstop>presetCombo</tabstop>
   <tabstop>presetName</tabstop>
-  <tabstop>presetStore</tabstop>
   <tabstop>presetDelete</tabstop>
   <tabstop>editSql</tabstop>
   <tabstop>btnExecute</tabstop>