quietlight/skraak_mcp - Change J62FGJ3BGFTUWEOUON4ATYNDFBEUIR6FCIOZSHOVHAA7KGFYHW6AC

first iteration of export feature

Created by quietlight on February 18, 2026

J62FGJ3BGFTUWEOUON4ATYNDFBEUIR6FCIOZSHOVHAA7KGFYHW6AC

Dependencies

In channels

main

Change contents

File addition: export.go (----------)

[3.22715]

package tools
import (
	"context"
	"database/sql"
	"fmt"
	"os"
	"path/filepath"
	"sort"
	"strings"
	"skraak/db"
)
// ExportDatasetInput defines the input parameters for the export dataset tool
type ExportDatasetInput struct {
	DatasetID string `json:"dataset_id" jsonschema:"required,Dataset ID to export"`
	Output    string `json:"output" jsonschema:"required,Output database path"`
	DryRun    bool   `json:"dry_run" jsonschema:"Show what would be exported without creating file"`
	Force     bool   `json:"force" jsonschema:"Overwrite existing output file"`
}
// ExportDatasetOutput defines the output structure
type ExportDatasetOutput struct {
	DatasetID   string            `json:"dataset_id"`
	DatasetName string            `json:"dataset_name"`
	OutputPath  string            `json:"output_path"`
	RowCounts   map[string]int64  `json:"row_counts"`
	FileSizeMB  float64           `json:"file_size_mb,omitempty"`
	DryRun      bool              `json:"dry_run"`
	Message     string            `json:"message"`
}
// TableRelationship defines how a table relates to a dataset
type TableRelationship struct {
	Table       string // table name
	Relation    string // "owned" | "owned-via" | "referenced" | "derived"
	FilterCol   string // column to filter on
	ViaTable    string // for referenced: junction table
	ViaColumn   string // for referenced: column in junction table
	SourceTable string // for derived: source table
}
// Dataset tables manifest - defines how each table relates to a dataset
var datasetTables = []TableRelationship{
	// Owned directly
	{Table: "dataset", Relation: "owned", FilterCol: "id"},
	{Table: "location", Relation: "owned", FilterCol: "dataset_id"},
	{Table: "cluster", Relation: "owned", FilterCol: "dataset_id"},
	{Table: "selection", Relation: "owned", FilterCol: "dataset_id"},
	{Table: "file_dataset", Relation: "owned", FilterCol: "dataset_id"},
	{Table: "species_dataset", Relation: "owned", FilterCol: "dataset_id"},
	// Owned via FK chain
	{Table: "file", Relation: "owned-via", FilterCol: "cluster_id", ViaTable: "cluster"},
	{Table: "moth_metadata", Relation: "owned-via", FilterCol: "file_id", ViaTable: "file"},
	{Table: "file_metadata", Relation: "owned-via", FilterCol: "file_id", ViaTable: "file"},
	{Table: "selection_metadata", Relation: "owned-via", FilterCol: "selection_id", ViaTable: "selection"},
	{Table: "label", Relation: "owned-via", FilterCol: "selection_id", ViaTable: "selection"},
	{Table: "label_subtype", Relation: "owned-via", FilterCol: "label_id", ViaTable: "label"},
	// Referenced (subset extraction)
	{Table: "cyclic_recording_pattern", Relation: "referenced", FilterCol: "id", ViaTable: "cluster", ViaColumn: "cyclic_recording_pattern_id"},
	{Table: "filter", Relation: "referenced", FilterCol: "id", ViaTable: "label", ViaColumn: "filter_id"},
	{Table: "species", Relation: "referenced", FilterCol: "id", ViaTable: "species_dataset", ViaColumn: "species_id"},
	{Table: "call_type", Relation: "referenced", FilterCol: "species_id", ViaTable: "species", ViaColumn: "id"},
	{Table: "ebird_taxonomy", Relation: "referenced", FilterCol: "species_code", ViaTable: "species", ViaColumn: "ebird_code"},
	// Copied as-is (no filtering)
	{Table: "ebird_taxonomy_v2024", Relation: "copy"},
}
// ExportDataset exports a single dataset with all related data to a new database
func ExportDataset(
	ctx context.Context,
	input ExportDatasetInput,
) (ExportDatasetOutput, error) {
	var output ExportDatasetOutput
	output.DatasetID = input.DatasetID
	output.OutputPath = input.Output
	output.DryRun = input.DryRun
	output.RowCounts = make(map[string]int64)
	// Open source database (read-only for safety)
	sourceDB, err := db.OpenReadOnlyDB(dbPath)
	if err != nil {
		return output, fmt.Errorf("failed to open source database: %w", err)
	}
	// Verify dataset exists and get name
	var datasetName string
	err = sourceDB.QueryRowContext(ctx,
		"SELECT name FROM dataset WHERE id = ? AND active = true",
		input.DatasetID,
	).Scan(&datasetName)
	if err != nil {
		sourceDB.Close()
		return output, fmt.Errorf("dataset not found: %s", input.DatasetID)
	}
	output.DatasetName = datasetName
	// Check if output file exists
	if !input.DryRun {
		if _, err := os.Stat(input.Output); err == nil && !input.Force {
			sourceDB.Close()
			return output, fmt.Errorf("output file exists: %s (use --force to overwrite)", input.Output)
		}
	}
	// Get FK order for tables
	fkOrder, err := db.GetFKOrder(sourceDB)
	if err != nil {
		sourceDB.Close()
		return output, fmt.Errorf("failed to compute table order: %w", err)
	}
	// Sort our manifest by FK order
	orderedTables := orderByFKDependency(datasetTables, fkOrder)
	// Calculate row counts for each table
	for _, tr := range orderedTables {
		count, err := countTableRows(ctx, sourceDB, tr, input.DatasetID)
		if err != nil {
			sourceDB.Close()
			return output, fmt.Errorf("failed to count rows in %s: %w", tr.Table, err)
		}
		if count > 0 {
			output.RowCounts[tr.Table] = count
		}
	}
	// If dry-run, return now
	if input.DryRun {
		sourceDB.Close()
		output.Message = fmt.Sprintf("Would export dataset '%s' (%s)", datasetName, input.DatasetID)
		return output, nil
	}
	// Close source DB before creating output (DuckDB can't attach same file twice)
	sourceDB.Close()
	// Create output directory if needed
	outputDir := filepath.Dir(input.Output)
	if outputDir != "" && outputDir != "." {
		if err := os.MkdirAll(outputDir, 0755); err != nil {
			return output, fmt.Errorf("failed to create output directory: %w", err)
		}
	}
	// Create output database
	outputDB, err := createOutputDatabase(input.Output)
	if err != nil {
		return output, fmt.Errorf("failed to create output database: %w", err)
	}
	defer outputDB.Close()
	// Attach source database
	_, err = outputDB.ExecContext(ctx, fmt.Sprintf("ATTACH '%s' AS source", dbPath))
	if err != nil {
		return output, fmt.Errorf("failed to attach source database: %w", err)
	}
	// Copy data in FK order
	for _, tr := range orderedTables {
		switch tr.Relation {
		case "copy":
			// Copy entire table as-is
			err = copyTableAsIs(ctx, outputDB, tr.Table)
		case "derived":
			// Derived tables are populated from already-copied data
			err = populateDerivedTable(ctx, outputDB, tr)
		default:
			err = copyTableData(ctx, outputDB, tr, input.DatasetID)
		}
		if err != nil {
			return output, fmt.Errorf("failed to copy %s: %w", tr.Table, err)
		}
	}
	// Detach source
	_, err = outputDB.ExecContext(ctx, "DETACH source")
	if err != nil {
		return output, fmt.Errorf("failed to detach source database: %w", err)
	}
	// Close output DB before getting file size
	outputDB.Close()
	outputDB = nil
	// Get file size
	if info, err := os.Stat(input.Output); err == nil {
		output.FileSizeMB = float64(info.Size()) / 1024 / 1024
	}
	// Create empty event log file
	eventLogPath := input.Output + ".events.jsonl"
	eventFile, err := os.Create(eventLogPath)
	if err != nil {
		return output, fmt.Errorf("failed to create event log file: %w", err)
	}
	eventFile.Close()
	output.Message = fmt.Sprintf("Successfully exported dataset '%s' (%s) to %s",
		datasetName, input.DatasetID, input.Output)
	return output, nil
}
// createOutputDatabase creates a new database with the schema
func createOutputDatabase(outputPath string) (*sql.DB, error) {
	// Remove existing file if any
	os.Remove(outputPath)
	// Open new database connection
	connStr := outputPath + "?access_mode=read_write"
	database, err := sql.Open("duckdb", connStr)
	if err != nil {
		return nil, fmt.Errorf("failed to create output database: %w", err)
	}
	// Read and execute schema
	schemaSQL, err := db.ReadSchemaSQL()
	if err != nil {
		database.Close()
		return nil, fmt.Errorf("failed to read schema: %w", err)
	}
	statements := db.ExtractDDLStatements(schemaSQL)
	for _, stmt := range statements {
		// For CREATE TABLE ... AS SELECT, create table structure manually
		if stmt.Type == "CREATE_TABLE_AS" {
			createStmt := buildDerivedTableCreate(stmt.TableName)
			if createStmt != "" {
				if _, err := database.Exec(createStmt); err != nil {
					database.Close()
					return nil, fmt.Errorf("failed to create table %s: %w", stmt.TableName, err)
				}
			}
		} else {
			if _, err := database.Exec(stmt.SQL); err != nil {
				// Ignore "already exists" errors for types
				if !strings.Contains(err.Error(), "already exists") {
					database.Close()
					return nil, fmt.Errorf("failed to execute DDL for %s: %w", stmt.TableName, err)
				}
			}
		}
	}
	return database, nil
}
// buildDerivedTableCreate builds a CREATE TABLE statement for tables defined as CREATE TABLE AS SELECT
func buildDerivedTableCreate(tableName string) string {
	switch tableName {
	case "ebird_taxonomy_v2024":
		return `CREATE TABLE ebird_taxonomy_v2024 (
			id VARCHAR(12),
			species_code VARCHAR(15),
			primary_com_name VARCHAR(100),
			sci_name VARCHAR(100),
			bird_order VARCHAR(30),
			family VARCHAR(100)
		)`
	default:
		return ""
	}
}
// copyTableAsIs copies an entire table without filtering
func copyTableAsIs(ctx context.Context, outputDB *sql.DB, table string) error {
	query := fmt.Sprintf("INSERT INTO %s SELECT * FROM source.%s", table, table)
	_, err := outputDB.ExecContext(ctx, query)
	return err
}
// copyTableData copies data from source to output database
func copyTableData(ctx context.Context, outputDB *sql.DB, tr TableRelationship, datasetID string) error {
	var query string
	switch tr.Relation {
	case "owned":
		// Direct filter on dataset_id (or id for dataset table)
		if tr.Table == "dataset" {
			query = fmt.Sprintf("INSERT INTO %s SELECT * FROM source.%s WHERE id = ?", tr.Table, tr.Table)
		} else {
			query = fmt.Sprintf("INSERT INTO %s SELECT * FROM source.%s WHERE dataset_id = ?", tr.Table, tr.Table)
		}
	case "owned-via":
		// Filter via FK chain
		query = buildOwnedViaQuery(tr, datasetID)
	case "referenced":
		// Subset via junction table
		query = buildReferencedQuery(tr, datasetID)
	default:
		return fmt.Errorf("unknown relation type: %s", tr.Relation)
	}
	_, err := outputDB.ExecContext(ctx, query, datasetID)
	return err
}
// buildOwnedViaQuery builds a query for owned-via tables
func buildOwnedViaQuery(tr TableRelationship, datasetID string) string {
	switch tr.ViaTable {
	case "cluster":
		return fmt.Sprintf(`INSERT INTO %s SELECT * FROM source.%s 
			WHERE %s IN (SELECT id FROM source.cluster WHERE dataset_id = ?)`,
			tr.Table, tr.Table, tr.FilterCol)
	case "file":
		return fmt.Sprintf(`INSERT INTO %s SELECT * FROM source.%s 
			WHERE %s IN (SELECT id FROM source.file WHERE cluster_id IN 
				(SELECT id FROM source.cluster WHERE dataset_id = ?))`,
			tr.Table, tr.Table, tr.FilterCol)
	case "selection":
		return fmt.Sprintf(`INSERT INTO %s SELECT * FROM source.%s 
			WHERE %s IN (SELECT id FROM source.selection WHERE dataset_id = ?)`,
			tr.Table, tr.Table, tr.FilterCol)
	case "label":
		return fmt.Sprintf(`INSERT INTO %s SELECT * FROM source.%s 
			WHERE %s IN (SELECT id FROM source.label WHERE selection_id IN 
				(SELECT id FROM source.selection WHERE dataset_id = ?))`,
			tr.Table, tr.Table, tr.FilterCol)
	default:
		// Generic fallback
		return fmt.Sprintf(`INSERT INTO %s SELECT * FROM source.%s WHERE %s IN 
			(SELECT id FROM source.%s WHERE dataset_id = ?)`,
			tr.Table, tr.Table, tr.FilterCol, tr.ViaTable)
	}
}
// buildReferencedQuery builds a query for referenced tables
func buildReferencedQuery(tr TableRelationship, datasetID string) string {
	switch tr.Table {
	case "ebird_taxonomy":
		// Join via species.ebird_code and filter by species_dataset
		return `INSERT INTO ebird_taxonomy SELECT DISTINCT t.* 
			FROM source.ebird_taxonomy t
			INNER JOIN source.species s ON t.species_code = s.ebird_code AND t.taxonomy_version = s.taxonomy_version
			WHERE s.id IN (SELECT species_id FROM source.species_dataset WHERE dataset_id = ?)`
	case "filter":
		// Filter via label -> selection -> dataset_id
		return `INSERT INTO filter SELECT DISTINCT f.* 
			FROM source.filter f
			WHERE f.id IN (
				SELECT l.filter_id FROM source.label l
				INNER JOIN source.selection s ON l.selection_id = s.id
				WHERE s.dataset_id = ?
			)`
	case "call_type":
		// Call types for species in this dataset
		return fmt.Sprintf(`INSERT INTO %s SELECT * FROM source.%s 
			WHERE %s IN (SELECT id FROM source.species WHERE id IN 
				(SELECT species_id FROM source.species_dataset WHERE dataset_id = ?))`,
			tr.Table, tr.Table, tr.FilterCol)
	case "cyclic_recording_pattern":
		// Patterns via cluster.dataset_id
		return `INSERT INTO cyclic_recording_pattern SELECT DISTINCT p.* 
			FROM source.cyclic_recording_pattern p
			WHERE p.id IN (
				SELECT c.cyclic_recording_pattern_id FROM source.cluster c
				WHERE c.dataset_id = ? AND c.cyclic_recording_pattern_id IS NOT NULL
			)`
	default:
		// Generic: filter via junction table
		return fmt.Sprintf(`INSERT INTO %s SELECT * FROM source.%s 
			WHERE %s IN (SELECT %s FROM source.%s WHERE dataset_id = ?)`,
			tr.Table, tr.Table, tr.FilterCol, tr.ViaColumn, tr.ViaTable)
	}
}
// populateDerivedTable populates a derived table from already-copied data
func populateDerivedTable(ctx context.Context, outputDB *sql.DB, tr TableRelationship) error {
	switch tr.Table {
	case "ebird_taxonomy_v2024":
		// Insert from already-copied ebird_taxonomy
		_, err := outputDB.ExecContext(ctx, `
			INSERT INTO ebird_taxonomy_v2024
			SELECT id, species_code, primary_com_name, sci_name, bird_order, family
			FROM ebird_taxonomy
			WHERE taxonomy_version = '2024'
		`)
		return err
	default:
		return fmt.Errorf("unknown derived table: %s", tr.Table)
	}
}
// countTableRows counts rows for a table relationship
func countTableRows(ctx context.Context, db *sql.DB, tr TableRelationship, datasetID string) (int64, error) {
	var query string
	switch tr.Relation {
	case "copy":
		// Count all rows in table
		query = "SELECT COUNT(*) FROM " + tr.Table
	case "owned":
		if tr.Table == "dataset" {
			query = "SELECT COUNT(*) FROM " + tr.Table + " WHERE id = ?"
		} else {
			query = "SELECT COUNT(*) FROM " + tr.Table + " WHERE dataset_id = ?"
		}
	case "owned-via":
		query = buildCountOwnedViaQuery(tr)
	case "referenced":
		query = buildCountReferencedQuery(tr)
	case "derived":
		// Count what would be derived based on already-referenced data
		if tr.Table == "ebird_taxonomy_v2024" {
			query = `SELECT COUNT(*) FROM ebird_taxonomy WHERE taxonomy_version = '2024' 
				AND species_code IN (SELECT ebird_code FROM species WHERE id IN 
					(SELECT species_id FROM species_dataset WHERE dataset_id = ?))`
		} else {
			return 0, nil
		}
	default:
		return 0, nil
	}
	var count int64
	err := db.QueryRowContext(ctx, query, datasetID).Scan(&count)
	return count, err
}
// buildCountOwnedViaQuery builds a count query for owned-via tables
func buildCountOwnedViaQuery(tr TableRelationship) string {
	switch tr.ViaTable {
	case "cluster":
		return fmt.Sprintf(`SELECT COUNT(*) FROM %s WHERE %s IN 
			(SELECT id FROM cluster WHERE dataset_id = ?)`, tr.Table, tr.FilterCol)
	case "file":
		return fmt.Sprintf(`SELECT COUNT(*) FROM %s WHERE %s IN 
			(SELECT id FROM file WHERE cluster_id IN 
				(SELECT id FROM cluster WHERE dataset_id = ?))`, tr.Table, tr.FilterCol)
	case "selection":
		return fmt.Sprintf(`SELECT COUNT(*) FROM %s WHERE %s IN 
			(SELECT id FROM selection WHERE dataset_id = ?)`, tr.Table, tr.FilterCol)
	case "label":
		return fmt.Sprintf(`SELECT COUNT(*) FROM %s WHERE %s IN 
			(SELECT id FROM label WHERE selection_id IN 
				(SELECT id FROM selection WHERE dataset_id = ?))`, tr.Table, tr.FilterCol)
	default:
		return fmt.Sprintf(`SELECT COUNT(*) FROM %s WHERE %s IN 
			(SELECT id FROM %s WHERE dataset_id = ?)`, tr.Table, tr.FilterCol, tr.ViaTable)
	}
}
// buildCountReferencedQuery builds a count query for referenced tables
func buildCountReferencedQuery(tr TableRelationship) string {
	switch tr.Table {
	case "ebird_taxonomy":
		return `SELECT COUNT(DISTINCT ebird_taxonomy.id) FROM ebird_taxonomy 
			INNER JOIN species ON ebird_taxonomy.species_code = species.ebird_code 
				AND ebird_taxonomy.taxonomy_version = species.taxonomy_version
			WHERE species.id IN (SELECT species_id FROM species_dataset WHERE dataset_id = ?)`
	case "filter":
		// Filter via label -> selection -> dataset_id
		return `SELECT COUNT(DISTINCT filter.id) FROM filter 
			INNER JOIN label ON filter.id = label.filter_id
			INNER JOIN selection ON label.selection_id = selection.id
			WHERE selection.dataset_id = ?`
	case "call_type":
		return fmt.Sprintf(`SELECT COUNT(*) FROM %s WHERE %s IN 
			(SELECT id FROM species WHERE id IN 
				(SELECT species_id FROM species_dataset WHERE dataset_id = ?))`, tr.Table, tr.FilterCol)
	case "cyclic_recording_pattern":
		return `SELECT COUNT(DISTINCT cyclic_recording_pattern.id) FROM cyclic_recording_pattern 
			INNER JOIN cluster ON cyclic_recording_pattern.id = cluster.cyclic_recording_pattern_id
			WHERE cluster.dataset_id = ?`
	default:
		return fmt.Sprintf(`SELECT COUNT(*) FROM %s WHERE %s IN 
			(SELECT %s FROM %s WHERE dataset_id = ?)`, tr.Table, tr.FilterCol, tr.ViaColumn, tr.ViaTable)
	}
}
// orderByFKDependency sorts tables by FK dependency order
func orderByFKDependency(tables []TableRelationship, fkOrder []string) []TableRelationship {
	// Create a map for quick order lookup
	orderMap := make(map[string]int)
	for i, table := range fkOrder {
		orderMap[table] = i
	}
	// Sort by FK order, with copy tables last
	sorted := make([]TableRelationship, len(tables))
	copy(sorted, tables)
	sort.Slice(sorted, func(i, j int) bool {
		ti, tj := sorted[i], sorted[j]
		// Copy tables always come last
		if ti.Relation == "copy" && tj.Relation != "copy" {
			return false
		}
		if tj.Relation == "copy" && ti.Relation != "copy" {
			return true
		}
		if ti.Relation == "copy" && tj.Relation == "copy" {
			return ti.Table < tj.Table
		}
		// Derived tables come after their source tables
		if ti.Relation == "derived" && tj.Relation != "derived" {
			return false
		}
		if tj.Relation == "derived" && ti.Relation != "derived" {
			return true
		}
		if ti.Relation == "derived" && tj.Relation == "derived" {
			// Order derived tables by their source table
			si, _ := orderMap[ti.SourceTable]
			sj, _ := orderMap[tj.SourceTable]
			return si < sj
		}
		// Use FK order for non-derived tables
		oi, _ := orderMap[ti.Table]
		oj, _ := orderMap[tj.Table]
		return oi < oj
	})
	return sorted
}

