431 lines
11 KiB
PHP
431 lines
11 KiB
PHP
<?php
|
|
|
|
class MySQLAdapter {
|
|
public $raw;
|
|
|
|
function __construct($SITE_CONFIG) {
|
|
$this->SITE_CONFIG = $SITE_CONFIG;
|
|
|
|
$db_params = $SITE_CONFIG['db'];
|
|
|
|
try {
|
|
if(false !== getenv('MYSQL_HOST')) {
|
|
$this->raw = mysqli_connect(getenv('MYSQL_HOST'),
|
|
getenv('MYSQL_USER'), getenv('MYSQL_PASSWORD'),
|
|
getenv('MYSQL_DATABASE'),
|
|
getenv('MYSQL_PORT'));
|
|
}
|
|
else {
|
|
$this->raw = mysqli_connect($db_params['host'],
|
|
$db_params['user'], $db_params['password'],
|
|
$db_params['database'],
|
|
$db_params['port']);
|
|
}
|
|
} catch (\Throwable $th) {
|
|
echo 'Connection failed<br>';
|
|
echo 'Error number: ' . mysqli_connect_errno() . '<br>';
|
|
echo 'Error message: ' . mysqli_connect_error() . '<br>';
|
|
die();
|
|
}
|
|
}
|
|
|
|
function _sanitize_path($post_path) {
|
|
$post_path = chop($post_path, '/');
|
|
|
|
if($post_path == "") {
|
|
return "";
|
|
}
|
|
|
|
if(!preg_match('/^(?:\/[\w-]+)+(?:\.[\w-]+)*$/', $post_path)) {
|
|
echo "Post path match against " . $post_path . " failed!";
|
|
die();
|
|
}
|
|
|
|
return $post_path;
|
|
}
|
|
|
|
function _exec($qery, $argtypes = '', ...$args) {
|
|
$stmt = $this->raw->prepare($qery);
|
|
if($argtypes != ""){
|
|
$stmt->bind_param($argtypes, ...$args);
|
|
}
|
|
$stmt->execute();
|
|
|
|
return $stmt->get_result();
|
|
}
|
|
|
|
function _normalize_post_data($post_data) {
|
|
$post_data ??= ['found' => null];
|
|
|
|
if(isset($post_data['found']) && $post_data['found'] == false) {
|
|
return $post_data;
|
|
}
|
|
|
|
$post_data["found"] = true;
|
|
|
|
$post_data['post_metadata'] = json_decode($post_data["post_metadata"], true) ?? [];
|
|
$post_data["post_content"] ??= '';
|
|
|
|
return $post_data;
|
|
}
|
|
|
|
function _normalize_post_array($post_data) {
|
|
$post_data ??= [];
|
|
|
|
return array_map(function($post) {
|
|
return $this->_normalize_post_data($post);
|
|
}, $post_data);
|
|
}
|
|
|
|
function bump_post($post_path, $post_metadata = [], $create_dirs = true) {
|
|
$post_path = $this->_sanitize_path($post_path);
|
|
$path_depth = substr_count($post_path, "/");
|
|
|
|
if($create_dirs) {
|
|
$this->make_post_directory(dirname($post_path));
|
|
}
|
|
|
|
$qry = "
|
|
INSERT INTO posts
|
|
(host, post_path, post_path_depth, post_metadata, post_content)
|
|
VALUES
|
|
( ?, ?, ?, ?, ?) AS new
|
|
ON DUPLICATE KEY UPDATE post_path=new.post_path;";
|
|
|
|
$this->_exec($qry, "ssiss",
|
|
$this->SITE_CONFIG['HTTP_HOST'],
|
|
$post_path,
|
|
$path_depth,
|
|
json_encode($post_metadata),
|
|
'');
|
|
}
|
|
|
|
function make_post_directory($directory) {
|
|
$json_metadata = ["type" => 'directory'];
|
|
|
|
while(strlen($directory) > 1) {
|
|
try {
|
|
$this->bump_post($directory, $json_metadata, false);
|
|
}
|
|
catch(Exception $e) {
|
|
}
|
|
|
|
$directory = dirname($directory);
|
|
}
|
|
}
|
|
|
|
function log_post_access($post_path, $agent, $referrer, $time) {
|
|
$post_path = $this->_sanitize_path($post_path);
|
|
|
|
$qry = "INSERT INTO path_access_counts
|
|
(access_time,
|
|
host, post_path, agent, referrer,
|
|
path_access_count,
|
|
path_processing_time)
|
|
VALUES ( from_unixtime(floor(unix_timestamp(CURRENT_TIMESTAMP) / 300)*300),
|
|
?, ?, ?, ?, 1, ?
|
|
) AS new
|
|
ON DUPLICATE KEY
|
|
UPDATE path_access_count=path_access_counts.path_access_count+1,
|
|
path_processing_time=path_access_counts.path_processing_time+new.path_processing_time;
|
|
";
|
|
|
|
$this->_exec($qry, "ssssd", $this->SITE_CONFIG['HTTP_HOST'], $post_path, $agent, $referrer, $time);
|
|
|
|
if(preg_match('/^user/', $agent)) {
|
|
$this->_exec("UPDATE posts SET post_access_count=post_access_count+1 WHERE post_path=? AND host=?", "ss",
|
|
$post_path, $this->SITE_CONFIG['HTTP_HOST']);
|
|
}
|
|
}
|
|
|
|
function get_post_access_counters() {
|
|
$qry = "
|
|
SELECT host, post_path, agent, path_access_count, path_processing_time
|
|
FROM path_access_counts
|
|
WHERE path_last_access_time > ( CURRENT_TIMESTAMP - INTERVAL 10 MINUTE );
|
|
";
|
|
|
|
$data = $this->_exec($qry, "")->fetch_all(MYSQLI_ASSOC);
|
|
|
|
$out_data = [];
|
|
|
|
foreach($data AS $post_data) {
|
|
$path = $post_data['post_path'];
|
|
|
|
$agent_data = ($out_data[$path] ?? []);
|
|
|
|
$agent_data[$post_data['agent']] = [
|
|
'count' => $post_data['path_access_count'],
|
|
'time' => round($post_data['path_processing_time'], 6)
|
|
];
|
|
|
|
$out_data[$path] = $agent_data;
|
|
}
|
|
|
|
return $out_data;
|
|
}
|
|
|
|
function get_post_access_counters_line() {
|
|
$qry = "
|
|
SELECT host, access_time, post_path, agent, referrer, path_access_count, path_processing_time
|
|
FROM path_access_counts
|
|
WHERE access_time < ( CURRENT_TIMESTAMP - INTERVAL 6 MINUTE )
|
|
ORDER BY access_time DESC;
|
|
";
|
|
|
|
$this->raw->begin_transaction();
|
|
$top_access_time = null;
|
|
|
|
try {
|
|
$data = $this->_exec($qry, "")->fetch_all(MYSQLI_ASSOC);
|
|
|
|
$data_prefix="access_metrics";
|
|
|
|
$out_data = "";
|
|
|
|
foreach($data AS $post_data) {
|
|
$top_access_time ??= $post_data['access_time'];
|
|
|
|
$path = $post_data['post_path'];
|
|
if($path == '') {
|
|
$path = '/';
|
|
}
|
|
$out_data .= $data_prefix . ",host=" . $post_data['host'] . ",agent=".$post_data['agent'];
|
|
$out_data .= ",path=".$path.",referrer=".$post_data['referrer'];
|
|
|
|
$out_data .= " access_sum=" . $post_data['path_access_count'] . ",time_sum=" . $post_data['path_processing_time'];
|
|
$out_data .= " " . strtotime($post_data['access_time']) . "000000000\n";
|
|
}
|
|
|
|
|
|
$this->_exec("DELETE FROM path_access_counts WHERE access_time <= ?", "s", $top_access_time);
|
|
|
|
$this->raw->commit();
|
|
return $out_data;
|
|
|
|
} catch (\Throwable $th) {
|
|
$this->raw->rollback();
|
|
|
|
throw $th;
|
|
}
|
|
}
|
|
|
|
function reset_post_settings_cache($post_path) {
|
|
$post_path = $this->_sanitize_path($post_path);
|
|
|
|
$this->_exec("
|
|
UPDATE posts
|
|
SET post_settings_cache=NULL
|
|
WHERE host = ? AND post_path LIKE ?;
|
|
", "ss", $this->SITE_CONFIG['HTTP_HOST'], $post_path . "%");
|
|
}
|
|
|
|
function escape_tag($tag) {
|
|
return preg_replace_callback('/[\WZ]/', function($match) {
|
|
return "Z" . ord($match[0]);
|
|
}, strtolower($tag));
|
|
}
|
|
|
|
function escape_search_tag($tag) {
|
|
preg_match("/^([\+\-]?)(.*?)(\*?)$/", $tag, $matches);
|
|
|
|
if(!isset($matches[1])) {
|
|
echo "Problem with tag!";
|
|
var_dump($tag);
|
|
}
|
|
|
|
return $matches[1] . $this->escape_tag($matches[2]) . $matches[3];
|
|
}
|
|
|
|
function update_post_search_data($post_path, $post_tags) {
|
|
$post_tags []= "path:" . $post_path;
|
|
$post_tags []= "host:" . $this->SITE_CONFIG['HTTP_HOST'];
|
|
|
|
$post_tags = array_unique($post_tags);
|
|
$post_tags = array_map(function($val) {
|
|
return $this->escape_tag($val);
|
|
}, $post_tags);
|
|
|
|
asort($post_tags);
|
|
$post_tags = join(' ', $post_tags);
|
|
|
|
$qry = "
|
|
INSERT INTO posts
|
|
( host, post_path, post_tags )
|
|
VALUES
|
|
( ?, ?, ? ) AS new
|
|
ON DUPLICATE KEY
|
|
UPDATE post_tags=new.post_tags;
|
|
";
|
|
|
|
$this->_exec($qry, "sss",
|
|
$this->SITE_CONFIG['HTTP_HOST'], $post_path, $post_tags);
|
|
}
|
|
|
|
function perform_post_search($taglist, $order = null, $limit = 20, $page = 0) {
|
|
$allowed_ordering = [
|
|
"post_create_time"
|
|
];
|
|
|
|
$qry = "
|
|
SELECT post_path, post_metadata
|
|
FROM posts
|
|
WHERE MATCH(post_tags) AGAINST (? IN BOOLEAN MODE)
|
|
";
|
|
|
|
if(!is_array($taglist)) {
|
|
$taglist = explode(' ', $taglist);
|
|
}
|
|
|
|
$taglist []= '+host:' . $this->SITE_CONFIG['HTTP_HOST'];
|
|
$taglist = array_unique($taglist);
|
|
$taglist = array_map(function($key) {
|
|
return $this->escape_search_tag($key);
|
|
}, $taglist);
|
|
|
|
$taglist = implode(' ', $taglist);
|
|
|
|
if(isset($order) and in_array($order, $allowed_ordering)) {
|
|
$qry = $qry . " ORDER BY " . $order;
|
|
}
|
|
|
|
$qry = $qry . " LIMIT ? OFFSET ?";
|
|
|
|
$search_results = $this->_exec($qry, "sii", $taglist, $limit, $limit * $page)->fetch_all(MYSQLI_ASSOC);
|
|
$search_results = [
|
|
"query_string" => $taglist,
|
|
"results" => $this->_normalize_post_array($search_results)
|
|
];
|
|
|
|
return $search_results;
|
|
}
|
|
|
|
function update_or_create_post($post_path, $post_metadata, $post_content) {
|
|
$post_path = $this->_sanitize_path($post_path);
|
|
$path_depth = substr_count($post_path, "/");
|
|
|
|
$this->make_post_directory(dirname($post_path));
|
|
|
|
$this->reset_post_settings_cache($post_path);
|
|
|
|
$qry = "
|
|
INSERT INTO posts
|
|
(host, post_path, post_path_depth, post_metadata, post_content)
|
|
VALUES
|
|
( ?, ?, ?, ?, ?) AS new
|
|
ON DUPLICATE KEY
|
|
UPDATE post_metadata=new.post_metadata,
|
|
post_content=new.post_content,
|
|
post_update_time=CURRENT_TIMESTAMP;";
|
|
|
|
$this->_exec($qry, "ssiss",
|
|
$this->SITE_CONFIG['HTTP_HOST'],
|
|
$post_path,
|
|
$path_depth,
|
|
json_encode($post_metadata),
|
|
$post_content);
|
|
|
|
$this->update_post_search_data($post_path, $post_metadata['tags'] ?? []);
|
|
}
|
|
|
|
function get_settings_for_path($post_path) {
|
|
$post_path = $this->_sanitize_path($post_path);
|
|
|
|
$post_settings = $this->_exec("
|
|
SELECT post_path, post_settings_cache
|
|
FROM posts
|
|
WHERE post_path = ? AND host = ?
|
|
", "ss", $post_path, $this->SITE_CONFIG['HTTP_HOST'])->fetch_assoc();
|
|
|
|
if(!isset($post_settings)) {
|
|
return [];
|
|
}
|
|
if(isset($post_settings['post_settings_cache'])) {
|
|
return json_decode($post_settings['post_settings_cache'], true);
|
|
}
|
|
|
|
$parent_settings = [];
|
|
if($post_path != "") {
|
|
$parent_settings = $this->get_settings_for_path(dirname($post_path));
|
|
}
|
|
|
|
$post_settings = [];
|
|
$post_metadata = $this->_exec("
|
|
SELECT post_path, post_metadata
|
|
FROM posts
|
|
WHERE post_path = ? AND host = ?
|
|
", "ss", $post_path, $this->SITE_CONFIG['HTTP_HOST'])->fetch_assoc();
|
|
|
|
if(isset($post_metadata['post_metadata'])) {
|
|
$post_metadata = json_decode($post_metadata['post_metadata'], true);
|
|
|
|
if(isset($post_metadata['settings'])) {
|
|
$post_settings = $post_metadata['settings'];
|
|
}
|
|
}
|
|
|
|
$post_settings = array_merge($parent_settings, $post_settings);
|
|
|
|
$this->_exec("UPDATE posts SET post_settings_cache=? WHERE post_path=?", "ss",
|
|
json_encode($post_settings), $post_path);
|
|
|
|
return $post_settings;
|
|
}
|
|
|
|
function get_post_by_path($post_path,
|
|
$with_subposts = false, $with_settings = true) {
|
|
|
|
$qry = "SELECT *
|
|
FROM posts
|
|
WHERE post_path = ? AND host = ?
|
|
";
|
|
|
|
$post_path = $this->_sanitize_path($post_path);
|
|
|
|
$post_data = $this->_exec($qry, "ss", $post_path, $this->SITE_CONFIG['HTTP_HOST'])->fetch_assoc();
|
|
|
|
$post_data ??= ['found' => false];
|
|
$post_data['post_path'] = $post_path;
|
|
|
|
$post_data = $this->_normalize_post_data($post_data);
|
|
|
|
if(!$post_data['found']) {
|
|
return $post_data;
|
|
}
|
|
|
|
if($with_subposts) {
|
|
$post_data['subposts'] = $this->get_subposts_by_path($post_path);
|
|
}
|
|
if($with_settings) {
|
|
$post_data['settings'] = $this->get_settings_for_path($post_path);
|
|
}
|
|
|
|
return $post_data;
|
|
}
|
|
|
|
function get_subposts_by_path($path) {
|
|
global $sql;
|
|
|
|
$path = $this->_sanitize_path($path);
|
|
|
|
$path_depth = substr_count($path, "/");
|
|
|
|
$qry = "SELECT post_path, post_metadata, post_update_time
|
|
FROM posts
|
|
WHERE
|
|
host = ?
|
|
AND (post_path LIKE CONCAT(?,'/%'))
|
|
AND post_path_depth = ?
|
|
ORDER BY post_path ASC
|
|
LIMIT 50";
|
|
|
|
$post_data = $this->_exec($qry, "ssi", $this->SITE_CONFIG['HTTP_HOST'],
|
|
$path, $path_depth+1)->fetch_all(MYSQLI_ASSOC);
|
|
|
|
$post_data = $this->_normalize_post_array($post_data);
|
|
|
|
return $post_data;
|
|
}
|
|
}
|
|
|
|
?>
|