[SOLVED] sqlite3 transposition of result into rows?

Turbocapitalist · 01-22-2023, 09:46 AM

I'm looking at an sqlite3 table and wondering how to massage the results into one new row per record, so I can avoid using Python dictionaries or Perl hashes in the scripts. Or are Python dictionaries or Perl hashes the way to go? General comments about the approach, based on the sample table, are welcome too.

I'm not sure what to try with the SQL query. Here's I have so far,

Code:

> select m1.record, m1.term, m1.value from metadata as m1 
  join metadata as m2 on m2.term='dc.date' and m2.value>=1672552800 
  and m1.record=m2.record Order by m1.record, m1.term;

5|dc.creator|another author
5|dc.date|1672552800
5|dc.title|another title
11|dc.creator|a third author
11|dc.date|1672754400
11|dc.title|the third title

What I would like would be something like this, if it is feasible:

Code:

> select something;
5|1672552800|another author|another title
11|1672754400|a third author|the third title

Here is some sample data, encoded as base64 because Cloudflare blocks the post otherwise:

Code:

$ echo '.dump' | sqlite3 test.database.sqlite3 | base64
UFJBR01BIGZvcmVpZ25fa2V5cz1PRkY7CkJFR0lOIFRSQU5TQUNUSU9OOwpDUkVBVEUgVEFCTEUg
bWV0YWRhdGEgKHJlY29yZCBpbnRlZ2VyLCB0ZXJtIHZhcmNoYXIoMjApIG5vdCBudWxsLCB2YWx1
ZSB2YXJjaGFyKDI1NikpOwpJTlNFUlQgSU5UTyBtZXRhZGF0YSBWQUxVRVMoMiwnZGMuZGF0ZScs
JzE2NzI1MjQwMDAnKTsKSU5TRVJUIElOVE8gbWV0YWRhdGEgVkFMVUVTKDIsJ2RjLmNyZWF0b3In
LCdvbmUgYXV0aG9yJyk7CklOU0VSVCBJTlRPIG1ldGFkYXRhIFZBTFVFUygyLCdkYy50aXRsZScs
J2EgdGl0bGUnKTsKSU5TRVJUIElOVE8gbWV0YWRhdGEgVkFMVUVTKDUsJ2RjLmRhdGUnLCcxNjcy
NTUyODAwJyk7CklOU0VSVCBJTlRPIG1ldGFkYXRhIFZBTFVFUyg1LCdkYy50aXRsZScsJ2Fub3Ro
ZXIgdGl0bGUnKTsKSU5TRVJUIElOVE8gbWV0YWRhdGEgVkFMVUVTKDUsJ2RjLmNyZWF0b3InLCdh
bm90aGVyIGF1dGhvcicpOwpJTlNFUlQgSU5UTyBtZXRhZGF0YSBWQUxVRVMoMTEsJ2RjLmRhdGUn
LCcxNjcyNzU0NDAwJyk7CklOU0VSVCBJTlRPIG1ldGFkYXRhIFZBTFVFUygxMSwnZGMudGl0bGUn
LCd0aGUgdGhpcmQgdGl0bGUnKTsKSU5TRVJUIElOVE8gbWV0YWRhdGEgVkFMVUVTKDExLCdkYy5j
cmVhdG9yJywnYSB0aGlyZCBhdXRob3InKTsKQ09NTUlUOwo=

Cloudflare does not like the SQL statements used to create a table and the usual obfuscation methods don't seem to work.

astrogeek · 01-22-2023, 12:18 PM

One way to do it would be with a JOIN of subselects:

Code:

sqlite> SELECT record, ts, author, title FROM
   ...> (SELECT record, value AS ts FROM metadata WHERE term='dc.date') AS T1
   ...> JOIN (SELECT record, value AS author FROM metadata WHERE term='dc.creator') AS T2 USING(record)
   ...> JOIN (SELECT record, value AS title FROM metadata WHERE term='dc.title') AS T3 USING(record)
   ...> order by record;
2|1672524000|one author|a title
5|1672552800|another author|another title
11|1672754400|a third author|the third title

With most SQL servers you might also define a PROCEDURE or VIEW to produce a single entity from which you select, but I am not familiar enough with Sqlite3 to know whether and how it may support those.

A better way would be to define a data model which stored those fields as a single table you would access by record number, but that would also depend on any other constraints your model and application imposed.

Turbocapitalist · 01-22-2023, 12:33 PM

Thanks. I'll have to play with subselects a bit until I can figure out how to tack on the date constraint too.

The data is kind of flexible and sparse, and not great for tables. It seems more suited to a Perl tied hash, though that lacks transactions. Or I'm not sure I'm ready for a key-value database like M or YottaDB though that does have proper support transactions.

astrogeek · 01-22-2023, 12:50 PM

To add a date constraint to the result set simply add that to the WHERE clause of the T1 subselect, for example:

Code:

...
   ...>(SELECT record, value AS ts FROM metadata WHERE term='dc.date' AND value>=1672552800) AS T1
...

Sorry I had neglected this in the first example.

chrism01 · 01-22-2023, 11:46 PM

Well, Perl's DB modules include DBI & https://metacpan.org/pod/DBD::SQLite.
That page should help.
If you haven't got them already, use your pkg mgr to install them.

HTH

NevemTeve · 01-23-2023, 02:13 AM

If WITH-clause is supported, I would try something like this:

Code:

WITH datsel  AS (SELECT m.record, m.value dc_date FROM metadata m WHERE m.term='dc.date'),
     cresel  AS (SELECT m.record, m.value creator FROM metadata m WHERE m.term='dc.creator'),
     titsel  AS (SELECT m.record, m.value title   FROM metadata m WHERE m.term='dc.title')
     linesel AS (SELECT d.record, d.dc_date, c.creator, t.title FROM
                 FROM datsel d
                 JOIN cresel c ON c.record=d.record
                 JOIN titsel t ON t.record=d.record)
SELECT * FROM linesel WHERE dc_date>=1672552800;

boughtonp · 01-23-2023, 05:40 AM

Quote:

Originally Posted by NevemTeve

If WITH-clause is supported

It is. See https://www.sqlite.org/lang.html for what Sqlite supports.

In addition to syntax railroad diagrams, The WITH clause page describes a number of examples of how it can be used.

From my reading of what Turbocapitalist is asking, it should be solvable with subselects and/or left joins, and perhaps also coalesce to provide defaults for missing values.

sundialsvcs · 01-23-2023, 09:57 AM

I did a quick search on "sqlite transpose" and found this: https://stackoverflow.com/questions/...able-in-sqlite

There were many other pages.

I don't think that SQLite directly supports SQL to "transpose" a table from vertical to horizontal.

If the amount of data is reasonable, then a programmed script in the language of your choice might be used to produce the result ...

Turbocapitalist · 01-29-2023, 06:26 AM

Thanks, everyone. The clarification on JOIN of subselects got me over the barrier. The WITH approach looks useful in the future.