-- Generated by Ora2Pg, the Oracle database Schema converter, version 24.3
-- Copyright 2000-2024 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=localhost;service_name=orclpdb1;port=1521

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON

SET check_function_bodies = false;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE SCHEMA IF NOT EXISTS apidb;
CREATE SCHEMA IF NOT EXISTS apidbtuning;
CREATE SCHEMA IF NOT EXISTS core;
CREATE SCHEMA IF NOT EXISTS release;
CREATE SCHEMA IF NOT EXISTS sres;
CREATE SCHEMA IF NOT EXISTS webintegrate;
CREATE SCHEMA IF NOT EXISTS webpatch;
CREATE SCHEMA IF NOT EXISTS webwww;


CREATE TABLE apidb.datasource5290 (
	name varchar(200),
	version varchar(30),
	is_species_scope smallint,
	taxon_id bigint,
	type varchar(50),
	subtype varchar(50),
	external_database_name varchar(200),
	project_id varchar(15)
) ;


CREATE TABLE apidb.externalresourceurl (
	external_resource_url_id integer NOT NULL,
	core_version varchar(50) NOT NULL,
	database_name varchar(100) NOT NULL,
	id_url varchar(300) NOT NULL,
	modification_date timestamp(0),
	user_read smallint,
	user_write smallint,
	group_read smallint,
	group_write smallint,
	other_read smallint,
	other_write smallint,
	row_user_id bigint,
	row_group_id smallint,
	row_project_id smallint,
	row_alg_invocation_id bigint
) ;
ALTER TABLE apidb.externalresourceurl ADD PRIMARY KEY (external_resource_url_id);
ALTER TABLE apidb.externalresourceurl ALTER COLUMN external_resource_url_id SET NOT NULL;
ALTER TABLE apidb.externalresourceurl ALTER COLUMN core_version SET NOT NULL;
ALTER TABLE apidb.externalresourceurl ALTER COLUMN database_name SET NOT NULL;
ALTER TABLE apidb.externalresourceurl ALTER COLUMN id_url SET NOT NULL;


CREATE TABLE apidb.featurelocation5273 (
	sequence_source_id varchar(60),
	feature_type varchar(30),
	is_reversed smallint,
	feature_source_id varchar(80),
	is_top_level smallint,
	start_min bigint,
	end_max bigint
) ;


CREATE TABLE apidb.fileattributes (
	file_id varchar(100) NOT NULL,
	filename varchar(200),
	filepath varchar(200),
	organism varchar(100),
	build_num smallint,
	category varchar(50),
	file_type varchar(50),
	file_format varchar(10),
	filesize bigint,
	checksum varchar(100)
) ;
ALTER TABLE apidb.fileattributes ADD PRIMARY KEY (file_id);


CREATE UNLOGGED TABLE apidb.genegff5290 (
	source_id varchar(50),
	project_id varchar(50),
	table_name varchar(80),
	row_count smallint,
	content text,
	modification_date timestamp(0)
) ;


CREATE TABLE apidb.instancemetainfo (
	instance_nickname varchar(256),
	current_updater varchar(50),
	update_start timestamp(0),
	project_id varchar(20),
	version varchar(12)
) ;


CREATE TABLE apidb.obsoletetuningtable (
	name varchar(65) NOT NULL,
	timestamp timestamp(0) NOT NULL
) ;
ALTER TABLE apidb.obsoletetuningtable ADD PRIMARY KEY (name);
ALTER TABLE apidb.obsoletetuningtable ALTER COLUMN timestamp SET NOT NULL;


CREATE TABLE apidb.orthomcltaxon5271 (
	orthomcl_taxon_id bigint NOT NULL,
	parent_id bigint,
	taxon_id bigint,
	name varchar(255),
	three_letter_abbrev varchar(6) NOT NULL,
	core_peripheral varchar(1) NOT NULL,
	is_species smallint NOT NULL,
	species_order smallint,
	depth_first_index bigint NOT NULL,
	sibling_depth_first_index bigint,
	common_name varchar(255),
	modification_date timestamp(0) NOT NULL,
	user_read smallint NOT NULL,
	user_write smallint NOT NULL,
	group_read smallint NOT NULL,
	group_write smallint NOT NULL,
	other_read smallint NOT NULL,
	other_write smallint NOT NULL,
	row_user_id bigint NOT NULL,
	row_group_id smallint NOT NULL,
	row_project_id smallint NOT NULL,
	row_alg_invocation_id bigint NOT NULL
) ;
ALTER TABLE apidb.orthomcltaxon5271 ALTER COLUMN orthomcl_taxon_id SET NOT NULL;
ALTER TABLE apidb.orthomcltaxon5271 ALTER COLUMN three_letter_abbrev SET NOT NULL;
ALTER TABLE apidb.orthomcltaxon5271 ALTER COLUMN core_peripheral SET NOT NULL;
ALTER TABLE apidb.orthomcltaxon5271 ALTER COLUMN is_species SET NOT NULL;
ALTER TABLE apidb.orthomcltaxon5271 ALTER COLUMN depth_first_index SET NOT NULL;
ALTER TABLE apidb.orthomcltaxon5271 ALTER COLUMN modification_date SET NOT NULL;
ALTER TABLE apidb.orthomcltaxon5271 ALTER COLUMN user_read SET NOT NULL;
ALTER TABLE apidb.orthomcltaxon5271 ALTER COLUMN user_write SET NOT NULL;
ALTER TABLE apidb.orthomcltaxon5271 ALTER COLUMN group_read SET NOT NULL;
ALTER TABLE apidb.orthomcltaxon5271 ALTER COLUMN group_write SET NOT NULL;
ALTER TABLE apidb.orthomcltaxon5271 ALTER COLUMN other_read SET NOT NULL;
ALTER TABLE apidb.orthomcltaxon5271 ALTER COLUMN other_write SET NOT NULL;
ALTER TABLE apidb.orthomcltaxon5271 ALTER COLUMN row_user_id SET NOT NULL;
ALTER TABLE apidb.orthomcltaxon5271 ALTER COLUMN row_group_id SET NOT NULL;
ALTER TABLE apidb.orthomcltaxon5271 ALTER COLUMN row_project_id SET NOT NULL;
ALTER TABLE apidb.orthomcltaxon5271 ALTER COLUMN row_alg_invocation_id SET NOT NULL;


