And we will go the other way. Moving the model to the database

Recently, web development from a knee-down product has become a serious engineering discipline. All this was made possible by the efforts of the legion of specialists who developed common practices that allow writing web projects using certain architecture, rather than like a researcher dropping a box of typographic font from the roof of a skyscraper in the hope that it will miraculously fit into the first volume. War and Peace. The most common web programming paradigm is, no doubt, MVC - Model-View-Controller. Speaking primitively, this paradigm provides for the separation of application code into a control layer (Controller), a view layer (View) and a data control layer (Model). In this case, MVC provides that the Controller and View may (but are not required) to depend on the Model, while the Model should under no circumstances depend on them.
There are many different approaches to how to separate the business logic of an application from the logic of display and control. All of them provide that the model is part of the application and interacts with the database, using the latter only as a data warehouse. We will try to go a different way and, as far as possible, bring the business logic of the application to the database level.
Warning : it is better for people with fine mental organization not to see what is going on under the cut.

Introduction

In order not to resemble a schizophrenic, I will say: in my life I happened to see one very large application written in a similar way. Almost all of its business logic was executed at the database level. It was effective in terms of application performance and terrible in terms of programmer performance. True, it was written under ASP.NET + MS SQL Server with its incomparably great capabilities. In our experiments, we will use a hardened bundle: PHP (5.3.1) + Zend Framework (1.11) + MySQL (5.1.4).
So, let's take a close look at our toolkit and let's not get very discouraged (although there is something from it). Tools for working with stored procedures and functions in MySQL are in their infancy, there is virtually no built-in support for working with stored procedures and functions in PHP.

And what will we sculpt? ..

As a Hello-world project, we take a simple blog with posts, comments and tags. Minimal functionality - our task is simply to investigate the principle of model transfer to the database.
We will use Zend Framework as a software platform.

On the assault!

So, our database has the simplest structure of 4 tables:
So, our task is to transfer all (or at least some) business logic to the database layer. To do this, we will use stored procedures and functions stored and executed directly on the database server side.
First, we initialize the Zend Framework application from the command line:

zf create project ./ zf create controller posts zf create controller comments zf create controller tags zf create action add Posts zf create action edit Posts zf create action save Posts zf create action delete Posts zf create action addComment Posts zf create action view Posts zf create db-table Posts sb_posts zf create db-table Comments sb_comments zf create db-table Tags sb_tags zf create db-table PostTags sb_post_tags zf create form Post zf create form Comment zf enable layout 

To begin, write the procedure for saving the post:

 CREATE DEFINER = 'sqlblog'@'localhost' PROCEDURE `Posts_save`( IN title VARCHAR(100), IN text TEXT, INOUT post_id INTEGER(11) ) BEGIN IF ISNULL(`post_id`) OR `post_id` = 0 THEN INSERT INTO `sb_posts` (`title`, `text`, `date`) VALUES (`title`, `text`, NOW()); SET `post_id` = LAST_INSERT_ID(); ELSE UPDATE `sb_posts` AS p SET p.`title` = `title`, p.`text` = `text` WHERE p.`id` = `post_id` LIMIT 1; END IF; END; 

The first two parameters passed to the procedure are the title of the record and the text of the record itself. The last parameter is the record ID. This parameter is not accidentally of type INOUT. The attentive reader could already verify that this procedure has a dual logic: creates an entry if post_id was not transmitted or transmitted as 0, and updates the entry otherwise. After the procedure is completed, the record ID is returned in the same third parameter.
Unfortunately, the convenience of working with stored procedures from PHP leaves much to be desired. In particular, PDO allows, when executing a request, to indicate what type of transmitted parameter is INOUT. In this case, after executing the request for the procedure call, the variable specified as such a parameter will return the value returned by the stored procedure:

 $post_id = 0; $sth = $dbh->prepare('CALL Posts_save(?, ?, ?)'); $sth->bindParam(1, 'Post title', PDO::PARAM_STR); $sth->bindParam(2, 'Post body', PDO::PARAM_STR); $sth->bindParam(3, $post_id, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, 11); $sth->execute(); print("New post ID is: $post_id"); 

However, this remarkable feature does not work in relation to MySQL. Therefore, for normal work with MySQL, you have to pervert and use the variables of MySQL itself. The trick is simple: specify the MySQL variable as the third parameter, and then retrieve its value using the SELECT:

 SET @post_id = 12; CALL Posts_save('Post title', 'Post body', @post_id); SELECT @post_id AS post_id; 

