root/rita/doc/master-schema-from-aaron.migrate

Revision 196, 39.9 KB (checked in by gcc, 10 months ago)

Update schema to version 1.15.
Line 
1createTable(table("building_type",
2        column("id", INTEGER, notnull(), primarykey()),
3        column("description", VARCHAR, length(255), notnull()),
4        column("record_version", BIGINT, notnull()),
5        column("is_deleted", SMALLINT, notnull())
6        ));
7
8createTable(table("bundle",
9        column("journey_site_id", INTEGER, notnull(), primarykey()),
10        column("id", INTEGER, notnull(), primarykey()),
11        column("shipment_id", INTEGER, notnull()),
12        column("bundle_type_id", INTEGER, notnull()),
13        column("bundle_qty", INTEGER, notnull()),
14        column("bundle_idnf", VARCHAR, length(40)),
15        column("is_new_bundle_provided", SMALLINT, notnull()),
16        column("record_version", BIGINT, notnull()),
17        column("is_deleted", SMALLINT, notnull())
18        ));
19addForeignKey(new ForeignKey("fk_bundle_reference_shipment", "bundle", new String[]{"journey_site_id","shipment_id"}, "shipment", new String[]{"journey_site_id","id"}));
20addForeignKey(new ForeignKey("fk_tblconta_reference_tblcont2", "bundle", "bundle_type_id", "bundle_type", "id"));
21
22createTable(table("bundle_type",
23        column("id", INTEGER, notnull(), primarykey()),
24        column("description", VARCHAR, length(255), notnull()),
25        column("group_id", INTEGER, notnull()),
26        column("enclosure_type_code", CHAR, length(2), notnull()),
27        column("bundle_weight", NUMERIC, precision(9), scale(3), notnull()),
28        column("bundle_volume", NUMERIC, precision(9), scale(3), notnull()),
29        column("max_cap_weight", NUMERIC, precision(9), scale(3), notnull()),
30        column("max_cap_volume", NUMERIC, precision(9), scale(3), notnull()),
31        column("is_valid_for_land", SMALLINT, notnull()),
32        column("is_valid_for_water", SMALLINT, notnull()),
33        column("is_valid_for_air", SMALLINT, notnull()),
34        column("record_version", BIGINT, notnull()),
35        column("is_deleted", SMALLINT, notnull())
36        ));
37addForeignKey(new ForeignKey("fk_bundle_t_reference_bundle_t", "bundle_type", "group_id", "bundle_type_group", "id"));
38addForeignKey(new ForeignKey("fk_bundle_t_reference_enclosur", "bundle_type", "enclosure_type_code", "enclosure_type", "code"));
39
40createTable(table("bundle_type_group",
41        column("id", INTEGER, notnull(), primarykey()),
42        column("description", VARCHAR, length(255), notnull()),
43        column("is_qty_allowed", SMALLINT),
44        column("record_version", BIGINT, notnull()),
45        column("is_deleted", SMALLINT, notnull())
46        ));
47
48createTable(table("change_type",
49        column("code", CHAR, length(1), notnull(), primarykey()),
50        column("description", VARCHAR, length(255), notnull()),
51        column("record_version", BIGINT, notnull()),
52        column("is_deleted", SMALLINT, notnull())
53        ));
54
55createTable(table("contact",
56        column("owner_site_id", INTEGER, notnull(), primarykey()),
57        column("id", INTEGER, notnull(), primarykey()),
58        column("project_id", INTEGER, notnull()),
59        column("org_id", INTEGER, notnull()),
60        column("title", VARCHAR, length(40)),
61        column("name", VARCHAR, length(255), notnull()),
62        column("job_title", VARCHAR, length(255)),
63        column("address", VARCHAR, length(255)),
64        column("country_id", INTEGER),
65        column("email1", VARCHAR, length(255)),
66        column("email2", VARCHAR, length(255)),
67        column("email3", VARCHAR, length(255)),
68        column("phone1", VARCHAR, length(255)),
69        column("phone2", VARCHAR, length(255)),
70        column("phone3", VARCHAR, length(255)),
71        column("record_version", BIGINT, notnull()),
72        column("is_deleted", SMALLINT, notnull())
73        ));
74addForeignKey(new ForeignKey("fk_tblproje_reference_tblorga2", "contact", "org_id", "org", "id"));
75addForeignKey(new ForeignKey("fk_tblproje_reference_tblcoun2", "contact", "country_id", "country", "id"));
76addForeignKey(new ForeignKey("fk_contact_reference_project_", "contact", new String[]{"project_id","owner_site_id"}, "project_site", new String[]{"project_id","site_id"}));
77
78createTable(table("country",
79        column("id", INTEGER, notnull(), primarykey()),
80        column("subregion_id", INTEGER, notnull()),
81        column("name", VARCHAR, length(40), notnull()),
82        column("official_name", VARCHAR, length(40), notnull()),
83        column("alternate_name", VARCHAR, length(255)),
84        column("country_type_code", CHAR, length(1), notnull()),
85        column("currency_id", INTEGER, notnull()),
86        column("iso2_idnf", CHAR, length(2), notnull()),
87        column("iso3_idnf", CHAR, length(3), notnull()),
88        column("isonumb_idnf", INTEGER, notnull()),
89        column("latitude", NUMERIC, precision(8), scale(5)),
90        column("longitude", NUMERIC, precision(8), scale(5)),
91        column("timezone", NUMERIC, precision(3), scale(1), notnull()),
92        column("capital_city", VARCHAR, length(40)),
93        column("record_version", BIGINT, notnull()),
94        column("is_deleted", SMALLINT, notnull())
95        ));
96addForeignKey(new ForeignKey("fk_country_reference_subregio", "country", "subregion_id", "subregion", "id"));
97addForeignKey(new ForeignKey("fk_country_reference_currency", "country", "currency_id", "currency", "id"));
98addForeignKey(new ForeignKey("fk_country_reference_country_", "country", "country_type_code", "country_type", "code"));
99
100createTable(table("country_type",
101        column("code", CHAR, length(1), notnull(), primarykey()),
102        column("description", VARCHAR, length(255), notnull()),
103        column("record_version", BIGINT, notnull()),
104        column("is_deleted", SMALLINT, notnull())
105        ));
106
107createTable(table("currency",
108        column("id", INTEGER, notnull(), primarykey()),
109        column("iso3_idnf", CHAR, length(3), notnull()),
110        column("name", VARCHAR, length(40), notnull()),
111        column("decimal_places", INTEGER),
112        column("record_version", BIGINT, notnull()),
113        column("is_deleted", SMALLINT, notnull())
114        ));
115
116createTable(table("cust_order",
117        column("id", INTEGER, notnull(), primarykey()),
118        column("project_id", INTEGER, notnull()),
119        column("request_site_id", INTEGER, notnull()),
120        column("destination_site_id", INTEGER),
121        column("type_code", CHAR, length(1), notnull()),
122        column("status_code", CHAR, length(2), notnull()),
123        column("sender_site_id", INTEGER),
124        column("sender_contact_id", INTEGER),
125        column("receiver_site_id", INTEGER),
126        column("receiver_contact_id", INTEGER),
127        column("invoice_site_id", INTEGER),
128        column("invoice_contact_id", INTEGER),
129        column("on_behalf_of", VARCHAR, length(255)),
130        column("description", VARCHAR, length(255)),
131        column("product_id", INTEGER),
132        column("load_site_desc", VARCHAR, length(255)),
133        column("est_deliver_time", TIMESTAMP),
134        column("cargo_prefer_move_date", DATE),
135        column("shipment_docm_idnf", VARCHAR, length(40)),
136        column("created_by_logon", VARCHAR, length(40), notnull()),
137        column("created_time", TIMESTAMP, notnull()),
138        column("record_version", BIGINT, notnull()),
139        column("is_deleted", SMALLINT, notnull())
140        ));
141addForeignKey(new ForeignKey("fk_cust_ord_reference_product2", "cust_order", "product_id", "product", "id"));
142addForeignKey(new ForeignKey("fk_cust_ord_reference_contact3", "cust_order", new String[]{"receiver_site_id","receiver_contact_id"}, "contact", new String[]{"owner_site_id","id"}));
143addForeignKey(new ForeignKey("fk_cust_ord_reference_contact", "cust_order", new String[]{"sender_site_id","sender_contact_id"}, "contact", new String[]{"owner_site_id","id"}));
144addForeignKey(new ForeignKey("fk_cust_ord_reference_project_", "cust_order", new String[]{"project_id","destination_site_id"}, "project_site", new String[]{"project_id","site_id"}));
145addForeignKey(new ForeignKey("fk_cust_ord_reference_request_2", "cust_order", "status_code", "request_status", "code"));
146addForeignKey(new ForeignKey("fk_cust_ord_reference_request_3", "cust_order", "type_code", "request_type", "code"));
147addForeignKey(new ForeignKey("fk_cust_ord_reference_project_2", "cust_order", new String[]{"project_id","request_site_id"}, "project_site", new String[]{"project_id","site_id"}));
148addForeignKey(new ForeignKey("fk_cust_ord_reference_contact2", "cust_order", new String[]{"invoice_site_id","invoice_contact_id"}, "contact", new String[]{"owner_site_id","id"}));
149
150createTable(table("cust_order_history",
151        column("commenting_site_id", INTEGER, notnull(), primarykey()),
152        column("cust_order_id", INTEGER, notnull(), primarykey()),
153        column("change_time", TIMESTAMP, notnull(), primarykey()),
154        column("chng_type_code", CHAR, length(1), notnull()),
155        column("new_status_code", CHAR, length(2)),
156        column("remarks", VARCHAR, length(255), notnull()),
157        column("created_by_logon", VARCHAR, length(40), notnull()),
158        column("record_version", BIGINT, notnull()),
159        column("is_deleted", SMALLINT, notnull())
160        ));
161addForeignKey(new ForeignKey("fk_cust_ord_reference_request_", "cust_order_history", "new_status_code", "request_status", "code"));
162addForeignKey(new ForeignKey("fk_cust_ord_reference_change_t", "cust_order_history", "chng_type_code", "change_type", "code"));
163addForeignKey(new ForeignKey("fk_cust_ord_reference_cust_ord", "cust_order_history", "cust_order_id", "cust_order", "id"));
164
165createTable(table("cust_order_line",
166        column("id", INTEGER, notnull(), primarykey()),
167        column("cust_order_id", INTEGER, notnull()),
168        column("seqno", INTEGER, notnull()),
169        column("kit_type_code", CHAR, length(1), notnull()),
170        column("parent_line_id", INTEGER),
171        column("ownr_item_desc", VARCHAR, length(255), notnull()),
172        column("ownr_item_idnf", VARCHAR, length(40)),
173        column("product_id", INTEGER),
174        column("is_cold_storage", SMALLINT, notnull()),
175        column("cold_min_temp", NUMERIC, precision(4), scale(1)),
176        column("cold_max_temp", NUMERIC, precision(4), scale(1)),
177        column("is_dangerous", SMALLINT, notnull()),
178        column("is_regulated", SMALLINT, notnull()),
179        column("sku_id", INTEGER),
180        column("sku_qty", INTEGER),
181        column("ltu_id", INTEGER, notnull()),
182        column("ltu_qty", INTEGER, notnull()),
183        column("ltu_weight", NUMERIC, precision(9), scale(3), notnull()),
184        column("ltu_volume", NUMERIC, precision(9), scale(3), notnull()),
185        column("bundle_type_id", INTEGER, notnull()),
186        column("bundle_qty", INTEGER, notnull()),
187        column("record_version", BIGINT, notnull()),
188        column("is_deleted", SMALLINT, notnull())
189        ));
190addForeignKey(new ForeignKey("fk_cust_ord_reference_product_2", "cust_order_line", "ltu_id", "product_ltu", "id"));
191addForeignKey(new ForeignKey("fk_cust_ord_reference_cust_ord2", "cust_order_line", "cust_order_id", "cust_order", "id"));
192addForeignKey(new ForeignKey("fk_cust_ord_reference_product_", "cust_order_line", "sku_id", "product_sku", "id"));
193addForeignKey(new ForeignKey("fk_cust_ord_reference_product", "cust_order_line", "product_id", "product", "id"));
194
195createTable(table("dispatch_priority",
196        column("id", INTEGER, notnull(), primarykey()),
197        column("description", VARCHAR, length(255), notnull()),
198        column("seqno", INTEGER, notnull()),
199        column("name", VARCHAR, length(40), notnull()),
200        column("record_version", BIGINT, notnull()),
201        column("is_deleted", SMALLINT, notnull())
202        ));
203
204createTable(table("distribution_type",
205        column("id", INTEGER, notnull(), primarykey()),
206        column("description", VARCHAR, length(255), notnull()),
207        column("record_version", BIGINT, notnull()),
208        column("is_deleted", SMALLINT, notnull())
209        ));
210
211createTable(table("enclosure_type",
212        column("code", CHAR, length(2), notnull(), primarykey()),
213        column("description", VARCHAR, length(255), notnull()),
214        column("record_version", BIGINT, notnull()),
215        column("is_deleted", SMALLINT, notnull())
216        ));
217
218createTable(table("exchange_rate",
219        column("currency_id", INTEGER, notnull(), primarykey()),
220        column("take_effect_date", DATE, notnull(), primarykey()),
221        column("exch_rate_usd", NUMERIC, precision(10), scale(4)),
222        column("record_version", BIGINT, notnull()),
223        column("is_deleted", SMALLINT, notnull())
224        ));
225addForeignKey(new ForeignKey("fk_exchange_reference_currency", "exchange_rate", "currency_id", "currency", "id"));
226
227createTable(table("journey",
228        column("journey_site_id", INTEGER, notnull(), primarykey()),
229        column("id", INTEGER, notnull(), primarykey()),
230        column("project_id", INTEGER, notnull()),
231        column("origin_site_id", INTEGER, notnull()),
232        column("destination_site_id", INTEGER, notnull()),
233        column("is_dispatched", SMALLINT, notnull()),
234        column("est_dispatch_time", TIMESTAMP, notnull()),
235        column("actual_dispatch_time", TIMESTAMP),
236        column("est_delivery_time", TIMESTAMP, notnull()),
237        column("is_delivery_note_received", SMALLINT, notnull()),
238        column("load_site_desc", VARCHAR, length(255)),
239        column("beneficiary_site_desc", VARCHAR, length(255)),
240        column("vehicle_id", INTEGER),
241        column("route_cost_id", INTEGER),
242        column("call_sign", VARCHAR, length(40)),
243        column("driver_name", VARCHAR, length(255)),
244        column("driver_idnf", VARCHAR, length(255)),
245        column("driver_contact", VARCHAR, length(255)),
246        column("remarks_dispatch", VARCHAR, length(255)),
247        column("record_version", BIGINT, notnull()),
248        column("is_deleted", SMALLINT, notnull())
249        ));
250        addIndex(uniqueIndex("ak_sourcedestsite_journey", "journey", "journey_site_id","id","destination_site_id"));
251addForeignKey(new ForeignKey("fk_journey_reference_vehicle", "journey", new String[]{"journey_site_id","vehicle_id"}, "vehicle", new String[]{"journey_site_id","id"}));
252addForeignKey(new ForeignKey("fk_journey_reference_route_co", "journey", new String[]{"journey_site_id","route_cost_id"}, "route_cost", new String[]{"journey_site_id","id"}));
253addForeignKey(new ForeignKey("fk_journey_reference_project_2", "journey", new String[]{"project_id","destination_site_id"}, "project_site", new String[]{"project_id","site_id"}));
254addForeignKey(new ForeignKey("fk_journey_reference_project_", "journey", new String[]{"project_id","journey_site_id"}, "project_site", new String[]{"project_id","site_id"}));
255
256createTable(table("journey_comment",
257        column("destination_site_id", INTEGER, notnull(), primarykey()),
258        column("journey_site_id", INTEGER, notnull(), primarykey()),
259        column("journey_id", INTEGER, notnull(), primarykey()),
260        column("actual_delivery_time", TIMESTAMP, notnull()),
261        column("remarks_delivery", VARCHAR, length(255)),
262        column("record_version", BIGINT, notnull()),
263        column("is_deleted", SMALLINT, notnull())
264        ));
265addForeignKey(new ForeignKey("fk_journey__reference_journey", "journey_comment", new String[]{"journey_site_id","journey_id","destination_site_id"}, "journey", new String[]{"journey_site_id","id","destination_site_id"}));
266
267createTable(table("kit_type",
268        column("code", CHAR, length(1), notnull(), primarykey()),
269        column("description", VARCHAR, length(255), notnull()),
270        column("record_version", BIGINT, notnull()),
271        column("is_deleted", SMALLINT, notnull())
272        ));
273
274createTable(table("languages",
275        column("iso2_idnf", CHAR, length(2), notnull(), primarykey()),
276        column("english_desc", VARCHAR, length(255), notnull()),
277        column("original_desc", VARCHAR, length(255), notnull()),
278        column("record_version", BIGINT, notnull()),
279        column("is_deleted", SMALLINT, notnull())
280        ));
281
282createTable(table("movement",
283        column("move_site_id", INTEGER, notnull(), primarykey()),
284        column("id", INTEGER, notnull(), primarykey()),
285        column("journey_site_id", INTEGER, notnull()),
286        column("bundle_id", INTEGER, notnull()),
287        column("request_site_id", INTEGER, notnull()),
288        column("request_id", INTEGER, notnull()),
289        column("request_line_id", INTEGER, notnull()),
290        column("owner_code", CHAR, length(1), notnull()),
291        column("ltu_qty", INTEGER, notnull()),
292        column("ltu_weight", NUMERIC, precision(9), scale(3), notnull()),
293        column("ltu_volume", NUMERIC, precision(9), scale(3), notnull()),
294        column("record_version", BIGINT, notnull()),
295        column("is_deleted", SMALLINT, notnull())
296        ));
297addForeignKey(new ForeignKey("fk_movement_reference_bundle", "movement", new String[]{"journey_site_id","bundle_id"}, "bundle", new String[]{"journey_site_id","id"}));
298addForeignKey(new ForeignKey("fk_tblmovem_reference_tblrequ2", "movement", new String[]{"request_line_id","request_site_id"}, "request_line", new String[]{"id","request_site_id"}));
299addForeignKey(new ForeignKey("fk_movement_reference_request", "movement", new String[]{"request_site_id","request_id"}, "request", new String[]{"request_site_id","id"}));
300
301createTable(table("movement_comment",
302        column("move_site_id", INTEGER, notnull(), primarykey()),
303        column("id", INTEGER, notnull(), primarykey()),
304        column("remarks", VARCHAR, length(255)),
305        column("record_version", BIGINT, notnull()),
306        column("is_deleted", SMALLINT, notnull())
307        ));
308addForeignKey(new ForeignKey("fk_movement_reference_movement", "movement_comment", new String[]{"move_site_id","id"}, "movement", new String[]{"move_site_id","id"}));
309
310createTable(table("org",
311        column("id", INTEGER, notnull(), primarykey()),
312        column("name", VARCHAR, length(100), notnull()),
313        column("abrv", VARCHAR, length(40)),
314        column("org_type_code", CHAR, length(3), notnull()),
315        column("is_intl", SMALLINT, notnull()),
316        column("is_donor", SMALLINT, notnull()),
317        column("country_id", INTEGER),
318        column("website", VARCHAR, length(255)),
319        column("wings_idnf", VARCHAR, length(40)),
320        column("compas_idnf", VARCHAR, length(40)),
321        column("dacota_idnf", INTEGER),
322        column("record_version", BIGINT, notnull()),
323        column("is_deleted", SMALLINT, notnull())
324        ));
325addForeignKey(new ForeignKey("fk_org_reference_org_type", "org", "org_type_code", "org_type", "code"));
326addForeignKey(new ForeignKey("fk_org_reference_country", "org", "country_id", "country", "id"));
327
328createTable(table("org_address",
329        column("id", INTEGER, notnull(), primarykey()),
330        column("org_id", INTEGER, notnull()),
331        column("address_line1", VARCHAR, length(255), notnull()),
332        column("address_line2", VARCHAR, length(255)),
333        column("address_line3", VARCHAR, length(255)),
334        column("address_postcode", VARCHAR, length(255)),
335        column("address_country_id", INTEGER, notnull()),
336        column("is_billing_address", SMALLINT, notnull()),
337        column("is_mailing_address", SMALLINT, notnull()),
338        column("is_default", SMALLINT, notnull()),
339        column("is_valid", SMALLINT, notnull()),
340        column("record_version", BIGINT, notnull()),
341        column("is_deleted", SMALLINT, notnull())
342        ));
343addForeignKey(new ForeignKey("fk_org_addr_reference_org", "org_address", "org_id", "org", "id"));
344
345createTable(table("org_type",
346        column("code", CHAR, length(3), notnull(), primarykey()),
347        column("description", VARCHAR, length(255), notnull()),
348        column("is_system", SMALLINT, notnull()),
349        column("record_version", BIGINT, notnull()),
350        column("is_deleted", SMALLINT, notnull())
351        ));
352
353createTable(table("port_type",
354        column("id", INTEGER, notnull(), primarykey()),
355        column("description", VARCHAR, length(255), notnull()),
356        column("record_version", BIGINT, notnull()),
357        column("is_deleted", SMALLINT, notnull())
358        ));
359
360createTable(table("product",
361        column("id", INTEGER, notnull(), primarykey()),
362        column("product_idnf", CHAR, length(18), notnull()),
363        column("description", VARCHAR, length(255), notnull()),
364        column("level", INTEGER),
365        column("parent_id", INTEGER),
366        column("dflt_ltu_id", INTEGER),
367        column("dflt_sku_id", INTEGER),
368        column("is_dangerous", SMALLINT),
369        column("is_regulated", SMALLINT),
370        column("is_cold_storage", SMALLINT),
371        column("cold_min_temp", NUMERIC, precision(4), scale(1)),
372        column("cold_max_temp", NUMERIC, precision(4), scale(1)),
373        column("record_version", BIGINT, notnull()),
374        column("is_deleted", SMALLINT, notnull())
375        ));
376addForeignKey(new ForeignKey("fk_product_reference_product_2", "product", "dflt_ltu_id", "product_ltu", "id"));
377addForeignKey(new ForeignKey("fk_product_reference_product_", "product", "dflt_sku_id", "product_sku", "id"));
378
379createTable(table("product_ltu",
380        column("id", INTEGER, notnull(), primarykey()),
381        column("description", VARCHAR, length(255), notnull()),
382        column("record_version", BIGINT, notnull()),
383        column("is_deleted", SMALLINT, notnull())
384        ));
385
386createTable(table("product_sku",
387        column("id", INTEGER, notnull(), primarykey()),
388        column("description", VARCHAR, length(255), notnull()),
389        column("record_version", BIGINT, notnull()),
390        column("is_deleted", SMALLINT, notnull())
391        ));
392
393createTable(table("project",
394        column("id", INTEGER, notnull(), primarykey()),
395        column("project_idnf", CHAR, length(10)),
396        column("name", VARCHAR, length(40), notnull()),
397        column("description", VARCHAR, length(255), notnull()),
398        column("org_id", INTEGER, notnull()),
399        column("region_id", INTEGER, notnull()),
400        column("subregion_id", INTEGER),
401        column("country_id", INTEGER),
402        column("purpose_id", INTEGER, notnull()),
403        column("is_logcluster", SMALLINT, notnull()),
404        column("approval_date", DATE, notnull()),
405        column("end_date", DATE),
406        column("close_date", DATE),
407        column("external_idnf", CHAR, length(15)),
408        column("budget_idnf", CHAR, length(15)),
409        column("subsidy_pct", NUMERIC, precision(6), scale(3), defaultValue(0), notnull()),
410        column("email_address", VARCHAR, length(255)),
411        column("web_address", VARCHAR, length(255)),
412        column("record_version", BIGINT, notnull()),
413        column("is_deleted", SMALLINT, notnull())
414        ));
415addForeignKey(new ForeignKey("fk_tblproje_reference_tbllocn2", "project", "country_id", "country", "id"));
416addForeignKey(new ForeignKey("fk_project_reference_region", "project", "region_id", "region", "id"));
417addForeignKey(new ForeignKey("fk_tblproje_reference_tblproj3", "project", "purpose_id", "project_purpose", "id"));
418addForeignKey(new ForeignKey("fk_project_reference_subregio", "project", "subregion_id", "subregion", "id"));
419addForeignKey(new ForeignKey("fk_project_reference_org", "project", "org_id", "org", "id"));
420
421createTable(table("project_classify",
422        column("code", CHAR, length(1), notnull(), primarykey()),
423        column("description", VARCHAR, length(255), notnull()),
424        column("record_version", BIGINT, notnull()),
425        column("is_deleted", SMALLINT, notnull())
426        ));
427
428createTable(table("project_purpose",
429        column("id", INTEGER, notnull(), primarykey()),
430        column("description", VARCHAR, length(255), notnull()),
431        column("project_classify_code", CHAR, length(1), notnull()),
432        column("record_version", BIGINT, notnull()),
433        column("is_deleted", SMALLINT, notnull())
434        ));
435addForeignKey(new ForeignKey("fk_project__reference_project_", "project_purpose", "project_classify_code", "project_classify", "code"));
436
437createTable(table("project_site",
438        column("id", INTEGER, notnull(), primarykey()),
439        column("project_id", INTEGER, notnull()),
440        column("site_id", INTEGER, notnull()),
441        column("site_prefix", CHAR, length(3)),
442        column("record_version", BIGINT, notnull()),
443        column("is_deleted", SMALLINT, notnull())
444        ));
445        addIndex(uniqueIndex("ak_keyprojsite_project_", "project_site", "project_id","site_id"));
446addForeignKey(new ForeignKey("fk_project__reference_site", "project_site", "site_id", "site", "id"));
447addForeignKey(new ForeignKey("fk_tblproje_reference_tblproj6", "project_site", "project_id", "project", "id"));
448
449createTable(table("region",
450        column("id", INTEGER, notnull(), primarykey()),
451        column("name", VARCHAR, length(40), notnull()),
452        column("is_system", SMALLINT, notnull()),
453        column("record_version", BIGINT, notnull()),
454        column("is_deleted", SMALLINT, notnull())
455        ));
456
457createTable(table("request",
458        column("request_site_id", INTEGER, notnull(), primarykey()),
459        column("id", INTEGER, notnull(), primarykey()),
460        column("project_id", INTEGER, notnull()),
461        column("seqno", INTEGER, notnull()),
462        column("type_code", CHAR, length(1), notnull()),
463        column("destination_site_id", INTEGER),
464        column("status_code", CHAR, length(2), notnull()),
465        column("sender_site_id", INTEGER),
466        column("sender_contact_id", INTEGER),
467        column("receiver_site_id", INTEGER),
468        column("receiver_contact_id", INTEGER),
469        column("invoice_site_id", INTEGER),
470        column("invoice_contact_id", INTEGER),
471        column("on_behalf_of", VARCHAR, length(255)),
472        column("cust_order_id", INTEGER),
473        column("description", VARCHAR, length(255)),
474        column("product_id", INTEGER),
475        column("transport_mode_code", CHAR, length(1)),
476        column("dispatch_priority_id", INTEGER, notnull()),
477        column("cargo_ready_date", DATE),
478        column("cargo_prefer_move_date", DATE),
479        column("cargo_must_move_date", DATE),
480        column("followup_date", DATE),
481        column("created_by_logon", VARCHAR, length(40), notnull()),
482        column("created_time", TIMESTAMP, notnull()),
483        column("track_idnf", CHAR, length(10), notnull()),
484        column("record_version", BIGINT, notnull()),
485        column("is_deleted", SMALLINT, notnull())
486        ));
487addForeignKey(new ForeignKey("fk_request_reference_project_", "request", new String[]{"project_id","request_site_id"}, "project_site", new String[]{"project_id","site_id"}));
488addForeignKey(new ForeignKey("fk_tblreque_reference_tblrequ3", "request", "dispatch_priority_id", "dispatch_priority", "id"));
489addForeignKey(new ForeignKey("fk_request_reference_request_", "request", "type_code", "request_type", "code"));
490addForeignKey(new ForeignKey("fk_tblreque_reference_tblrequ2", "request", "status_code", "request_status", "code"));
491addForeignKey(new ForeignKey("fk_request_reference_transpor", "request", "transport_mode_code", "transport_mode", "code"));
492addForeignKey(new ForeignKey("fk_tblreque_reference_tblproj2", "request", new String[]{"sender_site_id","sender_contact_id"}, "contact", new String[]{"owner_site_id","id"}));
493addForeignKey(new ForeignKey("fk_tblreque_reference_tblproj3", "request", new String[]{"receiver_site_id","receiver_contact_id"}, "contact", new String[]{"owner_site_id","id"}));
494addForeignKey(new ForeignKey("fk_request_reference_project_2", "request", new String[]{"project_id","destination_site_id"}, "project_site", new String[]{"project_id","site_id"}));
495addForeignKey(new ForeignKey("fk_request_reference_contact", "request", new String[]{"invoice_site_id","invoice_contact_id"}, "contact", new String[]{"owner_site_id","id"}));
496addForeignKey(new ForeignKey("fk_request_reference_product", "request", "product_id", "product", "id"));
497
498createTable(table("request_history",
499        column("commenting_site_id", INTEGER, notnull(), primarykey()),
500        column("request_site_id", INTEGER, notnull(), primarykey()),
501        column("request_id", INTEGER, notnull(), primarykey()),
502        column("change_time", TIMESTAMP, notnull(), primarykey()),
503        column("chng_type_code", CHAR, length(1), notnull()),
504        column("new_status_code", CHAR, length(2)),
505        column("new_priority_id", INTEGER),
506        column("remarks", VARCHAR, length(255), notnull()),
507        column("created_by_logon", VARCHAR, length(40), notnull()),
508        column("record_version", BIGINT, notnull()),
509        column("is_deleted", SMALLINT, notnull())
510        ));
511addForeignKey(new ForeignKey("fk_tblreque_reference_tblrequ7", "request_history", "new_status_code", "request_status", "code"));
512addForeignKey(new ForeignKey("fk_request__reference_request", "request_history", new String[]{"request_site_id","request_id"}, "request", new String[]{"request_site_id","id"}));
513addForeignKey(new ForeignKey("fk_tblreque_reference_tblrequ6", "request_history", "new_priority_id", "dispatch_priority", "id"));
514addForeignKey(new ForeignKey("fk_request__reference_change_t", "request_history", "chng_type_code", "change_type", "code"));
515
516createTable(table("request_line",
517        column("request_site_id", INTEGER, notnull(), primarykey()),
518        column("id", INTEGER, notnull(), primarykey()),
519        column("request_id", INTEGER, notnull()),
520        column("seqno", INTEGER, notnull()),
521        column("kit_type_code", CHAR, length(1), notnull()),
522        column("parent_line_id", INTEGER),
523        column("ownr_item_desc", VARCHAR, length(255), notnull()),
524        column("ownr_item_idnf", VARCHAR, length(40)),
525        column("line_dispatch_priority_id", INTEGER),
526        column("product_id", INTEGER, notnull()),
527        column("is_cold_storage", SMALLINT, notnull()),
528        column("cold_min_temp", NUMERIC, precision(4), scale(1)),
529        column("cold_max_temp", NUMERIC, precision(4), scale(1)),
530        column("is_dangerous", SMALLINT, notnull()),
531        column("is_regulated", SMALLINT, notnull()),
532        column("sku_id", INTEGER),
533        column("sku_qty", INTEGER),
534        column("ltu_id", INTEGER, notnull()),
535        column("record_version", BIGINT, notnull()),
536        column("is_deleted", SMALLINT, notnull())
537        ));
538addForeignKey(new ForeignKey("fk_request__reference_product_2", "request_line", "ltu_id", "product_ltu", "id"));
539addForeignKey(new ForeignKey("fk_tblreque_reference_tblrequ4", "request_line", new String[]{"request_site_id","request_id"}, "request", new String[]{"request_site_id","id"}));
540addForeignKey(new ForeignKey("fk_request__reference_dispatch", "request_line", "line_dispatch_priority_id", "dispatch_priority", "id"));
541addForeignKey(new ForeignKey("fk_request__reference_product", "request_line", "product_id", "product", "id"));
542addForeignKey(new ForeignKey("fk_request__reference_kit_type", "request_line", "kit_type_code", "kit_type", "code"));
543addForeignKey(new ForeignKey("fk_request__reference_product_", "request_line", "sku_id", "product_sku", "id"));
544
545createTable(table("request_status",
546        column("code", CHAR, length(2), notnull(), primarykey()),
547        column("status_type", CHAR, length(1), notnull()),
548        column("description", VARCHAR, length(255), notnull()),
549        column("name", VARCHAR, length(40), notnull()),
550        column("next_code", CHAR, length(2)),
551        column("seqno", INTEGER),
552        column("record_version", BIGINT, notnull()),
553        column("is_deleted", SMALLINT, notnull())
554        ));
555
556createTable(table("request_type",
557        column("code", CHAR, length(1), notnull(), primarykey()),
558        column("description", VARCHAR, length(255), notnull()),
559        column("record_version", BIGINT, notnull()),
560        column("is_deleted", SMALLINT, notnull())
561        ));
562
563createTable(table("route_available",
564        column("journey_site_id", INTEGER, notnull(), primarykey()),
565        column("route_definition_id", INTEGER, notnull(), primarykey()),
566        column("start_available_date", DATE, notnull(), primarykey()),
567        column("end_available_date", DATE),
568        column("record_version", BIGINT, notnull()),
569        column("is_deleted", SMALLINT, notnull())
570        ));
571
572createTable(table("route_bundle_cost",
573        column("journey_site_id", INTEGER, notnull(), primarykey()),
574        column("route_cost_id", INTEGER, notnull(), primarykey()),
575        column("bundle_type_id", INTEGER, notnull(), primarykey()),
576        column("cost_per_bundle", NUMERIC, precision(10), scale(4), notnull())
577        ));
578addForeignKey(new ForeignKey("fk_route_bu_reference_route_co", "route_bundle_cost", new String[]{"journey_site_id","route_cost_id"}, "route_cost", new String[]{"journey_site_id","id"}));
579addForeignKey(new ForeignKey("fk_route_bu_reference_bundle_t", "route_bundle_cost", "bundle_type_id", "bundle_type", "id"));
580
581createTable(table("route_cost",
582        column("journey_site_id", INTEGER, notnull(), primarykey()),
583        column("id", INTEGER, notnull(), primarykey()),
584        column("route_definition_id", INTEGER, notnull()),
585        column("transport_special_id", INTEGER, notnull()),
586        column("take_effect_date", DATE, notnull()),
587        column("cost_currency_id", INTEGER, notnull()),
588        column("cost_per_journey_by_weight", NUMERIC, precision(10), scale(4), notnull()),
589        column("cost_per_journey_by_volume", NUMERIC, precision(10), scale(4), notnull()),
590        column("cost_per_weight", NUMERIC, precision(10), scale(4), notnull()),
591        column("cost_per_volume", NUMERIC, precision(10), scale(4), notnull()),
592        column("is_cost_per_bundle", SMALLINT, notnull()),
593        column("record_version", BIGINT, notnull()),
594        column("is_deleted", SMALLINT, notnull())
595        ));
596addForeignKey(new ForeignKey("fk_tblroute_reference_tblrout3", "route_cost", new String[]{"journey_site_id","route_definition_id"}, "route_definition", new String[]{"journey_site_id","id"}));
597addForeignKey(new ForeignKey("fk_route_co_reference_transpor", "route_cost", new String[]{"journey_site_id","transport_special_id"}, "transport_special", new String[]{"journey_site_id","id"}));
598addForeignKey(new ForeignKey("fk_route_co_reference_currency", "route_cost", "cost_currency_id", "currency", "id"));
599
600createTable(table("route_definition",
601        column("journey_site_id", INTEGER, notnull(), primarykey()),
602        column("id", INTEGER, notnull(), primarykey()),
603        column("destination_site_id", INTEGER, notnull()),
604        column("vehicle_category_id", INTEGER, notnull()),
605        column("route_distance_km", NUMERIC, precision(9), scale(3)),
606        column("route_distance_nm", NUMERIC, precision(9), scale(3)),
607        column("record_version", BIGINT, notnull()),
608        column("is_deleted", SMALLINT, notnull())
609        ));
610addForeignKey(new ForeignKey("fk_route_de_reference_site", "route_definition", "destination_site_id", "site", "id"));
611addForeignKey(new ForeignKey("fk_tblroute_reference_tblsite2", "route_definition", "journey_site_id", "site", "id"));
612addForeignKey(new ForeignKey("fk_route_de_reference_vehicle_", "route_definition", "vehicle_category_id", "vehicle_category", "id"));
613
614createTable(table("shipment",
615        column("journey_site_id", INTEGER, notnull(), primarykey()),
616        column("id", INTEGER, notnull(), primarykey()),
617        column("journey_id", INTEGER, notnull()),
618        column("shipment_docm_idnf", VARCHAR, length(40)),
619        column("pack_list_date", DATE),
620        column("pack_list_numb", INTEGER),
621        column("record_version", BIGINT, notnull()),
622        column("is_deleted", SMALLINT, notnull())
623        ));
624addForeignKey(new ForeignKey("fk_shipment_reference_journey", "shipment", new String[]{"journey_site_id","journey_id"}, "journey", new String[]{"journey_site_id","id"}));
625
626createTable(table("site",
627        column("id", INTEGER, notnull(), primarykey()),
628        column("country_id", INTEGER, notnull()),
629        column("site_name", VARCHAR, length(40), notnull()),
630        column("latitude", NUMERIC, precision(8), scale(5)),
631        column("longitude", NUMERIC, precision(8), scale(5)),
632        column("rptg_site_id", INTEGER),
633        column("org_id", INTEGER),
634        column("rptg_ofcr_name", VARCHAR, length(255)),
635        column("site_addr1", VARCHAR, length(255), notnull()),
636        column("site_addr2", VARCHAR, length(255)),
637        column("site_email", VARCHAR, length(255)),
638        column("site_phone", VARCHAR, length(255)),
639        column("site_fax", VARCHAR, length(255)),
640        column("radio_call_sign", VARCHAR, length(255)),
641        column("site_cap_area", NUMERIC, precision(9), scale(3)),
642        column("site_cap_volume", NUMERIC, precision(9), scale(3)),
643        column("site_cap_weight", NUMERIC, precision(9), scale(3)),
644        column("remarks", VARCHAR, length(255)),
645        column("site_dstr_type_id", INTEGER, notnull()),
646        column("site_bldg_type_id", INTEGER, notnull()),
647        column("site_bldg_qty", INTEGER, notnull()),
648        column("site_port_type_id", INTEGER, notnull()),
649        column("site_port_name", VARCHAR, length(255)),
650        column("record_version", BIGINT, notnull()),
651        column("is_deleted", SMALLINT, notnull())
652        ));
653addForeignKey(new ForeignKey("fk_site_reference_building", "site", "site_bldg_type_id", "building_type", "id"));
654addForeignKey(new ForeignKey("fk_site_reference_country", "site", "country_id", "country", "id"));
655addForeignKey(new ForeignKey("fk_site_reference_port_typ", "site", "site_port_type_id", "port_type", "id"));
656addForeignKey(new ForeignKey("fk_site_reference_distribu", "site", "site_dstr_type_id", "distribution_type", "id"));
657addForeignKey(new ForeignKey("fk_site_reference_org", "site", "org_id", "org", "id"));
658
659createTable(table("site_track_no",
660        column("site_id", INTEGER, notnull(), primarykey()),
661        column("is_active", BOOLEAN),
662        column("site_track_idnf", CHAR, length(18), notnull()),
663        column("site_track_date", DATE, notnull()),
664        column("site_track_cptr_name", VARCHAR, length(40), notnull())
665        ));
666addForeignKey(new ForeignKey("fk_site_tra_reference_site", "site_track_no", "site_id", "site", "id"));
667
668createTable(table("subregion",
669        column("id", INTEGER, notnull(), primarykey()),
670        column("name", VARCHAR, length(40), notnull()),
671        column("region_id", INTEGER, notnull()),
672        column("record_version", BIGINT, notnull()),
673        column("is_deleted", SMALLINT, notnull())
674        ));
675addForeignKey(new ForeignKey("fk_subregio_reference_region", "subregion", "region_id", "region", "id"));
676
677createTable(table("sync_log",
678        column("id", INTEGER, notnull(), primarykey()),
679        column("direction", SMALLINT, notnull()),
680        column("start_time", TIMESTAMP, notnull()),
681        column("finish_time", TIMESTAMP, notnull()),
682        column("num_rows", INTEGER, notnull()),
683        column("is_success", SMALLINT),
684        column("message", VARCHAR, length(255))
685        ));
686
687createTable(table("sync_table_version",
688        column("table_name", VARCHAR, length(40), notnull(), primarykey()),
689        column("central_version", BIGINT, notnull()),
690        column("uploaded_version", BIGINT, notnull()),
691        column("last_sync_time", TIMESTAMP, notnull())
692        ));
693
694createTable(table("sync_version",
695        column("site_id", INTEGER),
696        column("site_track_idnf", CHAR, length(18), notnull()),
697        column("local_version", BIGINT, notnull()),
698        column("last_upload_time", TIMESTAMP),
699        column("is_active", SMALLINT
700)
701        ));
702
703createTable(table("system_authority",
704        column("user_auth_code", CHAR, length(3), notnull(), primarykey()),
705        column("record_version", BIGINT, notnull()),
706        column("is_deleted", SMALLINT, notnull())
707        ));
708
709createTable(table("table_control",
710        column("table_name", VARCHAR, length(40), notnull(), primarykey()),
711        column("description", VARCHAR, length(255), notnull()),
712        column("record_version", BIGINT, notnull()),
713        column("is_deleted", SMALLINT, notnull())
714        ));
715
716createTable(table("translations",
717        column("id", INTEGER, notnull(), primarykey()),
718        column("table_name", VARCHAR, length(40), notnull()),
719        column("language_idnf", CHAR, length(2), notnull()),
720        column("lookup_id", INTEGER),
721        column("lookup_code", CHAR, length(3)),
722        column("description", VARCHAR, length(255), notnull()),
723        column("record_version", BIGINT, notnull()),
724        column("is_deleted", SMALLINT, notnull())
725        ));
726addForeignKey(new ForeignKey("fk_translat_reference_table_co", "translations", "table_name", "table_control", "table_name"));
727addForeignKey(new ForeignKey("fk_translat_reference_language", "translations", "language_idnf", "languages", "iso2_idnf"));
728
729createTable(table("transport_mode",
730        column("code", CHAR, length(1), notnull(), primarykey()),
731        column("description", VARCHAR, length(255), notnull()),
732        column("record_version", BIGINT, notnull()),
733        column("is_deleted", SMALLINT, notnull())
734        ));
735
736createTable(table("transport_special",
737        column("journey_site_id", INTEGER, notnull(), primarykey()),
738        column("id", INTEGER, notnull(), primarykey()),
739        column("description", VARCHAR, length(255), notnull()),
740        column("record_version", BIGINT, notnull()),
741        column("is_deleted", SMALLINT, notnull())
742        ));
743
744createTable(table("user_authority",
745        column("id", INTEGER, notnull(), primarykey()),
746        column("user_id", INTEGER, notnull()),
747        column("user_auth_code", CHAR, length(3), notnull()),
748        column("project_id", INTEGER),
749        column("site_id", INTEGER),
750        column("expiry_date", DATE),
751        column("record_version", BIGINT, notnull()),
752        column("is_deleted", SMALLINT, notnull())
753        ));
754addForeignKey(new ForeignKey("fk_user_aut_reference_system_a", "user_authority", "user_auth_code", "system_authority", "user_auth_code"));
755addForeignKey(new ForeignKey("fk_user_aut_reference_project", "user_authority", "project_id", "project", "id"));
756addForeignKey(new ForeignKey("fk_user_aut_reference_project_", "user_authority", new String[]{"project_id","site_id"}, "project_site", new String[]{"project_id","site_id"}));
757addForeignKey(new ForeignKey("fk_user_aut_reference_users", "user_authority", "user_id", "users", "id"));
758
759createTable(table("user_parameters",
760        column("user_id", INTEGER, notnull(), primarykey()),
761        column("parameter_name", VARCHAR, length(40), notnull(), primarykey()),
762        column("parameter_value", VARCHAR, length(40)),
763        column("record_version", BIGINT, notnull()),
764        column("is_deleted", SMALLINT, notnull())
765        ));
766addForeignKey(new ForeignKey("fk_user_par_reference_users", "user_parameters", "user_id", "users", "id"));
767
768createTable(table("users",
769        column("id", INTEGER, notnull(), primarykey()),
770        column("org_id", INTEGER, notnull()),
771        column("email", VARCHAR, length(255), notnull()),
772        column("password", VARCHAR, length(16), notnull()),
773        column("name", VARCHAR, length(255), notnull()),
774        column("expiry_date", DATE),
775        column("record_version", BIGINT, notnull()),
776        column("is_deleted", SMALLINT, notnull())
777        ));
778addForeignKey(new ForeignKey("fk_tbluser_reference_tblorg2", "users", "org_id", "org", "id"));
779
780createTable(table("vehicle",
781        column("journey_site_id", INTEGER, notnull(), primarykey()),
782        column("id", INTEGER, notnull(), primarykey()),
783        column("model_id", INTEGER, notnull()),
784        column("rego_idnf", VARCHAR, length(255), notnull()),
785        column("description", VARCHAR, length(255)),
786        column("record_version", BIGINT, notnull()),
787        column("is_deleted", SMALLINT, notnull())
788        ));
789addForeignKey(new ForeignKey("fk_vehicle_reference_vehicle_2", "vehicle", "model_id", "vehicle_model", "id"));
790
791createTable(table("vehicle_category",
792        column("id", INTEGER, notnull(), primarykey()),
793        column("description", VARCHAR, length(255), notnull()),
794        column("transport_mode_code", CHAR, length(1), notnull()),
795        column("record_version", BIGINT, notnull()),
796        column("is_deleted", SMALLINT, notnull())
797        ));
798addForeignKey(new ForeignKey("fk_vehicle__reference_transpor", "vehicle_category", "transport_mode_code", "transport_mode", "code"));
799
800createTable(table("vehicle_model",
801        column("id", INTEGER, notnull(), primarykey()),
802        column("description", VARCHAR, length(255), notnull()),
803        column("vehicle_category_id", INTEGER, notnull()),
804        column("max_weight", NUMERIC, precision(9), scale(3), notnull()),
805        column("max_volume", NUMERIC, precision(9), scale(3), notnull()),
806        column("record_version", BIGINT, notnull()),
807        column("is_deleted", SMALLINT, notnull())
808        ));
809addForeignKey(new ForeignKey("fk_tblvehic_reference_tblvehi2", "vehicle_model", "vehicle_category_id", "vehicle_category", "id"));
Note: See TracBrowser for help on using the browser.