Help for SQLite
-
- Posts: 28
- Joined: Fri Mar 15, 2019 2:35 pm
Help for SQLite
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.
- airsoftsoftwair
- Posts: 5443
- Joined: Fri Feb 12, 2010 2:33 pm
- Location: Germany
- Contact:
Re: Help for SQLite
The plugin is based on LuaSQLite3, so this might help:
http://lua.sqlite.org/index.cgi/doc/tip ... lite3.wiki
http://lua.sqlite.org/index.cgi/doc/tip ... lite3.wiki
Re: Help for SQLite
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:
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
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 )
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
- airsoftsoftwair
- Posts: 5443
- Joined: Fri Feb 12, 2010 2:33 pm
- Location: Germany
- Contact:
Re: Help for SQLite
Great trick, thanks for sharing!
Re: Help for SQLite
Very neat, thanks for sharing!
Currently using: Hollywood 9 with Windows IDE and Hollywood 9 with Visual Studio Code and hw4vsc