[sqlite] Andl: choices for generic types: bool, binary, number/decimal, date/datetime, text/varchar

Just a final point -- the Sqlite text16() routines are a perfect match for C# Marshall.PtrToStringUni(). Definitely the nicest solution for my problem, regardless of database encoding. Thanks all. Regards David M Bennett FACS Andl - A New Database Language - andl.org -Original Message-

[sqlite] Unicode issue on windows consoles. Was: Version 3.11.0 beta

bbed... Name: signature.asc Type: application/pgp-signature Size: 842 bytes Desc: Message signed with OpenPGP using GPGMail URL: <http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20160212/1f0df2ea/attachment.pgp>

[sqlite] Andl: choices for generic types: bool, binary, number/decimal, date/datetime, text/varchar

On 10 February 2016 at 11:14, wrote: > >>>Every SQLite database file has a text encoding that applies to the > entire > file: one of utf8, utf16be, or utf16le. The database text encoding is > stored in the header. You can see the encoding for a particular database > using: > > sqlite3

[sqlite] Unicode issue on windows consoles. Was: Version 3.11.0 beta

Olivier Mascia wrote: >> Le 11 f?vr. 2016 ? 23:13, Clemens Ladisch a ?crit : >> - stdin from the console > > Convert from the codepage returned by GetConsoleCP() to UTF8. >> - stdout to the console > > Convert from UTF8 to the codepage returned by GetConsoleOutputCP(). This would corrupt any

[sqlite] query Benchmark

Hi all, is there a way to make a benchmark of queries to check which version is faster? I'm using sqlite shell, the question is about on how to make repetitive tests in the same conditions (for example I need to totally disable cache to avoid different results the second time query is

[sqlite] query Benchmark

On 12 Feb 2016, at 8:47am, Michele Pradella wrote: > Hi all, is there a way to make a benchmark of queries to check which version > is faster? I'm using sqlite shell, the question is about on how to make > repetitive tests in the same conditions (for example I need to totally > disable

[sqlite] query Benchmark

I'm already using my software. The scope of test my query with sqlite shell is to have a test environment without other interaction, just to test witch is the fast version of my query to use in my software. Another question is, if I have a query like this: SELECT *field* FROM car_plates WHERE

[sqlite] query Benchmark

Just to understand, if I have the query with LIKE simplified: SELECT field FROM car_plates WHERE (field LIKE 'EX011%'); I can't use index by field now suppose you know text value you can have in field, I can make the query: SELECT field FROM car_plates WHERE (field>='EX011A')AND(field<='EX011Z');

[sqlite] query Benchmark

http://sqlite.org/rtree.html To improve your query performance, you need a r-tree index on DateTime ???: Michele Pradella : ?2016/?2/?12 17:28 ???: sqlite-users at mailinglists.sqlite.org

[sqlite] query Benchmark

On 12 Feb 2016, at 9:28am, Michele Pradella wrote: > I'm already using my software. The scope of test my query with sqlite shell > is to have a test environment without other interaction, just to test witch > is the fast version of my query to use in my software. Okay. I understand why you

[sqlite] query Benchmark

On 12 Feb 2016, at 10:14am, Michele Pradella wrote: > I can make the query: > SELECT field FROM car_plates WHERE (field>='EX011A')AND(field<='EX011Z'); > now I can use the index the query is faster. SQLite makes this optimization for you. See section 4.0 of

[sqlite] Unicode issue on windows consoles. Was: Version 3.11.0 beta

penPGP using GPGMail URL: <http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20160212/c8497d08/attachment.pgp>

[sqlite] query Benchmark

Ok understood...anyway trying with sqlite shell it's seams that (filed LIKE 'AA%') is slower than (field>='AAA' AND field<='AAZ') do you think there's a way I can check if the optimization is working? Selea s.r.l. Michele Pradella R SELEA s.r.l. Via Aldo Moro 69 Italy -

[sqlite] query Benchmark

Moreover if I make field LIKE 'AA%' and I use INDEXED BY index_on_field shell return me "Error: no query solution" so it's seams sqlite with LIKE operator can't use index on field Selea s.r.l. Michele Pradella R SELEA s.r.l. Via Aldo Moro 69 Italy - 46019 Cicognara (MN) Tel

