Merge pull request #2647 from rouault/integer_or_text

Database: decrease db size

3 files changed, 123 insertions, 109 deletions

diff --git a/data/sql/commit.sql b/data/sql/commit.sql
index 9ea2935d..e5850211 100644
--- a/data/sql/commit.sql
+++ b/data/sql/commit.sql
@@ -9,6 +9,9 @@ CREATE INDEX grid_transformation_idx ON grid_transformation(source_crs_auth_name
 CREATE INDEX other_transformation_idx ON other_transformation(source_crs_auth_name, source_crs_code, target_crs_auth_name, target_crs_code);
 CREATE INDEX concatenated_operation_idx ON concatenated_operation(source_crs_auth_name, source_crs_code, target_crs_auth_name, target_crs_code);
 
+-- We don't need to select by auth_name, code so nullify them to save space
+UPDATE usage SET auth_name = NULL, code = NULL;
+
 -- Final consistency checks
 CREATE TABLE dummy(foo);
 CREATE TRIGGER final_checks
diff --git a/data/sql/metadata.sql b/data/sql/metadata.sql
index 5a196c0b..97a951dc 100644
--- a/data/sql/metadata.sql
+++ b/data/sql/metadata.sql
@@ -7,7 +7,7 @@
 -- DATABASE_LAYOUT_VERSION_MINOR constants in src/iso19111/factory.cpp must be
 -- updated as well.
 INSERT INTO "metadata" VALUES('DATABASE.LAYOUT.VERSION.MAJOR', 1);
-INSERT INTO "metadata" VALUES('DATABASE.LAYOUT.VERSION.MINOR', 0);
+INSERT INTO "metadata" VALUES('DATABASE.LAYOUT.VERSION.MINOR', 1);
 
 INSERT INTO "metadata" VALUES('EPSG.VERSION', 'v10.018');
 INSERT INTO "metadata" VALUES('EPSG.DATE', '2021-04-02');
diff --git a/data/sql/proj_db_table_defs.sql b/data/sql/proj_db_table_defs.sql
index d7d5a93b..9b1d3dce 100644
--- a/data/sql/proj_db_table_defs.sql
+++ b/data/sql/proj_db_table_defs.sql
@@ -1,5 +1,16 @@
 --- Table structures
 
+-- Note on the INTEGER_OR_TEXT data type. This is a "non-standard" type
+-- declaration, but this is perfectly legal as SQLite is loosely typed.
+-- As this declaration contains the string INT, it is assigned INTEGER affinity.
+-- Which means that values provided either as text (that contains integer value)
+-- or integer will be stored as integers, whereas text values will be stored as
+-- text. See paragraph 3 and 3.1 of https://www.sqlite.org/datatype3.html.
+-- The "INTEGER_OR_TEXT" name is a hint for the user, and software like
+-- GDAL (>= 3.3) to expose the column as string...
+-- The effect of using this rather than TEXT is making the DB size go from
+-- 9 MB to 8.4.
+
 CREATE TABLE metadata(
     key TEXT NOT NULL PRIMARY KEY CHECK (length(key) >= 1),
     value TEXT NOT NULL
@@ -7,7 +18,7 @@ CREATE TABLE metadata(
 
 CREATE TABLE unit_of_measure(
     auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
-    code TEXT NOT NULL CHECK (length(code) >= 1),
+    code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1),
     name TEXT NOT NULL CHECK (length(name) >= 2),
     type TEXT NOT NULL CHECK (type IN ('length', 'angle', 'scale', 'time')),
     conv_factor FLOAT,
@@ -18,7 +29,7 @@ CREATE TABLE unit_of_measure(
 
 CREATE TABLE celestial_body (
     auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
-    code TEXT NOT NULL CHECK (length(code) >= 1),
+    code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1),
     name TEXT NOT NULL CHECK (length(name) >= 2),
     semi_major_axis FLOAT NOT NULL CHECK (semi_major_axis > 0), -- approximate (in metre)
     CONSTRAINT pk_celestial_body PRIMARY KEY (auth_name, code)
@@ -28,14 +39,14 @@ INSERT INTO celestial_body VALUES('PROJ', 'EARTH', 'Earth', 6378137.0);
 
 CREATE TABLE ellipsoid (
     auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
-    code TEXT NOT NULL CHECK (length(code) >= 1),
+    code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1),
     name TEXT NOT NULL CHECK (length(name) >= 2),
     description TEXT,
     celestial_body_auth_name TEXT NOT NULL,
-    celestial_body_code TEXT NOT NULL,
+    celestial_body_code INTEGER_OR_TEXT NOT NULL,
     semi_major_axis FLOAT NOT NULL CHECK (semi_major_axis > 0),
     uom_auth_name TEXT NOT NULL,
-    uom_code TEXT NOT NULL,
+    uom_code INTEGER_OR_TEXT NOT NULL,
     inv_flattening FLOAT CHECK (inv_flattening = 0 OR inv_flattening >= 1.0),
     semi_minor_axis FLOAT CHECK (semi_minor_axis > 0 AND semi_minor_axis <= semi_major_axis),
     deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)),
@@ -54,7 +65,7 @@ END;
 
 CREATE TABLE extent(
     auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
-    code TEXT NOT NULL CHECK (length(code) >= 1),
+    code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1),
     name TEXT NOT NULL CHECK (length(name) >= 2),
     description TEXT NOT NULL,
     south_lat FLOAT CHECK (south_lat BETWEEN -90 AND 90),
@@ -68,26 +79,26 @@ CREATE TABLE extent(
 
 CREATE TABLE scope(
     auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
-    code TEXT NOT NULL CHECK (length(code) >= 1),
+    code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1),
     scope TEXT NOT NULL CHECK (length(scope) >= 1),
     deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)),
     CONSTRAINT pk_scope PRIMARY KEY (auth_name, code)
 );
 
 CREATE TABLE usage(
-    auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
-    code TEXT NOT NULL CHECK (length(code) >= 1),
+    auth_name TEXT CHECK (auth_name IS NULL OR length(auth_name) >= 1),
+    code INTEGER_OR_TEXT CHECK (code IS NULL OR length(code) >= 1),
     object_table_name TEXT NOT NULL CHECK (object_table_name IN (
         'geodetic_datum', 'vertical_datum',
         'geodetic_crs', 'projected_crs', 'vertical_crs', 'compound_crs',
         'conversion', 'grid_transformation',
         'helmert_transformation', 'other_transformation', 'concatenated_operation')),
     object_auth_name TEXT NOT NULL,
-    object_code TEXT NOT NULL,
+    object_code INTEGER_OR_TEXT NOT NULL,
     extent_auth_name TEXT NOT NULL,
-    extent_code TEXT NOT NULL,
+    extent_code INTEGER_OR_TEXT NOT NULL,
     scope_auth_name TEXT NOT NULL,
-    scope_code TEXT NOT NULL,
+    scope_code INTEGER_OR_TEXT NOT NULL,
     CONSTRAINT pk_usage PRIMARY KEY (auth_name, code),
     CONSTRAINT fk_usage_extent FOREIGN KEY (extent_auth_name, extent_code) REFERENCES extent(auth_name, code),
     CONSTRAINT fk_usage_scope FOREIGN KEY (scope_auth_name, scope_code) REFERENCES scope(auth_name, code)
@@ -115,11 +126,11 @@ END;
 
 CREATE TABLE prime_meridian(
     auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
-    code TEXT NOT NULL CHECK (length(code) >= 1),
+    code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1),
     name TEXT NOT NULL CHECK (length(name) >= 2),
     longitude FLOAT NOT NULL CHECK (longitude BETWEEN -180 AND 180),
     uom_auth_name TEXT NOT NULL,
-    uom_code TEXT NOT NULL,
+    uom_code INTEGER_OR_TEXT NOT NULL,
     deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)),
     CONSTRAINT pk_prime_meridian PRIMARY KEY (auth_name, code),
     CONSTRAINT fk_prime_meridian_unit_of_measure FOREIGN KEY (uom_auth_name, uom_code) REFERENCES unit_of_measure(auth_name, code)
