In this post, I am going explain how I used Golang to simplify a daunting task at work.
Before I start, I assume you have basic knowledge in SQL like me :-)
Lets take a look at this query
SELECT [circuit_id],[unique_id],[unique_number],[created_user], [created_date],[last_edited_user],[last_edited_date],[object_id] FROM [<DB_NAME>].[dbo].[<TABLE_NAME>] WHERE unique_id='1111' OR unique_id='2934' OR unique_id='3150' OR unique_id='3640' ***** thousands of unique_id *****
Obviously, its a simple query, provided you have a few number unique IDs. But unfortunately, the “god of few numbers” never blesses me in such a situation. Its always the other way round.
The QA team for a project I happen to part of always demands reports like these mostly with thousands of IDs in an excel file.
How I did it
- I would copy the coulmn with the unique IDs in the file and paste them in an editor
- Put the right strings/condition “OR unique_id=‘id_number’” around every unique id (here id_number) by way of using alt, command and arrow keys combo to position the cursor at serveral places at the same time. and type the condition.
Quite better than doing it one by one I guess. But bear in mind, my finger has to on the appropriate arrow key till I reach the end of the file and not to mention that these IDs come with various lengths so consistency of the cursor position is not guaranteed.
Cause to change my ways
So, out of fraustration and the resolution to make golang part of me, I decided to automate this laborous task to a large extent. I wrote a tiny program in Go to help me out.
Here is the program structure
|- main.go | |- uniqueIds.txt | |- queryFile.sql ( created by our code )
main.go with explanations below.
Lets see what is happening here.
createQueryFile() is where all the action happens. On line 20, we create a file called
queryFile.sql using the
Create() method provided by
os package. We also check for errors whiles doing so just incase anything goes wrong.
On line 26, we open the
queryFile.sql file in append and read only mode and on line 31, we write the the beginning part of our sql query stored in the
pQueryString variable declared in line 13 to the file.
On line 34, we open the file
uniqueIds.txt in read write mode but defer its closure since we will be reading from it and we don’t want any premature closure till we are done reading its contents. This file contains all the unique IDs pulled from the excel file. We store it in the
IDsFile variable and read its contents starting from line 42.
Since we want to read the contents of the file line by line, next is to scan the
uniqueIds.txt file. We make it “scanable” using the
NewScanner() method provided by the
bufio package by passing the
IDsFile variable as our argument since it is I/O capable. We then use the
Scan() method which returns a boolean, – true if content exists and false otherwise, we pick the first unique id with the
if condition on line 44 and append it to the
queryFile.sql in line 45 using the
q variable declared on line 26. This is because
q returns a pointer the
queryFile.sql file – thus its “mermory location” where we can alter its contents.
We move on at line 48 with an infinite loop to append the remaining IDs in a formated way (here
OR unique_id='XXXX') with the help of of
Fprintln from the
fmt package. Remember
Scan() returns a bool. So the loop breaks when it reaches the end of the file.
In doing all this, we also cease the opportunity to get rid of all white spaces surrounding our unique IDs in the
uniqueIds.txt file using
TrimSpace() method from the
Just like all SQL querries, we terminate ours with line 56.
We then invoke our function in line 60 inside the main function so that it gets executed anytime we run the program with the famous
go run main.go command.
With this program, all I now have to do is provide the
uniqueIds.txt file with IDs each on a separate line and I get back to learining Go ASAP. ;-)
To end this post, I would like stress on the fact that Go is easy to learn and small enough to fit into the programmers mind. I had fun doing this as I didn’t expect it would be this simple with. I am now ever prepared for my QA team.
I hope you find this useful and give Golang a try in solving those daunting tasks you run away from.
Thanks for your time. And oh did I mention everything was done using the standard libraby? SOLID Go