1 # This file is part of NIT ( http://www.nitlanguage.org ).
3 # Licensed under the Apache License, Version 2.0 (the "License");
4 # you may not use this file except in compliance with the License.
5 # You may obtain a copy of the License at
7 # http://www.apache.org/licenses/LICENSE-2.0
9 # Unless required by applicable law or agreed to in writing, software
10 # distributed under the License is distributed on an "AS IS" BASIS,
11 # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12 # See the License for the specific language governing permissions and
13 # limitations under the License.
15 # Social features acting on the database
16 module benitlux_social
22 redef class BenitluxDB
24 redef fun create_tables
28 # User and password table
29 # Contains only core user/account info. Extra preferences
30 # should be added to a different table.
31 assert create_table
("IF NOT EXISTS users (name TEXT PRIMARY KEY, pass TEXT, email TEXT, joined DATETIME DEFAULT CURRENT_TIMESTAMP, last_checkin DATETIME)") else
32 print_error
"Create 'users' failed with: {error or else "?"}"
36 assert create_table
("IF NOT EXISTS follows (user_from INTEGER, user_to INTEGER)") else
37 print_error
"Create 'follows' failed with: {error or else "?"}"
41 # TODO the `text` block is reserved for future use.
42 assert create_table
("""
43 IF NOT EXISTS reviews (author INTEGER, beer INTEGER, posted DATETIME DEFAULT CURRENT_TIMESTAMP, rating INTEGER, text TEXT)
45 print_error
"Create 'reviews' failed with: {error or else "?"}"
48 assert execute
("CREATE UNIQUE INDEX IF NOT EXISTS unique_reviews ON reviews(author, beer)") else
49 print_error
"Create 'unique_reviews' failed with: {error or else "?"}"
53 # Hold the history of check ins and outs.
54 assert create_table
("IF NOT EXISTS checkins (user INTEGER, time DATETIME DEFAULT CURRENT_TIMESTAMP, is_in BOOLEAN)") else
55 print_error
"Create 'checkins' failed with: {error or else "?"}"
59 # All tokens ever issued.
60 assert create_table
("IF NOT EXISTS tokens (user INTEGER, token TEXT, last_access DATETIME DEFAULT CURRENT_TIMESTAMP)") else
61 print_error
"Create 'tokens' failed with: {error or else "?"}"
64 # View similar to the table `review` with only the most recent review per author & beer
66 CREATE VIEW IF NOT EXISTS latest_reviews AS
67 SELECT reviews.author, reviews.beer, reviews.rating, reviews.posted, text
68 FROM reviews, (SELECT author, beer, max(posted) as posted
69 FROM reviews GROUP BY author, beer) AS uniqs
70 WHERE reviews.author = uniqs.author and
71 reviews.beer = uniqs.beer and
72 reviews.posted = uniqs.posted
74 print_error
"Create 'latest_reviews' failed with: {error or else "?"}"
77 # Distance in the rating between each beers by each author
79 CREATE VIEW IF NOT EXISTS user_dist AS
80 SELECT r0.author, r0.beer AS beer0, r1.beer AS beer1, ABS(r1.rating - r0.rating) AS dist
81 FROM latest_reviews AS r0, latest_reviews as r1
82 WHERE r0.author = r1.author""") else
83 print_error
"Create 'user_dist' failed with: {error or else "?"}"
86 # Average distance in the rating between each beers by all authors
88 CREATE VIEW IF NOT EXISTS global_dist AS
89 SELECT beer0, beer1, COUNT(dist) AS count, AVG(dist) as average
91 GROUP BY beer0, beer1""") else
92 print_error
"Create 'global_dist' failed with: {error or else "?"}"
96 # Check if the login credentials are valid
98 # If valid, returns the username with the capitalization used at registration.
99 # Returns `null` on invalid password or anormal errors.
100 fun login
(user
, pass
: String): nullable LoginResult
102 var stmt
= select
("ROWID, name FROM users WHERE lower({user.to_sql_string}) = lower(name) " +
103 "AND {pass.to_sql_string} = pass")
105 print_error
"Select 'login' failed with: {error or else "?"}"
110 var user_id
= row
[0].to_i
111 var token
= new_token
(user_id
)
112 var u
= new User(user_id
, row
[1].to_s
)
114 return new LoginResult(u
, token
)
119 # Get a new token and associate it to `user_id`
120 fun new_token
(user_id
: Int): String
123 var token
= generate_token
125 # Check if token already exists
126 var stmt
= select
("ROWID FROM tokens WHERE token={token.to_sql_string}")
128 if stmt
.iterator
.to_a
.not_empty
then continue
131 var res
= insert
("INTO tokens(user, token) VALUES({user_id}, {token.to_sql_string})")
134 # TODO merge the 2 requests to avoid race conditions,
135 # if we ever share the BD between 2 servers/threads
141 # Get the user id associated to `token`, if any
143 # Accepts `null` token to simplify call sites, but always returns `null` in such cases.
144 fun token_to_id
(token
: nullable String): nullable Int
146 if token
== null then return null
148 var stmt
= select
("user FROM tokens WHERE token={token.to_sql_string}")
150 print_error
"Select 'token_to_id' failed with: {error or else "?"}"
154 # TODO update token timestamp and platform/client hint of last connection.
155 # These informations could help detect malicious access to the account.
165 # Get `User` data from the integer `id`
166 fun id_to_user
(id
: Int): nullable User
168 var stmt
= select
("name FROM users WHERE ROWID = {id}")
173 res
= new User(id
, row
[0].to_s
)
179 # Try to sign up a new user, return `true` on success
180 fun signup
(user
, pass
, email
: String): nullable String
182 # Check if already in user
183 var stmt
= select
("ROWID FROM users WHERE lower({user.to_sql_string}) = lower(name)")
184 assert stmt
!= null else print_error
"Select 'sign_up' failed with: {error or else "?"}"
185 if not stmt
.iterator
.to_a
.is_empty
then return "Username already in use"
188 stmt
= select
("ROWID FROM users WHERE lower({email.to_sql_string}) = lower(email)")
189 assert stmt
!= null else print_error
"Select 'sign_up' failed with: {error or else "?"}"
190 if not stmt
.iterator
.to_a
.is_empty
then return "There's already an account with that email"
193 assert insert
("INTO users(name, pass, email) VALUES ({user.to_sql_string}, {pass.to_sql_string}, {email.to_sql_string})") else
194 print_error
"Insert 'sign_up' failed with: {error or else "?"}"
201 fun post_review
(author
, beer
, rating
: Int, text
: String)
203 assert insert
("OR REPLACE INTO reviews(author, beer, rating, text) VALUES ({author}, {beer}, {rating}, {text.to_sql_string})") else
204 print_error
"Insert 'post_review' failed with: {error or else "?"}"
208 # Fetch stats on `beer`, if `followed_only` limit to the reviews of followed users
209 fun beer_stats
(beer
: Int, followed_only
: nullable Int): nullable BeerStats
211 var sql
= "avg(rating), count(rating) FROM reviews WHERE beer = {beer}"
212 if followed_only
!= null then
213 sql
+= " AND (author = {followed_only} OR " +
214 "author IN (SELECT user_to FROM follows WHERE user_from = {followed_only}))"
217 var stmt
= select
(sql
)
218 assert stmt
!= null else print_error
"Select 'beer_stats' failed with: {error or else "?"}"
220 var b
= beer_from_id
(beer
)
221 if b
== null then return null
225 res
= new BeerStats(b
, row
[0].to_f
, row
[1].to_i
)
231 # Fetch the most recent rating left by `user_id` about `beer`
232 fun latest_rating
(user_id
, beer
: Int): nullable Int
234 var stmt
= select
("rating FROM reviews WHERE author = {user_id} AND beer = {beer} ORDER BY ROWID DESC LIMIT 1")
235 assert stmt
!= null else print_error
"Select 'rating' failed with: {error or else "?"}"
245 # Register that `user_from` follows `user_to`
246 fun add_followed
(user_from
, user_to
: Int)
248 assert insert
("OR IGNORE INTO follows(user_from, user_to) VALUES ({user_from}, {user_to})") else
249 print_error
"Insert 'add_followed' failed with: {error or else "?"}"
253 # Register that `user_from` does not follow `user_to`
254 fun remove_followed
(user_from
, user_to
: Int)
256 assert execute
("DELETE FROM follows WHERE user_from = {user_from} AND user_to = {user_to}") else
257 print_error
"Delete 'remove_followed' failed with: {error or else "?"}"
261 # Does `user_from` follow `user_to`?
262 fun follows
(user_from
, user_to
: Int): Bool
264 var stmt
= select
("ROWID FROM follows WHERE user_from = {user_from} AND user_to = {user_to}")
265 assert stmt
!= null else
266 print_error
"Select 'follows' failed with: {error or else "?"}"
269 for row
in stmt
.iterator
.to_a
do return true
273 # List of users with a name similar to `pattern` in relation to `user_id`
274 fun search_users
(pattern
: String, user_id
: nullable Int): nullable Array[UserAndFollowing]
276 # TODO a better search logic
278 var like_str
= "'%{pattern.replace('\\', "\\\\").replace('\'', "''").replace("%", "\\%")}%'"
279 var stmt
= select
("ROWID, name FROM users WHERE name LIKE {like_str}")
280 assert stmt
!= null else print_error
"Select 'search_users' failed with: {error or else "?"}"
282 var users
= new Array[User]
283 for row
in stmt
do users
.add
(new User(row
[0].to_i
, row
[1].to_s
))
285 return user_to_user_and_following
(users
, user_id
)
288 # List the followers of `user_id`
289 fun followers
(user_id
: Int): nullable Array[UserAndFollowing]
291 var stmt
= select
("ROWID, name FROM users WHERE ROWID in (SELECT user_from FROM follows WHERE user_to = {user_id})")
292 assert stmt
!= null else print_error
"Select 'followers' failed with: {error or else "?"}"
294 var users
= new Array[User]
295 for row
in stmt
do users
.add
(new User(row
[0].to_i
, row
[1].to_s
))
297 return user_to_user_and_following
(users
, user_id
)
300 # List users followed by `user_id`
301 fun followed
(user_id
: Int): nullable Array[UserAndFollowing]
303 var stmt
= select
("ROWID, name FROM users WHERE ROWID in (SELECT user_to FROM follows WHERE user_from = {user_id})")
304 assert stmt
!= null else print_error
"Select 'followed' failed with: {error or else "?"}"
306 var users
= new Array[User]
307 for row
in stmt
do users
.add
(new User(row
[0].to_i
, row
[1].to_s
))
309 return user_to_user_and_following
(users
, user_id
)
312 # List reciprocal friends of `user_id`
313 fun followed_followers
(user_id
: Int): nullable Array[User]
315 var stmt
= select
("""
316 ROWID, name FROM users WHERE
317 users.ROWID in (SELECT user_from FROM follows WHERE user_to = {{{user_id}}}) AND
318 users.ROWID in (SELECT user_to FROM follows WHERE user_from = {{{user_id}}})""")
319 assert stmt
!= null else print_error
"Select 'followed_followers' failed with: {error or else "?"}"
321 var users
= new Array[User]
322 for row
in stmt
do users
.add
new User(row
[0].to_i
, row
[1].to_s
)
327 # List `n` friends or recommendations (with information)
329 # If `user_id` is null, list only recommendations.
330 # The default value of `n` is 6 friends.
331 fun friends
(user_id
, n
: nullable Int): nullable Array[UserAndFollowing]
333 var limit
= n
or else 6
335 if user_id
!= null then
336 people
= followed_followers
(user_id
)
337 if people
== null then return null
339 people
= new Array[User]
342 if people
.length
< limit
then
343 # Add recommendations
344 # TODO fill with recommendations from a few friends
345 # TODO starting friends recommendations, popular clients, same visit patterns,
346 # currently at the bar, official people (proprio, brewers, barmaids & barmen)
348 limit
-= people
.length
349 user_id
= user_id
or else -1
351 # Recommend popular users
352 var stmt
= select
("""
353 ROWID, name, (SELECT count(*) FROM follows WHERE follows.user_to == users.ROWID) AS n_followers
354 FROM users WHERE ROWID != {{{user_id}}}
355 ORDER BY n_followers DESC LIMIT {{{limit}}}""")
356 assert stmt
!= null else print_error
"Select 'friends' failed with: {error or else "?"}"
357 for row
in stmt
do people
.add
new User(row
[0].to_i
, row
[1].to_s
)
359 else if people
.length
> limit
then
361 # TODO pass the limit `n` to followed_followers
362 people
= people
.subarray
(0, limit
)
365 return user_to_user_and_following
(people
, user_id
)
368 # Convert `users` to an `Array[UserAndFollowing]` in relation to `user_id`
369 private fun user_to_user_and_following
(users
: Array[User], user_id
: nullable Int): Array[UserAndFollowing]
371 var users_and_f
= new Array[UserAndFollowing]
374 var favs
= favorite_beers
(user
.id
)
375 if user_id
!= null then
376 var following
= follows
(user_id
, user
.id
)
377 var followed_by
= follows
(user
.id
, user_id
)
378 uaf
= new UserAndFollowing(user
, favs
.join
(", "), following
, followed_by
)
380 uaf
= new UserAndFollowing(user
, favs
.join
(", "), false, false)
387 # List the name of the favorite beers of `user_id`
388 fun favorite_beers
(user_id
: Int): Array[String]
390 var stmt
= select
("name FROM beers WHERE ROWID in (SELECT beer FROM reviews WHERE author = {user_id} AND rating = 5)")
391 assert stmt
!= null else print_error
"Select 'rating' failed with: {error or else "?"}"
393 var beers
= new Array[String]
394 for row
in stmt
do beers
.add row
[0].to_s
398 # List available beers and changes in relation to `user_from`
399 fun list_beers_and_rating
(user_from
: nullable Int, since
: nullable String): nullable Array[BeerAndRatings]
401 # TODO replace old services `beer_events_since` and `beer_events_today` by a single* SQL call
404 if since
!= null then
405 events
= beer_events_since
(since
)
406 else events
= beer_events_today
407 if events
== null then return null
409 # New since the last weekday
410 var all
= new Array[BeerAndRatings]
411 for beer
in events
.new_beers
do
412 var global
= beer_stats
(beer
.id
)
415 if user_from
!= null then
416 friend
= beer_stats
(beer
.id
, user_from
)
417 rating
= latest_rating
(user_from
, beer
.id
)
419 all
.add
new BeerAndRatings(beer
, global
, friend
, rating
,
420 false, "TODO batch", true, false, false)
423 # Still here since the last weekday
424 for beer
in events
.fix_beers
do
425 var global
= beer_stats
(beer
.id
)
428 if user_from
!= null then
429 friend
= beer_stats
(beer
.id
, user_from
)
430 rating
= latest_rating
(user_from
, beer
.id
)
432 all
.add
new BeerAndRatings(beer
, global
, friend
, rating
,
433 false, "TODO batch", false, true, false)
438 var badges
= badges
(beer
.beer
.id
, user_from
)
439 if badges
.not_empty
then beer
.badges
= badges
445 # Badges (or comments, of social description) on `beer` relative to `user_from`
446 fun badges
(beer
: Int, user_from
: nullable Int): Array[BeerBadge]
448 var badges
= new Array[BeerBadge]
450 # Overall favorite available today
451 var rows
= select
("""
454 SELECT beer, AVG(rating) AS average, COUNT(rating) AS count
457 SELECT beer FROM daily WHERE day IN (
458 SELECT max(day) FROM daily))
460 WHERE count > 1 ORDER BY average DESC LIMIT 1""")
462 print_error
"Select 'overall-favorite' failed with: {error or else "?"}"
465 if row
[0].to_i
== beer
then badges
.add
new BestBeerBadge
469 if user_from
!= null then
473 WHERE ROWID in (SELECT user_to FROM follows WHERE user_from = {{{user_from}}})
474 AND ROWID in (SELECT author FROM latest_reviews WHERE beer = {{{beer}}} AND rating = 5)""")
476 print_error
"Select 'friend-favorite' failed with: {error or else "?"}"
478 var badge
= new FavoriteBeerBadge
479 for row
in rows
do badge
.users
.add row
[0].to_s
480 if badge
.users
.not_empty
then badges
.add badge
483 # Rated similarly to a favorite by most (approximative description)
485 (SELECT name FROM beers WHERE beers.ROWID = beer1)
486 FROM global_dist WHERE beer0 = {{{beer}}} AND beer0 != beer1
487 AND count > 1 AND average < 2.0
488 AND beer1 IN (SELECT beer FROM latest_reviews WHERE
489 author = {{{user_from}}} AND rating = 5)
490 ORDER BY average LIMIT 1""")
492 print_error
"Select 'similar' failed with: {error or else "?"}"
494 var badge
= new SimilarBeerBadge
495 for row
in rows
do badge
.beers
.add row
[0].to_s
496 if badge
.beers
.not_empty
then badges
.add badge
500 # TODO more for people with no friends or favorites
505 # Register `user_id` as being in or out
506 fun checkin
(user_id
: Int, checkin
: Bool)
508 var res
= insert
("INTO checkins(user, is_in) VALUES({user_id}, {if checkin then 1 else 0})")
509 if not res
then print_error
"Insert 'checkin' failed with: {error or else "?"}"
512 # List currently checked in reciprocal friends of `user_id`
513 fun checkedin_followed_followers
(user_id
: Int): nullable CheckinReport
516 ROWID, name FROM users
517 WHERE 1 in (SELECT is_in FROM checkins WHERE user = users.ROWID ORDER BY ROWID DESC LIMIT 1)
518 AND ROWID in (SELECT user_from FROM follows WHERE user_to = {{{user_id}}})
519 AND ROWID in (SELECT user_to FROM follows WHERE user_from = {{{user_id}}})"""
521 var stmt
= select
(sql
)
523 print_error
"Select 'checkedin_followed_followers' failed with: {error or else "?"}"
527 var report
= new CheckinReport
528 for row
in stmt
do report
.users
.add
new User(row
[0].to_i
, row
[1].to_s
)