Record when a narinfo is added

As this will enable removing nars by date added.

1 files changed, 26 insertions, 4 deletions

diff --git a/nar-herder/database.scm b/nar-herder/database.scm
index b922cad..97d9fa5 100644
--- a/nar-herder/database.scm
+++ b/nar-herder/database.scm
@@ -94,7 +94,8 @@ CREATE TABLE narinfos (
   nar_size INTEGER NOT NULL,
   deriver TEXT,
   system TEXT,
-  contents NOT NULL
+  contents NOT NULL,
+  added_at TEXT,
 );
 
 CREATE UNIQUE INDEX narinfos_store_hash ON narinfos (substr(store_path, 12, 32));
@@ -164,6 +165,21 @@ SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = :name")))
 
       result)))
 
+(define (column-exists? db table-name column-name)
+  (let ((statement
+         (sqlite-prepare
+          db
+          (simple-format #f "PRAGMA table_info(~A);" table-name))))
+
+    (let ((columns
+           (sqlite-map
+            (lambda (row)
+              (vector-ref row 1))
+            statement)))
+      (sqlite-finalize statement)
+
+      (member column-name columns))))
+
 (define (update-schema db)
   (unless (table-exists? db "narinfos")
     (perform-initial-database-setup db))
@@ -182,6 +198,11 @@ CREATE TABLE cached_narinfo_files (
 CREATE INDEX cached_narinfo_files_narinfo_id
   ON cached_narinfo_files (narinfo_id);"))
 
+  (unless (column-exists? db "narinfos" "added_at")
+    (sqlite-exec
+     db
+     "ALTER TABLE narinfos ADD COLUMN added_at TEXT;"))
+
   (sqlite-exec
    db
    "CREATE INDEX IF NOT EXISTS narinfo_tags_narinfo_id
@@ -515,9 +536,9 @@ SELECT id FROM tags WHERE key = :key AND value = :value"
             db
             "
 INSERT INTO narinfos (
-  store_path, nar_hash, nar_size, deriver, system, contents
+  store_path, nar_hash, nar_size, deriver, system, contents, added_at
 ) VALUES (
-  :store_path, :nar_hash, :nar_size, :deriver, :system, :contents
+  :store_path, :nar_hash, :nar_size, :deriver, :system, :contents, :added_at
 )"
             #:cache? #t)))
       (sqlite-bind-arguments
@@ -527,7 +548,8 @@ INSERT INTO narinfos (
        #:nar_size (narinfo-size narinfo)
        #:deriver (narinfo-deriver narinfo)
        #:system (narinfo-system narinfo)
-       #:contents (narinfo-contents narinfo))
+       #:contents (narinfo-contents narinfo)
+       #:added_at (date->string (current-date) "~1 ~3"))
 
       (sqlite-step statement)
       (sqlite-reset statement)