SQLite C-Interface - Create Own Functions

Share your advanced PureBasic knowledge/code with the community.
Post Reply
User avatar
mk-soft
Always Here
Always Here
Posts: 5406
Joined: Fri May 12, 2006 6:51 pm
Location: Germany

SQLite C-Interface - Create Own Functions

Post by mk-soft »

Had some time over.
Is also still expandable :wink:

Update v1.03
- Bugfix CDecl
- Added string example

Update v1.04
- Bugfix sqlite3_result_text. Show sql_destructor_freememory

Update v1.05.2
- Optimization
- Update example

Code: Select all

;-TOP by mk-soft

; SQLite C-Interface v1.05.2
; Update 25.02.2024
; Link: https://www.purebasic.fr/english/viewtopic.php?t=75264

UseSQLiteDatabase()

ImportC ""
  sqlite3_create_function.i(DatabaseID, zFunctionName.p-utf8, nArg.i, eTextRep.i, *pApp, *xFunc, *xStep, *xFinal)
  sqlite3_create_function16.i(DatabaseID, zFunctionName.s, nArg.i, eTextRep.i, *pApp, *xFunc, *xStep, *xFinal)
  sqlite3_aggregate_context(*sqlite3_context, nBytes.i)
  sqlite3_result_int(*sqlite3_value, lVal.l)
  sqlite3_result_int64(*sqlite3_value, qVal.q)
  sqlite3_result_double(*sqlite3_context, dblVal.d)
  sqlite3_result_text(*sqlite3_context, *char, cBytes, *void1, *void2)
  sqlite3_result_text16(*sqlite3_context, *char, cBytes, *void1, *void2)
  sqlite3_value_numeric_type.i(*sqlite3_value)
  sqlite3_value_int.l(*sqlite3_value)
  sqlite3_value_int64.q(*sqlite3_value)
  sqlite3_value_double.d(*sqlite3_value)
  sqlite3_value_text(*sqlite3_value)
  sqlite3_value_text16(*sqlite3_value)
  
  sqlite3_value_type.i(*argv)
  
EndImport

#SQLITE_UTF8 = 1  ; IMP: R-37514-35566
#SQLITE_UTF16LE = 2 ; IMP: R-03371-37637
#SQLITE_UTF16BE = 3 ; IMP: R-51971-34154
#SQLITE_UTF16 = 4   ; Use native byte order
#SQLITE_ANY = 5     ; Deprecated
#SQLITE_UTF16_ALIGNED = 8 ; sqlite3_create_collation only


#SQLITE_INTEGER = 1
#SQLITE_FLOAT = 2
#SQLITE_TEXT = 3
#SQLITE_BLOB = 4
#SQLITE_NULL = 5

#SQLITE_STATIC = 0
#SQLITE_TRANSIENT = -1

Structure udtArgv
  *Index[0]
EndStructure

; ********

