FrxPDO.inc 6.52 KB
Newer Older
1
2
<?php
/**
metzlerd's avatar
metzlerd committed
3
 * @file
metzlerd's avatar
metzlerd committed
4
 * General database engine used to do sql queries.
5
6
7
 *
 */

metzlerd's avatar
metzlerd committed
8
class FrxPDO extends FrxDataSource {
metzlerd's avatar
metzlerd committed
9

metzlerd's avatar
metzlerd committed
10

11
  private $db;
metzlerd's avatar
metzlerd committed
12
13
14
  public $debug;


15
16
17
18
19
20
  /**
   * Object constructor
   *
   * @param unknown_type $uri Database connection string.
   * @param string $repos_path Path to location of data block definitions
   */
21
22
  public function __construct($conf, $repos_path, $name) {
    parent::__construct($conf, $repos_path, $name);
metzlerd's avatar
metzlerd committed
23
    $uri = $conf['uri'];
metzlerd's avatar
metzlerd committed
24
    $this->debug = @$conf['debug'];
metzlerd's avatar
metzlerd committed
25
    if ($uri) {
metzlerd's avatar
metzlerd committed
26
      // Test for PDO suport
metzlerd's avatar
metzlerd committed
27
      if (!class_exists('PDO')) {
28
        $this->error('PDO support not installed.', 'PDO support not installed.');
metzlerd's avatar
metzlerd committed
29
30
        return;
      }
metzlerd's avatar
metzlerd committed
31

metzlerd's avatar
metzlerd committed
32
33
      $options = array();
      if (@$conf['mysql_charset']) {
metzlerd's avatar
metzlerd committed
34
        $options = array(
metzlerd's avatar
metzlerd committed
35
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES ' . $conf['mysql_charset'],
metzlerd's avatar
metzlerd committed
36
37
38
        );
      }

metzlerd's avatar
metzlerd committed
39
      // Test for driver support
metzlerd's avatar
metzlerd committed
40
41
      @list($prot, $c) = explode(':', $uri, 2);
      $drivers = PDO::getAvailableDrivers();
42
      $this->db_type = $prot;
43

metzlerd's avatar
metzlerd committed
44
      if ($drivers && (array_search($prot, $drivers)===FALSE)) {
metzlerd's avatar
metzlerd committed
45
        $msg = 'PDO driver support for ' . $prot . ' not installed';
46
        $this->error($msg, $msg);
metzlerd's avatar
metzlerd committed
47
        return;
metzlerd's avatar
metzlerd committed
48
      }
metzlerd's avatar
metzlerd committed
49
50
      try {
        if (isset($conf['user'])) {
metzlerd's avatar
metzlerd committed
51
          $db = new PDO($uri, $conf['user'], @$conf['password'], $options);
52
        }
metzlerd's avatar
metzlerd committed
53
        else {
metzlerd's avatar
metzlerd committed
54
          $db = new PDO($uri, NULL, NULL, $options);
metzlerd's avatar
metzlerd committed
55
56
57
        }
        $this->db = $db;
        if (!is_object($db)) {
58
          $this->error('Unknown error connecting to database ' . $uri);
metzlerd's avatar
metzlerd committed
59
60
        }
      } catch (PDOException $e) {
61
        $this->error('Unable to connect to database', $e->getMessage());
62
      }
63
      $this->db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
metzlerd's avatar
metzlerd committed
64
65
66

    }
    else {
67
      $this->error('No database connection string specified');
68
    }
metzlerd's avatar
metzlerd committed
69
70
71

    // Set up the stuff required to translate.
    $this->te = new FrxSyntaxEngine(FRX_SQL_TOKEN, ':', $this);
72
  }
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87

