LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 01-22-2023, 10:46 AM   #1
Turbocapitalist
LQ Guru
 
Registered: Apr 2005
Distribution: Linux Mint, Devuan, OpenBSD
Posts: 6,692
Blog Entries: 3

Rep: Reputation: 3459Reputation: 3459Reputation: 3459Reputation: 3459Reputation: 3459Reputation: 3459Reputation: 3459Reputation: 3459Reputation: 3459Reputation: 3459Reputation: 3459
sqlite3 transposition of result into rows?


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.
 
Old 01-22-2023, 01:18 PM   #2
astrogeek
Moderator
 
Registered: Oct 2008
Distribution: Slackware [64]-X.{0|1|2|37|-current} ::12<=X<=15, FreeBSD_12{.0|.1}
Posts: 6,005
Blog Entries: 23

Rep: Reputation: 3967Reputation: 3967Reputation: 3967Reputation: 3967Reputation: 3967Reputation: 3967Reputation: 3967Reputation: 3967Reputation: 3967Reputation: 3967Reputation: 3967
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.

Last edited by astrogeek; 01-22-2023 at 01:22 PM.
 
1 members found this post helpful.
Old 01-22-2023, 01:33 PM   #3
Turbocapitalist
LQ Guru
 
Registered: Apr 2005
Distribution: Linux Mint, Devuan, OpenBSD
Posts: 6,692

Original Poster
Blog Entries: 3

Rep: Reputation: 3459Reputation: 3459Reputation: 3459Reputation: 3459Reputation: 3459Reputation: 3459Reputation: 3459Reputation: 3459Reputation: 3459Reputation: 3459Reputation: 3459
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.
 
Old 01-22-2023, 01:50 PM   #4
astrogeek
Moderator
 
Registered: Oct 2008
Distribution: Slackware [64]-X.{0|1|2|37|-current} ::12<=X<=15, FreeBSD_12{.0|.1}
Posts: 6,005
Blog Entries: 23

Rep: Reputation: 3967Reputation: 3967Reputation: 3967Reputation: 3967Reputation: 3967Reputation: 3967Reputation: 3967Reputation: 3967Reputation: 3967Reputation: 3967Reputation: 3967
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.

Last edited by astrogeek; 01-22-2023 at 04:50 PM.
 
1 members found this post helpful.
Old 01-23-2023, 12:46 AM   #5
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 7.7 (?), Centos 8.1
Posts: 18,145

Rep: Reputation: 2672Reputation: 2672Reputation: 2672Reputation: 2672Reputation: 2672Reputation: 2672Reputation: 2672Reputation: 2672Reputation: 2672Reputation: 2672Reputation: 2672
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
 
Old 01-23-2023, 03:13 AM   #6
NevemTeve
Senior Member
 
Registered: Oct 2011
Location: Budapest
Distribution: Debian/GNU/Linux, AIX
Posts: 4,428
Blog Entries: 1

Rep: Reputation: 1679Reputation: 1679Reputation: 1679Reputation: 1679Reputation: 1679Reputation: 1679Reputation: 1679Reputation: 1679Reputation: 1679Reputation: 1679Reputation: 1679
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;
 
Old 01-23-2023, 06:40 AM   #7
boughtonp
Senior Member
 
Registered: Feb 2007
Location: UK
Distribution: Debian
Posts: 3,077

Rep: Reputation: 2169Reputation: 2169Reputation: 2169Reputation: 2169Reputation: 2169Reputation: 2169Reputation: 2169Reputation: 2169Reputation: 2169Reputation: 2169Reputation: 2169
Quote:
Originally Posted by NevemTeve View Post
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.

 
1 members found this post helpful.
Old 01-23-2023, 10:57 AM   #8
sundialsvcs
LQ Guru
 
Registered: Feb 2004
Location: SE Tennessee, USA
Distribution: Gentoo, LFS
Posts: 10,102
Blog Entries: 4

Rep: Reputation: 3662Reputation: 3662Reputation: 3662Reputation: 3662Reputation: 3662Reputation: 3662Reputation: 3662Reputation: 3662Reputation: 3662Reputation: 3662Reputation: 3662
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 ...
 
Old 01-29-2023, 07:26 AM   #9
Turbocapitalist
LQ Guru
 
Registered: Apr 2005
Distribution: Linux Mint, Devuan, OpenBSD
Posts: 6,692

Original Poster
Blog Entries: 3

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


Reply

Tags
blocked by cloudflare, sqlite3


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Bash Script to manually import CSV into sqlite3 -> Spaces mess up everything burnhot Linux - Software 1 02-18-2015 02:17 PM
[SOLVED] How to import .txt file into sqlite3 database using C API? Newbie89 Linux - Newbie 3 05-06-2013 05:32 AM
To store user login info into sqlite3 using perl dhriti Programming 4 08-08-2012 02:31 PM
[SOLVED] MySQL SELECT INTO statement: Beware of 0, 2 or more result rows! rm_-rf_windows Linux - General 3 03-22-2012 06:52 PM
Compare two fields on consecutive rows and print the two rows aditi_borkar Linux - Newbie 3 04-09-2009 06:49 AM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 11:53 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration