/* 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: "ResourceCollection" ===*/
/* Defines common properties for a set of related information resources of various quality levels. A collection is defined by management (managing users, storage locations, etc.). Compare 'Presentation' for thematic collections! */
  /*   ResourceCollectionID: Resource collection ID, unique system generated value (primary key). */
  /*   Title: Unique user readable title, identifying the resource collection. Example: 'Ascochyta images from Melnik (1977)'. */
  /*   Description: Short description of the resource collection, ca. a single paragraph long. Basic xhtml formatting tags are permitted. */
  /*   Coverage: The extent or scope of the content of the resource. Coverage will typically include spatial location (e.g. a place name), temporal period (a period label, date, or date range) or taxonomic group [DC 1.1!] */
  /*   Authors: A list of authors displayed on the title page of the entire collection. Authors or editors of a collection need not be identical with resource managers, or creators of resources. */
  /*   Editors: A list of editors displayed on the title page of the entire collection. The creators of a resource collection can decide whether they are Authors or Editors, but at least one of the two must be used. */
  /*   Contributors: A list of contributors displayed on the title page of the entire collection. */
  /*   SourceDescription: Where applicable, a general description of the source or origin of the resources covered in the current collection. Compare SourceReferenceID if source is a publication. */
  /*   SourceReferenceID: For resources that are present in published sources: the reference ID as defined in a bibliographic reference manager like DiversityReferences (RefID, foreign key). Example: 'ABE_1990_MPL' for figures from a book recorded under this ID. */
  /*   SourceRefDescriptionCache: A readable description (author, year, etc.) corresponding to SourceReferenceID, automatically updated from the reference management subsystem. */
  /*   AcknowledgementID: Refers to an acknowledgement definition that documents source and intellectual property rights, and provides a formatted acknowledgement (foreign key). */
  /*   Rights: If not already covered by acknowledgment: documentation of copyright or usage agreements, including dates and the name of the responsible person or institution with which an agreement was reached. */
  /*   InternalNotes: Internal notes and remarks. Not published in public reports. */
  /*   IconURI: URI of a icon or logo, usually a small gif file that can be displayed next to the collection title. */
  /*   IconLinkURI: A URI that may optionally be placed behind the collection icon, to create a hyperlink to another site on the world-wide-web. */
  /*   IconLinkHidden: True = no link border around icon image (default); False = a colored link border is added around icon image. */
  /*   IconDescription: Text used for the alternative text of the image; usually this text is also displayed when the mouse hovers over the image. */
  /*   IconWidth: Width of image in pixels (improves speed with which html pages can be displayed). Set to 0 if no width is available. */
  /*   IconHeight: Height of image in pixels (improves speed with which html pages can be displayed). Set to 0 if no height is available. */
  /*   ReleaseDate: A date which may be up to 1 year in the future to prevent public access to the resource collection prior to this date (set by the managers of the collection). */
  /*   ReleaseNotes: Required whenever ReleaseDate postpones the public release: Documentation of the motives for the postponement, and name of responsible user (esp. if multiple collection managers exist). */
  /*   ManagedContent: True: the organization hosting the DiversityResources metadata management application is also legally responsible for the server from which the resource content is provided. Editable only by site managers, not by resource collection managers! */
  /*   EditorialControl: True if editorial control is required (always necessary for managed content). Editable only by site managers, not by resource collection managers! If set to false, EditorialReleaseBy/When are updated to document decisions. */
  /*   EditorialControlDelegated: Default is false = only site managers can set EditorialRelease, if true EditorialRelease can be set by all managers of the resource collection. Editable only by site managers, not by resource collection managers! */
  /*   EditorialControlDocumentation: If EditorialControl is false or EditorialControlDelegated true, a documentation about the rejection or delegation is required. In the case of a delegation the responsibility must accepted a signed paper or agreement by email exchange should be cited. */
  /*   EditorialControlSetBy: The responsible site manager who delegated the editorial control. Editable only by site managers, not by resource collection managers! */
  /*   EditorialControlSetWhen: Date/time when the EditorialControl was delegated. */
  /*   EditorialReleaseBy: The editorial review mechanism guarantees that no unwanted information is presented on the website. Important for images uploaded by external users. Editability depends on EditorialControlDelegated attribute. */
  /*   EditorialReleaseWhen: Date/time up to which the all resources in the collection have been reviewed and found suitable for public release. Instances added at a later date are automatically prevented from being available in the internet. Editability like EditorialReleaseBy. */
  /*   CheckAvailability_LastTestWhen: The data and time when the last routine availability check of all resource instances of this collection was performed. Compare also CheckAvailability_LastTestWhen of individual resource instances. */
  /*   CheckAvailability_Interval: The number of days after which a routine availability check of all resource instances shall be performed. Set to 999999 to disable routine test. */
  /*   IsTemplate: True for templates used to create a new resource collection together with the relevant resource classes. System attribute, not user editable, default is false. */
  /*   LogCreatedBy: The user who created this record. */
  /*   LogCreatedWhen: The date and time when this record was created. Also serves as publication date of the collection (use of ReleaseDate is not indicated, may later be changed to temporarily lock the collection). */
CREATE TABLE "ResourceCollection" (
    "ResourceCollectionID" 	int4	 NOT NULL PRIMARY KEY,
    "Title" 	varchar(80)	 NOT NULL UNIQUE,
    "Description" 	text	 NOT NULL,
    "Coverage" 	text	 NULL,
    "Authors" 	varchar(255)	 NULL UNIQUE,
    "Editors" 	varchar(255)	 NULL UNIQUE,
    "Contributors" 	varchar(255)	 NULL,
    "SourceDescription" 	text	 NULL,
    "SourceReferenceID" 	varchar(20)	 NULL,
    "SourceRefDescriptionCache" 	varchar(255)	 NULL,
    "AcknowledgementID" 	int4	 NULL,
    "Rights" 	text	 NULL,
    "InternalNotes" 	text	 NULL,
    "IconURI" 	varchar(255)	 NULL,
    "IconLinkURI" 	varchar(255)	 NULL,
    "IconLinkHidden" 	bool	 NOT NULL DEFAULT 'true',
    "IconDescription" 	varchar(100)	 NULL,
    "IconWidth" 	int4	 NOT NULL DEFAULT 0,
    "IconHeight" 	int4	 NOT NULL DEFAULT 0,
    "ReleaseDate" 	timestamp	 NOT NULL DEFAULT current_timestamp,
    "ReleaseNotes" 	varchar(255)	 NULL,
    "ManagedContent" 	bool	 NOT NULL DEFAULT 'false',
    "EditorialControl" 	bool	 NOT NULL DEFAULT 'true',
    "EditorialControlDelegated" 	bool	 NOT NULL DEFAULT 'false',
    "EditorialControlDocumentation" 	text	 NULL,
    "EditorialControlSetBy" 	int4	 NULL,
    "EditorialControlSetWhen" 	timestamp	 NULL,
    "EditorialReleaseBy" 	int4	 NULL,
    "EditorialReleaseWhen" 	timestamp	 NULL,
    "CheckAvailability_LastTestWhen" 	timestamp	 NOT NULL DEFAULT 0,
    "CheckAvailability_Interval" 	int2	 NOT NULL DEFAULT 60,
    "IsTemplate" 	bool	 NOT NULL DEFAULT 'false',
    "LogCreatedBy" 	int4	 NOT NULL,
    "LogCreatedWhen" 	timestamp	 NOT NULL DEFAULT current_timestamp
);




