Friendica Communications Platform (please note that this is a clone of the repository at github, issues are handled there) https://friendi.ca
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

2610 lines
124 KiB

4 years ago
4 years ago
11 months ago
11 months ago
11 months ago
4 years ago
4 years ago
4 years ago
4 years ago
5 months ago
5 months ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
11 months ago
11 months ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
8 months ago
8 months ago
8 months ago
8 months ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
5 months ago
6 months ago
4 months ago
  1. -- ------------------------------------------
  2. -- Friendica 2021.12-dev (Siberian Iris)
  3. -- DB_UPDATE_VERSION 1442
  4. -- ------------------------------------------
  5. --
  6. -- TABLE gserver
  7. --
  8. CREATE TABLE IF NOT EXISTS `gserver` (
  9. `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
  10. `url` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  11. `nurl` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  12. `version` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  13. `site_name` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  14. `info` text COMMENT '',
  15. `register_policy` tinyint NOT NULL DEFAULT 0 COMMENT '',
  16. `registered-users` int unsigned NOT NULL DEFAULT 0 COMMENT 'Number of registered users',
  17. `directory-type` tinyint DEFAULT 0 COMMENT 'Type of directory service (Poco, Mastodon)',
  18. `poco` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  19. `noscrape` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  20. `network` char(4) NOT NULL DEFAULT '' COMMENT '',
  21. `protocol` tinyint unsigned COMMENT 'The protocol of the server',
  22. `platform` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  23. `relay-subscribe` boolean NOT NULL DEFAULT '0' COMMENT 'Has the server subscribed to the relay system',
  24. `relay-scope` varchar(10) NOT NULL DEFAULT '' COMMENT 'The scope of messages that the server wants to get',
  25. `detection-method` tinyint unsigned COMMENT 'Method that had been used to detect that server',
  26. `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
  27. `last_poco_query` datetime DEFAULT '0001-01-01 00:00:00' COMMENT '',
  28. `last_contact` datetime DEFAULT '0001-01-01 00:00:00' COMMENT 'Last successful connection request',
  29. `last_failure` datetime DEFAULT '0001-01-01 00:00:00' COMMENT 'Last failed connection request',
  30. `failed` boolean COMMENT 'Connection failed',
  31. `next_contact` datetime DEFAULT '0001-01-01 00:00:00' COMMENT 'Next connection request',
  32. PRIMARY KEY(`id`),
  33. UNIQUE INDEX `nurl` (`nurl`(190)),
  34. INDEX `next_contact` (`next_contact`),
  35. INDEX `network` (`network`)
  36. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Global servers';
  37. --
  38. -- TABLE user
  39. --
  40. CREATE TABLE IF NOT EXISTS `user` (
  41. `uid` mediumint unsigned NOT NULL auto_increment COMMENT 'sequential ID',
  42. `parent-uid` mediumint unsigned COMMENT 'The parent user that has full control about this user',
  43. `guid` varchar(64) NOT NULL DEFAULT '' COMMENT 'A unique identifier for this user',
  44. `username` varchar(255) NOT NULL DEFAULT '' COMMENT 'Name that this user is known by',
  45. `password` varchar(255) NOT NULL DEFAULT '' COMMENT 'encrypted password',
  46. `legacy_password` boolean NOT NULL DEFAULT '0' COMMENT 'Is the password hash double-hashed?',
  47. `nickname` varchar(255) NOT NULL DEFAULT '' COMMENT 'nick- and user name',
  48. `email` varchar(255) NOT NULL DEFAULT '' COMMENT 'the users email address',
  49. `openid` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  50. `timezone` varchar(128) NOT NULL DEFAULT '' COMMENT 'PHP-legal timezone',
  51. `language` varchar(32) NOT NULL DEFAULT 'en' COMMENT 'default language',
  52. `register_date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'timestamp of registration',
  53. `login_date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'timestamp of last login',
  54. `default-location` varchar(255) NOT NULL DEFAULT '' COMMENT 'Default for item.location',
  55. `allow_location` boolean NOT NULL DEFAULT '0' COMMENT '1 allows to display the location',
  56. `theme` varchar(255) NOT NULL DEFAULT '' COMMENT 'user theme preference',
  57. `pubkey` text COMMENT 'RSA public key 4096 bit',
  58. `prvkey` text COMMENT 'RSA private key 4096 bit',
  59. `spubkey` text COMMENT '',
  60. `sprvkey` text COMMENT '',
  61. `verified` boolean NOT NULL DEFAULT '0' COMMENT 'user is verified through email',
  62. `blocked` boolean NOT NULL DEFAULT '0' COMMENT '1 for user is blocked',
  63. `blockwall` boolean NOT NULL DEFAULT '0' COMMENT 'Prohibit contacts to post to the profile page of the user',
  64. `hidewall` boolean NOT NULL DEFAULT '0' COMMENT 'Hide profile details from unkown viewers',
  65. `blocktags` boolean NOT NULL DEFAULT '0' COMMENT 'Prohibit contacts to tag the post of this user',
  66. `unkmail` boolean NOT NULL DEFAULT '0' COMMENT 'Permit unknown people to send private mails to this user',
  67. `cntunkmail` int unsigned NOT NULL DEFAULT 10 COMMENT '',
  68. `notify-flags` smallint unsigned NOT NULL DEFAULT 65535 COMMENT 'email notification options',
  69. `page-flags` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'page/profile type',
  70. `account-type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '',
  71. `prvnets` boolean NOT NULL DEFAULT '0' COMMENT '',
  72. `pwdreset` varchar(255) COMMENT 'Password reset request token',
  73. `pwdreset_time` datetime COMMENT 'Timestamp of the last password reset request',
  74. `maxreq` int unsigned NOT NULL DEFAULT 10 COMMENT '',
  75. `expire` int unsigned NOT NULL DEFAULT 0 COMMENT '',
  76. `account_removed` boolean NOT NULL DEFAULT '0' COMMENT 'if 1 the account is removed',
  77. `account_expired` boolean NOT NULL DEFAULT '0' COMMENT '',
  78. `account_expires_on` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'timestamp when account expires and will be deleted',
  79. `expire_notification_sent` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'timestamp of last warning of account expiration',
  80. `def_gid` int unsigned NOT NULL DEFAULT 0 COMMENT '',
  81. `allow_cid` mediumtext COMMENT 'default permission for this user',
  82. `allow_gid` mediumtext COMMENT 'default permission for this user',
  83. `deny_cid` mediumtext COMMENT 'default permission for this user',
  84. `deny_gid` mediumtext COMMENT 'default permission for this user',
  85. `openidserver` text COMMENT '',
  86. PRIMARY KEY(`uid`),
  87. INDEX `nickname` (`nickname`(32)),
  88. INDEX `parent-uid` (`parent-uid`),
  89. INDEX `guid` (`guid`),
  90. INDEX `email` (`email`(64)),
  91. FOREIGN KEY (`parent-uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE
  92. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='The local users';
  93. --
  94. -- TABLE item-uri
  95. --
  96. CREATE TABLE IF NOT EXISTS `item-uri` (
  97. `id` int unsigned NOT NULL auto_increment,
  98. `uri` varbinary(255) NOT NULL COMMENT 'URI of an item',
  99. `guid` varbinary(255) COMMENT 'A unique identifier for an item',
  100. PRIMARY KEY(`id`),
  101. UNIQUE INDEX `uri` (`uri`),
  102. INDEX `guid` (`guid`)
  103. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='URI and GUID for items';
  104. --
  105. -- TABLE contact
  106. --
  107. CREATE TABLE IF NOT EXISTS `contact` (
  108. `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
  109. `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id',
  110. `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
  111. `updated` datetime DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of last contact update',
  112. `network` char(4) NOT NULL DEFAULT '' COMMENT 'Network of the contact',
  113. `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'Name that this contact is known by',
  114. `nick` varchar(255) NOT NULL DEFAULT '' COMMENT 'Nick- and user name of the contact',
  115. `location` varchar(255) DEFAULT '' COMMENT '',
  116. `about` text COMMENT '',
  117. `keywords` text COMMENT 'public keywords (interests) of the contact',
  118. `xmpp` varchar(255) NOT NULL DEFAULT '' COMMENT 'XMPP address',
  119. `matrix` varchar(255) NOT NULL DEFAULT '' COMMENT 'Matrix address',
  120. `avatar` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  121. `header` varchar(255) COMMENT 'Header picture',
  122. `url` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  123. `nurl` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  124. `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the contact url',
  125. `addr` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  126. `alias` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  127. `pubkey` text COMMENT 'RSA public key 4096 bit',
  128. `prvkey` text COMMENT 'RSA private key 4096 bit',
  129. `batch` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  130. `notify` varchar(255) COMMENT '',
  131. `poll` varchar(255) COMMENT '',
  132. `subscribe` varchar(255) COMMENT '',
  133. `last-update` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last try to update the contact info',
  134. `success_update` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last successful contact update',
  135. `failure_update` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last failed update',
  136. `failed` boolean COMMENT 'Connection failed',
  137. `term-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
  138. `last-item` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'date of the last post',
  139. `last-discovery` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'date of the last follower discovery',
  140. `blocked` boolean NOT NULL DEFAULT '1' COMMENT 'Node-wide block status',
  141. `block_reason` text COMMENT 'Node-wide block reason',
  142. `readonly` boolean NOT NULL DEFAULT '0' COMMENT 'posts of the contact are readonly',
  143. `contact-type` tinyint NOT NULL DEFAULT 0 COMMENT 'Person, organisation, news, community, relay',
  144. `manually-approve` boolean COMMENT 'Contact requests have to be approved manually',
  145. `archive` boolean NOT NULL DEFAULT '0' COMMENT '',
  146. `unsearchable` boolean NOT NULL DEFAULT '0' COMMENT 'Contact prefers to not be searchable',
  147. `sensitive` boolean NOT NULL DEFAULT '0' COMMENT 'Contact posts sensitive content',
  148. `baseurl` varchar(255) DEFAULT '' COMMENT 'baseurl of the contact',
  149. `gsid` int unsigned COMMENT 'Global Server ID',
  150. `bd` date NOT NULL DEFAULT '0001-01-01' COMMENT '',
  151. `reason` text COMMENT '',
  152. `self` boolean NOT NULL DEFAULT '0' COMMENT '1 if the contact is the user him/her self',
  153. `remote_self` boolean NOT NULL DEFAULT '0' COMMENT '',
  154. `rel` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'The kind of the relation between the user and the contact',
  155. `protocol` char(4) NOT NULL DEFAULT '' COMMENT 'Protocol of the contact',
  156. `subhub` boolean NOT NULL DEFAULT '0' COMMENT '',
  157. `hub-verify` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  158. `rating` tinyint NOT NULL DEFAULT 0 COMMENT 'Automatically detected feed poll frequency',
  159. `priority` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Feed poll priority',
  160. `attag` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  161. `hidden` boolean NOT NULL DEFAULT '0' COMMENT '',
  162. `pending` boolean NOT NULL DEFAULT '1' COMMENT 'Contact request is pending',
  163. `deleted` boolean NOT NULL DEFAULT '0' COMMENT 'Contact has been deleted',
  164. `info` mediumtext COMMENT '',
  165. `notify_new_posts` boolean NOT NULL DEFAULT '0' COMMENT '',
  166. `fetch_further_information` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '',
  167. `ffi_keyword_denylist` text COMMENT '',
  168. `photo` varchar(255) DEFAULT '' COMMENT 'Link to the profile photo of the contact',
  169. `thumb` varchar(255) DEFAULT '' COMMENT 'Link to the profile photo (thumb size)',
  170. `micro` varchar(255) DEFAULT '' COMMENT 'Link to the profile photo (micro size)',
  171. `name-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
  172. `uri-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
  173. `avatar-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
  174. `request` varchar(255) COMMENT '',
  175. `confirm` varchar(255) COMMENT '',
  176. `poco` varchar(255) COMMENT '',
  177. `writable` boolean NOT NULL DEFAULT '0' COMMENT '',
  178. `forum` boolean NOT NULL DEFAULT '0' COMMENT 'contact is a forum. Deprecated, use \'contact-type\' = \'community\' and \'manually-approve\' = false instead',
  179. `prv` boolean NOT NULL DEFAULT '0' COMMENT 'contact is a private group. Deprecated, use \'contact-type\' = \'community\' and \'manually-approve\' = true instead',
  180. `bdyear` varchar(4) NOT NULL DEFAULT '' COMMENT '',
  181. `site-pubkey` text COMMENT 'Deprecated',
  182. `gender` varchar(32) NOT NULL DEFAULT '' COMMENT 'Deprecated',
  183. `duplex` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated',
  184. `issued-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated',
  185. `dfrn-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated',
  186. `aes_allow` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated',
  187. `ret-aes` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated',
  188. `usehub` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated',
  189. `closeness` tinyint unsigned NOT NULL DEFAULT 99 COMMENT 'Deprecated',
  190. `profile-id` int unsigned COMMENT 'Deprecated',
  191. PRIMARY KEY(`id`),
  192. INDEX `uid_name` (`uid`,`name`(190)),
  193. INDEX `self_uid` (`self`,`uid`),
  194. INDEX `alias_uid` (`alias`(128),`uid`),
  195. INDEX `pending_uid` (`pending`,`uid`),
  196. INDEX `blocked_uid` (`blocked`,`uid`),
  197. INDEX `uid_rel_network_poll` (`uid`,`rel`,`network`,`poll`(64),`archive`),
  198. INDEX `uid_network_batch` (`uid`,`network`,`batch`(64)),
  199. INDEX `batch_contact-type` (`batch`(64),`contact-type`),
  200. INDEX `addr_uid` (`addr`(128),`uid`),
  201. INDEX `nurl_uid` (`nurl`(128),`uid`),
  202. INDEX `nick_uid` (`nick`(128),`uid`),
  203. INDEX `attag_uid` (`attag`(96),`uid`),
  204. INDEX `network_uid_lastupdate` (`network`,`uid`,`last-update`),
  205. INDEX `uid_network_self_lastupdate` (`uid`,`network`,`self`,`last-update`),
  206. INDEX `uid_lastitem` (`uid`,`last-item`),
  207. INDEX `baseurl` (`baseurl`(64)),
  208. INDEX `uid_contact-type` (`uid`,`contact-type`),
  209. INDEX `uid_self_contact-type` (`uid`,`self`,`contact-type`),
  210. INDEX `self_network_uid` (`self`,`network`,`uid`),
  211. INDEX `gsid` (`gsid`),
  212. INDEX `uri-id` (`uri-id`),
  213. FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE,
  214. FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
  215. FOREIGN KEY (`gsid`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
  216. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='contact table';
  217. --
  218. -- TABLE tag
  219. --
  220. CREATE TABLE IF NOT EXISTS `tag` (
  221. `id` int unsigned NOT NULL auto_increment COMMENT '',
  222. `name` varchar(96) NOT NULL DEFAULT '' COMMENT '',
  223. `url` varbinary(255) NOT NULL DEFAULT '' COMMENT '',
  224. PRIMARY KEY(`id`),
  225. UNIQUE INDEX `type_name_url` (`name`,`url`),
  226. INDEX `url` (`url`)
  227. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='tags and mentions';
  228. --
  229. -- TABLE permissionset
  230. --
  231. CREATE TABLE IF NOT EXISTS `permissionset` (
  232. `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
  233. `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner id of this permission set',
  234. `allow_cid` mediumtext COMMENT 'Access Control - list of allowed contact.id \'<19><78>\'',
  235. `allow_gid` mediumtext COMMENT 'Access Control - list of allowed groups',
  236. `deny_cid` mediumtext COMMENT 'Access Control - list of denied contact.id',
  237. `deny_gid` mediumtext COMMENT 'Access Control - list of denied groups',
  238. PRIMARY KEY(`id`),
  239. INDEX `uid_allow_cid_allow_gid_deny_cid_deny_gid` (`uid`,`allow_cid`(50),`allow_gid`(30),`deny_cid`(50),`deny_gid`(30)),
  240. FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE
  241. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='';
  242. --
  243. -- TABLE verb
  244. --
  245. CREATE TABLE IF NOT EXISTS `verb` (
  246. `id` smallint unsigned NOT NULL auto_increment,
  247. `name` varchar(100) NOT NULL DEFAULT '' COMMENT '',
  248. PRIMARY KEY(`id`),
  249. INDEX `name` (`name`)
  250. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Activity Verbs';
  251. --
  252. -- TABLE 2fa_app_specific_password
  253. --
  254. CREATE TABLE IF NOT EXISTS `2fa_app_specific_password` (
  255. `id` mediumint unsigned NOT NULL auto_increment COMMENT 'Password ID for revocation',
  256. `uid` mediumint unsigned NOT NULL COMMENT 'User ID',
  257. `description` varchar(255) COMMENT 'Description of the usage of the password',
  258. `hashed_password` varchar(255) NOT NULL COMMENT 'Hashed password',
  259. `generated` datetime NOT NULL COMMENT 'Datetime the password was generated',
  260. `last_used` datetime COMMENT 'Datetime the password was last used',
  261. PRIMARY KEY(`id`),
  262. INDEX `uid_description` (`uid`,`description`(190)),
  263. FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE
  264. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Two-factor app-specific _password';
  265. --
  266. -- TABLE 2fa_recovery_codes
  267. --
  268. CREATE TABLE IF NOT EXISTS `2fa_recovery_codes` (
  269. `uid` mediumint unsigned NOT NULL COMMENT 'User ID',
  270. `code` varchar(50) NOT NULL COMMENT 'Recovery code string',
  271. `generated` datetime NOT NULL COMMENT 'Datetime the code was generated',
  272. `used` datetime COMMENT 'Datetime the code was used',
  273. PRIMARY KEY(`uid`,`code`),
  274. FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE
  275. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Two-factor authentication recovery codes';
  276. --
  277. -- TABLE 2fa_trusted_browser
  278. --
  279. CREATE TABLE IF NOT EXISTS `2fa_trusted_browser` (
  280. `cookie_hash` varchar(80) NOT NULL COMMENT 'Trusted cookie hash',
  281. `uid` mediumint unsigned NOT NULL COMMENT 'User ID',
  282. `user_agent` text COMMENT 'User agent string',
  283. `created` datetime NOT NULL COMMENT 'Datetime the trusted browser was recorded',
  284. `last_used` datetime COMMENT 'Datetime the trusted browser was last used',
  285. PRIMARY KEY(`cookie_hash`),
  286. INDEX `uid` (`uid`),
  287. FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE
  288. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Two-factor authentication trusted browsers';
  289. --
  290. -- TABLE addon
  291. --
  292. CREATE TABLE IF NOT EXISTS `addon` (
  293. `id` int unsigned NOT NULL auto_increment COMMENT '',
  294. `name` varchar(50) NOT NULL DEFAULT '' COMMENT 'addon base (file)name',
  295. `version` varchar(50) NOT NULL DEFAULT '' COMMENT 'currently unused',
  296. `installed` boolean NOT NULL DEFAULT '0' COMMENT 'currently always 1',
  297. `hidden` boolean NOT NULL DEFAULT '0' COMMENT 'currently unused',
  298. `timestamp` int unsigned NOT NULL DEFAULT 0 COMMENT 'file timestamp to check for reloads',
  299. `plugin_admin` boolean NOT NULL DEFAULT '0' COMMENT '1 = has admin config, 0 = has no admin config',
  300. PRIMARY KEY(`id`),
  301. INDEX `installed_name` (`installed`,`name`),
  302. UNIQUE INDEX `name` (`name`)
  303. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='registered addons';
  304. --
  305. -- TABLE apcontact
  306. --
  307. CREATE TABLE IF NOT EXISTS `apcontact` (
  308. `url` varbinary(255) NOT NULL COMMENT 'URL of the contact',
  309. `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the apcontact url',
  310. `uuid` varchar(255) COMMENT '',
  311. `type` varchar(20) NOT NULL COMMENT '',
  312. `following` varchar(255) COMMENT '',
  313. `followers` varchar(255) COMMENT '',
  314. `inbox` varchar(255) NOT NULL COMMENT '',
  315. `outbox` varchar(255) COMMENT '',
  316. `sharedinbox` varchar(255) COMMENT '',
  317. `manually-approve` boolean COMMENT '',
  318. `discoverable` boolean COMMENT 'Mastodon extension: true if profile is published in their directory',
  319. `nick` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  320. `name` varchar(255) COMMENT '',
  321. `about` text COMMENT '',
  322. `xmpp` varchar(255) COMMENT 'XMPP address',
  323. `matrix` varchar(255) COMMENT 'Matrix address',
  324. `photo` varchar(255) COMMENT '',
  325. `header` varchar(255) COMMENT 'Header picture',
  326. `addr` varchar(255) COMMENT '',
  327. `alias` varchar(255) COMMENT '',
  328. `pubkey` text COMMENT '',
  329. `subscribe` varchar(255) COMMENT '',
  330. `baseurl` varchar(255) COMMENT 'baseurl of the ap contact',
  331. `gsid` int unsigned COMMENT 'Global Server ID',
  332. `generator` varchar(255) COMMENT 'Name of the contact\'s system',
  333. `following_count` int unsigned DEFAULT 0 COMMENT 'Number of following contacts',
  334. `followers_count` int unsigned DEFAULT 0 COMMENT 'Number of followers',
  335. `statuses_count` int unsigned DEFAULT 0 COMMENT 'Number of posts',
  336. `updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
  337. PRIMARY KEY(`url`),
  338. INDEX `addr` (`addr`(32)),
  339. INDEX `alias` (`alias`(190)),
  340. INDEX `followers` (`followers`(190)),
  341. INDEX `baseurl` (`baseurl`(190)),
  342. INDEX `sharedinbox` (`sharedinbox`(190)),
  343. INDEX `gsid` (`gsid`),
  344. UNIQUE INDEX `uri-id` (`uri-id`),
  345. FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
  346. FOREIGN KEY (`gsid`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
  347. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='ActivityPub compatible contacts - used in the ActivityPub implementation';
  348. --
  349. -- TABLE application
  350. --
  351. CREATE TABLE IF NOT EXISTS `application` (
  352. `id` int unsigned NOT NULL auto_increment COMMENT 'generated index',
  353. `client_id` varchar(64) NOT NULL COMMENT '',
  354. `client_secret` varchar(64) NOT NULL COMMENT '',
  355. `name` varchar(255) NOT NULL COMMENT '',
  356. `redirect_uri` varchar(255) NOT NULL COMMENT '',
  357. `website` varchar(255) COMMENT '',
  358. `scopes` varchar(255) COMMENT '',
  359. `read` boolean COMMENT 'Read scope',
  360. `write` boolean COMMENT 'Write scope',
  361. `follow` boolean COMMENT 'Follow scope',
  362. `push` boolean COMMENT 'Push scope',
  363. PRIMARY KEY(`id`),
  364. UNIQUE INDEX `client_id` (`client_id`)
  365. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='OAuth application';
  366. --
  367. -- TABLE application-token
  368. --
  369. CREATE TABLE IF NOT EXISTS `application-token` (
  370. `application-id` int unsigned NOT NULL COMMENT '',
  371. `uid` mediumint unsigned NOT NULL COMMENT 'Owner User id',
  372. `code` varchar(64) NOT NULL COMMENT '',
  373. `access_token` varchar(64) NOT NULL COMMENT '',
  374. `created_at` datetime NOT NULL COMMENT 'creation time',
  375. `scopes` varchar(255) COMMENT '',
  376. `read` boolean COMMENT 'Read scope',
  377. `write` boolean COMMENT 'Write scope',
  378. `follow` boolean COMMENT 'Follow scope',
  379. `push` boolean COMMENT 'Push scope',
  380. PRIMARY KEY(`application-id`,`uid`),
  381. INDEX `uid_id` (`uid`,`application-id`),
  382. FOREIGN KEY (`application-id`) REFERENCES `application` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
  383. FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE
  384. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='OAuth user token';
  385. --
  386. -- TABLE attach
  387. --
  388. CREATE TABLE IF NOT EXISTS `attach` (
  389. `id` int unsigned NOT NULL auto_increment COMMENT 'generated index',
  390. `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id',
  391. `hash` varchar(64) NOT NULL DEFAULT '' COMMENT 'hash',
  392. `filename` varchar(255) NOT NULL DEFAULT '' COMMENT 'filename of original',
  393. `filetype` varchar(64) NOT NULL DEFAULT '' COMMENT 'mimetype',
  394. `filesize` int unsigned NOT NULL DEFAULT 0 COMMENT 'size in bytes',
  395. `data` longblob NOT NULL COMMENT 'file data',
  396. `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'creation time',
  397. `edited` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'last edit time',
  398. `allow_cid` mediumtext COMMENT 'Access Control - list of allowed contact.id \'<19><78>',
  399. `allow_gid` mediumtext COMMENT 'Access Control - list of allowed groups',
  400. `deny_cid` mediumtext COMMENT 'Access Control - list of denied contact.id',
  401. `deny_gid` mediumtext COMMENT 'Access Control - list of denied groups',
  402. `backend-class` tinytext COMMENT 'Storage backend class',
  403. `backend-ref` text COMMENT 'Storage backend data reference',
  404. PRIMARY KEY(`id`),
  405. INDEX `uid` (`uid`),
  406. FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE
  407. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='file attachments';
  408. --
  409. -- TABLE cache
  410. --
  411. CREATE TABLE IF NOT EXISTS `cache` (
  412. `k` varbinary(255) NOT NULL COMMENT 'cache key',
  413. `v` mediumtext COMMENT 'cached serialized value',
  414. `expires` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'datetime of cache expiration',
  415. `updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'datetime of cache insertion',
  416. PRIMARY KEY(`k`),
  417. INDEX `k_expires` (`k`,`expires`)
  418. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Stores temporary data';
  419. --
  420. -- TABLE config
  421. --
  422. CREATE TABLE IF NOT EXISTS `config` (
  423. `id` int unsigned NOT NULL auto_increment COMMENT '',
  424. `cat` varbinary(50) NOT NULL DEFAULT '' COMMENT '',
  425. `k` varbinary(50) NOT NULL DEFAULT '' COMMENT '',
  426. `v` mediumtext COMMENT '',
  427. PRIMARY KEY(`id`),
  428. UNIQUE INDEX `cat_k` (`cat`,`k`)
  429. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='main configuration storage';
  430. --
  431. -- TABLE contact-relation
  432. --
  433. CREATE TABLE IF NOT EXISTS `contact-relation` (
  434. `cid` int unsigned NOT NULL DEFAULT 0 COMMENT 'contact the related contact had interacted with',
  435. `relation-cid` int unsigned NOT NULL DEFAULT 0 COMMENT 'related contact who had interacted with the contact',
  436. `last-interaction` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last interaction',
  437. `follow-updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last update of the contact relationship',
  438. `follows` boolean NOT NULL DEFAULT '0' COMMENT '',
  439. PRIMARY KEY(`cid`,`relation-cid`),
  440. INDEX `relation-cid` (`relation-cid`),
  441. FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
  442. FOREIGN KEY (`relation-cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
  443. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Contact relations';
  444. --
  445. -- TABLE conv
  446. --
  447. CREATE TABLE IF NOT EXISTS `conv` (
  448. `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
  449. `guid` varchar(255) NOT NULL DEFAULT '' COMMENT 'A unique identifier for this conversation',
  450. `recips` text COMMENT 'sender_handle;recipient_handle',
  451. `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id',
  452. `creator` varchar(255) NOT NULL DEFAULT '' COMMENT 'handle of creator',
  453. `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'creation timestamp',
  454. `updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'edited timestamp',
  455. `subject` text COMMENT 'subject of initial message',
  456. PRIMARY KEY(`id`),
  457. INDEX `uid` (`uid`),
  458. FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE
  459. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='private messages';
  460. --
  461. -- TABLE conversation
  462. --
  463. CREATE TABLE IF NOT EXISTS `conversation` (
  464. `item-uri` varbinary(255) NOT NULL COMMENT 'Original URI of the item - unrelated to the table with the same name',
  465. `reply-to-uri` varbinary(255) NOT NULL DEFAULT '' COMMENT 'URI to which this item is a reply',
  466. `conversation-uri` varbinary(255) NOT NULL DEFAULT '' COMMENT 'GNU Social conversation URI',
  467. `conversation-href` varbinary(255) NOT NULL DEFAULT '' COMMENT 'GNU Social conversation link',
  468. `protocol` tinyint unsigned NOT NULL DEFAULT 255 COMMENT 'The protocol of the item',
  469. `direction` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'How the message arrived here: 1=push, 2=pull',
  470. `source` mediumtext COMMENT 'Original source',
  471. `received` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Receiving date',
  472. PRIMARY KEY(`item-uri`),
  473. INDEX `conversation-uri` (`conversation-uri`),
  474. INDEX `received` (`received`)
  475. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Raw data and structure information for messages';
  476. --
  477. -- TABLE workerqueue
  478. --
  479. CREATE TABLE IF NOT EXISTS `workerqueue` (
  480. `id` int unsigned NOT NULL auto_increment COMMENT 'Auto incremented worker task id',
  481. `command` varchar(100) COMMENT 'Task command',
  482. `parameter` mediumtext COMMENT 'Task parameter',
  483. `priority` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Task priority',
  484. `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Creation date',
  485. `pid` int unsigned NOT NULL DEFAULT 0 COMMENT 'Process id of the worker',
  486. `executed` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Execution date',
  487. `next_try` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Next retrial date',
  488. `retrial` tinyint NOT NULL DEFAULT 0 COMMENT 'Retrial counter',
  489. `done` boolean NOT NULL DEFAULT '0' COMMENT 'Marked 1 when the task was done - will be deleted later',
  490. PRIMARY KEY(`id`),
  491. INDEX `command` (`command`),
  492. INDEX `done_command_parameter` (`done`,`command`,`parameter`(64)),
  493. INDEX `done_executed` (`done`,`executed`),
  494. INDEX `done_priority_retrial_created` (`done`,`priority`,`retrial`,`created`),
  495. INDEX `done_priority_next_try` (`done`,`priority`,`next_try`),
  496. INDEX `done_pid_next_try` (`done`,`pid`,`next_try`),
  497. INDEX `done_pid_retrial` (`done`,`pid`,`retrial`),
  498. INDEX `done_pid_priority_created` (`done`,`pid`,`priority`,`created`)
  499. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Background tasks queue entries';
  500. --
  501. -- TABLE delayed-post
  502. --
  503. CREATE TABLE IF NOT EXISTS `delayed-post` (
  504. `id` int unsigned NOT NULL auto_increment,
  505. `uri` varchar(255) COMMENT 'URI of the post that will be distributed later',
  506. `uid` mediumint unsigned COMMENT 'Owner User id',
  507. `delayed` datetime COMMENT 'delay time',
  508. `wid` int unsigned COMMENT 'Workerqueue id',
  509. PRIMARY KEY(`id`),
  510. UNIQUE INDEX `uid_uri` (`uid`,`uri`(190)),
  511. INDEX `wid` (`wid`),
  512. FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE,
  513. FOREIGN KEY (`wid`) REFERENCES `workerqueue` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
  514. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Posts that are about to be distributed at a later time';
  515. --
  516. -- TABLE diaspora-interaction
  517. --
  518. CREATE TABLE IF NOT EXISTS `diaspora-interaction` (
  519. `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri',
  520. `interaction` mediumtext COMMENT 'The Diaspora interaction',
  521. PRIMARY KEY(`uri-id`),
  522. FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
  523. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Signed Diaspora Interaction';
  524. --
  525. -- TABLE event
  526. --
  527. CREATE TABLE IF NOT EXISTS `event` (
  528. `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
  529. `guid` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  530. `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id',
  531. `cid` int unsigned NOT NULL DEFAULT 0 COMMENT 'contact_id (ID of the contact in contact table)',
  532. `uri` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  533. `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the event uri',
  534. `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'creation time',
  535. `edited` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'last edit time',
  536. `start` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'event start time',
  537. `finish` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'event end time',
  538. `summary` text COMMENT 'short description or title of the event',
  539. `desc` text COMMENT 'event description',
  540. `location` text COMMENT 'event location',
  541. `type` varchar(20) NOT NULL DEFAULT '' COMMENT 'event or birthday',
  542. `nofinish` boolean NOT NULL DEFAULT '0' COMMENT 'if event does have no end this is 1',
  543. `ignore` boolean NOT NULL DEFAULT '0' COMMENT '0 or 1',
  544. `allow_cid` mediumtext COMMENT 'Access Control - list of allowed contact.id \'<19><78>\'',
  545. `allow_gid` mediumtext COMMENT 'Access Control - list of allowed groups',
  546. `deny_cid` mediumtext COMMENT 'Access Control - list of denied contact.id',
  547. `deny_gid` mediumtext COMMENT 'Access Control - list of denied groups',
  548. PRIMARY KEY(`id`),
  549. INDEX `uid_start` (`uid`,`start`),
  550. INDEX `cid` (`cid`),
  551. INDEX `uri-id` (`uri-id`),
  552. FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE,
  553. FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
  554. FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
  555. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Events';
  556. --
  557. -- TABLE fcontact
  558. --
  559. CREATE TABLE IF NOT EXISTS `fcontact` (
  560. `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
  561. `guid` varchar(255) NOT NULL DEFAULT '' COMMENT 'unique id',
  562. `url` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  563. `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the fcontact url',
  564. `name` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  565. `photo` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  566. `request` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  567. `nick` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  568. `addr` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  569. `batch` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  570. `notify` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  571. `poll` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  572. `confirm` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  573. `priority` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '',
  574. `network` char(4) NOT NULL DEFAULT '' COMMENT '',
  575. `alias` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  576. `pubkey` text COMMENT '',
  577. `updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
  578. PRIMARY KEY(`id`),
  579. INDEX `addr` (`addr`(32)),
  580. UNIQUE INDEX `url` (`url`(190)),
  581. UNIQUE INDEX `uri-id` (`uri-id`),
  582. FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
  583. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Diaspora compatible contacts - used in the Diaspora implementation';
  584. --
  585. -- TABLE fsuggest
  586. --
  587. CREATE TABLE IF NOT EXISTS `fsuggest` (
  588. `id` int unsigned NOT NULL auto_increment COMMENT '',
  589. `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id',
  590. `cid` int unsigned NOT NULL DEFAULT 0 COMMENT '',
  591. `name` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  592. `url` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  593. `request` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  594. `photo` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  595. `note` text COMMENT '',
  596. `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
  597. PRIMARY KEY(`id`),
  598. INDEX `cid` (`cid`),
  599. INDEX `uid` (`uid`),
  600. FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE,
  601. FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
  602. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='friend suggestion stuff';
  603. --
  604. -- TABLE group
  605. --
  606. CREATE TABLE IF NOT EXISTS `group` (
  607. `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
  608. `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id',
  609. `visible` boolean NOT NULL DEFAULT '0' COMMENT '1 indicates the member list is not private',
  610. `deleted` boolean NOT NULL DEFAULT '0' COMMENT '1 indicates the group has been deleted',
  611. `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'human readable name of group',
  612. PRIMARY KEY(`id`),
  613. INDEX `uid` (`uid`),
  614. FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE
  615. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='privacy groups, group info';
  616. --
  617. -- TABLE group_member
  618. --
  619. CREATE TABLE IF NOT EXISTS `group_member` (
  620. `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
  621. `gid` int unsigned NOT NULL DEFAULT 0 COMMENT 'groups.id of the associated group',
  622. `contact-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'contact.id of the member assigned to the associated group',
  623. PRIMARY KEY(`id`),
  624. INDEX `contactid` (`contact-id`),
  625. UNIQUE INDEX `gid_contactid` (`gid`,`contact-id`),
  626. FOREIGN KEY (`gid`) REFERENCES `group` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
  627. FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
  628. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='privacy groups, member info';
  629. --
  630. -- TABLE gserver-tag
  631. --
  632. CREATE TABLE IF NOT EXISTS `gserver-tag` (
  633. `gserver-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'The id of the gserver',
  634. `tag` varchar(100) NOT NULL DEFAULT '' COMMENT 'Tag that the server has subscribed',
  635. PRIMARY KEY(`gserver-id`,`tag`),
  636. INDEX `tag` (`tag`),
  637. FOREIGN KEY (`gserver-id`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
  638. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Tags that the server has subscribed';
  639. --
  640. -- TABLE hook
  641. --
  642. CREATE TABLE IF NOT EXISTS `hook` (
  643. `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
  644. `hook` varbinary(100) NOT NULL DEFAULT '' COMMENT 'name of hook',
  645. `file` varbinary(200) NOT NULL DEFAULT '' COMMENT 'relative filename of hook handler',
  646. `function` varbinary(200) NOT NULL DEFAULT '' COMMENT 'function name of hook handler',
  647. `priority` smallint unsigned NOT NULL DEFAULT 0 COMMENT 'not yet implemented - can be used to sort conflicts in hook handling by calling handlers in priority order',
  648. PRIMARY KEY(`id`),
  649. INDEX `priority` (`priority`),
  650. UNIQUE INDEX `hook_file_function` (`hook`,`file`,`function`)
  651. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='addon hook registry';
  652. --
  653. -- TABLE host
  654. --
  655. CREATE TABLE IF NOT EXISTS `host` (
  656. `id` tinyint unsigned NOT NULL auto_increment COMMENT 'sequential ID',
  657. `name` varchar(128) NOT NULL DEFAULT '' COMMENT 'The hostname',
  658. PRIMARY KEY(`id`),
  659. UNIQUE INDEX `name` (`name`)
  660. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Hostname';
  661. --
  662. -- TABLE inbox-status
  663. --
  664. CREATE TABLE IF NOT EXISTS `inbox-status` (
  665. `url` varbinary(255) NOT NULL COMMENT 'URL of the inbox',
  666. `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Creation date of this entry',
  667. `success` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last successful delivery',
  668. `failure` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last failed delivery',
  669. `previous` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Previous delivery date',
  670. `archive` boolean NOT NULL DEFAULT '0' COMMENT 'Is the inbox archived?',
  671. `shared` boolean NOT NULL DEFAULT '0' COMMENT 'Is it a shared inbox?',
  672. PRIMARY KEY(`url`)
  673. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Status of ActivityPub inboxes';
  674. --
  675. -- TABLE intro
  676. --
  677. CREATE TABLE IF NOT EXISTS `intro` (
  678. `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
  679. `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id',
  680. `fid` int unsigned COMMENT 'deprecated',
  681. `contact-id` int unsigned NOT NULL DEFAULT 0 COMMENT '',
  682. `suggest-cid` int unsigned COMMENT 'Suggested contact',
  683. `knowyou` boolean NOT NULL DEFAULT '0' COMMENT '',
  684. `duplex` boolean NOT NULL DEFAULT '0' COMMENT 'deprecated',
  685. `note` text COMMENT '',
  686. `hash` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  687. `datetime` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
  688. `blocked` boolean NOT NULL DEFAULT '0' COMMENT 'deprecated',
  689. `ignore` boolean NOT NULL DEFAULT '0' COMMENT '',
  690. PRIMARY KEY(`id`),
  691. INDEX `contact-id` (`contact-id`),
  692. INDEX `suggest-cid` (`suggest-cid`),
  693. INDEX `uid` (`uid`),
  694. FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE,
  695. FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
  696. FOREIGN KEY (`suggest-cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
  697. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='';
  698. --
  699. -- TABLE locks
  700. --
  701. CREATE TABLE IF NOT EXISTS `locks` (
  702. `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
  703. `name` varchar(128) NOT NULL DEFAULT '' COMMENT '',
  704. `locked` boolean NOT NULL DEFAULT '0' COMMENT '',
  705. `pid` int unsigned NOT NULL DEFAULT 0 COMMENT 'Process ID',
  706. `expires` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'datetime of cache expiration',
  707. PRIMARY KEY(`id`),
  708. INDEX `name_expires` (`name`,`expires`)
  709. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='';
  710. --
  711. -- TABLE mail
  712. --
  713. CREATE TABLE IF NOT EXISTS `mail` (
  714. `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
  715. `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id',
  716. `guid` varchar(255) NOT NULL DEFAULT '' COMMENT 'A unique identifier for this private message',
  717. `from-name` varchar(255) NOT NULL DEFAULT '' COMMENT 'name of the sender',
  718. `from-photo` varchar(255) NOT NULL DEFAULT '' COMMENT 'contact photo link of the sender',
  719. `from-url` varchar(255) NOT NULL DEFAULT '' COMMENT 'profile linke of the sender',
  720. `contact-id` varchar(255) COMMENT 'contact.id',
  721. `author-id` int unsigned COMMENT 'Link to the contact table with uid=0 of the author of the mail',
  722. `convid` int unsigned COMMENT 'conv.id',
  723. `title` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  724. `body` mediumtext COMMENT '',
  725. `seen` boolean NOT NULL DEFAULT '0' COMMENT 'if message visited it is 1',
  726. `reply` boolean NOT NULL DEFAULT '0' COMMENT '',
  727. `replied` boolean NOT NULL DEFAULT '0' COMMENT '',
  728. `unknown` boolean NOT NULL DEFAULT '0' COMMENT 'if sender not in the contact table this is 1',
  729. `uri` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  730. `uri-id` int unsigned COMMENT 'Item-uri id of the related mail',
  731. `parent-uri` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  732. `parent-uri-id` int unsigned COMMENT 'Item-uri id of the parent of the related mail',
  733. `thr-parent` varchar(255) COMMENT '',
  734. `thr-parent-id` int unsigned COMMENT 'Id of the item-uri table that contains the thread parent uri',
  735. `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'creation time of the private message',
  736. PRIMARY KEY(`id`),
  737. INDEX `uid_seen` (`uid`,`seen`),
  738. INDEX `convid` (`convid`),
  739. INDEX `uri` (`uri`(64)),
  740. INDEX `parent-uri` (`parent-uri`(64)),
  741. INDEX `contactid` (`contact-id`(32)),
  742. INDEX `author-id` (`author-id`),
  743. INDEX `uri-id` (`uri-id`),
  744. INDEX `parent-uri-id` (`parent-uri-id`),
  745. INDEX `thr-parent-id` (`thr-parent-id`),
  746. FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE,
  747. FOREIGN KEY (`author-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT,
  748. FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
  749. FOREIGN KEY (`parent-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
  750. FOREIGN KEY (`thr-parent-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
  751. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='private messages';
  752. --
  753. -- TABLE mailacct
  754. --
  755. CREATE TABLE IF NOT EXISTS `mailacct` (
  756. `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
  757. `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id',
  758. `server` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  759. `port` smallint unsigned NOT NULL DEFAULT 0 COMMENT '',
  760. `ssltype` varchar(16) NOT NULL DEFAULT '' COMMENT '',
  761. `mailbox` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  762. `user` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  763. `pass` text COMMENT '',
  764. `reply_to` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  765. `action` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '',
  766. `movetofolder` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  767. `pubmail` boolean NOT NULL DEFAULT '0' COMMENT '',
  768. `last_check` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
  769. PRIMARY KEY(`id`),
  770. INDEX `uid` (`uid`),
  771. FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE
  772. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Mail account data for fetching mails';
  773. --
  774. -- TABLE manage
  775. --
  776. CREATE TABLE IF NOT EXISTS `manage` (
  777. `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
  778. `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id',
  779. `mid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id',
  780. PRIMARY KEY(`id`),
  781. UNIQUE INDEX `uid_mid` (`uid`,`mid`),
  782. INDEX `mid` (`mid`),
  783. FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE,
  784. FOREIGN KEY (`mid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE
  785. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='table of accounts that can manage each other';
  786. --
  787. -- TABLE notification
  788. --
  789. CREATE TABLE IF NOT EXISTS `notification` (
  790. `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
  791. `uid` mediumint unsigned COMMENT 'Owner User id',
  792. `vid` smallint unsigned COMMENT 'Id of the verb table entry that contains the activity verbs',
  793. `type` tinyint unsigned COMMENT '',
  794. `actor-id` int unsigned COMMENT 'Link to the contact table with uid=0 of the actor that caused the notification',
  795. `target-uri-id` int unsigned COMMENT 'Item-uri id of the related post',
  796. `parent-uri-id` int unsigned COMMENT 'Item-uri id of the parent of the related post',
  797. `created` datetime COMMENT '',
  798. `seen` boolean DEFAULT '0' COMMENT '',
  799. PRIMARY KEY(`id`),
  800. UNIQUE INDEX `uid_vid_type_actor-id_target-uri-id` (`uid`,`vid`,`type`,`actor-id`,`target-uri-id`),
  801. INDEX `vid` (`vid`),
  802. INDEX `actor-id` (`actor-id`),
  803. INDEX `target-uri-id` (`target-uri-id`),
  804. INDEX `parent-uri-id` (`parent-uri-id`),
  805. INDEX `seen_uid` (`seen`,`uid`),
  806. FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE,
  807. FOREIGN KEY (`vid`) REFERENCES `verb` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT,
  808. FOREIGN KEY (`actor-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
  809. FOREIGN KEY (`target-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
  810. FOREIGN KEY (`parent-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
  811. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='notifications';
  812. --
  813. -- TABLE notify
  814. --
  815. CREATE TABLE IF NOT EXISTS `notify` (
  816. `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
  817. `type` smallint unsigned NOT NULL DEFAULT 0 COMMENT '',
  818. `name` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  819. `url` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  820. `photo` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  821. `date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
  822. `msg` mediumtext COMMENT '',
  823. `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id',
  824. `link` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  825. `iid` int unsigned COMMENT '',
  826. `parent` int unsigned COMMENT '',
  827. `uri-id` int unsigned COMMENT 'Item-uri id of the related post',
  828. `parent-uri-id` int unsigned COMMENT 'Item-uri id of the parent of the related post',
  829. `seen` boolean NOT NULL DEFAULT '0' COMMENT '',
  830. `verb` varchar(100) NOT NULL DEFAULT '' COMMENT '',
  831. `otype` varchar(10) NOT NULL DEFAULT '' COMMENT '',
  832. `name_cache` tinytext COMMENT 'Cached bbcode parsing of name',
  833. `msg_cache` mediumtext COMMENT 'Cached bbcode parsing of msg',
  834. PRIMARY KEY(`id`),
  835. INDEX `seen_uid_date` (`seen`,`uid`,`date`),
  836. INDEX `uid_date` (`uid`,`date`),
  837. INDEX `uid_type_link` (`uid`,`type`,`link`(190)),
  838. INDEX `uri-id` (`uri-id`),
  839. INDEX `parent-uri-id` (`parent-uri-id`),
  840. FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE,
  841. FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
  842. FOREIGN KEY (`parent-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
  843. ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='notifications';
  844. --
  845. -- TABLE notify-threads
  846. --
  847. CREATE TABLE IF NOT EXISTS `notify-threads` (
  848. `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
  849. `notify-id` int unsigned NOT NULL DEFAULT 0 COMMENT '',
  850. `master-parent-item` int unsigned COMMENT 'Deprecated',
  851. `master-parent-uri-id` int unsigned COMMENT 'Item-uri id of the parent of the related post',
  852. `parent-item` int unsigned NOT NULL DEFAULT 0 COMMENT '',
  853. `receiver-uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id',
  854. PRIMARY KEY(`id`),
  855. INDEX `master-parent-uri-id` (`master-parent-uri-id`),
  856. INDEX `receiver-uid` (`receiver-uid`),
  857. INDEX `notify-id` (`notify-id`),
  858. FOREIGN KEY (`notify-id`) REFERENCES `notify` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
  859. FOREIGN KEY (`master-parent-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,