Switch to storing Git repositories in a table

Rather than just storing the URL in the guix_revisions and
load_new_guix_revision_jobs tables. This will help when storing more
information like tags and branches in the future.

2 files changed, 64 insertions, 5 deletions

diff --git a/guix-data-service/model/git-repository.scm b/guix-data-service/model/git-repository.scm
new file mode 100644
index 0000000..5f35cd3
--- /dev/null
+++ b/guix-data-service/model/git-repository.scm
@@ -0,0 +1,58 @@
+(define-module (guix-data-service model git-repository)
+  #:use-module (ice-9 match)
+  #:use-module (squee)
+  #:export (all-git-repositories
+            git-repository-id->url
+            git-repository-url->git-repository-id
+
+            guix-revisions-and-jobs-for-git-repository))
+
+(define (all-git-repositories conn)
+  (exec-query
+   conn
+   (string-append
+    "SELECT id, label, url FROM git_repositories")))
+
+(define (git-repository-id->url conn id)
+  (match
+      (exec-query
+       conn
+       (string-append
+        "SELECT url FROM git_repositories WHERE id = $1;")
+       (list id))
+    (((url)) url)))
+
+(define (git-repository-url->git-repository-id conn url)
+  (let ((existing-id
+         (exec-query
+          conn
+          (string-append
+           "SELECT id FROM git_repositories WHERE url = '" url "'"))))
+    (match existing-id
+      (((id)) id)
+      (()
+       (caar
+        (exec-query conn
+                    (string-append
+                     "INSERT INTO git_repositories "
+                     "(url) "
+                     "VALUES "
+                     "('" url "') "
+                     "RETURNING id")))))))
+
+(define (guix-revisions-and-jobs-for-git-repository conn git-repository-id)
+  (define query
+    "
+SELECT NULL AS id, load_new_guix_revision_jobs.id AS job_id, commit, source
+FROM load_new_guix_revision_jobs
+WHERE git_repository_id = $1
+UNION
+SELECT id, NULL, commit, NULL
+FROM guix_revisions
+WHERE git_repository_id = $1
+ORDER BY 1 DESC NULLS FIRST, 2 DESC LIMIT 10;")
+
+  (exec-query
+   conn
+   query
+   (list git-repository-id)))
diff --git a/guix-data-service/model/guix-revision.scm b/guix-data-service/model/guix-revision.scm
index f03aa6d..6ae7693 100644
--- a/guix-data-service/model/guix-revision.scm
+++ b/guix-data-service/model/guix-revision.scm
@@ -25,21 +25,22 @@
      id)
     (() #f)))
 
-(define (insert-guix-revision conn url commit store_path)
+(define (insert-guix-revision conn git-repository-id commit store_path)
   (define insert
     (string-append "INSERT INTO guix_revisions "
-                   "(url, commit, store_path) VALUES "
-                   "('" url "', '"
+                   "(git_repository_id, commit, store_path) VALUES "
+                   "(" git-repository-id ", '"
                    commit "', '"
                    store_path "') "
                    "RETURNING id;"))
 
   (map car (exec-query conn insert)))
 
-(define (guix-revision-exists? conn url commit)
+(define (guix-revision-exists? conn git-repository-id commit)
   (define query
     (string-append "SELECT EXISTS("
-                   "SELECT 1 FROM guix_revisions WHERE url = '" url "' "
+                   "SELECT 1 FROM guix_revisions WHERE "
+                   "git_repository_id = '" git-repository-id "' "
                    "AND commit = '" commit "')"
                    ";"))