[5307] | 1 | <?php |
---|
| 2 | |
---|
| 3 | class DatabaseInfo { |
---|
| 4 | private $_conn; |
---|
| 5 | private $_dbhost; |
---|
| 6 | private $_dbuser; |
---|
| 7 | private $_dbpassword; |
---|
| 8 | private $_dbname; |
---|
| 9 | var $_sqlerror; |
---|
| 10 | var $_parser; |
---|
| 11 | |
---|
| 12 | public function __construct($dbname,$dbhost,$dbuser,$dbpassword){ |
---|
| 13 | $this->_dbhost = $dbhost; |
---|
| 14 | $this->_dbuser = $dbuser; |
---|
| 15 | $this->_dbpassword = $dbpassword; |
---|
| 16 | $this->_dbname = $dbname; |
---|
| 17 | $this->_parser = new SqlParser(""); |
---|
| 18 | $this->_conn = $this->conectar(); |
---|
| 19 | } |
---|
| 20 | |
---|
| 21 | public function conectar() { |
---|
| 22 | $connstring = "dbname=" . $this->_dbname . " host=" . $this->_dbhost . " user=" . $this->_dbuser . " password=" . $this->_dbpassword . ""; |
---|
| 23 | //echo $connstring; |
---|
| 24 | $conn = pg_connect($connstring); |
---|
| 25 | $this->_conn = $conn; |
---|
| 26 | |
---|
| 27 | $this->_parser->bloquearPalavra("controle_acesso_banco",true); |
---|
| 28 | $this->_parser->bloquearPalavra("controle_acesso_banco_usuarios",true); |
---|
| 29 | $this->_parser->bloquearPalavra("controle_acesso_banco_historico",true); |
---|
| 30 | |
---|
| 31 | //$this->bloquearFuncoes(true); |
---|
| 32 | |
---|
| 33 | return $conn; |
---|
| 34 | } |
---|
| 35 | |
---|
| 36 | public function bloquearFuncoes($bloqueio) { |
---|
| 37 | $res_funcoes = $this->getFuncoes(); |
---|
| 38 | while ($funcao = pg_fetch_object($res_funcoes)) { |
---|
| 39 | $nome_funcao = $funcao->proname; |
---|
| 40 | $this->_parser->bloquearPalavra($nome_funcao . "(",$bloqueio); |
---|
| 41 | } |
---|
| 42 | } |
---|
| 43 | |
---|
| 44 | public function getErro() { |
---|
| 45 | return $this->_sqlerror; |
---|
| 46 | } |
---|
| 47 | |
---|
| 48 | public function executar($sql,$transaction = true) { |
---|
| 49 | $error = ""; |
---|
| 50 | $this->_sqlerror = ""; |
---|
| 51 | |
---|
| 52 | $this->_parser->setSql($sql); |
---|
| 53 | |
---|
| 54 | $ret_parser = $this->_parser->verificaSql(); |
---|
| 55 | |
---|
| 56 | if ($ret_parser === false) { |
---|
| 57 | $error = $this->_parser->getErro(); |
---|
| 58 | } |
---|
| 59 | |
---|
| 60 | $sql = $this->_parser->getSql(); |
---|
| 61 | if ($error == "") { |
---|
| 62 | if ($transaction) { |
---|
| 63 | pg_query($this->_conn,"begin;"); |
---|
| 64 | } |
---|
| 65 | $res = pg_query($this->_conn,$sql); |
---|
| 66 | $error = pg_last_error($this->_conn); |
---|
| 67 | if ($transaction) { |
---|
| 68 | pg_query($this->_conn,"rollback;"); |
---|
| 69 | } |
---|
| 70 | |
---|
| 71 | } |
---|
| 72 | if ($error == "") { |
---|
| 73 | return $res; |
---|
| 74 | } else { |
---|
| 75 | $this->_sqlerror = $error; |
---|
| 76 | return false; |
---|
| 77 | } |
---|
| 78 | } |
---|
| 79 | |
---|
| 80 | public function fetch_array($recordset){ |
---|
| 81 | return pg_fetch_array($recordset); |
---|
| 82 | } |
---|
| 83 | |
---|
| 84 | public function fetch_all($recordset) { |
---|
| 85 | $process = array(); |
---|
| 86 | while ($rsatt = $this->fetch_array($recordset)){ |
---|
| 87 | $process[] = $rsatt; |
---|
| 88 | } |
---|
| 89 | return $process; |
---|
| 90 | } |
---|
| 91 | |
---|
| 92 | public function getDbIndices($schemaname,$tblname) { |
---|
| 93 | $sqlstr = "select pg_get_indexdef(i.oid) as indice, x.indisprimary FROM pg_index x JOIN pg_class c ON c.oid = x.indrelid JOIN pg_class i ON i.oid = x.indexrelid WHERE c.relname=(select relname from pg_stat_user_tables where relname = '$tblname' and schemaname = '$schemaname') "; |
---|
| 94 | $resu = $this->executar($sqlstr); |
---|
| 95 | return $resu; |
---|
| 96 | } |
---|
| 97 | |
---|
| 98 | public function getDbConstraints($schemaname,$tblname) { |
---|
| 99 | $sqlstr = "select pg_get_constraintdef(ct.oid) AS constraint FROM pg_class c inner join pg_catalog.pg_constraint ct ON (c.oid = ct.conrelid) WHERE c.relname=(select relname from pg_stat_user_tables where relname = '$tblname' and schemaname = '$schemaname')"; |
---|
| 100 | $resu = $this->executar($sqlstr); |
---|
| 101 | return $resu; |
---|
| 102 | } |
---|
| 103 | |
---|
| 104 | |
---|
| 105 | |
---|
| 106 | |
---|
| 107 | public function getAttTable($schemaname,$tblname){ |
---|
| 108 | |
---|
| 109 | $sqlstr = "SELECT |
---|
| 110 | pg_attribute.attnum AS index, |
---|
| 111 | attname AS field, |
---|
| 112 | typname AS type, |
---|
| 113 | atttypmod-4 as length, |
---|
| 114 | NOT attnotnull AS null, |
---|
| 115 | adsrc AS def, |
---|
| 116 | (select coalesce(d.description,'') as desc from pg_description d where d.objoid=attrelid and d.objsubid=attnum) as descricao_coluna |
---|
| 117 | FROM |
---|
| 118 | pg_attribute, |
---|
| 119 | pg_class, |
---|
| 120 | pg_type, |
---|
| 121 | pg_attrdef |
---|
| 122 | WHERE |
---|
| 123 | pg_class.oid=attrelid |
---|
| 124 | AND pg_type.oid=atttypid |
---|
| 125 | AND attnum>0 |
---|
| 126 | AND pg_class.oid=adrelid |
---|
| 127 | AND adnum=attnum |
---|
| 128 | AND atthasdef='t' |
---|
| 129 | AND lower(relname)= (select relname from pg_stat_user_tables where relname = '$tblname' and schemaname = '$schemaname') |
---|
| 130 | UNION |
---|
| 131 | SELECT |
---|
| 132 | pg_attribute.attnum AS index, |
---|
| 133 | attname AS field, |
---|
| 134 | typname AS type, |
---|
| 135 | atttypmod-4 as length, |
---|
| 136 | NOT attnotnull AS null, |
---|
| 137 | '' AS def, |
---|
| 138 | (select coalesce(d.description,'') as desc from pg_description d where d.objoid=attrelid and d.objsubid=attnum) as descricao_coluna |
---|
| 139 | FROM |
---|
| 140 | pg_attribute, |
---|
| 141 | pg_class, |
---|
| 142 | pg_type |
---|
| 143 | WHERE |
---|
| 144 | pg_class.oid=attrelid |
---|
| 145 | AND pg_type.oid=atttypid |
---|
| 146 | AND attnum>0 |
---|
| 147 | AND atthasdef='f' |
---|
| 148 | AND lower(relname)=(select relname from pg_stat_user_tables where relname = '$tblname' and schemaname = '$schemaname') |
---|
| 149 | order by |
---|
| 150 | index;"; |
---|
| 151 | |
---|
| 152 | //echo $sqlstr; |
---|
| 153 | $resu = $this->executar($sqlstr); |
---|
| 154 | //print_r($resu); |
---|
| 155 | return $resu; |
---|
| 156 | } |
---|
| 157 | |
---|
| 158 | public function getDbViews($schemaname = "",$viewname = ""){ |
---|
| 159 | $add = ""; |
---|
| 160 | if ($schemaname != "") { |
---|
| 161 | $add = " and schemaname ilike '%$schemaname%'"; |
---|
| 162 | } |
---|
| 163 | if ($schemaname != "") { |
---|
| 164 | $add .= " and viewname ilike '%$viewname%'"; |
---|
| 165 | } |
---|
| 166 | $sqlstr = "select schemaname, viewname, definition from pg_views where schemaname not in ('information_schema','pg_catalog') $add "; |
---|
| 167 | $resu = $this->executar($sqlstr); |
---|
| 168 | //print_r($resu); |
---|
| 169 | return $resu; |
---|
| 170 | } |
---|
| 171 | |
---|
| 172 | public function getQueries() { |
---|
| 173 | |
---|
| 174 | $sql = "select datname, procpid, usename, query_start, |
---|
| 175 | ( CASE WHEN client_addr is null then |
---|
| 176 | 'LOCAL' |
---|
| 177 | ELSE |
---|
| 178 | client_addr::text |
---|
| 179 | END) AS client_addr, |
---|
| 180 | current_query, |
---|
| 181 | |
---|
| 182 | (to_char(((timeofday()::TIMESTAMP)-query_start),'hh24:mi:ss')||' - |
---|
| 183 | Inicio: '||to_char(query_start,'hh24:mi:ss') ) AS duracao, |
---|
| 184 | ( CASE WHEN timeofday()::TIMESTAMP-query_start > |
---|
| 185 | INTERVAL '30 seconds' THEN |
---|
| 186 | 'LENTA' |
---|
| 187 | ELSE |
---|
| 188 | 'NORMAL' |
---|
| 189 | END) AS lentas, |
---|
| 190 | ( CASE WHEN waiting = 't' THEN |
---|
| 191 | 'SIM' |
---|
| 192 | ELSE |
---|
| 193 | 'NAO' |
---|
| 194 | END) AS waiting |
---|
| 195 | from pg_stat_activity |
---|
| 196 | where current_query not ilike '<IDLE>' |
---|
| 197 | order by query_start asc"; |
---|
| 198 | |
---|
| 199 | //$sql = "SELECT pg_stat_activity.datid, pg_stat_activity.datname, pg_stat_activity.procpid, pg_stat_activity.usesysid, pg_stat_activity.usename, pg_stat_activity.current_query, pg_stat_activity.query_start, pg_stat_activity.backend_start, pg_stat_activity.client_addr, pg_stat_activity.client_port FROM pg_stat_activity WHERE (pg_stat_activity.current_query <> '<IDLE>'::text) ORDER BY pg_stat_activity.query_start DESC;"; |
---|
| 200 | $resu = $this->executar($sql); |
---|
| 201 | return $resu; |
---|
| 202 | } |
---|
| 203 | |
---|
| 204 | public function getDbTables() { |
---|
| 205 | |
---|
| 206 | $sqlstr = "SELECT c.relname as tablename, |
---|
| 207 | |
---|
| 208 | pg_catalog.pg_get_userbyid(c.relowner) AS dono, |
---|
| 209 | |
---|
| 210 | pg_catalog.obj_description(c.oid, 'pg_class') AS comentario, reltuples::integer as registros, |
---|
| 211 | |
---|
| 212 | (SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE pt.oid=c.reltablespace) AS tablespace, |
---|
| 213 | 'public' AS schemaname |
---|
| 214 | |
---|
| 215 | FROM pg_catalog.pg_class c |
---|
| 216 | |
---|
| 217 | LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace |
---|
| 218 | |
---|
| 219 | WHERE c.relkind = 'r' |
---|
| 220 | |
---|
| 221 | AND nspname='public' |
---|
| 222 | |
---|
| 223 | ORDER BY c.relname"; |
---|
| 224 | |
---|
| 225 | $resu = $this->executar($sqlstr); |
---|
| 226 | return $resu; |
---|
| 227 | } |
---|
| 228 | |
---|
| 229 | public function getFuncoes($funnome = '') { |
---|
| 230 | |
---|
| 231 | if ($funnome != "") { $add .= " AND proname || '(' || pg_catalog.oidvectortypes(pg_proc.proargtypes) || ')' ilike '$funnome%' "; } |
---|
| 232 | |
---|
| 233 | |
---|
| 234 | $sql = "select pg_proc.oid as oid, proname, nspname, pg_catalog.pg_get_userbyid(proowner) AS proowner, pg_catalog.obj_description(pg_proc.oid, 'pg_proc') AS procomment, pg_catalog.oidvectortypes(pg_proc.proargtypes) AS proarguments from pg_namespace,pg_proc where pg_proc.pronamespace=pg_namespace.oid and nspname in ('public','representacoes') $add order by nspname, proname; "; |
---|
| 235 | |
---|
| 236 | $resu = $this->executar($sql); |
---|
| 237 | return $resu; |
---|
| 238 | } |
---|
| 239 | |
---|
| 240 | public function getDbFuncao($funcao,$funnome = '') { |
---|
| 241 | |
---|
| 242 | if ($funcao != "") { $add = " AND pc.oid = $funcao "; } |
---|
| 243 | if ($funnome != "") { $add .= " AND proname || '(' || pg_catalog.oidvectortypes(pc.proargtypes) || ')' ilike '$funnome' "; } |
---|
| 244 | |
---|
| 245 | $sqlstr = "SELECT |
---|
| 246 | pc.oid AS prooid, |
---|
| 247 | proname, |
---|
| 248 | lanname as prolanguage, |
---|
| 249 | pg_catalog.format_type(prorettype, NULL) as proresult, |
---|
| 250 | prosrc, |
---|
| 251 | probin, |
---|
| 252 | proretset, |
---|
| 253 | proisstrict, |
---|
| 254 | provolatile, |
---|
| 255 | prosecdef, |
---|
| 256 | pg_catalog.oidvectortypes(pc.proargtypes) AS proarguments, |
---|
| 257 | proargnames AS proargnames, |
---|
| 258 | pg_catalog.pg_get_userbyid(proowner) AS proowner, |
---|
| 259 | pg_catalog.obj_description(pc.oid, 'pg_proc') AS procomment, |
---|
| 260 | proname || '(' || pg_catalog.oidvectortypes(pc.proargtypes) || ')' as nomefuncao |
---|
| 261 | FROM pg_catalog.pg_proc pc, pg_catalog.pg_language pl |
---|
| 262 | WHERE |
---|
| 263 | pc.prolang = pl.oid |
---|
| 264 | $add |
---|
| 265 | "; |
---|
| 266 | $resu = $this->executar($sqlstr); |
---|
| 267 | return $resu; |
---|
| 268 | } |
---|
| 269 | |
---|
| 270 | public function getDbFuncaoSrc($funoid = "",$funnome = "") { |
---|
| 271 | $this->bloquearFuncoes(false); |
---|
| 272 | $res_funcoes = $this->getDbFuncao($funoid,$funnome); |
---|
| 273 | |
---|
| 274 | $textcontent = ""; |
---|
| 275 | |
---|
| 276 | if (pg_num_rows($res_funcoes)) { |
---|
| 277 | $dados = $this->fetch_array($res_funcoes); |
---|
| 278 | |
---|
| 279 | $textcontent = " |
---|
| 280 | -- Function: " . $dados["proname"]. "(" . $dados["proarguments"]. ") |
---|
| 281 | |
---|
| 282 | -- DROP FUNCTION " . $dados["proname"]. "(" . $dados["proarguments"]. "); |
---|
| 283 | |
---|
| 284 | CREATE OR REPLACE FUNCTION " . $dados["proname"]. "(" . $dados["proarguments"]. ") RETURNS " . $dados["proresult"]. " AS |
---|
| 285 | \$BODY\$"; |
---|
| 286 | $textcontent .= $dados["prosrc"]; |
---|
| 287 | $textcontent .= "\$BODY\$ |
---|
| 288 | LANGUAGE 'pltcl' VOLATILE |
---|
| 289 | COST 100; |
---|
| 290 | ALTER FUNCTION " . $dados["proname"]. "(" . $dados["proarguments"]. ") OWNER TO " . $dados["proowner"].";"; |
---|
| 291 | |
---|
| 292 | if ($dados["procomment"] != "") { |
---|
| 293 | $textcontent .= " |
---|
| 294 | COMMENT ON FUNCTION " . $dados["proname"]. "(" . $dados["proarguments"]. ") IS '" . $dados["procomment"] . "';"; |
---|
| 295 | } |
---|
| 296 | } |
---|
| 297 | |
---|
| 298 | return $textcontent; |
---|
| 299 | |
---|
| 300 | } |
---|
| 301 | |
---|
| 302 | public function getPrefixo($prefixo) { |
---|
| 303 | $sqlstr = "select |
---|
| 304 | relname AS tabela, |
---|
| 305 | attname AS valor |
---|
| 306 | from |
---|
| 307 | pg_attribute, |
---|
| 308 | pg_class |
---|
| 309 | where |
---|
| 310 | not attname in ('cmax','xmax','xmin','cmin','ctid','tableoid') |
---|
| 311 | and attrelid=relfilenode |
---|
| 312 | and relhaspkey |
---|
| 313 | and (attname ilike '%$prefixo%' or relname ilike '%$prefixo%') order by relname, attname"; |
---|
| 314 | $resu = $this->executar($sqlstr); |
---|
| 315 | return $resu; |
---|
| 316 | } |
---|
| 317 | |
---|
| 318 | function getDBTriggers() { |
---|
| 319 | $sql = "select tgname, tgfoid, tgrelid from pg_trigger where substring(trim(tgname),0,4) != 'RI_' and substring(trim(tgname),0,4) != 'pg_' "; |
---|
| 320 | $resu = $this->executar($sql); |
---|
| 321 | return $resu; |
---|
| 322 | } |
---|
| 323 | |
---|
| 324 | } |
---|
| 325 | |
---|
| 326 | ?> |
---|