Row filter for table reads (per driver and table)
Nobody has claimed this yet.
Assessment
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Newbie friendliness
- 25/100
Research direction
Start from the existing table-level filtering (--include-tables / --skip-tables) and follow how it is plumbed from the C API through the context into the drivers, then read the dumpData and createChangeset read paths in the PostgreSQL and SQLite drivers, since that is where the WHERE expression has to be appended. New surface is GEODIFF_CX_setTableFilter in the C API plus GeoDiff.set_table_filter in pygeodiff, with filters stored per driver and table. Done looks like a copy or diff between a filtered base and the live table reporting only in-filter changes, a row leaving the filter appearing as a delete, and no behaviour change when no filter is set. The Semantics to agree section is still open, so expect maintainer input before the driver work lands.
Written by the indexing model from the issue text.
Description
Summary
We would like geodiff to be able to read only the rows of a table that match a filter when it reads a live source (create a changeset, dump, copy). The filter would be set per driver and per table, as an expression in that driver's SQL.
Use case
Currently, there is Work-Packages with applies a filter to the geopackage and creates multiple poject. But imagine, if you want to have a single PG table and use different subsets of a database table in sync with a Mergin Maps project. Field teams/online web maps get an offline GeoPackage with just their rows (a team, an area, a status), and their edits flow back to the database. geodiff already does the hard parts: it creates the GeoPackage from the database (make_copy), merges changes (rebase) and writes them back with the PostgreSQL driver (apply_changeset).
What is missing is filtering by row. Today geodiff filters by table only (--include-tables, --skip-tables), so the whole table has to be read:
- A filtered GeoPackage has to be produced by copying the entire table first, or by copying the filtered rows into a scratch schema in the database and running
make_copyon that. The scratch schema needs extra permissions (CREATEon the database) and a copy of the rows on every sync cycle. - Comparing a filtered GeoPackage with the live table reports every row outside the filter as an insert, so the diff cannot be used directly.
- db-sync has the same limit: it keeps a full
basecopy of every included table.
With a row filter, make_copy reads only the matching rows, and a diff between a filtered base and the live table reports only changes inside the filter. A row that enters the filter is an insert, and a row that leaves it is a delete. That is exactly what a filtered, offline copy needs.
Proposal --- feel free skip, it might be AI slop :)
- C API:
int GEODIFF_CX_setTableFilter( GEODIFF_ContextH, const char *driverName, const char *tableName, const char *filterExpression ). An empty or NULL expression removes the filter. - pygeodiff:
GeoDiff.set_table_filter(driver, table, filter_expression). - The expression is SQL in the driver's dialect, written like a
WHEREclause on the table, for examplestatus <> 'ok' AND geom && ST_MakeEnvelope(...). It is used as it is, so it must come from a trusted source. The documentation should say so. - Filters are kept per driver and table. In cross-driver calls (
copy,create_changeset_dr) the same context serves both sides, so a PostgreSQL expression must never reach the SQLite driver. - The filter applies to reads of a live source:
dumpDataandcreateChangeset(on the base and the modified side), and so to copies and cross-driver diffs.applyChangesetis not filtered, because changes are applied by primary key. - Nothing changes when no filter is set, and the changeset format does not change.
Semantics to agree
- A row that stops matching the filter appears in a changeset as a delete. We think that should be documented behaviour.
- The SQLite driver should accept the same option, so GeoPackage-to-GeoPackage use behaves the same way. Its queries use qualified table names, so this needs a little more care than the PostgreSQL driver.
- A command-line option, for example
--filter DRIVER TABLE EXPRESSION, would let db-sync use it.
- Dominant language
- Python
- Stars
- 185
- Forks
- 22
- Avg merge
- 1h 31m
- Merged PRs (30d)
- 2
Getting set up
- No Dockerfile or Docker Compose file
- No pull request template
- Read the contributing guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from MerginMaps/geodiff
-
GEODIFF_changesCount aborts the process on a truncated changeset (uncaught GeoDiffException)Possibly taken @mohanedmoh claimed this 21 days ago. Open
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
MerginMaps/geodiff#272 · 1 comment ·
-
enhancement
Difficulty 4/5 3-5 days Newbie friendliness 46/100
MerginMaps/geodiff#274 · 1 reaction ·
-
Difficulty 4/5 3-5 days Newbie friendliness 40/100
MerginMaps/geodiff#267 ·
-
Difficulty 4/5 3-5 days Newbie friendliness 48/100
MerginMaps/geodiff#262 ·
-
bug data corruption - sync failure - crash
Difficulty 4/5 3-5 days Newbie friendliness 48/100
MerginMaps/geodiff#255 ·
All issues in MerginMaps/geodiff
Similar issues
-
Difficulty 2/5 1-3 hours Newbie friendliness 65/100
Deltares/numba_celltree#100 · 1 comment ·
-
auto-reported bug
Difficulty 2/5 1-3 hours Newbie friendliness 63/100
Maintainers usually reply within 1 day
-
soup airgap-bundle --bundle-size-cap nan, inf or a cap above ~1.67e299 GiB stops with an internal ValueError/OverflowError (exit 1) instead of a usage errorPossibly taken @Keshemir claimed this today. Openbug claimed difficulty:2
Difficulty 2/5 1-3 hours Newbie friendliness 82/100
MakazhanAlpamys/Soup#3045 · 2 comments ·
Maintainers usually reply within 1 day
-
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
dagster-io/skills#80 ·
-
bug
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
Maintainers usually reply within 1 day