The New Titles database is a current awareness service that provides 
periodic lists of titles cataloged by the Brown University Library. This
version of the New Titles list was first produced in October 2002
using MySQL (current version: 4.0.8) and PHP (current version: 4.2.2).
It was developed on walton.rockcluster.brown.edu and moved to the 
production server, dl.lib.brown.edu, in March 2003. Starting with
January 2003 the list is produced on a quarterly basis.
For further information, view spreadsheet of file structure and
statistics at <dl.lib.brown.edu/newtitles/newtitles_files.xls> and list 
of steps for producing quarterly lists at 
<dl.lib.brown.edu/newtitles/readme.txt>. This documentation was updated
in April 2003. [JMR]
_______________________________________________________________________

JOSIAH DATA OUTPUT 
One initial list is created in Josiah, using a range of cataloging
dates.   If present, Lexis-Nexis records should be removed from the file
(these records contain a MARC 035 field |aTCCLN).

From this list two primary output files are created in Josiah, one for
titles with call numbers in the bib record and another for titles with
call numbers in 
the item record.  Exclude CJK and Arabic language records from these
lists. The output files contain delimited fields for author, title,
imprint, bibliographic record number, bibliographic location, and
bibliographic or item call number (in that order). Both files are output
in call number order.

In the third iteration, separate files are output for (1) CJK materials,
(2) Arabic titles, (3) reference materials, (4) serials (LC), (5)
serials (non-LC) and (6) electronic serials. 
Using the initial list:
(1)	Create the CJK subfile from a search of Chinese, Japanese,
Korean item locations.  The file contains delimited fields for author,
title, imprint, bibliographic record number, bibliographic location,
bibliographic call number and item call number (in that order). It has a
second call number field since this list draws call numbers from either
the item or the bib record.
(2)	Create the Arabic subfile from a search on records coded for
the Arabic language.
(3) Create the reference subfile from a search of reference item
locations for respective buildings (Rock, Sciences, Hay, Orwig,
Demography, and Artslide).  The file contains delimited fields for
author, title, imprint, bibliographic record number, bibliographic
location, bibliographic call number and item call number (in that
order). It has a second call number field since this list draws call
numbers from either the item or the bib record.
 (4-6) Serials
a.	Create a general serials file using a search of bibliographic
format=s
b.	Three subfiles are created from the general serials file: one
for titles with call numbers in the bib record, another for titles with
call numbers in the item record and one for titles with call numbers in
local field 990.  For each output include author, title, imprint,
bibliographic record number, bibliographic location, and one call number
type (bibliographic, item or 990).  These will have only one call number
field.

CALL NUMBERS

Use the sorting of the files by call number to verify that records
contain a classification and book number.  If any missing, enter the
appropriate data in the Josiah record.


UPLOAD

Create delimited files using the Josiah User-selected format and upload
to ets.cis.brown.edu. For each file:
a.	From the Josiah list function, choose U, then C
b.	Enter the fields listed above and in that order
c.	On the screen for Record format, change the field delimited to
ASCII character vertical bar, and the repeated field character to ASCII
character circumflex
d.	Choose C 
e.	Name the file
f.	Output the file to the server by entering "y"
g.	Follow the usual steps to FTP the file to the server
h.	Return to the list of output files on the Josiah server and
delete the file you have successfully uploaded (there is space for only
10 files) [DC]
_______________________________________________________________________

MYSQL
Database: newtitles
The Josiah data files are uploaded into 7 MySQL tables in the
'newtitles' database. An additional permanent table is used to
associate call numbers used as form values with subjects names. In
the previous iteration a merged 'bibs' and 'items' file was created
to search by format ([videorecording], [microform], etc.). The
'UNION' command in MySQL 4.0 made this unnecessary. 

Data from the previous quarter is retained in 7 tables called
'bibs_old', etc.

The total number of records in the database for july-dec 2002 is 31,433
(6.4 MB). The tables are described below:

- Table: 'bibs' 
- Columns: author, title, imprint, bibno (primary key), location, callno 
- Number of Records: 21,762 (4.4 MB) july-dec 2002 [24,581 jan-june 2002]
- Note: This is the primary table for L.C. call number types.
- Scripts: All L.C call number scripts plus 'non-harris', 'location', 
'storage' and 'format' query this table. 'Storage' and 'format' also
query the 'items' table.

