FSQLite

FSQLite: An Enhanced Flavor of SQLite with Custom SQL Functions #

Overview #

FSQLite is a customized version of SQLite that extends its functionality by introducing several additional SQL functions. These extensions are designed to simplify common string processing tasks, making FSQLite an ideal choice for developers working with structured text data.

Features #

FSQLite adds the following custom SQL functions to the standard SQLite environment:

1. regexp_match #

  • Usage: regexp_match(pattern, string)
  • Description: Checks if a string matches a given regular expression pattern.
  • Returns:
    • 1 (integer) if the string matches the pattern.
    • 0 (integer) if the string does not match.
  • Example: 
    SELECT regexp_match('a.*b', 'abc'); -- Returns: 1
SELECT regexp_match('a.*b', 'def'); -- Returns: 0

2. regexp_extract #

  • Usage: regexp_extract(pattern, string)
  • Description: Extracts the first capturing group from a string that matches the provided regular expression pattern.
  • Returns:
    • The value of the first capturing group as a string if a match is found.
    • NULL if no match is found.
  • Example: 
    SELECT regexp_extract('a(.*)c', 'abc'); -- Returns: 'b'
SELECT regexp_extract('x(.*)y', 'abc'); -- Returns: NULL

3. split_part #

  • Usage: split_part(string, separator, part_index)
  • Description: Splits a string by a given separator and returns the specified part.
    • The part_index is 0-based, and negative indices can be used to count from the end.
  • Returns:
    • The selected part as a string.
    • NULL if the part_index is out of range.
  • Example: 
    SELECT split_part('a,b,c', ',', 1);  -- Returns: 'b'
SELECT split_part('a,b,c', ',', -1); -- Returns: 'c'
SELECT split_part('a,b,c', ',', 4);  -- Returns: NULL

4. reverse #

  • Usage: reverse(string)
  • Description: Reverses the characters in a string.
  • Returns:
    • The reversed string.
  • Example: 
    SELECT reverse('hello'); -- Returns: 'olleh'
SELECT reverse('12345'); -- Returns: '54321'

Implementation Details #

Backend Integration #

FSQLite leverages the github.com/mattn/go-sqlite3 package to implement these custom functions as native extensions. These functions are registered with SQLite during the database initialization phase using a ConnectHook.

Registration of Functions #

Each function is implemented in Go and registered with the SQLite driver:

  • regexp_match and regexp_extract utilize Go’s regexp package for pattern matching and extraction.
  • split_part uses the strings.Split function to split the input string and select the desired part.
  • reverse processes strings by iterating through their rune slices in reverse order.

Example Code Snippet #

Below is an example of initializing an FSQLite database and querying its custom functions:

import (
	"context"
	"database/sql"
	"fmt"
	"log"

	_ "path/to/fsqlite"
)

func main() {
	// Open the database
	db, err := sql.Open("fsqlite", ":memory:")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// Execute a query using the custom functions
	query := `
		SELECT
			regexp_match('a.*b', 'abc') AS match,
			regexp_extract('a(.*)c', 'abc') AS extract,
			reverse('hello') AS reversed,
			split_part('a,b,c', ',', 1) AS part
	`
	row := db.QueryRowContext(context.Background(), query)

	var match int
	var extract, reversed, part string

	err = row.Scan(&match, &extract, &reversed, &part)
	if err != nil {
		log.Fatal(err)
	}

	fmt.Printf("Match: %d, Extract: %s, Reversed: %s, Part: %s\n", match, extract, reversed, part)
}

Testing #

Unit Testing #

FSQLite includes a suite of unit tests that validate the behavior of its custom functions. The tests use the require and assert modules from github.com/stretchr/testify for robust test assertions.

Sample Test Cases #

  • Regular Expression Matching: 
    require.Equal(t, int64(1), results[0]) // 'a.*b' matches 'abc'
  • Regular Expression Extraction: 
    require.Equal(t, "b", results[1]) // Captures 'b' from 'a(.*)c'
  • String Reversal: 
    require.Equal(t, "olleh", results[2]) // Reverses 'hello'
  • Splitting and Part Extraction: 
    require.Equal(t, "b", results[3]) // Extracts 1st part of 'a,b,c'

Conclusion #

FSQLite is a powerful extension of SQLite that brings additional flexibility for string processing tasks. Its lightweight design and straightforward implementation make it a valuable tool for developers looking to enhance their database capabilities.