Row filter for table reads (per driver and table)

Open
#275 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
25/100
Issue type
Feature
Clarity
Mostly clear
Activity status
Active
Tech stack
c, cpp, postgresql, python, sqlite
Domain
databases

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_copy on that. The scratch schema needs extra permissions (CREATE on 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 base copy 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 WHERE clause on the table, for example status <> '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: dumpData and createChangeset (on the base and the modified side), and so to copies and cross-driver diffs. applyChangeset is 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

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from MerginMaps/geodiff

All issues in MerginMaps/geodiff

Similar issues

More Python issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.