From 87c347bfb51895499cc862a33453df9945a4656e Mon Sep 17 00:00:00 2001
From: horus
Date: Mon, 12 Feb 2018 22:50:56 +0100
Subject: Changes in the database schema.

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

(limited to 'crawler/database.go')

diff --git a/crawler/database.go b/crawler/database.go
index 3d5b1bd..a94e2f0 100644
--- a/crawler/database.go
+++ b/crawler/database.go
@@ -3,6 +3,7 @@ package main
 import (
 	"database/sql"
 	"log"
+	"strings"
 )
 
 func (app *App) createTables() error {
@@ -25,13 +26,15 @@ func (app *App) createTables() error {
 		name TEXT,
 		url TEXT,
 		short_url TEXT,
-		abv FLOAT(100,0),
-		volume FLOAT,
+		abv DECIMAL(10,2),
+		volume DECIMAL(10,2),
 		original_price INT,
 		discounted_price INT,
 		base_price INT,
 		image_url TEXT,
 		spirit_type TEXT,
+		website TEXT,
+		website_raw TEXT,
 		valid_until INT DEFAULT NULL,
 		created_at INT,
 		FOREIGN KEY(shop) REFERENCES shop(id)
@@ -41,14 +44,16 @@ func (app *App) createTables() error {
 		return err
 	}
 
-	views := []string{"whisky", "wodka", "rum", "gin", "misc"}
+	views := []string{"whisky", "wodka", "rum", "gin", "misc", "all"}
 
 	for _, v := range views {
 		var spirit_type string
 		if v == "misc" {
-			spirit_type = "Verschiedenes"
+			spirit_type = `spirit_type = "` + v + `"`
+		} else if v == "all" {
+			spirit_type = ""
 		} else {
-			spirit_type = v
+			spirit_type = `spirit_type NOT IN ("` + strings.Join(views, `", "`) + `")`
 		}
 
 		view_query := `CREATE OR REPLACE VIEW ` + v + `_view AS
@@ -57,8 +62,8 @@ func (app *App) createTables() error {
 			shop.name as shop, shop.url as shop_url, shop.shipping_costs/100 as shipping_costs, shop.free_shipping, ROUND(100-((discounted_price/original_price)*100)) AS procent, spirit_type, created_at
 		FROM angebot
 		JOIN shop ON angebot.shop = shop.id
-		WHERE
-			spirit_type = "` + spirit_type + `"`
+		WHERE 1 = 1 
+			` + spirit_type + ` AND (valid_until IS NULL OR valid_until > (SELECT UNIX_TIMESTAMP()))`
 		_, err = app.DB.Exec(view_query)
 		if err != nil {
 			return err
@@ -70,7 +75,7 @@ func (app *App) createTables() error {
 
 func (app *App) save_offer(W []Angebot) error {
 
-	query := `INSERT INTO angebot (shop, name, url, abv, volume, original_price, discounted_price, base_price, valid_until, image_url, spirit_type, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
+	query := `INSERT INTO angebot (shop, name, url, abv, volume, original_price, discounted_price, base_price, valid_until, image_url, website_raw, spirit_type, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
 
 	stmt, err := app.DB.Prepare(query)
 	if err != nil {
@@ -94,9 +99,9 @@ func (app *App) save_offer(W []Angebot) error {
 		if err == sql.ErrNoRows {
 
 			if 0 == o.Valid_until {
-				_, err = stmt.Exec(o.Shop, o.Name, o.Url, o.Abv, o.Volume, o.Original_price, o.Discounted_price, o.Base_price, sql.NullInt64{}, o.Image_url, o.Spirit_type, app.Now)
+				_, err = stmt.Exec(o.Shop, o.Name, o.Url, o.Abv, o.Volume, o.Original_price, o.Discounted_price, o.Base_price, sql.NullInt64{}, o.Image_url, o.Website, o.Spirit_type, app.Now)
 			} else {
-				_, err = stmt.Exec(o.Shop, o.Name, o.Url, o.Abv, o.Volume, o.Original_price, o.Discounted_price, o.Base_price, o.Valid_until, o.Image_url, o.Spirit_type, app.Now)
+				_, err = stmt.Exec(o.Shop, o.Name, o.Url, o.Abv, o.Volume, o.Original_price, o.Discounted_price, o.Base_price, o.Valid_until, o.Image_url, o.Website, o.Spirit_type, app.Now)
 			}
 			if err != nil {
 				return err
@@ -109,11 +114,11 @@ func (app *App) save_offer(W []Angebot) error {
 	return nil
 }
 
-func (app *App) remove_expired(W []Angebot) error {
+func (app *App) remove_expired(W []Angebot, shop_id int) error {
 
-	query := `SELECT id, shop, name, url, original_price, discounted_price FROM angebot WHERE created_at < ? AND valid_until IS NULL`
+	query := `SELECT id, shop, name, url, original_price, discounted_price FROM angebot WHERE shop = ? AND created_at < ? AND valid_until IS NULL`
 
-	rows, err := app.DB.Queryx(query, app.Now)
+	rows, err := app.DB.Queryx(query, shop_id, app.Now)
 	if err != nil {
 		return err
 	}
@@ -140,8 +145,12 @@ func (app *App) remove_expired(W []Angebot) error {
 }
 
 func (app *App) offer_contains(W []Angebot, offer_db Angebot) bool {
+	if len(W) < 1 {
+		return false
+	}
+
 	for _, v := range W {
-		if v.Shop == offer_db.Shop && v.Name == offer_db.Name && v.Original_price == offer_db.Original_price && v.Discounted_price == offer_db.Discounted_price {
+		if (v.Shop == offer_db.Shop) && (v.Name == offer_db.Name) && (v.Original_price == offer_db.Original_price) && (v.Discounted_price == offer_db.Discounted_price) {
 
 			if app.Config.Debug {
 				log.Println("Contains: " + v.Name)
-- 
cgit v1.2.3