SqliteAdapter::query() - Code Metrics - Inspection of "Fix SQLite in SILENT MODE" - Level-2/Maphper - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#53)

unknown

created 2017-12-26 10:51 UTC

SqliteAdapter::query() F

↳ Parent: SqliteAdapter

Complexity

Conditions	12
Paths	324

Size

Total Lines	34
Code Lines	23

Duplication

Lines	9
Ratio	26.47 %

Importance

Changes

Metric	Value
cc	12
eloc	23
nc	324
nop	1
dl	9
loc	34
rs	3.7956
c	0
b	0
f	0

How to fix Complexity

<?php
namespace Maphper\DataSource;

class SqliteAdapter implements DatabaseAdapter {
	private $pdo;
	private $queryCache = [];
	
	public function __construct(\PDO $pdo) {
		$this->pdo = $pdo;	
	}
	
	public function quote($str) {
		return '`' . str_replace('.', '`.`', trim($str, '`')) . '`';
	}
		
	public function query(\Maphper\Lib\Query $query) {
		$queryId = md5($query->getSql());
		if (isset($this->queryCache[$queryId])) $stmt = $this->queryCache[$queryId];

		else {
			$stmt = $this->pdo->prepare($query->getSql(), [\PDO::ATTR_CURSOR => \PDO::CURSOR_FWDONLY]);
			if ($stmt) $this->queryCache[$queryId] = $stmt;
		}
		
		$args = $query->getArgs();
		foreach ($args as &$arg) if ($arg instanceof \DateTime) {

			if ($arg->format('H:i:s')  == '00:00:00') $arg = $arg->format('Y-m-d');
			else $arg = $arg->format('Y-m-d H:i:s');
		}
				
		if ($stmt !== false) {
			try {
				if (count($args) > 0) $res = $stmt->execute($args);

				else $res = $stmt->execute();
				if ($stmt->errorCode() !== '00000') throw new \Exception('Invalid query');
				if (substr($query->getSql(), 0, 6) === 'SELECT') return $stmt->fetchAll(\PDO::FETCH_OBJ);
				else return $stmt;
			}
			catch (\Exception $e) {
				//SQLite causes an error if when the DB schema changes, rebuild $stmt and try again.
				if ($stmt->errorInfo()[2] == 'database schema has changed') {
					unset($this->queryCache[$queryId]);
					return $this->query($query);	
				}
				else return $stmt;				
			}
		}
		//Handle SQLite when PDO_ERRMODE is set to SILENT
		else {
			throw new \Exception('Invalid query');
		}
	}
	
	public function lastInsertId() {
		return $this->pdo->lastInsertId();
	}
	
	private function getType($val) {
		if ($val instanceof \DateTime) return 'DATETIME';
		else if (is_int($val)) return  'INTEGER';

		else if (is_double($val)) return 'DECIMAL(9,' . strlen($val) - strrpos($val, '.') - 1 . ')';
		else if (is_string($val) && strlen($val) < 256) return 'VARCHAR(255)';
		else if (is_string($val) && strlen($val) > 256) return 'LONGBLOG';
		else return 'VARCHAR(255)';		
	}

	private function tableExists($name) {
		$result = $this->pdo->query('SELECT name FROM sqlite_master WHERE type="table" and name="'. $name.'"');
		return count($result->fetchAll()) == 1;
	}

	private function getColumns($table) {
		$result = $this->pdo->query('PRAGMA table_info(' . $table . ');')->fetchAll(\PDO::FETCH_OBJ);
		$return = [];
		foreach ($result as $row) {
			$return[] = $row->name;
		}
		return $return;
	}

	//Alter the database so that it can store $data
	public function alterDatabase($table, array $primaryKey, $data) {
		//Unset query cache, otherwise it causes:
		// SQLSTATE[HY000]: General error: 17 database schema has changed
		$this->queryCache = [];

		$affix = '_'.substr(md5($table), 0, 6);
		$this->createTable($table . $affix, $primaryKey, $data);
		$fields = [];
		foreach ($data as $key => $value) { $fields[] = $key; }
		try {
			if ($this->tableExists($table)) {
				$columns = implode(', ', $this->getColumns($table));			

				$this->pdo->query('INSERT INTO ' . $this->quote($table . $affix) . '(' . $columns . ') SELECT ' . $columns . ' FROM ' . $this->quote($table));
				$this->pdo->query('DROP TABLE IF EXISTS ' . $table );
			}
		}
		catch (\PDOException $e) {
			// No data to copy
			echo $e->getMessage();
		}

		$this->pdo->query('DROP TABLE IF EXISTS ' . $table );
		$this->pdo->query('ALTER TABLE ' . $table . $affix. ' RENAME TO '. $table );

	}

