[sqlite] Delete an existing module

[gwenn]

Hello,
Is it possible to delete a module and the associated client data ?
I tried to pass a NULL pointer as the third parameter of
sqlite3_create_module_v2,
sqlite3_create_module_v2(
  db,
  zName,
  NULL,
  NULL,
  NULL
);
but I got a SQLITE_MISUSE error.
Thanks.

[sqlite] Version 3.13.0 coming soon

[Graham Holden]

 Original message 
From: Richard Hipp  
Date: 03/05/2016  13:33  (GMT+00:00) 
To: SQLite mailing list  
Subject: Re: [sqlite] Version 3.13.0 coming soon 

> Yes.? Apparently that is the new standard for security on unix
systems.? Write lets you create new temp files.? Execute lets you
check to see if a particular file exists.? But because read is
disabled, one cannot do an "ls" on the temporary directory to see what
temp files other applications have created.

New? I remember FTP repositories from the mid 80s (funic, wustl?) where this 
scheme was common place for the "upload" directory so random visitors couldn't 
see recently uploaded files until the admins had had a chance to remove "dodgy" 
files and move genuine files to the correct place.

Graham

[sqlite] Make mmap_size dynamic?

[Howard Chu]

Mikael wrote:
> On Tuesday, 3 May 2016, Richard Hipp  > wrote:
>
>> On 5/2/16, Mikael  wrote:
>>>
>>> If I just force it on (by hacking the build script), as long as mmap_size
>>> always is 2^63, will Sqlite access the file via memory accesses only, and
>>> never using fread/fwrite which would lead to undefined behavior because
>> of
>>> the absence of a UBC?
>>>
>>
>> SQLite only reads using mmap.  Write always happen using the write()
>> (or pwrite()) system call.  So it will never work on OpenBSD.
>>
>> The database file is mmap-ed read-only.  This is so that stray
>> pointers in the application cannot trivially corrupt the database
>> file.
>
>
> Aha.
>
> What do you say about the idea of a memory access only mode, enabled
> with SQLITE_MMAP_NO_FILEIO on any OS, for the daring users who want that?

It's certainly an avenue to getting functionality in OpenBSD. In general, e.g. 
on Linux, there's not much to gain from it. There is a small performance gain 
when all of your data fits in RAM. There is a larger performance loss when 
your DB is larger than RAM - every time you touch a page to write to it, if 
it's not already memory-resident, the OS faults it in from disk. It's a wasted 
page fault if you were simply going to overwrite the entire page (which e.g. 
LMDB does). If you update pages in-place, instead of doing COW as LMDB does, 
it may or may not be a wash, I haven't measured that use case.

-- 
   -- 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] Version 3.13.0 coming soon

[no...@null.net]

On Tue May 03, 2016 at 08:33:30AM -0400, Richard Hipp wrote:
> On 5/2/16, Rolf Ade  wrote:
> >
> > Richard Hipp  writes:
> >> A change summary for 3.13.0 is at
> >> https://www.sqlite.org/draft/releaselog/3_13_0.html
> >
> > Change the temporary directory search algorithm on Unix to allow
> > directories read and execute permission, but without read permission,
> > to
> > serve as temporary directories.
> >
> > .. "write and execute permission, but without read permission" ?
> 
> Yes.  Apparently that is the new standard for security on unix

The way I understood Rolf's comment was that he was pointing out a
typo: the text reads "read and execute" permission when it should
probably read "write and execute" permission.

The way I understood your reply (although informative) was that you
missed Rolf's point.

Could be however that I've mis-understood both points :-)

-- 
Mark Lawrence

[sqlite] Delete an existing module

[Richard Hipp]

On 5/3/16, gwenn  wrote:
> Hello,
> Is it possible to delete a module and the associated client data ?
> I tried to pass a NULL pointer as the third parameter of
> sqlite3_create_module_v2,
> sqlite3_create_module_v2(
>   db,
>   zName,
>   NULL,
>   NULL,
>   NULL
> );
> but I got a SQLITE_MISUSE error.

No.  Once a virtual table module has been created, it cannot be
modified or destroyed, except by closing the database connection.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Version 3.13.0 coming soon

[Richard Hipp]