@@ -134,13 +145,13 @@ END;
 
 CREATE TABLE geodetic_datum (
     auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
-    code TEXT NOT NULL CHECK (length(code) >= 1),
+    code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1),
     name TEXT NOT NULL CHECK (length(name) >= 2),
     description TEXT,
     ellipsoid_auth_name TEXT NOT NULL,
-    ellipsoid_code TEXT NOT NULL,
+    ellipsoid_code INTEGER_OR_TEXT NOT NULL,
     prime_meridian_auth_name TEXT NOT NULL,
-    prime_meridian_code TEXT NOT NULL,
+    prime_meridian_code INTEGER_OR_TEXT NOT NULL,
     publication_date TEXT, --- YYYY-MM-DD format
     frame_reference_epoch FLOAT, --- only set for dynamic datum, and should be set when it is a dynamic datum
     ensemble_accuracy FLOAT CHECK (ensemble_accuracy IS NULL OR ensemble_accuracy > 0), --- only for a datum ensemble. and should be set when it is a datum ensemble
@@ -163,9 +174,9 @@ END;
 
 CREATE TABLE geodetic_datum_ensemble_member (
     ensemble_auth_name TEXT NOT NULL,
-    ensemble_code TEXT NOT NULL,
+    ensemble_code INTEGER_OR_TEXT NOT NULL,
     member_auth_name TEXT NOT NULL,
-    member_code TEXT NOT NULL,
+    member_code INTEGER_OR_TEXT NOT NULL,
     sequence INTEGER NOT NULL CHECK (sequence >= 1),
     CONSTRAINT fk_geodetic_datum_ensemble_member_ensemble FOREIGN KEY (ensemble_auth_name, ensemble_code) REFERENCES geodetic_datum(auth_name, code),
     CONSTRAINT fk_geodetic_datum_ensemble_member_ensemble_member FOREIGN KEY (member_auth_name, member_code) REFERENCES geodetic_datum(auth_name, code),
@@ -174,7 +185,7 @@ CREATE TABLE geodetic_datum_ensemble_member (
 
 CREATE TABLE vertical_datum (
     auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
-    code TEXT NOT NULL CHECK (length(code) >= 1),
+    code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1),
     name TEXT NOT NULL CHECK (length(name) >= 2),
     description TEXT,
     publication_date TEXT CHECK (NULL OR length(publication_date) = 10), --- YYYY-MM-DD format
@@ -186,9 +197,9 @@ CREATE TABLE vertical_datum (
 
 CREATE TABLE vertical_datum_ensemble_member (
     ensemble_auth_name TEXT NOT NULL,
-    ensemble_code TEXT NOT NULL,
+    ensemble_code INTEGER_OR_TEXT NOT NULL,
     member_auth_name TEXT NOT NULL,
-    member_code TEXT NOT NULL,
+    member_code INTEGER_OR_TEXT NOT NULL,
     sequence INTEGER NOT NULL CHECK (sequence >= 1),
     CONSTRAINT fk_vertical_datum_ensemble_member_ensemble FOREIGN KEY (ensemble_auth_name, ensemble_code) REFERENCES vertical_datum(auth_name, code),
     CONSTRAINT fk_vertical_datum_ensemble_member_ensemble_member FOREIGN KEY (member_auth_name, member_code) REFERENCES vertical_datum(auth_name, code),
@@ -197,7 +208,7 @@ CREATE TABLE vertical_datum_ensemble_member (
 
 CREATE TABLE coordinate_system(
     auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
-    code TEXT NOT NULL CHECK (length(code) >= 1),
+    code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1),
     type TEXT NOT NULL CHECK (type IN ('Cartesian', 'vertical', 'ellipsoidal', 'spherical', 'ordinal')),
     dimension SMALLINT NOT NULL CHECK (dimension BETWEEN 1 AND 3),
     CONSTRAINT pk_coordinate_system PRIMARY KEY (auth_name, code),
@@ -208,15 +219,15 @@ CREATE TABLE coordinate_system(
 
 CREATE TABLE axis(
     auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
-    code TEXT NOT NULL CHECK (length(code) >= 1),
+    code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1),
     name TEXT NOT NULL CHECK (length(name) >= 2),
     abbrev TEXT NOT NULL,
     orientation TEXT NOT NULL,
     coordinate_system_auth_name TEXT NOT NULL,
-    coordinate_system_code TEXT NOT NULL,
+    coordinate_system_code INTEGER_OR_TEXT NOT NULL,
     coordinate_system_order SMALLINT NOT NULL CHECK (coordinate_system_order BETWEEN 1 AND 3),
     uom_auth_name TEXT,
-    uom_code TEXT,
+    uom_code INTEGER_OR_TEXT,
     CONSTRAINT pk_axis PRIMARY KEY (auth_name, code),
     CONSTRAINT fk_axis_coordinate_system FOREIGN KEY (coordinate_system_auth_name, coordinate_system_code) REFERENCES coordinate_system(auth_name, code),
     CONSTRAINT fk_axis_unit_of_measure FOREIGN KEY (uom_auth_name, uom_code) REFERENCES unit_of_measure(auth_name, code)
@@ -233,14 +244,14 @@ END;
 
 CREATE TABLE geodetic_crs(
     auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
-    code TEXT NOT NULL CHECK (length(code) >= 1),
+    code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1),
     name TEXT NOT NULL CHECK (length(name) >= 2),
     description TEXT,
     type TEXT NOT NULL CHECK (type IN ('geographic 2D', 'geographic 3D', 'geocentric')),
     coordinate_system_auth_name TEXT,
-    coordinate_system_code TEXT,
+    coordinate_system_code INTEGER_OR_TEXT,
     datum_auth_name TEXT,
-    datum_code TEXT,
+    datum_code INTEGER_OR_TEXT,
     text_definition TEXT, -- PROJ string or WKT string. Use of this is discouraged as prone to definition ambiguities
     deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)),
     CONSTRAINT pk_geodetic_crs PRIMARY KEY (auth_name, code),
@@ -280,13 +291,13 @@ END;
 
 CREATE TABLE vertical_crs(
     auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
-    code TEXT NOT NULL CHECK (length(code) >= 1),
+    code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1),
     name TEXT NOT NULL CHECK (length(name) >= 2),
     description TEXT,
     coordinate_system_auth_name TEXT NOT NULL,
-    coordinate_system_code TEXT NOT NULL,
+    coordinate_system_code INTEGER_OR_TEXT NOT NULL,
     datum_auth_name TEXT NOT NULL,
-    datum_code TEXT NOT NULL,
+    datum_code INTEGER_OR_TEXT NOT NULL,
     deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)),
     CONSTRAINT pk_vertical_crs PRIMARY KEY (auth_name, code),
     CONSTRAINT fk_vertical_crs_coordinate_system FOREIGN KEY (coordinate_system_auth_name, coordinate_system_code) REFERENCES coordinate_system(auth_name, code),
@@ -311,7 +322,7 @@ END;
 
 CREATE TABLE conversion_method(
     auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
-    code TEXT NOT NULL CHECK (length(code) >= 1),
+    code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1),
     name TEXT NOT NULL CHECK (length(name) >= 2),
 
     CONSTRAINT pk_conversion_method PRIMARY KEY (auth_name, code)
@@ -319,7 +330,7 @@ CREATE TABLE conversion_method(
 
 CREATE TABLE conversion_param(
     auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
-    code TEXT NOT NULL CHECK (length(code) >= 1),
+    code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1),
     name TEXT NOT NULL CHECK (length(name) >= 2),
 
     CONSTRAINT pk_conversion_param PRIMARY KEY (auth_name, code)
@@ -327,63 +338,63 @@ CREATE TABLE conversion_param(
 
 CREATE TABLE conversion_table(
     auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
-    code TEXT NOT NULL CHECK (length(code) >= 1),
+    code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1),
     name TEXT NOT NULL CHECK (length(name) >= 2),
 
     description TEXT,
 
     method_auth_name TEXT CHECK (method_auth_name IS NULL OR length(method_auth_name) >= 1),
-    method_code TEXT CHECK (method_code IS NULL OR length(method_code) >= 1),
+    method_code INTEGER_OR_TEXT CHECK (method_code IS NULL OR length(method_code) >= 1),
     -- method_name TEXT,
 
     param1_auth_name TEXT,
-    param1_code TEXT,
+    param1_code INTEGER_OR_TEXT,
     -- param1_name TEXT,
     param1_value FLOAT,
     param1_uom_auth_name TEXT,
-    param1_uom_code TEXT,
+    param1_uom_code INTEGER_OR_TEXT,
 
     param2_auth_name TEXT,
-    param2_code TEXT,
+    param2_code INTEGER_OR_TEXT,
     --param2_name TEXT,
     param2_value FLOAT,
     param2_uom_auth_name TEXT,
-    param2_uom_code TEXT,
+    param2_uom_code INTEGER_OR_TEXT,
 
     param3_auth_name TEXT,
-    param3_code TEXT,
+    param3_code INTEGER_OR_TEXT,
     --param3_name TEXT,
     param3_value FLOAT,
     param3_uom_auth_name TEXT,
-    param3_uom_code TEXT,
+    param3_uom_code INTEGER_OR_TEXT,
 
     param4_auth_name TEXT,
-    param4_code TEXT,
+    param4_code INTEGER_OR_TEXT,
     --param4_name TEXT,
     param4_value FLOAT,
     param4_uom_auth_name TEXT,
-    param4_uom_code TEXT,
+    param4_uom_code INTEGER_OR_TEXT,
 
     param5_auth_name TEXT,
-    param5_code TEXT,
+    param5_code INTEGER_OR_TEXT,
     --param5_name TEXT,
     param5_value FLOAT,
     param5_uom_auth_name TEXT,
-    param5_uom_code TEXT,
+    param5_uom_code INTEGER_OR_TEXT,
 
     param6_auth_name TEXT,
-    param6_code TEXT,
+    param6_code INTEGER_OR_TEXT,
     --param6_name TEXT,
     param6_value FLOAT,
     param6_uom_auth_name TEXT,
-    param6_uom_code TEXT,
+    param6_uom_code INTEGER_OR_TEXT,
 
     param7_auth_name TEXT,
-    param7_code TEXT,
+    param7_code INTEGER_OR_TEXT,
     --param7_name TEXT,
     param7_value FLOAT,
     param7_uom_auth_name TEXT,
-    param7_uom_code TEXT,
+    param7_uom_code INTEGER_OR_TEXT,
 
     deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)),
 
@@ -675,15 +686,15 @@ END;
 
 CREATE TABLE projected_crs(
     auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
-    code TEXT NOT NULL CHECK (length(code) >= 1),
+    code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1),
     name TEXT NOT NULL CHECK (length(name) >= 2),
     description TEXT,
     coordinate_system_auth_name TEXT,
-    coordinate_system_code TEXT,
+    coordinate_system_code INTEGER_OR_TEXT,
     geodetic_crs_auth_name TEXT,
-    geodetic_crs_code TEXT,
+    geodetic_crs_code INTEGER_OR_TEXT,
     conversion_auth_name TEXT,
-    conversion_code TEXT,
+    conversion_code INTEGER_OR_TEXT,
     text_definition TEXT, -- PROJ string or WKT string. Use of this is discouraged as prone to definition ambiguities
     deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)),
     CONSTRAINT pk_projected_crs PRIMARY KEY (auth_name, code),
