QGIS API Documentation  2.0.1-Dufour
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Properties Friends Macros Groups Pages
qgsofflineediting.cpp
Go to the documentation of this file.
1 /***************************************************************************
2  offline_editing.cpp
3 
4  Offline Editing Plugin
5  a QGIS plugin
6  --------------------------------------
7  Date : 22-Jul-2010
8  Copyright : (C) 2010 by Sourcepole
9  Email : info at sourcepole.ch
10  ***************************************************************************
11  * *
12  * This program is free software; you can redistribute it and/or modify *
13  * it under the terms of the GNU General Public License as published by *
14  * the Free Software Foundation; either version 2 of the License, or *
15  * (at your option) any later version. *
16  * *
17  ***************************************************************************/
18 
19 
20 #include <qgsapplication.h>
21 #include <qgsdatasourceuri.h>
22 #include <qgsgeometry.h>
23 #include <qgsmaplayer.h>
24 #include <qgsmaplayerregistry.h>
25 #include <qgsofflineediting.h>
26 #include <qgsproject.h>
27 #include <qgsvectordataprovider.h>
29 
30 #include <QDir>
31 #include <QDomDocument>
32 #include <QDomNode>
33 #include <QFile>
34 #include <QMessageBox>
35 
36 extern "C"
37 {
38 #include <sqlite3.h>
39 #include <spatialite.h>
40 }
41 
42 // TODO: DEBUG
43 #include <QDebug>
44 // END
45 
46 #define CUSTOM_PROPERTY_IS_OFFLINE_EDITABLE "isOfflineEditable"
47 #define CUSTOM_PROPERTY_REMOTE_SOURCE "remoteSource"
48 #define CUSTOM_PROPERTY_REMOTE_PROVIDER "remoteProvider"
49 #define PROJECT_ENTRY_SCOPE_OFFLINE "OfflineEditingPlugin"
50 #define PROJECT_ENTRY_KEY_OFFLINE_DB_PATH "/OfflineDbPath"
51 
53 {
54  connect( QgsMapLayerRegistry::instance(), SIGNAL( layerWasAdded( QgsMapLayer* ) ), this, SLOT( layerAdded( QgsMapLayer* ) ) );
55 }
56 
58 {
59 }
60 
65 bool QgsOfflineEditing::convertToOfflineProject( const QString& offlineDataPath, const QString& offlineDbFile, const QStringList& layerIds )
66 {
67  if ( layerIds.isEmpty() )
68  {
69  return false;
70  }
71  QString dbPath = QDir( offlineDataPath ).absoluteFilePath( offlineDbFile );
72  if ( createSpatialiteDB( dbPath ) )
73  {
74  spatialite_init( 0 );
75  sqlite3* db;
76  int rc = sqlite3_open( dbPath.toUtf8().constData(), &db );
77  if ( rc != SQLITE_OK )
78  {
79  showWarning( tr( "Could not open the spatialite database" ) );
80  }
81  else
82  {
83  // create logging tables
84  createLoggingTables( db );
85 
86  emit progressStarted();
87 
88  // copy selected vector layers to SpatiaLite
89  for ( int i = 0; i < layerIds.count(); i++ )
90  {
91  emit layerProgressUpdated( i + 1, layerIds.count() );
92 
93  QgsMapLayer* layer = QgsMapLayerRegistry::instance()->mapLayer( layerIds.at( i ) );
94  copyVectorLayer( qobject_cast<QgsVectorLayer*>( layer ), db, dbPath );
95  }
96 
97  emit progressStopped();
98 
99  sqlite3_close( db );
100 
101  // save offline project
102  QString projectTitle = QgsProject::instance()->title();
103  if ( projectTitle.isEmpty() )
104  {
105  projectTitle = QFileInfo( QgsProject::instance()->fileName() ).fileName();
106  }
107  projectTitle += " (offline)";
108  QgsProject::instance()->title( projectTitle );
109 
111 
112  return true;
113  }
114  }
115 
116  return false;
117 
118  // Workflow:
119  // copy layers to spatialite
120  // create spatialite db at offlineDataPath
121  // create table for each layer
122  // add new spatialite layer
123  // copy features
124  // save as offline project
125  // mark offline layers
126  // remove remote layers
127  // mark as offline project
128 }
129 
131 {
133 }
134 
136 {
137  // open logging db
138  sqlite3* db = openLoggingDb();
139  if ( db == NULL )
140  {
141  return;
142  }
143 
144  emit progressStarted();
145 
146  // restore and sync remote layers
147  QList<QgsMapLayer*> offlineLayers;
148  QMap<QString, QgsMapLayer*> mapLayers = QgsMapLayerRegistry::instance()->mapLayers();
149  for ( QMap<QString, QgsMapLayer*>::iterator layer_it = mapLayers.begin() ; layer_it != mapLayers.end(); ++layer_it )
150  {
151  QgsMapLayer* layer = layer_it.value();
152  if ( layer->customProperty( CUSTOM_PROPERTY_IS_OFFLINE_EDITABLE, false ).toBool() )
153  {
154  offlineLayers << layer;
155  }
156  }
157 
158  for ( int l = 0; l < offlineLayers.count(); l++ )
159  {
160  QgsMapLayer* layer = offlineLayers[l];
161 
162  emit layerProgressUpdated( l + 1, offlineLayers.count() );
163 
164  QString remoteSource = layer->customProperty( CUSTOM_PROPERTY_REMOTE_SOURCE, "" ).toString();
165  QString remoteProvider = layer->customProperty( CUSTOM_PROPERTY_REMOTE_PROVIDER, "" ).toString();
166  QString remoteName = layer->name();
167  remoteName.remove( QRegExp( " \\(offline\\)$" ) );
168 
169  QgsVectorLayer* remoteLayer = new QgsVectorLayer( remoteSource, remoteName, remoteProvider );
170  if ( remoteLayer->isValid() )
171  {
172  // TODO: only add remote layer if there are log entries?
173 
174  QgsVectorLayer* offlineLayer = qobject_cast<QgsVectorLayer*>( layer );
175 
176  // copy style
177  copySymbology( offlineLayer, remoteLayer );
178 
179  // register this layer with the central layers registry
181  QList<QgsMapLayer *>() << remoteLayer, true );
182 
183  // apply layer edit log
184  QString qgisLayerId = layer->id();
185  QString sql = QString( "SELECT \"id\" FROM 'log_layer_ids' WHERE \"qgis_id\" = '%1'" ).arg( qgisLayerId );
186  int layerId = sqlQueryInt( db, sql, -1 );
187  if ( layerId != -1 )
188  {
189  remoteLayer->startEditing();
190 
191  // TODO: only get commitNos of this layer?
192  int commitNo = getCommitNo( db );
193  for ( int i = 0; i < commitNo; i++ )
194  {
195  // apply commits chronologically
196  applyAttributesAdded( remoteLayer, db, layerId, i );
197  applyAttributeValueChanges( offlineLayer, remoteLayer, db, layerId, i );
198  applyGeometryChanges( remoteLayer, db, layerId, i );
199  }
200 
201  applyFeaturesAdded( offlineLayer, remoteLayer, db, layerId );
202  applyFeaturesRemoved( remoteLayer, db, layerId );
203 
204  if ( remoteLayer->commitChanges() )
205  {
206  // update fid lookup
207  updateFidLookup( remoteLayer, db, layerId );
208 
209  // clear edit log for this layer
210  sql = QString( "DELETE FROM 'log_added_attrs' WHERE \"layer_id\" = %1" ).arg( layerId );
211  sqlExec( db, sql );
212  sql = QString( "DELETE FROM 'log_added_features' WHERE \"layer_id\" = %1" ).arg( layerId );
213  sqlExec( db, sql );
214  sql = QString( "DELETE FROM 'log_removed_features' WHERE \"layer_id\" = %1" ).arg( layerId );
215  sqlExec( db, sql );
216  sql = QString( "DELETE FROM 'log_feature_updates' WHERE \"layer_id\" = %1" ).arg( layerId );
217  sqlExec( db, sql );
218  sql = QString( "DELETE FROM 'log_geometry_updates' WHERE \"layer_id\" = %1" ).arg( layerId );
219  sqlExec( db, sql );
220 
221  // reset commitNo
222  QString sql = QString( "UPDATE 'log_indices' SET 'last_index' = 0 WHERE \"name\" = 'commit_no'" );
223  sqlExec( db, sql );
224  }
225  else
226  {
227  showWarning( remoteLayer->commitErrors().join( "\n" ) );
228  }
229  }
230 
231  // remove offline layer
233  ( QStringList() << qgisLayerId ) );
234 
235  // disable offline project
236  QString projectTitle = QgsProject::instance()->title();
237  projectTitle.remove( QRegExp( " \\(offline\\)$" ) );
238  QgsProject::instance()->title( projectTitle );
240  remoteLayer->reload(); //update with other changes
241  }
242  }
243 
244  emit progressStopped();
245 
246  sqlite3_close( db );
247 }
248 
250 {
251 // attempting to perform self-initialization for a newly created DB
252  int ret;
253  char sql[1024];
254  char *errMsg = NULL;
255  int count = 0;
256  int i;
257  char **results;
258  int rows;
259  int columns;
260 
261  if ( sqlite_handle == NULL )
262  return;
263  // checking if this DB is really empty
264  strcpy( sql, "SELECT Count(*) from sqlite_master" );
265  ret = sqlite3_get_table( sqlite_handle, sql, &results, &rows, &columns, NULL );
266  if ( ret != SQLITE_OK )
267  return;
268  if ( rows < 1 )
269  ;
270  else
271  {
272  for ( i = 1; i <= rows; i++ )
273  count = atoi( results[( i * columns ) + 0] );
274  }
275  sqlite3_free_table( results );
276 
277  if ( count > 0 )
278  return;
279 
280  // all right, it's empty: proceding to initialize
281  strcpy( sql, "SELECT InitSpatialMetadata()" );
282  ret = sqlite3_exec( sqlite_handle, sql, NULL, NULL, &errMsg );
283  if ( ret != SQLITE_OK )
284  {
285  QString errCause = tr( "Unable to initialize SpatialMetadata:\n" );
286  errCause += QString::fromUtf8( errMsg );
287  showWarning( errCause );
288  sqlite3_free( errMsg );
289  return;
290  }
291  spatial_ref_sys_init( sqlite_handle, 0 );
292 }
293 
294 bool QgsOfflineEditing::createSpatialiteDB( const QString& offlineDbPath )
295 {
296  int ret;
297  sqlite3 *sqlite_handle;
298  char *errMsg = NULL;
299  QFile newDb( offlineDbPath );
300  if ( newDb.exists() )
301  {
302  QFile::remove( offlineDbPath );
303  }
304 
305  // see also QgsNewSpatialiteLayerDialog::createDb()
306 
307  QFileInfo fullPath = QFileInfo( offlineDbPath );
308  QDir path = fullPath.dir();
309 
310  // Must be sure there is destination directory ~/.qgis
311  QDir().mkpath( path.absolutePath( ) );
312 
313  // creating/opening the new database
314  QString dbPath = newDb.fileName();
315  spatialite_init( 0 );
316  ret = sqlite3_open_v2( dbPath.toUtf8().constData(), &sqlite_handle, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL );
317  if ( ret )
318  {
319  // an error occurred
320  QString errCause = tr( "Could not create a new database\n" );
321  errCause += QString::fromUtf8( sqlite3_errmsg( sqlite_handle ) );
322  sqlite3_close( sqlite_handle );
323  showWarning( errCause );
324  return false;
325  }
326  // activating Foreign Key constraints
327  ret = sqlite3_exec( sqlite_handle, "PRAGMA foreign_keys = 1", NULL, 0, &errMsg );
328  if ( ret != SQLITE_OK )
329  {
330  showWarning( tr( "Unable to activate FOREIGN_KEY constraints" ) );
331  sqlite3_free( errMsg );
332  sqlite3_close( sqlite_handle );
333  return false;
334  }
335  initializeSpatialMetadata( sqlite_handle );
336 
337  // all done: closing the DB connection
338  sqlite3_close( sqlite_handle );
339 
340  return true;
341 }
342 
344 {
345  // indices
346  QString sql = "CREATE TABLE 'log_indices' ('name' TEXT, 'last_index' INTEGER)";
347  sqlExec( db, sql );
348 
349  sql = "INSERT INTO 'log_indices' VALUES ('commit_no', 0)";
350  sqlExec( db, sql );
351 
352  sql = "INSERT INTO 'log_indices' VALUES ('layer_id', 0)";
353  sqlExec( db, sql );
354 
355  // layername <-> layer id
356  sql = "CREATE TABLE 'log_layer_ids' ('id' INTEGER, 'qgis_id' TEXT)";
357  sqlExec( db, sql );
358 
359  // offline fid <-> remote fid
360  sql = "CREATE TABLE 'log_fids' ('layer_id' INTEGER, 'offline_fid' INTEGER, 'remote_fid' INTEGER)";
361  sqlExec( db, sql );
362 
363  // added attributes
364  sql = "CREATE TABLE 'log_added_attrs' ('layer_id' INTEGER, 'commit_no' INTEGER, ";
365  sql += "'name' TEXT, 'type' INTEGER, 'length' INTEGER, 'precision' INTEGER, 'comment' TEXT)";
366  sqlExec( db, sql );
367 
368  // added features
369  sql = "CREATE TABLE 'log_added_features' ('layer_id' INTEGER, 'fid' INTEGER)";
370  sqlExec( db, sql );
371 
372  // removed features
373  sql = "CREATE TABLE 'log_removed_features' ('layer_id' INTEGER, 'fid' INTEGER)";
374  sqlExec( db, sql );
375 
376  // feature updates
377  sql = "CREATE TABLE 'log_feature_updates' ('layer_id' INTEGER, 'commit_no' INTEGER, 'fid' INTEGER, 'attr' INTEGER, 'value' TEXT)";
378  sqlExec( db, sql );
379 
380  // geometry updates
381  sql = "CREATE TABLE 'log_geometry_updates' ('layer_id' INTEGER, 'commit_no' INTEGER, 'fid' INTEGER, 'geom_wkt' TEXT)";
382  sqlExec( db, sql );
383 
384  /* TODO: other logging tables
385  - attr delete (not supported by SpatiaLite provider)
386  */
387 }
388 
389 void QgsOfflineEditing::copyVectorLayer( QgsVectorLayer* layer, sqlite3* db, const QString& offlineDbPath )
390 {
391  if ( layer == NULL )
392  {
393  return;
394  }
395 
396  QString tableName = layer->name();
397 
398  // create table
399  QString sql = QString( "CREATE TABLE '%1' (" ).arg( tableName );
400  QString delim = "";
401  const QgsFields& fields = layer->dataProvider()->fields();
402  for ( int idx = 0; idx < fields.count(); ++idx )
403  {
404  QString dataType = "";
405  QVariant::Type type = fields[idx].type();
406  if ( type == QVariant::Int )
407  {
408  dataType = "INTEGER";
409  }
410  else if ( type == QVariant::Double )
411  {
412  dataType = "REAL";
413  }
414  else if ( type == QVariant::String )
415  {
416  dataType = "TEXT";
417  }
418  else
419  {
420  showWarning( tr( "Unknown data type %1" ).arg( type ) );
421  }
422 
423  sql += delim + QString( "'%1' %2" ).arg( fields[idx].name() ).arg( dataType );
424  delim = ",";
425  }
426  sql += ")";
427 
428  // add geometry column
429  QString geomType = "";
430  switch ( layer->wkbType() )
431  {
432  case QGis::WKBPoint:
433  geomType = "POINT";
434  break;
435  case QGis::WKBMultiPoint:
436  geomType = "MULTIPOINT";
437  break;
438  case QGis::WKBLineString:
439  geomType = "LINESTRING";
440  break;
442  geomType = "MULTILINESTRING";
443  break;
444  case QGis::WKBPolygon:
445  geomType = "POLYGON";
446  break;
448  geomType = "MULTIPOLYGON";
449  break;
450  default:
451  showWarning( tr( "QGIS wkbType %1 not supported" ).arg( layer->wkbType() ) );
452  break;
453  };
454  QString sqlAddGeom = QString( "SELECT AddGeometryColumn('%1', 'Geometry', %2, '%3', 2)" )
455  .arg( tableName )
456  .arg( layer->crs().authid().startsWith( "EPSG:", Qt::CaseInsensitive ) ? layer->crs().authid().mid( 5 ).toLong() : 0 )
457  .arg( geomType );
458 
459  // create spatial index
460  QString sqlCreateIndex = QString( "SELECT CreateSpatialIndex('%1', 'Geometry')" ).arg( tableName );
461 
462  int rc = sqlExec( db, sql );
463  if ( rc == SQLITE_OK )
464  {
465  rc = sqlExec( db, sqlAddGeom );
466  if ( rc == SQLITE_OK )
467  {
468  rc = sqlExec( db, sqlCreateIndex );
469  }
470  }
471 
472  if ( rc == SQLITE_OK )
473  {
474  // add new layer
475  QgsVectorLayer* newLayer = new QgsVectorLayer( QString( "dbname='%1' table='%2'(Geometry) sql=" )
476  .arg( offlineDbPath ).arg( tableName ), tableName + " (offline)", "spatialite" );
477  if ( newLayer->isValid() )
478  {
479  // mark as offline layer
481 
482  // store original layer source
485 
486  // copy style
487  bool hasLabels = layer->hasLabelsEnabled();
488  if ( !hasLabels )
489  {
490  // NOTE: copy symbology before adding the layer so it is displayed correctly
491  copySymbology( layer, newLayer );
492  }
493 
494  // register this layer with the central layers registry
496  QList<QgsMapLayer *>() << newLayer );
497 
498  if ( hasLabels )
499  {
500  // NOTE: copy symbology of layers with labels enabled after adding to project, as it will crash otherwise (WORKAROUND)
501  copySymbology( layer, newLayer );
502  }
503 
504  // TODO: layer order
505 
506  // copy features
507  newLayer->startEditing();
508  QgsFeature f;
509 
510  // NOTE: force feature recount for PostGIS layer, else only visible features are counted, before iterating over all features (WORKAROUND)
511  layer->setSubsetString( "" );
512 
513  QgsFeatureIterator fit = layer->getFeatures();
514 
516  int featureCount = 1;
517 
518  QList<QgsFeatureId> remoteFeatureIds;
519  while ( fit.nextFeature( f ) )
520  {
521  remoteFeatureIds << f.id();
522 
523  // NOTE: Spatialite provider ignores position of geometry column
524  // fill gap in QgsAttributeMap if geometry column is not last (WORKAROUND)
525  int column = 0;
526  QgsAttributes attrs = f.attributes();
527  QgsAttributes newAttrs( attrs.count() );
528  for ( int it = 0; it < attrs.count(); ++it )
529  {
530  newAttrs[column++] = attrs[it];
531  }
532  f.setAttributes( newAttrs );
533 
534  newLayer->addFeature( f, false );
535 
536  emit progressUpdated( featureCount++ );
537  }
538  if ( newLayer->commitChanges() )
539  {
541  featureCount = 1;
542 
543  // update feature id lookup
544  int layerId = getOrCreateLayerId( db, newLayer->id() );
545  QList<QgsFeatureId> offlineFeatureIds;
546 
547  QgsFeatureIterator fit = newLayer->getFeatures( QgsFeatureRequest().setFlags( QgsFeatureRequest::NoGeometry ).setSubsetOfAttributes( QgsAttributeList() ) );
548  while ( fit.nextFeature( f ) )
549  {
550  offlineFeatureIds << f.id();
551  }
552 
553  // NOTE: insert fids in this loop, as the db is locked during newLayer->nextFeature()
554  sqlExec( db, "BEGIN" );
555  int remoteCount = remoteFeatureIds.size();
556  for ( int i = 0; i < remoteCount; i++ )
557  {
558  addFidLookup( db, layerId, offlineFeatureIds.at( i ), remoteFeatureIds.at( remoteCount - ( i + 1 ) ) );
559  emit progressUpdated( featureCount++ );
560  }
561  sqlExec( db, "COMMIT" );
562  }
563  else
564  {
565  showWarning( newLayer->commitErrors().join( "\n" ) );
566  }
567 
568  // remove remote layer
570  QStringList() << layer->id() );
571  }
572  }
573 }
574 
575 void QgsOfflineEditing::applyAttributesAdded( QgsVectorLayer* remoteLayer, sqlite3* db, int layerId, int commitNo )
576 {
577  QString sql = QString( "SELECT \"name\", \"type\", \"length\", \"precision\", \"comment\" FROM 'log_added_attrs' WHERE \"layer_id\" = %1 AND \"commit_no\" = %2" ).arg( layerId ).arg( commitNo );
578  QList<QgsField> fields = sqlQueryAttributesAdded( db, sql );
579 
580  const QgsVectorDataProvider* provider = remoteLayer->dataProvider();
581  QList<QgsVectorDataProvider::NativeType> nativeTypes = provider->nativeTypes();
582 
583  // NOTE: uses last matching QVariant::Type of nativeTypes
584  QMap < QVariant::Type, QString /*typeName*/ > typeNameLookup;
585  for ( int i = 0; i < nativeTypes.size(); i++ )
586  {
587  QgsVectorDataProvider::NativeType nativeType = nativeTypes.at( i );
588  typeNameLookup[ nativeType.mType ] = nativeType.mTypeName;
589  }
590 
591  emit progressModeSet( QgsOfflineEditing::AddFields, fields.size() );
592 
593  for ( int i = 0; i < fields.size(); i++ )
594  {
595  // lookup typename from layer provider
596  QgsField field = fields[i];
597  if ( typeNameLookup.contains( field.type() ) )
598  {
599  QString typeName = typeNameLookup[ field.type()];
600  field.setTypeName( typeName );
601  remoteLayer->addAttribute( field );
602  }
603  else
604  {
605  showWarning( QString( "Could not add attribute '%1' of type %2" ).arg( field.name() ).arg( field.type() ) );
606  }
607 
608  emit progressUpdated( i + 1 );
609  }
610 }
611 
612 void QgsOfflineEditing::applyFeaturesAdded( QgsVectorLayer* offlineLayer, QgsVectorLayer* remoteLayer, sqlite3* db, int layerId )
613 {
614  QString sql = QString( "SELECT \"fid\" FROM 'log_added_features' WHERE \"layer_id\" = %1" ).arg( layerId );
615  QList<int> newFeatureIds = sqlQueryInts( db, sql );
616 
617  // get new features from offline layer
618  QgsFeatureList features;
619  for ( int i = 0; i < newFeatureIds.size(); i++ )
620  {
621  QgsFeature feature;
622  if ( offlineLayer->getFeatures( QgsFeatureRequest().setFilterFid( newFeatureIds.at( i ) ) ).nextFeature( feature ) )
623  {
624  features << feature;
625  }
626  }
627 
628  // copy features to remote layer
629  emit progressModeSet( QgsOfflineEditing::AddFeatures, features.size() );
630 
631  int i = 1;
632  int newAttrsCount = remoteLayer->pendingFields().count();
633  for ( QgsFeatureList::iterator it = features.begin(); it != features.end(); ++it )
634  {
635  QgsFeature f = *it;
636 
637  // NOTE: Spatialite provider ignores position of geometry column
638  // restore gap in QgsAttributeMap if geometry column is not last (WORKAROUND)
639  QMap<int, int> attrLookup = attributeLookup( offlineLayer, remoteLayer );
640  QgsAttributes newAttrs( newAttrsCount );
641  QgsAttributes attrs = f.attributes();
642  for ( int it = 0; it < attrs.count(); ++it )
643  {
644  newAttrs[ attrLookup[ it ] ] = attrs[ it ];
645  }
646  f.setAttributes( newAttrs );
647 
648  remoteLayer->addFeature( f, false );
649 
650  emit progressUpdated( i++ );
651  }
652 }
653 
654 void QgsOfflineEditing::applyFeaturesRemoved( QgsVectorLayer* remoteLayer, sqlite3* db, int layerId )
655 {
656  QString sql = QString( "SELECT \"fid\" FROM 'log_removed_features' WHERE \"layer_id\" = %1" ).arg( layerId );
657  QgsFeatureIds values = sqlQueryFeaturesRemoved( db, sql );
658 
659  emit progressModeSet( QgsOfflineEditing::RemoveFeatures, values.size() );
660 
661  int i = 1;
662  for ( QgsFeatureIds::const_iterator it = values.begin(); it != values.end(); ++it )
663  {
664  QgsFeatureId fid = remoteFid( db, layerId, *it );
665  remoteLayer->deleteFeature( fid );
666 
667  emit progressUpdated( i++ );
668  }
669 }
670 
671 void QgsOfflineEditing::applyAttributeValueChanges( QgsVectorLayer* offlineLayer, QgsVectorLayer* remoteLayer, sqlite3* db, int layerId, int commitNo )
672 {
673  QString sql = QString( "SELECT \"fid\", \"attr\", \"value\" FROM 'log_feature_updates' WHERE \"layer_id\" = %1 AND \"commit_no\" = %2 " ).arg( layerId ).arg( commitNo );
675 
676  emit progressModeSet( QgsOfflineEditing::UpdateFeatures, values.size() );
677 
678  QMap<int, int> attrLookup = attributeLookup( offlineLayer, remoteLayer );
679 
680  for ( int i = 0; i < values.size(); i++ )
681  {
682  QgsFeatureId fid = remoteFid( db, layerId, values.at( i ).fid );
683 
684  remoteLayer->changeAttributeValue( fid, attrLookup[ values.at( i ).attr ], values.at( i ).value, false );
685 
686  emit progressUpdated( i + 1 );
687  }
688 }
689 
690 void QgsOfflineEditing::applyGeometryChanges( QgsVectorLayer* remoteLayer, sqlite3* db, int layerId, int commitNo )
691 {
692  QString sql = QString( "SELECT \"fid\", \"geom_wkt\" FROM 'log_geometry_updates' WHERE \"layer_id\" = %1 AND \"commit_no\" = %2" ).arg( layerId ).arg( commitNo );
693  GeometryChanges values = sqlQueryGeometryChanges( db, sql );
694 
696 
697  for ( int i = 0; i < values.size(); i++ )
698  {
699  QgsFeatureId fid = remoteFid( db, layerId, values.at( i ).fid );
700  remoteLayer->changeGeometry( fid, QgsGeometry::fromWkt( values.at( i ).geom_wkt ) );
701 
702  emit progressUpdated( i + 1 );
703  }
704 }
705 
706 void QgsOfflineEditing::updateFidLookup( QgsVectorLayer* remoteLayer, sqlite3* db, int layerId )
707 {
708  // update fid lookup for added features
709 
710  // get remote added fids
711  // NOTE: use QMap for sorted fids
712  QMap < QgsFeatureId, bool /*dummy*/ > newRemoteFids;
713  QgsFeature f;
714 
715  QgsFeatureIterator fit = remoteLayer->getFeatures( QgsFeatureRequest().setFlags( QgsFeatureRequest::NoGeometry ).setSubsetOfAttributes( QgsAttributeList() ) );
716 
718 
719  int i = 1;
720  while ( fit.nextFeature( f ) )
721  {
722  if ( offlineFid( db, layerId, f.id() ) == -1 )
723  {
724  newRemoteFids[ f.id()] = true;
725  }
726 
727  emit progressUpdated( i++ );
728  }
729 
730  // get local added fids
731  // NOTE: fids are sorted
732  QString sql = QString( "SELECT \"fid\" FROM 'log_added_features' WHERE \"layer_id\" = %1" ).arg( layerId );
733  QList<int> newOfflineFids = sqlQueryInts( db, sql );
734 
735  if ( newRemoteFids.size() != newOfflineFids.size() )
736  {
737  //showWarning( QString( "Different number of new features on offline layer (%1) and remote layer (%2)" ).arg(newOfflineFids.size()).arg(newRemoteFids.size()) );
738  }
739  else
740  {
741  // add new fid lookups
742  i = 0;
743  sqlExec( db, "BEGIN" );
744  for ( QMap<QgsFeatureId, bool>::const_iterator it = newRemoteFids.begin(); it != newRemoteFids.end(); ++it )
745  {
746  addFidLookup( db, layerId, newOfflineFids.at( i++ ), it.key() );
747  }
748  sqlExec( db, "COMMIT" );
749  }
750 }
751 
752 void QgsOfflineEditing::copySymbology( const QgsVectorLayer* sourceLayer, QgsVectorLayer* targetLayer )
753 {
754  QString error;
755  QDomDocument doc;
756  QDomElement node = doc.createElement( "symbology" );
757  doc.appendChild( node );
758  sourceLayer->writeSymbology( node, doc, error );
759 
760  if ( error.isEmpty() )
761  {
762  targetLayer->readSymbology( node, error );
763  }
764  if ( !error.isEmpty() )
765  {
766  showWarning( error );
767  }
768 }
769 
770 // NOTE: use this to map column indices in case the remote geometry column is not last
771 QMap<int, int> QgsOfflineEditing::attributeLookup( QgsVectorLayer* offlineLayer, QgsVectorLayer* remoteLayer )
772 {
773  const QgsAttributeList& offlineAttrs = offlineLayer->pendingAllAttributesList();
774  const QgsAttributeList& remoteAttrs = remoteLayer->pendingAllAttributesList();
775 
776  QMap < int /*offline attr*/, int /*remote attr*/ > attrLookup;
777  // NOTE: use size of remoteAttrs, as offlineAttrs can have new attributes not yet synced
778  for ( int i = 0; i < remoteAttrs.size(); i++ )
779  {
780  attrLookup.insert( offlineAttrs.at( i ), remoteAttrs.at( i ) );
781  }
782 
783  return attrLookup;
784 }
785 
786 void QgsOfflineEditing::showWarning( const QString& message )
787 {
788  QMessageBox::warning( NULL, tr( "Offline Editing Plugin" ), message );
789 }
790 
792 {
793  sqlite3* db = NULL;
795  if ( !dbPath.isEmpty() )
796  {
797  int rc = sqlite3_open( dbPath.toUtf8().constData(), &db );
798  if ( rc != SQLITE_OK )
799  {
800  showWarning( tr( "Could not open the spatialite logging database" ) );
801  sqlite3_close( db );
802  db = NULL;
803  }
804  }
805  return db;
806 }
807 
808 int QgsOfflineEditing::getOrCreateLayerId( sqlite3* db, const QString& qgisLayerId )
809 {
810  QString sql = QString( "SELECT \"id\" FROM 'log_layer_ids' WHERE \"qgis_id\" = '%1'" ).arg( qgisLayerId );
811  int layerId = sqlQueryInt( db, sql, -1 );
812  if ( layerId == -1 )
813  {
814  // next layer id
815  sql = "SELECT \"last_index\" FROM 'log_indices' WHERE \"name\" = 'layer_id'";
816  int newLayerId = sqlQueryInt( db, sql, -1 );
817 
818  // insert layer
819  sql = QString( "INSERT INTO 'log_layer_ids' VALUES (%1, '%2')" ).arg( newLayerId ).arg( qgisLayerId );
820  sqlExec( db, sql );
821 
822  // increase layer_id
823  // TODO: use trigger for auto increment?
824  sql = QString( "UPDATE 'log_indices' SET 'last_index' = %1 WHERE \"name\" = 'layer_id'" ).arg( newLayerId + 1 );
825  sqlExec( db, sql );
826 
827  layerId = newLayerId;
828  }
829 
830  return layerId;
831 }
832 
834 {
835  QString sql = "SELECT \"last_index\" FROM 'log_indices' WHERE \"name\" = 'commit_no'";
836  return sqlQueryInt( db, sql, -1 );
837 }
838 
840 {
841  QString sql = QString( "UPDATE 'log_indices' SET 'last_index' = %1 WHERE \"name\" = 'commit_no'" ).arg( getCommitNo( db ) + 1 );
842  sqlExec( db, sql );
843 }
844 
845 void QgsOfflineEditing::addFidLookup( sqlite3* db, int layerId, QgsFeatureId offlineFid, QgsFeatureId remoteFid )
846 {
847  QString sql = QString( "INSERT INTO 'log_fids' VALUES ( %1, %2, %3 )" ).arg( layerId ).arg( offlineFid ).arg( remoteFid );
848  sqlExec( db, sql );
849 }
850 
852 {
853  QString sql = QString( "SELECT \"remote_fid\" FROM 'log_fids' WHERE \"layer_id\" = %1 AND \"offline_fid\" = %2" ).arg( layerId ).arg( offlineFid );
854  return sqlQueryInt( db, sql, -1 );
855 }
856 
858 {
859  QString sql = QString( "SELECT \"offline_fid\" FROM 'log_fids' WHERE \"layer_id\" = %1 AND \"remote_fid\" = %2" ).arg( layerId ).arg( remoteFid );
860  return sqlQueryInt( db, sql, -1 );
861 }
862 
864 {
865  QString sql = QString( "SELECT COUNT(\"fid\") FROM 'log_added_features' WHERE \"layer_id\" = %1 AND \"fid\" = %2" ).arg( layerId ).arg( fid );
866  return ( sqlQueryInt( db, sql, 0 ) > 0 );
867 }
868 
869 int QgsOfflineEditing::sqlExec( sqlite3* db, const QString& sql )
870 {
871  char * errmsg;
872  int rc = sqlite3_exec( db, sql.toUtf8(), NULL, NULL, &errmsg );
873  if ( rc != SQLITE_OK )
874  {
875  showWarning( errmsg );
876  }
877  return rc;
878 }
879 
880 int QgsOfflineEditing::sqlQueryInt( sqlite3* db, const QString& sql, int defaultValue )
881 {
882  sqlite3_stmt* stmt = NULL;
883  if ( sqlite3_prepare_v2( db, sql.toUtf8().constData(), -1, &stmt, NULL ) != SQLITE_OK )
884  {
885  showWarning( sqlite3_errmsg( db ) );
886  return defaultValue;
887  }
888 
889  int value = defaultValue;
890  int ret = sqlite3_step( stmt );
891  if ( ret == SQLITE_ROW )
892  {
893  value = sqlite3_column_int( stmt, 0 );
894  }
895  sqlite3_finalize( stmt );
896 
897  return value;
898 }
899 
900 QList<int> QgsOfflineEditing::sqlQueryInts( sqlite3* db, const QString& sql )
901 {
902  QList<int> values;
903 
904  sqlite3_stmt* stmt = NULL;
905  if ( sqlite3_prepare_v2( db, sql.toUtf8().constData(), -1, &stmt, NULL ) != SQLITE_OK )
906  {
907  showWarning( sqlite3_errmsg( db ) );
908  return values;
909  }
910 
911  int ret = sqlite3_step( stmt );
912  while ( ret == SQLITE_ROW )
913  {
914  values << sqlite3_column_int( stmt, 0 );
915 
916  ret = sqlite3_step( stmt );
917  }
918  sqlite3_finalize( stmt );
919 
920  return values;
921 }
922 
923 QList<QgsField> QgsOfflineEditing::sqlQueryAttributesAdded( sqlite3* db, const QString& sql )
924 {
925  QList<QgsField> values;
926 
927  sqlite3_stmt* stmt = NULL;
928  if ( sqlite3_prepare_v2( db, sql.toUtf8().constData(), -1, &stmt, NULL ) != SQLITE_OK )
929  {
930  showWarning( sqlite3_errmsg( db ) );
931  return values;
932  }
933 
934  int ret = sqlite3_step( stmt );
935  while ( ret == SQLITE_ROW )
936  {
937  QgsField field( QString(( const char* )sqlite3_column_text( stmt, 0 ) ),
938  ( QVariant::Type )sqlite3_column_int( stmt, 1 ),
939  "", // typeName
940  sqlite3_column_int( stmt, 2 ),
941  sqlite3_column_int( stmt, 3 ),
942  QString(( const char* )sqlite3_column_text( stmt, 4 ) ) );
943  values << field;
944 
945  ret = sqlite3_step( stmt );
946  }
947  sqlite3_finalize( stmt );
948 
949  return values;
950 }
951 
953 {
954  QgsFeatureIds values;
955 
956  sqlite3_stmt* stmt = NULL;
957  if ( sqlite3_prepare_v2( db, sql.toUtf8().constData(), -1, &stmt, NULL ) != SQLITE_OK )
958  {
959  showWarning( sqlite3_errmsg( db ) );
960  return values;
961  }
962 
963  int ret = sqlite3_step( stmt );
964  while ( ret == SQLITE_ROW )
965  {
966  values << sqlite3_column_int( stmt, 0 );
967 
968  ret = sqlite3_step( stmt );
969  }
970  sqlite3_finalize( stmt );
971 
972  return values;
973 }
974 
976 {
977  AttributeValueChanges values;
978 
979  sqlite3_stmt* stmt = NULL;
980  if ( sqlite3_prepare_v2( db, sql.toUtf8().constData(), -1, &stmt, NULL ) != SQLITE_OK )
981  {
982  showWarning( sqlite3_errmsg( db ) );
983  return values;
984  }
985 
986  int ret = sqlite3_step( stmt );
987  while ( ret == SQLITE_ROW )
988  {
989  AttributeValueChange change;
990  change.fid = sqlite3_column_int( stmt, 0 );
991  change.attr = sqlite3_column_int( stmt, 1 );
992  change.value = QString(( const char* )sqlite3_column_text( stmt, 2 ) );
993  values << change;
994 
995  ret = sqlite3_step( stmt );
996  }
997  sqlite3_finalize( stmt );
998 
999  return values;
1000 }
1001 
1003 {
1004  GeometryChanges values;
1005 
1006  sqlite3_stmt* stmt = NULL;
1007  if ( sqlite3_prepare_v2( db, sql.toUtf8().constData(), -1, &stmt, NULL ) != SQLITE_OK )
1008  {
1009  showWarning( sqlite3_errmsg( db ) );
1010  return values;
1011  }
1012 
1013  int ret = sqlite3_step( stmt );
1014  while ( ret == SQLITE_ROW )
1015  {
1016  GeometryChange change;
1017  change.fid = sqlite3_column_int( stmt, 0 );
1018  change.geom_wkt = QString(( const char* )sqlite3_column_text( stmt, 1 ) );
1019  values << change;
1020 
1021  ret = sqlite3_step( stmt );
1022  }
1023  sqlite3_finalize( stmt );
1024 
1025  return values;
1026 }
1027 
1028 void QgsOfflineEditing::committedAttributesAdded( const QString& qgisLayerId, const QList<QgsField>& addedAttributes )
1029 {
1030  sqlite3* db = openLoggingDb();
1031  if ( db == NULL )
1032  {
1033  return;
1034  }
1035 
1036  // insert log
1037  int layerId = getOrCreateLayerId( db, qgisLayerId );
1038  int commitNo = getCommitNo( db );
1039 
1040  for ( QList<QgsField>::const_iterator it = addedAttributes.begin(); it != addedAttributes.end(); ++it )
1041  {
1042  QgsField field = *it;
1043  QString sql = QString( "INSERT INTO 'log_added_attrs' VALUES ( %1, %2, '%3', %4, %5, %6, '%7' )" )
1044  .arg( layerId )
1045  .arg( commitNo )
1046  .arg( field.name() )
1047  .arg( field.type() )
1048  .arg( field.length() )
1049  .arg( field.precision() )
1050  .arg( field.comment() );
1051  sqlExec( db, sql );
1052  }
1053 
1054  increaseCommitNo( db );
1055  sqlite3_close( db );
1056 }
1057 
1058 void QgsOfflineEditing::committedFeaturesAdded( const QString& qgisLayerId, const QgsFeatureList& addedFeatures )
1059 {
1060  sqlite3* db = openLoggingDb();
1061  if ( db == NULL )
1062  {
1063  return;
1064  }
1065 
1066  // insert log
1067  int layerId = getOrCreateLayerId( db, qgisLayerId );
1068 
1069  // get new feature ids from db
1070  QgsMapLayer* layer = QgsMapLayerRegistry::instance()->mapLayer( qgisLayerId );
1071  QgsDataSourceURI uri = QgsDataSourceURI( layer->source() );
1072 
1073  // only store feature ids
1074  QString sql = QString( "SELECT ROWID FROM '%1' ORDER BY ROWID DESC LIMIT %2" ).arg( uri.table() ).arg( addedFeatures.size() );
1075  QList<int> newFeatureIds = sqlQueryInts( db, sql );
1076  for ( int i = newFeatureIds.size() - 1; i >= 0; i-- )
1077  {
1078  QString sql = QString( "INSERT INTO 'log_added_features' VALUES ( %1, %2 )" )
1079  .arg( layerId )
1080  .arg( newFeatureIds.at( i ) );
1081  sqlExec( db, sql );
1082  }
1083 
1084  sqlite3_close( db );
1085 }
1086 
1087 void QgsOfflineEditing::committedFeaturesRemoved( const QString& qgisLayerId, const QgsFeatureIds& deletedFeatureIds )
1088 {
1089  sqlite3* db = openLoggingDb();
1090  if ( db == NULL )
1091  {
1092  return;
1093  }
1094 
1095  // insert log
1096  int layerId = getOrCreateLayerId( db, qgisLayerId );
1097 
1098  for ( QgsFeatureIds::const_iterator it = deletedFeatureIds.begin(); it != deletedFeatureIds.end(); ++it )
1099  {
1100  if ( isAddedFeature( db, layerId, *it ) )
1101  {
1102  // remove from added features log
1103  QString sql = QString( "DELETE FROM 'log_added_features' WHERE \"layer_id\" = %1 AND \"fid\" = %2" ).arg( layerId ).arg( *it );
1104  sqlExec( db, sql );
1105  }
1106  else
1107  {
1108  QString sql = QString( "INSERT INTO 'log_removed_features' VALUES ( %1, %2)" )
1109  .arg( layerId )
1110  .arg( *it );
1111  sqlExec( db, sql );
1112  }
1113  }
1114 
1115  sqlite3_close( db );
1116 }
1117 
1118 void QgsOfflineEditing::committedAttributeValuesChanges( const QString& qgisLayerId, const QgsChangedAttributesMap& changedAttrsMap )
1119 {
1120  sqlite3* db = openLoggingDb();
1121  if ( db == NULL )
1122  {
1123  return;
1124  }
1125 
1126  // insert log
1127  int layerId = getOrCreateLayerId( db, qgisLayerId );
1128  int commitNo = getCommitNo( db );
1129 
1130  for ( QgsChangedAttributesMap::const_iterator cit = changedAttrsMap.begin(); cit != changedAttrsMap.end(); ++cit )
1131  {
1132  QgsFeatureId fid = cit.key();
1133  if ( isAddedFeature( db, layerId, fid ) )
1134  {
1135  // skip added features
1136  continue;
1137  }
1138  QgsAttributeMap attrMap = cit.value();
1139  for ( QgsAttributeMap::const_iterator it = attrMap.begin(); it != attrMap.end(); ++it )
1140  {
1141  QString sql = QString( "INSERT INTO 'log_feature_updates' VALUES ( %1, %2, %3, %4, '%5' )" )
1142  .arg( layerId )
1143  .arg( commitNo )
1144  .arg( fid )
1145  .arg( it.key() ) // attr
1146  .arg( it.value().toString() ); // value
1147  sqlExec( db, sql );
1148  }
1149  }
1150 
1151  increaseCommitNo( db );
1152  sqlite3_close( db );
1153 }
1154 
1155 void QgsOfflineEditing::committedGeometriesChanges( const QString& qgisLayerId, const QgsGeometryMap& changedGeometries )
1156 {
1157  sqlite3* db = openLoggingDb();
1158  if ( db == NULL )
1159  {
1160  return;
1161  }
1162 
1163  // insert log
1164  int layerId = getOrCreateLayerId( db, qgisLayerId );
1165  int commitNo = getCommitNo( db );
1166 
1167  for ( QgsGeometryMap::const_iterator it = changedGeometries.begin(); it != changedGeometries.end(); ++it )
1168  {
1169  QgsFeatureId fid = it.key();
1170  if ( isAddedFeature( db, layerId, fid ) )
1171  {
1172  // skip added features
1173  continue;
1174  }
1175  QgsGeometry geom = it.value();
1176  QString sql = QString( "INSERT INTO 'log_geometry_updates' VALUES ( %1, %2, %3, '%4' )" )
1177  .arg( layerId )
1178  .arg( commitNo )
1179  .arg( fid )
1180  .arg( geom.exportToWkt() );
1181  sqlExec( db, sql );
1182 
1183  // TODO: use WKB instead of WKT?
1184  }
1185 
1186  increaseCommitNo( db );
1187  sqlite3_close( db );
1188 }
1189 
1191 {
1192  QgsVectorLayer* vLayer = qobject_cast<QgsVectorLayer *>( sender() );
1193  // enable logging
1194  connect( vLayer->editBuffer(), SIGNAL( committedAttributesAdded( const QString&, const QList<QgsField>& ) ),
1195  this, SLOT( committedAttributesAdded( const QString&, const QList<QgsField>& ) ) );
1196  connect( vLayer, SIGNAL( committedFeaturesAdded( const QString&, const QgsFeatureList& ) ),
1197  this, SLOT( committedFeaturesAdded( const QString&, const QgsFeatureList& ) ) );
1198  connect( vLayer, SIGNAL( committedFeaturesRemoved( const QString&, const QgsFeatureIds& ) ),
1199  this, SLOT( committedFeaturesRemoved( const QString&, const QgsFeatureIds& ) ) );
1200  connect( vLayer->editBuffer(), SIGNAL( committedAttributeValuesChanges( const QString&, const QgsChangedAttributesMap& ) ),
1201  this, SLOT( committedAttributeValuesChanges( const QString&, const QgsChangedAttributesMap& ) ) );
1202  connect( vLayer->editBuffer(), SIGNAL( committedGeometriesChanges( const QString&, const QgsGeometryMap& ) ),
1203  this, SLOT( committedGeometriesChanges( const QString&, const QgsGeometryMap& ) ) );
1204 }
1205 
1207 {
1208  QgsVectorLayer* vLayer = qobject_cast<QgsVectorLayer *>( sender() );
1209  // disable logging
1210  disconnect( vLayer->editBuffer(), SIGNAL( committedAttributesAdded( const QString&, const QList<QgsField>& ) ),
1211  this, SLOT( committedAttributesAdded( const QString&, const QList<QgsField>& ) ) );
1212  disconnect( vLayer, SIGNAL( committedFeaturesAdded( const QString&, const QgsFeatureList& ) ),
1213  this, SLOT( committedFeaturesAdded( const QString&, const QgsFeatureList& ) ) );
1214  disconnect( vLayer, SIGNAL( committedFeaturesRemoved( const QString&, const QgsFeatureIds& ) ),
1215  this, SLOT( committedFeaturesRemoved( const QString&, const QgsFeatureIds& ) ) );
1216  disconnect( vLayer->editBuffer(), SIGNAL( committedAttributeValuesChanges( const QString&, const QgsChangedAttributesMap& ) ),
1217  this, SLOT( committedAttributeValuesChanges( const QString&, const QgsChangedAttributesMap& ) ) );
1218  disconnect( vLayer->editBuffer(), SIGNAL( committedGeometriesChanges( const QString&, const QgsGeometryMap& ) ),
1219  this, SLOT( committedGeometriesChanges( const QString&, const QgsGeometryMap& ) ) );
1220 }
1221 
1223 {
1224  // detect offline layer
1225  if ( layer->customProperty( CUSTOM_PROPERTY_IS_OFFLINE_EDITABLE, false ).toBool() )
1226  {
1227  QgsVectorLayer* vLayer = qobject_cast<QgsVectorLayer *>( layer );
1228  connect( vLayer, SIGNAL( editingStarted() ), this, SLOT( startListenFeatureChanges() ) );
1229  connect( vLayer, SIGNAL( editingStopped() ), this, SLOT( stopListenFeatureChanges() ) );
1230  }
1231 }
1232 
1233