In this post, I’ll cover different examples of what I like to call templating. Doing the same over and over again, but only few parts change each iteration.
Environment Variables
While working on integrating an API, I had to authenticate with a Bearer token in the authentication header. These tokens are typically short-lived, so they change multiple times a day.
As part of the integration, I write down the API calls in Typora, a very nice markdown editor. I really like I can add code snippets that are being highlighted, it’s easy to work with, so it’s an ideal tool for taking notes that I can later refer to. A typical entry would be like this:
http 'Authentication: Bearer 123456789...XYZ' http: //www.example.com
A typical workflow is copying the call from my markdown notes and execute them in the shell. Off course the bearer token has expired in the meantime, so I have to request a new token, update my notes and copy again.
And then it hit me: why not replace the bearer token in my notes with environment variables? That way I can just copy and paste the command, and if the token is expired, I can just update it without changing my notes.
export TOKEN=123456789...XYZ # update the bearer token if expired
http "Authentication: Bearer $TOKEN" http://www.example.com
SQL statement generation
Currently working on a new Java application with typical user roles.
For developing and testing purposes it is useful to have a dedicated user for each role. In this particular case this requires a row to be added to the user and user_roles tables. Not that hard, but there are 5 roles, for each role 2 queries meaning a total of 10 queries that differ on only a few places, being the email address and the credential value.
sql insert into `user` (email, password, is_active, created_at, updated_at) values ('admin@example.com', sha1('pass'), 1, now(), now() );
insert into user_credential (user_id,credential,value,created_at,updated_at) values (LAST_INSERT_ID(),'admin',1,now(),now());
So it is Powershell to the rescue, more specific the templating functionality.
In powershell you can define placeholders between curly brackets around an index (zero-based). Next you can format the template while providing the values for each placeholder - the -f is shorthand for format and then you provide all values separated by commas. This $_ is a special variable in powershell, it is the currently processed value of the array. In the first iteration, it will have the value admin, the next iteration the value press, and so on.
$sql_template = @"
insert into `user`
(email, password, is_active, created_at, updated_at)
values('{0}@example.com', sha1('pass'), 1, now(), now() );
insert into user_credential
(user_id,credential,value,created_at,updated_at)
values (LAST_INSERT_ID(),'{0}',1,now(),now());
"@
# loop over roles array and provide each one of them to the template.
@('admin','press','super-admin','mice','spot')
|%{$sql_template -f $_}
Of course this does not work on all environments because the list of user roles is different for each environment. So you can rewrite this Powershell template with an actual SQL statement, using the CONCATfunction. It does not look pretty, but it gets the job done.
SELECT distinct uc.credential as c,
CONCAT("insert into `user` ( email, password,
is_active, created_at, updated_at)
values('", credential, "@emakina.com', sha1('pass'),
1, now(), now());")
as command
from user_credential uc
UNION
SELECT distinct uc.credential as c,
CONCAT("insert into user_credential (user_id, credential,
value, created_at, updated_at)
values (LAST_INSERT_ID(), '", credential,"', 1, now(), now());")
as command
from user_credential uc
order by c;
Conclusion
Templating can help a lot with reducing annoying tasks. Powershell (or any language/tool that supports some kind of templating) is a great help, if not available, CONCAT functions go a long way as well