def __init__(self, iface):
# Save reference to the QGIS interface
self.iface = iface
# initialize plugin directory
self.plugin_dir = os.path.dirname(__file__)
# initialize locale
locale = QSettings().value("locale/userLocale")[0:2]
localePath = os.path.join(self.plugin_dir, 'i18n',
'postgisquerybuilder_{}.qm'.format(locale))
if os.path.exists(localePath):
self.translator = QTranslator()
self.translator.load(localePath)
if qVersion() > '4.3.3':
QCoreApplication.installTranslator(self.translator)
#self.dlg = uic.loadUi( os.path.join( os.path.dirname( os.path.abspath( __file__ ) ), "ui_postgisquerybuilder.ui" ) )
self.querySet = querySet()
self.PSQL = PSQL(self.iface)
class DAOPsql:
att = ("(id SERIAL PRIMARY KEY, "
"bbl bigint(20), "
"price FLOAT, qprice INTEGER, "
"start_date TIME NULL, end_date TIME NULL, "
"address TEXT)")
def __init__(self, db_name):
db = DataBase(db_name)
self.sql = PSQL(db)
def do(self, arg):
return self.sql.select(arg)
def __init__(self, iface):
"""Constructor.
:param iface: An interface instance that will be passed to this class
which provides the hook by which you can manipulate the QGIS
application at run time.
:type iface: QgsInterface
"""
# Save reference to the QGIS interface
self.iface = iface
# initialize plugin directory
self.plugin_dir = os.path.dirname(__file__)
# initialize locale
locale = QSettings().value('locale/userLocale')[0:2]
locale_path = os.path.join(
self.plugin_dir,
'i18n',
'GeoGet_{}.qm'.format(locale))
if os.path.exists(locale_path):
self.translator = QTranslator()
self.translator.load(locale_path)
if qVersion() > '4.3.3':
QCoreApplication.installTranslator(self.translator)
# Create the dialog (after translation) and keep reference
self.dlg = GeoGetDialog()
# Добавление кнопки "свернуть"
# http://stackoverflow.com/questions/22187207/pyqt-dialogs-minimize-window-button-is-missing-in-osx
self.dlg.setWindowFlags(Qt.WindowMinimizeButtonHint)
# Declare instance attributes
self.actions = []
self.menu = self.tr(u'&Innoter GeoGet')
# TODO: We are going to let the user set this up in a future iteration
self.toolbar = self.iface.addToolBar(u'GeoGet')
self.toolbar.setObjectName(u'GeoGet')
# подключение моих модулей
self.Geometry = Geomerty(self.iface)
self.PSQL = PSQL(self.iface)
self.Cloud_pct_control = SyncedSlider(self.dlg.cloud_pct_slider, self.dlg.cloud_pct_mx)
self.Angle_control = SyncedSlider(self.dlg.angle_slider, self.dlg.angle_mx)
# мои переменный
self.last_used_path = None
self.results_layer = None
def __init__(self, iface):
# Save reference to the QGIS interface
self.iface = iface
# initialize plugin directory
self.plugin_dir = os.path.dirname(__file__)
# initialize locale
locale = QSettings().value("locale/userLocale")[0:2]
localePath = os.path.join(self.plugin_dir, 'i18n', 'postgisquerybuilder_{}.qm'.format(locale))
if os.path.exists(localePath):
self.translator = QTranslator()
self.translator.load(localePath)
if qVersion() > '4.3.3':
QCoreApplication.installTranslator(self.translator)
#self.dlg = uic.loadUi( os.path.join( os.path.dirname( os.path.abspath( __file__ ) ), "ui_postgisquerybuilder.ui" ) )
self.querySet = querySet()
self.PSQL = PSQL(self.iface)
class GeoGet:
"""QGIS Plugin Implementation."""
def __init__(self, iface):
"""Constructor.
:param iface: An interface instance that will be passed to this class
which provides the hook by which you can manipulate the QGIS
application at run time.
:type iface: QgsInterface
"""
# Save reference to the QGIS interface
self.iface = iface
# initialize plugin directory
self.plugin_dir = os.path.dirname(__file__)
# initialize locale
locale = QSettings().value('locale/userLocale')[0:2]
locale_path = os.path.join(
self.plugin_dir,
'i18n',
'GeoGet_{}.qm'.format(locale))
if os.path.exists(locale_path):
self.translator = QTranslator()
self.translator.load(locale_path)
if qVersion() > '4.3.3':
QCoreApplication.installTranslator(self.translator)
# Create the dialog (after translation) and keep reference
self.dlg = GeoGetDialog()
# Добавление кнопки "свернуть"
# http://stackoverflow.com/questions/22187207/pyqt-dialogs-minimize-window-button-is-missing-in-osx
self.dlg.setWindowFlags(Qt.WindowMinimizeButtonHint)
# Declare instance attributes
self.actions = []
self.menu = self.tr(u'&Innoter GeoGet')
# TODO: We are going to let the user set this up in a future iteration
self.toolbar = self.iface.addToolBar(u'GeoGet')
self.toolbar.setObjectName(u'GeoGet')
# подключение моих модулей
self.Geometry = Geomerty(self.iface)
self.PSQL = PSQL(self.iface)
self.Cloud_pct_control = SyncedSlider(self.dlg.cloud_pct_slider, self.dlg.cloud_pct_mx)
self.Angle_control = SyncedSlider(self.dlg.angle_slider, self.dlg.angle_mx)
# мои переменный
self.last_used_path = None
self.results_layer = None
# noinspection PyMethodMayBeStatic
def tr(self, message):
"""Get the translation for a string using Qt translation API.
We implement this ourselves since we do not inherit QObject.
:param message: String for translation.
:type message: str, QString
:returns: Translated version of message.
:rtype: QString
"""
# noinspection PyTypeChecker,PyArgumentList,PyCallByClass
return QCoreApplication.translate('GeoGet', message)
def add_action(
self,
icon_path,
text,
callback,
enabled_flag=True,
add_to_menu=True,
add_to_toolbar=True,
status_tip=None,
whats_this=None,
parent=None):
"""Add a toolbar icon to the toolbar.
:param icon_path: Path to the icon for this action. Can be a resource
path (e.g. ':/plugins/foo/bar.png') or a normal file system path.
:type icon_path: str
:param text: Text that should be shown in menu items for this action.
:type text: str
:param callback: Function to be called when the action is triggered.
:type callback: function
:param enabled_flag: A flag indicating if the action should be enabled
by default. Defaults to True.
:type enabled_flag: bool
:param add_to_menu: Flag indicating whether the action should also
be added to the menu. Defaults to True.
:type add_to_menu: bool
:param add_to_toolbar: Flag indicating whether the action should also
be added to the toolbar. Defaults to True.
:type add_to_toolbar: bool
:param status_tip: Optional text to show in a popup when mouse pointer
hovers over the action.
:type status_tip: str
:param parent: Parent widget for the new action. Defaults None.
:type parent: QWidget
:param whats_this: Optional text to show in the status bar when the
mouse pointer hovers over the action.
:returns: The action that was created. Note that the action is also
added to self.actions list.
:rtype: QAction
"""
icon = QIcon(icon_path)
action = QAction(icon, text, parent)
action.triggered.connect(callback)
action.setEnabled(enabled_flag)
if status_tip is not None:
action.setStatusTip(status_tip)
if whats_this is not None:
action.setWhatsThis(whats_this)
if add_to_toolbar:
self.toolbar.addAction(action)
if add_to_menu:
self.iface.addPluginToDatabaseMenu(
self.menu,
action)
self.actions.append(action)
return action
def initGui(self):
"""Create the menu entries and toolbar icons inside the QGIS GUI."""
icon_path = ':/plugins/GeoGet/icon.png'
self.add_action(
icon_path,
text=str(self.tr(u'Innoter GeoGet')),
callback=self.run,
parent=self.iface.mainWindow())
# My GUI part
# Кнопка "Выгрузить"
self.dlg.in_browse_btn_2.clicked.connect(
lambda: self.set_output(obj_type='folder'))
# TODO выводить ошибку "слой с результатами не найден", если self.results_layer is None
self.dlg.pushButton.clicked.connect(
lambda: functions_os.extract2folder(
file_list=functions_qgis.get_values_from_layer(self.results_layer, 'path'),
dst_folder=self.dlg.lineEdit.text()).encode('utf-8').decode('utf-8'))
# TODO выполнять только по клику
self.populateComboBox(
self.dlg.proj_code_comboBox, self.PSQL.simpleQuery(), u'--- не учитывать ---', True)
self.populateComboBox(
self.dlg.v_layer_list, self.get_layer_names(), u'Выберите слой', True)
self.dlg.in_browse_btn.clicked.connect(self.select_input_file)
# слой выбран, переключаем текущую [currentIndex()] вкладку на следующую
self.dlg.v_layer_list.activated.connect(
lambda: self.dlg.parameters_toolBox.setCurrentIndex(int(self.dlg.parameters_toolBox.currentIndex()) + 1))
self.dlg.search_btn.clicked.connect(self.search_inner_db)
# !!!
# self.dlg.proj_code_comboBox = ComboBox(self.dlg)
# self.dlg.proj_code_comboBox.activated.connect(lambda: self.dlg.test_textBrowser.setText(str('Cool!')))
def populateGui(self):
"""Make the GUI live."""
# перепроецируем карту в Web Mercator
# if self.iface.mapCanvas().mapRenderer().hasCrsTransformEnabled():
# my_crs = QgsCoordinateReferenceSystem(3785, QgsCoordinateReferenceSystem.EpsgCrsId)
# self.iface.mapCanvas().mapRenderer().setDestinationCrs(my_crs)
# TODO удалить этот импорт
# lyr = self.iface.addVectorLayer(
# os.path.join(os.path.dirname(__file__), r"testData\test_polygon.shp"), 'Test_Polygon', 'ogr')
# lyr.loadNamedStyle(os.path.join(os.path.dirname(os.path.join(__file__)), 'testData', 'Test_Polygon_Style.qml'))
# TODO удалить этот тест-блок
self.dlg.tweak_qgis_chbox.toggled.connect(
lambda: (QSettings().setValue('/qgis/dockAttributeTable', True),
QSettings().setValue('/qgis/attributeTableBehaviour', 1)
))
# layer = self.Geometry.get_layer("test_poly")
# geometry = self.Geometry.get_geometry(layer)
# self.dlg.test_textBrowser.append(str(geometry))
# if str(self.dlg.v_layer_list.currentText()) != u'Выберите слой' or None:
self.dlg.v_layer_list.activated.connect(
lambda: self.zoom2layer(self.dlg.v_layer_list.currentText().encode('utf-8').decode('utf-8')))
# self.dlg.search_btn.clicked.connect(
# lambda: self.t_search_db(self.dlg.v_layer_list.currentText().encode('utf-8').decode('utf-8')))
def unload(self):
"""Removes the plugin menu item and icon from QGIS GUI."""
for action in self.actions:
self.iface.removePluginDatabaseMenu(
self.tr(u'&Innoter GeoGet'),
action)
self.iface.removeToolBarIcon(action)
# remove the toolbar
del self.toolbar
def run(self):
"""Run method that performs all the real work"""
self.populateGui()
# show the dialog
self.dlg.show()
# Run the dialog event loop
result = self.dlg.exec_()
# See if OK was pressed
if result:
# Do something useful here - delete the line containing pass and
# substitute with your code.
pass
def populateComboBox(self, combo, list, predef, sort):
# procedure to fill specified combobox with provided list
combo.blockSignals(True)
combo.clear()
model = QStandardItemModel(combo)
predefInList = None
for elem in list:
try:
item = QStandardItem(unicode(elem))
except TypeError:
item = QStandardItem(str(elem))
model.appendRow(item)
if elem == predef:
predefInList = elem
if sort:
model.sort(0)
combo.setModel(model)
if predef != "":
if predefInList:
combo.setCurrentIndex(combo.findText(predefInList))
else:
combo.insertItem(0, predef)
combo.setCurrentIndex(0)
# combo.model().item(0).setEnabled(False)
combo.model().item(0).setBackground(QBrush(QColor(200, 200, 200)))
combo.blockSignals(False)
def get_layers_list(self):
"""Возвращает список открытых векторных полигональных слоёв (QgsVectorLayer objects)"""
# http://gis.stackexchange.com/questions/124866/how-to-identify-polygon-point-shapefiles-in-python/124870#124870
vector_type_index = 0
polygon_type_index = 2
layers = [layer for layer in self.iface.legendInterface().layers() if
layer.type() == vector_type_index and layer.geometryType() == polygon_type_index]
return layers
def get_layer_names(self):
""" Загружаем список открытых слоёв в диалог "выбор контура (v_layer_list)."""
layer_list = []
layers = self.get_layers_list()
for layer in layers:
layer_path = layer.source()
# layer_name = os.path.basename(layer_path)
layer_list.append(layer_path)
# # TODO учесть, что есть это:
# layer_list.append(layer.name())
return layer_list
def select_input_file(self):
path = self.last_used_path if self.last_used_path is not None else ""
filename = QFileDialog.getOpenFileName(
self.dlg, u"Укажите файл контура ", path,
u'Полигоны (*.shp *.kml *tab *geojson)')
if not filename:
return None
else:
# TODO отображать в списке только имя контура без пути и расшир (реализовать через словарь? (т.к. нужен и путь)
self.dlg.v_layer_list.insertItem(self.dlg.v_layer_list.count(), filename)
self.dlg.v_layer_list.setCurrentIndex(self.dlg.v_layer_list.count() - 1)
# TODO лучше всего загружать слой в QGIS вместе с результатами
self.load_layer(filename, os.path.basename(filename), 'ogr')
# записываем в self.last_used_path последний использовавшийся каталог
self.last_used_path = os.path.dirname(filename)
def set_output(self, obj_type):
path = self.last_used_path if self.last_used_path is not None else ""
if obj_type == 'folder':
folder_path = QFileDialog.getExistingDirectory(
self.dlg, u"Выберите директорию", path).encode('utf-8').decode('utf-8')
if not folder_path:
return None
else:
self.dlg.lineEdit.setText(folder_path)
self.last_used_path = folder_path
def load_layer(self, path, name, data_provider):
lyr = self.iface.addVectorLayer(path, name.split('.')[0], data_provider)
# zoom to newly loaded layer
self.zoom2layer(lyr.source())
# слой выбран, переключаем текущую [currentIndex()] вкладку на следующую
self.dlg.parameters_toolBox.setCurrentIndex(int(self.dlg.parameters_toolBox.currentIndex()) + 1)
def zoom2layer(self, layer_path):
layers = self.get_layers_list()
for layer in layers:
if layer.source() == layer_path:
self.iface.setActiveLayer(layer)
self.iface.zoomToActiveLayer()
break
# def t_search_db(self, layer_path):
# # TODO разобраться, почему при выборе "очистить результаты" (после поиска без опции) удляются не все слои
# if self.dlg.clear_results_chbx.isChecked():
# layers = self.clear_results(self.get_layers_list())
# else:
# layers = [layer for layer in self.get_layers_list() if not layer.name().startswith('results_')]
# for layer in layers:
# if layer.source() == layer_path:
# lyr = self.Geometry.get_layer(layer_path)
# wkt = self.Geometry.get_geometry(lyr)
# # TODO добавить поддержку минимальной облачности (аккуратно, во внутр. БД есть -9999)
# sat_set = self.get_sat_set()
# dates_dict = self.get_date_range()
# stereo_flag = self.get_stereo_flag()
# sql = self.PSQL.querySet(
# dates_dict, self.Cloud_pct_control.get_mx_value(), self.Angle_control.get_mx_value(), sat_set,
# stereo_flag, wkt)
# result_layer_name = 'results_DG'
# self.PSQL.loadSql(result_layer_name, sql)
# # загрузка готового стиля для слоя с результатами
# # shape_lyr = self.iface.activeLayer()
# shape_lyr = None
# for lyr in QgsMapLayerRegistry.instance().mapLayers().values():
# if lyr.name() == result_layer_name:
# shape_lyr = lyr
# break
# # shape_lyr = QgsMapLayerRegistry.instance().mapLayerByName("results_DG")
# shape_lyr.loadNamedStyle(os.path.join(
# os.path.dirname(os.path.join(__file__)), 'testData', 'Shape_Style.qml'))
#
# if self.dlg.open_attr_form_chbox.isChecked():
# self.iface.showAttributeTable(shape_lyr)
# self.dlg.test_textBrowser.setText(str(self.get_date_range()))
# break
def search_inner_db(self):
# очищаем результаты предыдущего поиска, если выбрана опция "очистить результаты"
if self.dlg.clear_results_chbx.isChecked():
layers = self.clear_results(self.get_layers_list())
else:
layers = [layer for layer in self.get_layers_list() if not layer.name().startswith('results_inner_')]
order_desc_criteria = str(self.dlg.proj_code_comboBox.currentText())
sql = self.PSQL.querySet(
order_desc=order_desc_criteria)
result_layer_name = 'results_inner_DB'
# TODO удалить - тест
self.dlg.test_textBrowser.setText(str(sql))
#
self.results_layer = self.PSQL.loadSql(result_layer_name, sql)
layer_path = self.results_layer.source()
self.zoom2layer(layer_path)
def clear_results(self, layer_list):
"""Удаляет из реестра слоёв результаты предыдущего поиска"""
for layer in layer_list:
if layer.name().startswith('results_'):
QgsMapLayerRegistry.instance().removeMapLayer(layer.id())
layer_list.remove(layer)
return layer_list
def get_sat_set(self):
sat_set = set()
if self.dlg.WV3_chbox.isChecked():
sat_set.add('WV03')
if self.dlg.WV2_chbox.isChecked():
sat_set.add('WV02')
if self.dlg.WV1_chbox.isChecked():
sat_set.add('WV01')
if self.dlg.GE01_chbox.isChecked():
sat_set.add('GE01')
if self.dlg.QB01_chbox.isChecked():
sat_set.add('QB02')
return sat_set
def get_date_range(self):
dates_dict = {'min_date': str(self.dlg.min_dateEdit.date().toPyDate()),
'max_date': str(self.dlg.max_dateEdit.date().toPyDate())}
return dates_dict
def get_stereo_flag(self):
if self.dlg.stereo_chbox.isChecked():
stereo_flag = True
else:
stereo_flag = False
return stereo_flag
class postgisQueryBuilder:
def __init__(self, iface):
# Save reference to the QGIS interface
self.iface = iface
# initialize plugin directory
self.plugin_dir = os.path.dirname(__file__)
# initialize locale
locale = QSettings().value("locale/userLocale")[0:2]
localePath = os.path.join(self.plugin_dir, 'i18n', 'postgisquerybuilder_{}.qm'.format(locale))
if os.path.exists(localePath):
self.translator = QTranslator()
self.translator.load(localePath)
if qVersion() > '4.3.3':
QCoreApplication.installTranslator(self.translator)
self.dlg = postgisQueryBuilderDialog()
#self.dlg = uic.loadUi( os.path.join( os.path.dirname( os.path.abspath( __file__ ) ), "ui_postgisquerybuilder.ui" ) )
self.querySet = querySet()
self.PSQL = PSQL(self.iface)
def eventsConnect(self):
self.dlg.QueryType.activated.connect(self.setQueryType)
self.dlg.LAYERa.activated.connect(self.setLAYERa)
self.dlg.LAYERb.activated.connect(self.setLAYERb)
self.dlg.FIELD.activated.connect(self.setFIELD)
self.dlg.OPERATOR.activated.connect(self.setOPERATOR)
self.dlg.DISTANCEOP.activated.connect(self.setDISTANCEOP)
self.dlg.SPATIALREL.activated.connect(self.setSPATIALREL)
self.dlg.SPATIALRELNOT.stateChanged.connect(self.setSPATIALRELNOT)
self.dlg.checkCreateView.clicked.connect(self.checkCreateView)
self.dlg.BUFFERRADIUS.textChanged.connect(self.setBUFFERRADIUS)
self.dlg.CONDITION.activated.connect(self.setCONDITION)
self.dlg.CONDITION.editTextChanged.connect(self.setCONDITION)
self.dlg.DISTANCE.textChanged.connect(self.setDISTANCE)
self.dlg.ButtonRun.clicked.connect(self.runQuery)
self.dlg.ButtonReset.clicked.connect(self.resetForm)
self.dlg.ButtonClose.clicked.connect(self.closeDialog)
self.dlg.ButtonHelp.clicked.connect(self.helpDialog)
self.dlg.fieldsListA.clicked.connect(self.setFieldsList)
self.dlg.fieldsListB.clicked.connect(self.setFieldsList)
self.dlg.checkMaterialized.clicked.connect(self.setMaterialized)
self.dlg.AddToMapButton.clicked.connect(self.layerAddToMap)
self.dlg.GetInfoButton.clicked.connect(self.layerGetTable)
self.dlg.DeleteButton.clicked.connect(self.layerDelete)
self.dlg.RefreshButton.clicked.connect(self.layerRefresh)
self.dlg.convertToTableButton.clicked.connect(self.convertToTable)
self.dlg.JOIN.activated.connect(self.setJOIN)
self.dlg.FIELDb.activated.connect(self.setFIELDb)
self.dlg.tabWidget.currentChanged.connect(self.tabChangedHub)
self.dlg.KEYFIELD.textChanged.connect(self.keyGeomFieldsChanged)
self.dlg.GEOMETRYFIELD.textChanged.connect(self.keyGeomFieldsChanged)
self.dlg.queryReadyButton.clicked.connect(self.focusOnQuery)
self.dlg.selectAllFields.clicked.connect(self.selectAllFields)
def eventsDisconnect(self):
self.dlg.QueryType.activated.disconnect(self.setQueryType)
self.dlg.LAYERa.activated.disconnect(self.setLAYERa)
self.dlg.LAYERb.activated.disconnect(self.setLAYERb)
self.dlg.FIELD.activated.disconnect(self.setFIELD)
self.dlg.OPERATOR.activated.disconnect(self.setOPERATOR)
self.dlg.DISTANCEOP.activated.disconnect(self.setDISTANCEOP)
self.dlg.SPATIALREL.activated.disconnect(self.setSPATIALREL)
self.dlg.SPATIALRELNOT.stateChanged.disconnect(self.setSPATIALRELNOT)
self.dlg.checkCreateView.clicked.disconnect(self.checkCreateView)
self.dlg.BUFFERRADIUS.textChanged.disconnect(self.setBUFFERRADIUS)
self.dlg.CONDITION.activated.disconnect(self.setCONDITION)
self.dlg.CONDITION.editTextChanged.disconnect(self.setCONDITION)
self.dlg.DISTANCE.textChanged.disconnect(self.setDISTANCE)
self.dlg.ButtonRun.clicked.disconnect(self.runQuery)
self.dlg.ButtonReset.clicked.disconnect(self.resetForm)
self.dlg.ButtonClose.clicked.disconnect(self.closeDialog)
self.dlg.AddToMapButton.clicked.disconnect(self.layerAddToMap)
self.dlg.GetInfoButton.clicked.disconnect(self.layerGetTable)
self.dlg.DeleteButton.clicked.disconnect(self.layerDelete)
self.dlg.RefreshButton.clicked.disconnect(self.layerRefresh)
self.dlg.convertToTableButton.clicked.disconnect(self.convertToTable)
self.dlg.JOIN.activated.disconnect(self.setJOIN)
self.dlg.FIELDb.activated.disconnect(self.setFIELDb)
self.dlg.fieldsListA.clicked.disconnect(self.setFieldsList)
self.dlg.fieldsListB.clicked.disconnect(self.setFieldsList)
self.dlg.tabWidget.currentChanged.disconnect(self.tabChangedHub)
self.dlg.KEYFIELD.textChanged.disconnect(self.keyGeomFieldsChanged)
self.dlg.GEOMETRYFIELD.textChanged.disconnect(self.keyGeomFieldsChanged)
self.dlg.selectAllFields.clicked.disconnect(self.selectAllFields)
def initGui(self):
# Create action that will start plugin configuration
self.action = QAction(
QIcon(":/plugins/postgisquerybuilder/querybuilderlogo.png"),
u"postgisQueryBuilder", self.iface.mainWindow())
# connect the action to the run method
self.action.triggered.connect(self.run)
# Add toolbar button and menu item
self.iface.addToolBarIcon(self.action)
self.iface.addPluginToDatabaseMenu(u"&postgisQueryBuilder", self.action)
self.dlg.GEOMETRYFIELD.setText("the_geom")
self.dlg.KEYFIELD.setText("ogc_fid")
self.populateGui()
self.eventsConnect()
self.toTableDlg = convertToTableDialog(self)
def populateGui(self):
self.populateComboBox(self.dlg.QueryType,self.querySet.getQueryLabels(),"Select query type",True)
self.populateComboBox(self.dlg.OPERATOR,["=","<>",">","<","<=",">="],"Select",True)
self.populateComboBox(self.dlg.DISTANCEOP,["=","<>",">","<","<=",">="],"Select",True)
self.populateComboBox(self.dlg.JOIN,["INNER JOIN","CROSS JOIN","RIGHT OUTER JOIN","LEFT OUTER JOIN","FULL OUTER JOIN"],"Select",True)
self.populateComboBox(self.dlg.SPATIALREL,self.querySet.getSpatialRelationships(),"Select spatial relationship",True)
self.dlg.tabWidget.setCurrentIndex(0)
#self.recurseChild(self.dlg,"")
# def layerAddToMap(self):
# for rowList in range(0,self.dlg.LayerList.count()):
# rowCheckbox = self.dlg.LayerList.item(rowList)
# #take only selected attributes by checkbox
# if rowCheckbox.checkState() == Qt.Checked:
# self.PSQL.loadView(rowCheckbox.text(),self.dlg.GEOMETRYFIELD.text(),self.dlg.KEYFIELD.text())
# self.uncheckList(self.dlg.LayerList)
def layerAddToMap(self):
for rowSel in (self.dlg.LayerList.selectedItems()):
self.PSQL.loadView(rowSel.text(),self.dlg.GEOMETRYFIELD.text(),self.dlg.KEYFIELD.text())
def layerGetTable(self):
for rowSel in (self.dlg.LayerList.selectedItems()):
self.PSQL.tableResultGen(rowSel.text(),"",self.dlg.TableResult)
self.dlg.tabWidget.setCurrentIndex(6)
break
def exconvertToTable(self):
for rowSel in (self.dlg.LayerList.selectedItems()):
if self.PSQL.isMaterializedView(rowSel.text()) or self.PSQL.isView(rowSel.text()):
q = 'CREATE TABLE "%s"."%s" as (SELECT * FROM "%s"."%s");' % (self.PSQL.schema,rowSel.text()+"_totable",self.PSQL.schema,rowSel.text())
res = self.PSQL.submitCommand(q)
if res != "":
QMessageBox.information(None, "ERROR:", res)
def convertToTable(self):
for rowSel in (self.dlg.LayerList.selectedItems()):
if self.PSQL.isMaterializedView(rowSel.text()) or self.PSQL.isView(rowSel.text()):
self.toTableDlg.ask(rowSel.text())
def layerDelete(self):
for rowSel in (self.dlg.LayerList.selectedItems()):
msg = "Are you sure you want to delete layer '%s' from schema '%s' ?" % (rowSel.text(),self.PSQL.getSchema())
reply = QMessageBox.question(None, 'Message', msg, QMessageBox.Yes, QMessageBox.No)
if reply == QMessageBox.Yes:
result = self.PSQL.deleteLayer(rowSel.text())
#print result
if not (result == None or result == ""):
QMessageBox.information(None, "ERROR:", result)
else:
#print "DELETED", rowSel.text()
pass
self.populateLayerMenu()
def layerRefresh(self):
for rowSel in (self.dlg.LayerList.selectedItems()):
if self.PSQL.isMaterializedView(rowSel.text()):
self.PSQL.refreshMaterializedView(rowSel.text())
def recurseChild(self,slot,tab):
# for testing: prints qt object tree
for child in slot.children():
#print tab,"|",child.objectName()
if child.children() != []:
self.recurseChild(child,tab + " ")
def uncheckList(self,slot):
for row in range(0,slot.count()):
self.dlg.LayerList.item(row).setCheckState(Qt.Unchecked);
def disableDialogSlot(self,slot):
for child in self.dlg.tabWidget.widget(3).children():
if child.objectName() == slot:
child.setDisabled(True)
def hideDialogSlot(self,slot):
for child in self.dlg.tabWidget.widget(3).children():
if child.objectName() == slot:
child.hide()
def showDialogSlot(self,slot):
for child in self.dlg.tabWidget.widget(3).children():
if child.objectName() == slot:
child.show()
def enableDialogSlot(self,slot):
for child in self.dlg.tabWidget.widget(3).children():
if child.objectName() == slot:
child.setEnabled(True)
def clearDialogSlot(self,slot):
for child in self.dlg.tabWidget.widget(3).children():
if child.objectName() == slot:
child.clear()
def checkCreateView(self):
#method called when checkbox createview is clicked
if self.dlg.checkCreateView.checkState():
self.dlg.QueryName.setEnabled(True)
self.dlg.checkMaterialized.setEnabled(True)
else:
self.dlg.QueryName.setDisabled(True)
self.dlg.checkMaterialized.setDisabled(True)
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def helpDialog(self):
if self.dlg.QueryType.currentText() != "Select query type":
webbrowser.open_new(self.querySet.getHelp())
def setMaterialized(self):
self.queryGen()
def keyGeomFieldsChanged(self):
self.querySet.setParameter("GEOMETRYFIELD",self.dlg.GEOMETRYFIELD.text())
self.querySet.setParameter("KEYFIELD",self.dlg.KEYFIELD.text())
test = None
try:
test = self.querySet.testQueryParametersCheckList()
except:
pass
if test:
self.queryGen()
def tabChangedHub(self,tab):
#print "TAB:",tab
if tab == 1:
try:
self.updateLayerMenu()
except:
pass
if tab == 3:
self.keyGeomFieldsChanged()
if tab == 5:
self.queryGen()
if tab == 4:
self.updateOrderBy()
elif tab == 7:
self.updateHistoryLog()
def updateOrderBy(self):
if self.dlg.LAYERa.currentText()[:6] != "Select":
try:
self.populateComboBox(self.dlg.orderBy,self.PSQL.getFieldsContent(self.dlg.LAYERa.currentText())," ",True)
except:
pass
def focusOnQuery(self):
self.dlg.tabWidget.setCurrentIndex(5)
def updateHistoryLog(self):
historyFile = os.path.join(os.path.dirname(__file__),"validSql.log")
if os.path.exists(historyFile):
in_file = open(historyFile,"r")
self.dlg.historyLog.setPlainText(in_file.read())
self.dlg.historyLog.moveCursor(QTextCursor.End)
def populateComboBox(self,combo,list,predef,sort):
#procedure to fill specified combobox with provided list
combo.clear()
model=QStandardItemModel(combo)
for elem in list:
try:
item = QStandardItem(unicode(elem))
except TypeError:
item = QStandardItem(str(elem))
model.appendRow(item)
if sort:
model.sort(0)
combo.setModel(model)
if predef != "":
combo.insertItem(0,predef)
combo.setCurrentIndex(0)
def loadSVG(self,svgName):
svgFile = os.path.join( os.path.dirname( os.path.abspath( __file__ ) ), "svg",svgName + ".svg")
#print svgFile
item = QGraphicsSvgItem(svgFile)
scene= QGraphicsScene()
scene.addItem(item)
self.dlg.DiagPanel.setScene(scene)
self.dlg.DiagPanel.fitInView(item,Qt.KeepAspectRatio) #,Qt.KeepAspectRatio
def setLAYERa(self):
#called when LAYERa is activated
if self.dlg.LAYERa.currentText()[:6] == "Select":
return
self.querySet.setParameter("LAYERa",self.dlg.LAYERa.currentText())
if self.querySet.testQueryParametersCheckList():
self.queryGen()
self.populateComboBox(self.dlg.FIELD,self.PSQL.getFieldsContent(self.dlg.LAYERa.currentText()),"Select field",True)
if not self.PSQL.testIfFieldExist(self.dlg.LAYERa.currentText(),self.querySet.getParameter("KEYFIELD")):
self.querySet.setFIDFIELD()
self.addListToFieldTable(self.dlg.fieldsListA,self.PSQL.getFieldsContent(self.dlg.LAYERa.currentText()),True)
self.populateFilterTable()
def selectAllFields(self):
for row in range(0,self.dlg.fieldsListA.count()):
self.dlg.fieldsListA.item(row).setCheckState(self.dlg.selectAllFields.checkState())
def setLAYERb(self):
#called when LAYERb is activated
if self.dlg.LAYERb.currentText()[:6] == "Select":
return
self.querySet.setParameter("LAYERb",self.dlg.LAYERb.currentText())
if self.querySet.testQueryParametersCheckList():
self.queryGen()
self.addListToFieldTable(self.dlg.fieldsListB,self.PSQL.getFieldsContent(self.dlg.LAYERb.currentText()),True)
self.populateComboBox(self.dlg.FIELDb,self.PSQL.getFieldsContent(self.dlg.LAYERb.currentText()),"Select field",True)
def addListToFieldTable(self,fieldSlot,fl,check):
#called to populate field list for WHERE statement
wdgt=fieldSlot
wdgt.clear()
#print "LAYERLIST:",check
for row in fl:
item=QListWidgetItem()
if check == True:
item.setFlags(item.flags() | Qt.ItemIsUserCheckable)
item.setCheckState(Qt.Unchecked)
item.setText(row)
#print row
if self.PSQL.isTable(row):
item.setIcon(QIcon(":/plugins/postgisquerybuilder/iT.png"))
elif self.PSQL.isView(row):
item.setIcon(QIcon(":/plugins/postgisquerybuilder/iV.png"))
elif self.PSQL.isMaterializedView(row):
item.setIcon(QIcon(":/plugins/postgisquerybuilder/iM.png"))
#exclude geometryfield from user options when postgis query
if self.geoQuery and row == self.querySet.getParameter("GEOMETRYFIELD"):
pass
#item.setFlags(item.flags() ^ Qt.ItemIsEnabled)
else:
wdgt.addItem(item)
#item.setFlags(item.flags() | Qt.ItemIsEnabled)
def setFieldsList(self):
# procedure to resume selected fields to SELECT statements
items = []
for index in xrange(self.dlg.fieldsListA.count()):
if self.dlg.fieldsListA.item(index).checkState() == Qt.Checked:
items.append('"'+self.dlg.LAYERa.currentText()+'"."'+self.dlg.fieldsListA.item(index).text()+'"')
for index in xrange(self.dlg.fieldsListB.count()):
if self.dlg.fieldsListB.item(index).checkState() == Qt.Checked:
items.append('"'+self.dlg.LAYERb.currentText()+'"."'+self.dlg.fieldsListB.item(index).text()+'"')
#print "SELECTED FIELDS",items
self.querySet.setFieldsSet(items)
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setFIELD(self):
if self.dlg.FIELD.currentText()[:6] == "Select":
return
self.querySet.setParameter("FIELD",'"'+self.dlg.LAYERa.currentText()+'"."'+self.dlg.FIELD.currentText()+'"')
self.querySet.setParameter("SIMPLEFIELD",self.dlg.FIELD.currentText())
fType = self.PSQL.getFieldsType(self.querySet.getParameter("LAYERa"),self.dlg.FIELD.currentText())
fType = fType[:4]
#print fType
if ((fType == "char") or (fType == "text") or (fType == "varc")):
self.tDelimiter = "'"
else:
self.tDelimiter = ""
self.populateComboBox(self.dlg.OPERATOR,["=","<>",">","<","<=",">="],"Select",True)
self.populateComboBox(self.dlg.CONDITION,self.PSQL.getUniqeValues(self.querySet.getParameter("LAYERa"),self.dlg.FIELD.currentText(),100),"",True)
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setFIELDb(self):
if self.dlg.FIELDb.currentText()[:6] == "Select":
return
self.querySet.setParameter("FIELDb",'"'+self.dlg.LAYERb.currentText()+'"."'+self.dlg.FIELDb.currentText()+'"')
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setDISTANCEOP(self):
if self.dlg.DISTANCEOP.currentText()[:6] == "Select":
return
self.querySet.setParameter("DISTANCEOP",self.dlg.DISTANCEOP.currentText())
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setDISTANCE(self):
self.querySet.setParameter("DISTANCE",self.dlg.DISTANCE.text())
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setOPERATOR(self):
if self.dlg.OPERATOR.currentText()[:6] == "Select":
return
self.querySet.setParameter("OPERATOR",self.dlg.OPERATOR.currentText())
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setSPATIALREL(self):
if self.dlg.SPATIALREL.currentText()[:6] == "Select":
return
self.querySet.setParameter("SPATIALREL",self.dlg.SPATIALREL.currentText())
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setBUFFERRADIUS(self):
self.querySet.setParameter("BUFFERRADIUS",self.dlg.BUFFERRADIUS.text())
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setCONDITION(self):
self.querySet.setParameter("CONDITION",self.tDelimiter+self.dlg.CONDITION.currentText()+self.tDelimiter)
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setCONDITIONtext(self):
self.querySet.setParameter("CONDITION",self.dlg.CONDITION.currentText())
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setSPATIALRELNOT(self):
if self.dlg.SPATIALRELNOT.isChecked():
self.querySet.setParameter("SPATIALRELNOT","NOT ")
else:
self.querySet.setParameter("SPATIALRELNOT"," ")
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setJOIN(self):
self.querySet.setParameter("JOIN",self.dlg.JOIN.currentText())
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setQueryName(self):
self.querySet.setParameter("VIEWNAME",self.dlg.QueryName.text())
if self.querySet.testQueryParametersCheckList():
self.dlg.QueryResult.setPlainText(self.querySet.getQueryParsed(self.dlg.checkCreateView.checkState()))
def queryGen(self):
if self.dlg.checkCreateView.checkState():
self.enableDialogSlot("QueryName")
if self.dlg.checkMaterialized.checkState():
self.querySet.setParameter("MATERIALIZED","MATERIALIZED")
else:
self.querySet.setParameter("MATERIALIZED","")
self.enableDialogSlot("QueryResult")
self.enableDialogSlot("checkCreateView")
self.enableDialogSlot("AddToMap")
self.dlg.queryReadyButton.show()
try:
qName = self.querySet.getNameParsed()
except:
return
self.dlg.QueryName.setText(qName)
self.querySet.setParameter("VIEWNAME", qName)
if self.dlg.filterTable.testIfSintaxOk():
self.querySet.setParameter("WHERE", self.dlg.filterTable.getWhereStatement())
else:
self.querySet.setParameter("WHERE", "")
if self.dlg.orderBy.currentText() != " ":
self.querySet.setParameter("ORDERBY", 'ORDER BY "'+self.dlg.orderBy.currentText()+'"')
else:
self.querySet.setParameter("ORDERBY", "")
self.dlg.QueryResult.setPlainText(self.querySet.getQueryParsed(self.dlg.checkCreateView.checkState()))
self.dlg.QueryName.textChanged.connect(self.setQueryName)
def setQueryType(self,line):
theQ = self.dlg.QueryType.currentText()
self.loadSVG(theQ.replace(" ","_"))
if theQ[:6] == "Select":
return
#self.querySet.resetParameters()
self.resetDialog()
self.querySet.setCurrentQuery(theQ)
if theQ[:2]=="ST":
self.geoQuery = True
else:
self.geoQuery = None
for slot in self.querySet.getRequiredSlots():
#print slot
self.enableDialogSlot(slot)
self.showDialogSlot(slot)
self.showDialogSlot(slot+"Label")
#print self.querySet.testQueryParametersCheckList()
#simulate click on checkbox to set required slot
self.dlg.SPATIALRELNOT.setCheckState(Qt.Checked)
self.dlg.SPATIALRELNOT.setCheckState(Qt.Unchecked)
self.dlg.Helper.setText(theQ+":\n"+self.querySet.getDescription())
self.dlg.Helper2.setText(self.querySet.getDescription())
self.loadSVG(theQ.replace(" ","_"))
def resetDialog(self):
self.eventsDisconnect()
self.clearAllDialogs()
self.querySet.resetParameters()
self.dlg.queryReadyButton.hide()
self.hideDialogSlot("queryReadyButton")
self.disableQueryDefSlot()
self.hideQueryDefSlot()
self.loadSVG("Select_query_type")
try:
tables = self.PSQL.getLayers()
except AttributeError:
pass
else:
self.populateComboBox(self.dlg.LAYERa,tables,"Select Layer",True)
self.populateComboBox(self.dlg.LAYERb,tables,"Select Layer",True)
self.geoQuery = None
self.populateComboBox(self.dlg.orderBy,[" "],"",True)
self.eventsConnect()
def hideQueryDefSlot(self):
toHide=["BUFFERRADIUS","FIELD","OPERATOR","CONDITION",\
"SPATIALREL","SPATIALRELNOT","LAYERaLabel","BUFFERRADIUSLabel",\
"FIELDLabel","OPERATORLabel","CONDITIONLabel","SPATIALRELLabel",
"SPATIALRELNOTLabel","fieldsListALabel","fieldsListBLabel",\
"DISTANCEOP","DISTANCE","DISTANCEOPLabel","DISTANCELabel","FIELDb","FIELDbLabel","JOIN","JOINLabel"]
for slot in toHide:
self.hideDialogSlot(slot)
def clearQueryDefSlot(self):
toClear=["LAYERa","LAYERb","BUFFERRADIUS","FIELD","FIELDb",\
"OPERATOR","CONDITION","SPATIALREL","fieldsListA","fieldsListB",\
"DISTANCEOP","DISTANCE","JOIN"]
for slot in toClear:
self.clearDialogSlot(slot)
def disableQueryDefSlot(self):
toDisable=["LAYERa","LAYERb","BUFFERRADIUS","FIELD","FIELDb",\
"OPERATOR","CONDITION","SPATIALREL",\
"SPATIALRELNOT","fieldsListA","fieldsListB",
"DISTANCEOP","DISTANCE","JOIN"]
for slot in toDisable:
self.disableDialogSlot(slot)
def clearAllDialogs(self):
self.dlg.LAYERa.clear()
self.dlg.LAYERb.clear()
self.dlg.BUFFERRADIUS.clear()
self.dlg.FIELD.clear()
self.dlg.CONDITION.clear()
self.dlg.QueryResult.clear()
self.dlg.QueryName.clear()
self.dlg.fieldsListA.clear()
self.dlg.fieldsListB.clear()
self.dlg.TableResult.clear()
self.dlg.SPATIALRELNOT.setCheckState(Qt.Unchecked)
self.dlg.DISTANCE.clear()
def getPSQLConnections(self):
conn = self.PSQL.getConnections()
self.populateComboBox(self.dlg.PSQLConnection,conn,"Select connection",True)
self.hideQueryDefSlot()
self.dlg.queryReadyButton.hide()
self.dlg.PSQLConnection.activated.connect(self.setConnection)
def closeDialog(self):
self.resetDialog()
self.dlg.hide()
def resetForm(self):
self.resetDialog()
self.populateGui()
self.dlg.tabWidget.setCurrentIndex(1)
def setConnection(self):
self.PSQL.setConnection(self.dlg.PSQLConnection.currentText())
#print "SCHEMAS",self.PSQL.getSchemas()
schemas = self.PSQL.getSchemas()
self.populateComboBox(self.dlg.DBSchema,schemas,"Select schema",True)
self.dlg.DBSchema.activated.connect(self.loadPSQLLayers)
for r in range (0,self.dlg.DBSchema.count()):
if self.dlg.DBSchema.itemText(r) == "public":
self.dlg.DBSchema.setCurrentIndex(r)
self.dlg.DBSchema.removeItem(0)
self.loadPSQLLayers()
def loadPSQLLayers(self):
if self.dlg.DBSchema.currentText() != "Select schema":
self.PSQL.setSchema(self.dlg.DBSchema.currentText())
self.populateGui()
self.resetDialog()
#self.dlg.tabWidget.setCurrentIndex(1)
self.querySet.setSchema(self.dlg.DBSchema.currentText())
self.populateLayerMenu()
def populateFilterTable(self):
self.dlg.filterTable.populateFilterTable(self.PSQL,self.dlg.LAYERa.currentText())
def testSignal(self,v1,v2):
#print "catch:", v1,v2
pass
def populateLayerMenu(self):
self.addListToFieldTable(self.dlg.LayerList,self.PSQL.getLayers(),None)
def updateLayerMenu(self):
if self.dlg.DBSchema.currentText() != "Select schema":
self.addListToFieldTable(self.dlg.LayerList,self.PSQL.getLayers(),None)
def runQuery(self):
#method to run generated query
if self.dlg.filterTable.testIfSintaxOk():
if self.dlg.AddToMap.checkState():
if self.dlg.checkCreateView.checkState():
self.PSQL.submitQuery(self.querySet.getParameter("VIEWNAME"),self.dlg.QueryResult.toPlainText())
self.PSQL.loadView(self.querySet.getParameter("VIEWNAME"),self.querySet.getParameter("GEOMETRYFIELD"),self.querySet.getParameter("KEYFIELD"))
else:
self.PSQL.loadSql(self.querySet.getParameter("VIEWNAME"),self.dlg.QueryResult.toPlainText(),self.querySet.getParameter("GEOMETRYFIELD"),self.querySet.getParameter("KEYFIELD"))
else:
rows = self.PSQL.tableResultGen(self.dlg.LAYERa.currentText(),self.dlg.QueryResult.toPlainText(),self.dlg.TableResult)
self.dlg.labelRowsNumber.setText("Total rows: "+str(rows))
self.dlg.tabWidget.setCurrentIndex(6)
else:
QMessageBox.information(None, "FILTER ERROR:", "The Filter table is malformed")
self.dlg.tabWidget.setCurrentIndex(4)
def unload(self):
# Remove the plugin menu item and icon
self.iface.removePluginMenu(u"&postgisQueryBuilder", self.action)
self.iface.removeToolBarIcon(self.action)
# run method that performs all the real work
def run(self):
# show the dialog
self.dlg.show()
self.getPSQLConnections()
# Run the dialog event loop
result = self.dlg.exec_()
# See if OK was pressed
if result == 1:
# do something useful (delete the line containing pass and
# substitute with your code)
pass
class postgisQueryBuilder:
def __init__(self, iface):
# Save reference to the QGIS interface
self.iface = iface
# initialize plugin directory
self.plugin_dir = os.path.dirname(__file__)
# initialize locale
locale = QSettings().value("locale/userLocale")[0:2]
localePath = os.path.join(self.plugin_dir, 'i18n',
'postgisquerybuilder_{}.qm'.format(locale))
if os.path.exists(localePath):
self.translator = QTranslator()
self.translator.load(localePath)
if qVersion() > '4.3.3':
QCoreApplication.installTranslator(self.translator)
#self.dlg = uic.loadUi( os.path.join( os.path.dirname( os.path.abspath( __file__ ) ), "ui_postgisquerybuilder.ui" ) )
self.querySet = querySet()
self.PSQL = PSQL(self.iface)
def eventsConnect(self):
self.dlg.QueryType.activated.connect(self.setQueryType)
self.dlg.checkAutoCompiled.stateChanged.connect(self.queryGen)
self.dlg.LAYERa.activated.connect(self.setLAYERa)
self.dlg.SCHEMAb.activated.connect(self.setSCHEMAb)
self.dlg.FILTERSCHEMA.activated.connect(self.setFILTERSCHEMA)
self.dlg.LAYERb.activated.connect(self.setLAYERb)
self.dlg.FIELD.activated.connect(self.setFIELD)
self.dlg.SPATIALREL.activated.connect(self.setSPATIALREL)
self.dlg.SPATIALRELNOT.stateChanged.connect(self.setSPATIALRELNOT)
self.dlg.checkCreateView.clicked.connect(self.checkCreateView)
self.dlg.BUFFERRADIUS.textChanged.connect(self.setBUFFERRADIUS)
self.dlg.ButtonRun.clicked.connect(self.runQuery)
self.dlg.ButtonReset.clicked.connect(self.resetForm)
self.dlg.ButtonHelp.clicked.connect(self.helpDialog)
self.dlg.fieldsListA.clicked.connect(self.setFieldsList)
self.dlg.fieldsListB.clicked.connect(self.setFieldsList)
self.dlg.checkMaterialized.clicked.connect(self.setMaterialized)
#self.dlg.AddToMapButton.clicked.connect(self.layerAddToMap)
#self.dlg.GetInfoButton.clicked.connect(self.layerGetTable)
#self.dlg.DeleteButton.clicked.connect(self.layerDelete)
#self.dlg.RefreshButton.clicked.connect(self.layerRefresh)
#self.dlg.convertToTableButton.clicked.connect(self.convertToTable)
self.dlg.JOIN.activated.connect(self.setJOIN)
self.dlg.FIELDb.activated.connect(self.setFIELDb)
self.dlg.tabWidget.currentChanged.connect(self.tabChangedHub)
self.dlg.KEYFIELD.activated.connect(self.keyGeomFieldsChanged)
self.dlg.KEYFIELD.editTextChanged.connect(self.keyGeomFieldsChanged)
self.dlg.GEOMETRYFIELD.activated.connect(self.keyGeomFieldsChanged)
self.dlg.GEOMETRYFIELD.editTextChanged.connect(
self.keyGeomFieldsChanged)
self.dlg.LAYERaAllFields.clicked.connect(self.selectAllFields)
self.dlg.LayerList.itemDoubleClicked.connect(self.useForQuery)
self.dlg.LayerList.customContextMenuRequested.connect(
self.layerContextMenu)
self.dlg.LayerList.itemSelectionChanged.connect(self.saveForQuery)
self.dlg.schemaAdd.clicked.connect(self.addNewSchema)
self.dlg.HelpLink.clicked.connect(self.helpLinkOpen)
def eventsDisconnect(self):
self.dlg.QueryType.activated.disconnect(self.setQueryType)
self.dlg.checkAutoCompiled.stateChanged.disconnect(self.queryGen)
self.dlg.LAYERa.activated.disconnect(self.setLAYERa)
self.dlg.SCHEMAb.activated.disconnect(self.setSCHEMAb)
self.dlg.FILTERSCHEMA.activated.disconnect(self.setFILTERSCHEMA)
self.dlg.LAYERb.activated.disconnect(self.setLAYERb)
self.dlg.FIELD.activated.disconnect(self.setFIELD)
self.dlg.SPATIALREL.activated.disconnect(self.setSPATIALREL)
self.dlg.SPATIALRELNOT.stateChanged.disconnect(self.setSPATIALRELNOT)
self.dlg.checkCreateView.clicked.disconnect(self.checkCreateView)
self.dlg.BUFFERRADIUS.textChanged.disconnect(self.setBUFFERRADIUS)
self.dlg.ButtonRun.clicked.disconnect(self.runQuery)
self.dlg.ButtonReset.clicked.disconnect(self.resetForm)
self.dlg.ButtonHelp.clicked.disconnect(self.helpDialog)
#self.dlg.AddToMapButton.clicked.disconnect(self.layerAddToMap)
#self.dlg.GetInfoButton.clicked.disconnect(self.layerGetTable)
#self.dlg.DeleteButton.clicked.disconnect(self.layerDelete)
#self.dlg.RefreshButton.clicked.disconnect(self.layerRefresh)
#self.dlg.convertToTableButton.clicked.disconnect(self.convertToTable)
self.dlg.JOIN.activated.disconnect(self.setJOIN)
self.dlg.FIELDb.activated.disconnect(self.setFIELDb)
self.dlg.fieldsListA.clicked.disconnect(self.setFieldsList)
self.dlg.fieldsListB.clicked.disconnect(self.setFieldsList)
self.dlg.tabWidget.currentChanged.disconnect(self.tabChangedHub)
self.dlg.KEYFIELD.activated.disconnect(self.keyGeomFieldsChanged)
self.dlg.KEYFIELD.editTextChanged.disconnect(self.keyGeomFieldsChanged)
self.dlg.GEOMETRYFIELD.activated.disconnect(self.keyGeomFieldsChanged)
self.dlg.GEOMETRYFIELD.editTextChanged.disconnect(
self.keyGeomFieldsChanged)
self.dlg.LAYERaAllFields.clicked.disconnect(self.selectAllFields)
self.dlg.LayerList.itemDoubleClicked.disconnect(self.useForQuery)
self.dlg.LayerList.customContextMenuRequested.disconnect(
self.layerContextMenu)
self.dlg.LayerList.itemSelectionChanged.disconnect(self.saveForQuery)
self.dlg.schemaAdd.clicked.disconnect(self.addNewSchema)
self.dlg.HelpLink.clicked.disconnect(self.helpLinkOpen)
def initGui(self):
# Create action that will start plugin configuration
self.action = QAction(
QIcon(":/plugins/postgisquerybuilder/querybuilderlogo.png"),
u"postgisQueryBuilder", self.iface.mainWindow())
# connect the action to the run method
self.action.triggered.connect(self.run)
# Add toolbar button and menu item
self.iface.addToolBarIcon(self.action)
self.iface.addPluginToDatabaseMenu(u"&postgisQueryBuilder",
self.action)
#setup Docked widget
self.dlg = postgisQueryBuilderDialog()
self.PQBdockwidget = QDockWidget("postgisQueryBuilder",
self.iface.mainWindow())
self.PQBdockwidget.setObjectName("postgisQueryBuilder")
self.PQBdockwidget.setWidget(self.dlg)
self.PQBdockwidget.setAllowedAreas(Qt.RightDockWidgetArea
| Qt.LeftDockWidgetArea)
self.iface.addDockWidget(Qt.RightDockWidgetArea, self.PQBdockwidget)
#defaults
#self.dlg.GEOMETRYFIELD.setText("the_geom")
#self.dlg.KEYFIELD.setText("ogc_fid")
self.predefinedLayer = None
#hide Temp slots
self.dlg.USERFIELD.hide()
self.dlg.USERFIELDLabel.hide()
self.dlg.PASSWORDFIELD.hide()
self.dlg.PASSWORDFIELDLabel.hide()
#init
self.getPSQLConnections()
self.populateGui()
self.eventsConnect()
self.toTableDlg = convertToTableDialog(self)
def populateGui(self):
self.populateComboBox(self.dlg.QueryType,
self.querySet.getQueryLabels(),
"Select query type", True)
self.populateComboBox(self.dlg.JOIN, [
"INNER JOIN", "CROSS JOIN", "RIGHT OUTER JOIN", "LEFT OUTER JOIN",
"FULL OUTER JOIN"
], "Select", True)
self.populateComboBox(self.dlg.SPATIALREL,
self.querySet.getSpatialRelationships(),
"Select spatial relationship", True)
self.populateComboBox(
self.dlg.KEYFIELD,
["ogc_fid", "id", "fid", "gid", "FID", "GID", "ID"], "", None)
self.populateComboBox(self.dlg.GEOMETRYFIELD,
["the_geom", "geom", "GEOM", "geometry"], "",
None)
self.dlg.checkAutoCompiled.setChecked(True)
self.dlg.tabWidget.setCurrentIndex(0)
#self.recurseChild(self.dlg,"")
# def layerAddToMap(self):
# for rowList in range(0,self.dlg.LayerList.count()):
# rowCheckbox = self.dlg.LayerList.item(rowList)
# #take only selected attributes by checkbox
# if rowCheckbox.checkState() == Qt.Checked:
# self.PSQL.loadView(rowCheckbox.text(),self.dlg.GEOMETRYFIELD.text(),self.dlg.KEYFIELD.text())
# self.uncheckList(self.dlg.LayerList)
def layerForQuery(self, listItem):
print listItem
def layerContextMenu(self, listItem):
self.predefinedLayer = None
for rowSel in (self.dlg.LayerList.selectedItems()):
self.selectedLayer = rowSel.text()
contextMenu = QMenu()
self.layerNameAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","useForQuery.png")),\
self.selectedLayer)
contextMenu.addSeparator()
self.countFeatureAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","useForQuery.png")),\
"Features Count: "+self.PSQL.getFeatureCount(self.selectedLayer))
self.relationTypeAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","useForQuery.png")),\
"Relation Type: "+self.PSQL.getRelationType(self.selectedLayer))
self.geometryTypeAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","useForQuery.png")),\
"Geometry Type: "+self.PSQL.getGeometryType(\
self.selectedLayer,suggestion = self.dlg.GEOMETRYFIELD.currentText()))
self.autoKeyAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","useForQuery.png")),\
"Detected key field: %s" % self.PSQL.guessKeyField(\
self.selectedLayer,suggestion = self.dlg.KEYFIELD.currentText()) or "undefined")
self.autoGeometryAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","useForQuery.png")),\
"Detected geom field: %s" % self.PSQL.guessGeometryField(\
self.selectedLayer,suggestion = self.dlg.GEOMETRYFIELD.currentText()) or "undefined")
self.nativeSRIDAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","useForQuery.png")),\
"Native SRID: EPSG:"+self.PSQL.getSRID(\
self.selectedLayer,suggestion = self.dlg.GEOMETRYFIELD.currentText()))
contextMenu.addSeparator()
self.useForQueryAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","useForQuery.png")),\
"Use for query")
self.useForQueryAction.triggered.connect(self.useForQuery)
self.layerAddToMapAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","addToLayer.png")),\
"Add layer to map canvas")
self.layerAddToMapAction.triggered.connect(self.layerAddToMap)
self.probeKeyGeomAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","probeKeyGeom.png")),\
"Auto detect primary key and geometry fields")
self.probeKeyGeomAction.triggered.connect(self.probeKeyGeom)
self.layerGetTableAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","layerGetTable.png")),\
"View as data table")
self.layerGetTableAction.triggered.connect(self.layerGetTable)
self.renameObjectAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","renameObject.png")),\
"Rename")
self.renameObjectAction.triggered.connect(self.renameObject)
self.layerDeleteAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","layerDelete.png")),\
"Delete")
self.layerDeleteAction.triggered.connect(self.layerDelete)
self.moveObjectAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","moveObject.png")),\
"Move to another schema")
self.moveObjectAction.triggered.connect(self.moveObject)
if self.PSQL.isView(
self.selectedLayer) or self.PSQL.isMaterializedView(
self.selectedLayer):
self.convertToTableAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","convertToTable.png")),\
"Convert view to table")
self.convertToTableAction.triggered.connect(self.convertToTable)
if self.PSQL.isView(self.selectedLayer):
self.editViewAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","layerDelete.png")),\
"Edit view definition")
self.editViewAction.triggered.connect(self.editViewDefinition)
if self.PSQL.isMaterializedView(self.selectedLayer):
self.layerRefreshAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","layerRefresh.png")),\
"Refresh materialized view")
self.layerRefreshAction.triggered.connect(self.layerRefresh)
contextMenu.exec_(QCursor.pos())
def editViewDefinition(self):
sqlView = 'CREATE OR REPLACE VIEW "%s"."%s" AS ' % (
self.PSQL.getSchema(), self.selectedLayer) + self.PSQL.getViewDef(
self.selectedLayer)
self.resetDialog()
self.dlg.QueryName.setText(self.selectedLayer)
self.dlg.checkCreateView.setChecked(True)
self.dlg.AddToMap.setChecked(False)
if self.PSQL.isMaterializedView(self.selectedLayer):
self.dlg.checkMaterialized.setChecked(True)
self.dlg.checkAutoCompiled.setChecked(False)
self.dlg.QueryResult.setPlainText(
self.querySet.formatSqlStatement(sqlView))
self.dlg.tabWidget.setCurrentIndex(3)
def useForQuery(self):
self.predefinedLayer = self.selectedLayer
self.resetDialog()
self.dlg.tabWidget.setCurrentIndex(1)
def saveForQuery(self):
for rowSel in (self.dlg.LayerList.selectedItems()):
self.selectedLayer = rowSel.text()
self.predefinedLayer = self.selectedLayer
def layerAddToMap(self):
keyGuess = self.PSQL.guessKeyField(
self.selectedLayer, suggestion=self.dlg.KEYFIELD.currentText())
geomGuess = self.PSQL.guessGeometryField(
self.selectedLayer,
suggestion=self.dlg.GEOMETRYFIELD.currentText())
self.PSQL.loadView(self.selectedLayer, geomGuess, keyGuess)
def probeKeyGeom(self):
self.populateComboBox(self.dlg.KEYFIELD,
self.PSQL.getKeyFields(self.selectedLayer), "",
None)
self.populateComboBox(self.dlg.GEOMETRYFIELD,
self.PSQL.getGeometryFields(self.selectedLayer),
"", None)
def layerGetTable(self):
self.PSQL.tableResultGen(self.selectedLayer, "", self.dlg.TableResult)
self.dlg.tabWidget.setCurrentIndex(4)
def convertToTable(self):
if self.PSQL.isMaterializedView(
self.selectedLayer) or self.PSQL.isView(self.selectedLayer):
self.toTableDlg.ask(self.selectedLayer)
def renameObject(self):
newLayer = renameDialog.rename(self.selectedLayer)
if newLayer:
error = self.PSQL.renameLayer(self.selectedLayer, newLayer)
if error:
QMessageBox.information(None, "ERROR:", error)
else:
self.updateLayerMenu()
def moveObject(self):
schemas = self.PSQL.getSchemas()
schemas.remove(self.PSQL.getSchema())
schemas.insert(0, self.PSQL.getSchema())
newSchema = moveDialog.getNewSchema(self)
if newSchema:
error = self.PSQL.moveLayer(self.selectedLayer, newSchema)
if error:
QMessageBox.information(None, "ERROR:", error)
else:
self.updateLayerMenu()
def layerDelete(self, cascade=None):
msg = "Are you sure you want to delete layer '%s' from schema '%s' ?" % (
self.selectedLayer, self.PSQL.getSchema())
reply = QMessageBox.question(None, 'Message', msg, QMessageBox.Yes,
QMessageBox.No)
if reply == QMessageBox.Yes:
result = self.PSQL.deleteLayer(self.selectedLayer)
if result:
if "DROP ... CASCADE" in result:
msg = result + "\n\nLayer '%s' has dependencies. Do you want to remove all recursively ?" % self.selectedLayer
reply = QMessageBox.question(None, 'Message', msg,
QMessageBox.Yes,
QMessageBox.No)
if reply == QMessageBox.Yes:
result = self.PSQL.deleteLayer(self.selectedLayer,
cascade=True)
if result:
QMessageBox.information(None, "ERROR:", result)
else:
print "CASCADE DELETED", self.selectedLayer
else:
QMessageBox.information(None, "ERROR:", result)
else:
print "DELETED", self.selectedLayer
pass
self.populateLayerMenu()
def layerRefresh(self):
if self.PSQL.isMaterializedView(self.selectedLayer):
self.PSQL.refreshMaterializedView(self.selectedLayer)
def recurseChild(self, slot, tab):
# for testing: prints qt object tree
for child in slot.children():
#print tab,"|",child.objectName()
if child.children() != []:
self.recurseChild(child, tab + " ")
def uncheckList(self, slot):
for row in range(0, slot.count()):
self.dlg.LayerList.item(row).setCheckState(Qt.Unchecked)
def addNewSchema(self):
newSchema = renameDialog.rename("new_schema")
error = self.PSQL.addSchema(newSchema)
if error:
QMessageBox.information(None, "ERROR:", error)
else:
self.populateComboBox(self.dlg.DBSchema, self.PSQL.getSchemas(),
"Select schema", True)
def disableDialogSlot(self, slot):
for child in self.dlg.tabWidget.widget(1).children():
if child.objectName() == slot:
child.setDisabled(True)
def hideDialogSlot(self, slot):
for child in self.dlg.tabWidget.widget(1).children():
if child.objectName() == slot:
child.hide()
def showDialogSlot(self, slot):
for child in self.dlg.tabWidget.widget(1).children():
if child.objectName() == slot:
child.show()
def enableDialogSlot(self, slot):
for child in self.dlg.tabWidget.widget(1).children():
if child.objectName() == slot:
child.setEnabled(True)
def clearDialogSlot(self, slot):
for child in self.dlg.tabWidget.widget(1).children():
if child.objectName() == slot:
child.clear()
def checkCreateView(self):
#method called when checkbox createview is clicked
if self.dlg.checkCreateView.checkState():
self.dlg.QueryName.setEnabled(True)
self.dlg.checkMaterialized.setEnabled(True)
else:
self.dlg.QueryName.setDisabled(True)
self.dlg.checkMaterialized.setDisabled(True)
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def helpDialog(self):
print "called help"
if self.dlg.QueryType.currentText() != "Select query type":
QDesktopServices.openUrl(QUrl(self.querySet.getHelp()))
#webbrowser.open_new(self.querySet.getHelp())
def helpLinkOpen(self):
QDesktopServices.openUrl(
QUrl(
"https://geogear.wordpress.com/postgisquerybuilder-v1-6-cheatsheet/"
))
def setMaterialized(self):
self.queryGen()
def keyGeomFieldsChanged(self):
self.querySet.setParameter("GEOMETRYFIELD",
self.dlg.GEOMETRYFIELD.currentText())
self.querySet.setParameter("KEYFIELD", self.dlg.KEYFIELD.currentText())
test = None
try:
test = self.querySet.testQueryParametersCheckList()
except:
pass
if test:
self.queryGen()
def tabChangedHub(self, tab):
#print "TAB:",tab
if tab == 0:
try:
self.updateLayerMenu()
except:
pass
try:
self.keyGeomFieldsChanged()
except:
pass
if tab == 3:
self.queryGen()
if tab == 2:
self.updateOrderBy()
elif tab == 5:
self.updateHistoryLog()
def updateOrderBy(self):
if self.dlg.LAYERa.currentText()[:6] != "Select":
try:
self.populateComboBox(
self.dlg.orderBy,
self.PSQL.getFieldsContent(self.dlg.LAYERa.currentText()),
" ", True)
except:
pass
def focusOnQuery(self):
self.dlg.tabWidget.setCurrentIndex(3)
def updateHistoryLog(self):
historyFile = os.path.join(os.path.dirname(__file__), "validSql.log")
if os.path.exists(historyFile):
in_file = open(historyFile, "r")
self.dlg.historyLog.setPlainText(in_file.read())
self.dlg.historyLog.moveCursor(QTextCursor.End)
def populateComboBox(self, combo, list, predef, sort):
#procedure to fill specified combobox with provided list
combo.blockSignals(True)
combo.clear()
model = QStandardItemModel(combo)
predefInList = None
for elem in list:
try:
item = QStandardItem(unicode(elem))
except TypeError:
item = QStandardItem(str(elem))
model.appendRow(item)
if elem == predef:
predefInList = elem
if sort:
model.sort(0)
combo.setModel(model)
if predef != "":
if predefInList:
combo.setCurrentIndex(combo.findText(predefInList))
else:
combo.insertItem(0, predef)
combo.setCurrentIndex(0)
combo.blockSignals(False)
def loadSVG(self, svgName):
self.dlg.DiagPanel.show()
svgFile = os.path.join(os.path.dirname(os.path.abspath(__file__)),
"svg", svgName + ".svg")
#print svgFile
item = QGraphicsSvgItem(svgFile)
scene = QGraphicsScene()
scene.addItem(item)
self.dlg.DiagPanel.setScene(scene)
self.dlg.DiagPanel.fitInView(item,
Qt.KeepAspectRatio) #,Qt.KeepAspectRatio
def setLAYERa(self):
#called when LAYERa is activated
if self.dlg.LAYERa.currentText()[:6] == "Select":
return
self.querySet.setParameter("LAYERa", self.dlg.LAYERa.currentText())
#try to guess layer geometry field
autoGeometry = self.PSQL.guessGeometryField(
self.dlg.LAYERa.currentText(),
suggestion=self.dlg.GEOMETRYFIELD.currentText())
self.querySet.setParameter("GEOMETRYFIELDa", autoGeometry)
if self.querySet.testQueryParametersCheckList():
self.queryGen()
self.populateComboBox(
self.dlg.FIELD,
self.PSQL.getFieldsContent(self.dlg.LAYERa.currentText()),
"Select field", True)
if not self.PSQL.testIfFieldExist(
self.dlg.LAYERa.currentText(),
self.querySet.getParameter("KEYFIELD")):
self.querySet.setFIDFIELD()
self.addListToFieldTable(self.dlg.fieldsListA,
self.PSQL.getFieldsContent(
self.dlg.LAYERa.currentText()),
True,
exclude=autoGeometry)
self.populateFilterTable()
self.setSCHEMAb()
def selectAllFields(self):
for row in range(0, self.dlg.fieldsListA.count()):
if self.dlg.fieldsListA.item(row).flags() & Qt.ItemIsEnabled:
self.dlg.fieldsListA.item(row).setCheckState(
self.dlg.LAYERaAllFields.checkState())
self.setFieldsList()
def setSCHEMAb(self):
#called when SCHEMAb is activated
self.querySet.setParameter("SCHEMAb", self.dlg.SCHEMAb.currentText())
self.populateComboBox(
self.dlg.LAYERb,
self.PSQL.getLayers(schema=self.dlg.SCHEMAb.currentText()),
"Select layer", True)
def setFILTERSCHEMA(self):
#called when FILTERSCHEMA is activated
self.dlg.filterTable.setCurrentSchema(
self.dlg.FILTERSCHEMA.currentText())
def setLAYERb(self):
#called when LAYERb is activated
if self.dlg.LAYERb.currentText()[:6] == "Select":
return
self.querySet.setParameter("LAYERb", self.dlg.LAYERb.currentText())
#try to guess layer geometry field
autoGeometry = self.PSQL.guessGeometryField(
self.dlg.LAYERb.currentText(),
suggestion=self.dlg.GEOMETRYFIELD.currentText())
self.querySet.setParameter("GEOMETRYFIELDb", autoGeometry)
if self.querySet.testQueryParametersCheckList():
self.queryGen()
self.addListToFieldTable(
self.dlg.fieldsListB,
self.PSQL.getFieldsContent(self.dlg.LAYERb.currentText()), True)
self.populateComboBox(
self.dlg.FIELDb,
self.PSQL.getFieldsContent(self.dlg.LAYERb.currentText()),
"Select field", True)
def addListToFieldTable(self, fieldSlot, fl, check, exclude=None):
#called to populate field list for WHERE statement
wdgt = fieldSlot
wdgt.clear()
#print "LAYERLIST:",check
for row in fl:
item = QListWidgetItem()
if check:
item.setFlags(item.flags() | Qt.ItemIsUserCheckable)
item.setCheckState(Qt.Unchecked)
item.setText(row)
#print row
if self.PSQL.isTable(row):
item.setIcon(QIcon(":/plugins/postgisquerybuilder/iT.png"))
elif self.PSQL.isView(row):
item.setIcon(QIcon(":/plugins/postgisquerybuilder/iV.png"))
elif self.PSQL.isMaterializedView(row):
item.setIcon(QIcon(":/plugins/postgisquerybuilder/iM.png"))
#disable geometryfield and auto set checked/unchecked
wdgt.addItem(item)
if check and exclude and row == exclude: #self.geoQuery and
if self.geoQuery:
item.setCheckState(Qt.Unchecked)
else:
item.setCheckState(Qt.Checked)
item.setFlags(item.flags() & (~Qt.ItemIsEnabled))
#item.setFlags(item.flags() | Qt.ItemIsEnabled)
def setFieldsList(self):
# procedure to resume selected fields to SELECT statements
items = []
for index in xrange(self.dlg.fieldsListA.count()):
if self.dlg.fieldsListA.item(index).checkState() == Qt.Checked:
items.append('"' + self.dlg.LAYERa.currentText() + '"."' +
self.dlg.fieldsListA.item(index).text() + '"')
for index in xrange(self.dlg.fieldsListB.count()):
if self.dlg.fieldsListB.item(index).checkState() == Qt.Checked:
items.append('"' + self.dlg.LAYERb.currentText() + '"."' +
self.dlg.fieldsListB.item(index).text() + '"')
#print "SELECTED FIELDS",items
self.querySet.setFieldsSet(items)
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setFIELD(self):
if self.dlg.FIELD.currentText()[:6] == "Select":
return
self.querySet.setParameter(
"FIELD", '"' + self.dlg.LAYERa.currentText() + '"."' +
self.dlg.FIELD.currentText() + '"')
self.querySet.setParameter("SIMPLEFIELD", self.dlg.FIELD.currentText())
fType = self.PSQL.getFieldsType(self.querySet.getParameter("LAYERa"),
self.dlg.FIELD.currentText())
fType = fType[:4]
#print fType
if ((fType == "char") or (fType == "text") or (fType == "varc")):
self.tDelimiter = "'"
else:
self.tDelimiter = ""
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setFIELDb(self):
if self.dlg.FIELDb.currentText()[:6] == "Select":
return
self.querySet.setParameter(
"FIELDb", '"' + self.dlg.LAYERb.currentText() + '"."' +
self.dlg.FIELDb.currentText() + '"')
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setSPATIALREL(self):
if self.dlg.SPATIALREL.currentText()[:6] == "Select":
return
self.querySet.setParameter("SPATIALREL",
self.dlg.SPATIALREL.currentText())
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setBUFFERRADIUS(self):
self.querySet.setParameter("BUFFERRADIUS",
self.dlg.BUFFERRADIUS.text())
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setSPATIALRELNOT(self):
if self.dlg.SPATIALRELNOT.isChecked():
self.querySet.setParameter("SPATIALRELNOT", "NOT ")
else:
self.querySet.setParameter("SPATIALRELNOT", " ")
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setJOIN(self):
self.querySet.setParameter("JOIN", self.dlg.JOIN.currentText())
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setQueryName(self):
try:
self.querySet.testIfQueryDefined()
except:
return
self.querySet.setParameter("VIEWNAME", self.dlg.QueryName.text())
if self.querySet.testQueryParametersCheckList():
self.dlg.QueryResult.setPlainText(
self.querySet.getQueryParsed(
self.dlg.checkCreateView.checkState()))
def queryGen(self):
if self.dlg.checkCreateView.checkState():
self.enableDialogSlot("QueryName")
if not self.querySet.testIfQueryDefined():
return
if self.dlg.checkAutoCompiled.checkState():
if self.dlg.checkMaterialized.checkState():
self.querySet.setParameter("MATERIALIZED", "MATERIALIZED")
else:
self.querySet.setParameter("MATERIALIZED", "")
self.enableDialogSlot("QueryResult")
self.enableDialogSlot("checkCreateView")
self.enableDialogSlot("AddToMap")
try:
qName = self.querySet.getNameParsed()
except:
qName = self.dlg.QueryName.text()
self.dlg.QueryName.setText(qName)
self.querySet.setParameter("VIEWNAME", qName)
if self.dlg.filterTable.testIfSintaxOk():
self.querySet.setParameter(
"WHERE", self.dlg.filterTable.getWhereStatement())
self.querySet.setParameter(
"SPATIALFROM",
self.dlg.filterTable.getSpatialFilterLayers(
schema=self.PSQL.getSchema()))
else:
self.querySet.setParameter("WHERE", "")
if self.dlg.orderBy.currentText() != " ":
self.querySet.setParameter(
"ORDERBY",
'ORDER BY "' + self.dlg.orderBy.currentText() + '"')
else:
self.querySet.setParameter("ORDERBY", "")
self.dlg.QueryResult.setPlainText(
self.querySet.getQueryParsed(
self.dlg.checkCreateView.checkState()))
self.dlg.QueryName.textChanged.connect(self.setQueryName)
def setQueryType(self, line):
theQ = self.dlg.QueryType.currentText()
if theQ[:6] == "Select":
return
self.loadSVG(theQ.replace(" ", "_"))
#self.querySet.resetParameters()
self.resetDialog()
self.dlg.summaryBox.show()
self.querySet.setCurrentQuery(theQ)
if theQ[:2] == "ST":
self.geoQuery = True
else:
self.geoQuery = None
for slot in self.querySet.getRequiredSlots():
#print slot
#self.enableDialogSlot(slot)
self.showDialogSlot(slot)
self.showDialogSlot(slot + "Label")
self.showDialogSlot(slot + "AllFields")
#print self.querySet.testQueryParametersCheckList()
#simulate click on checkbox to set required slot
self.dlg.SPATIALRELNOT.setCheckState(Qt.Checked)
self.dlg.SPATIALRELNOT.setCheckState(Qt.Unchecked)
self.dlg.Helper.setText(theQ + ":\n" + self.querySet.getDescription())
self.loadSVG(theQ.replace(" ", "_"))
if self.predefinedLayer:
self.dlg.LAYERa.setCurrentIndex(
self.dlg.LAYERa.findText(self.predefinedLayer))
self.setLAYERa()
def resetDialog(self):
self.eventsDisconnect()
self.clearAllDialogs()
self.querySet.resetParameters()
#self.disableQueryDefSlot()
self.hideQueryDefSlot()
self.loadSVG("Select_query_type")
self.dlg.summaryBox.hide()
try:
tables = self.PSQL.getLayers()
except AttributeError:
pass
else:
self.populateComboBox(self.dlg.LAYERa, tables, "Select Layer",
True)
self.populateComboBox(self.dlg.LAYERb, tables, "Select Layer",
True)
self.geoQuery = None
self.populateComboBox(self.dlg.orderBy, [" "], "", True)
self.eventsConnect()
def hideQueryDefSlot(self):
toHide=["BUFFERRADIUS","FIELD",\
"SPATIALREL","SPATIALRELNOT","LAYERaLabel","BUFFERRADIUSLabel",\
"FIELD","FIELDLabel","SPATIALRELLabel",
"SPATIALRELNOTLabel","fieldsListALabel","fieldsListBLabel",\
"FIELDb","FIELDbLabel","JOIN","JOINLabel",\
"LAYERa","SCHEMAb","LAYERb","LAYERbLabel","summaryBox","LAYERaAllFields","fieldsListA","fieldsListB"]
for slot in toHide:
self.hideDialogSlot(slot)
def clearQueryDefSlot(self):
toClear=["LAYERa","LAYERb","BUFFERRADIUS","FIELD","FIELDb",\
"SPATIALREL","fieldsListA","fieldsListB",\
"JOIN"]
for slot in toClear:
self.clearDialogSlot(slot)
def disableQueryDefSlot(self):
toDisable=["LAYERa","LAYERb","BUFFERRADIUS","FIELD","FIELDb",\
"SPATIALREL",\
"SPATIALRELNOT","fieldsListA","fieldsListB",
"JOIN"]
for slot in toDisable:
self.disableDialogSlot(slot)
def clearAllDialogs(self):
self.dlg.LAYERa.clear()
self.dlg.LAYERb.clear()
self.dlg.BUFFERRADIUS.clear()
self.dlg.FIELD.clear()
self.dlg.QueryResult.clear()
self.dlg.QueryName.clear()
self.dlg.fieldsListA.clear()
self.dlg.fieldsListB.clear()
self.dlg.TableResult.clear()
self.dlg.SPATIALRELNOT.setCheckState(Qt.Unchecked)
self.dlg.LAYERaAllFields.setCheckState(Qt.Unchecked)
def getPSQLConnections(self):
try:
self.dlg.PSQLConnection.activated.disconnect(self.setConnection)
except:
pass
conn = self.PSQL.getConnections()
self.populateComboBox(self.dlg.PSQLConnection, conn,
"Select connection", True)
self.hideQueryDefSlot()
self.dlg.PSQLConnection.activated.connect(self.setConnection)
def closeDialog(self):
self.resetDialog()
self.dlg.hide()
def resetForm(self):
self.resetDialog()
self.getPSQLConnections()
self.populateGui()
self.dlg.tabWidget.setCurrentIndex(0)
def setConnection(self):
if self.dlg.PSQLConnection.currentText()[:6] == "Select":
return
self.PSQL.setConnection(self.dlg.PSQLConnection.currentText())
#print "SCHEMAS",self.PSQL.getSchemas()
schemas = self.PSQL.getSchemas()
self.populateComboBox(self.dlg.DBSchema, schemas, "Select schema",
True)
self.dlg.DBSchema.activated.connect(self.loadPSQLLayers)
for r in range(0, self.dlg.DBSchema.count()):
if self.dlg.DBSchema.itemText(r) == "public":
self.dlg.DBSchema.setCurrentIndex(r)
self.dlg.DBSchema.removeItem(0)
self.loadPSQLLayers()
#clone dbschema in schemab
self.populateComboBox(self.dlg.SCHEMAb, [
self.dlg.DBSchema.itemText(i)
for i in range(self.dlg.DBSchema.count())
], self.dlg.DBSchema.currentText(), True)
self.querySet.setParameter("SCHEMAb", self.dlg.SCHEMAb.currentText())
self.populateComboBox(self.dlg.FILTERSCHEMA, [
self.dlg.DBSchema.itemText(i)
for i in range(self.dlg.DBSchema.count())
], self.dlg.DBSchema.currentText(), True)
def loadPSQLLayers(self):
if self.dlg.DBSchema.currentText() != "Select schema":
self.PSQL.setSchema(self.dlg.DBSchema.currentText())
self.populateGui()
self.resetDialog()
#self.dlg.tabWidget.setCurrentIndex(1)
self.querySet.setSchema(self.dlg.DBSchema.currentText())
self.populateComboBox(self.dlg.GEOMETRYFIELD,
self.PSQL.scanLayersForGeometry(), "", None)
self.populateComboBox(self.dlg.KEYFIELD,
self.PSQL.scanLayersForPrimaryKey(), "",
None)
#sync schemab con dbschema
self.dlg.SCHEMAb.setCurrentIndex(self.dlg.DBSchema.currentIndex())
self.querySet.setParameter("SCHEMAb",
self.dlg.SCHEMAb.currentText())
self.populateLayerMenu()
def populateFilterTable(self):
self.dlg.filterTable.populateFilterTable(self.PSQL,
self.dlg.LAYERa.currentText())
def testSignal(self, v1, v2):
#print "catch:", v1,v2
pass
def populateLayerMenu(self):
self.addListToFieldTable(self.dlg.LayerList, self.PSQL.getLayers(),
None)
def updateLayerMenu(self):
if self.dlg.DBSchema.currentText() != "Select schema":
self.addListToFieldTable(self.dlg.LayerList, self.PSQL.getLayers(),
None)
def runQuery(self):
#method to run generated query
if self.dlg.filterTable.testIfSintaxOk():
if self.dlg.AddToMap.checkState():
if self.dlg.checkCreateView.checkState(
): #self.querySet.getParameter("VIEWNAME")
self.PSQL.submitQuery(self.dlg.QueryName.text(),
self.dlg.QueryResult.toPlainText())
self.PSQL.loadView(
self.dlg.QueryName.text(),
self.querySet.getParameter("GEOMETRYFIELD"),
self.querySet.getParameter("KEYFIELD"))
else:
self.PSQL.loadSql(
self.dlg.QueryName.text(),
self.dlg.QueryResult.toPlainText(),
self.querySet.getParameter("GEOMETRYFIELD"),
self.querySet.getParameter("KEYFIELD"))
else:
if self.dlg.checkCreateView.checkState():
self.PSQL.submitQuery(self.dlg.QueryName.text(),
self.dlg.QueryResult.toPlainText())
sql = 'SELECT * FROM "%s"."%s"' % (
self.PSQL.getSchema(), self.dlg.QueryName.text())
print sql
rows = self.PSQL.tableResultGen(
self.dlg.LAYERa.currentText(), sql,
self.dlg.TableResult)
self.PSQL.loadedLayerRefresh(self.dlg.QueryName.text())
else:
rows = self.PSQL.tableResultGen(
self.dlg.LAYERa.currentText(),
self.dlg.QueryResult.toPlainText(),
self.dlg.TableResult)
self.dlg.labelRowsNumber.setText("Total rows: " + str(rows))
self.dlg.tabWidget.setCurrentIndex(4)
else:
QMessageBox.information(None, "FILTER ERROR:",
"The Filter table is malformed")
self.dlg.tabWidget.setCurrentIndex(2)
def unload(self):
# Remove the plugin menu item and icon
self.iface.removePluginMenu(u"&postgisQueryBuilder", self.action)
self.iface.removeToolBarIcon(self.action)
self.iface.removeDockWidget(self.PQBdockwidget)
# run method that performs all the real work
def run(self):
# show/hide the widget
if self.PQBdockwidget.isVisible():
self.PQBdockwidget.hide()
else:
self.PQBdockwidget.show()
self.getPSQLConnections()
def main():
parser = argparse.ArgumentParser(
description='Initialize or update a Postgres database with OSM data')
parser.add_argument(
'-i',
'--init',
dest='initialize',
action='store_const',
const=True,
default=False,
help='Initialize the database and supply a planet file')
parser.add_argument(
'-u',
'--update',
dest='update',
action='store_const',
const=True,
default=False,
help='Update and initialized database by downloading updates')
parser.add_argument('-f',
'--filter',
dest='filter_path',
default='./config/filter.json',
help='Filter file')
parser.add_argument('-b',
'--base_url',
dest='base_url',
default='https://planet.osm.ch/replication',
help='Planet website. Used both in init and update')
parser.add_argument(
'-m',
'--mode',
dest='mode',
default='hour',
help='replication mode (hour, minute...). Used both in init and update'
)
parser.add_argument('-ph',
'--psqlhost',
dest='psqlhost',
required=True,
help='Postgres host')
parser.add_argument('-ppo',
'--psqlport',
dest='psqlport',
default=5432,
help='Postgres port')
parser.add_argument('-pd',
'--psqldb',
dest='psqldb',
required=True,
help='Postgres database')
parser.add_argument('-pu',
'--psqluser',
dest='psqluser',
required=True,
help='Postgres user')
parser.add_argument('-pp',
'--psqlpassword',
dest='psqlpassword',
required=True,
help='Postgres password')
parser.add_argument(
'-p',
'--planet',
dest='planet_path',
default=None,
help='Planet file in pbf format. Option valid only if --init is used')
parser.add_argument('-d',
'--debug',
dest='debug',
action='store_const',
const=True,
default=False,
help='Debug mode')
args = parser.parse_args()
if args.initialize and args.update:
return print('Select either --init or --update, not both')
if not args.initialize and not args.update:
return print('Select one of --init or --update')
if args.initialize and args.planet_path is None:
return print('To initialize, we need a planet file (-p)')
if args.update and args.planet_path:
print('Ignoring planet file during updatess')
setup_logging(args.debug)
logging.debug('Options parsed')
logging.info(f'base url: {args.base_url}, mode: {args.mode}')
filt = Filter(args.filter_path)
if filt is None:
return
psql = PSQL(args.psqlhost, args.psqlport, args.psqldb, args.psqluser,
args.psqlpassword)
if args.initialize:
try:
initializer = Initializer(args, psql, filt)
initializer.start()
except:
logging.critical('Something came up.', exc_info=True)
return
psql.stop()
logging.info('Stopping PSQL. It might take some seconds')
class postgisQueryBuilder:
def __init__(self, iface):
# Save reference to the QGIS interface
self.iface = iface
# initialize plugin directory
self.plugin_dir = os.path.dirname(__file__)
# initialize locale
locale = QSettings().value("locale/userLocale")[0:2]
localePath = os.path.join(self.plugin_dir, 'i18n', 'postgisquerybuilder_{}.qm'.format(locale))
if os.path.exists(localePath):
self.translator = QTranslator()
self.translator.load(localePath)
if qVersion() > '4.3.3':
QCoreApplication.installTranslator(self.translator)
#self.dlg = uic.loadUi( os.path.join( os.path.dirname( os.path.abspath( __file__ ) ), "ui_postgisquerybuilder.ui" ) )
self.querySet = querySet()
self.PSQL = PSQL(self.iface)
def eventsConnect(self):
self.dlg.QueryType.activated.connect(self.setQueryType)
self.dlg.checkAutoCompiled.stateChanged.connect(self.queryGen)
self.dlg.LAYERa.activated.connect(self.setLAYERa)
self.dlg.SCHEMAb.activated.connect(self.setSCHEMAb)
self.dlg.FILTERSCHEMA.activated.connect(self.setFILTERSCHEMA)
self.dlg.LAYERb.activated.connect(self.setLAYERb)
self.dlg.FIELD.activated.connect(self.setFIELD)
self.dlg.SPATIALREL.activated.connect(self.setSPATIALREL)
self.dlg.SPATIALRELNOT.stateChanged.connect(self.setSPATIALRELNOT)
self.dlg.checkCreateView.clicked.connect(self.checkCreateView)
self.dlg.BUFFERRADIUS.textChanged.connect(self.setBUFFERRADIUS)
self.dlg.ButtonRun.clicked.connect(self.runQuery)
self.dlg.ButtonReset.clicked.connect(self.resetForm)
self.dlg.ButtonHelp.clicked.connect(self.helpDialog)
self.dlg.fieldsListA.clicked.connect(self.setFieldsList)
self.dlg.fieldsListB.clicked.connect(self.setFieldsList)
self.dlg.checkMaterialized.clicked.connect(self.setMaterialized)
#self.dlg.AddToMapButton.clicked.connect(self.layerAddToMap)
#self.dlg.GetInfoButton.clicked.connect(self.layerGetTable)
#self.dlg.DeleteButton.clicked.connect(self.layerDelete)
#self.dlg.RefreshButton.clicked.connect(self.layerRefresh)
#self.dlg.convertToTableButton.clicked.connect(self.convertToTable)
self.dlg.JOIN.activated.connect(self.setJOIN)
self.dlg.FIELDb.activated.connect(self.setFIELDb)
self.dlg.tabWidget.currentChanged.connect(self.tabChangedHub)
self.dlg.KEYFIELD.activated.connect(self.keyGeomFieldsChanged)
self.dlg.KEYFIELD.editTextChanged.connect(self.keyGeomFieldsChanged)
self.dlg.GEOMETRYFIELD.activated.connect(self.keyGeomFieldsChanged)
self.dlg.GEOMETRYFIELD.editTextChanged.connect(self.keyGeomFieldsChanged)
self.dlg.LAYERaAllFields.clicked.connect(self.selectAllFields)
self.dlg.LayerList.itemDoubleClicked.connect(self.useForQuery)
self.dlg.LayerList.customContextMenuRequested.connect(self.layerContextMenu)
self.dlg.LayerList.itemSelectionChanged.connect(self.saveForQuery)
self.dlg.schemaAdd.clicked.connect(self.addNewSchema)
self.dlg.HelpLink.clicked.connect(self.helpLinkOpen)
def eventsDisconnect(self):
self.dlg.QueryType.activated.disconnect(self.setQueryType)
self.dlg.checkAutoCompiled.stateChanged.disconnect(self.queryGen)
self.dlg.LAYERa.activated.disconnect(self.setLAYERa)
self.dlg.SCHEMAb.activated.disconnect(self.setSCHEMAb)
self.dlg.FILTERSCHEMA.activated.disconnect(self.setFILTERSCHEMA)
self.dlg.LAYERb.activated.disconnect(self.setLAYERb)
self.dlg.FIELD.activated.disconnect(self.setFIELD)
self.dlg.SPATIALREL.activated.disconnect(self.setSPATIALREL)
self.dlg.SPATIALRELNOT.stateChanged.disconnect(self.setSPATIALRELNOT)
self.dlg.checkCreateView.clicked.disconnect(self.checkCreateView)
self.dlg.BUFFERRADIUS.textChanged.disconnect(self.setBUFFERRADIUS)
self.dlg.ButtonRun.clicked.disconnect(self.runQuery)
self.dlg.ButtonReset.clicked.disconnect(self.resetForm)
self.dlg.ButtonHelp.clicked.disconnect(self.helpDialog)
#self.dlg.AddToMapButton.clicked.disconnect(self.layerAddToMap)
#self.dlg.GetInfoButton.clicked.disconnect(self.layerGetTable)
#self.dlg.DeleteButton.clicked.disconnect(self.layerDelete)
#self.dlg.RefreshButton.clicked.disconnect(self.layerRefresh)
#self.dlg.convertToTableButton.clicked.disconnect(self.convertToTable)
self.dlg.JOIN.activated.disconnect(self.setJOIN)
self.dlg.FIELDb.activated.disconnect(self.setFIELDb)
self.dlg.fieldsListA.clicked.disconnect(self.setFieldsList)
self.dlg.fieldsListB.clicked.disconnect(self.setFieldsList)
self.dlg.tabWidget.currentChanged.disconnect(self.tabChangedHub)
self.dlg.KEYFIELD.activated.disconnect(self.keyGeomFieldsChanged)
self.dlg.KEYFIELD.editTextChanged.disconnect(self.keyGeomFieldsChanged)
self.dlg.GEOMETRYFIELD.activated.disconnect(self.keyGeomFieldsChanged)
self.dlg.GEOMETRYFIELD.editTextChanged.disconnect(self.keyGeomFieldsChanged)
self.dlg.LAYERaAllFields.clicked.disconnect(self.selectAllFields)
self.dlg.LayerList.itemDoubleClicked.disconnect(self.useForQuery)
self.dlg.LayerList.customContextMenuRequested.disconnect(self.layerContextMenu)
self.dlg.LayerList.itemSelectionChanged.disconnect(self.saveForQuery)
self.dlg.schemaAdd.clicked.disconnect(self.addNewSchema)
self.dlg.HelpLink.clicked.disconnect(self.helpLinkOpen)
def initGui(self):
# Create action that will start plugin configuration
self.action = QAction(
QIcon(":/plugins/postgisquerybuilder/querybuilderlogo.png"),
u"postgisQueryBuilder", self.iface.mainWindow())
# connect the action to the run method
self.action.triggered.connect(self.run)
# Add toolbar button and menu item
self.iface.addToolBarIcon(self.action)
self.iface.addPluginToDatabaseMenu(u"&postgisQueryBuilder", self.action)
#setup Docked widget
self.dlg = postgisQueryBuilderDialog()
self.PQBdockwidget=QDockWidget("postgisQueryBuilder" , self.iface.mainWindow() )
self.PQBdockwidget.setObjectName("postgisQueryBuilder")
self.PQBdockwidget.setWidget(self.dlg)
self.PQBdockwidget.setAllowedAreas(Qt.RightDockWidgetArea | Qt.LeftDockWidgetArea)
self.iface.addDockWidget( Qt.RightDockWidgetArea, self.PQBdockwidget)
#defaults
#self.dlg.GEOMETRYFIELD.setText("the_geom")
#self.dlg.KEYFIELD.setText("ogc_fid")
self.predefinedLayer = None
#hide Temp slots
self.dlg.USERFIELD.hide()
self.dlg.USERFIELDLabel.hide()
self.dlg.PASSWORDFIELD.hide()
self.dlg.PASSWORDFIELDLabel.hide()
#init
self.getPSQLConnections()
self.populateGui()
self.eventsConnect()
self.toTableDlg = convertToTableDialog(self)
def populateGui(self):
self.populateComboBox(self.dlg.QueryType,self.querySet.getQueryLabels(),"Select query type",True)
self.populateComboBox(self.dlg.JOIN,["INNER JOIN","CROSS JOIN","RIGHT OUTER JOIN","LEFT OUTER JOIN","FULL OUTER JOIN"],"Select",True)
self.populateComboBox(self.dlg.SPATIALREL,self.querySet.getSpatialRelationships(),"Select spatial relationship",True)
self.populateComboBox(self.dlg.KEYFIELD,["ogc_fid","id","fid","gid","FID","GID","ID"],"",None)
self.populateComboBox(self.dlg.GEOMETRYFIELD,["the_geom","geom","GEOM","geometry"],"",None)
self.dlg.checkAutoCompiled.setChecked(True)
self.dlg.tabWidget.setCurrentIndex(0)
#self.recurseChild(self.dlg,"")
# def layerAddToMap(self):
# for rowList in range(0,self.dlg.LayerList.count()):
# rowCheckbox = self.dlg.LayerList.item(rowList)
# #take only selected attributes by checkbox
# if rowCheckbox.checkState() == Qt.Checked:
# self.PSQL.loadView(rowCheckbox.text(),self.dlg.GEOMETRYFIELD.text(),self.dlg.KEYFIELD.text())
# self.uncheckList(self.dlg.LayerList)
def layerForQuery(self,listItem):
print listItem
def layerContextMenu(self,listItem):
self.predefinedLayer = None
for rowSel in (self.dlg.LayerList.selectedItems()):
self.selectedLayer = rowSel.text()
contextMenu = QMenu()
self.layerNameAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","useForQuery.png")),\
self.selectedLayer)
contextMenu.addSeparator()
self.countFeatureAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","useForQuery.png")),\
"Features Count: "+self.PSQL.getFeatureCount(self.selectedLayer))
self.relationTypeAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","useForQuery.png")),\
"Relation Type: "+self.PSQL.getRelationType(self.selectedLayer))
self.geometryTypeAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","useForQuery.png")),\
"Geometry Type: "+self.PSQL.getGeometryType(\
self.selectedLayer,suggestion = self.dlg.GEOMETRYFIELD.currentText()))
self.autoKeyAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","useForQuery.png")),\
"Detected key field: %s" % self.PSQL.guessKeyField(\
self.selectedLayer,suggestion = self.dlg.KEYFIELD.currentText()) or "undefined")
self.autoGeometryAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","useForQuery.png")),\
"Detected geom field: %s" % self.PSQL.guessGeometryField(\
self.selectedLayer,suggestion = self.dlg.GEOMETRYFIELD.currentText()) or "undefined")
self.nativeSRIDAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","useForQuery.png")),\
"Native SRID: EPSG:"+self.PSQL.getSRID(\
self.selectedLayer,suggestion = self.dlg.GEOMETRYFIELD.currentText()))
contextMenu.addSeparator()
self.useForQueryAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","useForQuery.png")),\
"Use for query")
self.useForQueryAction.triggered.connect(self.useForQuery)
self.layerAddToMapAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","addToLayer.png")),\
"Add layer to map canvas")
self.layerAddToMapAction.triggered.connect(self.layerAddToMap)
self.probeKeyGeomAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","probeKeyGeom.png")),\
"Auto detect primary key and geometry fields")
self.probeKeyGeomAction.triggered.connect(self.probeKeyGeom)
self.layerGetTableAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","layerGetTable.png")),\
"View as data table")
self.layerGetTableAction.triggered.connect(self.layerGetTable)
self.renameObjectAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","renameObject.png")),\
"Rename")
self.renameObjectAction.triggered.connect(self.renameObject)
self.layerDeleteAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","layerDelete.png")),\
"Delete")
self.layerDeleteAction.triggered.connect(self.layerDelete)
self.moveObjectAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","moveObject.png")),\
"Move to another schema")
self.moveObjectAction.triggered.connect(self.moveObject)
if self.PSQL.isView(self.selectedLayer) or self.PSQL.isMaterializedView(self.selectedLayer):
self.convertToTableAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","convertToTable.png")),\
"Convert view to table")
self.convertToTableAction.triggered.connect(self.convertToTable)
if self.PSQL.isView(self.selectedLayer):
self.editViewAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","layerDelete.png")),\
"Edit view definition")
self.editViewAction.triggered.connect(self.editViewDefinition)
if self.PSQL.isMaterializedView(self.selectedLayer):
self.layerRefreshAction = contextMenu.addAction(QIcon(os.path.join(self.plugin_dir,"icons","layerRefresh.png")),\
"Refresh materialized view")
self.layerRefreshAction.triggered.connect(self.layerRefresh)
contextMenu.exec_(QCursor.pos())
def editViewDefinition(self):
sqlView = 'CREATE OR REPLACE VIEW "%s"."%s" AS ' % (self.PSQL.getSchema(),self.selectedLayer) + self.PSQL.getViewDef(self.selectedLayer)
self.resetDialog()
self.dlg.QueryName.setText(self.selectedLayer)
self.dlg.checkCreateView.setChecked(True)
self.dlg.AddToMap.setChecked(False)
if self.PSQL.isMaterializedView(self.selectedLayer):
self.dlg.checkMaterialized.setChecked(True)
self.dlg.checkAutoCompiled.setChecked(False)
self.dlg.QueryResult.setPlainText(self.querySet.formatSqlStatement(sqlView))
self.dlg.tabWidget.setCurrentIndex(3)
def useForQuery(self):
self.predefinedLayer = self.selectedLayer
self.resetDialog()
self.dlg.tabWidget.setCurrentIndex(1)
def saveForQuery(self):
for rowSel in (self.dlg.LayerList.selectedItems()):
self.selectedLayer = rowSel.text()
self.predefinedLayer = self.selectedLayer
def layerAddToMap(self):
keyGuess = self.PSQL.guessKeyField(self.selectedLayer,suggestion=self.dlg.KEYFIELD.currentText())
geomGuess = self.PSQL.guessGeometryField(self.selectedLayer,suggestion=self.dlg.GEOMETRYFIELD.currentText())
self.PSQL.loadView(self.selectedLayer,geomGuess,keyGuess)
def probeKeyGeom(self):
self.populateComboBox(self.dlg.KEYFIELD,self.PSQL.getKeyFields(self.selectedLayer),"",None)
self.populateComboBox(self.dlg.GEOMETRYFIELD,self.PSQL.getGeometryFields(self.selectedLayer),"",None)
def layerGetTable(self):
self.PSQL.tableResultGen(self.selectedLayer,"",self.dlg.TableResult)
self.dlg.tabWidget.setCurrentIndex(4)
def convertToTable(self):
if self.PSQL.isMaterializedView(self.selectedLayer) or self.PSQL.isView(self.selectedLayer):
self.toTableDlg.ask(self.selectedLayer)
def renameObject(self):
newLayer = renameDialog.rename(self.selectedLayer)
if newLayer:
error = self.PSQL.renameLayer(self.selectedLayer,newLayer)
if error:
QMessageBox.information(None, "ERROR:", error)
else:
self.updateLayerMenu()
def moveObject(self):
schemas = self.PSQL.getSchemas()
schemas.remove(self.PSQL.getSchema())
schemas.insert(0,self.PSQL.getSchema())
newSchema = moveDialog.getNewSchema(self)
if newSchema:
error = self.PSQL.moveLayer(self.selectedLayer,newSchema)
if error:
QMessageBox.information(None, "ERROR:", error)
else:
self.updateLayerMenu()
def layerDelete(self, cascade = None):
msg = "Are you sure you want to delete layer '%s' from schema '%s' ?" % (self.selectedLayer,self.PSQL.getSchema())
reply = QMessageBox.question(None, 'Message', msg, QMessageBox.Yes, QMessageBox.No)
if reply == QMessageBox.Yes:
result = self.PSQL.deleteLayer(self.selectedLayer)
if result:
if "DROP ... CASCADE" in result:
msg = result+"\n\nLayer '%s' has dependencies. Do you want to remove all recursively ?" % self.selectedLayer
reply = QMessageBox.question(None, 'Message', msg, QMessageBox.Yes, QMessageBox.No)
if reply == QMessageBox.Yes:
result = self.PSQL.deleteLayer(self.selectedLayer,cascade = True)
if result:
QMessageBox.information(None, "ERROR:", result)
else:
print "CASCADE DELETED", self.selectedLayer
else:
QMessageBox.information(None, "ERROR:", result)
else:
print "DELETED", self.selectedLayer
pass
self.populateLayerMenu()
def layerRefresh(self):
if self.PSQL.isMaterializedView(self.selectedLayer):
self.PSQL.refreshMaterializedView(self.selectedLayer)
def recurseChild(self,slot,tab):
# for testing: prints qt object tree
for child in slot.children():
#print tab,"|",child.objectName()
if child.children() != []:
self.recurseChild(child,tab + " ")
def uncheckList(self,slot):
for row in range(0,slot.count()):
self.dlg.LayerList.item(row).setCheckState(Qt.Unchecked)
def addNewSchema(self):
newSchema = renameDialog.rename("new_schema")
error = self.PSQL.addSchema(newSchema)
if error:
QMessageBox.information(None, "ERROR:", error)
else:
self.populateComboBox(self.dlg.DBSchema,self.PSQL.getSchemas(),"Select schema",True)
def disableDialogSlot(self,slot):
for child in self.dlg.tabWidget.widget(1).children():
if child.objectName() == slot:
child.setDisabled(True)
def hideDialogSlot(self,slot):
for child in self.dlg.tabWidget.widget(1).children():
if child.objectName() == slot:
child.hide()
def showDialogSlot(self,slot):
for child in self.dlg.tabWidget.widget(1).children():
if child.objectName() == slot:
child.show()
def enableDialogSlot(self,slot):
for child in self.dlg.tabWidget.widget(1).children():
if child.objectName() == slot:
child.setEnabled(True)
def clearDialogSlot(self,slot):
for child in self.dlg.tabWidget.widget(1).children():
if child.objectName() == slot:
child.clear()
def checkCreateView(self):
#method called when checkbox createview is clicked
if self.dlg.checkCreateView.checkState():
self.dlg.QueryName.setEnabled(True)
self.dlg.checkMaterialized.setEnabled(True)
else:
self.dlg.QueryName.setDisabled(True)
self.dlg.checkMaterialized.setDisabled(True)
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def helpDialog(self):
print "called help"
if self.dlg.QueryType.currentText() != "Select query type":
QDesktopServices.openUrl(QUrl(self.querySet.getHelp()))
#webbrowser.open_new(self.querySet.getHelp())
def helpLinkOpen(self):
QDesktopServices.openUrl(QUrl("https://geogear.wordpress.com/postgisquerybuilder-v1-6-cheatsheet/"))
def setMaterialized(self):
self.queryGen()
def keyGeomFieldsChanged(self):
self.querySet.setParameter("GEOMETRYFIELD",self.dlg.GEOMETRYFIELD.currentText())
self.querySet.setParameter("KEYFIELD",self.dlg.KEYFIELD.currentText())
test = None
try:
test = self.querySet.testQueryParametersCheckList()
except:
pass
if test:
self.queryGen()
def tabChangedHub(self,tab):
#print "TAB:",tab
if tab == 0:
try:
self.updateLayerMenu()
except:
pass
try:
self.keyGeomFieldsChanged()
except:
pass
if tab == 3:
self.queryGen()
if tab == 2:
self.updateOrderBy()
elif tab == 5:
self.updateHistoryLog()
def updateOrderBy(self):
if self.dlg.LAYERa.currentText()[:6] != "Select":
try:
self.populateComboBox(self.dlg.orderBy,self.PSQL.getFieldsContent(self.dlg.LAYERa.currentText())," ",True)
except:
pass
def focusOnQuery(self):
self.dlg.tabWidget.setCurrentIndex(3)
def updateHistoryLog(self):
historyFile = os.path.join(os.path.dirname(__file__),"validSql.log")
if os.path.exists(historyFile):
in_file = open(historyFile,"r")
self.dlg.historyLog.setPlainText(in_file.read())
self.dlg.historyLog.moveCursor(QTextCursor.End)
def populateComboBox(self,combo,list,predef,sort):
#procedure to fill specified combobox with provided list
combo.blockSignals (True)
combo.clear()
model=QStandardItemModel(combo)
predefInList = None
for elem in list:
try:
item = QStandardItem(unicode(elem))
except TypeError:
item = QStandardItem(str(elem))
model.appendRow(item)
if elem == predef:
predefInList = elem
if sort:
model.sort(0)
combo.setModel(model)
if predef != "":
if predefInList:
combo.setCurrentIndex(combo.findText(predefInList))
else:
combo.insertItem(0,predef)
combo.setCurrentIndex(0)
combo.blockSignals (False)
def loadSVG(self,svgName):
self.dlg.DiagPanel.show()
svgFile = os.path.join( os.path.dirname( os.path.abspath( __file__ ) ), "svg",svgName + ".svg")
#print svgFile
item = QGraphicsSvgItem(svgFile)
scene= QGraphicsScene()
scene.addItem(item)
self.dlg.DiagPanel.setScene(scene)
self.dlg.DiagPanel.fitInView(item,Qt.KeepAspectRatio) #,Qt.KeepAspectRatio
def setLAYERa(self):
#called when LAYERa is activated
if self.dlg.LAYERa.currentText()[:6] == "Select":
return
self.querySet.setParameter("LAYERa",self.dlg.LAYERa.currentText())
#try to guess layer geometry field
autoGeometry = self.PSQL.guessGeometryField(self.dlg.LAYERa.currentText(),suggestion=self.dlg.GEOMETRYFIELD.currentText())
self.querySet.setParameter("GEOMETRYFIELDa",autoGeometry)
if self.querySet.testQueryParametersCheckList():
self.queryGen()
self.populateComboBox(self.dlg.FIELD,self.PSQL.getFieldsContent(self.dlg.LAYERa.currentText()),"Select field",True)
if not self.PSQL.testIfFieldExist(self.dlg.LAYERa.currentText(),self.querySet.getParameter("KEYFIELD")):
self.querySet.setFIDFIELD()
self.addListToFieldTable(self.dlg.fieldsListA,self.PSQL.getFieldsContent(self.dlg.LAYERa.currentText()),True, exclude=autoGeometry)
self.populateFilterTable()
self.setSCHEMAb()
def selectAllFields(self):
for row in range(0,self.dlg.fieldsListA.count()):
if self.dlg.fieldsListA.item(row).flags() & Qt.ItemIsEnabled:
self.dlg.fieldsListA.item(row).setCheckState(self.dlg.LAYERaAllFields.checkState())
self.setFieldsList()
def setSCHEMAb(self):
#called when SCHEMAb is activated
self.querySet.setParameter("SCHEMAb",self.dlg.SCHEMAb.currentText())
self.populateComboBox(self.dlg.LAYERb,self.PSQL.getLayers(schema = self.dlg.SCHEMAb.currentText()),"Select layer",True)
def setFILTERSCHEMA(self):
#called when FILTERSCHEMA is activated
self.dlg.filterTable.setCurrentSchema(self.dlg.FILTERSCHEMA.currentText())
def setLAYERb(self):
#called when LAYERb is activated
if self.dlg.LAYERb.currentText()[:6] == "Select":
return
self.querySet.setParameter("LAYERb",self.dlg.LAYERb.currentText())
#try to guess layer geometry field
autoGeometry = self.PSQL.guessGeometryField(self.dlg.LAYERb.currentText(),suggestion=self.dlg.GEOMETRYFIELD.currentText())
self.querySet.setParameter("GEOMETRYFIELDb",autoGeometry)
if self.querySet.testQueryParametersCheckList():
self.queryGen()
self.addListToFieldTable(self.dlg.fieldsListB,self.PSQL.getFieldsContent(self.dlg.LAYERb.currentText()),True)
self.populateComboBox(self.dlg.FIELDb,self.PSQL.getFieldsContent(self.dlg.LAYERb.currentText()),"Select field",True)
def addListToFieldTable(self,fieldSlot,fl,check,exclude = None):
#called to populate field list for WHERE statement
wdgt=fieldSlot
wdgt.clear()
#print "LAYERLIST:",check
for row in fl:
item=QListWidgetItem()
if check:
item.setFlags(item.flags() | Qt.ItemIsUserCheckable)
item.setCheckState(Qt.Unchecked)
item.setText(row)
#print row
if self.PSQL.isTable(row):
item.setIcon(QIcon(":/plugins/postgisquerybuilder/iT.png"))
elif self.PSQL.isView(row):
item.setIcon(QIcon(":/plugins/postgisquerybuilder/iV.png"))
elif self.PSQL.isMaterializedView(row):
item.setIcon(QIcon(":/plugins/postgisquerybuilder/iM.png"))
#disable geometryfield and auto set checked/unchecked
wdgt.addItem(item)
if check and exclude and row == exclude: #self.geoQuery and
if self.geoQuery:
item.setCheckState(Qt.Unchecked)
else:
item.setCheckState(Qt.Checked)
item.setFlags(item.flags() & (~Qt.ItemIsEnabled))
#item.setFlags(item.flags() | Qt.ItemIsEnabled)
def setFieldsList(self):
# procedure to resume selected fields to SELECT statements
items = []
for index in xrange(self.dlg.fieldsListA.count()):
if self.dlg.fieldsListA.item(index).checkState() == Qt.Checked:
items.append('"'+self.dlg.LAYERa.currentText()+'"."'+self.dlg.fieldsListA.item(index).text()+'"')
for index in xrange(self.dlg.fieldsListB.count()):
if self.dlg.fieldsListB.item(index).checkState() == Qt.Checked:
items.append('"'+self.dlg.LAYERb.currentText()+'"."'+self.dlg.fieldsListB.item(index).text()+'"')
#print "SELECTED FIELDS",items
self.querySet.setFieldsSet(items)
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setFIELD(self):
if self.dlg.FIELD.currentText()[:6] == "Select":
return
self.querySet.setParameter("FIELD",'"'+self.dlg.LAYERa.currentText()+'"."'+self.dlg.FIELD.currentText()+'"')
self.querySet.setParameter("SIMPLEFIELD",self.dlg.FIELD.currentText())
fType = self.PSQL.getFieldsType(self.querySet.getParameter("LAYERa"),self.dlg.FIELD.currentText())
fType = fType[:4]
#print fType
if ((fType == "char") or (fType == "text") or (fType == "varc")):
self.tDelimiter = "'"
else:
self.tDelimiter = ""
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setFIELDb(self):
if self.dlg.FIELDb.currentText()[:6] == "Select":
return
self.querySet.setParameter("FIELDb",'"'+self.dlg.LAYERb.currentText()+'"."'+self.dlg.FIELDb.currentText()+'"')
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setSPATIALREL(self):
if self.dlg.SPATIALREL.currentText()[:6] == "Select":
return
self.querySet.setParameter("SPATIALREL",self.dlg.SPATIALREL.currentText())
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setBUFFERRADIUS(self):
self.querySet.setParameter("BUFFERRADIUS",self.dlg.BUFFERRADIUS.text())
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setSPATIALRELNOT(self):
if self.dlg.SPATIALRELNOT.isChecked():
self.querySet.setParameter("SPATIALRELNOT","NOT ")
else:
self.querySet.setParameter("SPATIALRELNOT"," ")
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setJOIN(self):
self.querySet.setParameter("JOIN",self.dlg.JOIN.currentText())
if self.querySet.testQueryParametersCheckList():
self.queryGen()
def setQueryName(self):
try:
self.querySet.testIfQueryDefined()
except:
return
self.querySet.setParameter("VIEWNAME",self.dlg.QueryName.text())
if self.querySet.testQueryParametersCheckList():
self.dlg.QueryResult.setPlainText(self.querySet.getQueryParsed(self.dlg.checkCreateView.checkState()))
def queryGen(self):
if self.dlg.checkCreateView.checkState():
self.enableDialogSlot("QueryName")
if not self.querySet.testIfQueryDefined():
return
if self.dlg.checkAutoCompiled.checkState():
if self.dlg.checkMaterialized.checkState():
self.querySet.setParameter("MATERIALIZED","MATERIALIZED")
else:
self.querySet.setParameter("MATERIALIZED","")
self.enableDialogSlot("QueryResult")
self.enableDialogSlot("checkCreateView")
self.enableDialogSlot("AddToMap")
try:
qName = self.querySet.getNameParsed()
except:
qName = self.dlg.QueryName.text()
self.dlg.QueryName.setText(qName)
self.querySet.setParameter("VIEWNAME", qName)
if self.dlg.filterTable.testIfSintaxOk():
self.querySet.setParameter("WHERE", self.dlg.filterTable.getWhereStatement())
self.querySet.setParameter("SPATIALFROM", self.dlg.filterTable.getSpatialFilterLayers(schema = self.PSQL.getSchema()))
else:
self.querySet.setParameter("WHERE", "")
if self.dlg.orderBy.currentText() != " ":
self.querySet.setParameter("ORDERBY", 'ORDER BY "'+self.dlg.orderBy.currentText()+'"')
else:
self.querySet.setParameter("ORDERBY", "")
self.dlg.QueryResult.setPlainText(self.querySet.getQueryParsed(self.dlg.checkCreateView.checkState()))
self.dlg.QueryName.textChanged.connect(self.setQueryName)
def setQueryType(self,line):
theQ = self.dlg.QueryType.currentText()
if theQ[:6] == "Select":
return
self.loadSVG(theQ.replace(" ","_"))
#self.querySet.resetParameters()
self.resetDialog()
self.dlg.summaryBox.show()
self.querySet.setCurrentQuery(theQ)
if theQ[:2]=="ST":
self.geoQuery = True
else:
self.geoQuery = None
for slot in self.querySet.getRequiredSlots():
#print slot
#self.enableDialogSlot(slot)
self.showDialogSlot(slot)
self.showDialogSlot(slot+"Label")
self.showDialogSlot(slot+"AllFields")
#print self.querySet.testQueryParametersCheckList()
#simulate click on checkbox to set required slot
self.dlg.SPATIALRELNOT.setCheckState(Qt.Checked)
self.dlg.SPATIALRELNOT.setCheckState(Qt.Unchecked)
self.dlg.Helper.setText(theQ+":\n"+self.querySet.getDescription())
self.loadSVG(theQ.replace(" ","_"))
if self.predefinedLayer:
self.dlg.LAYERa.setCurrentIndex(self.dlg.LAYERa.findText(self.predefinedLayer))
self.setLAYERa()
def resetDialog(self):
self.eventsDisconnect()
self.clearAllDialogs()
self.querySet.resetParameters()
#self.disableQueryDefSlot()
self.hideQueryDefSlot()
self.loadSVG("Select_query_type")
self.dlg.summaryBox.hide()
try:
tables = self.PSQL.getLayers()
except AttributeError:
pass
else:
self.populateComboBox(self.dlg.LAYERa,tables,"Select Layer",True)
self.populateComboBox(self.dlg.LAYERb,tables,"Select Layer",True)
self.geoQuery = None
self.populateComboBox(self.dlg.orderBy,[" "],"",True)
self.eventsConnect()
def hideQueryDefSlot(self):
toHide=["BUFFERRADIUS","FIELD",\
"SPATIALREL","SPATIALRELNOT","LAYERaLabel","BUFFERRADIUSLabel",\
"FIELD","FIELDLabel","SPATIALRELLabel",
"SPATIALRELNOTLabel","fieldsListALabel","fieldsListBLabel",\
"FIELDb","FIELDbLabel","JOIN","JOINLabel",\
"LAYERa","SCHEMAb","LAYERb","LAYERbLabel","summaryBox","LAYERaAllFields","fieldsListA","fieldsListB"]
for slot in toHide:
self.hideDialogSlot(slot)
def clearQueryDefSlot(self):
toClear=["LAYERa","LAYERb","BUFFERRADIUS","FIELD","FIELDb",\
"SPATIALREL","fieldsListA","fieldsListB",\
"JOIN"]
for slot in toClear:
self.clearDialogSlot(slot)
def disableQueryDefSlot(self):
toDisable=["LAYERa","LAYERb","BUFFERRADIUS","FIELD","FIELDb",\
"SPATIALREL",\
"SPATIALRELNOT","fieldsListA","fieldsListB",
"JOIN"]
for slot in toDisable:
self.disableDialogSlot(slot)
def clearAllDialogs(self):
self.dlg.LAYERa.clear()
self.dlg.LAYERb.clear()
self.dlg.BUFFERRADIUS.clear()
self.dlg.FIELD.clear()
self.dlg.QueryResult.clear()
self.dlg.QueryName.clear()
self.dlg.fieldsListA.clear()
self.dlg.fieldsListB.clear()
self.dlg.TableResult.clear()
self.dlg.SPATIALRELNOT.setCheckState(Qt.Unchecked)
self.dlg.LAYERaAllFields.setCheckState(Qt.Unchecked)
def getPSQLConnections(self):
try:
self.dlg.PSQLConnection.activated.disconnect(self.setConnection)
except:
pass
conn = self.PSQL.getConnections()
self.populateComboBox(self.dlg.PSQLConnection,conn,"Select connection",True)
self.hideQueryDefSlot()
self.dlg.PSQLConnection.activated.connect(self.setConnection)
def closeDialog(self):
self.resetDialog()
self.dlg.hide()
def resetForm(self):
self.resetDialog()
self.getPSQLConnections()
self.populateGui()
self.dlg.tabWidget.setCurrentIndex(0)
def setConnection(self):
if self.dlg.PSQLConnection.currentText()[:6] == "Select":
return
self.PSQL.setConnection(self.dlg.PSQLConnection.currentText())
#print "SCHEMAS",self.PSQL.getSchemas()
schemas = self.PSQL.getSchemas()
self.populateComboBox(self.dlg.DBSchema,schemas,"Select schema",True)
self.dlg.DBSchema.activated.connect(self.loadPSQLLayers)
for r in range (0,self.dlg.DBSchema.count()):
if self.dlg.DBSchema.itemText(r) == "public":
self.dlg.DBSchema.setCurrentIndex(r)
self.dlg.DBSchema.removeItem(0)
self.loadPSQLLayers()
#clone dbschema in schemab
self.populateComboBox(self.dlg.SCHEMAb,[self.dlg.DBSchema.itemText(i) for i in range(self.dlg.DBSchema.count())],self.dlg.DBSchema.currentText(),True)
self.querySet.setParameter("SCHEMAb",self.dlg.SCHEMAb.currentText())
self.populateComboBox(self.dlg.FILTERSCHEMA,[self.dlg.DBSchema.itemText(i) for i in range(self.dlg.DBSchema.count())],self.dlg.DBSchema.currentText(),True)
def loadPSQLLayers(self):
if self.dlg.DBSchema.currentText() != "Select schema":
self.PSQL.setSchema(self.dlg.DBSchema.currentText())
self.populateGui()
self.resetDialog()
#self.dlg.tabWidget.setCurrentIndex(1)
self.querySet.setSchema(self.dlg.DBSchema.currentText())
self.populateComboBox(self.dlg.GEOMETRYFIELD,self.PSQL.scanLayersForGeometry(),"",None)
self.populateComboBox(self.dlg.KEYFIELD,self.PSQL.scanLayersForPrimaryKey(),"",None)
#sync schemab con dbschema
self.dlg.SCHEMAb.setCurrentIndex(self.dlg.DBSchema.currentIndex())
self.querySet.setParameter("SCHEMAb",self.dlg.SCHEMAb.currentText())
self.populateLayerMenu()
def populateFilterTable(self):
self.dlg.filterTable.populateFilterTable(self.PSQL,self.dlg.LAYERa.currentText())
def testSignal(self,v1,v2):
#print "catch:", v1,v2
pass
def populateLayerMenu(self):
self.addListToFieldTable(self.dlg.LayerList,self.PSQL.getLayers(),None)
def updateLayerMenu(self):
if self.dlg.DBSchema.currentText() != "Select schema":
self.addListToFieldTable(self.dlg.LayerList,self.PSQL.getLayers(),None)
def runQuery(self):
#method to run generated query
if self.dlg.filterTable.testIfSintaxOk():
if self.dlg.AddToMap.checkState():
if self.dlg.checkCreateView.checkState(): #self.querySet.getParameter("VIEWNAME")
self.PSQL.submitQuery(self.dlg.QueryName.text(),self.dlg.QueryResult.toPlainText())
self.PSQL.loadView(self.dlg.QueryName.text(),self.querySet.getParameter("GEOMETRYFIELD"),self.querySet.getParameter("KEYFIELD"))
else:
self.PSQL.loadSql(self.dlg.QueryName.text(),self.dlg.QueryResult.toPlainText(),self.querySet.getParameter("GEOMETRYFIELD"),self.querySet.getParameter("KEYFIELD"))
else:
if self.dlg.checkCreateView.checkState():
self.PSQL.submitQuery(self.dlg.QueryName.text(),self.dlg.QueryResult.toPlainText())
sql = 'SELECT * FROM "%s"."%s"' % (self.PSQL.getSchema(),self.dlg.QueryName.text())
print sql
rows = self.PSQL.tableResultGen(self.dlg.LAYERa.currentText(),sql,self.dlg.TableResult)
self.PSQL.loadedLayerRefresh(self.dlg.QueryName.text())
else:
rows = self.PSQL.tableResultGen(self.dlg.LAYERa.currentText(),self.dlg.QueryResult.toPlainText(),self.dlg.TableResult)
self.dlg.labelRowsNumber.setText("Total rows: "+str(rows))
self.dlg.tabWidget.setCurrentIndex(4)
else:
QMessageBox.information(None, "FILTER ERROR:", "The Filter table is malformed")
self.dlg.tabWidget.setCurrentIndex(2)
def unload(self):
# Remove the plugin menu item and icon
self.iface.removePluginMenu(u"&postgisQueryBuilder", self.action)
self.iface.removeToolBarIcon(self.action)
self.iface.removeDockWidget(self.PQBdockwidget)
# run method that performs all the real work
def run(self):
# show/hide the widget
if self.PQBdockwidget.isVisible():
self.PQBdockwidget.hide()
else:
self.PQBdockwidget.show()
self.getPSQLConnections()
def __init__(self, db_name):
db = DataBase(db_name)
self.sql = PSQL(db)