	public function createTable($table, array $primaryKey, $data) {
		$parts = [];
		foreach ($primaryKey as $key) {

			$pk = $data->$key;
			if ($pk == null) $parts[] = $key . ' INTEGER'; 
			else $parts[] = $key . ' ' . $this->getType($pk) . ' NOT NULL';					
		}
		
		$pkField = implode(', ', $parts) . ', PRIMARY KEY(' . implode(', ', $primaryKey) . ')';
				
		$this->pdo->query('DROP TABLE IF EXISTS ' . $table );
		$this->pdo->query('CREATE TABLE ' . $table . ' (' . $pkField . ')');
					
		foreach ($data as $key => $value) {
			if (is_array($value) || (is_object($value) && !($value instanceof \DateTime))) continue;
			if (in_array($key, $primaryKey)) continue;

			$type = $this->getType($value);
		
			$this->pdo->query('ALTER TABLE ' . $table . ' ADD ' . $this->quote($key) . ' ' . $type);
		}
	}
	

	public function addIndex($table, array $fields) {
		if (empty($fields)) return false;
		
		//SQLite doesn't support ASC/DESC indexes, remove the keywords
		foreach ($fields as &$field) $field = str_ireplace([' desc', ' asc'], '', $field);
		sort($fields);
		$fields = array_map('strtolower', $fields);
		$fields = array_map('trim', $fields);
		$keyName = implode('_', $fields);
	
		
		try {
			$this->pdo->query('CREATE INDEX IF NOT EXISTS  ' . $keyName . ' ON ' . $table . ' (' . implode(', ', $fields) . ')');
		}
		catch (\Exception $e) {

			
		}
	}
	
	public function optimiseColumns($table) {
		//TODO
	}
}

