This script is used to migrate SQLite databases from RS TV Show Tracker <2.1 installations to the new binary format used in >2.2.

migrate_tvshowtracker_sqlite.php

<?
set_time_limit(0);
//define(DEBUG, true);

// Make sure TVShows.db3 exists near this PHP file. If not, edit accordingly.
$utn = 'TVShows.db3';

// This is the directory, where the new database will be dumped.
// Copy this "db" folder to the root of the software, replacing any previous "db" folder leftover.
mkdir('db');

$db = new PDO('sqlite:'.$utn);

ob_start();
print s();
$datas = $db->query('select * from settings')->fetchAll(PDO::FETCH_ASSOC);
for($i = 0; $i < count($datas); $i++){
	if($datas[$i]['key'] == 'uid'){
		$datas[$i]['key'] = 'uuid';
	}
	if($datas[$i]['key'] == 'last update'){
		$datas[$i]['key'] = 'update';
	}
	if($datas[$i]['key'] == 'Last Sync' || $datas[$i]['key'] == 'Version'){
		unset($datas[$i]);
	}
}
print v(count($datas));
foreach($datas as $data){
	print p($data['key']);
	print p($data['value']);
}
$ob = ob_get_contents();
ob_end_clean();
file_put_contents('db/conf', $ob);
flush();

$shows = $db->query('select * from tvshows');
foreach($shows as $show){
	$dir = /*$show['showid'].'-'.*/slug($show['name']).'/';
	mkdir('db/'.$dir);
	
	ob_start();
	print s();
	print p($show['name']);
	print p($gid = $db->query('select value from showdata where key = "grabber" and showid = '.$show['showid'])->fetchObject()->value);
	print p($db->query('select value from showdata where key = "'.$gid.'.id" and showid = '.$show['showid'])->fetchObject()->value);
	print p($db->query('select value from showdata where key = "descr" and showid = '.$show['showid'])->fetchObject()->value);
	print p($db->query('select value from showdata where key = "genre" and showid = '.$show['showid'])->fetchObject()->value);
	print p($db->query('select value from showdata where key = "cover" and showid = '.$show['showid'])->fetchObject()->value);
	print b($db->query('select value from showdata where key = "airing" and showid = '.$show['showid'])->fetchObject()->value);
	print p($db->query('select value from showdata where key = "airtime" and showid = '.$show['showid'])->fetchObject()->value);
	print p($db->query('select value from showdata where key = "airday" and showid = '.$show['showid'])->fetchObject()->value);
	print p($db->query('select value from showdata where key = "network" and showid = '.$show['showid'])->fetchObject()->value);
	print c($db->query('select value from showdata where key = "runtime" and showid = '.$show['showid'])->fetchObject()->value);
	print p($db->query('select value from showdata where key = "timezone" and showid = '.$show['showid'])->fetchObject()->value);
	print p($db->query('select value from showdata where key = "lang" and showid = '.$show['showid'])->fetchObject()->value);
	print p($db->query('select value from showdata where key = "url" and showid = '.$show['showid'])->fetchObject()->value);
	$eps = $db->query('select * from episodes where showid = '.$show['showid'])->fetchAll(PDO::FETCH_ASSOC);
	print v(count($eps));
	foreach($eps as $ep){
		print c($ep['season']);
		if ($ep['episode'] < 255) {
			print c($ep['episode']);
		} else {
			print c(255);
			print c($ep['episode'] - 255);
		}
		print u($ep['airdate']);
		print p($ep['name']);
		print p($ep['descr']);
		print p($ep['pic']);
		print p($ep['url']);
	}
	$ob = ob_get_contents();
	ob_end_clean();
	file_put_contents('db/'.$dir.'info', $ob);
	flush();
	
	ob_start();
	print s();
	$datas = array(array('key' => 'showid', 'value' => $show['showid']), array('key' => 'rowid', 'value' => $show['rowid']));
	$dataz = $db->query('select * from showdata where key like "title%" and showid = '.$show['showid'])->fetchAll(PDO::FETCH_ASSOC);
	foreach($dataz as $data){
		$datas[] = $data;
	}
	if (!empty($show['release'])) {
		$datas[] = array('key' => 'regex', 'value' => $show['release']);
	}
	print v(count($datas));
	foreach($datas as $data){
		print p($data['key']);
		print p($data['value']);
	}
	$ob = ob_get_contents();
	ob_end_clean();
	file_put_contents('db/'.$dir.'conf', $ob);
	flush();
	
	ob_start();
	print s();
	$saws = $db->query('select * from tracking where showid = '.$show['showid'])->fetchAll(PDO::FETCH_ASSOC);
	print v(count($saws));
	foreach($saws as $saw){
		$epid = $saw['episodeid']-(100*1000*$show['showid']);
		$sn = floor($epid / 1000);
		$ep = $epid - ($sn * 1000);
		print c($sn);
		if ($ep < 255) {
			print c($ep);
		} else {
			print c(255);
			print c($ep - 255);
		}
	}
	$ob = ob_get_contents();
	ob_end_clean();
	file_put_contents('db/'.$dir.'seen', $ob);
	flush();
}

function p($txt){ // string
	if (defined('DEBUG')) return strlen($txt).$txt;
	else return encode_7bhm(strlen($txt)).$txt;
}

function b($txt){ // bool
	if (defined('DEBUG')) return $txt == 'True' ? '-True-' : '-False-';
	else return $txt == 'True' ? chr(1) : chr(0);
}

function i($txt){ // 7-bit encoded int
	if (defined('DEBUG')) return '-b'.$txt.'-';
	else return encode_7bhm((int)$txt);
}

function u($txt){ // unsigned int
	if (defined('DEBUG')) return '-u'.$txt.'-';
	else return pack('V', (int)$txt);
}

function v($txt){ // unsigned short
	if (defined('DEBUG')) return '-s'.$txt.'-';
	else return pack('v', (int)$txt);
}

function c($txt){ // unsigned byte
	if (defined('DEBUG')) return '-c'.$txt.'-';
	else return pack('C', (int)$txt);
}

function s(){ // header
	return c(1).u(time());
}

function encode_7bhm($int) {
	if ($int==0) return chr(0);
	$ret = "";
	while($int != 0) {
		$high = floor($int / 128);
		$low = $int - ($high * 128);
		if ($int > 0) {
			if ($high > 0) { $low = $low + 128; }
			$ret .= chr($low);
		}
		$int = $high;
	}
	return $ret;
}

function slug($name){
	$name = strtolower(iconv('UTF-8', 'ASCII//TRANSLIT', html_entity_decode($name)));
	$name = preg_replace('/\s\(20\d{2}\)/', '', $name);
	$name = preg_replace('/ & /', ' and ', $name);
	$name = preg_replace('/[\'`’\._]/', '', $name);
	$name = preg_replace('/[^a-z0-9]/', ' ', $name);
	$name = preg_replace('/\s\s*/', '-', trim($name));
	return $name;
}
?>