Open corviday opened 6 years ago
jameshiebert
commented
6 years ago Looks like a problem added by this commit. There's no PostGIS extension (or an extension system at all) in sqlite. @corviday can you experiment with whether this step in the migration could be constrained to PostgreSQL deployments (and @rod-glover could you comment on your intuition?)?
rod-glover
commented
6 years ago Yes, this step in the migration can certainly be constrained to PostgreSQL deployments. We already have some variant migration code for PosgtreSQL vs. SQLite, and the same mechanism can be applied to this.
Some notes:
The original SQLite test databases modelmeta/data/mddb-v1.sqlite and modelmeta/data/mddb-v2.sqlite contain table spatial_ref_sys, which is the main effect (for us) of using PostGIS.
v1 database, spatial_ref_sys contains data (quick eyeball inspection looks just like the data installed by PostGIS. v2 database, it contains no data. spatial_ref_sys was created and loaded from a dump of a Postgres database (i.e., pcic_meta) at some point.It would be easy to add variant code for SQLite in the initial migration to create (and populate, if desired) the table spatial_ref_sys.
rod-glover
commented
6 years ago In working on another issue, I implemented variant code for SQLite in the initial create migration and it worked. (And was easy to do.)
However, attempting to migrate to 12f290b63791, handle variant sampling geometries on a SQLite database revealed that foreign key constraints are not named automatically for SQLite (as they are for Postgres). The migration fails in SQLite when it tries to drop the constraint (with nonexistent name) data_file_variables_grid_id_fkey. The constraint exists, but it is not named.
To see this, compare the following CREATE TABLE commands extracted from the Postgres and SQLite databases respectively after migration to initial create:
Postgres:
CREATE TABLE data_file_variables
(
data_file_variable_id serial NOT NULL,
derivation_method character varying(255),
variable_cell_methods character varying(255),
netcdf_variable_name character varying(32) NOT NULL,
disabled boolean,
range_min double precision NOT NULL,
range_max double precision NOT NULL,
data_file_id integer NOT NULL,
variable_alias_id integer NOT NULL,
level_set_id integer,
grid_id integer NOT NULL,
CONSTRAINT data_file_variables_pkey PRIMARY KEY (data_file_variable_id),
CONSTRAINT data_file_variables_data_file_id_fkey FOREIGN KEY (data_file_id)
REFERENCES data_files (data_file_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT data_file_variables_grid_id_fkey FOREIGN KEY (grid_id)
REFERENCES grids (grid_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT data_file_variables_level_set_id_fkey FOREIGN KEY (level_set_id)
REFERENCES level_sets (level_set_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT data_file_variables_variable_alias_id_fkey FOREIGN KEY (variable_alias_id)
REFERENCES variable_aliases (variable_alias_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)SQLite:
CREATE TABLE data_file_variables
(
data_file_variable_id INTEGER NOT NULL,
derivation_method VARCHAR(255),
variable_cell_methods VARCHAR(255),
netcdf_variable_name VARCHAR(32) NOT NULL,
disabled BOOLEAN,
range_min FLOAT NOT NULL,
range_max FLOAT NOT NULL,
data_file_id INTEGER NOT NULL,
variable_alias_id INTEGER NOT NULL,
level_set_id INTEGER,
grid_id INTEGER NOT NULL,
PRIMARY KEY (data_file_variable_id),
CONSTRAINT data_file_variables_data_file_id_fkey FOREIGN KEY(data_file_id) REFERENCES data_files (data_file_id) ON DELETE CASCADE,
FOREIGN KEY(grid_id) REFERENCES grids (grid_id),
FOREIGN KEY(level_set_id) REFERENCES level_sets (level_set_id),
FOREIGN KEY(variable_alias_id) REFERENCES variable_aliases (variable_alias_id),
CHECK (disabled IN (0, 1))
)The solution to this is to provide explicit names in the ORM for each foreign key constraint in the database, as in this existing example (which might have arisen for just this reason, or for another ...).
Since it is easy, it would probably be a good idea to do this for all foreign keys just so this doesn't bite us again in some future migration. We should use the existing automatically generated FK constraint names in a Postgres database. This is easy enough to do with an inspection tool like pgAdmin or by inspecting/grepping a dump of, e.g., the ce_meta database if one happens to be hanging around.
Following the sqlite test database creation instructions in this repository's README:
This procedure was previously working.