Piccolo

-------

``ModelBuilder`` now works with ``LazyTableReference`` (which is used when we
have circular references caused by a ``ForeignKey``).

With this table:

.. code-block:: python

class Band(Table):
manager = ForeignKey(
LazyTableReference(
'Manager',
module_path='some.other.folder.tables'
)
)

We can now create a dynamic test fixture:

.. code-block:: python

my_model = await ModelBuilder.build(Band)

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

-------

If you have lots of Piccolo apps, you can now create auto migrations for them
all in one go:

.. code-block:: bash

piccolo migrations new all --auto

Thanks to hoosnick for suggesting this new feature.

The documentation for running migrations has also been improved, as well as
improvements to the sorting of migrations based on their dependencies.

Support for Python 3.7 was dropped in this release as it's now end of life.

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

-------

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.

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