1		<?php
2		namespace Maphper\DataSource;
3
4		class SqliteAdapter implements DatabaseAdapter {
5		private $pdo;
6		private $queryCache = [];
7
8		public function __construct(\PDO $pdo) {
9		$this->pdo = $pdo;
10		}
11
12		public function quote($str) {
13		return '`' . str_replace('.', '`.`', trim($str, '`')) . '`';
14		}
15
16		public function query(\Maphper\Lib\Query $query) {
17		$queryId = md5($query->getSql());
18	View Code Duplication	if (isset($this->queryCache[$queryId])) $stmt = $this->queryCache[$queryId];
		0 ignored issues – show Duplication introduced 2017-12-23 16:13 UTC by Report Bug Copy Issue Report This code seems to be duplicated across your project. Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation. You can also find more detailed suggestions in the “Code” section of your repository. Loading history...
19		else {
20		$stmt = $this->pdo->prepare($query->getSql(), [\PDO::ATTR_CURSOR => \PDO::CURSOR_FWDONLY]);
21		if ($stmt) $this->queryCache[$queryId] = $stmt;
22		}
23
24		$args = $query->getArgs();
25	View Code Duplication	foreach ($args as &$arg) if ($arg instanceof \DateTime) {
		0 ignored issues – show Duplication introduced 2017-12-23 16:13 UTC by Report Bug Copy Issue Report This code seems to be duplicated across your project. Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation. You can also find more detailed suggestions in the “Code” section of your repository. Loading history...
26		if ($arg->format('H:i:s') == '00:00:00') $arg = $arg->format('Y-m-d');
27		else $arg = $arg->format('Y-m-d H:i:s');
28		}
29
30		if ($stmt !== false) {
31		try {
32		if (count($args) > 0) $res = $stmt->execute($args);
		0 ignored issues – show Unused Code introduced 2017-12-23 16:13 UTC by Report Bug Copy Issue Report The assignment to `$res` is dead and can be removed. Loading history...
33		else $res = $stmt->execute();
34		if ($stmt->errorCode() !== '00000') throw new \Exception('Invalid query');
35		if (substr($query->getSql(), 0, 6) === 'SELECT') return $stmt->fetchAll(\PDO::FETCH_OBJ);
36		else return $stmt;
37		}
38		catch (\Exception $e) {
39		//SQLite causes an error if when the DB schema changes, rebuild $stmt and try again.
40		if ($stmt->errorInfo()[2] == 'database schema has changed') {
41		unset($this->queryCache[$queryId]);
42		return $this->query($query);
43		}
44		else return $stmt;
45		}
46		}
47		//Handle SQLite when PDO_ERRMODE is set to SILENT
48		else {
49		throw new \Exception('Invalid query');
50		}
51		}
52
53		public function lastInsertId() {
54		return $this->pdo->lastInsertId();
55		}
56
57		private function getType($val) {
58		if ($val instanceof \DateTime) return 'DATETIME';
59	View Code Duplication	else if (is_int($val)) return 'INTEGER';
		0 ignored issues – show Duplication introduced 2017-12-23 16:13 UTC by Report Bug Copy Issue Report This code seems to be duplicated across your project. Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation. You can also find more detailed suggestions in the “Code” section of your repository. Loading history...
60		else if (is_double($val)) return 'DECIMAL(9,' . strlen($val) - strrpos($val, '.') - 1 . ')';
61		else if (is_string($val) && strlen($val) < 256) return 'VARCHAR(255)';
62		else if (is_string($val) && strlen($val) > 256) return 'LONGBLOG';
63		else return 'VARCHAR(255)';
64		}
65
66		private function tableExists($name) {
67		$result = $this->pdo->query('SELECT name FROM sqlite_master WHERE type="table" and name="'. $name.'"');
68		return count($result->fetchAll()) == 1;
69		}
70
71		private function getColumns($table) {
72		$result = $this->pdo->query('PRAGMA table_info(' . $table . ');')->fetchAll(\PDO::FETCH_OBJ);
73		$return = [];
74		foreach ($result as $row) {
75		$return[] = $row->name;
76		}
77		return $return;
78		}
79
80		//Alter the database so that it can store $data
81		public function alterDatabase($table, array $primaryKey, $data) {
82		//Unset query cache, otherwise it causes:
83		// SQLSTATE[HY000]: General error: 17 database schema has changed
84		$this->queryCache = [];
85
86		$affix = '_'.substr(md5($table), 0, 6);
87		$this->createTable($table . $affix, $primaryKey, $data);
88		$fields = [];
89		foreach ($data as $key => $value) { $fields[] = $key; }
90		try {
91		if ($this->tableExists($table)) {
92		$columns = implode(', ', $this->getColumns($table));
93
94		$this->pdo->query('INSERT INTO ' . $this->quote($table . $affix) . '(' . $columns . ') SELECT ' . $columns . ' FROM ' . $this->quote($table));
95		$this->pdo->query('DROP TABLE IF EXISTS ' . $table );
96		}
97		}
98		catch (\PDOException $e) {
99		// No data to copy
100		echo $e->getMessage();
101		}
102
103		$this->pdo->query('DROP TABLE IF EXISTS ' . $table );
104		$this->pdo->query('ALTER TABLE ' . $table . $affix. ' RENAME TO '. $table );
105
106		}
107
108		public function createTable($table, array $primaryKey, $data) {
109		$parts = [];
110	View Code Duplication	foreach ($primaryKey as $key) {
		0 ignored issues – show Duplication introduced 2017-12-23 16:13 UTC by Report Bug Copy Issue Report This code seems to be duplicated across your project. Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation. You can also find more detailed suggestions in the “Code” section of your repository. Loading history...
111		$pk = $data->$key;
112		if ($pk == null) $parts[] = $key . ' INTEGER';
113		else $parts[] = $key . ' ' . $this->getType($pk) . ' NOT NULL';
114		}
115
116		$pkField = implode(', ', $parts) . ', PRIMARY KEY(' . implode(', ', $primaryKey) . ')';
117
118		$this->pdo->query('DROP TABLE IF EXISTS ' . $table );
119		$this->pdo->query('CREATE TABLE ' . $table . ' (' . $pkField . ')');
120
121		foreach ($data as $key => $value) {
122		if (is_array($value) \|\| (is_object($value) && !($value instanceof \DateTime))) continue;
123		if (in_array($key, $primaryKey)) continue;
124
125		$type = $this->getType($value);
126
127		$this->pdo->query('ALTER TABLE ' . $table . ' ADD ' . $this->quote($key) . ' ' . $type);
128		}
129		}
130
131
132		public function addIndex($table, array $fields) {
133		if (empty($fields)) return false;
134
135		//SQLite doesn't support ASC/DESC indexes, remove the keywords
136		foreach ($fields as &$field) $field = str_ireplace([' desc', ' asc'], '', $field);
137		sort($fields);
138		$fields = array_map('strtolower', $fields);
139		$fields = array_map('trim', $fields);
140		$keyName = implode('_', $fields);
141
142
143		try {
144		$this->pdo->query('CREATE INDEX IF NOT EXISTS ' . $keyName . ' ON ' . $table . ' (' . implode(', ', $fields) . ')');
145		}
146		catch (\Exception $e) {
		0 ignored issues – show Coding Style Comprehensibility introduced 2017-12-23 16:13 UTC by Report Bug Copy Issue Report Consider adding a comment why this CATCH block is empty. Loading history...
147
148		}
149		}
150
151		public function optimiseColumns($table) {
152		//TODO
153		}
154		}

Level-2 / Maphper

Pull Request — master (#53)

SqliteAdapter::query() F

Complexity

Size

Duplication

Importance

How to fix Complexity

Long Method

Duplication Side-by-Side

Filter issues like