PHP pg_exec函数代码示例

function getBalanceForUser($link, $user_id, $givenuser)
{
    global $psqlschema, $serverid;
    $return = array();
    $balanacesjsondec = getAllBalances();
    $mybal = $balanacesjsondec[$givenuser];
    if ($mybal) {
        $return['bal'] = isset($mybal["balance"]) ? $mybal["balance"] : 0;
        $return['ec'] = isset($mybal["credit"]) ? $mybal["credit"] : 0;
        $return['everpaid'] = isset($mybal["everpaid"]) ? $mybal["everpaid"] : 0;
        $return['lbal'] = isset($mybal["included_balance_estimate"]) ? $return['bal'] - $mybal["included_balance_estimate"] : $return['bal'];
        $return['lec'] = isset($mybal["included_credit_estimate"]) ? $return['ec'] - $mybal["included_credit_estimate"] : $return['ec'];
        $return['datadate'] = $mybal["newest"];
        $return['balupdate'] = $mybal["last_balance_update"];
    } else {
        # fall back to sql
        $sql = "select * from {$psqlschema}.stats_balances where server={$serverid} and user_id={$user_id} order by time desc limit 1";
        $result = pg_exec($link, $sql);
        $numrows = pg_numrows($result);
        if (!$numrows) {
            $return['bal'] = "N/A";
            $return['ec'] = "N/A";
            $return['lbal'] = "N/A";
            $return['datadate'] = "N/A";
        } else {
            $row = pg_fetch_array($result, 0);
            $return['bal'] = $row["balance"];
            $return['ec'] = $row["credit"];
            $return['lbal'] = "N/A";
            $return['datadate'] = $row["time"];
            $return['everpaid'] = $row["everpaid"];
        }
    }
    return $return;
}

 function open($database, $host, $user, $password)
 {
     $connect_string = "";
     if (!$database) {
         return 0;
     }
     $host = split(":", $host);
     if ($host[0]) {
         $connect_string .= "host={$host['0']}";
     }
     if (isset($host[1])) {
         $connect_string .= " port={$host['1']}";
     }
     if ($user) {
         $connect_string .= " user={$user} ";
     }
     if ($password) {
         $connect_string .= " password={$password} ";
     }
     $connect_string .= " dbname={$database}";
     $this->connect_id = @pg_Connect($connect_string);
     if ($this->connect_id) {
         @pg_exec($this->connect_id, "SET DateStyle TO 'ISO'");
     }
     return $this->connect_id;
 }

function my_handler($sql_insert)
{
    global $table, $link, $new_name;
    $sql_insert = ereg_replace($table, $new_name, $sql_insert);
    $result = pg_exec($link, pre_query($sql_insert)) or pg_die(pg_errormessage($link), $sql_insert, __FILE__, __LINE__);
    $sql_query = $sql_insert;
}

