· 6 years ago · Mar 13, 2020, 03:12 PM
1create table invblog_blogs
2(
3 blog_id int(10) auto_increment
4 primary key,
5 blog_member_id bigint unsigned default 0 not null,
6 blog_num_views int(10) default 0 null,
7 blog_pinned tinyint(1) default 0 not null,
8 blog_disabled tinyint(1) default 0 not null,
9 blog_allowguests tinyint(1) default 1 not null,
10 blog_rating_total int(10) default 0 null,
11 blog_rating_count int(10) default 0 null,
12 blog_settings text null,
13 blog_last_visitors text null,
14 blog_seo_name varchar(255) null,
15 blog_editors int(3) null,
16 blog_groupblog_ids varchar(255) default '' not null,
17 blog_last_edate int(10) default 0 not null,
18 blog_count_entries int(11) unsigned default 0 not null,
19 blog_count_comments int(11) unsigned default 0 not null,
20 blog_count_entries_hidden int(11) unsigned default 0 not null,
21 blog_count_comments_hidden int(11) unsigned default 0 not null,
22 blog_rating_average decimal(11, 1) unsigned default 0.0 not null,
23 blog_cover_photo text null,
24 blog_cover_photo_offset int(10) null,
25 blog_social_group int unsigned null comment 'The social group ID, is this is a private blog',
26 blog_count_entries_future int unsigned default 0 not null,
27 blog_name text null,
28 blog_desc text null,
29 blog_club_id bigint unsigned null comment 'The club ID if this blog belongs to a club, or NULL',
30 blog_sidebar text null
31)
32 collate = utf8mb4_unicode_ci;
33
34create index as_list_view
35 on invblog_blogs (blog_pinned, blog_disabled, blog_allowguests, blog_last_edate);
36
37create index blog_grabber
38 on invblog_blogs (blog_disabled);
39
40create index blog_last_edate
41 on invblog_blogs (blog_last_edate);
42
43create index blog_member_id
44 on invblog_blogs (blog_member_id);
45
46create index blog_pinned
47 on invblog_blogs (blog_pinned);
48
49create table invblog_comments
50(
51 comment_id int(10) auto_increment
52 primary key,
53 comment_entry_id int(10) default 0 not null,
54 comment_member_id bigint unsigned null,
55 comment_member_name varchar(255) null,
56 comment_ip_address varchar(46) null,
57 comment_date int(10) null,
58 comment_edit_time int(10) null,
59 comment_text text null,
60 comment_approved int(1) default 0 null,
61 comment_edit_member_name varchar(255) null,
62 comment_edit_show tinyint(1) unsigned default 0 not null
63)
64 collate = utf8mb4_unicode_ci;
65
66create index comment_approved
67 on invblog_comments (comment_approved);
68
69create index comment_member_id
70 on invblog_comments (comment_member_id, comment_date);
71
72create index entry_comment
73 on invblog_comments (comment_id, comment_entry_id);
74
75create index entry_id
76 on invblog_comments (comment_entry_id, comment_approved);
77
78create index ip_lookup
79 on invblog_comments (comment_ip_address);
80
81create table invblog_entries
82(
83 entry_id int(10) auto_increment
84 primary key,
85 entry_blog_id int(10) default 0 not null,
86 entry_author_id bigint default 0 not null,
87 entry_author_name varchar(255) default '' not null,
88 entry_date int(10) null,
89 entry_name varchar(255) null,
90 entry_content mediumtext null,
91 entry_status varchar(10) null,
92 entry_locked tinyint(1) default 0 null,
93 entry_num_comments int(10) default 0 null,
94 entry_last_comment_mid bigint null,
95 entry_queued_comments smallint(5) default 0 not null,
96 entry_post_key varchar(32) null,
97 entry_edit_time int(10) null,
98 entry_edit_name varchar(255) null,
99 entry_last_update int(10) default 0 null,
100 entry_gallery_album int(10) default 0 null,
101 entry_poll_state int(8) unsigned null,
102 entry_featured tinyint(1) default 0 not null,
103 entry_name_seo varchar(255) null,
104 entry_publish_date int unsigned default 0 not null,
105 entry_image varchar(255) default '' not null,
106 entry_views int(10) default 0 not null,
107 entry_hidden tinyint(1) default 0 not null,
108 entry_pinned tinyint(1) unsigned default 0 not null,
109 entry_ip_address varchar(46) default '0' null,
110 entry_cover_photo text null,
111 entry_cover_photo_offset int unsigned null,
112 entry_hidden_comments int unsigned null,
113 entry_is_future_entry tinyint(1) unsigned default 0 not null comment 'Flag to show if an entry is set to be published in the future via the task',
114 entry_meta_data bit default b'0' not null
115)
116 collate = utf8mb4_unicode_ci;
117
118create index entry_author_id
119 on invblog_entries (entry_author_id, entry_last_update);
120
121create index entry_blog_id
122 on invblog_entries (entry_blog_id, entry_status, entry_date);
123
124create index entry_date
125 on invblog_entries (entry_date);
126
127create index entry_featured
128 on invblog_entries (entry_featured);
129
130create index entry_future_date
131 on invblog_entries (entry_publish_date, entry_date);
132
133create index entry_last_update
134 on invblog_entries (entry_blog_id, entry_status, entry_last_update);
135
136create index entry_status
137 on invblog_entries (entry_status, entry_last_update);
138
139create index ip_lookup
140 on invblog_entries (entry_ip_address);
141
142create table invblog_rss_import
143(
144 rss_id int(10) auto_increment
145 primary key,
146 rss_blog_id int(10) default 0 not null,
147 rss_url varchar(255) default '' not null,
148 rss_auth_user varchar(255) default '' not null,
149 rss_auth_pass varchar(255) default '' not null,
150 rss_last_import int(10) default 0 not null,
151 rss_tags text null,
152 rss_member bigint unsigned default 0 not null,
153 rss_import_show_link varchar(255) null comment 'Text to show for link to full article'
154)
155 collate = utf8mb4_unicode_ci;
156
157create index blog_time
158 on invblog_rss_import (rss_blog_id, rss_last_import);
159
160create index member
161 on invblog_rss_import (rss_member);
162
163create index rss_last_import
164 on invblog_rss_import (rss_last_import);
165
166create table invblog_rss_imported
167(
168 rss_imported_guid char(32) default '0' not null
169 primary key,
170 rss_imported_entry_id int(10) default 0 not null,
171 rss_imported_impid int(10) default 0 not null
172)
173 collate = utf8mb4_unicode_ci;
174
175create index rss_imported_impid
176 on invblog_rss_imported (rss_imported_impid);
177
178create table invcalendar_calendars
179(
180 cal_id int unsigned auto_increment
181 primary key,
182 cal_moderate tinyint(1) default 0 not null,
183 cal_position int(3) default 0 not null,
184 cal_title_seo varchar(255) null,
185 cal_comment_moderate tinyint(1) default 0 not null,
186 cal_color varchar(7) null comment 'Hex color code to represent this calendar',
187 cal_allow_comments tinyint(1) unsigned default 1 not null,
188 cal_allow_reviews tinyint(1) unsigned default 0 not null,
189 cal_review_moderate tinyint(1) unsigned default 0 not null,
190 cal_club_id bigint unsigned null comment 'The club ID if this calendar belongs to a club, or NULL',
191 cal_calendar_bitoptions int unsigned default 0 not null
192)
193 collate = utf8mb4_unicode_ci;
194
195create table invcalendar_event_comments
196(
197 comment_id int(10) auto_increment
198 primary key,
199 comment_eid int(10) default 0 not null,
200 comment_mid bigint unsigned default 0 not null,
201 comment_date int default 0 not null,
202 comment_approved tinyint(1) default 0 not null,
203 comment_text mediumtext null,
204 comment_append_edit tinyint(1) default 0 not null,
205 comment_edit_time int default 0 not null,
206 comment_edit_name varchar(255) null,
207 comment_ip_address varchar(46) null,
208 comment_author varchar(255) null
209)
210 collate = utf8mb4_unicode_ci;
211
212create index comment_approved
213 on invcalendar_event_comments (comment_approved);
214
215create index comment_eid
216 on invcalendar_event_comments (comment_eid);
217
218create index comment_mid
219 on invcalendar_event_comments (comment_mid);
220
221create index ip_address
222 on invcalendar_event_comments (comment_ip_address);
223
224create table invcalendar_event_reminders
225(
226 reminder_id bigint unsigned auto_increment comment 'ID Number'
227 primary key,
228 reminder_event_id bigint(10) unsigned null comment 'Event to remind about',
229 reminder_member_id bigint unsigned not null comment 'Member to notify',
230 reminder_date int unsigned not null comment 'Timestamp after which to send notification',
231 reminder_days_before int(2) unsigned not null
232)
233 collate = utf8mb4_unicode_ci;
234
235create index date
236 on invcalendar_event_reminders (reminder_date);
237
238create index member
239 on invcalendar_event_reminders (reminder_member_id);
240
241create index reminder
242 on invcalendar_event_reminders (reminder_event_id, reminder_member_id);
243
244create table invcalendar_event_reviews
245(
246 review_id bigint unsigned auto_increment comment 'ID Number'
247 primary key,
248 review_eid int unsigned default 0 not null comment 'The event ID',
249 review_mid bigint unsigned default 0 not null comment 'The member ID of the review author',
250 review_text mediumtext null comment 'The review contents',
251 review_append_edit tinyint(1) unsigned default 0 not null comment 'Boolean indicating if edit message should show',
252 review_edit_time int unsigned null comment 'Unix timestamp of when the review was last edited',
253 review_edit_name varchar(255) null comment 'Username of user who last edited review',
254 review_date int(10) null comment 'Unix timestamp of when review was made',
255 review_ip varchar(46) default '' not null comment 'IP address review was made from',
256 review_author_name varchar(255) default '' not null comment 'Username of user who made review',
257 review_rating tinyint(1) unsigned null comment 'The rating (out of 5) with the review',
258 review_votes int unsigned default 0 not null comment 'The number of people who have voted "helpful" or "unhelpful"',
259 review_votes_helpful int unsigned default 0 not null comment 'The number of people who have voted "helpful"',
260 review_votes_data text null comment 'JSON object containing data about who has voted helpful/unhelpful ',
261 review_approved tinyint(1) default 1 not null comment 'Review is approved?',
262 review_author_response mediumtext null
263)
264 collate = utf8mb4_unicode_ci;
265
266create index review_approved
267 on invcalendar_event_reviews (review_approved);
268
269create index review_eid
270 on invcalendar_event_reviews (review_eid);
271
272create index review_ip
273 on invcalendar_event_reviews (review_ip);
274
275create index review_mid
276 on invcalendar_event_reviews (review_mid, review_date);
277
278create table invcalendar_event_rsvp
279(
280 rsvp_id int auto_increment
281 primary key,
282 rsvp_event_id int default 0 not null,
283 rsvp_member_id bigint unsigned default 0 not null,
284 rsvp_date int default 0 not null,
285 rsvp_response tinyint unsigned default 0 not null comment '0=No, 1=Yes, 2=Maybe'
286)
287 collate = utf8mb4_unicode_ci;
288
289create index rsvp_event_id
290 on invcalendar_event_rsvp (rsvp_event_id);
291
292create index rsvp_member_id
293 on invcalendar_event_rsvp (rsvp_member_id);
294
295create index rsvp_search
296 on invcalendar_event_rsvp (rsvp_date, rsvp_member_id);
297
298create table invcalendar_events
299(
300 event_id int unsigned auto_increment
301 primary key,
302 event_calendar_id int unsigned default 0 not null,
303 event_member_id bigint unsigned default 0 not null,
304 event_content mediumtext null,
305 event_title varchar(255) default '' not null,
306 event_comments int default 0 not null,
307 event_rsvp tinyint(1) default 0 not null,
308 event_approved tinyint(1) default 0 not null,
309 event_saved int unsigned default 0 not null,
310 event_lastupdated int unsigned default 0 not null,
311 event_recurring text null comment 'Holds an RRULE value to indicate how the event recurs',
312 event_start_date datetime not null,
313 event_end_date datetime null,
314 event_title_seo varchar(255) null,
315 event_rating smallint unsigned default 0 not null,
316 event_post_key varchar(32) null,
317 event_sequence int default 0 not null,
318 event_all_day tinyint default 0 not null,
319 event_reviews int unsigned default 0 not null,
320 event_ip_address varchar(46) null,
321 event_locked tinyint(1) unsigned default 0 not null comment 'Whether the event is locked (for new comments/reviews) or not',
322 event_featured tinyint(1) unsigned default 0 not null comment 'Whether the event is featured or not',
323 event_last_comment int(11) unsigned null,
324 event_last_review int(11) unsigned null,
325 event_approved_by bigint unsigned null comment 'Member ID who approved the event',
326 event_approved_on int(11) unsigned null comment 'Date the event was approved on',
327 event_location text null comment 'Stores location data for the event, used to create a map',
328 event_rsvp_limit int(10) null comment 'Maximum number of attendees allowed for the event',
329 event_cover_photo varchar(255) null comment 'Header background image for the event',
330 event_album mediumint unsigned null comment 'Holds album ID associated with this event',
331 event_cover_offset int unsigned default 0 not null,
332 event_queued_comments int unsigned null,
333 event_hidden_comments int unsigned null,
334 event_unapproved_reviews int unsigned null,
335 event_hidden_reviews int unsigned null,
336 event_venue int(5) unsigned null,
337 event_meta_data bit default b'0' not null
338)
339 collate = utf8mb4_unicode_ci;
340
341create index approved
342 on invcalendar_events (event_calendar_id, event_approved, event_start_date, event_end_date);
343
344create index event_album
345 on invcalendar_events (event_album, event_approved, event_saved);
346
347create index event_featured
348 on invcalendar_events (event_featured, event_approved, event_calendar_id);
349
350create index event_member_id
351 on invcalendar_events (event_member_id, event_approved, event_lastupdated);
352
353create index event_venue
354 on invcalendar_events (event_venue);
355
356create index ip_lookup
357 on invcalendar_events (event_ip_address);
358
359create index recurring_events
360 on invcalendar_events (event_recurring, event_approved, event_calendar_id);
361
362create table invcalendar_import_feeds
363(
364 feed_id int auto_increment
365 primary key,
366 feed_title varchar(255) null,
367 feed_url text null,
368 feed_added int default 0 not null,
369 feed_lastupdated int default 0 not null,
370 feed_calendar_id int default 0 not null,
371 feed_member_id bigint unsigned default 0 not null,
372 feed_last_run int default 0 not null,
373 feed_allow_rsvp tinyint(1) unsigned default 0 not null,
374 feed_venue_id int(5) unsigned null
375)
376 collate = utf8mb4_unicode_ci;
377
378create index feed_calendar_id
379 on invcalendar_import_feeds (feed_calendar_id);
380
381create index feed_last_run
382 on invcalendar_import_feeds (feed_last_run);
383
384create index member
385 on invcalendar_import_feeds (feed_member_id);
386
387create table invcalendar_import_map
388(
389 import_id int auto_increment
390 primary key,
391 import_feed_id int default 0 not null,
392 import_event_id int default 0 not null,
393 import_guid varchar(255) default '0' not null
394)
395 collate = utf8mb4_unicode_ci;
396
397create index import_event_id
398 on invcalendar_import_map (import_event_id);
399
400create index import_feed_id
401 on invcalendar_import_map (import_feed_id);
402
403create index import_guid
404 on invcalendar_import_map (import_guid);
405
406create table invcalendar_venues
407(
408 venue_id bigint unsigned auto_increment comment 'ID Number'
409 primary key,
410 venue_position int(5) unsigned null,
411 venue_enabled tinyint(1) unsigned default 1 null,
412 venue_title_seo varchar(255) null,
413 venue_address text null comment 'Physical Address'
414)
415 collate = utf8mb4_unicode_ci;
416
417create table invclubsplus_socials
418(
419 id bigint unsigned auto_increment comment 'ID Number'
420 primary key,
421 name varchar(255) null,
422 image varchar(255) null,
423 link varchar(255) null,
424 link_type varchar(255) null,
425 icon_type varchar(255) null,
426 class varchar(255) null,
427 icon_hex varchar(255) null,
428 placeholder varchar(255) null,
429 description varchar(255) null,
430 category varchar(255) null,
431 weight int(10) default 0 not null
432)
433 collate = utf8mb4_unicode_ci;
434
435create table invclubsplus_tags
436(
437 clubsplus_tag_id bigint unsigned auto_increment comment 'ID Number'
438 primary key,
439 clubsplus_tag_name varchar(255) null,
440 clubsplus_tag_image varchar(255) null,
441 clubsplus_tag_color_start varchar(255) null,
442 clubsplus_tag_color_mid varchar(255) null,
443 clubsplus_tag_color_end varchar(255) null,
444 clubsplus_tag_weight int(10) default 0 not null
445)
446 collate = utf8mb4_unicode_ci;
447
448create table invcms_blocks
449(
450 block_id mediumint auto_increment
451 primary key,
452 block_active tinyint(1) default 0 not null,
453 block_key varchar(255) default '' not null,
454 block_template varchar(255) default '0' not null,
455 block_template_params text null comment 'Vars for any ad-hoc template',
456 block_type varchar(32) default '' not null,
457 block_config text null,
458 block_content mediumtext null,
459 block_position mediumint default 0 not null,
460 block_category mediumint default 0 not null,
461 block_plugin varchar(255) null,
462 block_plugin_config mediumtext null,
463 block_plugin_app varchar(100) null,
464 block_cache tinyint(1) unsigned default 0 not null,
465 block_plugin_plugin int unsigned null
466)
467 collate = utf8mb4_unicode_ci;
468
469create index block_active
470 on invcms_blocks (block_active);
471
472create index block_category
473 on invcms_blocks (block_category);
474
475create index block_key
476 on invcms_blocks (block_key);
477
478create index block_template
479 on invcms_blocks (block_template);
480
481create table invcms_containers
482(
483 container_id int auto_increment
484 primary key,
485 container_name varchar(255) null,
486 container_type varchar(32) default '' not null,
487 container_order mediumint default 0 not null,
488 container_parent_id int unsigned default 0 null,
489 container_key varchar(150) null
490)
491 collate = utf8mb4_unicode_ci;
492
493create index container_parent_id
494 on invcms_containers (container_parent_id);
495
496create index container_type
497 on invcms_containers (container_type, container_order);
498
499create table invcms_custom_database_1
500(
501 primary_id_field int auto_increment
502 primary key,
503 member_id int default 0 not null,
504 record_saved int(10) default 0 not null,
505 record_updated int(10) default 0 not null,
506 post_key varchar(32) default '0' not null,
507 rating_real smallint default 0 not null,
508 rating_hits mediumint default 0 not null,
509 rating_value mediumint default 0 not null,
510 category_id int default 0 not null,
511 record_future_date tinyint(1) default 0 not null,
512 record_locked tinyint(1) default 0 not null,
513 record_comments mediumint default 0 not null,
514 record_comments_queued mediumint default 0 not null,
515 record_comments_hidden mediumint(9) unsigned null,
516 record_views int(10) default 0 not null,
517 record_approved tinyint(1) default 0 not null,
518 record_pinned tinyint(1) default 0 not null,
519 record_dynamic_furl varchar(255) null,
520 record_static_furl varchar(255) null,
521 record_meta_keywords text null,
522 record_meta_description text null,
523 record_template int default 0 not null,
524 record_topicid int default 0 not null,
525 record_publish_date int(10) default 0 null,
526 record_expiry_date int(10) default 0 null,
527 record_comment_cutoff int(10) default 0 null,
528 record_on_homepage tinyint(1) default 0 null,
529 record_allow_comments tinyint(1) default 0 null,
530 record_allow_reviews tinyint(1) default 0 null,
531 record_featured tinyint(1) unsigned default 0 null,
532 record_last_comment int unsigned null,
533 record_last_comment_by int unsigned null,
534 record_last_comment_name varchar(255) null,
535 record_image text null comment 'Record image',
536 record_image_thumb text null comment 'Record image thumb',
537 record_last_review int unsigned default 0 null,
538 record_last_review_by int unsigned default 0 null,
539 record_last_review_name varchar(255) null,
540 record_reviews mediumint default 0 not null,
541 record_reviews_queued mediumint default 0 not null,
542 record_rating smallint default 0 not null,
543 record_edit_reason varchar(255) null,
544 record_edit_time int unsigned default 0 null,
545 record_edit_member_id int unsigned default 0 null,
546 record_edit_member_name varchar(255) null,
547 record_edit_show int unsigned default 0 null,
548 record_meta_data bit default b'0' not null,
549 field_1 varchar(255) null,
550 field_2 mediumtext null
551)
552 collate = utf8mb4_unicode_ci;
553
554create index category_id
555 on invcms_custom_database_1 (category_id);
556
557create index content_look_up
558 on invcms_custom_database_1 (category_id, record_approved, member_id, record_last_comment, record_last_review);
559
560create index listing_look_up
561 on invcms_custom_database_1 (category_id, record_future_date, record_approved, record_locked);
562
563create index record_approved
564 on invcms_custom_database_1 (record_approved);
565
566create index record_static_furl
567 on invcms_custom_database_1 (record_static_furl);
568
569create index record_topicid
570 on invcms_custom_database_1 (record_topicid);
571
572create table invcms_database_categories
573(
574 category_id int auto_increment
575 primary key,
576 category_database_id mediumint default 0 not null,
577 category_name varchar(255) null,
578 category_parent_id int default 0 not null,
579 category_last_record_id int default 0 not null,
580 category_last_record_date int default 0 not null,
581 category_last_record_member bigint unsigned default 0 not null,
582 category_last_record_name varchar(255) null,
583 category_last_record_seo_name varchar(255) null,
584 category_position int default 0 not null,
585 category_records int default 0 not null,
586 category_records_queued int default 0 not null,
587 category_record_comments int default 0 not null,
588 category_record_comments_queued int default 0 not null,
589 category_has_perms tinyint(1) default 0 not null,
590 category_show_records tinyint(1) default 1 not null,
591 category_furl_name varchar(255) null,
592 category_meta_keywords text null,
593 category_meta_description text null,
594 category_forum_override tinyint(1) default 0 not null,
595 category_forum_record tinyint(1) default 0 not null,
596 category_forum_comments tinyint(1) default 0 not null,
597 category_forum_delete tinyint(1) default 0 not null,
598 category_forum_forum mediumint default 0 not null,
599 category_forum_prefix varchar(255) null,
600 category_forum_suffix varchar(255) null,
601 category_page_title varchar(255) null,
602 category_full_path text null,
603 category_last_title varchar(255) null,
604 category_last_seo_title varchar(255) null,
605 category_allow_rating tinyint(1) unsigned default 0 not null,
606 category_fields text null comment 'JSON object of permitted form fields to show.',
607 category_records_future int default 0 not null,
608 category_record_reviews int unsigned default 0 not null,
609 category_record_reviews_queued int unsigned default 0 not null,
610 category_options int(10) default 0 not null,
611 category_template_listing varchar(250) null,
612 category_template_display varchar(250) null,
613 category_can_view_others tinyint(1) unsigned default 1 not null
614)
615 collate = utf8mb4_unicode_ci;
616
617create index category_database_id
618 on invcms_database_categories (category_database_id);
619
620create index category_select
621 on invcms_database_categories (category_parent_id, category_position);
622
623create table invcms_database_comments
624(
625 comment_id int auto_increment
626 primary key,
627 comment_user int default 0 not null,
628 comment_database_id mediumint default 0 not null,
629 comment_record_id int default 0 not null,
630 comment_date int default 0 not null,
631 comment_ip_address varchar(46) default '0' not null,
632 comment_post text null,
633 comment_approved tinyint(1) default 0 not null,
634 comment_author varchar(255) null,
635 comment_edit_date int default 0 not null,
636 comment_edit_reason varchar(255) null,
637 comment_edit_member_name varchar(255) null,
638 comment_edit_member_id bigint unsigned default 0 not null,
639 comment_edit_show smallint(1) unsigned default 0 not null
640)
641 collate = utf8mb4_unicode_ci;
642
643create index comment_approved
644 on invcms_database_comments (comment_approved);
645
646create index comment_database_id
647 on invcms_database_comments (comment_database_id, comment_record_id, comment_date);
648
649create index comment_ip_address
650 on invcms_database_comments (comment_ip_address);
651
652create index comment_user
653 on invcms_database_comments (comment_user);
654
655create table invcms_database_fields
656(
657 field_id int auto_increment
658 primary key,
659 field_database_id mediumint default 0 not null,
660 field_key varchar(255) default '' not null,
661 field_type varchar(255) null,
662 field_required tinyint(1) default 0 not null,
663 field_user_editable tinyint(1) default 0 not null,
664 field_position int default 0 not null,
665 field_max_length mediumint default 0 not null,
666 field_extra text null,
667 field_html tinyint default 0 not null,
668 field_truncate mediumint default 100 not null,
669 field_default_value text null,
670 field_display_listing tinyint(1) default 1 not null,
671 field_display_display tinyint(1) default 1 not null,
672 field_format_opts text null,
673 field_validator text null,
674 field_topic_format text null,
675 field_filter tinyint(1) default 0 not null,
676 field_allowed_extensions varchar(255) null,
677 field_is_multiple tinyint(1) default 0 not null,
678 field_is_searchable tinyint(1) default 1 null,
679 field_validator_custom text null,
680 field_display_commentform tinyint(1) unsigned default 0 null comment 'Determines if this field should show and be editable on the comment reply form.',
681 field_display_json mediumtext null comment 'JSON array of custom display HTML',
682 field_unique tinyint(1) unsigned default 0 not null,
683 field_allow_attachments tinyint(1) unsigned default 1 not null
684)
685 collate = utf8mb4_unicode_ci;
686
687create index field_database_id
688 on invcms_database_fields (field_database_id);
689
690create index field_key
691 on invcms_database_fields (field_key);
692
693create table invcms_database_fields_reciprocal_map
694(
695 map_origin_database_id int unsigned default 0 not null,
696 map_foreign_database_id int unsigned default 0 not null,
697 map_origin_item_id int unsigned default 0 not null,
698 map_foreign_item_id int unsigned default 0 not null,
699 map_field_id int unsigned default 0 not null
700)
701 collate = utf8mb4_unicode_ci;
702
703create index map_foreign
704 on invcms_database_fields_reciprocal_map (map_foreign_database_id, map_foreign_item_id);
705
706create index map_origin
707 on invcms_database_fields_reciprocal_map (map_origin_database_id, map_origin_item_id);
708
709create table invcms_database_fields_thumbnails
710(
711 thumb_original_location varchar(255) default '' not null
712 primary key,
713 thumb_location varchar(255) default '' not null,
714 thumb_field_id int(10) default 0 not null,
715 thumb_database_id int(10) default 0 not null,
716 thumb_record_id int unsigned default 0 not null
717)
718 collate = utf8mb4_unicode_ci;
719
720create table invcms_database_reviews
721(
722 review_id bigint auto_increment
723 primary key,
724 review_database_id int(10) default 0 not null,
725 review_item int(10) default 0 not null,
726 review_author int(10) default 0 not null,
727 review_author_name varchar(255) default '' not null,
728 review_content mediumtext null,
729 review_date int(10) default 0 not null,
730 review_ip_address varchar(46) default '' not null,
731 review_edit_time int(10) default 0 not null,
732 review_edit_member_name varchar(255) default '' not null,
733 review_edit_show int(1) default 0 not null,
734 review_rating int(10) default 0 not null,
735 review_votes_total int(10) default 0 not null,
736 review_votes_helpful int(10) default 0 not null,
737 review_votes_data text null,
738 review_approved int(1) default 0 not null,
739 review_author_response mediumtext null
740)
741 collate = utf8mb4_unicode_ci;
742
743create index review_approved
744 on invcms_database_reviews (review_approved);
745
746create index review_author
747 on invcms_database_reviews (review_author, review_date);
748
749create index review_database_id
750 on invcms_database_reviews (review_database_id);
751
752create index review_ip
753 on invcms_database_reviews (review_ip_address);
754
755create index review_item
756 on invcms_database_reviews (review_item, review_author);
757
758create table invcms_database_revisions
759(
760 revision_id int auto_increment
761 primary key,
762 revision_database_id mediumint default 0 not null,
763 revision_record_id int default 0 not null,
764 revision_data longtext null,
765 revision_date varchar(13) default '0' not null,
766 revision_member_id bigint default 0 not null
767)
768 collate = utf8mb4_unicode_ci;
769
770create index revision_database_id
771 on invcms_database_revisions (revision_database_id, revision_record_id);
772
773create index revision_member_id
774 on invcms_database_revisions (revision_member_id);
775
776create table invcms_databases
777(
778 database_id mediumint auto_increment
779 primary key,
780 database_key varchar(255) default '' not null,
781 database_record_count mediumint default 0 not null,
782 database_template_listing varchar(250) default 'listing' not null,
783 database_template_display varchar(250) default 'display' not null,
784 database_template_categories varchar(250) default 'category_index' not null,
785 database_all_editable tinyint(1) default 0 not null,
786 database_revisions tinyint(1) default 0 not null,
787 database_field_title varchar(255) null,
788 database_field_sort varchar(255) null,
789 database_field_direction varchar(4) default 'desc' not null,
790 database_field_perpage smallint default 25 not null,
791 database_comment_approve tinyint(1) default 0 not null,
792 database_record_approve tinyint(1) default 0 not null,
793 database_rss int default 0 not null,
794 database_field_content varchar(255) null,
795 database_comment_bump tinyint(1) default 0 not null,
796 database_forum_record tinyint(1) default 0 not null,
797 database_forum_comments tinyint(1) default 0 not null,
798 database_forum_delete tinyint(1) default 0 not null,
799 database_forum_forum mediumint default 0 not null,
800 database_forum_prefix varchar(255) null,
801 database_forum_suffix varchar(255) null,
802 database_search tinyint(1) default 0 not null,
803 database_tags_enabled tinyint default 0 not null,
804 database_tags_noprefixes tinyint default 0 not null,
805 database_tags_predefined text null,
806 database_fixed_field_perms text null comment 'Contains a JSON object of fixed field permissions.',
807 database_cat_index_type tinyint(1) unsigned default 0 not null,
808 database_page_id int unsigned default 0 not null comment 'Page ID this database is used on (if any)',
809 database_template_form varchar(250) default 'form' null,
810 database_template_featured varchar(250) default 'category_featured' not null,
811 database_featured_settings mediumtext null,
812 database_use_categories tinyint(1) unsigned default 1 not null,
813 database_default_category int(10) default 0 not null,
814 database_options int(10) default 0 not null,
815 database_fixed_field_settings mediumtext null,
816 database_use_as_page_title tinyint(1) unsigned default 1 not null comment 'Use database name as page title, or retain page title',
817 database_canonical_flag tinyint(1) unsigned default 0 null,
818 constraint database_key
819 unique (database_key)
820)
821 collate = utf8mb4_unicode_ci;
822
823create index forum_lookup
824 on invcms_databases (database_forum_record, database_forum_forum, database_forum_comments);
825
826create table invcms_folders
827(
828 folder_path text null,
829 folder_last_modified int default 0 not null,
830 folder_id int unsigned auto_increment
831 primary key,
832 folder_parent_id int unsigned default 0 not null,
833 folder_name varchar(250) null
834)
835 collate = utf8mb4_unicode_ci;
836
837create index folder_parent_id
838 on invcms_folders (folder_parent_id);
839
840create index folder_path
841 on invcms_folders (folder_path);
842
843create table invcms_media
844(
845 media_id int(10) auto_increment
846 primary key,
847 media_parent int(10) default 0 not null,
848 media_filename varchar(255) default '' not null,
849 media_added int(10) default 0 not null,
850 media_full_path text null,
851 media_file_object text null,
852 media_filename_stored text null,
853 media_is_image tinyint(1) unsigned default 0 not null
854)
855 collate = utf8mb4_unicode_ci;
856
857create table invcms_media_folders
858(
859 media_folder_id int(10) auto_increment
860 primary key,
861 media_folder_parent int(10) default 0 not null,
862 media_folder_name varchar(255) default '' not null,
863 media_folder_path text null
864)
865 collate = utf8mb4_unicode_ci;
866
867create index media_folder_parent
868 on invcms_media_folders (media_folder_parent);
869
870create table invcms_page_widget_areas
871(
872 area_page_id bigint unsigned default 0 not null comment 'The page ID',
873 area_widgets mediumtext null,
874 area_area varchar(255) null,
875 area_orientation enum ('horizontal', 'vertical') default 'horizontal' not null,
876 constraint area_page_area
877 unique (area_page_id, area_area)
878)
879 collate = utf8mb4_unicode_ci;
880
881create table invcms_pages
882(
883 page_id int auto_increment
884 primary key,
885 page_name varchar(255) null,
886 page_seo_name varchar(255) null,
887 page_content mediumtext null,
888 page_type varchar(32) null,
889 page_theme int(8) unsigned default 0 null,
890 page_meta_keywords text null,
891 page_meta_description text null,
892 page_template mediumtext null,
893 page_ipb_wrapper tinyint(1) default 0 not null,
894 page_title varchar(255) null,
895 page_quicknav tinyint default 1 not null,
896 page_folder_id int unsigned default 0 not null,
897 page_full_path text null,
898 page_show_sidebar int(1) unsigned default 0 null comment 'Display the standard suite sidebar',
899 page_default tinyint(1) unsigned default 0 not null,
900 page_js_css_ids mediumtext null comment 'JSON array storing CSS and JS template IDs for inclusion on this page.',
901 page_js_css_objects mediumtext null comment 'JSON array of file objects',
902 page_wrapper_template varchar(255) null,
903 page_has_error tinyint(1) unsigned default 0 not null
904)
905 collate = utf8mb4_unicode_ci;
906
907create index page_default
908 on invcms_pages (page_default, page_folder_id);
909
910create index page_folder
911 on invcms_pages (page_folder_id, page_seo_name);
912
913create index page_full_path
914 on invcms_pages (page_full_path);
915
916create index page_seo_name
917 on invcms_pages (page_seo_name);
918
919create table invcms_templates
920(
921 template_id int(10) auto_increment
922 primary key,
923 template_key varchar(255) null,
924 template_title varchar(255) null,
925 template_desc text null,
926 template_content mediumtext null,
927 template_location varchar(255) null,
928 template_group varchar(255) null,
929 template_container int(10) default 0 null,
930 template_position int(3) default 0 not null,
931 template_rel_id int(10) default 0 not null,
932 template_updated int(10) default 0 not null,
933 template_user_created int(1) default 0 not null,
934 template_user_edited int(1) default 0 not null,
935 template_params text null,
936 template_app varchar(100) null,
937 template_rel_type varchar(150) null,
938 template_master tinyint(1) default 0 not null,
939 template_type set ('css', 'template', 'js') default 'template' not null,
940 template_original_group varchar(255) null,
941 template_file_object text null comment 'The URL for the file object that corresponds with this item.'
942)
943 collate = utf8mb4_unicode_ci;
944
945create index template_key
946 on invcms_templates (template_key);
947
948create index template_location
949 on invcms_templates (template_location, template_user_edited);
950
951create index template_type
952 on invcms_templates (template_type, template_user_edited);
953
954create table invcms_url_store
955(
956 store_path text null comment 'Path part of the URL',
957 store_current_id int unsigned default 0 null,
958 store_type varchar(32) null
959)
960 collate = utf8mb4_unicode_ci;
961
962create table invcore_acp_notifcations_dismissals
963(
964 notification bigint unsigned not null comment 'Notification ID Number',
965 member bigint(10) unsigned not null comment 'Member ID Number',
966 time int unsigned not null comment 'Unix timestamp of when the notification was dismissed',
967 primary key (notification, member)
968)
969 collate = utf8mb4_unicode_ci;
970
971create index member
972 on invcore_acp_notifcations_dismissals (member);
973
974create table invcore_acp_notifications
975(
976 id bigint unsigned auto_increment comment 'ID Number'
977 primary key,
978 app varchar(32) not null comment 'The key of the application this notification belongs to',
979 ext varchar(32) not null comment 'The extension this notification belongs to within the application',
980 sent int unsigned not null comment 'Unix timestamp of when this notification was sent',
981 extra text null comment 'Any additional data about the notification'
982)
983 collate = utf8mb4_unicode_ci;
984
985create table invcore_acp_notifications_preferences
986(
987 member bigint(10) not null comment 'Member ID Number',
988 type varchar(32) not null comment 'Key representing the notification type',
989 view tinyint(1) unsigned default 1 not null comment 'Does the admin want to view this type of notification?',
990 email varchar(32) default 'never' not null comment 'How frequently to email the admin about this notification',
991 primary key (member, type)
992)
993 collate = utf8mb4_unicode_ci;
994
995create table invcore_acp_search_index
996(
997 url varchar(255) default '' not null comment 'The query string',
998 keyword varchar(250) default '' not null comment 'The keyword',
999 app varchar(255) default '' not null comment 'The application key',
1000 lang_key varchar(255) default '' not null comment 'The language key to use for the result link',
1001 restriction varchar(255) null comment 'If applicable, the ACP restriction key that should be checked for this result'
1002)
1003 collate = utf8mb4_unicode_ci;
1004
1005create index app
1006 on invcore_acp_search_index (app);
1007
1008create index keyword
1009 on invcore_acp_search_index (keyword);
1010
1011create table invcore_acp_tab_order
1012(
1013 id bigint unsigned default 0 not null comment 'ID Number',
1014 data mediumtext null,
1015 constraint member_id
1016 unique (id)
1017)
1018 collate = utf8mb4_unicode_ci;
1019
1020create table invcore_acronyms
1021(
1022 a_id int unsigned auto_increment
1023 primary key,
1024 a_short text null,
1025 a_long text null,
1026 a_casesensitive tinyint(1) null
1027)
1028 collate = utf8mb4_unicode_ci;
1029
1030create index a_short
1031 on invcore_acronyms (a_short);
1032
1033create table invcore_admin_login_logs
1034(
1035 admin_id int(10) auto_increment
1036 primary key,
1037 admin_ip_address varchar(46) default '0.0.0.0' not null,
1038 admin_username varchar(40) default '' not null,
1039 admin_time int unsigned default 0 not null,
1040 admin_success int(1) unsigned default 0 not null,
1041 admin_request text null
1042)
1043 collate = utf8mb4_unicode_ci;
1044
1045create index admin_ip_address
1046 on invcore_admin_login_logs (admin_ip_address);
1047
1048create index admin_time
1049 on invcore_admin_login_logs (admin_time);
1050
1051create table invcore_admin_logs
1052(
1053 id bigint auto_increment
1054 primary key,
1055 member_id bigint unsigned default 0 null,
1056 ctime int(10) default 0 null,
1057 note text null,
1058 ip_address varchar(46) null,
1059 appcomponent varchar(255) default '' not null,
1060 module varchar(255) default '' not null,
1061 controller varchar(255) default '' not null,
1062 do varchar(255) default '' null,
1063 lang_key varchar(255) null,
1064 member_name varchar(255) null
1065)
1066 collate = utf8mb4_unicode_ci;
1067
1068create index ctime
1069 on invcore_admin_logs (ctime);
1070
1071create index ip_address
1072 on invcore_admin_logs (ip_address);
1073
1074create index member
1075 on invcore_admin_logs (member_id);
1076
1077create table invcore_admin_permission_rows
1078(
1079 row_id bigint default 0 not null,
1080 row_id_type varchar(13) default 'member' not null,
1081 row_perm_cache mediumtext null,
1082 row_updated int(10) default 0 not null,
1083 primary key (row_id, row_id_type)
1084)
1085 collate = utf8mb4_unicode_ci;
1086
1087create index row_id_type
1088 on invcore_admin_permission_rows (row_id_type);
1089
1090create table invcore_advertisements
1091(
1092 ad_id bigint auto_increment
1093 primary key,
1094 ad_location text not null comment 'Stores the location for the advertisement to display',
1095 ad_html text null,
1096 ad_images text null comment 'Stores a JSON array of images to use for this ad (small, medium and large)',
1097 ad_link text null comment 'Link used by image advertisements',
1098 ad_impressions bigint unsigned default 0 not null comment 'The number of impressions for this ad, only tracked with image ads',
1099 ad_clicks int default 0 not null comment 'The number of clicks for this ad, only tracked with image ads',
1100 ad_exempt text null comment 'JSON array of group IDs exempt from this ad',
1101 ad_active tinyint default 0 not null,
1102 ad_html_https text null comment 'Optional HTTPS version of ad',
1103 ad_start int default 0 not null comment 'Starting timestamp for this ad',
1104 ad_end int default 0 not null comment 'Ending timestamp for this ad',
1105 ad_maximum_value int default 0 not null comment 'Maximum number of clicks or impressions allowed (for image ads only)',
1106 ad_maximum_unit varchar(1) null comment 'Type of maximum value (for image ads only)',
1107 ad_additional_settings text null comment 'JSON array of additional settings',
1108 ad_html_https_set tinyint(1) default 0 null comment 'This column flags if https was "set", even if ad_html_https is empty',
1109 ad_member bigint unsigned null comment 'If created by Nexus, the customer ID',
1110 ad_new_window tinyint(1) default 0 not null comment 'Open advertisement in a new window',
1111 ad_type tinyint default 0 not null comment '1 = HTML, 2 = images, 3 = email',
1112 ad_email_views int null comment 'Tracks the number of times an email containing the ad has been opened/viewed',
1113 ad_image_alt text null comment 'Alt text for web-based image ads, or for plaintext emails'
1114)
1115 collate = utf8mb4_unicode_ci;
1116
1117create index ad_show
1118 on invcore_advertisements (ad_active, ad_start, ad_end);
1119
1120create index ad_type
1121 on invcore_advertisements (ad_type, ad_active);
1122
1123create index member
1124 on invcore_advertisements (ad_member);
1125
1126create table invcore_announcements
1127(
1128 announce_id int unsigned auto_increment
1129 primary key,
1130 announce_title varchar(255) default '' not null,
1131 announce_content text null,
1132 announce_member_id bigint unsigned default 0 not null,
1133 announce_views int unsigned default 0 not null,
1134 announce_start int unsigned default 0 not null,
1135 announce_end int unsigned default 0 not null,
1136 announce_active tinyint(1) default 1 not null,
1137 announce_seo_title varchar(255) default '' not null,
1138 announce_ids varchar(245) null,
1139 announce_app varchar(45) default '*' not null,
1140 announce_location varchar(45) default '*' not null,
1141 announce_permissions text null comment 'Comma-delimited list of Group IDs, or * for all',
1142 announce_page_location varchar(45) default '' not null comment 'Page location to show announcement',
1143 announce_color varchar(45) null comment 'Page top announcement color',
1144 announce_type tinyint(1) default 1 not null comment 'Announcement type 0=None, 1=Content, 2=Url',
1145 announce_url varchar(255) null comment 'Announcement URL'
1146)
1147 collate = utf8mb4_unicode_ci;
1148
1149create index announce_end
1150 on invcore_announcements (announce_end);
1151
1152create index announce_start
1153 on invcore_announcements (announce_start);
1154
1155create index member
1156 on invcore_announcements (announce_member_id);
1157
1158create table invcore_api_keys
1159(
1160 api_id char(32) default '' not null comment 'API Key'
1161 primary key,
1162 api_permissions text null comment 'JSON encoded details of which endpoints the key can access',
1163 api_allowed_ips text null comment 'Comma-delimited allowed IPs'
1164)
1165 collate = utf8mb4_unicode_ci;
1166
1167create table invcore_api_logs
1168(
1169 id bigint unsigned auto_increment comment 'ID Number'
1170 primary key,
1171 endpoint varchar(255) not null comment 'The endpoint that was requested',
1172 method varchar(32) null comment 'The HTTP request method which was used',
1173 api_key char(32) null comment 'The API key that was used',
1174 ip_address varchar(46) null comment 'The IP address that the request came from',
1175 request_data text null comment 'The request body',
1176 response_code char(3) null comment 'The HTTP response code',
1177 response_output mediumtext null comment 'The response output',
1178 date int(10) not null comment 'Timestamp of when request was made',
1179 is_bad_key tinyint(1) unsigned default 0 not null comment 'If this log is for a request with a bad key - used to prevent brute force ',
1180 client_id char(32) null comment 'The OAuth client ID, if applicable',
1181 member_id bigint unsigned null comment 'The authorized use''s member ID, if applicable',
1182 access_token char(97) null comment 'The raw access token that was used'
1183)
1184 collate = utf8mb4_unicode_ci;
1185
1186create index bruteforce
1187 on invcore_api_logs (ip_address, is_bad_key);
1188
1189create index date
1190 on invcore_api_logs (date);
1191
1192create table invcore_applications
1193(
1194 app_id int(10) auto_increment
1195 primary key,
1196 app_author varchar(255) default '' not null,
1197 app_version varchar(255) default '' not null,
1198 app_long_version int(10) default 10000 not null,
1199 app_directory varchar(250) default '' not null,
1200 app_added int(10) default 0 not null,
1201 app_position int(2) default 0 not null,
1202 app_protected int(1) default 0 not null,
1203 app_enabled int(1) default 0 not null,
1204 app_hide_tab tinyint(1) default 0 not null,
1205 app_website varchar(255) null,
1206 app_update_check varchar(255) null,
1207 app_update_last_check int unsigned default 0 not null,
1208 app_update_version text null comment 'Json array of the latest version info returned by the update check URL',
1209 app_disabled_groups text null,
1210 app_disabled_message mediumtext null,
1211 app_diagnostic_url varchar(255) null comment 'The URL to send diagnostic reports to',
1212 app_default tinyint(1) unsigned default 0 null,
1213 app_secure tinyint(1) default 0 not null
1214)
1215 collate = utf8mb4_unicode_ci;
1216
1217create index app_default
1218 on invcore_applications (app_default);
1219
1220create index app_directory
1221 on invcore_applications (app_directory);
1222
1223create index app_position
1224 on invcore_applications (app_position);
1225
1226create table invcore_attachments
1227(
1228 attach_id int(10) auto_increment
1229 primary key,
1230 attach_ext varchar(250) default '' not null,
1231 attach_file varchar(250) default '' not null,
1232 attach_location varchar(250) default '' not null,
1233 attach_thumb_location varchar(250) default '' not null,
1234 attach_thumb_width smallint(5) default 0 not null,
1235 attach_thumb_height smallint(5) default 0 not null,
1236 attach_is_image tinyint(1) default 0 not null,
1237 attach_hits int(10) default 0 not null,
1238 attach_date int(10) default 0 not null,
1239 attach_post_key varchar(32) default '0' not null,
1240 attach_member_id bigint unsigned default 0 not null,
1241 attach_filesize int(10) default 0 not null,
1242 attach_img_width int(5) default 0 not null,
1243 attach_img_height int(5) default 0 not null,
1244 attach_is_archived int(1) default 0 not null
1245)
1246 collate = utf8mb4_unicode_ci;
1247
1248create index attach_location
1249 on invcore_attachments (attach_location);
1250
1251create index attach_mid_size
1252 on invcore_attachments (attach_member_id, attach_filesize);
1253
1254create index attach_post_key
1255 on invcore_attachments (attach_post_key);
1256
1257create index attach_thumb_location
1258 on invcore_attachments (attach_thumb_location);
1259
1260create table invcore_attachments_map
1261(
1262 attachment_id int unsigned default 0 not null comment 'The attachment ID',
1263 location_key varchar(255) default '' not null comment 'The editor location the content claiming this attachment has come from',
1264 id1 bigint unsigned null comment 'Primary ID number for content claiming attachment',
1265 id2 bigint unsigned null comment 'Secondary ID number for content claiming attachment',
1266 temp varchar(32) null comment 'Temporary key for content claiming attachment',
1267 id3 varchar(255) null comment 'Arbitrary data for content claiming attachment'
1268)
1269 collate = utf8mb4_unicode_ci;
1270
1271create index attachment_id
1272 on invcore_attachments_map (attachment_id);
1273
1274create index map_lookup
1275 on invcore_attachments_map (location_key, id1);
1276
1277create index temp_key
1278 on invcore_attachments_map (temp);
1279
1280create table invcore_automatic_moderation_pending
1281(
1282 pending_object_class varchar(150) default '' not null,
1283 pending_object_id bigint default 0 not null,
1284 pending_report_id bigint default 0 not null,
1285 pending_added int(10) default 0 not null,
1286 pending_rule_id int(10) default 0 not null,
1287 constraint pending_unique
1288 unique (pending_object_class, pending_object_id)
1289)
1290 collate = utf8mb4_unicode_ci;
1291
1292create table invcore_automatic_moderation_rules
1293(
1294 rule_id int(10) auto_increment
1295 primary key,
1296 rule_types text null,
1297 rule_points_needed int(10) default 0 not null,
1298 rule_position int(3) default 0 not null,
1299 rule_enabled int(1) default 1 not null,
1300 rule_filters text null
1301)
1302 collate = utf8mb4_unicode_ci;
1303
1304create table invcore_automatic_moderation_types
1305(
1306 type_id int(10) auto_increment
1307 primary key,
1308 type_position int(10) default 0 not null
1309)
1310 collate = utf8mb4_unicode_ci;
1311
1312create table invcore_banfilters
1313(
1314 ban_id int(10) auto_increment
1315 primary key,
1316 ban_type varchar(10) default 'ip' not null,
1317 ban_content varchar(250) null,
1318 ban_date int(10) default 0 not null,
1319 ban_reason varchar(255) null
1320)
1321 collate = utf8mb4_unicode_ci;
1322
1323create index ban_content
1324 on invcore_banfilters (ban_content);
1325
1326create index ban_type
1327 on invcore_banfilters (ban_type);
1328
1329create table invcore_bulk_mail
1330(
1331 mail_id int(10) auto_increment
1332 primary key,
1333 mail_subject varchar(255) default '' not null,
1334 mail_content mediumtext not null,
1335 mail_opts mediumtext null,
1336 mail_start int(10) default 0 not null comment 'Timestamp the bulk mail was created',
1337 mail_updated int(10) default 0 not null comment 'Timestamp last bulk mail batch was sent',
1338 mail_sentto int(10) default 0 not null comment 'The number of users the bulk mail has been sent to',
1339 mail_active tinyint(1) default 0 not null comment 'Whether the bulk mail is currently active or not',
1340 mail_offset int unsigned default 0 not null comment 'The offset for when the next batch is sent '
1341)
1342 collate = utf8mb4_unicode_ci;
1343
1344create index mail_active
1345 on invcore_bulk_mail (mail_active);
1346
1347create index mail_start
1348 on invcore_bulk_mail (mail_start);
1349
1350create table invcore_cache
1351(
1352 cache_key varchar(100) not null comment 'The key',
1353 cache_value longtext not null comment 'The value',
1354 cache_expire int(10) not null comment 'Unix timestamp of when the cache expires',
1355 constraint cache_key
1356 unique (cache_key)
1357)
1358 collate = utf8mb4_unicode_ci;
1359
1360create index cache_expire
1361 on invcore_cache (cache_expire);
1362
1363create index keyexpire
1364 on invcore_cache (cache_key, cache_expire);
1365
1366create table invcore_club_status_replies
1367(
1368 club_reply_id int(10) auto_increment
1369 primary key,
1370 club_reply_status_id int unsigned default 0 not null,
1371 club_reply_member_id bigint unsigned default 0 not null,
1372 club_reply_date int unsigned default 0 not null,
1373 club_reply_content text null,
1374 club_reply_approved tinyint(1) default 0 not null,
1375 club_reply_ip_address varchar(46) null
1376)
1377 collate = utf8mb4_unicode_ci;
1378
1379create index club_reply_date
1380 on invcore_club_status_replies (club_reply_date);
1381
1382create index club_reply_member_id
1383 on invcore_club_status_replies (club_reply_member_id);
1384
1385create index club_reply_status_count
1386 on invcore_club_status_replies (club_reply_status_id, club_reply_member_id);
1387
1388create index club_reply_status_id
1389 on invcore_club_status_replies (club_reply_status_id, club_reply_date);
1390
1391create index ip_lookup
1392 on invcore_club_status_replies (club_reply_ip_address);
1393
1394create table invcore_club_status_updates
1395(
1396 club_status_id int(10) auto_increment
1397 primary key,
1398 club_status_member_id bigint unsigned default 0 not null,
1399 club_status_club_id bigint unsigned default 0 not null,
1400 club_status_date int unsigned default 0 not null,
1401 club_status_content text null,
1402 club_status_replies int unsigned default 0 not null,
1403 club_status_last_ids text null,
1404 club_status_pinned tinyint(1) default 0 not null,
1405 club_status_is_latest int(1) default 0 not null,
1406 club_status_is_locked int(1) default 0 not null,
1407 club_status_hash varchar(32) default '' not null,
1408 club_status_imported int(1) default 0 not null,
1409 club_status_author_id bigint default 0 not null,
1410 club_status_author_ip varchar(46) default '' not null,
1411 club_status_approved int(1) default 1 not null
1412)
1413 collate = utf8mb4_unicode_ci;
1414
1415create index club_status_author_lookup
1416 on invcore_club_status_updates (club_status_author_id, club_status_member_id, club_status_date);
1417
1418create index club_status_date
1419 on invcore_club_status_updates (club_status_date);
1420
1421create index club_status_is_latest
1422 on invcore_club_status_updates (club_status_is_latest, club_status_date);
1423
1424create index club_status_member_id
1425 on invcore_club_status_updates (club_status_member_id, club_status_approved, club_status_date);
1426
1427create index club_status_pinned
1428 on invcore_club_status_updates (club_status_pinned);
1429
1430create index ip_lookup
1431 on invcore_club_status_updates (club_status_author_ip);
1432
1433create index s_hash
1434 on invcore_club_status_updates (club_status_member_id, club_status_hash, club_status_date);
1435
1436create table invcore_clubs
1437(
1438 id bigint unsigned auto_increment comment 'ID Number'
1439 primary key,
1440 name varchar(255) not null comment 'Name of the club',
1441 type enum ('public', 'open', 'closed', 'private', 'readonly') default 'open' not null comment 'Type of club',
1442 created int unsigned not null comment 'Unix timestamp of when the club was created',
1443 members int unsigned default 0 not null comment 'Current number of members',
1444 owner bigint unsigned null comment 'Member ID of club owner',
1445 profile_photo varchar(255) null comment 'The club profile photo',
1446 cover_photo varchar(255) null comment 'The club cover photo',
1447 cover_offset int unsigned null comment 'The position offset for the cover photo',
1448 featured tinyint(1) unsigned default 0 not null comment 'Binary value indicating if club is featured',
1449 location_json text null comment 'Full \IPS\Geolocation JSON value of club location',
1450 location_lat decimal(10, 8) null comment 'The latitude of the club location',
1451 location_long decimal(11, 8) null comment 'The longitude of the club location',
1452 about text null comment 'Blurb about this club',
1453 last_activity int unsigned null comment 'Timestamp of last activity',
1454 content int unsigned default 0 not null comment 'Count of all content items + comments',
1455 rebuilt int unsigned null comment 'Timestamp of when the last_activity and content values were last updated',
1456 approved tinyint(1) unsigned default 0 not null comment 'Boolean indicating if club is approved',
1457 profile_photo_uncropped varchar(255) null comment 'The uncropped club photo',
1458 fee text null comment 'JSON object with costs if this is a paid club',
1459 renewal_term int(5) unsigned null comment 'The renewal term in renewal_units (e.g. if renews every 5 days, value will be 5)',
1460 renewal_units char null comment 'The renewal units - "d" for days, etc.',
1461 renewal_price text null comment 'JSON object with renewal costs if this is a paid club',
1462 menu_tabs mediumtext null comment 'Club Menu sortorder',
1463 post_mod_perm tinyint(1) default 1 null,
1464 socials text null,
1465 platform text null,
1466 games text null,
1467 tags text null,
1468 brand_photo text null,
1469 owner_post_as tinyint(1) default 0 null,
1470 discord_widget varchar(255) null,
1471 discord_invite varchar(255) null
1472)
1473 collate = utf8mb4_unicode_ci;
1474
1475create index approved
1476 on invcore_clubs (approved);
1477
1478create index club_featured
1479 on invcore_clubs (featured);
1480
1481create index created
1482 on invcore_clubs (created);
1483
1484create index location_lat
1485 on invcore_clubs (location_lat);
1486
1487create index members
1488 on invcore_clubs (members);
1489
1490create index owner
1491 on invcore_clubs (owner);
1492
1493create table invcore_clubs_fields
1494(
1495 f_id bigint unsigned auto_increment comment 'ID Number'
1496 primary key,
1497 f_type varchar(32) null comment 'The field type',
1498 f_extra text null comment 'Extra details such as options',
1499 f_position int unsigned null comment 'Position order',
1500 f_required tinyint(1) unsigned default 0 not null comment 'Binary value indicating if the field is required',
1501 f_multiple tinyint(1) unsigned null comment 'For certain types of fields, if multiple values are supported',
1502 f_filterable tinyint(1) unsigned default 1 not null comment 'Binary value indicating if the field should be included in the filter options',
1503 f_allow_attachments tinyint(1) unsigned default 1 not null
1504)
1505 collate = utf8mb4_unicode_ci;
1506
1507create table invcore_clubs_fieldvalues
1508(
1509 club_id bigint unsigned auto_increment comment 'Club ID Number'
1510 primary key
1511)
1512 collate = utf8mb4_unicode_ci;
1513
1514create table invcore_clubs_memberships
1515(
1516 club_id bigint unsigned not null comment 'Club ID Number',
1517 member_id bigint unsigned not null comment 'Member ID number',
1518 joined int unsigned not null comment 'Unix timestamp of when the member joined the club',
1519 status enum ('member', 'requested', 'invited', 'leader', 'declined', 'banned', 'moderator', 'invited_bypassing_payment', 'payment_pending', 'expired_moderator', 'expired') default 'member' null comment 'The status of the membership. member is normal member, requested/invited are in the process of joining, leader is leader, declined means their request to join was declined, banned is banned by a leader',
1520 added_by bigint unsigned null comment 'If added by a leader, the leader''s member ID. NULL if joined themselves.',
1521 invited_by bigint unsigned null comment 'If invited by another member, the member id. NULL if joined themselves.',
1522 primary key (club_id, member_id)
1523)
1524 collate = utf8mb4_unicode_ci;
1525
1526create index member_id
1527 on invcore_clubs_memberships (member_id, status);
1528
1529create table invcore_clubs_node_map
1530(
1531 id bigint unsigned auto_increment comment 'Primary ID'
1532 primary key,
1533 club_id bigint unsigned default 0 not null comment 'The club ID',
1534 node_class varchar(255) default '' not null comment 'The class for the type of node',
1535 node_id bigint unsigned default 0 not null comment 'The ID number for the node',
1536 name varchar(255) default '' not null comment 'The name'
1537)
1538 collate = utf8mb4_unicode_ci;
1539
1540create index club_id
1541 on invcore_clubs_node_map (club_id);
1542
1543create index node
1544 on invcore_clubs_node_map (node_class, node_id);
1545
1546create table invcore_content_meta
1547(
1548 meta_id bigint unsigned auto_increment comment 'ID Number'
1549 primary key,
1550 meta_class varchar(255) null comment 'The Item Content Class',
1551 meta_item_id bigint null comment 'The Item Primary ID',
1552 meta_type varchar(100) null comment 'The Type of Meta Data',
1553 meta_data mediumblob null comment 'The data'
1554)
1555 collate = utf8mb4_unicode_ci;
1556
1557create index meta_content
1558 on invcore_content_meta (meta_class, meta_item_id);
1559
1560create table invcore_deletion_log
1561(
1562 dellog_id bigint unsigned auto_increment comment 'ID Number'
1563 primary key,
1564 dellog_content_class varchar(255) default '' not null,
1565 dellog_content_id bigint default 0 not null,
1566 dellog_content_title varchar(255) default '' not null,
1567 dellog_content_seo_title varchar(255) default '' not null,
1568 dellog_deleted_by bigint null,
1569 dellog_deleted_by_name varchar(255) null,
1570 dellog_deleted_date bigint default 0 not null,
1571 dellog_deleted_by_seo_name varchar(255) null,
1572 dellog_content_permissions text not null,
1573 dellog_content_container_id bigint default 0 not null,
1574 dellog_content_container_class varchar(255) null
1575)
1576 collate = utf8mb4_unicode_ci;
1577
1578create index dellog_content
1579 on invcore_deletion_log (dellog_content_class, dellog_content_id);
1580
1581create index dellog_deleted_by
1582 on invcore_deletion_log (dellog_deleted_by);
1583
1584create index dellog_deleted_date
1585 on invcore_deletion_log (dellog_deleted_date);
1586
1587create table invcore_dev
1588(
1589 app_key varchar(250) default '' not null comment 'Application key'
1590 primary key,
1591 working_version varchar(32) default '10000' not null comment 'The version ID being worked on, which can be the string ''working''',
1592 last_sync int(11) unsigned default 0 not null comment 'Timestamp of last sync',
1593 ran mediumtext null comment 'JSON array of queries ran'
1594)
1595 comment 'IN_DEV only. Store when the database schema was last synced.' collate = utf8mb4_unicode_ci;
1596
1597create table invcore_edit_history
1598(
1599 id bigint unsigned auto_increment comment 'ID Number'
1600 primary key,
1601 class varchar(255) not null comment 'The comment class',
1602 comment_id bigint unsigned not null comment 'The comment ID',
1603 member bigint unsigned not null comment 'The ID number of the member making the edit',
1604 time int unsigned not null comment 'Unix timestamp of the date the edit was made',
1605 old mediumtext not null comment 'The previous comment text',
1606 new mediumtext not null comment 'The new comment text',
1607 public bit default b'1' null comment 'Indicates if the log should display publicly',
1608 reason varchar(255) null comment 'User-provided reason for editing'
1609)
1610 collate = utf8mb4_unicode_ci;
1611
1612create index edit_log
1613 on invcore_edit_history (class, comment_id, time);
1614
1615create index member
1616 on invcore_edit_history (member);
1617
1618create index time
1619 on invcore_edit_history (time);
1620
1621create table invcore_email_templates
1622(
1623 template_id bigint unsigned auto_increment comment 'ID Number'
1624 primary key,
1625 template_app varchar(255) default '' not null,
1626 template_name varchar(255) default '' not null,
1627 template_content_html mediumtext null,
1628 template_parent bigint default 0 not null comment 'This column holds the template_id of the original un-edited version of the template',
1629 template_data text null,
1630 template_key char(32) default '' not null,
1631 template_content_plaintext mediumtext null,
1632 template_edited bit default b'0' null comment 'Flag to indicate if there is a customized version of the template',
1633 template_pinned tinyint(1) default 0 null,
1634 constraint template_key
1635 unique (template_key, template_parent)
1636)
1637 collate = utf8mb4_unicode_ci;
1638
1639create index template_app
1640 on invcore_email_templates (template_app);
1641
1642create index template_parent
1643 on invcore_email_templates (template_parent);
1644
1645create table invcore_emoticons
1646(
1647 id smallint(3) auto_increment
1648 primary key,
1649 typed varchar(128) default '' not null,
1650 image text not null,
1651 clickable smallint(2) default 1 not null,
1652 emo_set varchar(64) default 'default' not null,
1653 emo_position int(5) default 0 not null,
1654 emo_set_position int unsigned default 1 not null,
1655 image_2x text null,
1656 width smallint(3) default 0 not null,
1657 height smallint(3) default 0 not null
1658)
1659 collate = utf8mb4_unicode_ci;
1660
1661create index emo_set
1662 on invcore_emoticons (emo_set);
1663
1664create table invcore_error_logs
1665(
1666 log_id int auto_increment
1667 primary key,
1668 log_member bigint unsigned default 0 not null,
1669 log_date int default 0 not null,
1670 log_error text null,
1671 log_error_code varchar(24) default '0' not null,
1672 log_ip_address varchar(46) null,
1673 log_request_uri text null
1674)
1675 collate = utf8mb4_unicode_ci;
1676
1677create index log_date
1678 on invcore_error_logs (log_date);
1679
1680create index log_ip_address
1681 on invcore_error_logs (log_ip_address);
1682
1683create index member
1684 on invcore_error_logs (log_member);
1685
1686create table invcore_file_logs
1687(
1688 log_id bigint auto_increment
1689 primary key,
1690 log_type enum ('log', 'copy', 'error', 'move', 'orphaned') null,
1691 log_action enum ('move', 'copy', 'delete', 'save', 'orphaned') null,
1692 log_configuration_id int(10) default 0 not null,
1693 log_method varchar(255) default '' not null,
1694 log_filename text null,
1695 log_msg text null,
1696 log_date int(10) default 0 not null,
1697 log_data text null,
1698 log_container varchar(255) null,
1699 log_url text null
1700)
1701 collate = utf8mb4_unicode_ci;
1702
1703create index log_action
1704 on invcore_file_logs (log_action);
1705
1706create index log_date
1707 on invcore_file_logs (log_date);
1708
1709create index log_method
1710 on invcore_file_logs (log_method);
1711
1712create index log_type
1713 on invcore_file_logs (log_type);
1714
1715create table invcore_file_storage
1716(
1717 id bigint unsigned auto_increment comment 'ID Number'
1718 primary key,
1719 method varchar(128) null comment 'The storage method',
1720 configuration text null comment 'JSON object of configuration options'
1721)
1722 collate = utf8mb4_unicode_ci;
1723
1724create table invcore_files
1725(
1726 id bigint unsigned auto_increment comment 'ID Number'
1727 primary key,
1728 filename varchar(255) default '' null comment 'Filename',
1729 salt char(32) null,
1730 contents longblob null comment 'Raw file contents',
1731 container varchar(255) null
1732)
1733 collate = utf8mb4_unicode_ci;
1734
1735create index file
1736 on invcore_files (container, filename);
1737
1738create table invcore_files_temp
1739(
1740 id bigint unsigned auto_increment comment 'ID Number'
1741 primary key,
1742 upload_key char(32) null comment 'Key for upload process',
1743 filename varchar(255) default '' null comment 'Filename',
1744 mime varchar(255) null,
1745 contents text null comment 'Path to file on disk',
1746 time int unsigned default 0 not null comment 'Unix timestamp of upload date',
1747 storage_extension varchar(100) null
1748)
1749 collate = utf8mb4_unicode_ci;
1750
1751create index time
1752 on invcore_files_temp (time);
1753
1754create index upload_key
1755 on invcore_files_temp (upload_key);
1756
1757create table invcore_follow
1758(
1759 follow_id varchar(32) default '' not null
1760 primary key,
1761 follow_app varchar(150) default '' not null,
1762 follow_area varchar(200) default '' not null,
1763 follow_rel_id bigint unsigned default 0 not null,
1764 follow_member_id bigint unsigned default 0 not null,
1765 follow_is_anon int(1) default 0 not null,
1766 follow_added int unsigned default 0 not null,
1767 follow_notify_do int(1) default 0 not null,
1768 follow_notify_meta text null,
1769 follow_notify_freq varchar(20) default '' not null,
1770 follow_notify_sent int unsigned default 0 not null,
1771 follow_visible tinyint default 1 not null
1772)
1773 collate = utf8mb4_unicode_ci;
1774
1775create index digest_task
1776 on invcore_follow (follow_notify_do, follow_notify_freq, follow_notify_sent, follow_member_id);
1777
1778create index find_rel_follows
1779 on invcore_follow (follow_visible, follow_is_anon, follow_added);
1780
1781create index follow_added_extra
1782 on invcore_follow (follow_added, follow_is_anon, follow_member_id);
1783
1784create index follow_lookup_area
1785 on invcore_follow (follow_visible);
1786
1787create index follow_member_id
1788 on invcore_follow (follow_member_id, follow_visible, follow_added);
1789
1790create index follow_stream
1791 on invcore_follow (follow_app, follow_area, follow_member_id);
1792
1793create index followers
1794 on invcore_follow (follow_app, follow_area, follow_rel_id, follow_notify_freq);
1795
1796create table invcore_geoip_cache
1797(
1798 ip_address varchar(46) not null comment 'The IP address'
1799 primary key,
1800 data text null comment 'The JSON-encoded data returned by the service',
1801 date int(10) not null comment 'Unix timestamp of when the data was retrieved '
1802)
1803 comment 'Caches GeoIP data to prevent multiple remote calls' collate = utf8mb4_unicode_ci;
1804
1805create index date
1806 on invcore_geoip_cache (date);
1807
1808create table invcore_googleauth_used_codes
1809(
1810 member bigint unsigned not null comment 'The member ID the code was generated for',
1811 time int unsigned not null comment 'Unix timestamp the code was generated'
1812)
1813 collate = utf8mb4_unicode_ci;
1814
1815create index member
1816 on invcore_googleauth_used_codes (member);
1817
1818create index time
1819 on invcore_googleauth_used_codes (time);
1820
1821create table invcore_group_promotions
1822(
1823 promote_id bigint unsigned auto_increment comment 'ID Number'
1824 primary key,
1825 promote_position bigint unsigned default 0 not null comment 'Position of promotion rule',
1826 promote_enabled tinyint(1) default 0 not null comment 'Whether the rule is enabled or not',
1827 promote_filters text null comment 'Json-encoded array of filters that a member must meet in order for this rule to apply',
1828 promote_actions text null comment 'Json-encoded array of actions taken when this rule applies'
1829)
1830 collate = utf8mb4_unicode_ci;
1831
1832create index promotions
1833 on invcore_group_promotions (promote_enabled, promote_position);
1834
1835create table invcore_groups
1836(
1837 g_id int(3) unsigned auto_increment
1838 primary key,
1839 g_view_board tinyint(1) default 1 null,
1840 g_mem_info tinyint(1) default 1 null,
1841 g_use_search tinyint(1) default 1 null,
1842 g_edit_profile tinyint(1) default 1 null,
1843 g_edit_posts text null,
1844 g_delete_own_posts text null,
1845 g_use_pm tinyint(1) default 1 null,
1846 g_append_edit tinyint(1) null,
1847 g_access_offline tinyint(1) null,
1848 g_avoid_q tinyint(1) null,
1849 g_avoid_flood tinyint(1) null,
1850 g_icon text null,
1851 g_attach_max bigint null,
1852 prefix varchar(250) null,
1853 suffix varchar(250) null,
1854 g_max_messages int(5) default -1 null,
1855 g_max_mass_pm int(5) default -1 null,
1856 g_search_flood mediumint(6) default 2 null,
1857 g_edit_cutoff int(10) default 0 null,
1858 g_photo_max_vars varchar(200) default '100:200:200' null,
1859 g_dohtml tinyint(1) default 0 not null,
1860 g_bypass_badwords tinyint(1) default 0 not null,
1861 g_can_msg_attach tinyint(1) default 0 not null,
1862 g_attach_per_post int(10) default 0 not null,
1863 g_dname_changes int(3) default 0 not null,
1864 g_dname_date int(5) default 0 not null,
1865 g_mod_preview tinyint(1) unsigned default 0 not null,
1866 g_rep_max_positive mediumint(8) default 0 not null,
1867 g_rep_max_negative mediumint unsigned default 0 not null,
1868 g_signature_limits varchar(255) null,
1869 g_hide_online_list tinyint(1) default 0 not null,
1870 g_bitoptions int unsigned default 0 not null,
1871 g_pm_perday smallint default -1 not null,
1872 g_mod_post_unit int(5) unsigned default 0 not null,
1873 g_ppd_limit int(5) unsigned default 0 not null,
1874 g_ppd_unit int(5) unsigned default 0 not null,
1875 g_displayname_unit int(5) unsigned default 0 not null,
1876 g_sig_unit int(5) unsigned default 0 not null,
1877 g_pm_flood_mins int(5) default -1 not null,
1878 g_max_bgimg_upload int(10) default 1024 not null,
1879 g_post_polls tinyint(1) unsigned default 1 null comment 'Can create polls?',
1880 g_vote_polls tinyint(1) unsigned default 1 null comment 'Can vote in polls?',
1881 g_topic_rate_setting smallint(2) default 0 not null,
1882 g_bitoptions2 int unsigned default 0 not null,
1883 g_upload_animated_photos tinyint(1) unsigned default 1 not null,
1884 g_view_displaynamehistory tinyint(1) default 1 null,
1885 g_hide_own_posts text null,
1886 g_lock_unlock_own text null,
1887 g_can_report text null,
1888 g_create_clubs set ('public', 'open', 'closed', 'private', 'readonly') default 'public,open,closed,private' not null comment 'The types of club this group can create',
1889 g_club_allowed_nodes text not null comment 'Node types that can be created in clubs, or * for all',
1890 g_promote_exclude tinyint(1) default 0 not null comment 'Exclude members in this group from group promotions',
1891 g_close_polls tinyint(1) default 0 not null comment 'Can close own polls?',
1892 g_club_limit int unsigned null comment 'Maximum number of clubs, NULL for unlimited',
1893 idm_view_approvers tinyint(1) unsigned default 0 null comment 'Can view who approved files?',
1894 idm_bypass_revision tinyint(1) unsigned default 0 null comment 'Can bypass revisions?',
1895 idm_view_downloads tinyint(1) unsigned default 0 null comment 'Can view who downloaded files?',
1896 idm_throttling int unsigned default 0 null comment 'Downloads Speed Throttling kb',
1897 idm_wait_period int unsigned default 0 null comment 'Seconds users must wait before download',
1898 idm_restrictions text null comment 'Downloads Restrictions',
1899 idm_linked_files tinyint(1) unsigned default 0 null comment 'Can submit URLs?',
1900 idm_import_files tinyint(1) unsigned default 0 null comment 'Can import files from a path?',
1901 idm_bulk_submit tinyint(1) unsigned default 0 null comment 'Can bulk upload?',
1902 idm_add_paid tinyint(1) unsigned default 0 null comment 'Can submit paid files?',
1903 idm_bypass_paid tinyint(1) unsigned default 0 null comment 'Can download paid files without paying?',
1904 g_blog_allowlocal tinyint(1) unsigned default 0 null comment 'Can create blogs?',
1905 g_blog_maxblogs int unsigned default 0 null comment 'Maximum number of blogs',
1906 g_blog_allowprivate tinyint(1) unsigned default 0 null comment 'Can create private blogs?',
1907 g_blog_allowownmod tinyint(1) unsigned default 0 null comment 'Can moderate own blogs?',
1908 g_blog_allowdelete tinyint(1) unsigned default 0 null comment 'Can delete own blogs?',
1909 g_blog_allowcomment tinyint(1) unsigned default 0 null comment 'Can comment on blogs?',
1910 idm_paid_restrictions tinyint(1) unsigned default 0 null comment 'Download restrictions apply to Paid files?'
1911)
1912 collate = utf8mb4_unicode_ci;
1913
1914create table invcore_hooks
1915(
1916 id bigint unsigned auto_increment comment 'ID Number'
1917 primary key,
1918 plugin bigint unsigned null comment 'Plugin ID, if belongs to a plugin',
1919 app varchar(255) null comment 'Application key, if belongs to app',
1920 type char default '' not null comment 'C for code, S for skin',
1921 class varchar(255) default '' not null comment 'The class the hook overrides',
1922 filename varchar(32) default '' not null comment 'The filename on disk'
1923)
1924 collate = utf8mb4_unicode_ci;
1925
1926create index app
1927 on invcore_hooks (app);
1928
1929create index plugin
1930 on invcore_hooks (plugin);
1931
1932create table invcore_ignored_users
1933(
1934 ignore_id int(10) auto_increment
1935 primary key,
1936 ignore_owner_id int(8) default 0 not null,
1937 ignore_ignore_id int(8) default 0 not null,
1938 ignore_messages int(1) default 0 not null,
1939 ignore_topics int(1) default 0 not null,
1940 ignore_signatures int(1) default 0 not null,
1941 ignore_mentions tinyint(1) unsigned default 0 not null
1942)
1943 collate = utf8mb4_unicode_ci;
1944
1945create index ignore_ignore_id
1946 on invcore_ignored_users (ignore_ignore_id);
1947
1948create index ignore_owner_id
1949 on invcore_ignored_users (ignore_owner_id);
1950
1951create table invcore_image_proxy
1952(
1953 md5_url char(32) not null comment 'The md5sum of the URL'
1954 primary key,
1955 location varchar(255) null comment 'The stored location or NULL if it''s invalid',
1956 cache_time int unsigned not null comment 'Timestamp of when the image was cached'
1957)
1958 collate = utf8mb4_unicode_ci;
1959
1960create index cache_time
1961 on invcore_image_proxy (cache_time);
1962
1963create table invcore_incoming_emails
1964(
1965 rule_id int(10) auto_increment
1966 primary key,
1967 rule_criteria_field varchar(4) default '' not null,
1968 rule_criteria_type varchar(4) default '' not null,
1969 rule_criteria_value text not null,
1970 rule_app varchar(255) default '' not null,
1971 rule_added_by bigint unsigned default 0 not null,
1972 rule_added_date int(10) default 0 not null
1973)
1974 comment 'This table is used to store the incoming email routing rules' collate = utf8mb4_unicode_ci;
1975
1976create index member
1977 on invcore_incoming_emails (rule_added_by);
1978
1979create table invcore_ips_bulletins
1980(
1981 id bigint unsigned auto_increment comment 'ID Number'
1982 primary key,
1983 title varchar(255) not null comment 'The bulletin title',
1984 body text not null comment 'The bulletin body',
1985 severity varchar(32) not null comment 'The bulletin severity',
1986 style varchar(32) not null comment 'The bulletin style',
1987 dismissible varchar(32) not null comment 'If the bulletin can be dismissed',
1988 link varchar(255) null comment 'Where the notification should link to',
1989 cached int unsigned not null comment 'Unix timestamp of when this row was cached',
1990 conditions text not null comment 'PHP code to check if notification should show'
1991)
1992 collate = utf8mb4_unicode_ci;
1993
1994create table invcore_item_markers
1995(
1996 item_key char(32) default '' not null,
1997 item_member_id bigint unsigned default 0 not null,
1998 item_app varchar(100) default 'core' not null,
1999 item_read_array mediumtext null,
2000 item_global_reset int(10) default 0 not null,
2001 item_app_key_1 int(10) default 0 not null,
2002 item_app_key_2 int(10) default 0 not null,
2003 item_app_key_3 int(10) default 0 not null,
2004 constraint combo_key
2005 unique (item_key, item_member_id, item_app)
2006)
2007 collate = utf8mb4_unicode_ci;
2008
2009create index marker_index
2010 on invcore_item_markers (item_member_id, item_app, item_app_key_1);
2011
2012create table invcore_javascript
2013(
2014 javascript_id int(10) auto_increment
2015 primary key,
2016 javascript_app varchar(32) default '' not null,
2017 javascript_location varchar(32) default '' not null,
2018 javascript_plugin varchar(100) default '' null,
2019 javascript_path varchar(100) default '' not null,
2020 javascript_name varchar(150) default '' not null,
2021 javascript_type varchar(100) default '' not null,
2022 javascript_content mediumtext null,
2023 javascript_version varchar(10) default '0' not null,
2024 javascript_position int unsigned default 0 null,
2025 javascript_key char(32) default '' not null comment 'Look up key ',
2026 constraint javascript_lookup
2027 unique (javascript_app, javascript_location, javascript_path, javascript_name)
2028)
2029 collate = utf8mb4_unicode_ci;
2030
2031create index look_up_key
2032 on invcore_javascript (javascript_key);
2033
2034create table invcore_leaders
2035(
2036 leader_id bigint unsigned auto_increment comment 'Primary ID'
2037 primary key,
2038 leader_type enum ('m', 'g') default 'm' not null comment 'Record is for member (m) or group (g)',
2039 leader_type_id bigint default 0 not null comment 'Member or group ID',
2040 leader_group_id mediumint(8) default 0 not null comment 'The group to display in (core_leaders_group.group_id)',
2041 leader_position bigint null comment 'Position',
2042 constraint leader_type
2043 unique (leader_type, leader_type_id)
2044)
2045 collate = utf8mb4_unicode_ci;
2046
2047create index leader_group_id
2048 on invcore_leaders (leader_group_id);
2049
2050create table invcore_leaders_groups
2051(
2052 group_id mediumint unsigned auto_increment comment 'ID Number'
2053 primary key,
2054 group_name varchar(255) default '' not null,
2055 group_template varchar(255) default '' not null,
2056 group_position mediumint unsigned default 0 not null
2057)
2058 collate = utf8mb4_unicode_ci;
2059
2060create table invcore_log
2061(
2062 id bigint unsigned auto_increment comment 'ID Number'
2063 primary key,
2064 exception_class varchar(255) null comment 'If the log was an exception, the class name',
2065 exception_code int(3) unsigned null comment 'If the log was an exception, the code',
2066 message mediumtext not null comment 'The log message',
2067 backtrace text null comment 'The backtrace',
2068 time int(10) default 0 not null comment 'Unix timestamp of log',
2069 category varchar(128) null comment 'Optional string identifying the type of log',
2070 url text null comment 'URL the error occurred on, if any',
2071 member_id bigint unsigned default 0 not null comment 'Member that triggered the error, if any'
2072)
2073 collate = utf8mb4_unicode_ci;
2074
2075create index category
2076 on invcore_log (category);
2077
2078create index time
2079 on invcore_log (time);
2080
2081create table invcore_login_links
2082(
2083 token_login_method bigint(10) unsigned default 0 not null comment 'The ID number of the login method',
2084 token_member bigint unsigned default 0 not null comment 'The member ID',
2085 token_identifier varchar(255) default '' not null comment 'The server''s identifier for the user',
2086 token_linked tinyint(1) unsigned default 0 not null comment 'Boolean value indicating if the account has been successfully linked',
2087 token_access_token text null comment 'Access token',
2088 token_expires int unsigned null comment 'Unix timestamp of when the access token expires',
2089 token_refresh_token text null comment 'Refresh token, if provided',
2090 token_secret text null comment 'Token secret for OAuth 1.0 implementations',
2091 token_scope text null comment 'JSON-encoded array of scopes issued',
2092 primary key (token_login_method, token_member),
2093 constraint token_identifier
2094 unique (token_login_method, token_identifier)
2095)
2096 collate = utf8mb4_unicode_ci;
2097
2098create index token_member
2099 on invcore_login_links (token_member);
2100
2101create table invcore_login_methods
2102(
2103 login_id bigint unsigned auto_increment comment 'ID Number'
2104 primary key,
2105 login_classname varchar(255) not null comment 'Handler''s class name',
2106 login_order int unsigned not null comment 'The order to process in ',
2107 login_acp tinyint(1) unsigned default 1 not null comment 'Boolean value indicating if method can be used for ACP logins',
2108 login_settings text not null comment 'JSON-encoded handler-specific setting values',
2109 login_enabled tinyint(1) unsigned default 1 not null comment 'Boolean value indicating if method is enabled',
2110 login_register tinyint(1) unsigned default 1 not null comment 'Boolean value indicating if users can register using this method'
2111)
2112 collate = utf8mb4_unicode_ci;
2113
2114create index login_enabled
2115 on invcore_login_methods (login_enabled);
2116
2117create table invcore_mail_error_logs
2118(
2119 mlog_id int(10) auto_increment
2120 primary key,
2121 mlog_date int(10) default 0 not null,
2122 mlog_to text not null,
2123 mlog_from varchar(250) default '' not null,
2124 mlog_subject text null,
2125 mlog_content mediumtext null,
2126 mlog_msg mediumtext null,
2127 mlog_smtp_log mediumtext null,
2128 mlog_resend_data mediumtext null
2129)
2130 collate = utf8mb4_unicode_ci;
2131
2132create index mlog_date
2133 on invcore_mail_error_logs (mlog_date);
2134
2135create table invcore_member_history
2136(
2137 log_id bigint unsigned auto_increment comment 'ID Number'
2138 primary key,
2139 log_app varchar(250) default '' not null comment 'Application',
2140 log_member bigint unsigned default 0 not null comment 'Member ID',
2141 log_by bigint unsigned null comment 'Action performed by',
2142 log_type varchar(32) null comment 'Log Type',
2143 log_data text null,
2144 log_date decimal(12, 2) default 0.00 not null,
2145 log_ip_address varchar(46) null
2146)
2147 collate = utf8mb4_unicode_ci;
2148
2149create index application
2150 on invcore_member_history (log_app);
2151
2152create index log_by
2153 on invcore_member_history (log_by, log_ip_address, log_date);
2154
2155create index log_data
2156 on invcore_member_history (log_data);
2157
2158create index log_date
2159 on invcore_member_history (log_date);
2160
2161create index log_ip_address
2162 on invcore_member_history (log_ip_address);
2163
2164create index log_member
2165 on invcore_member_history (log_member);
2166
2167create table invcore_member_ranks
2168(
2169 id smallint(5) auto_increment
2170 primary key,
2171 posts int(10) null,
2172 title varchar(128) null,
2173 pips varchar(128) null,
2174 icon text not null,
2175 use_icon tinyint(1) default 0 not null
2176)
2177 collate = utf8mb4_unicode_ci;
2178
2179create index posts
2180 on invcore_member_ranks (posts);
2181
2182create table invcore_member_status_replies
2183(
2184 reply_id int(10) auto_increment
2185 primary key,
2186 reply_status_id int unsigned default 0 not null,
2187 reply_member_id bigint unsigned default 0 not null,
2188 reply_date int unsigned default 0 not null,
2189 reply_content text null,
2190 reply_approved tinyint(1) default 0 not null,
2191 reply_ip_address varchar(46) null
2192)
2193 collate = utf8mb4_unicode_ci;
2194
2195create index ip_lookup
2196 on invcore_member_status_replies (reply_ip_address);
2197
2198create index reply_date
2199 on invcore_member_status_replies (reply_date);
2200
2201create index reply_member_id
2202 on invcore_member_status_replies (reply_member_id);
2203
2204create index reply_status_count
2205 on invcore_member_status_replies (reply_status_id, reply_member_id);
2206
2207create index reply_status_id
2208 on invcore_member_status_replies (reply_status_id, reply_date);
2209
2210create table invcore_member_status_updates
2211(
2212 status_id int(10) auto_increment
2213 primary key,
2214 status_member_id bigint unsigned default 0 not null,
2215 status_date int unsigned default 0 not null,
2216 status_content text null,
2217 status_replies int unsigned default 0 not null,
2218 status_last_ids text null,
2219 status_is_latest int(1) default 0 not null,
2220 status_is_locked int(1) default 0 not null,
2221 status_hash varchar(32) default '' not null,
2222 status_imported int(1) default 0 not null,
2223 status_author_id int(10) default 0 not null,
2224 status_author_ip varchar(46) default '' not null,
2225 status_approved int(1) default 1 not null
2226)
2227 collate = utf8mb4_unicode_ci;
2228
2229create index ip_lookup
2230 on invcore_member_status_updates (status_author_ip);
2231
2232create index s_hash
2233 on invcore_member_status_updates (status_member_id, status_hash, status_date);
2234
2235create index status_author_lookup
2236 on invcore_member_status_updates (status_author_id, status_member_id, status_date);
2237
2238create index status_date
2239 on invcore_member_status_updates (status_date);
2240
2241create index status_is_latest
2242 on invcore_member_status_updates (status_is_latest, status_date);
2243
2244create index status_member_id
2245 on invcore_member_status_updates (status_member_id, status_approved, status_date);
2246
2247create table invcore_members
2248(
2249 member_id bigint unsigned auto_increment
2250 primary key,
2251 name varchar(255) default '' not null,
2252 member_group_id smallint(3) default 0 not null,
2253 email varchar(150) default '' not null,
2254 joined int(10) default 0 not null,
2255 ip_address varchar(46) default '' not null,
2256 skin smallint(5) null,
2257 warn_level int(10) null,
2258 warn_lastwarn int(10) default 0 not null,
2259 language mediumint(4) null,
2260 restrict_post int(10) default 0 not null,
2261 bday_day int(2) null,
2262 bday_month int(2) null,
2263 bday_year int(4) null,
2264 msg_count_new int(2) default 0 not null,
2265 msg_count_total int(3) default 0 not null,
2266 msg_count_reset int(1) default 0 not null,
2267 msg_show_notification int(1) default 0 not null,
2268 last_visit int(10) default 0 null,
2269 last_activity int(10) default 0 null,
2270 mod_posts int(10) default 0 not null,
2271 auto_track varchar(256) default '0' null,
2272 temp_ban int(10) default 0 null,
2273 mgroup_others varchar(245) default '' not null,
2274 members_seo_name varchar(255) default '' not null,
2275 members_cache mediumtext null,
2276 failed_logins text null,
2277 failed_login_count smallint(3) default 0 not null,
2278 members_profile_views int unsigned default 0 not null,
2279 members_pass_hash varchar(255) null,
2280 members_pass_salt varchar(22) null,
2281 members_bitoptions int unsigned default 0 not null,
2282 members_day_posts varchar(32) default '0,0' not null,
2283 notification_cnt mediumint default 0 not null,
2284 pp_last_visitors text null,
2285 pp_main_photo text null,
2286 pp_main_width int(5) null,
2287 pp_main_height int(5) null,
2288 pp_thumb_photo text null,
2289 pp_thumb_width int(5) null,
2290 pp_thumb_height int(5) null,
2291 pp_setting_count_comments int(2) null,
2292 pp_reputation_points int(10) null,
2293 pp_photo_type varchar(20) null,
2294 signature text null,
2295 pconversation_filters text null,
2296 pp_customization mediumtext null,
2297 timezone varchar(64) null,
2298 pp_cover_photo varchar(255) default '' not null,
2299 profilesync text null,
2300 profilesync_lastsync int(10) default 0 not null comment 'Indicates the last time any profile sync service was ran',
2301 allow_admin_mails bit default b'0' null,
2302 members_bitoptions2 int unsigned default 0 not null,
2303 create_menu text null comment 'Cached contents of the "Create" drop down menu.',
2304 members_disable_pm tinyint(1) unsigned default 0 not null comment '0 - not disabled, 1 - disabled, member can re-enable, 2 - disabled',
2305 marked_site_read int unsigned default 0 null,
2306 pp_cover_offset int(10) default 0 not null,
2307 acp_skin smallint null,
2308 acp_language mediumint null,
2309 member_title varchar(64) null,
2310 member_posts mediumint(7) default 0 not null,
2311 member_last_post int(10) null,
2312 member_streams text null,
2313 photo_last_update int(10) null,
2314 mfa_details text null,
2315 failed_mfa_attempts smallint(3) unsigned default 0 null comment 'Number of times tried and failed MFA',
2316 permission_array text null comment 'A cache of the clubs and social groups that the member is in',
2317 idm_block_submissions tinyint(1) unsigned default 0 null comment 'Blocked from submitting Downloads files?',
2318 steamid varchar(17) null,
2319 member_welcome tinyint(1) default 0 null,
2320 completed bit default b'0' not null comment 'Whether the account is completed or not'
2321)
2322 collate = utf8mb4_unicode_ci;
2323
2324create index allow_admin_mails
2325 on invcore_members (allow_admin_mails);
2326
2327create index bday_day
2328 on invcore_members (bday_day);
2329
2330create index bday_month
2331 on invcore_members (bday_month);
2332
2333create index completed
2334 on invcore_members (completed, temp_ban);
2335
2336create index email
2337 on invcore_members (email);
2338
2339create index failed_login_count
2340 on invcore_members (failed_login_count);
2341
2342create index ip_address
2343 on invcore_members (ip_address);
2344
2345create index joined
2346 on invcore_members (joined);
2347
2348create index last_activity
2349 on invcore_members (last_activity);
2350
2351create index member_groups
2352 on invcore_members (member_group_id, mgroup_others);
2353
2354create index members_bitoptions
2355 on invcore_members (members_bitoptions);
2356
2357create index mgroup
2358 on invcore_members (member_id, member_group_id);
2359
2360create index mod_posts
2361 on invcore_members (mod_posts);
2362
2363create index name_index
2364 on invcore_members (name);
2365
2366create index photo_last_update
2367 on invcore_members (photo_last_update);
2368
2369create index profilesync
2370 on invcore_members (profilesync_lastsync, profilesync);
2371
2372create index steamid
2373 on invcore_members (steamid);
2374
2375create table invcore_members_feature_seen
2376(
2377 member_id bigint unsigned default 0 not null comment 'Member ID'
2378 primary key,
2379 feature_id bigint unsigned default 0 not null
2380)
2381 collate = utf8mb4_unicode_ci;
2382
2383create table invcore_members_known_devices
2384(
2385 device_key varchar(32) default '' not null comment 'A random string whose value will be set as a permanent cookie to identify a device',
2386 member_id bigint unsigned default 0 not null comment 'The member ID authenticated on the device',
2387 user_agent text null comment 'The user agent string. Note this is not for identification but just for reference, and will be updated when the user upgrades their browser version',
2388 login_key varchar(32) null comment 'If the user has chosen "remember me", a random string whose value is set as a cookie to facilitate automatic login. NULL if they did not choose "remember me".',
2389 last_seen int unsigned default 0 not null comment 'A unix timestamp of when the device was last used. Updated at session start, not on every click',
2390 anonymous tinyint(1) unsigned default 0 not null comment 'Binary value indicating if, when the user is automatically logged in, their session should be anonymous',
2391 login_handler varchar(32) null comment 'The login handler which processed the most recent login (not updated on automatic log ins).',
2392 primary key (device_key, member_id)
2393)
2394 collate = utf8mb4_unicode_ci;
2395
2396create index member_id
2397 on invcore_members_known_devices (member_id, last_seen);
2398
2399create table invcore_members_known_ip_addresses
2400(
2401 device_key varchar(32) not null comment 'The device key as stored in core_members_known_devices.device_key',
2402 member_id bigint unsigned not null comment 'The member ID',
2403 ip_address varchar(46) not null comment 'The IP address',
2404 last_seen int unsigned not null comment 'A unix timestamp of when the device was last used on this IP address. Updated at session start, not on every click',
2405 primary key (device_key, member_id, ip_address)
2406)
2407 collate = utf8mb4_unicode_ci;
2408
2409create index ip_address
2410 on invcore_members_known_ip_addresses (ip_address);
2411
2412create index member_id
2413 on invcore_members_known_ip_addresses (member_id, last_seen);
2414
2415create table invcore_members_warn_actions
2416(
2417 wa_id int(11) unsigned auto_increment
2418 primary key,
2419 wa_points int(32) null,
2420 wa_mq int(2) null,
2421 wa_mq_unit char null,
2422 wa_rpa int(2) null,
2423 wa_rpa_unit char null,
2424 wa_suspend int(2) null,
2425 wa_suspend_unit char null,
2426 wa_override tinyint(1) null
2427)
2428 collate = utf8mb4_unicode_ci;
2429
2430create index wa_points
2431 on invcore_members_warn_actions (wa_points);
2432
2433create table invcore_members_warn_logs
2434(
2435 wl_id int(11) unsigned auto_increment
2436 primary key,
2437 wl_member bigint unsigned null,
2438 wl_moderator bigint unsigned null,
2439 wl_date int(10) null,
2440 wl_reason int(10) null,
2441 wl_points int(5) null,
2442 wl_note_member text null,
2443 wl_note_mods text null,
2444 wl_mq varchar(128) null,
2445 wl_rpa varchar(128) null,
2446 wl_suspend varchar(128) null,
2447 wl_acknowledged tinyint(1) null,
2448 wl_content_app varchar(32) null,
2449 wl_content_id1 varchar(32) null,
2450 wl_content_id2 varchar(32) null,
2451 wl_expire_date int(10) null,
2452 wl_content_module varchar(32) null
2453)
2454 collate = utf8mb4_unicode_ci;
2455
2456create index content
2457 on invcore_members_warn_logs (wl_content_app, wl_content_module, wl_content_id1, wl_content_id2);
2458
2459create index wl_date
2460 on invcore_members_warn_logs (wl_member, wl_date);
2461
2462create index wl_expire
2463 on invcore_members_warn_logs (wl_expire_date, wl_date);
2464
2465create index wl_moderator
2466 on invcore_members_warn_logs (wl_moderator);
2467
2468create table invcore_members_warn_reasons
2469(
2470 wr_id int(11) unsigned auto_increment
2471 primary key,
2472 wr_name varchar(255) null,
2473 wr_points float null,
2474 wr_points_override tinyint(1) null,
2475 wr_remove int(2) null,
2476 wr_remove_unit char null,
2477 wr_remove_override tinyint(1) null,
2478 wr_order int(10) null,
2479 wr_notes mediumtext null
2480)
2481 collate = utf8mb4_unicode_ci;
2482
2483create index wr_order
2484 on invcore_members_warn_reasons (wr_order);
2485
2486create table invcore_menu
2487(
2488 id bigint unsigned auto_increment comment 'ID Number'
2489 primary key,
2490 app varchar(250) default '' not null comment 'The application key for the item',
2491 extension varchar(255) null comment 'The FrontNavigation extension key',
2492 config text null comment 'Any additional configuration',
2493 position int unsigned default 1 not null comment 'This item''s position within the bar',
2494 parent int unsigned null comment 'The ID of the parent item if this is on the second bar, or NULL if the first bar',
2495 permissions text null comment 'Comma-delimited list of group IDs, or * for all, NULL to inherit',
2496 is_menu_child tinyint(1) unsigned default 0 not null comment 'If this item is a child of a dropdown menu'
2497)
2498 collate = utf8mb4_unicode_ci;
2499
2500create table invcore_message_posts
2501(
2502 msg_id int(10) auto_increment
2503 primary key,
2504 msg_topic_id int(10) default 0 not null,
2505 msg_date int(10) null,
2506 msg_post mediumtext null,
2507 msg_post_key varchar(32) default '0' not null,
2508 msg_author_id bigint unsigned default 0 not null,
2509 msg_ip_address varchar(46) default '0' not null,
2510 msg_is_first_post int(1) default 0 not null
2511)
2512 collate = utf8mb4_unicode_ci;
2513
2514create index msg_author_id
2515 on invcore_message_posts (msg_author_id);
2516
2517create index msg_date
2518 on invcore_message_posts (msg_date);
2519
2520create index msg_ip_address
2521 on invcore_message_posts (msg_ip_address);
2522
2523create fulltext index msg_post
2524 on invcore_message_posts (msg_post);
2525
2526create index msg_topic_id
2527 on invcore_message_posts (msg_topic_id);
2528
2529create index topic_date
2530 on invcore_message_posts (msg_topic_id, msg_date);
2531
2532create table invcore_message_topic_user_map
2533(
2534 map_id int(10) auto_increment
2535 primary key,
2536 map_user_id int(10) default 0 not null,
2537 map_topic_id int(10) default 0 not null,
2538 map_folder_id varchar(32) default '' not null,
2539 map_read_time int(10) default 0 not null,
2540 map_user_active int(1) default 0 not null,
2541 map_user_banned int(1) default 0 not null,
2542 map_has_unread int(1) default 0 not null,
2543 map_is_system int(1) default 0 not null,
2544 map_is_starter int(1) default 0 not null,
2545 map_left_time int(10) default 0 not null,
2546 map_ignore_notification int(1) default 0 not null,
2547 map_last_topic_reply int(10) default 0 not null,
2548 constraint map_main
2549 unique (map_user_id, map_topic_id)
2550)
2551 collate = utf8mb4_unicode_ci;
2552
2553create index map_topic_id
2554 on invcore_message_topic_user_map (map_topic_id);
2555
2556create index map_user
2557 on invcore_message_topic_user_map (map_user_id, map_user_active, map_last_topic_reply);
2558
2559create table invcore_message_topics
2560(
2561 mt_id int(10) auto_increment
2562 primary key,
2563 mt_date int(10) default 0 not null,
2564 mt_title varchar(255) default '' not null,
2565 mt_hasattach smallint(5) default 0 not null,
2566 mt_starter_id int(10) default 0 not null,
2567 mt_start_time int(10) default 0 not null,
2568 mt_last_post_time int(10) default 0 not null,
2569 mt_to_count int(3) default 0 not null,
2570 mt_to_member_id bigint unsigned default 0 not null,
2571 mt_replies int(10) default 0 not null,
2572 mt_first_msg_id int(10) default 0 not null,
2573 mt_is_draft int(1) default 0 not null,
2574 mt_is_deleted int(1) default 0 not null,
2575 mt_is_system int(1) default 0 not null
2576)
2577 collate = utf8mb4_unicode_ci;
2578
2579create index mt_date
2580 on invcore_message_topics (mt_date);
2581
2582create index mt_starter_id
2583 on invcore_message_topics (mt_starter_id);
2584
2585create fulltext index mt_title
2586 on invcore_message_topics (mt_title);
2587
2588create table invcore_moderator_logs
2589(
2590 id bigint auto_increment
2591 primary key,
2592 member_id bigint default 0 null,
2593 ctime int(10) default 0 null,
2594 note text null,
2595 ip_address varchar(46) null,
2596 appcomponent varchar(255) default '' not null,
2597 module varchar(255) default '' not null,
2598 controller varchar(255) default '' not null,
2599 do varchar(255) default '' null,
2600 lang_key text not null,
2601 class varchar(255) null,
2602 item_id int unsigned null,
2603 member_name varchar(255) default '' null
2604)
2605 collate = utf8mb4_unicode_ci;
2606
2607create index class
2608 on invcore_moderator_logs (class);
2609
2610create index ctime
2611 on invcore_moderator_logs (ctime);
2612
2613create index ip_address
2614 on invcore_moderator_logs (ip_address);
2615
2616create index item_id
2617 on invcore_moderator_logs (item_id);
2618
2619create index member_id
2620 on invcore_moderator_logs (member_id);
2621
2622create table invcore_moderators
2623(
2624 type enum ('m', 'g') default 'm' not null comment 'Member or group',
2625 id bigint default 0 not null comment 'ID Number',
2626 perms text null comment 'Permissions',
2627 updated int(10) default 0 null comment 'Updated',
2628 primary key (type, id)
2629)
2630 collate = utf8mb4_unicode_ci;
2631
2632create table invcore_modules
2633(
2634 sys_module_id mediumint(4) unsigned auto_increment
2635 primary key,
2636 sys_module_title varchar(32) default '' not null,
2637 sys_module_application varchar(32) default '' not null,
2638 sys_module_key varchar(32) default '' not null,
2639 sys_module_protected tinyint(1) unsigned default 0 null,
2640 sys_module_visible tinyint(1) unsigned default 1 not null,
2641 sys_module_position int(5) default 0 not null,
2642 sys_module_area varchar(32) default 'front' null,
2643 sys_module_default_controller varchar(32) null,
2644 sys_module_default tinyint(1) default 0 not null,
2645 constraint identifier
2646 unique (sys_module_application, sys_module_key, sys_module_area)
2647)
2648 collate = utf8mb4_unicode_ci;
2649
2650create index sys_module_application
2651 on invcore_modules (sys_module_application);
2652
2653create index sys_module_key
2654 on invcore_modules (sys_module_key);
2655
2656create index sys_module_visible
2657 on invcore_modules (sys_module_visible);
2658
2659create table invcore_notification_defaults
2660(
2661 notification_key varchar(100) default '' not null comment 'Key'
2662 primary key,
2663 `default` set ('email', 'inline') default '' not null comment 'Default options',
2664 disabled set ('email', 'inline') default '' not null comment 'Disabled options',
2665 editable bit default b'1' not null comment 'Member can edit'
2666)
2667 collate = utf8mb4_unicode_ci;
2668
2669create table invcore_notification_preferences
2670(
2671 member_id bigint unsigned default 0 not null comment 'Member ID Number',
2672 notification_key varchar(100) default '' not null comment 'Key',
2673 preference set ('email', 'inline') null comment 'The chosen notification method(s)',
2674 primary key (member_id, notification_key)
2675)
2676 collate = utf8mb4_unicode_ci;
2677
2678create table invcore_notifications
2679(
2680 id bigint unsigned auto_increment comment 'ID Number'
2681 primary key,
2682 member bigint unsigned default 0 not null comment 'The member ID',
2683 notification_app varchar(32) default '' not null comment 'The application that owns this type of notification',
2684 notification_key varchar(32) default '' not null comment 'The notification key',
2685 item_class varchar(128) null comment 'The class for the item the notification is about',
2686 item_id bigint unsigned null comment 'The ID number for the item the notification is about',
2687 member_data text null comment 'JSON-encoded merge data',
2688 sent_time int unsigned default 0 not null comment 'Unix timestamp of the time the notification was sent.',
2689 read_time int unsigned null comment 'Unix timestamp of the time the notification was read.',
2690 updated_time int unsigned default 0 not null comment 'Unix timestamp of when the notification was last updated (or sent)',
2691 item_sub_class varchar(128) null comment 'Item Sub Class for Comments and Reviews',
2692 item_sub_id bigint unsigned null comment 'Item Sub ID',
2693 extra text null comment 'JSON-encoded additional data'
2694)
2695 collate = utf8mb4_unicode_ci;
2696
2697create index item_lookup
2698 on invcore_notifications (item_class, item_id);
2699
2700create index member_read
2701 on invcore_notifications (member, read_time);
2702
2703create index member_sort
2704 on invcore_notifications (member, updated_time);
2705
2706create index sent_time
2707 on invcore_notifications (sent_time);
2708
2709create index subitem_lookup
2710 on invcore_notifications (item_sub_class, item_sub_id);
2711
2712create table invcore_oauth_authorize_prompts
2713(
2714 session_id varchar(128) default '' not null comment 'The session ID'
2715 primary key,
2716 client_id char(32) not null comment 'The client ID',
2717 response_type enum ('code', 'token') default 'code' not null comment 'The response type',
2718 redirect_uri text null comment 'The provided redirect URI',
2719 scope text null comment 'space-delimited scope',
2720 state text null,
2721 timestamp int unsigned not null comment 'Unix timestamp of when this prompt was initially shown',
2722 logged_in tinyint(1) unsigned default 0 not null comment 'Has the user logged in yet (for prompts that require it)?',
2723 prompt text null
2724)
2725 collate = utf8mb4_unicode_ci;
2726
2727create table invcore_oauth_clients
2728(
2729 oauth_client_id char(32) not null comment 'The client ID'
2730 primary key,
2731 oauth_client_secret varchar(255) null comment 'The hashed client secret or NULL for public clients',
2732 oauth_grant_types set ('authorization_code', 'implicit', 'client_credentials', 'password') not null comment 'Available grant types',
2733 oauth_redirect_uris text null comment 'JSON-encoded array of Redirect URIs',
2734 oauth_access_token_length int unsigned null comment 'Number of hours access token lives for, or NULL for no limit',
2735 oauth_use_refresh_tokens tinyint(1) unsigned not null comment 'Boolean value indicating if refresh tokens should be used',
2736 oauth_refresh_token_length int unsigned null comment 'Number of days refresh tokens live for, or NULL for no expiry or not applicable',
2737 oauth_scopes text null comment 'JSON-encoded array of scope data',
2738 oauth_enabled tinyint(1) unsigned default 1 not null comment 'Boolean value indicating if client is enabled',
2739 oauth_prompt enum ('login', 'reauthorize', 'automatic') default 'reauthorize' null comment 'How to prompt users. login = Users must always login. reauthorize = Users are shown authorize screen. automatic = Users with an existing access token skip authorise screen',
2740 oauth_choose_scopes tinyint(1) unsigned default 0 null comment 'Boolean value indicating if user can choose which of the requested scopes to grant',
2741 oauth_ucp tinyint(1) unsigned default 1 not null comment 'Boolean value indicating if the user can see and revoke their authorization in their settings page',
2742 oauth_brute_force text null comment 'JSON-encoded array of IP addresses and failed authentication, for brute-force prevention',
2743 oauth_type enum ('invision', 'wordpress') null comment 'A hint for how to display the form (NULL is generic custom)'
2744)
2745 collate = utf8mb4_unicode_ci;
2746
2747create table invcore_oauth_server_access_tokens
2748(
2749 client_id char(32) default '' not null comment 'The client ID',
2750 member_id bigint unsigned null comment 'The member ID or NULL for client_credentials',
2751 access_token char(64) default '' not null comment 'The access token',
2752 access_token_expires int unsigned null comment 'Unix timestamp of when access token expires, or NULL for never',
2753 refresh_token char(64) null comment 'Refresh token or NULL if not applicable',
2754 refresh_token_expires int unsigned null comment 'Unix timestamp of when refresh token expires or NULL if never - will be same value as access_token_expires if not applicable',
2755 scope text null comment 'JSON-encoded array of scopes or NULL if no scope was requested',
2756 authorization_code char(64) null comment 'The authorization code that generated the access token, if applicable',
2757 issued int unsigned not null comment 'Unix timestamp of when access token was generated',
2758 constraint access_lookup
2759 unique (client_id, access_token),
2760 constraint refresh_lookup
2761 unique (client_id, refresh_token)
2762)
2763 collate = utf8mb4_unicode_ci;
2764
2765create index authorization_code_lookup
2766 on invcore_oauth_server_access_tokens (client_id, authorization_code);
2767
2768create index member_id
2769 on invcore_oauth_server_access_tokens (member_id, issued);
2770
2771create index member_lookup
2772 on invcore_oauth_server_access_tokens (client_id, member_id);
2773
2774create index refresh_token_expires
2775 on invcore_oauth_server_access_tokens (refresh_token_expires);
2776
2777create table invcore_oauth_server_authorization_codes
2778(
2779 client_id char(32) default '' not null comment 'The client ID',
2780 redirect_uri text null comment 'The redirect_uri provided in the request',
2781 member_id bigint unsigned default 0 not null comment 'The member ID',
2782 expires int unsigned default 0 not null comment 'Unix timestamp of expiration',
2783 code char(64) default '' not null comment 'The authorization code',
2784 scope text null comment 'JSON-encoded array of scopes or NULL if no scope was requested',
2785 used tinyint(1) unsigned default 0 not null comment 'Boolean value indicating if code has been used',
2786 constraint authorization_code
2787 unique (client_id, code)
2788)
2789 collate = utf8mb4_unicode_ci;
2790
2791create table invcore_output_cache
2792(
2793 cache_key varchar(100) default '' not null comment 'The key'
2794 primary key,
2795 cache_value longtext not null comment 'The output HTML',
2796 cache_meta mediumtext not null comment 'JSON headers and meta data',
2797 cache_expire int(10) default 0 not null comment 'Unix timestamp of when the cache expires'
2798)
2799 collate = utf8mb4_unicode_ci;
2800
2801create index cache_expire
2802 on invcore_output_cache (cache_expire);
2803
2804create table invcore_permission_index
2805(
2806 perm_id int unsigned auto_increment
2807 primary key,
2808 app varchar(32) default '' not null,
2809 perm_type varchar(32) default '' not null,
2810 perm_type_id int unsigned default 0 not null,
2811 perm_view text not null,
2812 perm_2 text null,
2813 perm_3 text null,
2814 perm_4 text null,
2815 perm_5 text null,
2816 perm_6 text null,
2817 perm_7 text null,
2818 owner_only tinyint(1) default 0 not null,
2819 friend_only tinyint(1) default 0 not null,
2820 authorized_users varchar(255) null,
2821 constraint perm_type
2822 unique (app, perm_type, perm_type_id)
2823)
2824 collate = utf8mb4_unicode_ci;
2825
2826create index perm_index
2827 on invcore_permission_index (perm_type, perm_type_id);
2828
2829create table invcore_pfields_content
2830(
2831 member_id bigint unsigned default 0 not null
2832 primary key,
2833 field_1 mediumtext null
2834)
2835 collate = utf8mb4_unicode_ci;
2836
2837create table invcore_pfields_data
2838(
2839 pf_id smallint(5) auto_increment
2840 primary key,
2841 pf_content text null,
2842 pf_type varchar(250) default '' not null,
2843 pf_not_null tinyint(1) default 0 not null,
2844 pf_member_hide varchar(32) default 'all' not null,
2845 pf_max_input smallint default 0 not null,
2846 pf_member_edit tinyint(1) default 0 not null,
2847 pf_position smallint default 0 not null,
2848 pf_show_on_reg tinyint(1) default 0 not null,
2849 pf_input_format text null,
2850 pf_format text null comment 'Custom formatting for topic view',
2851 pf_group_id mediumint(4) unsigned default 0 not null,
2852 pf_search_type varchar(5) default '' not null,
2853 pf_filtering tinyint(1) default 0 not null,
2854 pf_multiple tinyint(1) default 0 not null,
2855 pf_allow_attachments tinyint(1) unsigned default 1 not null,
2856 pf_topic_hide varchar(32) default 'hide' not null,
2857 pf_admin_only tinyint(1) default 0 not null,
2858 pf_profile_format text null comment 'Custom formatting for profiles'
2859)
2860 collate = utf8mb4_unicode_ci;
2861
2862create index public_fields
2863 on invcore_pfields_data (pf_admin_only);
2864
2865create table invcore_pfields_groups
2866(
2867 pf_group_id mediumint(4) unsigned auto_increment
2868 primary key,
2869 pf_group_order mediumint(4) default 0 not null
2870)
2871 collate = utf8mb4_unicode_ci;
2872
2873create table invcore_plugins
2874(
2875 plugin_id bigint unsigned auto_increment comment 'ID Number'
2876 primary key,
2877 plugin_name varchar(255) default '' not null comment 'Name',
2878 plugin_update_check text null comment 'URL to check for updates',
2879 plugin_author varchar(255) null comment 'Author name',
2880 plugin_website varchar(255) null comment 'Author website URL',
2881 plugin_location varchar(80) default '' null comment 'Folder name for IN_DEV mode',
2882 plugin_version_long mediumint unsigned default 10000 not null comment 'Currently installed version (system)',
2883 plugin_version_human varchar(14) default '1.0.0' not null comment 'Currently installed version (human-readable)',
2884 plugin_update_check_last int(10) null comment 'Unix timestamp that the plugin was last checked for updates',
2885 plugin_update_check_data text null comment 'JSON data returned in last update check',
2886 plugin_enabled tinyint(1) default 1 not null comment 'Plugin is enabled?',
2887 plugin_order bigint unsigned default 0 not null comment 'The order plugins are executed',
2888 plugin_diagnostic_url varchar(255) null comment 'The URL to send diagnostic reports to.'
2889)
2890 collate = utf8mb4_unicode_ci;
2891
2892create table invcore_polls
2893(
2894 pid mediumint(8) auto_increment
2895 primary key,
2896 start_date int(10) null,
2897 choices text null,
2898 starter_id bigint unsigned default 0 not null,
2899 votes bigint default 0 not null,
2900 poll_question varchar(255) null,
2901 poll_only tinyint(1) default 0 not null,
2902 poll_view_voters int(1) default 0 not null,
2903 poll_closed int(1) unsigned default 0 not null,
2904 poll_close_date int(10) default -1 not null
2905)
2906 collate = utf8mb4_unicode_ci;
2907
2908create index member
2909 on invcore_polls (starter_id);
2910
2911create index poll_close_date
2912 on invcore_polls (poll_close_date);
2913
2914create table invcore_post_before_registering
2915(
2916 email varchar(255) not null comment 'The guest''s email address',
2917 class varchar(255) not null comment 'The class of the content that was posted',
2918 id bigint unsigned default 0 not null comment 'The ID of the content that was posted',
2919 timestamp int unsigned default 0 not null comment 'Unix timestamp of when the content was posted',
2920 secret char(32) not null comment 'A secret key which is used to link the user to this content',
2921 member bigint unsigned null comment 'The ID number of the member account once it has been created but before it has been validated',
2922 followup int unsigned null comment 'Unix timestamp of when followup email was sent',
2923 language bigint(10) unsigned not null comment 'ID number of the language that the guest had selected',
2924 constraint content_lookup
2925 unique (class, id)
2926)
2927 collate = utf8mb4_unicode_ci;
2928
2929create index member
2930 on invcore_post_before_registering (member);
2931
2932create index secret
2933 on invcore_post_before_registering (secret);
2934
2935create table invcore_profanity_filters
2936(
2937 wid int(3) auto_increment
2938 primary key,
2939 type varchar(250) default '' not null,
2940 swop varchar(250) null,
2941 m_exact tinyint(1) default 0 null,
2942 action set ('swap', 'moderate') default 'swap' not null
2943)
2944 collate = utf8mb4_unicode_ci;
2945
2946create index m_exact
2947 on invcore_profanity_filters (m_exact);
2948
2949create table invcore_profile_completion
2950(
2951 id bigint unsigned auto_increment comment 'ID Number'
2952 primary key,
2953 member_id bigint default 0 not null,
2954 step_id int default 0 not null,
2955 completed int null
2956)
2957 collate = utf8mb4_unicode_ci;
2958
2959create index member_id
2960 on invcore_profile_completion (member_id);
2961
2962create index step_id
2963 on invcore_profile_completion (step_id);
2964
2965create table invcore_profile_steps
2966(
2967 step_id bigint unsigned auto_increment comment 'ID Number'
2968 primary key,
2969 step_completion_act varchar(255) null,
2970 step_required bit default b'0' not null,
2971 step_extension varchar(255) null,
2972 step_subcompletion_act text null,
2973 step_position int unsigned default 0 not null
2974)
2975 collate = utf8mb4_unicode_ci;
2976
2977create table invcore_question_and_answer
2978(
2979 qa_id int auto_increment
2980 primary key,
2981 qa_answers text null
2982)
2983 collate = utf8mb4_unicode_ci;
2984
2985create table invcore_queue
2986(
2987 id bigint unsigned auto_increment comment 'ID Number'
2988 primary key,
2989 data mediumtext not null,
2990 offset bigint default 0 not null,
2991 date int(10) default 0 not null,
2992 app varchar(32) null,
2993 `key` varchar(32) default '' not null,
2994 priority tinyint(1) unsigned default 5 not null comment 'Order to run. Values 1 to 5 are allowed, 1 being highest priority.'
2995)
2996 collate = utf8mb4_unicode_ci;
2997
2998create index queue_key
2999 on invcore_queue (`key`);
3000
3001create index task_lookup
3002 on invcore_queue (app, `key`);
3003
3004create table invcore_ratings
3005(
3006 id bigint unsigned auto_increment comment 'ID Number'
3007 primary key,
3008 class varchar(100) default '' not null comment 'The content item classname',
3009 item_id bigint unsigned default 0 not null comment 'The content item ID',
3010 member bigint unsigned default 0 not null comment 'The member ID',
3011 rating tinyint(1) unsigned default 0 not null comment 'The rating',
3012 ip varchar(46) default '' not null comment 'IP address',
3013 constraint member_lookup
3014 unique (member, class, item_id)
3015)
3016 collate = utf8mb4_unicode_ci;
3017
3018create index ip
3019 on invcore_ratings (ip);
3020
3021create index lookup
3022 on invcore_ratings (class, item_id);
3023
3024create table invcore_rc_comments
3025(
3026 id int(10) auto_increment
3027 primary key,
3028 rid int default 0 not null,
3029 comment text not null,
3030 comment_by bigint unsigned default 0 not null,
3031 comment_date int(10) default 0 not null,
3032 approved tinyint default 1 not null,
3033 edit_date int default 0 not null,
3034 author_name varchar(255) null,
3035 ip_address varchar(46) null
3036)
3037 collate = utf8mb4_unicode_ci;
3038
3039create index ip_address
3040 on invcore_rc_comments (ip_address);
3041
3042create index member
3043 on invcore_rc_comments (comment_by);
3044
3045create index report_comments
3046 on invcore_rc_comments (rid, approved, comment_date);
3047
3048create table invcore_rc_index
3049(
3050 id bigint unsigned auto_increment comment 'ID Number'
3051 primary key,
3052 class varchar(255) default '' not null comment 'Indicates the type of content that was reported',
3053 content_id bigint unsigned default 0 not null comment 'The ID number of the content that was reported.',
3054 perm_id bigint(29) unsigned null comment 'The ID number from the core_permission_index table which indicates who can view this report.',
3055 status enum ('1', '2', '3') default '1' not null comment '1 = New report. 2 = Under Review. 3 = Complete.',
3056 num_reports int unsigned default 1 not null comment 'Number of reports received.',
3057 num_comments int unsigned default 0 not null comment 'Number of comments moderators have made on this report.',
3058 first_report_by bigint unsigned default 0 not null comment 'The ID number of the member who submitted the first report.',
3059 first_report_date int(10) default 0 not null comment 'Unix timestamp of when the first report was submitted.',
3060 last_updated int(10) null comment 'Unix timestamp of the last time a comment or report was made (for read/unread marking)',
3061 author bigint unsigned default 0 not null comment 'The ID number of the user who submitted the reported content.',
3062 auto_moderation_exempt int(1) unsigned not null
3063)
3064 collate = utf8mb4_unicode_ci;
3065
3066create index author
3067 on invcore_rc_index (author);
3068
3069create index content_lookup
3070 on invcore_rc_index (class, content_id);
3071
3072create index first_report_by
3073 on invcore_rc_index (first_report_by);
3074
3075create index report_count
3076 on invcore_rc_index (perm_id, status);
3077
3078create table invcore_rc_reports
3079(
3080 id int(10) auto_increment
3081 primary key,
3082 rid int default 0 not null,
3083 report mediumtext not null,
3084 report_by bigint unsigned default 0 not null,
3085 date_reported int(10) default 0 not null,
3086 ip_address varchar(46) null comment 'The IP address of the user reporting the content',
3087 report_type int(1) unsigned default 0 not null
3088)
3089 collate = utf8mb4_unicode_ci;
3090
3091create index ip_address
3092 on invcore_rc_reports (ip_address);
3093
3094create index report_by
3095 on invcore_rc_reports (report_by);
3096
3097create index rid
3098 on invcore_rc_reports (rid);
3099
3100create table invcore_reactions
3101(
3102 reaction_id bigint unsigned auto_increment comment 'ID Number'
3103 primary key,
3104 reaction_value tinyint(1) default 1 not null,
3105 reaction_icon varchar(255) null,
3106 reaction_position int(10) null,
3107 reaction_enabled tinyint(1) default 1 not null
3108)
3109 collate = utf8mb4_unicode_ci;
3110
3111create table invcore_reputation_index
3112(
3113 id bigint(10) unsigned auto_increment
3114 primary key,
3115 member_id bigint unsigned default 0 not null,
3116 app varchar(32) default '' not null,
3117 type varchar(32) default '' not null,
3118 type_id int unsigned default 0 not null,
3119 rep_date int unsigned default 0 not null,
3120 rep_rating tinyint(1) default 0 not null,
3121 member_received bigint unsigned default 0 not null,
3122 rep_class varchar(100) null,
3123 item_id int unsigned default 0 not null,
3124 class_type_id_hash char(32) default '' not null comment 'MD5 of rep_class:type_id',
3125 reaction bigint default 0 not null
3126)
3127 collate = utf8mb4_unicode_ci;
3128
3129create index app
3130 on invcore_reputation_index (app, type, type_id, member_id, member_received);
3131
3132create index class_type_id_hash
3133 on invcore_reputation_index (class_type_id_hash, rep_date, rep_rating);
3134
3135create index leaderboard
3136 on invcore_reputation_index (rep_class, rep_date, member_received, rep_rating);
3137
3138create index member_received
3139 on invcore_reputation_index (member_received, rep_date, rep_class, rep_rating);
3140
3141create index member_rep
3142 on invcore_reputation_index (member_id, rep_rating, rep_date);
3143
3144create index rep_class
3145 on invcore_reputation_index (rep_class, type_id);
3146
3147create index rep_date
3148 on invcore_reputation_index (rep_date);
3149
3150create table invcore_reputation_leaderboard_history
3151(
3152 leader_date int(10) default 0 not null,
3153 leader_member_id bigint unsigned default 0 not null,
3154 leader_position int(1) default 0 not null,
3155 leader_rep_total int(10) default 0 not null,
3156 constraint leader_date
3157 unique (leader_date, leader_member_id)
3158)
3159 collate = utf8mb4_unicode_ci;
3160
3161create index first_place
3162 on invcore_reputation_leaderboard_history (leader_position, leader_member_id);
3163
3164create table invcore_reputation_levels
3165(
3166 level_id mediumint(8) auto_increment
3167 primary key,
3168 level_points int(10) default 0 not null,
3169 level_image varchar(255) null
3170)
3171 collate = utf8mb4_unicode_ci;
3172
3173create table invcore_rss_export
3174(
3175 rss_id bigint unsigned auto_increment comment 'ID Number'
3176 primary key,
3177 rss_enabled tinyint(1) default 1 not null,
3178 rss_seo_title varchar(255) default '' not null,
3179 rss_configuration mediumtext not null,
3180 rss_count int(10) null,
3181 rss_position int(10) default 0 not null,
3182 rss_groups varchar(255) null
3183)
3184 collate = utf8mb4_unicode_ci;
3185
3186create table invcore_saved_charts
3187(
3188 chart_id bigint unsigned auto_increment comment 'ID Number'
3189 primary key,
3190 chart_member bigint unsigned null,
3191 chart_controller text not null,
3192 chart_configuration text null,
3193 chart_title varchar(255) default '' not null
3194)
3195 collate = utf8mb4_unicode_ci;
3196
3197create index chart_member
3198 on invcore_saved_charts (chart_member);
3199
3200create table invcore_search_index
3201(
3202 index_id bigint unsigned auto_increment comment 'Primary key'
3203 primary key,
3204 index_class varchar(255) null comment 'Content type class e.g. \IPS\forums\Topic',
3205 index_object_id int unsigned default 0 not null comment 'The ID of the object. pid for posts, tid for topics etc',
3206 index_item_id int unsigned default 0 not null comment 'For comments and reviews, the ID of the item it pertains to. For \IPS\Content\Item this would just be NULL',
3207 index_container_id int unsigned default 0 not null comment 'The id of the container for items/comments/reviews',
3208 index_content mediumtext not null comment 'The plain-text content to search',
3209 index_permissions text not null comment 'A comma-delimited list of groups which have permission to view',
3210 index_date_created int unsigned default 0 not null comment 'The object creation date',
3211 index_author int unsigned default 0 not null comment 'The object author id',
3212 index_title varchar(255) null comment 'Content title',
3213 index_date_updated int unsigned null comment 'Object last updated date',
3214 index_hidden tinyint(1) default 0 not null comment '0 = visible, -1 = hidden (previously visible), 1 = unapproved, 2 = parent item hidden',
3215 index_item_index_id bigint unsigned null comment 'The index ID of the item',
3216 index_item_author bigint unsigned null comment 'The author of the item',
3217 index_is_last_comment tinyint(1) default 0 not null comment 'Is this the last comment/review on an item?',
3218 index_class_type_id_hash char(32) default '' not null comment 'MD5 of (index_class:index_object_id)',
3219 index_container_class varchar(255) null comment 'The class of the container that relates to index_container_id',
3220 index_club_id bigint unsigned null comment 'The club ID, or NULL if it does not belong to a club',
3221 index_date_commented int unsigned null comment 'Object last commented/reviewed date',
3222 constraint object
3223 unique (index_class, index_object_id)
3224)
3225 collate = utf8mb4_unicode_ci;
3226
3227create index author_lookup
3228 on invcore_search_index (index_author, index_class, index_hidden, index_date_updated, index_date_commented);
3229
3230create index author_posted_in
3231 on invcore_search_index (index_author, index_item_index_id);
3232
3233create index container
3234 on invcore_search_index (index_class, index_container_id, index_date_commented);
3235
3236create index index_class_and_object_id
3237 on invcore_search_index (index_class, index_object_id);
3238
3239create index index_class_type_id_hash
3240 on invcore_search_index (index_class_type_id_hash);
3241
3242create index index_club_id
3243 on invcore_search_index (index_club_id);
3244
3245create fulltext index index_content
3246 on invcore_search_index (index_content, index_title);
3247
3248create index index_date_commented
3249 on invcore_search_index (index_date_commented, index_date_updated);
3250
3251create index index_date_created
3252 on invcore_search_index (index_date_created, index_date_commented);
3253
3254create index index_date_updated
3255 on invcore_search_index (index_date_updated, index_date_commented);
3256
3257create index index_hidden
3258 on invcore_search_index (index_hidden);
3259
3260create index index_item_author
3261 on invcore_search_index (index_item_author, index_date_commented);
3262
3263create index index_prune
3264 on invcore_search_index (index_item_id, index_date_updated);
3265
3266create index index_stream
3267 on invcore_search_index (index_class, index_item_id, index_date_commented, index_date_updated);
3268
3269create fulltext index index_title
3270 on invcore_search_index (index_title);
3271
3272create index item
3273 on invcore_search_index (index_class, index_item_id);
3274
3275create table invcore_search_index_item_map
3276(
3277 index_author_id int(10) default 0 not null,
3278 index_item_id int(10) default 0 not null,
3279 index_class varchar(80) default '' not null,
3280 constraint search_map
3281 unique (index_item_id, index_class, index_author_id)
3282)
3283 collate = utf8mb4_unicode_ci;
3284
3285create table invcore_search_index_tags
3286(
3287 index_id bigint unsigned default 0 not null comment 'Related rows index ID from core_search_index',
3288 index_tag varchar(255) null,
3289 index_is_prefix tinyint(1) unsigned default 0 not null comment 'Signifies if this tag is the item prefix'
3290)
3291 collate = utf8mb4_unicode_ci;
3292
3293create index index_id
3294 on invcore_search_index_tags (index_id);
3295
3296create index index_tag
3297 on invcore_search_index_tags (index_tag);
3298
3299create table invcore_security_answers
3300(
3301 answer_question_id bigint unsigned not null comment 'The ID number of the question this is an answer for',
3302 answer_member_id bigint unsigned not null comment 'The member ID this answer is for',
3303 answer_answer text null comment 'The answer, stored as an \IPS\Text\Encrypt tag',
3304 answer_is_chosen tinyint(1) unsigned default 0 not null comment 'When the authentication form is chosen, one answer is remembered with this flag so that refreshing produces the same question',
3305 constraint question_and_member
3306 unique (answer_question_id, answer_member_id)
3307)
3308 collate = utf8mb4_unicode_ci;
3309
3310create index member
3311 on invcore_security_answers (answer_member_id);
3312
3313create table invcore_security_questions
3314(
3315 question_id bigint unsigned auto_increment comment 'ID Number'
3316 primary key,
3317 question_order int(10) null comment 'Index for ordering questions'
3318)
3319 collate = utf8mb4_unicode_ci;
3320
3321create table invcore_seo_meta
3322(
3323 meta_id int auto_increment
3324 primary key,
3325 meta_url varchar(255) default '' not null,
3326 meta_tags text null,
3327 meta_title varchar(255) null
3328)
3329 collate = utf8mb4_unicode_ci;
3330
3331create index url
3332 on invcore_seo_meta (meta_url);
3333
3334create table invcore_sessions
3335(
3336 id varchar(128) default '0' not null
3337 primary key,
3338 member_name varchar(255) null,
3339 seo_name varchar(255) default '' not null,
3340 member_id bigint unsigned null,
3341 ip_address varchar(46) null,
3342 browser text not null,
3343 running_time int(10) null,
3344 login_type tinyint(1) default 0 null,
3345 member_group smallint(3) null,
3346 in_error tinyint(1) default 0 not null,
3347 current_appcomponent varchar(100) default '' not null,
3348 current_module varchar(100) default '' not null,
3349 current_controller varchar(100) default '' null,
3350 uagent_key varchar(200) default '' not null,
3351 uagent_version varchar(100) default '' not null,
3352 uagent_type varchar(200) default '' not null,
3353 search_thread_id int default 0 not null,
3354 search_thread_time int default 0 not null,
3355 data blob null,
3356 location_url text null,
3357 location_lang varchar(255) null,
3358 location_data text null,
3359 location_permissions varchar(1024) null,
3360 current_id int unsigned null,
3361 theme_id int unsigned default 0 not null,
3362 in_editor int unsigned default 0 not null comment 'User is currently using an editor'
3363)
3364 collate = utf8mb4_unicode_ci;
3365
3366create index ip_address
3367 on invcore_sessions (ip_address, member_id);
3368
3369create index login_type
3370 on invcore_sessions (login_type, running_time, member_id, member_group);
3371
3372create index member_id
3373 on invcore_sessions (member_id, ip_address);
3374
3375create index running_time
3376 on invcore_sessions (running_time);
3377
3378create table invcore_share_links
3379(
3380 share_id int unsigned auto_increment
3381 primary key,
3382 share_title varchar(255) default '' not null,
3383 share_key varchar(50) default '' not null,
3384 share_enabled int(1) default 0 not null,
3385 share_position int(3) default 0 not null,
3386 share_canonical int(1) default 1 not null,
3387 share_groups text null,
3388 share_autoshare tinyint unsigned default 0 not null comment 'Flag to denote whether to show this service as an auto share option when creating content.'
3389)
3390 collate = utf8mb4_unicode_ci;
3391
3392create table invcore_sitemap
3393(
3394 sitemap varchar(255) default '' not null comment 'The sitemap key'
3395 primary key,
3396 data mediumtext null comment 'The sitemap XML',
3397 updated int(10) default 0 not null comment 'Unix timestamp of last update',
3398 last_id bigint unsigned default 0 not null comment 'Stores the last ID included in this sitemap'
3399)
3400 collate = utf8mb4_unicode_ci;
3401
3402create table invcore_social_promote
3403(
3404 promote_id bigint auto_increment
3405 primary key,
3406 promote_class varchar(255) default '' not null comment 'The content class',
3407 promote_class_id int(10) default 0 not null comment 'The main ID for the content item/comment',
3408 promote_text mediumtext not null comment 'JSON array of text for all services',
3409 promote_short_link text not null,
3410 promote_media text not null comment 'JSON array of custom uploads',
3411 promote_added int(10) default 0 not null comment 'The date the item was authored or last edited',
3412 promote_scheduled int(10) default 0 not null comment 'The date the item is to be sent out',
3413 promote_sent int(10) default 0 not null comment 'The date the item was sent out',
3414 promote_added_by bigint unsigned default 0 not null,
3415 promote_schedule_auto int(1) unsigned default 0 not null comment 'A flag to note if this has been auto scheduled (1) or manually scheduled (0)',
3416 promote_share_to text null comment 'JSON array of share service names (facebook, twitter)',
3417 promote_images text not null comment 'JSON array of file locations (container/file.jpg)',
3418 promote_returned text null comment 'JSON array of data returned from the post',
3419 promote_failed int(2) unsigned default 0 not null comment 'The number of times this has failed to send',
3420 promote_internal tinyint(1) unsigned default 0 not null comment 'Has this been shared internally?',
3421 promote_form_data text null,
3422 promote_hide tinyint(1) unsigned default 0 null comment 'Allows rows to not be shown in our picks'
3423)
3424 collate = utf8mb4_unicode_ci;
3425
3426create index our_picks
3427 on invcore_social_promote (promote_internal, promote_sent, promote_hide);
3428
3429create index promote_lookup
3430 on invcore_social_promote (promote_class, promote_class_id, promote_internal);
3431
3432create index promote_sent
3433 on invcore_social_promote (promote_sent, promote_scheduled);
3434
3435create table invcore_social_promote_content
3436(
3437 response_id int(10) auto_increment
3438 primary key,
3439 response_promote_id int(10) default 0 not null,
3440 response_promote_key varchar(100) default '' not null,
3441 response_json mediumtext not null comment 'JSON array showing the response',
3442 response_failed int(1) default 0 not null,
3443 response_date int(10) default 0 not null,
3444 response_sent_date int unsigned default 0 null comment 'Timestamp of when sent was triggered as this will vary from actual response time',
3445 response_service_id varchar(100) null comment 'Specific page/group ID for this service if applicable'
3446)
3447 collate = utf8mb4_unicode_ci;
3448
3449create index response_promote_id
3450 on invcore_social_promote_content (response_promote_id, response_promote_key);
3451
3452create index response_sent_date
3453 on invcore_social_promote_content (response_sent_date);
3454
3455create table invcore_social_promote_sharers
3456(
3457 sharer_id int(10) auto_increment
3458 primary key,
3459 sharer_key varchar(100) default '' not null,
3460 sharer_settings mediumtext not null comment 'JSON array of promotion settings',
3461 sharer_enabled bit default b'0' not null
3462)
3463 collate = utf8mb4_unicode_ci;
3464
3465create index enabled
3466 on invcore_social_promote_sharers (sharer_enabled);
3467
3468create table invcore_soft_delete_log
3469(
3470 sdl_id int(10) auto_increment
3471 primary key,
3472 sdl_obj_id int(10) default 0 not null,
3473 sdl_obj_key varchar(128) default '' not null,
3474 sdl_obj_member_id bigint unsigned default 0 not null,
3475 sdl_obj_date int(10) default 0 not null,
3476 sdl_obj_reason text null,
3477 sdl_locked int(1) default 0 not null
3478)
3479 collate = utf8mb4_unicode_ci;
3480
3481create index look_up
3482 on invcore_soft_delete_log (sdl_obj_id, sdl_obj_key);
3483
3484create index member
3485 on invcore_soft_delete_log (sdl_obj_member_id);
3486
3487create table invcore_spam_service_log
3488(
3489 id int unsigned auto_increment
3490 primary key,
3491 log_date int unsigned default 0 not null,
3492 log_code smallint(1) unsigned default 0 not null,
3493 log_msg varchar(32) default '' not null,
3494 email_address varchar(255) default '' not null,
3495 ip_address varchar(46) default '' not null
3496)
3497 collate = utf8mb4_unicode_ci;
3498
3499create index log_date
3500 on invcore_spam_service_log (log_date);
3501
3502create table invcore_spam_whitelist
3503(
3504 whitelist_id bigint unsigned auto_increment comment 'ID Number'
3505 primary key,
3506 whitelist_type varchar(10) default 'ip' not null,
3507 whitelist_content varchar(250) null,
3508 whitelist_date int(10) default 0 not null,
3509 whitelist_reason varchar(255) null
3510)
3511 collate = utf8mb4_unicode_ci;
3512
3513create index whitelist
3514 on invcore_spam_whitelist (whitelist_content, whitelist_type);
3515
3516create table invcore_statistics
3517(
3518 id bigint unsigned auto_increment comment 'ID Number'
3519 primary key,
3520 type varchar(255) default '' not null comment 'Type of statistic',
3521 value_1 bigint default 0 not null,
3522 value_2 bigint default 0 not null,
3523 value_3 bigint default 0 not null,
3524 value_4 varchar(255) null,
3525 extra_data text null,
3526 time int unsigned default 0 not null
3527)
3528 collate = utf8mb4_unicode_ci;
3529
3530create index type_int_value
3531 on invcore_statistics (type, time, value_1);
3532
3533create index type_text_value
3534 on invcore_statistics (type, time, value_4);
3535
3536create table invcore_store
3537(
3538 store_key varchar(150) default '' not null comment 'Key'
3539 primary key,
3540 store_value longblob null comment 'Value'
3541)
3542 collate = utf8mb4_unicode_ci;
3543
3544create table invcore_streams
3545(
3546 id bigint unsigned auto_increment comment 'ID Number'
3547 primary key,
3548 classes text null comment 'The classes this stream will pull from or null for all',
3549 containers text null comment 'A JSON-encoded array of classes to allowed containers or NULL for all',
3550 ownership enum ('all', 'started', 'postedin', 'custom') default 'all' not null comment 'Stream pulls all content or only content the viewer has started or posted in',
3551 `read` enum ('all', 'unread') default 'all' not null comment 'If the stream only includes unread items',
3552 follow enum ('all', 'followed') default 'all' not null comment 'If the stream only shows followed content',
3553 date_type enum ('all', 'last_visit', 'custom', 'relative') default 'all' not null comment 'The date range for the stream',
3554 date_start int(10) null comment 'If using a custom date, the start date',
3555 date_end int(10) null comment 'If using a custom date, the end date',
3556 position int(10) default 0 not null comment 'The position in the list',
3557 member bigint null comment 'The member who created the stream or null for a global stream',
3558 sort enum ('newest', 'oldest') default 'newest' not null comment 'Whether the stream sorts newest first or oldest first by default',
3559 date_relative_days int unsigned null comment 'If using a relative date period, the number of days',
3560 custom_members text null comment 'If using custom ownership, the member IDs',
3561 tags text null comment 'Tags to match',
3562 followed_types set ('containers', 'items', 'members') null comment 'If filtering by followed content, what kind of follows to include',
3563 include_comments tinyint(1) default 1 not null comment 'Should comments and reviews be included?',
3564 title varchar(255) null comment 'If this is a member-created stream, the title (admin created streams use language strings)',
3565 `default` tinyint(1) unsigned default 0 not null comment 'Default item shows to the right of the top breadcrumb navigation',
3566 unread_links tinyint(1) unsigned default 0 not null comment 'Should a item link take you to the first unread item?',
3567 default_view enum ('expanded', 'condensed') default 'expanded' not null comment 'Should a stream show in a condensed or expanded layout by default',
3568 clubs text null comment 'Comma-delimited list of club IDs or 0 to exclude clubs, or NULL to not filter by clubs'
3569)
3570 collate = utf8mb4_unicode_ci;
3571
3572create index member
3573 on invcore_streams (member);
3574
3575create table invcore_sys_conf_settings
3576(
3577 conf_id int(10) auto_increment
3578 primary key,
3579 conf_key varchar(100) default '' not null,
3580 conf_value mediumtext null,
3581 conf_default text null,
3582 conf_keywords text null,
3583 conf_app varchar(255) null,
3584 conf_plugin bigint unsigned null,
3585 conf_report enum ('full', 'bool') null,
3586 constraint conf_key
3587 unique (conf_key)
3588)
3589 collate = utf8mb4_unicode_ci;
3590
3591create table invcore_sys_cp_sessions
3592(
3593 session_id varchar(128) default '' not null
3594 primary key,
3595 session_ip_address varchar(46) default '' not null,
3596 session_member_name varchar(255) default '' not null,
3597 session_member_id bigint unsigned default 0 not null,
3598 session_location varchar(255) default '' not null,
3599 session_log_in_time int(10) default 0 not null,
3600 session_running_time int(10) default 0 not null,
3601 session_url text null,
3602 session_app_data blob null,
3603 session_cookie_key char(32) not null
3604)
3605 collate = utf8mb4_unicode_ci;
3606
3607create index session_member_id
3608 on invcore_sys_cp_sessions (session_member_id);
3609
3610create index session_running_time
3611 on invcore_sys_cp_sessions (session_running_time);
3612
3613create table invcore_sys_lang
3614(
3615 lang_id mediumint(4) unsigned auto_increment
3616 primary key,
3617 lang_short varchar(100) default '' not null,
3618 lang_title varchar(255) default '' not null,
3619 lang_default tinyint(1) unsigned default 0 not null,
3620 lang_isrtl tinyint(1) unsigned default 0 not null,
3621 lang_protected tinyint(1) default 0 not null,
3622 lang_order mediumint(5) unsigned null,
3623 lang_enabled bit default b'1' not null comment 'Indicates if the language is enabled'
3624)
3625 collate = utf8mb4_unicode_ci;
3626
3627create index lang_default
3628 on invcore_sys_lang (lang_default);
3629
3630create index lang_order
3631 on invcore_sys_lang (lang_order);
3632
3633create index lang_short
3634 on invcore_sys_lang (lang_short);
3635
3636create table invcore_sys_lang_words
3637(
3638 word_id int unsigned auto_increment comment 'UID'
3639 primary key,
3640 lang_id mediumint(4) unsigned default 0 not null comment 'The ID number for the language',
3641 word_app varchar(80) default '' null comment 'The application key, if word belongs to an application',
3642 word_plugin bigint null comment 'The plugin ID, if word belongs to a plugin',
3643 word_theme int(10) null comment 'The theme ID, if the language string belongs to a theme.',
3644 word_key varchar(64) default '' not null comment 'The key',
3645 word_default mediumtext null comment 'The default value',
3646 word_custom mediumtext null comment 'The current value',
3647 word_default_version varchar(10) null comment 'The application/plugin version the word was last changed in',
3648 word_custom_version varchar(10) null comment 'The application/plugin version installed when this word was customised',
3649 word_js tinyint(1) unsigned default 0 not null comment 'Binary if this word should be available to JavaScript',
3650 word_export tinyint(1) unsigned default 1 not null comment 'Binary if this word should be exported (i.e. is not a forum/etc name)',
3651 constraint word_key
3652 unique (word_key, word_app, lang_id, word_js)
3653)
3654 collate = utf8mb4_unicode_ci;
3655
3656create index word_find
3657 on invcore_sys_lang_words (lang_id, word_app);
3658
3659create index word_js
3660 on invcore_sys_lang_words (word_js);
3661
3662create table invcore_sys_social_group_members
3663(
3664 group_id bigint unsigned not null,
3665 member_id bigint unsigned null
3666)
3667 collate = utf8mb4_unicode_ci;
3668
3669create index `group`
3670 on invcore_sys_social_group_members (group_id);
3671
3672create index member
3673 on invcore_sys_social_group_members (member_id);
3674
3675create table invcore_sys_social_groups
3676(
3677 group_id bigint unsigned auto_increment comment 'ID Number'
3678 primary key,
3679 owner_id bigint unsigned null
3680)
3681 collate = utf8mb4_unicode_ci;
3682
3683create index owner
3684 on invcore_sys_social_groups (owner_id);
3685
3686create table invcore_tags
3687(
3688 tag_id bigint auto_increment
3689 primary key,
3690 tag_aai_lookup char(32) default '' not null,
3691 tag_aap_lookup char(32) default '' not null,
3692 tag_meta_app varchar(200) default '' not null,
3693 tag_meta_area varchar(200) default '' not null,
3694 tag_meta_id int(10) default 0 not null,
3695 tag_meta_parent_id int(10) default 0 not null,
3696 tag_member_id bigint default 0 not null,
3697 tag_added int(10) default 0 not null,
3698 tag_prefix int(1) default 0 not null,
3699 tag_text varchar(255) null
3700)
3701 collate = utf8mb4_unicode_ci;
3702
3703create index tag_aai_lookup
3704 on invcore_tags (tag_aai_lookup);
3705
3706create index tag_aap_lookup
3707 on invcore_tags (tag_aap_lookup, tag_text);
3708
3709create index tag_added
3710 on invcore_tags (tag_added);
3711
3712create index tag_app
3713 on invcore_tags (tag_meta_app, tag_meta_area, tag_meta_id);
3714
3715create index tag_member_id
3716 on invcore_tags (tag_member_id);
3717
3718create index tag_text
3719 on invcore_tags (tag_text);
3720
3721create table invcore_tags_cache
3722(
3723 tag_cache_key char(32) default '' not null,
3724 tag_cache_text text null,
3725 tag_cache_date int(10) default 0 not null,
3726 constraint tag_cache_key
3727 unique (tag_cache_key)
3728)
3729 collate = utf8mb4_unicode_ci;
3730
3731create table invcore_tags_perms
3732(
3733 tag_perm_aai_lookup char(32) default '' not null,
3734 tag_perm_aap_lookup char(32) default '' not null,
3735 tag_perm_text varchar(2000) default '' not null,
3736 tag_perm_visible int(1) default 1 not null,
3737 constraint tag_perm_aai_lookup
3738 unique (tag_perm_aai_lookup)
3739)
3740 collate = utf8mb4_unicode_ci;
3741
3742create index tag_lookup
3743 on invcore_tags_perms (tag_perm_text, tag_perm_visible);
3744
3745create index tag_perm_aap_lookup
3746 on invcore_tags_perms (tag_perm_aap_lookup);
3747
3748create table invcore_tasks
3749(
3750 id bigint unsigned auto_increment comment 'ID Number'
3751 primary key,
3752 app varchar(255) null comment 'The application key which owns the task.',
3753 `key` varchar(100) default '' not null comment 'The task key.',
3754 frequency varchar(32) default '' not null comment 'DateInterval specification for the frequency the task runs.',
3755 next_run bigint(10) unsigned default 0 not null comment 'Unix timestamp of next time task should be ran.',
3756 running bit default b'0' not null comment 'Indicates if task is currently running (to prevent duplicate runs).',
3757 enabled bit default b'1' not null comment 'Some tasks may set this automatically when they do/don''t have things to do.',
3758 plugin bigint(10) null comment 'The plugin ID number which owns the task.',
3759 lock_count tinyint(1) unsigned default 0 not null comment 'How many times the task has been in a locked state without a successful run. A count of 3 will trigger a dashboard warning.',
3760 last_run bigint(10) unsigned default 0 not null comment 'The timestamp the task was last run, 0 if never.',
3761 constraint `key`
3762 unique (`key`)
3763)
3764 comment 'Stores information about tasks' collate = utf8mb4_unicode_ci;
3765
3766create index app
3767 on invcore_tasks (app);
3768
3769create index lock_count
3770 on invcore_tasks (lock_count);
3771
3772create index next_run
3773 on invcore_tasks (enabled, next_run);
3774
3775create table invcore_tasks_log
3776(
3777 task bigint unsigned null comment 'Task ID number',
3778 error bit default b'0' not null,
3779 log text null,
3780 time int unsigned default 0 not null
3781)
3782 collate = utf8mb4_unicode_ci;
3783
3784create index lookup
3785 on invcore_tasks_log (task, time);
3786
3787create index time
3788 on invcore_tasks_log (time);
3789
3790create table invcore_theme_conflict
3791(
3792 conflict_id int(10) auto_increment
3793 primary key,
3794 conflict_set_id int(10) default 0 not null,
3795 conflict_item_id int(10) default 0 not null,
3796 conflict_type varchar(10) default 'html' not null,
3797 conflict_app varchar(100) default '' not null,
3798 conflict_location varchar(255) default '' not null,
3799 conflict_path varchar(255) default '' not null,
3800 conflict_name varchar(255) default '' not null,
3801 conflict_data text null,
3802 conflict_content mediumtext null,
3803 conflict_long_version int(10) default 0 not null,
3804 conflict_date int(10) default 0 not null
3805)
3806 collate = utf8mb4_unicode_ci;
3807
3808create index conflict_set_id
3809 on invcore_theme_conflict (conflict_set_id, conflict_type);
3810
3811create table invcore_theme_content_history
3812(
3813 content_id int(10) auto_increment
3814 primary key,
3815 content_set_id int(10) default 0 not null,
3816 content_type varchar(10) default 'html' not null,
3817 content_app varchar(100) default '' not null,
3818 content_location varchar(255) default '' not null,
3819 content_path varchar(255) default '' not null,
3820 content_name varchar(255) default '' not null,
3821 content_data text null,
3822 content_content mediumtext null,
3823 content_long_version int(10) default 0 not null,
3824 content_date int(10) default 0 not null
3825)
3826 collate = utf8mb4_unicode_ci;
3827
3828create index content_set_id
3829 on invcore_theme_content_history (content_set_id, content_type);
3830
3831create table invcore_theme_css
3832(
3833 css_id int(10) auto_increment
3834 primary key,
3835 css_set_id int(10) default 0 not null,
3836 css_updated int(10) default 0 not null,
3837 css_location varchar(80) default '' not null,
3838 css_content mediumtext null,
3839 css_position int(10) default 0 not null,
3840 css_added_to int(10) default 0 not null,
3841 css_app varchar(50) default '0' not null,
3842 css_app_hide int(1) default 0 not null,
3843 css_attributes text null,
3844 css_modules varchar(250) default '' not null,
3845 css_removed int(1) default 0 not null,
3846 css_master_key varchar(100) default '' not null,
3847 css_path varchar(120) default '' not null,
3848 css_name varchar(80) default '' not null,
3849 css_version varchar(100) null,
3850 css_hidden tinyint unsigned null comment 'Skip this file when compiling',
3851 css_user_edited int(10) default 0 null,
3852 css_plugin bigint unsigned null comment 'The plugin ID, if created by a plugin',
3853 constraint unique_key
3854 unique (css_set_id, css_app, css_location, css_path, css_name)
3855)
3856 collate = utf8mb4_unicode_ci;
3857
3858create index css_app
3859 on invcore_theme_css (css_app);
3860
3861create index css_set_id
3862 on invcore_theme_css (css_set_id);
3863
3864create table invcore_theme_resources
3865(
3866 resource_id int(10) auto_increment
3867 primary key,
3868 resource_set_id int(10) default 0 not null,
3869 resource_app varchar(32) default '' not null,
3870 resource_location varchar(32) default '' not null,
3871 resource_path varchar(255) default '' not null,
3872 resource_name varchar(255) default '' not null,
3873 resource_added int(10) default 0 not null,
3874 resource_filename varchar(255) null comment 'File object URI',
3875 resource_plugin bigint unsigned null comment 'The plugin ID, if created by a plugin',
3876 resource_data mediumblob null comment 'Stores the image data so the images can be re-written if the file object is lost',
3877 resource_user_edited tinyint(1) default 0 not null
3878)
3879 collate = utf8mb4_unicode_ci;
3880
3881create index resource_app
3882 on invcore_theme_resources (resource_app);
3883
3884create index resource_set_id
3885 on invcore_theme_resources (resource_set_id);
3886
3887create table invcore_theme_settings_fields
3888(
3889 sc_id int(10) auto_increment
3890 primary key,
3891 sc_set_id int(10) default 0 not null,
3892 sc_key varchar(100) default '' not null,
3893 sc_type varchar(255) default '' not null,
3894 sc_multiple int(1) default 0 not null,
3895 sc_content text null,
3896 sc_updated int unsigned default 0 null,
3897 sc_app varchar(100) default 'core' null,
3898 sc_default text null,
3899 sc_tab_key varchar(255) default 'custom' null,
3900 sc_show_in_vse int(1) unsigned default 0 null comment 'Show this theme setting in the easy mode editor',
3901 sc_title varchar(255) null,
3902 sc_order int unsigned default 0 not null,
3903 sc_condition text null,
3904 constraint replace_key
3905 unique (sc_set_id, sc_key, sc_app)
3906)
3907 collate = utf8mb4_unicode_ci;
3908
3909create index sc_lookup
3910 on invcore_theme_settings_fields (sc_set_id, sc_app);
3911
3912create table invcore_theme_settings_values
3913(
3914 sv_id int(10) default 0 not null,
3915 sv_value text null
3916)
3917 collate = utf8mb4_unicode_ci;
3918
3919create index sv_id
3920 on invcore_theme_settings_values (sv_id);
3921
3922create table invcore_theme_templates
3923(
3924 template_id int(10) auto_increment
3925 primary key,
3926 template_set_id int(10) default 0 not null,
3927 template_group varchar(80) default '' not null,
3928 template_content mediumtext null,
3929 template_name varchar(80) null,
3930 template_data text null,
3931 template_updated int(10) default 0 not null,
3932 template_removable int(4) default 0 not null,
3933 template_added_to int(10) default 0 not null,
3934 template_user_added int default 0 not null,
3935 template_user_edited int default 0 not null,
3936 template_master_key varchar(100) default '' not null,
3937 template_location varchar(80) default '' null,
3938 template_app varchar(50) default 'core' not null,
3939 template_version varchar(10) null,
3940 template_plugin bigint unsigned null comment 'The plugin ID, if created by a plugin',
3941 constraint unique_key
3942 unique (template_set_id, template_location, template_app, template_group, template_name)
3943)
3944 collate = utf8mb4_unicode_ci;
3945
3946create index template_app
3947 on invcore_theme_templates (template_app);
3948
3949create index template_master_key
3950 on invcore_theme_templates (template_master_key);
3951
3952create index template_name
3953 on invcore_theme_templates (template_name, template_group);
3954
3955create index template_set_id
3956 on invcore_theme_templates (template_set_id);
3957
3958create table invcore_themes
3959(
3960 set_id int(10) auto_increment
3961 primary key,
3962 set_name varchar(200) default '' not null,
3963 set_key varchar(100) default '' not null,
3964 set_parent_id int(5) default 0 not null,
3965 set_parent_array mediumtext null,
3966 set_child_array mediumtext null,
3967 set_permissions text null,
3968 set_is_default int(1) default 0 not null,
3969 set_author_name varchar(255) default '' not null,
3970 set_author_url varchar(255) default '' not null,
3971 set_emo_dir varchar(255) default 'default' not null,
3972 set_added int(10) default 0 not null,
3973 set_updated int(10) default 0 not null,
3974 set_hide_from_list int(1) default 0 not null,
3975 set_order int(10) default 0 not null,
3976 set_by_skin_gen int(1) default 0 not null,
3977 set_skin_gen_data mediumtext null,
3978 set_template_settings mediumtext null,
3979 set_editor_skin varchar(255) null,
3980 set_logo_data text null comment 'JSON data of logo url, width, height',
3981 set_version varchar(100) null comment 'Human skin set version',
3982 set_long_version int unsigned null comment 'Integer version',
3983 set_update_check varchar(255) null comment 'Remote URL to retrieve update data',
3984 set_update_last_check int unsigned default 0 null comment 'Unix timestamp of last check',
3985 set_update_data text null comment 'JSON update data retrieved from remote URL.',
3986 set_css_map mediumtext null,
3987 set_resource_map mediumtext null,
3988 set_is_acp_default tinyint(1) unsigned default 0 not null comment 'Is default ACP theme?',
3989 set_cache_key char(32) null comment 'The current cache key, updated when any template is changed to force disk caches to rebuild',
3990 set_customized int(1) unsigned default 0 not null
3991)
3992 collate = utf8mb4_unicode_ci;
3993
3994create index parent_set_id
3995 on invcore_themes (set_parent_id, set_id);
3996
3997create index set_is_default
3998 on invcore_themes (set_is_default);
3999
4000create index set_order
4001 on invcore_themes (set_order);
4002
4003create table invcore_upgrade_history
4004(
4005 upgrade_id int(10) auto_increment
4006 primary key,
4007 upgrade_version_id int(10) default 0 not null,
4008 upgrade_version_human varchar(200) default '' not null,
4009 upgrade_date int(10) default 0 not null,
4010 upgrade_mid int(10) default 0 not null,
4011 upgrade_app varchar(32) default 'core' not null
4012)
4013 collate = utf8mb4_unicode_ci;
4014
4015create index upgrades
4016 on invcore_upgrade_history (upgrade_app, upgrade_version_id);
4017
4018create table invcore_validating
4019(
4020 vid varchar(32) default '' not null
4021 primary key,
4022 member_id bigint unsigned default 0 not null,
4023 entry_date int(10) default 0 not null,
4024 coppa_user tinyint(1) default 0 not null,
4025 lost_pass tinyint(1) default 0 not null,
4026 new_reg tinyint(1) default 0 not null,
4027 email_chg tinyint(1) default 0 not null,
4028 ip_address varchar(46) default '0' not null,
4029 user_verified tinyint(1) default 0 not null,
4030 prev_email varchar(150) default '0' not null,
4031 spam_flag tinyint default 0 not null,
4032 email_sent int(10) null comment 'Timestamp of when the confirmation email was last sent',
4033 forgot_security tinyint(1) unsigned default 0 not null,
4034 do_not_delete tinyint(1) unsigned default 0 not null comment 'A flag to specify that the account should not be deleted in the normal cleanup of unvalidated accounts. Used for accounts created in Commerce checkout.',
4035 reg_cancelled int unsigned default 0 not null comment 'Flag to see if the user likes soup. Kidding, timestamp of when they have cancelled their reg.'
4036)
4037 collate = utf8mb4_unicode_ci;
4038
4039create index coppa_user
4040 on invcore_validating (coppa_user);
4041
4042create index ip_address
4043 on invcore_validating (ip_address);
4044
4045create index lost_pass
4046 on invcore_validating (lost_pass, entry_date);
4047
4048create index member_id
4049 on invcore_validating (member_id);
4050
4051create index new_reg
4052 on invcore_validating (new_reg);
4053
4054create index spam_flag
4055 on invcore_validating (spam_flag);
4056
4057create table invcore_view_updates
4058(
4059 classname varchar(150) default '' not null comment 'Name of the class the view is for',
4060 id bigint unsigned null comment 'The ID number for the object'
4061)
4062 collate = utf8mb4_unicode_ci;
4063
4064create index classname_and_id
4065 on invcore_view_updates (classname, id);
4066
4067create table invcore_voters
4068(
4069 vid int(10) auto_increment
4070 primary key,
4071 vote_date int(10) default 0 not null,
4072 tid int(10) default 0 not null,
4073 member_id bigint unsigned default 0 not null,
4074 forum_id smallint(5) default 0 not null,
4075 member_choices text null,
4076 ip_address varchar(46) default '' not null,
4077 poll mediumint unsigned default 0 not null comment 'The poll ID'
4078)
4079 collate = utf8mb4_unicode_ci;
4080
4081create index ip_address
4082 on invcore_voters (ip_address);
4083
4084create index member
4085 on invcore_voters (member_id, poll);
4086
4087create index poll
4088 on invcore_voters (poll);
4089
4090create table invcore_widget_areas
4091(
4092 id bigint unsigned auto_increment comment 'ID Number'
4093 primary key,
4094 app varchar(80) default '' not null,
4095 module varchar(80) default '' not null,
4096 controller varchar(80) default '' not null,
4097 widgets text not null,
4098 area varchar(80) default 'sidebar' not null,
4099 constraint controller
4100 unique (app, module, controller, area)
4101)
4102 collate = utf8mb4_unicode_ci;
4103
4104create table invcore_widget_trash
4105(
4106 id varchar(100) default '' not null comment 'Widgets unique ID'
4107 primary key,
4108 data mediumtext null,
4109 date int unsigned default 0 null comment 'Date trash was added'
4110)
4111 collate = utf8mb4_unicode_ci;
4112
4113create index date
4114 on invcore_widget_trash (date);
4115
4116create table invcore_widgets
4117(
4118 id bigint unsigned auto_increment comment 'ID Number'
4119 primary key,
4120 `key` varchar(255) default '' not null comment 'The widget key',
4121 app varchar(255) null comment 'The application which owns the widget (if applicable)',
4122 plugin varchar(255) null comment 'The plugin which owns the widget (if applicable)',
4123 class varchar(255) default '' not null comment 'What type of widget is this? e.g. \IPS\Widget\StaticCache',
4124 caches mediumtext null comment 'JSON encoded array of cache keys to be referenced when deleting caches',
4125 `restrict` text null comment 'JSON array of owner app restrictions (e.g. core restricts to sidebar only, content restricts to IP.Content only)',
4126 default_area varchar(80) default 'sidebar' not null comment 'If no sidebar configuration is available for a page, all of the widgets for that app are loaded. This field determines which area on the page the default will show.',
4127 allow_reuse tinyint(1) unsigned default 0 not null comment 'If 1, the widget can be re-used instead of having the block vanish from the manager''s block list.',
4128 menu_style set ('menu', 'modal') default 'menu' not null,
4129 embeddable tinyint(1) unsigned default 0 not null comment 'Determines if Pages can embed this widget in a custom block.',
4130 constraint widget_key
4131 unique (`key`)
4132)
4133 collate = utf8mb4_unicode_ci;
4134
4135create index widget_app
4136 on invcore_widgets (app);
4137
4138create index widget_plugin
4139 on invcore_widgets (plugin);
4140
4141create table invdiscord_data
4142(
4143 id bigint unsigned auto_increment comment 'ID Number'
4144 primary key,
4145 member_id int(20) null,
4146 discord_status varchar(255) null,
4147 permission bigint(2) default 0 null,
4148 discord_game varchar(255) null,
4149 discord_id bigint(100) null,
4150 constraint discord_id
4151 unique (discord_id)
4152)
4153 collate = utf8mb4_unicode_ci;
4154
4155create table invdownloads_categories
4156(
4157 cid int(10) auto_increment
4158 primary key,
4159 cparent int(10) default 0 not null,
4160 copen tinyint(1) default 1 not null,
4161 cposition int(10) default 0 not null,
4162 ccfields text null,
4163 cname_furl varchar(255) null,
4164 ctags_disabled tinyint(1) null,
4165 ctags_noprefixes tinyint(1) null,
4166 ctags_predefined text null,
4167 cbitoptions int unsigned default 0 not null comment 'Bitwise options',
4168 ctypes text null comment 'JSON array of allowed file extensions or NULL for no restriction.',
4169 csortorder varchar(255) default 'updated DESC' not null comment 'The default sort order',
4170 cmaxfile int unsigned default 512000 null comment 'Maximum file size',
4171 cmaxss int unsigned default 512 null comment 'Maximum screenshot size',
4172 cmaxdims varchar(128) null comment 'Maximum screenshot dimensions',
4173 cversioning int unsigned null comment 'Number of versions of files to keep. 0 means disabled, NULL means keep all versions.',
4174 clog int unsigned null comment 'Number of days to keep download logs. 0 means disables, NULL means never delete logs.',
4175 cforum_id smallint unsigned null comment 'IP.Board integration: forum ID',
4176 ctopic_prefix varchar(255) null comment 'IP.Board integration: topic prefix',
4177 ctopic_suffix varchar(255) null comment 'IP.Board integration: topic suffix',
4178 clast_file_id bigint unsigned default 0 not null comment 'Latest file ID in this category or any sub-category',
4179 clast_file_date int(11) unsigned default 0 not null comment 'Latest file date in this category or any sub-category',
4180 cclub_id bigint unsigned null comment 'The club ID if this category belongs to a club, or NULL',
4181 cversion_numbers tinyint(1) default 1 not null comment '0 = Disable version numbers, 1 = Allow version numbers, 2 = Require version numbers'
4182)
4183 collate = utf8mb4_unicode_ci;
4184
4185create index cparent
4186 on invdownloads_categories (cparent);
4187
4188create index position_order
4189 on invdownloads_categories (cparent, cposition);
4190
4191create table invdownloads_ccontent
4192(
4193 file_id bigint unsigned default 0 not null
4194 primary key,
4195 updated int(10) default 0 null,
4196 field_14 varchar(255) null
4197)
4198 collate = utf8mb4_unicode_ci;
4199
4200create index field_14
4201 on invdownloads_ccontent (field_14);
4202
4203create table invdownloads_cfields
4204(
4205 cf_id smallint(5) auto_increment
4206 primary key,
4207 cf_content text null,
4208 cf_type varchar(250) default '' not null,
4209 cf_not_null tinyint(1) default 0 not null,
4210 cf_max_input smallint default 0 not null,
4211 cf_input_format text null,
4212 cf_position smallint default 0 not null,
4213 cf_topic tinyint(1) default 0 not null,
4214 cf_search_type varchar(5) default 'loose' not null,
4215 cf_multiple tinyint(1) unsigned default 0 not null,
4216 cf_format text null,
4217 cf_allow_attachments tinyint(1) unsigned default 1 not null,
4218 cf_display_location enum ('below', 'tab', 'sidebar') default 'sidebar' not null comment 'Display location for custom fields'
4219)
4220 collate = utf8mb4_unicode_ci;
4221
4222create index cf_position
4223 on invdownloads_cfields (cf_position);
4224
4225create table invdownloads_comments
4226(
4227 comment_id int(10) auto_increment
4228 primary key,
4229 comment_fid int(10) default 0 not null,
4230 comment_mid bigint unsigned default 0 not null,
4231 comment_date int(10) default 0 not null,
4232 comment_open tinyint(1) default 0 not null,
4233 comment_text mediumtext null,
4234 comment_append_edit tinyint(1) default 0 not null,
4235 comment_edit_time int(10) default 0 not null,
4236 comment_edit_name varchar(255) null,
4237 comment_ip_address varchar(46) null,
4238 comment_author varchar(255) null
4239)
4240 collate = utf8mb4_unicode_ci;
4241
4242create index comment_approved
4243 on invdownloads_comments (comment_open, comment_date);
4244
4245create index comment_fid
4246 on invdownloads_comments (comment_fid, comment_date);
4247
4248create index comment_ip_address
4249 on invdownloads_comments (comment_ip_address);
4250
4251create index comment_mid
4252 on invdownloads_comments (comment_mid, comment_date);
4253
4254create table invdownloads_downloads
4255(
4256 did int(10) auto_increment
4257 primary key,
4258 dfid int(10) default 0 not null,
4259 dtime int(10) default 0 not null,
4260 dip varchar(55) default '0' not null,
4261 dmid bigint unsigned default 0 not null,
4262 dsize bigint unsigned default 0 not null,
4263 dua text null,
4264 dbrowsers varchar(25) default '' not null,
4265 dos varchar(25) default '' not null
4266)
4267 collate = utf8mb4_unicode_ci;
4268
4269create index dfid
4270 on invdownloads_downloads (dfid, dsize);
4271
4272create index dip
4273 on invdownloads_downloads (dip);
4274
4275create index dmid
4276 on invdownloads_downloads (dmid);
4277
4278create index dtime
4279 on invdownloads_downloads (dtime);
4280
4281create table invdownloads_filebackup
4282(
4283 b_id int(10) auto_increment
4284 primary key,
4285 b_fileid int(10) default 0 not null,
4286 b_filetitle varchar(255) default '0' not null,
4287 b_filedesc text null,
4288 b_hidden tinyint(1) default 0 not null,
4289 b_backup int(10) default 0 not null,
4290 b_updated int(10) default 0 not null,
4291 b_records text null,
4292 b_version varchar(32) null,
4293 b_changelog text null
4294)
4295 collate = utf8mb4_unicode_ci;
4296
4297create index b_fileid
4298 on invdownloads_filebackup (b_fileid);
4299
4300create table invdownloads_files
4301(
4302 file_id int(10) auto_increment
4303 primary key,
4304 file_name varchar(255) default '0' not null,
4305 file_cat mediumint(8) default 0 not null,
4306 file_open tinyint(1) default 0 not null,
4307 file_broken tinyint(1) default 0 not null,
4308 file_broken_reason text null,
4309 file_broken_info varchar(255) null,
4310 file_views int(10) default 0 not null,
4311 file_downloads int(10) default 0 not null,
4312 file_submitted int(10) default 0 not null,
4313 file_updated int(10) default 0 not null,
4314 file_desc text null,
4315 file_size bigint default 0 not null,
4316 file_submitter bigint unsigned default 0 not null,
4317 file_approver bigint unsigned default 0 not null,
4318 file_approvedon int(10) default 0 not null,
4319 file_topicid int(10) default 0 not null,
4320 file_pendcomments smallint(4) default 0 not null,
4321 file_ipaddress varchar(46) default '0' not null,
4322 file_votes text null,
4323 file_rating decimal(10, 1) default 0.0 not null,
4324 file_new tinyint(1) default 0 not null,
4325 file_name_furl varchar(255) null,
4326 file_topicseoname varchar(255) null,
4327 file_post_key varchar(32) null,
4328 file_cost text null,
4329 file_nexus text null,
4330 file_version varchar(32) null,
4331 file_changelog text null,
4332 file_renewal_term int(5) default 0 not null,
4333 file_renewal_units char null,
4334 file_renewal_price text null,
4335 file_featured tinyint(1) default 0 not null,
4336 file_pinned tinyint(1) default 0 not null,
4337 file_comments int default 0 not null,
4338 file_primary_screenshot int null comment 'The record_id of the primary screenshot',
4339 file_reviews int unsigned default 0 not null,
4340 file_locked tinyint(1) unsigned default 0 not null comment 'If the file is locked (no comments/reviews allowed)',
4341 file_last_comment int unsigned null comment 'Timestamp the last comment was made',
4342 file_last_review int unsigned null comment 'Unix timestamp of last review',
4343 file_unapproved_comments int unsigned null,
4344 file_hidden_comments int unsigned null,
4345 file_unapproved_reviews int unsigned null,
4346 file_hidden_reviews int unsigned null,
4347 file_meta_data bit default b'0' not null,
4348 file_purchasable bit default b'1' not null
4349)
4350 collate = utf8mb4_unicode_ci;
4351
4352create index file_approver
4353 on invdownloads_files (file_approver);
4354
4355create index file_broken
4356 on invdownloads_files (file_broken);
4357
4358create index file_featured
4359 on invdownloads_files (file_featured);
4360
4361create index file_post_key
4362 on invdownloads_files (file_post_key);
4363
4364create index file_rating
4365 on invdownloads_files (file_rating);
4366
4367create index file_submitted
4368 on invdownloads_files (file_submitted);
4369
4370create index file_submitter
4371 on invdownloads_files (file_submitter, file_open, file_updated);
4372
4373create index file_views
4374 on invdownloads_files (file_views);
4375
4376create index ip_lookup
4377 on invdownloads_files (file_ipaddress);
4378
4379create table invdownloads_files_records
4380(
4381 record_id int auto_increment
4382 primary key,
4383 record_post_key varchar(32) null,
4384 record_file_id int default 0 not null,
4385 record_type varchar(32) default 'file' not null,
4386 record_location text null,
4387 record_thumb text null,
4388 record_realname varchar(255) null,
4389 record_link_type varchar(255) null,
4390 record_size bigint default 0 not null,
4391 record_backup tinyint(1) default 0 not null,
4392 record_default tinyint(1) default 0 not null,
4393 record_time int unsigned default 0 not null comment 'Unix timestamp of when file was uploaded',
4394 record_no_watermark text null comment 'If the record is for a screenshot, the URL to the un-watermarked version'
4395)
4396 collate = utf8mb4_unicode_ci;
4397
4398create index record_file_id
4399 on invdownloads_files_records (record_file_id);
4400
4401create index record_post_key
4402 on invdownloads_files_records (record_post_key);
4403
4404create index record_realname
4405 on invdownloads_files_records (record_realname);
4406
4407create index record_type
4408 on invdownloads_files_records (record_type, record_file_id, record_backup);
4409
4410create table invdownloads_reviews
4411(
4412 review_id bigint unsigned auto_increment comment 'ID Number'
4413 primary key,
4414 review_fid int unsigned default 0 not null comment 'The file ID',
4415 review_mid bigint unsigned default 0 not null comment 'The member ID of the review author',
4416 review_text mediumtext null comment 'The review contents',
4417 review_append_edit tinyint(1) unsigned default 0 not null comment 'Boolean indicating if edit message should show',
4418 review_edit_time int unsigned null comment 'Unix timestamp of when the review was last edited',
4419 review_edit_name varchar(255) null comment 'Username of user who last edited review',
4420 review_date int(10) null comment 'Unix timestamp of when review was made',
4421 review_ip varchar(46) default '' not null comment 'IP address review was made from',
4422 review_author_name varchar(255) default '' not null comment 'Username of user who made review',
4423 review_rating tinyint(1) unsigned null comment 'The rating (out of 5) with the review',
4424 review_votes int unsigned default 0 not null comment 'The number of people who have voted "helpful" or "unhelpful"',
4425 review_votes_helpful int unsigned default 0 not null comment 'The number of people who have voted "helpful"',
4426 review_votes_data text null comment 'JSON object containing data about who has voted helpful/unhelpful ',
4427 review_version varchar(32) null comment 'The version being reviewed',
4428 review_approved tinyint(1) default 1 not null comment 'Review is approved?',
4429 review_author_response mediumtext null
4430)
4431 collate = utf8mb4_unicode_ci;
4432
4433create index review_approved
4434 on invdownloads_reviews (review_approved, review_date);
4435
4436create index review_fid
4437 on invdownloads_reviews (review_fid);
4438
4439create index review_ip
4440 on invdownloads_reviews (review_ip);
4441
4442create index review_mid
4443 on invdownloads_reviews (review_mid, review_date);
4444
4445create table invdownloads_sessions
4446(
4447 dsess_id varchar(32) default '' not null
4448 primary key,
4449 dsess_mid int(10) default 0 not null,
4450 dsess_ip varchar(46) null,
4451 dsess_file int(10) default 0 not null,
4452 dsess_start int(10) default 0 not null,
4453 dsess_end int(10) default 0 not null
4454)
4455 collate = utf8mb4_unicode_ci;
4456
4457create index dsess_mid
4458 on invdownloads_sessions (dsess_mid, dsess_ip);
4459
4460create index dsess_start
4461 on invdownloads_sessions (dsess_start);
4462
4463create table invforums_answer_ratings
4464(
4465 id bigint unsigned auto_increment comment 'ID Number'
4466 primary key,
4467 post int unsigned default 0 not null comment 'The post ID',
4468 member bigint unsigned default 0 not null comment 'The member giving the rating',
4469 rating tinyint(1) default 0 not null comment 'The rating; 1 for positive or -1 for negative',
4470 date int unsigned default 0 not null comment 'Unix timestamp of when the rating was given',
4471 topic int unsigned null comment 'The topic ID',
4472 constraint member_rating
4473 unique (member, post)
4474)
4475 collate = utf8mb4_unicode_ci;
4476
4477create index member_topic_rating
4478 on invforums_answer_ratings (topic, member);
4479
4480create index post
4481 on invforums_answer_ratings (post);
4482
4483create table invforums_archive_posts
4484(
4485 archive_id int(10) default 0 not null
4486 primary key,
4487 archive_author_id bigint default 0 not null,
4488 archive_author_name varchar(255) null,
4489 archive_ip_address varchar(46) default '' not null,
4490 archive_content_date int(10) default 0 not null,
4491 archive_content mediumtext null,
4492 archive_queued int(1) default 1 not null,
4493 archive_topic_id int(10) default 0 not null,
4494 archive_is_first int(1) default 0 not null,
4495 archive_bwoptions int unsigned default 0 not null,
4496 archive_attach_key char(32) default '' not null,
4497 archive_html_mode int(1) default 0 not null,
4498 archive_show_edited_by int(1) default 0 not null,
4499 archive_edit_time int(10) default 0 not null,
4500 archive_edit_name varchar(255) default '' not null,
4501 archive_edit_reason varchar(255) default '' not null,
4502 archive_added int(10) default 0 not null,
4503 archive_restored int(1) default 0 not null,
4504 archive_forum_id int(10) default 0 not null,
4505 archive_field_int int(10) null
4506)
4507 collate = utf8mb4_unicode_ci;
4508
4509create index archive_author_id
4510 on invforums_archive_posts (archive_author_id);
4511
4512create index archive_content_date
4513 on invforums_archive_posts (archive_content_date, archive_topic_id);
4514
4515create index archive_forum_id
4516 on invforums_archive_posts (archive_forum_id);
4517
4518create index archive_ip_address
4519 on invforums_archive_posts (archive_ip_address);
4520
4521create index archive_queued
4522 on invforums_archive_posts (archive_queued, archive_content_date);
4523
4524create index archive_restored
4525 on invforums_archive_posts (archive_restored);
4526
4527create index archive_topic_id
4528 on invforums_archive_posts (archive_topic_id, archive_queued, archive_content_date);
4529
4530create table invforums_archive_rules
4531(
4532 archive_key char(32) default '' not null
4533 primary key,
4534 archive_app varchar(32) default 'core' not null,
4535 archive_field varchar(255) default '' not null,
4536 archive_value varchar(255) default '' not null,
4537 archive_text text not null,
4538 archive_unit varchar(255) default '' not null,
4539 archive_skip int(1) default 0 not null
4540)
4541 collate = utf8mb4_unicode_ci;
4542
4543create table invforums_forums
4544(
4545 id smallint(5) auto_increment
4546 primary key,
4547 topics int unsigned default 0 not null,
4548 posts int unsigned default 0 not null,
4549 last_post int(10) null,
4550 last_poster_id bigint unsigned default 0 not null,
4551 last_poster_name varchar(255) default '' not null,
4552 position int(5) unsigned default 0 null,
4553 password varchar(32) null,
4554 password_override varchar(255) null,
4555 last_title varchar(255) null,
4556 last_id int(10) null,
4557 sort_key varchar(32) null,
4558 show_rules tinyint(1) null,
4559 preview_posts tinyint(1) null,
4560 allow_poll tinyint(1) default 1 not null,
4561 inc_postcount tinyint(1) default 1 not null,
4562 skin_id int(10) null,
4563 parent_id mediumint(5) default -1 null,
4564 redirect_url varchar(250) default '' null,
4565 redirect_on tinyint(1) default 0 not null,
4566 redirect_hits int(10) default 0 not null,
4567 sub_can_post tinyint(1) default 1 null,
4568 permission_showtopic tinyint(1) default 0 not null,
4569 queued_topics int(10) default 0 not null,
4570 queued_posts int(10) default 0 not null,
4571 forum_allow_rating tinyint(1) default 0 not null,
4572 min_posts_post int unsigned default 0 not null,
4573 min_posts_view int unsigned default 0 not null,
4574 can_view_others tinyint(1) default 1 not null,
4575 name_seo varchar(255) null,
4576 seo_last_title varchar(255) default '' not null,
4577 seo_last_name varchar(255) default '' not null,
4578 last_x_topic_ids text null,
4579 forums_bitoptions int unsigned default 0 not null,
4580 disable_sharelinks int(1) default 0 not null,
4581 tag_predefined text null,
4582 archived_topics int(10) default 0 not null,
4583 archived_posts int(10) default 0 not null,
4584 ipseo_priority varchar(3) default '' null,
4585 viglink tinyint(1) default 1 not null,
4586 qa_rate_questions text null,
4587 qa_rate_answers text null,
4588 icon text null,
4589 club_id bigint unsigned null comment 'The club ID if this forum belongs to a club, or NULL',
4590 feature_color varchar(15) null comment 'Feature color'
4591)
4592 collate = utf8mb4_unicode_ci;
4593
4594create index can_view_others
4595 on invforums_forums (can_view_others);
4596
4597create index forum_password
4598 on invforums_forums (password);
4599
4600create index min_posts_view
4601 on invforums_forums (min_posts_view);
4602
4603create index position
4604 on invforums_forums (parent_id, position);
4605
4606create index widget_performance
4607 on invforums_forums (min_posts_view, password, password_override, can_view_others);
4608
4609create table invforums_posts
4610(
4611 pid int(10) auto_increment
4612 primary key,
4613 append_edit tinyint(1) default 0 null,
4614 edit_time int(10) null,
4615 author_id bigint unsigned default 0 not null,
4616 author_name varchar(255) null,
4617 ip_address varchar(46) default '' not null,
4618 post_date int(10) null,
4619 post mediumtext null,
4620 queued tinyint(1) default 0 not null,
4621 topic_id int(10) default 0 not null,
4622 new_topic tinyint(1) default 0 null,
4623 edit_name varchar(255) null,
4624 post_key varchar(32) default '0' not null,
4625 post_htmlstate smallint(1) default 0 not null,
4626 post_edit_reason varchar(255) default '' not null,
4627 post_bwoptions int unsigned default 0 not null,
4628 pdelete_time int default 0 not null,
4629 post_field_int int(10) default 0 null,
4630 post_field_t1 text null,
4631 post_field_t2 text null
4632)
4633 collate = utf8mb4_unicode_ci;
4634
4635create index author_id
4636 on invforums_posts (author_id, post_date, queued);
4637
4638create index first_post
4639 on invforums_posts (topic_id, post_date);
4640
4641create index ip_address
4642 on invforums_posts (ip_address);
4643
4644create index post_date
4645 on invforums_posts (post_date);
4646
4647create index post_key
4648 on invforums_posts (post_key);
4649
4650create index queued
4651 on invforums_posts (queued, post_date);
4652
4653create index topic_id
4654 on invforums_posts (topic_id, queued, post_date, author_id);
4655
4656create table invforums_question_ratings
4657(
4658 id bigint unsigned auto_increment comment 'ID Number'
4659 primary key,
4660 topic int unsigned not null comment 'The topic ID',
4661 forum smallint unsigned not null comment 'The forum ID',
4662 member bigint unsigned null comment 'The member giving the rating',
4663 rating tinyint(1) null comment 'The rating; 1 for positive or -1 for negative',
4664 date int unsigned not null comment 'Unix timestamp of when the rating was given',
4665 constraint member_rating
4666 unique (member, topic)
4667)
4668 collate = utf8mb4_unicode_ci;
4669
4670create index forum
4671 on invforums_question_ratings (forum);
4672
4673create index topic
4674 on invforums_question_ratings (topic, member);
4675
4676create table invforums_rss_import
4677(
4678 rss_import_id int(10) auto_increment
4679 primary key,
4680 rss_import_enabled tinyint(1) default 0 not null,
4681 rss_import_title varchar(255) default '' not null,
4682 rss_import_url varchar(255) default '' not null,
4683 rss_import_forum_id int(10) default 0 not null,
4684 rss_import_mid bigint unsigned default 0 not null,
4685 rss_import_pergo smallint(3) default 0 not null,
4686 rss_import_time smallint(3) default 0 not null,
4687 rss_import_last_import int(10) default 0 not null,
4688 rss_import_showlink varchar(255) default '0' not null,
4689 rss_import_topic_open tinyint(1) default 0 not null,
4690 rss_import_topic_hide tinyint(1) default 0 not null,
4691 rss_import_topic_pre varchar(50) default '' not null,
4692 rss_import_allow_html tinyint(1) default 0 not null,
4693 rss_import_auth tinyint(1) default 0 not null,
4694 rss_import_auth_user varchar(255) default 'Not Needed' null,
4695 rss_import_auth_pass varchar(255) default 'Not Needed' null,
4696 rss_import_auto_follow tinyint default 0 not null
4697)
4698 collate = utf8mb4_unicode_ci;
4699
4700create index member
4701 on invforums_rss_import (rss_import_mid);
4702
4703create index rss_grab
4704 on invforums_rss_import (rss_import_enabled, rss_import_last_import);
4705
4706create table invforums_rss_imported
4707(
4708 rss_imported_guid char(32) default '0' not null
4709 primary key,
4710 rss_imported_tid int(10) default 0 not null,
4711 rss_imported_impid int(10) default 0 not null
4712)
4713 collate = utf8mb4_unicode_ci;
4714
4715create index rss_imported_impid
4716 on invforums_rss_imported (rss_imported_impid);
4717
4718create table invforums_topic_mmod
4719(
4720 mm_id smallint(5) auto_increment
4721 primary key,
4722 mm_enabled tinyint(1) default 0 not null,
4723 topic_state varchar(10) default 'leave' not null,
4724 topic_pin varchar(10) default 'leave' not null,
4725 topic_move smallint(5) default 0 not null,
4726 topic_move_link tinyint(1) default 0 not null,
4727 topic_title_st varchar(250) default '' not null,
4728 topic_title_end varchar(250) default '' not null,
4729 topic_reply tinyint(1) default 0 not null,
4730 topic_reply_content text null,
4731 topic_reply_postcount tinyint(1) default 0 not null,
4732 mm_forums text null,
4733 topic_approve tinyint(1) default 0 not null
4734)
4735 collate = utf8mb4_unicode_ci;
4736
4737create table invforums_topics
4738(
4739 tid int(10) auto_increment
4740 primary key,
4741 title varchar(255) default '' not null,
4742 state varchar(8) null,
4743 posts int(10) null,
4744 starter_id bigint unsigned default 0 not null,
4745 start_date int(10) null,
4746 last_poster_id bigint unsigned default 0 not null,
4747 last_post int(10) default 0 not null,
4748 starter_name varchar(255) null,
4749 last_poster_name varchar(255) null,
4750 poll_state varchar(8) null,
4751 last_vote int(10) null,
4752 views int unsigned default 0 null,
4753 forum_id smallint(5) default 0 not null,
4754 approved tinyint(1) default 0 not null,
4755 author_mode tinyint(1) null,
4756 pinned tinyint(1) null,
4757 moved_to varchar(64) null,
4758 topic_firstpost int(10) default 0 not null,
4759 topic_queuedposts int(10) default 0 not null,
4760 topic_open_time int(10) default 0 not null,
4761 topic_close_time int(10) default 0 not null,
4762 topic_rating_total smallint unsigned default 0 not null,
4763 topic_rating_hits smallint unsigned default 0 not null,
4764 title_seo varchar(255) default '' not null,
4765 moved_on int default 0 not null,
4766 topic_archive_status int(1) default 0 not null,
4767 last_real_post int(10) default 0 not null,
4768 topic_answered_pid int(10) default 0 not null,
4769 popular_time int(10) null comment 'Timestamp of when this topic will stop being popular.',
4770 featured tinyint(1) unsigned default 0 null comment 'Topic is featured?',
4771 question_rating int(10) null comment 'If this topic is a question, it''s current rating',
4772 topic_hiddenposts int unsigned null,
4773 topic_meta_data bit default b'0' not null
4774)
4775 collate = utf8mb4_unicode_ci;
4776
4777create index approved
4778 on invforums_topics (approved);
4779
4780create index featured_topics
4781 on invforums_topics (featured, start_date);
4782
4783create index forum_id
4784 on invforums_topics (forum_id, pinned, approved);
4785
4786create index last_post
4787 on invforums_topics (forum_id, pinned, last_real_post, state);
4788
4789create index last_true_post
4790 on invforums_topics (forum_id, pinned, last_post);
4791
4792create index last_x_topics
4793 on invforums_topics (forum_id, approved, start_date);
4794
4795create index most_recent_post
4796 on invforums_topics (forum_id, approved, last_post);
4797
4798create index moved_redirects
4799 on invforums_topics (moved_on, moved_to, pinned);
4800
4801create index popular_topics
4802 on invforums_topics (popular_time, approved, forum_id, start_date);
4803
4804create index start_date
4805 on invforums_topics (start_date);
4806
4807create index starter_id
4808 on invforums_topics (starter_id, forum_id, approved, start_date);
4809
4810create index topic_archive_status
4811 on invforums_topics (topic_archive_status, forum_id);
4812
4813create index topic_firstpost
4814 on invforums_topics (topic_firstpost);
4815
4816create table invforums_view_method
4817(
4818 member_id bigint unsigned default 0 not null
4819 primary key,
4820 method varchar(10) default 'table' not null
4821)
4822 collate = utf8mb4_unicode_ci;
4823
4824create table invmember_socials
4825(
4826 member_id varchar(255) null
4827)
4828 collate = utf8mb4_unicode_ci;
4829
4830create table invreg_codes
4831(
4832 reg_id bigint unsigned auto_increment comment 'ID Number'
4833 primary key,
4834 reg_member_id int(20) null,
4835 reg_code varchar(255) null,
4836 reg_code_status smallint(2) default 0 null,
4837 reg_expiry text null,
4838 reg_is_unlim tinyint(1) default 0 null,
4839 reg_total_uses smallint(10) null,
4840 reg_uses smallint(10) null,
4841 reg_expiry_timestamp bigint null,
4842 reg_name varchar(50) null
4843)
4844 collate = utf8mb4_unicode_ci;
4845
4846create table invreg_codes_log
4847(
4848 reg_log_id bigint unsigned auto_increment comment 'ID Number'
4849 primary key,
4850 reg_log_member_id int(20) null,
4851 reg_log_code varchar(255) null,
4852 reg_log_timestamp varchar(255) null
4853)
4854 collate = utf8mb4_unicode_ci;
4855
4856create table invsocialsystem_about
4857(
4858 id bigint unsigned auto_increment comment 'ID Number'
4859 primary key,
4860 member_id int(20) null,
4861 shortbio text null comment 'About Me text',
4862 aboutme text null comment 'About Me text',
4863 country varchar(255) null,
4864 fav_song text null comment 'Spotify song link'
4865)
4866 collate = utf8mb4_unicode_ci;
4867
4868create table invsocialsystem_game_follows
4869(
4870 id bigint unsigned auto_increment comment 'ID Number'
4871 primary key,
4872 game_id bigint(10) default 0 null,
4873 member_id int(20) null,
4874 pinned varchar(2) null
4875)
4876 collate = utf8mb4_unicode_ci;
4877
4878create table invsocialsystem_games
4879(
4880 id bigint unsigned auto_increment comment 'ID Number'
4881 primary key,
4882 name varchar(255) not null,
4883 website varchar(255) null,
4884 publisher varchar(255) null,
4885 release_date varchar(255) null,
4886 platform text null,
4887 description text null comment 'Description text',
4888 logo varchar(255) null,
4889 cover_photo varchar(255) null,
4890 cover_photo_offset varchar(255) null,
4891 developer varchar(255) null,
4892 icon varchar(255) null,
4893 twitter varchar(255) null,
4894 members int(10) default 0 not null,
4895 updated_by int(10) default 0 null,
4896 requested_by int(10) default 0 null,
4897 status smallint(1) default 1 null
4898)
4899 collate = utf8mb4_unicode_ci;
4900
4901create table invsocialsystem_member_links
4902(
4903 id bigint unsigned auto_increment comment 'ID Number'
4904 primary key,
4905 member_id bigint null,
4906 social_id varchar(255) null,
4907 social_input varchar(255) null,
4908 favourite varchar(2) null
4909)
4910 collate = utf8mb4_unicode_ci;
4911
4912create table invsocialsystem_options
4913(
4914 id bigint(10) unsigned auto_increment comment 'ID Number'
4915 primary key,
4916 name varchar(255) not null,
4917 weight int(10) default 0 not null,
4918 logo varchar(255) null,
4919 parent_id bigint(10) default 0 null,
4920 parent_name varchar(255) not null,
4921 club_use tinyint(1) default 0 not null,
4922 logo_club varchar(255) null,
4923 gamesystem_use tinyint(1) default 1 not null
4924)
4925 collate = utf8mb4_unicode_ci;
4926
4927create table invsocialsystem_socials
4928(
4929 id bigint unsigned auto_increment comment 'ID Number'
4930 primary key,
4931 name varchar(255) null,
4932 image varchar(255) null,
4933 link varchar(255) null,
4934 link_type varchar(255) null,
4935 icon_type varchar(255) null,
4936 class varchar(255) null,
4937 placeholder varchar(255) null,
4938 description varchar(255) null,
4939 category varchar(255) null,
4940 weight int(10) default 0 not null,
4941 icon_hex varchar(255) null,
4942 gamesystem_use tinyint(1) default 1 not null,
4943 club_use tinyint(1) default 0 not null
4944)
4945 collate = utf8mb4_unicode_ci;
4946
4947create table invsponsors
4948(
4949 id bigint unsigned auto_increment comment 'ID Number'
4950 primary key,
4951 name varchar(255) null,
4952 image varchar(255) null,
4953 link varchar(255) null,
4954 weight int(10) default 0 not null
4955)
4956 collate = utf8mb4_unicode_ci;
4957
4958create table invtwitch_users
4959(
4960 id bigint unsigned auto_increment comment 'ID Number'
4961 primary key,
4962 member_id int(20) null,
4963 user_id bigint(100) default 0 null,
4964 display_name varchar(255) null,
4965 profile_image_url varchar(255) null,
4966 offline_image_url varchar(255) null,
4967 description text null,
4968 status bigint(2) default 0 null comment 'Indicates consent to use stream data',
4969 last_live int unsigned default 0 null comment 'Unix timestamp of upload date',
4970 view_count bigint(100) default 0 null,
4971 featured int(2) default 0 null,
4972 admin_disabled int(2) default 0 null,
4973 live_thumbnail varchar(255) null,
4974 live_title varchar(255) null,
4975 live_game_id varchar(255) null,
4976 live_game_name varchar(255) null,
4977 live_game_art varchar(255) null,
4978 profile_default bigint(2) default 0 null comment 'Indicates consent to autoplay on profile',
4979 type varchar(255) default 'twitch' null
4980)
4981 collate = utf8mb4_unicode_ci;
4982
4983create table invyoutube_users
4984(
4985 yt_id bigint unsigned auto_increment comment 'ID Number'
4986 primary key,
4987 yt_member_id int(20) null,
4988 yt_channel_id varchar(255) default '0' null,
4989 yt_channel_name varchar(255) null,
4990 yt_channel_cover varchar(255) null,
4991 yt_channel_avatar varchar(255) null,
4992 yt_featured int(2) default 0 null,
4993 yt_admin_disabled int(2) default 0 null,
4994 yt_status int(2) default 1 null,
4995 yt_upload_id varchar(255) default '1' null,
4996 yt_videos text null comment 'Video JSON',
4997 constraint yt_channel_id
4998 unique (yt_channel_id),
4999 constraint yt_member_id
5000 unique (yt_member_id)
5001)
5002 collate = utf8mb4_unicode_ci;