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
33 * @param string $ordering control videos ording by these
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>
42 * @return array a list of videos, each one being an assoc array with:
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>
51 public function get_videos_summary($category_id, $user, $offset, $count,
52 $ordering = 'hottest')
54 $this->load->helper('text');
60 $order_statement = "ORDER BY date DESC, score DESC, RAND()";
63 $order_statement = "ORDER BY date DESC";
65 case 'alphabetically':
66 $order_statement = "ORDER BY title";
70 $order_statement = "";
74 if ($category_id === NULL)
78 $category_id = intval($category_id);
79 $cond_category = "category_id = $category_id";
88 $cond_user = "v.user_id = $user";
89 else if (is_string($user))
90 $cond_user = "u.username = '$user'";
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
100 LIMIT $offset, $count");
102 if ($query->num_rows() > 0)
103 $videos = $query->result_array();
107 foreach ($videos as & $video)
109 // P2P-Tube Video URL
110 $video['video_url'] = site_url(sprintf("watch/%d/%s",
111 $video['id'], $video['name']));
114 $video['thumbs'] = $this->get_thumbs($video['name'],
115 $video['thumbs_count']);
118 //$video['shorted_title'] = ellipsize($video['title'], 45, 0.75);
119 $video['shorted_title'] = character_limiter($video['title'], 50);
126 * Returns the number of videos from database from a specific category or
128 * NULL parameters count videos from all categories and / or all users.
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
134 public function get_videos_count($category_id = NULL, $user = NULL)
136 if ($category_id === NULL)
137 $cond_category = "1";
139 $cond_category = "category_id = $category_id";
146 $cond_user = "v.user_id = $user";
147 else if(is_string($user))
148 $cond_user = "u.username = '$user'";
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");
156 if ($query->num_rows() > 0)
157 return $query->row()->count;
164 * Retrieves information about a video.
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.
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:
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>
189 public function get_video($id, $name = NULL)
191 $this->load->helper('video');
192 $this->load->helper('text');
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");
199 if ($query->num_rows() > 0)
201 $video = $query->row_array();
202 if ($name !== NULL && $video['name'] != $name)
203 $video['err'] = 'INVALID_NAME';
207 $video['err'] = 'INVALID_ID';
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);
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");
225 $video['url'] = array();
226 foreach ($video['assets'] as & $asset)
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'));
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;
242 $video['thumbs'] = $this->get_thumbs($video['name'], $video['thumbs_count']);
244 // Shorted description
245 $video['shorted_description'] = character_limiter(
246 $video['description'], 128);
251 public function compute_video_name($title)
253 $name = str_replace(' ', '-', $title);
255 return urlencode($name);
259 * Adds a new uploaded video to the DB.
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
268 public function add_video($title, $description, $tags, $av_info,
269 $category_id, $user_id)
271 $name = $this->compute_video_name($title);
274 $json_tags = array();
275 $tok = strtok($tags, ',');
276 while ($tok != FALSE)
278 $json_tags[trim($tok)] = 0;
282 $json_tags = json_encode($json_tags);
285 $json_formats = '[{"res":"1280x720","ext":"ogv","dar":"16:9"},'
286 . '{"res":"1067x600","ext":"ogv","dar":"16:9"}]';
289 $query = $this->db->query("INSERT INTO `videos`
290 (name, title, description, duration, formats, category_id,
292 VALUES ('$name', '$title', '$description', '"
293 . $av_info['duration']. "', '$json_formats', $category_id,
294 $user_id, '$json_tags', utc_timestamp())");
295 if ($query === FALSE)
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)
303 $video_id = $query->row()->id;
306 $activation_code = Videos_model::gen_activation_code();
308 $query = $this->db->query("INSERT INTO `videos_unactivated`
309 (video_id, activation_code)
310 VALUES ($video_id, '$activation_code')");
314 * Retrieves comments for a video.
316 * @param int $video_id
319 * @param string $ordering control comments ording by these possibilities:
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>
326 * @return array an array with comments
328 public function get_video_comments($video_id, $offset, $count,
329 $ordering = 'newest')
331 $this->load->helper('date');
338 $order_statement = "ORDER BY time DESC";
341 $order_statement = "ORDER BY score DESC, time DESC";
342 $cond_hottest = "AND c.likes + c.dislikes > 0";
346 $order_statement = "";
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
354 LIMIT $offset, $count");
356 if ($query->num_rows() == 0)
359 $comments = $query->result_array();
361 foreach ($comments as & $comment)
363 $comment['local_time'] = human_gmt_to_human_local($comment['time'],
364 $comment['time_zone']);
370 public function get_video_comments_count($video_id)
372 $query = $this->db->query(
373 "SELECT COUNT(*) count
374 FROM `videos_comments`
375 WHERE video_id = $video_id");
377 if ($query->num_rows() == 0)
380 return $query->row()->count;
384 * Insert in DB a comment for a video.
386 * @param int $video_id
387 * @param int $user_id
388 * @param string $content
390 public function comment_video($video_id, $user_id, $content)
393 $content = substr($content, 0, 512);
394 $content = htmlspecialchars($content);
395 $content = nl2br($content);
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())");
403 * Increments views count for a video.
405 * @param int $id DB video id
408 public function inc_views($id)
410 return $this->db->query('UPDATE `videos` '
411 . 'SET `views`=`views`+1 '
415 public function vote($video_id, $user_id, $like = TRUE)
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)
438 $this->db->query("UPDATE `videos`
440 WHERE id = $video_id");
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() )");
447 $query = $this->db->query("SELECT $col FROM `videos`
448 WHERE id = $video_id");
450 if ($query->num_rows() === 1)
452 $row = $query->row_array();
460 public function vote_comment($comment_id, $user_id, $like = TRUE)
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)
483 $this->db->query("UPDATE `videos_comments`
485 WHERE id = $comment_id");
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() )");
492 $query = $this->db->query("SELECT $col FROM `videos_comments`
493 WHERE id = $comment_id");
495 if ($query->num_rows() === 1)
497 $row = $query->row_array();
505 public function get_thumbs($name, $count)
509 for ($i=0; $i < $count; $i++)
510 $thumbs[] = site_url(sprintf("data/thumbs/%s_t%02d.jpg", $name, $i));
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
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.
527 public function search_videos($search_query, $offset = 0, $count = 0,
530 $search_query = trim($search_query);
531 $search_query = str_replace("'", " ", $search_query);
533 // Search word fragments.
534 // sfc = search fragment condition
536 // sfr = search fragment relevance
539 $fragm = strtok($search_query, $sep);
540 while ($fragm !== FALSE)
542 $sfc .= "(title LIKE '%$fragm%'
543 OR description LIKE '%$fragm%'
544 OR tags LIKE '%$fragm%') OR ";
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%') + ";
552 $fragm = strtok($sep);
554 $sfc = substr($sfc, 0, -4) . " )";
555 $sfr = substr($sfr, 0, -3) . " )";
557 if (! $this->is_advanced_search_query($search_query))
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";
567 $against = "AGAINST ('$search_query' IN BOOLEAN MODE)";
568 $search_cond = "( MATCH (title, description, tags)
570 $relevance = "( 0.5 * (MATCH(title) $against)
571 + 0.3 * (MATCH(tags) $against)
572 + 0.2 * (MATCH(description) $against)
573 + $sfr) AS relevance";
578 $selected_columns = "COUNT(*) count";
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,
589 $order = "ORDER BY relevance DESC, score DESC";
590 $limit = "LIMIT $offset, $count";
593 if ($category_id !== NULL)
594 $category_cond = "category_id = '$category_id' AND ";
598 $str_query = "SELECT $selected_columns
599 FROM `videos` v, `users` u
600 WHERE v.user_id = u.id AND $category_cond ( $search_cond )
603 // echo "<p>$str_query</p>";
604 $query = $this->db->query($str_query);
606 if ($query->num_rows() > 0)
609 return $query->row()->count;
611 $videos = $query->result_array();
616 $this->load->helper('text');
618 foreach ($videos as & $video)
620 // P2P-Tube Video URL
621 $video['video_url'] = site_url(sprintf("watch/%d/%s",
622 $video['id'], $video['name']));
625 $video['thumbs'] = $this->get_thumbs($video['name'],
626 $video['thumbs_count']);
629 //$video['shorted_title'] = ellipsize($video['title'], 45, 0.75);
630 $video['shorted_title'] = character_limiter($video['title'], 50);
632 // TODO: user information
633 $video['user_name'] = 'TODO';
639 public function decode_search_query($search_query)
641 $search_query = urldecode($search_query);
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);
652 return $search_query;
655 public function encode_search_query($search_query)
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);
666 $search_query = urlencode($search_query);
668 return $search_query;
672 * Return TRUE if it contains any special caracter from an advanced search
674 * @param string $search_query
677 public function is_advanced_search_query($search_query)
679 return (preg_match('/\*|\+|\-|>|\<|\(|\)|~|"/', $search_query) == 0
683 public static function gen_activation_code()
685 $ci =& get_instance();
687 $activation_code = substr(
688 sha1($ci->config->item('encryption_key')
693 return $activation_code;
697 /* End of file videos_model.php */
698 /* Location: ./application/models/videos_model.php */