/*=== Table: "ResourceCollectionManager" ===*/
/* A resource manager defines one or several persons that manage a ResourceCollection. Multiple resource managers can share the management responsibility equally, but no prioritization is yet possible. */
  /*   ResourceCollectionID: ID of the resource collection (foreign key). */
  /*   ManagingUserID: ID of user who may manages the resource information in the resource collection (foreign key, referring to DiversityUsers.UserID). */
  /*   LogCreatedBy: The user who created this record. */
  /*   LogCreatedWhen: The date and time when this record was created. */
CREATE TABLE "ResourceCollectionManager" (
    "ResourceCollectionID" 	int4	 NOT NULL,
    "ManagingUserID" 	int4	 NOT NULL,
    "LogCreatedBy" 	int4	 NOT NULL,
    "LogCreatedWhen" 	timestamp	 NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY ("ResourceCollectionID","ManagingUserID")
);




/*=== Table: "ResourceQualityClass" ===*/
/* Quality classes contains instances of abstract items. Defined separately for each resource collection, but collectively for all providers. Examples of quality classes are preview or thumbnail versions, compressed versions, or high quality versions. */
  /*   ResourceCollectionID: Refers to a resource collection (foreign key and part of primary key). */
  /*   QualityLevel: A quality level for this resource class resources, as defined in RscQualityLevel_Enum (foreign key and part of primary key). */
  /*   OnlineAvailability: Online availability scope (managers, login, intranet, internet) of resource instances of this quality class (also restricted by ItemOnlineAvailability of abstract item!). */
  /*   RequestAvailability: Availabilty scope of meta information (description, keywords); access information (URI) is available only on explicit request by electronic or postal means  (also restricted by ItemRequestAvailability of abstract item!). */
  /*   URI_Part2: Optional string, inserted between URI_Part1 (see ResourceProvider) and the individual resource name URI_Part3 (see ResourceAbstractItem). */
  /*   URI_Part4: Optional string, appended to the individual resource name (URL_Part3). This may be part of the filename + quality-level typical extension, e.g. '_thumbn.gif. */
  /*   RecommendedPresentation: Recommended presentation mode (Link, New-window-link, Download, Embed). The web interface may either follow the recommendation or implement its own rules. */
  /*   AutoGenerateMethodID: Defines a method used to automatically generate instances for this class based on another resource class (usually of higher quality level). ID = 0 disables autogeneration. */
  /*   InternalNotes: Internal notes regarding this quality version of the resource (history of editing, steps taken to generate this version, compression rate, etc.). */
  /*   LogCreatedBy: The user who created this record. */
  /*   LogCreatedWhen: The date and time when this record was created. */
  /*   LogUpdatedBy: The user who updated this record the last time. */
  /*   LogUpdatedWhen: The date and time when this record was last updated. */
CREATE TABLE "ResourceQualityClass" (
    "ResourceCollectionID" 	int4	 NOT NULL,
    "QualityLevel" 	int4	 NOT NULL,
    "OnlineAvailability" 	int2	 NOT NULL DEFAULT 9,
    "RequestAvailability" 	int2	 NOT NULL DEFAULT 9,
    "URI_Part2" 	varchar(255)	 NULL,
    "URI_Part4" 	varchar(255)	 NULL,
    "RecommendedPresentation" 	varchar(1)	 NOT NULL DEFAULT 'L',
    "AutoGenerateMethodID" 	int4	 NOT NULL DEFAULT 0,
    "InternalNotes" 	varchar(255)	 NULL,
    "LogCreatedBy" 	int4	 NOT NULL,
    "LogCreatedWhen" 	timestamp	 NOT NULL DEFAULT current_timestamp,
    "LogUpdatedBy" 	int4	 NOT NULL,
    "LogUpdatedWhen" 	timestamp	 NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY ("ResourceCollectionID","QualityLevel")
);




/*=== Table: "ResourceProvider" ===*/
/* Defines providers (primary/backup mirror/load balancing mirrors, online/offline status, etc.) of the content of a resource collection. */
  /*   ResourceCollectionID: Refers to a resource collection (foreign key and part of primary key). */
  /*   ProviderOrder: Number defining the order in which mirrored backup providers are accessed. Unique within a ResourceCollection (part of primary key). */
  /*   Status: Defines status as: Online primary location, Online load balancing, Online backup/mirror, Offline backup. */
  /*   URI_Part1: The first part of the URI, before the variable part defined in the individual resource. It should include protocol, server, and beginning of path. Example: 'http://www.example.net/images'. */
  /*   URI_Part1_Write: An alternative version of the first part of the URI, allowing write access (e.g. for updating mirrors or autogenerating instances). Example: 'user:pass@ftp://www.example.net/images'. URI_Part1 may rarely allow write access, URI_Part_Write always does. */
  /*   InternalNotes: Internal notes and remarks. */
  /*   LogCreatedBy: The user who created this record. */
  /*   LogCreatedWhen: The date and time when this record was created. */
  /*   LogUpdatedBy: The user who updated this record the last time. */
  /*   LogUpdatedWhen: The date and time when this record was last updated. */
CREATE TABLE "ResourceProvider" (
    "ResourceCollectionID" 	int4	 NOT NULL,
    "ProviderOrder" 	int4	 NOT NULL DEFAULT 1,
    "Status" 	varchar(1)	 NOT NULL,
    "URI_Part1" 	varchar(255)	 NULL,
    "URI_Part1_Write" 	varchar(255)	 NULL,
    "InternalNotes" 	text	 NULL,
    "LogCreatedBy" 	int4	 NOT NULL,
    "LogCreatedWhen" 	timestamp	 NOT NULL DEFAULT current_timestamp,
    "LogUpdatedBy" 	int4	 NOT NULL,
    "LogUpdatedWhen" 	timestamp	 NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY ("ResourceCollectionID","ProviderOrder")
);




/*=== Table: "ResourceAbstractItem" ===*/
/* The abstract resource item defines general information that applies to all duplicates or quality versions of a resource . */
  /*   ResourceID: Unique system generated value (primary key). Resource ID is a public ID. It may be stored outside of DiversityResources and should therefore not be changed after creation. */
  /*   ResourceCollectionID: Each resource belongs to a single resource collection (foreign key). The res. collection supplies general information not present in each item. */
  /*   URI_Part3: The part of the URI that is specific to the abstract resource item (applicable to all quality instances of the item). Inserted between the optional Class attributes URI_Part2 & Part4. Example: 'figure13.gif'. Required even if URI_ManualOverride is used. */
  /*   ItemOnlineAvailability: Online availability scope (managers, login, intranet, internet) of resource instances of this abstract item (also restricted by OnlineAvailability of resource class!). */
  /*   ItemRequestAvailability: Availabilty scope of meta information (description, keywords); access information (URI) is available only on explicit request by electronic or postal means  (also restricted by RequestAvailability of resource class!). */
  /*   NumberingCategory: For reports where the resource will be embedded in another document: Defines a numbering sequence category (fig., tab., etc.) to be used. */
  /*   LinkAssociatedIcon: Optional for hyperlinks: Complete URI of an icon that shall be displayed in addition to the text link (e.g. icon of a online journal, computer application, etc.). */
  /*   UpdateStatus: The update status (normal, new, updated) of a resource can be defined; this can be used to automatically add appropriate icons to alert users to changes. */
  /*   SourceReferenceID: For resources from published sources: the reference ID as defined in a bibliographic reference manager like DiversityReferences (RefID, foreign key). Compare also ResourceCollection.SourceReferenceID! */
  /*   SourceRefDescriptionCache: A readable description (author, year, etc.) corresponding to SourceReferenceID, automatically updated from the reference management subsystem. */
  /*   SourceDetail: Information how to locate original information in source, esp. page numbers. Do not add 'page' or 'p.'  for page numbers (incl. roman numbers) -- Specimen/culture collections: accession number or storage location code. */
  /*   SourceLabel: An identifier code used in the source from which a digitized resource is derived. This may, e. g. be the slide number of a digitized slide, or the page and figure number in a publication. */
  /*   SourceOrder: For resources from published sources: An ascending number reflecting the sequence of resources in the source. */
  /*   SourceCaption: The full, unchanged text of the original caption of the resource in the source, esp. in the case of images (if resource has fragments/parts, the captions of all fragments are entered here). */
  /*   RatingCountCache: The number of times this item was rated. */
  /*   RatingAverageCache: The average rating score as obtained by averaging all values in ResourceAbstractItem_Rating for this abstract item. */
  /*   _ParentResourceID: Optional: if the current resource is a modified version derived from another resource available in the system, this points to the original resource (ResourceID foreign key) [** not implemented, prepared for future versions**] */
  /*   IsTemplate: Defines a template for new abstract items. Default is false, should be true only a single time within each collection. A template may already include dependent records for descriptions, taxa, and keywords. */
  /*   LogCreatedBy: The user who created this record. */
  /*   LogCreatedWhen: The date and time when this record was created. */
  /*   LogUpdatedBy: The user who updated this record the last time. */
  /*   LogUpdatedWhen: The date and time when this record was last updated. */
CREATE TABLE "ResourceAbstractItem" (
    "ResourceID" 	int4	 NOT NULL PRIMARY KEY,
    "ResourceCollectionID" 	int4	 NOT NULL,
    "URI_Part3" 	varchar(255)	 NOT NULL,
    "ItemOnlineAvailability" 	int2	 NOT NULL DEFAULT 9,
    "ItemRequestAvailability" 	int2	 NOT NULL DEFAULT 9,
    "NumberingCategory" 	int2	 NOT NULL DEFAULT 0,
    "LinkAssociatedIcon" 	varchar(255)	 NULL,
    "UpdateStatus" 	int2	 NOT NULL DEFAULT 0,
    "SourceReferenceID" 	varchar(20)	 NULL,
    "SourceRefDescriptionCache" 	varchar(255)	 NULL,
    "SourceDetail" 	varchar(50)	 NULL,
    "SourceLabel" 	varchar(255)	 NULL,
    "SourceOrder" 	int4	 NULL,
    "SourceCaption" 	text	 NULL,
    "RatingCountCache" 	int4	 NOT NULL DEFAULT 0,
    "RatingAverageCache" 	float4	 NOT NULL DEFAULT 0,
    "_ParentResourceID" 	int4	 NULL,
    "IsTemplate" 	bool	 NOT NULL DEFAULT 'false',
    "LogCreatedBy" 	int4	 NOT NULL,
    "LogCreatedWhen" 	timestamp	 NOT NULL DEFAULT current_timestamp,
    "LogUpdatedBy" 	int4	 NULL,
    "LogUpdatedWhen" 	timestamp	 NULL DEFAULT current_timestamp
);




/*=== Table: "ResourceAbstractItem_Rating" ===*/
/* Individual rating scores of abstract item (relating to content, not presentation quality!). */
  /*   ResourceID: Unique system generated value (primary key). Resource ID is a public ID. It may be stored outside of DiversityResources and should therefore not be changed after creation. */
  /*   EntryDate: Time when the rating was entered. */
  /*   Rating: A numerical rating value. */
  /*   RatingComment: An optional comment associated with a rating. */
  /*   RatingID: Internal unique primary key number. */
CREATE TABLE "ResourceAbstractItem_Rating" (
    "ResourceID" 	int4	 NOT NULL,
    "EntryDate" 	timestamp	 NOT NULL DEFAULT current_timestamp,
    "Rating" 	int4	 NOT NULL DEFAULT 0,
    "RatingComment" 	varchar(255)	 NULL,
    "RatingID" 	SERIAL  	 NOT NULL PRIMARY KEY
);




