From fde1ea051c0385638accd67362cd25f2fe1ae654 Mon Sep 17 00:00:00 2001 From: "thomasvl@gmail.com" Date: Thu, 26 Feb 2009 01:35:12 +0000 Subject: adding SQLite helper/wrapper --- Foundation/GTMSQLite.h | 713 +++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 713 insertions(+) create mode 100644 Foundation/GTMSQLite.h (limited to 'Foundation/GTMSQLite.h') 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 +#import + +/// 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 -- cgit v1.2.3