utils/sqlite.php

   1 <?php
   2 /* $Id$ */
   3
   4 include_once 'config.inc.php';
   5 require_once 'persistens.php';
   6 require_once 'helper.php';
   7
   8 class SQLite extends PDO implements WebcalSupport {
   9
  10     function __construct($dns = NULL) {
  11         if ($dns) {
  12             $db_dns = "sqlite:$dns";
  13         }
  14         else {
  15             $db_dns = 'sqlite:'.TOP_FOLDER.'/'.DNS;
  16         }
  17         try {
  18                 parent::__construct($db_dns, 0600);
  19                 parent::exec("PRAGMA foreign_keys = OFF");
  20                         //parent::setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  21                         //parent::setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
  22                 }
  23         catch (PDOException $ex) {
  24             throw new Exception($ex->getMessage());
  25         }
  26     }
  27
  28         private function getMsg($resource) {
  29                 $err = $resource->errorInfo();
  30                 if (count($err) > 2)
  31                         return $err[2];
  32                 else
  33                         return "";
  34         }
  35
  36     function initDatabase($name, $pwd, $uid) {
  37     }
  38
  39     function createDatabase($name) {
  40         $sql = "select count(*) as found from sqlite_master where
  41                  type = 'table' and name = 'user'";
  42         $sth = $this->prepare($sql);
  43         if ($sth->errorCode() && $sth->errorCode() != '00000') {
  44             $err = "$sql\n";
  45             $err .= $this->getMsg($sth)."\n";
  46             throw new Exception($err);
  47         }
  48         $sth->execute();
  49         if ($sth->errorCode() && $sth->errorCode() != '00000') {
  50             $err = "$sql\n";
  51             $err .= $this->getMsg($sth)."\n";
  52             throw new Exception($err);
  53         }
  54         $res = $sth->fetch();
  55         if ($res['found'] == 0) {
  56             $this->beginTransaction();
  57             include 'db_create.sqlite.php';
  58             $db = make_sql_stm();
  59             foreach ($db as $sql) {
  60                 $sth->closeCursor();
  61                 $sth = $this->prepare($sql);
  62                 if ($sth->errorCode() && $sth->errorCode() != '00000') {
  63                     $this->rollBack();
  64                         $err = "$sql\n";
  65                             $err .= $this->getMsg($sth)."\n";
  66                     throw new Exception($err);
  67                 }
  68                 $sth->execute();
  69                 if ($sth->errorCode() && $sth->errorCode() != '00000') {
  70                     $this->rollBack();
  71                     $err = "$sql\n";
  72                         $err .= $this->getMsg($sth)."\n";
  73                     throw new Exception($err);
  74                 }
  75             }
  76             $this->commit();
  77         }
  78         else
  79             throw new Exception("Database exists");
  80     }
  81
  82     function getViewStyle($uid) {
  83         $sql = "select userview from user u where u.uid = ?";
  84         $sth = $this->prepare($sql);
  85         if ($sth->errorCode() && $sth->errorCode() != '00000') {
  86             $err = "$sql\n";
  87                 $err .= $this->getMsg($sth)."\n";
  88             return $err;
  89         }
  90         $sth->execute(array($uid));
  91         if ($sth->errorCode() && $sth->errorCode() != '00000') {
  92             $err = "$sql\n";
  93                 $err .= $this->getMsg($sth)."\n";
  94             return $err;
  95         }
  96         $res = $sth->fetch();
  97         return $res[0];
  98     }
  99
 100     function getRole($uid) {
 101         $sql = "select userrole from user u where u.uid = ?";
 102         $sth = $this->prepare($sql);
 103         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 104             $err = "$sql\n";
 105                 $err .= $this->getMsg($sth)."\n";
 106             return $err;
 107         }
 108         $sth->execute(array($uid));
 109         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 110             $err = "$sql\n";
 111                 $err .= $this->getMsg($sth)."\n";
 112             return $err;
 113         }
 114         $res = $sth->fetch();
 115         return $res[0];
 116     }
 117
 118     function getTimeout($uid) {
 119         $sql = "select timeout from user u where u.uid = ?";
 120         $sth = $this->prepare($sql);
 121         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 122             $err = "$sql\n";
 123                 $err .= $this->getMsg($sth)."\n";
 124             return $err;
 125         }
 126         $sth->execute(array($uid));
 127         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 128             $err = "$sql\n";
 129                 $err .= $this->getMsg($sth)."\n";
 130             return $err;
 131         }
 132         $res = $sth->fetch();
 133         return $res[0];
 134     }
 135
 136     function getTimezone($uid) {
 137         $sql = "select timezone from user u where u.uid = ?";
 138         $sth = $this->prepare($sql);
 139         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 140             $err = "$sql\n";
 141                 $err .= $this->getMsg($sth)."\n";
 142             return $err;
 143         }
 144         $sth->execute(array($uid));
 145         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 146             $err = "$sql\n";
 147                 $err .= $this->getMsg($sth)."\n";
 148             return $err;
 149         }
 150         $res = $sth->fetch();
 151         return $res[0];
 152     }
 153
 154     function getStartWeek($uid) {
 155         $sql = "select weekstart from user u where u.uid = ?";
 156         $sth = $this->prepare($sql);
 157         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 158             $err = "$sql\n";
 159                 $err .= $this->getMsg($sth)."\n";
 160             return $err;
 161         }
 162         $sth->execute(array($uid));
 163         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 164             $err = "$sql\n";
 165                 $err .= $this->getMsg($sth)."\n";
 166             return $err;
 167         }
 168         $res = $sth->fetch();
 169         return ($res[0]) ? 'MO' : 'SU';
 170     }
 171
 172     function getStartHour($uid) {
 173         $sql = "select daystart from user u where u.uid = ?";
 174         $sth = $this->prepare($sql);
 175         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 176             $err = "$sql\n";
 177                 $err .= $this->getMsg($sth)."\n";
 178             return $err;
 179         }
 180         $sth->execute(array($uid));
 181         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 182             $err = "$sql\n";
 183                 $err .= $this->getMsg($sth)."\n";
 184             return $err;
 185         }
 186         $res = $sth->fetch();
 187         return $res[0];
 188     }
 189
 190     function getEndHour($uid) {
 191         $sql = "select daystart from user u where u.uid = ?";
 192         $sth = $this->prepare($sql);
 193         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 194             $err = "$sql\n";
 195                 $err .= $this->getMsg($sth)."\n";
 196             return $err;
 197         }
 198         $sth->execute(array($uid));
 199         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 200             $err = "$sql\n";
 201                 $err .= $this->getMsg($sth)."\n";
 202             return $err;
 203         }
 204         $res = $sth->fetch();
 205         return $res[0];
 206     }
 207
 208     function authenticate($uid, $pwd) {
 209         $res = array();
 210         $sql = "select userrole, timeout, userview, weekstart, daystart,
 211         dayend, timezone, seckey, pubkey, c.id id, name, color, config
 212         from user u left join calendar c on c.uid = u.id where
 213         u.uid = ? and u.pwd = ?";
 214         $sth = $this->prepare($sql);
 215         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 216             $err = "$sql\n";
 217                 $err .= $this->getMsg($sth)."\n";
 218             throw new Exception($err);
 219         }
 220         $sth->execute(array($uid,$pwd));
 221         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 222             $err = "$sql\n";
 223                 $err .= $this->getMsg($sth)."\n";
 224             throw new Exception($err);
 225         }
 226         $res = $sth->fetchAll(PDO::FETCH_ASSOC);
 227         return $res;
 228     }
 229
 230     function addUser($data) {
 231         $sql = "insert into user (uid, pwd, timezone, userrole, seckey, pubkey, " .
 232             "timeout, userview, weekstart, daystart, dayend) values (?,?,?,?,?,?,?,?,?,?,?)";
 233         $sth = $this->prepare($sql);
 234         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 235             $err = "$sql\n";
 236             $err .= $this->getMsg($sth)."\n";
 237             return $err;
 238         }
 239         $this->beginTransaction();
 240         $sth->execute(array($data['uid'],$data['pwd'],$data['timezone'],
 241             $data['userrole'],$data['seckey'],$data['pubkey'],
 242             $data['timeout'], $data['view'], ($data['week_start'] == false) ? 0 : 1,
 243             $data['start'], $data['end']));
 244         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 245             $this->rollBack();
 246             $err = "$sql\n";
 247             $err .= $this->getMsg($sth)."\n";
 248             return $err;
 249         }
 250         $this->commit();
 251         return TRUE;
 252     }
 253
 254     function setUserSettings($uid, $data) {
 255         $sql = "update user set userrole = ?, timeout = ?, userview = ?,
 256             weekstart = ?, daystart = ?, dayend = ?, timezone = ?
 257             where uid = ?";
 258         $sth = $this->prepare($sql);
 259         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 260             $err = "$sql\n";
 261                 $err .= $this->getMsg($sth)."\n";
 262             return $err;
 263         }
 264         $this->beginTransaction();
 265         $sth->execute(array($data['userrole'],$data['timeout'],
 266             $data['userview'],$data['weekstart'],$data['daystart'],
 267             $data['dayend'],$data['timezone'],$uid));
 268         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 269             $this->rollBack();
 270             $err = "$sql\n";
 271                 $err .= $this->getMsg($sth)."\n";
 272             return $err;
 273         }
 274         $this->commit();
 275         return TRUE;
 276     }
 277 /*
 278 timeout integer default 3600,
 279 userview text default 'week',
 280 weekstart integer default 0,
 281 daystart real default 8.00,
 282 dayend real default 17.00,
 283 timezone text default 'Etc/UTC',
 284 */
 285     function changeDefault($data) {
 286         $temp = <<<_TEMP
 287             CREATE TEMPORARY TABLE tmpuser (
 288                 id integer,
 289                 uid text,
 290                 pwd text,
 291                 userrole integer,
 292                 timeout integer,
 293                 userview text,
 294                 weekstart integer,
 295                 daystart real,
 296                 dayend real,
 297                 timezone text,
 298                 seckey text,
 299                 pubkey text
 300             )
 301 _TEMP;
 302         $table = <<<_TABLE
 303             CREATE TABLE user (
 304                 id integer primary key autoincrement,
 305                 uid text not null,
 306                 pwd text not null,
 307                 userrole integer not null,
 308                 timeout integer default __TIMEOUT__,
 309                 userview text default '__VIEW__',
 310                 weekstart integer default __WEEKSTART__,
 311                 daystart real default __DAYSTART__,
 312                 dayend real default __DAYEND__,
 313                 timezone text default '__TIMEZONE__',
 314                 seckey text default '',
 315                 pubkey text default '',
 316                 constraint uid_index unique (uid)
 317                 constraint userrole_fk foreign key (userrole) references roles (id)
 318                 on delete restrict
 319                 on update cascade
 320                 on insert cascade
 321             )
 322 _TABLE;
 323         $table = str_replace('__TIMEOUT__', $data['TIMEOUT'], $table);
 324         $table = str_replace('__VIEW__', $data['VIEW_STYLE'], $table);
 325         $table = str_replace('__WEEKSTART__', $data['WEEK_START_SUNDAY'], $table);
 326         $table = str_replace('__DAYSTART__', $data['START_HOUR'], $table);
 327         $table = str_replace('__DAYEND__', $data['END_HOUR'], $table);
 328         $table = str_replace('__TIMEZONE__', $data['TIMEZONE'], $table);
 329         $this->beginTransaction();
 330         if ($this->exec($temp) === FALSE) {
 331             $err = "$sql\n";
 332                 $err .= $this->getMsg($sth)."\n";
 333             $this->rollBack();
 334             return $err;
 335         }
 336         if ($this->exec('insert into tmpuser select * from user') === FALSE) {
 337             $err = "cp to tmp table\n";
 338                 $err .= $this->getMsg($this)."\n";
 339             $this->rollBack();
 340             return $err;
 341         }
 342         if ($this->exec('drop table user') === FALSE) {
 343             $err = "drop user table\n";
 344                 $err .= $this->getMsg($this)."\n";
 345             $this->rollBack();
 346             return $err;
 347         }
 348         if ($this->exec($table) === FALSE) {
 349             $err = "create table\n";
 350                 $err .= $this->getMsg($this)."\n";
 351             $this->rollBack();
 352             return $err;
 353         }
 354         if ($this->exec('insert into user select * from tmpuser') === FALSE) {
 355             $err = "cp tmp to user table\n";
 356                 $err .= $this->getMsg($this)."\n";
 357             $this->rollBack();
 358             return $err;
 359         }
 360         if ($this->exec('drop table tmpuser') === FALSE) {
 361             $err = "drop user table\n";
 362                 $err .= $this->getMsg($this)."\n";
 363             $this->rollBack();
 364             return $err;
 365         }
 366         $this->commit();
 367         return TRUE;
 368     }
 369
 370     function deleteUser($uid) {
 371         $sql = "delete from user where uid = ?";
 372         $sth = $this->prepare($sql);
 373         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 374             $err = "$sql\n";
 375                 $err .= $this->getMsg($sth)."\n";
 376             return $err;
 377         }
 378         $this->beginTransaction();
 379         $sth->execute(array($uid));
 380         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 381             $this->rollBack();
 382             $err = "$sql\n";
 383                 $err .= $this->getMsg($sth)."\n";
 384             return $err;
 385         }
 386         $this->commit();
 387         return TRUE;
 388     }
 389
 390     function addCalendar($uid, CalendarInfo $cal) {
 391         //$fp = fopen('/tmp/add_calender.log', 'a');
 392         //fwrite($fp, "New calendar\nUID: $uid\nname: {$cal->name}\nColor: {$cal->color}\nConfig: {$cal->config}\n");
 393         $this->beginTransaction();
 394         $sql = "select id from user where uid = ?";
 395         //fwrite($fp, "Get ID: $sql\n");
 396         $sth = $this->prepare($sql);
 397         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 398             $err = "$sql\n";
 399                 $err .= $this->getMsg($sth)."\n";
 400             $this->rollBack();
 401             return $err;
 402         }
 403         $sth->execute(array($uid));
 404         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 405             $err = "$sql\n";
 406                 $err .= $this->getMsg($sth)."\n";
 407             $this->rollBack();
 408             return $err;
 409         }
 410         $sth->bindColumn(1, $id);
 411         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 412             $err = "$sql\n";
 413                 $err .= $this->getMsg($sth)."\n";
 414             $this->rollBack();
 415             return $err;
 416         }
 417         $sth->fetch(PDO::FETCH_BOUND);
 418         //fwrite($fp, "Returned user ID: $id\n");
 419         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 420             $err = "$sql\n";
 421                 $err .= $this->getMsg($sth)."\n";
 422             $this->rollBack();
 423             return $err;
 424         }
 425         $sth->closeCursor();
 426         $sql = "insert into calendar (uid, name, color, config)
 427             values ($id,?,?,?)";
 428         //var_dump($sql);
 429         //fwrite($fp, "Insert into calendar: $sql\n");
 430         $sth = $this->prepare($sql);
 431         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 432             $err = "$sql\n";
 433                 $err .= $this->getMsg($sth)."\n";
 434             $this->rollBack();
 435             return $err;
 436         }
 437         $sth->execute(array($cal->name,$cal->color,$cal->config));
 438         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 439             $err = "$sql\n";
 440                 $err .= $this->getMsg($sth)."\n";
 441             $this->rollBack();
 442             return $err;
 443         }
 444         $sth->closeCursor();
 445         $sth = $this->prepare('select max(id) from calendar');
 446         $sth->execute();
 447         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 448             $err = "$sql\n";
 449                 $err .= $this->getMsg($sth)."\n";
 450             $this->rollBack();
 451             return $err;
 452         }
 453         $sth->bindColumn(1, $id);
 454         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 455             $err = "$sql\n";
 456                 $err .= $this->getMsg($sth)."\n";
 457             $this->rollBack();
 458             return $err;
 459         }
 460         $sth->fetch(PDO::FETCH_BOUND);
 461         //fwrite($fp, "Returned ID for created calendar: $id\n");
 462         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 463             $err = "$sql\n";
 464                 $err .= $this->getMsg($sth)."\n";
 465             $this->rollBack();
 466             return $err;
 467         }
 468         $sth->closeCursor();
 469         $this->commit();
 470 /*        $sql = "select * from calendar where id = $id";
 471         $sth = $this->prepare($sql);
 472         $sth->execute();
 473         $result = $sth->fetchAll(PDO::FETCH_ASSOC);
 474         if ($result)
 475             fwrite($fp, var_export($result[0], TRUE) . "\n");
 476         else
 477             fwrite($fp, "No date found in calendar relation\n");
 478         fclose($fp);*/
 479         return $id;
 480     }
 481
 482     function deleteCalendar($uid, $id) {
 483         $this->beginTransaction();
 484         $sql = "select id from user where uid = ?";
 485         $sth = $this->prepare($sql);
 486         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 487             $err = "$sql\n";
 488                 $err .= $this->getMsg($sth)."\n";
 489             $this->rollBack();
 490             return $err;
 491         }
 492         $sth->execute(array($uid));
 493         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 494             $err = "$sql\n";
 495                 $err .= $this->getMsg($sth)."\n";
 496             $this->rollBack();
 497             return $err;
 498         }
 499         $sth->bindColumn(1, $uid);
 500         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 501             $err = "$sql\n";
 502                 $err .= $this->getMsg($sth)."\n";
 503             $this->rollBack();
 504             return $err;
 505         }
 506         $sth->fetch(PDO::FETCH_BOUND);
 507         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 508             $err = "$sql\n";
 509                 $err .= $this->getMsg($sth)."\n";
 510             $this->rollBack();
 511             return $err;
 512         }
 513         $sth->closeCursor();
 514         $sql = "delete from calendar where id = ? and uid = $uid";
 515         $sth = $this->prepare($sql);
 516         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 517             $err = "$sql\n";
 518                 $err .= $this->getMsg($sth)."\n";
 519             $this->rollBack();
 520             return $err;
 521         }
 522         $sth->execute(array($id));
 523         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 524             $err = "$sql\n";
 525                 $err .= $this->getMsg($sth)."\n";
 526             $this->rollBack();
 527             return $err;
 528         }
 529         $this->commit();
 530         return TRUE;
 531     }
 532
 533     function updateCalendar($uid, $id, CalendarInfo $cal) {
 534         $this->beginTransaction();
 535         $sql = "select id from user where uid = ?";
 536         $sth = $this->prepare($sql);
 537         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 538             $err = "$sql\n";
 539                 $err .= $this->getMsg($sth)."\n";
 540             $this->rollBack();
 541             return $err;
 542         }
 543         $sth->execute(array($uid));
 544         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 545             $err = "$sql\n";
 546                 $err .= $this->getMsg($sth)."\n";
 547             $this->rollBack();
 548             return $err;
 549         }
 550         $sth->bindColumn(1, $uid);
 551         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 552             $err = "$sql\n";
 553                 $err .= $this->getMsg($sth)."\n";
 554             $this->rollBack();
 555             return $err;
 556         }
 557         $sth->fetch(PDO::FETCH_BOUND);
 558         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 559             $err = "$sql\n";
 560                 $err .= $this->getMsg($sth)."\n";
 561             $this->rollBack();
 562             return $err;
 563         }
 564         $sth->closeCursor();
 565         $sql = "update calendar set name = ?, color = ?, config = ?
 566         where id = ? and uid = $uid";
 567         $sth = $this->prepare($sql);
 568         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 569             $err = "$sql\n";
 570                 $err .= $this->getMsg($sth)."\n";
 571             $this->rollBack();
 572             return $err;
 573         }
 574         $sth->execute(array($cal->name,$cal->color,$cal->config,$id));
 575         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 576             $err = "$sql\n";
 577                 $err .= $this->getMsg($sth)."\n";
 578             $this->rollBack();
 579             return $err;
 580         }
 581         $this->commit();
 582         return TRUE;
 583     }
 584
 585     function changePassword($uid, $pwd) {
 586         $this->beginTransaction();
 587         $sql = "update user set pwd = ? where uid = ?";
 588         $sth = $this->prepare($sql);
 589         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 590             $err = "$sql\n";
 591                 $err .= $this->getMsg($sth)."\n";
 592             return $err;
 593         }
 594         $sth->execute(array($pwd,$uid));
 595         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 596             $this->rollBack();
 597             $err = "$sql\n";
 598                 $err .= $this->getMsg($sth)."\n";
 599             return $err;
 600         }
 601         $this->commit();
 602         return TRUE;
 603     }
 604
 605     function getAllUsers($limit, $offset) {
 606         $sql = "select u.uid, r.name as userrole from user u, roles r
 607         where u.userrole = r.id limit ? offset ?";
 608         $sth = $this->prepare($sql);
 609         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 610             $err = "$sql\n";
 611                 $err .= $this->getMsg($sth)."\n";
 612             return $err;
 613         }
 614         $sth->execute(array($limit,$offset));
 615         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 616             $err = "$sql\n";
 617                 $err .= $this->getMsg($sth)."\n";
 618             return $err;
 619         }
 620         return $sth->fetchAll(PDO::FETCH_ASSOC);
 621     }
 622
 623     function getRoles() {
 624         $sql = "select id, name from roles order by id desc";
 625         $sth = $this->prepare($sql);
 626         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 627             $err = "$sql\n";
 628                 $err .= $this->getMsg($sth)."\n";
 629             return $err;
 630         }
 631         $sth->execute();
 632         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 633             $err = "$sql\n";
 634                 $err .= $this->getMsg($sth)."\n";
 635             return $err;
 636         }
 637         return $sth->fetchAll(PDO::FETCH_ASSOC);
 638     }
 639
 640     function getRoleName($id) {
 641         $sql = "select name from roles where id = ?";
 642         $sth = $this->prepare($sql);
 643         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 644             $err = "$sql\n";
 645                 $err .= $this->getMsg($sth)."\n";
 646             return $err;
 647         }
 648         $sth->execute(array($id));
 649         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 650             $err = "$sql\n";
 651                 $err .= $this->getMsg($sth)."\n";
 652             return $err;
 653         }
 654         return $sth->fetch(PDO::FETCH_ASSOC);
 655     }
 656
 657     function getVersion() {
 658         $version = array('version' => 0);
 659         $sql = "select count(*) as exist from sqlite_master where
 660             type = 'table' and tbl_name = 'about'";
 661         $sth = $this->prepare($sql);
 662         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 663             $err = "$sql\n";
 664                 $err .= $this->getMsg($sth)."\n";
 665             return $err;
 666         }
 667         $sth->execute();
 668         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 669             $err = "$sql\n";
 670                 $err .= $this->getMsg($sth)."\n";
 671             return $err;
 672         }
 673         $res = $sth->fetch();
 674         if ($res['exist'] > 0) {
 675             $sql = "select version from about where id = 1";
 676             $sth = $this->prepare($sql);
 677             if ($sth->errorCode() && $sth->errorCode() != '00000') {
 678                 $err = "$sql\n";
 679                         $err .= $this->getMsg($sth)."\n";
 680                 return $err;
 681             }
 682             $sth->execute();
 683             if ($sth->errorCode() && $sth->errorCode() != '00000') {
 684                     $err = "$sql\n";
 685                         $err .= $this->getMsg($sth)."\n";
 686                 return $err;
 687             }
 688             $res = $sth->fetch();
 689             $version = $res;
 690         }
 691         return $version;
 692     }
 693
 694     function execute($sql) {
 695         $sth = $this->prepare($sql);
 696         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 697             $err = "$sql\n";
 698                 $err .= $this->getMsg($sth)."\n";
 699             return $err;
 700         }
 701         $sth->execute();
 702         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 703             $err = "$sql\n";
 704                 $err .= $this->getMsg($sth)."\n";
 705             return $err;
 706         }
 707         return true;
 708     }
 709
 710     function getCalendarConfig($id) {
 711                 if ($id == -1) {
 712                         $sql = "select id, config from calendar";
 713                 }
 714                 else {
 715                         $sql = "select id, config from calendar where id = ?";
 716                 }
 717         $sth = $this->prepare($sql);
 718         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 719             $err = "$sql\n";
 720                 $err .= $this->getMsg($sth)."\n";
 721             return $err;
 722         }
 723         $sth->execute(array($id));
 724         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 725             $err = "$sql\n";
 726                 $err .= $this->getMsg($sth)."\n";
 727             return $err;
 728         }
 729         return $sth->fetchAll(PDO::FETCH_ASSOC);
 730         }
 731
 732         function nextTableNumber($name) {
 733                 $sql = "select count(*) as found from sqlite_master where " .
 734                            "type = 'table' and name like ?";
 735                 $sth = $this->prepare($sql);
 736         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 737             $err = "$sql\n";
 738                 $err .= $this->getMsg($sth)."\n";
 739             return $err;
 740         }
 741         $sth->execute(array($name . '%'));
 742         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 743             $err = "$sql\n";
 744                 $err .= $this->getMsg($sth)."\n";
 745             return $err;
 746         }
 747         $res = $sth->fetch(PDO::FETCH_ASSOC);
 748         return $res['found'];
 749         }
 750
 751     function getLdapConfig() {
 752         $sql = "select enable, dns, tls, base_dn, user_attr from ldap";
 753                 $sth = $this->prepare($sql);
 754         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 755             $err = "$sql\n";
 756                 $err .= $this->getMsg($sth)."\n";
 757             return $err;
 758         }
 759         $sth->execute();
 760         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 761             $err = "$sql\n";
 762                 $err .= $this->getMsg($sth)."\n";
 763             return $err;
 764         }
 765         return $sth->fetch(PDO::FETCH_ASSOC);
 766     }
 767
 768     function setLdapConfig(array $config) {
 769         $old_config = $this->getLdapConfig();
 770             if (! is_array($old_config) && $old_config)
 771                     return $old_config;
 772         else if (is_array($old_config))
 773             $sql = "update ldap set enable=?, dns=?, tls=?, base_dn=?, user_attr=?";
 774         else
 775             $sql = "insert into ldap values(?, ?, ?, ?, ?)";
 776         $this->beginTransaction();
 777                 $sth = $this->prepare($sql);
 778         if (! $sth) {
 779             $err = "$sql\n";
 780             foreach ($this->errorInfo() as $info)
 781                 $err .= "$info\n";
 782             $this->rollBack();
 783             return $err;
 784         }
 785         $sth->execute($config);
 786         if ($sth->errorCode() && $sth->errorCode() != '00000') {
 787             $this->rollBack();
 788             $err = "$sql\n";
 789             foreach ($this->errorInfo() as $info)
 790                 $err .= "$info\n";
 791             return $err;
 792         }
 793         $this->commit();
 794         return true;
 795     }
 796
 797 }