install.sql

-- PostgreSQL Software Version Extension
-- Installation Script
-- 
-- This script creates a custom software_version domain type with
-- comparison operators that properly handle semantic versioning.
--
-- Prerequisites:
-- 1. PostgreSQL with plpython3u extension enabled
-- 2. Python with packaging library
-- 3. Superuser privileges

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS plpython3u;

-- Create the software_version domain
CREATE DOMAIN software_version AS varchar(255);

-- Create comparison functions
CREATE OR REPLACE FUNCTION software_version_lt(v1 software_version, v2 software_version)
  RETURNS bool
AS $$
    from packaging import version
    return version.parse(v1) < version.parse(v2)
$$ LANGUAGE plpython3u IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION software_version_lte(v1 software_version, v2 software_version)
  RETURNS bool
AS $$
    from packaging import version
    return version.parse(v1) <= version.parse(v2)
$$ LANGUAGE plpython3u IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION software_version_eq(v1 software_version, v2 software_version)
  RETURNS bool
AS $$
    from packaging import version
    return version.parse(v1) == version.parse(v2)
$$ LANGUAGE plpython3u IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION software_version_gte(v1 software_version, v2 software_version)
  RETURNS bool
AS $$
    from packaging import version
    return version.parse(v1) >= version.parse(v2)
$$ LANGUAGE plpython3u IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION software_version_gt(v1 software_version, v2 software_version)
  RETURNS bool
AS $$
    from packaging import version
    return version.parse(v1) > version.parse(v2)
$$ LANGUAGE plpython3u IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION software_version_neq(v1 software_version, v2 software_version)
  RETURNS bool
AS $$
    from packaging import version
    return version.parse(v1) != version.parse(v2)
$$ LANGUAGE plpython3u IMMUTABLE STRICT;

-- Create comparison function for B-tree operations
CREATE OR REPLACE FUNCTION software_version_cmp(version_1 software_version, version_2 software_version)
RETURNS integer AS $$
    from packaging import version
    v1 = version.parse(version_1)
    v2 = version.parse(version_2)
    if v1 > v2:
        return 1
    if v2 > v1:
        return -1
    return 0
$$ LANGUAGE plpython3u IMMUTABLE STRICT;

-- Create operators
CREATE OPERATOR < (
    leftarg = software_version, 
    rightarg = software_version,
    function = software_version_lt,
    commutator = >, 
    negator = >=,
    restrict = scalargtsel, 
    join = scalargtjoinsel
);

CREATE OPERATOR <= (
    leftarg = software_version, 
    rightarg = software_version,
    function = software_version_lte,
    commutator = >=, 
    negator = >,
    restrict = scalargtsel, 
    join = scalargtjoinsel
);

CREATE OPERATOR = (
    leftarg = software_version, 
    rightarg = software_version,
    function = software_version_eq,
    commutator = =, 
    negator = !=,
    restrict = scalargtsel, 
    join = scalargtjoinsel
);

CREATE OPERATOR >= (
    leftarg = software_version, 
    rightarg = software_version,
    function = software_version_gte,
    commutator = <=, 
    negator = <,
    restrict = scalargtsel, 
    join = scalargtjoinsel
);

CREATE OPERATOR > (
    leftarg = software_version, 
    rightarg = software_version,
    function = software_version_gt,
    commutator = <, 
    negator = <=,
    restrict = scalargtsel, 
    join = scalargtjoinsel
);

CREATE OPERATOR != (
    leftarg = software_version, 
    rightarg = software_version,
    function = software_version_neq,
    commutator = !=, 
    negator = =,
    restrict = neqsel, 
    join = neqjoinsel
);

-- Create B-tree operator class
CREATE OPERATOR CLASS software_version_ops
    DEFAULT FOR TYPE software_version USING btree AS
        OPERATOR        1       <,
        OPERATOR        2       <=,
        OPERATOR        3       =,
        OPERATOR        4       >=,
        OPERATOR        5       >,
        FUNCTION        1       software_version_cmp(software_version, software_version);

-- Create convenience function for explicit parsing
CREATE OR REPLACE FUNCTION software_version_parse(version_str text)
RETURNS software_version AS $$
    from packaging import version
    # Validate the version string
    parsed = version.parse(version_str)
    return str(parsed)
$$ LANGUAGE plpython3u IMMUTABLE STRICT;

-- Grant permissions (adjust as needed)
GRANT USAGE ON DOMAIN software_version TO PUBLIC;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO PUBLIC;

-- Create a comment on the domain
COMMENT ON DOMAIN software_version IS 'A domain for software version strings with semantic versioning comparison support';

DO $$
BEGIN
    RAISE NOTICE 'Software Version Extension installed successfully!';
END $$;