SQLiteAsyncConnection Support for REGEXP

[mlriley1187]

Good morning,

I have been trying to use REGEXP with an SQLiteAsyncConnection for days now and am having zero luck with it. I'm not sure whether I am misunderstanding things or if sqlite-net doesn't support it. I only found one issue when searching the issues for "REGEXP" or "REGEX" and it doesn't really address this.

Nuget Packages Installed
sqlite-net-pcl v1.9.172
Microsoft.Data.Sqlite v8.0.6
I also had previously installed SQLitePCLRaw but that was causing errors with "raw" being defined in SQLitePCLRaw and SQLitePCLRaw .Core so I uninstalled it

Target Framework: .NET 8.0, WPF

I have been reading a lot of stack overflow posts but This Post is the one that I have chosen to try to implement in the code below.

My application only needs to read values from the database and will never insert or remove items from any table in it. The dabasefile is on disk and selected by the user.

Also, I am not new to C# but I am new to using it for SQL operations so please understand if I am lacking in some key understanding.

Any help is appreciated!

using CommunityToolkit.Mvvm.ComponentModel;
using EngineeringLabUi.Models;
using Microsoft.Data.Sqlite;
using SQLite;
using SQLitePCL;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq.Expressions;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace EngineeringLabUi.CustomTypes
{
    public partial class DictionaryDbServiceAsync : ObservableObject
    {
        [ObservableProperty] private static List<DictionaryDb> _dicDbTempList = new(800000);
        SQLiteAsyncConnection? dbConnection;
        SqliteCommand command;
        private string? sqlCommand = string.Empty;
        // query all values from the parameter table
        private string defaultSqlCmd = "SELECT * FROM PARAMETER";
        // path where we will create the database fle
        private string? dbPath = string.Empty;

        public DictionaryDbServiceAsync(string? newDbPath)
        {
            // set the dbpath to the path passed to the constructor
            dbPath = newDbPath;

            // create the DB directory if it doesn't exist
            if((string.IsNullOrEmpty(dbPath) == false) && (File.Exists(dbPath) == false))
            {
                Directory.CreateDirectory(dbPath);
            }

            // create the db file
            createDbFile();
        }

        public void createDbFile()
        {
            if(System.IO.File.Exists(dbPath) == true)
            {
                createDbConnection();
            }
            else
            {
                File.Create(dbPath).Close();
                createDbConnection();
            }
        }

        public void createDbConnection()
        {
            string strCon = string.Format("Data Source={0};", dbPath);
            dbConnection = new SQLiteAsyncConnection(databasePath: dbPath, openFlags: SQLiteOpenFlags.ReadOnly, storeDateTimeAsTicks: true);
            dbConnection?.CreateTableAsync<DictionaryDb>().Wait();

            // NOTE:  SQLite-net-pcl doesn't support regex at this time
            /* Here is where the error occurs that says dbConnection has no attribute "Handle"
             * which leads me to believe it isn't supported
             */
            SQLitePCL.raw.sqlite3_create_function(dbConnection.Handle, "REGEXP", 2, null, MatchRegex);
        }

        public async Task<List<DictionaryDb>> executeQuery(string sqlCommand)
        {
            if(sqlCommand.Equals("") == true)
            {
                // "SELECT * FROM PARAMETER"
                return await dbConnection.QueryAsync<DictionaryDb>(defaultSqlCmd);

            }
            else
            {
                // This one works 
                //var task = dbConnection.QueryAsync<DictionaryDb>("SELECT * FROM PARAMETER WHERE VARNAME LIKE ?", "%a665%");
                //task.Wait();
                //var result = task.Result;

                // testing regex - Can't get here with the above error
                var task = dbConnection.QueryAsync<DictionaryDb>($"SELECT VARNAME FROM PARAMETER WHERE VARNAME REGEXP '{sqlCommand}'");
                task.Wait();
                var result = task.Result;
                return result;
            }
        }

        // function to be registered for the REGEXP functionaliity
        private void MatchRegex(sqlite3_context ctx, object user_data, sqlite3_value[] args)
        {
            bool isMatched = System.Text.RegularExpressions.Regex.IsMatch(SQLitePCL.raw.sqlite3_value_text(args[1]).utf8_to_string(),
                                                                          SQLitePCL.raw.sqlite3_value_text(args[0]).utf8_to_string(),
                                                                          RegexOptions.IgnoreCase);
            if(isMatched)
                SQLitePCL.raw.sqlite3_result_int(ctx, 1);
            else
                SQLitePCL.raw.sqlite3_result_int(ctx, 0);
        }       
    }
}