SQLite跨环境对比应用版本：找出DEV版本低于STAGE的应用

Got it, let's solve this problem of finding apps where the DEV version is lower than the STAGE version. The main hurdle here is correctly comparing version numbers—since they're string-formatted with multiple segments, standard string comparison won't cut it (think about how '1.10' would incorrectly be seen as smaller than '1.9').

Step 1: Pair Up DEV and STAGE Data #

First, we need to match each app that exists in both environments. This is straightforward with an INNER JOIN:

SELECT 
    d.APP_NAME,
    d.APP_VERSION AS dev_version,
    s.APP_VERSION AS stage_version
FROM apps d
INNER JOIN apps s 
    ON d.APP_NAME = s.APP_NAME
    AND d.ENV = 'DEV'
    AND s.ENV = 'STAGE'

This gives us a list of apps present in both environments, along with their respective versions. Now we need to filter this list to only include rows where dev_version < stage_version.

Method 1: Pure SQL Version Comparison (No Custom Functions) #

Since SQLite doesn't have a built-in version comparison function, we can use a recursive CTE to split version strings into numeric segments, then compare each segment one by one. This handles cases where versions have different lengths (like 1.0 vs 1.0.3.4).

Here's the full query:

WITH RECURSIVE split_versions(APP_NAME, env, version_part, remaining_version, part_num) AS (
    -- Initialize: extract the first version segment
    SELECT 
        APP_NAME,
        ENV,
        CAST(SUBSTR(APP_VERSION, 1, INSTR(APP_VERSION || '.', '.') - 1) AS INTEGER),
        SUBSTR(APP_VERSION, INSTR(APP_VERSION || '.', '.') + 1),
        1
    FROM apps
    WHERE ENV IN ('DEV', 'STAGE')
    
    UNION ALL
    
    -- Recursively split remaining segments
    SELECT 
        APP_NAME,
        env,
        CAST(SUBSTR(remaining_version, 1, INSTR(remaining_version || '.', '.') - 1) AS INTEGER),
        SUBSTR(remaining_version, INSTR(remaining_version || '.', '.') + 1),
        part_num + 1
    FROM split_versions
    WHERE remaining_version != ''
),
version_comparison AS (
    SELECT 
        d.APP_NAME,
        d.version_part AS dev_part,
        s.version_part AS stage_part,
        d.part_num,
        CASE 
            WHEN d.version_part < s.version_part THEN 'LESS'
            WHEN d.version_part > s.version_part THEN 'GREATER'
            ELSE 'EQUAL'
        END AS comparison
    FROM split_versions d
    INNER JOIN split_versions s 
        ON d.APP_NAME = s.APP_NAME
        AND d.part_num = s.part_num
        AND d.env = 'DEV'
        AND s.env = 'STAGE'
)
SELECT DISTINCT APP_NAME
FROM version_comparison
WHERE 
    -- Case 1: First differing segment shows DEV version is smaller
    (comparison = 'LESS' AND part_num = (
        SELECT MIN(part_num) 
        FROM version_comparison vc 
        WHERE vc.APP_NAME = version_comparison.APP_NAME 
          AND vc.comparison != 'EQUAL'
    ))
    -- Case 2: All matching segments are equal, but DEV has fewer segments (e.g., 1.0 vs 1.0.1)
    OR (
        (SELECT COUNT(*) FROM split_versions d WHERE d.APP_NAME = version_comparison.APP_NAME AND d.env = 'DEV') < 
        (SELECT COUNT(*) FROM split_versions s WHERE s.APP_NAME = version_comparison.APP_NAME AND s.env = 'STAGE')
        AND NOT EXISTS (
            SELECT 1 
            FROM version_comparison vc 
            WHERE vc.APP_NAME = version_comparison.APP_NAME 
              AND vc.comparison = 'GREATER'
        )
    )

Method 2: Custom Function (Simpler, if allowed) #

If you can use custom functions (e.g., with Python in pysqlite), you can create a helper function to compare versions directly. This makes the query much cleaner.

First, create the function:

CREATE FUNCTION compare_versions(a TEXT, b TEXT) RETURNS INTEGER AS $$
    parts_a = list(map(int, a.split('.')))
    parts_b = list(map(int, b.split('.')))
    max_len = max(len(parts_a), len(parts_b))
    -- Pad shorter version with zeros to match length
    parts_a += [0] * (max_len - len(parts_a))
    parts_b += [0] * (max_len - len(parts_b))
    for x, y in zip(parts_a, parts_b):
        if x < y:
            return -1
        elif x > y:
            return 1
    return 0
$$ LANGUAGE python;

Then use it in your query:

SELECT 
    d.APP_NAME
FROM apps d
INNER JOIN apps s 
    ON d.APP_NAME = s.APP_NAME
    AND d.ENV = 'DEV'
    AND s.ENV = 'STAGE'
WHERE compare_versions(d.APP_VERSION, s.APP_VERSION) = -1;

Result for Your Sample Data #

Using your sample data (assuming the typo Applictaion_1/Applictaion_4 is a misspelling of Application_1/Application_4), the query will return:

Application_3

Because DEV's 1.0 is lower than STAGE's 1.0.3.4, while DEV's 1.2 for Application_1 is higher than STAGE's 0.2.3.

内容的提问来源于stack exchange，提问作者Rahul Trivedi