1: <?php
 2: 
 3: namespace Himedia\PDOTools;
 4: 
 5: /**
 6:  * Some useful functions.
 7:  *
 8:  * Copyright (c) 2014 HiMedia
 9:  * Licensed under the GNU Lesser General Public License v3 (LGPL version 3).
10:  *
11:  * @package Himedia\PDOTools
12:  * @copyright 2014 HiMedia
13:  * @license http://www.gnu.org/licenses/lgpl.html
14:  */
15: class Tools
16: {
17: 
18:     /**
19:      * Converts associative array to PostgreSQL hstore syntax.
20:      *
21:      * @param  array  $aData associative array
22:      * @return string hstore conversion of specified array
23:      * @see http://www.postgresql.org/docs/9.0/static/hstore.html
24:      */
25:     public static function assocArrayToHstore(array $aData)
26:     {
27:         $aHstore = array();
28:         foreach ($aData as $sKey => $sValue) {
29:             if (is_null($sValue)) {
30:                 $aHstore[] = sprintf('"%s" => NULL', $sKey);
31:             } else {
32:                 $aHstore[] = sprintf('"%s" => "%s"', $sKey, str_replace('"', "\\\"", $sValue));
33:             }
34:         }
35:         return sprintf("'%s'::hstore", implode(', ', $aHstore));
36:     }
37: 
38:     /**
39:      * Export associative result set to CSV file or string.
40:      *
41:      * @param  array  $aRows      Associative fetch all.
42:      * @param  string $sCsvPath   Path to export CSV. Set to '' to export into returning string.
43:      * @param  string $sDelimiter Field delimiter
44:      * @param  string $sEnclosure Field enclosure
45:      * @return string If no $sCsvPath, then CSV formatted string without the trailing newline, else empty string.
46:      */
47:     public static function exportToCSV(array $aRows, $sCsvPath, $sDelimiter = ',', $sEnclosure = '"')
48:     {
49:         if (empty($sCsvPath)) {
50:             $hFile = fopen('php://temp/maxmemory:' . (10*1024*1024), 'w');
51:         } else {
52:             $hFile = fopen($sCsvPath, 'w');
53:         }
54: 
55:         $bIsHeaderPrinted = false;
56:         foreach ($aRows as $aRow) {
57:             if (! $bIsHeaderPrinted) {
58:                 $bIsHeaderPrinted = true;
59:                 fputcsv($hFile, array_keys($aRow), $sDelimiter, $sEnclosure);
60:             }
61:             fputcsv($hFile, $aRow, $sDelimiter, $sEnclosure);
62:         }
63: 
64:         if (empty($sCsvPath)) {
65:             rewind($hFile);
66:             $sCSV = stream_get_contents($hFile);
67:         } else {
68:             $sCSV = '';
69:         }
70:         fclose($hFile);
71:         return rtrim($sCSV, "\n");
72:     }
73: 
74:     /**
75:      * Normalize specified query removing comments and multiple white spaces.
76:      *
77:      * @param $sRawQuery
78:      * @return string query normalized by removing comments and multiple white spaces.
79:      * @see http://stackoverflow.com/a/13823184
80:      */
81:     public static function normalizeQuery($sRawQuery)
82:     {
83:         $sSqlComments = '@(([\'"]).*?[^\\\]\2)|((?:\#|--).*?$|/\*(?:[^/*]|/(?!\*)|\*(?!/)|(?R))*\*\/)\s*|(?<=;)\s+@ms';
84:         $sNormalizedQuery = preg_replace('/\s+/', ' ', trim(preg_replace($sSqlComments, '$1', $sRawQuery)));
85:         return $sNormalizedQuery;
86:     }
87: }
88: