allow to define supported SQL adapter in email plugin
[tine20] / tine20 / Tinebase / EmailUser / Imap / Dbmail.php
1 <?php
2 /**
3  * Tine 2.0
4  * 
5  * @package     Tinebase
6  * @subpackage  EmailUser
7  * @license     http://www.gnu.org/licenses/agpl.html AGPL Version 3
8  * @copyright   Copyright (c) 2009-2012 Metaways Infosystems GmbH (http://www.metaways.de)
9  * @author      Philipp Schüle <p.schuele@metaways.de>
10  * 
11  */
12
13 /**
14  * plugin to handle Dbmail imap accounts 
15  * 
16  * @package    Tinebase
17  * @subpackage EmailUser
18  * 
19  * @todo generalize some logic and move it to abstract parent class
20  */
21 class Tinebase_EmailUser_Imap_Dbmail extends Tinebase_User_Plugin_Abstract
22 {
23     /**
24      * @var Zend_Db_Adapter
25      */
26     protected $_db = NULL;
27     
28     /**
29      * user table name with prefix
30      *
31      * @var string
32      */
33     protected $_userTable = NULL;
34
35     /**
36      * client id
37      *
38      * @var string
39      */
40     protected $_clientId = NULL;
41     
42     /**
43      * dbmail config
44      * 
45      * @var array 
46      * 
47      * @todo add those to imap config?
48      */
49     protected $_config = array(
50         'prefix'       => 'dbmail_',
51         'userTable'    => 'users',
52         'emailScheme'  => 'md5',
53         'mailboxTable' => 'mailboxes',
54         'emailGID'     => null,
55         'adapter'      => Tinebase_Core::PDO_MYSQL
56     );
57
58     /**
59      * user properties mapping
60      *
61      * @var array
62      */
63     protected $_propertyMapping = array(
64         'emailUserId'       => 'user_idnr',
65         'emailUsername'     => 'userid',
66         'emailPassword'     => 'passwd',
67         'emailGID'          => 'client_idnr', 
68         'emailLastLogin'    => 'last_login',
69         
70         'emailMailQuota'    => 'maxmail_size',
71         'emailMailSize'     => 'curmail_size',
72         'emailSieveQuota'   => 'maxsieve_size',
73         'emailSieveSize'    => 'cursieve_size',
74     
75         // makes mapping data to _config easier
76         'emailScheme'       => 'encryption_type',
77     );
78     
79     /**
80      * dbmail readonly
81      * 
82      * @var array
83      */
84     protected $_readOnlyFields = array(
85         'emailMailSize',
86         'emailSieveSize',
87         'emailLastLogin',
88     );
89     
90     /**
91      * stores if dbmail_users has tine20_userid column
92      * 
93      * @var boolean
94      */
95     protected $_hasTine20Userid = false;
96     
97     /**
98      * the constructor
99      *
100      */
101     public function __construct(array $_options = array())
102     {
103         $imapConfig = Tinebase_Config::getInstance()->get(Tinebase_Config::IMAP, new Tinebase_Config_Struct())->toArray();
104         
105         // merge _config and dbmail imap
106         $this->_config = array_merge($imapConfig['dbmail'], $this->_config);
107         
108         // set domain from imap config
109         $this->_config['domain'] = !empty($imapConfig['domain']) ? $imapConfig['domain'] : null;
110         
111         // _tablename = "dbmail_users"
112         $this->_userTable = $this->_config['prefix'] . $this->_config['userTable'];
113         
114         // connect to DB
115         $this->_getDb($this->_config);
116         
117         $columns = Tinebase_Db_Table::getTableDescriptionFromCache('dbmail_users', $this->_db);
118         if((isset($columns['tine20_userid']) || array_key_exists('tine20_userid', $columns)) && (isset($columns['tine20_clientid']) || array_key_exists('tine20_clientid', $columns))) {
119             $this->_hasTine20Userid = true;
120             $this->_propertyMapping['emailUserId'] = 'tine20_userid';
121             $this->_propertyMapping['emailGID']    = 'tine20_clientid';
122         }
123         
124         $this->_clientId = Tinebase_Application::getInstance()->getApplicationByName('Tinebase')->getId();
125         
126         $this->_config['emailGID'] = Tinebase_Application::getInstance()->getApplicationByName('Tinebase')->getId();
127     }
128     
129     /**
130      * delete user by id
131      *
132      * @param  Tinebase_Model_FullUser  $_user
133      */
134     public function inspectDeleteUser(Tinebase_Model_FullUser $_user)
135     {
136         Tinebase_Core::getLogger()->info(__METHOD__ . '::' . __LINE__ . ' Delete Dbmail settings for user ' . $_user->accountLoginName);
137
138         if($this->_hasTine20Userid === true) {
139             $where = array(
140                 $this->_db->quoteInto($this->_db->quoteIdentifier($this->_propertyMapping['emailUserId']) . ' = ?', $_user->getId()),
141                 $this->_db->quoteInto($this->_db->quoteIdentifier($this->_propertyMapping['emailGID'])    . ' = ?', $this->_config['emailGID'])
142             );
143         } else {
144             $where = array(
145                 $this->_db->quoteInto($this->_db->quoteIdentifier($this->_propertyMapping['emailUserId']) . ' = ?', $this->_convertToInt($_user->getId())),
146                 $this->_db->quoteInto($this->_db->quoteIdentifier($this->_propertyMapping['emailGID'])    . ' = ?', $this->_convertToInt($this->_config['emailGID']))
147             );
148         }
149         
150         if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . " delete from {$this->_userTable} " . print_r($where, true));
151         
152         $this->_db->delete($this->_userTable, $where);
153     }
154     
155     /**
156      * inspect get user by property
157      * 
158      * @param  Tinebase_Model_User  $_user  the user object
159      */
160     public function inspectGetUserByProperty(Tinebase_Model_User $_user)
161     {
162         if (! $_user instanceof Tinebase_Model_FullUser) {
163             return;
164         }
165         
166         $userId = $_user->getId();
167         
168         $select = $this->_getSelect();
169         
170         if($this->_hasTine20Userid === true) {
171             $select->where($this->_db->quoteIdentifier($this->_propertyMapping['emailUserId']) . ' = ?',   $userId);
172         } else {
173             $select->where($this->_db->quoteIdentifier($this->_propertyMapping['emailUserId']) . ' = ?',   $this->_convertToInt($userId));
174         }
175         
176         if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' ' . $select->__toString());
177
178         // Perferom query - retrieve user from database
179         $stmt = $this->_db->query($select);
180         $queryResult = $stmt->fetch();
181         $stmt->closeCursor();
182                 
183         if (!$queryResult) {
184             if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . 'Dbmail config for user ' . $userId . ' not found!');
185             return;
186         }
187         
188         #if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' ' . print_r($queryResult, TRUE));
189         
190         // convert data to Tinebase_Model_EmailUser       
191         $emailUser = $this->_rawDataToRecord($queryResult);
192         #if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' ' . print_r($emailUser->toArray(), TRUE));
193         
194         // modify/correct user name
195         // set emailUsername to Tine accout login name and append domain for login purposes if set
196         $emailUser->emailUsername = $this->_appendDomain($_user->accountLoginName);
197
198         $_user->imapUser  = $emailUser;
199         $_user->emailUser = Tinebase_EmailUser::merge(clone $_user->imapUser, isset($_user->emailUser) ? $_user->emailUser : null);
200     }
201     
202     /**
203      * update/set email user password
204      * 
205      * @param  string  $_userId
206      * @param  string  $_password
207      * @param  bool    $_encrypt encrypt password
208      */
209     public function inspectSetPassword($_userId, $_password, $_encrypt = TRUE)
210     {
211         if (! $_encrypt && preg_match('/\{(.*)\}(.*)/', $_password, $matches)) {
212             // if password should not be encrypted but already contains encryption type, we separate pw and type 
213             $scheme = $matches[1];
214             $password = $matches[2];
215         } else {
216             $scheme = $this->_config['emailScheme'];
217             $password = ($_encrypt) ? Hash_Password::generate($scheme, $_password, false) : $_password;
218         }
219         
220         $values = array(
221             $this->_propertyMapping['emailScheme']   => $scheme,
222             $this->_propertyMapping['emailPassword'] => $password,
223         );
224         
225         if($this->_hasTine20Userid === true) {
226             $where = array(
227                 $this->_db->quoteInto($this->_db->quoteIdentifier($this->_propertyMapping['emailUserId']) . ' = ?', $_userId),
228                 $this->_db->quoteInto($this->_db->quoteIdentifier($this->_propertyMapping['emailGID'])    . ' = ?', $this->_config['emailGID'])
229             );
230         } else {
231             $where = array(
232                 $this->_db->quoteInto($this->_db->quoteIdentifier($this->_propertyMapping['emailUserId']) . ' = ?', $this->_convertToInt($userId)),
233                 $this->_db->quoteInto($this->_db->quoteIdentifier($this->_propertyMapping['emailGID'])    . ' = ?', $this->_convertToInt($this->_config['emailGID']))
234             );
235         }
236         
237         $this->_db->update($this->_userTable, $values, $where);
238     }
239     
240     /**
241      * adds email properties for a new user
242      * 
243      * @param  Tinebase_Model_FullUser  $_addedUser
244      * @param  Tinebase_Model_FullUser  $_newUserProperties
245      */
246     protected function _addUser(Tinebase_Model_FullUser $_addedUser, Tinebase_Model_FullUser $_newUserProperties)
247     {
248         if (! $_addedUser->accountEmailAddress) {
249             if (Tinebase_Core::isLogLevel(Zend_Log::NOTICE)) Tinebase_Core::getLogger()->notice(__METHOD__ . '::' . __LINE__ 
250                 . ' User ' . $_addedUser->accountDisplayName . ' has no email address defined. Skipping dbmail user creation.');
251             return;
252         }
253         
254         $imapSettings = $this->_recordToRawData($_addedUser, $_newUserProperties);
255         
256         $this->_removeNonDBValues($imapSettings);
257         
258         if (! $this->_checkOldUserRecord($imapSettings)) {
259             if (Tinebase_Core::isLogLevel(Zend_Log::INFO)) Tinebase_Core::getLogger()->info(__METHOD__ . '::' . __LINE__ 
260                 . ' Adding Dbmail user ' . $imapSettings[$this->_propertyMapping['emailUsername']]);
261             
262             // generate random password if not set
263             if (empty($imapSettings[$this->_propertyMapping['emailPassword']])) {
264                 $imapSettings[$this->_propertyMapping['emailPassword']] = Hash_Password::generate($this->_config['emailScheme'], Tinebase_Record_Abstract::generateUID(), FALSE);
265             }
266             
267             $this->_db->insert($this->_userTable, $imapSettings);
268         }
269         
270         $this->inspectGetUserByProperty($_addedUser);
271     }
272     
273     /**
274      * remove some values that should not be written to dbmail DB
275      * 
276      * @param array $userdata
277      */
278     protected function _removeNonDBValues(&$userdata)
279     {
280         unset($userdata[$this->_propertyMapping['emailMailSize']]);
281         unset($userdata[$this->_propertyMapping['emailSieveSize']]);
282         unset($userdata[$this->_propertyMapping['emailLastLogin']]);
283     }
284     
285     /**
286      * check if old entry exists and update it
287      * 
288      * @param array $_userData
289      * @return boolean (TRUE if old record exists)
290      */
291     protected function _checkOldUserRecord($_userData)
292     {
293         $userIdProperty = $this->_propertyMapping['emailUsername'];
294         $where = $this->_db->quoteInto($this->_db->quoteIdentifier($userIdProperty) . ' = ?', $_userData[$userIdProperty]);
295         $select = $this->_db->select();
296         $select->from(array($this->_userTable => $this->_userTable), array($userIdProperty))
297             ->where($where)
298             ->limit(1);
299         
300         $stmt = $this->_db->query($select);
301         $queryResult = $stmt->fetch();
302         if ($queryResult) {
303             // preserve current pw
304             unset($_userData[$this->_propertyMapping['emailPassword']]);
305             
306             $this->_update($_userData, $where);
307             return TRUE;
308         }
309         
310         return FALSE;
311     }
312     
313     /**
314      * updates email properties for an existing user
315      * 
316      * @param  Tinebase_Model_FullUser  $_updatedUser
317      * @param  Tinebase_Model_FullUser  $_newUserProperties
318      */
319     protected function _updateUser(Tinebase_Model_FullUser $_updatedUser, Tinebase_Model_FullUser $_newUserProperties)
320     {
321         $imapSettings = $this->_recordToRawData($_updatedUser, $_newUserProperties);
322         
323         if($this->_hasTine20Userid === true) {
324             $where = array(
325                 $this->_db->quoteInto($this->_db->quoteIdentifier($this->_propertyMapping['emailUserId']) . ' = ?', $_updatedUser->getId()),
326                 $this->_db->quoteInto($this->_db->quoteIdentifier($this->_propertyMapping['emailGID'])    . ' = ?', $this->_config['emailGID'])
327             );
328         } else {
329             $where = array(
330                 $this->_db->quoteInto($this->_db->quoteIdentifier($this->_propertyMapping['emailUserId']) . ' = ?', $this->_convertToInt($_updatedUser->getId())),
331                 $this->_db->quoteInto($this->_db->quoteIdentifier($this->_propertyMapping['emailGID'])    . ' = ?', $this->_convertToInt($this->_config['emailGID']))
332             );
333         }
334
335         unset($imapSettings[$this->_propertyMapping['emailUserId']]);
336         $this->_removeNonDBValues($imapSettings);
337         
338         $this->_update($imapSettings, $where);
339         
340         $this->inspectGetUserByProperty($_updatedUser);
341     }
342     
343     /**
344      * update user in dbmail db
345      * 
346      * @param array $userData
347      * @param mixed $where
348      */
349     protected function _update($userData, $where)
350     {
351         if (Tinebase_Core::isLogLevel(Zend_Log::INFO)) Tinebase_Core::getLogger()->info(__METHOD__ . '::' . __LINE__ 
352             . " Update user {$userData[$this->_propertyMapping['emailUsername']]} in {$this->_userTable}");
353         if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ 
354             . " " . print_r($userData, TRUE));
355         
356         $this->_db->update($this->_userTable, $userData, $where);
357     }
358     
359     /**
360      * check if user exists already in dbmail user table
361      * 
362      * @param  Tinebase_Model_FullUser  $_user
363      */
364     protected function _userExists(Tinebase_Model_FullUser $_user)
365     {
366         $userId = $_user->getId();
367         
368         $select = $this->_getSelect();
369         
370         $select->where($this->_db->quoteIdentifier($this->_propertyMapping['emailUserId']) . ' = ?',   ($this->_hasTine20Userid === true) ? $userId : $this->_convertToInt($userId));
371                   
372         if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' ' . $select->__toString());
373
374         // Perferom query - retrieve user from database
375         $stmt = $this->_db->query($select);
376         $queryResult = $stmt->fetch();
377         $stmt->closeCursor();
378                 
379         if (!$queryResult) {
380             return false;
381         }
382         
383         return true;
384     }
385     
386     /**
387      * converts raw data from adapter into a single record / do mapping
388      *
389      * @param  array                     $_data
390      * @return Tinebase_Model_EmailUser
391      */
392     protected function _rawDataToRecord(array $_rawdata)
393     {
394         $data = array();
395         
396         foreach ($_rawdata as $key => $value) {
397             $keyMapping = array_search($key, $this->_propertyMapping);
398             if ($keyMapping !== FALSE) {
399                 switch($keyMapping) {
400                     case 'emailPassword':
401                         // do nothing
402                         break;
403                         
404                     case 'emailMailQuota':
405                     case 'emailMailSize':
406                     case 'emailSieveQuota':
407                     case 'emailSieveSize':
408                         $data[$keyMapping] = Tinebase_Helper::convertToMegabytes($value);
409                         break;
410                         
411                     default: 
412                         $data[$keyMapping] = $value;
413                         break;
414                 }
415             }
416         }
417         
418         return new Tinebase_Model_EmailUser($data, true);
419     }
420     
421     /**
422      * returns array of raw Dbmail data
423      *
424      * @param  Tinebase_Model_EmailUser  $_user
425      * @param  Tinebase_Model_EmailUser  $_newUserProperties
426      * @return array
427      */
428     protected function _recordToRawData(Tinebase_Model_FullUser $_user, Tinebase_Model_FullUser $_newUserProperties)
429     {
430         $rawData = array();
431         
432         foreach ($_newUserProperties->imapUser as $key => $value) {
433             $property = (isset($this->_propertyMapping[$key]) || array_key_exists($key, $this->_propertyMapping)) ? $this->_propertyMapping[$key] : false;
434             if ($property && ! in_array($key, $this->_readOnlyFields)) {
435                 switch ($key) {
436                     case 'emailPassword':
437                         $rawData[$property] =  Hash_Password::generate($this->_config['emailScheme'], $value, false);
438                         $rawData[$this->_propertyMapping['emailScheme']]   = $this->_config['emailScheme'];
439                         break;
440                         
441                     case 'emailUserId':
442                     case 'emailGID':
443                     case 'emailUsername':
444                         // do nothing
445                         break;
446                         
447                     case 'emailMailQuota':
448                     case 'emailMailSize':
449                     case 'emailSieveQuota':
450                     case 'emailSieveSize':
451                         // convert to bytes
452                         $rawData[$property] = Tinebase_Helper::convertToBytes($value . 'M');
453                         break;
454                         
455                     default:
456                         $rawData[$property] = $value;
457                 }
458             }
459         }
460         
461         $rawData[$this->_propertyMapping['emailUserId']]   = $this->_hasTine20Userid === true ? $_user->getId() : $this->_convertToInt($_user->getId());
462         if($this->_hasTine20Userid === true) {
463             $rawData[$this->_propertyMapping['emailGID']]  = $this->_config['emailGID'];
464             $rawData['client_idnr']                        = $this->_convertToInt($this->_config['emailGID']);
465         } else {
466             $rawData[$this->_propertyMapping['emailGID']]  = $this->_convertToInt($this->_config['emailGID']);
467         }
468         $rawData[$this->_propertyMapping['emailUsername']] = $this->_appendDomain($_user->accountLoginName);
469         
470         if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' ' . print_r($rawData, TRUE));
471         
472         return $rawData;
473     }
474     
475     /**
476      * convert some string to absolute int with crc32
477      * 
478      * @param  $_string
479      * @return integer
480      */
481     protected function _convertToInt($_string)
482     {
483         return sprintf("%u", crc32($_string));
484     }
485     
486     /**
487      * create mailbox for user
488      * 
489      * @param  Tinebase_Model_EmailUser  $_emailUser
490      * @param  string                    $_mailboxName
491      * @return void
492      */
493     protected function _createMailbox(Tinebase_Model_EmailUser $_emailUser, $_mailboxName = 'INBOX')
494     {
495         $data = array(
496             'owner_idnr'    => $_emailUser->emailUID,
497             'name'          => $_mailboxName,
498             'seen_flag'     => 1,
499             'answered_flag' => 1,
500             'deleted_flag'  => 1,
501             'flagged_flag'  => 1,
502             'recent_flag'   => 1,
503             'draft_flag'    => 1,
504         );
505         
506         $this->_db->insert($this->_config['prefix'] . $this->_config['mailboxTable'], $data);
507     }
508     
509     /**
510      * get the basic select object to fetch records from the database
511      *  
512      * @param  array|string|Zend_Db_Expr  $_cols        columns to get, * per default
513      * @param  boolean                    $_getDeleted  get deleted records (if modlog is active)
514      * @return Zend_Db_Select
515      */
516     protected function _getSelect($_cols = '*', $_getDeleted = FALSE)
517     {
518         $select = $this->_db->select()
519             ->from($this->_userTable);
520
521         if($this->_hasTine20Userid === true) {
522             $select->where($this->_db->quoteIdentifier($this->_propertyMapping['emailGID']) . ' = ?', $this->_config['emailGID'])
523                    ->limit(1);
524         } else {
525             $select->where($this->_db->quoteIdentifier($this->_propertyMapping['emailGID']) . ' = ?', $this->_convertToInt($this->_config['emailGID']))
526                    ->limit(1);
527         }
528         
529         return $select;
530     }
531 }