359 lines
10 KiB
PHP
359 lines
10 KiB
PHP
<?php
|
|
/**
|
|
* Spacetraders API Client Library - Storage
|
|
*
|
|
* SQLite storage for configuration and API cache.
|
|
*
|
|
* @category Storage
|
|
* @package SpacetradersAPI
|
|
* @author Keith Solomon <keith@keithsolomon.net>
|
|
* @license MIT License
|
|
* @version GIT: <git_id>
|
|
* @link https://git.keithsolomon.net/keith/Spacetraders
|
|
*/
|
|
|
|
/**
|
|
* Storage for configuration and API cache.
|
|
*/
|
|
class SpacetradersStorage {
|
|
/**
|
|
* Database handle.
|
|
*
|
|
* @var PDO
|
|
*/
|
|
private PDO $db;
|
|
|
|
/**
|
|
* Create storage and initialize schema.
|
|
*
|
|
* @param string $dbPath Absolute or project-relative SQLite database path.
|
|
*/
|
|
public function __construct( string $dbPath ) {
|
|
$directory = dirname( $dbPath );
|
|
if (! is_dir( $directory ) ) {
|
|
mkdir( $directory, 0777, true );
|
|
}
|
|
|
|
$this->db = new PDO( 'sqlite:' . $dbPath );
|
|
$this->db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
|
|
$this->db->setAttribute( PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC );
|
|
|
|
$this->_initializeSchema();
|
|
}
|
|
|
|
/**
|
|
* Create required tables if they do not exist.
|
|
*
|
|
* @return void
|
|
*/
|
|
private function _initializeSchema(): void { // phpcs:ignore
|
|
$this->db->exec(
|
|
'CREATE TABLE IF NOT EXISTS settings (
|
|
setting_key TEXT PRIMARY KEY,
|
|
setting_value TEXT NOT NULL,
|
|
updated_at INTEGER NOT NULL
|
|
)'
|
|
);
|
|
|
|
$this->db->exec(
|
|
'CREATE TABLE IF NOT EXISTS api_cache (
|
|
cache_key TEXT PRIMARY KEY,
|
|
response_json TEXT NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
expires_at INTEGER NOT NULL
|
|
)'
|
|
);
|
|
|
|
$this->db->exec(
|
|
'CREATE INDEX IF NOT EXISTS idx_api_cache_expires
|
|
ON api_cache (expires_at)'
|
|
);
|
|
|
|
$this->db->exec(
|
|
'CREATE TABLE IF NOT EXISTS market_scan_waypoints (
|
|
system_symbol TEXT NOT NULL,
|
|
waypoint_symbol TEXT NOT NULL,
|
|
exports_json TEXT NOT NULL,
|
|
probe_ship_symbol TEXT NOT NULL,
|
|
error_message TEXT NOT NULL,
|
|
updated_at INTEGER NOT NULL,
|
|
PRIMARY KEY (system_symbol, waypoint_symbol)
|
|
)'
|
|
);
|
|
|
|
$this->db->exec(
|
|
'CREATE INDEX IF NOT EXISTS idx_market_scan_system_updated
|
|
ON market_scan_waypoints (system_symbol, updated_at)'
|
|
);
|
|
}
|
|
|
|
/**
|
|
* Save a generic setting.
|
|
*
|
|
* @param string $key Setting key.
|
|
* @param string $value Setting value.
|
|
*
|
|
* @return void
|
|
*/
|
|
public function setSetting( string $key, string $value ): void {
|
|
$stmt = $this->db->prepare(
|
|
'INSERT INTO settings (setting_key, setting_value, updated_at)
|
|
VALUES (:key, :value, :updated_at)
|
|
ON CONFLICT(setting_key) DO UPDATE SET
|
|
setting_value = excluded.setting_value,
|
|
updated_at = excluded.updated_at'
|
|
);
|
|
|
|
$stmt->execute(
|
|
array(
|
|
':key' => $key,
|
|
':value' => $value,
|
|
':updated_at' => time(),
|
|
)
|
|
);
|
|
}
|
|
|
|
/**
|
|
* Load a generic setting.
|
|
*
|
|
* @param string $key Setting key.
|
|
*
|
|
* @return string|null
|
|
*/
|
|
public function getSetting( string $key ): ?string {
|
|
$stmt = $this->db->prepare(
|
|
'SELECT setting_value FROM settings WHERE setting_key = :key LIMIT 1'
|
|
);
|
|
$stmt->execute( array( ':key' => $key ) );
|
|
$row = $stmt->fetch();
|
|
|
|
if (! is_array( $row ) || ! isset( $row['setting_value'] ) ) {
|
|
return null;
|
|
}
|
|
|
|
return (string) $row['setting_value'];
|
|
}
|
|
|
|
/**
|
|
* Save the agent token in settings.
|
|
*
|
|
* @param string $token Agent token.
|
|
*
|
|
* @return void
|
|
*/
|
|
public function setAgentToken( string $token ): void {
|
|
$this->setSetting( 'agent_token', $token );
|
|
}
|
|
|
|
/**
|
|
* Get the stored agent token, if available.
|
|
*
|
|
* @return string|null
|
|
*/
|
|
public function getAgentToken(): ?string {
|
|
return $this->getSetting( 'agent_token' );
|
|
}
|
|
|
|
/**
|
|
* Fetch a valid cached response by cache key.
|
|
*
|
|
* @param string $cacheKey Cache key.
|
|
*
|
|
* @return array<string,mixed>|null
|
|
*/
|
|
public function getCache( string $cacheKey ): ?array {
|
|
$stmt = $this->db->prepare(
|
|
'SELECT response_json, expires_at
|
|
FROM api_cache
|
|
WHERE cache_key = :cache_key
|
|
LIMIT 1'
|
|
);
|
|
$stmt->execute( array( ':cache_key' => $cacheKey ) );
|
|
$row = $stmt->fetch();
|
|
|
|
if (! is_array( $row ) ) {
|
|
return null;
|
|
}
|
|
|
|
if ((int) $row['expires_at'] <= time() ) {
|
|
$this->deleteCache( $cacheKey );
|
|
return null;
|
|
}
|
|
|
|
$decoded = json_decode( (string) $row['response_json'], true );
|
|
if (! is_array( $decoded ) ) {
|
|
$this->deleteCache( $cacheKey );
|
|
return null;
|
|
}
|
|
|
|
return $decoded;
|
|
}
|
|
|
|
/**
|
|
* Store API response in cache.
|
|
*
|
|
* @param string $cacheKey Cache key.
|
|
* @param array<string,mixed> $response API response payload.
|
|
* @param int $ttl Time to live in seconds.
|
|
*
|
|
* @return void
|
|
*/
|
|
public function setCache( string $cacheKey, array $response, int $ttl = 600 ): void {
|
|
$json = json_encode( $response );
|
|
if ($json === false ) {
|
|
return;
|
|
}
|
|
|
|
$createdAt = time();
|
|
$expiresAt = $createdAt + max( 1, $ttl );
|
|
|
|
$stmt = $this->db->prepare(
|
|
'INSERT INTO api_cache (cache_key, response_json, created_at, expires_at)
|
|
VALUES (:cache_key, :response_json, :created_at, :expires_at)
|
|
ON CONFLICT(cache_key) DO UPDATE SET
|
|
response_json = excluded.response_json,
|
|
created_at = excluded.created_at,
|
|
expires_at = excluded.expires_at'
|
|
);
|
|
|
|
$stmt->execute(
|
|
array(
|
|
':cache_key' => $cacheKey,
|
|
':response_json' => $json,
|
|
':created_at' => $createdAt,
|
|
':expires_at' => $expiresAt,
|
|
)
|
|
);
|
|
}
|
|
|
|
/**
|
|
* Remove one cache row.
|
|
*
|
|
* @param string $cacheKey Cache key.
|
|
*
|
|
* @return void
|
|
*/
|
|
public function deleteCache( string $cacheKey ): void {
|
|
$stmt = $this->db->prepare(
|
|
'DELETE FROM api_cache WHERE cache_key = :cache_key'
|
|
);
|
|
$stmt->execute( array( ':cache_key' => $cacheKey ) );
|
|
}
|
|
|
|
/**
|
|
* Remove all expired cache rows.
|
|
*
|
|
* @return void
|
|
*/
|
|
public function purgeExpiredCache(): void {
|
|
$stmt = $this->db->prepare(
|
|
'DELETE FROM api_cache WHERE expires_at <= :now'
|
|
);
|
|
$stmt->execute( array( ':now' => time() ) );
|
|
}
|
|
|
|
/**
|
|
* Remove all cached API rows.
|
|
*
|
|
* @return void
|
|
*/
|
|
public function clearAllCache(): void {
|
|
$this->db->exec( 'DELETE FROM api_cache' );
|
|
}
|
|
|
|
/**
|
|
* Store scanned export data for a marketplace waypoint.
|
|
*
|
|
* @param string $systemSymbol System symbol.
|
|
* @param string $waypointSymbol Waypoint symbol.
|
|
* @param array<int,array<string,mixed>> $exports Scanned export records.
|
|
* @param string $probeShipSymbol Probe ship symbol used for scan.
|
|
* @param string $errorMessage Optional scan error message.
|
|
*
|
|
* @return void
|
|
*/
|
|
public function upsertMarketScanWaypoint(
|
|
string $systemSymbol,
|
|
string $waypointSymbol,
|
|
array $exports,
|
|
string $probeShipSymbol = '',
|
|
string $errorMessage = ''
|
|
): void {
|
|
$exportsJson = json_encode( array_values( $exports ) );
|
|
if ($exportsJson === false ) {
|
|
return;
|
|
}
|
|
|
|
$stmt = $this->db->prepare(
|
|
'INSERT INTO market_scan_waypoints (
|
|
system_symbol,
|
|
waypoint_symbol,
|
|
exports_json,
|
|
probe_ship_symbol,
|
|
error_message,
|
|
updated_at
|
|
)
|
|
VALUES (
|
|
:system_symbol,
|
|
:waypoint_symbol,
|
|
:exports_json,
|
|
:probe_ship_symbol,
|
|
:error_message,
|
|
:updated_at
|
|
)
|
|
ON CONFLICT(system_symbol, waypoint_symbol) DO UPDATE SET
|
|
exports_json = excluded.exports_json,
|
|
probe_ship_symbol = excluded.probe_ship_symbol,
|
|
error_message = excluded.error_message,
|
|
updated_at = excluded.updated_at'
|
|
);
|
|
|
|
$stmt->execute(
|
|
array(
|
|
':system_symbol' => $systemSymbol,
|
|
':waypoint_symbol' => $waypointSymbol,
|
|
':exports_json' => $exportsJson,
|
|
':probe_ship_symbol' => $probeShipSymbol,
|
|
':error_message' => $errorMessage,
|
|
':updated_at' => time(),
|
|
)
|
|
);
|
|
}
|
|
|
|
/**
|
|
* Load scanned market export data for a system.
|
|
*
|
|
* @param string $systemSymbol System symbol.
|
|
*
|
|
* @return array<int,array<string,mixed>>
|
|
*/
|
|
public function getMarketScanWaypointsBySystem( string $systemSymbol ): array {
|
|
$stmt = $this->db->prepare(
|
|
'SELECT
|
|
system_symbol,
|
|
waypoint_symbol,
|
|
exports_json,
|
|
probe_ship_symbol,
|
|
error_message,
|
|
updated_at
|
|
FROM market_scan_waypoints
|
|
WHERE system_symbol = :system_symbol
|
|
ORDER BY waypoint_symbol ASC'
|
|
);
|
|
$stmt->execute( array( ':system_symbol' => $systemSymbol ) );
|
|
$rows = $stmt->fetchAll();
|
|
|
|
if (! is_array( $rows ) ) {
|
|
return array();
|
|
}
|
|
|
|
foreach ( $rows as &$row ) {
|
|
$decodedExports = json_decode( (string) ( $row['exports_json'] ?? '[]' ), true );
|
|
$row['exports'] = is_array( $decodedExports ) ? $decodedExports : array();
|
|
}
|
|
unset( $row );
|
|
|
|
return $rows;
|
|
}
|
|
}
|