diff options
Diffstat (limited to 'app')
8 files changed, 1210 insertions, 9 deletions
diff --git a/app/schemas/org.terst.listen.data.AppDatabase/18.json b/app/schemas/org.terst.listen.data.AppDatabase/18.json new file mode 100644 index 0000000..f9f7a2c --- /dev/null +++ b/app/schemas/org.terst.listen.data.AppDatabase/18.json @@ -0,0 +1,1026 @@ +{ + "formatVersion": 1, + "database": { + "version": 18, + "identityHash": "b832d4c76ecdc3df30db9edc8777c1c9", + "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, 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" + } + ], + "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, 'b832d4c76ecdc3df30db9edc8777c1c9')" + ] + } +}
\ 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 52f07eb..49d3406 100644 --- a/app/src/androidTest/java/org/terst/listen/data/MigrationTest.kt +++ b/app/src/androidTest/java/org/terst/listen/data/MigrationTest.kt @@ -803,4 +803,57 @@ class MigrationTest { assertEquals("the transcript row outlived its episode", 0, it.getInt(0)) } } + + /** + * Cues gain a mark saying the server read them as advertising, and existing ones default to + * not. A mark on the words, never a range to skip -- nothing in the playback path reads it. + */ + @Test + fun migrate17To18MarksAdvertisingWithoutDisturbingExistingCues() { + helper.createDatabase(name, 17).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, + 161803, 'NONE')""" + ) + execSQL( + "INSERT INTO episode_transcripts (episodeId, source, cues, note, fetchedAtMs) " + + "VALUES ('e1', 'published', 1, '', 99)" + ) + execSQL( + "INSERT INTO transcript_cues (episodeId, startMs, endMs, text) " + + "VALUES ('e1', 0, 3120, 'Welcome back.')" + ) + close() + } + + val db = helper.runMigrationsAndValidate(name, 18, true, AppDatabase.MIGRATION_17_18) + + db.query("SELECT text, advertising FROM transcript_cues WHERE episodeId = 'e1'").use { + it.moveToFirst() + assertEquals("Welcome back.", it.getString(0)) + assertEquals("a line nobody judged is not an advertisement", 0, it.getInt(1)) + } + db.execSQL( + "INSERT INTO transcript_cues (episodeId, startMs, endMs, text, advertising) " + + "VALUES ('e1', 4000, 9000, 'Our sponsor this week.', 1)" + ) + db.query("SELECT advertising FROM transcript_cues WHERE startMs = 4000").use { + it.moveToFirst() + assertEquals(1, it.getInt(0)) + } + db.query("SELECT positionMs FROM episodes WHERE id = 'e1'").use { + it.moveToFirst() + assertEquals("the crown jewel must survive", 161803L, 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 96a25b9..5b9219d 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 = 17, + version = 18, exportSchema = true, ) @TypeConverters(Converters::class) @@ -469,6 +469,21 @@ abstract class AppDatabase : RoomDatabase() { } } + /** + * A cue gains a mark saying the server read it as advertising. + * + * A mark on the words, not a range to skip. Existing cues default to not: a line + * nobody has judged is not an advertisement, which is the same three-state care the + * transcript verdict itself takes. + */ + val MIGRATION_17_18 = object : Migration(17, 18) { + override fun migrate(db: SupportSQLiteDatabase) { + db.execSQL( + "ALTER TABLE `transcript_cues` ADD COLUMN `advertising` INTEGER NOT NULL DEFAULT 0" + ) + } + } + fun get(context: Context): AppDatabase = instance ?: synchronized(this) { instance ?: build(context.applicationContext).also { instance = it } } @@ -482,6 +497,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, ) .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 2cfd15f..5a81e5d 100644 --- a/app/src/main/java/org/terst/listen/data/Entities.kt +++ b/app/src/main/java/org/terst/listen/data/Entities.kt @@ -629,4 +629,14 @@ data class TranscriptCueEntity( val startMs: Long, val endMs: Long, val text: String, + /** + * Whether the server read this line as part of an advertisement. + * + * A mark on the words, not a range to skip. It is stored on the cue rather than as its own + * table because it is a property of the line as displayed, and the only thing that reads it + * is the screen drawing that line. Nothing in the playback path sees it, deliberately: the + * detector that produced it has never been checked against anything, and a skip into the + * middle of the programme is worse than no skip. + */ + @ColumnInfo(defaultValue = "0") val advertising: Boolean = false, ) 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 d7fb4f5..8dcd115 100644 --- a/app/src/main/java/org/terst/listen/server/ServerSkips.kt +++ b/app/src/main/java/org/terst/listen/server/ServerSkips.kt @@ -107,12 +107,19 @@ class ServerSkips( note = "", fetchedAtMs = System.currentTimeMillis(), ), - fetched.cues.map { + fetched.cues.map { cue -> TranscriptCueEntity( episodeId = episode.id, - startMs = it.startMs, - endMs = it.endMs, - text = it.text, + startMs = cue.startMs, + endMs = cue.endMs, + text = cue.text, + // Flattened onto the cue here rather than kept as ranges. The only thing + // that reads it is the screen drawing that line, and a range the playback + // path could reach is a skip waiting to be made out of a guess that has + // never been checked against anything. + advertising = fetched.ads.any { ad -> + cue.startMs >= ad.startMs && cue.endMs <= ad.endMs + }, ) }, ) 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 0980aff..a05eaf7 100644 --- a/app/src/main/java/org/terst/listen/server/SkipServer.kt +++ b/app/src/main/java/org/terst/listen/server/SkipServer.kt @@ -97,12 +97,29 @@ data class ServerCue( val text: String = "", ) +/** + * A stretch of an episode whose words read as advertising. + * + * A candidate, never a skip. It is shown beside the transcript so the reader can see what the + * server thinks is an advertisement, and nothing in the playback path reads it -- the same + * posture the signature library's shadow run takes, because a detector acting on its own first + * guesses makes skips into the middle of the programme. + */ +@Serializable +data class ServerAdCandidate( + @SerialName("startMs") val startMs: Long = 0, + @SerialName("endMs") val endMs: Long = 0, + /** Which kinds of marker qualified it: sponsor, code, url, offer, legal. */ + val signals: List<String> = emptyList(), +) + /** An episode's words as the server holds them. */ @Serializable data class ServerTranscript( val source: String = "", @SerialName("languageCode") val languageCode: String? = null, val cues: List<ServerCue> = emptyList(), + val ads: List<ServerAdCandidate> = emptyList(), ) /** What the server knows about one episode. */ @@ -322,6 +339,7 @@ class SkipServer( // to seek to. Same filtering the breaks get, because the player acts on these // numbers too -- a tap on a cue seeks to its start. cues = parsed.cues.filter { it.endMs > it.startMs && it.text.isNotBlank() }, + ads = parsed.ads.filter { it.endMs > it.startMs }, ) } diff --git a/app/src/main/java/org/terst/listen/ui/TranscriptScreen.kt b/app/src/main/java/org/terst/listen/ui/TranscriptScreen.kt index c6d6e52..81c1b84 100644 --- a/app/src/main/java/org/terst/listen/ui/TranscriptScreen.kt +++ b/app/src/main/java/org/terst/listen/ui/TranscriptScreen.kt @@ -100,6 +100,9 @@ fun TranscriptScreen( } } +/** Advertising is dimmed, not hidden: a wrong call has to stay visible to be disagreed with. */ +private const val ADVERTISING_ALPHA = 0.45f + @Composable internal fun CueList( cues: List<TranscriptCueEntity>, @@ -119,10 +122,15 @@ internal fun CueList( cue.text, style = MaterialTheme.typography.bodyMedium, fontWeight = if (speaking) FontWeight.Bold else FontWeight.Normal, - color = if (speaking) { - MaterialTheme.colorScheme.onSurface - } else { - MaterialTheme.colorScheme.onSurfaceVariant + // Advertising is dimmed rather than hidden or struck through. The reader can + // see what the server thinks is an advertisement and disagree with it, which + // matters because nothing has ever checked this detector against anything -- + // and hiding the lines would make a wrong call invisible. + color = when { + cue.advertising -> MaterialTheme.colorScheme.onSurfaceVariant + .copy(alpha = ADVERTISING_ALPHA) + speaking -> MaterialTheme.colorScheme.onSurface + else -> MaterialTheme.colorScheme.onSurfaceVariant }, modifier = Modifier .fillMaxWidth() 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 ae6a23b..3b0d671 100644 --- a/app/src/test/java/org/terst/listen/server/ServerSkipsTest.kt +++ b/app/src/test/java/org/terst/listen/server/ServerSkipsTest.kt @@ -453,4 +453,67 @@ class ServerSkipsTest { assertEquals(1, transcripts.cueRows.size) assertEquals(1, transcripts.rows.size) } + + /** + * Advertising the server read in the words is marked on the lines it covers. + * + * Flattened onto the cue rather than stored as ranges, deliberately. The only thing that + * reads it is the screen drawing that line; a range the playback path could reach would be + * a skip made out of a guess nothing has ever checked. + */ + @Test + fun `marks the lines the server read as advertising`() = runTest { + val transcripts = FakeTranscriptDao() + val transport = StubTransport( + null, + alsoServing = mapOf("api/transcript" to """ + {"source":"published","cues":[ + {"startMs":0,"endMs":3000,"text":"Back in a moment."}, + {"startMs":10000,"endMs":15000,"text":"Sponsored by a mattress company."}, + {"startMs":15000,"endMs":26000,"text":"Use promo code LISTEN for 20% off."}, + {"startMs":30000,"endMs":34000,"text":"Right, where were we?"}], + "ads":[{"startMs":10000,"endMs":26000,"signals":["sponsor","code"]}]} + """.trimIndent()), + ) + + subject(transport, Fakes(transcripts = transcripts)).record( + episode, + ServerEpisode( + ready = true, audioUrl = "x", durationMs = null, introMs = null, outroMs = null, + detected = true, + transcript = ServerTranscriptNote( + source = "published", cues = 4, + url = "https://listen.terst.org/api/transcript?episode=abc", + ), + ), + ) + + val marked = transcripts.cueRows.filter { it.advertising }.map { it.startMs } + assertEquals(listOf(10_000L, 15_000L), marked.sorted()) + } + + /** No candidates means no marks, rather than everything or nothing. */ + @Test + fun `leaves every line unmarked when the server found no advertising`() = runTest { + val transcripts = FakeTranscriptDao() + val transport = StubTransport( + null, + alsoServing = mapOf("api/transcript" to + """{"source":"asr","cues":[{"startMs":0,"endMs":3000,"text":"Only this."}]}"""), + ) + + subject(transport, Fakes(transcripts = transcripts)).record( + episode, + ServerEpisode( + ready = true, audioUrl = "x", durationMs = null, introMs = null, outroMs = null, + detected = true, + transcript = ServerTranscriptNote( + source = "asr", cues = 1, + url = "https://listen.terst.org/api/transcript?episode=abc", + ), + ), + ) + + assertEquals(emptyList<Long>(), transcripts.cueRows.filter { it.advertising }.map { it.startMs }) + } } |