From 7b702660ef52338b3c14c1310f53d43cd346ccac Mon Sep 17 00:00:00 2001
From: Jacek Kowalski <Jacek@jacekk.info>
Date: Fri, 21 Jun 2019 11:27:37 +0000
Subject: [PATCH] Add DB caching and make mapping independent on numToType

---
 lib/database.php |   76 ++++++++++++++++++++++++++++++++------
 1 files changed, 64 insertions(+), 12 deletions(-)

diff --git a/lib/database.php b/lib/database.php
index 48ad047..22cc51c 100644
--- a/lib/database.php
+++ b/lib/database.php
@@ -1,9 +1,10 @@
 <?php
 class Database {
 	private $pdo;
-	private $getByIdStatement;
-	private $getByNumStatement;
 	private $addStatement;
+	
+	private $cacheId;
+	private $cacheNum;
 	
 	public function __construct($file) {
 		$this->pdo = new PDO('sqlite:'.$file);
@@ -15,33 +16,84 @@
 			weight INT
 		)');
 		
-		$this->getByIdStatement = $this->pdo->prepare('SELECT num, weight FROM vehicles WHERE id=? LIMIT 1');
-		$this->getByNumStatement = $this->pdo->prepare('SELECT id, weight FROM vehicles WHERE num=? LIMIT 1');
-		$this->addStatement = $this->pdo->prepare('INSERT OR REPLACE INTO vehicles (id, num, weight) VALUES (?, ?, ?)');
+		$this->addStatement = $this->pdo->prepare('INSERT OR REPLACE INTO vehicles (id, num, weight) VALUES (:id, :num, :weight)');
+		
+		$this->_cacheClear();
+	}
+	
+	public function beginTransaction() {
+		$this->pdo->beginTransaction();
+	}
+	
+	public function commit() {
+		$this->pdo->commit();
+	}
+	
+	public function rollback() {
+		$this->pdo->rollback();
+	}
+	
+	protected function _cachePopulate() {
+		if($this->cacheId === NULL) {
+			$st = $this->pdo->prepare('SELECT * FROM vehicles');
+			$st->execute();
+			$result = $st->fetchAll(PDO::FETCH_ASSOC);
+			
+			$this->cacheId = [];
+			$this->cacheNum = [];
+			foreach($result as $vehicle) {
+				$this->_cacheAdd($vehicle);
+			}
+		}
+	}
+	
+	protected function _cacheAdd($vehicle) {
+		$this->_cachePopulate();
+		$this->cacheId[$vehicle['id']] = $vehicle;
+		$this->cacheNum[$vehicle['num']] = $vehicle;
+	}
+	
+	protected function _cacheClear() {
+		$this->cacheId = NULL;
+		$this->cacheNum = NULL;
+	}
+	
+	public function getAll() {
+		$this->_cachePopulate();
+		return $this->cacheId;
 	}
 	
 	public function getById($id) {
-		$this->getByIdStatement->execute([$id]);
-		return $this->getByIdStatement->fetch();
+		$this->_cachePopulate();
+		return $this->cacheId[$id] ?? NULL;
 	}
 	
 	public function getByNum($num) {
-		$st = $this->getByNumStatement->execute([(int)substr($num, 2)]);
-		return $this->getByNumStatement->fetch();
+		$this->_cachePopulate();
+		return $this->cacheNum[$num] ?? NULL;
 	}
 	
 	public function clear() {
 		$this->pdo->query('DELETE FROM vehicles');
+		$this->_cacheClear();
 	}
 	
 	public function add($id, $num, $weight) {
-		$this->addStatement->execute([$id, $num, $weight]);
+		$vehicle = [
+			'id' => (string)$id,
+			'num' => (string)$num,
+			'weight' => (string)$weight
+		];
+		$this->addStatement->execute($vehicle);
+		$this->_cacheAdd($vehicle);
 	}
 	
 	public function addMapping($mapping) {
+		$this->beginTransaction();
 		$weight = count($mapping);
-		foreach($mapping as $id => $vehicle) {
-			$this->add($id, (int)substr($vehicle['num'], 2), $weight);
+		foreach($mapping as $id => $num) {
+			$this->add($id, $num, $weight);
 		}
+		$this->commit();
 	}
 }

--
Gitblit v1.9.1