L0PjjP56

1/*====================================================
2--Program: TO Database dump and consolidation mysql batch file
3--Author: Christopher Kuttruff
4--Usage: Run this script on the desired truthout database
5--	 to consolidate relevant information from tables
6--	 and output to a delimited file
7=====================================================*/
8
9/*Change to desired database*/
10USE truth18_drupal;
11/*USE to_temp;*/
12
13DROP TABLE IF EXISTS TO_photo_paths;
14CREATE TABLE TO_photo_paths(id int(10) PRIMARY KEY, photo_path varchar(50));
15
16INSERT IGNORE INTO TO_photo_paths
17SELECT nid, filepath
18FROM files
19WHERE filename='_original';
20
21SELECT 
22/*Node ID, content_type, published status (1 for published)*/
23node.nid, node.type, node.status, 
24/*article type (1 for opinion, 2 for news), issues featured status*/
25field_article_type_value, field_featured_issuespage_value,
26/*TO original status (1 for originals), Article page title, headline title*/
27field_is_original_value, field_page_title_value, node.title, 
28/*Article author, source publication*/
29field_source_article_author_value, field_source_article_publicatio_value,
30/*Article source url, original article date*/
31field_source_article_url_value, field_source_article_date_value,
32/*article teaser/body text */
33node_revisions.teaser, node_revisions.body,
34/*Image filename, filename path, caption*/
35field_alternate_illustration_title, TO_photo_paths.photo_path, field_image_caption_value
36
37
38/*====================================================
39--Redirect output into text file
40====================================================*/
41
42INTO OUTFILE '/tmp2/1_mysql_outfile.txt'
43
44/*Specify delimiter*/
45FIELDS TERMINATED BY '|%%|'
46
47FROM 
48node
49
50/*====================================================
51--Organize tables by primary key, vid
52====================================================*/
53
54LEFT JOIN content_field_article_type on 
55     (node.vid = content_field_article_type.vid)
56LEFT JOIN content_type_article on (node.vid = 
57     content_type_article.vid)
58LEFT JOIN content_field_is_original on (node.vid = 
59     content_field_is_original.vid )	  			     
60LEFT JOIN content_field_page_title on (node.vid = 
61     content_field_page_title.vid )
62LEFT JOIN content_field_source_article_author on (node.vid = 
63     content_field_source_article_author.vid )
64LEFT JOIN content_field_source_article_publicatio on (node.vid = 
65     content_field_source_article_publicatio.vid )
66LEFT JOIN content_field_source_article_url on (node.vid = 
67     content_field_source_article_url.vid )
68LEFT JOIN content_field_source_article_date on (node.vid = 
69     content_field_source_article_date.vid )
70LEFT JOIN content_field_image_caption on (node.vid = 
71     content_field_image_caption.vid)
72LEFT JOIN node_revisions on (node.vid = 
73     node_revisions.vid )
74LEFT JOIN TO_photo_paths on (node.nid = 
75     TO_photo_paths.id)
76
77/*Cut off first 200 entries*/
78WHERE node.nid>200
79/*Order output by node id number*/
80ORDER BY nid;
81
82/*drop temporary photo paths table*/
83DROP TABLE TO_photo_paths;
84
85/*====================================================
86--Create delimited files to parse url aliases and meta tags
87====================================================*/
88/*Fix body text with perl script*/
89system perl 02_body_txt_parser.perl /tmp2/1_mysql_outfile.txt > /tmp2/2_parsed_db_output.txt
90
91/*Create table to hold parsed data*/
92DROP TABLE IF EXISTS TO_custom_fields;
93CREATE TABLE TO_custom_fields ( 
94nid_num int(10) UNSIGNED PRIMARY KEY, node_type int(10), 
95published_status int(1), article_type int(1), issue_feature_status int(2), 
96TO_orig_status int(1), page_title varchar(200), headline_title varchar(200), 
97article_author varchar(100), article_source varchar(100), 
98source_url varchar(200), article_date varchar(20), teaser_text text, 
99body_text longtext, img_filename varchar(20), 
100image_pathname varchar(20), image_caption text)
101CHARACTER SET utf8
102COLLATE utf8_general_ci;
103
104/*Load parsed body text and other data into newly created table*/
105LOAD DATA INFILE '/tmp2/2_parsed_db_output.txt'
106IGNORE INTO TABLE TO_custom_fields
107FIELDS TERMINATED BY '|%%|';
108
109/*Create temporary table to store necessary meta-tag info*/
110CREATE TABLE TO_temp_attribs (
111node_id int(10) UNSIGNED PRIMARY KEY, vid int(10) UNSIGNED, 
112term_id int(10) UNSIGNED, term_name varchar(200), 
113node_type varchar(20), article_type varchar(20), original_status int(2),
114INDEX USING BTREE(term_id, vid, term_name))
115CHARACTER SET utf8
116COLLATE utf8_general_ci;
117
118/*Insert info from term_node into temporary table*/
119INSERT IGNORE INTO TO_temp_attribs(node_id, vid, term_id, term_name, node_type,
120article_type, original_status)
121SELECT term_node.nid, node.vid, term_node.tid, term_data.name, 
122node.type, field_article_type_value, field_is_original_value
123FROM term_node
124LEFT JOIN node ON
125term_node.nid = node.nid
126JOIN term_data ON
127term_node.tid = term_data.tid
128JOIN content_field_article_type ON
129node.vid = content_field_article_type.vid
130JOIN content_field_is_original ON
131node.vid = content_field_is_original.vid;
132
133/*Create text file for content keyword/attribute parsing*/
134
135SELECT TO_temp_attribs.term_id, TO_temp_attribs.node_id, 
136TO_temp_attribs.term_name, TO_temp_attribs.node_type,
137TO_temp_attribs.article_type, TO_temp_attribs.original_status
138INTO OUTFILE '/tmp2/3_keywords.txt'
139FIELDS TERMINATED BY '|%%|'
140FROM TO_temp_attribs;
141/*Delete temporary table*/
142DROP TABLE TO_temp_attribs;
143
144/*Create text file for url aliases*/
145SELECT src, dst
146INTO OUTFILE '/tmp2/4_url_aliases.txt'
147/*Specify delimiter*/
148FIELDS TERMINATED BY '|%%|'
149FROM url_alias;
150
151/*====================================================
152--Run perl scripts to fix url aliases, and meta tags
153====================================================*/
154system perl 03_meta_tag_parser.perl /tmp2/3_keywords.txt > /tmp2/5_parsed_keywords.txt
155system perl 04_url_alias_parser.perl /tmp2/4_url_aliases.txt > /tmp2/6_parsed_aliases.txt
156
157
158/*====================================================
159--Load new data into joomla/custom tables
160====================================================*/
161
162DROP TABLE IF EXISTS TO_temp_keywords;
163CREATE TABLE TO_temp_keywords(
164node_id int(10) unsigned PRIMARY KEY,
165keywords varchar(200))
166CHARACTER SET utf8
167COLLATE utf8_general_ci;
168
169DROP TABLE IF EXISTS TO_temp_aliases;
170CREATE TABLE TO_temp_aliases(
171node_id int(10) unsigned,
172aliases varchar(200),
173INDEX USING BTREE(node_id))
174CHARACTER SET utf8
175COLLATE utf8_general_ci;
176
177LOAD DATA INFILE '/tmp2/5_parsed_keywords.txt'
178INTO TABLE TO_temp_keywords
179FIELDS TERMINATED BY '|%%|';
180
181LOAD DATA INFILE '/tmp2/6_parsed_aliases.txt'
182INTO TABLE TO_temp_aliases
183FIELDS TERMINATED BY '|%%|';
184DELETE FROM TO_temp_aliases WHERE node_id=0 
185OR aliases IS NULL;
186
187DROP TABLE IF EXISTS TO_custom_fields_tmp;
188CREATE TABLE TO_custom_fields_tmp (nid_num int(10) UNSIGNED PRIMARY KEY, node_type int(2),  published_status int(1), article_type int(1), TO_issue_feature_status int(2),  TO_orig_status int(1), article_page_title varchar(200), article_headline_title varchar(200),  article_author varchar(100), article_source varchar(100),  article_source_url varchar(200), article_date varchar(20), teaser_text text,  body_text longtext, image_pathname varchar(20), image_caption text,  keywords varchar(200), aliases varchar(200))
189CHARACTER SET utf8
190COLLATE utf8_general_ci;   
191
192INSERT IGNORE INTO TO_custom_fields_tmp SELECT nid_num, node_type, published_status, article_type, issue_feature_status, TO_orig_status, page_title, headline_title, article_author, article_source, source_url, article_date, teaser_text, body_text, image_pathname, image_caption text, TO_temp_keywords.keywords, TO_temp_aliases.aliases from TO_custom_fields LEFT JOIN TO_temp_keywords ON TO_custom_fields.nid_num = TO_temp_keywords.node_id LEFT JOIN TO_temp_aliases ON TO_custom_fields.nid_num = TO_temp_aliases.node_id;
193
194/*delete blanke image things  ***DEBUG****/
195/*DELETE FROM TO_custom_fields_tmp WHERE node_type='image';*/
196
197/*Drop temporary tables*/
198DROP TABLE TO_temp_keywords, TO_temp_aliases, TO_custom_fields;
199
200/*====================================================*/
201/*Insert proper fields into Joomla database and rest in custom table*/
202/*====================================================*/
203
204ALTER TABLE TO_custom_fields_tmp ADD COLUMN db_user int(11);
205UPDATE TO_custom_fields_tmp SET db_user=63;
206
207INSERT IGNORE INTO truth18_joomla.to_content(id, title, `alias`,
208introtext, `fulltext`, `state`, sectionid, 
209catid, created, created_by, created_by_alias, metakey)
210SELECT nid_num, article_headline_title, aliases,
211teaser_text, body_text, published_status, node_type,
212TO_orig_status, article_date, db_user, article_author, keywords
213FROM TO_custom_fields_tmp;
214
215/*Create table to hold additional custom fields not in joomla's _content*/
216DROP TABLE IF EXISTS truth18_joomla.TO_additional_fields;
217CREATE TABLE truth18_joomla.TO_additional_fields (id_num int(10) PRIMARY KEY 
218AUTO_INCREMENT,
219element_type varchar(10), TO_orig_status int(2), 
220article_headline_title varchar(200), article_page_title varchar(200), 
221article_source varchar(100), article_source_url varchar(200), 
222article_author varchar (50), article_date varchar(60), 
223article_image_caption text)
224CHARACTER SET utf8
225COLLATE utf8_general_ci;
226
227/*Insert leftover fields(with some additional 
228(not strictly necessary) info) into new tbl*/
229INSERT IGNORE INTO truth18_joomla.TO_additional_fields
230SELECT nid_num, node_type, 
231TO_orig_status, 
232article_headline_title,
233article_page_title, article_source, 
234article_source_url, article_author, 
235article_date, image_caption
236FROM TO_custom_fields_tmp;
237
238/*Create table to hold additional images*/
239DROP TABLE IF EXISTS truth18_joomla.TO_additional_image_paths;
240CREATE TABLE truth18_joomla.TO_additional_image_paths (photo_id int(10) PRIMARY KEY, 
241id_num int(10), img_type varchar(20), 
242img_path varchar(60), img_date varchar(20),
243INDEX USING BTREE(id_num))
244CHARACTER SET utf8
245COLLATE utf8_general_ci;
246
247INSERT INTO truth18_joomla.TO_additional_image_paths 
248SELECT fid, files.nid, filename, filepath, created
249FROM files
250LEFT JOIN node ON
251files.nid=node.nid;
252
253DROP TABLE TO_custom_fields_tmp;
254
255/* Adjust character encoding of body text and teaser */
256USE truth18_joomla;
257/* Might not be necessary... may need to changed 
258   initial character encoding of column*/
259UPDATE to_content SET introtext=CONVERT(CONVERT(CONVERT(introtext USING latin1) USING BINARY) USING utf8);
260UPDATE to_content SET `fulltext`=CONVERT(CONVERT(CONVERT(`fulltext` USING latin1) USING BINARY) USING utf8);
261
262/*Fix Meta tags*/
263system perl 05_meta_tag_fixer.perl
264
265/*====================================================
266--END OF SCRIPT
267====================================================*/