00001 #ifndef s11n_net_SQ3_HPP_INCLUDED 00002 #define s11n_net_SQ3_HPP_INCLUDED 1 00003 00004 #include <string> 00005 #include <map> 00006 #include <sqlite3.h> 00007 00008 #ifndef SQ3_USE_WCHAR 00009 # define SQ3_USE_WCHAR 0 00010 #endif 00011 00012 // Enable WCHAR support when it's there. Thanks to Artem Gr <artem@bizlink.ru> 00013 // for this. However, sqlite3's wchar code requires that a wchar is 16-bit, which 00014 // is not the case on gcc/linux (32-bit). 00015 #ifndef SQ3_USE_WCHAR 00016 # ifdef _GLIBCXX_USE_WCHAR_T 00017 # define SQ3_USE_WCHAR 1 00018 # elif defined(UNICODE) // Windows uses this 00019 # define SQ3_USE_WCHAR 1 00020 # else 00021 # define SQ3_USE_WCHAR 0 // default 00022 # endif 00023 #endif 00024 00025 #if SQ3_USE_WCHAR 00026 # warning SQ3_USE_WCHAR: INCOMPLETE/BROKEN code is enabled! 00027 #endif 00028 00029 00030 // #ifndef COUT 00031 // #include <iostream> 00032 // #define COUT std::cerr << "SQ3:"<<__FILE__ << ":" << std::dec<<__LINE__ << ": " 00033 // #endif 00034 00035 #include "refcount.hpp" 00036 00037 00038 /** 00039 The sq3 namespace encapsulates an OO sqlite3 API very similar to 00040 the sqlite3x API, but this one uses no exception handling (i.e., it 00041 doesn't throw on errors). It is intended to be an alternative for 00042 platforms/projects where exceptions are not desired or not 00043 available. 00044 00045 Wide-char support does not currently work. (On my platform 00046 (gcc/linux) wchar_t is 4 bytes and sqlite3 wants 2-bytes wide chars 00047 for UTF16 text.) 00048 00049 This code was written by stephan beal (stephan@s11n.net) and is 00050 released into the Public Domain. It was modelled heavily after code 00051 written by Cory Nelson, but was reimplemented from scratch for use 00052 on a PocketPC platform where throwing exceptions wasn't allowed. 00053 00054 This code's home page is: 00055 00056 http://s11n.net/sqlite/ 00057 00058 Change history (only "significant" changes documented here): 00059 00060 - 2007.02.26: integrated isnull() patch from 00061 Xosé Antón Otero Ferreira <xoseotero at gmail com> 00062 00063 - 2007.01.22: sq3::reader class renamed to sq3::cursor. 00064 Renamed cursor::read() to cursor::step(). 00065 00066 - 2007.01.27: Added the cursor::get(std::string const&,...) 00067 family of functions to enable get-by-string-index. Added 00068 database::clear(). 00069 00070 - 2007.01.28: rcptr<> shared pointer class introduced to enable 00071 reasonable copy semantics for the major underlying data structures. 00072 sq3::database and sq3::statement can now be shallowly copied. 00073 00074 - 2007.02.14: added statement::bind(char const *,...) functions. 00075 00076 */ 00077 namespace sq3 { 00078 00079 /** 00080 The type used for signed 64-bit integer operations. 00081 */ 00082 typedef sqlite_int64 int64_t; 00083 /** 00084 The type used for unsigned 64-bit integer operations. 00085 */ 00086 typedef sqlite_uint64 uint64_t; 00087 00088 // /** 00089 // The published result codes from the sqlite3 API. 00090 // */ 00091 // enum Sqlite3ResultCodes { 00092 // ResultOK = SQLITE_OK, 00093 // ResultERROR = SQLITE_ERROR, 00094 // ResultINTERNAL = SQLITE_INTERNAL, 00095 // ResultPERM = SQLITE_PERM, 00096 // ResultABORT = SQLITE_ABORT, 00097 // ResultBUSY = SQLITE_BUSY, 00098 // ResultLOCKED = SQLITE_LOCKED, 00099 // ResultNOMEM = SQLITE_NOMEM, 00100 // ResultREADONLY = SQLITE_READONLY, 00101 // ResultINTERRUPT = SQLITE_INTERRUPT, 00102 // ResultIOERR = SQLITE_IOERR, 00103 // ResultCORRUPT = SQLITE_CORRUPT, 00104 // ResultNOTFOUND = SQLITE_NOTFOUND, 00105 // ResultFULL = SQLITE_FULL, 00106 // ResultCANTOPEN = SQLITE_CANTOPEN, 00107 // ResultPROTOCOL = SQLITE_PROTOCOL, 00108 // ResultEMPTY = SQLITE_EMPTY, 00109 // ResultSCHEMA = SQLITE_SCHEMA, 00110 // ResultTOOBIG = SQLITE_TOOBIG, 00111 // ResultCONSTRAINT = SQLITE_CONSTRAINT, 00112 // ResultMISMATCH = SQLITE_MISMATCH, 00113 // ResultMISUSE = SQLITE_MISUSE, 00114 // ResultNOLFS = SQLITE_NOLFS, 00115 // ResultAUTH = SQLITE_AUTH, 00116 // ResultROW = SQLITE_ROW, 00117 // ResultDONE = SQLITE_DONE 00118 // }; 00119 00120 /** 00121 A char type used by some of the sqlite3 API to represent 00122 text data. This is really annoying, but sqlite3's API 00123 explicitely uses UNSIGNED char arrays for a couple of 00124 strings, while using signed char arrays for almost 00125 everything else. 00126 */ 00127 typedef char unsigned sqlite3_text_char_t; 00128 00129 00130 class statement; // unfortunate fwd decl 00131 00132 /** 00133 A specialized dtor to close sqlite3 handles, for use 00134 with refcount::rcptr<sqlite3,sqlite3_finalizer >. 00135 */ 00136 struct sqlite3_finalizer 00137 { 00138 /** 00139 Calls sqlite3_close(t) and assigns t to 0. 00140 */ 00141 void operator()( sqlite3 * & t ); 00142 }; 00143 00144 /** 00145 A specialized dtor to reset (not close) 00146 sq3::statement objects, for use with 00147 refcount::rcptr<sqlite3,statement_reset_finalizer>. 00148 */ 00149 struct statement_reset_finalizer 00150 { 00151 /** 00152 Calls t->reset() and assigns t to 0. 00153 */ 00154 void operator()( ::sq3::statement * & t ); 00155 }; 00156 00157 /** 00158 A specialized dtor to call reset sqlite3_stmt 00159 handles(), for use with 00160 refcount::rcptr<sqlite3,sqlite3_stmt_reset_finalizer>. 00161 */ 00162 struct sqlite3_stmt_reset_finalizer 00163 { 00164 /** 00165 Calls sqlite3_reset(t) and assigns t to 0. 00166 */ 00167 void operator()( sqlite3_stmt * & t ); 00168 }; 00169 00170 00171 /** 00172 A specialized dtor to finalize sqlite3_stmt 00173 handles, for use with 00174 refcount::rcptr<sqlite3,sqlite3_stmt_finalizer>. 00175 */ 00176 struct sqlite3_stmt_finalizer 00177 { 00178 /** 00179 Calls sqlite3_finalize(t) and assigns t to 0. 00180 */ 00181 void operator()( sqlite3_stmt * & t ); 00182 }; 00183 00184 /** 00185 rc_is_okay() is an easy way to check if rc is one of 00186 SQLITE_OK, SQLITE_ROW, or SQLITE_DONE. This function 00187 returns true if rc is one of those values, else false. 00188 (Code which accepts arbitrary SQL from a user often has to 00189 accept any of those three result codes as success.) 00190 */ 00191 bool rc_is_okay( int rc ); 00192 00193 00194 /** 00195 Encapsulates a connection to an sqlite database. 00196 00197 This type is virtual/subclassable so that clients can add 00198 initialization routines to all of their databases, such as 00199 adding sqlite-builtin functions and collating sequences. 00200 Simply do such initializations in your subclass ctors. 00201 00202 A note on the lack of proper constness for much of this 00203 API... Potentially, in the face of triggers, multiple 00204 threads, and whatnot, it is not generically possible to 00205 100% ensure that any given SQL statement does *not* modify 00206 the database in at least *some* way. To reflect this 00207 underlying state of flux, "it was decided" (it really was) 00208 that very little, if any, of the members of this class 00209 would be const. Only those which could 100% guaranty proper 00210 constness. 00211 00212 Notes about copying: 00213 00214 Copying a db object is actually shallow copying. All copies 00215 of this type will refer to the same underlying (sqlite3*) 00216 db handle until/unless: 00217 00218 - When open() is called, the object on which it was 00219 called may separate itself from the rcptr relationship with 00220 the older db handle and start a new one. 00221 00222 - close(false) affects only the calling db. When the 00223 reference count for the (sqlite3*) handle drops to zero, 00224 then sqlite3_close() will be called. close(true) closes the 00225 db handle immediately, affecting all copies of this object. 00226 00227 - When take_handle() is 00228 called then ownership of the underlying db handle is removed from ALL 00229 copies of this object. They will still refer to the handle, but using it 00230 is not legal. 00231 */ 00232 class database 00233 { 00234 private: 00235 friend class statement; 00236 mutable refcount::rcptr<sqlite3,sqlite3_finalizer> m_dbh; 00237 std::string m_name; 00238 public: 00239 00240 /** 00241 The low-level handle to the sqlite db. 00242 NEVER close this handle. It is permissible 00243 to use it to run queries, add functions 00244 to the db, etc. 00245 00246 This object retains ownership of the returned 00247 handle. 00248 */ 00249 sqlite3 * handle() const; 00250 /** 00251 Creates an unopened database. Use open() 00252 to open a file or take_handle() to transfer 00253 an existing db handle to this object. 00254 */ 00255 database(); 00256 00257 /** 00258 dbh is assumed to be an opened, valid db handle. 00259 This function transfers ownership of dbh to this 00260 object. Specifically, dbh will be closed when the 00261 last database with the same db handle goes out of 00262 scope or is closed. The name is simply informative, 00263 and may or may not be the same actually used for 00264 opening dbh. 00265 00266 Note that this function does not call the protected 00267 this->on_open() because, quite frankly, i'm not yet 00268 sure if it really makes sense to do so. 00269 */ 00270 void take_handle( sqlite3 * dbh, std::string const & name = "" ); 00271 00272 /** 00273 Transfers ownership of this->handle() to the 00274 caller. ALL copies of this object which point to 00275 the handle, except for this copy, still have a 00276 pointer to that handle but will not call the handle 00277 dtor when they go out of scope or are closed. 00278 */ 00279 sqlite3 * take_handle(); 00280 00281 /** 00282 Opens/creates the given db file. Use is_open() to 00283 see if the opening worked. 00284 00285 Subclasses should not call this from a ctor 00286 initialization list because this function may 00287 eventually call on_open(), which is virtual, but 00288 the subclass part of the class may not be in place 00289 to catch that virtual call. So subclasses should 00290 initialize with the no-arg parent class ctor 00291 and should then call open(filename) themselves. 00292 */ 00293 explicit database( std::string const & filename ); 00294 00295 /** 00296 Closes this db. 00297 */ 00298 virtual ~database(); 00299 00300 /** 00301 Returns true if this db is opened. 00302 Does not detect errors such as opening 00303 a non-db file. 00304 */ 00305 bool is_open() const; 00306 00307 /** 00308 Returns the name of the db file. 00309 */ 00310 std::string name() const; 00311 00312 /** 00313 Returns the last error message from 00314 sqlite, or an empty string if this object 00315 is not opened. Unfortunately, sqlite3 00316 returns the literal string "not an error" 00317 on non-errors, instead of returning an empty 00318 string. 00319 */ 00320 std::string errormsg() const; 00321 00322 /** 00323 Creates/opens the given db file. 00324 00325 The flags parameter is only used if this code is 00326 compiled against sqlite3 >= 3.5.1, and can 00327 theoretically take any values defined in the 00328 SQLITE_OPEN_xxx family of macros. The sqlite3 00329 documentation only describes the use of 00330 SQLITE_OPEN_READONLY, SQLITE_OPEN_READWRITE, and 00331 SQLITE_OPEN_CREATE, thus other values may nor may 00332 not work. If (0==flags) then 00333 (SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE) is 00334 assumed. See sqlite3_open_v2() for the exact 00335 semantics. 00336 00337 On success it returns SQLITE_OK, or some other 00338 value on error. 00339 00340 Calling open() will implicitly call close() on any 00341 existing connection. If that close() fails then 00342 this open() will also fail. If the open() succeeds, 00343 it does NOT affect other (shallow) copies of this 00344 object: they will still refer to the older db 00345 handle. 00346 00347 Note that sqlite3 supports the special db name 00348 ":memory:" to represent an in-memory database. Such 00349 databases cannot be saved directly to disk and are 00350 lost when this object closes the db. 00351 00352 Windows users beware: according to the sqlite3 00353 documentation, the db name MUST be in UTF8 format, 00354 regardless of the current codepage used by Windows, 00355 and it is the caller's responsibility to perform 00356 any conversion, if needed. 00357 00358 Internal notes: 00359 00360 If the underlying sqlite3_open() succeeds, the 00361 protected member on_open() in called. If it returns 00362 a value other than SQLITE_OK then this->close() is 00363 called and the value returned from on_open() is 00364 returned from this function. 00365 00366 Subclasses which override this function and do not 00367 want to call the base implementation should call 00368 on_open() when done to allow subclasses to 00369 initialize the database if they like. If on_open() 00370 fails then this->close() should be called to free 00371 up the resources and mark this object as unopened. 00372 00373 The flags argument was added 20071018, suggested by 00374 Joaquim Campos Salles (Joaquim.Salles at 00375 br.unisys.com). 00376 00377 */ 00378 virtual int open( char const *, long flags = 0 ); 00379 00380 /** 00381 Functionally identical to open(char const*,long). 00382 This function calls that one, so subclasses wishing 00383 to change open()'s behaviour need only reimplement 00384 that one. 00385 */ 00386 int open( std::string const &, long flags = 0 ); 00387 00388 /** 00389 "Closes" this db. That actually means that 00390 it queues it to be closed when the last 00391 database object which is using that db handle 00392 closes or goes out of scope. 00393 00394 The force parameter changes the handling of those 00395 sqlite3_close(): 00396 00397 If force is false, this function always returns 00398 SQLITE_OK unless this object is already closed, in 00399 which case SQLITE_ERROR is returned but can almost 00400 certainly be safely ignored. Unfortunately, due to 00401 the asynchronous nature of this operation, we can't 00402 return the value from the actual sqlite3_close() 00403 call (if any) when force is set to false. 00404 00405 If force is true then the internal reference 00406 counting is anulled and the db handle is closed 00407 immediately. This affects all copies of this 00408 object, so use with care (but use if you must). In 00409 this case, the value of sqlite3_close() is 00410 returned, but the the exact state of the underlying 00411 database handle is ambiguously defined in the 00412 sqlite3 docs. So... if that happens then the 00413 underlying db handle is assumed to be invalid, since 00414 the "test*.c" files which come with sqlite3 seem 00415 to treat it as such. 00416 */ 00417 int close( bool force = false ); 00418 00419 00420 00421 /** 00422 Returns the rowid of the most recently inserted row 00423 on this db. 00424 */ 00425 int64_t insertid(); 00426 00427 /** 00428 Returns the number of database rows that were 00429 changed (or inserted or deleted) by the most recently 00430 completed INSERT, UPDATE, or DELETE statement. 00431 00432 SQLite implements the command "DELETE FROM table" 00433 without a WHERE clause by dropping and recreating 00434 the table. To get an accurate count of the number 00435 of rows deleted, use "DELETE FROM table WHERE 1" 00436 instead. 00437 */ 00438 int changes(); 00439 00440 /** 00441 See sqlite3_busy_timeout(). 00442 */ 00443 int setbusytimeout( int ms ); 00444 00445 /** 00446 Functionally identical to execute(char const *). 00447 */ 00448 int execute(const std::string &sql); 00449 00450 /** 00451 Executes a statement which is assumed to have 00452 a single step and a void result. Returned result 00453 is that of an underlying call to sqlite3_step(), 00454 which means that SQLITE_DONE or SQLITE_ROW evaluate 00455 to success. 00456 */ 00457 int execute(char const * sql); 00458 00459 /** 00460 Executes a statement which is assumed to have 00461 a single result step and an integer result. 00462 On success, tgt will be set to the query's 00463 value. Typically one of SQLITE_ROW or SQLITE_DONE 00464 are returned on success. 00465 */ 00466 int execute(std::string const & sql, int & tgt); 00467 00468 /** 00469 See execute( std::string, int & ); 00470 */ 00471 int execute(char const * sql, int & tgt); 00472 00473 /** 00474 See execute( std::string, int & ); 00475 */ 00476 int execute(char const * sql, int64_t & tgt); 00477 00478 /** 00479 See execute( std::string, int & ); 00480 */ 00481 int execute(std::string const & sql, int64_t & tgt); 00482 00483 /** 00484 See execute( std::string, int & ); 00485 */ 00486 int execute(char const * sql, double & tgt); 00487 /** 00488 See execute( std::string, int & ); 00489 */ 00490 int execute(std::string const & sql, double & tgt); 00491 00492 /** 00493 See execute( std::string, int & ); 00494 */ 00495 int execute(char const * sql, std::string & tgt); 00496 /** 00497 See execute( std::string, int & ); 00498 */ 00499 int execute(std::string const & sql, std::string & tgt); 00500 00501 /** 00502 See execute( std::string, int & ); 00503 00504 sql is executed and the string result is written to 00505 tgt and the length of the result string (in bytes) 00506 is written to len. The text bytes are owned by 00507 sqlite and will likely become invalid on the next 00508 db cursor operation, so copy them if you need them. 00509 00510 Returns the result of stepping through a result 00511 set, which is typically one of SQLITE_ROW or 00512 SQLITE_DONE. 00513 */ 00514 int execute(char const * sql, sqlite3_text_char_t const ** tgt, int & len ); 00515 /** 00516 See execute( char const *, sqlite3_text_char_t const **, int & ). 00517 This function is identical. 00518 */ 00519 int execute(std::string const & sql, sqlite3_text_char_t const ** tgt, int & len ); 00520 00521 00522 /** 00523 See execute( char const *, sqlite3_text_char_t **, int & ). 00524 This function is identical except that tgt is a 00525 void pointer. 00526 */ 00527 int execute(std::string const & sql, void const ** tgt, int & sz ); 00528 /** 00529 See execute( char const *, sqlite3_text_char_t **, int & ). 00530 This function is identical except that tgt is a 00531 void pointer. 00532 */ 00533 int execute(char const * sql, void const ** tgt, int & sz ); 00534 00535 00536 /** 00537 Executes the given query, calling the given callback function for each 00538 row of the result set. The data pointer is passed on as-is to the callback. 00539 Any error string is written to errmsg. 00540 00541 Return value is that of an underlying sqlite3_exec() call. 00542 */ 00543 int execute( std::string const & sql, sqlite3_callback callback, void * data, std::string & errmsg ); 00544 /** 00545 Identical to the execute(std::string,sqlite3_callback,void*,std::string&). 00546 */ 00547 int execute( char const * sql, sqlite3_callback callback, void * data, std::string & errmsg ); 00548 00549 /** 00550 Convenience overload which has a default data value 00551 of 0 and ignores any error string passed back by 00552 sqlite3_exec(). 00553 */ 00554 int execute( std::string const & sql, sqlite3_callback callback, void * data = 0 ); 00555 /** 00556 Convenience overload which has a default data value 00557 of 0 and ignores any error string passed back by 00558 sqlite3_exec(). 00559 */ 00560 int execute( char const * sql, sqlite3_callback callback, void * data = 0 ); 00561 00562 /** 00563 This is a convenience wrapper for execute( "pragma ..." ). 00564 Return value is that of the underlying execute() call. 00565 00566 code should be a pragma key or key/value string, such as 00567 "temp_store=MEMORY" or "synchronous=OFF" 00568 */ 00569 int pragma( char const * code ); 00570 00571 /** 00572 Convenience wrapper around execute("vacuum"). The 00573 vacuum operation attempts to free up any unused 00574 disk space in the database. 00575 */ 00576 int vacuum(); 00577 00578 #if SQ3_USE_WCHAR 00579 int open( wchar_t const * dbname ); 00580 int open( std::wstring const & dbname ); 00581 // int execute(char const * sql, std::wstring & tgt); 00582 // int execute(std::string const & sql, std::wstring & tgt); 00583 #endif // SQ3_USE_WCHAR 00584 00585 /** 00586 Looks through sqlite_master for a list of views, 00587 triggers, and tables, and drops them all (in that 00588 order). Subclasses are welcomed to reimplement it 00589 to do less destructive cleansing, such as just 00590 dropping data from a certain table. 00591 00592 Returns SQLITE_OK on success. 00593 00594 If you need to free up the newly-freed space, be 00595 sure to call vacuum(), or else the file size may 00596 not actually shrink. 00597 00598 Also remember that any code which is expecting data 00599 to be in this database will not work after this function 00600 is done! 00601 */ 00602 virtual int clear(); 00603 00604 protected: 00605 /** 00606 This function is called when open() succeeds. The 00607 default implementation does nothing and always 00608 returns SQLITE_OK, but subclasses may wish to do 00609 something here. If this function returns any value 00610 other than SQLITE_OK then this->close() is called 00611 before open() returns. 00612 00613 */ 00614 virtual int on_open(); 00615 00616 00617 }; 00618 00619 /** 00620 This type represents a transaction block in an SQL 00621 session. Note that as of this writing, sqlite3 does 00622 not support nested transactions. 00623 */ 00624 class transaction 00625 { 00626 private: 00627 transaction & operator=( transaction const & ); // unimplemented 00628 transaction( transaction const & ); // unimplemented 00629 database & m_db; 00630 bool m_intrans; 00631 public: 00632 /** 00633 Creates a transaction for the given db. If start 00634 is true (the default) then this->begin() is called. 00635 */ 00636 transaction( database & db, bool start = true ); 00637 /** 00638 Calls this->rollback() 00639 */ 00640 ~transaction(); 00641 /** 00642 Starts the transaction. Return value is the result 00643 of calling sqlite3_exec(db,"begin;"). If SQLITE_OK 00644 is returned then this object considers itself to be 00645 active, such that calling commit() and rollback() 00646 should be able to succeed. 00647 */ 00648 int begin(); 00649 /** 00650 Commits the active transaction. Returns SQLITE_OK 00651 on success and any other value on error. Return 00652 value is that of the underlying sqlite3_exec() 00653 call. 00654 00655 After calling this function, this object is considered to 00656 NOT be in a transaction unless SQLITE_BUSY is returned. 00657 If that happens, the transaction is still open and commit() 00658 should be called later to close the transaction. 00659 */ 00660 int commit(); 00661 /** 00662 Initiates a rollback and returns the result of 00663 executing a rollback command. If this object is not 00664 active (begin() has not been called) then this 00665 function returns SQLITE_ERROR and has no 00666 side-effects. Return value is that of the 00667 underlying sqlite3_exec() call. 00668 */ 00669 int rollback(); 00670 }; 00671 00672 /** 00673 This type is for stepping through a db query result. 00674 Clients do not normally create cursors directly, but 00675 through the statement::get_cursor() 00676 function. 00677 00678 cursor objects are copied shallowly - each copy points 00679 back to a single original statement object. That statement 00680 is reset when the last of these copies goes out of scope 00681 or is finalized. 00682 00683 */ 00684 class cursor 00685 { 00686 private: 00687 //friend class statement; 00688 refcount::rcptr<statement,statement_reset_finalizer> m_stmt; 00689 /** 00690 And internal helper type for fetching data sets by 00691 string lookups instead of integer indexes. 00692 */ 00693 typedef std::map<std::string,int> NameToIndexMap; 00694 NameToIndexMap * m_cn; // maps column names to column indexes for use with get(). 00695 00696 00697 /** 00698 If rhs is this object, this function does nothing, 00699 otherwise it copies rhs. 00700 */ 00701 void copy( cursor const & rhs ); 00702 /** 00703 Hashes the colname-to-index mapping. 00704 Returns: 00705 00706 -1: result was cached before and will stay cached until 00707 this object expires. 00708 00709 0: this result set has no columns. This is likely an error. 00710 00711 1 or higher: the number of column indexes. 00712 00713 As a side-effect, this function initializes this->m_cn 00714 if that object has not already been created. 00715 00716 If the return value is anything other than this->colcount() 00717 then an error occurred during the collection of the 00718 column names. This is exceedingly unlikely to happen. 00719 */ 00720 int index_colnames(); 00721 00722 public: 00723 /** 00724 Creates an empty cursor, whose only valid use is to 00725 assign it from another cursor. 00726 */ 00727 cursor(); 00728 /** 00729 Copies rhs. This object and rhs now point to the 00730 same underlying sqlite3 structures, and modifying 00731 one of these objects modifies the other, in effect. 00732 */ 00733 cursor( cursor const & rhs ); 00734 /** 00735 See the copy ctor. 00736 */ 00737 cursor & operator=( cursor const & ); 00738 00739 /** 00740 Identical to calling st.get_cursor(). 00741 */ 00742 cursor( statement & st ); 00743 00744 /** 00745 A curious side-effect which one needs to be 00746 aware of but very rarely is an issue: 00747 00748 When cursors are created they *always* have an 00749 associated prepared statement. When the last cursor 00750 with a reference to that same statement goes out of 00751 scope or is close()ed then the underlying statement 00752 object is reset(). That sounds curious, but is 00753 indeed the desired behaviour for this class, and 00754 breaks some common client code constructs when the 00755 underlying statement is not automatically reset. 00756 Without this "feature", client code could not run, 00757 for exaple, myStatement.execute( myInt ), two times 00758 in a row because the second time around the 00759 statement would be at its end point and need to be 00760 manually reset. Thus client code should never mix 00761 the use of a cursor object and the non-cursor 00762 statement API on the same statement. 00763 00764 See statement::close() for more details. 00765 */ 00766 ~cursor(); 00767 00768 /** 00769 Uses sqlite3_step() to step through this object's 00770 data set by one step. Returns the result of 00771 sqlite3_step(), which means: SQLITE_ROW if it 00772 read, SQLITE_DONE at the end of a data set, 00773 and any other value on error. 00774 */ 00775 int step(); 00776 /** 00777 This is functionally the same as calling reset on 00778 the underlying prepared statement object to which 00779 this cursor is tied. Use with care, as it affects 00780 all cursors which point to the same statement 00781 object. 00782 00783 returns SQLITE_OK on success, else another sqlite3 00784 error code. 00785 */ 00786 int reset(); 00787 00788 /** 00789 "Disconnects" this object from the underlying 00790 result set, making this object useless for anything 00791 but as the target of an assignment. 00792 00793 It is normally not necessary to call this function, 00794 but it may be in some special cases. 00795 */ 00796 void close(); 00797 00798 /** 00799 Returns the column count of the underlying 00800 prepared statement. May return 0 for queries which 00801 has no return value (e.g. UPDATE). Returns -1 00802 on error. 00803 */ 00804 int colcount(); 00805 00806 /** 00807 If column index (0-based) is in bounds then this 00808 function check if the value of the given column index 00809 is NULL and assigns tgt to the result of this 00810 comprobation. On success, SQLITE_OK is returned. 00811 On any other return value, tgt is not modifed. 00812 */ 00813 int isnull( int index, bool & tgt ); 00814 00815 /** 00816 If column index (0-based) is in bounds then this 00817 function assigns tgt to the value of the given 00818 column index. On success, SQLITE_OK is returned. 00819 On any other return value, tgt is not modifed. 00820 */ 00821 int get( int index, int & tgt ); 00822 /** 00823 See get(int,int&). 00824 */ 00825 int get( int index, int64_t & tgt ); 00826 /** 00827 See get(int,int&). 00828 */ 00829 int get( int index, double & tgt ); 00830 /** 00831 See get(int,int&). 00832 */ 00833 int get( int index, std::string & tgt ); 00834 /** 00835 If index (0-based) is in bounds, this function 00836 gets the (char unsigned const *) data at that 00837 column index and assigns tgt to that value 00838 and sz to the size of the data. 00839 00840 tgt is written to by this func but ownership 00841 of the underlying data remains with sqlite. 00842 That is, the caller does not need to free 00843 the memory pointed to by tgt, but may need to 00844 copy it if he wants to use it later. 00845 */ 00846 int get( int index, sqlite3_text_char_t const ** tgt, int & sz ); 00847 /** 00848 See get(int,char const **, int&). Only the tgt 00849 type is different. 00850 */ 00851 int get( int index, void const ** tgt, int & sz ); 00852 00853 00854 /** 00855 This is fundamentally identical to get(int,int &) 00856 except that the key type is a string, which must 00857 exactly (case-sensitively) match a column name from 00858 this result set. On success, SQLITE_OK is returned 00859 and tgt is modified. On error, some other code is 00860 returned and tgt is not modified. 00861 00862 Note that fetching by string index is much less 00863 efficient than looking up by integer index, but of 00864 course also a lot more convenient. If you're 00865 looking for the most speed, go with 00866 get(int,...). If you're looking for flexibility and 00867 convenience, at the cost of a few extra cyles and a 00868 tiny bit of extra memory usage per cursor, then use 00869 string-based keys. 00870 */ 00871 int get( std::string const & key, int & tgt ); 00872 /** 00873 See get(std::string const &,int&). 00874 */ 00875 int get( std::string const & key, int64_t & tgt ); 00876 /** 00877 See get(std::string const &,int&). 00878 */ 00879 int get( std::string const & key, double & tgt ); 00880 /** 00881 See get(std::string const &,int&). 00882 */ 00883 int get( std::string const & key, std::string & tgt ); 00884 /** 00885 If indexis in bounds, this function gets the (char 00886 unsigned const *) data at that column index and 00887 assigns tgt to that value and sz to the size of the 00888 data. 00889 00890 tgt is written to by this func but ownership 00891 of the underlying data remains with sqlite. 00892 That is, the caller does not need to free 00893 the memory pointed to by tgt, but may need to 00894 copy it if he wants to use it later. 00895 */ 00896 int get( std::string const & key, sqlite3_text_char_t const ** tgt, int & sz ); 00897 /** 00898 See get(std::string const &,char const **, int&). Only the tgt 00899 type is different. 00900 */ 00901 int get( std::string const & key, void const ** tgt, int & sz ); 00902 00903 00904 /** 00905 Sets str to the column name as the given index 00906 (0-based). Returns SQLITE_OK on success, else 00907 SQLITE_ERROR and str is not modified. 00908 */ 00909 int colname( int index, std::string & str ); 00910 /** 00911 Points str to the nul-terminated column name at the 00912 given index (0-based), or 0 on error. This 00913 overload avoids an extra copy of the column name, 00914 but sqlite owns the string and clients will need to 00915 make a copy of it if they want to continue to use 00916 it beyond the lifetime of this object's underlying 00917 prepared statement. 00918 00919 Returns SQLITE_OK if str is set, otherwise 00920 SQLITE_ERROR. 00921 */ 00922 int colname( int index, char const ** str ); 00923 //int colname( int index, std::wstring & ); 00924 }; 00925 00926 00927 /** 00928 This class represents a prepared database statement. 00929 00930 statement objects are copied shallowly - each copy points 00931 back to a single original sqlite3_stmt object. That sqlite3_stmt 00932 is finalized with the last of these copies goes out of scope 00933 or is finalized. 00934 00935 Sample usage: 00936 \code 00937 // Reading data: 00938 statement st(mydb, "select * from sqlite_master"); 00939 if( ! st.is_prepared() ) 00940 { 00941 ... error ... 00942 } 00943 cursor cur( st.get_cursor() ); 00944 while( SQLITE_ROW == cur.step() ) 00945 { 00946 ... do something with each row ... 00947 } 00948 00949 00950 // Or: 00951 statement st(mydb, "select count(*) from mytable" ); 00952 int val = 0; 00953 int rc = st.execute( val ); 00954 if( ! rc_is_okay( rc ) ) { ... error ... } 00955 std::cout << "count(*) == " << val << '\n'; 00956 00957 // Writing data: 00958 statement st( mydb, "insert into mytable values(?,?)" ); 00959 st.bind( 1, "a value" ); 00960 st.bind( 2, someIntValue ); 00961 int rc = st.execute(); 00962 if( ! rc_is_okay( rc ) ) { ... error ... } 00963 00964 \endcode 00965 00966 00967 Note about copying: copying a statement object produces a 00968 shallow copy. All copies of this type will refer to the 00969 same underlying (sqlite3_stmt*) handle. The handle will be 00970 closed when the last instance of this class which points to 00971 that statement goes out of scope or is finalized. 00972 */ 00973 class statement 00974 { 00975 private: 00976 database & m_db; 00977 refcount::rcptr<sqlite3_stmt,sqlite3_stmt_finalizer> m_stmt; 00978 int m_argc; 00979 friend class cursor; 00980 public: 00981 /** 00982 Initializes a prepared statement without a 00983 query. Use prepare() to prepare the statement. 00984 */ 00985 statement( database & db ); 00986 00987 /** 00988 Initializes a statement with the given sql. 00989 Use is_prepared() to determine if the sql compiled 00990 successfully. 00991 */ 00992 statement( database & db, std::string const & sql ); 00993 /** 00994 Initializes a statement with the given sql. 00995 Use is_prepared() to determine if the sql compiled 00996 successfully. byteCount is the length of sql, in bytes. 00997 If set to -1 then strlen() is used to determine the size 00998 of sql. 00999 */ 01000 statement( database & db, char const * sql, int byteCount = -1 ); 01001 01002 /** 01003 Calls this->finalize() 01004 */ 01005 ~statement(); 01006 01007 /** 01008 (Re-)prepares an SQL statement. Return code is that 01009 of sqlite3_prepare(). If any value other than 01010 SQLITE_OK is returned then preparation failed and 01011 this object is not ready to be used. 01012 */ 01013 int prepare( std::string const & sql ); 01014 /** 01015 Same as prepare(std::string) but the len parameter 01016 specifies the length of sql. If byteCount is -1 then 01017 strlen(sql) is used to find the length. 01018 */ 01019 int prepare( char const * sql, int byteCount = -1 ); 01020 01021 #if SQ3_USE_WCHAR 01022 //statement( database & db, std::wstring const & sql ); 01023 statement( database & db, wchar_t const * sql, int byteCount = -1 ); 01024 /** 01025 */ 01026 //int prepare( std::wstring const & sql ); 01027 /** 01028 */ 01029 int prepare( sqlite3_wstring_t const sql, int byteCount = -1 ); 01030 //int execute( wchar_t * tgt, int & len ); 01031 //int execute( std::wchar & tgt ); 01032 //int bind( int index, wchar_t const * data, int len ); 01033 //int bind( int index, std::wstring const & data ); 01034 #endif // SQ3_USE_WCHAR 01035 01036 /** 01037 Binds NULL to the given placeholder index (1-based, 01038 not 0-based!). 01039 01040 Placeholders are added to SQL code with question 01041 marks, like this: 01042 01043 \code 01044 INSERT INTO MyTable(a,b) VALUES(?,?); 01045 \endcode 01046 01047 In this case we have two placeholders at indexes 1 01048 and 2. 01049 01050 Note that all bind()-related indexes are 1-based, 01051 but cursor::get() uses 0-based indexes. This 01052 inconsistency is an artefact of the sqlite3 API 01053 (and may even have a longer history). 01054 */ 01055 int bind( int index ); 01056 /** 01057 Binds data to the given placeholder index (1-based, 01058 not 0-based!). 01059 */ 01060 int bind( int index, int data ); 01061 /** 01062 Binds data to the given placeholder index (1-based, 01063 not 0-based!). 01064 */ 01065 int bind( int index, int64_t data ); 01066 /** 01067 Binds data to the given placeholder index (1-based, 01068 not 0-based!). 01069 */ 01070 int bind( int index, double data ); 01071 /** 01072 Binds data to the given placeholder index (1-based, 01073 not 0-based!). len must be the length of data, in bytes. 01074 */ 01075 int bind( int index, char const * data, int len ); 01076 /** 01077 Binds data to the given placeholder index (1-based, 01078 not 0-based!). len must be the length of data, in bytes. 01079 */ 01080 int bind( int index, void const * data, int len ); 01081 01082 /** 01083 Binds data to the given placeholder index (1-based, 01084 not 0-based!). 01085 */ 01086 int bind( int index, std::string const & data ); 01087 01088 /** 01089 Binds NULL to the given placeholder index. Note 01090 that binding by string index is notably less 01091 efficient than binding by integer index. 01092 01093 Named placeholders are embedded in SQL similar 01094 to: 01095 01096 \code 01097 INSERT INTO MyTable (a,b) VALUES(:A,:B); 01098 \endcode 01099 01100 In that string we have two named bound arguments: 01101 ":A" and ":B", at indexes 1 and 2, respectively. 01102 Note that the leading colon is considered to be part 01103 of the name. 01104 */ 01105 int bind( char const * index ); 01106 /** 01107 Binds data to the given placeholder index. See 01108 bind(char const *) for more info. 01109 */ 01110 int bind( char const * index, int data ); 01111 /** 01112 Binds data to the given placeholder index. See 01113 bind(char const *) for more info. 01114 */ 01115 int bind( char const * index, int64_t data ); 01116 /** 01117 Binds data to the given placeholder index. See 01118 bind(char const *) for more info. 01119 */ 01120 int bind( char const * index, double data ); 01121 /** 01122 Binds data to the given placeholder index. len must 01123 be the length of data, in bytes. See 01124 bind(char const *) for more info. 01125 */ 01126 int bind( char const * index, char const * data, int len ); 01127 /** 01128 Binds data to the given placeholder index. len must 01129 be the length of data, in bytes. See bind(char 01130 const *) for more info. 01131 */ 01132 int bind( char const * index, void const * data, int len ); 01133 01134 /** 01135 Binds data to the given placeholder index. See 01136 bind(char const *) for more info. 01137 */ 01138 int bind( char const * index, std::string const & data ); 01139 01140 /** 01141 Returns a cursor object ready to step over 01142 the result set from this object. 01143 01144 Note that due to low-level design details, it is 01145 unwise to mix the execute() functions and 01146 get_cursor() on the same statement. All cursors 01147 created from this statement (and all copies of 01148 those cursors) relate back to *this* statement 01149 object and when the last cursor goes out of scope 01150 the underlying prepared statement is 01151 reset. Additionally, the execute() family of 01152 functions are all actually implemented in terms of 01153 get_cursor(). Mis-interactions between a mixture of 01154 get_cursor() and execute() on the same client-side 01155 statement object cannot be ruled out. 01156 01157 See the ~cursor destructor for more details. 01158 */ 01159 cursor get_cursor(); 01160 01161 /** 01162 Assumes this object's SQL statement is a single 01163 statement. Executes that statement and returns the 01164 value from an underlying sqlite3_step() call. Thus 01165 SQLITE_ROW or SQLITE_DONE will be returned on 01166 success, depending on the underlying query. 01167 */ 01168 int execute(); 01169 01170 /** 01171 Executes this statement and saves the return value 01172 of that statement in tgt. If this function returns 01173 any other value than SQLITE_OK then tgt is not 01174 modified. Note that the value of this object's 01175 first field must be lexically convertible to tgt's 01176 type or else tgt will be set to some unspecified 01177 value. 01178 */ 01179 int execute( int & tgt ); 01180 01181 /** See execute(int&). */ 01182 int execute( int64_t & tgt ); 01183 01184 /** See execute(int&). */ 01185 int execute( double & tgt ); 01186 01187 /** See execute(int&). */ 01188 int execute( std::string & tgt ); 01189 01190 /** 01191 See execute(int&). The length of the "returned" 01192 string is saved in len (in bytes). Ownership of the 01193 string remains with sqlite3, and the client should 01194 copy it if he wants to ensure that he has it for 01195 later. The string's exact lifetime is unspecified 01196 in the sqlite3 documentation, but in theory it is 01197 valid until this statement object is finalized or 01198 a cursor object steps through the result set of this 01199 statement. 01200 */ 01201 int execute( sqlite3_text_char_t const ** tgt, int & len ); 01202 01203 /** 01204 See execute(sqlite3_text_char_t const 01205 **,int&). This is similar but is used to fetch blob 01206 data. The blob is "returned" by passinging tgt to 01207 it. The length of the blob (in bytes) is saved in 01208 len. Ownership of the blob data remains with 01209 sqlite3, and the client should copy it if he wants 01210 to ensure that he has it for later. The blob's 01211 exact lifetime is unspecified in the sqlite3 01212 documentation, but in theory it is 01213 valid until this statement object is finalized or 01214 a cursor object steps through the result set of this 01215 statement. 01216 */ 01217 int execute( void const ** tgt, int & len ); 01218 01219 /** 01220 Finizalizes the underlying prepared statement, 01221 freeing its resources. Any cursor objects created 01222 through this->get_cursor() now points to stale 01223 data and must not be used. 01224 01225 Return value is the result of calling sqlite3_finalize(), 01226 or SQLITE_ERROR if finalization cannot take place (e.g. 01227 finalize() was already called). 01228 */ 01229 int finalize(); 01230 01231 /** 01232 Use after construction to ensure that a statement 01233 was compiled. Returns true if the statement was 01234 compiled, else false. Returning false typically 01235 means that the supplied SQL has a syntax error, 01236 refers to non-existing fields, etc. 01237 */ 01238 bool is_prepared() const; 01239 01240 /** 01241 Calls sqlite3_reset() on the underlying statement 01242 handle and returns the result. 01243 */ 01244 int reset(); 01245 01246 /** Returns the column count of this prepared 01247 statement, or -1 on error. May return 0 for 01248 queries which has no return value (e.g. UPDATE). 01249 */ 01250 int colcount(); 01251 01252 /** 01253 On success, it returns the null-terminated column 01254 name of the given column. On error it returns 01255 0. The returned string is owned by sqlite3 and is 01256 not guaranteed to be valid longer than the lifetime 01257 of this statement, so copy it if you need it. 01258 */ 01259 char const * colname( int index ); 01260 01261 /** 01262 On success, assigns cn to the null-terminated column 01263 name at the given index and returns SQLITE_OK. On 01264 failure cn is not modified and some other value is 01265 returned. The column name string is not guaranteed 01266 to be valid longer than this preparation of this 01267 statement object, so copy it immediately if you will 01268 need it later. 01269 */ 01270 int colname( int index, char const ** cn ); 01271 01272 }; 01273 01274 } // namespace sq3 01275 01276 01277 #endif // s11n_net_SQ3_HPP_INCLUDED