ls-plugin_lsgallery/activate.sql at master · deems/ls-plugin_lsgallery · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
CREATE TABLE IF NOT EXISTS `prefix_lsgallery_album` (
  `album_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `album_user_id` int(11) unsigned NOT NULL,
  `album_title` varchar(200) NOT NULL,
  `album_description` text NOT NULL,
  `album_type` enum('personal','open','friend') NOT NULL DEFAULT 'open',
  `album_date_add` datetime NOT NULL,
  `album_date_edit` datetime NOT NULL,
  `album_cover_image_id` int(11) unsigned DEFAULT NULL,
  `image_count` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`album_id`),
  KEY `album_user_id` (`album_user_id`),
  KEY `album_cover_image_id` (`album_cover_image_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `prefix_lsgallery_image` (
  `image_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `album_id` int(11) unsigned NOT NULL,
  `image_description` text NOT NULL,
  `image_tags` varchar(200) NOT NULL,
  `image_filename` varchar(255) NOT NULL,
  `image_date_add` datetime NOT NULL,
  `image_date_edit` datetime DEFAULT NULL,
  `image_count_comment` int(11) NOT NULL DEFAULT '0',
  `image_rating` float(9,3) NOT NULL DEFAULT '0.000',
  `image_count_vote` int(11) unsigned NOT NULL DEFAULT '0',
  `image_count_vote_up` INT NOT NULL DEFAULT '0',
  `image_count_vote_down` INT NOT NULL DEFAULT '0',
  `image_count_vote_abstain` INT NOT NULL DEFAULT '0',
  `image_count_favourite` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`image_id`),
  KEY `album_id` (`album_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE utf8_general_ci ;

ALTER TABLE `prefix_lsgallery_image`
  ADD CONSTRAINT `prefix_lsgallery_image_ibfk_2` FOREIGN KEY (`album_id`) REFERENCES `prefix_lsgallery_album` (`album_id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `prefix_lsgallery_album`
  ADD CONSTRAINT `prefix_lsgallery_album_ibfk_1` FOREIGN KEY (`album_user_id`) REFERENCES `prefix_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `prefix_lsgallery_album_ibfk_2` FOREIGN KEY (`album_cover_image_id`) REFERENCES `prefix_lsgallery_image` (`image_id`) ON DELETE SET NULL ON UPDATE CASCADE;

CREATE TABLE IF NOT EXISTS `prefix_lsgallery_image_tag` (
  `image_tag_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `album_id` int(11) unsigned NOT NULL,
  `image_id` int(11) unsigned NOT NULL,
  `image_tag_text` varchar(50) NOT NULL,
  PRIMARY KEY (`image_tag_id`),
  KEY `image_id` (`image_id`),
  KEY `image_tag_text` (`image_tag_text`),
  KEY `album_id` (`album_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci ;

ALTER TABLE `prefix_lsgallery_image_tag`
  ADD CONSTRAINT `prefix_lsgallery_image_tag_ibfk_2` FOREIGN KEY (`album_id`) REFERENCES `prefix_lsgallery_album` (`album_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `prefix_lsgallery_image_tag_ibfk_1` FOREIGN KEY (`image_id`) REFERENCES `prefix_lsgallery_image` (`image_id`) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE TABLE IF NOT EXISTS `prefix_lsgallery_image_read` (
  `image_id` int(11) unsigned NOT NULL,
  `user_id` int(11) unsigned NOT NULL,
  `date_read` datetime NOT NULL,
  `comment_count_last` int(10) unsigned NOT NULL DEFAULT '0',
  `comment_id_last` int(11) unsigned NOT NULL DEFAULT '0',
  KEY `image_id` (`image_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;


ALTER TABLE `prefix_lsgallery_image_read`
  ADD CONSTRAINT `prefix_lsgallery_image_read_ibfk_1` FOREIGN KEY (`image_id`) REFERENCES `prefix_lsgallery_image` (`image_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `prefix_lsgallery_image_read_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `prefix_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;