Imported from paleoterra/PaleoRose (.claude/skills/xrose-database-reader/SKILL.md). Install upstream with npx skills add paleoterra/PaleoRose --skill xrose-database-reader. Copyright stays with the author.

XRose Database Reader

Read, inspect, and analyze XRose SQLite database files containing rose diagram documents.

Capabilities

1. Schema Inspection

   • List all tables in XRose file
   • Show table schemas
   • Identify configuration vs. data tables
   • Detect orphaned data tables

2. Data Reading

   • Query configuration tables (_windowController, _geometryController, etc.)
   • Read layer definitions and configurations
   • Extract dataset metadata
   • Query user data tables
   • Export data to CSV/JSON

3. Analysis

   • Summarize document contents
   • List all layers and their types
   • Show dataset-to-table mappings
   • Identify unused data tables
   • Validate schema integrity

4. Reporting

   • Generate document summary reports
   • Create data inventory
   • Show layer hierarchy
   • List colors used in document

5. Data Visualization Prep

   • Extract data for specific layers
   • Calculate statistics (mean vector, spread, etc.)
   • Prepare data for plotting
   • Export binned data

Workflow

When invoked, this skill will:

1. Validate: Ensure file is a valid XRose SQLite database
2. Inspect: Analyze schema and contents
3. Query: Execute user-requested queries
4. Report: Present results in clear format

Usage Instructions

When the user invokes this skill:

1. Ask what operation to perform:

   • Inspect: Show schema and tables
   • Read: Query specific data
   • Analyze: Generate summary report
   • Export: Extract data for external use

2. For inspecting:

   • List all tables
   • Show configuration settings
   • Display layer information
   • Identify data tables

3. For reading data:

   • Specify table name
   • Show all rows or filtered subset
   • Format output (table, JSON, CSV)

4. For analysis:

   • Count layers by type
   • List datasets and their sources
   • Find orphaned tables
   • Validate relationships

XRose File Format Specification

Overview

XRose files (.XRose extension) are SQLite database files that store rose diagram documents for geological data visualization. Each file contains configuration tables for the diagram appearance, layer definitions, and one or more data tables containing the actual measurement data.

File Structure

The XRose format uses SQLite as its container format. Each .XRose file contains:

• Configuration tables (prefixed with _)
• User data tables (dynamically named)

Configuration Tables

_windowController

Stores the document window dimensions.

Purpose: Stores the document window size (not the plot/canvas size).

_geometryController

Stores global rose diagram geometry and plotting settings.

Notes:

• MAXCOUNT is the maximum count for a single sector, used with TOTALCOUNT from layers to layout the plot
• MAXPERCENT is calculated using individual layer MAXPERCENT values to determine the overall plot scale

_colors

Color palette for the document.

Purpose: Centralized color definitions referenced by STROKECOLORID and FILLCOLORID in layers.

_datasets

Defines data sources that are actively used by layers in the rose diagram.

Notes:

• Datasets are only created when layers reference them (via _layerData.DATASET or _layerLineArrow.DATASET)
• Design Limitation: Data tables in the file that are not referenced by any dataset are "orphaned" and won't appear in the datasets list
• Multiple datasets can reference the same TABLENAME with different COLUMNNAME and PREDICATE values to create filtered views
• Only one column per dataset can contain vector data
• The predicate allows users to dynamically filter data as needed

Layer Tables

Layers control the visual representation of data and annotations. Each layer has a base entry in _layers and may have additional configuration in type-specific tables.

_layers

Base configuration common to all layer types.

Notes:

• ACTIVE indicates the user-selected layer for editing
• VISIBLE controls rendering (independent of ACTIVE)
• BIDIR is used for vector data to show both direction and its reciprocal (180° opposite)

_layerText

Configuration for text annotation layers.

_layerLineArrow

Configuration for vector/arrow display layers. Links to datasets.

Notes:

• VECTORTYPE draws a statistical vector line in one of two styles
• ARROWTYPE offers different arrow head styles pointing in the vector direction
• Statistical vectors and error bars are calculated from data (recalculated when configuration changes)

