anewt-developers team mailing list archive

[noreply@xxxxxxxxxxxxx]

------------------------------------------------------------
revno: 1726
committer: Wouter Bolsterlee <uws@xxxxxxxxx>
branch nick: anewt.uws
timestamp: Sun 2009-08-02 21:30:25 +0200
message:
  [database] Support ?int[]? style placeholders for array values
  
  This is very useful for 'WHERE foo IN (...)' queries. Added
  tests and extensive documentation with descriptions and
  examples on how to use this.
modified:
  database/sql-template.lib.php
  database/test/sqlite.test.php


--
lp:anewt
https://code.launchpad.net/~uws/anewt/anewt.uws

Your team Anewt developers is subscribed to branch lp:anewt.
To unsubscribe from this branch go to https://code.launchpad.net/~uws/anewt/anewt.uws/+edit-subscription.

=== modified file 'database/sql-template.lib.php'
--- database/sql-template.lib.php	2009-08-02 16:32:09 +0000
+++ database/sql-template.lib.php	2009-08-02 19:30:25 +0000
@@ -15,9 +15,14 @@
  * internally by AnewtDatabasePreparedQuery, but can also be used directly to
  * construct complex SQL queries with e.g. a variable number of placeholders.
  *
- * SQL templates can be used in two modes: positional and named mode. The amount
- * of input checking performed is the same, but the way the placeholders are
- * specified and filled differs.
+ * \section Placeholders
+ *
+ * SQL templates can be used in two modes: <strong>positional and named
+ * mode</strong>. The amount of input checking performed is the same, but the
+ * way the placeholders are specified and filled differs. Additionally, both
+ * modes support array values.
+ *
+ * \subsection positional-mode Positional mode
  *
  * In positional mode, placeholders look like <code>?int?</code>, and when
  * filling the template the values are provided as a list of values to be used
@@ -25,18 +30,62 @@
  * AnewtDatabaseSQLTemplate::fill() is used) or as an array (if
  * AnewtDatabaseSQLTemplate::fillv() is used).
  *
+ * Example:
+ *
+ * \code
+ * $connection->prepare_execute(
+ *     'INSERT INTO person (name, age) VALUES (?str?, ?int?)',
+ *     'John Doe', 12);
+ * \endcode
+ *
+ *
+ * \subsection name-mode Named mode
+ *
  * In named mode, placeholders look like <code>?int:name?</code>, and when
  * filling the template the values are provided as an associative array or as an
  * AnewtContainer instance, and the placeholder name is used to obtain the value
- * from the associative array or AnewtContainer. In this mode you can only pass
- * a single parameter to provide values, which means
- * AnewtDatabaseSQLTemplate::fillv() must be used
- * ( AnewtDatabaseSQLTemplate::fill() cannot). This also applies for the query
+ * from the associative array or AnewtContainer. Named mode allows for cleaner
+ * code in some cases, and also allows you to use the same value at different
+ * places in your query.
+ *
+ * Named mode templates can only be filled using a single values parameter,
+ * which means AnewtDatabaseSQLTemplate::fillv() must be used;
+ * AnewtDatabaseSQLTemplate::fill() cannot. This also applies for the query
  * methods on AnewtDatabaseConnection, where only the <code>...v()</code>
  * variants can be used, e.g. use
  * AnewtDatabaseConnection::prepare_executev_fetch_all(), not
  * AnewtDatabaseConnection::prepare_execute_fetch_all().
  *
+ * Example:
+ *
+ * \code
+ * $person = array(
+ *     'name' => 'John Doe',
+ *     'age'  => 12);
+ * $connection->prepare_executev(
+ *     'INSERT INTO person (name, age) VALUES (?str:name?, ?int:age?)',
+ *     $person);
+ * \endcode
+ *
+ *
+ * \subsection array-placholders Array placeholders
+ *
+ * In both named and positional modes array placeholders are supported. This
+ * means that you can use <code>?int[]?</code> (positional) or
+ * <code>?int[]:ids?</code> (named) to specify that you will not substitute
+ * a a single, but multiple values,. When the AnewtDatabaseSQLTemplate is
+ * filled, type-checking will be done on all values, and the result will be
+ * a comma-separated list of escaped values. This is most useful for <code>IN
+ * (...)</code> SQL queries.
+ *
+ * Example:
+ *
+ * \code
+ * $ids = array(1, 2, 3);
+ * $connection->prepare_execute_fetch_all(
+ *     'SELECT * FROM table WHERE id IN * (?int[]?);', $ids);
+ * \endcode
+ *
  * \see AnewtDatabaseConnection::prepare()
  */
 final class AnewtDatabaseSQLTemplate
