More efficient view code.

1 files changed, 17 insertions, 58 deletions

diff --git a/crawler/database.go b/crawler/database.go
index 241d9f4..f2abb4f 100644
--- a/crawler/database.go
+++ b/crawler/database.go
@@ -11,7 +11,7 @@ func (app *App) createTables() error {
 		name varchar(255) UNIQUE,
 		url varchar(255) UNIQUE,
 		logo_url text,
-		shipping_costs text,
+		shipping_costs int,
 		free_shipping text
 	)`
 	_, err := app.DB.Exec(query1)
@@ -41,69 +41,28 @@ func (app *App) createTables() error {
 		return err
 	}
 
-	whisky_view := `CREATE OR REPLACE VIEW whisky_view AS
-		SELECT
-			angebot.id, angebot.name, angebot.abv, angebot.volume, angebot.url,original_price/100 as original_price, discounted_price/100 as discounted_price, angebot.base_price, image_url,
-			shop.name as shop, shop.url as shop_url, ROUND(100-((discounted_price/original_price)*100)) AS procent 
-		FROM angebot
-		JOIN shop ON angebot.shop = shop.id
-		WHERE
-			spirit_type = "Whisky" AND original_price > 1998`
-	_, err = app.DB.Exec(whisky_view)
-	if err != nil {
-		return err
-	}
-
-	wodka_view := `CREATE OR REPLACE VIEW wodka_view AS
-		SELECT
-			angebot.id, angebot.name, angebot.abv, angebot.volume, angebot.url,original_price/100 as original_price, discounted_price/100 as discounted_price, angebot.base_price, image_url,
-			shop.name as shop, shop.url as shop_url, ROUND(100-((discounted_price/original_price)*100)) AS procent 
-		FROM angebot
-		JOIN shop ON angebot.shop = shop.id
-		WHERE
-			spirit_type = "Wodka"`
-	_, err = app.DB.Exec(wodka_view)
-	if err != nil {
-		return err
-	}
-
-	gin_view := `CREATE OR REPLACE VIEW gin_view AS
-		SELECT
-			angebot.id, angebot.name, angebot.abv, angebot.volume, angebot.url,original_price/100 as original_price, discounted_price/100 as discounted_price, angebot.base_price, image_url,
-			shop.name as shop, shop.url as shop_url, ROUND(100-((discounted_price/original_price)*100)) AS procent 
-		FROM angebot
-		JOIN shop ON angebot.shop = shop.id
-		WHERE
-			spirit_type = "gin"`
-	_, err = app.DB.Exec(gin_view)
-	if err != nil {
-		return err
-	}
+	views := []string{"whisky", "wodka", "rum", "gin", "misc"}
 
-	rum_view := `CREATE OR REPLACE VIEW rum_view AS
-		SELECT
-			angebot.id, angebot.name, angebot.abv, angebot.volume, angebot.url,original_price/100 as original_price, discounted_price/100 as discounted_price, angebot.base_price, image_url,
-			shop.name as shop, shop.url as shop_url, ROUND(100-((discounted_price/original_price)*100)) AS procent 
-		FROM angebot
-		JOIN shop ON angebot.shop = shop.id
-		WHERE
-			spirit_type = "rum"`
-	_, err = app.DB.Exec(rum_view)
-	if err != nil {
-		return err
-	}
+	for _, v := range views {
+		var spirit_type string
+		if v == "misc" {
+			spirit_type = "Anderes"
+		} else {
+			spirit_type = v
+		}
 
-	misc_view := `CREATE OR REPLACE VIEW misc_view AS
+		view_query := `CREATE OR REPLACE VIEW ` + v + `_view AS
 		SELECT
-			angebot.id, angebot.name, angebot.abv, angebot.volume, angebot.url,original_price/100 as original_price, discounted_price/100 as discounted_price, angebot.base_price, image_url,
-			shop.name as shop, shop.url as shop_url, ROUND(100-((discounted_price/original_price)*100)) AS procent 
+			angebot.id, angebot.name, angebot.abv, angebot.volume, angebot.url,original_price/100 as original_price, discounted_price/100 as discounted_price, angebot.base_price/100 as base_price, image_url,
+			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 = "Anderes"`
-	_, err = app.DB.Exec(misc_view)
-	if err != nil {
-		return err
+			spirit_type = "` + spirit_type + `"`
+		_, err = app.DB.Exec(view_query)
+		if err != nil {
+			return err
+		}
 	}
 
 	return err