# Data Models

SPIDB extends the [SONICDB](https://github.com/dkadyrov/sonicdb) framework with specialized models for stored product insect research. All models use SQLAlchemy ORM and support SQLite, MySQL, and PostgreSQL databases.

![SPIDB Schema](images/spidb.svg)

## Core Models (from SONICDB)

### Channel

Represents an individual recording channel.

**Attributes:**
- `id` (Integer) - Primary key
- `number` (Integer) - Channel number
- `type_class` (String) - Channel type (e.g., "piezoelectric", "microwave")
- `sensor_id` (Integer) - Foreign key to Sensor

**Relationships:**
- `sensor` - Parent Sensor object
- `files` - List of File objects from this channel
- `samples` - List of Sample objects from this channel

**Example:**

```python
# Get all piezoelectric channels
piezo_channels = db.session.query(db.Channel).filter(
    db.Channel.type_class == "piezoelectric"
).all()
```

### File

Stores audio file metadata and locations.

**Attributes:**
- `id` (Integer) - Primary key
- `filepath` (String) - Absolute path to file
- `filename` (String) - File name
- `extension` (String) - File extension (e.g., "wav")
- `sample_rate` (Integer) - Sample rate in Hz
- `start` (DateTime) - Recording start time
- `end` (DateTime) - Recording end time
- `duration` (Float) - Duration in seconds
- `channel_number` (Integer) - Channel number
- `channel_id` (Integer) - Foreign key to Channel
- `sensor_id` (Integer) - Foreign key to Sensor

**Relationships:**
- `channel` - Parent Channel object
- `sensor` - Parent Sensor object
- `samples` - List of Sample objects using this file

**Example:**

```python
# Get all WAV files
wav_files = db.session.query(db.File).filter(
    db.File.extension == "wav"
).all()

# Get files from a specific channel
channel_files = db.session.query(db.File).filter(
    db.File.channel_number == 0
).all()
```

### Sensor

Represents recording equipment.

**Attributes:**
- `id` (Integer) - Primary key
- `name` (String) - Sensor name (e.g., "A-SPIDS")
- `subname` (String) - Version or variant (e.g., "v1.0")
- `manufacturer` (String) - Manufacturer name
- `number_of_channels` (Integer) - Number of recording channels
- `type_class` (String) - Sensor type (e.g., "multi-sensor")

**Relationships:**
- `channels` - List of Channel objects
- `events` - List of Event objects
- `records` - List of Record objects
- `samples` - List of Sample objects
- `files` - List of File objects

**Example:**

```python
sensor = db.session.query(db.Sensor).filter(
    db.Sensor.name == "A-SPIDS"
).first()

print(f"{sensor.name} has {sensor.number_of_channels} channels")
```



## Extended Models (SPIDB-specific)

### Event

Represents a recording session with specific subject, material, and noise conditions.

**Attributes:**
- `id` (Integer) - Primary key
- `start` (DateTime) - Event start time
- `end` (DateTime) - Event end time
- `description` (String) - Text description
- `noise` (String) - Noise level/type (e.g., "Silence", "60dBA", "Ambient")
- `sensor_id` (Integer) - Foreign key to Sensor
- `subject_id` (Integer) - Foreign key to Subject
- `material_id` (Integer) - Foreign key to Material

**Relationships:**
- `sensor` - Parent Sensor object
- `subject` - Associated Subject object
- `material` - Associated Material object
- `records` - List of Record objects from this event
- `samples` - List of Sample objects from this event

**Example:**

```python
from datetime import datetime

# Create a new event
event = db.Event(
    start=datetime(2023, 5, 24, 19, 41, 19),
    end=datetime(2023, 5, 24, 19, 51, 19),
    description="Single cowpea beetle in rice",
    noise="Silence",
    sensor=sensor,
    subject=subject,
    material=material
)
db.session.add(event)
db.session.commit()

# Query events by noise level
silent_events = db.session.query(db.Event).filter(
    db.Event.noise == "Silence"
).all()
```

### Material

Represents substrate materials (grains, seeds, etc.).

**Attributes:**
- `id` (Integer) - Primary key
- `name` (String) - Material identifier
- `scientific_name` (String) - Taxonomic name (for organic materials)
- `common_name` (String) - Common name
- `density` (Float) - Material density in g/cm³

**Relationships:**
- `events` - List of Event objects with this material
- `records` - List of Record objects with this material
- `samples` - List of Sample objects with this material

**Example:**

```python
# Create a new material
material = db.Material(
    name="rice",
    scientific_name="Oryza sativa",
    common_name="Rice",
    density=1.4
)
db.session.add(material)
db.session.commit()

# Get all samples in rice
rice_samples = db.session.query(db.Sample).join(db.Material).filter(
    db.Material.name == "rice"
).all()
```

### Record

Represents fixed-duration segments of events (typically 60 seconds).

**Attributes:**
- `id` (Integer) - Primary key
- `start` (DateTime) - Record start time
- `end` (DateTime) - Record end time
- `event_id` (Integer) - Foreign key to parent Event
- `sensor_id` (Integer) - Foreign key to Sensor
- `subject_id` (Integer) - Foreign key to Subject
- `material_id` (Integer) - Foreign key to Material
- `noise` (String) - Noise level (inherited from event)

**Relationships:**
- `event` - Parent Event object
- `sensor` - Associated Sensor object
- `subject` - Associated Subject object
- `material` - Associated Material object
- `samples` - List of Sample objects from this record

**Example:**

```python
# Generate 60-second records from events
from spidb.build import generate_records

generate_records(db, duration=60, overwrite=True)

# Query records for a specific subject
cowpea_records = db.session.query(db.Record).join(db.Subject).filter(
    db.Subject.scientific_name == "Callosobruchus maculatus"
).all()
```

### Sample

Represents individual channel recordings, linking audio files to experimental context.

**Attributes:**
- `id` (Integer) - Primary key
- `datetime` (DateTime) - Sample timestamp
- `event_id` (Integer) - Foreign key to Event
- `record_id` (Integer) - Foreign key to Record
- `sensor_id` (Integer) - Foreign key to Sensor
- `channel_id` (Integer) - Foreign key to Channel
- `subject_id` (Integer) - Foreign key to Subject
- `material_id` (Integer) - Foreign key to Material
- `file_id` (Integer) - Foreign key to File
- `noise` (String) - Noise level

**Relationships:**
- `event` - Parent Event object
- `record` - Parent Record object
- `sensor` - Associated Sensor object
- `channel` - Associated Channel object
- `subject` - Associated Subject object
- `material` - Associated Material object
- `file` - Associated File object containing audio data

**Example:**

```python
# Generate samples from records
from spidb.build import generate_samples

generate_samples(db, overwrite=True)

# Complex query: Get all samples of adult beetles in rice from channel 0
samples = db.session.query(db.Sample).join(
    db.Subject
).join(
    db.Material
).join(
    db.Channel
).filter(
    db.Subject.life_stage == "Adult",
    db.Material.name == "rice",
    db.Channel.number == 0
).all()

# Access related data
for sample in samples[:5]:
    print(f"Subject: {sample.subject.common_name}")
    print(f"Material: {sample.material.common_name}")
    print(f"File: {sample.file.filename}")
    print(f"Duration: {sample.file.duration}s")
    print("---")
```

### Subject

Represents insect specimens with biological measurements.

**Attributes:**
- `id` (Integer) - Primary key
- `name` (String) - Descriptive identifier
- `scientific_name` (String) - Taxonomic binomial name
- `common_name` (String) - Common/vernacular name
- `life_stage` (String) - Development stage (e.g., "Adult", "Larva", "Pupa")
- `length` (Float) - Length in millimeters
- `width` (Float) - Width in millimeters
- `height` (Float) - Height in millimeters
- `weight` (Float) - Mass in grams
- `volume` (Float) - Volume in cubic centimeters
- `density` (Float) - Density in g/cm³

**Relationships:**
- `events` - List of Event objects with this subject
- `records` - List of Record objects with this subject
- `samples` - List of Sample objects with this subject

**Example:**

```python
# Create a new subject
subject = db.Subject(
    name="Cowpea Beetle Adult",
    scientific_name="Callosobruchus maculatus",
    common_name="Cowpea Beetle",
    life_stage="Adult",
    length=3.5,
    width=1.5,
    height=1.5,
    weight=0.003
)
db.session.add(subject)
db.session.commit()

# Query by life stage
adults = db.session.query(db.Subject).filter(
    db.Subject.life_stage == "Adult"
).all()
```

## Common Query Patterns

### Getting All Subjects and Their Materials

```python
from sqlalchemy import distinct

# Get all subject-material combinations
combinations = db.session.query(
    db.Subject.common_name,
    db.Material.common_name
).join(
    db.Sample
).join(
    db.Material
).distinct().all()

for subject, material in combinations:
    print(f"{subject} in {material}")
```

### Counting Samples by Life Stage

```python
from sqlalchemy import func

counts = db.session.query(
    db.Subject.life_stage,
    func.count(db.Sample.id)
).join(
    db.Sample
).group_by(
    db.Subject.life_stage
).all()

for stage, count in counts:
    print(f"{stage}: {count} samples")
```

### Getting Events with Duration

```python
# Get all events with their durations
events = db.session.query(db.Event).all()

for event in events:
    duration = (event.end - event.start).total_seconds()
    print(f"{event.description}: {duration}s")
```

## Model Relationships Diagram

```
┌─────────┐
│ Sensor  │
└────┬────┘
     │
     ├──────────┐
     │          │
┌────▼────┐ ┌──▼───┐
│ Channel │ │ Event│◄──┐
└────┬────┘ └──┬───┘   │
     │         │        │
┌────▼────┐   │    ┌───┴────┐  ┌──────────┐
│  File   │   │    │ Subject│  │ Material │
└────┬────┘   │    └───┬────┘  └────┬─────┘
     │        │        │             │
     │    ┌───▼────┐   │             │
     │    │ Record │◄──┴─────────────┘
     │    └───┬────┘
     │        │
     │   ┌────▼────┐
     └───► Sample  │
         └─────────┘
```

## See Also

- [Database Structure](database.md) - Complete database documentation
- [Usage Examples](usage.md) - Query examples and code snippets
- [CLI Guide](cli_guide.md) - Command-line interface reference