Piccolo

-------

Version pinning Pydantic to v1, as v2 has breaking changes.

We will add support for Pydantic v2 in a future release.

Thanks to sinisaos for helping with this.

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

-------

Fixture formatting
~~~~~~~~~~~~~~~~~~

When creating a fixture:

.. code-block:: bash

piccolo fixtures dump

The JSON output is now nicely formatted, which is useful because we can pipe
it straight to a file, and commit it to Git without having to manually run a
formatter on it.

.. code-block:: bash

piccolo fixtures dump > my_fixture.json

Thanks to sinisaos for this.

Protected table names
~~~~~~~~~~~~~~~~~~~~~

We used to raise a ``ValueError`` if a table was called ``user``.

.. code-block:: python

class User(Table): ValueError!
...

It's because ``user`` is already used by Postgres (e.g. try ``SELECT user`` or
``SELECT * FROM user``).

We now emit a warning instead for these reasons:

* Piccolo wraps table names in quotes to avoid clashes with reserved keywords.
* Sometimes you're stuck with a table name from a pre-existing schema, and
can't easily rename it.

Re-export ``WhereRaw``
~~~~~~~~~~~~~~~~~~~~~~

If you want to write raw SQL in your where queries you use ``WhereRaw``:

.. code-block:: python

>>> Band.select().where(WhereRaw('TRIM(name) = {}', 'Pythonistas'))

You can now import it from ``piccolo.query`` to be consistent with
``SelectRaw`` and ``OrderByRaw``.

.. code-block:: python

from piccolo.query import WhereRaw

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

-------

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.

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