Title: ClassiCollect Database Schema Index for classical



Dumped on 2003-07-30

Index of database - classical

Tables

Views

Table: artist

artist Structure
F-Key Name Type Description
artist_id serial UNIQUE
artist_name text PRIMARY KEY
role.role_id role_id integer PRIMARY KEY
abbreviation text
display boolean DEFAULT 'true'

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: artist_filtered_view

artist_filtered_view Structure
F-Key Name Type Description
artist_id integer
role_id integer
artist_name text

 

		
SELECT artist.artist_id
, artist.role_id
, artist.artist_name 
FROM artist 
WHERE artist.display 
ORDER BY"replace"
(artist.artist_name
     ,','::text
     ,'0'::text
);

Index - Schema public

Table: artist_sorted_view

artist_sorted_view Structure
F-Key Name Type Description
artist_id integer
role_id integer
artist_name text

 

		
SELECT artist.artist_id
, artist.role_id
, artist.artist_name 
FROM artist 
ORDER BY"replace"
(artist.artist_name
     ,','::text
     ,'0'::text
);

Index - Schema public

Table: artist_view

artist_view Structure
F-Key Name Type Description
role_id integer
artist_id integer
artist_name text

 

		
SELECT DISTINCT indistinct_artist_view.role_id
, indistinct_artist_view.artist_id
, indistinct_artist_view.artist_name 
FROM indistinct_artist_view 
ORDER BY indistinct_artist_view.role_id
, indistinct_artist_view.artist_id
, indistinct_artist_view.artist_name;

Index - Schema public

Table: composer

composer Structure
F-Key Name Type Description
composer_id serial UNIQUE
composer_name text PRIMARY KEY
display boolean DEFAULT 'true'

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: composer_filtered_view

composer_filtered_view Structure
F-Key Name Type Description
composer_id integer
composer_name text

 

		
SELECT composer.composer_id
, composer.composer_name 
FROM composer 
WHERE composer.display 
ORDER BY"replace"
(composer.composer_name
     ,','::text
     ,'0'::text
);

Index - Schema public

Table: composer_sorted_view

composer_sorted_view Structure
F-Key Name Type Description
composer_id integer
composer_name text
display boolean

 

		
SELECT composer.composer_id
, composer.composer_name
, composer.display 
FROM composer 
ORDER BY composer.display DESC
,"replace"
(composer.composer_name
     ,','::text
     ,'0'::text
);

Index - Schema public

Table: composer_view

composer_view Structure
F-Key Name Type Description
composer_id integer
composer_name text

 

		
SELECT DISTINCT indistinct_composer_view.composer_id
, indistinct_composer_view.composer_name 
FROM indistinct_composer_view 
ORDER BY indistinct_composer_view.composer_id
, indistinct_composer_view.composer_name;

Index - Schema public

Table: disc

disc Structure
F-Key Name Type Description
disc_id serial UNIQUE
label.label_id label_id integer PRIMARY KEY
catalog_no text PRIMARY KEY

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: disc_info

disc_info Structure
F-Key Name Type Description
disc_info_id serial
disc.disc_id disc_id integer PRIMARY KEY
disc_title text
release_year character(4)
media.media_id media_id integer
disc_count smallint DEFAULT 1
shelf_space real DEFAULT 1.0
price numeric(5,2)
purchase_date character varying(10)
lent_to text
comments text

Index - Schema public

Table: disc_info_view

disc_info_view Structure
F-Key Name Type Description
disc_info_id integer
disc_id integer
disc_title text
file_under text
release_year character(4)
"?column?" text
disc_count smallint
shelf_space real
price numeric(5,2)
purchase_date character varying(10)
lent_to text
comments text

 

		
SELECT d.disc_info_id
, d.disc_id
, d.disc_title
, (
SELECT f.description 
  FROM file_under_view f 
 WHERE (f.disc_id = d.disc_id)
) AS file_under
, d.release_year
, (
SELECT m.media_name 
  FROM media m 
 WHERE (m.media_id = d.media_id)
)
, d.disc_count
, d.shelf_space
, d.price
, d.purchase_date
, d.lent_to
, d.comments 
FROM disc_info d;

Index - Schema public

Table: disc_recording_view

