4 include_once 'config.inc.php';
5 require_once 'persistens.php';
6 require_once 'helper.php';
8 class SQLite
extends PDO
implements WebcalSupport
{
10 function __construct($dns = NULL) {
12 $db_dns = "sqlite:$dns";
15 $db_dns = 'sqlite:'.TOP_FOLDER.'/'.DNS;
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);
23 catch (PDOException $ex) {
24 throw new Exception($ex->getMessage());
28 private function getMsg($resource) {
29 $err = $resource->errorInfo();
36 function initDatabase($name, $pwd, $uid) {
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') {
45 $err .= $this->getMsg($sth)."\n
";
46 throw new Exception($err);
49 if ($sth->errorCode() && $sth->errorCode() != '00000') {
51 $err .= $this->getMsg($sth)."\n
";
52 throw new Exception($err);
55 if ($res['found'] == 0) {
56 $this->beginTransaction();
57 include 'db_create.sqlite.php';
59 foreach ($db as $sql) {
61 $sth = $this->prepare($sql);
62 if ($sth->errorCode() && $sth->errorCode() != '00000') {
65 $err .= $this->getMsg($sth)."\n
";
66 throw new Exception($err);
69 if ($sth->errorCode() && $sth->errorCode() != '00000') {
72 $err .= $this->getMsg($sth)."\n
";
73 throw new Exception($err);
79 throw new Exception("Database exists
");
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') {
87 $err .= $this->getMsg($sth)."\n
";
90 $sth->execute(array($uid));
91 if ($sth->errorCode() && $sth->errorCode() != '00000') {
93 $err .= $this->getMsg($sth)."\n
";
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') {
105 $err .= $this->getMsg($sth)."\n
";
108 $sth->execute(array($uid));
109 if ($sth->errorCode() && $sth->errorCode() != '00000') {
111 $err .= $this->getMsg($sth)."\n
";
114 $res = $sth->fetch();
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') {
123 $err .= $this->getMsg($sth)."\n
";
126 $sth->execute(array($uid));
127 if ($sth->errorCode() && $sth->errorCode() != '00000') {
129 $err .= $this->getMsg($sth)."\n
";
132 $res = $sth->fetch();
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') {
141 $err .= $this->getMsg($sth)."\n
";
144 $sth->execute(array($uid));
145 if ($sth->errorCode() && $sth->errorCode() != '00000') {
147 $err .= $this->getMsg($sth)."\n
";
150 $res = $sth->fetch();
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') {
159 $err .= $this->getMsg($sth)."\n
";
162 $sth->execute(array($uid));
163 if ($sth->errorCode() && $sth->errorCode() != '00000') {
165 $err .= $this->getMsg($sth)."\n
";
168 $res = $sth->fetch();
169 return ($res[0]) ? 'MO' : 'SU';
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') {
177 $err .= $this->getMsg($sth)."\n
";
180 $sth->execute(array($uid));
181 if ($sth->errorCode() && $sth->errorCode() != '00000') {
183 $err .= $this->getMsg($sth)."\n
";
186 $res = $sth->fetch();
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') {
195 $err .= $this->getMsg($sth)."\n
";
198 $sth->execute(array($uid));
199 if ($sth->errorCode() && $sth->errorCode() != '00000') {
201 $err .= $this->getMsg($sth)."\n
";
204 $res = $sth->fetch();
208 function authenticate($uid, $pwd) {
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') {
217 $err .= $this->getMsg($sth)."\n
";
218 throw new Exception($err);
220 $sth->execute(array($uid,$pwd));
221 if ($sth->errorCode() && $sth->errorCode() != '00000') {
223 $err .= $this->getMsg($sth)."\n
";
224 throw new Exception($err);
226 $res = $sth->fetchAll(PDO::FETCH_ASSOC);
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') {
236 $err .= $this->getMsg($sth)."\n
";
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') {
247 $err .= $this->getMsg($sth)."\n
";
254 function setUserSettings($uid, $data) {
255 $sql = "update user set userrole
= ?, timeout
= ?, userview
= ?,
256 weekstart
= ?, daystart
= ?, dayend
= ?, timezone
= ?
258 $sth = $this->prepare($sql);
259 if ($sth->errorCode() && $sth->errorCode() != '00000') {
261 $err .= $this->getMsg($sth)."\n
";
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') {
271 $err .= $this->getMsg($sth)."\n
";
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',
285 function changeDefault($data) {
287 CREATE TEMPORARY TABLE tmpuser (
304 id integer primary key autoincrement,
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)
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) {
332 $err .= $this->getMsg($sth)."\n
";
336 if ($this->exec('insert into tmpuser select * from user') === FALSE) {
337 $err = "cp to tmp table\n
";
338 $err .= $this->getMsg($this)."\n
";
342 if ($this->exec('drop table user') === FALSE) {
343 $err = "drop user table\n
";
344 $err .= $this->getMsg($this)."\n
";
348 if ($this->exec($table) === FALSE) {
349 $err = "create table\n
";
350 $err .= $this->getMsg($this)."\n
";
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
";
360 if ($this->exec('drop table tmpuser') === FALSE) {
361 $err = "drop user table\n
";
362 $err .= $this->getMsg($this)."\n
";
370 function deleteUser($uid) {
371 $sql = "delete from user where uid
= ?";
372 $sth = $this->prepare($sql);
373 if ($sth->errorCode() && $sth->errorCode() != '00000') {
375 $err .= $this->getMsg($sth)."\n
";
378 $this->beginTransaction();
379 $sth->execute(array($uid));
380 if ($sth->errorCode() && $sth->errorCode() != '00000') {
383 $err .= $this->getMsg($sth)."\n
";
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') {
399 $err .= $this->getMsg($sth)."\n
";
403 $sth->execute(array($uid));
404 if ($sth->errorCode() && $sth->errorCode() != '00000') {
406 $err .= $this->getMsg($sth)."\n
";
410 $sth->bindColumn(1, $id);
411 if ($sth->errorCode() && $sth->errorCode() != '00000') {
413 $err .= $this->getMsg($sth)."\n
";
417 $sth->fetch(PDO::FETCH_BOUND);
418 //fwrite($fp, "Returned user ID
: $id\n");
419 if ($sth->errorCode() && $sth->errorCode() != '00000') {
421 $err .= $this->getMsg($sth)."\n
";
426 $sql = "insert into
calendar (uid
, name
, color
, config
)
429 //fwrite($fp, "Insert into calendar
: $sql\n");
430 $sth = $this->prepare($sql);
431 if ($sth->errorCode() && $sth->errorCode() != '00000') {
433 $err .= $this->getMsg($sth)."\n
";
437 $sth->execute(array($cal->name,$cal->color,$cal->config));
438 if ($sth->errorCode() && $sth->errorCode() != '00000') {
440 $err .= $this->getMsg($sth)."\n
";
445 $sth = $this->prepare('select max(id) from calendar');
447 if ($sth->errorCode() && $sth->errorCode() != '00000') {
449 $err .= $this->getMsg($sth)."\n
";
453 $sth->bindColumn(1, $id);
454 if ($sth->errorCode() && $sth->errorCode() != '00000') {
456 $err .= $this->getMsg($sth)."\n
";
460 $sth->fetch(PDO::FETCH_BOUND);
461 //fwrite($fp, "Returned ID
for created calendar
: $id\n");
462 if ($sth->errorCode() && $sth->errorCode() != '00000') {
464 $err .= $this->getMsg($sth)."\n
";
470 /* $sql = "select
* from calendar where id
= $id";
471 $sth = $this->prepare($sql);
473 $result = $sth->fetchAll(PDO
::FETCH_ASSOC
);
475 fwrite($fp, var_export($result[0], TRUE) . "\n");
477 fwrite($fp, "No date found in calendar relation\n");
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') {
488 $err .= $this->getMsg($sth)."\n";
492 $sth->execute(array($uid));
493 if ($sth->errorCode() && $sth->errorCode() != '00000') {
495 $err .= $this->getMsg($sth)."\n";
499 $sth->bindColumn(1, $uid);
500 if ($sth->errorCode() && $sth->errorCode() != '00000') {
502 $err .= $this->getMsg($sth)."\n";
506 $sth->fetch(PDO
::FETCH_BOUND
);
507 if ($sth->errorCode() && $sth->errorCode() != '00000') {
509 $err .= $this->getMsg($sth)."\n";
514 $sql = "delete from calendar where id = ? and uid = $uid";
515 $sth = $this->prepare($sql);
516 if ($sth->errorCode() && $sth->errorCode() != '00000') {
518 $err .= $this->getMsg($sth)."\n
";
522 $sth->execute(array($id));
523 if ($sth->errorCode() && $sth->errorCode() != '00000') {
525 $err .= $this->getMsg($sth)."\n
";
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') {
539 $err .= $this->getMsg($sth)."\n
";
543 $sth->execute(array($uid));
544 if ($sth->errorCode() && $sth->errorCode() != '00000') {
546 $err .= $this->getMsg($sth)."\n
";
550 $sth->bindColumn(1, $uid);
551 if ($sth->errorCode() && $sth->errorCode() != '00000') {
553 $err .= $this->getMsg($sth)."\n
";
557 $sth->fetch(PDO::FETCH_BOUND);
558 if ($sth->errorCode() && $sth->errorCode() != '00000') {
560 $err .= $this->getMsg($sth)."\n
";
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') {
570 $err .= $this->getMsg($sth)."\n";
574 $sth->execute(array($cal->name
,$cal->color
,$cal->config
,$id));
575 if ($sth->errorCode() && $sth->errorCode() != '00000') {
577 $err .= $this->getMsg($sth)."\n";
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') {
591 $err .= $this->getMsg($sth)."\n";
594 $sth->execute(array($pwd,$uid));
595 if ($sth->errorCode() && $sth->errorCode() != '00000') {
598 $err .= $this->getMsg($sth)."\n";
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') {
611 $err .= $this->getMsg($sth)."\n";
614 $sth->execute(array($limit,$offset));
615 if ($sth->errorCode() && $sth->errorCode() != '00000') {
617 $err .= $this->getMsg($sth)."\n";
620 return $sth->fetchAll(PDO
::FETCH_ASSOC
);
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') {
628 $err .= $this->getMsg($sth)."\n";
632 if ($sth->errorCode() && $sth->errorCode() != '00000') {
634 $err .= $this->getMsg($sth)."\n";
637 return $sth->fetchAll(PDO
::FETCH_ASSOC
);
640 function getRoleName($id) {
641 $sql = "select name from roles where id = ?";
642 $sth = $this->prepare($sql);
643 if ($sth->errorCode() && $sth->errorCode() != '00000') {
645 $err .= $this->getMsg($sth)."\n";
648 $sth->execute(array($id));
649 if ($sth->errorCode() && $sth->errorCode() != '00000') {
651 $err .= $this->getMsg($sth)."\n";
654 return $sth->fetch(PDO
::FETCH_ASSOC
);
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') {
664 $err .= $this->getMsg($sth)."\n";
668 if ($sth->errorCode() && $sth->errorCode() != '00000') {
670 $err .= $this->getMsg($sth)."\n";
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') {
679 $err .= $this->getMsg($sth)."\n";
683 if ($sth->errorCode() && $sth->errorCode() != '00000') {
685 $err .= $this->getMsg($sth)."\n";
688 $res = $sth->fetch();
694 function execute($sql) {
695 $sth = $this->prepare($sql);
696 if ($sth->errorCode() && $sth->errorCode() != '00000') {
698 $err .= $this->getMsg($sth)."\n";
702 if ($sth->errorCode() && $sth->errorCode() != '00000') {
704 $err .= $this->getMsg($sth)."\n";
710 function getCalendarConfig($id) {
712 $sql = "select id, config from calendar";
715 $sql = "select id, config from calendar where id = ?";
717 $sth = $this->prepare($sql);
718 if ($sth->errorCode() && $sth->errorCode() != '00000') {
720 $err .= $this->getMsg($sth)."\n";
723 $sth->execute(array($id));
724 if ($sth->errorCode() && $sth->errorCode() != '00000') {
726 $err .= $this->getMsg($sth)."\n";
729 return $sth->fetchAll(PDO
::FETCH_ASSOC
);
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') {
738 $err .= $this->getMsg($sth)."\n";
741 $sth->execute(array($name . '%'));
742 if ($sth->errorCode() && $sth->errorCode() != '00000') {
744 $err .= $this->getMsg($sth)."\n";
747 $res = $sth->fetch(PDO
::FETCH_ASSOC
);
748 return $res['found'];
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') {
756 $err .= $this->getMsg($sth)."\n";
760 if ($sth->errorCode() && $sth->errorCode() != '00000') {
762 $err .= $this->getMsg($sth)."\n";
765 return $sth->fetch(PDO
::FETCH_ASSOC
);
768 function setLdapConfig(array $config) {
769 $old_config = $this->getLdapConfig();
770 if (! is_array($old_config) && $old_config)
772 else if (is_array($old_config))
773 $sql = "update ldap set enable=?, dns=?, tls=?, base_dn=?, user_attr=?";
775 $sql = "insert into ldap values(?, ?, ?, ?, ?)";
776 $this->beginTransaction();
777 $sth = $this->prepare($sql);
780 foreach ($this->errorInfo() as $info)
785 $sth->execute($config);
786 if ($sth->errorCode() && $sth->errorCode() != '00000') {
789 foreach ($this->errorInfo() as $info)