diff options
Diffstat (limited to 'app')
10 files changed, 1283 insertions, 2 deletions
diff --git a/app/schemas/org.terst.listen.data.AppDatabase/19.json b/app/schemas/org.terst.listen.data.AppDatabase/19.json new file mode 100644 index 0000000..376833d --- /dev/null +++ b/app/schemas/org.terst.listen.data.AppDatabase/19.json @@ -0,0 +1,1033 @@ +{ + "formatVersion": 1, + "database": { + "version": 19, + "identityHash": "485619e60522c24cfe94990fdfcc6903", + "entities": [ + { + "tableName": "subscriptions", + "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` TEXT NOT NULL, `feedUrl` TEXT NOT NULL, `kind` TEXT NOT NULL, `title` TEXT NOT NULL, `description` TEXT, `author` TEXT, `imageUrl` TEXT, `link` TEXT, `addedAtMs` INTEGER NOT NULL, `etag` TEXT, `lastModified` TEXT, `lastFetchedAtMs` INTEGER, `lastError` TEXT, `playbackSpeed` REAL, `skipIntroMs` INTEGER NOT NULL, `skipOutroMs` INTEGER NOT NULL, `dripEnabled` INTEGER NOT NULL DEFAULT 0, `dripIntervalMs` INTEGER NOT NULL DEFAULT 86400000, `dripCount` INTEGER NOT NULL DEFAULT 1, `dripOldestFirst` INTEGER NOT NULL DEFAULT 1, `dripLastReleaseAtMs` INTEGER, `skipAdChapters` INTEGER NOT NULL DEFAULT 0, `midrollPendingEpisodeId` TEXT, `bedtime` INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(`id`))", + "fields": [ + { + "fieldPath": "id", + "columnName": "id", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "feedUrl", + "columnName": "feedUrl", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "kind", + "columnName": "kind", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "title", + "columnName": "title", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "description", + "columnName": "description", + "affinity": "TEXT" + }, + { + "fieldPath": "author", + "columnName": "author", + "affinity": "TEXT" + }, + { + "fieldPath": "imageUrl", + "columnName": "imageUrl", + "affinity": "TEXT" + }, + { + "fieldPath": "link", + "columnName": "link", + "affinity": "TEXT" + }, + { + "fieldPath": "addedAtMs", + "columnName": "addedAtMs", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "etag", + "columnName": "etag", + "affinity": "TEXT" + }, + { + "fieldPath": "lastModified", + "columnName": "lastModified", + "affinity": "TEXT" + }, + { + "fieldPath": "lastFetchedAtMs", + "columnName": "lastFetchedAtMs", + "affinity": "INTEGER" + }, + { + "fieldPath": "lastError", + "columnName": "lastError", + "affinity": "TEXT" + }, + { + "fieldPath": "playbackSpeed", + "columnName": "playbackSpeed", + "affinity": "REAL" + }, + { + "fieldPath": "skipIntroMs", + "columnName": "skipIntroMs", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "skipOutroMs", + "columnName": "skipOutroMs", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "dripEnabled", + "columnName": "dripEnabled", + "affinity": "INTEGER", + "notNull": true, + "defaultValue": "0" + }, + { + "fieldPath": "dripIntervalMs", + "columnName": "dripIntervalMs", + "affinity": "INTEGER", + "notNull": true, + "defaultValue": "86400000" + }, + { + "fieldPath": "dripCount", + "columnName": "dripCount", + "affinity": "INTEGER", + "notNull": true, + "defaultValue": "1" + }, + { + "fieldPath": "dripOldestFirst", + "columnName": "dripOldestFirst", + "affinity": "INTEGER", + "notNull": true, + "defaultValue": "1" + }, + { + "fieldPath": "dripLastReleaseAtMs", + "columnName": "dripLastReleaseAtMs", + "affinity": "INTEGER" + }, + { + "fieldPath": "skipAdChapters", + "columnName": "skipAdChapters", + "affinity": "INTEGER", + "notNull": true, + "defaultValue": "0" + }, + { + "fieldPath": "midrollPendingEpisodeId", + "columnName": "midrollPendingEpisodeId", + "affinity": "TEXT" + }, + { + "fieldPath": "bedtime", + "columnName": "bedtime", + "affinity": "INTEGER", + "notNull": true, + "defaultValue": "0" + } + ], + "primaryKey": { + "autoGenerate": false, + "columnNames": [ + "id" + ] + }, + "indices": [ + { + "name": "index_subscriptions_feedUrl", + "unique": true, + "columnNames": [ + "feedUrl" + ], + "orders": [], + "createSql": "CREATE UNIQUE INDEX IF NOT EXISTS `index_subscriptions_feedUrl` ON `${TABLE_NAME}` (`feedUrl`)" + } + ] + }, + { + "tableName": "episodes", + "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` TEXT NOT NULL, `subscriptionId` TEXT NOT NULL, `guid` TEXT NOT NULL, `title` TEXT NOT NULL, `description` TEXT, `mediaUrl` TEXT NOT NULL, `mediaType` TEXT NOT NULL, `mediaLengthBytes` INTEGER, `durationMs` INTEGER, `publishedAtMs` INTEGER, `seasonNumber` INTEGER, `episodeNumber` INTEGER, `imageUrl` TEXT, `chaptersUrl` TEXT, `discoveredAtMs` INTEGER NOT NULL, `positionMs` INTEGER NOT NULL, `playedAtMs` INTEGER, `actualDurationMs` INTEGER, `downloadState` TEXT NOT NULL, `localPath` TEXT, `downloadedAtMs` INTEGER, `audioFromServer` INTEGER NOT NULL DEFAULT 0, `dripReleasedAtMs` INTEGER, `appliedRuleId` TEXT, `appliedRuleReason` TEXT, `speedOverride` REAL, `midrollCheckedAtMs` INTEGER, `chaptersJson` TEXT, `detectedIntroMs` INTEGER, `detectedOutroMs` INTEGER, PRIMARY KEY(`id`), FOREIGN KEY(`subscriptionId`) REFERENCES `subscriptions`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )", + "fields": [ + { + "fieldPath": "id", + "columnName": "id", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "subscriptionId", + "columnName": "subscriptionId", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "guid", + "columnName": "guid", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "title", + "columnName": "title", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "description", + "columnName": "description", + "affinity": "TEXT" + }, + { + "fieldPath": "mediaUrl", + "columnName": "mediaUrl", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "mediaType", + "columnName": "mediaType", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "mediaLengthBytes", + "columnName": "mediaLengthBytes", + "affinity": "INTEGER" + }, + { + "fieldPath": "durationMs", + "columnName": "durationMs", + "affinity": "INTEGER" + }, + { + "fieldPath": "publishedAtMs", + "columnName": "publishedAtMs", + "affinity": "INTEGER" + }, + { + "fieldPath": "seasonNumber", + "columnName": "seasonNumber", + "affinity": "INTEGER" + }, + { + "fieldPath": "episodeNumber", + "columnName": "episodeNumber", + "affinity": "INTEGER" + }, + { + "fieldPath": "imageUrl", + "columnName": "imageUrl", + "affinity": "TEXT" + }, + { + "fieldPath": "chaptersUrl", + "columnName": "chaptersUrl", + "affinity": "TEXT" + }, + { + "fieldPath": "discoveredAtMs", + "columnName": "discoveredAtMs", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "positionMs", + "columnName": "positionMs", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "playedAtMs", + "columnName": "playedAtMs", + "affinity": "INTEGER" + }, + { + "fieldPath": "actualDurationMs", + "columnName": "actualDurationMs", + "affinity": "INTEGER" + }, + { + "fieldPath": "downloadState", + "columnName": "downloadState", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "localPath", + "columnName": "localPath", + "affinity": "TEXT" + }, + { + "fieldPath": "downloadedAtMs", + "columnName": "downloadedAtMs", + "affinity": "INTEGER" + }, + { + "fieldPath": "audioFromServer", + "columnName": "audioFromServer", + "affinity": "INTEGER", + "notNull": true, + "defaultValue": "0" + }, + { + "fieldPath": "dripReleasedAtMs", + "columnName": "dripReleasedAtMs", + "affinity": "INTEGER" + }, + { + "fieldPath": "appliedRuleId", + "columnName": "appliedRuleId", + "affinity": "TEXT" + }, + { + "fieldPath": "appliedRuleReason", + "columnName": "appliedRuleReason", + "affinity": "TEXT" + }, + { + "fieldPath": "speedOverride", + "columnName": "speedOverride", + "affinity": "REAL" + }, + { + "fieldPath": "midrollCheckedAtMs", + "columnName": "midrollCheckedAtMs", + "affinity": "INTEGER" + }, + { + "fieldPath": "chaptersJson", + "columnName": "chaptersJson", + "affinity": "TEXT" + }, + { + "fieldPath": "detectedIntroMs", + "columnName": "detectedIntroMs", + "affinity": "INTEGER" + }, + { + "fieldPath": "detectedOutroMs", + "columnName": "detectedOutroMs", + "affinity": "INTEGER" + } + ], + "primaryKey": { + "autoGenerate": false, + "columnNames": [ + "id" + ] + }, + "indices": [ + { + "name": "index_episodes_subscriptionId", + "unique": false, + "columnNames": [ + "subscriptionId" + ], + "orders": [], + "createSql": "CREATE INDEX IF NOT EXISTS `index_episodes_subscriptionId` ON `${TABLE_NAME}` (`subscriptionId`)" + }, + { + "name": "index_episodes_subscriptionId_guid", + "unique": true, + "columnNames": [ + "subscriptionId", + "guid" + ], + "orders": [], + "createSql": "CREATE UNIQUE INDEX IF NOT EXISTS `index_episodes_subscriptionId_guid` ON `${TABLE_NAME}` (`subscriptionId`, `guid`)" + }, + { + "name": "index_episodes_publishedAtMs", + "unique": false, + "columnNames": [ + "publishedAtMs" + ], + "orders": [], + "createSql": "CREATE INDEX IF NOT EXISTS `index_episodes_publishedAtMs` ON `${TABLE_NAME}` (`publishedAtMs`)" + } + ], + "foreignKeys": [ + { + "table": "subscriptions", + "onDelete": "CASCADE", + "onUpdate": "NO ACTION", + "columns": [ + "subscriptionId" + ], + "referencedColumns": [ + "id" + ] + } + ] + }, + { + "tableName": "queue", + "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`episodeId` TEXT NOT NULL, `position` INTEGER NOT NULL, PRIMARY KEY(`episodeId`), FOREIGN KEY(`episodeId`) REFERENCES `episodes`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )", + "fields": [ + { + "fieldPath": "episodeId", + "columnName": "episodeId", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "position", + "columnName": "position", + "affinity": "INTEGER", + "notNull": true + } + ], + "primaryKey": { + "autoGenerate": false, + "columnNames": [ + "episodeId" + ] + }, + "indices": [ + { + "name": "index_queue_position", + "unique": false, + "columnNames": [ + "position" + ], + "orders": [], + "createSql": "CREATE INDEX IF NOT EXISTS `index_queue_position` ON `${TABLE_NAME}` (`position`)" + } + ], + "foreignKeys": [ + { + "table": "episodes", + "onDelete": "CASCADE", + "onUpdate": "NO ACTION", + "columns": [ + "episodeId" + ], + "referencedColumns": [ + "id" + ] + } + ] + }, + { + "tableName": "rules", + "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` TEXT NOT NULL, `name` TEXT NOT NULL, `position` INTEGER NOT NULL, `enabled` INTEGER NOT NULL, `subscriptionId` TEXT, `titleContains` TEXT, `titleExcludes` TEXT, `minDurationMs` INTEGER, `maxDurationMs` INTEGER, `maxAgeMs` INTEGER, `seasonNumber` INTEGER, `action` TEXT NOT NULL, `autoDownload` INTEGER NOT NULL, `speed` REAL, PRIMARY KEY(`id`))", + "fields": [ + { + "fieldPath": "id", + "columnName": "id", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "name", + "columnName": "name", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "position", + "columnName": "position", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "enabled", + "columnName": "enabled", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "subscriptionId", + "columnName": "subscriptionId", + "affinity": "TEXT" + }, + { + "fieldPath": "titleContains", + "columnName": "titleContains", + "affinity": "TEXT" + }, + { + "fieldPath": "titleExcludes", + "columnName": "titleExcludes", + "affinity": "TEXT" + }, + { + "fieldPath": "minDurationMs", + "columnName": "minDurationMs", + "affinity": "INTEGER" + }, + { + "fieldPath": "maxDurationMs", + "columnName": "maxDurationMs", + "affinity": "INTEGER" + }, + { + "fieldPath": "maxAgeMs", + "columnName": "maxAgeMs", + "affinity": "INTEGER" + }, + { + "fieldPath": "seasonNumber", + "columnName": "seasonNumber", + "affinity": "INTEGER" + }, + { + "fieldPath": "action", + "columnName": "action", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "autoDownload", + "columnName": "autoDownload", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "speed", + "columnName": "speed", + "affinity": "REAL" + } + ], + "primaryKey": { + "autoGenerate": false, + "columnNames": [ + "id" + ] + }, + "indices": [ + { + "name": "index_rules_position", + "unique": false, + "columnNames": [ + "position" + ], + "orders": [], + "createSql": "CREATE INDEX IF NOT EXISTS `index_rules_position` ON `${TABLE_NAME}` (`position`)" + } + ] + }, + { + "tableName": "show_fingerprints", + "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`subscriptionId` TEXT NOT NULL, `kind` TEXT NOT NULL, `referenceClipPcm` BLOB, `pendingEpisodeId` TEXT, `sampleRate` INTEGER NOT NULL, `confirmedCount` INTEGER NOT NULL, `lastConfirmedEpisodeId` TEXT, `updatedAtMs` INTEGER NOT NULL, PRIMARY KEY(`subscriptionId`, `kind`), FOREIGN KEY(`subscriptionId`) REFERENCES `subscriptions`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )", + "fields": [ + { + "fieldPath": "subscriptionId", + "columnName": "subscriptionId", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "kind", + "columnName": "kind", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "referenceClipPcm", + "columnName": "referenceClipPcm", + "affinity": "BLOB" + }, + { + "fieldPath": "pendingEpisodeId", + "columnName": "pendingEpisodeId", + "affinity": "TEXT" + }, + { + "fieldPath": "sampleRate", + "columnName": "sampleRate", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "confirmedCount", + "columnName": "confirmedCount", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "lastConfirmedEpisodeId", + "columnName": "lastConfirmedEpisodeId", + "affinity": "TEXT" + }, + { + "fieldPath": "updatedAtMs", + "columnName": "updatedAtMs", + "affinity": "INTEGER", + "notNull": true + } + ], + "primaryKey": { + "autoGenerate": false, + "columnNames": [ + "subscriptionId", + "kind" + ] + }, + "indices": [ + { + "name": "index_show_fingerprints_subscriptionId", + "unique": false, + "columnNames": [ + "subscriptionId" + ], + "orders": [], + "createSql": "CREATE INDEX IF NOT EXISTS `index_show_fingerprints_subscriptionId` ON `${TABLE_NAME}` (`subscriptionId`)" + } + ], + "foreignKeys": [ + { + "table": "subscriptions", + "onDelete": "CASCADE", + "onUpdate": "NO ACTION", + "columns": [ + "subscriptionId" + ], + "referencedColumns": [ + "id" + ] + } + ] + }, + { + "tableName": "show_midrolls", + "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`subscriptionId` TEXT NOT NULL, `id` TEXT NOT NULL, `referenceClipPath` TEXT NOT NULL, `sampleRate` INTEGER NOT NULL, `confirmedCount` INTEGER NOT NULL, `lastConfirmedEpisodeId` TEXT, `updatedAtMs` INTEGER NOT NULL, PRIMARY KEY(`subscriptionId`, `id`), FOREIGN KEY(`subscriptionId`) REFERENCES `subscriptions`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )", + "fields": [ + { + "fieldPath": "subscriptionId", + "columnName": "subscriptionId", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "id", + "columnName": "id", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "referenceClipPath", + "columnName": "referenceClipPath", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "sampleRate", + "columnName": "sampleRate", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "confirmedCount", + "columnName": "confirmedCount", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "lastConfirmedEpisodeId", + "columnName": "lastConfirmedEpisodeId", + "affinity": "TEXT" + }, + { + "fieldPath": "updatedAtMs", + "columnName": "updatedAtMs", + "affinity": "INTEGER", + "notNull": true + } + ], + "primaryKey": { + "autoGenerate": false, + "columnNames": [ + "subscriptionId", + "id" + ] + }, + "indices": [ + { + "name": "index_show_midrolls_subscriptionId", + "unique": false, + "columnNames": [ + "subscriptionId" + ], + "orders": [], + "createSql": "CREATE INDEX IF NOT EXISTS `index_show_midrolls_subscriptionId` ON `${TABLE_NAME}` (`subscriptionId`)" + } + ], + "foreignKeys": [ + { + "table": "subscriptions", + "onDelete": "CASCADE", + "onUpdate": "NO ACTION", + "columns": [ + "subscriptionId" + ], + "referencedColumns": [ + "id" + ] + } + ] + }, + { + "tableName": "episode_midrolls", + "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`episodeId` TEXT NOT NULL, `candidateId` TEXT NOT NULL, `startMs` INTEGER NOT NULL, `endMs` INTEGER NOT NULL, `kind` TEXT NOT NULL DEFAULT 'ad', `partners` INTEGER NOT NULL DEFAULT 0, `origin` TEXT NOT NULL DEFAULT 'audio', PRIMARY KEY(`episodeId`, `candidateId`), FOREIGN KEY(`episodeId`) REFERENCES `episodes`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )", + "fields": [ + { + "fieldPath": "episodeId", + "columnName": "episodeId", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "candidateId", + "columnName": "candidateId", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "startMs", + "columnName": "startMs", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "endMs", + "columnName": "endMs", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "kind", + "columnName": "kind", + "affinity": "TEXT", + "notNull": true, + "defaultValue": "'ad'" + }, + { + "fieldPath": "partners", + "columnName": "partners", + "affinity": "INTEGER", + "notNull": true, + "defaultValue": "0" + }, + { + "fieldPath": "origin", + "columnName": "origin", + "affinity": "TEXT", + "notNull": true, + "defaultValue": "'audio'" + } + ], + "primaryKey": { + "autoGenerate": false, + "columnNames": [ + "episodeId", + "candidateId" + ] + }, + "indices": [ + { + "name": "index_episode_midrolls_episodeId", + "unique": false, + "columnNames": [ + "episodeId" + ], + "orders": [], + "createSql": "CREATE INDEX IF NOT EXISTS `index_episode_midrolls_episodeId` ON `${TABLE_NAME}` (`episodeId`)" + } + ], + "foreignKeys": [ + { + "table": "episodes", + "onDelete": "CASCADE", + "onUpdate": "NO ACTION", + "columns": [ + "episodeId" + ], + "referencedColumns": [ + "id" + ] + } + ] + }, + { + "tableName": "break_verdicts", + "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`episodeId` TEXT NOT NULL, `startMs` INTEGER NOT NULL, `endMs` INTEGER NOT NULL, `verdict` TEXT NOT NULL, `decidedAtMs` INTEGER NOT NULL, `kind` TEXT NOT NULL DEFAULT 'ad', `partners` INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(`episodeId`, `startMs`, `endMs`))", + "fields": [ + { + "fieldPath": "episodeId", + "columnName": "episodeId", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "startMs", + "columnName": "startMs", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "endMs", + "columnName": "endMs", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "verdict", + "columnName": "verdict", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "decidedAtMs", + "columnName": "decidedAtMs", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "kind", + "columnName": "kind", + "affinity": "TEXT", + "notNull": true, + "defaultValue": "'ad'" + }, + { + "fieldPath": "partners", + "columnName": "partners", + "affinity": "INTEGER", + "notNull": true, + "defaultValue": "0" + } + ], + "primaryKey": { + "autoGenerate": false, + "columnNames": [ + "episodeId", + "startMs", + "endMs" + ] + }, + "indices": [ + { + "name": "index_break_verdicts_episodeId", + "unique": false, + "columnNames": [ + "episodeId" + ], + "orders": [], + "createSql": "CREATE INDEX IF NOT EXISTS `index_break_verdicts_episodeId` ON `${TABLE_NAME}` (`episodeId`)" + } + ] + }, + { + "tableName": "ad_reports", + "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`episodeId` TEXT NOT NULL, `startMs` INTEGER NOT NULL, `endMs` INTEGER NOT NULL, `reportedAtMs` INTEGER NOT NULL, `correctionWindowMs` INTEGER NOT NULL, `endCorrected` INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(`episodeId`, `startMs`))", + "fields": [ + { + "fieldPath": "episodeId", + "columnName": "episodeId", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "startMs", + "columnName": "startMs", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "endMs", + "columnName": "endMs", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "reportedAtMs", + "columnName": "reportedAtMs", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "correctionWindowMs", + "columnName": "correctionWindowMs", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "endCorrected", + "columnName": "endCorrected", + "affinity": "INTEGER", + "notNull": true, + "defaultValue": "0" + } + ], + "primaryKey": { + "autoGenerate": false, + "columnNames": [ + "episodeId", + "startMs" + ] + }, + "indices": [ + { + "name": "index_ad_reports_episodeId", + "unique": false, + "columnNames": [ + "episodeId" + ], + "orders": [], + "createSql": "CREATE INDEX IF NOT EXISTS `index_ad_reports_episodeId` ON `${TABLE_NAME}` (`episodeId`)" + } + ] + }, + { + "tableName": "episode_transcripts", + "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`episodeId` TEXT NOT NULL, `source` TEXT NOT NULL, `cues` INTEGER NOT NULL, `note` TEXT NOT NULL, `fetchedAtMs` INTEGER NOT NULL, PRIMARY KEY(`episodeId`), FOREIGN KEY(`episodeId`) REFERENCES `episodes`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )", + "fields": [ + { + "fieldPath": "episodeId", + "columnName": "episodeId", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "source", + "columnName": "source", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "cues", + "columnName": "cues", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "note", + "columnName": "note", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "fetchedAtMs", + "columnName": "fetchedAtMs", + "affinity": "INTEGER", + "notNull": true + } + ], + "primaryKey": { + "autoGenerate": false, + "columnNames": [ + "episodeId" + ] + }, + "foreignKeys": [ + { + "table": "episodes", + "onDelete": "CASCADE", + "onUpdate": "NO ACTION", + "columns": [ + "episodeId" + ], + "referencedColumns": [ + "id" + ] + } + ] + }, + { + "tableName": "transcript_cues", + "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`episodeId` TEXT NOT NULL, `startMs` INTEGER NOT NULL, `endMs` INTEGER NOT NULL, `text` TEXT NOT NULL, `advertising` INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(`episodeId`, `startMs`), FOREIGN KEY(`episodeId`) REFERENCES `episodes`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )", + "fields": [ + { + "fieldPath": "episodeId", + "columnName": "episodeId", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "startMs", + "columnName": "startMs", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "endMs", + "columnName": "endMs", + "affinity": "INTEGER", + "notNull": true + }, + { + "fieldPath": "text", + "columnName": "text", + "affinity": "TEXT", + "notNull": true + }, + { + "fieldPath": "advertising", + "columnName": "advertising", + "affinity": "INTEGER", + "notNull": true, + "defaultValue": "0" + } + ], + "primaryKey": { + "autoGenerate": false, + "columnNames": [ + "episodeId", + "startMs" + ] + }, + "indices": [ + { + "name": "index_transcript_cues_episodeId", + "unique": false, + "columnNames": [ + "episodeId" + ], + "orders": [], + "createSql": "CREATE INDEX IF NOT EXISTS `index_transcript_cues_episodeId` ON `${TABLE_NAME}` (`episodeId`)" + } + ], + "foreignKeys": [ + { + "table": "episodes", + "onDelete": "CASCADE", + "onUpdate": "NO ACTION", + "columns": [ + "episodeId" + ], + "referencedColumns": [ + "id" + ] + } + ] + } + ], + "setupQueries": [ + "CREATE TABLE IF NOT EXISTS room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)", + "INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, '485619e60522c24cfe94990fdfcc6903')" + ] + } +}
\ No newline at end of file diff --git a/app/src/androidTest/java/org/terst/listen/data/MigrationTest.kt b/app/src/androidTest/java/org/terst/listen/data/MigrationTest.kt index 49d3406..a261149 100644 --- a/app/src/androidTest/java/org/terst/listen/data/MigrationTest.kt +++ b/app/src/androidTest/java/org/terst/listen/data/MigrationTest.kt @@ -856,4 +856,54 @@ class MigrationTest { assertEquals("the crown jewel must survive", 161803L, it.getLong(0)) } } + + /** + * A break gains the name of the detector that found it, and existing rows default to the + * audio scan -- which is where every break in the database came from before the words + * could produce one. + */ + @Test + fun migrate18To19NamesWhereABreakCameFrom() { + helper.createDatabase(name, 18).apply { + execSQL( + """INSERT INTO subscriptions + (id, feedUrl, kind, title, addedAtMs, skipIntroMs, skipOutroMs, dripEnabled, + dripIntervalMs, dripCount, dripOldestFirst, skipAdChapters, bedtime) + VALUES ('s1', 'https://example.com/feed.xml', 'PODCAST', 'A Show', 1000, 0, 0, + 0, 86400000, 1, 1, 1, 0)""" + ) + execSQL( + """INSERT INTO episodes + (id, subscriptionId, guid, title, mediaUrl, mediaType, discoveredAtMs, + positionMs, downloadState) + VALUES ('e1', 's1', 'g1', 'One', 'https://cdn/1.mp3', 'audio/mpeg', 1000, + 314159, 'NONE')""" + ) + execSQL( + """INSERT INTO episode_midrolls (episodeId, candidateId, startMs, endMs, kind, partners) + VALUES ('e1', 'server-0', 60000, 90000, 'ad', 3)""" + ) + close() + } + + val db = helper.runMigrationsAndValidate(name, 19, true, AppDatabase.MIGRATION_18_19) + + db.query("SELECT origin, startMs FROM episode_midrolls WHERE episodeId = 'e1'").use { + it.moveToFirst() + assertEquals("a break that predates the words came from the audio", "audio", it.getString(0)) + assertEquals(60000L, it.getLong(1)) + } + db.execSQL( + "INSERT INTO episode_midrolls (episodeId, candidateId, startMs, endMs, kind, origin) " + + "VALUES ('e1', 'server-1', 600000, 660000, 'ad', 'words')" + ) + db.query("SELECT origin FROM episode_midrolls WHERE candidateId = 'server-1'").use { + it.moveToFirst() + assertEquals("words", it.getString(0)) + } + db.query("SELECT positionMs FROM episodes WHERE id = 'e1'").use { + it.moveToFirst() + assertEquals("the crown jewel must survive", 314159L, it.getLong(0)) + } + } } diff --git a/app/src/main/java/org/terst/listen/data/AppDatabase.kt b/app/src/main/java/org/terst/listen/data/AppDatabase.kt index 5b9219d..dc9c094 100644 --- a/app/src/main/java/org/terst/listen/data/AppDatabase.kt +++ b/app/src/main/java/org/terst/listen/data/AppDatabase.kt @@ -34,7 +34,7 @@ class Converters { EpisodeTranscriptEntity::class, TranscriptCueEntity::class, ], - version = 18, + version = 19, exportSchema = true, ) @TypeConverters(Converters::class) @@ -484,6 +484,21 @@ abstract class AppDatabase : RoomDatabase() { } } + /** + * A break gains the name of the detector that found it. + * + * Existing rows default to the audio scan, because that is where every break in the + * database came from: the words could not produce one until now. Defaulting to + * "unknown" would have been honest about the column and useless about the rows. + */ + val MIGRATION_18_19 = object : Migration(18, 19) { + override fun migrate(db: SupportSQLiteDatabase) { + db.execSQL( + "ALTER TABLE `episode_midrolls` ADD COLUMN `origin` TEXT NOT NULL DEFAULT 'audio'" + ) + } + } + fun get(context: Context): AppDatabase = instance ?: synchronized(this) { instance ?: build(context.applicationContext).also { instance = it } } @@ -497,7 +512,7 @@ abstract class AppDatabase : RoomDatabase() { MIGRATION_6_7, MIGRATION_7_8, MIGRATION_8_9, MIGRATION_9_10, MIGRATION_10_11, MIGRATION_11_12, MIGRATION_12_13, MIGRATION_13_14, MIGRATION_14_15, MIGRATION_15_16, MIGRATION_16_17, - MIGRATION_17_18, + MIGRATION_17_18, MIGRATION_18_19, ) .addCallback(SEED_DEFAULT_FILTERS) // No fallbackToDestructiveMigration. A listener's playback positions and play diff --git a/app/src/main/java/org/terst/listen/data/Entities.kt b/app/src/main/java/org/terst/listen/data/Entities.kt index 5a81e5d..1ef3bde 100644 --- a/app/src/main/java/org/terst/listen/data/Entities.kt +++ b/app/src/main/java/org/terst/listen/data/Entities.kt @@ -476,6 +476,16 @@ data class EpisodeMidrollEntity( * before this column existed, and a server that did not say, both read as 0. */ @ColumnInfo(defaultValue = "0") val partners: Int = 0, + /** + * Which detector found this: "audio" for the cross-correlation scan over several episodes' + * waveforms, "words" for subtraction or recurrence over their transcripts, or both names + * where the two agreed. + * + * Shown when a break is judged. The listener deciding whether a skip was right wants to + * know what decided it, and the two are not equally sure of themselves. Rows written before + * the words could find anything default to "audio", which is where they all came from. + */ + @ColumnInfo(defaultValue = "audio") val origin: String = "audio", ) /** diff --git a/app/src/main/java/org/terst/listen/fingerprint/SkipSummary.kt b/app/src/main/java/org/terst/listen/fingerprint/SkipSummary.kt index 3f5d04b..66b5fcc 100644 --- a/app/src/main/java/org/terst/listen/fingerprint/SkipSummary.kt +++ b/app/src/main/java/org/terst/listen/fingerprint/SkipSummary.kt @@ -27,6 +27,14 @@ data class EpisodeSkips( * answers and must stay different. */ val status: String?, + /** + * What decided the breaks -- the audio scan, the words, or both. + * + * Null when nothing is cut. It is here because the listener judging a skip wants to know + * what judged it first, and the two detectors are not equally sure of themselves: one + * compares waveforms across a batch of episodes, the other reads what was said. + */ + val foundBy: String? = null, ) { /** The three numbers on one line, known ones only; null when none is known. */ val numbers: String? = listOfNotNull( @@ -162,8 +170,29 @@ object SkipSummary { .takeIf { ranges.isNotEmpty() } ?.let { clock(it) }, status = if (ranges.isEmpty()) statusWithoutBreaks(episode) else null, + foundBy = foundBy(ranges), ) + /** + * Names the detectors behind an episode's breaks, in a fixed order so the line reads the + * same every time. + * + * A break the two agreed on arrives already saying so, and counts for both. + */ + internal fun foundBy(ranges: List<EpisodeMidrollEntity>): String? { + if (ranges.isEmpty()) return null + val origins = ranges.flatMap { it.origin.split("+") }.map { it.trim() }.toSet() + val named = listOfNotNull( + "the audio".takeIf { "audio" in origins }, + "the words".takeIf { "words" in origins }, + ) + return when (named.size) { + 0 -> null + 1 -> "Found by ${named.single()}" + else -> "Found by ${named.joinToString(" and ")}" + } + } + private fun statusWithoutBreaks(episode: EpisodeEntity): String = when { // Nothing has been downloaded yet, so the skips cannot have been checked either; say what // is actually happening instead of "Not checked yet". diff --git a/app/src/main/java/org/terst/listen/server/ServerSkips.kt b/app/src/main/java/org/terst/listen/server/ServerSkips.kt index 8dcd115..87aa104 100644 --- a/app/src/main/java/org/terst/listen/server/ServerSkips.kt +++ b/app/src/main/java/org/terst/listen/server/ServerSkips.kt @@ -151,6 +151,9 @@ class ServerSkips( endMs = range.endMs, kind = range.kind, partners = range.partners, + // An older server says nothing, and everything it ever found came from + // the audio scan, so that is the honest default rather than "unknown". + origin = range.origin.ifBlank { "audio" }, ) ) } diff --git a/app/src/main/java/org/terst/listen/server/SkipServer.kt b/app/src/main/java/org/terst/listen/server/SkipServer.kt index a05eaf7..68a04ed 100644 --- a/app/src/main/java/org/terst/listen/server/SkipServer.kt +++ b/app/src/main/java/org/terst/listen/server/SkipServer.kt @@ -62,6 +62,11 @@ data class ServerBreak( val kind: String = "ad", /** How many other episodes carried this audio; 0 when the server does not say. */ val partners: Int = 0, + /** + * Which detector found it: "audio", "words", or both names where they agreed. Empty when + * the server did not say, which is what an older server sends. + */ + val origin: String = "", ) /** diff --git a/app/src/main/java/org/terst/listen/ui/EpisodeTimeline.kt b/app/src/main/java/org/terst/listen/ui/EpisodeTimeline.kt index cd8c4b6..6fe77e5 100644 --- a/app/src/main/java/org/terst/listen/ui/EpisodeTimeline.kt +++ b/app/src/main/java/org/terst/listen/ui/EpisodeTimeline.kt @@ -149,6 +149,15 @@ fun SkipReadout(skips: EpisodeSkips, breaks: List<SkipSummary.BreakMarker>) { Column(verticalArrangement = Arrangement.spacedBy(2.dp)) { skips.numbers?.let { Text(it, style = MaterialTheme.typography.bodyMedium) } skips.status?.let { Text(it, style = MaterialTheme.typography.bodySmall) } + // What decided the cut, which is the question anyone has about a skip they disagree + // with. Quieter than the numbers: it explains them rather than competing with them. + skips.foundBy?.let { + Text( + it, + style = MaterialTheme.typography.bodySmall, + color = MaterialTheme.colorScheme.onSurfaceVariant, + ) + } if (breaks.any { SkipSummary.isAdvertising(it.kind) }) { LegendEntry(MaterialTheme.colorScheme.tertiary, SkipSummary.LEGEND_ADVERTISING) } diff --git a/app/src/test/java/org/terst/listen/fingerprint/FoundBySummaryTest.kt b/app/src/test/java/org/terst/listen/fingerprint/FoundBySummaryTest.kt new file mode 100644 index 0000000..819d2a9 --- /dev/null +++ b/app/src/test/java/org/terst/listen/fingerprint/FoundBySummaryTest.kt @@ -0,0 +1,75 @@ +package org.terst.listen.fingerprint + +import org.junit.Assert.assertEquals +import org.junit.Assert.assertNull +import org.junit.Test +import org.terst.listen.data.EpisodeMidrollEntity + +/** + * What the episode screen says about which detector decided a skip. + * + * The listener judging a skip wants to know what judged it, and the two are not equally sure + * of themselves: one compares waveforms across a batch of episodes, the other reads what was + * said. A break the two agreed on counts for both, because agreement by entirely different + * means is the strongest thing either can offer. + */ +class FoundBySummaryTest { + + private fun range(origin: String, startMs: Long = 0) = EpisodeMidrollEntity( + episodeId = "e1", candidateId = "c$startMs", startMs = startMs, endMs = startMs + 1000, + origin = origin, + ) + + @Test + fun `breaks from the audio scan alone say so`() { + assertEquals("Found by the audio", SkipSummary.foundBy(listOf(range("audio")))) + } + + @Test + fun `breaks from the words alone say so`() { + assertEquals("Found by the words", SkipSummary.foundBy(listOf(range("words")))) + } + + @Test + fun `an episode with some of each names both`() { + val got = SkipSummary.foundBy(listOf(range("audio"), range("words", 5000))) + assertEquals("Found by the audio and the words", got) + } + + /** A break both detectors found arrives already saying so, and counts for both. */ + @Test + fun `a break the two agreed on names both`() { + assertEquals("Found by the audio and the words", SkipSummary.foundBy(listOf(range("audio+words")))) + } + + /** The order is fixed, so the line does not rearrange itself between episodes. */ + @Test + fun `the order does not depend on which break came first`() { + val one = SkipSummary.foundBy(listOf(range("words"), range("audio", 5000))) + val other = SkipSummary.foundBy(listOf(range("audio"), range("words", 5000))) + assertEquals(one, other) + } + + @Test + fun `nothing cut says nothing`() { + assertNull(SkipSummary.foundBy(emptyList())) + } + + /** A row written by a version that did not record it is not worth a sentence. */ + @Test + fun `an unrecognised origin says nothing`() { + assertNull(SkipSummary.foundBy(listOf(range("")))) + } + + /** + * A readout built without naming a detector explains nothing, which is right: the default + * belongs to the case where nothing has been cut and there is nothing to account for. + */ + @Test + fun `a readout that names no detector says nothing about one`() { + val skips = EpisodeSkips(served = "15:16", declared = "13:08", cut = null, status = "None found") + + assertNull(skips.foundBy) + assertEquals("Served 15:16 \u00b7 Declared 13:08", skips.numbers) + } +} diff --git a/app/src/test/java/org/terst/listen/server/ServerSkipsTest.kt b/app/src/test/java/org/terst/listen/server/ServerSkipsTest.kt index 3b0d671..0d5f686 100644 --- a/app/src/test/java/org/terst/listen/server/ServerSkipsTest.kt +++ b/app/src/test/java/org/terst/listen/server/ServerSkipsTest.kt @@ -516,4 +516,56 @@ class ServerSkipsTest { assertEquals(emptyList<Long>(), transcripts.cueRows.filter { it.advertising }.map { it.startMs }) } + + /* + * A break records which detector found it. + * + * The listener judging a skip wants to know what decided it, and the two detectors are not + * equally sure of themselves: one compares waveforms across a batch of episodes, the other + * reads what was said. + */ + + @Test + fun `records which detector found each break`() = runTest { + val midrolls = FakeMidrollDao() + val transport = StubTransport("""{"status":"ready","audioUrl":"/audio/a?token=t"}""") + + subject(transport, Fakes(midrolls = midrolls)).record( + episode, + ServerEpisode( + ready = true, audioUrl = "x", durationMs = null, introMs = null, outroMs = null, + detected = true, + breaks = listOf( + ServerBreak(60_000, 90_000, "ad", partners = 3, origin = "audio"), + ServerBreak(600_000, 660_000, "ad", origin = "words"), + ), + ), + ) + + assertEquals( + listOf("audio", "words"), + midrolls.rangeRows.sortedBy { it.startMs }.map { it.origin }, + ) + } + + /** + * A server that says nothing found it by the audio scan: that is where every break came + * from before the words could produce one, so "unknown" would be honest about the field + * and useless about the break. + */ + @Test + fun `a break from a server that does not say came from the audio`() = runTest { + val midrolls = FakeMidrollDao() + + subject(StubTransport(null), Fakes(midrolls = midrolls)).record( + episode, + ServerEpisode( + ready = true, audioUrl = "x", durationMs = null, introMs = null, outroMs = null, + detected = true, + breaks = listOf(ServerBreak(60_000, 90_000, "ad")), + ), + ) + + assertEquals("audio", midrolls.rangeRows.single().origin) + } } |