On 5/3/16, Graham Holden  wrote:
>
>> Yes.  Apparently that is the new standard for security on unix
> systems.
>
> New? I remember FTP repositories from the mid 80s (funic, wustl?) where this
> scheme was common place for the "upload" directory so random visitors

The capability is not new, but the idea of applying it to /var/tmp is, afaik.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] ODP: ODP: Cancelling sqlite3_wal_checkpoint

[Albert Banaszkiewicz]

Thank you!


Od: sqlite-users-bounces at mailinglists.sqlite.org  w imieniu u?ytkownika Richard Hipp 
Wys?ane: 3 maja 2016 15:41
Do: SQLite mailing list
Temat: Re: [sqlite] ODP: Cancelling sqlite3_wal_checkpoint

On 5/3/16, Albert Banaszkiewicz  wrote:
> I see.
> How safe it will be with respect to data integrity ? Are there any chances
> data can get lost ?

SQLite is designed to handle this without data loss.  And we actually
run a lot of tests for that as well.

> Will it affect other read connections in any way ?

No.

>
> If the process is terminated like this, what will happen when
> sqlite3_wal_checkpoint is called again ? Is the process going to be resumed
> from the point where it was interrupted (more or less) or from the beginning
> ?

It starts over again from the beginning, unfortunately.  No
intermediate state was saved that would allow it to resume where it
left off.

That would also be the case where checkpoint interruptible using
sqlite3_interrupt().

--
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ODP: Cancelling sqlite3_wal_checkpoint

[Albert Banaszkiewicz]

I see.
How safe it will be with respect to data integrity ? Are there any chances data 
can get lost ? 
Will it affect other read connections in any way ?

If the process is terminated like this, what will happen when 
sqlite3_wal_checkpoint is called again ? Is the process going to be resumed 
from the point where it was interrupted (more or less) or from the beginning ?

Cheers,
Albert


Od: sqlite-users-bounces at mailinglists.sqlite.org  w imieniu u?ytkownika Richard Hipp 
Wys?ane: 3 maja 2016 15:20
Do: SQLite mailing list
Temat: Re: [sqlite] Cancelling sqlite3_wal_checkpoint

On 5/3/16, Albert Banaszkiewicz  wrote:
> Hi all,
>
>
> Is it possible to cancel check-pointing operation in any way ?
>

You can kill the thread or process that is running the checkpoint.
Other than that, there is not currently any way to interrupt a
checkpoint in progress.

--
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Version 3.13.0 coming soon

[Richard Hipp]

On 5/3/16, nomad at null.net  wrote:
> On Tue May 03, 2016 at 08:33:30AM -0400, Richard Hipp wrote:
>>
>> Yes.  Apparently that is the new standard for security on unix
>
> The way I understood Rolf's comment was that he was pointing out a
> typo:

Ah.  You are correct: I completely missed Rolf's point.

Fixed now.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Cancelling sqlite3_wal_checkpoint

[Albert Banaszkiewicz]

Hi all,


Is it possible to cancel check-pointing operation in any way ?

I could find how to interrupt queries (?sqlite3_interrupt) but I assume it 
won't work for check-pointing (not really a query).


However, like long-lasting queries, check-pointing can take a while, thus I am 
wondering if there is any way to reliably cancel it.


Thank you in advance,

Albert?

[sqlite] FTS offsets() reporting SQLITE_CORRUPT with empty string, redux

[Michael Gratton]

Hi all,

I'm running into a bug where the FTS offsets() function is returning a 
null string, causing SQLITE_CORRUPT to be reported for SELECT query 
results in both SQLite 3.11 and 3.12.2.

This is similar to a previously reported issue with SQLite from a while 
back -  
,
 
however the minimal example from that report does not trigger any error 
in 3.11/3.12.2.

Unfortunately, I haven't been able to produce a minimal example for 
this new instance of the bug. Taking problematic rows from the existing 
DB, inserting them into a new FTS4 table, then running the same query 
is not triggering it. I can however reproduce it with a freshly 
constructed copy of the application's database, so it may not be an 
actual corrupt database. I cannot post the database file to a public 
forum however, since it contains confidential information.

A minimal query on the database in question that produces the error is 
simply of the form:

 > SELECT offsets(FTS4Table) FROM FTS4Table WHERE FTS4Table MATCH 
