QGIS API Documentation  3.27.0-Master (bef583a8ef)
qgsalgorithmexporttopostgresql.cpp
Go to the documentation of this file.
1 /***************************************************************************
2  qgsalgorithmexporttopostgresql.cpp
3  ---------------------
4  begin : November 2021
5  copyright : (C) 2021 by Clemens Raffler
6  email : clemens dot raffler at gmail dot com
7  ***************************************************************************/
8 
9 /***************************************************************************
10  * *
11  * This program is free software; you can redistribute it and/or modify *
12  * it under the terms of the GNU General Public License as published by *
13  * the Free Software Foundation; either version 2 of the License, or *
14  * (at your option) any later version. *
15  * *
16  ***************************************************************************/
17 
19 #include "qgsproviderregistry.h"
20 #include "qgsprovidermetadata.h"
22 #include "qgsvectorfilewriter.h"
23 
25 
26 QString QgsExportToPostgresqlAlgorithm::name() const
27 {
28  return QStringLiteral( "importintopostgis" );
29 }
30 
31 QString QgsExportToPostgresqlAlgorithm::displayName() const
32 {
33  return QStringLiteral( "Export to PostgreSQL" );
34 }
35 
36 QStringList QgsExportToPostgresqlAlgorithm::tags() const
37 {
38  return QObject::tr( "export,import,postgis,table,layer,into,copy" ).split( ',' );
39 }
40 
41 QString QgsExportToPostgresqlAlgorithm::group() const
42 {
43  return QStringLiteral( "Database" );
44 }
45 
46 QString QgsExportToPostgresqlAlgorithm::groupId() const
47 {
48  return QStringLiteral( "database" );
49 }
50 
51 void QgsExportToPostgresqlAlgorithm::initAlgorithm( const QVariantMap & )
52 {
53  addParameter( new QgsProcessingParameterFeatureSource( QStringLiteral( "INPUT" ), QObject::tr( "Layer to export" ), QList<int>() << QgsProcessing::TypeVector ) );
54  addParameter( new QgsProcessingParameterProviderConnection( QStringLiteral( "DATABASE" ), QObject::tr( "Database (connection name)" ), QStringLiteral( "postgres" ) ) );
55  addParameter( new QgsProcessingParameterDatabaseSchema( QStringLiteral( "SCHEMA" ), QObject::tr( "Schema (schema name)" ), QStringLiteral( "DATABASE" ), QStringLiteral( "public" ), true ) );
56  addParameter( new QgsProcessingParameterDatabaseTable( QStringLiteral( "TABLENAME" ), QObject::tr( "Table to export to (leave blank to use layer name)" ), QStringLiteral( "DATABASE" ), QStringLiteral( "SCHEMA" ), QVariant(), true, true ) );
57  addParameter( new QgsProcessingParameterField( QStringLiteral( "PRIMARY_KEY" ), QObject::tr( "Primary key field" ), QVariant(), QStringLiteral( "INPUT" ), QgsProcessingParameterField::Any, false, true ) );
58  addParameter( new QgsProcessingParameterString( QStringLiteral( "GEOMETRY_COLUMN" ), QObject::tr( "Geometry column" ), QStringLiteral( "geom" ) ) );
59  addParameter( new QgsProcessingParameterString( QStringLiteral( "ENCODING" ), QObject::tr( "Encoding" ), QStringLiteral( "UTF-8" ), false, true ) );
60  addParameter( new QgsProcessingParameterBoolean( QStringLiteral( "OVERWRITE" ), QObject::tr( "Overwrite" ), true ) );
61  addParameter( new QgsProcessingParameterBoolean( QStringLiteral( "CREATEINDEX" ), QObject::tr( "Create spatial index" ), true ) );
62  addParameter( new QgsProcessingParameterBoolean( QStringLiteral( "LOWERCASE_NAMES" ), QObject::tr( "Convert field names to lowercase" ), true ) );
63  addParameter( new QgsProcessingParameterBoolean( QStringLiteral( "DROP_STRING_LENGTH" ), QObject::tr( "Drop length constraints on character fields" ), false ) );
64  addParameter( new QgsProcessingParameterBoolean( QStringLiteral( "FORCE_SINGLEPART" ), QObject::tr( "Create single-part geometries instead of multipart" ), false ) );
65 }
66 
67 QString QgsExportToPostgresqlAlgorithm::shortHelpString() const
68 {
69  return QObject::tr( "This algorithm exports a vector layer to a PostgreSQL "
70  "database, creating a new table.\n\n"
71  "Prior to this a connection between QGIS and the PostgreSQL "
72  "database has to be created (for example through the QGIS Browser panel)."
73  );
74 }
75 
76 QString QgsExportToPostgresqlAlgorithm::shortDescription() const
77 {
78  return QObject::tr( "Exports a vector layer to a PostgreSQL database" );
79 }
80 
81 QgsExportToPostgresqlAlgorithm *QgsExportToPostgresqlAlgorithm::createInstance() const
82 {
83  return new QgsExportToPostgresqlAlgorithm();
84 }
85 
86 bool QgsExportToPostgresqlAlgorithm::prepareAlgorithm( const QVariantMap &parameters, QgsProcessingContext &context, QgsProcessingFeedback * )
87 {
88  mSource.reset( parameterAsSource( parameters, QStringLiteral( "INPUT" ), context ) );
89  if ( !mSource )
90  throw QgsProcessingException( invalidSourceError( parameters, QStringLiteral( "INPUT" ) ) );
91 
92  const QString connectionName = parameterAsConnectionName( parameters, QStringLiteral( "DATABASE" ), context );
93 
94  try
95  {
96  QgsProviderMetadata *md = QgsProviderRegistry::instance()->providerMetadata( QStringLiteral( "postgres" ) );
97  mConn.reset( static_cast<QgsAbstractDatabaseProviderConnection *>( md->createConnection( connectionName ) ) );
98  }
100  {
101  QgsProcessingException( QObject::tr( "Could not retrieve connection details for %1" ).arg( connectionName ) );
102  }
103 
104  mSchema = parameterAsSchema( parameters, QStringLiteral( "SCHEMA" ), context );
105  mPrimaryKeyField = parameterAsString( parameters, QStringLiteral( "PRIMARY_KEY" ), context );
106  mEncoding = parameterAsString( parameters, QStringLiteral( "ENCODING" ), context );
107  mOverwrite = parameterAsBoolean( parameters, QStringLiteral( "OVERWRITE" ), context );
108 
109  mTable = parameterAsDatabaseTableName( parameters, QStringLiteral( "TABLENAME" ), context ).trimmed();
110  if ( mTable.isEmpty() )
111  {
112  mTable = mSource->sourceName();
113  mTable = mTable.replace( '.', '_' );
114  }
115  mTable = mTable.replace( ' ', QString() ).right( 63 );
116 
117  mGeomColumn = parameterAsString( parameters, QStringLiteral( "GEOMETRY_COLUMN" ), context );
118  if ( mGeomColumn.isEmpty() )
119  mGeomColumn = QStringLiteral( "geom" );
120  if ( mSource->wkbType() == QgsWkbTypes::NoGeometry )
121  mGeomColumn.clear();
122 
123  mCreateIndex = parameterAsBoolean( parameters, QStringLiteral( "CREATEINDEX" ), context );
124 
125  if ( mOverwrite )
126  mOptions[QStringLiteral( "overwrite" )] = true;
127  if ( parameterAsBoolean( parameters, QStringLiteral( "LOWERCASE_NAMES" ), context ) )
128  {
129  mOptions[QStringLiteral( "lowercaseFieldNames" )] = true;
130  mGeomColumn = mGeomColumn.toLower();
131  }
132  if ( parameterAsBoolean( parameters, QStringLiteral( "DROP_STRING_LENGTH" ), context ) )
133  mOptions[QStringLiteral( "dropStringConstraints" )] = true;
134  if ( parameterAsBoolean( parameters, QStringLiteral( "FORCE_SINGLEPART" ), context ) )
135  mOptions[QStringLiteral( "forceSinglePartGeometryType" )] = true;
136  if ( !mEncoding.isEmpty() )
137  mOptions[QStringLiteral( "fileEncoding" )] = mEncoding;
138 
139  return true;
140 }
141 
142 QVariantMap QgsExportToPostgresqlAlgorithm::processAlgorithm( const QVariantMap &parameters, QgsProcessingContext &context, QgsProcessingFeedback *feedback )
143 {
144  Q_UNUSED( parameters );
145  Q_UNUSED( context );
146 
147  QgsDataSourceUri uri = QgsDataSourceUri( mConn->uri() );
148  uri.setSchema( mSchema );
149  uri.setTable( mTable );
150  uri.setKeyColumn( mPrimaryKeyField );
151  uri.setGeometryColumn( mGeomColumn );
152 
153  std::unique_ptr< QgsVectorLayerExporter > exporter = std::make_unique< QgsVectorLayerExporter >( uri.uri(), mProviderName, mSource->fields(), mSource->wkbType(), mSource->sourceCrs(), mOverwrite, mOptions );
154 
155  if ( exporter->errorCode() != Qgis::VectorExportResult::Success )
156  throw QgsProcessingException( QObject::tr( "Error exporting to PostGIS\n%1" ).arg( exporter->errorMessage() ) );
157 
158  QgsFeatureIterator featureIterator = mSource->getFeatures();
159 
160  const double progressStep = ( mSource->featureCount() ) ? 100.0 / mSource->featureCount() : 0.0;
161 
162  qgssize i = 0;
163  QgsFeature f;
164  while ( featureIterator.nextFeature( f ) )
165  {
166  if ( feedback->isCanceled() )
167  break;
168 
169  if ( !exporter->addFeature( f, QgsFeatureSink::FastInsert ) )
170  feedback->reportError( exporter->errorMessage() );
171 
172  feedback->setProgress( i * progressStep );
173  i++;
174  }
175  exporter->flushBuffer();
176 
177  if ( exporter->errorCode() != Qgis::VectorExportResult::Success )
178  throw QgsProcessingException( QObject::tr( "Error exporting to PostGIS\n%1" ).arg( exporter->errorMessage() ) );
179 
180  exporter.reset();
181 
182  if ( !mGeomColumn.isEmpty() && mCreateIndex )
183  {
184  try
185  {
187  opt.geometryColumnName = mGeomColumn;
188  mConn->createSpatialIndex( mSchema, mTable, opt );
189  }
190  catch ( QgsProviderConnectionException &ex )
191  {
192  throw QgsProcessingException( QObject::tr( "Error creating spatial index:\n%1" ).arg( ex.what() ) );
193  }
194  }
195 
196  try
197  {
198  mConn->vacuum( mSchema, mTable );
199  }
200  catch ( QgsProviderConnectionException &ex )
201  {
202  feedback->reportError( QObject::tr( "Error vacuuming table:\n{0}" ).arg( ex.what() ) );
203  }
204 
205  QVariantMap outputs;
206  return outputs;
207 }
The QgsAbstractDatabaseProviderConnection class provides common functionality for DB based connection...
Class for storing the component parts of a RDBMS data source URI (e.g.
void setSchema(const QString &schema)
Sets the scheme for the URI.
void setTable(const QString &table)
Sets table to table.
void setGeometryColumn(const QString &geometryColumn)
Sets geometry column name to geometryColumn.
QString uri(bool expandAuthConfig=true) const
Returns the complete URI as a string.
void setKeyColumn(const QString &column)
Sets the name of the (primary) key column.
QString what() const
Definition: qgsexception.h:48
Wrapper for iterator of features from vector data provider or vector layer.
bool nextFeature(QgsFeature &f)
@ FastInsert
Use faster inserts, at the cost of updating the passed features to reflect changes made at the provid...
The feature class encapsulates a single feature including its unique ID, geometry and a list of field...
Definition: qgsfeature.h:56
bool isCanceled() const SIP_HOLDGIL
Tells whether the operation has been canceled already.
Definition: qgsfeedback.h:54
void setProgress(double progress)
Sets the current progress for the feedback object.
Definition: qgsfeedback.h:63
Contains information about the context in which a processing algorithm is executed.
Custom exception class for processing related exceptions.
Definition: qgsexception.h:83
Base class for providing feedback from a processing algorithm.
virtual void reportError(const QString &error, bool fatalError=false)
Reports that the algorithm encountered an error while executing.
A boolean parameter for processing algorithms.
A database schema parameter for processing algorithms, allowing users to select from existing schemas...
A database table name parameter for processing algorithms, allowing users to select from existing dat...
An input feature source (such as vector layers) parameter for processing algorithms.
A vector layer or feature source field parameter for processing algorithms.
A data provider connection parameter for processing algorithms, allowing users to select from availab...
A string parameter for processing algorithms.
@ TypeVector
Tables (i.e. vector layers with or without geometry). When used for a sink this indicates the sink ha...
Definition: qgsprocessing.h:54
Custom exception class for provider connection related exceptions.
Definition: qgsexception.h:101
Holds data provider key, description, and associated shared library file or function pointer informat...
virtual QgsAbstractProviderConnection * createConnection(const QString &uri, const QVariantMap &configuration) SIP_THROW(QgsProviderConnectionException)
Creates a new connection from uri and configuration, the newly created connection is not automaticall...
static QgsProviderRegistry * instance(const QString &pluginPath=QString())
Means of accessing canonical single instance.
QgsProviderMetadata * providerMetadata(const QString &providerKey) const
Returns metadata of the provider or nullptr if not found.
unsigned long long qgssize
Qgssize is used instead of size_t, because size_t is stdlib type, unknown by SIP, and it would be har...
Definition: qgis.h:2786
The SpatialIndexOptions contains extra options relating to spatial index creation.
QString geometryColumnName
Specifies the name of the geometry column to create the index for.