Re: [sqlite] Is there any way to close a connection without checkpointing the WAL?

[Simon Slavin]

On 5 Dec 2016, at 9:46pm, Scott Hess  wrote:

> I don't think so, that seems like it could result in corruption.
> Unless you mean something more like causing the OS to block all fsync
> calls on the filesystem and release them as a single uber-sync?

Yeah, that’s what I meant.  It would just cause the device to suspend all 
writing until you gave it another command which released it all.  Obviously 
it’d have to sync anyway if it ran out of caching space.

Seems like the sort of call a device driver might have.  And Chromium should be 
able to talk to its storage at that level.  But I don’t recall if I’ve ever 
seen it.  So I’m glad the development team has your back inside SQLite.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there any way to close a connection without checkpointing the WAL?

[Scott Hess]

On Mon, Dec 5, 2016 at 1:34 PM, Simon Slavin  wrote:
> On 5 Dec 2016, at 9:26pm, Scott Hess  wrote:
>> An obvious solution would be to simply not call sqlite3_close(),
>> though that has various other unfortunate side effects.
>
> Yeah.  Don’t do that, eh ?

:-).  OK, the biggest unfortunate effect is that you don't release the
various resources like memory and file descriptors, which in turn
means that you spend your time populating exception policies for
automated leak detectors and the like.  Also, it means you have to
have high confidence that you're skipping the close because of
shutdown rather than some other reason (like you're closing the
database before deleting it).  All of this can probably be ground
through eventually, but this kind of thing is likely to cause people
to wonder if the code is doing the right thing.

> What you’re actually trying to do is disable/delay fsync() for
> a time for a particular storage device.  Can that not be done
> at device-driver level ?  It would then affect all the programs
> writing "I just quit" to their log files too, which would be an
> additional benefit.

I don't think so, that seems like it could result in corruption.
Unless you mean something more like causing the OS to block all fsync
calls on the filesystem and release them as a single uber-sync?

-scott
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there any way to close a connection without checkpointing the WAL?

[Scott Hess]

On Mon, Dec 5, 2016 at 1:38 PM, Richard Hipp  wrote:
> On 12/5/16, Scott Hess  wrote:
>> Is there any clean way to request no WAL checkpoint on sqlite3_close()?
>
> sqlite3_db_config(SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE, db).  See
> https://www.sqlite.org/draft/c3ref/c_dbconfig_enable_fkey.html at the
> bottom.

Oh!  Now I wonder if I asked about this earlier :-).

Thanks,
scott
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there any way to close a connection without checkpointing the WAL?

[Richard Hipp]

On 12/5/16, Scott Hess  wrote:
>
> Is there any clean way to request no WAL checkpoint on sqlite3_close()?

sqlite3_db_config(SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE, db).  See
https://www.sqlite.org/draft/c3ref/c_dbconfig_enable_fkey.html at the
bottom.

-- 
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] Is there any way to close a connection without checkpointing the WAL?

[Simon Slavin]

On 5 Dec 2016, at 9:26pm, Scott Hess  wrote:

> An obvious solution would be to simply not call sqlite3_close(),
> though that has various other unfortunate side effects.

Yeah.  Don’t do that, eh ?

What you’re actually trying to do is disable/delay fsync() for a time for a 
particular storage device.  Can that not be done at device-driver level ?  It 
would then affect all the programs writing "I just quit" to their log files 
too, which would be an additional benefit.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is there any way to close a connection without checkpointing the WAL?

[Scott Hess]

At Chromium shutdown, various services desire to write data to their
SQLite databases, which results in a (small) thundering herd of
fsyncs, which makes shutdown slower than it could be.  Normally, one
could enable WAL mode to amortize the fsync cost across longer periods
than a single transaction, but as best I can tell, sqlite3_close()
requires the WAL checkpoint, so won't help.

Is there any clean way to request no WAL checkpoint on sqlite3_close()?

An obvious solution would be to simply not call sqlite3_close(),
though that has various other unfortunate side effects.

Thanks,
scott
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like operator

[Igor Tandetnik]

On 12/5/2016 3:43 PM, Don V Nielsen wrote:

Igor, I'm not sure if you gain anything from"length(lower(name))". Just
"length(name)" would suffice.


