PHP DatabaseManager::setAutoCommit方法代码示例

 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 performLikelihoodRateFilter($con, $previousTable, $currentTable, $args)
 {
     if ($args == null || count($args) == 0) {
         return;
     } else {
         if (count($args) != 2) {
             REDLog::writeErrLog("Args for Likelihood Rate Test Filter are incomplete, please have a check");
         }
     }
     $dnaVcfTable = $args[0];
     $threshold = (double) $args[1];
     TableCreator::createFilterTable($con, $previousTable, $currentTable);
     REDLog::writeInfoLog("Start performing Likelihood Rate Test Filter");
     $sqlClause1 = "select {$previousTable}.chrom,{$previousTable}.pos,{$previousTable}.AD,\n{$dnaVcfTable}.qual from {$previousTable},{$dnaVcfTable} where {$previousTable}.chrom=\n{$dnaVcfTable}.chrom and {$previousTable}.pos={$dnaVcfTable}.pos";
     $rs = DatabaseManager::query1($con, $sqlClause1);
     $i = 0;
     while ($row = mysqli_fetch_array($rs)) {
         $chr = (string) $row[0];
         $pos = (int) $row[1];
         $ad = (string) $row[2];
         $qual = (double) $row[3];
         $pb = new SiteBean();
         $pb->SiteBean1($chr, $pos);
         $pb->setAd($ad);
         $pb->setQual($qual);
         $siteBeans[$i] = $pb;
         $i++;
     }
     DatabaseManager::setAutoCommit($con, false);
     $count = 0;
     for ($j = 0; $j < $i; $j++) {
         $str = $siteBeans[$j]->getAd();
         $section = explode("/", $str);
         $ref = (int) $section[0];
         $alt = (int) $section[1];
         if ($ref + $alt > 0) {
             $f_ml = 1.0 * $ref / ($ref + $alt);
             $y = pow($f_ml, $ref) * pow(1 - $f_ml, $alt);
             $y = log($y) / log(10.0);
             $judge = $y + $siteBeans[$j]->getQual() / 10.0;
             if ($judge >= $threshold) {
                 $siteChr = $siteBeans[$j]->getChr();
                 $sitePos = $siteBeans[$j]->getPos();
                 $sqlClause2 = "insert into {$currentTable} select * from {$previousTable} where chrom='" . $siteChr . "' and pos=" . $sitePos;
                 DatabaseManager::insertClause($con, $sqlClause2);
                 if (++$count % 10000 == 0) {
                     DatabaseManager::commit($con);
                 }
             }
         }
     }
     DatabaseManager::commit($con);
     DatabaseManager::setAutoCommit($con, true);
     REDLog::writeInfoLog("End performing Likelihood Rate Test Filter");
 }

 static function performQualityControlFilter($con, $previousTable, $currentTable, $args)
 {
     $COMMIT_COUNTS_PER_ONCE = 10000;
     if ($args == null || count($args) == 0) {
         return;
     } elseif (count($args) != 2) {
         REDLog::writeErrLog("Args for Quality Control Filter are incomplete, please have a check");
     }
     $quality = (double) $args[0];
     $depth = (int) $args[1];
     TableCreator::createFilterTable($con, $previousTable, $currentTable);
     REDLog::writeInfoLog("Start performing Quality Control Filter");
     try {
         $count = 0;
         $str = array("CHROM", "POS", "AD");
         $rs = DatabaseManager::query2($con, $previousTable, $str, null, null);
         $i = 0;
         while ($row = mysqli_fetch_array($rs)) {
             if ($row[2] != null) {
                 $siteBean = new SiteBean();
                 $siteBean->SiteBean1($row[0], $row[1]);
                 $siteBean->setAd($row[2]);
                 $siteBeans[$i] = $siteBean;
                 $i = $i + 1;
             }
         }
         DatabaseManager::setAutoCommit($con, false);
         for ($j = 0; $j < $i; $j++) {
             $str = $siteBeans[$j]->getAd();
             $section = explode("/", $str);
             $ref_n = (int) $section[0];
             $alt_n = (int) $section[1];
             $pos = $siteBeans[$j]->getPos();
             $chr = $siteBeans[$j]->getChr();
             if ($ref_n + $alt_n >= $depth) {
                 $sqlClause = "insert into " . $currentTable . " (select * from " . $previousTable . " where filter='PASS' and pos={$pos} and qual>=" . $quality . " and chrom='" . $chr . "')";
                 $v = mysqli_query($con, $sqlClause);
                 if (++$count % $COMMIT_COUNTS_PER_ONCE == 0) {
                     DatabaseManager::commit($con);
                 }
             }
         }
         DatabaseManager::commit($con);
         DatabaseManager::setAutoCommit($con, true);
         if (!$v) {
             throw new Exception("Error execute sql clause in QualityControlFilter:performFilter()");
         }
     } catch (Exception $e) {
         REDLog::writeErrLog($e->getMessage());
     }
     REDLog::writeInfoLog("End performing Quality Control Filter");
 }

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

<?php

