Feature/sqlite support

Resolves https://git.gfz-potsdam.de/dynamicexposure/globaldynamicexposure/loss-calculator/-/issues/72

\approve @ds @tara

exposurelib/database.py

@@ -27,6 +27,7 @@ from exposurelib.utils import get_geom_of_quadkey
 from shapely.geometry import box
 from pygeotile.tile import Tile
 from collections import namedtuple
+from packaging import version
 
 
 logger = logging.getLogger(__name__)
@@ -60,6 +61,7 @@ class SpatialiteDatabase:
 
         self.connection = None
         self.cursor = None
+        self.old_sqlite_version = False
 
     def connect(self, init_spatial_metadata=True):
         """
@@ -110,6 +112,10 @@ class SpatialiteDatabase:
             logger.debug("SQLite version: %s" % row[0])
             logger.debug("Spatialite version: %s" % row[1])
 
+            # check if sqlite version <3.35
+            if version.parse(row[0]) < version.parse("3.35.0"):
+                self.old_sqlite_version = True
+
         self.cursor = self.connection.cursor()
 
     def close(self):
@@ -135,6 +141,11 @@ class ExposureDatabase(SpatialiteDatabase):
     def __init__(self, database_filepath, spatialite_filepath="mod_spatialite"):
         SpatialiteDatabase.__init__(self, database_filepath, spatialite_filepath)
 
+    def commit_and_close(self):
+
+        self.connection.commit()
+        self.close()
+
     def create_tables(self):
         """
         Creates all necessary tables in the database. These are:
@@ -264,7 +275,12 @@ class ExposureDatabase(SpatialiteDatabase):
         logger.debug("Table AssessmentSource created")
 
     def create_damage_view(
-        self, view_name, entity, assessment_source_id, damage_state_id=None, overwrite=True
+        self,
+        view_name,
+        entity,
+        assessment_source_id,
+        damage_state_id=None,
+        overwrite=True,
     ):
         """
         Creates a view of damage assessments with the geometry from the respective entity.
@@ -765,8 +781,20 @@ class ExposureDatabase(SpatialiteDatabase):
             date,
             comment,
         )
-        sql_statement += "RETURNING id"
-        self.cursor.execute(sql_statement)
+
+        # The RETURNING statement is not supported for SQLite versions<3.34.
+        # Therefore, we do a SELECT statement.
+        if self.old_sqlite_version:
+            self.cursor.execute(sql_statement)
+
+            sql_statement = "SELECT id FROM DamageAssessment "
+            sql_statement += "WHERE entity_id = %d " % entity_id
+            sql_statement += "AND assessment_source_id = %d" % assessment_source_id
+            self.cursor.execute(sql_statement)
+        else:
+            sql_statement += "RETURNING id"
+            self.cursor.execute(sql_statement)
+
         return self.cursor.fetchone()[0]
 
     def insert_damage(self, assessment_id, damage_state_id, damage_probability):
@@ -888,7 +916,13 @@ class ExposureDatabase(SpatialiteDatabase):
         return result[0]
 
     def insert_complete_damage_assessment(
-        self, entity_id, assessment_source_id, damage_values, damage_ids, date=None, comment=""
+        self,
+        entity_id,
+        assessment_source_id,
+        damage_values,
+        damage_ids,
+        date=None,
+        comment="",
     ):
         """
         Inserts a complete damage assessment to the database. It creates the entry in