[sqlite] Skip scan. Was: full table scan ignores PK sort order?

[Richard Hipp]

On 3/2/15, James K. Lowden  wrote:
>> "CREATE TABLE IF NOT EXISTS test
>> (
>> a  INTEGER,
>> b  INTEGER,
>> c INTEGER,
>> d  BLOB,
>> PRIMARY KEY (a, b DESC)
>> );"
> ...
>> "SELECT * FROM test WHERE b < ? AND c > ?;"
>
> A full table scan is executed because no index supports the search
> criteria.  Specifically, the first member of the primary key, "a", is
> not mentioned in the WHERE clause.

James is correct.

However, SQLite is sometimes able to use an index of the form PRIMARY
KEY(a,b) with a WHERE clause of just b=:N.  It will use the index to
skip forward to each cluster of b=:N values.  If there are too many
distinct a values, then a full table scan turns out to be faster, so
this technique is only used if the number of distinct a values is
small.  The only way that SQLite will ever know that there are few
distinct a values is if you have run ANALYZE on the database.  See
https://www.sqlite.org/optoverview.html#skipscan for additional
information.

But that rule does not apply in this case, so James is still correct.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] PRAGMA Synchronous safety

[Doug Nebeker]

Thank you for your responses Simon and Richard.  To your questions:

> Are you using any PRAGMAs apart from "PRAGMA synchronous" ?

PRAGMA temp_store=1 (file)
PRAGMA cache_size=2000
PRAGMA page_size=4096
sqlite3_soft_heap_limit( 1GB )

Those are the only non-default settings.

> Are you testing the result codes of /all/ your sqlite3_ calls

Yes.  But I usually don't see the full log to see if something has happened 
earlier.  I'll have to track down a log and see if the corruption error is the 
first one that happens.

> are you replacing it with a known good one before continuing

Yes.  Once we get the report, the user has to delete the file and start fresh 
with an empty database.

> Depends on whether you are using WAL mode or not.

I am not using WAL.

Doug


-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
Sent: Monday, March 2, 2015 3:47 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] PRAGMA Synchronous safety


On 2 Mar 2015, at 4:45pm, Doug Nebeker  wrote:

> 1. Is setting synchronous to FULL necessary to prevent these corruption 
> problems?

You should not be getting this corruption problem, so I don't know whether FULL 
would cure it.

> 2. NORMAL is much slower than NONE.  Is FULL much slower than NORMAL?

Sorry but it's too hard to predict 'much' since the amount of time taken varies 
with different versions of the OS, width of the data bus, disk format, and 
other such things.  All you can do is try it on your own specific setup.

In the meantime ...

Are you using any PRAGMAs apart from "PRAGMA synchronous" ?

Are you testing the result codes of /all/ your sqlite3_ calls to check that 
they're SQLITE_OK ?  Sometimes it's not the expected call which causes the 
corruption, or which first returns an error because the database is corrupt.

Once the database is reported as corrupt, are you replacing it with a known 
good one before continuing ?  Corruption is not automatically fixed and once a 
database is corrupt it will remain corrupt until something is done about it.

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

[sqlite] List duplication

[Mike Owens]

Okay, I blocked the sqlite-users at sqlite.org address in the to address
so if it is sent alone, it will be blocked.

On Mon, Mar 2, 2015 at 9:46 PM, Mike Owens  wrote:
> Oh okay. I see. I'll look into it.
>
> On Mon, Mar 2, 2015 at 9:23 PM, Darren Duncan 
> wrote:
>>
>> So in that case, still have the SQLite mail server reject messages to the
>> old list rather than forwarding them, and let the problematic MUAs deal with
>> it. The key thing is that by not forwarding but rejecting, the mail server
>> isn't sending out 2 copies of messages directly, and the rejecting is
>> reminding people to pay attention until the issue as a consequence goes
>> away.  Thus any explicit Reply-To headers can be left unmunged by the list
>> server. -- Darren Duncan
>>
>>
>> On 2015-03-02 7:10 PM, Mike Owens wrote:
>>>
>>> The problem is that this is the very bone of contention in the reply-to
>>> religious war. Is it not? I may be wrong, but I thought this is the very
>>> setting that people get so defensive about changing. As we have it now,
>>> people have a suitable default pointing back to the (correct) list but
>>> also
>>> the freedom to change the reply-to header should they want to. If we
>>> strip
>>> the reply-to header in order to correct for the problematic MUA's, then
>>> the
>>> latter freedom is lost. And if I remember correctly, some people get very
>>> angry about this.
>>>
>>>
>>> On Mon, Mar 2, 2015 at 8:18 PM, Darren Duncan 
>>> wrote:
>>>
 On 2015-03-02 6:14 PM, Mike Owens wrote:

> On Mon, Mar 2, 2015 at 5:27 PM, R.Smith  wrote:
>
>> Ah, thank you, all makes sense now. If you change the first option to
>> YES
>> then nobody else's quirky reply-to headers will get into the list, and
>> the
>> second option remains as is (it should be setting the standard
>> @mailinglists reply-to field) - this should solve the duplication
>> issue,
>> but if it is disagreeable to anyone, more consideration is needed.
>>
>
> I almost don't want to even speak of this for fear that this issue will
> raise it's ugly head again. Per the Mailmain documentation (
> http://www.gnu.org/software/mailman/mailman-admin/node11.html):
>
> Beware! Reply-To: munging is considered a religious issue and the
> policies
>
>> you set here can ignite some of the most heated off-topic flame wars
>> on
>> your mailing lists. We'll try to stay as agnostic as possible, but our
>> biases may still peak through.
>>
>>
> That's as much as I'll say about that.
>

 Well it doesn't have to be complete munging, rather just enough munging
 to
 remove references to the old mailing list name. -- Darren Duncan
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>
> --
> Mike



-- 
Mike

[sqlite] Test

[Mike Owens]

This is a test. The sqlite-users at sqlite.org address has been blocked.
Only sqlite-users at mailinglists.sqlite.org should be allowed through.

Mike

[sqlite] PRAGMA Synchronous safety

[Simon Slavin]

On 2 Mar 2015, at 4:45pm, Doug Nebeker  wrote:

> 1. Is setting synchronous to FULL necessary to prevent these corruption 
> problems?

You should not be getting this corruption problem, so I don't know whether FULL 
would cure it.

> 2. NORMAL is much slower than NONE.  Is FULL much slower than NORMAL?

Sorry but it's too hard to predict 'much' since the amount of time taken varies 
with different versions of the OS, width of the data bus, disk format, and 
other such things.  All you can do is try it on your own specific setup.

In the meantime ...

Are you using any PRAGMAs apart from "PRAGMA synchronous" ?

Are you testing the result codes of /all/ your sqlite3_ calls to check that 
they're SQLITE_OK ?  Sometimes it's not the expected call which causes the 
corruption, or which first returns an error because the database is corrupt.

Once the database is reported as corrupt, are you replacing it with a known 
good one before continuing ?  Corruption is not automatically fixed and once a 
database is corrupt it will remain corrupt until something is done about it.

Simon.

[sqlite] List duplication

[Mike Owens]

Oh okay. I see. I'll look into it.

On Mon, Mar 2, 2015 at 9:23 PM, Darren Duncan 
wrote:

> So in that case, still have the SQLite mail server reject messages to the
> old list rather than forwarding them, and let the problematic MUAs deal
> with it. The key thing is that by not forwarding but rejecting, the mail
> server isn't sending out 2 copies of messages directly, and the rejecting
> is reminding people to pay attention until the issue as a consequence goes
> away.  Thus any explicit Reply-To headers can be left unmunged by the list
> server. -- Darren Duncan
>
>
> On 2015-03-02 7:10 PM, Mike Owens wrote:
>
>> The problem is that this is the very bone of contention in the reply-to
>> religious war. Is it not? I may be wrong, but I thought this is the very
>> setting that people get so defensive about changing. As we have it now,
>> people have a suitable default pointing back to the (correct) list but
>> also
>> the freedom to change the reply-to header should they want to. If we strip
>> the reply-to header in order to correct for the problematic MUA's, then
>> the
>> latter freedom is lost. And if I remember correctly, some people get very
>> angry about this.
>>
>>
>> On Mon, Mar 2, 2015 at 8:18 PM, Darren Duncan 
>> wrote:
>>
>>  On 2015-03-02 6:14 PM, Mike Owens wrote:
>>>
>>>  On Mon, Mar 2, 2015 at 5:27 PM, R.Smith  wrote:

  Ah, thank you, all makes sense now. If you change the first option to
> YES
> then nobody else's quirky reply-to headers will get into the list, and
> the
> second option remains as is (it should be setting the standard
> @mailinglists reply-to field) - this should solve the duplication
> issue,
> but if it is disagreeable to anyone, more consideration is needed.
>
>
 I almost don't want to even speak of this for fear that this issue will
 raise it's ugly head again. Per the Mailmain documentation (
 http://www.gnu.org/software/mailman/mailman-admin/node11.html):

 Beware! Reply-To: munging is considered a religious issue and the
 policies

  you set here can ignite some of the most heated off-topic flame wars on
