Thanks for the many insights Jukka, it has helped me a lot in thinking where to
dig and what to look at.
First, and this is what matters the most to me, I was able to confirm data
integrity by comparing for each table the pg version to the sqlite version.
Some types are altered (e.g. Dates which do not exist in sqlite and therefore
became strings, and a JSON which also became a string, even though sqlite has a
json type) but that's not a big issue.
I confirm that Foreign keys constraint are gone, if I run a "spatialite
mydb.sqlite .schema | grep FOREI", only the spatialite extension tables are
left. As you said it implies that the safety net is gone and the database no
longer prevents me from making edits I shouldn't do but since this database is
intended mainly for read only usage and not data management, that is perfectly
acceptable.
As for the association tables, you guessed right, the composite key is gone and
an additional ogc_fid primary key is added.
Thanks again,
Kind regards,
Loïc
From: Rahkonen Jukka (MML)
Sent: 03 March 2022 13:55:33
To: DUTRIEUX Loic (JRC-ISPRA); gdal-dev@lists.osgeo.org
Subject: Re: Implications of "Multi-column primary key in [table] to supported"
warning
Hi,
If you have a table with multi-column primary key (composite key) then the
target table in Spatialite obviously will not have the same PK. I would guess
that none of the columns in the composite is unique and usable as PK so the
only possibility is to create a new unique column for the PK. Therefore the
tables that you will have are not identical with the source tables by their
schemas. The foreign key constraints between the tables are dropped any way,
ogr2ogr does not try to transfer them ever.
I think you can answer your question yourself by making a test with your own
data. If all of the tables that have a composite PK get a new PK field in
Spatialite (I guess it will of type "integer autoincrement") and if the row
counts in source and target tables match I believe that the data has been
transferred right. When it comes to question "is it safe to assume that db
relations won't get mixed up" you do not have any constrained relations left in
Spatialite. The data integrity is OK immediately after the conversion but at
that stage there is nothing in Spatialite that prevents you from making edits
that you should't do. But you can add similar constraints that you have in
PostGIS afterward into SQLite
https://urldefense.com/v3/__https://sqlite.org/foreignkeys.html__;!!DOxrgLBm!XPxXzkROhjk7yXj_UMlx2cbkKXEZfzDfmaMiH6Vp7pfUEbQ4fUUGEDTCfGCvSCFs8-qDs1E$
. I am not sure that all type of constraints that you can have in PostGIS are
supported in SQLite but foreign key should behave in the same way.
SQLite driver
https://urldefense.com/v3/__https://gdal.org/drivers/vector/sqlite.html__;!!DOxrgLBm!XPxXzkROhjk7yXj_UMlx2cbkKXEZfzDfmaMiH6Vp7pfUEbQ4fUUGEDTCfGCvSCFskfue4zU$
supports prelude_statements but if you want to re-create constraints you
would need something like postlude_statements. It means that ogr2ogr cannot do
the whole job but you must run the required SQL statements with ogrinfo or some
SQLite client.
-Jukka Rahkonen-
-Alkuperäinen viesti-
Lähettäjä: gdal-dev Puolesta DUTRIEUX Loic
Lähetetty: torstai 3. maaliskuuta 2022 13.56
Vastaanottaja: gdal-dev@lists.osgeo.org
Aihe: [gdal-dev] Implications of "Multi-column primary key in [table] to
supported" warning
Hi everyone,
Quick question, I'm using ogr2ogr to clone a postGIS database to a spatialite
file database (by the way, very convenient that ogr2ogr handles the transfer of
non-spatial tables too). The database contains many-to-many relations and as a
consequence association tables with multiple primary keys. When I run ogr2ogr I
get a warning saying that Multi-column primary key are not supported.
Should I be worried of that warning for my use case or is it safe to assume
that db relations won't get mixed up.
Thanks and kind regards,
Loïc
___
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://urldefense.com/v3/__https://lists.osgeo.org/mailman/listinfo/gdal-dev__;!!DOxrgLBm!XPxXzkROhjk7yXj_UMlx2cbkKXEZfzDfmaMiH6Vp7pfUEbQ4fUUGEDTCfGCvSCFsH0SPbLQ$
___
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev
