RowIDetective/RowIDetective.sql at main · MetadataForensics/RowIDetective · 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
WITH LastROWID AS (
	SELECT seq AS last_rowid
	FROM sqlite_sequence
	WHERE sqlite_sequence.name = 'message'
)
SELECT * FROM (
	SELECT * FROM (
		SELECT
		CASE
			WHEN length(DATE) = 18
			THEN LAG(DATETIME(DATE/1000000000 + 978307200, 'UNIXEPOCH'),1) OVER (ORDER BY ROWID)
			WHEN length(DATE) = 9
			THEN LAG(DATETIME(DATE + 978307200, 'UNIXEPOCH'),1) OVER (ORDER BY ROWID)
			END AS "Beginning Timestamp",
		CASE
			WHEN length(DATE) = 18
			THEN DATETIME(DATE/1000000000 + 978307200, 'UNIXEPOCH')
			WHEN length(DATE) = 9
			THEN DATETIME(DATE + 978307200, 'UNIXEPOCH')
			END  AS "Ending Timestamp",
		LAG (guid,1) OVER (ORDER BY ROWID) AS "Previous guid",
		guid AS "guid",
		LAG (ROWID,1) OVER (ORDER BY ROWID) AS "Previous ROWID",
		ROWID AS "ROWID",
		(ROWID - (LAG (ROWID,1) OVER (ORDER BY ROWID)) - 1) AS "Number of Missing Rows"
		FROM message) list
	WHERE ROWID - "Previous ROWID" > 1

	UNION ALL

	SELECT
	CASE
		WHEN message.ROWID != (SELECT last_rowid FROM LastROWID)
		THEN MAX(CASE
					WHEN length(DATE) = 18 THEN DATETIME(DATE/1000000000 + 978307200, 'UNIXEPOCH')
					WHEN length(DATE) = 9  THEN DATETIME(DATE + 978307200, 'UNIXEPOCH')
				END)
		END AS "Beginning Timestamp",
	CASE
		WHEN message.ROWID != (SELECT last_rowid FROM LastROWID)
		THEN "Time of Extraction"
		END AS "Ending Timestamp",
	CASE
		WHEN message.ROWID != (SELECT last_rowid FROM LastROWID)
		THEN guid
		END AS "Previous guid",
	CASE
		WHEN message.ROWID != (SELECT last_rowid FROM LastROWID)
		THEN "Unknown"
		END AS "guid",
	CASE
		WHEN message.ROWID != (SELECT last_rowid FROM LastROWID)
		THEN MAX(ROWID)
		END AS "Previous ROWID",
	CASE
		WHEN message.ROWID != (SELECT last_rowid FROM LastROWID)
		THEN (SELECT last_rowid FROM LastROWID)
		END AS "ROWID",
	CASE
		WHEN message.ROWID != (SELECT last_rowid FROM LastROWID)
		THEN ((SELECT last_rowid FROM LastROWID) - message.ROWID)
		END AS "Number of Missing Rows"
	FROM message)
WHERE "ROWID" IS NOT NULL;