#!/bin/sh
 
### cat@brainycat.com Tue Apr  7 21:27:49 PDT 2009
### copyrighted under the terms of the GPLv3
### http://www.gnu.org/licenses/gpl-3.0.html
 
### BACKUP YOUR DATABASE BEFORE RUNNING THIS SCRIPT
### "its always easier to rm unwanted data than recreate wanted data"
### HERE document shamelessly copied from phpmyadmin backup output
 
### VARIABLES
 
# input file format URL\tLINKNAME\twpdb.terms.term_id\twpdb.term_taxonomy.term_taxonomy_id
# input file format s/ /_/g
inputList="/PATH/TO/LIST"
outFile="/PATH/TO/OUTFILE.sql"
thisDB="WORDPRESS_DATABASE"
thisLinksTable="WORDPRESS_links"
thisTermRELTable="WORDPRESS_term_relationships"
thisTermTAXOTable="WORDPRESS_term_taxonomy"
# change these as appropriate. Other changes can be made in the "while read" loop
linkUser="2"
linkTarget="_LINKTARGET"
 
# if you are replacing all existing data
# remove the comments from the DROP statement
 
insertLinks="INSERT INTO \`$thisLinksTable\` (\`link_url\`, \`link_name\`, \`link_image\`, \`link_target\`, \`link_category\`, \`link_description\`, \`link_visible\`, \`link_owner\`, \`link_rating\`, \`link_updated\`, \`link_rel\`, \`link_notes\`, \`link_rss\`) VALUES"
linkDate=$(date --rfc-3339 seconds | sed -e "s/-.....$//")
 
### FUNCTIONS
 
### MAIN
 
cat > $outFile < < EOF
-- SQL Script
 
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
 
--
-- Database: \`$thisDB\`
--
 
-- --------------------------------------------------------
 
--
-- Table structure for table \`$thisLinksTable\`
--
 
-- DROP TABLE IF EXISTS \`$thisLinksTable\`;
CREATE TABLE IF NOT EXISTS \`$thisLinksTable\` (
 \`link_id\` bigint(20) NOT NULL auto_increment,
 \`link_url\` varchar(255) NOT NULL default '',
 \`link_name\` varchar(255) NOT NULL default '',
 \`link_image\` varchar(255) NOT NULL default '',
 \`link_target\` varchar(25) NOT NULL default '',
 \`link_category\` bigint(20) NOT NULL default '0',
 \`link_description\` varchar(255) NOT NULL default '',
 \`link_visible\` varchar(20) NOT NULL default 'Y',
 \`link_owner\` int(11) NOT NULL default '1',
 \`link_rating\` int(11) NOT NULL default '0',
 \`link_updated\` datetime NOT NULL default '0000-00-00 00:00:00',
 \`link_rel\` varchar(255) NOT NULL default '',
 \`link_notes\` mediumtext NOT NULL,
 \`link_rss\` varchar(255) NOT NULL default '',
 PRIMARY KEY  (\`link_id\`),
 KEY \`link_category\` (\`link_category\`),
 KEY \`link_visible\` (\`link_visible\`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;
 
EOF
 
# generate the SQL statements
while read thisURL thisNAME thisCAT thisTERMTAXO; do
	thisNAME=$(echo $thisNAME | sed -e "s/_/ /g"); # replace spaces in descriptions
	#echo -e "thisURL= $thisURL  thisNAME = $thisNAME  thisCAT = $thisCAT   thisTERMTAXO = $thisTERMTAXO\n"; # debug
	echo "${insertLinks} ('$thisURL', '$thisNAME', '', '$linkTarget', '$thisCAT', '', 'Y', '$linkUser', '0', '$linkDate', '', '', '');" >> $outFile
	echo "INSERT INTO ${thisTermRELTable} (\`object_id\`, \`term_taxonomy_id\`) VALUES (LAST_INSERT_ID(), '${thisTERMTAXO}');" >> $outFile
	echo "UPDATE ${thisTermTAXOTable} SET count=count+1 WHERE term_taxonomy_id = ${thisTERMTAXO};" >> $outFile
done < $inputList
 

Also filed under ♠ comments: ∅