Re: [sqlite] Mailing list shutting down...

[Florian Weimer]

* Richard Hipp:

> On 6/13/18, Brian Curley  wrote:
>> Doesn't the Fossil site already have a Capcha interface built into it that
>> could be adopted to enforce additional authentication around subscriptions?
>
> There are no captchas built into GNU MailMan.  You enter your email
> address to subscribe and you get a confirmation email.  Click on a
> link in the confirmation email.  Then your subscription goes to
> moderation.  After the moderator approves, you are signed up.

Some largish operators use CAPTCHAs:

  

But wouldn't any replacement that allows email notification have
exactly the same signup issue?  Sure, you might only have one pending
signup request per email instead per list, but if the bots are
actually targeting innocent users, for most recipients, they can just
use multiple aliases of the form ,
, and so on.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 3.17.0 bug report: FTS5 insertion puts a wrong value in last_insert_rowid

[Florian Weimer]

* Gwendal Roué:

> I have found a regression in SQLite 3.17.0. In the following SQL statements:
>
> CREATE VIRTUAL TABLE t1 USING FTS5(content);
> INSERT INTO t1(content) VALUES ('some text');
> SELECT last_insert_rowid(); // 10 (wrong)
> SELECT rowid FROM t1;   // 1
>
> The expected value of the the SQL function last_insert_rowid()
> function is 1, not 10. Same for the C function
> sqlite3_last_insert_rowid().

I think this is a known issue.  SQLite 3.18 adds a
sqlite3_set_last_insert_rowid() function and uses it in “the new
interface in the FTS3, FTS4, and FTS5 extensions to ensure that the
sqlite3_last_insert_rowid() interface always returns reasonable
values”.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Read-only access which does not block writers

[Florian Weimer]

On 11/25/2016 02:07 AM, Howard Chu wrote:

Florian Weimer wrote:

On 11/24/2016 10:41 PM, Howard Chu wrote:

As a
compromise you could use SQLightning, which replaces SQLite's Btree
layer with LMDB. Since LMDB *does* allow readers that don't block
writers.


How does it do that?  Does LMDB perform lock-free optimistic reads and
retroactively verifies that the entire read operation was consistent?
The web
page currently says that “readers need write access to locks and lock
file”:

  <http://lmdb.tech/doc/>


Readers are lock-free/wait-free. Since LMDB uses MVCC readers get their
own fully isolated snapshot of the DB so no retroactive verification is
needed.


I think you misunderstood what I was asking (see the start of the 
thread).  I need to support Byzantine readers which do not follow the 
locking protocol.  Based on the documentation, LMDB uses locks to 
implement MVCC and prevent premature page reuse.


Thanks,
Florian

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

Re: [sqlite] Read-only access which does not block writers

[Florian Weimer]

On 11/24/2016 10:41 PM, Howard Chu wrote:

Florian Weimer wrote:

On 11/24/2016 02:54 PM, Richard Hipp wrote:

On 11/24/16, Florian Weimer <fwei...@redhat.com> wrote:

I'd like to replace the use of Berkeley DB in RPM with SQLite.

The scenario is special in the follow way.  There is no database
server,
all access goes directly to the database.  Unprivileged users without
write access to the RPM database are expected to run read-only queries
against the database.  Privileged users (basically, root) is
expected to
use locking to exclude concurrent writers.  But read-only users should
not be able to stop acquisition of a write lock.

Is there a way to do this with SQLite?


Seems like quite a lot of burden to go this route. SQLite has a much
larger footprint than BDB, and much worse performance overall.


SQLite has zero footprint for us because central parts of the system use 
it as well, and it is not likely to go away.  We also use the full 
Transaction Data Store, so the Berkeley DB shared object is about twice 
as large as the SQLite object.



As a
compromise you could use SQLightning, which replaces SQLite's Btree
layer with LMDB. Since LMDB *does* allow readers that don't block
writers.


How does it do that?  Does LMDB perform lock-free optimistic reads and 
retroactively verifies that the entire read operation was consistent? 
The web page currently says that “readers need write access to locks and 
lock file”:


  <http://lmdb.tech/doc/>

The restriction on opening the database twice within the same process is 
something which we would have to work around, too.


I see you are working on eliminating the key size limit, which is nice.

Thanks,
Florian

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

Re: [sqlite] Read-only access which does not block writers

[Florian Weimer]

On 11/24/2016 02:54 PM, Richard Hipp wrote:

On 11/24/16, Florian Weimer <fwei...@redhat.com> wrote:

I'd like to replace the use of Berkeley DB in RPM with SQLite.

The scenario is special in the follow way.  There is no database server,
all access goes directly to the database.  Unprivileged users without
write access to the RPM database are expected to run read-only queries
against the database.  Privileged users (basically, root) is expected to
use locking to exclude concurrent writers.  But read-only users should
not be able to stop acquisition of a write lock.

Is there a way to do this with SQLite?


The readers can open the database using URI filenames
(https://www.sqlite.org/uri.html) with query parameters "mode=ro" and
"locking=0".  That will prevent the readers from blocking the writer.


Thanks, this looks promising.

For the writers, can I use an external lock file (accessible only by 
root), and specify “locking=0” as well?  I need to avoid stalling on a 
read lock on the database file because any process which can open the 
file could create such a lock, thus blocking regular SQLite operation.



But, if a write happens in the middle of a read, the reader might see
inconsistent data and report SQLITE_CORRUPT.  This is harmless in the
sense that the database file is not really corrupt (the reader is
merely seeing parts of the files from two different points in time)
and subsequent reads should still work.  If you are unlucky, a write
that happens at the same time as a read might cause the reader to
return incorrect results, so the reader can never be 100% sure that
the answer it gets back is correct.


Could I use PRAGMA data_version to detect the case where there answer 
might be silently incorrect?



How important is it to you that the reader always get a correct answer?


An incorrect answer or SQLITE_CORRUPT is borderline acceptable.  Endless 
loops or crashes would be bad.


I need to run a test how often the existing code gets a wrong answer.  I 
can't imagine that it always gets this right because Berkeley DB does 
not really support this use case, either.  Either readers block writers, 
or there will be wrong data occasionally.


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

Re: [sqlite] Read-only access which does not block writers

[Florian Weimer]

On 11/24/2016 01:10 PM, Simon Slavin wrote:


On 24 Nov 2016, at 11:02am, Florian Weimer <fwei...@redhat.com> wrote:


The scenario is special in the follow way.  There is no database server, all 
access goes directly to the database.  Unprivileged users without write access 
to the RPM database are expected to run read-only queries against the database. 
 Privileged users (basically, root) is expected to use locking to exclude 
concurrent writers.  But read-only users should not be able to stop acquisition 
of a write lock.

Is there a way to do this with SQLite?


From the above you would want to use WAL mode.  You can read about it here:

<https://www.sqlite.org/wal.html>


Item 5 says:

“
It is not possible to open read-only WAL databases. The opening process 
must have write privileges for "-shm" wal-index shared memory file 
associated with the database, if that file exists, or else write access 
on the directory containing the database file if the "-shm" file does 
not exist.

”

So WAL mode does not work in this scenario.

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

[sqlite] Read-only access which does not block writers

[Florian Weimer]

I'd like to replace the use of Berkeley DB in RPM with SQLite.

The scenario is special in the follow way.  There is no database server, 
all access goes directly to the database.  Unprivileged users without 
write access to the RPM database are expected to run read-only queries 
against the database.  Privileged users (basically, root) is expected to 
use locking to exclude concurrent writers.  But read-only users should 
not be able to stop acquisition of a write lock.


Is there a way to do this with SQLite?

One way that would work is to copy the database file after each 
modification to a read-only public view.  But the database can be fairly 
large, so this doesn't look feasible until we have reflink support at 
the file system level.


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

Re: [sqlite] Using a "custom" version of SQLite under Python in Ubuntu.

[Florian Weimer]

* Richard Hipp:

> (1) You should be able to drop in SQLite 3.15.0 (or any other release
> after 3.8.2) in place of SQLite 3.8.2 and everything will continue
> working just fine.  The only changes will be that the applications
> will run a little faster.  We carefully test backwards compatibility.

I did this once, admittedly on CentOS, but there SQLite is used in
more places by the operating system itself.  I did not encounter any
problems.  I didn't lobby Red Hat to upgrade the system library
version because it was just a temporary situation for me.

I don't know how open Canonical would be to upgrading SQLite in an LTS
release, based on customer demand.  Maybe it's worth a try?

> (1a)  Sometimes an application will do a SELECT without an ORDER BY
> when the application really does need the data in a specific order.
> This error is not noticed because SQLite happens to return the rows in
> the desired order, just by chance.  Upgrading to a newer version of
> SQLite might change the output order.  This is about the only
> compatibility issues you can expect when upgrading SQLite.  Notice
> that this is a bug in the application, not a bug in SQLite.

Query planner changes can also have negative performance impact.

> (2) SQLite works fine with static linking.

It can break POSIX locking if one process accesses the same database
with two different SQLite code copies.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] smartest way to exchange a sqlite3 database with another empty

[Florian Weimer]

* Richard Hipp:

> You cannot rename a database file while another process has that
> database open.  Windows simply will not allow that.  If you do it on
> Unix, then the process that has the file open will not know that the
> file has been renamed and will continue to write to the original file.

The journal, SHM and WAL files will also not be renamed at the same
time on POSIX systems, which could have nasty consequences.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why so much I/O ? Can sqlite be improved ?

[Florian Weimer]

* Domingo Alvarez Duarte:

> After 12 hours inserting of:
>
> 934,135,285 records on bolsas_familia
>
> 22,711,259 records in favorecidos
>
> 5,570 records in municipios
>
> ...

Insertion will be faster if you create the index after populating the
tables.

> time sqlite3 bolsa_familia3.db "vacuum;"
>
> real147m6.252s
> user10m53.790s
> sys3m43.663s

You really need to increase the cache size if at all possible.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Another unexpected source of SQLITE_BUSY_SNAPSHOT

[Florian Weimer]

* Dan Kennedy:

>> My concern is about sqlite3_step(UPDATE) without a following
>> sqlite3_reset(UPDATE).  Perhaps I should change my wrapper to
>> unconditionally call sqlite3_reset() after DML-related sqlite3_step(),
>> whether the stepping operation succeeded or not.
>
> For a DML statement, I usually just do:
>
>   sqlite3_step(pStmt);
>   rc = sqlite3_reset(pStmt);
>   if( rc!=SQLITE_OK ){
> /* Handle error */
>   }
>
> I don't see why you would want to call step() but not reset() for a
> DML operation. And since reset() always returns either the same or a
> more specific error code, there isn't too much reason to ever catch
> the return value of step(). IMO.

The reset operation acquires another mutex.  I have not investigated
this in detail yet, but even in the non-contended case, this mutex
operation showed up in profiles for me (with a heavy INSERT workload,
rows mostly in the order of the INTEGER NOT NULL PRIMARY KEY).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Potential bug: Database on gvfs mount cannot be committed to

[Florian Weimer]

* R. Smith:

> Enterprise DBs have servers on the same machine as the Files they
> access, they do not actually use the network file-system to access the
> DB data-files over the network from multiple clients, or even servers
> (unless the DBs are partitioned so and ONLY accessed by the single
> process so locking is permanent and moot).

Deployments vary considerably.  A lot of enterprises use snapshotable
storage for databases as well, just as an option in case upgrades need
to be rolled back.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug: Successfully committed transaction rolled back after power failure

[Florian Weimer]

On 01/25/2016 04:47 PM, Richard Hipp wrote:
> On 1/25/16, Matthias-Christian Ott  wrote:
>>
>> Does this mean that if I use SQLite SQLITE_EXTRA_DURABLE=0, PRAGMA
>> journal_mode=DELETE and PRAGMA synchronous=FULL, SQLite could loose a
>> transaction that it said to be committed depending on the VFS?
> 
> Sort of.  This appears to be true if you are running on QNX and you
> lose power (or do a hard reset) shortly after the transaction commits.
> It might be the case on other OSes/filesystems but it has never before
> been observed.

Both XFS and Ceph on Linux require the directory fsync operation to make
an unlink persistent.  (I did not run experiments, I asked people
familiar with these file systems.)

Please activate directory fsyncs for Linux, too.

Florian

[sqlite] Another unexpected source of SQLITE_BUSY_SNAPSHOT

[Florian Weimer]

* Dan Kennedy:

