[sqlite] Mailing list policy change

[SQLite mailing list]

On 2015-10-28 10:34 PM, SQLite mailing list wrote:
> On 10/28/15, SQLite mailing list  
> wrote:
>> This is ridiculous.  I know how to handle spam.  I can do nothing
>> about not knowing who sent these emails.
>>
> One thing you could do is add a signature line, to tell the rest of us
> who you are  :-)
>

I think you've made his point for him precisely. If any of us fail to 
add such a line, as I never do, then it's a guess.

I'm starting to miss Alexa.

[sqlite] Simple Math Question

[SQLite mailing list]

On 28 Oct 2015, at 10:34pm, SQLite mailing list  wrote:

> This explains the deficiency in the SQLite print function, but it doesn't
> have to be that way.

I'm with a previous poster.  SQLite is primarily a database system.  Its 
primary jobs are storage and retrieval.  It shouldn't really be used to print 
at all and putting unusual effort into its print functions may not be wise.

However, I would support improvement in its floating point calculations, 
including implementing 'slop' in testing for equality.  This is not only for 
use when expressions include the equal sign, but also for cases where comparing 
two numbers is done in important internal operations, like checking that 
primary keys do not include duplicate entries.

However, I just tried to create some relevant problems:

SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> CREATE TABLE t(r REAL PRIMARY KEY,t TEXT);
sqlite> INSERT INTO t VALUES (1,'one'),(2,'two');
sqlite> INSERT INTO t VALUES (1.4,'one point four'),(1.5,'one point 
five'),(1.6,'one point six');;
sqlite> SELECT * FROM t;
1.0|one
2.0|two
1.4|one point four
1.5|one point five
1.6|one point six
sqlite> INSERT INTO t VALUES (1.6,'attempt duplication');
Error: UNIQUE constraint failed: t.r
sqlite> INSERT INTO t VALUES (1.3+0.3,'attempt duplication');
Error: UNIQUE constraint failed: t.r
sqlite> INSERT INTO t VALUES (1.2+0.2,'attempt duplication');
Error: UNIQUE constraint failed: t.r
sqlite> INSERT INTO t VALUES (0.2+0.8,'attempt duplication');
Error: UNIQUE constraint failed: t.r
sqlite> INSERT INTO t VALUES (0.3+0.7,'attempt duplication');
Error: UNIQUE constraint failed: t.r
sqlite> SELECT 0.3+0.7, (0.3+0.7) = (0.2+0.8);
1.0|1
sqlite> SELECT 0.3+0.7, (0.3+0.7) - 1, (0.3+0.7) = 1;
1.0|0.0|1
sqlite> SELECT 
(9.2+7.9+0+1.0+1.3+1.6),(9.2+7.9+0+1.0+1.3+1.6)-21,(9.2+7.9+0+1.0+1.3+1.6)=21.0;
21.0|3.5527136788005e-15|0

As you can see (remembering that 1 = TRUE), SQLite is correctly noticing the 
problem when it does pure comparisons, whether in internal collations or for 
other tests for equality.  What it's not doing is checking for near equality 
when printing.  And I'm happy with that.

Simon Slavin.

[sqlite] Mailing list policy change

[SQLite mailing list]

Yeah.  Let's not admit defeat to a lone a**hole.  My spam filter is bored 
anyway -- let's give it something to do. 

Eric

Sent from my iPhone

> On Oct 28, 2015, at 19:12, SQLite mailing list  mailinglists.sqlite.org> wrote:
> 
> I agree.  This cure is worse than the disease.
> 
> At least for now (from the 2 I got) the Alexa sender address was constant and 
> can be blacklisted.  Regardless of how Alexa got our email addresses, they 
> have them and can send spam like any spammer.
> 
> -- Darren Duncan
> 
>> On 2015-10-28 2:50 PM, SQLite mailing list wrote:
>> This really is awful and unworkable. There a re a few options
>> 
>> 1. maintain things as they are now - and everyone has to add a
>> signature line and we need to open every message to see who has sent
>> it. There are some posters I make a point of reading and just seeing
>> their name in a mail header makes me much more likely to open it.
>> 
>> 2. Somehow configure the system to display the senders name and not
>> their email address - seems frought with issues
>> 
>> 3. Go back to the old system and we have one more bit of spam that we
>> need to get rid of (something I have already done).
>> 
>> I vote for 3. Alexa was a minor inconvenience and the solution imposed
>> is much more of a PITA than she was.
>> 
>> 
>> 
>> 
>> On 28 October 2015 at 20:34, SQLite mailing list
>>  wrote:
>>> On 10/28/15, SQLite mailing list  
>>> wrote:
 
 This is ridiculous.  I know how to handle spam.  I can do nothing
 about not knowing who sent these emails.
>>> 
>>> One thing you could do is add a signature line, to tell the rest of us
>>> who you are  :-)
>>> 
>>> --
>>> D. Richard Hipp
>>> drh at sqlite.org
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Mailing list policy change

[SQLite mailing list]

This really is awful and unworkable. There a re a few options

1. maintain things as they are now - and everyone has to add a
signature line and we need to open every message to see who has sent
it. There are some posters I make a point of reading and just seeing
their name in a mail header makes me much more likely to open it.

2. Somehow configure the system to display the senders name and not
their email address - seems frought with issues

3. Go back to the old system and we have one more bit of spam that we
need to get rid of (something I have already done).

I vote for 3. Alexa was a minor inconvenience and the solution imposed
is much more of a PITA than she was.




On 28 October 2015 at 20:34, SQLite mailing list
 wrote:
> On 10/28/15, SQLite mailing list  
> wrote:
>>
>> This is ridiculous.  I know how to handle spam.  I can do nothing
>> about not knowing who sent these emails.
>>
>
> One thing you could do is add a signature line, to tell the rest of us
> who you are  :-)
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Simple Math Question

[SQLite mailing list]

On Wed, 28 Oct 2015 17:52:25 + Simon wrote:

