diff options
Diffstat (limited to 'crawler/database.go')
| -rw-r--r-- | crawler/database.go | 63 |
1 files changed, 59 insertions, 4 deletions
diff --git a/crawler/database.go b/crawler/database.go index 085f210..241d9f4 100644 --- a/crawler/database.go +++ b/crawler/database.go @@ -41,15 +41,70 @@ func (app *App) createTables() error { return err } - query3 := `CREATE OR REPLACE VIEW whisky_view AS + whisky_view := `CREATE OR REPLACE VIEW whisky_view AS SELECT - angebot.id, angebot.name, angebot.abv, angebot.volume, angebot.url,original_price, discounted_price, angebot.base_price, image_url, - shop.name as shop, shop.url as shop_url, (original_price/discounted_price) AS quotient + angebot.id, angebot.name, angebot.abv, angebot.volume, angebot.url,original_price/100 as original_price, discounted_price/100 as discounted_price, angebot.base_price, image_url, + shop.name as shop, shop.url as shop_url, ROUND(100-((discounted_price/original_price)*100)) AS procent FROM angebot JOIN shop ON angebot.shop = shop.id WHERE spirit_type = "Whisky" AND original_price > 1998` - _, err = app.DB.Exec(query3) + _, err = app.DB.Exec(whisky_view) + if err != nil { + return err + } + + wodka_view := `CREATE OR REPLACE VIEW wodka_view AS + SELECT + angebot.id, angebot.name, angebot.abv, angebot.volume, angebot.url,original_price/100 as original_price, discounted_price/100 as discounted_price, angebot.base_price, image_url, + shop.name as shop, shop.url as shop_url, ROUND(100-((discounted_price/original_price)*100)) AS procent + FROM angebot + JOIN shop ON angebot.shop = shop.id + WHERE + spirit_type = "Wodka"` + _, err = app.DB.Exec(wodka_view) + if err != nil { + return err + } + + gin_view := `CREATE OR REPLACE VIEW gin_view AS + SELECT + angebot.id, angebot.name, angebot.abv, angebot.volume, angebot.url,original_price/100 as original_price, discounted_price/100 as discounted_price, angebot.base_price, image_url, + shop.name as shop, shop.url as shop_url, ROUND(100-((discounted_price/original_price)*100)) AS procent + FROM angebot + JOIN shop ON angebot.shop = shop.id + WHERE + spirit_type = "gin"` + _, err = app.DB.Exec(gin_view) + if err != nil { + return err + } + + rum_view := `CREATE OR REPLACE VIEW rum_view AS + SELECT + angebot.id, angebot.name, angebot.abv, angebot.volume, angebot.url,original_price/100 as original_price, discounted_price/100 as discounted_price, angebot.base_price, image_url, + shop.name as shop, shop.url as shop_url, ROUND(100-((discounted_price/original_price)*100)) AS procent + FROM angebot + JOIN shop ON angebot.shop = shop.id + WHERE + spirit_type = "rum"` + _, err = app.DB.Exec(rum_view) + if err != nil { + return err + } + + misc_view := `CREATE OR REPLACE VIEW misc_view AS + SELECT + angebot.id, angebot.name, angebot.abv, angebot.volume, angebot.url,original_price/100 as original_price, discounted_price/100 as discounted_price, angebot.base_price, image_url, + shop.name as shop, shop.url as shop_url, ROUND(100-((discounted_price/original_price)*100)) AS procent + FROM angebot + JOIN shop ON angebot.shop = shop.id + WHERE + spirit_type = "Anderes"` + _, err = app.DB.Exec(misc_view) + if err != nil { + return err + } return err } |