Re: [sqlite] how into insert row into middle of table with integer primary key

[petern]

Shane.

Below is a simple benchmark you can play with to decide if that trigger is
fast enough for your application.  On the time scale of human thinking and
reaction time, I've found SQLite code quite responsive and magnitudes
easier to maintain than the equivalent application code.

FYI, that trigger will fly past the first two UPDATE statements if no id's
match the WHERE clauses.  So, if your insert collisions are infrequent,
there will be no measurable penalty for routinely inserting into the view.

CREATE ids(id INTEGER PRIMARY KEY);
CREATE VIEW ids_ins AS SELECT * FROM ids;
CREATE TRIGGER ids_ins INSTEAD OF INSERT ON ids_ins
BEGIN
  UPDATE ids SET id = -id-1 WHERE id >= NEW.id;
  UPDATE ids SET id = -id WHERE id < 0;
  INSERT INTO ids VALUES (NEW.id);
END;

--insert a million rows

sqlite> WITH genids AS (SELECT (1)id UNION ALL SELECT (id+1)id FROM genids)
INSERT INTO ids SELECT * FROM genids LIMIT 1e6;
Run Time: real 1.903 user 1.136000 sys 0.048000

sqlite> SELECT count() FROM ids;
count()
100
Run Time: real 0.006 user 0.00 sys 0.008000

--move a million rows out of the way and back again...

sqlite> INSERT INTO ids_ins VALUES(1);
Run Time: real 5.853 user 4.732000 sys 0.148000

sqlite> SELECT count() FROM ids;
count()
101
Run Time: real 0.006 user 0.004000 sys 0.00


On Tue, Nov 21, 2017 at 10:40 PM, Shane Dev  wrote:

> Hi Igor,
>
> Homework exercise? No, this is purely a hobby project in my free time. My
> goal is see how much logic can moved from application code to the database.
>
> Why do I want store ID numbers whose values may change? Why not. Obviously,
> this would be bad idea if the ID column was referenced by other column /
> table. In that case, I would have created a different table such as
>
> sqlite> .sch fruit
> CREATE TABLE fruit(id integer primary key, sort integer unique, name text);
>
> However, this just moves the problem from the id to the sort column. I
> still have to consider how to manage changes to values in the sort column.
> Apparently there is no single SQL statement which can insert a record in to
> any arbitrary sort position. Even if I use the stepped approach (fruit.sort
> = 100, 200, 300 ...) or define sort as real unique, I will still need to
> determine if it is necessary to reset the gaps between sort column values.
> Peter Nichvolodov's trigger solution (
> https://www.mail-archive.com/sqlite-users@mailinglists.
> sqlite.org/msg106788.html)
> is elegant, but might be slow if the table had many entries.
>
>
> On 22 November 2017 at 00:11, Igor Korot  wrote:
>
> > Simon,
> >
> > On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin 
> > wrote:
> > >
> > >
> > > On 21 Nov 2017, at 10:09pm, Jens Alfke  wrote:
> > >
> > >>> On Nov 21, 2017, at 1:56 AM, R Smith  wrote:
> > >>>
> > >>> That assumes you are not starting from an integer part (like 4000)
> and
> > hitting the exact same relative insert spot every time, which /can/
> happen,
> > but is hugely unlikely.
> > >>
> > >> Not to beat this into the ground, but: it’s not that unlikely. Let’s
> > say you sort rows by date. You’ve already got some entries from 2015 in
> > your database, and some from 2017. Someone now inserts 60 entries from
> > 2016, and to be ‘helpful’, they insert them in chronological order. Wham,
> > this immediately hits that case.
> > >
> > > Yes, if you use this method, you do need to renumber them every so
> > often.  You assess this when you’re working out (before + after) / 2, and
> > you do it using something like the double-UPDATE command someone came up
> > with earlier.
> > >
> > > But that just brings us back to the question of why OP wants to store
> ID
> > numbers which might change.
> >
> > Homework exercise?
> > Stupid requirements?
> >
> > Thank you.
> >
> > >
> > > Simon.
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how into insert row into middle of table with integer primary key

[Clemens Ladisch]

Shane Dev wrote:
> Why do I want store ID numbers whose values may change? Why not.

Because the name "ID" implies that its value _identifies_ the row.
If it changes, it is not an ID.

> Obviously, this would be bad idea if the ID column was referenced by
> other column / table. In that case, I would have created a different
> table such as
>
> CREATE TABLE fruit(id integer primary key, sort integer unique, name text);
>
> However, this just moves the problem from the id to the sort column.

Now updates to the sort column no longer change the rowid, so no longer
require moving the entire row around.

> I still have to consider how to manage changes to values in the sort
> column.

You could make updates much easier by dropping the UNIQUE constraint on
the sort column.

> Apparently there is no single SQL statement which can insert a record
> in to any arbitrary sort position.

If you have a short list (short enough that the user can rearrange them
randomly with the mouse), then just updating all values is no problem.

If you have a large list, then you should use a data structure that is
more suitable for random insertions.  The table above is the equivalent
of an array; the equivalent of a linked list would be this:

CREATE TABLE fruit (
  id   INTEGER PRIMARY KEY,
  next INTEGER REFERENCES fruit,
  name TEXT
);

id  next  name
42   23   apple
235   banana
569   pear
69  NULL  kiwi

Now insertion requires only updating one other pointer.  (But querying
must be done with a CTE.)


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

Re: [sqlite] how into insert row into middle of table with integer primary key

[R Smith]

On 2017/11/22 2:29 AM, Jens Alfke wrote:



On Nov 21, 2017, at 2:48 PM, Simon Slavin  wrote:

But that just brings us back to the question of why OP wants to store ID 
numbers which might change.

When I’ve run into this before, the requirement has been to support lists with 
customizable ordering, like an outliner where the user can freely drag the rows 
up and down.


Oh there are many valid reasons why to have Order in data, one I use 
regularly is to dictate the process flow in manufacturing where some 
thing needs to go to machine Y before it can move on to machine X, or 
process E, for a specific item, has to happen before process B etc.


The problem is not that "Order" by itself is silly to have in data, the 
problem is that the OP intended (at first) to gain such order by 
manipulating/relying on the PRIMARY KEY value expecting the DB itself to 
have intrinsic order, which is folly. (A bit like changing your Surname 
to adjust your place in the phone-book or indicate your position in a 
race result.)


I think the OP has been swayed from this view so it is no longer a 
problem. Only remaining detail is how to best maintain the order when 
correctly kept as a separate entity. I think the examples already 
discussed will do perfectly when implemented wisely.



Cheers,
Ryan

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

Re: [sqlite] how into insert row into middle of table with integer primary key

[Shane Dev]

Hi Igor,

Homework exercise? No, this is purely a hobby project in my free time. My
goal is see how much logic can moved from application code to the database.

Why do I want store ID numbers whose values may change? Why not. Obviously,
this would be bad idea if the ID column was referenced by other column /
table. In that case, I would have created a different table such as

sqlite> .sch fruit
CREATE TABLE fruit(id integer primary key, sort integer unique, name text);

However, this just moves the problem from the id to the sort column. I
still have to consider how to manage changes to values in the sort column.
Apparently there is no single SQL statement which can insert a record in to
any arbitrary sort position. Even if I use the stepped approach (fruit.sort
= 100, 200, 300 ...) or define sort as real unique, I will still need to
determine if it is necessary to reset the gaps between sort column values.
Peter Nichvolodov's trigger solution (
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg106788.html)
is elegant, but might be slow if the table had many entries.


On 22 November 2017 at 00:11, Igor Korot  wrote:

> Simon,
>
> On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin 
> wrote:
> >
> >
> > On 21 Nov 2017, at 10:09pm, Jens Alfke  wrote:
> >
> >>> On Nov 21, 2017, at 1:56 AM, R Smith  wrote:
> >>>
> >>> That assumes you are not starting from an integer part (like 4000) and
> hitting the exact same relative insert spot every time, which /can/ happen,
> but is hugely unlikely.
> >>
> >> Not to beat this into the ground, but: it’s not that unlikely. Let’s
> say you sort rows by date. You’ve already got some entries from 2015 in
> your database, and some from 2017. Someone now inserts 60 entries from
> 2016, and to be ‘helpful’, they insert them in chronological order. Wham,
> this immediately hits that case.
> >
> > Yes, if you use this method, you do need to renumber them every so
> often.  You assess this when you’re working out (before + after) / 2, and
> you do it using something like the double-UPDATE command someone came up
> with earlier.
> >
> > But that just brings us back to the question of why OP wants to store ID
> numbers which might change.
>
> Homework exercise?
> Stupid requirements?
>
> Thank you.
>
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how into insert row into middle of table with integer primary key

[Wout Mertens]

On Tue, Nov 21, 2017, 11:10 PM Jens Alfke,  wrote:

>
> It’s a lot better to use strings, and just increase the length of the
> string as necessary. So to insert in between “A” and “C” you add “B”, then
> to insert between “A” and “B” you add “AM”, etc.
>

Except that you can't insert before "A" :)
With numbers you can go negative.
Of course you could disallow "A" as the key, start at "B" and then to sort
before use "AN".

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[jose isaias cabrera]

I have been having the same problem for a while.  But, this is using Windows 
Live Mail client.  I would love to keep the list email driven, if possible. 
But, whatever it is, I will be part of the next phase of communication. 
Thanks.



-Original Message- 
From: Keith Medcalf

Sent: Tuesday, November 21, 2017 10:59 AM
To: SQLite mailing list
Subject: Re: [sqlite] Many ML emails going to GMail's SPAM


In my opinion it is the beginning of the end of crappy freemail providers 
and their overzealous spam filtering.  And it is about time.


If you run an RFC complaint MTA then there is really very little problem 
with SPAM at all -- I have many connections per second rejected for RFC 
non-compliance -- and get maybe 3 SPAM messages per day, all of which 
originate from the crappy Johhny-cum-lately freemail systems.  It is just 
that the Johhny-cum-lately's (freemail, telco's, cableco's) have no idea how 
to run RFC compliant Internet Hosts and MTA's that have issues.  Plus those 
that insist on being RFC non-compliant so they can communicate with the 
Johhny-cum-lately non-RFC compliant hosts and MTA's.


