quietlight/skraak_mcp - Change WMJ6PWEIVOPQUW5VYBBZWZVWTLKRTU5EO2HNAL3MQVMB42PTNOCAC

much more extensive db state tests in shell scripts

Created by quietlight on April 18, 2026

WMJ6PWEIVOPQUW5VYBBZWZVWTLKRTU5EO2HNAL3MQVMB42PTNOCAC

Dependencies

[2] 2Y2ZW565SRONQ2UXPLX5SRP2HDFWMRF5KDXKSKVRCHBBGEGMTVIQC

In channels

main

Change contents

Replacement in shell_scripts/test_db_state.sh at line 2 [2.22467]

B:BD[2.22480] → [2.22480:22544]

# Verify database state - check table counts and data integrity

[2.22480]

[2.22544]

# Verify database state - check table counts and referential integrity

Replacement in shell_scripts/test_db_state.sh at line 4 [2.22467]
B:BD[2.22582] → [2.22582:22622]
```
# Default: uses test.duckdb (read-only)
```
[2.22582]
[2.22622]
```
# Default: uses test.duckdb
```
Insertion in shell_scripts/test_db_state.sh at line 20 [2.22467]
[2.22893]
[2.22893]
```
sql() {
    "$PROJECT_DIR/skraak" sql --db "$DB_PATH" "$1" 2>/dev/null
}
```

Replacement in shell_scripts/test_db_state.sh at line 25 [2.22467]

B:BD[2.22894] → [2.22894:24033]

# Run multiple queries in single session
results=$(send_requests "$DB_PATH" \
    '"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT COUNT(*) as cnt FROM dataset WHERE active = true"}}' \
    '"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT COUNT(*) as cnt FROM location WHERE active = true"}}' \
    '"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT COUNT(*) as cnt FROM cluster WHERE active = true"}}' \
    '"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT COUNT(*) as cnt FROM file WHERE active = true"}}' \
    '"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT COUNT(*) as cnt FROM file_dataset"}}' \
    '"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT COUNT(*) as cnt FROM moth_metadata WHERE active = true"}}' \
    '"method":"tools/call","params":{"name":"execute_sql","arguments":{"query":"SELECT COUNT(*) as orphaned FROM file f LEFT JOIN file_dataset fd ON f.id = fd.file_id WHERE f.active = true AND fd.file_id IS NULL"}}' \
)

[2.22894]

[2.24033]

cnt() {
    sql "$1" | jq -r '.rows[0].cnt // "error"'
}

Replacement in shell_scripts/test_db_state.sh at line 29 [2.22467]

B:BD[2.24034] → [2.24034:24854]

# Extract counts
datasets=$(echo "$results" | grep '"id":2' | head -1 | jq -r '.result.structuredContent.rows[0].cnt // "error"')
locations=$(echo "$results" | grep '"id":3' | head -1 | jq -r '.result.structuredContent.rows[0].cnt // "error"')
clusters=$(echo "$results" | grep '"id":4' | head -1 | jq -r '.result.structuredContent.rows[0].cnt // "error"')
files=$(echo "$results" | grep '"id":5' | head -1 | jq -r '.result.structuredContent.rows[0].cnt // "error"')
file_dataset=$(echo "$results" | grep '"id":6' | head -1 | jq -r '.result.structuredContent.rows[0].cnt // "error"')
moth_metadata=$(echo "$results" | grep '"id":7' | head -1 | jq -r '.result.structuredContent.rows[0].cnt // "error"')
orphaned=$(echo "$results" | grep '"id":8' | head -1 | jq -r '.result.structuredContent.rows[0].orphaned // "error"')

[2.24034]

[2.24854]

# Assert a query returns 0 rows (integrity violation check)
check_zero() {
    local name="$1"
    local query="$2"
    local count
    count=$(cnt "$query")
    ((TESTS_RUN++)) || true
    if [ "$count" = "0" ]; then
        echo -e "  ${GREEN}✓${NC} $name"
        ((TESTS_PASSED++)) || true
    else
        echo -e "  ${RED}✗${NC} $name: $count violation(s)"
        ((TESTS_FAILED++)) || true
    fi
}

Replacement in shell_scripts/test_db_state.sh at line 45 [2.22467]

B:BD[2.24855] → [2.24855:24871]

# Print results

[2.24855]

[2.24871]

# ── Counts ────────────────────────────────────────────────────────────────────

Replacement in shell_scripts/test_db_state.sh at line 47 [2.22467]

B:BD[2.24892] → [2.24892:25109]

