From a418c52123969b01c37bafd67ec226410211cccf Mon Sep 17 00:00:00 2001
From: Max
Date: Thu, 8 Feb 2018 16:07:46 +0100
Subject: Crawler extracts volume, price per litre and abv. (bottleshop only)

---
 crawler/database.go | 21 ++++++++++++++++-----
 1 file changed, 16 insertions(+), 5 deletions(-)

(limited to 'crawler/database.go')

diff --git a/crawler/database.go b/crawler/database.go
index 741028f..b1d4e5e 100644
--- a/crawler/database.go
+++ b/crawler/database.go
@@ -24,8 +24,12 @@ func (app *App) createTables() error {
 		shop int,
 		name TEXT,
 		url TEXT,
+		short_url TEXT,
+		abv FLOAT(100,0),
+		volume FLOAT,
 		original_price INT,
 		discounted_price INT,
+		price_per_litre INT,
 		image_url TEXT,
 		spirit_type TEXT,
 		valid_until INT DEFAULT NULL,
@@ -37,7 +41,14 @@ func (app *App) createTables() error {
 		return err
 	}
 
-	query3 := `CREATE OR REPLACE VIEW angebote AS SELECT angebot.name,angebot.url,original_price, discounted_price,image_url,shop.name as shop, shop.url as shop_url, (original_price/discounted_price) AS quotient FROM angebot JOIN shop ON angebot.shop = shop.id WHERE spirit_type = "Whisky" AND original_price > 1998`
+	query3 := `CREATE OR REPLACE VIEW whisky_view AS
+		SELECT
+			angebot.id, angebot.name, angebot.abv, angebot.volume, angebot.url,original_price, discounted_price, angebot.price_per_litre, image_url,
+			shop.name as shop, shop.url as shop_url, (original_price/discounted_price) AS quotient 
+		FROM angebot
+		JOIN shop ON angebot.shop = shop.id
+		WHERE
+			spirit_type = "Whisky" AND original_price > 1998`
 	_, err = app.DB.Exec(query3)
 
 	return err
@@ -45,7 +56,7 @@ func (app *App) createTables() error {
 
 func (app *App) save_offer(W []Angebot) error {
 
-	query := `INSERT INTO angebot (shop, name, url, original_price, discounted_price, valid_until, image_url, spirit_type, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`
+	query := `INSERT INTO angebot (shop, name, url, abv, volume, original_price, discounted_price, price_per_litre, valid_until, image_url, spirit_type, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
 
 	stmt, err := app.DB.Prepare(query)
 	if err != nil {
@@ -61,7 +72,7 @@ func (app *App) save_offer(W []Angebot) error {
 			continue
 		}
 
-		err := app.DB.QueryRow("SELECT 1 FROM angebot WHERE shop = ? AND name = ? AND url = ? AND original_price = ? AND discounted_price = ? AND image_url = ? AND spirit_type = ?", o.Shop, o.Name, o.Url, o.Original_price, o.Discounted_price, o.Image_url, o.Spirit_type).Scan(&found)
+		err := app.DB.QueryRow("SELECT 1 FROM angebot WHERE shop = ? AND name = ? AND url = ? AND original_price = ? AND discounted_price = ? AND spirit_type = ?", o.Shop, o.Name, o.Url, o.Original_price, o.Discounted_price, o.Spirit_type).Scan(&found)
 
 		/*
 		 */
@@ -69,9 +80,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.Original_price, o.Discounted_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.Price_per_litre, sql.NullInt64{}, o.Image_url, o.Spirit_type, app.Now)
 			} else {
-				_, err = stmt.Exec(o.Shop, o.Name, o.Url, o.Original_price, o.Discounted_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.Price_per_litre, o.Valid_until, o.Image_url, o.Spirit_type, app.Now)
 			}
 			if err != nil {
 				return err
-- 
cgit v1.2.3