> your mailing lists. We'll try to stay as agnostic as possible, but our
> biases may still peak through.
>
>
>  That's as much as I'll say about that.


>>> Well it doesn't have to be complete munging, rather just enough munging
>>> to
>>> remove references to the old mailing list name. -- Darren Duncan
>>>
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Mike

[sqlite] Characters corrupt after importing a CSV file

[James K. Lowden]

On Mon, 2 Mar 2015 09:07:55 +
Hick Gunter  wrote:

> We do that here. Works well most of the time, but fails miserably if
> the first 10 columns are not representative of the data. 

By "that", I take you mean sampling 10 rows to determine the
appropriate widths.  

> Also requires each and every query to be run twice. Not good for
> queries that require sorting of the result set 

No need to run the query twice.  Just buffer the first 10 lines,
compute widths, print them, and continue.  

My .autowidth suggestion is just a convenience.  More important to me
is that 1,000,000,000 doesn't appear as 10,000,000 just because the
width is set to 8.  

--jkl

> 
> -Urspr?ngliche Nachricht-
> Von: James K. Lowden [mailto:jklowden at schemamania.org]
> Gesendet: Montag, 02. M?rz 2015 05:28
> An: sqlite-users at mailinglists.sqlite.org
> Betreff: Re: [sqlite] Characters corrupt after importing a CSV file
> 
> On Thu, 26 Feb 2015 10:58:30 +
> Adam Podstawczy?ski  wrote:
> 
> > While this solves the issue for me, I still believe this behavior is
> > confusing ? truncated characters should be handled more gracefully.
> 
> To your point, I think the column and column-width features of the
> shell could benefit from some re-thinking.
> 
> My ~/.sqliterc includes these lines:
> 
> .mode column
> .header ON
> 
> and truncated strings and numbers are a source of confusion and error
> even though I know how they work.
> 
> I wish that by default column formatting would be "soft" in the sense
> that printf(1) and printf(3) define: digits to the left of the
> decimal are never truncated, and strings are not truncated unless
> explicitly requested:
> 
> $ printf '%5.4f\n' 123456789.123456789
> 123456789.1235
> $ printf '%5.4s\n' 123456789.123456789
>  1234
> 
> This would make the output "snakey" on the screen sometimes, but
> would never conceal important information.
> 
> I would change the width specification to accept the width and
> precision notation used by printf.  The statement
> 
> .width 8
> 
> would mean "set the minimum column width to 8", whereas
> 
> .width  8.8
> 
> would mean "set the minumum width to 8, minimum precision to 8, and
> truncate strings to 8".  I wouldn't worry about columns that mix
> strings and numbers; funny-looking strings in that case should be
> expected.  And, besides, don't do that.
> 
> It would also be nice to have a mode ".autowidth" that set column
> widths based on, say, the first 10 lines (and, preferably, their
> labels) in a manner similar to column(1).  Provided it never
> truncated anything, I would use that more often than an
> improved .width.
> 
> Thoughts?
> 
> --jkl
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: hick at scigames.at
> 
> This communication (including any attachments) is intended for the
> use of the intended recipient(s) only and may contain information
> that is confidential, privileged or legally protected. Any
> unauthorized use or dissemination of this communication is strictly
> prohibited. If you have received this communication in error, please
> immediately notify the sender by return e-mail message and delete all
> copies of the original communication. Thank you for your cooperation.
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] full table scan ignores PK sort order?

[James K. Lowden]

On Mon, 2 Mar 2015 09:48:28 +
Jan Asselman  wrote:

> "CREATE TABLE IF NOT EXISTS test
> (
> a  INTEGER,
> b  INTEGER,
> c INTEGER,
> d  BLOB,
> PRIMARY KEY (a, b DESC)
> );"
...
> "SELECT * FROM test WHERE b < ? AND c > ?;"
...
> A full table scan is executed because column a is not part of the
> query.

A full table scan is executed because no index supports the search
criteria.  Specifically, the first member of the primary key, "a", is
not mentioned in the WHERE clause. (Primary keys are often implemented
as indexes, as they are in SQLite. In general, though, a primary key is
just a logical constraint.)  The table is the only source of
information sufficient to answer the query. 

It's actually not sufficient for column "a" to be "mentioned"; it has
to be searchable.  For example, 

WHERE a between b and c

also requires a table scan because the information in the index is
still insufficient.  

--jkl

[sqlite] List duplication

[Mike Owens]

The problem is that this is the very bone of contention in the reply-to
religious war. Is it not? I may be wrong, but I thought this is the very
setting that people get so defensive about changing. As we have it now,
people have a suitable default pointing back to the (correct) list but also
the freedom to change the reply-to header should they want to. If we strip
the reply-to header in order to correct for the problematic MUA's, then the
latter freedom is lost. And if I remember correctly, some people get very
angry about this.


On Mon, Mar 2, 2015 at 8:18 PM, Darren Duncan 
wrote:

> On 2015-03-02 6:14 PM, Mike Owens wrote:
>
>> On Mon, Mar 2, 2015 at 5:27 PM, R.Smith  wrote:
>>
>>> Ah, thank you, all makes sense now. If you change the first option to YES
>>> then nobody else's quirky reply-to headers will get into the list, and
>>> the
>>> second option remains as is (it should be setting the standard
>>> @mailinglists reply-to field) - this should solve the duplication issue,
>>> but if it is disagreeable to anyone, more consideration is needed.
>>>
>>
>> I almost don't want to even speak of this for fear that this issue will
>> raise it's ugly head again. Per the Mailmain documentation (
>> http://www.gnu.org/software/mailman/mailman-admin/node11.html):
>>
>> Beware! Reply-To: munging is considered a religious issue and the policies
>>
>>> you set here can ignite some of the most heated off-topic flame wars on
>>> your mailing lists. We'll try to stay as agnostic as possible, but our
>>> biases may still peak through.
>>>
>>>
>> That's as much as I'll say about that.
>>
>
> Well it doesn't have to be complete munging, rather just enough munging to
> remove references to the old mailing list name. -- Darren Duncan
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Mike

[sqlite] List duplication

[Darren Duncan]

So far so good.  I only got one copy of your test message.  I also sent my own 
message to just sqlite-users at sqlite.org and it was bounced as expected. -- 
Darren Duncan

On 2015-03-02 8:14 PM, Mike Owens wrote:
> Okay, I blocked the sqlite-users at sqlite.org address in the to address
> so if it is sent alone, it will be blocked.
>
> On Mon, Mar 2, 2015 at 9:46 PM, Mike Owens  wrote:
>> Oh okay. I see. I'll look into it.
>>
>> On Mon, Mar 2, 2015 at 9:23 PM, Darren Duncan 
>> wrote:
>>>
>>> So in that case, still have the SQLite mail server reject messages to the
>>> old list rather than forwarding them, and let the problematic MUAs deal with
>>> it. The key thing is that by not forwarding but rejecting, the mail server
>>> isn't sending out 2 copies of messages directly, and the rejecting is
>>> reminding people to pay attention until the issue as a consequence goes
>>> away.  Thus any explicit Reply-To headers can be left unmunged by the list
>>> server. -- Darren Duncan
>>>
>>>
>>> On 2015-03-02 7:10 PM, Mike Owens wrote:

 The problem is that this is the very bone of contention in the reply-to
 religious war. Is it not? I may be wrong, but I thought this is the very
 setting that people get so defensive about changing. As we have it now,
 people have a suitable default pointing back to the (correct) list but
 also
 the freedom to change the reply-to header should they want to. If we
 strip
 the reply-to header in order to correct for the problematic MUA's, then
 the
 latter freedom is lost. And if I remember correctly, some people get very
 angry about this.


 On Mon, Mar 2, 2015 at 8:18 PM, Darren Duncan 
 wrote:

> On 2015-03-02 6:14 PM, Mike Owens wrote:
>
>> On Mon, Mar 2, 2015 at 5:27 PM, R.Smith  wrote:
>>
>>> Ah, thank you, all makes sense now. If you change the first option to
>>> YES
>>> then nobody else's quirky reply-to headers will get into the list, and
>>> the
>>> second option remains as is (it should be setting the standard
>>> @mailinglists reply-to field) - this should solve the duplication
>>> issue,
>>> but if it is disagreeable to anyone, more consideration is needed.
>>>
>>
>> I almost don't want to even speak of this for fear that this issue will
>> raise it's ugly head again. Per the Mailmain documentation (
>> http://www.gnu.org/software/mailman/mailman-admin/node11.html):
>>
>> Beware! Reply-To: munging is considered a religious issue and the
>> policies
>>
>>> you set here can ignite some of the most heated off-topic flame wars
>>> on
>>> your mailing lists. We'll try to stay as agnostic as possible, but our
>>> biases may still peak through.
>>>
>>>
>> That's as much as I'll say about that.
>>
>
> Well it doesn't have to be complete munging, rather just enough munging
> to
> remove references to the old mailing list name. -- Darren Duncan

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[Donald Shepherd]

