Posts Tagged script

How to do a global search and replace within a DB across all Tables

As part of my take over the world campaign I’ve run into the need to automatically do a global Search and replace within a Mysql Database covering all tables.  For this you need a little php magic and luckily enough I found just that magic right here.

I’ll save you the looking up time and paste it below but make sure to thank to the original author on their blog:

 

<?php
// Find and replace facility for complete MySQL database
//
// Written by Mark Jackson @ MJDIGITAL
// Can be used by anyone - but give me a nod if you do!
// http://www.mjdigital.co.uk/blog

// SEARCH FOR
$search        = '**__FIND_THIS__**';

// REPLACE WITH
$replace    = '**__REPLACE_WITH_THIS__**'; // (used if queryType below is set to 'replace')

// DB Details
$hostname = "__DB_HOST__";
$database = "__DB_DATABASE__";
$username = "__DB_USER__";
$password = "__DB_PASSWORD__";

// Query Type: 'search' or 'replace'
$queryType = 'replace';

// show errors (.ini file dependant) - true/false
$showErrors = true;

//////////////////////////////////////////////////////
//
//        DO NOT EDIT BELOW
//
//////////////////////////////////////////////////////

if($showErrors) {
error_reporting(E_ALL);
ini_set('error_reporting', E_ALL);
ini_set('display_errors',1);
}

// Create connectio to DB
$MJCONN = mysql_pconnect($hostname, $username, $password) or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db($database,$MJCONN);

// Get list of tables
$table_sql = 'SHOW TABLES';
$table_q = mysql_query($table_sql,$MJCONN) or die("Cannot Query DB: ".mysql_error());
$tables_r = mysql_fetch_assoc($table_q);
$tables = array();

do{
$tables[] = $tables_r['Tables_in_'.strtolower($database)];
}while($tables_r = mysql_fetch_assoc($table_q));

// create array to hold required SQL
$use_sql = array();

$rowHeading = ($queryType=='replace') ?
'Replacing \''.$search.'\' with \''.$replace.'\' in \''.$database."'\n\nSTATUS    |    ROWS AFFECTED    |    TABLE/FIELD    (+ERROR)\n"
: 'Searching for \''.$search.'\' in \''.$database."'\n\nSTATUS    |    ROWS CONTAINING    |    TABLE/FIELD    (+ERROR)\n";

$output = $rowHeading;

$summary = '';

// LOOP THROUGH EACH TABLE
foreach($tables as $table) {
// GET A LIST OF FIELDS
$field_sql = 'SHOW FIELDS FROM '.$table;
$field_q = mysql_query($field_sql,$MJCONN);
$field_r = mysql_fetch_assoc($field_q);

// compile + run SQL
do {
$field = $field_r['Field'];
$type = $field_r['Type'];

switch(true) {
// set which column types can be replaced/searched
case stristr(strtolower($type),'char'): $typeOK = true; break;
case stristr(strtolower($type),'text'): $typeOK = true; break;
case stristr(strtolower($type),'blob'): $typeOK = true; break;
case stristr(strtolower($field_r['Key']),'pri'): $typeOK = false; break; // do not replace on primary keys
default: $typeOK = false; break;
}

if($typeOK) { // Field type is OK ro replacement
// create unique handle for update_sql array
$handle = $table.'_'.$field;
if($queryType=='replace') {
$sql[$handle]['sql'] = 'UPDATE '.$table.' SET '.$field.' = REPLACE('.$field.',\''.$search.'\',\''.$replace.'\')';
} else {
$sql[$handle]['sql'] = 'SELECT * FROM '.$table.' WHERE '.$field.' REGEXP(\''.$search.'\')';
}

// execute SQL
$error = false;
$query = @mysql_query($sql[$handle]['sql'],$MJCONN) or $error = mysql_error();
$row_count = @mysql_affected_rows() or $row_count = 0;

// store the output (just in case)
$sql[$handle]['result'] = $query;
$sql[$handle]['affected'] = $row_count;
$sql[$handle]['error'] = $error;

// Write out Results into $output
$output .= ($query) ? 'OK        ' : '--        ';
$output .= ($row_count>0) ? '<strong>'.$row_count.'</strong>            ' : '<span style="color:#CCC">'.$row_count.'</span>            ';
$fieldName = '`'.$table.'`.`'.$field.'`';
$output .= $fieldName;
$erTab = str_repeat(' ', (60-strlen($fieldName)) );
$output .= ($error) ? $erTab.'(ERROR: '.$error.')' : '';

$output .= "\n";
}
}while($field_r = mysql_fetch_assoc($field_q));
}

// write the output out to the page
echo '<pre>';
echo $output."\n";
echo '<pre>';
?>

Tags: , , , , ,

Creating a custom slideshow for your wallpaper in ubuntu

