/* SQL DDL: Create an implementation of the model using the SQL Data Definition Language */ /* Note: Comments such as this may not be supported by your system and may have to be removed */ /* You may want to change some field types to improve the implementation on your DBMS. */ /* Data type 'National Character' etc. indicates Unicode text./* PostgreSQL: You should create the entire database as Unicode! */ /* PostgreSQL: For Postgres you should create the entire database as Unicode! The 'National' has been removed from the SQL code */ /* PostgreSQL: the data type CHARACTER VARYING has been replaced with VARCHAR */ /* PostgreSQL: the data type DATETIME has been replaced with TIMESTAMP */ /* PostgreSQL: Table and field names have been quoted (= "Fieldname") to allow case-sensitivity */ /* PostgreSQL: the default values for boolean fields have been replaced with 'true' and 'false' */ /*=== Table: "ReferenceType" ===*/ /* Literature reference subsystem: Reference type definitions. Which reference attributes are enabled and how they should be labeled? Currently only the usage of a type is defined here; the attribute labels are defined directly in the vba code. */ /* RefType: The ReferenceTitle type, as used by Reference Manager (tm) 9.0 in their default databases. Additional ref. types may be added if they occur in the import. */ /* Description: A description, explaining the scope and use of the reference type to the user. */ /* LabelSet: This number is used to retrieve the correct customization set in the forms. Do not change the numbering without updating the methods in the code! */ /* ShowInPicklist: Only reference types selected here are available in a pick list; other types may be entered if the user knows their type code. */ /* Notes: Internal notes regarding the use of a ReferenceTitle type. */ CREATE TABLE "ReferenceType" ( "RefType" varchar(10) NOT NULL PRIMARY KEY, "Description" varchar(255) NULL, "LabelSet" int2 NOT NULL, "ShowInPicklist" bool NOT NULL, "Notes" varchar(255) NULL ); /*=== Table: "ReferenceTitle" ===*/ /* Literature reference subsystem: Main entity; compatible with Reference Manager™ v.9. */ /* RefType: Type of literature reference, determines which fields are available for data entry. The value must come from the pick list provided. //[RefMan 01: TY] */ /* RefID: Unique reference ID code for the reference record. The only characters allowed are '0'-'9' and 'A'-'Z' //[RefMan 02: ID] */ /* RefDescription: * A short text identifying the reference, usually authors, year, title. Example: 'Smith, J. & Nao, S. (1999). New Taxa.' System generated, but users may update it. //[RefMan: not applicable] */ /* Title: The main (primary) title. Use normal capitalization, omit a period ('.') at the end, and do not type a paragraph return (Enter) at the end of each line! //[RefMan 03: TI, T1, CT, BT only for BOOK & UNPB] */ /* DateYear: Year of the publication date (primary date). Only numbers are allowed and the year must be entered with 4 digits ('1998', not '98'). //[RefMan 05: Y1,PY pro parte] */ /* DateMonth: Optional: The month of the publication date. [Note: in DateYear/Month/Day the information printed on the book or journal are entered, even if this is not the true date!] //[RefMan 05: Y1,PY pro parte] */ /* DateDay: Optional: The day of the publication date. [Note continued: if the true date is relevant, e.g. for the purpose of nomenclatural priority, it can be entered under DateSecondary.] //[RefMan 05: Y1,PY pro parte] */ /* DateSuppl: Optional: A date supplement, like 'approx.', a season ('Summer'), a quarter ('1st Quarter'), or any other information regarding the publication date. //[RefMan 05: Y1,PY pro parte] */ /* SourceTitle: The book (secondary) title in cases where the reference is an article or chapter from a book. Use normal capitalization.//[RefMan 13: T2; BT for all types except BOOK & UNPB] */ /* SeriesTitle: The series title. Use normal capitalization, omit a period ('.') at the end, and do not type a paragraph return (Enter) at the end of each line! //[RefMan 23: T3] */ /* Periodical: Journal/periodical in which the article appeared. Linked to the Abbreviation attribute of ReferencePeriodical. //[RefMan 11: JF, JO, JA] */ /* Volume: The volume (for periodicals or journals, excluding the issue number), report number, etc. //[RefMan 12: VL pro parte, comp. Edition!] */ /* Issue: The issue, if any. Useful also to enter a special designation for a supplement, for example for 'xxx 45 (Suppl.)' enter volume = 45 and issue = 'Suppl.'. Do not put '()' around the issue number. //[RefMan 15: IS] */ /* Pages: The page, table, or figure numbers for the reference, e.g. '23-41', '341 pp.', or '20, 22-24, 32' (for non-consecutive pages). //[RefMan 09: SP + 10: EP] */ /* Publisher: The name of the publisher (publishing company or institution, including universities or scientific societies). //[RefMan 17: PB] */ /* PublPlace: The location where the item being referenced was published, such as a city and state. //[RefMan 16: CY, CP] */ /* Edition: Number of the edition of a book. Use only positive integer numbers. //[RefMan 12: VL pro parte, compare Volume!] */ /* DateYear2: Year of a secondary date, esp. the true publ. date where relevant for nomenclatural priority. Only numbers are allowed and the year must be entered with 4 digits ('1998', not '98'). //[RefMan 28: Y2 pro parte] */ /* DateMonth2: Optional: The month of a secondary date. //[RefMan 28: Y2 pro parte] */ /* DateDay2: Optional: The day of a secondary date. //[RefMan 28: Y2 pro parte] */ /* DateSuppl2: Optional: A date supplement a secondary date, like 'approx.', a season ('Summer'), a quarter ('1st Quarter'), or any other information regarding the secondary date. //[RefMan 28: Y2 pro parte] */ /* ISSN_ISBN: The 'International Standard Serial Number' or 'International Standard Book Number'. Optional information; use is recommended only for publications that are otherwise difficult to order. //[RefMan 26: SN] */ /* Miscellaneous1: Various reference type dependent information; e.g. the total number of volumes for books //[RefMan 29: M1] */ /* Miscellaneous2: Various reference type dependent information //[RefMan 30: M2] */ /* Miscellaneous3: Various reference type dependent information //[RefMan 31: M3] */ /* URL: URL address (http://www...) or local file (C:\graphic.gif; \\servername\Data\x.doc) //[RefMan 33: UR] */ /* SourceRefID: * Independent publication (e.g. an edited book) in which a dependent publication was published. Refers to the ID code of a reference already entered in this system. Provided as an alternative to ref. manager's denormalized storage! */ /* Language: * Language of the article/book, as ISO 2 letter codes. //[RefMan: not supported!] */ /* DuplicateCheck: * A system generated string (typically Au. 1-4/Yr./Jour./Vol./first page) that is assumed to be unique. Use 'DuplicateOverride' to override if two reference titles are falsely identified as duplicates. //[RefMan: not applicable] */ /* DuplicateOverride: * A number to manually override automatic duplicate check, enter a number 1-255 if the system claims that non-duplicate entries are duplicates. //[RefMan: not applicable] */ /* ReplaceWithRefID: * Instead of direct deletes (currently not available for security reasons), users here select which reference should be the valid one, into which all related information (keywords, markers) is merged. References are later deleted after a review process. */ /* Problem: * A problem that occurred during data editing within the application. Typically the entries here should later be deleted after help has been obtained. Do not enter scientific or bibliographic problems here; use Notes for such permanent problems! */ /* ProblemUpdatedBy: * Operator who entered the problem text. */ /* ProblemUpdatedWhen: * Date and time when problem was recorded. */ /* CitationText: * Full text of a citation that describes the current reference. Use if ref. is only known as a citation in the bibliography of another publication, or if imported from unstructure data source. Empty if reference is transcribed from original publication! */ /* CitationFrom: * Description of publication, Only known as citation from bibliography of another publication given here (as ID code or author/year description); Empty if transcribed from original publication! */ /* ImportedFrom: * If imported from a reference database (esp. a commercial one): The name of the database system or provider; otherwise empty. This information is important to prevent copyright violations! */ /* ImportedID: * If imported from a reference database (esp. a commercial one): The ID identifying the record in that database; otherwise empty. Requires ImportedFrom //[RefMan: not supported!; Internal management attribute] */ /* PlausibilityCheckedBy: * Person responsible for a first plausibility or consistency check. User and Date are automatically filled if 'Original check' performed directly. //[RefMan: not applicable. Internal mgmt.attribute] */ /* PlausibilityCheckedWhen: * Date and time when plausibility/consistency was checked (i.e. data entry rules and spelling errors checked, no comparison with original publication) //[RefMan: not applicable. Internal mgmt.attribute] */ /* OriginalComparedBy: * Name of user responsible for comparison of ReferenceTitle record with original publication //[RefMan: not applicable. Internal mgmt.attribute] */ /* OriginalComparedWhen: * Date and time when entry was compared with the original publication (important esp. when ReferenceTitle was entered from secondary ReferenceTitle list) //[RefMan: not applicable. Internal mgmt.attribute] */ /* Responsible: * Person responsible for entering this reference into the data collection. */ /* LogCreatedBy: * Operator who first entered (typed or imported) the data. The operator (or typist) may be different from the person responsible. //[RefMan: not applicable. Internal mgmt.attribute] */ /* LogCreatedWhen: * Date and time when record was first entered (= typed or imported) into this system. Note: Updates are recorded in ReferenceChanges. //[RefMan: not applicable. Internal mgmt.attribute] */ CREATE TABLE "ReferenceTitle" ( "RefType" varchar(10) NOT NULL DEFAULT 'JOUR', "RefID" varchar(20) NOT NULL PRIMARY KEY, "RefDescription" varchar(120) NULL, "Title" text NULL, "DateYear" int2 NULL, "DateMonth" int2 NULL, "DateDay" int2 NULL, "DateSuppl" varchar(255) NULL, "SourceTitle" text NULL, "SeriesTitle" varchar(255) NULL, "Periodical" varchar(255) NULL, "Volume" varchar(255) NULL, "Issue" varchar(255) NULL, "Pages" varchar(255) NULL, "Publisher" varchar(255) NULL, "PublPlace" varchar(255) NULL, "Edition" int2 NULL, "DateYear2" int2 NULL, "DateMonth2" int2 NULL, "DateDay2" int2 NULL, "DateSuppl2" varchar(255) NULL, "ISSN_ISBN" varchar(18) NULL, "Miscellaneous1" varchar(255) NULL, "Miscellaneous2" varchar(255) NULL, "Miscellaneous3" varchar(255) NULL, "URL" text NULL, "SourceRefID" varchar(20) NULL, "Language" varchar(20) NOT NULL DEFAULT '?', "DuplicateCheck" varchar(255) NULL UNIQUE, "DuplicateOverride" int2 NOT NULL DEFAULT 0, "ReplaceWithRefID" varchar(20) NULL, "Problem" text NULL, "ProblemUpdatedBy" int4 NULL, "ProblemUpdatedWhen" timestamp NULL, "CitationText" text NULL, "CitationFrom" varchar(255) NULL, "ImportedFrom" varchar(50) NULL, "ImportedID" varchar(50) NULL, "PlausibilityCheckedBy" int4 NULL, "PlausibilityCheckedWhen" timestamp NULL, "OriginalComparedBy" int4 NULL, "OriginalComparedWhen" timestamp NULL, "Responsible" int4 NOT NULL, "LogCreatedBy" int4 NOT NULL, "LogCreatedWhen" timestamp NOT NULL DEFAULT current_timestamp ); /*=== Table: "ReferenceAuthor" ===*/ /* Literature reference subsystem: Reference authors, book editors, or series editors. */ /* Author: Author. Example: 'Miller, W. I., Jr.'. Format: Last name, a comma, the first initial with period and blank, an optional middle initial, and an optional comma plus suffix (Jr./Sr./III./MD etc.) //[RefMan 04: A1/AU, 14: A2/ED, 24: A3; no '*' allowed!] */ /* AuthorAddress: The address of the author, if available. Entered only in cases where it is of special relevance to one of the users of the database (or if imported from a database). //[RefMan 32: AD] */ /* RefID: Refers to the ID code of the main ReferenceTitle table (= foreign key). */ /* AuthorType: Three different author categories are supported: 1 = primary author, 2 = secondary author/editor, 3 = series editor. //[RefMan implicit] */ /* AuthorSequence: The sequence of authors of the article. (Default based on system date/time; counter attrib. wouldn't work with replication -> random sequence! Note: Au+RefID+Type is not necessarily unique. Two authors may have identical abbreviated names, e.g. spouses!). */ CREATE TABLE "ReferenceAuthor" ( "Author" varchar(255) NOT NULL, "AuthorAddress" varchar(255) NULL, "RefID" varchar(20) NOT NULL, "AuthorType" int2 NOT NULL DEFAULT 1, "AuthorSequence" int4 NOT NULL DEFAULT CLng(99999*(Now()-37200)), PRIMARY KEY ("RefID","AuthorType","AuthorSequence") ); /*=== Table: "ReferenceAvailability" ===*/ /* Literature reference subsystem: Availability and location of reference items in private or official filing system; e.g., book signatures or reprint article availability. Each responsible user may enter multiple filing codes. */ /* RefID: Refers to the ID code of the main ReferenceTitle table (= foreign key). */ /* AvailabilityID: Unique ID to identify the availability record. (Technical note: to improve reliability of database replication, the primary key is formed in combination with the RefID. Note that FilingCode may be Null.). */ /* FilingCode: Information about availability or location of a copy of the referenced publication: Filing code of reprint or book in private filing system, institutional catalogue code, signature, official call number, or shelf code in a library. //[RefMan 27: AV] */ /* ReprintStatus: Refers to filing system of responsible person. Reprint may be 'Not in file' (0), 'On request (card to author)' (1), 'On request (internal order/copy marker)' (2), 'On request (interlibrary loan)' (3), or 'In file' (4) //[RefMan 08: RP pro parte] */ /* RequestDate: Only if ReprintStatus = 'On request' (1/2): The date on which the reprint was requested. //[RefMan 08: RP pro parte] */ /* Responsible: The person responsible for the availability/filing code information, and to which the reprint status/request date refers. //[RefMan: not supported] */ /* LogCreatedBy: Operator who first entered (typed or imported) the data. The operator (or typist) may be different from the person responsible. //[RefMan: not applicable. Internal mgmt.attribute] */ /* LogCreatedWhen: Date and time when record was first entered (= typed or imported) into this system. Note: Updates are recorded in ReferenceChanges. //[RefMan: not applicable. Internal mgmt.attribute] */ CREATE TABLE "ReferenceAvailability" ( "RefID" varchar(20) NOT NULL, "AvailabilityID" SERIAL NOT NULL, "FilingCode" varchar(255) NULL, "ReprintStatus" int2 NOT NULL DEFAULT 0, "RequestDate" timestamp NULL, "Responsible" int4 NOT NULL, "LogCreatedBy" int4 NOT NULL, "LogCreatedWhen" timestamp NOT NULL DEFAULT current_timestamp, PRIMARY KEY ("RefID","AvailabilityID") ); /*=== Table: "ReferenceCollection" ===*/ /* Literature reference subsystem: Declares that a reference belongs to a virtual reference database called a 'reference collection'. For example, all references from a collaborative project may form a reference collection. */ /* RefID: Refers to the ID code of the main ReferenceTitle table (= foreign key). */ /* ReferenceCollection: Code to identify a reference as belonging to a specific reference collection (= a virtual reference database, e.g. for a collaborative project). */ /* Responsible: The person responsible for assigning the reference to a reference collection. //[RefMan: not supported] */ CREATE TABLE "ReferenceCollection" ( "RefID" varchar(20) NOT NULL, "ReferenceCollection" varchar(25) NOT NULL, "Responsible" int4 NOT NULL, PRIMARY KEY ("ReferenceCollection","RefID") ); /*=== Table: "ReferenceKeyword" ===*/ /* Literature reference subsystem: Keywords (may include 'subject heading'-keywords and organism names). */ /* RefID: Refers to the ID code of the main ReferenceTitle table (= foreign key). */ /* Keyword: A keyword, esp. subject headings or private markers. A keyword may consist of multiple words (phrases, e.g. 'red herring'), which can then only be searched as a whole. //[RefMan 07: KW] */ /* Responsible: The person responsible for the assignment of this keyword to a reference. //[RefMan: not supported] */ /* KeywordType: (temporarily introduced to preserve information when importing data that distinguish between (k)eyword, (g)eography, and organism (n)ames). */ CREATE TABLE "ReferenceKeyword" ( "RefID" varchar(20) NOT NULL, "Keyword" varchar(255) NOT NULL, "Responsible" int4 NOT NULL, "KeywordType" varchar(1) NULL, PRIMARY KEY ("Keyword","RefID") ); /*=== Table: "ReferenceAbstract" ===*/ /* Literature reference subsystem: Public Abstracts. */ /* RefID: Refers to the ID code of the main ReferenceTitle table (= foreign key). */ /* Responsible: * The person responsible for the abstract information. Abstracts are copyright protected! Leave empty if the abstract was not written by you, but copied from the publication itself or a bibliographic database. //[RefMan: not supported] */ /* Language: * Language of the abstract, as ISO 2 letter codes. //[RefMan : not supported!] */ /* Abstract: An abstract containing a short summary of the content of the article or book //[RefMan 25: N2] */ /* Shareable: * Do you want to share your abstract with other users, i.e. can it be published on the web? //[RefMan : not supported!] */ /* LogCreatedBy: * Operator who first entered (typed or imported) the data. The operator (or typist) may be different from the person responsible. //[RefMan: not applicable. Internal mgmt.attribute] */ /* LogCreatedWhen: * Date and time when record was first entered (= typed or imported) into this system. Note: Updates are recorded in ReferenceChanges. //[RefMan: not applicable. Internal mgmt.attribute] */ CREATE TABLE "ReferenceAbstract" ( "RefID" varchar(20) NOT NULL, "Responsible" int4 NOT NULL, "Language" varchar(2) NOT NULL DEFAULT '?', "Abstract" text NOT NULL, "Shareable" bool NOT NULL DEFAULT 'false', "LogCreatedBy" int4 NOT NULL, "LogCreatedWhen" timestamp NOT NULL DEFAULT current_timestamp, PRIMARY KEY ("RefID","Responsible","Language") ); /*=== Table: "ReferenceUserMarker" ===*/ /* Literature reference subsystem: User specific markers, e.g. to mark up a reference for a publication. */ /* RefID: Refers to the ID code of the main ReferenceTitle table (= foreign key). */ /* Marker: * User specific marker, e.g. to mark up a reference for a specific project or for a publication. These markers will normally be visible only under the same Responsible login name. */ /* Responsible: * The person responsible for the assignment of this user marker to a reference. Normally each person sees only his or her own markers! */ CREATE TABLE "ReferenceUserMarker" ( "RefID" varchar(20) NOT NULL, "Marker" varchar(255) NOT NULL, "Responsible" int4 NOT NULL, PRIMARY KEY ("RefID","Marker","Responsible") ); /*=== Table: "ReferenceUserNote" ===*/ /* Literature reference subsystem: User specific notes. Each user will only see the notes entered under the same responsible user name. */ /* RefID: Refers to the ID code of the main ReferenceTitle table (= foreign key). */ /* Responsible: * The person responsible for the Notes. //[RefMan: not supported] */ /* Notes: Internal notes regarding the reference title. Notes will normally be visible only under the same Responsible login name. //[RefMan 06: N1, AB] */ /* LogCreatedBy: * Operator who first entered (typed or imported) the data. The operator (or typist) may be different from the person responsible. //[RefMan: not applicable. Internal mgmt.attribute] */ /* LogCreatedWhen: * Date and time when record was first entered (= typed or imported) into this system. Note: Updates are recorded in ReferenceChanges. //[RefMan: not applicable. Internal mgmt.attribute] */ CREATE TABLE "ReferenceUserNote" ( "RefID" varchar(20) NOT NULL, "Responsible" int4 NOT NULL, "Notes" text NOT NULL, "LogCreatedBy" int4 NOT NULL, "LogCreatedWhen" timestamp NOT NULL DEFAULT current_timestamp, PRIMARY KEY ("RefID","Responsible") ); /*=== Table: "ReferenceUserDefined" ===*/ /* Literature reference subsystem: User-defined fields originating form imported Reference Manager RIS files. These user-defined fields are currently not well supported in Diversity References. */ /* RefID: Refers to the ID code of the main ReferenceTitle table (= foreign key). */ /* Responsible: The person responsible for the user defined fields (usually who imported the RIS file). */ /* UserDef1: User defined fields as entered in Reference Manager, only provided for import/export compatibility and not supported beyond that. //[RefMan 18: U1] */ /* UserDef2: User defined fields as entered in Reference Manager, only provided for import/export compatibility and not supported beyond that. //[RefMan 19: U2] */ /* UserDef3: User defined fields as entered in Reference Manager, only provided for import/export compatibility and not supported beyond that. //[RefMan 20: U3] */ /* UserDef4: User defined fields as entered in Reference Manager, only provided for import/export compatibility and not supported beyond that. //[RefMan 21: U4] */ /* UserDef5: User defined fields as entered in Reference Manager, only provided for import/export compatibility and not supported beyond that. //[RefMan 22: U5] */ CREATE TABLE "ReferenceUserDefined" ( "RefID" varchar(20) NOT NULL, "Responsible" int4 NOT NULL, "UserDef1" text NULL, "UserDef2" text NULL, "UserDef3" text NULL, "UserDef4" text NULL, "UserDef5" text NULL, PRIMARY KEY ("RefID","Responsible") ); /*=== Table: "ReferenceWordIdx1" ===*/ /* Literature reference subsystem: Implementation entity. The words of the primary Title and the Source title, excluding stop words ('the', 'and', etc.), are written to this index table to allow fast queries. Words > 50 char. are truncated. */ /* Word: Words > 50 character are truncated! */ CREATE TABLE "ReferenceWordIdx1" ( "Word" varchar(50) NOT NULL, "RefID" varchar(20) NOT NULL, PRIMARY KEY ("Word","RefID") ); /*=== Table: "ReferenceWordIdx2" ===*/ /* Literature reference subsystem: Implementation entity. The words of the Abstracts, excluding stop words ('the', 'and', etc.), are written to this index table to allow fast queries. Words > 50 char. are truncated. */ /* Word: Words > 50 character are truncated! */ CREATE TABLE "ReferenceWordIdx2" ( "Word" varchar(50) NOT NULL, "RefID" varchar(20) NOT NULL, PRIMARY KEY ("Word","RefID") ); /*=== Table: "ReferenceAnonymousNote" ===*/ /* Literature reference subsystem: Anonymous notes added through the internet public interface. */ /* RefID: Refers to the ID code of the main ReferenceTitle table (= foreign key). */ /* Notes: Notes entered by an anonymous user over the internet. */ /* InternetAccessCode: A system generated code that can be used to enable the anonymous user to update the Note over the internet. */ /* LogCreatedBy: Name of person who entered the anyonmous notes (free text, not referring to the User management component like most other LogCreatedBy attributes!). */ /* LogCreatedWhen: Date and time when record was entered into this system. */ /* LogUpdatedWhen: Date and time when record was last updated. */ CREATE TABLE "ReferenceAnonymousNote" ( "RefID" varchar(20) NOT NULL, "Notes" text NOT NULL, "InternetAccessCode" varchar(255) NULL, "LogCreatedBy" varchar(50) NOT NULL, "LogCreatedWhen" timestamp NOT NULL DEFAULT current_timestamp, "LogUpdatedWhen" timestamp NULL, PRIMARY KEY ("RefID","LogCreatedWhen") ); /*=== Table: "ReferenceChanges" ===*/ /* Literature reference subsystem: Changes of reference records are logged here (user, date, attributes affected). */ /* RefID: Refers to the ID code of the main ReferenceTitle table (= foreign key). */ /* Changes: Optionally a record of the attributes and data that were affected by the update //[RefMan: not applicable. Internal mgmt.attribute] */ /* LogUpdatedBy: * User who last updated the reference identified by RefID. This is the operator (or typist) name, which may be different from the person responsible. //[RefMan: not applicable. Internal mgmt.attribute] */ /* LogUpdatedWhen: * Date and time of last update of the reference. //[RefMan: not applicable. Internal mgmt.attribute] */ CREATE TABLE "ReferenceChanges" ( "RefID" varchar(20) NOT NULL, "Changes" text NOT NULL, "LogUpdatedBy" int4 NOT NULL, "LogUpdatedWhen" timestamp NOT NULL, PRIMARY KEY ("LogUpdatedWhen","RefID") ); /*=== Table: "ReferencePeriodical" ===*/ /* Literature reference subsystem: Journal/periodical titles. */ /* Abbreviation: Standardized abbreviation of periodical or journal. Use periods after the abbreviations //[RefMan 11: JA, JO] */ /* FullName: Full, non-abbreviated name of periodical or journal //[RefMan 11: JF, JO] */ /* Notes: Notes, remarks, or comments regarding the journal/periodical as a whole, incl. "continued as (new title)" or notes about local availability. */ /* ImportedFrom: If imported from another database: The name of the database system or provider; otherwise empty. */ /* Responsible: The person responsible for entering the information. */ /* LogCreatedBy: Operator who first entered (typed or imported) the data. The operator (or typist) may be different from the person responsible. //[RefMan: not applicable. Internal mgmt.attribute] */ /* LogCreatedWhen: Date and time when record was first entered (typed or imported) into this system. */ CREATE TABLE "ReferencePeriodical" ( "Abbreviation" varchar(255) NOT NULL PRIMARY KEY, "FullName" varchar(255) NULL, "Notes" text NULL, "ImportedFrom" varchar(50) NULL, "Responsible" int4 NOT NULL, "LogCreatedBy" int4 NOT NULL, "LogCreatedWhen" timestamp NOT NULL DEFAULT current_timestamp ); /*=== Table: "ReferencePeriodicalSyn" ===*/ /* Literature reference subsystem, Journal/periodical titles, thesaurus with synonyms -> valid name. Any entry in Periodical.Abbreviation and Periodical.FullName must also be added to the synonym table. */ /* Synonym: Alternative, synonymous names for the main record. Automatically translated into StdAbbrev if a thesaurus pick list is used in forms. //[RefMan 11: J1, J2] */ /* Abbreviation: Standardized abbreviation of periodical or journal, as defined in RefPeriodical entity (= foreign key). */ /* Source: Source of the synonym/thesaurus name: 'ABBR' for standard abbreviation, 'FULL' for standard full name, else name or abbrev. of user who added a non-standard synonym (like PNAS for Proc. Nat. Acad. Sci.). */ CREATE TABLE "ReferencePeriodicalSyn" ( "Synonym" varchar(255) NOT NULL PRIMARY KEY, "Abbreviation" varchar(255) NOT NULL, "Source" varchar(50) NOT NULL ); ALTER TABLE "ReferenceTitle" ADD FOREIGN KEY ("SourceRefID") REFERENCES "ReferenceTitle" ("RefID") ON UPDATE CASCADE, FOREIGN KEY ("RefType") REFERENCES "ReferenceType" ("RefType") ON UPDATE CASCADE, FOREIGN KEY ("ReplaceWithRefID") REFERENCES "ReferenceTitle" ("RefID") ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE "ReferenceAuthor" ADD FOREIGN KEY ("RefID") REFERENCES "ReferenceTitle" ("RefID") ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE "ReferenceAvailability" ADD FOREIGN KEY ("RefID") REFERENCES "ReferenceTitle" ("RefID") ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE "ReferenceCollection" ADD FOREIGN KEY ("RefID") REFERENCES "ReferenceTitle" ("RefID") ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE "ReferenceKeyword" ADD FOREIGN KEY ("RefID") REFERENCES "ReferenceTitle" ("RefID") ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE "ReferenceAbstract" ADD FOREIGN KEY ("RefID") REFERENCES "ReferenceTitle" ("RefID") ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE "ReferenceUserMarker" ADD FOREIGN KEY ("RefID") REFERENCES "ReferenceTitle" ("RefID") ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE "ReferenceUserNote" ADD FOREIGN KEY ("RefID") REFERENCES "ReferenceTitle" ("RefID") ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE "ReferenceUserDefined" ADD FOREIGN KEY ("RefID") REFERENCES "ReferenceTitle" ("RefID") ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE "ReferenceAnonymousNote" ADD FOREIGN KEY ("RefID") REFERENCES "ReferenceTitle" ("RefID") ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE "ReferenceChanges" ADD FOREIGN KEY ("RefID") REFERENCES "ReferenceTitle" ("RefID") ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE "ReferencePeriodicalSyn" ADD FOREIGN KEY ("Abbreviation") REFERENCES "ReferencePeriodical" ("Abbreviation") ON UPDATE CASCADE ON DELETE CASCADE