ILIAS  release_5-1 Revision 5.0.0-5477-g43f3e3fab5f
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
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
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
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 {
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();
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();
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();
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?>
$result
static $BRACKETS_LIST
Definition: ilSCORM13DB.php:40
& getRecord($tableOrView, $idname, $idvalue)
removeRecords($tables, $idnames, $idvalues)
static $ERRORS
Definition: ilSCORM13DB.php:39
__construct($dsn, $login, $password, $type='mysql')
Definition: ilSCORM13DB.php:56
static $DB
Definition: ilSCORM13DB.php:34
removeRecord($table, $idname, $idvalue)
static $DSN
Definition: ilSCORM13DB.php:35
il_to_pdo_dsn($il_dsn)
exec($queries, $params=null, &$result=null)
exec('delete...') exec('delete... id=?', array(231)) exec('insert... id=?', array(array(231),...
static $errors
Definition: ilSCORM13DB.php:51
init($dsn, $login, $password, $type='mysql')
Definition: ilSCORM13DB.php:70
static $BRACKETS
Definition: ilSCORM13DB.php:37
static $LAST_ID
Definition: ilSCORM13DB.php:38
& query($query, $params=null, $order=null, $paging=null, $fetchType=PDO::FETCH_ASSOC)
setRecords($tableOrView, $rows, $idname=null)
escapeName($name)
static $SQLCOMMAND
Definition: ilSCORM13DB.php:53
setRecord($tableOrView, $row, $idname=null)
static $TYPE
Definition: ilSCORM13DB.php:36
& getRecords($tableOrView, $idname=null, $idvalues=null, $order=null, $paging=null)
$r
Definition: example_031.php:79
$params
Definition: example_049.php:96