Sqawk

What	Sqawk
Where	https://github.com/dbohdan/sqawk
Description	An awk-like program that uses SQL. Can parse, format, filter, and combine data from multiple files. Powered by SQLite.
Platforms	Linux, Windows XP/7/8.x/10, macOS, FreeBSD, NetBSD, OpenBSD.
Prerequisites	Tcl 8.6 or 9, Tcllib, SQLite3 bindings for Tcl.
Updated	2024-05-10 (v0.24.0)
License	MIT
Contact	dbohdan

Examples

Sum up numbers

find . -iname '*.jpg' -type f -printf '%s\n' | sqawk 'select sum(a1)/1024/1024 from a'

Shuffle lines

sqawk -1 'select a1 from a order by random()' < file

Find duplicate lines

Print them and how many times they are repeated.

sqawk -1 -OFS ' -- ' 'select a0, count(*) from a group by a0 having count(*) > 1' < file

Sample output

13 -- 2
16 -- 3
83 -- 2
100 -- 2

Combine data from two files

Input files

MD5SUM

d2e7d4d1c7587b40ef7e6637d8d777bc  0005.jpg
4e7cde72529efc40f58124f13b43e1d9  001.jpg
e2ab70817194584ab6fe2efc3d8987f6  0.0.6-settings.png
9d2cfea6e72d00553fb3d10cbd04f087  010_2.jpg
3df1ff762f1b38273ff2a158e3c1a6cf  0.10-planets.jpg
0be1582d861f9d047f4842624e7d01bb  012771602077.png
60638f91b399c78a8b2d969adeee16cc  014tiles.png
7e7a0b502cd4d63a7e1cda187b122b0b  017.jpg
1e958dfe8f99de90bb8a9520a0181f51  01newscreenie.jpeg
f56fb95efa84fbfdd8e01222b4a58029  02_characterselect.jpg
[...]

list

drwxr-xr-x. 2 dbohdan dbohdan   94208 Apr  7  2013 .
drwxr-xr-x. 4 dbohdan dbohdan    4096 Apr 14  2014 ..
-rw-r--r--. 1 dbohdan dbohdan  136229 Apr  7  2013 0005.jpg
-rw-r--r--. 1 dbohdan dbohdan  112600 Apr  7  2013 001.jpg
-rw-r--r--. 1 dbohdan dbohdan   26651 Apr  7  2013 0.0.6-settings.png
-rw-r--r--. 1 dbohdan dbohdan  155579 Apr  7  2013 010_2.jpg
-rw-r--r--. 1 dbohdan dbohdan   41485 Apr  7  2013 0.10-planets.jpg
-rw-r--r--. 1 dbohdan dbohdan 2758972 Apr  7  2013 012771602077.png
-rw-r--r--. 1 dbohdan dbohdan  426774 Apr  7  2013 014tiles.png
[...]

Query

sqawk 'select a.a1, b.b5, a.a2 from a inner join b on a.a2 = b.b9 where b.b5 < 10000 order by b.b5' MD5SUMS list

Output

d50700db41035eb74580decf83f83184 615 z81.png
e1b64d03caf4615d54e9022d5b13a22d 677 init.png
a0fb29411c169603748edcc02c0e86e6 823 agendaroids.gif
3b0c65213e121793d4458e09bb7b1f58 970 screen01.gif
05f89f23756e8ea4bc5379c841674a6e 999 retropong.png
a49a7b5ac5833ec365ed3cb7031d1d84 1458 fncpong.png
80616256c790c2a831583997a6214280 1516 el2_small.jpg
1eee29eaef2ae740d04ad4ee6d140db7 1575 thrust-0.89f.gif
19edfd5a70c9e029b9c601ccb17c4d12 1665 xrockman.png
4e1105deeeba96e7b935aa07e2066d89 1741 xroarsnap.png
[...]

Comments

JM on windows, make sure you use double quotes for your SQL script

3/30-2016 JM: is it possible to have an option to save the SQLite database in a file?

I guess there is a conversion to an in-memory database.
This way I can save time on the conversion if the source file does not change often and want to use SQLiteStudio for example

3/31: answering to myself, I think I am missing the point,SQLite clients provide the import from csv file types already.

dbohdan 2017-11-18: Starting with version 0.21 you can use an on-disk SQLite database with the -dbfile option. Here is an example with CSV import.

$ cat test.csv
1,2,3
4,5,6
7,8,9
$ sqawk -dbfile csv-import.sqlite3 'select "only importing"' format=csv test.csv
only importing
$ sqawk -dbfile csv-import.sqlite3 -noinput 'select a1, a2 from a where a1 + a2 > 7'
4 5
7 8

Category Application

Category SQLite

Category String Processing