> On 10/18/2015 10:27 PM, Florian Weimer wrote:
>> My first surprise was SQLITE_BUSY_SNAPSHOT from BEGIN IMMEDIATE (see
>> ?SQLITE_BUSY_SNAPSHOT from BEGIN IMMEDIATE?).  I have found another
>> source of such snapshot failures with WAL-mode databases.
>>
>> I like to pre-compile my DML statements before starting transactions,
>> mainly for the implied syntax check.  (But perhaps there are also
>> performance benefits, too?I haven't checked.)
>>
>> Here is what I did (if my tracing is correct).  Unless mentioned
>> otherwise, the operations succeed with SQLITE_OK.
>>
>>Open the database.
>>Compile the UPDATE statement.
>>Compile the ROLLBACK statement (used below).
>>Start a BEGIN DEFERRED transaction (with a fresh statement).
>>Reset the UPDATE statement.
>>Step the UPDATE statement.
>> -> this fails with SQLITE_BUSY_SNAPSHOT
>>Reset/step/reset the pre-compiled ROLLBACK statement.
>>Start a BEGIN IMMEDIATE transaction (with a fresh statement).
>> -> this fails with SQLITE_BUSY_SNAPSHOT
>>Start a BEGIN IMMEDIATE transaction (with a fresh statement).
>>Reset the UPDATE statement.
>> -> this fails with SQLITE_BUSY_SNAPSHOT
>>
>> After the last failure, iterating through the list of stattements does
>> not show that the UPDATE statement is busy.
>
>
> The SQLITE_BUSY_SNAPSHOT returned by the final call to sqlite3_reset()
> on the UPDATE is not a new error - it has just saved the error code
> from the error that occurred during the sqlite3_step() call in the
> previous transaction.

D'oh!  I mistakenly assumed using the sqlite3_prepare_v2() function
would avoid that.  Thanks.

> So technically the error did not occur within
> the BEGIN IMMEDIATE transaction.

Right.

I have changed my wrapper for sqlite3_reset() to ignore the error
value and removed the compensation code from my transaction monitor.
The tests that previously failed occasionally still succeed.

>> I don't know how risky pre-compiling UPDATE statement is.  For SELECT
>> statements, it is problematic because they can lead to persistent
>> transaction failures with SQLITE_BUSY_SNAPSHOT because the
>> pre-compiled and partitial executed statement is busy and will block
>> creating a new transaction.  I wonder if this can happen with UPDATE
>> statements as well.
>
> I don't quite understand this. Preparing an SQL statement may read the
> database (in order to read the schema). But calling sqlite3_prepare()
> doesn't leave the statement in a "partially executed" state.

My concern is about sqlite3_step(UPDATE) without a following
sqlite3_reset(UPDATE).  Perhaps I should change my wrapper to
unconditionally call sqlite3_reset() after DML-related sqlite3_step(),
whether the stepping operation succeeded or not.

[sqlite] Another unexpected source of SQLITE_BUSY_SNAPSHOT

[Florian Weimer]

My first surprise was SQLITE_BUSY_SNAPSHOT from BEGIN IMMEDIATE (see
?SQLITE_BUSY_SNAPSHOT from BEGIN IMMEDIATE?).  I have found another
source of such snapshot failures with WAL-mode databases.

I like to pre-compile my DML statements before starting transactions,
mainly for the implied syntax check.  (But perhaps there are also
performance benefits, too?I haven't checked.)

Here is what I did (if my tracing is correct).  Unless mentioned
otherwise, the operations succeed with SQLITE_OK.

  Open the database.
  Compile the UPDATE statement.
  Compile the ROLLBACK statement (used below).
  Start a BEGIN DEFERRED transaction (with a fresh statement).
  Reset the UPDATE statement.
  Step the UPDATE statement.
   -> this fails with SQLITE_BUSY_SNAPSHOT
  Reset/step/reset the pre-compiled ROLLBACK statement.
  Start a BEGIN IMMEDIATE transaction (with a fresh statement).
   -> this fails with SQLITE_BUSY_SNAPSHOT
  Start a BEGIN IMMEDIATE transaction (with a fresh statement).
  Reset the UPDATE statement.
   -> this fails with SQLITE_BUSY_SNAPSHOT

After the last failure, iterating through the list of stattements does
not show that the UPDATE statement is busy.

This seems to contradict this piece of the documentation:

| If X starts a transaction that will initially only read but X knows
| it will eventually want to write and does not want to be troubled
| with possible SQLITE_BUSY_SNAPSHOT errors that arise because another
| connection jumped ahead of it in line, then X can issue BEGIN
| IMMEDIATE to start its transaction instead of just an ordinary
| BEGIN. The BEGIN IMMEDIATE command goes ahead and starts a write
| transaction, and thus blocks all other writers. If the BEGIN
| IMMEDIATE operation succeeds, then no subsequent operations in that
| transaction will ever fail with an SQLITE_BUSY error.



This happens with version 3.8.11.1 on Fedora 22.

I don't know how risky pre-compiling UPDATE statement is.  For SELECT
statements, it is problematic because they can lead to persistent
transaction failures with SQLITE_BUSY_SNAPSHOT because the
pre-compiled and partitial executed statement is busy and will block
creating a new transaction.  I wonder if this can happen with UPDATE
statements as well.

[sqlite] SQLITE_BUSY_SNAPSHOT from BEGIN IMMEDIATE

[Florian Weimer]

* Rowan Worth:

> On 29 September 2015 at 03:47, Florian Weimer  wrote:
>
>> Relatively rarely, while starting a transaction on a concurrently
>> modified SQLite database in WAL mode, I get a SQLITE_BUSY_SNAPSHOT
>> error for just-prepared "BEGIN IMMEDIATE" statement.
>>
>
> BEGIN IMMEDIATE takes a RESERVED lock straight away, which for WAL mode
> translates into 1) starting a read transaction and 2) upgrading to a write
> transaction.

Thanks for the independent confirmation.  It seems that I don't have
to treat BEGIN IMMEDIATE in a special way after all.  If a transaction
fails with SQLITE_BUSY_SNAPSHOT and there are no busy statements which
could cause persistent upgrade failures (which is true for a spurious
BEGIN IMMEDIATE failure due to failed lock upgrade), I will retry the
transaction and start it with BEGIN IMMEDIATE unconditionally (even if
a lower isolation mode was requested originally).  This will cover
failed lock upgrades within the transaction, too.

[sqlite] SQLITE_BUSY_SNAPSHOT from BEGIN IMMEDIATE

[Florian Weimer]

Relatively rarely, while starting a transaction on a concurrently
modified SQLite database in WAL mode, I get a SQLITE_BUSY_SNAPSHOT
error for just-prepared "BEGIN IMMEDIATE" statement.  When I look at
the list of statements with sqlite3_stmt_next, I cannot find any busy
statement.  Is it possible that a "BEGIN IMMEDIATE" statement by
itself fails with this error code?  This is a bit suprising.  I would
have expected a regular SQLITE_BUSY because this is not exactly a
snapshot upgrade failure as far as the application is concerned (it is
possible to try again immediately and succeed).

This is with SQLite 3.8.7.1 from Debian jessie.

[sqlite] Potential bug: Database on gvfs mount cannot be committed to

[Florian Weimer]

* Roger Binns:

> On 09/06/2015 11:13 AM, Florian Weimer wrote:
>> Surely that's not true, and NFS and SMB are fine as long as there
>> is no concurrent access?
>
> And no program crashes, no network glitches, no optimisation in the
> protocols to deal with latency, nothing else futzing with the files,
> no programs futzing with them (backup agents, virus scanners etc), the
> protocols are 100% complete compared to local file access, the
> implementation of client and server for the protocol is 100% complete
> and bug free, the operating systems don't treat network filesystems
> sufficiently different to cause problems, you aren't using WAL, and
> the list goes on.
>
> In other words it can superficially appear to work.  But one day
> you'll eventually notice corruption, post to this list, and be told
> not to use network filesystems.  The only variable is how long it
> takes before you make that post.

Sorry, this all sounds a bit BS to me.  Surely, as an fopen
replacement, SQLite works with network file systems, be it the home
NAS, or something in a typical datacenter.  And if the SQLite locking
doesn't work in practice (which I doubt, remote file systems are
better at locking than they used to be), should we really fall back
on lock files with user overrides?  I hope not.

(A lot of people run enterprise databases on NFS because that gives
them snapshot-able storage and other goodies.  Not everyone uses iSCSI
and block devices.)

[sqlite] Potential bug: Database on gvfs mount cannot be committed to

[Florian Weimer]

* Roger Binns:

> On 09/06/2015 06:16 AM, Markus Weiland wrote:
>> I've discovered a potential bug in handling of SQLite database
>> files on gvfs mounted network shares.
>
> SQLite doesn't support being stored on the network for several
> reasons, including that network file protocols don't implement fully
> and correctly various access and locking, and that some journaling
> modes like WAL require shared memory and hence can't be networked.
> This is covered in the FAQ:
>
>   https://www.sqlite.org/faq.html#q5
>
> Or if you want to be pedantic, it is gvfs and networked filesystems
> that have the bug.

Surely that's not true, and NFS and SMB are fine as long as there is
no concurrent access?

[sqlite] Crippling query plan change between 3.7.13 and 3.8.10.2

[Florian Weimer]

The Debian security tracker 
uses an SQLite database to keep track of vulnerabilites and
generate reports.

We recently upgraded SQLite from 3.7.13 to 3.8.7.1 as part of an
operating system upgrade and experienced a crippling query planner
change.  I verified that the issue is present in 3.8.10.2 as well.

