1 # Setup tables for Social Networking module
4 CREATE TABLE `social_activities` (
5 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
6 `member_id` INTEGER UNSIGNED NOT NULL,
7 `application_id` INTEGER UNSIGNED NOT NULL,
9 `created_date` TIMESTAMP NOT NULL,
14 # Applications/ Gagdets table
15 CREATE TABLE `social_applications` (
16 `id` INTEGER UNSIGNED,
17 `url` VARCHAR(255) NOT NULL DEFAULT '',
18 `title` VARCHAR(255) NOT NULL,
19 `height` INTEGER UNSIGNED,
20 `scrolling` INTEGER UNSIGNED,
21 `screenshot` VARCHAR(255) NOT NULL,
22 `thumbnail` VARCHAR(255) NOT NULL,
23 `author` VARCHAR(255) NOT NULL,
24 `author_email` VARCHAR(128) NOT NULL,
25 `description` TEXT NOT NULL,
26 `settings` TEXT NOT NULL,
27 `views` TEXT NOT NULL,
28 `last_updated` TIMESTAMP NOT NULL,
33 # Application Settings, like storing the perference string.
34 CREATE TABLE `social_application_settings` (
35 `application_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
36 `member_id` INTEGER UNSIGNED NOT NULL,
37 `name` VARCHAR(255) NOT NULL,
38 `value` TEXT NOT NULL,
39 PRIMARY KEY (`application_id`, `member_id`, `name`)
43 # Application members mapping
44 CREATE TABLE `social_members_applications` (
45 `member_id` INTEGER UNSIGNED NOT NULL,
46 `application_id` INTEGER UNSIGNED NOT NULL,
47 PRIMARY KEY (`member_id`, `application_id`)
52 CREATE TABLE `social_friends` (
53 `member_id` INTEGER UNSIGNED NOT NULL,
54 `friend_id` INTEGER UNSIGNED NOT NULL,
55 `relationship` INTEGER UNSIGNED NOT NULL,
56 PRIMARY KEY (`member_id`, `friend_id`)
60 # Friend requests table
61 CREATE TABLE `social_friend_requests` (
62 `member_id` INTEGER UNSIGNED NOT NULL,
63 `friend_id` INTEGER UNSIGNED NOT NULL,
64 `relationship` INTEGER UNSIGNED NOT NULL,
65 PRIMARY KEY (`member_id`, `friend_id`)
69 # Person Positions (jobs)
70 CREATE TABLE `social_member_position` (
71 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
72 `member_id` INTEGER UNSIGNED NOT NULL,
73 `title` VARCHAR(255) NOT NULL,
74 `company` VARCHAR(255) NOT NULL,
75 `from` VARCHAR(10) NOT NULL DEFAULT 0,
76 `to` VARCHAR(10) NOT NULL DEFAULT 0,
83 CREATE TABLE `social_member_education` (
84 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
85 `member_id` INTEGER UNSIGNED NOT NULL,
86 `university` VARCHAR(255) NOT NULL,
87 `country` VARCHAR(128),
88 `province` VARCHAR(128),
91 `from` VARCHAR(10) NOT NULL DEFAULT 0,
92 `to` VARCHAR(10) NOT NULL DEFAULT 0,
93 `description` TEXT NOT NULL,
98 # Person related web sites
99 CREATE TABLE `social_member_websites` (
100 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
101 `member_id` INTEGER UNSIGNED NOT NULL,
102 `url` VARCHAR(255) NOT NULL,
103 `site_name` VARCHAR(255),
108 # Tracks visitor counts
109 CREATE TABLE `social_member_track` (
110 `member_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
111 `visitor_id` INTEGER UNSIGNED NOT NULL,
112 `timestamp` TIMESTAMP NOT NULL,
113 PRIMARY KEY (`member_id`, `visitor_id`, `timestamp`)
117 # Person additional information cojoint with the members table
118 CREATE TABLE `social_member_additional_information` (
119 `member_id` INTEGER UNSIGNED NOT NULL,
120 `expertise` VARCHAR(255) NOT NULL,
125 PRIMARY KEY (`member_id`)
129 # Privacy Control Preferences
130 CREATE TABLE `social_privacy_preferences` (
131 `member_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
132 `preferences` TEXT NOT NULL,
133 PRIMARY KEY (`member_id`)
137 # Social Group tables
138 CREATE TABLE `social_groups` (
139 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
140 `member_id` INTEGER UNSIGNED NOT NULL,
141 `type_id` INTEGER UNSIGNED NOT NULL,
142 `privacy` INTEGER UNSIGNED NOT NULL,
143 `name` VARCHAR(255) NOT NULL,
144 `logo` VARCHAR(255) NOT NULL,
145 `description` TEXT NOT NULL,
146 `created_date` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
147 `last_updated` TIMESTAMP NOT NULL,
152 CREATE TABLE `social_groups_activities` (
153 `activity_id` INTEGER UNSIGNED NOT NULL,
154 `group_id` INTEGER UNSIGNED NOT NULL,
155 PRIMARY KEY (`activity_id`, `group_id`)
159 CREATE TABLE `social_groups_members` (
160 `group_id` INTEGER UNSIGNED NOT NULL,
161 `member_id` INTEGER UNSIGNED NOT NULL,
162 PRIMARY KEY (`group_id`, `member_id`)
166 CREATE TABLE `social_groups_invitations` (
167 `sender_id` INTEGER UNSIGNED NOT NULL,
168 `member_id` INTEGER UNSIGNED NOT NULL,
169 `group_id` INTEGER UNSIGNED NOT NULL,
170 PRIMARY KEY (`sender_id`, `member_id`, `group_id`)
174 CREATE TABLE `social_groups_requests` (
175 `sender_id` INTEGER UNSIGNED NOT NULL,
176 `member_id` INTEGER UNSIGNED NOT NULL,
177 `group_id` INTEGER UNSIGNED NOT NULL,
178 PRIMARY KEY (`sender_id`, `member_id`, `group_id`)
182 CREATE TABLE `social_groups_types` (
183 `type_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
184 `title` VARCHAR(127) NOT NULL,
185 PRIMARY KEY (`type_id`)
189 # CREATE TABLE `social_groups_forums` (
190 # `group_id` INTEGER UNSIGNED NOT NULL,
191 # `forum_id` INTEGER UNSIGNED NOT NULL,
192 # PRIMARY KEY (`group_id`, `forum_id`)
196 # Groups message board
197 CREATE TABLE `social_groups_board` (
198 `id` int(10) unsigned NOT NULL auto_increment,
199 `member_id` int(10) unsigned NOT NULL,
200 `group_id` int(10) unsigned NOT NULL,
201 `body` text NOT NULL,
202 `created_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
208 CREATE TABLE `social_user_settings` (
209 `member_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
210 `app_settings` TEXT NOT NULL,
211 PRIMARY KEY (`member_id`)
216 #====== Initial Data ========
217 INSERT INTO social_groups_types SET title='business', type_id=1;
218 INSERT INTO social_groups_types SET title='common_interest', type_id=2;
219 INSERT INTO social_groups_types SET title='entertainment_arts', type_id=3;
220 INSERT INTO social_groups_types SET title='geography', type_id=4;
221 INSERT INTO social_groups_types SET title='internet_technology', type_id=5;
222 INSERT INTO social_groups_types SET title='organization', type_id=6;
223 INSERT INTO social_groups_types SET title='music', type_id=7;
224 INSERT INTO social_groups_types SET title='sports_recreation', type_id=8;
226 # END Social Networking setup
229 INSERT INTO `modules` VALUES ('_standard/social', 2, 8388608, 0, 0, 0);
231 # Login attempt control table
232 CREATE TABLE `member_login_attempt` (
233 `login` varchar(20) NOT NULL,
234 `attempt` tinyint(3) unsigned default NULL,
235 `expiry` int(10) unsigned default NULL,
236 PRIMARY KEY (`login`)
239 # --------------------------------------------------------
240 # Adding feature of blog subsription
241 # Table structure for table `blog_subscription`
243 CREATE TABLE `blog_subscription` (
244 `group_id` MEDIUMINT NOT NULL ,
245 `member_id` MEDIUMINT NOT NULL ,
246 PRIMARY KEY (group_id,member_id)
249 # END Adding feature of blog subsription
251 # --------------------------------------------------------
252 # Adding feature of "detail view" and "icon view" on course home page
254 ALTER TABLE `courses` add `home_view` tinyint NOT NULL DEFAULT 0;
255 ALTER TABLE `fha_student_tools` add `home_view` tinyint NOT NULL DEFAULT 0;
257 # END Adding feature of "detail view" and "icon view" on course home page