jdaigle / horton Goto Github PK

As a developer,
I want a Powershell Module
In order to be able to easily execute common database development tasks

E.g.

• dbup (update all databases in my project based on conventions)
• new-migration -database -name (not "add-migration" due to conflict)

Given a file or files containing tuples, Horton should be able to synchronize a table with the values in this table. E.g. adding new rows, modifying exiting rows if different. Optionally removing rows that aren't in the "seed" data.

The schema_info table is completely redesigned for as of 4.0.0. This renders horton.exe incompatible with existing schema_info tables.

It likely possible to detect the old schema and seamlessly upgrade. Perhaps following this general flow:

1. Rename old table. Create new table.
2. For each row in old table, compare using old hashing algorithm to files on disk.
3. If old hash matches file on disk, recompute hash and insert.
4. If old hash does not mach file on disk, or the file does not exist, insert a empty hash. This will force horton to correctly detect changes.

Hi - I'm maintaining my own version of Horton that works slightly differently, but I recently found a bugfix that you might want to pull into the main version as well. Explanation and small change are here: mreishus@02c7a95

Does Horton support desired state for stored procedures? If so, could a quick example of this be added to the documentations?

I thought it did, from reading this blog post, but I tried and I can't get it to work.

Here's what I did:

• I added Horton.exe 4.0.0 to my project root dir and wrote a ps1 script to run it across my DB.
• I added a "Migrations_Horton" directory.
• Inside that directory, I included a "StoredProcedures" directory.
• Inside there, I added uspGetBillOfMaterials.sql, copied from commit f432b88.

Then:

• I ran Horton.exe UPDATE which did create the proc on my db! Success. ✅
• Eliminate drift check: I modified the proc directly on the SQL server using SSMS. I ran Horton.exe UPDATE and expected horton to bring the proc back to the committed state. It didn't. I also tried SYNC.
• Update check: I edited the uspGetBillOfMaterials.sql on disk and re-ran Horton.exe UPDATE. It didn't change anything, I still had the version I manually edited on the server. I also tried SYNC. Still nothing.

Unless I'm missing something major here, it seems that horton no longer supports desired state for stored procedures? Am I doing anything wrong or was this removed in the 4.0 release?

Concepts

• New convention-based directory structure. The root directory should contain a "migrations" subdirectory which contain the one-time and numbered migration scripts. An optional "objects" subdirectory contain scripts which will run anytime they are modified - it'll also traverse subdirectories to support organization. Any other directory is ignored.
• Simplified command line. No more "commands". It'll run migrations followed by objects. If any migration has been modified, then hard-stop.
• By default will prompt before executing scripts; command line switch to run without input (-U)
• Command line switch to warn on modified migrations and re-execute.
• Command line switch to execute scripts as a "baseline". I.e. the scripts won't run, but will simply update scripts table.
• New scripts table to replace "schema_info". Include the text of the script for history.
• Command line switch to create database for local dev

New Directory Structure Conventions

database_dir/
|-- migrations/
|-- objects/
|    |-- sprocs/ (optional)
|    |-- views/ (optional)
|    |-- functions/ (optional)
|    |-- indexes/ (optional)
|-- misc/ (optional)

Command Syntax

Usage: horton.exe [OPTIONS] COMMAND [PATH]

Path:
  Path to directory containing migrations (leave blank for current directory)

Commands:
 UPDATE     Executes current migrations if no conflicts exist.

Options:
  -f, --files=VALUE path to migrations directory
  -s, --server=VALUE         server hostname.
                               (leave blank for "localhost")
  -d, --database=VALUE       database name.
                               (leave blank to look for "database.name")
  -u, --username=VALUE       username of the database connection.
                               (leave blank for integrated security)
  -p, --password=VALUE       password of the database connection.
                               (required if username is provided)
  -c, --connectionString=VALUE
                             ADO.NET connection string.
                               (optional, overrides other parameters)
  -U, --UNATTEND             Surpress user acknowledgement during
                               execution.
  -v, --version              Print version number and exit.
  -h, --help, -?             show help message and exit.

Examples:
 horton.exe -m "\path\to\migrations" -s LOCALHOST -d Northwind -U
 horton.exe -m "\path\to\migrations" -s LOCALHOST -d Northwind -u sa -p pa55w0rd