Changeset 10211

Show
Ignore:
Timestamp:
2008-08-08 21:53:30 (4 months ago)
Author:
murdos
Message:

Commit new design for ratings tables.

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • mb_server/branches/Rating-BRANCH/admin/sql/updates/20080707-1.sql

    r10120 r10211  
    55BEGIN; 
    66 
    7 -- The aggregate rating tables     
     7-- Add aggregate rating fields on _meta tables     
    88     
    9 CREATE TABLE artist_rating 
    10 ( 
    11     artist              INTEGER NOT NULL, 
    12     rating             INTEGER NOT NULL, 
    13     count             INTEGER NOT NULL 
    14 ); 
     9ALTER table artist_meta ADD COLUMN rating       REAL; 
     10ALTER table artist_meta ADD COLUMN rating_count INTEGER DEFAULT 0; 
    1511     
    16 CREATE TABLE release_rating 
    17 ( 
    18     release           INTEGER NOT NULL, 
    19     rating             INTEGER NOT NULL, 
    20     count             INTEGER NOT NULL 
    21 ); 
     12ALTER table label_meta ADD COLUMN rating       REAL; 
     13ALTER table label_meta ADD COLUMN rating_count INTEGER DEFAULT 0; 
    2214     
    23 CREATE TABLE track_rating 
    24 ( 
    25     track             INTEGER NOT NULL, 
    26     rating            INTEGER NOT NULL, 
    27     count            INTEGER NOT NULL 
    28 ); 
     15ALTER table track_meta ADD COLUMN rating       REAL; 
     16ALTER table track_meta ADD COLUMN rating_count INTEGER DEFAULT 0; 
    2917     
    30 CREATE TABLE label_rating 
    31 ( 
    32     label             INTEGER NOT NULL, 
    33     rating            INTEGER NOT NULL, 
    34     count            INTEGER NOT NULL 
    35 ); 
     18ALTER table albummeta ADD COLUMN rating       REAL; 
     19ALTER table albummeta ADD COLUMN rating_count INTEGER DEFAULT 0; 
    3620     
    37     -- primary keys 
    38      
    39 ALTER TABLE artist_rating ADD CONSTRAINT artist_rating_pkey PRIMARY KEY (artist, rating); 
    40 ALTER TABLE release_rating ADD CONSTRAINT release_rating_pkey PRIMARY KEY (release, rating); 
    41 ALTER TABLE track_rating ADD CONSTRAINT track_rating_pkey PRIMARY KEY (track, rating); 
    42 ALTER TABLE label_rating ADD CONSTRAINT label_rating_pkey PRIMARY KEY (label, rating); 
    43      
    44     -- indexes 
    45      
    46 CREATE INDEX artist_rating_idx_artist ON artist_rating (artist); 
    47 CREATE INDEX artist_rating_idx_rating ON artist_rating (rating); 
    48 CREATE INDEX release_rating_idx_release ON release_rating (release); 
    49 CREATE INDEX release_rating_idx_rating ON release_rating (rating); 
    50 CREATE INDEX track_rating_idx_track ON track_rating (track); 
    51 CREATE INDEX track_rating_idx_rating ON track_rating (rating); 
    52 CREATE INDEX label_rating_idx_label ON label_rating (label); 
    53 CREATE INDEX label_rating_idx_rating ON label_rating (rating); 
    54          
    55         -- foreign keys 
    56 ALTER TABLE artist_rating 
    57         ADD CONSTRAINT fk_artist_rating_artist 
    58         FOREIGN KEY (artist) 
    59         REFERENCES artist(id); 
    60          
    61          
    62 ALTER TABLE release_rating 
    63         ADD CONSTRAINT fk_release_rating_release 
    64         FOREIGN KEY (release) 
    65         REFERENCES album(id); 
    66          
    67          
    68 ALTER TABLE track_rating 
    69         ADD CONSTRAINT fk_track_rating_track 
    70         FOREIGN KEY (track) 
    71         REFERENCES track(id); 
    72          
    73          
    74 ALTER TABLE label_rating 
    75         ADD CONSTRAINT fk_label_rating_track 
    76         FOREIGN KEY (label) 
    77         REFERENCES label(id); 
    78          
    7921COMMIT; 

  • mb_server/branches/Rating-BRANCH/admin/sql/updates/20080707-2.sql

    r10120 r10211  
    99CREATE TABLE artist_rating_raw 
    1010( 
    11     artist              INTEGER NOT NULL, 
    12     rating             INTEGER NOT NULL, 
    13     moderator     INTEGER NOT NULL 
     11    artist      INTEGER NOT NULL, 
     12    editor      INTEGER NOT NULL, 
     13    rating      INTEGER NOT NULL 
    1414); 
    1515     
    1616CREATE TABLE release_rating_raw 
    1717( 
    18     release          INTEGER NOT NULL, 
    19     rating            INTEGER NOT NULL, 
    20     moderator    INTEGER NOT NULL 
     18    release     INTEGER NOT NULL, 
     19    editor      INTEGER NOT NULL, 
     20    rating      INTEGER NOT NULL 
    2121); 
    2222     
    2323CREATE TABLE track_rating_raw 
    2424( 
    25     track            INTEGER NOT NULL, 
    26     rating           INTEGER NOT NULL, 
    27     moderator   INTEGER NOT NULL 
     25    track       INTEGER NOT NULL, 
     26    editor      INTEGER NOT NULL, 
     27    rating      INTEGER NOT NULL 
    2828); 
    2929     
    3030CREATE TABLE label_rating_raw 
    3131( 
    32     label            INTEGER NOT NULL, 
    33     rating            INTEGER NOT NULL, 
    34     moderator    INTEGER NOT NULL 
     32    label       INTEGER NOT NULL, 
     33    editor      INTEGER NOT NULL, 
     34    rating      INTEGER NOT NULL 
    3535); 
    3636     
    3737    -- primary keys 
    3838     
    39 ALTER TABLE artist_rating_raw ADD CONSTRAINT artist_rating_raw_pkey PRIMARY KEY (artist, rating, moderator); 
    40 ALTER TABLE release_rating_raw ADD CONSTRAINT release_rating_raw_pkey PRIMARY KEY (release, rating, moderator); 
    41 ALTER TABLE track_rating_raw ADD CONSTRAINT track_rating_raw_pkey PRIMARY KEY (track, rating, moderator); 
    42 ALTER TABLE label_rating_raw ADD CONSTRAINT label_rating_raw_pkey PRIMARY KEY (label, rating, moderator); 
     39ALTER TABLE artist_rating_raw ADD CONSTRAINT artist_rating_raw_pkey PRIMARY KEY (artist, editor); 
     40ALTER TABLE release_rating_raw ADD CONSTRAINT release_rating_raw_pkey PRIMARY KEY (release, editor); 
     41ALTER TABLE track_rating_raw ADD CONSTRAINT track_rating_raw_pkey PRIMARY KEY (track, editor); 
     42ALTER TABLE label_rating_raw ADD CONSTRAINT label_rating_raw_pkey PRIMARY KEY (label, editor); 
    4343     
    4444    -- indexes 
    4545         
    4646CREATE INDEX artist_rating_raw_idx_artist ON artist_rating_raw (artist); 
    47 CREATE INDEX artist_rating_raw_idx_rating ON artist_rating_raw (rating); 
    48 CREATE INDEX artist_rating_raw_idx_moderator ON artist_rating_raw (moderator); 
     47CREATE INDEX artist_rating_raw_idx_editor ON artist_rating_raw (editor); 
    4948         
    5049CREATE INDEX release_rating_raw_idx_release ON release_rating_raw (release); 
    51 CREATE INDEX release_rating_raw_idx_rating ON release_rating_raw (rating); 
    52 CREATE INDEX release_rating_raw_idx_moderator ON release_rating_raw (moderator); 
     50CREATE INDEX release_rating_raw_idx_editor ON release_rating_raw (editor); 
    5351         
    5452CREATE INDEX track_rating_raw_idx_track ON track_rating_raw (track); 
    55 CREATE INDEX track_rating_raw_idx_rating ON track_rating_raw (rating); 
    56 CREATE INDEX track_rating_raw_idx_moderator ON track_rating_raw (moderator); 
     53CREATE INDEX track_rating_raw_idx_editor ON track_rating_raw (editor); 
    5754         
    5855CREATE INDEX label_rating_raw_idx_label ON label_rating_raw (label); 
    59 CREATE INDEX label_rating_raw_idx_rating ON label_rating_raw (rating); 
    60 CREATE INDEX label_rating_raw_idx_moderator ON label_rating_raw (moderator); 
     56CREATE INDEX label_rating_raw_idx_editor ON label_rating_raw (editor); 
    6157         
    6258COMMIT; 

  • mb_server/branches/Rating-BRANCH/admin/sql/vertical/rawdata/CreateIndexes.sql

    r9386 r10211  
    99-- Alphabetical order by table 
    1010 
     11CREATE INDEX artist_rating_raw_idx_artist ON artist_rating_raw (artist); 
     12CREATE INDEX artist_rating_raw_idx_editor ON artist_rating_raw (editor); 
     13         
    1114CREATE INDEX artist_tag_raw_idx_artist ON artist_tag_raw (artist); 
    1215CREATE INDEX artist_tag_raw_idx_tag ON artist_tag_raw (tag); 
    1316CREATE INDEX artist_tag_raw_idx_moderator ON artist_tag_raw (moderator); 
    1417 
     18CREATE INDEX release_rating_raw_idx_release ON release_rating_raw (release); 
     19CREATE INDEX release_rating_raw_idx_editor ON release_rating_raw (editor); 
     20         
    1521CREATE INDEX release_tag_raw_idx_release ON release_tag_raw (release); 
    1622CREATE INDEX release_tag_raw_idx_tag ON release_tag_raw (tag); 
    1723CREATE INDEX release_tag_raw_idx_moderator ON release_tag_raw (moderator); 
    1824 
     25CREATE INDEX track_rating_raw_idx_track ON track_rating_raw (track); 
     26CREATE INDEX track_rating_raw_idx_editor ON track_rating_raw (editor); 
     27         
    1928CREATE INDEX track_tag_raw_idx_track ON track_tag_raw (track); 
    2029CREATE INDEX track_tag_raw_idx_tag ON track_tag_raw (tag); 
    2130CREATE INDEX track_tag_raw_idx_moderator ON track_tag_raw (moderator); 
     31 
     32CREATE INDEX label_rating_raw_idx_label ON label_rating_raw (label); 
     33CREATE INDEX label_rating_raw_idx_editor ON label_rating_raw (editor); 
    2234 
    2335CREATE INDEX label_tag_raw_idx_label ON label_tag_raw (label); 

  • mb_server/branches/Rating-BRANCH/admin/sql/vertical/rawdata/CreatePrimaryKeys.sql

    r9448 r10211  
    33-- Alphabetical order by table 
    44 
     5ALTER TABLE artist_rating_raw ADD CONSTRAINT artist_rating_raw_pkey PRIMARY KEY (artist, editor); 
    56ALTER TABLE artist_tag_raw ADD CONSTRAINT artist_tag_raw_pkey PRIMARY KEY (artist, tag, moderator); 
     7ALTER TABLE release_rating_raw ADD CONSTRAINT release_rating_raw_pkey PRIMARY KEY (release, editor); 
    68ALTER TABLE release_tag_raw ADD CONSTRAINT release_tag_raw_pkey PRIMARY KEY (release, tag, moderator); 
     9ALTER TABLE track_rating_raw ADD CONSTRAINT track_rating_raw_pkey PRIMARY KEY (track, editor); 
    710ALTER TABLE track_tag_raw ADD CONSTRAINT track_tag_raw_pkey PRIMARY KEY (track, tag, moderator); 
     11ALTER TABLE label_rating_raw ADD CONSTRAINT label_rating_raw_pkey PRIMARY KEY (label, editor); 
    812ALTER TABLE label_tag_raw ADD CONSTRAINT label_tag_raw_pkey PRIMARY KEY (label, tag, moderator); 
    913 

  • mb_server/branches/Rating-BRANCH/admin/sql/vertical/rawdata/CreateTables.sql

    r9386 r10211  
    22BEGIN; 
    33 
     4CREATE TABLE artist_rating_raw 
     5( 
     6    artist              INTEGER NOT NULL, 
     7    editor              INTEGER NOT NULL, 
     8    rating              INTEGER NOT NULL 
     9); 
     10     
    411CREATE TABLE artist_tag_raw 
    512( 
    …… 
    916); 
    1017 
     18CREATE TABLE release_rating_raw 
     19( 
     20    release             INTEGER NOT NULL, 
     21    editor              INTEGER NOT NULL, 
     22    rating              INTEGER NOT NULL 
     23); 
     24     
    1125CREATE TABLE release_tag_raw 
    1226( 
    …… 
    1630); 
    1731 
     32CREATE TABLE track_rating_raw 
     33( 
     34    track               INTEGER NOT NULL, 
     35    editor              INTEGER NOT NULL, 
     36    rating              INTEGER NOT NULL 
     37); 
     38     
    1839CREATE TABLE track_tag_raw 
    1940( 
    …… 
    2344); 
    2445 
     46CREATE TABLE label_rating_raw 
     47( 
     48    label               INTEGER NOT NULL, 
     49    editor              INTEGER NOT NULL, 
     50    rating              INTEGER NOT NULL 
     51); 
     52     
    2553CREATE TABLE label_tag_raw 
    2654(