* {"default" => "<default value>",}
* {"default" => NULL_DATE,} (for datetime fields)
* {"primary" => "1",}
- * {"relation" => ["<foreign key table name>" => "<foreign key field name>"],}
+ * {"foreign|relation" => ["<foreign key table name>" => "<foreign key field name>"],}
* "comment" => "Description of the fields"
* ],
* ...
* ],
* ],
*
+ * Whenever possible prefer "foreign" before "relation" with the foreign keys.
+ * "foreign" adds true foreign keys on the database level, while "relation" simulates this behaviour.
+ *
* If you need to make any change, make sure to increment the DB_UPDATE_VERSION constant value below.
*
*/
use Friendica\Database\DBA;
if (!defined('DB_UPDATE_VERSION')) {
- define('DB_UPDATE_VERSION', 1338);
+ define('DB_UPDATE_VERSION', 1348);
}
return [
"comment" => "OAuth usage",
"fields" => [
"id" => ["type" => "varchar(40)", "not null" => "1", "primary" => "1", "comment" => ""],
- "client_id" => ["type" => "varchar(20)", "not null" => "1", "default" => "", "relation" => ["clients" => "client_id"],
+ "client_id" => ["type" => "varchar(20)", "not null" => "1", "default" => "", "foreign" => ["clients" => "client_id"],
"comment" => ""],
"redirect_uri" => ["type" => "varchar(200)", "not null" => "1", "default" => "", "comment" => ""],
"expires" => ["type" => "int", "not null" => "1", "default" => "0", "comment" => ""],
],
"indexes" => [
"PRIMARY" => ["id"],
+ "client_id" => ["client_id"]
]
],
"cache" => [
"diaspora-interaction" => [
"comment" => "Signed Diaspora Interaction",
"fields" => [
- "uri-id" => ["type" => "int unsigned", "not null" => "1", "primary" => "1", "relation" => ["item-uri" => "id"], "comment" => "Id of the item-uri table entry that contains the item uri"],
+ "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"],
"interaction" => ["type" => "mediumtext", "comment" => "The Diaspora interaction"]
],
"indexes" => [
"id" => ["type" => "int unsigned", "not null" => "1", "extra" => "auto_increment", "primary" => "1", "relation" => ["thread" => "iid"]],
"guid" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => "A unique identifier for this item"],
"uri" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => ""],
- "uri-id" => ["type" => "int unsigned", "relation" => ["item-uri" => "id"], "comment" => "Id of the item-uri table entry that contains the item uri"],
+ "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", "relation" => ["item-uri" => "id"], "comment" => "Id of the item-uri table that contains the parent uri"],
+ "parent-uri-id" => ["type" => "int unsigned", "foreign" => ["item-uri" => "id"], "comment" => "Id of the item-uri table that contains the 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", "relation" => ["item-uri" => "id"], "comment" => "Id of the item-uri table that contains the thread 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."],
"edited" => ["type" => "datetime", "not null" => "1", "default" => DBA::NULL_DATETIME, "comment" => "Date of last edit (default is created)"],
"commented" => ["type" => "datetime", "not null" => "1", "default" => DBA::NULL_DATETIME, "comment" => "Date of last comment/reply to this item"],
"author-id" => ["type" => "int unsigned", "not null" => "1", "default" => "0", "relation" => ["contact" => "id"], "comment" => "Link to the contact table with uid=0 of the author of this item"],
"icid" => ["type" => "int unsigned", "relation" => ["item-content" => "id"], "comment" => "Id of the item-content table entry that contains the whole item content"],
"iaid" => ["type" => "int unsigned", "relation" => ["item-activity" => "id"], "comment" => "Id of the item-activity table entry that contains the activity data"],
+ "vid" => ["type" => "smallint unsigned", "relation" => ["verb" => "id"], "comment" => "Id of the verb table entry that contains the activity verbs"],
"extid" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => ""],
"post-type" => ["type" => "tinyint unsigned", "not null" => "1", "default" => "0", "comment" => "Post type (personal note, bookmark, ...)"],
"global" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => ""],
"unseen" => ["type" => "boolean", "not null" => "1", "default" => "1", "comment" => "item has not been seen"],
"mention" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "The owner of this item was mentioned in it"],
"forum_mode" => ["type" => "tinyint unsigned", "not null" => "1", "default" => "0", "comment" => ""],
- "psid" => ["type" => "int unsigned", "relation" => ["permissionset" => "id"], "comment" => "ID of the permission set of this post"],
+ "psid" => ["type" => "int unsigned", "foreign" => ["permissionset" => "id", "on delete" => "restrict"], "comment" => "ID of the permission set of this post"],
// 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"],
"icid" => ["icid"],
"iaid" => ["iaid"],
"psid_wall" => ["psid", "wall"],
+ "uri-id" => ["uri-id"],
+ "parent-uri-id" => ["parent-uri-id"],
+ "thr-parent-id" => ["thr-parent-id"],
]
],
"item-activity" => [
"fields" => [
"id" => ["type" => "int unsigned", "not null" => "1", "extra" => "auto_increment", "primary" => "1"],
"uri" => ["type" => "varchar(255)", "comment" => ""],
- "uri-id" => ["type" => "int unsigned", "relation" => ["item-uri" => "id"], "comment" => "Id of the item-uri table entry that contains the item uri"],
+ "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"],
"activity" => ["type" => "smallint unsigned", "not null" => "1", "default" => "0", "comment" => ""]
],
"fields" => [
"id" => ["type" => "int unsigned", "not null" => "1", "extra" => "auto_increment", "primary" => "1"],
"uri" => ["type" => "varchar(255)", "comment" => ""],
- "uri-id" => ["type" => "int unsigned", "relation" => ["item-uri" => "id"], "comment" => "Id of the item-uri table entry that contains the item uri"],
+ "uri-id" => ["type" => "int unsigned", "foreign" => ["item-uri" => "id"], "comment" => "Id of the item-uri table entry that contains the item uri"],
"uri-plink-hash" => ["type" => "varchar(80)", "not null" => "1", "default" => "", "comment" => "RIPEMD-128 hash from uri"],
"title" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => "item title"],
"content-warning" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => ""],
"uri-id" => ["uri-id"]
]
],
- "item-delivery-data" => [
- "comment" => "Delivery data for items",
- "fields" => [
- "iid" => ["type" => "int unsigned", "not null" => "1", "primary" => "1", "relation" => ["item" => "id"], "comment" => "Item id"],
- "postopts" => ["type" => "text", "comment" => "External post connectors add their network name to this comma-separated string to identify that they should be delivered to these networks during delivery"],
- "inform" => ["type" => "mediumtext", "comment" => "Additional receivers of the linked item"],
- "queue_count" => ["type" => "mediumint", "not null" => "1", "default" => "0", "comment" => "Initial number of delivery recipients, used as item.delivery_queue_count"],
- "queue_done" => ["type" => "mediumint", "not null" => "1", "default" => "0", "comment" => "Number of successful deliveries, used as item.delivery_queue_done"],
- "queue_failed" => ["type" => "mediumint", "not null" => "1", "default" => "0", "comment" => "Number of unsuccessful deliveries, used as item.delivery_queue_failed"],
- "activitypub" => ["type" => "mediumint", "not null" => "1", "default" => "0", "comment" => "Number of successful deliveries via ActivityPub"],
- "dfrn" => ["type" => "mediumint", "not null" => "1", "default" => "0", "comment" => "Number of successful deliveries via DFRN"],
- "legacy_dfrn" => ["type" => "mediumint", "not null" => "1", "default" => "0", "comment" => "Number of successful deliveries via legacy DFRN"],
- "diaspora" => ["type" => "mediumint", "not null" => "1", "default" => "0", "comment" => "Number of successful deliveries via Diaspora"],
- "ostatus" => ["type" => "mediumint", "not null" => "1", "default" => "0", "comment" => "Number of successful deliveries via OStatus"],
- ],
- "indexes" => [
- "PRIMARY" => ["iid"],
- ]
- ],
"item-uri" => [
"comment" => "URI and GUID for items",
"fields" => [
"link" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => ""],
"iid" => ["type" => "int unsigned", "not null" => "1", "default" => "0", "relation" => ["item" => "id"], "comment" => "item.id"],
"parent" => ["type" => "int unsigned", "not null" => "1", "default" => "0", "relation" => ["item" => "id"], "comment" => ""],
+ "uri-id" => ["type" => "int unsigned", "relation" => ["item-uri" => "id"], "comment" => "Item-uri id of the related post"],
+ "parent-uri-id" => ["type" => "int unsigned", "relation" => ["item-uri" => "id"], "comment" => "Item-uri id of the parent of the related post"],
"seen" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => ""],
"verb" => ["type" => "varchar(100)", "not null" => "1", "default" => "", "comment" => ""],
"otype" => ["type" => "varchar(10)", "not null" => "1", "default" => "", "comment" => ""],
"fields" => [
"id" => ["type" => "int unsigned", "not null" => "1", "extra" => "auto_increment", "primary" => "1", "comment" => "sequential ID"],
"notify-id" => ["type" => "int unsigned", "not null" => "1", "default" => "0", "relation" => ["notify" => "id"], "comment" => ""],
- "master-parent-item" => ["type" => "int unsigned", "not null" => "1", "default" => "0", "relation" => ["item" => "id"],
- "comment" => ""],
+ "master-parent-item" => ["type" => "int unsigned", "not null" => "1", "default" => "0", "relation" => ["item" => "id"], "comment" => ""],
+ "master-parent-uri-id" => ["type" => "int unsigned", "relation" => ["item-uri" => "id"], "comment" => "Item-uri id of the parent of the related post"],
"parent-item" => ["type" => "int unsigned", "not null" => "1", "default" => "0", "comment" => ""],
"receiver-uid" => ["type" => "mediumint unsigned", "not null" => "1", "default" => "0", "relation" => ["user" => "uid"],
"comment" => "User id"],
],
"indexes" => [
"PRIMARY" => ["id"],
+ "master-parent-uri-id" => ["master-parent-uri-id"],
]
],
"oembed" => [
"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"],
"order" => ["type" => "mediumint unsigned", "not null" => "1", "default" => "1", "comment" => "Field ordering per user"],
- "psid" => ["type" => "int unsigned", "relation" => ["permissionset" => "id"], "comment" => "ID of the permission set of this profile field - 0 = public"],
+ "psid" => ["type" => "int unsigned", "foreign" => ["permissionset" => "id", "on delete" => "restrict"], "comment" => "ID of the permission set of this profile field - 0 = public"],
"label" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => "Label of the field"],
"value" => ["type" => "text", "comment" => "Value of the field"],
"created" => ["type" => "datetime", "not null" => "1", "default" => DBA::NULL_DATETIME, "comment" => "creation time"],
"expire" => ["expire"],
]
],
- "term" => [
- "comment" => "item taxonomy (categories, tags, etc.) table",
+ "tag" => [
+ "comment" => "tags and mentions",
"fields" => [
- "tid" => ["type" => "int unsigned", "not null" => "1", "extra" => "auto_increment", "primary" => "1", "comment" => ""],
- "oid" => ["type" => "int unsigned", "not null" => "1", "default" => "0", "relation" => ["item" => "id"], "comment" => ""],
- "otype" => ["type" => "tinyint unsigned", "not null" => "1", "default" => "0", "comment" => ""],
- "type" => ["type" => "tinyint unsigned", "not null" => "1", "default" => "0", "comment" => ""],
- "term" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => ""],
- "url" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => ""],
- "guid" => ["type" => "varchar(255)", "not null" => "1", "default" => "", "comment" => ""],
- "created" => ["type" => "datetime", "not null" => "1", "default" => DBA::NULL_DATETIME, "comment" => ""],
- "received" => ["type" => "datetime", "not null" => "1", "default" => DBA::NULL_DATETIME, "comment" => ""],
- "global" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => ""],
- "uid" => ["type" => "mediumint unsigned", "not null" => "1", "default" => "0", "relation" => ["user" => "uid"], "comment" => "User id"],
+ "id" => ["type" => "int unsigned", "not null" => "1", "extra" => "auto_increment", "primary" => "1", "comment" => ""],
+ "name" => ["type" => "varchar(96)", "not null" => "1", "default" => "", "comment" => ""],
+ "url" => ["type" => "varbinary(255)", "not null" => "1", "default" => "", "comment" => ""]
],
"indexes" => [
- "PRIMARY" => ["tid"],
- "term_type" => ["term(64)", "type"],
- "oid_otype_type_term" => ["oid", "otype", "type", "term(32)"],
- "uid_otype_type_term_global_created" => ["uid", "otype", "type", "term(32)", "global", "created"],
- "uid_otype_type_url" => ["uid", "otype", "type", "url(64)"],
- "guid" => ["guid(64)"],
+ "PRIMARY" => ["id"],
+ "type_name_url" => ["UNIQUE", "name", "url"],
+ "url" => ["url"]
+ ]
+ ],
+ "post-category" => [
+ "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"],
+ "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"]
+ ]
+ ],
+ "post-delivery-data" => [
+ "comment" => "Delivery data for items",
+ "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"],
+ "postopts" => ["type" => "text", "comment" => "External post connectors add their network name to this comma-separated string to identify that they should be delivered to these networks during delivery"],
+ "inform" => ["type" => "mediumtext", "comment" => "Additional receivers of the linked item"],
+ "queue_count" => ["type" => "mediumint", "not null" => "1", "default" => "0", "comment" => "Initial number of delivery recipients, used as item.delivery_queue_count"],
+ "queue_done" => ["type" => "mediumint", "not null" => "1", "default" => "0", "comment" => "Number of successful deliveries, used as item.delivery_queue_done"],
+ "queue_failed" => ["type" => "mediumint", "not null" => "1", "default" => "0", "comment" => "Number of unsuccessful deliveries, used as item.delivery_queue_failed"],
+ "activitypub" => ["type" => "mediumint", "not null" => "1", "default" => "0", "comment" => "Number of successful deliveries via ActivityPub"],
+ "dfrn" => ["type" => "mediumint", "not null" => "1", "default" => "0", "comment" => "Number of successful deliveries via DFRN"],
+ "legacy_dfrn" => ["type" => "mediumint", "not null" => "1", "default" => "0", "comment" => "Number of successful deliveries via legacy DFRN"],
+ "diaspora" => ["type" => "mediumint", "not null" => "1", "default" => "0", "comment" => "Number of successful deliveries via Diaspora"],
+ "ostatus" => ["type" => "mediumint", "not null" => "1", "default" => "0", "comment" => "Number of successful deliveries via OStatus"],
+ ],
+ "indexes" => [
+ "PRIMARY" => ["uri-id"],
+ ]
+ ],
+ "post-tag" => [
+ "comment" => "post relation to tags",
+ "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"],
+ "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" => ""],
+ "cid" => ["type" => "int unsigned", "not null" => "1", "default" => "0", "primary" => "1", "foreign" => ["contact" => "id", "on delete" => "restrict"], "comment" => "Contact id of the mentioned public contact"],
+ ],
+ "indexes" => [
+ "PRIMARY" => ["uri-id", "type", "tid", "cid"],
+ "tid" => ["tid"],
+ "cid" => ["cid"]
]
],
"thread" => [
"fields" => [
"id" => ["type" => "varchar(40)", "not null" => "1", "primary" => "1", "comment" => ""],
"secret" => ["type" => "text", "comment" => ""],
- "client_id" => ["type" => "varchar(20)", "not null" => "1", "default" => "", "relation" => ["clients" => "client_id"]],
+ "client_id" => ["type" => "varchar(20)", "not null" => "1", "default" => "", "foreign" => ["clients" => "client_id"]],
"expires" => ["type" => "int", "not null" => "1", "default" => "0", "comment" => ""],
"scope" => ["type" => "varchar(200)", "not null" => "1", "default" => "", "comment" => ""],
"uid" => ["type" => "mediumint unsigned", "not null" => "1", "default" => "0", "relation" => ["user" => "uid"], "comment" => "User id"],
],
"indexes" => [
"PRIMARY" => ["id"],
+ "client_id" => ["client_id"]
]
],
"user" => [
"iid_uid" => ["iid", "uid"]
]
],
+ "verb" => [
+ "comment" => "Activity Verbs",
+ "fields" => [
+ "id" => ["type" => "smallint unsigned", "not null" => "1", "extra" => "auto_increment", "primary" => "1"],
+ "name" => ["type" => "varchar(100)", "not null" => "1", "default" => "", "comment" => ""]
+ ],
+ "indexes" => [
+ "PRIMARY" => ["id"]
+ ]
+ ],
"worker-ipc" => [
"comment" => "Inter process communication between the frontend and the worker",
"fields" => [