sqlite-database-integration / wp-includes / sqlite / class-wp-sqlite-pdo-user-defined-functions.php

class-wp-sqlite-pdo-user-defined-functions.php in SQLite Database Integration 2.1.13, at wp-includes/sqlite/class-wp-sqlite-pdo-user-defined-functions.php

763 lines 19.5 KB

Raw Download Zip

1	<?php
2	/**
3	* Custom functions for the SQLite implementation.
4	*
5	* @package wp-sqlite-integration
6	* @since 1.0.0
7	*/
8
9	/**
10	* This class defines user defined functions(UDFs) for PDO library.
11	*
12	* These functions replace those used in the SQL statement with the PHP functions.
13	*
14	* Usage:
15	*
16	* <code>
17	* new WP_SQLite_PDO_User_Defined_Functions(ref_to_pdo_obj);
18	* </code>
19	*
20	* This automatically enables ref_to_pdo_obj to replace the function in the SQL statement
21	* to the ones defined here.
22	*/
23	class WP_SQLite_PDO_User_Defined_Functions {
24
25	/**
26	* The class constructor
27	*
28	* Initializes the use defined functions to PDO object with PDO::sqliteCreateFunction().
29	*
30	* @param PDO $pdo The PDO object.
31	*/
32	public function __construct( $pdo ) {
33	if ( ! $pdo ) {
34	wp_die( 'Database is not initialized.', 'Database Error' );
35	}
36	foreach ( $this->functions as $f => $t ) {
37	$pdo->sqliteCreateFunction( $f, array( $this, $t ) );
38	}
39	}
40
41	/**
42	* Array to define MySQL function => function defined with PHP.
43	*
44	* Replaced functions must be public.
45	*
46	* @var array
47	*/
48	private $functions = array(
49	'month' => 'month',
50	'monthnum' => 'month',
51	'year' => 'year',
52	'day' => 'day',
53	'hour' => 'hour',
54	'minute' => 'minute',
55	'second' => 'second',
56	'week' => 'week',
57	'weekday' => 'weekday',
58	'dayofweek' => 'dayofweek',
59	'dayofmonth' => 'dayofmonth',
60	'unix_timestamp' => 'unix_timestamp',
61	'now' => 'now',
62	'md5' => 'md5',
63	'curdate' => 'curdate',
64	'rand' => 'rand',
65	'from_unixtime' => 'from_unixtime',
66	'localtime' => 'now',
67	'localtimestamp' => 'now',
68	'isnull' => 'isnull',
69	'if' => '_if',
70	'regexp' => 'regexp',
71	'field' => 'field',
72	'log' => 'log',
73	'least' => 'least',
74	'greatest' => 'greatest',
75	'get_lock' => 'get_lock',
76	'release_lock' => 'release_lock',
77	'ucase' => 'ucase',
78	'lcase' => 'lcase',
79	'unhex' => 'unhex',
80	'inet_ntoa' => 'inet_ntoa',
81	'inet_aton' => 'inet_aton',
82	'datediff' => 'datediff',
83	'locate' => 'locate',
84	'utc_date' => 'utc_date',
85	'utc_time' => 'utc_time',
86	'utc_timestamp' => 'utc_timestamp',
87	'version' => 'version',
88	);
89
90	/**
91	* Method to return the unix timestamp.
92	*
93	* Used without an argument, it returns PHP time() function (total seconds passed
94	* from '1970-01-01 00:00:00' GMT). Used with the argument, it changes the value
95	* to the timestamp.
96	*
97	* @param string $field Representing the date formatted as '0000-00-00 00:00:00'.
98	*
99	* @return number of unsigned integer
100	*/
101	public function unix_timestamp( $field = null ) {
102	return is_null( $field ) ? time() : strtotime( $field );
103	}
104
105	/**
106	* Method to emulate MySQL FROM_UNIXTIME() function.
107	*
108	* @param int $field The unix timestamp.
109	* @param string $format Indicate the way of formatting(optional).
110	*
111	* @return string
112	*/
113	public function from_unixtime( $field, $format = null ) {
114	// Convert to ISO time.
115	$date = gmdate( 'Y-m-d H:i:s', $field );
116
117	return is_null( $format ) ? $date : $this->dateformat( $date, $format );
118	}
119
120	/**
121	* Method to emulate MySQL NOW() function.
122	*
123	* @return string representing current time formatted as '0000-00-00 00:00:00'.
124	*/
125	public function now() {
126	return gmdate( 'Y-m-d H:i:s' );
127	}
128
129	/**
130	* Method to emulate MySQL CURDATE() function.
131	*
132	* @return string representing current time formatted as '0000-00-00'.
133	*/
134	public function curdate() {
135	return gmdate( 'Y-m-d' );
136	}
137
138	/**
139	* Method to emulate MySQL MD5() function.
140	*
141	* @param string $field The string to be hashed.
142	*
143	* @return string of the md5 hash value of the argument.
144	*/
145	public function md5( $field ) {
146	return md5( $field );
147	}
148
149	/**
150	* Method to emulate MySQL RAND() function.
151	*
152	* SQLite does have a random generator, but it is called RANDOM() and returns random
153	* number between -9223372036854775808 and +9223372036854775807. So we substitute it
154	* with PHP random generator.
155	*
156	* This function uses mt_rand() which is four times faster than rand() and returns
157	* the random number between 0 and 1.
158	*
159	* @return int
160	*/
161	public function rand() {
162	return mt_rand( 0, 1 );
163	}
164
165	/**
166	* Method to emulate MySQL DATEFORMAT() function.
167	*
168	* @param string $date Formatted as '0000-00-00' or datetime as '0000-00-00 00:00:00'.
169	* @param string $format The string format.
170	*
171	* @return string formatted according to $format
172	*/
173	public function dateformat( $date, $format ) {
174	$mysql_php_date_formats = array(
175	'%a' => 'D',
176	'%b' => 'M',
177	'%c' => 'n',
178	'%D' => 'jS',
179	'%d' => 'd',
180	'%e' => 'j',
181	'%H' => 'H',
182	'%h' => 'h',
183	'%I' => 'h',
184	'%i' => 'i',
185	'%j' => 'z',
186	'%k' => 'G',
187	'%l' => 'g',
188	'%M' => 'F',
189	'%m' => 'm',
190	'%p' => 'A',
191	'%r' => 'h:i:s A',
192	'%S' => 's',
193	'%s' => 's',
194	'%T' => 'H:i:s',
195	'%U' => 'W',
196	'%u' => 'W',
197	'%V' => 'W',
198	'%v' => 'W',
199	'%W' => 'l',
200	'%w' => 'w',
201	'%X' => 'Y',
202	'%x' => 'o',
203	'%Y' => 'Y',
204	'%y' => 'y',
205	);
206
207	$time = strtotime( $date );
208	$format = strtr( $format, $mysql_php_date_formats );
209
210	return gmdate( $format, $time );
211	}
212
213	/**
214	* Method to extract the month value from the date.
215	*
216	* @param string $field Representing the date formatted as 0000-00-00.
217	*
218	* @return string Representing the number of the month between 1 and 12.
219	*/
220	public function month( $field ) {
221	/*
222	* From https://www.php.net/manual/en/datetime.format.php:
223	*
224	* n - Numeric representation of a month, without leading zeros.
225	* 1 through 12
226	*/
227	return intval( gmdate( 'n', strtotime( $field ) ) );
228	}
229
230	/**
231	* Method to extract the year value from the date.
232	*
233	* @param string $field Representing the date formatted as 0000-00-00.
234	*
235	* @return string Representing the number of the year.
236	*/
237	public function year( $field ) {
238	/*
239	* From https://www.php.net/manual/en/datetime.format.php:
240	*
241	* Y - A full numeric representation of a year, 4 digits.
242	*/
243	return intval( gmdate( 'Y', strtotime( $field ) ) );
244	}
245
246	/**
247	* Method to extract the day value from the date.
248	*
249	* @param string $field Representing the date formatted as 0000-00-00.
250	*
251	* @return string Representing the number of the day of the month from 1 and 31.
252	*/
253	public function day( $field ) {
254	/*
255	* From https://www.php.net/manual/en/datetime.format.php:
256	*
257	* j - Day of the month without leading zeros.
258	* 1 to 31.
259	*/
260	return intval( gmdate( 'j', strtotime( $field ) ) );
261	}
262
263	/**
264	* Method to emulate MySQL SECOND() function.
265	*
266	* @see https://www.php.net/manual/en/datetime.format.php
267	*
268	* @param string $field Representing the time formatted as '00:00:00'.
269	*
270	* @return number Unsigned integer
271	*/
272	public function second( $field ) {
273	/*
274	* From https://www.php.net/manual/en/datetime.format.php:
275	*
276	* s - Seconds, with leading zeros (00 to 59)
277	*/
278	return intval( gmdate( 's', strtotime( $field ) ) );
279	}
280
281	/**
282	* Method to emulate MySQL MINUTE() function.
283	*
284	* @param string $field Representing the time formatted as '00:00:00'.
285	*
286	* @return int
287	*/
288	public function minute( $field ) {
289	/*
290	* From https://www.php.net/manual/en/datetime.format.php:
291	*
292	* i - Minutes with leading zeros.
293	* 00 to 59.
294	*/
295	return intval( gmdate( 'i', strtotime( $field ) ) );
296	}
297
298	/**
299	* Method to emulate MySQL HOUR() function.
300	*
301	* Returns the hour for time, in 24-hour format, from 0 to 23.
302	* Importantly, midnight is 0, not 24.
303	*
304	* @param string $time Representing the time formatted, like '14:08:12'.
305	*
306	* @return int
307	*/
308	public function hour( $time ) {
309	/*
310	* From https://www.php.net/manual/en/datetime.format.php:
311	*
312	* H 24-hour format of an hour with leading zeros.
313	* 00 through 23.
314	*/
315	return intval( gmdate( 'H', strtotime( $time ) ) );
316	}
317
318	/**
319	* Covers MySQL WEEK() function.
320	*
321	* Always assumes $mode = 1.
322	*
323	* @TODO: Support other modes.
324	*
325	* From https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_week:
326	*
327	* > Returns the week number for date. The two-argument form of WEEK()
328	* > enables you to specify whether the week starts on Sunday or Monday
329	* > and whether the return value should be in the range from 0 to 53
330	* > or from 1 to 53. If the mode argument is omitted, the value of the
331	* > default_week_format system variable is used.
332	* >
333	* > The following table describes how the mode argument works:
334	* >
335	* > Mode First day of week Range Week 1 is the first week …
336	* > 0 Sunday 0-53 with a Sunday in this year
337	* > 1 Monday 0-53 with 4 or more days this year
338	* > 2 Sunday 1-53 with a Sunday in this year
339	* > 3 Monday 1-53 with 4 or more days this year
340	* > 4 Sunday 0-53 with 4 or more days this year
341	* > 5 Monday 0-53 with a Monday in this year
342	* > 6 Sunday 1-53 with 4 or more days this year
343	* > 7 Monday 1-53 with a Monday in this year
344	*
345	* @param string $field Representing the date.
346	* @param int $mode The mode argument.
347	*/
348	public function week( $field, $mode ) {
349	/*
350	* From https://www.php.net/manual/en/datetime.format.php:
351	*
352	* W - ISO-8601 week number of year, weeks starting on Monday.
353	* Example: 42 (the 42nd week in the year)
354	*
355	* Week 1 is the first week with a Thursday in it.
356	*/
357	return intval( gmdate( 'W', strtotime( $field ) ) );
358	}
359
360	/**
361	* Simulates WEEKDAY() function in MySQL.
362	*
363	* Returns the day of the week as an integer.
364	* The days of the week are numbered 0 to 6:
365	* * 0 for Monday
366	* * 1 for Tuesday
367	* * 2 for Wednesday
368	* * 3 for Thursday
369	* * 4 for Friday
370	* * 5 for Saturday
371	* * 6 for Sunday
372	*
373	* @param string $field Representing the date.
374	*
375	* @return int
376	*/
377	public function weekday( $field ) {
378	/*
379	* date('N') returns 1 (for Monday) through 7 (for Sunday)
380	* That's one more than MySQL.
381	* Let's subtract one to make it compatible.
382	*/
383	return intval( gmdate( 'N', strtotime( $field ) ) ) - 1;
384	}
385
386	/**
387	* Method to emulate MySQL DAYOFMONTH() function.
388	*
389	* @see https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_dayofmonth
390	*
391	* @param string $field Representing the date.
392	*
393	* @return int Returns the day of the month for date as a number in the range 1 to 31.
394	*/
395	public function dayofmonth( $field ) {
396	return intval( gmdate( 'j', strtotime( $field ) ) );
397	}
398
399	/**
400	* Method to emulate MySQL DAYOFWEEK() function.
401	*
402	* > Returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday).
403	* > These index values correspond to the ODBC standard. Returns NULL if date is NULL.
404	*
405	* @param string $field Representing the date.
406	*
407	* @return int Returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday).
408	*/
409	public function dayofweek( $field ) {
410	/**
411	* From https://www.php.net/manual/en/datetime.format.php:
412	*
413	* `w` – Numeric representation of the day of the week
414	* 0 (for Sunday) through 6 (for Saturday)
415	*/
416	return intval( gmdate( 'w', strtotime( $field ) ) ) + 1;
417	}
418
419	/**
420	* Method to emulate MySQL DATE() function.
421	*
422	* @see https://www.php.net/manual/en/datetime.format.php
423	*
424	* @param string $date formatted as unix time.
425	*
426	* @return string formatted as '0000-00-00'.
427	*/
428	public function date( $date ) {
429	return gmdate( 'Y-m-d', strtotime( $date ) );
430	}
431
432	/**
433	* Method to emulate MySQL ISNULL() function.
434	*
435	* This function returns true if the argument is null, and true if not.
436	*
437	* @param mixed $field The field to be tested.
438	*
439	* @return boolean
440	*/
441	public function isnull( $field ) {
442	return is_null( $field );
443	}
444
445	/**
446	* Method to emulate MySQL IF() function.
447	*
448	* As 'IF' is a reserved word for PHP, function name must be changed.
449	*
450	* @param mixed $expression The statement to be evaluated as true or false.
451	* @param mixed $truthy Statement or value returned if $expression is true.
452	* @param mixed $falsy Statement or value returned if $expression is false.
453	*
454	* @return mixed
455	*/
456	public function _if( $expression, $truthy, $falsy ) {
457	return ( true === $expression ) ? $truthy : $falsy;
458	}
459
460	/**
461	* Method to emulate MySQL REGEXP() function.
462	*
463	* @param string $pattern Regular expression to match.
464	* @param string $field Haystack.
465	*
466	* @return integer 1 if matched, 0 if not matched.
467	*/
468	public function regexp( $pattern, $field ) {
469	/*
470	* If the original query says REGEXP BINARY
471	* the comparison is byte-by-byte and letter casing now
472	* matters since lower- and upper-case letters have different
473	* byte codes.
474	*
475	* The REGEXP function can't be easily made to accept two
476	* parameters, so we'll have to use a hack to get around this.
477	*
478	* If the first character of the pattern is a null byte, we'll
479	* remove it and make the comparison case-sensitive. This should
480	* be reasonably safe since PHP does not allow null bytes in
481	* regular expressions anyway.
482	*/
483	if ( "\x00" === $pattern[0] ) {
484	$pattern = substr( $pattern, 1 );
485	$flags = '';
486	} else {
487	// Otherwise, the search is case-insensitive.
488	$flags = 'i';
489	}
490	$pattern = str_replace( '/', '\/', $pattern );
491	$pattern = '/' . $pattern . '/' . $flags;
492
493	return preg_match( $pattern, $field );
494	}
495
496	/**
497	* Method to emulate MySQL FIELD() function.
498	*
499	* This function gets the list argument and compares the first item to all the others.
500	* If the same value is found, it returns the position of that value. If not, it
501	* returns 0.
502	*
503	* @return int
504	*/
505	public function field() {
506	$num_args = func_num_args();
507	if ( $num_args < 2 \|\| is_null( func_get_arg( 0 ) ) ) {
508	return 0;
509	}
510	$arg_list = func_get_args();
511	$search_string = strtolower( array_shift( $arg_list ) );
512
513	for ( $i = 0; $i < $num_args - 1; $i++ ) {
514	if ( strtolower( $arg_list[ $i ] ) === $search_string ) {
515	return $i + 1;
516	}
517	}
518
519	return 0;
520	}
521
522	/**
523	* Method to emulate MySQL LOG() function.
524	*
525	* Used with one argument, it returns the natural logarithm of X.
526	* <code>
527	* LOG(X)
528	* </code>
529	* Used with two arguments, it returns the natural logarithm of X base B.
530	* <code>
531	* LOG(B, X)
532	* </code>
533	* In this case, it returns the value of log(X) / log(B).
534	*
535	* Used without an argument, it returns false. This returned value will be
536	* rewritten to 0, because SQLite doesn't understand true/false value.
537	*
538	* @return double\|null
539	*/
540	public function log() {
541	$num_args = func_num_args();
542	if ( 1 === $num_args ) {
543	$arg1 = func_get_arg( 0 );
544
545	return log( $arg1 );
546	}
547	if ( 2 === $num_args ) {
548	$arg1 = func_get_arg( 0 );
549	$arg2 = func_get_arg( 1 );
550
551	return log( $arg1 ) / log( $arg2 );
552	}
553	return null;
554	}
555
556	/**
557	* Method to emulate MySQL LEAST() function.
558	*
559	* This function rewrites the function name to SQLite compatible function name.
560	*
561	* @return mixed
562	*/
563	public function least() {
564	$arg_list = func_get_args();
565
566	return min( $arg_list );
567	}
568
569	/**
570	* Method to emulate MySQL GREATEST() function.
571	*
572	* This function rewrites the function name to SQLite compatible function name.
573	*
574	* @return mixed
575	*/
576	public function greatest() {
577	$arg_list = func_get_args();
578
579	return max( $arg_list );
580	}
581
582	/**
583	* Method to dummy out MySQL GET_LOCK() function.
584	*
585	* This function is meaningless in SQLite, so we do nothing.
586	*
587	* @param string $name Not used.
588	* @param integer $timeout Not used.
589	*
590	* @return string
591	*/
592	public function get_lock( $name, $timeout ) {
593	return '1=1';
594	}
595
596	/**
597	* Method to dummy out MySQL RELEASE_LOCK() function.
598	*
599	* This function is meaningless in SQLite, so we do nothing.
600	*
601	* @param string $name Not used.
602	*
603	* @return string
604	*/
605	public function release_lock( $name ) {
606	return '1=1';
607	}
608
609	/**
610	* Method to emulate MySQL UCASE() function.
611	*
612	* This is MySQL alias for upper() function. This function rewrites it
613	* to SQLite compatible name upper().
614	*
615	* @param string $content String to be converted to uppercase.
616	*
617	* @return string SQLite compatible function name.
618	*/
619	public function ucase( $content ) {
620	return "upper($content)";
621	}
622
623	/**
624	* Method to emulate MySQL LCASE() function.
625	*
626	* This is MySQL alias for lower() function. This function rewrites it
627	* to SQLite compatible name lower().
628	*
629	* @param string $content String to be converted to lowercase.
630	*
631	* @return string SQLite compatible function name.
632	*/
633	public function lcase( $content ) {
634	return "lower($content)";
635	}
636
637	/**
638	* Method to emulate MySQL UNHEX() function.
639	*
640	* For a string argument str, UNHEX(str) interprets each pair of characters
641	* in the argument as a hexadecimal number and converts it to the byte represented
642	* by the number. The return value is a binary string.
643	*
644	* @param string $number Number to be unhexed.
645	*
646	* @return string Binary string
647	*/
648	public function unhex( $number ) {
649	return pack( 'H*', $number );
650	}
651
652	/**
653	* Method to emulate MySQL INET_NTOA() function.
654	*
655	* This function gets 4 or 8 bytes integer and turn it into the network address.
656	*
657	* @param integer $num Long integer.
658	*
659	* @return string
660	*/
661	public function inet_ntoa( $num ) {
662	return long2ip( $num );
663	}
664
665	/**
666	* Method to emulate MySQL INET_ATON() function.
667	*
668	* This function gets the network address and turns it into integer.
669	*
670	* @param string $addr Network address.
671	*
672	* @return int long integer
673	*/
674	public function inet_aton( $addr ) {
675	return absint( ip2long( $addr ) );
676	}
677
678	/**
679	* Method to emulate MySQL DATEDIFF() function.
680	*
681	* This function compares two dates value and returns the difference.
682	*
683	* @param string $start Start date.
684	* @param string $end End date.
685	*
686	* @return string
687	*/
688	public function datediff( $start, $end ) {
689	$start_date = new DateTime( $start );
690	$end_date = new DateTime( $end );
691	$interval = $end_date->diff( $start_date, false );
692
693	return $interval->format( '%r%a' );
694	}
695
696	/**
697	* Method to emulate MySQL LOCATE() function.
698	*
699	* This function returns the position if $substr is found in $str. If not,
700	* it returns 0. If mbstring extension is loaded, mb_strpos() function is
701	* used.
702	*
703	* @param string $substr Needle.
704	* @param string $str Haystack.
705	* @param integer $pos Position.
706	*
707	* @return integer
708	*/
709	public function locate( $substr, $str, $pos = 0 ) {
710	if ( ! extension_loaded( 'mbstring' ) ) {
711	$val = strpos( $str, $substr, $pos );
712	if ( false !== $val ) {
713	return $val + 1;
714	}
715	return 0;
716	}
717	$val = mb_strpos( $str, $substr, $pos );
718	if ( false !== $val ) {
719	return $val + 1;
720	}
721	return 0;
722	}
723
724	/**
725	* Method to return GMT date in the string format.
726	*
727	* @return string formatted GMT date 'dddd-mm-dd'
728	*/
729	public function utc_date() {
730	return gmdate( 'Y-m-d', time() );
731	}
732
733	/**
734	* Method to return GMT time in the string format.
735	*
736	* @return string formatted GMT time '00:00:00'
737	*/
738	public function utc_time() {
739	return gmdate( 'H:i:s', time() );
740	}
741
742	/**
743	* Method to return GMT time stamp in the string format.
744	*
745	* @return string formatted GMT timestamp 'yyyy-mm-dd 00:00:00'
746	*/
747	public function utc_timestamp() {
748	return gmdate( 'Y-m-d H:i:s', time() );
749	}
750
751	/**
752	* Method to return MySQL version.
753	*
754	* This function only returns the current newest version number of MySQL,
755	* because it is meaningless for SQLite database.
756	*
757	* @return string representing the version number: major_version.minor_version
758	*/
759	public function version() {
760	return '5.5';
761	}
762	}
763