So it seems that I’ve had a similar experience to a lot of people changing their wallpapers in ubuntu.  You’re scrolling through a bunch and in the list you notice this one called cosmos that doesn’t quite looks like the rest.  “Hmmm, what’s this?’ you think.  This looks like a bunch of images stacked on top of each other.  Could it be?  And yes, it is, it’s a multi-image slideshow that can have as your background that rotates on a schedule that you choose.  I immediately sat down and started figuring out how this thing works and in the end it was pretty simple, time consuming to setup as you have to input a ton of values into an xml file but simple.  I’m not going to go into huge detail about how it works if you want to read more head over to http://www.linuxjournal.com/content/create-custom-transitioning-background-your-gnome-228-desktop

So today I stumbled upon a motherload of amazing 3D space art at http://joejesus.deviantart.com and I go completely gaga over space art.. My wallpapers are all sci-fi scenes, I just can’t get over how breath taking some of these are.  So I raided the guys stash and ended up with around 30-40 new pieces for my wallpaper slideshow and like hell was I going to enter all of these in by hand so off I went searching for a nice little script that would do it for me.  I mean come on, one of the reasons I’m a linux user because I gave up the notion that time consuming repetitive tasks were something that you had to do by hand and sure enough I found a wonderful gentlemen over at the ubuntu forums who coded up a beauty of a script that worked like a charm.  So make sure to head over to : http://ubuntuforums.org/showthread.php?p=9578962 and give the guy a big thank you hug for saving you hours of work, and read the instructions on how to use it.

Here’s the script, just copy and paste this into a file, chmod it to 755 and you’re off to the races:

#!/bin/sh
#This script creates xml files that can act as dynamic wallpapers for Gnome by referring to multiple wallpapers
#Coded by David J Krajnik
if [ "$*" = "" ]; then
  echo "This script creates xml files that can act as dynamic backgrounds for Gnome by referring to multiple wallpapers";
  echo "Usage: mkwlppr.sh target-file.xml [duration] pic1 pic2 [pic3 .. picN]";
else
  files=$*;
  #Grab the name of the target xml file
  xmlfile=`echo $files | cut -d " " -f 1`;
  #remove the first item from $files
  files=`echo $files | sed 's/^\<[^ ]*\>//'`;
  if [ "`echo $xmlfile | grep '\.xml$'`" = "" ]; then
    echo "Your target file must be an XML file";
  else
    inputIsValid="true";
    firstItem=`echo $files | cut -d " " -f 1`;
    duration="1795.0";#set the default duration
    if [ "`echo $firstItem | grep '^[0-9]\+\.[0-9]\+$'`" != "" ]; then
      echo "The duration must be an integer";
      files=`echo $files | sed 's/^\<[^ ]*\>//'`;
      inputIsValid="";
    elif [ "`echo $firstItem | grep '^[0-9]\+$'`" != "" ]; then
      #If the item is a number, then use it as the duration for each wallpaper image
      duration="`expr $firstItem - 5`.0";
      #remove the duration from the list of files
      files=`echo $files | sed 's/^\<[^ ]*\>//'`;
    fi
    if [ "$files" = "" ]; then
      echo "You must enter image files to associate with the XML file";
    else
      for file in $files
      do
        if [ ! -f $file ]; then
	  echo "\"$file\" does not exist";
	  inputIsValid="";
        elif [ "`echo $file | sed 's/^.*\.\(jpg\|jpeg\|bmp\|png\|gif\|tif\|tiff\|jif\|jfif\|jp2\|jpx\|j2k\|j2c\)$//'`" != "" ]; then
	  echo "\"$file\" is not an image file";
	  inputIsValid="";
	fi
      done
      if [ $inputIsValid ]; then
        currDir=`pwd`;
        echo "<background>" >> $xmlfile
        echo "  <starttime>\n    <year>2009</year>\n    <month>08</month>\n    <day>04</day>" >> $xmlfile;
        echo "    <hour>00</hour>\n    <minute>00</minute>\n    <second>00</second>\n  </starttime>" >> $xmlfile;
        echo "  <!-- This animation will start at midnight. -->" >> $xmlfile;
        firstFile=`echo $files | cut -d " " -f 1`;#grab the first item
        if [ "`echo $firstFile | sed 's/\(.\).*/\1/'`" != "/" ]; then
          #If the first character in the filename is not '/', then it is a relative path and must have the current directory's path appended
          firstFile="$currDir/$firstFile";
        fi
        firstFile=`echo $firstFile | sed 's/[^/]\+\/\.\.\/\?//g'`;#Remove occurrences of ".." from the filepath
        files=`echo $files | sed 's/^\<[^ ]*\>//'`;#remove the first item
        prevFile=$firstFile;
        currFile="";
        #TODO add absolute path to the filenames
        #if $currFile =~ "^/.*" then the file needs to path appended
        echo "  <static>\n    <duration>$duration</duration>\n    <file>$firstFile</file>\n  </static>" >> $xmlfile;
        for currFile in $files
        do
          if [ "`echo $currFile | sed 's/\(.\).*/\1/'`" != "/" ]; then
            #If the first character in the filename is not '/', then it is a relative path and must have the current directory's path appended
            currFile="$currDir/$currFile";
          fi
          currFile=`echo $currFile | sed 's/[^/]\+\/\.\.\/\?//g'`;#Remove occurrences of ".." from the filepath
          echo "  <transition>\n    <duration>5.0</duration>\n    <from>$prevFile</from>\n    <to>$currFile</to>\n  </transition>" >> $xmlfile;
          echo "  <static>\n    <duration>$duration</duration>\n    <file>$currFile</file>\n  </static>" >> $xmlfile;
          prevFile=$currFile;
        done
        echo "  <transition>\n    <duration>5.0</duration>\n    <from>$currFile</from>\n    <to>$firstFile</to>\n  </transition>" >> $xmlfile;
        echo "</background>" >> $xmlfile;
      fi
    fi
  fi
fi

Tags: , , , , , , , , , ,

Your own free content spinner shell script in bash (fTW!)

So today I set out to modify my massive unique tweet and content generator for my Netsyphon network(ie: hotel-kelowna, innkelowna.com, kelownasrestaurants.com, etc etc) and add an inline content spinner to it.  To date there are a lot of search/replaces happening but they are all one time replaces and reference external files for the replace values..  Usually there were only one or two possible s/r results per line so I could handle it easily with gsub.

But now I wanted to be able to take a single article with a ton of spinnable content and have a script generate 10-20-100 different versions of this article.  I would have a sentence where 80% of the content was spinnable with infinite amount of results.  I could handle this with the old method but it would require creating a ton of external files for each search/replace possibility which would get insane.  Instead I knew that there had to be a relatively simple method with bash to make this happen, and much to my delight the boys over at #bash on irc.freenode came to the rescue

Big props out to \amethyst and geirha, my two heroes for the day.  Some useful info from #bash as well:

FAQ: http://mywiki.wooledge.org/BashFAQ | Guide: http://mywiki.wooledge.org/BashGuide | ref: http://tinyurl.com/txlv | http://bash-hackers.org/wiki/ | USE MORE QUOTES!: http://www.grymoire.com/Unix/Quote.html | Scripts and more: http://www.shelldorado.com/

And without further ado here are the scripts that were created by these two gents.

http://pastebin.com/xNgKaR2c:

FAQ: http://mywiki.wooledge.org/BashFAQ | Guide: http://mywiki.wooledge.org/BashGuide | ref: http://tinyurl.com/txlv | http://bash-hackers.org/wiki/ | USE MORE QUOTES!: http://www.grymoire.com/Unix/Quote.html | Scripts and more: http://www.shelldorado.com/

And without further ado here are the scripts that were created by these two gents.

The first by Geirha, is setup to read from an external file , and if not will take the input from stdin

http://pastebin.com/xNgKaR2c:

spinner_thingy ()
{
while read -r -d ‘{‘; do
printf “%s” “$REPLY”;
IFS=’|’ read -r -d ‘}’ -a words;
n=${#words[@]};
((n)) && printf “%s” “${words[RANDOM%n]}”;
done < “${1:-/dev/stdin}”;
printf “%s” “$REPLY”
}

The second by\amethyst can be found here: http://pastebin.com/tAXJ04L6

I went ahead and replaced the example line at the top with a reference to a filename

line=”$(<test.txt)”
makesentence ()
{
local line=”$1″
while [[ $line = *{*}* ]]; do
local -a choices
local prefix=${line%%{*} rest=${line#*{}
local choice=${rest%%\}*}
line=${rest#*\}}
IFS=’|’ read -a choices <<< “$choice”
[[ $choice = *"|" ]] && choices+=( “” )
printf “%s” “$prefix”
if (( ${#choices[@]} )); then
printf “%s” “${choices[RANDOM % ${#choices[@]}]}”
fi
done
printf “%s\n” “$line”
}
makesentence “$line”

So there you have it.. 30 minutes in IRC and the helpful boys over at #bash have me right as rain.. Again a big shout out, these guys have been integral in helping me with all of my scripting in this project.. I would have jack if it wasn’t for them.

As a parting gift I also found a way to do this with php, but alas I wanted a shell script that I could easily integrate into the mothership so it was all one nice neat package.. But for those php guys here it is:

<?php
$text = "The {quick|slow|reasonably paced} {brown|green|blue|pink} {fox|goat|rat|camel}
{jumped|walked|hopped} {over|past|under} the {lazy|tired|boring} {dog|cat|stoat}";
$count = 0;
while ($count++ < 100){
echo Spin($text);
echo "<br />";
}
?>

Now go generate some content!

Tags: , , , , , , , ,

Page optimized by WP Minify WordPress Plugin