SQL

CREATE TABLE audio_transcriptions (
  id INT,
  audio_chunk_id INT,
  offset_index INT,
  timestamp NUM,
  transcription TEXT,
  device TEXT,
  is_input_device NUM,
  speaker_id INT,
  transcription_engine TEXT,
  start_time REAL,
  end_time REAL,
  text_length INT,
  sync_id TEXT,
  synced_at NUM,
  redacted_at INT
)

Columns

Column Data type Allow null Primary key Actions
id INT ✓ read-only
audio_chunk_id INT ✓ read-only
offset_index INT ✓ read-only
timestamp NUM ✓ read-only
transcription TEXT ✓ read-only
device TEXT ✓ read-only
is_input_device NUM ✓ read-only
speaker_id INT ✓ read-only
transcription_engine TEXT ✓ read-only
start_time REAL ✓ read-only
end_time REAL ✓ read-only
text_length INT ✓ read-only
sync_id TEXT ✓ read-only
synced_at NUM ✓ read-only
redacted_at INT ✓ read-only

Indexes

Name Columns Unique SQL Drop?
idx_audio_trans_chunk_id audio_chunk_id SQL 
CREATE INDEX idx_audio_trans_chunk_id
ON audio_transcriptions(audio_chunk_id)
read-only
idx_audio_trans_timestamp timestamp SQL 
CREATE INDEX idx_audio_trans_timestamp
ON audio_transcriptions(timestamp)
read-only
idx_audio_transcriptions_audio_chunk_id audio_chunk_id SQL 
CREATE INDEX idx_audio_transcriptions_audio_chunk_id
ON audio_transcriptions(audio_chunk_id)
read-only
idx_audio_transcriptions_audio_chunk_id_timestamp
  • audio_chunk_id
  • timestamp
 SQL 
CREATE INDEX idx_audio_transcriptions_audio_chunk_id_timestamp
ON audio_transcriptions(audio_chunk_id, timestamp)
read-only
idx_audio_transcriptions_length text_length SQL 
CREATE INDEX idx_audio_transcriptions_length
ON audio_transcriptions (text_length)
read-only
idx_audio_transcriptions_redacted_at redacted_at SQL 
CREATE INDEX idx_audio_transcriptions_redacted_at
ON audio_transcriptions(redacted_at)
read-only
idx_audio_transcriptions_sync_id sync_id SQL 
CREATE INDEX idx_audio_transcriptions_sync_id
ON audio_transcriptions(sync_id) WHERE sync_id IS NOT NULL
read-only
idx_audio_transcriptions_timestamp timestamp SQL 
CREATE INDEX idx_audio_transcriptions_timestamp
ON audio_transcriptions(timestamp)
read-only
idx_audio_transcriptions_transcription transcription SQL 
CREATE INDEX idx_audio_transcriptions_transcription
ON audio_transcriptions(transcription)
read-only

Triggers

Name SQL Drop?
audio_transcriptions_ai SQL 
CREATE TRIGGER audio_transcriptions_ai AFTER INSERT ON audio_transcriptions
WHEN NEW.transcription IS NOT NULL AND NEW.transcription != ''
BEGIN
    INSERT INTO audio_transcriptions_fts(rowid, transcription, device, speaker_id)
    VALUES (NEW.id, NEW.transcription, COALESCE(NEW.device, ''), NEW.speaker_id);
END
read-only
audio_transcriptions_delete SQL 
CREATE TRIGGER audio_transcriptions_delete AFTER DELETE ON audio_transcriptions
WHEN OLD.transcription IS NOT NULL AND OLD.transcription != ''
BEGIN
    INSERT INTO audio_transcriptions_fts(audio_transcriptions_fts, rowid, transcription, device, speaker_id)
    VALUES ('delete', OLD.id, OLD.transcription, COALESCE(OLD.device, ''), OLD.speaker_id);
END
read-only
audio_transcriptions_update SQL 
CREATE TRIGGER audio_transcriptions_update AFTER UPDATE ON audio_transcriptions
WHEN OLD.transcription IS NOT NULL AND OLD.transcription != ''
BEGIN
    INSERT INTO audio_transcriptions_fts(audio_transcriptions_fts, rowid, transcription, device, speaker_id)
    VALUES ('delete', OLD.id, OLD.transcription, COALESCE(OLD.device, ''), OLD.speaker_id);
    INSERT INTO audio_transcriptions_fts(rowid, transcription, device, speaker_id)
    VALUES (NEW.id, COALESCE(NEW.transcription, ''), COALESCE(NEW.device, ''), NEW.speaker_id);
END
read-only