SQLite

Basics

SQLite natively supports the following types: NULL, INTEGER, REAL, TEXT, BLOB. Additinally date works as well

Date and Time

Use of CURRENT_TIMESTAMP variable

UPDATE outbox SET date_sent = CURRENT_TIMESTAMP WHERE date_sent IS NULL

Date select

DELETE FROM outbox WHERE date_created < date('now', '-1 month')

VACUUM: Reduce Filesize after DELETE

VACUUM

can also be run via sqlite3 command line tool

sqlite3 grafana.db 'VACUUM;'

Using Python

see [1]

import sqlite3

# connect to DB
# con = sqlite3.connect('database_file.db')
con = sqlite3.connect(":memory:")
# con.row_factory = sqlite3.Row  # allows for access via row["name"]
cur = con.cursor()
 
# create table
cur.execute(
CREATE TABLE points
   (date date, lat real, lon real, source text, name text)
)

# ALTER TABLE
cur.execute("ALTER TABLE points ADD date_registered date")

# INSERT
sql = "INSERT INTO points(date) VALUES (?)"
cur.execute(sql, (datetime.date.today(),))  # here the "," is required if only one entry in tuple

sql = "INSERT INTO points(date, lat, lon) VALUES (?,?,?)"
cur.execute(sql, (myDate, myLat, myLon))
con.commit()

# UPDATE
sql = "UPDATE points SET lat = ? WHERE hash = ?"
cur.execute(sql, (myLat, h))
con.commit()

# SELECT 1 row 
sql = "SELECT date FROM points WHERE hash = ? LIMIT 1"
row = cur.execute(sql, (h,)).fetchone()

# SELECT multiple rows
sql = "SELECT date, lat, lon FROM points ORDER BY email"
for row in cur.execute(sql):
    print (row[0])
    print (row["date"]) # if "con.row_factory = sqlite3.Row" is used

cur.close()
con.close()

Using PHP

$PATH = "/var/www/virtual/entorb/mail-daemon/outbox.db";
$db = new SQLite3($PATH);
$db->busyTimeout(10000);
$db->exec('BEGIN');
$stmt = $db->prepare('UPDATE outbox SET date_sent = CURRENT_TIMESTAMP WHERE date_sent IS NULL AND date_created = :date_created AND send_to = :send_to AND subject = :subject');
$stmt->bindValue(':date_created', $res['date_created']);
$stmt->bindValue(':send_to', $res['send_to']);
$stmt->bindValue(':subject', $res['subject']);
$result = $stmt->execute();
$db->exec('COMMIT');
$db->close();

Using Perl

use lib ( '/var/www/virtual/entorb/perl5/lib/perl5' );
my $PATH = "/var/www/virtual/entorb/mail-daemon/outbox.db";
use DBI;
my $dbh = DBI->connect( "dbi:SQLite:dbname=$PATH", "", "" );
$dbh->{ AutoCommit } = 0;
my $sth = $dbh->prepare( "INSERT INTO outbox(send_to, subject, body, send_from, send_cc, send_bcc, date_created, date_sent) VALUES (?, ?, ?, ?, , , CURRENT_TIMESTAMP, NULL)" );
$sth->bind_param( 1, $send_to,   DBI::SQL_VARCHAR );
$sth->bind_param( 2, $subject,   DBI::SQL_VARCHAR );
$sth->bind_param( 3, $body,      DBI::SQL_VARCHAR );
$sth->bind_param( 4, $send_from, DBI::SQL_VARCHAR );
$sth->execute;
$dbh->commit;
$dbh->disconnect();