CREATE UNLOGGED TABLE apidb.rflpgenotypenumber4724 (
	rflp_genotype_id bigint NOT NULL,
	protocol_app_node_id bigint NOT NULL,
	genotype_number varchar(20) NOT NULL,
	modification_date timestamp(0),
	user_read smallint,
	user_write smallint,
	group_read smallint,
	group_write smallint,
	other_read smallint,
	other_write smallint,
	row_user_id bigint,
	row_group_id smallint,
	row_project_id smallint,
	row_alg_invocation_id bigint
) ;
ALTER TABLE apidb.rflpgenotypenumber4724 ALTER COLUMN rflp_genotype_id SET NOT NULL;
ALTER TABLE apidb.rflpgenotypenumber4724 ALTER COLUMN protocol_app_node_id SET NOT NULL;
ALTER TABLE apidb.rflpgenotypenumber4724 ALTER COLUMN genotype_number SET NOT NULL;


CREATE UNLOGGED TABLE apidb.transcriptdetail2013 (
	source_id varchar(100),
	gene_source_id varchar(100),
	project_id varchar(50),
	field_name varchar(50),
	field_title varchar(1000),
	row_count bigint,
	content text,
	modification_date timestamp(0)
) ;


CREATE TABLE apidb.tuningmgrexternaldependency (
	name varchar(65) NOT NULL,
	max_mod_date timestamp(0),
	timestamp timestamp(0) NOT NULL,
	row_count bigint NOT NULL
) ;
ALTER TABLE apidb.tuningmgrexternaldependency ADD PRIMARY KEY (name);
ALTER TABLE apidb.tuningmgrexternaldependency ALTER COLUMN timestamp SET NOT NULL;
ALTER TABLE apidb.tuningmgrexternaldependency ALTER COLUMN row_count SET NOT NULL;


CREATE TABLE apidb.tuningtable (
	name varchar(65) NOT NULL,
	timestamp timestamp(0) NOT NULL,
	definition text NOT NULL,
	status varchar(20),
	last_check timestamp(0),
	check_os_user varchar(20)
) ;
ALTER TABLE apidb.tuningtable ADD PRIMARY KEY (name);
ALTER TABLE apidb.tuningtable ALTER COLUMN timestamp SET NOT NULL;
ALTER TABLE apidb.tuningtable ALTER COLUMN definition SET NOT NULL;


CREATE UNLOGGED TABLE apidbtuning.associateddataset4351 (
	local_dataset_id varchar(15),
	local_project varchar(20),
	pubmed_id varchar(60),
	associated_dataset_id varchar(15),
	project varchar(20),
	webapp_name varchar(20),
	display_name varchar(200)
) ;
CREATE INDEX ldsix4351 ON apidbtuning.associateddataset4351 (local_dataset_id, pubmed_id);


CREATE TABLE apidbtuning.blasttypes5281 (
	organism varchar(414),
	project_id varchar(20),
	type varchar(20)
) ;


CREATE UNLOGGED TABLE apidbtuning.codingsequence5281 (
	source_id varchar(80),
	project_id varchar(20),
	sequence text
) ;


CREATE UNLOGGED TABLE apidbtuning.compoundattributes2902 (
	id bigint,
	source_id varchar(30) NOT NULL,
	compound_name varchar(1024),
	other_names varchar(4000),
	iupac_name varchar(4000),
	syn varchar(4000),
	definition varchar(4000),
	secondary_ids varchar(4000),
	formula varchar(4000),
	mass bigint,
	project_id char(8)
) ;
ALTER TABLE apidbtuning.compoundattributes2902 ALTER COLUMN source_id SET NOT NULL;


CREATE TABLE apidbtuning.datasetcontact5418 (
	dataset_contact_id bigint NOT NULL,
	dataset_presenter_id varchar(15),
	is_primary_contact varchar(20),
	name varchar(255),
	email varchar(255),
	affiliation varchar(255),
	city varchar(255),
	state varchar(255),
	country varchar(255),
	address varchar(255),
	zip varchar(25)
) ;
ALTER TABLE apidbtuning.datasetcontact5418 ADD PRIMARY KEY (dataset_contact_id);


CREATE TABLE apidbtuning.datasetgenelist5364 (
	source_id varchar(500),
	organism varchar(80),
	orthomcl_name varchar(60),
	profile_study_id integer,
	dataset_name varchar(200),
	dataset_presenter_id varchar(15),
	fdiff bigint,
	fdiff_abs bigint,
	min_value bigint,
	min_timepoint varchar(4000),
	max_value bigint,
	max_timepoint varchar(4000),
	myrow bigint
) ;


CREATE TABLE apidbtuning.datasethistory5418 (
	dataset_history_id bigint NOT NULL,
	dataset_presenter_id varchar(15),
	build_number bigint,
	note varchar(1000),
	genome_source varchar(300),
	genome_version varchar(40),
	annotation_source varchar(300),
	annotation_version varchar(40),
	functional_annotation_source varchar(300),
	functional_annotation_version varchar(20)
) ;
ALTER TABLE apidbtuning.datasethistory5418 ADD PRIMARY KEY (dataset_history_id);


CREATE TABLE apidbtuning.datasethyperlink5418 (
	dataset_link_id bigint NOT NULL,
	dataset_presenter_id varchar(15),
	text varchar(4000),
	description varchar(4000),
	url varchar(2000),
	ispublication varchar(1)
) ;
ALTER TABLE apidbtuning.datasethyperlink5418 ADD PRIMARY KEY (dataset_link_id);


CREATE TABLE apidbtuning.datasethyperlinks2617 (
	dataset_id varchar(15),
	dataset_name varchar(200),
	text varchar(4000),
	description varchar(4000),
	url varchar(4000)
) ;


CREATE TABLE apidbtuning.datasetmodelref5418 (
	dataset_model_ref_id bigint NOT NULL,
	dataset_presenter_id varchar(15),
	record_type varchar(100),
	target_type varchar(20),
	target_name varchar(300)
) ;
ALTER TABLE apidbtuning.datasetmodelref5418 ADD PRIMARY KEY (dataset_model_ref_id);


