At $dayjob I am blessed with the task of wrangling a 20+ year old codebase of SQL Server stored procedures. It's around 300k lines of monkey tested, business-critical code that:
* wasn't source controlled
* was never tuned (properly) for performance
* deployed into environments by editing/executing sql in SSMS.
* of course, no automated tests, etc...
It's a windows shop, I am developing on a Mac, and we do linux on Github Actions.
I selected tools like PowerShell Core, sqlcmd, docker for running Windows SQL Server instances, RedGate SQL Compare for extracting existing schema and code from the legacy servers, tSQLt for unit testing, TSqlLint for code compliance, SQLFluff for style compliance, and Flyway for deployments.
We quickly discovered PowerShell Core was the most interoperable cross-platform scripting shell when Windows had to be one of the platforms.
It wasn't pleasant to code in. The regex engine comes from .Net, which has bad catastrophic backtracking problems, and the array situation was goofy. Launching executables with any sort of control over the launch and capturing the output stream was hit or miss - I would often have to launch a process, redirect its output to a temp file, and then read the temp file after the child exited. So piping around with child's stdout into a string variable was always more trouble than it should have been.
BUT, PowerShell Core executes fast (nice job M$FT, if there's one thing you do well, it's micro-optimizing!) It has nice tools for interacting with the user, like ascii art list pickers and easy input prompt generators. And, most of the strange quirks in dealing with the Windows file system are papered over if one avoids folders with locked files.
Anything you want to achieve can probably be done if you search hard enough. Recommended!
* wasn't source controlled
* was never tuned (properly) for performance
* deployed into environments by editing/executing sql in SSMS.
* of course, no automated tests, etc...
It's a windows shop, I am developing on a Mac, and we do linux on Github Actions.
I selected tools like PowerShell Core, sqlcmd, docker for running Windows SQL Server instances, RedGate SQL Compare for extracting existing schema and code from the legacy servers, tSQLt for unit testing, TSqlLint for code compliance, SQLFluff for style compliance, and Flyway for deployments.
We quickly discovered PowerShell Core was the most interoperable cross-platform scripting shell when Windows had to be one of the platforms.
It wasn't pleasant to code in. The regex engine comes from .Net, which has bad catastrophic backtracking problems, and the array situation was goofy. Launching executables with any sort of control over the launch and capturing the output stream was hit or miss - I would often have to launch a process, redirect its output to a temp file, and then read the temp file after the child exited. So piping around with child's stdout into a string variable was always more trouble than it should have been.
BUT, PowerShell Core executes fast (nice job M$FT, if there's one thing you do well, it's micro-optimizing!) It has nice tools for interacting with the user, like ascii art list pickers and easy input prompt generators. And, most of the strange quirks in dealing with the Windows file system are papered over if one avoids folders with locked files.
Anything you want to achieve can probably be done if you search hard enough. Recommended!