/*=== Table: "ResourceDescription" ===*/
/* Description of an entire resource (or an optional resource fragment). The entity combines the description and the resource fragment definition, which could also be modeled in a 1:1 relationship. */
  /*   ResourceID: ID of external resource to which this description applies (foreign key). */
  /*   FragmentID: A number that is unique within a resource ID, identifying the resource fragment. Use '0' for entire resource. The FragmentID is used by external modules to identify a resource fragment and should therefore not be changed after creation. */
  /*   FragmentLabel: For fragments of resources: label identifying resource part, fragment, or component (e.g. figures in a composite image labeled a, b, etc.; tracks on audio resources, etc.). Use description like 'top', 'left side', etc. for unlabeled parts! */
  /*   FragmentStart: Optional: Definition in technical units of the start of this part of a multipart resource. Empty for entire resource! Use percent of height/width for images (not pixel, they are quality dependent!), seconds for audio/video, bookmark-code for text, etc. */
  /*   FragmentEnd: Optional: Definition in technical units of the end of this part of a multipart resource. Empty for entire resource! Compare FragmentStart for examples of technical units. */
  /*   Creator: Optional: Author(s), photographer, director, etc. Person(s) or institution(s) responsible for the creation of the abstract resource item (not of the instance, e.g. a scan of an image). [DC11!] */
  /*   CreationDate: Creation date (optionally also time) of the original resource. For digitized resources this should refer to the original creation (of the abstract item, e.g. when picture was drawn or photographed), not the digitization process. */
  /*   CreationMetadata: Optional: additional information regarding the creation process, e.g. image lens, scale, exposure time, etc. */
  /*   Rights: Optional: Information about rights (copyright, intellectual property) held in and over the resource. Enter esp. name of person or institution holding the copyright. Leave empty if unknown or if rights are globally described in resource collection. [DC11!] */
  /*   Language: Language of the resource itself. Empty = not yet analysed; hyphen ('-') = language neutral (e.g. images without text). Compare also language of resource title or description meta data under DescriptionTranslation. 2 letter ISO code of language  [DC 1.1!] */
  /*   InternalNotes: Internal notes, applying to the entire resource or parts of the resource. Visible only to managers of the resource collection. */
  /*   LogCreatedBy: The user who created this record. */
  /*   LogCreatedWhen: The date and time when this record was created. */
  /*   LogUpdatedBy: The user who updated this record the last time. */
  /*   LogUpdatedWhen: The date and time when this record was last updated. */
CREATE TABLE "ResourceDescription" (
    "ResourceID" 	int4	 NOT NULL,
    "FragmentID" 	int4	 NOT NULL DEFAULT 0,
    "FragmentLabel" 	varchar(255)	 NOT NULL DEFAULT '-',
    "FragmentStart" 	varchar(255)	 NULL,
    "FragmentEnd" 	varchar(255)	 NULL,
    "Creator" 	varchar(255)	 NULL,
    "CreationDate" 	timestamp	 NULL,
    "CreationMetadata" 	text	 NULL,
    "Rights" 	text	 NULL,
    "Language" 	varchar(2)	 NULL,
    "InternalNotes" 	text	 NULL,
    "LogCreatedBy" 	int4	 NOT NULL,
    "LogCreatedWhen" 	timestamp	 NOT NULL DEFAULT current_timestamp,
    "LogUpdatedBy" 	int4	 NULL,
    "LogUpdatedWhen" 	timestamp	 NULL DEFAULT current_timestamp,
  PRIMARY KEY ("ResourceID","FragmentID")
);




/*=== Table: "ResourceDescriptionTranslation" ===*/
/* Defines language specific, translatable elements associated with a resource item. The web interface supports any language for which the language or audience specific elements have been defined for the requested resource items. */
  /*   ResourceID: ID of external resource to which this description applies (foreign key). */
  /*   FragmentID: ID of fragment ('0' for entire resource) to which the display element applies (foreign key). */
  /*   Language: Language of the current title, description, keywords, etc. (i.e. language of meta data to the resource object; the language of the resource itself is defined in xxx). 2 letter ISO code of language  [DC 1.1!] */
  /*   Title: A title for the resource item, esp. used as link text if hyperlink to resource is created. If possible, the title should be unique within a resource collection (not enforced!) [DC 1.1!] */
  /*   Description: Short description of the entire resource, primarily intended for use in hyperlink lists. Basic xhtml character formatting tags may be used. If no separate Caption is used, this is also used as caption for embedded resources  [DC 1.1!] */
  /*   Caption: Optional: If Description does not already provides an adequate caption for embedded resource display, a caption may be defined here. Basic xhtml formatting tags may be used. For images: the measurement scale (example: 'bar=10 µm') should be included here. */
  /*   DisplayOrderOverride: Normally the attribute is empty and resources are sorted by title. If filled, used to sort resources in a presentation. Example: 'The Area of New York' shall be sorted under 'New York''. Numbers are sorted as text, i.e. '10'<'9', use '09', '10'! */
  /*   DisplayOrderCache: Computed field, set to DisplayOrderOverride if present, otherwise to Title. The cache is introduced to allow indexing and fast sorting. */
  /*   LogUpdatedBy: The user who created/updated the keyword. */
  /*   LogUpdatedWhen: The date and time when this record was last updated. */
CREATE TABLE "ResourceDescriptionTranslation" (
    "ResourceID" 	int4	 NOT NULL,
    "FragmentID" 	int4	 NOT NULL DEFAULT 0,
    "Language" 	varchar(2)	 NOT NULL DEFAULT 'en',
    "Title" 	varchar(255)	 NOT NULL,
    "Description" 	text	 NULL,
    "Caption" 	text	 NULL,
    "DisplayOrderOverride" 	varchar(255)	 NULL,
    "DisplayOrderCache" 	varchar(255)	 NULL,
    "LogUpdatedBy" 	int4	 NOT NULL,
    "LogUpdatedWhen" 	timestamp	 NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY ("ResourceID","FragmentID","Language")
);




