Re: [Qgis-user] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited

2020-08-21 Thread baswein 
I too am struggling with how to use geopackages with a team. And am wondering
if anyone has figured out how to keep them from being marked as changed when
opened for viewing in QGIS.  This is fine with small geopackages but with
large ones it uses up a lot of bandwidth.
QSettings().setValue("/qgis/walForSqlite3", False) makes it not enter WAL
mode but it still is marked as changed. Changing the environment variable
OGR_SQLITE_JOURNAL = DELETE also turns off WAL mode but still marks the file
as changes. We use google file stream and it is not possible to mark the
file as read only in google file steam so that is not an option. 
I guess my question is are geopackages just incompatible with any sort of
syncing service- google drive, drive file stream, dropbox, one drive, etc? 
If that is the case I think that could be a severe limiting factor for the
format as these file syncing services are ubiquitous at this point and are
likely to increase in the increasingly work from home environment that we
are facing.  
In my naivety I image a system that does not mark the file as changed when
loaded but when any of the layers are toggled for editing it creates a lock
file that prevents other users from editing the file as MS Word does. After
the layer is saved and editing is toggled off the lock file disappears (is
this what the WAL file si doing?). I know there could still be issues with
network latency but if google decided that there is a conflict and it
creates a duplicate file.  
Please don't make me go back to shpfls! :)
-Bas  



--
Sent from: http://osgeo-org.1560.x6.nabble.com/QGIS-User-f4125267.html
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited

2018-04-19 Thread Borys Jurgiel 
> Hi Borys,
> 
> Are you sure that this is also a problem with 2.x?
> 
> If I remember correctly 2.x browser panel does not open gpkg for scanning
> contents, this feature was added for 3.x.

Ok, the problem with 2.18 is a bit different. It is limited to concurrent use 
of two QGIS 
instances, and only occurs for some gpkgs:

1. Run QGIS 2.18, go to the Browser and expand a directory containing a gpkg.
2. Run another QGIS instance and load any layer from that gpkg.
3. The loop begins: the wal and shm files are repeatedly created and removed 
from the 
disk, and the expanded branch is being refreshed.

Your fixes of course didn't help this particular case. I don't observe it in 
QGIS 3, so I'm 
leaving it as it was. Furthermore, I didn't manage to reproduce it with every 
gpkg.

Regards,
Borys


___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited

2018-04-10 Thread Borys Jurgiel 
Me wrote:

> Bingo! Seems it's exactly the 2.18 issue. I'll backport it this afternoon
> (unless you guys are faster) and ensure it helps.

Ooops, not today, as I just realized I don't have GDAL 2 in my ld 
QGIS2 dev environment (and Qt4 in my current one). I'll sort it out 
before the 2.18.19 release.

Borys

___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited

2018-04-10 Thread Even Rouault 
On mardi 10 avril 2018 08:11:30 CEST Richard Duivenvoorde wrote:
> On 09-04-18 22:22, Even Rouault wrote:
> > Or perhaps have some option in a dialog box somewhere! "I promise that I
> > will not edit this file in any concurrent session, or in this session, so
> > you can safely open it in DELETE mode" ? Or spend more time digging again
> > into the details of sqlite locking to see if we could make a clever use
> > of it, and/or possibly improving it in sqlite itself (rather involved!)
> 
> I would be ok with an option in the dialog, though it is hard to promise
> that somebody *else* will not open it too in a non readonly mode, in a
> networked work environment ...

You'll have issues with sqlite in a networking environment, as WAL cannot work 
in that context (QGIS does some effort to detect if the file location is a 
network driver or not, before deciding to turn WAL on). So you'll get the 
default journaling behaviour, which is lock prone.

> 
> Is it a possibility (at least on linux) to look at the file permissions?
> That would open the possibility to put a read-only (permissioned) file,
> and in that way you would promise to not edit/delete?

Just tested: QGIS can open file a read-only .gpkg on Linux (it will fallback 
to the default journaling mode), and presumably on Windows as well.

Or were you talking about some side car files that would be used to 
application level locking ?