On Tue, 3 Mar 2015 at 03:01 Simon Slavin  wrote:

>
> On 2 Mar 2015, at 12:23am, Jay Kreibich  wrote:
>
> > Every database I?ve every used starts SQL parameter indexes from 1.  I?m
> not sure it is part of the SQL standard, but it is more or less the defacto
> standard of SQL APIs, and might be considered part of the SQL language.
>
> I hope the SQLite library does something appropriate if you try to bind to
> parameter 0.  It would seem to be an excellent avenue for a security bug if
> nothing tests for it.
>

SQLITE_RANGE  is returned if the
parameter index is out of range.

https://www.sqlite.org/c3ref/bind_blob.html

I can't confirm that 100% off the top of my head but I'm uncoordinated
enough to repeatedly confuse the bind and column value API calls and use
0-based indices for both and haven't noticed any really untoward behaviour
(beyond my code not working and requiring fixing).

Regards,
Donald Shepherd.

[sqlite] [ANN] SQLiteAdmin V1.3.6

[sonypsx]

Hello Peter,

may i inform you that the link provided in your announcement is wrong - you
end up with a 404 error.
Please crosscheck:
Working link:
http://www.lcsql.com/sqliteadmin.html

Your link:
www.lcsql.com/sqliteadmin.htm

I guess it?s the lower case typo ...

Cheers
sonypsx


-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Peter
Haworth
Gesendet: Montag, 02. M?rz 2015 20:14
An: sqlite-users at mailinglists.sqlite.org
Betreff: [sqlite] [ANN] SQLiteAdmin V1.3.6

I'm happy to announce the release of v1.3.6 of SQLiteAdmin, an SQLite
Database administration tool. This is a free release for current users.

SQLiteAdmin provides a GUI-based approach to creating your database schema
including many capabilities not possible within the SQLite language, plus
browse and edit data features.  A free trial version is available at
www.lcsql.com/sqliteadmin.htm .

The complete release notes for v1.3.6 are available at
http://tinyurl.com/o746roq.  Some notable enhancements are:

   - Support for inserting and viewing BLOB columns
   - The ability to create database diagrams in various formats showing the
   foreign key relationships between tables and a list of columns and
   constraints in each table.
   - Separate preference settings for each database you open.
   - Open a database by drag/dropping it onto the SQLiteAdmin application
   window.

Peter Haworth
lcSQL Software
www.lcsql.com
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] System.Data.SQLite and spellfix under VB.net

[sonypsx]

Hello,



May I post this question again - since I did not got any reply.



could some please post a sample how to use the spellfix module with
System.Data.SQLite under VB.net?

If i try to create the required table as documented here:



  http://www.sqlite.org/spellfix1.html



I end up with an error: 

"SQL logic error or missing database no such module: spellfix1"



I'm already working fine with the FullText Extensions:

  http://www.sqlite.org/fts3.html

So I'm curious how to do spellfix e.g. Levenshtein in SQLite.



Thanks in advance

sonypsx

[sqlite] List duplication

[Mike Owens]

On Mon, Mar 2, 2015 at 5:27 PM, R.Smith  wrote:

>
> Ah, thank you, all makes sense now. If you change the first option to YES
> then nobody else's quirky reply-to headers will get into the list, and the
> second option remains as is (it should be setting the standard
> @mailinglists reply-to field) - this should solve the duplication issue,
> but if it is disagreeable to anyone, more consideration is needed.
>

I almost don't want to even speak of this for fear that this issue will
raise it's ugly head again. Per the Mailmain documentation (
http://www.gnu.org/software/mailman/mailman-admin/node11.html):

Beware! Reply-To: munging is considered a religious issue and the policies
> you set here can ignite some of the most heated off-topic flame wars on
> your mailing lists. We'll try to stay as agnostic as possible, but our
> biases may still peak through.
>

That's as much as I'll say about that.


> Thanks again Mike for the list maintenance and the quick replies!
>

My pleasure. The SQLite community is a delight to work with. It's always
seemed to be composed of intelligent, well-behaved people.


>
>
>
>
>>
>> On Mon, Mar 2, 2015 at 5:04 PM, R.Smith  wrote:
>>
>>
>>> On 2015-03-03 12:42 AM, Darren Duncan wrote:
>>>
>>>  I think that what needs to be done is for each foo at sqlite.org to return
 an error/undeliverable message if someone sends a message to it, citing
 that all messages must be explicitly sent to the corresponding
 foo at mailinglists.sqlite.org.  That should handily solve the problem. --
 Darren Duncan

  I see where you are coming from, but if the Reply-To field contains 2
>>> email addresses and then the server penalizes you for using one of them,
>>> that might go down in history as the most-evil mailing-list quirk of all
>>> time.
>>>
>>> As to Mike's post - the dual mailing-list's reason for being is very
>>> clear
>>> and welcomed, no qualms there, just the Reply-to duplication that is
>>> quirky. I read all forum emails, I do not recall any multi-person
>>> decision
>>> to add this dual Reply-To thing, however memory is not my strength so I'm
>>> happy with the explanation - but I am wondering - is this done and
>>> dusted?
>>> Is there any chance we might re-open the discussion now that real-World
>>> scenarios have set in?
>>>
>>> It's an extremely minor irritation and will cause a few extra
>>> mail-traffic
>>> items at its worst - the only real casualty being my OCD, but I can't
>>> help
>>> thinking there is not a single good reason to keep the situation (unless
>>> someone can show the opposite).
>>>
>>>
>>>
>>>
>>>  On 2015-03-02 10:37 AM, Mike Owens wrote:

  For what it is worth, the move to mailinglists.sqlite.org is a result
> of
> the Mailman web interface having to be hosted under the following two
> constraints:
>
> 1. It must be on port 80
> 2. It cannot be on sqlite.org port 80
>
> I explained this reasoning in a previous email. The short version is
> because we are using two web servers on the VM that hosts both the
> sqlite.org website and fossil repos (althttpd) and the Mailman web
> interface (Apache). We previously did this on a single IP where mailman
> was
> on port 8080. However, we had a significant number of complaints from
> people who could not reach the Mailman web interface via
> sqlite.org:8080
> due to firewall restrictions in their respective locations. So we did
> what
> we could to move it to port 80.
>
> So to satisfy these two constraints, mailinglists.sqlite.org was born.
> Unless somebody else knows better, Mailman does not allow one to use
> two
> domains for a given list. Either something will screw up with the mail
> routing or in the web interface if you try to use more than one. You
> have
> to pick one domain and stick with it. Thus I could not continue to
> support
> both the previous sqlite.org (:8080) domain and the new
> mailinglists.sqlite.org (:80) for the users list. So I made the move
> from
> the one to the other.
>
> Regarding the reply-to policy. I honestly don't remember the reasoning
> behind it. I know there was a big long discussion about it in the past
> (search the list) and after the dust settled we chose the current
> policy
> and that is the way it is configured today.  I do believe the policy
> was
> a
> result of the consensus of the mailing list users. I can say that we do
> everything we can to make most of the people happy most of the time.
> That
> is the very reason we made this change to begin with -- to make it
> possible
> for everyone to use the list. It would have been easier to just keep
> things
> the same and let the people who can't reach port 8080 deal with it, but
> we
> did what we had to to make it accessible for them as well. There are a
> lot
> of variables in 

[sqlite] List duplication

[Mike Owens]

On Mon, Mar 2, 2015 at 5:24 PM, Darren Duncan 
wrote:


> As near as I can tell, the Reply-To header from this list only contains
> sqlite-users at mailinglists.sqlite.org and does not also contain
> sqlite-users at sqlite.org so therefore I don't see the problem you're
> stating. But if it sometimes does so, then the list manager needs to ensure
> that sqlite-users at sqlite.org is never in the Reply-To header of messages
> from the list. -- Darren Duncan
>

It is. As mentioned above, the explicit reply-to header is empty. I think
some people's MUA's are doing this. I just answered an off-list email from
somebody and the reply to was set to the sqlite-users at sqlite.org email,
which makes me think it is the MUA and not Mailman.

Mike

[sqlite] List duplication

[Darren Duncan]

So in that case, still have the SQLite mail server reject messages to the old 
list rather than forwarding them, and let the problematic MUAs deal with it. 
The key thing is that by not forwarding but rejecting, the mail server isn't 
sending out 2 copies of messages directly, and the rejecting is reminding 
people 
to pay attention until the issue as a consequence goes away.  Thus any explicit 
Reply-To headers can be left unmunged by the list server. -- Darren Duncan

