Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?

On 9 Dec 2014, at 1:36am, David Barrett wrote: > *Re: Why VACUUM.* We vacuum weekly. This particular database is a > "rolling journal" -- we are constantly adding new rows to the end of the > table, and every week we truncate off the head of the journal to only keep >

Re: [sqlite] How do I update multiple rows in a single sql statement

On 12/8/2014 8:43 PM, Keith Medcalf wrote: That is not a problem -- and I only fixed the where clause, not the set clause. The data will be "hot" on the second access, so the overhead of the additional access is negligible since CPU usage is negligible (even if it goes through all the motions

Re: [sqlite] How do I update multiple rows in a single sql statement

That is not a problem -- and I only fixed the where clause, not the set clause. The data will be "hot" on the second access, so the overhead of the additional access is negligible since CPU usage is negligible (even if it goes through all the motions of cold access) compared to I/O usage --

Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?

Hi all, great questions: *Re: Why VACUUM.* We vacuum weekly. This particular database is a "rolling journal" -- we are constantly adding new rows to the end of the table, and every week we truncate off the head of the journal to only keep 3M rows at the "tail". Given that we're truncating the

Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?

Wanting is not needing. If a highly I/O bound process interferes with the I/O performed by other (not I/O bound) processes, then the OS is broken and the proper solution is to get a better O/S. These sorts of problems were solved back in the 60's (okay, maybe 70's). Therefore, unless a

Re: [sqlite] How do I update multiple rows in a single sql statement

On 12/8/2014 8:20 PM, Keith Medcalf wrote: update temp_table set id=(select id from some_table where c=42), operation='UPDATE' where exists (select 1 from some_table s where s.a=temp_table.a and s.b=temp_table.b and s.c=42); is the proper way of phrasing of a correlated subquery

Re: [sqlite] How do I update multiple rows in a single sql statement

update temp_table set id=(select id from some_table where c=42), operation='UPDATE' where exists (select 1 from some_table s where s.a=temp_table.a and s.b=temp_table.b and s.c=42); is the proper way of phrasing of a correlated subquery ... --- Theory is when you know everything but

Re: [sqlite] Table names length and content

You get a palladium star for avoiding the most obvious source of errors (and wasted keystrokes) ... --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows

Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/08/2014 01:35 PM, Max Vlasov wrote: > I wonder whether I/O "sleeping" possible in the first place. In this particular case the OP wants to vacuum while the machine is doing other I/O activity unrelated to the vacuum. Having more sleeping

Re: [sqlite] [SQLite]Ba​sic query

Thank you everyone! I'll try to solve this case with your replies! Thanks, 2014年12月9日火曜日、Richard Hippさんは書きました: > On Mon, Dec 8, 2014 at 12:20 PM, Scott Robison > > wrote: > > > On Dec 8, 2014 2:10 AM, "Shinichiro Yoshioka"

Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?

On Mon, Dec 8, 2014 at 5:56 PM, Roger Binns wrote: > On 12/07/2014 04:43 PM, David Barrett wrote: >> so I'm curious if you can think of a way using the API (or any >> other way) to essentially "nice" the process by inserting a short >> "sleep" into whatever loop runs inside

Re: [sqlite] How do I update multiple rows in a single sql statement

On 12/8/2014 3:35 PM, Luuk wrote: update temp_table set id=(select id from some_table where c=42), operation='UPDATE' where exists (select 1 from some_table s, temp_table t where s.a=t.a and s.b=t.b and s.c=42); Same thing. Your WHERE clause doesn't depend on the values in the

Re: [sqlite] How do I update multiple rows in a single sql statement

On 8-12-2014 21:17, Igor Tandetnik wrote: On 12/8/2014 3:08 PM, Luuk wrote: i hope this does it: update temp_table set id=(select id from some_table where c=42), operation='UPDATE' where exists (select 1 from some_table s, temp_table t where s.a=t.a and s.b=t.b); This updates all

Re: [sqlite] How do I update multiple rows in a single sql statement

The visibility of table created in with clause is only in the SET part. The following is a valid sql statement in sqlite3. with ds as (select id, a , b, c from some_table where c = 43) update temp_table set id = (select ds.id from ds where ds.a = temp_table.a AND ds.b = temp_table.b),

Re: [sqlite] Table names length and content

"Richard Hipp" wrote... On Mon, Dec 8, 2014 at 1:36 PM, Roger Binns wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/08/2014 10:30 AM, jose isaias cabrera wrote: >>> Hmmm... what I am looking for it is not there. If the "string >>> length" defined there is

Re: [sqlite] How do I update multiple rows in a single sql statement