echo "  Datasets:       $datasets"
echo "  Locations:      $locations"
echo "  Clusters:       $clusters"
echo "  Files:          $files"
echo "  File-Dataset:   $file_dataset"
echo "  Moth Metadata:  $moth_metadata"

[2.24892]

[2.25109]

echo "  Datasets:        $(cnt 'SELECT COUNT(*) AS cnt FROM dataset WHERE active = true')"
echo "  Locations:       $(cnt 'SELECT COUNT(*) AS cnt FROM location WHERE active = true')"
echo "  Clusters:        $(cnt 'SELECT COUNT(*) AS cnt FROM cluster WHERE active = true')"
echo "  Files:           $(cnt 'SELECT COUNT(*) AS cnt FROM file WHERE active = true')"
echo "  File-Dataset:    $(cnt 'SELECT COUNT(*) AS cnt FROM file_dataset')"
echo "  Segments:        $(cnt 'SELECT COUNT(*) AS cnt FROM segment WHERE active = true')"
echo "  Labels:          $(cnt 'SELECT COUNT(*) AS cnt FROM label WHERE active = true')"
echo "  Label subtypes:  $(cnt 'SELECT COUNT(*) AS cnt FROM label_subtype WHERE active = true')"
echo "  Moth metadata:   $(cnt 'SELECT COUNT(*) AS cnt FROM moth_metadata WHERE active = true')"
echo "  File metadata:   $(cnt 'SELECT COUNT(*) AS cnt FROM file_metadata WHERE active = true')"
echo "  Label metadata:  $(cnt 'SELECT COUNT(*) AS cnt FROM label_metadata WHERE active = true')"

Replacement in shell_scripts/test_db_state.sh at line 60 [2.22467]

B:BD[2.25118] → [2.25118:25161]

# Check integrity
echo "Integrity Checks:"

[2.25118]

[2.25161]

# ── Location hierarchy ────────────────────────────────────────────────────────
echo "Location hierarchy:"
check_zero "location.dataset_id → dataset" \
    "SELECT COUNT(*) AS cnt FROM location l LEFT JOIN dataset d ON l.dataset_id = d.id WHERE d.id IS NULL"
check_zero "cluster.location_id → location" \
    "SELECT COUNT(*) AS cnt FROM cluster c LEFT JOIN location l ON c.location_id = l.id WHERE l.id IS NULL"
check_zero "cluster.dataset_id → dataset" \
    "SELECT COUNT(*) AS cnt FROM cluster c LEFT JOIN dataset d ON c.dataset_id = d.id WHERE d.id IS NULL"
check_zero "cluster.cyclic_recording_pattern_id → cyclic_recording_pattern" \
    "SELECT COUNT(*) AS cnt FROM cluster c LEFT JOIN cyclic_recording_pattern p ON c.cyclic_recording_pattern_id = p.id WHERE c.cyclic_recording_pattern_id IS NOT NULL AND p.id IS NULL"
echo ""

Replacement in shell_scripts/test_db_state.sh at line 72 [2.22467]

B:BD[2.25162] → [2.25162:25411]

if [ "$orphaned" = "0" ]; then
    echo -e "  ${GREEN}✓${NC} No orphaned files (files have file_dataset entries)"
    ((TESTS_PASSED++)) || true
else
    echo -e "  ${RED}✗${NC} Found $orphaned orphaned files!"
    ((TESTS_FAILED++)) || true
fi

[2.25162]

[2.25411]

# ── File linkage ──────────────────────────────────────────────────────────────
echo "File linkage:"
check_zero "file.location_id → location" \
    "SELECT COUNT(*) AS cnt FROM file f LEFT JOIN location l ON f.location_id = l.id WHERE f.location_id IS NOT NULL AND l.id IS NULL"
check_zero "file.cluster_id → cluster" \
    "SELECT COUNT(*) AS cnt FROM file f LEFT JOIN cluster c ON f.cluster_id = c.id WHERE f.cluster_id IS NOT NULL AND c.id IS NULL"
check_zero "file_dataset.file_id → file" \
    "SELECT COUNT(*) AS cnt FROM file_dataset fd LEFT JOIN file f ON fd.file_id = f.id WHERE f.id IS NULL"
check_zero "file_dataset.dataset_id → dataset" \
    "SELECT COUNT(*) AS cnt FROM file_dataset fd LEFT JOIN dataset d ON fd.dataset_id = d.id WHERE d.id IS NULL"
