作業メモ（scorm_portal から NLportal 4.0 へのデータ移行）

問題バンク scorm_portal のコンテンツを、NLportal 4.0 へ移しました。問の形式が変更されるものがあるので、プログラムを書いて変換しながら移しました。作業用のコードが行方不明にならないように記録します(参考記事)。

最初に，同じデータを他のデータベースに複製して，その後複製された方の内容に修正を加えました。具体的には configfile.xml というファイルの修正です。

<?php

// scorm_portal のデータをNLportal4.0用に修正するプログラム。

//require_once './db_connect.php';

  // データベースに接続
  $DB = mysqli_connect( 'localhost', 'username', 'password', 'scorm_portal') or die(mysqli_connect_error());  
  mysqli_set_charset($DB, 'utf8');

  // データベースに接続
  $DB2 = mysqli_connect( 'localhost', 'username', 'password', 'NLportal40') or die(mysqli_connect_error());  
  mysqli_set_charset($DB2, 'utf8');

// コピー先($DB2)のデータベースの内容を削除する
$sql = "SHOW TABLES;";
$result = mysqli_query($DB2, $sql) or die(mysqli_error($DB2));
$rows = mysqli_num_rows($result);

echo "コピー先のTable ".$rows." 個を削除します\n";

for ($i = 0; $i < $rows; $i = $i + 1) {
	$row = mysqli_fetch_array($result);
	//var_dump($row[0]);
	$sql = "DROP TABLE `".$row[0]."`;";
	mysqli_query($DB2, $sql) or die(mysqli_error($DB2));
	echo "DROP ".$row[0]."\n";
}

//  コピーする

$sql = "SHOW TABLES;";
$result = mysqli_query($DB, $sql) or die(mysqli_error($DB));
$rows = mysqli_num_rows($result);

echo "Table ".$rows." 個をコピーします\n";

for ($i = 0; $i < $rows; $i = $i + 1) {
	$row = mysqli_fetch_array($result);
	//var_dump($row[0]);
	//$sql = "DROP TABLE `".$row[0]."`;";
	$a_table_name = "`".$row[0]."`";

	$sql = "CREATE TABLE NLportal40.$a_table_name LIKE scorm_portal.$a_table_name";
	mysqli_query($DB2, $sql) or die(mysqli_error($DB2));

	$sql = "INSERT INTO NLportal40.$a_table_name SELECT * FROM scorm_portal.$a_table_name";
	mysqli_query($DB2, $sql) or die(mysqli_error($DB2));

	echo "copy ".$row[0]."\n";
}

// author table 名だけを取得し、配列に入れる

$sql = "SHOW TABLES LIKE 'table_%';";
$result = mysqli_query($DB, $sql) or die(mysqli_error($DB));
$rows = mysqli_num_rows($result);

echo "author の table は".$rows." 個あります\n";

$table_ary = array();
$author_ary = array();

for ($i = 0; $i < $rows; $i = $i + 1) {
	$row = mysqli_fetch_array($result);
	$table_ary[] = $row[0];
	$author_ary[] = mb_eregi_replace('table_','',$row[0]);
}

$table_num = $rows;
//var_dump($table_ary);

// 作業フォルダーを用意する
$timestamp = makeTimeStamp( );
$head = "tmp".$timestamp;
$foldername = mb_convert_encoding($head, "UTF-8", "auto");
passthru("mkdir -p /var/www/html/temporary/$foldername/tmp/");

echo "作業フォルダー $foldername を作成\n";

// key の用意 以前のバージョンはKeyを含んでいなかった
$file = '/xxxx/xxxxxxxxx/key.txt';
$keyxml = simplexml_load_string(file_get_contents($file));