function db_query($req)
{
    // Fonction "surcouche" e mysql_query () qui ajoute la gestion d'erreur
    global $type;
    global $conn;
    switch ($type) {
        case "POSTGRESQL":
            if ($sth = pg_exec($conn, $req)) {
                return $sth;
            } else {
                echo pg_errormessage($conn);
                error_req($req);
            }
            break;
        case "MYSQL":
            if ($sth = mysql_query("{$req}")) {
                // la requete s'est bien passé
                // affichage de la requete si le verbose est activé
                return $sth;
            } else {
                // Erreur dans la requete, donc génération du mail.
                echo mysql_error();
                error_req($req);
            }
    }
}

 function Fetch()
 {
     $sql = "\n\t\tSELECT DISTINCT\n\t\t\tcommit_log.commit_date - SystemTimeAdjust()        AS commit_date_raw,\n\t\t\tcommit_log.id                                      AS commit_log_id,\n\t\t\tcommit_log.encoding_losses                         AS encoding_losses,\n\t\t\tcommit_log.message_id                              AS message_id,\n\t\t\tcommit_log.committer                               AS committer,\n\t\t\tcommit_log.description                             AS commit_description,\n\t\t\tto_char(commit_log.commit_date - SystemTimeAdjust(), 'DD Mon YYYY')  AS commit_date,\n\t\t\tto_char(commit_log.commit_date - SystemTimeAdjust(), 'HH24:MI')      AS commit_time,\n\t\t\tNULL                                               AS port_id,\n\t\t\tNULL                                               AS category,\n\t\t\tNULL                                               AS category_id,\n\t\t\tNULL                                               AS port,\n\t\t\telement_pathname(element.id)                       AS pathname,\n\t\t\tNULL AS version,\n\t\t\tcommit_log_elements.revision_name AS revision,\n\t\t\tNULL AS epoch,\n\t\t\telement.status                                     AS status,\n\t\t\tNULL AS needs_refresh,\n\t\t\tNULL                                               AS forbidden,\n\t\t\tNULL                                               AS broken,\n\t\t\tNULL                                               AS deprecated,\n\t\t\tNULL                                               AS ignore,\n\t\t\tNULL                                               AS expiration_date,\n\t\t\tNULL                                               AS date_added,\n\t\t\tNULL                                               AS element_id,\n\t\t\tNULL                                               AS short_description,\n\t\t\tNULL                                               AS stf_message";
     if ($this->UserID) {
         $sql .= ",\n\t        onwatchlist ";
     }
     $sql .= "\n    FROM commit_log, commit_log_elements, element ";
     if ($this->UserID) {
         $sql .= "\n\t      LEFT OUTER JOIN\n\t (SELECT element_id as wle_element_id, COUNT(watch_list_id) as onwatchlist\n\t    FROM watch_list JOIN watch_list_element \n\t        ON watch_list.id      = watch_list_element.watch_list_id\n\t       AND watch_list.user_id = " . $this->UserID . "\n\t       AND watch_list.in_service\t\t\n\t  GROUP BY wle_element_id) AS TEMP\n\t       ON TEMP.wle_element_id = element.id";
     }
     $sql .= "\n\t  WHERE commit_log.id IN (SELECT tmp.id FROM (SELECT DISTINCT CL.id, CL.commit_date\n  FROM commit_log CL\n WHERE CL.committer  = '" . pg_escape_string($this->Committer) . "'\nORDER BY CL.commit_date DESC ";
     if ($this->Limit) {
         $sql .= " LIMIT " . $this->Limit;
     }
     if ($this->Offset) {
         $sql .= " OFFSET " . $this->Offset;
     }
     $sql .= ")as tmp)\n\t    AND commit_log_elements.commit_log_id = commit_log.id\n\t    AND commit_log_elements.element_id    = element.id\n   ORDER BY 1 desc,\n\t\t\tcommit_log_id";
     if ($this->Debug) {
         echo '<pre>' . $sql . '</pre>';
     }
     $this->LocalResult = pg_exec($this->dbh, $sql);
     if ($this->LocalResult) {
         $numrows = pg_numrows($this->LocalResult);
         if ($this->Debug) {
             echo "That would give us {$numrows} rows";
         }
     } else {
         $numrows = -1;
         echo 'pg_exec failed: ' . "<pre>{$sql}</pre>";
     }
     return $numrows;
 }

 function CreateDatabase()
 {
     global $dcl_domain_info, $dcl_domain;
     $conn = @pg_connect(sprintf('dbname=template1 port=%s host=%s user=%s password=%s', $dcl_domain_info[$dcl_domain]['dbPort'], $dcl_domain_info[$dcl_domain]['dbHost'], $dcl_domain_info[$dcl_domain]['dbUser'], $dcl_domain_info[$dcl_domain]['dbPassword']));
     $query = sprintf('CREATE DATABASE %s', $dcl_domain_info[$dcl_domain]['dbName']);
     return pg_exec($conn, $query) > 0;
 }

 function CreateHTML()
 {
     global $freshports_CommitMsgMaxNumOfLinesToShow;
     if (isset($this->Filter)) {
         $sql = "select * from SanityTestFailures({$this->UserID}, '" . pg_escape_string($this->Filter) . "')";
     } else {
         $sql = "set client_encoding = 'ISO-8859-15';\nSELECT S.*, STF.message as stf_message\n  FROM SanityTestFailures({$this->UserID}) S LEFT OUTER JOIN sanity_test_failures STF\n    ON S.commit_log_id = STF.commit_log_id";
     }
     if ($this->MessageID != '') {
         $sql .= " WHERE message_id = '" . pg_escape_string($this->MessageID) . "'";
     }
     $sql .= " ORDER BY S.commit_date_raw DESC, S.category, S.port";
     if ($this->Debug) {
         echo "\n<p>sql={$sql}</p>\n";
     }
     $result = pg_exec($this->dbh, $sql);
     if (!$result) {
         die("read from database failed");
         exit;
     }
     $DisplayCommit = new DisplayCommit($this->dbh, $result);
     $DisplayCommit->Debug = $this->Debug;
     $DisplayCommit->SetDaysMarkedAsNew($this->DaysMarkedAsNew);
     $DisplayCommit->SetUserID($this->UserID);
     $DisplayCommit->SetWatchListAsk($this->WatchListAsk);
     $DisplayCommit->SanityTestFailure = true;
     $RetVal = $DisplayCommit->CreateHTML();
     $this->HTML = $DisplayCommit->HTML;
     return $RetVal;
 }

function start_maint()
{
    custom_db("cubit");
    pg_exec("VACUUM");
    pg_exec("VACUUM FULL");
    pg_exec("VACUUM ANALYZE");
    pg_exec("REINDEX DATABASE cubit");
    $get_comps = "SELECT * FROM companies WHERE status = 'active'";
    $run_comps = pg_exec($get_comps) or errDie("Unable to get active companies");
    if (pg_numrows($run_comps) < 1) {
        return "<li class='err'>No Active Companies Found To Process.</li>";
    } else {
        #process the blk1 db
        custom_db("cubit_blk1");
        pg_exec("VACUUM");
        pg_exec("VACUUM FULL");
        pg_exec("VACUUM ANALYZE");
        pg_exec("REINDEX DATABASE cubit_blk1");
        #process the active companies
        while ($carr = pg_fetch_array($run_comps)) {
            $company = "cubit_" . $carr['code'];
            custom_db("{$company}");
            pg_exec("VACUUM");
            pg_exec("VACUUM FULL");
            pg_exec("VACUUM ANALYZE");
            pg_exec("REINDEX DATABASE {$company}");
        }
    }
    $display = "\r\n\t\t<table " . TMPL_tblDflts . ">\r\n\t\t\t<tr>\r\n\t\t\t\t<td><li class='err'>Process Has Been Completed.</li></td>\r\n\t\t\t</tr>\r\n\t\t</table>";
    return $display;
}

 /**
  * @throws SQLException
  * @return void
  */
 protected function initTables()
 {
     include_once 'creole/drivers/pgsql/metadata/PgSQLTableInfo.php';
     // Get Database Version
     $result = pg_exec($this->dblink, "SELECT version() as ver");
     if (!$result) {
         throw new SQLException("Failed to select database version");
     }
     // if (!$result)
     $row = pg_fetch_assoc($result, 0);
     $arrVersion = sscanf($row['ver'], '%*s %d.%d');
     $version = sprintf("%d.%d", $arrVersion[0], $arrVersion[1]);
     // Clean up
     $arrVersion = null;
     $row = null;
     pg_free_result($result);
     $result = null;
     $result = pg_exec($this->dblink, "SELECT oid, relname FROM pg_class\n\t\t\t\t\t\t\t\t\t\tWHERE relkind = 'r' AND relnamespace = (SELECT oid\n\t\t\t\t\t\t\t\t\t\tFROM pg_namespace\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t     nspname NOT IN ('information_schema','pg_catalog')\n\t\t\t\t\t\t\t\t\t\t     AND nspname NOT LIKE 'pg_temp%'\n\t\t\t\t\t\t\t\t\t\t     AND nspname NOT LIKE 'pg_toast%'\n\t\t\t\t\t\t\t\t\t\tLIMIT 1)\n\t\t\t\t\t\t\t\t\t\tORDER BY relname");
     if (!$result) {
         throw new SQLException("Could not list tables", pg_last_error($this->dblink));
     }
     while ($row = pg_fetch_assoc($result)) {
         $this->tables[strtoupper($row['relname'])] = new PgSQLTableInfo($this, $row['relname'], $version, $row['oid']);
     }
 }

 public function query($string = '')
 {
     if ($string != '') {
         $this->query = $this->escape($string);
     } else {
         $this->query = $this->select . $this->insert . $this->update . $this->delete . $this->create . $this->from . $this->join . $this->where . $this->group . $this->order . $this->limit;
     }
     $this->insert = " RETURNING id";
     $this->result = pg_exec($this->connection, $this->query);
     $this->inserted_id = $this->result;
     $this->error = pg_errormessage($this->connection);
     $this->affected_rows = pg_affected_rows($this->result);
     $this->select = '';
     $this->insert = '';
     $this->update = '';
     $this->delete = '';
     $this->create = '';
     $this->from = '';
     $this->join = '';
     $this->where = '';
     $this->group = '';
     $this->order = '';
     $this->limit = '';
     return $this;
 }

