|
本帖最后由 wsgtrsys 于 2022-8-30 22:36 编辑
自己安装了tvheaded从有线的数据流中自动获取到epg数据。安装了diyp后,也从tvheadend串流观看电视。
但diyp需要的epg结构和tvheadend的不一样,需要进一步加工后使用。
程序用到了mysql、php、ngnix、memcache等软件。
先在mysql中建立数据库,并建立两张表。
- CREATE DATABASE `myepg` CHARACTER SET utf8 COLLATE utf8_general_ci;
- use myepg;
- CREATE TABLE IF NOT EXISTS `epg_channel` (
- `name` varchar(16) NOT NULL,
- `channel_id` varchar(40) NOT NULL,
- index(name)
- );
- CREATE TABLE IF NOT EXISTS `epg_programme` (
- `title` varchar(100) NOT NULL,
- `sdate` varchar(16) NOT NULL,
- `sstart` varchar(16) NOT NULL,
- `sstop` varchar(16) NOT NULL,
- `channel` varchar(40) NOT NULL,
- `sdesc` varchar(100) NOT NULL,
- INDEX(sdate),
- INDEX(channel)
- );
复制代码
然后在web目录中,建立两个php文件,一个负责下载tvheadend数据,并导入数据库。
- <?php
- $displayname = 'display-name';
- $send_mysql = 1;
- function getContent($url, $username, $password)
- {
- $process = curl_init($url);
- // curl_setopt($process, CURLOPT_USERPWD, $username . ":" . $password);
- curl_setopt($process, CURLOPT_RETURNTRANSFER, true);
- $data = curl_exec($process);
- curl_close($process);
- return $data;
- }
- $data = getContent("http://192.168.88.17:9981/xmltv/channels", "a", "a");
- $xml = simplexml_load_string($data);
- $conn = mysqli_connect("localhost", "test", "test", "myepg");
- $result = mysqli_query($conn, 'TRUNCATE TABLE `epg_channel`;');
- $result = mysqli_query($conn, 'TRUNCATE TABLE `epg_programme`;');
- foreach ($xml->children() as $xmldata) {
- if ($xmldata->getName() == "channel") {
- if ($send_mysql == 1) {
- $sql = "INSERT INTO epg_channel(name,channel_id) VALUES ('" . $xmldata->$displayname . "','" . $xmldata->attributes()->id . "')";
-
- $result = mysqli_query($conn, $sql);
-
- if (!empty($result)) {
- } else {
- $error_message = mysqli_error($conn) . "\n";
- }
- }
- }
-
- if ($xmldata->getName() == "programme") {
-
- $start_time = substr($xmldata->attributes()->start, 8, 2) . ":" . substr($xmldata->attributes()->start, 10, 2);
- $stop_time = substr($xmldata->attributes()->stop, 8, 2) . ":" . substr($xmldata->attributes()->stop, 10, 2);
- $jm_date = substr($xmldata->attributes()->stop, 0, 4) . "-" . substr($xmldata->attributes()->stop, 4, 2) . "-" . substr($xmldata->attributes()->stop, 6, 2);
-
- if ($send_mysql == 1) {
- $sql = "INSERT INTO epg_programme(channel,sdate,sstart,sstop,title,sdesc) VALUES ('" . $xmldata->attributes()->channel . "','" . $jm_date . "','". $start_time . "','". $stop_time ."','" . $xmldata->title . "','')";
-
- $result = mysqli_query($conn, $sql);
-
- if (!empty($result)) {
- } else {
- $error_message = mysqli_error($conn) . "\n";
- }
- }
- }
-
- }
- ?>
复制代码
第二个文件负责处理diyp的数据请求
- <?php
- $displayname = 'display-name';
- $riqi =$_GET['date'];
- $ch=$_GET['ch'];
- $is_found = 0;
- $send_mysql = 1;
- //http://epg.51zmt.top:8000/api/diyp/?ch=%E5%87%A4%E5%87%B0%E4%B8%AD%E6%96%87&date=2022-08-16
- function getContent($s_ch, $s_date)
- {
- $process = curl_init("http://epg.51zmt.top:8000/api/diyp/"."?ch=".$s_ch ."&date=" . $s_date);
- curl_setopt($process, CURLOPT_RETURNTRANSFER, true);
- $data = curl_exec($process);
- curl_close($process);
- return $data;
- }
- $memcache = new Memcache;
- $memcache->connect('localhost', 11211) or die ("Could not connect Memcache");
- $key = md5($ch.$riqi);
- $cache_result = array();
- $cache_result = $memcache->get($key);
- if($cache_result){
- echo $cache_result;
- }else
- {
- $conn = mysqli_connect("localhost", "test", "test", "myepg");
- $sql = "SELECT channel_id FROM epg_channel where name='" . $ch . "' limit 1";
- $retval = mysqli_query($conn, $sql);
- if (mysqli_num_rows($retval) <= 0) {
- $data = getContent($ch, $riqi);
- $memcache->set($key, $data, MEMCACHE_COMPRESSED, 1200);
- echo $data;
- return;
- }
- $sql = "SELECT * FROM epg_programme WHERE channel = (SELECT channel_id FROM epg_channel where name='" . $ch . "' limit 1) AND sdate = '" . $riqi . "'";
- // echo $sql. "\n";
- $retval = mysqli_query($conn, $sql);
- if (mysqli_num_rows($retval) > 0) {
- while ($row = mysqli_fetch_assoc($retval)) {
-
- $epg_datas[] = array(
- "start" => $row['sstart'],
- "end" => $row['sstop'],
- "title" => $row['title'],
- "desc" => ""
- );
- $is_found = 1;
- }
- }
- if ($is_found == 1) {
- $age = array(
- "channel_name" => "",
- "date" => "$riqi",
- "epg_data" => $epg_datas
- );
- $datas = json_encode($age, JSON_UNESCAPED_UNICODE);
- $memcache->set($key, $datas, MEMCACHE_COMPRESSED, 1200);
- echo ($datas);
-
- } else {
- $epg_datas[] = array(
- "start" => "00:00",
- "end" => "23:59",
- "title" => "未知节目",
- "desc" => ""
- );
- $age = array(
- "channel_name" => "",
- "date" => "$riqi",
- "epg_data" => $epg_datas
- );
- echo (json_encode($age, JSON_UNESCAPED_UNICODE));
- }
- }
- ?>
复制代码
把第一个php加入到crontab任务中,每天凌晨更新epg到mysql数据库。
第二php响应diyp的请求,首先在memcache中查询,如果没有再查mysql,如果再没有就转发51zmt的数据并缓存。。。。太污了。。
至于为什么要用mysql和memcache,因为如果直接查xml或者mysql实在太慢了,加了cache就是秒回了。
|
|