> On 28 Oct 2015, at 5:08pm, James K. Lowden 
> wrote:
> 
> > If we accept what you say, above, then why should 
> > 
> >> (9.2+7.8+0+3.0+1.3+1.7)
> > 
> > in particular present any problem?  There's no division.  Each value
> > has an exact decimal representation.
> 
> You didn't work it out yourself, did you ?
> 
> 0.2 in binary is 0.0011001100110011...
> 0.3 in binary is 0.0100110011001100...
> 
> They both recur at the 1/16th level.  0.7 and 0.8 are, of course,
> their complements.  Only two tenths don't have problems in binary:
> point zero and point five.

I didn't work it out.  The assertion was

> > any base-2 representation right of the decimal should be
> > precise to represent in base-10

which I understood to mean, "if you can represent it in decimal, you
can represent it in binary".  I didn't think that was true, but there
seemed to be concensus that it was.   

Thanks for doing my homework.  :-)  

--jkl

P.S., To OFL, I wish the names were preserved in the From, so that
mail software preserves the "Simon said, James said" context.  It would
also be less damaging if the addresses merely mangled with e.g.
"-ciao-alexa" inserted. The malware is unlikely to adapt -- one way in
which it is *not* like a real virus -- and human beings can easily
remove the extra letters.  

One trick I've used with success is to insert the HTML zero-width space
character into the email address.  It looks the same, copies and pastes
just fine, but scripts scraping a page will copy it verbatim and get a
useless address.  

[sqlite] Mailing list policy change

[SQLite mailing list]

On Wed, Oct 28, 2015 at 9:08 PM, SQLite
 wrote:
>
> On 28 Oct 2015, at 7:36pm, General Discussion of SQLite Database 
>  wrote:
>
>> Has anybody received email from Alexa since the policy change?  I have 
>> not
>
> Nor me.  I reliably got one for every post I made for about a week before the 
> change.

This is ridiculous.  I know how to handle spam.  I can do nothing
about not knowing who sent these emails.

Dr Hipp, please reconsider.

[sqlite] Simple Math Question

[SQLite mailing list]

On 10/28/2015 7:25 PM, SQLite mailing list wrote:
> On 28 Oct 2015, at 11:23pm, SQLite mailing list  mailinglists.sqlite.org> wrote:
>
>> This can't possibly work. "Fuzzy equality" is not transitive (x is close 
>> enough to y, y is close enough to z, but x is just far enough from z to be 
>> non-equal), which would break any indexing scheme.
>
> Oh crumbs.  You're right.  I didn't think of it like that.  Is there a way to 
> do it correctly ?

None that I know of. Which is probably why no DB engine is doing 
anything like this.
-- 
Igor Tandetnik

[sqlite] Mailing list policy change

[SQLite]

On 28 Oct 2015, at 7:36pm, General Discussion of SQLite Database  wrote:

> Has anybody received email from Alexa since the policy change?  I have not

Nor me.  I reliably got one for every post I made for about a week before the 
change.

Simon.

[sqlite] Mailing list policy change

[SQLite]

Actually looking at this thread (in gmail) since the policy change is
a very retrograde step - all messages are displayed as  from SQLite.

There are numerous scenarios where I want to see the name of the
sender (not necessarily the email address) so that I can pick and
choose which messages I read.

I fear the cure here is going to be worse than the disease.
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 28 October 2015 at 19:46, SQLite
 wrote:
> Is this over-reacting a bit. I have had one email from alexa (about
> 3/4 weeks ago). If it starts to become a real problem then do
> something about it - until then I would think we all have more
> important things to worry about.
>
>
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
>
> On 28 October 2015 at 19:42, SQLite
>  wrote:
>> On Wed, Oct 28, 2015 at 11:22 AM, General Discussion of SQLite Database <
>> sqlite-users at mailinglists.sqlite.org> wrote:
>>
>>> On 28.10.2015 18:52, General Discussion of SQLite Database wrote:
>>>
 Hence, we have token the radical approach of denying the sender email
 address to*everyone*.

>>>
>>> Could you preserve the sender's name in the from header instead of
>>> substituting the generic "General Discussion of SQLite Database"?
>>>
>>> This would make it possible to automatically highlight messages by author,
>>> i.e. the SQLite dev team.
>>
>>
>> My suggestion is to go whole-hog and find a mailing-list system or host
>> which allows routing return addresses back through the server.  It could be
>> blob-7fe742b at mailinglists.sqlite.org , or it could even use info stripped
>> from the email, so ScottHess-7fe742b at mailinglists.sqlite.org.  The basic
>> goal being to have a readable part and an unpredictable part.  Then people
>> abusing the system in simple ways can be directly identified.  [If the
>> spammer is going to spend time looking up old email addresses, then
>> changing the list policies will take a long time to help, much, since there
>> are years of addresses already out there.]
>>
>> Another option would be to have the server forward emails with various
>> delays so that when people report spam you could (maybe) figure out by the
>> timing which subset of recipients were at fault.
>>
>> Personally, I'd rather know who's communicating on the channel and deal
>> with periodic spam.
>>
>> -scott (shess at google.com)
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Mailing list policy change

[SQLite]

Is this over-reacting a bit. I have had one email from alexa (about
3/4 weeks ago). If it starts to become a real problem then do
something about it - until then I would think we all have more
important things to worry about.



Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 28 October 2015 at 19:42, SQLite
 wrote:
> On Wed, Oct 28, 2015 at 11:22 AM, General Discussion of SQLite Database <
> sqlite-users at mailinglists.sqlite.org> wrote:
>
>> On 28.10.2015 18:52, General Discussion of SQLite Database wrote:
>>
>>> Hence, we have token the radical approach of denying the sender email
>>> address to*everyone*.
>>>
>>
>> Could you preserve the sender's name in the from header instead of
>> substituting the generic "General Discussion of SQLite Database"?
>>
>> This would make it possible to automatically highlight messages by author,
>> i.e. the SQLite dev team.
>
>
> My suggestion is to go whole-hog and find a mailing-list system or host
> which allows routing return addresses back through the server.  It could be
> blob-7fe742b at mailinglists.sqlite.org , or it could even use info stripped
> from the email, so ScottHess-7fe742b at mailinglists.sqlite.org.  The basic
> goal being to have a readable part and an unpredictable part.  Then people
> abusing the system in simple ways can be directly identified.  [If the
> spammer is going to spend time looking up old email addresses, then
> changing the list policies will take a long time to help, much, since there
> are years of addresses already out there.]
>
> Another option would be to have the server forward emails with various
> delays so that when people report spam you could (maybe) figure out by the
> timing which subset of recipients were at fault.
>
> Personally, I'd rather know who's communicating on the channel and deal
> with periodic spam.
>
> -scott (shess at google.com)
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Simple Math Question

