You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
157 lines
5.9 KiB
157 lines
5.9 KiB
MySQL database "literature", table "refs"
|
|
=========================================
|
|
|
|
field names
|
|
-----------
|
|
|
|
fields available in table "refs" matching field in "refs.fp5"
|
|
-------------------------------- ----------------------------
|
|
|
|
author Author Generic [A_13]
|
|
address Author Affiliation [03]
|
|
corporate_author Corporate Author [A_40]
|
|
first_author FirstAuthorGeneric [A_23]
|
|
author_count NumberOfAuthorsGeneric
|
|
title Title Generic [A_14]
|
|
orig_title Translated Title [11]
|
|
publication Journal Book Title Generic [A_15]
|
|
abbrev_journal Abbrev. Journal Title [46]
|
|
year Date of Publication [20]
|
|
date (none)
|
|
volume Volume ID [22]
|
|
volume_numeric Volume Numeric
|
|
issue Issue ID [24]
|
|
pages Pages Generic [A_19]
|
|
first_page FirstPage [A_34]
|
|
keywords Keywords [45]
|
|
abstract Abstract [43]
|
|
edition Edition [15]
|
|
editor Author, Monographic [07]
|
|
publisher Publisher [19]
|
|
place Place of Publication [18]
|
|
medium Packaging Method [27]
|
|
series_editor Series Editor [30]
|
|
series_title Series Title [32]
|
|
abbrev_series_title Abbrev. Series Title [A_17]
|
|
series_volume Series Volume ID [33]
|
|
series_volume_numeric (none!)
|
|
series_issue Series Issue ID [34]
|
|
issn ISSN [40]
|
|
isbn ISBN [41]
|
|
language Language [35]
|
|
summary_language Summary Language [A_10]
|
|
area Research Area [A_05]
|
|
type Record Type [A_06]
|
|
publication_status (none!)
|
|
thesis (none!)
|
|
expedition Expedition [A_11]
|
|
doi DOI [A_26]
|
|
conference Conference [A_33]
|
|
url Location|URL [38]
|
|
call_number Call Number [44] (in "Refs Mat.fp5": IPOE Literature DB Call Number [A_41])
|
|
location Copy Location [A_35]
|
|
contribution_id Contribution ID
|
|
online_publication (none!)
|
|
online_citation (none!)
|
|
file File Name [A_27]
|
|
notes Notes [42]
|
|
serial Serial Number
|
|
orig_record Original Record
|
|
approved Approved Boolean
|
|
created_date Created Date [A_36]
|
|
created_time Created Time [A_38]
|
|
created_by Erstellt von
|
|
modified_date Modified Date [A_37]
|
|
modified_time Modified Time [A_39]
|
|
modified_by GeŠndert von
|
|
|
|
|
|
column types
|
|
------------
|
|
|
|
author TEXT
|
|
address TEXT
|
|
corporate_author VARCHAR(255)
|
|
first_author VARCHAR(100)
|
|
author_count TINYINT UNSIGNED
|
|
title TEXT
|
|
orig_title TEXT
|
|
publication VARCHAR(255)
|
|
abbrev_journal VARCHAR(100)
|
|
year SMALLINT
|
|
date VARCHAR(50)
|
|
volume VARCHAR(50)
|
|
volume_numeric SMALLINT UNSIGNED
|
|
issue VARCHAR(50)
|
|
pages VARCHAR(50)
|
|
first_page MEDIUMINT UNSIGNED
|
|
keywords TEXT
|
|
abstract TEXT
|
|
edition TINYINT UNSIGNED
|
|
editor TEXT
|
|
publisher VARCHAR(255)
|
|
place VARCHAR(100)
|
|
medium VARCHAR(50)
|
|
series_editor TEXT
|
|
series_title TEXT
|
|
abbrev_series_title VARCHAR(100)
|
|
series_volume VARCHAR(50)
|
|
series_volume_numeric SMALLINT UNSIGNED
|
|
series_issue VARCHAR(50)
|
|
issn VARCHAR(100)
|
|
isbn VARCHAR(100)
|
|
language VARCHAR(100)
|
|
summary_language VARCHAR(100)
|
|
area VARCHAR(255)
|
|
type VARCHAR(100)
|
|
publication_status ENUM("published","in print","submitted","unpublished")
|
|
thesis ENUM("Bachelor's thesis","Master's thesis","Ph.D. thesis","Diploma thesis","Doctoral thesis","Habilitation thesis")
|
|
expedition VARCHAR(255)
|
|
doi VARCHAR(100)
|
|
conference VARCHAR(255)
|
|
url VARCHAR(255)
|
|
call_number TEXT
|
|
location TEXT
|
|
contribution_id VARCHAR(100)
|
|
online_publication ENUM("no","yes") NOT NULL
|
|
online_citation VARCHAR(255)
|
|
file VARCHAR(255)
|
|
notes TEXT
|
|
serial MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
|
|
orig_record MEDIUMINT
|
|
approved ENUM("no","yes") NOT NULL
|
|
created_date DATE
|
|
created_time TIME
|
|
created_by VARCHAR(100)
|
|
modified_date DATE
|
|
modified_time TIME
|
|
modified_by VARCHAR(100)
|
|
|
|
|
|
table creation code
|
|
-------------------
|
|
|
|
CREATE TABLE refs (author TEXT, address TEXT, corporate_author VARCHAR(255), first_author VARCHAR(100), author_count TINYINT UNSIGNED, title TEXT, orig_title TEXT, publication VARCHAR(255), abbrev_journal VARCHAR(100), year SMALLINT, date VARCHAR(50), volume VARCHAR(50), volume_numeric SMALLINT UNSIGNED, issue VARCHAR(50), pages VARCHAR(50), first_page MEDIUMINT UNSIGNED, keywords TEXT, abstract TEXT, edition TINYINT UNSIGNED, editor TEXT, publisher VARCHAR(255), place VARCHAR(100), medium VARCHAR(50), series_editor TEXT, series_title TEXT, abbrev_series_title VARCHAR(100), series_volume VARCHAR(50), series_volume_numeric SMALLINT UNSIGNED, series_issue VARCHAR(50), issn VARCHAR(100), isbn VARCHAR(100), language VARCHAR(100), summary_language VARCHAR(100), area VARCHAR(255), type VARCHAR(100), publication_status ENUM("published","in print","submitted","unpublished"), thesis ENUM("Bachelor's thesis","Master's thesis","Ph.D. thesis","Diploma thesis","Doctoral thesis","Habilitation thesis"), expedition VARCHAR(255), doi VARCHAR(100), conference VARCHAR(255), url VARCHAR(255), call_number TEXT, location TEXT, contribution_id VARCHAR(100), online_publication ENUM("no","yes") NOT NULL, online_citation VARCHAR(255), file VARCHAR(255), notes TEXT, serial MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, orig_record MEDIUMINT, approved ENUM("no","yes") NOT NULL, created_date DATE, created_time TIME, created_by VARCHAR(100), modified_date DATE, modified_time TIME, modified_by VARCHAR(100));
|
|
|
|
|
|
rules for data import
|
|
---------------------
|
|
- fields are separated by tabs, records are separated by returns (if not specified otherwise within the LOAD DATA statement)
|
|
- order of fields must resemble the above field order!
|
|
- DATE format must be YYYY-MM-DD -> Available via Filemaker Calculation Fields 'Created Date [A_36]' & 'Modified Date [A_37]'
|
|
- TIME format must be HH:MM:SS -> Available via Filemaker Calculation Fields 'Created Time [A_38]' & 'Modified Time [A_39]'
|
|
- carriage returns *within* fields (ASCII character 11) must be replaced with a "UNIX return" (ASCII character 10) -> Search for: (\x0B) Replace with: \\n
|
|
- empty fields are indicated by \N -> Search for: (?<=\t|^)(?=\t|$) Replace with: \\N
|
|
- character encoding: higher ASCII chars must be encoded as ISO-8859-1 aka 'Western (ISO Latin 1)'
|
|
- file encoding must be UNIX
|
|
|
|
|
|
load data code
|
|
--------------
|
|
|
|
LOAD DATA LOCAL INFILE "/PATH/TO/FILE/refs.txt" INTO TABLE refs;
|
|
|
|
or, alternatively, use something like the following from your shell:
|
|
|
|
mysqlimport --local -u root -p YOUR_DB_NAME "/PATH/TO/FILE/refs.txt"
|
|
|