- Table: 'items' 
- Columns: author, title, imprint, bibno (primary key), location, callno 
- Number of Records: 6,282 (1.4 MB) july-dec 2002 [5,915 jan-june 2002]
- Note: This is the primary table for  Harris and Cutter call number types.
- Scripts: 'harris_poetry', 'harris_plays', 'harris_music',
'harris_french', 'harris_videos', 'jcb', 'rhodeisland', 'itemsearch',
'format', 'storage'. ('Format' and 'storage also query the 'bibs' table

- Table: 'ref' [new for july-dec 2002]
- Columns: author, title, imprint, bibno, location, callno
- Number of Records: 173 (37.8 KB)
- Note: Includes only LC call numbers
- Script: 'reference'

- Table: 'lc_serials' [new for july-dec 2002]
- Columns: author, title, imprint, bibno, location, callno
- Number of Records: 340 (53.2 KB)
- Script: 'serials_lc'

- Table: 'nonlc_serials' [new for july-dec 2002]
- Columns: author, title, imprint, bibno, location, callno
- Number of Records: 396 (63.5 KB)
- Script: 'serials_nonlc'

- Table: 'ejournals' [new for july-dec 2002]
- Columns: author, title, imprint, bibno, location, callno
- Number of Records: 506 (74.6 KB)
- Script: 'serials_elec'

- Table: 'cjk' [new for july-dec 2002]
- Columns: author, title, imprint, bibno, location, callno
- Number of Records: 1,824 (363.6 KB)
- Script: 'cjk'

- Table: 'subjects'
- Columns: subjectid, class, subject_name
- Number of Records: 150 (10.6 KB)
- Note: This table was created to match form values with subject names
on results pages, e.g., 'PK' with 'Indo-Iranian Languages & 
Literatures'.
- Scripts: All non-custom scripts

DATA MANIPULATION
Minimal data manipulation is needed.  The 'items' and 'cjk' files have 
empty call number fields (|"") which are stripped out before uploading
to MySQL. Additionally I strip out the prefix '880-XX' in the cjk
file. Once the data is uploaded I check the bib number for proper
formatting (bibno like "b%"), check for the absence of a call number 
(callno is null), and eliminate "Rare" and "Small" in the callno field
since they interfere with subject searching of LC call numbers. In the
lc_serials, nonlc_serials, and ref tables I convert josiah location
codes (Update [table] set location = "Rock" where location = "r0001",
etc.) 

Beginning with the July-Sept. 2004 list the Josiah skip field was output.
Queries are run to move the part of the title equivalent to the skip value 
to a new field called 'article'.  The queries on the web sort by title and 
display $article.$title.

LANGUAGE ISSUES 
- Some Arabic characters are rendered in the database with a leading '.'
or '`'.  These characters appear in the Josiah webpac without the
leading punctuation mark.  With the leading '.' or '`' they sort at the
beginning or end of the alphabetical sequence.  

SORTING ISSUES
- The bibs file is output in call number order and that is used as the
default sort for L.C. call numbers.  Because the items file contains
many non-standard call number types, author is used as the default sort.
The default call number sort sorts by LC class without regard to size
designation; since the call number field is not delimited in the MySQL 
database the machine sort places 1-SIZE, 2-SIZE, 3-SIZE titles at the
beginning of the list.
- Titles which begin with double quotes do not sort properly because
the double quotes are replaced by a space when the file is uploaded.
Using ltrim(title) resolves this sorting problem.

_______________________________________________________________________

PHP SCRIPTS

SEARCH FORM (index.html)
The call number breakdown used on the search form is that used in previous 
versions of New Titles.  That in turn was based on the traditional circulation
statistics breakdown with changes suggested by selectors.  Because different 
scripts are used to retrieve results for different call number patterns, the 
search form uses the script, 'link.php', to generate searches.  For example: 
- <option value="lcsearch.php?subject=PA">Classics
- <option value="typeZZ1-4.php?subject=PC,1,1000">Romanic Languages

SEARCH QUERIES & SCRIPTS
A. Form Value Produces Subject Name on Results Page 
- Value Examples: 
	$subject = "A" $subject_name = "General Works"
	$format = "microform" $subject_name = "Microforms"
	$location = "d0001"  $subject_name = "Demography Library"
- Script: All scripts except custom queries
- Query: 
	SELECT subject_name FROM subjects WHERE class = \"$subject\" 
	[ or class = $format or class = $location]

B. Form Passes single or multiple Values to a Script 
- Value Examples: $subject = "A", $subject = "BR,BT,BV,BX"
- Script: lcsearch.php
- Query: 
	$subj = explode(",",$subject); 
 
	$sql = "
	SELECT *, if(author=\"\",1,0) AS ausort
 	FROM bibs
 	WHERE callno like \"$subj[0]%\" OR callno like \"%-SIZE $subj[0]%\"";

	for ($i=1; $i<sizeof($subj); $i++) {

	$sql .= " OR callno like \"$subj[$i]%\" OR callno like \"%-SIZE $subj[$i]%\"";
	}

- Note: This script only works when both values are composed 
of two letters.  Using this script when one of the values has only one letter 
retrieves everything beginning with that letter. (Using this to retrieve Q-QA 
retrieves beginning with 'Q', not just 'Q1-Q999'.) See below for the 'Q-QA' type.

C. Form Passes a Range of Letter Values to a Script 
- Value Examples: 	
	$subject = "H-HK" [to retrieve H-HJ] 
	$subject = "Q,QA"  [to retrieve Q1-Q9999]
- Script: between.php
- Query: 
	$subject = explode(",",$subject);
	$subject1 = $subject[0];
	$subject2 = $subject[1];

	$sql = " 
	SELECT callno, author, title, imprint, bibno, location
 	FROM bibs
 	WHERE callno between \"$subject1%\" AND \"$subject2%\"
	OR callno like \"%-SIZE $subject1%\" 
	AND SUBSTRING(callno,8) 
	BETWEEN \"$subject1%\" AND \"$subject2%\"
	";
- Note: 'SUBSTRING(callno,8)' is used to exclude '1-SIZE', '2-SIZE',
'3-SIZE' from the range values.

D.   Form Passes a Range of Letter & Number Values to a Script 
- Value Examples: 	
	$subject = " PJ,4500,5199" 
	$subject = " PC,1001,1997,PQ,4001,5999" 

- Scripts: 	typeZ1-3.php	[to retrieve Z1-659 types]
		typeZ4-4.php	[to retrieve F1001-1200 types]
		typeZZ1-3.php	[to retrieve PS1-999 types]
		typeZZ1-4.php 	[to retrieve PC1-1000 types]
		typeZZ2-2.php	[to retrieve QA70-79 types]
		typeZZ3-3.php 	[to retrieve DS330-449 types]
		typeZZ4-4.php	[to retrieve PJ4500-5199 types]
		typeZZ8-8.php	[to retrieve PC1001-1997; PQ4001-5999]

- Query Example (for typeZZ1-4.php): 
	$subject = explode(",",$subject);
	$subject1 = $subject[0];
	$subject2 = $subject[1];
	$subject3 = $subject[2];
         
	$sql = "
	SELECT callno, author, title, imprint, bibno, location
 	FROM bibs    
 	WHERE callno like \"$subject1%\"
 	AND SUBSTRING(callno, 3, 2) >= $subject2
 	AND SUBSTRING(callno, 3, 4) <= $subject3
 	OR callno like \"%-SIZE $subject1%\"
 	AND SUBSTRING(callno, 10, 2) >= $subject2
 	AND SUBSTRING(callno, 10, 4) <= $subject3
	";

- Note: 'SUBSTRING(callno)' is used to extract the numerical value for
comparison.

E. Form Passes a Single Value to a Script 
- Value Examples: $subject = "Ref", $subject = "1-N"
- Script: itemsearch.php
- Query: 
	SELECT callno, author, title, imprint, bibno, location
 	FROM items
 	WHERE (callno like \"$subject%\" 
	OR callno like \"%-SIZE $subject%\")
 	AND location != \"r0001\"
 	AND location != \"j0001\"
 	ORDER BY author, title
- Note: This is similar to type B, lcsearch.php, but queries 'items'
instead of 'bibs'

F. Form Passes a Single Value to a Script 
- Value Examples: $format = "graphic"
- Script: format.php
- Query: 
	SELECT callno, author, title, imprint, bibno, location
 	FROM newtitles 
 	WHERE title like \"%[$format]%\"
- Note: This queries the title field in 'newtitles' 

G. Form Passes a Single Value to a Script 
- Value Examples: $location = "q0001"
- Script: location.php
- Query: 
	SELECT callno, author, title, imprint, bibno, location
 	FROM newtitles 
 	WHERE location = \"$location\"
- Note: This queries the location field in 'newtitles' 

H. Custom Queries 
- Scripts: 
	africa.php (DT1-38, 94+)
	art.php (N, NB-NK)
	asian.php (PL1-499; 3300-7900)
	cjk.php
	eastasia.php (DS500-9999)
	french.php (PC2001-3761; PQ1-3999) 
	harris_french.php
	harris_music.php	
	harris_plays.php
	harris_poetry.php 
	harris_videos.php
	jcb.php
	libraries.php (Z660-1000)
	linguistics.php (P,- PB)
	literature.php (PN1-1500, 3300+)
	math.php (QA1-69; 80+)
	non-harris.php
	recordings.php
	reference.php
	rhodeisland.php
	scores.php
	serials_elec.php
	serials_lc.php
	serials_nonlc.php
	technology.php (T-TJ, TL)

- Query Example (for Harris Poetry): 
	SELECT callno, author, title, imprint, bibno, location 
	FROM items WHERE callno not like \"Collection%\"
	AND (callno like \"76%\" OR callno like \"%-SIZE 76%\" 
	OR callno like \"19%\" OR callno like \"%-SIZE 19%\" 
	OR callno like \"20%\" OR callno like \"%-SIZE 20%\" 
	OR  callno like \"Coll%\" OR callno like \"%-SIZE Coll%\"
	OR callno like \"%WW%\" 
	OR callno like \"%Yiddish%\")
	ORDER BY author, title
- Note: In cases with more variations than patterns custom queries were
used in a template.
_______________________________________________________________________

SUGGESTIONS FOR FUTURE IMPROVEMENTS:
- Add new serials, new reference titles [done 3/03]
- Refine output for e-resources [e-journals added 3/03]
- Reduce the number of scripts if possible [done 8/04]
- Move to production environment [done 3/03]
- Add users table and email notification (Brown only?)
- Consider creating a similar tool by selector, using fund codes