jam-cloud/db/up/recordings_public_launch.sql

-- so that rows can live on after session is over
ALTER TABLE recordings DROP CONSTRAINT "recordings_music_session_id_fkey";
-- unambiguous declartion that the recording is over or not
ALTER TABLE recordings ADD COLUMN is_done BOOLEAN DEFAULT FALSE;

-- add name and description on claimed_recordings, which is the user's individual view of a recording
ALTER TABLE claimed_recordings ADD COLUMN description VARCHAR(8000);
ALTER TABLE claimed_recordings ADD COLUMN description_tsv tsvector;
ALTER TABLE claimed_recordings ADD COLUMN name_tsv tsvector;

CREATE TRIGGER tsvectorupdate_description BEFORE INSERT OR UPDATE
ON claimed_recordings FOR EACH ROW EXECUTE PROCEDURE 
tsvector_update_trigger(description_tsv, 'public.jamenglish', description);

CREATE TRIGGER tsvectorupdate_name BEFORE INSERT OR UPDATE
ON claimed_recordings FOR EACH ROW EXECUTE PROCEDURE 
tsvector_update_trigger(name_tsv, 'public.jamenglish', name);

CREATE INDEX claimed_recordings_description_tsv_index ON claimed_recordings USING gin(description_tsv);
CREATE INDEX claimed_recordings_name_tsv_index ON claimed_recordings USING gin(name_tsv);

-- copies of connection.client_id and track.id
ALTER TABLE recorded_tracks ADD COLUMN client_id VARCHAR(64) NOT NULL;
ALTER TABLE recorded_tracks ADD COLUMN track_id VARCHAR(64) NOT NULL;

-- so that server can correlate to client track
DELETE FROM tracks;
ALTER TABLE tracks ADD COLUMN client_track_id VARCHAR(64) NOT NULL;