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_sql(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                 prev_day = prev_day.to_sql_date_string
  97                 return beer_events_since_sql("date({prev_day})")
  98         end
  99
 100         # `BeerEvents` since the SQLite formatted date command `sql_date`
 101         #
 102         # Return `null` on error
 103         private fun beer_events_since_sql(sql_date: String): nullable BeerEvents
 104         do
 105                 var events = new BeerEvents
 106
 107                 # New
 108                 var stmt = select("ROWID, name, desc FROM beers WHERE " +
 109                                   "ROWID IN (SELECT beer FROM daily WHERE day=(SELECT MAX(day) FROM daily)) AND " +
 110                                   "NOT ROWID IN (SELECT beer FROM daily WHERE date(day) = {sql_date})")
 111                 if stmt == null then return null
 112                 for row in stmt do events.new_beers.add row.to_beer
 113
 114                 # Gone
 115                 stmt = select("ROWID, name, desc FROM beers WHERE " +
 116                               "NOT ROWID IN (SELECT beer FROM daily WHERE day=(SELECT MAX(day) FROM daily)) AND " +
 117                               "ROWID IN (SELECT beer FROM daily WHERE date(day) = {sql_date})")
 118                 if stmt == null then return null
 119                 for row in stmt do events.gone_beers.add row.to_beer
 120
 121                 # Fix
 122                 stmt = select("ROWID, name, desc FROM beers WHERE " +
 123                               "ROWID IN (SELECT beer FROM daily WHERE day=(SELECT MAX(day) FROM daily)) AND " +
 124                               "ROWID IN (SELECT beer FROM daily WHERE date(day) = {sql_date})")
 125                 if stmt == null then return null
 126                 for row in stmt do events.fix_beers.add row.to_beer
 127
 128                 return events
 129         end
 130
 131         # List known beers
 132         #
 133         # Return `null` on error.
 134         fun beers: nullable Array[Beer]
 135         do
 136                 var stmt = select("rowid, name, desc FROM beers")
 137                 if stmt == null then return null
 138                 return [for row in stmt do row.to_beer]
 139         end
 140
 141         # Load beer information from its database id
 142         fun beer_from_id(id: Int): nullable Beer
 143         do
 144                 var stmt = select("ROWID, name, desc FROM beers WHERE ROWID = {id}")
 145                 if stmt == null then return null
 146
 147                 var res = null
 148                 for row in stmt do
 149                         res = row.to_beer
 150                         break
 151                 end
 152                 return res
 153         end
 154
 155         # Days where `beer` was available, all known days if `beer == null`
 156         #
 157         # Return `null` on error.
 158         fun days(beer: nullable Beer): nullable Array[String]
 159         do
 160                 var stmt
 161                 if beer == null then
 162                         stmt = select("DISTINCT day FROM daily")
 163                 else
 164                         stmt = select("""
 165 DISTINCT day FROM daily WHERE beer=(SELECT ROWID FROM beers WHERE name="{{{beer.name}}}")""")
 166                 end
 167
 168                 if stmt == null then return null
 169                 return [for row in stmt do row[0].to_s]
 170         end
 171
 172         # All the subscribers to the mailing list
 173         fun subscribers: Array[String]
 174         do
 175                 var subs = new Array[String]
 176                 for row in select("email FROM subscribers") do subs.add row[0].to_s
 177                 return subs
 178         end
 179
 180         # Add `email` to the mailing list subscribers
 181         fun subscribe(email: String)
 182         do
 183                 assert insert("OR IGNORE INTO subscribers (email) VALUES (lower({email.to_sql_string}))") else
 184                         print error or else "?"
 185                 end
 186         end
 187
 188         # Remove `email` to the mailing list subscribers
 189         fun unsubscribe(email: String)
 190         do
 191                 assert execute("DELETE FROM subscribers WHERE email = lower({email.to_sql_string})") else
 192                         print error or else "?"
 193                 end
 194         end
 195 end
 196
 197 redef class StatementRow
 198         # Convert this BD row to a `Beer`
 199         fun to_beer: Beer do return new Beer(self[0].to_i, self[1].to_s, self[2].to_s)
 200 end