Shell Scripting Examples

Generate comma separated dates for last seven days:

# Fetch the date 7 days ago
DATE=$(date -d '7 day ago' '+%Y-%m-%d');

# Generate comma-separated string for last 7 days
for i in {6..1}; do DATE=$DATE","$(date -d "$i day ago" '+%Y-%m-%d'); done

Considering current date is 2016-04-08, DATE variable value will be as follows:

2016-04-01,2016-04-02,2016-04-03,2016-04-04,2016-04-05,2016-04-06,2016-04-07

We can use different date formats on a case to case basis. This thing comes in handy when we have to feed a date range to some reporting tool.

Generate comma separated dates for last month and from today to start of the day:

	# This script generate comma separated dates for last month in yyyy-mm-dd format.
	# Ex:
	# "2018-04-01,2018-04-02,2018-04-03,2018-04-04,2018-04-05,2018-04-06,2018-04-07,
	# 2018-04-08,2018-04-09,2018-04-10,2018-04-11,2018-04-12,2018-04-13,2018-04-14,
	# 2018-04-15,2018-04-16,2018-04-17,2018-04-18,2018-04-19,2018-04-20,2018-04-21,
	# 2018-04-22,2018-04-23,2018-04-24,2018-04-25,2018-04-26,2018-04-27,2018-04-28,
	# 2018-04-29,2018-04-30"
	set `date +%m" "%Y`
	CURMTH=$1
	CURYR=$2
	if [ $CURMTH -eq 1 ]
	then
	PRVMTH=12
	PRVYR=`expr $CURYR - 1`
	else
	PRVMTH=`expr $CURMTH - 1`
	PRVYR=$CURYR
	fi
	if [ $PRVMTH -lt 10 ]
	then PRVMTH="0"$PRVMTH
	fi
	LASTDY=`cal $PRVMTH $PRVYR | egrep "28|29|30|31" |tail -1 |awk '{print $NF}'`
	FROM_DATE="$PRVYR-$PRVMTH-01"
	TO_DATE="$PRVYR-$PRVMTH-$LASTDY"
	# convert in seconds sinch the epoch:
	start=$(date -d$FROM_DATE +%s)
	end=$(date -d$TO_DATE +%s)
	cur=$start
	while [ $cur -le $end ]; do
	# convert seconds to date:
	REPORT_DATE=$REPORT_DATE$(date -d@$cur +%Y-%m-%d)",";
	let cur+=24*60*60
	done
	# Remove last comma (,)
	REPORT_DATE=${REPORT_DATE::-1}
	echo $REPORT_DATE;

view raw generate-dates-for-last-month.sh hosted with ❤ by GitHub

	# This script generates the dates from today till the start of the month in yyyy-mm-dd format.
	# Ex. If current date is 2018-05-03 then it will generate "2018-05-01,2018-05-02,2018-05-03".
	set `date +%m" "%Y`
	CURMTH=$1
	CURYR=$2
	CURRENT_DATE=`date +%d`;
	FROM_DATE="$CURYR-$CURMTH-01"
	TO_DATE="$CURYR-$CURMTH-$CURRENT_DATE"
	# convert in seconds sinch the epoch:
	start=$(date -d$FROM_DATE +%s)
	end=$(date -d$TO_DATE +%s)
	cur=$start
	while [ $cur -le $end ]; do
	# convert seconds to date:
	REPORT_DATE=$REPORT_DATE$(date -d@$cur +%Y-%m-%d)",";
	let cur+=24*60*60
	done
	# Remove last comma (,)
	REPORT_DATE=${REPORT_DATE::-1}
	echo $REPORT_DATE;

view raw generate-dates-from-today-till-start-of-the-month.sh hosted with ❤ by GitHub

Java - SQL Parsing

Recently I had a use case where I have to validate a SQL query. Basically I had to look at the JOINS and WHERE clauses in the query.

I thought that why to parse a SQL on my own and extract table names and where clauses from the query. Why to reinvent the wheel and write your own logic when you can re-use existing open source libraries. Keeping that in mind I started searching for a simple open source library to serve the purpose.

