apiSyncer/mySQLref.sql at master · CharlesBClarke/apiSyncer · 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
127
CREATETABLEIFNOTEXISTSobjects(
    idINTAUTO_INCREMENTPRIMARYKEY,
    serialVARCHAR(64)NOTNULLUNIQUE,
    nameVARCHAR(255)NOTNULL
);
CREATETABLEIFNOTEXISTSrelationships(
parent_idINT,
child_idINT,
PRIMARYKEY(
    parent_id,
    child_id
),
FOREIGNKEY(
    parent_id
)REFERENCESobjects(id)
    ON DELETECASCADEFOREIGNKEY(
        child_id
    )REFERENCESobjects(id)
        ON DELETECASCADE
);
-- Add root object
INSERT INTO objects(
serial,
name
)
VALUES(
'ROOT001',
'Root Item'
);
-- Insert new objects
INSERT INTO objects(
serial,
name
)
VALUES(
'ITEM002',
'Item A'
),
(
'ITEM003',
'Item B'
),
(
'ITEM004',
'Item C'
),
(
'ITEM005',
'Item D'
);
-- Establish parent-child relationships
INSERT INTO relationships(
parent_id,
child_id
)
VALUES(
(
SELECT
id
FROM
objects
WHERE
serial = 'ROOT001'
),
(
SELECT
id
FROM
objects
WHERE
serial = 'ITEM002'
)
),
(
(
SELECT
id
FROM
objects
WHERE
serial = 'ROOT001'
),
(
SELECT
id
FROM
objects
WHERE
serial = 'ITEM003'
)
),
(
(
SELECT
id
FROM
objects
WHERE
serial = 'ITEM002'
),
(
SELECT
id
FROM
objects
WHERE
serial = 'ITEM004'
)
),
(
(
SELECT
id
FROM
objects
WHERE
serial = 'ITEM003'
),
(
SELECT
id
FROM
objects
WHERE
serial = 'ITEM005'
)
);