Background Painting Progress Database

Table definitions

Sqlite3 database.

CREATE TABLE "bgs" ( 
CREATE TABLE `locations` (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

insert into locations (id, name) values (NULL, 'interior');
insert into locations (id, name) values (NULL, 'exterior');

CREATE TABLE `bgs` (
    `id` INTEGER PRIMARY KEY,
    `show_code` TEXT NOT NULL DEFAULT '', 
    `episode_code` INTEGER NOT NULL DEFAULT 0, 
    `episode` TEXT NOT NULL DEFAULT '',
    `scene` INTEGER NOT NULL, 
    `width` INTEGER NOT NULL, 
    `height` INTEGER NOT NULL, 
    `overlay_count` INTEGER NOT NULL DEFAULT 0, 
    `partial` INTEGER DEFAULT 0, 
    `establishing_shot` INTEGER DEFAULT 0, 
    `location_id` INTEGER NOT NULL,
    `hours` INTEGER, 
    `finished` INTEGER DEFAULT 0,
    `approved` INTEGER DEFAULT 0,
    FOREIGN KEY (location_id) REFERENCES locations(id)
);

Analytics

sqlite> .mode column
sqlite> .header on

SELECT 
    `bgs`.`id`,
    `show_code` || '-' || substr('000'||`episode_code`,-3,3) || ' ' || `episode` as `episode`,
    substr('000' || `scene`, -3, 3) as `scene`, 
    `width` || ' x ' || `height` as `dims`, 
    `locations`.`name` as `location`, 
    `overlay_count` as `overlays`, 
    CASE (`establishing_shot`) 
        WHEN 0 THEN '-' 
        WHEN 1 THEN 'y' 
        END 
    AS `establishing_shot`, 
    CASE (`partial`) 
        WHEN 0 THEN '-' 
        WHEN 1 THEN 'y' 
        END 
    AS `[partial]`, 
    CASE (`finished`) 
        WHEN 0 THEN '-' 
        WHEN 1 then 'y' 
        END 
    AS `finished`, 
    CASE (`approved`) 
        WHEN 0 THEN '-' 
        WHEN 1 then 'y' 
        END 
    AS `approved`, 
    `hours` 
FROM `bgs` 
INNER JOIN `locations` ON `bgs`.`location_id` = `locations`.`id` 
ORDER BY `scene`;