[sqlite] lsm1 extension

[Charles Leifer]

Hi,

I'm not quite sure of the proper way to compile the lsm1 extension (in the
lsm-vtab branch). I ended up hand-hacking the makefile to replace $(TCCX)
and add the appropriate flags for gcc (-fPIC) to get it to build. I'd like
to do it the "right way", though, as I was hoping to share some
instructions on this exciting new extension with a colleague.

Anyone able to help? Thanks!

Charlie
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JSON1 extension & speed-up by using indices etc.?

[Jens Alfke]

> On Jun 19, 2017, at 5:43 PM, Simon Slavin  wrote:
> 
>> You can create indexes to support JSON1 queries by using the same json_xx 
>> function calls in a CREATE INDEX statement.
> 
> That’s a great idea.  I don’t know if it works, though.

It does, and I believe it was the primary use case driving the extension of 
CREATE INDEX to take an expression as well as a column name.

This makes querying JSON workable in practice, and it can be used for other 
purposes too. For example, you could register a function that extracts image 
metadata from EXIF tags, and then be able to efficiently index and query that 
metadata on JPEG images stored in blob columns.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JSON1 extension & speed-up by using indices etc.?

[Wout Mertens]

On Tue, Jun 20, 2017 at 2:43 AM Simon Slavin  wrote:

> On 20 Jun 2017, at 1:34am, Jens Alfke  wrote:
>
> > You can create indexes to support JSON1 queries by using the same
> json_xx function calls in a CREATE INDEX statement.
>
> That’s a great idea.  I don’t know if it works, though.
>
>
This works great, I use it all the time. As long as you use the exact same
calculated statement in the index as in the query, the index will be used
instead of calculating the value.

Wout.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JSON1 extension & speed-up by using indices etc.?

[Simon Slavin]

On 20 Jun 2017, at 1:34am, Jens Alfke  wrote:

> My understanding from reading the docs is that SQLite view’s aren’t “built” 
> at all: their contents have no physical existence in the database, the views 
> are simply macros that transform the statements that use them. (Correct me if 
> I’m wrong; I haven’t actually used views.)

You’re right.  When you create a VIEW SQLite stores the CREATE VIEW command, 
and not the data it retrieves.  When the VIEW is used in a later command SQLite 
integrates the two commands.

> You can create indexes to support JSON1 queries by using the same json_xx 
> function calls in a CREATE INDEX statement.

That’s a great idea.  I don’t know if it works, though.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JSON1 extension & speed-up by using indices etc.?

[Jens Alfke]

> On Jun 19, 2017, at 6:50 AM, Robert M. Münch  
> wrote:
> 
> This view works and of course takes some time to build.

My understanding from reading the docs is that SQLite view’s aren’t “built” at 
all: their contents have no physical existence in the database, the views are 
simply macros that transform the statements that use them. (Correct me if I’m 
wrong; I haven’t actually used views.)

> Can the JSON1 extension be supported by adding indices or whatever, that 
> speeds-up the VIEW construction/updates?

You can create indexes to support JSON1 queries by using the same json_xx 
function calls in a CREATE INDEX statement.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_exec statement count including create/drop?

[petern]

Hi Clemens.

Found the problem.  My IDE is picking up other installed libsqlite3.so.  It
was wrong to think that uninstalling the distro's SQLite package would
purge it from /usr/lib.  Other copies still exist.  I'm sure glad the
project is now linking the correct library file!

Regarding sqlite3_trace_v2(db, SQLITE_TRACE_PROFILE,...).  Calls to
sqlite3_sql() on the third trace callback parameter (cast to sqlite_stmt*)
do retrieve the correct part of the the original sqlite3_exec input SQL
string except for ill formed SQL.  Apparently the SQLITE_TRACE_PROFILE
event is not generated for the failed statement.

It would have been helpful to see the event and the parse point of the
failed statement.

At the same time, I found that setting PRAGMA empty_result_callbacks=1 also
has the desired effect of causing sqlite3_exec callbacks on every statement
including create/drop.  However, there is no way of accessing the statement
sql at all that way.

Thanks for your help so far.  This was worth it just to straighten out my
linker issues!

Do you have further thoughts on getting the parse point of the failed
statement from sqlite3_exec on a multiple statement SQL string?

Peter





On Mon, Jun 19, 2017 at 1:30 PM, Clemens Ladisch  wrote:

> petern wrote:
> > linker says -> "undefined reference to `sqlite3_trace_v2'"
>
> Then your distribution's package appears to be compiled with
> SQLITE_OMIT_TRACE.
>
> Just add sqlite3.c to your project.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_exec statement count including create/drop?

[Clemens Ladisch]

petern wrote:
> linker says -> "undefined reference to `sqlite3_trace_v2'"

Then your distribution's package appears to be compiled with
SQLITE_OMIT_TRACE.

Just add sqlite3.c to your project.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_exec statement count including create/drop?

[petern]

Clemens.  I'm trying you suggestion but...

linker says -> "undefined reference to `sqlite3_trace_v2'"

[I am linking to libsqlite3.so.  Version by sqlite_version() is 3.19.3.]

Is sqlite3_trace_v2() defined by a SQLite compile time option and if so
what is it?

Help page has only negative SQLITE_OMIT_TRACE:
https://sqlite.org/compile.html

I don't see anything called TRACE in bld/Makefile.




On Mon, Jun 19, 2017 at 1:05 AM, Clemens Ladisch  wrote:

> petern wrote:
> > Is there a C API way to get a total count or notification as each
> statement
> > is prepared by sqlite_prepare_v2 within sqlite3_exec?
>
> sqlite3_trace_v2() with SQLITE_TRACE_PROFILE is called at the end of each
> statement.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to search for fields with accents in UTF-8 data?

[David Raymond]

The Windows command prompt and unicode have always not played well with each 
other. SQLite itself works perfectly with data on disk or in the database, 
there are just translation and display problems when going to and from the 
command prompt.

If you write out your query in, say, Notepad++ and save it in UTF-8, then you 
can do ".read queryFile.txt" from the CLI and be sure that it's reading it ok. 
(Assuming of course your DB isn't using one of the UTF-16 options) The output 
may still look weird if it would include accented characters, but anything like 
count(*) or unicode(something) that return numbers, or anything that's ASCII 
will always look ok.


foo.txt: Saved in UTF-8

.bail on
.echo on
create table if not exists foo (foo text collate nocase);
insert or ignore into foo values ('Île-de-France');
select * from foo;
select char(206), unicode('Î');
select count(*) from foo where foo = 'Île-de-France';

end foo.txt



D:\Temp>sqlite3
SQLite version 3.19.3 2017-06-08 14:26:16
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> .read foo.txt
create table if not exists foo (foo text collate nocase);
Run Time: real 0.001 user 0.00 sys 0.015600
insert or ignore into foo values ('Île-de-France');
Run Time: real 0.000 user 0.00 sys 0.00
select * from foo;
--EQP-- 0,0,0,SCAN TABLE foo
foo
Île-de-France
Run Time: real 0.001 user 0.00 sys 0.00
select char(206), unicode('Î');
char(206)|unicode('Î')
Î|206
Run Time: real 0.000 user 0.00 sys 0.00
select count(*) from foo where foo = 'Île-de-France';
--EQP-- 0,0,0,SCAN TABLE foo
count(*)
1
Run Time: real 0.000 user 0.00 sys 0.00

sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JSON1 extension & speed-up by using indices etc.?

[Simon Slavin]

On 19 Jun 2017, at 2:50pm, Robert M. Münch  wrote:

> Hi, I have a table A(rec_id, JSON-of-record) and I create a view like this:
> 
> CREATE VIEW json AS SELECT rec_id, json_extract(json_value,'$.col-1') as 
> col1, json_extract(json_value,'$.col-2') as col2, ... , 
> json_extract(json_value,'$.col-50') as col50 FROM a
> 
> 
> All SELECT requests will then run against the view.
> 
> This view works and of course takes some time to build.

Please supply a same SELECT command that you would use this VIEW for.  I’m 
especially interested in clauses such as GROUP BY, ORDER BY and WHERE.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to search for fields with accents in UTF-8 data?

[Olivier Mascia]

> Le 19 juin 2017 à 15:20, Simon Slavin  a écrit :
> 
> On 19 Jun 2017, at 11:13am, Gilles  wrote:
> 
>> It looks running sqlite3.exe in a terminal window (CMD) in Windows 7 doesn't 
>> work: Apparently, it doesn't support UTF-8.
> 
> Correct.  And the "it" that doesn’t support UTF-8 is the Windows console.  
> SQLite works fine and handles everything as Unicode internally.  The Windows 
> console won’t process multibyte characters internally and can’t display them 
> correctly.
> 
> 
> 
> "many legacy applications continue to use character sets based on code pages. 
> Even new applications sometimes have to work with code pages, often for one 
> of the following reasons: […]
>   • To communicate with the Windows Console, which does not support 
> Unicode."
> 
> 
> Some people have found ways to hack around this, but they simulate compliance 
> for a certain codepage rather than implement UTF-8 globally.
> 
> 
> 
> "The Windows Console doesn’t support Unicode."
> 
> 
> 
> Simon.

Switch the console I/O (windows only of course) of sqlite3 shell.c to use 
WriteConsoleW and ReadConsoleW, and there you go and forget about CHCP, 
codepages... Learned it the hard way last year, well after at some point I 
though DBCS would be enough. No.

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to search for fields with accents in UTF-8 data?

[Hick Gunter]

CP1252 = Windows-1252 = ISO 8859-1 aka Latin-1, an extension of ASCII

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Gilles
Gesendet: Montag, 19. Juni 2017 16:23
An: SQLite Maillist 
Betreff: Re: [sqlite] How to search for fields with accents in UTF-8 data?

Found the problem: Turns out the CSV file isn't in UTF8 but in CP1252 :-/

Icon.exe can be used to convert a file before importing it in SQLite.
https://dbaportal.eu/2012/10/24/iconv-for-windows/

Thanks everyone for the help.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to search for fields with accents in UTF-8 data?

[Gilles]

Found the problem: Turns out the CSV file isn't in UTF8 but in CP1252 :-/

Icon.exe can be used to convert a file before importing it in SQLite.
https://dbaportal.eu/2012/10/24/iconv-for-windows/

Thanks everyone for the help.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] JSON1 extension & speed-up by using indices etc.?

[Robert M. Münch]

Hi, I have a table A(rec_id, JSON-of-record) and I create a view like this:

CREATE VIEW json AS SELECT rec_id, json_extract(json_value,'$.col-1') as col1, 
json_extract(json_value,'$.col-2') as col2, ... , 
json_extract(json_value,'$.col-50') as col50 FROM a


All SELECT requests will then run against the view.

This view works and of course takes some time to build. Can the JSON1 extension 
be supported by adding indices or whatever, that speeds-up the VIEW 
construction/updates?

Viele Grüsse.

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using key/value idea to store data & creating normal table VIEW?

[Robert M. Münch]

On 19 Jun 2017, at 10:47, Robert M. Münch wrote:

> Well, the question for me is, which approach will be faster?
> ...

Hi, answering my own question as I tried it with 5 records and 50 columns. 
The JSON approach is way faster up to factor 1000.

Viele Grüsse.

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to search for fields with accents in UTF-8 data?

[Simon Slavin]

On 19 Jun 2017, at 11:13am, Gilles  wrote:

> It looks running sqlite3.exe in a terminal window (CMD) in Windows 7 doesn't 
> work: Apparently, it doesn't support UTF-8.

Correct.  And the "it" that doesn’t support UTF-8 is the Windows console.  
SQLite works fine and handles everything as Unicode internally.  The Windows 
console won’t process multibyte characters internally and can’t display them 
correctly.



"many legacy applications continue to use character sets based on code pages. 
Even new applications sometimes have to work with code pages, often for one of 
the following reasons: […]
• To communicate with the Windows Console, which does not support 
Unicode."


Some people have found ways to hack around this, but they simulate compliance 
for a certain codepage rather than implement UTF-8 globally.



"The Windows Console doesn’t support Unicode."



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error message on insert

[Tim Streater]

On 19 Jun 2017, at 12:2, Keith Medcalf  wrote:

