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"); }