| 1 |
<?php |
| 2 |
// Exit if accessed directly |
| 3 |
if( ! defined( 'ABSPATH' ) ) exit; |
| 4 |
|
| 5 |
if( ! function_exists( 'wpforo_get_install_sqls' ) ) { |
| 6 |
function wpforo_get_install_sqls() { |
| 7 |
$charset_collate = ''; |
| 8 |
if( ! empty( WPF()->db->charset ) ) $charset_collate = "DEFAULT CHARACTER SET " . WPF()->db->charset; |
| 9 |
if( ! empty( WPF()->db->collate ) ) $charset_collate .= " COLLATE " . WPF()->db->collate; |
| 10 |
$engine = version_compare( WPF()->db->db_version(), '5.6.4', '>=' ) ? 'InnoDB' : 'MyISAM'; |
| 11 |
|
| 12 |
return [ |
| 13 |
WPF()->tables->boards => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->boards . "`( |
| 14 |
`boardid` INT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 15 |
`title` VARCHAR(100) NOT NULL, |
| 16 |
`slug` VARCHAR(255) NOT NULL, |
| 17 |
`pageid` BIGINT NOT NULL, |
| 18 |
`modules` TEXT, |
| 19 |
`locale` VARCHAR(10) DEFAULT '', |
| 20 |
`is_standalone` TINYINT(1) NOT NULL DEFAULT 0, |
| 21 |
`excld_urls` TEXT, |
| 22 |
`status` TINYINT(1) NOT NULL DEFAULT 1, |
| 23 |
`settings` TEXT, |
| 24 |
PRIMARY KEY (`boardid`), |
| 25 |
UNIQUE KEY `unique_slug` (`slug`(191)), |
| 26 |
KEY `bulk_index` (`slug`(191), `locale`, `is_standalone`, `status`) |
| 27 |
) ENGINE=INNODB $charset_collate", |
| 28 |
WPF()->tables->forums => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->forums . "`( |
| 29 |
`forumid` INT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 30 |
`title` VARCHAR(255) NOT NULL, |
| 31 |
`slug` VARCHAR(255) NOT NULL, |
| 32 |
`description` LONGTEXT, |
| 33 |
`parentid` INT UNSIGNED NOT NULL DEFAULT 0, |
| 34 |
`icon` VARCHAR(255), |
| 35 |
`cover` BIGINT UNSIGNED NOT NULL DEFAULT 0, |
| 36 |
`cover_height` INT(4) UNSIGNED NOT NULL DEFAULT 150, |
| 37 |
`last_topicid` INT UNSIGNED NOT NULL DEFAULT 0, |
| 38 |
`last_postid` INT UNSIGNED NOT NULL DEFAULT 0, |
| 39 |
`last_userid` VARCHAR(255) NOT NULL, |
| 40 |
`last_post_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', |
| 41 |
`topics` INT NOT NULL DEFAULT 0, |
| 42 |
`posts` INT NOT NULL DEFAULT 0, |
| 43 |
`permissions` TEXT, |
| 44 |
`meta_key` TEXT, |
| 45 |
`meta_desc` TEXT, |
| 46 |
`status` tinyint(1) UNSIGNED NOT NULL DEFAULT 0, |
| 47 |
`is_cat` tinyint(1) UNSIGNED NOT NULL DEFAULT 0, |
| 48 |
`layout` tinyint(1) UNSIGNED NOT NULL DEFAULT 0, |
| 49 |
`order` INT UNSIGNED NOT NULL DEFAULT 0, |
| 50 |
`color` VARCHAR(7) NOT NULL DEFAULT '', |
| 51 |
`type` VARCHAR(20) NOT NULL DEFAULT 'forum', |
| 52 |
PRIMARY KEY (`forumid`), |
| 53 |
UNIQUE KEY `UNIQUE SLUG` (`slug`(191)), |
| 54 |
KEY `order` (`order`), |
| 55 |
KEY `status` (`status`), |
| 56 |
KEY `parentid` (`parentid`), |
| 57 |
KEY `last_postid` (`last_postid`), |
| 58 |
KEY `last_userid` (`last_userid`(191)), |
| 59 |
KEY `is_cat` (`is_cat`) |
| 60 |
) ENGINE=InnoDB $charset_collate", |
| 61 |
WPF()->tables->topics => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->topics . "`( |
| 62 |
`topicid` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 63 |
`forumid` INT UNSIGNED NOT NULL, |
| 64 |
`first_postid` BIGINT UNSIGNED NOT NULL DEFAULT 0, |
| 65 |
`userid` INT UNSIGNED NOT NULL, |
| 66 |
`title` VARCHAR(255) NOT NULL, |
| 67 |
`slug` VARCHAR(255) NOT NULL, |
| 68 |
`created` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', |
| 69 |
`modified` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', |
| 70 |
`last_post` BIGINT UNSIGNED NOT NULL DEFAULT 0, |
| 71 |
`posts` INT NOT NULL DEFAULT 0, |
| 72 |
`votes` INT NOT NULL DEFAULT 0, |
| 73 |
`answers` INT NOT NULL DEFAULT 0, |
| 74 |
`views` INT UNSIGNED NOT NULL DEFAULT 0, |
| 75 |
`meta_key` TEXT, |
| 76 |
`meta_desc` TEXT, |
| 77 |
`type` TINYINT NOT NULL DEFAULT 0, |
| 78 |
`solved` TINYINT(1) NOT NULL DEFAULT 0, |
| 79 |
`closed` tinyint(1) UNSIGNED NOT NULL DEFAULT 0, |
| 80 |
`has_attach` tinyint(1) UNSIGNED NOT NULL DEFAULT 0, |
| 81 |
`private` tinyint(1) UNSIGNED NOT NULL DEFAULT 0, |
| 82 |
`status` tinyint(1) UNSIGNED NOT NULL DEFAULT 0, |
| 83 |
`name` VARCHAR(50) NOT NULL DEFAULT '', |
| 84 |
`email` VARCHAR(50) NOT NULL DEFAULT '', |
| 85 |
`prefix` VARCHAR(100) NOT NULL DEFAULT '', |
| 86 |
`tags` TEXT, |
| 87 |
`indexed` VARCHAR(32) DEFAULT NULL, |
| 88 |
`local` TINYINT(1) NOT NULL DEFAULT 0, |
| 89 |
`cloud` TINYINT(1) NOT NULL DEFAULT 0, |
| 90 |
`task_tag` INT UNSIGNED NOT NULL DEFAULT 0, |
| 91 |
PRIMARY KEY (`topicid`), |
| 92 |
KEY `slug` (`slug`(191)), |
| 93 |
FULLTEXT KEY `title` (`title`), |
| 94 |
KEY `forumid` (`forumid`), |
| 95 |
KEY `first_postid` (`first_postid`), |
| 96 |
KEY `created` (`created`), |
| 97 |
KEY `modified` (`modified`), |
| 98 |
KEY `last_post` (`last_post`), |
| 99 |
KEY `type` (`type`), |
| 100 |
KEY `status` (`status`), |
| 101 |
KEY `email` (`email`), |
| 102 |
KEY `solved` (`solved`), |
| 103 |
KEY `is_private` (`private`), |
| 104 |
KEY `own_private` (`userid`,`private`), |
| 105 |
KEY `forumid_status` (`forumid`,`status`), |
| 106 |
KEY `forumid_status_private` (`forumid`,`status`,`private`), |
| 107 |
KEY `prefix` (`prefix`), |
| 108 |
KEY `indexed` (`indexed`), |
| 109 |
KEY `local` (`local`), |
| 110 |
KEY `cloud` (`cloud`), |
| 111 |
KEY `task_tag` (`task_tag`) |
| 112 |
) ENGINE=$engine $charset_collate", |
| 113 |
WPF()->tables->postmeta => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->postmeta . "`( |
| 114 |
`metaid` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 115 |
`postid` BIGINT UNSIGNED NOT NULL, |
| 116 |
`metakey` VARCHAR(255) NOT NULL, |
| 117 |
`metavalue` MEDIUMTEXT, |
| 118 |
`forumid` INT UNSIGNED NOT NULL DEFAULT 0, |
| 119 |
`topicid` BIGINT UNSIGNED NOT NULL DEFAULT 0, |
| 120 |
`is_first_post` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, |
| 121 |
`status` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, |
| 122 |
`private` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, |
| 123 |
PRIMARY KEY (`metaid`), |
| 124 |
KEY `postid_metakey` (`postid`, `metakey`(191)), |
| 125 |
KEY `forumid` (`forumid`), |
| 126 |
KEY `topicid` (`topicid`), |
| 127 |
KEY `is_first_post` (`is_first_post`), |
| 128 |
KEY `status` (`status`), |
| 129 |
KEY `private` (`private`) |
| 130 |
) ENGINE=InnoDB $charset_collate", |
| 131 |
WPF()->tables->posts => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->posts . "`( |
| 132 |
`postid` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 133 |
`parentid` BIGINT UNSIGNED NOT NULL DEFAULT 0, |
| 134 |
`forumid` INT UNSIGNED NOT NULL, |
| 135 |
`topicid` BIGINT UNSIGNED NOT NULL, |
| 136 |
`userid` INT UNSIGNED NOT NULL, |
| 137 |
`title` varchar(255), |
| 138 |
`body` LONGTEXT, |
| 139 |
`created` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', |
| 140 |
`modified` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', |
| 141 |
`likes` INT UNSIGNED NOT NULL DEFAULT 0, |
| 142 |
`votes` INT NOT NULL DEFAULT 0, |
| 143 |
`is_answer` tinyint(1) UNSIGNED NOT NULL DEFAULT 0, |
| 144 |
`is_first_post` tinyint(1) UNSIGNED NOT NULL DEFAULT 0, |
| 145 |
`status` tinyint(1) UNSIGNED NOT NULL DEFAULT 0, |
| 146 |
`name` VARCHAR(50) NOT NULL DEFAULT '', |
| 147 |
`email` VARCHAR(50) NOT NULL DEFAULT '', |
| 148 |
`private` tinyint(1) UNSIGNED NOT NULL DEFAULT 0, |
| 149 |
`root` BIGINT NULL DEFAULT NULL, |
| 150 |
PRIMARY KEY (`postid`), |
| 151 |
FULLTEXT KEY `title`(`title`(191)), |
| 152 |
FULLTEXT KEY `body` (`body`), |
| 153 |
FULLTEXT KEY `title_plus_body` (`title`,`body`), |
| 154 |
KEY `topicid` (`topicid`), |
| 155 |
KEY `forumid` (`forumid`), |
| 156 |
KEY `userid` (`userid`), |
| 157 |
KEY `created` (`created`), |
| 158 |
KEY `parentid` (`parentid`), |
| 159 |
KEY `is_answer` (`is_answer`), |
| 160 |
KEY `is_first_post` (`is_first_post`), |
| 161 |
KEY `status` (`status`), |
| 162 |
KEY `email` (`email`), |
| 163 |
KEY `is_private` (`private`), |
| 164 |
KEY `root` (`root`), |
| 165 |
KEY `forumid_status` (`forumid`,`status`), |
| 166 |
KEY `topicid_status` (`topicid`,`status`), |
| 167 |
KEY `topicid_solved` (`topicid`,`is_answer`), |
| 168 |
KEY `topicid_parentid` (`topicid`,`parentid`), |
| 169 |
KEY `forumid_status_private` (`forumid`, `status`, `private`), |
| 170 |
KEY `forumid_answer_first` (`forumid`, `is_answer`, `is_first_post`) |
| 171 |
) ENGINE=$engine $charset_collate", |
| 172 |
WPF()->tables->profiles => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->profiles . "` ( |
| 173 |
`userid` BIGINT UNSIGNED NOT NULL, |
| 174 |
`title` VARCHAR(255) NOT NULL DEFAULT 'member', |
| 175 |
`groupid` INT UNSIGNED NOT NULL, |
| 176 |
`secondary_groupids` VARCHAR(255) NOT NULL DEFAULT '', |
| 177 |
`avatar` VARCHAR(991) NOT NULL DEFAULT '', |
| 178 |
`cover` VARCHAR(255) NOT NULL DEFAULT '', |
| 179 |
`posts` INT UNSIGNED NOT NULL DEFAULT 0, |
| 180 |
`topics` INT UNSIGNED NOT NULL DEFAULT 0, |
| 181 |
`questions` INT UNSIGNED NOT NULL DEFAULT 0, |
| 182 |
`answers` INT UNSIGNED NOT NULL DEFAULT 0, |
| 183 |
`comments` INT UNSIGNED NOT NULL DEFAULT 0, |
| 184 |
`reactions_in` TEXT, |
| 185 |
`reactions_out` TEXT, |
| 186 |
`points` INT NOT NULL DEFAULT 0, |
| 187 |
`custom_points` INT NOT NULL DEFAULT 0, |
| 188 |
`online_time` INT UNSIGNED NOT NULL DEFAULT 0, |
| 189 |
`timezone` VARCHAR(255) NOT NULL DEFAULT '', |
| 190 |
`location` VARCHAR(255) NOT NULL DEFAULT '', |
| 191 |
`signature` TEXT, |
| 192 |
`about` TEXT, |
| 193 |
`occupation` TEXT, |
| 194 |
`status` VARCHAR(8) NOT NULL DEFAULT 'active' COMMENT 'active, blocked, trashed, spamer', |
| 195 |
`is_email_confirmed` TINYINT(1) NOT NULL DEFAULT 0, |
| 196 |
`is_mention_muted` TINYINT(1) NOT NULL DEFAULT 0, |
| 197 |
`fields` LONGTEXT, |
| 198 |
PRIMARY KEY (`userid`), |
| 199 |
KEY `groupid` (`groupid`), |
| 200 |
KEY `online_time` (`online_time`), |
| 201 |
KEY `posts` (`posts`), |
| 202 |
KEY `status` (`status`), |
| 203 |
KEY `is_email_confirmed` (`is_email_confirmed`) |
| 204 |
) ENGINE=InnoDB $charset_collate", |
| 205 |
WPF()->tables->usergroups => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->usergroups . "`( |
| 206 |
`groupid` INT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 207 |
`name` VARCHAR(255) NOT NULL, |
| 208 |
`cans` LONGTEXT NOT NULL COMMENT 'board permissions', |
| 209 |
`description` TEXT, |
| 210 |
`utitle` VARCHAR(100) NOT NULL DEFAULT '', |
| 211 |
`role` VARCHAR(50) NOT NULL DEFAULT '', |
| 212 |
`access` VARCHAR(50) NOT NULL DEFAULT '', |
| 213 |
`color` VARCHAR(7) NOT NULL DEFAULT '', |
| 214 |
`visible` TINYINT(1) UNSIGNED NOT NULL DEFAULT 1, |
| 215 |
`secondary` TINYINT(1) UNSIGNED NOT NULL DEFAULT 1, |
| 216 |
`is_default` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, |
| 217 |
PRIMARY KEY (`groupid`), |
| 218 |
KEY `visible` (`visible`), |
| 219 |
KEY `secondary` (`secondary`), |
| 220 |
UNIQUE KEY `UNIQUE_GROUP_NAME` (`name`(191)) |
| 221 |
) ENGINE=MyISAM $charset_collate", |
| 222 |
WPF()->tables->languages => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->languages . "`( |
| 223 |
`langid` INT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 224 |
`name` VARCHAR(255) NOT NULL, |
| 225 |
`status` TINYINT(1) UNSIGNED NOT NULL DEFAULT 1, |
| 226 |
PRIMARY KEY (`langid`), |
| 227 |
UNIQUE KEY `UNIQUE language name` (`name`(191)) |
| 228 |
) ENGINE=MyISAM $charset_collate", |
| 229 |
WPF()->tables->phrases => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->phrases . "` ( |
| 230 |
`phraseid` INT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 231 |
`langid` INT UNSIGNED NOT NULL, |
| 232 |
`phrase_key` text NOT NULL, |
| 233 |
`phrase_value` text NOT NULL, |
| 234 |
`package` VARCHAR(255) NOT NULL DEFAULT 'wpforo', |
| 235 |
PRIMARY KEY (`phraseid`), |
| 236 |
KEY `langid` (`langid`), |
| 237 |
KEY `phrase_key` (`phrase_key`(191)), |
| 238 |
UNIQUE KEY lng_and_key_uniq (`langid`, `phrase_key`(191)) |
| 239 |
) ENGINE=MyISAM $charset_collate", |
| 240 |
WPF()->tables->accesses => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->accesses . "`( |
| 241 |
`accessid` INT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 242 |
`access` VARCHAR(255) NOT NULL, |
| 243 |
`title` VARCHAR(255) NOT NULL, |
| 244 |
`cans` LONGTEXT NOT NULL COMMENT 'forum permissions', |
| 245 |
PRIMARY KEY (`accessid`), |
| 246 |
UNIQUE KEY ( `access`(191) ) |
| 247 |
) ENGINE=MyISAM $charset_collate", |
| 248 |
WPF()->tables->subscribes => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->subscribes . "` ( |
| 249 |
`subid` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 250 |
`itemid` BIGINT UNSIGNED NOT NULL, |
| 251 |
`type` VARCHAR(5) NOT NULL, |
| 252 |
`confirmkey` varchar(32) NOT NULL, |
| 253 |
`userid` BIGINT UNSIGNED NOT NULL, |
| 254 |
`active` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, |
| 255 |
`user_name` VARCHAR(60) NOT NULL, |
| 256 |
`user_email` VARCHAR(60) NOT NULL, |
| 257 |
PRIMARY KEY (`subid`), |
| 258 |
UNIQUE KEY `fld_group_unq` (`itemid`,`type`,`userid`,`user_email`(60)), |
| 259 |
UNIQUE KEY `confirmkey` (`confirmkey`), |
| 260 |
KEY `itemid_2` (`itemid`), |
| 261 |
KEY `userid` (`userid`) |
| 262 |
) ENGINE=INNODB $charset_collate", |
| 263 |
WPF()->tables->follows => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->follows . "` ( |
| 264 |
`followid` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 265 |
`itemid` BIGINT UNSIGNED NOT NULL, |
| 266 |
`itemtype` VARCHAR(5) NOT NULL DEFAULT 'user', |
| 267 |
`confirmkey` VARCHAR(32) NOT NULL, |
| 268 |
`userid` BIGINT UNSIGNED NOT NULL, |
| 269 |
`active` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, |
| 270 |
`name` VARCHAR(60) NOT NULL, |
| 271 |
`email` VARCHAR(60) NOT NULL, |
| 272 |
PRIMARY KEY (`followid`), |
| 273 |
UNIQUE KEY `fld_group_unq` (`itemid`,`itemtype`,`userid`,`email`(60)), |
| 274 |
UNIQUE KEY `confirmkey` (`confirmkey`), |
| 275 |
KEY `itemid` (`itemid`), |
| 276 |
KEY `userid` (`userid`) |
| 277 |
) ENGINE=INNODB $charset_collate", |
| 278 |
WPF()->tables->visits => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->visits . "` ( |
| 279 |
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 280 |
`userid` BIGINT UNSIGNED NOT NULL, |
| 281 |
`name` VARCHAR(60) NOT NULL, |
| 282 |
`ip` VARCHAR(60) NOT NULL, |
| 283 |
`time` INT UNSIGNED NOT NULL, |
| 284 |
`forumid` INT UNSIGNED NOT NULL, |
| 285 |
`topicid` BIGINT UNSIGNED NOT NULL, |
| 286 |
PRIMARY KEY (`id`), |
| 287 |
KEY `userid` (`userid`), |
| 288 |
KEY `forumid` (`forumid`), |
| 289 |
KEY `topicid` (`topicid`), |
| 290 |
KEY `time` (`time`), |
| 291 |
KEY `ip` (`ip`), |
| 292 |
KEY `time_forumid` (`time`,`forumid`), |
| 293 |
KEY `time_topicid` (`time`,`topicid`), |
| 294 |
UNIQUE KEY `unique_tracking` (`userid`,`ip`,`forumid`,`topicid`) |
| 295 |
) ENGINE=INNODB $charset_collate", |
| 296 |
WPF()->tables->activity => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->activity . "` ( |
| 297 |
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 298 |
`type` VARCHAR(60) NOT NULL, |
| 299 |
`itemid` BIGINT UNSIGNED NOT NULL, |
| 300 |
`itemtype` VARCHAR(60) NOT NULL, |
| 301 |
`itemid_second` BIGINT UNSIGNED NOT NULL DEFAULT 0, |
| 302 |
`userid` BIGINT UNSIGNED NOT NULL DEFAULT 0, |
| 303 |
`name` VARCHAR(60) NOT NULL DEFAULT '', |
| 304 |
`email` VARCHAR(70) NOT NULL DEFAULT '', |
| 305 |
`date` INT UNSIGNED NOT NULL DEFAULT 0, |
| 306 |
`content` TEXT, |
| 307 |
`permalink` VARCHAR(1024) NOT NULL DEFAULT '', |
| 308 |
`new` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, |
| 309 |
PRIMARY KEY (`id`), |
| 310 |
KEY `type` (`type`), |
| 311 |
KEY `type_objid_objtype` (`type`,`itemid`,`itemtype`), |
| 312 |
KEY `type_objid_objtype_userid` (`type`,`itemid`,`itemtype`,`userid`), |
| 313 |
KEY `itemtype_userid_new` (`itemtype`,`userid`,`new`), |
| 314 |
KEY `date` (`date`) |
| 315 |
) ENGINE=INNODB $charset_collate", |
| 316 |
WPF()->tables->post_revisions => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->post_revisions . "` ( |
| 317 |
`revisionid` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 318 |
`userid` BIGINT UNSIGNED NOT NULL DEFAULT 0, |
| 319 |
`textareaid` VARCHAR(50) NOT NULL, |
| 320 |
`postid` BIGINT UNSIGNED NOT NULL DEFAULT 0, |
| 321 |
`body` LONGTEXT, |
| 322 |
`created` INT UNSIGNED NOT NULL DEFAULT 0, |
| 323 |
`version` SMALLINT NOT NULL DEFAULT 0, |
| 324 |
`email` VARCHAR(50) NOT NULL DEFAULT '', |
| 325 |
`url` TEXT, |
| 326 |
PRIMARY KEY (`revisionid`), |
| 327 |
KEY `userid_textareaid_postid_email` (`userid`, `textareaid`, `postid`, `email`, `url`(70)) |
| 328 |
) ENGINE=INNODB $charset_collate", |
| 329 |
WPF()->tables->tags => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->tags . "`( |
| 330 |
`tagid` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 331 |
`tag` VARCHAR(255) NOT NULL, |
| 332 |
`prefix` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, |
| 333 |
`count` INT UNSIGNED NOT NULL DEFAULT 0, |
| 334 |
PRIMARY KEY (`tagid`), |
| 335 |
UNIQUE KEY `tag` (`tag`(190)), |
| 336 |
KEY (`prefix`) |
| 337 |
) ENGINE=MyISAM $charset_collate", |
| 338 |
WPF()->tables->logs => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->logs . "`( |
| 339 |
`logid` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 340 |
`sessionid` VARCHAR(255) NOT NULL, |
| 341 |
`key` VARCHAR(255) NOT NULL, |
| 342 |
`value` MEDIUMTEXT NOT NULL, |
| 343 |
PRIMARY KEY (`logid`), |
| 344 |
KEY `sessionid_key` (`sessionid`(20), `key`(160)) |
| 345 |
) ENGINE=InnoDB $charset_collate", |
| 346 |
WPF()->tables->reactions => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->reactions . "` ( |
| 347 |
`reactionid` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 348 |
`userid` BIGINT UNSIGNED NOT NULL, |
| 349 |
`postid` BIGINT UNSIGNED NOT NULL, |
| 350 |
`post_userid` BIGINT UNSIGNED NOT NULL DEFAULT 0, |
| 351 |
`reaction` TINYINT(4) NOT NULL DEFAULT 1, |
| 352 |
`type` VARCHAR(50) NOT NULL DEFAULT 'up', |
| 353 |
`name` VARCHAR(50) DEFAULT NULL, |
| 354 |
`email` VARCHAR(100) DEFAULT NULL, |
| 355 |
PRIMARY KEY (`reactionid`), |
| 356 |
UNIQUE KEY `unique_reaction` (`userid`,`postid`,`type`,`email`), |
| 357 |
KEY `performance` (`postid`,`post_userid`,`type`) |
| 358 |
) ENGINE=InnoDB $charset_collate", |
| 359 |
WPF()->tables->bookmarks => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->bookmarks . "` ( |
| 360 |
`bookmarkid` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 361 |
`userid` BIGINT UNSIGNED NOT NULL, |
| 362 |
`boardid` INT UNSIGNED NOT NULL, |
| 363 |
`postid` BIGINT UNSIGNED NOT NULL, |
| 364 |
`created` INT UNSIGNED NOT NULL, |
| 365 |
`status` TINYINT(1) UNSIGNED NOT NULL DEFAULT 1, |
| 366 |
PRIMARY KEY (`bookmarkid`), |
| 367 |
UNIQUE KEY `unique_bookmark` (`userid`,`boardid`,`postid`), |
| 368 |
KEY `performance` (`userid`,`boardid`,`postid`,`status`) |
| 369 |
) ENGINE=InnoDB $charset_collate", |
| 370 |
WPF()->tables->ai_cache => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->ai_cache . "` ( |
| 371 |
`type` VARCHAR(50) NOT NULL COMMENT 'Cache type', |
| 372 |
`cache_key` BINARY(16) NOT NULL COMMENT 'MD5 hash', |
| 373 |
`response` MEDIUMTEXT NOT NULL COMMENT 'JSON response data', |
| 374 |
`expires_at` INT UNSIGNED NOT NULL COMMENT 'Unix timestamp for cache expiry (0=never)', |
| 375 |
`postid` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Post ID', |
| 376 |
PRIMARY KEY (`type`, `cache_key`), |
| 377 |
KEY `idx_expires` (`expires_at`), |
| 378 |
KEY `idx_postid` (`postid`) |
| 379 |
) ENGINE=InnoDB $charset_collate", |
| 380 |
WPF()->tables->ai_chat_conversations => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->ai_chat_conversations . "` ( |
| 381 |
`conversation_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 382 |
`userid` BIGINT UNSIGNED NOT NULL COMMENT 'User ID who owns this conversation', |
| 383 |
`title` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Conversation title (auto-generated from first message)', |
| 384 |
`running_summary` TEXT COMMENT 'AI-generated running summary of the conversation', |
| 385 |
`key_facts` TEXT COMMENT 'JSON array of extracted key facts', |
| 386 |
`message_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Number of messages in conversation', |
| 387 |
`total_tokens` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Total tokens used in conversation', |
| 388 |
`total_credits` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Total credits spent on conversation', |
| 389 |
`last_message_at` DATETIME DEFAULT NULL COMMENT 'Timestamp of last message', |
| 390 |
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 391 |
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
| 392 |
PRIMARY KEY (`conversation_id`), |
| 393 |
KEY `idx_userid` (`userid`), |
| 394 |
KEY `idx_userid_updated` (`userid`, `updated_at`), |
| 395 |
KEY `idx_last_message` (`last_message_at`) |
| 396 |
) ENGINE=InnoDB $charset_collate", |
| 397 |
WPF()->tables->ai_chat_messages => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->ai_chat_messages . "` ( |
| 398 |
`message_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 399 |
`conversation_id` BIGINT UNSIGNED NOT NULL COMMENT 'Parent conversation ID', |
| 400 |
`role` ENUM('user', 'assistant') NOT NULL COMMENT 'Message role', |
| 401 |
`content` TEXT NOT NULL COMMENT 'Message content', |
| 402 |
`tokens_used` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Tokens used for this message', |
| 403 |
`credits_spent` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Credits spent for this response', |
| 404 |
`quality_tier` VARCHAR(20) NOT NULL DEFAULT 'fast' COMMENT 'Quality tier used (fast, balanced, advanced)', |
| 405 |
`sources_count` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Number of RAG sources used', |
| 406 |
`sources_json` TEXT COMMENT 'JSON array of source references', |
| 407 |
`context_updated` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Whether context was updated after this message', |
| 408 |
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 409 |
PRIMARY KEY (`message_id`), |
| 410 |
KEY `idx_conversation` (`conversation_id`), |
| 411 |
KEY `idx_conversation_created` (`conversation_id`, `created_at`), |
| 412 |
KEY `idx_role` (`role`) |
| 413 |
) ENGINE=InnoDB $charset_collate", |
| 414 |
WPF()->tables->ai_tasks => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->ai_tasks . "` ( |
| 415 |
`task_id` INT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 416 |
`task_name` VARCHAR(100) NOT NULL COMMENT 'User-defined task name', |
| 417 |
`task_type` VARCHAR(30) NOT NULL COMMENT 'topic_generator or reply_generator', |
| 418 |
`status` VARCHAR(20) NOT NULL DEFAULT 'paused' COMMENT 'active, paused, error', |
| 419 |
`board_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Board ID for multi-board support', |
| 420 |
`config` LONGTEXT NOT NULL COMMENT 'JSON configuration for task-specific settings', |
| 421 |
`frequency` VARCHAR(30) NOT NULL DEFAULT 'daily' COMMENT 'hourly, 3hours, 6hours, daily, 3days, weekly, custom', |
| 422 |
`next_run_time` DATETIME DEFAULT NULL COMMENT 'When task should run next', |
| 423 |
`last_run_time` DATETIME DEFAULT NULL COMMENT 'When task last ran', |
| 424 |
`last_run_status` VARCHAR(20) DEFAULT NULL COMMENT 'success, failure, skipped', |
| 425 |
`total_runs` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Total execution count', |
| 426 |
`successful_runs` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Successful execution count', |
| 427 |
`failed_runs` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Failed execution count', |
| 428 |
`items_created` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Total topics/replies created', |
| 429 |
`credits_used` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Total credits consumed', |
| 430 |
`credit_stop_threshold` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Stop when credits below this', |
| 431 |
`auto_pause_on_limit` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Auto-pause when credit threshold reached', |
| 432 |
`created_by` INT UNSIGNED NOT NULL COMMENT 'User ID who created the task', |
| 433 |
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 434 |
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
| 435 |
PRIMARY KEY (`task_id`), |
| 436 |
KEY `idx_status` (`status`), |
| 437 |
KEY `idx_board_id` (`board_id`), |
| 438 |
KEY `idx_task_type` (`task_type`), |
| 439 |
KEY `idx_next_run` (`next_run_time`), |
| 440 |
KEY `idx_status_next_run` (`status`, `next_run_time`) |
| 441 |
) ENGINE=InnoDB $charset_collate", |
| 442 |
WPF()->tables->ai_task_logs => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->ai_task_logs . "` ( |
| 443 |
`log_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 444 |
`task_id` INT UNSIGNED NOT NULL COMMENT 'Foreign key to ai_tasks', |
| 445 |
`execution_time` DATETIME NOT NULL COMMENT 'When execution started', |
| 446 |
`status` VARCHAR(20) NOT NULL COMMENT 'success, failure, skipped', |
| 447 |
`items_created` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Topics/replies created this run', |
| 448 |
`credits_used` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Credits consumed this run', |
| 449 |
`execution_duration` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Duration in milliseconds', |
| 450 |
`result_data` LONGTEXT COMMENT 'JSON with created item IDs, skipped counts, etc.', |
| 451 |
`error_message` TEXT COMMENT 'Error details if failed', |
| 452 |
`api_request_summary` TEXT COMMENT 'Sanitized API request summary', |
| 453 |
`api_response_summary` TEXT COMMENT 'Truncated API response summary', |
| 454 |
`api_response_time` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'API response time in ms', |
| 455 |
PRIMARY KEY (`log_id`), |
| 456 |
KEY `idx_task_id` (`task_id`), |
| 457 |
KEY `idx_execution_time` (`execution_time`), |
| 458 |
KEY `idx_status` (`status`), |
| 459 |
KEY `idx_task_status` (`task_id`, `status`) |
| 460 |
) ENGINE=InnoDB $charset_collate", |
| 461 |
WPF()->tables->ai_moderation => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->ai_moderation . "` ( |
| 462 |
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 463 |
`content_type` VARCHAR(20) NOT NULL COMMENT 'topic or post', |
| 464 |
`content_id` BIGINT UNSIGNED NOT NULL COMMENT 'Topic ID or Post ID', |
| 465 |
`topicid` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Parent topic ID (for posts)', |
| 466 |
`forumid` INT UNSIGNED NOT NULL COMMENT 'Forum ID', |
| 467 |
`userid` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Author user ID', |
| 468 |
`moderation_type` VARCHAR(50) NOT NULL COMMENT 'spam, toxicity, quality, compliance', |
| 469 |
`score` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Score 0-100', |
| 470 |
`is_flagged` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Whether content was flagged', |
| 471 |
`confidence` DECIMAL(3,2) NOT NULL DEFAULT 0.00 COMMENT 'AI confidence 0.00-1.00', |
| 472 |
`action_taken` VARCHAR(50) DEFAULT NULL COMMENT 'approve, hold, delete, ban_user, etc.', |
| 473 |
`action_reason` TEXT COMMENT 'Reason for the action', |
| 474 |
`indicators` LONGTEXT COMMENT 'JSON array of detected indicators', |
| 475 |
`analysis_summary` TEXT COMMENT 'Brief AI analysis summary', |
| 476 |
`quality_tier` VARCHAR(20) NOT NULL DEFAULT 'balanced' COMMENT 'fast, balanced, advanced, premium', |
| 477 |
`credits_used` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Credits consumed', |
| 478 |
`context_used` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Whether RAG context was used', |
| 479 |
`indexed_topics_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Number of indexed topics available', |
| 480 |
`detection_time_ms` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Processing time in ms', |
| 481 |
`content_preview` TEXT COMMENT 'First 500 chars of moderated content', |
| 482 |
`created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When moderation was performed', |
| 483 |
`reviewed_by` BIGINT UNSIGNED DEFAULT NULL COMMENT 'Admin who reviewed (if manually reviewed)', |
| 484 |
`reviewed_at` DATETIME DEFAULT NULL COMMENT 'When manually reviewed', |
| 485 |
`review_action` VARCHAR(50) DEFAULT NULL COMMENT 'Override action taken by admin', |
| 486 |
`review_notes` TEXT COMMENT 'Admin notes on review', |
| 487 |
PRIMARY KEY (`id`), |
| 488 |
KEY `idx_content` (`content_type`, `content_id`), |
| 489 |
KEY `idx_topicid` (`topicid`), |
| 490 |
KEY `idx_forumid` (`forumid`), |
| 491 |
KEY `idx_userid` (`userid`), |
| 492 |
KEY `idx_moderation_type` (`moderation_type`), |
| 493 |
KEY `idx_is_flagged` (`is_flagged`), |
| 494 |
KEY `idx_action_taken` (`action_taken`), |
| 495 |
KEY `idx_created` (`created`), |
| 496 |
KEY `idx_reviewed` (`reviewed_by`, `reviewed_at`), |
| 497 |
KEY `idx_score` (`score`), |
| 498 |
KEY `idx_composite` (`content_type`, `moderation_type`, `is_flagged`, `created`) |
| 499 |
) ENGINE=InnoDB $charset_collate", |
| 500 |
WPF()->tables->ai_embeddings => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->ai_embeddings . "` ( |
| 501 |
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 502 |
`topicid` BIGINT UNSIGNED NOT NULL COMMENT 'Parent topic ID', |
| 503 |
`postid` BIGINT UNSIGNED NOT NULL COMMENT 'Post/chunk ID', |
| 504 |
`forumid` INT UNSIGNED NOT NULL COMMENT 'Forum ID for filtering', |
| 505 |
`userid` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Post author ID', |
| 506 |
`embedding_vector` LONGBLOB NOT NULL COMMENT 'Binary packed float32 vector', |
| 507 |
`vector_dimensions` SMALLINT UNSIGNED NOT NULL DEFAULT 1024 COMMENT 'Vector dimensions (256, 512, 1024)', |
| 508 |
`vector_magnitude` DOUBLE NOT NULL DEFAULT 1.0 COMMENT 'Pre-computed magnitude for optimization', |
| 509 |
`model_name` VARCHAR(100) NOT NULL DEFAULT 'amazon.titan-embed-text-v2' COMMENT 'Embedding model used', |
| 510 |
`content_hash` VARCHAR(32) NOT NULL COMMENT 'MD5 hash of content for change detection', |
| 511 |
`content_preview` TEXT DEFAULT NULL COMMENT 'Full chunk content for search results', |
| 512 |
`content_type` VARCHAR(20) NOT NULL DEFAULT 'forum' COMMENT 'forum or WordPress post_type', |
| 513 |
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 514 |
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
| 515 |
PRIMARY KEY (`id`), |
| 516 |
UNIQUE KEY `uk_post_model` (`postid`, `model_name`), |
| 517 |
KEY `idx_topicid` (`topicid`), |
| 518 |
KEY `idx_forumid` (`forumid`), |
| 519 |
KEY `idx_userid` (`userid`), |
| 520 |
KEY `idx_content_hash` (`content_hash`), |
| 521 |
KEY `idx_model_name` (`model_name`), |
| 522 |
KEY `idx_content_type` (`content_type`) |
| 523 |
) ENGINE=InnoDB $charset_collate", |
| 524 |
WPF()->tables->ai_embeddings_cache => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->ai_embeddings_cache . "` ( |
| 525 |
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 526 |
`source_type` VARCHAR(20) NOT NULL COMMENT 'topic or post', |
| 527 |
`source_id` BIGINT UNSIGNED NOT NULL COMMENT 'Source topic/post ID', |
| 528 |
`similar_type` VARCHAR(20) NOT NULL COMMENT 'topic or post', |
| 529 |
`similar_id` BIGINT UNSIGNED NOT NULL COMMENT 'Similar item ID', |
| 530 |
`similarity_score` FLOAT NOT NULL COMMENT 'Cosine similarity score 0.0-1.0', |
| 531 |
`rank_position` TINYINT UNSIGNED NOT NULL COMMENT 'Rank 1-20', |
| 532 |
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 533 |
`expires_at` DATETIME NOT NULL COMMENT 'Cache expiry time', |
| 534 |
PRIMARY KEY (`id`), |
| 535 |
UNIQUE KEY `uk_source_similar` (`source_type`, `source_id`, `similar_type`, `similar_id`), |
| 536 |
KEY `idx_source` (`source_type`, `source_id`, `rank_position`), |
| 537 |
KEY `idx_expires` (`expires_at`), |
| 538 |
KEY `idx_similar` (`similar_type`, `similar_id`) |
| 539 |
) ENGINE=InnoDB $charset_collate", |
| 540 |
WPF()->tables->ai_logs => "CREATE TABLE IF NOT EXISTS `" . WPF()->tables->ai_logs . "` ( |
| 541 |
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
| 542 |
`action_type` VARCHAR(50) NOT NULL COMMENT 'AI action type: semantic_search, translation, bot_reply, etc.', |
| 543 |
`userid` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'User ID (0 for guests/cron/system)', |
| 544 |
`user_type` VARCHAR(20) NOT NULL DEFAULT 'user' COMMENT 'user, guest, cron, system', |
| 545 |
`credits_used` INT UNSIGNED NOT NULL DEFAULT 0, |
| 546 |
`status` VARCHAR(20) NOT NULL DEFAULT 'success' COMMENT 'success, error, cached', |
| 547 |
`content_type` VARCHAR(20) DEFAULT NULL COMMENT 'topic, post, etc.', |
| 548 |
`content_id` BIGINT UNSIGNED DEFAULT NULL, |
| 549 |
`forumid` INT UNSIGNED DEFAULT NULL, |
| 550 |
`topicid` BIGINT UNSIGNED DEFAULT NULL, |
| 551 |
`request_summary` TEXT COMMENT 'Sanitized request summary', |
| 552 |
`response_summary` TEXT COMMENT 'Truncated response summary', |
| 553 |
`error_message` TEXT DEFAULT NULL, |
| 554 |
`duration_ms` INT UNSIGNED NOT NULL DEFAULT 0, |
| 555 |
`ip_address` VARCHAR(45) DEFAULT NULL, |
| 556 |
`extra_data` LONGTEXT COMMENT 'JSON with additional context', |
| 557 |
`created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 558 |
PRIMARY KEY (`id`), |
| 559 |
KEY `idx_action_type` (`action_type`), |
| 560 |
KEY `idx_userid` (`userid`), |
| 561 |
KEY `idx_created` (`created`), |
| 562 |
KEY `idx_status` (`status`), |
| 563 |
KEY `idx_content` (`content_type`, `content_id`), |
| 564 |
KEY `idx_composite` (`action_type`, `created`, `status`) |
| 565 |
) ENGINE=InnoDB $charset_collate", |
| 566 |
]; |
| 567 |
} |
| 568 |
} |
| 569 |
|