> insert into filters (absid, filter_name, enabled, filter_order) 
> values (null, 'Untitled filter', 0, coalesce((select max(filter_order) from
> filters)+1,1))
>
> If you want the filter_order by filter_name then you would need:
>
> insert into filters (absid, filter_name, enabled, filter_order) 
> values (null, 'Untitled filter', 0, coalesce((select max(filter_order) from
> filters where filter_name='Untitled filter')+1,1))
>
> (you need the coalesce to make sure the initial value is 1, unless you have
> defined the column in the table with a default of 1)
>
> -- 
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı

Thanks, Keith and Chris. I had actually tried a "select max(…", but without, it 
would seem, enough brackets.


--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error message on insert

[Chris Locke]

insert into filters (absid, filter_name, enabled, filter_order) values
(null, 'Untitled filter', 0, ((select max(filter_order) from filters)+1)

On Mon, Jun 19, 2017 at 11:46 AM, Tim Streater  wrote:

> I want to insert a new row in my table, and while doing so setting a
> column to one more than the maximum value of that column, thus:
>
>insert into filters (absid, filter_name, enabled, filter_order) values
> (null, 'Untitled filter', 0, max(filter_order)+1)
>
> However I get "Error: no such column: filter_order”. I had a look at the
> syntax diagram for insert which would seem to permit the above.
>
> I can do this in code anyway so it’s not a show stopper, but what have I
> done incorrectly? My IDE appears to use sqlite 3.14.1.
>
> Thanks.
>
>
> --
> Cheers  --  Tim
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unusual but trivially reproducible bug

[Hick Gunter]

Limit2 is taken to be the alias of table foo.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Robert Cousins
Gesendet: Sonntag, 18. Juni 2017 21:19
An: sqlite-users@mailinglists.sqlite.org
Betreff: [sqlite] unusual but trivially reproducible bug

Summary:
  Leaving out the space after the word 'limit' causes the limit clause to 
be ignored.
I've reproduced it on version 3.19.2 2017-05-25 16:50:27
edb4e819b0c058c7d74d27ebd14cc5ceb2bad6a6144a486a970182b7afe3f8b9
A sample output is below.
On one hand, this is a failure to catch a syntax error. On the other hand, 
the fact that it isn't caught is perhaps problematic and indicates an 
underlying parser issue.
Thanx

sqlite> create table foo(id Integer not null primary key, bar int);
sqlite> insert into foo values (NULL,1),(NULL,2),(NULL,3); select * from
sqlite> foo limit1;
id  bar
--  --
1   1
2   2
3   3
sqlite> select * from foo limit2;
id  bar
--  --
1   1
2   2
3   3
sqlite> select * from foo limit 1;
id  bar
--  --
1   1
sqlite> select * from foo limit 2;
id  bar
--  --
1   1
2   2
sqlite>

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error message on insert

[Keith Medcalf]

insert into filters (absid, filter_name, enabled, filter_order) 
values (null, 'Untitled filter', 0, coalesce((select max(filter_order) from 
filters)+1,1))

If you want the filter_order by filter_name then you would need:

insert into filters (absid, filter_name, enabled, filter_order) 
values (null, 'Untitled filter', 0, coalesce((select max(filter_order) from 
filters where filter_name='Untitled filter')+1,1))

(you need the coalesce to make sure the initial value is 1, unless you have 
defined the column in the table with a default of 1)

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı


> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Tim Streater
> Sent: Monday, 19 June, 2017 04:47
> To: SQLite Users
> Subject: [sqlite] Error message on insert
> 
> I want to insert a new row in my table, and while doing so setting a
> column to one more than the maximum value of that column, thus:
> 
>insert into filters (absid, filter_name, enabled, filter_order) values
> (null, 'Untitled filter', 0, max(filter_order)+1)
> 
> However I get "Error: no such column: filter_order”. I had a look at the
> syntax diagram for insert which would seem to permit the above.
> 
> I can do this in code anyway so it’s not a show stopper, but what have I
> done incorrectly? My IDE appears to use sqlite 3.14.1.
> 
> Thanks.
> 
> 
> --
> Cheers  --  Tim
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Error message on insert

[Tim Streater]

I want to insert a new row in my table, and while doing so setting a column to 
one more than the maximum value of that column, thus:

   insert into filters (absid, filter_name, enabled, filter_order) values 
(null, 'Untitled filter', 0, max(filter_order)+1)

However I get "Error: no such column: filter_order”. I had a look at the syntax 
diagram for insert which would seem to permit the above.

I can do this in code anyway so it’s not a show stopper, but what have I done 
incorrectly? My IDE appears to use sqlite 3.14.1.

Thanks.


--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unusual but trivially reproducible bug

[Richard Hipp]

On 6/18/17, Robert Cousins  wrote:
> Summary:
>   Leaving out the space after the word 'limit' causes the limit
> clause to be ignored.

Not a syntax error.  Omitting the space makes the parser think that
"limit1" is an alias for the final table name - the equivalent of:

   SELECT * FROM foo AS limit1;

Only, without the (optional) AS keyword.

> I've reproduced it on version 3.19.2 2017-05-25 16:50:27
> edb4e819b0c058c7d74d27ebd14cc5ceb2bad6a6144a486a970182b7afe3f8b9
> A sample output is below.
> On one hand, this is a failure to catch a syntax error. On the other
> hand, the fact that it isn't caught is perhaps problematic and indicates
> an underlying parser issue.
> Thanx
>
> sqlite> create table foo(id Integer not null primary key, bar int);
> sqlite> insert into foo values (NULL,1),(NULL,2),(NULL,3);
> sqlite> select * from foo limit1;
> id  bar
> --  --
> 1   1
> 2   2
> 3   3
> sqlite> select * from foo limit2;
> id  bar
> --  --
> 1   1
> 2   2
> 3   3
> sqlite> select * from foo limit 1;
> id  bar
> --  --
> 1   1
> sqlite> select * from foo limit 2;
> id  bar
> --  --
> 1   1
> 2   2
> sqlite>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to search for fields with accents in UTF-8 data?

[Gilles]

Thanks everyone.

It looks running sqlite3.exe in a terminal window (CMD) in Windows 7 
doesn't work: Apparently, it doesn't support UTF-8.


And when using DB Browser for SQLite, it does work only if I copy/paste 
the output with the "?" where an accented character lives:

https://s15.postimg.org/e05v2q09n/SQLite.UTF8.accents.query.DB.Browser.for.SQLite.png

Could the problem be with fonts not supporting UTF8?

I'd rather not mess with Windows encoding, especially since one of the 
answers in SuperUser says that Windows only partially support Unicode.


Here's the output of the commands:

sqlite> SELECT unicode(Libreg) FROM MyTable WHERE Libreg LIKE 
'%le-de-France' LIMIT 1;

65533
sqlite> SELECT char(206), unicode('I');
I;73
sqlite> SELECT hex('Ile-de-France');
496C652D64652D4672616E6365
sqlite> SELECT hex(region) FROM MyTable WHERE LIBREG like 
'%le-de-France' LIMIT 1;

Error: no such column: region
sqlite> SELECT hex(libreg) FROM MyTable WHERE LIBREG like 
'%le-de-France' LIMIT 1;

CE6C652D64652D4672616E6365

PS: I might be breaking the thread in the mailing list. For some reason, 
the SQLite mailing lists refuses my post from Nabble although I used the 
same email address to register 1) with Nabble and 2) with the SQLite 
mailing list http://sqlite.1065341.n5.nabble.com/

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unusual but trivially reproducible bug

