1 # This file is part of NIT ( http://www.nitlanguage.org ).
3 # Copyright 2014 Alexis Laferrière <alexis.laf@xymus.net>
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
9 # http://www.apache.org/licenses/LICENSE-2.0
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.
17 # Database interface to be used by the Web server and daily program
24 # The database of this project
35 # Create all tables for this project (IF NOT EXISTS)
38 assert create_table
("IF NOT EXISTS beers (name TEXT PRIMARY KEY, desc TEXT)") else
39 print error
or else "?"
42 assert create_table
("IF NOT EXISTS daily (beer INTEGER, day DATE)") else
43 print error
or else "?"
46 assert create_table
("IF NOT EXISTS subscribers (email TEXT UNIQUE PRIMARY KEY, joined DATETIME DEFAULT CURRENT_TIMESTAMP)") else
47 print error
or else "?"
51 # Update the DB with a all the `beers` available today
53 # Delete any other previous information for today.
54 fun insert_beers_of_the_day
(beers
: HashSet[Beer])
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 "?"
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 "?"
69 assert execute
("INSERT INTO daily (beer, day) VALUES (" +
70 "(SELECT min(ROWID) FROM beers WHERE lower(name) = lower({beer.name.to_sql_string})), " +
72 print error
or else "?"
77 # Build and return a `BeerEvents` for today compared to the last weekday
78 fun beer_events_today
: BeerEvents
80 var tm
= new Tm.localtime
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')"
87 return beer_events_since
(last_weekday
).as(not null) # This is used by daily
90 # Build and return a `BeerEvents` for today compared to `prev_day`
92 # Return `null` on error
93 fun beer_events_since
(prev_day
: String): nullable BeerEvents
95 var events
= new BeerEvents
98 var stmt
= select
("name, desc FROM beers WHERE " +
99 "ROWID IN (SELECT beer FROM daily WHERE date(day) = date('now')) 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
105 stmt
= select
("name, desc FROM beers WHERE " +
106 "NOT ROWID IN (SELECT beer FROM daily WHERE date(day) = date('now')) 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
112 stmt
= select
("name, desc FROM beers WHERE " +
113 "ROWID IN (SELECT beer FROM daily WHERE date(day) = date('now')) 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
121 # All the subscribers to the mailing list
122 fun subscribers
: Array[String]
124 var subs
= new Array[String]
125 for row
in select
("email FROM subscribers") do subs
.add row
[0].to_s
129 # Add `email` to the mailing list subscribers
130 fun subscribe
(email
: String)
132 assert insert
("OR IGNORE INTO subscribers (email) VALUES (lower({email.to_sql_string}))") else
133 print error
or else "?"
137 # Remove `email` to the mailing list subscribers
138 fun unsubscribe
(email
: String)
140 assert execute
("DELETE FROM subscribers WHERE email = lower({email.to_sql_string})") else
141 print error
or else "?"
146 redef class StatementRow
147 # Convert this BD row to a `Beer`
148 fun to_beer
: Beer do return new Beer(self[0].to_s
, self[1].to_s
)