Function extractSchema

pkg/cli/database/schema/main.go:128–163

extractSchema extracts the complete schema by querying sqlite_master

(db *database.DB)

// extractSchema extracts the complete schema by querying sqlite_master
func extractSchema(db *database.DB) (string, error) {
	// Query sqlite_master for all schema objects, excluding FTS shadow tables
	// FTS shadow tables are internal tables automatically created by FTS virtual tables
	rows, err := db.Conn.Query(`SELECT sql FROM sqlite_master
		WHERE sql IS NOT NULL
		AND name NOT LIKE 'sqlite_%'
		AND (type != 'table'
		OR (type = 'table' AND name NOT IN (
			SELECT m1.name FROM sqlite_master m1
			JOIN sqlite_master m2 ON m1.name LIKE m2.name || '_%'
			WHERE m2.type = 'table' AND m2.sql LIKE '%VIRTUAL TABLE%'
		)))`)
	if err != nil {
		return "", fmt.Errorf("querying sqlite_master: %w", err)
	}
	defer rows.Close()

	var schemas []string
	for rows.Next() {
		var sql string
		if err := rows.Scan(&sql); err != nil {
			return "", fmt.Errorf("scanning row: %w", err)
		}
		schemas = append(schemas, sql)
	}

	if err := rows.Err(); err != nil {
		return "", fmt.Errorf("iterating rows: %w", err)
	}

	// Add autogenerated header comment
	header := `-- This is the final state of the CLI database after all migrations.
-- Auto-generated by generate-schema.go. Do not edit manually.
`
	return header + strings.Join(schemas, ";\n") + ";\n", nil
}