Trunk-Recorder Convert JSON files into SQLite database. (Tested with PHP8 Homebrew on M1 Mac Mini).

catalog.php

#!/opt/homebrew/bin/php
<?php
namespace MimoSDR;
use \DateTime as DateTime;
use \Generator as Generator;
use \DirectoryIterator as DirectoryIterator;
use \UnexpectedValueException as UnexpectedValueException;
use \PDO as PDO;

define('SQL_CREATE_TABLE', <<<SQL
DROP TABLE IF EXISTS sdr_sites;
DROP TABLE IF EXISTS p25;
DROP TABLE IF EXISTS p25_talkgroups;
DROP TABLE IF EXISTS p25_units;
DROP TABLE IF EXISTS audio;
DROP TABLE IF EXISTS audio_sources;
DROP TABLE IF EXISTS audio_frequencies;

CREATE TABLE sdr_sites (
    siteId INTEGER PRIMARY KEY,
    lat REAL,
    lng REAL,
    name TEXT,
    location TEXT
);
INSERT INTO sdr_sites (lat, lng, name, location) VALUES (40.725468, -73.499046, 'NCPD', '299 Hicksville Rd,
Bethpage, NY 11714');
INSERT INTO sdr_sites (lat, lng, name, location) VALUES (40.746600, -73.636288, 'NCFC', '1194 Prospect Ave,
Westbury, NY 11590');
INSERT INTO sdr_sites (lat, lng, name, location) VALUES (40.808251, -73.180623, 'SBUH', '3122 Express Drive S,
Islandia, NY 11749');
INSERT INTO sdr_sites (lat, lng, name, location) VALUES (40.727732, -73.559855, 'NUMC', '2201 Hempstead Tpke,
East Meadow, NY 11554');

CREATE TABLE p25 (
    p25Id INTEGER PRIMARY KEY,
    WACN TEXT NOT NULL,
    systemId TEXT NOT NULL,
    nameShort TEXT NOT NULL,
    nameLong TEXT NOT NULL
);
INSERT INTO p25 (WACN, systemId, nameShort, nameLong) VALUES ('BEE00','1AE','NCPD','Nassau County Police Department');
INSERT INTO p25 (WACN, systemId, nameShort, nameLong) VALUES ('BEE00','3CE','SCPD','Suffolk County Police Department');

CREATE TABLE p25_talkgroups (
    talkgroupId INTEGER PRIMARY KEY,
    p25Id INTEGER NOT NULL,
    tgId INTEGER NOT NULL,
    mode TEXT NOT NULL,
    alphaTag TEXT NOT NULL,
    description TEXT NOT NULL,
    tag TEXT NOT NULL,
    'group' TEXT NOT NULL,
    priority INTEGER,
    FOREIGN KEY(p25Id) REFERENCES p25(p25Id)
);

CREATE TABLE p25_units (
    unitId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    p25Id INTEGER NOT NULL,
    src INTEGER NOT NULL,
    alias TEXT NOT NULL,
    startTime DATETIME NOT NULL,
    endTime DATETIME,
    FOREIGN KEY(p25Id) REFERENCES p25(p25Id)
);

CREATE TABLE audio (
    audioId INTEGER PRIMARY KEY,
    siteId INTEGER,
    p25Id INTEGER,
    tgId INTEGER,
    freq INTEGER,
    timeStart DATETIME,
    timeStop DATETIME,
    emergency BOOL,
    fileAudio TEXT,
    fileJSON TEXT,
    fileSize INTEGER,
    FOREIGN KEY(p25Id) REFERENCES p25(p25Id)
    FOREIGN KEY(siteId) REFERENCES sdr_sites(siteId)
);

CREATE TABLE audio_sources (
    sourceId INTEGER PRIMARY KEY,
    audioId INTEGER NOT NULL,
    src INTEGER,
    time DATETIME,
    pos REAL,
    emergency BOOL,
    signal_system TEXT,
    tag TEXT,
    FOREIGN KEY(audioId) REFERENCES audio(audioId)
);

CREATE TABLE audio_frequencies (
    frequencyId INTEGER PRIMARY KEY,
    audioId INTEGER NOT NULL,
    freq INTEGER,
    time DATETIME,
    pos REAL,
    len INTEGER,
    error_count INTEGER,
    spike_count INTEGER,
    FOREIGN KEY(audioId) REFERENCES audio(audioId)
);
SQL
);
define('SQL_INSERT_AUDIO', 'INSERT INTO audio (siteId, p25Id, tgId, freq, timeStart, timeStop, emergency, fileAudio, fileJSON, fileSize) VALUES (:siteId, :p25Id, :tgId, :freq, :timeStart, :timeStop, :emergency, :fileAudio, :fileJSON, :fileSize);');
define('SQL_INSERT_SOURCE', 'INSERT INTO audio_sources (audioId, src, time, pos, emergency, signal_system, tag) VALUES (:audioId, :src, :time, :pos, :emergency, :signal_system, :tag);');
define('SQL_INSERT_FREQUENCIES', 'INSERT INTO audio_frequencies (audioId, freq, time, pos, len, error_count, spike_count) VALUES (:audioId, :freq, :time, :pos, :len, :error_count, :spike_count);');

$MimoSDR = new PDO('sqlite:./MimoSDR.db');
$MimoSDR->exec(SQL_CREATE_TABLE);
echo SQL_CREATE_TABLE;

function files(?DateTime $timeStart = NULL, ?DateTime $timeEnd = NULL)
{
    $timeStart = $timeStart ?? (new DateTime())->setTime(0, 0, 0, 0);
    $timeEnd = $timeEnd ?? (new DateTime())->setTime(23, 59, 59, 999999);

    for ($date = $timeStart; $timeStart <= $timeEnd; $date->modify('+1 Day'))
    {
        $day = $date->format('Y/n/j');
        try 
        {
            $path = new DirectoryIterator("./{$day}/");

            foreach ($path as $file)
            {
                if ($file == '.' OR $file == '..')
                {   # The current dir `.` and the previous dir `..` can be skipped.
                    continue;
                }

                $EXT = '.' . $file->getExtension();

                $baseName = $file->getBasename($EXT);
                $fullPath = __DIR__ . "/$day/$baseName";

                if ($EXT != '.wav' OR !file_exists("$fullPath.json"))
                {   # If it's not a wave file or the json file doesn't exist, we skip over it.
                    continue;
                }

                [$TGID, $UNIX] = preg_split('/[-_]/', $baseName);

                if (NULL === ($JSON = json_decode(file_get_contents("$fullPath.json"), TRUE) ?? NULL))
                {   # JSON file was empty.
                    continue;
                }

                $MHz = ($JSON['freqList'][0]['freq'] ?? $JSON['freq']) / 1000000;

                yield [
                    'path' => $day . '/',
                    'file' => $file->getBasename($EXT),
                    'size' => $file->getSize(),
                    'TGID' => $TGID,
                    'UNIX' => $UNIX,
                    'MHz' => $MHz,
                    'JSON' => $JSON,
                ];
            }
        } catch (UnexpectedValueException $e) {
            trigger_error('No directory found for that date.');
        }
    }
}

$dateStart = new \DateTime('2020-01-01 00:00:00.000000');
$dateEnd = new \DateTime();

foreach (files($dateStart, $dateEnd) as $file)
{
    echo "${file['path']}${file['file']}" . PHP_EOL;

    // What SDR Captured this information?
    $siteId = match (TRUE) {
        $file['MHz'] >= 136 AND $file['MHz'] <= 174 => 1, # VHF
        $file['MHz'] >= 400 AND $file['MHz'] <= 480 => 2, # UHF1
        $file['MHz'] >= 480 AND $file['MHz'] <= 512 => 4, # UHF2
        $file['MHz'] >= 700 AND $file['MHz'] <= 999 => 3, # 800
        default => NULL,
    };

    // What P25 system (if any) was used?
    $p25Id = match ($siteId) {
        4 => 1,
        3 => 2,
        default => NULL,
    };

    insert($MimoSDR, "${file['path']}${file['file']}.wav", "${file['path']}${file['file']}.json", $file['size'], $siteId, $file['JSON'], $p25Id);
}

function insert(\PDO $sql, string $filePathWAV, string $filePathJSON, int $fileSize, int $siteId, ?array $json, ?int $p25Id = NULL)
{
    $insert = $sql->prepare(SQL_INSERT_AUDIO);
    $insert->execute([
        ':siteId' => $siteId,
        ':p25Id' => $p25Id,
        ':tgId' => $json['talkgroup'],
        ':freq' => $json['freq'],
        ':timeStart' => $json['start_time'],
        ':timeStop' => $json['stop_time'],
        ':emergency' => $json['emergency'],
        ':fileAudio' => $filePathWAV,
        ':fileJSON' => $filePathJSON,
        ':fileSize' => $fileSize,
    ]);
    $audioId = $sql->lastInsertId();

    foreach ($json['srcList'] as $source)
    {
        $insert = $sql->prepare(SQL_INSERT_SOURCE);
        $insert->execute([
            ':audioId' => $audioId,
            ':src' => $source['src'],
            ':time' => $source['time'],
            ':pos' => $source['pos'],
            ':emergency' => $source['emergency'] ?? NULL,
            ':signal_system' => $source['signal_system'] ?? NULL,
            ':tag' => $source['tag'] ?? NULL
        ]);
    }

    foreach ($json['freqList'] as $frequency)
    {
        $insert = $sql->prepare(SQL_INSERT_FREQUENCIES);
        $insert->execute([
            ':audioId' => $audioId,
            ':freq' => $frequency['freq'],
            ':time' => $frequency['time'],
            ':pos' => $frequency['pos'],
            ':len' => $frequency['len'],
            ':error_count' => $frequency['error_count'],
            ':spike_count' => $frequency['spike_count'],
        ]);
    }
}