/*=== Table: "ResourceKeyword" ===*/
/* Resource management subsystem: Keywords (general keywords applicable to the entire resource, not taxon-specific). */
  /*   ResourceID: ID of external resource to which the keyword applies (foreign key). */
  /*   FragmentID: ID of fragment ('0' for entire resource) to which the keyword applies (foreign key). */
  /*   Keyword: A keyword, including subject headings or private markers. A keyword entry may consist of multiple words or phrases (e. g. 'red herring'). */
  /*   LogUpdatedBy: The user who created/updated the keyword. */
  /*   LogUpdatedWhen: The date and time when this record was last updated. */
CREATE TABLE "ResourceKeyword" (
    "ResourceID" 	int4	 NOT NULL,
    "FragmentID" 	int4	 NOT NULL DEFAULT 0,
    "Keyword" 	varchar(255)	 NOT NULL,
    "LogUpdatedBy" 	int4	 NOT NULL,
    "LogUpdatedWhen" 	timestamp	 NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY ("ResourceID","FragmentID","Keyword")
);




/*=== Table: "ResourceTaxon" ===*/
/* Defines organism names, features or characteristics of the organism, and topics (nomenclature, description, distribution, etc.) covered. */
  /*   ResourceID: ID of external resource to which the taxon applies (foreign key). */
  /*   FragmentID: ID of fragment ('0' for entire resource) to which the taxon applies (foreign key). */
  /*   RecordID: Internal number providing a primary key. A combined primary key would be ResourceID, FragmentID, and either TaxonomicName or VernacularName (one of which must be filled). Such an alternative use of fields is, however, not possible in a primary key. */
  /*   TaxonomicName: Scientific name of taxon (if possible include authors). Any name can be entered, regardless of whether it is present in taxonomy or not. */
  /*   VernacularName: Non-scientific name of organism (e.g. 'white fir') or non-taxonomic name ('conifer', 'hardwood'). */
  /*   NomID: Unique ID code of the taxon. Refers to the NomID code of TaxonomyName (= foreign key) in the database DiversityTaxonomy. */
  /*   IdentificationQualifier: Qualification of the identification e.g. 'cf.', 'aff.', 'sp. nov.'. */
  /*   IdentificationResponsible: 'ORIGINAL' = according to original description (e.g. publication), else name of person responsible for a later identification. */
  /*   FeatureList: Semicolon-separated list of features of the taxon, e.g. 'ascomata; asci; ascospores; germination'. */
  /*   _TopicNomenclature: *** All fields starting with underscore are present only as a reminder, not yet decided upon! */
  /*   _TopicDescription: 0 = not covered (or negligible), 1 = partly covered, 2 = primary topic of resource. */
  /*   _TopicGeography: 0 = not covered (or negligible), 1 = partly covered, 2 = primary topic of resource. */
  /*   _TopicPhylogeny: 0 = not covered (or negligible), 1 = partly covered, 2 = primary topic of resource. */
  /*   _IDQuality: *** assessment of identification quality? */
  /*   _IDQualityResponsible: *** person responsible for setting IDQuality? */
  /*   LogUpdatedBy: The user who created/updated this record the last time. */
  /*   LogUpdatedWhen: The date and time when this record was last updated. */
CREATE TABLE "ResourceTaxon" (
    "ResourceID" 	int4	 NOT NULL,
    "FragmentID" 	int4	 NOT NULL DEFAULT 0,
    "RecordID" 	SERIAL  	 NOT NULL PRIMARY KEY,
    "TaxonomicName" 	varchar(255)	 NULL,
    "VernacularName" 	varchar(255)	 NULL,
    "NomID" 	int4	 NULL,
    "IdentificationQualifier" 	varchar(10)	 NULL,
    "IdentificationResponsible" 	varchar(50)	 NOT NULL DEFAULT ='Original',
    "FeatureList" 	text	 NULL,
    "_TopicNomenclature" 	int4	 NULL DEFAULT 0,
    "_TopicDescription" 	int4	 NULL DEFAULT 0,
    "_TopicGeography" 	int4	 NULL DEFAULT 0,
    "_TopicPhylogeny" 	int4	 NULL DEFAULT 0,
    "_IDQuality" 	varchar(255)	 NULL,
    "_IDQualityResponsible" 	varchar(255)	 NULL,
    "LogUpdatedBy" 	int4	 NULL,
    "LogUpdatedWhen" 	timestamp	 NULL DEFAULT current_timestamp
);




/*=== Table: "ResourceInstance" ===*/
/* Resource instances define attributes that apply to a specific quality instance of an abstract information resource. Inherits information from collection, abstract item, provider, and quality class. */
  /*   ResourceID: ID of external resource to which this association applies (foreign key and part of primary key). */
  /*   ResourceCollectionID: Refers to a resource collection (foreign key and part of primary key). */
  /*   ProviderOrder: Refers to one of possible several resource providers within a resource collection (foreign key and part of primary key). */
  /*   QualityLevel: A quality level for this resource class resources, as defined in RscQualityLevel_Enum (foreign key and part of primary key). */
  /*   URI_ManualOverride: If this attribute is filled with a complete URI, it is directly used, ignoring URI_Part1 to URI_Part4. */
  /*   URI_Cache: The complete URI address. If URI_ManualOverride is empty, it is generated from URI_Part1 to URI_Part4 (for digital resources that are available online). */
  /*   OnlineAvailabilityCache: (calculated value; = Min(Class.OnlineAvailability, ItemOnlineAvailability) AND current date > Collection.ReleaseDate AND (Collection.EditorialReleaseWhen (date) > Instance creation date OR Not EditorialControl)). */
  /*   RequestAvailabilityCache: (calculated value; = Min(Class.RequestAvailability, ItemRequestAvailability) AND current date > Collection.ReleaseDate AND (Collection.EditorialReleaseWhen (date) > Instance creation date OR Not EditorialControl)). */
  /*   CheckAvailability_LastResult: The result of the last availability test (set by a link checking program). */
  /*   CheckAvailability_LastStatusCode: Http status code of last availability test as defined in RFC 2616; contains 0 if status code not applicable (e. g. non-http request) or not evaluated (set by a link checking program). */
  /*   CheckAvailability_LastTestWhen: The date and time the availability was last tested (set by a link checking program). A standard testing cycle might retest every 3-6 days. The date is no longer updated when instance is deactivated. */
  /*   CheckAvailability_LastSuccessWhen: The date and time when the availability test result was positive the last time (set by a link checking program). */
  /*   CheckAvailability_Deactivated: Automatically deactivated if LastResult is negative and LastSuccessWhen more than 14 days ago (set by a link checking program). Date and time when the resource instance was deactivated can be found in CheckAvailability_LastTestWhen. */
  /*   CheckAvailability_Attempts: The total number of availability tests since the last ResetWhen (set by a link checking program). */
  /*   CheckAvailability_Failures: The total number of failed availability tests since the last ResetWhen (set by a link checking program). */
  /*   CheckAvailability_ResetWhen: Date and time when the attributes Attempts and Failures were last reset to 0 (automatically set by the reset routine of the link checking program). */
  /*   MediaTypeID: Media type according to MIME, refers to RscMediaType_Enum (foreign key). May be determined based on information in http requests or using recognized file extension (compare RscMediaTypeExtension_Enum). 0 indicates an unknown media type. */
  /*   ContentLength: Size of resource in bytes: file size or content length of download stream. The information is used to add size indications to links, helping resource consumers to make appropriate choices. The value is -1 if the size is unknown. */
  /*   LastModified: Last-Modified date obtained during resource request (e. g. from http-header). Used to identify updated instances and update attributes that can be automatically obtained (e.g. size, pixel, etc.). */
  /*   ImgPixelHeight: Optional for images: The height/width in pixel; esp. used for internet presentations; pages can be displayed better if height is known before the image is fully loaded. The value is 0 if the height is not available. */
  /*   ImgPixelWidth: Optional for images: The height/width in pixel; esp. used for internet presentations; pages can be displayed better if height is known before the image is fully loaded. The value is 0 if the width is not available. */
  /*   ImgColorDepth: Optional for images: The color depth in bits/pixel. The value is 0 if the color depth is not available. */
  /*   LogUpdatedWhen: The date and time when this record was last updated. The creation time is not recorded, since instance records are automatically created and deleted with item, provider, or quality class records. */
CREATE TABLE "ResourceInstance" (
    "ResourceID" 	int4	 NOT NULL,
    "ResourceCollectionID" 	int4	 NOT NULL,
    "ProviderOrder" 	int4	 NOT NULL,
    "QualityLevel" 	int4	 NOT NULL,
    "URI_ManualOverride" 	varchar(255)	 NULL,
    "URI_Cache" 	varchar(255)	 NULL,
    "OnlineAvailabilityCache" 	int2	 NOT NULL DEFAULT 0,
    "RequestAvailabilityCache" 	int2	 NOT NULL DEFAULT 0,
    "CheckAvailability_LastResult" 	bool	 NOT NULL,
    "CheckAvailability_LastStatusCode" 	int2	 NOT NULL DEFAULT 0,
    "CheckAvailability_LastTestWhen" 	timestamp	 NOT NULL DEFAULT 0,
    "CheckAvailability_LastSuccessWhen" 	timestamp	 NOT NULL DEFAULT 0,
    "CheckAvailability_Deactivated" 	bool	 NOT NULL DEFAULT 'false',
    "CheckAvailability_Attempts" 	int4	 NOT NULL DEFAULT 0,
    "CheckAvailability_Failures" 	int4	 NOT NULL DEFAULT 0,
    "CheckAvailability_ResetWhen" 	timestamp	 NOT NULL DEFAULT current_timestamp,
    "MediaTypeID" 	int2	 NOT NULL DEFAULT 0,
    "ContentLength" 	int4	 NOT NULL DEFAULT -1,
    "LastModified" 	timestamp	 NOT NULL DEFAULT #12/30/1899#,
    "ImgPixelHeight" 	int2	 NOT NULL DEFAULT 0,
    "ImgPixelWidth" 	int2	 NOT NULL DEFAULT 0,
    "ImgColorDepth" 	int2	 NOT NULL DEFAULT 0,
    "LogUpdatedWhen" 	timestamp	 NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY ("ResourceID","ResourceCollectionID","ProviderOrder","QualityLevel")
);




/*=== Table: "RscAutoGenerateMethod_Enum" ===*/
/* Definitions how to create automatically generate instances for resource quality classes based on other classes. */
  /*   AutoGenerateMethodID: Unique ID for the autogeneration method (primary key). The method with ID=0 is special, it defines "autogeneration is disabled". */
  /*   Title: User readable, short and unique description of autogeneration method. */
  /*   ImgPixelExtension: Maximum extension (height or width) of autogenerated images in pixel. The other dimension will be scaled so that the aspect ratio remains constant. AutoGeneratePixelHeight/Width should both be 0. */
  /*   ImgPixelHeight: Height of autogenerated images in pixel. Enter 0 to keep aspect ratio of the image and resize based on width. AutoGeneratePixelExtension should be = 0 if used. */
  /*   ImgPixelWidth: Width of autogenerated images in pixel. Enter 0 to keep aspect ratio of the image and resize based on heigth. AutoGeneratePixelExtension should be = 0 if used. */
  /*   ImgCropping: Border around the original image in percent to be cropped, cropping occurs before reducing the size as requested in ImgPixelExtension/Height/Width. In a 1000x500 pixel image, ImgCropping=10 will leave 900 x 450 pixel after cropping (5% left, 5% right). */
  /*   ImgColorDepth: If autogeneration is requested: The color depth in bits/pixel. The value 0 indicates that the color depth shall remain unchanged. */
  /*   ImgGrayscale: True if image shall be gray-scale. */
  /*   _Format: Destination format for resource instance (currently as file extension, may later be changed to MIME types) // Not used, format is already defined in URI-components through file extension! */
  /*   _Compression: Compression factor for compression algorithms causing information loss. PROBLEM: unclear how to define for all formats! */
  /*   InternalNotes: Internal notes regarding this quality version of the resource (history of editing, steps taken to generate this version, compression rate, etc.). */
CREATE TABLE "RscAutoGenerateMethod_Enum" (
    "AutoGenerateMethodID" 	int4	 NOT NULL PRIMARY KEY,
    "Title" 	varchar(65)	 NULL UNIQUE,
    "ImgPixelExtension" 	int2	 NOT NULL DEFAULT 0,
    "ImgPixelHeight" 	int2	 NOT NULL DEFAULT 0,
    "ImgPixelWidth" 	int2	 NOT NULL DEFAULT 0,
    "ImgCropping" 	int2	 NOT NULL DEFAULT 0,
    "ImgColorDepth" 	int4	 NOT NULL DEFAULT 0,
    "ImgGrayscale" 	bool	 NOT NULL,
    "_Format" 	varchar(255)	 NULL,
    "_Compression" 	varchar(255)	 NULL,
    "InternalNotes" 	varchar(255)	 NULL
);




