Postgres data sources for CineFiles web sites

New data sources are available for the web sites to display information managed in the CineFiles CollectionSpace system. Their design is based on the requirements for the IST-developed site, but they have been refactored extensively. These data sources are described below. A separate document will describe how these data sources are refreshed (TBD).

doclist_view

doclist_view is a table despite the name. The name was retained in order to simplify refactoring the IST developed CineFiles web site. This is the primary source for searching documents (keyword search and field-based search). It is also a primary source for the document citation that is displayed by the function doc_detail_summary.

Schema

Field	Type	Notes
doc_id	int8
doctitle	varchar
doctype	text
pages	int8
pg_info	varchar
source	text	usually the publisher of the document
src_id	text	source identifier used behind the scenes
author	text	Can be multiple pipe-separated values.
name_id	text	7/31/2014: Can be multiple pipe-separated values
doclanguage	text	Can be multiple pipe-separated values.
pubdate	varchar
code	int4	Access code for this document: 0 = PFA Staff Only null = PFA Staff Only 1 = In House Only 2 = Campus (UCB) 3 = Education (.edu) 4 = World
cast_cr	bool
tech_cr	bool
bx_info	bool
filmog	bool
dist_co	bool
prod_co	bool
costinfo	bool
illust	bool
biblio	bool
docurl	varchar	URL provided for direct access or further information
pubdatescalar	timestamp	publication date
srcurl	varchar
docsubject	text	Subject of document. Can be multiple pipe-separated values.
docnamesubject	text	Named subjects (person or organization) in document. Can be multiple pipe-separated values.
updatedat	timestamp	date of last update to document

Notes

Resolved - one row per document. NOTE: This table is supposed to have one row per document. As of 3/19/2014 there are 884 documents with two rows, due to the fact that some sources have more than one srcUrl value. That will be fixed in a future version.

Example rows

2

The abyss

review

2

New Yorker, The

organization484

Terrence Rafferty

person214

English

1989 Sep 04

0

FALSE

9/3/89 17:00

(null)

3

The abyss

review

2

Film Journal International

organization15066

Kevin Lally

person215

English

1989 Sep

4

TRUE

FALSE

TRUE

FALSE

8/31/89 17:00

(null)

4

There is a tide

review

1

p. 61

Village Voice

organization494

Amy Taubin

person216

English

1989 Aug 15

4

FALSE

TRUE

FALSE

8/14/89 17:00

http://www.villagevoice.com/

(null)

12

The abyss: a foray into deep waters

review

2

p. H-15

New York Times

organization482

Aljean Harmetz

person217

English

1989 Aug 06

0

FALSE

TRUE

FALSE

8/5/89 17:00

(null)

Underwater cinematography

James Cameron|Ed Harris

13

Playing with water

review

1

p. H-16

New York Times

organization482

David A. Kaplan

person218

English

1989 Aug 06

0

FALSE

8/5/89 17:00

(null)

Special effects

Industrial Light and Magic (Studio)

filmlist_view

filmlist_view is a table despite the name. The name was retained in order to simplify refactoring the IST developed CineFiles web site. This is the primary source for searching films (keyword search and field-based search). It is also a primary source for the film citation produced by the function film_detail_summary.

Schema

Field	Type	Notes
film_id	varchar
name_id	text	Name identifiers for director. Can be multiple pipe-separated values, but NOTE that these strings have leading and trailing pipes to allow "like" searches to work on these.
doc_count	int8	Number of documents associated with this film
filmtitle	varchar	Primary title
country	text	Can be multiple pipe-separated values.
filmyear	int4	Single 4-digit year. NOTE: Some films have multiple rows in this table because of multiple release years. In order to allow numeric search operations such as between and greater than, these were not generated as pipe-separated values. E.g., see pfafilm31474 below.
director	text	Can be multiple pipe-separated values.
filmlanguage	text	Can be multiple pipe-separated values.
prodco	text	Can be multiple pipe-separated values.
subject	text	Can be multiple pipe-separated values.
genre	text	Can be multiple pipe-separated values.
title	text	Primary and alternate titles. Can be multiple pipe-separated values.
prodco_id	text	Added 7/31/14: Can be multiple pipe-separated values.
updatedat	timestamp	Date record was last updated