@@ -152,7 +201,7 @@
 				return ANEWT_DATABASE_SQL_FIELD_TYPE_TABLE;
 
 			default:
-				throw new AnewtDatabaseException('Field type "%s" is unknown', $type_str);
+				throw new AnewtDatabaseException('Field type "%s" is unknown', $field_type);
 		}
 	}
 
@@ -223,17 +272,18 @@
 
 		/* Find placeholders fields. All placeholders start with ? followed by
 		 * a keyword and end with ? too, e.g. ?int? for a positional
-		 * placeholder and ?string:somename? for a named placeholder. */
+		 * placeholder and ?string:somename? for a named placeholder. In both
+		 * cases, the parameter can end with [] to indicate an array of values. */
 
 		$placeholder_matches = array();
-		$placeholder_pattern_positional = '/\?([a-z]+)\?/i';
-		$placeholder_pattern_named = '/\?([a-z]+):([^?]+)\?/i';
+		$placeholder_pattern_positional = '/\?([a-z]+)(\[\])?\?/i';
+		$placeholder_pattern_named = '/\?([a-z]+)(\[\])?:([^?]+)\?/i';
 
 		if (preg_match_all($placeholder_pattern_named, $sql, $placeholder_matches))
 		{
 			$this->named_mode = true;
 			 if (preg_match($placeholder_pattern_positional, $sql))
-				 throw new AnewtDatabaseQueryException('Mixing positional and named placeholders is not supported');
+				 throw new AnewtDatabaseQueryException('Mixing positional and named placeholders is not supported.');
 		}
 		else
 		{
@@ -246,35 +296,45 @@
 			 * using named mode. Extract the placeholder types and save them in
 			 * $this->placeholders, and replace all ?field? parts with %s to
 			 * allow easy vsprintf substitution when in the fill() method.
-			 *
-			 * In positional mode:
-			 * - $placeholder_matches[1] contains the placeholder types
-			 * - $this->placeholders will be a numeric array of field types
-			 *
-			 * In named mode:
-			 * - $placeholder_matches[1] contains the placeholder types
-			 * - $placeholder_matches[2] contains the placeholder names
-			 * - $this->placeholders will be a numeric array of (name, field type) tuples
 			 */
 
+			$multiple = false;
 			if ($this->named_mode)
 			{
+				/* In named mode:
+				 * - $placeholder_matches[1] contains the placeholder types
+				 * - $placeholder_matches[2] contains the placeholder names
+				 * - $this->placeholders will be a numeric array of (name, field type, multiple) tuples
+				 */
+
 				$sql = preg_replace($placeholder_pattern_named, '%s', $sql);
 
 				while ($placeholder_matches[1])
 				{
+					
 					$type = AnewtDatabaseSQLTemplate::field_type_for_string(array_shift($placeholder_matches[1]));
-					$name = array_shift($placeholder_matches[2]);
+					$multiple = (array_shift($placeholder_matches[2]) === '[]');
+					$name = array_shift($placeholder_matches[3]);
 
-					$this->placeholders[] = array($name, $type);
+					$this->placeholders[] = array($name, $type, $multiple);
 				}
 			}
 			else
 			{
+				/* In positional mode:
+				 * - $placeholder_matches[1] contains the placeholder types
+				 * - $this->placeholders will be a numeric array of (NULL, field type, multiple) tuples
+				 */
+
 				$sql = preg_replace($placeholder_pattern_positional, '%s', $sql);
 
-				foreach ($placeholder_matches[1] as $type_string)
-					$this->placeholders[] = AnewtDatabaseSQLTemplate::field_type_for_string($type_string);
+				while ($placeholder_matches[1])
+				{
+					$type = AnewtDatabaseSQLTemplate::field_type_for_string(array_shift($placeholder_matches[1]));
+					$multiple = (array_shift($placeholder_matches[2]) === '[]');
+
+					$this->placeholders[] = array(null, $type, $multiple);
+				}
 			}
 		}
 
@@ -294,6 +354,8 @@
 	 *
 	 * \return
 	 *   The escaped value
