Merge pull request #1100 from ADOdb/pr1002-sqlite-regression

sqlite: Fix regression in SQLDate() method

2 files changed, 36 insertions, 26 deletions

diff --git a/drivers/adodb-sqlite.inc.php b/drivers/adodb-sqlite.inc.php
index 00638e70..e9fab6dc 100644
--- a/drivers/adodb-sqlite.inc.php
+++ b/drivers/adodb-sqlite.inc.php
@@ -164,12 +164,24 @@ class ADODB_sqlite extends ADOConnection {
 	function SQLDate($fmt, $col=false)
 	{
 		$fmt = $this->qstr($fmt);
-		return ($col) ? "strftime($fmt,$col)" : "strftime($fmt)";
+		return ($col) ? "adodb_date($fmt,$col)" : "adodb_date($fmt)";
 	}
 
 
 	function _createFunctions()
 	{
+		// Register date conversion function for SQLDate() method
+		// Replaces the legacy adodb_date() functions removed in 5.23.0
+		@sqlite_create_function($this->_connectionID, 'adodb_date',
+			function (string $fmt, $date = null) : string {
+				if ($date === null || $date === false) {
+					return date($fmt);
+				}
+
+				// If it's an int then assume a Unix timestamp, otherwise convert it
+				return date($fmt, is_int($date) ? $date : strtotime($date));
+			}
+		);
 	}
 
 
diff --git a/drivers/adodb-sqlite3.inc.php b/drivers/adodb-sqlite3.inc.php
index e8fe433f..696e2990 100644
--- a/drivers/adodb-sqlite3.inc.php
+++ b/drivers/adodb-sqlite3.inc.php
@@ -400,32 +400,18 @@ class ADODB_sqlite3 extends ADOConnection {
 	 */
 	function sqlDate($fmt, $col=false)
 	{
-		/*
-		* In order to map the values correctly, we must ensure the proper
-		* casing for certain fields
-		* Y must be UC, because y is a 2 digit year
-		* d must be LC, because D is 3 char day
-		* A must be UC  because a is non-portable am
-		* Q must be UC  because q means nothing
-		*/
-		$fromChars = array('y','D','a','q');
-		$toChars   = array('Y','d','A','Q');
-		$fmt       = str_replace($fromChars,$toChars,$fmt);
+		// In order to map the values correctly, we must ensure the proper
+		// casing for certain fields:
+		// - Y must be UC, because y is a 2 digit year
+		// - d must be LC, because D is 3 char day
+		// - A must be UC  because a is non-portable am
+		// - Q must be UC  because q means nothing
+		$fromChars = array('y', 'D', 'a', 'q');
+		$toChars = array('Y', 'd', 'A', 'Q');
+		$fmt = str_replace($fromChars, $toChars, $fmt);
 
 		$fmt = $this->qstr($fmt);
-		return ($col) ? "strftime($fmt,$col)" : "strftime($fmt)";
-	}
-
-	/**
-	 * Creates any custom functions for SQLite
-	 *
-	 * This function is a placeholder for creating custom SQLite functions.
-	 * Currently, it does not implement any custom functions.
-	 *
-	 * @return void
-	 */
-	function _createFunctions()
-	{
+		return $col ? "adodb_date($fmt,$col)" : "adodb_date($fmt)";
 	}
 
 	/**
@@ -446,7 +432,19 @@ class ADODB_sqlite3 extends ADOConnection {
 			$argHostname = $argDatabasename;
 		}
 		$this->_connectionID = new SQLite3($argHostname);
-		$this->_createFunctions();
+
+		// Register date conversion function for SQLDate() method
+		// Replaces the legacy adodb_date() functions removed in 5.23.0
+		$this->_connectionID->createFunction('adodb_date',
+			function (string $fmt, $date = null) : string {
+				if ($date === null || $date === false) {
+					return date($fmt);
+				}
+
+				// If it's an int then assume a Unix timestamp, otherwise convert it
+				return date($fmt, is_int($date) ? $date : strtotime($date));
+			}
+		);
 
 		return true;
 	}