@@ -725,13 +736,13 @@ END;
 
 CREATE TABLE compound_crs(
     auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
-    code TEXT NOT NULL CHECK (length(code) >= 1),
+    code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1),
     name TEXT NOT NULL CHECK (length(name) >= 2),
     description TEXT,
     horiz_crs_auth_name TEXT NOT NULL,
-    horiz_crs_code TEXT NOT NULL,
+    horiz_crs_code INTEGER_OR_TEXT NOT NULL,
     vertical_crs_auth_name TEXT NOT NULL,
-    vertical_crs_code TEXT NOT NULL,
+    vertical_crs_code INTEGER_OR_TEXT NOT NULL,
     deprecated BOOLEAN NOT NULL CHECK (deprecated IN (0, 1)),
     CONSTRAINT pk_compound_crs PRIMARY KEY (auth_name, code),
     CONSTRAINT fk_compound_crs_vertical_crs FOREIGN KEY (vertical_crs_auth_name, vertical_crs_code) REFERENCES vertical_crs(auth_name, code)
@@ -762,7 +773,7 @@ END;
 
 CREATE TABLE coordinate_operation_method(
     auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
-    code TEXT NOT NULL CHECK (length(code) >= 1),
+    code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1),
     name TEXT NOT NULL CHECK (length(name) >= 2),
 
     CONSTRAINT pk_coordinate_operation_method PRIMARY KEY (auth_name, code)
