.. _l5_tips_chatty:

Chatty
======

.. _l5_tips_chatty_database:

Database Location
-----------------

The SMS content and associated metadata is stored in a database at ``/home/purism/.purple/chatty/db/chatty-history.db``.


.. _l5_tips_chatty_export:

Export Chatty messages (SMS, Matrix)
------------------------------------

You can export the messages from Chatty with a few commands.

.. note::

   SQL is a very powerful language to search for data in databases, and the possibilities are so numerous, we cannot cover every single possible use-case out there.
   The steps we provide should provide you a good starting place to explore on your own.

#. Install ``sqlite3`` from the Terminal application.

   .. code-block:: bash

      sudo apt install sqlite3

#. Open the Terminal and create a Sqlite connection to the database.

  .. code-block:: bash

     sqlite /home/purism/.purple/chatty/db/chatty-history.db

#. Find the ``thread_id`` values for the messages that are desired.
   Write down the IDs for later.

  .. code-block:: sql

     SELECT m.thread_id, m.time, m.body FROM messages AS m;

3. Switch the output mode to a parsable format, add the column headers, and declare the output file name.
   For this guide, the CSV format will be used, however, please refer to the `Sqlite command line shell reference <https://sqlite.org/cli.html#changing_output_formats>`_ for the other available formats.

   .. code-block:: sql

      .mode csv
      .headers on

   .. note::

      * ``.mode`` sets the output format.
      * ``.headers`` on will add the column names to the top of the output.

#. By default, the column output will be separated by commas.
   If you want to change the separator, enter the ``.separator`` mode and provide the separator value.
   The example below sets it to the caret character (``^``) but you can set it to almost anything.

   .. code-block:: sql

      .separator ^

#. Run a test ``SELECT`` query to check that the output is what you want.

   .. important::

      Replace the part that say ``:thread_id`` with the thread ID value obtained earlier.

   .. code-block:: sql

      SELECT m.time, m.body FROM messages AS m WHERE m.thread_id = :thread_id ORDER BY m.time ASC;

   If you have multiple thread IDs, use ``IN`` instead of ``=`` like so:

   .. code-block:: sql

      SELECT m.time, m.body FROM messages AS m WHERE m.thread_id IN (:thread_id1, :thread_id2) ORDER BY m.time ASC;

#. If the output looks good, enable file output and set the file name.

   .. code-block:: sql

      .out my_messages.csv

   .. note::

      In this guide, the file is placed in the current directory from where the ``sqlite3`` command was invoked.
#. Run the same SQL query as before to export the data this time.
   Because of the ``.out`` command used earlier, the output of the query will be written to the specified file, though the query result will not be printed to the screen so make sure the query is correct.