SHELECT

Copyright (C) 2011,2018 Dominique Corfa dominique.corfa@free.fr

  This program is free software: you can redistribute it and/or modify
  it under the terms of the GNU General Public License as published by
  the Free Software Foundation, either version 3 of the License, or
  (at your option) any later version.

  This program is distributed in the hope that it will be useful,
  but WITHOUT ANY WARRANTY; without even the implied warranty of
  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  GNU General Public License for more details.

  You should have received a copy of the GNU General Public License
  along with this program.  If not, see .

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, provided you do not enfreight the GNU General Public License version 3 or later.

Installation

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.

Usage

    $ SHELECT help
  

Examples

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