On 12/8/2014 3:08 PM, Luuk wrote: i hope this does it: update temp_table set id=(select id from some_table where c=42), operation='UPDATE' where exists (select 1 from some_table s, temp_table t where s.a=t.a and s.b=t.b); This updates all rows in temp_table, as long as at least one

Re: [sqlite] How do I update multiple rows in a single sql statement

On 8-12-2014 20:50, Venkat Murty wrote: How do I update multiple rows in a single sql statement. Two tables: create table some_table(id, a, b, c); create table temp_table (id, operation, a, b, c); Operation: Updating id, operation fields in temp_table if the record exists in some_table.

[sqlite] How do I update multiple rows in a single sql statement

How do I update multiple rows in a single sql statement. Two tables: create table some_table(id, a, b, c); create table temp_table (id, operation, a, b, c); Operation: Updating id, operation fields in temp_table if the record exists in some_table. with ds as (select id, a , b, c from

Re: [sqlite] Table names length and content

That's a relief. I thought I was doing something wrong. Thanks, -- Bill Drago Senior Engineer L3 Communications / Narda Microwave East 435 Moreland Road Hauppauge, NY 11788 631-272-5947 / william.dr...@l-3com.com > -Original Message- > From: sqlite-users-boun...@sqlite.org

Re: [sqlite] Table names length and content

On Mon, Dec 8, 2014 at 1:36 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 12/08/2014 10:30 AM, jose isaias cabrera wrote: > >>> Hmmm... what I am looking for it is not there. If the "string > >>> length" defined there is what defines the

Re: [sqlite] Table names length and content

On Mon, Dec 8, 2014 at 7:37 PM, Drago, William @ CSG - NARDAEAST < william.dr...@l-3com.com> wrote: > Uh oh! I never use quotes when I create my tables. What are the > implications of not quoting? > If you restrict yourself to C-style identifiers, the primary implication is better ease of use

Re: [sqlite] Table names length and content

On Mon, Dec 8, 2014 at 7:36 PM, Roger Binns wrote: > SQLite supports all those, as well as zero length table names, column > types and names. > > > sqlite> create table " <>!@#$%^&*()_+=-{}\|[]'"";:?/.,, etc., etc. in > the table name"("" ""); > To whichl add: sqlite

Re: [sqlite] Table names length and content

> -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Richard Hipp > Sent: Monday, December 08, 2014 1:28 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Table names length and content > > On Mon, Dec 8,

Re: [sqlite] Table names length and content

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/08/2014 10:30 AM, jose isaias cabrera wrote: >>> Hmmm... what I am looking for it is not there. If the "string >>> length" defined there is what defines the length of the name of >>> a table, I am in business. :-) However, there is nothing

Re: [sqlite] Table names length and content

"Richard Hipp" wrote... On Mon, Dec 8, 2014 at 1:22 PM, jose isaias cabrera

Re: [sqlite] Table names length and content

On Mon, Dec 8, 2014 at 1:22 PM, jose isaias cabrera wrote: > > "Stephan Beal" wrote... > > On Mon, Dec 8, 2014 at 6:32 PM, jose isaias cabrera < >> jic...@cinops.xerox.com >> >>> wrote: >>> >> >> When creating table names, are there any constraints that one must look

Re: [sqlite] Table names length and content

"Stephan Beal" wrote... On Mon, Dec 8, 2014 at 6:32 PM, jose isaias cabrera

Re: [sqlite] Table names length and content

On Mon, Dec 8, 2014 at 6:32 PM, jose isaias cabrera wrote: > When creating table names, are there any constraints that one must look > for? ie. Length? Characters? etc.? I quickly browse through this spot, > > https://www.sqlite.org/lang_createtable.html

[sqlite] Table names length and content

Greetings! When creating table names, are there any constraints that one must look for? ie. Length? Characters? etc.? I quickly browse through this spot, https://www.sqlite.org/lang_createtable.html but could not find any information regarding this. Would someone please bring light for

Re: [sqlite] [SQLite]Ba​sic query

On Mon, Dec 8, 2014 at 12:20 PM, Scott Robison wrote: > On Dec 8, 2014 2:10 AM, "Shinichiro Yoshioka" > wrote: > > > > Hi, > > > > I'm about to use sqlite-amalgamation(sqlite3.c) on Visual C++. > > But although the compiling was successfully

Re: [sqlite] [SQLite]Ba​sic query

