[SOLVED] sqlite3 transposition of result into rows?
ProgrammingThis forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.
Notices
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
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:
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.
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.
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.
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;
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.
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.