[SQLite mailing list]

On 10/28/2015 6:52 PM, SQLite mailing list wrote:
> However, I would support improvement in its floating point calculations, 
> including implementing 'slop' in testing for equality. This is not only for 
> use when expressions include the equal sign, but also for cases where 
> comparing two numbers is done in important internal operations, like checking 
> that primary keys do not include duplicate entries.

This can't possibly work. "Fuzzy equality" is not transitive (x is close 
enough to y, y is close enough to z, but x is just far enough from z to 
be non-equal), which would break any indexing scheme.
-- 
Igor Tandetnik

[sqlite] Mailing list policy change

[General Discussion of SQLite Database]

On 28.10.2015 18:52, General Discussion of SQLite Database wrote:

> Hence, we have token the radical approach of denying the sender email
> address to*everyone*.

Could you preserve the sender's name in the from header instead of 
substituting the generic "General Discussion of SQLite Database"?

This would make it possible to automatically highlight messages by 
author, i.e. the SQLite dev team.

Ralf

[sqlite] Simple Math Question

[SQLite mailing list]

On Wed, Oct 28, 2015 at 6:29 PM, SQLite mailing list <
sqlite-users at mailinglists.sqlite.org> wrote:

> On 10/28/2015 7:25 PM, SQLite mailing list wrote:
>
>> On 28 Oct 2015, at 11:23pm, SQLite mailing list <
>> sqlite-users at mailinglists.sqlite.org> wrote:
>>
>> This can't possibly work. "Fuzzy equality" is not transitive (x is close
>>> enough to y, y is close enough to z, but x is just far enough from z to be
>>> non-equal), which would break any indexing scheme.
>>>
>>
>> Oh crumbs.  You're right.  I didn't think of it like that.  Is there a
>> way to do it correctly ?
>>
>
> None that I know of. Which is probably why no DB engine is doing anything
> like this.


It's probably why some have explicit decimal numeric types available.
Often in a database engine the overhead of a manual math implementation is
not the dominant factor in performance.

-scott

[sqlite] Simple Math Question

[SQLite mailing list]

>
> Those binary representations can be converted back into precise decimal
> representations, but those decimal representations will not be the original
> decimal values, because they were translated from decimal strings into
> binary floating-point values and back into decimal strings.
>
> -scott


This explains the deficiency in the SQLite print function, but it doesn't
have to be that way.

See: Steele, Jr., Guy L., and White, Jon L. How to print floating-point
numbers accurately. In Proc. ACM SIGPLAN ?90 Conf. Prog. Lang. Design and
Implementation. ACM (White Plains, NY, June 1990), 112?126. ACM SIGPLAN
Notices 25, 6 (June 1990).

A retrospective by Steele & White is here:

http://grouper.ieee.org/groups/754/email/pdfq3pavhBfih.pdf

I'm not advocating that SQLite add Steele & White's Dragon algorithm, just
pointing out that there are ways to fix the deficiency.

e

--
Doug Currie
doug.currie at gmail.com

[sqlite] Mailing list policy change

[General Discussion of SQLite Database]

>
> Could you preserve the sender's name in the from header instead of
> substituting the generic "General Discussion of SQLite Database"?
>
> This would make it possible to automatically highlight messages by
> author, i.e. the SQLite dev team.

I second that request!

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com

CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Mailing list policy change

[General Discussion of SQLite Database]

On 28 Oct 2015, at 5:52pm, General Discussion of SQLite Database  wrote:

> All replies must go back to the mailing list itself.

Erm ... just a warning from an experienced mailadmin.  If you do this exactly 
the way you described they can trigger an endless loop of spam just by 
subscribing Alexa's email address to this list.  So, of course, you have made 
this impossible.

Simon.

[sqlite] Simple Math Question

[General Discussion of SQLite Database]

On 28 Oct 2015, at 5:08pm, James K. Lowden  wrote:

> If we accept what you say, above, then why should 
> 
>> (9.2+7.8+0+3.0+1.3+1.7)
> 
> in particular present any problem?  There's no division.  Each value
> has an exact decimal representation.

You didn't work it out yourself, did you ?

0.2 in binary is 0.0011001100110011...
0.3 in binary is 0.0100110011001100...

They both recur at the 1/16th level.  0.7 and 0.8 are, of course, their 
complements.  Only two tenths don't have problems in binary: point zero and 
point five.

Simon.

[sqlite] Simple Math Question

[SQLite mailing list]

>>  (9.2+7.8+0+3.0+1.3+1.7)

>in particular present any problem?  There's no division.  Each value
>has an exact decimal representation.  I'm prepared to assert that any
>permutation of their sums also has an exact decimal representation.
>Therefore they should have an exact binary representation, too.  

Not true. They don't have an exact binary representation.
If you check these numbers here..

http://www.h-schmidt.net/FloatConverter/IEEE754.html

You'll find

9.2 -> 9.19809265137
7.8 -> 7.80190734863

and so on. So adding these numbers doesn't generate the same answer as doing it 
in base10 maths

Andy Ling
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

[sqlite] Mailing list policy change

[SQLite mailing list]

> Has anybody received email from Alexa since the policy change?  I have
> not

I have never received any ... presumably Alexa's MTA (s if more than one) is 
blacklisted ...

[sqlite] Mailing list policy change

[SQLite mailing list]

On 10/28/15, SQLite mailing list  
wrote:
>
> This is ridiculous.  I know how to handle spam.  I can do nothing
> about not knowing who sent these emails.
>

One thing you could do is add a signature line, to tell the rest of us
who you are  :-)

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Mailing list policy change

[SQLite mailing list]

I agree.  This cure is worse than the disease.

At least for now (from the 2 I got) the Alexa sender address was constant and 
can be blacklisted.  Regardless of how Alexa got our email addresses, they have 
them and can send spam like any spammer.

-- Darren Duncan

On 2015-10-28 2:50 PM, SQLite mailing list wrote:
> This really is awful and unworkable. There a re a few options
>
> 1. maintain things as they are now - and everyone has to add a
> signature line and we need to open every message to see who has sent
> it. There are some posters I make a point of reading and just seeing
> their name in a mail header makes me much more likely to open it.
>
> 2. Somehow configure the system to display the senders name and not
> their email address - seems frought with issues
>
> 3. Go back to the old system and we have one more bit of spam that we
> need to get rid of (something I have already done).
>
> I vote for 3. Alexa was a minor inconvenience and the solution imposed
> is much more of a PITA than she was.
>
>
>
>
> On 28 October 2015 at 20:34, SQLite mailing list
>  wrote:
>> On 10/28/15, SQLite mailing list  
>> wrote:
>>>
>>> This is ridiculous.  I know how to handle spam.  I can do nothing
>>> about not knowing who sent these emails.
>>>
>>
>> One thing you could do is add a signature line, to tell the rest of us
>> who you are  :-)
>>
>> --
>> D. Richard Hipp
>> drh at sqlite.org

[sqlite] Simple Math Question

[SQLite mailing list]

On Wed, Oct 28, 2015 at 3:52 PM, SQLite mailing list <
sqlite-users at mailinglists.sqlite.org> wrote:

> On 28 Oct 2015, at 10:34pm, SQLite mailing list <
> sqlite-users at mailinglists.sqlite.org> wrote:
> > This explains the deficiency in the SQLite print function, but it doesn't
> > have to be that way.
>
> I'm with a previous poster.  SQLite is primarily a database system.  Its
> primary jobs are storage and retrieval.  It shouldn't really be used to
> print at all and putting unusual effort into its print functions may not be
> wise.
>
> However, I would support improvement in its floating point calculations,
> including implementing 'slop' in testing for equality.  This is not only
> for use when expressions include the equal sign, but also for cases where
> comparing two numbers is done in important internal operations, like
> checking that primary keys do not include duplicate entries.
>

IMHO, this kind of thing can be subtle and full of bugs.  For purposes of a
value literally typed as a float, I think using the IEEE 754 value as a
blob is the right thing to do.

In addition, it would be reasonable to have a function for comparison
including an epsilon.  That wouldn't make sense for indices, though,
because of the problem where two values may not be within epsilon of each
other, but they may be within epsilon of a third, so insert success becomes
order dependent (in one order, 1 of 3 succeeds, in the other order 2 of 3
succeed).

Actually, I think I'd be willing to forbid UNIQUE plus REAL entirely :-).

-scott (shess at google.com)

[sqlite] Mailing list policy change

[General Discussion of SQLite Database]

On 10/28/15, General Discussion of SQLite Database
 wrote:
> On 2015-10-28 10:52 AM, General Discussion of SQLite Database wrote:
>> The reason for this change is to combat the "Alexa" spam.  For the
>> past few weeks, whenever anybody posts to the mailing list, that
>> person gets a reply from "Alexa"...
>
> While that was often the case, I recall someone saying they got the Alexa
> spam
> simply by subscribing to the list, without posting.  This implies a
> server-side
> leak.  Unless that poster was wrong. -- Darren Duncan
>

Has anybody received email from Alexa since the policy change?  I have not

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Mailing list policy change

[SQLite]

On Wed, Oct 28, 2015 at 1:46 PM, SQLite <
sqlite-users at mailinglists.sqlite.org> wrote:

> Is this over-reacting a bit. I have had one email from alexa (about
> 3/4 weeks ago). If it starts to become a real problem then do
> something about it - until then I would think we all have more
> important things to worry about.
>

For some people it is a larger problem. I've received a few (I think 4)
Alexa emails since this began. It sounds like some people get a lot more
(like DRH).

SDR


>
>
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
> -Forensic
> 
> Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
>
> On 28 October 2015 at 19:42, SQLite
>  wrote:
> > On Wed, Oct 28, 2015 at 11:22 AM, General Discussion of SQLite Database <
> > sqlite-users at mailinglists.sqlite.org> wrote:
> >
> >> On 28.10.2015 18:52, General Discussion of SQLite Database wrote:
> >>
> >>> Hence, we have token the radical approach of denying the sender email
> >>> address to*everyone*.
> >>>
> >>
> >> Could you preserve the sender's name in the from header instead of
> >> substituting the generic "General Discussion of SQLite Database"?
> >>
> >> This would make it possible to automatically highlight messages by
> author,
> >> i.e. the SQLite dev team.
> >
> >
> > My suggestion is to go whole-hog and find a mailing-list system or host
> > which allows routing return addresses back through the server.  It could
> be
> > blob-7fe742b at mailinglists.sqlite.org , or it could even use info
> stripped
> > from the email, so ScottHess-7fe742b at mailinglists.sqlite.org.  The basic
> > goal being to have a readable part and an unpredictable part.  Then
> people
> > abusing the system in simple ways can be directly identified.  [If the
> > spammer is going to spend time looking up old email addresses, then
> > changing the list policies will take a long time to help, much, since
> there
> > are years of addresses already out there.]
> >
> > Another option would be to have the server forward emails with various
> > delays so that when people report spam you could (maybe) figure out by
> the
> > timing which subset of recipients were at fault.
> >
> > Personally, I'd rather know who's communicating on the channel and deal
> > with periodic spam.
> >
> > -scott (shess at google.com)
> > ___
> > 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
>



-- 
Scott Robison

[sqlite] Mailing list policy change

