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 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()
print size, maxSize
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) ) \
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 upload(self, db, data_sources_items, maxSize):
import_ok = True
layers_to_replace = {}
self.status_bar.showMessage(pystring(self.tr("Uploading to database '{db}'...")).format(db=db.database))
QApplication.processEvents()
upload_count = 0
messages = ""
# Connect to database
try:
conn = db.psycopg_connection()
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
cursor = conn.cursor()
sql = "SELECT pg_size_pretty(pg_database_size('" + str(db.database) + "'))"
cursor.execute(sql)
size = int(cursor.fetchone()[0].split(' ')[0])
cursor.close()
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]
# 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 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()
# 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
if QGis.QGIS_VERSION_INT < 20900:
vectorLayerImport = PGVectorLayerImport(db, cloudUri, fields, wkbType, layer.crs(), True)
else:
vectorLayerImport = QgsVectorLayerImport(cloudUri, "postgres", fields, wkbType, layer.crs(), True)
if vectorLayerImport.hasError():
import_ok &= False
messages += vectorLayerImport.errorMessage() + "\n"
continue
# Create cursor
cursor = conn.cursor()
# Build import string
attribs = range(0, fields.count())
count = 0
importstr = ""
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")
continue
# 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 = "\\N"
elif val.type() == QVariant.Date or val.type() == QVariant.DateTime:
val = val.toString(Qt.ISODate)
if not val:
val = "\\N"
else:
val = 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 = "\\N"
elif isinstance(val, QDate) or isinstance(val, QDateTime):
val = val.toString(Qt.ISODate)
if not val:
val = "\\N"
else:
val = 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 += "\t" + val
importstr += "\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
cursor.close()
if ok:
try:
conn.commit()
upload_count += 1
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
}
conn.close()
self._replace_local_layers(layers_to_replace)
self.progress_label.setText("")
if import_ok:
return upload_count
else:
raise RuntimeError(messages)