-- 
Spatialys - Geospatial professional services
http://www.spatialys.com
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited

2018-04-10 Thread Borys Jurgiel 
Thanks a lot!
Dnia wtorek, 10 kwietnia 2018 09:01:56 CEST Alessandro Pasotti pisze:
> See:
> 
> https://github.com/qgis/QGIS/commit/
1d4c6061a8a5512092041aafea14a8a32247d046
> #diff-e89106528fe0226767dfccab355e44dc
> 
> but I'm afraid this wasn't all of it.

Bingo! Seems it's exactly the 2.18 issue. I'll backport it this afternoon 
(unless you 
guys are faster) and ensure it helps.

Regards,
Borys
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited

2018-04-10 Thread Borys Jurgiel 
Dnia wtorek, 10 kwietnia 2018 08:11:48 CEST Alessandro Pasotti pisze:

> Hi, I also faced this problem when Implementing gpkg support in the QGIS
> browser panel: when the file browser scanned the folder, it opened the
> gpkg, which in turn changed its file stats and made the directory watcher
> think that something has changed in the directory, which in turns started
> scanning again in a delicious endless loop :)
> 
> I think I solved this with a kind of locker for the directory scan thread,
> but it was't easy.

Ah so this it the loop in 2.18... Could you recall that commit that fixed it in 
3.0? I 
understand it's not trivial to backport it to 2.18? 

Regards,
Borys
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited

2018-04-10 Thread Borys Jurgiel 
> Hi Borys,
> 
> Are you sure that this is also a problem with 2.x?
> 
> If I remember correctly 2.x browser panel does not open gpkg for scanning
> contents, this feature was added for 3.x.

It doesn't show the GPKG contents, but it starts the loop (sometimes?) when 
scanning a 
directory containing a GPKG.

Now I recall also a rush in the system file browser - like the wal and shm 
files were 
repeatedly removed and created back. I don't remember with which QGIS version 
and 
which OS I observed it, but I'll try to reproduce it.

Regards,
Borys

PS. Shouldn't we move to the developer ML? :)
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited

2018-04-10 Thread Alessandro Pasotti 
See:

https://github.com/qgis/QGIS/commit/1d4c6061a8a5512092041aafea14a8a32247d046#diff-e89106528fe0226767dfccab355e44dc

but I'm afraid this wasn't all of it.

See also:

https://github.com/qgis/QGIS/commit/bcb48f2bdc8f274cbc68f376489430f45e0b17d1#diff-e89106528fe0226767dfccab355e44dc



On Tue, Apr 10, 2018 at 8:55 AM, Nyall Dawson 
wrote:

> On 10 April 2018 at 16:47, Alessandro Pasotti  wrote:
> > On Tue, Apr 10, 2018 at 8:33 AM, Borys Jurgiel 
> > wrote:
> >>
> >> Dnia wtorek, 10 kwietnia 2018 08:11:48 CEST Alessandro Pasotti pisze:
> >>
> >>
> >>
> >> > Hi, I also faced this problem when Implementing gpkg support in the
> QGIS
> >>
> >> > browser panel: when the file browser scanned the folder, it opened the
> >>
> >> > gpkg, which in turn changed its file stats and made the directory
> >> > watcher
> >>
> >> > think that something has changed in the directory, which in turns
> >> > started
> >>
> >> > scanning again in a delicious endless loop :)
> >>
> >> >
> >>
> >> > I think I solved this with a kind of locker for the directory scan
> >> > thread,
> >>
> >> > but it was't easy.
> >>
> >>
> >>
> >> Ah so this it the loop in 2.18... Could you recall that commit that
> fixed
> >> it in 3.0? I understand it's not trivial to backport it to 2.18?
> >
> >
> > Hi Borys,
> >
> > Are you sure that this is also a problem with 2.x?
> >
> > If I remember correctly 2.x browser panel does not open gpkg for scanning
> > contents, this feature was added for 3.x.
>
> I've seen this with recent versions of 2.18
>
> Nyall
>



-- 
Alessandro Pasotti
w3:   www.itopen.it
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited

