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 | ?> |
---|