  public function parseConnectionStr() {
    $uri = @$this->conf['uri'];
    @list($prot, $conn) = explode(':', $uri, 2);
    $conn = str_replace(';', ' ', $conn);
    $info = array();
    foreach(explode(' ', $conn) as $pairs) {
      if (strpos($pairs, '=')!==FALSE) {
        list($key, $value) = @explode('=', $pairs, 2);
        $info[trim($key)] = trim($value);
      }
    }
    return $info;
  }

88
  /**
metzlerd's avatar
metzlerd committed
89
   * Get data based on file data block in the repository.
90
91
92
93
94
   *
   * @param String $block_name
   * @param Array $parm_data
   * @param Query $subQuery
   */
95
  public function sqlData($sql, $options = array()) {
96
    // Load the block from the file
metzlerd's avatar
metzlerd committed
97
98
    $db = $this->db;
    $xml ='';
99
100
101
    // Load the types array based on data
    $this->types = isset($options['type']) ? $options['type'] : array();

metzlerd's avatar
metzlerd committed
102
    if ($sql && $db) {
103
      $sql = $this->te->replace($sql);
metzlerd's avatar
metzlerd committed
104
105
106
107
108
109
110
111
112
113
114
115
      try {
        $rs = $db->query($sql);

      }
      catch (PDOException $e) {
        watchdog_exception('error',$e);
        $line = $e->getLine();
        $text = $e->getMessage();
       drupal_set_message($short, 'error', FALSE);
       return;

      }
116
      if (@$options['return_type'] == 'raw') {
117
        return $rs;
118
      }
metzlerd's avatar
metzlerd committed
119
      $xml = new SimpleXMLElement('<table/>');
120
      $e = $db->errorCode();
metzlerd's avatar
metzlerd committed
121

metzlerd's avatar
metzlerd committed
122
      if ($e != '00000') {
metzlerd's avatar
metzlerd committed
123
        $i = $db->errorInfo();
metzlerd's avatar
metzlerd committed
124
125
126
127
128
        $text =  $i[0] . ':' . $i[2];
        //if (user_access('build forena sql blocks')) {
        if (!$this->block_name) {
          $short = t('%e', array('%e' => $text));
        } else {
metzlerd's avatar
metzlerd committed
129
          $short = t('SQL Error in %b.sql', array('%b' => $this->block_name));
metzlerd's avatar
metzlerd committed
130
131
132
        }
        $this->error($short, $text);

metzlerd's avatar
metzlerd committed
133
      }
134
      else if ($rs && $rs->columnCount())  {
135
        if (@$options['return_type'] == 'raw') return $rs;
136
        $rownum = 0;
137
        foreach ($rs as $data) {
138
          $rownum++;
metzlerd's avatar
metzlerd committed
139
          $row_node = $xml->addChild('row');
140
          $row_node['num'] = $rownum;
141
          foreach ($data as $key => $value) {
metzlerd's avatar
metzlerd committed
142
            $row_node->addChild($key, htmlspecialchars($value));
143
144
145
          }
        }
      }
metzlerd's avatar
metzlerd committed
146
147

      if ($this->debug) {
148
        $d = '';
149
        if ($xml)  {
150
          $d = htmlspecialchars($xml->asXML());
151
152
        }
        $this->debug('SQL: ' . $sql, '<pre> SQL:' . $sql . "\n XML: " . $d . "/n</pre>");
metzlerd's avatar
metzlerd committed
153
154
      }
      return $xml;
155
    }
metzlerd's avatar
metzlerd committed
156

157
  }
metzlerd's avatar
metzlerd committed
158

159
160
161
162
163
164
165
  /**
   * Wrapper method cause some ODBC providers do not support
   * quoting.   We're going to assume the MSSQL method of quoting.
   * @param $value
   */
  public function quote($value) {
    $new_value =  $this->db->quote($value);
166
    if (($value!=='' || $value!==NULL) && !$new_value) {
metzlerd's avatar
metzlerd committed
167
      $value = "'" . str_replace("'", "''", $value) . "'";
168
169
    }
    else {
metzlerd's avatar
metzlerd committed
170
      $value = $new_value;
171
172
173
174
    }
    return $value;
  }

175
176
  /**
   * Implement custom SQL formatter to make sure that strings are properly escaped.
metzlerd's avatar
metzlerd committed
177
178
   * Ideally we'd replace this with something that handles prepared statements, but it
   * wouldn't work for
179
180
181
182
183
   *
   * @param unknown_type $value
   * @param unknown_type $key
   * @param unknown_type $data
   */
184
  public function format($value, $key) {
metzlerd's avatar
metzlerd committed
185
    $db = $this->db;
186
    $value = $this->parmConvert($key, $value);
187
    if ($db) {
metzlerd's avatar
metzlerd committed
188
      if ($value==='' || $value ===NULL || $value === array()) {
metzlerd's avatar
metzlerd committed
189
        $value = 'NULL';
190
      }
191
192
193
194
195
196
197
198
      elseif (is_int($value)) {
        $value = (int)$value;
        $value = (string)$value;
      }
      elseif (is_float($value)) {
        $value = (float)$value;
        $value = (string)$value;
      }
199
      elseif (is_array($value)) {
metzlerd's avatar
metzlerd committed
200
201
202
203
204
205
206
207
208
209
210
        if ($value == array()) {
          $value = 'NULL';
        }
        else {
          // Build a array of values string
          $i=0;
          $val ='';
          foreach ($value as $v) {
            $i++;
            if ($i!=1) {
              $val .= ',';
metzlerd's avatar
metzlerd committed
211
212
            }
            $val .= $this->quote($v);
metzlerd's avatar
metzlerd committed
213
          }
metzlerd's avatar
metzlerd committed
214
          $value = $val;
215
216
        }
      }
217
      else  $value =  $this->quote($value);
218
    }
219
    return (string)$value;
220
  }
metzlerd's avatar
metzlerd committed
221

222
223
224
225
226
227
228
229
230
231
  public function searchTables($str) {
    $str .= '%';
    $sql = $this->searchTablesSQL();
    if ($sql) {
      $st = $this->db->prepare($sql);
      if ($st) $st->execute(array(':str' => $str));
      if ($st) return $st->fetchAll(PDO::FETCH_COLUMN, 0);
    }
  }

232
233
234
235
236
237
238
239
240
241
242
  public function searchTableColumns($table, $str) {
    $str .= '%';
    $sql = $this->searchTableColumnsSQL();
    $info = $this->parseConnectionStr();
    $database = isset($info['dbname']) ? $info['dbname'] : @$info['database'];
    if ($sql) {
      $st = $this->db->prepare($sql);
      if ($st) $st->execute(array(':table' => $table, ':database' => $database, ':str' => $str));
      if ($st) return $st->fetchAll(PDO::FETCH_COLUMN, 0);
    }
  }
243
}