CREATE TABLE apidbtuning.datasetnametaxon5418 (
	dataset_taxon_id bigint NOT NULL,
	dataset_presenter_id varchar(15),
	taxon_id bigint,
	name varchar(200)
) ;
ALTER TABLE apidbtuning.datasetnametaxon5418 ADD PRIMARY KEY (dataset_taxon_id);


CREATE TABLE apidbtuning.datasetpresenter5418 (
	dataset_presenter_id varchar(15) NOT NULL,
	name varchar(200),
	project_id varchar(40),
	dataset_name_pattern varchar(200),
	display_name varchar(200),
	short_display_name varchar(200),
	short_attribution varchar(200),
	summary text,
	protocol varchar(4000),
	description text,
	usage varchar(4000),
	caveat varchar(4000),
	acknowledgement varchar(4000),
	release_policy varchar(4000),
	display_category varchar(60),
	type varchar(100),
	subtype varchar(100),
	category varchar(100),
	is_species_scope smallint,
	build_number_introduced integer,
	dataset_sha1_digest varchar(50)
) ;
ALTER TABLE apidbtuning.datasetpresenter5418 ADD PRIMARY KEY (dataset_presenter_id);


CREATE TABLE apidbtuning.datasetproperty5418 (
	dataset_property_id bigint NOT NULL,
	dataset_presenter_id varchar(15),
	property varchar(255),
	value varchar(4000)
) ;
ALTER TABLE apidbtuning.datasetproperty5418 ADD PRIMARY KEY (dataset_property_id);


CREATE TABLE apidbtuning.datasetpublication5418 (
	dataset_publication_id bigint NOT NULL,
	dataset_presenter_id varchar(15),
	pmid varchar(60),
	citation varchar(4000)
) ;
ALTER TABLE apidbtuning.datasetpublication5418 ADD PRIMARY KEY (dataset_publication_id);


CREATE TABLE apidbtuning.estattributes5272 (
	project_id varchar(20),
	source_id varchar(80),
	primer varchar(128),
	a_count bigint,
	c_count bigint,
	g_count bigint,
	t_count bigint,
	other_count bigint,
	length bigint,
	dbest_name varchar(120),
	vector varchar(4000),
	stage varchar(4000),
	organism varchar(400),
	ncbi_tax_id bigint,
	external_db_name varchar(200),
	best_alignment_count bigint,
	library_id integer,
	library_dbest_name varchar(120)
) ;


CREATE UNLOGGED TABLE apidbtuning.estsequence5194 (
	source_id varchar(80),
	project_id varchar(20),
	sequence text
) ;


CREATE UNLOGGED TABLE apidbtuning.eupathbuilddates5286 (
	build_number varchar(5),
	release_date timestamp(0),
	project varchar(20),
	release_number varchar(10)
) ;


CREATE TABLE apidbtuning.geneattributes5291 (
	project_id varchar(20),
	source_id varchar(80),
	na_feature_id bigint,
	na_sequence_id bigint,
	is_reversed smallint,
	start_min bigint,
	end_max bigint,
	strand_plus_minus varchar(1),
	sequence_id varchar(50),
	name varchar(60),
	old_product varchar(4000),
	product varchar(4000),
	gene_type varchar(400),
	gene_ebi_biotype varchar(80),
	gene_id bigint,
	is_pseudo bigint,
	organism varchar(80),
	species varchar(60),
	taxon_id bigint,
	genus_species varchar(60),
	strain varchar(4000),
	ncbi_tax_id bigint,
	so_id varchar(500),
	so_term_name varchar(150),
	so_term_definition varchar(150),
	so_version varchar(7),
	anticodon varchar(3),
	external_db_name varchar(200),
	external_db_version varchar(255),
	external_db_rls_id bigint,
	chromosome varchar(20),
	chromosome_order_num bigint,
	sequence_type varchar(400),
	transcript_count bigint,
	exon_count bigint,
	previous_ids varchar(3600),
	is_deprecated bigint,
	paralog_number bigint,
	ortholog_number bigint,
	context_start bigint,
	context_end bigint,
	orthomcl_name varchar(60),
	total_hts_snps bigint,
	hts_nonsynonymous_snps bigint,
	hts_stop_codon_snps bigint,
	hts_noncoding_snps bigint,
	hts_synonymous_snps bigint,
	hts_nonsyn_syn_ratio bigint,
	comment_string varchar(300),
	uniprot_id varchar(960),
	uniprot_id_internal varchar(960),
	entrez_id varchar(1200),
	representative_transcript varchar(80),
	zoom_context_start bigint,
	zoom_context_end bigint,
	strain_count bigint,
	locations varchar(4000)
) ;


CREATE TABLE apidbtuning.geneid5276 (
	id varchar(400),
	gene varchar(80),
	unique_mapping smallint,
	union_member varchar(400),
	database_name varchar(800)
) ;


CREATE TABLE apidbtuning.genemodelcharmd2735 (
	ontology_term_name varchar(21),
	gene_source_id varchar(80),
	source_id varchar(80),
	string_value varchar(400),
	number_value bigint,
	taxon_id bigint
) ;


CREATE TABLE apidbtuning.genemodelcharorgmd5385 (
	ontology_term_name varchar(23),
	gene_source_id varchar(80),
	source_id varchar(80),
	taxon_id bigint,
	taxon_name varchar(53),
	string_value varchar(100),
	number_value bigint
) ;


CREATE TABLE apidbtuning.genemodeldump5276 (
	source_id varchar(80),
	project_id varchar(20),
	sequence_id varchar(50),
	gm_start bigint,
	gm_end bigint,
	type varchar(30),
	is_reversed smallint,
	transcript_ids varchar(4000)
) ;


CREATE TABLE apidbtuning.genesummaryfilter5276 (
	filter_name varchar(80)
) ;


CREATE TABLE apidbtuning.geneword5281 (
	source_id varchar(80),
	taxon_id bigint,
	word varchar(800)
) ;


