Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

gpkg_metadata_reference triggers fail on row level entries. #228

Closed
cclark1984 opened this issue Jul 18, 2016 · 4 comments
Closed

gpkg_metadata_reference triggers fail on row level entries. #228

cclark1984 opened this issue Jul 18, 2016 · 4 comments
Milestone

Comments

@cclark1984
Copy link
Contributor

@cclark1984 cclark1984 commented Jul 18, 2016

Both the gpkg_metadata_reference_row_id_value_insert and gpkg_metadata_reference_row_id_value_update triggers fail when adding a row with a row_id_value. When exercised the triggers acquire the table name and use that to verify that inserted rowid x in table y exists. Unfortunately, using this dynamic string substitution of the table name for the table object reference will not work in sqlite - at best, nothing happens. Typically when invoked in code, it throws an error.

@jyutzler jyutzler added this to the post-1.1 milestone Aug 2, 2016
@cclark1984
Copy link
Contributor Author

@cclark1984 cclark1984 commented Aug 2, 2016

Offending portion of the triggers in bold:

CREATE TRIGGER 'gpkg_metadata_reference_row_id_value_insert'
BEFORE INSERT ON 'gpkg_metadata_reference'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'insert on table gpkg_metadata_reference
violates constraint: row_id_value must be NULL when reference_scope
is "geopackage", "table" or "column"')
WHERE NEW.reference_scope IN ('geopackage','table','column')
AND NEW.row_id_value IS NOT NULL;
SELECT RAISE(ABORT, 'insert on table gpkg_metadata_reference
violates constraint: row_id_value must exist in specified table when
reference_scope is "row" or "row/col"')
WHERE NEW.reference_scope IN ('row','row/col')
AND NOT EXISTS (SELECT rowid
FROM (SELECT NEW.table_name AS table_name) WHERE rowid =
NEW.row_id_value);

END

CREATE TRIGGER 'gpkg_metadata_reference_row_id_value_update'
BEFORE UPDATE OF 'row_id_value' ON 'gpkg_metadata_reference'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'update on table gpkg_metadata_reference
violates constraint: row_id_value must be NULL when reference_scope
is "geopackage", "table" or "column"')
WHERE NEW.reference_scope IN ('geopackage','table','column')
AND NEW.row_id_value IS NOT NULL;
SELECT RAISE(ABORT, 'update on table gpkg_metadata_reference
violates constraint: row_id_value must exist in specified table when
reference_scope is "row" or "row/col"')
WHERE NEW.reference_scope IN ('row','row/col')
AND NOT EXISTS (SELECT rowid
FROM (SELECT NEW.table_name AS table_name) WHERE rowid =
NEW.row_id_value);

END

@cclark1984
Copy link
Contributor Author

@cclark1984 cclark1984 commented Aug 2, 2016

We had to remove the offending selects and were left with the following:

CREATE TRIGGER 'gpkg_metadata_reference_row_id_value_insert'
BEFORE INSERT ON 'gpkg_metadata_reference'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'insert on table gpkg_metadata_reference
violates constraint: row_id_value must be NULL when reference_scope
is "geopackage", "table" or "column"')
WHERE NEW.reference_scope IN ('geopackage','table','column')
AND NEW.row_id_value IS NOT NULL;
END;

CREATE TRIGGER 'gpkg_metadata_reference_row_id_value_update'
BEFORE UPDATE OF 'row_id_value' ON 'gpkg_metadata_reference'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'update on table gpkg_metadata_reference
violates constraint: row_id_value must be NULL when reference_scope
is "geopackage", "table" or "column"')
WHERE NEW.reference_scope IN ('geopackage','table','column')
AND NEW.row_id_value IS NOT NULL;
END;

@jyutzler
Copy link
Contributor

@jyutzler jyutzler commented Aug 25, 2016

The SWG has agreed in principle to accept this change, but we're waiting for review from another development team. This review is in progress.

@jyutzler
Copy link
Contributor

@jyutzler jyutzler commented Sep 13, 2016

The final word comes from @KRyden:

It is possible to implement the actions as defined - however it is definitely non-trivial.

Simplification of the spec as Compusult proposes is probably the better route. Geopackage needs to be kept as simple as possible if it's to succeed

@jyutzler jyutzler closed this Sep 13, 2016
rouault added a commit to OSGeo/gdal that referenced this issue Feb 2, 2017
… have been removed by latest revisions of the spec (see opengeospatial/geopackage#228, opengeospatial/geopackage#240)

git-svn-id: https://svn.osgeo.org/gdal/trunk@37281 f0d54148-0727-0410-94bb-9a71ac55c965
kwrobot pushed a commit to aashish24/gdal-svn that referenced this issue Feb 2, 2017
… have been removed by latest revisions of the spec (see opengeospatial/geopackage#228, opengeospatial/geopackage#240)

git-svn-id: https://svn.osgeo.org/gdal/trunk/gdal@37281 f0d54148-0727-0410-94bb-9a71ac55c965
danielbarela added a commit to ngageoint/geopackage-js that referenced this issue Sep 26, 2018
* Upgrade node-sqlite3 to better-sqlite3 (#120)
* Update all APIs to Promises and remove all callbacks
* Support for the rtree extension
* can now query a geopackage as indexed if either the rtree index is there or if the nga feature index extension is there
* Update triggers for metadata_reference (see opengeospatial/geopackage#228, opengeospatial/geopackage#240)
* crs wkt extension
* schema extension
* metadata extension
* webp extension support
* Related Tables Extension support
* csv parser
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants