<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
color:black;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";
color:black;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0cm;
margin-right:0cm;
margin-bottom:0cm;
margin-left:36.0pt;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
color:black;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
color:black;}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:Consolas;
color:black;}
span.EmailStyle21
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle23
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:8217539;
mso-list-type:hybrid;
mso-list-template-ids:234912470 134807567 134807577 134807579 134807567 134807577 134807579 134807567 134807577 134807579;}
@list l0:level1
{mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:54.0pt;
text-indent:-18.0pt;}
@list l0:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:90.0pt;
text-indent:-18.0pt;}
@list l0:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
margin-left:126.0pt;
text-indent:-9.0pt;}
@list l0:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:162.0pt;
text-indent:-18.0pt;}
@list l0:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:198.0pt;
text-indent:-18.0pt;}
@list l0:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
margin-left:234.0pt;
text-indent:-9.0pt;}
@list l0:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:270.0pt;
text-indent:-18.0pt;}
@list l0:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:306.0pt;
text-indent:-18.0pt;}
@list l0:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
margin-left:342.0pt;
text-indent:-9.0pt;}
@list l1
{mso-list-id:780489503;
mso-list-type:hybrid;
mso-list-template-ids:1665437620 -2024132050 134807555 134807557 134807553 134807555 134807557 134807553 134807555 134807557;}
@list l1:level1
{mso-level-start-at:0;
mso-level-number-format:bullet;
mso-level-text:-;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:54.0pt;
text-indent:-18.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-font-family:Calibri;}
@list l1:level2
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:90.0pt;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l1:level3
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:126.0pt;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l1:level4
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:162.0pt;
text-indent:-18.0pt;
font-family:Symbol;}
@list l1:level5
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:198.0pt;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l1:level6
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:234.0pt;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l1:level7
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:270.0pt;
text-indent:-18.0pt;
font-family:Symbol;}
@list l1:level8
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:306.0pt;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l1:level9
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:342.0pt;
text-indent:-18.0pt;
font-family:Wingdings;}
ol
{margin-bottom:0cm;}
ul
{margin-bottom:0cm;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body bgcolor="white" lang="EN-GB" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="color:windowtext;mso-fareast-language:EN-US">Hi,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:windowtext;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:windowtext;mso-fareast-language:EN-US">I think the issue is not really the downsides of the file format so much as the more recent promotion of GeoPackage as a default format over shapefiles.
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:windowtext;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:windowtext;mso-fareast-language:EN-US">In my view it is unreasonable to expect the average user to delve into this level of detail and I would personally expect that the software (or documentation) would make clear any
serious issues such as data corruption risks. Personally I’d go further and say work to avoid the situation at all.
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:windowtext;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:windowtext;mso-fareast-language:EN-US">This is why I asked the question really as there seem to me to be two key points:<o:p></o:p></span></p>
<ol style="margin-top:0cm" start="1" type="1">
<li class="MsoListParagraph" style="color:windowtext;margin-left:18.0pt;mso-list:l0 level1 lfo2">
<span style="mso-fareast-language:EN-US">There is some evidence that read-only access may be editing the file, see
</span><span style="color:black"><a href="https://github.com/qgis/QGIS/issues/23991">https://github.com/qgis/QGIS/issues/23991</a></span><span style="mso-fareast-language:EN-US"><o:p></o:p></span></li><li class="MsoListParagraph" style="color:windowtext;margin-left:18.0pt;mso-list:l0 level1 lfo2">
<span style="color:black">Ideally it would be possible for QGIS to prevent multiple edit sessions</span><span style="mso-fareast-language:EN-US"><o:p></o:p></span></li></ol>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">IMHO the usefulness of the format seems limited if it is not possible for multiple users to view the same data. That said, the bug report I’ve linked to shows that other agree and the issue is being dealt with using the normal methods.
<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">The second point is really the focus of why I originally asked the question. It is clear that SQLite itself cannot achieve this as it is unaware of edit sessions however QGIS could simply create a sidecar file as a worst case and that should
work. If the sidecar file exists then editing is not possible unless you own the file otherwise the file is available for editing.
<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">In simple terms I want QGIS to say no (and explain it is already being edited in a dialog) if a second user tries to put a GeoPackage in edit mode when someone else is already editing it. <o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I’ve already created a section in my bespoke plugin which corrects the bad projections for MapInfo tab files so I might just write the functionality above myself using Python.
<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Cheers,<o:p></o:p></p>
<p class="MsoNormal">Paul<span style="color:windowtext;mso-fareast-language:EN-US"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:windowtext;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US" style="color:windowtext">From:</span></b><span lang="EN-US" style="color:windowtext"> Jonathan Moules <jonathan-lists@lightpear.com>
<br>
<b>Sent:</b> 07 October 2019 15:17<br>
<b>To:</b> Tobias Wendorff <tobias.wendorff@tu-dortmund.de>; Andrea Peri <aperi2007@gmail.com><br>
<b>Cc:</b> qgis-user <qgis-user@lists.osgeo.org>; Paul Wittle <P.Wittle@dorsetcc.gov.uk><br>
<b>Subject:</b> Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p>(A little late).<o:p></o:p></p>
<p>TL;DR: at least for QGIS, is - never multi-user edit SQLite/SpatiaLite/GeoPackages on network file systems.<o:p></o:p></p>
<p>SQLite, (and therefore SpatiaLite and GeoPackage) has quite a few caveats when it comes to multiple users trying to edit it at once.
<o:p></o:p></p>
<p><a href="https://www2.sqlite.org/howtocorrupt.html">https://www2.sqlite.org/howtocorrupt.html</a><o:p></o:p></p>
<p>(my bold)<o:p></o:p></p>
<p>"SQLite depends on the underlying filesystem to do locking as the documentation says it will. But some filesystems contain bugs in their locking logic such that the locks do not always behave as advertised.
<b>This is especially true of network filesystems and NFS in particular.</b> If SQLite is used on a filesystem where the locking primitives contain bugs, and if two or more threads or processes try to access the same database at the same time, then database
corruption might result."<o:p></o:p></p>
<p>And there's also:<o:p></o:p></p>
<p><a href="https://www.sqlite.org/whentouse.html">https://www.sqlite.org/whentouse.html</a><o:p></o:p></p>
<p>Put simply (Note: I'm not an expert): It's fine to edit SQLite databases if they're not on a network file system with as many users as you want, or if they are on a network and you can guarantee only one process is going to write. However if multiple people/processes
want to write to a network file system, you'll need a piece of middleware to manage the process, otherwise there's a good chance of corruption as Paul is seeing.<o:p></o:p></p>
<p>It may also be that QGIS is doing some of the other things on the "how to corrupt" page too. I imagine it will only get worse if you use multiple different software packages to edit simultaneously.<o:p></o:p></p>
<p>Cheers,<o:p></o:p></p>
<p>Jonathan<o:p></o:p></p>
<div>
<p class="MsoNormal">On 2019-09-27 09:50, Tobias Wendorff wrote:<o:p></o:p></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<pre>Am 27.09.2019 um 10:24 schrieb Andrea Peri <a href="mailto:aperi2007@gmail.com"><aperi2007@gmail.com></a>:<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>Have you tried to use spatialite instead of geopackage. ?<o:p></o:p></pre>
</blockquote>
<pre><o:p> </o:p></pre>
<pre>Why not plain SQLite? Nobody needs and uses the spatial functions of Spatialite, they are even not part of bloatware GPKG (sorry, the created db-files are huge without any compression).<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>The only reason is indexing and this could be forked off GPGK and Spatialite.<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>To the topic: I think, it‘s always a bad idea to let multiple users work on a single SQLite-based database. It hasn‘t been created for this reason.<o:p></o:p></pre>
<p class="MsoNormal"><br>
<br>
<o:p></o:p></p>
<pre>_______________________________________________<o:p></o:p></pre>
<pre>Qgis-user mailing list<o:p></o:p></pre>
<pre><a href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a><o:p></o:p></pre>
<pre>List info: <a href="https://lists.osgeo.org/mailman/listinfo/qgis-user">https://lists.osgeo.org/mailman/listinfo/qgis-user</a><o:p></o:p></pre>
<pre>Unsubscribe: <a href="https://lists.osgeo.org/mailman/listinfo/qgis-user">https://lists.osgeo.org/mailman/listinfo/qgis-user</a><o:p></o:p></pre>
</blockquote>
</div>
This e-mail and any files transmitted with it are intended solely for the use of the individual or entity to whom they are addressed. It may contain unclassified but sensitive or protectively marked material and should be handled accordingly. Unless you are
the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All traffic may be subject to recording and/or
monitoring in accordance with relevant legislation. Any views expressed in this message are those of the individual sender, except where the sender specifies and with authority, states them to be the views of Dorset Council. Dorset Council does not accept
service of documents by fax or other electronic means. Virus checking: Whilst all reasonable steps have been taken to ensure that this electronic communication and its attachments whether encoded, encrypted or otherwise supplied are free from computer viruses,
Dorset Council accepts no liability in respect of any loss, cost, damage or expense suffered as a result of accessing this message or any of its attachments. For information on how Dorset Council processes your information, please see www.dorsetcouncil.gov.uk/416433
</body>
</html>