xml2db/tests/sample_models/orders/orders_ddl_mysql_version0.sql at 256dbcd867deba21d08de5280720ef1fdf2f32f0 · cre-dev/xml2db · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126

CREATE TABLE orderperson (
	pk_orderperson INTEGER NOT NULL AUTO_INCREMENT,
	name_attr VARCHAR(255),
	name VARCHAR(255),
	address VARCHAR(255),
	city VARCHAR(255),
	`zip_codingSystem` VARCHAR(255),
	zip_state VARCHAR(255),
	zip_value VARCHAR(255),
	country VARCHAR(255),
	`phoneNumber` VARCHAR(4000),
	`companyId_type` VARCHAR(3),
	`companyId_value` VARCHAR(255),
	coordinates VARCHAR(255),
	record_hash BINARY(20),
	CONSTRAINT cx_pk_orderperson PRIMARY KEY (pk_orderperson),
	CONSTRAINT orderperson_xml2db_record_hash UNIQUE (record_hash)
)


CREATE TABLE intfeature (
	pk_intfeature INTEGER NOT NULL AUTO_INCREMENT,
	id VARCHAR(255),
	value INTEGER,
	record_hash BINARY(20),
	CONSTRAINT cx_pk_intfeature PRIMARY KEY (pk_intfeature),
	CONSTRAINT intfeature_xml2db_record_hash UNIQUE (record_hash)
)


CREATE TABLE stringfeature (
	pk_stringfeature INTEGER NOT NULL AUTO_INCREMENT,
	id VARCHAR(255),
	value VARCHAR(255),
	record_hash BINARY(20),
	CONSTRAINT cx_pk_stringfeature PRIMARY KEY (pk_stringfeature),
	CONSTRAINT stringfeature_xml2db_record_hash UNIQUE (record_hash)
)


CREATE TABLE item (
	pk_item INTEGER NOT NULL AUTO_INCREMENT,
	product_name VARCHAR(255),
	product_version VARCHAR(255),
	note VARCHAR(255),
	quantity INTEGER,
	price DOUBLE,
	currency VARCHAR(3),
	record_hash BINARY(20),
	CONSTRAINT cx_pk_item PRIMARY KEY (pk_item),
	CONSTRAINT item_xml2db_record_hash UNIQUE (record_hash)
)


CREATE TABLE item_product_features_intfeature (
	fk_item INTEGER NOT NULL,
	fk_intfeature INTEGER NOT NULL,
	FOREIGN KEY(fk_item) REFERENCES item (pk_item),
	FOREIGN KEY(fk_intfeature) REFERENCES intfeature (pk_intfeature)
)


CREATE TABLE item_product_features_stringfeature (
	fk_item INTEGER NOT NULL,
	fk_stringfeature INTEGER NOT NULL,
	FOREIGN KEY(fk_item) REFERENCES item (pk_item),
	FOREIGN KEY(fk_stringfeature) REFERENCES stringfeature (pk_stringfeature)
)


CREATE TABLE shiporder (
	pk_shiporder INTEGER NOT NULL AUTO_INCREMENT,
	orderid VARCHAR(255),
	processed_at DATETIME,
	fk_orderperson INTEGER,
	shipto_fk_orderperson INTEGER,
	record_hash BINARY(20),
	CONSTRAINT cx_pk_shiporder PRIMARY KEY (pk_shiporder),
	CONSTRAINT shiporder_xml2db_record_hash UNIQUE (record_hash),
	FOREIGN KEY(fk_orderperson) REFERENCES orderperson (pk_orderperson),
	FOREIGN KEY(shipto_fk_orderperson) REFERENCES orderperson (pk_orderperson)
)


CREATE TABLE shiporder_item (
	fk_shiporder INTEGER NOT NULL,
	fk_item INTEGER NOT NULL,
	FOREIGN KEY(fk_shiporder) REFERENCES shiporder (pk_shiporder),
	FOREIGN KEY(fk_item) REFERENCES item (pk_item)
)


CREATE TABLE orders (
	pk_orders INTEGER NOT NULL AUTO_INCREMENT,
	batch_id VARCHAR(255),
	version INTEGER,
	input_file_path VARCHAR(256),
	record_hash BINARY(20),
	CONSTRAINT cx_pk_orders PRIMARY KEY (pk_orders),
	CONSTRAINT orders_xml2db_record_hash UNIQUE (record_hash)
)


CREATE TABLE orders_shiporder (
	fk_orders INTEGER NOT NULL,
	fk_shiporder INTEGER NOT NULL,
	FOREIGN KEY(fk_orders) REFERENCES orders (pk_orders),
	FOREIGN KEY(fk_shiporder) REFERENCES shiporder (pk_shiporder)
)

CREATE INDEX ix_item_product_features_intfeature_fk_intfeature ON item_product_features_intfeature (fk_intfeature)

CREATE INDEX ix_item_product_features_intfeature_fk_item ON item_product_features_intfeature (fk_item)

CREATE INDEX ix_item_product_features_stringfeature_fk_item ON item_product_features_stringfeature (fk_item)

CREATE INDEX ix_item_product_features_stringfeature_fk_stringfeature ON item_product_features_stringfeature (fk_stringfeature)

CREATE INDEX ix_shiporder_item_fk_item ON shiporder_item (fk_item)

CREATE INDEX ix_shiporder_item_fk_shiporder ON shiporder_item (fk_shiporder)

CREATE INDEX ix_orders_shiporder_fk_orders ON orders_shiporder (fk_orders)

CREATE INDEX ix_orders_shiporder_fk_shiporder ON orders_shiporder (fk_shiporder)