streamline SQLAlchemy DB schema

Remove all information not strictly used by the frontend as well as
any index information. This will make it easier to create a SQLite
database from the schema.

diff --git a/nominatim/db/sqlalchemy_schema.py b/nominatim/db/sqlalchemy_schema.py
index 2ca518cad06cc888113434ba3a168b5247602f49..3b769eb3e34fff7dd16ee12e3d2946c5887392f9 100644 (file)
--- a/nominatim/db/sqlalchemy_schema.py
+++ b/nominatim/db/sqlalchemy_schema.py
@@ -41,6 +41,9 @@ class SqliteTypes:
 #pylint: disable=too-many-instance-attributes
 class SearchTables:
     """ Data class that holds the tables of the Nominatim database.
 #pylint: disable=too-many-instance-attributes
 class SearchTables:
     """ Data class that holds the tables of the Nominatim database.

+
+        This schema strictly reflects the read-access view of the database.
+        Any data used for updates only will not be visible.

     """
 
     def __init__(self, meta: sa.MetaData, engine_name: str) -> None:
     """
 
     def __init__(self, meta: sa.MetaData, engine_name: str) -> None:


@@ -63,14 +66,13 @@ class SearchTables:
             sa.Column('value', sa.Text))
 
         self.placex = sa.Table('placex', meta,
             sa.Column('value', sa.Text))
 
         self.placex = sa.Table('placex', meta,

-            sa.Column('place_id', sa.BigInteger, nullable=False, unique=True),
+            sa.Column('place_id', sa.BigInteger, nullable=False),

             sa.Column('parent_place_id', sa.BigInteger),
             sa.Column('linked_place_id', sa.BigInteger),
             sa.Column('importance', sa.Float),
             sa.Column('indexed_date', sa.DateTime),
             sa.Column('rank_address', sa.SmallInteger),
             sa.Column('rank_search', sa.SmallInteger),
             sa.Column('parent_place_id', sa.BigInteger),
             sa.Column('linked_place_id', sa.BigInteger),
             sa.Column('importance', sa.Float),
             sa.Column('indexed_date', sa.DateTime),
             sa.Column('rank_address', sa.SmallInteger),
             sa.Column('rank_search', sa.SmallInteger),

-            sa.Column('partition', sa.SmallInteger),

             sa.Column('indexed_status', sa.SmallInteger),
             sa.Column('osm_type', sa.String(1), nullable=False),
             sa.Column('osm_id', sa.BigInteger, nullable=False),
             sa.Column('indexed_status', sa.SmallInteger),
             sa.Column('osm_type', sa.String(1), nullable=False),
             sa.Column('osm_id', sa.BigInteger, nullable=False),


@@ -88,33 +90,31 @@ class SearchTables:
             sa.Column('centroid', Geometry))
 
         self.addressline = sa.Table('place_addressline', meta,
             sa.Column('centroid', Geometry))
 
         self.addressline = sa.Table('place_addressline', meta,

-            sa.Column('place_id', sa.BigInteger, index=True),
-            sa.Column('address_place_id', sa.BigInteger, index=True),
+            sa.Column('place_id', sa.BigInteger),
+            sa.Column('address_place_id', sa.BigInteger),

             sa.Column('distance', sa.Float),
             sa.Column('distance', sa.Float),

-            sa.Column('cached_rank_address', sa.SmallInteger),

             sa.Column('fromarea', sa.Boolean),
             sa.Column('isaddress', sa.Boolean))
 
         self.postcode = sa.Table('location_postcode', meta,
             sa.Column('fromarea', sa.Boolean),
             sa.Column('isaddress', sa.Boolean))
 
         self.postcode = sa.Table('location_postcode', meta,

-            sa.Column('place_id', sa.BigInteger, unique=True),
+            sa.Column('place_id', sa.BigInteger),

             sa.Column('parent_place_id', sa.BigInteger),
             sa.Column('rank_search', sa.SmallInteger),
             sa.Column('rank_address', sa.SmallInteger),
             sa.Column('indexed_status', sa.SmallInteger),
             sa.Column('indexed_date', sa.DateTime),
             sa.Column('country_code', sa.String(2)),
             sa.Column('parent_place_id', sa.BigInteger),
             sa.Column('rank_search', sa.SmallInteger),
             sa.Column('rank_address', sa.SmallInteger),
             sa.Column('indexed_status', sa.SmallInteger),
             sa.Column('indexed_date', sa.DateTime),
             sa.Column('country_code', sa.String(2)),

-            sa.Column('postcode', sa.Text, index=True),
+            sa.Column('postcode', sa.Text),

             sa.Column('geometry', Geometry))
 
         self.osmline = sa.Table('location_property_osmline', meta,
             sa.Column('geometry', Geometry))
 
         self.osmline = sa.Table('location_property_osmline', meta,

-            sa.Column('place_id', sa.BigInteger, nullable=False, unique=True),
+            sa.Column('place_id', sa.BigInteger, nullable=False),

             sa.Column('osm_id', sa.BigInteger),
             sa.Column('parent_place_id', sa.BigInteger),
             sa.Column('indexed_date', sa.DateTime),
             sa.Column('startnumber', sa.Integer),
             sa.Column('endnumber', sa.Integer),
             sa.Column('step', sa.SmallInteger),
             sa.Column('osm_id', sa.BigInteger),
             sa.Column('parent_place_id', sa.BigInteger),
             sa.Column('indexed_date', sa.DateTime),
             sa.Column('startnumber', sa.Integer),
             sa.Column('endnumber', sa.Integer),
             sa.Column('step', sa.SmallInteger),

-            sa.Column('partition', sa.SmallInteger),

             sa.Column('indexed_status', sa.SmallInteger),
             sa.Column('linegeo', Geometry),
             sa.Column('address', self.types.Composite),
             sa.Column('indexed_status', sa.SmallInteger),
             sa.Column('linegeo', Geometry),
             sa.Column('address', self.types.Composite),


@@ -125,7 +125,6 @@ class SearchTables:
             sa.Column('country_code', sa.String(2)),
             sa.Column('name', self.types.Composite),
             sa.Column('derived_name', self.types.Composite),
             sa.Column('country_code', sa.String(2)),
             sa.Column('name', self.types.Composite),
             sa.Column('derived_name', self.types.Composite),

-            sa.Column('country_default_language_code', sa.Text),

             sa.Column('partition', sa.Integer))
 
         self.country_grid = sa.Table('country_osm_grid', meta,
             sa.Column('partition', sa.Integer))
 
         self.country_grid = sa.Table('country_osm_grid', meta,


@@ -135,12 +134,12 @@ class SearchTables:
 
         # The following tables are not necessarily present.
         self.search_name = sa.Table('search_name', meta,
 
         # The following tables are not necessarily present.
         self.search_name = sa.Table('search_name', meta,

-            sa.Column('place_id', sa.BigInteger, index=True),
+            sa.Column('place_id', sa.BigInteger),

             sa.Column('importance', sa.Float),
             sa.Column('search_rank', sa.SmallInteger),
             sa.Column('address_rank', sa.SmallInteger),
             sa.Column('importance', sa.Float),
             sa.Column('search_rank', sa.SmallInteger),
             sa.Column('address_rank', sa.SmallInteger),

-            sa.Column('name_vector', self.types.IntArray, index=True),
-            sa.Column('nameaddress_vector', self.types.IntArray, index=True),
+            sa.Column('name_vector', self.types.IntArray),
+            sa.Column('nameaddress_vector', self.types.IntArray),

             sa.Column('country_code', sa.String(2)),
             sa.Column('centroid', Geometry))
 
             sa.Column('country_code', sa.String(2)),
             sa.Column('centroid', Geometry))
 


@@ -150,6 +149,5 @@ class SearchTables:
             sa.Column('startnumber', sa.Integer),
             sa.Column('endnumber', sa.Integer),
             sa.Column('step', sa.SmallInteger),
             sa.Column('startnumber', sa.Integer),
             sa.Column('endnumber', sa.Integer),
             sa.Column('step', sa.SmallInteger),

-            sa.Column('partition', sa.SmallInteger),

             sa.Column('linegeo', Geometry),
             sa.Column('postcode', sa.Text))
             sa.Column('linegeo', Geometry),
             sa.Column('postcode', sa.Text))