Screwdriver: Template/Command list screen shows wrong VERSION

[yoshwata]

What happened:
The VERSION number on template's list screen is something wrong.

The VERSION indicates 0.0.9 on screen.

However, there is no special tags like stable or latest on 0.0.9, and it also not to be newest one (0.0.14).

What you expected to happen:
latest, stable tagged version or simply newest one should appear on list screen.
(I prefer to show stable version.)

How to reproduce it:

1. Register a template and update 10 time to make the version 0.0.10.
2. Set tags stable and latest to 0.0.10
3. Then, the VERSION of list screen is still 0.0.9.

This will occur on command's list screen too.

[yoshwata]

The cause of problem is, like I wrote at https://github.com/screwdriver-cd/screwdriver/pull/1778, the columns is aggregated with MAX.

When we hava a template like below.

| namespace | name | description | version
----|---- |---- |---- |
| foo | bar | 2 | 0.0.10
| foo | bar | 1 | 0.0.9
| foo | bar | 9 | 0.0.8

List screen shows like this about foo/bar template (version might be already hidden)

| namespace | name | description | version
----|---- |---- |---- |
| foo | bar | 9 | 0.0.9

The expected behavior is like this.

| namespace | name | description | version
----|---- |---- |---- |
| foo | bar | 2 | 0.0.10

There is two problems to fix this issue.

1. Order of semantic version

In Postgres order by with semver is available like this.

SELECT id, version
FROM   versions
ORDER  BY string_to_array(version, '.')::int[];

https://dba.stackexchange.com/questions/74283/how-to-order-by-typical-software-release-versions-like-x-y-z

Unfortunately I can't find corresponding method with MYSQL.

2. DB performance

This is why I close the PR. There are a lot of queries emitted to DB, so paginating to reduce the load or some sort of SQL to get the list of newest templates/commands is needed.

[yoshwata]

I propose two solution. I'd like to get your opinion.

1. Paginating

Restricting number of DB queries by paginating. New search queries are emitted by scrolling list screen, so DB performance is not so bad.

2. Implement DB query to get the list of newest templates.

This is a sample for mysql to list newest version of templates.

select namespace, name, (select version from templates where namespace=t.namespace and name=t.name order by INET_ATON(SUBSTRING_INDEX(CONCAT(version, '.0'),'.',4)) desc limit 1) version from templates t group by namespace, name;

However it does not work for number grater than 255 because of INET_ATON. We have to rethink DB query to proceed this plan.

3. Order by createTime (Subspecies of solution No.2)

This idea is to select the newest version of template by createTime. This solution seems reasonable if we can blink at the problem that old version of template can appear on list screen.

| namespace | name | description | version | createTime
----|---- |---- |---- |---- |
| foo | bar | 2 | 0.3.10 | 2019/10/9
| foo | bar | 1 | 0.2.9 | 2019/10/7
| foo | bar | 9 | 0.1.8 | 2019/10/5

In the situation above, 0.2.10 will selected after the patch version of 0.2 is uploaded. Some people might say that this will get better way than present.

4. Order by major, minor, patch column (Subspecies of solution No.2)

Adding new column of major, minor patch column to template table, that might be able to used as the variable for order by. Using this simple independent integer sequence instead of string semver, we can implement simple query to find newest version of template.

[jithine]

Option ~2~ 3 appears to be simplest and easiest to implement, should cover 90% of cases.

For postgres this is an option https://dba.stackexchange.com/questions/74283/how-to-order-by-typical-software-release-versions-like-x-y-z

But to solve this generically option 4 seems to be right approach.

[wahapo]

Option 2: This is an interesting implementation. But ORACLE does not have INET_ATON. To implement this option, it seems necessary to switch the process of sequelize depending on the type of RDB. I am worried that this would make the code very dirty.

Option 3: I feel that it will be a simple implementation compared to option 2. I expect it to be a modification that just uses a subquery. And there are almost no cases in Yahoo! JAPAN where a template with a lower version number is published as the latest version. It's a very special case, and if it exists, it will be better than current one.

Option 4: I expect that the subquery part modified with option 3 will be extended to use three columns. So, I feel that it is a good correction volume to work on after option 3.(However, it is quite a volume ...)

@jithin1987 I want to start with Option 3 to advance development to Option 4. If you have any concerns about Option 3, would you provide feedback?

[jithine]

@wahapo I am good with option 3. Actually in my previous comment I meant to say option 3 only 🤦‍♂

[wahapo]

@jithine OK, then first of all I will implement with option 3. Thank you for your feedback😃

[wahapo]

By research, I discovered a pattern where createTime is NULL. In this case I need to look for other plausible sort key. It seems that there is nothing appropriate other than id.

> select id, name, namespace, createTime from templates;
+----+-------+-------------+---------+------------+
| id | name  | namespace   | version | createTime |
+----+-------+-------------+---------+------------+
|  1 | foo   | bar         | 0.0.0   | NULL       |
|  2 | foo   | bar         | 0.0.1   | NULL       |
+----+-------+-------------+---------+------------+

By using the following query, I can achieve what I want to achieve fairly accurately.

> select id, name, namespace, createTime
  from templates as m
  where not exists(
    select 1
    from templates as s
    where m.name = s.name
    and m.namespace = s.namespace
    and m.id < s.id
  );
+----+-------+-------------+---------+------------+
| id | name  | namespace   | version | createTime |
+----+-------+-------------+---------+------------+
|  2 | foo   | bar         | 0.0.1   | NULL       |
+----+-------+-------------+---------+------------+

[wahapo]

Sequelize is unlikely to handle subqueries well. I thought another SQL, so I will try it.

select
    templates.id,
    templates.name,
    templates.namespace,
    templates.version,
    templates.createTime
  from templates
  left join templates as t on (
    templates.name = t.name
    and templates.namespace = t.namespace
    and templates.id < t.id
  )
  where t.id is null;

[wahapo]

It seems that association setting is necessary to use LEFT JOIN with sequelize.
https://sequelize.org/master/manual/associations.html

When setting association between the same table names, I gave another table name t with AS clause, however when I accessed it twice in the browser, it shows alias t already exists, so it didn't work. In the case of joining tables with the same name, I didn't find a way to solve this well.

I found an example using subquery and implemented it with @ibu1224 using this as a reference.
https://stackoverflow.com/questions/36164694/sequelize-subquery-in-where-clause

[jithine]

Changes are available in https://cd.screwdriver.cd/templates