Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-78

Native support for parameter lists

    Details

    • Type: Improvement Improvement
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.1
    • Component/s: Drivers
    • Labels:
      None

      Description

      Prerequisites:

      1. Detection of this mode has to be super-fast. It should not have an auto-detection but should be triggered explicitly.
      2. This mode cannot be supported after $conn->prepare(). It is only viable for $conn->executeQuery() or $conn->executeUpdate().

      Case 1: Positional

      $stmt = $conn->executeQuery("SELECT a.id FROM articles a WHERE a.id IN (?) AND a.foo = ?",
          array($paramList, $foo), array( Connection::PARAM_ARRAY => PDO::PARAM_INT) , PDO:PARAM_STR ),
          Connection::FLAG_EXPAND_ARRAYS
      );
      

      Would internally, right before execution, be rewritten to:

      $c = count($paramList); // 3!
      "SELECT a.id FROM articles a WHERE a.id IN (?, ?, ?) AND a.foo = ?"
      

      Case 2: Named

      $stmt = $conn->executeQuery("SELECT a.id FROM articles a WHERE a.id IN (:id) AND a.foo = :bar",
          array('id' => $paramList, 'bar' => $foo),
          array( array(Connection::PARAM_ARRAY => PDO::PARAM_INT), PDO::PARAM_STR ),
          Connection::FLAG_EXPAND_ARRAYS
      );
      

      Would internally, right before execution, be rewritten to:

      $c = count($paramList); // 3!
      "SELECT a.id FROM articles a WHERE a.id IN (:id1, :id2, :id3) AND a.foo = :bar"
      

        Activity

        Hide
        Benjamin Eberlei added a comment -

        Implementing this for Named parameters is not really possible without re-parsing the query after every replacement just because the named parameters can occur more then once and this messes with the positions.

        Show
        Benjamin Eberlei added a comment - Implementing this for Named parameters is not really possible without re-parsing the query after every replacement just because the named parameters can occur more then once and this messes with the positions.
        Hide
        Benjamin Eberlei added a comment -

        Using arrays as param list is a very bad idea performance wise (and also code handling).

        Algorithm now:

        1. New Constants Connection::PARAM_INT_ARRAY and PARAM_STR_ARRAY, being apart by Connection::ARRAY_TYPE_OFFSET from their PDO::PARAM_STR and PDO:PARAM_INT plain values.
        2. Detect if there is one _ARRAY param. If not abort expansion (complexity O(T)) where T is the number of types.
        3. Parse SQL statement for positional needle => character position
        4. Iterate param list and inline arrays values

        Show
        Benjamin Eberlei added a comment - Using arrays as param list is a very bad idea performance wise (and also code handling). Algorithm now: 1. New Constants Connection::PARAM_INT_ARRAY and PARAM_STR_ARRAY, being apart by Connection::ARRAY_TYPE_OFFSET from their PDO::PARAM_STR and PDO:PARAM_INT plain values. 2. Detect if there is one _ARRAY param. If not abort expansion (complexity O(T)) where T is the number of types. 3. Parse SQL statement for positional needle => character position 4. Iterate param list and inline arrays values
        Show
        Benjamin Eberlei added a comment - Implemented in https://github.com/doctrine/dbal/commit/df50f44a179028a3e30dcab02cd3ff33cc4f8498

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Benjamin Eberlei
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: