import benitlux_model
# The database of this project
-class DB
+class BenitluxDB
super Sqlite3DB
redef init open(path)
fun create_tables
do
assert create_table("IF NOT EXISTS beers (name TEXT PRIMARY KEY, desc TEXT)") else
- print error or else "?"
+ print_error error or else "?"
end
+ # Beers availability on each day
assert create_table("IF NOT EXISTS daily (beer INTEGER, day DATE)") else
- print error or else "?"
+ print_error error or else "?"
end
assert create_table("IF NOT EXISTS subscribers (email TEXT UNIQUE PRIMARY KEY, joined DATETIME DEFAULT CURRENT_TIMESTAMP)") else
- print error or else "?"
+ print_error error or else "?"
end
end
end
# Build and return a `BeerEvents` for today compared to the last weekday
- fun beer_events_today: BeerEvents
+ fun beer_events_today: nullable BeerEvents
do
var tm = new Tm.localtime
var last_weekday
last_weekday = "date('now', 'weekday 6', '-7 day')"
else last_weekday = "date('now', '-1 day')"
- return beer_events_since(last_weekday).as(not null) # This is used by daily
+ return beer_events_since_sql(last_weekday)
end
# Build and return a `BeerEvents` for today compared to `prev_day`
# Return `null` on error
fun beer_events_since(prev_day: String): nullable BeerEvents
do
+ prev_day = prev_day.to_sql_date_string
+ return beer_events_since_sql("date({prev_day})")
+ end
+
+ # `BeerEvents` since the SQLite formatted date command `sql_date`
+ #
+ # Return `null` on error
+ private fun beer_events_since_sql(sql_date: String): nullable BeerEvents
+ do
var events = new BeerEvents
# New
var stmt = select("ROWID, name, desc FROM beers WHERE " +
"ROWID IN (SELECT beer FROM daily WHERE day=(SELECT MAX(day) FROM daily)) AND " +
- "NOT ROWID IN (SELECT beer FROM daily WHERE date(day) = date({prev_day}))")
+ "NOT ROWID IN (SELECT beer FROM daily WHERE date(day) = {sql_date})")
if stmt == null then return null
for row in stmt do events.new_beers.add row.to_beer
# Gone
stmt = select("ROWID, name, desc FROM beers WHERE " +
"NOT ROWID IN (SELECT beer FROM daily WHERE day=(SELECT MAX(day) FROM daily)) AND " +
- "ROWID IN (SELECT beer FROM daily WHERE date(day) = date({prev_day}))")
+ "ROWID IN (SELECT beer FROM daily WHERE date(day) = {sql_date})")
if stmt == null then return null
for row in stmt do events.gone_beers.add row.to_beer
# Fix
stmt = select("ROWID, name, desc FROM beers WHERE " +
"ROWID IN (SELECT beer FROM daily WHERE day=(SELECT MAX(day) FROM daily)) AND " +
- "ROWID IN (SELECT beer FROM daily WHERE date(day) = date({prev_day}))")
+ "ROWID IN (SELECT beer FROM daily WHERE date(day) = {sql_date})")
if stmt == null then return null
for row in stmt do events.fix_beers.add row.to_beer
do
var stmt = select("ROWID, name, desc FROM beers WHERE ROWID = {id}")
if stmt == null then return null
- for row in stmt do return row.to_beer
- return null
+
+ var res = null
+ for row in stmt do
+ res = row.to_beer
+ break
+ end
+ return res
end
# Days where `beer` was available, all known days if `beer == null`