<commit_before>.. _couch-to-sql-model-migration:

***************************************
Migrating models from couch to postgres
***************************************

This is a step by step guide to migrating a single model from couch to postgres.

Selecting a Model
################

To find all classes that descend from ``Document``:
::

    from dimagi.ext.couchdbkit import Document

    def all_subclasses(cls):
        return set(cls.__subclasses__()).union([s for c in cls.__subclasses__() for s in all_subclasses(c)])

    sorted([str(s) for s in all_subclasses(Document)])

To find how many documents of a given type exist in a given environment:
::

    from corehq.dbaccessors.couchapps.all_docs import get_doc_ids_by_class, get_deleted_doc_ids_by_class
    
    len(list(get_doc_ids_by_class(MyDocumentClass) + get_deleted_doc_ids_by_class(MyDocumentClass)))

There's a little extra value to migrating models that have dedicated views:
::

    grep -r MyDocumentClass . | grep _design.*map.js

There's a lot of extra value in migrating areas where you're familiar with the code context.

Ultimately, all progress is good.

Conceptual Steps
################

1. Add SQL model
2. Wherever the couch document is saved, create or update the corresponding SQL model
3. Migrate all existing couch documents
4. Whenever a couch document is read, read from SQL instead
5. Delete couch model and any related views

Practical Steps
###############

Even a simple model takes several pull requests to migrate, to avoid data loss while deploys and migrations are in progress. Best practice is a minimum of three pull requests, described below, each deployed to all large environments before merging the next one.

A note on source control: it's best to create all pull requests at once so that reviewers have full context on the migration. It's easier to do the work in a single branch and then make the branches for individual PRs later on. If you don't typically run a linter before PRing, let the linter run on each PR and fix errors before opening the next one.

PR 1: Add SQL model and migration management command, write to SQL
****
This should contain:

* A new model, with a django migration to create it.

  * Expect to rename your model laster, and specify ``db_table`` with the final expected table name. If your couch model is ``MyModel``, name your SQL model ``SQLMyModel`` with ``db_table`` set to ``myapp_mymodel`` so that once the couch model is gone, you can rename the SQL model back to ``MyModel`` and remove the ``Meta``. It's a headache to rename models via django migrations, especially submodels.
  * `Sample commit for RegistrationRequest <https://github.com/dimagi/commcare-hq/pull/26555/commits/5<PASSWORD>>`_, a simple model with no related models. This example pulls functions common to booth couch and sql into a mixin used by both classes.
  * It's frequently useful to include a column for the corresponding couch document id. The `HqDeploy migration <https://github.com/dimagi/commcare-hq/pull/26440/files>`_ does this (search for `couch_id`).
  * Note that by default, the sql fields will be non-null. Boilerplate to run on production to identify which attributes are null in actual documents, using ``HqDeploy`` as an example (note this loads all docs into memory):
  ::
  
    from corehq.dbaccessors.couchapps.all_docs import get_all_docs_with_doc_types
    from corehq.apps.hqadmin.models import HqDeploy
    attrs = ['environment', 'user', 'date']
    docs = list(get_all_docs_with_doc_types(HqDeploy.get_db(), ['HqDeploy']))
    [(attr, len([d for d in docs if not doc.get(attr)])) for attr in attrs]

* A standalone management command that fetches all couch docs and creates a corresponding SQL model if it doesn't already exist.

  * The base class ``PopulateSQLCommand`` makes this fairly trivial for simple models.
  * `Sample commit for RegistrationRequest <https://github.com/dimagi/commcare-hq/pull/26555/commits/e8639003899d9e10fb9fc2cd7388df843104b5e1>`_.
  * Don't know which database your doc type is in? Run ``from corehq.util.couchdb_management import couch_config; couch_config.all_dbs_by_slug`` in a shell to list all of the couch databases and then ``MyModel.get_db()`` to see which one you need.
  
* Adds code to keep the couch and sql items in sync.

  * In the simple cases, this is just a matter of updating the ``save`` methods for both couch and sql to save themselves and also either find and update or create the corresponding item. `Sample commit for RegistrationRequest <https://github.com/dimagi/commcare-hq/pull/26555/commits/a157aa456850f1c1d076581035b273e6394d132a>`_.
  * The `SyncCouchToSQLMixin <https://github.com/dimagi/commcare-hq/blob/c2b93b627c830f3db7365172e9be2de0019c6421/corehq/ex-submodules/dimagi/utils/couch/migration.py#L4>`_ and `SyncSQLToCouchMixin <https://github.com/dimagi/commcare-hq/blob/c2b93b627c830f3db7365172e9be2de0019c6421/corehq/ex-submodules/dimagi/utils/couch/migration.py#L115>`_ also exist for this purpose.

* Most models belong to a domain. These models need to be deleted when the domain is deleted.

  * Add the new model to `DOMAIN_DELETE_OPERATIONS <https://github.com/dimagi/commcare-hq/blob/522294560cee0f3ac1ddeae0501d653b1ea0f215/corehq/apps/domain/deletion.py#L179>`_.
  * Update tests in `test_delete_domain.py`. `Sample PR that handles several app manager models <https://github.com/dimagi/commcare-hq/pull/26310/files>`_.
  
To test this step locally:

* With master checked out, make sure you have at least one couch document that will get migrated.
* Check out your branch and run the populate command. Verify it creates as many objects as expected.
* Test editing the pre-existing object. In a shell, verify your changes appear in both couch and sql.
* Test creating a new object. In a shell, verify your changes appear in both couoch and sql.

Once this PR is deployed, run the migration command in any environments where it's likely to take more than a trivial amount of time.<commit_msg>Added max length of attributes<commit_after>.. _couch-to-sql-model-migration:

***************************************
Migrating models from couch to postgres
***************************************

This is a step by step guide to migrating a single model from couch to postgres.

Selecting a Model
################

To find all classes that descend from ``Document``:
::

    from dimagi.ext.couchdbkit import Document

    def all_subclasses(cls):
        return set(cls.__subclasses__()).union([s for c in cls.__subclasses__() for s in all_subclasses(c)])

    sorted([str(s) for s in all_subclasses(Document)])

To find how many documents of a given type exist in a given environment:
::

    from corehq.dbaccessors.couchapps.all_docs import get_doc_ids_by_class, get_deleted_doc_ids_by_class
    
    len(list(get_doc_ids_by_class(MyDocumentClass) + get_deleted_doc_ids_by_class(MyDocumentClass)))

There's a little extra value to migrating models that have dedicated views:
::

    grep -r MyDocumentClass . | grep _design.*map.js

There's a lot of extra value in migrating areas where you're familiar with the code context.

Ultimately, all progress is good.

Conceptual Steps
################

1. Add SQL model
2. Wherever the couch document is saved, create or update the corresponding SQL model
3. Migrate all existing couch documents
4. Whenever a couch document is read, read from SQL instead
5. Delete couch model and any related views

Practical Steps
###############

Even a simple model takes several pull requests to migrate, to avoid data loss while deploys and migrations are in progress. Best practice is a minimum of three pull requests, described below, each deployed to all large environments before merging the next one.

A note on source control: it's best to create all pull requests at once so that reviewers have full context on the migration. It's easier to do the work in a single branch and then make the branches for individual PRs later on. If you don't typically run a linter before PRing, let the linter run on each PR and fix errors before opening the next one.

PR 1: Add SQL model and migration management command, write to SQL
****
This should contain:

* A new model, with a django migration to create it.

  * Expect to rename your model laster, and specify ``db_table`` with the final expected table name. If your couch model is ``MyModel``, name your SQL model ``SQLMyModel`` with ``db_table`` set to ``myapp_mymodel`` so that once the couch model is gone, you can rename the SQL model back to ``MyModel`` and remove the ``Meta``. It's a headache to rename models via django migrations, especially submodels.
  * `Sample commit for RegistrationRequest <https://github.com/dimagi/commcare-hq/pull/26555/commits/5df642a5f798880e29d65f1a389d4c068aaa47c3>`_, a simple model with no related models. This example pulls functions common to booth couch and sql into a mixin used by both classes.
  * It's frequently useful to include a column for the corresponding couch document id. The `HqDeploy migration <https://github.com/dimagi/commcare-hq/pull/26440/files>`_ does this (search for `couch_id`).
  * Note that by default, the sql fields will be non-null. Boilerplate to run on production to identify which attributes are null in actual documents and what the maximum length is of each attribute, using ``HqDeploy`` as an example (note this loads all docs into memory):
  ::
  
    from corehq.dbaccessors.couchapps.all_docs import get_all_docs_with_doc_types
    from corehq.apps.hqadmin.models import HqDeploy
    attrs = ['environment', 'user', 'date']
    docs = list(get_all_docs_with_doc_types(HqDeploy.get_db(), ['HqDeploy']))
    [(attr, len([doc for doc in docs if not doc.get(attr)]), max([len(doc.get(attr, '')) for doc in docs])) for attr in attrs]

* A standalone management command that fetches all couch docs and creates a corresponding SQL model if it doesn't already exist.

  * The base class ``PopulateSQLCommand`` makes this fairly trivial for simple models.
  * `Sample commit for RegistrationRequest <https://github.com/dimagi/commcare-hq/pull/26555/commits/e8639003899d9e10fb9fc2cd7388df843104b5e1>`_.
  * Don't know which database your doc type is in? Run ``from corehq.util.couchdb_management import couch_config; couch_config.all_dbs_by_slug`` in a shell to list all of the couch databases and then ``MyModel.get_db()`` to see which one you need.
  
* Adds code to keep the couch and sql items in sync.

  * In the simple cases, this is just a matter of updating the ``save`` methods for both couch and sql to save themselves and also either find and update or create the corresponding item. `Sample commit for RegistrationRequest <https://github.com/dimagi/commcare-hq/pull/26555/commits/a157aa456850f1c1d076581035b273e6394d132a>`_.
  * The `SyncCouchToSQLMixin <https://github.com/dimagi/commcare-hq/blob/c2b93b627c830f3db7365172e9be2de0019c6421/corehq/ex-submodules/dimagi/utils/couch/migration.py#L4>`_ and `SyncSQLToCouchMixin <https://github.com/dimagi/commcare-hq/blob/c2b93b627c830f3db7365172e9be2de0019c6421/corehq/ex-submodules/dimagi/utils/couch/migration.py#L115>`_ also exist for this purpose.

* Most models belong to a domain. These models need to be deleted when the domain is deleted.

  * Add the new model to `DOMAIN_DELETE_OPERATIONS <https://github.com/dimagi/commcare-hq/blob/522294560cee0f3ac1ddeae0501d653b1ea0f215/corehq/apps/domain/deletion.py#L179>`_.
  * Update tests in `test_delete_domain.py`. `Sample PR that handles several app manager models <https://github.com/dimagi/commcare-hq/pull/26310/files>`_.
  
To test this step locally:

* With master checked out, make sure you have at least one couch document that will get migrated.
* Check out your branch and run the populate command. Verify it creates as many objects as expected.
* Test editing the pre-existing object. In a shell, verify your changes appear in both couch and sql.
* Test creating a new object. In a shell, verify your changes appear in both couoch and sql.

Once this PR is deployed, run the migration command in any environments where it's likely to take more than a trivial amount of time.
{'fraction_non_alphanumeric': 0.09073739530300542, 'fraction_numerical': 0.030629003120381016, 'mean_word_length': 4.693782141187471, 'pattern_counts': {'":': 0, '<': 20, '<?xml version=': 0, '>': 20, 'https://': 16, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '491042', 'n_tokens_mistral': 3781, 'n_tokens_neox': 3481, 'n_words': 1495}