Piccolo

-------

Added support for schemas in Postgres and CockroachDB.

For example:

.. code-block:: python

class Band(Table, schema="music"):
...

When creating the table, the schema will be created automatically if it doesn't
already exist.

.. code-block:: python

await Band.create_table()

It also works with migrations. If we change the ``schema`` value for the table,
Piccolo will detect this, and create a migration for moving it to the new schema.

.. code-block:: python

class Band(Table, schema="music_2"):
...

Piccolo will detect that the table needs to be moved to a new schema.
>>> piccolo migrations new my_app --auto

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

-------

Fixing a bug with ``ModelBuilder`` and ``Decimal`` / ``Numeric`` columns.

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

-------

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!

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