From 7fa8c604abb8d1301d37cd7c8c79a0b3b40ff3e5 Mon Sep 17 00:00:00 2001
From: Matt Joiner <anacrolix@gmail.com>
Date: Fri, 23 Oct 2020 11:03:42 +1100
Subject: [PATCH] sqlite storage: Working recursive CTE for excess data

---
 storage/sqlite/sql | 11 +++++++++++
 1 file changed, 11 insertions(+)
 create mode 100644 storage/sqlite/sql

diff --git a/storage/sqlite/sql b/storage/sqlite/sql
new file mode 100644
index 00000000..a6f4bdce
--- /dev/null
+++ b/storage/sqlite/sql
@@ -0,0 +1,11 @@
+with recursive excess(
+	usage_with,
+	last_used,
+	blob_rowid,
+	data_length
+) as (
+	select * from (select (select sum(length(data)) from blob), last_used, rowid, length(data) from blob order by last_used, rowid limit 1)
+	union all
+	select usage_with-data_length, blob.last_used, blob.rowid, length(data) from excess join blob
+		on blob.rowid=(select rowid from blob where (last_used, rowid) > (excess.last_used, blob_rowid))
+) select * from excess limit 10;
-- 
2.48.1