Surprisingly I found only a few and those were not serving the purpose then I found Apache-Calcite(https://calcite.apache.org/). Apache-Calcite is a huge project and SQL parsing is just one part of it.

I didn't find a good example though using its SQL parser and had to dig in a bit to get a working example. Following is a working example:

	public class BaseQueryValidator {
	private static List<String> extractTableAliases(SqlNode node) {
	final List<String> tables = new ArrayList<>();
	// If order by comes in the query.
	if (node.getKind().equals(SqlKind.ORDER_BY)) {
	// Retrieve exact select.
	node = ((SqlSelect) ((SqlOrderBy) node).query).getFrom();
	} else {
	node = ((SqlSelect) node).getFrom();
	}
	if (node == null) {
	return tables;
	}
	// Case when only 1 data set in the query.
	if (node.getKind().equals(SqlKind.AS)) {
	tables.add(((SqlBasicCall) node).operand(1).toString());
	return tables;
	}
	// Case when there are more than 1 data sets in the query.
	if (node.getKind().equals(SqlKind.JOIN)) {
	final SqlJoin from = (SqlJoin) node;
	// Case when only 2 data sets are in the query.
	if (from.getLeft().getKind().equals(SqlKind.AS)) {
	tables.add(((SqlBasicCall) from.getLeft()).operand(1).toString());
	} else {
	// Case when more than 2 data sets are in the query.
	SqlJoin left = (SqlJoin) from.getLeft();
	// Traverse until we get a AS.
	while (!left.getLeft().getKind().equals(SqlKind.AS)) {
	tables.add(((SqlBasicCall) left.getRight()).operand(1).toString());
	left = (SqlJoin) left.getLeft();
	}
	tables.add(((SqlBasicCall) left.getLeft()).operand(1).toString());
	tables.add(((SqlBasicCall) left.getRight()).operand(1).toString());
	}
	tables.add(((SqlBasicCall) from.getRight()).operand(1).toString());
	return tables;
	}
	return tables;
	}
	private static Map<String, String> extractWhereClauses(SqlNode node) {
	final Map<String, String> tableToPlaceHolder = new HashMap<>();
	// If order by comes in the query.
	if (node.getKind().equals(SqlKind.ORDER_BY)) {
	// Retrieve exact select.
	node = ((SqlOrderBy) node).query;
	}
	if (node == null) {
	return tableToPlaceHolder;
	}
	final SqlBasicCall where = (SqlBasicCall) ((SqlSelect) node).getWhere();
	if (where != null) {
	// Case when there is only 1 where clause
	if (where.operand(0).getKind().equals(SqlKind.IDENTIFIER)
	&& where.operand(1).getKind().equals(SqlKind.LITERAL)) {
	tableToPlaceHolder.put(where.operand(0).toString(),
	where.operand(1).toString());
	return tableToPlaceHolder;
	}
	final SqlBasicCall sqlBasicCallRight = where.operand(1);
	SqlBasicCall sqlBasicCallLeft = where.operand(0);
	// Iterate over left until we get a pair of identifier and literal.
	while (!sqlBasicCallLeft.operand(0).getKind().equals(SqlKind.IDENTIFIER)
	&& !sqlBasicCallLeft.operand(1).getKind().equals(SqlKind.LITERAL)) {
	tableToPlaceHolder.put(((SqlBasicCall) sqlBasicCallLeft.operand(1)).operand(0).toString(),
	((SqlBasicCall) sqlBasicCallLeft.operand(1)).operand(1).toString());
	sqlBasicCallLeft = sqlBasicCallLeft.operand(0); // Move to next where condition.
	}
	tableToPlaceHolder.put(sqlBasicCallLeft.operand(0).toString(),
	sqlBasicCallLeft.operand(1).toString());
	tableToPlaceHolder.put(sqlBasicCallRight.operand(0).toString(),
	sqlBasicCallRight.operand(1).toString());
	return tableToPlaceHolder;
	}
	return tableToPlaceHolder;
	}
	public static void main(String[] args) throws SqlParseException {
	final String query = "SELECT e.first_name AS FirstName, s.salary AS Salary from employee AS e join salary AS s on e.emp_id=s.emp_id where e.organization = 'Tesla' and s.organization = 'Tesla'";
	final SqlParser parser = SqlParser.create(baseQuery);
	final SqlNode sqlNode = parser.parseQuery();
	final SqlSelect sqlSelect = (SqlSelect) sqlNode;
	final SqlJoin from = (SqlJoin) sqlSelect.getFrom();
	// Extract table names/data sets, For above SQL query : [e, s]
	final List<String> tables = extractTableNames(from);
	// Extract where clauses, For above SQL query : [e.organization -> 'Tesla', s.organization -> 'Tesla']
	final Map<String, String> whereClauses = extractWhereClauses(sqlSelect);
	}
	}

view raw BaseQueryValidator.java hosted with ❤ by GitHub

So this is just about extracting table names and where clauses. This SQL parser can do definitely more and can be used to extract other parts of the query as well. Of course you can dig in the java docs and API to cater your requirements. Happy Learning !!!