def refresh_databases(self): QApplication.setOverrideCursor(Qt.WaitCursor) if self.clouddb: db_list = self.api.read_databases() if self.show_api_error(db_list): QApplication.restoreOverrideCursor() return self.db_connections = DbConnections() for db in db_list: self.db_connections.add_from_json(db) self.ui.tabDatabases.clear() self.ui.btnDbDelete.setEnabled(False) self.ui.cbUploadDatabase.clear() self.ui.cbUploadDatabase.setEditable(True) self.ui.cbUploadDatabase.lineEdit().setReadOnly(True) if self.db_connections.count() == 0: self.ui.cbUploadDatabase.setEditText(self.tr("No databases")) else: for name, db in self.db_connections.iteritems(): it = QListWidgetItem(name) it.setToolTip(db.description()) self.ui.tabDatabases.addItem(it) self.ui.cbUploadDatabase.addItem(name) if self.ui.cbUploadDatabase.count() > 1: # Display the "Select database" text if more than one db is available self.ui.cbUploadDatabase.setCurrentIndex(-1) self.ui.cbUploadDatabase.setEditText( self.tr("Select database")) self.db_connections.refresh(self.user) self.db_size(self.db_connections) QApplication.restoreOverrideCursor()
def upload(self, db, data_sources_items, maxSize): import_ok = True layers_to_replace = {} raster_to_upload = {} self.status_bar.showMessage( pystring(self.tr("Uploading to database '{db}'...")).format( db=db.database)) QApplication.processEvents() messages = "" # Connect to database try: conn = db.psycopg_connection() cursor = conn.cursor() except Exception as e: raise RuntimeError("Connection to database failed %s" % str(e)) for data_source, item in data_sources_items.iteritems(): # Check available space, block if exceded size = DbConnections().db_size() if size > maxSize: QMessageBox.warning( None, self.tr("Database full"), self. tr("You have exceeded the maximum database size for your current QGIS Cloud plan. Please free up some space or upgrade your QGIS Cloud plan." )) break # Layers contains all layers with shared data source layer = item['layers'][0] if layer.type() == QgsMapLayer.VectorLayer: # The QgsFields() is to support the QGIS 1.x API, see apicompat/vectorapi.py fields = QgsFields(layer.pendingFields()) srid = layer.crs().postgisSrid() geom_column = "wkb_geometry" wkbType = layer.wkbType() if wkbType == QGis.WKBNoGeometry: cloudUri = "dbname='%s' host=%s port=%d user='******' password='******' key='' table=\"public\".\"%s\"" % ( db.database, db.host, db.port, db.username, db.password, item['table']) geom_column = "" else: if not QGis.isMultiType(wkbType): wkbType = QGis.multiType(wkbType) # Create table (pk='' => always generate a new primary key) cloudUri = "dbname='%s' host=%s port=%d user='******' password='******' key='' table=\"public\".\"%s\" (%s)" % ( db.database, db.host, db.port, db.username, db.password, item['table'], geom_column) self.progress_label.setText( pystring(self.tr("Creating table '{table}'...")).format( table=item['table'])) QApplication.processEvents() if wkbType != QGis.WKBNoGeometry: # Check if SRID is known on database, otherwise create record cursor.execute( "SELECT srid FROM public.spatial_ref_sys WHERE srid = %s" % layer.crs().postgisSrid()) if not cursor.fetchone(): try: cursor.execute( "INSERT INTO public.spatial_ref_sys VALUES ({srid},'EPSG',{srid},'{wktstr}','{projstr}')" .format(srid=layer.crs().postgisSrid(), wktstr=layer.crs().toWkt(), projstr=layer.crs().toProj4())) conn.commit() except Exception as e: conn.rollback() import_ok &= False messages += "Failed to create SRS record on database: " + str( e) + "\n" continue # cursor.close() # TODO: Ask user for overwriting existing table # The postgres provider is terribly slow at creating tables with # many attribute columns in QGIS < 2.9.0 vectorLayerImport = PGVectorLayerImport( db, conn, cursor, cloudUri, fields, wkbType, layer.crs(), True) if vectorLayerImport.hasError(): import_ok &= False messages += "VectorLayerImport-Error: " + vectorLayerImport.errorMessage( ) + "\n" continue vectorLayerImport = None # Build import string attribs = range(0, fields.count()) count = 0 importstr = bytearray() ok = True self.progress_label.setText(self.tr("Uploading features...")) QApplication.processEvents() for feature in layer.getFeatures(): # First field is primary key importstr += "%d" % count count += 1 if not feature.geometry(): QgsMessageLog.logMessage( pystring( self. tr("Feature {id} of layer {layer} has no geometry" )).format(id=feature.id(), layer=layer.name()), "QGISCloud") importstr += "\t" + b"\\N" elif QGis.multiType( feature.geometry().wkbType()) != wkbType: QgsMessageLog.logMessage( pystring( self. tr("Feature {id} of layer {layer} has wrong geometry type {type}" )).format( id=feature.id(), layer=layer.name(), type=QGis.featureType( feature.geometry().wkbType())), "QGISCloud") importstr += "\t" + b"\\N" else: # Second field is geometry in EWKB Hex format importstr += "\t" + self._wkbToEWkbHex( feature.geometry().asWkb(), srid) # Finally, copy data attributes for attrib in attribs: val = feature[attrib] if sipv1(): # QGIS 1.x if val is None or val.type( ) == QVariant.Invalid or val.isNull(): val = b"\\N" elif val.type() == QVariant.Date or val.type( ) == QVariant.DateTime: val = bytearray( val.toString(Qt.ISODate).encode('utf-8')) if not val: val = b"\\N" else: val = bytearray( unicode(val.toString()).encode('utf-8')) val = val.replace('\x00', '?') val = val.replace('\t', r"E'\t'") val = val.replace('\n', r"E'\n'") val = val.replace('\r', r"E'\r'") val = val.replace('\\', r"\\") else: # QGIS 2.x if val is None or isinstance(val, QPyNullVariant): val = b"\\N" elif isinstance(val, QDate) or isinstance( val, QDateTime): val = bytearray( val.toString(Qt.ISODate).encode('utf-8')) if not val: val = b"\\N" else: val = bytearray(unicode(val).encode('utf-8')) val = val.replace('\x00', '?') val = val.replace('\t', r"E'\t'") val = val.replace('\n', r"E'\n'") val = val.replace('\r', r"E'\r'") val = val.replace('\\', r"\\") importstr += b"\t" + val importstr += b"\n" # Upload in chunks if (count % 100) == 0: try: cursor.copy_from(StringIO(importstr), '"public"."%s"' % item['table']) except Exception as e: messages += str(e) + "\n" ok = False break importstr = "" self.progress_label.setText( pystring( self.tr("{table}: {count} features uploaded")). format(table=item['table'], count=count)) QApplication.processEvents() # Periodically update ui if (count % 10) == 0: QApplication.processEvents() if ok and importstr: try: cursor.copy_from(StringIO(importstr), '"public"."%s"' % item['table']) except Exception as e: messages += str(e) + "\n" ok = False if ok: try: conn.commit() except Exception as e: messages += str(e) + "\n" ok = False else: conn.rollback() import_ok &= ok if ok: for layer in item['layers']: layers_to_replace[layer.id()] = { 'layer': layer, 'data_source': data_source, 'db_name': db.database, 'table_name': item['table'], 'geom_column': geom_column } if wkbType != QGis.WKBNoGeometry: sql = 'create index "%s_%s_idx" on "public"."%s" using gist ("%s");' % ( item['table'], geom_column, item['table'], geom_column) cursor.execute(sql) elif layer.type() == QgsMapLayer.RasterLayer: raster_to_upload[layer.id()] = { 'layer': layer, 'data_source': layer.source(), 'db_name': db.database, 'table_name': item['table'], 'geom_column': 'rast' } RasterUpload(conn, cursor, raster_to_upload, maxSize, self.progress_label) layers_to_replace[layer.id()] = raster_to_upload[layer.id()] sql = "SELECT 'SELECT SETVAL(' || quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) || \ ', COALESCE(MAX(' ||quote_ident(C.attname)|| ')+1, 1) ) \ FROM ' || quote_ident(PGT.schemaname)|| '.' ||quote_ident(T.relname)|| ';' \ FROM pg_class AS S, pg_depend AS D, pg_class AS T, pg_attribute AS C, \ pg_tables AS PGT \ WHERE S.relkind = 'S' \ AND S.oid = D.objid \ AND D.refobjid = T.oid \ AND D.refobjid = C.attrelid \ AND D.refobjsubid = C.attnum \ AND T.relname = PGT.tablename \ AND schemaname = 'public' \ AND tablename = '%s' ORDER BY S.relname;" % (item['table']) cursor.execute(sql) rows = cursor.fetchall() for row in rows: cursor.execute(row[0]) cursor.close() conn.close() self._replace_local_layers(layers_to_replace) self.progress_label.setText("") if not import_ok: raise RuntimeError(messages)
def __init__(self, iface, version): QDockWidget.__init__(self, None) self.iface = iface self.clouddb = True self.version = version # Set up the user interface from Designer. self.ui = Ui_QgisCloudPlugin() self.ui.setupUi(self) self.storage_exceeded = True myAbout = DlgAbout() self.ui.aboutText.setText( myAbout.aboutString() + myAbout.contribString() + myAbout.licenseString() + "<p>Versions:<ul>" + "<li>QGIS: %s</li>" % unicode(QGis.QGIS_VERSION).encode("utf-8") + "<li>Python: %s</li>" % sys.version.replace("\n", " ") + "<li>OS: %s</li>" % platform.platform() + "</ul></p>") self.ui.lblVersionPlugin.setText(self.version) self.ui.tblLocalLayers.setColumnCount(5) header = [ "Layers", "Data source", "Table name", "Geometry type", "SRID" ] self.ui.tblLocalLayers.setHorizontalHeaderLabels(header) self.ui.tblLocalLayers.resizeColumnsToContents() self.ui.tblLocalLayers.setEditTriggers( QAbstractItemView.NoEditTriggers) self.ui.btnUploadData.setEnabled(False) self.ui.btnPublishMap.setEnabled(False) self.ui.progressWidget.hide() self.ui.btnLogout.hide() self.ui.lblLoginStatus.hide() self.ui.widgetServices.hide() self.ui.widgetDatabases.setEnabled(False) self.ui.labelOpenLayersPlugin.hide() try: if QGis.QGIS_VERSION_INT >= 20300: from openlayers_menu import OpenlayersMenu else: # QGIS 1.x - QGIS-2.2 from openlayers_menu_compat import OpenlayersMenu self.ui.btnBackgroundLayer.setMenu(OpenlayersMenu(self.iface)) except: self.ui.btnBackgroundLayer.hide() self.ui.labelOpenLayersPlugin.show() # map<data source, table name> self.data_sources_table_names = {} # flag to disable update of local data sources during upload self.do_update_local_data_sources = True QObject.connect(self.ui.btnLogin, SIGNAL("clicked()"), self.check_login) QObject.connect(self.ui.btnDbCreate, SIGNAL("clicked()"), self.create_database) QObject.connect(self.ui.btnDbDelete, SIGNAL("clicked()"), self.delete_database) QObject.connect(self.ui.btnDbRefresh, SIGNAL("clicked()"), self.refresh_databases) QObject.connect(self.ui.tabDatabases, SIGNAL("itemSelectionChanged()"), self.select_database) QObject.connect(self.ui.btnPublishMap, SIGNAL("clicked()"), self.publish_map) QObject.connect(self.ui.btnRefreshLocalLayers, SIGNAL("clicked()"), self.refresh_local_data_sources) QObject.connect(self.iface, SIGNAL("newProjectCreated()"), self.reset_load_data) QObject.connect(QgsMapLayerRegistry.instance(), SIGNAL("layerWillBeRemoved(QString)"), self.remove_layer) QObject.connect(QgsMapLayerRegistry.instance(), SIGNAL("layerWasAdded(QgsMapLayer *)"), self.add_layer) QObject.connect(self.ui.cbUploadDatabase, SIGNAL("currentIndexChanged(int)"), lambda idx: self.activate_upload_button()) QObject.connect(self.ui.btnUploadData, SIGNAL("clicked()"), self.upload_data) self.ui.editServer.textChanged.connect(self.serverURL) self.ui.resetUrlBtn.clicked.connect(self.resetApiUrl) self.read_settings() self.api = API() self.db_connections = DbConnections() self.local_data_sources = LocalDataSources() self.data_upload = DataUpload(self.iface, self.statusBar(), self.ui.lblProgress, self.api, self.db_connections) if self.URL == "": self.ui.editServer.setText(self.api.api_url()) else: self.ui.editServer.setText(self.URL) self.palette_red = QPalette(self.ui.lblVersionPlugin.palette()) self.palette_red.setColor(QPalette.WindowText, Qt.red)
def __init__(self, iface, version): QDockWidget.__init__(self, None) self.iface = iface self.version = version # Set up the user interface from Designer. self.ui = Ui_QgisCloudPlugin() self.ui.setupUi(self) self.ui.tblLocalLayers.setColumnCount(5) header = [ "Layers", "Data source", "Table name", "Geometry type", "SRID" ] self.ui.tblLocalLayers.setHorizontalHeaderLabels(header) self.ui.tblLocalLayers.resizeColumnsToContents() # TODO; delegate for read only columns self.ui.btnUploadData.setEnabled(False) self.ui.uploadProgressBar.hide() self.ui.btnPublishMapUpload.hide() self.ui.lblLoginStatus.hide() # map<data source, table name> self.data_sources_table_names = {} self.dbs_refreshed = False # flag to disable update of local data sources during upload self.do_update_local_data_sources = True QObject.connect(self.ui.btnLogin, SIGNAL("clicked()"), self.refresh_databases) QObject.connect(self.ui.btnDbCreate, SIGNAL("clicked()"), self.create_database) QObject.connect(self.ui.btnDbDelete, SIGNAL("clicked()"), self.delete_database) QObject.connect(self.ui.btnDbRefresh, SIGNAL("clicked()"), self.refresh_databases) QObject.connect(self.ui.tabDatabases, SIGNAL("itemSelectionChanged()"), self.select_database) QObject.connect(self.ui.btnPublishMap, SIGNAL("clicked()"), self.publish_map) QObject.connect(self.ui.btnRefreshLocalLayers, SIGNAL("clicked()"), self.refresh_local_data_sources) QObject.connect(self.iface, SIGNAL("newProjectCreated()"), self.reset_load_data) QObject.connect(QgsMapLayerRegistry.instance(), SIGNAL("layerWillBeRemoved(QString)"), self.remove_layer) QObject.connect(QgsMapLayerRegistry.instance(), SIGNAL("layerWasAdded(QgsMapLayer *)"), self.add_layer) QObject.connect(self.ui.cbUploadDatabase, SIGNAL("currentIndexChanged(int)"), self.upload_database_selected) QObject.connect(self.ui.btnUploadData, SIGNAL("clicked()"), self.upload_data) QObject.connect(self.ui.btnPublishMapUpload, SIGNAL("clicked()"), self.publish_map) self.read_settings() self.update_urls() self.api = API() self.db_connections = DbConnections() self.local_data_sources = LocalDataSources() self.data_upload = DataUpload(self.iface, self.statusBar(), self.ui.uploadProgressBar, self.api, self.db_connections) self.ui.editServer.setText(self.api.api_url())