Messing with database constraints. (crawler)

1 files changed, 29 insertions, 33 deletions

diff --git a/crawler/database.go b/crawler/database.go
index 557f491..41ef4f0 100644
--- a/crawler/database.go
+++ b/crawler/database.go
@@ -2,6 +2,7 @@ package main
 
 import (
 	"database/sql"
+	"fmt"
 	"strings"
 
 	log "github.com/Sirupsen/logrus"
@@ -10,9 +11,9 @@ import (
 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,
-		short_url TEXT,
+		name varchar(255) UNIQUE NOT NULL,
+		url varchar(255) UNIQUE NOT NULL,
+		short_url varchar(255) UNIQUE,
 		logo_url text,
 		shipping_costs int,
 		free_shipping text
@@ -24,22 +25,24 @@ func (app *App) createTables() error {
 
 	query2 := `CREATE TABLE IF NOT EXISTS angebot (
 		id INTEGER PRIMARY KEY AUTO_INCREMENT,
-		shop int,
-		name TEXT,
-		url TEXT,
+		shop INT NOT NULL,
+		name VARCHAR(255) NOT NULL,
+		url VARCHAR(255) NOT NULL,
 		short_url TEXT,
-		abv DECIMAL(10,2),
-		volume DECIMAL(10,2),
-		original_price INT,
-		discounted_price INT,
-		base_price INT,
+		abv DECIMAL(10,2) NOT NULL,
+		volume DECIMAL(10,2) NOT NULL,
+		original_price INT NOT NULL,
+		discounted_price INT NOT NULL,
+		base_price INT NOT NULL,
 		image_url TEXT,
-		spirit_type TEXT,
+		spirit_type TEXT NOT NULL,
 		website TEXT,
-		website_raw TEXT,
-		valid_until INT DEFAULT NULL,
+		website_raw TEXT NOT NULL,
+		valid_until INT NOT NULL DEFAULT 0,
 		created_at INT,
-		FOREIGN KEY(shop) REFERENCES shop(id)
+		FOREIGN KEY(shop) REFERENCES shop(id),
+		UNIQUE(name,shop,volume,abv,original_price,discounted_price,valid_until),
+		UNIQUE(name,shop,url,created_at)
 	)`
 	_, err = app.DB.Exec(query2)
 	if err != nil {
@@ -67,7 +70,7 @@ func (app *App) createTables() error {
 		FROM angebot
 		JOIN shop ON angebot.shop = shop.id
 		WHERE 
-			` + spirit_type + ` (valid_until IS NULL OR valid_until > (SELECT UNIX_TIMESTAMP()))`
+			` + spirit_type + ` (valid_until = 0 OR valid_until > (SELECT UNIX_TIMESTAMP()))`
 		_, err = app.DB.Exec(view_query)
 		if err != nil {
 			return err
@@ -103,15 +106,12 @@ 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.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.Website, 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 {
 				o.error_msg = err.Error()
 				o.error_ctx = "Save Offer: Inserting offer failed"
-				DebugOffer(o, "Save Offer: Inserting offer failed")
+				WarnOffer(o, "Save Offer: Inserting offer failed")
 				return err
 			}
 
@@ -124,8 +124,8 @@ func (app *App) save_offer(W []Angebot) error {
 
 func (app *App) remove_expired(W []Angebot, shop Shop) error {
 
-	query := `SELECT id, shop, name, url, original_price, discounted_price FROM angebot WHERE shop = ? AND created_at < ?
-		AND (valid_until IS NULL OR valid_until > ?)`
+	query := `SELECT id, name, shop, volume, abv, original_price, discounted_price FROM angebot WHERE shop = ? AND created_at < ?
+		AND (valid_until = 0 OR valid_until > ?)`
 
 	rows, err := app.DB.Queryx(query, shop.Id, app.Now, app.Now)
 	if err != nil {
@@ -148,7 +148,9 @@ func (app *App) remove_expired(W []Angebot, shop Shop) error {
 			expire_query := `UPDATE angebot SET valid_until = ? WHERE id = ?`
 			_, err = app.DB.Exec(expire_query, app.Now, offer_db.Id)
 			if err != nil {
-				Debug(err, "Remove expired: Update failed")
+				offer_db.error_msg = err.Error()
+				offer_db.error_ctx = fmt.Sprintf("UPDATE angebot SET valid_until = %d WHERE id = %d", app.Now, offer_db.Id)
+				WarnOffer(offer_db, "Remove expired: Update failed")
 				return err
 			}
 		}
@@ -164,19 +166,13 @@ 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 {
-			}
+		// resembles UNIQUE constraint
+		if (v.Name == offer_db.Name) && (v.Shop == offer_db.Shop) && (v.Volume == offer_db.Volume) && (v.Abv == offer_db.Abv) && (v.Original_price == offer_db.Original_price) && (v.Discounted_price == offer_db.Discounted_price) && (v.Valid_until == offer_db.Valid_until) {
 
 			return true
 
 		}
 	}
 
-	if app.Config.Debug {
-		log.Debug("Contains not: " + offer_db.Name)
-	}
-
 	return false
 }