ILIAS  release_5-1 Revision 5.0.0-5477-g43f3e3fab5f
class.pdoDB.php
Go to the documentation of this file.
1<?php
2
3class pdoDB {
4
8 protected $pdo;
9 protected static $staticPbo;
13 protected $type_to_mysql_type = array(
14 'text' => 'VARCHAR',
15 'integer' => 'INT',
16 'float' => 'DOUBLE',
17 'date' => 'DATE',
18 'time' => 'TIME',
19 'datetime' => 'TIMESTAMP',
20 'clob' => 'LONGTEXT',
21
22 );
23
24
25 public function __construct() {
26 $this->pdo = new PDO('mysql:host=localhost;dbname=test_db;charset=utf8', 'travis', '');
27 $this->pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
28 $attr = PDO::MYSQL_ATTR_USE_BUFFERED_QUERY;
29 }
30
31
37 public function nextId($table_name) {
38 if ($this->tableExists($table_name . '_seq')) {
39 $table_seq = $table_name . '_seq';
40 $stmt = $this->pdo->prepare("SELECT * FROM $table_seq");
41 $stmt->execute();
42 $rows = $stmt->fetch(PDO::FETCH_ASSOC);
43 $stmt->closeCursor();
44
45 return count($rows) ? 0 : $rows['seq'];
46 } else {
47 // return $this->pdo->lastInsertId($table_name) + 1;
48 return 0;
49 }
50 }
51
52
59 public function createTable($table_name, $fields) {
60 $fields_query = $this->createTableFields($fields);
61 $query = "CREATE TABLE $table_name ($fields_query);";
62 $this->pdo->exec($query);
63 }
64
65
71 protected function createTableFields($fields) {
72 $query = "";
73 foreach ($fields as $name => $field) {
74 $type = $this->type_to_mysql_type[$field['type']];
75 $length = $field['length'];
76 $primary = isset($field['is_primary']) && $field['is_primary'] ? "PRIMARY KEY" : "";
77 $notnull = isset($field['is_notnull']) && $field['is_notnull'] ? "NOT NULL" : "";
78 $sequence = isset($field['sequence']) && $field['sequence'] ? "AUTO_INCREMENT" : "";
79 $query .= "$name $type ($length) $sequence $primary $notnull,";
80 }
81
82 return substr($query, 0, - 1);
83 }
84
85
90 public function addPrimaryKey($table_name, $primary_keys) {
91 $keys = implode($primary_keys);
92 $this->pdo->exec("ALTER TABLE $table_name ADD PRIMARY KEY ($keys)");
93 }
94
95
99 public function createSequence($table_name) {
100 //TODO
101 }
102
103
109 public function tableExists($table_name) {
110 $result = $this->pdo->prepare("SHOW TABLES LIKE :table_name");
111 $result->execute(array( ':table_name' => $table_name ));
112 $return = $result->rowCount();
113 $result->closeCursor();
114
115 return $return > 0;
116 }
117
118
125 public function tableColumnExists($table_name, $column_name) {
126 $statement = $this->pdo->query("SHOW COLUMNS FROM $table_name WHERE Field = '$column_name'");
127 $statement != NULL ? $statement->closeCursor() : "";
128
129 return $statement != NULL && $statement->rowCount() != 0;
130 }
131
132
138 public function addTableColumn($table_name, $column_name, $attributes) {
139 $col = array( $column_name => $attributes );
140 $col_str = $this->createTableFields($col);
141 $this->pdo->exec("ALTER TABLE $$table_name ADD $$col_str");
142 }
143
144
148 public function dropTable($table_name) {
149 $this->pdo->exec("DROP TABLE $table_name");
150 }
151
152
158 public function query($query) {
159 $res = $this->pdo->query($query);
160 $err = $this->pdo->errorInfo();
161
162 return $res;
163 }
164
165
171 public function fetchAll($query_result) {
172 return $query_result->fetchAll($query_result);
173 }
174
175
179 public function dropSequence($table_name) {
180 $table_seq = $table_name . "_seq";
181 if ($this->tableExists($table_seq)) {
182 $this->pdo->exec("DROP TABLE $table_seq");
183 }
184 }
185
186
191 public function dropTableColumn($table_name, $column_name) {
192 $this->pdo->exec("ALTER TABLE $$table_name DROP COLUMN $column_name");
193 }
194
195
201 public function renameTableColumn($table_name, $column_old_name, $column_new_name) {
202 $this->pdo->exec("alter table $table_name change $column_old_name $column_new_name");
203 }
204
205
210 public function insert($table_name, $values) {
211 $real = array();
212 foreach ($values as $val) {
213 $real[] = $this->quote($val[1], $val[0]);
214 }
215 $values = implode(",", $real);
216 $this->pdo->exec("INSERT INTO $table_name VALUES ($values)");
217 }
218
219
225 public function fetchObject($query_result) {
226 $res = $query_result->fetchObject();
227 if ($res == NULL) {
228 $query_result->closeCursor();
229
230 return NULL;
231 }
232
233 return $res;
234 }
235
236
242 public function update($table_name, $values, $where) {
243 $query = "UPDATE $table_name SET ";
244 foreach ($values as $key => $val) {
245 $qval = $this->quote($val[1], $val[0]);
246 $query .= "$key=$qval,";
247 }
248 $query = substr($query, 0, - 1) . " WHERE ";
249 foreach ($where as $key => $val) {
250 $qval = $this->quote($val[1], $val[0]);
251 $query .= "$key=$qval,";
252 }
253 $query = substr($query, 0, - 1);
254 $this->pdo->exec($query);
255 }
256
257
261 public function manipulate($query) {
262 $this->pdo->exec($query);
263 }
264
265
271 public function fetchAssoc($query_result) {
272 $res = $query_result->fetch(PDO::FETCH_ASSOC);
273 if ($res == NULL) {
274 $query_result->closeCursor();
275
276 return NULL;
277 }
278
279 return $res;
280 }
281
282
288 public function numRows($query_result) {
289 return $query_result->rowCount();
290 }
291
292
299 public function quote($value, $type) {
300 //TODO TYPE SENSITIVE.
301 return $this->pdo->quote($value);
302 }
303
304
311 public function addIndex($table_name, $index_name) {
312 return NULL;
313 }
314}
315
316?>
$result
renameTableColumn($table_name, $column_old_name, $column_new_name)
addTableColumn($table_name, $column_name, $attributes)
numRows($query_result)
nextId($table_name)
Definition: class.pdoDB.php:37
static $staticPbo
Definition: class.pdoDB.php:9
manipulate($query)
createTable($table_name, $fields)
experimental....
Definition: class.pdoDB.php:59
addIndex($table_name, $index_name)
dropTableColumn($table_name, $column_name)
__construct()
Definition: class.pdoDB.php:25
update($table_name, $values, $where)
$type_to_mysql_type
Definition: class.pdoDB.php:13
dropSequence($table_name)
createTableFields($fields)
Definition: class.pdoDB.php:71
query($query)
dropTable($table_name)
fetchAll($query_result)
fetchObject($query_result)
createSequence($table_name)
Definition: class.pdoDB.php:99
quote($value, $type)
addPrimaryKey($table_name, $primary_keys)
Definition: class.pdoDB.php:90
insert($table_name, $values)
tableColumnExists($table_name, $column_name)
tableExists($table_name)
fetchAssoc($query_result)