Import avatars and likes in the Zendesk AP importer

Import avatars and likes in the Zendesk AP importer

Co-authored-by: Justin DiRose justin@justindirose.com

diff --git a/script/import_scripts/base/generic_database.rb b/script/import_scripts/base/generic_database.rb
index 885d5bb..2b10e0c 100644
--- a/script/import_scripts/base/generic_database.rb
+++ b/script/import_scripts/base/generic_database.rb
@@ -14,6 +14,7 @@ module ImportScripts
 
       configure_database
       create_category_table
+      create_like_table
       create_user_table
       create_topic_table
       create_post_table
@@ -34,37 +35,64 @@ module ImportScripts
       SQL
     end
 
+    def insert_like(like)
+      @db.execute(<<-SQL, prepare(like))
+        INSERT OR REPLACE INTO like (id, user_id, post_id, topic)
+        VALUES (:id, :user_id, :post_id, :topic)
+      SQL
+    end
+
     def insert_topic(topic)
+      like_user_ids = topic.delete(:like_user_ids)
       attachments = topic.delete(:attachments)
       topic[:upload_count] = attachments&.size || 0
 
-      @db.execute(<<-SQL, prepare(topic))
-        INSERT OR REPLACE INTO topic (id, title, raw, category_id, closed, user_id, created_at, url, upload_count)
-        VALUES (:id, :title, :raw, :category_id, :closed, :user_id, :created_at, :url, :upload_count)
-      SQL
-
-      attachments&.each do |attachment|
-        @db.execute(<<-SQL, topic_id: topic[:id], path: attachment)
-          INSERT OR REPLACE INTO topic_upload (topic_id, path)
-          VALUES (:topic_id, :path)
+      @db.transaction do
+        @db.execute(<<-SQL, prepare(topic))
+          INSERT OR REPLACE INTO topic (id, title, raw, category_id, closed, user_id, created_at, url, upload_count)
+          VALUES (:id, :title, :raw, :category_id, :closed, :user_id, :created_at, :url, :upload_count)
         SQL
+
+        attachments&.each do |attachment|
+          @db.execute(<<-SQL, topic_id: topic[:id], path: attachment)
+            INSERT OR REPLACE INTO topic_upload (topic_id, path)
+            VALUES (:topic_id, :path)
+          SQL
+        end
+
+        like_user_ids&.each do |user_id|
+          @db.execute(<<-SQL, topic_id: topic[:id], user_id: user_id)
+            INSERT OR REPLACE INTO like (topic_id, user_id)
+            VALUES (:topic_id, :user_id)
+          SQL
+        end
       end
     end
 
     def insert_post(post)
+      like_user_ids = post.delete(:like_user_ids)
       attachments = post.delete(:attachments)
       post[:upload_count] = attachments&.size || 0
 
-      @db.execute(<<-SQL, prepare(post))
-        INSERT OR REPLACE INTO post (id, raw, topic_id, user_id, created_at, reply_to_post_id, url, upload_count)
-        VALUES (:id, :raw, :topic_id, :user_id, :created_at, :reply_to_post_id, :url, :upload_count)
-      SQL
-
-      attachments&.each do |attachment|
-        @db.execute(<<-SQL, post_id: post[:id], path: attachment)
-          INSERT OR REPLACE INTO post_upload (post_id, path)
-          VALUES (:post_id, :path)
+      @db.transaction do
+        @db.execute(<<-SQL, prepare(post))
+          INSERT OR REPLACE INTO post (id, raw, topic_id, user_id, created_at, reply_to_post_id, url, upload_count)
+          VALUES (:id, :raw, :topic_id, :user_id, :created_at, :reply_to_post_id, :url, :upload_count)
         SQL
+
+        attachments&.each do |attachment|
+          @db.execute(<<-SQL, post_id: post[:id], path: attachment)
+            INSERT OR REPLACE INTO post_upload (post_id, path)
+            VALUES (:post_id, :path)
+          SQL
+        end
+
+        like_user_ids&.each do |user_id|
+          @db.execute(<<-SQL, post_id: post[:id], user_id: user_id)
+            INSERT OR REPLACE INTO like (post_id, user_id)
+            VALUES (:post_id, :user_id)
+          SQL
+        end
       end
     end
 
@@ -196,6 +224,25 @@ module ImportScripts
       SQL
     end
 
