1 /** 2 Copyright (C) 2004 Free Software Foundation, Inc. 3 4 Written by: Richard Frith-Macdonald <rfm@gnu.org> 5 Date: April 2004 6 7 This file is part of the SQLClient Library. 8 9 This library is free software; you can redistribute it and/or 10 modify it under the terms of the GNU Lesser General Public 11 License as published by the Free Software Foundation; either 12 version 3 of the License, or (at your option) any later version. 13 14 This library is distributed in the hope that it will be useful, 15 but WITHOUT ANY WARRANTY; without even the implied warranty of 16 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 17 Lesser General Public License for more details. 18 19 You should have received a copy of the GNU Lesser General Public 20 License along with this library; if not, write to the Free 21 Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111 USA. 22 23 <title>SQLClient documentation</title> 24 <chapter> 25 <heading>The SQLClient library</heading> 26 <section> 27 <heading>What is the SQLClient library?</heading> 28 <p> 29 The SQLClient library is designed to provide a simple interface to SQL 30 databases for GNUstep applications. It does not attempt the sort of 31 abstraction provided by the much more sophisticated GDL2 library, but 32 rather allows applications to directly execute SQL queries and 33 statements. 34 </p> 35 <p> 36 SQLClient provides for the Objective-C programmer much the same thing 37 that JDBC provides for the Java programmer (though SQLClient is a bit 38 faster, easier to use, and easier to add new database backends for 39 than JDBC). 40 </p> 41 <p> 42 The major features of the SQLClient library are - 43 </p> 44 <list> 45 <item> 46 Simple API for executing queries and statements ... a variable 47 length sequence of comma separated strings and other objects 48 (NSNumber, NSDate, NSData) are concatenated into a single SQL 49 statement and executed. 50 </item> 51 <item> 52 Simple API ([SQLTransaction])for combining multiple SQL statements 53 into a single transaction which can be used to minimise client-server 54 interactions to get the best possible performance from your database. 55 </item> 56 <item> 57 Supports multiple sumultaneous named connections to a database 58 server in a thread-safe manner.<br /> 59 </item> 60 <item> 61 Supports multiple simultaneous connections to different database 62 servers with backend driver bundles loaded for different database 63 engines. Clear, simple subclassing of the abstract base class to 64 enable easy implementation of new backend bundles. 65 </item> 66 <item> 67 Configuration for all connections held in one place and referenced 68 by connection name for ease of configuration control. 69 Changes via NSUserDefaults can even allow reconfiguration of 70 client instances within a running application. 71 </item> 72 <item> 73 Thread safe operation ... The base class supports locking such that 74 a single instance can be shared between multiple threads. 75 </item> 76 <item> 77 Support for standalone web applications ... eg to allow data to be 78 added to the database by people posting web forms to the application. 79 </item> 80 <item> 81 Supports notification of connection to and disconnection from the 82 database server. 83 </item> 84 </list> 85 </section> 86 <section> 87 <heading>What backend bundles are available?</heading> 88 <p> 89 Current backend bundles are - 90 </p> 91 <list> 92 <item> 93 ECPG - a bundle using the embedded SQL interface for postgres.<br /> 94 This is based on a similar code which was in production use 95 for over eighteen months, so it should be reliable, but inefficient. 96 </item> 97 <item> 98 Postgres - a bundle using the libpq native interface for postgres.<br /> 99 This is the preferred backend as it allows 'SELECT FOR UPDATE', which 100 the ECPG backend cannot support due to limitations in the postgres 101 implementation of cursors. The code is now well tested and known 102 to be efficient. 103 </item> 104 <item> 105 MySQL - a bundle using the mysqlclient library for *recent* MySQL.<br /> 106 I don't use MySQL ... but the test program ran successfully with a 107 vanilla install of the MySQL packages for recent Debian unstable. 108 </item> 109 <item> 110 SQLite - a bundle using the sqlite3 library which supports an 111 SQL-like API for direct access to a database file (rather than 112 acting as a client of a database server process).<br /> 113 Not as functional as the other backends (doesn't support dates 114 for instance), but good enough for many purposes and very 115 'lightweight'. See http://www.sqlite.org 116 </item> 117 <item> 118 Oracle - a bundle using embedded SQL for Oracle.<br /> 119 Completely untested ... may even need some work to compile ... but 120 this *is* based on code which was working about a year ago.<br /> 121 No support for BLOBs yet. 122 </item> 123 </list> 124 </section> 125 <section> 126 <heading>Where can you get it? How can you install it?</heading> 127 <p> 128 The SQLClient library is currently only available via CVS from the 129 GNUstep CVS repository.<br /> 130 See <https://savannah.gnu.org/cvs/?group=gnustep><br /> 131 You need to check out <code>gnustep/dev-libs/SQLClient</code> 132 </p> 133 <p> 134 To build this library you must have a basic GNUstep environment set up ... 135 </p> 136 <list> 137 <item> 138 The gnustep-make package must have been built and installed. 139 </item> 140 <item> 141 The gnustep-base package must have been built and installed. 142 </item> 143 <item> 144 The Performance library (from the dev-libs area in GNUstep CVS) 145 must have been built and installed. 146 </item> 147 <item> 148 If this environment is in place, all you should need to do is run 'make' 149 to configure and build the library, 'make install' to install it. 150 </item> 151 <item> 152 Then you can run the test programs. 153 </item> 154 <item> 155 Your most likely problems are that the configure script may not 156 detect the database libraries you want ... Please figure out how 157 to modify <code>configure.ac</code> so that it will detect the 158 required headers and libraries on your system, and supply na patch. 159 </item> 160 <item> 161 Once the library is installed, you can include the header file 162 <code><SQLClient/SQLClient.h%gt;</code> and link your programs 163 with the <code>SQLClient</code> library to use it. 164 </item> 165 </list> 166 <p> 167 Bug reports, patches, and contributions (eg a backend bundle for a 168 new database) should be entered on the GNUstep project page 169 <http://savannah.gnu.org/projects/gnustep> and the bug 170 reporting page <http://savannah.gnu.org/bugs/?group=gnustep> 171 </p> 172 </section> 173 </chapter> 174 175 $Date: 2014-10-13 11:47:06 +0100 (Mon, 13 Oct 2014) $ $Revision: 38114 $ 176 */ 177 178 #ifndef INCLUDED_SQLClient_H 179 #define INCLUDED_SQLClient_H 180 181 #import <Foundation/NSArray.h> 182 #import <Foundation/NSObject.h> 183 184 @class NSConditionLock; 185 @class NSCountedSet; 186 @class NSMapTable; 187 @class NSMutableDictionary; 188 @class NSMutableSet; 189 @class NSString; 190 @class NSThread; 191 192 @class GSCache; 193 @class SQLTransaction; 194 195 /** 196 * Notification sent when an instance becomes connected to the database 197 * server. The notification object is the instance connected. 198 */ 199 extern NSString * const SQLClientDidConnectNotification; 200 201 /** 202 * Notification sent when an instance becomes disconnected from the database 203 * server. The notification object is the instance disconnected. 204 */ 205 extern NSString * const SQLClientDidDisconnectNotification; 206 207 #if !defined(SQLCLIENT_PRIVATE) 208 #define SQLCLIENT_PRIVATE @private 209 #endif 210 211 /** 212 * <p>An enhanced array to represent a record returned from a query. 213 * You should <em>NOT</em> try to create instances of this class 214 * except via the +newWithValues:keys:count: method. 215 * </p> 216 * <p>SQLRecord is the abstract base class of a class cluster. 217 * If you wish to subclass it you must implement the primitive methods 218 * +newWithValues:keys:count: -count -keyAtIndex: -objectAtIndex: 219 * and -replaceObjectAtIndex:withObject: 220 * </p> 221 * <p>NB. You do not need to use SQLRecord (or a subclass of it), all you 222 * actually need to supply is a class which responds to the 223 * +newWithValues:keys:count: method that the system uses to create 224 * new records ... none of the other methods of the SQLRecord class 225 * are used internally by the SQLClient system. 226 * </p> 227 */ 228 @interface SQLRecord : NSArray 229 230 /** 231 * Create a new SQLRecord containing the specified fields.<br /> 232 * NB. The values and keys are <em>retained</em> by the record rather 233 * than being copied.<br /> 234 * A nil value is represented by [NSNull null].<br /> 235 * Keys must be unique string values (case insensitive comparison). 236 */ 237 + (id) newWithValues: (id*)v 238 keys: (NSString**)k 239 count: (unsigned int)c; 240 241 /** 242 * Returns an array containing the names of all the fields in the record. 243 */ 244 - (NSArray*) allKeys; 245 246 /** 247 * Returns the number of items in the record.<br /> 248 * Subclasses must implement this method. 249 */ 250 - (NSUInteger) count; 251 252 /** 253 * Return the record as a mutable dictionary with the keys as the 254 * record field names standardised to be lowercase strings. 255 */ 256 - (NSMutableDictionary*) dictionary; 257 258 /** 259 * Optimised mechanism for retrieving all keys in order. 260 */ 261 - (void) getKeys: (id*)buf; 262 263 /** 264 * Optimised mechanism for retrieving all objects. 265 */ 266 - (void) getObjects: (id*)buf; 267 268 /** <override-subclass /> 269 * Returns the key at the specified indes.<br /> 270 */ 271 - (NSString*) keyAtIndex: (NSUInteger)index; 272 273 /** <override-subclass /> 274 * Returns the object at the specified indes.<br /> 275 */ 276 - (id) objectAtIndex: (NSUInteger)index; 277 278 /** 279 * Returns the value of the named field.<br /> 280 * The field name is case insensitive. 281 */ 282 - (id) objectForKey: (NSString*)key; 283 284 /** 285 * Replaces the value at the specified index.<br /> 286 * Subclasses must implement this method. 287 */ 288 - (void) replaceObjectAtIndex: (NSUInteger)index withObject: (id)anObject; 289 290 /** 291 * Replaces the value of the named field.<br /> 292 * The field name is case insensitive.<br /> 293 * NB. You must be careful not to change the contents of a record which 294 * has been cached (unless you are sure you really want to), as you will 295 * be changing the contents of the cache, not just a private copy. 296 */ 297 - (void) setObject: (id)anObject forKey: (NSString*)aKey; 298 299 /** 300 * Return approximate size of this record in bytes.<br /> 301 * The exclude set is used to specify objects to exclude from the 302 * calculation (to prevent recursion etc). 303 */ 304 - (NSUInteger) sizeInBytes: (NSMutableSet*)exclude; 305 @end 306 307 extern NSString *SQLException; 308 extern NSString *SQLConnectionException; 309 extern NSString *SQLEmptyException; 310 extern NSString *SQLUniqueException; 311 312 /** 313 * Returns the timestamp of the most recent call to SQLClientTimeNow(). 314 */ 315 extern NSTimeInterval SQLClientTimeLast(); 316 317 /** 318 * Convenience function to provide timing information quickly.<br /> 319 * This returns the current date/time, and stores the value for use 320 * by the SQLClientTimeLast() function. 321 */ 322 extern NSTimeInterval SQLClientTimeNow(); 323 324 /** 325 * This returns the timestamp from which any of the SQLClient classes was 326 * first used or SQLClientTimeNow() was first called (whichever came first). 327 */ 328 extern NSTimeInterval SQLClientTimeStart(); 329 330 /** 331 * A convenience method to return the current clock 'tick' ... which is 332 * the current second based on the time we started. This does <em>not</em> 333 * check the current time, but relies on SQLClientTimeLast() returning an 334 * up to date value (so if you need an accurate tick, you should ensure 335 * that SQLClientTimeNow() is called at least once a second).<br /> 336 * The returned value is always greater than zero, and is basically 337 * calculated as (SQLClientTimeLast() - SQLClientTimeStart() + 1).<br /> 338 * In the event that the system clock is reset into the past, the value 339 * of SQLClientTimeStart() is automatically adjusted to ensure that the 340 * result of a call to SQLClientTimeTick() is never less than the result 341 * of any earlier call to the function. 342 */ 343 extern unsigned SQLClientTimeTick(); 344 345 @class SQLClientPool; 346 347 /** 348 * <p>The SQLClient class encapsulates dynamic SQL access to relational 349 * database systems. A shared instance of the class is used for 350 * each database (as identified by the name of the database), and 351 * the number of simultanous database connections is managed too. 352 * </p> 353 * <p>SQLClient is an abstract base class ... when you create an instance 354 * of it, you are actually creating an instance of a concrete subclass 355 * whose implementation is loaded from a bundle. 356 * </p> 357 */ 358 @interface SQLClient : NSObject 359 { 360 SQLCLIENT_PRIVATE 361 void *extra; /** For subclass specific data */ 362 NSRecursiveLock *lock; /** Maintain thread-safety */ 363 /** 364 * A flag indicating whether this instance is currently connected to 365 * the backend database server. This variable must <em>only</em> be 366 * set by the -backendConnect or -backendDisconnect methods. 367 */ 368 BOOL connected; 369 /** 370 * A flag indicating whether this instance is currently within a 371 * transaction. This variable must <em>only</em> be 372 * set by the -begin, -commit or -rollback methods. 373 */ 374 BOOL _inTransaction; /** Are we inside a transaction? */ 375 /** 376 * A flag indicating whether leading and trailing white space in values 377 * read from the database should automatically be removed.<br /> 378 * This should only be modified by the -setShouldTrim: method. 379 */ 380 BOOL _shouldTrim; /** Should whitespace be trimmed? */ 381 NSString *_name; /** Unique identifier for instance */ 382 NSString *_client; /** Identifier within backend */ 383 NSString *_database; /** The configured database name/host */ 384 NSString *_password; /** The configured password */ 385 NSString *_user; /** The configured user */ 386 NSMutableArray *_statements; /** Uncommitted statements */ 387 /** 388 * Timestamp of completion of last operation.<br /> 389 * Maintained by -simpleExecute: -simpleQuery:recordType:listType: 390 * and -cache:simpleQuery:recordType:listType: 391 * Also set for a failed connection attempt, but not reported by the 392 * -lastOperation method in that case. 393 */ 394 NSTimeInterval _lastOperation; 395 NSTimeInterval _lastStart; /** Last op start or connect */ 396 NSTimeInterval _duration; /** Duration logging threshold */ 397 unsigned int _debugging; /** The current debugging level */ 398 GSCache *_cache; /** The cache for query results */ 399 NSThread *_cacheThread; /** Thread for cache queries */ 400 unsigned int _connectFails; /** The count of connection failures */ 401 NSMapTable *_observers; /** Observations of async events */ 402 NSCountedSet *_names; /** Track notification names */ 403 SQLClientPool *_pool; /** The pool of the client (or nil) */ 404 /** Allow for extensions by allocating memory and pointing to it from 405 * the _extra ivar. That way we can avoid binary incompatibility between 406 * minor releases. 407 */ 408 void *_extra; 409 } 410 411 /** 412 * Returns an array containing all the SQLClient instances . 413 */ 414 + (NSArray*) allClients; 415 416 /** 417 * Return an existing SQLClient instance (using +existingClient:) if possible, 418 * or creates one, initialises it using -initWithConfiguration:name:, and 419 * returns the new instance (autoreleased).<br /> 420 * Returns nil on failure. 421 */ 422 + (SQLClient*) clientWithConfiguration: (NSDictionary*)config 423 name: (NSString*)reference; 424 425 /** 426 * Return an existing SQLClient instance for the specified name 427 * if one exists, otherwise returns nil. 428 */ 429 + (SQLClient*) existingClient: (NSString*)reference; 430 431 /** 432 * Returns the maximum number of simultaneous database connections 433 * permitted (set by +setMaxConnections: and defaults to 8) for 434 * connections outside of SQLClientPool instances. 435 */ 436 + (unsigned int) maxConnections; 437 438 /** 439 * <p>Use this method to reduce the number of database connections 440 * currently active so that it is less than the limit set by the 441 * +setMaxConnections: method. This mechanism is used internally 442 * by the class to ensure that, when it is about to open a new 443 * connection, the limit is not exceeded. 444 * </p> 445 * <p>If since is not nil, then any connection which has not been 446 * used more recently than that date is disconnected anyway.<br /> 447 * You can (and probably should) use this periodically to purge 448 * idle connections, but you can also pass a date in the future to 449 * close all connections. 450 * </p> 451 * <p>Purging does not apply to connections made by SQLClientPool 452 * instances. 453 * </p> 454 */ 455 + (void) purgeConnections: (NSDate*)since; 456 457 /** 458 * <p>Set the maximum number of simultaneous database connections 459 * permitted (defaults to 8 and may not be set less than 1). 460 * </p> 461 * <p>This value is used by the +purgeConnections: method to determine how 462 * many connections should be disconnected when it is called. 463 * </p> 464 * <p>Connections used by SQLClientPool instances are not considered by 465 * this maximum. 466 * </p> 467 */ 468 + (void) setMaxConnections: (unsigned int)c; 469 470 /** 471 * Start a transaction for this database client.<br /> 472 * You <strong>must</strong> match this with either a -commit 473 * or a -rollback.<br /> 474 * <p>Normally, if you execute an SQL statement without using this 475 * method first, the <em>autocommit</em> feature is employed, and 476 * the statement takes effect immediately. Use of this method 477 * permits you to execute several statements in sequence, and 478 * only have them take effect (as a single operation) when you 479 * call the -commit method. 480 * </p> 481 * <p>NB. You must <strong>not</strong> execute an SQL statement 482 * which would start a transaction directly ... use only this 483 * method. 484 * </p> 485 * <p>Where possible, consider using the [SQLTransaction] class rather 486 * than calling -begin -commit or -rollback yourself. 487 * </p> 488 */ 489 - (void) begin; 490 491 /** 492 * <p>Build an sql query string using the supplied arguments. 493 * </p> 494 * <p>This method has at least one argument, the string starting the 495 * query to be executed (which must have the prefix 'select '). 496 * </p> 497 * <p>Additional arguments are a nil terminated list which also be strings, 498 * and these are appended to the statement.<br /> 499 * Any string arguments are assumed to have been quoted appropriately 500 * already, but non-string arguments are automatically quoted using the 501 * -quote: method. 502 * </p> 503 * <example> 504 * sql = [db buildQuery: @"SELECT Name FROM ", table, nil]; 505 * </example> 506 * <p>Upon error, an exception is raised. 507 * </p> 508 * <p>The method returns a string containing sql suitable for passing to 509 * the -simpleQuery:recordType:listType: 510 * or -cache:simpleQuery:recordType:listType: methods. 511 * </p> 512 */ 513 - (NSString*) buildQuery: (NSString*)stmt,...; 514 515 /** 516 * Takes the query statement and substitutes in values from 517 * the dictionary where markup of the format {key} is found.<br /> 518 * Returns the resulting query string. 519 * <example> 520 * sql = [db buildQuery: @"SELECT Name FROM {Table} WHERE ID = {ID}" 521 * with: values]; 522 * </example> 523 * <p>Any non-string values in the dictionary will be replaced by 524 * the results of the -quote: method.<br /> 525 * The markup format may also be {key?default} where <em>default</em> 526 * is a string to be used if there is no value for the <em>key</em> 527 * in the dictionary. 528 * </p> 529 * <p>The method returns a string containing sql suitable for passing to 530 * the -simpleQuery:recordType:listType: 531 * or -cache:simpleQuery:recordType:listType: methods. 532 * </p> 533 */ 534 - (NSString*) buildQuery: (NSString*)stmt with: (NSDictionary*)values; 535 536 /** 537 * Return the client name for this instance.<br /> 538 * Normally this is useful only for debugging/reporting purposes, but 539 * if you are using multiple instances of this class in your application, 540 * and you are using embedded SQL, you will need to use this 541 * method to fetch the client/connection name and store its C-string 542 * representation in a variable 'connectionName' declared to the sql 543 * preprocessor, so you can then have statements of the form - 544 * 'exec sql at :connectionName ...'. 545 */ 546 - (NSString*) clientName; 547 548 /** 549 * Complete a transaction for this database client.<br /> 550 * This <strong>must</strong> match an earlier -begin. 551 * <p>NB. You must <strong>not</strong> execute an SQL statement 552 * which would commit or rollback a transaction directly ... use 553 * only this method or the -rollback method. 554 * </p> 555 * <p>Where possible, consider using the [SQLTransaction] class rather 556 * than calling -begin -commit or -rollback yourself. 557 * </p> 558 */ 559 - (void) commit; 560 561 /** 562 * If the <em>connected</em> instance variable is NO, this method 563 * calls -backendConnect to ensure that there is a connection to the 564 * database server established. Returns the result.<br /> 565 * Performs any necessary locking for thread safety.<br /> 566 * This method also counts the number of consecutive failed connection 567 * attempts. A delay is enforced between each connection attempt, with 568 * the length of the delay growing with each failure. This ensures 569 * that applications which fail to deal with connection failures, and 570 * just keep trying to reconnect, will not overload the system/server.<br /> 571 * The maximum delay is 30 seconds, so when the database server is restarted, 572 * the application can reconnect reasonably quickly. 573 */ 574 - (BOOL) connect; 575 576 /** 577 * Return a flag to say whether a connection to the database server is 578 * currently live (the value of the 'connected' instance variable).<br /> 579 * This is mostly useful for debug/reporting. 580 */ 581 - (BOOL) connected; 582 583 /** 584 * Return the database name for this instance (or nil). 585 */ 586 - (NSString*) database; 587 588 /** 589 * If the <em>connected</em> instance variable is YES, this method 590 * calls -backendDisconnect to ensure that the connection to the 591 * database server is dropped.<br /> 592 * Performs any necessary locking for thread safety. 593 */ 594 - (void) disconnect; 595 596 /** 597 * Perform arbitrary operation <em>which does not return any value.</em><br /> 598 * This arguments to this method are a nil terminated list which are 599 * concatenated in the manner of the -query:,... method.<br /> 600 * Any string arguments are assumed to have been quoted appropriately 601 * already, but non-string arguments are automatically quoted using the 602 * -quote: method. 603 * <example> 604 * [db execute: @"UPDATE ", table, @" SET Name = ", 605 * myName, " WHERE ID = ", myId, nil]; 606 * </example> 607 * Where the database backend support it, this method returns the count of 608 * the number of rows to which the operation applied. Otherwise this 609 * returns -1. 610 */ 611 - (NSInteger) execute: (NSString*)stmt,...; 612 613 /** 614 * Takes the statement and substitutes in values from 615 * the dictionary where markup of the format {key} is found.<br /> 616 * Passes the result to the -execute:,... method. 617 * <example> 618 * [db execute: @"UPDATE {Table} SET Name = {Name} WHERE ID = {ID}" 619 * with: values]; 620 * </example> 621 * Any non-string values in the dictionary will be replaced by 622 * the results of the -quote: method.<br /> 623 * The markup format may also be {key?default} where <em>default</em> 624 * is a string to be used if there is no value for the <em>key</em> 625 * in the dictionary.<br /> 626 * Where the database backend support it, this method returns the count of 627 * the number of rows to which the operation applied. Otherwise this 628 * returns -1. 629 */ 630 - (NSInteger) execute: (NSString*)stmt with: (NSDictionary*)values; 631 632 /** 633 * Calls -initWithConfiguration:name: passing a nil reference name. 634 */ 635 - (id) initWithConfiguration: (NSDictionary*)config; 636 637 /** 638 * Calls -initWithConfiguration:name:pool: passing NO to say the client is 639 * not in a pool. 640 */ 641 - (id) initWithConfiguration: (NSDictionary*)config 642 name: (NSString*)reference; 643 644 /** 645 * Initialise using the supplied configuration, or if that is nil, try to 646 * use values from NSUserDefaults (and automatically update when the 647 * defaults change).<br /> 648 * Uses the reference name to determine configuration information ... and if 649 * a nil name is supplied, defaults to the value of SQLClientName in the 650 * configuration dictionary (or in the standard user defaults). If there is 651 * no value for SQLClientName, uses the string 'Database'.<br /> 652 * If pool is nil and a SQLClient instance already exists with the 653 * name used for this instance, the receiver is deallocated and the existing 654 * instance is retained and returned ... there may only ever be one instance 655 * for a particular reference name which is not in a pool.<br /> 656 * <br /> 657 * The config argument (or the SQLClientReferences user default) 658 * is a dictionary with names as keys and dictionaries 659 * as its values. Configuration entries from the dictionary corresponding 660 * to the database client are used if possible, general entries are used 661 * otherwise.<br /> 662 * Database ... is the name of the database to use, if it is missing 663 * then 'Database' may be used instead.<br /> 664 * User ... is the name of the database user to use, if it is missing 665 * then 'User' may be used instead.<br /> 666 * Password ... is the name of the database user password, if it is 667 * missing then 'Password' may be used instead.<br /> 668 * ServerType ... is the name of the backend server to be used ... by 669 * convention the name of a bundle containing the interface to that backend. 670 * If this is missing then 'Postgres' is used.<br /> 671 * The database name may be of the format 'name@host:port' when you wish to 672 * connect to a database on a different host over the network. 673 */ 674 - (id) initWithConfiguration: (NSDictionary*)config 675 name: (NSString*)reference 676 pool: (SQLClientPool*)pool; 677 678 /** Two clients are considered equal if they refer to the same database 679 * and are logged in as the same database user using the same protocol. 680 * These are the general criteria for transactions to be compatoible so 681 * that an SQLTransaction object generated by one client can be used by 682 * the other. 683 */ 684 - (BOOL) isEqual: (id)other; 685 686 /** 687 * Return the state of the flag indicating whether the library thinks 688 * a transaction is in progress. This flag is normally maintained by 689 * -begin, -commit, and -rollback. 690 */ 691 - (BOOL) isInTransaction; 692 693 /** 694 * Returns the date/time stamp of the last database operation performed 695 * by the receiver, or nil if no operation has ever been done by it.<br /> 696 * Simply connecting to or disconnecting from the databsse does not 697 * count as an operation. 698 */ 699 - (NSDate*) lastOperation; 700 701 /** Compares the receiver with the other client to see which one has been 702 * inactive but connected for longest (if they are connected) and returns 703 * that instance.<br /> 704 * If neither is idle but connected, the method returns nil.<br /> 705 * In a tie, the method returns the other instance. 706 */ 707 - (SQLClient*) longestIdle: (SQLClient*)other; 708 709 /** 710 * Return the database reference name for this instance (or nil). 711 */ 712 - (NSString*) name; 713 714 /** 715 * Return the database password for this instance (or nil). 716 */ 717 - (NSString*) password; 718 719 /** 720 * <p>Perform arbitrary query <em>which returns values.</em> 721 * </p> 722 * <p>This method handles its arguments in the same way as the -buildQuery:,... 723 * method and returns the result of the query. 724 * </p> 725 * <example> 726 * result = [db query: @"SELECT Name FROM ", table, nil]; 727 * </example> 728 * <p>Upon error, an exception is raised. 729 * </p> 730 * <p>The query returns an array of records (each of which is represented 731 * by an SQLRecord object). 732 * </p> 733 * <p>Each SQLRecord object contains one or more fields, in the order in 734 * which they occurred in the query. Fields may also be retrieved by name. 735 * </p> 736 * <p>NULL field items are returned as NSNull objects. 737 * </p> 738 * <p>Most other field items are returned as NSString objects. 739 * </p> 740 * <p>Date and timestamp field items are returned as NSDate objects. 741 * </p> 742 */ 743 - (NSMutableArray*) query: (NSString*)stmt,...; 744 745 /** 746 * Takes the query statement and substitutes in values from 747 * the dictionary (in the same manner as the -buildQuery:with: method) 748 * then executes the query and returns the response.<br /> 749 * <example> 750 * result = [db query: @"SELECT Name FROM {Table} WHERE ID = {ID}" 751 * with: values]; 752 * </example> 753 * Any non-string values in the dictionary will be replaced by 754 * the results of the -quote: method.<br /> 755 * The markup format may also be {key?default} where <em>default</em> 756 * is a string to be used if there is no value for the <em>key</em> 757 * in the dictionary. 758 */ 759 - (NSMutableArray*) query: (NSString*)stmt with: (NSDictionary*)values; 760 761 /** 762 * Convert an object to a string suitable for use in an SQL query.<br /> 763 * Normally the -execute:,..., and -query:,... methods will call this 764 * method automatically for everything apart from string objects.<br /> 765 * Strings have to be handled specially, because they are used both for 766 * parts of the SQL command, and as values (where they need to be quoted). 767 * So where you need to pass a string value which needs quoting, 768 * you must call this method explicitly.<br /> 769 * Subclasses may override this method to provide appropriate quoting for 770 * types of object which need database backend specific quoting conventions. 771 * However, the defalt implementation should be OK for most cases.<br /> 772 * This method makes use of -quoteString: to quote literal strings.<br /> 773 * The base class implementation formats NSDate objects as<br /> 774 * YYYY-MM-DD hh:mm:ss.mmm ?ZZZZ<br /> 775 * NSData objects are not quoted ... they must not appear in queries, and 776 * where used for insert/update operations, they need to be passed to the 777 * -backendExecute: method unchanged. 778 */ 779 - (NSString*) quote: (id)obj; 780 781 /** 782 * Produce a quoted string from the supplied arguments (printf style). 783 */ 784 - (NSString*) quotef: (NSString*)fmt, ...; 785 786 /** 787 * Convert a big (64 bit) integer to a string suitable for use in an SQL query. 788 */ 789 - (NSString*) quoteBigInteger: (int64_t)i; 790 791 /** 792 * Convert a 'C' string to a string suitable for use in an SQL query 793 * by using -quoteString: to convert it to a literal string format.<br /> 794 * NB. a null pointer is treated as an empty string. 795 */ 796 - (NSString*) quoteCString: (const char *)s; 797 798 /** 799 * Convert a single character to a string suitable for use in an SQL query 800 * by using -quoteString: to convert it to a literal string format.<br /> 801 * NB. a nul character is not allowed and will cause an exception. 802 */ 803 - (NSString*) quoteChar: (char)c; 804 805 /** 806 * Convert a float to a string suitable for use in an SQL query. 807 */ 808 - (NSString*) quoteFloat: (float)f; 809 810 /** 811 * Convert an integer to a string suitable for use in an SQL query. 812 */ 813 - (NSString*) quoteInteger: (int)i; 814 815 /** 816 * Convert a string to a form suitable for use as a string 817 * literal in an SQL query.<br /> 818 * Subclasses may override this for non-standard literal string 819 * quoting conventions. 820 */ 821 - (NSString*) quoteString: (NSString *)s; 822 823 /** 824 * Revert a transaction for this database client.<br /> 825 * If there is no transaction in progress, this method does nothing.<br /> 826 * <p>NB. You must <strong>not</strong> execute an SQL statement 827 * which would commit or rollback a transaction directly ... use 828 * only this method or the -rollback method. 829 * </p> 830 * <p>Where possible, consider using the [SQLTransaction] class rather 831 * than calling -begin -commit or -rollback yourself. 832 * </p> 833 */ 834 - (void) rollback; 835 836 /** 837 * Set the database host/name for this object.<br /> 838 * This is called automatically to configure the connection ... 839 * you normally shouldn't need to call it yourself. 840 */ 841 - (void) setDatabase: (NSString*)s; 842 843 /** 844 * Set the database reference name for this object. This is used to 845 * differentiate between multiple connections to the database.<br /> 846 * This is called automatically to configure the connection ... 847 * you normally shouldn't need to call it yourself.<br /> 848 * NB. attempts to change the name of an instance to that of an existing 849 * instance are ignored. 850 */ 851 - (void) setName: (NSString*)s; 852 853 /** 854 * Set the database password for this object.<br /> 855 * This is called automatically to configure the connection ... 856 * you normally shouldn't need to call it yourself. 857 */ 858 - (void) setPassword: (NSString*)s; 859 860 /** Sets an internal flag to indicate whether leading and trailing white 861 * space characters should be removed from values retrieved from the 862 * database by the receiver. 863 */ 864 - (void) setShouldTrim: (BOOL)aFlag; 865 866 /** 867 * Set the database user for this object.<br /> 868 * This is called automatically to configure the connection ... 869 * you normally shouldn't need to call it yourself. 870 */ 871 - (void) setUser: (NSString*)s; 872 873 /** 874 * Calls -backendExecute: in a safe manner.<br /> 875 * Handles locking.<br /> 876 * Maintains -lastOperation date.<br /> 877 * Returns the result of -backendExecute: 878 */ 879 - (NSInteger) simpleExecute: (NSArray*)info; 880 881 /** 882 * Calls -simpleQuery:recordType:listType: with the default record class 883 * and default array class. 884 */ 885 - (NSMutableArray*) simpleQuery: (NSString*)stmt; 886 887 /** 888 * Calls -backendQuery:recordType:listType: in a safe manner.<br /> 889 * Handles locking.<br /> 890 * Maintains -lastOperation date.<br /> 891 * The value of rtype must respond to the 892 * [SQLRecord+newWithValues:keys:count:] method.<br /> 893 * If rtype is nil then the [SQLRecord] class is used.<br /> 894 * The value of ltype must respond to the [NSObject+alloc] method to produce 895 * a container which must repond to the [NSMutableArray-initWithCapacity:] 896 * method to initialise itsself and the [NSMutableArray-addObject:] method 897 * to add records to the list.<br /> 898 * If ltype is nil then the [NSMutableArray] class is used.<br /> 899 * This library provides a few helper classes to provide alternative 900 * values for rtype and ltype. 901 */ 902 - (NSMutableArray*) simpleQuery: (NSString*)stmt 903 recordType: (id)rtype 904 listType: (id)ltype; 905 906 /** 907 * Return the database user for this instance (or nil). 908 */ 909 - (NSString*) user; 910 @end 911 912 /** 913 * This category contains the methods which a subclass <em>must</em> 914 * override to provide a working instance, and helper methods for the 915 * backend implementations.<br /> 916 * Application programmers should <em>not</em> call the backend 917 * methods directly.<br /> 918 * <p>When subclassing to produce a backend driver bundle, please be 919 * aware that the subclass must <em>NOT</em> introduce additional 920 * instance variables. Instead the <em>extra</em> instance variable 921 * is provided for use as a pointer to subclass specific data. 922 * </p> 923 */ 924 @interface SQLClient(Subclass) 925 926 /** <override-subclass /> 927 * Attempts to establish a connection to the database server.<br /> 928 * Returns a flag to indicate whether the connection has been established.<br /> 929 * If a connection was already established, returns YES and does nothing.<br /> 930 * You should not need to use this method normally, as it is called for you 931 * automatically when necessary.<br /> 932 * <p>Subclasses <strong>must</strong> implement this method to establish a 933 * connection to the database server process (and initialise the 934 * <em>extra</em> instance variable if necessary), setting the 935 * <em>connected</em> instance variable to indicate the state of the object. 936 * </p> 937 * <p>This method must call +purgeConnections: to ensure that there is a 938 * free slot for the new connection. 939 * </p> 940 * <p>Application code must <em>not</em> call this method directly, it is 941 * for internal use only. The -connect method calls this method if the 942 * <em>connected</em> instance variable is NO. 943 * </p> 944 */ 945 - (BOOL) backendConnect; 946 947 /** <override-subclass /> 948 * Disconnect from the database unless already disconnected.<br /> 949 * <p>This method is called automatically when the receiver is deallocated 950 * or reconfigured, and may also be called automatically when there are 951 * too many database connections active. 952 * </p> 953 * <p>If the receiver is an instance of a subclass which uses the 954 * <em>extra</em> instance variable, it <strong>must</strong> clear that 955 * variable in the -backendDisconnect method, because a reconfiguration 956 * may cause the class of the receiver to change. 957 * </p> 958 * <p>This method must set the <em>connected</em> instance variable to NO. 959 * </p> 960 * <p>Application code must <em>not</em> call this method directly, it is 961 * for internal use only. The -disconnect method calls this method if the 962 * <em>connected</em> instance variable is YES. 963 * </p> 964 */ 965 - (void) backendDisconnect; 966 967 /** <override-subclass /> 968 * Perform arbitrary operation <em>which does not return any value.</em><br /> 969 * This method has a single argument, an array containing the string 970 * representing the statement to be executed as its first object, and an 971 * optional sequence of data objects following it.<br /> 972 * <example> 973 * [db backendExecute: [NSArray arrayWithObject: 974 * @"UPDATE MyTable SET Name = 'The name' WHERE ID = 123"]]; 975 * </example> 976 * <p>The backend implementation is required to perform the SQL statement 977 * using the supplied NSData objects at the points in the statement 978 * marked by the <code>'?'''?'</code> sequence. The marker saequences are 979 * inserted into the statement at an earlier stage by the -execute:,... 980 * and -execute:with: methods. 981 * </p> 982 * <p>Callers should lock the instance using the <em>lock</em> 983 * instance variable for the duration of the operation, and unlock 984 * it afterwards. 985 * </p> 986 * <p>NB. callers (other than the -begin, -commit, and -rollback methods) 987 * should not pass any statement to this method which would cause a 988 * transaction to begin or end. 989 * </p> 990 * <p>Application code must <em>not</em> call this method directly, it is 991 * for internal use only. 992 * </p> 993 * <p>Where the database backend support it, this method returns the count of 994 * the number of rows to which the operation applied. Otherwise this 995 * returns -1. 996 * </p> 997 */ 998 - (NSInteger) backendExecute: (NSArray*)info; 999 1000 /** <override-subclass /> 1001 * <p>Perform arbitrary query <em>which returns values.</em> 1002 * </p> 1003 * <example> 1004 * result = [db backendQuery: @"SELECT Name FROM Table" 1005 * recordType: [SQLRecord class]] 1006 * listType: [NSMutableArray class]]; 1007 * </example> 1008 * <p>Upon error, an exception is raised. 1009 * </p> 1010 * <p>The query returns an array of records (each of which is represented 1011 * by an SQLRecord object). 1012 * </p> 1013 * <p>Each SQLRecord object contains one or more fields, in the order in 1014 * which they occurred in the query. Fields may also be retrieved by name. 1015 * </p> 1016 * <p>NULL field items are returned as NSNull objects. 1017 * </p> 1018 * <p>Callers should lock the instance using the <em>lock</em> 1019 * instance variable for the duration of the operation, and unlock 1020 * it afterwards. 1021 * </p> 1022 * <p>Application code must <em>not</em> call this method directly, it is 1023 * for internal use only. 1024 * </p> 1025 * <p>The rtype argument specifies an object to be used to 1026 * create the records produced by the query.<br /> 1027 * This is provided as a performance optimisation when you want to store 1028 * data directly into a special class of your own.<br /> 1029 * The object must respond to the [SQLRecord +newWithValues:keys:count:] 1030 * method to produce a new record initialised with the supplied data. 1031 * </p> 1032 * <p>The ltype argument specifies an object to be used to create objects to 1033 * store the records produced by the query.<br /> 1034 * The should be a subclass of NSMutableArray. It must at least 1035 * implement the [NSObject+alloc] method to create an instnce to store 1036 * records. The instance must implement [NSMutableArray-initWithCapacity:] 1037 * to initialise itsself and [NSMutableArray-addObject:] to allow the 1038 * backend to add records to it.<br /> 1039 * For caching to work, it must be possible to make a mutable copy of the 1040 * instance using the mutableCopy method. 1041 * </p> 1042 */ 1043 - (NSMutableArray*) backendQuery: (NSString*)stmt 1044 recordType: (id)rtype 1045 listType: (id)ltype; 1046 1047 /** 1048 * Calls -backendQuery:recordType:listType: with the default record class 1049 * and array class. 1050 */ 1051 - (NSMutableArray*) backendQuery: (NSString*)stmt; 1052 1053 /** <override-subclass /> 1054 * Called to enable asynchronous notification of database events using the 1055 * specified name (which must be a valid identifier consisting of ascii 1056 * letters, digits, and underscore characters, starting with a letter). 1057 * Names are not case sensitive (so AAA is the same as aaa).<br /> 1058 * Repeated calls to list on the same name should be treated as a single 1059 * call.<br /> 1060 * The backend is responsible for implicitly unlistening when a connection 1061 * is closed.<br /> 1062 * There is a default implementation which does nothing ... for backends 1063 * which don't support asynchronous notifications.<br /> 1064 * If a backend <em>does</em> support asynchronous notifications, 1065 * it should do so by posting NSNotification instances to 1066 * [NSNotificationCenter defaultCenter] using the SQLClient instance as 1067 * the notification object and supplying any payload as a string using 1068 * the 'Payload' key in the NSNotification userInfo dictionary. 1069 * The userInfo dictionary should also contain a boolean (NSNumber) value, 1070 * using the 'Local' key, to indicate whether the notification was sent by 1071 * the current SQLClient instance or by some other client/ 1072 */ 1073 - (void) backendListen: (NSString*)name; 1074 1075 /** <override-subclass /> 1076 * The backend should implement this to send asynchronous notifications 1077 * to anything listening for them. The name of the notification is an 1078 * SQL identifier used for listening for the asynchronous data.<br /> 1079 * The payload string may be nil if no additional information is 1080 * needed in the notification. 1081 */ 1082 - (void) backendNotify: (NSString*)name payload: (NSString*)more; 1083 1084 /** <override-subclass /> 1085 * Called to disable asynchronous notification of database events using the 1086 * specified name. This has no effect if the name has not been used in an 1087 * earlier call to -backendListen:, or if the name has already been 1088 * unlistened since the last call to listen. on it.<br /> 1089 * There is a default implementation which does nothing ... for backends 1090 * which don't support asynchronous notifications. 1091 */ 1092 - (void) backendUnlisten: (NSString*)name; 1093 1094 /** <override-subclass /> 1095 * This method is <em>only</em> for the use of the 1096 * -insertBLOBs:intoStatement:length:withMarker:length:giving: 1097 * method.<br /> 1098 * Subclasses which need to insert binary data into a statement 1099 * must implement this method to copy the escaped data into place 1100 * and return the number of bytes actually copied. 1101 */ 1102 - (unsigned) copyEscapedBLOB: (NSData*)blob into: (void*)buf; 1103 1104 /** 1105 * <p>This method is a convenience method provided for subclasses which need 1106 * to insert escaped binary data into an SQL statement before sending the 1107 * statement to a backend server process. This method makes use of the 1108 * -copyEscapedBLOB:into: and -lengthOfEscapedBLOB: methods, which 1109 * <em>must</em> be implemented by the subclass. 1110 * </p> 1111 * <p>The blobs array is an array containing the original SQL statement 1112 * string (unused by this method) followed by the data items to be inserted. 1113 * </p> 1114 * <p>The statement and sLength arguments specify the datastream to be 1115 * copied and into which the BLOBs are to be inserted. 1116 * </p> 1117 * <p>The marker and mLength arguments specify the sequence of marker bytes 1118 * in the statement which indicate a position for insertion of an escaped BLOB. 1119 * </p> 1120 * <p>The method returns either the original statement or a copy containing 1121 * the escaped BLOBs. The length of the returned data is stored in result. 1122 * </p> 1123 */ 1124 - (const void*) insertBLOBs: (NSArray*)blobs 1125 intoStatement: (const void*)statement 1126 length: (unsigned)sLength 1127 withMarker: (const void*)marker 1128 length: (unsigned)mLength 1129 giving: (unsigned*)result; 1130 1131 /** <override-subclass /> 1132 * This method is <em>only</em> for the use of the 1133 * -insertBLOBs:intoStatement:length:withMarker:length:giving: 1134 * method.<br /> 1135 * Subclasses which need to insert binary data into a statement 1136 * must implement this method to return the length of the escaped 1137 * bytestream which will be inserted. 1138 */ 1139 - (unsigned) lengthOfEscapedBLOB: (NSData*)blob; 1140 1141 @end 1142 1143 /** 1144 * This category contains methods for asynchronous notification of 1145 * events via the database (for those database backends which support 1146 * it: currently only PostgreSQL). 1147 */ 1148 @interface SQLClient (Notifications) 1149 /** Adds anObserver to receive notifications when the backend database 1150 * server sends an asynchronous event identified by the specified name 1151 * (which must be a valid database identifier).<br /> 1152 * When a notification (NSNotification instance) is received by the method 1153 * specified by aSelector, its <em>object</em> will be the SQLClient 1154 * instance to which anObserver was added and its userInfo dictionary 1155 * will contain the key 'Local' and possibly the key 'Payload'.<br /> 1156 * If the 'Local' value is the boolean YES, the notification originated 1157 * as an action by this SQLClient instance.<br /> 1158 * If the 'Payload' value is not nil, then it is a string providing extra 1159 * information about the notification.<br /> 1160 * NB. At the point when the observer is notified about an event the 1161 * database client object will be locked and may not be used to query 1162 * or modify the database (typically a database query will already be 1163 * in progress). The method handling the notification must therefore 1164 * handle any database operations in a later timeout. 1165 */ 1166 - (void) addObserver: (id)anObserver 1167 selector: (SEL)aSelector 1168 name: (NSString*)name; 1169 1170 /** Posts a notification via the database. The name is an SQL identifier 1171 * (for which observers may have registered) and the extra payload 1172 * information may be nil if not required. 1173 */ 1174 - (void) postNotificationName: (NSString*)name payload: (NSString*)more; 1175 1176 /** Removes anObserver as an observer for asynchronous notifications from 1177 * the database server. If name is omitted, the observer will be removed 1178 * for all names. 1179 */ 1180 - (void) removeObserver: (id)anObserver name: (NSString*)name; 1181 @end 1182 1183 /** 1184 * This category contains convenience methods including those for 1185 * frequently performed database operations ... message logging etc. 1186 */ 1187 @interface SQLClient (Convenience) 1188 1189 /** 1190 * Returns a transaction object configured to handle batching and 1191 * execute part of a batch of statements if execution of the whole 1192 * using the [SQLTransaction-executeBatch] method fails.<br /> 1193 * If stopOnFailure is YES than execution of the transaction will 1194 * stop with the first statement to fail, otherwise it will execute 1195 * all the statements it can, skipping any failed statements. 1196 */ 1197 - (SQLTransaction*) batch: (BOOL)stopOnFailure; 1198 1199 /** 1200 * Convenience method to deal with the results of a query converting the 1201 * normal array of records into an array of record columns. Each column 1202 * in the array is an array containing all the values from that column. 1203 */ 1204 - (NSMutableArray*) columns: (NSMutableArray*)records; 1205 1206 /** 1207 * Executes a query (like the -query:,... method) and checks the result 1208 * (raising an exception if the query did not contain a single record) 1209 * and returns the resulting record. 1210 */ 1211 - (SQLRecord*) queryRecord: (NSString*)stmt,...; 1212 1213 /** 1214 * Executes a query (like the -query:,... method) and checks the result.<br /> 1215 * Raises an exception if the query did not contain a single record, or 1216 * if the record did not contain a single field.<br /> 1217 * Returns the resulting field as a <em>string</em>. 1218 */ 1219 - (NSString*) queryString: (NSString*)stmt,...; 1220 1221 /** 1222 * Convenience method to deal with the results of a query where each 1223 * record contains a single field ... it converts the array of records 1224 * returned by the query to an array containing the fields.<br /> 1225 * NB. This does not check that the contents of the records array are 1226 * actually instances of [SQLRecord], so you must ensure you don't 1227 * call it more than once on the same array (something that may happen 1228 * if you retrieve the array using a cache based query). 1229 */ 1230 - (void) singletons: (NSMutableArray*)records; 1231 1232 /** 1233 * Creates and returns an autoreleased SQLTransaction instance which will 1234 * use the receiver as the database connection to perform transactions. 1235 */ 1236 - (SQLTransaction*) transaction; 1237 1238 @end 1239 1240 1241 1242 /** 1243 * This category porovides basic methods for logging debug information. 1244 */ 1245 @interface SQLClient (Logging) 1246 /** 1247 * Return the class-wide debugging level, which is inherited by all 1248 * newly created instances. 1249 */ 1250 + (unsigned int) debugging; 1251 1252 /** 1253 * Return the class-wide duration logging threshold, which is inherited by all 1254 * newly created instances. 1255 */ 1256 + (NSTimeInterval) durationLogging; 1257 1258 /** 1259 * Set the debugging level to be inherited by all new instances.<br /> 1260 * See [SQLClient(Logging)-setDebugging:] 1261 * for controlling an individual instance of the class. 1262 */ 1263 + (void) setDebugging: (unsigned int)level; 1264 1265 /** 1266 * Set the duration logging threshold to be inherited by new instances.<br /> 1267 * See [SQLClient(Logging)-setDurationLogging:] 1268 * for controlling an individual instance of the class. 1269 */ 1270 + (void) setDurationLogging: (NSTimeInterval)threshold; 1271 1272 /** 1273 * The default implementation calls NSLogv to log a debug message.<br /> 1274 * Override this in a category to provide more sophisticated logging.<br /> 1275 * Do NOT override with code which can be slow or which calls (directly 1276 * or indirectly) any SQLCLient methods, since this method will be used 1277 * inside locked regions of the SQLClient code and you could cause 1278 * deadlocks or long delays to other threads using the class. 1279 */ 1280 - (void) debug: (NSString*)fmt, ...; 1281 1282 /** 1283 * Return the current debugging level.<br /> 1284 * A level of zero (default) means that no debug output is produced, 1285 * except for that concerned with logging the database transactions 1286 * taking over a certain amount of time (see the -setDurationLogging: method). 1287 */ 1288 - (unsigned int) debugging; 1289 1290 /** 1291 * Returns the threshold above which queries and statements taking a long 1292 * time to execute are logged. A negative value (default) indicates that 1293 * this logging is disabled. A value of zero means that all statements 1294 * are logged. 1295 */ 1296 - (NSTimeInterval) durationLogging; 1297 1298 /** 1299 * Set the debugging level of this instance ... overrides the default 1300 * level inherited from the class. 1301 */ 1302 - (void) setDebugging: (unsigned int)level; 1303 1304 /** 1305 * Set a threshold above which queries and statements taking a long 1306 * time to execute are logged. A negative value (default) disables 1307 * this logging. A value of zero logs all statements. 1308 */ 1309 - (void) setDurationLogging: (NSTimeInterval)threshold; 1310 @end 1311 1312 /** 1313 * This category provides methods for caching the results of queries 1314 * in order to reduce the number of client-server trips and the database 1315 * load produced by an application which needs update its information 1316 * from the database frequently. 1317 */ 1318 @interface SQLClient (Caching) 1319 1320 /** 1321 * Returns the cache used by the receiver for storing the results of 1322 * requests made through it. Creates a new cache if necessary. 1323 */ 1324 - (GSCache*) cache; 1325 1326 /** 1327 * Calls -cache:simpleQuery:recordType:listType: with the default 1328 * record class, array class, and with a query string formed from 1329 * stmt and the following values (if any). 1330 */ 1331 - (NSMutableArray*) cache: (int)seconds 1332 query: (NSString*)stmt,...; 1333 1334 /** 1335 * Calls -cache:simpleQuery:recordType:listType: with the default 1336 * record class array class and with a query string formed from stmt 1337 * and values. 1338 */ 1339 - (NSMutableArray*) cache: (int)seconds 1340 query: (NSString*)stmt 1341 with: (NSDictionary*)values; 1342 1343 /** 1344 * Calls -cache:simpleQuery:recordType:listType: with the default 1345 * record class and array class. 1346 */ 1347 - (NSMutableArray*) cache: (int)seconds simpleQuery: (NSString*)stmt; 1348 1349 /** 1350 * If the result of the query is already cached and has not expired, 1351 * return it. Otherwise, perform the query and cache the result 1352 * giving it the specified lifetime in seconds.<br /> 1353 * If seconds is negative, the query is performed irrespective of 1354 * whether it is already cached, and its absolute value is used to 1355 * set the lifetime of the results.<br /> 1356 * If seconds is zero, the cache for this query is emptied.<br /> 1357 * Handles locking.<br /> 1358 * Maintains -lastOperation date.<br /> 1359 * The value of rtype must respond to the 1360 * [SQLRecord+newWithValues:keys:count:] method.<br /> 1361 * If rtype is nil then the [SQLRecord] class is used.<br /> 1362 * The value of ltype must respond to the [NSObject+alloc] method to produce 1363 * a container which must repond to the [NSMutableArray-initWithCapacity:] 1364 * method to initialise itsself and the [NSMutableArray-addObject:] method 1365 * to add records to the list.<br /> 1366 * If ltype is nil then the [NSMutableArray] class is used.<br /> 1367 * The list produced by this argument is used as the return value of 1368 * this method.<br /> 1369 * If a cache thread has been set using the -setCacheThread: method, and the 1370 * -cache:simpleQuery:recordType:listType: method is called from a 1371 * thread other than the cache thread, then any query to retrieve 1372 * uncached data will be performed in the cache thread, and for cached 1373 * (but expired) data, the old (expired) results may be returned ... 1374 * in which case an asynchronous query to update the cache will be 1375 * executed as soon as possible in the cache thread. 1376 */ 1377 - (NSMutableArray*) cache: (int)seconds 1378 simpleQuery: (NSString*)stmt 1379 recordType: (id)rtype 1380 listType: (id)ltype; 1381 1382 /** 1383 * Sets the cache to be used by the receiver for storing the results of 1384 * requests made through it.<br /> 1385 * If aCache is nil, the current cache is released, and a new cache will 1386 * be automatically created as soon as there is a need to cache anything. 1387 */ 1388 - (void) setCache: (GSCache*)aCache; 1389 1390 /** Sets the thread to be used to retrieve data to populate the cache.<br /> 1391 * All cached queries will be performed in this thread (if non-nil).<br /> 1392 * The setting of a thread for the cache also implies that expired items in 1393 * the cache may not be removed when they are queried from another thread, 1394 * rather they can be kept (if they are not <em>too</em> old) and an 1395 * asynchronous query to update them will be run on the cache thread.<br /> 1396 * The rule is that, if the item's age is more than twice its nominal 1397 * lifetime, it will be retrieved immediately, otherwise it will be 1398 * retrieved asynchronously.<br /> 1399 * Currently this may only be the main thread or nil. Any attempt to set 1400 * another thread will use the main thread instead. 1401 */ 1402 - (void) setCacheThread: (NSThread*)aThread; 1403 @end 1404 1405 /** <p>An SQLClientPool instance may be used to create/control a pool of 1406 * client objects. Code may obtain autoreleased proxies to the clients 1407 * from the pool and use them safe in the knowledge that they won't be 1408 * used anywhere else ... as soon as the client would be deallocated, it 1409 * is returned to the pool. 1410 * </p> 1411 * <p>All clients in the pool share the same cache object, so query results 1412 * cached by one client will be available to other clients in the pool. 1413 * </p> 1414 */ 1415 @interface SQLClientPool : NSObject 1416 { 1417 NSConditionLock *lock; /** Controls access to the pool contents */ 1418 SQLClient **c; /** The clients of the pool. */ 1419 BOOL *u; /** Whether the client is in use. */ 1420 int max; /** Maximum connection count */ 1421 int min; /** Minimum connection count */ 1422 NSDictionary *_config; /** The pool configuration object */ 1423 NSString *_name; /** The pool configuration name */ 1424 NSTimeInterval _duration; /** Duration logging threshold */ 1425 unsigned int _debugging; /** The current debugging level */ 1426 uint64_t _immediate; /** Immediate client provisions */ 1427 uint64_t _delayed; /** Count of delayed provisions */ 1428 uint64_t _failed; /** Count of timed out provisions */ 1429 NSTimeInterval _longest; /** Count of longest delay */ 1430 NSTimeInterval _delayWaits; /** Time waiting for provisions */ 1431 NSTimeInterval _failWaits; /** Time waiting for timewouts */ 1432 } 1433 1434 /** Returns the count of currently available connections in the pool. 1435 */ 1436 - (int) availableConnections; 1437 1438 /** 1439 * Creates a pool of clients using a single client configuration.<br /> 1440 * Calls -initWithConfiguration:name:pool: (passing NO to say the client 1441 * is not in a pool) to create each client.<br /> 1442 * The value of maxConnections is the size of the pool (ie the number of 1443 * clients created) and thus the maximum number of concurrent connections 1444 * to the database server.<br /> 1445 * The value of minConnections is the minimum number of connected clients 1446 * normally expected to be in the pool. The pool tries to ensure that it 1447 * doesn't contain more than this number of idle connected clients. 1448 */ 1449 - (id) initWithConfiguration: (NSDictionary*)config 1450 name: (NSString*)reference 1451 max: (int)maxConnections 1452 min: (int)minConnections; 1453 1454 /** Return the maximum number of database connections in the pool. 1455 */ 1456 - (int) maxConnections; 1457 1458 /** Return the minimum number of database connections in the pool. 1459 */ 1460 - (int) minConnections; 1461 1462 /** Fetches an (autoreleased) client from the pool.<br /> 1463 * This method blocks indefinitely waiting for a client to become 1464 * available in the pool. 1465 */ 1466 - (SQLClient*) provideClient; 1467 1468 /** Fetches an (autoreleased) client from the pool.<br /> 1469 * If no client is or becomes available before the specified date then 1470 * the method returns nil.<br /> 1471 * If when is nil then a date in the distant future is used so that 1472 * the method will effectively wait forever to get a client. 1473 */ 1474 - (SQLClient*) provideClientBeforeDate: (NSDate*)when; 1475 1476 /** 1477 * Sets the cache for all the clients in the pool. 1478 */ 1479 - (void) setCache: (GSCache*)aCache; 1480 1481 /** 1482 * Sets the cache thread for all the clients in the pool. 1483 */ 1484 - (void) setCacheThread: (NSThread*)aThread; 1485 1486 /** Set the debugging level for all clients in the pool. 1487 */ 1488 - (void) setDebugging: (unsigned int)level; 1489 1490 /** Set the duration logging threshold for all clients in the pool. 1491 */ 1492 - (void) setDurationLogging: (NSTimeInterval)threshold; 1493 1494 /** Sets the pool size limits (number of connections we try to maintain).<br /> 1495 * The value of maxConnections is the size of the pool (ie the number of 1496 * clients created) and thus the maximum number of concurrent connections 1497 * to the database server.<br /> 1498 * The value of minConnections is the minimum number of connected clients 1499 * normally expected to be in the pool. The pool tries to ensure that it 1500 * doesn't contain more than this number of idle connected clients.<br /> 1501 * The value of maxConnections must be greater than or equal to that of 1502 * minConnections and may not be greater than 100. 1503 * The value of minConnections must be less than or equal to that of 1504 * maxConnections and may not be less than 1. 1505 */ 1506 - (void) setMax: (int)maxConnections min: (int)minConnections; 1507 1508 /** Returns a string describing the usage of the pool. 1509 */ 1510 - (NSString*) statistics; 1511 1512 /** Puts the client back in the pool. This happens automatically 1513 * when a client from a pool would normally be deallocated so you don't 1514 * generally need to do it.<br /> 1515 * Returns YES if the supplied client was from the pool, NO otherwise.<br /> 1516 * If the swallowed client would take the count of idle client connections 1517 * in the pool above the configured minimum, the oldest (ie longest idle) 1518 * client in the pool is disconnected. 1519 */ 1520 - (BOOL) swallowClient: (SQLClient*)client; 1521 1522 @end 1523 1524 /** 1525 * The SQLTransaction transaction class provides a convenient mechanism 1526 * for grouping together a series of SQL statements to be executed as a 1527 * single transaction. It avoids the need for handling begin/commit, 1528 * and should be as efficient as reasonably possible.<br /> 1529 * You obtain an instance by calling [SQLClient-transaction], add SQL 1530 * statements to it using the -add:,... and/or -add:with: methods, and 1531 * then use the -execute method to perform all the statements as a 1532 * single operation.<br /> 1533 * Any exception is caught and re-raised in the -execute method after any 1534 * tidying up to leave the database in a consistent state.<br /> 1535 * NB. This class is not in itsself thread-safe, though the underlying 1536 * database operations should be. If you have multiple threads, you 1537 * should create multiple SQLTransaction instances, at least one per thread. 1538 */ 1539 @interface SQLTransaction : NSObject <NSCopying> 1540 { 1541 SQLCLIENT_PRIVATE 1542 SQLClient *_db; 1543 NSMutableArray *_info; 1544 unsigned _count; 1545 BOOL _batch; 1546 BOOL _stop; 1547 uint8_t _merge; 1548 } 1549 1550 /** 1551 * Adds an SQL statement to the transaction. This is similar to 1552 * [SQLClient-execute:,...] but does not cause any database operation 1553 * until -execute is called, so it will not raise a database exception. 1554 */ 1555 - (void) add: (NSString*)stmt,...; 1556 1557 /** 1558 * Adds an SQL statement to the transaction. This is similar to 1559 * [SQLClient-execute:with:] but does not cause any database operation 1560 * until -execute is called, so it will not raise a database exception. 1561 */ 1562 - (void) add: (NSString*)stmt with: (NSDictionary*)values; 1563 1564 /** 1565 * Appends a copy of the other transaction to the receiver.<br /> 1566 * This provides a convenient way of merging transactions which have been 1567 * built by different code modules, in order to have them all executed 1568 * together in a single operation (for efficiency etc).<br /> 1569 * This does not alter the other transaction, so if the execution of 1570 * a group of merged transactions fails, it is then possible to attempt 1571 * to commit the individual transactions separately.<br /> 1572 * NB. All transactions appended must be using the same database 1573 * connection (SQLClient instance). 1574 */ 1575 - (void) append: (SQLTransaction*)other; 1576 1577 /** 1578 * Make a copy of the receiver. 1579 */ 1580 - (id) copyWithZone: (NSZone*)z; 1581 1582 /** 1583 * Returns the number of individual statements and/or subsidiary transactions 1584 * which have been added to the receiver. For a count of the total number 1585 * of statements, use the -totalCount method. 1586 */ 1587 - (NSUInteger) count; 1588 1589 /** 1590 * Returns the database client with which this instance operates.<br /> 1591 * This client is retained by the transaction. 1592 */ 1593 - (SQLClient*) db; 1594 1595 /** 1596 * <p>Performs any statements added to the transaction as a single operation. 1597 * If any problem occurs, an NSException is raised, but the database connection 1598 * is left in a consistent state and a partially completed operation is 1599 * rolled back. 1600 * </p> 1601 * <p>NB. If the database is not already in a transaction, this implicitly 1602 * calls the -begin method to start the transaction before executing the 1603 * statements.<br /> 1604 * The method always commits the transaction, even if the transaction was 1605 * begun earlier rather than in -execute.<br /> 1606 * This behavior allows you to call [SQLClient-begin], then run one or more 1607 * queries, build up a transaction based upon the query results, and then 1608 * -execute that transaction, causing the entire process to be commited as 1609 * a single transaction . 1610 * </p> 1611 */ 1612 - (void) execute; 1613 1614 /** Convenience method which calls 1615 * -executeBatchReturningFailures:logExceptions: with 1616 * a nil failures argument and exception logging off. 1617 */ 1618 - (unsigned) executeBatch; 1619 1620 /** 1621 * <p>This is similar to the -execute method, but may allow partial 1622 * execution of the transaction if appropriate: 1623 * </p> 1624 * <p>If the transaction was created using the [SQLClient-batch:] method and 1625 * the transaction as a whole fails, individual statements are retried.<br /> 1626 * The stopOnFailure flag for the batch creation indicates whether the 1627 * retries are stopped at the first statement to fail, or continue (skipping 1628 * any failed statements). 1629 * </p> 1630 * <p>If the transaction has had transactions appended to it, those 1631 * subsidiary transactions may succeed or fail atomically depending 1632 * on their individual attributes. 1633 * </p> 1634 * <p>If the transaction was not created using [SQLClient-batch:], then 1635 * calling this method is equivalent to calling the -execute method. 1636 * </p> 1637 * <p>If any statements/transactions in the batch fail, they are added to 1638 * the transaction supplied in the failures parameter (if it's not nil) 1639 * so that you can retry them later.<br /> 1640 * NB. statements/transactions which are not executed at all (because the 1641 * batch is set to stop on the first failure) are <em>also</em> added to 1642 * the failures transaction. 1643 * </p> 1644 * <p>If the log argument is YES, then any exceptions encountered when 1645 * executing the batch are logged using the [SQLClient-debug:,...] method, 1646 * even if debug logging is not enabled with [SQLClient-setDebugging:]. 1647 * </p> 1648 * The method returns the number of statements which actually succeeded. 1649 */ 1650 - (unsigned) executeBatchReturningFailures: (SQLTransaction*)failures 1651 logExceptions: (BOOL)log; 1652 1653 /** 1654 * Insert trn at the index'th position in the receiver.<br /> 1655 * The transaction trn must be non-empty and must use the same 1656 * database client as the receiver. 1657 */ 1658 - (void) insertTransaction: (SQLTransaction*)trn atIndex: (unsigned)index; 1659 1660 /** Remove the index'th transaction or statement from the receiver. 1661 */ 1662 - (void) removeTransactionAtIndex: (unsigned)index; 1663 1664 /** 1665 * Resets the transaction, removing all previously added statements. 1666 * This allows the transaction object to be re-used for multiple 1667 * transactions. 1668 */ 1669 - (void) reset; 1670 1671 /** <p>Use this method to enable merging of statemements subsequently added 1672 * or appended to the receiver. The history argument specifies how many 1673 * of the most recent statements in the transaction should be checked for 1674 * merging in a new statement, with a value of zero meaning that no 1675 * merging is done.<br /> 1676 * Returns the previous setting for the transaction. 1677 * </p> 1678 * <p>You may use this feature with an insert statement of the form:<br /> 1679 * INSERT INTO table (fieldnames) VALUES (values);<br /> 1680 * For databases which support multiline inserts such that they can be 1681 * merged into something of the form: 1682 * INSERT INTO table (fieldnames) VALUES (values1),(values2),...; 1683 * </p> 1684 * <p>Or may use this with an update or delete statement of the form:<br /> 1685 * command table SET settings WHERE condition;<br /> 1686 * So that statements may be merged into:<br /> 1687 * command table SET settings WHERE (condition1) OR (condition2) OR ...; 1688 * </p> 1689 * If no opportunity for merging is found, the new statement is simply 1690 * added to the transaction.<br /> 1691 * Caveats:<br /> 1692 * 1. databases may not actually support multiline insert.<br /> 1693 * 2. Merging is done only if the statement up to the string 'VALUES' 1694 * (for insert) or 'WHERE' (for update) matches.<br /> 1695 * 3. Merging into any of the last N statements (where N is greater than 1) 1696 * may of course change the order of statements in the transaction, 1697 * so care must be taken not to use this feature where that might matter.<br /> 1698 * 4. This is a simple text match rather than sql syntactic analysis, 1699 * so it's possible to confuse the process with complex statements. 1700 */ 1701 - (uint8_t) setMerge: (uint8_t)history; 1702 1703 /** 1704 * Returns the total count of statements in this transaction including 1705 * those in any subsidiary transactions. For a count of the statements 1706 * and/or transactions directly added to the receiver, use the -count method. 1707 */ 1708 - (unsigned) totalCount; 1709 1710 /** Return an autoreleased copy of the index'th transaction or statement 1711 * added to the receiver.<br /> 1712 * Since the returned transaction contains a copy of the statement/transaction 1713 * in the receiver, you can modify it without effecting the original. 1714 */ 1715 - (SQLTransaction*) transactionAtIndex: (unsigned)index; 1716 @end 1717 1718 1719 1720 /* A helper for building a dictionary from an SQL query which returns 1721 * key-value pairs (you can subclass it to handle other records).<br /> 1722 * You create an instance of this class, and pass it as both the 1723 * record and list class arguments of the low level SQLClient query.<br /> 1724 * The query (which must return a number of records, each with two fields) 1725 * will result in a mutable dictionary being built, with dictionary keys 1726 * being the first field from each record and dictionary values being the 1727 * second field of each record.<br /> 1728 * You may use the same instance for more than one query, but a second query 1729 * will replace the content dictionary produced by the first.<br /> 1730 * If you want to handle records containing more than two values, you 1731 * must create a subclass which overrides the -newWithValues:keys:count: 1732 * method to create the record objects and add them to the content 1733 * dictionary.<br /> 1734 * See [SQLClient-simpleQuery:recordType:listType:] also.<br /> 1735 * NB. When this class is used, the query will actually return an 1736 * [NSMutableDictionary] instance rather than an [NSMutableArray] of 1737 * [SQLRecord] objects. 1738 */ 1739 @interface SQLDictionaryBuilder : NSObject 1740 { 1741 NSMutableDictionary *content; 1742 } 1743 1744 /** No need to do anything ... the object will already have been added by 1745 * the -newWithValues:keys:count: method. 1746 */ 1747 - (void) addObject: (id)anObject; 1748 1749 /** When a container is supposed to be allocated, we just return the 1750 * receiver (which will then quietly ignore -addObject: messages). 1751 */ 1752 - (id) alloc; 1753 1754 /** Returns the content dictionary for the receiver. 1755 */ 1756 - (NSMutableDictionary*) content; 1757 1758 /** Creates a new content dictionary ... this method will be called 1759 * automatically by the SQLClient object when it performs a query, 1760 * so there is no need to call it at any other time. 1761 */ 1762 - (id) initWithCapacity: (NSUInteger)capacity; 1763 1764 /** Makes a mutable copy of the content dictionary (called when a caching 1765 * query uses this helper to produce the cached collection). 1766 */ 1767 - (id) mutableCopyWithZone: (NSZone*)aZone; 1768 1769 /** This is the main workhorse of the class ... it is called once for 1770 * every record read from the database, and is responsible for adding 1771 * that record to the content dictionary. The default implementation, 1772 * instead of creating an object to hold the supplied record data, 1773 * uses the two fields from the record as a key-value pair to add to 1774 * the content dictionary, and returns nil as the record object. 1775 * It's OK to return a nil object since we ignore the -addObject: 1776 * argument. 1777 */ 1778 - (id) newWithValues: (id*)values 1779 keys: (NSString**)keys 1780 count: (unsigned int)count; 1781 @end 1782 1783 /* A helper for building a counted set from an SQL query which returns 1784 * individual values (you can subclass it to handle other records).<br /> 1785 * You create an instance of this class, and pass it as both the 1786 * record and list class arguments of the low level SQLClient query.<br /> 1787 * The query (which must return a number of records, each with one field) 1788 * will result in a counted set being built and a record of the number of 1789 * added objects being kept.<br /> 1790 * You may use the same instance for more than one query, but a second query 1791 * will replace the content set produced by the first.<br /> 1792 * If you want to handle records containing more than one value, you 1793 * must create a subclass which overrides the -newWithValues:keys:count: 1794 * method to create the record objects and add them to the content 1795 * set, and increment the counter.<br /> 1796 * See [SQLClient-simpleQuery:recordType:listType:] also.<br /> 1797 * NB. When this class is used, the query will actually return an 1798 * [NSCountedSet] instance rather than an [NSMutableArray] of 1799 * [SQLRecord] objects. 1800 */ 1801 @interface SQLSetBuilder : NSObject 1802 { 1803 NSCountedSet *content; 1804 NSUInteger added; 1805 } 1806 1807 /** Returns the number of objects actually added to the counted set. 1808 */ 1809 - (NSUInteger) added; 1810 1811 /** No need to do anything ... the object will already have been added by 1812 * the -newWithValues:keys:count: method. 1813 */ 1814 - (void) addObject: (id)anObject; 1815 1816 /** When a container is supposed to be allocated, we just return the 1817 * receiver (which will then quietly ignore -addObject: messages). 1818 */ 1819 - (id) alloc; 1820 1821 /** Returns the counted set for the receiver. 1822 */ 1823 - (NSCountedSet*) content; 1824 1825 /** Creates a new content set ... this method will be called 1826 * automatically by the SQLClient object when it performs a query, 1827 * so there is no need to call it at any other time. 1828 */ 1829 - (id) initWithCapacity: (NSUInteger)capacity; 1830 1831 /** Makes a mutable copy of the content dictionary (called when a caching 1832 * query uses this helper to produce the cached collection). 1833 */ 1834 - (id) mutableCopyWithZone: (NSZone*)aZone; 1835 1836 /** This is the main workhorse of the class ... it is called once for 1837 * every record read from the database, and is responsible for adding 1838 * that record to the content set. The default implementation, 1839 * instead of creating an object to hold the supplied record data, 1840 * uses the singe field from the record to add to 1841 * the content set, and returns nil as the record object. 1842 * It's OK to return a nil object since we ignore the -addObject: 1843 * argument. 1844 */ 1845 - (id) newWithValues: (id*)values 1846 keys: (NSString**)keys 1847 count: (unsigned int)count; 1848 @end 1849 1850 /* A helper for building a collection of singletons from an SQL query 1851 * which returns singleton values.<br /> 1852 * You create an instance of this class, and pass it as the record 1853 * class argument of the low level SQLClient query.<br /> 1854 * The query (which must return a number of records, each with one field) 1855 * will result in the singleton values being stored in the list class.<br /> 1856 * See [SQLClient-simpleQuery:recordType:listType:] also. 1857 */ 1858 @interface SQLSingletonBuilder : NSObject 1859 - (id) newWithValues: (id*)values 1860 keys: (NSString**)keys 1861 count: (unsigned int)count; 1862 @end 1863 1864 #endif 1865 1866