check_zero "active files have file_dataset entry" \
    "SELECT COUNT(*) AS cnt FROM file f LEFT JOIN file_dataset fd ON f.id = fd.file_id WHERE f.active = true AND fd.file_id IS NULL"
check_zero "file_dataset count >= active file count" \
    "SELECT CASE WHEN (SELECT COUNT(*) FROM file_dataset) >= (SELECT COUNT(*) FROM file WHERE active = true) THEN 0 ELSE 1 END AS cnt"
check_zero "file_metadata.file_id → file" \
    "SELECT COUNT(*) AS cnt FROM file_metadata fm LEFT JOIN file f ON fm.file_id = f.id WHERE f.id IS NULL"
echo ""
# ── Segment integrity ─────────────────────────────────────────────────────────
echo "Segment integrity:"
check_zero "segment.file_id → file" \
    "SELECT COUNT(*) AS cnt FROM segment s LEFT JOIN file f ON s.file_id = f.id WHERE f.id IS NULL"
check_zero "segment.dataset_id → dataset" \
    "SELECT COUNT(*) AS cnt FROM segment s LEFT JOIN dataset d ON s.dataset_id = d.id WHERE d.id IS NULL"
check_zero "active segments on inactive files" \
    "SELECT COUNT(*) AS cnt FROM segment s JOIN file f ON s.file_id = f.id WHERE s.active = true AND f.active = false"
echo ""

Replacement in shell_scripts/test_db_state.sh at line 100 [2.22467]

B:BD[2.25412] → [2.25412:25705]

if [ "$files" = "$file_dataset" ] || [ "$file_dataset" -ge "$files" ]; then
    echo -e "  ${GREEN}✓${NC} file_dataset count >= file count"
    ((TESTS_PASSED++)) || true
else
    echo -e "  ${RED}✗${NC} file_dataset count < file count (missing entries)"
    ((TESTS_FAILED++)) || true
fi

[2.25412]

[2.25705]

# ── Label integrity ───────────────────────────────────────────────────────────
echo "Label integrity:"
check_zero "label.segment_id → segment" \
    "SELECT COUNT(*) AS cnt FROM label l LEFT JOIN segment s ON l.segment_id = s.id WHERE s.id IS NULL"
check_zero "label.species_id → species" \
    "SELECT COUNT(*) AS cnt FROM label l LEFT JOIN species sp ON l.species_id = sp.id WHERE sp.id IS NULL"
check_zero "label.filter_id → filter" \
    "SELECT COUNT(*) AS cnt FROM label l LEFT JOIN filter f ON l.filter_id = f.id WHERE f.id IS NULL"
check_zero "active labels on inactive segments" \
    "SELECT COUNT(*) AS cnt FROM label l JOIN segment s ON l.segment_id = s.id WHERE l.active = true AND s.active = false"
check_zero "label_metadata.label_id → label" \
    "SELECT COUNT(*) AS cnt FROM label_metadata lm LEFT JOIN label l ON lm.label_id = l.id WHERE l.id IS NULL"
echo ""

Replacement in shell_scripts/test_db_state.sh at line 114 [2.22467]

B:BD[2.25706] → [2.25706:25731]

((TESTS_RUN+=2)) || true

[2.25706]

[2.25731]

# ── Label subtype integrity ───────────────────────────────────────────────────
echo "Label subtype integrity:"
check_zero "label_subtype.label_id → label" \
    "SELECT COUNT(*) AS cnt FROM label_subtype ls LEFT JOIN label l ON ls.label_id = l.id WHERE l.id IS NULL"
check_zero "label_subtype.calltype_id → call_type" \
    "SELECT COUNT(*) AS cnt FROM label_subtype ls LEFT JOIN call_type ct ON ls.calltype_id = ct.id WHERE ct.id IS NULL"
check_zero "label_subtype.filter_id → filter" \
    "SELECT COUNT(*) AS cnt FROM label_subtype ls LEFT JOIN filter f ON ls.filter_id = f.id WHERE ls.filter_id IS NOT NULL AND f.id IS NULL"
echo ""

Insertion in shell_scripts/test_db_state.sh at line 124 [2.22467]

[2.25732]

# ── Reference table integrity ─────────────────────────────────────────────────
echo "Reference table integrity:"
check_zero "call_type.species_id → species" \
    "SELECT COUNT(*) AS cnt FROM call_type ct LEFT JOIN species sp ON ct.species_id = sp.id WHERE sp.id IS NULL"

Insertion in shell_scripts/test_db_state.sh at line 129 [2.22467]

[2.25740]


# ── Summary ───────────────────────────────────────────────────────────────────