1 <?php # vim:ts=2:sw=2:et:
2 /* For licensing and copyright terms, see the file named LICENSE */
4 interface IMTrackDBExtension {
5 /** allows the extension an opportunity to adjust the environment;
6 * register sqlite functions or otherwise tweak parameters */
7 function onHandleCreated(PDO $db);
10 class MTrackDBSchema_Table {
16 /* compares two tables; returns true if they are identical,
17 * false if the definitions are altered */
18 function sameAs(MTrackDBSchema_Table $other) {
19 if ($this->name != $other->name) {
20 throw new Exception("can only compare tables with the same name!");
22 foreach (array('fields', 'keys', 'triggers') as $propname) {
23 if (!is_array($this->{$propname})) continue;
24 foreach ($this->{$propname} as $f) {
25 if (!isset($other->{$propname}[$f->name])) {
26 # echo "$propname $f->name is new\n";
29 $o = clone $other->{$propname}[$f->name];
34 # echo "$propname $f->name are not equal\n";
40 if (!is_array($other->{$propname})) continue;
41 foreach ($other->{$propname} as $f) {
42 if (!isset($this->{$propname}[$f->name])) {
43 # echo "$propname $f->name was deleted\n";
53 interface IMTrackDBSchema_Driver {
54 function setDB(PDO $db);
55 function determineVersion();
56 function createTable(MTrackDBSchema_Table $table);
57 function alterTable(MTrackDBSchema_Table $from, MTrackDBSchema_Table $to);
58 function dropTable(MTrackDBSchema_Table $table);
61 class MTrackDBSchema_Generic implements IMTrackDBSchema_Driver {
63 var $typemap = array();
65 function setDB(PDO $db) {
69 function determineVersion() {
71 $q = $this->db->query('select version from mtrack_schema');
73 foreach ($q as $row) {
77 } catch (Exception $e) {
82 function computeFieldCreate($f) {
84 $str .= isset($this->typemap[$f->type]) ? $this->typemap[$f->type] : $f->type;
85 if (isset($f->nullable) && $f->nullable == '0') {
88 if (isset($f->default)) {
89 if (!strlen($f->default)) {
90 $str .= " DEFAULT ''";
92 $str .= " DEFAULT $f->default";
98 function computeIndexCreate($table, $k) {
107 return "CREATE $kt INDEX $k->name on $table->name (" . join(', ', $k->fields) . ")";
110 function createTable(MTrackDBSchema_Table $table)
112 echo "Create $table->name\n";
117 foreach ($table->fields as $f) {
118 if ($f->type == 'autoinc') {
121 $str = $this->computeFieldCreate($f);
125 if (is_array($table->keys)) foreach ($table->keys as $k) {
126 if ($k->type != 'primary') continue;
127 if ($pri_key !== null) continue;
128 $sql[] = "\tprimary key (" . join(', ', $k->fields) . ")";
131 $sql = "CREATE TABLE $table->name (\n" .
137 $this->db->exec($sql);
139 if (is_array($table->keys)) foreach ($table->keys as $k) {
140 if ($k->type == 'primary') continue;
141 $this->db->exec($this->computeIndexCreate($table, $k));
145 function alterTable(MTrackDBSchema_Table $from, MTrackDBSchema_Table $to)
147 /* if keys have changed, we drop the old key definitions before changing the columns */
149 echo "Need to alter $from->name\n";
150 throw new Exception("bang!");
153 function dropTable(MTrackDBSchema_Table $table)
155 echo "Drop $table->name\n";
156 $this->db->exec("drop table $table->name");
160 class MTrackDBSchema_SQLite extends MTrackDBSchema_Generic {
162 function determineVersion() {
163 /* older versions did not have a schema version table, so we dance
164 * around a little bit, but only for sqlite, as those older versions
165 * didn't support other databases */
167 $q = $this->db->query('select version from mtrack_schema');
169 foreach ($q as $row) {
173 } catch (Exception $e) {
176 /* do we have any tables at all? if we do, we treat that as schema
178 foreach ($this->db->query('select count(*) from sqlite_master') as $row) {
181 'create table mtrack_schema (version integer not null)');
188 var $typemap = array(
189 'autoinc' => 'INTEGER PRIMARY KEY AUTOINCREMENT',
192 function createTable(MTrackDBSchema_Table $table)
194 parent::createTable($table);
197 function alterTable(MTrackDBSchema_Table $from, MTrackDBSchema_Table $to)
199 $tname = $from->name . '_' . uniqid();
202 foreach ($to->fields as $f) {
203 if ($f->type == 'autoinc') {
206 $str = $this->computeFieldCreate($f);
210 $sql = "CREATE TEMPORARY TABLE $tname (\n" .
214 $this->db->exec($sql);
216 /* copy old data into this table */
217 $sql = "INSERT INTO $tname (";
219 foreach ($from->fields as $f) {
220 if (!isset($to->fields[$f->name])) continue;
223 $sql .= join(', ', $names);
224 $sql .= ") SELECT " . join(', ', $names) . " from $from->name";
227 $this->db->exec($sql);
229 $this->db->exec("DROP TABLE $from->name");
230 $this->createTable($to);
231 $sql = "INSERT INTO $from->name (";
233 foreach ($from->fields as $f) {
234 if (!isset($to->fields[$f->name])) continue;
237 $sql .= join(', ', $names);
238 $sql .= ") SELECT " . join(', ', $names) . " from $tname";
240 $this->db->exec($sql);
241 $this->db->exec("DROP TABLE $tname");
247 class MTrackDBSchema_pgsql extends MTrackDBSchema_Generic {
248 var $typemap = array(
249 'autoinc' => 'SERIAL UNIQUE',
250 'timestamp' => 'timestamp with time zone',
254 function alterTable(MTrackDBSchema_Table $from, MTrackDBSchema_Table $to)
259 /* if keys have changed, we drop the old key definitions before changing the columns */
260 if (is_array($from->keys)) foreach ($from->keys as $k) {
261 if (!isset($to->keys[$k->name]) || $to->keys[$k->name] != $k) {
262 if ($k->type == 'primary') {
263 $actions[] = "DROP CONSTRAINT {$from->name}_pkey";
265 $sql[] = "DROP INDEX $k->name";
270 foreach ($from->fields as $f) {
271 if (!isset($to->fields[$f->name])) {
272 $actions[] = "DROP COLUMN $f->name";
276 foreach ($to->fields as $f) {
277 if (isset($from->fields[$f->name])) continue;
278 $actions[] = "ADD COLUMN " . $this->computeFieldCreate($f);
281 /* changed and new keys */
282 if (is_array($from->keys)) foreach ($from->keys as $k) {
283 if (isset($to->keys[$k->name]) && $to->keys[$k->name] != $k) {
284 if ($k->type == 'primary') {
285 $actions[] = "ADD primary key (" . join(', ', $k->fields) . ")";
287 $sql[] = $this->computeIndexCreate($to, $k);
291 if (is_array($to->keys)) foreach ($to->keys as $k) {
292 if (isset($from->keys[$k->name])) continue;
293 if ($k->type == 'primary') {
294 $actions[] = "ADD primary key (" . join(', ', $k->fields) . ")";
296 $sql[] = $this->computeIndexCreate($to, $k);
300 if (count($actions)) {
301 $sql[] = "ALTER TABLE $from->name " . join(",\n", $actions);
303 echo "Need to alter $from->name\n";
306 foreach ($sql as $s) {
312 class MTrackDBSchema {
317 function __construct($filename) {
318 $s = simplexml_load_file($filename);
320 $this->version = (int)$s['version'];
322 /* fabricate a table to hold the schema info */
323 $table = new MTrackDBSchema_Table;
324 $table->name = 'mtrack_schema';
326 $f->name = 'version';
327 $f->type = 'integer';
329 $table->fields[$f->name] = $f;
330 $this->tables[$table->name] = $table;
332 foreach ($s->table as $t) {
333 $table = new MTrackDBSchema_Table;
334 $table->name = (string)$t['name'];
336 foreach ($t->field as $f) {
338 foreach ($f->attributes() as $k => $v) {
339 $F->{(string)$k} = (string)$v;
341 if (isset($f->comment)) {
342 $F->comment = (string)$f->comment;
344 $table->fields[$F->name] = $F;
346 foreach ($t->key as $k) {
348 $K->fields = array();
349 if (isset($k['type'])) {
350 $K->type = (string)$k['type'];
352 $K->type = 'primary';
354 foreach ($k->field as $f) {
355 $K->fields[] = (string)$f;
357 if (isset($k['name'])) {
358 $K->name = (string)$k['name'];
360 $K->name = sprintf("idx_%s_%s", $table->name, join('_', $K->fields));
362 $table->keys[$K->name] = $K;
365 $this->tables[$table->name] = $table;
367 foreach ($s->post as $p) {
368 $this->post[(string)$p['driver']] = (string)$p;
371 /* apply custom ticket fields */
372 if (isset($this->tables['tickets'])) {
373 $table = $this->tables['tickets'];
374 $custom = MTrackTicket_CustomFields::getInstance();
375 foreach ($custom->fields as $field) {
377 $f->name = $field->name;
379 $table->fields[$f->name] = $f;
387 static $extensions = array();
389 static $query_strings = array();
391 static function registerExtension(IMTrackDBExtension $ext) {
392 self::$extensions[] = $ext;
395 // given a unix timestamp, return a value timestamp string
396 // suitable for use with the database
397 static function unixtime($unix) {
398 list($unix) = explode('.', $unix, 2);
403 throw new Exception("unix time $unix is too small\n");
405 $d = date_create("@$unix", new DateTimeZone('UTC'));
406 // 2008-12-22T05:42:42.285445Z
407 if (!is_object($d)) {
408 throw new Exception("failed to create date for time $unix");
410 return $d->format('Y-m-d\TH:i:s.u\Z');
413 static function get() {
414 if (self::$db == null) {
415 $dsn = MTrackConfig::get('core', 'dsn');
417 $dsn = 'sqlite:' . MTrackConfig::get('core', 'dblocation');
420 $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
423 if ($db->getAttribute(PDO::ATTR_DRIVER_NAME) == 'sqlite') {
424 $db->sqliteCreateAggregate('mtrack_group_concat',
425 array('MTrackDB', 'group_concat_step'),
426 array('MTrackDB', 'group_concat_final'));
428 $db->sqliteCreateFunction('mtrack_cleanup_attachments',
429 array('MTrackAttachment', 'attachment_row_deleted'));
432 foreach (self::$extensions as $ext) {
433 $ext->onHandleCreated($db);
439 static function lastInsertId($tablename, $keyfield) {
440 if (!strlen($tablename) || !strlen($keyfield)) {
441 throw new Exception("missing tablename or keyfield");
443 if (self::$db->getAttribute(PDO::ATTR_DRIVER_NAME) == 'pgsql') {
444 return self::$db->lastInsertId($tablename . '_' . $keyfield . '_seq');
446 return self::$db->lastInsertId();
450 static function group_concat_step($context, $rownum, $value)
452 if (!is_array($context)) {
459 static function group_concat_final($context, $rownum)
461 if ($context === null) {
465 return join(", ", $context);
468 static function esc($str) {
469 return "'" . str_replace("'", "''", $str) . "'";
472 /* issue a query, passing optional parameters */
473 static function q($sql) {
475 if (isset(self::$query_strings[$sql])) {
476 self::$query_strings[$sql]++;
478 self::$query_strings[$sql] = 1;
480 $params = func_get_args();
481 array_shift($params);
483 # echo "<br>SQL: $sql\n";
487 if (count($params)) {
488 $q = $db->prepare($sql);
489 $q->execute($params);
491 $q = $db->query($sql);
493 } catch (Exception $e) {
494 echo $e->getMessage();