--
-- Current Database: `ncia`
--

--
-- Table structure for table `clinical_trial`
--

DROP TABLE IF EXISTS `clinical_trial`;
CREATE TABLE `clinical_trial` (
  `trial_pk_id` bigint NOT NULL,
  `trial_sponsor_name` varchar(64) character set latin1 collate latin1_bin default NULL,
  `trial_protocol_id` varchar(64) character set latin1 collate latin1_bin default NULL,
  `trial_protocol_name` varchar(64) character set latin1 collate latin1_bin default NULL,
  `trial_coordinating_center` varchar(64) character set latin1 collate latin1_bin default NULL,
  PRIMARY KEY  (`trial_pk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



--
-- Table structure for table `clinical_trial_sponsor`
--

DROP TABLE IF EXISTS `clinical_trial_sponsor`;
CREATE TABLE `clinical_trial_sponsor` (
  `id` bigint NOT NULL,
  `coordinating_center` varchar(255) character set latin1 collate latin1_bin NOT NULL,
  `sponsor_name` varchar(255) character set latin1 collate latin1_bin NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



--
-- Table structure for table `curation_status`
--

DROP TABLE IF EXISTS `curation_status`;
CREATE TABLE `curation_status` (
  `curation_pk_id` bigint NOT NULL,
  `sop_instance_uid` varchar(64) character set latin1 collate latin1_bin default NULL,
  `curation_status` varchar(20) character set latin1 collate latin1_bin default NULL,
  `visibility` varchar(100) character set latin1 collate latin1_bin default NULL,
  `curator_name` varchar(40) character set latin1 collate latin1_bin default NULL,
  `curation_timestamp` datetime default NULL,
  `comments` varchar(1000) character set latin1 collate latin1_bin default NULL,
  `file_load_timestamp` datetime default NULL,
  `db_update_status` decimal(22,0) default NULL,
  `db_update_timestamp` datetime default NULL,
  `patient_id` varchar(64) character set latin1 collate latin1_bin default NULL,
  `study_instance_uid` varchar(500) character set latin1 collate latin1_bin default NULL,
  `series_instance_uid` varchar(64) character set latin1 collate latin1_bin default NULL,
  PRIMARY KEY  (`curation_pk_id`),
  KEY `curstatus_sop_instance_uid` (`sop_instance_uid`),
  KEY `db_update_status_idx` (`db_update_status`),
  KEY `db_update_timestamp_idx` (`db_update_timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `dicom_tag_info`
--

DROP TABLE IF EXISTS `dicom_tag_info`;
CREATE TABLE `dicom_tag_info` (
  `dicom_tag_info_pk_id` bigint NOT NULL,
  `group_num` varchar(4) character set latin1 collate latin1_bin default NULL,
  `tag_num` varchar(4) character set latin1 collate latin1_bin default NULL,
  `tag_name` varchar(100) character set latin1 collate latin1_bin default NULL,
  `table_name` varchar(30) character set latin1 collate latin1_bin default NULL,
  `column_name` varchar(30) character set latin1 collate latin1_bin default NULL,
  `cde_public_id` varchar(30) character set latin1 collate latin1_bin default NULL,
  `cde_public_id_version` varchar(20) character set latin1 collate latin1_bin default NULL,
  `description` varchar(4000) character set latin1 collate latin1_bin default NULL,
  PRIMARY KEY  (`dicom_tag_info_pk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
-- Table structure for table `download_history`
--

DROP TABLE IF EXISTS `download_history`;
CREATE TABLE `download_history` (
  `download_history_pk_id` bigint NOT NULL,
  `user_id` decimal(22,0) default NULL,
  `timestamp` datetime default NULL,
  `total_file_size` decimal(22,0) default NULL,
  `download_file_name` varchar(300) character set latin1 collate latin1_bin default NULL,
  PRIMARY KEY  (`download_history_pk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `general_equipment`
--

DROP TABLE IF EXISTS `general_equipment`;
CREATE TABLE `general_equipment` (
  `general_equipment_pk_id` bigint NOT NULL,
  `manufacturer` varchar(64) character set latin1 collate latin1_bin default NULL,
  `institution_name` varchar(64) character set latin1 collate latin1_bin default NULL,
  `manufacturer_model_name` varchar(64) character set latin1 collate latin1_bin default NULL,
  `software_versions` varchar(64) character set latin1 collate latin1_bin default NULL,
  `institution_address` varchar(1024) character set latin1 collate latin1_bin default NULL,
  `station_name` varchar(16) character set latin1 collate latin1_bin default NULL,
  `device_serial_number` varchar(64) character set latin1 collate latin1_bin default NULL,
  PRIMARY KEY  (`general_equipment_pk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



--
-- Table structure for table `grid_node`
--

DROP TABLE IF EXISTS `grid_node`;
CREATE TABLE `grid_node` (
  `grid_node_pk_id` bigint NOT NULL,
  `node_name` varchar(50) character set latin1 collate latin1_bin NOT NULL,
  `url` varchar(255) character set latin1 collate latin1_bin NOT NULL,
  `node_description` varchar(255) character set latin1 collate latin1_bin default NULL,
  PRIMARY KEY  (`grid_node_pk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `hibernate_unique_key`
--

DROP TABLE IF EXISTS `hibernate_unique_key`;
CREATE TABLE `hibernate_unique_key` (
  `next_hi` decimal(22,0) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
-- Table structure for table `image_markup`
--

DROP TABLE IF EXISTS `image_markup`;
CREATE TABLE `image_markup` (
  `image_markup_pk_id` bigint NOT NULL,
  `series_instance_uid` varchar(64) character set latin1 collate latin1_bin default NULL,
  `general_series_pk_id` bigint default NULL,
  `login_name` varchar(300) character set latin1 collate latin1_bin default NULL,
  `markup_content` longtext character set latin1 collate latin1_bin,
  `submission_date` datetime default NULL,
  PRIMARY KEY  (`image_markup_pk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
-- Table structure for table `login_history`
--

DROP TABLE IF EXISTS `login_history`;
CREATE TABLE `login_history` (
  `login_history_pk_id` bigint NOT NULL,
  `login_timestamp` datetime default NULL,
  `ip_address` varchar(15) character set latin1 collate latin1_bin default NULL,
  PRIMARY KEY  (`login_history_pk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



--
-- Table structure for table `saved_query`
--

DROP TABLE IF EXISTS `saved_query`;
CREATE TABLE `saved_query` (
  `saved_query_pk_id` bigint NOT NULL,
  `user_id` bigint default NULL,
  `query_name` varchar(200) character set latin1 collate latin1_bin default NULL,
  `new_data_flag` varchar(5) character set latin1 collate latin1_bin default NULL,
  `active` varchar(5) character set latin1 collate latin1_bin default NULL,
  `query_execute_timestamp` datetime default NULL,
  PRIMARY KEY  (`saved_query_pk_id`),
  KEY `saved_query_user_idx` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `saved_query_attribute`
--

DROP TABLE IF EXISTS `saved_query_attribute`;
CREATE TABLE `saved_query_attribute` (
  `saved_query_attribute_pk_id` bigint NOT NULL,
  `attribute_name` varchar(300) character set latin1 collate latin1_bin default NULL,
  `subattribute_name` varchar(300) character set latin1 collate latin1_bin default NULL,
  `attribute_value` varchar(300) character set latin1 collate latin1_bin default NULL,
  `saved_query_pk_id` bigint default NULL,
  `instance_number` decimal(22,0) default NULL,
  PRIMARY KEY  (`saved_query_attribute_pk_id`),
  KEY `saved_query_attr_parent` (`saved_query_pk_id`),
  CONSTRAINT `fk_saved_query_pk_id` FOREIGN KEY (`saved_query_pk_id`) REFERENCES `saved_query` (`saved_query_pk_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
-- Table structure for table `tmp_table_1`
--

DROP TABLE IF EXISTS `tmp_table_1`;
CREATE TABLE `tmp_table_1` (
  `general_series_pk_id` bigint default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `tmp_table_2`
--

DROP TABLE IF EXISTS `tmp_table_2`;
CREATE TABLE `tmp_table_2` (
  `study_pk_id` bigint default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `tmp_table_3`
--

DROP TABLE IF EXISTS `tmp_table_3`;
CREATE TABLE `tmp_table_3` (
  `patient_pk_id` bigint default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `trial_data_provenance`
--

DROP TABLE IF EXISTS `trial_data_provenance`;
CREATE TABLE `trial_data_provenance` (
  `trial_dp_pk_id` bigint NOT NULL,
  `dp_site_name` varchar(64) character set latin1 collate latin1_bin default NULL,
  `dp_site_id` varchar(64) character set latin1 collate latin1_bin default NULL,
  `project` varchar(200) character set latin1 collate latin1_bin default NULL,
  PRIMARY KEY  (`trial_dp_pk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `trial_site`
--

DROP TABLE IF EXISTS `trial_site`;
CREATE TABLE `trial_site` (
  `trial_site_pk_id` bigint NOT NULL,
  `trial_site_id` varchar(64) character set latin1 collate latin1_bin default NULL,
  `trial_site_name` varchar(64) character set latin1 collate latin1_bin default NULL,
  `trial_pk_id` bigint default NULL,
  PRIMARY KEY  (`trial_site_pk_id`),
  KEY `fk_trial_pk_id` (`trial_pk_id`),
  CONSTRAINT `fk_trial_pk_id` FOREIGN KEY (`trial_pk_id`) REFERENCES `clinical_trial` (`trial_pk_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

COMMIT;