adding SQLite helper/wrapper

1 files changed, 713 insertions, 0 deletions

diff --git a/Foundation/GTMSQLite.h b/Foundation/GTMSQLite.h
new file mode 100644
index 0000000..0697d5a
--- /dev/null
+++ b/Foundation/GTMSQLite.h
@@ -0,0 +1,713 @@
+//
+//  GTMSQLite.h
+//
+//  Copyright 2006-2008 Google Inc.
+//
+//  Licensed under the Apache License, Version 2.0 (the "License"); you may not
+//  use this file except in compliance with the License.  You may obtain a copy
+//  of the License at
+//
+//  http://www.apache.org/licenses/LICENSE-2.0
+//
+//  Unless required by applicable law or agreed to in writing, software
+//  distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+//  WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.  See the
+//  License for the specific language governing permissions and limitations under
+//  the License.
+//
+//
+//  This class is a convenience wrapper for SQLite storage with
+//  release/retain semantics. In its most basic form, that is all this
+//  class offers. You have the option of activating "CFAdditions" on
+//  init which patches or overrides the following SQLite functionality:
+//
+//  - Strings you pass through the API layer will always be converted
+//  to precomposed UTF-8 with compatibility mapping
+//  (kCFStringNormalizationFormKC).  This is done in an attempt to
+//  make SQLite correctly handle string equality for composed
+//  character sequences. This change applies only to
+//  NSStrings/CFStrings passed through the GTMSQLiteDatabase or
+//  GTMSQLiteStatement. Direct access to the database using the
+//  underlying sqlite3_* handles is not affected.
+//
+//  - The SQL UPPER/LOWER functions are replaced with CFString-based
+//  implementations which (unlike SQLite's native implementation)
+//  handle case conversion outside the ASCII range. These
+//  implementations seem to be 20-30% slower than the SQLite
+//  implementations but may be worth it for accuracy.
+//
+//  - The SQLite "NOCASE" collation is replaced with a CFString-based
+//  collation that is case insensitive but still uses literal
+//  comparison (composition-sensitive).
+//
+//  - Additional collation sequences can be created by using these keywords
+//  separated by underscores. Each option corresponds to a CFStringCompareFlags
+//  option.
+//    NOCASE                  (kCFCompareCaseInsensitive)
+//    NONLITERAL              (kCFCompareNonliteral)
+//    LOCALIZED               (kCFCompareLocalized)
+//    NUMERIC                 (kCFCompareNumerically)
+
+//  These additional options are available when linking with the 10.5 SDK:
+//    NODIACRITIC             (kCFCompareDiacriticInsensitive)
+//    WIDTHINSENSITIVE        (kCFCompareWidthInsensitive)
+//
+//  Ordering of the above options can be changed by adding "REVERSE".
+//
+//  Thus, for a case-insensitive, width-insensitive, composition-insensitive
+//  comparison that ignores diacritical marks and sorts in reverse use:
+//
+//    NOCASE_NONLITERAL_NODIACRITIC_WIDTHINSENSITIVE_REVERSE
+//
+//  - SQL LIKE and GLOB commands are implemented with CFString/CFCharacterSet
+//  comparisons. As with the other CF additions, this gives us better handling
+//  of case and composed character sequences. However, whereever reasonable,
+//  SQLite semantics have been retained. Specific notes:
+//
+//    * LIKE is case insensitive and uses non-literal comparison
+//      (kCFCompareNonliteral) by default. It is possible to modify this
+//      behavior using the accessor methods. You must use those methods
+//      instead of the SQLite  "PRAGMA case_sensitive_like" in order for them
+//      to interact properly with our CFString implementations.
+//
+//    * ESCAPE clauses to LIKE are honored, but the escape character must
+//      be expressable as a single UniChar (UTF16). The escaped characters in
+//      LIKE only escape the following UniChar, not a composed character
+//      sequence. This is not viewed as a limitation since the use of ESCAPE
+//      is typically only for characters with meaning to SQL LIKE ('%', '_')
+//      all of which can be expressed as a single UniChar.
+//
+//    * GLOB is by default case sensitive but non-literal. Again, accessor
+//      methods are available to change this behavior.
+//
+//    * Single character pattern matches ('_' for LIKE, '?' for GLOB) will
+//      always consume a full composed character sequence.
+//
+//    * As with the standard SQLite implementation, character set comparisons
+//      are only available for GLOB.
+//
+//    * Character set comparisons are always literal and case sensitive and do
+//      not take into account composed character sequences. Essentially
+//      character sets should always be expressed as a set of single UniChars
+//      or ranges between single UniChars.
+//
+
+//  SQLite is preinstalled on 10.4 only. As long as we're using the OS version
+//  of the library, limit ourself to Tiger+
+#if MAC_OS_X_VERSION_MIN_REQUIRED < MAC_OS_X_VERSION_10_4
+#error SQLite support is Tiger or later
+#endif
+
+#import <Foundation/Foundation.h>
+#import <sqlite3.h>
+
+/// Wrapper for SQLite with release/retain semantics and CFString convenience features
+@interface GTMSQLiteDatabase : NSObject {
+ @protected
+  sqlite3 *db_;     // strong
+  NSString *path_;   // strong
+  int timeoutMS_;
+  BOOL hasCFAdditions_;
+  CFOptionFlags likeOptions_;
+  CFOptionFlags globOptions_;
+  NSMutableArray *userArgDataPool_;  // strong
+}
+
+//  Get the numeric version number of the SQLite library (compiled in value
+//  for SQLITE_VERSION_NUMBER).
+//
+//  Returns:
+//    Integer version number
+//
++ (int)sqliteVersionNumber;
+
+//  Get the string version number of the SQLite library.
+//
+//  Returns:
+//    Autoreleased NSString version string
+//
++ (NSString *)sqliteVersionString;
+
+//  Create and open a database instance on a file-based database.
+//
+//  Args:
+//    path: Path to the database. If it does not exist an empty database
+//          will be created.
+//    withCFAdditions: If true, the SQLite database will include CFString
+//                     based string functions and collation sequences. See
+//                     the class header for information on these differences
+//                     and performance impact.
+//    err:  Result code from SQLite. If nil is returned by this function
+//          check the result code for the error. If NULL no result code is
+//          reported.
+//
+- (id)initWithPath:(NSString *)path
+   withCFAdditions:(BOOL)additions
+              utf8:(BOOL)useUTF8
+         errorCode:(int *)err;
+
+//  Create and open a memory-based database. Memory-based databases
+//  cannot be shared amongst threads, and each instance is unique. See
+//  SQLite documentation for details.
+//
+//  For argument details see [... initWithPath:withCFAdditions:errorCode:]
+//
+- (id)initInMemoryWithCFAdditions:(BOOL)additions
+                             utf8:(BOOL)useUTF8
+                        errorCode:(int *)err;
+
+//  Get the underlying SQLite database handle. In general you should
+//  never do this, if you do use this be careful with how you compose
+//  and decompse strings you pass to the database.
+//
+//  Returns:
+//    sqlite3 pointer
+//
+- (sqlite3 *)sqlite3DB;
+
+//  Enable/Disable the database synchronous mode. Disabling
+//  synchronous mode results in much faster insert throughput at the
+//  cost of safety. See the SQlite documentation for details.
+//
+//  Args:
+//    enable: Boolean flag to determine mode.
+//
+- (void)synchronousMode:(BOOL)enable;
+
+//  Check if this database instance has our CFString functions and collation
+//  sequences (see top of file for details).
+//
+//  Returns:
+//    YES if the GTMSQLiteDatabase instance has our CF additions
+//
+- (BOOL)hasCFAdditions;
+
+//  Set comparison options for the "LIKE" operator for databases with
+//  our CF addtions active.
+//
+//  Args:
+//    options: CFStringCompareFlags value. Note that a limited list
+//             of options are supported. For example one cannot
+//             use kCFCompareBackwards as an option.
+//
+- (void)setLikeComparisonOptions:(CFOptionFlags)options;
+
+//  Get current comparison options for the "LIKE" operator in a database
+//  with our CF additions active.
+//
+//  Returns:
+//    Current comparison options or zero if CF additions are inactive.
+//
+- (CFOptionFlags)likeComparisonOptions;
+
+//  Set comparison options for the "GLOB" operator for databases with
+//  our CF addtions active.
+//
+//  Args:
+//    options: CFStringCompareFlags value. Note that a limited list
+//             of options are supported. For example one cannot
+//             use kCFCompareBackwards as an option.
+//
+- (void)setGlobComparisonOptions:(CFOptionFlags)options;
+
+//  Get current comparison options for the "GLOB" operator in a database
+//  with our CF additions active.
+//
+//  Returns:
+//    Current comparison options or zero if CF additions are inactive.
+//
+- (CFOptionFlags)globComparisonOptions;
+
+//  Obtain the last error code from the database
+//
+//  Returns:
+//    SQLite error code, if no error is pending returns SQLITE_OK
+//
+- (int)lastErrorCode;
+
+//  Obtain an error string for the last error from the database
+//
+//  Returns:
+//    Autoreleased NSString error message
+//
+- (NSString *)lastErrorString;
+
+//  Obtain a count of rows added, mmodified or deleted by the most recent
+//  statement. See sqlite3_changes() for details and limitations.
+//
+//  Returns:
+//    Row count
+//
+- (int)lastChangeCount;
+
+//  Obtain a count of rows added, mmodified or deleted since the database
+//  was opened. See sqlite3_total_changes() for details and limitations.
+//
+//  Returns:
+//    Row count
+//
+- (int)totalChangeCount;
+
+//  Obtain the last insert row ID
+//
+//  Returns:
+//    64-bit row ID
+//
+- (unsigned long long)lastInsertRowID;
+
+//  Interrupt any currently running database operations as soon as possible.
+//  Running operations will receive a SQLITE_INTERRUPT and will need to
+//  handle it correctly (this is the callers problem to deal with).
+//
+- (void)interrupt;
+
+//  Set the timeout value in milliseconds. This is a database global affecting
+//  all running and future statements.
+//
+//  Args:
+//    timeoutMS: Integer count in ms SQLite will wait for the database to
+//               unlock before giving up and returning SQLITE_BUSY. A value
+//               of 0 or less means the database always returns immediately.
+//
+//  Returns:
+//    SQLite result code, SQLITE_OK on no error
+//
+- (int)setBusyTimeoutMS:(int)timeoutMS;
+
+//  Get the current busy timeout in milliseconds.
+//
+//  Returns:
+//    Current database busy timeout value in ms, 0 or less means no timeout.
+//
+- (int)busyTimeoutMS;
+
+//  Execute a string containing one or more SQL statements. No returned data
+//  is available, use GTMSQLiteStatement for that usage.
+//
+//  Args:
+//    sql: Raw SQL statement to prepare. It is the caller's responsibility
+//         to properly escape the SQL.
+//
+//  Returns:
+//    SQLite result code, SQLITE_OK on no error
+//
+- (int)executeSQL:(NSString *)sql;
+
+//  Convenience method to start a deferred transaction (most common case).
+//
+//  Returns:
+//    YES if the transaction started successfully
+//
+- (BOOL)beginDeferredTransaction;
+
+//  Convenience method to roll back a transaction.
+//
+//  Returns:
+//    YES if the transaction rolled back successfully
+//
+- (BOOL)rollback;
+
+//  Convenience method to commit a transaction.
+//
+//  Returns:
+//    YES if the transaction committed successfully
+//
+- (BOOL)commit;
+
+@end
+
+//  Wrapper class for SQLite statements with retain/release semantics.
+//  Attempts to behave like an NSEnumerator, however you should bind
+//  your values before beginning enumeration and unlike NSEnumerator,
+//  a reset is supported.
+//
+//  The GTMSQLiteDatabase class has options to modify some SQL
+//  functions and force particular string representations. This class
+//  honors the database preferences for those options. See the
+//  GTMSQLiteDatabase header for details.
+
+///  Wrapper class for SQLite statements with retain/release
+///  semantics.
+@interface GTMSQLiteStatement : NSObject {
+
+@protected
+  sqlite3_stmt *statement_;
+  BOOL hasCFAdditions_;
+}
+
+#pragma mark Creation, Access and Finalization
+
+//  Create an autoreleased prepared statement, see initWithSQL: for arguments.
+//
+//  NOTE: Even though this object is autoreleased you MUST call
+//  [finalizeStatement] on this when your done. See the init for explanation.
+//
+//  Returns:
+//    Autoreleased GTMSQLiteStatement
+//
++ (id)statementWithSQL:(NSString *)sql
+            inDatabase:(GTMSQLiteDatabase *)gtmdb
+             errorCode:(int *)err;
+
+//  Designated initializer, create a prepared statement. Positional and named
+//  parameters are supported, see the SQLite documentation.
+//
+//  NOTE: Although this object will clean up its statement when deallocated,
+//  you are REQUIRED to "finalize" the statement when you are
+//  through with it. Failing to do this will prevent the database from allowing
+//  new transactions or queries. In other words, leaving an instance on the
+//  autorelease pool unfinalized may interfere with other database usage if any
+//  caller sharing the database uses transactions.
+//
+//  Args:
+//    sql: Raw SQL statement to prepare. It is the caller's responsibility
+//         to properly escape the SQL and make sure that the SQL contains
+//         only _one_ statement. Additional statements are silently ignored.
+//    db: The GTMSQLiteDatabase (not retained)
+//    err:  Result code from SQLite. If nil is returned by this function
+//          check the result code for the error. If NULL no result code is
+//          reported.
+//
+- (id)initWithSQL:(NSString *)sql
+       inDatabase:(GTMSQLiteDatabase *)gtmdb
+        errorCode:(int *)err;
+
+//  Get the underlying SQLite statement handle. In general you should never
+//  do this, if you do use this be careful with how you compose and
+//  decompse strings you pass to the database.
+//
+//  Returns:
+//    sqlite3_stmt pointer
+//
+- (sqlite3_stmt *)sqlite3Statement;
+
+//  Finalize the statement, allowing other transactions to start on the database
+//  This method MUST be called when you are done with a statement.  Failure to
+//  do so means that the database will not be torn down properly when it's
+//  retain count drops to 0 or GC collects it.
+//
+//  Returns:
+//    SQLite result code, SQLITE_OK on no error
+//
+- (int)finalizeStatement;
+
+#pragma mark Parameters and Binding
+
+//  Get the number of parameters that can be bound in the prepared statement.
+//
+//  Returns:
+//    Integer count of parameters or -1 on error
+//
+- (int)parameterCount;
+
+//  Get the position of a parameter with a given name.
+//
+//  Args:
+//    paramName: String name of the parameter, including any leading punctuation
+//               (see SQLite docs)
+//
+//  Returns:
+//    1-based parameter position index or -1 on error
+//
+- (int)positionOfParameterNamed:(NSString *)paramName;
+
+//  Get the name of a parameter at a particular index.
+//
+//  Args:
+//    position: Parameter position (1-based index)
+//
+//  Returns:
+//    Autoreleased string name of the parameter, including any leading
+//    punctuation (see SQLite docs) or nil on error.
+//
+- (NSString *)nameOfParameterAtPosition:(int)position;
+
+//  Bind a NULL at a given position
+//
+//  Args:
+//    position: Parameter position (1-based index)
+//
+//  Returns:
+//    SQLite result code, SQLITE_OK on no error
+//
+- (int)bindSQLNullAtPosition:(int)position;
+
+//  Bind a blob parameter at a given position index to a raw pointer and
+//  length. The data will be copied by SQLite
+//
+//  Args:
+//    position: Parameter position (1-based index)
+//    bytes: Raw pointer to the data to copy/bind
+//    length: Number of bytes in the blob
+//
+//  Returns:
+//    SQLite result code, SQLITE_OK on no error
+//
+- (int)bindBlobAtPosition:(int)position bytes:(void *)bytes length:(int)length;
+
+//  Bind an NSData as a blob at a given position. The data will be copied
+//  by SQLite.
+//
+//  Args:
+//    position: Parameter position (1-based index)
+//    data: NSData to convert to blob
+//
+//  Returns:
+//    SQLite result code, SQLITE_OK on no error
+//
+- (int)bindBlobAtPosition:(int)position data:(NSData *)data;
+
+//  Bind a double at the given position (for floats convert to double).
+//
+//  Args:
+//    position: Parameter position (1-based index)
+//    value: Double to bind
+//
+//  Returns:
+//    SQLite result code, SQLITE_OK on no error
+//
+- (int)bindDoubleAtPosition:(int)position value:(double)value;
+
+//  Bind an NSNumber as a double value at the given position.
+//
+//  Args:
+//    position: Parameter position (1-based index)
+//    number: NSNumber to bind
+//
+//  Returns:
+//    SQLite result code, SQLITE_OK on no error
+//
+- (int)bindNumberAsDoubleAtPosition:(int)position number:(NSNumber *)number;
+
+//  Bind a 32-bit integer at the given position.
+//
+//  Args:
+//    position: Parameter position (1-based index)
+//    value: Integer to bind
+//
+//  Returns:
+//    SQLite result code, SQLITE_OK on no error
+//
+- (int)bindInt32AtPosition:(int)position value:(int)value;
+
+//  Bind an NSNumber as a 32-bit integer value at the given position.
+//
+//  Args:
+//    position: Parameter position (1-based index)
+//    number: NSNumber to bind
+//
+//  Returns:
+//    SQLite result code, SQLITE_OK on no error
+//
+- (int)bindNumberAsInt32AtPosition:(int)position number:(NSNumber *)number;
+
+//  Bind a 64-bit integer at the given position.
+//
+//  Args:
+//    position: Parameter position (1-based index)
+//    value: Int64 value to bind
+//
+//  Returns:
+//    SQLite result code, SQLITE_OK on no error
+//
+- (int)bindLongLongAtPosition:(int)position value:(long long)value;
+
+//  Bind an NSNumber as a 64-bit integer value at the given position.
+//
+//  Args:
+//    position: Parameter position (1-based index)
+//    number: NSNumber to bind
+//
+//  Returns:
+//    SQLite result code, SQLITE_OK on no error
+//
+- (int)bindNumberAsLongLongAtPosition:(int)position number:(NSNumber *)number;
+
+//  Bind a string at the given position.
+//
+//  Args:
+//    position: Parameter position (1-based index)
+//    string: String to bind (string will be converted to UTF8 and copied).
+//            NOTE: For bindings it is not necessary for you to SQL escape
+//                  your strings.
+//
+//  Returns:
+//    SQLite result code, SQLITE_OK on no error
+//
+- (int)bindStringAtPosition:(int)position string:(NSString *)string;
+
+#pragma mark Results
+
+//  Get the number of result columns per row this statement will generate.
+//
+//  Returns:
+//    Column count, 0 if no columns will be returned ("UPDATE.." etc.),
+//    -1 on error.
+//
+- (int)resultColumnCount;
+
+//  Get the name of result colument at a given index.
+//
+//  Args:
+//    position: Column position (0-based index)
+//
+//  Returns:
+//    Autoreleased NSString column name or nil if no column exists at that
+//    position or error.
+//
+- (NSString *)resultColumnNameAtPosition:(int)position;
+
+//  Get the number of data values in the current row of this statement.
+//  Generally this will be the same as resultColumnCount:, except when row
+//  iteration is done (see SQLite docs for sqlite3_data_count()).
+//
+//  Returns:
+//    Data count or 0 if no data will be returned, -1 on error.
+//
+- (int)rowDataCount;
+
+//  Get the SQLite type constant for a column in a row. Note that because
+//  SQLite does not enforce column type restrictions the type of a particular
+//  column in a row may not match the declared type of the column.
+//
+//  Args:
+//    position: Column position (0-based index)
+//
+//  Returns:
+//    SQLite data type constant (i.e. SQLITE_INTEGER, SQLITE_FLOAT, etc.) or
+//    -1 on error.
+//
+- (int)resultColumnTypeAtPosition:(int)position;
+
+//  Get the data for a result row blob column as an NSData
+//
+//  Args:
+//    position: Column position (0-based index)
+//
+//  Returns:
+//    Autoreleased NSData, nil on error
+//
+- (NSData *)resultBlobDataAtPosition:(int)position;
+
+//  Get the data for a result row blob column as a double
+//
+//  Args:
+//    position: Column position (0-based index)
+//
+//  Returns:
+//    Double value
+//
+- (double)resultDoubleAtPosition:(int)position;
+
+//  Get the data for a result row blob column as an integer
+//
+//  Args:
+//    position: Column position (0-based index)
+//
+//  Returns:
+//    Integer value
+//
+- (int)resultInt32AtPosition:(int)position;
+
+//  Get the data for a result row blob column as a long long
+//
+//  Args:
+//    position: Column position (0-based index)
+//
+//  Returns:
+//    Long long value
+//
+- (long long)resultLongLongAtPosition:(int)position;
+
+//  Get the data for a result row blob column as an NSNumber
+//
+//  Args:
+//    position: Column position (0-based index)
+//
+//  Returns:
+//    Autoreleased NSNumber value or nil on error
+//
+- (NSNumber *)resultNumberAtPosition:(int)position;
+
+//  Get the data for a result row blob column as an NSString
+//
+//  Args:
+//    position: Column position (0-based index)
+//
+//  Returns:
+//    Autoreleased NSString value or nil on error
+//
+- (NSString *)resultStringAtPosition:(int)position;
+
+//  Get a Foundation object (NSData, NSNumber, NSString, NSNull) for the column,
+//  autodetecting the most appropriate representation.
+//
+//  Args:
+//    position: Column position (0-based index)
+//
+//  Returns:
+//    Autoreleased Foundation type, nil on error
+//
+- (id)resultFoundationObjectAtPosition:(int)position;
+
+//  Get an array of Foundation objects for the row in query column order.
+//
+//  Returns:
+//    Autoreleased array of Foundation types or nil if there is no
+//    data in the row or error
+//
+- (NSArray *)resultRowArray;
+
+//  Get a dictionary of Foundation objects for the row keyed by column name.
+//
+//  Returns:
+//    Autoreleased dictionary of Foundation types or nil if there is no
+//    data in the row or error.
+//
+- (NSDictionary *)resultRowDictionary;
+
+#pragma mark Rows
+
+//  Step the statement forward one row, potentially spinning forever till
+//  the row can be located (if database is SQLITE_BUSY).
+//
+//  Returns:
+//    SQLite result code, SQLITE_ROW if a row was found or SQLITE_DONE if
+//    no further rows match the statement.
+//
+- (int)stepRow;
+
+//  Step the statement forward one row, waiting at most the currrent database
+//  busy timeout (see [GTMSQLiteDatabase setBusyTimeoutMS]).
+//
+//  Returns:
+//    SQLite result code, SQLITE_ROW if a row was found or SQLITE_DONE if
+//    no further rows match the statement. If SQLITE_BUSY is returned the
+//    database did not unlock during the timeout.
+//
+- (int)stepRowWithTimeout;
+
+//  Reset the statement starting again at the first row
+//
+//  Returns:
+//    SQLite result code, SQLITE_OK on no error
+//
+- (int)reset;
+
+//  Check if the SQLite parser recognizes the receiver as one or more valid
+//  SQLite statements.
+//
+//  Returns:
+//    YES if the string is a complete and valid SQLite statement
+//
++ (BOOL)isCompleteStatement:(NSString *)string;
+
+//  Quote and escape the receiver for SQL.
+//  Example: "This is wild! It's fun!"
+//  Becomes: "'This is wild! It''s fun!'"
+//
+//  Returns:
+//    Autoreleased NSString
++ (NSString *)quoteAndEscapeString:(NSString *)string;
+
+@end