Redesigned persistent data formta

This is the start of a project to add imagery to the bot's output. We began by rewriting the scraper to use a SQLite datafile instead of dumping achievement names to a text file. This allows storage of additional information about each achievement, and allows for more sophisticated scraping.

Profiles to be scraped can be added on the command line using the scraper script, instead of being specified in a config file. The scraper can conduct full or delta scrapes; in a delta scrape, only each profile's recent games are scraped, whereas they are all scraped in a full scrape.

When a game is scraped for the first time, images from the store page of that game are saved locally to be used by the bot. The bot has been altered to not use Twitter, and instead generate a pixelated image based on an image from the game of the chosen achievement. This is just for development purposes. It also crashes occasionally due to picking an achievement from a game that does not have any images saved.

Sprites of the moons from Odyssey have been included in the repository. A short message denoting their copyright is included.

20 files changed, 442 insertions, 32 deletions

@@ -5,12 +5,17 @@ add_subdirectory(vendor/libtwittercpp)
 
 find_package(PkgConfig)
 pkg_check_modules(yaml-cpp yaml-cpp REQUIRED)
+pkg_check_modules(GraphicsMagick GraphicsMagick++ REQUIRED)
+pkg_check_modules(sqlite3 sqlite3>=3.8.3 REQUIRED)
 
 include_directories(
   vendor/libtwittercpp/src
-  ${yaml-cpp_INCLUDE_DIRS})
+  ${yaml-cpp_INCLUDE_DIRS}
+  ${GraphicsMagick_INCLUDE_DIRS})
 
-add_executable(lunatic lunatic.cpp)
+link_directories(${GraphicsMagick_LIBRARY_DIRS})
+
+add_executable(lunatic lunatic.cpp database.cpp)
 set_property(TARGET lunatic PROPERTY CXX_STANDARD 11)
 set_property(TARGET lunatic PROPERTY CXX_STANDARD_REQUIRED ON)
