[sqlite] [PATCH][really] add missing autoconf feature detection

Yuriy Kaminskiy wrote: > Use of some function/features protected by #ifdefs, but lacks autoconf magic > to > automatically enable them when possible. Of course, they can be manually > enabled, but it is not very likely. And unused code tends to bitrot. Oops, last time patch attachme

[sqlite] [PATCH] add missing autoconf feature detection

Use of some function/features protected by #ifdefs, but lacks autoconf magic to automatically enable them when possible. Of course, they can be manually enabled, but it is not very likely. And unused code tends to bitrot. ___ sqlite-users mailing list

Re: [sqlite] When to open/close connections

Simon Slavin wrote: > On 29 Apr 2014, at 2:24pm, Drago, William @ MWG - NARDAEAST > wrote: > >> Does closing the connection force, or at least encourage, the OS to write to >> disk whatever it might have been caching? > > Closing a connection calls fclose() on the

Re: [sqlite] When to open/close connections

Simon Slavin wrote: > On 28 Apr 2014, at 11:11pm, RSmith wrote: > >> Second approach is better when you rarely access the database, also it will >> make sure releases happen (or at least provide immediate errors if not), but >> keeping a connection open is much better when

Re: [sqlite] Out of memory error for SELECT char();

Eduardo Morras wrote: > On Sat, 8 Mar 2014 14:09:17 -0500 > Richard Hipp wrote: >> It isn't really running out of memory >> >> The implementation of char() allocates 4 bytes of output buffer for >> each input character, which is sufficient to hold any valid unicode >>

Re: [sqlite] Trying to use in-memory database

Richard Hipp wrote: > On Wed, Feb 19, 2014 at 5:25 PM, Jeff Archer < > jsarc...@nanotronicsimaging.com> wrote: > >> Long time SQLite user but I don't think I have ever tried to do an >> in-memory database before. >> Just upgraded to 3.8.3.1 but I am not having any other failures with >> existing

Re: [sqlite] Once again about random values appearance

James K. Lowden wrote: > On Fri, 14 Feb 2014 08:32:02 +0400 > Max Vlasov wrote: > >> From: Max Vlasov >> To: General Discussion of SQLite Database >> Reply-To: General Discussion of SQLite Database >>

Re: [sqlite] LIKE operator and collations

Constantine Yannakopoulos wrote: > I have a case where the user needs to perform a search in a text column of > a table with many rows. Typically the user enters the first n matching > characters as a search string and the application issues a SELECT statement > that uses the LIKE operator with

Re: [sqlite] Understanding transactions

Igor Tandetnik wrote: > On 2/4/2014 5:51 PM, Yuriy Kaminskiy wrote: >> Igor Tandetnik wrote: >>> On 2/4/2014 11:57 AM, Yuriy Kaminskiy wrote: >>>> Phew. Do you speak C? Enjoy. >>>> >>>> printf("insert...\r"); ffl

Re: [sqlite] Understanding transactions

Igor Tandetnik wrote: > On 2/4/2014 11:57 AM, Yuriy Kaminskiy wrote: >> Phew. Do you speak C? Enjoy. >> >> printf("insert...\r"); fflush(stdout); >> for(i = 0; i < 1000; i++) { >> rc = sqlite3_bind_int(ins_sth, 1, i); >> asser

Re: [sqlite] Understanding transactions

Igor Tandetnik wrote: > On 2/4/2014 5:23 AM, Yuriy Kaminskiy wrote: >> How sqlite is supposed to behave when >> *) there are read-only transaction; >> *) there are update transaction on other connection; >> *) cache space is exhausted by update transaction; >>

Re: [sqlite] Understanding transactions

Igor Tandetnik wrote: > On 2/3/2014 3:21 PM, Yuriy Kaminskiy wrote: >> Igor Tandetnik wrote: >>> On 2/3/2014 1:07 PM, Baruch Burstein wrote: >>>> 1) How does a transaction affect SELECTs? If I start a transaction >>>> and do >>>> an UPD

Re: [sqlite] Understanding transactions

Igor Tandetnik wrote: > On 2/3/2014 1:07 PM, Baruch Burstein wrote: >> 1) How does a transaction affect SELECTs? If I start a transaction and do >> an UPDATE/DELETE/INSERT, what data will a SELECT in the same transaction >> see? > > The new data. A transaction always sees its own changes. > >>

Re: [sqlite] Do I really need rollback after sqlite3_step returns BUSY?

