1 files changed, 130 insertions, 0 deletions
diff --git a/crawler/database.go b/crawler/database.go
new file mode 100644
index 0000000..a6145bb
--- /dev/null
+++ b/crawler/database.go
@@ -0,0 +1,130 @@
+package main
+
+import (
+	"database/sql"
+	"log"
+)
+
+func (app *App) createTables() error {
+	query1 := `CREATE TABLE IF NOT EXISTS shop(
+		id INTEGER PRIMARY KEY AUTO_INCREMENT,
+		name varchar(255) UNIQUE,
+		url varchar(255) UNIQUE,
+		logo_url text,
+		shipping_costs text,
+		free_shipping text
+	)`
+	_, err := app.DB.Exec(query1)
+	if err != nil {
+		return err
+	}
+
+	query2 := `CREATE TABLE IF NOT EXISTS angebot (
+		id INTEGER PRIMARY KEY AUTO_INCREMENT,
+		shop int,
+		name TEXT,
+		url TEXT,
+		original_price INT,
+		discounted_price INT,
+		image_url TEXT,
+		spirit_type TEXT,
+		valid_until INT DEFAULT NULL,
+		created_at INT,
+		FOREIGN KEY(shop) REFERENCES shop(id)
+	)`
+	_, 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, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`
+
+	stmt, err := app.DB.Prepare(query)
+	if err != nil {
+		return err
+	}
+	defer stmt.Close()
+
+	for _, o := range W {
+
+		var found int
+
+		if o.Discounted_price == 0 || o.Original_price == 0 {
+			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)
+
+		/*
+		 */
+
+		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)
+			} 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)
+			}
+			if err != nil {
+				return err
+			}
+
+		}
+	}
+
+	//return app.remove_expired(W)
+	return nil
+}
+
+func (app *App) remove_expired(W []Angebot) error {
+
+	query := `SELECT id, shop, name, url, original_price, discounted_price FROM angebot WHERE created_at < ? AND valid_until IS NULL`
+
+	rows, err := app.DB.Queryx(query, app.Now)
+	if err != nil {
+		return err
+	}
+	defer rows.Close()
+
+	for rows.Next() {
+		var offer_db Angebot
+		err = rows.StructScan(&offer_db)
+
+		if err != nil {
+			return err
+		}
+
+		if !app.offer_contains(W, offer_db) {
+			expire_query := `UPDATE angebot SET valid_until = ? WHERE id = ?`
+			_, err = app.DB.Exec(expire_query, app.Now, offer_db.Id)
+			if err != nil {
+				return err
+			}
+		}
+	}
+
+	return nil
+}
+
+func (app *App) offer_contains(W []Angebot, offer_db Angebot) bool {
+	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 app.Config.Debug {
+				log.Println("Contains: " + v.Name)
+				log.Println("")
+			}
+
+			return true
+
+		}
+	}
+
+	if app.Config.Debug {
+		log.Println("Contains not: " + offer_db.Name)
+		log.Println("")
+	}
+
+	return false
+}