Here are the details.  A sample database file is available at
.

  CREATE TABLE source_packages
  (name TEXT NOT NULL,
  release TEXT NOT NULL,
  subrelease TEXT NOT NULL,
  archive TEXT NOT NULL,
  version TEXT NOT NULL,
  version_id INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (name, release, subrelease, archive));
  CREATE TABLE package_notes
  (id INTEGER NOT NULL PRIMARY KEY,
   bug_name TEXT NOT NULL,
   package TEXT NOT NULL,
   fixed_version TEXT
   CHECK (fixed_version IS NULL OR fixed_version <> ''),
   fixed_version_id INTEGER NOT NULL DEFAULT 0,
   release TEXT NOT NULL,
   package_kind TEXT NOT NULL DEFAULT 'unknown',
   urgency TEXT NOT NULL,
   bug_origin TEXT NOT NULL DEFAULT '');
  CREATE TABLE debian_bugs
  (bug INTEGER NOT NULL,
   note INTEGER NOT NULL,
   PRIMARY KEY (bug, note));
  CREATE TABLE bugs
  (name TEXT NOT NULL PRIMARY KEY,
   cve_status TEXT NOT NULL
   CHECK (cve_status IN
  ('', 'CANDIDATE', 'ASSIGNED', 'RESERVED', 'REJECTED')),
   not_for_us INTEGER NOT NULL CHECK (not_for_us IN (0, 1)),
   description TEXT NOT NULL,
   release_date TEXT NOT NULL,
   source_file TEXT NOT NULL,
   source_line INTEGER NOT NULL);
  CREATE TABLE bugs_notes
  (bug_name TEXT NOT NULL CHECK (typ <> ''),
   typ TEXT NOT NULL CHECK (typ IN ('TODO', 'NOTE')),
   release TEXT NOT NULL DEFAULT '',
   comment TEXT NOT NULL CHECK (comment <> ''));
  CREATE TABLE bugs_xref
  (source TEXT NOT NULL,
   target TEXT NOT NULL,
   PRIMARY KEY (source, target));
  CREATE TABLE bug_status
  (bug_name TEXT NOT NULL,
   release TEXT NOT NULL,
   status TEXT NOT NULL
   CHECK (status IN ('vulnerable', 'fixed', 'unknown', 
'undetermined',
 'partially-fixed', 'todo')),
   reason TEXT NOT NULL,
   PRIMARY KEY (bug_name, release));
  CREATE TABLE source_package_status
  (bug_name TEXT NOT NULL,
   package INTEGER NOT NULL,
   vulnerable INTEGER NOT NULL,
   urgency TEXT NOT NULL,
   PRIMARY KEY (bug_name, package));
  CREATE TABLE removed_packages (name TEXT NOT NULL PRIMARY KEY);
  CREATE TABLE nvd_data
  (cve_name TEXT NOT NULL PRIMARY KEY,
  cve_desc TEXT NOT NULL,
  discovered TEXT NOT NULL,
  published TEXT NOT NULL,
  severity TEXT NOT NULL,
  range_local INTEGER,
  range_remote INTEGER,
  range_user_init INTEGER,
  loss_avail INTEGER NOT NULL,
  loss_conf INTEGER NOT NULL,
  loss_int INTEGER NOT NULL,
  loss_sec_prot_user INTEGER NOT NULL,
  loss_sec_prot_admin INTEGER NOT NULL,
  loss_sec_prot_other INTEGER NOT NULL);
  CREATE TABLE debsecan_data
  (name TEXT NOT NULL PRIMARY KEY,
  data TEXT NOT NULL);
  CREATE TABLE package_notes_nodsa
  (bug_name TEXT NOT NULL,
  package TEXT NOT NULL,
  release TEXT NOT NULL,
  reason TEXT NOT NULL,
  comment TEXT NOT NULL,
  PRIMARY KEY (bug_name, package, release));
  CREATE INDEX package_notes_package
  ON package_notes(package);
  CREATE INDEX bugs_xref_target ON bugs_xref(target);
  CREATE INDEX source_package_status_package
  ON source_package_status(package);
  CREATE UNIQUE INDEX package_notes_bug
  ON package_notes(bug_name, package, release);
  CREATE VIEW debian_cve AS
  SELECT DISTINCT debian_bugs.bug, st.bug_name
  FROM package_notes, debian_bugs, source_package_status AS st
  WHERE package_notes.bug_name = st.bug_name
  AND debian_bugs.note = package_notes.id
  ORDER BY debian_bugs.bug;

The offending query is:

  SELECT sp.name, st.bug_name,
(SELECT cve_desc FROM nvd_data
WHERE cve_name = st.bug_name),
(SELECT debian_cve.bug FROM debian_cve
WHERE debian_cve.bug_name = st.bug_name
ORDER BY debian_cve.bug),
sp.release, sp.subrelease,
sp.version,
(SELECT pn.fixed_version FROM package_notes AS pn
WHERE pn.bug_name = st.bug_name
AND pn.package = sp.name AND
(pn.release = sp.release OR (pn.release = '' AND fixed_version != ''))),

[sqlite] Is a transaction in progress?

[Florian Weimer]

On 05/11/2015 09:11 AM, Florian Weimer wrote:
> Is there a way to discover if a transaction is in progress for a given
> database handle, without committing or aborting the transaction?

Found it?it's sqlite3_get_autocommit.  The name is not really obvious,
though.

-- 
Florian Weimer / Red Hat Product Security

[sqlite] Is a transaction in progress?

[Florian Weimer]

Is there a way to discover if a transaction is in progress for a given
database handle, without committing or aborting the transaction?

-- 
Florian Weimer / Red Hat Product Security

[sqlite] Thoughts on storing arrays of complex numbers

[Florian Weimer]

* Steven M. McNeese:

> I would serialize to JSON and store as a string.

You need to be careful about the choice of JSON library, many of them
lose information when converting doubles to JSON.

[sqlite] Why is empty string not equal to empty blob?

[Florian Weimer]

* Paul:

> Maybe this question was already asked and explained.
> Or maybe it is documented somewhere (could not fiund it).
> Sorry, if this is the case, but why does
>
> SELECT '' = x'';
>
> yields 0?

In SQLite, string literals have manifest type string.  This is
different from SQL, where string literals have unknown type and are
automatically cast as needed.

sqlite> SELECT '1' = 1;
0

I think SQL92 requires the expression to be true.

Re: [sqlite] Database corruption issue

[Florian Weimer]

* Grzegorz Sikorski:

> We do fsck on the startup. Occasionally, there are some errors, so we
> decided to do 'fsck -p' to fix them before mounting the
> filesystem. Here is how we then mount the actual filesystem:
> /dev/mmcblk0p2 on /media/DATA type ext4
> (rw,relatime,barrier=1,journal_checksum,nodelalloc,data=journal,usrquota)
>
> I am not an expert in ext4, so I am not sure it is the best way to
> mount, but as far as I read on some forums, this is the safest (and
> slowest!) option.

It is, but it also less tested than data=writeback.  Have you
backported any ext4 fixes (such as commit 2d859db3e4) to your kernel?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Goodram C50 SSD slow in SQlite benchmark

[Florian Weimer]

* piotr maliński:

> The usb3 case has asmedia chip and phoronix sqlite insert benchmark runs
> around 6 times faster than when running via sata. Some benchmarks like
> untaring, some iozone benchmarks do not differ so I'm suspecting controller
> or OS issue. Does sqlite insert uses some specific I/O operations?

Yes, write barriers.  Many USB SATA adapters do not handle them
properly, leading to significant speed-ups and increased risk of data
loss.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is is possible to optimize this query on a very large datatbase table?

[Florian Weimer]

* Frank Chang:

> This table could potentially hold 10 to 40 million rows. We are
> using the following query to obtain the minumum rowid for each
> unique LastName:
>  
> sqlite> explain query plan select t1.FieldName,t1.rowid from BlobLastNameTest 
> t1
>  GROUP BY t1.FIELDNAME HAVING t1.rowid = (SELECT MIN(rowid) FROM 
> BlobLastNameTes
> t where FieldName = t1.FIELDNAME);

You could try

  SELECT FieldName, rowid FROM BlobLastNameTest ORDER BY FieldName, rowid;

and perform the aggregation in the application.  Perhaps this is faster.

A better query needs support for DISTINCT ON, which SQLite lacks
AFAIK.  But speed will obviously be limited because you cannot avoid
traversing the index for the whole table.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite-dev] Is there an option to not lock during transaction?

[Florian Weimer]

* Pavel Ivanov:

> To answer your original question: if you disable shared cache, start
> reading transaction on one connection and start writing transaction on
> the other connection then you'll be able to read on the first
> connection database in the state it was before writing transaction.
> But if your writing transaction will grow too big then it won't be
> able to proceed until reading transaction is finished.

