v1.0.0

PriviMetrics 2

← Changes All versions

592 lines · 21.5 KB

<?php
declare(strict_types=1);
require_once __DIR__ . '/pmaf.php';
class StorageManager
{
private ?PDO $pdo = null;
private PmafStorage $pmaf;
public function __construct()
{
if (env('DB_ENABLED', 'false') === 'true') {
$this->connectMySQL();
if ($this->pdo) {
$this->ensureSchema();
}
}
$this->pmaf = new PmafStorage(PM_DATA_DIR);
}
private function connectMySQL(): void
{
$host = env('DB_HOST', 'localhost');
$port = env('DB_PORT', '3306');
$name = env('DB_NAME', '');
$user = env('DB_USER', '');
$pass = env('DB_PASS', '');
try {
$this->pdo = new PDO(
"mysql:host={$host};port={$port};dbname={$name};charset=utf8mb4",
$user,
$pass,
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]
);
} catch (PDOException $e) {
$this->pdo = null;
error_log('PriviMetrics: MySQL connection failed: ' . $e->getMessage());
}
}
public function isMySQLAvailable(): bool
{
return $this->pdo !== null;
}
public function pdo(): ?PDO
{
return $this->pdo;
}
public function ensureSchema(): void
{
if (!$this->pdo) return;
$this->pdo->exec("
CREATE TABLE IF NOT EXISTS pm_sites (
id VARCHAR(32) PRIMARY KEY,
name VARCHAR(255) NOT NULL,
domain VARCHAR(255) NOT NULL,
tracking_code VARCHAR(64) NOT NULL UNIQUE,
storage ENUM('xml','mysql','pmaf') NOT NULL DEFAULT 'xml',
restriction_mode ENUM('full','main','none') NOT NULL DEFAULT 'full',
traffic_sources_mode ENUM('inherit','on','off') NOT NULL DEFAULT 'inherit',
visitor_countries_mode ENUM('inherit','on','off') NOT NULL DEFAULT 'inherit',
search_queries_mode ENUM('inherit','on','off') NOT NULL DEFAULT 'inherit',
active TINYINT(1) NOT NULL DEFAULT 1,
created_at INT NOT NULL,
INDEX idx_tracking_code (tracking_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
");
foreach (
[
'traffic_sources_mode' => "ALTER TABLE pm_sites ADD COLUMN traffic_sources_mode ENUM('inherit','on','off') NOT NULL DEFAULT 'inherit'",
'visitor_countries_mode' => "ALTER TABLE pm_sites ADD COLUMN visitor_countries_mode ENUM('inherit','on','off') NOT NULL DEFAULT 'inherit'",
'search_queries_mode' => "ALTER TABLE pm_sites ADD COLUMN search_queries_mode ENUM('inherit','on','off') NOT NULL DEFAULT 'inherit'",
] as $column => $alterSql
) {
try {
$exists = $this->pdo->query("SHOW COLUMNS FROM pm_sites LIKE '{$column}'")->fetch();
if (!$exists) $this->pdo->exec($alterSql);
} catch (PDOException $e) {
}
}
$this->pdo->exec("
CREATE TABLE IF NOT EXISTS pm_hits (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
site_id VARCHAR(32) NOT NULL,
ts INT NOT NULL,
user_hash CHAR(32) NOT NULL,
ip_partial VARCHAR(45) NOT NULL,
country VARCHAR(100) NOT NULL DEFAULT 'Unknown',
country_code CHAR(2) NOT NULL DEFAULT 'XX',
user_agent VARCHAR(255) NOT NULL DEFAULT '',
page_url VARCHAR(1024) NOT NULL DEFAULT '',
page_title VARCHAR(512) NOT NULL DEFAULT '',
search_query VARCHAR(255) NOT NULL DEFAULT '',
referrer VARCHAR(255) NOT NULL DEFAULT '',
utm_source VARCHAR(255) NOT NULL DEFAULT '',
INDEX idx_site_ts (site_id, ts),
INDEX idx_site_page (site_id, page_url(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
");
try {
$exists = $this->pdo->query("SHOW COLUMNS FROM pm_hits LIKE 'utm_source'")->fetch();
if (!$exists) {
$this->pdo->exec("ALTER TABLE pm_hits ADD COLUMN utm_source VARCHAR(255) NOT NULL DEFAULT ''");
}
} catch (PDOException $e) {
}
$this->pdo->exec("
CREATE TABLE IF NOT EXISTS pm_admin (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(100) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
created_at INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
");
}
public function sitesBackend(): string
{
return $this->isMySQLAvailable() ? 'mysql' : 'xml';
}
public function listSites(): array
{
if ($this->sitesBackend() === 'mysql') {
$stmt = $this->pdo->query("SELECT * FROM pm_sites ORDER BY created_at DESC");
$sites = $stmt->fetchAll();
} else {
$sites = $this->xmlListSites();
}
return $this->applyCustomOrder($sites);
}
private function applyCustomOrder(array $sites): array
{
$orderRaw = env('SITE_ORDER', '');
if ($orderRaw === '' || $orderRaw === null) {
return $sites;
}
$order = array_filter(array_map('trim', explode(',', $orderRaw)), fn($id) => $id !== '');
if (!$order) {
return $sites;
}
$position = array_flip($order);
$known = [];
$unknown = [];
foreach ($sites as $site) {
if (isset($position[$site['id']])) {
$known[$position[$site['id']]] = $site;
} else {
$unknown[] = $site;
}
}
ksort($known);
return array_merge(array_values($known), $unknown);
}
public function saveSiteOrder(array $orderedIds): bool
{
$validIds = array_column($this->listSitesUnordered(), 'id');
$clean = array_values(array_intersect($orderedIds, $validIds));
if (!$clean) return false;
return envSet('SITE_ORDER', implode(',', $clean));
}
private function listSitesUnordered(): array
{
if ($this->sitesBackend() === 'mysql') {
$stmt = $this->pdo->query("SELECT * FROM pm_sites ORDER BY created_at DESC");
return $stmt->fetchAll();
}
return $this->xmlListSites();
}
public function getSite(string $id): ?array
{
foreach ($this->listSites() as $site) {
if ($site['id'] === $id) return $site;
}
return null;
}
public function findSiteByTrackingCode(string $code): ?array
{
if ($this->sitesBackend() === 'mysql') {
$stmt = $this->pdo->prepare("SELECT * FROM pm_sites WHERE tracking_code = ? AND active = 1 LIMIT 1");
$stmt->execute([$code]);
$row = $stmt->fetch();
return $row ?: null;
}
foreach ($this->xmlListSites() as $site) {
if ($site['tracking_code'] === $code && $site['active']) return $site;
}
return null;
}
public function createSite(
string $name,
string $domain,
string $storage,
string $restrictionMode,
string $trafficSourcesMode = 'inherit',
string $visitorCountriesMode = 'inherit',
string $searchQueriesMode = 'inherit'
): array {
$site = [
'id' => generateID(),
'name' => $name,
'domain' => $domain,
'tracking_code' => bin2hex(random_bytes(12)),
'storage' => $storage,
'restriction_mode' => $restrictionMode,
'traffic_sources_mode' => pmValidFeatureMode($trafficSourcesMode),
'visitor_countries_mode' => pmValidFeatureMode($visitorCountriesMode),
'search_queries_mode' => pmValidFeatureMode($searchQueriesMode),
'active' => 1,
'created_at' => time(),
];
if ($this->sitesBackend() === 'mysql') {
$stmt = $this->pdo->prepare("
INSERT INTO pm_sites (id, name, domain, tracking_code, storage, restriction_mode, traffic_sources_mode, visitor_countries_mode, search_queries_mode, active, created_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
");
$stmt->execute([
$site['id'],
$site['name'],
$site['domain'],
$site['tracking_code'],
$site['storage'],
$site['restriction_mode'],
$site['traffic_sources_mode'],
$site['visitor_countries_mode'],
$site['search_queries_mode'],
$site['active'],
$site['created_at'],
]);
} else {
$sites = $this->xmlListSites();
$sites[] = $site;
$this->xmlSaveSites($sites);
}
return $site;
}
public function updateSite(string $id, array $fields): bool
{
if ($this->sitesBackend() === 'mysql') {
$allowed = ['name', 'domain', 'storage', 'restriction_mode', 'active', 'traffic_sources_mode', 'visitor_countries_mode', 'search_queries_mode'];
$sets = [];
$params = [];
foreach ($fields as $k => $v) {
if (!in_array($k, $allowed, true)) continue;
$sets[] = "{$k} = ?";
$params[] = $v;
}
if (!$sets) return false;
$params[] = $id;
$stmt = $this->pdo->prepare("UPDATE pm_sites SET " . implode(', ', $sets) . " WHERE id = ?");
return $stmt->execute($params);
}
$sites = $this->xmlListSites();
$found = false;
foreach ($sites as &$site) {
if ($site['id'] === $id) {
foreach ($fields as $k => $v) {
$site[$k] = $v;
}
$found = true;
break;
}
}
unset($site);
if ($found) $this->xmlSaveSites($sites);
return $found;
}
public function deleteSite(string $id): bool
{
$site = $this->getSite($id);
if (!$site) return false;
if ($this->sitesBackend() === 'mysql') {
$stmt = $this->pdo->prepare("DELETE FROM pm_sites WHERE id = ?");
$stmt->execute([$id]);
} else {
$sites = array_values(array_filter($this->xmlListSites(), fn($s) => $s['id'] !== $id));
$this->xmlSaveSites($sites);
}
$hitsStorage = $site['storage'] ?? 'xml';
if ($hitsStorage === 'mysql' && $this->isMySQLAvailable()) {
$stmt = $this->pdo->prepare("DELETE FROM pm_hits WHERE site_id = ?");
$stmt->execute([$id]);
} elseif ($hitsStorage === 'pmaf') {
$this->pmaf->deleteAll($id);
} else {
$file = PM_DATA_DIR . '/hits_' . $id . '.xml';
if (file_exists($file)) unlink($file);
}
return true;
}
private function xmlSitesFile(): string
{
return PM_DATA_DIR . '/sites.xml';
}
private function xmlListSites(): array
{
$file = $this->xmlSitesFile();
if (!file_exists($file)) return [];
$xml = @simplexml_load_file($file);
if ($xml === false) return [];
$sites = [];
foreach ($xml->site as $s) {
$sites[] = [
'id' => (string) $s->id,
'name' => (string) $s->name,
'domain' => (string) $s->domain,
'tracking_code' => (string) $s->tracking_code,
'storage' => (string) $s->storage,
'restriction_mode' => (string) $s->restriction_mode,
'traffic_sources_mode' => pmValidFeatureMode((string) ($s->traffic_sources_mode ?? '') ?: 'inherit'),
'visitor_countries_mode' => pmValidFeatureMode((string) ($s->visitor_countries_mode ?? '') ?: 'inherit'),
'search_queries_mode' => pmValidFeatureMode((string) ($s->search_queries_mode ?? '') ?: 'inherit'),
'active' => (string) $s->active === '1' || (string) $s->active === 'true',
'created_at' => (int) $s->created_at,
];
}
return $sites;
}
private function xmlSaveSites(array $sites): void
{
if (!is_dir(PM_DATA_DIR)) mkdir(PM_DATA_DIR, 0755, true);
$xml = new SimpleXMLElement('<sites></sites>');
foreach ($sites as $s) {
$node = $xml->addChild('site');
$node->addChild('id', htmlspecialchars((string) $s['id']));
$node->addChild('name', htmlspecialchars((string) $s['name']));
$node->addChild('domain', htmlspecialchars((string) $s['domain']));
$node->addChild('tracking_code', htmlspecialchars((string) $s['tracking_code']));
$node->addChild('storage', htmlspecialchars((string) $s['storage']));
$node->addChild('restriction_mode', htmlspecialchars((string) $s['restriction_mode']));
$node->addChild('traffic_sources_mode', htmlspecialchars(pmValidFeatureMode($s['traffic_sources_mode'] ?? 'inherit')));
$node->addChild('visitor_countries_mode', htmlspecialchars(pmValidFeatureMode($s['visitor_countries_mode'] ?? 'inherit')));
$node->addChild('search_queries_mode', htmlspecialchars(pmValidFeatureMode($s['search_queries_mode'] ?? 'inherit')));
$node->addChild('active', $s['active'] ? '1' : '0');
$node->addChild('created_at', (string) $s['created_at']);
}
$dom = new DOMDocument('1.0', 'UTF-8');
$dom->preserveWhiteSpace = false;
$dom->formatOutput = true;
$dom->loadXML($xml->asXML());
$dom->save($this->xmlSitesFile());
}
public function saveHit(array $site, array $hit): void
{
$storage = $site['storage'] ?? 'xml';
if ($storage === 'mysql' && $this->isMySQLAvailable()) {
$this->mysqlSaveHit($site['id'], $hit);
} elseif ($storage === 'pmaf') {
$this->pmaf->appendHit($site['id'], $hit, time());
} else {
$this->xmlSaveHit($site['id'], $hit);
}
}
private function mysqlSaveHit(string $siteId, array $hit): void
{
try {
$stmt = $this->pdo->prepare("
INSERT INTO pm_hits
(site_id, ts, user_hash, ip_partial, country, country_code, user_agent, page_url, page_title, search_query, referrer, utm_source)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
");
$stmt->execute([
$siteId,
time(),
$hit['user_hash'],
$hit['ip'],
$hit['country'],
$hit['country_code'],
$hit['user_agent'],
$hit['page_url'],
$hit['page_title'],
$hit['search_query'],
$hit['referrer'],
$hit['utm_source'] ?? '',
]);
} catch (PDOException $e) {
error_log('PriviMetrics: mysqlSaveHit failed: ' . $e->getMessage());
}
}
private function xmlHitsFile(string $siteId): string
{
return PM_DATA_DIR . '/hits_' . $siteId . '.xml';
}
private function xmlSaveHit(string $siteId, array $hit): void
{
if (!is_dir(PM_DATA_DIR)) mkdir(PM_DATA_DIR, 0755, true);
$file = $this->xmlHitsFile($siteId);
$lockFile = $file . '.lock';
$fp = fopen($lockFile, 'c');
if ($fp && flock($fp, LOCK_EX)) {
if (file_exists($file)) {
$xml = @simplexml_load_file($file);
if ($xml === false) {
$xml = new SimpleXMLElement('<hits></hits>');
}
} else {
$xml = new SimpleXMLElement('<hits></hits>');
}
$node = $xml->addChild('hit');
$node->addChild('ts', (string) time());
$node->addChild('user_hash', htmlspecialchars($hit['user_hash']));
$node->addChild('ip', htmlspecialchars($hit['ip']));
$node->addChild('country', htmlspecialchars($hit['country']));
$node->addChild('country_code', htmlspecialchars($hit['country_code']));
$node->addChild('user_agent', htmlspecialchars($hit['user_agent']));
$node->addChild('page_url', htmlspecialchars($hit['page_url']));
$node->addChild('page_title', htmlspecialchars($hit['page_title']));
$node->addChild('search_query', htmlspecialchars($hit['search_query']));
$node->addChild('referrer', htmlspecialchars($hit['referrer']));
$node->addChild('utm_source', htmlspecialchars($hit['utm_source'] ?? ''));
$dom = new DOMDocument('1.0', 'UTF-8');
$dom->preserveWhiteSpace = false;
$dom->formatOutput = true;
$dom->loadXML($xml->asXML());
$dom->save($file);
flock($fp, LOCK_UN);
fclose($fp);
}
}
public function getHits(array $site, int $start, int $end): array
{
$storage = $site['storage'] ?? 'xml';
if ($storage === 'mysql' && $this->isMySQLAvailable()) {
return $this->mysqlGetHits($site['id'], $start, $end);
}
if ($storage === 'pmaf') {
return $this->pmaf->getHits($site['id'], $start, $end);
}
return $this->xmlGetHits($site['id'], $start, $end);
}
private function mysqlGetHits(string $siteId, int $start, int $end): array
{
$stmt = $this->pdo->prepare("
SELECT * FROM pm_hits WHERE site_id = ? AND ts BETWEEN ? AND ? ORDER BY ts ASC
");
$stmt->execute([$siteId, $start, $end]);
return $stmt->fetchAll();
}
private function xmlGetHits(string $siteId, int $start, int $end): array
{
$file = $this->xmlHitsFile($siteId);
if (!file_exists($file)) return [];
$xml = @simplexml_load_file($file);
if ($xml === false) return [];
$hits = [];
foreach ($xml->hit as $h) {
$ts = (int) $h->ts;
if ($ts < $start || $ts > $end) continue;
$hits[] = [
'ts' => $ts,
'user_hash' => (string) $h->user_hash,
'ip_partial' => (string) $h->ip,
'country' => (string) $h->country,
'country_code' => (string) $h->country_code,
'user_agent' => (string) $h->user_agent,
'page_url' => (string) $h->page_url,
'page_title' => (string) $h->page_title,
'search_query' => (string) $h->search_query,
'referrer' => (string) $h->referrer,
'utm_source' => (string) ($h->utm_source ?? ''),
];
}
return $hits;
}
public function countAllHits(array $sites): int
{
$total = 0;
foreach ($sites as $site) {
$storage = $site['storage'] ?? 'xml';
if ($storage === 'mysql' && $this->isMySQLAvailable()) {
$stmt = $this->pdo->prepare("SELECT COUNT(*) c FROM pm_hits WHERE site_id = ?");
$stmt->execute([$site['id']]);
$total += (int) $stmt->fetch()['c'];
} elseif ($storage === 'pmaf') {
$total += $this->pmaf->countHits($site['id']);
} else {
$file = $this->xmlHitsFile($site['id']);
if (file_exists($file)) {
$xml = @simplexml_load_file($file);
if ($xml !== false) $total += count($xml->hit);
}
}
}
return $total;
}
public function createAdmin(string $username, string $passwordHash): void
{
if (!$this->pdo) return;
$stmt = $this->pdo->prepare("
INSERT INTO pm_admin (username, password_hash, created_at) VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE password_hash = VALUES(password_hash)
");
$stmt->execute([$username, $passwordHash, time()]);
}
public function cleanOldData(int $days = 365): void
{
$cutoff = time() - ($days * 86400);
if ($this->isMySQLAvailable()) {
$stmt = $this->pdo->prepare("DELETE FROM pm_hits WHERE ts < ?");
$stmt->execute([$cutoff]);
}
foreach (glob(PM_DATA_DIR . '/hits_*.xml') as $file) {
if (filemtime($file) < $cutoff) {
$xml = @simplexml_load_file($file);
if ($xml === false) continue;
$kept = new SimpleXMLElement('<hits></hits>');
foreach ($xml->hit as $h) {
if ((int) $h->ts >= $cutoff) {
$node = $kept->addChild('hit');
foreach ($h->children() as $childName => $childVal) {
$node->addChild($childName, htmlspecialchars((string) $childVal));
}
}
}
$dom = new DOMDocument('1.0', 'UTF-8');
$dom->preserveWhiteSpace = false;
$dom->formatOutput = true;
$dom->loadXML($kept->asXML());
$dom->save($file);
}
}
foreach (glob(PM_DATA_DIR . '/hits_*.pmaf') as $file) {
if (filemtime($file) < $cutoff) {
preg_match('/hits_(.+)\.pmaf$/', $file, $m);
if (isset($m[1])) {
$this->pmaf->pruneOlderThan($m[1], $cutoff);
}
}
}
}
}