Spatialite geodatabase for QGIS : How to limit writing

[Silver Ashen]

Hello everybody,

I work in a small company with 3 people doing often simple GIS stuff,
and 7 people doing sometimes very very basic stuff in GIS.

I would like to store all the shapefiles and MapInfo layers in some thematic spatialite databases.

But I really want to avoid a situation where two or more people are editing the same layer.

I red that Spatialite is not the most appropriate tool for manage multi-access, but PostGIS is a little bit more
complicated for my colleagues and I want to stay simple.

Is it possible to manage the rights of read and write layers in Spatialite (QGIS) ?

Bye.

[a.fu...@lqt.it]

Hi Silver,

> But I really want to avoid a situation where two or more people are
> editing the same layer.

> Is it possible to manage the rights of read and write layers in
> Spatialite (QGIS) ?
>

sorry, absolutely no.
the main difference distinguishing SQLite (and obviously SpatiaLite)
from any other DBMS is in that SQLite is a "personal DBMS".
i.e. it offers absolutely nothing intended to support multi-users
concurrent access.
and there is absolutely nothing implementing individual access
permissions.

very shortly said:
- a single user at each time can safely access a SQLite/SpatiaLite
database file without any restriction
- many concurrent users can safely read at the same time from the
same database file.
but this is true uniquely for strict "read-only" access.
- if two users will attempt to write at the same time a big chaos
will arise for sure. the first request will pass succesfully;
the second one will simply abort returning a "DATABASE IS LOCKED"
errorr message.
it's not a defect: it simply is the natural consequence of the
highly peculiar "light-weight" architecture adopted by SQLite.

> I red that Spatialite is not the most appropriate tool for manage
> multi-access
>

exactly: SpatiaLite and PostGIS are roughly equivalent. Both them
supports the same Spatial SQL capabilities.
* if you absolutely need to support unrestricted multi-user concurrency
PostGIS certainly is the "right tool" for your work.
* if you don't care at all about multi-users, you can safely switch
to the simpler SQLite/SpatiaLite.

> but PostGIS is a little bit more complicated for my colleagues
> and I want to stay simple.
>

You can't have your cake and eat it too :-)

SQLite/SpatiaLite are simpler and easier than PostGIS mainly because
they decided to completely sacrifice multi-users concurrency.
If they'll eventually decide to support multiple concurrent access
they'll inexorably become as complex as PostGIS is.

bye Sandro

--
Il messaggio e' stato analizzato alla ricerca di virus o
contenuti pericolosi da MailScanner, ed e'
risultato non infetto.

[Andrea P.]

Not always the lock capability of a standard dbms are pretty useful for a gis editing.

The dbms lock capabilities is on single transaction So you can avoid the collision on a single write operation.
But not on the ususal editing of a layer.

This is a capability inside the gis editor you are using.

Use only a transactional multiuser dbms is not sufficient to avoid the collision on a layer editing.

[James Card]

On Wed, 12 Jun 2013 06:35:56 -0700, Silver Ashen <silver...@gmail.com>
wrote:

> But I really want to avoid a situation where two or more people are
> editing the same layer.
>

> Is it possible to manage the rights of read and write layers in
> Spatialite (QGIS) ?

This is not what Spatialite was designed for, but it seems to me that it
might be possible to develop a QGIS plug-in that controls write-access to
specific layers. You would have to train (and enforce the policy) that
your users always only access their layers under the control of that
plug-in. Perhaps it is possible, but certainly not an optimum solution.

--
James Card -- <http://jdcard.com/>
Often there are several ways to understand a given set of
facts; some of them may be more useful than others.

[a.fu...@lqt.it]

Hi James,

> This is not what Spatialite was designed for, but it seems to me that
> it might be possible to develop a QGIS plug-in that controls
> write-access to specific layers. You would have to train (and
> enforce
> the policy) that your users always only access their layers under
> the
> control of that plug-in. Perhaps it is possible, but certainly not
> an
> optimum solution.
>

I strongly doubt that such an approach could really work.

What SQLite actually forbids is *any* concurrent access in
write-mode; if User-A will attempt to update Table-A at the
same exact time when User-B will attempt to insert a new row
into Table-B, one of two requests will be inexorably rejected
(and thus will be irremediably lost).

This is because during "hot write" operations SQLite sets a
RESERVED lock interesting both the db-file itself and the
corresponding journal file.

Remember: there is no centralized server process accessing
physical storage in SQLite.
so we'll simply have two unrelated instances of libsqlite
competing between them in order to get an exclusive lock;
one wins, the other fails.

You can eventually find further infos about locking as
implemented by SQLite from here:
http://www.sqlite.org/lockingv3.html

[Andrea P.]

We eperiment a similar approach using the triggers.

We realize a plugin to write.
To avoid that unwanted editing was able to change the geometries we set a trigger that check for a value on a table.
On true allow writw.
On false denied write.

Il giorno mercoledì 12 giugno 2013 15:35:56 UTC+2, Silver Ashen ha scritto:

[Silver Ashen]

Thank you very much to all of you for your precise answers to that question.

If concurrent users can only read the same layers, what about the stability of this reading for Windows XP or 7 users ?

I red somewhere on gis.stackexchange (sorry I don't find the post) that it could be some problems with Windows and multi-access...What can I expect ?

Thanks again.

2013/6/12 <a.fu...@lqt.it>

--
You received this message because you are subscribed to a topic in the Google Groups "SpatiaLite Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/spatialite-users/zb6DP-Q8uMU/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to spatialite-users+unsubscribe@googlegroups.com.
To post to this group, send email to spatialite-users@googlegroups.com.
Visit this group at http://groups.google.com/group/spatialite-users?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

[JaakL]

Hello,

I would suggest to give PostGIS a try, it should not be really that hard: just install it and use. For Mac there is http://postgresapp.com/ which is very easy to set up, and for windows there is step-by-step guide http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut01 . You need to install database on one machine and use it, for simpler cases no other management needed. For sure it would be less haggle than trying to hack Spatialite for multiuser usage.

Jaak

[Silver Ashen]

Thanks Jaak,

I'm used with PostGIS but I'm just in an internship, and I'm scared to put them on PostGIS without much time to learn them how to get comfortable enough with it :).

But you're right, if multi-access is finally so important, it's easyer to get on PostGIS than to try to crack Spatialite.

Samy

2013/6/13 JaakL <ja...@nutiteq.com>

--

You received this message because you are subscribed to a topic in the Google Groups "SpatiaLite Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/spatialite-users/zb6DP-Q8uMU/unsubscribe?hl=en.

To unsubscribe from this group and all its topics, send an email to spatialite-use...@googlegroups.com.
To post to this group, send email to spatiali...@googlegroups.com.

[Alex Mandel]

If they are using QGIS as the frontend, not much is different. Only 1
person has to know how to manage users/permissions on the postgres
server side.

Enjoy,
Alex

On 06/12/2013 11:37 PM, Silver Ashen wrote:
> Thanks Jaak,
>
> I'm used with PostGIS but I'm just in an internship, and I'm scared to put
> them on PostGIS without much time to learn them how to get comfortable
> enough with it :).
>
> But you're right, if multi-access is finally so important, it's easyer to
> get on PostGIS than to try to crack Spatialite.
>
> Samy
>
>
> 2013/6/13 JaakL <ja...@nutiteq.com>
>
>> Hello,
>>
>> I would suggest to give PostGIS a try, it should not be really that hard:
>> just install it and use. For Mac there is http://postgresapp.com/ which
>> is very easy to set up, and for windows there is step-by-step guide

>> http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut01. You need to install database on one machine and use it, for simpler cases

[Andrea P.]

In QGIS there is a plugin names RT_SQL_LAyer.
That plugin allow an user to add a layer from postgis defining relatime the sql query .
The main goal of that plugin is that is allow an user to have a read-only account on postgres.

So you could give to all your user read-only account and to only power-users the read-write-accounts.

Regards,

Andrea.

Il giorno mercoledì 12 giugno 2013 15:35:56 UTC+2, Silver Ashen ha scritto: