X-Git-Url: http://mop.ddnsfree.com/gitweb/?a=blobdiff_plain;f=web%2FObj%2Fdbase_pgsql.phh;h=7cd0c4bc0ee237e5cab8607b095b30adcb38d24e;hb=79db26bc13a3eed3104658c4ce5b174557275909;hp=4c34c2c4353261cb791d0c71bc2282684bd4865d;hpb=31691c02c1802a699c4595448919cce161834c77;p=brisk.git diff --git a/web/Obj/dbase_pgsql.phh b/web/Obj/dbase_pgsql.phh index 4c34c2c..7cd0c4b 100644 --- a/web/Obj/dbase_pgsql.phh +++ b/web/Obj/dbase_pgsql.phh @@ -2,7 +2,7 @@ /* * brisk - dbase_pgsql.phh * - * Copyright (C) 2006-2012 Matteo Nastasi + * Copyright (C) 2006-2015 Matteo Nastasi * mailto: nastasi@alternativeoutput.it * matteo.nastasi@milug.org * web: http://www.alternativeoutput.it @@ -25,7 +25,7 @@ require_once("${G_base}Obj/dbase_base.phh"); $escsql_from = array( "\\", "'" ); -$escsql_to = array( "\\\\", "\\'" ); +$escsql_to = array( "\\\\", "''" ); function escsql($s) { @@ -62,8 +62,9 @@ class DBConn static function destroy() { if (DBConn::$dbcnnx != FALSE) { + $ret = pg_close(DBConn::$dbcnnx); DBConn::$dbcnnx = FALSE; - return (pg_close(DBConn::$dbcnnx)); + return ($ret); } return TRUE; } @@ -112,16 +113,23 @@ class BriskDB function query($sql) { - if (($res = pg_query($this->dbconn->db(), $sql)) == FALSE) { + if (($res = @pg_query($this->dbconn->db(), $sql)) == FALSE) { + error_log('pg_result_status: ' . pg_result_status($res)); + error_log('pg_connection_status: ' . pg_connection_status($this->dbconn->db())); // try to recover the connection if (($this->dbconn = DBConn::recover()) == FALSE) return FALSE; - return (pg_query($this->dbconn->db(), $sql)); + return (@pg_query($this->dbconn->db(), $sql)); } return ($res); } + function last_error() + { + return pg_last_error($this->dbconn->db); + } + function users_load() { } @@ -133,8 +141,8 @@ class BriskDB /* 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); + $user_sql = sprintf("SELECT * FROM %susers WHERE login = '%s'", + $G_dbpfx, escsql($login)); if (($user_pg = $this->query($user_sql)) != FALSE) if (pg_numrows($user_pg) == 1) return TRUE; @@ -160,8 +168,8 @@ class BriskDB function user_add($login, $pass, $email, $type, $disa_reas, $guar_code) { GLOBAL $G_dbpfx; - $usr_sql = sprintf("INSERT INTO %susers (login, pass, email, type, disa_reas, guar_code) - VALUES ('%s', '%s', '%s', %d, %d, %d) RETURNING *;", + $usr_sql = sprintf("INSERT INTO %susers (login, pass, email, type, disa_reas, guar_code, lintm) + VALUES ('%s', '%s', '%s', %d, %d, %d, now()) RETURNING *;", $G_dbpfx, escsql(strtolower($login)), escsql($pass), escsql($email), $type, $disa_reas, $guar_code); @@ -185,6 +193,63 @@ class BriskDB return (TRUE); } + /* + $laddr is native php int (32 or 64 bit) + if ret True is ip is free + */ + function selfreg_check($laddr) + { + GLOBAL $G_dbpfx, $G_selfreg_tout, $G_selfreg_mask; + + $sere_sql = sprintf("DELETE from %sselfreg_chk WHERE atime < now();", $G_dbpfx); + if (($sere_pg = $this->query($sere_sql)) == FALSE) { + return (FALSE); + } + + if (($sere_pg = $this->query($sere_sql)) == FALSE) { + return (FALSE); + } + + $sere_sql = sprintf("SELECT * FROM %sselfreg_chk WHERE (ip & %d) = %d;", + $G_dbpfx, int2four($G_selfreg_mask), int2four($laddr & $G_selfreg_mask)); + if (($sere_pg = $this->query($sere_sql)) == FALSE) { + return(FALSE); + } + + $ret = pg_numrows($sere_pg); + + if ($ret === FALSE) { + return(FALSE); + } + else if ($ret === 0) { + return(TRUE); + } + else if ($ret > 0) { + // already present + return(FALSE); + } + else { + // unreachable branch + return(FALSE); + } + } + + /* + $laddr is native php int (32 or 64 bit) + if ret True is ip is free + */ + function selfreg_set($laddr) + { + GLOBAL $G_dbpfx, $G_selfreg_tout, $G_selfreg_mask; + + $newi_sql = sprintf("INSERT INTO %sselfreg_chk (ip, atime) VALUES (%d, now() + interval '%d seconds');", + $G_dbpfx, int2four($laddr & $G_selfreg_mask), $G_selfreg_tout); + if (($newi_pg = $this->query($newi_sql)) == FALSE) { + return(FALSE); + } + return(TRUE); + } + /* to be able to add mail record code into the record itself I must reserve it before. */ @@ -252,15 +317,16 @@ class BriskDB return TRUE; } - function user_update_flag_ty($code, $old_type, $old_reas, $type, $reas) + function user_update_flag_ty($code, $type, $old_val, $old_reas, $new_val, $new_reas) { GLOBAL $G_dbpfx; - // (u.type & (CAST (X'00ff0000' as integer))) - - $user_sql = sprintf("UPDATE %susers SET (type, disa_reas) = ((%d & (CAST (X'00ff0000' as integer))), %d) -WHERE code = %d AND (type & CAST (X'%08x' as integer)) != 0 AND disa_reas = %d;", - $G_dbpfx, $type, $reas, $code, $old_type, $old_reas); + $user_sql = sprintf("UPDATE %susers SET (type, disa_reas) + = (type & ~(CAST (X'%08x' as integer)) | (CAST (X'%08x' as integer)), %d) + WHERE code = %d AND (type & (CAST (X'%08x' as integer))) + = (CAST (X'%08x' as integer)) AND disa_reas = %d;", + $G_dbpfx, $type, ($new_val ? $type : 0), $new_reas, + $code, $type, ($old_val ? $type : 0), $old_reas); if ( ! (($user_pg = $this->query($user_sql)) != FALSE && pg_affected_rows($user_pg) == 1) ) { return FALSE; @@ -646,6 +712,7 @@ WHERE code = %d AND (type & CAST (X'%08x' as integer)) != 0 AND disa_reas = %d;" /* * matches management */ + $codes_where = ""; $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) { @@ -666,12 +733,34 @@ WHERE code = %d AND (type & CAST (X'%08x' as integer)) != 0 AND disa_reas = %d;" log_crit(sprintf("bin5_points_save: failed at insert table order [%s]", $ord_sql)); break; } + $codes_where .= sprintf("%scode = %d", ($i == 0 ? "" : " OR "), $ucodes[$i]); } if ($i < $n) break; + + $cnt_sql = sprintf("UPDATE %susers SET (match_cnt, game_cnt) + = (match_cnt+1, game_cnt+1) WHERE %s;", + $G_dbpfx, $codes_where); + error_log($cnt_sql); + if (($cnt_pg = $this->query($cnt_sql)) == FALSE || pg_affected_rows($cnt_pg) != $n) { + log_crit(sprintf("bin5_points_save: failed increment match and game [%s]", $cnt_sql)); + break; + } } else { $mtc_obj = pg_fetch_object($mtc_pg,0); + + for ($i = 0 ; $i < $n ; $i++) { + $codes_where .= sprintf("%scode = %d", ($i == 0 ? "" : " OR "), $ucodes[$i]); + } + $cnt_sql = sprintf("UPDATE %susers SET (game_cnt) + = (game_cnt+1) WHERE %s;", + $G_dbpfx, $codes_where); + error_log($cnt_sql); + if (($cnt_pg = $this->query($cnt_sql)) == FALSE || pg_affected_rows($cnt_pg) != $n) { + log_crit(sprintf("bin5_points_save: failed increment game [%s]", $cnt_sql)); + break; + } } /* @@ -769,6 +858,28 @@ INSERT INTO %smails (code, ucode, type, tstamp, subj, body_txt, body_htm, hash) return (TRUE); } + function usersnet_bycode($owner, $target) + { + GLOBAL $G_dbpfx; + $ret = FALSE; + + $net_sql = sprintf("SELECT * FROM %susersnet WHERE owner = %d AND target = %d;", + $G_dbpfx, $owner, $target); + if (($net_pg = $this->query($net_sql)) == FALSE) { + return FALSE; + } + if (pg_numrows($net_pg) != 1) + return FALSE; + + $net_obj = pg_fetch_object($net_pg, 0); + + return (UsersNetItem::UsersNetItemFromRecord($net_obj)); + } + + function usersnet_default($owner, $target) + { + return (UsersNetItem::UsersNetItemDefaults($owner, $target)); + } } // End class BriskDB