1	<?php
2
3	namespace BO\Zmsdb\Connection;
4
5	use BO\Slim\Helper\Sanitizer;
6
7	/**
8	*
9	* @codeCoverageIgnore
10	*
11	* @SuppressWarnings(TooManyFields)
12	* Handle read and write connections
13	*/
14	class Select
15	{
16	/**
17	* @var Bool $enableProfiling
18	*/
19	public static $enableProfiling = false;
20
21	/**
22	* @var String $readSourceName PDO connection string
23	*/
24	public static $readSourceName = null;
25
26	/**
27	* @var String $writeSourceName PDO connection string
28	*/
29	public static $writeSourceName = null;
30
31	/**
32	* @var String $dbname_zms
33	*/
34	public static $dbname_zms = 'zmsbo';
35
36	/**
37	* @var String $username Login
38	*/
39	public static $username = null;
40
41	/**
42	* @var String $password Credential
43	*/
44	public static $password = null;
45
46	/**
47	* @var Array $pdoOptions compatible to the 4th PDO::__construct parameter
48	*/
49	public static $pdoOptions = [];
50
51	/**
52	* @var String $connectionTimezone
53	*
54	*/
55	public static $connectionTimezone = ' UTC';
56
57	/**
58	* @var Bool $enableWsrepSyncWait
59	*/
60	public static $enableWsrepSyncWait = false;
61
62	/**
63	* @var Bool $enableWsrepSyncWait
64	*/
65	public static $galeraConnection = false;
66
67	/**
68	* @var PdoInterface $readConnection for read only requests
69	*/
70	protected static $readConnection = null;
71
72	/**
73	* @var PdoInterface $writeConnection for write only requests
74	*/
75	protected static $writeConnection = null;
76
77	/**
78	* @var \Aura\Sql\Profiler $readProfiler for read only requests
79	*/
80	protected static $readProfiler = null;
81
82	/**
83	* @var \Aura\Sql\Profiler $writeProfiler for write only requests
84	*/
85	protected static $writeProfiler = null;
86
87	/**
88	* @var Bool $useTransaction
89	*
90	*/
91	protected static $useTransaction = false;
92
93	/**
94	* @var Bool $useProfiling
95	*
96	*/
97	protected static $useProfiling = false;
98
99	/**
100	* @var Bool $useQueryCache
101	*
102	*/
103	protected static $useQueryCache = true;
104
105	protected static function sanitizeStackTrace($trace)
106	{
107	return Sanitizer::sanitizeStackTrace($trace);
108	}
109
110	/**
111	* Create a PDO compatible object
112	*
113	* @param  String $dataSourceName compatible with PDO
114	* @return PdoInterface
115	*/
116	protected static function createPdoConnection($dataSourceName)
117	{
118	try {
119	$pdoOptions = array_merge([
120	], self::$pdoOptions);
121	$pdo = new Pdo($dataSourceName, self::$username, self::$password, $pdoOptions);
122	$pdo->exec('SET NAMES "UTF8";');
123	//$timezone = date_default_timezone_get();
124	//$pdo->prepare('SET time_zone = ?;')->execute([$timezone]);
125	$pdo->exec('SET SESSION sql_mode = "STRICT_ALL_TABLES";');
126	$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
127	} catch (\Exception $exception) {
128	$sanitizedDsn     = self::sanitizeStackTrace($dataSourceName);
129	$sanitizedMessage = self::sanitizeStackTrace($exception->getMessage());
130	throw new \BO\Zmsdb\Exception\Pdo\PDOFailed(
131	$sanitizedDsn . ': ' . $sanitizedMessage,
132	);
133	}
134	return $pdo;
135	}
136
137	/**
138	* Set the read connection.
139	* Usually this function is only required to set mockups for testing
140	*
141	* @param PdoInterface $connection
142	*/
143	public static function setReadConnection(PdoInterface $connection)
144	{
145	self::$readConnection = $connection;
146	}
147
148	/**
149	* Create or return a connection for reading data
150	*
151	* @return PdoInterface
152	*/
153	public static function getReadConnection()
154	{
155	if (null === self::$readConnection) {
156	self::$readConnection = self::createPdoConnection(self::$readSourceName);
157	self::$readProfiler = new \Aura\Sql\Profiler\Profiler();
158	self::$readProfiler->setActive(self::$enableProfiling);
159	self::$readConnection->setProfiler(self::$readProfiler);
160	//self::$readConnection->exec('SET SESSION TRANSACTION READ ONLY');
161	if (!self::$useQueryCache) {
162	try {
163	self::$readConnection->exec('SET SESSION query_cache_type = 0;');
164	} catch (\Exception $exception) {
165	// ignore, query cache might be disabled
166	}
167	}
168	if (self::$useProfiling) {
169	self::$readConnection->exec('SET profiling = 1;');
170	}
171	}
172	return self::$readConnection;
173	}
174
175	/**
176	* Test if a read connection is established
177	*
178	*/
179	public static function hasReadConnection()
180	{
181	return (null === self::$readConnection) ? false : true;
182	}
183
184	/**
185	* Close a connection for reading data
186	*
187	*/
188	public static function closeReadConnection()
189	{
190	self::$readConnection = null;
191	}
192
193	/**
194	* Set the write connection.
195	* Usually this function is only required to set mockups for testing
196	*
197	* @param  PdoInterface $connection
198	* @return self
199	*/
200	public static function setWriteConnection(PdoInterface $connection)
201	{
202	self::$writeConnection = $connection;
203	}
204
205	/**
206	* Create or return a connection for writing data
207	*
208	* @return PdoInterface
209	*/
210	public static function getWriteConnection()
211	{
212	if (null === self::$writeConnection) {
213	self::$writeConnection = self::createPdoConnection(self::$writeSourceName);
214	self::$writeProfiler = new \Aura\Sql\Profiler\Profiler();
215	self::$writeProfiler->setActive(self::$enableProfiling);
216	self::$writeConnection->setProfiler(self::$writeProfiler);
217	if (self::$useTransaction) {
218	self::$writeConnection->exec('SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED');
219	self::$writeConnection->exec('SET SESSION innodb_lock_wait_timeout=15');
220	self::$writeConnection->beginTransaction();
221	}
222	if (!self::$useQueryCache) {
223	try {
224	self::$writeConnection->exec('SET SESSION query_cache_type = 0;');
225	} catch (\Exception $exception) {
226	// ignore, query cache might be disabled
227	}
228	}
229	if (self::$useProfiling) {
230	self::$writeConnection->exec('SET profiling = 1;');
231	}
232	if (self::$galeraConnection && self::$enableWsrepSyncWait) {
233	self::$writeConnection->exec(
234	'SET SESSION wsrep_sync_wait = (
235	SELECT CAST(value AS INT) FROM config WHERE name = "setting__wsrepsync"
236	);'
237	);
238	}
239	// On writing, use the same host to avoid racing/transaction conditions
240	self::$readConnection = self::$writeConnection;
241	}
242	return self::$writeConnection;
243	}
244
245	/**
246	* Test if a write connection is established
247	*
248	*/
249	public static function hasWriteConnection()
250	{
251	return (null === self::$writeConnection) ? false : true;
252	}
253
254	/**
255	* Close a connection for writing data
256	*
257	*/
258	public static function closeWriteConnection()
259	{
260	self::$writeConnection = null;
261	}
262
263	/**
264	* Set query cache
265	*
266	* @param Bool $useQueryCache
267	*
268	*/
269	public static function setQueryCache($useQueryCache = true)
270	{
271	static::$useQueryCache = $useQueryCache;
272	}
273
274	/**
275	* Set profiling
276	*
277	* @param Bool $useProfiling
278	*
279	*/
280	public static function setProfiling($useProfiling = true)
281	{
282	static::$useProfiling = $useProfiling;
283	}
284
285	/**
286	* Set cluster wide causality checks, needed for critical reads across different nodes
287	* @param Bool $wsrepStatus Set to true for critical reads
288	*/
289	public static function setCriticalReadSession($wsrepStatus = true)
290	{
291	static::$enableWsrepSyncWait = $wsrepStatus;
292	static::getWriteConnection();
293	}
294
295	/**
296	* Set transaction
297	*
298	* @param Bool $useTransaction
299	*
300	*/
301	public static function setTransaction($useTransaction = true)
302	{
303	static::$useTransaction = $useTransaction;
304	}
305
306	/**
307	* Rollback transaction if started
308	*
309	*/
310	public static function writeRollback()
311	{
312	if (self::$useTransaction && self::getWriteConnection()->inTransaction()) {
313	return self::getWriteConnection()->rollBack();
314	}
315	return null;
316	}
317
318	/**
319	* Commit transaction if started
320	*
321	*/
322	public static function writeCommit()
323	{
324	if (self::$useTransaction && null !== self::$writeConnection && self::getWriteConnection()->inTransaction()) {
325	$status = self::getWriteConnection()->commit();
326	self::$writeConnection->beginTransaction();
327	return $status;
328	}
329	return null;
330	}
331
332	public static function writeCommitWithStartLock()
333	{
334	return self::writeCommit() && (new \BO\Zmsdb\Config())->readProperty('status__calculateSlotsLastRun', true);
335	}
336	}