Row filter for table reads (per driver and table)

Aperta
#275 0 commenti 0 reazioni 0 assegnatari Vedi su GitHub

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
4/5
Tempo stimato
3-5 giorni
Idoneità per principianti
25/100
Tipo di issue
Funzionalità
Chiarezza
Abbastanza chiara
Stato di attività
Attiva
Stack tecnologico
c, cpp, postgresql, python, sqlite
Ambito
databases

Direzione di ricerca

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.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

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.
Lingua principale
Python
Stelle
185
Fork
22
Merge medio
1h 31m
PR unite (30g)
2

Preparare l'ambiente

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di MerginMaps/geodiff

Tutte le issue di MerginMaps/geodiff

Issue simili

Altre issue su Python

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.