function write_details($_POST)
{
    extract($_POST);
    # validate input
    require_lib("validate");
    $v = new validate();
    $v->isOk($branch_ip, "url", 1, 50, "Invalid Branch IP.");
    $v->isOk($branch_company, "url", 4, 4, "Invalid Branch Company Code.");
    $v->isOk($branch_username, "string", 1, 50, "Invalid Branch Username.");
    //	$v->isOk ($branch_password, "url", 1, 50, "Invalid Branch Password.");
    //	$v->isOk ($branch_passwordconfirm, "url", 1, 50, "Invalid Branch Password.");
    //	$v->isOk ($branch_ip, "url", 1, 50, "Invalid Branch IP.");
    if ($branch_password != $branch_passwordconfirm) {
        $v->addError($branch_password, "Passwords do not match.");
    }
    # display errors, if any
    if ($v->isError()) {
        $confirmCust = "";
        $errors = $v->getErrors();
        foreach ($errors as $e) {
            $confirmCust .= "<li class='err'>" . $e["msg"] . "</li>";
        }
        return get_details($_POST, $confirmCust);
    }
    db_connect();
    $add_sql = "\n\t\tINSERT INTO branches_data (\n\t\t\tbranch_name, branch_desc, branch_contact, branch_ip, \n\t\t\tdate_added, last_online, branch_username, branch_password, \n\t\t\tlast_login_from, branch_localuser, branch_company\n\t\t) VALUES (\n\t\t\t'{$branch_name}', '{$branch_desc}', '{$branch_contact}', '{$branch_ip}', \n\t\t\t'now', '1990-01-01', '{$branch_username}', md5('{$branch_password}'), \n\t\t\t'1990-01-01', '{$branch_localuser}', '{$branch_company}'\n\t\t)";
    $run_add = pg_exec($add_sql) or errDie("Unable to add branch information.");
    $display = "\n\t\t\t\t\t<table " . TMPL_tblDflts . ">\n\t\t\t\t\t\t<tr>\n\t\t\t\t\t\t\t<th>Branch Added</th>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t\t\t\t<td>Branch Has Been Added.</td>\n\t\t\t\t\t\t</tr>\n\t\t\t\t\t</table>\n\t\t\t\t";
    return $display;
}

 function Fetch()
 {
     $sql = "set client_encoding = 'ISO-8859-15';\n\t\tSELECT DISTINCT\n\t\t\tCL.commit_date - SystemTimeAdjust()                                            AS commit_date_raw,\n\t\t\tCL.id                                                                          AS commit_log_id,\n\t\t\tCL.encoding_losses                                                             AS encoding_losses,\n\t\t\tCL.message_id                                                                  AS message_id,\n\t\t\tCL.committer                                                                   AS committer,\n\t\t\tCL.description                                                                 AS commit_description,\n\t\t\tto_char(CL.commit_date - SystemTimeAdjust(), 'DD Mon YYYY')                    AS commit_date,\n\t\t\tto_char(CL.commit_date - SystemTimeAdjust(), 'HH24:MI')                        AS commit_time,\n\t\t\tCLP.port_id                                                                    AS port_id,\n\t\t\tC.name                                                                         AS category,\n\t\t\tC.id                                                                           AS category_id,\n\t\t\tE.name                                                                         AS port,\n\t\t\telement_pathname(E.id)                                                         AS pathname,\n\t\t\tCASE when CLP.port_version IS NULL then P.version   else CLP.port_version  END AS version,\n\t\t\tCASE when CLP.port_version is NULL then P.revision  else CLP.port_revision END AS revision,\n\t\t\tCASE when CLP.port_epoch   is NULL then P.portepoch else CLP.port_epoch    END AS epoch,\n\t\t\tE.status                                                                       AS status,\n\t\t\tCLP.needs_refresh                                                              AS needs_refresh,\n\t\t\tP.forbidden                                                                    AS forbidden,\n\t\t\tP.broken                                                                       AS broken,\n\t\t\tP.deprecated                                                                   AS deprecated,\n\t\t\tP.ignore                                                                       AS ignore,\n\t\t\tP.expiration_date                                                              AS expiration_date,\n\t\t\tdate_part('epoch', P.date_added)                                               AS date_added,\n\t\t\tP.element_id                                                                   AS element_id,\n\t\t\tP.short_description                                                            AS short_description,\n\t\t\tSTF.message                                                                    AS stf_message";
     if ($this->UserID) {
         $sql .= ",\n\t        onwatchlist ";
     }
     $sql .= "\n    FROM commit_log_ports CLP JOIN (SELECT * FROM commit_log WHERE commit_log.committer = '" . pg_escape_string($this->Committer) . "' ORDER BY commit_date DESC ";
     if ($this->Limit) {
         $sql .= " LIMIT " . $this->Limit;
     }
     if ($this->Offset) {
         $sql .= " OFFSET " . $this->Offset;
     }
     $sql .= ") CL on (CLP.commit_log_id = CL.id) \n          LEFT OUTER JOIN sanity_test_failures STF ON STF.commit_log_id = CLP.commit_log_id, categories C, ports P, element E ";
     if ($this->UserID) {
         $sql .= "\n\t      LEFT OUTER JOIN\n\t (SELECT element_id as wle_element_id, COUNT(watch_list_id) as onwatchlist\n\t    FROM watch_list JOIN watch_list_element \n\t        ON watch_list.id      = watch_list_element.watch_list_id\n\t       AND watch_list.user_id = " . $this->UserID . "\n\t       AND watch_list.in_service\t\t\n\t  GROUP BY wle_element_id) AS TEMP\n\t       ON TEMP.wle_element_id = E.id";
     }
     $sql .= "\n\t  WHERE CLP.port_id = P.id\n\t    AND C.id        = P.category_id\n\t    AND E.id        = P.element_id\n   ORDER BY 1 desc,\n\t\t\tcommit_log_id,\n\t\t\tcategory,\n\t\t\tport";
     if ($this->Debug) {
         echo '<pre>' . $sql . '</pre>';
     }
     $this->LocalResult = pg_exec($this->dbh, $sql);
     if ($this->LocalResult) {
         $numrows = pg_numrows($this->LocalResult);
         if ($this->Debug) {
             echo "That would give us {$numrows} rows";
         }
     } else {
         $numrows = -1;
         echo 'pg_exec failed: ' . "<pre>{$sql}</pre>";
     }
     return $numrows;
 }

 function CreateHTML()
 {
     global $freshports_CommitMsgMaxNumOfLinesToShow;
     if (isset($this->Filter)) {
         $sql = "select * from LatestCommitsFiltered({$this->MaxNumberOfPorts}, {$this->UserID}, '" . pg_escape_string($this->Filter) . "')";
     } else {
         #			$sql = "select * from LatestCommits($this->MaxNumberOfPorts, $this->UserID)";
         $sql = "\n  SELECT LC.*, STF.message AS stf_message\n    FROM LatestCommits({$this->MaxNumberOfPorts}, 0, '" . pg_escape_string($this->BranchName) . "') LC LEFT OUTER JOIN sanity_test_failures STF\n      ON LC.commit_log_id = STF.commit_log_id\nORDER BY LC.commit_date_raw DESC, LC.category, LC.port, element_pathname";
     }
     if ($this->Debug) {
         echo "\n<p>sql={$sql}</p>\n";
     }
     $result = pg_exec($this->dbh, $sql);
     if (!$result) {
         die("read from database failed");
         exit;
     }
     $DisplayCommit = new DisplayCommit($this->dbh, $result);
     $DisplayCommit->Debug = $this->Debug;
     $DisplayCommit->SetDaysMarkedAsNew($this->DaysMarkedAsNew);
     $DisplayCommit->SetUserID($this->UserID);
     $DisplayCommit->SetWatchListAsk($this->WatchListAsk);
     $RetVal = $DisplayCommit->CreateHTML();
     $this->HTML = $DisplayCommit->HTML;
     return $RetVal;
 }

 public function excecute($sql)
 {
     if ($sql instanceof Query) {
         $sql = $sql->getQuery($this->config->getType());
     }
     $this->last_query = $sql;
     return pg_exec($this->connection, $sql);
 }

function debitos_tipos_numcgm($numcgm)
{
    $instit = db_getsession("DB_instit");
    $sql = "select distinct t.k00_tipo,t.k00_descr,t.k00_marcado,b.k00_numcgm,t.k00_emrec,t.k00_agnum,t.k00_agpar\n          from arrenumcgm b\n                inner join arrecad a    on b.k00_numpre = a.k00_numpre\n                inner join arretipo t   on t.k00_tipo   = a.k00_tipo\n                inner join arreinstit n on n.k00_numpre = a.k00_numpre\n                  where b.k00_numcgm = {$numcgm} and n.k00_instit = {$instit}";
    // echo "<br> cgm = $sql <br> ";
    $result = pg_exec($sql) or die("Sql : " . pg_ErrorMessage());
    return pg_numrows($result) == 0 ? false : $result;
}