321 lines
8.0 KiB
PHP
321 lines
8.0 KiB
PHP
<?php
|
|
/* μlogger
|
|
*
|
|
* Copyright(C) 2017 Bartek Fabiszewski (www.fabiszewski.net)
|
|
*
|
|
* This is free software; you can redistribute it and/or modify it under
|
|
* the terms of the GNU General Public License as published by
|
|
* the Free Software Foundation; either version 3 of the License, or
|
|
* (at your option) any later version.
|
|
*
|
|
* This program is distributed in the hope that it will be useful, but
|
|
* WITHOUT ANY WARRANTY; without even the implied warranty of
|
|
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
|
|
* General Public License for more details.
|
|
*
|
|
* You should have received a copy of the GNU General Public License
|
|
* along with this program; if not, see <http://www.gnu.org/licenses/>.
|
|
*/
|
|
|
|
require_once(ROOT_DIR . "/helpers/utils.php");
|
|
|
|
/**
|
|
* PDO wrapper
|
|
*/
|
|
class uDb extends PDO {
|
|
/**
|
|
* Singleton instance
|
|
*
|
|
* @var uDb Object instance
|
|
*/
|
|
protected static $instance;
|
|
|
|
/**
|
|
* Table names
|
|
*
|
|
* @var array Array of names
|
|
*/
|
|
protected static $tables;
|
|
|
|
/**
|
|
* Database driver name
|
|
*
|
|
* @var string Driver
|
|
*/
|
|
protected static $driver;
|
|
|
|
/**
|
|
* @var string Database DSN
|
|
*/
|
|
private static $dbdsn = "";
|
|
/**
|
|
* @var string Database user
|
|
*/
|
|
private static $dbuser = "";
|
|
/**
|
|
* @var string Database pass
|
|
*/
|
|
private static $dbpass = "";
|
|
/**
|
|
* @var string Optional table names prefix, eg. "ulogger_"
|
|
*/
|
|
private static $dbprefix = "";
|
|
|
|
/**
|
|
* PDO constuctor
|
|
*
|
|
* @param string $dsn
|
|
* @param string $user
|
|
* @param string $pass
|
|
*/
|
|
public function __construct($dsn, $user, $pass) {
|
|
try {
|
|
$options = [
|
|
PDO::ATTR_EMULATE_PREPARES => false, // try to use native prepared statements
|
|
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // throw exceptions
|
|
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // return assoc array by default
|
|
];
|
|
@parent::__construct($dsn, $user, $pass, $options);
|
|
self::$driver = $this->getAttribute(PDO::ATTR_DRIVER_NAME);
|
|
$this->setCharset("utf8");
|
|
$this->initTables();
|
|
} catch (PDOException $e) {
|
|
header("HTTP/1.1 503 Service Unavailable");
|
|
die("Database connection error (" . $e->getMessage() . ")");
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Initialize table names based on config
|
|
*/
|
|
private function initTables() {
|
|
self::$tables = [];
|
|
$prefix = preg_replace('/[^a-z0-9_]/i', '', self::$dbprefix);
|
|
self::$tables['positions'] = $prefix . "positions";
|
|
self::$tables['tracks'] = $prefix . "tracks";
|
|
self::$tables['users'] = $prefix . "users";
|
|
self::$tables['config'] = $prefix . "config";
|
|
self::$tables['ol_layers'] = $prefix . "ol_layers";
|
|
}
|
|
|
|
/**
|
|
* Returns singleton instance
|
|
*
|
|
* @return uDb Singleton instance
|
|
*/
|
|
public static function getInstance() {
|
|
if (!self::$instance) {
|
|
self::getConfig();
|
|
self::$instance = new self(self::$dbdsn, self::$dbuser, self::$dbpass);
|
|
}
|
|
return self::$instance;
|
|
}
|
|
|
|
/**
|
|
* Read database setup from config file
|
|
* @noinspection IssetArgumentExistenceInspection
|
|
* @noinspection PhpIncludeInspection
|
|
*/
|
|
private static function getConfig() {
|
|
$configFile = dirname(__DIR__) . "/config.php";
|
|
if (!file_exists($configFile)) {
|
|
header("HTTP/1.1 503 Service Unavailable");
|
|
die("Missing config.php file!");
|
|
}
|
|
include($configFile);
|
|
if (isset($dbdsn)) {
|
|
self::$dbdsn = self::normalizeDsn($dbdsn);
|
|
}
|
|
if (isset($dbuser)) {
|
|
self::$dbuser = $dbuser;
|
|
}
|
|
if (isset($dbpass)) {
|
|
self::$dbpass = $dbpass;
|
|
}
|
|
if (isset($dbprefix)) {
|
|
self::$dbprefix = $dbprefix;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Get full table name including prefix
|
|
*
|
|
* @param string $name Name
|
|
* @return string Full table name
|
|
*/
|
|
public function table($name) {
|
|
return self::$tables[$name];
|
|
}
|
|
|
|
/**
|
|
* Returns function name for getting date-time column value as unix timestamp
|
|
* @param string $column
|
|
* @return string
|
|
*/
|
|
public function unix_timestamp($column) {
|
|
switch (self::$driver) {
|
|
default:
|
|
case "mysql":
|
|
return "UNIX_TIMESTAMP($column)";
|
|
break;
|
|
case "pgsql":
|
|
return "EXTRACT(EPOCH FROM $column::TIMESTAMP WITH TIME ZONE)";
|
|
break;
|
|
case "sqlite":
|
|
return "STRFTIME('%s', $column)";
|
|
break;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Returns placeholder for LOB data types
|
|
* @return string
|
|
*/
|
|
public function lobPlaceholder() {
|
|
switch (self::$driver) {
|
|
default:
|
|
case "mysql":
|
|
case "sqlite":
|
|
return "?";
|
|
break;
|
|
case "pgsql":
|
|
return "?::bytea";
|
|
break;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Returns construct for getting LOB as string
|
|
* @param string $column Column name
|
|
* @return string
|
|
*/
|
|
public function from_lob($column) {
|
|
switch (self::$driver) {
|
|
default:
|
|
case "mysql":
|
|
case "sqlite":
|
|
return $column;
|
|
break;
|
|
case "pgsql":
|
|
return "encode($column, 'escape') AS $column";
|
|
break;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Returns function name for getting date-time column value as 'YYYY-MM-DD hh:mm:ss'
|
|
* @param string $column
|
|
* @return string
|
|
*/
|
|
public function from_unixtime($column) {
|
|
switch (self::$driver) {
|
|
default:
|
|
case "mysql":
|
|
return "FROM_UNIXTIME($column)";
|
|
break;
|
|
case "pgsql":
|
|
return "TO_TIMESTAMP($column)";
|
|
break;
|
|
case "sqlite":
|
|
return "DATETIME($column, 'unixepoch')";
|
|
break;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Replace into
|
|
* Note: requires PostgreSQL >= 9.5
|
|
* @param string $table Table name (without prefix)
|
|
* @param string[] $columns Column names
|
|
* @param string[][] $values Values [ [ value1, value2 ], ... ]
|
|
* @param string $key Unique column
|
|
* @param string $update Updated column
|
|
* @return string
|
|
*/
|
|
public function insertOrReplace($table, $columns, $values, $key, $update) {
|
|
$cols = implode(", ", $columns);
|
|
$rows = [];
|
|
foreach ($values as $row) {
|
|
$rows[] = "(" . implode(", ", $row) . ")";
|
|
}
|
|
$vals = implode(", ", $rows);
|
|
switch (self::$driver) {
|
|
default:
|
|
case "mysql":
|
|
return "INSERT INTO {$this->table($table)} ($cols)
|
|
VALUES $vals
|
|
ON DUPLICATE KEY UPDATE $update = VALUES($update)";
|
|
break;
|
|
case "pgsql":
|
|
return "INSERT INTO {$this->table($table)} ($cols)
|
|
VALUES $vals
|
|
ON CONFLICT ($key) DO UPDATE SET $update = EXCLUDED.$update";
|
|
break;
|
|
case "sqlite":
|
|
return "REPLACE INTO {$this->table($table)} ($cols)
|
|
VALUES $vals";
|
|
break;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Set character set
|
|
* @param string $charset
|
|
*/
|
|
private function setCharset($charset) {
|
|
if (self::$driver === "pgsql" || self::$driver === "mysql") {
|
|
$this->exec("SET NAMES '$charset'");
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Extract database name from DSN
|
|
* @param string $dsn
|
|
* @return string Empty string if not found
|
|
*/
|
|
public static function getDbName($dsn) {
|
|
$name = "";
|
|
if ($dsn && strpos($dsn, ":") !== false) {
|
|
list($scheme, $dsnWithoutScheme) = explode(":", $dsn, 2);
|
|
switch ($scheme) {
|
|
case "sqlite":
|
|
case "sqlite2":
|
|
case "sqlite3":
|
|
$pattern = "/(.+)/";
|
|
break;
|
|
case "pgsql":
|
|
$pattern = "/dbname=([^; ]+)/";
|
|
break;
|
|
default:
|
|
$pattern = "/dbname=([^;]+)/";
|
|
break;
|
|
}
|
|
$result = preg_match($pattern, $dsnWithoutScheme, $matches);
|
|
if ($result === 1) {
|
|
$name = $matches[1];
|
|
}
|
|
}
|
|
return $name;
|
|
}
|
|
|
|
/**
|
|
* Normalize DSN.
|
|
* Make sure sqlite DSN file path is absolute
|
|
* @param string $dsn DSN
|
|
* @return string Normalized DSN
|
|
*/
|
|
public static function normalizeDsn($dsn) {
|
|
if (stripos($dsn, "sqlite") !== 0) {
|
|
return $dsn;
|
|
}
|
|
$arr = explode(":", $dsn, 2);
|
|
if (count($arr) < 2 || empty($arr[1]) || uUtils::isAbsolutePath($arr[1])) {
|
|
return $dsn;
|
|
}
|
|
$scheme = $arr[0];
|
|
$path = dirname(__DIR__) . DIRECTORY_SEPARATOR . $arr[1];
|
|
return $scheme . ":" . realpath(dirname($path)) . DIRECTORY_SEPARATOR . basename(($path));
|
|
}
|
|
}
|
|
?>
|