Piccolo

-------

``piccolo fixtures load`` is now more intelligent about how it loads data, to
avoid foreign key constraint errors.

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

-------

``Array`` columns now support choices.

.. code-block:: python

class Ticket(Table):
class Extras(str, enum.Enum):
drink = "drink"
snack = "snack"
program = "program"

extras = Array(Varchar(), choices=Extras)

We can then use the ``Enum`` in our queries:

.. code-block:: python

>>> await Ticket.insert(
... Ticket(extras=[Extras.drink, Extras.snack]),
... Ticket(extras=[Extras.program]),
... )

This will also be supported in Piccolo Admin in the next release.

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

------

You can now use the ``returning`` clause with ``delete`` queries.

For example:

.. code-block:: python

>>> await Band.delete().where(Band.popularity < 100).returning(Band.name)
[{'name': 'Terrible Band'}, {'name': 'Awful Band'}]

This also means you can count the number of deleted rows:

.. code-block:: python

>>> len(await Band.delete().where(Band.popularity < 100).returning(Band.id))
2

Thanks to waldner for adding this feature.

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

------

SQLite ``TransactionType``
~~~~~~~~~~~~~~~~~~~~~~~~~~

You can now specify the transaction type for SQLite.

This is useful when using SQLite in production, as it's possible to get
``database locked`` errors if you're running lots of transactions concurrently,
and don't use the correct transaction type.

In this example we use an ``IMMEDIATE`` transaction:

.. code-block:: python

from piccolo.engine.sqlite import TransactionType

async with Band._meta.db.transaction(
transaction_type=TransactionType.immediate
):
band = await Band.objects().get_or_create(Band.name == 'Pythonistas')
...

We've added a `new tutorial <https://piccolo-orm.readthedocs.io/en/latest/piccolo/tutorials/using_sqlite_and_asyncio_effectively.html>`_
which explains this in more detail, as well as other tips for using asyncio and
SQLite together effectively.

Thanks to powellnorma and sinisaos for their help with this.

Other changes
~~~~~~~~~~~~~

* Fixed a bug with camelCase column names (we recommend using snake_case, but
sometimes it's unavoidable when using Piccolo with an existing schema).
Thanks to sinisaos for this.
* Fixed a typo in the docs with ``raw`` queries - thanks to StitiFatah for
this.

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

------

Some big improvements to ``order_by`` clauses.

It's now possible to combine ascending and descending:

.. code-block:: python

await Band.select(
Band.name,
Band.popularity
).order_by(
Band.name
).order_by(
Band.popularity,
ascending=False
)

You can also order by anything you want using ``OrderByRaw``:

.. code-block:: python

from piccolo.query import OrderByRaw

await Band.select(
Band.name
).order_by(
OrderByRaw('random()')
)

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

------

Added the ``auto_update`` argument to ``Column``. Its main use case is columns
like ``modified_on`` where we want the value to be updated automatically each
time the row is saved.

.. code-block:: python

class Band(Table):
name = Varchar()
popularity = Integer()
modified_on = Timestamp(
null=True,
default=None,
auto_update=datetime.datetime.now
)

The `modified_on` column will automatically be updated to the current
timestamp:
>>> await Band.update({
... Band.popularity: Band.popularity + 100
... }).where(
... Band.name == 'Pythonistas'
... )

It works with ``MyTable.update`` and also when using the ``save`` method on
an existing row.

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