CREATE TABLE apidbtuning.genomicseqattributes5282 (
	project_id varchar(20),
	source_id varchar(240),
	a_count bigint,
	c_count bigint,
	g_count bigint,
	t_count bigint,
	other_count bigint,
	length bigint,
	at_percent varchar(6),
	organism varchar(400),
	ncbi_tax_id bigint,
	taxon_id bigint,
	sequence_description varchar(1600),
	genbank_accession varchar(80),
	database_version varchar(120),
	database_name varchar(200),
	chromosome varchar(80),
	external_database_release_id bigint,
	sequence_ontology_id bigint,
	chromosome_order_num bigint,
	so_id varchar(500),
	sequence_type varchar(400),
	is_top_level bigint,
	na_sequence_id bigint,
	genome_source varchar(50),
	name_for_filenames varchar(50),
	has_msa bigint
) ;


CREATE TABLE apidbtuning.genomicsequenceid5281 (
	id varchar(240),
	sequence varchar(240)
) ;


CREATE UNLOGGED TABLE apidbtuning.genomicsequencesequence5281 (
	source_id varchar(240),
	project_id varchar(20),
	sequence text
) ;


CREATE TABLE apidbtuning.genomicshyperlink2621 (
	dataset_id varchar(15),
	dataset_name varchar(200),
	text varchar(4000),
	description varchar(4000),
	url varchar(4000)
) ;


CREATE TABLE apidbtuning.genomicsinternalhyperlink5421 (
	dataset_link_id bigint,
	dataset_presenter_id varchar(15),
	description varchar(4000),
	text varchar(4000),
	url varchar(4000),
	ispublication varchar(1)
) ;


CREATE TABLE apidbtuning.gotermsummary5276 (
	gene_source_id varchar(80),
	transcript_source_id varchar(80),
	aa_sequence_id bigint,
	taxon_id bigint,
	is_not varchar(3),
	go_id varchar(100),
	go_term_id bigint,
	ontology varchar(20),
	go_term_name varchar(1000),
	source varchar(24),
	evidence_code varchar(12),
	evidence_category varchar(8),
	reference varchar(250),
	evidence_code_parameter varchar(80),
	depth bigint,
	is_go_slim bigint
) ;


CREATE TABLE apidbtuning.orfattributes2364 (
	project_id varchar(20),
	source_id varchar(240),
	lowercase_source_id varchar(240),
	organism varchar(400),
	ncbi_tax_id bigint,
	nas_id varchar(240),
	length bigint,
	start_min bigint,
	end_max bigint,
	is_reversed smallint,
	chromosome varchar(80),
	chromosome_order_num bigint,
	na_sequence_id bigint,
	na_feature_id bigint
) ;


CREATE UNLOGGED TABLE apidbtuning.orfsequence2364 (
	source_id varchar(240),
	project_id varchar(20),
	sequence text
) ;


CREATE UNLOGGED TABLE apidbtuning.orfsequenceamoeba (
	source_id varchar(240),
	project_id varchar(20),
	sequence text
) ;


CREATE UNLOGGED TABLE apidbtuning.orfsequencecrypto (
	source_id varchar(240),
	project_id varchar(20),
	sequence text
) ;


CREATE UNLOGGED TABLE apidbtuning.orfsequencefungi (
	source_id varchar(240),
	project_id varchar(20),
	sequence text
) ;


CREATE UNLOGGED TABLE apidbtuning.orfsequencegiardia (
	source_id varchar(240),
	project_id varchar(20),
	sequence text
) ;


CREATE UNLOGGED TABLE apidbtuning.orfsequencemicro (
	source_id varchar(240),
	project_id varchar(20),
	sequence text
) ;


CREATE UNLOGGED TABLE apidbtuning.orfsequencepiro (
	source_id varchar(240),
	project_id varchar(20),
	sequence text
) ;


CREATE UNLOGGED TABLE apidbtuning.orfsequenceplasmo (
	source_id varchar(240),
	project_id varchar(20),
	sequence text
) ;


CREATE UNLOGGED TABLE apidbtuning.orfsequencetoxo (
	source_id varchar(240),
	project_id varchar(20),
	sequence text
) ;


CREATE UNLOGGED TABLE apidbtuning.orfsequencetrich (
	source_id varchar(240),
	project_id varchar(20),
	sequence text
) ;


CREATE UNLOGGED TABLE apidbtuning.orfsequencetritryp (
	source_id varchar(240),
	project_id varchar(20),
	sequence text
) ;


CREATE TABLE apidbtuning.organismabbreviationblast5281 (
	organism varchar(400),
	parent varchar(400),
	abbreviation varchar(4000),
	project_id varchar(80)
) ;


CREATE TABLE apidbtuning.organismabbreviationws5281 (
	organism varchar(400),
	parent varchar(400),
	abbreviation varchar(50),
	project_id varchar(4000)
) ;


CREATE TABLE apidbtuning.organismattributes5286 (
	project_id varchar(20),
	source_id varchar(50),
	internal_abbrev varchar(30),
	public_abbrev varchar(30),
	orthomcl_abbrev varchar(20),
	family_name_for_files varchar(200),
	organism_name varchar(400),
	genome_source varchar(50),
	strain_abbrev varchar(50),
	is_annotated_genome smallint,
	is_reference_strain smallint,
	is_family_representative smallint,
	name_for_filenames varchar(50),
	component_taxon_id bigint,
	database_version varchar(120),
	megabps varchar(8),
	ncbi_tax_id bigint,
	snpcount bigint,
	genecount bigint,
	pseudogenecount bigint,
	codinggenecount bigint,
	othergenecount bigint,
	chipchipgenecount bigint,
	orthologcount bigint,
	gocount bigint,
	tfbscount bigint,
	proteomicscount bigint,
	estcount bigint,
	ecnumbercount bigint,
	isorganellar smallint,
	hashtsisolate smallint,
	haspopset smallint,
	hasepitope smallint,
	hasarray smallint,
	hascentromere bigint,
	contigcount varchar(4000),
	supercontigcount varchar(4000),
	chromosomecount varchar(4000),
	communitycount bigint,
	popsetcount bigint,
	arraygenecount bigint,
	rnaseqcount bigint,
	rtpcrcount bigint,
	avg_transcript_length bigint,
	species varchar(400),
	species_ncbi_tax_id bigint,
	strain varchar(400)
) ;


