X-Git-Url: https://mop.ddnsfree.com/gitweb/?a=blobdiff_plain;f=web%2FObj%2Fdbase_pgsql.phh;h=20f47259eb7a54b708aead1afc75f6ba4f375e66;hb=b41e2233431a8862b741c7eb4c998c1a47263ed7;hp=510987feeef152e330051798d086e3529361f80e;hpb=e7958819c64c6a5aeb2ee568fe187d3f0389195f;p=brisk.git diff --git a/web/Obj/dbase_pgsql.phh b/web/Obj/dbase_pgsql.phh index 510987f..20f4725 100644 --- a/web/Obj/dbase_pgsql.phh +++ b/web/Obj/dbase_pgsql.phh @@ -2,7 +2,7 @@ /* * brisk - dbase_pgsql.phh * - * Copyright (C) 2006-2011 Matteo Nastasi + * Copyright (C) 2006-2012 Matteo Nastasi * mailto: nastasi@alternativeoutput.it * matteo.nastasi@milug.org * web: http://www.alternativeoutput.it @@ -22,16 +22,15 @@ * */ -require_once("Obj/dbase_base.phh"); +require_once("${G_base}Obj/dbase_base.phh"); $escsql_from = array( "\\", "'" ); -$escsql_to = array( "\\\\", "\\'" ); - +$escsql_to = array( "\\\\", "\\'" ); function escsql($s) { GLOBAL $escsql_from, $escsql_to; - + return str_replace($escsql_from, $escsql_to, $s); } @@ -41,107 +40,188 @@ class DBConn var $db = FALSE; function DBConn() + { + $this->db = DBConn::$dbcnnx; + } + + static function create() { GLOBAL $G_dbauth; if (DBConn::$dbcnnx == FALSE) { - if (!(DBConn::$dbcnnx = @pg_connect ($G_dbauth))) { - echo "DB connection failed."; - exit; + if (!(DBConn::$dbcnnx = @pg_connect ($G_dbauth, PGSQL_CONNECT_FORCE_NEW))) { + return (FALSE); } } - $this->db = DBConn::$dbcnnx; - return; + $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); } } -class LoginDB +class BriskDB { var $dbconn; var $item; var $item_n; - function LoginDB() + function BriskDB($dbconn) + { + $this->dbconn = $dbconn; + } + + static function create() { - GLOBAL $DOCUMENT_ROOT, $G_dbpfx, $G_false; - log_main("LoginDB create:start"); + GLOBAL $DOCUMENT_ROOT, $G_dbpfx; + + $ret = FALSE; + + log_main("BriskDB create:start"); - $this->dbconn = new DBConn(); + do { + if (($dbconn = DBConn::create()) == FALSE) { + break; + } + + $ret = new BriskDB($dbconn); + } while (0); - log_main("LoginDB create:end"); + 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 LoginDB */ + /* check the existence of the nick in the BriskDB */ log_main("login_exists: ".$login); - $user_sql = sprintf("SELECT * FROM %susers WHERE login = lower('%s');", $G_dbpfx, escsql($login)); - if (($user_pg = pg_query($this->dbconn->db(), $user_sql)) != FALSE) + $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 = $this->query($user_sql)) != FALSE) if (pg_numrows($user_pg) == 1) return TRUE; return FALSE; } - function &getrecord_bylogin($login) { - GLOBAL $G_false, $G_dbpfx; + function getrecord_bylogin($login) { + GLOBAL $G_dbpfx; - $user_sql = sprintf("SELECT * FROM %susers WHERE login = lower('%s');", $G_dbpfx, escsql($login)); - if (($user_pg = pg_query($this->dbconn->db(), $user_sql)) == FALSE) - return $ret; - + $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 = $this->query($user_sql)) == FALSE) { + return FALSE; + } if (pg_numrows($user_pg) != 1) - return $ret; + 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; + } - function &login_verify($login, $pass) + function login_verify($login, $pass) { - GLOBAL $G_dbpfx, $G_false; + GLOBAL $G_dbpfx; - $ret = &$G_false; + $ret = FALSE; log_main("login_verify: ".$login); - //O /* check the existence of the nick in the LoginDB */ + //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 $ret; + 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) { + 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); - return ($ret); - //O break; + break; } } } // end for ($e = 0 ... @@ -160,10 +240,8 @@ class LoginDB return ($ret); } - function &getitem_bylogin($login, &$id) { - GLOBAL $G_false; - - $ret = &$G_false; + function getitem_bylogin($login, &$id) { + $ret = FALSE; $id = -1; log_main("getitem_bylogin: ".$login); @@ -185,124 +263,139 @@ class LoginDB return ($ret->email); } -} // End class LoginDB - - if (0 == 1) { - - - - - - - function count() - { - // sprintf("select count(code) from %sbrisk"); - return ($this->item_n); - } - function getlogin_byidx($idx) + function addusers_from_olddb($olddb, &$cont) { - if ($idx >= $this->item_n) - return FALSE; - return ($this->item[$idx]->login); - } + GLOBAL $G_dbpfx; - function &getitem_bylogin($login, &$id) - { - GLOBAL $G_false; + 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); - log_main("login_exists: ".$login); - - /* check the existence of the nick in the LoginDB */ - for ($i = 0 ; $i < $this->item_n ; $i++) { - if (strcasecmp($this->item[$i]->login, $login) == 0) { - log_main("login[".$i."]: ".$this->item[$i]->login); - $ret = &$this->item[$i]; - $id = $i; - return ($ret); - } - } - $id = -1; - return ($G_false); - } + if ( ! (($user_pg = $this->query($user_sql)) != FALSE && pg_affected_rows($user_pg) == 1) ) { + $cont .= sprintf("ERROR IN LINE: %s\n", eschtml($user_sql)); - function getmail($login) - { - log_main("getmail"); - - /* check the existence of the nick in the LoginDB */ - for ($i = 0 ; $i < $this->item_n ; $i++) { - if (strcasecmp($this->item[$i]->login, $login) == 0) { - log_main("login[".$i."]: ".$this->item[$i]->login); - return ($this->item[$i]->email); + return FALSE; } } - return (FALSE); + return TRUE; } - function gettype($login) + function getdbconn() { - log_main("getmail"); - - /* check the existence of the nick in the LoginDB */ - for ($i = 0 ; $i < $this->item_n ; $i++) { - if (strcasecmp($this->item[$i]->login, $login) == 0) { - log_main("login[".$i."]: ".$this->item[$i]->login); - return ($this->item[$i]->type); - } - } - return (FALSE); + return ($this->dbconn); } - function &login_verify($login, $pass) - { - GLOBAL $G_false; + // ttok text UNIQUE, + // tidx + function bin5_points_save($date, $ttok, $tidx, $ucodes, $pts) + { + GLOBAL $G_dbpfx; - $ret = &$G_false; + $is_trans = FALSE; + $ret = FALSE; - log_main("login_verify: ".$login); + $n = count($ucodes); + /* check the existence of the nick in the BriskDB */ + log_main("bin5_points_save: "); - /* check the existence of the nick in the LoginDB */ - for ($i = 0 ; $i < $this->item_n ; $i++) { - log_main("login_verify: LOOP"); - if (strcasecmp($this->item[$i]->login, $login) == 0) { - log_main("login[".$i."]: ".$this->item[$i]->login); - - /* if it exists check for a valid challenge */ - if (($a_sem = Challenges::lock_data()) != FALSE) { - - if (($chals = &Challenges::load_data()) != FALSE) { - for ($e = 0 ; $e < $chals->item_n ; $e++) { - - log_main("challenge[".$i."]: ".$chals->item[$e]->login); - if (strcmp($login, $chals->item[$e]->login) == 0) { - log_main("login_verify [".$pass."] with [".md5($chals->item[$e]->token.$this->item[$i]->pass)."]"); - - if (strcmp($pass , md5($chals->item[$e]->token.$this->item[$i]->pass)) == 0) { - log_main("login_verify SUCCESS for ".$login); - - $chals->rem($login); - $ret = &$this->item[$i]; - break; - } - } - } // end for ($e = 0 ... - } - - if ($chals->ismod()) { - Challenges::save_data(&$chals); - } - - Challenges::unlock_data($a_sem); - } + do { + if ($this->query("BEGIN") == FALSE) { + break; + } + $is_trans = TRUE; + + /* + * matches management + */ + $mtc_sql = sprintf("SELECT * FROM %sbin5_matches WHERE ttok = '%s';", $G_dbpfx, escsql($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 = $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; - } // if (strcasecmp($this->item[$i]->login, ... + } + } + $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 = $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; + } + + $gam_obj = pg_fetch_object($gam_pg,0); + + /* + * points management + */ + for ($i = 0 ; $i < $n ; $i++) { + /* put points */ + $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 = $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; + } } - return ($ret); + if ($i < $n) + break; + + if ($this->query("COMMIT") == FALSE) { + break; + } + + $is_trans = FALSE; + + $ret = TRUE; + } while (0); + + if ($is_trans) + $this->query("ROLLBACK"); + + return $ret; + } + +} // End class BriskDB + +class LoginDBOld +{ + var $item; + var $item_n; + + function LoginDBOld($filename) + { + GLOBAL $DOCUMENT_ROOT; + log_main("LoginDBOld create:start"); + + if (file_exists("$DOCUMENT_ROOT/Etc/".$filename)) { + require("$DOCUMENT_ROOT/Etc/".$filename); } + else { + return (FALSE); + } + $this->item_n = count($this->item); + log_main("LoginDBOld create:end"); + } - } // if (0 == 1) { + function count() + { + return ($this->item_n); + } +} // End class LoginDBOld -?> \ No newline at end of file +?>