Development for Microsoft SQL Server: Unix way
Hi, Habr!
Medium and large projects, in which the whole or a significant part of the business logic is implemented in the stored procedures of the DBMS, have a number of inconveniences in managing the code of the modules. Namely:
Yes, there are commercial products that solve these problems in whole or in part, however, in the opinion of the author, they do it not effectively and not elegantly. In view of this, the mapping of modules and database structure to the file system is implemented, using FUSE (Filesystem in Userspace), in the SQLFuse project. Now followers of the Unix way will be able to go here.
Consider the sequence of actions for mounting an experimental database.
To build and mount the following software is needed:
Ensure that the FUSE kernel module is loaded:
After the software is installed, you need to clone the git repository:
Building the project is done by the teams:
')
If the compilation and build is successful, then you need to configure the sqlfuse.conf configuration file, which is located in the conf directory of the cloned repository:
The auth parameter refers to the group name defined in the sqlfuse.auth.conf file:
Instead of the auth parameter in the sqlfuse.auth.conf file, the definition of username and password in the sqlfuse.conf file is allowed . This is not done on purpose, as theauthor is paranoid when editing connection profiles, someone can peek at the user's password.
Do not forget that the AdwentureWorks2008R2 database must be connected, and the selected user has all the necessary rights to modify the SQL Server modules.
If you have problems with encodings, then try using the to_codeset and from_codeset parameters to specify the direction of conversion:
When an error occurs:
Use the ansi_npw = true parameter to force the inclusion of the QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_WARNINGS, ANSI_PADDINGS options to ON :
Create the advworks folder in the root of the cloned SQLFuse repository and mount our database into it:
You can use the -d option to keep track of errors generated by SQL Server:
Let me give you an example of a few simple actions on server objects.
Thus, management and navigation through the modules becomes like in the usual file directories: version control systems, patches and favorite code editors can be used, that is, the whole Unix philosophy is evident for SQL Server.
If you make a mistake in the description of the module or a disconnection occurred, the file system will return the error -EFAULT (“ Invalid address”).
At the time when the post was written, SQLFuse supported the following modules for reading / writing :
Schemes, tables, views are available only for reading . FOREIGN KEY, PRIMARY KEY and indexes restrictions are not displayed at all . When there will be more free time I will add this functionality.
It was necessary to slightly simplify the syntax of T-SQL, in order to avoid confusion and more flexible module management. Everything remains the same, except that redundancy in the table description has been removed in the definition of constraints and columns. Definitions will look like this:
In parts where the instructions about the target table are commented out, SQLFuse will make the substitution of the necessary code automatically, based on the location of the module file. The name of the module, which is defined in the text of the file, does not matter, it will be replaced with the name of the file, schema and / or table as necessary. In the texts of the modules there can not be several packages of commands, GO
As free time comes, plans to implement:
Who is interested in the idea and who wants to take part in the development - welcome: all the source code is available under the GPLv3 license. The author will be glad to any comments and suggestions.
PS I can tell you about the experience of developing a user-mode file system at FUSE, if anyone is interested.
Medium and large projects, in which the whole or a significant part of the business logic is implemented in the stored procedures of the DBMS, have a number of inconveniences in managing the code of the modules. Namely:
- Lack of versioning and tracking code changes;
- Difficulties in synchronization of test and working infrastructure;
- The scarcity of search, navigation, and code review tools.
Yes, there are commercial products that solve these problems in whole or in part, however, in the opinion of the author, they do it not effectively and not elegantly. In view of this, the mapping of modules and database structure to the file system is implemented, using FUSE (Filesystem in Userspace), in the SQLFuse project. Now followers of the Unix way will be able to go here.
Consider the sequence of actions for mounting an experimental database.
Preparing to mount
To build and mount the following software is needed:
- FUSE module Linux kernel;
- FreeTDS driver;
- Necessary software for assembly. Usually called in popular Linux distributions as base-devel , which includes the gcc compiler, the make tool, and so on;
- GNU Bison and Flex.
Ensure that the FUSE kernel module is loaded:
modprobe -a fuse After the software is installed, you need to clone the git repository:
git clone https://github.com/AlexandrMov/SQLFuse.git Building the project is done by the teams:
make clean make ')
Customization
If the compilation and build is successful, then you need to configure the sqlfuse.conf configuration file, which is located in the conf directory of the cloned repository:
# , [global] # maxconn=1 # , SQL Server appname=SQLFuse # [AdventureWorks2008R2] # / SQL Server servername=192.168.6.50 # dbname=AdventureWorks2008R2 # , sqlfuse.auth.conf auth=advauth The auth parameter refers to the group name defined in the sqlfuse.auth.conf file:
# [advauth] # username=fuse # password=123 Instead of the auth parameter in the sqlfuse.auth.conf file, the definition of username and password in the sqlfuse.conf file is allowed . This is not done on purpose, as the
When specifying a server instance, for example, SQLServer \ SQLEXPRESS , the "\" character can be omitted . This is also true for the username parameter if you need to log in via Active Directory.
Do not forget that the AdwentureWorks2008R2 database must be connected, and the selected user has all the necessary rights to modify the SQL Server modules.
Known Issues
If you have problems with encodings, then try using the to_codeset and from_codeset parameters to specify the direction of conversion:
from_codeset=UTF-8 to_codeset=CP1251 When an error occurs:
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.Use the ansi_npw = true parameter to force the inclusion of the QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_WARNINGS, ANSI_PADDINGS options to ON :
Mounting
Create the advworks folder in the root of the cloned SQLFuse repository and mount our database into it:
mkdir advworks ./sqlfuse -o profilename=AdventureWorks2008R2 ./advworks You can use the -d option to keep track of errors generated by SQL Server:
./sqlfuse -d -o profilename=AdventureWorks2008R2 ./advworks &> ./advworks.log & Basic actions on modules
Let me give you an example of a few simple actions on server objects.
- View and navigate through the database structure in MidnightCommander :
- Creating a new field in the Person.Person table and deleting it:
echo "COLUMN TestColumn NVARCHAR(50) NOT NULL" > ./Person/Person/TestColumn rm ./Person/Person/TestColumn - Copying the procedure from the HumanResources scheme to the dbo scheme:
cp ./HumanResources/uspUpdateEmployeeLogin ./dbo/uspUpdateEmployeeLogin - Search procedure by name in all schemes:
find ./ -maxdepth 2 -iname *Employee* -type f -print
Thus, management and navigation through the modules becomes like in the usual file directories: version control systems, patches and favorite code editors can be used, that is, the whole Unix philosophy is evident for SQL Server.
keep in mind that truncate is not supported yet, which means execute the commandwill not work. Although the operation was blocked at the file system level, it was still not recommended to do this, as it can lead to a re-creation of the column and data loss!echo " NOT NULL" >> TestColumn
Always save the original modules before editing!
If you make a mistake in the description of the module or a disconnection occurred, the file system will return the error -EFAULT (“ Invalid address”).
Supported Modules
At the time when the post was written, SQLFuse supported the following modules for reading / writing :
- Stored procedures, functions;
- Restrictions CHECK, DEFAULT;
- Columns and triggers.
Schemes, tables, views are available only for reading . FOREIGN KEY, PRIMARY KEY and indexes restrictions are not displayed at all . When there will be more free time I will add this functionality.
It was necessary to slightly simplify the syntax of T-SQL, in order to avoid confusion and more flexible module management. Everything remains the same, except that redundancy in the table description has been removed in the definition of constraints and columns. Definitions will look like this:
- Columns:
/*ALTER TABLE Person.Person ADD/ALTER/DROP*/ COLUMN TestColumn DATETIME -- ... - CHECK limit:
/*ALTER TABLE Person.Person ADD/DROP*/ WITH CHECK CONSTRAINT CK_Test CHECK -- ... - DEFAULT constraint:
/*ALTER TABLE Person.Person ADD/DROP*/ DEFAULT (GETDATE()) FOR (TestColumn) -- ...
In parts where the instructions about the target table are commented out, SQLFuse will make the substitution of the necessary code automatically, based on the location of the module file. The name of the module, which is defined in the text of the file, does not matter, it will be replaced with the name of the file, schema and / or table as necessary. In the texts of the modules there can not be several packages of commands, GO
Epilogue or what's next?
As free time comes, plans to implement:
- Support for constraints and indexes, as well as manipulation of tables, views, and diagrams;
- Logging of SQL commands and outputting them through a channel or just a file;
- Saving backups when manipulating modules, support for rollback in case of errors;
- In the next post, describe the formation of table definitions, and the transformation of the structure into SQL scripts.
Who is interested in the idea and who wants to take part in the development - welcome: all the source code is available under the GPLv3 license. The author will be glad to any comments and suggestions.
PS I can tell you about the experience of developing a user-mode file system at FUSE, if anyone is interested.
Data sources
- The AdventureWorks2008R2 database is http://msftdbprodsamples.codeplex.com/releases/view/59211 ;
- The GPLv3 license is http://www.gnu.org/licenses/gpl-3.0.html ;
- FreeTDS - http://freetds.org/ ;
- GNU Bison - http://www.gnu.org/software/bison/ ;
- Flex - http://flex.sourceforge.net/ ;
- FUSE - http://fuse.sourceforge.net/ ;
- Wikipedia article on Unix Way - http://ru.wikipedia.org/wiki/Philosofiya_UNIX .
Source: https://habr.com/ru/post/227185/
All Articles