-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathFKeys Child Indices.html
83 lines (72 loc) · 8.36 KB
/
FKeys Child Indices.html
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
<html>
<head>
<link rel="stylesheet" href="code_style.css">
<style>
div.code {background-color: #F0F0F0}
</style>
</head>
<body>
<div class="code">
<span class="kwd">CREATE VIEW </span><span class="tab">service_meta_foreign_keys_child_indices</span> <span class="kwl">AS</span><br />
<span class="kwd">WITH</span><br />
<div style="background-color: #F0E0F0">
<span class="tab">tables</span> <span class="kwl">AS (</span><br />
<span class="kwd">SELECT </span><span class="fld">tbl_name <span class="kwl">AS</span> table_name, sql</span><br />
<span class="kwd">FROM </span><span class="tab">sqlite_master</span><br />
<span class="kwd">WHERE </span><span class="fld">type</span> = <span class="lit">'table'</span><br />
<span class="kwl"> AND </span><span class="fld">name</span> <span class="kwl">NOT LIKE</span> <span class="lit">'sqlite_%'</span><br />
<span class="kwl">),</span><br />
</div>
<div style="background-color: #F0F0E0">
<span class="tab">index_list</span> <span class="kwl">AS (</span><br />
<span class="kwd">SELECT </span><span class="fld">table_name, name <span class="kwl">AS</span> index_name, "unique", origin, partial</span><br />
<span class="kwd">FROM </span><span class="tab">tables</span> <span class="kwl">AS</span> <span class="tab">t, </span><span class="prc">pragma_index_list</span>(<span class="tab">t</span><b>.</b><span class="fld">table_name</span>)<br />
<span class="kwl">),</span><br />
<span class="tab">index_columns</span> <span class="kwl">AS (</span><br />
<span class="kwd">SELECT </span><span class="fld"> il.*, name <span class="kwl">AS</span> col_name, cid, seqno, "desc", coll, "key"</span><br />
<span class="kwd">FROM </span><span class="tab">index_list</span> <span class="kwl">AS</span> <span class="tab">il, </span><span class="prc">pragma_index_xinfo</span>(<span class="tab">il</span><b>.</b><span class="fld">index_name</span>)<br />
<span class="kwd">ORDER BY </span><span class="fld">index_name, seqno</span><br />
<span class="kwl">),</span><br />
<span class="tab">noddl_indices</span> <span class="kwl">AS (</span><br />
<span class="kwd">SELECT </span><span class="fld">table_name, index_name,</span><br />
<span class="fld"> <span class="prc">json_group_array</span>(col_name) <span class="kwl">AS</span> col_names,</span><br />
<span class="fld"> "unique", origin, partial</span><br />
<span class="kwd">FROM </span><span class="tab">index_columns</span><br />
<span class="kwd">GROUP BY </span><span class="fld">index_name</span><br />
<span class="kwl">),</span><br />
<span class="tab">indices</span> <span class="kwl">AS (</span><br />
<span class="kwd">SELECT </span><span class="tab">bi</span><span class="fld">.*, sm.sql</span><br />
<span class="kwd">FROM </span><span class="tab">noddl_indices <span class="kwl">AS</span> bi, sqlite_master <span class="kwl">AS</span> sm</span><br />
<span class="kwd">WHERE </span><span class="fld">type</span> = <span class="lit">'index' </span><span class="kwl">AND </span><span class="fld">index_name = name</span><br />
<span class="kwd">ORDER BY </span><span class="fld">table_name, index_name</span><br />
<span class="kwl">),</span><br />
</div>
<div style="background-color: #E0F0F0">
<span class="tab">fkey_columns</span> <span class="kwl">AS (</span><br />
<span class="kwd">SELECT </span><span class="fld">table_name <span class="kwl">AS</span> src_table, "from" <span class="kwl">AS</span> src_col,</span><br />
<span class="fld"> "table" <span class="kwl">AS</span> dst_table, "to" <span class="kwl">AS</span> dst_col,</span><br />
<span class="fld"> on_update, on_delete, id <span class="kwl">AS</span> fk_id, seq <span class="kwl">AS</span> fk_seq</span><br />
<span class="kwd">FROM </span><span class="tab">tables</span> <span class="kwl">AS</span> <span class="tab">t,</span><br />
<span class="prc"> pragma_foreign_key_list</span> (<span class="tab">t</span><b>.</b><span class="fld">table_name</span>)<br />
<span class="kwd">ORDER BY </span><span class="fld">src_table, fk_id, fk_seq</span><br />
<span class="kwl">),</span><br />
<span class="tab">foreign_keys</span> <span class="kwl">AS (</span><br />
<span class="kwd">SELECT </span><span class="fld">src_table, json_group_array(src_col) <span class="kwl">AS</span> src_cols,</span><br />
<span class="fld"> dst_table, json_group_array(dst_col) <span class="kwl">AS</span> dst_cols,</span><br />
<span class="fld"> on_update, on_delete, fk_id</span><br />
<span class="kwd">FROM </span><span class="tab">fkey_columns</span><br />
<span class="kwd">GROUP BY </span><span class="fld">src_table, fk_id</span><br />
<span class="kwd">ORDER BY </span><span class="fld">src_table, dst_table</span><br />
<span class="kwl">),</span><br />
</div>
<span class="tab">foreign_key_child_indices</span> <span class="kwl">AS (</span><br />
<span class="kwd">SELECT </span><span class="fld"><span class="tab">fks</span>.*, <span class="tab">i</span>.index_name, <span class="tab">i</span>.col_names</span><br />
<span class="kwd">FROM </span><span class="tab">foreign_keys <span class="kwl">AS</span> fks</span><br />
<span class="kwd">LEFT JOIN </span><span class="tab">indices <span class="kwl">AS</span> i</span><br />
<span class="kwd">ON </span><span class="fld"><span class="tab">i</span>.table_name = <span class="tab">fks</span>.src_table </span><span class="kwl">AND</span><br />
<span class="tab">i</span><span class="fld">.col_names</span><span class="prc"> like rtrim(</span><span class="tab">fks</span><span class="fld">.src_cols, </span><span class="lit">']'</span><span class="prc">) || </span><span class="lit">'%'</span><br />
<span class="kwl">)</span><br />
<span class="kwd">SELECT <span class="fld">*</span> FROM </span><span class="tab">foreign_key_child_indices;</span><br />
</div>
</body>
</html>