src/db/sql/Schema.sql

CREATE TABLE Topic
(
    ID   INTEGER PRIMARY KEY AUTOINCREMENT,
    Name TEXT NOT NULL UNIQUE CHECK (length(Name) <= 500)
);

INSERT INTO Topic (Name)
VALUES ('Unknown');

CREATE TABLE File
(
    ID               INTEGER PRIMARY KEY AUTOINCREMENT,
    Name             TEXT    NOT NULL,
    TopicID          INTEGER,
    Extension        TEXT,
    Path             TEXT,
    Rate             INTEGER,
    MinFreq          REAL,
    MaxFreq          REAL,
    BitDepth         INTEGER,
    Channels         INTEGER,
    Duration         REAL,
    RMSLoudness      REAL,
    ZeroCrossingRate REAL,
    SpectralCentroid REAL,
    EQ_20_250_Hz     REAL,
    EQ_250_2000_Hz   REAL,
    EQ_2000_6000_Hz  REAL,
    EQ_6000_20000_Hz REAL,
    MFCC_1           REAL,
    MFCC_2           REAL,
    MFCC_3           REAL,
    MFCC_4           REAL,
    MFCC_5           REAL,
    MFCC_6           REAL,
    MFCC_7           REAL,
    MFCC_8           REAL,
    MFCC_9           REAL,
    MFCC_10          REAL,
    MFCC_11          REAL,
    MFCC_12          REAL,
    MFCC_13          REAL,
    Summary          TEXT    NOT NULL,
    Conflict         INTEGER NOT NULL CHECK (Conflict IN (0, 1)),
    Silence          REAL    NOT NULL,

    FOREIGN KEY (TopicID) REFERENCES Topic (ID)
);

CREATE TABLE Utterance
(
    ID        INTEGER PRIMARY KEY AUTOINCREMENT,
    FileID    INTEGER                                                       NOT NULL,
    Speaker   TEXT CHECK (Speaker IN ('Customer', 'CSR'))                   NOT NULL,
    Sequence  INTEGER                                                       NOT NULL,
    StartTime REAL                                                          NOT NULL,
    EndTime   REAL                                                          NOT NULL,
    Content   TEXT                                                          NOT NULL,
    Sentiment TEXT CHECK (Sentiment IN ('Neutral', 'Positive', 'Negative')) NOT NULL,
    Profane   INTEGER                                                       NOT NULL CHECK (Profane IN (0, 1)),
    FOREIGN KEY (FileID) REFERENCES File (ID)
);