On 2015-03-02 7:10 PM, Mike Owens wrote:
> The problem is that this is the very bone of contention in the reply-to
> religious war. Is it not? I may be wrong, but I thought this is the very
> setting that people get so defensive about changing. As we have it now,
> people have a suitable default pointing back to the (correct) list but also
> the freedom to change the reply-to header should they want to. If we strip
> the reply-to header in order to correct for the problematic MUA's, then the
> latter freedom is lost. And if I remember correctly, some people get very
> angry about this.
>
>
> On Mon, Mar 2, 2015 at 8:18 PM, Darren Duncan 
> wrote:
>
>> On 2015-03-02 6:14 PM, Mike Owens wrote:
>>
>>> On Mon, Mar 2, 2015 at 5:27 PM, R.Smith  wrote:
>>>
 Ah, thank you, all makes sense now. If you change the first option to YES
 then nobody else's quirky reply-to headers will get into the list, and
 the
 second option remains as is (it should be setting the standard
 @mailinglists reply-to field) - this should solve the duplication issue,
 but if it is disagreeable to anyone, more consideration is needed.

>>>
>>> I almost don't want to even speak of this for fear that this issue will
>>> raise it's ugly head again. Per the Mailmain documentation (
>>> http://www.gnu.org/software/mailman/mailman-admin/node11.html):
>>>
>>> Beware! Reply-To: munging is considered a religious issue and the policies
>>>
 you set here can ignite some of the most heated off-topic flame wars on
 your mailing lists. We'll try to stay as agnostic as possible, but our
 biases may still peak through.


>>> That's as much as I'll say about that.
>>>
>>
>> Well it doesn't have to be complete munging, rather just enough munging to
>> remove references to the old mailing list name. -- Darren Duncan

[sqlite] [ANN] SQLiteAdmin V1.3.6

[Peter Haworth]

I'm happy to announce the release of v1.3.6 of SQLiteAdmin, an SQLite
Database administration tool. This is a free release for current users.

SQLiteAdmin provides a GUI-based approach to creating your database schema
including many capabilities not possible within the SQLite language, plus
browse and edit data features.  A free trial version is available at
www.lcsql.com/sqliteadmin.htm .

The complete release notes for v1.3.6 are available at
http://tinyurl.com/o746roq.  Some notable enhancements are:

   - Support for inserting and viewing BLOB columns
   - The ability to create database diagrams in various formats showing the
   foreign key relationships between tables and a list of columns and
   constraints in each table.
   - Separate preference settings for each database you open.
   - Open a database by drag/dropping it onto the SQLiteAdmin application
   window.

Peter Haworth
lcSQL Software
www.lcsql.com

[sqlite] List duplication

[Darren Duncan]

On 2015-03-02 6:14 PM, Mike Owens wrote:
> On Mon, Mar 2, 2015 at 5:27 PM, R.Smith  wrote:
>> Ah, thank you, all makes sense now. If you change the first option to YES
>> then nobody else's quirky reply-to headers will get into the list, and the
>> second option remains as is (it should be setting the standard
>> @mailinglists reply-to field) - this should solve the duplication issue,
>> but if it is disagreeable to anyone, more consideration is needed.
>
> I almost don't want to even speak of this for fear that this issue will
> raise it's ugly head again. Per the Mailmain documentation (
> http://www.gnu.org/software/mailman/mailman-admin/node11.html):
>
> Beware! Reply-To: munging is considered a religious issue and the policies
>> you set here can ignite some of the most heated off-topic flame wars on
>> your mailing lists. We'll try to stay as agnostic as possible, but our
>> biases may still peak through.
>>
>
> That's as much as I'll say about that.

Well it doesn't have to be complete munging, rather just enough munging to 
remove references to the old mailing list name. -- Darren Duncan

[sqlite] List duplication

[Darren Duncan]

On 2015-03-02 6:08 PM, Mike Owens wrote:
> On Mon, Mar 2, 2015 at 5:24 PM, Darren Duncan 
> wrote:
>> As near as I can tell, the Reply-To header from this list only contains
>> sqlite-users at mailinglists.sqlite.org and does not also contain
>> sqlite-users at sqlite.org so therefore I don't see the problem you're
>> stating. But if it sometimes does so, then the list manager needs to ensure
>> that sqlite-users at sqlite.org is never in the Reply-To header of messages
>> from the list. -- Darren Duncan
>
> It is. As mentioned above, the explicit reply-to header is empty. I think
> some people's MUA's are doing this. I just answered an off-list email from
> somebody and the reply to was set to the sqlite-users at sqlite.org email,
> which makes me think it is the MUA and not Mailman.

A benefit of my proposal is, if implemented, the instances of other people's 
explicit reply-to headers to sqlite-users at sqlite.org will disappear very 
quickly, as the messages they are replying to will be ones propagated through 
the list after the change rather than before. -- Darren Duncan

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[R.Smith]

On 2015-03-02 04:51 PM, Paolo Bolzoni wrote:
> I kinda get your point, but still we are speaking of the C interface
> all the time. Sure in other contexts, like the TCL/SQL, the indexes
> start from 1. However the bind and the column function are both in the
> C interface.

It's when the C interface (or API) refers to items indexed by the SQL 
that's the odd child - it will cause much greater confusion if you bind 
in SQL ?1,?2,... and then the API binds or refers to those as 
col[0],col[1],... in stead of col[1],col[2],...  This is what I meant 
with the API following the TCL convention in certain cases. In the ones 
where there are no TCL/SQL obfuscation it indexes like any C API to 
0-based ranges.

Yeah - it's still not nice, but I'm not sure any other way would be better.

[sqlite] List duplication

[Mike Owens]

For clarity, here is the currently policy for Reply-to as it is set in
Mailman:


   - Should any existing Reply-To: header found in the original message be
   stripped? If so, this will be done regardless of whether an explict
   Reply-To: header is added by Mailman or not. : *NO*
   - Where are replies to list messages directed? *This list*
   - Explicit Reply-To: header : *None*


On Mon, Mar 2, 2015 at 5:04 PM, R.Smith  wrote:

>
>
> On 2015-03-03 12:42 AM, Darren Duncan wrote:
>
>> I think that what needs to be done is for each foo at sqlite.org to return
>> an error/undeliverable message if someone sends a message to it, citing
>> that all messages must be explicitly sent to the corresponding
>> foo at mailinglists.sqlite.org.  That should handily solve the problem. --
>> Darren Duncan
>>
>
> I see where you are coming from, but if the Reply-To field contains 2
> email addresses and then the server penalizes you for using one of them,
> that might go down in history as the most-evil mailing-list quirk of all
> time.
>
> As to Mike's post - the dual mailing-list's reason for being is very clear
> and welcomed, no qualms there, just the Reply-to duplication that is
> quirky. I read all forum emails, I do not recall any multi-person decision
> to add this dual Reply-To thing, however memory is not my strength so I'm
> happy with the explanation - but I am wondering - is this done and dusted?
> Is there any chance we might re-open the discussion now that real-World
> scenarios have set in?
>
> It's an extremely minor irritation and will cause a few extra mail-traffic
> items at its worst - the only real casualty being my OCD, but I can't help
> thinking there is not a single good reason to keep the situation (unless
> someone can show the opposite).
>
>
>
>
>> On 2015-03-02 10:37 AM, Mike Owens wrote:
>>
>>> For what it is worth, the move to mailinglists.sqlite.org is a result of
>>> the Mailman web interface having to be hosted under the following two
>>> constraints:
>>>
>>> 1. It must be on port 80
>>> 2. It cannot be on sqlite.org port 80
>>>
>>> I explained this reasoning in a previous email. The short version is
>>> because we are using two web servers on the VM that hosts both the
>>> sqlite.org website and fossil repos (althttpd) and the Mailman web
>>> interface (Apache). We previously did this on a single IP where mailman
>>> was
>>> on port 8080. However, we had a significant number of complaints from
>>> people who could not reach the Mailman web interface via sqlite.org:8080
>>> due to firewall restrictions in their respective locations. So we did
>>> what
>>> we could to move it to port 80.
>>>
>>> So to satisfy these two constraints, mailinglists.sqlite.org was born.
>>> Unless somebody else knows better, Mailman does not allow one to use two
>>> domains for a given list. Either something will screw up with the mail
>>> routing or in the web interface if you try to use more than one. You have
>>> to pick one domain and stick with it. Thus I could not continue to
>>> support
>>> both the previous sqlite.org (:8080) domain and the new
>>> mailinglists.sqlite.org (:80) for the users list. So I made the move
>>> from
>>> the one to the other.
>>>
>>> Regarding the reply-to policy. I honestly don't remember the reasoning
>>> behind it. I know there was a big long discussion about it in the past
>>> (search the list) and after the dust settled we chose the current policy
>>> and that is the way it is configured today.  I do believe the policy was
>>> a
>>> result of the consensus of the mailing list users. I can say that we do
>>> everything we can to make most of the people happy most of the time. That
>>> is the very reason we made this change to begin with -- to make it
>>> possible
>>> for everyone to use the list. It would have been easier to just keep
>>> things
>>> the same and let the people who can't reach port 8080 deal with it, but
>>> we
>>> did what we had to to make it accessible for them as well. There are a
>>> lot
>>> of variables in the system and we juggle them as best we can.
>>>
>>> Any feedback or suggestions are always welcome.
>>>
>>>
>>> On Mon, Mar 2, 2015 at 5:18 AM, David Woodhouse 
>>> wrote:
>>>
>>>  On Mon, 2015-03-02 at 12:45 +0200, R.Smith wrote:

> Ok, I've found the source of the list duplications.
>
> Some emails (Such as the one by J.K. Lowden 2-March-2015 re: Characters
> corrupt after importing...) contains a "Reply-To" field in the header
> with both list addresses which must have sneaked in there due to some
> automatic list feature.  (By "Both" I mean the old:
> sqlite-users at sqlite.org and the new:
>
 sqlite-users at mailinglists.sqlite.org)

 You don't need that, do you? Just hitting Reply All to a message which
 is:
   To: sqlite-users at sqlite.org
   Reply-To: sqlite-users at mailinglists.sqlite.org

 would generate a message which ends up going to both, wouldn't it?

 (I 

[sqlite] full table scan ignores PK sort order?

[Richard Hipp]

On 3/2/15, Igor Tandetnik  wrote:
> On 3/2/2015 4:48 AM, Jan Asselman wrote:
>> But when I step over the rows they are not returned in primary key sort
>> order. Why is this?
>
> Because you didn't add an ORDER BY clause. If you need a particular sort
> order, specify it with ORDER BY.
>

Igor is 100% correct - I should have been emphatic about this in my
reply:  If you need output in a particular order ALWAYS ALWAYS ALWAYS
use an ORDER BY clause.  No exceptions.  If SQLite can provide the
output in the requested order using an index, it will.  Never try to
out-smart the query planner.  Doing so only leads to needless pain and
suffering.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] full table scan ignores PK sort order?

[Igor Tandetnik]

On 3/2/2015 4:48 AM, Jan Asselman wrote:
> But when I step over the rows they are not returned in primary key sort 
> order. Why is this?

Because you didn't add an ORDER BY clause. If you need a particular sort 
order, specify it with ORDER BY.

> If I look at the images at the query planning document 
> (https://www.sqlite.org/queryplanner.html) I get the idea that the primary 
> key B-tree should be used to traverse the table when a full table scan is 
> executed.

Not the primary key, but the ROWID column. It may optionally be aliased 
by a column declared as INTEGER PRIMARY KEY (must be spelled exactly 
this way); your table doesn't have such an alias.

This changes for tables created with WITHOUT ROWID clause; but this, 
too, doesn't apply in your case.

> And since the 'DESC' keyword is used on column b in the primary key, I would 
> expect that, as the rowId increases, the values retuned for column b would 
> decrease. But this is not the case.

How can this be the case? You can update the value of b in an existing 
row - do you expect all the rows to be physically moved and renumbered 
when this happens?

> Does a full table scan then ignore the PK B-tree?

What you think of as "PK B-tree" doesn't exist.

> If I explicitly order using an 'ORDER BY' statement then - looking at the 
> query plan - sqlite seems to perform a full table scan and store the result 
> in a temporary table which is then sorted. I'd like to avoid the memory 
> consumption produced by this query plan...

If you "ORDER BY a, b desc" SQLite should be using index scan without an 
explicit sort step. Does this not happen?
-- 
Igor Tandetnik

[sqlite] PRAGMA Synchronous safety

[Richard Hipp]

On 3/2/15, Doug Nebeker  wrote:
> In the documents, using PRAGMA synchronous 1 (NORMAL) is supposed to be safe
> unless there is an OS crash or power failure.
>
> I've had a few customers (about one a month, out of thousands) that is
> getting the "database disk image is malformed" ExtErr=11 error with the
> NORMAL pragma, and they claim there was no crash, power failure or anything
> else unusual.  The database _does_ get hammered under normal operation.
>
> There are two separate sqlite3* handles to the database.  Each one is
> protected by a critical section, so there is never more than one thread
> accessing a handle at a time.  The database is on a local NTFS drive.
>
> Two questions:
>
> 1. Is setting synchronous to FULL necessary to prevent these corruption
> problems?

Not that we are aware of.

>
> 2. NORMAL is much slower than NONE.  Is FULL much slower than NORMAL?
>

Depends on whether you are using WAL mode or not.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] PRAGMA Synchronous safety

[Doug Nebeker]

In the documents, using PRAGMA synchronous 1 (NORMAL) is supposed to be safe 
unless there is an OS crash or power failure.

I've had a few customers (about one a month, out of thousands) that is getting 
the "database disk image is malformed" ExtErr=11 error with the NORMAL pragma, 
and they claim there was no crash, power failure or anything else unusual.  The 
database _does_ get hammered under normal operation.

There are two separate sqlite3* handles to the database.  Each one is protected 
by a critical section, so there is never more than one thread accessing a 
handle at a time.  The database is on a local NTFS drive.

Two questions:

1. Is setting synchronous to FULL necessary to prevent these corruption 
problems?

2. NORMAL is much slower than NONE.  Is FULL much slower than NORMAL?

Thanks
Doug

[sqlite] full table scan ignores PK sort order?

[Richard Hipp]

On 3/2/15, Jan Asselman  wrote:
> Hi,
>
> I created the following table in sqlite 3.8.6
>
> "CREATE TABLE IF NOT EXISTS test
> (
> a  INTEGER,
> b  INTEGER,
> c INTEGER,
> d  BLOB,
> PRIMARY KEY (a, b DESC)
> );"
>
> When I execute the following query
>
> "SELECT * FROM test WHERE b < ? AND c > ?;"
>
> A full table scan is executed because column a is not part of the query.
>
> But when I step over the rows they are not returned in primary key sort
> order. Why is this?

The PRIMARY KEY is really just a UNIQUE constraint unless (1) it
refers to a single column of type INTEGER or (2) the table is a
WITHOUT ROWID table.

Simplest solution for you seems to be to add the keywords "WITHOUT
ROWID" after the ")" and before the ";".


>
> If I look at the images at the query planning document
> (https://www.sqlite.org/queryplanner.html) I get the idea that the primary
> key B-tree should be used to traverse the table when a full table scan is
> executed. And since the 'DESC' keyword is used on column b in the primary
> key, I would expect that, as the rowId increases, the values retuned for
> column b would decrease. But this is not the case.
>
> Does a full table scan then ignore the PK B-tree? Does it perhaps scan all
> table pages in the order in which they appear in the file system? I guess
> this would make sense to increase traversal speed, since it needs to check
> all pages anyway?
>
> If I explicitly order using an 'ORDER BY' statement then - looking at the
> query plan - sqlite seems to perform a full table scan and store the result
> in a temporary table which is then sorted. I'd like to avoid the memory
> consumption produced by this query plan...
>
> Any help or information would be appreciated!
> Thanks in advance!
>
> Jan Asselman
>
>
>
>
>
>
>
> iba AG: Supervisory Board: Horst Anhaus, chairman; Management Board: Dr.
> Ulrich Lettau CEO,  Marta Anhaus, Harald Opel; Registered Office:
> Fuerth/Germany; Registration Office: Fuerth, HRB 9865, Vat.Reg.No DE
> 132760166, WEEE-Reg.No. DE11469996
>
> This message is intended only for the named recipient and may contain
> confidential or privileged information. Taking notice of this message by
> third parties is not permitted. If you have received it in error, please
> advise the sender by return e-mail and delete this message and any
> attachments. Any unauthorized use or dissemination of this information is
> strictly prohibited.
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[Nesvarbu Ne]

Valodia valodia

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[Simon Slavin]

On 2 Mar 2015, at 12:23am, Jay Kreibich  wrote:

> Every database I?ve every used starts SQL parameter indexes from 1.  I?m not 
> sure it is part of the SQL standard, but it is more or less the defacto 
> standard of SQL APIs, and might be considered part of the SQL language.

I hope the SQLite library does something appropriate if you try to bind to 
parameter 0.  It would seem to be an excellent avenue for a security bug if 
nothing tests for it.

Simon.

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[Paolo Bolzoni]

I kinda get your point, but still we are speaking of the C interface
all the time. Sure in other contexts, like the TCL/SQL, the indexes
start from 1. However the bind and the column function are both in the
C interface.

Oh, well... I just have to wrap my mind around it. Definitely not a
real problem, I was merely curious.

On Mon, Mar 2, 2015 at 1:23 AM, Jay Kreibich  wrote:
>
> On Mar 1, 2015, at 5:33 PM, Richard Hipp  wrote:
>
>> On 3/1/15, Paolo Bolzoni  wrote:
>>> Dear everyone,
>>>
>>> I find strange and confusing that bind indexes start from 1 (docs in
>>> [1]) and instead column indexes start from 0 (doc in [2]). Is there
>>> any technical reason or it is just an unlucky legacy?
>>
>> Seems like there was a reason for this, 11 years ago when it went in,
>> but I cannot now call that reason to mind right this moment.  So lets
>> just call it unlucky legacy.
>
> Every database I?ve every used starts SQL parameter indexes from 1.  I?m not 
> sure it is part of the SQL standard, but it is more or less the defacto 
> standard of SQL APIs, and might be considered part of the SQL language.
>
> I assume column indexes start at 0 because of C.  Column indexes are used 
> within the context of the C language API, so it makes some sense to use C 
> conventions.
>
>
> I know they?re very easy to confuse (I still do it myself), but I can kind of 
> see why we ended up there, even if it isn?t a very strong reason.
>
>  -j
>
>
> --
> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it, but 
> showing it to the wrong people has the tendency to make them feel 
> uncomfortable." -- Angela Johnson
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] List duplication

[Darren Duncan]

On 2015-03-02 3:04 PM, R.Smith wrote:
> On 2015-03-03 12:42 AM, Darren Duncan wrote:
>> I think that what needs to be done is for each foo at sqlite.org to return an
>> error/undeliverable message if someone sends a message to it, citing that all
>> messages must be explicitly sent to the corresponding
>> foo at mailinglists.sqlite.org.  That should handily solve the problem. -- 
>> Darren
>> Duncan
>
> I see where you are coming from, but if the Reply-To field contains 2 email
> addresses and then the server penalizes you for using one of them, that might 
> go
> down in history as the most-evil mailing-list quirk of all time.

As near as I can tell, the Reply-To header from this list only contains 
sqlite-users at mailinglists.sqlite.org and does not also contain 
sqlite-users at sqlite.org so therefore I don't see the problem you're stating. 
But if it sometimes does so, then the list manager needs to ensure that 
sqlite-users at sqlite.org is never in the Reply-To header of messages from the 
list. -- Darren Duncan

[sqlite] System.Data.SQLite.DLL version 1.0.95.0 Bug

[Joe Mistachkin]

This issue should be fixed on the "nextRelease" branch, here:

https://system.data.sqlite.org/index.html/info/154d609bbe

Please let us know if this clears the problem for you.

--
Joe Mistachkin

[sqlite] List duplication

[Darren Duncan]

I think that what needs to be done is for each foo at sqlite.org to return an 
error/undeliverable message if someone sends a message to it, citing that all 
messages must be explicitly sent to the corresponding 
foo at mailinglists.sqlite.org.  That should handily solve the problem. -- 
Darren 
Duncan

On 2015-03-02 10:37 AM, Mike Owens wrote:
> For what it is worth, the move to mailinglists.sqlite.org is a result of
> the Mailman web interface having to be hosted under the following two
> constraints:
>
> 1. It must be on port 80
> 2. It cannot be on sqlite.org port 80
>
> I explained this reasoning in a previous email. The short version is
> because we are using two web servers on the VM that hosts both the
> sqlite.org website and fossil repos (althttpd) and the Mailman web
> interface (Apache). We previously did this on a single IP where mailman was
> on port 8080. However, we had a significant number of complaints from
> people who could not reach the Mailman web interface via sqlite.org:8080
> due to firewall restrictions in their respective locations. So we did what
> we could to move it to port 80.
>
> So to satisfy these two constraints, mailinglists.sqlite.org was born.
> Unless somebody else knows better, Mailman does not allow one to use two
> domains for a given list. Either something will screw up with the mail
> routing or in the web interface if you try to use more than one. You have
> to pick one domain and stick with it. Thus I could not continue to support
> both the previous sqlite.org (:8080) domain and the new
> mailinglists.sqlite.org (:80) for the users list. So I made the move from
> the one to the other.
>
> Regarding the reply-to policy. I honestly don't remember the reasoning
> behind it. I know there was a big long discussion about it in the past
> (search the list) and after the dust settled we chose the current policy
> and that is the way it is configured today.  I do believe the policy was a
> result of the consensus of the mailing list users. I can say that we do
> everything we can to make most of the people happy most of the time. That
> is the very reason we made this change to begin with -- to make it possible
> for everyone to use the list. It would have been easier to just keep things
> the same and let the people who can't reach port 8080 deal with it, but we
> did what we had to to make it accessible for them as well. There are a lot
> of variables in the system and we juggle them as best we can.
>
> Any feedback or suggestions are always welcome.
>
>
> On Mon, Mar 2, 2015 at 5:18 AM, David Woodhouse  
> wrote:
>
>> On Mon, 2015-03-02 at 12:45 +0200, R.Smith wrote:
>>> Ok, I've found the source of the list duplications.
>>>
>>> Some emails (Such as the one by J.K. Lowden 2-March-2015 re: Characters
>>> corrupt after importing...) contains a "Reply-To" field in the header
>>> with both list addresses which must have sneaked in there due to some
>>> automatic list feature.  (By "Both" I mean the old:
>>> sqlite-users at sqlite.org and the new:
>> sqlite-users at mailinglists.sqlite.org)
>>
>> You don't need that, do you? Just hitting Reply All to a message which
>> is:
>>   To: sqlite-users at sqlite.org
>>   Reply-To: sqlite-users at mailinglists.sqlite.org
>>
>> would generate a message which ends up going to both, wouldn't it?
>>
>> (I can't easily test; I've configured my mailer to ignore abusive
>> Reply-To: headers from mailing lists where it can detect them, so my
>> Reply and Reply All buttons actually do what I *ask* them to.)
>>
>> But looking at the first message in the 'PhD Student' thread, it appears
>> just as in my example above. And John KcKown's response of 26 Feb 2015
>> 07:16:47 -0600 is indeed to both addresses, as if he'd done the correct
>> thing and simply hit 'Reply All'.
>>
>>> I usually use the "Reply to List" button (Thunderbird) which replies
>>> correctly,
>>
>> Note that that is considered extremely anti-social in many cases,
>> because it cuts some people out of discussions entirely. See
>> http://david.woodhou.se/reply-to-list.html for a full discussion.
>>
>> --
>> dwmw2

[sqlite] System.Data.SQLite.DLL version 1.0.95.0 Bug

[Joe Mistachkin]

Michal wrote:
>
> The table does have a Primary Key. 
>

Can you share the complete schema of the table involved?

--
Joe Mistachkin

[sqlite] System.Data.SQLite and spellfix under VB.net

[Joe Mistachkin]

sonypsx wrote:
>
> could some please post a sample how to use the spellfix module with
> System.Data.SQLite under VB.net?
> 

The first step would be to compile the spellfix extension as a loadable
module, as documented here:

https://www.sqlite.org/loadext.html

Next, you can load it using the SQLiteConnection.LoadExtension method.

Finally, you should be able to follow along with the normal spellfix
docs, here:

https://www.sqlite.org/spellfix1.html

--
Joe Mistachkin

[sqlite] List duplication

[R.Smith]

Ok, I've found the source of the list duplications.

Some emails (Such as the one by J.K. Lowden 2-March-2015 re: Characters 
corrupt after importing...) contains a "Reply-To" field in the header 
with both list addresses which must have sneaked in there due to some 
automatic list feature.  (By "Both" I mean the old: 
sqlite-users at sqlite.org and the new: sqlite-users at mailinglists.sqlite.org)

I usually use the "Reply to List" button (Thunderbird) which replies 
correctly, but if you only hit the "Reply to" button then BOTH addresses 
are inserted in the "To:" field of the message to be sent - which of 
course results in the duplication (Such as the duplicated message from 
H. Gunter in reply to the message above).

An astute eye might catch this and remove a reference, but mostly nobody 
cares to study the things that should work automatically - and they 
shouldn't need to.

May I ask the list admin to see if any mention of the old list email 
address in automatic message body generated text still lingers and can 
be gotten rid of?

Thank you kindly!
Ryan

[sqlite] List duplication

[Mike Owens]

For what it is worth, the move to mailinglists.sqlite.org is a result of
the Mailman web interface having to be hosted under the following two
constraints:

1. It must be on port 80
2. It cannot be on sqlite.org port 80

I explained this reasoning in a previous email. The short version is
because we are using two web servers on the VM that hosts both the
sqlite.org website and fossil repos (althttpd) and the Mailman web
interface (Apache). We previously did this on a single IP where mailman was
on port 8080. However, we had a significant number of complaints from
people who could not reach the Mailman web interface via sqlite.org:8080
due to firewall restrictions in their respective locations. So we did what
we could to move it to port 80.

So to satisfy these two constraints, mailinglists.sqlite.org was born.
Unless somebody else knows better, Mailman does not allow one to use two
domains for a given list. Either something will screw up with the mail
routing or in the web interface if you try to use more than one. You have
to pick one domain and stick with it. Thus I could not continue to support
both the previous sqlite.org (:8080) domain and the new
mailinglists.sqlite.org (:80) for the users list. So I made the move from
the one to the other.

Regarding the reply-to policy. I honestly don't remember the reasoning
behind it. I know there was a big long discussion about it in the past
(search the list) and after the dust settled we chose the current policy
and that is the way it is configured today.  I do believe the policy was a
result of the consensus of the mailing list users. I can say that we do
everything we can to make most of the people happy most of the time. That
is the very reason we made this change to begin with -- to make it possible
for everyone to use the list. It would have been easier to just keep things
the same and let the people who can't reach port 8080 deal with it, but we
did what we had to to make it accessible for them as well. There are a lot
of variables in the system and we juggle them as best we can.

Any feedback or suggestions are always welcome.


On Mon, Mar 2, 2015 at 5:18 AM, David Woodhouse  wrote:

> On Mon, 2015-03-02 at 12:45 +0200, R.Smith wrote:
> > Ok, I've found the source of the list duplications.
> >
> > Some emails (Such as the one by J.K. Lowden 2-March-2015 re: Characters
> > corrupt after importing...) contains a "Reply-To" field in the header
> > with both list addresses which must have sneaked in there due to some
> > automatic list feature.  (By "Both" I mean the old:
> > sqlite-users at sqlite.org and the new:
> sqlite-users at mailinglists.sqlite.org)
>
> You don't need that, do you? Just hitting Reply All to a message which
> is:
>  To: sqlite-users at sqlite.org
>  Reply-To: sqlite-users at mailinglists.sqlite.org
>
> would generate a message which ends up going to both, wouldn't it?
>
> (I can't easily test; I've configured my mailer to ignore abusive
> Reply-To: headers from mailing lists where it can detect them, so my
> Reply and Reply All buttons actually do what I *ask* them to.)
>
> But looking at the first message in the 'PhD Student' thread, it appears
> just as in my example above. And John KcKown's response of 26 Feb 2015
> 07:16:47 -0600 is indeed to both addresses, as if he'd done the correct
> thing and simply hit 'Reply All'.
>
> > I usually use the "Reply to List" button (Thunderbird) which replies
> > correctly,
>
> Note that that is considered extremely anti-social in many cases,
> because it cuts some people out of discussions entirely. See
> http://david.woodhou.se/reply-to-list.html for a full discussion.
>
> --
> dwmw2
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>


-- 
Mike

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[Jay Kreibich]

On Mar 2, 2015, at 8:51 AM, Paolo Bolzoni  
wrote:

> I kinda get your point, but still we are speaking of the C interface
> all the time.

Not exactly.  SQL parameters are defined in SQL, and they?re part of the SQL 
language.  The parameter placement (and from that, their indexing and binding) 
can be considered an integral part of the SQL expression.  After all, in a 
given SQL statement, which parameter is ?parameter #3? shouldn?t really change, 
regardless of if you are using C, Python, Pascal, or some other language 
(including raw SQL at a prompt[1]).  This is more explicit when you use 
numbered or named parameters, but the same logic applies: the definition of the 
parameter enumeration and placement is more squarely in the domain of the SQL 
language, not the host access language.

[1] consider, for example, if the CLI tool sqlite3 allowed one to store 
statements and then execute them with a parameter list, perhaps taken from a 
CSV file or something.


Getting data back out, i.e. the column functions, on the other hand, is more of 
a language and database driver specific thing.  SQL doesn?t tend to enumerate 
columns, it just provides them in the order requested.  How a DB API chooses to 
return those values into a language specific construct is really up to the 
database driver and API designer.  Consider a language that has a native array 
or vector type, for example? it might make sense for an API to just return a 
whole row, rather than picking out column values one at a time, eliminating the 
whole need to index columns outside of the native language access syntax.  The 
sqlite3_exec() API kind of works that way, for example.  From that standpoint, 
it makes some amount of sense to use the C semantics, and index off zero.

Actually, I?m guessing the sqlite3_exec() function is the whole reason for 
column indexes starting at zero.  SQLite2 didn?t have prepared statements and 
ONLY had the sqlite_exec() API.  My guess is to keep the APIs as similar as 
possible, output columns are also indexed from zero.

Although? there are times when SQL does reference output columns.  For example, 
you can use a numerical reference to an output column in a GROUP BY or ORDER BY 
clause, and in those cases SQL is consistent and considers the first column to 
be ?1?, just as it does with parameters.  From that standpoint, it might make 
sense to say columns should be referenced from 1, regardless of the host 
language.

All of this is a little fuzzy and gray.  The fact they?re different is very 
unfortunate, and I think most of us would agree that if anything is ?broken?, 
it is the column access functions, not the parameters.  But regardless, it is 
what it is.

 -j



> Sure in other contexts, like the TCL/SQL, the indexes
> start from 1. However the bind and the column function are both in the
> C interface.
> 
> Oh, well... I just have to wrap my mind around it. Definitely not a
> real problem, I was merely curious.
> 
> On Mon, Mar 2, 2015 at 1:23 AM, Jay Kreibich  wrote:
>> 
>> On Mar 1, 2015, at 5:33 PM, Richard Hipp  wrote:
>> 
>>> On 3/1/15, Paolo Bolzoni  wrote:
 Dear everyone,
 
 I find strange and confusing that bind indexes start from 1 (docs in
 [1]) and instead column indexes start from 0 (doc in [2]). Is there
 any technical reason or it is just an unlucky legacy?
>>> 
>>> Seems like there was a reason for this, 11 years ago when it went in,
>>> but I cannot now call that reason to mind right this moment.  So lets
>>> just call it unlucky legacy.
>> 
>> Every database I?ve every used starts SQL parameter indexes from 1.  I?m not 
>> sure it is part of the SQL standard, but it is more or less the defacto 
>> standard of SQL APIs, and might be considered part of the SQL language.
>> 
>> I assume column indexes start at 0 because of C.  Column indexes are used 
>> within the context of the C language API, so it makes some sense to use C 
>> conventions.
>> 
>> 
>> I know they?re very easy to confuse (I still do it myself), but I can kind 
>> of see why we ended up there, even if it isn?t a very strong reason.
>> 
>> -j
>> 
>> 
>> --
>> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>> 
>> "Intelligence is like underwear: it is important that you have it, but 
>> showing it to the wrong people has the tendency to make them feel 
>> uncomfortable." -- Angela Johnson
>> 
>> 
>> 
>> 
>> 
>> ___
>> 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

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela 

[sqlite] List duplication

[David Woodhouse]

On Mon, 2015-03-02 at 12:45 +0200, R.Smith wrote:
> Ok, I've found the source of the list duplications.
> 
> Some emails (Such as the one by J.K. Lowden 2-March-2015 re: Characters 
> corrupt after importing...) contains a "Reply-To" field in the header 
> with both list addresses which must have sneaked in there due to some 
> automatic list feature.  (By "Both" I mean the old: 
> sqlite-users at sqlite.org and the new: sqlite-users at 
> mailinglists.sqlite.org)

You don't need that, do you? Just hitting Reply All to a message which
is:
 To: sqlite-users at sqlite.org
 Reply-To: sqlite-users at mailinglists.sqlite.org

would generate a message which ends up going to both, wouldn't it?

(I can't easily test; I've configured my mailer to ignore abusive
Reply-To: headers from mailing lists where it can detect them, so my
Reply and Reply All buttons actually do what I *ask* them to.)

But looking at the first message in the 'PhD Student' thread, it appears
just as in my example above. And John KcKown's response of 26 Feb 2015
07:16:47 -0600 is indeed to both addresses, as if he'd done the correct
thing and simply hit 'Reply All'.

> I usually use the "Reply to List" button (Thunderbird) which replies 
> correctly,

Note that that is considered extremely anti-social in many cases,
because it cuts some people out of discussions entirely. See
http://david.woodhou.se/reply-to-list.html for a full discussion.

-- 
dwmw2

[sqlite] List duplication

[David Woodhouse]

On Sat, 2015-02-28 at 14:10 -0800, Darren Duncan wrote:
> 
> My comment on "whoever" is meant to be plural.
> 
> I think as a general principle that whenever people reply to list messages, 
> they 
> look at the headers of the message they are writing and make sure the list 
> doesn't appear twice in the recipients field.
> 
> If the result of hitting reply or reply-all to some message in a client puts 
> the 
> same recipient in the address bar twice, then the users should remove the 
> extra 
> copies, that's what I do, it can be simple as that.

That's ironic.

