PB 6.10b6; Win64; SQlite; ICU or load_extension;

[HanPBF]

Is load_extension enabled in SQLite for PureBasic?
Is ICU extension available so lower('ÄÖÜ') gets 'äöü'?

Thanks in advance

[mk-soft]

ICU is not included ..

https://sqlite.org/src/dir/ext/icu
https://www.sqlite.org/c3ref/load_extension.html

[HanPBF]

Thanks a lot for the info!

At least, I can use SQLite natively from PureBasic and enable loading of extension.
But, this is too much work.

Could PureBasic enable load_extension by default so "select load_extension(...)" can be used?

[idle]

You might be able to use utf16 module
Does inplace upper lower case or strip accents and has full case folding compares.
https://github.com/idle-PB/UTF16

[mk-soft]

Perhaps create own function ...

Link: SQLite C-Interface

You can also replace function.

Added Example Update 2

Code: Select all

;-TOP by mk-soft

UseSQLiteDatabase()

; SQLite C-Interface v1.04

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
  
  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
  
  ; ****
  
  Procedure RegisterSQLiteFunctions(DataBase.i)
    Protected dataBaseID.i = DatabaseID(DataBase)
    
    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)
    
  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 = 0
  
  Global db_file.s, sql.s
  
  ; ----
  
  db_file = ":memory:"
  
  If Not OpenDatabase(#DB, db_file, "", "")
    Debug "Error Opendatabase"
    End
  EndIf
  
  RegisterSQLiteFunctions(#DB)
  
  sql = "Select lower('Hello World! ÄÖÜ'), upper('I Like Purebasic! äöü')"; where recid = 0"
  If CheckDatabaseQuery(#DB, sql)
    While NextDatabaseRow(#DB)
      Debug "Lower = " + GetDatabaseString(#DB, 0)
      Debug "Upper = " + GetDatabaseString(#DB, 1)
      Debug "---------------------------"
    Wend
  EndIf
  
CompilerEndIf

[infratec]

You can always build your own sqlite dll with all features included and use this dll:

Code: Select all

UseSQLiteDatabase("mySQLite.dll")

[HanPBF]

@mk-soft I had to copy'n'paste Your code to get the solution... so much work...

Why does ImportC "" after UseSQLiteDatabase() work?
Is it exactly what PureBasic does internally?
Loading the library SQLite3.lib and ImportC "" uses that last one?

Thanks a lot for Your help!
Thanks @infratec for the info!

[mk-soft]

PB uses ImportC "" to search through its own existing libraries.
Makes things easier

[HanPBF]

A disadvantage in using registered functions is the missing index availability - index is not allowed for those kind of functions.
So compiling an own version of SQLite.dll would be better - something I don't want to do at all.

The best chance to clean up all of this is to replace all the special chars with standard ascii chars (ä -> ae, Ä -> AE etc.)

[DarkDragon]

A disadvantage in using registered functions is the missing index availability - index is not allowed for those kind of functions.
So compiling an own version of SQLite.dll would be better - something I don't want to do at all.

Are you saying you want to pay a developer to do it for you?

[HanPBF]

No, I say, I am not able to dive into the MinGW and "oh, and You have also to do..." world of this non trivial software.

I don't need PureBasic as C with another syntax.
That's not the idea, is it?

[DarkDragon]

No, I say, I am not able to dive into the MinGW and "oh, and You have also to do..." world of this non trivial software.

I don't need PureBasic as C with another syntax.
That's not the idea, is it?

Well, what's the point in software development if you never explore something new? You're already using SQL which is not PureBasic and you could even implement a database like SQLite yourself if you don't want to leave the PureBasic environment. No matter what language or what framework you'll use, you always have some limitations. That being said: don't give up, it's just SQLite and doesn't have many dependencies.

[HanPBF]

At the moment I analyze millions of words of my data and put all words existing in a table.
So, only comparing direct chars - as they are written.

Problem not solved but eliminated...

Well, what's the point in software development if you never explore something new?

I explore the basic theories and not the quirks or tricks someone develops.
Ok, I understand that SQLite has no chance here. mk-soft's solution with function extension is great.

implement a database like SQLite yourself if you don't want to leave the PureBasic environment.

What I can say: I have asked chatgpt to mimick closure functionality for PureBasic.
So I could put a procedure defintion in a procedure call as argument. Crazy enough: it worked for this little test.