contrib/benitlux/src/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 DB
  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                         return new LoginResult(u, token)
 114                 end
 115                 return null
 116         end
 117
 118         # Get a new token and associate it to `user_id`
 119         fun new_token(user_id: Int): String
 120         do
 121                 loop
 122                         var token = generate_token
 123
 124                         # Check if token already exists
 125                         var stmt = select("ROWID FROM tokens WHERE token={token.to_sql_string}")
 126                         assert stmt != null
 127                         if stmt.iterator.to_a.not_empty then continue
 128
 129                         # Register token
 130                         var res = insert("INTO tokens(user, token) VALUES({user_id}, {token.to_sql_string})")
 131                         assert res
 132
 133                         # TODO merge the 2 requests to avoid race conditions,
 134                         # if we ever share the BD between 2 servers/threads
 135
 136                         return token
 137                 end
 138         end
 139
 140         # Get the user id associated to `token`, if any
 141         #
 142         # Accepts `null` token to simplify call sites, but always returns `null` in such cases.
 143         fun token_to_id(token: nullable String): nullable Int
 144         do
 145                 if token == null then return null
 146
 147                 var stmt = select("user FROM tokens WHERE token={token.to_sql_string}")
 148                 if stmt == null then
 149                         print_error "Select 'token_to_id' failed with: {error or else "?"}"
 150                         return null
 151                 end
 152
 153                 # TODO update token timestamp and platform/client hint of last connection.
 154                 # These informations could help detect malicious access to the account.
 155
 156                 for row in stmt do return row[0].to_i
 157                 return null
 158         end
 159
 160         # Get `User` data from the integer `id`
 161         fun id_to_user(id: Int): nullable User
 162         do
 163                 var stmt = select("name FROM users WHERE ROWID = {id}")
 164                 assert stmt != null
 165
 166                 for row in stmt do return new User(id, row[0].to_s)
 167                 return null
 168         end
 169
 170         # Try to sign up a new user, return `true` on success
 171         fun signup(user, pass, email: String): nullable String
 172         do
 173                 # Check if already in user
 174                 var stmt = select("ROWID FROM users WHERE lower({user.to_sql_string}) = lower(name)")
 175                 assert stmt != null else print_error "Select 'sign_up' failed with: {error or else "?"}"
 176                 if not stmt.iterator.to_a.is_empty then return "Username already in use"
 177
 178                 # Check email use
 179                 stmt = select("ROWID FROM users WHERE lower({email.to_sql_string}) = lower(email)")
 180                 assert stmt != null else print_error "Select 'sign_up' failed with: {error or else "?"}"
 181                 if not stmt.iterator.to_a.is_empty then return "There's already an account with that email"
 182
 183                 # Insert intro BD
 184                 assert insert("INTO users(name, pass, email) VALUES ({user.to_sql_string}, {pass.to_sql_string}, {email.to_sql_string})") else
 185                         print_error "Insert 'sign_up' failed with: {error or else "?"}"
 186                 end
 187
 188                 return null
 189         end
 190
 191         # Post a review
 192         fun post_review(author, beer, rating: Int, text: String)
 193         do
 194                 assert insert("OR REPLACE INTO reviews(author, beer, rating, text) VALUES ({author}, {beer}, {rating}, {text.to_sql_string})") else
 195                         print_error "Insert 'post_review' failed with: {error or else "?"}"
 196                 end
 197         end
 198
 199         # Fetch stats on `beer`, if `followed_only` limit to the reviews of followed users
 200         fun beer_stats(beer: Int, followed_only: nullable Int): nullable BeerStats
 201         do
 202                 var sql = "avg(rating), count(rating) FROM reviews WHERE beer = {beer}"
 203                 if followed_only != null then
 204                         sql += " AND (author = {followed_only} OR " +
 205                           "author IN (SELECT user_to FROM follows WHERE user_from = {followed_only}))"
 206                 end
 207
 208                 var stmt = select(sql)
 209                 assert stmt != null else print_error "Select 'beer_stats' failed with: {error or else "?"}"
 210
 211                 var b = beer_from_id(beer)
 212                 if b == null then return null
 213
 214                 for row in stmt do return new BeerStats(b, row[0].to_f, row[1].to_i)
 215                 return null
 216         end
 217
 218         # Fetch the most recent rating left by `user_id` about `beer`
 219         fun latest_rating(user_id, beer: Int): nullable Int
 220         do
 221                 var stmt = select("rating FROM reviews WHERE author = {user_id} AND beer = {beer} ORDER BY ROWID DESC LIMIT 1")
 222                 assert stmt != null else print_error "Select 'rating' failed with: {error or else "?"}"
 223                 for row in stmt do return row[0].to_i
 224                 return null
 225         end
 226
 227         # Register that `user_from` follows `user_to`
 228         fun add_followed(user_from, user_to: Int)
 229         do
 230                 assert insert("OR IGNORE INTO follows(user_from, user_to) VALUES ({user_from}, {user_to})") else
 231                         print_error "Insert 'add_followed' failed with: {error or else "?"}"
 232                 end
 233         end
 234
 235         # Register that `user_from` does not follow `user_to`
 236         fun remove_followed(user_from, user_to: Int)
 237         do
 238                 assert execute("DELETE FROM follows WHERE user_from = {user_from} AND user_to = {user_to}") else
 239                         print_error "Delete 'remove_followed' failed with: {error or else "?"}"
 240                 end
 241         end
 242
 243         # Does `user_from` follow `user_to`?
 244         fun follows(user_from, user_to: Int): Bool
 245         do
 246                 var stmt = select("ROWID FROM follows WHERE user_from = {user_from} AND user_to = {user_to}")
 247                 assert stmt != null else
 248                         print_error "Select 'follows' failed with: {error or else "?"}"
 249                 end
 250                 for row in stmt do return true
 251                 return false
 252         end
 253
 254         # List of users with a name similar to `pattern` in relation to `user_id`
 255         fun search_users(pattern: String, user_id: nullable Int): nullable Array[UserAndFollowing]
 256         do
 257                 # TODO a better search logic
 258
 259                 var like_str = "'%{pattern.replace('\\', "\\\\").replace('\'', "''").replace("%", "\\%")}%'"
 260                 var stmt = select("ROWID, name FROM users WHERE name LIKE {like_str}")
 261                 assert stmt != null else print_error "Select 'search_users' failed with: {error or else "?"}"
 262
 263                 var users = new Array[User]
 264                 for row in stmt do users.add(new User(row[0].to_i, row[1].to_s))
 265
 266                 return user_to_user_and_following(users, user_id)
 267         end
 268
 269         # List the followers of `user_id`
 270         fun followers(user_id: Int): nullable Array[UserAndFollowing]
 271         do
 272                 var stmt = select("ROWID, name FROM users WHERE ROWID in (SELECT user_from FROM follows WHERE user_to = {user_id})")
 273                 assert stmt != null else print_error "Select 'followers' failed with: {error or else "?"}"
 274
 275                 var users = new Array[User]
 276                 for row in stmt do users.add(new User(row[0].to_i, row[1].to_s))
 277
 278                 return user_to_user_and_following(users, user_id)
 279         end
 280
 281         # List users followed by `user_id`
 282         fun followed(user_id: Int): nullable Array[UserAndFollowing]
 283         do
 284                 var stmt = select("ROWID, name FROM users WHERE ROWID in (SELECT user_to FROM follows WHERE user_from = {user_id})")
 285                 assert stmt != null else print_error "Select 'followed' failed with: {error or else "?"}"
 286
 287                 var users = new Array[User]
 288                 for row in stmt do users.add(new User(row[0].to_i, row[1].to_s))
 289
 290                 return user_to_user_and_following(users, user_id)
 291         end
 292
 293         # List reciprocal friends of `user_id`
 294         fun followed_followers(user_id: Int): nullable Array[User]
 295         do
 296                 var stmt = select("ROWID, name FROM users WHERE " +
 297                         "ROWID in (SELECT user_from FROM follows WHERE user_to = {user_id}) AND " +
 298                         "ROWID in (SELECT user_to FROM follows WHERE user_from = {user_id})")
 299                 assert stmt != null else print_error "Select 'followed_followers' failed with: {error or else "?"}"
 300
 301                 var users = new Array[User]
 302                 for row in stmt do users.add new User(row[0].to_i, row[1].to_s)
 303
 304                 return users
 305         end
 306
 307         # List `n` friends or recommendations (with information)
 308         #
 309         # If `user_id` is null, list only recommendations.
 310         # The default value of `n` is 6 friends.
 311         fun friends(user_id, n: nullable Int): nullable Array[UserAndFollowing]
 312         do
 313                 var limit = n or else 6
 314                 var people = null
 315                 if user_id != null then
 316                         people = followed_followers(user_id)
 317                         if people == null then return null
 318                 else
 319                         people = new Array[User]
 320                 end
 321
 322                 if people.length < limit then
 323                         # Add recommendations
 324                         # TODO fill with recommendations from a few friends
 325                         # TODO starting friends recommendations, popular clients, same visit patterns,
 326                         # currently at the bar, official people (proprio, brewers, barmaids & barmen)
 327
 328                         limit -= people.length
 329                         user_id = user_id or else -1
 330
 331                         # Recommend popular users
 332                         var stmt = select("""
 333 ROWID, name, (SELECT count(*) FROM follows WHERE follows.user_to == users.ROWID) AS n_followers
 334 FROM users WHERE ROWID != {{{user_id}}}
 335 ORDER BY n_followers DESC LIMIT {{{limit}}}""")
 336                         assert stmt != null else print_error "Select 'friends' failed with: {error or else "?"}"
 337                         for row in stmt do people.add new User(row[0].to_i, row[1].to_s)
 338
 339                 else if people.length > limit then
 340
 341                         # TODO pass the limit `n` to followed_followers
 342                         people = people.subarray(0, limit)
 343                 end
 344
 345                 return user_to_user_and_following(people, user_id)
 346         end
 347
 348         # Convert `users` to an `Array[UserAndFollowing]` in relation to `user_id`
 349         private fun user_to_user_and_following(users: Array[User], user_id: nullable Int): Array[UserAndFollowing]
 350         do
 351                 var users_and_f = new Array[UserAndFollowing]
 352                 for user in users do
 353                         var uaf
 354                         var favs = favorite_beers(user.id)
 355                         if user_id != null then
 356                                 var following = follows(user_id, user.id)
 357                                 var followed_by = follows(user.id, user_id)
 358                                 uaf = new UserAndFollowing(user, favs.join(", "), following, followed_by)
 359                         else
 360                                 uaf = new UserAndFollowing(user, favs.join(", "), false, false)
 361                         end
 362                         users_and_f.add uaf
 363                 end
 364                 return users_and_f
 365         end
 366
 367         # List the name of the favorite beers of `user_id`
 368         fun favorite_beers(user_id: Int): Array[String]
 369         do
 370                 var stmt = select("name FROM beers WHERE ROWID in (SELECT beer FROM reviews WHERE author = {user_id} AND rating = 5)")
 371                 assert stmt != null else print_error "Select 'rating' failed with: {error or else "?"}"
 372
 373                 var beers = new Array[String]
 374                 for row in stmt do beers.add row[0].to_s
 375                 return beers
 376         end
 377
 378         # List available beers and changes in relation to `user_from`
 379         fun list_beers_and_rating(user_from: nullable Int, since: nullable String): nullable Array[BeerAndRatings]
 380         do
 381                 # TODO replace old services `beer_events_since` and `beer_events_today` by a single* SQL call
 382
 383                 var events
 384                 if since != null then
 385                         events = beer_events_since(since)
 386                 else events = beer_events_today
 387                 if events == null then return null
 388
 389                 # New since the last weekday
 390                 var all = new Array[BeerAndRatings]
 391                 for beer in events.new_beers do
 392                         var global = beer_stats(beer.id)
 393                         var friend = null
 394                         var rating = null
 395                         if user_from != null then
 396                                 friend = beer_stats(beer.id, user_from)
 397                                 rating = latest_rating(user_from, beer.id)
 398                         end
 399                         all.add new BeerAndRatings(beer, global, friend, rating,
 400                                 false, "TODO batch", true, false, false)
 401                 end
 402
 403                 # Still here since the last weekday
 404                 for beer in events.fix_beers do
 405                         var global = beer_stats(beer.id)
 406                         var friend = null
 407                         var rating = null
 408                         if user_from != null then
 409                                 friend = beer_stats(beer.id, user_from)
 410                                 rating = latest_rating(user_from, beer.id)
 411                         end
 412                         all.add new BeerAndRatings(beer, global, friend, rating,
 413                                 false, "TODO batch", false, true, false)
 414                 end
 415
 416                 # Apply badges
 417                 for beer in all do
 418                         var badges = badges(beer.beer.id, user_from)
 419                         if badges.not_empty then beer.badges = badges
 420                 end
 421
 422                 return all
 423         end
 424
 425         # Badges (or comments, of social description) on `beer` relative to `user_from`
 426         fun badges(beer: Int, user_from: nullable Int): Array[BeerBadge]
 427         do
 428                 var badges = new Array[BeerBadge]
 429
 430                 # Overall favorite available today
 431                 var rows = select("""
 432 beer
 433 FROM (
 434         SELECT beer, AVG(rating) AS average, COUNT(rating) AS count
 435         FROM latest_reviews
 436         WHERE beer IN (
 437                 SELECT beer FROM daily WHERE day IN (
 438                         SELECT max(day) FROM daily))
 439         GROUP BY beer)
 440 WHERE count > 1 ORDER BY average DESC LIMIT 1""")
 441                 if rows == null then
 442                         print_error "Select 'overall-favorite' failed with: {error or else "?"}"
 443                 else
 444                         for row in rows do
 445                                 if row[0].to_i == beer then badges.add new BestBeerBadge
 446                         end
 447                 end
 448
 449                 if user_from != null then
 450                         # Favorite of friend
 451                         rows = select("""
 452 name FROM users
 453 WHERE ROWID in (SELECT user_to FROM follows WHERE user_from = {{{user_from}}})
 454         AND ROWID in (SELECT author FROM latest_reviews WHERE beer = {{{beer}}} AND rating = 5)""")
 455                         if rows == null then
 456                                 print_error "Select 'friend-favorite' failed with: {error or else "?"}"
 457                         else
 458                                 var badge = new FavoriteBeerBadge
 459                                 for row in rows do badge.users.add row[0].to_s
 460                                 if badge.users.not_empty then badges.add badge
 461                         end
 462
 463                         # Rated similarly to a favorite by most (approximative description)
 464                         rows = select("""
 465 (SELECT name FROM beers WHERE beers.ROWID = beer1)
 466 FROM global_dist WHERE beer0 = {{{beer}}} AND beer0 != beer1
 467         AND count > 1 AND average < 2.0
 468         AND beer1 IN (SELECT beer FROM latest_reviews WHERE
 469                 author = {{{user_from}}} AND rating = 5)
 470 ORDER BY average LIMIT 1""")
 471                         if rows == null then
 472                                 print_error "Select 'similar' failed with: {error or else "?"}"
 473                         else
 474                                 var badge = new SimilarBeerBadge
 475                                 for row in rows do badge.beers.add row[0].to_s
 476                                 if badge.beers.not_empty then badges.add badge
 477                         end
 478                 end
 479
 480                 # TODO more for people with no friends or favorites
 481
 482                 return badges
 483         end
 484
 485         # Register `user_id` as being in or out
 486         fun checkin(user_id: Int, checkin: Bool)
 487         do
 488                 var res = insert("INTO checkins(user, is_in) VALUES({user_id}, {if checkin then 1 else 0})")
 489                 if not res then print_error "Insert 'checkin' failed with: {error or else "?"}"
 490         end
 491
 492         # List currently checked in reciprocal friends of `user_id`
 493         fun checkedin_followed_followers(user_id: Int): nullable CheckinReport
 494         do
 495                 var sql = """
 496 ROWID, name FROM users
 497 WHERE 1 in (SELECT is_in FROM checkins WHERE user = users.ROWID ORDER BY ROWID DESC LIMIT 1)
 498         AND ROWID in (SELECT user_from FROM follows WHERE user_to = {user_id})
 499         AND ROWID in (SELECT user_to FROM follows WHERE user_from = {user_id})"""
 500
 501                 var stmt = select(sql)
 502                 if stmt == null then
 503                         print_error "Select 'checkedin_followed_followers' failed with: {error or else "?"}"
 504                         return null
 505                 end
 506
 507                 var report = new CheckinReport
 508                 for row in stmt do report.users.add new User(row[0].to_i, row[1].to_s)
 509                 return report
 510         end
 511 end