A far better approach is to remain RFC compliant and if someone you want to 
communicate with insists on not being an RFC compliant Internet host and 
MTA, then tell them to bugger off and use old fashioned snail-mail that does 
not require a properly configured host connected to the Internet.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




-Original Message-
From: sqlite-users [mailto:sqlite-users-
boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
Sent: Tuesday, 21 November, 2017 07:31
To: SQLite mailing list
Subject: Re: [sqlite] Many ML emails going to GMail's SPAM

On 11/21/17, Paul Sanderson  wrote:

Coincidence!  I have just been in my gmail folder marking a load of

SQLite

email as 'not spam'


I've been seeing mailing list emails go to spam for a while now.
Nothing has changed with MailMan.  I think what we are seeing is the
beginning of the end of email as a viable communication medium.

I really need to come up with an alternative to the mailing list.
Perhaps some kind of forum system.  Suggestions are welcomed.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users




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


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

Re: [sqlite] how into insert row into middle of table with integer primary key

[Olaf Schmidt]

Am 22.11.2017 um 01:29 schrieb Jens Alfke:


When I’ve run into this before, the requirement has been to support lists with 
customizable ordering, like an outliner where the user can freely drag the rows 
up and down.


Yep.
And therefore such cases should be handled at the App-Level IMO...

There's a lot of ways to approach that - one that comes to mind
(since JSON is in the meantime standard in App-development),
is to store such "orderable Groups" in their own JSON-Blob-DBFields
(as simple Text - serialized into JSON-Array-format for example).

E.g. when we assume that any given "fruit-salad" is stored as
a single record (a single Blob) in a table "recipes", then
this could look like the following VB-Code...

(which interested users could paste e.g. into an Excel-VBA-Module,
after installing and referencing the vbRichClient5-COM-wrapper
for SQLite):

Private Cnn As cMemDB, SQL As String

Sub Main()
  Set Cnn = New_c.MemDB 'create an SQLite InMemory-DB-Instance
  Cnn.Exec "Create Table Recipes(ID Integer Primary Key, R Text)"

  InsertNewRecipe MakeRecipe("apple", "pear", "kiwi") 'insert 1st record

  Dim R As cCollection  'at App-Level, a Recipe is a Collection
  Set R = GetRecipeByID(1)  'retr. the above inserted Record by ID
  R.Add "banana", Before:=1 'add banana before Index 1 (pear)
  UpdateRecipe 1, R 'write the new content of R back into the DB (ID 1)

  'check, whether the DB-update was successful, retr. a Collection by ID
  Debug.Print GetRecipeByID(1).SerializeToJSONString

  'search-queries against the JSON-content are possible per Like...
  SQL = "Select R From Recipes Where R Like '%banana%'"
  Debug.Print Cnn.GetRs(SQL)(0)

  'or when the SQLite-JSONExtension is available, it will allow
  'to query the contents of JSON-fields more specifically...
  SQL = "Select R From Recipes Where json_extract(R,'$[1]')='banana'"
  Debug.Print Cnn.GetRs(SQL)(0)
End Sub

The above prints out (the same thing from all 3 Debug-Statements):
["apple","banana","pear","kiwi"]
["apple","banana","pear","kiwi"]
["apple","banana","pear","kiwi"]

The critical line in the above main-code (which makes handling
the issue per SQL obsolete) is: -> R.Add "banana", Before:=1
(most Array-, List- or Collection-Objects allow such Inserts inbetween,
 no matter which programming-language).


'-- the needed Helper-Functions for the above Main-Routine --
Function MakeRecipe(ParamArray PA()) As cCollection
  'returntype of a new Recipe is a JSON-Array-(in a cCollection)
  Set MakeRecipe = New_c.JSONArray
  Dim P: For Each P In PA: MakeRecipe.Add P: Next 'copy-over-loop
End Function

Sub InsertNewRecipe(R As cCollection)
  Cnn.ExecCmd "Insert Into Recipes(R) Values(?)", _
   R.SerializeToJSONString
End Sub

Function GetRecipeByID(ByVal ID As Long) As cCollection
  Dim sJSON As String 'first retrieve the JSON-String by ID
  sJSON = Cnn.GetSingleVal("Select R From Recipes Where ID=" & ID)
  'deserialize sJSON into a cCollection
  Set GetRecipeByID = New_c.JSONDecodeToCollection(sJSON)
End Function

Sub UpdateRecipe(ByVal ID As Long, R As cCollection)
  Cnn.ExecCmd "Update Recipes Set R=? Where ID=?",_
   R.SerializeToJSONString, ID
End Sub


Olaf

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

Re: [sqlite] how into insert row into middle of table with integer primary key

[Jens Alfke]

> On Nov 21, 2017, at 2:48 PM, Simon Slavin  wrote:
> 
> But that just brings us back to the question of why OP wants to store ID 
> numbers which might change.

When I’ve run into this before, the requirement has been to support lists with 
customizable ordering, like an outliner where the user can freely drag the rows 
up and down.

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[jonathon]

On 11/21/2017 08:20 PM, Keith Medcalf wrote:

> Strict RFC compliance is very simple:

Which explains why virtually every email client on mobile devices is
unable to send email that complies with the relevant RFCs.

Using  RFC-compliance as a spam detection tool is useful, because it
eliminates 100% of the spam out there.
Unfortunately, it also eliminates at least 70% of the legitimate email
out there.


jonathon



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[jungle Boogie]

On 21 November 2017 at 11:42, Warren Young  wrote:
> On Nov 21, 2017, at 10:24 AM, Peter Da Silva  
> wrote:
>>
>> But the mailers I use (Gmail’s web interface, Apple Mail and (yuck) Outlook) 
>> all do basic threading.
>
> I’d describe what Apple Mail and Gmail do as “clumping” rather than 
> “threading.”
>
> I think we can all agree that drh gets trees, so if he wants to make a 
> threaded web forum, he certainly needs no advice from us on how to achieve it.
>
> The effort to implement Hacker News can’t have been all that great.  It would 
> suffice for our purposes.  Do it atop Fossil and you get user authentication 
> for free, which reduces spam.  When (!) spam gets through, it can be shunned 
> using the normal Fossil mechanism, so that later clones don’t contain it.
>

An alternative to HN with similar layout:
https://lobste.rs/
https://github.com/lobsters/lobsters
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[jungle Boogie]

On 21 November 2017 at 06:30, Richard Hipp  wrote:
> On 11/21/17, Paul Sanderson  wrote:
>> Coincidence!  I have just been in my gmail folder marking a load of SQLite
>> email as 'not spam'
>
> I've been seeing mailing list emails go to spam for a while now.
> Nothing has changed with MailMan.  I think what we are seeing is the
> beginning of the end of email as a viable communication medium.
>
> I really need to come up with an alternative to the mailing list.
> Perhaps some kind of forum system.  Suggestions are welcomed.

I'm in the keep the email list, but get it setup correctly as per
Keith's recommendations.

Also, there's #sqlite on irc.freenode.net

Let's get on irc (because we all have an irc client already, right)
and chat. No need for Richard to be distracted by setting up, managing
and running forums and Discord.

> --
> D. Richard Hipp
> d...@sqlite.org

-- 
---
inum: 883510009027723
sip: jungleboo...@sip2sip.info
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[sub sk79]

Sqlite had a forum in the past on Nabble. Seems nice to me, I still get
several hits to nabble when googling for sqlite issues.
What didn't work there?

-SK

On Tue, Nov 21, 2017 at 9:30 AM, Richard Hipp  wrote:

> On 11/21/17, Paul Sanderson  wrote:
> > Coincidence!  I have just been in my gmail folder marking a load of
> SQLite
> > email as 'not spam'
>
> I've been seeing mailing list emails go to spam for a while now.
> Nothing has changed with MailMan.  I think what we are seeing is the
> beginning of the end of email as a viable communication medium.
>
> I really need to come up with an alternative to the mailing list.
> Perhaps some kind of forum system.  Suggestions are welcomed.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how into insert row into middle of table with integer primary key

[Igor Korot]

Simon,

On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin  wrote:
>
>
> On 21 Nov 2017, at 10:09pm, Jens Alfke  wrote:
>
>>> On Nov 21, 2017, at 1:56 AM, R Smith  wrote:
>>>
>>> That assumes you are not starting from an integer part (like 4000) and 
>>> hitting the exact same relative insert spot every time, which /can/ happen, 
>>> but is hugely unlikely.
>>
>> Not to beat this into the ground, but: it’s not that unlikely. Let’s say you 
>> sort rows by date. You’ve already got some entries from 2015 in your 
>> database, and some from 2017. Someone now inserts 60 entries from 2016, and 
>> to be ‘helpful’, they insert them in chronological order. Wham, this 
>> immediately hits that case.
>
> Yes, if you use this method, you do need to renumber them every so often.  
> You assess this when you’re working out (before + after) / 2, and you do it 
> using something like the double-UPDATE command someone came up with earlier.
>
> But that just brings us back to the question of why OP wants to store ID 
> numbers which might change.

Homework exercise?
Stupid requirements?

Thank you.

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

Re: [sqlite] Callback for sqlite3_finalize()

[Stadin, Benjamin]

That should read sqlite3_create_function_v2


Am 22.11.17, 00:03 schrieb "sqlite-users im Auftrag von Stadin, Benjamin" 
:

dms_create_function_v2

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

[sqlite] Callback for sqlite3_finalize()

[Stadin, Benjamin]

Hi,

I register a custom SQL function using dms_create_function_v2, and in the C 
callback I create a rather heavy C++ helper class which I need to prepare the 
result. 

I currently use sqlite3_get_auxdata and sqlite3_set_auxdata, but my problem is 
that the finalization callback  of sqlite3_set_auxdata is called after each 
step(). 

Is there a way to hook into sqlite3_finalize(), in order to manage the 
lifecycle of application data that is created together with a statement and to 
be destroyed once the statement is finalized?

Ben

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Warren Young]

On Nov 21, 2017, at 1:20 PM, Keith Medcalf  wrote:
> 
> Strict RFC compliance is very simple:

We await your patch, then. :)

> And checking SPF is pretty useful as well.

But not DKIM, which solves the same problem as SPF, but with strong crypto so 
it can’t be MITM’d?

DKIM effectively signs email from a given server.  It doesn’t tell you that a 
particular person sent it, but it does unambiguously prove that a given 
*server* sent it, assuming the server doesn’t lose control of its private key.

Some receivers may require only SPF, and some may require only DKIM, so if you 
don’t support both, you cut those receivers off.

Some receivers may support both but weigh a correct response to one higher than 
the other, so if you only support the lesser-ranked one, your messages are more 
likely to be seen as spam, which gets you right back onto the boat aboard which 
we sailed off into this thread.

> DMARC generally causes more trouble than it solves

That may well be, but some receivers may require it.  If this proposed 
MTA/forum/mailing list doesn’t support it, those sites will be cut off.

Which sites?  Let’s start with the US federal government:

https://goo.gl/F7ahDg

…which employs a large fraction of the US workforce:

   https://goo.gl/nXDCc4

…all of which we’re willing to cut off from discussing SQLite and Fossil?

> And this is all without blacklists or other questionable whack-job filtering …

You’re only considering the inbound SMTP case, I think.  This MTA must also 
talk to all the other existing SMTP servers, since to a first approximation, 
all SMTP servers have a SQLite or Fossil user behind them.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how into insert row into middle of table with integer primary key

[Simon Slavin]

On 21 Nov 2017, at 10:09pm, Jens Alfke  wrote:

>> On Nov 21, 2017, at 1:56 AM, R Smith  wrote:
>> 
>> That assumes you are not starting from an integer part (like 4000) and 
>> hitting the exact same relative insert spot every time, which /can/ happen, 
>> but is hugely unlikely.
> 
> Not to beat this into the ground, but: it’s not that unlikely. Let’s say you 
> sort rows by date. You’ve already got some entries from 2015 in your 
> database, and some from 2017. Someone now inserts 60 entries from 2016, and 
> to be ‘helpful’, they insert them in chronological order. Wham, this 
> immediately hits that case.

Yes, if you use this method, you do need to renumber them every so often.  You 
assess this when you’re working out (before + after) / 2, and you do it using 
something like the double-UPDATE command someone came up with earlier.

But that just brings us back to the question of why OP wants to store ID 
numbers which might change.

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

Re: [sqlite] Energy consumption of SQLite queries

[Roman Fleysher]

Dear Ali,

A couple of comments. Indeed lots of energy is transferred into heat, but not 
all. Therefore, using temperature (after calibrating specific heat coefficient 
of the device ) is not a good method. Some energy is radiated as visible and 
invisible light and hard to catch it all. Some as vibration. Some energy is 
used to flip the bits on the disk. So to speak internal energy. 

Thus, monitoring input power is the only way. However, since many jobs are 
running, the results will be indeed inconsistent. Some people, therefore, 
erroneously conclude that the question is not answerable. This is not true.

A properly crafted research proposal could get funding needed to accomplish 
this fine goal. I expect 1 million US dollars for 5 years should be close to 
sufficient. Make sure you measure how long SQLIte performs the task, record 
power consumption in that period. Then run machine for the same period without 
SQLIte. Difference in power consumption is what was due to  SQLite, controlling 
for the other processes. Obviously, caching and other things already mentioned, 
will affect the numbers. Thus, you need to properly randomize these trials, 
playing with their durations. You will have to perform many of these (therefore 
5 year long project) to average out all fluctuations. 

Given complexity of the project, you should consider getting initial funding to 
design it in the first place and obtain preliminary data (and necessary 
equipment) to justify and ensure future success. It appears, given your initial 
email, that such funding is well underway towards being secured. Be sure to 
control temperature and humanity in the room, because cooling fans also consume 
energy, which depends on their speed and viscosity of the air. The tidal forces 
(of the moon) will affect friction in bearings of all moving parts (fans, 
disks). Be sure to either co-vary for them or randomize experiments for 
different phases of the moon. DO NOT MOVE computer while experiment is running. 
Coriolis force will affect friction in all rotating parts as well.

In summary, this is a perfectly doable experiment, if carefully planned and 
executed. Radio astronomy easily reaches sensitivities of 10^{-9}. You can do 
it too!

At conclusion of the 5 year research period, SQLite will be much different from 
what it is today. So will kernels of operating systems, hardware etc. 
Therefore, at conclusion of the research, you will have answered how much power 
was consumed by SQLite 5 years ago. I am sure this will be very valuable piece 
of information then, after all the money and efforts are spent. Because of this 
short delay (5 years is short on the astronomical time scale) and because of 
the experience you gained by conclusion of the project, I am rather certain you 
will be able to obtain additional funding to continue and refine the answer to 
the newer version of SQLite available then. The future is in your hands!


Roman



From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Ali Dorri [alidorri...@gmail.com]
Sent: Tuesday, November 21, 2017 4:49 PM
To: Robert Oeffner
Cc: SQLite mailing list
Subject: Re: [sqlite] Energy consumption of SQLite queries

Dear All,

Thanks for your comments. That was really helpful.

Regards
Ali

On Tue, Nov 21, 2017 at 11:41 PM, Robert Oeffner  wrote:

> This is an interesting topic more belonging to the realms of information
> theory and statistical physics.
>
> I am not an expert in this area but from what I recall from undergraduate
> physics the moment you create order in one corner of the universe entropy
> rises in another place of the universe. If you loosely speaking equate
> information gathering such as an SQL query as creating order then that must
> have a cost in terms of increasing the entropy (heat in this case)
> elsewhere. There is a lower bound on how little entropy is generated during
> this process which comes down to the efficiency of the process (hardware
> and software in your case).
>
> One could get philosophical here and question whether mankinds computer
> modeling of climate change in itself causes the excess heat leading to
> global warming.
>
>
> Regards,
>
> Robert
>
>
> --
> Robert Oeffner, Ph.D.
> Research Associate,
> The Read Group, Department of Haematology,
> Cambridge Institute for Medical Research
> University of Cambridge
> Cambridge Biomedical Campus
> Wellcome Trust/MRC Building
> Hills Road
> Cambridge CB2 0XY
> www.cimr.cam.ac.uk/investigators/read/index.html
>
>
>
> Date: Tue, 21 Nov 2017 09:54:25 +1100
>> From: Ali Dorri 
>> To: SQLite mailing list 
>> Subject: [sqlite] Energy consumption of SQLite queries
>> Message-ID:
>> 

Re: [sqlite] how into insert row into middle of table with integer primary key

[Jens Alfke]

> On Nov 21, 2017, at 1:56 AM, R Smith  wrote:
> 
> That assumes you are not starting from an integer part (like 4000) and 
> hitting the exact same relative insert spot every time, which /can/ happen, 
> but is hugely unlikely.

Not to beat this into the ground, but: it’s not that unlikely. Let’s say you 
sort rows by date. You’ve already got some entries from 2015 in your database, 
and some from 2017. Someone now inserts 60 entries from 2016, and to be 
‘helpful’, they insert them in chronological order. Wham, this immediately hits 
that case.

(This is similar to the problem that some tree data structures have, where 
adding entries in sorted order results in the must unbalanced possible tree.)

It’s a lot better to use strings, and just increase the length of the string as 
necessary. So to insert in between “A” and “C” you add “B”, then to insert 
between “A” and “B” you add “AM”, etc.

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Gary R. Schmidt]

On 22/11/2017 01:30, Richard Hipp wrote:

On 11/21/17, Paul Sanderson  wrote:

Coincidence!  I have just been in my gmail folder marking a load of SQLite
email as 'not spam'


I've been seeing mailing list emails go to spam for a while now.
Nothing has changed with MailMan.  I think what we are seeing is the
beginning of the end of email as a viable communication medium.

I really need to come up with an alternative to the mailing list.
Perhaps some kind of forum system.  Suggestions are welcomed.

There is nothing wrong with email - but there is an awful lot wrong with 
gnail and Google's ideas on how email is done.  (Not to mention Yahoo, 
but it seems that MS have the sense to leave the underpinnings of 
hotmail as they were.)


To put it simply - friends don't let friends use gmail.

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

Re: [sqlite] Energy consumption of SQLite queries

[Ali Dorri]

Dear All,

Thanks for your comments. That was really helpful.

Regards
Ali

On Tue, Nov 21, 2017 at 11:41 PM, Robert Oeffner  wrote:

> This is an interesting topic more belonging to the realms of information
> theory and statistical physics.
>
> I am not an expert in this area but from what I recall from undergraduate
> physics the moment you create order in one corner of the universe entropy
> rises in another place of the universe. If you loosely speaking equate
> information gathering such as an SQL query as creating order then that must
> have a cost in terms of increasing the entropy (heat in this case)
> elsewhere. There is a lower bound on how little entropy is generated during
> this process which comes down to the efficiency of the process (hardware
> and software in your case).
>
> One could get philosophical here and question whether mankinds computer
> modeling of climate change in itself causes the excess heat leading to
> global warming.
>
>
> Regards,
>
> Robert
>
>
> --
> Robert Oeffner, Ph.D.
> Research Associate,
> The Read Group, Department of Haematology,
> Cambridge Institute for Medical Research
> University of Cambridge
> Cambridge Biomedical Campus
> Wellcome Trust/MRC Building
> Hills Road
> Cambridge CB2 0XY
> www.cimr.cam.ac.uk/investigators/read/index.html
>
>
>
> Date: Tue, 21 Nov 2017 09:54:25 +1100
>> From: Ali Dorri 
>> To: SQLite mailing list 
>> Subject: [sqlite] Energy consumption of SQLite queries
>> Message-ID:
>> 

Re: [sqlite] Many ML emails going to GMail's SPAM

[Petite Abeille]

> On Nov 21, 2017, at 6:23 PM, Warren Young  wrote:
> 
> This is what drh does.  We’re fans because he does it well.

drh + djb = bliss?

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Petite Abeille]

> On Nov 21, 2017, at 3:30 PM, Richard Hipp  wrote:
> 
> I think what we are seeing is the beginning of the end of email as a viable 
> communication medium.

Nonsense. Email is one of these cockroach technologies: it will survive us all. 

> I really need to come up with an alternative to the mailing list.

No you don’t. You just want to.

> Perhaps some kind of forum system.  Suggestions are welcomed.