-target_link_libraries(lunatic ${yaml-cpp_LIBRARIES} twitter++)
\ No newline at end of file
+target_link_libraries(lunatic ${yaml-cpp_LIBRARIES} ${GraphicsMagick_LIBRARIES} ${sqlite3_LIBRARIES} twitter++)
\ No newline at end of file
@@ -0,0 +1,5 @@
+source 'https://rubygems.org'
+
+gem 'sqlite3'
+gem 'sequel'
+gem 'nokogiri'
@@ -0,0 +1,19 @@
+GEM
+  remote: https://rubygems.org/
+  specs:
+    mini_portile2 (2.3.0)
+    nokogiri (1.8.2)
+      mini_portile2 (~> 2.3.0)
+    sequel (5.5.0)
+    sqlite3 (1.3.13)
+
+PLATFORMS
+  ruby
+
+DEPENDENCIES
+  nokogiri
+  sequel
+  sqlite3
+
+BUNDLED WITH
+   1.16.1
@@ -0,0 +1,122 @@
+#include "database.h"
+#include <sqlite3.h>
+#include <stdexcept>
+
+database::database(std::string path)
+{
+  if (sqlite3_open_v2(
+    path.c_str(),
+    &ppdb_,
+    SQLITE_OPEN_READONLY,
+    NULL) != SQLITE_OK)
+  {
+    // We still have to free the resources allocated. In the event that
+    // allocation failed, ppdb will be null and sqlite3_close_v2 will just
+    // ignore it.
+    std::string errmsg(sqlite3_errmsg(ppdb_));
+    sqlite3_close_v2(ppdb_);
+
+    throw std::logic_error(errmsg);
+  }
+}
+
+database::database(database&& other) : database()
+{
+  swap(*this, other);
+}
+
+database& database::operator=(database&& other)
+{
+  swap(*this, other);
+
+  return *this;
+}
+
+void swap(database& first, database& second)
+{
+  std::swap(first.ppdb_, second.ppdb_);
+}
+
+database::~database()
+{
+  sqlite3_close_v2(ppdb_);
+}
+
+achievement database::getRandomAchievement() const
+{
+  std::string queryString = "SELECT achievements.achievement_id, achievements.game_id, achievements.title, games.moon_image FROM achievements INNER JOIN games ON games.game_id = achievements.game_id ORDER BY RANDOM() LIMIT 1";
+
+  sqlite3_stmt* ppstmt;
+  if (sqlite3_prepare_v2(
+    ppdb_,
+    queryString.c_str(),
+    queryString.length(),
+    &ppstmt,
+    NULL) != SQLITE_OK)
+  {
+    std::string errorMsg = sqlite3_errmsg(ppdb_);
+    sqlite3_finalize(ppstmt);
+
+    throw std::logic_error(errorMsg);
+  }
+
+  if (sqlite3_step(ppstmt) != SQLITE_ROW)
+  {
+    std::string errorMsg = sqlite3_errmsg(ppdb_);
+    sqlite3_finalize(ppstmt);
+
+    throw std::logic_error(errorMsg);
+  }
+
+  achievement result;
+
+  result.achievementId = sqlite3_column_int(ppstmt, 0);
+  result.gameId = sqlite3_column_int(ppstmt, 1);
+  result.title = reinterpret_cast<const char*>(sqlite3_column_text(ppstmt, 2));
+  result.moonImage = reinterpret_cast<const char*>(sqlite3_column_text(ppstmt, 3));
+
+  sqlite3_finalize(ppstmt);
+
+  return result;
+}
+
+std::string database::getRandomImageForGame(int gameId) const
+{
+  std::string queryString = "SELECT filename FROM images WHERE game_id = ? ORDER BY RANDOM() LIMIT 1";
+
+  sqlite3_stmt* ppstmt;
+  if (sqlite3_prepare_v2(
+    ppdb_,
+    queryString.c_str(),
+    queryString.length(),
+    &ppstmt,
+    NULL) != SQLITE_OK)
+  {
+    std::string errorMsg = sqlite3_errmsg(ppdb_);
+    sqlite3_finalize(ppstmt);
+
+    throw std::logic_error(errorMsg);
+  }
+
+  if (sqlite3_bind_int(ppstmt, 1, gameId) != SQLITE_OK)
+  {
+    std::string errorMsg = sqlite3_errmsg(ppdb_);
+    sqlite3_finalize(ppstmt);
+
+    throw std::logic_error(errorMsg);
+  }
+
+  if (sqlite3_step(ppstmt) != SQLITE_ROW)
+  {
+    std::string errorMsg = sqlite3_errmsg(ppdb_);
+    sqlite3_finalize(ppstmt);
+
+    throw std::logic_error(errorMsg);
+  }
+
+  std::string result = reinterpret_cast<const char*>(sqlite3_column_text(ppstmt, 0));
+
+  sqlite3_finalize(ppstmt);
+
+  return result;
+}
@@ -0,0 +1,53 @@
+#ifndef DATABASE_H_75C3CE0F
+#define DATABASE_H_75C3CE0F
+
+#include <string>
+
+struct sqlite3;
+
+struct achievement {
+  int achievementId;
+  int gameId;
+  std::string title;
+  std::string moonImage;
+};
+
+class database {
+public:
+
+  // Constructor
+
+  explicit database(std::string path);
+
+  // Disable copying
+
+  database(const database& other) = delete;
+  database& operator=(const database& other) = delete;
+
+  // Move constructor and move assignment
+
+  database(database&& other);
+  database& operator=(database&& other);
+
+  // Swap
+
+  friend void swap(database& first, database& second);
+
+  // Destructor
+
+  ~database();
+
+  // Accessors
+
+  achievement getRandomAchievement() const;
+
+  std::string getRandomImageForGame(int gameId) const;
+
+private:
+
+  database() = default;
+
+  sqlite3* ppdb_ = nullptr;
+};
+
+#endif /* end of include guard: DATABASE_H_75C3CE0F */
@@ -5,6 +5,8 @@
 #include <twitter.h>
 #include <chrono>
 #include <thread>
+#include <Magick++.h>
+#include "database.h"
 
 int main(int argc, char** argv)
 {
@@ -14,6 +16,8 @@ int main(int argc, char** argv)
     return -1;
   }
 
+  Magick::InitializeMagick(nullptr);
+
   std::string configfile(argv[1]);
   YAML::Node config = YAML::LoadFile(configfile);
 
@@ -23,7 +27,9 @@ int main(int argc, char** argv)
   auth.setAccessKey(config["access_key"].as<std::string>());
   auth.setAccessSecret(config["access_secret"].as<std::string>());
 
-  twitter::client client(auth);
+  //twitter::client client(auth);
+
+  database db(config["database"].as<std::string>());
 
   std::random_device randomDevice;
   std::mt19937 rng(randomDevice());
@@ -32,6 +38,40 @@ int main(int argc, char** argv)
   {
     std::cout << "Generating tweet" << std::endl;
 
+    achievement ach = db.getRandomAchievement();
+    std::string imageName = db.getRandomImageForGame(ach.gameId);
+    std::string imagePath = config["images"].as<std::string>()
+      + "/" + imageName;
+
+
+    try
+    {
+      Magick::Image image;
+      image.read(imagePath);
+      image.transform("1600x900");
+      image.scale("160x90");
+      image.scale("1600x900");
+      image.magick("png");
+      image.write("output.png");
+    } catch (const Magick::WarningCoder& ex)
+    {
+      // Ok
+    }
+    
+
+
+
+
+
+
+
+
+
+
+
+
+    /*
+
     // Reload achievements list every time in case it has been updated
     std::vector<std::string> achievements;
     std::ifstream datafile(config["achievements"].as<std::string>());
@@ -53,7 +93,7 @@ int main(int argc, char** argv)
     }
 
     std::uniform_int_distribution<int> dist(0, achievements.size() - 1);
-    std::string achievement = achievements[dist(rng)];
+    std::string achievement = achievements[dist(rng)];*/
 
     std::string header;
     if (std::bernoulli_distribution(1.0 / 50.0)(rng))
@@ -63,16 +103,16 @@ int main(int argc, char** argv)
       header = "YOU GOT A MOON!";
     }
 
-    std::string action = header + "\n" + achievement;
+    std::string action = header + "\n" + ach.title;
     action.resize(140);
 
-    try
+    /*try
     {
       client.updateStatus(action);
     } catch (const twitter::twitter_error& e)
     {
       std::cout << "Twitter error: " << e.what() << std::endl;
-    }
+    }*/
 
     std::cout << action << std::endl;
     std::cout << "Waiting" << std::endl;
@@ -0,0 +1 @@
+The images in this directory are originally from the game Super Mario Odyssey, which is copyrighted by Nintendo.
\ No newline at end of file
@@ -0,0 +1,32 @@
+CREATE TABLE `profiles` (
+  `profile_id` INTEGER PRIMARY KEY,
+  `profile_path` VARCHAR(255) NOT NULL
+);
+
+CREATE UNIQUE INDEX `profile_by_path` ON `profiles`(`profile_path`);
+
+CREATE TABLE `games` (
+  `game_id` INTEGER PRIMARY KEY,
+  `steam_appid` INTEGER NOT NULL,
+  `moon_image` VARCHAR(255) NOT NULL
+);
+
+CREATE UNIQUE INDEX `game_by_appid` ON `games`(`steam_appid`);
+
+CREATE TABLE `achievements` (
+  `achievement_id` INTEGER PRIMARY KEY,
+  `game_id` INTEGER NOT NULL,
+  `title` VARCHAR(255) NOT NULL
+);
+
+CREATE TABLE `dids` (
+  `profile_id` INTEGER NOT NULL,
+  `achievement_id` INTEGER NOT NULL,
+  `achieved_at` DATETIME NOT NULL
+);
+
+CREATE TABLE `images` (
+  `image_id` INTEGER PRIMARY KEY,
+  `game_id` INTEGER NOT NULL,
+  `filename` VARCHAR(255) NOT NULL
+);
@@ -1,42 +1,175 @@
 require 'json'
-require 'nokogiri'
 require 'open-uri'
 require 'yaml'
 
-config = YAML.load(open(ARGV[0]))
-usernames = config["usernames"]
+require 'rubygems'
+require 'bundler/setup'
+Bundler.require :default
 
-achieves = usernames.map do |username|
-  page = Nokogiri::HTML(open("https://steamcommunity.com/#{username}/games/?tab=all"))
+@config = YAML.load(open(ARGV[0]))
+db_existed = File.exists?(@config["database"])
+db = Sequel.connect("sqlite://" + @config["database"])
+
+if ARGV[1] == "init"
+  if db_existed
+    raise "Datafile already exists"
+  end
+
+  schema = File.read("schema.sql")
+
+  db.run schema
+
+  puts "Initialized datafile"
+
+  exit
+end
+
+class Profile < Sequel::Model
+  many_to_many :achievements, join_table: :dids
+end
+
+class Game < Sequel::Model
+  one_to_many :achievements
+  one_to_many :images
+end
+
+class Achievement < Sequel::Model
+  many_to_one :game
+  many_to_many :profiles, join_table: :dids
+end
+
+class Image < Sequel::Model
+  many_to_one :game
+end
+
+class Did < Sequel::Model
+  many_to_one :profile
+  many_to_one :achievement
+end
+
+@moonimgs = Dir.entries(@config["moon_images"]).select do |img|
+  img.end_with? ".png"
+end
+
+def scrape_profile(profile, full)
+  if full
+    url = "https://steamcommunity.com/#{profile.profile_path}/games/?tab=all"
+  else
+    url = "https://steamcommunity.com/#{profile.profile_path}/games/"
+  end
+
+  page = Nokogiri::HTML(open(url))
   script = page.css(".responsive_page_template_content script").text[18..-1]
   data = JSON.parse(script[0..script.index(";\r\n\t\t")-1])
   ids = data.map { |d| d["appid"] }
 
   index = 0