disc_recording_view Structure
F-Key Name Type Description
werk_id integer
disc_id integer
description text
label_name text
catalog_no text

 

		
SELECT r.werk_id
, d.disc_id
, r.description
, l.label_name
, d.catalog_no 
FROM disc d
, recording r
, label l 
WHERE (
     (l.label_id = d.label_id)
   AND (r.disc_id = d.disc_id)
)
ORDER BY r.description
, l.label_name
, d.catalog_no;

Index - Schema public

Table: disc_view

disc_view Structure
F-Key Name Type Description
werk_id integer
recording_id integer
description text
label_name text
catalog_no text

 

		
SELECT r.werk_id
, r.recording_id
, r.description
, l.label_name
, d.catalog_no 
FROM recording r
, disc d
, label l 
WHERE (
     (l.label_id = d.label_id)
   AND (r.disc_id = d.disc_id)
)
ORDER BY r.description
, l.label_name
, d.catalog_no;

Index - Schema public

Table: file_under

file_under Structure
F-Key Name Type Description
file_under_id serial UNIQUE
recording.disc_id#2 disc_id integer PRIMARY KEY
recording.recording_id#2 recording_id integer
performance.performance_id#1 performance_id integer

Index - Schema public

Table: file_under_view

file_under_view Structure
F-Key Name Type Description
disc_id integer
description text

 

		
SELECT f.disc_id
, CASE WHEN 
(
     (
      SELECT (
                 (split_part
                       (a.artist_name
                             ,','::text
                             , 1
                       ) || 
                    ', '::text
                 ) || rl.role_name
           )
        FROM role rl
           , artist a
           , performance p 
       WHERE (
                 (
                       (rl.role_id = a.role_id)
                     AND (a.artist_id = p.artist_id)
                 )
               AND (p.performance_id = f.performance_id)
           )
     ) IS NOT NULL
) THEN 
(
SELECT (
           (split_part
                 (a.artist_name
                       ,','::text
                       , 1
                 ) || 
              ', '::text
           ) || rl.role_name
     )
  FROM role rl
     , artist a
     , performance p 
 WHERE (
           (
                 (rl.role_id = a.role_id)
               AND (a.artist_id = p.artist_id)
           )
         AND (p.performance_id = f.performance_id)
     )
) WHEN 
(
     (
      SELECT (
                 (split_part
                       (c.composer_name
                             ,','::text
                             , 1
                       ) || ': '::text
                 ) || w.title
           )
        FROM composer c
           , werk w
           , recording r 
       WHERE (
                 (
                       (c.composer_id = w.composer_id)
                     AND (w.werk_id = r.werk_id)
                 )
               AND (r.recording_id = f.recording_id)
           )
     ) IS NOT NULL
) THEN 
(
SELECT (
           (split_part
                 (c.composer_name
                       ,','::text
                       , 1
                 ) || ': '::text
           ) || w.title
     )
  FROM composer c
     , werk w
     , recording r 
 WHERE (
           (
                 (c.composer_id = w.composer_id)
               AND (w.werk_id = r.werk_id)
           )
         AND (r.recording_id = f.recording_id)
     )
) ELSE NULL::text END AS description 
FROM file_under f;

Index - Schema public

Table: filed_under_artist

filed_under_artist Structure
F-Key Name Type Description
filed_under_artist_id serial
disc.disc_id disc_id integer UNIQUE PRIMARY KEY
artist.artist_id artist_id integer PRIMARY KEY

Index - Schema public

Table: filed_under_werk

filed_under_werk Structure
F-Key Name Type Description
filed_under_werk_id serial
disc.disc_id disc_id integer UNIQUE PRIMARY KEY
werk.werk_id werk_id integer PRIMARY KEY

Index - Schema public

Table: flat_artist_view

flat_artist_view Structure
F-Key Name Type Description
artist_id integer
artist_name text
role_name text

 

		
SELECT a.artist_id
, a.artist_name
, r.role_name 
FROM (artist a 
  JOIN role r 
 USING (role_id)
)
ORDER BY a.artist_name
, r.role_name;

Index - Schema public

Table: flat_recording_view

flat_recording_view Structure
F-Key Name Type Description
composer_name text
title text
description text
label_name text
catalog_no text

 

		
SELECT c.composer_name
, w.title
, r.description
, l.label_name
, d.catalog_no 
FROM (
     (
           (
                 (recording r 
                    JOIN werk w 
                   USING (werk_id)
                 )
              JOIN composer c 
             USING (composer_id)
           )
        JOIN disc d 
       USING (disc_id)
     )
  JOIN label l 
 USING (label_id)
)
ORDER BY c.composer_name
, w.title
, r.description
, l.label_name
, d.catalog_no;

Index - Schema public

Table: genre

genre Structure
F-Key Name Type Description
genre_id serial UNIQUE
genre_name text PRIMARY KEY
display boolean DEFAULT 'true'

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: genre_filtered_view

genre_filtered_view Structure
F-Key Name Type Description
genre_id integer
genre_name text

 

		
SELECT genre.genre_id
, genre.genre_name 
FROM genre 
WHERE genre.display 
ORDER BY genre.genre_name;

Index - Schema public

Table: indistinct_artist_view

indistinct_artist_view Structure
F-Key Name Type Description
role_id integer
artist_id integer
artist_name text

 

		
SELECT a.role_id
, a.artist_id
, a.artist_name 
FROM artist a
, recording r
, performance p 
WHERE (
     (a.artist_id = p.artist_id)
   AND (p.recording_id = r.recording_id)
)
ORDER BY"replace"
(a.artist_name
     ,','::text
     ,'0'::text
);

Index - Schema public

Table: indistinct_composer_view

indistinct_composer_view Structure
F-Key Name Type Description
composer_id integer
composer_name text

 

		
SELECT c.composer_id
, c.composer_name 
FROM composer c
, werk w
, recording r 
WHERE (
     (c.composer_id = w.composer_id)
   AND (w.werk_id = r.werk_id)
)
ORDER BY"replace"
(c.composer_name
     ,','::text
     ,'0'::text
);

Index - Schema public

Table: indistinct_werk_view

indistinct_werk_view Structure
F-Key Name Type Description
composer_id integer
werk_id integer
title text

 

		
SELECT w.composer_id
, r.werk_id
, w.title 
FROM werk w
, disc d
, recording r 
WHERE (r.werk_id = w.werk_id)
ORDER BY"replace"
(w.title
     ,' '::text
     ,'0'::text
);

Index - Schema public

Table: label

label Structure
F-Key Name Type Description
label_id serial UNIQUE
label_name text PRIMARY KEY
display boolean DEFAULT 'true'

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: label_filtered_view

label_filtered_view Structure
F-Key Name Type Description
label_name text
label_id integer

 

		
SELECT label.label_name
, label.label_id 
FROM label 
WHERE label.display 
ORDER BY label.label_name;

Index - Schema public

Table: label_view

label_view Structure
F-Key Name Type Description
label_id integer
label_name text

 

		
SELECT DISTINCT l.label_id
, l.label_name 
FROM label l
, disc d 
WHERE (l.label_id = d.label_id)
ORDER BY l.label_name
, l.label_id;

Index - Schema public

Table: media

media Structure
F-Key Name Type Description
media_id serial UNIQUE
media_name text PRIMARY KEY DEFAULT 'cd'

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: musical_key

musical_key Structure
F-Key Name Type Description
musical_key_id serial UNIQUE
key_name text PRIMARY KEY

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: performance

performance Structure
F-Key Name Type Description
performance_id serial UNIQUE UNIQUE#1
recording.recording_id recording_id integer PRIMARY KEY UNIQUE#1
artist.artist_id artist_id integer PRIMARY KEY

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: performance_info

performance_info Structure
F-Key Name Type Description
performance_info_id serial
performance.performance_id performance_id integer PRIMARY KEY
comments text

Index - Schema public

Table: performance_member

performance_member Structure
F-Key Name Type Description
performance_member_id serial
performance.performance_id performance_id integer PRIMARY KEY
artist.artist_id artist_id integer PRIMARY KEY

Index - Schema public

Table: performance_view

performance_view Structure
F-Key Name Type Description
performance_id integer
recording_id integer
artist_name text
role_name text

 

		
SELECT p.performance_id
, p.recording_id
, a.artist_name
, r.role_name 
FROM (
     (performance p 
        JOIN artist a 
       USING (artist_id)
     )
  JOIN role r 
 USING (role_id)
)
ORDER BY a.artist_name
, r.role_name;

