I want SQLite3 with FTS (Full-Text Search) and AES crypto support. It doesn't come handed to me on a silver platter, but I can make it happen nonetheless... and I will.
Note that both "SQLite Encryption Extension" and "SQLCipher" exist. SQLCipher seems to be the popular choice (and it dominates SEO, for sure).
Building SQLCipher Standalone OS XSince it's already been added to brew, it's super simple:
brew options sqlcipher
brew install sqlcipher --with-fts
You'll need to grab build tools (gcc et al) as well as openssl.
sudo apt-get install -y build-essential
# I found the correct package with 'apt-cache search openssl2'
sudo apt-get install -y openssl libssl-dev
Then you can clone the sqlcipher and get to work
git clone https://github.com/sqlcipher/sqlcipher.git
pushd sqlcipher
./configure --help
# CFLAGS will turn on encryption, default temp_store to memory,
# (file usage optional), enable full-text search, link to libcrypto
./configure CFLAGS="-DSQLITE_HAS_CODEC -DSQLITE_TEMP_STORE=2 -DSQLITE_ENABLE_FTS3 -lcrypto"
Before you actually compile, you'll want to make sure that you're taking full advantage of the CPUs.
This is a very time consuming process, so if you have additional CPU cores (four on the RPi 2, eight on a macbook) you definitely want to be running all of them at full capacity.
On linux nproc command will show you the number of CPUs you have.
On OS X use sysctl -n hw.physicalcpu (or sysctl -n hw.ncpu to account for hyperthreading).
make clean
NUMCPUS=$(nproc 2>/dev/null || sysctl -n hw.ncpu)
time make -j $NUMCPUS
I was very impressed with the speed on the RPi 2:
real 2m36.831s
user 2m35.100s
sys 0m3.290s
The B+ wasn't too bad either:
real 5m45.961s
user 5m34.060s
sys 0m6.150s
Due to the limited RAM on the B, it took well over 10 minutes, maybe over 20.
Finish the installation
sudo chown -R $(whoami):$(whoami) /usr/local
make install
sudo ldconfig
IMPORTANT: Don't forget sudo ldconfig or otherwise the system will have trouble finding libsqlcipher.so
Create a test file test-sqlcipher-fts.sql with the following contents:
-- sqlcipher test.sqlcipher < test-sqlcipher-fts.sql
-- this will generate a 256-bit key from a passphrase via PBKDF2
PRAGMA KEY = 'secret';
-- to supply a 128- or 256-bit key (in hex) on your own, do like so
-- PRAGMA key = "x'2DD29CA851E7B56E4697B0E1F0850729'";
-- PRAGMA key = "x'2DD29CA851E7B56E4697B0E1F08507293D761A05CE4D1B628663F411A8086D99'";
PRAGMA CIPHER = 'aes-128-cbc';
CREATE TABLE messages(id INTEGER, user VARCHAR, msg TEXT);
CREATE VIRTUAL TABLE messages_fts USING FTS4(id INTEGER, msg TEXT);
INSERT INTO messages(id, user, msg)
VALUES (1, 'coolaj86', 'this is test message number one');
INSERT INTO messages(id, user, msg)
VALUES (2, 'ajthedj', 'this is test message number two');
INSERT INTO messages(id, user, msg)
VALUES (3, 'coolaj86', 'this is test message number three');
INSERT INTO messages_fts SELECT id, msg FROM messages;
SELECT 'SEARCH one';
SELECT messages.user, messages.msg
FROM messages INNER JOIN messages_fts ON messages.id = messages_fts.id
WHERE messages_fts.msg MATCH 'one';
SELECT 'SEARCH two';
SELECT messages.user, messages.msg
FROM messages INNER JOIN messages_fts ON messages.id = messages_fts.id
WHERE messages_fts.msg MATCH 'two';
SELECT 'SEARCH message';
SELECT messages.user, messages.msg
FROM messages INNER JOIN messages_fts ON messages.id = messages_fts.id
WHERE messages_fts.msg MATCH 'message';
And then run it like so:
sqlcipher test.sqlcipher < test-sqlcipher-fts.sql
If you don't get any errors, you're good to go.
Also, you may want to double check that you can't access the database with the regular sqlite3:
sqlite3 test.sqlcipher
You should see a message that the database is file is encrypted or is not a database.
Since node.js is old and out-of-sync with v8, I'm just going to demo with io.js as that is more likely to work in the future when io.js is merged back into node.js.
Note: I would not expect this to work in node < 0.12.x.
export LDFLAGS="-L/usr/local/lib"
export CPPFLAGS="-I/usr/local/include -I/usr/local/include/sqlcipher"
export CXXFLAGS="-I/usr/local/include -I/usr/local/include/sqlcipher"
npm install sqlite3 --build-from-source --sqlite_libname=sqlcipher --sqlite=/usr/local --verbose
For reference, I'm on io.js@2.3.1 and sqlite3@3.0.8.
Here's the JavaScript version of the standalone test above:
test-sqlcipher-fts.js:
// node test-sqlcipher-fts.js
'use strict';
var sqlite3 = require('sqlite3');
var db = new sqlite3.Database('./test.sqlcipher');
db.serialize(function() {
var stmt
, messages
;
db.run("PRAGMA KEY = 'secret'");
// db.run("PRAGMA key = \"x'2DD29CA851E7B56E4697B0E1F08507293D761A05CE4D1B628663F411A8086D99'\"");
db.run("PRAGMA CIPHER = 'aes-128-cbc'");
db.run("CREATE TABLE messages(id INTEGER, user VARCHAR, msg TEXT)");
db.run("CREATE VIRTUAL TABLE messages_fts USING FTS4(user VARCHAR, msg TEXT)");
stmt = db.prepare("INSERT INTO messages(id, user, msg) VALUES (?, ?, ?)");
messages = [
[1, 'coolaj86', 'this is test message number one']
, [2, 'ajthedj', 'this is test message number two']
, [3, 'coolaj86', 'this is test message number three']
];
messages.forEach(function (msg) {
stmt.run(msg);
});
stmt.finalize();
db.run("INSERT INTO messages_fts SELECT user, msg FROM messages");
db.get("SELECT * FROM messages INNER JOIN messages_fts ON messages.user = messages_fts.user WHERE messages_fts.msg MATCH 'one'", function (err, data) {
if (err) {
console.error(err);
return;
}
console.log(data);
});
db.all("SELECT * FROM messages INNER JOIN messages_fts ON messages.user = messages_fts.user WHERE messages_fts.msg MATCH 'two'", function (err, data) {
if (err) {
console.error(err);
return;
}
console.log(data);
});
db.each("SELECT * FROM messages INNER JOIN messages_fts ON messages.user = messages_fts.user WHERE messages_fts.msg MATCH 'message'", function (err, data) {
if (err) {
console.error(err);
return;
}
console.log(data);
});
});
And here's how you run it:
node test-sqlcipher-fts.js
Solution: Explicitly include -I/usr/local/include/sqlcipher, not just -I/usr/local/include
export CPPFLAGS="-I/usr/local/include -I/usr/local/include/sqlcipher"
export CXXFLAGS="-I/usr/local/include -I/usr/local/include/sqlcipher"
> sqlite3@3.0.8 install /home/pi/node_modules/sqlite3
> node-pre-gyp install --fallback-to-build
make: Entering directory '/home/pi/node_modules/sqlite3/build'
CXX(target) Release/obj.target/node_sqlite3/src/database.o
In file included from ../src/database.cc:5:0:
../src/database.h:10:21: fatal error: sqlite3.h: No such file or directory
compilation terminated.
node_sqlite3.target.mk:93: recipe for target 'Release/obj.target/node_sqlite3/src/database.o' failed
make: *** [Release/obj.target/node_sqlite3/src/database.o] Error 1
make: Leaving directory '/home/pi/node_modules/sqlite3/build'
Solution: sudo ldconfig
module.js:353
Module._extensions[extension](this, filename);
^
Error: libsqlcipher.so.0: cannot open shared object file: No such file or directory
at Error (native)
at Module.load (module.js:353:32)
at Function.Module._load (module.js:308:12)
at Module.require (module.js:363:17)
at require (module.js:382:17)
at Object.<anonymous> (/home/pi/node_modules/sqlite3/lib/sqlite3.js:4:15)
at Module._compile (module.js:428:26)
at Object.Module._extensions..js (module.js:446:10)
at Module.load (module.js:353:32)
at Function.Module._load (module.js:308:12)
By AJ ONeal
Thanks! It's really motivating to know that people like you are benefiting from what I'm doing and want more of it. :)
Did I make your day?
(you can learn about the bigger picture I'm working towards on my patreon page )
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4