How I uploaded base in GIT

Good day, habrovchane. Most of the products faced by the developer usually require deployment on multiple machines that operate independently of each other. This gives rise to one of the typical problems - discrepancy of the database on different servers, inconsistency of identifiers in reference tables, and of course non-uniformity due to carelessness and missing patches when updating the database on a particular machine. In some cases, this translates into wild (in my naive view) concepts like “we never delete columns — we only add”.

In others, it leads to clogging of the base with debris from other sites and to errors after the “simplest merge”.

Familiar with such situations, critics and knowing exactly what I invented the bicycle - I invite under the cat.

I have met this principle of storage in several companies, but for some reason its description is hidden from me in the network.
')
The overall concept is simple as an orange and takes 2 settings:

1. The database should be aware of which patches have been applied to it.
2. When creating links in records in any case, the values ​​of identifiers are not used. (except obtained in calculations).

Under these conditions, the product should work on any machine with an objectively stable result. Yes, it may seem that the second condition is not feasible, but if all databases are created by the same script, the divergence of identifiers will already be an anomaly.

Iteration during schema creation.

"Who created the column?"
“What should be stored here? Where do these numbers come from? Write at least comments! ”
“We haven't used it for 100 years. Where does it come from?

Familiar? Many accept as an axiom the fact that the database exists "as is". This is a letter from Uncle Fedor, which does not have its author. But all so only in part. Every change, column and record has its own author. All changes are made on the timeline. Looks like git / svn, etc.? We all actively use version control systems and have become friends with all the bonuses that this approach gives us. Let's try applying it here.

So, go to practice and take a look at the myriad of folders in the project.


To begin, create a table with information about patches. Here we will remember what patches we have worked (name + type) and with what result (result)


To make everything work the way we want, add the most important file with the original name.


The principle was taken as the most obvious - the name of the patch is the name of the file. For triggers, tables, and foreign keys, the presence of objects with such names is checked through information_schema . If the result is negative, the corresponding script is run.

No identifier collisions

Much more interesting is the filling of directories with data. It is carried out through the execution of go-scripts in the / COMMON / DATA directory.

The fact of working out the script is recorded in our dc table (data containers). If everything was ok, we skip the file next time.

Execution is in alphabetical order, so the most universal is to use the timestamp in the name.

In the simplest version, these are the usual INSERT requests with a bright hope of stability and predictability of the auto-increment.

 --    INSERT INTO ds.reso_type (name, description) VALUES ('', ' ,  '); INSERT INTO ds.reso_type (name, description) VALUES ('', ' ,  '); INSERT INTO ds.reso_type (name, description) VALUES ('', ' ,  '); # noinspection SqlResolve INSERT INTO ds.human_type (name, description) VALUES ("", "   ?"); 

But what about the more complex data? We use nested queries.

 INSERT INTO reso_speed (resoId, popId, speed) VALUES ( (SELECT ht.id FROM human_type ht WHERE ht.name = ''), (SELECT rt.id FROM reso_type rt WHERE rt.name = ''), 30); 

Or we will write auxiliary functions.

 # tasktype DELIMITER // CREATE FUNCTION installTaskType(name VARCHAR(50), title VARCHAR(100), groupId INT(11), description VARCHAR(1000)) RETURNS int(11) BEGIN SELECT count(id) into @c from tasktype as t where t.name = name COLLATE 'utf8_unicode_ci'; if (@c = 0) THEN insert into `tasktype`(`name`,`title`,`group_id`,`description`) VALUES (name, title, groupId, description); RETURN LAST_INSERT_ID(); END IF; SELECT id INTO @taskTypeId FROM tasktype as t where t.name = name COLLATE 'utf8_unicode_ci'; RETURN @taskTypeId; END // DELIMITER ; #   taskgroup DELIMITER // CREATE FUNCTION installTaskGroup(name VARCHAR(255)) RETURNS INT(11) BEGIN SELECT count(id) into @c FROM taskgroup as t where t.name = name COLLATE 'utf8_unicode_ci'; if (@c = 0) THEN INSERT INTO taskgroup (`name`) VALUES (name); RETURN LAST_INSERT_ID(); END IF; SELECT id INTO @groupId FROM taskgroup as t WHERE t.name = name COLLATE 'utf8_unicode_ci'; RETURN @groupId; END // DELIMITER ; 

In the go-script we write:

 SELECT installTaskGroup('TEST_GROUP') into @groupId; SELECT installTaskType('TEST_TASK', '      HABR', @groupId, ''); 

So, we got an approach that allows you to develop a unified database management system using the example of git + mysql.

In the quality of profit received:

- authorship + in some cases, binding tasks task tracker;
- sequence in the data;
- New project on jenkins;
- calm nerves.

I sincerely thank everyone who read it. Greetings and waiting for criticism). I agree that the product turned out raw in some places, but it is excellent for home use.

Slippers do not throw much - this is the first attempt to publish on Habré (girls can).