ILIAS  eassessment Revision 61809
 All Data Structures Namespaces Files Functions Variables Groups Pages
ilSCORM13DB.php
Go to the documentation of this file.
1 <?php
2 /*
3  +-----------------------------------------------------------------------------+
4  | ILIAS open source |
5  +-----------------------------------------------------------------------------+
6  | Copyright (c) 1998-2007 ILIAS open source, University of Cologne |
7  | |
8  | This program is free software; you can redistribute it and/or |
9  | modify it under the terms of the GNU General Public License |
10  | as published by the Free Software Foundation; either version 2 |
11  | of the License, or (at your option) any later version. |
12  | |
13  | This program is distributed in the hope that it will be useful, |
14  | but WITHOUT ANY WARRANTY; without even the implied warranty of |
15  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
16  | GNU General Public License for more details. |
17  | |
18  | You should have received a copy of the GNU General Public License |
19  | along with this program; if not, write to the Free Software |
20  | Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. |
21  +-----------------------------------------------------------------------------+
22 */
23 
30 class ilSCORM13DB
31 {
32 
33  // variables for static usage
34  public static $DB;
35  private static $DSN;
36  private static $TYPE;
37  private static $BRACKETS;
38  private static $LAST_ID;
39  private static $ERRORS;
40  private static $BRACKETS_LIST = array(
41  'mysql' => '``',
42  'sqlite' => '""',
43  ); // for table or field names containing whitespace or other special chars
44 
45  // similar variables for dynamic usage
46  private $db;
47  private $dsn;
48  private $type;
49  private $brackets;
50  private $lastId;
51  private static $errors;
52 
53  private static $SQLCOMMAND = array();
54 
55 
56  public function __construct($dsn, $login, $password, $type='mysql')
57  {
58  try {
59  self::$DB = new PDO($dsn, $login, $password);
60  } catch (PDOException $e) {
61  error_log("Error!: " . $e->getMessage());
62  }
63  $this->dsn = $dsn;
64  $this->brackets = self::$BRACKETS_LIST[$type];
65  $this->type = is_null($type) ? substr($dsn, 0, strpos($dsn, ':')) : $type;
66  $this->brackets = self::$BRACKETS_LIST[$this->type];
67 
68  }
69 
70  public function init($dsn, $login, $password, $type='mysql')
71  {
72  try {
73  self::$DB = new PDO($dsn, $login, $password);
74  } catch (PDOException $e) {
75  error_log("Error!: " . $e->getMessage());
76  }
77  self::$DSN = $dsn;
78  self::$TYPE = is_null($type) ? substr($dsn, 0, strpos($dsn, ':')) : $type;
79  self::$BRACKETS = self::$BRACKETS_LIST[self::$TYPE];
80  }
81 
82  public function addQueries()
83  {
84  require_once("./Modules/Scorm2004/classes/ilSCORM13Player_mysql.php");
85  }
86 
87  public function getLastId()
88  {
89  return self::getDB()->lastInsertId();
90  return $this && $this instanceof ilSCORM13DB
91  ? $this->lastId
92  : self::$LAST_ID;
93  }
94 
95  public function getType()
96  {
97  return $this && $this instanceof ilSCORM13DB
98  ? $this->type
99  : self::$TYPE;
100  }
101 
102  private function getDSN()
103  {
104  return $this && $this instanceof ilSCORM13DB
105  ? $this->dsn
106  : self::$DSN;
107  }
108 
109  private function getDB()
110  {
111  return $this && $this instanceof ilSCORM13DB
112  ? $this->db
113  : self::$DB;
114  }
115 
116  private function escapeName($name)
117  {
118  $b = $this && $this instanceof ilSCORM13DB
119  ? $this->brackets
120  : self::$BRACKETS;
121  return $b[0] . preg_replace('/[^\w_.-]/', '_', $name) . $b[1];
122  }
123 
124  private function setLastId($id)
125  {
126  $this && $this instanceof ilSCORM13DB
127  ? $this->lastId = $id
128  : self::$LAST_ID = $id;
129  }
130 
131  public function & getRecord($tableOrView, $idname, $idvalue)
132  {
133  if (!is_string($idname) || !is_numeric($idvalue))
134  {
135  return false;
136  }
137  $q = 'SELECT * FROM ' . self::escapeName($tableOrView) . ' WHERE ' . self::escapeName($idname) . '=' . $idvalue;
138  $r = self::query($q);
139  return $r[0];
140  }
141 
142  public function setRecord($tableOrView, $row, $idname=null)
143  {
144  $r = self::setRecords($tableOrView, array($row), $idname);
145  return $r[0];
146  }
147 
148  public function & getRecords($tableOrView, $idname=null, $idvalues=null, $order=null, $paging=null)
149  {
150  $tableOrView = self::escapeName($tableOrView);
151  $q = "SELECT * FROM $tableOrView";
152  if (is_string($idname) && is_array($idvalues))
153  {
154  $idname = self::escapeName($idname);
155  foreach ($idvalues as &$idvalue)
156  {
157  if (!is_numeric($idvalue)) return false;
158  $idvalue = "$idname=$idvalue";
159  }
160  $q .= ' ' . implode(' OR ', $idvalues);
161  }
162  return self::query($q, null, $order, $paging);
163  }
164 
165  function setRecords($tableOrView, $rows, $idname=null)
166  {
167  //$d = new PDO(self::getDSN());
168  $d = self::getDB();
169  $r = 0;
170  if (!is_array($row = $rows[0]))
171  {
172  return false;
173  }
174  $tableOrView = self::escapeName($tableOrView);
175  $q = array();
176  if (is_string($idname)) {
177  $idvalue = $row[$idname];
178  $idname = self::escapeName($idname);
179  }
180  $u = is_numeric($idvalue);
181  if ($u)
182  {
183  foreach (array_keys($row) as $k)
184  {
185  $q[] = self::escapeName($k) . '=?';
186  }
187  $q = implode(', ', $q);
188  $q = "UPDATE $tableOrView SET $q WHERE $idname=$idvalue";
189  }
190  else
191  {
192  foreach (array_keys($row) as $k)
193  {
194  $q[] = self::escapeName($k);
195  }
196  $q = implode(', ', $q);
197  $q = "INSERT INTO $tableOrView ($q) VALUES (" . str_pad('', count($row)*2-1, '?,') . ')';
198  }
199  //echo "<br>$q";
200  if ($s = $d->prepare($q))
201  {
202  $type = self::getType();
203  foreach ($rows as &$row)
204  {
205  $row = $s->execute(array_values($row));
206  $arr = $s->errorInfo();
207  file_put_contents('/tmp/sql.log', implode("\n", array('', date('c'), $sql, var_export($q, true),var_export($arr,true))), FILE_APPEND);
208 
209  if (!$u && is_string($idname) && $row)
210  {
211  $row = $d->lastInsertId();
212  self::setLastId($row);
213  }
214  }
215  }
216  unset($d);
217  return $rows;
218  }
219 
220  public function removeRecord($table, $idname, $idvalue)
221  {
222  self::removeRecords($table, $idname, array($idvalue));
223  }
224 
225  public function removeRecords($tables, $idnames, $idvalues)
226  {
227  if (!is_array($idvalues))
228  {
229  return false;
230  }
231  //$d = new PDO(self::getDSN());
232  $d = self::getDB();
233  if (!is_array($tables))
234  {
235  $tables = array($tables);
236  }
237  if (!is_array($idnames))
238  {
239  $idnames = array($idnames);
240  }
241  $tables = array_reverse($tables);
242  foreach ($tables as $i => &$table)
243  {
244  $table = self::escapeName($table);
245  $idname = $idnames[$i % count($idnames)];
246  if (!is_string($idname)) return false;
247  $idname = self::escapeName($idname);
248  $q = "DELETE FROM $table WHERE $idname=?";
249  foreach ($idvalues as $idvalue)
250  {
251  $table = self::exec($q, $idvalue);
252  }
253  }
254  unset($d);
255  return array_reverse($tables);
256  }
257 
258  public function & query($query, $params=null, $order=null, $paging=null, $fetchType=PDO::FETCH_ASSOC)
259  {
260  $r = array();
261  $d = self::getDB();
262  self::addQueries();
263  $q = array(self::$SQLCOMMAND[$query] ? self::$SQLCOMMAND[$query] : $query);
264  if (is_array($order))
265  {
266  $o = array();
267  foreach ($order as $k => $v)
268  {
269  $o[] = self::escapeName($k) . ' ' . ($v ? 'ASC' : 'DESC');
270  }
271  $q[] = 'ORDER BY ' . implode(', ', $o);
272  }
273  if (is_array($paging))
274  {
275  if (is_int($o = $paging['count']))
276  {
277  // MySQL Style
278  $q[] = "LIMIT $o";
279  if (is_int($o = $paging['offset']))
280  {
281  $q[] = "OFFSET $o";
282  }
283  }
284  }
285  $q = implode(' ', $q);
286  $s = $d->prepare($q);
287  $s->execute($params);
288  $arr = $s->errorInfo();
289  file_put_contents('/tmp/sql.log', implode("\n", array('', date('c'), $q, var_export($params, true),var_export($arr,true))), FILE_APPEND);
290  $r = $s->fetchAll($fetchType);
291  unset($d);
292  return $r;
293  }
294 
304  public function exec($queries, $params=null, &$result = null)
305  {
306  if (!is_array($queries))
307  {
308  $r = self::exec(array($queries), $params);
309  return $r[0];
310  }
311  if (!is_array($params))
312  {
313  $params = array();
314  }
315  if (!is_array(current($params)))
316  {
317  $params = array($params);
318  }
319  //$d = new PDO(self::getDSN());
320  $d = self::getDB();
321  self::addQueries();
322  foreach ($queries as $i => &$q)
323  {
324  if ($s = $d->prepare($sql = (self::$SQLCOMMAND[$q] ? self::$SQLCOMMAND[$q] : $q)))
325  {
326  error_log("SQL-Command: ".self::$SQLCOMMAND[$q]);
327  $q = 0;
328  $r = array();
329  $ps = is_array($params) ? $params[$i % count($params)] : null;
330  if (!is_array(current($ps)))
331  {
332  $ps = array($ps);
333  }
334  foreach ($ps as $p)
335  {
336  $q+=$s->execute($p);
337  $arr = $s->errorInfo();
338  file_put_contents('/tmp/sql.log', implode("\n", array('', date('c'), $sql, var_export($p, true),var_export($arr,true))), FILE_APPEND);
339 
340  if (is_array($result))
341  {
342  count($queries)<2
343  ? $result = $s->fetchAll(PDO::FETCH_ASSOC)
344  : $result[] = $s->fetchAll(PDO::FETCH_ASSOC);
345  }
346  }
347  }
348  else
349  {
350  // prepare failed
351  }
352  }
353  unset($d);
354  return $queries;
355  }
356 
357 
358  function begin()
359  {
360  self::getDB()->beginTransaction();
361  self::$ERRORS = 0;
362  }
363 
364  function commit()
365  {
366  self::$ERRORS
367  ? self::getDB()->rollBack()
368  : self::getDB()->commit();
369  return self::$ERRORS;
370  }
371 
372  function rollback()
373  {
374  self::getDB()->rollBack();
375  }
376 
377  //convert an ILIAS DB-DSN to a PDO DSN
378  function il_to_pdo_dsn($il_dsn)
379  {
380  $pattern = '/([a-z]+)(:\/\/)([^:]*)(:)([^@]*)(@)([^\/]+)(\/)(.*)/i';
381  preg_match($pattern, $il_dsn, $matches);
382  $pdo_dsn[0]=$matches[1].":dbname=".$matches[9].";host=".$matches[7];
383  $pdo_dsn[1]=$matches[3];
384  $pdo_dsn[2]=$matches[5];
385  return $pdo_dsn;
386  }
387 
388 
389 }
390 
391 ?>