!
Zotlabs|Hubzilla Development As told in the support forum earlier, I want to modify the email invite stuff. The initial reason was to extent some functionality. Now, I see the need to rewrite the stuff at all because of some detected misbehavior. That requires at least to modify the register table, or perhaps to have a table scheme redesign. The question arises because postgres does not support the same kind of modifications as mysql. To add fields to the scheme, mysql allows new columns to be placed in a disired order, while postgres supports only an add to the end. In most cases, the defined order of columns does not matter access times. Thus, the order has only a nice or a less nice look.
Currently, the columns in register are named id, hash, ... etc. Most other tables have more descriptive column names, easier to apply in a more complex query. Thus, the question is, should the column names altered to some like reg_id, reg_hash, ... etc on the occasion?
Current design:
CREATE TABLE 'register' (
'id' int(10) unsigned NOT NULL AUTO_INCREMENT,
'hash' char(191) NOT NULL DEFAULT '',
'created' datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
'uid' int(10) unsigned NOT NULL DEFAULT '0',
'password' char(191) NOT NULL DEFAULT '',
'lang' char(16) NOT NULL DEFAULT '',
PRIMARY KEY ('id'),
KEY 'hash' ('hash'),
KEY 'created' ('created'),
KEY 'uid' ('uid')
) ENGINE=InnoDB AUTO_INCREMENT=489 DEFAULT CHARSET=utf8mb4;
Proposed design:
CREATE TABLE IF NOT EXISTS 'register' (
'reg_id' int(10) unsigned NOT NULL AUTO_INCREMENT,
'reg_hash' char(191) NOT NULL DEFAULT '',
'reg_email' char(191) NOT NULL DEFAULT '',
'reg_created' datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
'reg_expires' datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
'reg_uid' int(10) unsigned NOT NULL DEFAULT 0 ,
'reg_password' char(191) NOT NULL DEFAULT '',
'reg_lang' char(16) NOT NULL DEFAULT '',
'reg_stuff' json NOT NULL DEFAULT '',
PRIMARY KEY ('reg_id'),
KEY 'ix_reg_hash' ('reg_hash'),
KEY 'ix_reg_email' ('reg_email'),
KEY 'ix_reg_created' ('reg_created'),
KEY 'ix_reg_expires' ('reg_expires'),
KEY 'ix_reg_uid' ('reg_uid')
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Reasons:
reg_email
is to verify the registration if bound to one email address.
reg_expires
is to check if the invitation does not exceed a given time frame
reg_lang
is to adjust the account according to the language used just during the invitation (note, individual language selection is served when writing and sending the invitations - part of the new stuff).
reg_stuff
is only for any future use, if additional data should be saved for what reason ever. Prevents the need of scheme modifications for some minor changes.
