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

Just starting out? Need help? Post your questions and find answers here.
HanPBF
Enthusiast
Enthusiast
Posts: 564
Joined: Fri Feb 19, 2010 3:42 am

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

Post by HanPBF »

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

Thanks in advance
User avatar
mk-soft
Always Here
Always Here
Posts: 5406
Joined: Fri May 12, 2006 6:51 pm
Location: Germany

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

Post by mk-soft »

Last edited by mk-soft on Sun Feb 25, 2024 11:04 am, edited 1 time 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
HanPBF
Enthusiast
Enthusiast
Posts: 564
Joined: Fri Feb 19, 2010 3:42 am

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

Post by 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?
User avatar
idle
Always Here
Always Here
Posts: 5097
Joined: Fri Sep 21, 2007 5:52 am
Location: New Zealand

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

Post by 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
User avatar
mk-soft
Always Here
Always Here
Posts: 5406
Joined: Fri May 12, 2006 6:51 pm
Location: Germany

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

Post by 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
Last edited by mk-soft on Sun Feb 25, 2024 1:34 pm, edited 4 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
infratec
Always Here
Always Here
Posts: 6874
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

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

Post by infratec »

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

Code: Select all

UseSQLiteDatabase("mySQLite.dll")
HanPBF
Enthusiast
Enthusiast
Posts: 564
Joined: Fri Feb 19, 2010 3:42 am

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

Post by HanPBF »

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

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!
User avatar
mk-soft
Always Here
Always Here
Posts: 5406
Joined: Fri May 12, 2006 6:51 pm
Location: Germany

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

Post by mk-soft »

PB uses ImportC "" to search through its own existing libraries.
Makes things easier ;)
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
HanPBF
Enthusiast
Enthusiast
Posts: 564
Joined: Fri Feb 19, 2010 3:42 am

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

Post by 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
Addict
Addict
Posts: 2228
Joined: Mon Jun 02, 2003 9:16 am
Location: Germany
Contact:

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

Post by DarkDragon »

HanPBF wrote: Mon Feb 26, 2024 10:16 am 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? 😉😄
bye,
Daniel
HanPBF
Enthusiast
Enthusiast
Posts: 564
Joined: Fri Feb 19, 2010 3:42 am

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

Post by 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
Addict
Addict
Posts: 2228
Joined: Mon Jun 02, 2003 9:16 am
Location: Germany
Contact:

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

Post by DarkDragon »

HanPBF wrote: Tue Feb 27, 2024 10:10 am 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.
bye,
Daniel
HanPBF
Enthusiast
Enthusiast
Posts: 564
Joined: Fri Feb 19, 2010 3:42 am

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

Post by 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.
Post Reply