-  ids.map do |id|
+  ids.each do |id|
     index += 1
-    puts "#{username} - #{index}/#{ids.count}"
+    puts "#{profile.profile_path} - #{index}/#{ids.count}"
 
-    achsp = Nokogiri::HTML(open("https://steamcommunity.com/#{username}/stats/#{id}/"))
-    achsp.css(".achieveTxt .achieveUnlockTime + h3").map { |d| d.text }
-  end
-end.flatten
+    achsp = Nokogiri::HTML(
+      open("https://steamcommunity.com/#{profile.profile_path}/stats/#{id}/"))
 
-if File.exists?(config["achievements"])
-  already = File.read(config["achievements"]).split("\n")
-  all_achieves = achieves + already
-else
-  all_achieves = achieves
-end
+    achsp.css(".achieveTxt").each do |node|
+      unless node.css(".achieveUnlockTime").empty?
+        if Game.where(steam_appid: id).count > 0
+          game = Game.where(steam_appid: id).first
+        else
+          moon_index = Random.rand(@moonimgs.size)
+
+          game = Game.new(steam_appid: id, moon_image: @moonimgs[moon_index])
+          game.save
+
+          storepage = Nokogiri::HTML(
+            open("http://store.steampowered.com/app/#{id}"))
+
+          img_id = 0
+          storepage.css(".highlight_screenshot_link").each do |node|
+            begin
+              imagepage = open(node["href"]).read
+
+              img_id += 1
+              img_filename = "#{id}-#{img_id}.jpg"
+              img_filepath = File.join(@config["images"], img_filename)
+
+              img_file = File.open(img_filepath, "w")
+              img_file.write(imagepage)
+              img_file.close
+
+              image = Image.new(game: game, filename: img_filename)
+              image.save
+            rescue OpenURI::HTTPError
+              puts "Error downloading an image"
+            end
+
+            sleep 2
+          end
+        end
+
+        title = node.at_css("h3").text
 
-all_achieves.sort!
-all_achieves.uniq!
+        if game.achievements_dataset.where(title: title).count > 0
+          achievement = game.achievements_dataset.where(title: title).first
+        else
+          achievement = Achievement.new(game: game, title: title)
+          achievement.save
+        end
 
-if config.key? "blacklist"
-  blacklist = File.read(config["blacklist"]).split("\n")
-  all_achieves.reject! { |l| blacklist.include? l }
+        unless Did.where(profile: profile, achievement: achievement).count > 0
+          begin
+            unlock = DateTime.strptime(
+              node.css(".achieveUnlockTime").text.lstrip[9..-1],
+              "%b %d, %Y @ %l:%M%P")
+          rescue ArgumentError
+            unlock = DateTime.strptime(
+              node.css(".achieveUnlockTime").text.lstrip[9..-1],
+              "%b %d @ %l:%M%P")
+          end
+
+          join = Did.new(
+            profile: profile,
+            achievement: achievement,
+            achieved_at: unlock)
+          join.save
+        end
+      end
+    end
+  end
 end
 
-File.open(config["achievements"], "w") do |f|
-  f << all_achieves.join("\n")
+if ARGV[1] == "add"
+  userpath = ARGV[2]
+
+  if Profile.where(profile_path: userpath).count > 0
+    raise "Profile " + userpath + " already exists"
+  end
+
+  profile = Profile.new(profile_path: userpath)
+  profile.save
+
+  scrape_profile profile, true
+elsif ARGV[1] == "update"
+  if ARGV.size == 3
+    scrape_profile Profile.where(profile_path: ARGV[2]).first, false
+  else
+    Profile.all.each do |profile|
+      scrape_profile profile, false
+    end
+  end
+elsif ARGV[1] == "full"
+  if ARGV.size == 3
+    scrape_profile Profile.where(profile_path: ARGV[2]).first, true
+  else
+    Profile.all.each do |profile|
+      scrape_profile profile, true
+    end
+  end
+elsif ARGV[1] == "recolor"
+  Game.all.each do |game|
+    moon_index = Random.rand(@moonimgs.size)
+
+    game.moon_image = @moonimgs[moon_index]
+    game.save
+  end
 end