recommended solution to migrating SQLcipher

[JReally]

Is there a recommended solution to migrating SQLcipher Legacy=3 to Legacy=4

I tried using "Vacuum into " with a connection with Legacy = 3 and then using Legacy = 4
Something like:
VACUUM main into
QuotedStr('file:' + pDstDB +'?cipher=sqlcipher&legacy=4&key='+ pDstPwd)

It resulted in a new DB but neither Legacy 3 or 4 would open it.

Using the following works to do 3 to 3 or 4 to 4
VACUUM main into
QuotedStr('file:' + pDstDB +'?cipher=sqlcipher&key='+ pDstPwd)

Perhaps I missed something?

In the past I have just created a new 4 DB and copied over the data but thought the
"Vacuum into" could have worked (& be somewhat simpler)

Jon

[utelle]

Is there a recommended solution to migrating SQLcipher Legacy=3 to Legacy=4

The approach you chose, that is using the SQL command VACUUM INTO, is in principle the right one. However, there's a little trap lurking, and that's the database's page size.

I tried using "Vacuum into " with a connection with Legacy = 3 and then using Legacy = 4 Something like: VACUUM main into QuotedStr('file:' + pDstDB +'?cipher=sqlcipher&legacy=4&key='+ pDstPwd)

It resulted in a new DB but neither Legacy 3 or 4 would open it.

This is only surprising at first glance. At second glance it becomes clear that this has something to do with the database's page size. The default page size of a SQLCipher Legacy 3 database is 1024 bytes, while the default page size of a SQLCipher Legacy 4 database is 4096 bytes.

However, the command VACUUM INTO doesn't change the page size (unless you explicitly request a different page size).

Try to open the resulting database with the parameters ?cipher=sqlcipher&legacy=4&legacy_page_size=1024&key=..., and you should be able to access the database

Perhaps I missed something?

Yes. Actually, you have to explicitly request a page size of 4096 bytes for the destination database. Fortunately, the VACUUM INTO command supports different page sizes for the source and destination database.

To get a SQLCipher Legacy 4 database you have to issue a PRAGMA page_size= command first. Thus, the following commands should give you the expected result:

-- Open SQLCipher Legacy 3 database as usual

-- Request the desired page size for the destination database
PRAGMA page_size=4096;
-- Create database copy via VACUUM INTO
VACUUM INTO 'file:dest.db?cipher=sqlcipher&legacy=4&key=pswd';

In the past I have just created a new 4 DB and copied over the data but thought the "Vacuum into" could have worked (& be somewhat simpler)

Actually, VACUUM INTO does just what you did manually: it copies the database schema and the database content.

[JReally]

Great...
works perfectly. Thanks for the quick and detailed response.

Jon