contrib/benitlux/src/benitlux_db.nit

   1 # This file is part of NIT ( http://www.nitlanguage.org ).
   2 #
   3 # Copyright 2014 Alexis Laferrière <alexis.laf@xymus.net>
   4 #
   5 # Licensed under the Apache License, Version 2.0 (the "License");
   6 # you may not use this file except in compliance with the License.
   7 # You may obtain a copy of the License at
   8 #
   9 #     http://www.apache.org/licenses/LICENSE-2.0
  10 #
  11 # Unless required by applicable law or agreed to in writing, software
  12 # distributed under the License is distributed on an "AS IS" BASIS,
  13 # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  14 # See the License for the specific language governing permissions and
  15 # limitations under the License.
  16
  17 # Database interface to be used by the Web server and daily program
  18 module benitlux_db
  19
  20 import sqlite3
  21
  22 import benitlux_model
  23
  24 # The database of this project
  25 class DB
  26         super Sqlite3DB
  27
  28         redef init open(path)
  29         do
  30                 super
  31
  32                 create_tables
  33         end
  34
  35         # Create all tables for this project (IF NOT EXISTS)
  36         fun create_tables
  37         do
  38                 assert create_table("IF NOT EXISTS beers (name TEXT PRIMARY KEY, desc TEXT)") else
  39                         print error or else "?"
  40                 end
  41
  42                 assert create_table("IF NOT EXISTS daily (beer INTEGER, day DATE)") else
  43                         print error or else "?"
  44                 end
  45
  46                 assert create_table("IF NOT EXISTS subscribers (email TEXT UNIQUE PRIMARY KEY, joined DATETIME DEFAULT CURRENT_TIMESTAMP)") else
  47                         print error or else "?"
  48                 end
  49         end
  50
  51         # Update the DB with a all the `beers` available today
  52         #
  53         # Delete any other previous information for today.
  54         fun insert_beers_of_the_day(beers: HashSet[Beer])
  55         do
  56                 # Clean the DB of the previous beers of the day
  57                 assert execute("DELETE FROM daily WHERE day == date('now')") else
  58                         print error or else "?"
  59                 end
  60
  61                 # Add beer info
  62                 for beer in beers do
  63                         # Add meta if not there
  64                         assert execute("INSERT OR IGNORE INTO beers (name, desc) VALUES ({beer.name.to_sql_string}, {beer.desc.to_sql_string})") else
  65                                 print error or else "?"
  66                         end
  67
  68                         # Add day
  69                         assert execute("INSERT INTO daily (beer, day) VALUES (" +
  70                                        "(SELECT min(ROWID) FROM beers WHERE lower(name) = lower({beer.name.to_sql_string})), " +
  71                                                    "date('now'))") else
  72                                 print error or else "?"
  73                         end
  74                 end
  75         end
  76
  77         # Build and return a `BeerEvents` for today compared to the last weekday
  78         fun beer_events_today: BeerEvents
  79         do
  80                 var tm = new Tm.localtime
  81                 var last_weekday
  82                 if tm.wday == 1 then
  83                         # We're monday! we compare with the last friday
  84                         last_weekday = "date('now', 'weekday 6', '-7 day')"
  85                 else last_weekday = "date('now', '-1 day')"
  86
  87                 return beer_events_since(last_weekday).as(not null) # This is used by daily
  88         end
  89
  90         # Build and return a `BeerEvents` for today compared to `prev_day`
  91         #
  92         # Return `null` on error
  93         fun beer_events_since(prev_day: String): nullable BeerEvents
  94         do
  95                 var events = new BeerEvents
  96
  97                 # New
  98                 var stmt = select("name, desc FROM beers WHERE " +
  99                                   "ROWID IN (SELECT beer FROM daily WHERE day=(SELECT MAX(day) FROM daily)) AND " +
 100                                   "NOT ROWID IN (SELECT beer FROM daily WHERE date(day) = date({prev_day}))")
 101                 if stmt == null then return null
 102                 for row in stmt do events.new_beers.add row.to_beer
 103
 104                 # Gone
 105                 stmt = select("name, desc FROM beers WHERE " +
 106                                   "NOT ROWID IN (SELECT beer FROM daily WHERE day=(SELECT MAX(day) FROM daily)) AND " +
 107                                   "ROWID IN (SELECT beer FROM daily WHERE date(day) = date({prev_day}))")
 108                 if stmt == null then return null
 109                 for row in stmt do events.gone_beers.add row.to_beer
 110
 111                 # Fix
 112                 stmt = select("name, desc FROM beers WHERE " +
 113                                   "ROWID IN (SELECT beer FROM daily WHERE day=(SELECT MAX(day) FROM daily)) AND " +
 114                                   "ROWID IN (SELECT beer FROM daily WHERE date(day) = date({prev_day}))")
 115                 if stmt == null then return null
 116                 for row in stmt do events.fix_beers.add row.to_beer
 117
 118                 return events
 119         end
 120
 121         # List known beers
 122         #
 123         # Return `null` on error.
 124         fun beers: nullable Array[Beer]
 125         do
 126                 var stmt = select("name, desc FROM beers")
 127                 if stmt == null then return null
 128                 return [for row in stmt do row.to_beer]
 129         end
 130
 131         # Days where `beer` was available, all known days if `beer == null`
 132         #
 133         # Return `null` on error.
 134         fun days(beer: nullable Beer): nullable Array[String]
 135         do
 136                 var stmt
 137                 if beer == null then
 138                         stmt = select("DISTINCT day FROM daily")
 139                 else
 140                         stmt = select("""
 141 DISTINCT day FROM daily WHERE beer=(SELECT ROWID FROM beers WHERE name="{{{beer.name}}}")""")
 142                 end
 143
 144                 if stmt == null then return null
 145                 return [for row in stmt do row[0].to_s]
 146         end
 147
 148         # All the subscribers to the mailing list
 149         fun subscribers: Array[String]
 150         do
 151                 var subs = new Array[String]
 152                 for row in select("email FROM subscribers") do subs.add row[0].to_s
 153                 return subs
 154         end
 155
 156         # Add `email` to the mailing list subscribers
 157         fun subscribe(email: String)
 158         do
 159                 assert insert("OR IGNORE INTO subscribers (email) VALUES (lower({email.to_sql_string}))") else
 160                         print error or else "?"
 161                 end
 162         end
 163
 164         # Remove `email` to the mailing list subscribers
 165         fun unsubscribe(email: String)
 166         do
 167                 assert execute("DELETE FROM subscribers WHERE email = lower({email.to_sql_string})") else
 168                         print error or else "?"
 169                 end
 170         end
 171 end
 172
 173 redef class StatementRow
 174         # Convert this BD row to a `Beer`
 175         fun to_beer: Beer do return new Beer(self[0].to_s, self[1].to_s)
 176 end