I'm guarding against various Unicode weirdnesses that could cause string 
length to change on case transformation. While SQLite only folds ASCII 
letters by default, it could be compiled with full ICU collation support.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like operator

[Don V Nielsen]

Igor, I'm not sure if you gain anything from"length(lower(name))". Just
"length(name)" would suffice.

On Mon, Dec 5, 2016 at 10:11 AM, Dominique Devienne 
wrote:

> On Mon, Dec 5, 2016 at 4:24 PM, Igor Tandetnik  wrote:
>
> > On 12/5/2016 10:19 AM, Igor Tandetnik wrote:
> >
> >> On 12/5/2016 7:30 AM, ravi.shan...@cellworksgroup.com wrote:
> >>
> >>> select name from employee table where name like '%Araya%' or name like
> >>> '%Amul%' or name like '%Aj%';
> >>>
> >>> Table - Employee
> >>>
> >>> Id | Name | age |
> >>> 1  | Arayan Kuma | 29  |
> >>> 2  | Amul Kanth  | 30  |
> >>> 3  | Ajay Kumar | 45  |
> >>>
> >>> I dont like to use may or conditions for pattern matching using like
> >>> operator.
> >>> Is there any other way I can workaround without using or condition in
> >>> like operator in sqlite.
> >>>
> >>
> >> WHERE length(replace(replace(replace(name, 'Araya', ''), 'Amul', ''),
> >> 'Aj', '')) != length(name)
> >>
> >
> > Actually, this is not quite the same: it's case-sensitive, whereas LIKE
> is
> > case-insensitive by default. To be equivalent, make it
> >
> > WHERE length(replace(replace(replace(lower(name), 'araya', ''), 'amul',
> > ''), 'aj', '')) != length(lower(name))
>
>
> Or use the pragma [1].
> Thanks, didn't realize/know LIKE was case-insensitive (for ASCII chars
> only) by default, in SQLite.
> Also made me double-check whether Oracle is case-sensitive or not (it is)
> [2]
>
> BTW, Igor: wow :)
> Not that I'd use that ever, but still, very clever! --DD
>
> [1] https://www.sqlite.org/pragma.html#pragma_case_sensitive_like
> [2]
> http://stackoverflow.com/questions/5391069/case-insensitive-searching-in-
> oracle
> ___
> 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] like operator

[Dominique Devienne]

On Mon, Dec 5, 2016 at 4:24 PM, Igor Tandetnik  wrote:

> On 12/5/2016 10:19 AM, Igor Tandetnik wrote:
>
>> On 12/5/2016 7:30 AM, ravi.shan...@cellworksgroup.com wrote:
>>
>>> select name from employee table where name like '%Araya%' or name like
>>> '%Amul%' or name like '%Aj%';
>>>
>>> Table - Employee
>>>
>>> Id | Name | age |
>>> 1  | Arayan Kuma | 29  |
>>> 2  | Amul Kanth  | 30  |
>>> 3  | Ajay Kumar | 45  |
>>>
>>> I dont like to use may or conditions for pattern matching using like
>>> operator.
>>> Is there any other way I can workaround without using or condition in
>>> like operator in sqlite.
>>>
>>
>> WHERE length(replace(replace(replace(name, 'Araya', ''), 'Amul', ''),
>> 'Aj', '')) != length(name)
>>
>
> Actually, this is not quite the same: it's case-sensitive, whereas LIKE is
> case-insensitive by default. To be equivalent, make it
>
> WHERE length(replace(replace(replace(lower(name), 'araya', ''), 'amul',
> ''), 'aj', '')) != length(lower(name))


Or use the pragma [1].
Thanks, didn't realize/know LIKE was case-insensitive (for ASCII chars
only) by default, in SQLite.
Also made me double-check whether Oracle is case-sensitive or not (it is)
[2]

BTW, Igor: wow :)
Not that I'd use that ever, but still, very clever! --DD

[1] https://www.sqlite.org/pragma.html#pragma_case_sensitive_like
[2]
http://stackoverflow.com/questions/5391069/case-insensitive-searching-in-oracle
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like operator

[Igor Tandetnik]

On 12/5/2016 10:19 AM, Igor Tandetnik wrote:

On 12/5/2016 7:30 AM, ravi.shan...@cellworksgroup.com wrote:

select name from employee table where name like '%Araya%' or name like
'%Amul%' or name like '%Aj%';

Table - Employee

Id | Name | age |
1  | Arayan Kuma | 29  |
2  | Amul Kanth  | 30  |
3  | Ajay Kumar | 45  |

I dont like to use may or conditions for pattern matching using like
operator.
Is there any other way I can workaround without using or condition in
like operator in sqlite.


WHERE length(replace(replace(replace(name, 'Araya', ''), 'Amul', ''),
'Aj', '')) != length(name)


Actually, this is not quite the same: it's case-sensitive, whereas LIKE 
is case-insensitive by default. To be equivalent, make it


WHERE length(replace(replace(replace(lower(name), 'araya', ''), 'amul', 
''), 'aj', '')) != length(lower(name))


--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like operator

[Igor Tandetnik]

On 12/5/2016 7:30 AM, 
ravi.shan...@cellworksgroup.com wrote:

select name from employee table where name like '%Araya%' or name like
'%Amul%' or name like '%Aj%';

Table - Employee

Id | Name | age |
1  | Arayan Kuma | 29  |
2  | Amul Kanth  | 30  |
3  | Ajay Kumar | 45  |

I dont like to use may or conditions for pattern matching using like
operator.
Is there any other way I can workaround without using or condition in
like operator in sqlite.


WHERE length(replace(replace(replace(name, 'Araya', ''), 'Amul', ''), 
'Aj', '')) != length(name)


--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with CASE in WHERE clause

[Olivier Mascia]

> Le 5 déc. 2016 à 12:36, Simon Slavin  a écrit :
> 
> And defining a column with a type of INTEGER is as close to BOOL as SQLite 
> gets.

It is even better when you take into account that SQLite internally has a 
storage type for 'integer value 0' and 'integer value 1' (types 8 and 9). For 
these, there is not even a 'value' stored, merely the type (this is true with 
SQLite > 3.3.0).

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] like operator

[ravi.shan...@cellworksgroup.com]

Hi Guys,

select name from employee table where name like '%Araya%' or name like 
'%Amul%' or name like '%Aj%';


Table - Employee

Id | Name | age |
1  | Arayan Kuma | 29  |
2  | Amul Kanth  | 30  |
3  | Ajay Kumar | 45  |

I dont like to use may or conditions for pattern matching using like 
operator.
Is there any other way I can workaround without using or condition in 
like operator in sqlite.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with CASE in WHERE clause

[Jean-Christophe Deschamps]

Hi Simon,

At 11:41 05/12/2016, you wrote:

On 5 Dec 2016, at 7:48am, Jean-Christophe Deschamps  
wrote:


> The choice of literals representing true and false is merely cosmetic.

You got me interested in that.  I had thought that "TRUE" and "FALSE" 
were reserved words in SQLite.  But I can find nothing to back that up, and


SELECT TRUE

returns an error.  It’s too late to add them now, of course, for 
backward compatibility reasons.  Someone may have a table column 
called "false".


Simon.


I'm as surprised as you about this, but it isn't the point I wanted to 
make.
BTW SQLite generally does a pretty good job at sorting out reserved 
words used as keywords vs. keywords used as schema names, but I always 
recommend that double quotes surround reserved names used as schema names.


I meant that we could call the truth of a boolean expression 
'STAINLESS' or 'RASPBERRY' instead of True and False, or 1 and 0. The 
symbols or literals we use for expressing a boolean value is just a 
convention. I wasn't talking especially about SQLite nor SQL (nor any 
language).


Look at the various incompatible conventions for expressing boolean 
values as "boolean-codepage nightmare" in that it reproduces, in the 
{false, true} domain, exactly the same issues codepages have created in 
character sets.


JcD 


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with CASE in WHERE clause

[Jean-Christophe Deschamps]

At 12:18 05/12/2016, you wrote:


From: Jean-Christophe Deschamps
Sent: Monday, December 05, 2016 9:48 AM
To: SQLite mailing list
Subject: Re: [sqlite] Problem with CASE in WHERE clause

