Skip to content

SCHEMA_COMPARE.md

Latest commit

 

History

History
67 lines (60 loc) · 5.18 KB

SCHEMA_COMPARE.md

File metadata and controls

67 lines (60 loc) · 5.18 KB

Schema Compare & DDL Exporter

Overview

  • Purpose: Compare database schemas and export DDL to reconcile differences.
  • Scope: Start simple with core objects; provide a toggle to include/ignore partitions.

Comparison Rules (Initial)

  • Tables: Compare by name, columns, types, nullability, default values, primary keys.
  • Columns: Compare name, data type (incl. length/precision/scale), nullability, default.
  • Primary Keys: Compare involved column list and order. Do not compare constraint names.
  • Foreign Keys: Compare referenced table, column list and order. Do not compare constraint names.
  • Unique Constraints: Compare column list and order. Ignore constraint names.
  • Indexes: Compare column list and order (and uniqueness). Ignore index names.
  • Sequences/Identity: Do not compare names; compare presence/usage and numeric type.
  • Partitions: Option to include partitioned tables vs. compare only parent definitions.

Options

  • Include partitions: When enabled, partition definitions are considered; when disabled, only parent table structure is compared.

Deliverables (This Iteration)

  • New WinForms screen: “Schema Compare & DDL Exporter”.
  • Two connection setups (Source and Target) with provider selection.
  • Checkbox: “Include partitions in comparison”.
  • Buttons: Compare, Export DDL (disabled until a diff is available).
  • Result area: Text panel to show summary of differences (placeholder for now).
  • Documentation: This file with plan, progress, and open questions.

Plan & Progress

  • Audit repository and identify UI patterns
  • Create this plan and questions document
  • Scaffold Schema Compare form (UI only)
  • Add toolbar entry in Main to open the tool
  • Wire options and stub Compare/Export actions
  • Implement initial comparison engine (tables/columns/PK)
  • Implement DDL generation (PostgreSQL + generic fallback)
  • Extend loaders to include indexes/FKs (by columns only)
  • Compare and generate DDL for indexes and FKs
  • Add UI toggles: include indexes/FKs and use guards
  • Apply IF NOT EXISTS guards where supported (PostgreSQL: CREATE TABLE/INDEX, ADD COLUMN)
  • Add copy-to-clipboard for generated DDL
  • Add simple progress indicator (status label + UI disable) during compare

Open Questions

  1. Supported engines: Which databases must be compared initially (SQL Server, PostgreSQL, MySQL, Oracle, Firebird, SQLite)? PostgreSQL only
  2. Cross-engine comparisons: Should we allow comparing different engines (e.g., Postgres → SQL Server) or same-engine only for now? Not now
  3. Connection sources: Reuse saved connections from settings, or always ad‑hoc per compare session? Reuse, may be we could create a pool of connections, instead of replicating the connection string.
  4. Schema scope: Compare a single schema vs. all non-system schemas? Need include/exclude filters? We should, select the schemas to compare.
  5. Object coverage: Are views, triggers, functions, and materialized views in scope now or later? Only tables.
  6. Defaults and computed: Should we compare default expressions and computed/generated columns? How to handle engine-specific expressions? Ignore them
  7. Collation/encoding: Compare collations/charsets? If so, per column and/or database-level? Not now
  8. Identity/sequence: How to normalize identity/serial vs. explicit sequences for cross-engine comparison? Check of if it has sequnce or indentity on both sides, the rest Ignore.
  9. Partitions: For which engines to support partition awareness first (e.g., Postgres, SQL Server)? How to treat subpartitioning? Handle main table only, will we improve it in the future.
  10. DDL target: Generate DDL for the target engine only, or produce a neutral diff plus per-engine renderers? only Same engine now.
  11. DDL content: Should scripts include CREATE/ALTER/DROP for all deltas, and ordering to satisfy dependencies (PK → FK)? I don't know, mark as future roadmap.
  12. Safety: Include IF EXISTS/IF NOT EXISTS guards? Wrap in transaction? Optionally output ROLLBACK-able scripts? If exists wher possible, no transactions now.
  13. Output format: Show script in UI only, copy to clipboard, and/or Save As to file? Preferred default path? Show in up, so we can copy and paste.
  14. Naming/casing: Case sensitivity and quoting rules per engine—apply when comparing or when rendering? Make it parametrized.
  15. Performance: Expected schema size; need pagination or background worker with progress for large catalogs? not now.
  16. Localization: UI language preferences (English/PT-BR)? Use existing resources or keep English-only initially? English only.
  17. Exclusions: Any object name patterns or schemas to always ignore (e.g., system schemas)? ignore system schemas
  18. Tests: Preferred engines and sample databases available for validation? Tests will be run manually.

Notes & Next Steps

  • Implemented comparison using DbCon + DbSchemaLoader to normalize tables/columns/PK.
  • DDL generation supports PostgreSQL specifically, with a generic fallback for other engines.
  • Defaults applied from answers: same-engine only; tables-only; ignore defaults/collation; handle main table only for partitions.
  • Next: add loaders and compare logic for indexes/unique/FKs (column lists only), and add guarded DDL where possible.