-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathForeign Keys.html
45 lines (39 loc) · 3.76 KB
/
Foreign Keys.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
<html>
<head>
<link rel="stylesheet" href="code_style.css">
<style>
div.code {background-color: #E0F0F0}
</style>
</head>
<body>
<div class="code">
<span class="kwd">CREATE VIEW </span><span class="tab">service_meta_foreign_keys</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>
<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 />
<span class="kwd">SELECT <span class="fld">*</span> FROM </span><span class="tab">foreign_keys;</span><br />
</div>
</body>
</html>