Select * from ... wait... i'm not a dba!
Doesn’t it make you grumpy when you sat in front of terminal and there someone asks you what a certain acronym in IT is… for example… Grumpy Admin what does SCSI actually mean…. I am actually quite a bit of a geek when it comes to IT acronyms and I normally fire off a reply with such authority that they don’t question. Blagging with confidence and getting it right more than 50% of the time is signs of a good administrator J
When dealing with upper management and project managers my normal, let me referral to “let me google that for you” just can’t cut the mustard and would perhaps end up with a unemployed Grump Admin.
Typically they ask these sort of things when I am in PowerShell string together queries and stuff as if the pressure doesn’t get to me!
Wouldn’t it be good if we could just ask PowerShell to define something quickly like “define SCSI” and that would shut them up and look kind a geeky at the same time! I wonder how long till someone implements Cortana in PowerShell! The virus of the future will be wav files maybe???
In my youth, well two/three years ago. I wrote and very small android application that had a small SQLite database for looking up IT acronyms
https://play.google.com/store/apps/details?id=com.hazzy.ITAcronyms&hl=en
Development has seemed to have stopped, but if the number of installs grows I will update it etc. Grumpy Admin will add this to his todo list! Right under “DO Avril Lavigne”.
So I had the data set already which could be reused, so why not! Should be a fun 10 min exercise. Again this another where writing the blog post actually takes more time than doing the bad proof of concept code!
Now first thing is – there is no native sqlite provide or connector in .net
So we need a database connection provider – Lucky for me Google was to the rescue, the first hit will do for me, as I am feeling lucky today… I sure there might be better etc, but Grumpy Admin is also a lazy admin right?
Grumpy Admin golden rule is don’t reinvent the wheel. Changes are somewhere out there someone has done what you want to do already. Learn from them
So I will be using the psqilite from codeplex. Which we do with a simple import-module command
Import-Module .\SQlite.psd1
Now before we connect and use our sqlite database file lets open it up and dust it off and make sure it still there! For complete easy I am goign to be using a great little tool called DB Browser for SQLite!
As you can see the structure of the data base is pretty simple, I am not a DBA – I created this from a data dump and shipped it with my little baby android app.
So I have my database and it’s good, I have confirmed that it still works, now let’s give it some PowerShell love! Remember this demo is just an “I can connect to” proof of concept nothing special. I not modifying or creating or inserting data. All these are possible. For now, all I am going to do is execute a query and read the results.
So the first step for Grumpy Admin is too define some variables, we are going to need some information to connect to the database right. Like where the Database is, etc! I could hard code this in, but to allow for future exploitation and easy adaption of the code we will put it in a variable!
If things need to be changed or could be end user definable than they should be a variable.
$database =”C:\SQLite\Database\Data”
$ds=”Data Source =$database”
After that we need to send the data source path which is in our $database variable as an parameter inside the sqliteconnection function –
$conn=new-object System.Data.SQLite.SQLiteConnection($ds)
Let’s invoke it and create an object, our own little connection object called $conn and then as ever, I am going to do a quick get-member on it, to see what there is to play with.
Exciting stuff, all good now let’s actually invoke this object and create the connection to the data source by opening it! How you ask.. Well we just OPEN it…
$conn.Open()
Now we have an open connection to the database we can go ahead and start to build our little SQL query. Don’t forget this is SQLlite – so check your syntax, if like me you never really do SQL there are plenty of resources in the world of Google. Me I like to do things simple. SQL experts will hate this…..
$query=”select * from Data”
Now thanks to the power of Google a problem I was having, when trying to dump the data into a string was is solved by casting the returned data in to a dataset type. So I will do that now.
$data = new-object system.data.dataset
Next we need to use a dataadapter to execute our query against the actual database. The first step again is of course to construct the object and then call the Fill function that will the put the data from the database in to the destination which in this case will be our variable $data.
$adapter = new-object System.Data.SQLite.SQLiteDataAdapter($query,$conn)
$adapter.Fill($data)
Next let’s have a quick look at the $data, if you noticed you will see the number of rows returned outputted. This is positive sign I hope!
Then as it an object we access it as such and get our results! A quick GM on the Table dataand we can do
$data.Tables[0]|GM
Then we can do a quick
$data.Tables[0].Item
There you go, we can see the data, very useful 🙂 Then the only thing left to do is to close the connection to the database gracefully and then do whatever we want with the results. As the
$conn.close()
Simple it works, grabbing data from SQLite database… let go back to my aim. I want to be able to ask my PowerShell prompt to define certain acronyms such as CPU based on my SQLlite database. I will upload it for you guys to download if you want to play.
Let’s make this more useful then!
function sqlitequery{
param([string]$query)
$database =”C:\SQLite\Database\Data”
$ds=”Data Source =$database”
$conn=new-object System.Data.SQLite.SQLiteConnection($ds)
$conn.Open()
$data = new-object system.data.dataset
$adapter = new-object System.Data.SQLite.SQLiteDataAdapter($query,$conn)
$adapter.Fill($data)
$data.Tables[0].Rows
$conn.close()
}
Excellent, it now wrapped in a little SQLquery function. So all we need to do is provide the query with the SQL and it will do its thing…I hope this works!
$SQL=”select * from Data where Item=’CPU'”
sqlitequery $SQL
Ace – that worked. Let’s now put this inside another little function to allow us to modify the SQL query, this should then complete our aim! Job done, and coffee break time!
function define {
param([string]$acro)
$SQL=”select * from Data where Item=’$acro'”
sqlitequery $SQL
}
Opps… looks like I have accidently used a reserve keyword, if we do a
get-help About_reserved_words
We can see a list of all the reserved words… so quick change of name on the function and we should be good to go. I admit I should have been thinking right!
function whatis {
param([string]$acro)
$SQL=”select * from Data where Item=’$acro'”
sqlitequery $SQL
}
And as people in my old job would of said “we are cooking on gas!!!”.
A little bit of tidy it up a bit we end up with something like this
function whatis {
param([string]$acro)
$SQL=”select * from Data where Item=’$acro'”
$result=sqlitequery $SQL
if ($result -eq “”)
{write-warning “Not Found”
}else{$result.Meaning
}}
There no error handling, cause that is the way I role, as this is just demo proof of concept stuff. And to be honest, no one will ever really use this use case! But it demonstrates a few things!
So in summary the take away points are that using SQL and SQLlite provider type stuff are easy with PowerShell… You could do some great things with this! Right better go, Grumpy admin has things to do… I really do wonder what tools and things you can do with PowerShell and SQL or SQLlite? Only limited by your imagination I guess…
Hazzy