File addition: test_export.sh (---x------)

[4.1]

#!/bin/bash
# Test export dataset functionality
# Usage: ./test_export.sh [db_path]
set -e
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
PROJECT_DIR="$(dirname "$SCRIPT_DIR")"
SKRAAK="$PROJECT_DIR/skraak"
DB_PATH="${1:-$PROJECT_DIR/db/test.duckdb}"
EXPORT_DB="/tmp/skraak_export_test_$$.duckdb"
echo "=== Testing Export Dataset ==="
echo "Database: $DB_PATH"
echo ""
# Clean up any existing export
rm -f "$EXPORT_DB" "$EXPORT_DB.events.jsonl"
# Get a dataset ID to export
echo "Test 1: Get dataset ID..."
DATASET_ID=$("$SKRAAK" sql --db "$DB_PATH" "SELECT id FROM dataset WHERE active = true LIMIT 1" | jq -r '.rows[0].id')
if [ -z "$DATASET_ID" ] || [ "$DATASET_ID" = "null" ]; then
    echo "ERROR: No active dataset found"
    exit 1
fi
echo "  Dataset ID: $DATASET_ID"
# Test dry-run
echo ""
echo "Test 2: Dry-run export..."
OUTPUT=$("$SKRAAK" export dataset --db "$DB_PATH" --id "$DATASET_ID" --output "$EXPORT_DB" --dry-run)
echo "$OUTPUT" | jq -r '.message'
DRY_RUN=$(echo "$OUTPUT" | jq -r '.dry_run')
if [ "$DRY_RUN" != "true" ]; then
    echo "ERROR: dry_run should be true"
    exit 1
fi
echo "  ✓ Dry-run works"
# Verify no file created
if [ -f "$EXPORT_DB" ]; then
    echo "ERROR: Export file should not exist after dry-run"
    exit 1
fi
echo "  ✓ No file created in dry-run mode"
# Test actual export
echo ""
echo "Test 3: Export dataset..."
OUTPUT=$("$SKRAAK" export dataset --db "$DB_PATH" --id "$DATASET_ID" --output "$EXPORT_DB" --force)
echo "$OUTPUT" | jq -r '.message'
# Verify export file exists
if [ ! -f "$EXPORT_DB" ]; then
    echo "ERROR: Export file not created"
    exit 1
fi
echo "  ✓ Export file created"
# Verify event log file exists
if [ ! -f "$EXPORT_DB.events.jsonl" ]; then
    echo "ERROR: Event log file not created"
    exit 1
fi
echo "  ✓ Event log file created"
# Verify row counts
echo ""
echo "Test 4: Verify row counts..."
FILE_COUNT=$(echo "$OUTPUT" | jq -r '.row_counts.file')
EXPORTED_COUNT=$("$SKRAAK" sql --db "$EXPORT_DB" "SELECT COUNT(*) as count FROM file" | jq -r '.rows[0].count')
if [ "$FILE_COUNT" != "$EXPORTED_COUNT" ]; then
    echo "ERROR: File count mismatch: expected $FILE_COUNT, got $EXPORTED_COUNT"
    exit 1
fi
echo "  ✓ Row counts match ($FILE_COUNT files)"
# Verify dataset
echo ""
echo "Test 5: Verify dataset..."
DATASET_COUNT=$("$SKRAAK" sql --db "$EXPORT_DB" "SELECT COUNT(*) as count FROM dataset WHERE id = '$DATASET_ID'" | jq -r '.rows[0].count')
if [ "$DATASET_COUNT" != "1" ]; then
    echo "ERROR: Dataset not found in export"
    exit 1
fi
echo "  ✓ Dataset found in export"
# Test error handling - dataset not found
echo ""
echo "Test 6: Test error handling..."
ERROR=$("$SKRAAK" export dataset --db "$DB_PATH" --id "NOTAREALID" --output "$EXPORT_DB" 2>&1 || true)
if [[ ! "$ERROR" =~ "dataset not found" ]]; then
    echo "ERROR: Should report dataset not found"
    echo "$ERROR"
    exit 1
fi
echo "  ✓ Error handling works for missing dataset"
# Test --force overwrite
echo ""
echo "Test 7: Test --force overwrite..."
OUTPUT=$("$SKRAAK" export dataset --db "$DB_PATH" --id "$DATASET_ID" --output "$EXPORT_DB" --force 2>&1)
if [[ "$OUTPUT" =~ "error" ]]; then
    echo "ERROR: Should not error with --force"
    echo "$OUTPUT"
    exit 1
fi
echo "  ✓ --force overwrite works"
# Test error without --force
echo ""
echo "Test 8: Test error without --force..."
ERROR=$("$SKRAAK" export dataset --db "$DB_PATH" --id "$DATASET_ID" --output "$EXPORT_DB" 2>&1 || true)
if [[ ! "$ERROR" =~ "file exists" ]]; then
    echo "ERROR: Should report file exists"
    echo "$ERROR"
    exit 1
fi
echo "  ✓ Error handling works for existing file"
# Clean up
rm -f "$EXPORT_DB" "$EXPORT_DB.events.jsonl"
echo ""
echo "=== All tests passed ==="

Insertion in main.go at line 31 [3.149392]
[5.21025]
[6.21951]
```
	case "export":
		cmd.RunExport(os.Args[2:])
```

Insertion in main.go at line 59 [3.149392]

[5.21375]

[6.21997]

	fmt.Fprintf(os.Stderr, "  export     Export dataset to new database\n")

Insertion in main.go at line 67 [3.149392]

[7.36647]

[6.22071]

	fmt.Fprintf(os.Stderr, "  %s export dataset --db ./db/skraak.duckdb --id abc123 --output export.duckdb\n", os.Args[0])

File addition: schema_test.go (----------)

[3.161589]

