Rewrite positional handling, test for "numeric"

Changed how the positional compilation is performed. It's rendered by the compiler
the same as the pyformat compilation. The string is then processed to replace
the placeholders with the correct ones, and to obtain the correct order of the
parameters.
This vastly simplifies the computation of the order of the parameters, that in
case of nested CTE is very hard to compute correctly.

Reworked how numeric paramstyle behavers:
- added support for repeated parameter, without duplicating them like in normal
positional dialects
- implement insertmany support. This requires that the dialect supports out of
order placehoders, since all parameters that are not part of the VALUES clauses
are placed at the beginning of the parameter tuple
- support for different identifiers for a numeric parameter. It's for example
possible to use postgresql style placeholder $1, $2, etc

Added two new dialect based on sqlite to test "numeric" fully using
both :1 style and $1 style. Includes a workaround for SQLite's
not-really-correct numeric implementation.

Changed parmstyle of asyncpg dialect to use numeric, rendering with its native
$ identifiers

Fixes: #8926
Fixes: #8849
Change-Id: I7c640467d49adfe6d795cc84296fc7403dcad4d6

2 files changed, 22 insertions, 0 deletions

diff --git a/doc/build/changelog/unreleased_20/8849.rst b/doc/build/changelog/unreleased_20/8849.rst
new file mode 100644
index 000000000..29ecf2a2c
--- /dev/null
+++ b/doc/build/changelog/unreleased_20/8849.rst
@@ -0,0 +1,14 @@
+.. change::
+    :tags: bug, sql
+    :tickets: 8849
+
+    Reworked how numeric paramstyle behavers, in particular, fixed insertmany
+    behaviour that prior to this was non functional; added support for repeated
+    parameter without duplicating them like in other positional dialects;
+    introduced new numeric paramstyle called ``numeric_dollar`` that can be
+    used to render statements that use the PostgreSQL placeholder style (
+    i.e. ``$1, $2, $3``).
+    This change requires that the dialect supports out of order placehoders,
+    that may be used used in the statements, in particular when using
+    insert-many values with statement that have parameters in the returning
+    clause.
diff --git a/doc/build/changelog/unreleased_20/8926.rst b/doc/build/changelog/unreleased_20/8926.rst
new file mode 100644
index 000000000..a0000fb87
--- /dev/null
+++ b/doc/build/changelog/unreleased_20/8926.rst
@@ -0,0 +1,8 @@
+.. change::
+    :tags: asyncpg
+    :tickets: 8926
+
+    Changed the paramstyle used by asyncpg from ``format`` to
+    ``numeric_dollar``. This has two main benefits since it does not require
+    additional processing of the statement and allows for duplicate parameters
+    to be present in the statements.