Copyright (C) 2011,2018 Dominique Corfa dominique.corfa@free.fr
Here is a small bash script (requires at least version 4.0), SHELECT, where the bash is used as a glue to combine basic text commands (as cut, join, sort) in a SQL way. It operate on text files, formed as relationnal table. The only addition to a simple "field separated" text file is a header giving the columns name. By the way, I am searching a nice name for it.
You are welcome to take it, use it and enhance it following your need.
This is a bash script. You must have a bash to run it.
Copy the file SHELECT in any directory included in your $PATH, and set the execute access. It's done, you can run it.
I have signed the SHELECT file with my key.You may check this signature by inserting my public key in your key holder.
$ SHELECT help
Use it as a cat.
$ SHELECT \* FROM table # id,nom,prenom 01,De La Fontaine,Jean 02,Marcœur,Albert 03,Klee,Paul
Use it as sort.
$ SHELECT \* FROM table ORDER BY nom # id,nom,prenom 01,De La Fontaine,Jean 03,Klee,Paul 02,Marcœur,Albert
Use it as cut, but actually cut is not used internally because it does not keep the original columns order.
$ SHELECT prenom nom FROM table # prenom,nom Jean,De La Fontaine Albert,Marcœur Paul,Klee
Use it as awk to filter.
$ SHELECT \* FROM table WHERE 'prenom=="Jean" # id,nom,prenom 01,De La Fontaine,Jean
Let's use a very common situation with customers and orders. IN the following description, we imagine we use a bash interactive session. Commands typed by the operator are in bold characters. Here is the few file we have prepared. Note that we have soft-linked the file so we can refer to them with a single char in the SQL command, simulating the use of a 'AS' SQL keyword.
$ ls -l total 16 lrwxrwxrwx 1 dominique dominique 7 2011-12-11 22:04 a -> article -rw-r--r-- 1 dominique dominique 55 2011-12-11 22:01 article lrwxrwxrwx 1 dominique dominique 8 2011-12-11 21:43 c -> customer -rw-r--r-- 1 dominique dominique 78 2011-12-11 21:40 customer lrwxrwxrwx 1 dominique dominique 7 2011-12-11 22:04 i -> invoice -rw-r--r-- 1 dominique dominique 33 2011-12-11 22:01 invoice lrwxrwxrwx 1 dominique dominique 5 2011-12-11 22:04 o -> order -rw-r--r-- 1 dominique dominique 80 2011-12-11 21:46 order
Nowadays, we do evrything using mobile phone, so customer contact is a number.
$ SHELECT \* FROM customer # id,name,phone 1,Corfa,33678912345 2,Dupont,33666666666 3,Dupond,33606060606
A minimalistic article file. Note that we protect the star '*' from shell expansion.
$ SHELECT \* FROM article # id,name,price 1,machin,3 2,truc,16.50 3,bidule,9.30 4,chose,1 5,objet,9.99
A table for orders, in which customer refers to the customer ID.
$ SHELECT \* FROM order # id,date,customer,delivered 1,2011-12-10,1,1 2,2011-12-10,3,0 3,2011-12-11,1,0
And the composition of the order. It refers to the order ID and to the articles ID and is also used for the edition of the invoice. It's indeed a '1..*' relation table.
$ SHELECT \* FROM invoice # order,article 1,1 1,1 1,1 1,3 3,5 3,5 2,3 2,3 2,3 2,4 2,4 2,4 2,4 2,4 2,4
Let's print the orders we have to deliver:
$ SHELECT o.date c.name a.name a.price FROM o JOIN c ON o.customer=c.id JOIN i ON o.id=i.order JOIN a ON i.article=a.id WHERE 'o.delivered==0' # date,name,name,price 2011-12-10,Dupond,bidule,9.30 2011-12-10,Dupond,bidule,9.30 2011-12-10,Dupond,bidule,9.30 2011-12-10,Dupond,chose,1 2011-12-10,Dupond,chose,1 2011-12-10,Dupond,chose,1 2011-12-10,Dupond,chose,1 2011-12-10,Dupond,chose,1 2011-12-10,Dupond,chose,1 2011-12-11,Corfa,objet,9.99 2011-12-11,Corfa,objet,9.99
It may be summarized easily as:
$ SHELECT o.date c.name a.name a.price 'count(a.name)' 'sum(a.price)' FROM o JOIN c ON o.customer=c.id JOIN i ON o.id=i.order JOIN a ON i.article=a.id WHERE 'o.delivered==0' GROUP BY o.date c.name a.name a.price # date,name,name,price,count(a.name),sum(a.price) 2011-12-10,Dupond,bidule,9.30,3,27.9 2011-12-10,Dupond,chose,1,6,6 2011-12-11,Corfa,objet,9.99,2,19.98