-- MySQL dump 10.13  Distrib 5.5.62, for Win64 (AMD64)
--
-- Host: mirri-is.mirri.org    Database: mirri-db
-- ------------------------------------------------------
-- Server version	8.0.29

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `Genomic information`
--

DROP TABLE IF EXISTS `Genomic information`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Genomic information` (
  `Strain_AN` text COLLATE utf8mb4_unicode_ci,
  `Marker` text COLLATE utf8mb4_unicode_ci,
  `INSDC_AN` text COLLATE utf8mb4_unicode_ci,
  `Sequence` text COLLATE utf8mb4_unicode_ci,
  `f_id` bigint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `Geographic origin`
--

DROP TABLE IF EXISTS `Geographic origin`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Geographic origin` (
  `ID` text COLLATE utf8mb4_unicode_ci,
  `Country` text COLLATE utf8mb4_unicode_ci,
  `Region` text COLLATE utf8mb4_unicode_ci,
  `City` text COLLATE utf8mb4_unicode_ci,
  `Locality` text COLLATE utf8mb4_unicode_ci,
  `f_id` bigint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `Growth media`
--

DROP TABLE IF EXISTS `Growth media`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Growth media` (
  `Acronym` text COLLATE utf8mb4_unicode_ci,
  `Description` text COLLATE utf8mb4_unicode_ci,
  `Full_description` text COLLATE utf8mb4_unicode_ci,
  `f_id` bigint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `Literature`
--

DROP TABLE IF EXISTS `Literature`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Literature` (
  `ID` text COLLATE utf8mb4_unicode_ci,
  `Full_reference` text COLLATE utf8mb4_unicode_ci,
  `Authors` text COLLATE utf8mb4_unicode_ci,
  `Title` text COLLATE utf8mb4_unicode_ci,
  `Journal` text COLLATE utf8mb4_unicode_ci,
  `Year` text COLLATE utf8mb4_unicode_ci,
  `Volume` text COLLATE utf8mb4_unicode_ci,
  `Issue` text COLLATE utf8mb4_unicode_ci,
  `First_page` text COLLATE utf8mb4_unicode_ci,
  `Last_page` text COLLATE utf8mb4_unicode_ci,
  `Book_title` text COLLATE utf8mb4_unicode_ci,
  `Editors` text COLLATE utf8mb4_unicode_ci,
  `Publisher` text COLLATE utf8mb4_unicode_ci,
  `f_id` bigint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `Markers`
--

DROP TABLE IF EXISTS `Markers`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Markers` (
  `Acronym` text COLLATE utf8mb4_unicode_ci,
  `Marker` text COLLATE utf8mb4_unicode_ci,
  `f_id` bigint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `Ontobiotope`
--

DROP TABLE IF EXISTS `Ontobiotope`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Ontobiotope` (
  `ID` text COLLATE utf8mb4_unicode_ci,
  `Name` text COLLATE utf8mb4_unicode_ci,
  `f_id` bigint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `Sexual states`
--

DROP TABLE IF EXISTS `Sexual states`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Sexual states` (
  `H+` text COLLATE utf8mb4_unicode_ci,
  `f_id` bigint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `Strains`
--

DROP TABLE IF EXISTS `Strains`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Strains` (
  `Accession_number` text COLLATE utf8mb4_unicode_ci,
  `Restrictions_on_use` text COLLATE utf8mb4_unicode_ci,
  `Nagoya_protocol_restrictions_and_compliance_conditions` text COLLATE utf8mb4_unicode_ci,
  `ABS_related_files` text COLLATE utf8mb4_unicode_ci,
  `MTA_file` text COLLATE utf8mb4_unicode_ci,
  `Other_culture_collection_numbers` text COLLATE utf8mb4_unicode_ci,
  `Strain_from_a_registered_collection` text COLLATE utf8mb4_unicode_ci,
  `Risk_Group` text COLLATE utf8mb4_unicode_ci,
  `Dual_use` text COLLATE utf8mb4_unicode_ci,
  `Quarantine_in_Europe` text COLLATE utf8mb4_unicode_ci,
  `Organism_type` text COLLATE utf8mb4_unicode_ci,
  `Taxon_name` text COLLATE utf8mb4_unicode_ci,
  `Infrasubspecific_names` text COLLATE utf8mb4_unicode_ci,
  `Comment_on_taxonomy` text COLLATE utf8mb4_unicode_ci,
  `Interspecific_hybrid` text COLLATE utf8mb4_unicode_ci,
  `Status` text COLLATE utf8mb4_unicode_ci,
  `History_of_deposit` text COLLATE utf8mb4_unicode_ci,
  `Depositor` text COLLATE utf8mb4_unicode_ci,
  `Date_of_deposit` text COLLATE utf8mb4_unicode_ci,
  `Date_of_inclusion_in_the_catalogue` text COLLATE utf8mb4_unicode_ci,
  `Collected_by` text COLLATE utf8mb4_unicode_ci,
  `Date_of_collection` text COLLATE utf8mb4_unicode_ci,
  `Isolated_by` text COLLATE utf8mb4_unicode_ci,
  `Date_of_isolation` text COLLATE utf8mb4_unicode_ci,
  `Substrate/host_of_isolation` text COLLATE utf8mb4_unicode_ci,
  `Tested_temperature_growth_range` text COLLATE utf8mb4_unicode_ci,
  `Recommended_growth_temperature` text COLLATE utf8mb4_unicode_ci,
  `Recommended_medium_for_growth` text COLLATE utf8mb4_unicode_ci,
  `Form_of_supply` text COLLATE utf8mb4_unicode_ci,
  `Other_denomination` text COLLATE utf8mb4_unicode_ci,
  `Coordinates_of_geographic_origin` text COLLATE utf8mb4_unicode_ci,
  `Altitude_of_geographic_origin` text COLLATE utf8mb4_unicode_ci,
  `Geographic_origin` text COLLATE utf8mb4_unicode_ci,
  `Isolation_habitat` text COLLATE utf8mb4_unicode_ci,
  `Ontobiotope_term_for_the_isolation_habitat` text COLLATE utf8mb4_unicode_ci,
  `GMO` text COLLATE utf8mb4_unicode_ci,
  `GMO_construction_information` text COLLATE utf8mb4_unicode_ci,
  `Mutant_information` text COLLATE utf8mb4_unicode_ci,
  `Genotype` text COLLATE utf8mb4_unicode_ci,
  `Sexual_state` text COLLATE utf8mb4_unicode_ci,
  `Ploidy` text COLLATE utf8mb4_unicode_ci,
  `Plasmids` text COLLATE utf8mb4_unicode_ci,
  `Plasmids_collections_fields` text COLLATE utf8mb4_unicode_ci,
  `Literature` text COLLATE utf8mb4_unicode_ci,
  `Plant_pathogenicity_code` text COLLATE utf8mb4_unicode_ci,
  `Pathogenicity` text COLLATE utf8mb4_unicode_ci,
  `Enzyme_production` text COLLATE utf8mb4_unicode_ci,
  `Production_of_metabolites` text COLLATE utf8mb4_unicode_ci,
  `Applications` text COLLATE utf8mb4_unicode_ci,
  `Remarks` text COLLATE utf8mb4_unicode_ci,
  `Literature_linked_to_the_sequence/genome` text COLLATE utf8mb4_unicode_ci,
  `f_id` bigint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `culture_collections`
--

DROP TABLE IF EXISTS `culture_collections`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `culture_collections` (
  `cc_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`cc_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `file_upload`
--

DROP TABLE IF EXISTS `file_upload`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `file_upload` (
  `f_id` int NOT NULL AUTO_INCREMENT,
  `filename` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `cc_id` int DEFAULT NULL,
  PRIMARY KEY (`f_id`),
  KEY `file_upload_FK` (`cc_id`),
  CONSTRAINT `file_upload_FK` FOREIGN KEY (`cc_id`) REFERENCES `culture_collections` (`cc_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

CREATE OR REPLACE VIEW `vw_last_file` AS
select
    `fu`.`cc_id` AS `cc_id`,
    max(`fu`.`f_id`) AS `file_id`
from
    `file_upload` `fu`
group by
    `fu`.`cc_id`;