[sqlite] lsm1 extension

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

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

> 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

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

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

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

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.

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

> 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

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

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

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

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

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

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

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

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

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

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, ... ,

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

> 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

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

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

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

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

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

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

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:

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

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

Re: [sqlite] Error message on insert

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

Re: [sqlite] Error message on insert

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 >

Re: [sqlite] unusual but trivially reproducible bug

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

Re: [sqlite] Error message on insert

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

[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:

Re: [sqlite] unusual but trivially reproducible bug

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:

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

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:

Re: [sqlite] unusual but trivially reproducible bug

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

Re: [sqlite] unusual but trivially reproducible bug

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

[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

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

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.

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

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

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

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

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

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

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

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: