application/models/videos_model.php

   1 <?php
   2
   3 /**
   4  * Class Videos_model models videos information from the DB
   5  *
   6  * @category    Model
   7  * @author              Călin-Andrei Burloiu
   8  */
   9 class Videos_model extends CI_Model {
  10         public $db = NULL;
  11
  12         public function __construct()
  13         {
  14                 parent::__construct();
  15
  16                 if ($this->db === NULL)
  17                 {
  18                         $this->load->library('singleton_db');
  19                         $this->db = $this->singleton_db->connect();
  20                 }
  21         }
  22
  23         /**
  24          * Retrieves a set of videos information which can be used for displaying
  25          * that videos as a list with few details.
  26          *
  27          * @param               int $category_id        DB category ID; pass NULL for all
  28          * categories
  29          * @param               mixed $user                     an user_id (as int) or an username
  30          * (as string); pass NULL for all users
  31          * @param               int $offset
  32          * @param               int $count
  33          * @param               string $ordering        control videos ording by these
  34          * possibilities:
  35          * <ul>
  36          *   <li><strong>'hottest':</strong> newest most appreciated first. An
  37          *   appreciated video is one which has a bigger
  38          *   score = views + likes - dislikes.</li>
  39          *   <li><strong>'newest':</strong> newest first.</li>
  40          *   <li><strong>'alphabetically':</strong> sort alphabetically.</li>
  41          * </ul>
  42          * @return              array   a list of videos, each one being an assoc array with:
  43          * <ul>
  44          *   <li>id, name, title, duration, thumbs_count, default_thumb, views => from DB</li>
  45          *   <li>shorted_title => ellipsized title</li>
  46          *   <li>video_url => P2P-Tube video URl</li>
  47          *   <li>user_id, user_name</li>
  48          *   <li>thumbs => thumbnail images' URLs</li>
  49          * </ul>
  50          */
  51         public function get_videos_summary($category_id, $user, $offset, $count,
  52                 $ordering = 'hottest')
  53         {
  54                 $this->load->helper('text');
  55
  56                 // Ordering
  57                 switch ($ordering)
  58                 {
  59                 case 'hottest':
  60                         $order_statement = "ORDER BY date DESC, score DESC, RAND()";
  61                         break;
  62                 case 'newest':
  63                         $order_statement = "ORDER BY date DESC";
  64                         break;
  65                 case 'alphabetically':
  66                         $order_statement = "ORDER BY title";
  67                         break;
  68
  69                 default:
  70                         $order_statement = "";
  71                 }
  72
  73                 // Category filtering
  74                 if ($category_id === NULL)
  75                         $cond_category = "1";
  76                 else
  77                 {
  78                         $category_id = intval($category_id);
  79                         $cond_category = "category_id = $category_id";
  80                 }
  81
  82                 // User filtering
  83                 if ($user === NULL)
  84                         $cond_user = "1";
  85                 else
  86                 {
  87                         if (is_int($user))
  88                                 $cond_user = "v.user_id = $user";
  89                         else if (is_string($user))
  90                                 $cond_user = "u.username = '$user'";
  91                 }
  92
  93                 $query = $this->db->query(
  94                         "SELECT v.id, name, title, duration, user_id, u.username, views,
  95                                 thumbs_count, default_thumb,
  96                                 (views + likes - dislikes) AS score
  97                         FROM `videos` v, `users` u
  98                         WHERE v.user_id = u.id AND $cond_category AND $cond_user
  99                         $order_statement
 100                         LIMIT $offset, $count");
 101
 102                 if ($query->num_rows() > 0)
 103                         $videos = $query->result_array();
 104                 else
 105                         return array();
 106
 107                 foreach ($videos as & $video)
 108                 {
 109                         // P2P-Tube Video URL
 110                         $video['video_url'] = site_url(sprintf("watch/%d/%s",
 111                                 $video['id'], $video['name']));
 112
 113                         // Thumbnails
 114                         $video['thumbs'] = $this->get_thumbs($video['name'],
 115                                 $video['thumbs_count']);
 116
 117                         // Ellipsized title
 118                         //$video['shorted_title'] = ellipsize($video['title'], 45, 0.75);
 119                         $video['shorted_title'] = character_limiter($video['title'], 50);
 120                 }
 121
 122                 return $videos;
 123         }
 124
 125         /**
 126          * Returns the number of videos from database from a specific category or
 127          * user.
 128          * NULL parameters count videos from all categories and / or all users.
 129          *
 130          * @param int $category_id
 131          * @param mixed $user   an user_id (as int) or an username (as string)
 132          * @return int  number of videos or FALSE if an error occured
 133          */
 134         public function get_videos_count($category_id = NULL, $user = NULL)
 135         {
 136                 if ($category_id === NULL)
 137                         $cond_category = "1";
 138                 else
 139                         $cond_category = "category_id = $category_id";
 140
 141                 if ($user === NULL)
 142                         $cond_user = "1";
 143                 else
 144                 {
 145                         if (is_int($user))
 146                                 $cond_user = "v.user_id = $user";
 147                         else if(is_string($user))
 148                                 $cond_user = "u.username = '$user'";
 149                 }
 150
 151                 $query = $this->db->query(
 152                         "SELECT COUNT(*) count
 153                         FROM `videos` v, `users` u
 154                         WHERE v.user_id = u.id AND $cond_category AND $cond_user");
 155
 156                 if ($query->num_rows() > 0)
 157                         return $query->row()->count;
 158
 159                 // Error
 160                 return FALSE;
 161         }
 162
 163         /**
 164          * Retrieves information about a video.
 165          *
 166          * If $name does not match with the video's `name` from the DB an error is
 167          * marked in the key 'err'. If it's NULL it is ignored.
 168          *
 169          * @access public
 170          * @param string $id    video's `id` column from `videos` DB table
 171          * @param string $name  video's `name` column from `videos` DB
 172          * table. NULL means there is no name provided.
 173          * @return array        an associative list with information about a video
 174          * with the following keys:
 175          * <ul>
 176          *   <li>all columns form DB with some exceptions that are overwritten or new</li>
 177          *   <li>content is moved in assets</li>
 178          *   <li>assets => list of associative lists where each one represents a</li>
 179          * video asset having keys: "src", "res", "par" and "ext". Value of key
 180          * "src" is the video torrent formated as
 181          * {name}_{format}.{video_ext}.{default_torrent_ext}</li>
 182          *   <li>username => user name from `users` table</li>
 183          *   <li>category_title => a human-friendly category name</li>
 184          *   <li>tags => associative list of "tag => score"</li>
 185          *   <li>date => date and time when the video was created</li>
 186          *   <li>thumbs => thumbnail images' URLs</li>
 187          * </ul>
 188          */
 189         public function get_video($id, $name = NULL)
 190         {
 191                 $this->load->helper('video');
 192                 $this->load->helper('text');
 193
 194                 $query = $this->db->query("SELECT v.*, u.username
 195                                                                 FROM `videos` v, `users` u
 196                                                                 WHERE v.user_id = u.id AND v.id = $id");
 197                 $video = array();
 198
 199                 if ($query->num_rows() > 0)
 200                 {
 201                         $video = $query->row_array();
 202                         if ($name !== NULL && $video['name'] != $name)
 203                                 $video['err'] = 'INVALID_NAME';
 204                 }
 205                 else
 206                 {
 207                         $video['err'] = 'INVALID_ID';
 208                         return $video;
 209                 }
 210
 211                 // Convert JSON encoded string to arrays.
 212                 $video['assets'] = json_decode($video['formats'], TRUE);
 213                 unset($video['formats']);
 214                 $video['tags'] = json_decode($video['tags'], TRUE);
 215                 asort($video['tags']);
 216                 $video['tags'] = array_reverse($video['tags'], TRUE);
 217
 218                 // Sort assets by their megapixels number.
 219                 function access_function($a) { return $a['res']; }
 220                 function assets_cmp($a, $b)
 221                         { return megapixels_cmp($a, $b, "access_function"); }
 222                 usort($video['assets'], "assets_cmp");
 223
 224                 // Torrents
 225                 $video['url'] = array();
 226                 foreach ($video['assets'] as & $asset)
 227                 {
 228                         $def = substr($asset['res'], strpos($asset['res'], 'x') + 1) . 'p';
 229                         $asset['def'] = $def;
 230                         $asset['src'] = site_url('data/torrents/'. $video['name'] . '_'
 231                                 . $def . '.'. $asset['ext']
 232                                 . '.'. $this->config->item('default_torrent_ext'));
 233                 }
 234
 235                 // Category title
 236                 $categories = $this->config->item('categories');
 237                 $category_name = $categories[ intval($video['category_id']) ];
 238                 $video['category_title'] = $category_name ?
 239                         $this->lang->line("ui_categ_$category_name") : $category_name;
 240
 241                 // Thumbnails
 242                 $video['thumbs'] = $this->get_thumbs($video['name'], $video['thumbs_count']);
 243
 244                 // Shorted description
 245                 $video['shorted_description'] = character_limiter(
 246                                 $video['description'], 128);
 247
 248                 return $video;
 249         }
 250
 251         public function compute_video_name($title)
 252         {
 253                 $name = str_replace(' ', '-', $title);
 254
 255                 return urlencode($name);
 256         }
 257
 258         /**
 259          * Adds a new uploaded video to the DB.
 260          *
 261          * @param type $title
 262          * @param type $description
 263          * @param type $tags comma separated tags
 264          * @param type $av_info a dictionary of video properties containing keys
 265          * width, height, dar (display aspect ratio), duration and size; is can be
 266          * returned with function get_av_info from video helper
 267          */
 268         public function add_video($title, $description, $tags, $av_info,
 269                         $category_id, $user_id)
 270         {
 271                 $name = $this->compute_video_name($title);
 272
 273                 // Tags.
 274                 $json_tags = array();
 275                 $tok = strtok($tags, ',');
 276                 while ($tok != FALSE)
 277                 {
 278                         $json_tags[trim($tok)] = 0;
 279
 280                         $tok = strtok(',');
 281                 }
 282                 $json_tags = json_encode($json_tags);
 283
 284                 // TODO formats
 285                 $json_formats = '[{"res":"1280x720","ext":"ogv","dar":"16:9"},'
 286                                 . '{"res":"1067x600","ext":"ogv","dar":"16:9"}]';
 287
 288
 289                 $query = $this->db->query("INSERT INTO `videos`
 290                                 (name, title, description, duration, formats, category_id,
 291                                                 user_id, tags, date)
 292                                 VALUES ('$name', '$title', '$description', '"
 293                                                 . $av_info['duration']. "', '$json_formats', $category_id,
 294                                                 $user_id, '$json_tags', utc_timestamp())");
 295                 if ($query === FALSE)
 296                         return FALSE;
 297
 298                 // Find out the id of the new video added.
 299                 $query = $this->db->query("SELECT id from `videos`
 300                                 WHERE name = '$name'");
 301                 if ($query->num_rows() === 0)
 302                         return FALSE;
 303                 $video_id = $query->row()->id;
 304
 305                 // Activation code.
 306                 $activation_code = Videos_model::gen_activation_code();
 307
 308                 $query = $this->db->query("INSERT INTO `videos_unactivated`
 309                                 (video_id, activation_code)
 310                                 VALUES ($video_id, '$activation_code')");
 311         }
 312
 313         /**
 314          * Retrieves comments for a video.
 315          *
 316          * @param int $video_id
 317          * @param int $offset
 318          * @param int $count
 319          * @param string $ordering      control comments ording by these possibilities:
 320          * <ul>
 321          *   <li><strong>'hottest':</strong> newest most appreciated first. An
 322          *   appreciated comment is one which has a bigger
 323          *   score = likes - dislikes.</li>
 324          *   <li><strong>'newest':</strong> newest first.</li>
 325          * </ul>
 326          * @return array        an array with comments
 327          */
 328         public function get_video_comments($video_id, $offset, $count,
 329                         $ordering = 'newest')
 330         {
 331                 $this->load->helper('date');
 332                 $cond_hottest = '';
 333
 334                 // Ordering
 335                 switch ($ordering)
 336                 {
 337                 case 'newest':
 338                         $order_statement = "ORDER BY time DESC";
 339                         break;
 340                 case 'hottest':
 341                         $order_statement = "ORDER BY score DESC, time DESC";
 342                         $cond_hottest = "AND c.likes + c.dislikes > 0";
 343                         break;
 344
 345                 default:
 346                         $order_statement = "";
 347                 }
 348
 349                 $query = $this->db->query(
 350                         "SELECT c.*, u.username, u.time_zone, (c.likes + c.dislikes) AS score
 351                                 FROM `videos_comments` c, `users` u
 352                                 WHERE c.user_id = u.id AND video_id = $video_id $cond_hottest
 353                                 $order_statement
 354                                 LIMIT $offset, $count");
 355
 356                 if ($query->num_rows() == 0)
 357                         return array();
 358
 359                 $comments = $query->result_array();
 360
 361                 foreach ($comments as & $comment)
 362                 {
 363                         $comment['local_time'] = human_gmt_to_human_local($comment['time'],
 364                                 $comment['time_zone']);
 365                 }
 366
 367                 return $comments;
 368         }
 369
 370         public function get_video_comments_count($video_id)
 371         {
 372                 $query = $this->db->query(
 373                                         "SELECT COUNT(*) count
 374                                                 FROM `videos_comments`
 375                                                 WHERE video_id = $video_id");
 376
 377                 if ($query->num_rows() == 0)
 378                         return FALSE;
 379
 380                 return $query->row()->count;
 381         }
 382
 383         /**
 384          * Insert in DB a comment for a video.
 385          *
 386          * @param int $video_id
 387          * @param int $user_id
 388          * @param string $content
 389          */
 390         public function comment_video($video_id, $user_id, $content)
 391         {
 392                 // Prepping content.
 393                 $content = substr($content, 0, 512);
 394                 $content = htmlspecialchars($content);
 395                 $content = nl2br($content);
 396
 397                 return $query = $this->db->query(
 398                         "INSERT INTO `videos_comments` (video_id, user_id, content, time)
 399                         VALUES ($video_id, $user_id, '$content', UTC_TIMESTAMP())");
 400         }
 401
 402         /**
 403          * Increments views count for a video.
 404          *
 405          * @param int $id       DB video id
 406          * @return void
 407          */
 408         public function inc_views($id)
 409         {
 410                 return $this->db->query('UPDATE `videos` '
 411                                                 . 'SET `views`=`views`+1 '
 412                                                 . 'WHERE id='. $id);
 413         }
 414
 415         public function vote($video_id, $user_id, $like = TRUE)
 416         {
 417                 if ($like)
 418                 {
 419                         $col = 'likes';
 420                         $action = 'like';
 421                 }
 422                 else
 423                 {
 424                         $col = 'dislikes';
 425                         $action = 'dislike';
 426                 }
 427
 428                 $query = $this->db->query("SELECT * FROM `users_actions`
 429                         WHERE user_id = $user_id
 430                                 AND target_id = $video_id
 431                                 AND target_type = 'video'
 432                                 AND action = '$action'
 433                                 AND date = CURDATE()");
 434                 // User already voted today
 435                 if ($query->num_rows() > 0)
 436                         return -1;
 437
 438                 $this->db->query("UPDATE `videos`
 439                         SET $col = $col + 1
 440                         WHERE id = $video_id");
 441
 442                 // Mark this action so that the user cannot repeat it today.
 443                 $this->db->query("INSERT INTO `users_actions`
 444                                 (user_id, action, target_type, target_id, date)
 445                         VALUES ( $user_id, '$action', 'video', $video_id, CURDATE() )");
 446
 447                 $query = $this->db->query("SELECT $col FROM `videos`
 448                         WHERE id = $video_id");
 449
 450                 if ($query->num_rows() === 1)
 451                 {
 452                         $row = $query->row_array();
 453                         return $row[ $col ];
 454                 }
 455
 456                 // Error
 457                 return FALSE;
 458         }
 459
 460         public function vote_comment($comment_id, $user_id, $like = TRUE)
 461         {
 462                 if ($like)
 463                 {
 464                         $col = 'likes';
 465                         $action = 'like';
 466                 }
 467                 else
 468                 {
 469                         $col = 'dislikes';
 470                         $action = 'dislike';
 471                 }
 472
 473                 $query = $this->db->query("SELECT * FROM `users_actions`
 474                                 WHERE user_id = $user_id
 475                                         AND target_id = $comment_id
 476                                         AND target_type = 'vcomment'
 477                                         AND action = '$action'
 478                                         AND date = CURDATE()");
 479                 // User already voted today
 480                 if ($query->num_rows() > 0)
 481                         return -1;
 482
 483                 $this->db->query("UPDATE `videos_comments`
 484                                 SET $col = $col + 1
 485                                 WHERE id = $comment_id");
 486
 487                 // Mark this action so that the user cannot repeat it today.
 488                 $this->db->query("INSERT INTO `users_actions`
 489                                         (user_id, action, target_type, target_id, date)
 490                                 VALUES ( $user_id, '$action', 'vcomment', $comment_id, CURDATE() )");
 491
 492                 $query = $this->db->query("SELECT $col FROM `videos_comments`
 493                                 WHERE id = $comment_id");
 494
 495                 if ($query->num_rows() === 1)
 496                 {
 497                         $row = $query->row_array();
 498                         return $row[ $col ];
 499                 }
 500
 501                 // Error
 502                 return FALSE;
 503         }
 504
 505         public function get_thumbs($name, $count)
 506         {
 507                 $thumbs = array();
 508
 509                 for ($i=0; $i < $count; $i++)
 510                         $thumbs[] = site_url(sprintf("data/thumbs/%s_t%02d.jpg", $name, $i));
 511
 512                 return $thumbs;
 513         }
 514
 515         /**
 516          * Searches videos in DB based on a search query string and returns an
 517          * associative array of results.
 518          * If count is zero the function only return the number of results.
 519          * @param string $search_query
 520          * @param int $offset
 521          * @param int $count
 522          * @param int $category_id      if NULL, all categories are searched
 523          * @return array        an associative array with the same keys as that from
 524          * get_videos_summary's result, but with two additional keys:
 525          * description and date.
 526          */
 527         public function search_videos($search_query, $offset = 0, $count = 0,
 528                                                                         $category_id = NULL)
 529         {
 530                 $search_query = trim($search_query);
 531                 $search_query = str_replace("'", " ", $search_query);
 532
 533                 // Search word fragments.
 534                 // sfc = search fragment condition
 535                 $sfc = "( ";
 536                 // sfr = search fragment relevance
 537                 $sfr = "( ";
 538                 $sep = ' +-*<>()~"';
 539                 $fragm = strtok($search_query, $sep);
 540                 while ($fragm !== FALSE)
 541                 {
 542                         $sfc .= "(title LIKE '%$fragm%'
 543                                         OR description LIKE '%$fragm%'
 544                                         OR tags LIKE '%$fragm%') OR ";
 545
 546                         // Frament relevances are half of boolean relevances such
 547                         // that they will appear at the end of the results.
 548                         $sfr .= "0.25 * (title LIKE '%$fragm%')
 549                                         + 0.1 * (description LIKE '%$fragm%')
 550                                         + 0.15 * (tags LIKE '%$fragm%') + ";
 551
 552                         $fragm = strtok($sep);
 553                 }
 554                 $sfc = substr($sfc, 0, -4) . " )";
 555                 $sfr = substr($sfr, 0, -3) . " )";
 556
 557                 if (! $this->is_advanced_search_query($search_query))
 558                 {
 559                         $search_cond = "MATCH (title, description, tags)
 560                                         AGAINST ('$search_query') OR $sfc";
 561                         $relevance = "( MATCH (title, description, tags)
 562                                         AGAINST ('$search_query') + $sfr ) AS relevance";
 563                 }
 564                 // boolean mode
 565                 else
 566                 {
 567                         $against = "AGAINST ('$search_query' IN BOOLEAN MODE)";
 568                         $search_cond = "( MATCH (title, description, tags)
 569                                         $against) OR $sfc";
 570                         $relevance = "( 0.5 * (MATCH(title) $against)
 571                                         + 0.3 * (MATCH(tags) $against)
 572                                         + 0.2 * (MATCH(description) $against)
 573                                         + $sfr) AS relevance";
 574                 }
 575
 576                 if ($count === 0)
 577                 {
 578                         $selected_columns = "COUNT(*) count";
 579                         $order = "";
 580                         $limit = "";
 581                 }
 582                 else
 583                 {
 584                         // TODO select data, description if details are needed
 585                         $selected_columns = "v.id, name, title, duration, user_id, views,
 586                                         thumbs_count, default_thumb, u.username,
 587                                         (views + likes - dislikes) AS score,
 588                                         $relevance";
 589                         $order = "ORDER BY relevance DESC, score DESC";
 590                         $limit = "LIMIT $offset, $count";
 591                 }
 592
 593                 if ($category_id !== NULL)
 594                         $category_cond = "category_id = '$category_id' AND ";
 595                 else
 596                         $category_cond = "";
 597
 598                 $str_query = "SELECT $selected_columns
 599                         FROM `videos` v, `users` u
 600                         WHERE  v.user_id = u.id AND $category_cond ( $search_cond )
 601                         $order
 602                         $limit";
 603 //              echo "<p>$str_query</p>";
 604                 $query = $this->db->query($str_query);
 605
 606                 if ($query->num_rows() > 0)
 607                 {
 608                         if ($count === 0)
 609                                 return $query->row()->count;
 610                         else
 611                                 $videos = $query->result_array();
 612                 }
 613                 else
 614                         return NULL;
 615
 616                 $this->load->helper('text');
 617
 618                 foreach ($videos as & $video)
 619                 {
 620                         // P2P-Tube Video URL
 621                         $video['video_url'] = site_url(sprintf("watch/%d/%s",
 622                                 $video['id'], $video['name']));
 623
 624                         // Thumbnails
 625                         $video['thumbs'] = $this->get_thumbs($video['name'],
 626                                 $video['thumbs_count']);
 627
 628                         // Ellipsized title
 629                         //$video['shorted_title'] = ellipsize($video['title'], 45, 0.75);
 630                         $video['shorted_title'] = character_limiter($video['title'], 50);
 631
 632                         // TODO: user information
 633                         $video['user_name'] = 'TODO';
 634                 }
 635
 636                 return $videos;
 637         }
 638
 639         public function decode_search_query($search_query)
 640         {
 641                 $search_query = urldecode($search_query);
 642
 643                 $search_query = str_replace('_AST_', '*', $search_query);
 644                 $search_query = str_replace('_AND_', '+', $search_query);
 645                 $search_query = str_replace('_GT_', '>', $search_query);
 646                 $search_query = str_replace('_LT_', '<', $search_query);
 647                 $search_query = str_replace('_PO_', '(', $search_query);
 648                 $search_query = str_replace('_PC_', ')', $search_query);
 649                 $search_query = str_replace('_LOW_', '~', $search_query);
 650                 $search_query = str_replace('_QUO_', '"', $search_query);
 651
 652                 return $search_query;
 653         }
 654
 655         public function encode_search_query($search_query)
 656         {
 657                 $search_query = str_replace('*', '_AST_', $search_query);
 658                 $search_query = str_replace('+', '_AND_', $search_query);
 659                 $search_query = str_replace('>', '_GT_', $search_query);
 660                 $search_query = str_replace('<', '_LT_', $search_query);
 661                 $search_query = str_replace('(', '_PO_', $search_query);
 662                 $search_query = str_replace(')', '_PC_', $search_query);
 663                 $search_query = str_replace('~', '_LOW_', $search_query);
 664                 $search_query = str_replace('"', '_QUO_', $search_query);
 665
 666                 $search_query = urlencode($search_query);
 667
 668                 return $search_query;
 669         }
 670
 671         /**
 672          * Return TRUE if it contains any special caracter from an advanced search
 673          * query.
 674          * @param string $search_query
 675          * @return boolean
 676          */
 677         public function is_advanced_search_query($search_query)
 678         {
 679                 return (preg_match('/\*|\+|\-|>|\<|\(|\)|~|"/', $search_query) == 0
 680                         ? FALSE : TRUE);
 681         }
 682
 683         public static function gen_activation_code()
 684         {
 685                 $ci =& get_instance();
 686
 687                 $activation_code = substr(
 688                         sha1($ci->config->item('encryption_key')
 689                                 . mt_rand()),
 690                         0,
 691                         16);
 692
 693                 return $activation_code;
 694         }
 695 }
 696
 697 /* End of file videos_model.php */
 698 /* Location: ./application/models/videos_model.php */