[odb-users] Help needed for data migration with SQLite

[Alain-Serge Nagni]

Hi guys,
  We have a system that runs on 2 different environments. We are using PostgreSql  and SQLite databases. By following the documentation we added few data members to our class (CMyFilter) and we are able to perform the migration for the PostgreSql Database. This is the commands that we ran on the server where the PostgreSql database is : 
1)  psql —user=odb_test  —dbname=theOdbTestServer  -f  CMyfilter-002-pre.sql
2)  psql —user=odb_test  —dbname=theOdbTestServer  -f  CMyfilter-002-post.sql



We are trying now to perform a migration with the SQLite database on the local PCs with the same class (it’s a library) but we are not sure how to proceed since we do not have the CMyFilter-002-pre.sql and CMyFilter-002-post.sql . We are using the example in the documentation but obviously we are doing something wrong. When we execute the schema_catalog:: migrate_schema_pre we will have a "CMyFilter table exist” error.  This is the code:


            std::string strDatabaseName("theOdbTestServer.sqlite3");

            auto pDB = std::shared_ptr<odb::database>( new odb::sqlite::database ( strDatabaseName, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE));

            schema_version schemaVesion(pDB->schema_version ());
            schema_version baseVesrion (schema_catalog::base_version(*pDB.get()));
            schema_version currentVersion (schema_catalog::current_version(*pDB.get()));

            std::cout << "schemaVesion: " << schemaVesion << " - baseVesrion: " << baseVesrion << " - currentVersion: " << currentVersion << std::endl;

            if (schemaVesion == 0)
            {
                // No schema in the database. Create the schema and
                // initialize the database.
                //
                transaction theTransaction(pDB->begin ());
                	schema_catalog::create_schema(*pDB.get());

                	// Populate the database with initial data, if any.

                theTransaction.commit ();
            }
            else if(schemaVesion < currentVersion)
            {
                // Old schema (and data) in the database, migrate them.
                //

                if (schemaVesion < baseVesrion)
                {
                    // Error: migration from this version is no longer supported.
                    std::cout << "Error: migration from this version is no longer supported." << std::endl;
                }

                for (schemaVesion = schema_catalog::next_version (*pDB.get(), schemaVesion);
                     schemaVesion <= currentVersion;
                     schemaVesion = schema_catalog::next_version (*pDB.get(), schemaVesion))
                {
                    try
                    {
                        transaction theTransaction(m_pDB->begin ());
                        	schema_catalog::migrate_schema_pre (*pDB.get(), schemaVesion);

                        	// Data migration goes here.

                        	schema_catalog::migrate_schema_post (*pDB.get(), schemaVesion);
                        theTransaction.commit ();

                    }
                    catch (const odb::exception& e)
                    {
                        cerr << e.what () << endl;
                    }
                }
            }
            else if (schemaVesion > currentVersion)
            {
                // Error: old application trying to access new database.
                std::cout << "Error: old application trying to access new database." << std::endl;
            }





We attached the CMyFilter-002-pre.sql and CMyFilter-002-post.sql  files, the CMyFilterPragmas.hpp file, and the CMyFilter.xml file to this email in case you need it. It looks like with the SQLite database, the migration can not be performed if the database is open (Which makes sense actually). If that is the case, how this can be performed.


Thank you for your help,
Alain-Serge