Re: [sqlite] Enabling MMAP in Android

[advancenOO]

OK, I know it is not safe when use mmap in Android 7 fuse. 
And do you mean the bug is in fuse kernel framework as you mentioned Android
FUSE 
filesystem driver? Which corresponding to Linux 3.10?
 
By the way, do you have any possible repro steps especially in Android
Sqlite?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can a trigger recursively update a table?

[Shane Dev]

Thanks for your answers, I missed that part of the create trigger
documentation.

Can we conclude there is no single CTE or other SQL statement which can
update a branch of the tree starting with a flexibly specified node?

i.e. I have to "hard-code" the starting node (top of branch) in my CTE like
this -

with recursive cte(lev, id, parent, status) as (select 0, id, parent, status
from hierarchy
-- change the start node in the line below
where id=3
union all select lev+1, h.id, h.parent, h.status from hierarchy as h, cte
where h.parent=cte.id) update
hierarchy set status=null where id in (select id from cte);

On 13 December 2017 at 00:59, J. King  wrote:

> CTEs cannot be used inside triggers for UPDATE statements. See near the
> bottom of:
> 
>
> On December 12, 2017 6:44:35 PM EST, Shane Dev 
> wrote:
> >Hi,
> >
> >I have a hierarchical table -
> >
> >sqlite> .sch hierarchy
> >CREATE TABLE hierarchy(id integer primary key, parent references
> >hierarchy,
> >descrip text, status text);
> >
> >with some entries -
> >
> >sqlite> select * from hierarchy;
> >id  parent  descrip status
> >1   rootopen
> >2   1   branch1 open
> >3   1   branch2 open
> >4   3   branch22open
> >5   4   branch222   open
> >6   1   branch3 open
> >
> >I can clear the status field of the second branch (i.e id = 3, 4 and 5)
> >with -
> >
> >with recursive cte(lev, id, parent, status) as (select 0, id, parent,
> >status from hierarchy where id=3 union all select lev+1, h.id,
> >h.parent,
> >h.status from hierarchy as h, cte where h.parent=cte.id) update
> >hierarchy
> >set status=null where id in (select id from cte);
> >
> >sqlite> select * from hierarchy;
> >id  parent  descrip status
> >1   rootopen
> >2   1   branch1 open
> >3   1   branch2
> >4   3   branch22
> >5   4   branch222
> >6   1   branch3 open
> >
> >However, if I try to create a trigger with this statement -
> >
> >sqlite> create view vhierarchy as select * from hierarchy;
> >sqlite> create trigger thierarchy instead of delete on vhierarchy begin
> >with recursive cte(lev, id, parent, status) as (select 0, id, parent,
> >status from hierarchy where id=old.id union all select lev+1, h.id,
> >h.parent, h.status from hierarchy as h, cte where h.parent=cte.id)
> >update
> >hierarchy set status=null where id in (select id from cte); end;
> >
> >I get an error -
> >
> >Error: near "update": syntax error
> >
> >Can anyone see why?
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
> ___
> 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] [EXTERNAL] values ?

[Hick Gunter]

You have to SELECT that

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Mark Wagner
Gesendet: Dienstag, 12. Dezember 2017 19:35
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] values ?

My reading of https://sqlite.org/syntax/select-core.html makes me think that I 
should be able to issue something like values('foo'); and get a row with a 
single column whose value is 'foo'.  But I get a syntax error.

Probably obvious to the right people but what am I missing?

sqlite> values('foo', 'bar');
Error: near "values": syntax error
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Enabling MMAP in Android

[advancenOO]

Yeah. Thanks for your suggestion about -DSQLITE_ENABLE_BATCH_ATOMIC_WRITE.
But the version of my sqlite is 3.16.2, so I guess I may use it at a later
time int the future.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Enabling MMAP in Android

[advancenOO]

Thanks for the notification about the bugs in MacOS.
So the most important reason for disable SQLITE_MMAP_READWRITE is the bugs
in OS, not the stray pointer in upper applications. Is it right?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Enabling MMAP in Android

[Howard Chu]

J Decker wrote:

On Tue, Dec 12, 2017 at 4:35 PM, Howard Chu  wrote:


Martin Raiber wrote:


On 12.12.2017 19:47 Simon Slavin wrote:


