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 BenitluxDB
  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 error or else "?"
  40                 end
  41
  42                 # Beers availability on each day
  43                 assert create_table("IF NOT EXISTS daily (beer INTEGER, day DATE)") else
  44                         print_error error or else "?"
  45                 end
  46
  47                 assert create_table("IF NOT EXISTS subscribers (email TEXT UNIQUE PRIMARY KEY, joined DATETIME DEFAULT CURRENT_TIMESTAMP)") else
  48                         print_error error or else "?"
  49                 end
  50         end
  51
  52         # Update the DB with a all the `beers` available today
  53         #
  54         # Delete any other previous information for today.
  55         fun insert_beers_of_the_day(beers: HashSet[Beer])
  56         do
  57                 # Clean the DB of the previous beers of the day
  58                 assert execute("DELETE FROM daily WHERE day == date('now')") else
  59                         print error or else "?"
  60                 end
  61
  62                 # Add beer info
  63                 for beer in beers do
  64                         # Add meta if not there
  65                         assert execute("INSERT OR IGNORE INTO beers (name, desc) VALUES ({beer.name.to_sql_string}, {beer.desc.to_sql_string})") else
  66                                 print error or else "?"
  67                         end
  68
  69                         # Add day
  70                         assert execute("INSERT INTO daily (beer, day) VALUES (" +
  71                                        "(SELECT min(ROWID) FROM beers WHERE lower(name) = lower({beer.name.to_sql_string})), " +
  72                                                    "date('now'))") else
  73                                 print error or else "?"
  74                         end
  75                 end
  76         end
  77
  78         # Build and return a `BeerEvents` for today compared to the last weekday
  79         fun beer_events_today: nullable BeerEvents
  80         do
  81                 var tm = new Tm.localtime
  82                 var last_weekday
  83                 if tm.wday == 1 then
  84                         # We're monday! we compare with the last friday
  85                         last_weekday = "date('now', 'weekday 6', '-7 day')"
  86                 else last_weekday = "date('now', '-1 day')"
  87
  88                 return beer_events_since(last_weekday)
  89         end
  90
  91         # Build and return a `BeerEvents` for today compared to `prev_day`
  92         #
  93         # Return `null` on error
  94         fun beer_events_since(prev_day: String): nullable BeerEvents
  95         do
  96                 var events = new BeerEvents
  97
  98                 # New
  99                 var stmt = select("ROWID, name, desc FROM beers WHERE " +
 100                                   "ROWID IN (SELECT beer FROM daily WHERE day=(SELECT MAX(day) FROM daily)) AND " +
 101                                   "NOT ROWID IN (SELECT beer FROM daily WHERE date(day) = date({prev_day}))")
 102                 if stmt == null then return null
 103                 for row in stmt do events.new_beers.add row.to_beer
 104
 105                 # Gone
 106                 stmt = select("ROWID, name, desc FROM beers WHERE " +
 107                               "NOT ROWID IN (SELECT beer FROM daily WHERE day=(SELECT MAX(day) FROM daily)) AND " +
 108                               "ROWID IN (SELECT beer FROM daily WHERE date(day) = date({prev_day}))")
 109                 if stmt == null then return null
 110                 for row in stmt do events.gone_beers.add row.to_beer
 111
 112                 # Fix
 113                 stmt = select("ROWID, name, desc FROM beers WHERE " +
 114                               "ROWID IN (SELECT beer FROM daily WHERE day=(SELECT MAX(day) FROM daily)) AND " +
 115                               "ROWID IN (SELECT beer FROM daily WHERE date(day) = date({prev_day}))")
 116                 if stmt == null then return null
 117                 for row in stmt do events.fix_beers.add row.to_beer
 118
 119                 return events
 120         end
 121
 122         # List known beers
 123         #
 124         # Return `null` on error.
 125         fun beers: nullable Array[Beer]
 126         do
 127                 var stmt = select("rowid, name, desc FROM beers")
 128                 if stmt == null then return null
 129                 return [for row in stmt do row.to_beer]
 130         end
 131
 132         # Load beer information from its database id
 133         fun beer_from_id(id: Int): nullable Beer
 134         do
 135                 var stmt = select("ROWID, name, desc FROM beers WHERE ROWID = {id}")
 136                 if stmt == null then return null
 137
 138                 var res = null
 139                 for row in stmt do
 140                         res = row.to_beer
 141                         break
 142                 end
 143                 return res
 144         end
 145
 146         # Days where `beer` was available, all known days if `beer == null`
 147         #
 148         # Return `null` on error.
 149         fun days(beer: nullable Beer): nullable Array[String]
 150         do
 151                 var stmt
 152                 if beer == null then
 153                         stmt = select("DISTINCT day FROM daily")
 154                 else
 155                         stmt = select("""
 156 DISTINCT day FROM daily WHERE beer=(SELECT ROWID FROM beers WHERE name="{{{beer.name}}}")""")
 157                 end
 158
 159                 if stmt == null then return null
 160                 return [for row in stmt do row[0].to_s]
 161         end
 162
 163         # All the subscribers to the mailing list
 164         fun subscribers: Array[String]
 165         do
 166                 var subs = new Array[String]
 167                 for row in select("email FROM subscribers") do subs.add row[0].to_s
 168                 return subs
 169         end
 170
 171         # Add `email` to the mailing list subscribers
 172         fun subscribe(email: String)
 173         do
 174                 assert insert("OR IGNORE INTO subscribers (email) VALUES (lower({email.to_sql_string}))") else
 175                         print error or else "?"
 176                 end
 177         end
 178
 179         # Remove `email` to the mailing list subscribers
 180         fun unsubscribe(email: String)
 181         do
 182                 assert execute("DELETE FROM subscribers WHERE email = lower({email.to_sql_string})") else
 183                         print error or else "?"
 184                 end
 185         end
 186 end
 187
 188 redef class StatementRow
 189         # Convert this BD row to a `Beer`
 190         fun to_beer: Beer do return new Beer(self[0].to_i, self[1].to_s, self[2].to_s)
 191 end