[Ketil Froyn]

It's not a bug, you're setting up a table alias called "limit2". The
"AS" keyword is optional, but this is the same as:

select * from foo AS limit1;

Ketil

On 18 June 2017 at 21:19, Robert Cousins  wrote:
> Summary:
>   Leaving out the space after the word 'limit' causes the limit
> clause to be ignored.
> I've reproduced it on version 3.19.2 2017-05-25 16:50:27
> edb4e819b0c058c7d74d27ebd14cc5ceb2bad6a6144a486a970182b7afe3f8b9
> A sample output is below.
> On one hand, this is a failure to catch a syntax error. On the other
> hand, the fact that it isn't caught is perhaps problematic and indicates
> an underlying parser issue.
> Thanx
>
> sqlite> create table foo(id Integer not null primary key, bar int);
> sqlite> insert into foo values (NULL,1),(NULL,2),(NULL,3);
> sqlite> select * from foo limit1;
> id  bar
> --  --
> 1   1
> 2   2
> 3   3
> sqlite> select * from foo limit2;
> id  bar
> --  --
> 1   1
> 2   2
> 3   3
> sqlite> select * from foo limit 1;
> id  bar
> --  --
> 1   1
> sqlite> select * from foo limit 2;
> id  bar
> --  --
> 1   1
> 2   2
> sqlite>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
-Ketil
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unusual but trivially reproducible bug

