Re: [sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS

On 8 Oct 2014, at 5:14am, Stephen Chrzanowski wrote: > The one downside I just realized is that ON CONFLICT can be used outside of > the table declarations as well, so perhaps a different word or signal might > be needed for it to make linguistic sense, or, this version of

[sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS

I'm making up a small database (for yet another tool I never plan on releasing) and during the table creation, I had a thought about the "Not Null" and "On Conflict" resolution mechanism. When adding a NULL value to a table that has the NOT NULL flag set on that field, instead of raising an

Re: [sqlite] UTF support

Actually, Unicode / ISO 10646 is a 21-bit encoding, with values from 0 to 0x10. On Wed, Oct 8, 2014 at 10:13 AM, J Decker wrote: > On Tue, Oct 7, 2014 at 2:20 PM, jose isaias cabrera < > jic...@cinops.xerox.com > > wrote: > > > > > "J Decker" wrote... > > > > > > So, I

Re: [sqlite] UTF support

On Tue, Oct 7, 2014 at 2:20 PM, jose isaias cabrera wrote: > > "J Decker" wrote... > > > So, I guess it is technically not allowed to encode 11 bit unicode >> characters as 16. >> the greek characters are 0x3XX which is 10 bits... I checked what >> WideCharToMultiByte

Re: [sqlite] UTF support

"J Decker" wrote... So, I guess it is technically not allowed to encode 11 bit unicode characters as 16. the greek characters are 0x3XX which is 10 bits... I checked what WideCharToMultiByte was doing and found it was using 11 bit encodings... fixed my encoder to use an appropriate size for

Re: [sqlite] Detecting multiple CHECK failures

On 7 Oct 2014, at 10:00pm, Peter Haworth wrote: > I'm a great believer in using CHECK constraints to do as much validation as > possible within the database rather than code it in my application. > > However, I think I'm right in saying that as soon as a CHECK constraint >

[sqlite] Detecting multiple CHECK failures

I'm a great believer in using CHECK constraints to do as much validation as possible within the database rather than code it in my application. However, I think I'm right in saying that as soon as a CHECK constraint fails, an error is returned to my application so no other CHECK constraints are

Re: [sqlite] Full text search: Regression on 3.8.7 (or using incorrectly?)

On 10/08/2014 01:52 AM, Sohail Somani wrote: Figured it out: match terms should be "l0l* h4x*" NOT "*l0l* *h4x*", though it did work as expected with the older version. I'd suggest keeping the old behaviour unless there is a performance-based reason not to. On 2014-10-07, 2:49 PM, Sohail

Re: [sqlite] Full text search: Regression on 3.8.7 (or using incorrectly?)

Figured it out: match terms should be "l0l* h4x*" NOT "*l0l* *h4x*", though it did work as expected with the older version. I'd suggest keeping the old behaviour unless there is a performance-based reason not to. On 2014-10-07, 2:49 PM, Sohail Somani wrote: SELECT COUNT(*) FROM t_fts WHERE

[sqlite] Full text search: Regression on 3.8.7 (or using incorrectly?)

$ ./bin/sqlite3 --version 3.7.17 2013-05-20 00:56:22 118a3b35693b134d56ebd780123b7fd6f1497668 $ ./bin/sqlite3 -batch < /tmp/test.sql --- 1 --- 0 --- 2 --- 1 $ ./bin/sqlite3 --version 3.8.7 2014-09-30 19:04:41 5ce05757aac80b99c3b2141cd301809f8e28e661 /bin/sqlite3 -batch < /tmp/test.sql --- 1

[sqlite] HN has noticed "SQLite 3.8.7 is 50% faster than 3.7.17 "

https://news.ycombinator.com/item?id=8420274 -- Andreas Kupries Senior Tcl Developer Code to Cloud: Smarter, Safer, Faster™ F: 778.786.1133 andre...@activestate.com, http://www.activestate.com Learn about Stackato for Private PaaS: http://www.activestate.com/stackato 21'st Tcl/Tk Conference:

Re: [sqlite] Sqlite giving error on unique constraint

There are multiple insert statements and multiple tables. The insert statements look like below. params = versionName, versionNumber, updateFrom, metafileURL, patchURL, sha1 conn.execute("INSERT INTO UpdateFrom VALUES(?,?,?,?,?,?)", params) and the table looks like below. CREATE TABLE

Re: [sqlite] Is there equivalent to MySQL IF() function?

On Tue, 7 Oct 2014 12:15:09 +0300 "Tony Papadimitriou" wrote: > Is there any an equivalent function to the MySQL > IF(condition,true_expr,false_expr) function? > > For example, SELECT AGE,IF(AGE < 3,"BABY",IF(AGE < > 18,"CHILD","ADULT")); > > If not, please add to wish list :)

Re: [sqlite] UTF support

So, I guess it is technically not allowed to encode 11 bit unicode characters as 16. the greek characters are 0x3XX which is 10 bits... I checked what WideCharToMultiByte was doing and found it was using 11 bit encodings... fixed my encoder to use an appropriate size for what's required, added 11

[sqlite] CONFIG_SERIALIED superset of CONFIG_MULITHREAD?

Hello. I'm wondering if CONFIG_SERIALIZED is a superset of CONFIG_MULITHREAD, recursive mutex wise. I imagine MULTITHREAD is turning on mutexes to protect the pager and other "low-level" execution stuff, and SERIALIZED is turning on more mutexes to protect stuff hanging off the connection,

Re: [sqlite] passing error messages to pysqlite

On Sat, 04 Oct 2014 10:44:20 -0400, Mark Halegua wrote: > self.cdata.execute('insert into publishers(publisher_name, >remarks) values("test", "remarks");') Are you sure this is correct? In SQL, string literals are delimited by single quotes. Double

Re: [sqlite] UTF support

Did find sqlite3_prepare16_v2; this allows the replace to work without error; but the result from the select to read it back isn't the same as what I put in... Still trying to figure out the differences... going to implement WideCharToMultiByte as conversion too to see what differences are

Re: [sqlite] UTF support

Hello J, string_tsTest; int nLengthNeeded = WideCharToMultiByte(CP_UTF8, 0, pszWide,nLength, 0, 0, 0, 0); if( !nLengthNeeded ) { ASSERT(0); return(E_ABORT); } sTest.resize(nLengthNeeded + 16);

Re: [sqlite] UTF support

On Tue, Oct 7, 2014 at 5:39 AM, Richard Hipp wrote: > On Tue, Oct 7, 2014 at 12:06 AM, J Decker wrote: > > > I saw a few things go by about unicode... and understand that it should > > just work to store the data as characters... > > > > I'm getting a

Re: [sqlite] UTF support

On Tue, Oct 7, 2014 at 8:50 AM, J Decker wrote: > On Tue, Oct 7, 2014 at 5:02 AM, Richard Hipp wrote: > > > On Tue, Oct 7, 2014 at 12:06 AM, J Decker wrote: > > > > > I saw a few things go by about unicode... and understand that it should >

Re: [sqlite] UTF support

On Tue, Oct 7, 2014 at 5:02 AM, Richard Hipp wrote: > On Tue, Oct 7, 2014 at 12:06 AM, J Decker wrote: > > > I saw a few things go by about unicode... and understand that it should > > just work to store the data as characters... > > > > I'm getting a

Re: [sqlite] UTF support

On Tue, Oct 7, 2014 at 12:06 AM, J Decker wrote: > I saw a few things go by about unicode... and understand that it should > just work to store the data as characters... > > I'm getting a unrecognized token... and think this page isn't right... > I was playing with greek

Re: [sqlite] UTF support

"J Decker" wrote... I saw a few things go by about unicode... and understand that it should just work to store the data as characters... I'm getting a unrecognized token... and think this page isn't right... I was playing with greek translation of 'mary had a little lamb'

Re: [sqlite] UTF support

On Tue, Oct 7, 2014 at 12:06 AM, J Decker wrote: > I saw a few things go by about unicode... and understand that it should > just work to store the data as characters... > > I'm getting a unrecognized token... and think this page isn't right... > I was playing with greek

Re: [sqlite] passing error messages to pysqlite

Is anyone able to tell me why I'm having this commit problem in python? Mark On Saturday, October 04, 2014 11:43:41 AM Mark Halegua wrote: > Just tried the same code on a different system, using sqlite3 3.8.2 > > Same result. > > Mark > > On Saturday, October 04, 2014 10:44:20 AM Mark

Re: [sqlite] Is there equivalent to MySQL IF() function?

Totally agree. And this is exactly why my natural first reaction was to expect a syntax error, because in my mind you can't compare a non-boolean expr to a boolean expr. I need to start C-ing everything differently. Not easy... :) Thank you (and I didn't imply there was malice, ... just a

Re: [sqlite] Is there equivalent to MySQL IF() function?

On 2014/10/07 13:20, Tony Papadimitriou wrote: Well, it is exactly because I understand the difference between a boolean expression and a non-boolean expression, along with a bit misleading documentation, that I got confused. It is usually those who are used to only the C-like treatment of a

Re: [sqlite] Is there equivalent to MySQL IF() function?

Well, it is exactly because I understand the difference between a boolean expression and a non-boolean expression, along with a bit misleading documentation, that I got confused. It is usually those who are used to only the C-like treatment of a boolean result as being equivalent to an

Re: [sqlite] Is there equivalent to MySQL IF() function?

On 2014/10/07 12:13, Tony Papadimitriou wrote: Thanks. It seems quite a bit more verbose than the IF() function, but it works, so I can't complain. As an aside - It's not only a little more verbose, it also happens to be the way prescribed by the SQL standard and to my knowledge MySQL,

Re: [sqlite] Is there equivalent to MySQL IF() function?

On 2014/10/07 12:42, Tony Papadimitriou wrote: You're right, ... but in that page it says: The only difference between the following two CASE expressions is that the x expression is evaluated exactly once in the first example but might be evaluated multiple times in the second: CASE x

Re: [sqlite] Is there equivalent to MySQL IF() function?

OK, I think I managed to figure out what it means by reading a little more about the two CASE cases. 'CASE expr WHEN' compares the base expr with the WHEN expr, whereas 'CASE WHEN' compares the WHEN expr to true So, the difference is more than just how many times 'x' is evaluated. (Maybe the

Re: [sqlite] Is there equivalent to MySQL IF() function?

You're right, ... but in that page it says: The only difference between the following two CASE expressions is that the x expression is evaluated exactly once in the first example but might be evaluated multiple times in the second: CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END

Re: [sqlite] Is there equivalent to MySQL IF() function?

On Tue, Oct 7, 2014 at 1:13 PM, Tony Papadimitriou wrote: > As you can see, the second select gives unexpected output, and according > to the syntax diagram it's not supposed to be a valid variation of the CASE > statement. Is that normal?

Re: [sqlite] Is there equivalent to MySQL IF() function?

Thanks. It seems quite a bit more verbose than the IF() function, but it works, so I can't complain. I played with it a bit, and I did notice there are two forms: CASE expr WHEN and CASE WHEN When I accidentally put an expr (CASE expr WHEN) *AND* explicit WHEN conditions rather than

Re: [sqlite] Is there equivalent to MySQL IF() function?

SELECT CASE WHEN (AGE<3) THEN 'Baby' WHEN (AGE BETWEEN 4 AND 18) THEN 'Child' ELSE 'Adult' END On 2014/10/07 11:15, Tony Papadimitriou wrote: Hi all, Is there any an equivalent function to the MySQL IF(condition,true_expr,false_expr) function? For example, SELECT AGE,IF(AGE <

[sqlite] Is there equivalent to MySQL IF() function?

Hi all, Is there any an equivalent function to the MySQL IF(condition,true_expr,false_expr) function? For example, SELECT AGE,IF(AGE < 3,"BABY",IF(AGE < 18,"CHILD","ADULT")); If not, please add to wish list :) TIA ___ sqlite-users mailing list

Re: [sqlite] Long lived prepared statements

> > By the way, the last call to fetchrow_array() (that returns > > 'undef') implicitly calls finish() internally. > I assume this is the same finish of the handle obtained from my > prepare. But because I have a reference to the handle holed away it > is not really "finished" and that is why I