On 12 Dec 2017, at 6:27pm, Jens Alfke  wrote:

On Dec 12, 2017, at 5:46 AM, Simon Slavin  wrote:



Before you answer that question, you should know that both Windows and
macOS have been proved to have serious bugs in their memory mapping code.


This has been brought up several times recently, but I’ve never seen
any details given about exactly what was wrong with macOS’s mmap
implementation. Does anyone have a pointer to authoritative information
about this?


See this thread:



This is the thread which led to memory mapping being disabled for
writing on macOS, as discussed here:





There might also be a mmap bug in the Android 7.0 fuse layer:
https://www.mail-archive.com/openldap-its@openldap.org/msg10970.html



There is definitely a bug in Android 7 fuse/mmap. The bug is definitely
not present when bypassing fuse, but only rooted devices can bypass...

My experience was in majority only rooted devices could use fuse.  since

/dev/fuse was rw--- .  (although not all.)


The /sdcard partition is always mounted through fuse because it's a vfat/exfat 
filesystem that doesn't support owner/permission bits, and the fuse driver 
imposes the Android security model on top of it. Many android devices no 
longer include a physical SDcard slot, but still have an internal storage 
partition that's labeled /sdcard and it still behaves this way.


--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Enabling MMAP in Android

[J Decker]

On Tue, Dec 12, 2017 at 4:35 PM, Howard Chu  wrote:

> Martin Raiber wrote:
>
>> On 12.12.2017 19:47 Simon Slavin wrote:
>>
>>> On 12 Dec 2017, at 6:27pm, Jens Alfke  wrote:
>>>
>>> On Dec 12, 2017, at 5:46 AM, Simon Slavin  wrote:

> Before you answer that question, you should know that both Windows and
> macOS have been proved to have serious bugs in their memory mapping code.
>
 This has been brought up several times recently, but I’ve never seen
 any details given about exactly what was wrong with macOS’s mmap
 implementation. Does anyone have a pointer to authoritative information
 about this?

>>> See this thread:
>>>
>>> >> itable-catalog-corruption-td85620.html>
>>>
>>> This is the thread which led to memory mapping being disabled for
>>> writing on macOS, as discussed here:
>>>
>>> >> and-PRAGMA-fullfsync-on-macOS-td95366i20.html>
>>>
>>
>> There might also be a mmap bug in the Android 7.0 fuse layer:
>> https://www.mail-archive.com/openldap-its@openldap.org/msg10970.html
>>
>
> There is definitely a bug in Android 7 fuse/mmap. The bug is definitely
> not present when bypassing fuse, but only rooted devices can bypass...
>
> My experience was in majority only rooted devices could use fuse.  since
/dev/fuse was rw--- .  (although not all.)

> --
>   -- Howard Chu
>   CTO, Symas Corp.   http://www.symas.com
>   Director, Highland Sun http://highlandsun.com/hyc/
>   Chief Architect, OpenLDAP  http://www.openldap.org/project/
> ___
> 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] Enabling MMAP in Android

[Howard Chu]

Martin Raiber wrote:

On 12.12.2017 19:47 Simon Slavin wrote:

On 12 Dec 2017, at 6:27pm, Jens Alfke  wrote:


On Dec 12, 2017, at 5:46 AM, Simon Slavin  wrote:

Before you answer that question, you should know that both Windows and macOS 
have been proved to have serious bugs in their memory mapping code.

This has been brought up several times recently, but I’ve never seen any 
details given about exactly what was wrong with macOS’s mmap implementation. 
Does anyone have a pointer to authoritative information about this?

See this thread:



This is the thread which led to memory mapping being disabled for writing on 
macOS, as discussed here:




There might also be a mmap bug in the Android 7.0 fuse layer:
https://www.mail-archive.com/openldap-its@openldap.org/msg10970.html


There is definitely a bug in Android 7 fuse/mmap. The bug is definitely not 
present when bypassing fuse, but only rooted devices can bypass...


--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Enabling MMAP in Android

[Howard Chu]

Jens Alfke wrote:


I’m skeptical about mmap being broken on Macs, since there are other production 
databases such as Realm* that use it heavily. (Though I am not sure whether 
Realm uses writeable mappings.)

—Jens

