Crypt database

[ProgAndy]

That was totally my assumption when doing it originally.

However, now you'll have me testing in different environments to be sure.

I just read the documentation again. Default is UTF-8, but SQLite also supports UTF16 in both byte orders. Since all SQL is internally converted to UTF-8 (at least till now) there is no difference.

[StungStang]

What is this issue about UTF-8 or 16?...

What i've to use?...

I'ts wrong to pass :

_SQLite_Exec(-1, "pragma key = 'My pass';")

It's seems to be pretty simple than our function

Hi!

[ProgAndy]

Just use UTF-8 its the default and for western languages it has a smaller overhead.

Your command is fine, and it does the same as the function. wrapping DLLCalls requires always some effort in AutoIt.

Edited March 4, 2011 by ProgAndy

[jchd]

@ProgAndy

I beg to differ regarding strings converted to binary.

Look, if I use Latin-1 (more precisely Windows Occidental ANSI) and I pass the string "5‰" I get that:

$str = "5‰"

ConsoleWrite(Binary($str), @LF)

--> 0x3589

You see that the per-thousand symbol ‰ is the ANSI 0x89

If I switch to a standard Korean ANSI setup, I obtain:

$str = "5‰"

ConsoleWrite(Binary($str), @LF)

--> 0x35A2B6

Binary() translates (or emasculates) the string to whatever ANSI setting is in force.

Only UTF-8 is independant of byte order and ANSI setting (obviously).

BTW, no, SQLite doesn't translate forcibly into UTF-8. The _data_ stored in the DB is in either UTF-8 or -16 encoding, depending on the initial choice during creation (default being UTF-8).

In the current SQLite UDF, all SQLite data exchange now use UTF-16 for SQLite functions interface (when an UTF-16 version is available). The interface encoding and the DB encoding are two completely separate things without any relationship (from the user's point of view).

The status of sqlite3_[re]key is a bit special as these two functions take a void * (i.e. byte *) and a length, so that if you supply a pointer to an UTF-16BE string it will use the bytes in the order of appearance, which will give a distinct result from the same string in UTF-16LE. Only if you supply text in UTF-8 are you guaranteed that the bytes used for passhphrase will be the same on any platform, that you use a MacInTrash, an iPhone, a PDP-7, an ARM CPU or whatever machine/OS SQLite is running on.

And YES I completely forgot to pass the DB handle. Big Ooops. I correct this and will repost when possible.

[StungStang]

mmm...at the ends of game...UTF 8 or UTF16? Now i'm using UTF16 i've to change? What is the best?...What give the smaller size of database?

Thanks

Hi!

Edited March 4, 2011 by StungStang

[jchd]

Which country do you live in, or more importantly, which is the most used language in the data you intend to store?

Since you seem to be collecting US radios, I'd say UTF-8 (the default) in good for you and will clearly give a smaller footprint, but I'd be very surprised if you would notice a significant performance change (either side) by switching to UTF-16.

[StungStang]

Currently i've a very long database that include a radio around the world (Asia, Europe, America, ecc..)...

You think i've to switch utf8?

Hi!...

P.S. This is not important but userfully...it's possible to integrate System.Data.SQLite.dll like as the sqlite.dll.au3 in the script?...or i've to install it with FileInstall function?

[ProgAndy]

@jchd: I meant Binary is universal, since you can fill everything in. It's true that you cannot rely on the automatic conversion, but you can create the binary representation you want manually and use that without problems. That's why I meant, your approch is easier to use since you don't have to bother when using strings. I started from the view, that the key is binary data and you have to choose how to create it, I don't know why I used a string in the example. Maybe directly using binary data would have been better to avoid confusion.

In the current SQLite UDF, all SQLite data exchange now use UTF-16 for SQLite functions interface (when an UTF-16 version is available). The interface encoding and the DB encoding are two completely separate things without any relationship (from the user's point of view).

I know, I have simplifiet it too much. Indeed, the UDF uses UTF-16 interfaces, but I refered to this part of the doc:

In the current implementation of SQLite, the SQL parser only works with UTF-8 text. So if you supply UTF-16 text it will be converted. This is just an implementation issue and there is nothing to prevent future versions of SQLite from parsing UTF-16 encoded SQL natively.

Since you call _SQLite_Exec, the given data is parsed via the SQL-parse I think, and therefore it is always converted to UTF-8, am I wrong?

PS: I know, never rely on a behaviour that is not given to be in future versions

PPS: Also, custom functions have to expose the supported encoding and SQLite converts the strings according to it. So I don't know what encodings are accepted by pragma key. Maybe it is just UTF-8, maybe only UTF-16, maybe both?

P.S. This is not important but userfully...it's possible to integrate System.Data.SQLite.dll like as the sqlite.dll.au3 in the script?...or i've to install it with FileInstall function?

Just use FileInstall. The way, the sqlite.dll is included is just a workaround since it is not possible to use FileInstall relative to the UDF directory, but only to the mainscript so a FileInstall in an UDF will most likely fail since it cannot find the source file. Edited March 4, 2011 by ProgAndy

[jchd]

@StungStang

As I said, you're unlikely to experience any performance difference between an UTF-16 DB and the same DB encoded in UTF-8 unless your DB useage is highly specific (probably not your case).

The encoding is choosen only once at creation time and can't be changed. You can still dump the DB, change a parameter in the dump file and re-create a new DB in UTF-16 having the exact same user content.

For now, just use the UTF-8 default and you'll be fine for any purpose. Unless your DB contains more than (say) 50% of asian language text, use UTF-8 for best speed and size.

FYI some users of SQLite have databases > 80Tb running in data centers with hundreds or even thousands simultaneous connections, other have phonebook applications running on low-end smartphones, or geographic DB in GPS devices.

Even if there may exist a significant number of web radios worldwide, the resulting DB will be fairly small compared to the engine's capabilities.

There are many other speed/size sensitive settings depending on your DB, its schema and your use profile.

I'll get back to you later to inform you about an extension I've writen that could prove very useful in your case.

Follow ProgAndy's advice and simply FileInstall the DLL you want. SQLite.dll.au3 is just a clusmy way to have new users find an easy to use DB engine.

@ProgAndy

About the fact that the SQL parser is an UTF-8 thingy: yes you push a sensitive button! I'd like to have dllcalls more efficient and use bindings for variables and litteral (you choose the right bind type and encoding for every binding), but this is a performance bottleneck in AutoIt. So yes, for any forseable AutoIt future, the _exec and various _query functions will pass native UCS-2 (= single-word subset of UTF-16 used by AutoIt) native strings leaving the conversion to UTF-8 to the engine core. The reason is that doing the conversion to UTF-8 in AutoIt in the UDF would be much MUCH slower than the same conversion done by thight C code in the SQLite engine. I've seen good performance improvement when I switched the UDF from using UTF-8 sqlite API to UTF-16 calls.

The parser is free to convert input into any encoding of its choice, that's completely transparent.

I've lookdes at the code for both sqlite3_[re]key and the corresponding pragmas code and it all of them appear to take the string parameter as a zstring of bytes. That's why passing the string as UTF-8 in the function case will lead to the same result as passing the string in the pragma. Passing an UTF-16 'ABC' to the _key functions would give a distinct result from passing 'ABC' to the pragma. The _key functions are core functions, not extension functions. As such there is no registration where you specify a prefered input encoding.