If I /sync a table from a db to a git repo, is that essentially a "commit" to the repo?

jschwarz_continuus

And if so, can I add a commit comment (like I can in the source control gui) when executing the /sync from the cli?

Alex B

Hi @jschwarz_continuus,

@AlexYates is right - It doesn't do the Git commit to the local clone (or give the option for the push), it just updates the files.

I was conflating a few things in my "essentially" - it's the same as using the "Save changes" on the Commit tab when using the Working Folder method.  This conveniently leaves out that it's targeting Git and needs to be committed for Git and so is just wrong.

Next time I'll have to refresh the forum post before I reply when I happen to leave it open for longer than expected as Alex's message wasn't there when I replied (thus making me look more foolish!).

Apologies for the confusion!

Kind regards,
Alex

jschwarz_continuus

Thanks for answering on a Saturday!.
No, unfortunately, that's not my issue.  When I execute the sqlcompare with the target as the git repo it will not create the files.  However, I've tried a workaround where I make the target a /Script aimed at the same directory as the git repo, and that does create the expected .sql files in the repo.
Now that they are created, I will be able to git add and git commit them.

So to summarize the switches that worked vs not,

these switches did not create sql files in the repo...
/sourcecontrol2 /revision2:HEAD /ScriptsFolderXML:D:\DatabaseRepositories\ADB_DEV-SourceLib.xml /Synchronize /force

... and these switches did ...
/Scripts2:D:\DatabaseRepositories\adb-dev-db /Synchronize /force

I don't think that this workaround was necessary with svn, but it does seem to be needed for Git.  Are you able to confirm that?

AlexYates

It will just update the files. It won't do the commit.

Also, SQL Source Control does a lot of clever stuff behind the scenes that SQL Compare won't do for you:
https://documentation.red-gate.com/soc7/reference-information/how-sql-source-control-works-behind-the-scenes

Alex B

Hi @jschwarz_continuus , 

That is essentially a commit, yes, but there is not the functionality to add a commit message using SQL Compare - that only possible from within SQL Source Control.

Kind regards,
Alex

jschwarz_continuus

@AlexYates , Yes, SQL Source Control is clever, does it have a powershell or cli interface that can be used by a Ci/Cd process to utilize it programatically, or is it only usable via gui in ssms?

@Alex B , you and Alex Yates have different answers regarding whether a commit is made to Git.  Updating files and committing to git are two different things.  Can I get a definitive answer on which it is?

jschwarz_continuus

Thanks for the clarification.  Knowing that, I should be able to "save the changes" and then perform my own commit and push with comments as this cli is being called and executed in a shell script where I can execute git commands after the SqlCompare runs.

AlexYates

@jschwarz_continuus: I'm afraid SQL Source Control does not have a PS/cli interface, but Git does. You could do what you want by running the SQL Compare command first, and then running a few git commands.

However, it's not normally desirable to automate source control commits. Typically in a CI process, a developer will write their code, commit it carefully, and then the automated processes recognise the commit/push and run various builds, tests and deploys etc.

jschwarz_continuus

@AlexYates
I think a Ps/cli for SQL Source Control would be a clever idea!

I'm not going to debate a the merits and shortcomings of a "typical" CI process, but suffice it to say in my experience, the "typical" process does not fit the clients that I've worked with very well.  I find the RedGate tools to be excellent tools, but the typical approach to deployment does not serve everyone equally well.

Thank you for taking the time to assist, and answer!

AlexYates

That's fair enough. Often "it depends". :-)

Good luck with your implementation!

jschwarz_continuus

Hey @Alex B, and @AlexYates.  Now that I have an answer on the commit question, and I'm running this cli to try and create new files in my repository, but the new files are not being created.  For instance I create a new view in the db, I compare the db to the git repo files with a /sync.  I am expecting a new .sql file to be created in the repo directory for views but it is not.  I get a report that correctly identifies it as new, but no .sql file in my repo directory.  Is there a switch in the cli in addtion to sync that I need to use in order to get those files created?

In this case I have a table that already exists and is being update, and 3 new objects, My Cli is...
"D:\RedGate\SQL Compare 13\sqlcompare.exe" /server1:ASERVER1 /database1:ADB_DEV /sourcecontrol2 /revision2:HEAD /ScriptsFolderXML:D:\DatabaseRepositories\ADB-DEV_SourceLib.xml /Synchronize /force /Report:d:\temp\ADB-DEMO_CommitReport.html /ReportType:Classic  /Include:table:\[dbo\].\[DeployVersionControlTest\]  /Include:view:\[dbo\].\[DeployVersionControlTestView\]  /Include:function:\[dbo\].\[DeployVersionControlTestFunc\]  /Include:storedprocedure:\[dbo\].\[DeployVersionControlTestProc\] /LogLevel:Verbose

I get exactly the comparison results that I expect, and the html report is perfect, but no files are getting updated or created in the git repo.  Can anyone spot what I'm missing here to get the files created in the fit repo directory?

AlexYates

In this scenario I would expect the files to be created on the filesystem, but not yet added to Git.

Just to clarify, is it the case that:
 1. The file exists on disk, but is not visible in Git (expected).
2. The file does not exist on disk or in Git (curious).

Assuming scenario 1, open a command prompt and navigate to your git repo and run:
> git status
(It should show you have an untracked file.)

Now run:
> git add .
(This will stage all changes ready to commit. If you wanted to cherry pick specific changes replace the . with the file names.)

Finally:
> git commit -m "your commit message here"

Now the commit should show up in Git.

(If you wanted to automate this task, following your schema compare, of course you could.)

AlexYates

Yep, I think that's how it works. /sourcecontrol is read inly but/scripts updates the files. But @Alex B can verify.

Glad you got it working!

Alex B

Hi @jschwarz_continuus,

What @AlexYates says is correct - the /sourcecontrol2 will only let you create a deployment script to update a database that is represented by what's in source control, whereas if you use the /scripts it will update the scripts files themselves (and create new ones for new objects).

I'm not aware of any difference between how this works with different source control systems (svn vs git) and checking in the UI confirms this as there is only the option to create a deployment script (even going against a working folder).

Hope that helps clear things up!

Kind regards,
Alex