Relational database use tables for any kind of data. They have a structured query language that work consistent and efficiently with atomic operations. It is possible to read data from different files but there is no consistency check of the files. With a cascade on delete relational databases delete multiple elements with a single atomic operation. The database engine maintains referential integrity of the data.

Example sorting rows

            a.1         a.2
            a.2   ->    a.1
            b.1         b.2
            b.2         b.1

This can be solved in a shell with GNU coreutils but its easier with a database and a join of two distinct tables.

select * from (
        select 'a' as col1 union select 'b' as col1
) join (
        select '1' as col2 union select '2' as col2
) order by col1, col2 desc;

Even complex operations can be written in SQL. Sorting of data requires self join and a a comparison and a group by. This is similar to C\++ std::sort algorithm.

Example in SQLite

with a as
(
                select 'a' as x, 21
                union all
                select 'b', 2
                union all
                select 'd', 23
                union all
                select 'c', 221
)
select count(*), x.* from a as x join a as y
where y.x <= x.x
group by x.x
order by count(*)
;

Common table expression

Some problems require recursion.

Example recursive split of a string.

WITH RECURSIVE
        count_down(x,y) AS (
        SELECT 5 as x, 0 as y
                UNION ALL
        SELECT cd.x - 1,cd.x||cd.y
        FROM count_down AS cd
        WHERE cd.x > 0
        )
SELECT * FROM count_down;

WITH RECURSIVE
example(name, list, element) AS (
                select name, substr(list, 1+instr(list, ",")), substr(list, 0, instr(list, ",")) from (
                        select 'letter' as name, "A,B,C," as list
                        UNION ALL
                        select 'number', "1,2,3,4,"
                )
                UNION ALL
                SELECT name, substr(list, 1+instr(list, ",")), substr(list, 0, instr(list, ","))
                FROM example
                WHERE instr(list, ",")
                )
SELECT name, element FROM example;

Links

Join with GNU coreutils

The example can be split and joined with file streams in the shell.

yash <( cat <<-'YASH'
    exec 3>>|4 5>>|6
    bash <<-BASH
        printf "a.1\na.2\nb.1\nb.2\n" | tr . "\t" | \
        tee  \
            >(cut -f1 | sort | uniq | nl >&3 ) > \
            >(sort | uniq | tac | nl >&5)
        exec 3<&- 5<&- 7<&-
        join -j 2 \
            <(head -n2 <&4  ) \
            <(head -n4 <&6 | sort -k2)
BASH
YASH
) | sort | tr ' ' . | cut -d. -f1,4
zipfile
.header on
.mode column
.width 10
SELECT quote(readfile('temp.zip')) AS X;
CREATE VIRTUAL TABLE temp.zip USING zipfile('temp.zip');
DELETE FROM temp.zip;
INSERT INTO temp.zip(name, data) VALUES('dir1', NULL);           -- Add directory
INSERT INTO temp.zip(name, data) VALUES('m.txt', 'abcdefghi');   -- Add regular file
SELECT * FROM temp.zip;