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                         user_id = user_id or else -1
 350
 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)
 358
 359                 else if people.length > limit then
 360
 361                         # TODO pass the limit `n` to followed_followers
 362                         people = people.subarray(0, limit)
 363                 end
 364
 365                 return user_to_user_and_following(people, user_id)
 366         end
 367
 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]
 370         do
 371                 var users_and_f = new Array[UserAndFollowing]
 372                 for user in users do
 373                         var uaf
 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)
 379                         else
 380                                 uaf = new UserAndFollowing(user, favs.join(", "), false, false)
 381                         end
 382                         users_and_f.add uaf
 383                 end
 384                 return users_and_f
 385         end
 386
 387         # List the name of the favorite beers of `user_id`
 388         fun favorite_beers(user_id: Int): Array[String]
 389         do
 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 "?"}"
 392
 393                 var beers = new Array[String]
 394                 for row in stmt do beers.add row[0].to_s
 395                 return beers
 396         end
 397
 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]
 400         do
 401                 # TODO replace old services `beer_events_since` and `beer_events_today` by a single* SQL call
 402
 403                 var events
 404                 if since != null then
 405                         events = beer_events_since(since)
 406                 else events = beer_events_today
 407                 if events == null then return null
 408
 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)
 413                         var friend = null
 414                         var rating = null
 415                         if user_from != null then
 416                                 friend = beer_stats(beer.id, user_from)
 417                                 rating = latest_rating(user_from, beer.id)
 418                         end
 419                         all.add new BeerAndRatings(beer, global, friend, rating,
 420                                 false, "TODO batch", true, false, false)
 421                 end
 422
 423                 # Still here since the last weekday
 424                 for beer in events.fix_beers do
 425                         var global = beer_stats(beer.id)
 426                         var friend = null
 427                         var rating = null
 428                         if user_from != null then
 429                                 friend = beer_stats(beer.id, user_from)
 430                                 rating = latest_rating(user_from, beer.id)
 431                         end
 432                         all.add new BeerAndRatings(beer, global, friend, rating,
 433                                 false, "TODO batch", false, true, false)
 434                 end
 435
 436                 # Apply badges
 437                 for beer in all do
 438                         var badges = badges(beer.beer.id, user_from)
 439                         if badges.not_empty then beer.badges = badges
 440                 end
 441
 442                 return all
 443         end
 444
 445         # Badges (or comments, of social description) on `beer` relative to `user_from`
 446         fun badges(beer: Int, user_from: nullable Int): Array[BeerBadge]
 447         do
 448                 var badges = new Array[BeerBadge]
 449
 450                 # Overall favorite available today
 451                 var rows = select("""
 452 beer
 453 FROM (
 454         SELECT beer, AVG(rating) AS average, COUNT(rating) AS count
 455         FROM latest_reviews
 456         WHERE beer IN (
 457                 SELECT beer FROM daily WHERE day IN (
 458                         SELECT max(day) FROM daily))
 459         GROUP BY beer)
 460 WHERE count > 1 ORDER BY average DESC LIMIT 1""")
 461                 if rows == null then
 462                         print_error "Select 'overall-favorite' failed with: {error or else "?"}"
 463                 else
 464                         for row in rows do
 465                                 if row[0].to_i == beer then badges.add new BestBeerBadge
 466                         end
 467                 end
 468
 469                 if user_from != null then
 470                         # Favorite of friend
 471                         rows = select("""
 472 name FROM users
 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)""")
 475                         if rows == null then
 476                                 print_error "Select 'friend-favorite' failed with: {error or else "?"}"
 477                         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
 481                         end
 482
 483                         # Rated similarly to a favorite by most (approximative description)
 484                         rows = select("""
 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""")
 491                         if rows == null then
 492                                 print_error "Select 'similar' failed with: {error or else "?"}"
 493                         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
 497                         end
 498                 end
 499
 500                 # TODO more for people with no friends or favorites
 501
 502                 return badges
 503         end
 504
 505         # Register `user_id` as being in or out
 506         fun checkin(user_id: Int, checkin: Bool)
 507         do
 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 "?"}"
 510         end
 511
 512         # List currently checked in reciprocal friends of `user_id`
 513         fun checkedin_followed_followers(user_id: Int): nullable CheckinReport
 514         do
 515                 var sql = """
 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}}})"""
 520
 521                 var stmt = select(sql)
 522                 if stmt == null then
 523                         print_error "Select 'checkedin_followed_followers' failed with: {error or else "?"}"
 524                         return null
 525                 end
 526
 527                 var report = new CheckinReport
 528                 for row in stmt do report.users.add new User(row[0].to_i, row[1].to_s)
 529                 return report
 530         end
 531 end