* and LMDB, but I am not sure if LMDB is in use on macOS.


LMDB is in common use on MacOS, no issues have been reported. On iOS there's 
this nagging problem that the virtual address space is still limited to 4GB, 
even on 64bit systems.


--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can a trigger recursively update a table?

[Igor Tandetnik]

On 12/12/2017 6:44 PM, Shane Dev wrote:

However, if I try to create a trigger with this statement -


http://www.sqlite.org/lang_with.html
"""
Limitations And Caveats
- The WITH clause cannot be used within a CREATE TRIGGER.
"""

--
Igor Tandetnik

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

Re: [sqlite] Can a trigger recursively update a table?

[J. King]

CTEs cannot be used inside triggers for UPDATE statements. See near the bottom 
of:


On December 12, 2017 6:44:35 PM EST, Shane Dev  wrote:
>Hi,
>
>I have a hierarchical table -
>
>sqlite> .sch hierarchy
>CREATE TABLE hierarchy(id integer primary key, parent references
>hierarchy,
>descrip text, status text);
>
>with some entries -
>
>sqlite> select * from hierarchy;
>id  parent  descrip status
>1   rootopen
>2   1   branch1 open
>3   1   branch2 open
>4   3   branch22open
>5   4   branch222   open
>6   1   branch3 open
>
>I can clear the status field of the second branch (i.e id = 3, 4 and 5)
>with -
>
>with recursive cte(lev, id, parent, status) as (select 0, id, parent,
>status from hierarchy where id=3 union all select lev+1, h.id,
>h.parent,
>h.status from hierarchy as h, cte where h.parent=cte.id) update
>hierarchy
>set status=null where id in (select id from cte);
>
>sqlite> select * from hierarchy;
>id  parent  descrip status
>1   rootopen
>2   1   branch1 open
>3   1   branch2
>4   3   branch22
>5   4   branch222
>6   1   branch3 open
>
>However, if I try to create a trigger with this statement -
>
>sqlite> create view vhierarchy as select * from hierarchy;
>sqlite> create trigger thierarchy instead of delete on vhierarchy begin
>with recursive cte(lev, id, parent, status) as (select 0, id, parent,
>status from hierarchy where id=old.id union all select lev+1, h.id,
>h.parent, h.status from hierarchy as h, cte where h.parent=cte.id)
>update
>hierarchy set status=null where id in (select id from cte); end;
>
>I get an error -
>
>Error: near "update": syntax error
>
>Can anyone see why?
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can a trigger recursively update a table?

[Shane Dev]

Hi,

I have a hierarchical table -

sqlite> .sch hierarchy
CREATE TABLE hierarchy(id integer primary key, parent references hierarchy,
descrip text, status text);

with some entries -

sqlite> select * from hierarchy;
id  parent  descrip status
1   rootopen
2   1   branch1 open
3   1   branch2 open
4   3   branch22open
5   4   branch222   open
6   1   branch3 open

I can clear the status field of the second branch (i.e id = 3, 4 and 5)
with -

with recursive cte(lev, id, parent, status) as (select 0, id, parent,
status from hierarchy where id=3 union all select lev+1, h.id, h.parent,
h.status from hierarchy as h, cte where h.parent=cte.id) update hierarchy
set status=null where id in (select id from cte);

sqlite> select * from hierarchy;
id  parent  descrip status
1   rootopen
2   1   branch1 open
3   1   branch2
4   3   branch22
5   4   branch222
6   1   branch3 open

However, if I try to create a trigger with this statement -

sqlite> create view vhierarchy as select * from hierarchy;
sqlite> create trigger thierarchy instead of delete on vhierarchy begin
with recursive cte(lev, id, parent, status) as (select 0, id, parent,
status from hierarchy where id=old.id union all select lev+1, h.id,
h.parent, h.status from hierarchy as h, cte where h.parent=cte.id) update
hierarchy set status=null where id in (select id from cte); end;

I get an error -

Error: near "update": syntax error

Can anyone see why?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] values ?

[David Raymond]

Normally I think you'd just loop through each on their own, since they don't 
have any dependencies on each other.

select 1 from foo where id = ?;--process if no records returned

But you could also do

with inputValues (inputValue) as (values (?), (?), (?))
select inputValue from inputValues
where not exists (select 1 from foo where id = inputValue);