[Scott Robison]

Not a bug. Instead of a keyword, you've defined an alias for the table
named "limit1".

On Jun 19, 2017 4:00 AM, "Robert Cousins"  wrote:

> Summary:
>   Leaving out the space after the word 'limit' causes the limit
> clause to be ignored.
> I've reproduced it on version 3.19.2 2017-05-25 16:50:27
> edb4e819b0c058c7d74d27ebd14cc5ceb2bad6a6144a486a970182b7afe3f8b9
> A sample output is below.
> On one hand, this is a failure to catch a syntax error. On the other
> hand, the fact that it isn't caught is perhaps problematic and indicates
> an underlying parser issue.
> Thanx
>
> sqlite> create table foo(id Integer not null primary key, bar int);
> sqlite> insert into foo values (NULL,1),(NULL,2),(NULL,3);
> sqlite> select * from foo limit1;
> id  bar
> --  --
> 1   1
> 2   2
> 3   3
> sqlite> select * from foo limit2;
> id  bar
> --  --
> 1   1
> 2   2
> 3   3
> sqlite> select * from foo limit 1;
> id  bar
> --  --
> 1   1
> sqlite> select * from foo limit 2;
> id  bar
> --  --
> 1   1
> 2   2
> sqlite>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] unusual but trivially reproducible bug

[Robert Cousins]

Summary:
  Leaving out the space after the word 'limit' causes the limit
clause to be ignored.
I've reproduced it on version 3.19.2 2017-05-25 16:50:27
edb4e819b0c058c7d74d27ebd14cc5ceb2bad6a6144a486a970182b7afe3f8b9
A sample output is below.
On one hand, this is a failure to catch a syntax error. On the other
hand, the fact that it isn't caught is perhaps problematic and indicates
an underlying parser issue.
Thanx

sqlite> create table foo(id Integer not null primary key, bar int);
sqlite> insert into foo values (NULL,1),(NULL,2),(NULL,3);
sqlite> select * from foo limit1;
id  bar  
--  --
1   1
2   2
3   3
sqlite> select * from foo limit2;
id  bar  
--  --
1   1
2   2
3   3
sqlite> select * from foo limit 1;
id  bar  
--  --
1   1
sqlite> select * from foo limit 2;
id  bar  
--  --
1   1
2   2
sqlite>

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using key/value idea to store data & creating normal table VIEW?

[Robert M. Münch]

On 17 Jun 2017, at 19:29, Igor Tandetnik wrote:

> For the first three (or any fixed N) columns, yes. But I thought you wanted a 
> view that somehow automatically becomes wider or narrower as rows are 
> inserted or deleted in the underlying table. I don't believe such a thing is 
> possible.

That's right, the query has to be build fitting the structure.

> So you already know the answer to your question, then? What else would you 
> like assistance with?

Well, the question for me is, which approach will be faster?

A table with (entity, value) and a query like I posted:

select rec_id
, max(case when col_id = "col-1" then value end) col1
, max(case when col_id = "col-2" then value end) col2
, max(case when col_id = "col-3" then value end) col3
from products where col1 = "ABC"
group by rec_id


or using the JSON1 extension and building a query like:

select rec_id
, json_extract(json_value,"$.col1") as col1 from json_products
, json_extract(json_value,"$.col2") as col2 from json_products
from products, json_each(col1)
where json_each.value = "ABC"

The JSON data might be 20.000 records each JSON object/array containing 5 - 50 
fields.

Are there are any benchmarks available?

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_exec statement count including create/drop?

[Clemens Ladisch]

petern wrote:
> Is there a C API way to get a total count or notification as each statement
> is prepared by sqlite_prepare_v2 within sqlite3_exec?

sqlite3_trace_v2() with SQLITE_TRACE_PROFILE is called at the end of each
statement.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to search for fields with accents in UTF-8 data?

[Hick Gunter]

What do the following statements return, when run in sqlite3.exe (Please note 
that single quotes are SQLite3 string delimiters):