package db
import (
	"database/sql"
	"strings"
	"testing"
	_ "github.com/duckdb/duckdb-go/v2"
)
func TestReadSchemaSQL(t *testing.T) {
	schema, err := ReadSchemaSQL()
	if err != nil {
		t.Fatalf("ReadSchemaSQL() error = %v", err)
	}
	// Verify schema contains expected elements
	if !strings.Contains(schema, "CREATE TABLE dataset") {
		t.Error("schema missing CREATE TABLE dataset")
	}
	if !strings.Contains(schema, "CREATE TYPE dataset_type") {
		t.Error("schema missing CREATE TYPE dataset_type")
	}
	if !strings.Contains(schema, "CREATE INDEX") {
		t.Error("schema missing CREATE INDEX")
	}
	if !strings.Contains(schema, "ebird_taxonomy_v2024") {
		t.Error("schema missing ebird_taxonomy_v2024")
	}
}
func TestExtractDDLStatements(t *testing.T) {
	schema, err := ReadSchemaSQL()
	if err != nil {
		t.Fatalf("ReadSchemaSQL() error = %v", err)
	}
	statements := ExtractDDLStatements(schema)
	if len(statements) == 0 {
		t.Fatal("ExtractDDLStatements returned no statements")
	}
	// Count statement types
	typeCounts := make(map[string]int)
	tableNames := make(map[string]bool)
	for _, stmt := range statements {
		typeCounts[stmt.Type]++
		if stmt.TableName != "" {
			tableNames[stmt.TableName] = true
		}
		t.Logf("Statement type=%s table=%s sql=%s", stmt.Type, stmt.TableName, stmt.SQL[:min(50, len(stmt.SQL))])
	}
	// Verify we have all expected types
	if typeCounts["CREATE_TYPE"] < 2 {
		t.Errorf("expected at least 2 CREATE_TYPE statements, got %d", typeCounts["CREATE_TYPE"])
	}
	if typeCounts["CREATE_TABLE"] < 10 {
		t.Errorf("expected at least 10 CREATE_TABLE statements, got %d", typeCounts["CREATE_TABLE"])
	}
	if typeCounts["CREATE_INDEX"] < 5 {
		t.Errorf("expected at least 5 CREATE_INDEX statements, got %d", typeCounts["CREATE_INDEX"])
	}
	// CREATE_TABLE_AS might be 0 if the extraction logic changes - that's OK
	// as long as we handle it correctly in the export code
	// Verify key tables are found
	expectedTables := []string{"dataset", "location", "cluster", "file", "selection", "label"}
	for _, expected := range expectedTables {
		if !tableNames[expected] {
			t.Errorf("missing table %s in extracted statements", expected)
		}
	}
}
func min(a, b int) int {
	if a < b {
		return a
	}
	return b
}
func TestExtractDDLStatement_Types(t *testing.T) {
	tests := []struct {
		name      string
		sql       string
		wantType  string
		wantTable string
	}{
		{
			name:      "CREATE TYPE",
			sql:       "CREATE TYPE dataset_type AS ENUM ('structured', 'unstructured');",
			wantType:  "CREATE_TYPE",
			wantTable: "",
		},
		{
			name:      "CREATE TABLE simple",
			sql:       "CREATE TABLE dataset (id VARCHAR(12) PRIMARY KEY);",
			wantType:  "CREATE_TABLE",
			wantTable: "dataset",
		},
		{
			name:      "CREATE TABLE with newlines",
			sql:       "CREATE TABLE location\n(\n    id VARCHAR(12) PRIMARY KEY\n);",
			wantType:  "CREATE_TABLE",
			wantTable: "location",
		},
		{
			name:      "CREATE TABLE AS SELECT",
			sql:       "CREATE TABLE ebird_taxonomy_v2024 AS SELECT id, species_code FROM ebird_taxonomy WHERE taxonomy_version = '2024';",
			wantType:  "CREATE_TABLE_AS",
			wantTable: "ebird_taxonomy_v2024",
		},
		{
			name:      "CREATE INDEX",
			sql:       "CREATE INDEX idx_file_location ON file(location_id);",
			wantType:  "CREATE_INDEX",
			wantTable: "idx_file_location",
		},
		{
			name:      "CREATE UNIQUE INDEX",
			sql:       "CREATE UNIQUE INDEX idx_species_label ON species(label);",
			wantType:  "CREATE_INDEX",
			wantTable: "idx_species_label",
		},
	}
	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			stmt := parseDDLStatement(tt.sql)
			if stmt.Type != tt.wantType {
				t.Errorf("parseDDLStatement().Type = %v, want %v", stmt.Type, tt.wantType)
			}
			if stmt.TableName != tt.wantTable {
				t.Errorf("parseDDLStatement().TableName = %v, want %v", stmt.TableName, tt.wantTable)
			}
		})
	}
}
func TestExtractTableName(t *testing.T) {
	tests := []struct {
		name string
		sql  string
		want string
	}{
		{
			name: "simple table",
			sql:  "CREATE TABLE dataset (id VARCHAR(12) PRIMARY KEY",
			want: "dataset",
		},
		{
			name: "table with space before paren",
			sql:  "CREATE TABLE location (id VARCHAR(12)",
			want: "location",
		},
		{
			name: "table with newline",
			sql:  "CREATE TABLE cluster\n(\n    id VARCHAR(12)",
			want: "cluster",
		},
		{
			name: "table with no space",
			sql:  "CREATE TABLE file(id VARCHAR(21)",
			want: "file",
		},
	}
	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			got := extractTableName(tt.sql)
			if got != tt.want {
				t.Errorf("extractTableName() = %v, want %v", got, tt.want)
			}
		})
	}
}
func TestExtractTableNameAsSelect(t *testing.T) {
	tests := []struct {
		name string
		sql  string
		want string
	}{
		{
			name: "simple AS SELECT",
			sql:  "CREATE TABLE ebird_taxonomy_v2024 AS SELECT id FROM ebird_taxonomy",
			want: "ebird_taxonomy_v2024",
		},
		{
			name: "AS SELECT with newlines",
			sql:  "CREATE TABLE my_view AS\nSELECT id\nFROM source",
			want: "my_view",
		},
	}
	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			got := extractTableNameAsSelect(tt.sql)
			if got != tt.want {
				t.Errorf("extractTableNameAsSelect() = %v, want %v", got, tt.want)
			}
		})
	}
}
func TestExtractIndexName(t *testing.T) {
	tests := []struct {
		name string
		sql  string
		want string
	}{
		{
			name: "CREATE INDEX",
			sql:  "CREATE INDEX idx_file_location ON file(location_id)",
			want: "idx_file_location",
		},
		{
			name: "CREATE UNIQUE INDEX",
			sql:  "CREATE UNIQUE INDEX idx_species_label ON species(label)",
			want: "idx_species_label",
		},
		{
			name: "index with spaces",
			sql:  "CREATE INDEX idx_test ON table_name (column)",
			want: "idx_test",
		},
	}
	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			got := extractIndexName(tt.sql)
			if got != tt.want {
				t.Errorf("extractIndexName() = %v, want %v", got, tt.want)
			}
		})
	}
}
func TestExtractDDLStatements_SkipsComments(t *testing.T) {
	schema := `-- This is a comment
CREATE TABLE test (id INT);
-- Another comment
CREATE INDEX idx_test ON test(id);
`
	statements := ExtractDDLStatements(schema)
	// Should have 2 statements, not 4
	if len(statements) != 2 {
		t.Errorf("expected 2 statements, got %d", len(statements))
	}
	for _, stmt := range statements {
		if strings.Contains(stmt.SQL, "--") {
			t.Errorf("statement should not contain comments: %s", stmt.SQL)
		}
	}
}
func TestGetFKOrder(t *testing.T) {
	// Use in-memory database
	db, err := sql.Open("duckdb", ":memory:")
	if err != nil {
		t.Fatalf("failed to open database: %v", err)
	}
	defer db.Close()
	// Create tables with FK relationships
	schema := `
		CREATE TABLE parent (id VARCHAR(12) PRIMARY KEY);
		CREATE TABLE child (id VARCHAR(12) PRIMARY KEY, parent_id VARCHAR(12), FOREIGN KEY (parent_id) REFERENCES parent(id));
		CREATE TABLE grandchild (id VARCHAR(12) PRIMARY KEY, child_id VARCHAR(12), FOREIGN KEY (child_id) REFERENCES child(id));
		CREATE TABLE independent (id VARCHAR(12) PRIMARY KEY);
	`
	_, err = db.Exec(schema)
	if err != nil {
		t.Fatalf("failed to create schema: %v", err)
	}
	order, err := GetFKOrder(db)
	if err != nil {
		t.Fatalf("GetFKOrder() error = %v", err)
	}
	// Build a map for quick lookup
	orderMap := make(map[string]int)
	for i, table := range order {
		orderMap[table] = i
	}
	// Verify order: parent must come before child, child before grandchild
	if orderMap["parent"] >= orderMap["child"] {
		t.Error("parent should come before child")
	}
	if orderMap["child"] >= orderMap["grandchild"] {
		t.Error("child should come before grandchild")
	}
	// Independent table can be anywhere
	if _, ok := orderMap["independent"]; !ok {
		t.Error("independent table missing from order")
	}
}
func TestGetTableRowCount(t *testing.T) {
	// Use in-memory database
	db, err := sql.Open("duckdb", ":memory:")
	if err != nil {
		t.Fatalf("failed to open database: %v", err)
	}
	defer db.Close()
	// Create and populate table
	_, err = db.Exec("CREATE TABLE test (id INT)")
	if err != nil {
		t.Fatalf("failed to create table: %v", err)
	}
	_, err = db.Exec("INSERT INTO test VALUES (1), (2), (3)")
	if err != nil {
		t.Fatalf("failed to insert: %v", err)
	}
	count, err := GetTableRowCount(db, "test")
	if err != nil {
		t.Fatalf("GetTableRowCount() error = %v", err)
	}
	if count != 3 {
		t.Errorf("GetTableRowCount() = %d, want 3", count)
	}
}

File addition: schema.go (----------)

[3.161589]

package db
import (
	"database/sql"
	"embed"
	"fmt"
	"strings"
)
//go:embed schema.sql
var schemaFS embed.FS
// ReadSchemaSQL reads the schema.sql file
// Uses embedded file for distributed binaries
func ReadSchemaSQL() (string, error) {
	data, err := schemaFS.ReadFile("schema.sql")
	if err != nil {
		return "", fmt.Errorf("failed to read schema.sql: %w", err)
	}
	return string(data), nil
}
// DDLStatement represents a parsed DDL statement with metadata
type DDLStatement struct {
	SQL       string
	Type      string // "CREATE_TYPE", "CREATE_TABLE", "CREATE_INDEX", "CREATE_TABLE_AS"
	TableName string // for CREATE TABLE and CREATE INDEX
}
// ExtractDDLStatements splits schema SQL into executable DDL statements
// Returns statements in order: types, tables, indexes
// Handles CREATE TABLE ... AS SELECT specially (marked but included)
func ExtractDDLStatements(schemaSQL string) []DDLStatement {
	var statements []DDLStatement
	// Split by semicolon, but handle the CREATE TABLE AS SELECT case
	lines := strings.Split(schemaSQL, "\n")
	var currentStmt strings.Builder
	for _, line := range lines {
		trimmed := strings.TrimSpace(line)
		// Skip empty lines and comments
		if trimmed == "" || strings.HasPrefix(trimmed, "--") {
			continue
		}
		currentStmt.WriteString(line)
		currentStmt.WriteString("\n")
		// Statement ends at semicolon
		if strings.HasSuffix(trimmed, ";") {
			sql := strings.TrimSpace(currentStmt.String())
			if sql != "" {
				stmt := parseDDLStatement(sql)
				statements = append(statements, stmt)
			}
			currentStmt.Reset()
		}
	}
	// Handle any remaining statement without trailing semicolon
	if currentStmt.Len() > 0 {
		sql := strings.TrimSpace(currentStmt.String())
		if sql != "" && strings.HasSuffix(sql, ";") {
			stmt := parseDDLStatement(sql)
			statements = append(statements, stmt)
		}
	}
	return statements
}
// parseDDLStatement determines the type and table name of a DDL statement
func parseDDLStatement(sql string) DDLStatement {
	upper := strings.ToUpper(sql)
	switch {
	case strings.HasPrefix(upper, "CREATE TYPE"):
		return DDLStatement{SQL: sql, Type: "CREATE_TYPE", TableName: ""}
	case isCreateTableAsSelect(upper):
		// CREATE TABLE name AS SELECT ...
		tableName := extractTableNameAsSelect(sql)
		return DDLStatement{SQL: sql, Type: "CREATE_TABLE_AS", TableName: tableName}
	case strings.HasPrefix(upper, "CREATE TABLE"):
		tableName := extractTableName(sql)
		return DDLStatement{SQL: sql, Type: "CREATE_TABLE", TableName: tableName}
	case strings.HasPrefix(upper, "CREATE INDEX") || strings.HasPrefix(upper, "CREATE UNIQUE INDEX"):
		indexName := extractIndexName(sql)
		return DDLStatement{SQL: sql, Type: "CREATE_INDEX", TableName: indexName}
	default:
		return DDLStatement{SQL: sql, Type: "UNKNOWN", TableName: ""}
	}
}
// isCreateTableAsSelect checks if SQL is CREATE TABLE ... AS SELECT
func isCreateTableAsSelect(upper string) bool {
	if !strings.HasPrefix(upper, "CREATE TABLE") {
		return false
	}
	// Look for AS followed by whitespace and SELECT
	rest := upper[12:] // len("CREATE TABLE")
	
	// Find AS (followed by whitespace)
	asIdx := strings.Index(rest, " AS")
	if asIdx == -1 {
		return false
	}
	
	// Check if what follows AS is whitespace and then SELECT
	afterAs := rest[asIdx+3:] // skip " AS"
	afterAs = strings.TrimSpace(afterAs)
	return strings.HasPrefix(afterAs, "SELECT")
}
// extractTableName extracts table name from CREATE TABLE statement
func extractTableName(sql string) string {
	// CREATE TABLE name (
	// or CREATE TABLE name(
	upper := strings.ToUpper(sql)
	// Find "CREATE TABLE"
	idx := strings.Index(upper, "CREATE TABLE")
	if idx == -1 {
		return ""
	}
	// Move past "CREATE TABLE"
	rest := sql[idx+12:]
	rest = strings.TrimSpace(rest)
	// Find opening parenthesis or end
	endIdx := strings.Index(rest, "(")
	if endIdx == -1 {
		endIdx = len(rest)
	}
	name := strings.TrimSpace(rest[:endIdx])
	return name
}
// extractTableNameAsSelect extracts table name from CREATE TABLE ... AS SELECT
func extractTableNameAsSelect(sql string) string {
	// CREATE TABLE name AS SELECT
	// or CREATE TABLE name AS\nSELECT (multiline)
	upper := strings.ToUpper(sql)
	idx := strings.Index(upper, "CREATE TABLE")
	if idx == -1 {
		return ""
	}
	rest := sql[idx+12:]
	rest = strings.TrimSpace(rest)
	// Find " AS" (followed by whitespace that leads to SELECT)
	asIdx := strings.Index(strings.ToUpper(rest), " AS")
	if asIdx == -1 {
		return ""
	}
	name := strings.TrimSpace(rest[:asIdx])
	return name
}
// extractIndexName extracts index name from CREATE INDEX statement
func extractIndexName(sql string) string {
	upper := strings.ToUpper(sql)
	// Handle "CREATE UNIQUE INDEX" or "CREATE INDEX"
	var rest string
	if strings.HasPrefix(upper, "CREATE UNIQUE INDEX") {
		rest = sql[19:]
	} else if strings.HasPrefix(upper, "CREATE INDEX") {
		rest = sql[12:]
	} else {
		return ""
	}
	rest = strings.TrimSpace(rest)
	// Find " ON "
	onIdx := strings.Index(strings.ToUpper(rest), " ON ")
	if onIdx == -1 {
		return ""
	}
	name := strings.TrimSpace(rest[:onIdx])
	return name
}
// FKRelation represents a foreign key relationship between tables
type FKRelation struct {
	Table        string // table that has the FK
	Column       string // FK column
	ForeignTable string // referenced table
}
// GetFKOrder computes the order tables should be copied based on FK dependencies
// Tables with no FKs come first, then dependent tables in topological order
func GetFKOrder(db *sql.DB) ([]string, error) {
	// Use DuckDB's duckdb_constraints() function for accurate FK info
	query := `
		SELECT table_name, referenced_table
		FROM duckdb_constraints()
		WHERE constraint_type = 'FOREIGN KEY'
		AND referenced_table IS NOT NULL
	`
	rows, err := db.Query(query)
	if err != nil {
		return nil, fmt.Errorf("failed to query FK relationships: %w", err)
	}
	defer rows.Close()
	// Build reverse dependency graph: table -> tables that depend on it
	// dependsOnMe[A] = [B, C] means B and C have FKs to A
	dependsOnMe := make(map[string][]string)
	tables := make(map[string]bool)
	for rows.Next() {
		var table, foreignTable string
		if err := rows.Scan(&table, &foreignTable); err != nil {
			return nil, fmt.Errorf("failed to scan FK row: %w", err)
		}
		tables[table] = true
		tables[foreignTable] = true
		// foreignTable is referenced by table
		dependsOnMe[foreignTable] = append(dependsOnMe[foreignTable], table)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("error iterating FK rows: %w", err)
	}
	// Get all tables from the database
	tableRows, err := db.Query(`
		SELECT table_name 
		FROM information_schema.tables 
		WHERE table_schema = 'main' 
		AND table_type = 'BASE TABLE'
	`)
	if err != nil {
		return nil, fmt.Errorf("failed to query tables: %w", err)
	}
	defer tableRows.Close()
	for tableRows.Next() {
		var name string
		if err := tableRows.Scan(&name); err != nil {
			return nil, fmt.Errorf("failed to scan table name: %w", err)
		}
		tables[name] = true
	}
	// Count how many FKs each table has (tables it depends on)
	fkCount := make(map[string]int)
	for table := range tables {
		fkCount[table] = 0
	}
	for _, dependents := range dependsOnMe {
		for _, dependent := range dependents {
			fkCount[dependent]++
		}
	}
	// Topological sort (Kahn's algorithm)
	// 1. Start with tables that have no FKs (fkCount = 0)
	var queue []string
	for table := range tables {
		if fkCount[table] == 0 {
			queue = append(queue, table)
		}
	}
	// 2. Process queue
	var result []string
	for len(queue) > 0 {
		// Pop first element
		current := queue[0]
		queue = queue[1:]
		result = append(result, current)
		// For each table that depends on current, decrease its FK count
		for _, dependent := range dependsOnMe[current] {
			fkCount[dependent]--
			if fkCount[dependent] == 0 {
				queue = append(queue, dependent)
			}
		}
	}
	// If result doesn't contain all tables, there's a cycle
	if len(result) != len(tables) {
		// Add remaining tables (cycle handling)
		for table := range tables {
			found := false
			for _, r := range result {
				if r == table {
					found = true
					break
				}
			}
			if !found {
				result = append(result, table)
			}
		}
	}
	return result, nil
}
// GetTableRowCount returns the number of rows in a table
func GetTableRowCount(db *sql.DB, table string) (int64, error) {
	var count int64
	err := db.QueryRow(fmt.Sprintf("SELECT COUNT(*) FROM %s", table)).Scan(&count)
	if err != nil {
		return 0, fmt.Errorf("failed to count rows in %s: %w", table, err)
	}
	return count, nil
}

