Database: decrease db size by using a INTEGER_OR_TEXT data type on codes

Most codes (especially *all* in EPSG) are integers. As integers are
stored as variable-length in the db, it is preferable to store them as
such when possible.

So use a special 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.

There is no need to change the DATABASE.LAYOUT version numbering as this
is completely forward and backward compatible.

1 files changed, 117 insertions, 106 deletions

diff --git a/data/sql/proj_db_table_defs.sql b/data/sql/proj_db_table_defs.sql
index d7d5a93b..08f74c34 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,7 +79,7 @@ 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)
@@ -76,18 +87,18 @@ CREATE TABLE scope(
 
 CREATE TABLE usage(
     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),
     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
 );