contrib/benitlux: standardize dates on the server-side

[nit.git] / contrib / benitlux / src / benitlux_db.nit

diff --git a/contrib/benitlux/src/benitlux_db.nit b/contrib/benitlux/src/benitlux_db.nit
index ae6a905..c899ede 100644 (file)
--- a/contrib/benitlux/src/benitlux_db.nit
+++ b/contrib/benitlux/src/benitlux_db.nit
@@ -22,7 +22,7 @@ import sqlite3
 import benitlux_model
 
 # The database of this project
-class DB
+class BenitluxDB
        super Sqlite3DB
 
        redef init open(path)
@@ -36,15 +36,16 @@ class DB
        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
 
@@ -84,7 +85,7 @@ class DB
                        last_weekday = "date('now', 'weekday 6', '-7 day')"
                else last_weekday = "date('now', '-1 day')"
 
-               return beer_events_since(last_weekday)
+               return beer_events_since_sql(last_weekday)
        end
 
        # Build and return a `BeerEvents` for today compared to `prev_day`
@@ -92,26 +93,35 @@ class DB
        # 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
 
@@ -133,8 +143,13 @@ class DB
        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`