Stay put. Relax. It will pass. 

There are more productive way to spend your time: what about adding analytic 
functions, and MERGE to SQLite for one.

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Keith Medcalf]

And checking SPF is pretty useful as well.  Once you have enforced strict 
compliance, however, the effect of SPF is negligible (less than 1/1000%).  

DKIM/DMARC generally causes more trouble than it solves (it was designed by a 
committee of idiots after all) and should be mostly ignored other than for 
displaying a DKIM Signature Status in the mail reader interface.

Most of the problem is the horribly broken e-mail clients, none of which 
display useful information.  For those old enough to remember postal mail, it 
is like having a secretary that throws out the envelope and trims off most of 
the inside and signature information before giving you your mail.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>Strict RFC compliance is very simple:
>
>(1) When a remote MTA connects it MUST NOT speak until spoken to.
>(2) A remote MTA MUST NOT violate the command/response protocol.
>(3) The IP Address of the remote host MUST resolve (in the in-
>addr.arpa domain) to a name that forward resolves to a set of IP
>Addresses that includes the originating address.
>(4) The name given by a remote host in its HELO or EHLO, if not an IP
>Address, must be resolvable to an IP Address.
>(5) The domain name given in the envelope-from must be resolvable to
>an IP Address.
>
>Optional:
>
>(6) The IP Address determined by step 4 must accept SMTP connections.
>(7) The IP Address determined by step 5 must accept SMTP connections.
>(8) The MTA in step 7 must accept an envelope specifying envelope-to
>the original envelope sender with an empty envelope-from
>
>Enforcing compliance with (1) eliminates >70% of all spam.
>Enforcing compliance with (2) eliminates an additional 10% of all
>spam.
>Enforcing compliance with (3) eliminates an additional 10% of all
>spam.
>Enforcing compliance with (4) and (5) eliminates almost another 10%
>of spam.
>Enforcing (6), (7), and (8) (that is, requiring full RFC compliance)
>eliminates 99.99% of spam.
>
>If you also can enforce the dropping of direct-to-mx connections
>(that is, connections to higher numbered MX's should be rejected if a
>lower number MX MTA is availkable), then you can increase the spam
>rejection to about 99.999%
>
>And this is all without blacklists or other questionable whack-job
>filtering ...
>
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Warren Young
>>Sent: Tuesday, 21 November, 2017 12:43
>>To: SQLite mailing list
>>Subject: Re: [sqlite] Many ML emails going to GMail's SPAM
>>
>>On Nov 21, 2017, at 10:24 AM, Peter Da Silva
>> wrote:
>>>
>>> But the mailers I use (Gmail’s web interface, Apple Mail and
>(yuck)
>>Outlook) all do basic threading.
>>
>>I’d describe what Apple Mail and Gmail do as “clumping” rather than
>>“threading.”
>>
>>I think we can all agree that drh gets trees, so if he wants to make
>>a threaded web forum, he certainly needs no advice from us on how to
>>achieve it.
>>
>>The effort to implement Hacker News can’t have been all that great.
>>It would suffice for our purposes.  Do it atop Fossil and you get
>>user authentication for free, which reduces spam.  When (!) spam
>gets
>>through, it can be shunned using the normal Fossil mechanism, so
>that
>>later clones don’t contain it.
>>
>>As far as I can tell, the only really hard part is the email
>>gatewaying problem, evidenced by the fact that Fossil still doesn’t
>>have a feature to echo commits, ticket changes, etc. via email.
>>
>>The comment up-thread about RFC-complaint email handwaves the
>>complexity of achieving that in 2017, even when using existing
>tools,
>>which is not a given where drh is concerned.
>>
>>If you start with Postfix’s RFC list:
>>
>>   http://www.postfix.org/smtpd.8.html
>>
>>then chase all the “obsoleted by” and “updated by” links from those
>>RFCs and add in completely missing RFCs that are also requirements
>in
>>2017, you get this list, which is probably also incomplete, because
>I
>>am no expert on MTA implementation:
>>
>>   RFC 1123 (Host requirements)
>>   RFC 1870 (Message size declaration)
>>   RFC 1985 (ETRN command)
>>   RFC 2034 (SMTP enhanced status codes)
>>   RFC 2920 (SMTP pipelining)
>>   RFC 3207 (STARTTLS command)
>>   RFC 3461 (SMTP DSN extension)
>>   RFC 3463 (Enhanced status codes)
>>   RFC 3848 (ESMTP transmission types)
>>   RFC 3885 (SMTP Service Extension for Message Tracking)
>>   RFC 4954 (AUTH command)
>>   RFC 5321 (SMTP protocol)
>>   RFC 5322 (Internet Message Format)
>>   RFC 6152 (8bit-MIME transport)
>>   RFC 6409 (Message Submission for Mail)
>>   RFC 6531 (Internationalized SMTP)
>>   RFC 6532 (Internationalized Email Headers)
>>   RFC 6533 (Internationalized 

Re: [sqlite] Many ML emails going to GMail's SPAM

[Keith Medcalf]

Strict RFC compliance is very simple:

(1) When a remote MTA connects it MUST NOT speak until spoken to.
(2) A remote MTA MUST NOT violate the command/response protocol.
(3) The IP Address of the remote host MUST resolve (in the in-addr.arpa domain) 
to a name that forward resolves to a set of IP Addresses that includes the 
originating address.
(4) The name given by a remote host in its HELO or EHLO, if not an IP Address, 
must be resolvable to an IP Address.
(5) The domain name given in the envelope-from must be resolvable to an IP 
Address.

Optional:

(6) The IP Address determined by step 4 must accept SMTP connections.
(7) The IP Address determined by step 5 must accept SMTP connections.
(8) The MTA in step 7 must accept an envelope specifying envelope-to the 
original envelope sender with an empty envelope-from

Enforcing compliance with (1) eliminates >70% of all spam.
Enforcing compliance with (2) eliminates an additional 10% of all spam.
Enforcing compliance with (3) eliminates an additional 10% of all spam.
Enforcing compliance with (4) and (5) eliminates almost another 10% of spam.
Enforcing (6), (7), and (8) (that is, requiring full RFC compliance) eliminates 
99.99% of spam.

If you also can enforce the dropping of direct-to-mx connections (that is, 
connections to higher numbered MX's should be rejected if a lower number MX MTA 
is availkable), then you can increase the spam rejection to about 99.999%

And this is all without blacklists or other questionable whack-job filtering ...


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Warren Young
>Sent: Tuesday, 21 November, 2017 12:43
>To: SQLite mailing list
>Subject: Re: [sqlite] Many ML emails going to GMail's SPAM
>
>On Nov 21, 2017, at 10:24 AM, Peter Da Silva
> wrote:
>>
>> But the mailers I use (Gmail’s web interface, Apple Mail and (yuck)
>Outlook) all do basic threading.
>
>I’d describe what Apple Mail and Gmail do as “clumping” rather than
>“threading.”
>
>I think we can all agree that drh gets trees, so if he wants to make
>a threaded web forum, he certainly needs no advice from us on how to
>achieve it.
>
>The effort to implement Hacker News can’t have been all that great.
>It would suffice for our purposes.  Do it atop Fossil and you get
>user authentication for free, which reduces spam.  When (!) spam gets
>through, it can be shunned using the normal Fossil mechanism, so that
>later clones don’t contain it.
>
>As far as I can tell, the only really hard part is the email
>gatewaying problem, evidenced by the fact that Fossil still doesn’t
>have a feature to echo commits, ticket changes, etc. via email.
>
>The comment up-thread about RFC-complaint email handwaves the
>complexity of achieving that in 2017, even when using existing tools,
>which is not a given where drh is concerned.
>
>If you start with Postfix’s RFC list:
>
>   http://www.postfix.org/smtpd.8.html
>
>then chase all the “obsoleted by” and “updated by” links from those
>RFCs and add in completely missing RFCs that are also requirements in
>2017, you get this list, which is probably also incomplete, because I
>am no expert on MTA implementation:
>
>   RFC 1123 (Host requirements)
>   RFC 1870 (Message size declaration)
>   RFC 1985 (ETRN command)
>   RFC 2034 (SMTP enhanced status codes)
>   RFC 2920 (SMTP pipelining)
>   RFC 3207 (STARTTLS command)
>   RFC 3461 (SMTP DSN extension)
>   RFC 3463 (Enhanced status codes)
>   RFC 3848 (ESMTP transmission types)
>   RFC 3885 (SMTP Service Extension for Message Tracking)
>   RFC 4954 (AUTH command)
>   RFC 5321 (SMTP protocol)
>   RFC 5322 (Internet Message Format)
>   RFC 6152 (8bit-MIME transport)
>   RFC 6409 (Message Submission for Mail)
>   RFC 6531 (Internationalized SMTP)
>   RFC 6532 (Internationalized Email Headers)
>   RFC 6533 (Internationalized Delivery Status Notifications)
>   RFC 7489 (DMARC)
>   RFC 7504 (SMTP 521 and 556 Reply Codes)
>   RFC 7505 ("Null MX" No Service Resource Record)
>   RFC 7817 (STARTTLS updates)
>   RFC 8098 (Message Disposition Notification)
>
>Those 23 standards print as 579 pages.  Yes, that’s right, someone
>“just” has to implement 579 pages of standardese, which gets you only
>SMTP, which we’d better hope is enough since IMAPv4 + POPv3 probably
>doubles that again.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Many ML emails going to GMail's SPAM

[sub sk79]

I vote to keep mailing list. It works great for me with GMail (accessible
from all my devices instantly)
Reasons:
1. GMail has a threaded view built-in which works great. You might need to
enable it in settings.
2. I doubt if any spam filter can ever be better than Gmail's. Spam
fighting is not just a matter of having Bayesian algos right -- it needs
mountains of data to work right. Who can beat Google in that?
3. Searching fast and accurate is crucial to my use of SQLite mailing list.
Again Google can't be beat.
4. If at all needed, maybe this offering  from Google is the way to go:
https://support.google.com/a/answer/167430?hl=en (I have no experience
using it, though)

Thanks,
SK



On Tue, Nov 21, 2017 at 1:53 PM, Mike King  wrote:

