Piccolo

-------

Added the ``on_conflict`` clause for ``insert`` queries. This enables **upserts**.

For example, here we insert some bands, and if they already exist then do
nothing:

.. code-block:: python

await Band.insert(
Band(name='Pythonistas'),
Band(name='Rustaceans'),
Band(name='C-Sharps'),
).on_conflict(action='DO NOTHING')

Here we insert some albums, and if they already exist then we update the price:

.. code-block:: python

await Album.insert(
Album(title='OK Computer', price=10.49),
Album(title='Kid A', price=9.99),
Album(title='The Bends', price=9.49),
).on_conflict(
action='DO UPDATE',
target=Album.title,
values=[Album.price]
)

Thanks to sinisaos for helping with this.

-------------------------------------------------------------------------------

-------

ASGI frameworks
~~~~~~~~~~~~~~~

The ASGI frameworks in ``piccolo asgi new`` have been updated. ``starlite`` has
been renamed to ``litestar``. Thanks to sinisaos for this.

ModelBuilder
~~~~~~~~~~~~

Generic types are now used in ``ModelBuilder``.

.. code-block:: python

mypy knows this is a `Band` instance:
band = await ModelBuilder.build(Band)

``DISTINCT ON``
~~~~~~~~~~~~~~~

Added support for ``DISTINCT ON`` queries. For example, here we fetch the most
recent album for each band:

.. code-block:: python

>>> await Album.select().distinct(
... on=[Album.band]
... ).order_by(
... Album.band
... ).order_by(
... Album.release_date,
... ascending=False
... )

Thanks to sinisaos and williamflaherty for their help with this.

-------------------------------------------------------------------------------

-------

Joins are now possible without foreign keys using ``join_on``.

For example:

.. code-block:: python

class Manager(Table):
name = Varchar(unique=True)
email = Varchar()

class Band(Table):
name = Varchar()
manager_name = Varchar()

>>> await Band.select(
... Band.name,
... Band.manager_name.join_on(Manager.name).email
... )

-------------------------------------------------------------------------------

-------

Added support for savepoints within transactions.

.. code-block:: python

async with DB.transaction() as transaction:
await Manager.objects().create(name="Great manager")
savepoint = await transaction.savepoint()
await Manager.objects().create(name="Great manager")
await savepoint.rollback_to()
Only the first manager will be inserted.

The behaviour of nested context managers has also been changed slightly.

.. code-block:: python

async with DB.transaction():
async with DB.transaction():
This used to raise an exception

We no longer raise an exception if there are nested transaction context
managers, instead the inner ones do nothing.

If you want the existing behaviour:

.. code-block:: python

async with DB.transaction():
async with DB.transactiona(allow_nested=False):
TransactionError!

-------------------------------------------------------------------------------

-------

Added the ``log_responses`` option to the database engines. This makes the
engine print out the raw response from the database for each query, which
is useful during debugging.

.. code-block:: python

piccolo_conf.py

DB = PostgresEngine(
config={'database': 'my_database'},
log_queries=True,
log_responses=True
)

We also updated the Starlite ASGI template - it now uses the new import paths
(thanks to sinisaos for this).

-------------------------------------------------------------------------------

-------

Joins now work within ``update`` queries. For example:

.. code-block:: python

await Band.update({
Band.name: 'Amazing Band'
}).where(
Band.manager.name == 'Guido'
)

Other changes:

* Improved the template used by ``piccolo app new`` when creating a new
Piccolo app (it now uses ``table_finder``).

-------------------------------------------------------------------------------