Add PlayAsia shop crawler

Most of this code isn't new - there's been a hidden 'Affiliates.pm'
module for a while. This just consolidates the code with the other shops
and opens up the code. The API endpoint is hidden. :)

1 files changed, 24 insertions, 0 deletions

diff --git a/util/updates/update_20190824.sql b/util/updates/update_20190824.sql
index f5a80231..6a8b5330 100644
--- a/util/updates/update_20190824.sql
+++ b/util/updates/update_20190824.sql
@@ -30,7 +30,31 @@ CREATE TABLE shop_dlsite (
   price     text NOT NULL DEFAULT ''
 );
 
+CREATE TABLE shop_playasia (
+  pax       text NOT NULL PRIMARY KEY,
+  gtin      bigint NOT NULL,
+  lastfetch timestamptz,
+  url       text NOT NULL DEFAULT '',
+  price     text NOT NULL DEFAULT ''
+);
+
+CREATE TABLE shop_playasia_gtin (
+  gtin      bigint NOT NULL PRIMARY KEY,
+  lastfetch timestamptz
+);
+
 GRANT SELECT, INSERT, UPDATE, DELETE ON shop_jlist               TO vndb_multi;
 GRANT SELECT, INSERT, UPDATE, DELETE ON shop_mg                  TO vndb_multi;
 GRANT SELECT, INSERT, UPDATE, DELETE ON shop_denpa               TO vndb_multi;
 GRANT SELECT, INSERT, UPDATE, DELETE ON shop_dlsite              TO vndb_multi;
+GRANT SELECT, INSERT, UPDATE, DELETE ON shop_playasia            TO vndb_multi;
+GRANT SELECT, INSERT, UPDATE, DELETE ON shop_playasia_gtin       TO vndb_multi;
+
+CREATE        INDEX shop_playasia__gtin    ON shop_playasia (gtin);
+
+INSERT INTO shop_playasia (pax, gtin, lastfetch, url, price)
+  SELECT data, MAX(gtin), MAX(lastfetch), MAX(url), MAX(price)
+    FROM affiliate_links
+    JOIN releases ON affiliate_links.rid = releases.id
+   WHERE affiliate = 0 AND NOT affiliate_links.hidden AND price <> 'US$ 0.00'
+   GROUP BY data;