But there you have to change the query itself depending on how many input 
values you have, etc.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Mark Wagner
Sent: Tuesday, December 12, 2017 2:26 PM
To: SQLite mailing list
Subject: Re: [sqlite] values ?

My use case was this.  For some given input, find which of those input
values do not have corresponding rows in a given table.

In other words something like this (but values seemed easier).


select '1' as x union select '2' as x union select '3 as x where x not in
(select id from foo);

Picture the 1,2,3 as some form of input which requires further processing
if we don't have rows for them.

Perhaps there's a better way to do this that I'm not thinking of.


On Tue, Dec 12, 2017 at 11:07 AM, Stephen Chrzanowski 
wrote:

> Nifty... but... With no option for "where" or "order by", where would this
> come in useful?
>
> On Tue, Dec 12, 2017 at 1:48 PM, Mark Wagner  wrote:
>
> > Argh.  Yes, I was on 3.8.2.  Thanks!
> >
> > On Tue, Dec 12, 2017 at 10:45 AM, Richard Hipp  wrote:
> >
> > > On 12/12/17, Mark Wagner  wrote:
> > > > My reading of https://sqlite.org/syntax/select-core.html makes me
> > think
> > > > that I should be able to issue something like values('foo'); and get
> a
> > > row
> > > > with a single column whose value is 'foo'.  But I get a syntax error.
> > > >
> > > > Probably obvious to the right people but what am I missing?
> > >
> > > It probably means you are using an older version of SQLite.  The
> > > syntax you describe as introduced in version 3.8.3 (2014-02-03).
> > > --
> > > 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
>
___
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] values ?

[Mark Wagner]

My use case was this.  For some given input, find which of those input
values do not have corresponding rows in a given table.

In other words something like this (but values seemed easier).


select '1' as x union select '2' as x union select '3 as x where x not in
(select id from foo);

Picture the 1,2,3 as some form of input which requires further processing
if we don't have rows for them.

Perhaps there's a better way to do this that I'm not thinking of.


On Tue, Dec 12, 2017 at 11:07 AM, Stephen Chrzanowski 
wrote:

> Nifty... but... With no option for "where" or "order by", where would this
> come in useful?
>
> On Tue, Dec 12, 2017 at 1:48 PM, Mark Wagner  wrote:
>
> > Argh.  Yes, I was on 3.8.2.  Thanks!
> >
> > On Tue, Dec 12, 2017 at 10:45 AM, Richard Hipp  wrote:
> >
> > > On 12/12/17, Mark Wagner  wrote:
> > > > My reading of https://sqlite.org/syntax/select-core.html makes me
> > think
> > > > that I should be able to issue something like values('foo'); and get
> a
> > > row
> > > > with a single column whose value is 'foo'.  But I get a syntax error.
> > > >
> > > > Probably obvious to the right people but what am I missing?
> > >
> > > It probably means you are using an older version of SQLite.  The
> > > syntax you describe as introduced in version 3.8.3 (2014-02-03).
> > > --
> > > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Enabling MMAP in Android

[Jens Alfke]

> On Dec 12, 2017, at 10:47 AM, Simon Slavin  wrote:
> 
> See this thread:
> 
>   
> >

That thread reported SQLite database corruption when writeable mmap was 
enabled, but didn't identify what caused it. There was also the following 
observation by Török Edwin, which no one ever replied to:

>> I notice that SQLite doesn't use msync, and msync(2) says 'Without use of 
>> this call there is no guarantee that changes are written back before 
>> munmap(2) is called'. 
>> Is f(data)sync enough to ensure changed mmap pages are written to the disk? 


That sounds pretty suspicious to me; maybe the problem was just that sqlite 
needed to call msync?

> This is the thread which led to memory mapping being disabled for writing on 
> macOS, as discussed here:
> 
>   
> >

Yup, and I was heavily involved in that thread :) Again, there was only 
circumstantial evidence that mmap was causing problems; no one seems to have 
gotten to the bottom of it.

I’m skeptical about mmap being broken on Macs, since there are other production 
databases such as Realm* that use it heavily. (Though I am not sure whether 
Realm uses writeable mappings.)

—Jens