CREATE TABLE apidbtuning.organismtree5281 (
	organism varchar(400),
	term varchar(400),
	parentterm varchar(400),
	internal bigint,
	project_id varchar(4000),
	tree_level bigint,
	rank varchar(255)
) ;


CREATE TABLE apidbtuning.popsetattributes5247 (
	source_id varchar(80),
	lower_source_id varchar(80),
	project_id varchar(40),
	na_sequence_id bigint,
	protocol_app_node_id bigint,
	segment_result_id bigint,
	taxon_id bigint,
	organism varchar(400),
	description varchar(2000),
	external_database_release_id bigint,
	external_db_name varchar(200),
	external_db_version varchar(255),
	isolation_source varchar(2000),
	curated_isolation_source varchar(40),
	note varchar(2000),
	gene_name varchar(2000),
	product varchar(2000),
	geographic_location varchar(2000),
	curated_geographic_location varchar(40),
	latitude bigint,
	longitude bigint,
	lat_lon varchar(2000),
	pcr_primers varchar(2000),
	host varchar(2000),
	host_ncbi_tax_id bigint,
	popset varchar(2000),
	strain varchar(2000),
	length bigint
) ;


CREATE UNLOGGED TABLE apidbtuning.popsetsequence5090 (
	source_id varchar(80),
	project_id varchar(40),
	sequence text
) ;


CREATE UNLOGGED TABLE apidbtuning.popsetstatistics5090 (
	project_id varchar(40),
	genus varchar(1600),
	popset_count bigint
) ;


CREATE TABLE apidbtuning.projecttaxon5282 (
	taxon varchar(1600),
	project_id varchar(40)
) ;
CREATE UNIQUE INDEX projtaxuq_ix5282 ON apidbtuning.projecttaxon5282 (taxon);
CREATE UNIQUE INDEX projtax_ix5282 ON apidbtuning.projecttaxon5282 (taxon, project_id);


CREATE TABLE apidbtuning.proteinattributes5291 (
	project_id varchar(40),
	source_id varchar(60),
	aa_sequence_id bigint,
	transcript_source_id varchar(80),
	gene_source_id varchar(80),
	na_sequence_id bigint,
	is_reversed smallint,
	cds_start bigint,
	cds_end bigint,
	cds_length bigint,
	protein_length bigint,
	tm_count bigint,
	molecular_weight bigint,
	min_molecular_weight decimal(38,2),
	max_molecular_weight decimal(38,2),
	isoelectric_point decimal(38,2),
	hydropathicity_gravy_score decimal(38,2),
	aromaticity_score decimal(38,2),
	signalp_peptide varchar(800),
	ec_numbers varchar(1200),
	ec_numbers_derived varchar(1200),
	annotated_go_component varchar(1200),
	annotated_go_function varchar(1200),
	annotated_go_process varchar(1200),
	predicted_go_component varchar(1200),
	predicted_go_function varchar(1200),
	predicted_go_process varchar(1200),
	annotated_go_id_component varchar(1200),
	annotated_go_id_function varchar(1200),
	annotated_go_id_process varchar(1200),
	predicted_go_id_component varchar(1200),
	predicted_go_id_function varchar(1200),
	predicted_go_id_process varchar(1200),
	anticodon varchar(12),
	has_seqedit bigint,
	rank_in_transcript bigint,
	uniprot_ids varchar(4000)
) ;


CREATE UNLOGGED TABLE apidbtuning.proteinsequence5276 (
	source_id varchar(60),
	project_id varchar(40),
	sequence text
) ;


CREATE UNLOGGED TABLE apidbtuning.rflpgenotypes5269 (
	protocol_app_node_id bigint NOT NULL,
	output_pan_id bigint NOT NULL,
	genotypes varchar(80),
	project_id char(6)
) ;
ALTER TABLE apidbtuning.rflpgenotypes5269 ALTER COLUMN protocol_app_node_id SET NOT NULL;
ALTER TABLE apidbtuning.rflpgenotypes5269 ALTER COLUMN output_pan_id SET NOT NULL;


CREATE TABLE apidbtuning.sampleprocess5291 (
	name varchar(200),
	protocol_app_node_id bigint,
	input_name varchar(200),
	input_pan_id bigint,
	input_isa_type varchar(50),
	input_material_type varchar(400),
	protocol varchar(200),
	output_name varchar(200),
	output_pan_id bigint,
	output_isa_type varchar(50),
	output_material_type varchar(400)
) ;


CREATE TABLE apidbtuning.snpattributesdots2502 (
	project_id varchar(20),
	source_id varchar(80),
	na_feature_id bigint,
	dataset varchar(200),
	type varchar(80),
	platform varchar(50),
	na_sequence_id bigint,
	seq_source_id varchar(80),
	start_min bigint,
	reference_strain varchar(800),
	reference_na varchar(800),
	is_coding bigint,
	position_in_cds bigint,
	position_in_protein bigint,
	reference_aa varchar(800),
	has_nonsynonymous_allele varchar(3),
	major_allele varchar(160),
	major_product varchar(160),
	major_allele_count bigint,
	major_allele_frequency bigint,
	minor_allele varchar(160),
	minor_product varchar(160),
	minor_allele_count bigint,
	minor_allele_frequency bigint,
	gene_source_id varchar(80),
	gene_strand varchar(7),
	lflank varchar(120),
	rflank varchar(120),
	organism varchar(400),
	ncbi_tax_id bigint,
	chromosome varchar(80),
	chromosome_order_num bigint
) ;


CREATE UNLOGGED TABLE apidbtuning.statistics5276 (
	project_id varchar(20),
	organism varchar(400),
	gene_count bigint,
	transcript_count bigint,
	cds_count bigint,
	protein_count bigint,
	sequence_count bigint,
	total_sequence_length bigint,
	est_count bigint
) ;


CREATE TABLE apidbtuning.studyiddatasetid (
	study_stable_id varchar(200),
	dataset_id varchar(15),
	dataset_short_display_name varchar(200)
) ;


CREATE TABLE apidbtuning.transcriptattributes5291 (
	project_id varchar(20),
	source_id varchar(80),
	gene_source_id varchar(80),
	gene_na_feature_id bigint,
	gene_start_min bigint,
	gene_end_max bigint,
	gene_name varchar(60),
	old_gene_product varchar(300),
	gene_product varchar(4000),
	gene_type varchar(400),
	gene_ebi_biotype varchar(80),
	gene_id bigint,
	gene_transcript_count bigint,
	gene_exon_count bigint,
	representative_transcript varchar(80),
	gene_previous_ids varchar(3600),
	is_deprecated bigint,
	gene_paralog_number bigint,
	gene_ortholog_number bigint,
	gene_context_start bigint,
	gene_context_end bigint,
	gene_zoom_context_start bigint,
	gene_zoom_context_end bigint,
	orthomcl_name varchar(60),
	gene_total_hts_snps bigint,
	gene_hts_nonsynonymous_snps bigint,
	gene_hts_stop_codon_snps bigint,
	gene_hts_noncoding_snps bigint,
	gene_hts_synonymous_snps bigint,
	gene_hts_nonsyn_syn_ratio bigint,
	comment_string varchar(300),
	uniprot_id varchar(960),
	uniprot_id_internal varchar(960),
	gene_entrez_id varchar(1200),
	gene_locations varchar(4000),
	transcript_source_id varchar(80),
	transcript_type varchar(400),
	na_feature_id bigint,
	transcript_product varchar(300),
	start_min bigint,
	end_max bigint,
	is_reversed smallint,
	strand varchar(40),
	is_pseudo bigint,
	exon_count bigint,
	length bigint,
	spliced_na_sequence_id bigint,
	sequence_id varchar(50),
	organism varchar(80),
	species varchar(60),
	strain varchar(4000),
	ncbi_tax_id bigint,
	taxon_id bigint,
	so_id varchar(500),
	so_term_name varchar(150),
	so_term_definition varchar(150),
	so_version varchar(7),
	anticodon varchar(3),
	external_db_name varchar(200),
	external_db_version varchar(255),
	external_db_rls_id bigint,
	chromosome varchar(20),
	sequence_type varchar(400),
	chromosome_order_num bigint,
	na_sequence_id bigint,
	protein_source_id varchar(60),
	aa_sequence_id bigint,
	coding_start bigint,
	coding_end bigint,
	cds_length bigint,
	protein_length bigint,
	has_seqedit bigint,
	tm_count bigint,
	molecular_weight bigint,
	isoelectric_point decimal(38,2),
	signalp_peptide varchar(800),
	ec_numbers varchar(1200),
	ec_numbers_derived varchar(1200),
	annotated_go_component varchar(1200),
	annotated_go_function varchar(1200),
	annotated_go_process varchar(1200),
	predicted_go_component varchar(1200),
	predicted_go_function varchar(1200),
	predicted_go_process varchar(1200),
	annotated_go_id_component varchar(1200),
	annotated_go_id_function varchar(1200),
	annotated_go_id_process varchar(1200),
	predicted_go_id_component varchar(1200),
	predicted_go_id_function varchar(1200),
	predicted_go_id_process varchar(1200),
	five_prime_utr_length bigint,
	three_prime_utr_length bigint
) ;


CREATE UNLOGGED TABLE apidbtuning.transcriptsequence5276 (
	source_id varchar(80),
	project_id varchar(20),
	sequence text
) ;


CREATE TABLE core.databaseinfo (
	database_id integer NOT NULL,
	name varchar(60),
	description varchar(500) NOT NULL,
	modification_date timestamp(0) NOT NULL,
	user_read smallint NOT NULL,
	user_write smallint NOT NULL,
	group_read smallint NOT NULL,
	group_write smallint NOT NULL,
	other_read smallint NOT NULL,
	other_write smallint NOT NULL,
	row_user_id bigint NOT NULL,
	row_group_id smallint NOT NULL,
	row_project_id smallint NOT NULL,
	row_alg_invocation_id bigint NOT NULL
) ;
ALTER TABLE core.databaseinfo ALTER COLUMN database_id SET NOT NULL;
ALTER TABLE core.databaseinfo ALTER COLUMN description SET NOT NULL;
ALTER TABLE core.databaseinfo ALTER COLUMN modification_date SET NOT NULL;
ALTER TABLE core.databaseinfo ALTER COLUMN user_read SET NOT NULL;
ALTER TABLE core.databaseinfo ALTER COLUMN user_write SET NOT NULL;
ALTER TABLE core.databaseinfo ALTER COLUMN group_read SET NOT NULL;
ALTER TABLE core.databaseinfo ALTER COLUMN group_write SET NOT NULL;
ALTER TABLE core.databaseinfo ALTER COLUMN other_read SET NOT NULL;
ALTER TABLE core.databaseinfo ALTER COLUMN other_write SET NOT NULL;
ALTER TABLE core.databaseinfo ALTER COLUMN row_user_id SET NOT NULL;
ALTER TABLE core.databaseinfo ALTER COLUMN row_group_id SET NOT NULL;
ALTER TABLE core.databaseinfo ALTER COLUMN row_project_id SET NOT NULL;
ALTER TABLE core.databaseinfo ALTER COLUMN row_alg_invocation_id SET NOT NULL;


CREATE TABLE release.ares9_eupabl9n_10282020_expdp (
	abort_step bigint,
	access_method varchar(16),
	ancestor_object_name varchar(128),
	ancestor_object_schema varchar(128),
	ancestor_object_type varchar(128),
	ancestor_process_order bigint,
	base_object_name varchar(128),
	base_object_schema varchar(128),
	base_object_type varchar(128),
	base_process_order bigint,
	block_size bigint,
	cluster_ok bigint,
	completed_bytes bigint,
	completed_rows bigint,
	completion_time timestamp(0),
	control_queue varchar(128),
	creation_level bigint,
	creation_time timestamp(0),
	cumulative_time bigint,
	data_buffer_size bigint,
	data_io bigint,
	dataobj_num bigint,
	db_version varchar(60),
	degree bigint,
	domain_process_order bigint,
	dump_allocation bigint,
	dump_fileid bigint,
	dump_length bigint,
	dump_orig_length bigint,
	dump_position bigint,
	duplicate bigint,
	elapsed_time bigint,
	error_count bigint,
	extend_size bigint,
	file_max_size bigint,
	file_name varchar(4000),
	file_type bigint,
	flags bigint,
	grantor varchar(128),
	granules bigint,
	guid uuid,
	in_progress char(1),
	instance varchar(60),
	instance_id bigint,
	is_default bigint,
	job_mode varchar(21),
	job_version varchar(60),
	last_file bigint,
	last_update timestamp(0),
	load_method bigint,
	metadata_buffer_size bigint,
	metadata_io bigint,
	name varchar(128),
	object_int_oid varchar(130),
	object_long_name varchar(4000),
	object_name varchar(200),
	object_number bigint,
	object_path_seqno bigint,
	object_row bigint,
	object_schema varchar(128),
	object_tablespace varchar(128),
	object_type varchar(128),
	object_type_path varchar(200),
	objnum bigint,
	old_value varchar(4000),
	operation varchar(8),
	option_tag varchar(128),
	orig_base_object_name varchar(128),
	orig_base_object_schema varchar(128),
	original_object_name varchar(128),
	original_object_schema varchar(128),
	packet_number bigint,
	parallelization bigint,
	parent_object_name varchar(128),
	parent_object_schema varchar(128),
	parent_process_order bigint,
	partition_name varchar(128),
	phase bigint,
	platform varchar(101),
	process_name varchar(128),
	process_order bigint,
	processing_state char(1),
	processing_status char(1),
	property bigint,
	proxy_schema varchar(128),
	proxy_view varchar(128),
	queue_tabnum bigint,
	remote_link varchar(128),
	scn bigint,
	seed bigint,
	service_name varchar(64),
	size_estimate bigint,
	src_compat varchar(60),
	start_time timestamp(0),
	state varchar(12),
	status_queue varchar(128),
	subpartition_name varchar(128),
	target_xml_clob text,
	tde_rewrapped_key bytea,
	template_table varchar(128),
	timezone varchar(64),
	total_bytes bigint,
	trigflag bigint,
	unload_method bigint,
	user_directory varchar(4000),
	user_file_name varchar(4000),
	user_name varchar(128),
	value_n bigint,
	value_t varchar(4000),
	version bigint,
	work_item varchar(21),
	xml_clob text,
	xml_process_order bigint
) ;
COMMENT ON TABLE release.ares9_eupabl9n_10282020_expdp IS E'Data Pump Master Table EXPORT                         FULL                          ';
CREATE INDEX sys_mtable_000014a53_ind_1 ON release.ares9_eupabl9n_10282020_expdp (object_schema, original_object_name, object_type);
CREATE INDEX sys_mtable_000014a53_ind_2 ON release.ares9_eupabl9n_10282020_expdp (object_schema, object_name, object_type, partition_name, subpartition_name);
CREATE INDEX sys_mtable_000014a53_ind_3 ON release.ares9_eupabl9n_10282020_expdp (base_process_order);
CREATE INDEX sys_mtable_000014a53_ind_5 ON release.ares9_eupabl9n_10282020_expdp (seed);
ALTER TABLE release.ares9_eupabl9n_10282020_expdp ADD UNIQUE (process_order,duplicate);


CREATE TABLE sres.externaldatabaserelease (
	external_database_release_id bigint NOT NULL,
	external_database_id bigint NOT NULL,
	release_date timestamp(0),
	version varchar(255) NOT NULL,
	download_url varchar(255),
	id_type varchar(255),
	id_url varchar(255),
	secondary_id_type varchar(255),
	secondary_id_url varchar(255),
	description varchar(4000),
	file_name varchar(255),
	file_md5 varchar(32),
	blast_file varchar(255),
	blast_file_md5 varchar(32),
	id_is_alias smallint,
	modification_date timestamp(0) NOT NULL,
	user_read smallint NOT NULL,
	user_write smallint NOT NULL,
	group_read smallint NOT NULL,
	group_write smallint NOT NULL,
	other_read smallint NOT NULL,
	other_write smallint NOT NULL,
	row_user_id bigint NOT NULL,
	row_group_id smallint NOT NULL,
	row_project_id smallint NOT NULL,
	row_alg_invocation_id bigint NOT NULL
) ;
ALTER TABLE sres.externaldatabaserelease ADD PRIMARY KEY (external_database_release_id);
ALTER TABLE sres.externaldatabaserelease ALTER COLUMN external_database_release_id SET NOT NULL;
ALTER TABLE sres.externaldatabaserelease ALTER COLUMN external_database_id SET NOT NULL;
ALTER TABLE sres.externaldatabaserelease ALTER COLUMN version SET NOT NULL;
ALTER TABLE sres.externaldatabaserelease ALTER COLUMN modification_date SET NOT NULL;
ALTER TABLE sres.externaldatabaserelease ALTER COLUMN user_read SET NOT NULL;
ALTER TABLE sres.externaldatabaserelease ALTER COLUMN user_write SET NOT NULL;
ALTER TABLE sres.externaldatabaserelease ALTER COLUMN group_read SET NOT NULL;
ALTER TABLE sres.externaldatabaserelease ALTER COLUMN group_write SET NOT NULL;
ALTER TABLE sres.externaldatabaserelease ALTER COLUMN other_read SET NOT NULL;
ALTER TABLE sres.externaldatabaserelease ALTER COLUMN other_write SET NOT NULL;
ALTER TABLE sres.externaldatabaserelease ALTER COLUMN row_user_id SET NOT NULL;
ALTER TABLE sres.externaldatabaserelease ALTER COLUMN row_group_id SET NOT NULL;
ALTER TABLE sres.externaldatabaserelease ALTER COLUMN row_project_id SET NOT NULL;
ALTER TABLE sres.externaldatabaserelease ALTER COLUMN row_alg_invocation_id SET NOT NULL;


