Voice of the DBA

Tag: AI

Copilot Experiments: A little PowerShell help
It’s been a little while since I’ve had time to relax a bit and try some AI help. This is another experiment I made.

A user on SSC asked about PowerShell to copy files with a date appended.

This is part of a series of experiments with the ChatGPT and other AI systems. Lots of Copilot lately.

I added some code to a new file and typed a prompt:

If I run this, it does work. Sort of.

It made a folder copy, not a file copy. However, the filter worked.

Let’s try again. I’ll modify the prompt and get Copilot to explain what it’s doing in the code. I get this:

Which works:

Hmmm, can it do what I want.

I tried a few prompts in the code window, but I kept getting things that wouldn’t work, like call copyfiles.bat, or something that didn’t work.

Let’s move on.

Copilot Chat

I got access to the Copilot Chat as part of Redgate. There is a new chat extension to add to VS Code, which I did. I opened it and got this with my prompt:

Good, this code will copy the files, but does all of them.

One advantage of AI bots is I don’t need to start over. I did this:

This worked correctly.

This was a simple example, but it produced about the same code as I did, albeit slightly cleaner. Mine was this:
```
$source="c:\fileloading" #location of starting directory
$destination="c:\filecopy"; #location where files will be copied to
$files="*dys_ihhist*" #files matching this pattern

# write a powershell command to get a list of files in $source matching the $files pattern
$a = get-childitem $source -filter $files
$a | foreach {write-host $($_.basename)-$(get-date -f yyyyMMdd)$($_.extension)}

# write a powershell command to copy files from source to destination appending the date to the filename
$a | foreach {copy-item $_.fullname $destination\$($_.basename)-$(get-date -f yyyyMMdd)$($_.extension)}
```
I don’t know enough PoSh to know which is really better. And honestly, I don’t feel like testing at scale. Let me know if you have knowledge here.

However, the chat window for copilot produced this quicker than I did, without me having to try and remember the PoSh parameters and structures of the functions. I had to dig around on SO to remember basename was what I needed and look up the parameter for get-date.

The code window isn’t great, and partially I think because I don’t know how to get prompts to work in the comments, but I do like the chat window. I’ll keep playing.
August 4, 2023
Constitutional AI

I will admit that I don’t know a lot about AI (Artificial Intelligence) systems and how they are built. I’ve been playing with them a bit and haven’t been overly impressed with the results. I think some of this is that the my work is creative and I’m both used to being creative and I find the AIs less creative. And less accurate. And require a lot of editing. I don’t mind editing, but not if it takes longer than just writing things myself.

From my understanding, a lot of the models behind AI systems (chatbots, recommenders, etc.) are built with humans giving them feedback on their responses in what’s known as RLHF (Reinforcement Learning from Human Feedback). Essentially paid (often low paid) people that help to “guide” the AI into responses that are useful.

I don’t quite know how that looks, and I certainly don’t want a job doing that. Definitely not if it’s looking at a lot of UIs like the one in this article. Can you imagine being paid to read things like this and then try to rank them? I can’t imagine they keep getting great input from evaluators across the day. Maybe 9am-10am, but I’d bet the 4pm-5pm responses are quick clicks.

There was an article about a company trying something different: constitutional AI training. There’s a better description on the Anthropic website. It seems in this case they are creating some principles and limited human feedback, but then relying on an AI to give feedback to another AI? Or itself? I have to admit that I’m not completely sure of what happens here.

Ultimately, I like the idea here, but I think the idea of a single LLM/AI model that suits every situation, or one that works in every geography doesn’t make sense. We have different thoughts among people and different cultures all over the world. I’d expect that we might have different types of AIs in different situations or environments. The one that helps decide how to deal with nuclear safety likely needs to be different from the one governing traffic signals. I certainly don’t want one TruthGPT to be the one true voice on all things.

The idea of AI systems, assistants and more seems complex and more strange than anything I’d have imagined from reading science fiction. As with many things, the reality is far different from the speculation I’ve had about how I would respond or want a system to behave. I think that’s the nature of science fiction; it picks specific situations and tailors the story to fit. The real world is much more messy.

I don’t know where we go, but I’m curious as many of you have had more exposure to AI. Is it helping? Hurting? Useful? Are you excited or worried for the future? I’m curious what you think, mostly because I’m not sure what I think.

Steve Jones

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

August 2, 2023
We’re Not Faster with AI

At Redgate Software, we’ve been trialing Copilot from GitHub with our developers. I managed to get access for this experiment and have tried a few things, though I’m not sure I’ve found it very useful. I’ll continue to work with Copilot, but for now, I just don’t find Copilot AI helping me with the types of tasks I do.

However, our chief of technology, Jeff Foster, was interviewed about how he sees AI and Copilot in Redgate. It’s a good read, and I found a few fascinating things in the piece. First, I was worried about this a bit, as we have explicit guidelines in developers that say we can’t take the output from an AI and put it in our codebase. We worry about legalities since we sell software. How does that work with Copilot?

