· 7 years ago · Feb 04, 2019, 08:00 PM
1SET FOREIGN_KEY_CHECKS=0;
2DROP TABLE IF EXISTS `chv_albums`;
3CREATE TABLE `chv_albums` (
4 `album_id` bigint(32) NOT NULL AUTO_INCREMENT,
5 `album_name` varchar(100) NOT NULL,
6 `album_user_id` bigint(32) DEFAULT NULL,
7 `album_date` datetime NOT NULL,
8 `album_date_gmt` datetime NOT NULL,
9 `album_creation_ip` varchar(255) NOT NULL,
10 `album_privacy` enum('public','password','private','private_but_link','custom') DEFAULT 'public',
11 `album_privacy_extra` text,
12 `album_password` text,
13 `album_image_count` bigint(32) NOT NULL DEFAULT '0',
14 `album_description` text,
15 `album_likes` bigint(32) NOT NULL DEFAULT '0',
16 `album_views` bigint(32) NOT NULL DEFAULT '0',
17 PRIMARY KEY (`album_id`),
18 KEY `album_name` (`album_name`),
19 KEY `album_user_id` (`album_user_id`),
20 KEY `album_date_gmt` (`album_date_gmt`),
21 KEY `album_privacy` (`album_privacy`),
22 KEY `album_image_count` (`album_image_count`),
23 KEY `album_creation_ip` (`album_creation_ip`),
24 FULLTEXT KEY `searchindex` (`album_name`,`album_description`)
25) ENGINE=InnoDB DEFAULT CHARSET=utf8;
26
27DROP TABLE IF EXISTS `chv_categories`;
28CREATE TABLE `chv_categories` (
29 `category_id` bigint(32) NOT NULL AUTO_INCREMENT,
30 `category_name` varchar(32) NOT NULL,
31 `category_url_key` varchar(32) NOT NULL,
32 `category_description` text,
33 PRIMARY KEY (`category_id`),
34 UNIQUE KEY `url_key` (`category_url_key`) USING BTREE
35) ENGINE=InnoDB DEFAULT CHARSET=utf8;
36
37DROP TABLE IF EXISTS `chv_confirmations`;
38CREATE TABLE `chv_confirmations` (
39 `confirmation_id` bigint(32) NOT NULL AUTO_INCREMENT,
40 `confirmation_user_id` bigint(32) NOT NULL,
41 `confirmation_type` enum('account-activate','account-change-email','account-password-forgot') NOT NULL,
42 `confirmation_date` datetime NOT NULL,
43 `confirmation_date_gmt` datetime NOT NULL,
44 `confirmation_token_hash` varchar(255) NOT NULL,
45 `confirmation_status` enum('active','valid','invalid') NOT NULL,
46 `confirmation_extra` text,
47 PRIMARY KEY (`confirmation_id`)
48) ENGINE=InnoDB DEFAULT CHARSET=utf8;
49
50DROP TABLE IF EXISTS `chv_deletions`;
51CREATE TABLE `chv_deletions` (
52 `deleted_id` bigint(32) NOT NULL AUTO_INCREMENT,
53 `deleted_date_gmt` datetime NOT NULL,
54 `deleted_content_id` bigint(32) NOT NULL,
55 `deleted_content_date_gmt` datetime NOT NULL,
56 `deleted_content_user_id` bigint(32) DEFAULT NULL,
57 `deleted_content_ip` varchar(255) NOT NULL,
58 `deleted_content_md5` varchar(32) DEFAULT NULL,
59 `deleted_content_original_filename` varchar(255) DEFAULT NULL,
60 `deleted_content_views` bigint(32) NOT NULL DEFAULT '0',
61 `deleted_content_likes` bigint(32) NOT NULL DEFAULT '0',
62 PRIMARY KEY (`deleted_id`),
63 KEY `deleted_content_id` (`deleted_content_id`),
64 KEY `deleted_content_user_id` (`deleted_content_user_id`),
65 KEY `deleted_content_ip` (`deleted_content_ip`),
66 KEY `deleted_content_md5` (`deleted_content_md5`),
67 KEY `deleted_content_views` (`deleted_content_views`),
68 KEY `deleted_content_likes` (`deleted_content_likes`)
69) ENGINE=InnoDB DEFAULT CHARSET=utf8;
70
71
72DROP TABLE IF EXISTS `chv_follows`;
73CREATE TABLE `chv_follows` (
74 `follow_id` bigint(32) NOT NULL AUTO_INCREMENT,
75 `follow_date` datetime NOT NULL,
76 `follow_date_gmt` datetime NOT NULL,
77 `follow_user_id` bigint(32) NOT NULL,
78 `follow_followed_user_id` bigint(32) NOT NULL,
79 `follow_ip` varchar(255) NOT NULL,
80 PRIMARY KEY (`follow_id`),
81 KEY `follow_user_id` (`follow_user_id`),
82 KEY `follow_followed_user_id` (`follow_followed_user_id`)
83) ENGINE=InnoDB DEFAULT CHARSET=utf8;
84
85DROP TABLE IF EXISTS `chv_images`;
86CREATE TABLE `chv_images` (
87 `image_id` bigint(32) NOT NULL AUTO_INCREMENT,
88 `image_name` varchar(255) NOT NULL,
89 `image_extension` varchar(255) NOT NULL,
90 `image_size` int(11) NOT NULL,
91 `image_width` int(11) NOT NULL,
92 `image_height` int(11) NOT NULL,
93 `image_date` datetime NOT NULL,
94 `image_date_gmt` datetime NOT NULL,
95 `image_title` varchar(100) DEFAULT NULL,
96 `image_description` text,
97 `image_nsfw` tinyint(1) NOT NULL DEFAULT '0',
98 `image_user_id` bigint(32) DEFAULT NULL,
99 `image_album_id` bigint(32) DEFAULT NULL,
100 `image_uploader_ip` varchar(255) NOT NULL,
101 `image_storage_mode` enum('datefolder','direct','old','path') NOT NULL DEFAULT 'datefolder',
102 `image_path` varchar(4096) DEFAULT NULL,
103 `image_storage_id` bigint(32) DEFAULT NULL,
104 `image_md5` varchar(32) NOT NULL,
105 `image_source_md5` varchar(32) DEFAULT NULL,
106 `image_original_filename` varchar(255) NOT NULL,
107 `image_original_exifdata` longtext,
108 `image_views` bigint(32) NOT NULL DEFAULT '0',
109 `image_category_id` bigint(32) DEFAULT NULL,
110 `image_chain` tinyint(128) NOT NULL,
111 `image_thumb_size` int(11) NOT NULL,
112 `image_medium_size` int(11) NOT NULL DEFAULT '0',
113 `image_expiration_date_gmt` datetime DEFAULT NULL,
114 `image_likes` bigint(32) NOT NULL DEFAULT '0',
115 `image_is_animated` tinyint(1) NOT NULL DEFAULT '0',
116 PRIMARY KEY (`image_id`),
117 KEY `image_name` (`image_name`),
118 KEY `image_extension` (`image_extension`),
119 KEY `image_size` (`image_size`),
120 KEY `image_width` (`image_width`),
121 KEY `image_height` (`image_height`),
122 KEY `image_date_gmt` (`image_date_gmt`),
123 KEY `image_nsfw` (`image_nsfw`),
124 KEY `image_user_id` (`image_user_id`),
125 KEY `image_album_id` (`image_album_id`),
126 KEY `image_uploader_ip` (`image_uploader_ip`),
127 KEY `image_storage_mode` (`image_storage_mode`),
128 KEY `image_path` (`image_path`(255)),
129 KEY `image_storage_id` (`image_storage_id`),
130 KEY `image_md5` (`image_md5`),
131 KEY `image_source_md5` (`image_source_md5`),
132 KEY `image_views` (`image_views`),
133 KEY `image_category_id` (`image_category_id`),
134 KEY `image_chain` (`image_chain`),
135 KEY `image_expiration_date_gmt` (`image_expiration_date_gmt`),
136 KEY `image_likes` (`image_likes`),
137 KEY `image_is_animated` (`image_is_animated`),
138 FULLTEXT KEY `searchindex` (`image_name`,`image_title`,`image_description`,`image_original_filename`)
139) ENGINE=InnoDB DEFAULT CHARSET=utf8;
140
141DROP TABLE IF EXISTS `chv_importing`;
142CREATE TABLE `chv_importing` (
143 `importing_id` bigint(32) NOT NULL AUTO_INCREMENT,
144 `importing_import_id` bigint(32) NOT NULL,
145 `importing_path` varchar(4096) NOT NULL,
146 `importing_content_type` enum('user','album','image') NOT NULL,
147 `importing_content_id` bigint(32) NOT NULL DEFAULT '0',
148 PRIMARY KEY (`importing_id`),
149 UNIQUE KEY `importing_path` (`importing_path`(255))
150) ENGINE=InnoDB DEFAULT CHARSET=utf8;
151
152
153DROP TABLE IF EXISTS `chv_imports`;
154CREATE TABLE `chv_imports` (
155 `import_id` bigint(32) NOT NULL AUTO_INCREMENT,
156 `import_path` varchar(4096) NOT NULL,
157 `import_options` varchar(255) DEFAULT NULL,
158 `import_status` enum('queued','working','paused','canceled','completed') NOT NULL,
159 `import_users` bigint(32) NOT NULL DEFAULT '0',
160 `import_images` bigint(32) NOT NULL DEFAULT '0',
161 `import_albums` bigint(32) NOT NULL DEFAULT '0',
162 `import_time_created` datetime DEFAULT NULL,
163 `import_time_updated` datetime DEFAULT NULL,
164 `import_errors` tinyint(1) NOT NULL DEFAULT '0',
165 `import_started` tinyint(1) NOT NULL DEFAULT '0',
166 PRIMARY KEY (`import_id`),
167 KEY `import_path` (`import_path`(255)) USING BTREE
168) ENGINE=InnoDB DEFAULT CHARSET=utf8;
169
170
171DROP TABLE IF EXISTS `chv_ip_bans`;
172CREATE TABLE `chv_ip_bans` (
173 `ip_ban_id` bigint(20) NOT NULL AUTO_INCREMENT,
174 `ip_ban_date` datetime NOT NULL,
175 `ip_ban_date_gmt` datetime NOT NULL,
176 `ip_ban_expires` datetime DEFAULT NULL,
177 `ip_ban_expires_gmt` datetime DEFAULT NULL,
178 `ip_ban_ip` varchar(255) NOT NULL,
179 `ip_ban_message` longtext,
180 PRIMARY KEY (`ip_ban_id`),
181 KEY `ip_ban_date_gmt` (`ip_ban_date_gmt`),
182 UNIQUE KEY `ip_ban_ip` (`ip_ban_ip`) USING BTREE
183) ENGINE=InnoDB DEFAULT CHARSET=utf8;
184
185DROP TABLE IF EXISTS `chv_likes`;
186CREATE TABLE `chv_likes` (
187 `like_id` bigint(32) NOT NULL AUTO_INCREMENT,
188 `like_date` datetime NOT NULL,
189 `like_date_gmt` datetime NOT NULL,
190 `like_user_id` bigint(32) DEFAULT NULL,
191 `like_content_type` enum('image','album') DEFAULT NULL,
192 `like_content_id` bigint(32) NOT NULL,
193 `like_content_user_id` bigint(32) DEFAULT NULL,
194 `like_ip` varchar(255) NOT NULL,
195 PRIMARY KEY (`like_id`),
196 KEY `like_date_gmt` (`like_date_gmt`),
197 KEY `like_user_id` (`like_user_id`),
198 KEY `like_content_type` (`like_content_type`),
199 KEY `like_content_id` (`like_content_id`),
200 KEY `like_content_user_id` (`like_content_user_id`),
201 KEY `like_ip` (`like_ip`)
202) ENGINE=InnoDB DEFAULT CHARSET=utf8;
203
204
205DROP TABLE IF EXISTS `chv_logins`;
206CREATE TABLE `chv_logins` (
207 `login_id` bigint(32) NOT NULL AUTO_INCREMENT,
208 `login_user_id` bigint(32) NOT NULL,
209 `login_type` enum('password','session','cookie','facebook','twitter','google','vk') NOT NULL,
210 `login_ip` varchar(255) DEFAULT NULL,
211 `login_hostname` text,
212 `login_date` datetime NOT NULL,
213 `login_date_gmt` datetime NOT NULL,
214 `login_resource_id` varchar(255) DEFAULT NULL,
215 `login_resource_name` text,
216 `login_resource_avatar` text,
217 `login_resource_url` text,
218 `login_secret` text DEFAULT NULL COMMENT 'The secret part',
219 `login_token_hash` text COMMENT 'Hashed complement to secret if needed',
220 PRIMARY KEY (`login_id`),
221 KEY `login_user_id` (`login_user_id`)
222) ENGINE=InnoDB DEFAULT CHARSET=utf8;
223
224DROP TABLE IF EXISTS `chv_notifications`;
225CREATE TABLE `chv_notifications` (
226 `notification_id` bigint(32) NOT NULL AUTO_INCREMENT,
227 `notification_date_gmt` datetime NOT NULL,
228 `notification_user_id` bigint(32) NOT NULL,
229 `notification_trigger_user_id` bigint(32) DEFAULT NULL,
230 `notification_type` enum('follow','like') NOT NULL,
231 `notification_content_type` enum('user','image','album') NOT NULL,
232 `notification_type_id` bigint(32) NOT NULL COMMENT 'type_id based on action (type) table',
233 `notification_is_read` tinyint(1) NOT NULL DEFAULT '0',
234 PRIMARY KEY (`notification_id`),
235 KEY `notification_date_gmt` (`notification_date_gmt`),
236 KEY `notification_user_id` (`notification_user_id`),
237 KEY `notification_trigger_user_id` (`notification_trigger_user_id`),
238 KEY `notification_type` (`notification_type`),
239 KEY `notification_content_type` (`notification_content_type`),
240 KEY `notification_type_id` (`notification_type_id`),
241 KEY `notification_is_read` (`notification_is_read`)
242) ENGINE=InnoDB DEFAULT CHARSET=utf8;
243
244DROP TABLE IF EXISTS `chv_pages`;
245CREATE TABLE `chv_pages` (
246 `page_id` bigint(32) NOT NULL AUTO_INCREMENT,
247 `page_url_key` varchar(32) DEFAULT NULL,
248 `page_type` enum('internal','link') NOT NULL DEFAULT 'internal',
249 `page_file_path` varchar(255) DEFAULT NULL,
250 `page_link_url` text,
251 `page_icon` varchar(255) DEFAULT NULL,
252 `page_title` varchar(255) NOT NULL,
253 `page_description` text,
254 `page_keywords` text,
255 `page_is_active` tinyint(1) NOT NULL DEFAULT '1',
256 `page_is_link_visible` tinyint(1) NOT NULL DEFAULT '1',
257 `page_attr_target` enum('_self','_blank') DEFAULT '_self',
258 `page_attr_rel` varchar(255) DEFAULT NULL,
259 `page_sort_display` int(11) DEFAULT NULL,
260 `page_internal` varchar(255) DEFAULT NULL,
261 PRIMARY KEY (`page_id`),
262 UNIQUE KEY `page_internal` (`page_internal`),
263 KEY `page_url_key` (`page_url_key`),
264 KEY `page_type` (`page_type`),
265 KEY `page_is_active` (`page_is_active`),
266 KEY `page_is_link_visible` (`page_is_link_visible`),
267 KEY `page_sort_display` (`page_sort_display`)
268) ENGINE=InnoDB DEFAULT CHARSET=utf8;
269INSERT INTO `chv_pages` VALUES ('1', 'tos', 'internal', null, null, 'icon-text', 'Terms of service', null, null, '1', '1', '_self', null, '1', 'tos');
270INSERT INTO `chv_pages` VALUES ('2', 'privacy', 'internal', null, null, 'icon-lock', 'Privacy', null, null, '1', '1', '_self', null, '2', 'privacy');
271INSERT INTO `chv_pages` VALUES ('3', 'contact', 'internal', null, null, 'icon-mail', 'Contact', null, null, '1', '1', '_self', null, '3', 'contact');
272
273DROP TABLE IF EXISTS `chv_queues`;
274CREATE TABLE `chv_queues` (
275 `queue_id` bigint(32) NOT NULL AUTO_INCREMENT,
276 `queue_type` enum('storage-delete') NOT NULL,
277 `queue_date_gmt` datetime NOT NULL,
278 `queue_args` longtext NOT NULL,
279 `queue_join` bigint(32) NOT NULL,
280 `queue_attempts` varchar(255) DEFAULT '0',
281 `queue_status` enum('pending','failed') NOT NULL DEFAULT 'pending',
282 PRIMARY KEY (`queue_id`)
283) ENGINE=InnoDB DEFAULT CHARSET=utf8;
284
285DROP TABLE IF EXISTS `chv_redirects`;
286CREATE TABLE `chv_redirects` (
287 `redirect_id` bigint(32) NOT NULL AUTO_INCREMENT,
288 `redirect_from` varchar(2083) NOT NULL,
289 `redirect_content_id` bigint(32) NOT NULL,
290 `redirect_content_type` enum('image','user','album') NOT NULL,
291 PRIMARY KEY (`redirect_id`),
292 UNIQUE KEY `redirect_from` (`redirect_from`(255)),
293 KEY `redirect_content_id` (`redirect_content_id`)
294) ENGINE=InnoDB DEFAULT CHARSET=utf8;
295
296
297DROP TABLE IF EXISTS `chv_requests`;
298CREATE TABLE `chv_requests` (
299 `request_id` bigint(32) NOT NULL AUTO_INCREMENT,
300 `request_type` enum('upload','signup','account-edit','account-password-forgot','account-password-reset','account-resend-activation','account-email-needed','account-change-email','account-activate','login', 'content-password') NOT NULL,
301 `request_user_id` bigint(32) DEFAULT NULL,
302 `request_content_id` bigint(32) DEFAULT NULL,
303 `request_ip` varchar(255) NOT NULL,
304 `request_date` datetime NOT NULL,
305 `request_date_gmt` datetime NOT NULL,
306 `request_result` enum('success','fail') NOT NULL,
307 PRIMARY KEY (`request_id`),
308 KEY `request_type` (`request_type`),
309 KEY `request_user_id` (`request_user_id`),
310 KEY `request_content_id` (`request_content_id`),
311 KEY `request_ip` (`request_ip`),
312 KEY `request_date_gmt` (`request_date_gmt`),
313 KEY `request_result` (`request_result`)
314) ENGINE=InnoDB DEFAULT CHARSET=utf8;
315
316DROP TABLE IF EXISTS `chv_settings`;
317CREATE TABLE `chv_settings` (
318 `setting_id` int(11) NOT NULL AUTO_INCREMENT,
319 `setting_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
320 `setting_value` text,
321 `setting_default` text,
322 `setting_typeset` enum('string','bool') DEFAULT 'string',
323 PRIMARY KEY (`setting_id`),
324 KEY `setting_name` (`setting_name`)
325) ENGINE=InnoDB DEFAULT CHARSET=utf8;
326
327DROP TABLE IF EXISTS `chv_stats`;
328CREATE TABLE `chv_stats` (
329 `stat_id` bigint(32) NOT NULL AUTO_INCREMENT,
330 `stat_type` enum('total','date') NOT NULL,
331 `stat_date_gmt` date DEFAULT NULL,
332 `stat_users` bigint(32) NOT NULL DEFAULT '0',
333 `stat_images` bigint(32) NOT NULL DEFAULT '0',
334 `stat_albums` bigint(32) NOT NULL DEFAULT '0',
335 `stat_image_views` bigint(32) NOT NULL DEFAULT '0',
336 `stat_album_views` bigint(32) NOT NULL DEFAULT '0',
337 `stat_image_likes` bigint(32) NOT NULL DEFAULT '0',
338 `stat_album_likes` bigint(32) NOT NULL DEFAULT '0',
339 `stat_disk_used` bigint(32) NOT NULL DEFAULT '0',
340 PRIMARY KEY (`stat_id`),
341 UNIQUE KEY `stat_date_gmt` (`stat_date_gmt`) USING BTREE,
342 KEY `stat_type` (`stat_type`)
343) ENGINE=InnoDB DEFAULT CHARSET=utf8;
344INSERT INTO `chv_stats` VALUES ('1', 'total', NULL, '0', '0', '0', '0', '0', '0', '0', '0');
345
346DROP TABLE IF EXISTS `chv_storages`;
347CREATE TABLE `chv_storages` (
348 `storage_id` bigint(32) NOT NULL AUTO_INCREMENT,
349 `storage_api_id` bigint(32) NOT NULL,
350 `storage_name` varchar(255) NOT NULL,
351 `storage_service` varchar(255) DEFAULT NULL,
352 `storage_url` varchar(255) NOT NULL,
353 `storage_bucket` varchar(255) DEFAULT NULL,
354 `storage_region` varchar(255) DEFAULT NULL,
355 `storage_server` varchar(255) DEFAULT NULL,
356 `storage_account_id` varchar(255) DEFAULT NULL,
357 `storage_account_name` varchar(255) DEFAULT NULL,
358 `storage_key` text,
359 `storage_secret` text,
360 `storage_is_https` tinyint(1) NOT NULL DEFAULT '0',
361 `storage_is_active` tinyint(1) NOT NULL DEFAULT '0',
362 `storage_capacity` bigint(32) DEFAULT NULL,
363 `storage_space_used` bigint(32) DEFAULT '0',
364 PRIMARY KEY (`storage_id`),
365 KEY `storage_api_id` (`storage_api_id`),
366 KEY `storage_is_active` (`storage_is_active`)
367) ENGINE=InnoDB DEFAULT CHARSET=utf8;
368
369DROP TABLE IF EXISTS `chv_storage_apis`;
370CREATE TABLE `chv_storage_apis` (
371 `storage_api_id` bigint(32) NOT NULL AUTO_INCREMENT,
372 `storage_api_name` varchar(255) NOT NULL,
373 `storage_api_type` varchar(255) NOT NULL,
374 PRIMARY KEY (`storage_api_id`)
375) ENGINE=InnoDB DEFAULT CHARSET=utf8;
376INSERT INTO `chv_storage_apis` VALUES ('1', 'Amazon S3', 's3');
377INSERT INTO `chv_storage_apis` VALUES ('2', 'Google Cloud', 'gcloud');
378INSERT INTO `chv_storage_apis` VALUES ('3', 'Microsoft Azure', 'azure');
379INSERT INTO `chv_storage_apis` VALUES ('4', 'Chevereto Grid', 'chvgrid');
380INSERT INTO `chv_storage_apis` VALUES ('5', 'FTP', 'ftp');
381INSERT INTO `chv_storage_apis` VALUES ('6', 'SFTP', 'sftp');
382INSERT INTO `chv_storage_apis` VALUES ('7', 'OpenStack', 'openstack');
383INSERT INTO `chv_storage_apis` VALUES ('8', 'Local', 'local');
384INSERT INTO `chv_storage_apis` VALUES ('9', 'S3 compatible', 's3compatible');
385INSERT INTO `chv_storage_apis` VALUES ('10', 'Alibaba Cloud OSS', 'oss');
386INSERT INTO `chv_storage_apis` VALUES ('11', 'Backblaze B2', 'b2');
387
388DROP TABLE IF EXISTS `chv_users`;
389CREATE TABLE `chv_users` (
390 `user_id` bigint(32) NOT NULL AUTO_INCREMENT,
391 `user_name` varchar(255) DEFAULT NULL,
392 `user_username` varchar(255) NOT NULL,
393 `user_date` datetime NOT NULL,
394 `user_date_gmt` datetime NOT NULL,
395 `user_email` varchar(255) DEFAULT NULL,
396 `user_avatar_filename` varchar(255) DEFAULT NULL,
397 `user_facebook_username` varchar(255) DEFAULT NULL,
398 `user_twitter_username` varchar(255) DEFAULT NULL,
399 `user_website` varchar(255) DEFAULT NULL,
400 `user_background_filename` varchar(255) DEFAULT NULL,
401 `user_bio` varchar(255) DEFAULT NULL,
402 `user_timezone` varchar(255) NOT NULL,
403 `user_language` varchar(255) DEFAULT NULL,
404 `user_status` enum('valid','awaiting-confirmation','awaiting-email','banned') NOT NULL,
405 `user_is_admin` tinyint(1) NOT NULL DEFAULT '0',
406 `user_is_manager` tinyint(1) NOT NULL DEFAULT '0',
407 `user_is_private` tinyint(1) NOT NULL DEFAULT '0',
408 `user_newsletter_subscribe` tinyint(1) NOT NULL DEFAULT '1',
409 `user_show_nsfw_listings` tinyint(1) NOT NULL DEFAULT '0',
410 `user_image_count` bigint(32) NOT NULL DEFAULT '0',
411 `user_album_count` bigint(32) NOT NULL DEFAULT '0',
412 `user_image_keep_exif` tinyint(1) NOT NULL DEFAULT '1',
413 `user_image_expiration` varchar(255) DEFAULT NULL,
414 `user_registration_ip` varchar(255) NOT NULL,
415 `user_likes` bigint(32) NOT NULL DEFAULT '0' COMMENT 'Likes made to content owned by this user',
416 `user_liked` bigint(32) NOT NULL DEFAULT '0' COMMENT 'Likes made by this user',
417 `user_following` bigint(32) NOT NULL DEFAULT '0',
418 `user_followers` bigint(32) NOT NULL DEFAULT '0',
419 `user_content_views` bigint(32) NOT NULL DEFAULT '0',
420 `user_notifications_unread` bigint(32) NOT NULL DEFAULT '0',
421 PRIMARY KEY (`user_id`),
422 UNIQUE KEY `username` (`user_username`) USING BTREE,
423 UNIQUE KEY `email` (`user_email`) USING BTREE,
424 KEY `user_date_gmt` (`user_date_gmt`),
425 KEY `user_status` (`user_status`),
426 KEY `user_is_admin` (`user_is_admin`),
427 KEY `user_is_manager` (`user_is_manager`),
428 KEY `user_is_private` (`user_is_private`),
429 KEY `user_newsletter_subscribe` (`user_newsletter_subscribe`),
430 KEY `user_show_nsfw_listings` (`user_show_nsfw_listings`),
431 KEY `user_image_count` (`user_image_count`),
432 KEY `user_album_count` (`user_album_count`),
433 KEY `user_image_keep_exif` (`user_image_keep_exif`),
434 KEY `user_image_expiration` (`user_image_expiration`),
435 KEY `user_registration_ip` (`user_registration_ip`),
436 KEY `user_likes` (`user_likes`),
437 KEY `user_following` (`user_following`),
438 KEY `user_followers` (`user_followers`),
439 KEY `user_liked` (`user_liked`),
440 KEY `user_content_views` (`user_content_views`),
441 FULLTEXT KEY `searchindex` (`user_name`,`user_username`)
442) ENGINE=InnoDB DEFAULT CHARSET=utf8;
443
444TRUNCATE TABLE `chv_stats`;
445
446INSERT INTO `chv_stats` (stat_id, stat_date_gmt, stat_type) VALUES ("1", NULL, "total") ON DUPLICATE KEY UPDATE stat_type=stat_type;
447
448UPDATE `chv_stats` SET
449stat_images = (SELECT IFNULL(COUNT(*),0) FROM `chv_images`),
450stat_albums = (SELECT IFNULL(COUNT(*),0) FROM `chv_albums`),
451stat_users = (SELECT IFNULL(COUNT(*),0) FROM `chv_users`),
452stat_image_views = (SELECT IFNULL(SUM(image_views),0) FROM `chv_images`),
453stat_disk_used = (SELECT IFNULL(SUM(image_size) + SUM(image_thumb_size) + SUM(image_medium_size),0) FROM `chv_images`)
454WHERE stat_type = "total";
455
456INSERT INTO `chv_stats` (stat_type, stat_date_gmt, stat_images, stat_image_views, stat_disk_used)
457SELECT sb.stat_type, sb.stat_date_gmt, sb.stat_images, sb.stat_image_views, sb.stat_disk_used
458FROM (SELECT "date" AS stat_type, DATE(image_date_gmt) AS stat_date_gmt, COUNT(*) AS stat_images, SUM(image_views) AS stat_image_views, SUM(image_size + image_thumb_size + image_medium_size) AS stat_disk_used FROM `chv_images` GROUP BY DATE(image_date_gmt)) AS sb
459ON DUPLICATE KEY UPDATE stat_images = sb.stat_images;
460
461INSERT INTO `chv_stats` (stat_type, stat_date_gmt, stat_users)
462SELECT sb.stat_type, sb.stat_date_gmt, sb.stat_users
463FROM (SELECT "date" AS stat_type, DATE(user_date_gmt) AS stat_date_gmt, COUNT(*) AS stat_users FROM `chv_users` GROUP BY DATE(user_date_gmt)) AS sb
464ON DUPLICATE KEY UPDATE stat_users = sb.stat_users;
465
466INSERT INTO `chv_stats` (stat_type, stat_date_gmt, stat_albums)
467SELECT sb.stat_type, sb.stat_date_gmt, sb.stat_albums
468FROM (SELECT "date" AS stat_type, DATE(album_date_gmt) AS stat_date_gmt, COUNT(*) AS stat_albums FROM `chv_albums` GROUP BY DATE(album_date_gmt)) AS sb
469ON DUPLICATE KEY UPDATE stat_albums = sb.stat_albums;
470
471UPDATE `chv_users` SET user_content_views = COALESCE((SELECT SUM(image_views) FROM `chv_images` WHERE image_user_id = user_id GROUP BY user_id), "0");