注册 登录
自由的生活_软路由 返回首页

心想事成的个人空间 https://www.routerclub.com/?681 [收藏] [复制] [分享] [RSS]

日志

导出tvheadend的epg提供给diyp使用

已有 781 次阅读2022-8-30 22:10

自己安装了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就是秒回了。



路过

雷人

握手

鲜花

鸡蛋

评论 (0 个评论)

facelist doodle 涂鸦板

您需要登录后才可以评论 登录 | 注册

QQ|Archiver|手机版|小黑屋|软路由 ( 渝ICP备15001194号-1|渝公网安备 50011602500124号 )

GMT+8, 2024-5-30 00:13 , Processed in 0.077452 second(s), 5 queries , Gzip On, Redis On.

Powered by Discuz! X3.5 Licensed

© 2001-2023 Discuz! Team.

返回顶部