1/* SQLSource.h - this file is part of SOGo
2 *
3 * Copyright (C) 2009-2012 Inverse inc.
4 *
5 * Authors: Ludovic Marcotte <lmarcotte@inverse.ca>
6 *          Francis Lachapelle <flachapelle@inverse.ca>
7 *
8 * This file is free software; you can redistribute it and/or modify
9 * it under the terms of the GNU General Public License as published by
10 * the Free Software Foundation; either version 2, or (at your option)
11 * any later version.
12 *
13 * This file is distributed in the hope that it will be useful,
14 * but WITHOUT ANY WARRANTY; without even the implied warranty of
15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
16 * GNU General Public License for more details.
17 *
18 * You should have received a copy of the GNU General Public License
19 * along with this program; see the file COPYING.  If not, write to
20 * the Free Software Foundation, Inc., 59 Temple Place - Suite 330,
21 * Boston, MA 02111-1307, USA.
22 */
23
24#import <Foundation/NSArray.h>
25#import <Foundation/NSDictionary.h>
26#import <Foundation/NSException.h>
27#import <Foundation/NSObject.h>
28#import <Foundation/NSString.h>
29#import <Foundation/NSValue.h>
30#import <Foundation/NSURL.h>
31
32#import <NGExtensions/NSNull+misc.h>
33#import <NGExtensions/NSObject+Logs.h>
34
35#import <GDLContentStore/GCSChannelManager.h>
36#import <GDLContentStore/NSURL+GCS.h>
37#import <GDLContentStore/EOQualifier+GCS.h>
38#import <GDLAccess/EOAdaptorChannel.h>
39
40#import <SOGo/SOGoSystemDefaults.h>
41
42#import "SOGoConstants.h"
43#import "NSString+Utilities.h"
44#import "NSString+Crypto.h"
45
46#import "SQLSource.h"
47
48/**
49 * The view MUST contain the following columns:
50 *
51 * c_uid      - will be used for authentication - it's a username or username@domain.tld)
52 * c_name     - which can be identical to c_uid - will be used to uniquely identify entries)
53 * c_password - password of the user, can be encoded in {scheme}pass format, or when stored without
54 *              scheme it uses the scheme set in userPasswordAlgorithm.
55 *              Possible algorithms are:  plain, md5, crypt-md5, sha, ssha (including 256/512 variants),
56 *              cram-md5, smd5, crypt, crypt-md5
57 * c_cn       - the user's common name
58 * mail       - the user's mail address
59 *
60 * Other columns can be defined - see LDAPSource.m for the complete list.
61 *
62 *
63 * A SQL source can be defined like this:
64 *
65 *  {
66 *    id = zot;
67 *    type = sql;
68 *    viewURL = "mysql://sogo:sogo@127.0.0.1:5432/sogo/sogo_view";
69 *    canAuthenticate = YES;
70 *    isAddressBook = YES;
71 *    userPasswordAlgorithm = md5;
72 *    prependPasswordScheme = YES;
73 *  }
74 *
75 * If prependPasswordScheme is set to YES, the generated passwords will have the format {scheme}password.
76 * If it is NO (the default), the password will be written to database without encryption scheme.
77 *
78 */
79
80@implementation SQLSource
81
82+ (id) sourceFromUDSource: (NSDictionary *) udSource
83                 inDomain: (NSString *) domain
84{
85  return [[[self alloc] initFromUDSource: udSource
86                                inDomain: domain] autorelease];
87}
88
89- (id) init
90{
91  if ((self = [super init]))
92    {
93      _sourceID = nil;
94      _domainField = nil;
95      _authenticationFilter = nil;
96      _loginFields = nil;
97      _mailFields = nil;
98      _userPasswordAlgorithm = nil;
99      _viewURL = nil;
100      _kindField = nil;
101      _multipleBookingsField = nil;
102      _imapHostField = nil;
103      _sieveHostField = nil;
104    }
105
106  return self;
107}
108
109- (void) dealloc
110{
111  [_sourceID release];
112  [_authenticationFilter release];
113  [_loginFields release];
114  [_mailFields release];
115  [_userPasswordAlgorithm release];
116  [_viewURL release];
117  [_kindField release];
118  [_multipleBookingsField release];
119  [_domainField release];
120  [_imapHostField release];
121  [_sieveHostField release];
122
123  [super dealloc];
124}
125
126- (id) initFromUDSource: (NSDictionary *) udSource
127               inDomain: (NSString *) sourceDomain
128{
129  self = [self init];
130
131  ASSIGN(_sourceID, [udSource objectForKey: @"id"]);
132  ASSIGN(_authenticationFilter, [udSource objectForKey: @"authenticationFilter"]);
133  ASSIGN(_loginFields, [udSource objectForKey: @"LoginFieldNames"]);
134  ASSIGN(_mailFields, [udSource objectForKey: @"MailFieldNames"]);
135  ASSIGN(_userPasswordAlgorithm, [udSource objectForKey: @"userPasswordAlgorithm"]);
136  ASSIGN(_imapLoginField, [udSource objectForKey: @"IMAPLoginFieldName"]);
137  ASSIGN(_imapHostField, [udSource objectForKey:  @"IMAPHostFieldName"]);
138  ASSIGN(_sieveHostField, [udSource objectForKey:  @"SieveHostFieldName"]);
139  ASSIGN(_kindField, [udSource objectForKey: @"KindFieldName"]);
140  ASSIGN(_multipleBookingsField, [udSource objectForKey: @"MultipleBookingsFieldName"]);
141  ASSIGN(_domainField, [udSource objectForKey: @"DomainFieldName"]);
142  if ([udSource objectForKey: @"prependPasswordScheme"])
143    _prependPasswordScheme = [[udSource objectForKey: @"prependPasswordScheme"] boolValue];
144  else
145    _prependPasswordScheme = NO;
146
147  if (!_userPasswordAlgorithm)
148    _userPasswordAlgorithm = @"none";
149
150  if ([udSource objectForKey: @"viewURL"])
151    _viewURL = [[NSURL alloc] initWithString: [udSource objectForKey: @"viewURL"]];
152
153#warning this domain code has no effect yet
154  if ([sourceDomain length])
155    ASSIGN (_domain, sourceDomain);
156
157  if (!_viewURL)
158    {
159      [self autorelease];
160      return nil;
161    }
162
163  return self;
164}
165
166- (NSString *) domain
167{
168  return _domain;
169}
170
171- (BOOL) _isPassword: (NSString *) plainPassword
172             equalTo: (NSString *) encryptedPassword
173{
174  if (!plainPassword || !encryptedPassword)
175    return NO;
176
177  return [plainPassword isEqualToCrypted: encryptedPassword
178                       withDefaultScheme: _userPasswordAlgorithm];
179}
180
181/**
182 * Encrypts a string using this source password algorithm.
183 * @param plainPassword the unencrypted password.
184 * @return a new encrypted string.
185 * @see _isPassword:equalTo:
186 */
187- (NSString *) _encryptPassword: (NSString *) plainPassword
188{
189  NSString *pass;
190  NSString* result;
191
192  pass = [plainPassword asCryptedPassUsingScheme: _userPasswordAlgorithm];
193
194  if (pass == nil)
195    {
196      [self errorWithFormat: @"Unsupported user-password algorithm: %@", _userPasswordAlgorithm];
197      return nil;
198    }
199
200  if ([_userPasswordAlgorithm caseInsensitiveCompare: @"md5-crypt"] == NSOrderedSame ||
201      [_userPasswordAlgorithm caseInsensitiveCompare: @"sha256-crypt"] == NSOrderedSame ||
202      [_userPasswordAlgorithm caseInsensitiveCompare: @"sha512-crypt"] == NSOrderedSame)
203    {
204      _userPasswordAlgorithm = @"crypt";
205    }
206
207  if (_prependPasswordScheme)
208    result = [NSString stringWithFormat: @"{%@}%@", _userPasswordAlgorithm, pass];
209  else
210    result = pass;
211
212  return result;
213}
214
215//
216// SQL sources don't support right now all the password policy
217// stuff supported by OpenLDAP (and others). If we want to support
218// this for SQL sources, we'll have to implement the same
219// kind of logic in this module.
220//
221- (BOOL) checkLogin: (NSString *) _login
222	   password: (NSString *) _pwd
223	       perr: (SOGoPasswordPolicyError *) _perr
224	     expire: (int *) _expire
225	      grace: (int *) _grace
226{
227  EOAdaptorChannel *channel;
228  EOQualifier *qualifier;
229  GCSChannelManager *cm;
230  NSException *ex;
231  NSMutableString *sql;
232  BOOL rc;
233
234  rc = NO;
235
236  _login = [_login stringByReplacingString: @"'"  withString: @"''"];
237  cm = [GCSChannelManager defaultChannelManager];
238  channel = [cm acquireOpenChannelForURL: _viewURL];
239  if (channel)
240    {
241      if (_loginFields)
242        {
243          NSMutableArray *qualifiers;
244          NSString *field;
245          EOQualifier *loginQualifier;
246          int i;
247
248          qualifiers = [NSMutableArray arrayWithCapacity: [_loginFields count]];
249          for (i = 0; i < [_loginFields count]; i++)
250            {
251              field = [_loginFields objectAtIndex: i];
252              loginQualifier = [[EOKeyValueQualifier alloc] initWithKey: field
253                                              operatorSelector: EOQualifierOperatorEqual
254                                                         value: _login];
255              [loginQualifier autorelease];
256              [qualifiers addObject: loginQualifier];
257            }
258          qualifier = [[EOOrQualifier alloc] initWithQualifierArray: qualifiers];
259        }
260      else
261        {
262          qualifier = [[EOKeyValueQualifier alloc] initWithKey: @"c_uid"
263                                              operatorSelector: EOQualifierOperatorEqual
264                                                         value: _login];
265        }
266      [qualifier autorelease];
267      sql = [NSMutableString stringWithFormat: @"SELECT c_password"
268                             @" FROM %@"
269                             @" WHERE ",
270                             [_viewURL gcsTableName]];
271      if (_authenticationFilter)
272        {
273          qualifier = [[EOAndQualifier alloc] initWithQualifiers:
274                                                qualifier,
275                       [EOQualifier qualifierWithQualifierFormat: _authenticationFilter],
276                                              nil];
277          [qualifier autorelease];
278        }
279      [qualifier _gcsAppendToString: sql];
280
281      ex = [channel evaluateExpressionX: sql];
282      if (!ex)
283        {
284          NSDictionary *row;
285          NSArray *attrs;
286          NSString *value;
287
288          attrs = [channel describeResults: NO];
289          row = [channel fetchAttributes: attrs  withZone: NULL];
290          value = [row objectForKey: @"c_password"];
291
292          rc = [self _isPassword: _pwd  equalTo: value];
293	  [channel cancelFetch];
294        }
295      else
296        [self errorWithFormat: @"could not run SQL '%@': %@", qualifier, ex];
297
298      [cm releaseChannel: channel];
299    }
300  else
301    [self errorWithFormat:@"failed to acquire channel for URL: %@",
302          [_viewURL absoluteString]];
303
304  return rc;
305}
306
307/**
308 * Change a user's password.
309 * @param login the user's login name.
310 * @param oldPassword the previous password.
311 * @param newPassword the new password.
312 * @param perr is not used.
313 * @return YES if the password was successfully changed.
314 */
315- (BOOL) changePasswordForLogin: (NSString *) login
316		    oldPassword: (NSString *) oldPassword
317		    newPassword: (NSString *) newPassword
318			   perr: (SOGoPasswordPolicyError *) perr
319{
320  EOAdaptorChannel *channel;
321  GCSChannelManager *cm;
322  NSException *ex;
323  NSString *sqlstr;
324  BOOL didChange;
325  BOOL isOldPwdOk;
326
327  isOldPwdOk = NO;
328  didChange = NO;
329
330  // Verify current password
331  isOldPwdOk = [self checkLogin:login password:oldPassword perr:perr expire:0 grace:0];
332
333  if (isOldPwdOk)
334    {
335      // Encrypt new password
336      NSString *encryptedPassword = [self _encryptPassword: newPassword];
337      if(encryptedPassword == nil)
338        return NO;
339
340      // Save new password
341      login = [login stringByReplacingString: @"'"  withString: @"''"];
342      cm = [GCSChannelManager defaultChannelManager];
343      channel = [cm acquireOpenChannelForURL: _viewURL];
344      if (channel)
345	{
346	  sqlstr = [NSString stringWithFormat: (@"UPDATE %@"
347						@" SET c_password = '%@'"
348						@" WHERE c_uid = '%@'"),
349			     [_viewURL gcsTableName], encryptedPassword, login];
350
351	  ex = [channel evaluateExpressionX: sqlstr];
352	  if (!ex)
353	    {
354	      didChange = YES;
355	    }
356	  else
357	    {
358	      [self errorWithFormat: @"could not run SQL '%@': %@", sqlstr, ex];
359	    }
360	  [cm releaseChannel: channel];
361	}
362    }
363
364  return didChange;
365}
366
367- (NSString *) _whereClauseFromArray: (NSArray *) theArray
368                               value: (NSString *) theValue
369			       exact: (BOOL) theBOOL
370{
371  NSMutableString *s;
372  int i;
373
374  s = [NSMutableString string];
375
376  for (i = 0; i < [theArray count]; i++)
377    {
378      if (theBOOL)
379	[s appendFormat: @" OR LOWER(%@) = '%@'", [theArray objectAtIndex: i], theValue];
380      else
381	[s appendFormat: @" OR LOWER(%@) LIKE '%%%@%%'", [theArray objectAtIndex: i], theValue];
382    }
383
384  return s;
385}
386
387- (NSDictionary *) _lookupContactEntry: (NSString *) theID
388                         considerEmail: (BOOL) b
389                              inDomain: (NSString *) domain
390{
391  NSMutableDictionary *response;
392  NSMutableArray *qualifiers;
393  NSArray *fieldNames;
394  EOAdaptorChannel *channel;
395  EOQualifier *loginQualifier, *domainQualifier, *qualifier;
396  GCSChannelManager *cm;
397  NSMutableString *sql;
398  NSString *value, *field;
399  NSException *ex;
400  int i;
401
402  response = nil;
403
404  theID = [theID stringByReplacingString: @"'"  withString: @"''"];
405  cm = [GCSChannelManager defaultChannelManager];
406  channel = [cm acquireOpenChannelForURL: _viewURL];
407  if (channel)
408    {
409      qualifiers = [NSMutableArray arrayWithCapacity: [_loginFields count] + 1];
410
411      // Always compare against the c_uid field
412      loginQualifier = [[EOKeyValueQualifier alloc] initWithKey: @"c_uid"
413                                               operatorSelector: EOQualifierOperatorEqual
414                                                          value: theID];
415      [loginQualifier autorelease];
416      [qualifiers addObject: loginQualifier];
417
418      if (_loginFields)
419        {
420          for (i = 0; i < [_loginFields count]; i++)
421            {
422              field = [_loginFields objectAtIndex: i];
423              if ([field caseInsensitiveCompare: @"c_uid"] != NSOrderedSame)
424                {
425                  loginQualifier = [[EOKeyValueQualifier alloc] initWithKey: field
426                                                           operatorSelector: EOQualifierOperatorEqual
427                                                                      value: theID];
428                  [loginQualifier autorelease];
429                  [qualifiers addObject: loginQualifier];
430                }
431            }
432        }
433
434      domainQualifier = nil;
435      if (_domainField && domain)
436        {
437          domainQualifier = [[EOKeyValueQualifier alloc] initWithKey: _domainField
438                                                    operatorSelector: EOQualifierOperatorEqual
439                                                               value: domain];
440          [domainQualifier autorelease];
441        }
442
443      if (b)
444        {
445          // Always compare againts the mail field
446          loginQualifier = [[EOKeyValueQualifier alloc] initWithKey: @"mail"
447                                                   operatorSelector: EOQualifierOperatorEqual
448                                                              value: [theID lowercaseString]];
449          [loginQualifier autorelease];
450          [qualifiers addObject: loginQualifier];
451
452	  if (_mailFields)
453	    {
454              for (i = 0; i < [_mailFields count]; i++)
455                {
456                  field = [_mailFields objectAtIndex: i];
457                  if ([field caseInsensitiveCompare: @"mail"] != NSOrderedSame
458                      && ![_loginFields containsObject: field])
459                    {
460                      loginQualifier = [[EOKeyValueQualifier alloc] initWithKey: field
461                                                               operatorSelector: EOQualifierOperatorEqual
462                                                                          value: [theID lowercaseString]];
463                      [loginQualifier autorelease];
464                      [qualifiers addObject: loginQualifier];
465                    }
466                }
467            }
468	}
469
470      sql = [NSMutableString stringWithFormat: @"SELECT *"
471                             @" FROM %@"
472                             @" WHERE ",
473                             [_viewURL gcsTableName]];
474      qualifier = [[EOOrQualifier alloc] initWithQualifierArray: qualifiers];
475      if (domainQualifier)
476        qualifier = [[EOAndQualifier alloc] initWithQualifiers: domainQualifier, qualifier, nil];
477      [qualifier _gcsAppendToString: sql];
478
479      ex = [channel evaluateExpressionX: sql];
480      if (!ex)
481        {
482	  NSMutableArray *emails;
483
484          response = [[channel fetchAttributes: [channel describeResults: NO]
485                                      withZone: NULL] mutableCopy];
486          [response autorelease];
487	  [channel cancelFetch];
488
489          /* Convert all c_ fields to obtain their ldif equivalent */
490          fieldNames = [response allKeys];
491          for (i = 0; i < [fieldNames count]; i++)
492            {
493              field = [fieldNames objectAtIndex: i];
494              if ([field hasPrefix: @"c_"])
495                [response setObject: [response objectForKey: field]
496                             forKey: [field substringFromIndex: 2]];
497            }
498
499          // FIXME
500          // We have to do this here since we do not manage modules
501          // constraints right now over a SQL backend.
502          [response setObject: [NSNumber numberWithBool: YES] forKey: @"CalendarAccess"];
503          [response setObject: [NSNumber numberWithBool: YES] forKey: @"MailAccess"];
504          [response setObject: [NSNumber numberWithBool: YES] forKey: @"ActiveSyncAccess"];
505
506	  // We set the domain, if any
507          value = nil;
508	  if (_domain)
509	    value = _domain;
510	  else if (_domainField)
511            value = [response objectForKey: _domainField];
512          if (![value isNotNull])
513	    value = @"";
514	  [response setObject: value forKey: @"c_domain"];
515
516	  // We populate all mail fields
517	  emails = [NSMutableArray array];
518
519	  if ([response objectForKey: @"mail"])
520	    [emails addObject: [response objectForKey: @"mail"]];
521
522	  if (_mailFields && [_mailFields count] > 0)
523	    {
524	      NSString *s;
525	      int i;
526
527	      for (i = 0; i < [_mailFields count]; i++)
528		if ((s = [response objectForKey: [_mailFields objectAtIndex: i]]) &&
529		    [[s stringByTrimmingSpaces] length] > 0)
530		  [emails addObjectsFromArray: [s componentsSeparatedByString: @" "]];
531	    }
532
533	  [response setObject: emails  forKey: @"c_emails"];
534          if (_imapHostField)
535            {
536              value = [response objectForKey: _imapHostField];
537              if ([value isNotNull])
538                [response setObject: value forKey: @"c_imaphostname"];
539            }
540
541          if (_sieveHostField)
542            {
543              value = [response objectForKey: _sieveHostField];
544              if ([value isNotNull])
545                [response setObject: value forKey: @"c_sievehostname"];
546            }
547
548          // We check if the user can authenticate
549          if (_authenticationFilter)
550            {
551              EOQualifier *q_uid, *q_auth;
552
553              sql = [NSMutableString stringWithFormat: @"SELECT c_uid"
554                                     @" FROM %@"
555                                     @" WHERE ",
556                                     [_viewURL gcsTableName]];
557
558              q_auth = [EOQualifier qualifierWithQualifierFormat: _authenticationFilter];
559
560              q_uid = [[EOKeyValueQualifier alloc] initWithKey: @"c_uid"
561                                              operatorSelector: EOQualifierOperatorEqual
562                                                         value: theID];
563              [q_uid autorelease];
564
565              qualifier = [[EOAndQualifier alloc] initWithQualifiers: q_uid, q_auth, nil];
566              [qualifier autorelease];
567              [qualifier _gcsAppendToString: sql];
568
569              ex = [channel evaluateExpressionX: sql];
570              if (!ex)
571                {
572                  NSDictionary *authResponse;
573
574                  authResponse = [channel fetchAttributes: [channel describeResults: NO]  withZone: NULL];
575                  [response setObject: [NSNumber numberWithBool: [authResponse count] > 0] forKey: @"canAuthenticate"];
576                  [channel cancelFetch];
577                }
578              else
579                [self errorWithFormat: @"could not run SQL '%@': %@", sql, ex];
580            }
581          else
582            [response setObject: [NSNumber numberWithBool: YES] forKey: @"canAuthenticate"];
583
584          // We check if we should use a different login for IMAP
585          if (_imapLoginField)
586            {
587              if ([[response objectForKey: _imapLoginField] isNotNull])
588                [response setObject: [response objectForKey: _imapLoginField] forKey: @"c_imaplogin"];
589            }
590
591	  // We check if it's a resource of not
592	  if (_kindField)
593	    {
594	      if ((value = [response objectForKey: _kindField]) && [value isNotNull])
595		{
596		  if ([value caseInsensitiveCompare: @"location"] == NSOrderedSame ||
597		      [value caseInsensitiveCompare: @"thing"] == NSOrderedSame ||
598		      [value caseInsensitiveCompare: @"group"] == NSOrderedSame)
599		    {
600		      [response setObject: [NSNumber numberWithInt: 1]
601				forKey: @"isResource"];
602		    }
603		}
604	    }
605
606	  if (_multipleBookingsField)
607	    {
608	      if ((value = [response objectForKey: _multipleBookingsField]))
609		{
610		  [response setObject: [NSNumber numberWithInt: [value intValue]]
611			    forKey: @"numberOfSimultaneousBookings"];
612		}
613	    }
614
615          [response setObject: self forKey: @"source"];
616        }
617      else
618        [self errorWithFormat: @"could not run SQL '%@': %@", sql, ex];
619      [cm releaseChannel: channel];
620    }
621  else
622    [self errorWithFormat:@"failed to acquire channel for URL: %@",
623          [_viewURL absoluteString]];
624
625  return response;
626}
627
628
629- (NSDictionary *) lookupContactEntry: (NSString *) theID
630                             inDomain: (NSString *) domain
631{
632  return [self _lookupContactEntry: theID  considerEmail: NO inDomain: domain];
633}
634
635- (NSDictionary *) lookupContactEntryWithUIDorEmail: (NSString *) entryID
636                                           inDomain: (NSString *) domain
637{
638  return [self _lookupContactEntry: entryID  considerEmail: YES inDomain: domain];
639}
640
641/* Returns an EOQualifier of the following form:
642 * (_domainField = domain OR _domainField = visibleDomain1 [...])
643 * Should only be called on SQL sources using _domainField name.
644 */
645- (EOQualifier *) _visibleDomainsQualifierFromDomain: (NSString *) domain
646{
647  int i;
648  EOQualifier *qualifier, *domainQualifier;
649  NSArray *visibleDomains;
650  NSMutableArray *qualifiers;
651  NSString *currentDomain;
652
653  SOGoSystemDefaults *sd;
654
655  /* Return early if no domain or if being called on a 'static' sql source */
656  if (!domain || !_domainField)
657    return nil;
658
659  sd = [SOGoSystemDefaults sharedSystemDefaults];
660  visibleDomains = [sd visibleDomainsForDomain: domain];
661  qualifier = nil;
662
663  domainQualifier =
664    [[EOKeyValueQualifier alloc] initWithKey: _domainField
665                            operatorSelector: EOQualifierOperatorEqual
666                                       value: domain];
667  [domainQualifier autorelease];
668
669  if ([visibleDomains count])
670    {
671      qualifiers = [NSMutableArray arrayWithCapacity: [visibleDomains count] + 1];
672      [qualifiers addObject: domainQualifier];
673      for(i = 0; i < [visibleDomains count]; i++)
674        {
675          currentDomain = [visibleDomains objectAtIndex: i];
676          qualifier =
677            [[EOKeyValueQualifier alloc] initWithKey: _domainField
678                                    operatorSelector: EOQualifierOperatorEqual
679                                               value: currentDomain];
680          [qualifier autorelease];
681          [qualifiers addObject: qualifier];
682        }
683      qualifier = [[EOOrQualifier alloc] initWithQualifierArray: qualifiers];
684      [qualifier autorelease];
685    }
686
687  return qualifier ? qualifier : domainQualifier;
688}
689
690
691- (NSArray *) allEntryIDsVisibleFromDomain: (NSString *) domain
692{
693  EOAdaptorChannel *channel;
694  EOQualifier *domainQualifier;
695  GCSChannelManager *cm;
696  NSException *ex;
697  NSMutableArray *results;
698  NSMutableString *sql;
699
700  results = [NSMutableArray array];
701
702  cm = [GCSChannelManager defaultChannelManager];
703  channel = [cm acquireOpenChannelForURL: _viewURL];
704  if (channel)
705    {
706      sql = [NSMutableString stringWithFormat: @"SELECT c_uid FROM %@",
707                      [_viewURL gcsTableName]];
708
709      if (_domainField)
710        {
711          if ([domain length])
712            {
713              domainQualifier =
714                [self _visibleDomainsQualifierFromDomain: domain];
715              if (domainQualifier)
716                {
717                  [sql appendString: @" WHERE "];
718                  [domainQualifier _gcsAppendToString: sql];
719                }
720            }
721          else
722            {
723              /* Should not happen but avoid returning the whole table
724               * if a domain should have been defined */
725              [sql appendFormat: @" WHERE %@ is NULL", _domainField];
726            }
727        }
728
729      ex = [channel evaluateExpressionX: sql];
730      if (!ex)
731        {
732          NSDictionary *row;
733          NSArray *attrs;
734          NSString *value;
735
736          attrs = [channel describeResults: NO];
737
738          while ((row = [channel fetchAttributes: attrs withZone: NULL]))
739            {
740              value = [row objectForKey: @"c_uid"];
741              if (value)
742                [results addObject: value];
743            }
744        }
745      else
746        [self errorWithFormat: @"could not run SQL '%@': %@", sql, ex];
747      [cm releaseChannel: channel];
748    }
749  else
750    [self errorWithFormat:@"failed to acquire channel for URL: %@",
751          [_viewURL absoluteString]];
752
753
754  return results;
755}
756
757- (NSArray *) allEntryIDs
758{
759  return [self allEntryIDsVisibleFromDomain: nil];
760}
761
762- (NSArray *) fetchContactsMatching: (NSString *) filter
763                           inDomain: (NSString *) domain
764{
765  EOAdaptorChannel *channel;
766  NSMutableArray *results;
767  GCSChannelManager *cm;
768  NSException *ex;
769  NSMutableString *sql;
770  NSString *lowerFilter;
771
772  results = [NSMutableArray array];
773
774  cm = [GCSChannelManager defaultChannelManager];
775  channel = [cm acquireOpenChannelForURL: _viewURL];
776  if (channel)
777    {
778      lowerFilter = [filter lowercaseString];
779      lowerFilter = [lowerFilter stringByReplacingString: @"'"  withString: @"''"];
780
781      sql = [NSMutableString stringWithFormat: (@"SELECT *"
782                                         @" FROM %@"
783                                         @" WHERE"
784                                         @" (LOWER(c_cn) LIKE '%%%@%%'"
785                                         @" OR LOWER(mail) LIKE '%%%@%%'"),
786                      [_viewURL gcsTableName],
787                      lowerFilter, lowerFilter];
788
789      if (_mailFields && [_mailFields count] > 0)
790        {
791          [sql appendString: [self _whereClauseFromArray: _mailFields  value: lowerFilter  exact: NO]];
792        }
793
794      [sql appendString: @")"];
795
796      if (_domainField)
797        {
798          if ([domain length])
799            {
800              EOQualifier *domainQualifier;
801              domainQualifier =
802                [self _visibleDomainsQualifierFromDomain: domain];
803              if (domainQualifier)
804                {
805                  [sql appendFormat: @" AND ("];
806                  [domainQualifier _gcsAppendToString: sql];
807                  [sql appendFormat: @")"];
808                }
809            }
810          else
811            [sql appendFormat: @" AND %@ IS NULL", _domainField];
812        }
813
814      ex = [channel evaluateExpressionX: sql];
815      if (!ex)
816        {
817          NSDictionary *row;
818          NSArray *attrs;
819
820          attrs = [channel describeResults: NO];
821
822          while ((row = [channel fetchAttributes: attrs withZone: NULL]))
823            {
824              row = [row mutableCopy];
825              [(NSMutableDictionary *) row setObject: self forKey: @"source"];
826              [results addObject: row];
827              [row release];
828            }
829        }
830      else
831        [self errorWithFormat: @"could not run SQL '%@': %@", sql, ex];
832      [cm releaseChannel: channel];
833    }
834  else
835    [self errorWithFormat:@"failed to acquire channel for URL: %@",
836          [_viewURL absoluteString]];
837
838  return results;
839}
840
841- (void) setSourceID: (NSString *) newSourceID
842{
843}
844
845- (NSString *) sourceID
846{
847  return _sourceID;
848}
849
850- (void) setDisplayName: (NSString *) newDisplayName
851{
852}
853
854- (NSString *) displayName
855{
856  /* This method is only used when supporting user "source" addressbooks,
857     which is only supported by the LDAP backend for now. */
858  return _sourceID;
859}
860
861- (void) setListRequiresDot: (BOOL) newListRequiresDot
862{
863}
864
865- (BOOL) listRequiresDot
866{
867  /* This method is not implemented for SQLSource. It must enable a mechanism
868     where using "." is not required to list the content of addressbooks. */
869  return YES;
870}
871
872/* card editing */
873- (void) setModifiers: (NSArray *) newModifiers
874{
875}
876
877- (NSArray *) modifiers
878{
879  /* This method is only used when supporting card editing,
880     which is only supported by the LDAP backend for now. */
881  return nil;
882}
883
884- (NSException *) addContactEntry: (NSDictionary *) roLdifRecord
885                           withID: (NSString *) aId
886{
887  NSString *reason;
888
889  reason = [NSString stringWithFormat: @"method '%@' is not available"
890                     @" for class '%@'", NSStringFromSelector (_cmd),
891                     NSStringFromClass (object_getClass(self))];
892
893  return [NSException exceptionWithName: @"SQLSourceIOException"
894                                 reason: reason
895                               userInfo: nil];
896}
897
898- (NSException *) updateContactEntry: (NSDictionary *) roLdifRecord
899{
900  NSString *reason;
901
902  reason = [NSString stringWithFormat: @"method '%@' is not available"
903                     @" for class '%@'", NSStringFromSelector (_cmd),
904                     NSStringFromClass (object_getClass(self))];
905
906  return [NSException exceptionWithName: @"SQLSourceIOException"
907                                 reason: reason
908                               userInfo: nil];
909}
910
911- (NSException *) removeContactEntryWithID: (NSString *) aId
912{
913  NSString *reason;
914
915  reason = [NSString stringWithFormat: @"method '%@' is not available"
916                     @" for class '%@'", NSStringFromSelector (_cmd),
917                     NSStringFromClass (object_getClass(self))];
918
919  return [NSException exceptionWithName: @"SQLSourceIOException"
920                                 reason: reason
921                               userInfo: nil];
922}
923
924/* user addressbooks */
925- (BOOL) hasUserAddressBooks
926{
927  return NO;
928}
929
930- (NSArray *) addressBookSourcesForUser: (NSString *) user
931{
932  return nil;
933}
934
935- (NSException *) addAddressBookSource: (NSString *) newId
936                       withDisplayName: (NSString *) newDisplayName
937                               forUser: (NSString *) user
938{
939  NSString *reason;
940
941  reason = [NSString stringWithFormat: @"method '%@' is not available"
942                     @" for class '%@'", NSStringFromSelector (_cmd),
943                     NSStringFromClass (object_getClass(self))];
944
945  return [NSException exceptionWithName: @"SQLSourceIOException"
946                                 reason: reason
947                               userInfo: nil];
948}
949
950- (NSException *) renameAddressBookSource: (NSString *) newId
951                          withDisplayName: (NSString *) newDisplayName
952                                  forUser: (NSString *) user
953{
954  NSString *reason;
955
956  reason = [NSString stringWithFormat: @"method '%@' is not available"
957                     @" for class '%@'", NSStringFromSelector (_cmd),
958                     NSStringFromClass (object_getClass(self))];
959
960  return [NSException exceptionWithName: @"SQLSourceIOException"
961                                 reason: reason
962                               userInfo: nil];
963}
964
965- (NSException *) removeAddressBookSource: (NSString *) newId
966                                  forUser: (NSString *) user
967{
968  NSString *reason;
969
970  reason = [NSString stringWithFormat: @"method '%@' is not available"
971                     @" for class '%@'", NSStringFromSelector (_cmd),
972                     NSStringFromClass (object_getClass(self))];
973
974  return [NSException exceptionWithName: @"SQLSourceIOException"
975                                 reason: reason
976                               userInfo: nil];
977}
978
979@end
980