From 91c901a764dbf2d600366ed1d8ee19c813d3047d Mon Sep 17 00:00:00 2001
From: Max
Date: Mon, 5 Feb 2018 02:54:46 +0100
Subject: Adds some database code.

---
 config.go | 17 +++++++++++++++++
 db.go     | 60 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
 main.go   | 47 +++++++++++++++++++++++++++++++++++++++++++++--
 3 files changed, 120 insertions(+), 4 deletions(-)

diff --git a/config.go b/config.go
index f877eba..30387a3 100644
--- a/config.go
+++ b/config.go
@@ -6,21 +6,26 @@ import (
 )
 
 type Config struct {
+	DBDriver   string
 	DBDBName   string
 	DBHost     string
 	DBPort     int
 	DBUser     string
 	DBPassword string
 	DBOptions  string
+	DBPath     string // for sqlite
 }
 
 // Parses the configuration and sets the configuration struct.
 func (c *Config) parseConfig(configFile string) {
 
+	viper.SetDefault("DBDriver", "mysql")
 	viper.SetDefault("DBDBName", "alkobote")
 	viper.SetDefault("DBHost", "127.0.0.1")
 	viper.SetDefault("DBPort", 3306)
 
+	viper.SetDefault("DBPath", "./alkobote.db")
+
 	// Name of the configuration file
 	viper.SetConfigName("config")
 
@@ -52,3 +57,15 @@ func (c *Config) parseConfig(configFile string) {
 
 	c.setsConfig()
 }
+
+// Actually sets the config struct
+func (c *Config) setsConfig() {
+	c.DBDriver = viper.GetString("DBDriver")
+	c.DBHost = viper.GetString("DBHost")
+	c.DBPort = viper.GetInt("DBPort")
+	c.DBUser = viper.GetString("DBUser")
+	c.DBPassword = viper.GetString("DBPassword")
+	c.DBDBName = viper.GetString("DBDBName")
+	c.DBOptions = viper.GetString("DBOptions")
+	c.DBOptions = viper.GetString("DBPath")
+}
diff --git a/db.go b/db.go
index f2f819d..8ea7165 100644
--- a/db.go
+++ b/db.go
@@ -1,8 +1,64 @@
 package main
 
 import (
-	"github.com/jmoiron/sqlx"
+	//	"github.com/jmoiron/sqlx"
+	"database/sql"
 )
 
-func save_offer(W []Angebot) {
+func (app *App) createTables() error {
+	query1 := `CREATE TABLE IF NOT EXISTS angebot (
+		id INTEGER PRIMARY KEY AUTOINCREMENT,
+		shop text,
+		name text,
+		url text,
+		original_price int,
+		discounted_price int,
+		valid_until text,
+		image_url text,
+		spirit_type text,
+		created_at text
+	)`
+	_, err := app.DB.Exec(query1)
+	if err != nil {
+		return err
+	}
+
+	query2 := `CREATE TABLE IF NOT EXISTS shop(
+		id INTEGER PRIMARY KEY AUTOINCREMENT,
+		name text,
+		url text,
+		logo_url text,
+		shipping_costs text,
+		free_shipping text
+	)`
+	_, err = app.DB.Exec(query2)
+	return err
+}
+
+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) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`
+
+	stmt, err := app.DB.Prepare(query)
+	if err != nil {
+		return err
+	}
+
+	for _, o := range W {
+
+		var found int
+
+		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)
+
+		if err == sql.ErrNoRows {
+
+			_, err = stmt.Exec(o.Shop, o.Name, o.Url, o.Original_price, o.Discounted_price, o.Valid_until, o.Image_url, o.Spirit_type)
+			if err != nil {
+				return err
+			}
+
+		}
+	}
+
+	return nil
 }
diff --git a/main.go b/main.go
index 94f1e3a..beb3aa1 100644
--- a/main.go
+++ b/main.go
@@ -7,6 +7,8 @@ import (
 
 	_ "database/sql"
 	_ "github.com/go-sql-driver/mysql"
+	_ "github.com/mattn/go-sqlite3"
+
 	"github.com/jmoiron/sqlx"
 )
 
@@ -43,34 +45,75 @@ func main() {
 	app := App{Config: &Config{}}
 	app.Config.parseConfig("")
 
-	// Hard coded mysql driver.
-	app.DB, err = sqlx.Connect("mysql", app.Config.DBUser+":"+app.Config.DBPassword+"@"+app.Config.DBHost+"/"+app.Config.DBDBName+app.Config.DBOptions)
+	if "sqlite3" == app.Config.DBDriver {
+		//app.DB, err = sqlx.Connect(app.Config.DBDriver, app.Config.DBPath)
+		app.DB, err = sqlx.Connect(app.Config.DBDriver, "./alkobote.db")
+	} else {
+		app.DB, err = sqlx.Connect(app.Config.DBDriver, app.Config.DBUser+":"+app.Config.DBPassword+"@"+app.Config.DBHost+"/"+app.Config.DBDBName+app.Config.DBOptions)
+	}
+	if err != nil {
+		log.Fatal(err)
+	}
+
+	err = app.createTables()
 	if err != nil {
 		log.Fatal(err)
 	}
 
 	W := ScrapeBottleWord()
+	err = app.save_offer(W)
+	if err != nil {
+		log.Fatal(err)
+	}
 	printName(W, "BottleWorld")
 
 	W = ScrapeMCWhisky()
+	err = app.save_offer(W)
+	if err != nil {
+		log.Fatal(err)
+	}
 	printName(W, "MC Whisky")
 
 	W = ScrapeRumundCo()
+	err = app.save_offer(W)
+	if err != nil {
+		log.Fatal(err)
+	}
 	printName(W, "Rum und Co")
 
 	W = ScrapeWhic()
+	err = app.save_offer(W)
+	if err != nil {
+		log.Fatal(err)
+	}
 	printName(W, "Whic")
 
 	W = ScrapeWhiskyde()
+	err = app.save_offer(W)
+	if err != nil {
+		log.Fatal(err)
+	}
 	printName(W, "Whisky.de")
 
 	W = ScrapeWhiskysitenl()
+	err = app.save_offer(W)
+	if err != nil {
+		log.Fatal(err)
+	}
 	printName(W, "Whiskysite.nl")
 
 	W = ScrapeWhiskyworld()
+	err = app.save_offer(W)
+	if err != nil {
+		log.Fatal(err)
+	}
 	printName(W, "Whiskyworld")
 
 	W = ScrapeWhiskyzone()
+	err = app.save_offer(W)
+	if err != nil {
+		log.Fatal(err)
+	}
 	printName(W, "Whiskyzone")
 }
 
-- 
cgit v1.2.3