def __init__(self, toolbar, parent=None):
CartoDBUserDataDialog.__init__(self, toolbar, parent)
self.ui = Ui_NewSQL()
self.ui.setupUi(self)
self._initEditor()
self.ui.bar = QgsMessageBar()
self.ui.bar.setSizePolicy(QSizePolicy.Expanding, QSizePolicy.Fixed)
self.ui.verticalLayout.insertWidget(0, self.ui.bar)
self.ui.testBT.clicked.connect(self.testQuery)
self.ui.cancelBT.clicked.connect(self.reject)
self.ui.addLayerBT.clicked.connect(self.accept)
self.initUserConnection()
def __init__(self, toolbar, parent=None):
CartoDBUserDataDialog.__init__(self, toolbar, parent)
self.ui = Ui_NewSQL()
self.ui.setupUi(self)
self._initEditor()
self.ui.bar = QgsMessageBar()
self.ui.bar.setSizePolicy(QSizePolicy.Expanding, QSizePolicy.Fixed)
self.ui.verticalLayout.insertWidget(0, self.ui.bar)
self.ui.testBT.clicked.connect(self.testQuery)
self.ui.cancelBT.clicked.connect(self.reject)
self.ui.addLayerBT.clicked.connect(self.accept)
self.initUserConnection()
def __init__(self):
CartoDBConnectionsManager.__init__(self)
self.settings = QSettings()
self.ui = Ui_NewSQL()
self.ui.setupUi(self)
self._initEditor()
self.populateConnectionList()
self.ui.bar = QgsMessageBar()
self.ui.bar.setSizePolicy(QSizePolicy.Expanding, QSizePolicy.Fixed)
self.ui.verticalLayout.insertWidget(0, self.ui.bar)
self.ui.newConnectionBT.clicked.connect(self.openNewConnectionDialog)
self.ui.editConnectionBT.clicked.connect(self.editConnectionDialog)
self.ui.deleteConnectionBT.clicked.connect(self.deleteConnectionDialog)
self.ui.loadTableBT.clicked.connect(self.findTables)
self.ui.testBT.clicked.connect(self.testQuery)
self.ui.cancelBT.clicked.connect(self.reject)
self.ui.addLayerBT.clicked.connect(self.accept)
class CartoDBNewSQLDialog(CartoDBUserDataDialog):
def __init__(self, toolbar, parent=None):
CartoDBUserDataDialog.__init__(self, toolbar, parent)
self.ui = Ui_NewSQL()
self.ui.setupUi(self)
self._initEditor()
self.ui.bar = QgsMessageBar()
self.ui.bar.setSizePolicy(QSizePolicy.Expanding, QSizePolicy.Fixed)
self.ui.verticalLayout.insertWidget(0, self.ui.bar)
self.ui.testBT.clicked.connect(self.testQuery)
self.ui.cancelBT.clicked.connect(self.reject)
self.ui.addLayerBT.clicked.connect(self.accept)
self.initUserConnection()
def _initEditor(self):
self.ui.sqlEditor = QsciScintilla(self)
self.ui.sqlEditor.textChanged.connect(self.setValidQuery)
# Don't want to see the horizontal scrollbar at all
self.ui.sqlEditor.SendScintilla(QsciScintilla.SCI_SETHSCROLLBAR, 0)
self.ui.sqlEditor.setMarginLineNumbers(0, True)
self.ui.sqlEditor.setMarginWidth(0, "000")
self.ui.sqlEditor.setMarginsForegroundColor(QColor("#2468A7"))
# Brace matching: enable for a brace immediately before or after
# the current position.
self.ui.sqlEditor.setBraceMatching(QsciScintilla.SloppyBraceMatch)
# Current line visible with special background color
self.ui.sqlEditor.setCaretLineVisible(True)
self.ui.sqlEditor.setCaretLineBackgroundColor(QColor("#E4EEFF"))
lexer = QsciLexerSQL()
self.ui.sqlEditor.setLexer(lexer)
''' TODO autocomplete.
api = QsciAPIs(lexer)
api.add('aLongString')
api.add('aLongerString')
api.add('aDifferentString')
api.add('sOmethingElse')
api.prepare()
self.ui.sqlEditor.setAutoCompletionThreshold(1)
self.ui.sqlEditor.setAutoCompletionSource(QsciScintilla.AcsAPIs)
'''
sizePolicy = QSizePolicy(QSizePolicy.Expanding, QSizePolicy.Expanding)
sizePolicy.setHorizontalStretch(0)
sizePolicy.setVerticalStretch(0)
sizePolicy.setHeightForWidth(self.ui.sqlEditor.sizePolicy().hasHeightForWidth())
self.ui.sqlEditor.setSizePolicy(sizePolicy)
self.ui.splitter.setSizes([self.size().width() * 0.6, self.size().height() * 0.4])
self.ui.leftContainer.insertWidget(1, self.ui.sqlEditor)
def setTablesListItems(self, tables):
self.ui.tablesTree.clear()
self.ui.tablesTree.addTopLevelItems(tables)
return True
def getTablesListSelectedItems(self):
return [] # self.ui.tablesTree.selectedItems()
@pyqtSlot()
def findTables(self):
self.ui.testBT.setEnabled(True)
cl = CartoDBAPIKey(self.currentApiKey, self.currentUser)
try:
if not str(self.currentMultiuser) in ['true', '1', 'True']:
sqlTables = "SELECT CDB_UserTables() table_name"
res = cl.sql(
"WITH usertables AS (" + sqlTables + ") \
SELECT ut.table_name, c.column_name, c.data_type column_type \
FROM usertables ut \
JOIN information_schema.columns c ON c.table_name = ut.table_name \
WHERE c.data_type != 'USER-DEFINED' \
ORDER BY ut.table_name, c.column_name")
else:
sqlTables = "SELECT string_agg(privilege_type, ', ') AS privileges, table_schema, table_name \
FROM information_schema.role_table_grants tg \
JOIN ( \
SELECT DISTINCT u.usename \
FROM information_schema.tables t \
JOIN pg_catalog.pg_class c ON (t.table_name = c.relname) \
JOIN pg_catalog.pg_user u ON (c.relowner = u.usesysid) \
WHERE t.table_schema = '" + self.currentUser + "') u ON u.usename = tg.grantee \
WHERE table_schema NOT IN ('pg_catalog', 'information_schema', 'cartodb', 'public', 'cdb_importer') \
GROUP BY table_schema, table_name \
ORDER BY table_schema, table_name"
res = cl.sql(
"WITH usertables AS (" + sqlTables + ") \
SELECT ut.table_name, c.column_name, c.data_type column_type, ut.privileges \
FROM usertables ut \
JOIN information_schema.columns c ON c.table_name = ut.table_name \
WHERE c.data_type != 'USER-DEFINED' \
ORDER BY ut.table_name, c.column_name")
tables = []
oldTableName = None
parentTableItem = None
for table in res['rows']:
if table['table_name'] != oldTableName:
parentTableItem = QTreeWidgetItem()
oldTableName = table['table_name']
parentTableItem.setText(0, self.tr(oldTableName))
parentTableItem.setIcon(0, QIcon(":/plugins/qgis-cartodb/images/icons/layers.png"))
if str(self.currentMultiuser) in ['true', '1', 'True'] and table['privileges'] == 'SELECT':
parentTableItem.setTextColor(0, QColor('#999999'))
tables.append(parentTableItem)
tableItem = QTreeWidgetItem(parentTableItem)
tableItem.setText(0, self.tr(table['column_name']))
if str(self.currentMultiuser) in ['true', '1', 'True'] and table['privileges'] == 'SELECT':
tableItem.setTextColor(0, QColor('#999999'))
tableItem.setToolTip(0, self.tr(table['column_type']))
tableItem.setIcon(0, QIcon(":/plugins/qgis-cartodb/images/icons/text.png"))
if table['column_type'] == 'integer' or table['column_type'] == 'double precision':
tableItem.setIcon(0, QIcon(":/plugins/qgis-cartodb/images/icons/number.png"))
elif table['column_type'] == 'timestamp with time zone':
tableItem.setIcon(0, QIcon(":/plugins/qgis-cartodb/images/icons/calendar.png"))
self.setTablesListItems(tables)
except CartoDBException as e:
QgsMessageLog.logMessage('Some error ocurred getting tables: ' + str(e.args), 'CartoDB Plugin', QgsMessageLog.CRITICAL)
QMessageBox.information(self, QApplication.translate('CartoDBPlugin', 'Error'), QApplication.translate('CartoDBPlugin', 'Error getting tables'), QMessageBox.Ok)
self.ui.tablesTree.clear()
def testQuery(self):
self.ui.bar.clearWidgets()
self.ui.bar.pushMessage("Info", QApplication.translate('CartoDBPlugin', 'Validating Query'), level=QgsMessageBar.INFO)
sql = self.ui.sqlEditor.text()
if sql is None or sql == '':
self.ui.bar.clearWidgets()
self.ui.bar.pushMessage("Warning", "Please write the sql query", level=QgsMessageBar.WARNING, duration=5)
self.setValidQuery(False)
return
sql = 'SELECT count(cartodb_id) num, ST_Union(the_geom) the_geom FROM (' + sql + ') a'
cartoUrl = 'http://{}.cartodb.com/api/v2/sql?format=GeoJSON&q={}&api_key={}'.format(self.currentUser, sql, self.currentApiKey)
response = urlopen(cartoUrl)
result = json.loads(response.read())
self.ui.bar.clearWidgets()
if 'error' not in result:
self.ui.bar.pushMessage("Info", QApplication.translate('CartoDBPlugin', 'Query is valid'), level=QgsMessageBar.INFO, duration=5)
self.setValidQuery(True)
else:
if 'hint' in result:
self.ui.bar.pushMessage("Warning", result['hint'], level=QgsMessageBar.WARNING, duration=10)
for error in result['error']:
self.ui.bar.pushMessage("Error", error, level=QgsMessageBar.CRITICAL, duration=5)
self.setValidQuery(False)
def setValidQuery(self, valid=False):
self.ui.addLayerBT.setEnabled(valid)
def getQuery(self):
return self.ui.sqlEditor.text()
def showEvent(self, event):
worker = CartoDBPluginWorker(self, 'findTables')
worker.start()
class CartoDBNewSQLDialog(CartoDBUserDataDialog):
def __init__(self, toolbar, parent=None):
CartoDBUserDataDialog.__init__(self, toolbar, parent)
self.ui = Ui_NewSQL()
self.ui.setupUi(self)
self._initEditor()
self.ui.bar = QgsMessageBar()
self.ui.bar.setSizePolicy(QSizePolicy.Expanding, QSizePolicy.Fixed)
self.ui.verticalLayout.insertWidget(0, self.ui.bar)
self.ui.testBT.clicked.connect(self.testQuery)
self.ui.cancelBT.clicked.connect(self.reject)
self.ui.addLayerBT.clicked.connect(self.accept)
self.initUserConnection()
def _initEditor(self):
self.ui.sqlEditor = QsciScintilla(self)
self.ui.sqlEditor.textChanged.connect(self.setValidQuery)
# Don't want to see the horizontal scrollbar at all
self.ui.sqlEditor.SendScintilla(QsciScintilla.SCI_SETHSCROLLBAR, 0)
self.ui.sqlEditor.setMarginLineNumbers(0, True)
self.ui.sqlEditor.setMarginWidth(0, "000")
self.ui.sqlEditor.setMarginsForegroundColor(QColor("#2468A7"))
# Brace matching: enable for a brace immediately before or after
# the current position.
self.ui.sqlEditor.setBraceMatching(QsciScintilla.SloppyBraceMatch)
# Current line visible with special background color
self.ui.sqlEditor.setCaretLineVisible(True)
self.ui.sqlEditor.setCaretLineBackgroundColor(QColor("#E4EEFF"))
lexer = QsciLexerSQL()
self.ui.sqlEditor.setLexer(lexer)
''' TODO autocomplete.
api = QsciAPIs(lexer)
api.add('aLongString')
api.add('aLongerString')
api.add('aDifferentString')
api.add('sOmethingElse')
api.prepare()
self.ui.sqlEditor.setAutoCompletionThreshold(1)
self.ui.sqlEditor.setAutoCompletionSource(QsciScintilla.AcsAPIs)
'''
sizePolicy = QSizePolicy(QSizePolicy.Expanding, QSizePolicy.Expanding)
sizePolicy.setHorizontalStretch(0)
sizePolicy.setVerticalStretch(0)
sizePolicy.setHeightForWidth(
self.ui.sqlEditor.sizePolicy().hasHeightForWidth())
self.ui.sqlEditor.setSizePolicy(sizePolicy)
self.ui.splitter.setSizes(
[self.size().width() * 0.6,
self.size().height() * 0.4])
self.ui.leftContainer.insertWidget(1, self.ui.sqlEditor)
def setTablesListItems(self, tables):
self.ui.tablesTree.clear()
self.ui.tablesTree.addTopLevelItems(tables)
return True
def getTablesListSelectedItems(self):
return [] # self.ui.tablesTree.selectedItems()
@pyqtSlot()
def findTables(self):
self.ui.testBT.setEnabled(True)
cl = CartoDBAPIKey(self.currentApiKey, self.currentUser)
try:
if not str(self.currentMultiuser) in ['true', '1', 'True']:
sqlTables = "SELECT CDB_UserTables() table_name"
res = cl.sql("WITH usertables AS (" + sqlTables + ") \
SELECT ut.table_name, c.column_name, c.data_type column_type \
FROM usertables ut \
JOIN information_schema.columns c ON c.table_name = ut.table_name \
WHERE c.data_type != 'USER-DEFINED' \
ORDER BY ut.table_name, c.column_name")
else:
sqlTables = "SELECT string_agg(privilege_type, ', ') AS privileges, table_schema, table_name \
FROM information_schema.role_table_grants tg \
JOIN ( \
SELECT DISTINCT u.usename \
FROM information_schema.tables t \
JOIN pg_catalog.pg_class c ON (t.table_name = c.relname) \
JOIN pg_catalog.pg_user u ON (c.relowner = u.usesysid) \
WHERE t.table_schema = '" + self.currentUser + "') u ON u.usename = tg.grantee \
WHERE table_schema NOT IN ('pg_catalog', 'information_schema', 'cartodb', 'public', 'cdb_importer') \
GROUP BY table_schema, table_name \
ORDER BY table_schema, table_name"
res = cl.sql("WITH usertables AS (" + sqlTables + ") \
SELECT ut.table_name, c.column_name, c.data_type column_type, ut.privileges \
FROM usertables ut \
JOIN information_schema.columns c ON c.table_name = ut.table_name \
WHERE c.data_type != 'USER-DEFINED' \
ORDER BY ut.table_name, c.column_name")
tables = []
oldTableName = None
parentTableItem = None
for table in res['rows']:
if table['table_name'] != oldTableName:
parentTableItem = QTreeWidgetItem()
oldTableName = table['table_name']
parentTableItem.setText(0, self.tr(oldTableName))
parentTableItem.setIcon(
0,
QIcon(
":/plugins/qgis-cartodb/images/icons/layers.png"))
if str(self.currentMultiuser) in [
'true', '1', 'True'
] and table['privileges'] == 'SELECT':
parentTableItem.setTextColor(0, QColor('#999999'))
tables.append(parentTableItem)
tableItem = QTreeWidgetItem(parentTableItem)
tableItem.setText(0, self.tr(table['column_name']))
if str(self.currentMultiuser) in [
'true', '1', 'True'
] and table['privileges'] == 'SELECT':
tableItem.setTextColor(0, QColor('#999999'))
tableItem.setToolTip(0, self.tr(table['column_type']))
tableItem.setIcon(
0, QIcon(":/plugins/qgis-cartodb/images/icons/text.png"))
if table['column_type'] == 'integer' or table[
'column_type'] == 'double precision':
tableItem.setIcon(
0,
QIcon(
":/plugins/qgis-cartodb/images/icons/number.png"))
elif table['column_type'] == 'timestamp with time zone':
tableItem.setIcon(
0,
QIcon(
":/plugins/qgis-cartodb/images/icons/calendar.png")
)
self.setTablesListItems(tables)
except CartoDBException as e:
QgsMessageLog.logMessage(
'Some error ocurred getting tables: ' + str(e.args),
'CartoDB Plugin', QgsMessageLog.CRITICAL)
QMessageBox.information(
self, QApplication.translate('CartoDBPlugin', 'Error'),
QApplication.translate('CartoDBPlugin',
'Error getting tables'), QMessageBox.Ok)
self.ui.tablesTree.clear()
def testQuery(self):
self.ui.bar.clearWidgets()
self.ui.bar.pushMessage("Info",
QApplication.translate('CartoDBPlugin',
'Validating Query'),
level=QgsMessageBar.INFO)
sql = self.ui.sqlEditor.text()
if sql is None or sql == '':
self.ui.bar.clearWidgets()
self.ui.bar.pushMessage("Warning",
"Please write the sql query",
level=QgsMessageBar.WARNING,
duration=5)
self.setValidQuery(False)
return
sql = 'SELECT count(cartodb_id) num, ST_Union(the_geom) the_geom FROM (' + sql + ') a'
cartoUrl = 'https://maps.geografia.com.au/user/{}/api/v2/sql?format=GeoJSON&q={}&api_key={}'.format(
self.currentUser, sql, self.currentApiKey)
response = urlopen(cartoUrl)
result = json.loads(response.read())
self.ui.bar.clearWidgets()
if 'error' not in result:
self.ui.bar.pushMessage("Info",
QApplication.translate(
'CartoDBPlugin', 'Query is valid'),
level=QgsMessageBar.INFO,
duration=5)
self.setValidQuery(True)
else:
if 'hint' in result:
self.ui.bar.pushMessage("Warning",
result['hint'],
level=QgsMessageBar.WARNING,
duration=10)
for error in result['error']:
self.ui.bar.pushMessage("Error",
error,
level=QgsMessageBar.CRITICAL,
duration=5)
self.setValidQuery(False)
def setValidQuery(self, valid=False):
self.ui.addLayerBT.setEnabled(valid)
def getQuery(self):
return self.ui.sqlEditor.text()
def showEvent(self, event):
worker = CartoDBPluginWorker(self, 'findTables')
worker.start()
