download all tumblr likes and store in sqlite db

likeindex.lisp

(defvar *client*
  (make-instance
   'north:client
   :key "key"
   :secret "secret"
   :token "token"
   :token-secret "secret"
   :request-token-uri "https://www.tumblr.com/oauth/request_token"
   :authorize-uri "https://www.tumblr.com/oauth/authorize"
   :access-token-uri "https://www.tumblr.com/oauth/access_token"))

(defmacro with-json-decoding (() &body body)
  `(let ((drakma:*text-content-types* (list*
                                       '("application" . "json")
                                       drakma:*text-content-types*)))
     (cl-json:decode-json-from-string
      (progn ,@body))))

;; got from humbler
(defun aget (key alist &optional default)
  (let ((cons (assoc key alist)))
    (if cons
        (cdr cons)
        default)))

(defun get-response-posts (resp)
  (let* ((resp (second resp))
         (posts (second resp)))
    (rest posts)))

(defun get-post-tags (post)
  (cdr (assoc :tags post)))

(defvar *user/likes* "https://api.tumblr.com/v2/user/likes")

(defun request-posts (client &key before after)
  (let ((params (cond (before `(("before" . ,before)))
                      (after `(("after" . ,after)))
                      (t '()))))
    (get-response-posts
     (with-json-decoding
         ()
       (north:make-signed-request
        client
        *user/likes*
        :get :params params)))))

(defun store-post (db post)
  (sqlite:with-transaction db
    (flet
        ((ex (s &rest params) (apply #'sqlite:execute-non-query db s params)))
      (let ((post_id (aget :id post)))
        (ex "DELETE FROM post_participants WHERE post_id = ?" post_id)
        (ex "DELETE FROM post_tags WHERE post_id = ?" post_id)
        (ex "DELETE FROM posts WHERE id = ?" post_id)
        (ex "INSERT INTO posts (id, type, url, blogname, timestamp, liked_timestamp, summary) VALUES (?, ?, ?, ?, ?, ?, ?)" (aget :id post) (aget :type post) (aget :post--url post) (aget :blog--name post) (aget :timestamp post) (aget ::liked--timestamp post) (aget :summary post))
        (iter (for participant in (aget :trail post))
              (ex "INSERT INTO post_participants (post_id, blogname, blogpost_id) VALUES (?, ?, ?)" post_id (aget :name (aget :blog participant)) (aget :id (aget :post participant))))
        (iter (for tag in (aget :tags post))
              (ex "INSERT OR IGNORE INTO tags (tag) VALUES (?)" tag)
              (let ((tag_id (sqlite:execute-single db "SELECT id FROM tags WHERE tag = ?" tag)))
                (ex "INSERT INTO post_tags (post_id, tag_id) VALUES (?, ?)" post_id tag_id)))))))


(defun first-fetch-needed-p (db)
  (when (sqlite:execute-single db "SELECT id FROM posts")
    t))

(defun first-fetch (db client)
  (iter (for post in (request-posts client))
        (store-post db post)))

(defun get-backfill-timestamp (db)
  (sqlite:execute-single db "SELECT liked_timestamp FROM posts ORDER BY liked_timestamp ASC LIMIT 1"))

(defun backfill-once (db client)
  (let* ((ts (write-to-string (get-backfill-timestamp db)))
         (posts (request-posts client :before ts)))
    (iter (for post in posts)
          (store-post db post))
    (length posts)))

(defun backfill-until-done (db client)
  (do ((last-post-count -1))
      ((and (<= 0 last-post-count) (> 20 last-post-count)))
    (format t "~a " last-post-count)
    (setf last-post-count (backfill-once db client))
    (format t "~a~%" last-post-count)
))

schema.sql

CREATE TABLE tags
(
  id  INTEGER PRIMARY KEY AUTOINCREMENT,
  tag VARCHAR
);
CREATE UNIQUE INDEX tags_tag_uindex
  ON tags (tag);
CREATE TABLE posts
(
  id              INTEGER PRIMARY KEY,
  type            VARCHAR NOT NULL,
  url             VARCHAR NOT NULL,
  blogname        VARCHAR NOT NULL,
  timestamp       INTEGER NOT NULL,
  liked_timestamp INTEGER NOT NULL,
  summary         VARCHAR NOT NULL
);
CREATE INDEX posts_type_index
  ON posts (type);
CREATE INDEX posts_blogname_index
  ON posts (blogname);
CREATE INDEX posts_timestamp_index
  ON posts (timestamp);
CREATE INDEX posts_liked_timestamp_index
  ON posts (liked_timestamp);
CREATE TABLE post_tags
(
  post_id INTEGER,
  tag_id  INTEGER,
  CONSTRAINT post_tags_tag_id_post_id_pk PRIMARY KEY (tag_id, post_id),
  CONSTRAINT post_tags_tags_id_fk FOREIGN KEY (tag_id) REFERENCES tags (id),
  CONSTRAINT post_tags_posts_id_fk FOREIGN KEY (post_id) REFERENCES posts (id)
);
CREATE INDEX post_tags_post_id_index
  ON post_tags (post_id);
CREATE INDEX post_tags_tag_id_index
  ON post_tags (tag_id);
CREATE TABLE post_participants
(
  post_id     INTEGER NOT NULL,
  blogname    VARCHAR NOT NULL,
  blogpost_id INTEGER NOT NULL,
  CONSTRAINT post_participants_posts_id_fk FOREIGN KEY (post_id) REFERENCES posts (id)
);
CREATE INDEX post_participants_blogname_index
  ON post_participants (blogname);