Help for SQLite

Discuss about plugins that don't have a dedicated forum
Post Reply
zenzizenzizenzic
Posts: 28
Joined: Fri Mar 15, 2019 2:35 pm

Help for SQLite

Post by zenzizenzizenzic »

Is there a good source of information for this? I'd like to know what data types are available, and did not see that in the documentation.
User avatar
airsoftsoftwair
Posts: 5425
Joined: Fri Feb 12, 2010 2:33 pm
Location: Germany
Contact:

Re: Help for SQLite

Post by airsoftsoftwair »

The plugin is based on LuaSQLite3, so this might help:
http://lua.sqlite.org/index.cgi/doc/tip ... lite3.wiki
User avatar
lazi
Posts: 625
Joined: Thu Feb 24, 2011 11:08 pm

Re: Help for SQLite

Post by lazi »

This was a long term problem of me, and obviously not a Hollywood one, rather than SQLite can be blamed for it.

SQLite string comparisons only works with ASCII characters. On all platforms.
When we use UTF-8 data,it is a pain handling the ORDER BY, LIKE, UPPER, LOWER and case insensitive comparisons. I have spent a lot of time to find a workaround without success.
As usual the solution is so simple that almost unbeliveable.

Similar to Hollywood where we can change internal functions easily the same feature is available for LuaSQLite too, which is the base of our plugin.

So, there is a sqlite3:create_function() method which could override standard SQLite functions. That's great!
Look at this, we can simply change SQLIite's UPPER function with Hollywood's one which is fully UTF-8 aware:

Code: Select all

db:create_function("UPPER", 1, Function(ctx,s) ctx:result(UpperStr(s)) EndFunction ) 
Simple enough, isn't it?

I love this, Andreas cheers! :)

The idea is came from this nice mind: https://techblog.dorogin.com/case-insen ... d4915079fc
The method's doc is here: http://lua.sqlite.org/index.cgi/doc/tip ... e_function
User avatar
airsoftsoftwair
Posts: 5425
Joined: Fri Feb 12, 2010 2:33 pm
Location: Germany
Contact:

Re: Help for SQLite

Post by airsoftsoftwair »

Great trick, thanks for sharing!
User avatar
Clyde
Posts: 348
Joined: Sun Feb 14, 2010 12:38 pm
Location: Dresden / Germany

Re: Help for SQLite

Post by Clyde »

Very neat, thanks for sharing!
Currently using: Hollywood 9 with Windows IDE and Hollywood 9 with Visual Studio Code and hw4vsc
Post Reply