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.cis_response = '. CIS_RESP_COMPLETION. ')'
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.cis_response";
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.cis_response
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;
324 * Request content_ingest to the CIS in order to start the content
327 * @param string $activation_code
328 * @param string $raw_video_fn uploaded video file name
329 * @param string $name
330 * @param int $raw_video_size uploaded video file size in bytes
331 * @param array $transcode_configs dictionary which must be included in
332 * the JSON data that needs to be sent to CIS
333 * @return mixed return the HTTP content (body) on success and FALSE
336 public function send_content_ingestion($activation_code, $raw_video_fn,
337 $name, $raw_video_size, $transcode_configs)
339 $this->config->load('content_ingestion');
341 $url = $this->config->item('cis_url') . 'ingest_content';
343 'code'=>$activation_code,
344 'raw_video'=>$raw_video_fn,
346 'weight'=>$raw_video_size,
347 'transcode_configs'=>$transcode_configs,
348 'thumbs'=>$this->config->item('thumbs_count')
350 $json_data = json_encode($data);
352 // Send request to CIS.
353 $r = new HttpRequest($url, HttpRequest::METH_POST);
354 $r->setBody($json_data);
357 $response = $r->send()->getBody();
359 catch (HttpException $ex)
367 public function set_cis_response($activation_code,
368 $response = CIS_RESP_COMPLETION)
370 return $this->db->query("UPDATE `videos_unactivated`
371 SET cis_response = $response
372 WHERE activation_code = '$activation_code'");
375 public function send_upload_error_email($activation_code,
376 $cis_response = CIS_RESP_INTERNAL_ERROR)
378 $query = $this->db->query("SELECT v.title, u.email
379 FROM `videos_unactivated` a, `videos` v, `users` u
380 WHERE a.activation_code = '$activation_code'
381 AND a.video_id = v.id AND v.user_id = u.id");
383 if ($query->num_rows() > 0)
385 $title = $query->row()->title;
386 $email = $query->row()->email;
391 $subject = '['. $this->config->item('site_name')
393 if ($cis_response == CIS_RESP_INTERNAL_ERROR)
395 $msg = sprintf($this->lang->line(
396 'video_internal_cis_error_email_content'), $title);
398 else if ($cis_response == CIS_RESP_UNREACHABLE)
400 $msg = sprintf($this->lang->line(
401 'video_unreachable_cis_error_email_content'), $title);
403 $headers = "From: ". $this->config->item('noreply_email');
405 return mail($email, $subject, $msg, $headers);
409 * Activates a video by deleting its entry from `videos_unactivated`.
411 * @param mixed $code_or_id use type string for activation_code or type
413 * @return boolean TRUE on success, FALSE otherwise
415 public function activate_video($code_or_id)
417 if (is_string($code_or_id))
418 $query = $this->db->query("SELECT uploaded_file from `videos_unactivated`
419 WHERE activation_code = '$code_or_id'");
420 else if (is_int($code_or_id))
421 $query = $this->db->query("SELECT uploaded_file from `videos_unactivated`
422 WHERE video_id = '$code_or_id'");
426 if ($query->num_rows() > 0)
427 $uploaded_file = $query->row()->uploaded_file;
431 if (is_string($code_or_id))
432 $query = $this->db->query("DELETE FROM `videos_unactivated`
433 WHERE activation_code = '$code_or_id'");
434 else if (is_int($code_or_id))
435 $query = $this->db->query("DELETE FROM `videos_unactivated`
436 WHERE video_id = '$code_or_id'");
443 return unlink("data/upload/$uploaded_file");
446 public function get_unactivated_videos()
448 $query = $this->db->query("SELECT a.video_id, v.name, a.cis_response
449 FROM `videos_unactivated` a, `videos` v
450 WHERE a.video_id = v.id AND a.cis_response = "
451 . CIS_RESP_COMPLETION);
453 if ($query->num_rows() > 0)
454 return $query->result_array();
460 * Retrieves comments for a video.
462 * @param int $video_id
465 * @param string $ordering control comments ording by these possibilities:
467 * <li><strong>'hottest':</strong> newest most appreciated first. An
468 * appreciated comment is one which has a bigger
469 * score = likes - dislikes.</li>
470 * <li><strong>'newest':</strong> newest first.</li>
472 * @return array an array with comments
474 public function get_video_comments($video_id, $offset, $count,
475 $ordering = 'newest')
477 $this->load->helper('date');
484 $order_statement = "ORDER BY time DESC";
487 $order_statement = "ORDER BY score DESC, time DESC";
488 $cond_hottest = "AND c.likes + c.dislikes > 0";
492 $order_statement = "";
495 $query = $this->db->query(
496 "SELECT c.*, u.username, u.time_zone, (c.likes + c.dislikes) AS score
497 FROM `videos_comments` c, `users` u
498 WHERE c.user_id = u.id AND video_id = $video_id $cond_hottest
500 LIMIT $offset, $count");
502 if ($query->num_rows() == 0)
505 $comments = $query->result_array();
507 foreach ($comments as & $comment)
509 $comment['local_time'] = human_gmt_to_human_local($comment['time'],
510 $comment['time_zone']);
516 public function get_video_comments_count($video_id)
518 $query = $this->db->query(
519 "SELECT COUNT(*) count
520 FROM `videos_comments`
521 WHERE video_id = $video_id");
523 if ($query->num_rows() == 0)
526 return $query->row()->count;
530 * Insert in DB a comment for a video.
532 * @param int $video_id
533 * @param int $user_id
534 * @param string $content
536 public function comment_video($video_id, $user_id, $content)
539 $content = substr($content, 0, 512);
540 $content = htmlspecialchars($content);
541 $content = nl2br($content);
543 return $query = $this->db->query(
544 "INSERT INTO `videos_comments` (video_id, user_id, content, time)
545 VALUES ($video_id, $user_id, '$content', UTC_TIMESTAMP())");
549 * Increments views count for a video.
551 * @param int $id DB video id
554 public function inc_views($id)
556 return $this->db->query('UPDATE `videos` '
557 . 'SET `views`=`views`+1 '
561 public function vote($video_id, $user_id, $like = TRUE)
574 $query = $this->db->query("SELECT * FROM `users_actions`
575 WHERE user_id = $user_id
576 AND target_id = $video_id
577 AND target_type = 'video'
578 AND action = '$action'
579 AND date = CURDATE()");
580 // User already voted today
581 if ($query->num_rows() > 0)
584 $this->db->query("UPDATE `videos`
586 WHERE id = $video_id");
588 // Mark this action so that the user cannot repeat it today.
589 $this->db->query("INSERT INTO `users_actions`
590 (user_id, action, target_type, target_id, date)
591 VALUES ( $user_id, '$action', 'video', $video_id, CURDATE() )");
593 $query = $this->db->query("SELECT $col FROM `videos`
594 WHERE id = $video_id");
596 if ($query->num_rows() === 1)
598 $row = $query->row_array();
606 public function vote_comment($comment_id, $user_id, $like = TRUE)
619 $query = $this->db->query("SELECT * FROM `users_actions`
620 WHERE user_id = $user_id
621 AND target_id = $comment_id
622 AND target_type = 'vcomment'
623 AND action = '$action'
624 AND date = CURDATE()");
625 // User already voted today
626 if ($query->num_rows() > 0)
629 $this->db->query("UPDATE `videos_comments`
631 WHERE id = $comment_id");
633 // Mark this action so that the user cannot repeat it today.
634 $this->db->query("INSERT INTO `users_actions`
635 (user_id, action, target_type, target_id, date)
636 VALUES ( $user_id, '$action', 'vcomment', $comment_id, CURDATE() )");
638 $query = $this->db->query("SELECT $col FROM `videos_comments`
639 WHERE id = $comment_id");
641 if ($query->num_rows() === 1)
643 $row = $query->row_array();
651 public function get_thumbs($name, $count)
655 for ($i=0; $i < $count; $i++)
656 $thumbs[] = site_url(sprintf("data/thumbs/%s_t%02d.jpg", $name, $i));
662 * Searches videos in DB based on a search query string and returns an
663 * associative array of results.
664 * If count is zero the function only return the number of results.
665 * @param string $search_query
668 * @param int $category_id if NULL, all categories are searched
669 * @return array an associative array with the same keys as that from
670 * get_videos_summary's result, but with two additional keys:
671 * description and date.
673 public function search_videos($search_query, $offset = 0, $count = 0,
676 $search_query = trim($search_query);
677 $search_query = str_replace("'", " ", $search_query);
679 // Search word fragments.
680 // sfc = search fragment condition
682 // sfr = search fragment relevance
685 $fragm = strtok($search_query, $sep);
686 while ($fragm !== FALSE)
688 $sfc .= "(title LIKE '%$fragm%'
689 OR description LIKE '%$fragm%'
690 OR tags LIKE '%$fragm%') OR ";
692 // Frament relevances are half of boolean relevances such
693 // that they will appear at the end of the results.
694 $sfr .= "0.25 * (title LIKE '%$fragm%')
695 + 0.1 * (description LIKE '%$fragm%')
696 + 0.15 * (tags LIKE '%$fragm%') + ";
698 $fragm = strtok($sep);
700 $sfc = substr($sfc, 0, -4) . " )";
701 $sfr = substr($sfr, 0, -3) . " )";
703 if (! $this->is_advanced_search_query($search_query))
705 $search_cond = "MATCH (title, description, tags)
706 AGAINST ('$search_query') OR $sfc";
707 $relevance = "( MATCH (title, description, tags)
708 AGAINST ('$search_query') + $sfr ) AS relevance";
713 $against = "AGAINST ('$search_query' IN BOOLEAN MODE)";
714 $search_cond = "( MATCH (title, description, tags)
716 $relevance = "( 0.5 * (MATCH(title) $against)
717 + 0.3 * (MATCH(tags) $against)
718 + 0.2 * (MATCH(description) $against)
719 + $sfr) AS relevance";
724 $selected_columns = "COUNT(*) count";
730 // TODO select data, description if details are needed
731 $selected_columns = "v.id, name, title, duration, user_id, views,
732 thumbs_count, default_thumb, u.username,
733 (views + likes - dislikes) AS score,
735 $order = "ORDER BY relevance DESC, score DESC";
736 $limit = "LIMIT $offset, $count";
739 if ($category_id !== NULL)
740 $category_cond = "category_id = '$category_id' AND ";
744 $str_query = "SELECT $selected_columns
745 FROM `videos` v, `users` u
746 WHERE v.user_id = u.id AND $category_cond ( $search_cond )
749 // echo "<p>$str_query</p>";
750 $query = $this->db->query($str_query);
752 if ($query->num_rows() > 0)
755 return $query->row()->count;
757 $videos = $query->result_array();
762 $this->load->helper('text');
764 foreach ($videos as & $video)
766 // P2P-Tube Video URL
767 $video['video_url'] = site_url(sprintf("watch/%d/%s",
768 $video['id'], $video['name']));
771 $video['thumbs'] = $this->get_thumbs($video['name'],
772 $video['thumbs_count']);
773 $video['json_thumbs'] = json_encode($video['thumbs']);
776 //$video['shorted_title'] = ellipsize($video['title'], 45, 0.75);
777 $video['shorted_title'] = character_limiter($video['title'], 50);
779 // TODO: user information
780 $video['user_name'] = 'TODO';
786 public function decode_search_query($search_query)
788 $search_query = urldecode($search_query);
790 $search_query = str_replace('_AST_', '*', $search_query);
791 $search_query = str_replace('_AND_', '+', $search_query);
792 $search_query = str_replace('_GT_', '>', $search_query);
793 $search_query = str_replace('_LT_', '<', $search_query);
794 $search_query = str_replace('_PO_', '(', $search_query);
795 $search_query = str_replace('_PC_', ')', $search_query);
796 $search_query = str_replace('_LOW_', '~', $search_query);
797 $search_query = str_replace('_QUO_', '"', $search_query);
799 return $search_query;
802 public function encode_search_query($search_query)
804 $search_query = str_replace('*', '_AST_', $search_query);
805 $search_query = str_replace('+', '_AND_', $search_query);
806 $search_query = str_replace('>', '_GT_', $search_query);
807 $search_query = str_replace('<', '_LT_', $search_query);
808 $search_query = str_replace('(', '_PO_', $search_query);
809 $search_query = str_replace(')', '_PC_', $search_query);
810 $search_query = str_replace('~', '_LOW_', $search_query);
811 $search_query = str_replace('"', '_QUO_', $search_query);
813 $search_query = urlencode($search_query);
815 return $search_query;
819 * Return TRUE if it contains any special caracter from an advanced search
821 * @param string $search_query
824 public function is_advanced_search_query($search_query)
826 return (preg_match('/\*|\+|\-|>|\<|\(|\)|~|"/', $search_query) == 0
830 public static function gen_activation_code()
832 $ci =& get_instance();
834 $activation_code = substr(
835 sha1($ci->config->item('encryption_key')
840 return $activation_code;
844 /* End of file videos_model.php */
845 /* Location: ./application/models/videos_model.php */