Function openvet_storage::database::functions::semver_functions

pub fn semver_functions(db: &Connection) -> Result<()>

Defines functions for working with semantic versioning Version in the database.

This defines functions that you can use within SQLite table schemas to access the fields of semantic versions. This is what the functions look like (represented as Rust functions):

fn semver_version_major(version: &str) -> u64;
fn semver_version_minor(version: &str) -> u64;
fn semver_version_patch(version: &str) -> u64;
fn semver_version_pre(version: &str) -> Option<&str>;
fn semver_version_build(version: &str) -> Option<&str>;

One use-case is to use these for generated columns. For example:

CREATE TABLE semantic_versions(
    id INTEGER NOT NULL            PRIMARY KEY,
    version TEXT NOT NULL          CHECK(type_validate("semver::Version", version)),

    -- automatically generated columns for semver
    version_major INTEGER NOT NULL GENERATED ALWAYS AS (semver_version_major(version)) STORED,
    version_minor INTEGER NOT NULL GENERATED ALWAYS AS (semver_version_minor(version)) STORED,
    version_patch INTEGER NOT NULL GENERATED ALWAYS AS (semver_version_patch(version)) STORED,
    version_pre TEXT               GENERATED ALWAYS AS (semver_version_pre(version)) STORED,
    version_build TEXT             GENERATED ALWAYS AS (semver_version_build(version)) STORED,

    -- index on versions
    UNIQUE (version_major, version_minor, version_patch, version_pre, version_build)
) STRICT;

By writing it this way, it means you can insert a row with just a string-encoded semantic version, but you are able to build indices and query versions efficiently. You get the convenience of inserting a simple string-encoded version, but the ability to query the individual parts of it.

Examples

// create in-memory database and register semver functions
let conn = rusqlite::Connection::open_in_memory().unwrap();
openvet_storage::database::functions::semver_functions(&conn).unwrap();

// helper for running a query
let query_i64 = |query: &str| -> i64 {
    conn.query_row(query, (), |r| r.get(0)).unwrap()
};

assert_eq!(0, query_i64("SELECT semver_version_major('0.1.234')"));
assert_eq!(1, query_i64("SELECT semver_version_minor('0.1.234')"));
assert_eq!(234, query_i64("SELECT semver_version_patch('0.1.234')"));