I am having trouble doing a recursive query with sql. I wanted to know what is the best way to solve this.
I have the following model
where basically there are versions of applications, which are installed in different environments. It is important to note that each version has a previous version, so a model similar to the hotfix can be applied. To know the tickets that are in an environment, it is necessary to search for the latest version of the environment and make recursion
my goal is to build a table as follows:
ticket | arrival_date_to_enviroment | arrival_version_to_enviroment | enviroment
I suppose I have to do a recursive query, to know which tickets are in which version. For this I think that the first step would be to know the versions that a version includes I can do this part of the problem by posing something in the following way:
WITH RECURSIVE version_recursive AS (
select v.name, v.version_from, v.application from versions v
where v.name='21.1204.0'
UNION
select v.name, v.version_from, v.application from versions v
JOIN version_recursive s ON s.version_from = v.name AND s.application = v.application
)
SELECT *
FROM version_recursive;
the next step would be to find the latest deploy in the environment and join with tickets.
But one of my main problems is that I don't know how to complete arrival_version_to_enviroment using sql
I really appreciate any help and advice. I need to do this using sql, without programming logic
CodePudding user response：
If I understand your meaning of arrival, something like this might do it:
- iterate over the versions (recursively), but keep the base version with each related prior version.
- join in the various required detail
I included more detail in the result than was requested, and more is probably suggested. For instance, the initial environment a ticket is introduced is useful to know, but so is the current environment for this deployment. I decided to include just the initial environment introduced. It's trivial to add more detail.
The SQL:
WITH RECURSIVE versionx (base, name, version_from, application, lv) AS (
SELECT name, name, version_from, application, 1 FROM versions
UNION ALL
SELECT v0.base, v1.name, v1.version_from, v1.application, lv 1
FROM versions AS v1
JOIN versionx AS v0
ON v0.version_from = v1.name
AND lv < 20 -- Temporary guard against bad data. Remove when ready.
)
SELECT t.id AS ticket_id -- The ticket
, d.date AS arrival_date -- Initial date ticket was introduced.
, x.name AS arrival_version -- Initial version ticket was introduced.
, x.base AS version -- Current version.
, d0.date AS cur_date -- Date of deployment of current version.
, e.name AS env_name -- Initial deployment environment
FROM versionx AS x
JOIN tickets AS t
ON t.version = x.name
JOIN deployments AS d
ON d.version = x.name
JOIN environments AS e
ON e.id = d.environment_id
JOIN deployments AS d0
ON d0.version = x.base
;
The result, based on given test data:
----------- -------------- ----------------- --------- ------------ ----------
| ticket_id | arrival_date | arrival_version | version | cur_date | env_name |
----------- -------------- ----------------- --------- ------------ ----------
| 1 | 2021-12-07 | ver01 | ver01 | 2021-12-07 | env01 |
| 1 | 2021-12-07 | ver01 | ver02 | 2021-12-08 | env01 |
| 2 | 2021-12-08 | ver02 | ver02 | 2021-12-08 | env02 |
----------- -------------- ----------------- --------- ------------ ----------
The test data:
SELECT * FROM versions;
------- -------------- -------------
| name | version_from | application |
------- -------------- -------------
| ver01 | NULL | app01 |
| ver02 | ver01 | app02 |
------- -------------- -------------
SELECT * FROM deployments;
------ ---------------- ------------ ---------
| id | environment_id | date | version |
------ ---------------- ------------ ---------
| 1 | 1 | 2021-12-07 | ver01 |
| 2 | 2 | 2021-12-08 | ver02 |
------ ---------------- ------------ ---------
SELECT * FROM environments;
------ ------- ------------- -------
| id | name | application | url |
------ ------- ------------- -------
| 1 | env01 | app01 | url01 |
| 2 | env02 | app02 | url02 |
------ ------- ------------- -------
SELECT * FROM tickets;
------ ---------
| id | version |
------ ---------
| 1 | ver01 |
| 2 | ver02 |
------ ---------
CodePudding user response：
Different Example:
-- creating table
CREATE TABLE versions (
"name" text NULL,
version_from text NULL,
aqpplication text NULL
);
-- insert sample data for test
INSERT INTO versions ("name", version_from, aqpplication) VALUES('1.0', NULL, 'app1');
INSERT INTO versions ("name", version_from, aqpplication) VALUES('1.1', '1.0', 'app1');
INSERT INTO versions ("name", version_from, aqpplication) VALUES('1.2', '1.1', 'app1');
INSERT INTO versions ("name", version_from, aqpplication) VALUES('1.3', '1.21', 'app1');
INSERT INTO versions ("name", version_from, aqpplication) VALUES('5.5', NULL, 'app2');
INSERT INTO versions ("name", version_from, aqpplication) VALUES('5.6', '5.5', 'app2');
INSERT INTO versions ("name", version_from, aqpplication) VALUES('5.7', '5.6', 'app2');
INSERT INTO versions ("name", version_from, aqpplication) VALUES('5.8', '5.7', 'app2');
INSERT INTO versions ("name", version_from, aqpplication) VALUES('1.4', '1.3', 'app1');
-- recursive select query
WITH RECURSIVE version_recursive(name, version_from, application, version_history) AS (
SELECT vn.*, vn."name"::text
FROM versions AS vn
WHERE vn.version_from is null
UNION ALL
SELECT v.*,
(v_rec.version_history || '->' || v."name"::TEXT)
FROM version_recursive as v_rec, versions AS v
WHERE v.version_from = v_rec."name"
)
SELECT * FROM version_recursive ORDER BY "name";
Result:
-------- -------------- ------------- --------------------
| name | version_from | application | version_history |
-------- -------------- ------------- --------------------
| 1.0 | NULL | app1 | 1.0 |
| 1.1 | 1.0 | app1 | 1.0->1.1 |
| 1.2 | 1.1 | app1 | 1.0->1.1->1.2 |
| 5.5 | NULL | app2 | 5.5 |
| 5.6 | 5.5 | app2 | 5.5->5.6 |
| 5.7 | 5.6 | app2 | 5.5->5.6->5.7 |
| 5.8 | 5.7 | app2 | 5.5->5.6->5.7->5.8 |
-------- -------------- ------------- --------------------