source: branches/2.5/prototype/services/PostgreSQL.php @ 8232

Revision 8232, 17.9 KB checked in by douglas, 10 years ago (diff)

Ticket #0000 - Copiadas as alterações do Trunk. Versão final 2.5.1.

Line 
1<?php
2/**
3 *
4 * Copyright (C) 2012 Consórcio Expresso Livre - 4Linux (www.4linux.com.br) e Prognus Software Livre (www.prognus.com.br)
5 *
6 * This program is free software; you can redistribute it and/or modify it under
7 * the terms of the GNU Affero General Public License version 3 as published by
8 * the Free Software Foundation with the addition of the following permission
9 * added to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED
10 * WORK IN WHICH THE COPYRIGHT IS OWNED BY FUNAMBOL, FUNAMBOL DISCLAIMS THE
11 * WARRANTY OF NON INFRINGEMENT  OF THIRD PARTY RIGHTS.
12 *
13 * This program is distributed in the hope that it will be useful, but WITHOUT
14 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
15 * FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more
16 * details.
17 *
18 * You should have received a copy of the GNU Affero General Public License
19 * along with this program; if not, see www.gnu.org/licenses or write to
20 * the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
21 * MA 02110-1301 USA.
22 *
23 * This code is based on the OpenXchange Connector and on the Prognus pSync
24 * Connector both developed by the community and licensed under the GPL
25 * version 2 or above as published by the Free Software Foundation.
26 *
27 * You can contact Prognus Software Livre headquarters at Av. Tancredo Neves,
28 * 6731, PTI, Edifício do Saber, 3º floor, room 306, Foz do Iguaçu - PR - Brasil or at
29 * e-mail address prognus@prognus.com.br.
30 *
31 * Classe de abstração que implementa métodos de manipulação de banco de dados
32 * executando instruções SQL a partir de parâmetros passados pelos métodos.
33 *
34 * @package    Prototype
35 * @license    http://www.gnu.org/copyleft/gpl.html GPL
36 * @author     Consórcio Expresso Livre - 4Linux (www.4linux.com.br) e Prognus Software Livre (www.prognus.com.br)
37 * @version    2.4
38 * @sponsor    Caixa Econômica Federal
39 * @since      Arquivo disponibilizado na versão 2.4
40 */
41
42use prototype\api\Config as Config;
43
44class PostgreSQL implements Service
45{
46    private $con; //Conexão com o banco de dados
47    private $config; //Configuração
48    public  $error = false; //Armazena um erro caso ocorra
49    private $maps; //Cache de maps
50    private $tables;
51
52    public function find ( $uri, $justthese = false, $criteria = false )
53    {
54        $condition = '';
55
56        if(!isset($this->maps[$uri['concept']]) || !isset($this->tables[$uri['concept']]))
57        {
58            $this->maps[$uri['concept']] = Config::get($uri['concept'], 'PostgreSQL.mapping');
59            $this->tables[$uri['concept']] =  Config::get($uri['concept'],'PostgreSQL.concept');
60        }
61
62        $tables = $this->tables[$uri['concept']];
63
64        $justthese = self::parseJustthese($justthese, $this->maps[$uri['concept']] , $this->tables[$uri['concept']]);
65
66        if(isset($criteria['condition']))
67        {
68            $pc = $this->parseCondition($criteria['condition']);
69
70            if(is_array($pc))
71            {
72                if(!in_array($this->tables[$uri['concept']], $pc['tables']))
73                    $pc['tables'][] = $this->tables[$uri['concept']];
74
75                $tables = implode(',', $pc['tables'] );
76                $condition .= ' WHERE ' . $pc['conditions'];
77            }
78
79        }
80
81        $criteria = ($criteria !== false) ? $this->parseCriteria ( $criteria , $this->maps[$uri['concept']] , $condition , $this->tables[$uri['concept']]) : $condition;
82
83        return $this->execSql( 'SELECT '.$justthese['select'].' FROM '. $tables .' '.$criteria );
84    }
85
86   public function read ( $uri, $justthese = false , $criteria = false)
87   {
88       if(!isset($this->maps[$uri['concept']]) || !isset($this->tables[$uri['concept']]))
89       {
90           $this->maps[$uri['concept']] = Config::get($uri['concept'], 'PostgreSQL.mapping');
91           $this->tables[$uri['concept']] =  Config::get($uri['concept'],'PostgreSQL.concept');
92       }
93
94       $condition = ' WHERE '.$this->tables[$uri['concept']].'.'.$this->maps[$uri['concept']]['id'].' = \''.addslashes( $uri['id'] ).'\'';
95       $justthese = self::parseJustthese($justthese, $this->maps[$uri['concept']] , $this->tables[$uri['concept']]);
96       $tables = $this->tables[$uri['concept']];
97
98       if(isset($criteria['condition']))
99       {
100           $pc = $this->parseCondition($criteria['condition']);
101
102           if(is_array($pc))
103           {
104               if(!in_array($this->tables[$uri['concept']], $pc['tables']))
105                   $pc['tables'][] = $this->tables[$uri['concept']];
106
107               $tables = implode(',', $pc['tables'] );
108               $condition .= ' AND ' .  $pc['conditions'];
109           }
110       }
111
112       $criteria = ($criteria !== false) ? $this->parseCriteria ( $criteria , $this->maps[$uri['concept']] , $condition , $this->tables[$uri['concept']]) : $condition;
113
114       return $this->execSql( 'SELECT '.$justthese['select'].' FROM '. $tables . ' ' . $criteria , true );
115    }
116   
117    public function deleteAll ( $uri,   $justthese = false, $criteria = false ){
118            $map = Config::get($uri['concept'], 'PostgreSQL.mapping');
119        if(!self::parseCriteria ( $criteria , $map)) return false; //Validador para não apagar tabela inteira
120        return $this->execSql( 'DELETE FROM '.(Config::get($uri['concept'],'PostgreSQL.concept')).' '.self::parseCriteria ( $criteria ,$map) );
121    }
122
123    public function delete ( $uri, $justthese = false, $criteria = false ){
124            if(!isset($uri['id']) && !is_int($uri['id'])) return false; //Delete chamado apenas passando id inteiros
125        $map = Config::get($uri['concept'], 'PostgreSQL.mapping');
126        $criteria = ($criteria !== false) ? $this->parseCriteria ( $criteria , $map , ' WHERE '.$map['id'].' = \''.pg_escape_string( $uri['id'] ).'\'') : ' WHERE '.$map['id'].' = \''.pg_escape_string( $uri['id'] ).'\'';
127        return $this->execSql('DELETE FROM '.(Config::get($uri['concept'],'PostgreSQL.concept')).$criteria);
128    }
129
130    public function replace ( $uri,  $data, $criteria = false ){
131            $map = Config::get($uri['concept'], 'PostgreSQL.mapping');
132        return $this->execSql('UPDATE '.(Config::get($uri['concept'],'PostgreSQL.concept')).' '. self::parseUpdateData( $data ,$map).' '.self::parseCriteria($criteria , $map));
133    }
134
135    public function update ( $uri,  $data, $criteria = false ){
136            $map = Config::get($uri['concept'], 'PostgreSQL.mapping');
137        $criteria = ($criteria !== false) ?
138            $this->parseCriteria ( $criteria , $map , ' WHERE '.$map['id'].' = \''.pg_escape_string( $uri['id'] ).'\'') : ' WHERE '.$map['id'].' = \''.pg_escape_string( $uri['id'] ).'\'';
139
140        return $this->execSql('UPDATE '.(Config::get($uri['concept'],'PostgreSQL.concept')).' '. self::parseUpdateData( $data ,$map).$criteria);
141    }
142
143    public function create ( $uri,  $data ){   
144        return $this->execSql( 'INSERT INTO '.(Config::get($uri['concept'],'PostgreSQL.concept')).' '.self::parseInsertData( $data , $uri['concept'] ), true );
145    }
146
147    public function execSql( $sql, $unique = false )
148    {
149        if(!$this->con) $this->open( $this->config );
150
151        $rs = pg_query( $this->con, $sql );
152
153        switch( pg_num_rows( $rs ) )
154        {
155        case -1:
156          $this->error = pg_last_error ( $this->con );
157          return( false );
158
159        case 0:
160          return( pg_affected_rows( $rs ) ? true : array() );
161
162        default:
163          $return = array();
164
165          while( $row = pg_fetch_assoc( $rs ) )
166              $return[] = $row;
167
168          return( $unique ? $return[0] : $return );
169        }
170    }
171
172
173    //@DEPRECATED
174    public function execResultSql( $sql, $unique = false ){
175        return $this->execSql( $sql, $unique );
176    }
177
178    public function begin( $uri ) {
179   
180    if(!$this->con)
181        $this->open( $this->config );
182       
183        $this->error = false;
184    pg_query($this->con, "BEGIN WORK");
185    }
186
187    public function commit($uri ) {
188   
189    if( $this->error !== false )
190    {
191        $error = $this->error;
192        $this->error = false;
193
194        throw new Exception( $error );
195    }
196
197    pg_query($this->con, "COMMIT");
198
199    return( true );
200    }
201
202    public function rollback( $uri ){
203   
204    pg_query($this->con, "ROLLBACK");
205    }
206
207    public function open  ( $config ){
208               
209        $this->config = $config;
210       
211        $rs = '';
212        $rs .= ( isset($this->config['host']) && $this->config['host'] )  ? ' host='.$this->config['host'] : '' ;
213        $rs .= ( isset($this->config['user']) && $this->config['user'] )  ? ' user='.$this->config['user'] : '' ;
214        $rs .= ( isset($this->config['password']) && $this->config['password'] )  ? ' password='.$this->config['password'] : '' ;
215        $rs .= ( isset($this->config['dbname']) && $this->config['dbname'] )  ? ' dbname='.$this->config['dbname'] : '' ;
216        $rs .= ( isset($this->config['port']) && $this->config['port'] )  ? ' port='.$this->config['port'] : '' ;
217
218    if($this->con = pg_connect( $rs ))
219        return $this->con;
220
221    throw new Exception('It was not possible to enable the target connection!');
222    //$this->con = pg_connect('host='.$config['host'].' user='.$config['user'].' password='.$config['password'].' dbname='.$config['dbname'].'  options=\'--client_encoding=UTF8\'');
223    }
224
225    public function close(){
226
227        pg_close($this->con);
228           
229            $this->con = false;
230
231    }
232
233    public function setup(){}
234
235    public function teardown(){}
236
237    private static function parseInsertData( $data , $concept){
238     
239            $map = Config::get($concept, 'PostgreSQL.mapping');
240       
241        $ind = array();
242        $val = array();
243       
244        foreach ($data as $i => $v){
245                    if(!isset($map[$i])) continue;
246               
247            $ind[] = $map[$i];
248            $val[] = '\''.pg_escape_string($v).'\'';
249        }
250        return '('.implode(',', $ind).') VALUES ('.implode(',', $val).') RETURNING '.$map['id'].' as id';       
251    }
252   
253    private static function parseUpdateData( $data , &$map){
254
255        $d = array();
256        foreach ($data as $i => $v)
257            {
258                if(!isset($map[$i])) continue;
259               
260                $d[] = $map[$i].' = \''.pg_escape_string ($v).'\'';
261            }
262       
263        return 'SET '.implode(',', $d);
264    }
265
266    private static function parseCriteria( $criteria  , &$map , $query = '' ){
267
268        if( isset($criteria["filter"]) && $criteria["filter"] !== NULL )
269        {
270            /*
271          * ex: array   (
272          *       [0] 'OR',
273          *       [1] array( 'OR', array( array( '=', 'campo', 'valor' ) ),
274          *       [2] array( '=', 'campo' , 'valor' ),
275          *       [3] array( 'IN', 'campo', array( '1' , '2' , '3' ) )
276          *     )
277          * OR
278          *     array( '=' , 'campo' , 'valor' )
279        */
280               if($fc = self::parseFilter( $criteria['filter'] , $map))
281                    $query .= ($query === '') ?  'WHERE ('.$fc.')' : ' AND ('.$fc.')';
282        }
283        /*
284          * ex: array( 'table1' => 'table2' ,  'table1' => 'table2')
285          *     
286          */
287        if( isset($criteria["join"]) )
288        {
289        foreach ($criteria["join"] as $i => $v)
290            $query .= ' AND '.$i.' = '.$v.' ';
291        }
292       
293        if( isset($criteria["group"]) )
294        {
295            $query .= ' GROUP BY '.( is_array($criteria["group"]) ? implode(', ', $criteria["group"]) : $criteria["group"] ).' ';
296        }
297   
298        if( isset($criteria["order"]) )
299        {
300            //Verificar se os atributos para o ORDER BY serao ordenados em ordem decrescente [DESC]
301            $orderDesc = ( isset($criteria["orderDesc"]) && count($criteria["order"]) == count($criteria["orderDesc"]) ) ? $criteria["orderDesc"] : false;
302       
303            $query .= ' ORDER BY '.self::parseOrder( $criteria["order"], $map, $orderDesc ).' ';
304       
305        }
306
307        if( isset($criteria["limit"]) )
308        {
309            $query .= ' LIMIT '. $criteria["limit"] .' ';
310        }
311        if( isset($criteria["offset"]) )
312        {
313            $query .= ' OFFSET '. $criteria["offset"] .' ';
314        }
315       
316        return $query;
317    }
318   
319    private static function parseFilter( $filter ,&$map){
320   
321    if( !is_array( $filter ) || count($filter) <= 0) return null;
322               
323    $op = self::parseOperator( array_shift( $filter ) );
324       
325    if( is_array($filter[0]) )
326    {
327        $nested = array();
328
329        foreach( $filter as $i => $f )
330                if( $n = self::parseFilter( $f , $map))
331                    $nested[] = $n;
332
333               
334        return (count($nested) > 0 ) ? '('.implode( ' '.$op.' ', $nested ).')' : '';
335    }
336
337        if(!isset($map[$filter[0]])) return '';
338                 
339        $filter[0] = $map[$filter[0]];
340       
341    $igSuffix = $igPrefix = '';
342               
343    if( strpos( $op[0], 'i' ) === 0 )
344    {
345        $op[0] = substr( $op[0], 1 );
346        $filter[0] = 'upper("'.$filter[0].'")';
347        $igPrefix = 'upper(';
348        $igSuffix = ')';
349    }
350
351    if( is_array($filter[1]) )
352        return( $filter[0].' '.$op[0]." ($igPrefix'".implode( "'$igSuffix,$igPrefix'", array_map("pg_escape_string" , $filter[1]) )."'$igSuffix)" );
353
354    return( $filter[0].' '.$op[0]." $igPrefix'".$op[1].pg_escape_string( $filter[1] ).$op[2]."'$igSuffix" );
355    }
356
357    private static function parseOperator( $op ){
358   
359    switch(strtolower($op))
360    {
361        case 'and':
362        case 'or': return( $op );
363        case 'in': return array( $op );
364        case '!in': return array( 'NOT IN' );
365        case '^': return array( 'like', '%',  '' );
366        case '$': return array( 'like',  '', '%' );
367        case '*': return array( 'like', '%', '%' );
368        case 'i^': return array( 'ilike', '%',  '' );
369        case 'i$': return array( 'ilike',  '', '%' );
370        case 'i*': return array( 'ilike', '%', '%' );
371        default : return array( $op,  '',  '' );
372    }
373    }
374
375    static function parseJustthese($justthese , &$map , $table = '')
376    {
377
378        if(!is_array($justthese)) //Caso seja um full select pegar todas as keys
379            $justthese = array_keys($map);
380
381        $return = array();
382
383        if($table)
384            $table .= '.';
385
386        foreach ($justthese as &$value)
387        {
388            if(!isset($map[$value])) continue; //Escapa itens não existentes no mapa
389
390            if(is_array($map[$value]))
391                $return['deepness'][$value] = $map[$value];
392            else
393                $return['select'][] = $table . $map[$value] .' as "'. $value. '"';
394        }
395
396        $return['select'] = implode(', ', $return['select']);
397        return $return;
398    }
399
400    private function parseCondition( $condition )
401    {
402        $tables = array();
403        $conditions = '';
404
405            $matches = array();
406            if(preg_match_all('/\s*(AND|^)\s*([a-z]+)\.([a-z]+)\s+\=\s+([a-z]+)\.([a-z]+)(\s|$)+/i', $condition ,$matches,PREG_SET_ORDER))
407            {
408               foreach ($matches as $i => $v)
409               {
410                   if(!isset($this->maps[$v[2]]) || !isset($this->tables[$v[2]]))
411                   {
412                       $this->maps[$v[2]] = Config::get($v[2], 'PostgreSQL.mapping');
413                       $this->tables[$v[2]] =  Config::get($v[2],'PostgreSQL.concept');
414                   }
415                   if(!isset($this->maps[$v[4]]) || !isset($this->tables[$v[4]]))
416                   {
417                       $this->maps[$v[4]] = Config::get($v[4], 'PostgreSQL.mapping');
418                       $this->tables[$v[4]] =  Config::get($v[4],'PostgreSQL.concept');
419                   }
420
421                   if(isset($this->maps[$v[2]][$v[3]]) && isset($this->maps[$v[4]][$v[5]]))
422                       $conditions .= ' '. $v[1] .' '. $this->tables[$v[2]] . '.' . $this->maps[$v[2]][$v[3]] .' = '. $this->tables[$v[4]] . '.' . $this->maps[$v[4]][$v[5]];
423                   else
424                       continue;
425
426                   if(!in_array( $this->tables[$v[2]], $tables))
427                       $tables[] = $this->tables[$v[2]];
428
429                   if(!in_array( $this->tables[$v[4]], $tables))
430                       $tables[] = $this->tables[$v[4]];
431               }
432
433            }
434
435            if(preg_match_all('/\s*(AND|OR|^)\s*([a-z]+)\.([a-z]+)\s+([\=\>\<\!]+|like)+\s+([a-z0-9\/\+\=]+)(\s|$)+/i', $condition , $matches ,PREG_SET_ORDER))
436            {
437                foreach ($matches as $i => $v)
438                {
439                    if(!isset($this->maps[$v[2]]) || !isset($this->tables[$v[2]]))
440                    {
441                        $this->maps[$v[2]] = Config::get($v[2], 'PostgreSQL.mapping');
442                        $this->tables[$v[2]] =  Config::get($v[2],'PostgreSQL.concept');
443                    }
444
445                    if(isset($this->maps[$v[2]][$v[3]]))
446                        $conditions .= ' '. $v[1] .' '. $this->tables[$v[2]] . '.' . $this->maps[$v[2]][$v[3]] .' '.$v[4].' \''. pg_escape_string(base64_decode($v[5])) .'\'';
447                    else
448                        continue;
449
450                    if(!in_array( $this->tables[$v[2]], $tables))
451                        $tables[] = $this->tables[$v[2]];
452                }
453            }
454
455        return (count($tables) > 0 && count($conditions ) > 0) ? array('tables' => $tables , 'conditions' => $conditions ) : '' ;
456    }
457
458    private static function parseOrder($order , &$map, $orderDesc=false)
459    {
460
461        if($notArray = !is_array($order)) //Caso seja um full select pegar todas as keys
462            $order = array( $order );
463
464    //Caso seja feita ordenacao em ordem descrescente
465    //concatenar DESC em cada atributo
466    if($orderDesc !== false){
467        if(!is_array($orderDesc)){
468            $orderDesc = array( $orderDesc );
469        }
470        $order_count = count($order);
471        for($i=0; $i<$order_count; ++$i){
472            $order[$i] .= ($orderDesc[$i] === true) ? ' DESC' : '';
473        }
474    }
475
476        $return = array();
477
478        foreach ($order as &$value)
479        {
480            if(!isset($map[$value])) continue; //Escapa itens não existentes no mapa
481
482            $value = $map[$value];
483        }
484
485        return ( $notArray ?  $order[0] : implode(', ', $order) );
486    }
487
488}
489
490?>
Note: See TracBrowser for help on using the repository browser.