Index - Schema public

Table: preordered_recorded_werk_view

preordered_recorded_werk_view Structure
F-Key Name Type Description
werk_id integer
artist_id integer
composer_name text
title text

 

		
SELECT DISTINCT w.werk_id
, p.artist_id
, c.composer_name
, w.title 
FROM werk w
, composer c
, recording r
, performance p 
WHERE (
     (
           (p.recording_id = r.recording_id)
         AND (w.werk_id = r.werk_id)
     )
   AND (c.composer_id = w.composer_id)
)
ORDER BY w.werk_id
, p.artist_id
, c.composer_name
, w.title;

Index - Schema public

Table: producer

producer Structure
F-Key Name Type Description
producer_id serial UNIQUE
producer_name text PRIMARY KEY

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: recorded_werk_view

recorded_werk_view Structure
F-Key Name Type Description
werk_id integer
artist_id integer
composer_name text
title text

 

		
SELECT preordered_recorded_werk_view.werk_id
, preordered_recorded_werk_view.artist_id
, preordered_recorded_werk_view.composer_name
, preordered_recorded_werk_view.title 
FROM preordered_recorded_werk_view 
ORDER BY"replace"
(preordered_recorded_werk_view.composer_name
     ,','::text
     ,'0'::text
)
,"replace"
(preordered_recorded_werk_view.title
     ,' '::text
     ,'0'::text
);

Index - Schema public

Table: recording

recording Structure
F-Key Name Type Description
recording_id serial UNIQUE#1 UNIQUE
disc.disc_id disc_id integer PRIMARY KEY UNIQUE#1
werk.werk_id werk_id integer PRIMARY KEY
description text PRIMARY KEY DEFAULT ''

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: recording_info

recording_info Structure
F-Key Name Type Description
recording_info_id serial
recording.recording_id recording_id integer PRIMARY KEY
recording_date character varying(10) DEFAULT ''
duration interval
venue.venue_id venue_id integer
tonmeister.tonmeister_id tonmeister_id integer
producer.producer_id producer_id integer
transfer_guy.transfer_guy_id transfer_guy_id integer
channels smallint DEFAULT 2
spars.spars_id spars_id integer
rating text
original_issue text
comments text

Index - Schema public

Table: recording_info_view

recording_info_view Structure
F-Key Name Type Description
recording_info_id integer
recording_id integer
recording_date character varying(10)
duration interval
venue_name text
tonmeister_name text
producer_name text
transfer_guy_name text
spars_name bpchar
channels smallint
rating text
original_issue text
comments text

 

		
SELECT ri.recording_info_id
, ri.recording_id
, ri.recording_date
, ri.duration
, (
SELECT v.venue_name 
  FROM venue v 
 WHERE (ri.venue_id = v.venue_id)
) AS venue_name
, (
SELECT t.tonmeister_name 
  FROM tonmeister t 
 WHERE (ri.tonmeister_id = t.tonmeister_id)
) AS tonmeister_name
, (
SELECT p.producer_name 
  FROM producer p 
 WHERE (ri.producer_id = p.producer_id)
) AS producer_name
, (
SELECT tg.transfer_guy_name 
  FROM transfer_guy tg 
 WHERE (ri.transfer_guy_id = tg.transfer_guy_id)
) AS transfer_guy_name
, (
SELECT s.spars_name 
  FROM spars s 
 WHERE (ri.spars_id = s.spars_id)
) AS spars_name
, ri.channels
, ri.rating
, ri.original_issue
, ri.comments 
FROM recording_info ri;

Index - Schema public

Table: recording_view

recording_view Structure
F-Key Name Type Description
recording_id integer
disc_id integer
composer_name text
title text
description text

 

		
SELECT r.recording_id
, r.disc_id
, c.composer_name
, w.title
, r.description 
FROM (
     (recording r 
        JOIN werk w 
       USING (werk_id)
     )
  JOIN composer c 
 USING (composer_id)
)
ORDER BY"replace"
(c.composer_name
     ,','::text
     ,'0'::text
)
,"replace"
(w.title
     ,' '::text
     ,'0'::text
)
, r.description;

Index - Schema public

Table: role

