129 lines
5.5 KiB
SQL
129 lines
5.5 KiB
SQL
-- this manifest update makes every table associated with music_sessions UNLOGGED
|
|
|
|
-- tables to mark UNLOGGED
|
|
-- connections, fan_invitations, invitations, genres_music_sessions, join_requests, tracks, music_sessions
|
|
|
|
-- breaking foreign keys for tables
|
|
-- connections: user_id
|
|
-- fan_invitations: receiver_id, sender_id
|
|
-- music_session: user_id, band_id, claimed_recording_id, claimed_recording_initiator_id
|
|
-- genres_music_sessions: genre_id
|
|
-- invitations: sender_id, receiver_id
|
|
-- fan_invitations: user_id
|
|
-- notifications: invitation_id, join_request_id, session_id
|
|
|
|
|
|
-- divorce notifications from UNLOGGED tables
|
|
|
|
DROP TABLE sessions_plays;
|
|
|
|
-- NOTIFICATIONS
|
|
----------------
|
|
-- "notifications_session_id_fkey" FOREIGN KEY (session_id) REFERENCES music_sessions(id) ON DELETE CASCADE
|
|
ALTER TABLE notifications DROP CONSTRAINT notifications_session_id_fkey;
|
|
-- "notifications_join_request_id_fkey" FOREIGN KEY (join_request_id) REFERENCES join_requests(id) ON DELETE CASCADE
|
|
ALTER TABLE notifications DROP CONSTRAINT notifications_join_request_id_fkey;
|
|
-- "notifications_invitation_id_fkey" FOREIGN KEY (invitation_id) REFERENCES invitations(id) ON DELETE CASCADE
|
|
ALTER TABLE notifications DROP CONSTRAINT notifications_invitation_id_fkey;
|
|
|
|
-- FAN_INVITATIONS
|
|
------------------
|
|
DROP TABLE fan_invitations;
|
|
DROP TABLE invitations;
|
|
DROP TABLE join_requests;
|
|
DROP TABLE genres_music_sessions;
|
|
DROP TABLE tracks;
|
|
DROP TABLE connections;
|
|
DROP TABLE music_sessions;
|
|
|
|
-- MUSIC_SESSIONS
|
|
-----------------
|
|
CREATE UNLOGGED TABLE music_sessions (
|
|
id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4() NOT NULL,
|
|
description VARCHAR(8000),
|
|
user_id VARCHAR(64) NOT NULL,
|
|
created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL,
|
|
updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL,
|
|
musician_access BOOLEAN NOT NULL,
|
|
band_id VARCHAR(64),
|
|
approval_required BOOLEAN NOT NULL,
|
|
fan_access BOOLEAN NOT NULL,
|
|
fan_chat BOOLEAN NOT NULL,
|
|
claimed_recording_id VARCHAR(64),
|
|
claimed_recording_initiator_id VARCHAR(64)
|
|
);
|
|
|
|
-- CONNECTIONS
|
|
--------------
|
|
CREATE UNLOGGED TABLE connections (
|
|
id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4() NOT NULL,
|
|
user_id VARCHAR(64),
|
|
client_id VARCHAR(64) UNIQUE NOT NULL,
|
|
created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL,
|
|
updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL,
|
|
music_session_id VARCHAR(64),
|
|
ip_address VARCHAR(64),
|
|
as_musician BOOLEAN,
|
|
aasm_state VARCHAR(64) DEFAULT 'idle'::VARCHAR NOT NULL
|
|
);
|
|
ALTER TABLE ONLY connections ADD CONSTRAINT connections_music_session_id_fkey FOREIGN KEY (music_session_id) REFERENCES music_sessions(id) ON DELETE SET NULL;
|
|
|
|
-- GENRES_MUSIC_SESSIONS
|
|
------------------------
|
|
CREATE UNLOGGED TABLE genres_music_sessions (
|
|
id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4() NOT NULL,
|
|
genre_id VARCHAR(64),
|
|
music_session_id VARCHAR(64)
|
|
);
|
|
ALTER TABLE ONLY genres_music_sessions ADD CONSTRAINT genres_music_sessions_music_session_id_fkey FOREIGN KEY (music_session_id) REFERENCES music_sessions(id) ON DELETE CASCADE;
|
|
|
|
CREATE UNLOGGED TABLE fan_invitations (
|
|
id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4() NOT NULL,
|
|
sender_id VARCHAR(64),
|
|
receiver_id VARCHAR(64),
|
|
music_session_id VARCHAR(64),
|
|
created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL,
|
|
updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL
|
|
);
|
|
ALTER TABLE ONLY fan_invitations ADD CONSTRAINT fan_invitations_music_session_id_fkey FOREIGN KEY (music_session_id) REFERENCES music_sessions(id) ON DELETE CASCADE;
|
|
|
|
CREATE UNLOGGED TABLE join_requests (
|
|
id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4() NOT NULL,
|
|
user_id VARCHAR(64),
|
|
music_session_id VARCHAR(64),
|
|
text VARCHAR(2000),
|
|
created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL,
|
|
updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL
|
|
);
|
|
ALTER TABLE ONLY join_requests ADD CONSTRAINT user_music_session_uniqkey UNIQUE (user_id, music_session_id);
|
|
ALTER TABLE ONLY join_requests ADD CONSTRAINT join_requests_music_session_id_fkey FOREIGN KEY (music_session_id) REFERENCES music_sessions(id) ON DELETE CASCADE;
|
|
|
|
-- INVITATIONS
|
|
--------------
|
|
CREATE UNLOGGED TABLE invitations (
|
|
id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4() NOT NULL,
|
|
sender_id VARCHAR(64),
|
|
receiver_id VARCHAR(64),
|
|
music_session_id VARCHAR(64),
|
|
created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL,
|
|
updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL,
|
|
join_request_id VARCHAR(64)
|
|
);
|
|
ALTER TABLE ONLY invitations ADD CONSTRAINT invitations_uniqkey UNIQUE (sender_id, receiver_id, music_session_id);
|
|
ALTER TABLE ONLY invitations ADD CONSTRAINT invitations_join_request_id_fkey FOREIGN KEY (join_request_id) REFERENCES join_requests(id) ON DELETE CASCADE;
|
|
ALTER TABLE ONLY invitations ADD CONSTRAINT invitations_music_session_id_fkey FOREIGN KEY (music_session_id) REFERENCES music_sessions(id) ON DELETE CASCADE;
|
|
|
|
-- TRACKS
|
|
---------
|
|
CREATE UNLOGGED TABLE tracks (
|
|
id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4() NOT NULL,
|
|
connection_id VARCHAR(64),
|
|
instrument_id VARCHAR(64),
|
|
sound VARCHAR(64) NOT NULL,
|
|
created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL,
|
|
updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL,
|
|
client_track_id VARCHAR(64) NOT NULL
|
|
);
|
|
ALTER TABLE ONLY tracks ADD CONSTRAINT connections_tracks_connection_id_fkey FOREIGN KEY (connection_id) REFERENCES connections(id) ON DELETE CASCADE;
|
|
|