[sqlite] query Benchmark

I try using COLLATE NOCASE index with PRAGMA case_sensitive_like=OFF; and COLLATE BINARY index with PRAGMA case_sensitive_like=ON; but I have always the query with field LIKE 'AA%' that can't use index on field "No Query solution" is reported by shell if you try to force index. And with explain

[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

I noticed my queries going very slowly after changing a join to a left join, examination of the query plan showed that the optimiser was choosing a poor plan. It only occurs when the left join is on a views, if explicit tables are used instead then the problem does not occur. To repeat the issue

[sqlite] query Benchmark

On 02/12/2016 03:47 PM, Michele Pradella wrote: > Hi all, is there a way to make a benchmark of queries to check which > version is faster? I'm using sqlite shell, the question is about on > how to make repetitive tests in the same conditions (for example I > need to totally disable cache to

[sqlite] query Benchmark

On 2/12/16, Michele Pradella wrote: > I try using COLLATE NOCASE index with PRAGMA case_sensitive_like=OFF; > and COLLATE BINARY index with PRAGMA case_sensitive_like=ON; > but I have always the query with field LIKE 'AA%' that can't use index > on field "No Query solution" is reported by shell

[sqlite] query Benchmark

table: CREATE TABLE IF NOT EXISTS car_plates (Id INTEGER PRIMARY KEY AUTOINCREMENT,DateTime BIGINT,StringKey VARCHAR(255) UNIQUE,Plate VARCHAR(255),Type BIGINT,CameraName VARCHAR(255),CameraIP VARCHAR(255),CameraMAC VARCHAR(255),FileName VARCHAR(255),Country VARCHAR(255),Confidence

[sqlite] query Benchmark

On 2/12/16, Michele Pradella wrote: > table: > CREATE TABLE IF NOT EXISTS car_plates (Id INTEGER PRIMARY KEY > AUTOINCREMENT,DateTime BIGINT,StringKey VARCHAR(255) UNIQUE,Plate > VARCHAR(255),Type BIGINT,CameraName VARCHAR(255),CameraIP > VARCHAR(255),CameraMAC VARCHAR(255),FileName

[sqlite] query Benchmark

ok, assume casr_sensitive_like=OFF (default), according the point 6 of LIKE optimization: http://www.sqlite.org/optoverview.html should be the same to have my table definition and CREATE INDEX IF NOT EXISTS car_plates_plate ON car_plates(Plate COLLATE NOCASE); Correct? because that way LIKE do

[sqlite] Unicode issue on windows consoles. Was: Version 3.11.0 beta

On Fri, Feb 12, 2016 at 11:36 AM, Olivier Mascia wrote: > > So it would be a bad idea to change sqlite3's output depending on the > > current code page or font. > > I share your view on keeping things as unicode as possible, but the > frontier is thin before trying to impose our views onto

[sqlite] query Benchmark

No...I do not understand, try dump file and re-import but the result it's always the same even if I use PRAGMA case_sensitive_like=ON; the query PRAGMA case_sensitive_like=ON; EXPLAIN QUERY PLAN SELECT DateTime,FileName,Plate,Type,CameraName,Id,Country,Reason,CarPlateType,VehicleType,GPS

[sqlite] Unicode issue on windows consoles. Was: Version 3.11.0 beta

Olivier Mascia wrote: > it looks evident that the command-line tool of sqlite3 is meant to be > compiled as a narrow I/O program. It uses the standard main() and not > wmain(), ... and does not attempt to use the wide chars in or out > channels. That's just because UTF-8 is the most portable

[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

Dave Blake wrote: > I noticed my queries going very slowly after changing a join to a left > join, examination of the query plan showed that the optimiser was choosing > a poor plan. It chooses a _correct_ plan. > It only occurs when the left join is on a views, if explicit > tables are used

[sqlite] query Benchmark

the strange thing is that if you create CREATE TABLE car_plates (Id INTEGER PRIMARY KEY AUTOINCREMENT,DateTime BIGINT,StringKey VARCHAR(255) UNIQUE,Plate VARCHAR(255),Type BIGINT,CameraName VARCHAR(255),CameraIP VARCHAR(255),CameraMAC VARCHAR(255),FileName VARCHAR(255),Country

[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

Thank you for looking at this. >It chooses a _correct_ plan. Really? With query 1) to select a song it first scans the song_artist table that could contain many thousands of records. That does not seem right. >Your queries are quite different. They produce the same result sets, but yes one

[sqlite] query Benchmark

If and only If you follow rule 6 and declare the column with a collation sequence that matches the collation in effect for the LIKE operator. So if the column/index is declared with CASE SENSITIVE collation (BINARY, the default), and you use like on that column, and the LIKE is case

[sqlite] query Benchmark

Why this query PRAGMA case_sensitive_like=ON;EXPLAIN QUERY PLAN SELECT * FROM car_plates INDEXED BY car_plates_plate WHERE ((CarPlateType!=-1)AND((Plate LIKE 'AA00O%')OR(Plate LIKE 'AA0O0%')))OR((CarPlateType==-1)AND((Plate LIKE '~A00O%'))) give me "Error: no query solution" if I remove the

[sqlite] Windows A and W APIs dual support

Size: 842 bytes Desc: Message signed with OpenPGP using GPGMail URL: <http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20160212/c1a4f0c0/attachment.pgp>

[sqlite] query Benchmark

Splitting query in 2 SELECT using UNION let me use car_plates_plate index without problemvery strange...but I found a walkaround Selea s.r.l. Michele Pradella R SELEA s.r.l. Via Aldo Moro 69 Italy - 46019 Cicognara (MN) Tel +39 0375 889091 Fax +39 0375 889080

[sqlite] query Benchmark

On 2/12/16, Michele Pradella wrote: > Why this query > PRAGMA case_sensitive_like=ON;EXPLAIN QUERY PLAN SELECT * FROM > car_plates INDEXED BY car_plates_plate > WHERE ((CarPlateType!=-1)AND((Plate LIKE 'AA00O%')OR(Plate LIKE > 'AA0O0%')))OR((CarPlateType==-1)AND((Plate LIKE '~A00O%'))) > > give

[sqlite] query Benchmark

Already solved with UNION of SELECT Selea s.r.l. Michele Pradella R SELEA s.r.l. Via Aldo Moro 69 Italy - 46019 Cicognara (MN) Tel +39 0375 889091 Fax +39 0375 889080 *michele.pradella at selea.com* *http://www.selea.com* Il 12/02/2016

[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

Dave Blake wrote: >> It chooses a _correct_ plan. > > Really? With query 1) to select a song it first scans the song_artist table > that could contain many thousands of records. That does not seem right. The result is correct. This would not necessarily be the case if it executed an outer join

[sqlite] Windows A and W APIs dual support

Olivier Mascia wrote: > Are there Windows platforms, supported by SQLite source code of course, where > the 'W' version of the APIs are not available? Once upon a time, SQLite supported Windows 95/98/Me. Nowadays, the code is still there, but untested. Regards, Clemens

[sqlite] Windows A and W APIs dual support

On Feb 12, 2016, at 1:49 PM, Clemens Ladisch wrote: > > Olivier Mascia wrote: >> Are there Windows platforms, supported by SQLite source code of course, >> where the 'W' version of the APIs are not available? > > Once upon a time, SQLite supported Windows 95/98/Me. The DOS-based versions of

[sqlite] Windows A and W APIs dual support

windows W is wide-char not utf-16. as much as A is ansi and isn't utf-8 On Fri, Feb 12, 2016 at 1:07 PM, Warren Young wrote: > On Feb 12, 2016, at 1:49 PM, Clemens Ladisch wrote: >> >> Olivier Mascia wrote: >>> Are there Windows platforms, supported by SQLite source code of course, >>> where

[sqlite] Windows A and W APIs dual support

On Fri, Feb 12, 2016 at 4:05 PM, J Decker wrote: > windows W is wide-char not utf-16. > as much as A is ansi and isn't utf-8 > Has Windows ever supported a wide character set that was not UCS-2 or UTF-16? I've always understood Microsoft embraced UCS-2 specifically so that it would not have to

[sqlite] Windows A and W APIs dual support

well mbstowc and vice versa only understand 16 bit encodings, and do not generate codpairsand do generate d800-dfff characters which are entirely illegal in utf-16 (without corresponding pair) But; fortunately, they do end up supporting utf-8 since it's just a stream of bytes with a nul

[sqlite] Windows A and W APIs dual support

On 2/12/2016 7:24 PM, J Decker wrote: > well mbstowc and vice versa only understand 16 bit encodings, and do > not generate codpairsand do generate d800-dfff characters which > are entirely illegal in utf-16 (without corresponding pair) What character in what ANSI codepage ends up converted

[sqlite] Windows A and W APIs dual support

On Feb 12, 2016, at 4:42 PM, Scott Robison wrote: > > I find it kind of interesting that Microsoft takes a lot > of (deserved) flack for not adhering to standards, yet UTF-8 came about > specifically because some didn't want to use UCS-2 ?for good reason. UCS-2/UTF-16 isn?t compatible with C

[sqlite] Windows A and W APIs dual support

On Fri, Feb 12, 2016 at 5:32 PM, Igor Tandetnik wrote: > On 2/12/2016 7:24 PM, J Decker wrote: >> > What character in what ANSI codepage ends up converted by mbstowcs to an > unpaired surrogate? > > What character in what ANSI codepage requires a surrogate pair to represent > (that is,

[sqlite] Windows A and W APIs dual support

On Fri, Feb 12, 2016 at 7:05 PM, Warren Young wrote: > On Feb 12, 2016, at 4:42 PM, Scott Robison wrote: >> >> I find it kind of interesting that Microsoft takes a lot >> of (deserved) flack for not adhering to standards, yet UTF-8 came about >> specifically because some didn't want to use UCS-2

[sqlite] Windows A and W APIs dual support

On 2/12/2016 10:14 PM, J Decker wrote: > mbstowcs( out, utf8, 5 ); mbstowcs expects the string in the codepage of the current locale - which is never UTF-8. > for( n = 0; n < 5; n++ ) > printf( "%04x ", out[n] ); // output is 00f0 0090 0080 0081; expect d800 dc01 Why do you expect that? It

[sqlite] Windows A and W APIs dual support

On Fri, Feb 12, 2016 at 7:37 PM, Igor Tandetnik wrote: > On 2/12/2016 10:14 PM, J Decker wrote: >> >> mbstowcs( out, utf8, 5 ); > > > mbstowcs expects the string in the codepage of the current locale - which is > never UTF-8. > >> for( n = 0; n < 5; n++ ) >> printf( "%04x ", out[n] ); // output

[sqlite] Windows A and W APIs dual support

On 2/12/2016 10:44 PM, J Decker wrote: > On Fri, Feb 12, 2016 at 7:37 PM, Igor Tandetnik wrote: >> It performs the conversion it is documented to perform. It indeed doesn't >> perform the conversion that you, for reasons unclear, expect it to perform. >> In other words, you engage in wishful

[sqlite] Windows A and W APIs dual support

On Fri, Feb 12, 2016 at 8:00 PM, Igor Tandetnik wrote: > On 2/12/2016 10:44 PM, J Decker wrote: >> >> On Fri, Feb 12, 2016 at 7:37 PM, Igor Tandetnik >> wrote: >>> >>> It performs the conversion it is documented to perform. It indeed doesn't >>> perform the conversion that you, for reasons

[sqlite] Windows A and W APIs dual support

On Fri, Feb 12, 2016 at 8:00 PM, Igor Tandetnik wrote: > On 2/12/2016 10:44 PM, J Decker wrote: >> >> On Fri, Feb 12, 2016 at 7:37 PM, Igor Tandetnik >> wrote: >>> >>> It performs the conversion it is documented to perform. It indeed doesn't >>> perform the conversion that you, for reasons

[sqlite] Windows A and W APIs dual support

On Fri, Feb 12, 2016 at 8:05 PM, Warren Young wrote: > On Feb 12, 2016, at 4:42 PM, Scott Robison > wrote: > > > > I find it kind of interesting that Microsoft takes a lot > > of (deserved) flack for not adhering to standards, yet UTF-8 came about > > specifically because some didn't want to