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 error
or else "?"
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 "?"
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 "?"
52 # Update the DB with a all the `beers` available today
54 # Delete any other previous information for today.
55 fun insert_beers_of_the_day
(beers
: HashSet[Beer])
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 "?"
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 "?"
70 assert execute
("INSERT INTO daily (beer, day) VALUES (" +
71 "(SELECT min(ROWID) FROM beers WHERE lower(name) = lower({beer.name.to_sql_string})), " +
73 print error
or else "?"
78 # Build and return a `BeerEvents` for today compared to the last weekday
79 fun beer_events_today
: nullable BeerEvents
81 var tm
= new Tm.localtime
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')"
88 return beer_events_since_sql
(last_weekday
)
91 # Build and return a `BeerEvents` for today compared to `prev_day`
93 # Return `null` on error
94 fun beer_events_since
(prev_day
: String): nullable BeerEvents
96 prev_day
= prev_day
.to_sql_date_string
97 return beer_events_since_sql
("date({prev_day})")
100 # `BeerEvents` since the SQLite formatted date command `sql_date`
102 # Return `null` on error
103 private fun beer_events_since_sql
(sql_date
: String): nullable BeerEvents
105 var events
= new BeerEvents
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
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
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
133 # Return `null` on error.
134 fun beers
: nullable Array[Beer]
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
]
141 # Load beer information from its database id
142 fun beer_from_id
(id
: Int): nullable Beer
144 var stmt
= select
("ROWID, name, desc FROM beers WHERE ROWID = {id}")
145 if stmt
== null then return null
155 # Days where `beer` was available, all known days if `beer == null`
157 # Return `null` on error.
158 fun days
(beer
: nullable Beer): nullable Array[String]
162 stmt
= select
("DISTINCT day FROM daily")
165 DISTINCT day FROM daily WHERE beer=(SELECT ROWID FROM beers WHERE name="{{{beer.name}}}")""")
168 if stmt
== null then return null
169 return [for row
in stmt
do row
[0].to_s
]
172 # All the subscribers to the mailing list
173 fun subscribers
: Array[String]
175 var subs
= new Array[String]
176 for row
in select
("email FROM subscribers") do subs
.add row
[0].to_s
180 # Add `email` to the mailing list subscribers
181 fun subscribe
(email
: String)
183 assert insert
("OR IGNORE INTO subscribers (email) VALUES (lower({email.to_sql_string}))") else
184 print error
or else "?"
188 # Remove `email` to the mailing list subscribers
189 fun unsubscribe
(email
: String)
191 assert execute
("DELETE FROM subscribers WHERE email = lower({email.to_sql_string})") else
192 print error
or else "?"
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
)