SQLite Forum

SQLite Forum: "Unsubscribe" link
Login

SQLite Forum: "Unsubscribe" link

(1) By Stephen C (StephenC) on 2020-03-27 16:09:31 [link] [source]

As I was changing the forum to send me daily digests instead of individuals, I noticed the "Unsubscribe" link. Does that delete my account, or just toggle the check boxes?

I don't wish to unsub, so going to avoid pushing the button just because I don't want to "science it out" on that particular function.... ;)

(2) By Richard Hipp (drh) on 2020-03-27 16:28:48 in reply to 1 [link] [source]

The "unsubscribe" button (which does require confirmation, btw) deletes your entry from the SUBSCRIBER table. But if you also have an entry in the USER table, that USER table entry is retained.

There are two places where user identities are stored:

  1. The USER table, holds logins and passwords and your display name. The USER table is present in every repository.

  2. The SUBSCRIBER table tracks notification requests and the email addresses for where to send the notifications. The SUBSCRIBER table might not exist in repositories for which notification has never been configured.

A visitor to the website can be a USER or a SUBSCRIBER or neither or both.

The SUBSCRIBER table contains a reference to the corresponding USER, if there is one, in the SUBSCRIBER.SUNAME field. SUNAME is NULL for subscribers that do not correspond to users.

SUBSCRIBERS have no passwords. Instead, there is a big random identifier (the SUBSCRIBERCODE) that is shown at the bottom of every notification email. Anybody with knowledge of the SUBSCRIBERCODE can modify the subscription information. There are no passwords, because I wanted it to be easy to subscribe without having to remember yet another password.

USERS are more of the traditional user concept with a password.

The USER table contains a INFO field which can contain anything. The convention for the INFO content is that the first line is the display name followed by the email address inside of <...>. But that is only a convention and is not enforced. For self-registered users, the INFO field follows that format, but for manually created users, not necessarily. There are extension SQL functions in Fossil:

  • find_emailaddr(X) → Given a USER.INFO value X, locate and return the email address, or NULL if there is none.

  • display_name(X) → Given USER.INFO value X, locate and return the display name if there is one, otherwise return NULL.

The display_name() SQL function was added earlier today and is what I use to paint the display names on forum posts. Prior to today, I was extracting the display name using this SQL expression:

   trim(substr(user.info,1,instr(user.info,'<')-1))

That was after doing a LEFT JOIN on the user table. The new display_name() function seems both simpler to use and faster to execute.

The perfect solution would to be to have separate EMAIL and DISPLAYNAME fields in the USER table. But that would require a schema change and the ensuing complexity of automatically updating the schema seems like it is more trouble and risk than it is worth.

(3) By Richard Hipp (drh) on 2020-03-27 16:34:21 in reply to 2 [source]

My previous reply probably should have gone to the Fossil Forum. I wasn't paying attention to where I was posting...