static function loadDbSNPTable($con, $dbSNPPath)
{
REDLog::writeInfoLog("Start loading dbSNP file into database");
$dbSNPTable = "dbsnp_database";
try {
if (!DatabaseManager::hasEstablishTable($con, $dbSNPTable)) {
self::createDBSNPTable($con, $dbSNPTable);
$count = 0;
$fp = fopen($dbSNPPath, 'r');
while ($line = fgets($fp) != null) {
if (strpos($line, "#") === 0) {
$count++;
} else {
break;
}
}
fclose($fp);
$sqlClause = "load data local infile '{$dbSNPPath}' into table {$dbSNPTable} IGNORE {$count} LINES";
//fields terminated by '\t' lines terminated by '\n'
echo $sqlClause;
$v = mysqli_query($con, $sqlClause);
if (!$v) {
throw new Exception("Error execute sql clause in loadDbSNPTable()");
}
}
} catch (Exception $e) {
REDLog::writeErrLog($e->getMessage());
}
REDLog::writeInfoLog("End loading dbSNP file into database");
}
static function loadRepeatTable($con, $repeatPath)
{
REDLog::writeInfoLog("Start loading RepeatMasker file into database");
$repeatTable = "repeat_masker";
try {
if (!DatabaseManager::hasEstablishTable($con, $repeatTable)) {
self::createRepeatRegionsTable($con, $repeatTable);
DatabaseManager::setAutoCommit($con, false);
$count = 0;
$fp = fopen($repeatPath, 'r');
fgets($fp);
fgets($fp);
fgets($fp);
while (($line = fgets($fp)) != null) {
$line1 = trim($line);
$section = explode(" ", preg_replace("/\\s(?=\\s)/", "\\1", $line1));
# /[\s]+/
$sqlClause = "insert into {$repeatTable}(chrom,begin,end,type) values('{$section['4']}','{$section['5']}','{$section['6']}','{$section['10']}')";
$v = mysqli_query($con, $sqlClause);
if (++$count % 10000 == 0) {
DatabaseManager::commit($con);
}
}
DatabaseManager::commit($con);
DatabaseManager::setAutoCommit($con, true);
if (!$v) {
throw new Exception("Error execute sql clause in loadRepeatTable()\n");
}
}
} catch (Exception $e) {
REDLog::writeErrLog($e->getMessage());
}
fclose($fp);
REDLog::writeInfoLog("End loading RepeatMasker file into database");
}
static function loadDarnedTable($con, $darnedPath)
{
REDLog::writeInfoLog("Start loading DARNED file into database");
$darnedTable = "darned_database";
if (!DatabaseManager::hasEstablishTable($con, $darnedTable)) {
self::createDARNEDTable($con, $darnedTable);
try {
$count = 0;
DatabaseManager::setAutoCommit($con, false);
$fp = fopen($darnedPath, 'r');
fgets($fp);
while (($line = fgets($fp)) != null) {
$line1 = trim($line);
$section = explode("\t", $line1);
$stringBulider = "insert into " . $darnedTable . "(chrom,coordinate,strand,inchr,inrna) values(";
for ($i = 0; $i < 5; $i++) {
if ($i == 0) {
$stringBulider = $stringBulider . "'chr" . $section[$i] . "',";
} else {
if ($i == 4) {
$sec = str_replace("I", "G", $section[$i]);
$stringBulider = $stringBulider . "'" . $sec . "'";
} else {
if ($i == 1) {
$stringBulider = $stringBulider . $section[$i] . ",";
} else {
$stringBulider = $stringBulider . "'" . $section[$i] . "',";
}
}
}
}
$stringBulider = $stringBulider . ")";
$v = mysqli_query($con, $stringBulider);
if (!$v) {
throw new Exception("Error execute sql clause in loadDarnedTable()");
}
if (++$count % 10000 == 0) {
DatabaseManager::commit($con);
}
DatabaseManager::commit($con);
DatabaseManager::setAutoCommit($con, true);
}
} catch (Exception $e) {
REDLog::writeErrLog($e->getMessage());
}
}
REDLog::writeInfoLog("End loading DARNED file into database");
}
static function loadSpliceJunctionTable($con, $spliceJunctionPath)
{
REDLog::writeInfoLog("Start loading Gene Annotation File into database");
$spliceJunctionTable = "splice_junction";
if (!DatabaseManager::hasEstablishTable($con, $spliceJunctionTable)) {
self::createSpliceJunctionTable($con, $spliceJunctionTable);
try {
$sqlClause = "load data local infile '{$spliceJunctionPath}' into table {$spliceJunctionTable}";
/*fields terminated by '\t' lines terminated by '\n'*/
$v = mysqli_query($con, $sqlClause);
if (!$v) {
throw new Exception("Error execute sql clause in loadSpliceJunctionTable()");
}
} catch (Exception $e) {
REDLog::writeErrLog($e->getMessage());
}
}
REDLog::writeInfoLog("End loading Gene Annotation File into database");
}
static function loadRefSeqGeneTable($con, $refSeqGenePath)
{
REDLog::writeInfoLog("Start loading Ref Seq Gene File into database");
$refseqGeneTableName = "reference_gene";
if (!DatabaseManager::hasEstablishTable($con, $refseqGeneTableName)) {
self::createRefSeqGeneTable($con, $refseqGeneTableName);
try {
$sqlClause = "load data local infile '" . $refSeqGenePath . "' into table " . $refseqGeneTableName;
/*." fields terminated"
."by '\t' lines terminated by '\n'";*/
/*echo $sqlClause;*/
$v = mysqli_query($con, $sqlClause);
if (!$v) {
throw new Exception("Error execute sql clause in loadRefSeqGeneTable()");
}
} catch (Exception $e) {
REDLog::writeErrLog($e->getMessage());
}
}
REDLog::writeInfoLog("End loading Ref Seq Gene File into database");
}
