Re: [sqlite] Can I create a stealth index?

Thanks! I've send an email with the .fullschema to your private email below. Not sure if there is another better address? - Deon -Original Message- From: drhsql...@gmail.com [mailto:drhsql...@gmail.com] On Behalf Of Richard Hipp Sent: Tuesday, January 16, 2018 6:45 AM To: SQLite

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

Thanks Peter, That saved me hours of work. According to the comments - /* Read a single field of CSV text. Compatible with rfc4180 and extended ** with the option of having a separator other than ",". I tried - sqlite> CREATE VIRTUAL TABLE temp.t1 USING csv(filename='test.tsv'); where

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

On 1/16/18, Shane Dev wrote: > I tried - > > sqlite> CREATE VIRTUAL TABLE temp.t1 USING csv(filename='test.tsv'); > > where test.tsv is a tab separated table. However > > select count(*) from t1; > > goes into an infinite loop. Do you how to specify a separator other than >

Re: [sqlite] zipfile.c: crc32 not calculated for uncompressed files - extraction fails

On 01/17/2018 03:51 AM, Ralf Junker wrote: zipfile.c fails to calculate the CRC32 value if the compression method is explicitly set to 0. Example SQL: INSERT INTO zz(name, mode, mtime, data, method) VALUES('f.txt', '-rw-r--r--', 10, 'abcde', 0); As a result, a CRC32 value of 0 is

[sqlite] c program which performs the same function as the SQLite shell command ".import"

Hi, I am looking for an efficient way to write a c program which performs the same function as the SQLite shell command ".import" My initial strategy is to include the sqlite library source files and copy the control block from shell.c that begins after if( c=='i' && strncmp(azArg[0], "import",

Re: [sqlite] Can an SQL script be built from within sqlite?

On Tue, Jan 16, 2018 at 10:51 AM, Paul Sanderson < sandersonforens...@gmail.com> wrote: > That terminal app is still sandboxed. AFAIAA you essentially get access to > the application's data folder and you can add, create, delete, etc files > within it. > Sounds good enough, no? But really, what

Re: [sqlite] Can an SQL script be built from within sqlite?

That terminal app is still sandboxed. AFAIAA you essentially get access to the applictaios data folder and you can add, create, delete, etc files within it. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786

Re: [sqlite] Can an SQL script be built from within sqlite?

I've not tried it, but this article from OSXdaily says you can get the command line (Terminal) in iOS. http://osxdaily.com/2018/01/08/get-terminal-app-ios-command-line/ That probably does not solve the fork requirement, and I'm sure it is sandboxed. John G On 15 January 2018 at 15:00,

Re: [sqlite] Crash when querying a unique index containing 12 columns using sub-selects

Hello again, Thanks for the responses, here is some additional information. > Is it hanging, or is it crashing? Your statement of the problem is unclear > on this point. It depends on the version. 3.8.0 gives an access violation (a crash) the other versions I have tried hang, in that the call

Re: [sqlite] Infinite loop when updating indexed with case and where clause

> On 1/16/18, Don V Nielsen wrote: > > Off topic question regarding fix: "0!=(wctrlFlags & WHERE_ONEPASS_MULTIROW)" > > > > Is there a performance bonus or compiler optimization if one compares a > > target constant to a source condition versus comparing a target condition

Re: [sqlite] Crash when querying a unique index containing 12 columns using sub-selects

On Tue, Jan 16, 2018 at 4:10 PM, Richard Hipp wrote: > On 1/16/18, Matthew Towler wrote: > > > > Firstly, here is a C++11 example application. > > Does not compile. These are the errors: > > x2.cpp:53:2: warning: missing terminating " character > R"(

Re: [sqlite] Crash when querying a unique index containing 12 columns using sub-selects

On 1/16/18, Dominique Devienne wrote: > You're not compiling in C++11 mode, are you? > Probably use -std=c++11 or perhaps -std=gnu++11 on your g++ command line. Did that. It compiles now. But it also just works. There is no slowdown. Everything is very fast, regardless

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

On 1/16/18, 10:29 AM, "sqlite-users on behalf of petern" wrote: > https://sqlite.org/csv.html BTW typo on that page: “The example above showed a single filename='th3file.csv' argument for the CSV

Re: [sqlite] About sql parametrized queries

On 1/16/18, Enrique Mesa wrote: > I am writing this message because i need a bit of help performing this kind > of query in sqlite engine. My wish to run the following SQL statement: > "SELECT * FROM users WHERE username = ? ;"; Suggestion you number your parameters

Re: [sqlite] Infinite loop when updating indexed with case and where clause

On 1/16/18, Harald Klimach wrote: > > here is a weird behavior I observe with the following kind of setup: > Bug fix is in. You can download the latest snapshot from https://sqlite.org/download.html. Alternatively, you can apply the patch at

Re: [sqlite] Infinite loop when updating indexed with case and where clause

Wow, > Bug fix is in. You can download the latest snapshot from > https://sqlite.org/download.html. > > Alternatively, you can apply the patch at > https://www.sqlite.org/src/info/feb2c2b6f66b0f45 to whatever prior > version of SQLite that you happen to be using. thanks a lot for this quick

Re: [sqlite] Can an SQL script be built from within sqlite?

On 1/16/18, Paul Sanderson wrote: > That terminal app is still sandboxed. AFAIAA you essentially get access to > the applictaios data folder and you can add, create, delete, etc files > within it. Right. And so it is apparently not possible to compile a

Re: [sqlite] Can I create a stealth index?

On Tue, Jan 16, 2018 at 3:38 PM, Deon Brewis wrote: > I have seen a few cases where a newly added index would start showing up > uninvited in old, previously tested queries and bring performance down by > an order of magnitude. ('analyze' doesn't fix it). > That seems quite

Re: [sqlite] Crash when querying a unique index containing 12 columns using sub-selects

On 1/16/18, Matthew Towler wrote: > > Firstly, here is a C++11 example application. Does not compile. These are the errors: x2.cpp:53:2: warning: missing terminating " character R"(SELECT AbundanceId FROM[ABUNDANCE] ^ x2.cpp:53:1: error: missing terminating "

Re: [sqlite] Can I create a stealth index?

On 1/16/18, Deon Brewis wrote: > > I have seen a few cases where a newly added index would start showing up > uninvited in old, previously tested queries and bring performance down by an > order of magnitude. ('analyze' doesn't fix it). We would welcome the opportunity to try

Re: [sqlite] Can an SQL script be built from within sqlite?

On 16 Jan 2018, at 2:25pm, Richard Hipp wrote: > On 1/16/18, Paul Sanderson wrote: > >> That terminal app is still sandboxed. AFAIAA you essentially get access to >> the applictaios data folder and you can add, create, delete, etc files >> within

[sqlite] About sql parametrized queries

I am writing this message because i need a bit of help performing this kind of query in sqlite engine. My wish to run the following SQL statement: "SELECT * FROM users WHERE username = ? ;"; I am not using wrappers. I am using just plain SQLite C Library from my program wirtten in C++. I don't

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

FYI. csv.c is already a separate C program which imports CSV files without necessity of the SQLite shell: https://sqlite.org/csv.html On Tue, Jan 16, 2018 at 12:47 AM, Shane Dev wrote: > Hi, > > I am looking for an efficient way to write a c program which performs the >

[sqlite] zipfile.c: crc32 not calculated for uncompressed files - extraction fails

zipfile.c fails to calculate the CRC32 value if the compression method is explicitly set to 0. Example SQL: INSERT INTO zz(name, mode, mtime, data, method) VALUES('f.txt', '-rw-r--r--', 10, 'abcde', 0); As a result, a CRC32 value of 0 is written to the file. Some archive managers

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

Apparently the CSV virtual table supports neither changes (INSERT, UPDATE, DELETE), nor reading single column csv files. What I really want is the functionality of .import and .output SQLite shell commands. Maybe a better strategy would be to compile shell.c with my c program and call the

Re: [sqlite] Speed issue of SELECT in my application

On 17 Jan 2018, at 3:52am, Nick wrote: > b INTEGER NOT NULL UNIQUE, […] > UNIQUE(b, i) The second constraint is redundant. If values of b are unique, then so is anything that includes values of b. > And I’ve got some speed issues when I query the db:

Re: [sqlite] Speed issue of SELECT in my application

Vague. Some thoughts: How long is the text? A million? A billion? If a million, does SQLite take what you consider a long time to receive/display results from a TEXT row? SELECT printf('%100s'); --... Run Time: real 0.854 user 0.016000 sys 0.008000 --vs: INSERT INTO t1(e) SELECT

[sqlite] Speed issue of SELECT in my application

I have a table below in my application: CREATE TABLE t1 ( a INTEGER PRIMARY KEY AUTOINCREMENT, b INTEGER NOT NULL UNIQUE, c INTEGER NOT NULL, d INTEGER, e TEXT, f INTEGER, g INTEGER, h TEXT, i INTEGER,

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

Shane. Expect to do a lot of hacking on shell.c. It's not intended as a library but as the main program of a console application. Another way involves controlling the IO handles of your process and sending strings but that will probably run into portability problems that are even a bigger

Re: [sqlite] Speed issue of SELECT in my application

Thank you Simon. As you said, UNIQUE(b, i) is redundant, but I guess it will not affect the performance of the SELECT. I find "SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b>?)" when I use EXPLAIN QUERY PLAN, so I do not need to add any index, right? Um, I guess I have nothing to do to

Re: [sqlite] Speed issue of SELECT in my application

On 17 Jan 2018, at 6:48am, Nick wrote: > As you said, UNIQUE(b, i) is redundant, but I guess it will not affect the > performance of the SELECT. Correct. It’ll make the database file bigger, and it’ll slightly slow down INSERT/UPDATE/DELETE but have only a trivial

Re: [sqlite] Speed issue of SELECT in my application

OK. Thank you for your help. -- Sent from: 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] Infinite loop when updating indexed with case and where clause

Off topic question regarding fix: "0!=(wctrlFlags & WHERE_ONEPASS_MULTIROW)" Is there a performance bonus or compiler optimization if one compares a target constant to a source condition versus comparing a target condition to a source constant, as in "(wctrlFlags & WHERE_ONEPASS_MULTIROW)!=0"?

Re: [sqlite] Infinite loop when updating indexed with case and where clause

On 1/16/18, Don V Nielsen wrote: > Off topic question regarding fix: "0!=(wctrlFlags & WHERE_ONEPASS_MULTIROW)" > > Is there a performance bonus or compiler optimization if one compares a > target constant to a source condition versus comparing a target condition > to a

Re: [sqlite] Infinite loop when updating indexed with case and where clause

On 1/16/18, 8:12 AM, "sqlite-users on behalf of Don V Nielsen" wrote: > Off topic question regarding fix: "0!=(wctrlFlags & WHERE_ONEPASS_MULTIROW)" > Is there a performance bonus or compiler optimization if one

Re: [sqlite] Infinite loop when updating indexed with case and where clause

Thanks for the bug report! A ticket for this issue is here: https://www.sqlite.org/src/tktview/47b2581aa9bfececa7d95b2ef2aa433418c7a583 I will post another message as soon as we have it fixed. On 1/16/18, Harald Klimach wrote: > Hi there, > > here is a weird behavior I

[sqlite] Can I create a stealth index?

Can I create an index in SQLITE that is only ever used in an 'indexed by' clause and not automatically picked up the query optimizer? I have seen a few cases where a newly added index would start showing up uninvited in old, previously tested queries and bring performance down by an order of

[sqlite] Infinite loop when updating indexed with case and where clause

Hi there, here is a weird behavior I observe with the following kind of setup: BEGIN TRANSACTION; CREATE TABLE simple("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "lft" INTEGER, "rgt" INTEGER); INSERT INTO simple VALUES(1,78,79); CREATE INDEX "index_on_lft" ON "simple" ("lft"); CREATE INDEX