The choice of using an encrypted database or not for an application is not easy as the operation at database level can’t be reverted. But actually you still can "desencrypt" an encrypted SQLite database and vice versa, later if you change your mind. The main principle is that a new database file has to be created and the content of the old one to be transferred into the new one. This requires some manual operations but nothing too costly.
Here is a short procedure, that of course is supported by the SQLite Sorcerer. I will take the case where you have an encrypted database that you want to desencrypt.
- Create an empty non encrpyted database, the one that will receive the content of the encrypted one.
- Launch the comparison process (only to view both database files during the operation).
- Open your encrypted database in the second visualizer using the password or the encryption key.
- Get its SQL code.
- Copy it to clipboard.
- Open the query Panel of the new database file, in the 1st visualizer.
- Paste the copied code
- Remove (copy them in clipboard) trigger creation statements
- Execute the batch query
- Return to step 7 for each trigger creation statement
You should now have the same structure in both files
- Now, for each table of the encrypted file (in the second visualizer), open the table.
- Show its data.
- Export data to a file (keep default options).
- Once all table data are exported, import all csv files in the corresponding tables of the empty new file.
And you should now have a filled non-encrypted copy of your original encrypted database file, with the same structure and containing the same data. Just test your new non encrypted database, of course.
I can streamline this process in a future version of SQLite Sorcerer. A dump creation feature would be great but quite touchy because of data types or a "transfer to" feature may be…
The new version of the SQLiteSorcerer has just been released.
- You can now filter data using keywords or regular expressions: very useful when you search data in the table
- You can import data from a CSV or a TXT file: a very important feature to populate a table from an external file or exchange data between 2 databases.
I still must add some screenshots in the application page.
I hope you will enjoy this new version (that is now signed). Check the version history for more details on the changes.
The SQLite Sorcerer, in its v1.2, now provides the ability to create and open encrypted databases. To reencrypt them also. Here is how the feature is addressed in the tool.
Continue reading »
I am proud to publish today the first public version of the SQLite Sorcerer, a new administration tool for SQLite databases, written in Flex 3 for Adobe AIR.
Continue reading »
UPDATE (8th of Apr 2009) : your new AIR admin tool for SQLite local databases is now available here !
There are already lots of good tools to manage SQLite databases (only a few developped in AIR) and I already use a good one (SQLite Administrator). But a few months ago, I had to update an AIR application and it was a real pain because of the change in the SQLite database. I was very lazy and I updated the installed database manually instead of developping a class that could perform the migration of the DB after the update of the application it-self. This was of course a very dirty way of working and I want to be more professional next time (even if the application is for the private use of my wife).
Then, in last December, I decided to start an actionscript class that could:
- compare 2 SQLite databases (the new version and the old one)
- generate the SQL statements to be executed for the migration of the installed database.
But 3 weeks ago, I felt that an AIR application that could compare SQLite db was interesting (this feature is not that common in the market of SQLite admin tools) but if the user have to create/edit his databases using another tool, it was not so user-friendly and convenient. As you can understand, I have been victim of gold-plating and now the app can and will do more than only database comparison…
Continue reading »