Date: Mon, 15 Jul 2024 20:31:13 +0000
To: pgsql-general@postgresql.org
From: Dan Kortschak <dan+pgsql@kortschak.io>
Subject: re-novice coming back to pgsql: porting an SQLite update statement to postgres
Message-ID: <1aea530e4c96109b91fdde53a29e38beb625dc77.camel@kortschak.io>
Feedback-ID: 18519778:user:proton
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/1aea530e4c96109b91fdde53a29e38beb625dc77.camel%40kortschak.io>
Precedence: bulk

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 (
=09rowid    INTEGER PRIMARY KEY AUTOINCREMENT,
=09id       TEXT UNIQUE NOT NULL,
=09name     TEXT,
=09type     TEXT NOT NULL,
=09client   TEXT NOT NULL,
=09hostname TEXT NOT NULL,
=09created  TEXT NOT NULL, -- unix micro
=09datastr  TEXT NOT NULL  -- JSON text
);
create table if not exists events (
=09id        INTEGER PRIMARY KEY AUTOINCREMENT,
=09bucketrow INTEGER NOT NULL,
=09starttime INTEGER NOT NULL, -- unix micro
=09endtime   INTEGER NOT NULL, -- unix micro
=09datastr   TEXT NOT NULL,    -- JSON text
=09FOREIGN 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, start=
time);
create index if not exists event_index_endtime ON events(bucketrow, endtime=
);


PostgreSQL schema:

create table if not exists buckets (
=09rowid    SERIAL PRIMARY KEY,
=09id       TEXT UNIQUE NOT NULL,
=09name     TEXT,
=09type     TEXT NOT NULL,
=09client   TEXT NOT NULL,
=09hostname TEXT NOT NULL,
=09created  TIMESTAMP WITH TIME ZONE NOT NULL,
=09datastr  JSONB NOT NULL
);
create table if not exists events (
=09id        SERIAL PRIMARY KEY,
=09bucketrow INTEGER NOT NULL,
=09starttime TIMESTAMP WITH TIME ZONE NOT NULL,
=09endtime   TIMESTAMP WITH TIME ZONE NOT NULL,
=09datastr   JSONB NOT NULL,
=09FOREIGN 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, start=
time);
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;
=09-- ensure we have an amend array.
=09update events set datastr =3D json_insert(datastr, '$.amend', json('[]')=
)
=09where
=09=09datetime(starttime, 'subsec') < datetime(?5, 'subsec') and
=09=09datetime(endtime, 'subsec') > datetime(?4, 'subsec') and
=09=09bucketrow =3D (
=09=09=09select rowid from buckets where id =3D ?1
=09=09);
=09update events set datastr =3D json_insert(datastr, '$.amend[#]', json_ob=
ject('time', ?2, 'msg', ?3, 'replace', (
=09=09-- trim amendments down to original event bounds.
=09=09select json_group_array(json_replace(value,
=09=09=09'$.start', case
=09=09=09=09when datetime(starttime, 'subsec') > datetime(json_extract(valu=
e, '$.start'), 'subsec') then
=09=09=09=09=09starttime
=09=09=09=09else
=09=09=09=09=09json_extract(value, '$.start')
=09=09=09=09end,
=09=09=09'$.end', case
=09=09=09=09when datetime(endtime, 'subsec') < datetime(json_extract(value,=
 '$.end'), 'subsec') then
=09=09=09=09=09endtime
=09=09=09=09else
=09=09=09=09=09json_extract(value, '$.end')
=09=09=09=09end
=09=09))
=09=09from
=09=09=09json_each(?6)
=09=09where
=09=09=09datetime(json_extract(value, '$.start'), 'subsec') < datetime(endt=
ime, 'subsec') and
=09=09=09datetime(json_extract(value, '$.end'), 'subsec') > datetime(startt=
ime, 'subsec')
=09)))
=09where
=09=09datetime(starttime, 'subsec') < datetime(?5, 'subsec') and
=09=09datetime(endtime, 'subsec') > datetime(?4, 'subsec') and
=09=09bucketrow =3D (
=09=09=09select rowid from buckets where id =3D ?1
=09=09);
commit;

Schema: https://github.com/kortschak/dex/blob/2ce9ff385443f5f475a5efdf91721=
903699852fa/cmd/worklog/store/db.go#L203-L223
Amend statement: https://github.com/kortschak/dex/blob/2ce9ff385443f5f475a5=
efdf91721903699852fa/cmd/worklog/store/db.go#L791-L828