Doesn't enabling WAL mode address the transaction size limit?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22

[Florian Weimer]

* Richard Hipp:

> I don't think it makes sense in SQL (not just SQLite but SQL in
> general) for an aggregate query to return columns that are not in
> the GROUP BY clause.

Isn't this just what PostgreSQL implements as DISTINCT ON?  Then it
*is* useful.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] IS [NOT] DISTINCT FROM

[Florian Weimer]

In standard SQL, you cannot write

  expr1 IS expr2

(Only "expr1 IS NULL" is permitted.)

You have to write:

  expr1 IS NOT DISTINCT FROM expr2

Are there any plans to add the second syntax to SQLite?  This version
also supports comparing multiple values at once, as in:

  (expr1, expr2) IS NOT DISTINCT FROM (expr3, expr4)

Arguably,

  expr1 IS expr3 AND expr2 IS expr4

is much clearer, but it is not portable.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VACUUM can actually increase the DB file?

[Florian Weimer]

* Oliver Schneider:

> just a few minutes ago I ran a VACUUM on a DB file and the size before
> was 2089610240 and afterwards 2135066624. Is this normal?

This is just typical behavior with B-trees because the fill factor
almost always changes when they are rebuilt.  It seems that SQLite
doesn't try to maximize the fill factor during index creation, so this
behavior is expected for many database files.

Choosing the best fill factor is often difficult.  If additional keys
are inserted into an index with a near-100% fill-factor, many page
splits are required.  But for indices on archive tables, a way to
create the most compact representation could be desirable.  Some
systems (such as PostgreSQL) make the fill factor a per-index
configuration parameter, and they also construct the B-tree from a
sorted representation, which makes it possible to obtain compact
indices if the user requests this (it's also faster than doing
repeated B-tree inserts).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database corruption on Linux ext3

[Florian Weimer]

* D. Richard Hipp:

> An appliance manufacturer has discovered a database corruption issue  
> on Linux using ext3.   The issue is documented here:
>
>  http://www.sqlite.org/draft/lockingv3.html#ext3-barrier-problem

It's a generic Linux problem, not an ext3-specific issue.  Until
recently, the Linux block layer had no concept of a sync operation.
Linux basically assumed that all writes were synchronous and ordered,
which they are not if your hardware has (non-transparent) write
caches.  It did not disable write caching by default, either.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL in SQLite

[Florian Weimer]

* Richard Hipp:

> Good to know.  Would moving to shm_open() help?

I think it's difficult to clean up the shared memory segments when a
database which had opened the database crashes.  And access control
would be very hard to get right, too.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed: drop support for LinuxThreads

[Florian Weimer]

* D. Richard Hipp:

> If we drop support for (the non-standard, non-compliant) LinuxThreads  
> threading library and instead support only standard Posix threads  
> implemented using NPTL, beginning with SQLite release 3.7.0, what  
> disruptions might this cause?

There are several variants of NPTL with varying feature sets on
different GNU/Linux architectures.  But I think you are only after the
behavior of fcntl locks, and those are the same across architectures.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed new sqlite3_open_v3() interface

[Florian Weimer]

* D. Richard Hipp:

> Question 2:  Are there other foibles that we could correct using  
> sqlite3_open_v3?

You could default the page size to the file system block size (if it
can be determined).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Berkeley DB adds SQL using SQLite API !!

[Florian Weimer]

* sub sk:

> Somehow no one seems to  have mentioned it on this mailing list so far!?
> Here is the scoop...
>
> On March 23, Oracle announced the latest release of Oracle® Berkeley
> DB - 11g Release 2 -  which introduces a new SQL API, based on lo and
> behold, SQLite v3 API. What this means is that all tools that work
> with SQLite will also work with Oracle Berkeley DB.

How do we resolve the symbol clashes?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ANN: SQLite 3.6.16.C#

[Florian Weimer]

* Noah Hart:

> I am pleased to announce that the C# port is done to the point where others
> can look at it.

Congratulations!

(Is there something similar for Java, not using JNI nor NestedVM? 8-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.

[Florian Weimer]

* Robel Girma:

> Example, 5000 users connect to our server every 10 seconds and each
> time they connect, I need to update a table with their IP and
> Last_connect_time.

That's 500 commits per second, right?  If you need durability, you can
get these numbers only with special hardware.

SQL Server might offer better performance, assuming it can group
commits.  However, it might be easier to to just keep the data in
memory and log the changes to disk.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Join performance in SQLite

[Florian Weimer]

* D. Richard Hipp:

> One of the criticisms of SQLite is that it is slow to do joins.  That  
> is true if SQLite is unable to figure out how to use an index to speed  
> the join.  I was under the impression that SQLite actually did a  
> fairly reasonable job of making use of indices, if they exist.  But  
> without indices, an k-way join takes time proportional to N^k.
>
> Do other SQL database engines not have this same limitation?  Are  
> MySQL and PostgreSQL and Firebird and MS-SQL and Oracle creating  
> phantom indices on-the-fly to help them do joins faster, for example?   

PostgreSQL roughly does one of the following (when dealing with a
two-way join):

  * If one side of the join is estimated to be a small set, PostgreSQL
performs a sequential scan on it, hashes it, and joins the other
table in a hash join.

  * If both sides are large, each side is sorted, and a merge join is
performed.

Things go horribly wrong if the estimates are off and the wrong plan
is picked.

There's also a nested loop join (which would be what SQLite does), but
I haven't seen it in recent version.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_create_collation_v2 and SQLITE_UTF16_ALIGNED

[Florian Weimer]

* D. Richard Hipp:

> On May 3, 2009, at 11:15 AM, Florian Weimer wrote:
>
>> The documentation suggests that I can pass SQLITE_UTF16_ALIGNED.
>> However, the logic in main.c:createCollation() assumes that
>> SQLITE_UTF16_ALIGNED is ORed with another encoding flag value
>> (presumably SQLITE_UTF16).  If I specify SQLITE_UTF16_ALIGNED alone, I
>> end up with a crash due to a double free bug inside SQLite.
>>
>> (This has been observed with SQLite 3.6.13.)
>
> This has already been fixed.  See
> http://www.sqlite.org/cvstrac/chngview?cn=6558

Thanks.  But isn't this a backwards-incompatible API change?  The
changes to sqlite/src/test1.c suggest this.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_create_collation_v2 and SQLITE_UTF16_ALIGNED

[Florian Weimer]

The documentation suggests that I can pass SQLITE_UTF16_ALIGNED.
However, the logic in main.c:createCollation() assumes that
SQLITE_UTF16_ALIGNED is ORed with another encoding flag value
(presumably SQLITE_UTF16).  If I specify SQLITE_UTF16_ALIGNED alone, I
end up with a crash due to a double free bug inside SQLite.

(This has been observed with SQLite 3.6.13.)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strict affinity again

[Florian Weimer]

* D. Richard Hipp:

> SQLite is strongly typed,

Curiously, it's even more strongly typed than SQL:

sqlite> SELECT 1 = '1';
0

fw=> SELECT 1 = '1';
 ?column?
--
 t
(1 row)

(In SQL, quoted values are not of string type, but their type is
inferred from context.)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FAQ on threading outdated

[Florian Weimer]

It seems to me that the FAQ entry on threading is outdated and should
point to  instead.

Due to locking issues in concurrent, garbage-collected languages,
last-resort finalization of statement and database objects has to
happen in a separate finalization thread.  The documentation mentioned
above does not explicitly mention it, but I assume this is safe in
SQLITE_CONFIG_MULTITHREAD mode (provided that the finalizer thread
does not touch objects used by the main thread).

It would also be helpful to know in what version the
SQLITE_CONFIG_MULTITHREAD option was added to SQLite (or if the
previous threads-afe behavior matched SQLITE_CONFIG_MULTITHREAD).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] General index information