_layerCore

Configuration for the core (center circle) layer.

_layerGrid

Configuration for grid (rings and radials) layer.

Notes:

• Rings are concentric circles showing magnitude
• Radials are lines emanating from center
• RADIALS_COMPASSPOINT controls N/S/E/W labeling
• RADIALS_ORDER controls whether labels follow quadrant order or 0-360° order

_layerData

Configuration for data visualization layers. Links to datasets.

Plot Types (PLOTTYPE values):

• Petals (rose diagram sectors)
• Kite
• Dot
• Dot deviation (shows individual data points that deviate from mean vector)
• Histogram
• Histogram deviation (shows deviation from average in each sector)

Notes:

• TOTALCOUNT is the actual count of data points, used with _geometryController.MAXCOUNT to layout the plot
• Multiple layers can display the same dataset with different visualization settings
• Deviation plot types show how individual measurements deviate from the average (more or less), different from error bars

Data Tables

User data is stored in dynamically named tables (e.g., rtest, measurements, etc.). The table name is referenced by _datasets.TABLENAME.

Data Table Structure

Each data table has:

• _id (INTEGER PRIMARY KEY) - Auto-generated row identifier
• One or more user-defined columns containing:
  • Vector data (NUMERIC or TEXT) - angles, directions, measurements
  • Additional metadata columns

Example:

CREATE TABLE rtest (
    _id INTEGER PRIMARY KEY,
    "azimuth" NUMERIC,
    "dip" NUMERIC,
    "location" TEXT
);

Notes:

• Column names are user-defined and can be anything
• Only one column per dataset contains the vector data (specified by _datasets.COLUMNNAME)
• Users can import tables with many columns; datasets select which column to visualize
• Data can be filtered using _datasets.PREDICATE for flexible data views
• Design Limitation: Data tables can exist in the XRose file without any corresponding entry in _datasets (orphaned tables). This happens when:
  • Data is imported but never used in a layer
  • A layer/dataset referencing the table is deleted
  • No mechanism exists to track or clean up unused tables

Relationships

Foreign Key Relationships

_colors.COLORID
    ← _layers.STROKECOLORID
    ← _layers.FILLCOLORID

_layers.LAYERID
    ← _layerText.LAYERID
    ← _layerLineArrow.LAYERID
    ← _layerCore.LAYERID
    ← _layerGrid.LAYERID
    ← _layerData.LAYERID

_datasets._id
    ← _layerData.DATASET
    ← _layerLineArrow.DATASET

_datasets.TABLENAME
    → [User Data Tables]

Layer Type Mapping

Each layer must have an entry in _layers and may have an entry in one type-specific table:

• TYPE = "text" → entry in _layerText
• TYPE = "data" → entry in _layerData
• TYPE = "arrow" → entry in _layerLineArrow
• TYPE = "core" → entry in _layerCore
• TYPE = "grid" → entry in _layerGrid

Only _layerData and _layerLineArrow reference datasets (via DATASET column).

Design Principles

1. Separation of Concerns: Configuration (layers, geometry) is separated from data (user tables)
2. Flexible Data Import: Users can import tables with any structure; datasets define which columns to use
3. Multiple Views: Multiple datasets can reference the same table with different filters and columns
4. Layer Composition: Complex visualizations are built by combining multiple typed layers
5. Reusable Colors: Centralized color palette prevents duplication
6. Statistical Calculations: Vectors and error bars are calculated from data and cached until configuration changes

Design Limitations

1. Orphaned Data Tables: The current design creates datasets only when layers reference them. This means:

   • Data tables can exist in the file without any _datasets entry
   • There's no inventory of all available data tables in the document
   • Users cannot see or manage imported data that isn't currently being visualized
   • Deleted layers/datasets leave their data tables behind with no cleanup mechanism