>
> Another vote for a threaded forum here. I do try and keep up with the ML
> but if it was threaded it would be a lot easier.
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Petite Abeille]

> On Nov 21, 2017, at 8:42 PM, Warren Young  wrote:
> 
> As far as I can tell, the only really hard part is the email gatewaying 
> problem, evidenced by the fact that Fossil still doesn’t have a feature to 
> echo commits, ticket changes, etc. via email.

“Every program attempts to expand until it can read mail. Those programs which 
cannot so expand are replaced by ones which can.” 

— Zawinski's Law


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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Warren Young]

On Nov 21, 2017, at 10:24 AM, Peter Da Silva  
wrote:
> 
> But the mailers I use (Gmail’s web interface, Apple Mail and (yuck) Outlook) 
> all do basic threading.

I’d describe what Apple Mail and Gmail do as “clumping” rather than “threading.”

I think we can all agree that drh gets trees, so if he wants to make a threaded 
web forum, he certainly needs no advice from us on how to achieve it.

The effort to implement Hacker News can’t have been all that great.  It would 
suffice for our purposes.  Do it atop Fossil and you get user authentication 
for free, which reduces spam.  When (!) spam gets through, it can be shunned 
using the normal Fossil mechanism, so that later clones don’t contain it.

As far as I can tell, the only really hard part is the email gatewaying 
problem, evidenced by the fact that Fossil still doesn’t have a feature to echo 
commits, ticket changes, etc. via email.

The comment up-thread about RFC-complaint email handwaves the complexity of 
achieving that in 2017, even when using existing tools, which is not a given 
where drh is concerned.  

If you start with Postfix’s RFC list:

   http://www.postfix.org/smtpd.8.html

then chase all the “obsoleted by” and “updated by” links from those RFCs and 
add in completely missing RFCs that are also requirements in 2017, you get this 
list, which is probably also incomplete, because I am no expert on MTA 
implementation:

   RFC 1123 (Host requirements)
   RFC 1870 (Message size declaration)
   RFC 1985 (ETRN command)
   RFC 2034 (SMTP enhanced status codes)
   RFC 2920 (SMTP pipelining)
   RFC 3207 (STARTTLS command)
   RFC 3461 (SMTP DSN extension)
   RFC 3463 (Enhanced status codes)
   RFC 3848 (ESMTP transmission types)
   RFC 3885 (SMTP Service Extension for Message Tracking)
   RFC 4954 (AUTH command)
   RFC 5321 (SMTP protocol) 
   RFC 5322 (Internet Message Format)
   RFC 6152 (8bit-MIME transport)
   RFC 6409 (Message Submission for Mail)
   RFC 6531 (Internationalized SMTP)
   RFC 6532 (Internationalized Email Headers)
   RFC 6533 (Internationalized Delivery Status Notifications)
   RFC 7489 (DMARC)
   RFC 7504 (SMTP 521 and 556 Reply Codes)
   RFC 7505 ("Null MX" No Service Resource Record)
   RFC 7817 (STARTTLS updates)
   RFC 8098 (Message Disposition Notification)

Those 23 standards print as 579 pages.  Yes, that’s right, someone “just” has 
to implement 579 pages of standardese, which gets you only SMTP, which we’d 
better hope is enough since IMAPv4 + POPv3 probably doubles that again.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Mike King]

In the UK we’ve still got a threaded conferencing service called CIX must
be over 30 years old now (it was based on BIX / Cosy). The joke is it’s the
UKs oldest online social network :)

Another vote for a threaded forum here. I do try and keep up with the ML
but if it was threaded it would be a lot easier.

Cheers,

Mike

On Tue, 21 Nov 2017 at 17:20, Paul Sanderson 
wrote:

> What about some sort of poll.
>
> Mail lists might work but the additonal functionality offered by a forum (I
> am a member of many) makes them my choice.
>
> 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 21 November 2017 at 16:43, Martin Raiber  wrote:
>
> > On 21.11.2017 17:30 John McKown wrote:
> > > On Tue, Nov 21, 2017 at 10:27 AM, Drago, William @ CSG - NARDA-MITEQ <
> > > william.dr...@l3t.com> wrote:
> > >
> > >>> I really need to come up with an alternative to the mailing list.
> > >>> Perhaps some kind of forum system.  Suggestions are welcomed.
> > >>> --
> > >>> D. Richard Hipp
> > >>> d...@sqlite.org
> > >> Please, not a forum. The email list is instant, dynamic, and
> > convenient. I
> > >> don't think checking into a forum to stay current with the brisk
> > activity
> > >> here is very practical or appealing.
> > > ​I completely agree. The problem with a forum is mainly that it is not
> > _a_
> > > forum. It is a forum per list. Which means I spend way too much time
> > > "polling" 8 to 10 web "forums" during the day just to see if anybody
> has
> > > said anything of interest.
> >
> > I am using Discourse as community forum and I cannot really see any
> > downside to that except for the increased server requirements.
> > Individuals who want to use it like a mailing list still can do that
> > (enable mailing list mode). They have a FAQ wrt. to cos/prons mailing
> > list:
> https://meta.discourse.org/t/discourse-vs-email-mailing-lists/54298
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[David Raymond]

Haven't you read the FAQ? http://www.sqlite.org/faq.html#q6
"Threads are evil. Avoid them."


(Sorry, couldn't resist)


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Peter Da Silva
Sent: Tuesday, November 21, 2017 12:24 PM
To: SQLite mailing list
Subject: Re: [sqlite] Many ML emails going to GMail's SPAM

On 11/21/17, 11:21 AM, "sqlite-users on behalf of Warren Young" 
 
wrote:
> You don’t get proper threading with the current ticket comment system, but 
> both mailers I use these days lack that feature, as do most forum systems.  I 
> miss threading, but clearly I can live without it.

If by “proper threading” you mean trn-style, no, nothing but Usenet has ever 
gotten that right.

But the mailers I use (Gmail’s web interface, Apple Mail and (yuck) Outlook) 
all do basic threading.

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Darren Duncan]

On 2017-11-21 6:30 AM, Richard Hipp wrote:

On 11/21/17, Paul Sanderson  wrote:

Coincidence!  I have just been in my gmail folder marking a load of SQLite
email as 'not spam'


I've been seeing mailing list emails go to spam for a while now.
Nothing has changed with MailMan.  I think what we are seeing is the
beginning of the end of email as a viable communication medium.

I really need to come up with an alternative to the mailing list.
Perhaps some kind of forum system.  Suggestions are welcomed.


If you do go the forum route, please choose one with an email interface so that 
one can choose to receive an email for each forum message (it doesn't have to 
support posting via email though), and download archives of messages, so we 
don't go backwards. -- Darren Duncan


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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Stephen Chrzanowski]

IMO, the additional functionality provided doesn't outweigh yet another
niche and individual forum I need to log into that can potentially end up
being hacked.  Its another thing Richard (et all) has to maintain and
update.

On Tue, Nov 21, 2017 at 12:20 PM, Paul Sanderson <
sandersonforens...@gmail.com> wrote:

> What about some sort of poll.
>
> Mail lists might work but the additonal functionality offered by a forum (I
> am a member of many) makes them my choice.
>
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Peter Da Silva]

On 11/21/17, 11:21 AM, "sqlite-users on behalf of Warren Young" 
 
wrote:
> You don’t get proper threading with the current ticket comment system, but 
> both mailers I use these days lack that feature, as do most forum systems.  I 
> miss threading, but clearly I can live without it.

If by “proper threading” you mean trn-style, no, nothing but Usenet has ever 
gotten that right.

But the mailers I use (Gmail’s web interface, Apple Mail and (yuck) Outlook) 
all do basic threading.

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Warren Young]

On Nov 21, 2017, at 7:52 AM, Dominique Devienne  wrote:
> 
> On Tue, Nov 21, 2017 at 3:30 PM, Richard Hipp  wrote:
> 
>> I really need to come up with an alternative to the mailing list. Perhaps 
>> some kind of forum system.  Suggestions are welcomed.
> 
> After re-inventing database and source-control, forum software next? :)

Also parser generators:

   http://www.hwaci.com/sw/lemon/

This is what drh does.  We’re fans because he does it well.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Warren Young]

On Nov 21, 2017, at 7:30 AM, Richard Hipp  wrote:
> 
> On 11/21/17, Paul Sanderson  wrote:
>> Coincidence!  I have just been in my gmail folder marking a load of SQLite
>> email as 'not spam'
> 
> I've been seeing mailing list emails go to spam for a while now.
> Nothing has changed with MailMan.  I think what we are seeing is the
> beginning of the end of email as a viable communication medium.
> 
> I really need to come up with an alternative to the mailing list.
> Perhaps some kind of forum system.  Suggestions are welcomed.

I thought the idea that came up last time this subject came up was pretty good: 
rework Fossil's ticketing system into a web forum.  Then everyone who clones 
the repository also has a forum archive.  

Import the current mail archive, and now everyone who clones gets a clean 
FTSable copy of the old ML archives going back years and years.

I don’t mean that both purposes are served by the same code, I mean that the 
ticketing system is already pretty far down the road toward a web forum.  If 
they do end up sharing code, tickets would be a subset of the web forum, not 
the other way around.

You don’t get proper threading with the current ticket comment system, but both 
mailers I use these days lack that feature, as do most forum systems.  I miss 
threading, but clearly I can live without it.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Paul Sanderson]

What about some sort of poll.

Mail lists might work but the additonal functionality offered by a forum (I
am a member of many) makes them my choice.

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 21 November 2017 at 16:43, Martin Raiber  wrote:

> On 21.11.2017 17:30 John McKown wrote:
> > On Tue, Nov 21, 2017 at 10:27 AM, Drago, William @ CSG - NARDA-MITEQ <
> > william.dr...@l3t.com> wrote:
> >
> >>> I really need to come up with an alternative to the mailing list.
> >>> Perhaps some kind of forum system.  Suggestions are welcomed.
> >>> --
> >>> D. Richard Hipp
> >>> d...@sqlite.org
> >> Please, not a forum. The email list is instant, dynamic, and
> convenient. I
> >> don't think checking into a forum to stay current with the brisk
> activity
> >> here is very practical or appealing.
> > ​I completely agree. The problem with a forum is mainly that it is not
> _a_
> > forum. It is a forum per list. Which means I spend way too much time
> > "polling" 8 to 10 web "forums" during the day just to see if anybody has
> > said anything of interest.
>
> I am using Discourse as community forum and I cannot really see any
> downside to that except for the increased server requirements.
> Individuals who want to use it like a mailing list still can do that
> (enable mailing list mode). They have a FAQ wrt. to cos/prons mailing
> list: https://meta.discourse.org/t/discourse-vs-email-mailing-lists/54298
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Peter Da Silva]

