2 -- Minor enhancement: Add columns to time_zone table to support timezone protocol changes.
5 SELECT check_db_revision(1,2,10);
7 CREATE TABLE timezones (
8 our_tzno SERIAL PRIMARY KEY,
9 tzid TEXT UNIQUE NOT NULL,
12 last_modified TIMESTAMP DEFAULT current_timestamp,
17 CREATE TABLE tz_aliases (
18 our_tzno INT8 REFERENCES timezones(our_tzno),
22 CREATE TABLE tz_localnames (
23 our_tzno INT8 REFERENCES timezones(our_tzno),
25 localised_name TEXT NOT NULL,
26 preferred BOOLEAN DEFAULT TRUE
30 -- Let's assume that all timezone definitions currently present are old, and
31 -- we can find newer ones. We don't really want the service feeding them out
32 -- so we'll mark them inactive as well.
33 INSERT INTO timezones (tzid, olson_name, active, last_modified, vtimezone, etag )
34 SELECT tz_id, tz_locn, false, '1970-01-01T00:00:00Z', tz_spec, 'import' FROM time_zone;
35 INSERT INTO tz_aliases (our_tzno, tzalias)
36 SELECT timezones.our_tzno, tz_locn FROM time_zone LEFT JOIN timezones ON (tz_id = tzid)
37 WHERE tz_locn IS NOT NULL AND tz_locn != '';
39 DROP TABLE time_zone CASCADE;
40 ALTER TABLE calendar_item ADD CONSTRAINT "calendar_item_tz_id_fkey" FOREIGN KEY (tz_id) REFERENCES timezones(tzid)
41 ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
42 ALTER TABLE collection ADD CONSTRAINT "collection_timezone_fkey" FOREIGN KEY (timezone) REFERENCES timezones(tzid)
43 ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
45 SELECT new_db_revision(1,2,11, 'Novembre' );