use Friendica\Database\DBA;
if (!defined('DB_UPDATE_VERSION')) {
- define('DB_UPDATE_VERSION', 1373);
+ define('DB_UPDATE_VERSION', 1376);
}
return [
"nurl" => ["UNIQUE", "nurl(190)"],
]
],
+ "user" => [
+ "comment" => "The local users",
+ "fields" => [
+ "uid" => ["type" => "mediumint unsigned", "not null" => "1", "extra" => "auto_increment", "primary" => "1", "comment" => "sequential ID"],
+ "parent-uid" => ["type" => "mediumint unsigned", "not null" => "1", "default" => "0", "foreign" => ["user" => "uid"],
+ "comment" => "The parent user that has full control about this user"],
+ "guid" => ["type" => "varchar(64)", "not null" => "1", "default" => "", "comment" => "A unique identifier for this user"],
+ "username" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => "Name that this user is known by"],
+ "password" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => "encrypted password"],
+ "legacy_password" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "Is the password hash double-hashed?"],
+ "nickname" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => "nick- and user name"],
+ "email" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => "the users email address"],
+ "openid" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => ""],
+ "timezone" => ["type" => "varchar(128)", "not null" => "1", "default" => "", "comment" => "PHP-legal timezone"],
+ "language" => ["type" => "varchar(32)", "not null" => "1", "default" => "en", "comment" => "default language"],
+ "register_date" => ["type" => "datetime", "not null" => "1", "default" => DBA::NULL_DATETIME, "comment" => "timestamp of registration"],
+ "login_date" => ["type" => "datetime", "not null" => "1", "default" => DBA::NULL_DATETIME, "comment" => "timestamp of last login"],
+ "default-location" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => "Default for item.location"],
+ "allow_location" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "1 allows to display the location"],
+ "theme" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => "user theme preference"],
+ "pubkey" => ["type" => "text", "comment" => "RSA public key 4096 bit"],
+ "prvkey" => ["type" => "text", "comment" => "RSA private key 4096 bit"],
+ "spubkey" => ["type" => "text", "comment" => ""],
+ "sprvkey" => ["type" => "text", "comment" => ""],
+ "verified" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "user is verified through email"],
+ "blocked" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "1 for user is blocked"],
+ "blockwall" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "Prohibit contacts to post to the profile page of the user"],
+ "hidewall" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "Hide profile details from unkown viewers"],
+ "blocktags" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "Prohibit contacts to tag the post of this user"],
+ "unkmail" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "Permit unknown people to send private mails to this user"],
+ "cntunkmail" => ["type" => "int unsigned", "not null" => "1", "default" => "10", "comment" => ""],
+ "notify-flags" => ["type" => "smallint unsigned", "not null" => "1", "default" => "65535", "comment" => "email notification options"],
+ "page-flags" => ["type" => "tinyint unsigned", "not null" => "1", "default" => "0", "comment" => "page/profile type"],
+ "account-type" => ["type" => "tinyint unsigned", "not null" => "1", "default" => "0", "comment" => ""],
+ "prvnets" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => ""],
+ "pwdreset" => ["type" => "varchar(255)", "comment" => "Password reset request token"],
+ "pwdreset_time" => ["type" => "datetime", "comment" => "Timestamp of the last password reset request"],
+ "maxreq" => ["type" => "int unsigned", "not null" => "1", "default" => "10", "comment" => ""],
+ "expire" => ["type" => "int unsigned", "not null" => "1", "default" => "0", "comment" => ""],
+ "account_removed" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "if 1 the account is removed"],
+ "account_expired" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => ""],
+ "account_expires_on" => ["type" => "datetime", "not null" => "1", "default" => DBA::NULL_DATETIME, "comment" => "timestamp when account expires and will be deleted"],
+ "expire_notification_sent" => ["type" => "datetime", "not null" => "1", "default" => DBA::NULL_DATETIME, "comment" => "timestamp of last warning of account expiration"],
+ "def_gid" => ["type" => "int unsigned", "not null" => "1", "default" => "0", "comment" => ""],
+ "allow_cid" => ["type" => "mediumtext", "comment" => "default permission for this user"],
+ "allow_gid" => ["type" => "mediumtext", "comment" => "default permission for this user"],
+ "deny_cid" => ["type" => "mediumtext", "comment" => "default permission for this user"],
+ "deny_gid" => ["type" => "mediumtext", "comment" => "default permission for this user"],
+ "openidserver" => ["type" => "text", "comment" => ""],
+ ],
+ "indexes" => [
+ "PRIMARY" => ["uid"],
+ "nickname" => ["nickname(32)"],
+ "parent-uid" => ["parent-uid"],
+ ]
+ ],
"contact" => [
"comment" => "contact table",
"fields" => [
"id" => ["type" => "int unsigned", "not null" => "1", "extra" => "auto_increment", "primary" => "1", "comment" => "sequential ID"],
- "uid" => ["type" => "mediumint unsigned", "not null" => "1", "default" => "0", "relation" => ["user" => "uid"], "comment" => "Owner User id"],
+ "uid" => ["type" => "mediumint unsigned", "not null" => "1", "default" => "0", "foreign" => ["user" => "uid"], "comment" => "Owner User id"],
"created" => ["type" => "datetime", "not null" => "1", "default" => DBA::NULL_DATETIME, "comment" => ""],
"updated" => ["type" => "datetime", "default" => DBA::NULL_DATETIME, "comment" => "Date of last contact update"],
"self" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "1 if the contact is the user him/her self"],
"url" => ["url"]
]
],
- "user" => [
- "comment" => "The local users",
- "fields" => [
- "uid" => ["type" => "mediumint unsigned", "not null" => "1", "extra" => "auto_increment", "primary" => "1", "comment" => "sequential ID"],
- "parent-uid" => ["type" => "mediumint unsigned", "not null" => "1", "default" => "0", "relation" => ["user" => "uid"],
- "comment" => "The parent user that has full control about this user"],
- "guid" => ["type" => "varchar(64)", "not null" => "1", "default" => "", "comment" => "A unique identifier for this user"],
- "username" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => "Name that this user is known by"],
- "password" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => "encrypted password"],
- "legacy_password" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "Is the password hash double-hashed?"],
- "nickname" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => "nick- and user name"],
- "email" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => "the users email address"],
- "openid" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => ""],
- "timezone" => ["type" => "varchar(128)", "not null" => "1", "default" => "", "comment" => "PHP-legal timezone"],
- "language" => ["type" => "varchar(32)", "not null" => "1", "default" => "en", "comment" => "default language"],
- "register_date" => ["type" => "datetime", "not null" => "1", "default" => DBA::NULL_DATETIME, "comment" => "timestamp of registration"],
- "login_date" => ["type" => "datetime", "not null" => "1", "default" => DBA::NULL_DATETIME, "comment" => "timestamp of last login"],
- "default-location" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => "Default for item.location"],
- "allow_location" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "1 allows to display the location"],
- "theme" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => "user theme preference"],
- "pubkey" => ["type" => "text", "comment" => "RSA public key 4096 bit"],
- "prvkey" => ["type" => "text", "comment" => "RSA private key 4096 bit"],
- "spubkey" => ["type" => "text", "comment" => ""],
- "sprvkey" => ["type" => "text", "comment" => ""],
- "verified" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "user is verified through email"],
- "blocked" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "1 for user is blocked"],
- "blockwall" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "Prohibit contacts to post to the profile page of the user"],
- "hidewall" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "Hide profile details from unkown viewers"],
- "blocktags" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "Prohibit contacts to tag the post of this user"],
- "unkmail" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "Permit unknown people to send private mails to this user"],
- "cntunkmail" => ["type" => "int unsigned", "not null" => "1", "default" => "10", "comment" => ""],
- "notify-flags" => ["type" => "smallint unsigned", "not null" => "1", "default" => "65535", "comment" => "email notification options"],
- "page-flags" => ["type" => "tinyint unsigned", "not null" => "1", "default" => "0", "comment" => "page/profile type"],
- "account-type" => ["type" => "tinyint unsigned", "not null" => "1", "default" => "0", "comment" => ""],
- "prvnets" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => ""],
- "pwdreset" => ["type" => "varchar(255)", "comment" => "Password reset request token"],
- "pwdreset_time" => ["type" => "datetime", "comment" => "Timestamp of the last password reset request"],
- "maxreq" => ["type" => "int unsigned", "not null" => "1", "default" => "10", "comment" => ""],
- "expire" => ["type" => "int unsigned", "not null" => "1", "default" => "0", "comment" => ""],
- "account_removed" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "if 1 the account is removed"],
- "account_expired" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => ""],
- "account_expires_on" => ["type" => "datetime", "not null" => "1", "default" => DBA::NULL_DATETIME, "comment" => "timestamp when account expires and will be deleted"],
- "expire_notification_sent" => ["type" => "datetime", "not null" => "1", "default" => DBA::NULL_DATETIME, "comment" => "timestamp of last warning of account expiration"],
- "def_gid" => ["type" => "int unsigned", "not null" => "1", "default" => "0", "comment" => ""],
- "allow_cid" => ["type" => "mediumtext", "comment" => "default permission for this user"],
- "allow_gid" => ["type" => "mediumtext", "comment" => "default permission for this user"],
- "deny_cid" => ["type" => "mediumtext", "comment" => "default permission for this user"],
- "deny_gid" => ["type" => "mediumtext", "comment" => "default permission for this user"],
- "openidserver" => ["type" => "text", "comment" => ""],
- ],
- "indexes" => [
- "PRIMARY" => ["uid"],
- "nickname" => ["nickname(32)"],
- ]
- ],
"clients" => [
"comment" => "OAuth usage",
"fields" => [
"comment" => "",
"fields" => [
"id" => ["type" => "int unsigned", "not null" => "1", "extra" => "auto_increment", "primary" => "1", "comment" => "sequential ID"],
- "uid" => ["type" => "mediumint unsigned", "not null" => "1", "default" => "0", "relation" => ["user" => "uid"], "comment" => "Owner id of this permission set"],
+ "uid" => ["type" => "mediumint unsigned", "not null" => "1", "default" => "0", "foreign" => ["user" => "uid"], "comment" => "Owner id of this permission set"],
"allow_cid" => ["type" => "mediumtext", "comment" => "Access Control - list of allowed contact.id '<19><78>'"],
"allow_gid" => ["type" => "mediumtext", "comment" => "Access Control - list of allowed groups"],
"deny_cid" => ["type" => "mediumtext", "comment" => "Access Control - list of denied contact.id"],
],
"indexes" => [
"PRIMARY" => ["id"],
- "uid_allow_cid_allow_gid_deny_cid_deny_gid" => ["allow_cid(50)", "allow_gid(30)", "deny_cid(50)", "deny_gid(30)"],
+ "uid_allow_cid_allow_gid_deny_cid_deny_gid" => ["uid", "allow_cid(50)", "allow_gid(30)", "deny_cid(50)", "deny_gid(30)"],
]
],
// Main tables
"fields" => [
"id" => ["type" => "int unsigned", "not null" => "1", "extra" => "auto_increment", "primary" => "1", "comment" => "sequential ID"],
"guid" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => ""],
- "uid" => ["type" => "mediumint unsigned", "not null" => "1", "default" => "0", "relation" => ["user" => "uid"], "comment" => "Owner User id"],
+ "uid" => ["type" => "mediumint unsigned", "not null" => "1", "default" => "0", "foreign" => ["user" => "uid"], "comment" => "Owner User id"],
"cid" => ["type" => "int unsigned", "not null" => "1", "default" => "0", "foreign" => ["contact" => "id"], "comment" => "contact_id (ID of the contact in contact table)"],
"uri" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => ""],
"created" => ["type" => "datetime", "not null" => "1", "default" => DBA::NULL_DATETIME, "comment" => "creation time"],
"uri-id" => ["type" => "int unsigned", "foreign" => ["item-uri" => "id"], "comment" => "Id of the item-uri table entry that contains the item uri"],
"uri-hash" => ["type" => "varchar(80)", "not null" => "1", "default" => "", "comment" => "RIPEMD-128 hash from uri"],
"parent" => ["type" => "int unsigned", "not null" => "1", "default" => "0", "relation" => ["item" => "id"], "comment" => "item.id of the parent to this item if it is a reply of some form; otherwise this must be set to the id of this item"],
- "parent-uri" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => "uri of the parent to this item"],
- "parent-uri-id" => ["type" => "int unsigned", "foreign" => ["item-uri" => "id"], "comment" => "Id of the item-uri table that contains the parent uri"],
+ "parent-uri" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => "uri of the top-level parent to this item"],
+ "parent-uri-id" => ["type" => "int unsigned", "foreign" => ["item-uri" => "id"], "comment" => "Id of the item-uri table that contains the top-level parent uri"],
"thr-parent" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => "If the parent of this item is not the top-level item in the conversation, the uri of the immediate parent; otherwise set to parent-uri"],
"thr-parent-id" => ["type" => "int unsigned", "foreign" => ["item-uri" => "id"], "comment" => "Id of the item-uri table that contains the thread parent uri"],
"created" => ["type" => "datetime", "not null" => "1", "default" => DBA::NULL_DATETIME, "comment" => "Creation timestamp."],
"moderated" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => ""],
"deleted" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "item has been deleted"],
// User specific fields. Eventually they will move to user-item
- "uid" => ["type" => "mediumint unsigned", "not null" => "1", "default" => "0", "relation" => ["user" => "uid"], "comment" => "Owner id which owns this copy of the item"],
+ "uid" => ["type" => "mediumint unsigned", "not null" => "1", "default" => "0", "foreign" => ["user" => "uid"], "comment" => "Owner id which owns this copy of the item"],
"contact-id" => ["type" => "int unsigned", "not null" => "1", "default" => "0", "relation" => ["contact" => "id"], "comment" => "contact.id"],
"wall" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "This item was posted to the wall of uid"],
"origin" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "item originated at this site"],
// It has to be decided whether these fields belong to the user or the structure
"resource-id" => ["type" => "varchar(32)", "not null" => "1", "default" => "", "comment" => "Used to link other tables to items, it identifies the linked resource (e.g. photo) and if set must also set resource_type"],
"event-id" => ["type" => "int unsigned", "not null" => "1", "default" => "0", "relation" => ["event" => "id"], "comment" => "Used to link to the event.id"],
- // Could possibly be replaced by the "attach" table?
- "attach" => ["type" => "mediumtext", "comment" => "JSON structure representing attachments to this item"],
// Deprecated fields. Will be removed in upcoming versions
+ "attach" => ["type" => "mediumtext", "comment" => "Deprecated"],
"allow_cid" => ["type" => "mediumtext", "comment" => "Deprecated"],
"allow_gid" => ["type" => "mediumtext", "comment" => "Deprecated"],
"deny_cid" => ["type" => "mediumtext", "comment" => "Deprecated"],
"comment" => "Store OpenWebAuth token to verify contacts",
"fields" => [
"id" => ["type" => "int unsigned", "not null" => "1", "extra" => "auto_increment", "primary" => "1", "comment" => "sequential ID"],
- "uid" => ["type" => "mediumint unsigned", "not null" => "1", "default" => "0", "relation" => ["user" => "uid"], "comment" => "User id - currently unused"],
+ "uid" => ["type" => "mediumint unsigned", "not null" => "1", "default" => "0", "foreign" => ["user" => "uid"], "comment" => "User id - currently unused"],
"type" => ["type" => "varchar(32)", "not null" => "1", "default" => "", "comment" => "Verify type"],
"token" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => "A generated token"],
"meta" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => ""],
"pconfig" => [
"comment" => "personal (per user) configuration storage",
"fields" => [
- "id" => ["type" => "int unsigned", "not null" => "1", "extra" => "auto_increment", "primary" => "1", "comment" => ""],
+ "id" => ["type" => "int unsigned", "not null" => "1", "extra" => "auto_increment", "primary" => "1", "comment" => "Primary key"],
"uid" => ["type" => "mediumint unsigned", "not null" => "1", "default" => "0", "foreign" => ["user" => "uid"], "comment" => "User id"],
- "cat" => ["type" => "varbinary(50)", "not null" => "1", "default" => "", "comment" => ""],
- "k" => ["type" => "varbinary(100)", "not null" => "1", "default" => "", "comment" => ""],
- "v" => ["type" => "mediumtext", "comment" => ""],
+ "cat" => ["type" => "varchar(50)", "not null" => "1", "default" => "", "comment" => "Category"],
+ "k" => ["type" => "varchar(100)", "not null" => "1", "default" => "", "comment" => "Key"],
+ "v" => ["type" => "mediumtext", "comment" => "Value"],
],
"indexes" => [
"PRIMARY" => ["id"],
"comment" => "photo storage",
"fields" => [
"id" => ["type" => "int unsigned", "not null" => "1", "extra" => "auto_increment", "primary" => "1", "comment" => "sequential ID"],
- "uid" => ["type" => "mediumint unsigned", "not null" => "1", "default" => "0", "relation" => ["user" => "uid"], "comment" => "Owner User id"],
+ "uid" => ["type" => "mediumint unsigned", "not null" => "1", "default" => "0", "foreign" => ["user" => "uid"], "comment" => "Owner User id"],
"contact-id" => ["type" => "int unsigned", "not null" => "1", "default" => "0", "foreign" => ["contact" => "id", "on delete" => "restrict"], "comment" => "contact.id"],
"guid" => ["type" => "char(16)", "not null" => "1", "default" => "", "comment" => "A unique identifier for this photo"],
"resource-id" => ["type" => "char(32)", "not null" => "1", "default" => "", "comment" => ""],
"comment" => "post relation to categories",
"fields" => [
"uri-id" => ["type" => "int unsigned", "not null" => "1", "primary" => "1", "foreign" => ["item-uri" => "id"], "comment" => "Id of the item-uri table entry that contains the item uri"],
- "uid" => ["type" => "mediumint unsigned", "not null" => "1", "default" => "0", "primary" => "1", "relation" => ["user" => "uid"], "comment" => "User id"],
+ "uid" => ["type" => "mediumint unsigned", "not null" => "1", "default" => "0", "primary" => "1", "foreign" => ["user" => "uid"], "comment" => "User id"],
"type" => ["type" => "tinyint unsigned", "not null" => "1", "default" => "0", "primary" => "1", "comment" => ""],
"tid" => ["type" => "int unsigned", "not null" => "1", "default" => "0", "primary" => "1", "foreign" => ["tag" => "id", "on delete" => "restrict"], "comment" => ""],
],
"indexes" => [
"PRIMARY" => ["uri-id", "uid", "type", "tid"],
- "uri-id" => ["tid"]
+ "uri-id" => ["tid"],
+ "uid" => ["uid"],
]
],
"post-delivery-data" => [
"cid" => ["cid"]
]
],
+ "post-user" => [
+ "comment" => "User specific post data",
+ "fields" => [
+ "uri-id" => ["type" => "int unsigned", "not null" => "1", "primary" => "1", "foreign" => ["item-uri" => "id"], "comment" => "Id of the item-uri table entry that contains the item uri"],
+ "uid" => ["type" => "mediumint unsigned", "not null" => "1", "primary" => "1", "foreign" => ["user" => "uid"], "comment" => "Owner id which owns this copy of the item"],
+ "contact-id" => ["type" => "int unsigned", "not null" => "1", "default" => "0", "foreign" => ["contact" => "id"], "comment" => "contact.id"],
+ "unseen" => ["type" => "boolean", "not null" => "1", "default" => "1", "comment" => "post has not been seen"],
+ "hidden" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "Marker to hide the post from the user"],
+ "notification-type" => ["type" => "tinyint unsigned", "not null" => "1", "default" => "0", "comment" => ""],
+ "origin" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "item originated at this site"],
+ "psid" => ["type" => "int unsigned", "foreign" => ["permissionset" => "id", "on delete" => "restrict"], "comment" => "ID of the permission set of this post"],
+ ],
+ "indexes" => [
+ "PRIMARY" => ["uid", "uri-id"],
+ "uri-id" => ["uri-id"],
+ "contact-id" => ["contact-id"],
+ "psid" => ["psid"],
+ ],
+ ],
"process" => [
"comment" => "Currently running system processes",
"fields" => [
"iid" => ["type" => "int unsigned", "not null" => "1", "default" => "0", "primary" => "1", "foreign" => ["item" => "id"],
"comment" => "sequential ID"],
"uri-id" => ["type" => "int unsigned", "foreign" => ["item-uri" => "id"], "comment" => "Id of the item-uri table entry that contains the item uri"],
- "uid" => ["type" => "mediumint unsigned", "not null" => "1", "default" => "0", "relation" => ["user" => "uid"], "comment" => "User id"],
+ "uid" => ["type" => "mediumint unsigned", "not null" => "1", "default" => "0", "foreign" => ["user" => "uid"], "comment" => "User id"],
"contact-id" => ["type" => "int unsigned", "not null" => "1", "default" => "0", "relation" => ["contact" => "id"], "comment" => ""],
"owner-id" => ["type" => "int unsigned", "not null" => "1", "default" => "0", "relation" => ["contact" => "id"], "comment" => "Item owner"],
"author-id" => ["type" => "int unsigned", "not null" => "1", "default" => "0", "relation" => ["contact" => "id"], "comment" => "Item author"],