role Structure
F-Key Name Type Description
role_id serial UNIQUE
role_name text PRIMARY KEY
priority integer
display boolean DEFAULT 'true'

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: role_filtered_view

role_filtered_view Structure
F-Key Name Type Description
role_id integer
role_name text

 

		
SELECT role.role_id
, role.role_name 
FROM role 
WHERE role.display 
ORDER BY role.role_name;

Index - Schema public

Table: role_sorted_view

role_sorted_view Structure
F-Key Name Type Description
role_id integer
role_name text
priority integer
display boolean

 

		
SELECT role.role_id
, role.role_name
, role.priority
, role.display 
FROM role 
ORDER BY role.display DESC
, role.role_name;

Index - Schema public

Table: role_view

role_view Structure
F-Key Name Type Description
role_id integer
role_name text

 

		
SELECT DISTINCT rl.role_id
, rl.role_name 
FROM role rl
, artist a
, performance p 
WHERE (
     (rl.role_id = a.role_id)
   AND (a.artist_id = p.artist_id)
)
ORDER BY rl.role_name
, rl.role_id;

Index - Schema public

Table: spars

spars Structure
F-Key Name Type Description
spars_id serial UNIQUE
spars_name character(3) PRIMARY KEY DEFAULT 'ADD'

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: timing

timing Structure
F-Key Name Type Description
timing_id serial UNIQUE
movement_no smallint PRIMARY KEY
recording.recording_id recording_id integer PRIMARY KEY
movement_time interval

Index - Schema public

Table: tonmeister

tonmeister Structure
F-Key Name Type Description
tonmeister_id serial UNIQUE
tonmeister_name text PRIMARY KEY

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: transfer_guy

transfer_guy Structure
F-Key Name Type Description
transfer_guy_id serial UNIQUE
transfer_guy_name text PRIMARY KEY

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: venue

venue Structure
F-Key Name Type Description
venue_id serial UNIQUE
venue_name text PRIMARY KEY

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: werk

werk Structure
F-Key Name Type Description
werk_id serial UNIQUE
composer.composer_id composer_id integer PRIMARY KEY
title text PRIMARY KEY
display boolean DEFAULT 'true'

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: werk_filtered_view

werk_filtered_view Structure
F-Key Name Type Description
werk_id integer
composer_id integer
title text

 

		
SELECT werk.werk_id
, werk.composer_id
, werk.title 
FROM werk 
WHERE werk.display 
ORDER BY"replace"
(werk.title
     ,' '::text
     ,'0'::text
);

Index - Schema public

Table: werk_info

werk_info Structure
F-Key Name Type Description
werk_info_id serial
werk.werk_id werk_id integer PRIMARY KEY
subtitle text
werk_no text
musical_key.musical_key_id musical_key_id integer
genre.genre_id genre_id integer

Index - Schema public

Table: werk_info_view

werk_info_view Structure
F-Key Name Type Description
werk_info_id integer
werk_id integer
subtitle text
werk_no text
key_name text
genre_name text

 

		
SELECT w.werk_info_id
, w.werk_id
, w.subtitle
, w.werk_no
, (
SELECT m.key_name 
  FROM musical_key m 
 WHERE (m.musical_key_id = w.musical_key_id)
) AS key_name
, (
SELECT g.genre_name 
  FROM genre g 
 WHERE (g.genre_id = w.genre_id)
) AS genre_name 
FROM werk_info w;

Index - Schema public

Table: werk_sorted_view

werk_sorted_view Structure
F-Key Name Type Description
werk_id integer
composer_id integer
title text
display boolean

 

		
SELECT werk.werk_id
, werk.composer_id
, werk.title
, werk.display 
FROM werk 
ORDER BY werk.display DESC
,"replace"
(werk.title
     ,' '::text
     ,'0'::text
);

Index - Schema public

Table: werk_view

werk_view Structure
F-Key Name Type Description
composer_id integer
werk_id integer
title text

 

		
SELECT DISTINCT indistinct_werk_view.composer_id
, indistinct_werk_view.werk_id
, indistinct_werk_view.title 
FROM indistinct_werk_view 
ORDER BY indistinct_werk_view.composer_id
, indistinct_werk_view.werk_id
, indistinct_werk_view.title;

Index - Schema public

W3C HTML 4.01 Strict