Contains various resources relating to SQLite

SQLite Quickies.md

Dates

SELECT 
	date('now') AS [Today],	
	datetime(strftime('%s','now'), 'unixepoch') AS [Now_UTC],
	datetime(strftime('%s','now'), 'unixepoch', 'localtime') AS [Now_Local],
	strftime('%s','now') AS [Now_EPOCH],
	CAST((julianday('now') - 2440587.5)*86400000 AS INTEGER) AS [Now_EPOCH_Ms],
	date('now','start of month','+1 month','-1 day') AS [LastDayOfCurrentMonth],	
	CAST(strftime('%s', '2019-02-07') AS INTEGER) * 1000 AS [GivenDate_EPOCH_Ms], 
	CAST(strftime('%s', '2019-02-07', 'utc') AS INTEGER) * 1000 AS [GivenDate_UTC_EPOCH_Ms],
	datetime(1549543530, 'unixepoch') AS [DateTime_From_EPOCH],
	datetime(1549543530, 'unixepoch', 'localtime') AS [DateTime_From_EPOCH_Compensated];

WITHOUT ROWID

If a table has a non INTEGER PRIMARY KEY, then using WITHOUT ROWID can be justified You will need to specify a PRIMARY KEY and remember it cannot be NULL (unlike a WITH ROWID PRIMARY KEY)

CREATE TABLE IF NOT EXISTS [Person] (
    [LastUpdatedEpochMilliseconds] INTEGER DEFAULT (CAST((julianday('now') - 2440587.5)*86400000 AS INTEGER)),
    [Id] INTEGER PRIMARY KEY NOT NULL,
    [Name] TEXT NOT NULL,
    [Age] INTEGER NOT NULL
);
-- Covering index for queries including [Id] and [Name]
CREATE INDEX IF NOT EXISTS Person_IDX_COVERING ON [Person] ([Id], [Name]);

Check if a record exists

SELECT EXISTS (SELECT 1 FROM [Person] WHERE [Id] = @Id LIMIT 1);

Upserting (https://www.sqlite.org/lang_UPSERT.html)

INSERT INTO [Person] 
    ([Id], [Name], [Age]) 
VALUES 
    (@Id, @Name, @Age)
ON CONFLICT ([Id])
DO UPDATE SET
    [Name] = excluded.Name,
    [Age] = excluded.Id;

-- Alternatively you can first try UPDATing an existing record and if not exists then INSERT
UPDATE [Person] SET [Name] = 'Foo', [Age] = 10 WHERE [Id] = 1;

INSERT OR IGNORE INTO [Person] ([Id], [Name], [Age]) VALUES (1, 'Foo', 10);    

Updating a record and including the update time-stamp.

UPDATE [Meta] SET
    [MinRateTimestampEpochMilliseconds] = (SELECT MIN([TimestampEpochMilliseconds]) FROM [Rate]),
    [MaxRateTimestampEpochMilliseconds] = (SELECT MAX([TimestampEpochMilliseconds]) FROM [Rate]),
    [LastUpdatedEpochMilliseconds] = CAST((julianday('now') - 2440587.5)*86400000 AS INTEGER);

Get all objects

SELECT * FROM SQLITE_MASTER;

Optimization

Should be run just before connection close.

PRAGMA optimize;