Adds referential integrity.

1 files changed, 18 insertions, 17 deletions

diff --git a/database.go b/database.go
index d5d59de..a6145bb 100644
--- a/database.go
+++ b/database.go
@@ -6,30 +6,31 @@ import (
 )
 
 func (app *App) createTables() error {
-	query1 := `CREATE TABLE IF NOT EXISTS angebot (
+	query1 := `CREATE TABLE IF NOT EXISTS shop(
 		id INTEGER PRIMARY KEY AUTO_INCREMENT,
-		shop TEXT,
-		name TEXT,
-		url TEXT,
-		original_price INT,
-		discounted_price INT,
-		image_url TEXT,
-		spirit_type TEXT,
-		valid_until INT DEFAULT NULL,
-		created_at INT
+		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 shop(
+	query2 := `CREATE TABLE IF NOT EXISTS angebot (
 		id INTEGER PRIMARY KEY AUTO_INCREMENT,
-		name text,
-		url text,
-		logo_url text,
-		shipping_costs text,
-		free_shipping text
+		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
@@ -108,7 +109,7 @@ func (app *App) remove_expired(W []Angebot) error {
 
 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.Url == offer_db.Url && v.Original_price == offer_db.Original_price && v.Discounted_price == offer_db.Discounted_price {
+		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)