[Florian Weimer]

* Igor Tandetnik:

> L B  wrote:
>> The explanation you made is only valid for sqlite or
>> for SQL in general?
>
> For SQL in general.

True, but there are some database engines that support skip-index
scans, so if there are only few different values of X, say x_1,
... x_k, you've got an index on (X, Y) and want to find all y, the
query is executed as lookups for (x_1, y), ..., (x_k, y).

(Obviously, this is only beneficial if k is not too big.)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Destruction callback for sqlite3_aggregate_context

[Florian Weimer]

* Florian Weimer:

>> On Nov 19, 2008, at 9:59 PM, Florian Weimer wrote:
>>
>>> In order to avoid a resource leak, I think I need some sort of
>>> callback when the memory allocated by sqlite3_aggregate_context is
>>> freed by SQLite.
>>>
>>> As far as I can see, it is not guaranteed that the xFinal function
>>> specified in sqlite3_create_function will be called in all cases, so
>>> this is not the right place to free data referenced by the aggregate
>>> context.
>>
>> It is guaranteed that the xFinal callback will be called in all cases.
>
> Good.  I checked that it's called when sqlite3_interrupt() is invoked,
> so for my purposes, it should be fine.

However, in both cases (sqlite3_interrupt and sqlite3_result_error), I
only get an SQLITE_ERROR with the default error message back from
SQLite.  I think we've discussed this before. 8-/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Destruction callback for sqlite3_aggregate_context

[Florian Weimer]

* Dan:

> On Nov 19, 2008, at 9:59 PM, Florian Weimer wrote:
>
>> In order to avoid a resource leak, I think I need some sort of
>> callback when the memory allocated by sqlite3_aggregate_context is
>> freed by SQLite.
>>
>> As far as I can see, it is not guaranteed that the xFinal function
>> specified in sqlite3_create_function will be called in all cases, so
>> this is not the right place to free data referenced by the aggregate
>> context.
>
> It is guaranteed that the xFinal callback will be called in all cases.

Good.  I checked that it's called when sqlite3_interrupt() is invoked,
so for my purposes, it should be fine.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Destruction callback for sqlite3_aggregate_context

[Florian Weimer]

In order to avoid a resource leak, I think I need some sort of
callback when the memory allocated by sqlite3_aggregate_context is
freed by SQLite.

As far as I can see, it is not guaranteed that the xFinal function
specified in sqlite3_create_function will be called in all cases, so
this is not the right place to free data referenced by the aggregate
context.

What can I do instead?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Signaling errors from collation functions

[Florian Weimer]

I'm working on something which is supposed to be a foolproof interface
to SQLite.  During that, I discovered that SQLite lacks a way to
return errors from a user-defined collation function.  Is there
something I've missed?

Right now, I call sqlite3_interrupt() to signal at least something
(after obtaining the database handle in a somewhat painful way).  When
calling into SQLite and getting back SQLITE_INTERRUPT, I could do some
magic and recover the original error message, but for some types of
errors, this won't work (memory allocation failures, for instance).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] cidr data type

[Florian Weimer]

* Jay A. Kreibich:

>   Yeah, I screwed that up.  I was too caught up on the other error.
>
>   In the original function (-1 << network_size) returns the wrong bit
>   mask.  For example, 10.0.0.0/8 should return a netmask of 255.0.0.0,
>   or 0xFF00.  The original function will return 255.255.255.0,
>   which is a /24 mask.
>
>   My mind was thinking "you need to flip that", but did the wrong
>   thing.  We're looking for (ip_addr & (~0 << (32 - network_size))).

That's why it's called network_size and not prefix_length, I think.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] cidr data type

[Florian Weimer]

* Jay A. Kreibich:

>> Is this Java or C?  For C, this breaks if network_size == 32.
>
>   It breaks for everything except network_size == 16.

I was alluding to the fact that a popular architecture implements
modulo-32 shifts for 32-bit integers (and modulo-64 shifts for 64-bit
integers), for example:

sqlite> SELECT 1 << 64;
1
sqlite> 

