Start to handle information about Git branches

Add some new pages /branches and /branch/... as well as a new git_branches
table. Also extend the email processing to enter the branch information in to
the database.

1 files changed, 57 insertions, 0 deletions

diff --git a/guix-data-service/model/git-branch.scm b/guix-data-service/model/git-branch.scm
new file mode 100644
index 0000000..896e551
--- /dev/null
+++ b/guix-data-service/model/git-branch.scm
@@ -0,0 +1,57 @@
+(define-module (guix-data-service model git-branch)
+  #:use-module (squee)
+  #:export (insert-git-branch-entry
+            git-branches-for-commit
+            most-recent-100-commits-for-branch
+            all-branches-with-most-recent-commit))
+
+(define (insert-git-branch-entry conn
+                                 name commit
+                                 git-repository-id datetime)
+  (exec-query
+   conn
+   (string-append
+    "INSERT INTO git_branches (name, commit, git_repository_id, datetime) "
+    "VALUES ($1, $2, $3, $4) "
+    "ON CONFLICT DO NOTHING")
+   (list name
+         commit
+         git-repository-id
+         datetime)))
+
+(define (git-branches-for-commit conn commit)
+  (define query
+    "
+SELECT name, datetime FROM git_branches WHERE commit = $1
+ORDER BY datetime DESC")
+
+  (exec-query conn query (list commit)))
+
+(define (most-recent-100-commits-for-branch conn branch-name)
+  (define query
+    (string-append
+     "SELECT git_branches.commit, datetime, "
+     "(guix_revisions.id IS NOT NULL) as guix_revision_exists "
+     "FROM git_branches "
+     "LEFT OUTER JOIN guix_revisions ON git_branches.commit = guix_revisions.commit "
+     "WHERE name = $1 ORDER BY datetime DESC LIMIT 100;"))
+
+  (exec-query
+   conn
+   query
+   (list branch-name)))
+
+(define (all-branches-with-most-recent-commit conn)
+  (define query
+    (string-append
+     "SELECT DISTINCT ON (name) name, git_branches.commit, "
+     "datetime, (guix_revisions.id IS NOT NULL) guix_revision_exists "
+     "FROM git_branches "
+     "LEFT OUTER JOIN guix_revisions ON git_branches.commit = guix_revisions.commit "
+     "WHERE git_branches.commit IS NOT NULL "
+     "ORDER BY name, datetime DESC;"))
+
+  (exec-query
+   conn
+   query))
+