Domain design for backend storage?

[xeoncross]

Instead of combining the sql queries inside the backend logic, would it be possible to create domain objects so that multiple backends (SQL, key/value, disk, AWS storage, Memory, etc...) could be written to use the great backend logic you're pulling together?

In other words, simply provide a type BackendStorage interface{ ... } to whatever logic you're working on so that multiple storage solutions could be built and used.

Then again, I realize it's called "go-sqlmail", but you're building a needed tool and wanted to mention this as soon as possible to see if it could be a goal.

[foxcpp]

Backend interface defined by go-imap is already a storage abstraction. I don't think it is a good idea to add another abstraction for the same purpose. There is not much "backend logic" in go-sqlmail, it just sends queries to RDBMS and converts responses to format consumed by go-imap. If somebody wants to implement AWS/disk/etc storage - they probably should just implement go-imap interface, this will be much easier, especially since there is backendutil package which contains basically all logic you would need for implementation.

However, this all may be different for SMTP backend. I haven't looked into it but go-smtp may be a little bit too low-level. But in this case I'm going to contribute implementation of higher-level interfaces into go-smtp instead of keeping them here.

I had some ideas about moving message contents to external non-SQL storage, while keeping metadata in DB, but this is not the priority because popular RDBMSs handle large blobs in rows well enough.

[foxcpp]

If you don't have any other questions - close the issue.

[xeoncross]

Thanks for your reply and explanation. Keep up the good work!

[sapiens-sapide]

Hello @foxcpp @xeoncross,

FYI I'm also digging a way to store only relevant data into db and leave raw mail to filesystem or object store (because it's not a good idea to store huge emails in SQL db).

I'm amazed by the job made by @emersion, and by you @foxcpp, and like to join the journey !
Here is my point of view, — and experience — :
all you need to handle emails are 3 capabilities:

1. an object store to handle raw email (could be FS or S3)
2. a database to handle meta data about emails and basic search capabilities.
3. a full text engine to handle more advanced searches.

At this stage, and as far as I understand, go-imap backend interfaces may be too broad to easily build different storage solutions on top.

To go further, I think we should agree on :

• an extended model to store relevant meta-data in db : goal is to satisfy most IMAP (and SMTP) commands, excepting those that require to fetch full email body. (think how Dovecot index works)
• an extended IMAP-backend interface that should define all the necessary funcs a store must implement regarding IMAP commands, or command sets (mailboxes, messages, search, idle, etc…). Note that authentication and users management should not fall into this interface.
• similary : a SMTP-backend interface that should define all the necessary funcs a store must implement regarding STMP commands.

Thus, we'll build different backend solutions that all agree and rely on common interface sets, but with different implementations or storage paradigms.

I've write down a schema to go in this direction and I'd definitely share it with you if you think it's a good direction.

Stan.

[foxcpp]

Primary design goal of go-sqlmail is to provide storage backend for maddy. maddy is currently just nothing but a proxy, but I posted some ideas here: foxcpp/maddy#15. @sapiens-sapide., you may want to join discussion since we are moving in direction of interface splitting and so your work can be useful here.

I overlooked some design issues when I started development of go-sqlmail, so now I think it is a good idea to support different storage for metadata and body.

[foxcpp]

It doesn't look like we have any progress here. So I will document my own ideas on how I would design it.

First, we abstract away "object store" using the following interface (we use io.Reader because if underlying storage supports streaming API - it will be useful to us).

type ObjectStore interface {
  Store(key string, data io.Reader) error
  Load(key string) (io.ReadCloser, error)
  Delete(key string) error
}

Second, add the ObjectStore field to imap.Opts{}. Add body_ext column to msgs table.
If body_ext is 1 - use ObjectStore with contents of body column as a key. This allows performing simple migration. If ObjectStore is set, all new messages will use it. Keys for ObjectStore are independent of IMAP UIDs so we will not have to rewrite them if we invalidate UIDs for whatever reason.

The ObjectStore key is not changed if we move messages. The key is not changed if we copy messages either since message contents are immutable in IMAP. But we also have to keep track of references to ObjectStore keys then. This is done via a separate table called ext_keys.

I'm not sure about how to interface with the FTS engine, however.

[foxcpp]

I'm working on this. go-imap-sql 0.2 will support so-called "external store" interface. There will be maildir package implementing an external store using a Maildir-like structure. It will not used by default tho.

[foxcpp]

The interface is here: https://github.com/foxcpp/go-imap-sql/blob/extstore/external_store.go.
Are we good to go and this issue should be closed or you have some concerns regarding design?

[sapiens-sapide]

I'm still working on another design in which mails are stored as 'raw' objects, — ie full bytes, including headers —, alongside mails' metadata and lexicon in a separate database and index. I think it's better to store the raw emails, without any logic embedded, even mbox reference.
It's still a WIP and I lack of time, so I'm sorry not being able to share anything for now… May be in few weeks.

[foxcpp]

Take your time, we are not in a hurry. Your design may turn out to be better, so I guess I will wait for it instead of releasing go-imap-sql 0.2 with my experiment (I'm calling it this way becuase I'm not sure about how well it works).

[foxcpp]

Okay, I stepped forward and simplified the "store" interface.

[foxcpp]

Merged extstore into dev.