The last time I used PostgreSQL for anything was about 15 years ago and
I have only limited SQL background, so please consider this a novice
question.
I have an embedded SQLite database that I would like to port to
PostgreSQL, I have done the majority of this porting, but am stuck on a
final compound statement. This is for a hobby project.
For background I'll give the SQLite schema and the statement in
question, and also the PostgreSQL schema. The SQLite code here is also
available to view in context at the links at the bottom of the post in
case this is helpful
SQLite schema:
create table if not exists buckets (
rowid INTEGER PRIMARY KEY AUTOINCREMENT,
id TEXT UNIQUE NOT NULL,
name TEXT,
type TEXT NOT NULL,
client TEXT NOT NULL,
hostname TEXT NOT NULL,
created TEXT NOT NULL, -- unix micro
datastr TEXT NOT NULL -- JSON text
);
create table if not exists events (
id INTEGER PRIMARY KEY AUTOINCREMENT,
bucketrow INTEGER NOT NULL,
starttime INTEGER NOT NULL, -- unix micro
endtime INTEGER NOT NULL, -- unix micro
datastr TEXT NOT NULL, -- JSON text
FOREIGN KEY (bucketrow) REFERENCES buckets(rowid)
);
create index if not exists event_index_id ON events(id);
create index if not exists event_index_starttime ON events(bucketrow, starttime);
create index if not exists event_index_endtime ON events(bucketrow, endtime);
PostgreSQL schema:
create table if not exists buckets (
rowid SERIAL PRIMARY KEY,
id TEXT UNIQUE NOT NULL,
name TEXT,
type TEXT NOT NULL,
client TEXT NOT NULL,
hostname TEXT NOT NULL,
created TIMESTAMP WITH TIME ZONE NOT NULL,
datastr JSONB NOT NULL
);
create table if not exists events (
id SERIAL PRIMARY KEY,
bucketrow INTEGER NOT NULL,
starttime TIMESTAMP WITH TIME ZONE NOT NULL,
endtime TIMESTAMP WITH TIME ZONE NOT NULL,
datastr JSONB NOT NULL,
FOREIGN KEY (bucketrow) REFERENCES buckets(rowid)
);
create index if not exists event_index_id ON events(id);
create index if not exists event_index_starttime ON events(bucketrow, starttime);
create index if not exists event_index_endtime ON events(bucketrow, endtime);
The part that I am having difficulty knowing where to start deals with
the JSON data in the events table. This contains largely unstructured
data, but can have an array of amendment notes added using the SQL
statements below. These notes are essentially append-only alterations
to the unstructured data, spanning a period of the unstructured data
within the time bounds of the event table row.
My question is where would be the best place for me to looks to learn
about how to implement a port of this SQLite? and what would broadly be
the most sensible approach to take (to narrow down what I need to read
through in learning)?
Apologies for the long post, and thank you for any help.
Dan
SQLite statement:
begin transaction;
-- ensure we have an amend array.
update events set datastr = json_insert(datastr, '$.amend', json('[]'))
where
datetime(starttime, 'subsec') < datetime(?5, 'subsec') and
datetime(endtime, 'subsec') > datetime(?4, 'subsec') and
bucketrow = (
select rowid from buckets where id = ?1
);
update events set datastr = json_insert(datastr, '$.amend[#]', json_object('time', ?2, 'msg', ?3, 'replace', (
-- trim amendments down to original event bounds.
select json_group_array(json_replace(value,
'$.start', case
when datetime(starttime, 'subsec') > datetime(json_extract(value, '$.start'), 'subsec') then
starttime
else
json_extract(value, '$.start')
end,
'$.end', case
when datetime(endtime, 'subsec') < datetime(json_extract(value, '$.end'), 'subsec') then
endtime
else
json_extract(value, '$.end')
end
))
from
json_each(?6)
where
datetime(json_extract(value, '$.start'), 'subsec') < datetime(endtime, 'subsec') and
datetime(json_extract(value, '$.end'), 'subsec') > datetime(starttime, 'subsec')
)))
where
datetime(starttime, 'subsec') < datetime(?5, 'subsec') and
datetime(endtime, 'subsec') > datetime(?4, 'subsec') and
bucketrow = (
select rowid from buckets where id = ?1
);
commit;
Schema: https://github.com/kortschak/dex/blob/2ce9ff385443f5f475a5efdf91721903699852fa/cmd/worklog/store/db.go#L203-L223
Amend statement: https://github.com/kortschak/dex/blob/2ce9ff385443f5f475a5efdf91721903699852fa/cmd/worklog/store/db.go#L791-L828