On Dec 8, 2014 2:10 AM, "Shinichiro Yoshioka" wrote: > > Hi, > > I'm about to use sqlite-amalgamation(sqlite3.c) on Visual C++. > But although the compiling was successfully finished, even if I set break > point > on the source code, I can't trace the working line in

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On Mon, Dec 8, 2014 at 9:01 AM, Dan Kennedy wrote: > You could hack SQLite to do enforce unique constraints the same way as FKs. > When adding an entry to a UNIQUE index b-tree, you check for a duplicate. If > one exists, increment a counter. Do the opposite when removing

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

> Le 8 déc. 2014 à 17:21, Simon Slavin a écrit : > >> Why not an opt-in way to ask for deferred constraint checking. The key here >> is only to allow perfectly legit requests to run. With all the due respect >> to sqlite implementors and the wonderful design of sqlite. >

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On 8 Dec 2014, at 3:05pm, Gwendal Roué wrote: > Why not an opt-in way to ask for deferred constraint checking. The key here > is only to allow perfectly legit requests to run. With all the due respect to > sqlite implementors and the wonderful design of sqlite. SQL-99

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

Yes, that would be nice. For example, sqlite already needs explicit opt-in for some of the relational toolkit. I think about "PRAGMA foreign_keys = ON". Why not an opt-in way to ask for deferred constraint checking. The key here is only to allow perfectly legit requests to run. With all the

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On 12/08/2014 09:55 PM, Nico Williams wrote: Ideally there would be something like DEFERRED foreign key checking for uniqueness constraints... You could hack SQLite to do enforce unique constraints the same way as FKs. When adding an entry to a UNIQUE index b-tree, you check for a duplicate.

Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/07/2014 04:43 PM, David Barrett wrote: > so I'm curious if you can think of a way using the API (or any > other way) to essentially "nice" the process by inserting a short > "sleep" into whatever loop runs inside the VACUUM command. Using OS

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