(I wasn't aware that SQLite supports bitwise operators.)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] cidr data type

[Florian Weimer]

* Dennis Cote:

> This last function can be implemented using bit manipulation operators 
> in SQL or in a custom function in C.
>
> containedIn(ip_addr, network_addr, network_size)
>
> can be replaced by
>
> nework_addr == (ip_addr & (-1 << network_size))
>
> which will be true if the IP address is in the network.

Is this Java or C?  For C, this breaks if network_size == 32.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is the standard way to store dates and do operations with dates please?

[Florian Weimer]

* Dennis Cote:

>  From the wikipedia article you cited:
>
> Note: although many references say that the Julian in "Julian day" 
> refers to Scaliger's father, Julius Scaliger, in the introduction to 
> Book V of his Opus de Emendatione Temporum ("Work on the Emendation of 
> Time") he states, "Iulianum vocavimus: quia ad annum Iulianum dumtaxat 
> accomodata est", which translates more or less as "We have called it 
> Julian merely because it is accommodated to the Julian year." This 
> Julian refers to Julius Caesar, who introduced the Julian calendar in 46 BC.
>
> I can't vouch for the veracity of this note, but he he seems to know 
> what he is talking about and has given what is purported to be a 
> reference from the original author that backs his claim (as best I can 
> tell from the quoted Latin and its translation). As always you have to 
> take everything on wikipedia with a grain of salt, but this looks 
> authoritative.

"Calendrical Calculations" by Reingold and Dershowitz has the following
to say about the matter:

| It is often claimed [...] that Scaliger named the [Julian] period [a
| method of counting years] after his father, the Renaissance physician
| Julius Cæsar Scaliger, but this claim is not borne out by examination
| of Scaliger's great work, /De Emendatione Temporum/, from which the
| section quote above [Iulianam vocavimus: quia ad annum Iulianum
| dumtaxat accommodata est] is taken.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Most widely deployed?

[Florian Weimer]

* Aristotle Pagaltzis:

> * Shawn Wilsher <[EMAIL PROTECTED]> [2008-02-21 20:00]:
>> > Every copy of Firefox 3 contains a copy of SQLite.
>> And Firefox 2 ;)
>
> Really? What is it used for?

It's used to store data used by the client-side URL classifier
("phishing filter").
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Checking that a row exists?

[Florian Weimer]

* Gilles Ganault:

> Is this the standard way to check that a row exists in PHP/SQLite, or
> is there a better syntax?

> $query = "SELECT count(*) FROM contacts WHERE contacts_phones_tel GLOB '%s*';

This query stops after the first match:

  SELECT 1 FROM contacts WHERE contacts_phones_tel GLOB ? LIMIT 1

> $query = sprintf($query,$row['calls_phones_tel']);

Danger: SQL injections lurks here.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VFS memory leak : During lock / unlock operations

[Florian Weimer]

* Dennis Cote:

> The OP said they were measuring an excess of lock calls. That would 
> imply that SQLite is locking files it has already locked. Is that 
> possible with the POSIX APIs?

SQLite uses fcntl-based locks, which keep a separate lock for each byte
in a file (or, more precisely, any byte that you can address with
off_t).  You can lock and unlock ranges, and if the sizes do not match
(which isn't a requirement), the number of calls do not need to match.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed change to sqlite3_trace()

[Florian Weimer]

> Legacy applications should continue to work.  You might get a 
> compiler warning about a type mismatch on the 2nd parameter to
> sqlite3_trace().  But calling a 2-parameter function with 3
> parameters is suppose to be harmless in C.

It's not allowed by the standard, so it will almost certainly break on
some platforms.  The change also breaks C++ source code compatibility.

Please add a new function, perhaps with a flag argument to control which
events are traced (something that might make sense anyway).

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Proposed sqlite3_initialize() interface

[Florian Weimer]

> This is still just an idea.  If you think that adding a new
> required sqlite3_initialize() interface would cause serious
> hardship for your use of SQLite, please speak up now.

It requires changing and recompiling all applications linking to it.
This is a bit annoying for distributions.  Debian would probably have to
ship an sqlite3 and sqlite4 (?) package until the transition is done.

I think the list of affected functions you posted is overly pessimistic;
most of these functions probably do not need a fully initialized
library.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Race condition -- fixed?

[Florian Weimer]

> This is true of SQLite because isolation in SQLite
> is "SERIALIZABLE".  This is the highest level of isolate
> provided by SQL.  Most client/server database engines
> by default implement "READ COMMITTED".  The value of
> "balance" might change between the SELECT and the
> UPDATE in MySQL, for example.  (I'm less clear about
> what happens in PostgreSQL and Oracle.  The point is
> that your mileage may vary so be cautious.)

PostgreSQL uses "READ COMMITTED" by default as well (each statement
acquires a new snapshot).

However, PostgreSQL does not implement true SERIALIZABLE semantics
because of the phantom problem (transaction outcome depends on the
absence of certain rows--but no lock can be acquired on them, so they
might be added by a parallel transaction, resulting in a
non-serializable history).

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite testing with Boundschecker

[Florian Weimer]

> So apparently boundschecker is upset because SQLite is merely making
> a copy pointer to previously freed memory.  There really is no harm
> in this.  Nothing bad can happen unless the program actually tries
> to dereference the pointer - which it never does.

I don't think GCC makes this guarantee.  The standard certainly doesnt
require this; copying a pointer value after the object it points to
has been freed results in undefined behavior.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] What's this?

[Florian Weimer]

* Ion Silvestru:

> This is a message I received:

Your mailserver rejected a message, probably with good reason:

> <[EMAIL PROTECTED]>:
> 217.26.144.4 failed after I sent the message.
> Remote host said: 550 This message contains a virus (HTML.Phishing.Pay-131)

ezmlm, the mailing list management software used by sqlite.org,
assumes this was in error, and tells you how to retrieve the message.
Unfortunately, ezmlm is no longer maintained and was written long
before spam, email worms and phishing messages turned into such a
nuisance.  Some of its assumptions about mail traffic are just plain
wrong today.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite - how to protect the data

[Florian Weimer]

* mxs:

> If anybody knows hot to ensure that the data is not readable without the
> front end I would appreciate it.

You need to turn your application into some kind of web-based service
which is actively monitored for misuse.  

Anybody who's got a copy of the application can reverse-engineer it
and reimplement the decryption routines.  There is no way around that.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Abuse of the SQLite website

[Florian Weimer]

* Terry Jones:

> Given 50K attempts in 5 hours, this is either a bug somewhere or it's
> automated, likely the latter.

I've seen broken proxies which acted as accidental traffic amplifiers.
It's not necessarily a deliberate attack.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Fix for sqlite3.h in version 3.3.10

[Florian Weimer]

> Having SQLITE_TRANSIENT point to a real function is perhaps a
> good idea.  The problem is such a change would break backwards
> compatibility of the API.  Somebody who compiled against an
> older SQLite and who drops in a newer DLL with this change will
> find that their code no longer works.  This is something we work
> very hard to avoid.

You could redefine SQLITE_TRANSIENT and still check for the older -1
value in the SQLite code (where you've got better control what the
compiler does).

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Sqlite design question

[Florian Weimer]

* Ken:

>  Would the reader be blocked by the writer?

Yes.

> Would the writer be blocked by the reader?

Yes.

However, depending on the size of the transactions this may not be an
issue.

>  I guess I'm unclear what I can/cant do using sqlite and how to gain
>  as much performance as possible.

If your database isn't too large, and you aren't running on Windows,
you could make a copy of the database before updating it, so that
readers and the writer work on different databases.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite web site performance

[Florian Weimer]

* w. wg:

> The following is from ibm developerworks site:
>
> Its creator conservatively estimates that it can handle a Web site
> with a load of up to 100,00 hits a day, and there have been cases
> where SQLite has handled a load 10 times that.
>
> origin link:
> http://www-128.ibm.com/developerworks/opensource/library/os-sqlite/

Ah, I thought you were saying that IBM DeveloperWorks only get 10,000
visits (or hits) per day.  This looks like a simple typo to me (and
the number is probably a wild guess anyway).  A couple of requests per
second are absolutely doable with SQLite.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite web site performance

[Florian Weimer]

* w. wg:

> The documents on sqlite official site says that sqlite can works fine
> if a site's traciffic is under 10 click  per day. But I found a
> lot of sites ( including IBM developer network) say the number is only
> 1 per day.

Your are probably confusing the number of hits and visitors.  I'm sure
the IBM developer network has more than 10,000 hits per day.  Given
the number of individual pages, search engines alone will generate
more traffic than that.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] 2PC / two-phase commit?

[Florian Weimer]

> SQLite uses a 2-phase commit internally when it is
> making changes to two or more ATTACHed database files.

Are there any plans to expose this at the API level?  This could be
useful for implementing reliable data exchange with other database
systems.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite and McAfee Anti-Virus

[Florian Weimer]

> After sending my email I realized the blame is partly with
> me because SQLite is putting is own name on TEMP files.
> I changed this so that the prefix is now "etilqs_" instead
> of "sqlite_".  That should help head off future troubles.

I think this is worse.  Veritas does something similar which makes
people believe their system has been hacked (IIRC, they use something
like .sEcUrItY names, way too cute).

Let's hope the situation can still be resolved amicably.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] database corrouption during power failure

[Florian Weimer]

* jayanth KP:

>Please can anybody respond to this question. I am able to hits is
>issue pretty often now...

You should check that your operating system and hardware actually
flush buffers when told to do so.  Here's a test you can run:

http://cvs.danga.com/browse.cgi/wcmtools/diskchecker/diskchecker.pl?rev=HEAD

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Avoiding Fragmentation of SQLite database file ???

[Florian Weimer]

* A. Pagaltzis:

>> On Windows, perhaps. On most UNIX systems, this is very hard
>> to do and often not supported at all by the file system.
>
> Then again, Unix filesystems tend to be designed such
> that as long as there is sufficient free space on the
> device, fragmentation will remain insignificant.

It's been this way on Windows for a couple of years, too. 8-)

All this doesn't help that much if you've got a large file which grows
over time.  For example, running "filefrag" on a Monotone database
stored on an ext3 file system before and after a small "pull" results
in:

monotone.db: 9257 extents found, perfection would be 1 extent
monotone.db: 9403 extents found, perfection would be 1 extent

This is with a page size of 4096 bytes.  The default of 1024 is
probably even worse.

Re: [sqlite] Avoiding Fragmentation of SQLite database file ???

[Florian Weimer]

* Jay Sprenkle:

> On 6/14/06, RohitPatel <[EMAIL PROTECTED]> wrote:
>>
>> Any solution to that (which does not force end-user of app to manage sqlite
>> file fragments or to defragment disk) ?
>
> A scheduled task or cron job is trivial to implement and does not
> add any extra work for the end user.

On Windows, perhaps.  On most UNIX systems, this is very hard to do
and often not supported at all by the file system.

Re: [sqlite] Multithreading. Again.

[Florian Weimer]

> Remember, that the operating system bug that is causing all the
> multithreading grief is that file locks created by one thread
> cannot be reliably removed or modified by a different thread.
> So if a statement acquires a lock on the database file in one
> thread and you try to finalize the statement in a different
> thread, the finalization would involve releasing the lock in
> a different thread from which it was acquired - an operation
> that silently fails on certain Linux kernels.