The problem here is the Reply-To: setting on the mailing list.

If a user hits 'Reply' on a message, that should always go to the sender
of the message they're looking at.

If a user hits 'Reply to All' on a message, *that* should go to
everyone.

This list has a 'Reply-To' header which hijacks the private reply so
that it goes to the list... which a lot of list admins do precisely
because they *don't* want their users to have to think at all, because
they assume their users are so dim that they can't even manage to
correctly choose between the private 'Reply' button and the public
'Reply All' button that's *right* next to it.

(To be fair, there's often a lot of truth in that assumption, but it's
largely a self-fulfilling prophecy because although things *should* be
simple, a lot of people are confused by what is *not* inconsistent
behaviour of the private "Reply" button which ought to *always* give a
private reply and is now just confusing because of this misguided
attempt to make things simpler.)

So what happens here is people do the right thing and hit 'Reply All' to
reply publicly, and that misguided Reply-To: header is corrupting the
behaviour so that their reply goes *both* to the added Reply-To: address
'sqlite-users at mailinglists.sqlite.org' *and* to the address which was in
the To: or Cc: of the previous message, 'sqlite-users at sqlite.org'.

So the only reason people now have to pay careful attention to the
recipients in their reply is because the list "helpfully" hacks
things... ostensibly so that they *don't* have to pay attention to what
they're doing. Nice :)

-- 
dwmw2

[sqlite] System.Data.SQLite version 1.0.95.0 released

[Joe Mistachkin]

System.Data.SQLite version 1.0.95.0 (with SQLite 3.8.8.3) is now available
on the System.Data.SQLite website:

 https://system.data.sqlite.org/

Further information about this release can be seen at

 https://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki

Please post on the SQLite mailing list (sqlite-users at sqlite.org) if you
encounter any problems with this release.

--
Joe Mistachkin

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[Igor Tandetnik]

On 3/2/2015 9:51 AM, Paolo Bolzoni wrote:
> I kinda get your point, but still we are speaking of the C interface
> all the time. Sure in other contexts, like the TCL/SQL, the indexes
> start from 1. However the bind and the column function are both in the
> C interface.

Not quite. There's ?N syntax you can use in your SQL statement to refer 
to a parameter number N. It may be confusing if ?1 had to be bound with 
index 1 in some contexts, and index 0 in others.
-- 
Igor Tandetnik

[sqlite] problem compiling std_call dll

[Bart Smissaert]

Thanks for clearing this up. I did wonder about this. Came across it when
making a .tlb for those calls to the std_call dll.

RBS


On Mon, Mar 2, 2015 at 6:14 AM, Scott Robison 
wrote:

> On Sun, Mar 1, 2015 at 10:55 PM, J Decker  wrote:
>
> > On Sun, Mar 1, 2015 at 9:22 PM, Keith Medcalf 
> wrote:
> >
> > > >I do not know if this is the case, but typically Windows creates names
> > > >like
> > > >_sqlite3_db_filename at X (where X is a number) if a function is defined
> > as
> > > >stdcall. It doesn't *have* to do this, but that's the convention used
> by
> > >
> > > the @X is used to include the ordinal reference (@X) in the
> > symbol
> > > since you can link by either name or ordinal (or, in the case of
> > specially
> > > constructed libraries, by both having to match).
> >
> > 1) the @X is the size of paramters pushed on the stack.
> > 2) (something else in another mail that irked me)  It's not a 'default
> > calling convention for windows' it's a default calling convention for
> some
> > compilers; and doesn't have be even be on windows.
> >
>
> 1. I didn't see you'd already explained the @X meaning. Sorry for
> duplicating your answer.
> 2. Just in case you meant me, I was not trying to say it is the default
> calling convention for windows. I was saying that appending "@X" to the
> symbol is the default convention used by (at least) Microsoft compilers
> when using the stdcall calling convention. It's a confusing bit of
> terminology what with multiple applications of the word "convention" so I
> just wanted to clarify what I meant. I understand Windows does not require
> everyone to use stdcall for their own APIs and such.
>
> --
> Scott Robison
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] full table scan ignores PK sort order?

[Jan Asselman]

Hi,

I created the following table in sqlite 3.8.6

"CREATE TABLE IF NOT EXISTS test
(
a  INTEGER,
b  INTEGER,
c INTEGER,
d  BLOB,
PRIMARY KEY (a, b DESC)
);"

When I execute the following query

"SELECT * FROM test WHERE b < ? AND c > ?;"

A full table scan is executed because column a is not part of the query.

But when I step over the rows they are not returned in primary key sort order. 
Why is this?

If I look at the images at the query planning document 
(https://www.sqlite.org/queryplanner.html) I get the idea that the primary key 
B-tree should be used to traverse the table when a full table scan is executed. 
And since the 'DESC' keyword is used on column b in the primary key, I would 
expect that, as the rowId increases, the values retuned for column b would 
decrease. But this is not the case.

Does a full table scan then ignore the PK B-tree? Does it perhaps scan all 
table pages in the order in which they appear in the file system? I guess this 
would make sense to increase traversal speed, since it needs to check all pages 
anyway?

If I explicitly order using an 'ORDER BY' statement then - looking at the query 
plan - sqlite seems to perform a full table scan and store the result in a 
temporary table which is then sorted. I'd like to avoid the memory consumption 
produced by this query plan...

Any help or information would be appreciated!
Thanks in advance!

Jan Asselman







iba AG: Supervisory Board: Horst Anhaus, chairman; Management Board: Dr. Ulrich 
Lettau CEO,  Marta Anhaus, Harald Opel; Registered Office: Fuerth/Germany; 
Registration Office: Fuerth, HRB 9865, Vat.Reg.No DE 132760166, WEEE-Reg.No. 
DE11469996

This message is intended only for the named recipient and may contain 
confidential or privileged information. Taking notice of this message by third 
parties is not permitted. If you have received it in error, please advise the 
sender by return e-mail and delete this message and any attachments. Any 
unauthorized use or dissemination of this information is strictly prohibited.

[sqlite] Characters corrupt after importing a CSV file

[Hick Gunter]

We do that here. Works well most of the time, but fails miserably if the first 
10 columns are not representative of the data. Also requires each and every 
query to be run twice. Not good for queries that require sorting of the result 
set (i.e. the ORDER BY clause is not fulfilled automatically by the chosen 
query plan).

-Urspr?ngliche Nachricht-
Von: James K. Lowden [mailto:jklowden at schemamania.org]
Gesendet: Montag, 02. M?rz 2015 05:28
An: sqlite-users at mailinglists.sqlite.org
Betreff: Re: [sqlite] Characters corrupt after importing a CSV file

On Thu, 26 Feb 2015 10:58:30 +
Adam Podstawczy?ski  wrote:

> While this solves the issue for me, I still believe this behavior is
> confusing ? truncated characters should be handled more gracefully.

To your point, I think the column and column-width features of the shell could 
benefit from some re-thinking.

My ~/.sqliterc includes these lines:

.mode column
.header ON

and truncated strings and numbers are a source of confusion and error even 
though I know how they work.

I wish that by default column formatting would be "soft" in the sense that 
printf(1) and printf(3) define: digits to the left of the decimal are never 
truncated, and strings are not truncated unless explicitly
requested:

$ printf '%5.4f\n' 123456789.123456789
123456789.1235
$ printf '%5.4s\n' 123456789.123456789
 1234

This would make the output "snakey" on the screen sometimes, but would never 
conceal important information.

I would change the width specification to accept the width and precision 
notation used by printf.  The statement

.width 8

would mean "set the minimum column width to 8", whereas

.width  8.8

would mean "set the minumum width to 8, minimum precision to 8, and truncate 
strings to 8".  I wouldn't worry about columns that mix strings and numbers; 
funny-looking strings in that case should be expected.  And, besides, don't do 
that.

It would also be nice to have a mode ".autowidth" that set column widths based 
on, say, the first 10 lines (and, preferably, their
labels) in a manner similar to column(1).  Provided it never truncated 
anything, I would use that more often than an improved .width.

Thoughts?

--jkl


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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] Sqlite and threads/multiple applications

[Will Fong]

Hi Olivier,

On Sun, Mar 1, 2015 at 5:22 PM, Olivier Vidal  wrote:
> What would be the most reliable operating system for Sqlite(last version)?

As other people have already mentioned, the operating system doesn't
matter. Pick the one you're most comfortable or interest in using. The
most reliable or secure OS is worthless if you don't care or know how
to use it.

What I believe is even more important (in terms of reliability) than
the OS is the underlying hardware. Namely because no one ever
considers hardware. This is true for all databases and even all
software. If the hardware says "yeah, I fysnc'ed!", it's not the OS's
fault if there was a file corruption due to power failure. These days,
a lot of people are using cloud infrastructure where they have
absolutely no control and no idea how things work under the hood.

-w