Show
Ignore:
Timestamp:
2008-07-30 11:31:10 (4 months ago)
Author:
murdos
Message:

Introduces entities _meta tables, currently only holding lastupdate info.

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • mb_server/trunk/admin/sql/CreateFKConstraints.sql

    r9856 r10196  
    5151    REFERENCES artist(id); 
    5252 
     53ALTER TABLE artist_meta 
     54        ADD CONSTRAINT fk_artist_meta_artist 
     55        FOREIGN KEY (id) 
     56        REFERENCES artist(id); 
     57         
    5358ALTER TABLE artist_relation 
    5459    ADD CONSTRAINT artist_relation_fk_artist1 
    …… 
    112117    REFERENCES country(id); 
    113118 
     119ALTER TABLE label_meta 
     120        ADD CONSTRAINT fk_label_meta_label 
     121        FOREIGN KEY (id) 
     122        REFERENCES label(id); 
     123         
    114124ALTER TABLE labelalias 
    115125    ADD CONSTRAINT labelalias_fk_ref 
    …… 
    576586    REFERENCES artist(id); 
    577587 
     588ALTER TABLE track_meta 
     589        ADD CONSTRAINT fk_track_meta_track 
     590        FOREIGN KEY (id) 
     591        REFERENCES track(id); 
     592         
    578593ALTER TABLE track_tag 
    579594    ADD CONSTRAINT fk_track_tag_track 

  • mb_server/trunk/admin/sql/CreateFunctions.sql

    r9871 r10196  
    9898 
    9999--'----------------------------------------------------------------- 
    100 -- Keep lastupdated columns up to date 
    101 --'----------------------------------------------------------------- 
    102  
    103 create or replace function b_iu_update_lastmodified () returns TRIGGER as ' 
    104 begin 
    105     NEW.lastupdate = now();  
    106     IF (TG_RELNAME = ''track'') 
    107     THEN 
    108         -- update the releases 
    109         UPDATE album SET lastupdate = NOW() FROM albumjoin WHERE NEW.id = albumjoin.track and albumjoin.album = album.id; 
    110     END IF; 
    111     IF (TG_RELNAME = ''album'') 
    112     THEN 
    113         -- update the artist 
    114         IF (NEW.artist != 1) 
    115         THEN 
    116             UPDATE artist SET lastupdate = NOW() WHERE artist.id = NEW.artist; 
    117         END IF; 
    118     END IF; 
    119    return NEW; 
    120 end; 
    121 ' language 'plpgsql'; 
    122  
    123 --'----------------------------------------------------------------- 
    124100-- Keep rows in albummeta in sync with album 
    125101--'----------------------------------------------------------------- 
    …… 
    127103create or replace function insert_album_meta () returns TRIGGER as $$ 
    128104begin  
    129     insert into albummeta (id, tracks, discids, puids) values (NEW.id, 0, 0, 0);  
     105    insert into albummeta (id, tracks, discids, puids, lastupdate) values (NEW.id, 0, 0, 0, now());  
    130106    insert into album_amazon_asin (album, lastupdate) values (NEW.id, '1970-01-01 00:00:00');  
    131107     
    …… 
    140116        update album_amazon_asin set lastupdate = '1970-01-01 00:00:00' where album = NEW.id;  
    141117    end if; 
     118    UPDATE albummeta SET lastupdate = now() WHERE id = NEW.id;  
    142119   return NULL; 
    143120end; 
    …… 
    153130 
    154131--'----------------------------------------------------------------- 
     132-- Keep rows in <entity>_meta table in sync with table <entity> 
     133--'----------------------------------------------------------------- 
     134 
     135create or replace function a_idu_entity () returns TRIGGER as $$ 
     136begin  
     137    IF (TG_OP = 'INSERT')  
     138    THEN 
     139        EXECUTE 'INSERT INTO ' || TG_RELNAME || '_meta (id) VALUES (' || NEW.id || ')'; 
     140    ELSIF (TG_OP = 'DELETE')  
     141    THEN 
     142        EXECUTE 'DELETE FROM ' || TG_RELNAME || '_meta WHERE id = ' || OLD.id; 
     143    ELSIF (TG_OP = 'UPDATE') 
     144    THEN 
     145        EXECUTE 'UPDATE ' || TG_RELNAME || ' SET lastupdate = now() WHERE id = ' || NEW.id;  
     146    END IF; 
     147    RETURN NULL;  
     148end;  
     149$$ language 'plpgsql'; 
     150 
     151--'----------------------------------------------------------------- 
     152-- Propagates changes on <entity>_meta to linked entities metadata  
     153--'----------------------------------------------------------------- 
     154create or replace function a_idu_entity_meta () returns TRIGGER as $$ 
     155declare 
     156    entity_id track_meta.id%TYPE; 
     157begin  
     158    IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN 
     159        entity_id := NEW.id; 
     160    ELSE 
     161        entity_id := OLD.id; 
     162    END IF; 
     163 
     164    IF (TG_RELNAME = 'track_meta') THEN 
     165        -- update the releases 
     166        UPDATE albummeta SET lastupdate = NOW()  
     167        WHERE id IN (SELECT distinct album FROM albumjoin WHERE entity_id = albumjoin.track); 
     168    ELSIF (TG_RELNAME = 'albummeta') THEN 
     169        -- update the artist 
     170        UPDATE artist_meta SET lastupdate = NOW() 
     171        WHERE id = (SELECT artist FROM album where id = entity_id) 
     172            -- Skip Various artists 
     173            AND id <> 1;  
     174    END IF; 
     175    RETURN NULL;  
     176end;  
     177$$ language 'plpgsql'; 
     178--'----------------------------------------------------------------- 
    155179-- Changes to albumjoin could cause changes to albummeta.tracks 
    156 -- and/or albummeta.puids and/org albummeta.puids 
     180-- and/or albummeta.puids and/or albummeta.puids 
    157181--'----------------------------------------------------------------- 
    158182 
    …… 
    161185    UPDATE  albummeta 
    162186    SET     tracks = tracks + 1, 
    163             puids = puids + (SELECT COUNT(*) FROM puidjoin WHERE track = NEW.track) 
     187            puids = puids + (SELECT COUNT(*) FROM puidjoin WHERE track = NEW.track), 
     188            lastupdate = now() 
    164189    WHERE   id = NEW.album; 
    165  
    166     -- When a track is added to an album, update the albums lastupdate 
    167     UPDATE  album SET lastupdate = NOW() WHERE album.id = NEW.album; 
    168190 
    169191    return NULL; 
    …… 
    180202    UPDATE  albummeta 
    181203    SET     tracks = tracks - 1, 
    182             puids = puids - (SELECT COUNT(*) FROM puidjoin WHERE track = OLD.track) 
     204            puids = puids - (SELECT COUNT(*) FROM puidjoin WHERE track = OLD.track), 
     205            lastupdate = now() 
    183206    WHERE   id = OLD.album; 
    184207 
    185208    UPDATE  albummeta 
    186209    SET     tracks = tracks + 1, 
    187             puids = puids + (SELECT COUNT(*) FROM puidjoin WHERE track = NEW.track) 
     210            puids = puids + (SELECT COUNT(*) FROM puidjoin WHERE track = NEW.track), 
     211            lastupdate = now() 
    188212    WHERE   id = NEW.album; 
    189  
    190     -- When a track is added to an album, update the albums lastupdate 
    191     UPDATE  album SET lastupdate = NOW() WHERE album.id = NEW.album; 
    192213 
    193214    return NULL; 
    …… 
    199220    UPDATE  albummeta 
    200221    SET     tracks = tracks - 1, 
    201             puids = puids - (SELECT COUNT(*) FROM puidjoin WHERE track = OLD.track) 
     222            puids = puids - (SELECT COUNT(*) FROM puidjoin WHERE track = OLD.track), 
     223            lastupdate = now() 
    202224    WHERE   id = OLD.album; 
    203  
    204     -- When a track is removed from an album, update the albums lastupdate 
    205     UPDATE  album SET lastupdate = NOW() WHERE album.id = OLD.album; 
    206225 
    207226    return NULL; 
    …… 
    378397    UPDATE albummeta SET firstreleasedate = ( 
    379398        SELECT MIN(releasedate) FROM release WHERE album = $1 
    380     ) WHERE id = $1; 
     399    ), lastupdate = now() WHERE id = $1; 
    381400    RETURN; 
    382401END; 
    383402' LANGUAGE 'plpgsql'; 
    384403 
    385 CREATE OR REPLACE FUNCTION a_ins_release () RETURNS TRIGGER AS ' 
     404CREATE OR REPLACE FUNCTION a_ins_release () RETURNS TRIGGER AS $$ 
    386405BEGIN 
    387406    EXECUTE set_album_firstreleasedate(NEW.album); 
    388     UPDATE album SET lastupdate = NOW() WHERE id = NEW.album; 
    389407    RETURN NEW; 
    390408END; 
    391 ' LANGUAGE 'plpgsql'; 
    392  
    393 CREATE OR REPLACE FUNCTION a_upd_release () RETURNS TRIGGER AS ' 
     409$$ LANGUAGE 'plpgsql'; 
     410 
     411CREATE OR REPLACE FUNCTION a_upd_release () RETURNS TRIGGER AS $$ 
    394412BEGIN 
    395413    EXECUTE set_album_firstreleasedate(NEW.album); 
    …… 
    398416        EXECUTE set_album_firstreleasedate(OLD.album); 
    399417    END IF; 
    400     UPDATE album SET lastupdate = NOW() WHERE id = NEW.album; 
    401418    RETURN NEW; 
    402419END; 
    403 ' LANGUAGE 'plpgsql'; 
    404  
    405 CREATE OR REPLACE FUNCTION a_del_release () RETURNS TRIGGER AS ' 
     420$$ LANGUAGE 'plpgsql'; 
     421 
     422CREATE OR REPLACE FUNCTION a_del_release () RETURNS TRIGGER AS $$ 
    406423BEGIN 
    407424    EXECUTE set_album_firstreleasedate(OLD.album); 
    408     UPDATE album SET lastupdate = NOW() WHERE id = OLD.album; 
    409425    RETURN OLD; 
    410426END; 
    411 ' LANGUAGE 'plpgsql'; 
     427$$ LANGUAGE 'plpgsql'; 
    412428 
    413429--'----------------------------------------------------------------- 

  • mb_server/trunk/admin/sql/CreatePrimaryKeys.sql

    r9856 r10196  
    1111ALTER TABLE annotation ADD CONSTRAINT annotation_pkey PRIMARY KEY (id); 
    1212ALTER TABLE artist ADD CONSTRAINT artist_pkey PRIMARY KEY (id); 
     13ALTER TABLE artist_meta ADD CONSTRAINT artist_meta_pkey PRIMARY KEY(id); 
    1314ALTER TABLE artistalias ADD CONSTRAINT artistalias_pkey PRIMARY KEY (id); 
    1415ALTER TABLE artist_relation ADD CONSTRAINT artist_relation_pkey PRIMARY KEY (id); 
    …… 
    3940ALTER TABLE l_url_url ADD CONSTRAINT l_url_url_pkey PRIMARY KEY (id); 
    4041ALTER TABLE label ADD CONSTRAINT label_pkey PRIMARY KEY (id); 
     42ALTER TABLE label_meta ADD CONSTRAINT label_meta_pkey PRIMARY KEY(id); 
    4143ALTER TABLE labelalias ADD CONSTRAINT labelalias_pkey PRIMARY KEY (id); 
    4244ALTER TABLE label_tag ADD CONSTRAINT label_tag_pkey PRIMARY KEY (label, tag); 
    …… 
    8385ALTER TABLE tag ADD CONSTRAINT tag_pkey PRIMARY KEY (id); 
    8486ALTER TABLE track ADD CONSTRAINT track_pkey PRIMARY KEY (id); 
     87ALTER TABLE track_meta ADD CONSTRAINT track_meta_pkey PRIMARY KEY(id); 
    8588ALTER TABLE track_tag ADD CONSTRAINT track_tag_pkey PRIMARY KEY (track, tag); 
    8689ALTER TABLE trackwords ADD CONSTRAINT trackwords_pkey PRIMARY KEY (wordid, trackid); 

  • mb_server/trunk/admin/sql/CreateTables.sql

    r10192 r10196  
    3030    modpending_lang     INTEGER, 
    3131    quality             SMALLINT DEFAULT -1, 
    32     modpending_qual     INTEGER DEFAULT 0, 
    33     lastupdate          TIMESTAMP WITH TIME ZONE DEFAULT NOW() 
     32    modpending_qual     INTEGER DEFAULT 0 
    3433); 
    3534 
    …… 
    6766    firstreleasedate    CHAR(10), 
    6867    asin                CHAR(10), 
    69     coverarturl         VARCHAR(255) 
     68    coverarturl         VARCHAR(255), 
     69    lastupdate          TIMESTAMP WITH TIME ZONE DEFAULT NOW() 
    7070); 
    7171 
    …… 
    102102    type                SMALLINT, 
    103103    quality             SMALLINT DEFAULT -1, 
    104     modpending_qual     INTEGER DEFAULT 0, 
    105     lastupdate          TIMESTAMP WITH TIME ZONE DEFAULT NOW() 
     104    modpending_qual     INTEGER DEFAULT 0 
     105); 
     106 
     107CREATE TABLE artist_meta 
     108( 
     109    id          INTEGER NOT NULL, 
     110    lastupdate  TIMESTAMP WITH TIME ZONE DEFAULT NOW() 
    106111); 
    107112 
    …… 
    228233    begindate           CHAR(10), 
    229234    enddate             CHAR(10), 
    230     type                SMALLINT, 
    231     lastupdate          TIMESTAMP WITH TIME ZONE DEFAULT NOW() 
     235    type                SMALLINT 
     236); 
     237 
     238CREATE TABLE label_meta 
     239( 
     240    id          INTEGER NOT NULL, 
     241    lastupdate  TIMESTAMP WITH TIME ZONE DEFAULT NOW() 
    232242); 
    233243 
    …… 
    928938    length              INTEGER DEFAULT 0, 
    929939    year                INTEGER DEFAULT 0, 
    930     modpending          INTEGER DEFAULT 0, 
    931     lastupdate          TIMESTAMP WITH TIME ZONE DEFAULT NOW() 
     940    modpending          INTEGER DEFAULT 0 
     941); 
     942 
     943CREATE TABLE track_meta 
     944( 
     945    id          INTEGER NOT NULL, 
     946    lastupdate  TIMESTAMP WITH TIME ZONE DEFAULT NOW() 
    932947); 
    933948 

  • mb_server/trunk/admin/sql/CreateTriggers.sql

    r9870 r10196  
    11\set ON_ERROR_STOP 1 
    22 
    3 CREATE TRIGGER b_iu_album BEFORE INSERT OR DELETE OR UPDATE ON album  
    4    FOR EACH ROW EXECUTE PROCEDURE b_iu_update_lastmodified(); 
     3-- Album related tables 
    54 
    6 -- These XXXX_album_meta() functions should really have the _meta dropped 
     5    -- These XXXX_album_meta() functions should really have the _meta dropped 
    76CREATE TRIGGER a_ins_album AFTER INSERT ON album  
    87    FOR EACH ROW EXECUTE PROCEDURE insert_album_meta(); 
    …… 
    1110CREATE TRIGGER a_del_album after DELETE ON album  
    1211    FOR EACH ROW EXECUTE PROCEDURE delete_album_meta(); 
     12 
     13CREATE TRIGGER a_idu_albummeta AFTER INSERT OR DELETE OR UPDATE ON albummeta  
     14    FOR EACH ROW EXECUTE PROCEDURE a_idu_entity_meta(); 
    1315 
    1416CREATE TRIGGER a_ins_albumjoin AFTER INSERT ON albumjoin 
    …… 
    2628    FOR EACH ROW EXECUTE PROCEDURE a_del_album_cdtoc(); 
    2729 
    28 CREATE TRIGGER b_iu_artist BEFORE INSERT OR DELETE OR UPDATE ON artist  
    29    FOR EACH ROW EXECUTE PROCEDURE b_iu_update_lastmodified(); 
     30-- Artist 
     31CREATE TRIGGER a_idu_artist AFTER INSERT OR DELETE OR UPDATE ON artist  
     32    FOR EACH ROW EXECUTE PROCEDURE a_idu_entity(); 
     33CREATE TRIGGER a_idu_artist_meta AFTER INSERT OR DELETE OR UPDATE ON artist_meta  
     34    FOR EACH ROW EXECUTE PROCEDURE a_idu_entity_meta(); 
    3035 
     36-- Moderations 
    3137CREATE TRIGGER a_upd_moderation_open AFTER UPDATE ON moderation_open 
    3238    FOR EACH ROW EXECUTE PROCEDURE after_update_moderation_open(); 
    3339 
     40-- Release events 
    3441CREATE TRIGGER b_iu_release BEFORE INSERT OR UPDATE ON release 
    3542    FOR EACH ROW EXECUTE PROCEDURE before_insertupdate_release(); 
    …… 
    4148    FOR EACH ROW EXECUTE PROCEDURE a_del_release(); 
    4249 
     50-- album_amazon_asin 
    4351CREATE TRIGGER a_ins_album_amazon_asin AFTER INSERT ON album_amazon_asin 
    4452    FOR EACH ROW EXECUTE PROCEDURE a_ins_album_amazon_asin(); 
    …… 
    4856    FOR EACH ROW EXECUTE PROCEDURE a_del_album_amazon_asin(); 
    4957 
    50 create trigger b_iu_label BEFORE INSERT OR DELETE OR UPDATE ON label  
    51    FOR EACH ROW EXECUTE PROCEDURE b_iu_update_lastmodified(); 
     58-- Label 
     59CREATE TRIGGER a_idu_label AFTER INSERT OR DELETE OR UPDATE ON label  
     60    FOR EACH ROW EXECUTE PROCEDURE a_idu_entity(); 
     61CREATE TRIGGER a_idu_label_meta AFTER INSERT OR DELETE OR UPDATE ON label_meta  
     62    FOR EACH ROW EXECUTE PROCEDURE a_idu_entity_meta(); 
    5263 
     64-- PUIDs 
    5365CREATE TRIGGER a_ins_puidjoin AFTER INSERT ON puidjoin 
    5466    FOR EACH ROW EXECUTE PROCEDURE a_ins_puidjoin(); 
    …… 
    6173    FOR EACH ROW EXECUTE PROCEDURE a_idu_puidjoin_stat(); 
    6274 
     75-- Tags 
    6376CREATE TRIGGER a_ins_artist_tag AFTER INSERT ON artist_tag 
    6477    FOR EACH ROW EXECUTE PROCEDURE a_ins_tag(); 
    …… 
    7083CREATE TRIGGER a_del_release_tag AFTER DELETE ON release_tag 
    7184     FOR EACH ROW EXECUTE PROCEDURE a_del_tag(); 
    72  
    73 CREATE TRIGGER b_iu_track BEFORE INSERT OR DELETE OR UPDATE ON track  
    74    FOR EACH ROW EXECUTE PROCEDURE b_iu_update_lastmodified(); 
    7585 
    7686CREATE TRIGGER a_ins_track_tag AFTER INSERT ON track_tag 
    …… 
    8494    FOR EACH ROW EXECUTE PROCEDURE a_del_tag(); 
    8595 
     96-- Tracks 
     97CREATE TRIGGER a_idu_track AFTER INSERT OR DELETE OR UPDATE ON track  
     98    FOR EACH ROW EXECUTE PROCEDURE a_idu_entity(); 
     99CREATE TRIGGER a_idu_track_meta AFTER INSERT OR DELETE OR UPDATE ON track_meta  
     100    FOR EACH ROW EXECUTE PROCEDURE a_idu_entity_meta(); 
     101 
    86102-- vi: set ts=4 sw=4 et : 

  • mb_server/trunk/admin/sql/DropFKConstraints.sql

    r9856 r10196  
    1313-- albummeta ? 
    1414ALTER TABLE albumwords DROP CONSTRAINT albumwords_fk_albumid; 
     15ALTER TABLE artist_meta DROP CONSTRAINT fk_artist_meta_artist; 
    1516ALTER TABLE artist_relation DROP CONSTRAINT artist_relation_fk_artist1; 
    1617ALTER TABLE artist_relation DROP CONSTRAINT artist_relation_fk_artist2; 
    …… 
    2526ALTER TABLE automod_election_vote DROP CONSTRAINT automod_election_vote_fk_automod_election; 
    2627ALTER TABLE automod_election_vote DROP CONSTRAINT automod_election_vote_fk_voter; 
     28ALTER TABLE label_meta DROP CONSTRAINT fk_label_meta_track; 
    2729ALTER TABLE labelwords DROP CONSTRAINT labelwords_fk_labelid; 
    2830ALTER TABLE labelalias DROP CONSTRAINT labelalias_fk_ref; 
    …… 
    123125ALTER TABLE script_language DROP CONSTRAINT script_language_fk_script; 
    124126ALTER TABLE track DROP CONSTRAINT track_fk_artist; 
     127ALTER TABLE track_meta DROP CONSTRAINT track_meta_fk_track; 
    125128ALTER TABLE track_tag DROP CONSTRAINT fk_track_tag_track; 
    126129ALTER TABLE track_tag DROP CONSTRAINT fk_track_tag_tag; 

  • mb_server/trunk/admin/sql/DropFunctions.sql

    r9870 r10196  
    2525DROP FUNCTION a_upd_puidjoin(); 
    2626DROP FUNCTION a_del_puidjoin(); 
    27 DROP FUNCTION b_iu_update_lastmodified(); 
     27DROP FUNCTION a_idu_entity(); 
     28DROP FUNCTION a_idu_entity_meta(); 
    2829 
    2930-- vi: set ts=4 sw=4 et : 

  • mb_server/trunk/admin/sql/DropPrimaryKeys.sql

    r9899 r10196  
    1111ALTER TABLE annotation DROP CONSTRAINT annotation_pkey; 
    1212ALTER TABLE artist DROP CONSTRAINT artist_pkey; 
     13ALTER TABLE artist_meta DROP CONSTRAINT artist_meta_pkey; 
    1314ALTER TABLE artistalias DROP CONSTRAINT artistalias_pkey; 
    1415ALTER TABLE artist_relation DROP CONSTRAINT artist_relation_pkey; 
    …… 
    3940ALTER TABLE l_url_url DROP CONSTRAINT l_url_url_pkey; 
    4041ALTER TABLE label DROP CONSTRAINT label_pkey; 
     42ALTER TABLE label_meta DROP CONSTRAINT label_meta_pkey; 
    4143ALTER TABLE label_tag DROP CONSTRAINT label_tag_pkey; 
    4244ALTER TABLE labelalias DROP CONSTRAINT labelalias_pkey; 
    …… 
    8284ALTER TABLE stats DROP CONSTRAINT stats_pkey; 
    8385ALTER TABLE track DROP CONSTRAINT track_pkey; 
     86ALTER TABLE track_meta DROP CONSTRAINT track_meta_pkey; 
    8487ALTER TABLE track_tag DROP CONSTRAINT track_tag_pkey; 
    8588ALTER TABLE trackwords DROP CONSTRAINT trackwords_pkey; 

  • mb_server/trunk/admin/sql/DropTables.sql

    r9856 r10196  
    1010DROP TABLE annotation; 
    1111DROP TABLE artist; 
     12DROP TABLE artist_meta; 
    1213DROP TABLE artistalias; 
    1314DROP TABLE artistwords; 
    …… 
    2223DROP TABLE gid_redirect; 
    2324DROP TABLE label; 
     25DROP TABLE label_meta; 
    2426DROP TABLE labelwords; 
    2527DROP TABLE l_album_album; 
    …… 
    7779DROP TABLE stats; 
    7880DROP TABLE track; 
     81DROP TABLE track_meta; 
    7982DROP TABLE trackwords; 
    8083DROP TABLE url; 

  • mb_server/trunk/admin/sql/DropTriggers.sql

    r9871 r10196  
    3232DROP TRIGGER a_ins_label_tag ON label_tag; 
    3333DROP TRIGGER a_del_label_tag ON label_tag; 
    34 DROP TRIGGER b_iu_artist ON artist; 
    35 DROP TRIGGER b_iu_album ON album; 
    36 DROP TRIGGER b_iu_track ON track; 
    37 DROP TRIGGER b_iu_label ON label; 
     34DROP TRIGGER a_idu_artist ON artist; 
     35DROP TRIGGER a_idu_track ON track; 
     36DROP TRIGGER a_idu_label ON label; 
     37DROP TRIGGER a_idu_artist_meta ON artist_meta; 
     38DROP TRIGGER a_idu_track_meta ON track_meta; 
     39DROP TRIGGER a_idu_label_meta ON label_meta; 
     40DROP TRIGGER a_idu_albummeta ON albummeta; 
    3841 
    3942-- vi: set ts=4 sw=4 et : 

  • mb_server/trunk/admin/sql/updates/20080610-1.sql

    r9870 r10196  
    33BEGIN; 
    44 
    5 -- Add timestamps to entities 
    6 alter table artist add column lastupdate          TIMESTAMP WITH TIME ZONE DEFAULT NOW(); 
    7 alter table album  add column lastupdate          TIMESTAMP WITH TIME ZONE DEFAULT NOW(); 
    8 alter table label  add column lastupdate          TIMESTAMP WITH TIME ZONE DEFAULT NOW(); 
    9 alter table track  add column lastupdate          TIMESTAMP WITH TIME ZONE DEFAULT NOW(); 
     5-- Introduces _meta tables containing entities metadata, starting with timestamps 
     6CREATE TABLE artist_meta 
     7( 
     8    id          INTEGER NOT NULL, 
     9    lastupdate  TIMESTAMP WITH TIME ZONE DEFAULT NOW() 
     10); 
     11 
     12CREATE TABLE label_meta 
     13( 
     14    id          INTEGER NOT NULL, 
     15    lastupdate  TIMESTAMP WITH TIME ZONE DEFAULT NOW() 
     16); 
     17 
     18CREATE TABLE track_meta 
     19( 
     20    id          INTEGER NOT NULL, 
     21    lastupdate  TIMESTAMP WITH TIME ZONE DEFAULT NOW() 
     22); 
     23 
     24ALTER TABLE albummeta  ADD COLUMN lastupdate          TIMESTAMP WITH TIME ZONE DEFAULT NOW(); 
     25 
     26-- primary keys 
     27ALTER TABLE artist_meta ADD CONSTRAINT artist_meta_pkey PRIMARY KEY(id); 
     28ALTER TABLE label_meta ADD CONSTRAINT label_meta_pkey PRIMARY KEY(id); 
     29ALTER TABLE track_meta ADD CONSTRAINT track_meta_pkey PRIMARY KEY(id); 
     30 
     31-- Initial population 
     32 
     33INSERT INTO artist_meta (id) SELECT id FROM artist; 
     34INSERT INTO label_meta (id) SELECT id FROM label; 
     35INSERT INTO track_meta (id) SELECT id FROM track; 
    1036 
    1137COMMIT; 

  • mb_server/trunk/admin/sql/updates/20080610-2.sql

    r9871 r10196  
    1010-- create triggers 
    1111 
     12-- foreign keys 
     13ALTER TABLE artist_meta 
     14        ADD CONSTRAINT fk_artist_meta_artist 
     15        FOREIGN KEY (id) 
     16        REFERENCES artist(id); 
     17         
     18ALTER TABLE label_meta 
     19        ADD CONSTRAINT fk_label_meta_label 
     20        FOREIGN KEY (id) 
     21        REFERENCES label(id); 
     22         
     23ALTER TABLE track_meta 
     24        ADD CONSTRAINT fk_track_meta_track 
     25        FOREIGN KEY (id) 
     26        REFERENCES track(id); 
     27         
    1228COMMIT; 
    1329