@@ -770,19 +781,19 @@ CREATE TABLE coordinate_operation_method(
 
 CREATE TABLE helmert_transformation_table(
     auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
-    code TEXT NOT NULL CHECK (length(code) >= 1),
+    code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1),
     name TEXT NOT NULL CHECK (length(name) >= 2),
 
     description TEXT,
 
     method_auth_name TEXT NOT NULL CHECK (length(method_auth_name) >= 1),
-    method_code TEXT NOT NULL CHECK (length(method_code) >= 1),
+    method_code INTEGER_OR_TEXT NOT NULL CHECK (length(method_code) >= 1),
     --method_name TEXT NOT NULL CHECK (length(method_name) >= 2),
 
     source_crs_auth_name TEXT NOT NULL,
-    source_crs_code TEXT NOT NULL,
+    source_crs_code INTEGER_OR_TEXT NOT NULL,
     target_crs_auth_name TEXT NOT NULL,
-    target_crs_code TEXT NOT NULL,
+    target_crs_code INTEGER_OR_TEXT NOT NULL,
 
     accuracy FLOAT CHECK (accuracy >= 0),
 
@@ -790,36 +801,36 @@ CREATE TABLE helmert_transformation_table(
     ty FLOAT NOT NULL,
     tz FLOAT NOT NULL,
     translation_uom_auth_name TEXT NOT NULL,
-    translation_uom_code TEXT NOT NULL,
+    translation_uom_code INTEGER_OR_TEXT NOT NULL,
     rx FLOAT,
     ry FLOAT,
     rz FLOAT,
     rotation_uom_auth_name TEXT,
-    rotation_uom_code TEXT,
+    rotation_uom_code INTEGER_OR_TEXT,
     scale_difference FLOAT,
     scale_difference_uom_auth_name TEXT,
-    scale_difference_uom_code TEXT,
+    scale_difference_uom_code INTEGER_OR_TEXT,
     rate_tx FLOAT,
     rate_ty FLOAT,
     rate_tz FLOAT,
     rate_translation_uom_auth_name TEXT,
-    rate_translation_uom_code TEXT,
+    rate_translation_uom_code INTEGER_OR_TEXT,
     rate_rx FLOAT,
     rate_ry FLOAT,
     rate_rz FLOAT,
     rate_rotation_uom_auth_name TEXT,
-    rate_rotation_uom_code TEXT,
+    rate_rotation_uom_code INTEGER_OR_TEXT,
     rate_scale_difference FLOAT,
     rate_scale_difference_uom_auth_name TEXT,
-    rate_scale_difference_uom_code TEXT,
+    rate_scale_difference_uom_code INTEGER_OR_TEXT,
     epoch FLOAT,
     epoch_uom_auth_name TEXT,
-    epoch_uom_code TEXT,
+    epoch_uom_code INTEGER_OR_TEXT,
     px FLOAT, -- Pivot / evaluation point for Molodensky-Badekas
     py FLOAT,
     pz FLOAT,
     pivot_uom_auth_name TEXT,
-    pivot_uom_code TEXT,
+    pivot_uom_code INTEGER_OR_TEXT,
 
     operation_version TEXT, -- normally mandatory in OGC Topic 2 but optional here
 
@@ -1002,34 +1013,34 @@ END;
 
 CREATE TABLE grid_transformation(
     auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
-    code TEXT NOT NULL CHECK (length(code) >= 1),
+    code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1),
     name TEXT NOT NULL CHECK (length(name) >= 2),
 
     description TEXT,
 
     method_auth_name TEXT NOT NULL CHECK (length(method_auth_name) >= 1),
-    method_code TEXT NOT NULL CHECK (length(method_code) >= 1),
+    method_code INTEGER_OR_TEXT NOT NULL CHECK (length(method_code) >= 1),
     method_name TEXT NOT NULL CHECK (length(method_name) >= 2),
 
     source_crs_auth_name TEXT NOT NULL,
-    source_crs_code TEXT NOT NULL,
+    source_crs_code INTEGER_OR_TEXT NOT NULL,
     target_crs_auth_name TEXT NOT NULL,
-    target_crs_code TEXT NOT NULL,
+    target_crs_code INTEGER_OR_TEXT NOT NULL,
 
     accuracy FLOAT CHECK (accuracy >= 0),
 
     grid_param_auth_name TEXT NOT NULL,
-    grid_param_code TEXT NOT NULL,
+    grid_param_code INTEGER_OR_TEXT NOT NULL,
     grid_param_name TEXT NOT NULL,
     grid_name TEXT NOT NULL,
 
     grid2_param_auth_name TEXT,
-    grid2_param_code TEXT,
+    grid2_param_code INTEGER_OR_TEXT,
     grid2_param_name TEXT,
     grid2_name TEXT,
 
     interpolation_crs_auth_name TEXT,
-    interpolation_crs_code TEXT,
+    interpolation_crs_code INTEGER_OR_TEXT,
 
     operation_version TEXT, -- normally mandatory in OGC Topic 2 but optional here
 
@@ -1121,7 +1132,7 @@ END;
 
 CREATE TABLE other_transformation(
     auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
-    code TEXT NOT NULL CHECK (length(code) >= 1),
+    code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1),
     name TEXT NOT NULL CHECK (length(name) >= 2),
 
     description TEXT,
@@ -1131,67 +1142,67 @@ CREATE TABLE other_transformation(
     -- if method_auth_name = 'PROJ', method_code can be 'WKT' for a
     -- PROJ string and then method_name is a WKT string (CoordinateOperation)
     method_auth_name TEXT NOT NULL CHECK (length(method_auth_name) >= 1),
-    method_code TEXT NOT NULL CHECK (length(method_code) >= 1),
+    method_code INTEGER_OR_TEXT NOT NULL CHECK (length(method_code) >= 1),
     method_name TEXT NOT NULL CHECK (length(method_name) >= 2),
 
     source_crs_auth_name TEXT NOT NULL,
-    source_crs_code TEXT NOT NULL,
+    source_crs_code INTEGER_OR_TEXT NOT NULL,
     target_crs_auth_name TEXT NOT NULL,
-    target_crs_code TEXT NOT NULL,
+    target_crs_code INTEGER_OR_TEXT NOT NULL,
 
     accuracy FLOAT CHECK (accuracy >= 0),
 
     param1_auth_name TEXT,
-    param1_code TEXT,
+    param1_code INTEGER_OR_TEXT,
     param1_name TEXT,
     param1_value FLOAT,
     param1_uom_auth_name TEXT,
-    param1_uom_code TEXT,
+    param1_uom_code INTEGER_OR_TEXT,
 
     param2_auth_name TEXT,
-    param2_code TEXT,
+    param2_code INTEGER_OR_TEXT,
     param2_name TEXT,
     param2_value FLOAT,
     param2_uom_auth_name TEXT,
-    param2_uom_code TEXT,
+    param2_uom_code INTEGER_OR_TEXT,
 
     param3_auth_name TEXT,
-    param3_code TEXT,
+    param3_code INTEGER_OR_TEXT,
     param3_name TEXT,
     param3_value FLOAT,
     param3_uom_auth_name TEXT,
-    param3_uom_code TEXT,
+    param3_uom_code INTEGER_OR_TEXT,
 
     param4_auth_name TEXT,
-    param4_code TEXT,
+    param4_code INTEGER_OR_TEXT,
     param4_name TEXT,
     param4_value FLOAT,
     param4_uom_auth_name TEXT,
-    param4_uom_code TEXT,
+    param4_uom_code INTEGER_OR_TEXT,
 
     param5_auth_name TEXT,
-    param5_code TEXT,
+    param5_code INTEGER_OR_TEXT,
     param5_name TEXT,
     param5_value FLOAT,
     param5_uom_auth_name TEXT,
-    param5_uom_code TEXT,
+    param5_uom_code INTEGER_OR_TEXT,
 
     param6_auth_name TEXT,
-    param6_code TEXT,
+    param6_code INTEGER_OR_TEXT,
     param6_name TEXT,
     param6_value FLOAT,
     param6_uom_auth_name TEXT,
-    param6_uom_code TEXT,
+    param6_uom_code INTEGER_OR_TEXT,
 
     param7_auth_name TEXT,
-    param7_code TEXT,
+    param7_code INTEGER_OR_TEXT,
     param7_name TEXT,
     param7_value FLOAT,
     param7_uom_auth_name TEXT,
-    param7_uom_code TEXT,
+    param7_uom_code INTEGER_OR_TEXT,
 
     interpolation_crs_auth_name TEXT,
-    interpolation_crs_code TEXT,
+    interpolation_crs_code INTEGER_OR_TEXT,
 
     operation_version TEXT, -- normally mandatory in OGC Topic 2 but optional here
 
@@ -1234,15 +1245,15 @@ END;
 -- enforce that source_crs_code == step1.source_crs_code etc
 CREATE TABLE concatenated_operation(
     auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
-    code TEXT NOT NULL CHECK (length(code) >= 1),
+    code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1),
     name TEXT NOT NULL CHECK (length(name) >= 2),
 
     description TEXT,
 
     source_crs_auth_name TEXT NOT NULL,
-    source_crs_code TEXT NOT NULL,
+    source_crs_code INTEGER_OR_TEXT NOT NULL,
     target_crs_auth_name TEXT NOT NULL,
-    target_crs_code TEXT NOT NULL,
+    target_crs_code INTEGER_OR_TEXT NOT NULL,
 
     accuracy FLOAT CHECK (accuracy >= 0),
 
@@ -1277,10 +1288,10 @@ END;
 
 CREATE TABLE concatenated_operation_step(
     operation_auth_name TEXT NOT NULL CHECK (length(operation_auth_name) >= 1),
-    operation_code TEXT NOT NULL CHECK (length(operation_code) >= 1),
+    operation_code INTEGER_OR_TEXT NOT NULL CHECK (length(operation_code) >= 1),
     step_number INTEGER NOT NULL CHECK (step_number >= 1),
     step_auth_name TEXT NOT NULL CHECK (length(step_auth_name) >= 1),
-    step_code TEXT NOT NULL CHECK (length(step_code) >= 1),
+    step_code INTEGER_OR_TEXT NOT NULL CHECK (length(step_code) >= 1),
 
     CONSTRAINT pk_concatenated_operation_step PRIMARY KEY (operation_auth_name, operation_code, step_number)
     --CONSTRAINT fk_concatenated_operation_step_to_operation FOREIGN KEY (step_auth_name, step_code) REFERENCES coordinate_operation(auth_name, code)
@@ -1302,7 +1313,7 @@ END;
 CREATE TABLE geoid_model(
     name TEXT NOT NULL,
     operation_auth_name TEXT NOT NULL,
-    operation_code TEXT NOT NULL,
+    operation_code INTEGER_OR_TEXT NOT NULL,
     CONSTRAINT pk_geoid_model PRIMARY KEY (name, operation_auth_name, operation_code)
     -- CONSTRAINT fk_geoid_model_operation FOREIGN KEY (operation_auth_name, operation_code) REFERENCES coordinate_operation(auth_name, code)
 );
@@ -1322,7 +1333,7 @@ CREATE TABLE alias_name(
         'projected_crs', 'vertical_crs', 'compound_crs', 'conversion', 'grid_transformation',
         'helmert_transformation', 'other_transformation', 'concatenated_operation')),
     auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
-    code TEXT NOT NULL CHECK (length(code) >= 1),
+    code INTEGER_OR_TEXT NOT NULL CHECK (length(code) >= 1),
     alt_name TEXT NOT NULL CHECK (length(alt_name) >= 2),
     source TEXT
 );
@@ -1346,14 +1357,14 @@ CREATE TABLE supersession(
         'projected_crs', 'vertical_crs', 'compound_crs', 'conversion', 'grid_transformation',
         'helmert_transformation', 'other_transformation', 'concatenated_operation')),
     superseded_auth_name TEXT NOT NULL,
-    superseded_code TEXT NOT NULL,
+    superseded_code INTEGER_OR_TEXT NOT NULL,
     replacement_table_name TEXT NOT NULL CHECK (replacement_table_name IN (
         'unit_of_measure', 'celestial_body', 'ellipsoid', 
         'extent', 'prime_meridian', 'geodetic_datum', 'vertical_datum', 'geodetic_crs',
         'projected_crs', 'vertical_crs', 'compound_crs', 'conversion', 'grid_transformation',
         'helmert_transformation', 'other_transformation', 'concatenated_operation')),
     replacement_auth_name TEXT NOT NULL,
-    replacement_code TEXT NOT NULL,
+    replacement_code INTEGER_OR_TEXT NOT NULL,
     source TEXT,
     same_source_target_crs BOOLEAN NOT NULL CHECK (same_source_target_crs IN (0, 1)) -- for transformations, whether the (source_crs, target_crs) of the replacement transfrm is the same as the superseded one
 );
@@ -1378,9 +1389,9 @@ CREATE TABLE deprecation(
         'projected_crs', 'vertical_crs', 'compound_crs', 'conversion', 'grid_transformation',
         'helmert_transformation', 'other_transformation', 'concatenated_operation')),
     deprecated_auth_name TEXT NOT NULL,
-    deprecated_code TEXT NOT NULL,
+    deprecated_code INTEGER_OR_TEXT NOT NULL,
     replacement_auth_name TEXT NOT NULL,
-    replacement_code TEXT NOT NULL,
+    replacement_code INTEGER_OR_TEXT NOT NULL,
     source TEXT
 );
 
@@ -1479,7 +1490,7 @@ CREATE VIEW authority_list AS
     UNION
     SELECT DISTINCT auth_name FROM scope
     UNION
-    SELECT DISTINCT auth_name FROM usage
+    SELECT DISTINCT auth_name FROM usage WHERE auth_name IS NOT NULL
     UNION
     SELECT DISTINCT auth_name FROM prime_meridian
     UNION