2. No Data Table Discovery: To find all data tables in a document, you must:

   • Query SQLite system tables to list all non-configuration tables
   • Filter out tables starting with _ (configuration tables)
   • This is an implementation detail, not part of the documented format

Common Queries

List All Tables

sqlite3 file.XRose ".tables"

Show Document Summary

-- Get geometry settings
SELECT * FROM _geometryController;

-- Count layers by type
SELECT TYPE, COUNT(*) as count
FROM _layers
GROUP BY TYPE;

-- List all datasets
SELECT _id, NAME, TABLENAME, COLUMNNAME
FROM _datasets;

Find All Data Tables (Including Orphaned)

-- Get all table names
SELECT name FROM sqlite_master
WHERE type='table'
AND name NOT LIKE '_%'
AND name NOT LIKE 'sqlite_%';

Show Layer Configuration

-- Get all layers with their colors
SELECT
    l.LAYERID,
    l.LAYER_NAME,
    l.TYPE,
    l.VISIBLE,
    sc.RED as stroke_r, sc.GREEN as stroke_g, sc.BLUE as stroke_b,
    fc.RED as fill_r, fc.GREEN as fill_g, fc.BLUE as fill_b
FROM _layers l
LEFT JOIN _colors sc ON l.STROKECOLORID = sc.COLORID
LEFT JOIN _colors fc ON l.FILLCOLORID = fc.COLORID;

Export Data from Table

# Export to CSV
sqlite3 file.XRose ".mode csv" ".headers on" "SELECT * FROM rtest;"

# Export to JSON
sqlite3 file.XRose ".mode json" "SELECT * FROM rtest;"

Get Dataset Data with Predicate

-- Example: If dataset has PREDICATE="location='Site A'"
SELECT * FROM measurements WHERE location='Site A';

Usage Examples

Example 1: Inspect File

# When user asks: "What's in this XRose file?"
sqlite3 "Unit Tests/rtest1.XRose" << EOF
.tables
SELECT 'Geometry:' as info;
SELECT * FROM _geometryController;
SELECT 'Layers:' as info;
SELECT LAYERID, LAYER_NAME, TYPE, VISIBLE FROM _layers;
SELECT 'Datasets:' as info;
SELECT NAME, TABLENAME, COLUMNNAME FROM _datasets;
EOF

Example 2: Read Data Table

# When user asks: "Show me the data from the rtest table"
sqlite3 "Unit Tests/rtest1.XRose" << EOF
.mode column
.headers on
SELECT * FROM rtest LIMIT 20;
EOF

Example 3: Find Orphaned Tables

# When user asks: "Are there any unused data tables?"
sqlite3 "Unit Tests/rtest1.XRose" << EOF
-- Get all non-config tables
WITH all_tables AS (
    SELECT name FROM sqlite_master
    WHERE type='table'
    AND name NOT LIKE '_%'
    AND name NOT LIKE 'sqlite_%'
),
used_tables AS (
    SELECT DISTINCT TABLENAME as name FROM _datasets
)
SELECT at.name as "Orphaned Tables"
FROM all_tables at
LEFT JOIN used_tables ut ON at.name = ut.name
WHERE ut.name IS NULL;
EOF

Example 4: Layer Details

# When user asks: "Tell me about the data layers"
sqlite3 "Unit Tests/rtest1.XRose" << EOF
.mode column
.headers on
SELECT
    l.LAYER_NAME,
    ld.PLOTTYPE,
    ld.TOTALCOUNT,
    d.NAME as dataset_name,
    d.TABLENAME,
    d.COLUMNNAME
FROM _layers l
JOIN _layerData ld ON l.LAYERID = ld.LAYERID
JOIN _datasets d ON ld.DATASET = d._id
WHERE l.TYPE = 'data';
EOF

Quick Reference Card

Configuration

Store reader settings in .xrose-reader.json:

{
  "defaultDisplayMode": "column",
  "showHeaders": true,
  "maxRowsDefault": 100,
  "autoDetectDataTables": true,
  "highlightOrphanedTables": true,
  "exportFormat": "csv"
}