SOLID stored procedures and functions

Question

I want to use stored procedures more in the application I'm working with. The application is looking for several dozen databases. The application stores information in its own database.

I was thinking about shifting business logic to stored procedures in specific databases. Therefore, if the logic is common to all external databases, then hold it in the application (.NET). If the logic is database specific, then create a stored procedure.

I'm not sure how SOLID works with stored procedures and functions since there are no interfaces or abstractions. The following publication seems to suggest that you should try to combine queries: http://ledgersmbdev.blogspot.co.uk/2013/02/building-solid-databases-interface.html . For example, if a stored procedure has four SQL statements, why not try to combine them into one SQL statement? Is that what the post says? Is this a SOLID approach?

Answer 1

I was thinking about shifting business logic with stored procedures in specific databases. Therefore, if the logic is common to all external databases then they hold it in the application (.NET). If the logic is database specific, then create a stored procedure.

This expression is of great concern to me. When you say that logic is specific to a database, to which I assume that you mean one of the twelve of the above, this is a design flaw. Databases are simply such that in the information repositories they do not need any "special" logic to access them outside any representation structure. In addition, if the data needs to be manipulated using what is not installed , than you need to put it in your application. That is, you do not perform your calculations when they can be compensated for your application.

When developing your application, you must make sure that you do not modify your database in order to ignore the relational model. In my experience, this is one of the best ways to make your application unmanageable and slow. To clarify, business logic doesn't have to live in a database, it makes it tough so others can use your data. A typical argument against this is that “I'm the only one using the data”, which I say is a terrible reason for the design.

Moving on, you should try to determine what actually works in the relational (given) model, and create an application that can request this data. Instead of creating a database appropriate for your application. At the same time, SOLID DOES NOT WORK WITH REAL MODELS AS THEY ARE NOT OBJECTS ORIENTED
Wiki

In computer programming, SOLID (Single Responsibility, Open-closed, Liskov, interface segregation and dependency inversion) is a mnemonic acronym introduced by Michael Perse for the "first five principles" identified by Robert C. Martin 1 [2] in the early 2000s [3], which means the five basic principles of object-oriented programming and design.

UPDATE FROM COMMENTS

The application connects information from different systems and decides when a group of records can be deleted (these are general rules that apply to all systems). There are specific database rules that must be applied before deletion. I was thinking about offsetting local business rules for stored procedures. -

Looking at this comment, I do not quite understand what rules of a particular database will be set outside of audit / soft deletions. I would agree that the rules for a particular database can be set to administrative stored procedures in the database, where you should draw a line when you get problems like the following:

My application requests for historical data, if they are not older than 6 months , then should be extracted from offline storage.

The option that you have to delete this data after 6 months should allow the application to clear it using some business logic or create a scheduled task for this cleaning, since this is a normal database operation to delete tuples.

My argument here would be to place this in a database and reject your application from calling these procedures. In fact, your application should not even know that the database exists in a properly abstracted application. Therefore, if this is the example you are proposing, then my solution would be as follows:

 1) Create stored procedures in the database that only a maintenance based user can invoke, NOT THE APPLICATION 2) Create a database scheduled task to run these based on your data needs. 

Answer 2

I agree with the concept of not putting business logic in SQL, as well as the concept of SQL, which is a set-based language.

Think of SOLID as a special OO implementation of some really good programming practices and apply them to any code you write, including SQL. A good table layout design will incorporate SOLID ideas.

I spent too much time debugging large complex stored procedures, so if you can avoid this, do it. If you must write stored procedure code, then SOLID can help you.

For example, I have a large stored procedure that queries dozens of tables and returns a dataset for use by the printer to send greetings . This is a classic example of a business process disguised as a “report” written in SQL.

Looking at this operation with a SOLID lens:

He should have a separate responsibility and the only reason for change.

The code determines who receives the letter, as well as what data falls into this letter. If any aspect of the process changes, you need to update and test the entire system. The solid principle is to have a function that determines who receives the letter and what is in it.

It can be as simple as having one request that returns a list of clients. Pass this as an array of identifiers or TVPs to another stored procedure to collect data.

Open / close

In most SP (stored procedure) procedures, the entire procedure is either hardcoded or “data driven”. Data is provided, as a rule, for working with SQL. The SQL flow is changed using CASE, discarding the value of a field or SQL in text fields that receive EVALd.

The SOLID approach to my problem can be seen in the “basically SP that we just created. Now I have an SP that calls GET_CUSTOMERS and another GET_DATA . This wizard basically controls the workflow and this workflow should not change. The wizard is closed for modification.

If we need to improve the system for sending letters, and emails have different data fields, I can change it to GET_DATA with a call to GET_DATA_FOR_EMAIL . Open for change.

The owner has one responsibility. If the workflow changes, then this wizard changes in accordance with rule No. 1.

Liskov substitution principle

This is very OO, but to create a point, we can say that GET_CUSTOMERS SP can be considered as a replaceable object here. I should be in another search, say GET_CUSTOMERS_WHO_NEED_DIFFERENT_LETTER and be able to reuse my code. Or maybe GET_CUSTOMERS_FOR_INTERNAL_TEST ? If all the components of your system are single-position and cannot be reused, the system will be more difficult to maintain and understand. It could be a stretch. Think less about overlaying OO objects and functions with similar arguments that can be reused.

Dependency Inversion Principle

This is the biggest mistake I've seen. Do not write code around selects and where clauses. When my system started up with a table, a table * SELECT * FROM, where customer.letter-sent = false * (actually more than 1 thousand lines), I hope that the search for all clients ends and send them an email. Start with abstractions and code, where the highest level controls the workflow in SP.

The idea is that the abstraction "finds customers" and returns a list of customers. I have to swap this logic for something else in the code. The violation of this, which I often see, is to "find clients" who write to the table connected to the client table, and then another SP that reads this table. In this case, your abstraction leaked into the implementation, and you switched from reusable code to a system that sends letters to a specific table.

SQL is very efficient, and it is too difficult to combine steps that must be separate in the abstractions that will be implemented in the implementation.

Stored procedures are a good tool that helps the reader work in a team to increase productivity and provides convenience through scalability. This is still code and theft of templates from other languages ​​will only help if your overall design.

Very often you do not need a design template if you need a simple one. If your code is more than 50 lines or so, try moving it to the application level and / or think of solid re-factoring.