• Main Page
  • Namespaces
  • Classes
  • Files
  • File List

sq3.hpp

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

Generated on Tue Jan 4 2011 for libsq3 by  doxygen 1.7.1