Sqlite

Quick Usage

Run sqlite3. At the prompt, you can open (or create a new) database with .open. Use .tables to show tables.

You can then begin entering SQL queries. For scripting, you can also pipe these queries to the sqlite3 program.

Create a table

create table bandwidth (
	mac character(12),
	time datetime,
	tx_bytes unsigned int,
	rx_bytes unsigned int
);

Create a composite key

CREATE UNIQUE INDEX pk_index ON "bandwidth" ("mac", "time");

Find the current hour as a timestamp, which you can insert as a datetime field?

select strftime('%s', 'now') - (strftime('%s','now')%3600);

To insert a record:

insert into bandwidth ('mac', 'time', 'tx_bytes', 'rx_bytes') 
VALUES ('000c2958db2f', strftime('%s', 'now') - (strftime('%s','now')%3600), 100, 100);

Scripting

There are two ways to pipe queries for execution. Either pipe it directly with | or by piping a file into stdin with < filename.

# echo "INSERT ..." | sqlite3 database.db
# sqlite3 database.db < queries.sql

See Also

• CLI: https://sqlite.org/cli.html
• Types: https://www.tutorialspoint.com/sqlite/sqlite_data_types.htm