2018-04-10 Thread Nyall Dawson 
On 10 April 2018 at 16:47, Alessandro Pasotti  wrote:
> On Tue, Apr 10, 2018 at 8:33 AM, Borys Jurgiel 
> wrote:
>>
>> Dnia wtorek, 10 kwietnia 2018 08:11:48 CEST Alessandro Pasotti pisze:
>>
>>
>>
>> > Hi, I also faced this problem when Implementing gpkg support in the QGIS
>>
>> > browser panel: when the file browser scanned the folder, it opened the
>>
>> > gpkg, which in turn changed its file stats and made the directory
>> > watcher
>>
>> > think that something has changed in the directory, which in turns
>> > started
>>
>> > scanning again in a delicious endless loop :)
>>
>> >
>>
>> > I think I solved this with a kind of locker for the directory scan
>> > thread,
>>
>> > but it was't easy.
>>
>>
>>
>> Ah so this it the loop in 2.18... Could you recall that commit that fixed
>> it in 3.0? I understand it's not trivial to backport it to 2.18?
>
>
> Hi Borys,
>
> Are you sure that this is also a problem with 2.x?
>
> If I remember correctly 2.x browser panel does not open gpkg for scanning
> contents, this feature was added for 3.x.

I've seen this with recent versions of 2.18

Nyall
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited

2018-04-10 Thread Alessandro Pasotti 
On Tue, Apr 10, 2018 at 8:33 AM, Borys Jurgiel 
wrote:

> Dnia wtorek, 10 kwietnia 2018 08:11:48 CEST Alessandro Pasotti pisze:
>
>
>
> > Hi, I also faced this problem when Implementing gpkg support in the QGIS
>
> > browser panel: when the file browser scanned the folder, it opened the
>
> > gpkg, which in turn changed its file stats and made the directory watcher
>
> > think that something has changed in the directory, which in turns started
>
> > scanning again in a delicious endless loop :)
>
> >
>
> > I think I solved this with a kind of locker for the directory scan
> thread,
>
> > but it was't easy.
>
>
>
> Ah so this it the loop in 2.18... Could you recall that commit that fixed
> it in 3.0? I understand it's not trivial to backport it to 2.18?
>

Hi Borys,

Are you sure that this is also a problem with 2.x?

If I remember correctly 2.x browser panel does not open gpkg for scanning
contents, this feature was added for 3.x.


-- 
Alessandro Pasotti
w3:   www.itopen.it
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited

2018-04-10 Thread Alessandro Pasotti 
On Tue, Apr 10, 2018 at 12:07 AM, rjwillson  wrote:

> Thanks Even,
>
> It definitely doesn't sound like an easy solution.
>
> I wonder how others deal with it and maybe someone in the developers list
> will have some ideas
>
> 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.
>
>
Hi, I also faced this problem when Implementing gpkg support in the QGIS
browser panel: when the file browser scanned the folder, it opened the
gpkg, which in turn changed its file stats and made the directory watcher
think that something has changed in the directory, which in turns started
scanning again in a delicious endless loop :)

I think I solved this with a kind of locker for the directory scan thread,
but it was't easy.

I feel that it would be really useful is we could open a gpkg from GDAL in
read-only mode without creating the WAL, expecially without creating the
WAL in the same directory (but I guess this is by design, buried deep into
sqlite code).



-- 
Alessandro Pasotti
w3:   www.itopen.it
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited

2018-04-10 Thread Richard Duivenvoorde 
On 09-04-18 22:22, Even Rouault wrote:
> Or perhaps have some option in a dialog box somewhere! "I promise that I will 
> not edit this file in any concurrent session, or in this session, so you can 
> safely open it in DELETE mode" ? Or spend more time digging again into the 
> details of sqlite locking to see if we could make a clever use of it, and/or 
> possibly improving it in sqlite itself (rather involved!)

I would be ok with an option in the dialog, though it is hard to promise
that somebody *else* will not open it too in a non readonly mode, in a
networked work environment ...

