Git and Microsoft SQL Server

Hello to all!

In the previous post it was told about the difficulties experienced by developers when writing SQL-code (moreover, these problems are relevant not only for MS SQL Server). Here is a story about how to use Git for version control of SQL Server code using SQLFuse . The principle is the same as with regular files, but there are some features.


')

Major changes in SQLFuse

Since the release of the first publication on Habré, SQLFuse has been rewritten for use as a deploy system, which predetermined the following new qualities:

• Changes made to files and directories are accumulated in the cache, then the timer resets the SQL commands to the database, wrapping them in a transaction. If a single operation fails, all changes are rolled back and the cache is cleared; the hierarchy of files and directories is aligned with their actual state relative to the database. Instead of resetting the timer changes, for example, it was possible to do interaction with the D-Bus, but this is a further stage in the development of the project.
• The text content of the table module files (columns, restrictions, etc.) does not display the name of the module, so as not to mislead the version control system: the name of the file or directory will always be the name of the module.
• Optimization of SQL commands before dumping them into the database. As it turned out, when patching files on files, they are deleted, then created along with new data, is unacceptable for the database due to the possibility of data loss. Therefore, such operations are converted, if possible, to a single ALTER command. Moreover, Git completely removes the directories for which the patch is applied, and then creates them again with the patch changes - this problem has also been solved.

Still not working :

• Support creating / editing views. Please note that the format for displaying views may change.
• XML indexes
• Extended Attributes
• The order of the columns when creating tables. That is, when creating a table, the columns will be created in the database in the order in which they were created on the file system; I hope that this problem will be solved in future versions of SQLFuse.

I wanted to express my gratitude for the feedback and testing of SQLFuse with SQL Server 2000. After testing, it turned out that it would not work to make SQLFuse work with SQL Server 2000, as there are no metatables in the latter, and there is no adequate XML support at the language level that is used to generate code for table modules.

Why git?

Git does not create additional directories with its service files in each directory of the observed structures. So, for example, comes Subversion. Creating SQLFuse directories is perceived as creating a table or a schema, and the files inside them as modules with textual definition, so creating Subversion service directories is not possible.

Most likely, SQLFuse will be able to make friends with Mercurial, I will be happy with bug reports or read a story about integration experiences.

Development scheme and necessary infrastructure

Suppose there are two SQL Server - the first working (work), the second test (test); one Deploy server. The public repository is located on github.com .

Sandra is the primary branch commiter, can make changes to the production and test servers. Bob is a developer who can make changes only to the test server. A test database was mounted on the Deploy server in the home directories of Sandra and Bob users. Only for Sandra is an additional mounted working database.

Deploy server setup

The previous post also explained how to build SQLFuse from source and what dependencies would be required. For more information about this and how to install it into the system, see the SQLFuse project page . As before, the base for the demonstration will be - AdventureWorks2008R2 . It is assumed that a machine running one of the Linux distributions is already configured, using systemd as the initialization daemon.

Creating users in the system:

useradd -m -N sandra useradd -m -N bob 

Do not forget to change the password of users, for example, using the passwd command, and set global variables that are necessary for Git to work.

The systemd user service should be started when the user logs on to the system and mount the database in the appropriate directories. To do this, place the service description files in the ~ / .config / systemd / user / directory, and the SQLFuse configuration files in the ~ / .config / sqlfuse / directory .

Creating the necessary directories:

 mkdir -p ~/workspace/work/sqlserv_1/advworks #          mkdir -p ~/workspace/test/sqlserv_1 mkdir -p ~/.config/sqlfuse mkdir -p ~/.config/systemd/user 

With this setting, after the user is logged out of the system, the services will be terminated automatically, - this feature can be used as an emergency cancellation of the cache reset in the database.

Thanks to the use of the systemd instance mechanism, adding a new service is reduced to copying the file:

 cd ~/.config/systemd/user cp test-sqlserv_1-advworks@test_advworks.service work-sqlserv_1-advworks@work_advworks.service 

The SQLFuse configuration file, which describes the test_advworks and work_advworks connection profiles, is located in the ~ / .config / sqlfuse directory, and in our case will look like this:


To speed up work, specify the maximum number of connections greater than 1 in order to execute queries to the database in parallel, when generating the list and code of the table objects. However, if you set a value greater than 2, then this will not have a special effect, since Git does not know how or does not want to parallelize its requests to the file system.

You can not do the same sequence of steps for all users on the preparation of SQLFuse, if you place the necessary files in the directory / etc / skel .

