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