Is it a possibility (at least on linux) to look at the file permissions?
That would open the possibility to put a read-only (permissioned) file,
and in that way you would promise to not edit/delete?
There have been other issues also with the wal-files (eg browser was
also rescanning dirs etc), so it would be cool to come up with / invest
in a good solution?

Regards,

Richard
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited

2018-04-09 Thread rjwillson 
Thanks Even,
It definitely doesn't sound like an easy solution. 
I wonder how others deal with it and maybe someone in the developers list will 
have some ideas
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. 
 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
Rob 
 Original message From: Even Rouault 
 Date: 2018-04-09  16:22  (GMT-05:00) To: 
qgis-user@lists.osgeo.org Cc: rjwill...@gmail.com 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 
On lundi 9 avril 2018 15:24:41 CEST rjwill...@gmail.com wrote:
> I would really like to use and promote GeoPackage as the go to data format
> in my organization but I am having the following problem that I haven't been
> able to solve.
> 
> 
> 
> When ever someone opens a Q template project that has a layer with a
> GeoPackage data source, the GeoPackage file (not its contents) will be
> altered and it will show that it has been updated in the Windows file
> manager. I have been told that this happens because the write ahead logging
> has been triggered (creation of *.wal and *.shm) files by simply viewing the
> contents of the GeoPackage in QGIS and/or in Intramaps Roam (works on top
> of QGIS for tablets).
> 
> 
> 
> This is problematic because most users in our organization will then look at
> the windows modified date time stamp and conclude that the file's contents
> have been modified. Additionally, all of the GIS layers are stored in
> Dropbox and subsequently Dropbox concludes that the file has been modified
> and it re-uploads a new copy of the GeoPackage. When it is a large
> GeoPackage > 1 GB in size this is quite problematic and causes a cascade of
> uploads and downloads.
> 
> 
> 
> Over the last few months I have tried to suppress this modification for the
> large GeoPackages in our system in the following ways: (1) make the layer
> read only in the QGIS project (no effect); (2) make the Dropbox folder read
> only for all users except the Dropbox administrator (this is effective for
> the large base layers); (3) change the properties of the file in windows to
> read only (effective for the Dropbox administrator); and (4) add a user
> variable to the windows system environment variables: OGR_SQLITE_JOURNAL =
> DELETE (effective for QGIS but not for Intramaps Roam)
> 
> 
> 
> Combining 2, 3, and 4 allows me to use GeoPackage for layers I can ensure
> are placed in the read-only Dropbox folders.
> 
> 
> 
> It is likely that my organization will never need the simultaneous editing
> of datasets that is facilitated by Write-Ahead Logging, but I would really
> like to be able to replace shapefiles and File geodatabases with GeoPackages
> in all of our projects going forward.
> 
> 
> 
> Therefore, is there a simpler way to suppress the write ahead logging
> behaviour that causes the files to believe they have been updated for all
> GeoPackages created in QGIS or used in a QGIS project?

My memory is already fading about the details, but the reason for the current 
behaviour is that if you open a GeoPackage database in read-only non-WAL mode 
and someone else try to edit it concurrently, unpleasant locking will occur. 
Actually due to how QGIS works, locking could occur even in the same session 
(since background rendering threads can open read-only connections while the 
main connection is writing data). See https://issues.qgis.org/issues/15351

So the solution was:

commit b6b8759efbeb833d0d3dbf6df008087701361ad3
Author: Even Rouault 
Date:   Thu Sep 22 23:23:00 2016 +0200

    Fix database locking when editing GeoPackage
    
    Concurrent read and write can lock a GeoPackage database given
    the default journaling mode of SQLite (delete). Use WAL when
    possible to avoid that.
    
    Fixes #15351

I understand your use case as well, but I don't have a solution in mind that 
would fit all. 
I don't remember if I tried scenarios where one connection would open in 
DELETE mode and another one would try to turn to WAL on. 
A quick try shows that the a connection that opens in DELETE mode doesn't see 
the change to WAL done by another one. And WAL documentation doesn't describe 
this scenario, so it is quite unknown what would happen. So the current 
behaviour is to be on the safe side and always turn on WAL on opening.

