<html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"></head><body>Thanks Even,<div><br></div><div>It definitely doesn't sound like an easy solution. </div><div><br></div><div>I wonder how others deal with it and maybe someone in the developers list will have some ideas</div><div><br></div><div>I have just been applying band aid fixes for the last year to prevent the gpkgs from updating themselves but yesterday I discovered the beauty of storing rasters in the gpkg format. Unfortunately the rasters make the gpkgs large and so I can't have them just start uploading on a field tablet via cell connection when they get viewed in Q or other app like Roam. </div><div><br></div><div> I have been trying to get off shapefiles for 15 years now because of their limitations but I find I have to keep going back to them</div><div><br></div><div>Rob </div><div><br></div><div style="font-size:100%;color:#000000"><!-- originalMessage --><div>-------- Original message --------</div><div>From: Even Rouault <even.rouault@spatialys.com> </div><div>Date: 2018-04-09 16:22 (GMT-05:00) </div><div>To: qgis-user@lists.osgeo.org </div><div>Cc: rjwillson@gmail.com </div><div>Subject: Re: [Qgis-user] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited </div><div><br></div></div>On lundi 9 avril 2018 15:24:41 CEST rjwillson@gmail.com wrote:<br>> I would really like to use and promote GeoPackage as the go to data format<br>> in my organization but I am having the following problem that I haven't been<br>> able to solve.<br>> <br>> <br>> <br>> When ever someone opens a Q template project that has a layer with a<br>> GeoPackage data source, the GeoPackage file (not its contents) will be<br>> altered and it will show that it has been updated in the Windows file<br>> manager. I have been told that this happens because the write ahead logging<br>> has been triggered (creation of *.wal and *.shm) files by simply viewing the<br>> contents of the GeoPackage in QGIS and/or in Intramaps Roam (works on top<br>> of QGIS for tablets).<br>> <br>> <br>> <br>> This is problematic because most users in our organization will then look at<br>> the windows modified date time stamp and conclude that the file's contents<br>> have been modified. Additionally, all of the GIS layers are stored in<br>> Dropbox and subsequently Dropbox concludes that the file has been modified<br>> and it re-uploads a new copy of the GeoPackage. When it is a large<br>> GeoPackage > 1 GB in size this is quite problematic and causes a cascade of<br>> uploads and downloads.<br>> <br>> <br>> <br>> Over the last few months I have tried to suppress this modification for the<br>> large GeoPackages in our system in the following ways: (1) make the layer<br>> read only in the QGIS project (no effect); (2) make the Dropbox folder read<br>> only for all users except the Dropbox administrator (this is effective for<br>> the large base layers); (3) change the properties of the file in windows to<br>> read only (effective for the Dropbox administrator); and (4) add a user<br>> variable to the windows system environment variables: OGR_SQLITE_JOURNAL =<br>> DELETE (effective for QGIS but not for Intramaps Roam)<br>> <br>> <br>> <br>> Combining 2, 3, and 4 allows me to use GeoPackage for layers I can ensure<br>> are placed in the read-only Dropbox folders.<br>> <br>> <br>> <br>> It is likely that my organization will never need the simultaneous editing<br>> of datasets that is facilitated by Write-Ahead Logging, but I would really<br>> like to be able to replace shapefiles and File geodatabases with GeoPackages<br>> in all of our projects going forward.<br>> <br>> <br>> <br>> Therefore, is there a simpler way to suppress the write ahead logging<br>> behaviour that causes the files to believe they have been updated for all<br>> GeoPackages created in QGIS or used in a QGIS project?<br><br>My memory is already fading about the details, but the reason for the current <br>behaviour is that if you open a GeoPackage database in read-only non-WAL mode <br>and someone else try to edit it concurrently, unpleasant locking will occur. <br>Actually due to how QGIS works, locking could occur even in the same session <br>(since background rendering threads can open read-only connections while the <br>main connection is writing data). See https://issues.qgis.org/issues/15351<br><br>So the solution was:<br><br>commit b6b8759efbeb833d0d3dbf6df008087701361ad3<br>Author: Even Rouault <even.rouault@spatialys.com><br>Date: Thu Sep 22 23:23:00 2016 +0200<br><br> Fix database locking when editing GeoPackage<br> <br> Concurrent read and write can lock a GeoPackage database given<br> the default journaling mode of SQLite (delete). Use WAL when<br> possible to avoid that.<br> <br> Fixes #15351<br><br>I understand your use case as well, but I don't have a solution in mind that <br>would fit all. <br>I don't remember if I tried scenarios where one connection would open in <br>DELETE mode and another one would try to turn to WAL on. <br>A quick try shows that the a connection that opens in DELETE mode doesn't see <br>the change to WAL done by another one. And WAL documentation doesn't describe <br>this scenario, so it is quite unknown what would happen. So the current <br>behaviour is to be on the safe side and always turn on WAL on opening.<br><br>Or perhaps have some option in a dialog box somewhere! "I promise that I will <br>not edit this file in any concurrent session, or in this session, so you can <br>safely open it in DELETE mode" ? Or spend more time digging again into the <br>details of sqlite locking to see if we could make a clever use of it, and/or <br>possibly improving it in sqlite itself (rather involved!)<br><br>Even<br><br>-- <br>Spatialys - Geospatial professional services<br>http://www.spatialys.com<br></body></html>