PRAGMA user_version when converting from 2.x to 3.0.1

[Brendan Duddridge]

Hi,

I've been working on migrating my database from 2.x to 3.0.1 format.

The problem I'm having now is the exported database does not have the user_version in it after the sqlcipher_export. And the problem is that I also cannot read the user_version before I do the migration because the 3.0.1 library won't read that information.

When I call:

NSInteger databaseVersion = [db intForQuery:@"pragma user_version;"];

I get the following error:

Unknown error calling sqlite3_step (26: file is encrypted or is not a database) rs

I'm using fmdb to connect to the database, hence the intForQuery call.

Is there maybe someway to get sqlcipher_export to set the user_version to be the same as the original database after the conversion?

Thanks,

Brendan

[Brendan Duddridge]

To follow up on this, I am using the pragma user_version to determine the current version of the database schema. After I do the conversion to 3.0.1 format, I may need to further execute some SQL statements in order to bring the database schema up to the latest version.

When I ship app updates I have the latest database version included in the UserInfo plist file. I use that information to determine the difference between the user's database version and the app's database version. That tells me exactly which SQL statements to execute to bring the user's database up to the current schema. So I can't just set the user_version to be the latest version after I migrate to 3.0.1 because I may have missed some schema migrations that needed to be applied.

Is there any solution to getting the user_version from the existing 2.x database format before the conversion? Or am I SOL? :-)

Thanks,

Brendan

[Nick Parker]

Hello Brendan,

I have replied inline below:

On 3/1/14, 12:56 PM, Brendan Duddridge wrote:
> To follow up on this, I am using the pragma user_version to determine
> the current version of the database schema. After I do the conversion to
> 3.0.1 format, I may need to further execute some SQL statements in order
> to bring the database schema up to the latest version.
>
> When I ship app updates I have the latest database version included in
> the UserInfo plist file. I use that information to determine the
> difference between the user's database version and the app's database
> version. That tells me exactly which SQL statements to execute to bring
> the user's database up to the current schema. So I can't just set the
> user_version to be the latest version after I migrate to 3.0.1 because I
> may have missed some schema migrations that needed to be applied.
>
> Is there any solution to getting the user_version from the existing 2.x
> database format before the conversion? Or am I SOL? :-)

The 3.x version is fully capable of operating on an older SQLCipher
database, however it will require that you specify the settings used
when creating your database originally in order for the decryption to be
successful. With that said, you can specify your distinct settings via
PRAGMA commands and then read PRAGMA user_version.

The sqlcipher_export command does not migrate the user_version of a
database because it is not limited to populating a new non-existent
database with the contents of an attached database. For example, you
could attach a database with a partial schema including a user_version
and export additional data to the database. In this scenario we would
not want to overwrite the user_version. When you desire to fully
replicate a database, capturing the user_version before a
sqlcipher_export and manually setting the user_version on the new
database is the recommended approach.

>
> Thanks,
>
> Brendan
>
> On Saturday, March 1, 2014 2:01:17 AM UTC-7, Brendan Duddridge wrote:
>
> Hi,
>
> I've been working on migrating my database from 2.x to 3.0.1 format.
>
> The problem I'm having now is the exported database does not have
> the user_version in it after the sqlcipher_export. And the problem
> is that I also cannot read the user_version before I do the
> migration because the 3.0.1 library won't read that information.
>
> When I call:
>
> NSInteger databaseVersion = [db intForQuery:@"pragma user_version;"];
>
>
> I get the following error:
>
>

> *Unknown error calling sqlite3_step (26: file is encrypted or is not
> a database) rs*

>
>
> I'm using fmdb to connect to the database, hence the intForQuery call.
>
>
>
> Is there maybe someway to get sqlcipher_export to set the
> user_version to be the same as the original database after the
> conversion?
>
>
> Thanks,
>
>
> Brendan
>

> --
>
> ---
> You received this message because you are subscribed to the Google
> Groups "SQLCipher Users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlcipher+...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.

--
Nick Parker

[Brendan Duddridge]

Hello Nick,

Oh that's great! I didn't know I could read the old format database file using 3.0.x. I thought it had to be migrated in order to read it. That will make it easier for me because then I can do a more orderly migration. That is, still read and operate under the old format, but if the user de-crypts and then re-encrypts then I can use the new format. Does that sound right?

Thanks!

Brendan

On Saturday, March 1, 2014 2:01:17 AM UTC-7, Brendan Duddridge wrote:

[Nick Parker]

Hello Brendan,

Yes, the desired behavior would be up to you, but from a functionality
perspective relative to SQLCipher, you can certainly still access older
databases with the new libraries, you just have to properly configure
the settings on the library based on the database you are attempting to
access.

On 3/3/14, 2:43 PM, Brendan Duddridge wrote:
> Hello Nick,
>
> Oh that's great! I didn't know I could read the old format database file
> using 3.0.x. I thought it had to be migrated in order to read it. That
> will make it easier for me because then I can do a more orderly
> migration. That is, still read and operate under the old format, but if
> the user de-crypts and then re-encrypts then I can use the new format.
> Does that sound right?
>
> Thanks!
>
> Brendan
>
> On Saturday, March 1, 2014 2:01:17 AM UTC-7, Brendan Duddridge wrote:
>
> Hi,
>
> I've been working on migrating my database from 2.x to 3.0.1 format.
>
> The problem I'm having now is the exported database does not have
> the user_version in it after the sqlcipher_export. And the problem
> is that I also cannot read the user_version before I do the
> migration because the 3.0.1 library won't read that information.
>
> When I call:
>
> NSInteger databaseVersion = [db intForQuery:@"pragma user_version;"];
>
>
> I get the following error:
>
>

> *Unknown error calling sqlite3_step (26: file is encrypted or is not
> a database) rs*

>
>
> I'm using fmdb to connect to the database, hence the intForQuery call.
>
>
>
> Is there maybe someway to get sqlcipher_export to set the
> user_version to be the same as the original database after the
> conversion?
>
>
> Thanks,
>
>
> Brendan
>

> --
>
> ---
> You received this message because you are subscribed to the Google
> Groups "SQLCipher Users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlcipher+...@googlegroups.com

> <mailto:sqlcipher+...@googlegroups.com>.

[Brendan Duddridge]

Hi Nick,

I got it working great now with your help.

Thanks!

-- 
Brendan Duddridge
Sent with Airmail