[Home] [fr]

SHELECT

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.

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

You can get the full syntax of the command by typing :
    $ 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