~qeef/damn-project

3 2

SQLite as DB alternative

Details
Message ID
<bd4eeb77-f669-9aa4-8e20-58e67003bb22@net-c.cat>
DKIM signature
missing
Download raw message
Hi,

When developing damn did you consider SQLite? If yes, why did you end up 
choosing Postgres? If not, do you think deploying with SQLite instead of 
Postgres is feasible?

Thanks
Details
Message ID
<Y5i8UQLaHmpKQ7Lx@gmail.com>
In-Reply-To
<bd4eeb77-f669-9aa4-8e20-58e67003bb22@net-c.cat> (view parent)
DKIM signature
missing
Download raw message
Hello and sorry for late reply.

> When developing damn did you consider SQLite? If yes, why did you end
> up choosing Postgres?

First, the damn-server depends on PostGIS extension [1]. Second, it is
designed for greater loads because of mapathon use-case.

> If not, do you think deploying with SQLite instead of Postgres is
> feasible?

I do not think so. damn-server is "just API to the database" -- based on
requests over HTTP, damn-server decides which SQL query to run. A lot
can be done by just queries, see for example st_divide function [2] and
its use when saving the area [3].

Please, note small inconsistency that is going to be solved as part of
https://todo.sr.ht/~qeef/damn-project/7 hopefully this year yet. In the
README of the damn-server, it is mentioned that it is possible to run
the docker image with the PostGIS database from the damn-deploy [4].
However, along with the steps in the README, to sucessfully build the
damndb docker image, the content of the damndb/Dockerfile needs to be
changed to:

	FROM postgis/postgis:11-3.3-alpine

	ENV POSTGRES_PASSWORD ${POSTGRES_PASSWORD:-pass}
	ENV POSTGRES_USER ${POSTGRES_USER:-damnuser}
	ENV POSTGRES_DB ${POSTGRES_DB:-damndb}

	ADD --chown=postgres https://git.sr.ht/~qeef/damn-server/blob/master/20_create_damn_db.sql /docker-entrypoint-initdb.d/

[1]: https://postgis.net/
[2]: https://git.sr.ht/~qeef/damn-server/tree/master/item/20_create_damn_db.sql
[3]: https://git.sr.ht/~qeef/damn-server/tree/master/item/damn_server/area.py#L158
[4]: https://git.sr.ht/~qeef/damn-deploy
Details
Message ID
<27ef17e6-7fe2-fe5e-5338-776207cd17e0@net-c.cat>
In-Reply-To
<Y5i8UQLaHmpKQ7Lx@gmail.com> (view parent)
DKIM signature
missing
Download raw message
Thanks for the reply!


There's at least SpatiaLite[0] as a GIS extension for SQLite. How good
it is, and how it compares to PostGIS (feature-wise and language-wise) I
don't know, I haven't used either.


RE loads: that makes sense, though it's possible to tweak SQLite for
better performance in concurrent contexts (such as enbaling WAL
mode[1][2]).  It's also possible to serialize SQLite access (e.g. with a
"proxy class"), and with more SQLite tweaks (allowing it to assume
single-process and single-thread[3], plus [4]), I doubt it would be much
slower than Postgres.

(Aside: I had a big performance improvement by enabling WAL mode in a
two-writers scenario, where one of them rarely writes and the other
mostly reads. But I can't comment much more than that, I would need to
try things out in context.)


For context, what prompted the email was that I'd like to use DAMN for
small (1 person) tasks and but feel it's overkill to use your public
instance. So I'd like to run DAMN on my desktop/laptop but I'm not too
keen on having to setup Postgres+PostGIS (it's just overkill for this
usecase).


[0]: https://www.gaia-gis.it/fossil/libspatialite/index
[1]: https://www.sqlite.org/wal.html
[2]: https://www.sqlite.org/pragma.html#pragma_journal_mode
[3]: https://www.sqlite.org/faq.html#q6
[4]: https://www.sqlite.org/pragma.html#pragma_locking_mode
Details
Message ID
<Y5pXLYdxQWdGW1fh@gmail.com>
In-Reply-To
<27ef17e6-7fe2-fe5e-5338-776207cd17e0@net-c.cat> (view parent)
DKIM signature
missing
Download raw message
> For context, what prompted the email was that I'd like to use DAMN for
> small (1 person) tasks and but feel it's overkill to use your public
> instance.

Feel free to use it, if it is not a problem that you need to be online
to lock/unlock squares. You may set the priority to 0 to unlist the
area.

> So I'd like to run DAMN on my desktop/laptop but I'm not too keen on
> having to setup Postgres+PostGIS (it's just overkill for this
> usecase).

You don't need to set up Postgres + PostGIS, if you can run docker and
docker-compose. I have updated the damn-deploy repository, so the
instructions in the damn-server README works now:

	git clone https://git.sr.ht/~qeef/damn-deploy
	cd damn-deploy
	sed -i '/POSTGRES_PASSWORD=.*/d' .env
	docker-compose -f http.yml up db

However, running damn project on localhost for the purpose of personal
maping is overkill, indeed.

I think that the proper solution is to improve JOSM TODO plugin.
Meanwhile, you may try to use divide_to_squares.py script [1] and the
following workflow:

1. Create GeoJSON of area you want to map.
2. ./divide_to_squares.py area.geojson
3. Open squares.geojson in JOSM.
4. Ctrl + F -> put tmp to Search string -> Enter.
5. Add selected squares to TODO plugin.

I do not say it is perfect.

[1]: https://git.sr.ht/~qeef/py-geojson/tree/master/item/divide_to_squares.py
Reply to thread Export thread (mbox)