Skip to content
This repository has been archived by the owner on Mar 28, 2021. It is now read-only.

Guide to migrating your SQLite to a MySQL like database

Jump to bottom
Jiří Smolík edited this page Mar 13, 2017 · 9 revisions

Eventually, Coldsweat will have its own uniform interface to migrate database content. Right now, however, we have to do this manually. Prerequisites:

  1. Install QColdsweat on your QNAP.
  2. Gain sFTP access to your QNAP.
  3. Make yourself familiar with QColdsweat's folder structure.

The below guide is made for UNIX/Linux like systems. The same process can be adapted for Windows though.

Step 1

If you want to convert the database outside of your QNAP (e.g. your PC), first retrieve <data-folder>/sqlite.db. Then save this python script as dump_for_mysql.py somewhere on the target device.

Step 2

Now you need to compile & install:

Use platform-specific package managers, if possible. If you want to convert the database on your QNAP, SSH into it and execute: opkg install python sqlite3-cli.

Step 3

Let's assume that:

  • The database and the script from step 1 are located in the same folder.
  • You have a command line open (or an active SSH session to your QNAP) and that its working directory is set to the folder from previous point.

This is how you convert your SQLite database into an SQL script:

sqlite3 sqlite.db .dump | python dump_for_mysql.py > dump.sql

The result dump.sql file is what we've been after.

Step 4

The result SQL script needs a little post-processing (the script is not exactly perfect):

  1. Add USE coldsweat; as the FIRST LINE of the script. It must work with the coldsweat database.
  2. Replace all occurrences of NOT NULL PRIMARY KEY with NOT NULL PRIMARY KEY AUTO_INCREMENT. Coldsweat doesn't generate its own record IDs.
  3. Replace every field-enclosing backtick (`) for a single quote (') in the first INSERT INTO statement for every table. This is a heavy blunder of the python script...
  4. Replace DROP TABLE IF EXISTS read; for DROP TABLE IF EXISTS `read`;. After all, read is a keyword for some databases and if you have a table named the same way, you have to escape it.

Now, your SQL script should, hopefully, be accepted by most databases. Make sure you have a MySQL-like database ready (follow this guide) and import the SQL script into it.

Clone this wiki locally