Re: [sqlite] SQLite Release 3.18.0

I am getting the same thing with just plain, PRAGMA optimize; -Original Message- From: David Burgess Sent: Sunday, April 2, 2017 9:56 PM To: SQLite mailing list Subject: Re: [sqlite] SQLite Release 3.18.0 I tried optimize. sqlite> pragma main.optimize; Error: database schema has

Re: [sqlite] SQLite Release 3.18.0

I tried optimize. sqlite> pragma main.optimize; Error: database schema has changed sqlite> pragma temp.optimize; sqlite> Anyone know what the error message means? On Mon, Apr 3, 2017 at 11:42 AM, jose isaias cabrera wrote: > > Thanks, Dr. Hipp for the new release of

Re: [sqlite] 3.18 Problem

My specific problem is solved with - sqlite> PRAGMA ignore_check_constraints = 1; sqlite> PRAGMA integrity_check; sqlite> PRAGMA ignore_check_constraints = 0; An option on integrity_check() to do the same would be nice. On Mon, Apr 3, 2017 at 11:40 AM, Richard Hipp wrote: >

[sqlite] SQLite Release 3.18.0

Thanks, Dr. Hipp for the new release of SQLite v3.18.0. One of bullets of the new Added the PRAGMA optimize command is, (Not yet implemented) Create indexes that might have been helpful to recent queries. May I also suggest that the tool also provides info on useless indexes. Thanks. josé

Re: [sqlite] 3.18 Problem

On 4/2/17, Simon Slavin wrote: > >> On 3 Apr 2017, at 2:11am, David Burgess wrote: >> >> (I guess I should re-check >> to see if that has changed since 3.7) > > Yep. Many speedups in recent versions of SQLite. > But not in triggers :-) -- D. Richard

Re: [sqlite] 3.18 Problem

> On 3 Apr 2017, at 2:11am, David Burgess wrote: > > (I guess I should re-check > to see if that has changed since 3.7) Yep. Many speedups in recent versions of SQLite. > TRIGGER I don’t see anything obvious wrong with your trigger, but I don’t use GLOB often. Maybe

Re: [sqlite] 3.18 Problem

Yes a trigger is right thing to use. I started with a trigger and moved to a check purely for performance. The trouble being that inserts are done in blocks of anywhere up to 10,000. The trigger overhead on the big inserts is significant (I guess I should re-check to see if that has changed since

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

On 3 Apr 2017, at 12:27am, Hamish Allan wrote: > SELECT d FROM x WHERE b = 1 GROUP BY c ORDER BY a; Your problem comes down to this: If you are GROUPing BY c, why do you want ORDER BY a ? If you remove the "ORDER BY a" clause then the ideal index would be on (b, c). But

Re: [sqlite] 3.18 Problem

On 3 Apr 2017, at 12:41am, David Burgess wrote: > Bad trigger performance prevents me using your suggested solution. I’d like to see your TRIGGER. But if a well-written TRIGGER can’t reach the performance you need (it’ll never be as fast as a constraint) then your best

Re: [sqlite] If I got many to many relationship data first, how do I insert them to my table?

对于这样的问题SQLite也没有特别的解决方案。你说的方法几乎就是“标准”的做法，先把customer_id 设置为null, 然后插入该订单，（ 这样就不会引起外部键检查失败）， 当获取该订单准确的customer_id后，再把它更新。 App的逻辑应考虑到这一点，所有customer_id 为null的订单都是不完整的，相当于草稿。 Zhai Sent from Mail for Windows 10 From: 邱朗

Re: [sqlite] 3.18 Problem

Perhaps you could show the trigger you are complaining about? > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of David Burgess > Sent: Sunday, 2 April, 2017 17:41 > To: SQLite mailing list > Subject: Re: [sqlite] 3.18 Problem >

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

What is the purpose of the ORDER BY? The value of a used for the order by is from some random row in the grouping of c. Are there relationships between a, b, c, d that you have not documented nor told us about? In any case, your most efficient index is on (b, c). The order by is

Re: [sqlite] 3.18 Problem

Bad trigger performance prevents me using your suggested solution. Is there a secret to making triggers perform like CHECK? On Mon, Apr 3, 2017 at 1:35 AM, Simon Slavin wrote: > > On 2 Apr 2017, at 2:50pm, David Burgess wrote: > >> I have a table with

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

I had nearly the same question a month ago (Subject: Index usefulness for GROUP BY). In my case, the best index was on the WHERE clause because it eliminated the scan and returned only the few important rows for the other clauses. However, the best result will depend on how many rows are

[sqlite] Indexing WHERE with GROUP BY and ORDER BY

Given a table: CREATE TABLE x (a INT, b INT, c TEXT, d TEXT); the query: SELECT d FROM x WHERE b = 1 GROUP BY c ORDER BY a; shows the following plan, without indexes: 0|0|0|SCAN TABLE x 0|0|0|USE TEMP B-TREE FOR GROUP BY 0|0|0|USE TEMP B-TREE FOR ORDER BY I can create an index to cover the

Re: [sqlite] 3.18 Problem

On 2 Apr 2017, at 2:50pm, David Burgess wrote: > I have a table with 500,000 rows, it has a unique column of registration > numbers > The rules for the format of these numbers has changed over time and the > schema has changed with the rules of the time. PRAGMA

[sqlite] 3.18 Problem

Hi . I am new to this mailing list I have a table with 500,000 rows, it has a unique column of registration numbers The rules for the format of these numbers has changed over time and the schema has changed with the rules of the time. PRAGMA integrity_check now complains about some rows not

Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines

James K. Lowden wrote: > Clemens Ladisch wrote: > >> since commit 68f6dc7af1013f29, newlines in the .dump output are >> escaped with char(). > > Why not use vis(3) instead? Because vis() is a nonstandard function that is not available everywhere, and in any case it does not

Re: [sqlite] Continuous recovery of journal

On Sat, Apr 1, 2017 at 10:22 PM, Jeffrey Mattox wrote: > >> On Apr 1, 2017, at 10:43 PM, J Decker wrote: > > > > I can add an idle sweep to close connections when nothing has been in > progress for a while but only on sqlite connections which complicates >