Woody Wu wrote: > Hi, Simon > > On 7 January 2014 19:32, Simon Slavin wrote: > >> On 7 Jan 2014, at 10:13am, Woody Wu wrote: >> >>> Thanks for the clear guide. _busy_timeout is easier to use. By the >>> way, i want confirm that if i am not in an

Re: [sqlite] [patch][rebase for 3.8.2] really move "const" check out of loop "WHERE const" (and "WHERE const AND expr" optimization)

Richard Hipp wrote: > On Thu, Jan 2, 2014 at 3:25 PM, Yuriy Kaminskiy <yum...@gmail.com> wrote: > >> Richard Hipp wrote: >>> Please verify that the alternative optimization checked-in at >>> http://www.sqlite.org/src/info/b7e39851a7 covers all of the cas

Re: [sqlite] [patch][rebase for 3.8.2] really move "const" check out of loop "WHERE const" (and "WHERE const AND expr" optimization)

| 16|TableLock|0|2|0|t|00| 17|String8|0|9|0|now|00| 18|Function|1|9|7|date(-1)|01| 19|Integer|6|8|0||00| 20|Function|3|7|1|substr(2)|02| 21|String8|0|2|0|01-01|00| 22|Goto|0|2|0||00| > On Thu, Jan 2, 2014 at 9:25 AM, Yuriy Kaminskiy <yum...@gmail.com> wrote: > >> On 2013/11/04

Re: [sqlite] [sqlite-dev] sqlite3 db is locked

Simon Slavin wrote: > On 2 Jan 2014, at 2:57pm, Yuriy Kaminskiy <yum...@gmail.com> wrote: > >> Simon Slavin wrote: >>> sqlite3_busy_timeout() >> Waiting for timeout *cannot* fix any errors that can trigger failure in >> sqlite3_close. Those are *program log

Re: [sqlite] [sqlite-dev] sqlite3 db is locked

Simon Slavin wrote: > On 1 Jan 2014, at 7:43am, Alexander Syvak wrote: > >> The code in function from the 1st e-mail is used before exiting, so the >> sqlite3_close is called in fact. > > Please do not cross-post between sqlite-dev@ and sqlite@. If you need to > move

Re: [sqlite] Error 11 after doing a lot of simple insert/update operations!

Woody Wu wrote: > Hi, Simon > > I upload the source code onto my dropbox: > https://www.dropbox.com/s/9shhshi0wn3e717/downloadfile.c Please have a > look at it. > > The same test program run without a problem on my pc Linux after complied > natively. But I think I should not dout my

Re: [sqlite] Concrete example of corruption

Richard Hipp wrote: > Please try the changes in the branch at > http://www.sqlite.org/src/info/8759a8e4d8 and let me know if they > adequately cover your concerns. Let's suppose user just did cp -b somewhere/else/db opened.db There *are* still file named opened.db, but it points to *different*

Re: [sqlite] Concrete example of corruption

Warren Young wrote: > On 12/5/2013 20:31, Stephen Chrzanowski wrote: [...] >> File handling is NOT SQLites responsibility > > I'm not sure about that. SQLite, at least at one time, was billed as a > competitor for fopen() rather than for Oracle. But fopen(3) have no locking *at all*. And

Re: [sqlite] [patch 2/2] move "const" out of loop in "WHERE const AND expr"

On 2012/04/08 Yuriy Kaminskiy wrote: > On 2011/10/23, Yuriy Kaminskiy wrote: >> Yuriy Kaminskiy wrote: >>> Yuriy Kaminskiy wrote: >>>> Yuriy Kaminskiy wrote: >>>>> When WHERE condition is constant, there are no need to evaluate and check >>>

Re: [sqlite] ORDER BY DESC after GROUP BY not using INDEX - intentionally?

Fabian Büttner wrote: > Hi, > > I have been thinking about a question on stackoverflow > (http://stackoverflow.com/questions/19236363/select-distinct-faster-than-group-by), > where some SQL framework removes duplicates from results using GROUP BY > instead of DISTINCT. > I don't want to discuss

Re: [sqlite] SQLite keeps on searching endlessly

Raheel Gupta wrote: >> Yes, but they allow the searches to be faster. You are making it longer >> to do INSERT but shorter to do SELECT. Which is best for you depends on >> your purposes. >> > > I need the inserts to be faster. > So which is better ? An Index or a Primary Key ? Is there any

Re: [sqlite] Bug in sqlite3.exe?

Staffan Tylen wrote: > On Tue, Sep 24, 2013 at 6:50 PM, Simon Slavin wrote: > >> On 24 Sep 2013, at 5:35pm, Staffan Tylen wrote: >> >>> sqlite> .tables >>> CityCountry Languages >>> Country Country

Re: [sqlite] INSERT INTO ???

Bernhard Amann wrote: >> INSERT INTO SELECT * FROM ; >> >> However, this only works if already exists, which is actually >> quite cumbersome.. >> Is there a way to make the new table 'on the fly"? > > create table newtable as select * from oldtable; ... however, this won't keep constraints,

Re: [sqlite] racing with date('now') (was: Select with dates)

Keith Medcalf wrote: >> In C there are local variables, where you can save result of impure >> functions when it is important. There are no local variables in SQL >> - with even more extreme example shown in E.Pasma message nearby - >> `SELECT strftime('%f') AS q FROM t WHERE q <> q`; >> oh, by

Re: [sqlite] racing with date('now') (was: Select with dates)

Stephan Beal wrote: > On Sun, Sep 15, 2013 at 1:58 PM, Yuriy Kaminskiy <yum...@gmail.com> wrote: > >> Sure, there can be several way to interpret CURRENT_* and *('now'). >> However, >> some of them can be useful (transaction, statement), and others (step) -

Re: [sqlite] racing with date('now') (was: Select with dates)

Simon Slavin wrote: > On 14 Sep 2013, at 10:41pm, Yuriy Kaminskiy <yum...@gmail.com> wrote: > >> ... and I'd call even that difference between CURRENT_* and *('now') rather >> "query optimizer artifact" rather than "documented feature one can rely

Re: [sqlite] racing with date('now') (was: Select with dates)

Yuriy Kaminskiy wrote: > Keith Medcalf wrote: >>>>> You can easily reproduce this problem if you switch unit from month to >>>>> millisecond, e.g. >>>>> SELECT * FROM t WHERE strftime('%f') <> strftime('%f'); >>>>> will no

Re: [sqlite] racing with date('now') (was: Select with dates)

Keith Medcalf wrote: You can easily reproduce this problem if you switch unit from month to millisecond, e.g. SELECT * FROM t WHERE strftime('%f') <> strftime('%f'); will non-deterministically return rows. > IMO, correct [= least surprise] behavior should be "timestamp

Re: [sqlite] racing with date('now') (was: Select with dates)

Keith Medcalf wrote: >> On Saturday, 14 September, 2013 07:19, Yuriy Kaminskiy said: >> Keith Medcalf wrote: >>>> On Thu, 12 Sep 2013 14:01:04 +0100 >>>> Simon Davies <simon.james.dav...@gmail.com> wrote: >>>> >>>>> Why not &g

[sqlite] racing with date('now') (was: Select with dates)

Keith Medcalf wrote: >> On Thu, 12 Sep 2013 14:01:04 +0100 >> Simon Davies wrote: >> >>> Why not >>> SELECT * FROM "entry" WHERE >>>bankdate >= date('now','start of month') >>> AND bankdate < date('now','start of month','+1 month') >> The

Re: [sqlite] to encrypt sqlite db

Etienne wrote: > - Original message - > From: Paolo Bolzoni > To: General Discussion of SQLite Database > Subject: Re: [sqlite] to encrypt sqlite db > Date: Sun, 1 Sep 2013 18:24:13 +0200 >> On Sun, Sep 1, 2013 at 6:10 PM, Etienne

Re: [sqlite] to encrypt sqlite db

Ulrich Telle wrote: > Am 31.08.2013 22:01, schrieb Etienne: On Sat, 31 Aug 2013 17:17:23 +0200 Etienne wrote: > > On the other hand removing patterns definitely cannot hurt. > > Precisely. > > The very first

Re: [sqlite] Concurrent inserts -> DB corruption in travisci vm with sqlite 3.7.9

Gary Weaver wrote: > On Aug 15, 2013, at 3:47 PM, ibrahim wrote: > >> On 15.08.2013 21:39, Gary Weaver wrote: >>> SQLite varies between file is encrypted/not a DB errors and database disk >>> image is malformed. It would seem consistent with SQLite not handling >>>

Re: [sqlite] secure_delete versus journal_mode persist.

Simon Slavin wrote: > On 29 Jan 2013, at 8:19am, Scott Hess wrote: > >> insert into x values ('SQLite is a software library that implements >> a self-contained, serverless, zero-configuration, transactional SQL >> database engine. SQLite is the most widely deployed SQL database

Re: [sqlite] Problem with sqlite3prepare16_v2

Igor Tandetnik wrote: > On 1/6/2013 7:10 PM, Walter wrote: >>sqlite3_prepare16_v2 (vMdb, ws.c_str (), ws.size (), , ); > > The third parameter of sqlite3_prepare16_v2 is the length of the string > *in bytes*, not in characters. You are effectively passing only half the > statement. Besides,

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

Jay A. Kreibich wrote: > On Fri, Jan 04, 2013 at 10:55:43AM +0100, Krzysztof scratched on the wall: >> Hi, >> >> When I use INSERT OR IGNORE, if insertion fail (record exists), >> then sqlite3_last_insert_rowid does return nothing. Is exists similar >> solution which: >> 1. If insert success then

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

Clemens Ladisch wrote: > Krzysztof wrote: >> When I use INSERT OR IGNORE, if insertion fail (record exists), >> then sqlite3_last_insert_rowid does return nothing. > > If your unique key is the rowid, then you already know the ID that > you tried to insert. > If your unique key is not the rowid,

Re: [sqlite] What is wrong with this queries?

Igor Korot wrote: > Hi, ALL, > > sqlite> CREATE TABLE leagueplayers(id integer, playerid integer, value > integer, > currvalue double, foreign key(id) references leagues(id), foreign > key(playerid) > references players(playerid)); > sqlite> INSERT INTO leagueplayers VALUES(1,(SELECT

Re: [sqlite] sqlite3 db open/close

Durga D wrote: >What happens if sqlite3_close() called multiple times but > sqlite3_open_v2() called only once. > > Practically I dint see any malfunction/corruption here. I would like to > know the behavior of sqlite in this scenario. About same as char *foo = malloc(10);

Re: [sqlite] DatabaseError: database disk image is malformed

Larry Knibb wrote: > On 15 October 2012 12:32, Keith Medcalf wrote: >> Define "clients". Do you mean multiple client processes running on a single >> computer against a database hosted on an attached local device, such as on a >> Terminal Server for example? Or do you

Re: [sqlite] Unofficial poll

Jay A. Kreibich wrote: > On Sun, Sep 23, 2012 at 09:25:06PM +0400, Yuriy Kaminskiy scratched on the > wall: >> Jim Dodgen wrote: > >>> I program mostly on Perl on Linux and it is a beautiful fit. Example >>> is I can have a date field with a POSIX time value

Re: [sqlite] Unofficial poll

Jim Dodgen wrote: > On Sun, Sep 23, 2012 at 3:37 AM, Baruch Burstein wrote: >> I am curious about the usefulness of sqlite's "unique" type handling, and >> so would like to know if anyone has ever actually found any practical use >> for it/used it in some project? I am

Re: [sqlite] Covering index scan optimization

Simon Slavin wrote: > On 15 Sep 2012, at 12:08pm, Elefterios Stamatogiannakis > wrote: > >> What i would really like to have in SQLite concerning OLAP, would be bigger >> pages, > > You can set pagesize for a new database using a PRAGMA: > >

Re: [sqlite] classic update join question

Igor Tandetnik wrote: > On 9/5/2012 12:38 PM, E. Timothy Uy wrote: >> I have a column in table 'alpha' which I would like to populate with data >> from table 'beta'. As far as I know, we cannot do an UPDATE using JOIN in >> sqlite, but we can >> >> UPDATE alpha SET frequency = (SELECT frequency

Re: [sqlite] why no such column in sqlite3 ?

Rob Richardson wrote: > Put single quotes around Testitem: > > sprintf( sqlquery, "INSERT INTO tblTest ( CINDEX, CDATE, CDESCR, > CAMOUNT ) VALUES ( 5, 2012-08-29, 'Testitem', 300 )"); And around cdate too. There are no dedicated date type in sqlite, 2012-08-29 is treated as expression

Re: [sqlite] how to update the Moving average value

Keith Medcalf wrote: > You are right Klaas, it should be -2 not -3. You could always constrain id > to (MAXINT >= id >= 3-MAXINT) if you wanted to be sure there would not be an > arithmetic overflow. 1) s/MAXINT/INT64_MAX/; 2) it is rather inefficient; 3) it will break on ID discontinuity; and

Re: [sqlite] AUTO_INCREMENT error

Igor Tandetnik wrote: > Brandon Pimenta wrote: >> CREATE TABLE test ( >> test_1 INTEGER PRIMARY KEY NOT NULL AUTOINCREMENT >> ); > > Make it > > INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL > > Though NOT NULL is redundant - PRIMARY KEY implies it. Unlike other sql

Re: [sqlite] SQLITE_STATIC and temporary data

Jay A. Kreibich wrote: > On Wed, Aug 01, 2012 at 08:49:19PM +1000, Yose Widjaja scratched on the wall: >> Dear Friends, >> >> So SQLITE_STATIC is meant to be used for data that is static. However, >> would it still be safe when it is used with data that expires after the >> sqlite3_step()

Re: [sqlite] force read schema after "delete from sqlite_master"

Gabriel Corneanu wrote: > I have the following scenario: I need to "clear"/"initialize" a db file > while potential readers are active (polling for data). > The "normal" way to do it is begin a transaction, drop all tables, recreate > tables, commit (vacuum to regain space). > > The biggest

Re: [sqlite] Bug in sqlite3_step

Igor Tandetnik wrote: > On 7/3/2012 10:05 AM, Unsupported wrote: >> // case 1: exception >> //verify(sqlite3_prepare_v2(db, "create trigger updater >> update of result on plugins" >> // " begin" >> // " update mails set kav=case old.result when >>

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

Paul van Helden wrote: > Is this correct? Should update triggers not only fire for actual changes? I > have a large table with a column which contains all NULL values except for > 4. I expected an UPDATE table SET column=NULL to only fire 4 triggers, > except it fires for every row. I'm pretty

Re: [sqlite] escaping GLOB pattern

nobre wrote: > "If the optional ESCAPE clause is present, then the expression following the > ESCAPE keyword must evaluate to a string consisting of a single character. > This character may be used in the LIKE pattern to include literal percent or > underscore characters. The escape character

Re: [sqlite] escaping GLOB pattern

Richard Hipp wrote: > On Mon, Jun 18, 2012 at 3:34 AM, Baruch Burstein wrote: > >> Is there a built-in way to escape a GLOB pattern? Will it escape it if I >> bind it to a parameter in a prepared function instead of embedding it >> directly in the query string? no,

Re: [sqlite] Strange behavior with fts4

Philip Bennefall wrote: > I hate to be cluttering up the list in this fashion, but I have come across > an issue that I cannot seem to find a solution for. > > I am using two fts tables, one that uses the normal tokenizer and another > that uses the porter stemmer, so that I can search the same

Re: [sqlite] Why can't sqlite disregard unused outer joins?

Richard Hipp wrote: > On Wed, May 30, 2012 at 2:17 PM, Yuriy Kaminskiy <yum...@gmail.com> wrote: > >> Pavel Ivanov wrote: >>> Here is an example when left outer join makes the difference. Example >>> could seem very artificial but SQLite should count on an

Re: [sqlite] Why can't sqlite disregard unused outer joins?

Pavel Ivanov wrote: > Here is an example when left outer join makes the difference. Example > could seem very artificial but SQLite should count on any possible > usage. > > sqlite> create table Employee (name int); > sqlite> create table Uniform (employeename, inseam, constraint ue > unique

Re: [sqlite] copy table to another db file

Luuk wrote: > On 17-05-2012 11:04, YAN HONG YE wrote: >> I have two db files: >> >> sqlite3 *db1; >> sqlite3 *db2; >> rc1 = sqlite3_open("myfile1", ); >> rc2 = sqlite3_open("myfile2", ); >> >> I want to copy db1.table1 to db2 file, but I don't know how to do? > > sqlite myfile1 > sqlite> attach

Re: [sqlite] Is it possible to insert UTF-8 strings in SQLITE3.EXE?

Kit wrote: > 2012/5/13, Frank Chang : >> Here is another way I found out how insert UTF-8 strings in SQLITE3.EXE. >> >> F:\sqlite3_6_16>sqlite3.exe mdName.dat >> SQLite version 3.6.16 >> Enter ".help" for instructions >> Enter SQL statements terminated with a ";" >>

Re: [sqlite] Getting rowid for last returned row

Simon Slavin wrote: > On 11 May 2012, at 3:36pm, Scott Ferrett > wrote: > >> If this is not possible, I can restrict this bit of code to only work on >> UPDATE statements. But that still leaves me with the problem of needing >> the rowid of the row being updated. >

Re: [sqlite] ordering doesn't work the way I expected

William Parsons wrote: > In my application, I've encountered a problem with ordering where the result > doesn't match what I would have expected, and would like some clarification. > The issue is illustrated by the following: > > % sqlite3 :memory: > SQLite version 3.7.10 2012-01-16 13:28:40 >

Re: [sqlite] Bug: Memory leak using PRAGMA temp_store_directory

Josh Gibbs wrote: > I reported this a while ago and forgot about this until today while I > was doing some debugging and once again got the report of leaked memory. > > I'm using the c amalgamation code from 3.7.10 with VStudio 2010, and > always start up my databases setting a temp directory to

Re: [sqlite] [patch 2/2] move "const" out of loop in "WHERE const AND expr"

On 2011/10/23, Yuriy Kaminskiy wrote: > Yuriy Kaminskiy wrote: >> Yuriy Kaminskiy wrote: >>> Yuriy Kaminskiy wrote: >>>> When WHERE condition is constant, there are no need to evaluate and check >>>> it for >>>> each row. It works, but only

Re: [sqlite] table aliases in update

Igor Tandetnik wrote: > Baruch Burstein wrote: >> Does sqlite not support table aliases in update statements? > > Indeed it does not. > >> Is there a way >> to work around this to get the affect of >> >> update table1 t1 >>set col1 = col1 * 2 >>where col1 <=

Re: [sqlite] [patch] sqlite-3.7.11: valgrind errors in testsuite

Dan Kennedy wrote: > On 03/31/2012 04:04 PM, Yuriy Kaminskiy wrote: >> valgrind ./testfixture test/trigger7.test >> >> Note: line numbers below are off-by-2. >> >> trigger7-2.1... Ok >> trigger7-2.2...==11533== Invalid read of size 1 >> >> S

[sqlite] [patch] sqlite-3.7.11: valgrind errors in testsuite

valgrind ./testfixture test/trigger7.test Note: line numbers below are off-by-2. trigger7-2.1... Ok trigger7-2.2...==11533== Invalid read of size 1 ==11533==at 0x401FD90: memcpy (mc_replace_strmem.c:482) ==11533==by 0x8098EE2: sqlite3VdbeMemGrow (vdbemem.c:90) ==11533==by 0x80CD503:

Re: [sqlite] Crash (c0000005 - Access Violation) in sqlite3.exe

Yuriy Kaminskiy wrote: > Richard Hipp wrote: >> On Thu, Feb 23, 2012 at 12:29 PM, Petite Abeille >> <petite.abei...@gmail.com>wrote: >> >>> On Feb 23, 2012, at 6:21 PM, Levi Haskell wrote: >>> >>>> sqlite> select 1 from (select

Re: [sqlite] Crash (c0000005 - Access Violation) in sqlite3.exe

Richard Hipp wrote: > On Thu, Feb 23, 2012 at 12:29 PM, Petite Abeille > wrote: > >> On Feb 23, 2012, at 6:21 PM, Levi Haskell wrote: >> >>> sqlite> select 1 from (select *); >> Wow, wicked :) >> >> Confirmed on sqlite3 -version >> 3.7.10 2012-01-16 13:28:40

Re: [sqlite] Support EXTRACT SQL standard function

Roger Andersson wrote: > On 02/12/12 20:34, Yuriy Kaminskiy wrote: >> I wonder, how it will be handled if you issue such request at >> month/year/... >> change (23:59.59.999 GMT -> 00:00:00.000 GMT)? >> Is timestamp for current_date/current_time generated once and c

Re: [sqlite] Support EXTRACT SQL standard function

Roger Andersson wrote: > On 02/11/12 15:22, Kit wrote: >> 2012/2/10 Willian Gustavo >> Veiga: >>> SQLite is a great database to unit test (TDD) applications. You can >>> run it >>> in memory with your tests ... >>> >>> I've found a problem when I was unit testing my application.

Re: [sqlite] How to insert control characters into a table

Kai Peters wrote: > Hi, > > how can I insert a control character like carriage return? > > Something like: > > update fielddefs set choices = 'Male' || '\r' || 'Female' where id = 2 update ... 'Male' || X'0D' || 'Female' ... ___ sqlite-users mailing

Re: [sqlite] [patch 2/2] move "const" out of loop in "WHERE const AND expr"

Yuriy Kaminskiy wrote: > Yuriy Kaminskiy wrote: >> Yuriy Kaminskiy wrote: >>> When WHERE condition is constant, there are no need to evaluate and check >>> it for >>> each row. It works, but only partially: >> ... >>> [In fact, you ca

Re: [sqlite] [bug] LIKE operator ignores rest of string after NUL character

Simon Slavin wrote: > On 9 Nov 2011, at 8:03pm, Yuriy Kaminskiy wrote: > >> Look at: SELECT hex(X'1245005679'),hex(X'1245001234'); >> >> And compare: SELECT X'1245005679' LIKE X'1245001234'; 1 -- incorrect SELECT >> X'1245005679' = X'1245001234'; 0 -- corre

Re: [sqlite] [bug] LIKE operator ignores rest of string after NUL character

Roger Andersson wrote: > On 11/09/11 19:42, Yuriy Kaminskiy wrote: >> Paul Corke wrote: >>> On 09 November 2011 15:32, hmas wrote: >>> >>>> sqlite> select hex(foocol) from footable where foocol like >>>> '98012470700566'; >>>>

[sqlite] [bug] LIKE operator ignores rest of string after NUL character (was: select ... where [=] or [like])

Paul Corke wrote: > On 09 November 2011 15:32, hmas wrote: > >> sqlite> select hex(foocol) from footable where foocol like >> '98012470700566'; >> 39393939393830313234373037303035363600 > > It looks like there's an extra 00 on the end. > > x'3900' != x'39' That said, it seems LIKE

Re: [sqlite] Vacuum on sqlite file fills the .wal file and it never shriks since

Richard Hipp wrote: > On Tue, Nov 8, 2011 at 10:04 AM, Orit Alul wrote: > >> Hi, >> >> I've performed a vacuuming operation (I ran the following command: >> sqlite3.exe VACUUM;). >> It caused the WAL file to be the same size as the db file and it never >> shrink back.

Re: [sqlite] [patch] Re: Potential bug: "insert into X select * from Y" ignores the "ON CONFLICT REPLACE" conflict-clause

Yuriy Kaminskiy wrote: > Yuriy Kaminskiy wrote: >> David wrote: >>> Simon L wrote 2011-10-25 06:20: >>>> To reproduce this problem, enter the following 5 SQL statements at the >>>> SQLite command line. >>>> >>>> create table X(i

Re: [sqlite] [patch] Re: Potential bug: "insert into X select * from Y" ignores the "ON CONFLICT REPLACE" conflict-clause

Yuriy Kaminskiy wrote: > David wrote: >> Simon L wrote 2011-10-25 06:20: >>> To reproduce this problem, enter the following 5 SQL statements at the >>> SQLite command line. >>> >>> create table X(id INTEGER primary key ON CONFLICT REPLACE); >>

Re: [sqlite] [patch] shell.c: fix .schema failure after PRAGMA case_sensitive_like = ON

Yuriy Kaminskiy wrote: > Two alternative patches, choose whichever you like. > > Alternative 1: (IMO, preferred; tested) > Don't lowercase argument of .schema. > With PRAGMA case_sensitive_like = ON, you just need to use right case for > table > names. > > Index

[sqlite] [patch] shell.c: make written history size tuneable

... with $SQLITE3_HISTSIZE. Positive numbers limits history size, zero - don't write to history at all (but read existing and keep in memory), negative - always append to history file (useful when you run few instances of sqlite3 at time and want to save history from all). Default - 100, same as

Re: [sqlite] [patch 2/2] move "const" out of loop in "WHERE const AND expr"

Yuriy Kaminskiy wrote: > Yuriy Kaminskiy wrote: >> When WHERE condition is constant, there are no need to evaluate and check it >> for >> each row. It works, but only partially: > ... >> [In fact, you can move out out loop not only *whole* constant WHERE, but

Re: [sqlite] core dump happened in sqlite3_step for 30 statements in BEGIN TRANSACTION executing every 10 Millisecond Take 2

ChingChang Hsiao wrote: > I can't reply in my system, so I create the problem description again. > > I miss one source code line "char tempString[1024];"in the last email. The > code dump happened after 4 days' run in a test script not immediately. The > SQLITE statements seem to be ok. Could be

Re: [sqlite] does sqlite3_reset have a performance impact onqueries?

Teg wrote: > I'd like this clarified too. I specifically don't use transactions > when I'm selecting. In fact, I'll select, then start a transaction > later for inserting the results. Would I be better off wrapping the > whole thing in a transaction? Cannot be sure without looking at

Re: [sqlite] does sqlite3_reset have a performance impact onqueries?

Black, Michael (IS) wrote: > Maybe my memory is fading but this is the first time I've heard anybody say > the wrapping a BEBIN around a SELECT was needed. I'd swear it was always > said it wasn't ever needed. > > > > From the docs > > http://www.sqlite.org/lang_transaction.html > basically,

Re: [sqlite] does sqlite3_reset have a performance impact on queries?

Stephan Beal wrote: > On Tue, Nov 1, 2011 at 11:25 PM, Tal Tabakman wrote: > >> second,needless to say that I want to avoid this since it causes mem >> leaks.) >> > > Why would it leak? Are you intentionally NOT calling finalize()? > > >>sqlite3_prepare_v2(handle,

[sqlite] [patch] Re: Potential bug: "insert into X select * from Y" ignores the "ON CONFLICT REPLACE" conflict-clause

David wrote: > Simon L wrote 2011-10-25 06:20: >> To reproduce this problem, enter the following 5 SQL statements at the >> SQLite command line. >> >> create table X(id INTEGER primary key ON CONFLICT REPLACE); >> create table Y(id INTEGER primary key ON CONFLICT REPLACE); >> insert into X values

[sqlite] [patch 2/2] move "const" out of loop in "WHERE const AND expr"

Yuriy Kaminskiy wrote: > When WHERE condition is constant, there are no need to evaluate and check it > for > each row. It works, but only partially: ... > [In fact, you can move out out loop not only *whole* constant WHERE, but also > all constant AND terms of WHERE, like this: &g

[sqlite] [patch] constant WHERE elimination (partially) ineffective

When WHERE condition is constant, there are no need to evaluate and check it for each row. It works, but only partially: sqlite> explain SELECT * FROM t; 0|Trace|0|0|0||00| 1|Goto|0|17|0||00| 2|OpenRead|0|60|0|9|00| 3|Rewind|0|15|0||00| 4|Column|0|0|1||00| 5|Column|0|1|2||00| 6|Rowid|0|3|0||00|

Re: [sqlite] [patch] shell.c: fix .schema failure after PRAGMA case_sensitive_like = ON

Yuriy Kaminskiy wrote: > Alternative 2: (partially tested) > Explicitly use case-insensitive comparison for table/indexes, no matter what > case_sensitive_like is. > > Index: sqlite3-3.7.8/src/shell.c > === > -

[sqlite] [patch] shell.c: fix .schema failure after PRAGMA case_sensitive_like = ON

Two alternative patches, choose whichever you like. Alternative 1: (IMO, preferred; tested) Don't lowercase argument of .schema. With PRAGMA case_sensitive_like = ON, you just need to use right case for table names. The author or authors of this code dedicate any and all copyright interest in

Re: [sqlite] [patch, try 2] Re: Ambiguous column name when using multiple JOIN USING clauses and WHERE clause

Jeremy Evans wrote: > On Sat, Oct 15, 2011 at 11:46 PM, Yuriy Kaminskiy <yum...@mail.ru> wrote: > 7> Whoops, patch eaten by hungry ewoks. Hopefully, inlining will work better: >> Subject: fix false "ambiguous column" detection in multiple JOIN USING >> >>

[sqlite] [patch, try 2] Re: Ambiguous column name when using multiple JOIN USING clauses and WHERE clause

Yuriy Kaminskiy wrote: > Yuriy Kaminskiy wrote: >> Jeremy Evans wrote: >>> After being open for more than 2 years, this ticket >>> (http://www.sqlite.org/src/tktview/3338b3fa19ac4abee6c475126a2e6d9d61f26ab1) >>> was closed by Dr. Hipp with the comment: >>

[sqlite] [patch] Re: Ambiguous column name when using multiple JOIN USING clauses and WHERE clause

Yuriy Kaminskiy wrote: > Jeremy Evans wrote: >> After being open for more than 2 years, this ticket >> (http://www.sqlite.org/src/tktview/3338b3fa19ac4abee6c475126a2e6d9d61f26ab1) >> was closed by Dr. Hipp with the comment: >> >> "The column name is ambiguou

Re: [sqlite] Ambiguous column name when using multiple JOIN USING clauses and WHERE clause

Jeremy Evans wrote: > After being open for more than 2 years, this ticket > (http://www.sqlite.org/src/tktview/3338b3fa19ac4abee6c475126a2e6d9d61f26ab1) > was closed by Dr. Hipp with the comment: > > "The column name is ambiguous. Does it mean a.a or b.a? The result is > the same either way, but