'somestr';

I can say that it is the offsets() function that is causing it - 
removing/replacing it in the SELECT statement makes the error go away. 
The form of the MATCH string doesn't seem to matter, but only some 
strings will cause the error to appear. Also, a custom tokeniser is in 
use - unicodesn from here: 


For context, the application's bug report is here: 


What can I do to help resolve this bug?

//Mike

-- 
? Michael Gratton, Percept Wrangler.
? 

[sqlite] SQLite vs GPLed software

[dandl]

>From a purely numerical point of view, the largest numbers would be found in
devices, eg phones, cars, TVs, clocks/timers, etc. Lots of GPL in there, but
also lots of other licences too. I have no idea how the GSM stack is
licensed, for example, but I think there are more GSM phones than instances
of Sqlite.

And I certainly can't think of any cases where a GPL licence has been of
specific benefit in achieving wider usage, as compared to the approach taken
by Sqlite.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Richard Hipp
> Sent: Tuesday, 3 May 2016 10:11 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] SQLite vs GPLed software
> 
> On 5/2/16, Scott Robison  wrote:
> > I don't have an answer, but given that each linux distro of dozens or
> > hundreds of independently sourced packages has many separate instances
> > of the GPL, that would begin to eat into SQLITE'S lead. But probably
> > not enough to win.
> 
> What GPLed packages are on Android?  Compared to Android, all other Linux
> distros are rounding error, I think (correct me if I'm wrong).
> 
> Also, what percentage of those other GPLed packages statically link
against
> SQLite?
> 
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Differences for DELETE with EXISTS

[Rob Golsteijn]

>> I observe a difference in results of a DELETE query using the EXISTS
>> operator between Sqlite version 3.8.11.1 and 3.9.0.
>
> Thanks for the bug report.
>
> Ticket: https://www.sqlite.org/src/info/dc6ebeda9396087
> Candidate fix: https://www.sqlite.org/src/info/3f221f592a9a1900
> -- 
> Richard Hipp
> drh at sqlite.org


Thanx for solving this issue so quickly. We integrated the fix and we get the 
expected results.



I guess that the fact that the behavior of sqlite was updated to the old 
behavior also answers my question how my query should be interpreted according 
to the SQL standard:

EXISTS and subqueries should operate on the original (unmodified) tables, not 
on the (partly) updated table.

Met Vriendelijke Groet, Kind Regards,

Rob Golsteijn

[sqlite] ODP: Cancelling sqlite3_wal_checkpoint

[Richard Hipp]

On 5/3/16, Albert Banaszkiewicz  wrote:
> I see.
> How safe it will be with respect to data integrity ? Are there any chances
> data can get lost ?

SQLite is designed to handle this without data loss.  And we actually
run a lot of tests for that as well.

> Will it affect other read connections in any way ?

No.

>
> If the process is terminated like this, what will happen when
> sqlite3_wal_checkpoint is called again ? Is the process going to be resumed
> from the point where it was interrupted (more or less) or from the beginning
> ?

It starts over again from the beginning, unfortunately.  No
intermediate state was saved that would allow it to resume where it
left off.

That would also be the case where checkpoint interruptible using
sqlite3_interrupt().

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Cancelling sqlite3_wal_checkpoint

[Richard Hipp]

On 5/3/16, Albert Banaszkiewicz  wrote:
> Hi all,
>
>
> Is it possible to cancel check-pointing operation in any way ?
>

You can kill the thread or process that is running the checkpoint.
Other than that, there is not currently any way to interrupt a
checkpoint in progress.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Version 3.13.0 coming soon

[Richard Hipp]

On 5/2/16, Rolf Ade  wrote:
>
> Richard Hipp  writes:
>> A change summary for 3.13.0 is at
>> https://www.sqlite.org/draft/releaselog/3_13_0.html
>
> Change the temporary directory search algorithm on Unix to allow
> directories read and execute permission, but without read permission,
> to
> serve as temporary directories.
>
> .. "write and execute permission, but without read permission" ?

Yes.  Apparently that is the new standard for security on unix
systems.  Write lets you create new temp files.  Execute lets you
check to see if a particular file exists.  But because read is
disabled, one cannot do an "ls" on the temporary directory to see what
temp files other applications have created.

Note that SQLite always unlinks its own temp files as soon as they are
created, so you were never able to see SQLite's temp file, unless you
are lucky and happen to read the directory during the microsecond in
between the open() and unlink() system calls.  But not all
applications do that.

The fact that SQLite formerly rejected temporary directories that
omitted read permission was submitted to us as a security
vulnerability.  If that's the worst security vulnerability that SQLite
ever creates, then I think we are doing a good job.  Nevertheless, we
have now fixed the issue.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Make mmap_size dynamic?

[Mikael]

Aha, great! So what I was asking for was already in the box, and a
mmap_size of say 2^63 will be fine then!


One reason that I asked for this was that I want to use it on OpenBSD, and
there, mmaping in Sqlite is disabled altogether, in the absence of a
unified buffer cache (UBC) in the OS.

If I just force it on (by hacking the build script), as long as mmap_size
always is 2^63, will Sqlite access the file via memory accesses only, and
never using fread/fwrite which would lead to undefined behavior because of
the absence of a UBC?

Thanks!

On Tuesday, 3 May 2016, Scott Hess  wrote:

> The existing mmap functionality only maps the actual blocks associated with
> the file.  So if your file is 16kb and your mmap_size is 1GB, only 16kb is
> used.  Unless you add data to the file, then the mmap area grows,
> obviously.
>
> -scott
>
>
> On Mon, May 2, 2016 at 2:01 AM, Mikael  > wrote:
>
> > Dear Dr. Hipp & list,
> >
> > What about making Sqlite's memory mapping adapt to the current database
> > size, in increments of say 100MB?
> >
> > The at least 48 bits (256TB) of addressing space that modern 64bit
> > architectures give per process is not suffering any risk of depletion, as
> > long as the space not is used wastefully, which would be the case now as
> > today in the absence of an incremental setting, to guarantee that a
> > database never will grow outside of the mmap size, a developer is tempted
> > to set mmap_size to a value so high that it guaranteedly never will be
> > reached e.g. 1TB, and that way an application could be almost 100%
> wasteful
> > with address space, and that way a process would get a constraint of max
> > 200 or so databases.
> >
> > Can Sqlite user code implement this by itself already somehow?
> >
> > This would also be useful to do memorymapped-only IO on an OS that not
> has
> > a unified buffer cache, such as OpenBSD, where memory mapping is disabled
> > altogether for this reason currently.
> >
> > Looking forward to your response,
> >
> > Thanks,
> > Mikael
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org 
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org 
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Version 3.13.0 coming soon

[Rolf Ade]

Richard Hipp  writes:
> A change summary for 3.13.0 is at
> https://www.sqlite.org/draft/releaselog/3_13_0.html

Change the temporary directory search algorithm on Unix to allow
directories read and execute permission, but without read permission, to
serve as temporary directories.

.. "write and execute permission, but without read permission" ?

[sqlite] SQLite vs GPLed software

[Simon Slavin]

On 3 May 2016, at 12:42am, Richard Hipp  wrote:

> There are countless other GPL projects without which life as
> we know it would come to an end:  valgrind, gcc, bash, to name but a
> few.  But I cannot think of any that have extensive usage outside the
> geek community, and hence for all their importance, they don't have
> all that many deployments relative to Linux.

How about libjpeg ?  Or libpng or whatever ?  libjpeg is installed on my oven.  
At least it's credited in the oven documentation so I assume it's there.  It's 
on my SatNav, TV, digital TV recorder, oven and, of course, all phone and 
computer-like devices.

Simon.

[sqlite] SQLite vs GPLed software

[Richard Hipp]

On 5/2/16, Roger Binns  wrote:
> On 02/05/16 16:42, Richard Hipp wrote:
>> But some of the discussion did get me
>> thinking about the extent of GPLed software versus SQLite.
>
> Something that needs be made abundantly clear is the GPL is *not* about
> popularity.  The GPL is about freedom (think freedom of speech, not
> price).  Even then it is about freedom for end users, not developer
> focused.

I said I won't participate in that debate and I mean it.  The relative
merits of GPL vs  whatever is *not* the question on the floor.

-- 
D. Richard Hipp
drh at sqlite.org