File size: 4,762 Bytes
1a25764
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
dcb1716
 
 
 
 
1a25764
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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
128
129
130
131
132
133
134
135
-- ==============================================================================
--  Database Inspection — json-api-server
--  Usage:  sqlite3 storage/data.db < manual/inspect-queries.sql
-- ==============================================================================

--  1. OVERVIEW
--  ..............................................................................
SELECT 'users'    AS table_name, COUNT(*) AS row_count FROM users
UNION ALL SELECT 'posts',    COUNT(*) FROM posts
UNION ALL SELECT 'comments', COUNT(*) FROM comments
UNION ALL SELECT 'albums',   COUNT(*) FROM albums
UNION ALL SELECT 'photos',   COUNT(*) FROM photos
UNION ALL SELECT 'todos',    COUNT(*) FROM todos
ORDER BY row_count DESC;

SELECT m.name AS table_name, COUNT(*) AS column_count
FROM sqlite_master m
JOIN pragma_table_info(m.name) ON 1=1
WHERE m.type = 'table'
GROUP BY m.name
ORDER BY m.name;

--  2. USERS
--  ..............................................................................
SELECT id, name, username, email, phone FROM users ORDER BY id;

SELECT id, name,
       substr(address, 1, 50) AS address_preview,
       substr(company, 1, 50) AS company_preview
FROM users ORDER BY id;

--  3. POSTS
--  ..............................................................................
SELECT p.id, p.userId, u.name AS author, substr(p.title, 1, 50) AS title
FROM posts p
JOIN users u ON u.id = p.userId
ORDER BY p.id;

SELECT u.id, u.name, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.userId = u.id
GROUP BY u.id
ORDER BY post_count DESC;

--  4. COMMENTS
--  ..............................................................................
SELECT c.id, c.postId, c.name, c.email, substr(c.body, 1, 40) AS body_preview
FROM comments c
ORDER BY c.id
LIMIT 10;

SELECT postId, COUNT(*) AS comment_count
FROM comments
GROUP BY postId
ORDER BY postId;

--  5. ALBUMS & PHOTOS
--  ..............................................................................
SELECT a.id, a.userId, u.name AS owner, a.title
FROM albums a
JOIN users u ON u.id = a.userId
ORDER BY a.id;

SELECT a.id, a.title, COUNT(p.id) AS photo_count
FROM albums a
LEFT JOIN photos p ON p.albumId = a.id
GROUP BY a.id
ORDER BY a.id;

SELECT id, albumId, substr(title, 1, 40) AS title, url
FROM photos
ORDER BY id
LIMIT 10;

--  6. TODOS
--  ..............................................................................
SELECT t.id, t.userId, u.name AS user, substr(t.title, 1, 40) AS title, t.completed
FROM todos t
JOIN users u ON u.id = t.userId
ORDER BY t.id;

SELECT u.name,
       COUNT(t.id) AS total,
       SUM(CASE WHEN t.completed THEN 1 ELSE 0 END) AS done,
       SUM(CASE WHEN NOT t.completed THEN 1 ELSE 0 END) AS pending
FROM users u
LEFT JOIN todos t ON t.userId = u.id
GROUP BY u.id
ORDER BY u.id;

--  7. RELATIONSHIPS & INTEGRITY
--  ..............................................................................
SELECT u.id, u.name,
       COUNT(DISTINCT p.id) AS posts,
       COUNT(DISTINCT c.id) AS comments_on_posts,
       COUNT(DISTINCT a.id) AS albums,
       COUNT(DISTINCT t.id) AS todos
FROM users u
LEFT JOIN posts p  ON p.userId = u.id
LEFT JOIN comments c ON c.postId IN (SELECT id FROM posts WHERE userId = u.id)
LEFT JOIN albums a  ON a.userId = u.id
LEFT JOIN todos t   ON t.userId = u.id
GROUP BY u.id
ORDER BY u.id;

SELECT 'posts' AS tbl, 'userId' AS col, p.userId AS val
FROM posts p LEFT JOIN users u ON u.id = p.userId WHERE u.id IS NULL
UNION ALL
SELECT 'comments', 'postId', c.postId
FROM comments c LEFT JOIN posts p ON p.id = c.postId WHERE p.id IS NULL
UNION ALL
SELECT 'albums', 'userId', a.userId
FROM albums a LEFT JOIN users u ON u.id = a.userId WHERE u.id IS NULL;

--  8. SETTINGS
--  ..............................................................................
SELECT id, key, value, description, updated_at FROM settings ORDER BY key;

--  9. STATISTICS
--  ..............................................................................
SELECT 'posts' AS type, AVG(LENGTH(body)) AS avg_len, MIN(LENGTH(body)) AS min_len, MAX(LENGTH(body)) AS max_len FROM posts
UNION ALL
SELECT 'comments', AVG(LENGTH(body)), MIN(LENGTH(body)), MAX(LENGTH(body)) FROM comments;

SELECT CASE WHEN completed THEN 'done' ELSE 'pending' END AS status,
       COUNT(*) AS count,
       ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM todos), 1) AS pct
FROM todos GROUP BY completed;

SELECT 'user_activity' AS metric, MAX(pc) || ' posts' AS value FROM (SELECT userId, COUNT(*) AS pc FROM posts GROUP BY userId)
UNION ALL
SELECT 'user_activity', MAX(ac) || ' albums' FROM (SELECT userId, COUNT(*) AS ac FROM albums GROUP BY userId)
UNION ALL
SELECT 'user_activity', MAX(tc) || ' todos'  FROM (SELECT userId, COUNT(*) AS tc FROM todos GROUP BY userId);