Ideally there would be something like DEFERRED foreign key checking for uniqueness constraints... You can get something like that by using non-unique indexes (but there would also go your primary keys) and then check that there are no duplicates before you COMMIT. (Doing this reliably would

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On Mon, Dec 8, 2014 at 8:23 AM, Marc L. Allen wrote: > I'm not sure I'd even consider it broken. > ​Well, to some on that forum: "If it doesn't work the way that _I_ want, then it is ipso-facto broken". And I forgot the in my message. Sorry.​ > > SQLite is

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On 8-12-2014 14:58, Gwendal Roué wrote: Le 8 déc. 2014 à 14:48, RSmith a écrit : On 2014/12/08 11:55, Gwendal Roué wrote: Hi, Unique indexes make some valid update queries fail. Please find below the SQL queries that lead to the unexpected error: -- The `books` and

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

Doesn't that code risk being broken in a later version that doesn't update in the order provided by the sub-query? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of J T Sent: Monday, December 08, 2014 9:23 AM To:

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

> Le 8 déc. 2014 à 15:18, John McKown a écrit : > > On Mon, Dec 8, 2014 at 8:15 AM, Marc L. Allen > wrote: > >> I am like you, Gwendal, in that I don't like that behavior in SQLite; >> however, not liking it doesn't make it a bug. >>

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

Cancel that, apparently that only updates the last record... -Original Message- From: John McKown To: General Discussion of SQLite Database Sent: Mon, Dec 8, 2014 9:18 am Subject: Re: [sqlite] sqlite bugreport : unique index

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

I'm not sure I'd even consider it broken. SQLite is wonderful. Simply wonderful. Code size and amount of features forced into it impresses me no end. But, it was never intended to run with the big dogs. The fact that, quite often, it can is a tribute to the people that work on it. When

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

Might have another work around. update page set position=position + 1 where designation=(select designation from page where book='1' order by position desc) and then insert your page. Please see if that'll work. I tested it, but your results may differ. -Original Message-

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On Mon, Dec 8, 2014 at 8:15 AM, Marc L. Allen wrote: > I am like you, Gwendal, in that I don't like that behavior in SQLite; > however, not liking it doesn't make it a bug. > ​On another of my forums, this is called a BAD - Broken, As Designed.​ As opposed to the

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

I am like you, Gwendal, in that I don't like that behavior in SQLite; however, not liking it doesn't make it a bug. The constraint-checking algorithm was defined to work exactly the way it's working. When designed, the fact that your type of insert would fail was known and understood. Hence,

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On 2014/12/08 15:58, Gwendal Roué wrote: I'm new to this mailing list, and I won't try to push my opinion, which is : yes this is a bug, and this bug could be fixed without introducing any regression (since fixing it would cause failing code to suddenly run, and this has never been a

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

> Le 8 déc. 2014 à 14:48, RSmith a écrit : > > > On 2014/12/08 11:55, Gwendal Roué wrote: >> Hi, >> >> Unique indexes make some valid update queries fail. >> >> Please find below the SQL queries that lead to the unexpected error: >> >> -- The `books` and `pages` tables

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On 2014/12/08 11:55, Gwendal Roué wrote: Hi, Unique indexes make some valid update queries fail. Please find below the SQL queries that lead to the unexpected error: -- The `books` and `pages` tables implement a book with several pages. -- Page ordering is implemented via the `position`

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

Sorry, wasn't focused on what I was looking at. Though, you said you already tried the order by without success which would have been my next suggestion or clarification of my first. As, you should be able to update the rows from the end down to the page that would be after your insertion

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

> Le 8 déc. 2014 à 14:39, Simon Slavin a écrit : > > On 8 Dec 2014, at 1:31pm, Gwendal Roué wrote: > >> We share the same conclusion. I even tried to decorate the update query with >> "ORDER" clauses, in a foolish attempt to reverse the ordering of row

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

J T, I did provide a sequence of queries that reliably reproduce the issue (see below, from the first CREATE to the last UPDATE). There is no trigger involved, as far as I know. Forgive me but I don't see how I could use your advice. My work around has been to destroy the unique index, and

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On 8 Dec 2014, at 1:31pm, Gwendal Roué wrote: > We share the same conclusion. I even tried to decorate the update query with > "ORDER" clauses, in a foolish attempt to reverse the ordering of row updates, > and circumvent the issue. A way to solve this is to use REAL for

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

Thanks J T. Let's give a look. > Le 8 déc. 2014 à 14:24, J T a écrit : > > Try having your cascade occur before the row is created, updated or deleted. > > http://www.sqlite.org/lang_createtrigger.html > > > > > > > > -Original Message- > From: Richard Hipp

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

> Le 8 déc. 2014 à 14:14, Richard Hipp a écrit : > > On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué wrote: > >> Hi, >> >> Unique indexes make some valid update queries fail. >> >> Please find below the SQL queries that lead to the unexpected error: >> >> --

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

Try having your cascade occur before the row is created, updated or deleted. http://www.sqlite.org/lang_createtrigger.html -Original Message- From: Richard Hipp To: General Discussion of SQLite Database Sent: Mon, Dec 8, 2014 8:14 am

Re: [sqlite] How to Verify (know that they are correct) Queries in SQLite GUI Manager Firefox add-on?

The questions you have to ask is Are the rows I'm returning identifiable by a unique id -- typically the row id, but also unique identifiers, like ISBN for books, Employee ID for employees, etc. If you find duplicates of what should be a unique id in a table then its probably a sign the data

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué wrote: > Hi, > > Unique indexes make some valid update queries fail. > > Please find below the SQL queries that lead to the unexpected error: > > -- The `books` and `pages` tables implement a book with several pages. > -- Page

[sqlite] sqlite bugreport : unique index causes valid updates to fail

Hi, Unique indexes make some valid update queries fail. Please find below the SQL queries that lead to the unexpected error: -- The `books` and `pages` tables implement a book with several pages. -- Page ordering is implemented via the `position` column in the pages table. -- A unique index

Re: [sqlite] [SQLite]Ba​sic query

Hi Shinichiro, If opening the database failed with sqlite3_open() != SQLITE_OK, it is probably best to check the return code and error message using sqlite3_errmsg(). It will give a strong hint. Debugging into the sqlite3 code itself never worked for me. Make sure that - the directory the

Re: [sqlite] [SQLite]Ba​sic query

Hi, Clemens and Martin, Thank you for your prompt responses. >How exactly are you using SQLite in your program? If you are not using >the SQLite C API (sqlite3_* functions) directly, I'm using sqlite APIs for calling from C source code directly. >which Version of VC++ do you use? I'm using

Re: [sqlite] [SQLite]Ba​sic query

Hi Shinichiro, which Version of VC++ do you use? As far as I know, older versions do not support debugging source files with more than 65535 lines. Also, why do you want to debug into the sqlite.c file? The file sqlite.c is just another source file for your compiler; optimizations would

Re: [sqlite] [SQLite]Ba​sic query

Shinichiro Yoshioka wrote: > I'm about to use sqlite-amalgamation(sqlite3.c) on Visual C++. > But although the compiling was successfully finished, even if I set break > point > on the source code, I can't trace the working line in sqlite3.c correctly. How exactly are you using SQLite in your

[sqlite] [SQLite]Ba​sic query

Hi, I'm about to use sqlite-amalgamation(sqlite3.c) on Visual C++. But although the compiling was successfully finished, even if I set break point on the source code, I can't trace the working line in sqlite3.c correctly. I suspected that the sqlite3.c was optimized, but there is no opiton like