CREATE TABLE sres.ontologyterm (
	ontology_term_id bigint NOT NULL,
	ontology_term_type_id integer,
	name varchar(400) NOT NULL,
	definition varchar(4000),
	external_database_release_id bigint,
	source_id varchar(255),
	category varchar(100),
	ancestor_term_id bigint,
	is_user_defined smallint,
	uri varchar(500),
	is_obsolete smallint,
	taxon_id bigint,
	notes varchar(4000),
	minimum_level smallint,
	maximum_level smallint,
	number_of_levels smallint,
	modification_date timestamp(0) NOT NULL,
	user_read smallint NOT NULL,
	user_write smallint NOT NULL,
	group_read smallint NOT NULL,
	group_write smallint NOT NULL,
	other_read smallint NOT NULL,
	other_write smallint NOT NULL,
	row_user_id bigint NOT NULL,
	row_group_id smallint NOT NULL,
	row_project_id smallint NOT NULL,
	row_alg_invocation_id bigint NOT NULL
) ;
ALTER TABLE sres.ontologyterm ADD PRIMARY KEY (ontology_term_id);
ALTER TABLE sres.ontologyterm ALTER COLUMN ontology_term_id SET NOT NULL;
ALTER TABLE sres.ontologyterm ALTER COLUMN name SET NOT NULL;
ALTER TABLE sres.ontologyterm ALTER COLUMN modification_date SET NOT NULL;
ALTER TABLE sres.ontologyterm ALTER COLUMN user_read SET NOT NULL;
ALTER TABLE sres.ontologyterm ALTER COLUMN user_write SET NOT NULL;
ALTER TABLE sres.ontologyterm ALTER COLUMN group_read SET NOT NULL;
ALTER TABLE sres.ontologyterm ALTER COLUMN group_write SET NOT NULL;
ALTER TABLE sres.ontologyterm ALTER COLUMN other_read SET NOT NULL;
ALTER TABLE sres.ontologyterm ALTER COLUMN other_write SET NOT NULL;
ALTER TABLE sres.ontologyterm ALTER COLUMN row_user_id SET NOT NULL;
ALTER TABLE sres.ontologyterm ALTER COLUMN row_group_id SET NOT NULL;
ALTER TABLE sres.ontologyterm ALTER COLUMN row_project_id SET NOT NULL;
ALTER TABLE sres.ontologyterm ALTER COLUMN row_alg_invocation_id SET NOT NULL;


CREATE TABLE webintegrate.queryinstance (
	wdk_instance_id bigint NOT NULL,
	query_name varchar(200) NOT NULL,
	table_name varchar(30) NOT NULL,
	instance_checksum varchar(40) NOT NULL,
	params text,
	result_message text
) ;
ALTER TABLE webintegrate.queryinstance ADD PRIMARY KEY (wdk_instance_id);
ALTER TABLE webintegrate.queryinstance ALTER COLUMN wdk_instance_id SET NOT NULL;
ALTER TABLE webintegrate.queryinstance ALTER COLUMN query_name SET NOT NULL;
ALTER TABLE webintegrate.queryinstance ALTER COLUMN table_name SET NOT NULL;
ALTER TABLE webintegrate.queryinstance ALTER COLUMN instance_checksum SET NOT NULL;


CREATE TABLE webintegrate.step_analysis_results (
	context_hash varchar(96) NOT NULL,
	status varchar(96),
	start_date TIMESTAMP(6),
	update_date TIMESTAMP(6),
	timeout_mins bigint,
	log text,
	char_data text,
	bin_data bytea
) ;
ALTER TABLE webintegrate.step_analysis_results ADD PRIMARY KEY (context_hash);
ALTER TABLE webintegrate.step_analysis_results ALTER COLUMN context_hash SET NOT NULL;


CREATE TABLE webpatch.queryinstance (
	wdk_instance_id bigint NOT NULL,
	query_name varchar(200) NOT NULL,
	table_name varchar(30) NOT NULL,
	instance_checksum varchar(40) NOT NULL,
	params text,
	result_message text
) ;
ALTER TABLE webpatch.queryinstance ADD PRIMARY KEY (wdk_instance_id);
ALTER TABLE webpatch.queryinstance ALTER COLUMN wdk_instance_id SET NOT NULL;
ALTER TABLE webpatch.queryinstance ALTER COLUMN query_name SET NOT NULL;
ALTER TABLE webpatch.queryinstance ALTER COLUMN table_name SET NOT NULL;
ALTER TABLE webpatch.queryinstance ALTER COLUMN instance_checksum SET NOT NULL;


CREATE TABLE webpatch.step_analysis_results (
	context_hash varchar(96) NOT NULL,
	status varchar(96),
	start_date TIMESTAMP(6),
	update_date TIMESTAMP(6),
	log text,
	char_data text,
	bin_data bytea
) ;
ALTER TABLE webpatch.step_analysis_results ADD PRIMARY KEY (context_hash);
ALTER TABLE webpatch.step_analysis_results ALTER COLUMN context_hash SET NOT NULL;


CREATE TABLE webwww.queryinstance (
	wdk_instance_id bigint NOT NULL,
	query_name varchar(200) NOT NULL,
	table_name varchar(30) NOT NULL,
	instance_checksum varchar(40) NOT NULL,
	params text,
	result_message text
) ;
ALTER TABLE webwww.queryinstance ADD PRIMARY KEY (wdk_instance_id);
ALTER TABLE webwww.queryinstance ALTER COLUMN wdk_instance_id SET NOT NULL;
ALTER TABLE webwww.queryinstance ALTER COLUMN query_name SET NOT NULL;
ALTER TABLE webwww.queryinstance ALTER COLUMN table_name SET NOT NULL;
ALTER TABLE webwww.queryinstance ALTER COLUMN instance_checksum SET NOT NULL;


CREATE TABLE webwww.step_analysis_results (
	context_hash varchar(96) NOT NULL,
	status varchar(96),
	start_date TIMESTAMP(6),
	update_date TIMESTAMP(6),
	timeout_mins bigint,
	log text,
	char_data text,
	bin_data bytea
) ;
ALTER TABLE webwww.step_analysis_results ADD PRIMARY KEY (context_hash);
ALTER TABLE webwww.step_analysis_results ALTER COLUMN context_hash SET NOT NULL;
