Data Models¶

SPIDB extends the SONICDB framework with specialized models for stored product insect research. All models use SQLAlchemy ORM and support SQLite, MySQL, and PostgreSQL databases.

SPIDB Schema

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:

# 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:

# 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:

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:

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:

# 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:

# 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:

# 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:

# 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¶

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¶

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¶

# 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  │
         └─────────┘

Data Models¶

Core Models (from SONICDB)¶

Channel¶

File¶

Sensor¶

Extended Models (SPIDB-specific)¶

Event¶

Material¶

Record¶

Sample¶

Subject¶

Common Query Patterns¶

Getting All Subjects and Their Materials¶

Counting Samples by Life Stage¶

Getting Events with Duration¶

Model Relationships Diagram¶

See Also¶