/*=== Table: "RscHTTP11StatusCode_Enum" ===*/
/* Contains definitions of http status codes as defined in RFC 2616. Used in the context of the Availability Check (ResourceInstance) to report messages. */
  /*   Code: Code number according to internet standard RFC 2616. */
  /*   CodeText: Short title. */
  /*   Category: Heading or category. */
  /*   TechnicalDescriptionRFC2616: Full text of description. */
CREATE TABLE "RscHTTP11StatusCode_Enum" (
    "Code" 	int2	 NOT NULL PRIMARY KEY,
    "CodeText" 	varchar(255)	 NOT NULL,
    "Category" 	varchar(255)	 NOT NULL,
    "TechnicalDescriptionRFC2616" 	text	 NOT NULL
);




/*=== Table: "RscMediaType_CompleteImported" ===*/
/* Temporary table, documenting possible MIME media types. Not all media types should be made available in the system! */
CREATE TABLE "RscMediaType_CompleteImported" (
    "ID" 	SERIAL  	 NOT NULL PRIMARY KEY,
    "Type" 	varchar(255)	 NULL,
    "Subtype" 	varchar(255)	 NULL,
    "Description" 	varchar(255)	 NULL,
    "Used" 	bool	 NULL,
    "DoNotUse" 	bool	 NULL
);




/*=== Table: "RscMediaType_Enum" ===*/
/* System wide enumeration of resource media types, according to MIME (rfc 2046; e.g. file format image/gif). */
  /*   MediaTypeID: Unique internal ID number for a media type. */
  /*   MediaType: Media type according to MIME (rfc 2046; e.g. file format: image/gif). */
  /*   Description: Description of the media type, displayed in user interface. */
  /*   _WebCompatible: The quality level is compatible with current internet browsers. */
  /*   _HtmlEmbeddableImage: Resources of this format can be embedded in html files using the html image tag. */
  /*   _HtmlEmbeddableText: Resources of this format can be embedded in html files directly (header/body less html resources designed to be included elsewhere). */
CREATE TABLE "RscMediaType_Enum" (
    "MediaTypeID" 	int2	 NOT NULL PRIMARY KEY,
    "MediaType" 	varchar(255)	 NOT NULL,
    "Description" 	varchar(255)	 NOT NULL,
    "_WebCompatible" 	bool	 NOT NULL DEFAULT 'false',
    "_HtmlEmbeddableImage" 	bool	 NOT NULL DEFAULT 'false',
    "_HtmlEmbeddableText" 	bool	 NOT NULL DEFAULT 'false'
);




/*=== Table: "RscMediaTypeExtension_Enum" ===*/
/* System wide definition of file extensions to be recognized as a media type (e.g. '.gif' for image/gif). */
  /*   RecognizedFileExtension: File extension recognized for a media type. */
  /*   MediaTypeID: ID number of media type (foreign key). */
CREATE TABLE "RscMediaTypeExtension_Enum" (
    "RecognizedFileExtension" 	varchar(255)	 NOT NULL PRIMARY KEY,
    "MediaTypeID" 	int2	 NOT NULL
);




/*=== Table: "RscMediaTypeIcon_Enum" ===*/
/* System wide enumeration of generic icon images for resource media types (file formats), optionally quality level dependent. */
  /*   MediaTypeID: ID number of media type (foreign key + 1st part of primary key). */
  /*   QualityLevel: A quality level as defined in RscQualityLevel_Enum (foreign key + 2nd part of primary key). */
  /*   SmallIconURI: Complete URI of icon image used to symbolize all resource instances of this quality level. Different quality levels may have the same or different icons. */
  /*   SmallIconWidth: Width of small icon image in pixel. */
  /*   SmallIconHeight: Height of small icon image in pixel. */
  /*   LargeIconURI: Like SmallIconURI, but for a larger icon size. */
  /*   LargeIconWidth: Width of large icon image in pixel. */
  /*   LargeIconHeight: Height of large icon image in pixel. */
CREATE TABLE "RscMediaTypeIcon_Enum" (
    "MediaTypeID" 	int2	 NOT NULL,
    "QualityLevel" 	int4	 NOT NULL,
    "SmallIconURI" 	varchar(255)	 NOT NULL,
    "SmallIconWidth" 	int2	 NOT NULL DEFAULT 0,
    "SmallIconHeight" 	int2	 NOT NULL DEFAULT 0,
    "LargeIconURI" 	varchar(255)	 NOT NULL,
    "LargeIconWidth" 	int2	 NOT NULL DEFAULT 0,
    "LargeIconHeight" 	int2	 NOT NULL DEFAULT 0,
  PRIMARY KEY ("MediaTypeID","QualityLevel")
);




/*=== Table: "RscPresentationMode_Enum" ===*/
/* System wide enumeration of possible presentation modes (link, embedded, etc.). */
  /*   Mode: Unique 1 letter code defining a presentation mode (primary key). Code values are fixed and may be directly evaluated by applications. */
  /*   Title: User readable, unique title for presentation mode. */
  /*   Description: Optional additional description of presentation mode. */
  /*   DisplayOrder: Display order / sequence. */
CREATE TABLE "RscPresentationMode_Enum" (
    "Mode" 	varchar(1)	 NOT NULL PRIMARY KEY,
    "Title" 	varchar(65)	 NOT NULL UNIQUE,
    "Description" 	varchar(255)	 NULL,
    "DisplayOrder" 	int4	 NOT NULL DEFAULT 0
);




/*=== Table: "RscProviderStatus_Enum" ===*/
/* System wide enumeration of provider status, used in ResourceProvider. */
  /*   Status: Unique 1 letter code defining a provider status (primary key). Code values are fixed and may be directly evaluated by applications. */
  /*   Title: User readable, unique title for provider status. */
  /*   Description: Optional additional description of provider status. */
  /*   AccessType: 'Internet', 'LAN', 'offline storage'. */
  /*   IsOnline: Is the provider available online? */
  /*   InternetAccess: In the provider available on the Internet? */
  /*   LoadBalancing: Is the access load shared between multiple providers? */
  /*   ContentMaster: This provider provides the original resource version, from which backup or load balancing mirrors may be updated (if they are present). */
  /*   DisplayOrder: Display order / sequence. */
