gabogg/hikcentral
Watch
1
0
Fork
You've already forked hikcentral
 0
Code Issues 113 Pull requests 8 Projects Releases Packages Wiki Activity Actions

chore(db): drop the legacy counting_cameras.zone_name column #209

New issue
Closed
opened 2026-10-02 16:11:21 +00:00 by gabogg · 1 comment
gabogg commented 2026-10-02 16:11:21 +00:00
Owner
Copy link

This was generated by AI during triage.

Blocked by: #200 Unblocked 2026-10-03: #200 closed when #168 merged (016a012). #168 left the camera INSERTs writing a literal 'General' into this column. Removing those writes is part of this issue, and it supersedes #220 item 3.

Agent Brief

Category: enhancement (cleanup)
Summary: Drop the legacy counting_cameras.zone_name column once #200 has stopped every read and write of it.

Current behavior (after #200):

  • counting_cameras.zone_name TEXT NOT NULL DEFAULT 'General' stays in the table, unused and marked legacy.
  • Cameras serve camera_group, derived from their linked Camera Group.

Desired behavior:

  • A startup migration removes the column. SQLite can't drop a column that has a default and NOT NULL portably, so rebuild the table:
    • create the new table;
    • copy every other column;
    • swap the tables;
    • recreate indexes and triggers.
  • It is idempotent: a database without the column is left untouched.
  • It runs inside one transaction. If any step fails, the original table is left intact.
  • It preserves every row and every other column value: camera_index_code, name, direction, active/excluded flags, the day's counts, last_event_time, updated_at and resource_group_code.
  • Removing the column also removes it from the schema CREATE TABLE and from any remaining code reference.

Acceptance criteria:

  • On a database created before #200 (with zone_name and data), startup removes the column and keeps every row and every other value.
  • Startup on a database that has already been migrated is a no-op.
  • Indexes, triggers and foreign keys on counting_cameras exist after the migration, and PRAGMA foreign_key_check is clean.
  • If the copy fails partway, the original table and data survive. Test this by injecting a failure.
  • No code reference to zone_name remains.
  • The local validation run (prod DB copy) starts cleanly after the migration.

Out of scope:

  • Any other schema cleanup.
  • Changes to camera discovery or to Camera Group handling.

Low priority: the column is harmless while unused. This is housekeeping, not a fix.

Refs #200, #99, PR #168.

> *This was generated by AI during triage.* ~~Blocked by: #200~~ Unblocked 2026-10-03: #200 closed when #168 merged (`016a012`). #168 left the camera INSERTs writing a literal `'General'` into this column. Removing those writes is part of this issue, and it supersedes #220 item 3. ## Agent Brief **Category:** enhancement (cleanup) **Summary:** Drop the legacy `counting_cameras.zone_name` column once #200 has stopped every read and write of it. **Current behavior (after #200):** - `counting_cameras.zone_name TEXT NOT NULL DEFAULT 'General'` stays in the table, unused and marked legacy. - Cameras serve `camera_group`, derived from their linked Camera Group. **Desired behavior:** - A startup migration removes the column. SQLite can't drop a column that has a default and `NOT NULL` portably, so rebuild the table: - create the new table; - copy every other column; - swap the tables; - recreate indexes and triggers. - It is idempotent: a database without the column is left untouched. - It runs inside one transaction. If any step fails, the original table is left intact. - It preserves every row and every other column value: `camera_index_code`, name, direction, active/excluded flags, the day's counts, `last_event_time`, `updated_at` and `resource_group_code`. - Removing the column also removes it from the schema `CREATE TABLE` and from any remaining code reference. **Acceptance criteria:** - [ ] On a database created before #200 (with `zone_name` and data), startup removes the column and keeps every row and every other value. - [ ] Startup on a database that has already been migrated is a no-op. - [ ] Indexes, triggers and foreign keys on `counting_cameras` exist after the migration, and `PRAGMA foreign_key_check` is clean. - [ ] If the copy fails partway, the original table and data survive. Test this by injecting a failure. - [ ] No code reference to `zone_name` remains. - [ ] The local validation run (prod DB copy) starts cleanly after the migration. **Out of scope:** - Any other schema cleanup. - Changes to camera discovery or to Camera Group handling. Low priority: the column is harmless while unused. This is housekeeping, not a fix. Refs #200, #99, PR #168.
gabogg commented 2026-10-03 09:17:44 +00:00
Author
Owner
Copy link

Maintainer decision (2026-10-03), from PR #234 review pass 1: the premise "SQLite can't drop a column that has a default and NOT NULL portably, so rebuild the table" is wrong. On SQLite 3.35+ (we have 3.51, and Python 3.11+ on Windows bundles a new enough version), ALTER TABLE counting_cameras DROP COLUMN zone_name keeps every row, index and trigger. The migration becomes one inline, guarded DROP COLUMN in the existing style, plus a startup check for SQLite >= 3.35. The other acceptance criteria stand.

**Maintainer decision (2026-10-03), from PR #234 review pass 1:** the premise "SQLite can't drop a column that has a default and NOT NULL portably, so rebuild the table" is wrong. On SQLite 3.35+ (we have 3.51, and Python 3.11+ on Windows bundles a new enough version), `ALTER TABLE counting_cameras DROP COLUMN zone_name` keeps every row, index and trigger. The migration becomes one inline, guarded `DROP COLUMN` in the existing style, plus a startup check for SQLite >= 3.35. The other acceptance criteria stand.
Sign in to join this conversation.
No Branch/Tag specified
Branches Tags
master
docs/test-policy-217
feat/historical-flow-publication
feat/historical-flow-curation
fix/holiday-stamping-followups
chore/schedule-p3-followups-186
fix/test-isolation
test/silent-camera-trace-199
feat/historical-flow-statistics
No results found.
Labels
Clear labels   
blocked
Waits on the issues listed in its "Blocked by:" line before implementation

  
bug
Category: something is broken; behaviour differs from the spec or docs

  
enhancement
Category: new feature or improvement, including refactors, chores and docs

  
high-priority
Address before ordinary-priority work; independent of triage readiness and blockers

  
low-priority
Address after ordinary-priority work; independent of triage readiness and blockers

  
needs-info
Waiting on reporter for more information

  
needs-triage
Maintainer needs to evaluate this issue

  
ready-for-agent
Fully specified, ready for an AFK agent

  
ready-for-human
Requires human implementation

  
referenced
Previously closed issue reopened or referenced by an open issue; visible history marker

  
research
Needs investigation (internet or local resources) before it can be triaged down; used alongside a category and a triage role

  
wontfix
Will not be actioned

No labels
blocked
bug
enhancement
high-priority
low-priority
needs-info
needs-triage
ready-for-agent
ready-for-human
referenced
research
wontfix
Milestone
Clear milestone
No items
No milestone
Projects
Clear projects
No items
No project
Assignees
Clear assignees
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference
gabogg/hikcentral#209
No description provided.