simplex-chat/scripts/db/README.md

Transfer data from SQLite to Postgres database

1. * Decrypt SQLite database if it is encrypted.

   sqlcipher encrypted_simplex_v1_agent.db
   

   PRAGMA key = 'password';
   ATTACH DATABASE 'simplex_v1_agent.db' AS plaintext KEY '';
   SELECT sqlcipher_export('plaintext');
   DETACH DATABASE plaintext;
   

   Repeat for simplex_v1_chat.db.

2. Prepare Postgres database.

   • Create Postgres database. In shell:

     createdb -O simplex simplex_v1
     

     Or via query.

   • Build simplex-chat executable with client_postgres flag and run it to initialize new chat database.

     This should create simplex_v1_agent_schema and simplex_v1_chat_schema schemas in simplex_v1 database, with migrations tables populated. Some tables would have initialization data - it will be truncated via pgloader command in next step.

3. Load data from decrypted SQLite databases to Postgres database via pgloader.

   Install pgloader and add it to PATH. Run in shell (substitute paths):

   SQLITE_DBPATH='simplex_v1_agent.db' POSTGRES_CONN='postgres://simplex@/simplex_v1' POSTGRES_SCHEMA='simplex_v1_agent_schema' pgloader --on-error-stop sqlite.load
   
   SQLITE_DBPATH='simplex_v1_chat.db' POSTGRES_CONN='postgres://simplex@/simplex_v1' POSTGRES_SCHEMA='simplex_v1_chat_schema' pgloader --on-error-stop sqlite.load
   

4. Update sequences for Postgres tables.

   DO $$
   DECLARE
      rec RECORD;
   BEGIN
      EXECUTE 'SET SEARCH_PATH TO simplex_v1_agent_schema';
   
      FOR rec IN
         SELECT
            table_name,
            column_name,
            pg_get_serial_sequence(table_name, column_name) AS seq_name
         FROM
            information_schema.columns
         WHERE
            table_schema = 'simplex_v1_agent_schema'
            AND identity_generation = 'ALWAYS'
      LOOP
         EXECUTE format(
            'SELECT setval(%L, (SELECT MAX(%I) FROM %I))',
            rec.seq_name, rec.column_name, rec.table_name
         );
      END LOOP;
   END $$;
   

   Repeat for simplex_v1_chat_schema.

5. * Compare number of rows between Postgres and SQLite tables.

   To check number of rows for all tables in Postgres database schema run:

   WITH tbl AS (
      SELECT table_schema, table_name
      FROM information_schema.Tables
      WHERE table_name NOT LIKE 'pg_%'
        AND table_schema IN ('simplex_v1_agent_schema')
   )
   SELECT
      table_schema AS schema_name,
      table_name,
      (xpath('/row/c/text()', query_to_xml(
         format('SELECT count(*) AS c FROM %I.%I', table_schema, table_name), false, true, ''
      )))[1]::text::int AS records_count
   FROM tbl
   ORDER BY records_count DESC;
   

   Repeat for simplex_v1_chat_schema.

6. Build and run desktop app with Postgres backend.

   Run in shell (paths are from project root):

   ./scripts/desktop/build-lib-mac.sh arm64 postgres
   
   ./gradlew runDistributable -Pdatabase.backend=postgres
   # or
   ./gradlew packageDmg -Pdatabase.backend=postgres