CompilerIf #PB_Compiler_IsMainFile
  
  Procedure Unicode(String.s)
    Protected *mem
    *mem = AllocateMemory(StringByteLength(String) + SizeOf(Character))
    If *mem
      PokeS(*mem, String, -1, #PB_Unicode)
    EndIf
    ProcedureReturn *mem
  EndProcedure
  
  ; ****
  
  ProcedureC sql_sin(*context, argc.i, *argv.udtArgv)
    Protected a.d
    a = sqlite3_value_double(*argv\Index[0])
    a = Sin(a)
    sqlite3_result_double(*context, a)
  EndProcedure
  
  ; ----
  
  ProcedureC sql_cos(*context, argc.i, *argv.udtArgv)
    Protected a.d
    a = sqlite3_value_double(*argv\Index[0])
    a = Cos(a)
    sqlite3_result_double(*context, a)
  EndProcedure
  
  ; ----
  
  ProcedureC sql_tan(*context, argc.i, *argv.udtArgv)
    Protected a.d
    a = sqlite3_value_double(*argv\Index[0])
    a = Tan(a)
    sqlite3_result_double(*context, a)
  EndProcedure
  
  ; ****
  
  ProcedureC sql_destructor_freememory(*void)
    If *void
      Debug "SQL-Destructor: FreeMemory: " + *void
      FreeMemory(*void)
    EndIf
  EndProcedure
  
  ; ----
  
  ; ProcedureC sql_lcase(*context, argc.i, *argv.udtArgv)
  ;   Protected *string, *result
  ;   *string = sqlite3_value_text(*argv\Index[0])
  ;   *result = UTF8(LCase(PeekS(*string, -1, #PB_UTF8)))
  ;   sqlite3_result_text(*context, *result, -1, @sql_destructor_freememory(), 0)
  ; EndProcedure
  
  ProcedureC sql_lcase(*context, argc.i, *argv.udtArgv)
    Protected *string, result.s
    *string = sqlite3_value_text16(*argv\Index[0])
    result = LCase(PeekS(*string))
    sqlite3_result_text16(*context, result, -1, #SQLITE_TRANSIENT, 0)
  EndProcedure
  
  ; ----
  
  ; ProcedureC sql_ucase(*context, argc.i, *argv.udtArgv)
  ;   Protected *string, *result
  ;   *string = sqlite3_value_text(*argv\Index[0])
  ;   *result = UTF8(UCase(PeekS(*string, -1, #PB_UTF8)))
  ;   sqlite3_result_text(*context, *result, -1, @sql_destructor_freememory(), 0)
  ; EndProcedure
  
  ProcedureC sql_ucase(*context, argc.i, *argv.udtArgv)
    Protected *string, result.s
    *string = sqlite3_value_text16(*argv\Index[0])
    result = UCase(PeekS(*string))
    sqlite3_result_text16(*context, result, -1, #SQLITE_TRANSIENT, 0)
  EndProcedure
  
  ; ----
  
  ProcedureC sql_myfunction(*context, argc.i, *argv.udtArgv)
    Protected i
    Debug "* sql_myfunction:"
    For i = 0 To argc -1
      Select sqlite3_value_numeric_type(*argv\Index[i])
        Case #SQLITE_INTEGER
          Debug "- Integer Arg[" + i + "] = " + sqlite3_value_int64(*argv\Index[i])
        Case #SQLITE_FLOAT
          Debug "- Float Arg[" + i + "] = " + sqlite3_value_double(*argv\Index[i])
        Case #SQLITE_TEXT
          Debug "- Text Arg[" + i + "] = " + PeekS(sqlite3_value_text16(*argv\Index[i]))
        Case #SQLITE_BLOB
          Debug "- Blob Arg[" + i + "] = Blob"
        Case #SQLITE_NULL
          Debug "- Null Arg[" + i + "] = NULL"
      EndSelect
    Next
    Debug "****"
    sqlite3_result_int(*context, 1)
  EndProcedure
  
  ProcedureC sql_myfunction2(*context, argc.i, *argv.udtArgv)
    Debug "* sql_myfunction2:"
    For i = 0 To argc -1
      Select sqlite3_value_numeric_type(*argv\Index[i])
        Case #SQLITE_INTEGER
          Debug "- Integer Arg[" + i + "] = " + sqlite3_value_int64(*argv\Index[i])
        Case #SQLITE_FLOAT
          Debug "- Float Arg[" + i + "] = " + sqlite3_value_double(*argv\Index[i])
        Case #SQLITE_TEXT
          Debug "- Text Arg[" + i + "] = " + PeekS(sqlite3_value_text16(*argv\Index[i]))
        Case #SQLITE_BLOB
          Debug "- Blob Arg[" + i + "] = Blob"
        Case #SQLITE_NULL
          Debug "- Null Arg[" + i + "] = NULL"
      EndSelect
    Next
    Debug "****"
    sqlite3_result_int(*context, 2)
  EndProcedure
  
  ; ****
  
  Procedure RegisterSQLiteFunctions(DataBase.i)
    Protected dataBaseID.i = DatabaseID(DataBase)
    
    sqlite3_create_function16(dataBaseID, "Sin",  1, #SQLITE_UTF8, #Null, @sql_sin(), #Null, #Null)
    sqlite3_create_function16(dataBaseID, "Cos",  1, #SQLITE_UTF8, #Null, @sql_cos(), #Null, #Null)
    sqlite3_create_function16(dataBaseID, "Tan",  1, #SQLITE_UTF8, #Null, @sql_tan(), #Null, #Null)
    
    ; sqlite3_create_function16(dataBaseID, "LCase",  1, #SQLITE_UTF8, #Null, @sql_lcase(), #Null, #Null)
    ; sqlite3_create_function16(dataBaseID, "UCase",  1, #SQLITE_UTF8, #Null, @sql_ucase(), #Null, #Null)
    
    ; Replace functions
    sqlite3_create_function16(dataBaseID, "lower",  1, #SQLITE_UTF16, #Null, @sql_lcase(), #Null, #Null)
    sqlite3_create_function16(dataBaseID, "upper",  1, #SQLITE_UTF16, #Null, @sql_ucase(), #Null, #Null)
    
    ; Same function name with diverent parameter count
    sqlite3_create_function16(dataBaseID, "myfunc",  1, #SQLITE_UTF8, #Null, @sql_myfunction(), #Null, #Null)
    sqlite3_create_function16(dataBaseID, "myfunc",  2, #SQLITE_UTF8, #Null, @sql_myfunction2(), #Null, #Null)
    
  EndProcedure
  
  ; ----
  
  Procedure CheckDatabaseUpdate(Database, Query.s)
    Protected r1
    r1 = DatabaseUpdate(Database, Query.s)
    If r1 = 0
      Debug "Error DatabaseUpdate: " + DatabaseError()
    EndIf
    ProcedureReturn r1
  EndProcedure
  
  Procedure CheckDatabaseQuery(Database, Query.s)
    Protected r1
    r1 = DatabaseQuery(Database, Query.s)
    If r1 = 0
      Debug "Error DatabaseQuery: " + DatabaseError()
    EndIf
    ProcedureReturn r1
  EndProcedure
  
  ; ----
  
  #DB_Calc = 0
  
  Global db_file.s, sql.s
  
  ; ----
  
  db_file = ":memory:"
  
  If Not OpenDatabase(#DB_Calc, db_file, "", "")
    Debug "Error Opendatabase"
    End
  EndIf
  
  RegisterSQLiteFunctions(#DB_Calc)
  
  sql = "CREATE TABLE vars (recid int, a double, b double, c double, d double)"
  If Not CheckDatabaseUpdate(#DB_Calc, sql)
    End
  EndIf
  
  sql = "INSERT INTO vars (recid, a, b, c, d) VALUES (0, 1, 2, 3, 4)"
  If Not CheckDatabaseUpdate(#DB_Calc, sql)
    End
  EndIf
  
  sql = "INSERT INTO vars (recid, a, b, c, d) VALUES (1, 10, 20, 30, 40)"
  If Not CheckDatabaseUpdate(#DB_Calc, sql)
    End
  EndIf
  
  sql = "INSERT INTO vars (recid, a, b, c, d) VALUES (2, 100, 200, 300, 400)"
  If Not CheckDatabaseUpdate(#DB_Calc, sql)
    End
  EndIf
  
  sql = "Select recid, (a * b + c) From vars"; where recid = 0"
  If CheckDatabaseQuery(#DB_Calc, sql)
    While NextDatabaseRow(#DB_Calc)
      Debug "RecID " + GetDatabaseLong(#DB_Calc, 0) + ": a * b + c = " + GetDatabaseDouble(#DB_Calc, 1)
      Debug "---------------------------"
    Wend
  EndIf
  
  sql = "Select sin(a), cos(a), tan(a) From vars"; where recid = 0"
  If CheckDatabaseQuery(#DB_Calc, sql)
    While NextDatabaseRow(#DB_Calc)
      Debug "Sin(a) = " + GetDatabaseDouble(#DB_Calc, 0)
      Debug "Cos(a) = " + GetDatabaseDouble(#DB_Calc, 1)
      Debug "Tan(a) = " + GetDatabaseDouble(#DB_Calc, 2)
      Debug "---------------------------"
    Wend
  EndIf
  
  sql = "Select lower('Hello World! ÄÖÜ'), upper('I Like Purebasic! äöü')"; where recid = 0"
  If CheckDatabaseQuery(#DB_Calc, sql)
    While NextDatabaseRow(#DB_Calc)
      Debug "LCase = " + GetDatabaseString(#DB_Calc, 0)
      Debug "UCase = " + GetDatabaseString(#DB_Calc, 1)
      Debug "---------------------------"
    Wend
  EndIf
  
  sql = "Select myfunc(1), myfunc(1.1,'Hello')"
  If CheckDatabaseQuery(#DB_Calc, sql)
    While NextDatabaseRow(#DB_Calc)
      Debug "0: Result = " + GetDatabaseString(#DB_Calc, 0)
      Debug "1: Result = " + GetDatabaseString(#DB_Calc, 1)
      Debug "---------------------------"
    Wend
  EndIf
  
CompilerEndIf
Last edited by mk-soft on Sun Feb 25, 2024 5:27 pm, edited 5 times in total.
My Projects ThreadToGUI / OOP-BaseClass / EventDesigner V3
PB v3.30 / v5.75 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace / OneDrive
Top
User avatar
mk-soft
Always Here
Always Here
Posts: 5406
Joined: Fri May 12, 2006 6:51 pm
Location: Germany

Re: SQLite C-Interface - Create Own Functions

Post by mk-soft »

Update v1.05.2
- Optimization
- Update example
:wink:
My Projects ThreadToGUI / OOP-BaseClass / EventDesigner V3
PB v3.30 / v5.75 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace / OneDrive
Top
User avatar
skywalk
Addict
Addict
Posts: 3999
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

Re: SQLite C-Interface - Create Own Functions

Post by skywalk »

Great stuff mk-soft :!:
Is it possible to add multi-row calculations like Stddev() as a user defined function?

EDIT:
Found pb versions of several sqlite extensions.c.
Looks like they work after changing .l to .i.

https://www.purebasic.fr/english/viewto ... 57#p516157
https://www.purebasic.fr/english/viewto ... ec#p602451

EDIT2:
Next I will try to make a spline function for 2 arrays returned from a query.
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
Top
User avatar
mk-soft
Always Here
Always Here
Posts: 5406
Joined: Fri May 12, 2006 6:51 pm
Location: Germany

Re: SQLite C-Interface - Create Own Functions

Post by mk-soft »

The examples for aggregate function are good. *xStep and *xFinal. I wanted to write my own examples for aggregate functions first.
You can pass multiple arguments to the *xStep function, but with *xFinal you can only return one value result.
My Projects ThreadToGUI / OOP-BaseClass / EventDesigner V3
PB v3.30 / v5.75 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace / OneDrive
Top
User avatar
skywalk
Addict
Addict
Posts: 3999
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

Re: SQLite C-Interface - Create Own Functions

Post by skywalk »

Yes, I was able to offload certain functions within the database, but now I need to collapse stdev() within SQL statements.
As mentioned, I am also interested in curve fitting splines for a pair of array columns, not just a single column.
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
Top
Post Reply

Return to “Tricks 'n' Tips”