Created
March 29, 2018 01:20
-
-
Save inklesspen/8ab646f3224b26b0825f0d74f0531d64 to your computer and use it in GitHub Desktop.
download all tumblr likes and store in sqlite db
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
(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) | |
)) |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment