source: trunk/prototype/services/PostgreSQL.php @ 7655

Revision 7655, 13.6 KB checked in by douglasz, 11 years ago (diff)

Ticket #3236 - Melhorias de performance no codigo do Expresso.
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   
50    public function find ( $uri, $justthese = false, $criteria = false ){
51                   
52        $map =  Config::get($uri['concept'], 'PostgreSQL.mapping');
53       
54    $criteria = ($criteria !== false) ? $this->parseCriteria ( $criteria , $map) : '';
55
56    $justthese = self::parseJustthese($justthese, $map);
57
58    return $this->execSql( 'SELECT '.$justthese['select'].' FROM '. (Config::get($uri['concept'],'PostgreSQL.concept')) .' '.$criteria );
59    }
60
61   public function read ( $uri, $justthese = false , $criteria = false){
62   
63      $map =  Config::get($uri['concept'], 'PostgreSQL.mapping');   
64      $justthese = self::parseJustthese($justthese, $map);
65      $criteria = ($criteria !== false) ? $this->parseCriteria ( $criteria , $map , ' WHERE '.$map['id'].' = \''.pg_escape_string( $uri['id'] ).'\'') : ' WHERE '.$map['id'].' = \''.pg_escape_string( $uri['id'] ).'\'';
66   
67      return $this->execSql( 'SELECT '.$justthese['select'].' FROM '. (Config::get($uri['concept'],'PostgreSQL.concept')) .$criteria , true );
68    }
69   
70    public function deleteAll ( $uri,   $justthese = false, $criteria = false ){
71            $map = Config::get($uri['concept'], 'PostgreSQL.mapping');
72        if(!self::parseCriteria ( $criteria , $map)) return false; //Validador para não apagar tabela inteira
73        return $this->execSql( 'DELETE FROM '.(Config::get($uri['concept'],'PostgreSQL.concept')).' '.self::parseCriteria ( $criteria ,$map) );
74    }
75
76    public function delete ( $uri, $justthese = false, $criteria = false ){
77            if(!isset($uri['id']) && !is_int($uri['id'])) return false; //Delete chamado apenas passando id inteiros
78        $map = Config::get($uri['concept'], 'PostgreSQL.mapping');
79        $criteria = ($criteria !== false) ? $this->parseCriteria ( $criteria , $map , ' WHERE '.$map['id'].' = \''.pg_escape_string( $uri['id'] ).'\'') : ' WHERE '.$map['id'].' = \''.pg_escape_string( $uri['id'] ).'\'';
80        return $this->execSql('DELETE FROM '.(Config::get($uri['concept'],'PostgreSQL.concept')).$criteria);
81    }
82
83    public function replace ( $uri,  $data, $criteria = false ){
84            $map = Config::get($uri['concept'], 'PostgreSQL.mapping');
85        return $this->execSql('UPDATE '.(Config::get($uri['concept'],'PostgreSQL.concept')).' '. self::parseUpdateData( $data ,$map).' '.self::parseCriteria($criteria , $map));
86    }
87       
88    public function update ( $uri,  $data, $criteria = false ){
89            $map = Config::get($uri['concept'], 'PostgreSQL.mapping');
90        $criteria = ($criteria !== false) ? $this->parseCriteria ( $criteria , $map , ' WHERE '.$map['id'].' = \''.pg_escape_string( $uri['id'] ).'\'') : ' WHERE '.$map['id'].' = \''.pg_escape_string( $uri['id'] ).'\'';
91
92        return $this->execSql('UPDATE '.(Config::get($uri['concept'],'PostgreSQL.concept')).' '. self::parseUpdateData( $data ,$map).$criteria);
93    }
94
95    public function create ( $uri,  $data ){   
96        return $this->execSql( 'INSERT INTO '.(Config::get($uri['concept'],'PostgreSQL.concept')).' '.self::parseInsertData( $data , $uri['concept'] ), true );
97    }
98
99    public function execSql( $sql, $unique = false )
100    {
101        if(!$this->con) $this->open( $this->config );
102
103        $rs = pg_query( $this->con, $sql );
104
105        switch( pg_num_rows( $rs ) )
106        {
107        case -1:
108          $this->error = pg_last_error ( $this->con );
109          return( false );
110
111        case 0:
112          return( pg_affected_rows( $rs ) ? true : array() );
113
114        default:
115          $return = array();
116
117          while( $row = pg_fetch_assoc( $rs ) )
118              $return[] = $row;
119
120          return( $unique ? $return[0] : $return );
121        }
122    }
123
124
125    //@DEPRECATED
126    public function execResultSql( $sql, $unique = false ){
127        return $this->execSql( $sql, $unique );
128    }
129
130    public function begin( $uri ) {
131   
132    if(!$this->con)
133        $this->open( $this->config );
134       
135        $this->error = false;
136    pg_query($this->con, "BEGIN WORK");
137    }
138
139    public function commit($uri ) {
140   
141    if( $this->error !== false )
142    {
143        $error = $this->error;
144        $this->error = false;
145
146        throw new Exception( $error );
147    }
148
149    pg_query($this->con, "COMMIT");
150
151    return( true );
152    }
153
154    public function rollback( $uri ){
155   
156    pg_query($this->con, "ROLLBACK");
157    }
158
159    public function open  ( $config ){
160               
161        $this->config = $config;
162       
163        $rs = '';
164        $rs .= ( isset($this->config['host']) && $this->config['host'] )  ? ' host='.$this->config['host'] : '' ;
165        $rs .= ( isset($this->config['user']) && $this->config['user'] )  ? ' user='.$this->config['user'] : '' ;
166        $rs .= ( isset($this->config['password']) && $this->config['password'] )  ? ' password='.$this->config['password'] : '' ;
167        $rs .= ( isset($this->config['dbname']) && $this->config['dbname'] )  ? ' dbname='.$this->config['dbname'] : '' ;
168        $rs .= ( isset($this->config['port']) && $this->config['port'] )  ? ' port='.$this->config['port'] : '' ;
169
170    if($this->con = pg_connect( $rs ))
171        return $this->con;
172
173    throw new Exception('It was not possible to enable the target connection!');
174    //$this->con = pg_connect('host='.$config['host'].' user='.$config['user'].' password='.$config['password'].' dbname='.$config['dbname'].'  options=\'--client_encoding=UTF8\'');
175    }
176
177    public function close(){
178
179        pg_close($this->con);
180           
181            $this->con = false;
182
183    }
184
185    public function setup(){}
186
187    public function teardown(){}
188
189    private static function parseInsertData( $data , $concept){
190     
191            $map = Config::get($concept, 'PostgreSQL.mapping');
192       
193        $ind = array();
194        $val = array();
195       
196        foreach ($data as $i => $v){
197                    if(!isset($map[$i])) continue;
198               
199            $ind[] = $map[$i];
200            $val[] = '\''.pg_escape_string($v).'\'';
201        }
202        return '('.implode(',', $ind).') VALUES ('.implode(',', $val).') RETURNING '.$map['id'].' as id';       
203    }
204   
205    private static function parseUpdateData( $data , &$map){
206                                       
207        $d = array();
208        foreach ($data as $i => $v)
209            {
210                if(!isset($map[$i])) continue;
211               
212                $d[] = $map[$i].' = \''.pg_escape_string ($v).'\'';
213            }
214       
215        return 'SET '.implode(',', $d);
216    }
217
218    private static function parseCriteria( $criteria  , &$map , $query = '' ){               
219   
220        if( isset($criteria["filter"]) && $criteria["filter"] !== NULL )
221        {
222            /*
223          * ex: array   (
224          *       [0] 'OR',
225          *       [1] array( 'OR', array( array( '=', 'campo', 'valor' ) ),
226          *       [2] array( '=', 'campo' , 'valor' ),
227          *       [3] array( 'IN', 'campo', array( '1' , '2' , '3' ) )
228          *     )
229          * OR
230          *     array( '=' , 'campo' , 'valor' )
231        */
232                $query .= ($query === '') ?  'WHERE ('.self::parseFilter( $criteria['filter'] , $map).')' : ' AND ('.self::parseFilter( $criteria['filter'] , $map).')';
233        }
234        /*
235          * ex: array( 'table1' => 'table2' ,  'table1' => 'table2')
236          *     
237          */
238        if( isset($criteria["join"]) )
239        {
240        foreach ($criteria["join"] as $i => $v)
241            $query .= ' AND '.$i.' = '.$v.' ';
242        }
243       
244        if( isset($criteria["group"]) )
245        {
246            $query .= ' GROUP BY '.( is_array($criteria["group"]) ? implode(', ', $criteria["group"]) : $criteria["group"] ).' ';
247        }
248   
249        if( isset($criteria["order"]) )
250        {
251            //Verificar se os atributos para o ORDER BY serao ordenados em ordem decrescente [DESC]
252            $orderDesc = ( isset($criteria["orderDesc"]) && count($criteria["order"]) == count($criteria["orderDesc"]) ) ? $criteria["orderDesc"] : false;
253       
254            $query .= ' ORDER BY '.self::parseOrder( $criteria["order"], $map, $orderDesc ).' ';
255       
256        }
257
258        if( isset($criteria["limit"]) )
259        {
260            $query .= ' LIMIT '. $criteria["limit"] .' ';
261        }
262        if( isset($criteria["offset"]) )
263        {
264            $query .= ' OFFSET '. $criteria["offset"] .' ';
265        }
266       
267        return $query;
268    }
269   
270    private static function parseFilter( $filter ,&$map){
271   
272    if( !is_array( $filter ) || count($filter) <= 0) return null;
273               
274    $op = self::parseOperator( array_shift( $filter ) );
275       
276    if( is_array($filter[0]) )
277    {
278        $nested = array();
279
280        foreach( $filter as $i => $f )
281                if( $n = self::parseFilter( $f , $map))
282                    $nested[] = $n;
283
284               
285        return (count($nested) > 0 ) ? '('.implode( ' '.$op.' ', $nested ).')' : '';
286    }
287
288        if(!isset($map[$filter[0]])) return '';
289                 
290        $filter[0] = $map[$filter[0]];
291       
292    $igSuffix = $igPrefix = '';
293               
294    if( strpos( $op[0], 'i' ) === 0 )
295    {
296        $op[0] = substr( $op[0], 1 );
297        $filter[0] = 'upper("'.$filter[0].'")';
298        $igPrefix = 'upper(';
299        $igSuffix = ')';
300    }
301
302    if( is_array($filter[1]) )
303        return( $filter[0].' '.$op[0]." ($igPrefix'".implode( "'$igSuffix,$igPrefix'", array_map("pg_escape_string" , $filter[1]) )."'$igSuffix)" );
304
305    return( $filter[0].' '.$op[0]." $igPrefix'".$op[1].pg_escape_string( $filter[1] ).$op[2]."'$igSuffix" );
306    }
307
308    private static function parseOperator( $op ){
309   
310    switch(strtolower($op))
311    {
312        case 'and':
313        case 'or': return( $op );
314        case 'in': return array( $op );
315        case '!in': return array( 'NOT IN' );
316        case '^': return array( 'like', '%',  '' );
317        case '$': return array( 'like',  '', '%' );
318        case '*': return array( 'like', '%', '%' );
319        case 'i^': return array( 'ilike', '%',  '' );
320        case 'i$': return array( 'ilike',  '', '%' );
321        case 'i*': return array( 'ilike', '%', '%' );
322        default : return array( $op,  '',  '' );
323    }
324    }
325   
326    private static function parseJustthese($justthese , &$map)
327    {
328                 
329        if(!is_array($justthese)) //Caso seja um full select pegar todas as keys
330            $justthese = array_keys($map);
331
332        $return = array();
333
334        foreach ($justthese as &$value)
335        {
336            if(!isset($map[$value])) continue; //Escapa itens não existentes no mapa
337
338            if(is_array($map[$value]))
339                $return['deepness'][$value] = $map[$value];
340            else
341                $return['select'][] = $map[$value] .' as "'. $value. '"';
342        }
343       
344        $return['select'] = implode(', ', $return['select']);
345        return $return; 
346    }
347
348    private static function parseOrder($order , &$map, $orderDesc=false)
349    {
350
351        if($notArray = !is_array($order)) //Caso seja um full select pegar todas as keys
352            $order = array( $order );
353
354    //Caso seja feita ordenacao em ordem descrescente
355    //concatenar DESC em cada atributo
356    if($orderDesc !== false){
357        if(!is_array($orderDesc)){
358            $orderDesc = array( $orderDesc );
359        }
360
361        for($i=0; $i<count($order); ++$i){
362            $order[$i] .= ($orderDesc[$i] === true) ? ' DESC' : '';
363        }
364    }
365
366        $return = array();
367
368        foreach ($order as &$value)
369        {
370            if(!isset($map[$value])) continue; //Escapa itens não existentes no mapa
371
372            $value = $map[$value];
373        }
374
375        return ( $notArray ?  $order[0] : implode(', ', $order) );
376    }
377
378}
379
380?>
Note: See TracBrowser for help on using the repository browser.