Skip to content

Commit f020bbf

Browse files
jef-njef-n
committedMar 22, 2014
Merge pull request #1221 from luipir/spatialite-savestyle
added saveStyle capability to Spatialite Provider - Developed for ARPA Piemonte (Dipartimento Tematico Geologia e Dissesto)
·
ltr-3_40archive/master_2
2 parents e392efa + c44b091 commit f020bbf

File tree

2 files changed

+416
-0
lines changed

2 files changed

+416
-0
lines changed
 

‎src/providers/spatialite/qgsspatialiteprovider.cpp

Lines changed: 412 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -26,6 +26,8 @@ email : a.furieri@lqt.it
2626
#include "qgsmessagelog.h"
2727
#include "qgsvectorlayerimport.h"
2828

29+
#include <QMessageBox>
30+
2931
#include "qgsspatialiteprovider.h"
3032
#include "qgsspatialiteconnpool.h"
3133
#include "qgsspatialitefeatureiterator.h"
@@ -5144,3 +5146,413 @@ QgsAttributeList QgsSpatiaLiteProvider::pkAttributeIndexes()
51445146
return mPrimaryKeyAttrs;
51455147
}
51465148

5149+
// ---------------------------------------------------------------------------
5150+
5151+
QGISEXTERN bool saveStyle( const QString& uri, const QString& qmlStyle, const QString& sldStyle,
5152+
const QString& styleName, const QString& styleDescription,
5153+
const QString& uiFileContent, bool useAsDefault, QString& errCause )
5154+
{
5155+
QgsSqliteHandle *handle;
5156+
sqlite3 *sqliteHandle = NULL;
5157+
char **results;
5158+
int rows;
5159+
int columns;
5160+
char *errMsg = NULL;
5161+
5162+
QgsDataSourceURI dsUri( uri );
5163+
QString sqlitePath = dsUri.database();
5164+
QgsDebugMsg( "Database is: " + sqlitePath );
5165+
5166+
// trying to open the SQLite DB
5167+
spatialite_init( 0 );
5168+
handle = QgsSqliteHandle::openDb( sqlitePath );
5169+
if ( NULL == handle )
5170+
{
5171+
QgsDebugMsg( "Connection to database failed. Save style aborted." );
5172+
errCause = QObject::tr( "Connection to database failed" );
5173+
return QgsVectorLayerImport::ErrConnectionFailed;
5174+
}
5175+
5176+
sqliteHandle = handle->handle();
5177+
5178+
// check if layer_styles table already exist
5179+
QString countIfExist = QString( "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='%1';" ).arg( "layer_styles" );
5180+
5181+
int ret = sqlite3_get_table( sqliteHandle, countIfExist.toUtf8().constData(), &results, &rows, &columns, &errMsg );
5182+
if ( SQLITE_OK != ret )
5183+
{
5184+
QgsSqliteHandle::closeDb( handle );
5185+
QgsMessageLog::logMessage( QObject::tr( "Error executing query: %1" ).arg( countIfExist ) );
5186+
errCause = QObject::tr( "Error looking for style. The query was logged" );
5187+
return false;
5188+
}
5189+
5190+
// if not table exist... create is
5191+
int howMany = 0;
5192+
if ( 1 == rows )
5193+
{
5194+
howMany = atoi( results[( rows * columns ) + 0 ] );
5195+
}
5196+
sqlite3_free_table( results );
5197+
5198+
// create table if not exist
5199+
if ( 0 == howMany )
5200+
{
5201+
5202+
QString createQuery = QString( "CREATE TABLE layer_styles("
5203+
"id INTEGER PRIMARY KEY AUTOINCREMENT"
5204+
",f_table_catalog varchar(256)"
5205+
",f_table_schema varchar(256)"
5206+
",f_table_name varchar(256)"
5207+
",f_geometry_column varchar(256)"
5208+
",styleName varchar(30)"
5209+
",styleQML text"
5210+
",styleSLD text"
5211+
",useAsDefault boolean"
5212+
",description text"
5213+
",owner varchar(30)"
5214+
",ui text"
5215+
",update_time timestamp DEFAULT CURRENT_TIMESTAMP"
5216+
")" );
5217+
ret = sqlite3_exec( sqliteHandle, createQuery.toUtf8().constData(), NULL, NULL, &errMsg );
5218+
if ( SQLITE_OK != ret )
5219+
{
5220+
QgsSqliteHandle::closeDb( handle );
5221+
errCause = QObject::tr( "Unable to save layer style. It's not possible to create the destination table on the database." );
5222+
return false;
5223+
}
5224+
}
5225+
5226+
QString uiFileColumn;
5227+
QString uiFileValue;
5228+
if ( !uiFileContent.isEmpty() )
5229+
{
5230+
uiFileColumn = ",ui";
5231+
uiFileValue = QString( ",%1" ).arg( QgsSpatiaLiteProvider::quotedValue( uiFileContent ) );
5232+
}
5233+
5234+
QString sql = QString( "INSERT INTO layer_styles("
5235+
"f_table_catalog,f_table_schema,f_table_name,f_geometry_column,styleName,styleQML,styleSLD,useAsDefault,description,owner%11"
5236+
") VALUES ("
5237+
"%1,%2,%3,%4,%5,%6,%7,%8,%9,%10%12"
5238+
")" )
5239+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.database() ) )
5240+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.schema() ) )
5241+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.table() ) )
5242+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.geometryColumn() ) )
5243+
.arg( QgsSpatiaLiteProvider::quotedValue( styleName.isEmpty() ? dsUri.table() : styleName ) )
5244+
.arg( QgsSpatiaLiteProvider::quotedValue( qmlStyle ) )
5245+
.arg( QgsSpatiaLiteProvider::quotedValue( sldStyle ) )
5246+
.arg( useAsDefault ? "1" : "0" )
5247+
.arg( QgsSpatiaLiteProvider::quotedValue( styleDescription.isEmpty() ? QDateTime::currentDateTime().toString() : styleDescription ) )
5248+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.username() ) )
5249+
.arg( uiFileColumn )
5250+
.arg( uiFileValue );
5251+
5252+
QString checkQuery = QString( "SELECT styleName"
5253+
" FROM layer_styles"
5254+
" WHERE f_table_catalog=%1"
5255+
" AND f_table_schema=%2"
5256+
" AND f_table_name=%3"
5257+
" AND f_geometry_column=%4"
5258+
" AND styleName=%5" )
5259+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.database() ) )
5260+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.schema() ) )
5261+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.table() ) )
5262+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.geometryColumn() ) )
5263+
.arg( QgsSpatiaLiteProvider::quotedValue( styleName.isEmpty() ? dsUri.table() : styleName ) );
5264+
5265+
ret = sqlite3_get_table( sqliteHandle, checkQuery.toUtf8().constData(), &results, &rows, &columns, &errMsg );
5266+
if ( SQLITE_OK != ret )
5267+
{
5268+
QgsSqliteHandle::closeDb( handle );
5269+
QgsMessageLog::logMessage( QObject::tr( "Error executing query: %1" ).arg( checkQuery ) );
5270+
errCause = QObject::tr( "Error looking for style. The query was logged" );
5271+
return false;
5272+
}
5273+
5274+
if ( 0 != rows )
5275+
{
5276+
sqlite3_free_table( results );
5277+
if ( QMessageBox::question( 0, QObject::tr( "Save style in database" ),
5278+
QObject::tr( "A style named \"%1\" already exists in the database for this layer. Do you want to overwrite it?" )
5279+
.arg( styleName.isEmpty() ? dsUri.table() : styleName ),
5280+
QMessageBox::Yes | QMessageBox::No ) == QMessageBox::No )
5281+
{
5282+
QgsSqliteHandle::closeDb( handle );
5283+
errCause = QObject::tr( "Operation aborted" );
5284+
return false;
5285+
}
5286+
5287+
sql = QString( "UPDATE layer_styles"
5288+
" SET useAsDefault=%1"
5289+
",styleQML=%2"
5290+
",styleSLD=%3"
5291+
",description=%4"
5292+
",owner=%5"
5293+
" WHERE f_table_catalog=%6"
5294+
" AND f_table_schema=%7"
5295+
" AND f_table_name=%8"
5296+
" AND f_geometry_column=%9"
5297+
" AND styleName=%10" )
5298+
.arg( useAsDefault ? "1" : "0" )
5299+
.arg( QgsSpatiaLiteProvider::quotedValue( qmlStyle ) )
5300+
.arg( QgsSpatiaLiteProvider::quotedValue( sldStyle ) )
5301+
.arg( QgsSpatiaLiteProvider::quotedValue( styleDescription.isEmpty() ? QDateTime::currentDateTime().toString() : styleDescription ) )
5302+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.username() ) )
5303+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.database() ) )
5304+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.schema() ) )
5305+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.table() ) )
5306+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.geometryColumn() ) )
5307+
.arg( QgsSpatiaLiteProvider::quotedValue( styleName.isEmpty() ? dsUri.table() : styleName ) );
5308+
}
5309+
5310+
if ( useAsDefault )
5311+
{
5312+
QString removeDefaultSql = QString( "UPDATE layer_styles"
5313+
" SET useAsDefault=0"
5314+
" WHERE f_table_catalog=%1"
5315+
" AND f_table_schema=%2"
5316+
" AND f_table_name=%3"
5317+
" AND f_geometry_column=%4" )
5318+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.database() ) )
5319+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.schema() ) )
5320+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.table() ) )
5321+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.geometryColumn() ) );
5322+
sql = QString( "BEGIN; %1; %2; COMMIT;" ).arg( removeDefaultSql ).arg( sql );
5323+
}
5324+
5325+
ret = sqlite3_exec( sqliteHandle, sql.toUtf8().constData(), NULL, NULL, &errMsg );
5326+
if ( SQLITE_OK != ret )
5327+
{
5328+
QgsSqliteHandle::closeDb( handle );
5329+
QgsMessageLog::logMessage( QObject::tr( "Error executing query: %1" ).arg( sql ) );
5330+
errCause = QObject::tr( "Error looking for style. The query was logged" );
5331+
return false;
5332+
}
5333+
5334+
bool saved = ( SQLITE_OK == ret ) ? true : false;
5335+
5336+
if ( NULL != errMsg )
5337+
sqlite3_free( errMsg );
5338+
5339+
QgsSqliteHandle::closeDb( handle );
5340+
return saved;
5341+
}
5342+
5343+
5344+
QGISEXTERN QString loadStyle( const QString& uri, QString& errCause )
5345+
{
5346+
QgsSqliteHandle *handle;
5347+
sqlite3 *sqliteHandle = NULL;
5348+
char **results;
5349+
int rows;
5350+
int columns;
5351+
char *errMsg = NULL;
5352+
QString sql;
5353+
5354+
QgsDataSourceURI dsUri( uri );
5355+
QString sqlitePath = dsUri.database();
5356+
QgsDebugMsg( "Database is: " + sqlitePath );
5357+
5358+
// trying to open the SQLite DB
5359+
spatialite_init( 0 );
5360+
handle = QgsSqliteHandle::openDb( sqlitePath );
5361+
if ( NULL == handle )
5362+
{
5363+
QgsDebugMsg( "Connection to database failed. Save style aborted." );
5364+
errCause = QObject::tr( "Connection to database failed" );
5365+
return "";
5366+
}
5367+
5368+
sqliteHandle = handle->handle();
5369+
5370+
QString selectQmlQuery = QString( "SELECT styleQML"
5371+
" FROM layer_styles"
5372+
" WHERE f_table_catalog=%1"
5373+
" AND f_table_schema=%2"
5374+
" AND f_table_name=%3"
5375+
" AND f_geometry_column=%4"
5376+
" ORDER BY CASE WHEN useAsDefault THEN 1 ELSE 2 END"
5377+
",update_time DESC LIMIT 1" )
5378+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.database() ) )
5379+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.schema() ) )
5380+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.table() ) )
5381+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.geometryColumn() ) );
5382+
5383+
int ret = sqlite3_get_table( sqliteHandle, selectQmlQuery.toUtf8().constData(), &results, &rows, &columns, &errMsg );
5384+
if ( SQLITE_OK != ret )
5385+
{
5386+
QgsSqliteHandle::closeDb( handle );
5387+
QgsMessageLog::logMessage( QObject::tr( "Error executing query: %1" ).arg( selectQmlQuery ) );
5388+
errCause = QObject::tr( "Error executing loading style. The query was logged" );
5389+
return "";
5390+
}
5391+
5392+
QString style = ( rows == 1 ) ? QString::fromUtf8( results[( rows * columns ) + 0 ] ) : "";
5393+
sqlite3_free_table( results );
5394+
5395+
QgsSqliteHandle::closeDb( handle );
5396+
return style;
5397+
}
5398+
5399+
QGISEXTERN int listStyles( const QString &uri, QStringList &ids, QStringList &names,
5400+
QStringList &descriptions, QString& errCause )
5401+
{
5402+
QgsSqliteHandle *handle;
5403+
sqlite3 *sqliteHandle = NULL;
5404+
char **results;
5405+
int rows;
5406+
int columns;
5407+
char *errMsg = NULL;
5408+
QString sql;
5409+
5410+
QgsDataSourceURI dsUri( uri );
5411+
QString sqlitePath = dsUri.database();
5412+
QgsDebugMsg( "Database is: " + sqlitePath );
5413+
5414+
// trying to open the SQLite DB
5415+
spatialite_init( 0 );
5416+
handle = QgsSqliteHandle::openDb( sqlitePath );
5417+
if ( NULL == handle )
5418+
{
5419+
QgsDebugMsg( "Connection to database failed. Save style aborted." );
5420+
errCause = QObject::tr( "Connection to database failed" );
5421+
return -1;
5422+
}
5423+
5424+
sqliteHandle = handle->handle();
5425+
5426+
// check if layer_styles table already exist
5427+
QString countIfExist = QString( "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='%1';" ).arg( "layer_styles" );
5428+
5429+
int ret = sqlite3_get_table( sqliteHandle, countIfExist.toUtf8().constData(), &results, &rows, &columns, &errMsg );
5430+
if ( SQLITE_OK != ret )
5431+
{
5432+
QgsSqliteHandle::closeDb( handle );
5433+
QgsMessageLog::logMessage( QObject::tr( "Error executing query: %1" ).arg( countIfExist ) );
5434+
errCause = QObject::tr( "Error looking for style. The query was logged" );
5435+
return -1;
5436+
}
5437+
5438+
int howMany = 0;
5439+
if ( 1 == rows )
5440+
{
5441+
howMany = atoi( results[( rows * columns ) + 0 ] );
5442+
}
5443+
sqlite3_free_table( results );
5444+
5445+
if ( 0 == howMany )
5446+
{
5447+
QgsSqliteHandle::closeDb( handle );
5448+
QgsMessageLog::logMessage( QObject::tr( "No styles available on DB" ) );
5449+
errCause = QObject::tr( "No styles available on DB" );
5450+
return false;
5451+
}
5452+
5453+
// get them
5454+
QString selectRelatedQuery = QString( "SELECT id,styleName,description"
5455+
" FROM layer_styles"
5456+
" WHERE f_table_catalog=%1"
5457+
" AND f_table_schema=%2"
5458+
" AND f_table_name=%3"
5459+
" AND f_geometry_column=%4" )
5460+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.database() ) )
5461+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.schema() ) )
5462+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.table() ) )
5463+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.geometryColumn() ) );
5464+
5465+
ret = sqlite3_get_table( sqliteHandle, selectRelatedQuery.toUtf8().constData(), &results, &rows, &columns, &errMsg );
5466+
if ( SQLITE_OK != ret )
5467+
{
5468+
QgsSqliteHandle::closeDb( handle );
5469+
QgsMessageLog::logMessage( QObject::tr( "Error executing query: %1" ).arg( selectRelatedQuery ) );
5470+
errCause = QObject::tr( "Error loading styles. The query was logged" );
5471+
return -1;
5472+
}
5473+
5474+
int numberOfRelatedStyles = rows;
5475+
for ( int i = 1; i <= rows; i++ )
5476+
{
5477+
ids.append( results[( i * columns ) + 0 ] );
5478+
names.append( QString::fromUtf8( results[( i * columns ) + 1 ] ) );
5479+
descriptions.append( QString::fromUtf8( results[( i * columns ) + 2 ] ) );
5480+
}
5481+
sqlite3_free_table( results );
5482+
5483+
QString selectOthersQuery = QString( "SELECT id,styleName,description"
5484+
" FROM layer_styles"
5485+
" WHERE NOT (f_table_catalog=%1 AND f_table_schema=%2 AND f_table_name=%3 AND f_geometry_column=%4)"
5486+
" ORDER BY update_time DESC" )
5487+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.database() ) )
5488+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.schema() ) )
5489+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.table() ) )
5490+
.arg( QgsSpatiaLiteProvider::quotedValue( dsUri.geometryColumn() ) );
5491+
5492+
ret = sqlite3_get_table( sqliteHandle, selectOthersQuery.toUtf8().constData(), &results, &rows, &columns, &errMsg );
5493+
if ( SQLITE_OK != ret )
5494+
{
5495+
QgsSqliteHandle::closeDb( handle );
5496+
QgsMessageLog::logMessage( QObject::tr( "Error executing query: %1" ).arg( selectOthersQuery ) );
5497+
errCause = QObject::tr( "Error executing the select query for unrelated styles. The query was logged" );
5498+
return -1;
5499+
}
5500+
5501+
for ( int i = 1; i <= rows; i++ )
5502+
{
5503+
ids.append( results[( i * columns ) + 0 ] );
5504+
names.append( QString::fromUtf8( results[( i * columns ) + 1 ] ) );
5505+
descriptions.append( QString::fromUtf8( results[( i * columns ) + 2 ] ) );
5506+
}
5507+
sqlite3_free_table( results );
5508+
5509+
QgsSqliteHandle::closeDb( handle );
5510+
return numberOfRelatedStyles;
5511+
}
5512+
5513+
QGISEXTERN QString getStyleById( const QString& uri, QString styleId, QString& errCause )
5514+
{
5515+
QgsSqliteHandle *handle;
5516+
sqlite3 *sqliteHandle = NULL;
5517+
char **results;
5518+
int rows;
5519+
int columns;
5520+
char *errMsg = NULL;
5521+
QString sql;
5522+
5523+
QgsDataSourceURI dsUri( uri );
5524+
QString sqlitePath = dsUri.database();
5525+
QgsDebugMsg( "Database is: " + sqlitePath );
5526+
5527+
// trying to open the SQLite DB
5528+
spatialite_init( 0 );
5529+
handle = QgsSqliteHandle::openDb( sqlitePath );
5530+
if ( NULL == handle )
5531+
{
5532+
QgsDebugMsg( "Connection to database failed. Save style aborted." );
5533+
errCause = QObject::tr( "Connection to database failed" );
5534+
return "";
5535+
}
5536+
5537+
sqliteHandle = handle->handle();
5538+
5539+
QString style;
5540+
QString selectQmlQuery = QString( "SELECT styleQml FROM layer_styles WHERE id=%1" ).arg( QgsSpatiaLiteProvider::quotedValue( styleId ) );
5541+
int ret = sqlite3_get_table( sqliteHandle, selectQmlQuery.toUtf8().constData(), &results, &rows, &columns, &errMsg );
5542+
if ( SQLITE_OK == ret )
5543+
{
5544+
if ( 1 == rows )
5545+
style = QString::fromUtf8( results[( rows * columns ) + 0 ] );
5546+
else
5547+
errCause = QObject::tr( "Consistency error in table '%1'. Style id should be unique" ).arg( "layer_styles" );
5548+
}
5549+
else
5550+
{
5551+
QgsMessageLog::logMessage( QObject::tr( "Error executing query: %1" ).arg( selectQmlQuery ) );
5552+
errCause = QObject::tr( "Error executing the select query. The query was logged" );
5553+
}
5554+
5555+
QgsSqliteHandle::closeDb( handle );
5556+
sqlite3_free_table( results );
5557+
return style;
5558+
}

‎src/providers/spatialite/qgsspatialiteprovider.h

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -154,6 +154,10 @@ class QgsSpatiaLiteProvider: public QgsVectorDataProvider
154154
*/
155155
bool isValid();
156156

157+
/**Describes if provider has save and load style support
158+
@return true in case saving style to db is supported by this provider*/
159+
virtual bool isSaveAndLoadStyleToDBSupported() { return true; }
160+
157161
/**Adds a list of features
158162
@return true in case of success and false in case of failure*/
159163
bool addFeatures( QgsFeatureList & flist );

0 commit comments

Comments
 (0)
Please sign in to comment.