+	 *
+	 * \see escape_field_array
 	 */
 	private function escape_field($field_type, $value)
 	{
@@ -494,6 +556,32 @@
 	}
 
 	/**
+	 * Escape multiple values that have the same field type.
+	 *
+	 * This method concatenates all values using a comma as separator. This
+	 * method is used for array placeholders and can be used to build queries
+	 * like these: <code>... WHERE value IN (?str[]?)</code>.
+	 *
+	 * \param $field_type
+	 *   The field type (one of the \c ANEWT_DATABASE_SQL_FIELD_TYPE_* constants)
+	 * \param $values
+	 *   The values to escape
+	 *
+	 * \return
+	 *   The escaped values, in a comma-separated string.
+	 *
+	 * \see escape_field
+	 */
+	private function escape_field_array($field_type, $values)
+	{
+		$values_escaped = array();
+		foreach ($values as $value)
+			$values_escaped[] = $this->escape_field($field_type, $value);
+
+		return join(', ', $values_escaped);
+	}
+
+	/**
 	 * Fill the SQL template using the values passed as multiple parameters.
 	 *
 	 * See AnewtDatabaseSQLTemplate::fillv() for a detailed description.
@@ -530,19 +618,24 @@
 		$n_placeholders = count($this->placeholders);
 		$escaped_values = array();
 
-		/* Named mode... */
+
+		/* I. Named mode */
+
 		if ($this->named_mode)
 		{
 			/* Sanity checks */
+
 			$values_is_container = ($values instanceof AnewtContainer);
 			if (!is_assoc_array($values) && !$values_is_container)
 				throw new AnewtDatabaseQueryException(
 					'SQL templates in named mode require a single associative array or AnewtContainer instance when filled.');
 
+
 			/* Fill the placeholders */
+
 			for ($i = 0; $i < $n_placeholders; $i++)
 			{
-				list ($field_name, $field_type) = $this->placeholders[$i];
+				list ($field_name, $field_type, $multiple) = $this->placeholders[$i];
 
 				if ($values_is_container)
 				{
@@ -556,14 +649,27 @@
 					$value = $values[$field_name];
 				}
 
-				$escaped_values[] = $this->escape_field($field_type, $value);
+				/* Multiple values */
+				if ($multiple)
+				{
+					if (!is_numeric_array($value))
+						throw new AnewtDatabaseQueryException('Value for field "%s[]:%s" is not a numeric array.', $field_type, $field_name);
+
+					$escaped_values[] = $this->escape_field_array($field_type, $value);
+				}
+				/* Single value */
+				else
+					$escaped_values[] = $this->escape_field($field_type, $value);
 			}
 		}
 
-		/* ... and positional mode */
+
+		/* II. Positional mode */
+
 		else
 		{
 			/* Sanity checks */
+
 			if (!is_numeric_array($values))
 				throw new AnewtDatabaseQueryException('SQL templates in positional mode can only be filled using a numeric array');
 
@@ -573,12 +679,28 @@
 					'Expected %d placeholder values, but %d values were provided.',
 					$n_placeholders, $n_values);
 
+
 			/* Fill the placeholders */
-			for ($i = 0; $i < $n_placeholders; $i++)
+
+			foreach ($this->placeholders as $placeholder)
 			{
-				$field_type = $this->placeholders[$i];
-				$value = $values[$i];
-				$escaped_values[] = $this->escape_field($field_type, $value);
+				list ($field_name, $field_type, $multiple) = $placeholder;
+
+				$value = array_shift($values);
+
+				/* Multiple values */
+				if ($multiple)
+				{
+					if (!is_numeric_array($value))
+						throw new AnewtDatabaseQueryException('Value for field "%s[]" is not a numeric array.', $field_type);
+
+					$escaped_values[] = $this->escape_field_array($field_type, $value);
+				}
+				/* Single value */
+				else
+				{
+					$escaped_values[] = $this->escape_field($field_type, $value);
+				}
 			}
 		}
 

=== modified file 'database/test/sqlite.test.php'
--- database/test/sqlite.test.php	2009-08-02 16:32:09 +0000
+++ database/test/sqlite.test.php	2009-08-02 19:30:25 +0000
@@ -255,6 +255,25 @@
 		$this->assertEquals(1, $n);
 	}
 
+	function test_array_values()
+	{
+		$connection = AnewtDatabase::get_connection();
+
+		$pq = $connection->prepare('SELECT * FROM test_table WHERE integer_col IN (?int[]?);');
+		$rs = $pq->execute(array(1, 2, 3));
+		$row = $rs->fetch_all();
+	}
+
+	/**
+	 * @expectedException AnewtDatabaseQueryException
+	 */
+	function test_invalid_array_values()
+	{
+		AnewtDatabase::get_connection()->prepare_execute(
+			'SELECT * FROM test_table WHERE integer_col IN (?time[]?);',
+			array(NULL, 'invalid-time-value'));
+	}
+
 	function test_transaction()
 	{
 		$cnt_sql = 'SELECT COUNT(*) AS cnt FROM test_table';