SQLite-Ivo
From Cheatsheet
Jump to navigationJump to search
This page was written and contributed by Ivo Palli.
General
An SQL solution that puts data into local files, without a server/client setup.
The source files are merged into a single C file called the amalgamation to easy embedding SQLite into your own program.
There are a number of optional components you can enable via defines:
- FTS3 and FTS5 - Full Text Searching module
- RTREE - An R-Tree is a special index that is designed for doing range queries
- DBSTAT - A read-only table with info about the amount of disk space used to store the content of an SQLite database
- JSON1 - Functions that are useful for managing JSON content stored in an SQLite database
- RBU - Resumable Bulk Update. Efficient functions useful on low-power devices
Datatypes
- NULL - The value is a NULL value.
- INTEGER - The value is a signed integer, stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
- REAL - The value is a floating point value, stored as an 8-byte IEEE floating point number.
- TEXT - The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
- BLOB - The value is a blob of data, stored exactly as it was input.
More here: https://www.sqlite.org/datatype3.html
Timestamps
There is no specialized data type for timestamp. You can store stuff as an integer with epoch.
A good default value for a timestamp field is:
ts integer(4) default (cast(strftime('%s','now') as int))
Installation
RHEL
yum install sqlite sqlite3 --version
From source
wget https://www.sqlite.org/2022/sqlite-amalgamation-3390400.zip unzip sqlite-amalgamation-*.zip cd sqlite-amalgamation-*/ musl-gcc -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_DBSTAT_VTAB -Wall -g -O2 -c sqlite3.c ar rcs libsqlite3.a sqlite3.o musl-gcc -static -Wall -O2 -o shell shell.c -L. -lsqlite3 rlwrap ./shell .help .quit
- https://www.sqlite.org/download.html
- https://stackoverflow.com/questions/2734719/how-to-compile-a-static-library-in-linux
Usage
Create a table
# https://www.sqlite.org/lang_createtable.html cat << 'EOF' | sqlite3 lookup.db CREATE TABLE IF NOT EXISTS projects(id TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL, added TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL); .quit EOF
Insert a row
# https://www.sqlite.org/lang_insert.html
cat << 'EOF' | sqlite3 lookup.db
INSERT INTO projects(id, name) VALUES('hello!', 'world');
.quit
EOF
Show a database table
# List tables within a database sqlite3 scripting3.db ".header off" ".mode tabs" ".tables" # Describe schema sqlite3 lookup.db ".header off" ".mode tabs" ".schema usage"
Select
# Note that tables automatically have a 'rowid' field that auto-increments # https://www.sqlite.org/lang_select.html sqlite3 lookup.db ".mode tabs" "SELECT rowid,* FROM projects"
Join
# JOINing sqlite3 lookup.db ".mode tabs" ".headers on" "SELECT * FROM usage u JOIN servers s ON u.server_id = s.id ORDER BY ts DESC LIMIT 1;"
Time
# Converting timestamp to epoch
SELECT strftime('%s', usage.ts) FROM usage;
# https://www.sqlite.org/lang_datefunc.html
select DATETIME('now'); -- UTC time
select DATETIME('now', 'localtime'); -- Local time
select DATETIME('now', 'localtime', '-5 hours'); -- Local time, but 5 hours ago
select DATETIME(0, 'unixepoch'); -- 1970-01-01 00:00:00
select strftime('%s'); -- Current time in epoch UTC
select strftime('%s', '2022-07-01 00:00'); -- Converts to epoch UTC
select strftime('%s', '2022-07-01 00:00', 'localtime'); -- Converts to epoch local time
select strftime('%s', '2022-07-01 00:00', 'utc'); -- Date is local time and you want the epoch in UTC
- When your timestamp field is already in seconds since epoch:
db 'select *,strftime("%Y-%m-%d %H:%M:%S", ts, "unixepoch") from usage where ts > strftime("%s", "now", "-7 minutes");' | csv </syntaxhighlight>
Timestamps
-- Set up a table
CREATE TABLE IF NOT EXISTS `config`(`key` TEXT NOT NULL UNIQUE, `value` TEXT, `ts` integer(4) default (cast(strftime('%s','now') as int)));
-- Insert a value, this sets the timestamp. This will also re-set the timestamp if the key/value already exists
INSERT OR REPLACE INTO `config`(`key`,`value`) VALUES('naam','ivo');
-- Query the value. Repeat this after a while to see no value returning
SELECT `value` FROM `config` WHERE `key` = 'naam' AND `ts` > strftime('%s', datetime('now', '-10 seconds'));
-- Update the timestamp
UPDATE `config` SET `ts` = strftime('%s', 'now') WHERE `key` = 'naam';
-- Repeat the query again after updating the timestamp
Select with multiple clauses
# Multiple clauses and last X hours
# https://stackoverflow.com/q/34283690/1007204
SELECT * from hypervisors WHERE hypervisor_hostname = 'node001' AND state = 'up' AND ts >= DATETIME('now','-3 hours')
Truncate a table
# Truncate a table DELETE FROM users_new;
Select into JSON format
# Getting info from OpenStack
db '.mode json' 'select strftime("%Y-%m-%d %H:%M:%S", u.ts, "unixepoch") as clock, s.name as Server, u.vcpu as CPU, u.memory as Memory, u.disk as Disk, p.name as Project from usage u join servers s on u.server_id = s.id join projects p on s.project_id = p.id where u.ts > strftime("%s", "now", "-7 minutes");' | jq .
Dump a database
# Dumping a database sqlite3 -batch test.db ".dump"
Wait for lock instead of immediate error
# Wait for database unlock instead of exiting immediately sqlite3 -batch imdb.db "PRAGMA busy_timeout = 5000; select id from imdb" | wc -l # Note, this will echo '5000' to stdout from the PRAGMA sqlite3 lookup.db ".output /dev/null" "PRAGMA busy_timeout = 5000" ".output stdout" ".schema" # This will redirect the PRAGMA to /dev/null echo ".schema" | sqlite3 lookup.db -cmd ".output /dev/null" -cmd "PRAGMA busy_timeout = 5000" -cmd ".output stdout" # Execute the PRAGMA while still reading commands from stdin afterwards
Profiling queries
.eqp on -- Turns on EXPLAIN for all queries .timer on -- Keeps track of real, user and system time taken SELECT .... -- Do some complex query
Adding a column to a table
ALTER TABLE servers ADD tags TEXT; ALTER TABLE servers ADD flex INTEGER;
Remove a column from a table
ALTER TABLE servers DROP flex;
Inserting with values from another select
CREATE TABLE IF NOT EXISTS flex(server_id TEXT PRIMARY KEY NOT NULL, ts integer(4) default (cast(strftime('%s','now') as int)));
INSERT OR IGNORE INTO flex (server_id) SELECT id from servers WHERE tags LIKE '%"flex":"true"%';
Printing true or false depending if a field has a value
SELECT s.name,CASE WHEN f.server_id IS NULL THEN 'false' ELSE 'true' END AS flex FROM servers s LEFT JOIN flex f ON s.id = f.server_id WHERE s.project_id = '795d39ce5a5a42539463ccdcf6648b8c' AND s.recent = 1;
Converting one type to another
Here we are converting regular timestamps to seconds since epoch
-- Get the create statement for the original table
.schema users
-- CREATE TABLE users(id TEXT PRIMARY KEY NOT NULL, recent INTEGER NOT NULL, name TEXT NOT NULL, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL);
-- Start a transaction
PRAGMA foreign_keys=off;
BEGIN TRANSACTION;
-- Alter the column
CREATE TABLE users_new(id TEXT PRIMARY KEY NOT NULL, recent INTEGER NOT NULL, name TEXT NOT NULL, ts integer(4) default (cast(strftime('%s','now') as int)));
-- Copy the data while converting
INSERT INTO users_new SELECT id,recent,name,strftime('%s', ts) FROM users;
-- Move table
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;
-- Finish up
COMMIT;
PRAGMA foreign_keys=on;
You can also copy a table into another database file via the ATTACH command.
REPLACE blanks values that you don't specify
rlwrap sqlite3 test.db SQLite version 3.39.4 2022-09-29 15:55:41 Enter ".help" for usage hints. sqlite> create table xx (a1 integer PRIMARY KEY, a2 integer, a3 integer); sqlite> .schema CREATE TABLE xx (a1 integer PRIMARY KEY, a2 integer, a3 integer); sqlite> insert into xx(a1,a2,a3) values(1,2,3),(4,5,6),(7,8,9); sqlite> select * from xx; 1|2|3 4|5|6 7|8|9 sqlite> replace into xx (a1,a2) values(1, 666); sqlite> select * from xx; 1|666| <-- last value is blanked 4|5|6 7|8|9
Code example
/*
musl-gcc -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_DBSTAT_VTAB -Wall -O2 -c sqlite3.c -lpthread -ldl # Create object file
musl-gcc -Wall -O2 -o example example.c sqlite3.o
*/
#include "sqlite3.h"
#include <stdio.h>
#include "debug.c"
sqlite3 *db = NULL;
void close_db()
{
sqlite3_close(db);
}
int main(void)
{
sqlite3_stmt *res;
char *database_file = "example.db";
int rc;
debug_options.to_stdout = 1;
debug("Using SQLite %s", sqlite3_libversion());
atexit(close_db);
if(sqlite3_open(database_file, &db) != SQLITE_OK)
die("Cannot open database %s: %s", database_file, sqlite3_errmsg(db));
if(sqlite3_prepare_v2(db, "SELECT SQLITE_VERSION()", -1, &res, 0) != SQLITE_OK)
die("Failed to fetch data: %s", sqlite3_errmsg(db));
rc = sqlite3_step(res);
if(rc == SQLITE_ROW)
printf("%s\n", sqlite3_column_text(res, 0));
sqlite3_finalize(res);
return 0;
}
// EOF
