package metadata import ( "context" "database/sql" "fmt" "strings" ) // Only the owned portable-core schema is certified here. Existing application // tables are NOT adopted by translating arbitrary SQLite DDL to PostgreSQL. func (r *Repository) inspectCatalog(ctx context.Context, tx *sql.Tx, report *PortabilityReport) error { if r.backend == SQLite { return r.inspectSQLiteCatalog(ctx, tx, report) } return r.inspectPostgreSQLCatalog(ctx, tx, report) } func (r *Repository) inspectSQLiteCatalog(ctx context.Context, tx *sql.Tx, report *PortabilityReport) error { steps, _ := r.migrationDefinitions() expected := map[string]string{} for _, step := range steps { for _, statement := range step.Statements { fields := strings.Fields(statement) if len(fields) > 2 && fields[0] == "CREATE" && (fields[1] == "TABLE" || fields[1] == "INDEX") { name := strings.SplitN(fields[2], "(", 2)[0] expected[name] = strings.TrimSpace(statement) } } } rows, err := tx.QueryContext(ctx, `SELECT name,type,sql FROM sqlite_master WHERE type IN ('table','index') AND sql IS NOT NULL AND name!='schema_migration_steps' AND substr(name,1,7)!='sqlite_' ORDER BY name`) if err != nil { return r.failure(err) } defer rows.Close() seen := map[string]bool{} for rows.Next() { var name, kind, ddl string if err := rows.Scan(&name, &kind, &ddl); err != nil { return r.failure(err) } if wanted, ok := expected[name]; ok { seen[name] = true if wanted != strings.TrimSpace(ddl) { report.Blockers = append(report.Blockers, "catalog mismatch: "+name) } } else if kind == "index" { report.Blockers = append(report.Blockers, "unported index: "+name) } } if err := rows.Err(); err != nil { return r.failure(err) } for name := range expected { if !seen[name] { report.Blockers = append(report.Blockers, "missing catalog object: "+name) } } return nil } func numericColumn(column string) bool { switch column { case "created_at", "created_at_ms", "updated_at_ms", "is_primary", "size_bytes", "id", "sequence", "attempts", "next_attempt_ms", "claimed_at_ms", "last_sequence": return true } return false } func (r *Repository) inspectPostgreSQLCatalog(ctx context.Context, tx *sql.Tx, report *PortabilityReport) error { var ledgerPolicy bool if err := tx.QueryRowContext(ctx, `SELECT COALESCE(bool_or(c.relrowsecurity OR c.relforcerowsecurity OR EXISTS(SELECT 1 FROM pg_policy p WHERE p.polrelid=c.oid)),false) FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace WHERE n.nspname=current_schema() AND c.relname='schema_migration_steps'`).Scan(&ledgerPolicy); err != nil { return r.failure(err) } if ledgerPolicy { report.Blockers = append(report.Blockers, "unported policy: schema_migration_steps") } defaults := map[string]string{ "paths.is_primary": "0", "revisions.filename": "''::text", "revisions.message": "''::text", "revisions.body": "''::text", "assets.filename": "''::text", "activity_outbox.actor_uuid": "''::text", "activity_outbox.target_kind": "''::text", "activity_outbox.target_uuid": "''::text", "activity_outbox.payload": "''::text", "activity_outbox.status": "'pending'::text", "activity_outbox.attempts": "0", "activity_outbox.last_error": "''::text", "activity_outbox.claimed_at_ms": "0", } constraints := map[string][]string{ "objects": {"PRIMARY KEY (uuid)"}, "object_origins": {"PRIMARY KEY (object_uuid)", "FOREIGN KEY (object_uuid) REFERENCES objects(uuid)"}, "paths": {"PRIMARY KEY (domain_uuid, path)", "FOREIGN KEY (object_uuid) REFERENCES objects(uuid)"}, "revisions": {"PRIMARY KEY (uuid)", "FOREIGN KEY (object_uuid) REFERENCES objects(uuid)"}, "assets": {"PRIMARY KEY (domain_uuid, address)", "UNIQUE (object_uuid)", "FOREIGN KEY (object_uuid) REFERENCES objects(uuid)", "CHECK ((size_bytes >= 0))"}, "asset_head_history": {"PRIMARY KEY (id)", "CHECK ((size_bytes >= 0))"}, "revision_document_refs": {"PRIMARY KEY (revision_uuid, document_hash)", "FOREIGN KEY (revision_uuid) REFERENCES revisions(uuid)"}, "activity_outbox": {"PRIMARY KEY (uuid)", "UNIQUE (domain_uuid, sequence)", "UNIQUE (domain_uuid, idempotency_key)"}, "activity_outbox_heads": {"PRIMARY KEY (domain_uuid, event_type)"}, } identityTablePresent := false for _, spec := range coreTables { var ordinary bool if err := tx.QueryRowContext(ctx, `SELECT c.relkind='r' AND c.relpersistence='p' AND NOT c.relrowsecurity AND NOT c.relforcerowsecurity AND NOT EXISTS(SELECT 1 FROM pg_policy p WHERE p.polrelid=c.oid) AND NOT EXISTS(SELECT 1 FROM pg_inherits x WHERE x.inhrelid=c.oid OR x.inhparent=c.oid) FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace WHERE n.nspname=current_schema() AND c.relname=$1`, spec.name).Scan(&ordinary); err != nil { if err == sql.ErrNoRows { report.Blockers = append(report.Blockers, "missing table: "+spec.name) continue } return r.failure(err) } if !ordinary { report.Blockers = append(report.Blockers, "unported policy or table mode: "+spec.name) } if spec.identity { identityTablePresent = true } rows, err := tx.QueryContext(ctx, `SELECT a.attname,format_type(a.atttypid,a.atttypmod),a.attnotnull,COALESCE(pg_get_expr(d.adbin,d.adrelid),''),a.attidentity,a.attgenerated,COALESCE(col.collname,'') FROM pg_attribute a JOIN pg_class c ON c.oid=a.attrelid JOIN pg_namespace n ON n.oid=c.relnamespace LEFT JOIN pg_attrdef d ON d.adrelid=a.attrelid AND d.adnum=a.attnum LEFT JOIN pg_collation col ON col.oid=a.attcollation WHERE n.nspname=current_schema() AND c.relname=$1 AND a.attnum>0 AND NOT a.attisdropped`, spec.name) if err != nil { return r.failure(err) } for rows.Next() { var name, kind, def, identity, generated, collation string var required bool if err := rows.Scan(&name, &kind, &required, &def, &identity, &generated, &collation); err != nil { rows.Close() return r.failure(err) } wantType := "text" if numericColumn(name) { wantType = "bigint" } if name == "is_primary" || name == "attempts" { wantType = "integer" } wantIdentity := "" if spec.identity && name == "id" { wantIdentity = "a" } wantCollation := "" if wantType == "text" { wantCollation = "default" } if spec.name == "paths" && name == "path" || spec.name == "assets" && name == "address" { wantCollation = "C" } if kind != wantType || !required || def != defaults[spec.name+"."+name] || identity != wantIdentity || generated != "" || collation != wantCollation { report.Blockers = append(report.Blockers, fmt.Sprintf("column contract mismatch: %s.%s", spec.name, name)) } } err = rows.Err() rows.Close() if err != nil { return r.failure(err) } rows, err = tx.QueryContext(ctx, `SELECT pg_get_constraintdef(c.oid),c.convalidated,c.condeferrable,c.condeferred FROM pg_constraint c JOIN pg_class t ON t.oid=c.conrelid JOIN pg_namespace n ON n.oid=t.relnamespace WHERE n.nspname=current_schema() AND t.relname=$1`, spec.name) if err != nil { return r.failure(err) } wanted := map[string]bool{} for _, s := range constraints[spec.name] { wanted[s] = true } actual := map[string]bool{} matches := true constraintCount := 0 for rows.Next() { constraintCount++ var def string var valid, deferable, deferred bool if err := rows.Scan(&def, &valid, &deferable, &deferred); err != nil { rows.Close() return r.failure(err) } actual[def] = true matches = matches && valid && !deferable && !deferred && wanted[def] } err = rows.Err() rows.Close() if err != nil { return r.failure(err) } if !matches || len(actual) != len(wanted) || constraintCount != len(wanted) { report.Blockers = append(report.Blockers, "constraint contract mismatch: "+spec.name) } } var schema string if err := tx.QueryRowContext(ctx, `SELECT quote_ident(current_schema())`).Scan(&schema); err != nil { return r.failure(err) } indexes := map[string]string{ "idx_paths_domain_object": "paths USING btree (domain_uuid, object_uuid)", "idx_revisions_metadata_head": "revisions USING btree (object_uuid, lang, created_at_ms DESC, uuid DESC)", "idx_asset_metadata_history": "asset_head_history USING btree (domain_uuid, address, id)", "idx_metadata_document_refs": "revision_document_refs USING btree (document_hash, revision_uuid)", "idx_metadata_outbox_pending": "activity_outbox USING btree (status, next_attempt_ms, domain_uuid, sequence)", } rows, err := tx.QueryContext(ctx, `SELECT i.relname,pg_get_indexdef(i.oid),x.indisvalid,x.indisready FROM pg_index x JOIN pg_class i ON i.oid=x.indexrelid JOIN pg_class t ON t.oid=x.indrelid JOIN pg_namespace n ON n.oid=t.relnamespace WHERE n.nspname=current_schema() AND t.relname!='schema_migration_steps' AND NOT EXISTS(SELECT 1 FROM pg_constraint con WHERE con.conindid=i.oid)`) if err != nil { return r.failure(err) } defer rows.Close() seen := map[string]bool{} for rows.Next() { var name, def string var valid, ready bool if err := rows.Scan(&name, &def, &valid, &ready); err != nil { return r.failure(err) } suffix, ok := indexes[name] if !ok { report.Blockers = append(report.Blockers, "unported index: "+name) continue } seen[name] = true if def != "CREATE INDEX "+name+" ON "+schema+"."+suffix || !valid || !ready { report.Blockers = append(report.Blockers, "index contract mismatch: "+name) } } if err := rows.Err(); err != nil { return r.failure(err) } for name := range indexes { if !seen[name] { report.Blockers = append(report.Blockers, "missing index: "+name) } } var sequenceMatches bool if identityTablePresent { err = tx.QueryRowContext(ctx, `SELECT seqstart=1 AND seqincrement=1 AND seqmin=1 AND seqmax=9223372036854775807 AND seqcache=1 AND NOT seqcycle FROM pg_sequence WHERE seqrelid=pg_get_serial_sequence('asset_head_history','id')::regclass`).Scan(&sequenceMatches) } if err != nil && err != sql.ErrNoRows { return r.failure(err) } if !sequenceMatches { report.Blockers = append(report.Blockers, "identity sequence contract mismatch: asset_head_history.id") } return nil }