diff options
| -rw-r--r-- | crawler/database.go | 37 |
1 files changed, 23 insertions, 14 deletions
diff --git a/crawler/database.go b/crawler/database.go index 3d5b1bd..a94e2f0 100644 --- a/crawler/database.go +++ b/crawler/database.go @@ -3,6 +3,7 @@ package main import ( "database/sql" "log" + "strings" ) func (app *App) createTables() error { @@ -25,13 +26,15 @@ func (app *App) createTables() error { name TEXT, url TEXT, short_url TEXT, - abv FLOAT(100,0), - volume FLOAT, + abv DECIMAL(10,2), + volume DECIMAL(10,2), original_price INT, discounted_price INT, base_price INT, image_url TEXT, spirit_type TEXT, + website TEXT, + website_raw TEXT, valid_until INT DEFAULT NULL, created_at INT, FOREIGN KEY(shop) REFERENCES shop(id) @@ -41,14 +44,16 @@ func (app *App) createTables() error { return err } - views := []string{"whisky", "wodka", "rum", "gin", "misc"} + views := []string{"whisky", "wodka", "rum", "gin", "misc", "all"} for _, v := range views { var spirit_type string if v == "misc" { - spirit_type = "Verschiedenes" + spirit_type = `spirit_type = "` + v + `"` + } else if v == "all" { + spirit_type = "" } else { - spirit_type = v + spirit_type = `spirit_type NOT IN ("` + strings.Join(views, `", "`) + `")` } view_query := `CREATE OR REPLACE VIEW ` + v + `_view AS @@ -57,8 +62,8 @@ func (app *App) createTables() error { 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 = "` + spirit_type + `"` + WHERE 1 = 1 + ` + spirit_type + ` AND (valid_until IS NULL OR valid_until > (SELECT UNIX_TIMESTAMP()))` _, err = app.DB.Exec(view_query) if err != nil { return err @@ -70,7 +75,7 @@ func (app *App) createTables() error { func (app *App) save_offer(W []Angebot) error { - query := `INSERT INTO angebot (shop, name, url, abv, volume, original_price, discounted_price, base_price, valid_until, image_url, spirit_type, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)` + query := `INSERT INTO angebot (shop, name, url, abv, volume, original_price, discounted_price, base_price, valid_until, image_url, website_raw, spirit_type, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)` stmt, err := app.DB.Prepare(query) if err != nil { @@ -94,9 +99,9 @@ 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.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, 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.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 { return err @@ -109,11 +114,11 @@ func (app *App) save_offer(W []Angebot) error { return nil } -func (app *App) remove_expired(W []Angebot) error { +func (app *App) remove_expired(W []Angebot, shop_id int) error { - query := `SELECT id, shop, name, url, original_price, discounted_price FROM angebot WHERE created_at < ? AND valid_until IS NULL` + query := `SELECT id, shop, name, url, original_price, discounted_price FROM angebot WHERE shop = ? AND created_at < ? AND valid_until IS NULL` - rows, err := app.DB.Queryx(query, app.Now) + rows, err := app.DB.Queryx(query, shop_id, app.Now) if err != nil { return err } @@ -140,8 +145,12 @@ func (app *App) remove_expired(W []Angebot) error { } func (app *App) offer_contains(W []Angebot, offer_db Angebot) bool { + if len(W) < 1 { + return false + } + 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 (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) |