I’m a mailing list fan, too. Reddit I use for yucks only.

On 11/21/17, 10:48 AM, "sqlite-users on behalf of Stephen Chrzanowski" 
 
wrote:

All in all, just please, oh PLEASE stay away from redit  I will not
ever go there.  I got BANNED for a first post question that included an
example to clarify what I was looking for.  Nothing nasty, nothing
ignorant, it was a technical question about something or other, and out to
the curb I went.
 

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Stephen Chrzanowski]

All in all, just please, oh PLEASE stay away from redit  I will not
ever go there.  I got BANNED for a first post question that included an
example to clarify what I was looking for.  Nothing nasty, nothing
ignorant, it was a technical question about something or other, and out to
the curb I went.

On Tue, Nov 21, 2017 at 11:43 AM, Martin Raiber  wrote:

> On 21.11.2017 17:30 John McKown wrote:
> > On Tue, Nov 21, 2017 at 10:27 AM, Drago, William @ CSG - NARDA-MITEQ <
> > william.dr...@l3t.com> wrote:
> >
> >>> I really need to come up with an alternative to the mailing list.
> >>> Perhaps some kind of forum system.  Suggestions are welcomed.
> >>> --
> >>> D. Richard Hipp
> >>> d...@sqlite.org
> >> Please, not a forum. The email list is instant, dynamic, and
> convenient. I
> >> don't think checking into a forum to stay current with the brisk
> activity
> >> here is very practical or appealing.
> > ​I completely agree. The problem with a forum is mainly that it is not
> _a_
> > forum. It is a forum per list. Which means I spend way too much time
> > "polling" 8 to 10 web "forums" during the day just to see if anybody has
> > said anything of interest.
>
> I am using Discourse as community forum and I cannot really see any
> downside to that except for the increased server requirements.
> Individuals who want to use it like a mailing list still can do that
> (enable mailing list mode). They have a FAQ wrt. to cos/prons mailing
> list: https://meta.discourse.org/t/discourse-vs-email-mailing-lists/54298
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Martin Raiber]

On 21.11.2017 17:30 John McKown wrote:
> On Tue, Nov 21, 2017 at 10:27 AM, Drago, William @ CSG - NARDA-MITEQ <
> william.dr...@l3t.com> wrote:
>
>>> I really need to come up with an alternative to the mailing list.
>>> Perhaps some kind of forum system.  Suggestions are welcomed.
>>> --
>>> D. Richard Hipp
>>> d...@sqlite.org
>> Please, not a forum. The email list is instant, dynamic, and convenient. I
>> don't think checking into a forum to stay current with the brisk activity
>> here is very practical or appealing.
> ​I completely agree. The problem with a forum is mainly that it is not _a_
> forum. It is a forum per list. Which means I spend way too much time
> "polling" 8 to 10 web "forums" during the day just to see if anybody has
> said anything of interest.

I am using Discourse as community forum and I cannot really see any
downside to that except for the increased server requirements.
Individuals who want to use it like a mailing list still can do that
(enable mailing list mode). They have a FAQ wrt. to cos/prons mailing
list: https://meta.discourse.org/t/discourse-vs-email-mailing-lists/54298

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[R Smith]

On 2017/11/21 6:30 PM, Keith Medcalf wrote:

//...
I consider that there is no such thing as a "false positive".  Either the sending MTA is 
a properly configured RFC compliant Internet host with a properly configured MTA, or I do not want 
to accept communications from it.  If it is properly configured *AND* it is not on any of the 
blacklists that I use (only some of which are spam related -- as I said there is a huge cross-over 
between dirty spammers and dirty crackers) then I will accept the message.  Otherwise, not only do 
they not get to send messages, they will likely be "administratively prohibited" from 
communicating at all on any port for any reason whatsoever.  And that is THEIR problem to address, 
not mine.

Just as there is a modern propensity for the re-labelling of "impersonation" as "identity theft" in 
order to lay blame and inconvenience on the person impersonated instead of where it belongs (as a natural consequence 
of the law) on the "impersonator" and the relying party who made the "mistake", pretending that the 
recipient is somehow required to receive, read and obey whatever some idiot sends is an inversion of the natural order 
of things and is delusional (on the part of both parties).
//...


For best effect, read the above with a German accent while holding your 
right hand straight forward and pointing slightly up in front of you.



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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Peter Da Silva]

On 11/21/17, 10:30 AM, "sqlite-users on behalf of Keith Medcalf" 
 
wrote:
> I simply tell those people that they either (a) fix their systems or (b) use 
> snail-mail.  Takes care of the problem entirely.

I am absolutely not going to get into that discussion with, for one example, a 
lawyer in another country who is helping me deal with winding down my mother’s 
estate.


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

Re: [sqlite] Many ML emails going to GMail's SPAM

[John McKown]

On Tue, Nov 21, 2017 at 10:27 AM, Drago, William @ CSG - NARDA-MITEQ <
william.dr...@l3t.com> wrote:

> > I really need to come up with an alternative to the mailing list.
> > Perhaps some kind of forum system.  Suggestions are welcomed.
> > --
> > D. Richard Hipp
> > d...@sqlite.org
>
> Please, not a forum. The email list is instant, dynamic, and convenient. I
> don't think checking into a forum to stay current with the brisk activity
> here is very practical or appealing.
>

​I completely agree. The problem with a forum is mainly that it is not _a_
forum. It is a forum per list. Which means I spend way too much time
"polling" 8 to 10 web "forums" during the day just to see if anybody has
said anything of interest.


>
> --
> Bill Drago
> Staff Engineer
> L3 Narda-MITEQ
> 435 Moreland Road
> Hauppauge, NY 11788
> 631-272-5947 / william.dr...@l3t.com
>

-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Keith Medcalf]

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


On Tuesday, 21 November, 2017 09:06, Peter Da Silva 
 wrote:

>On 11/21/17, 9:59 AM, "sqlite-users on behalf of Keith Medcalf"
>kmedc...@dessus.com> wrote:

>> If you run an RFC complaint MTA then there is really very little
>problem with SPAM at all -- I have many connections per second
>rejected for RFC non-compliance -- and get maybe 3 SPAM messages per
>day, all of which originate from the crappy Johhny-cum-lately
>freemail systems

>So. taronga.com is a high profile spam target thanks to my using it
>for Usenet posts for years. Like, at one point in the ‘90s I got so
>much spam that it blew out my bandwidth limit and I got charged an
>overage, just for receiving handshakes and dropping spam on the
>ground.

Yeah, I used to be in the UUCP maps as well, way back in the olden days.

>I tried being aggressively OCD about RFC compliance and found I was
>missing mail I actually needed. Like, from lawyers and similar stuff
>that had real world consequences.

I simply tell those people that they either (a) fix their systems or (b) use 
snail-mail.  Takes care of the problem entirely.

>So I went back to using a combination of multiple layers of filters
>and a greylist front end. Oh, and blocking all of China and
>Argentina.

I use a couple of blacklists.  Oftentimes the same malefactor that happens to 
be sending spam is also running ssh probes and other miscreant malicious crap.  
Getting blacklisted by me means you are blacklisted and get dead air.  I do 
return appropriate ICMP "administratively denied" notifications, but other than 
that, they can bugger off entirely.

>Still get a lot of spam that Apple Mail’s Bayesian filter takes care
>of. Mostly.

I do not let the stuff in in the first place, so there is nothing much to 
filter.  This makes it much easier.

>Still too many false positives. I switched to gmail for mail I
>actually really needed to get. I was spending too much lifetime
>dealing with mail issues.

And that is the major difference I suppose.  I consider that there is no such 
thing as a "false positive".  Either the sending MTA is a properly configured 
RFC compliant Internet host with a properly configured MTA, or I do not want to 
accept communications from it.  If it is properly configured *AND* it is not on 
any of the blacklists that I use (only some of which are spam related -- as I 
said there is a huge cross-over between dirty spammers and dirty crackers) then 
I will accept the message.  Otherwise, not only do they not get to send 
messages, they will likely be "administratively prohibited" from communicating 
at all on any port for any reason whatsoever.  And that is THEIR problem to 
address, not mine.

Just as there is a modern propensity for the re-labelling of "impersonation" as 
"identity theft" in order to lay blame and inconvenience on the person 
impersonated instead of where it belongs (as a natural consequence of the law) 
on the "impersonator" and the relying party who made the "mistake", pretending 
that the recipient is somehow required to receive, read and obey whatever some 
idiot sends is an inversion of the natural order of things and is delusional 
(on the part of both parties).




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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Drago, William @ CSG - NARDA-MITEQ]

> I really need to come up with an alternative to the mailing list.
> Perhaps some kind of forum system.  Suggestions are welcomed.
> --
> D. Richard Hipp
> d...@sqlite.org

Please, not a forum. The email list is instant, dynamic, and convenient. I 
don't think checking into a forum to stay current with the brisk activity here 
is very practical or appealing.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l3t.com
CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of 
the intended recipient and may contain material that is proprietary, 
confidential, privileged or otherwise legally protected or restricted under 
applicable government laws. Any review, disclosure, distributing or other use 
without expressed permission of the sender is strictly prohibited. If you are 
not the intended recipient, please contact the sender and delete all copies 
without reading, printing, or saving.

Beginning April 1, 2018, L3 Technologies, Inc. will discontinue the use of all 
@L-3Com.com email addresses. To ensure delivery of your messages to this 
recipient, please update your records to use william.dr...@l3t.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Peter Da Silva]

On 11/21/17, 9:59 AM, "sqlite-users on behalf of Keith Medcalf" 
 
