davical/dba/patches/1.3.4.sql

-- Notable enhancement:	add unique constraints to tables missing them

BEGIN;
SELECT check_db_revision(1,3,3);

-- Temporary sequence so we can set the count in each of the tables.
--
-- While this doesn't give the result we really want, where count increments
-- for each dav_id, it'll give us the uniqueness we want. Then as users
-- update their records, we'll eventually up with counts per dav_id.
CREATE SEQUENCE temporary_seq;

-- addressbook_address_adr
ALTER TABLE addressbook_address_adr
  ADD COLUMN count INT;

UPDATE addressbook_address_adr
  SET count = nextval('temporary_seq');

CREATE UNIQUE INDEX addressbook_address_adr_pk
  ON addressbook_address_adr(dav_id, count);

-- addressbook_address_email
ALTER TABLE addressbook_address_email
  ADD COLUMN count INT;

CREATE UNIQUE INDEX addressbook_address_email_pk
  ON addressbook_address_email(dav_id, count);

UPDATE addressbook_address_email
  SET count = nextval('temporary_seq');

-- addressbook_address_tel
ALTER TABLE addressbook_address_tel
  ADD COLUMN count INT;

UPDATE addressbook_address_tel
  SET count = nextval('temporary_seq');

CREATE UNIQUE INDEX addressbook_address_tel_pk
  ON addressbook_address_tel(dav_id, count);

-- calendar_alarm
ALTER TABLE calendar_alarm
  ADD COLUMN count INT;

UPDATE calendar_alarm
  SET count = nextval('temporary_seq');

CREATE UNIQUE INDEX calendar_alarms_pk
  ON calendar_alarm(dav_id, count);

-- Tidy up after ourselves.
DROP SEQUENCE temporary_seq;

-- Add unique constraints where no other changes are needed.
CREATE UNIQUE INDEX role_member_pk
  ON role_member (role_no, user_no);
CREATE UNIQUE INDEX tmp_password_pk
  ON tmp_password (user_no, valid_until);

-- http://blogs.transparent.com/polish/names-of-the-months-and-their-meaning/
SELECT new_db_revision(1,3,4, 'Kwiecień' );

COMMIT;
ROLLBACK;