File addition: export.go (----------)

[8.1]

package cmd
import (
	"context"
	"encoding/json"
	"flag"
	"fmt"
	"os"
	"skraak/tools"
)
// RunExport handles the "export" subcommand
func RunExport(args []string) {
	if len(args) < 1 {
		printExportUsage()
		os.Exit(1)
	}
	switch args[0] {
	case "dataset":
		runExportDataset(args[1:])
	default:
		fmt.Fprintf(os.Stderr, "Unknown export subcommand: %s\n\n", args[0])
		printExportUsage()
		os.Exit(1)
	}
}
func printExportUsage() {
	fmt.Fprintf(os.Stderr, "Usage: skraak export <subcommand> [options]\n\n")
	fmt.Fprintf(os.Stderr, "Subcommands:\n")
	fmt.Fprintf(os.Stderr, "  dataset    Export a dataset with all related data\n")
	fmt.Fprintf(os.Stderr, "\nExamples:\n")
	fmt.Fprintf(os.Stderr, "  skraak export dataset --db ./db/skraak.duckdb --id abc123 --output export.duckdb\n")
	fmt.Fprintf(os.Stderr, "  skraak export dataset --db ./db/skraak.duckdb --id abc123 --output export.duckdb --dry-run\n")
}
func runExportDataset(args []string) {
	fs := flag.NewFlagSet("export dataset", flag.ExitOnError)
	dbPath := fs.String("db", "", "Path to source DuckDB database (required)")
	datasetID := fs.String("id", "", "Dataset ID to export (required)")
	output := fs.String("output", "", "Output database path (required)")
	dryRun := fs.Bool("dry-run", false, "Show what would be exported without creating file")
	force := fs.Bool("force", false, "Overwrite existing output file")
	fs.Usage = func() {
		fmt.Fprintf(os.Stderr, "Usage: skraak export dataset --db <path> --id <dataset_id> --output <path> [options]\n\n")
		fmt.Fprintf(os.Stderr, "Export a dataset with all related data to a new DuckDB database.\n\n")
		fmt.Fprintf(os.Stderr, "Options:\n")
		fs.PrintDefaults()
		fmt.Fprintf(os.Stderr, "\nExamples:\n")
		fmt.Fprintf(os.Stderr, "  skraak export dataset --db ./db/skraak.duckdb --id abc123 --output export.duckdb\n")
		fmt.Fprintf(os.Stderr, "  skraak export dataset --db ./db/skraak.duckdb --id abc123 --output export.duckdb --dry-run\n")
		fmt.Fprintf(os.Stderr, "  skraak export dataset --db ./db/skraak.duckdb --id abc123 --output export.duckdb --force\n")
	}
	if err := fs.Parse(args); err != nil {
		os.Exit(1)
	}
	// Validate required flags
	missing := []string{}
	if *dbPath == "" {
		missing = append(missing, "--db")
	}
	if *datasetID == "" {
		missing = append(missing, "--id")
	}
	if *output == "" {
		missing = append(missing, "--output")
	}
	if len(missing) > 0 {
		fmt.Fprintf(os.Stderr, "Error: missing required flags: %v\n\n", missing)
		fs.Usage()
		os.Exit(1)
	}
	tools.SetDBPath(*dbPath)
	input := tools.ExportDatasetInput{
		DatasetID: *datasetID,
		Output:    *output,
		DryRun:    *dryRun,
		Force:     *force,
	}
	outputResult, err := tools.ExportDataset(context.Background(), input)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Error: %v\n", err)
		os.Exit(1)
	}
	enc := json.NewEncoder(os.Stdout)
	enc.SetIndent("", "  ")
	enc.Encode(outputResult)
}