+    def count_likes
+      @db.get_first_value(<<-SQL)
+        SELECT COUNT(*)
+        FROM like
+      SQL
+    end
+
+    def fetch_likes(last_row_id)
+      rows = @db.execute(<<-SQL, last_row_id)
+        SELECT ROWID AS rowid, *
+        FROM like
+        WHERE ROWID > :last_row_id
+        ORDER BY ROWID
+        LIMIT #{@batch_size}
+      SQL
+
+      add_last_column_value(rows, 'rowid')
+    end
+
     def execute_sql(sql)
       @db.execute(sql)
     end
@@ -227,6 +274,16 @@ module ImportScripts
       SQL
     end
 
+    def create_like_table
+      @db.execute <<-SQL
+        CREATE TABLE IF NOT EXISTS like (
+          user_id #{key_data_type} NOT NULL,
+          topic_id #{key_data_type},
+          post_id #{key_data_type}
+        )
+      SQL
+    end
+
     def create_user_table
       @db.execute <<-SQL
         CREATE TABLE IF NOT EXISTS user (
diff --git a/script/import_scripts/zendesk_api.rb b/script/import_scripts/zendesk_api.rb
index 43749dc..7d16be8 100644
--- a/script/import_scripts/zendesk_api.rb
+++ b/script/import_scripts/zendesk_api.rb
@@ -30,12 +30,22 @@ class ImportScripts::ZendeskApi < ImportScripts::Base
     import_users
     import_topics
     import_posts
+    import_likes
   end
 
   def fetch_from_api
+    fetch_categories
+    fetch_topics
+    fetch_posts
+    fetch_users
+
+    @db.sort_posts_by_created_at
+  end
+
+  def fetch_categories
     puts '', 'fetching categories...'
 
-    get_from_api('/api/v2/community/topics.json', 'topics') do |row|
+    get_from_api('/api/v2/community/topics.json', 'topics', show_status: true) do |row|
       @db.insert_category(
         id: row['id'],
         name: row['name'],
@@ -44,10 +54,16 @@ class ImportScripts::ZendeskApi < ImportScripts::Base
         url: row['html_url']
       )
     end
+  end
 
+  def fetch_topics
     puts '', 'fetching topics...'
 
-    get_from_api('/api/v2/community/posts.json', 'posts') do |row|
+    get_from_api('/api/v2/community/posts.json', 'posts', show_status: true) do |row|
+      if row['vote_count'] > 0
+        like_user_ids = fetch_likes("/api/v2/community/posts/#{row['id']}/votes.json")
+      end
+
       @db.insert_topic(
         id: row['id'],
         title: row['title'],
@@ -56,11 +72,15 @@ class ImportScripts::ZendeskApi < ImportScripts::Base
         closed: row['closed'],
         user_id: row['author_id'],
         created_at: row['created_at'],
-        url: row['html_url']
+        url: row['html_url'],
+        like_user_ids: like_user_ids
       )
     end
+  end
 
+  def fetch_posts
     puts '', 'fetching posts...'
+    current_count = 0
     total_count = @db.count_topics
     start_time = Time.now
     last_id = ''
@@ -69,49 +89,72 @@ class ImportScripts::ZendeskApi < ImportScripts::Base
       rows, last_id = @db.fetch_topics(last_id)
       break if rows.empty?
 
-      print_status(offset, total_count, start_time)
-
       rows.each do |topic_row|
-        get_from_api("/api/v2/community/posts/#{topic_row['id']}/comments.json", 'comments', show_status: false) do |row|
+        get_from_api("/api/v2/community/posts/#{topic_row['id']}/comments.json", 'comments') do |row|
+          if row['vote_count'] > 0
+            like_user_ids = fetch_likes("/api/v2/community/posts/#{topic_row['id']}/comments/#{row['id']}/votes.json")
+          end
+
           @db.insert_post(
             id: row['id'],
             raw: row['body'],
             topic_id: topic_row['id'],
             user_id: row['author_id'],
             created_at: row['created_at'],
-            url: row['html_url']
+            url: row['html_url'],
+            like_user_ids: like_user_ids
           )
         end
+
+        current_count += 1
+        print_status(current_count, total_count, start_time)
       end
     end
+  end
 
+  def fetch_users
     puts '', 'fetching users...'
 
-    results = @db.execute_sql("SELECT user_id FROM topic")
-    user_ids = results.map { |h| h['user_id']&.to_i }
-    results = @db.execute_sql("SELECT user_id FROM post")
-    user_ids += results.map { |h| h['user_id']&.to_i }
-    user_ids.uniq!
-    user_ids.sort!
+    user_ids = @db.execute_sql(<<~SQL).map { |row| row['user_id'] }
+      SELECT user_id FROM topic
+      UNION
+      SELECT user_id FROM post
+      UNION

[... diff too long, it was truncated ...]

GitHub sha: 888b635c

1 Like