libsq3  2007.10.18
sq3.hpp
1 #ifndef s11n_net_SQ3_HPP_INCLUDED
2 #define s11n_net_SQ3_HPP_INCLUDED 1
3 
4 #include <string>
5 #include <map>
6 #include <sqlite3.h>
7 
8 #ifndef SQ3_USE_WCHAR
9 # define SQ3_USE_WCHAR 0
10 #endif
11 
12 // Enable WCHAR support when it's there. Thanks to Artem Gr <artem@bizlink.ru>
13 // for this. However, sqlite3's wchar code requires that a wchar is 16-bit, which
14 // is not the case on gcc/linux (32-bit).
15 #ifndef SQ3_USE_WCHAR
16 # ifdef _GLIBCXX_USE_WCHAR_T
17 # define SQ3_USE_WCHAR 1
18 # elif defined(UNICODE) // Windows uses this
19 # define SQ3_USE_WCHAR 1
20 # else
21 # define SQ3_USE_WCHAR 0 // default
22 # endif
23 #endif
24 
25 #if SQ3_USE_WCHAR
26 # warning SQ3_USE_WCHAR: INCOMPLETE/BROKEN code is enabled!
27 #endif
28 
29 
30 // #ifndef COUT
31 // #include <iostream>
32 // #define COUT std::cerr << "SQ3:"<<__FILE__ << ":" << std::dec<<__LINE__ << ": "
33 // #endif
34 
35 #include "refcount.hpp"
36 
37 
38 /**
39  The sq3 namespace encapsulates an OO sqlite3 API very similar to
40  the sqlite3x API, but this one uses no exception handling (i.e., it
41  doesn't throw on errors). It is intended to be an alternative for
42  platforms/projects where exceptions are not desired or not
43  available.
44 
45  Wide-char support does not currently work. (On my platform
46  (gcc/linux) wchar_t is 4 bytes and sqlite3 wants 2-bytes wide chars
47  for UTF16 text.)
48 
49  This code was written by stephan beal (stephan@s11n.net) and is
50  released into the Public Domain. It was modelled heavily after code
51  written by Cory Nelson, but was reimplemented from scratch for use
52  on a PocketPC platform where throwing exceptions wasn't allowed.
53 
54  This code's home page is:
55 
56  http://s11n.net/sqlite/
57 
58  Change history (only "significant" changes documented here):
59 
60  - 2007.02.26: integrated isnull() patch from
61  Xosé Antón Otero Ferreira <xoseotero at gmail com>
62 
63  - 2007.01.22: sq3::reader class renamed to sq3::cursor.
64  Renamed cursor::read() to cursor::step().
65 
66  - 2007.01.27: Added the cursor::get(std::string const&,...)
67  family of functions to enable get-by-string-index. Added
68  database::clear().
69 
70  - 2007.01.28: rcptr<> shared pointer class introduced to enable
71  reasonable copy semantics for the major underlying data structures.
72  sq3::database and sq3::statement can now be shallowly copied.
73 
74  - 2007.02.14: added statement::bind(char const *,...) functions.
75 
76 */
77 namespace sq3 {
78 
79  /**
80  The type used for signed 64-bit integer operations.
81  */
82  typedef sqlite_int64 int64_t;
83  /**
84  The type used for unsigned 64-bit integer operations.
85  */
86  typedef sqlite_uint64 uint64_t;
87 
88 // /**
89 // The published result codes from the sqlite3 API.
90 // */
91 // enum Sqlite3ResultCodes {
92 // ResultOK = SQLITE_OK,
93 // ResultERROR = SQLITE_ERROR,
94 // ResultINTERNAL = SQLITE_INTERNAL,
95 // ResultPERM = SQLITE_PERM,
96 // ResultABORT = SQLITE_ABORT,
97 // ResultBUSY = SQLITE_BUSY,
98 // ResultLOCKED = SQLITE_LOCKED,
99 // ResultNOMEM = SQLITE_NOMEM,
100 // ResultREADONLY = SQLITE_READONLY,
101 // ResultINTERRUPT = SQLITE_INTERRUPT,
102 // ResultIOERR = SQLITE_IOERR,
103 // ResultCORRUPT = SQLITE_CORRUPT,
104 // ResultNOTFOUND = SQLITE_NOTFOUND,
105 // ResultFULL = SQLITE_FULL,
106 // ResultCANTOPEN = SQLITE_CANTOPEN,
107 // ResultPROTOCOL = SQLITE_PROTOCOL,
108 // ResultEMPTY = SQLITE_EMPTY,
109 // ResultSCHEMA = SQLITE_SCHEMA,
110 // ResultTOOBIG = SQLITE_TOOBIG,
111 // ResultCONSTRAINT = SQLITE_CONSTRAINT,
112 // ResultMISMATCH = SQLITE_MISMATCH,
113 // ResultMISUSE = SQLITE_MISUSE,
114 // ResultNOLFS = SQLITE_NOLFS,
115 // ResultAUTH = SQLITE_AUTH,
116 // ResultROW = SQLITE_ROW,
117 // ResultDONE = SQLITE_DONE
118 // };
119 
120  /**
121  A char type used by some of the sqlite3 API to represent
122  text data. This is really annoying, but sqlite3's API
123  explicitely uses UNSIGNED char arrays for a couple of
124  strings, while using signed char arrays for almost
125  everything else.
126  */
127  typedef char unsigned sqlite3_text_char_t;
128 
129 
130  class statement; // unfortunate fwd decl
131 
132  /**
133  A specialized dtor to close sqlite3 handles, for use
134  with refcount::rcptr<sqlite3,sqlite3_finalizer >.
135  */
137  {
138  /**
139  Calls sqlite3_close(t) and assigns t to 0.
140  */
141  void operator()( sqlite3 * & t );
142  };
143 
144  /**
145  A specialized dtor to reset (not close)
146  sq3::statement objects, for use with
147  refcount::rcptr<sqlite3,statement_reset_finalizer>.
148  */
150  {
151  /**
152  Calls t->reset() and assigns t to 0.
153  */
154  void operator()( ::sq3::statement * & t );
155  };
156 
157  /**
158  A specialized dtor to call reset sqlite3_stmt
159  handles(), for use with
160  refcount::rcptr<sqlite3,sqlite3_stmt_reset_finalizer>.
161  */
163  {
164  /**
165  Calls sqlite3_reset(t) and assigns t to 0.
166  */
167  void operator()( sqlite3_stmt * & t );
168  };
169 
170 
171  /**
172  A specialized dtor to finalize sqlite3_stmt
173  handles, for use with
174  refcount::rcptr<sqlite3,sqlite3_stmt_finalizer>.
175  */
177  {
178  /**
179  Calls sqlite3_finalize(t) and assigns t to 0.
180  */
181  void operator()( sqlite3_stmt * & t );
182  };
183 
184  /**
185  rc_is_okay() is an easy way to check if rc is one of
186  SQLITE_OK, SQLITE_ROW, or SQLITE_DONE. This function
187  returns true if rc is one of those values, else false.
188  (Code which accepts arbitrary SQL from a user often has to
189  accept any of those three result codes as success.)
190  */
191  bool rc_is_okay( int rc );
192 
193 
194  /**
195  Encapsulates a connection to an sqlite database.
196 
197  This type is virtual/subclassable so that clients can add
198  initialization routines to all of their databases, such as
199  adding sqlite-builtin functions and collating sequences.
200  Simply do such initializations in your subclass ctors.
201 
202  A note on the lack of proper constness for much of this
203  API... Potentially, in the face of triggers, multiple
204  threads, and whatnot, it is not generically possible to
205  100% ensure that any given SQL statement does *not* modify
206  the database in at least *some* way. To reflect this
207  underlying state of flux, "it was decided" (it really was)
208  that very little, if any, of the members of this class
209  would be const. Only those which could 100% guaranty proper
210  constness.
211 
212  Notes about copying:
213 
214  Copying a db object is actually shallow copying. All copies
215  of this type will refer to the same underlying (sqlite3*)
216  db handle until/unless:
217 
218  - When open() is called, the object on which it was
219  called may separate itself from the rcptr relationship with
220  the older db handle and start a new one.
221 
222  - close(false) affects only the calling db. When the
223  reference count for the (sqlite3*) handle drops to zero,
224  then sqlite3_close() will be called. close(true) closes the
225  db handle immediately, affecting all copies of this object.
226 
227  - When take_handle() is
228  called then ownership of the underlying db handle is removed from ALL
229  copies of this object. They will still refer to the handle, but using it
230  is not legal.
231  */
232  class database
233  {
234  private:
235  friend class statement;
237  std::string m_name;
238  public:
239 
240  /**
241  The low-level handle to the sqlite db.
242  NEVER close this handle. It is permissible
243  to use it to run queries, add functions
244  to the db, etc.
245 
246  This object retains ownership of the returned
247  handle.
248  */
249  sqlite3 * handle() const;
250  /**
251  Creates an unopened database. Use open()
252  to open a file or take_handle() to transfer
253  an existing db handle to this object.
254  */
255  database();
256 
257  /**
258  dbh is assumed to be an opened, valid db handle.
259  This function transfers ownership of dbh to this
260  object. Specifically, dbh will be closed when the
261  last database with the same db handle goes out of
262  scope or is closed. The name is simply informative,
263  and may or may not be the same actually used for
264  opening dbh.
265 
266  Note that this function does not call the protected
267  this->on_open() because, quite frankly, i'm not yet
268  sure if it really makes sense to do so.
269  */
270  void take_handle( sqlite3 * dbh, std::string const & name = "" );
271 
272  /**
273  Transfers ownership of this->handle() to the
274  caller. ALL copies of this object which point to
275  the handle, except for this copy, still have a
276  pointer to that handle but will not call the handle
277  dtor when they go out of scope or are closed.
278  */
279  sqlite3 * take_handle();
280 
281  /**
282  Opens/creates the given db file. Use is_open() to
283  see if the opening worked.
284 
285  Subclasses should not call this from a ctor
286  initialization list because this function may
287  eventually call on_open(), which is virtual, but
288  the subclass part of the class may not be in place
289  to catch that virtual call. So subclasses should
290  initialize with the no-arg parent class ctor
291  and should then call open(filename) themselves.
292  */
293  explicit database( std::string const & filename );
294 
295  /**
296  Closes this db.
297  */
298  virtual ~database();
299 
300  /**
301  Returns true if this db is opened.
302  Does not detect errors such as opening
303  a non-db file.
304  */
305  bool is_open() const;
306 
307  /**
308  Returns the name of the db file.
309  */
310  std::string name() const;
311 
312  /**
313  Returns the last error message from
314  sqlite, or an empty string if this object
315  is not opened. Unfortunately, sqlite3
316  returns the literal string "not an error"
317  on non-errors, instead of returning an empty
318  string.
319  */
320  std::string errormsg() const;
321 
322  /**
323  Creates/opens the given db file.
324 
325  The flags parameter is only used if this code is
326  compiled against sqlite3 >= 3.5.1, and can
327  theoretically take any values defined in the
328  SQLITE_OPEN_xxx family of macros. The sqlite3
329  documentation only describes the use of
330  SQLITE_OPEN_READONLY, SQLITE_OPEN_READWRITE, and
331  SQLITE_OPEN_CREATE, thus other values may nor may
332  not work. If (0==flags) then
333  (SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE) is
334  assumed. See sqlite3_open_v2() for the exact
335  semantics.
336 
337  On success it returns SQLITE_OK, or some other
338  value on error.
339 
340  Calling open() will implicitly call close() on any
341  existing connection. If that close() fails then
342  this open() will also fail. If the open() succeeds,
343  it does NOT affect other (shallow) copies of this
344  object: they will still refer to the older db
345  handle.
346 
347  Note that sqlite3 supports the special db name
348  ":memory:" to represent an in-memory database. Such
349  databases cannot be saved directly to disk and are
350  lost when this object closes the db.
351 
352  Windows users beware: according to the sqlite3
353  documentation, the db name MUST be in UTF8 format,
354  regardless of the current codepage used by Windows,
355  and it is the caller's responsibility to perform
356  any conversion, if needed.
357 
358  Internal notes:
359 
360  If the underlying sqlite3_open() succeeds, the
361  protected member on_open() in called. If it returns
362  a value other than SQLITE_OK then this->close() is
363  called and the value returned from on_open() is
364  returned from this function.
365 
366  Subclasses which override this function and do not
367  want to call the base implementation should call
368  on_open() when done to allow subclasses to
369  initialize the database if they like. If on_open()
370  fails then this->close() should be called to free
371  up the resources and mark this object as unopened.
372 
373  The flags argument was added 20071018, suggested by
374  Joaquim Campos Salles (Joaquim.Salles at
375  br.unisys.com).
376 
377  */
378  virtual int open( char const *, long flags = 0 );
379 
380  /**
381  Functionally identical to open(char const*,long).
382  This function calls that one, so subclasses wishing
383  to change open()'s behaviour need only reimplement
384  that one.
385  */
386  int open( std::string const &, long flags = 0 );
387 
388  /**
389  "Closes" this db. That actually means that
390  it queues it to be closed when the last
391  database object which is using that db handle
392  closes or goes out of scope.
393 
394  The force parameter changes the handling of those
395  sqlite3_close():
396 
397  If force is false, this function always returns
398  SQLITE_OK unless this object is already closed, in
399  which case SQLITE_ERROR is returned but can almost
400  certainly be safely ignored. Unfortunately, due to
401  the asynchronous nature of this operation, we can't
402  return the value from the actual sqlite3_close()
403  call (if any) when force is set to false.
404 
405  If force is true then the internal reference
406  counting is anulled and the db handle is closed
407  immediately. This affects all copies of this
408  object, so use with care (but use if you must). In
409  this case, the value of sqlite3_close() is
410  returned, but the the exact state of the underlying
411  database handle is ambiguously defined in the
412  sqlite3 docs. So... if that happens then the
413  underlying db handle is assumed to be invalid, since
414  the "test*.c" files which come with sqlite3 seem
415  to treat it as such.
416  */
417  int close( bool force = false );
418 
419 
420 
421  /**
422  Returns the rowid of the most recently inserted row
423  on this db.
424  */
425  int64_t insertid();
426 
427  /**
428  Returns the number of database rows that were
429  changed (or inserted or deleted) by the most recently
430  completed INSERT, UPDATE, or DELETE statement.
431 
432  SQLite implements the command "DELETE FROM table"
433  without a WHERE clause by dropping and recreating
434  the table. To get an accurate count of the number
435  of rows deleted, use "DELETE FROM table WHERE 1"
436  instead.
437  */
438  int changes();
439 
440  /**
441  See sqlite3_busy_timeout().
442  */
443  int setbusytimeout( int ms );
444 
445  /**
446  Functionally identical to execute(char const *).
447  */
448  int execute(const std::string &sql);
449 
450  /**
451  Executes a statement which is assumed to have
452  a single step and a void result. Returned result
453  is that of an underlying call to sqlite3_step(),
454  which means that SQLITE_DONE or SQLITE_ROW evaluate
455  to success.
456  */
457  int execute(char const * sql);
458 
459  /**
460  Executes a statement which is assumed to have
461  a single result step and an integer result.
462  On success, tgt will be set to the query's
463  value. Typically one of SQLITE_ROW or SQLITE_DONE
464  are returned on success.
465  */
466  int execute(std::string const & sql, int & tgt);
467 
468  /**
469  See execute( std::string, int & );
470  */
471  int execute(char const * sql, int & tgt);
472 
473  /**
474  See execute( std::string, int & );
475  */
476  int execute(char const * sql, int64_t & tgt);
477 
478  /**
479  See execute( std::string, int & );
480  */
481  int execute(std::string const & sql, int64_t & tgt);
482 
483  /**
484  See execute( std::string, int & );
485  */
486  int execute(char const * sql, double & tgt);
487  /**
488  See execute( std::string, int & );
489  */
490  int execute(std::string const & sql, double & tgt);
491 
492  /**
493  See execute( std::string, int & );
494  */
495  int execute(char const * sql, std::string & tgt);
496  /**
497  See execute( std::string, int & );
498  */
499  int execute(std::string const & sql, std::string & tgt);
500 
501  /**
502  See execute( std::string, int & );
503 
504  sql is executed and the string result is written to
505  tgt and the length of the result string (in bytes)
506  is written to len. The text bytes are owned by
507  sqlite and will likely become invalid on the next
508  db cursor operation, so copy them if you need them.
509 
510  Returns the result of stepping through a result
511  set, which is typically one of SQLITE_ROW or
512  SQLITE_DONE.
513  */
514  int execute(char const * sql, sqlite3_text_char_t const ** tgt, int & len );
515  /**
516  See execute( char const *, sqlite3_text_char_t const **, int & ).
517  This function is identical.
518  */
519  int execute(std::string const & sql, sqlite3_text_char_t const ** tgt, int & len );
520 
521 
522  /**
523  See execute( char const *, sqlite3_text_char_t **, int & ).
524  This function is identical except that tgt is a
525  void pointer.
526  */
527  int execute(std::string const & sql, void const ** tgt, int & sz );
528  /**
529  See execute( char const *, sqlite3_text_char_t **, int & ).
530  This function is identical except that tgt is a
531  void pointer.
532  */
533  int execute(char const * sql, void const ** tgt, int & sz );
534 
535 
536  /**
537  Executes the given query, calling the given callback function for each
538  row of the result set. The data pointer is passed on as-is to the callback.
539  Any error string is written to errmsg.
540 
541  Return value is that of an underlying sqlite3_exec() call.
542  */
543  int execute( std::string const & sql, sqlite3_callback callback, void * data, std::string & errmsg );
544  /**
545  Identical to the execute(std::string,sqlite3_callback,void*,std::string&).
546  */
547  int execute( char const * sql, sqlite3_callback callback, void * data, std::string & errmsg );
548 
549  /**
550  Convenience overload which has a default data value
551  of 0 and ignores any error string passed back by
552  sqlite3_exec().
553  */
554  int execute( std::string const & sql, sqlite3_callback callback, void * data = 0 );
555  /**
556  Convenience overload which has a default data value
557  of 0 and ignores any error string passed back by
558  sqlite3_exec().
559  */
560  int execute( char const * sql, sqlite3_callback callback, void * data = 0 );
561 
562  /**
563  This is a convenience wrapper for execute( "pragma ..." ).
564  Return value is that of the underlying execute() call.
565 
566  code should be a pragma key or key/value string, such as
567  "temp_store=MEMORY" or "synchronous=OFF"
568  */
569  int pragma( char const * code );
570 
571  /**
572  Convenience wrapper around execute("vacuum"). The
573  vacuum operation attempts to free up any unused
574  disk space in the database.
575  */
576  int vacuum();
577 
578 #if SQ3_USE_WCHAR
579  int open( wchar_t const * dbname );
580  int open( std::wstring const & dbname );
581 // int execute(char const * sql, std::wstring & tgt);
582 // int execute(std::string const & sql, std::wstring & tgt);
583 #endif // SQ3_USE_WCHAR
584 
585  /**
586  Looks through sqlite_master for a list of views,
587  triggers, and tables, and drops them all (in that
588  order). Subclasses are welcomed to reimplement it
589  to do less destructive cleansing, such as just
590  dropping data from a certain table.
591 
592  Returns SQLITE_OK on success.
593 
594  If you need to free up the newly-freed space, be
595  sure to call vacuum(), or else the file size may
596  not actually shrink.
597 
598  Also remember that any code which is expecting data
599  to be in this database will not work after this function
600  is done!
601  */
602  virtual int clear();
603 
604  protected:
605  /**
606  This function is called when open() succeeds. The
607  default implementation does nothing and always
608  returns SQLITE_OK, but subclasses may wish to do
609  something here. If this function returns any value
610  other than SQLITE_OK then this->close() is called
611  before open() returns.
612 
613  */
614  virtual int on_open();
615 
616 
617  };
618 
619  /**
620  This type represents a transaction block in an SQL
621  session. Note that as of this writing, sqlite3 does
622  not support nested transactions.
623  */
625  {
626  private:
627  transaction & operator=( transaction const & ); // unimplemented
628  transaction( transaction const & ); // unimplemented
629  database & m_db;
630  bool m_intrans;
631  public:
632  /**
633  Creates a transaction for the given db. If start
634  is true (the default) then this->begin() is called.
635  */
636  transaction( database & db, bool start = true );
637  /**
638  Calls this->rollback()
639  */
640  ~transaction();
641  /**
642  Starts the transaction. Return value is the result
643  of calling sqlite3_exec(db,"begin;"). If SQLITE_OK
644  is returned then this object considers itself to be
645  active, such that calling commit() and rollback()
646  should be able to succeed.
647  */
648  int begin();
649  /**
650  Commits the active transaction. Returns SQLITE_OK
651  on success and any other value on error. Return
652  value is that of the underlying sqlite3_exec()
653  call.
654 
655  After calling this function, this object is considered to
656  NOT be in a transaction unless SQLITE_BUSY is returned.
657  If that happens, the transaction is still open and commit()
658  should be called later to close the transaction.
659  */
660  int commit();
661  /**
662  Initiates a rollback and returns the result of
663  executing a rollback command. If this object is not
664  active (begin() has not been called) then this
665  function returns SQLITE_ERROR and has no
666  side-effects. Return value is that of the
667  underlying sqlite3_exec() call.
668  */
669  int rollback();
670  };
671 
672  /**
673  This type is for stepping through a db query result.
674  Clients do not normally create cursors directly, but
675  through the statement::get_cursor()
676  function.
677 
678  cursor objects are copied shallowly - each copy points
679  back to a single original statement object. That statement
680  is reset when the last of these copies goes out of scope
681  or is finalized.
682 
683  */
684  class cursor
685  {
686  private:
687  //friend class statement;
689  /**
690  And internal helper type for fetching data sets by
691  string lookups instead of integer indexes.
692  */
693  typedef std::map<std::string,int> NameToIndexMap;
694  NameToIndexMap * m_cn; // maps column names to column indexes for use with get().
695 
696 
697  /**
698  If rhs is this object, this function does nothing,
699  otherwise it copies rhs.
700  */
701  void copy( cursor const & rhs );
702  /**
703  Hashes the colname-to-index mapping.
704  Returns:
705 
706  -1: result was cached before and will stay cached until
707  this object expires.
708 
709  0: this result set has no columns. This is likely an error.
710 
711  1 or higher: the number of column indexes.
712 
713  As a side-effect, this function initializes this->m_cn
714  if that object has not already been created.
715 
716  If the return value is anything other than this->colcount()
717  then an error occurred during the collection of the
718  column names. This is exceedingly unlikely to happen.
719  */
720  int index_colnames();
721 
722  public:
723  /**
724  Creates an empty cursor, whose only valid use is to
725  assign it from another cursor.
726  */
727  cursor();
728  /**
729  Copies rhs. This object and rhs now point to the
730  same underlying sqlite3 structures, and modifying
731  one of these objects modifies the other, in effect.
732  */
733  cursor( cursor const & rhs );
734  /**
735  See the copy ctor.
736  */
737  cursor & operator=( cursor const & );
738 
739  /**
740  Identical to calling st.get_cursor().
741  */
742  cursor( statement & st );
743 
744  /**
745  A curious side-effect which one needs to be
746  aware of but very rarely is an issue:
747 
748  When cursors are created they *always* have an
749  associated prepared statement. When the last cursor
750  with a reference to that same statement goes out of
751  scope or is close()ed then the underlying statement
752  object is reset(). That sounds curious, but is
753  indeed the desired behaviour for this class, and
754  breaks some common client code constructs when the
755  underlying statement is not automatically reset.
756  Without this "feature", client code could not run,
757  for exaple, myStatement.execute( myInt ), two times
758  in a row because the second time around the
759  statement would be at its end point and need to be
760  manually reset. Thus client code should never mix
761  the use of a cursor object and the non-cursor
762  statement API on the same statement.
763 
764  See statement::close() for more details.
765  */
766  ~cursor();
767 
768  /**
769  Uses sqlite3_step() to step through this object's
770  data set by one step. Returns the result of
771  sqlite3_step(), which means: SQLITE_ROW if it
772  read, SQLITE_DONE at the end of a data set,
773  and any other value on error.
774  */
775  int step();
776  /**
777  This is functionally the same as calling reset on
778  the underlying prepared statement object to which
779  this cursor is tied. Use with care, as it affects
780  all cursors which point to the same statement
781  object.
782 
783  returns SQLITE_OK on success, else another sqlite3
784  error code.
785  */
786  int reset();
787 
788  /**
789  "Disconnects" this object from the underlying
790  result set, making this object useless for anything
791  but as the target of an assignment.
792 
793  It is normally not necessary to call this function,
794  but it may be in some special cases.
795  */
796  void close();
797 
798  /**
799  Returns the column count of the underlying
800  prepared statement. May return 0 for queries which
801  has no return value (e.g. UPDATE). Returns -1
802  on error.
803  */
804  int colcount();
805 
806  /**
807  If column index (0-based) is in bounds then this
808  function check if the value of the given column index
809  is NULL and assigns tgt to the result of this
810  comprobation. On success, SQLITE_OK is returned.
811  On any other return value, tgt is not modifed.
812  */
813  int isnull( int index, bool & tgt );
814 
815  /**
816  If column index (0-based) is in bounds then this
817  function assigns tgt to the value of the given
818  column index. On success, SQLITE_OK is returned.
819  On any other return value, tgt is not modifed.
820  */
821  int get( int index, int & tgt );
822  /**
823  See get(int,int&).
824  */
825  int get( int index, int64_t & tgt );
826  /**
827  See get(int,int&).
828  */
829  int get( int index, double & tgt );
830  /**
831  See get(int,int&).
832  */
833  int get( int index, std::string & tgt );
834  /**
835  If index (0-based) is in bounds, this function
836  gets the (char unsigned const *) data at that
837  column index and assigns tgt to that value
838  and sz to the size of the data.
839 
840  tgt is written to by this func but ownership
841  of the underlying data remains with sqlite.
842  That is, the caller does not need to free
843  the memory pointed to by tgt, but may need to
844  copy it if he wants to use it later.
845  */
846  int get( int index, sqlite3_text_char_t const ** tgt, int & sz );
847  /**
848  See get(int,char const **, int&). Only the tgt
849  type is different.
850  */
851  int get( int index, void const ** tgt, int & sz );
852 
853 
854  /**
855  This is fundamentally identical to get(int,int &)
856  except that the key type is a string, which must
857  exactly (case-sensitively) match a column name from
858  this result set. On success, SQLITE_OK is returned
859  and tgt is modified. On error, some other code is
860  returned and tgt is not modified.
861 
862  Note that fetching by string index is much less
863  efficient than looking up by integer index, but of
864  course also a lot more convenient. If you're
865  looking for the most speed, go with
866  get(int,...). If you're looking for flexibility and
867  convenience, at the cost of a few extra cyles and a
868  tiny bit of extra memory usage per cursor, then use
869  string-based keys.
870  */
871  int get( std::string const & key, int & tgt );
872  /**
873  See get(std::string const &,int&).
874  */
875  int get( std::string const & key, int64_t & tgt );
876  /**
877  See get(std::string const &,int&).
878  */
879  int get( std::string const & key, double & tgt );
880  /**
881  See get(std::string const &,int&).
882  */
883  int get( std::string const & key, std::string & tgt );
884  /**
885  If indexis in bounds, this function gets the (char
886  unsigned const *) data at that column index and
887  assigns tgt to that value and sz to the size of the
888  data.
889 
890  tgt is written to by this func but ownership
891  of the underlying data remains with sqlite.
892  That is, the caller does not need to free
893  the memory pointed to by tgt, but may need to
894  copy it if he wants to use it later.
895  */
896  int get( std::string const & key, sqlite3_text_char_t const ** tgt, int & sz );
897  /**
898  See get(std::string const &,char const **, int&). Only the tgt
899  type is different.
900  */
901  int get( std::string const & key, void const ** tgt, int & sz );
902 
903 
904  /**
905  Sets str to the column name as the given index
906  (0-based). Returns SQLITE_OK on success, else
907  SQLITE_ERROR and str is not modified.
908  */
909  int colname( int index, std::string & str );
910  /**
911  Points str to the nul-terminated column name at the
912  given index (0-based), or 0 on error. This
913  overload avoids an extra copy of the column name,
914  but sqlite owns the string and clients will need to
915  make a copy of it if they want to continue to use
916  it beyond the lifetime of this object's underlying
917  prepared statement.
918 
919  Returns SQLITE_OK if str is set, otherwise
920  SQLITE_ERROR.
921  */
922  int colname( int index, char const ** str );
923  //int colname( int index, std::wstring & );
924  };
925 
926 
927  /**
928  This class represents a prepared database statement.
929 
930  statement objects are copied shallowly - each copy points
931  back to a single original sqlite3_stmt object. That sqlite3_stmt
932  is finalized with the last of these copies goes out of scope
933  or is finalized.
934 
935  Sample usage:
936 \code
937  // Reading data:
938  statement st(mydb, "select * from sqlite_master");
939  if( ! st.is_prepared() )
940  {
941  ... error ...
942  }
943  cursor cur( st.get_cursor() );
944  while( SQLITE_ROW == cur.step() )
945  {
946  ... do something with each row ...
947  }
948 
949 
950  // Or:
951  statement st(mydb, "select count(*) from mytable" );
952  int val = 0;
953  int rc = st.execute( val );
954  if( ! rc_is_okay( rc ) ) { ... error ... }
955  std::cout << "count(*) == " << val << '\n';
956 
957  // Writing data:
958  statement st( mydb, "insert into mytable values(?,?)" );
959  st.bind( 1, "a value" );
960  st.bind( 2, someIntValue );
961  int rc = st.execute();
962  if( ! rc_is_okay( rc ) ) { ... error ... }
963 
964 \endcode
965 
966 
967  Note about copying: copying a statement object produces a
968  shallow copy. All copies of this type will refer to the
969  same underlying (sqlite3_stmt*) handle. The handle will be
970  closed when the last instance of this class which points to
971  that statement goes out of scope or is finalized.
972  */
973  class statement
974  {
975  private:
976  database & m_db;
978  int m_argc;
979  friend class cursor;
980  public:
981  /**
982  Initializes a prepared statement without a
983  query. Use prepare() to prepare the statement.
984  */
985  statement( database & db );
986 
987  /**
988  Initializes a statement with the given sql.
989  Use is_prepared() to determine if the sql compiled
990  successfully.
991  */
992  statement( database & db, std::string const & sql );
993  /**
994  Initializes a statement with the given sql.
995  Use is_prepared() to determine if the sql compiled
996  successfully. byteCount is the length of sql, in bytes.
997  If set to -1 then strlen() is used to determine the size
998  of sql.
999  */
1000  statement( database & db, char const * sql, int byteCount = -1 );
1001 
1002  /**
1003  Calls this->finalize()
1004  */
1005  ~statement();
1006 
1007  /**
1008  (Re-)prepares an SQL statement. Return code is that
1009  of sqlite3_prepare(). If any value other than
1010  SQLITE_OK is returned then preparation failed and
1011  this object is not ready to be used.
1012  */
1013  int prepare( std::string const & sql );
1014  /**
1015  Same as prepare(std::string) but the len parameter
1016  specifies the length of sql. If byteCount is -1 then
1017  strlen(sql) is used to find the length.
1018  */
1019  int prepare( char const * sql, int byteCount = -1 );
1020 
1021 #if SQ3_USE_WCHAR
1022  //statement( database & db, std::wstring const & sql );
1023  statement( database & db, wchar_t const * sql, int byteCount = -1 );
1024  /**
1025  */
1026  //int prepare( std::wstring const & sql );
1027  /**
1028  */
1029  int prepare( sqlite3_wstring_t const sql, int byteCount = -1 );
1030  //int execute( wchar_t * tgt, int & len );
1031  //int execute( std::wchar & tgt );
1032  //int bind( int index, wchar_t const * data, int len );
1033  //int bind( int index, std::wstring const & data );
1034 #endif // SQ3_USE_WCHAR
1035 
1036  /**
1037  Binds NULL to the given placeholder index (1-based,
1038  not 0-based!).
1039 
1040  Placeholders are added to SQL code with question
1041  marks, like this:
1042 
1043  \code
1044  INSERT INTO MyTable(a,b) VALUES(?,?);
1045  \endcode
1046 
1047  In this case we have two placeholders at indexes 1
1048  and 2.
1049 
1050  Note that all bind()-related indexes are 1-based,
1051  but cursor::get() uses 0-based indexes. This
1052  inconsistency is an artefact of the sqlite3 API
1053  (and may even have a longer history).
1054  */
1055  int bind( int index );
1056  /**
1057  Binds data to the given placeholder index (1-based,
1058  not 0-based!).
1059  */
1060  int bind( int index, int data );
1061  /**
1062  Binds data to the given placeholder index (1-based,
1063  not 0-based!).
1064  */
1065  int bind( int index, int64_t data );
1066  /**
1067  Binds data to the given placeholder index (1-based,
1068  not 0-based!).
1069  */
1070  int bind( int index, double data );
1071  /**
1072  Binds data to the given placeholder index (1-based,
1073  not 0-based!). len must be the length of data, in bytes.
1074  */
1075  int bind( int index, char const * data, int len );
1076  /**
1077  Binds data to the given placeholder index (1-based,
1078  not 0-based!). len must be the length of data, in bytes.
1079  */
1080  int bind( int index, void const * data, int len );
1081 
1082  /**
1083  Binds data to the given placeholder index (1-based,
1084  not 0-based!).
1085  */
1086  int bind( int index, std::string const & data );
1087 
1088  /**
1089  Binds NULL to the given placeholder index. Note
1090  that binding by string index is notably less
1091  efficient than binding by integer index.
1092 
1093  Named placeholders are embedded in SQL similar
1094  to:
1095 
1096  \code
1097  INSERT INTO MyTable (a,b) VALUES(:A,:B);
1098  \endcode
1099 
1100  In that string we have two named bound arguments:
1101  ":A" and ":B", at indexes 1 and 2, respectively.
1102  Note that the leading colon is considered to be part
1103  of the name.
1104  */
1105  int bind( char const * index );
1106  /**
1107  Binds data to the given placeholder index. See
1108  bind(char const *) for more info.
1109  */
1110  int bind( char const * index, int data );
1111  /**
1112  Binds data to the given placeholder index. See
1113  bind(char const *) for more info.
1114  */
1115  int bind( char const * index, int64_t data );
1116  /**
1117  Binds data to the given placeholder index. See
1118  bind(char const *) for more info.
1119  */
1120  int bind( char const * index, double data );
1121  /**
1122  Binds data to the given placeholder index. len must
1123  be the length of data, in bytes. See
1124  bind(char const *) for more info.
1125  */
1126  int bind( char const * index, char const * data, int len );
1127  /**
1128  Binds data to the given placeholder index. len must
1129  be the length of data, in bytes. See bind(char
1130  const *) for more info.
1131  */
1132  int bind( char const * index, void const * data, int len );
1133 
1134  /**
1135  Binds data to the given placeholder index. See
1136  bind(char const *) for more info.
1137  */
1138  int bind( char const * index, std::string const & data );
1139 
1140  /**
1141  Returns a cursor object ready to step over
1142  the result set from this object.
1143 
1144  Note that due to low-level design details, it is
1145  unwise to mix the execute() functions and
1146  get_cursor() on the same statement. All cursors
1147  created from this statement (and all copies of
1148  those cursors) relate back to *this* statement
1149  object and when the last cursor goes out of scope
1150  the underlying prepared statement is
1151  reset. Additionally, the execute() family of
1152  functions are all actually implemented in terms of
1153  get_cursor(). Mis-interactions between a mixture of
1154  get_cursor() and execute() on the same client-side
1155  statement object cannot be ruled out.
1156 
1157  See the ~cursor destructor for more details.
1158  */
1159  cursor get_cursor();
1160 
1161  /**
1162  Assumes this object's SQL statement is a single
1163  statement. Executes that statement and returns the
1164  value from an underlying sqlite3_step() call. Thus
1165  SQLITE_ROW or SQLITE_DONE will be returned on
1166  success, depending on the underlying query.
1167  */
1168  int execute();
1169 
1170  /**
1171  Executes this statement and saves the return value
1172  of that statement in tgt. If this function returns
1173  any other value than SQLITE_OK then tgt is not
1174  modified. Note that the value of this object's
1175  first field must be lexically convertible to tgt's
1176  type or else tgt will be set to some unspecified
1177  value.
1178  */
1179  int execute( int & tgt );
1180 
1181  /** See execute(int&). */
1182  int execute( int64_t & tgt );
1183 
1184  /** See execute(int&). */
1185  int execute( double & tgt );
1186 
1187  /** See execute(int&). */
1188  int execute( std::string & tgt );
1189 
1190  /**
1191  See execute(int&). The length of the "returned"
1192  string is saved in len (in bytes). Ownership of the
1193  string remains with sqlite3, and the client should
1194  copy it if he wants to ensure that he has it for
1195  later. The string's exact lifetime is unspecified
1196  in the sqlite3 documentation, but in theory it is
1197  valid until this statement object is finalized or
1198  a cursor object steps through the result set of this
1199  statement.
1200  */
1201  int execute( sqlite3_text_char_t const ** tgt, int & len );
1202 
1203  /**
1204  See execute(sqlite3_text_char_t const
1205  **,int&). This is similar but is used to fetch blob
1206  data. The blob is "returned" by passinging tgt to
1207  it. The length of the blob (in bytes) is saved in
1208  len. Ownership of the blob data remains with
1209  sqlite3, and the client should copy it if he wants
1210  to ensure that he has it for later. The blob's
1211  exact lifetime is unspecified in the sqlite3
1212  documentation, but in theory it is
1213  valid until this statement object is finalized or
1214  a cursor object steps through the result set of this
1215  statement.
1216  */
1217  int execute( void const ** tgt, int & len );
1218 
1219  /**
1220  Finizalizes the underlying prepared statement,
1221  freeing its resources. Any cursor objects created
1222  through this->get_cursor() now points to stale
1223  data and must not be used.
1224 
1225  Return value is the result of calling sqlite3_finalize(),
1226  or SQLITE_ERROR if finalization cannot take place (e.g.
1227  finalize() was already called).
1228  */
1229  int finalize();
1230 
1231  /**
1232  Use after construction to ensure that a statement
1233  was compiled. Returns true if the statement was
1234  compiled, else false. Returning false typically
1235  means that the supplied SQL has a syntax error,
1236  refers to non-existing fields, etc.
1237  */
1238  bool is_prepared() const;
1239 
1240  /**
1241  Calls sqlite3_reset() on the underlying statement
1242  handle and returns the result.
1243  */
1244  int reset();
1245 
1246  /** Returns the column count of this prepared
1247  statement, or -1 on error. May return 0 for
1248  queries which has no return value (e.g. UPDATE).
1249  */
1250  int colcount();
1251 
1252  /**
1253  On success, it returns the null-terminated column
1254  name of the given column. On error it returns
1255  0. The returned string is owned by sqlite3 and is
1256  not guaranteed to be valid longer than the lifetime
1257  of this statement, so copy it if you need it.
1258  */
1259  char const * colname( int index );
1260 
1261  /**
1262  On success, assigns cn to the null-terminated column
1263  name at the given index and returns SQLITE_OK. On
1264  failure cn is not modified and some other value is
1265  returned. The column name string is not guaranteed
1266  to be valid longer than this preparation of this
1267  statement object, so copy it immediately if you will
1268  need it later.
1269  */
1270  int colname( int index, char const ** cn );
1271 
1272  };
1273 
1274 } // namespace sq3
1275 
1276 
1277 #endif // s11n_net_SQ3_HPP_INCLUDED