>
> At 06:29 05/12/2016, you wrote:
> >My app supports sqlite3, SQL Server, and PostgreSQL.
> >
> >SQL Server has a ‘bitâ’ data type, which accepts 1/0 and
> >‘1â€Ëœ1’/’0’ as valid values.
> >
> >PostgreSQL stgreSQL has a ‘bool’ data type, which supports a 
var a variety of
> >values ­ TRUE, ‘t’, ‘true€˜true’, ‘yy’, 
‘yes’, ‘oes’, ‘on’,
> >‘1’ for true, and the opposithe opposites for false, but 
does not allow 1/0.

>
> All [three] engines should support (1=1) and (1=0) for true and false,
> respectively, as well as bare columnname as a boolean assertion, like
> Simon said: select ... where columnC and not columnF ...
>
> The choice of literals representing true and false is merely cosmetic.
>

So if I understand correctly, it makes sense to use ‘1’/’0’ to 
*set* the boolean value in a cross-database manner, but there are a 
variety of ways to test for it.

Frank


That's not how I see that. I'm no expert in SQL standards (note the 
plural!) but AFAICT the only sure and guaranteed portable way to SET a 
boolean value in SQL --regardless of whether a particular engine offers 
a BOOLEAN datatype and the values it consider valid to represent the 
logic valuations of what we call True and False-- is the result of a 
known true or known false expression, like (3=3) and (2=5).


As you've found, some engines accept something in
'TRUE', 'True', 'true', 't', 'T', 'Y, 'y', '1', 1, TRUE, True, true, ...
'FALSE', 'False', 'false', 'f', 'F', 'N', 'n', '0', 0, FALSE, False, 
false, ...


It may make sense to use '1' and '0' in your precise use case but I'm 
unsure of the portability. An expression yielding a known boolean 
result is forcibly valid and correctly interpreted.


And yes,
select ... where columnname = (1=1)
is mouthful for
select ... where columnname

I don't believe any engine would interpret the last statement as
select ... where columnname is not null

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with CASE in WHERE clause

[Simon Slavin]

On 5 Dec 2016, at 11:18am, Frank Millman  wrote:

> So if I understand correctly, it makes sense to use ‘1’/’0’ to *set* the 
> boolean value in a cross-database manner, but there are a variety of ways to 
> test for it.

Rather than the strings it would be better to use the integers 0 and 1.  This 
is how SQLite handles boolean values internally.  And they take less storage 
space and are faster to handle.

Although other values (e.g. 1.0 or the strings '1' or '1.0') may evaluate to 0 
or 1 under some circumstances, this relies on context, affinities, and other 
accidents of syntax.  But the integers always test as FALSE and TRUE.  And 
defining a column with a type of INTEGER is as close to BOOL as SQLite gets.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with CASE in WHERE clause

[Frank Millman]

From: Jean-Christophe Deschamps 
Sent: Monday, December 05, 2016 9:48 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Problem with CASE in WHERE clause

> 
> At 06:29 05/12/2016, you wrote:
> >My app supports sqlite3, SQL Server, and PostgreSQL.
> >
> >SQL Server has a ‘bit’ data type, which accepts 1/0 and 
> >‘1’/’0’ as valid values.
> >
> >PostgreSQL has a ‘bool’ data type, which supports a variety of 
> >values ­ TRUE, ‘t’, ‘true’, ‘yy’, ‘yes’, ‘on’, 
> >‘1’ for true, and the opposites for false, but does not allow 1/0.
> 
> All [three] engines should support (1=1) and (1=0) for true and false, 
> respectively, as well as bare columnname as a boolean assertion, like 
> Simon said: select ... where columnC and not columnF ...
> 
> The choice of literals representing true and false is merely cosmetic.
> 

So if I understand correctly, it makes sense to use ‘1’/’0’ to *set* the 
boolean value in a cross-database manner, but there are a variety of ways to 
test for it.
Frank


___
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] Problem with CASE in WHERE clause

[Simon Slavin]

On 5 Dec 2016, at 7:48am, Jean-Christophe Deschamps  wrote:

> The choice of literals representing true and false is merely cosmetic.

You got me interested in that.  I had thought that "TRUE" and "FALSE" were 
reserved words in SQLite.  But I can find nothing to back that up, and

SELECT TRUE

returns an error.  It’s too late to add them now, of course, for backward 
compatibility reasons.  Someone may have a table column called "false".

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users