wrote:
> If you run an RFC complaint MTA then there is really very little problem with 
> SPAM at all -- I have many connections per second rejected for RFC 
> non-compliance -- and get maybe 3 SPAM messages per day, all of which 
> originate from the crappy Johhny-cum-lately freemail systems

So. taronga.com is a high profile spam target thanks to my using it for Usenet 
posts for years. Like, at one point in the ‘90s I got so much spam that it blew 
out my bandwidth limit and I got charged an overage, just for receiving 
handshakes and dropping spam on the ground.

I tried being aggressively OCD about RFC compliance and found I was missing 
mail I actually needed. Like, from lawyers and similar stuff that had real 
world consequences.

So I went back to using a combination of multiple layers of filters and a 
greylist front end. Oh, and blocking all of China and Argentina.

Still get a lot of spam that Apple Mail’s Bayesian filter takes care of. Mostly.

Still too many false positives. I switched to gmail for mail I actually really 
needed to get. I was spending too much lifetime dealing with mail issues.

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Pierpaolo Bernardi]

Il giorno 21 novembre 2017, alle ore 15:30, Richard Hipp  ha 
scritto:

>On 11/21/17, Paul Sanderson  wrote:
>> Coincidence!  I have just been in my gmail folder marking a load of SQLite
>> email as 'not spam'
>I've been seeing mailing list emails go to spam for a while now.
>Nothing has changed with MailMan.  I think what we are seeing is the
>beginning of the end of email as a viable communication medium.
>I really need to come up with an alternative to the mailing list.
>Perhaps some kind of forum system.  Suggestions are welcomed.

I suggest that people check their spam folder daily. It takes a few seconds to 
manually approve the few false positives.

Any web based thing otoh is a crawling horror which will destroy any usefulness 
in the whole thing. And they require an active connection which is not always 
available.

IMHO

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Keith Medcalf]

If by JS you mean JavaScript, then this is a non-starter.  Many people (myself 
included) do not permit remote code to be executed on our computers.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Wout Mertens
>Sent: Tuesday, 21 November, 2017 08:26
>To: SQLite mailing list
>Subject: Re: [sqlite] Many ML emails going to GMail's SPAM
>
>Discourse has a mailing-list mode you can enable, which will send you
>all
>posts (I presume, I never tried it)
>
>The default setup sends you interesting new topics at an interval of
>your
>choosing.
>
>What I like very much about Discourse:
>
>   - great engagement
>   - easy following of only those topics that interest you
>   - great way to have a live archive of posts
>   - no spam. The JS hoops spammers have to jump through are a great
>   deterrent so far.
>
>I must say that I can't really remember a google search resulting in
>a post
>on a Discourse forum. I wonder if it has bad googlability or if other
>sources are deemed better by Google, or if Discourse is simply not
>very
>popular.
>
>On Tue, Nov 21, 2017 at 4:16 PM Stephen Chrzanowski
>
>wrote:
>
>> I love the email methodology, and I'd honestly be sad to see it go.
>But if
>> GMail is causing the mischaracterization of the mail, maybe just a
>note on
>> the sqlite.org home page that directs people on how to whitelist
>the
>> mailing list?
>>
>> I'm indifferent to the forum idea, but, so long the forum software
>will
>> give me notifications of ALL entries, with the full content of the
>post.
>> That way, I can decide if I want to jump on the forum and
>contribute, or
>> ask.
>>
>> On Tue, Nov 21, 2017 at 9:52 AM, Dominique Devienne
>
>> wrote:
>>
>> >
>> > But many people still prefer email. I doubt something else would
>be as
>> > convenient.
>> >
>> >
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Keith Medcalf]

In my opinion it is the beginning of the end of crappy freemail providers and 
their overzealous spam filtering.  And it is about time.  

If you run an RFC complaint MTA then there is really very little problem with 
SPAM at all -- I have many connections per second rejected for RFC 
non-compliance -- and get maybe 3 SPAM messages per day, all of which originate 
from the crappy Johhny-cum-lately freemail systems.  It is just that the 
Johhny-cum-lately's (freemail, telco's, cableco's) have no idea how to run RFC 
compliant Internet Hosts and MTA's that have issues.  Plus those that insist on 
being RFC non-compliant so they can communicate with the Johhny-cum-lately 
non-RFC compliant hosts and MTA's.  

A far better approach is to remain RFC compliant and if someone you want to 
communicate with insists on not being an RFC compliant Internet host and MTA, 
then tell them to bugger off and use old fashioned snail-mail that does not 
require a properly configured host connected to the Internet.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
>Sent: Tuesday, 21 November, 2017 07:31
>To: SQLite mailing list
>Subject: Re: [sqlite] Many ML emails going to GMail's SPAM
>
>On 11/21/17, Paul Sanderson  wrote:
>> Coincidence!  I have just been in my gmail folder marking a load of
>SQLite
>> email as 'not spam'
>
>I've been seeing mailing list emails go to spam for a while now.
>Nothing has changed with MailMan.  I think what we are seeing is the
>beginning of the end of email as a viable communication medium.
>
>I really need to come up with an alternative to the mailing list.
>Perhaps some kind of forum system.  Suggestions are welcomed.
>--
>D. Richard Hipp
>d...@sqlite.org
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Many ML emails going to GMail's SPAM

[J. King]

I greatly prefer e-mail, too. It's a shame mailing lists run afoul of SPF and 
usually DKIM, and doubly so that ARC is unlikely to be of much help. 

I abhor Discourse, so it's depressing for me that it's so popular. Alas...

On November 21, 2017 10:16:24 AM EST, Stephen Chrzanowski  
wrote:
>I love the email methodology, and I'd honestly be sad to see it go. 
>But if
>GMail is causing the mischaracterization of the mail, maybe just a note
>on
>the sqlite.org home page that directs people on how to whitelist the
>mailing list?
>
>I'm indifferent to the forum idea, but, so long the forum software will
>give me notifications of ALL entries, with the full content of the
>post.
>That way, I can decide if I want to jump on the forum and contribute,
>or
>ask.
>
>On Tue, Nov 21, 2017 at 9:52 AM, Dominique Devienne
>
>wrote:
>
>>
>> But many people still prefer email. I doubt something else would be
>as
>> convenient.
>>
>>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Wout Mertens]

Discourse has a mailing-list mode you can enable, which will send you all
posts (I presume, I never tried it)

The default setup sends you interesting new topics at an interval of your
choosing.

What I like very much about Discourse:

   - great engagement
   - easy following of only those topics that interest you
   - great way to have a live archive of posts
   - no spam. The JS hoops spammers have to jump through are a great
   deterrent so far.

I must say that I can't really remember a google search resulting in a post
on a Discourse forum. I wonder if it has bad googlability or if other
sources are deemed better by Google, or if Discourse is simply not very
popular.

On Tue, Nov 21, 2017 at 4:16 PM Stephen Chrzanowski 
wrote:

> I love the email methodology, and I'd honestly be sad to see it go.  But if
> GMail is causing the mischaracterization of the mail, maybe just a note on
> the sqlite.org home page that directs people on how to whitelist the
> mailing list?
>
> I'm indifferent to the forum idea, but, so long the forum software will
> give me notifications of ALL entries, with the full content of the post.
> That way, I can decide if I want to jump on the forum and contribute, or
> ask.
>
> On Tue, Nov 21, 2017 at 9:52 AM, Dominique Devienne 
> wrote:
>
> >
> > But many people still prefer email. I doubt something else would be as
> > convenient.
> >
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Stephen Chrzanowski]

I love the email methodology, and I'd honestly be sad to see it go.  But if
GMail is causing the mischaracterization of the mail, maybe just a note on
the sqlite.org home page that directs people on how to whitelist the
mailing list?

I'm indifferent to the forum idea, but, so long the forum software will
give me notifications of ALL entries, with the full content of the post.
That way, I can decide if I want to jump on the forum and contribute, or
ask.

On Tue, Nov 21, 2017 at 9:52 AM, Dominique Devienne 
wrote:

>
> But many people still prefer email. I doubt something else would be as
> convenient.
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Peter Da Silva]

On 11/21/17, 8:52 AM, "sqlite-users on behalf of Dominique Devienne" 
 
wrote:
> After re-inventing database and source-control, forum software next? :) I 
> have no doubt it would be lean, fast, SQLite-based, in C (and/or TCL).

Plus XMPP and NNTP/NNRPD interfaces, and a PERFECT bidirectional email gateway 
that maintains BOTH kinds of threading.
 

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Dominique Devienne]

On Tue, Nov 21, 2017 at 3:30 PM, Richard Hipp  wrote:

> On 11/21/17, Paul Sanderson  wrote:
> > Coincidence!  I have just been in my gmail folder marking a load of
> SQLite
> > email as 'not spam'
>
> I've been seeing mailing list emails go to spam for a while now.
> Nothing has changed with MailMan.  I think what we are seeing is the
> beginning of the end of email as a viable communication medium.
>

I really need to come up with an alternative to the mailing list.
> Perhaps some kind of forum system.  Suggestions are welcomed.
>

After re-inventing database and source-control, forum software next? :)
I have no doubt it would be lean, fast, SQLite-based, in C (and/or TCL).
Fossile's web-ui is almost like a forum already in fact.

But many people still prefer email. I doubt something else would be as
convenient.

I've used a local install of https://www.discourse.org/ at work, and it's
nice,
with some similarities to StackOverflow for editing, no surprise given Jeff
Atwood's involvement. OpenSource. But I suspect it's not "lite" enough for
you Richard :) Plus it's probably not SQLite based, which is just silly!

Joking apart, I can live with the occasional GMail mischaracterization of
SPAM.
Glad to hear it's not related to a recent MailMan config change. Case
closed, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Simon Slavin]

On 21 Nov 2017, at 2:30pm, Richard Hipp  wrote:

> I really need to come up with an alternative to the mailing list.
> Perhaps some kind of forum system.  Suggestions are welcomed.

If we’re to end up with a chat-based system then I’d prefer Discord.

I can’t recommend a web-based forum system right now.  I’ve seen a lot of them 
and don’t find any superior to any other.  Or, in fact, any good at all.

Simon.

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

Re: [sqlite] SELECT result different after ANALYZE

[Ralf Junker]

On 21.11.2017 15:36, Richard Hipp wrote:


I'll be working on some other solution for you.


Many thanks, but this is not necessary. I can rebuild from Fossil.

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

Re: [sqlite] SELECT result different after ANALYZE

[Richard Hipp]

On 11/21/17, Richard Hipp  wrote:
>
> To work around this problem, please DROP all indexes on the INTEGER
> PRIMARY KEY columns.

Except, you don't have any indexes on INTEGER PRIMARY KEY columns.  I
misread the schema.

I'll be working on some other solution for you.

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Stephen Chrzanowski]

I know it can't be expected of users, but, I've setup GMail to whitelist
anything coming from the mail list.  GMail tells me that the message should
have gone to spam, but because of a rule, yadda yadda.


On Tue, Nov 21, 2017 at 9:30 AM, Richard Hipp  wrote:

> On 11/21/17, Paul Sanderson  wrote:
> > Coincidence!  I have just been in my gmail folder marking a load of
> SQLite
> > email as 'not spam'
>
> I've been seeing mailing list emails go to spam for a while now.
> Nothing has changed with MailMan.  I think what we are seeing is the
> beginning of the end of email as a viable communication medium.
>
> I really need to come up with an alternative to the mailing list.
> Perhaps some kind of forum system.  Suggestions are welcomed.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Richard Hipp]

On 11/21/17, Paul Sanderson  wrote:
> Coincidence!  I have just been in my gmail folder marking a load of SQLite
> email as 'not spam'

I've been seeing mailing list emails go to spam for a while now.
Nothing has changed with MailMan.  I think what we are seeing is the
beginning of the end of email as a viable communication medium.

I really need to come up with an alternative to the mailing list.
Perhaps some kind of forum system.  Suggestions are welcomed.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT result different after ANALYZE

[Richard Hipp]

On 11/20/17, David Raymond  wrote:
>
> To reproduce, download this database file (5.6MB, SHA1
> 12d1295d06327ee19ed2453517b0dd83233c6829, available for two days from now):
>
>https://expirebox.com/download/328baafe26688579fccd55debfc54ad3.html
>
> This SQL returns a single result row with a value of 1:
>
> SELECT DISTINCT t2.a FROM t1
>   INNER JOIN t2 ON t1.t2_id = t2.id
>   WHERE t1.t2_id <> -1;
>
> Then run ANALYZE and run the above select again. This time I receive no
> result.

Thank you for the bug report.

To work around this problem, please DROP all indexes on the INTEGER
PRIMARY KEY columns.  Such indexes are accomplish nothing (they will
never be used on a real query - they are just slow down inserts and
updates and take up extra space on disk) except in this case they do
appear to be confusing the query planner.

This problem will be corrected in the next release.


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

Re: [sqlite] Energy consumption of SQLite queries

[Robert Oeffner]

This is an interesting topic more belonging to the realms of information 
theory and statistical physics.


I am not an expert in this area but from what I recall from 
undergraduate physics the moment you create order in one corner of the 
universe entropy rises in another place of the universe. If you loosely 
speaking equate information gathering such as an SQL query as creating 
order then that must have a cost in terms of increasing the entropy 
(heat in this case) elsewhere. There is a lower bound on how little 
entropy is generated during this process which comes down to the 
efficiency of the process (hardware and software in your case).


One could get philosophical here and question whether mankinds computer 
modeling of climate change in itself causes the excess heat leading to 
global warming.



Regards,

Robert


--
Robert Oeffner, Ph.D.
Research Associate,
The Read Group, Department of Haematology,
Cambridge Institute for Medical Research
University of Cambridge
Cambridge Biomedical Campus
Wellcome Trust/MRC Building
Hills Road
Cambridge CB2 0XY
www.cimr.cam.ac.uk/investigators/read/index.html




Date: Tue, 21 Nov 2017 09:54:25 +1100
From: Ali Dorri 
To: SQLite mailing list 
Subject: [sqlite] Energy consumption of SQLite queries
Message-ID:

Re: [sqlite] Energy consumption of SQLite queries

[Paul Sanderson]

A pretty much impossible task I would think.

The power usage of SQLite compared to the power usage of different hardware
components would be miniscule. But, there are so many other tasks running
on a system, many in the background, that isolating SQLite from the rest
would be next to impossible. Just look at process on a windows system via
the task manager or a linux system using top to get a very simplistic idea
of the different tasks that are using processor time - Sort by processor
usage and the list is always changing even when you are doing nothing. Add
in variable speed fans and processor throttling to manage temperature/power
consumption etc. and you have a mammoth task.

Good luck :)

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 21 November 2017 at 00:36, Simon Slavin  wrote:

> On 20 Nov 2017, at 10:54pm, Ali Dorri  wrote:
>
> > I am doing a research on the energy consumed by a query in SQLite. I
> have a
> > program which fills a database with blocks of data. Then, it attempts to
> > remove some data from the database. I don't know how to measure the
> energy
> > consumed from my host, i.e., my laptop which has both the SQLite and the
> > program, from the time I generated the query till the query is finished
> and
> > control returns back to my program.
>
> This is a hardware question, not anything to do with a particular piece of
> software.
>
> If you have a desktop computer, get one of those gadgets that you plug
> into the power socket and monitors how much power is passed to things that
> plug into them:
>
> 
>
> On a laptop, since the power is taken from an internal battery, and mains
> power is used to recharge it inconsistently, monitoring power usage from
> the mains is pointless.  See if the firmware provides a display or an API
> function which shows how much is going out.
>
> Then set up side-by-side comparisons, one with your computer doing those
> things in SQLite and one without.  The differences between the two power
> consumptions is how much power SQLite is using.  Unless you have really
> detailed power measurement, the results will be small and probably
> meaningless.
>
> Since you mention doing side-by-side comparisons with other databases,
> your setup should probably be comparing the same computer doing things in
> different DBMSs.  Maybe set up some procedure for doing something 10,000
> times and see how much power is used in total.
>
> Worth noting that power consumption from SQLite will be terribly
> inconsistent, based on what data is cached, how many database pages need to
> be accessed, and the state of the journal files.  This pales into
> insignificance, however, with the inconsistency of most other DBMSs, which
> perform far more internal caching and indexing.  You will get very
> different results from the same setup depending on how long the DBMS server
> has been running, not just on how long the computer has been turned on.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [OT] Updating sqlite in PHP windows

[Simon Slavin]

On 21 Nov 2017, at 10:30am, Eduardo  wrote:

> Thanks Simon. It seems that php 5.6 it's EOL and all updates (except 
> security) are on 7.1 and 7.2 branchs. 
> 
> See the answers I get from php staff, 7.2 will have 3.21 version plus FTS and 
> JSON extensions on (If I understand correctly)

Oh good grief yes.  If you’re still using PHP 5 please upgrade to PHP 7 unless 
you have compatibility problems.  So many bugs fixed, so much better security.

Which version of SQLite you get with it depends on your source and platform, 
but you can cross that bridge when you come to it.

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Paul Sanderson]

Coincidence!  I have just been in my gmail folder marking a load of SQLite
email as 'not spam'

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 21 November 2017 at 10:35, Dominique Devienne 
wrote:

> Just FYI. Not sure if something changed on the mailer's settings.
> Possibly/likely linked to GMail changing it's SPAM heuristics I guess. --DD
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Many ML emails going to GMail's SPAM

[Dominique Devienne]

Just FYI. Not sure if something changed on the mailer's settings.
Possibly/likely linked to GMail changing it's SPAM heuristics I guess. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [OT] Updating sqlite in PHP windows

[Eduardo]

On Mon, 20 Nov 2017 14:33:53 +
Simon Slavin  escribió:

> On 20 Nov 2017, at 11:06am, Eduardo  wrote:
> 
> > Or better, a recipe that works to compile sqlite3 on php5.6.x?
> 
> This is the best-looking page I’ve found, but I have never tried it on 
> Windows.
> 
> 

Thanks Simon. It seems that php 5.6 it's EOL and all updates (except security) 
are on 7.1 and 7.2 branchs. 

See the answers I get from php staff, 7.2 will have 3.21 version plus FTS and 
JSON extensions on (If I understand correctly)

https://bugs.php.net/bug.php?id=75544

> Simon.


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

Re: [sqlite] how into insert row into middle of table with integer primary key

[R Smith]

On 2017/11/21 7:35 AM, Jens Alfke wrote:



On Nov 20, 2017, at 2:05 PM, Simon Slavin  wrote:

INSERT INTO fruit VALUES ((1 + 2) / 2), 'banana')

This gives you a value of 1.5, and puts the new entry in the right place.

This solution (which comes up every time this problem is discussed, it seems) 
is attractive but not very scaleable. All you have to do is add 60 records one 
at a time after record 1, and you’ll exceed the precision of double-precision 
floating point and get duplicate values that don’t have a stable sort order.


It doesn't really matter

That assumes you are not starting from an integer part (like 4000) and 
hitting the exact same relative insert spot every time, which /can/ 
happen, but is hugely unlikely.


In the very unlikely event that you /are/ inserting at the same spot 
(let's ignore for a moment that the chosen design is flawed if this is 
the case) , you definitely can run into the limit of division precision. 
However, the solution is pretty simple:


The moment you assign a Sort Index value that differs from its neighbour 
by less than, say, 1x10^-8  (that's still many bits away from the 
limit), then run (or at least flag/schedule for) your Sort-Index 
re-balancing operation.


The fact that a normal double precision float is only 64 bits long is 
never a reason to panic and doesn't invalidate a solution, though it 
does mean you need to pay attention.


Also worthy to note, this solution is only really great if you have an 
insanely big dataset or insert loads of entries at a time and so want to 
defer a more expensive sort re-jig till later. If you only insert one 
new thing now and again on a medium sized db, then just rejig the Sort 
indexer immediately.


What Jens' point does illustrate is: This solution *must* be accompanied 
by some Sort-Index re-jigging algorithm.

You have however a lot of freedom in choosing the frequency and scope of it.

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

Re: [sqlite] read/write binary data via tcl

[rene]

-readonly is it.

Sorry for the noise.



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

Re: [sqlite] read/write binary data via tcl

[rene]

Getting binary data back with incrblob is only possible with a writeable
database connection.
Is there a way to do this with a readonly database connection?


Thank you
rene



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