Or perhaps have some option in a dialog box somewhere! "I promise that I will 
not edit this file in any concurrent session, or in this session, so you can 
safely open it in

Re: [Qgis-user] How to Prevent GeoPackage file from updating modified date when viewed in a Qgis project when it is not being edited

2018-04-09 Thread Even Rouault 
On lundi 9 avril 2018 15:24:41 CEST rjwill...@gmail.com wrote:
> I would really like to use and promote GeoPackage as the go to data format
> in my organization but I am having the following problem that I haven't been
> able to solve.
> 
> 
> 
> When ever someone opens a Q template project that has a layer with a
> GeoPackage data source, the GeoPackage file (not its contents) will be
> altered and it will show that it has been updated in the Windows file
> manager. I have been told that this happens because the write ahead logging
> has been triggered (creation of *.wal and *.shm) files by simply viewing the
> contents of the GeoPackage in QGIS and/or in Intramaps Roam (works on top
> of QGIS for tablets).
> 
> 
> 
> This is problematic because most users in our organization will then look at
> the windows modified date time stamp and conclude that the file's contents
> have been modified. Additionally, all of the GIS layers are stored in
> Dropbox and subsequently Dropbox concludes that the file has been modified
> and it re-uploads a new copy of the GeoPackage. When it is a large
> GeoPackage > 1 GB in size this is quite problematic and causes a cascade of
> uploads and downloads.
> 
> 
> 
> Over the last few months I have tried to suppress this modification for the
> large GeoPackages in our system in the following ways: (1) make the layer
> read only in the QGIS project (no effect); (2) make the Dropbox folder read
> only for all users except the Dropbox administrator (this is effective for
> the large base layers); (3) change the properties of the file in windows to
> read only (effective for the Dropbox administrator); and (4) add a user
> variable to the windows system environment variables: OGR_SQLITE_JOURNAL =
> DELETE (effective for QGIS but not for Intramaps Roam)
> 
> 
> 
> Combining 2, 3, and 4 allows me to use GeoPackage for layers I can ensure
> are placed in the read-only Dropbox folders.
> 
> 
> 
> It is likely that my organization will never need the simultaneous editing
> of datasets that is facilitated by Write-Ahead Logging, but I would really
> like to be able to replace shapefiles and File geodatabases with GeoPackages
> in all of our projects going forward.
> 
> 
> 
> Therefore, is there a simpler way to suppress the write ahead logging
> behaviour that causes the files to believe they have been updated for all
> GeoPackages created in QGIS or used in a QGIS project?

My memory is already fading about the details, but the reason for the current 
behaviour is that if you open a GeoPackage database in read-only non-WAL mode 
and someone else try to edit it concurrently, unpleasant locking will occur. 
Actually due to how QGIS works, locking could occur even in the same session 
(since background rendering threads can open read-only connections while the 
main connection is writing data). See https://issues.qgis.org/issues/15351

So the solution was:

commit b6b8759efbeb833d0d3dbf6df008087701361ad3
Author: Even Rouault 
Date:   Thu Sep 22 23:23:00 2016 +0200

Fix database locking when editing GeoPackage

Concurrent read and write can lock a GeoPackage database given
the default journaling mode of SQLite (delete). Use WAL when
possible to avoid that.

Fixes #15351

I understand your use case as well, but I don't have a solution in mind that 
would fit all. 
I don't remember if I tried scenarios where one connection would open in 
DELETE mode and another one would try to turn to WAL on. 
A quick try shows that the a connection that opens in DELETE mode doesn't see 
the change to WAL done by another one. And WAL documentation doesn't describe 
this scenario, so it is quite unknown what would happen. So the current 
behaviour is to be on the safe side and always turn on WAL on opening.

Or perhaps have some option in a dialog box somewhere! "I promise that I will 
not edit this file in any concurrent session, or in this session, so you can 
safely open it in DELETE mode" ? Or spend more time digging again into the 
details of sqlite locking to see if we could make a clever use of it, and/or 
possibly improving it in sqlite itself (rather involved!)

Even

-- 
Spatialys - Geospatial professional services
http://www.spatialys.com
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user