1 --*********************************************************
\r
2 -- Author: Berlin Brown
\r
5 -- Main sql script for creating the botlist database.
\r
7 -- updated: 11/12/2006
\r
9 -- file: create_tables.sql
\r
10 -- see insert_tables.sql
\r
13 -- 2/2/2008 - sql clean-ups, made sure script is up to date.
\r
14 --*********************************************************
\r
16 ---------------------------------
\r
17 -- Create the user admin tables
\r
18 ---------------------------------
\r
19 CREATE TABLE users (
\r
20 username VARCHAR(50) NOT NULL PRIMARY KEY,
\r
21 password VARCHAR(50) NOT NULL,
\r
22 enabled BIT NOT NULL
\r
25 CREATE TABLE authorities (
\r
26 username VARCHAR(50) NOT NULL,
\r
27 authority VARCHAR(50) NOT NULL
\r
30 ALTER TABLE authorities ADD CONSTRAINT fk_authorities_users foreign key (username) REFERENCES users(username);
\r
32 -- End of Creating Admin Tables
\r
33 ---------------------------------
\r
36 -- User links is deprecated (not used, but deleting may cause issues)
\r
37 CREATE TABLE user_links (
\r
38 id int(11) NOT NULL auto_increment,
\r
39 main_url varchar(255) NOT NULL,
\r
40 url_title varchar(128),
\r
41 url_description varchar(255),
\r
42 keywords varchar(255),
\r
44 source_url varchar(255),
\r
45 total_rating int(11) DEFAULT 0,
\r
46 occurrence int(11) DEFAULT 0,
\r
47 created_on DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
\r
53 CREATE TABLE user_visit_log (
\r
54 id int(11) NOT NULL auto_increment,
\r
55 remote_host varchar(30),
\r
57 referer varchar(255),
\r
58 user_agent varchar(255),
\r
59 request_uri varchar(255),
\r
60 request_page varchar(124),
\r
61 created_on DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
\r
68 CREATE TABLE city_listing (
\r
69 id int(11) NOT NULL auto_increment,
\r
70 city_name varchar(255) NOT NULL,
\r
71 created_on datetime NOT NULL default '0000-00-00 00:00:00',
\r
72 city_category varchar(10) default 'MINOR',
\r
73 state_abbr varchar(10) default NULL,
\r
75 UNIQUE KEY city_name (city_name),
\r
76 KEY city_listing_created_on_ndx (created_on)
\r
81 CREATE TABLE post_sections (
\r
82 id int(11) NOT NULL auto_increment,
\r
83 generated_id varchar(255) NOT NULL UNIQUE,
\r
84 section_name varchar(128) NOT NULL UNIQUE,
\r
85 created_on DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
\r
90 -- Create a default post ad listing
\r
91 -- A city has a post listing
\r
92 CREATE TABLE post_listing (
\r
93 id int(11) NOT NULL auto_increment,
\r
94 city_id int(11) NOT NULL,
\r
95 section_id int(11) NOT NULL,
\r
96 category varchar(128) NOT NULL,
\r
97 email varchar(128) NOT NULL,
\r
98 location varchar(255),
\r
99 title varchar(255) NOT NULL,
\r
100 main_url varchar(255),
\r
101 keywords varchar(255),
\r
102 message text NOT NULL,
\r
103 age int(11) DEFAULT 0,
\r
104 created_on DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
\r
106 constraint fk_post_listing
\r
107 foreign key (city_id) references city_listing(id),
\r
108 constraint fk_post_section
\r
109 foreign key (section_id) references post_sections(id)
\r
113 -- Image Metadata associated with ad postings
\r
114 -- typically, an ad listing could have 2 image uploads
\r
115 CREATE TABLE post_image_metadata(
\r
116 id int(11) NOT NULL auto_increment,
\r
117 adlist_id int(11) NOT NULL,
\r
118 image_filename varchar(255) NOT NULL UNIQUE,
\r
119 image_filesize int(11),
\r
120 image_originalname varchar(255),
\r
121 created_on DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
\r
123 constraint fk_image_adlist
\r
124 foreign key (adlist_id) references post_listing(id)
\r
127 -- Create the simple user blog
\r
128 -- Users have user-links
\r
129 -- The foreign key is attached to the 'has-a'
\r
131 -- entity_links is currently associated with
\r
132 -- 'child_list_links' and 'user_comments'
\r
133 CREATE TABLE entity_links (
\r
134 id int(11) NOT NULL auto_increment,
\r
135 main_url varchar(255) NOT NULL,
\r
136 url_title varchar(128) NOT NULL,
\r
137 url_description varchar(255) default NULL,
\r
138 keywords varchar(255) default NULL,
\r
139 views int(11) default '0',
\r
140 created_on datetime NOT NULL default '0000-00-00 00:00:00',
\r
141 rating int(11) NOT NULL default '0',
\r
142 user_id int(11) default NULL,
\r
143 full_name varchar(128) NOT NULL,
\r
144 hostname varchar(128) default NULL,
\r
145 process_count int(11) NOT NULL default '0',
\r
146 updated_on datetime default '0000-00-00 00:00:00',
\r
147 link_type varchar(20) default NULL,
\r
148 bot_rating decimal(5,2) default '0.00',
\r
149 generated_obj_id varchar(60) default NULL,
\r
150 user_up_votes int(11) default '0',
\r
151 user_down_votes int(11) default '0',
\r
152 links_ct int(11) default '0',
\r
153 inbound_link_ct int(11) default '0',
\r
154 outbound_links_ct int(11) default '0',
\r
155 image_ct int(11) default '0',
\r
156 meta_descr_len int(11) default '0',
\r
157 meta_keywords_len int(11) default '0',
\r
158 meta_descr_wct int(11) default '0',
\r
159 meta_keywords_wct int(11) default '0',
\r
160 geo_locations_ct int(11) default '0',
\r
161 geo_locations_found varchar(128) default NULL,
\r
162 document_size int(11) default '0',
\r
163 request_time int(11) default '0',
\r
164 object_id_status tinyint(4) default '0',
\r
165 para_tag_ct int(11) default '0',
\r
167 UNIQUE KEY main_url (main_url),
\r
168 UNIQUE KEY generated_obj_id (generated_obj_id),
\r
169 KEY entity_links_created_on_ndx (created_on),
\r
170 KEY entity_links_rating_ndx (rating),
\r
171 KEY entity_links_views_ndx (views)
\r
175 -- Entity Type Foaf
\r
177 CREATE TABLE entity_type_foaf (
\r
178 -- **********************
\r
179 -- Default Entity Type Fields
\r
180 -- **********************
\r
181 id int(11) NOT NULL auto_increment,
\r
182 main_url varchar(255) NOT NULL UNIQUE,
\r
183 url_title varchar(128) NOT NULL,
\r
184 url_description varchar(255) default NULL,
\r
185 keywords varchar(255) default NULL,
\r
186 generated_obj_id varchar(60) default NULL UNIQUE,
\r
187 created_on DATETIME NOT NULL default '0000-00-00 00:00:00',
\r
188 updated_on datetime default '0000-00-00 00:00:00',
\r
190 -- Full name and user id (information about who created the entry)
\r
191 full_name varchar(80) NOT NULL,
\r
192 user_id int(11) default NULL,
\r
193 views int(11) default '0',
\r
194 rating SMALLINT default '0',
\r
195 process_count SMALLINT default '0',
\r
197 nickname varchar(50) NOT NULL,
\r
198 foaf_name varchar(80) NOT NULL,
\r
199 foaf_interest_descr TEXT NOT NULL,
\r
200 foaf_mbox varchar(255) NOT NULL,
\r
201 foaf_page_doc_url varchar(255) NOT NULL,
\r
202 foaf_img varchar(255),
\r
203 friend_set_uid varchar(60),
\r
205 request_time SMALLINT,
\r
206 http_status_code varchar(10),
\r
208 date_of_birth DATE NOT NULL DEFAULT '0000-00-00',
\r
214 -- created: 1/4/2007
\r
215 CREATE TABLE child_list_links (
\r
216 id int(11) NOT NULL auto_increment,
\r
218 main_url varchar(255) NOT NULL unique,
\r
219 url_title varchar(128) NOT NULL,
\r
220 keywords varchar(255),
\r
221 created_on DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
\r
223 constraint fk_child_link
\r
224 foreign key (link_id) references entity_links(id)
\r
228 -- Create the forum groups for the forum discussions
\r
229 CREATE TABLE forum_group (
\r
230 id int(11) NOT NULL auto_increment,
\r
232 forum_name varchar(255) NOT NULL UNIQUE,
\r
233 forum_descr varchar(255),
\r
234 keywords varchar(255),
\r
235 created_on DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
\r
239 CREATE TABLE user_comments(
\r
240 id int(11) NOT NULL auto_increment,
\r
242 adlist_id int(11),
\r
244 comment_id int(11),
\r
246 full_name varchar(128) NOT NULL,
\r
248 subject varchar(255),
\r
249 zipcode varchar(20),
\r
250 main_url varchar(255),
\r
251 keywords varchar(255),
\r
252 message TEXT NOT NULL,
\r
253 created_on DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
\r
256 foreign key (link_id) references entity_links(id),
\r
257 constraint fk_post_listing_comments
\r
258 foreign key (adlist_id) references post_listing(id),
\r
259 constraint fk_forum_group_comments
\r
260 foreign key (forum_id) references forum_group(id)
\r
264 -- Group Links - Categorize links by group
\r
266 CREATE TABLE link_groups (
\r
267 id int(11) NOT NULL auto_increment,
\r
268 group_name varchar(255) NOT NULL UNIQUE,
\r
269 generated_id varchar(255) NOT NULL UNIQUE,
\r
270 created_on DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
\r
274 CREATE TABLE group_links (
\r
275 id int(11) NOT NULL auto_increment,
\r
276 group_id int(11) NOT NULL,
\r
277 main_url varchar(255) NOT NULL unique,
\r
278 url_title varchar(128) NOT NULL,
\r
279 url_description varchar(255),
\r
280 keywords varchar(255),
\r
281 views int(11) DEFAULT 0,
\r
282 rating int(11) DEFAULT 0,
\r
283 created_on DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
\r
285 constraint fk_group_links
\r
286 foreign key (group_id) references link_groups(id)
\r
292 CREATE TABLE core_users (
\r
293 id int(11) NOT NULL auto_increment,
\r
294 user_name varchar(50) NOT NULL,
\r
295 user_password varchar(128) NOT NULL,
\r
296 user_email varchar(255) NOT NULL,
\r
297 user_url varchar(255) default NULL,
\r
298 location varchar(255) default NULL,
\r
299 date_of_birth date NOT NULL default '0000-00-00',
\r
300 experience_points int(11) default '0',
\r
301 karma int(11) default '0',
\r
302 secretques_code tinyint(4) NOT NULL default '0',
\r
303 secret_answer varchar(128) default NULL,
\r
304 account_number varchar(128) NOT NULL,
\r
305 active_code tinyint(4) default '0',
\r
306 failed_attempts int(11) default '0',
\r
307 last_login_success datetime NOT NULL default '0000-00-00 00:00:00',
\r
308 last_login_failure datetime NOT NULL default '0000-00-00 00:00:00',
\r
309 created_on datetime NOT NULL default '0000-00-00 00:00:00',
\r
310 updated_on datetime NOT NULL default '0000-00-00 00:00:00',
\r
312 UNIQUE KEY user_name (user_name),
\r
313 KEY core_users_created_on_ndx (created_on)
\r
317 -- Access Control List - List of access levels
\r
318 CREATE TABLE acl_control (
\r
319 id int(11) NOT NULL auto_increment,
\r
320 control_uid varchar(128) NOT NULL,
\r
321 control_name varchar(50) NOT NULL,
\r
322 short_descr varchar(50) NOT NULL,
\r
323 long_descr varchar(255) default NULL,
\r
324 created_on datetime NOT NULL default '0000-00-00 00:00:00',
\r
329 -- Group Control List - List of groups
\r
330 CREATE TABLE group_control (
\r
331 id int(11) NOT NULL auto_increment,
\r
332 group_uid VARCHAR(128) NOT NULL,
\r
333 group_name VARCHAR(50) NOT NULL,
\r
334 short_descr VARCHAR(50) NOT NULL,
\r
335 long_descr VARCHAR(255),
\r
336 created_on DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
\r
340 CREATE TABLE acl_manager (
\r
341 id int(11) NOT NULL auto_increment,
\r
342 acl_id int(11) NOT NULL,
\r
343 user_id int(11) NOT NULL,
\r
344 created_on DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
\r
346 constraint fk_acl_manager_acl
\r
347 foreign key (acl_id) references acl_control(id),
\r
348 constraint fk_acl_manager_user
\r
349 foreign key (user_id) references core_users(id)
\r
352 CREATE TABLE group_manager (
\r
353 id int(11) NOT NULL auto_increment,
\r
354 group_id int(11) NOT NULL,
\r
355 user_id int(11) NOT NULL,
\r
356 created_on DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
\r
358 constraint fk_group_manager_group
\r
359 foreign key (group_id) references group_control(id),
\r
360 constraint fk_group_manager_user
\r
361 foreign key (user_id) references core_users(id)
\r
365 -- Profile Settings, profile settings associated with the core user
\r
366 -- set link color to light blue: 3838E5
\r
368 CREATE TABLE profile_settings (
\r
369 id int(11) NOT NULL auto_increment,
\r
370 user_id int(11) NOT NULL UNIQUE,
\r
371 link_color varchar(10) NOT NULL DEFAULT '3838E5',
\r
372 created_on DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
\r
374 constraint fk_profile_settings
\r
375 foreign key (user_id) references core_users(id)
\r
378 -- End of User Tables
\r
380 CREATE TABLE active_media_feeds (
\r
381 id int(11) NOT NULL auto_increment,
\r
382 display_type char(1) NOT NULL default 'N',
\r
383 created_on datetime NOT NULL default '0000-00-00 00:00:00',
\r
385 KEY active_media_feeds_created_on_ndx (created_on)
\r
388 CREATE TABLE media_feeds (
\r
389 id int(11) NOT NULL auto_increment,
\r
390 image_filename varchar(255) NOT NULL,
\r
391 media_url varchar(255) NOT NULL,
\r
392 image_thumbnail varchar(255) NOT NULL,
\r
393 media_title varchar(255) NOT NULL,
\r
394 media_descr text NOT NULL,
\r
395 media_type char(1) NOT NULL default 'N',
\r
396 author varchar(80) NOT NULL,
\r
397 rating decimal(11,5) default NULL,
\r
398 rating_count int(11) default '0',
\r
399 views int(11) default '0',
\r
400 keywords varchar(128) NOT NULL,
\r
401 orginal_imgurl varchar(255) NOT NULL,
\r
402 process_count int(11) default '0',
\r
403 system_id int(11) default NULL,
\r
404 validity int(11) default NULL,
\r
405 created_on datetime NOT NULL default '0000-00-00 00:00:00',
\r
407 UNIQUE KEY media_url (media_url),
\r
408 KEY media_feeds_created_on_ndx (created_on)
\r
411 CREATE TABLE admin_main_banner (
\r
412 id int(11) NOT NULL auto_increment,
\r
413 headline varchar(128) NOT NULL,
\r
414 enabled char(1) NOT NULL default 'N',
\r
415 app_section varchar(40) default NULL,
\r
416 created_on datetime NOT NULL default '0000-00-00 00:00:00',
\r
420 CREATE TABLE cat_group_terms (
\r
421 id int(11) NOT NULL auto_increment,
\r
422 category_name varchar(20) NOT NULL,
\r
423 category_term varchar(40) NOT NULL,
\r
424 created_on datetime NOT NULL default '0000-00-00 00:00:00',
\r
425 PRIMARY KEY (id, category_name)
\r
428 CREATE TABLE cat_link_groups (
\r
429 category_name varchar(20) NOT NULL,
\r
430 category_descr varchar(80) NOT NULL,
\r
431 category_color varchar(10) NOT NULL,
\r
432 created_on datetime NOT NULL default '0000-00-00 00:00:00',
\r
433 PRIMARY KEY (category_name),
\r
434 UNIQUE KEY category_name (category_name)
\r
438 CREATE TABLE developer_users (
\r
439 id int(11) NOT NULL auto_increment,
\r
440 user_name varchar(50) NOT NULL,
\r
441 user_id int(11) NOT NULL,
\r
442 key_id varchar(128) NOT NULL,
\r
443 enabled char(1) NOT NULL default 'N',
\r
444 description varchar(255) NOT NULL,
\r
445 last_application varchar(30) NOT NULL,
\r
446 last_login_success datetime NOT NULL default '0000-00-00 00:00:00',
\r
447 last_login_failure datetime NOT NULL default '0000-00-00 00:00:00',
\r
448 created_on datetime NOT NULL default '0000-00-00 00:00:00',
\r
449 updated_on datetime NOT NULL default '0000-00-00 00:00:00',
\r
451 UNIQUE KEY user_name (user_name)
\r
454 CREATE TABLE doc_file (
\r
455 id int(11) NOT NULL auto_increment,
\r
456 child_id int(11) default NULL,
\r
457 full_name varchar(128) NOT NULL,
\r
458 title varchar(255) NOT NULL,
\r
459 filename varchar(255) NOT NULL,
\r
460 message text NOT NULL,
\r
461 created_on datetime NOT NULL default '0000-00-00 00:00:00',
\r
465 CREATE TABLE doc_file_metadata (
\r
466 id int(11) NOT NULL auto_increment,
\r
467 document_id int(11) NOT NULL,
\r
468 doc_filename varchar(255) NOT NULL,
\r
469 doc_filesize int(11) default NULL,
\r
470 doc_originalname varchar(255) default NULL,
\r
471 created_on datetime NOT NULL default '0000-00-00 00:00:00',
\r
473 UNIQUE KEY doc_filename (doc_filename),
\r
474 KEY fk_file_document (document_id)
\r
477 CREATE TABLE search_query_filters (
\r
478 id int(11) NOT NULL auto_increment,
\r
479 search_term varchar(60) NOT NULL,
\r
480 description varchar(128) NOT NULL,
\r
481 rating int(11) NOT NULL,
\r
482 views int(11) NOT NULL,
\r
483 user_name varchar(50) NOT NULL,
\r
484 user_id int(11) NOT NULL,
\r
485 created_on datetime NOT NULL default '0000-00-00 00:00:00',
\r
489 CREATE TABLE system_audit_log (
\r
490 id int(11) NOT NULL auto_increment,
\r
491 application_name varchar(60) NOT NULL,
\r
492 message_id varchar(10) NOT NULL,
\r
493 message varchar(255) default NULL,
\r
494 log_level varchar(10) NOT NULL,
\r
495 send_to varchar(80) default NULL,
\r
496 created_on datetime NOT NULL default '0000-00-00 00:00:00',
\r
500 CREATE TABLE system_feed_items (
\r
501 id int(11) NOT NULL auto_increment,
\r
502 main_url varchar(255) NOT NULL,
\r
503 url_title varchar(128) NOT NULL,
\r
504 url_description text,
\r
505 url_source varchar(255) NOT NULL,
\r
506 process_count int(11) NOT NULL default '0',
\r
507 created_on datetime NOT NULL default '1901-01-01 00:00:00',
\r
508 hostname varchar(128) default NULL,
\r
509 enum_proc_type varchar(30),
\r
511 UNIQUE KEY main_url (main_url)
\r
514 CREATE TABLE system_scan_feeds (
\r
515 id int(11) NOT NULL auto_increment,
\r
516 main_url varchar(255) NOT NULL,
\r
517 url_title varchar(128) NOT NULL,
\r
518 url_description varchar(255) default NULL,
\r
519 url_source varchar(255) NOT NULL,
\r
520 created_on datetime NOT NULL default '0000-00-00 00:00:00',
\r
522 UNIQUE KEY main_url (main_url)
\r
525 CREATE TABLE system_web_files (
\r
526 id int(11) NOT NULL auto_increment,
\r
527 filename varchar(255) NOT NULL,
\r
528 fsize int(10) NOT NULL,
\r
529 fmtime int(10) NOT NULL,
\r
530 fext varchar(40) NOT NULL,
\r
531 projectname varchar(80) NOT NULL,
\r
532 created_on datetime NOT NULL default '0000-00-00 00:00:00',
\r
536 CREATE TABLE user_entity_links (
\r
537 id int(11) NOT NULL auto_increment,
\r
538 user_id int(11) NOT NULL,
\r
539 link_id int(11) NOT NULL,
\r
540 created_on datetime NOT NULL default '0000-00-00 00:00:00',
\r
544 CREATE TABLE user_visit_log (
\r
545 id int(11) NOT NULL auto_increment,
\r
546 remote_host varchar(30) default NULL,
\r
547 host varchar(30) default NULL,
\r
548 referer varchar(255) default NULL,
\r
549 user_agent varchar(255) default NULL,
\r
550 request_uri varchar(255) default NULL,
\r
551 request_page varchar(124) default NULL,
\r
552 created_on datetime NOT NULL default '0000-00-00 00:00:00',
\r
554 KEY user_visit_log_created_on_ndx (created_on)
\r
557 --*********************************************************
\r
558 -- Request session log, session log for REST system
\r
559 -- Updated: 2/2/2008
\r
560 --*********************************************************
\r
561 CREATE TABLE session_request_log (
\r
562 request_id varchar(50) NOT NULL UNIQUE,
\r
563 remote_host varchar(20) NOT NULL,
\r
564 msg_value varchar(40) NOT NULL,
\r
565 msg_key varchar(40) NOT NULL,
\r
566 param_key varchar(40),
\r
567 param_value varchar(40),
\r
568 created_on DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
\r
569 PRIMARY KEY (request_id)
\r
572 --*********************************************************
\r
573 -- Insert initial data for botlist
\r
574 -- Updated: 2/2/2008
\r
575 --*********************************************************
\r
577 INSERT INTO city_listing(city_name, created_on) VALUES('Atlanta', NOW());
\r
578 INSERT INTO city_listing(city_name, created_on) VALUES('Austin', NOW());
\r
580 INSERT INTO city_listing(city_name, created_on) VALUES('Dallas', NOW());
\r
581 INSERT INTO city_listing(city_name, created_on) VALUES('Denver', NOW());
\r
582 INSERT INTO city_listing(city_name, created_on) VALUES('Chicago', NOW());
\r
583 INSERT INTO city_listing(city_name, created_on) VALUES('Houston', NOW());
\r
584 INSERT INTO city_listing(city_name, created_on) VALUES('Miami', NOW());
\r
585 INSERT INTO city_listing(city_name, created_on) VALUES('NewYork', NOW());
\r
586 INSERT INTO city_listing(city_name, created_on) VALUES('Phoenix', NOW());
\r
587 INSERT INTO city_listing(city_name, created_on) VALUES('Las Vegas', NOW());
\r
588 INSERT INTO city_listing(city_name, created_on) VALUES('San Diego', NOW());
\r
589 INSERT INTO city_listing(city_name, created_on) VALUES('SF', NOW());
\r
590 INSERT INTO city_listing(city_name, created_on) VALUES('Seattle', NOW());
\r
591 INSERT INTO city_listing(city_name, created_on) VALUES('Portland', NOW());
\r
593 INSERT INTO city_listing(city_name, created_on) VALUES('Bangalore', NOW());
\r
594 INSERT INTO city_listing(city_name, created_on) VALUES('Berlin', NOW());
\r
595 INSERT INTO city_listing(city_name, created_on) VALUES('London', NOW());
\r
596 INSERT INTO city_listing(city_name, created_on) VALUES('Tokyo', NOW());
\r
597 INSERT INTO city_listing(city_name, created_on) VALUES('Other', NOW());
\r
599 -- Update city listings, setting current set to 'MAJOR'
\r
600 update city_listing set city_category = 'MAJOR';
\r
603 -- Insert minor cities
\r
606 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Birmingham, Al', 'MINOR', 'AL', NOW());
\r
607 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Tuscaloosa, Al', 'MINOR', 'AL', NOW());
\r
609 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Arlington, Tx', 'MINOR', 'TX', NOW());
\r
610 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('San Antonio, Tx', 'MINOR', 'TX', NOW());
\r
612 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Biloxi, Ms', 'MINOR', 'MS', NOW());
\r
614 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('New Orleans, La', 'MINOR', 'LA', NOW());
\r
616 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Portland, Or', 'MINOR', 'OR', NOW());
\r
617 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Athens, Ga', 'MINOR', 'GA', NOW());
\r
619 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Cleveland, Oh', 'MINOR', 'OH', NOW());
\r
622 --- Second set of city listings
\r
624 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Los Angeles, Ca', 'MAJOR', NULL, NOW());
\r
625 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Wash DC', 'MAJOR', NULL, NOW());
\r
627 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Sacremento, Ca', 'MINOR', 'CA', NOW());
\r
629 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Denver, Co', 'MINOR', 'CO', NOW());
\r
631 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Jacksonville, Fl', 'MINOR', 'FL', NOW());
\r
632 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Orlando, Fl', 'MINOR', 'FL', NOW());
\r
634 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Hawaii', 'MINOR', 'HAWAII', NOW());
\r
636 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Indianapolis, IN', 'MINOR', 'IN', NOW());
\r
638 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Maine - Minor', 'MINOR', 'MAINE', NOW());
\r
640 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Massachusetts', 'MINOR', 'MASS', NOW());
\r
642 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('New Jersey', 'MINOR', 'NJ', NOW());
\r
644 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Paris, France', 'MINOR', 'FRANCE', NOW());
\r
646 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Alberta, Canada', 'MINOR', 'CANADA', NOW());
\r
648 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Beijing, China', 'MINOR', 'CHINA', NOW());
\r
650 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Hong Kong, China', 'MINOR', 'CHINA', NOW());
\r
652 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Shanghai, China', 'MINOR', 'CHINA', NOW());
\r
654 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Madrid, Spain', 'MINOR', 'SPAIN', NOW());
\r
656 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Mumbai, India', 'MINOR', 'INDIA', NOW());
\r
658 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Delhi, India', 'MINOR', 'INDIA', NOW());
\r
660 INSERT INTO city_listing(city_name, city_category, state_abbr, created_on) VALUES('Australia', 'MINOR', 'AUSTRALIA', NOW());
\r
663 --- Add new reviews section
\r
665 INSERT INTO post_sections(generated_id, section_name, created_on)
\r
666 VALUES('c8ec2847bdac35595ffba82aa0f65fcbreviews', 'Reviews', NOW());
\r
668 INSERT INTO post_sections(generated_id, section_name, created_on)
\r
669 VALUES('e6ba9e9ab139f9c0b090cf77beaccNews', 'Local News', NOW());
\r
672 -- Create the forums
\r
673 INSERT INTO post_sections(generated_id, section_name, created_on)
\r
674 VALUES('1fb6674f66ff9e617ec1313978513096', 'General Listings', NOW());
\r
676 INSERT INTO post_sections(generated_id, section_name, created_on)
\r
677 VALUES('9ce00803181d4611895ad3e764b2adb2', 'Personals', NOW());
\r
679 INSERT INTO post_sections(generated_id, section_name, created_on)
\r
680 VALUES('7ebc9a603519b9cdc277c2fc2d68d1a9', 'Resumes', NOW());
\r
682 INSERT INTO post_sections(generated_id, section_name, created_on)
\r
683 VALUES('0b578fbfe97b317ded5ad929c2210b7d', 'Jobs', NOW());
\r
685 INSERT INTO post_sections(generated_id, section_name, created_on)
\r
686 VALUES('cab4d3bd8e28cb03f17c12e5b322d6fb', 'For Sale', NOW());
\r
688 INSERT INTO post_sections(generated_id, section_name, created_on)
\r
689 VALUES('effb06927b7bea46709d5d21c2465e04', 'Technology', NOW());
\r
691 INSERT INTO post_sections(generated_id, section_name, created_on)
\r
692 VALUES('9e876afb5e45a7f1d670ceceec3352a8', 'Events', NOW());
\r
694 INSERT INTO post_sections(generated_id, section_name, created_on)
\r
695 VALUES('c8ec2847bdac35595ffba82aa0f65fcbreviews', 'Reviews', NOW());
\r
698 -- Insert the discussion forums
\r
699 INSERT INTO forum_group(forum_name,
\r
700 forum_descr, keywords, created_on)
\r
701 VALUES ('General Forum', 'General Forum', 'general forum forums chat', NOW());
\r
703 INSERT INTO forum_group(forum_name,
\r
704 forum_descr, keywords, created_on)
\r
705 VALUES ('Bugs and Feature Requests', 'Bugs and Feature Requests', 'bugs chat forums', NOW());
\r
707 INSERT INTO forum_group(forum_name,
\r
708 forum_descr, keywords, created_on)
\r
709 VALUES ('Bot Chat', 'Bot Chat', 'bot chat forums', NOW());
\r
712 -- Insert new users (apr pwd)
\r
714 insert into core_users(user_name, user_password, user_email, date_of_birth, account_number, active_code, last_login_success, last_login_failure, created_on, updated_on) VALUES(
\r
715 'botbob', 'c5084a613255f920e3be35e5366a94a8', 'botbob@bot.com', '1981-01-01', 'c3c18d19b5887570e74ef6cdce4b6abbbotbob', 1, NOW(), NOW(), NOW(), NOW());
\r
717 insert into profile_settings(user_id, created_on) values(LAST_INSERT_ID(), NOW());
\r
719 insert into core_users(user_name, user_password, user_email, date_of_birth, account_number, active_code, last_login_success, last_login_failure, created_on, updated_on) VALUES(
\r
720 'botrover99', 'fa911d71b19af603c0f8a10455a670ef', 'botrover99@email.com', '1981-01-01', '55ebef6779f025f9174478e6abcd3874botrover99', 1, NOW(), NOW(), NOW(), NOW());
\r
722 insert into profile_settings(user_id, created_on) values(LAST_INSERT_ID(), NOW());
\r
724 insert into core_users(user_name, user_password, user_email, date_of_birth, account_number, active_code, last_login_success, last_login_failure, created_on, updated_on) VALUES(
\r
725 'botbert99', 'fa911d71b19af603c0f8a10455a670ef', 'botbert99@email.com', '1981-01-01', 'b2acafa3c71be70c5aed5aedad365342botbert99', 1, NOW(), NOW(), NOW(), NOW());
\r
727 insert into profile_settings(user_id, created_on) values(LAST_INSERT_ID(), NOW());
\r
730 -- Insert Default Link Groups
\r
732 insert into link_groups(group_name, generated_id, created_on) values('Info Articles', 'aaf9dfb546f650d5fa614156000info', NOW());
\r
733 insert into link_groups(group_name, generated_id, created_on) values('Media', 'fd50091908d57ab8b15db358000media', NOW());
\r
734 insert into link_groups(group_name, generated_id, created_on) values('NSFW', '65ebdbd0e6a0a67c029000nsfw', NOW());
\r
736 -- Update the entity_links
\r
738 --update entity_links a
\r
739 -- set user_id = (select id from core_users b
\r
740 -- where a.full_name = b.user_name);
\r
742 -- Insert into user links
\r
744 --insert into user_entity_links(user_id, link_id, created_on)
\r
745 -- select user_id, id, NOW() from entity_links limit 0, 200;
\r