From c102c17ddfc3aaddde33023f89ca77c2ee04f0e5 Mon Sep 17 00:00:00 2001
From: horus
Date: Mon, 19 Feb 2018 17:53:20 +0100
Subject: Fix nasty bug. (crawler)

---
 crawler/database.go | 28 ++++++++++++++--------------
 1 file changed, 14 insertions(+), 14 deletions(-)

(limited to 'crawler/database.go')

diff --git a/crawler/database.go b/crawler/database.go
index c557698..0d495c8 100644
--- a/crawler/database.go
+++ b/crawler/database.go
@@ -76,6 +76,15 @@ func (app *App) createTables() error {
 		}
 	}
 
+	view_query := `CREATE OR REPLACE VIEW _intern_view AS SELECT angebot.id, angebot.name, angebot.abv, angebot.volume, angebot.url as long_url, angebot.short_url as url, spirit_type, original_price, discounted_price, base_price, shop.name as shop, shop.id as shop_id, shop.short_url as shop_url, created_at, valid_until
+		FROM angebot
+		JOIN shop ON angebot.shop = shop.id
+		WHERE (valid_until = 0 OR valid_until > (SELECT UNIX_TIMESTAMP()))`
+	_, err = app.DB.Exec(view_query)
+	if err != nil {
+		return err
+	}
+
 	return err
 }
 
@@ -99,10 +108,8 @@ func (app *App) save_offer(W []Angebot) error {
 		}
 
 		// resembles UNIQUE constraint
-		detect_duplicate_query := `SELECT 1 FROM angebot WHERE name = ? AND shop = ? AND 
-		volume = ? AND abv = ? AND original_price = ? AND discounted_price = ? AND valid_until = ?`
-
-		err := app.DB.QueryRow(detect_duplicate_query, o.Name, o.Shop, o.Volume, o.Abv, o.Original_price, o.Discounted_price, o.Valid_until).Scan(&found)
+		detect_duplicate_query := fmt.Sprintf(`SELECT 1 FROM _intern_view WHERE name = ? AND shop_id = %d AND volume = %4.2f AND abv = %4.2f AND original_price = %d AND discounted_price = %d AND valid_until = %d`, o.Shop, o.Volume, o.Abv, o.Original_price, o.Discounted_price, o.Valid_until)
+		err := app.DB.QueryRow(detect_duplicate_query, o.Name).Scan(&found)
 
 		if err == sql.ErrNoRows {
 
@@ -110,24 +117,17 @@ func (app *App) save_offer(W []Angebot) error {
 
 			if err != nil {
 				o.error_msg = err.Error()
-				o.error_ctx = fmt.Sprintf(`INSERT INTO angebot
-					(shop, name, url, abv, volume, age, original_price, discounted_price, base_price, 
-					valid_until, image_url, spirit_type, created_at)
-					VALUES (%d, "%s", "%s", %f, %f, %d, %d, %d, %d, %d, "%s", "%s", %d)`, o.Shop, o.Name, o.Url, o.Abv, o.Volume, o.Age, o.Original_price,
+				o.error_ctx = fmt.Sprintf(`INSERT INTO angebot (shop, name, url, abv, volume, age, original_price, discounted_price, base_price, valid_until, image_url, spirit_type, created_at) VALUES (%d, "%s", "%s", %f, %f, %d, %d, %d, %d, %d, "%s", "%s", %d)`, o.Shop, o.Name, o.Url, o.Abv, o.Volume, o.Age, o.Original_price,
 					o.Discounted_price, o.Base_price, o.Valid_until, o.Image_url, o.Spirit_type, app.Now)
-				WarnOffer(o, "Save Offer: Inserting offer failed")
-				return err
+				WarnOffer(o, fmt.Sprintf("Save Offer: Inserting offer failed (%d)", found))
 			}
 
 			DebugOffer(o, "database.go: Inserting offer.")
 
 		} else if err != nil {
 			o.error_msg = err.Error()
-			o.error_ctx = fmt.Sprintf(`SELECT 1 FROM angebot WHERE name = %s AND shop = %d AND 
-						volume = %f AND abv = %f AND original_price = %d AND discounted_price = %d
-						AND valid_until = %d`, o.Name, o.Shop, o.Volume, o.Abv, o.Original_price, o.Discounted_price, o.Valid_until)
+			o.error_ctx = fmt.Sprintf(strings.Replace(detect_duplicate_query, "?", `"%s"`, 1), o.Name)
 			WarnOffer(o, "database.go: Duplicate query failed")
-			return err
 		}
 	}
 
-- 
cgit v1.2.3