Foal DB design issue - silent but deadly replacements

[sebb <se...@gmail.com>]

I think there is a basic problem with the Foal database design which
means that historic permalinks can be lost.

This has already been seen with the migration tool.

I just realised that the same issue affects imports and archives as well.

The problem is that an insert operation silently replaces any existing record.
Any existing content is lost; this includes any historic permalinks.
It will also affect settings such as the deleted flag.

As it stands, importing a message file may cause information to be lost.

With the previous release of Ponymail, I don't think this was a
problem; a message archive could generally be re-imported without
causing any issues.This made it very easy to backfill missing messages
from archives, as there was no need to allow for existing entries.

At present, I don't think it is safe to allow imports, unless they are
guaranteed to be brand new.

I think the mbox insert action needs to be changed to create
throughout, and appropriate action taken for duplicates.

Re: Foal DB design issue - silent but deadly replacements

[sebb <se...@gmail.com>]

On Tue, 28 Dec 2021 at 17:07, Daniel Gruno <hu...@apache.org> wrote:
>
> On 27/12/2021 05.22, sebb wrote:
> > I think there is a basic problem with the Foal database design which
> > means that historic permalinks can be lost.
> >
> > This has already been seen with the migration tool.
> >
> > I just realised that the same issue affects imports and archives as well.
> >
> > The problem is that an insert operation silently replaces any existing record.
> > Any existing content is lost; this includes any historic permalinks.
> > It will also affect settings such as the deleted flag.
> >
> > As it stands, importing a message file may cause information to be lost.
> >
> > With the previous release of Ponymail, I don't think this was a
> > problem; a message archive could generally be re-imported without
> > causing any issues.This made it very easy to backfill missing messages
> > from archives, as there was no need to allow for existing entries.
> >
> > At present, I don't think it is safe to allow imports, unless they are
> > guaranteed to be brand new.
> >
> > I think the mbox insert action needs to be changed to create
> > throughout, and appropriate action taken for duplicates.
>
> There should be a simple check in the archiver, something like:

The importer also needs to do the same check; it's more likely to
generate clashes than the archiver.

> if DKIM ID exists, update the permalinks only, by appending the
> potentially new IDs to the existing DB entry.

As already noted, the easiest way to detect clashes is to use "create"
when inserting an entry.
This will fail if the entry already exists.

It's obviously necessary to update the Permalinks.
But it's not just Permalinks that matter.
Other fields need to be considered.

What about different 'delete' and 'private' flags?

What if the new doc has a different body id?

What about _notes and _archived_at?

Re: Foal DB design issue - silent but deadly replacements

[Daniel Gruno <hu...@apache.org>]

On 27/12/2021 05.22, sebb wrote:
> I think there is a basic problem with the Foal database design which
> means that historic permalinks can be lost.
> 
> This has already been seen with the migration tool.
> 
> I just realised that the same issue affects imports and archives as well.
> 
> The problem is that an insert operation silently replaces any existing record.
> Any existing content is lost; this includes any historic permalinks.
> It will also affect settings such as the deleted flag.
> 
> As it stands, importing a message file may cause information to be lost.
> 
> With the previous release of Ponymail, I don't think this was a
> problem; a message archive could generally be re-imported without
> causing any issues.This made it very easy to backfill missing messages
> from archives, as there was no need to allow for existing entries.
> 
> At present, I don't think it is safe to allow imports, unless they are
> guaranteed to be brand new.
> 
> I think the mbox insert action needs to be changed to create
> throughout, and appropriate action taken for duplicates.

There should be a simple check in the archiver, something like:
if DKIM ID exists, update the permalinks only, by appending the 
potentially new IDs to the existing DB entry.