/usr/share/freetuxtv/sqlite3-create-tables.sql is in freetuxtv 0.6.6~dfsg1-1.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 | -- @libdbevolution-lastdbversion=0.5.0.1
-- @libdbevolution-dbversion=0.1.0.1
CREATE TABLE IF NOT EXISTS channel_logo (
id_channellogo INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
label_channellogo VARCHAR(50) NOT NULL,
filename_channellogo VARCHAR(20)
);
CREATE TABLE IF NOT EXISTS label_channellogo (
id_labelchannellogo INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
label_labelchannellogo VARCHAR(50) NOT NULL,
idchannellogo_labelchannellogo INTEGER NOT NULL
CONSTRAINT fk_idchannellogo_labelchannellogo REFERENCES channel_logo(id_channellogo) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS channels_group (
id_channelsgroup INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
name_channelsgroup VARCHAR(50) NOT NULL,
bregex_channelsgroup VARCHAR(50) NULL,
eregex_channelsgroup VARCHAR(50) NULL,
uri_channelsgroup VARCHAR(500) NULL
);
CREATE TABLE IF NOT EXISTS channel (
id_channel INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
name_channel VARCHAR(50) NOT NULL,
order_channel INTEGER NOT NULL,
idchannellogo_channel INTEGER NULL
CONSTRAINT fk_idchannellogo_channel REFERENCES channel_logo(id_channellogo) ON DELETE SET NULL,
uri_channel VARCHAR(255) NOT NULL,
channelsgroup_channel INTEGER NOT NULL
CONSTRAINT fk_channelsgroup_channel REFERENCES channels_group(id_channelsgroup) ON DELETE CASCADE
);
CREATE TRIGGER fkd_channellogo_id
BEFORE DELETE ON channel_logo
FOR EACH ROW BEGIN
UPDATE channel SET idchannellogo_channel = NULL WHERE idchannellogo_channel = OLD.id_channellogo;
DELETE FROM label_channellogo WHERE idchannellogo_labelchannellogo = OLD.id_channellogo;
END;
CREATE TRIGGER fkd_channel_id
BEFORE DELETE ON channels_group
FOR EACH ROW BEGIN
DELETE from channel WHERE channelsgroup_channel = OLD.id_channelsgroup;
END;
-- @libdbevolution-dbversion=0.5.0.1
ALTER TABLE channels_group RENAME TO channels_group_tmp;
ALTER TABLE channel RENAME TO channel_tmp;
CREATE TABLE IF NOT EXISTS config (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
dbversion VARCHAR(50) NOT NULL
);
CREATE TABLE IF NOT EXISTS tvchannel (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
name VARCHAR(50) NOT NULL,
langs VARCHAR(50),
countries VARCHAR(50),
logo_filename VARCHAR(20),
website VARCHAR(255),
tvguide_url VARCHAR(255)
);
CREATE TABLE IF NOT EXISTS label_tvchannel (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
label VARCHAR(50) NOT NULL,
tvchannel_id INTEGER NOT NULL
CONSTRAINT fk_labeltvchannel_tvchannelid REFERENCES tvchannel(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS channels_group (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
position INTEGER NOT NULL,
name VARCHAR(50) NOT NULL,
type INTEGER NOT NULL,
uri VARCHAR(500) NULL,
bregex VARCHAR(50) NULL,
eregex VARCHAR(50) NULL,
last_update DATETIME NULL
);
CREATE TABLE IF NOT EXISTS channel (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
name VARCHAR(50) NOT NULL,
position INTEGER NOT NULL,
uri VARCHAR(255) NOT NULL,
vlc_options TEXT NULL,
views INTEGER NOT NULL DEFAULT 0,
channelsgroup_id INTEGER NOT NULL
CONSTRAINT fk_channel_channelsgroupid REFERENCES channels_group(id) ON DELETE CASCADE,
tvchannel_id INTEGER NULL
CONSTRAINT fk_channel_tvchannelid REFERENCES tvchannel(id) ON DELETE SET NULL
);
CREATE TRIGGER fkd_delele_tvchannel
BEFORE DELETE ON tvchannel
FOR EACH ROW BEGIN
UPDATE channel SET tvchannel_id = NULL WHERE tvchannel_id = OLD.id;
DELETE FROM label_tvchannel WHERE tvchannel_id = OLD.id;
END;
CREATE TRIGGER fkd_delete_channelsgroup
BEFORE DELETE ON channels_group
FOR EACH ROW BEGIN
DELETE from channel WHERE channelsgroup_id = OLD.id;
END;
INSERT INTO tvchannel (id, name, logo_filename)
SELECT id_channellogo, label_channellogo, filename_channellogo
FROM channel_logo;
INSERT INTO label_tvchannel (id, label, tvchannel_id)
SELECT id_labelchannellogo, label_labelchannellogo,
idchannellogo_labelchannellogo
FROM label_channellogo;
INSERT INTO channels_group (id, position, name, type, uri, bregex, eregex, last_update)
SELECT id_channelsgroup,
(
SELECT COUNT(0) FROM channels_group_tmp t1
WHERE t1.id_channelsgroup <= channels_group_tmp.id_channelsgroup
) AS position,
name_channelsgroup, 0, uri_channelsgroup,
bregex_channelsgroup, eregex_channelsgroup, DATETIME('NOW', 'localtime')
FROM channels_group_tmp;
INSERT INTO channel (id, name, position, uri, channelsgroup_id, tvchannel_id)
SELECT id_channel, name_channel, order_channel, uri_channel, channelsgroup_channel, idchannellogo_channel
FROM channel_tmp;
DROP TRIGGER fkd_channel_id;
DROP TRIGGER fkd_channellogo_id;
DROP TABLE channel_tmp;
DROP TABLE channels_group_tmp;
DROP TABLE label_channellogo;
DROP TABLE channel_logo;
-- @libdbevolution-dbversion=0.5.1.1
ALTER TABLE channel ADD COLUMN deinterlace_mode VARCHAR(15) NULL;
-- @libdbevolution-dbversion=0.5.1.2
ALTER TABLE channel ADD COLUMN updated BOOLEAN NULL DEFAULT 1;
-- @libdbevolution-dbversion=0.6.0.1
CREATE TABLE IF NOT EXISTS recording (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
title VARCHAR(100) NOT NULL,
begin_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
filename TEXT NULL,
channel_id INTEGER NULL
CONSTRAINT fk_recording_channelid REFERENCES channel(id) ON DELETE SET NULL
);
CREATE TRIGGER fkd_delele_channel
BEFORE DELETE ON channel
FOR EACH ROW BEGIN
UPDATE recording SET channel_id = NULL WHERE channel_id = OLD.id;
END;
-- @libdbevolution-dbversion=0.6.0.2
ALTER TABLE recording ADD COLUMN status INTEGER NOT NULL DEFAULT 1;
|