Skip to content

Commit 83dc8a7

Browse files
happycubehappycube
committed
first rough pass at sqlite, ld-analyze can read this if there aren't dropouts...
1 parent 86c0030 commit 83dc8a7

File tree

1 file changed

+232
-2
lines changed

1 file changed

+232
-2
lines changed

‎lddecode/core.py‎

Lines changed: 232 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,12 +1,15 @@
11
import copy
22
import itertools
3+
import os
34
import platform
5+
import sqlite3
46
import sys
57
import threading
68
import time
79
import types
810

911
from queue import Queue
12+
from textwrap import dedent
1013

1114
# standard numeric/scientific libraries
1215
import numpy as np
@@ -3442,12 +3445,21 @@ def __init__(
34423445
self.ac3_processes, self.outfile_ac3 = ac3_pipe(fname_out + ".ac3")
34433446
self.do_rftbc = True
34443447

3448+
if os.path.exists(fname_out + '.tbc.db'):
3449+
os.unlink(fname_out + '.tbc.db')
3450+
self.dbconn = sqlite3.connect(fname_out + '.tbc.db')
3451+
self.create_db_schema()
3452+
34453453
self.pipe_rftbc = extra_options.get("pipe_RF_TBC", None)
34463454
if self.pipe_rftbc:
34473455
self.do_rftbc = True
34483456

34493457
self.fname_out = fname_out
34503458

3459+
# TODO: set this uniquely?
3460+
self.wrote_metadata = False
3461+
self.capture_id = 1
3462+
34513463
self.firstfield = None # In frame output mode, the first field goes here
34523464

34533465
self.system = system
@@ -3540,6 +3552,134 @@ def close(self):
35403552

35413553
self.print_stats()
35423554

3555+
def create_db_schema(self):
3556+
cur = self.dbconn.cursor()
3557+
3558+
# Enforce foreign key constraints (SQLite default is usually OFF)
3559+
# cur.execute("PRAGMA foreign_keys = ON;")
3560+
3561+
cur.executescript(dedent('''\
3562+
PRAGMA user_version = 1;
3563+
3564+
CREATE TABLE capture (
3565+
capture_id INTEGER PRIMARY KEY,
3566+
system TEXT NOT NULL CHECK (system IN ('NTSC','PAL','PAL_M')),
3567+
decoder TEXT NOT NULL CHECK (decoder IN ('ld-decode','vhs-decode')),
3568+
git_branch TEXT,
3569+
git_commit TEXT,
3570+
video_sample_rate REAL,
3571+
active_video_start INTEGER,
3572+
active_video_end INTEGER,
3573+
field_width INTEGER,
3574+
field_height INTEGER,
3575+
number_of_sequential_fields INTEGER,
3576+
colour_burst_start INTEGER,
3577+
colour_burst_end INTEGER,
3578+
is_mapped INTEGER CHECK (is_mapped IN (0,1)),
3579+
is_subcarrier_locked INTEGER CHECK (is_subcarrier_locked IN (0,1)),
3580+
is_widescreen INTEGER CHECK (is_widescreen IN (0,1)),
3581+
white_16b_ire INTEGER,
3582+
black_16b_ire INTEGER,
3583+
capture_notes TEXT
3584+
);
3585+
3586+
CREATE TABLE pcm_audio_parameters (
3587+
capture_id INTEGER PRIMARY KEY REFERENCES capture(capture_id) ON DELETE CASCADE,
3588+
bits INTEGER,
3589+
is_signed INTEGER CHECK (is_signed IN (0,1)),
3590+
is_little_endian INTEGER CHECK (is_little_endian IN (0,1)),
3591+
sample_rate REAL
3592+
);
3593+
3594+
CREATE TABLE field_record (
3595+
capture_id INTEGER NOT NULL REFERENCES capture(capture_id) ON DELETE CASCADE,
3596+
field_id INTEGER NOT NULL,
3597+
audio_samples INTEGER,
3598+
decode_faults INTEGER,
3599+
disk_loc REAL,
3600+
efm_t_values INTEGER,
3601+
field_phase_id INTEGER,
3602+
file_loc INTEGER,
3603+
is_first_field INTEGER CHECK (is_first_field IN (0,1)),
3604+
median_burst_ire REAL,
3605+
pad INTEGER CHECK (pad IN (0,1)),
3606+
sync_conf INTEGER,
3607+
ntsc_is_fm_code_data_valid INTEGER CHECK (ntsc_is_fm_code_data_valid IN (0,1)),
3608+
ntsc_fm_code_data INTEGER,
3609+
ntsc_field_flag INTEGER CHECK (ntsc_field_flag IN (0,1)),
3610+
ntsc_is_video_id_data_valid INTEGER CHECK (ntsc_is_video_id_data_valid IN (0,1)),
3611+
ntsc_video_id_data INTEGER,
3612+
ntsc_white_flag INTEGER CHECK (ntsc_white_flag IN (0,1)),
3613+
PRIMARY KEY (capture_id, field_id)
3614+
);
3615+
3616+
CREATE TABLE vits_metrics (
3617+
capture_id INTEGER NOT NULL,
3618+
field_id INTEGER NOT NULL,
3619+
b_psnr REAL,
3620+
w_snr REAL,
3621+
FOREIGN KEY (capture_id, field_id)
3622+
REFERENCES field_record(capture_id, field_id) ON DELETE CASCADE,
3623+
PRIMARY KEY (capture_id, field_id)
3624+
);
3625+
3626+
CREATE TABLE vbi (
3627+
capture_id INTEGER NOT NULL,
3628+
field_id INTEGER NOT NULL,
3629+
vbi0 INTEGER NOT NULL,
3630+
vbi1 INTEGER NOT NULL,
3631+
vbi2 INTEGER NOT NULL,
3632+
FOREIGN KEY (capture_id, field_id)
3633+
REFERENCES field_record(capture_id, field_id) ON DELETE CASCADE,
3634+
PRIMARY KEY (capture_id, field_id)
3635+
);
3636+
3637+
CREATE TABLE drop_outs (
3638+
capture_id INTEGER NOT NULL,
3639+
field_id INTEGER NOT NULL,
3640+
field_line INTEGER NOT NULL,
3641+
startx INTEGER NOT NULL,
3642+
endx INTEGER NOT NULL,
3643+
FOREIGN KEY (capture_id, field_id)
3644+
REFERENCES field_record(capture_id, field_id) ON DELETE CASCADE,
3645+
PRIMARY KEY (capture_id, field_id, field_line, startx, endx)
3646+
);
3647+
3648+
CREATE TABLE vitc (
3649+
capture_id INTEGER NOT NULL,
3650+
field_id INTEGER NOT NULL,
3651+
vitc0 INTEGER NOT NULL,
3652+
vitc1 INTEGER NOT NULL,
3653+
vitc2 INTEGER NOT NULL,
3654+
vitc3 INTEGER NOT NULL,
3655+
vitc4 INTEGER NOT NULL,
3656+
vitc5 INTEGER NOT NULL,
3657+
vitc6 INTEGER NOT NULL,
3658+
vitc7 INTEGER NOT NULL,
3659+
FOREIGN KEY (capture_id, field_id)
3660+
REFERENCES field_record(capture_id, field_id) ON DELETE CASCADE,
3661+
PRIMARY KEY (capture_id, field_id)
3662+
);
3663+
3664+
CREATE TABLE closed_caption (
3665+
capture_id INTEGER NOT NULL,
3666+
field_id INTEGER NOT NULL,
3667+
data0 INTEGER,
3668+
data1 INTEGER,
3669+
FOREIGN KEY (capture_id, field_id)
3670+
REFERENCES field_record(capture_id, field_id) ON DELETE CASCADE,
3671+
PRIMARY KEY (capture_id, field_id)
3672+
);
3673+
'''))
3674+
3675+
self.dbconn.commit()
3676+
3677+
cur.execute("SELECT name FROM sqlite_master WHERE type='table';")
3678+
tables = cur.fetchall()
3679+
#print([table[0] for table in tables])
3680+
3681+
cur.close()
3682+
35433683
def roughseek(self, location, isField=True):
35443684
self.prevPhaseID = None
35453685

@@ -3635,6 +3775,51 @@ def writeout(self, dataset):
36353775

36363776
self.fieldinfo.append(fi)
36373777

3778+
c_id = self.capture_id
3779+
f_id = fi['seqNo'] - 1
3780+
print(c_id, f_id)
3781+
3782+
# Insert parent record into 'field_record'
3783+
# We cast booleans to int because of the CHECK (val IN (0,1)) constraint
3784+
self.dbconn.execute('''
3785+
INSERT INTO field_record (
3786+
capture_id, field_id, is_first_field, sync_conf, disk_loc,
3787+
file_loc, median_burst_ire, field_phase_id, decode_faults,
3788+
audio_samples, efm_t_values
3789+
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''',
3790+
(c_id, f_id, int(fi['isFirstField']), fi['syncConf'], fi['diskLoc'],
3791+
fi['fileLoc'], fi['medianBurstIRE'], fi['fieldPhaseID'], fi['decodeFaults'],
3792+
fi['audioSamples'], fi['efmTValues']))
3793+
3794+
w_snr = fi['vitsMetrics'].get('wSNR', -1)
3795+
b_psnr = fi['vitsMetrics'].get('bPSNR', -1)
3796+
3797+
self.dbconn.execute('''
3798+
INSERT INTO vits_metrics (
3799+
capture_id, field_id, w_snr, b_psnr
3800+
) VALUES (?, ?, ?, ?)''',
3801+
(c_id, f_id, w_snr, b_psnr))
3802+
3803+
# Insert dropouts (if any) into 'drop_outs'
3804+
if self.doDOD and fi.get("dropOuts"):
3805+
dropout_lines = fi["dropOuts"]["fieldLine"]
3806+
dropout_starts = fi["dropOuts"]["startx"]
3807+
dropout_ends = fi["dropOuts"]["endx"]
3808+
3809+
# Use executemany for cleaner/faster insertion of multiple rows
3810+
dropout_data = [
3811+
(c_id, f_id, line, start, end)
3812+
for line, start, end in zip(dropout_lines, dropout_starts, dropout_ends)
3813+
]
3814+
3815+
self.dbconn.executemany('''
3816+
INSERT INTO drop_outs (
3817+
capture_id, field_id, field_line, startx, endx
3818+
) VALUES (?, ?, ?, ?, ?)''',
3819+
dropout_data)
3820+
3821+
self.dbconn.commit()
3822+
36383823
self.outfile_video.write(picture)
36393824
self.fields_written += 1
36403825

@@ -4322,14 +4507,17 @@ def seek(self, startframe, target):
43224507

43234508
def build_json(self):
43244509
""" build up the JSON structure for file output. """
4325-
jout = {}
4326-
jout["pcmAudioParameters"] = {
4510+
pcmAudioParameters = {
43274511
"bits": 16,
43284512
"isLittleEndian": True,
43294513
"isSigned": True,
43304514
"sampleRate": self.analog_audio,
43314515
}
43324516

4517+
jout = {}
4518+
4519+
jout["pcmAudioParameters"] = pcmAudioParameters
4520+
43334521
vp = {}
43344522

43354523
vp["numberOfSequentialFields"] = len(self.fieldinfo)
@@ -4374,4 +4562,46 @@ def build_json(self):
43744562

43754563
jout["videoParameters"] = vp
43764564

4565+
if not self.wrote_metadata:
4566+
self.wrote_metadata = True
4567+
4568+
cursor = self.dbconn.cursor()
4569+
decoder_val = vp.get('decoder', 'ld-decode')
4570+
4571+
cursor.execute("""
4572+
INSERT INTO capture (
4573+
system, decoder, git_branch, git_commit,
4574+
video_sample_rate, active_video_start, active_video_end,
4575+
field_width, field_height, number_of_sequential_fields,
4576+
colour_burst_start, colour_burst_end,
4577+
white_16b_ire, black_16b_ire
4578+
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
4579+
""", (
4580+
vp["system"], decoder_val, vp["gitBranch"], vp["gitCommit"],
4581+
vp["sampleRate"], vp["activeVideoStart"], vp["activeVideoEnd"],
4582+
vp["fieldWidth"], vp["fieldHeight"], vp["numberOfSequentialFields"],
4583+
vp["colourBurstStart"], vp["colourBurstEnd"],
4584+
vp["white16bIre"], vp["black16bIre"]
4585+
))
4586+
4587+
self.capture_id = cursor.lastrowid
4588+
4589+
# 2. Insert into 'pcm_audio_parameters'
4590+
# Now requires capture_id as a Foreign Key.
4591+
# Booleans are cast to int() to satisfy CHECK(x IN (0,1))
4592+
cursor.execute("""
4593+
INSERT INTO pcm_audio_parameters (
4594+
capture_id, bits, is_little_endian, is_signed, sample_rate
4595+
) VALUES (?, ?, ?, ?, ?)
4596+
""", (
4597+
self.capture_id,
4598+
pcmAudioParameters["bits"],
4599+
int(pcmAudioParameters["isLittleEndian"]),
4600+
int(pcmAudioParameters["isSigned"]),
4601+
pcmAudioParameters["sampleRate"]
4602+
))
4603+
4604+
self.dbconn.commit()
4605+
cursor.close()
4606+
43774607
return jout

0 commit comments

Comments
 (0)