Typecho 插件 Access 适配 SQLite3 数据库
不蒜子
前段时间，使用 Typecho 搭建了一个网站，在网上搜索访问统计实现，发现比较简单的是使用不蒜子,
总的来说，使用很简单，但是不能初始化数据，只能找作者修改统计数据或在自己页面中通过 js 加上初始数。对于个人博客来说，看个 IP、UV 和 PV 也够了。
适配 SQLite3
昨天，在网上发现有个 Typecho 插件 Access，打算试用一下，发现只支持 MySQL，不支持 SQLite3，查看后发现 GitHub 上有个 issue 是否能添加SQLite3支持 #15 已经被关闭。由于本人对 PHP 和 SQLite3 均不熟悉，也没有开发过 Typecho 插件，所以从网上查看了相关的文档，给 Access 做了个 SQLite3 的适配，已经可以使用了，只是启用插件时如果数据库表 typecho_access_log 不存在，会报一个“Database Query Error”的错误，
此时刷新一下页面，插件就启用成功了。
修改 Plugin.php 文件中 install 方法
/**
* 初始化以及升级插件数据库，如初始化失败,直接抛出异常
*
* @access public
* @return string
* @throws Typecho_Plugin_Exception
*/
public static function install()
{
if (substr(trim(dirname(__FILE__), '/'), -6) != 'Access') {
throw new Typecho_Plugin_Exception(_t('插件目录名必须为Access'));
}
$db = Typecho_Db::get();
$adapterName = $db->getAdapterName();
if (strpos($adapterName, 'Mysql') !== false) {
$prefix = $db->getPrefix();
$scripts = file_get_contents('usr/plugins/Access/sql/Mysql.sql');
$scripts = str_replace('typecho_', $prefix, $scripts);
$scripts = str_replace('%charset%', 'utf8', $scripts);
$scripts = explode(';', $scripts);
try {
$configLink = '<a href="' . Helper::options()->adminUrl . 'options-plugin.php?config=Access">' . _t('前往设置') . '</a>';
# 初始化数据库如果不存在
if (!$db->fetchRow($db->query("SHOW TABLES LIKE '{$prefix}access_log';", Typecho_Db::READ))) {
foreach ($scripts as $script) {
$script = trim($script);
if ($script) {
$db->query($script, Typecho_Db::WRITE);
}
}
$msg = _t('成功创建数据表，插件启用成功，') . $configLink;
}
# 处理旧版本数据
if ($db->fetchRow($db->query("SHOW TABLES LIKE '{$prefix}access';", Typecho_Db::READ))) {
$rows = $db->fetchAll($db->select()->from('table.access'));
foreach ($rows as $row) {
$ua = new Access_UA($row['ua']);
$time = Helper::options()->gmtTime + (Helper::options()->timezone - Helper::options()->serverTimezone);
$row['browser_id' ] = $ua->getBrowserID();
$row['browser_version' ] = $ua->getBrowserVersion();
$row['os_id' ] = $ua->getOSID();
$row['os_version' ] = $ua->getOSVersion();
$row['path' ] = parse_url($row['url'], PHP_URL_PATH);
$row['query_string' ] = parse_url($row['url'], PHP_URL_QUERY);
$row['ip' ] = bindec(decbin(ip2long($row['ip'])));
$row['entrypoint' ] = $row['referer'];
$row['entrypoint_domain'] = $row['referer_domain'];
$row['time' ] = $row['date'];
$row['robot' ] = $ua->isRobot() ? 1 : 0;
$row['robot_id' ] = $ua->getRobotID();
$row['robot_version' ] = $ua->getRobotVersion();
unset($row['date']);
try {
$db->query($db->insert('table.access_log')->rows($row));
} catch (Typecho_Db_Exception $e) {
if ($e->getCode() != 23000)
throw new Typecho_Plugin_Exception(_t('导入旧版数据失败，插件启用失败，错误信息：%s。', $e->getMessage()));
}
}
$db->query("DROP TABLE `{$prefix}access`;", Typecho_Db::WRITE);
$msg = _t('成功创建数据表并更新数据，插件启用成功，') . $configLink;
}
return $msg;
} catch (Typecho_Db_Exception $e) {
throw new Typecho_Plugin_Exception(_t('数据表建立失败，插件启用失败，错误信息：%s。', $e->getMessage()));
} catch (Exception $e) {
throw new Typecho_Plugin_Exception($e->getMessage());
}
} else if (strpos($adapterName, 'SQLite') !== false) {
$prefix = $db->getPrefix();
$scripts = file_get_contents('usr/plugins/Access/sql/SQLite.sql');
$scripts = str_replace('typecho_', $prefix, $scripts);
$scripts = explode(';', $scripts);
try {
$configLink = '<a href="' . Helper::options()->adminUrl . 'options-plugin.php?config=Access">' . _t('前往设置') . '</a>';
# 初始化数据库如果不存在
if (!$db->fetchRow($db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='{$prefix}access_log';", Typecho_Db::READ))) {
foreach ($scripts as $script) {
$script = trim($script);
if ($script) {
$db->query($script, Typecho_Db::WRITE);
}
}
$msg = _t('成功创建数据表，插件启用成功，') . $configLink;
} else {
$msg = _t('数据表已经存在，插件启用成功，') . $configLink;
}
return $msg;
} catch (Typecho_Db_Exception $e) {
throw new Typecho_Plugin_Exception(_t('数据表建立失败，插件启用失败，错误信息：%s。', $e->getMessage()));
} catch (Exception $e) {
throw new Typecho_Plugin_Exception($e->getMessage());
}
} else {
throw new Typecho_Plugin_Exception(_t('你的适配器为%s，目前只支持Mysql和SQLite', $adapterName));
}
}增加 sql/SQLite.sql 文件
CREATE TABLE `typecho_access_log` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`ua` varchar(255) default '' ,
`browser_id` varchar(32) default '' ,
`browser_version` varchar(32) default '' ,
`os_id` varchar(32) default '' ,
`os_version` varchar(32) default '' ,
`url` varchar(255) default '' ,
`path` varchar(255) default '' ,
`query_string` varchar(255) default '' ,
`ip` int(32) default '0' ,
`entrypoint` varchar(255) default '' ,
`entrypoint_domain` varchar(100) default '' ,
`referer` varchar(255) default '' ,
`referer_domain` varchar(100) default '' ,
`time` int(32) default '0' ,
`content_id` int(10) default NULL,
`meta_id` int(10) default NULL,
`robot` tinyint(1) default '0' ,
`robot_id` varchar(32) default '' ,
`robot_version` varchar(32) default ''
);
CREATE INDEX `typecho_access_log_time` ON `typecho_access_log` (`time` );
CREATE INDEX `typecho_access_log_path` ON `typecho_access_log` (`path` );
CREATE INDEX `typecho_access_log_ip_ua` ON `typecho_access_log` (`ip`, `ua` );
CREATE INDEX `typecho_access_log_robot` ON `typecho_access_log` (`robot`, `time` );
CREATE INDEX `typecho_access_log_os_id` ON `typecho_access_log` (`os_id` );
CREATE INDEX `typecho_access_log_robot_id` ON `typecho_access_log` (`robot_id` );
CREATE INDEX `typecho_access_log_browser_id` ON `typecho_access_log` (`browser_id` );
CREATE INDEX `typecho_access_log_content_id` ON `typecho_access_log` (`content_id` );
CREATE INDEX `typecho_access_log_meta_id` ON `typecho_access_log` (`meta_id` );
CREATE INDEX `typecho_access_log_entrypoint` ON `typecho_access_log` (`entrypoint` );
CREATE INDEX `typecho_access_log_entrypoint_domain` ON `typecho_access_log` (`entrypoint_domain`);
CREATE INDEX `typecho_access_log_referer` ON `typecho_access_log` (`referer` );
CREATE INDEX `typecho_access_log_referer_domain` ON `typecho_access_log` (`referer_domain` );
COMMIT;适配 SQLite3 已提交 Pull request，插件作者已合并
统计错误
在测试过程中，发现昨天和今天的 UV IP 统计不准确。查看 Access_Core.php 源码，发现昨天和今天的 UV IP 是通过分时段统计再相加得来的。这样，各小时内重复的数据会计算多次，造成错误。比如一个 IP 192.168.1.1 每个小时都访问网站，按理来说，一整天只算一次，但按现在累加的方法，这一天统计的 ip 192.168.1.1 会是 24 次，比实际多 23 次。
修改 Access_Core.php 文件中 parseOverview 方法（看了作者的修改，我写的有些麻烦了）
在分类分时段统计数据和总统计数据之间添加如下代码
# 昨天 ip uv pv 统计数据
$yesterday = date("Y-m-d", strtotime('-1 day'));
$yesterdayStart = strtotime(date("{$yesterday} 00:00:00"));
$yesterdayEnd = strtotime(date("{$yesterday} 23:59:59"));
$yesterdayIpSubQuery = $this->db->select('DISTINCT ip')->from('table.access_log')->where("time >= ? AND time <= ?", $yesterdayStart, $yesterdayEnd);
if (method_exists($yesterdayIpSubQuery, 'prepare')) {
$yesterdayIpSubQuery = $yesterdayIpSubQuery->prepare($yesterdayIpSubQuery);
}
$yesterdayUvSubQuery = $this->db->select('DISTINCT ip,ua')->from('table.access_log')->where("time >= ? AND time <= ?", $yesterdayStart, $yesterdayEnd);
if (method_exists($yesterdayUvSubQuery, 'prepare')) {
$yesterdayUvSubQuery = $yesterdayUvSubQuery->prepare($yesterdayUvSubQuery);
}
$this->overview['ip']['yesterday']['total'] = $this->db->fetchAll($this->db->select('COUNT(1) AS count')
->from('(' . $yesterdayIpSubQuery . ') AS tmp'))[0]['count'];
$this->overview['uv']['yesterday']['total'] = $this->db->fetchAll($this->db->select('COUNT(1) AS count')
->from('(' . $yesterdayUvSubQuery . ') AS tmp'))[0]['count'];
// 上面分时段相加是对的，这里就注释掉了
//$this->overview['pv']['yesterday']['total'] = $this->db->fetchAll($this->db->select('COUNT(1) AS count')
// ->from('table.access_log')->where("time >= ? AND time <= ?", $yesterdayStart, $yesterdayEnd))[0]['count'];
# 今天 ip uv pv 统计数据
$today = date("Y-m-d");
$todayStart = strtotime(date("{$today} 00:00:00"));
$todayEnd = strtotime(date("{$today} 23:59:59"));
$todayIpSubQuery = $this->db->select('DISTINCT ip')->from('table.access_log')->where("time >= ? AND time <= ?", $todayStart, $todayEnd);
if (method_exists($todayIpSubQuery, 'prepare')) {
$todayIpSubQuery = $todayIpSubQuery->prepare($todayIpSubQuery);
}
$todayUvSubQuery = $this->db->select('DISTINCT ip,ua')->from('table.access_log')->where("time >= ? AND time <= ?", $todayStart, $todayEnd);
if (method_exists($todayUvSubQuery, 'prepare')) {
$todayUvSubQuery = $todayUvSubQuery->prepare($todayUvSubQuery);
}
$this->overview['ip']['today']['total'] = $this->db->fetchAll($this->db->select('COUNT(1) AS count')
->from('(' . $todayIpSubQuery . ') AS tmp'))[0]['count'];
$this->overview['uv']['today']['total'] = $this->db->fetchAll($this->db->select('COUNT(1) AS count')
->from('(' . $todayUvSubQuery . ') AS tmp'))[0]['count'];
// 上面分时段相加是对的，这里就注释掉了
//$this->overview['pv']['today']['total'] = $this->db->fetchAll($this->db->select('COUNT(1) AS count')
// ->from('table.access_log')->where("time >= ? AND time <= ?", $todayStart, $todayEnd))[0]['count'];修改 Access_Core.php 文件中 parseOverview 方法（看了作者的修改，我整理了一下）
在“分类分时段统计数据”下面 foreach 的最后边，添加如下代码
# 分类分时段统计数据
foreach (['today' => date("Y-m-d"), 'yesterday'=> date("Y-m-d", strtotime('-1 day'))] as $day => $time) {
for ($i = 0; $i < 24; $i++) {
// 略...
}
# 今天和昨天 ip uv pv 统计数据
$start = strtotime(date("{$time} 00:00:00"));
$end = strtotime(date("{$time} 23:59:59"));
// ip 统计数据
$subQuery = $this->db->select('DISTINCT ip')->from('table.access_log')->where("time >= ? AND time <= ?", $start, $end);
if (method_exists($subQuery, 'prepare')) {
$subQuery = $subQuery->prepare($subQuery);
}
$this->overview['ip'][$day]['total'] = $this->db->fetchAll($this->db->select('COUNT(1) AS count')
->from('(' . $subQuery . ') AS tmp'))[0]['count'];
// uv 统计数据
$subQuery = $this->db->select('DISTINCT ip,ua')->from('table.access_log')->where("time >= ? AND time <= ?", $start, $end);
if (method_exists($subQuery, 'prepare')) {
$subQuery = $subQuery->prepare($subQuery);
}
$this->overview['uv'][$day]['total'] = $this->db->fetchAll($this->db->select('COUNT(1) AS count')
->from('(' . $subQuery . ') AS tmp'))[0]['count'];
// pv 统计数据
$this->overview['pv'][$day]['total'] = $this->db->fetchAll($this->db->select('COUNT(1) AS count')
->from('table.access_log')->where("time >= ? AND time <= ?", $start, $end))[0]['count'];
}统计错误已向插件作者反馈，插件作者已修复
下载
因为“Database Query Error”的小问题，暂时就不给插件作者提交 Pull request 了，
有需要的人，可以通过以下方式获取 Access 插件：
- 点此从插件作者的 GitHub 下载（推荐从这里下载）
- 点此从我的 GitHub 下载（不定期从上游同步）
点此从本站下载（更新日期：2018-01-27）已删除
有问题在下方留言。
本文由「管理员」创作，并被添加「Typecho 插件 SQLite3 访问统计 适配」标签，最后更新于 2025-09-06 18:04:48，已有 2446 位童鞋阅读过。
本站所有文章除特别声明外，均采用「署名 4.0 国际」许可协议，可自由转载、引用，但需署名作者且注明文章出处！
此处评论已关闭