Piccolo

-------

Fixture upserting
~~~~~~~~~~~~~~~~~

Fixtures can now be upserted. For example:

.. code-block:: bash

piccolo fixtures load my_fixture.json --on_conflict='DO UPDATE'

The options are:

* ``DO NOTHING``, meaning any rows with a matching primary key will be left
alone.
* ``DO UPDATE``, meaning any rows with a matching primary key will be updated.

This is really useful, as you can now edit fixtures and load them multiple
times without getting foreign key constraint errors.

Schema fixes
~~~~~~~~~~~~

We recently added support for schemas, for example:

.. code-block:: python

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

This release contains:

* A fix for migrations when changing a table's schema back to 'public' (thanks to
sinisaos for discovering this).
* A fix for ``M2M`` queries, when the tables are in a schema other than
'public' (thanks to quinnalfaro for reporting this).

Added ``distinct`` method to ``count`` queries
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

We recently added support for ``COUNT DISTINCT`` queries. The syntax is:

.. code-block:: python

await Concert.count(distinct=[Concert.start_date])

The following alternative syntax now also works (just to be consistent with
other queries like ``select``):

.. code-block:: python

await Concert.count().distinct([Concert.start_date])

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

-------

``count`` queries can now return the number of distinct rows. For example, if
we have this table:

.. code-block:: python

class Concert(Table):
band = Varchar()
start_date = Date()

With this data:

.. table::
:widths: auto

=========== ==========
band start_date
=========== ==========
Pythonistas 2023-01-01
Pythonistas 2023-02-03
Rustaceans 2023-01-01
=========== ==========

We can easily get the number of unique concert dates:

.. code-block:: python

>>> await Concert.count(distinct=[Concert.start_date])
2

We could have just done this instead:

.. code-block:: python

len(await Concert.select(Concert.start_date).distinct())

But it's far less efficient when you have lots of rows, because all of the
distinct rows need to be returned from the database.

Also, the docs for the ``count`` query, aggregate functions, and
``group_by`` clause were significantly improved.

Many thanks to lqmanh and sinisaos for their help with this.

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

-------

If Piccolo detects a renamed table in an auto migration, it asks the user for
confirmation. When lots of tables have been renamed, Piccolo is now more
intelligent about when to ask for confirmation. Thanks to sumitsharansatsangi
for suggesting this change, and sinisaos for reviewing.

Also, fixed the type annotations for ``MigrationManager.add_table``.

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

-------

Fixed a bug with serialising table classes in migrations.

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

-------

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.

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