contrib/benitlux/src/server/benitlux_social.nit

   1 # This file is part of NIT ( http://www.nitlanguage.org ).
   2 #
   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
   6 #
   7 #     http://www.apache.org/licenses/LICENSE-2.0
   8 #
   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.
  14
  15 # Social features acting on the database
  16 module benitlux_social
  17
  18 import nitcorn::token
  19
  20 import benitlux_db
  21
  22 redef class BenitluxDB
  23
  24         redef fun create_tables
  25         do
  26                 super
  27
  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 "?"}"
  33                 end
  34
  35                 # User follow table
  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 "?"}"
  38                 end
  39
  40                 # Reviews table
  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)
  44                 """) else
  45                         print_error "Create 'reviews' failed with: {error or else "?"}"
  46                 end
  47
  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 "?"}"
  50                 end
  51
  52                 # Checkins table
  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 "?"}"
  56                 end
  57
  58                 # User tokens
  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 "?"}"
  62                 end
  63
  64                 # View similar to the table `review` with only the most recent review per author & beer
  65                 assert execute("""
  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
  73 """) else
  74                         print_error "Create 'latest_reviews' failed with: {error or else "?"}"
  75                 end
  76
  77                 # Distance in the rating between each beers by each author
  78                 assert execute("""
  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 "?"}"
  84                 end
  85
  86                 # Average distance in the rating between each beers by all authors
  87                 assert execute("""
  88 CREATE VIEW IF NOT EXISTS global_dist AS
  89 SELECT beer0, beer1, COUNT(dist) AS count, AVG(dist) as average
  90 FROM user_dist
  91 GROUP BY beer0, beer1""") else
  92                         print_error "Create 'global_dist' failed with: {error or else "?"}"
  93                 end
  94         end
  95
  96         # Check if the login credentials are valid
  97         #
  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
 101         do
 102                 var stmt = select("ROWID, name FROM users WHERE lower({user.to_sql_string}) = lower(name) " +
 103                                   "AND {pass.to_sql_string} = pass")
 104                 if stmt == null then
 105                         print_error "Select 'login' failed with: {error or else "?"}"
 106                         return null
 107                 end
 108
 109                 for row in stmt do
 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)
 113                         stmt.close
 114                         return new LoginResult(u, token)
 115                 end
 116                 return null
 117         end
 118
 119         # Get a new token and associate it to `user_id`
 120         fun new_token(user_id: Int): String
 121         do
 122                 loop
 123                         var token = generate_token
 124
 125                         # Check if token already exists
 126                         var stmt = select("ROWID FROM tokens WHERE token={token.to_sql_string}")
 127                         assert stmt != null
 128                         if stmt.iterator.to_a.not_empty then continue
 129
 130                         # Register token
 131                         var res = insert("INTO tokens(user, token) VALUES({user_id}, {token.to_sql_string})")
 132                         assert res
 133
 134                         # TODO merge the 2 requests to avoid race conditions,
 135                         # if we ever share the BD between 2 servers/threads
 136
 137                         return token
 138                 end
 139         end
 140
 141         # Get the user id associated to `token`, if any
 142         #
 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
 145         do
 146                 if token == null then return null
 147
 148                 var stmt = select("user FROM tokens WHERE token={token.to_sql_string}")
 149                 if stmt == null then
 150                         print_error "Select 'token_to_id' failed with: {error or else "?"}"
 151                         return null
 152                 end
 153
 154                 # TODO update token timestamp and platform/client hint of last connection.
 155                 # These informations could help detect malicious access to the account.
 156
 157                 var res = null
 158                 for row in stmt do
 159                         res = row[0].to_i
 160                         break
 161                 end
 162                 return res
 163         end
 164
 165         # Get `User` data from the integer `id`
 166         fun id_to_user(id: Int): nullable User
 167         do
 168                 var stmt = select("name FROM users WHERE ROWID = {id}")
 169                 assert stmt != null
 170
 171                 var res = null
 172                 for row in stmt do
 173                         res = new User(id, row[0].to_s)
 174                         break
 175                 end
 176                 return res
 177         end
 178
 179         # Try to sign up a new user, return `true` on success
 180         fun signup(user, pass, email: String): nullable String
 181         do
 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."
 186
 187                 # Check email 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"
 191
 192                 # Insert intro BD
 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 "?"}"
 195                 end
 196
 197                 return null
 198         end
 199
 200         # Post a review
 201         fun post_review(author, beer, rating: Int, text: String)
 202         do
 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 "?"}"
 205                 end
 206         end
 207
 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
 210         do
 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}))"
 215                 end
 216
 217                 var stmt = select(sql)
 218                 assert stmt != null else print_error "Select 'beer_stats' failed with: {error or else "?"}"
 219
 220                 var b = beer_from_id(beer)
 221                 if b == null then return null
 222
 223                 var res = null
 224                 for row in stmt do
 225                         res = new BeerStats(b, row[0].to_f, row[1].to_i)
 226                         break
 227                 end
 228                 return res
 229         end
 230
 231         # Fetch the most recent rating left by `user_id` about `beer`
 232         fun latest_rating(user_id, beer: Int): nullable Int
 233         do
 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 "?"}"
 236
 237                 var res = null
 238                 for row in stmt do
 239                         res = row[0].to_i
 240                         break
 241                 end
 242                 return res
 243         end
 244
 245         # Register that `user_from` follows `user_to`
 246         fun add_followed(user_from, user_to: Int)
 247         do
 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 "?"}"
 250                 end
 251         end
 252
 253         # Register that `user_from` does not follow `user_to`
 254         fun remove_followed(user_from, user_to: Int)
 255         do
 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 "?"}"
 258                 end
 259         end
 260
 261         # Does `user_from` follow `user_to`?
 262         fun follows(user_from, user_to: Int): Bool
 263         do
 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 "?"}"
 267                 end
 268
 269                 for row in stmt.iterator.to_a do return true
 270                 return false
 271         end
 272
 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]
 275         do
 276                 # TODO a better search logic
 277
 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 "?"}"
 281
 282                 var users = new Array[User]
 283                 for row in stmt do users.add(new User(row[0].to_i, row[1].to_s))
 284
 285                 return user_to_user_and_following(users, user_id)
 286         end
 287
 288         # List the followers of `user_id`
 289         fun followers(user_id: Int): nullable Array[UserAndFollowing]
 290         do
 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 "?"}"
 293
 294                 var users = new Array[User]
 295                 for row in stmt do users.add(new User(row[0].to_i, row[1].to_s))
 296
 297                 return user_to_user_and_following(users, user_id)
 298         end
 299
 300         # List users followed by `user_id`
 301         fun followed(user_id: Int): nullable Array[UserAndFollowing]
 302         do
 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 "?"}"
 305
 306                 var users = new Array[User]
 307                 for row in stmt do users.add(new User(row[0].to_i, row[1].to_s))
 308
 309                 return user_to_user_and_following(users, user_id)
 310         end
 311
 312         # List reciprocal friends of `user_id`
 313         fun followed_followers(user_id: Int): nullable Array[User]
 314         do
 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 "?"}"
 320
 321                 var users = new Array[User]
 322                 for row in stmt do users.add new User(row[0].to_i, row[1].to_s)
 323
 324                 return users
 325         end
 326
 327         # List `n` friends or recommendations (with information)
 328         #
 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]
 332         do
 333                 var limit = n or else 6
 334                 var people = null
 335                 if user_id != null then
 336                         people = followed_followers(user_id)
 337                         if people == null then return null
 338                 else
 339                         people = new Array[User]
 340                 end
 341
 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)
 347
 348                         limit -= people.length
 349
 350                         # Recommend popular users without the local user or `followed_followers`
 351                         var avoid_ids = new Array[Int]
 352                         if user_id != null then avoid_ids.add user_id
 353                         for p in people do avoid_ids.add p.id
 354                         if avoid_ids.is_empty then avoid_ids.add -1
 355
 356                         var stmt = select("""
 357 ROWID, name, (SELECT count(*) FROM follows WHERE follows.user_to == users.ROWID) AS n_followers
 358 FROM users WHERE {{{
 359         [for id in avoid_ids do "ROWID != {id}"].join(" AND ")
 360 }}}
 361 ORDER BY n_followers DESC LIMIT {{{limit}}}""")
 362                         assert stmt != null else print_error "Select 'friends' failed with: {error or else "?"}"
 363                         for row in stmt do people.add new User(row[0].to_i, row[1].to_s)
 364
 365                 else if people.length > limit then
 366
 367                         # TODO pass the limit `n` to followed_followers
 368                         people = people.subarray(0, limit)
 369                 end
 370
 371                 return user_to_user_and_following(people, user_id)
 372         end
 373
 374         # Convert `users` to an `Array[UserAndFollowing]` in relation to `user_id`
 375         private fun user_to_user_and_following(users: Array[User], user_id: nullable Int): Array[UserAndFollowing]
 376         do
 377                 var users_and_f = new Array[UserAndFollowing]
 378                 for user in users do
 379                         var uaf
 380                         var favs = favorite_beers(user.id)
 381                         if user_id != null then
 382                                 var following = follows(user_id, user.id)
 383                                 var followed_by = follows(user.id, user_id)
 384                                 uaf = new UserAndFollowing(user, favs.join(", "), following, followed_by)
 385                         else
 386                                 uaf = new UserAndFollowing(user, favs.join(", "), false, false)
 387                         end
 388                         users_and_f.add uaf
 389                 end
 390                 return users_and_f
 391         end
 392
 393         # List the name of the favorite beers of `user_id`
 394         fun favorite_beers(user_id: Int): Array[String]
 395         do
 396                 var stmt = select("name FROM beers WHERE ROWID in (SELECT beer FROM reviews WHERE author = {user_id} AND rating = 5)")
 397                 assert stmt != null else print_error "Select 'rating' failed with: {error or else "?"}"
 398
 399                 var beers = new Array[String]
 400                 for row in stmt do beers.add row[0].to_s
 401                 return beers
 402         end
 403
 404         # List available beers and changes in relation to `user_from`
 405         fun list_beers_and_rating(user_from: nullable Int, since: nullable String): nullable Array[BeerAndRatings]
 406         do
 407                 # TODO replace old services `beer_events_since` and `beer_events_today` by a single* SQL call
 408
 409                 var events
 410                 if since != null then
 411                         events = beer_events_since(since)
 412                 else events = beer_events_today
 413                 if events == null then return null
 414
 415                 # New since the last weekday
 416                 var all = new Array[BeerAndRatings]
 417                 for beer in events.new_beers do
 418                         var global = beer_stats(beer.id)
 419                         var friend = null
 420                         var rating = null
 421                         if user_from != null then
 422                                 friend = beer_stats(beer.id, user_from)
 423                                 rating = latest_rating(user_from, beer.id)
 424                         end
 425                         all.add new BeerAndRatings(beer, global, friend, rating,
 426                                 false, "TODO batch", true, false, false)
 427                 end
 428
 429                 # Still here since the last weekday
 430                 for beer in events.fix_beers do
 431                         var global = beer_stats(beer.id)
 432                         var friend = null
 433                         var rating = null
 434                         if user_from != null then
 435                                 friend = beer_stats(beer.id, user_from)
 436                                 rating = latest_rating(user_from, beer.id)
 437                         end
 438                         all.add new BeerAndRatings(beer, global, friend, rating,
 439                                 false, "TODO batch", false, true, false)
 440                 end
 441
 442                 # Apply badges
 443                 for beer in all do
 444                         var badges = badges(beer.beer.id, user_from)
 445                         if badges.not_empty then beer.badges = badges
 446                 end
 447
 448                 return all
 449         end
 450
 451         # Badges (or comments, of social description) on `beer` relative to `user_from`
 452         fun badges(beer: Int, user_from: nullable Int): Array[BeerBadge]
 453         do
 454                 var badges = new Array[BeerBadge]
 455
 456                 # Overall favorite available today
 457                 var rows = select("""
 458 beer
 459 FROM (
 460         SELECT beer, AVG(rating) AS average, COUNT(rating) AS count
 461         FROM latest_reviews
 462         WHERE beer IN (
 463                 SELECT beer FROM daily WHERE day IN (
 464                         SELECT max(day) FROM daily))
 465         GROUP BY beer)
 466 WHERE count > 1 ORDER BY average DESC LIMIT 1""")
 467                 if rows == null then
 468                         print_error "Select 'overall-favorite' failed with: {error or else "?"}"
 469                 else
 470                         for row in rows do
 471                                 if row[0].to_i == beer then badges.add new BestBeerBadge
 472                         end
 473                 end
 474
 475                 if user_from != null then
 476                         # Favorite of friend
 477                         rows = select("""
 478 name FROM users
 479 WHERE ROWID in (SELECT user_to FROM follows WHERE user_from = {{{user_from}}})
 480         AND ROWID in (SELECT author FROM latest_reviews WHERE beer = {{{beer}}} AND rating = 5)""")
 481                         if rows == null then
 482                                 print_error "Select 'friend-favorite' failed with: {error or else "?"}"
 483                         else
 484                                 var badge = new FavoriteBeerBadge
 485                                 for row in rows do badge.users.add row[0].to_s
 486                                 if badge.users.not_empty then badges.add badge
 487                         end
 488
 489                         # Rated similarly to a favorite by most (approximative description)
 490                         rows = select("""
 491 (SELECT name FROM beers WHERE beers.ROWID = beer1)
 492 FROM global_dist WHERE beer0 = {{{beer}}} AND beer0 != beer1
 493         AND count > 1 AND average < 2.0
 494         AND beer1 IN (SELECT beer FROM latest_reviews WHERE
 495                 author = {{{user_from}}} AND rating = 5)
 496 ORDER BY average LIMIT 1""")
 497                         if rows == null then
 498                                 print_error "Select 'similar' failed with: {error or else "?"}"
 499                         else
 500                                 var badge = new SimilarBeerBadge
 501                                 for row in rows do badge.beers.add row[0].to_s
 502                                 if badge.beers.not_empty then badges.add badge
 503                         end
 504                 end
 505
 506                 # TODO more for people with no friends or favorites
 507
 508                 return badges
 509         end
 510
 511         # Register `user_id` as being in or out
 512         fun checkin(user_id: Int, checkin: Bool)
 513         do
 514                 var res = insert("INTO checkins(user, is_in) VALUES({user_id}, {if checkin then 1 else 0})")
 515                 if not res then print_error "Insert 'checkin' failed with: {error or else "?"}"
 516         end
 517
 518         # List currently checked in reciprocal friends of `user_id`
 519         fun checkedin_followed_followers(user_id: Int): nullable CheckinReport
 520         do
 521                 var sql = """
 522 ROWID, name FROM users
 523 WHERE 1 in (SELECT is_in FROM checkins WHERE user = users.ROWID ORDER BY ROWID DESC LIMIT 1)
 524         AND ROWID in (SELECT user_from FROM follows WHERE user_to = {{{user_id}}})
 525         AND ROWID in (SELECT user_to FROM follows WHERE user_from = {{{user_id}}})"""
 526
 527                 var stmt = select(sql)
 528                 if stmt == null then
 529                         print_error "Select 'checkedin_followed_followers' failed with: {error or else "?"}"
 530                         return null
 531                 end
 532
 533                 var report = new CheckinReport
 534                 for row in stmt do report.users.add new User(row[0].to_i, row[1].to_s)
 535                 return report
 536         end
 537 end