[dbsrgits/SQL-Translator.git] / t / data / access / gdpdm.ddl

DROP TABLE div_aa_annotation;
CREATE TABLE div_aa_annotation
 (
	div_aa_annotation_id			Long Integer (4), 
	div_annotation_type_id			Long Integer (4), 
	div_allele_assay_id			Long Integer (4), 
	annotation_value			Text (50)

);
-- CREATE ANY INDEXES ...

DROP TABLE div_allele;
CREATE TABLE div_allele
 (
	div_allele_id			Long Integer (4), 
	div_obs_unit_sample_id			Long Integer (4), 
	div_allele_assay_id			Long Integer (4), 
	allele_num			Long Integer (4), 
	quality			Long Integer (4), 
	value			Text (50)

);
-- CREATE ANY INDEXES ...

DROP TABLE div_allele_assay;
CREATE TABLE div_allele_assay
 (
	div_allele_assay_id			Long Integer (4), 
	div_marker_id			Long Integer (4), 
	div_poly_type_id			Long Integer (4), 
	comments			Text (50), 
	date			DateTime (Short) (8), 
	name			Text (50), 
	phase_determined			Text (50), 
	producer			Text (50), 
	position			Text (50), 
	ref_seq			Text (50), 
	div_ref_stock_id			Long Integer (4), 
	source_assay			Text (50), 
	length			Long Integer (4)

);
-- CREATE ANY INDEXES ...

DROP TABLE div_annotation_type;
CREATE TABLE div_annotation_type
 (
	div_annotation_type_id			Long Integer (4), 
	anno_type			Text (50)

);
-- CREATE ANY INDEXES ...

DROP TABLE div_exp_entry;
CREATE TABLE div_exp_entry
 (
	div_exp_entry_id			Long Integer (4), 
	div_experiment_id			Long Integer (4), 
	div_obsunit_id			Long Integer (4), 
	div_stock_id			Long Integer (4), 
	plant			Text (50)

);
-- CREATE ANY INDEXES ...

DROP TABLE div_experiment;
CREATE TABLE div_experiment
 (
	div_experiment_id			Long Integer (4), 
	name			Text (50), 
	design			Text (50), 
	originator			Text (50)

);
-- CREATE ANY INDEXES ...

DROP TABLE div_generation;
CREATE TABLE div_generation
 (
	div_generation_id			Long Integer (4), 
	value			Text (50)

);
-- CREATE ANY INDEXES ...

DROP TABLE div_locality;
CREATE TABLE div_locality
 (
	div_locality_id			Long Integer (4), 
	elevation			Long Integer (4), 
	city			Text (50), 
	country			Text (50), 
	origcty			Text (50), 
	latitude			Long Integer (4), 
	longitude			Long Integer (4), 
	locality_name			Text (50), 
	state_province			Text (50)

);
-- CREATE ANY INDEXES ...

DROP TABLE div_locus;
CREATE TABLE div_locus
 (
	div_locus_id			Long Integer (4), 
	chromosome_number			Long Integer (4), 
	comments			Text (50), 
	genetic_bin			Text (50), 
	genetic_map			Text (50), 
	genetic_position			Long Integer (4), 
	locus_type			Text (50), 
	name			Text (50), 
	physical_position			Long Integer (4)

);
-- CREATE ANY INDEXES ...

DROP TABLE div_marker;
CREATE TABLE div_marker
 (
	div_marker_id			Long Integer (4), 
	div_locus_id			Long Integer (4), 
	name			Text (50), 
	ref_seq			Text (50), 
	div_ref_stock_id			Long Integer (4)

);
-- CREATE ANY INDEXES ...

DROP TABLE div_obs_unit;
CREATE TABLE div_obs_unit
 (
	div_obs_unit_id			Long Integer (4), 
	div_experiment_id			Long Integer (4), 
	div_stock_id			Long Integer (4), 
	div_locality_id			Long Integer (4), 
	name			Text (50), 
	field_coord_x			Long Integer (4), 
	field_coord_y			Long Integer (4), 
	rep			Long Integer (4), 
	block			Long Integer (4), 
	plot			Long Integer (4), 
	plant			Text (50), 
	planting_date			DateTime (Short) (8), 
	harvest_date			DateTime (Short) (8), 
	summary			Boolean

);
-- CREATE ANY INDEXES ...

DROP TABLE div_obs_unit_sample;
CREATE TABLE div_obs_unit_sample
 (
	div_obs_unit_sample_id			Long Integer (4), 
	div_obs_unit_id			Long Integer (4), 
	date			DateTime (Short) (8), 
	name			Text (50), 
	producer			Text (50)

);
-- CREATE ANY INDEXES ...

DROP TABLE div_passport;
CREATE TABLE div_passport
 (
	div_passport_id			Long Integer (4), 
	div_locality_id			Long Integer (4), 
	accename			Text (50), 
	collnumb			Long Integer (4), 
	collector			Text (50), 
	remarks			Text (50), 
	genus			Text (50), 
	germplasm_type			Text (50), 
	local_name			Text (50), 
	population			Text (50), 
	race_name			Text (50), 
	reference			Text (50), 
	secondary_source			Text (50), 
	source			Text (50), 
	species			Text (50), 
	subspecies			Text (50), 
	instcode			Text (50), 
	accenumb			Long Integer (4), 
	collcode			Text (50), 
	spauthor			Text (50), 
	subtaxa			Text (50), 
	subtauthor			Text (50), 
	cropname			Text (50), 
	acqdate			DateTime (Short) (8), 
	colldate			DateTime (Short) (8), 
	bredcode			Text (50), 
	sampstat			Text (50), 
	collsrc			Text (50), 
	donorcode			Text (50), 
	donornumb			Long Integer (4), 
	othernumb			Long Integer (4), 
	duplsite			Text (50), 
	storage			Text (50)

);
-- CREATE ANY INDEXES ...

DROP TABLE div_poly_type;
CREATE TABLE div_poly_type
 (
	div_poly_type_id			Long Integer (4), 
	poly_type			Text (50)

);
-- CREATE ANY INDEXES ...

DROP TABLE div_statistic_type;
CREATE TABLE div_statistic_type
 (
	div_statistic_type_id			Long Integer (4), 
	stat_type			Text (50)

);
-- CREATE ANY INDEXES ...

DROP TABLE div_stock;
CREATE TABLE div_stock
 (
	div_stock_id			Long Integer (4), 
	div_generation_id			Long Integer (4), 
	div_passport_id			Long Integer (4), 
	seed_lot			Text (50)

);
-- CREATE ANY INDEXES ...

DROP TABLE div_stock_parent;
CREATE TABLE div_stock_parent
 (
	div_stock_parent_id			Long Integer (4), 
	div_stock_id			Long Integer (4), 
	div_parent_id			Long Integer (4), 
	recurrent			Boolean, 
	role			Text (50)

);
-- CREATE ANY INDEXES ...

DROP TABLE div_trait;
CREATE TABLE div_trait
 (
	div_trait_id			Long Integer (4), 
	div_trait_uom_id			Long Integer (4), 
	div_statistic_type_id			Long Integer (4), 
	div_obs_unit_id			Long Integer (4), 
	date			DateTime (Short) (8), 
	value			Text (50)

);
-- CREATE ANY INDEXES ...

DROP TABLE div_trait_uom;
CREATE TABLE div_trait_uom
 (
	div_trait_uom_id			Long Integer (4), 
	qtl_trait_ontology_id			Long Integer (4), 
	div_unit_of_measure_id			Long Integer (4), 
	local_trait_name			Text (50)

);
-- CREATE ANY INDEXES ...

DROP TABLE div_treatment;
CREATE TABLE div_treatment
 (
	div_treatment_id			Long Integer (4), 
	div_treatment_uom_id			Long Integer (4), 
	div_obs_unit_id			Long Integer (4), 
	value			Text (50)

);
-- CREATE ANY INDEXES ...

DROP TABLE div_treatment_uom;
CREATE TABLE div_treatment_uom
 (
	div_treatment_uom_id			Long Integer (4), 
	qtl_treatment_ontology_id			Long Integer (4), 
	div_unit_of_measure_id			Long Integer (4)

);
-- CREATE ANY INDEXES ...

DROP TABLE div_unit_of_measure;
CREATE TABLE div_unit_of_measure
 (
	div_unit_of_measure_id			Long Integer (4), 
	unit_type			Text (50)

);
-- CREATE ANY INDEXES ...

DROP TABLE qtl_trait_ontology;
CREATE TABLE qtl_trait_ontology
 (
	qtl_trait_ontology_id			Long Integer (4)

);
-- CREATE ANY INDEXES ...

DROP TABLE qtl_treatment_ontology;
CREATE TABLE qtl_treatment_ontology
 (
	qtl_treatment_ontology_id			Long Integer (4)

);
-- CREATE ANY INDEXES ...


-- CREATE ANY Relationships ...
Commit	Line	Data
ef6a6784	1	DROP TABLE div_aa_annotation;
	2	CREATE TABLE div_aa_annotation
	3	(
	4	div_aa_annotation_id Long Integer (4),
	5	div_annotation_type_id Long Integer (4),
	6	div_allele_assay_id Long Integer (4),
	7	annotation_value Text (50)
	8
	9	);
	10	-- CREATE ANY INDEXES ...
	11
	12	DROP TABLE div_allele;
	13	CREATE TABLE div_allele
	14	(
	15	div_allele_id Long Integer (4),
	16	div_obs_unit_sample_id Long Integer (4),
	17	div_allele_assay_id Long Integer (4),
	18	allele_num Long Integer (4),
	19	quality Long Integer (4),
	20	value Text (50)
	21
	22	);
	23	-- CREATE ANY INDEXES ...
	24
	25	DROP TABLE div_allele_assay;
	26	CREATE TABLE div_allele_assay
	27	(
	28	div_allele_assay_id Long Integer (4),
	29	div_marker_id Long Integer (4),
	30	div_poly_type_id Long Integer (4),
	31	comments Text (50),
	32	date DateTime (Short) (8),
	33	name Text (50),
	34	phase_determined Text (50),
	35	producer Text (50),
	36	position Text (50),
	37	ref_seq Text (50),
	38	div_ref_stock_id Long Integer (4),
	39	source_assay Text (50),
	40	length Long Integer (4)
	41
	42	);
	43	-- CREATE ANY INDEXES ...
	44
	45	DROP TABLE div_annotation_type;
	46	CREATE TABLE div_annotation_type
	47	(
	48	div_annotation_type_id Long Integer (4),
	49	anno_type Text (50)
	50
	51	);
	52	-- CREATE ANY INDEXES ...
	53
	54	DROP TABLE div_exp_entry;
	55	CREATE TABLE div_exp_entry
	56	(
	57	div_exp_entry_id Long Integer (4),
	58	div_experiment_id Long Integer (4),
	59	div_obsunit_id Long Integer (4),
	60	div_stock_id Long Integer (4),
	61	plant Text (50)
	62
	63	);
	64	-- CREATE ANY INDEXES ...
65
66	DROP TABLE div_experiment;
67	CREATE TABLE div_experiment
68	(
69	div_experiment_id Long Integer (4),
70	name Text (50),
71	design Text (50),
72	originator Text (50)
73
74	);
75	-- CREATE ANY INDEXES ...
76
77	DROP TABLE div_generation;
78	CREATE TABLE div_generation
79	(
80	div_generation_id Long Integer (4),
81	value Text (50)
82
83	);
84	-- CREATE ANY INDEXES ...
85
86	DROP TABLE div_locality;
87	CREATE TABLE div_locality
88	(
89	div_locality_id Long Integer (4),
90	elevation Long Integer (4),
91	city Text (50),
92	country Text (50),
93	origcty Text (50),
94	latitude Long Integer (4),
95	longitude Long Integer (4),
96	locality_name Text (50),
97	state_province Text (50)
98
99	);
100	-- CREATE ANY INDEXES ...
101
102	DROP TABLE div_locus;
103	CREATE TABLE div_locus
104	(
105	div_locus_id Long Integer (4),
106	chromosome_number Long Integer (4),
107	comments Text (50),
108	genetic_bin Text (50),
109	genetic_map Text (50),
110	genetic_position Long Integer (4),
111	locus_type Text (50),
112	name Text (50),
113	physical_position Long Integer (4)
114
115	);
116	-- CREATE ANY INDEXES ...
117
118	DROP TABLE div_marker;
119	CREATE TABLE div_marker
120	(
121	div_marker_id Long Integer (4),
122	div_locus_id Long Integer (4),
123	name Text (50),
124	ref_seq Text (50),
125	div_ref_stock_id Long Integer (4)
126
127	);
128	-- CREATE ANY INDEXES ...
129
130	DROP TABLE div_obs_unit;
131	CREATE TABLE div_obs_unit
132	(
133	div_obs_unit_id Long Integer (4),
134	div_experiment_id Long Integer (4),
135	div_stock_id Long Integer (4),
136	div_locality_id Long Integer (4),
137	name Text (50),
138	field_coord_x Long Integer (4),
139	field_coord_y Long Integer (4),
140	rep Long Integer (4),
141	block Long Integer (4),
142	plot Long Integer (4),
143	plant Text (50),
144	planting_date DateTime (Short) (8),
145	harvest_date DateTime (Short) (8),
146	summary Boolean
147
148	);
149	-- CREATE ANY INDEXES ...
150
151	DROP TABLE div_obs_unit_sample;
152	CREATE TABLE div_obs_unit_sample
153	(
154	div_obs_unit_sample_id Long Integer (4),
155	div_obs_unit_id Long Integer (4),
156	date DateTime (Short) (8),
157	name Text (50),
158	producer Text (50)
159
160	);
161	-- CREATE ANY INDEXES ...
162
163	DROP TABLE div_passport;
164	CREATE TABLE div_passport
165	(
166	div_passport_id Long Integer (4),
167	div_locality_id Long Integer (4),
168	accename Text (50),
169	collnumb Long Integer (4),
170	collector Text (50),
171	remarks Text (50),
172	genus Text (50),
173	germplasm_type Text (50),
174	local_name Text (50),
175	population Text (50),
176	race_name Text (50),
177	reference Text (50),
178	secondary_source Text (50),
179	source Text (50),
180	species Text (50),
181	subspecies Text (50),
182	instcode Text (50),
183	accenumb Long Integer (4),
184	collcode Text (50),
185	spauthor Text (50),
186	subtaxa Text (50),
187	subtauthor Text (50),
188	cropname Text (50),
189	acqdate DateTime (Short) (8),
190	colldate DateTime (Short) (8),
191	bredcode Text (50),
192	sampstat Text (50),
193	collsrc Text (50),
194	donorcode Text (50),
195	donornumb Long Integer (4),
196	othernumb Long Integer (4),
197	duplsite Text (50),
198	storage Text (50)
199
200	);
201	-- CREATE ANY INDEXES ...
202
203	DROP TABLE div_poly_type;
204	CREATE TABLE div_poly_type
205	(
206	div_poly_type_id Long Integer (4),
207	poly_type Text (50)
208
209	);
210	-- CREATE ANY INDEXES ...
211
212	DROP TABLE div_statistic_type;
213	CREATE TABLE div_statistic_type
214	(
215	div_statistic_type_id Long Integer (4),
216	stat_type Text (50)
217
218	);
219	-- CREATE ANY INDEXES ...
220
221	DROP TABLE div_stock;
222	CREATE TABLE div_stock
223	(
224	div_stock_id Long Integer (4),
225	div_generation_id Long Integer (4),
226	div_passport_id Long Integer (4),
227	seed_lot Text (50)
228
229	);
230	-- CREATE ANY INDEXES ...
231
232	DROP TABLE div_stock_parent;
233	CREATE TABLE div_stock_parent
234	(
235	div_stock_parent_id Long Integer (4),
236	div_stock_id Long Integer (4),
237	div_parent_id Long Integer (4),
238	recurrent Boolean,
239	role Text (50)
240
241	);
242	-- CREATE ANY INDEXES ...
243
244	DROP TABLE div_trait;
245	CREATE TABLE div_trait
246	(
247	div_trait_id Long Integer (4),
248	div_trait_uom_id Long Integer (4),
249	div_statistic_type_id Long Integer (4),
250	div_obs_unit_id Long Integer (4),
251	date DateTime (Short) (8),
252	value Text (50)
253
254	);
255	-- CREATE ANY INDEXES ...
256
257	DROP TABLE div_trait_uom;
258	CREATE TABLE div_trait_uom
259	(
260	div_trait_uom_id Long Integer (4),
261	qtl_trait_ontology_id Long Integer (4),
262	div_unit_of_measure_id Long Integer (4),
263	local_trait_name Text (50)
264
265	);
266	-- CREATE ANY INDEXES ...
267
268	DROP TABLE div_treatment;
269	CREATE TABLE div_treatment
270	(
271	div_treatment_id Long Integer (4),
272	div_treatment_uom_id Long Integer (4),
273	div_obs_unit_id Long Integer (4),
274	value Text (50)
275
276	);
277	-- CREATE ANY INDEXES ...
278
279	DROP TABLE div_treatment_uom;
280	CREATE TABLE div_treatment_uom
281	(
282	div_treatment_uom_id Long Integer (4),
283	qtl_treatment_ontology_id Long Integer (4),
284	div_unit_of_measure_id Long Integer (4)
285
286	);
287	-- CREATE ANY INDEXES ...
288
289	DROP TABLE div_unit_of_measure;
290	CREATE TABLE div_unit_of_measure
291	(
292	div_unit_of_measure_id Long Integer (4),
293	unit_type Text (50)
294
295	);
296	-- CREATE ANY INDEXES ...
297
298	DROP TABLE qtl_trait_ontology;
299	CREATE TABLE qtl_trait_ontology
300	(
301	qtl_trait_ontology_id Long Integer (4)
302
303	);
304	-- CREATE ANY INDEXES ...
305
306	DROP TABLE qtl_treatment_ontology;
307	CREATE TABLE qtl_treatment_ontology
308	(
309	qtl_treatment_ontology_id Long Integer (4)
310
311	);
312	-- CREATE ANY INDEXES ...
313
314
315
316	-- CREATE ANY Relationships ...