Skip to content

Instantly share code, notes, and snippets.

@TimWolla

TimWolla/messageParser.php

Created August 14, 2014 19:40
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save TimWolla/f7fca2018190b84c885b to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save TimWolla/f7fca2018190b84c885b to your computer and use it in GitHub Desktop.
Download ZIP
Raw
messageParser.php
<?php
include('/srv/www/de/bisaboard/www/wcf/config.inc.php');
$mysqli = new mysqli($dbHost, $dbUser, $dbPassword, $dbName);
if ($mysqli->connect_errno) {
printf("Connect failed: %s\n", $mysqli->connect_error);
exit();
}
$mysqli->query("SET NAMES 'utf8'");
$replaceStmt = $mysqli->prepare('UPDATE wbb1_post SET message = ? WHERE postID = ?');
$selectStmt = $mysqli->prepare("SELECT postID,message FROM wbb1_post WHERE postID BETWEEN ? AND ? + 999");
$postID = 0;
$new = '';
$start = 0;
$old = '';
$replaceStmt->bind_param('si', $new, $postID);
$selectStmt->bind_param('ii', $start, $start);
$selectStmt->bind_result($postID, $old);
for (; $start <= 5800000; $start += 1000) {
echo $start."\n";
$result = $selectStmt->execute();
if (!$result) exit('Boom at '.$i);
$selectStmt->store_result();
while ($selectStmt->fetch()) {
$new = fixMessage($postID, $old);
if (sha1($old) === sha1($new)) continue;
file_put_contents('/tmp/old', $old);
file_put_contents('/tmp/new', $new);
echo "Please confirm rewrite of ".$postID;
fgets(STDIN, 1024);
$replaceStmt->execute();
}
}
$mysqli->close();
function fixMessage($postID, $message) {
if (empty(trim($message))) return;
// Fix quotes
$newMessage = preg_replace_callback('~\[quote=[\'"]?.*?[\'"]?\s*,\s*[\'"]?(.+?)[\'"]?s*\]~i', function($quote) {
$url = preg_replace_callback('~(?P<protocol>\w+://)?(?:/+)?(.+)~', function($matches) {
if (empty($matches['protocol'])) { // relative URL
$matches[2] = preg_replace_callback('~index\.php/?\?page=Thread&postID=(?<postID>\d+)(?:&(?P<params>[^\s#]*))?(?:#.*)?~i', function($matchedURL) {
$url = 'http://www.bisaboard.de/index.php/Thread/?postID='.$matchedURL['postID'];
if (isset($matchedURL['params']) && !empty($matchedURL['params'])) {
$url .= '&' . $matchedURL['params'];
}
return $url . '#post'.$matchedURL['postID'];
}, $matches[2]);
$matches[2] = preg_replace_callback('~index\.php/?\?postID=(\d+)&page=Thread(?:&(?P<params>[^\s#]*))?(?:#.*)?~i', function($matchedURL) {
$url = 'http://www.bisaboard.de/index.php/Thread/?postID='.$matchedURL['postID'];
if (isset($matchedURL['params']) && !empty($matchedURL['params'])) {
$url .= '&' . $matchedURL['params'];
}
return $url . '#post'.$matchedURL['postID'];
}, $matches[2]);
$matches[2] = preg_replace_callback('~index\.php/?\?page=PMView&pmID=(?P<pmID>\d+)(?:&(?P<params>[^\s#]*))?(?:#.*)?~i', function($matchedURL) {
$url = 'http://www.bisaboard.de/index.php/Conversation/?messageID='.$matchedURL['pmID'];
if (isset($matchedURL['params']) && !empty($matchedURL['params'])) {
$url .= '&' . $matchedURL['params'];
}
return $url . '#message'.$matchedURL['pmID'];
}, $matches[2]);
$matches[2] = preg_replace_callback('~index\.php/?\?pmID=(\d+)&page=PMView(?:&(?P<params>[^\s#]*))?(?:#.*)?~i', function($matchedURL) {
$url = 'http://www.bisaboard.de/index.php/Conversation/?messageID='.$matchedURL['pmID'];
if (isset($matchedURL['params']) && !empty($matchedURL['params'])) {
$url .= '&' . $matchedURL['params'];
}
return $url . '#message'.$matchedURL['pmID'];
}, $matches[2]);
return $matches[2];
}
else {
return $matches[0];
}
}, $quote[1]);
return str_replace($quote[1], $url, $quote[0]);
}, $message);
// Fix URL BBCodes
$newMessage = preg_replace_callback('~\[url(?:=[\'"]?(?P<url>.+?)[\'"]?)?\](?P<title>.+?)\[/url\]~i', function($matches) {
if (empty($matches['url']) && !empty($matches['title'])) {
$url = $matches['title'];
$title = false;
}
else if (!empty($matches['url'])) {
$url = $matches['url'];
$title = $matches['title'];
}
else if (empty($matches['url']) && empty($matches['title'])) {
return $matches[0];
}
$url = preg_replace('~https?://(?:www\.)?bisaboard\.de/+~', 'http://www.bisaboard.de/', $url);
$url = preg_replace('~https?://(?:www\.)?bisafans\.de/bb/+~', 'http://www.bisaboard.de/', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)(board[0-9]+\-[^/\.]+/)*board([0-9]+)\-([^/\.]+)/([0-9]+)\-([^/\.]*)/last\-post\.html(?:\?(?P<params>[^\s]*))?(?:#?P<anchor>(.*))?~i', '$1/index.php/Thread/$5-$6/?action=lastPost', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)(board[0-9]+\-[^/\.]+/)*board([0-9]+)\-([^/\.]+)/([0-9]+)\-([^/\.]*)/first\-new\-post\.html(?:\?(?P<params>[^\s]*))?(?:#?P<anchor>(.*))?~i', '$1/index.php/Thread/$5-$6/?action=firstNew', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)(board[0-9]+\-[^/\.]+/)*board([0-9]+)\-([^/\.]+)/p([0-9]+)\-([^/\.]*)/?(?:\?(?P<params>[^\s#]*))?(?:#.*)?~i', '$1/index.php/Thread/?postID=$5&$args#post$5', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)(board[0-9]+\-[^/\.]+/)*board([0-9]+)\-([^/\.]+)/([0-9]+)\-([^/\.]*)/index([0-9]+)\.html(?:\?(?P<params>[^\s]*))?(?:#?P<anchor>(.*))?~i', '$1/index.php/Thread/$5-$6/?pageNo=$7', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)(board[0-9]+\-[^/\.]+/)*board([0-9]+)\-([^/\.]+)/([0-9]+)\-([^/\.]*)/?(?:\?(?P<params>[^\s]*))?(?:#?P<anchor>(.*))?~i', '$1/index.php/Thread/$5-$6/', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)(board[0-9]+\-[^/\.]+/)*board([0-9]+)\-([^/\.]+)/index([0-9]+)\.html(?:\?(?P<params>[^\s]*))?(?:#?P<anchor>(.*))?~i', '$1/index.php/Board/$3-$4/?pageNo=5', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)(board[0-9]+\-[^/\.]+/)*board([0-9]+)\-([^/\.]+)/?(?:\?(?P<params>[^\s]*))?(?:#?P<anchor>(.*))?~i', '$1/index.php/Board/$3-$4/', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)index\.html$(?:\?(?P<params>[^\s]*))?(?:#?P<anchor>(.*))?~i', '$1/index.php/BoardList/', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)user/([0-9]+)/([^/\.]*)/?(?:\?(?P<params>[^\s]*))?(?:#?P<anchor>(.*))?~i', '$1/index.php/User/$2-$3/', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)user/gallery([0-9]+)\-([^/\.]*)/?(?:\?(?P<params>[^\s]*))?(?:#?P<anchor>(.*))?~i', '$1/galerie/index.php/ImageList/?userID=$2', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)user/gallery([0-9]+)\-([^/\.]*)/photo([0-9]+)\-([^/\.]*)/?(?:\?(?P<params>[^\s]*))?(?:#?P<anchor>(.*))?~i', '$1/galerie/index.php/Image/$4-$5/', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)user/gallery([0-9]+)\-([^/\.]*)/album([0-9]+)\-([^/\.]*)/?(?:\?(?P<params>[^\s]*))?(?:#?P<anchor>(.*))?~i', '$1/galerie/index.php/Album/$4-$5/', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)gallery/category([0-9]+)\-([^/\.]*)/?(?:\?(?P<params>[^\s]*))?(?:#?P<anchor>(.*))?~i', '$1/galerie/index.php/Gallery/', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)gallery/category([0-9]+)\-([^/\.]*)/?(?:\?(?P<params>[^\s]*))?(?:#?P<anchor>(.*))?~i', '$1/galerie/index.php/Gallery/', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)index\.php/?\?page=Portal(?:(\?|&)(?P<params>[^\s]*))?(?:#?P<anchor>(.*))?~i', '$1/index.php/Dashboard/?$args', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)index\.php/?\?page=MemberList(?:(\?|&)(?P<params>[^\s]*))?(?:#?P<anchor>(.*))?~i', '$1/index.php/MemberList/?$args', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)index\.php/?\?page=Rules(?:(\?|&)(?P<params>[^\s]*))?(?:#?P<anchor>(.*))?~i', '$1/index.php/Terms/?$args', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)index\.php/?\?page=Chat(?:(\?|&)(?P<params>[^\s]*))?(?:#?P<anchor>(.*))?~i', '$1/chat/index.php/Chat/?$args', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)index\.php/?\?page=Calender(:?Month)?(?:(\?|&)(?P<params>[^\s]*))?(?:#?P<anchor>(.*))?~i', '$1/kalender/index.php/Calender/?$args', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)index\.php/?\?page=CalenderWeek(?:(\?|&)(?P<params>[^\s]*))?(?:#?P<anchor>(.*))?~i', '$1/kalender/index.php/Weekly/?$args', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)index\.php/?\?page=CalenderEvents(?:(\?|&)(?P<params>[^\s]*))?(?:#?P<anchor>(.*))?~i', '$1/kalender/index.php/UpcomingEventList/?$args', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)index\.php/?\?page=UserGalleryOverview(?:(\?|&)(?P<params>[^\s]*))?(?:#?P<anchor>(.*))?~i', '$1/galerie/index.php/Gallery/?$args', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)index\.php/?\?page=UserGalleryAlbumList(?:(\?|&)(?P<params>[^\s]*))?(?:#?P<anchor>(.*))?~i', '$1/galerie/index.php/AlbumList/?$args', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)index\.php/?\?page=Thread&postID=(\d+)(?:&(?P<params>[^\s#]*))?(?:#.*)?~i', '$1/index.php/Thread/?postID=$2&$args#post$2', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)index\.php/?\?postID=(\d+)&page=Thread(?:&(?P<params>[^\s#]*))?(?:#.*)?~i', '$1/index.php/Thread/?postID=$2&$args#post$2', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)index\.php/?\?page=User&userID=(\d+)(?:&(?P<params>[^\s#]*))?(?:#.*)?~i', '$1/index.php/User/$2/?$args', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)index\.php/?\?page=PMView&pmID=(\d+)(?:&(?P<params>[^\s#]*))?(?:#.*)?~i', '$1/index.php/Conversation/?messageID=$2&$args#message$2', $url);
$url = replaceURL('~(https?://www\.bisaboard\.de/)index\.php/?\?pmID=(\d+)&page=PMView(?:&(?P<params>[^\s#]*))?(?:#.*)?~i', '$1/index.php/Conversation/?messageID=$2&$args#message$2', $url);
if ($title) {
return "[url='".$url."']".$title."[/url]";
}
else {
return "[url]".$url."[/url]";
}
}, $newMessage);
// $newMessage = preg_replace('~\[u\](.*?)\[/u\]~', '[i]$1[/i]', $newMessage);
// Replace user BBCode
$newMessage = preg_replace('~\[user=[\'"]?(\d+)[\'"]?\](.*?)\[/user\]~', '[url=http://www.bisaboard.de/index.php/User/$1-$2/]$2[/url]', $newMessage);
// Fix tables
$matches = null;
preg_match_all('~\[table(?:=[\'"]?(?P<attributes>.*?)[\'"]?)\](?<table>.*?)\[\/table\]~is', $newMessage, $matches);
foreach ($matches['table'] as $key => $table) {
$newMessage = str_replace($matches[0][$key], parseOkanTable($matches['attributes'][$key], $table, $matches[0][$key]), $newMessage);
}
return $newMessage;
}
function replaceURL($pattern, $replacement, $subject) {
return preg_replace_callback($pattern, function($matches) use ($replacement) {
// print_r($matches);
if (!empty($matches[1])) $matches[1] = 'http://www.bisaboard.de';
$url = preg_replace_callback('~\$(\d)~', function($replacementMatches) use ($matches) {
if (!isset($matches[$replacementMatches[1]])) {
echo "WARN";
print_r($matches);
print_r($replacementMatches);
}
return $matches[$replacementMatches[1]];
} , $replacement);
if (isset($matches['params']) && !empty($matches['params'])) {
if (preg_match('~(?:&|\?)\$args~', $url)) {
$url = str_replace(
array('?$args', '&$args'),
array('?' . $matches['params'], '&' . $matches['params']),
$url
);
}
else if (preg_match('~/?\?[^\s]*?$~', $url)) {
$url .= '&' . $matches['params'];
}
else {
$url .= '?' . $matches['params'];
}
}
else {
$url = str_replace(array('?$args', '&$args'), '', $url);
}
if (isset($matches['anchor']) && !empty($matches['anchor'])) {
$url .= '#' . $matches['anchor'];
}
return $url;
}, $subject);
}
function parseOkanTable($tableHead = null, $tableBody = null, $original) {
$tableHeadElements = null;
// get table head (th) elements
if ($tableHead !== null && $tableHead != '') $tableHeadElements = explode(',', $tableHead);
// get table data (td) elements
$tableDataElements = preg_split('/\[\*\]/', strim($tableBody), -1, PREG_SPLIT_NO_EMPTY);
// set counter for td elements
$counter = 0;
// remove <br /> elements
foreach ($tableDataElements as $key => $val) {
$tableDataElements[$key] = strim($val);
if ($tableDataElements[$key] == '<br />') {
unset($tableDataElements[$key]);
}
}
if (count($tableHeadElements) != 0 && count($tableDataElements) != 0) {
$tableHeadElementsTemp = $tableDataElementsTemp = '';
foreach ($tableHeadElements as $tableHeadElement) {
// $tableHeadElementsTemp .= "\t\t[td]\n\t\t\t[b]".strim($tableHeadElement)."[/b]\n\t\t[/td]\n";
$tableHeadElementsTemp .= "\t\t[td][b]".strim($tableHeadElement)."[/b][/td]\n";
}
foreach ($tableDataElements as $tableDataElement) {
if (($counter % count($tableHeadElements)) == 0) {
$tableDataElementsTemp .= "\t[tr]\n";
}
// $tableDataElementsTemp .= "\t\t[td]\n\t\t\t".strim($tableDataElement)."\n\t\t[/td]\n";
$tableDataElementsTemp .= "\t\t[td]".strim($tableDataElement)."[/td]\n";
$counter++;
if ($counter % count($tableHeadElements) == 0 || $counter == count($tableDataElements)) $tableDataElementsTemp .= "\t[/tr]\n";
}
return "[table]\n\t[tr]\n".$tableHeadElementsTemp."\t[/tr]\n".$tableDataElementsTemp."[/table]\n";
}
else {
return '[code="BBCode","Defekte Tabelle"]'.$original.'[/code]';
}
}
function strim($text) {
$text = preg_replace('/^(\s|'.chr(226).chr(128).chr(175).'|'.chr(194).chr(160).')+/', '', $text);
$text = preg_replace('/(\s|'.chr(226).chr(128).chr(175).'|'.chr(194).chr(160).')+$/', '', $text);
return $text;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment