Last year my blog got infected with a virus and rather than pay my hosting provider to clean it, I decided to turn it off.  Unfortunately I used to use it for reference because I knew I had some random process documented.  Some of the guys at work also had it bookmarked to reference historical articles about some custom piece of code or full stack site that I created.  I also wanted to start writing again to document my studies.

I took a backup of the site from my old hosting provider, but it would not import into WordPress.  I also had a DB dump of the site, so I wrote this program to extract my old posts.  I will slowly re-post my old articles.  Some posts may look good, others will not.  While I have most of my old graphics I do not have all of them, so some may be missing bits and pieces.  I apologize for that.

The process to get my old posts back was to first understand the DB format, then decide how to go about it.  I actually wrote a couple of different scripts, one that dumped all of the posts with date, title and content into one text file.  The second one was to put each article into their own file.  This made it easier to figure out each post.

One thing that I am still struggling with is the formatting for code. Formatting is so important for code, yet I am still learning how this new interface formats and am fighting to make the code look good. I am loosing the battle, but as I continue to work with this editor I hope to eventually win the war.

#!/usr/bin/perl

# 2016-11-12
# Jud Bishop

#use chainrin_wrd01;
#describe wp_posts;
#select ID from wp_posts;
#select post_date from wp_posts where id=128;
#select post_date, post_title, post_content from wp_posts where id=128;

#Database changed
#MariaDB [chainrin_wrd01]> describe wp_posts;
#+-----------------------+---------------------+------+-----+---------------------+----------------+
#| Field | Type | Null | Key | Default | Extra |
#+-----------------------+---------------------+------+-----+---------------------+----------------+
#| ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
#| post_author | bigint(20) unsigned | NO | MUL | 0 | |
#| post_date | datetime | NO | | 0000-00-00 00:00:00 | |
#| post_date_gmt | datetime | NO | | 0000-00-00 00:00:00 | |
#| post_content | longtext | NO | | NULL | |
#| post_title | text | NO | | NULL | |
#| post_excerpt | text | NO | | NULL | |
#| post_status | varchar(20) | NO | | publish | |
#| comment_status | varchar(20) | NO | | open | |
#| ping_status | varchar(20) | NO | | open | |
#| post_password | varchar(20) | NO | | | |
#| post_name | varchar(200) | NO | MUL | | |
#| to_ping | text | NO | | NULL | |
#| pinged | text | NO | | NULL | |
#| post_modified | datetime | NO | | 0000-00-00 00:00:00 | |
#| post_modified_gmt | datetime | NO | | 0000-00-00 00:00:00 | |
#| post_content_filtered | longtext | NO | | NULL | |
#| post_parent | bigint(20) unsigned | NO | MUL | 0 | |
#| guid | varchar(255) | NO | | | |
#| menu_order | int(11) | NO | | 0 | |
#| post_type | varchar(20) | NO | MUL | post | |
#| post_mime_type | varchar(100) | NO | | | |
#| comment_count | bigint(20) | NO | | 0 | |
#+-----------------------+---------------------+------+-----+---------------------+----------------+
#

use strict;
use warnings;
use DBI;

my $dbh;
my $sql;
my $sth;
my $fh; #file handle

sub dbi_connect {
 $dbh = DBI->connect('dbi:mysql:dbname=chainrin_wrd01;host=127.0.0.1','chainring','',{AutoCommit=>1,RaiseError=>1,PrintError=>1}) || die "Error connecting: '$DBI::errstr'";
}

sub dbi_disconnect{
      $sth->finish;
      $dbh->disconnect;
}

sub sql_prepare {
     print "$sql\n";
     $sth = $dbh->prepare($sql) || die "Error preparing: $DBI::errstr";
}

sub sql_table_print {

my $result = $sth->execute || die "Error executing: $DBI::errstr";

# HEADER
 print "Field names: @{ $sth->{NAME} }\n";

# DATA
 while (my @data = $sth->fetchrow_array()) {
 my $date = $data[0];
 $date =~ s/\r//g;
 my $title = $data[1];
 $title =~ s/\r//g;
 my $content = $data[2];
 $content =~ s/\r//g;

# It's not pretty, but it's legible.
 my $filename = $title;
 $filename =~ s/\ /-/g;
 $filename =~ s/:/-/g;
 $filename =~ s/\>/-/g;
 $filename =~ s/\</-/g;
 $filename =~ s/\//-/g;

 open_file($filename);
   print $fh "$date\n";
   print $fh "$title\n";
   print $fh "$content\n";
   print $fh "\n";
 close_file();
 }

}

sub open_file {
 print "open_file\n";
 my $filename = shift;
 if ($filename eq ''){ $filename = "filename"; }
 print "$filename\n";
 $filename = "/tmp/Posts/" . $filename;
 open($fh, '>', $filename) || die "Unable to open file: $!";
}

sub close_file {
 close ($fh) || die "Unable to close file: $!";
}

# Main
dbi_connect();
$sql = "select post_date, post_title, post_content from wp_posts";
sql_prepare();
sql_table_print();
dbi_disconnect();