for ($i = 0; $i < $table_num; $i = $i + 1) {
	$a_table_name = "`".$table_ary[$i]."`";
	$sql = "SELECT id, title, zip FROM $a_table_name;";
	$result = mysqli_query($DB2, $sql) or die(mysqli_error($DB2));
	$rows = mysqli_num_rows($result);
	echo "$table_ary[$i] has ".$rows." rows.\n";

	for ($j = 0; $j < $rows; $j = $j + 1){
		$row = mysqli_fetch_array($result);
		$id = $row['id'];
		$a_file = fopen("/var/www/html/temporary/$foldername/tmp.zip","w");
		fwrite($a_file, $row['zip']);
		fclose($a_file);
		passthru("cd /var/www/html/temporary/$foldername/; unzip -q /var/www/html/temporary/$foldername/tmp.zip -d /var/www/html/temporary/$foldername/tmp/");
		//decode_TAns($foldername);
		$config_text = file_get_contents("/var/www/html/temporary/$foldername/tmp/configfile.xml");
		$config_xml = simplexml_load_string($config_text);		
		$config_xml->version = "4.0";

		// 以前の自動採点コンテンツにはIDが無い
		$timestamp = makeTimeStamp( );
		$config_xml->q_ID = $timestamp; 
		$config_xml->edit->when = $timestamp;

		$config_xml->server->url = "https://kanaike.susi.oita-u.ac.jp";
		$config_xml->cgi->url = "/php_simple_question_NL/answertest_for_NL40.php";
		$config_xml->tex->url = "/php_simple_question_NL/fortex_xml_space.php";
		$config_xml->refer->url = "/php_simple_question_NL/refer_status.php";

		// 以前の自動採点コンテンツにはauthor_nameが無い
		$config_xml->author_name = $author_ary[$i];
		$config_xml->phpseclibKey = makePhpseclibKey($timestamp);

		$xmlstr = reConfig($config_xml);

		// configfile.xml を置き換える
		$configfile = fopen("/var/www/html/temporary/$foldername/tmp/configfile.xml","w");
		fputs($configfile,$xmlstr);
		fclose($configfile);

		//passthru("cd /var/www/html/temporary/$foldername/tmp ; zip -q -r /var/www/html/temporary/$foldername/new.zip ./configfile.xml ./glossary ./html");
		passthru("cd /var/www/html/temporary/$foldername/tmp ; zip -q -r /var/www/html/temporary/$foldername/new.zip ./configfile.xml ./html");
		$zipbinary = file_get_contents("/var/www/html/temporary/$foldername/new.zip");
		$zipbinary = mysqli_real_escape_string($DB2, $zipbinary);

		$sql = "UPDATE $a_table_name SET zip='$zipbinary' WHERE id='$id'";
		$result_UPDATE = mysqli_query($DB2, $sql) or die(mysqli_error($DB));
		echo "changed ".$row['title']."\n";

		// 作業ファイルを消す
		passthru("rm -r /var/www/html/temporary/$foldername/*");
		//break 2;
	}
}


function makeTimeStamp( ) {
	$stamp =  microtime();
	list($msec, $sec) = explode(" ", $stamp);
	$mustamp = $stamp*1000000;
	return $sec.$mustamp;
}

function makePhpseclibKey($timestamp) {
	global $keyxml;
	$keynum = intval(substr($timestamp,-2,2));
	$keytxt = (string) $keyxml->key[$keynum]->public;
	return $keytxt;
}

function reConfig($xml) {
	$keytxt = (string) $xml->phpseclibKey;
	$xml_version = $xml->version;
	$xml_server_url = $xml->server->url;
	$xml_cgi_url = $xml->cgi->url;
	$xml_tex_url = $xml->tex->url;
	$xml_refer_url = $xml->refer->url;
	$xml_author_name = $xml->author_name;
	$xml_title = $xml->title;
	$xml_keyword = $xml->keyword;
	$xml_edit_where = $xml->edit->where;
	$xml_edit_when = $xml->edit->when;
	$xml_q_ID = $xml->q_ID;
	$xml_TextHeight = $xml->TextHeight;
$configstr = <<< end_of_quote
<?xml version="1.0" encoding="utf-8"?>
<root>
<version>$xml_version</version>
<server>
<url>$xml_server_url</url>
</server>
<cgi>
<url>$xml_cgi_url</url>
</cgi>
<tex>
<url>$xml_tex_url</url>
</tex>
<refer>
<url>$xml_refer_url</url>
</refer>
<LMSserver></LMSserver>
<UserID></UserID>
<record></record>
<q_ID>$xml_q_ID</q_ID>
<author_name>$xml_author_name</author_name>
<title>$xml_title</title>
<keyword>$xml_keyword</keyword>
<edit>
<where>$xml_edit_where</where>
<when>$xml_edit_when</when>
</edit>
<phpseclibKey>$keytxt</phpseclibKey>
<TextHeight>$xml_TextHeight</TextHeight>
end_of_quote;

	$i = 0;
	$str = '';

	foreach ($xml->question as $q) {
		$i = $i + 1;
		$str = $str."<question>\n\n<num>".$i."</num>\n";

		if ((string)$q->AnswerTest == "Glossary") { // Glossary は利用しない
			$str = $str."<AnswerTest>none</AnswerTest>\n";
			$tmp = trim((string)$q->TAns);
			$TAry = explode('_',$tmp);
			$q->TAns = $TAry[0];
			$q->AnsTestOpt = "";
		} else if ((string)$q->AnswerTest == "NLanguage"){ // NL3.0 のコンテンツ
			$tmpTest = (string)$q->FilterType;
			if ($tmpTest == "none") {
				$str = $str."<AnswerTest>none</AnswerTest>\n";
			} elseif ($tmpTest == "tex"){
				$str = $str."<AnswerTest>tex</AnswerTest>\n";
			} elseif ($tmpTest == "mhchem"){
				$str = $str."<AnswerTest>tex</AnswerTest>\n";
			} elseif ($tmpTest == "dictation"){
				$str = $str."<AnswerTest>dictation</AnswerTest>\n";	
			} elseif ($tmpTest == "expression"){
				$str = $str."<AnswerTest>AlgEquiv</AnswerTest>\n";
　　　　　　　　　　　　} 		

		}  else {
			$str = $str."<AnswerTest>".$q->AnswerTest."</AnswerTest>\n";
		}

		if ($q->Allotment) {
			$str = $str."<Allotment>".$q->Allotment."</Allotment>\n";
		} else {
			$str = $str."<Allotment>5</Allotment>\n";
		}

		$str = $str.'<size>'.$q->size."</size>\n";

		if ($q->verticalSize) {
			$str = $str."<verticalSize>".$q->verticalSize."</verticalSize>\n";
		} else {
			$str = $str."<verticalSize>24</verticalSize>\n";
		}

		$str = $str.'<x>'.$q->x."</x>\n";
		$str = $str.'<y>'.$q->y."</y>\n";
		$str = $str."<SAns></SAns>\n";
		$str = $str.'<TAns>'.htmlentities((string)$q->TAns)."</TAns>\n"; //此処はエンティティーをかける。CDATAセクションとの違い。
		$str = $str.'<AnsTestOpt>'.$q->AnsTestOpt."</AnsTestOpt>\n";

		if ($q->operation4sans) {
			if (trim($q->operation4sans->text) == "") {
				$str = $str."<operation4sans><text><![CDATA[sans:''sans;]]></text></operation4sans>\n";
			} else {
				$str = $str.'<operation4sans><text><![CDATA['.$q->operation4sans->text."]]></text></operation4sans>\n";
			}
			if (trim($q->operation4tans->text) == "") {
				$str = $str."<operation4tans><text><![CDATA[tans:''tans;]]></text></operation4tans>\n";
			} else {
				$str = $str.'<operation4tans><text><![CDATA['.$q->operation4tans->text."]]></text></operation4tans>\n";
			}

		} else if ($q->operation){
			$str = $str.'<operation4sans><text><![CDATA['.$q->operation->text."]]></text></operation4sans>\n";
			$str = $str.'<operation4tans><text><![CDATA['.$q->operation4tans->text."]]></text></operation4tans>\n";
		} else {
			$str = $str."<operation4sans><text><![CDATA[sans:''sans;]]></text></operation4sans>\n";
			$str = $str."<operation4tans><text><![CDATA[tans:''tans;]]></text></operation4tans>\n";
		}
		$str = $str."</question>\n\n";
	}
	$configstr = $configstr.$str."</root>";
	return $configstr;
}