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
Beispiel #3
0
    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()
Beispiel #4
0
    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()
Beispiel #5
0
    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()