What are the best methods to prevent SQL creep?

Question

I have a webapp written in PHP using the MySQL database backend. This question can also be easily applied to any language and application trying to use the SQL database and the OOP MVC project.

How do you limit your SQL code to a model?

There is a rather long story, characteristic of my case for a question. As mentioned earlier, I am working on a PHP / MySQL / AJAX site. I developed it using OOP and MVC design principles - using a model, view, and controller. I managed to keep the elements of the view, such as layout and styling, completely limited to the View and make them reusable quite easily. I thought I did the same with SQL code. But as the work progresses, it becomes pretty clear that the model needs serious refactoring.

The way I found to support SQL in the model was to encapsulate each SQL query in its own Query object. And then, when I needed to call any SQL in the view or controller, I would query the query through the factory. There is no SQL code in the controller or view.

But it became unusually tiring. I don’t think that I am actually typing anything by doing this and spend too much time creating queries with names like "SelectIdsFromTableWhereUser". The factory for queries approaches thousands of rows. A little search in Eclipse showed that the vast majority of these queries are used in one or two places and never again. Not good.

I know that in good MVC you want SQL to be completely different from Controller or View. But at the moment, it seems to me that it would be better to just place SQL where it was necessary in the code, and not try to bury it and the database code deep in the model. These queries are used only once, why encapsulate them?

Is it important to keep SQL separate from Controller or View? What is the result of this? What is lost allowing it to spread? How do you solve this problem?

Change For the query, here is a little more details about my model.

There are two parts. Part "Tables" and part "Requests". The Tables section stores domain objects, which are mainly intended as wrappers around class objects, which are exact analogs of tables in the database. For example, there might be a Foo database table with id , name and type fields. There will be a Table object ( class FooTable ) that has an array with the fields "id", "name" and "type". It will look like this:

 class FooTable extends MySQLTable { private $id; private $data; private $statements; public function __construct($id, $data=NULL, $populate=false) { // Initialize the table with prepared statements to populate, update and insert. Also, // initialize it with any data passed in from the $data object. } public function get($field) {} public function set($field, $value) {} public function populate() {} public function update() {} public function insert() {} } 

If there is a fooBar database fooBar that has a one to one relationship (one Foo many Bars ) with id , fooID and bar fields, then there will be a fooBar Table object ( class FooBarTable ), which will look something like the above FooTable .

The FooTable and many FooBarTable will be contained in the Foo object. Give the object Foo factory id to the table Foo , and it will fill itself with Foo data and all its bar and their data.

Query objects are used to pull these Foo identifiers in the order in which they are needed. So, if I want Foo objects ordered by date, voice or name, for this I need another request object. Or, if I want to select all Foo objects with bar in a certain range. I need a request object.

In most cases, I use table objects (shells, not base tables) to interact with the database. But when it comes to choosing which table objects the queries are in.

In the original design, I did not think that there would be too many requests, and I thought that they would be things that would see reuse. Since there may be several places where I need Foo in date order. But it didn’t work out that way. There are more ways than expected, and most of them are a single shutdown, which is used once in a view or command, and then never again. I also thought that queries could encapsulate quite complex SQL, and it would be nice to have them as objects, so that I would always be sure that they would provide them with the necessary data, and this would be a relatively sanitized environment in which you can test the SQL query itself But again, this did not work. Most of them contain fairly simple SQL.

Answer 1

To start with the last question: what happens is a separation of problems or simple English, “Connect things that belong together.” The keyword here belongs, which is a rather subjective word.

In the early days of PHP, many people found that everything on the same page belonged together. Since PHP was an acronym for "Personalized Home Page", its design goal was small sites with multiple pages, and then this sense of belonging makes perfect sense.

When things grow, the sense of belonging changes. When we get a complex data model that needs to be consistent and that needs to evolve over time, then suddenly the operations on this “model” begin to “belong” together, because it becomes too difficult to dig SQL operations and queries around the place. Therefore, in order to maintain control, we need all operations with the model on one page.

As a practical approach, I like to draw a line in the sand between the user interface and the model and define there an API that encapsulates the goals of the mini-user (the user needs to add something to the cart → getPromotions, the user wants to add something to the basket: the addToCart method is necessary and etc.).

I like to draw a line here because it captures the wishes of the user, the responsibility of the user interface is to make the user accessible in a simple and effective way, the responsibility of the service / model / storage level is to recognize this desire.

If everything is done correctly, it is also located at the level 1 step, remote from the user. However, many developers confuse what the user wants with the functions that need to be implemented. Then you get very inefficient maintenance methods, such as saveProject (The user does not want the project to be saved, she just wants him there the next time she logs in. This is taken for granted, and therefore it does not have API service locations). This implementation-based API leads to paths of almost empty wrapper methods.

Things like repositories, etc., are a way to structure this level of service.

This user-oriented API approach also tends to clear views (the contents of display elements can be retrieved with a few service calls) and controllers (the action then consists of a regular disinfection and usually a single method call).

Answer 2

Why not use repositories ? It seems to me that this would be a simple and easy way to encapsulate SQL. Your current approach seems unnecessarily complicated.

The NerdDinner tutorial has a good example of using a repository in the context of MVC; even if it's not in PHP, hopefully this will give you an idea of ​​how this template works.

Answer 3

It is impossible to give good advice without knowing what you are doing, but it is clear that something is probably very wrong.

From what you said, it seems like you're right in thinking that your model needs some kind of major refactoring. In fact, it looks like he needs a serious redesign (this means that the API used by your controllers and views to access it will change).

Some thoughts:

You speak:

The way I found to save SQL to the Model was to encapsulate every single SQL query in its own query object. And then, when I needed to call some SQL in the view or controller, I will access the query through the factory. There is no SQL code in the Controller or View.Controller or View.

It makes me think that you are lacking in meaning. The model should be more than a bunch of template code, so SQL does not appear (gasp!) Appears on some kind of controller. Your model should be the model of your domain object . What you described is simply an inefficient SQL proxy.

It may be useful if you post some examples of how your model will be used. That is, edit your question to include some examples of how your controllers and views use your models.