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).
| Table of Contents | ||
|---|---|---|
|
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.
...
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 | int4 | always '2' because this short identifier is no longer used. 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: | |
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 | 2 person214 | English | 1989 Sep 04 | 0 | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE |
| 9/3/89 17:00 | (null) | (null) | (null) |
3 | The abyss | review | 2 |
| Film Journal International | organization15066 | Kevin Lally | 2 person215 | English | 1989 Sep | 4 | TRUE | TRUE | FALSE | FALSE | FALSE | TRUE | FALSE | FALSE | FALSE |
| 8/31/89 17:00 | (null) | (null) | (null) |
4 | There is a tide | review | 1 | p. 61 | Village Voice | organization494 | Amy Taubin | 2 person216 | English | 1989 Aug 15 | 4 | FALSE | FALSE | FALSE | FALSE | TRUE | TRUE | TRUE | FALSE | FALSE |
| 8/14/89 17:00 | (null) | (null) | |
12 | The abyss: a foray into deep waters | review | 2 | p. H-15 | New York Times | organization482 | Aljean Harmetz | 2 person217 | English | 1989 Aug 06 | 0 | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE | FALSE | 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 2 | person218 | English | 1989 Aug 06 | 0 | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE |
| 8/5/89 17:00 | (null) | Special effects | Industrial Light and Magic (Studio) |
...
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. |
Notes
Example rows
pfafilm10 | 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 | Yukinojo henge|An actor's revenge|Yukinojo Hengei|The Revenge of Yukinojo|The Avenging ghost of Yukinojo|Yukinojo Henge (1963 : Kon Ichikawa)|An Actor's revenge (1963 : Kon Ichikawa) | organization1|etc. |
pfafilm100 | |person391| | 1 | World gone wild | (null) | (null) | Lee H. Katzin | English | (null) | (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.
...
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 |
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):
| Code Block |
|---|
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:
| Code Block |
|---|
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'):
| Code Block |
|---|
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:
| Code Block |
|---|
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):
| Code Block |
|---|
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:
| Code Block |
|---|
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'):
| Code Block |
|---|
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:
| Code Block |
|---|
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)
|