4 * Class Videos_model models videos information from the DB
7 * @author Călin-Andrei Burloiu
9 class Videos_model extends CI_Model {
12 public function __construct()
14 parent::__construct();
16 if ($this->db === NULL)
18 $this->load->library('singleton_db');
19 $this->db = $this->singleton_db->connect();
24 * Retrieves a set of videos information which can be used for displaying
25 * that videos as a list with few details.
27 * @param int $category_id DB category ID; pass NULL for all
29 * @param mixed $user an user_id (as int) or an username
30 * (as string); pass NULL for all users
32 * @param int $count number of videos to retrieve; if set to TRUE this
33 * method will retrieve the number of videos that satisfy condition
34 * @param string $ordering control videos ording by these
37 * <li><strong>'hottest':</strong> newest most appreciated first. An
38 * appreciated video is one which has a bigger
39 * score = views + likes - dislikes.</li>
40 * <li><strong>'newest':</strong> newest first.</li>
41 * <li><strong>'alphabetically':</strong> sort alphabetically.</li>
43 * @param bool $unactivated whether to retrieve or not ingested unactivated
44 * videos; typically only administrators should see this kind of assets
45 * @return array a list of videos, each one being an assoc array with:
47 * <li>id, name, title, duration, thumbs_count, default_thumb, views => from DB</li>
48 * <li>shorted_title => ellipsized title</li>
49 * <li>video_url => P2P-Tube video URl</li>
50 * <li>user_id, user_name</li>
51 * <li>thumbs => thumbnail images' URLs</li>
54 public function get_videos_summary($category_id, $user, $offset, $count,
55 $ordering = 'hottest', $unactivated = FALSE)
57 $this->load->helper('text');
59 $order_statement = "";
66 $order_statement = "ORDER BY date DESC, score DESC, RAND()";
69 $order_statement = "ORDER BY date DESC";
71 case 'alphabetically':
72 $order_statement = "ORDER BY title";
76 $order_statement = "";
80 // Show unactivated videos.
81 $cond_unactivated = ($unactivated
82 ? '(a.activation_code IS NULL OR a.activation_code IS NOT NULL
83 AND a.content_ingested = 1)'
84 : 'a.activation_code IS NULL');
87 if ($category_id === NULL)
91 $category_id = intval($category_id);
92 $cond_category = "category_id = $category_id";
101 $cond_user = "v.user_id = $user";
102 else if (is_string($user))
103 $cond_user = "u.username = '$user'";
107 $fields = "COUNT(*) count";
109 $fields = "v.id, name, title, duration, user_id, u.username, views,
110 thumbs_count, default_thumb,
111 (views + likes - dislikes) AS score,
112 a.activation_code, a.content_ingested";
114 $query = $this->db->query(
117 LEFT JOIN `videos_unactivated` a ON (id = a.video_id),
119 WHERE v.user_id = u.id AND $cond_category AND $cond_user
120 AND $cond_unactivated
122 LIMIT $offset, $count");
124 if ($query->num_rows() > 0)
127 return $query->row()->count;
129 $videos = $query->result_array();
134 foreach ($videos as & $video)
136 // P2P-Tube Video URL
137 $video['video_url'] = site_url(sprintf("watch/%d/%s",
138 $video['id'], $video['name']));
141 $video['thumbs'] = $this->get_thumbs($video['name'],
142 $video['thumbs_count']);
143 $video['json_thumbs'] = json_encode($video['thumbs']);
146 //$video['shorted_title'] = ellipsize($video['title'], 45, 0.75);
147 $video['shorted_title'] = character_limiter($video['title'], 50);
154 * Returns the number of videos from database from a specific category or
156 * NULL parameters count videos from all categories and / or all users.
158 * @param int $category_id
159 * @param mixed $user an user_id (as int) or an username (as string)
160 * @return int number of videos or FALSE if an error occured
162 public function get_videos_count($category_id = NULL, $user = NULL,
163 $unactivated = FALSE)
165 return $this->get_videos_summary($category_id, $user, 0, TRUE, NULL,
170 * Retrieves information about a video.
172 * If $name does not match with the video's `name` from the DB an error is
173 * marked in the key 'err'. If it's NULL it is ignored.
176 * @param string $id video's `id` column from `videos` DB table
177 * @param string $name video's `name` column from `videos` DB
178 * table. NULL means there is no name provided.
179 * @return array an associative list with information about a video
180 * with the following keys:
182 * <li>all columns form DB with some exceptions that are overwritten or new</li>
183 * <li>content is moved in assets</li>
184 * <li>assets => list of associative lists where each one represents a</li>
185 * video asset having keys: "src", "res", "par" and "ext". Value of key
186 * "src" is the video torrent formated as
187 * {name}_{format}.{video_ext}.{default_torrent_ext}</li>
188 * <li>username => user name from `users` table</li>
189 * <li>category_title => a human-friendly category name</li>
190 * <li>tags => associative list of "tag => score"</li>
191 * <li>date => date and time when the video was created</li>
192 * <li>thumbs => thumbnail images' URLs</li>
195 public function get_video($id, $name = NULL)
197 $this->load->helper('video');
198 $this->load->helper('text');
200 $query = $this->db->query("SELECT v.*, u.username,
201 a.activation_code, a.content_ingested
203 LEFT JOIN `videos_unactivated` a ON (v.id = a.video_id),
205 WHERE v.user_id = u.id AND v.id = $id");
208 if ($query->num_rows() > 0)
210 $video = $query->row_array();
211 if ($name !== NULL && $video['name'] != $name)
212 $video['err'] = 'INVALID_NAME';
219 // Convert JSON encoded string to arrays.
220 $video['assets'] = json_decode($video['formats'], TRUE);
221 unset($video['formats']);
222 $video['tags'] = json_decode($video['tags'], TRUE);
223 asort($video['tags']);
224 $video['tags'] = array_reverse($video['tags'], TRUE);
226 // Sort assets by their megapixels number.
227 function access_function($a) { return $a['res']; }
228 function assets_cmp($a, $b)
229 { return megapixels_cmp($a, $b, "access_function"); }
230 usort($video['assets'], "assets_cmp");
233 $video['url'] = array();
234 foreach ($video['assets'] as & $asset)
236 $def = substr($asset['res'], strpos($asset['res'], 'x') + 1) . 'p';
237 $asset['def'] = $def;
238 $asset['src'] = site_url('data/torrents/'. $video['name'] . '_'
239 . $def . '.'. $asset['ext']
240 . '.'. $this->config->item('default_torrent_ext'));
244 $categories = $this->config->item('categories');
245 $category_name = $categories[ intval($video['category_id']) ];
246 $video['category_title'] = $category_name ?
247 $this->lang->line("ui_categ_$category_name") : $category_name;
250 $video['thumbs'] = $this->get_thumbs($video['name'], $video['thumbs_count']);
252 // Shorted description
253 $video['shorted_description'] = character_limiter(
254 $video['description'], 128);
260 * Adds a new uploaded video to the DB.
262 * @param string $name
263 * @param string $title
264 * @param string $description
265 * @param string $tags comma separated tags
266 * @param string $duration video duration formatted [HH:]mm:ss
267 * @param array $formats a dictionary corresponding to `formats` JSON
268 * column from `videos` table
269 * @param int $category_id
270 * @param int $user_id
271 * @param string $uploaded_file the raw video file uploaded by the user
272 * @return mixed returns an activation code on success or FALSE otherwise
274 public function add_video($name, $title, $description, $tags, $duration,
275 $formats, $category_id, $user_id, $uploaded_file)
277 $this->load->config('content_ingestion');
280 $json_tags = array();
281 $tok = strtok($tags, ',');
282 while ($tok != FALSE)
284 $json_tags[trim($tok)] = 0;
288 $json_tags = json_encode($json_tags);
290 $json_formats = json_encode($formats);
293 $thumbs_count = $this->config->item('thumbs_count');
294 $default_thumb = rand(0, $thumbs_count - 1);
296 $query = $this->db->query("INSERT INTO `videos`
297 (name, title, description, duration, formats, category_id,
298 user_id, tags, date, thumbs_count, default_thumb)
299 VALUES ('$name', '$title', '$description', '$duration',
300 '$json_formats', $category_id,
301 $user_id, '$json_tags', utc_timestamp(),
302 $thumbs_count, $default_thumb)");
303 if ($query === FALSE)
306 // Find out the id of the new video added.
307 $query = $this->db->query("SELECT id from `videos`
308 WHERE name = '$name'");
309 if ($query->num_rows() === 0)
311 $video_id = $query->row()->id;
314 $activation_code = Videos_model::gen_activation_code();
316 $query = $this->db->query("INSERT INTO `videos_unactivated`
317 (video_id, activation_code, uploaded_file)
318 VALUES ($video_id, '$activation_code', '$uploaded_file')");
320 return $activation_code;
323 public function set_content_ingested($activation_code)
325 return $this->db->query("UPDATE `videos_unactivated`
326 SET content_ingested = 1
327 WHERE activation_code = '$activation_code'");
331 * Activates a video by deleting its entry from `videos_unactivated`.
333 * @param mixed $code_or_id use type string for activation_code or type
335 * @return boolean TRUE on success, FALSE otherwise
337 public function activate_video($code_or_id)
339 if (is_string($code_or_id))
340 $query = $this->db->query("SELECT uploaded_file from `videos_unactivated`
341 WHERE activation_code = '$code_or_id'");
342 else if (is_int($code_or_id))
343 $query = $this->db->query("SELECT uploaded_file from `videos_unactivated`
344 WHERE video_id = '$code_or_id'");
348 if ($query->num_rows() > 0)
349 $uploaded_file = $query->row()->uploaded_file;
353 if (is_string($code_or_id))
354 $query = $this->db->query("DELETE FROM `videos_unactivated`
355 WHERE activation_code = '$code_or_id'");
356 else if (is_int($code_or_id))
357 $query = $this->db->query("DELETE FROM `videos_unactivated`
358 WHERE video_id = '$code_or_id'");
365 return unlink("data/upload/$uploaded_file");
368 public function get_unactivated_videos()
370 $query = $this->db->query("SELECT a.video_id, v.name, a.content_ingested
371 FROM `videos_unactivated` a, `videos` v
372 WHERE a.video_id = v.id AND a.content_ingested = 1");
374 if ($query->num_rows() > 0)
375 return $query->result_array();
381 * Retrieves comments for a video.
383 * @param int $video_id
386 * @param string $ordering control comments ording by these possibilities:
388 * <li><strong>'hottest':</strong> newest most appreciated first. An
389 * appreciated comment is one which has a bigger
390 * score = likes - dislikes.</li>
391 * <li><strong>'newest':</strong> newest first.</li>
393 * @return array an array with comments
395 public function get_video_comments($video_id, $offset, $count,
396 $ordering = 'newest')
398 $this->load->helper('date');
405 $order_statement = "ORDER BY time DESC";
408 $order_statement = "ORDER BY score DESC, time DESC";
409 $cond_hottest = "AND c.likes + c.dislikes > 0";
413 $order_statement = "";
416 $query = $this->db->query(
417 "SELECT c.*, u.username, u.time_zone, (c.likes + c.dislikes) AS score
418 FROM `videos_comments` c, `users` u
419 WHERE c.user_id = u.id AND video_id = $video_id $cond_hottest
421 LIMIT $offset, $count");
423 if ($query->num_rows() == 0)
426 $comments = $query->result_array();
428 foreach ($comments as & $comment)
430 $comment['local_time'] = human_gmt_to_human_local($comment['time'],
431 $comment['time_zone']);
437 public function get_video_comments_count($video_id)
439 $query = $this->db->query(
440 "SELECT COUNT(*) count
441 FROM `videos_comments`
442 WHERE video_id = $video_id");
444 if ($query->num_rows() == 0)
447 return $query->row()->count;
451 * Insert in DB a comment for a video.
453 * @param int $video_id
454 * @param int $user_id
455 * @param string $content
457 public function comment_video($video_id, $user_id, $content)
460 $content = substr($content, 0, 512);
461 $content = htmlspecialchars($content);
462 $content = nl2br($content);
464 return $query = $this->db->query(
465 "INSERT INTO `videos_comments` (video_id, user_id, content, time)
466 VALUES ($video_id, $user_id, '$content', UTC_TIMESTAMP())");
470 * Increments views count for a video.
472 * @param int $id DB video id
475 public function inc_views($id)
477 return $this->db->query('UPDATE `videos` '
478 . 'SET `views`=`views`+1 '
482 public function vote($video_id, $user_id, $like = TRUE)
495 $query = $this->db->query("SELECT * FROM `users_actions`
496 WHERE user_id = $user_id
497 AND target_id = $video_id
498 AND target_type = 'video'
499 AND action = '$action'
500 AND date = CURDATE()");
501 // User already voted today
502 if ($query->num_rows() > 0)
505 $this->db->query("UPDATE `videos`
507 WHERE id = $video_id");
509 // Mark this action so that the user cannot repeat it today.
510 $this->db->query("INSERT INTO `users_actions`
511 (user_id, action, target_type, target_id, date)
512 VALUES ( $user_id, '$action', 'video', $video_id, CURDATE() )");
514 $query = $this->db->query("SELECT $col FROM `videos`
515 WHERE id = $video_id");
517 if ($query->num_rows() === 1)
519 $row = $query->row_array();
527 public function vote_comment($comment_id, $user_id, $like = TRUE)
540 $query = $this->db->query("SELECT * FROM `users_actions`
541 WHERE user_id = $user_id
542 AND target_id = $comment_id
543 AND target_type = 'vcomment'
544 AND action = '$action'
545 AND date = CURDATE()");
546 // User already voted today
547 if ($query->num_rows() > 0)
550 $this->db->query("UPDATE `videos_comments`
552 WHERE id = $comment_id");
554 // Mark this action so that the user cannot repeat it today.
555 $this->db->query("INSERT INTO `users_actions`
556 (user_id, action, target_type, target_id, date)
557 VALUES ( $user_id, '$action', 'vcomment', $comment_id, CURDATE() )");
559 $query = $this->db->query("SELECT $col FROM `videos_comments`
560 WHERE id = $comment_id");
562 if ($query->num_rows() === 1)
564 $row = $query->row_array();
572 public function get_thumbs($name, $count)
576 for ($i=0; $i < $count; $i++)
577 $thumbs[] = site_url(sprintf("data/thumbs/%s_t%02d.jpg", $name, $i));
583 * Searches videos in DB based on a search query string and returns an
584 * associative array of results.
585 * If count is zero the function only return the number of results.
586 * @param string $search_query
589 * @param int $category_id if NULL, all categories are searched
590 * @return array an associative array with the same keys as that from
591 * get_videos_summary's result, but with two additional keys:
592 * description and date.
594 public function search_videos($search_query, $offset = 0, $count = 0,
597 $search_query = trim($search_query);
598 $search_query = str_replace("'", " ", $search_query);
600 // Search word fragments.
601 // sfc = search fragment condition
603 // sfr = search fragment relevance
606 $fragm = strtok($search_query, $sep);
607 while ($fragm !== FALSE)
609 $sfc .= "(title LIKE '%$fragm%'
610 OR description LIKE '%$fragm%'
611 OR tags LIKE '%$fragm%') OR ";
613 // Frament relevances are half of boolean relevances such
614 // that they will appear at the end of the results.
615 $sfr .= "0.25 * (title LIKE '%$fragm%')
616 + 0.1 * (description LIKE '%$fragm%')
617 + 0.15 * (tags LIKE '%$fragm%') + ";
619 $fragm = strtok($sep);
621 $sfc = substr($sfc, 0, -4) . " )";
622 $sfr = substr($sfr, 0, -3) . " )";
624 if (! $this->is_advanced_search_query($search_query))
626 $search_cond = "MATCH (title, description, tags)
627 AGAINST ('$search_query') OR $sfc";
628 $relevance = "( MATCH (title, description, tags)
629 AGAINST ('$search_query') + $sfr ) AS relevance";
634 $against = "AGAINST ('$search_query' IN BOOLEAN MODE)";
635 $search_cond = "( MATCH (title, description, tags)
637 $relevance = "( 0.5 * (MATCH(title) $against)
638 + 0.3 * (MATCH(tags) $against)
639 + 0.2 * (MATCH(description) $against)
640 + $sfr) AS relevance";
645 $selected_columns = "COUNT(*) count";
651 // TODO select data, description if details are needed
652 $selected_columns = "v.id, name, title, duration, user_id, views,
653 thumbs_count, default_thumb, u.username,
654 (views + likes - dislikes) AS score,
656 $order = "ORDER BY relevance DESC, score DESC";
657 $limit = "LIMIT $offset, $count";
660 if ($category_id !== NULL)
661 $category_cond = "category_id = '$category_id' AND ";
665 $str_query = "SELECT $selected_columns
666 FROM `videos` v, `users` u
667 WHERE v.user_id = u.id AND $category_cond ( $search_cond )
670 // echo "<p>$str_query</p>";
671 $query = $this->db->query($str_query);
673 if ($query->num_rows() > 0)
676 return $query->row()->count;
678 $videos = $query->result_array();
683 $this->load->helper('text');
685 foreach ($videos as & $video)
687 // P2P-Tube Video URL
688 $video['video_url'] = site_url(sprintf("watch/%d/%s",
689 $video['id'], $video['name']));
692 $video['thumbs'] = $this->get_thumbs($video['name'],
693 $video['thumbs_count']);
694 $video['json_thumbs'] = json_encode($video['thumbs']);
697 //$video['shorted_title'] = ellipsize($video['title'], 45, 0.75);
698 $video['shorted_title'] = character_limiter($video['title'], 50);
700 // TODO: user information
701 $video['user_name'] = 'TODO';
707 public function decode_search_query($search_query)
709 $search_query = urldecode($search_query);
711 $search_query = str_replace('_AST_', '*', $search_query);
712 $search_query = str_replace('_AND_', '+', $search_query);
713 $search_query = str_replace('_GT_', '>', $search_query);
714 $search_query = str_replace('_LT_', '<', $search_query);
715 $search_query = str_replace('_PO_', '(', $search_query);
716 $search_query = str_replace('_PC_', ')', $search_query);
717 $search_query = str_replace('_LOW_', '~', $search_query);
718 $search_query = str_replace('_QUO_', '"', $search_query);
720 return $search_query;
723 public function encode_search_query($search_query)
725 $search_query = str_replace('*', '_AST_', $search_query);
726 $search_query = str_replace('+', '_AND_', $search_query);
727 $search_query = str_replace('>', '_GT_', $search_query);
728 $search_query = str_replace('<', '_LT_', $search_query);
729 $search_query = str_replace('(', '_PO_', $search_query);
730 $search_query = str_replace(')', '_PC_', $search_query);
731 $search_query = str_replace('~', '_LOW_', $search_query);
732 $search_query = str_replace('"', '_QUO_', $search_query);
734 $search_query = urlencode($search_query);
736 return $search_query;
740 * Return TRUE if it contains any special caracter from an advanced search
742 * @param string $search_query
745 public function is_advanced_search_query($search_query)
747 return (preg_match('/\*|\+|\-|>|\<|\(|\)|~|"/', $search_query) == 0
751 public static function gen_activation_code()
753 $ci =& get_instance();
755 $activation_code = substr(
756 sha1($ci->config->item('encryption_key')
761 return $activation_code;
765 /* End of file videos_model.php */
766 /* Location: ./application/models/videos_model.php */