Notes

NOTE: Some films have multiple rows in this table because of multiple release years. In order to allow numeric search operations such as between and greater than, these were not generated as pipe-separated values. E.g., see pfafilm31474 below.

Example rows

pfafilm10

|person12|

22

Yukinojo henge (An actor's revenge)

Japan

1963

Kon Ichikawa

Japanese

Daiei Motion Picture Co. Ltd.

Actors -- Japan -- Drama|Kabuki -- Drama|Revenge -- Drama|Melodrama

Melodrama

organization1|etc.

pfafilm100

|person391|

1

World gone wild

(null)

Lee H. Katzin

English

(null)

Drama

World gone wild

organization2

pfafilm1000

|person8886|person8498|

3

Ama

Ghana|Great Britain

1991

Kwesi Owusu|Kwate Nee-Owoo

English

Channel Four Films|Efire Tete Films

Cultural conflict -- Great Britain -- Drama|Ghanaians -- Great Britain -- London -- Drama|Africans -- Great Britain -- London -- Drama|Girls -- Great Britain -- London -- Drama|Ghana -- Religion -- Drama

Drama

Ama

organization3

pfafilm10018

|person383|

17

Insignificance

Great Britain

1985

Nicolas Roeg

English

Recorded Picture Company|Zenith Entertainment Ltd.

Einstein, Albert, 1879-1955 -- Drama|Monroe, Marilyn, 1926-1962 -- Drama|DiMaggio, Joe, 1914-1999 -- Drama|McCarthy, Joseph, 1908-1957 -- Drama|Johnson, Terry, 1949- Insignificance -- Film and video adaptations

Adaptation|Comedy|Drama|Feature

Insignificance

organization4

pfafilm1002

|person3849|

1

Ambar (Amber)

Mexico

1994

Luis Estrada

Spanish

Imcine|Bandidos Films

Adventure and adventurers -- Drama|Amber -- Drama

Drama

Ambar|Amber

etc

pfafilm31474

|person4489|

4

Shift

United States

1972

Ernie Gehr

English

(null)

City traffic

Experimental|Short|Bay Area avant-garde

Shift

etc

pfafilm31474

|person4489|

4

Shift

United States

1973

Ernie Gehr

English

(null)

City traffic

Experimental|Short|Bay Area avant-garde

Shift

etc

pfafilm31474

|person4489|

4

Shift

United States

1974

Ernie Gehr

English

(null)

City traffic

Experimental|Short|Bay Area avant-garde

Shift

etc

filmdocs

filmdocs is a many-to-many table showing the relationships between films and documents. One film can be tied to many documents. One document can be tied to many films. (Used in DocList.java to populate temp tables for searching.) It is also a primary source for the film citation produced by the function film_detail_summary and for the documentation citation produced by the function doc_detail_summary.

Schema

Field	Type	Notes
film_id	varchar
doc_id	int8
entered	unknown	deprecated; might get deleted
modified	unknown	deprecated; might get deleted
entered_by	unknown	deprecated; might get deleted
verified_by	unknown	deprecated; might get deleted
note	unknown	deprecated; might get deleted

Notes

NOTE: Five fields (entered through note) might be deleted once we confirm they are not being used.

Example rows

pfafilm1000	4177	not used	not used	not used	not used	not used
pfafilm1000	4178	not used	not used	not used	not used	not used
pfafilm1000	4179	not used	not used	not used	not used	not used
pfafilm26266	10627	not used	not used	not used	not used	not used
pfafilm26268	10627	not used	not used	not used	not used	not used

personlist

List of person records

Schema

Field	Type	Notes
shortid	varchar	person identifier
personname	varchar	person name
updatedat	timestamp	updated timestamp

organizationlist

List of person records

Schema

Field	Type	Notes
shortid	varchar	organization identifier
orgname	varchar	organization name
updatedat	timestamp	updated timestamp

alldoctitles_view

Used in DocList.java to populate temp tables for searching

allfilmtitles_view

Used in DocList.java to populate temp tables for searching.

docsubjects_view

Used in DocList.java to populate temp tables for searching.

filmgenres

Used in FilmSearchArgs.java

doc_detail_summary (function)

Returns multiple result sets used for document citation. Called by DocDetail.java.

Execution (for doc_id=12):

begin;
select cinefiles_denorm.doc_detail_summary('12', 'document', 'doctitle', 'authors', 'source', 'doctype', 'lang', 'docsubj', 'docnamesubj', 'docfilmsubj', 'docurl');
fetch all in "document";
fetch all in "doctitle";
fetch all in "authors";
fetch all in "source";
fetch all in "doctype";
fetch all in "lang";
fetch all in "docsubj";
fetch all in "docnamesubj";
fetch all in "docfilmsubj";
fetch all in "docurl";
commit;

Note

Some name_id and subj_id fields returned are static '2' because those fields are not used. See example below.

Returns:

content  | doc_id |    date     | pages | pg_info | cast_cr | tech_cr | bx_info
 | filmog | dist_co | prod_co | cost | illust | note
----------+--------+-------------+-------+---------+---------+---------+--------
-+--------+---------+---------+------+--------+------
 Document |     12 | 1989 Aug 06 |     2 | p. H-15 |       0 |       0 |       0
 |      0 |       0 |       0 |    1 |      0 |
(1 row)

    content     |                title
----------------+-------------------------------------
 Document Title | The abyss: a foray into deep waters
(1 row)

     content      | name_id |     author
------------------+---------+----------------
 Document Authors |       2 | Aljean Harmetz
(1 row)

     content     |     src_id      |     source     | srcurl
-----------------+-----------------+----------------+--------
 Document Source | organization482 | New York Times |
(1 row)

    content    |  type
---------------+--------
 Document Type | review
(1 row)

      content       |  lang
--------------------+---------
 Document Languages | English
(1 row)

      content      | subj_id |           subj
-------------------+---------+---------------------------
 Document Subjects |       2 | Underwater cinematography
(1 row)

        content         | name_id |                    namesubj

------------------------+---------+---------------------------------------------
----
 Document Name Subjects |       2 | James Cameron, Kapuskasing, Ontario, Canada
 Document Name Subjects |       2 | Ed Harris, Englewood, New Jersey, United Sta
tes
(2 rows)

        content         | film_id  |            filmsubj
------------------------+----------+--------------------------------
 Document Film Subjects | pfafilm2 | The abyss, James Cameron, 1989
(1 row)

   content    | docurl
--------------+--------
 Document URL |
(1 row)

film_detail_summary (function)

Returns multiple results sets for film citation. Called by FilmDetail.java

Note

Resolved. ~~Some queries used in this function need to do select distinct in order to handle films with multiple release years.~~

Some name_id and subj_id fields returned are static '2' because those fields are not used. See example below.

Execution (for film_id='pfafilm3'):

begin;
select cinefiles_denorm.film_detail_summary('pfafilm3','title', 'director', 'country', 'filmyear', 'lang', 'prodco', 'genre', 'subject', 'reldocs');
fetch all in "title";
fetch all in "director";
fetch all in "country";
fetch all in "filmyear";
fetch all in "lang";
fetch all in "prodco";
fetch all in "genre";
fetch all in "subject";
fetch all in "reldocs";
commit;

Returns:

content |  filmid  |            title
---------+----------+------------------------------
 Title   | pfafilm3 | L'Oeuvre au noir (The Abyss)
(1 row)

  content  | id |   director
-----------+----+---------------
 Directors |  2 | André Delvaux
(1 row)

  content  | country
-----------+---------
 Countries | Belgium
 Countries | France
(2 rows)

 content | year
---------+------
 Years   | 1988
(1 row)

  content  |  lang
-----------+--------
 Languages | French
(1 row)

 content | id | prodco
---------+----+--------
(0 rows)

 content |   genre
---------+------------
 Genres  | Adaptation
 Genres  | Historical
(2 rows)

 content  | id |                   subject
----------+----+---------------------------------------------
 Subjects |  2 | Alchemists -- Drama
 Subjects |  2 | Belgium -- History -- 16th century -- Drama
(2 rows)

   content    | id |                        title                         |
type     | pages | pg_info |             source             | name_id |
      author               |   pubdate   | juliandate | code | docurl
--------------+----+------------------------------------------------------+-----
---------+-------+---------+--------------------------------+---------+---------
---------------------------+-------------+------------+------+--------
 Related Docs | 18 | Andre Delvaux: une oeuvre--un film: L'oeuvre au noir | pres
s kit    |    19 |         | Editions Méridiens Klincksieck |       2 | André De
lvaux|Marguerite Yourcenar | 1988        |            |    4 |
 Related Docs | 19 | L'oeuvre au noir                                     | prog
ram note |     1 | p. 47   | Cannes Film Festival           |       2 | André De
lvaux                      | 1988 May    |            |    4 |
 Related Docs | 20 | L'oeuvre au noir                                     | revi
ew       |     1 |         | Variety                        |       2 | Lenny Bo
rger                       | 1988 May 18 |            |    4 |
 Related Docs | 21 | L'oeuvre au noir (the abyss)                         | revi
ew       |     1 |         | Hollywood Reporter             |       2 |
                           | 1988 May 16 |            |    0 |
 Related Docs | 22 | L'oeuvre au noir                                     | pres
s kit    |    58 |         | UGC Images France SA.          |       2 |
                           | 1988        |            |    4 |
(5 rows)

alldoctitles_view

Used in DocList.java to populate temp tables for searching

allfilmtitles_view

Used in DocList.java to populate temp tables for searching.

docsubjects_view

Used in DocList.java to populate temp tables for searching.

filmgenres

Used in FilmSearchArgs.java

doc_detail_summary (function)

Returns multiple result sets used for document citation. Called by DocDetail.java.

Execution (for doc_id=12):

begin;
select cinefiles_denorm.doc_detail_summary('12', 'document', 'doctitle', 'authors', 'source', 'doctype', 'lang', 'docsubj', 'docnamesubj', 'docfilmsubj', 'docurl');
fetch all in "document";
fetch all in "doctitle";
fetch all in "authors";
fetch all in "source";
fetch all in "doctype";
fetch all in "lang";
fetch all in "docsubj";
fetch all in "docnamesubj";
fetch all in "docfilmsubj";
fetch all in "docurl";
commit;

Note

Some name_id and subj_id fields returned are static '2' because those fields are not used. See example below.

Returns:

content  | doc_id |    date     | pages | pg_info | cast_cr | tech_cr | bx_info
 | filmog | dist_co | prod_co | cost | illust | note
----------+--------+-------------+-------+---------+---------+---------+--------
-+--------+---------+---------+------+--------+------
 Document |     12 | 1989 Aug 06 |     2 | p. H-15 |       0 |       0 |       0
 |      0 |       0 |       0 |    1 |      0 |
(1 row)

    content     |                title
----------------+-------------------------------------
 Document Title | The abyss: a foray into deep waters
(1 row)

     content      | name_id |     author
------------------+---------+----------------
 Document Authors |       2 | Aljean Harmetz
(1 row)

     content     |     src_id      |     source     | srcurl
-----------------+-----------------+----------------+--------
 Document Source | organization482 | New York Times |
(1 row)

    content    |  type
---------------+--------
 Document Type | review
(1 row)

      content       |  lang
--------------------+---------
 Document Languages | English
(1 row)

      content      | subj_id |           subj
-------------------+---------+---------------------------
 Document Subjects |       2 | Underwater cinematography
(1 row)

        content         | name_id |                    namesubj

------------------------+---------+---------------------------------------------
----
 Document Name Subjects |       2 | James Cameron, Kapuskasing, Ontario, Canada
 Document Name Subjects |       2 | Ed Harris, Englewood, New Jersey, United Sta
tes
(2 rows)

        content         | film_id  |            filmsubj
------------------------+----------+--------------------------------
 Document Film Subjects | pfafilm2 | The abyss, James Cameron, 1989
(1 row)

   content    | docurl
--------------+--------
 Document URL |
(1 row)

film_detail_summary (function)

Returns multiple results sets for film citation. Called by FilmDetail.java

Note

Resolved. ~~Some queries used in this function need to do select distinct in order to handle films with multiple release years.~~

Some name_id and subj_id fields returned are static '2' because those fields are not used. See example below.

Execution (for film_id='pfafilm3'):

begin;
select cinefiles_denorm.film_detail_summary('pfafilm3','title', 'director', 'country', 'filmyear', 'lang', 'prodco', 'genre', 'subject', 'reldocs');
fetch all in "title";
fetch all in "director";
fetch all in "country";
fetch all in "filmyear";
fetch all in "lang";
fetch all in "prodco";
fetch all in "genre";
fetch all in "subject";
fetch all in "reldocs";
commit;

Returns:

content |  filmid  |            title
---------+----------+------------------------------
 Title   | pfafilm3 | L'Oeuvre au noir (The Abyss)
(1 row)

  content  | id |   director
-----------+----+---------------
 Directors |  2 | André Delvaux
(1 row)

  content  | country
-----------+---------
 Countries | Belgium
 Countries | France
(2 rows)

 content | year
---------+------
 Years   | 1988
(1 row)

  content  |  lang
-----------+--------
 Languages | French
(1 row)

 content | id | prodco
---------+----+--------
(0 rows)

 content |   genre
---------+------------
 Genres  | Adaptation
 Genres  | Historical
(2 rows)

 content  | id |                   subject
----------+----+---------------------------------------------
 Subjects |  2 | Alchemists -- Drama
 Subjects |  2 | Belgium -- History -- 16th century -- Drama
(2 rows)

   content    | id |                        title                         |
type     | pages | pg_info |             source             | name_id |
      author               |   pubdate   | juliandate | code | docurl
--------------+----+------------------------------------------------------+-----
---------+-------+---------+--------------------------------+---------+---------
---------------------------+-------------+------------+------+--------
 Related Docs | 18 | Andre Delvaux: une oeuvre--un film: L'oeuvre au noir | pres
s kit    |    19 |         | Editions Méridiens Klincksieck |       2 | André De
lvaux|Marguerite Yourcenar | 1988        |            |    4 |
 Related Docs | 19 | L'oeuvre au noir                                     | prog
ram note |     1 | p. 47   | Cannes Film Festival           |       2 | André De
lvaux                      | 1988 May    |            |    4 |
 Related Docs | 20 | L'oeuvre au noir                                     | revi
ew       |     1 |         | Variety                        |       2 | Lenny Bo
rger                       | 1988 May 18 |            |    4 |
 Related Docs | 21 | L'oeuvre au noir (the abyss)                         | revi
ew       |     1 |         | Hollywood Reporter             |       2 |
                           | 1988 May 16 |            |    0 |
 Related Docs | 22 | L'oeuvre au noir                                     | pres
s kit    |    58 |         | UGC Images France SA.          |       2 |
                           | 1988        |            |    4 |
(5 rows)