SELECT hex('Île-de-France');

SELECT hex(region) FROM MyTable WHERE LIBREG like '%le-de-France' LIMIT 1;

I expect one of them is ISO (lead character > 7F) and the other UTF8 (2 
character sequence), so they can never match.
Alternatively, I have also seen "double conversion" ISO -> UTF8 when the 
encoding was already UTF8 but the conversion ISO -> UTF8 was performed anyway.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von CC
Gesendet: Sonntag, 18. Juni 2017 12:53
An: sqlite-users@mailinglists.sqlite.org
Betreff: [sqlite] How to search for fields with accents in UTF-8 data?

Hello

I imported a CSV file where data are encoded in UTF-8.

Some of the characters (like Î) are not available in the ASCII table, so I 
can't use the CLI sqlite3.exe to search.

As an alternative, I tried SQLite Studio, but it fails:

;Returns no record
SELECT COUNT(*) FROM MyTable WHERE REGION="Île-de-France";

;Returns the expected records
SELECT COUNT(*) FROM MyTable WHERE "LIBREG" LIKE "%le-de-France";

I found nothing in SQLite Studio's menus that could be related to encoding so 
that I could tell it the DB contains UTF-8 instead of ANSI.

Is there another Windows application I could try that is more likely to work 
with UTF-8 data?

Thank you.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to search for fields with accents in UTF-8 data?

[Klaas Van B.]

For some applications it is, for others not in all cases. For "just" accented 
characters it should be no problem following these instructions.
General advice: download OpenOffice or similar OpenSource packages. They are 
completely free and support almost all OS's.

Kind regards | Vriendelijke groeten | Cordiali saluti,
Klaas `Z4us` van Buiten V, Experienced Freelance ICT-Guy
https://www.linkedin.com/in/klaas-van-buiten-0325b2102


On Mon, 19/6/17, Klaas Van B.  wrote:

 Subject: Re: How to search for fields with accents in UTF-8 data?
 To: "SQLite Maillist" 
 Cc: "CC" 
 Date: Monday, 19 June, 2017, 8:41
 
 You can even make UTF-8 the default encoding
 in Windows as it is in SQLite
 
 
https://superuser.com/questions/239810/setting-utf8-as-default-character-encoding-in-windows-7
 
 CC 
 wrote on Sun, 18 Jun 2017 12:52:33 +0200:
 
 >As an alternative, I tried SQLite
 Studio, but it fails:
 
 ;>Returns no record
 >SELECT COUNT(*) FROM MyTable WHERE
 REGION="Île-de-France";
 
 ;>Returns the expected records
 >SELECT COUNT(*) FROM MyTable WHERE
 "LIBREG" LIKE "%le-de-France";
 
 >I found nothing in SQLite Studio's
 menus that could be related to
 >encoding so that I could tell it
 the DB contains UTF-8 instead of ANSI.
 
 >Is there another Windows
 application I could try that is more likely to
 >work with UTF-8 data?
 
 Kind regards | Vriendelijke groeten |
 Cordiali saluti,
 Klaas `Z4us` van Buiten V, Experienced
 Freelance ICT-Guy
 https://www.linkedin.com/in/klaas-van-buiten-0325b2102
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to search for fields with accents in UTF-8 data?

[Klaas Van B.]

You can even make UTF-8 the default encoding in Windows as it is in SQLite

https://superuser.com/questions/239810/setting-utf8-as-default-character-encoding-in-windows-7

CC  wrote on Sun, 18 Jun 2017 12:52:33 +0200:

>As an alternative, I tried SQLite Studio, but it fails:

;>Returns no record
>SELECT COUNT(*) FROM MyTable WHERE REGION="Île-de-France";

;>Returns the expected records
>SELECT COUNT(*) FROM MyTable WHERE "LIBREG" LIKE "%le-de-France";

>I found nothing in SQLite Studio's menus that could be related to
>encoding so that I could tell it the DB contains UTF-8 instead of ANSI.

>Is there another Windows application I could try that is more likely to
>work with UTF-8 data?

Kind regards | Vriendelijke groeten | Cordiali saluti,
Klaas `Z4us` van Buiten V, Experienced Freelance ICT-Guy
https://www.linkedin.com/in/klaas-van-buiten-0325b2102
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users