* and LMDB, but I am not sure if LMDB is in use on macOS.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] values ?

[Stephen Chrzanowski]

Nifty... but... With no option for "where" or "order by", where would this
come in useful?

On Tue, Dec 12, 2017 at 1:48 PM, Mark Wagner  wrote:

> Argh.  Yes, I was on 3.8.2.  Thanks!
>
> On Tue, Dec 12, 2017 at 10:45 AM, Richard Hipp  wrote:
>
> > On 12/12/17, Mark Wagner  wrote:
> > > My reading of https://sqlite.org/syntax/select-core.html makes me
> think
> > > that I should be able to issue something like values('foo'); and get a
> > row
> > > with a single column whose value is 'foo'.  But I get a syntax error.
> > >
> > > Probably obvious to the right people but what am I missing?
> >
> > It probably means you are using an older version of SQLite.  The
> > syntax you describe as introduced in version 3.8.3 (2014-02-03).
> > --
> > 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] Enabling MMAP in Android

[Martin Raiber]

On 12.12.2017 19:47 Simon Slavin wrote:
> On 12 Dec 2017, at 6:27pm, Jens Alfke  wrote:
>
>> On Dec 12, 2017, at 5:46 AM, Simon Slavin  wrote:
>>> Before you answer that question, you should know that both Windows and 
>>> macOS have been proved to have serious bugs in their memory mapping code.
>> This has been brought up several times recently, but I’ve never seen any 
>> details given about exactly what was wrong with macOS’s mmap implementation. 
>> Does anyone have a pointer to authoritative information about this?
> See this thread:
>
> 
>
> This is the thread which led to memory mapping being disabled for writing on 
> macOS, as discussed here:
>
> 

There might also be a mmap bug in the Android 7.0 fuse layer:
https://www.mail-archive.com/openldap-its@openldap.org/msg10970.html


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

Re: [sqlite] values ?

[Mark Wagner]

Argh.  Yes, I was on 3.8.2.  Thanks!

On Tue, Dec 12, 2017 at 10:45 AM, Richard Hipp  wrote:

> On 12/12/17, Mark Wagner  wrote:
> > My reading of https://sqlite.org/syntax/select-core.html makes me think
> > that I should be able to issue something like values('foo'); and get a
> row
> > with a single column whose value is 'foo'.  But I get a syntax error.
> >
> > Probably obvious to the right people but what am I missing?
>
> It probably means you are using an older version of SQLite.  The
> syntax you describe as introduced in version 3.8.3 (2014-02-03).
> --
> 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] Enabling MMAP in Android

[Simon Slavin]

On 12 Dec 2017, at 6:27pm, Jens Alfke  wrote:

> On Dec 12, 2017, at 5:46 AM, Simon Slavin  wrote:

>> Before you answer that question, you should know that both Windows and macOS 
>> have been proved to have serious bugs in their memory mapping code.
> 
> This has been brought up several times recently, but I’ve never seen any 
> details given about exactly what was wrong with macOS’s mmap implementation. 
> Does anyone have a pointer to authoritative information about this?

See this thread:



This is the thread which led to memory mapping being disabled for writing on 
macOS, as discussed here:



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

Re: [sqlite] values ?

[Richard Hipp]

On 12/12/17, Mark Wagner  wrote:
> My reading of https://sqlite.org/syntax/select-core.html makes me think
> that I should be able to issue something like values('foo'); and get a row
> with a single column whose value is 'foo'.  But I get a syntax error.
>
> Probably obvious to the right people but what am I missing?

It probably means you are using an older version of SQLite.  The
syntax you describe as introduced in version 3.8.3 (2014-02-03).
-- 
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] values ?

[John McKown]

On Tue, Dec 12, 2017 at 12:34 PM, Mark Wagner  wrote:

> My reading of https://sqlite.org/syntax/select-core.html makes me think
> that I should be able to issue something like values('foo'); and get a row
> with a single column whose value is 'foo'.  But I get a syntax error.
>
> Probably obvious to the right people but what am I missing?
>
> sqlite> values('foo', 'bar');
> Error: near "values": syntax error
>

​Works for me too:

$ sqlite3
SQLite version 3.14.2 2016-09-12 18:50:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> values(1,2)
   ...> ;
