- multivalued inserts, [ticket:2623]

- update "not supported" messages for empty inserts, mutlivalue inserts

- rework the ValuesBase approach for multiple value sets so that stmt.parameters
does store a list for multiple values; the _has_multiple_parameters flag now indicates
which of the two modes the statement is within.  it now raises exceptions if a subsequent
call to values() attempts to call a ValuesBase with one mode in the style of the other
mode; that is, you can't switch a single- or multi- valued ValuesBase to the other mode,
and also if a multiple value is passed simultaneously with a kwargs set.
Added tests for these error conditions

- Calling values() multiple times in multivalue mode now extends the parameter list to
include the new parameter sets.

- add error/test if multiple *args were passed to ValuesBase.values()

- rework the compiler approach for multivalue inserts, back to where
_get_colparams() returns the same list of (column, value) as before, thereby
maintaining the identical number of append() and other calls when multivalue
is not enabled.  In the case of multivalue, it makes a last-minute switch to return
a list of lists instead of the single list.  As it constructs the additional lists, the inline
defaults and other calculated default parameters of the first parameter
set are copied into the newly generated lists so that these features continue
to function for a multivalue insert.   Multivalue inserts now add no additional
function calls to the compilation for regular insert constructs.

- parameter lists for multivalue inserts now includes an integer index for all
parameter sets.

- add detailed documentation for ValuesBase.values(), including careful wording
to describe the difference between multiple values and an executemany() call.

- add a test for multivalue insert + returning - it works !

- remove the very old/never used "postgresql_returning"/"firebird_returning" flags.

1 files changed, 126 insertions, 4 deletions

diff --git a/test/sql/test_generative.py b/test/sql/test_generative.py
index fdec2f840..e868cbe88 100644
--- a/test/sql/test_generative.py
+++ b/test/sql/test_generative.py
@@ -6,9 +6,9 @@ from sqlalchemy.testing import fixtures, AssertsExecutionResults, \
 from sqlalchemy import testing
 from sqlalchemy.sql.visitors import ClauseVisitor, CloningVisitor, \
     cloned_traverse, ReplacingCloningVisitor
-from sqlalchemy import util, exc
+from sqlalchemy import exc
 from sqlalchemy.sql import util as sql_util
