[Mayan EDMS: 2616] Database conversion from SQLite to PostgreSQL (or MySQL)

[Roberto Rosario]

At the moment Django (the web framework used by Mayan) doesn't provide an
official method to migrate from database engine to another.

It should not be up to an application software to perform platform,
framework or OS work.

However, since many Mayan users are not familiar with Django or database
engines (and they shouldn't need to) we've started a branch to add support
for conversion of the database backend by Mayan itself. The branch is
available
here: https://gitlab.com/mayan-edms/mayan-edms/tree/feature/db_migration

We are focusing on making the database conversion as simple as possible.
For this we are creating a command line program that will export the data
from SQLite and import it to a new initialized PostgreSQL or MySQL
database. We are calling this command 'convertdb'. Since the SQL file from
one database vendor is not compatible with the SQL format of another vendor
the data is exported using JSON (a database independent format) format
using Django's DUMPDATA and LOADDATA commands. DUMPDATA and LOADDATA were
not made for database migrations, they were designed to create FIXTURE
files. FIXTURES are files that initialized a database with some data for
use in test suits. Our tests indicate that these commands are suitable for
export and import of data.

Since not all databases increment primary keys in the same way, the
ordering of records can be lost during the conversion and the import will
fail. To avoid this we are adding support to reference database objects
using natural keys. These allow referencing a database object by a field or
a collection of fields that are naturally unique, instead of using an
autogenerate primary key number. These natural keys are used only during
the database conversion and not for normal operation. Natural keys ensure
that all database object relationships remain intact after the conversion.

Creating natural keys is a slow process of analysis and introspection of
all the models defined in the Mayan apps. It is also a process that
involves a lot of testing and simulation. We've given this feature top
priority. Once this support is stable it will be included in the next
planned release, version 3.1 so that user can start enjoying the benefits
of the 3.x series without worrying about database migrations. Thank you.