97 lines
7.1 KiB
SQL
97 lines
7.1 KiB
SQL
|
|
CREATE TABLE icecast_mount_templates(
|
|
id VARCHAR(64) PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),
|
|
name VARCHAR(256) NOT NULL,
|
|
source_username VARCHAR(64),
|
|
source_pass VARCHAR(64),
|
|
max_listeners INTEGER DEFAULT 4,
|
|
max_listener_duration INTEGER DEFAULT 3600,
|
|
dump_file VARCHAR(1024),
|
|
intro VARCHAR(1024),
|
|
fallback_mount VARCHAR(1024),
|
|
fallback_override INTEGER DEFAULT 1,
|
|
fallback_when_full INTEGER DEFAULT 1,
|
|
charset VARCHAR(1024) DEFAULT 'ISO8859-1',
|
|
is_public INTEGER DEFAULT 0,
|
|
stream_name VARCHAR(1024),
|
|
stream_description VARCHAR(10000),
|
|
stream_url VARCHAR(1024),
|
|
genre VARCHAR(256),
|
|
bitrate INTEGER,
|
|
mime_type VARCHAR(64) NOT NULL DEFAULT 'audio/mpeg',
|
|
subtype VARCHAR(64),
|
|
burst_size INTEGER,
|
|
mp3_metadata_interval INTEGER,
|
|
hidden INTEGER DEFAULT 1,
|
|
on_connect VARCHAR(1024),
|
|
on_disconnect VARCHAR(1024),
|
|
authentication_id varchar(64) NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
ALTER TABLE icecast_mounts ALTER COLUMN mime_type DROP NOT NULL;
|
|
ALTER TABLE icecast_mounts ALTER COLUMN mime_type DROP DEFAULT;
|
|
ALTER TABLE icecast_mounts ALTER COLUMN subtype DROP NOT NULL;
|
|
ALTER TABLE icecast_mounts ALTER COLUMN subtype DROP DEFAULT;
|
|
ALTER TABLE icecast_mounts ADD COLUMN music_session_id VARCHAR(64) REFERENCES music_sessions(id) ON DELETE CASCADE;
|
|
ALTER TABLE icecast_mounts ADD COLUMN icecast_server_id VARCHAR(64) NOT NULL REFERENCES icecast_servers(id);
|
|
ALTER TABLE icecast_mounts ADD COLUMN icecast_mount_template_id VARCHAR(64) REFERENCES icecast_mount_templates(id);
|
|
ALTER TABLE icecast_mounts ADD COLUMN sourced_needs_changing_at TIMESTAMP;
|
|
;
|
|
CREATE TABLE icecast_server_groups (
|
|
id VARCHAR(64) PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),
|
|
name VARCHAR(255) UNIQUE NOT NULL
|
|
);
|
|
-- bootstrap the default icecast group
|
|
INSERT INTO icecast_server_groups (id, name) VALUES ('default', 'default');
|
|
INSERT INTO icecast_server_groups (id, name) VALUES ('unused', 'unused');
|
|
|
|
ALTER TABLE users ADD COLUMN icecast_server_group_id VARCHAR(64) NOT NULL REFERENCES icecast_server_groups(id) DEFAULT 'default';
|
|
|
|
-- and by default, all servers and users are in this group
|
|
ALTER TABLE icecast_servers ADD COLUMN icecast_server_group_id VARCHAR(64) NOT NULL REFERENCES icecast_server_groups(id) DEFAULT 'default';
|
|
ALTER TABLE icecast_servers ADD COLUMN mount_template_id VARCHAR(64) REFERENCES icecast_mount_templates(id);
|
|
|
|
|
|
ALTER TABLE icecast_servers DROP CONSTRAINT "icecast_servers_admin_auth_id_fkey";
|
|
ALTER TABLE icecast_servers ADD CONSTRAINT "icecast_servers_admin_auth_id_fkey" FOREIGN KEY (admin_auth_id) REFERENCES icecast_admin_authentications(id) ON DELETE SET NULL;
|
|
ALTER TABLE icecast_servers DROP CONSTRAINT "icecast_servers_mount_template_id_fkey";
|
|
ALTER TABLE icecast_servers ADD CONSTRAINT "icecast_servers_mount_template_id_fkey" FOREIGN KEY (mount_template_id) REFERENCES icecast_mount_templates(id) ON DELETE SET NULL;
|
|
ALTER TABLE icecast_servers DROP CONSTRAINT "icecast_servers_directory_id_fkey";
|
|
ALTER TABLE icecast_servers ADD CONSTRAINT "icecast_servers_directory_id_fkey" FOREIGN KEY (directory_id) REFERENCES icecast_directories(id) ON DELETE SET NULL;
|
|
ALTER TABLE icecast_servers DROP CONSTRAINT "icecast_servers_icecast_server_group_id_fkey";
|
|
ALTER TABLE icecast_servers ADD CONSTRAINT "icecast_servers_icecast_server_group_id_fkey" FOREIGN KEY (icecast_server_group_id) REFERENCES icecast_server_groups(id) ON DELETE SET NULL;
|
|
ALTER TABLE icecast_servers DROP CONSTRAINT "icecast_servers_limit_id_fkey";
|
|
ALTER TABLE icecast_servers ADD CONSTRAINT "icecast_servers_limit_id_fkey" FOREIGN KEY (limit_id) REFERENCES icecast_limits(id) ON DELETE SET NULL;
|
|
ALTER TABLE icecast_servers DROP CONSTRAINT "icecast_servers_logging_id_fkey";
|
|
ALTER TABLE icecast_servers ADD CONSTRAINT "icecast_servers_logging_id_fkey" FOREIGN KEY (logging_id) REFERENCES icecast_loggings(id) ON DELETE SET NULL;
|
|
ALTER TABLE icecast_servers DROP CONSTRAINT "icecast_servers_master_relay_id_fkey";
|
|
ALTER TABLE icecast_servers ADD CONSTRAINT "icecast_servers_master_relay_id_fkey" FOREIGN KEY (master_relay_id) REFERENCES icecast_master_server_relays(id) ON DELETE SET NULL;
|
|
ALTER TABLE icecast_servers DROP CONSTRAINT "icecast_servers_path_id_fkey";
|
|
ALTER TABLE icecast_servers ADD CONSTRAINT "icecast_servers_path_id_fkey" FOREIGN KEY (path_id) REFERENCES icecast_paths(id) ON DELETE SET NULL;
|
|
ALTER TABLE icecast_servers DROP CONSTRAINT "icecast_servers_security_id_fkey";
|
|
ALTER TABLE icecast_servers ADD CONSTRAINT "icecast_servers_security_id_fkey" FOREIGN KEY (security_id) REFERENCES icecast_securities(id) ON DELETE SET NULL;
|
|
ALTER TABLE icecast_servers DROP CONSTRAINT "icecast_servers_template_id_fkey";
|
|
ALTER TABLE icecast_servers ADD CONSTRAINT "icecast_servers_template_id_fkey" FOREIGN KEY (template_id) REFERENCES icecast_templates(id) ON DELETE SET NULL;
|
|
|
|
ALTER TABLE icecast_mounts DROP CONSTRAINT "icecast_mounts_icecast_mount_template_id_fkey";
|
|
ALTER TABLE icecast_mounts ADD CONSTRAINT "icecast_mounts_icecast_mount_template_id_fkey" FOREIGN KEY (icecast_mount_template_id) REFERENCES icecast_mount_templates(id) ON DELETE SET NULL;
|
|
ALTER TABLE icecast_mounts DROP CONSTRAINT "icecast_mounts_icecast_server_id_fkey";
|
|
ALTER TABLE icecast_mounts ADD CONSTRAINT "icecast_mounts_icecast_server_id_fkey" FOREIGN KEY (icecast_server_id) REFERENCES icecast_servers(id) ON DELETE SET NULL;
|
|
|
|
ALTER TABLE icecast_templates DROP CONSTRAINT "icecast_templates_admin_auth_id_fkey";
|
|
ALTER TABLE icecast_templates ADD CONSTRAINT "icecast_templates_admin_auth_id_fkey" FOREIGN KEY (admin_auth_id) REFERENCES icecast_admin_authentications(id) ON DELETE SET NULL;
|
|
ALTER TABLE icecast_templates DROP CONSTRAINT "icecast_templates_directory_id_fkey";
|
|
ALTER TABLE icecast_templates ADD CONSTRAINT "icecast_templates_directory_id_fkey" FOREIGN KEY (directory_id) REFERENCES icecast_directories(id) ON DELETE SET NULL;
|
|
ALTER TABLE icecast_templates DROP CONSTRAINT "icecast_templates_limit_id_fkey";
|
|
ALTER TABLE icecast_templates ADD CONSTRAINT "icecast_templates_limit_id_fkey" FOREIGN KEY (limit_id) REFERENCES icecast_limits(id) ON DELETE SET NULL;
|
|
ALTER TABLE icecast_templates DROP CONSTRAINT "icecast_templates_logging_id_fkey";
|
|
ALTER TABLE icecast_templates ADD CONSTRAINT "icecast_templates_logging_id_fkey" FOREIGN KEY (logging_id) REFERENCES icecast_loggings(id) ON DELETE SET NULL;
|
|
ALTER TABLE icecast_templates DROP CONSTRAINT "icecast_templates_master_relay_id_fkey";
|
|
ALTER TABLE icecast_templates ADD CONSTRAINT "icecast_templates_master_relay_id_fkey" FOREIGN KEY (master_relay_id) REFERENCES icecast_master_server_relays(id) ON DELETE SET NULL;
|
|
ALTER TABLE icecast_templates DROP CONSTRAINT "icecast_templates_path_id_fkey";
|
|
ALTER TABLE icecast_templates ADD CONSTRAINT "icecast_templates_path_id_fkey" FOREIGN KEY (path_id) REFERENCES icecast_paths(id) ON DELETE SET NULL;
|
|
ALTER TABLE icecast_templates DROP CONSTRAINT "icecast_templates_security_id_fkey";
|
|
ALTER TABLE icecast_templates ADD CONSTRAINT "icecast_templates_security_id_fkey" FOREIGN KEY (security_id) REFERENCES icecast_securities(id) ON DELETE SET NULL;
|