The first request is needed only when updating an existing entry.
To create a record, create the following form:

 class Application_Form_Post extends Zend_Form { public function init() { $this ->addElement('hidden', 'id') ->addElement('text', 'title', array('label' => 'Title:', 'required' => true)) ->addElement('textarea', 'text', array('label' => 'Text:', 'required' => true)) ->addElement('text', 'tags', array('label' => 'Tags:', 'required' => true)) ->addElement('submit', 'submit', array('label' => 'Add Post')); } public function prepareDecorators() { $this->setDecorators(array( 'FormElements', 'FormErrors', 'Form' )); return $this; } } 

Now in the controller code we just need to write the following:

 class PostsController extends Zend_Controller_Action { public function addAction() { $this->view->form = $form = new Application_Form_Post(array( 'name' => 'postForm', 'action' => '/posts/save/' )); $form->prepareDecorators(); } public function saveAction() { $post_id = $this->_getParam('id'); $this->view->form = $form = new Application_Form_Post(array( 'name' => 'postForm', 'action' => '/posts/save/' )); if ($this->getRequest()->isPost()) { if ($form->isValid($this->getRequest()->getPost())) { $result = $this->_helper->procedure()->Posts_save( $form->getValue('title'), $form->getValue('text'), $post_id ); if ($post_id) { $tags = $form->getValue('tags'); $tags = explode(',', $tags); $tags = array_map('trim', $tags); $this->_helper->procedure()->Post_clearTags($post_id); foreach ($tags as $tag) { $this->_helper->procedure()->Post_addTag($post_id, $tag); } $this->_redirect('/posts/view/id/' . $post_id); } } $this->view->form = $form->prepareDecorators(); } else { $this->_redirect('/posts'); } } } 

Expand our functionality slightly. As expected, the author adds tags to each post, which then need to be displayed as a cloud. In order to accomplish this task, we need to at some stage calculate the weight of each tag. There are several options:

1. Calculate on the fly, request for each tag
2. Store the weight of each tag in the table and update it with any changes

The first option is easily solved by the query:

 SELECT t.*, getTagWeight(t.id) AS weight FROM sb_tags AS t 

Here getTagWeight is a pre-created function that calculates the tag weight. However, due to the fact that the tag cloud will be displayed very often (much more often than the order and composition of the tags will change), such a request cannot be considered an effective solution to the problem. A good way out of this situation would be caching the tag cloud entirely, but we will try to solve this problem in a different way.
In MySQL, starting from version 5.0, a mechanism of triggers appeared - procedures that are run when a certain condition is met. Such a trigger is attached and it can work, for example, before inserting data or after deletion. Thus, we can solve the problem of calculating tag weights by hanging two triggers on the link table - sb_post_tags:

 CREATE DEFINER = 'sqlblog'@'localhost' TRIGGER `sb_post_tags_after_ins_tr` AFTER INSERT ON `sb_post_tags` FOR EACH ROW BEGIN UPDATE `sb_tags` AS t SET t.`weight` = `Tag_calculateWeight`(`Tag_getById`(NEW . tag_id)) WHERE t.`id` = NEW.tag_id; END; CREATE DEFINER = 'sqlblog'@'localhost' TRIGGER `sb_post_tags_after_del_tr` AFTER DELETE ON `sb_post_tags` FOR EACH ROW BEGIN UPDATE `sb_tags` AS t SET t.`weight` = `Tag_calculateWeight`(`Tag_getById`(OLD.`tag_id`)) WHERE t.`id` = OLD.`tag_id`; END; 

Thus, at each insertion / deletion to / from the sb_post_tags table, tag weights are recalculated and stored in the sb_tags table, from where they can be extracted by a simple query.
')

findings

So what’s the bottom line? You can write simple projects in this way, but for large projects with this approach, in my opinion, costs per person will grow exponentially with the complexity of the project.
Are there any pluses? There is: it is fast. Even compared to DDD, this is fast. I spent 15-20 minutes writing 13 procedures and 7 functions. And they are written really easy and simple.
Minuses:

• Debugging is bad. I do not know if step-by-step debugging of stored procedures is possible.
• A bunch of PHP-Mysql applied to stored procedures works disgustingly. Perhaps, if you use PgSQL or MSSql, then development efficiency can be improved.
• Due to some limitations of the MYSQL language, some functionality nevertheless had to be implemented by standard means. In particular, I am talking about paginated output, since LIMIT can take only constant values, except when the PREPARE / EXECUTE combination is used.

In any case, this topic should be considered only as an experiment. Discussion is welcome.
I did not consider all the procedures here, but only indicated the key points of the development. For those who are interested in the project as a whole, I can offer a project repository on github