sfeedql - randomcrap - random crap programs of varying quality
 (HTM) git clone git://git.codemadness.org/randomcrap
 (DIR) Log
 (DIR) Files
 (DIR) Refs
 (DIR) README
 (DIR) LICENSE
       ---
       sfeedql (1617B)
       ---
            1 #!/bin/sh
            2 # sfeed Stupid Query Language hack
            3 # inspired by some ugly hack on some blog somewhere on the interwebs.
            4 #
            5 # Reads sfeed(5) data from stdin or from files.
            6 # It creates a temporary in-memory SQLite3 database for executing SQL queries.
            7 #
            8 # Options:
            9 # When files are specified then the basename of the file is the value for the
           10 # feedname column for stdin it is "-".
           11 #
           12 # Use the -q option to execute a SQL query, the table name is items.
           13 # the default query is to show the last 10 items (by timestamp DESC).
           14 #
           15 # Example: curl -s https://codemadness.org/atom_content.xml | \
           16 #                  sfeed | \
           17 #                  sfeedql -q "SELECT * FROM items WHERE title LIKE '%sfeed%'"
           18 
           19 usage() {
           20         echo "$0 [-q query] [files...]" >&2
           21         exit 1
           22 }
           23 
           24 # tsvwithfeed(suffix, filename)
           25 # use the sfeed(5) format, add the feedname as an extra field.
           26 tsvwithfeed() {
           27         LC_ALL=C awk -v "suffix=$1" '
           28 BEGIN {
           29         FS = OFS = "\t";
           30 }
           31 {
           32         print $0 OFS suffix;
           33 }' "$2"
           34 }
           35 
           36 # default query
           37 query="SELECT * FROM items ORDER BY timestamp DESC LIMIT 10"
           38 
           39 while getopts q: name; do
           40         case "$name" in
           41         q) query="$OPTARG";;
           42         ?) usage;;
           43         esac
           44 done
           45 shift $(($OPTIND - 1))
           46 
           47 if test "$#" -eq 0; then
           48         # no files specified: use stdin.
           49         tsvwithfeed "-" "-"
           50 else
           51         # ... or read from files.
           52         for f in "$@"; do
           53                 tsvwithfeed "${f##*/}" "$f"
           54         done
           55 fi | \
           56 sqlite3 \
           57 ':memory:' \
           58 'CREATE TABLE items (
           59         timestamp bigint,
           60         title TEXT,
           61         link TEXT,
           62         content TEXT,
           63         content_type varchar(8),
           64         id TEXT,
           65         author TEXT,
           66         enclosure TEXT,
           67         category TEXT,
           68         feedname nvarchar(255));' \
           69 '.mode ascii' \
           70 '.headers off' \
           71 '.separator "\t" "\n"' \
           72 '.import /dev/stdin items' \
           73 "${query}"