1|2
sqlite> values('foo','bar');
foo|bar
sqlite>
​
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] values ?

[Bart Smissaert]

Works OK here.
What is your SQLite version?

RBS

On Tue, Dec 12, 2017 at 6:34 PM, Mark Wagner  wrote:

> My reading of https://sqlite.org/syntax/select-core.html makes me think
> that I should be able to issue something like values('foo'); and get a row
> with a single column whose value is 'foo'.  But I get a syntax error.
>
> Probably obvious to the right people but what am I missing?
>
> sqlite> values('foo', 'bar');
> Error: near "values": syntax error
> ___
> 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] values ?

[Mark Wagner]

My reading of https://sqlite.org/syntax/select-core.html makes me think
that I should be able to issue something like values('foo'); and get a row
with a single column whose value is 'foo'.  But I get a syntax error.

Probably obvious to the right people but what am I missing?

sqlite> values('foo', 'bar');
Error: near "values": syntax error
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Enabling MMAP in Android

[Jens Alfke]

> On Dec 12, 2017, at 5:46 AM, Simon Slavin  wrote:
> 
> The stray pointer or array overrun is not in the SQLite code.  It’s in the 
> implementation of memory mapping built into the OS.  So you need to ask 
> yourself whether you trust your OS.

This argument (which is a standard one against using writeable mmap) is about 
_application_ code issuing stray writes. This wasn’t a topic about OS bugs.

I’m not sure I buy the argument anyway. It’s already possible for app code to 
write into SQLite’s buffer cache, which can then be written back to disk, 
corrupting the file. And the buffer cache is located in the malloc heap, which 
is close to application heap blocks, while a mmap’ed database file is likely to 
be somewhere way far away in address space.

> Before you answer that question, you should know that both Windows and macOS 
> have been proved to have serious bugs in their memory mapping code.

This has been brought up several times recently, but I’ve never seen any 
details given about exactly what was wrong with macOS’s mmap implementation. 
Does anyone have a pointer to authoritative information about this?

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

Re: [sqlite] Seasonal syntax

[Stephen Chrzanowski]

You had to do that just beclause

On Tue, Dec 12, 2017 at 12:24 PM, Simon Slavin  wrote:

> Some SQL terminology:
>
>   Selection Clause: WHERE 
>Sort Clause: ORDER BY 
> Sublist Clause: LIMIT  OFFSET 
> Subsort Clause: GROUP BY  HAVING 
>   Santa Clause: SELECT name,hobbies,address FROM people WHERE
> behaviour='nice’
>
> Season’s greetings and best wishes to all subscribers.
>
> 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] Seasonal syntax

[Simon Slavin]

Some SQL terminology:

  Selection Clause: WHERE 
   Sort Clause: ORDER BY 
Sublist Clause: LIMIT  OFFSET 
Subsort Clause: GROUP BY  HAVING 
  Santa Clause: SELECT name,hobbies,address FROM people WHERE 
behaviour='nice’

Season’s greetings and best wishes to all subscribers.

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

Re: [sqlite] extracting domain names from website addresses efficiently

[Teg]

Hello Peter,

Monday, December 11, 2017, 9:16:27 AM, you wrote:

PDS> This seems like a job for regular expressions.
PDS>  

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

I'm  with  you. This seems like using the wrong tool for the job. Sure
it can work but it's probably not the most efficient way. 

-- 
 Tegmailto:t...@djii.com

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

Re: [sqlite] Enabling MMAP in Android

[Richard Hipp]

On 12/12/17, advancenOO  wrote:
> Um, stray pointer or array overrun in upper applications will be treated as
> BUG in my system. And I believe all these bugs could be fixed before using
> sqlite.

You have a very optimistic view of application software :-)

Look - if you are interested in maximizing performance, you need to
use the latest Linux kernel with the latest F2FS filesystem, then
compile SQLite with the -DSQLITE_ENABLE_BATCH_ATOMIC_WRITE.  That
combination will get you way more performance boost than you will ever
find by playing games with mmap().
-- 
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] Enabling MMAP in Android

[Simon Slavin]

On 12 Dec 2017, at 1:32pm, advancenOO  wrote:

> Um, stray pointer or array overrun in upper applications will be treated as
> BUG in my system. And I believe all these bugs could be fixed before using
> sqlite.
> So can I safely enable SQLITE_MMAP_READWRITE directly if I can ignore the
> possibility of the BUG?

The stray pointer or array overrun is not in the SQLite code.  It’s in the 
implementation of memory mapping built into the OS.  So you need to ask 
yourself whether you trust your OS.

Before you answer that question, you should know that both Windows and macOS 
have been proved to have serious bugs in their memory mapping code.

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

Re: [sqlite] Enabling MMAP in Android

[advancenOO]

I am a developer of system, not application. So I have nothing to do with the
SQL or indices even though I know these may cut orders of magnitude from
execution times. 

And that is why I use speedtest1.c of Sqlite to evaluate the performance of
my system.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Enabling MMAP in Android

[advancenOO]

Um, stray pointer or array overrun in upper applications will be treated as
BUG in my system. And I believe all these bugs could be fixed before using
sqlite.
So can I safely enable SQLITE_MMAP_READWRITE directly if I can ignore the
possibility of the BUG?

But I notice that Sqlite may not call msync() in mmap, calls fsync instead. 
So I am wondering if it is safe to use SQLITE_MMAP_READWRITE to write to
mmapped memory as msync() is not called.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] extracting domain names from website addresses efficiently

[Klaus Maas]

Very interesting.

I must be very slow, because at first I did no get what this was about, 
although you fed it me on a spoon.


This is actually very straightforward. I find this easier to understand 
than recursive CTEs.


Thank you, Peter and IgorT, for helping me on my way to understand this 
powerful feature.


Still some way to go, but that is  part of the fun.


email signature Klaus Maas

On 2017-12-12 00:22, petern wrote:

Klaus.  The CTE manual with good examples is at
https://www.sqlite.org/lang_with.html
IgorT posted some good stuff about your problem using CTE.

FYI. TRIGGER is also recursive.  Could be more efficient if you have to
store them anyway:

CREATE TABLE domain(d TEXT);
CREATE TRIGGER domain_after_ins AFTER INSERT ON domain WHEN instr(NEW.d,'.')
   BEGIN INSERT INTO domain VALUES(substr(NEW.d,instr(NEW.d,'.')+1)); END;

PRAGMA recursive_triggers=1;
INSERT INTO domain VALUES ('a.b.c.d');
INSERT INTO domain VALUES('e.f.g.1.2');
SELECT * FROM domain;
d
--
a.b.c.d
b.c.d
c.d
d
e.f.g.1.2
f.g.1.2
g.1.2
1.2
2

Peter







On Mon, Dec 11, 2017 at 9:22 AM, Klaus Maas  wrote:


Thank you, Igor, for your solution.
I expected that I would be pointed to a recursive approach.
Your solution is quite elegant in my opinion.
My problem, however, is that I have not yet understood recursive CTE's.
Could you give me some pointers for good resources to learn about them?

Well, your code example is helping a lot in understanding them, although I
am still a long way off producing something similar myself without
assistance.

There is 1 item in the last where condition of which I do not understand
the purpose:
(instr(subdomain, '.') = 0 and subdomain = long)
This means that anything in the form of 'ftp://test/' would output the
string between the two delimiters (:// and /), in this case 'test'.
But that is not a domain name in the format domain.tld.
(I am working under the assumption that table links is cleaned up contains
valid links only with the protocol and :// prepended.)
Or am I missing something?.

Klaus


On 2017-12-11 14:59, Igor Tandetnik wrote:


Something like this (could likely be simplified further, but this should
give the idea):

with recursive subdomains as (
   SELECT substr(link, instr(link, '://')+3, instr(substr(link,
instr(link, '://')+3), '/')-1) AS long,
  substr(link, instr(link, '://')+3, instr(substr(link,
instr(link, '://')+3), '/')-1) as subdomain
   FROM links
union all
   select long, substr(subdomain, instr(subdomain, '.') + 1)
   from subdomains
   where instr(subdomain, '.') > 0)
select * from subdomains
where (instr(subdomain, '.') = 0 and subdomain = long) OR
   (instr(subdomain, '.') > 0 and instr(substr(subdomain,
instr(subdomain, '.') + 1), '.')=0);

The main point is to recursively build a table of all suffixes, then
select just the suffixes you want.


___
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