tips:sqlite [Galileo Labs]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== sqlite ======

compile from sources into a python virtualenv (or change PREFIX to /usr/local) with spellfix extension
<code bash>
cd /tmp

if [ ! -d sqlite ]; then
    [ -f sqlite.tar.gz ] || wget https://www.sqlite.org/src/tarball/sqlite.tar.gz
    tar zxf sqlite.tar.gz
fi

cd sqlite
export CFLAGS="-DSQLITE_ENABLE_FTS3 \
    -DSQLITE_ENABLE_FTS3_PARENTHESIS \
    -DSQLITE_ENABLE_FTS4 \
    -DSQLITE_ENABLE_FTS5 \
    -DSQLITE_ENABLE_JSON1 \
    -DSQLITE_ENABLE_LOAD_EXTENSION \
    -DSQLITE_ENABLE_RTREE \
    -DSQLITE_ENABLE_STAT4 \
    -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT \
    -DSQLITE_SOUNDEX \
    -DSQLITE_TEMP_STORE=3 \
    -DSQLITE_USE_URI \
    -O2 \
    -fPIC"

export PREFIX="${VIRTUAL_ENV}"
LIBS="-lm" ./configure --disable-tcl --enable-shared --enable-tempstore=always --prefix="$PREFIX"
make -j4 && \
make install && \
gcc -shared -fPIC -Wall -O2 -I ./ ext/misc/spellfix.c -o ${PREFIX}/lib/spellfix.so

echo "Add 'export LD_LIBRARY_PATH=${VIRTUAL_ENV}/lib:$LD_LIBRARY_PATH' to your environment"

</code>

<file python test.py>
import sqlite3

db = sqlite3.connect(':memory:')
db.enable_load_extension(True)
db.load_extension('spellfix')                 # for Linux
db.enable_load_extension(False)
c = db.cursor()
c.execute('CREATE TABLE mytable (id integer, description text)')
c.execute('INSERT INTO mytable VALUES (1, "hello world, guys")')
c.execute('INSERT INTO mytable VALUES (2, "hello there everybody")')
c.execute('SELECT * FROM mytable WHERE editdist3(description, "hel o wrold guy") < 600')
print(c.fetchall())
</file>