Initialization of the working environment

In order to start tracking changes, Sandra should make the first commit for the working database to the master branch and send the changes to the bare repository: I'm too lazy to set up GitLab, so for clarity, we will use github.com :

 #      systemctl --user start work-sqlserv_1-advworks@work_advworks.service #        cd ~/workspace/work/sqlserv_1 git init git add -v advworks/ git commit -m 'Initial work commit' #      git remote add origin https://github.com/alexandrmov/adventureworks.git git push origin master 

Next, we will prepare a test database and a testing branch ( initially, a test database should not be mounted! ):

 #     github: cd ~/workspace/test/sqlserv_1 git clone https://github.com/alexandrmov/adventureworks.git #         mv adventureworks/* ./ rmdir adventureworks # ,       git checkout -b testing rm -rf advworks/* #    systemctl --user start test-sqlserv_1-advworks@test_advworks.service #    testing       git add -v advworks/ git commit -m 'Initial testing commit' git push origin testing 

All merges, changes in the history of commits and other operations must be carried out on local workstations, but not as on mounted databases! Therefore, in order not to accidentally break a working or test server, you can write several hooks that prohibit changing the current branches in the repositories where the databases are mounted, and executing any commands except git pull to receive changes from the central repository.

For Bob, setting the work environment is almost the same. You need to clone the public repository, go to the testing branch, recursively delete the contents of the advworks directory , and finally mount the test database by running the systemd service.

Of course, it would be very cool to recreate test structures from a working database by copying a hierarchy of directories and files, but, unfortunately, while views are working in read-only mode and extended attributes are not supported.

Pseudoreal example

Let's say there is a need to ask a dealer discount for each product. To do this, Bob will add a DealerDiscount field to the Production.ProductListPriceHistory table to store the discount percentage. Bob also needs to change the dbo.ufnGetProductDealerPrice function.

Bob’s actions to complete the task will be as follows:

• Synchronize the master branch with the public repository, since you need to use the code version of the modules from the production server.
• Create a column and limit by creating the appropriate files and change the function:
  
• To commit changes in the new branch of dealerdiscount and send them to the public repository:
  

   #       git checkout -b dealerdiscount git add advworks/dbo/ufnGetProductDealerPrice git add advworks/Production/ProductListPriceHistory/DealerDiscount git add advworks/Production/ProductListPriceHistory/DF_ProductListDealerDiscount git commit -m "Dealer discount" #       git push origin dealerdiscount #       git checkout testing git rebase testing dealerdiscount #         git push origin testing 

  
  
• Log in to the Deploy server and make changes to the test database for running in and testing new changes:
  

   cd ~/workspace/test/sqlserv_1/ git fetch git pull origin testing 

  
  
• Create what is called a Pull Request (here you can see an example) for discussion and final verification by the committer before merging into a working database.

As attentive readers could notice, a lot of spaces are generated at the end of text modules (procedures, functions, triggers, etc.) - this is due to the fact that SQL Server does not correctly return the data length from metatables. The actual size can be determined only when sp_helptext is executed : it is always smaller than in the metatable. If you do not use the sys.sql_modules metatable, and immediately sp_helptext , then the data acquisition speed is significantly reduced when executing the git status command because you have to call the procedure in a loop. That is why, to prevent incorrect utilities with files, such a crutch was made. Please keep in mind that when you reset the cache in the database, the end spaces in the text modules are truncated.

After verification, Sandra should merge the new functional with the master branch, making changes to the operational database — actions are the same as for Bob, only with the master branch. If the change transfer process is complicated, then the merge can be performed "manually." After applying the SQL commands, the changes should be recorded and sent to the central repository, and the test and development branches of the new functionality are synchronized.

Conclusion

The development scheme considered is very similar to utopia, where all changes can be made atomically and without errors, however, SQLFuse can only be used to track the module code, with changes made directly to the database using SQL scripts. Or, for test servers, make changes by a mixed method, and for workers, use a script that will generate the difference between commits. Watch out for my githaba account , perhaps this script will appear soon, but I hope someone will get ahead of me and write it first, putting it in free access.

So, the considered approach allows to carry out:

• the ability to control the design of the code when commits;
• data integrity at competitive adjustments of the same SQL Server modules;
• maintaining a history of edits made by users;
• use of resources like GitHub, and web applications such as GitLab;
• more flexible search by code and building dependency graphs between modules without connecting to the database.

The next publication will describe the use of the system for automatically generating documentation for SQL Server modules, as well as the way to create / edit views.