public inbox for [email protected]  

help / color / mirror / Atom feed

From: Dan Kortschak <[email protected]>
To: [email protected]
Subject: re-novice coming back to pgsql: porting an SQLite update statement to postgres
Date: Mon, 15 Jul 2024 20:31:13 +0000
Message-ID: <[email protected]> (raw)

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....
Amend statement: https://github.com/kortschak/dex/blob/2ce9ff385443f5f475a5efdf91721903699852fa/cmd/worklog/store/db....

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox