diff options
| -rw-r--r-- | config.go | 15 | ||||
| -rw-r--r-- | database.go | 129 | ||||
| -rw-r--r-- | db.go | 64 | ||||
| -rw-r--r-- | main.go | 81 | ||||
| -rw-r--r-- | whiskyde.go | 2 |
5 files changed, 171 insertions, 120 deletions
@@ -9,11 +9,13 @@ type Config struct { DBDriver string DBDBName string DBHost string - DBPort int + DBPort string DBUser string DBPassword string DBOptions string DBPath string // for sqlite + + Debug bool } // Parses the configuration and sets the configuration struct. @@ -21,11 +23,13 @@ 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("DBHost", "localhost") + viper.SetDefault("DBPort", "3306") viper.SetDefault("DBPath", "./alkobote.db") + viper.SetDefault("Debug", false) + // Name of the configuration file viper.SetConfigName("config") @@ -62,10 +66,11 @@ func (c *Config) parseConfig(configFile string) { func (c *Config) setsConfig() { c.DBDriver = viper.GetString("DBDriver") c.DBHost = viper.GetString("DBHost") - c.DBPort = viper.GetInt("DBPort") + c.DBPort = viper.GetString("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") + c.DBPath = viper.GetString("DBPath") + c.Debug = viper.GetBool("Debug") } diff --git a/database.go b/database.go new file mode 100644 index 0000000..d5d59de --- /dev/null +++ b/database.go @@ -0,0 +1,129 @@ +package main + +import ( + "database/sql" + "log" +) + +func (app *App) createTables() error { + query1 := `CREATE TABLE IF NOT EXISTS angebot ( + 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 + )` + _, err := app.DB.Exec(query1) + if err != nil { + return err + } + + query2 := `CREATE TABLE IF NOT EXISTS shop( + id INTEGER PRIMARY KEY AUTO_INCREMENT, + 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, 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.Url == offer_db.Url && 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 +} @@ -1,64 +0,0 @@ -package main - -import ( - // "github.com/jmoiron/sqlx" - "database/sql" -) - -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 -} @@ -4,10 +4,11 @@ import ( "encoding/json" "fmt" "log" + "time" _ "database/sql" _ "github.com/go-sql-driver/mysql" - _ "github.com/mattn/go-sqlite3" + //_ "github.com/mattn/go-sqlite3" "github.com/jmoiron/sqlx" ) @@ -17,9 +18,12 @@ type App struct { Shops []Shop Config *Config DB *sqlx.DB + Now int64 + Debug bool } type Angebot struct { + Id int Name string Shop string Url string @@ -27,7 +31,7 @@ type Angebot struct { Discounted_price int Image_url string Spirit_type string - Valid_until string + Valid_until int } type Shop struct { @@ -45,79 +49,43 @@ func main() { app := App{Config: &Config{}} app.Config.parseConfig("") + app.Now = time.Now().Unix() + 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") + app.DB, err = sqlx.Connect(app.Config.DBDriver, app.Config.DBPath) } 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) + if app.Config.Debug { + log.Println(app.Config.DBUser + ":" + app.Config.DBPassword + "@tcp(" + app.Config.DBHost + ":" + app.Config.DBPort + ")/" + app.Config.DBDBName + app.Config.DBOptions) + } + app.DB, err = sqlx.Connect(app.Config.DBDriver, app.Config.DBUser+":"+app.Config.DBPassword+"@tcp("+app.Config.DBHost+":"+app.Config.DBPort+")/"+app.Config.DBDBName+app.Config.DBOptions) } - printName(W, "Rum und Co") + defer app.DB.Close() - W = ScrapeWhic() - err = app.save_offer(W) if err != nil { log.Fatal(err) } - printName(W, "Whic") - W = ScrapeWhiskyde() - err = app.save_offer(W) + err = app.createTables() 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 := ScrapeHTML() - W = ScrapeWhiskyworld() err = app.save_offer(W) if err != nil { log.Fatal(err) } - printName(W, "Whiskyworld") - - W = ScrapeWhiskyzone() - err = app.save_offer(W) + err = app.remove_expired(W) if err != nil { log.Fatal(err) } - printName(W, "Whiskyzone") } func printName(W []Angebot, name string) { + return fmt.Println("-------------------") fmt.Println("Sonderangebote von " + name) fmt.Println("-------------------") @@ -129,3 +97,16 @@ func printName(W []Angebot, name string) { fmt.Println(string(output)) } + +func ScrapeHTML() []Angebot { + W := ScrapeBottleWord() + W = append(W, ScrapeMCWhisky()...) + W = append(W, ScrapeRumundCo()...) + W = append(W, ScrapeWhic()...) + W = append(W, ScrapeWhiskyde()...) + W = append(W, ScrapeWhiskysitenl()...) + W = append(W, ScrapeWhiskyworld()...) + W = append(W, ScrapeWhiskyzone()...) + + return W +} diff --git a/whiskyde.go b/whiskyde.go index 62e30f1..5bf3248 100644 --- a/whiskyde.go +++ b/whiskyde.go @@ -43,7 +43,7 @@ func ScrapeWhiskyde() []Angebot { }) e.ForEach(".article-price-prefix", func(i int, e *colly.HTMLElement) { - W.Valid_until = e.ChildText(".article-price-special") + //W.Valid_until = e.ChildText(".article-price-special") }) W.Shop = "Whisky.de" |