Hmm.  The main reason why I'm asking is that I'd like to cleanly shut
down the database even when the application terminates due to an
unhandled exception or something like that.  The try/finally approach
is often helpful, but it won't help you if all uses of the handle are
statically nested (as far as the call graph is concerned).  The
garbage collector (or, in my case, a special cleanup handler) could
deal with the remaining open statement or database handles.

If RH9 is the main remaining problem OS, I can probably live with
that, especially if none of the RHELs is affected.

Re: [sqlite] Multithreading. Again.

[Florian Weimer]

> As long as no prepared statements are outstanding, you should
> be safe moving sqlite database connections across threads, as
> of version 3.3.1.  The rules are not really quite that strict,
> but the exact rules are more complex and this strict rule
> gives you an extra margin of safety.

Is it possible to finalize statements in a separate thread?  This
would be a rather important feature because on most multi-threaded
VMs, user-defined finalizers (which would be used to clean up SQLite
objects which are no longer used) run in a separate thread.

Re: [sqlite] Purging the mailing list roles. Was: Please Restore Your Account Access

[Florian Weimer]

* Clay Dowling:

> [EMAIL PROTECTED] at least does have an auto-responder.  The message
> contains the text of any message sent to it, appended to the bottom of
> the email.  That would serve to automatically validate the check
> email.

In my experience, the real problem with ezmlm's subscription mechanism
is that it embeds the subscription cookie in the Reply-To: header.  If
you put it into the subject and require that it's preserved by the
subscriber (like other mailing list managers do), such accidents are
much less likely.

Re: [sqlite] Issue with sqlite3_result_error inside aggregatefunction

[Florian Weimer]

* Nathan Kurz:

> On Wed, Nov 16, 2005 at 01:16:40PM +0100, Florian Weimer wrote:
>> * D. Richard Hipp:
>> 
>> > If an error occurs in a step function, record that fact in
>> > the aggregate context.  Then when the finalizer is called,
>> > check the error flag in the context and call sqlite3_result_error
>> > at that point if it is appropriate to do so.
>> 
>> Does this really work?  According to my experiments, the fact that
>> sqlite3_result_error has been called from the finalizer is ignored.
>> The sources seem to lack the appropriate check, but I'm not sure.
>
> I haven't looked at the code that handles it, but in practice it seems
> that an error returned from the step function is noticed and aborts
> the query, but gives only the non-descript error message: "SQL logic
> error or missing database".  An error returned from the final function
> is ignored, and the string of the error message is used as the result.

Thanks for the confirmation, so I'm not imagining things.
Unfortunately, signaling errors from the step function causes a
segmentation fault on my system (actually, I tested this only through
the apsw Python wrapper, which indirectly sparked this thread).

It's not a terribly important issue for me at this stage, I just
spotted it while writing test cases for some of my own code.

Re: [sqlite] Issue with sqlite3_result_error inside aggregatefunction

[Florian Weimer]

* D. Richard Hipp:

> If an error occurs in a step function, record that fact in
> the aggregate context.  Then when the finalizer is called,
> check the error flag in the context and call sqlite3_result_error
> at that point if it is appropriate to do so.

Does this really work?  According to my experiments, the fact that
sqlite3_result_error has been called from the finalizer is ignored.
The sources seem to lack the appropriate check, but I'm not sure.

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Florian Weimer]

> Please let me restate the proposed change:  I desire to change
> SQLite so that it no longer distinguishes between integer and
> real.

Put this way, this seems to be a good idea, provided that there isn't
a performance penalty.  Most of the numbers I store in SQLite
databases are small integers, and it would be a shame if things were
slower as a result.

There is a problem, though, if you use SQLite from a language with a
different form of manifestant types.  In current Python versions, for
example, integers and floats are a different type, and integer
division yields integers (but the Python developers plan to change
this) -- and there is no separate division operator for floats.
Currently, the Python type is preserved when writing an integer or
float to the database, after your change, it won't.  The fix on the
Python side is not particularly pleasant: you must add calls to the
float function before you can use a value returned from the database
in a division operation.

(Ruby has a similar problem, it seem.  Most Lisps and Perl don't.
Statically typed languages are unaffected by the change.)

Re: [sqlite] Re: OCaml binding for SQLite 3

[Florian Weimer]

* Toby Allsopp:

> On 30 Oct 2005 at 03:14 NZST, Florian Weimer wrote:
>
>> Is it true that there isn't yet a binding to SQLite versoin 3 from
>> Objective Caml?
>>
>> (I only found one for version 2.)
>
> There seem to be a couple of different ocaml-sqlite3 bindings around:
>
> http://www.imada.sdu.dk/~bardur/personal/45-programs/ocaml-sqlite3/
> http://metamatix.org/~ocaml/ocaml_sqlite3.html
>
> I found these by typing "ocaml-sqlite3" into Google.  

I don't use Google anymore. 8-( Thanks for the pointer.

I completely forgot that monotone-viz also includes a binding
("mlsqlite").  Hmm, it seems that each one lacks something one of the
others offers.

[sqlite] OCaml binding for SQLite 3

[Florian Weimer]

Is it true that there isn't yet a binding to SQLite versoin 3 from
Objective Caml?

(I only found one for version 2.)

Re: [sqlite] Optimal page size

[Florian Weimer]

* Chris Schirlinger:

> 4096 in Win32 machines and 1024 on nix ones (I think, I am no expert 
> on Unix style OS's)

This depends on the file system.  On Linux on x86, it's typically 4096
bytes.

According to my performance measurements, switching from page size of
1024 bytes to 4096 bytes gives a measurable performance improvement
(but nothing dramatic, for my workload).

Re: [sqlite] Convert SQLite to Java

[Florian Weimer]

* Randy Ray:

> Java libraries linked with C (via JNI) generally have to run with the exact
> same version of Java they were built with.

Uh-oh, Sun advertises something else.

I can understand that you must exactly match JVM versions for
certified configurations, but this isn't true even if you don't use
native code.

Re: [sqlite] Share an sqlite3 struct between multiple threads

[Florian Weimer]

* Christian Smith:

> IMHO, SQLite should, however, only open a single file per database (based
> on inode) which should allow threads to override each others locks as the
> locks will be on a single file.

I think you need multiple file descriptors, otherwise you'd have to
use pread for accessing pages (or wrap each lseek/read call in a
mutex).

Apart from that, some kind of per-process table which stores the
device/inode pair and the lock status information should do the trick.
The sqlite3 struct would contain an index into that table.  Each time
a file lock has to be changed, the thread acquires a global mutex, and
updates the information in the global lock table.  If necessary, POSIX
file locks are acquired or released.

Of course, you can no longer access the same SQLite3 database using
two sqlite3 objects in the same thread without risking a deadlock, but
I don't think this is a major problem.

Re: [sqlite] Re: philosophy behind public domain?

[Florian Weimer]

* D. Richard Hipp:

> This would be a problem for any citizen of Germany or Austria
> that wanted to contribute code to the SQLite project.  I cannot
> see that this would ever be a problem for an SQLite users.

Yes, of course.

> Can citizens of Germany and Austria assign their copyright interest
> to third parties?

The exploitation rights can be transferred.  These rights are the
important rights as far as software is concerned, but an author cannot
disclaim or transfer the non-commercial rights (being named as the
author, possiblity to prevent distortion and misrepresentation, and so
on).

> If so, then if you want to contribute code to SQLite, just assign
> the copyright to me and I will then dedicate the code to the public
> domain, which I can do since I am not a citizen of Austria or
> Germany.

The contributor can still exercise his or her right to be named as an
author, for example, so the result wouldn't be truly public domain.