Awk Is Easy To Use

2023-09-19

The problem

Unfortunately a lot of coding is dealing with boilerplate - assigning getters and setters to all the properties of a class for example. And because you're dealing with data a lot of the time this is database driven. The simplest way for me is inspecting the schema, for example this is how I'd pull up a list of columns for a table in SQL Server:

select column_name from information_schema.columns

This would allow me to do something like the following:

select 'public string ' + column_name + '{ get; set; }' from information_schema.columns

Which would product the following valid C# code which I could then copy and paste as needed:

public string FirstName { get; set; } 
public string LastName { get; set; } 
...

Sometimes I might do something similar in a spreadsheet. Assuming the contents of A1 in the cell below represents a field it would be something like:

='public string ' & A1 & ' { get; set;}'

The above would produce the same valid C# code. I've been working with Rails and Sqlite recently and so I thought the spreadsheet option was going to be my approach but then I thought about trying something else.

I spend most of my time in the command line these days (I finally made the switch from IDE to Neovim!) and so I thought it would improve my workflow if I could use one of the command line tools. I've used awk but I always thought it would be too cumbersome, however I trid the tldr tool to get me going. Running this command for awk gave me the exact lines I needed:

- Print the fifth column (a.k.a. field) in a space-separated file:
    awk '{print $5}' path/to/file

- Print all lines where the 10th column value equals the specified value:
    awk '($10 == value)' 

By running Sqllite's pragma command and piping the output I would then have my list of fields to work with. For example if I run the following for the Northwind datbase:

sqlite3 northwind.sqllite3 'pragma table_info(employees)' 

I will end up with the following table data with the field values seperated by the pipe operator:

0|id|INTEGER|1||1
1|last_name|varchar(50)|1||0
2|first_name|varchar(50)|1||0
3|email|varchar(50)|0||0
...

From the above result set I can see I need column 2 for my field name. And I can go further by inspecting the values of other fields such that if I want some Ruby code for validating the required fields (field 4) I can run the following:

sqlite3 northwind.sqlite3 'pragma table_info(employees)' | awk -F '|' '($4 == 1) {print "validates :" $2 ", presence: true "}'

Which will then product the correct ruby I can copy and paste:

validates :last_name, presence: true
validates :first_name, presence: true
...

Simple stuff. I imagine I will do it like this always from now on!