X-Git-Url: https://mop.ddnsfree.com/gitweb/?a=blobdiff_plain;f=web%2FObj%2Fdbase_pgsql.phh;h=bd4dfb3ce78305700efaa23dd478dc859ec596a2;hb=ce816fe4c9d983c42b3e9c25e62606c6bcc56b67;hp=8dbdfb6e70fd7d29012e23bd7b759f75beafd68c;hpb=e469882b494df00aab18f2e38d18c5faa214c740;p=brisk.git diff --git a/web/Obj/dbase_pgsql.phh b/web/Obj/dbase_pgsql.phh index 8dbdfb6..bd4dfb3 100644 --- a/web/Obj/dbase_pgsql.phh +++ b/web/Obj/dbase_pgsql.phh @@ -2,8 +2,8 @@ /* * brisk - dbase_pgsql.phh * - * Copyright (C) 2006-2011 Matteo Nastasi - * mailto: nastasi@alternativeoutput.it + * Copyright (C) 2006-2012 Matteo Nastasi + * mailto: nastasi@alternativeoutput.it * matteo.nastasi@milug.org * web: http://www.alternativeoutput.it * @@ -34,30 +34,46 @@ function escsql($s) return str_replace($escsql_from, $escsql_to, $s); } -class DBConn +class DBConn { static $dbcnnx = FALSE; var $db = FALSE; - + function DBConn() { - $this->db = DBConn::$dbcnnx; + $this->db = DBConn::$dbcnnx; } static function create() { GLOBAL $G_dbauth; - + if (DBConn::$dbcnnx == FALSE) { - if (!(DBConn::$dbcnnx = @pg_connect ($G_dbauth))) { + if (!(DBConn::$dbcnnx = @pg_connect ($G_dbauth, PGSQL_CONNECT_FORCE_NEW))) { return (FALSE); } } $out = new DBConn(); - + return $out; } + + static function destroy() + { + if (DBConn::$dbcnnx != FALSE) { + DBConn::$dbcnnx = FALSE; + return (pg_close(DBConn::$dbcnnx)); + } + return TRUE; + } + + static function recover() + { + self::destroy(); + return (self::create()); + } + function db() { return ($this->db); @@ -69,7 +85,7 @@ class BriskDB var $dbconn; var $item; var $item_n; - + function BriskDB($dbconn) { $this->dbconn = $dbconn; @@ -82,34 +98,47 @@ class BriskDB $ret = FALSE; log_main("BriskDB create:start"); - + do { - if (($dbconn = DBConn::create()) == FALSE) + if (($dbconn = DBConn::create()) == FALSE) { break; - + } + $ret = new BriskDB($dbconn); } while (0); - + return ($ret); } + function query($sql) + { + if (($res = pg_query($this->dbconn->db(), $sql)) == FALSE) { + // try to recover the connection + if (($this->dbconn = DBConn::recover()) == FALSE) + return FALSE; + return (pg_query($this->dbconn->db(), $sql)); + } + + return ($res); + } + function users_load() { } - + function login_exists($login) { GLOBAL $G_dbpfx; /* check the existence of the nick in the BriskDB */ log_main("login_exists: ".$login); - + $user_sql = sprintf("SELECT * FROM %susers WHERE login = lower('%s') AND (type & CAST (X'%08x' as integer)) = 0;", $G_dbpfx, escsql($login), USER_FLAG_TY_DISABLE); - if (($user_pg = pg_query($this->dbconn->db(), $user_sql)) != FALSE) + if (($user_pg = $this->query($user_sql)) != FALSE) if (pg_numrows($user_pg) == 1) return TRUE; - + return FALSE; } @@ -117,83 +146,115 @@ class BriskDB GLOBAL $G_dbpfx; $user_sql = sprintf("SELECT * FROM %susers WHERE login = lower('%s') AND (type & CAST (X'%08x' as integer)) = 0;", $G_dbpfx, escsql($login), USER_FLAG_TY_DISABLE); - if (($user_pg = pg_query($this->dbconn->db(), $user_sql)) == FALSE) + if (($user_pg = $this->query($user_sql)) == FALSE) { return FALSE; - + } if (pg_numrows($user_pg) != 1) return FALSE; - + $user_obj = pg_fetch_object($user_pg, 0); return ($user_obj); } + function user_update_login_time($code, $lintm) + { + GLOBAL $G_dbpfx; + + $user_sql = sprintf("UPDATE %susers SET (lintm) = (date 'epoch' + %d * INTERVAL '1 second') WHERE code = %d;", $G_dbpfx, $lintm, $code); + + // $user_pg = $this->query($user_sql); + // $row_n = pg_affected_rows($user_pg); + // fprintf(STDERR, "query: %s NUM: %d\n", ($user_pg == FALSE ? "FALSE" : "TRUE"), $row_n); + if ( ! (($user_pg = $this->query($user_sql)) != FALSE && pg_affected_rows($user_pg) == 1) ) { + return FALSE; + } + + return TRUE; + } + + function user_prefs_update($code, $flags, $supp_comp) + { + GLOBAL $G_dbpfx; + + $user_sql = sprintf("UPDATE %susers SET (type, supp_comp) = (%d, '%s') WHERE code = %d;", + $G_dbpfx, $flags, $supp_comp, $code); + fprintf(STDERR, "REQUEST [%s]\n", $user_sql); + if ( ! (($user_pg = $this->query($user_sql)) != FALSE && pg_affected_rows($user_pg) == 1) ) { + return FALSE; + } + fprintf(STDERR, "REQUEST GOOD [%s]\n", $user_sql); + + return TRUE; + } - + /* + if success return a LoginDBItem object + */ function login_verify($login, $pass) { GLOBAL $G_dbpfx; - + $ret = FALSE; - + log_main("login_verify: ".$login); - - + //O /* check the existence of the nick in the BriskDB */ //O for ($i = 0 ; $i < $this->item_n ; $i++) { //O log_main("login_verify: BEGIN"); - + if (($user_obj = $this->getrecord_bylogin($login)) == FALSE) { return FALSE; } log_main("login[".$user_obj->code."]: ".$user_obj->login); - + /* if it exists check for a valid challenge */ - if (($a_sem = Challenges::lock_data()) != FALSE) { + if (($a_sem = Challenges::lock_data(TRUE)) != FALSE) { if (($chals = &Challenges::load_data()) != FALSE) { for ($e = 0 ; $e < $chals->item_n ; $e++) { log_main("challenge[".$e."]: ".$chals->item[$e]->login); if (strcmp($login, $chals->item[$e]->login) == 0) { log_main("login_verify [".$pass."] with [".md5($chals->item[$e]->token.$user_obj->pass)."]"); - + if (strcmp($pass, md5($chals->item[$e]->token.$user_obj->pass)) == 0) { log_main("login_verify SUCCESS for ".$login); - + $chals->rem($login); + $this->user_update_login_time($user_obj->code, time()); $ret = LoginDBItem::LoginDBItemFromRecord($user_obj); break; } } } // end for ($e = 0 ... } - + if ($chals->ismod()) { Challenges::save_data(&$chals); } - + Challenges::unlock_data($a_sem); } //O break; // O } // if (strcasecmp($this->item[$i]->login, ... //O } - + return ($ret); } function getitem_bylogin($login, &$id) { $ret = FALSE; $id = -1; - + log_main("getitem_bylogin: ".$login); - + if (($user_obj = $this->getrecord_bylogin($login)) == FALSE) return $ret; $id = $user_obj->code; return (LoginDBItem::LoginDBItemFromRecord($user_obj)); } - + // TODO FOR DB function getmail($login) { @@ -201,7 +262,7 @@ class BriskDB if (($ret = $this->getrecord_bylogin($login)) == FALSE) return FALSE; - + return ($ret->email); } @@ -212,11 +273,9 @@ class BriskDB for ($i = 0 ; $i < $olddb->count() ; $i++) { $user_sql = sprintf("INSERT INTO %susers ( login, pass, email, type) VALUES ('%s', '%s', '%s', %d);", $G_dbpfx, escsql(strtolower($olddb->item[$i]->login)), escsql($olddb->item[$i]->pass), - escsql($olddb->item[$i]->email), $olddb->item[$i]->type & USER_FLAG_TY_ALL); - - // if ( ! (($user_pg = pg_exec($dbconn,$order_add_sql)) != FALSE && pg_affected_rows($order_pg) == 1) ) { + escsql($olddb->item[$i]->email), $olddb->item[$i]->type & USER_FLAG_TY_ALL); - if ( ! (($user_pg = pg_query($this->dbconn->db(), $user_sql)) != FALSE && pg_affected_rows($user_pg) == 1) ) { + if ( ! (($user_pg = $this->query($user_sql)) != FALSE && pg_affected_rows($user_pg) == 1) ) { $cont .= sprintf("ERROR IN LINE: %s\n", eschtml($user_sql)); return FALSE; @@ -230,11 +289,12 @@ class BriskDB return ($this->dbconn); } - // ttok text UNIQUE, - // tidx - function bin5_points_save($date, $ttok, $tidx, $ucodes, $pts) + // ttok text UNIQUE, + // tidx + function bin5_points_save($date, $table, $tidx, $ucodes, $pts) { GLOBAL $G_dbpfx; + $sql_ttok = escsql($table->table_token); $is_trans = FALSE; $ret = FALSE; @@ -242,9 +302,9 @@ class BriskDB $n = count($ucodes); /* check the existence of the nick in the BriskDB */ log_main("bin5_points_save: "); - + do { - if (pg_query($this->dbconn->db(), "BEGIN") == FALSE) { + if ($this->query("BEGIN") == FALSE) { break; } $is_trans = TRUE; @@ -252,31 +312,43 @@ class BriskDB /* * matches management */ - $mtc_sql = sprintf("SELECT * FROM %sbin5_matches WHERE ttok = '%s';", $G_dbpfx, escsql($ttok)); - if (($mtc_pg = pg_query($this->dbconn->db(), $mtc_sql)) == FALSE || pg_numrows($mtc_pg) != 1) { + $mtc_sql = sprintf("UPDATE %sbin5_matches SET (mazzo_next, mult_next) = (%d, %d) WHERE ttok = '%s' RETURNING *;", + $G_dbpfx, $table->mazzo, $table->mult, $sql_ttok); + if (($mtc_pg = $this->query($mtc_sql)) == FALSE || pg_numrows($mtc_pg) != 1) { // match not exists, insert it - $mtc_sql = sprintf("INSERT INTO %sbin5_matches (ttok, tidx) VALUES ('%s', %d) RETURNING *;", - $G_dbpfx, escsql($ttok), $tidx); - if ( ! (($mtc_pg = pg_query($this->dbconn->db(), $mtc_sql)) != FALSE && - pg_affected_rows($mtc_pg) == 1) ) { + $mtc_sql = sprintf("INSERT INTO %sbin5_matches (ttok, tidx, mazzo_next, mult_next) VALUES ('%s', %d, %d, %d) RETURNING *;", + $G_dbpfx, $sql_ttok, $tidx, $table->mazzo, $table->mult); + if (($mtc_pg = $this->query($mtc_sql)) == FALSE || pg_affected_rows($mtc_pg) != 1) { log_crit(sprintf("bin5_points_save: failed at insert match [%s]", $mtc_sql)); break; } + for ($i = 0 ; $i < $n ; $i++) { + $ord_sql = sprintf("INSERT INTO %sbin5_table_orders (mcode, ucode, pos) VALUES (%d, %d, %d);", + $G_dbpfx, $G_dbpfx, $sql_ttok, $ucode[$i], $i); + if (($ord_pg = $this->query($ord_sql)) == FALSE || pg_affected_rows($ord_pg) != 1 ) { + log_crit(sprintf("bin5_points_save: failed at insert table order [%s]", $ord_sql)); + break; + } + } + if ($i < $n) + break; } $mtc_obj = pg_fetch_object($mtc_pg,0); /* * games management */ - $gam_sql = sprintf("INSERT INTO %sbin5_games (mcode, tstamp) - VALUES (%d, to_timestamp(%d)) RETURNING *;", - $G_dbpfx, $mtc_obj->code, $date); - if ( ! (($gam_pg = pg_query($this->dbconn->db(), $gam_sql)) != FALSE && - pg_affected_rows($gam_pg) == 1) ) { + $gam_sql = sprintf("INSERT INTO %sbin5_games (mcode, tstamp, asta_pnt, pnt, asta_win, friend, mazzo, mult) + VALUES (%d, to_timestamp(%d), %d, %d, %d, %d, %d, %d) RETURNING *;", + $G_dbpfx, $mtc_obj->code, $date, + ($table->old_asta_win == -1 ? -1 : $ucodes[$table->old_asta_win]), + ($table->old_friend == -1 ? -1 : $ucodes[$table->old_friend]), + $table->old_asta_pnt, $table->old_mult, $table->old_pnt); + if (($gam_pg = $this->query($gam_sql)) == FALSE || pg_affected_rows($gam_pg) != 1) { log_crit(sprintf("bin5_points_save: failed at insert game [%s]", $gam_sql)); - break; + break; } - + $gam_obj = pg_fetch_object($gam_pg,0); /* @@ -284,37 +356,35 @@ class BriskDB */ for ($i = 0 ; $i < $n ; $i++) { /* put points */ - $pts_sql = sprintf("INSERT INTO %sbin5_points (gcode, ucode, pts) + $pts_sql = sprintf("INSERT INTO %sbin5_points (gcode, ucode, pts) VALUES (%d, %d, %d);", $G_dbpfx, $gam_obj->code, $ucodes[$i], $pts[$i]); - if ( ! (($pts_pg = pg_query($this->dbconn->db(), $pts_sql)) != FALSE && - pg_affected_rows($pts_pg) == 1) ) { + if (($pts_pg = $this->query($pts_sql)) == FALSE || pg_affected_rows($pts_pg) != 1) { log_crit(sprintf("bin5_points_save: failed at insert point [%s]", $pts_sql)); - break; + break; } } - if ($i < $n) break; - - if (pg_query($this->dbconn->db(), "COMMIT") == FALSE) { + + if ($this->query("COMMIT") == FALSE) { break; } - + $is_trans = FALSE; $ret = TRUE; } while (0); - + if ($is_trans) - pg_query($this->dbconn-db(), "ROLLBACK"); - + $this->query("ROLLBACK"); + return $ret; } } // End class BriskDB -class LoginDBOld +class LoginDBOld { var $item; var $item_n; @@ -341,4 +411,4 @@ class LoginDBOld } // End class LoginDBOld -?> \ No newline at end of file +?>