SQLite Custom URL Functions

Sure, here's a simple Python script to register these SQLite custom functions:

pythonCopy code
import sqlite3
from urllib.parse import urlparse, ParseResult


def url_valid(url):
    try:
        result = urlparse(url)
        return all([result.scheme, result.netloc])
    except ValueError:
        return False


def url_scheme(url):
    try:
        return urlparse(url).scheme
    except ValueError:
        return None


def url_host(url):
    try:
        return urlparse(url).netloc
    except ValueError:
        return None


def url_path(url):
    try:
        return urlparse(url).path
    except ValueError:
        return None


def url_fragment(url):
    try:
        return urlparse(url).fragment
    except ValueError:
        return None


conn = sqlite3.connect(":memory:")

conn.create_function("url_valid", 1, url_valid)
conn.create_function("url_scheme", 1, url_scheme)
conn.create_function("url_host", 1, url_host)
conn.create_function("url_path", 1, url_path)
conn.create_function("url_fragment", 1, url_fragment)

This script creates an in-memory SQLite database and registers the five requested custom functions. It uses the Python built-in urllib.parse.urlparse function to parse the URLs, which will raise a ValueError if the URL is not valid.

Sure, here's a set of pytest tests for these functions:

pythonCopy code
import pytest
import sqlite3
from urllib.parse import urlparse


def test_url_valid():
    with sqlite3.connect(":memory:") as conn:
        conn.create_function("url_valid", 1, url_valid)
        cursor = conn.cursor()

        cursor.execute("SELECT url_valid('https://sqlite.org')")
        assert cursor.fetchone()[0]

        cursor.execute("SELECT url_valid('not_a_url')")
        assert not cursor.fetchone()[0]


def test_url_scheme():
    with sqlite3.connect(":memory:") as conn:
        conn.create_function("url_scheme", 1, url_scheme)
        cursor = conn.cursor()

        cursor.execute("SELECT url_scheme('https://www.sqlite.org/vtab.html#usage')")
        assert cursor.fetchone()[0] == 'https'

        cursor.execute("SELECT url_scheme('ftp://example.com')")
        assert cursor.fetchone()[0] == 'ftp'


def test_url_host():
    with sqlite3.connect(":memory:") as conn:
        conn.create_function("url_host", 1, url_host)
        cursor = conn.cursor()

        cursor.execute("SELECT url_host('https://www.sqlite.org/vtab.html#usage')")
        assert cursor.fetchone()[0] == 'www.sqlite.org'

        cursor.execute("SELECT url_host('https://example.com')")
        assert cursor.fetchone()[0] == 'example.com'


def test_url_path():
    with sqlite3.connect(":memory:") as conn:
        conn.create_function("url_path", 1, url_path)
        cursor = conn.cursor()

        cursor.execute("SELECT url_path('https://www.sqlite.org/vtab.html#usage')")
        assert cursor.fetchone()[0] == '/vtab.html'

        cursor.execute("SELECT url_path('https://example.com/test')")
        assert cursor.fetchone()[0] == '/test'


def test_url_fragment():
    with sqlite3.connect(":memory:") as conn:
        conn.create_function("url_fragment", 1, url_fragment)
        cursor = conn.cursor()

        cursor.execute("SELECT url_fragment('https://www.sqlite.org/vtab.html#usage')")
        assert cursor.fetchone()[0] == 'usage'

        cursor.execute("SELECT url_fragment('https://example.com/test#fragment')")
        assert cursor.fetchone()[0] == 'fragment'

This suite of tests verifies the correct operation of each function for valid URLs, and checks the correct return values for different components of a URL.

To run the tests, you can use the command: pytest test_script.py