[sqlite] Possible index corruption
I am using sqlite to implement document file format in my program. I got a document file (sqlite database) from user where the image embedded into the document note is displayed OK, but disappears on the next load of the same document (even if no user editing action was done). The only database modification were alter table statements to adapt this document to a newer version of the file format (new tables, some new fields). No sqlite error was logged during this procedure, but the resulting database seems to have an invalid index. Running this query on the database fails to find the image entry with the matching ID: SELECT * FROM images WHERE note_id="2O7Ej89J3K8Fax5OedEiQQ" but this query works OK (lists the matching item): SELECT * FROM images WHERE note_id LIKE "2O7Ej89J3K8Fax5OedEiQQ" Corrupted database file can be downloaded from here: http://notecasepro.com/temp/new.zip Original database (non-corrupted, before altering) is available here: http://notecasepro.com/temp/old.zip Note that I had to delete other tables to protect customer info. If I "VACUUM" the database, bug is gone (but only when done on this minimal sample, not when done on entire document file). Current pre-release version of the program that corrupted the database uses sqlite v3.13.0, compiled with these flags: SQLITE_ENABLE_COLUMN_METADATA SQLITE_OMIT_AUTHORIZATION SQLITE_OMIT_CAST SQLITE_OMIT_DEPRECATED SQLITE_OMIT_EXPLAIN SQLITE_OMIT_UTF16 SQLITE_OMIT_PROGRESS_CALLBACK SQLITE_OMIT_LOAD_EXTENSION SQLITE_OMIT_SHARED_CACHE SQLITE_OMIT_COMPLETE SQLITE_OMIT_BUILTIN_TEST SQLITE_OMIT_CHECK SQLITE_OMIT_COMPOUND_SELECT SQLITE_OMIT_CONFLICT_CLAUSE SQLITE_OMIT_INCRBLOB SQLITE_OMIT_MEMORYDB SQLITE_OMIT_TEMPDB SQLITE_OMIT_TCL_VARIABLE SQLITE_OMIT_TRACE SQLITE_OMIT_BLOB_LITERAL SQLITE_CORE Note that the old (uncorrupted) document was probably created in one of previous versions of sqlite. Issue can be reproduced on Windows and Mac (probably other platforms as well, did not test). Is this enough info to verify/solve the issue? If needed, I can try to persuade the customer to give access to the full document file to someone outside testing this, outside the mailing list (directly to email) or gather any additional data. Regards, Miroslav ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: SQLite's include guards are reserved identifiers
On Fri, Jul 8, 2016 at 6:56 PM, Daniel Seither wrote: > Using clang 3.8 with -Wreserved-id-macro (enabled by -Weverything), I > just noticed that SQLite uses include guards with a leading underscore, > for example _SQLITE3_H_ in the amalgamation. According to the C > standard, this is a reserved identifier, leading to undefined behavior: While uncommon, it is not unheard of for apps to use include guards to check for a library's existence. While it is indeed not in compliance, in practice fixing this may affect existing SQLite users. -- Cory Nelson ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: SQLite's include guards are reserved identifiers
On 7/12/16 12:58 AM, dandl wrote: Try to write a fully standard compliant standard library without using any of the reserved namespace!!! This is the key point that has been missed so far. The C/C++ standards do not provide a mechanism by which the supplier of a library can reserve or sequester some range of identifiers, for both historic and possible future use, and at the same time be guaranteed to conflict with neither the provider of an implementation nor the user of the library. Therefore a choice has to be made. The fact that the ISO committee didn't provide/mandate a system doesn't mean that some system can't be adopted, outside the standard. The C standard is designed to be supplemented by auxiliary standards, and enforcing such a rule would be outside the scope the standard wanted to take (and when the standard first was developed, domains weren't as expected). Intruding on the implementation space in violation of the standard is not the best solution to the problem. The safest solution (and one widely adopted) is to choose a prefix of sufficient length and starting with a letter. It should be one that is highly likely to be unique to the enterprise and then be applied to all visible identifiers. The ownership of that prefix could be backed up by ownership of a matching domain, trademark, registered company name, etc. [The Java domain thing is just plain silly, and widely breached.] Yes, using a domain you own is a good ad-hock unique prefix. If you do something like that, at least when you get a conflict, you can point to the other party and ask them why they are using something they don't own (There might be some codes gotten this way that might have other possible meanings). Trademarks and registered names may not be the best as often the reservation is somewhat limited in scope, and may be confused with an ordinary word (like Coke). In fact Sqlite has done an excellent job of exactly that, with one exception: they chose to add an unnecessary underscore before the guard prefix and thereby became non-compliant. It should simply be removed (or have been removed -- perhaps it's now too late). -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Lemon doesn't reset `yyerrcnt` after error
On 7/9/16, Nick Wellnhofer wrote: > > This still doesn't work for me. OK. Another fix. Please try the latest trunk version. Note to passive readers of this thread: none of this has any impact on SQLite. SQLite does not use the feature of the Lemon LALR(1) parser generator that Nick is fixing. -- 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] Cyclic detection in recursive queries
On 12/07/2016 13:59, New, Cecil (GE Aviation, US) wrote: The best I have been able to come with is documented at: http://stackoverflow.com/questions/32640043/cannot-detect-cyclic-data-in-an-sqlite-database/32719216#32719216 But a) it is ugly, b) performance impact of all the length(), replace() functions, c) if values end in similar strings, it probably won't work. After some thought, I think the minimum that would solve this problem is to enhance the instr() function to either take a starting position to begin the search or to take an occurrence number to search for. Oracle's version of instr() does both of these (see https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_1103.htm) Postgresql has a specific way of detecting loops, which would be even more robust. It is documented here: https://www.postgresql.org/docs/9.1/static/queries-with.html Three suggestions to solve this problem are described in Section 24.4 of the following document: https://staff.info.unamur.be/dbm/Documents/Tutorials/SQLfast/SQLfast-Tuto24-Recursive-programming.pdf Recursive triggers can be used as well (see Section 24.7). J-L Hainaut ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert or replace on PK and UNIQUE INDEX
On 2016/07/12 3:55 PM, R Smith wrote: On 2016/07/12 2:12 PM, Dominique Devienne wrote: In the session above, we can see that an insert or replace w/o an id (the PK) value, results in the id changing in the table, which is not what we'd like. etc. By the way, another important problem with this is maintaining foreign keys linked to ID fields. Best not to do it, or if you do, ALWAYS specify the new ID yourself, never rely on the DB engine's feelings of what the ID should be. (As I've mentioned many times before). :) Cheers, Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert or replace on PK and UNIQUE INDEX
On 2016/07/12 2:12 PM, Dominique Devienne wrote: In the session above, we can see that an insert or replace w/o an id (the PK) value, results in the id changing in the table, which is not what we'd like. If you ask any DB engine to INSERT, and you do not specify the autoinc/pk/identity ID, then a new ID will always be used. By specifying "OR REPLACE" in SQLite it /replaces/ the offending row by deleting it and inserting the new version of the row as you asked (it never overwrites /some/ values in the row) - however, if you fail to specify the ID for the row, then any newly inserted row (like any other inserted row) MUST get a new row ID. The (incorrect on our part) thinking was that the UNIQUE index would also be used to recognize that the row already existed, and the id to remain the same (In reality in this case the natural key checked by the UNIQUE index is passed in unchanged to the insert or replace, and it's other columns that change), but apparently the autoincrement is first applied, and then the UNIQUE index is apparently still used, resulting in this unwanted ID change. The best way to think about this or remember it, is that SQLite (or any DB engine I think) will never adjust the data within a row unless asked to UPDATE. When you ask it to INSERT or REPLACE or any other such a thing, an entire row will always be affected and handled like a new row should you omit any fields. (This is usually documented and expected, even by the standard). You kind-of expected the INSERT OR REPLACE to be more of an INSERT OR UPDATE (which is the commonly-referred to UPSERT operation) and not really supported natively by many - unless you count MERGE (which does quite a bit more). Interestingly, as is evident from this list - This assumption is extremely common and happens all the time, even if it gets discussed here no end... (just search the list for "UPSERT"), so don't feel alone, near everyone has this wrong initially. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert or replace on PK and UNIQUE INDEX
Thank you all, Clemens, Richard, Simon, Hick. Now we know OR REPLACE is never what we want (in our use cases), and will rewrite into two statements, as Clemens and Simon indicated. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert or replace on PK and UNIQUE INDEX
The conflict action "replace" looks at ALL the PRIMARY KEY and UNIQUE constraints (express or implied) and deletes ALL the existing rows that prevent the candidate row from being inserted. As in "I don't care what it takes, I want THIS row to be in the table." The other conflict actions just reflect the "level of panic" you associate with not being able to insert the row, from "don't care" (IGNORE) to "bloody hell" (ROLLBACK). -Ursprüngliche Nachricht- Von: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dominique Devienne Gesendet: Dienstag, 12. Juli 2016 14:39 An: SQLite mailing list Betreff: Re: [sqlite] insert or replace on PK and UNIQUE INDEX On Tue, Jul 12, 2016 at 2:31 PM, Richard Hipp wrote: > On 7/12/16, Dominique Devienne wrote: > > > > Is that normal or expected? > > The operation of REPLACE is defined here: > > https://www.sqlite.org/mark/lang_conflict.html?the+REPLACE+al*+current > +row#mark So the plural in "deletes pre-existing rows" explain that in my second example, with both the PK and NK where each point to different rows, both rows are first deleted, then the new row is inserted? I've never used "or replace" (this question comes from a different usage of SQLite than mine), and I guess they didn't read or understand the implications of the doc either. Thanks. Makes sense now, even though I still find it surprising a bit. Not what I'd naively assume without the benefit of reading the doc :) --DD ___ 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] insert or replace on PK and UNIQUE INDEX
On 12 Jul 2016, at 1:39pm, Dominique Devienne wrote: > So the plural in "deletes pre-existing rows" explain that in my second > example, > with both the PK and NK where each point to different rows, both rows are > first > deleted, then the new row is inserted? Correct. There is a common assumption that INSERT OR REPLACE is just a name, and that behind the scenes the SQL engine just makes some complicated changes inside the database. But in SQLite it really does do a number of DELETEs and then an INSERT. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert or replace on PK and UNIQUE INDEX
On Tue, Jul 12, 2016 at 2:31 PM, Richard Hipp wrote: > On 7/12/16, Dominique Devienne wrote: > > > > Is that normal or expected? > > The operation of REPLACE is defined here: > > https://www.sqlite.org/mark/lang_conflict.html?the+REPLACE+al*+current+row#mark So the plural in "deletes pre-existing rows" explain that in my second example, with both the PK and NK where each point to different rows, both rows are first deleted, then the new row is inserted? I've never used "or replace" (this question comes from a different usage of SQLite than mine), and I guess they didn't read or understand the implications of the doc either. Thanks. Makes sense now, even though I still find it surprising a bit. Not what I'd naively assume without the benefit of reading the doc :) --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert or replace on PK and UNIQUE INDEX
On 12 Jul 2016, at 1:26pm, Clemens Ladisch wrote: > An INSERT OR UPDATE (or MERGE) statement does not exist in SQLite. > > Just try the UPDATE, and if the number of affected rows is zero, do the > INSERT. I prefer to do INSERT OR IGNORE ... UPDATE ... That way you do not have to count the affected rows. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert or replace on PK and UNIQUE INDEX
On 7/12/16, Dominique Devienne wrote: > > Is that normal or expected? The operation of REPLACE is defined here: https://www.sqlite.org/mark/lang_conflict.html?the+REPLACE+al*+current+row#mark -- 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] insert or replace on PK and UNIQUE INDEX
Dominique Devienne wrote:
> sqlite> select * from t;
> 1|one bis
> 2|two
> sqlite> insert or replace into t (name) values ('one bis');
> sqlite> select * from t;
> 2|two
> 3|one bis
>
> In the session above, we can see that an insert or replace w/o an id (the PK)
> value,
> results in the id changing in the table, which is not what we'd like.
>
> Is that normal or expected?
REPLACE always deletes the old row, if it exists. This is documented.
> Is there a SQL way (one statement ideally) to achieve what we'd like?
An INSERT OR UPDATE (or MERGE) statement does not exist in SQLite.
Just try the UPDATE, and if the number of affected rows is zero, do the
INSERT.
Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query question: order by ascending, return the two largest values in ascending order
Clemens Regards, 2. Sorting the entries before LIMIT is applied. 1. Sorting the entries before group_concat() is applied; or Chris Locke wrote: > Whats the benefit of getting a sorted query and then sorting that query > again? > > On Tue, Jul 12, 2016 at 12:45 AM, Stephen Chrzanowski > wrote: > >> select * from (SELECT date_time_stamp FROM general ORDER BY date_time_stamp >> DESC LIMIT 2) a order by date_time_stamp; ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cyclic detection in recursive queries
On 7/12/16, New, Cecil (GE Aviation, US) wrote: > The best I have been able to come with is documented at: > http://stackoverflow.com/questions/32640043/cannot-detect-cyclic-data-in-an-sqlite-database/32719216#32719216\ So you have a graph with loops. What is your problem, though? Do you merely want to detect the loops? Or are you trying to query the graph without getting stuck chasing loops around and around? > Postgresql has a specific way of detecting loops, which would be even more > robust. It is documented here: > https://www.postgresql.org/docs/9.1/static/queries-with.html That's a long document. Can you be more specific about what loop detection mechanism of PostgreSQL you have in mind? -- 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] insert or replace on PK and UNIQUE INDEX
On Tue, Jul 12, 2016 at 2:12 PM, Dominique Devienne
wrote:
> C:\Users\ddevienne>sqlite3
> SQLite version 3.10.2 2016-01-20 15:27:19
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table t (id integer primary key autoincrement, name text
> unique);
> sqlite> insert into t values (1, 'one'), (2, 'two');
> sqlite> select * from t;
> 1|one
> 2|two
> sqlite> insert into t values (1, 'one bis');
> Error: UNIQUE constraint failed: t.id
> sqlite> insert or replace into t values (1, 'one bis');
> sqlite> select * from t;
> 1|one bis
> 2|two
> sqlite> insert or replace into t (name) values ('one bis');
> sqlite> select * from t;
> 2|two
> 3|one bis
>
> In the session above, we can see that an insert or replace w/o an id (the
> PK) value,
> results in the id changing in the table, which is not what we'd like.
>
> The (incorrect on our part) thinking was that the UNIQUE index would also
> be used
> to recognize that the row already existed, and the id to remain the same
> (In reality in
> this case the natural key checked by the UNIQUE index is passed in
> unchanged to the
> insert or replace, and it's other columns that change), but apparently the
> autoincrement
> is first applied, and then the UNIQUE index is apparently still used,
> resulting in this
> unwanted ID change.
>
> Is that normal or expected?
> Is there a SQL way (one statement ideally) to achieve what we'd like?
>
sqlite> insert or replace into t values (3, 'two');
sqlite> select * from t;
3|two
Worse, and that's even more unexpected to me, I tried doing the insert or
replace above,
but specifying the PK the time, *and* explicitly trying to conflict on the
UNIQUE index,
and to me great surprise (horror?), that actually deleted one row instead
of failing!
That an insert or replace can result in fewer rows (i.e. akin to a delete)
is shocking to me.
Can someone please shed some light on this too? Thanks again, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] insert or replace on PK and UNIQUE INDEX
C:\Users\ddevienne>sqlite3
SQLite version 3.10.2 2016-01-20 15:27:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t (id integer primary key autoincrement, name text
unique);
sqlite> insert into t values (1, 'one'), (2, 'two');
sqlite> select * from t;
1|one
2|two
sqlite> insert into t values (1, 'one bis');
Error: UNIQUE constraint failed: t.id
sqlite> insert or replace into t values (1, 'one bis');
sqlite> select * from t;
1|one bis
2|two
sqlite> insert or replace into t (name) values ('one bis');
sqlite> select * from t;
2|two
3|one bis
In the session above, we can see that an insert or replace w/o an id (the
PK) value,
results in the id changing in the table, which is not what we'd like.
The (incorrect on our part) thinking was that the UNIQUE index would also
be used
to recognize that the row already existed, and the id to remain the same
(In reality in
this case the natural key checked by the UNIQUE index is passed in
unchanged to the
insert or replace, and it's other columns that change), but apparently the
autoincrement
is first applied, and then the UNIQUE index is apparently still used,
resulting in this
unwanted ID change.
Is that normal or expected?
Is there a SQL way (one statement ideally) to achieve what we'd like?
Thanks, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Cyclic detection in recursive queries
The best I have been able to come with is documented at: http://stackoverflow.com/questions/32640043/cannot-detect-cyclic-data-in-an-sqlite-database/32719216#32719216 But a) it is ugly, b) performance impact of all the length(), replace() functions, c) if values end in similar strings, it probably won't work. After some thought, I think the minimum that would solve this problem is to enhance the instr() function to either take a starting position to begin the search or to take an occurrence number to search for. Oracle's version of instr() does both of these (see https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_1103.htm) Postgresql has a specific way of detecting loops, which would be even more robust. It is documented here: https://www.postgresql.org/docs/9.1/static/queries-with.html ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Redundant open *.sqlite-wal file
Hi, Richard, Funny I got this email because you actually called me on the phone yesterday and I just missed your call. You were returning my call to your team. I had called in the hopes of being pointed in the right direction for someone to help me with two small things with my program. 1) I'm in Visual Studio, VB.NET, on a Windows 10 platform and I need to periodically sync from an SQL Server to an SQLite database. 2) This is an easy one: I'm an amateur and I haven't figured out how to populate a datagrid table with SQLite, since normally I do the drag-and-drop method and that's not working for me with SQLite, either because it just doesn't, or perhaps because something's installed wrong. I thought someone like you would instantly know if there's a way to drag and drop or not. (I'm a musician band leader and I'm trying to finish a program for a gig in a few days where my musicians can read the music from tablet PCs.) Any help would be greatly appreciated. I'm willing to hire someone to guide me through this. Thanks Richard, Robby Helperin -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Monday, July 11, 2016 6:13 AM To: SQLite mailing list Subject: Re: [sqlite] Redundant open *.sqlite-wal file On 7/11/16, pavel.pimenov wrote: > Hi > > journal_mode=PERSIST ! but sqlite 3.13.0 tries to open a file > *.sqlite-wal SQLite does not know the journal mode until it has opened the database. And it cannot safely open the database without first checking for the existance of a -wal file that some prior process might have left laying around due to a (non-SQLite related) crash. -- 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query question: order by ascending, return the two largest values in ascending order
Whats the benefit of getting a sorted query and then sorting that query again? On Tue, Jul 12, 2016 at 12:45 AM, Stephen Chrzanowski wrote: > Simons + My answer; > > select * from (SELECT date_time_stamp FROM general ORDER BY date_time_stamp > DESC LIMIT 2) a order by date_time_stamp; > > On Mon, Jul 11, 2016 at 7:33 PM, Simon Slavin > wrote: > > > > > On 12 Jul 2016, at 12:25am, Keith Christian > > wrote: > > > > > A table has a column of dates and times that look like this: > > > > > > 2015-10-02 07:55:02 > > > 2015-10-02 07:55:02 > > > 2015-10-02 10:00:03 > > > 2015-10-02 10:05:02 > > > 2015-10-02 10:10:02 > > > > > > > > > Schema: > > > CREATE TABLE general ( id integer primary key autoincrement, server > > > text, date_time_stamp text); > > > > > > > > > Would like to get the latest two dates and times, kept in ascending > > > order, e.g. the query should return these two values: > > > > > > 2015-10-02 10:05:02 > > > 2015-10-02 10:10:02 > > > > SELECT date_time_stamp FROM general ORDER BY date_time_stamp DESC LIMIT 2 > > > > The only difference is that the rows will always be in the reverse order > > to what you asked for: biggest timestamp first. But since it's > consistent > > that shouldn't be a problem. > > > > I recommend you create an index on the date_time_stamp column, since that > > will make the above query work far faster. > > > > Simon. > > ___ > > 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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query question: order by ascending, return the two largest values in ascending order
Thanks for all of the responses. Duplicates are OK, the date time stamps are part of a log file that I am trying to develop a query for. I couldn't get the LIMIT/OFFSET part of the query right after several attempts so I thought I'd ask the experts here. I appreciate your replies and suggestions. Keith On Mon, Jul 11, 2016 at 11:12 PM, J Decker wrote: > SELECT DISTINCT date_time_stamp FROM general ORDER BY date_time_stamp DESC > LIMIT 2 > > isn't it simply to use DISTINCT? > > On Mon, Jul 11, 2016 at 4:25 PM, Keith Christian > wrote: > >> A table has a column of dates and times that look like this: >> >> 2015-10-02 07:55:02 >> 2015-10-02 07:55:02 >> 2015-10-02 10:00:03 >> 2015-10-02 10:05:02 >> 2015-10-02 10:10:02 >> >> >> Schema: >> CREATE TABLE general ( id integer primary key autoincrement, server >> text, date_time_stamp text); >> >> >> Would like to get the latest two dates and times, kept in ascending >> order, e.g. the query should return these two values: >> >> 2015-10-02 10:05:02 >> 2015-10-02 10:10:02 >> >> >> Is there a way to store the number of values in the date_time_stamp >> column and use the count minus N to get the largest N values in the >> column? >> >> >> Query: >> select date_time_stamp a from general, c as count(a) from general >> where date_time_stamp!='date_time_stamp_isempty' order by a limit c,2; >> >> >> Error: incomplete SQL: select date_time_stamp a from general, c as >> count(a) from general where date_time_stamp!='_isempty' order by a >> limit c,2 >> ___ >> 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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ENABLE_UPDATE_DELETE_LIMIT
Thanks, definitely looks straightforward to me. On Tue, Jul 12, 2016 at 11:14 AM, Jan Nijtmans wrote: > 2016-07-12 11:03 GMT+02:00 Chris Brody: >> Personally I would really like to see this. Can you show the patch somewhere? > > Here is the patch. I'm not sure that the SQLite mailing list accepts > attachments, > but your private mail surely does. The patch is generated against current > SQLite trunk. > > Regards, > Jan Nijtmans ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ENABLE_UPDATE_DELETE_LIMIT
2016-07-12 11:03 GMT+02:00 Chris Brody:
> Personally I would really like to see this. Can you show the patch somewhere?
Here is the patch. I'm not sure that the SQLite mailing list accepts
attachments,
but your private mail surely does. The patch is generated against current
SQLite trunk.
Regards,
Jan Nijtmans
Index: src/parse.y
==
--- src/parse.y
+++ src/parse.y
@@ -735,54 +735,56 @@
limit_opt(A) ::= LIMIT expr(X) COMMA expr(Y).
{A.pOffset = X.pExpr; A.pLimit =
Y.pExpr;}
/// The DELETE statement /
//
-%ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W)
orderby_opt(O) limit_opt(L). {
sqlite3WithPush(pParse, C, 1);
sqlite3SrcListIndexedBy(pParse, X, &I);
+#if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) &&
!defined(SQLITE_OMIT_SUBQUERY)
W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "DELETE");
- sqlite3DeleteFrom(pParse,X,W);
-}
-%endif
-%ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
-cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W). {
- sqlite3WithPush(pParse, C, 1);
- sqlite3SrcListIndexedBy(pParse, X, &I);
+#else
+ if( O || L.pLimit ){
+sqlite3ErrorMsg(pParse, "%s on DELETE not supported", O?"ORDER
BY":"LIMIT");
+sqlite3ExprDelete(pParse->db, W);
+sqlite3ExprListDelete(pParse->db, O);
+sqlite3ExprDelete(pParse->db, L.pLimit);
+sqlite3ExprDelete(pParse->db, L.pOffset);
+W = 0;
+ }
+#endif
sqlite3DeleteFrom(pParse,X,W);
}
-%endif
%type where_opt {Expr*}
%destructor where_opt {sqlite3ExprDelete(pParse->db, $$);}
where_opt(A) ::= .{A = 0;}
where_opt(A) ::= WHERE expr(X). {A = X.pExpr;}
// The UPDATE command
//
-%ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
where_opt(W) orderby_opt(O) limit_opt(L). {
sqlite3WithPush(pParse, C, 1);
sqlite3SrcListIndexedBy(pParse, X, &I);
sqlite3ExprListCheckLength(pParse,Y,"set list");
+#if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) &&
!defined(SQLITE_OMIT_SUBQUERY)
W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "UPDATE");
- sqlite3Update(pParse,X,Y,W,R);
-}
-%endif
-%ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
-cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
-where_opt(W). {
- sqlite3WithPush(pParse, C, 1);
- sqlite3SrcListIndexedBy(pParse, X, &I);
- sqlite3ExprListCheckLength(pParse,Y,"set list");
+#else
+ if( O || L.pLimit ){
+sqlite3ErrorMsg(pParse, "%s on UPDATE not supported", O?"ORDER
BY":"LIMIT");
+sqlite3ExprDelete(pParse->db, W);
+sqlite3ExprListDelete(pParse->db, O);
+sqlite3ExprDelete(pParse->db, L.pLimit);
+sqlite3ExprDelete(pParse->db, L.pOffset);
+W = 0;
+ }
+#endif
sqlite3Update(pParse,X,Y,W,R);
}
-%endif
%type setlist {ExprList*}
%destructor setlist {sqlite3ExprListDelete(pParse->db, $$);}
setlist(A) ::= setlist(A) COMMA nm(X) EQ expr(Y). {
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ENABLE_UPDATE_DELETE_LIMIT
> Actually, it is very well possible to build an amalgamation which can > be built with or without ENABLE_UPDATE_DELETE_LIMIT, and > functions fine as expected both ways without rerunning Lemon. If you > are interested in a patch which demonstrates this, I'm happy to provide that. Personally I would really like to see this. Can you show the patch somewhere? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ENABLE_UPDATE_DELETE_LIMIT
2016-07-11 17:23 GMT+02:00 Richard Hipp: > Yes. ENABLE_UPDATE_DELETE_LIMIT makes changes to the LALR(1) parser > tables which cannot be #ifdef-ed out. So there is not a convenient > way to turn that feature off and on at compile-time except to rerun > the Lemon parser generator, which basically means rebuilding from > canonical sources. Actually, it is very well possible to build an amalgamation which can be built with or without ENABLE_UPDATE_DELETE_LIMIT, and functions fine as expected both ways without rerunning Lemon. If you are interested in a patch which demonstrates this, I'm happy to provide that. Regards, Jan Nijtmans ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug Report: All database opening blocked awaiting wal index rebuild
> Le 11 juil. 2016 à 21:57, Brian Vincent a écrit : > > Yes, you seem to understand the issue. The issue only happens when using > shared caches. > > I've reproduced the issue using both SERIALIZED and MULTITHREADED modes. > ... > Being an inherit limitation would seem to imply that there is no solution > to this problem, that having shared caches and WAL indexes rebuilding > necessarily should block all unrelated databases opening. I don't see why > that should be the case and I'll explain some reasons why. It's a little > bit hard for me to talk about it though, because I'm not entirely sure what > the lock SQLITE_MUTEX_STATIC_OPEN is protecting. When iterating through > the list of shared caches, it acquires the lock SQLITE_MUTEX_STATIC_MASTER, > so the other OPEN lock must be for something else. The comments say it's > to prevent a race condition and references "Ticket #3537", but I can't seem > to find that ticket. Indeed: > sqlite3_mutex *mutexOpen = 0; /* Prevents a race condition. Ticket #3537 */ I couldn't find that ticket either. > Please let me know if I'm thinking about this problem clearly, or if you > would like me to test some things or write a simple test case. As I'm just as you a user of Sqlite, it probably is best to let its developers take on this thread from here (or from the beginning). -- Meilleures salutations, Met vriendelijke groeten, Best Regards, Olivier Mascia, integral.be/om ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