[General Discussion of SQLite Database]

Effective immediately, the sender email address for mailing list posts
will be elided.  All replies must go back to the mailing list itself.

The reason for this change is to combat the "Alexa" spam.  For the
past few weeks, whenever anybody posts to the mailing list, that
person gets a reply from "Alexa" that contains not-safe-for-work
photos and also (presumably) malware.  We have tried other techniques
to thwart Alexa, but we have so far been unable to figure out which of
2000+ subscribers is providing Alexa with the sender's email address.
Hence, we have token the radical approach of denying the sender email
address to *everyone*.

This is sad.  It means that sending off-list replies (something I do
frequently) is no longer possible unless the sender includes their
email address in the signature line (as I do - see below).  But it is
what it is.  We live in a fallen world.  Pray for the wretched soul of
the Alexa spammer that he might turn from his wicked ways and find
forgiveness.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] index usage and boolean constants

[Marcus Rohrmoser]

Hi fellow-sqliters,

please forgive  my newbie question, I hope it wasn't asked similar recently. I 
didn't find a way to properly search the list archives to check.

CREATE TABLE demo (
  id INTEGER PRIMARY KEY
);
EXPLAIN QUERY PLAN SELECT * FROM demo WHERE 0 OR id=3;
EXPLAIN QUERY PLAN SELECT * FROM demo WHERE id=3;

doesn't use the index in case 1, only in the 2nd case.

Is there a way to hint the query analyzer to use the index? What other tricks 
come into your mind?

Cheers,
Marcus

P.S.: my complete use case is this 
https://github.com/mro/librdf.sqlite/issues/11

[sqlite] Mailing list policy change

[SQLite]

On Wed, Oct 28, 2015 at 1:32 PM, General Discussion of SQLite Database <
sqlite-users at mailinglists.sqlite.org> wrote:

> On 2015-10-28 10:52 AM, General Discussion of SQLite Database wrote:
>
>> The reason for this change is to combat the "Alexa" spam.  For the
>> past few weeks, whenever anybody posts to the mailing list, that
>> person gets a reply from "Alexa"...
>>
>
> While that was often the case, I recall someone saying they got the Alexa
> spam simply by subscribing to the list, without posting.  This implies a
> server-side leak.  Unless that poster was wrong. -- Darren Duncan


I (Scott Robison) tried to exercise that by signing up a new account with a
new email address and never received Alexa spam to the new address with my
(very obviously faked) user name. I can't say whether it is because the
list admins saw the (very obviously faked) account and deleted it (as they
did a day or so later) or if the Alexa spam generator requires manual
intervention, but at the very least the process of signing up for the
address was not enough.

Also, I have not received Alexa spam to every email I have sent to the
list. I've received a few, but not every time.

-- 
Scott Robison

[sqlite] Simple Math Question

[James K. Lowden]

On Fri, 23 Oct 2015 10:43:44 -0700
Scott Hess  wrote:

> You're right, any base-2 representation right of the decimal should be
> precise to represent in base-10.  But it's the kind of thing where if
> you find yourself counting on it, you probably made a grave error
> earlier in your design :-).

I'm either brave or naive enough to think I can still add to this
discussion.  If we accept what you say, above, then why should 

>  (9.2+7.8+0+3.0+1.3+1.7)

in particular present any problem?  There's no division.  Each value
has an exact decimal representation.  I'm prepared to assert that any
permutation of their sums also has an exact decimal representation.
Therefore they should have an exact binary representation, too.  

To the OP, I want to point out that whether or not a fraction can be
presented exactly is a function of the base.   Consider that 1/3 has no
finite decimal representation.  But in base 3 it's just 

0.1

--jkl

[sqlite] Mailing list policy change

[SQLite]

On Wed, Oct 28, 2015 at 11:22 AM, General Discussion of SQLite Database <
sqlite-users at mailinglists.sqlite.org> wrote:

> On 28.10.2015 18:52, General Discussion of SQLite Database wrote:
>
>> Hence, we have token the radical approach of denying the sender email
>> address to*everyone*.
>>
>
> Could you preserve the sender's name in the from header instead of
> substituting the generic "General Discussion of SQLite Database"?
>
> This would make it possible to automatically highlight messages by author,
> i.e. the SQLite dev team.


My suggestion is to go whole-hog and find a mailing-list system or host
which allows routing return addresses back through the server.  It could be
blob-7fe742b at mailinglists.sqlite.org , or it could even use info stripped
from the email, so ScottHess-7fe742b at mailinglists.sqlite.org.  The basic
goal being to have a readable part and an unpredictable part.  Then people
abusing the system in simple ways can be directly identified.  [If the
spammer is going to spend time looking up old email addresses, then
changing the list policies will take a long time to help, much, since there
are years of addresses already out there.]

Another option would be to have the server forward emails with various
delays so that when people report spam you could (maybe) figure out by the
timing which subset of recipients were at fault.

Personally, I'd rather know who's communicating on the channel and deal
with periodic spam.

-scott (shess at google.com)

[sqlite] Mailing list policy change

[General Discussion of SQLite Database]

On 2015-10-28 10:52 AM, General Discussion of SQLite Database wrote:
> The reason for this change is to combat the "Alexa" spam.  For the
> past few weeks, whenever anybody posts to the mailing list, that
> person gets a reply from "Alexa"...

While that was often the case, I recall someone saying they got the Alexa spam 
simply by subscribing to the list, without posting.  This implies a server-side 
leak.  Unless that poster was wrong. -- Darren Duncan

[sqlite] Mailing list policy change

[General Discussion of SQLite Database]

On 2015-10-28 11:25 AM, General Discussion of SQLite Database wrote:
>>
>> Could you preserve the sender's name in the from header instead of
>> substituting the generic "General Discussion of SQLite Database"?
>>
>> This would make it possible to automatically highlight messages by
>> author, i.e. the SQLite dev team.
>
> I second that request!

I third that request!