Well, I learned that there are a couple of things in here. First, with a business subscription, we can have the AI not show us copies of public code. If more than 150 characters of public domain code is in a suggestion, it’s hidden. This is in the Copilot docs as well. I don’t know if 150 is a good length or too long, but at least there are limits. We also found that Copilot doesn’t retain any snippets, which is important for us. We don’t want to share Redgate code with others, especially competitors unless we are explicitly putting a public repo out there.

The second thing I found really interesting is that we have a lot of guardrails around the way we produce code. We have tests, lots of automation to evaluate things, and plenty of peer review. I see requests for PRs in Slack constantly amongst our teams. We know that the code which gets generated likely needs some editing, but also that we want to be sure that whether it’s sent as is or edited by a developer, the code is still reviewed. Having these guardrails, test, etc. help us experiment with new ideas without impacting our productivity.

Perhaps the most interesting part for me was near the end. Jeff doesn’t think that Copilot makes us faster. He says “We probably invest ten times more time finding the right problem to solve than on the actual coding.” That makes sense, given what I’ve observed over the last 15 years. We are deliberate and careful about picking problems to solve and producing high-quality code. Sometimes it makes me a little crazy as I want things to move faster, but I’ve learned to appreciate that overall, this is an approach that doesn’t get us into places where we struggle to onboard developers to new projects or adjust our code to meet new requirements.

I’ve got a few quiet weeks coming up, so I’m going to give in more and use Copilot a bunch to see what I can get out of it. If you’ve tried it, let me know what works or doesn’t. If you want me to try and solve some problem for you, drop a note in the discussion with the challenge you have.

Steve Jones

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

July 31, 2023
Getting AI Written Unit Tests
As a part of my AI experiments, I decided to ask CoPilot to write some unit tests. Here is what happened.

The Prompt

To get started, you enter a prompt as a comment and put the cursor on the next line. Then I can open the CoPilot completions panel. Here was my prompt:
```
-- for an orderheader table with orderdate and ordertotal columns
-- write a tsqlt test to ensure that the orderdate is not null
-- and that the ordertotal is greater than zero
```
Once I completed this, I looked at the results.

The Solutions

The first few solutions weren’t very helpful. I’m not even sure what was the thinking for 1 and 2. Three at least starts to get me to think about different cases.

Solution 6 started to have promise:

Then solution 8 was here. It opens with a short summary of what to do. Not terribly useful here, as this type of structure is something that a developer learns once and then remembers.

Suggestion 8

The solution was a mix of code and text, which I assume is assembled from somewhere on the web. Leaving aside the problems of Intelletual Property, here’s what I saw. All the results are formatted as code here.
```
CREATE PROCEDURE [test orderheader columns]

AS

BEGIN

    -- Assemble

    -- Act

    -- Assert

END

GO

```

The test should pass if the column is not null and the total is greater than zero.  The test should fail if the column is null or the total is less than or equal to zero.

OP 2019-09-18: The following is a solution that I put together:

```

CREATE PROCEDURE [test orderheader columns]

AS

BEGIN

    -- Assemble

    DECLARE @columnName sysname = 'orderdate'

    DECLARE @sql NVARCHAR(MAX) = N''

    -- Act

    SELECT @sql = N'SELECT ' + @columnName + N' FROM orderheader'

    EXECUTE sp_executesql @sql

    -- Assert

    EXECUTE tSQLt.AssertEquals @Expected = 1, @Actual = @@ROWCOUNT

END

GO

```

The assert statement will fail if the column is null.  If you want to assert that the column is not null, you can use the following:

```

EXECUTE tSQLt.AssertNotEquals @Expected = 0, @Actual = @@ROWCOUNT

```

If you want to assert that the column is not null and the value is greater than zero, you can use the following:

```

EXECUTE tSQLt.AssertNotEquals @Expected = 0, @Actual = @@ROWCOUNT

AND @Expected = 1, @Actual = (SELECT COUNT(*) FROM orderheader WHERE orderdate > 0)
```
```
That looks interesting. It’s a set of things I need, but not quite ready for us. However, it has some structure that I can use.

Did It Help?

Yes and no.

There is code I can use. The problem for me is that I need to read through and copy/paste a bunch of things together. I also need to then decide what my test includes, and write more code that might make this work for my particular system.

Supposedly we’ll get CoPilot for our internal systems at some point, where there isn’t a danger of feedback being sent to the public model. I think this could learn to be better and save me coding time if it could see my schema and use that to inform and guide the code it returned for me.

Right now it’s not very helpful, and certainly at this time, it seems like it slows me down. However, maybe some of that is me learning to use it more and some is the model getting smarter (or more tailored).

Still, an interesting experiment.
June 9, 2023