include '../REDTools/DatabaseConnect.php';
include '../database/DatabaseManager.php';
echo DatabaseManager::setAutoCommit();

 static function parseMultiRNAVCFFile($con, $vcfPath, $userid)
 {
     $altColumn = 4;
     $infoColumn = 7;
     $formatColumnIndex = 8;
     $columnLength = 0;
     REDLog::writeInfoLog("Start parsing RNA VCF file");
     try {
         $fp = fopen($vcfPath, 'r');
         DatabaseManager::setAutoCommit($con, false);
         $lineCount = 0;
         $hasEstablishTable = false;
         while (($line1 = fgets($fp)) != null) {
             $line = trim($line1);
             if (strpos($line, "##") === 0) {
                 continue;
             }
             if (strpos($line, "#") === 0) {
                 $columnStrings = explode("\t", substr($line, 1));
                 $columnLength = count($columnStrings);
                 $sampleNamesLength = $columnLength - $formatColumnIndex - 1;
                 for ($j = 0; $j < $sampleNamesLength; $j++) {
                     $sampleNames[$j] = $columnStrings[$formatColumnIndex + 1 + $j];
                 }
                 $tableBuilders = "{$columnStrings['0']} varchar(30),{$columnStrings['1']} int,{$columnStrings['2']} varchar(30),\n\t\t\t\t    {$columnStrings['3']} varchar(5),{$columnStrings['4']} varchar(5),{$columnStrings['5']} float(10,2),\n\t\t\t\t    {$columnStrings['6']} text,{$columnStrings['7']} text,";
                 continue;
             }
             if ($sampleNames == null) {
                 throw new Exception("There are no samples in this vcf file");
             }
             $sections = explode("\t", $line);
             for ($i = $formatColumnIndex + 1; $i < $columnLength; $i++) {
                 if (!strpos($sections[$i], ".")) {
                     $contain = false;
                     $rr = 1;
                 } else {
                     $contain = true;
                     $rr = 2;
                 }
                 if (strcmp($sections[$altColumn], ".") == 0 || $contain) {
                     continue;
                 }
                 $formatColumns = explode(":", $sections[$formatColumnIndex]);
                 $formatLength = count($formatColumns);
                 $dataColumns = explode(":", str_replace(",", "/", $sections[$i]));
                 $dataColumnLength = count($dataColumns);
                 if ($formatLength != $dataColumnLength) {
                     continue;
                 }
                 if (!$hasEstablishTable) {
                     for ($j = 0; $j < $formatLength; $j++) {
                         $formatColumn = $formatColumns[$j];
                         $tableBuilders = $tableBuilders . $formatColumn . " text,";
                     }
                     $tableBuilders = $tableBuilders . "alu varchar(1) default 'F',index(chrom,pos)";
                     for ($j = 0, $len = count($sampleNames); $j < $len; $j++) {
                         $tableName[$j] = $userid . "_" . $sampleNames[$j] . "_rnavcf" . "_" . date("Ymdhisa");
                         DatabaseManager::deleteTable($con, $tableName[$j]);
                         $createClause = "create table " . $tableName[$j] . "({$tableBuilders})";
                         $v = mysqli_query($con, $createClause);
                         if (!$v) {
                             throw new Exception("Error create RNATable.");
                         }
                     }
                     DatabaseManager::commit($con);
                     $hasEstablishTable = true;
                 }
                 $sqlClause = "insert into " . $tableName[$i - $formatColumnIndex - 1] . "(";
                 for ($j = 0; $j < $formatColumnIndex; $j++) {
                     $sqlClause = $sqlClause . $columnStrings[$j] . ",";
                 }
                 for ($j = 0; $j < $formatLength; $j++) {
                     $formatColumn = $formatColumns[$j];
                     $sqlClause = $sqlClause . $formatColumn . ",";
                 }
                 $sqlClause = substr($sqlClause, 0, strlen($sqlClause) - 1);
                 $sqlClause = $sqlClause . ") values('";
                 if (strpos($sections[0], "ch") === 0 && !(strpos($sections[0], "chr") === 0)) {
                     $str = str_replace("ch", "chr", $sections[0]) . "'";
                     $sqlClause = $sqlClause . $str;
                 } else {
                     if (strlen($sections[0]) < 3) {
                         $sqlClause = $sqlClause . "chr" . $sections[0] . "'";
                     } else {
                         $sqlClause = $sqlClause . $sections[0] . "'";
                     }
                 }
                 for ($j = 1; $j < $formatColumnIndex; $j++) {
                     $sqlClause = $sqlClause . ",'" . $sections[$j] . "'";
                 }
                 for ($j = 0; $j < count($dataColumns); $j++) {
                     $dataColumn = $dataColumns[$j];
                     $sqlClause = $sqlClause . ",'" . $dataColumn . "'";
                 }
                 $sqlClause = $sqlClause . ")";
                 $v = mysqli_query($con, $sqlClause);
                 if (!$v) {
                     throw new Exception("Error execute sql clause:  {$sqlClause}");
                 }
                 if (++$lineCount % 10000 == 0) {
//.........这里部分代码省略.........