Even if the sender email address is hidden, it is immensely important to know 
at 
a glance (in the headers) who is the one speaking.  The SQLite lists receive a 
lot of traffic and I only read a fraction of them, often deciding what to read 
by who sent it (along with subject).

The list message headers should still have 2 email addresses, one being the 
list 
address and name as usual, and the other being the sender's name, but for them 
have a faux address such as no-reply at mailinglists.sqlite.org so that 
people's 
address books don't automatically associate some random poster's name with the 
mailing list itself.

-- Darren Duncan

[sqlite] SIGSEGV in sqlite3_open

[Lohmann, Niels, Dr. (CQTN)]

Hi there,

unfortunately, I cannot create a better stack trace on short notice.

However, you may be able to reproduce the problem as follows:

- Create an in-memory database.
- Release it.
- Create an in-memory database.
- Release it.

The first run succeeds, whereas the second one yields a crash when using 
"?cache=shared".

Without shared cache, both runs succeed.

I hope this helps.

Regards,
Niels

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Richard 
Hipp
Gesendet: Mittwoch, 28. Oktober 2015 12:42
An: General Discussion of SQLite Database
Cc: Remuss, Volker (CQTN)
Betreff: Re: [sqlite] SIGSEGV in sqlite3_open

On 10/28/15, Lohmann, Niels, Dr. (CQTN)  wrote:
> Hi there,
>
> unfortunately, the stack trace is truncated:
>
> (gdb) bt
> #0  0x010385c4 in SignalProcmask_r () from
> C:\QNX650\target\qnx6/armle-v7/lib/libc.so.3
> #1  0x010206d8 in pthread_sigmask () from
> C:\QNX650\target\qnx6/armle-v7/lib/libc.so.3
> #2  0x01028acc in __abort () from
> C:\QNX650\target\qnx6/armle-v7/lib/libc.so.3
> #3  0x0038fc00 in defaultAbort ()
> #4  0x0012ebfc in nsc::NavServerApplication::onSignal (this=0x58a718,
> signalInfo=0xdf3ad4, data=) at
> ../public/../source/navapplApplication.cpp:4226
> #5  0x0036dd20 in aaf::localSignalHandlerExtended ()
> #6  0x0038f924 in genericSignalHandler ()
> #7  
> #8  0x78991330 in ?? () from libsqlite_shared.so
> Cannot access memory at address 0x329457c
>
>
> Meanwhile, we found out that replacing "file::memory:?cache=shared" by
> "file::memory:" may avoid the problem. We have not tested it thouroughly.
> What do you think? Could the shared cache be the reason for the issue?
>

You might have found some really obscure issue in SQLite.  But that
will be hard to determine unless we can reproduce the problem, or at
least get a complete stack trace.

Can you statically link against SQLite instead of using a shared
library, and use -g when compiling?  That might give a better stack
trace.

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

[sqlite] Simple Math Question

[General Discussion of SQLite Database]

On Wed, Oct 28, 2015 at 10:08 AM, James K. Lowden 
wrote:

> On Fri, 23 Oct 2015 10:43:44 -0700 Scott Hess  wrote:
> > You're right, any base-2 representation right of the decimal should be
> > precise to represent in base-10.  But it's the kind of thing where if
> > you find yourself counting on it, you probably made a grave error
> > earlier in your design :-).
>
> I'm either brave or naive enough to think I can still add to this
> discussion.  If we accept what you say, above, then why should
>
> >  (9.2+7.8+0+3.0+1.3+1.7)
>
> in particular present any problem?  There's no division.  Each value
> has an exact decimal representation.  I'm prepared to assert that any
> permutation of their sums also has an exact decimal representation.
> Therefore they should have an exact binary representation, too.


Of those numbers, only 0 and 3.0 have an exact binary representation:
  echo 9.2 7.8 0 3.0 1.3 1.7 | xargs -n1 -I{} printf "{} is %a\n" {}
  9.2 is 0x1.2p+3
  7.8 is 0x1.fp+2
  0 is 0x0p+0
  3.0 is 0x1.8p+1
  1.3 is 0x1.4cccdp+0
  1.7 is 0x1.bp+0

Those binary representations can be converted back into precise decimal
representations, but those decimal representations will not be the original
decimal values, because they were translated from decimal strings into
binary floating-point values and back into decimal strings.

-scott

[sqlite] Casting ctype functions' arguments

[SQLite mailing list]

Hi, everyone.

I've been auditing the OpenBSD codebase for calls to ctype functions
with potentially signed chars. This is undefined on some platforms. I
found a number of instances in Sqlite, so I cloned your repo and ran my
script on it.

Here's the relevant CERT entry:


https://www.securecoding.cert.org/confluence/display/c/STR37-C.+Arguments+to+character-handling+functions+must+be+representable+as+an+unsigned+char

The below diff was generated by Coccinelle, an extremely useful
automated tool, so some of the formatting may be odd and mistakes are
possible.

Let me know what you think.

Thanks,
Michael