Insertion in README.md at line 69 [3.334405]

[2.79]

# Export dataset (for collaboration, testing, or archival)
./skraak export dataset --db ./db/skraak.duckdb --id abc123 --output export.duckdb
./skraak export dataset --db ./db/skraak.duckdb --id abc123 --output export.duckdb --dry-run

Insertion in README.md at line 120 [3.334405]

[9.18453]

[5.30044]


**Export:**
- `export dataset` - Export a dataset with all related data to new database

Insertion in README.md at line 172 [3.334405]

[2.1390]


## Dataset Export
Export a dataset with all related data to a new DuckDB database for collaboration, testing, or archival.
**Use cases:**
- **Collaboration:** Export, send to collaborator, they return event log for replay
- **Testing:** Create focused test database from production (100 MB vs 1.5 GB)
- **Archival:** Snapshot a dataset at a point in time

Insertion in README.md at line 182 [3.334405]

[2.1391]

[10.153096]

**Export:**
```bash
# Export dataset to new database
./skraak export dataset --db ./db/skraak.duckdb --id abc123 --output export.duckdb
# Preview without creating file
./skraak export dataset --db ./db/skraak.duckdb --id abc123 --output export.duckdb --dry-run
# Overwrite existing export
./skraak export dataset --db ./db/skraak.duckdb --id abc123 --output export.duckdb --force
```
**What's exported:**
- All rows owned by dataset (via dataset_id foreign key traversal)
- Subset of reference data (species, patterns, filters used)
- Creates empty event log file for changes
**Re-import changes:**
```bash
# After collaborator returns event log, replay on backup
./skraak replay events --db ./backup.duckdb --log export.duckdb.events.jsonl
```

Insertion in CLAUDE.md at line 298 [3.363912]

[6.25495]


---
## Dataset Export
Export a dataset with all related data to a new DuckDB database.
```bash
skraak export dataset --db skraak.duckdb --id abc123 --output export.duckdb
skraak export dataset --db skraak.duckdb --id abc123 --output export.duckdb --dry-run
```
**Use cases:** Collaboration, testing (small DB vs 1.5 GB), archival
**Key files:**
- `tools/export.go` - Export logic with table manifest
- `db/schema.go` - Schema utilities (DDL extraction, FK ordering)

Replacement in CLAUDE.md at line 347 [3.363912]
B:BD[11.70719] → [6.25502:25546]
```
**Status:** Event log complete (2026-02-18)
```
[11.70719]
[12.9397]
```
**Status:** Dataset export complete (2026-02-19)
```

Replacement in CLAUDE.md at line 350 [3.363912]

B:BD[12.9514] → [6.25547:25643]

**CLI Commands:** `mcp`, `sql`, `dataset`, `location`, `cluster`, `pattern`, `import`, `replay`

[12.9514]

[6.25643]

**CLI Commands:** `mcp`, `sql`, `dataset`, `location`, `cluster`, `pattern`, `import`, `export`, `replay`

Replacement in CLAUDE.md at line 352 [3.363912]

B:BD[6.25723] → [6.25723:25771]

**Test Scripts:** 9 comprehensive shell scripts

[6.25723]

[12.9648]

**Dataset Export:** Full dataset export with FK traversal (`skraak export dataset`)
**Test Scripts:** 10 comprehensive shell scripts

Replacement in CLAUDE.md at line 360 [3.363912]
B:BD[11.70799] → [6.25772:25806]
```
**Last Updated:** 2026-02-18 NZDT
```
[11.70799]
```
**Last Updated:** 2026-02-19 NZDT
```

Insertion in CHANGELOG.md at line 4 [13.1]

[13.81]

[6.25808]


## [2026-02-19] Dataset Export for Collaboration and Testing
**New feature: Export a dataset with all related data to a new database**
**Purpose:** Enable dataset-level exports for collaboration (export, modify, replay changes), testing (small focused test DBs), and archival.
**Architecture:**
- Schema read from embedded `db/schema.sql` (DDL statements extracted dynamically)
- Table copy order computed from FK relationships using `duckdb_constraints()`
- ATTACH mechanism for efficient cross-database copying
- Declarative manifest defines table relationships
**Added:**
- `tools/export.go` — `ExportDataset()` with table manifest and copy logic
- `cmd/export.go` — `skraak export dataset` CLI command
- `db/schema.go` — Schema utilities: `ReadSchemaSQL()`, `ExtractDDLStatements()`, `GetFKOrder()`
- `shell_scripts/test_export.sh` — Integration test script
**Command:**
```bash
skraak export dataset --db skraak.duckdb --id abc123 --output export.duckdb
skraak export dataset --db skraak.duckdb --id abc123 --output export.duckdb --dry-run
skraak export dataset --db skraak.duckdb --id abc123 --output export.duckdb --force
```
**What's exported:**
- Dataset row and all owned data (locations, clusters, files, selections, labels)
- Subset of reference data (species, patterns, filters used by dataset)
- Reference tables copied as-is (`ebird_taxonomy_v2024`)
- Empty event log created for capturing changes
**Design decisions:**
- Schema from `schema.sql` ensures schema-resilience (new columns auto-included)
- FK order computed dynamically via `duckdb_constraints()` function
- Close source DB before output DB (DuckDB single-connection limit)
- `SELECT *` copies all columns without hard-coding

Insertion in CHANGELOG.md at line 42 [13.1]

[6.25809]

**Testing:**
- `db/schema_test.go` — Unit tests for DDL extraction and FK ordering
- Integration tests verify row counts match source
- Error handling tests for missing dataset, existing file
---

first iteration of export feature

Dependencies

In channels

Change contents

File addition: export.go (----------)

File addition: test_export.sh (---x------)

Insertion in main.go at line 31 [3.149392]

Insertion in main.go at line 59 [3.149392]

Insertion in main.go at line 67 [3.149392]

File addition: schema_test.go (----------)

File addition: schema.go (----------)

File addition: export.go (----------)

Insertion in README.md at line 69 [3.334405]

Insertion in README.md at line 120 [3.334405]

Insertion in README.md at line 172 [3.334405]

Insertion in README.md at line 182 [3.334405]

Insertion in CLAUDE.md at line 298 [3.363912]

Replacement in CLAUDE.md at line 347 [3.363912]

Replacement in CLAUDE.md at line 350 [3.363912]

Replacement in CLAUDE.md at line 352 [3.363912]

Replacement in CLAUDE.md at line 360 [3.363912]

Insertion in CHANGELOG.md at line 4 [13.1]

Insertion in CHANGELOG.md at line 42 [13.1]