def _uneditableFields(self):
schema = 'public' if self.owner is None else self.owner
sql = "SELECT table_name, column_name, column_default, is_nullable, data_type, table_schema \
FROM information_schema.columns \
WHERE data_type != 'USER-DEFINED' AND table_schema = '" + schema + "' AND table_name = '" + self.cartoTable + "' \
ORDER BY ordinal_position"
cl = CartoDBAPIKey(self._apiKey, self.user)
try:
res = cl.sql(sql, True, True)
for pgField in res['rows']:
if pgField['data_type'] == 'timestamp with time zone':
self.setEditorWidgetV2(self.fieldNameIndex(pgField['column_name']), 'DateTime')
self.setEditorWidgetV2Config(self.fieldNameIndex(pgField['column_name']), {
u'display_format': u'yyyy-MM-dd hh:mm:ss',
u'field_format': u'yyyy-MM-dd hh:mm:ss',
u'calendar_popup': True})
elif pgField['data_type'] == 'boolean':
self.setEditorWidgetV2(self.fieldNameIndex(pgField['column_name']), 'CheckBox')
self.setEditorWidgetV2Config(self.fieldNameIndex(pgField['column_name']), {
u'CheckedState': u'true',
u'UncheckedState': u'false'})
except CartoDBException as e:
QgsMessageLog.logMessage(errorMsg + ' - ' + str(e), 'CartoDB Plugin', QgsMessageLog.CRITICAL)
self.setFieldEditable(self.fieldNameIndex('cartodb_id'), False)
self.setEditorWidgetV2(self.fieldNameIndex('cartodb_id'), 'Hidden')
self.setFieldEditable(self.fieldNameIndex('updated_at'), False)
self.setEditorWidgetV2(self.fieldNameIndex('updated_at'), 'Hidden')
self.setFieldEditable(self.fieldNameIndex('created_at'), False)
self.setEditorWidgetV2(self.fieldNameIndex('created_at'), 'Hidden')
self.setFieldEditable(self.fieldNameIndex('OGC_FID'), False)
self.setFieldEditable(self.fieldNameIndex('GEOMETRY'), False)
def _updateSQL(self, sql, errorMsg):
# QgsMessageLog.logMessage('SQL: ' + str(sql), 'CartoDB Plugin', QgsMessageLog.INFO)
cl = CartoDBAPIKey(self._apiKey, self.user)
try:
res = cl.sql(sql, True, True)
QgsMessageLog.logMessage('Result: ' + str(res), 'CartoDB Plugin', QgsMessageLog.INFO)
return res
except CartoDBException as e:
QgsMessageLog.logMessage(errorMsg + ' - ' + str(e), 'CartoDB Plugin', QgsMessageLog.CRITICAL)
self.iface.messageBar().pushMessage('Error!!', errorMsg, level=self.iface.messageBar().CRITICAL, duration=10)
return e
def findTables(self):
# Get tables from CartoDB.
self.currentUser = self.ui.connectionList.currentText()
self.currentApiKey = self.settings.value('/CartoDBPlugin/%s/api' % self.currentUser)
cl = CartoDBAPIKey(self.currentApiKey, self.currentUser)
try:
res = cl.sql("SELECT CDB_UserTables() order by 1")
tables = []
for table in res['rows']:
tables.append(table['cdb_usertables'])
QgsMessageLog.logMessage('This account has ' + str(len(tables)) + ' tables', 'CartoDB Plugin', QgsMessageLog.INFO)
self.setTablesListItems(tables)
self.settings.setValue('/CartoDBPlugin/selected', self.currentUser)
except CartoDBException as e:
QgsMessageLog.logMessage('Some error ocurred getting tables', 'CartoDB Plugin', QgsMessageLog.CRITICAL)
QMessageBox.information(self, self.tr('Error'), self.tr('Error getting tables'), QMessageBox.Ok)
self.ui.tablesList.clear()
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 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()