Index: autoconf/tea/win/nmakehlp.c
==
--- autoconf/tea/win/nmakehlp.c
+++ autoconf/tea/win/nmakehlp.c
@@ -603,15 +603,15 @@
sp = fopen(substitutions, "rt");
if (sp != NULL) {
while (fgets(szBuffer, cbBuffer, sp) != NULL) {
char *ks, *ke, *vs, *ve;
ks = szBuffer;
-   while (ks && *ks && isspace(*ks)) ++ks;
+   while (ks && *ks && isspace((unsigned char)*ks)) ++ks;
ke = ks;
-   while (ke && *ke && !isspace(*ke)) ++ke;
+   while (ke && *ke && !isspace((unsigned char)*ke)) ++ke;
vs = ke;
-   while (vs && *vs && isspace(*vs)) ++vs;
+   while (vs && *vs && isspace((unsigned char)*vs)) ++vs;
ve = vs;
while (ve && *ve && !(*ve == '\r' || *ve == '\n')) ++ve;
*ke = 0, *ve = 0;
list_insert(, ks, vs);
}

Index: ext/fts1/fts1.c
==
--- ext/fts1/fts1.c
+++ ext/fts1/fts1.c
@@ -203,17 +203,17 @@
 ** tokenizer-generated tokens rather than doing its own local
 ** tokenization.
 */
 /* TODO(shess) Is __isascii() a portable version of (c&0x80)==0? */
 static int safe_isspace(char c){
-  return (c&0x80)==0 ? isspace(c) : 0;
+  return (c&0x80)==0 ? isspace((unsigned char)c) : 0;
 }
 static int safe_tolower(char c){
-  return (c&0x80)==0 ? tolower(c) : c;
+  return (c&0x80)==0 ? tolower((unsigned char)c) : c;
 }
 static int safe_isalnum(char c){
-  return (c&0x80)==0 ? isalnum(c) : 0;
+  return (c&0x80)==0 ? isalnum((unsigned char)c) : 0;
 }

 typedef enum DocListType {
   DL_DOCIDS,  /* docids only */
   DL_POSITIONS,   /* docids + positions */

Index: ext/fts1/simple_tokenizer.c
==
--- ext/fts1/simple_tokenizer.c
+++ ext/fts1/simple_tokenizer.c
@@ -136,11 +136,11 @@
   for(ii=0; iipCurrent[ii];
-c->zToken[ii] = (unsigned char)ch<0x80 ? tolower(ch) : ch;
+c->zToken[ii] = (unsigned char)ch<0x80 ? tolower((unsigned char)ch) : 
ch;
   }
   c->zToken[n] = '\0';
   *ppToken = c->zToken;
   *pnBytes = n;
   *piStartOffset = (int) (c->pCurrent-c->pInput);

Index: ext/misc/amatch.c
==
--- ext/misc/amatch.c
+++ ext/misc/amatch.c
@@ -814,14 +814,14 @@
   int nKey = (int)strlen(zKey);
   int nStr = (int)strlen(zStr);
   int i;
   if( nStr0 && isspace(zOut[i-1]) ){ i--; }

Index: mptest/mptest.c

[sqlite] SIGSEGV in sqlite3_open

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/28/2015 01:48 AM, Lohmann, Niels, Dr. (CQTN) wrote:
> #0  0x010385c4 in SignalProcmask_r () from
> C:\QNX650\target\qnx6/armle-v7/lib/libc.so.3

Have you tried turning off all compiler optimisations?  Some
older/embedded system compilers can produce crashing code.

> #8  0x78991330 in ?? () from libsqlite_shared.so Cannot access
> memory at address 0x329457c

One possible way this can happen is if shared libraries get unloaded.
 I experienced this in the past with an Apache module where it got
loaded to parse its configuration, then got unloaded, then reloaded to
actually use it.  Address space randomisation meant it got loaded at a
different address.  Things got very interesting trying to debug what
was going on.

> Meanwhile, we found out that replacing "file::memory:?cache=shared"
> by "file::memory:" may avoid the problem. We have not tested it
> thouroughly. What do you think?

The chances of it being an SQLite issue are *very* slim, but not
non-existent.  SQLite is used all over the place (many billions of
apps), and they all use sqlite3_open, although way fewer use the
shared cache.  Also https://www.sqlite.org/testing.html

This is far more likely an issue with the platform (QNX 6.5 is not the
most recent release), compiler or your app itself.  Stray writes from
elsewhere in the app code can easily cause problems within SQLite
which does a lot of checking.  A strong memory checking tool like
Valgrind makes them relatively easy to find.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v2

iEYEARECAAYFAlYw/voACgkQmOOfHg372QS2CwCghtnlWi00gtYawiuDX+M4/jMp
dX4AnRbX1vGnFzz45QUwZOO6WIgnRrOw
=C6a8
-END PGP SIGNATURE-

[sqlite] index usage and boolean constants

[Richard Hipp]

On 10/28/15, Marcus Rohrmoser  wrote:
>
> CREATE TABLE demo (
>   id INTEGER PRIMARY KEY
> );
> EXPLAIN QUERY PLAN SELECT * FROM demo WHERE 0 OR id=3;
> EXPLAIN QUERY PLAN SELECT * FROM demo WHERE id=3;
>
> doesn't use the index in case 1, only in the 2nd case.
>
> Is there a way to hint the query analyzer to use the index? What other
> tricks come into your mind?
>

The query planner in SQLite does not (currently) recognize the special
case of "0 OR ...".  Hence, the only way to get it to use an index is
the second form of the query.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] SIGSEGV in sqlite3_open

[Richard Hipp]

On 10/28/15, Lohmann, Niels, Dr. (CQTN)  wrote:
> Hi there,
>
> unfortunately, I cannot create a better stack trace on short notice.
>
> However, you may be able to reproduce the problem as follows:
>
> - Create an in-memory database.
> - Release it.
> - Create an in-memory database.
> - Release it.
>
> The first run succeeds, whereas the second one yields a crash when using
> "?cache=shared".
>

My test program is shown below.  It works fine for me.  Perhaps you
can suggest a modification that leads to a crash.

-  begin attachment -
#include 
#include 
#include "sqlite3.h"

/* Check a return code to verify it is SQLITE_OK.  If it is not,
** print an error and panic. */
static void checkReturn(int rc){
  if( rc!=SQLITE_OK ){
printf("Error: (%d) %s\n", rc, sqlite3_errstr(rc));
exit(1);
  }
}

/* Print a single row of output from an sqlite3_exec() query */
static int execCallback(void *notUsed, int argc, char **argv, char **colv){
  int i;
  for(i=0; i

[sqlite] SIGSEGV in sqlite3_open

[Lohmann, Niels, Dr. (CQTN)]

Hi there,

unfortunately, the stack trace is truncated:

(gdb) bt
#0  0x010385c4 in SignalProcmask_r () from 
C:\QNX650\target\qnx6/armle-v7/lib/libc.so.3
#1  0x010206d8 in pthread_sigmask () from 
C:\QNX650\target\qnx6/armle-v7/lib/libc.so.3
#2  0x01028acc in __abort () from C:\QNX650\target\qnx6/armle-v7/lib/libc.so.3
#3  0x0038fc00 in defaultAbort ()
#4  0x0012ebfc in nsc::NavServerApplication::onSignal (this=0x58a718, 
signalInfo=0xdf3ad4, data=) at 
../public/../source/navapplApplication.cpp:4226
#5  0x0036dd20 in aaf::localSignalHandlerExtended ()
#6  0x0038f924 in genericSignalHandler ()
#7  
#8  0x78991330 in ?? () from libsqlite_shared.so
Cannot access memory at address 0x329457c


Meanwhile, we found out that replacing "file::memory:?cache=shared" by 
"file::memory:" may avoid the problem. We have not tested it thouroughly. What 
do you think? Could the shared cache be the reason for the issue?

Best Regards,
Niels


-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Richard 
Hipp
Gesendet: Dienstag, 27. Oktober 2015 17:17
An: General Discussion of SQLite Database
Cc: Remuss, Volker (CQTN)
Betreff: Re: [sqlite] SIGSEGV in sqlite3_open

On 10/27/15, Lohmann, Niels, Dr. (CQTN)  wrote:
> Hi there,
>
> I experienced a SIGSEGV running the following code:

Can you provide us with a stack trace at the point of the crash?

>
> sqlite3* memory_db = NULL;
> sqlite3_open("file::memory:?cache=shared", _db);
>
> To be exact, it occurs when executed a second time. The whole function looks
> like this
>
> void init() {
>   sqlite3* memory_db = NULL;
>   sqlite3_open("file::memory:?cache=shared", _db);
>
>   // fill memory_db
>   // attach memory_db to other databases
>
>   sqlite3_close(memory_db);
>   memory_db = NULL;
>
>  // close other database connections
> }
>
> Executing init() the first time yields no error, and everything works as
> expected.
>
> Executing init() the second time yields the SIGSEGV on the sqlite3_open
> call.
>
> Do you have any idea about why this could be? There seems to be some kind of
> state stored somewhere. Are there any traces of the memory database that
> need to be closed/removed/freed/released?
>
> Thanks a lot for your help.
>
> All the best,
> Niels
> ___
> 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-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SIGSEGV in sqlite3_open

[Richard Hipp]

On 10/28/15, Lohmann, Niels, Dr. (CQTN)  wrote:
> Hi there,
>
> unfortunately, the stack trace is truncated:
>
> (gdb) bt
> #0  0x010385c4 in SignalProcmask_r () from
> C:\QNX650\target\qnx6/armle-v7/lib/libc.so.3
> #1  0x010206d8 in pthread_sigmask () from
> C:\QNX650\target\qnx6/armle-v7/lib/libc.so.3
> #2  0x01028acc in __abort () from
> C:\QNX650\target\qnx6/armle-v7/lib/libc.so.3
> #3  0x0038fc00 in defaultAbort ()
> #4  0x0012ebfc in nsc::NavServerApplication::onSignal (this=0x58a718,
> signalInfo=0xdf3ad4, data=) at
> ../public/../source/navapplApplication.cpp:4226
> #5  0x0036dd20 in aaf::localSignalHandlerExtended ()
> #6  0x0038f924 in genericSignalHandler ()
> #7  
> #8  0x78991330 in ?? () from libsqlite_shared.so
> Cannot access memory at address 0x329457c
>
>
> Meanwhile, we found out that replacing "file::memory:?cache=shared" by
> "file::memory:" may avoid the problem. We have not tested it thouroughly.
> What do you think? Could the shared cache be the reason for the issue?
>

You might have found some really obscure issue in SQLite.  But that
will be hard to determine unless we can reproduce the problem, or at
least get a complete stack trace.

Can you statically link against SQLite instead of using a shared
library, and use -g when compiling?  That might give a better stack
trace.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00

[Dominique Devienne]

On Tue, Oct 27, 2015 at 11:05 PM, James K. Lowden 
wrote:

> On Tue, 27 Oct 2015 12:24:03 +0100 Clemens Ladisch 
> wrote:
> > > You've solved the problem by compiling for a
> single-threaded environment.
> > No; the single-threaded environment is the problem.
>
> That's a matter of opinion.  Another way to look at it: threads set
> back computing by 20 years.
>
> Protected memory was invented for good reason.  Then it was uninvented,
> and we're still dealing with the fallout.  Software transactional
> memory has so far failed to pass muster, and communicating sequential
> processes has still not seen implementation support in most languages.


Most languages, true. But one (new'ish) language, Go [1],
embraced CSP from the get go [2].

This talk [3] by Rob Pike, one of the three fathers of Go,
featured on the front page [1] discusses specifically Go's CSP
implementation.
It goes hand-in-hand with this other "classical" Go talk [4]: Concurrency
Is Not Parallelism.

[1] https://golang.org/
[2] https://blog.golang.org/share-memory-by-communicating
[3] https://www.youtube.com/watch?v=f6kdp27TYZs
[4] https://www.youtube.com/watch?v=cN_DpYBzKso

[sqlite] FTS5 External Content Table - REPLACE - SQL logic error or missing database

[Dan Kennedy]

On 10/27/2015 05:06 PM, chromedout64 at yahoo.com wrote:
> Using the example from the documentation at https://www.sqlite.org/fts5.html
> I thought this worked in a previous version.
>
> CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c);
> CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', 
> content_rowid='a');
> CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN
>INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
> END;
> CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN
>INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, 
> old.c);
> END;
> CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN
>INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, 
> old.c);
>INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
> END;
>
> REPLACE INTO tbl VALUES(1,'foo','bar');

I think this may have worked with an older version of FTS5 that did not 
support REPLACE.

The trouble is that because the top-level statement uses OR REPLACE 
conflict handling, so do the INSERT statements fired by the triggers. 
And OR REPLACE conflict handling was broken for external content tables.

Fts5 is now updated on trunk so that OR REPLACE is ignored for 
contentless and external content tables:

   http://sqlite.org/src/info/a85c2a4758c27e8d

So this sort of thing should work again.

Dan.