CREATE TABLE "RscProviderStatus_Enum" (
    "Status" 	varchar(1)	 NOT NULL PRIMARY KEY,
    "Title" 	varchar(65)	 NOT NULL UNIQUE,
    "Description" 	varchar(255)	 NULL,
    "AccessType" 	varchar(15)	 NOT NULL,
    "IsOnline" 	bool	 NOT NULL,
    "InternetAccess" 	bool	 NOT NULL,
    "LoadBalancing" 	bool	 NOT NULL,
    "ContentMaster" 	bool	 NOT NULL,
    "DisplayOrder" 	int4	 NOT NULL DEFAULT 0
);




/*=== Table: "RscQualityLevel_Enum" ===*/
/* System wide enumeration of quality levels, used in ResourceQualityClass. */
  /*   QualityLevel: Unique ID for the quality level (primary key). A higher level indicates a higher quality. */
  /*   Abbreviation: Short abbreviated description of the quality level, may displayed in the user interface. */
  /*   Title: User readable, unique title for quality level, e.g. 'original version' or 'modified'; for images: 'full resolution', 'default compressed', or 'thumbnail image'. */
  /*   Description: Additional description of quality level. */
  /*   _PresentationDefault: Default for recommended presentation mode (Link, New-window-link, Download, Embed) of new ResourceQualityClass records based on this quality level. */
  /*   _ImageAutoGenerateDefault: Default for recommended method to automatically generate instances for ResourceClasses of this quality level (currently only for images). */
  /*   _Abridged: Information in this quality level is abridged, e.g. thumbnail image preview, or short preview video sequences. */
  /*   _Internal: Information of this quality level is normally not intended for public use, esp. original scans, internal backup copies, etc. */
CREATE TABLE "RscQualityLevel_Enum" (
    "QualityLevel" 	int4	 NOT NULL PRIMARY KEY,
    "Abbreviation" 	varchar(25)	 NULL,
    "Title" 	varchar(65)	 NOT NULL UNIQUE,
    "Description" 	varchar(255)	 NOT NULL,
    "_PresentationDefault" 	varchar(1)	 NOT NULL DEFAULT 'L',
    "_ImageAutoGenerateDefault" 	int4	 NOT NULL DEFAULT 0,
    "_Abridged" 	bool	 NOT NULL DEFAULT 'false',
    "_Internal" 	bool	 NOT NULL DEFAULT 'false'
);


ALTER TABLE "ResourceCollectionManager" ADD 
  FOREIGN KEY ("ResourceCollectionID") REFERENCES "ResourceCollection" ("ResourceCollectionID") ON UPDATE CASCADE ON DELETE CASCADE
ALTER TABLE "ResourceQualityClass" ADD 
  FOREIGN KEY ("ResourceCollectionID") REFERENCES "ResourceCollection" ("ResourceCollectionID") ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY ("QualityLevel") REFERENCES "RscQualityLevel_Enum" ("QualityLevel") ON UPDATE CASCADE,
  FOREIGN KEY ("AutoGenerateMethodID") REFERENCES "RscAutoGenerateMethod_Enum" ("AutoGenerateMethodID") ON UPDATE CASCADE
ALTER TABLE "ResourceProvider" ADD 
  FOREIGN KEY ("ResourceCollectionID") REFERENCES "ResourceCollection" ("ResourceCollectionID") ON UPDATE CASCADE ON DELETE CASCADE
ALTER TABLE "ResourceAbstractItem" ADD 
  FOREIGN KEY ("ResourceCollectionID") REFERENCES "ResourceCollection" ("ResourceCollectionID") ON UPDATE CASCADE ON DELETE CASCADE
ALTER TABLE "ResourceAbstractItem_Rating" ADD 
  FOREIGN KEY ("ResourceID") REFERENCES "ResourceAbstractItem" ("ResourceID") ON UPDATE CASCADE ON DELETE CASCADE
ALTER TABLE "ResourceDescription" ADD 
  FOREIGN KEY ("ResourceID") REFERENCES "ResourceAbstractItem" ("ResourceID") ON UPDATE CASCADE ON DELETE CASCADE
ALTER TABLE "ResourceDescriptionTranslation" ADD 
  FOREIGN KEY ("ResourceID","FragmentID") REFERENCES "ResourceDescription" ("ResourceID","FragmentID") ON UPDATE CASCADE ON DELETE CASCADE
ALTER TABLE "ResourceKeyword" ADD 
  FOREIGN KEY ("ResourceID","FragmentID") REFERENCES "ResourceDescription" ("ResourceID","FragmentID") ON UPDATE CASCADE ON DELETE CASCADE
ALTER TABLE "ResourceTaxon" ADD 
  FOREIGN KEY ("ResourceID","FragmentID") REFERENCES "ResourceDescription" ("ResourceID","FragmentID") ON UPDATE CASCADE ON DELETE CASCADE
ALTER TABLE "ResourceInstance" ADD 
  FOREIGN KEY ("ResourceID") REFERENCES "ResourceAbstractItem" ("ResourceID") ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY ("ResourceCollectionID","QualityLevel") REFERENCES "ResourceQualityClass" ("ResourceCollectionID","QualityLevel") ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY ("ResourceCollectionID","ProviderOrder") REFERENCES "ResourceProvider" ("ResourceCollectionID","ProviderOrder") ON DELETE CASCADE,
  FOREIGN KEY ("MediaTypeID") REFERENCES "RscMediaType_Enum" ("MediaTypeID") ON UPDATE CASCADE
ALTER TABLE "RscMediaTypeExtension_Enum" ADD 
  FOREIGN KEY ("MediaTypeID") REFERENCES "RscMediaType_Enum" ("MediaTypeID") ON UPDATE CASCADE ON DELETE CASCADE
ALTER TABLE "RscMediaTypeIcon_Enum" ADD 
  FOREIGN KEY ("MediaTypeID") REFERENCES "RscMediaType_Enum" ("MediaTypeID") ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY ("QualityLevel") REFERENCES "RscQualityLevel_Enum" ("QualityLevel") ON UPDATE CASCADE ON DELETE CASCADE
ALTER TABLE "RscQualityLevel_Enum" ADD 
  FOREIGN KEY ("_ImageAutoGenerateDefault") REFERENCES "RscAutoGenerateMethod_Enum" ("AutoGenerateMethodID") ON UPDATE CASCADE