-from sqlalchemy.testing import eq_, ne_, assert_raises
+from sqlalchemy.testing import eq_, is_, assert_raises, assert_raises_message
 
 class TraversalTest(fixtures.TestBase, AssertsExecutionResults):
     """test ClauseVisitor's traversal, particularly its
@@ -1304,8 +1304,8 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
         s = text('select 42', execution_options=dict(foo='bar'))
         assert s._execution_options == dict(foo='bar')
 
-class InsertTest(fixtures.TestBase, AssertsCompiledSQL):
-    """Tests the generative capability of Insert"""
+class ValuesBaseTest(fixtures.TestBase, AssertsCompiledSQL):
+    """Tests the generative capability of Insert, Update"""
 
     __dialect__ = 'default'
 
@@ -1351,3 +1351,125 @@ class InsertTest(fixtures.TestBase, AssertsCompiledSQL):
                             "table1 (col1, col2, col3) "
                             "VALUES (:col1, :col2, :col3)")
 
+    def test_add_kwarg(self):
+        i = t1.insert()
+        eq_(i.parameters, None)
+        i = i.values(col1=5)
+        eq_(i.parameters, {"col1": 5})
+        i = i.values(col2=7)
+        eq_(i.parameters, {"col1": 5, "col2": 7})
+
+    def test_via_tuple_single(self):
+        i = t1.insert()
+        eq_(i.parameters, None)
+        i = i.values((5, 6, 7))
+        eq_(i.parameters, {"col1": 5, "col2": 6, "col3": 7})
+
+    def test_kw_and_dict_simulatenously_single(self):
+        i = t1.insert()
+        i = i.values({"col1": 5}, col2=7)
+        eq_(i.parameters, {"col1": 5, "col2": 7})
+
+    def test_via_tuple_multi(self):
+        i = t1.insert()
+        eq_(i.parameters, None)
+        i = i.values([(5, 6, 7), (8, 9, 10)])
+        eq_(i.parameters, [
+                {"col1": 5, "col2": 6, "col3": 7},
+                {"col1": 8, "col2": 9, "col3": 10},
+                ]
+            )
+
+    def test_inline_values_single(self):
+        i = t1.insert(values={"col1": 5})
+        eq_(i.parameters, {"col1": 5})
+        is_(i._has_multi_parameters, False)
+
+    def test_inline_values_multi(self):
+        i = t1.insert(values=[{"col1": 5}, {"col1": 6}])
+        eq_(i.parameters, [{"col1": 5}, {"col1": 6}])
+        is_(i._has_multi_parameters, True)
+
+    def test_add_dictionary(self):
+        i = t1.insert()
+        eq_(i.parameters, None)
+        i = i.values({"col1": 5})
+        eq_(i.parameters, {"col1": 5})
+        is_(i._has_multi_parameters, False)
+
+        i = i.values({"col1": 6})
+        # note replaces
+        eq_(i.parameters, {"col1": 6})
+        is_(i._has_multi_parameters, False)
+
+        i = i.values({"col2": 7})
+        eq_(i.parameters, {"col1": 6, "col2": 7})
+        is_(i._has_multi_parameters, False)
+
+    def test_add_kwarg_disallowed_multi(self):
+        i = t1.insert()
+        i = i.values([{"col1": 5}, {"col1": 7}])
+        assert_raises_message(
+            exc.InvalidRequestError,
+            "This construct already has multiple parameter sets.",
+            i.values, col2=7
+        )
+
+    def test_cant_mix_single_multi_formats_dict_to_list(self):
+        i = t1.insert().values(col1=5)
+        assert_raises_message(
+            exc.ArgumentError,
+            "Can't mix single-values and multiple values "
+            "formats in one statement",
+            i.values, [{"col1": 6}]
+        )
+
+    def test_cant_mix_single_multi_formats_list_to_dict(self):
+        i = t1.insert().values([{"col1": 6}])
+        assert_raises_message(
+            exc.ArgumentError,
+            "Can't mix single-values and multiple values "
+            "formats in one statement",
+            i.values, {"col1": 5}
+        )
+
+    def test_erroneous_multi_args_dicts(self):
+        i = t1.insert()
+        assert_raises_message(
+            exc.ArgumentError,
+            "Only a single dictionary/tuple or list of "
+            "dictionaries/tuples is accepted positionally.",
+            i.values, {"col1": 5}, {"col1": 7}
+        )
+
+    def test_erroneous_multi_args_tuples(self):
+        i = t1.insert()
+        assert_raises_message(
+            exc.ArgumentError,
+            "Only a single dictionary/tuple or list of "
+            "dictionaries/tuples is accepted positionally.",
+            i.values, (5, 6, 7), (8, 9, 10)
+        )
+
+    def test_erroneous_multi_args_plus_kw(self):
+        i = t1.insert()
+        assert_raises_message(
+            exc.ArgumentError,
+            "Can't pass kwargs and multiple parameter sets simultaenously",
+            i.values, [{"col1": 5}], col2=7
+        )
+
+    def test_update_no_support_multi_values(self):
+        u = t1.update()
+        assert_raises_message(
+            exc.InvalidRequestError,
+            "This construct does not support multiple parameter sets.",
+            u.values, [{"col1": 5}, {"col1": 7}]
+        )
+
+    def test_update_no_support_multi_constructor(self):
+        assert_raises_message(
+            exc.InvalidRequestError,
+            "This construct does not support multiple parameter sets.",
+            t1.update, values=[{"col1": 5}, {"col1": 7}]
+        )