Voice of the DBA

Author: way0utwest

A New Word: Mottleheaded

mottleheaded – adj. feeling uneasy when socializing with odd combinations of friend and family, or friends and colleagues, or colleagues and family – mixing a medley of ingredients that don’t typically go together, which risks either watering down your identity into gray much or accidentally triggering some sort of explosion.

Mixing groups of people from different situations has sometimes been a struggle for me. I’ve felt quite mottleheaded at times when I have people I work with in coaching, at Redgate, and from friends/neighbors/family. It seems that they can be very different people.

I’m sure I’m overthinking this, but I’ve rarely invited different groups of people to the same place, precisely because I do worry that someone might not enjoy the time with those others. I know I’ve felt awkward at times at horse-related things for my wife. I don’t mind and am happy to support her, even if it’s strange for me.

From the Dictionary of Obscure Sorrows

September 20, 2024
Bad Stored Procedures

I don’t see a lot of SQL at The Daily WTF, but this one was great. It’s a stored procedure that was likely just converted from embedded code, as noted by the poster. It’s a strange set of code, that doesn’t quite make sense to me, and I can’t imagine why someone wrote it. Arguably, this is no better than having this code in a C# or ASP.NET application.

Or is it?

I think it is better. If I saw this code in a review or even in a production database, I could work on cleaning it up, adding protection against SQL Injection, and even tuning how it works to reduce the load on the database. I could likely wrap testing around this and get it deployed way quicker than if I were trying to update the source code for an app. More importantly, this is centralized code. If this is called from multiple places in the app code, I’ve fixed it once, not requiring an app developer, who has other work being piled on them, to spend time updating repeated sections of the code.

Even better, I could refactor some of the schema behind this stored procedure and easily find that my changes might affect this code. I could add a feature flag to this procedure and slowly migrate my schema in the background, without disturbing the user, again because the code is centralized. That’s a technique that most developers use in C#/Java/Python/etc., so why not in SQL?

I find it very interesting that a lot of developers refactor their classes and methods to better adhere to SOLID or some other practice, and they are happy to remove repeated code in their language. Yet, they don’t want to implement a stored procedure or function into their calls, essentially creating a database method for the things they need.

The more I work with legacy systems, the more value I see in using stored procedures. Every developer ought to know how to build them, and every developer ought to be able to create them in dev systems so they can easily deploy database and code changes together. More importantly, they can also share the load of tuning queries with operations staff, who may notice things in a production environment that are not apparent in development ones.

The big challenge in all of this is that database tooling is immature. Capturing your database code in source control is hard, and often it is a separate process from the one you follow for application code. I see some companies (including my employer) trying to make this easier, but there is a long way to go, and a lot of habits to change for developers.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

September 20, 2024
When Do We Worry About Scalability?

“We need to get the code written for feature X. Can you finish this query today?”

We’ve all heard some variation of that request. We have a request or demand, and we need to get it done. We need to get code out so our business can advance, sell more things, get more customers, etc. There’s always some reason to get new code pushed to production quickly.

However, many technical people want to ensure their code works well. At least, I believe most do. While most people can write code that works and meets the requirement, some don’t know how to write code that performs well or don’t know how to test their code to check. Often there isn’t a large workload in dev or test environments to verify things.

There may not be a large workload in production either, at least not at first.

So, what do you worry about first: your code being used or performing well? That’s a similar question to this one: Worry about Scalability or Popularity First? While most of us don’t work for a startup and our organizations have some sort of financial stability, does popularity matter?

I’d say that for any feature you build, whether a startup mobile app or a legacy ERP system, you’re still looking at this type of question. You want to know if it’s used, and how often. That might determine if you spend more time on this feature or area. Maybe you have some idea of popularity, or just plan old use of the feature. In that case, certainly make sure it will scale to not only meet your data size now, but plan for some level of growth across the next 6-12 months.

If it’s a new area of functionality for your application, then maybe you have no idea. In that case, the DevOps approach is get something working, a minimally viable version of your code or query, and then tune it later if it becomes a problem. Many technical people approach the endless number of tickets and requests they get like this.

The problem is management often doesn’t budget in time to clean up the technical debt (to care about scalability).

My view for database code is that we should always be leveling up our database code knowledge. If we deploy bad code in production, and can’t fix it, then at least we can avoid adding to the problem by writing the same poorly performing code again. Learn a better way to write that type of query. Whether you’re splitting strings, finding islands and gaps, calculating running totals, or anything else. Learn what works well and write that code next time.

That helps your team balance the scalability and popularity-chase by producing good code the first time. Or at least, the next time.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

September 18, 2024
Parsing EXE Output in PowerShell
I saw a post internally that asked this question: Anyone have a handy powershell script testing if the installed flyway version matches a specific string?

That seemed simple, but getting program output from PoSh wasn’t something I’ve tried. So I tackled the challenge and this is what happened.

Getting the Output

The first thing I wanted to do was actually figure out what the output of checking the version was from the CLI. I looked at the help and noticed a version verb. When I run that, I see a bunch of lines of output.

A lot of output. I need to parse a bunch of strings, and then find a line.

My first experiment was to run this to get a file with this output.
```
flyway version > fwversion.txt
```
Now, let’s parse this.

Parsing Content

It’s been awhile since I read stuff from a file, but I know Get-Content works to read the file. What about finding a line. I saw this post with an answer that noted Select-String can be used, so I decided to try that.

Here’s a first cut of code:

That didn’t work. However, with some experiments, I tried this code:
```
Get-content fwversion.txt | select-string 'Edition'
```
That worked.

Now, I’ll assign that to a variable with this code:

$a=Get-content fwversion.txt | select-string ‘Edition’

Next, I’ll split this string by spaces into a new variable with this:

$b = $a -split(‘ ‘)

Then I can evaluate the various element of $b. You can see below the first and third elements are what I’m interested in. Really the third. Remember, PoSh is zero-based.

That let’s me parse the output, but I don’t want to save a file. Now on to the next step.

Capturing the Output from a Program

One of the things I know you can do in a PoSh ptompt is run a program. The redirection operator allows you to move output. When I tried it, I couldn’t quite get the output I wanted, but I did find this post that helped. With that, I ran this code:
```
$a = & "flyway" --version  2>&1 | select-string 'Edition'
```
This runs Flyway, captures the output in a stream and then uses the code above to find the right line. I assign this to a variable.

Almost there.

Adding a Parameter and a Test

Since I want to call this from the CLI and pass i a parameter, I added a param() clause to my script and then a test that compares the version output from the flyway.exe to the parameter. That gest me this code:
```
param(
    [string]$versionToCheck=""
)
$a = & "flyway" --version  2>&1 | select-string 'Edition'
$b = $a -split(' ')
if ($b[3] -eq $versionToCheck)
{ Write-Output("$($b) installed")}
else {
```
Write-Output(“wrong version – $($b) installed”)
}
Now I can call this from the CLI and check things. It works well. At least for now.

I am certainly not a PoSh expert, but this short script took me about 15 minutes to write with a little research. Then a little testing and I sent it off to the requester. Haven’t heard any complaints, so I’m hoping this actually works for them.
September 18, 2024