If you have a database created by SQLite, you can migrate it to a Berkeley DB database for use with the BDB SQL interface. For production applications, you should do this only when your application is shutdown.
To migrate your data from SQLite to a Berkeley DB database:
Make sure your application is shutdown.
Open the SQLite database within the sqlite3 shell.
Execute the .output command to
specify the location where you want to dump data.
Dump the database using the SQLite
.dump command.
Close the sqlite3 shell and open the Berkeley DB dbsql shell. Note that if you build the BDB SQL interface with the compatibility option, you can alternatively use Berkeley DB's sqlite3 utility.
Load the dumped data using the
.read command.
Note that you can migrate in the reverse direction as well.
Dump the Berkeley DB database by calling .dump
from within the dbsql shell, and load it into SQLite by calling
.read from within SQLite's
sqlite3 shell.
You can migrate data between SQLite and Berkeley DB that uses the UTF-8 character encoding.
The following data types can be migrated between SQLite and Berkeley DB:
CHAR, TEXT , VARCHAR, NVARCHAR, STRING
REAL, DOUBLE, FLOAT
INTEGER, BOOLEAN, BIG INTEGER, NUMBER
NUMERIC
BLOB, CLOB
NULL, NOT NULL
COLLATE BINARY, COLLATE RTRIM, COLLATE NOCASE
DATETIME, CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP
The following schema can be migrated between SQLite and Berkeley DB:
PRAGMA writable_schema=ON/OFF
PRAGMA foreign_keys=ON/OFF
PRAGMA cache_size
CREATE TABLE
PRIMARY KEY
UNIQUE
CONFLICT IGNORE, FAIL, REPLACE, ABORT, ROOLBACK
REFERENCE ON ... CASECADE, ON ... NO ACTION, DEFERRABLE INITIALLY DEFERRED, and so forth.
AUTOINCREMENT
Static DEFAULT value, dynamic DEFAULT value
Functions such as datetime, typeof, and so forth.
ASC, DESC
HIDDEN
CHECK
CREATE INDEX, UNIQUE INDEX
CREATE VIEW
SELECT statement, ANALYZE
JOIN
UNION
CREATE TRIGGER AFTER/BEFORE BEGIN
CREATE VIRTUAL TABLE USING
INSERT