Piccolo

------

Improved Pydantic docs
~~~~~~~~~~~~~~~~~~~~~~

The Pydantic docs used to be in the Piccolo API repo, but have been moved over
to this repo. We took this opportunity to improve them significantly with
additional examples. Courtesy sinisaos.

Internal code refactoring
~~~~~~~~~~~~~~~~~~~~~~~~~

Some of the code has been optimised and cleaned up. Courtesy yezz123.

Schema generation for recursive foreign keys
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

When using ``piccolo schema generate``, it would get stuck in a loop if a
table had a foreign key column which referenced itself. Thanks to knguyen5
for reporting this issue, and wmshort for implementing the fix. The output
will now look like:

.. code-block:: python

class Employee(Table):
name = Varchar()
manager = ForeignKey("self")

Fixing a bug with Alter.add_column
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

When using the ``Alter.add_column`` API directly (not via migrations), it would
fail with foreign key columns. For example:

.. code-block:: python

SomeTable.alter().add_column(
name="my_fk_column",
column=ForeignKey(SomeOtherTable)
).run_sync()

This has now been fixed. Thanks to wmshort for discovering this issue.

create_pydantic_model improvements
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Additional fields can now be added to the Pydantic schema. This is useful
when using Pydantic's JSON schema functionality:

.. code-block:: python

my_model = create_pydantic_model(Band, my_extra_field="Hello")
>>> my_model.schema()
{..., "my_extra_field": "Hello"}

This feature was added to support new features in Piccolo Admin.

Fixing a bug with import clashes in migrations
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

In certain situations it was possible to create a migration file with clashing
imports. For example:

.. code-block:: python

from uuid import UUID
from piccolo.columns.column_types import UUID

Piccolo now tries to detect these clashes, and prevent them. If they can't be
prevented automatically, a warning is shown to the user. Courtesy 0scarB.

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

------

Added Python 3.10 support (courtesy kennethcheo).

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

------

Fixed schema generation bug
~~~~~~~~~~~~~~~~~~~~~~~~~~~

When using ``piccolo schema generate`` to auto generate Piccolo ``Table``
classes from an existing database, it would fail in this situation:

* A table has a column with an index.
* The column name clashed with a Postgres type.

For example, we couldn't auto generate this ``Table`` class:

.. code-block:: python

class MyTable(Table):
time = Timestamp(index=True)

This is because ``time`` is a builtin Postgres type, and the ``CREATE INDEX``
statement being inspected in the database wrapped the column name in quotes,
which broke our regex.

Thanks to knguyen5 for fixing this.

Improved testing docs
~~~~~~~~~~~~~~~~~~~~~

A convenience method called ``get_table_classes`` was added to ``Finder``.

``Finder`` is the main class in Piccolo for dynamically importing projects /
apps / tables / migrations etc.

``get_table_classes`` lets us easily get the ``Table`` classes for a project.
This makes writing unit tests easier, when we need to setup a schema.

.. code-block:: python

from unittest import TestCase

from piccolo.table import create_tables, drop_tables
from piccolo.conf.apps import Finder

TABLES = Finder().get_table_classes()

class TestApp(TestCase):
def setUp(self):
create_tables(*TABLES)

def tearDown(self):
drop_tables(*TABLES)

def test_app(self):
Do some testing ...
pass

The docs were updated to reflect this.

When dropping tables in a unit test, remember to use ``piccolo tester run``, to
make sure the test database is used.

get_output_schema
~~~~~~~~~~~~~~~~~

``get_output_schema`` is the main entrypoint for database reflection in
Piccolo. It has been modified to accept an optional ``Engine`` argument, which
makes it more flexible.

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

------

Table._meta.refresh_db
~~~~~~~~~~~~~~~~~~~~~~

Added the ability to refresh the database engine.

.. code-block:: python

MyTable._meta.refresh_db()

This causes the ``Table`` to fetch the ``Engine`` again from your
``piccolo_conf.py`` file. The reason this is useful, is you might change the
``PICCOLO_CONF`` environment variable, and some ``Table`` classes have
already imported an engine. This is now used by the ``piccolo tester run``
command to ensure all ``Table`` classes have the correct engine.

ColumnMeta edge cases
~~~~~~~~~~~~~~~~~~~~~

Fixed an edge case where ``ColumnMeta`` couldn't be copied if it had extra
attributes added to it.

Improved column type conversion
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

When running migrations which change column types, Piccolo now provides the
``USING`` clause to the ``ALTER COLUMN`` DDL statement, which makes it more
likely that type conversion will be successful.

For example, if there is an ``Integer`` column, and it's converted to a
``Varchar`` column, the migration will run fine. In the past, running this in
reverse would fail. Now Postgres will try and cast the values back to integers,
which makes reversing migrations more likely to succeed.

Added drop_tables
~~~~~~~~~~~~~~~~~

There is now a convenience function for dropping several tables in one go. If
the database doesn't support ``CASCADE``, then the tables are sorted based on
their ``ForeignKey`` columns, so they're dropped in the correct order. It all
runs inside a transaction.

.. code-block:: python

from piccolo.table import drop_tables

drop_tables(Band, Manager)

This is a useful tool in unit tests.

Index support in schema generation
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

When using ``piccolo schema generate``, Piccolo will now reflect the indexes
from the database into the generated ``Table`` classes. Thanks to wmshort for
this.

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

------
Added the ``db_column_name`` option to columns. This is for edge cases where
a legacy database is being used, with problematic column names. For example,
if a column is called ``class``, this clashes with a Python builtin, so the
following isn't possible:

.. code-block:: text

class MyTable(Table):
class = Varchar() Syntax error!

You can now do the following:

.. code-block:: python

class MyTable(Table):
class_ = Varchar(db_column_name='class')

Here are some example queries using it:

.. code-block:: python

Create - both work as expected
MyTable(class_='Test').save().run_sync()
MyTable.objects().create(class_='Test').run_sync()

Objects
row = MyTable.objects().first().where(MyTable.class_ == 'Test').run_sync()
>>> row.class_
'Test'

Select
>>> MyTable.select().first().where(MyTable.class_ == 'Test').run_sync()
{'id': 1, 'class': 'Test'}

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

------
An internal code clean up (courtesy yezz123).

Dramatically improved CLI appearance when running migrations (courtesy
wmshort).

Added a runtime reflection feature, where ``Table`` classes can be generated
on the fly from existing database tables (courtesy AliSayyah). This is useful
when dealing with very dynamic databases, where tables are frequently being
added / modified, so hard coding them in a ``tables.py`` is impractical. Also,
for exploring databases on the command line. It currently just supports
Postgres.

Here's an example:

.. code-block:: python

from piccolo.table_reflection import TableStorage

storage = TableStorage()
Band = await storage.get_table('band')
>>> await Band.select().run()
[{'id': 1, 'name': 'Pythonistas', 'manager': 1}, ...]

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