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*/
77namespace 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 */
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 */
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 */
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 */
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
This type is for stepping through a db query result.
Definition: sq3.hpp:685
int isnull(int index, bool &tgt)
If column index (0-based) is in bounds then this function check if the value of the given column inde...
Definition: sq3.cpp:406
int reset()
This is functionally the same as calling reset on the underlying prepared statement object to which t...
Definition: sq3.cpp:377
void close()
"Disconnects" this object from the underlying result set, making this object useless for anything but...
Definition: sq3.cpp:385
int step()
Uses sqlite3_step() to step through this object's data set by one step.
Definition: sq3.cpp:370
int get(int index, int &tgt)
If column index (0-based) is in bounds then this function assigns tgt to the value of the given colum...
Definition: sq3.cpp:413
~cursor()
A curious side-effect which one needs to be aware of but very rarely is an issue:
Definition: sq3.cpp:365
cursor()
Creates an empty cursor, whose only valid use is to assign it from another cursor.
Definition: sq3.cpp:334
int colcount()
Returns the column count of the underlying prepared statement.
Definition: sq3.cpp:395
cursor & operator=(cursor const &)
See the copy ctor.
Definition: sq3.cpp:347
int colname(int index, std::string &str)
Sets str to the column name as the given index (0-based).
Definition: sq3.cpp:519
Encapsulates a connection to an sqlite database.
Definition: sq3.hpp:233
database()
Creates an unopened database.
Definition: sq3.cpp:544
int setbusytimeout(int ms)
See sqlite3_busy_timeout().
Definition: sq3.cpp:691
bool is_open() const
Returns true if this db is opened.
Definition: sq3.cpp:563
sqlite3 * handle() const
The low-level handle to the sqlite db.
Definition: sq3.cpp:540
int vacuum()
Convenience wrapper around execute("vacuum").
Definition: sq3.cpp:822
virtual ~database()
Closes this db.
Definition: sq3.cpp:555
int execute(const std::string &sql)
Functionally identical to execute(char const *).
Definition: sq3.cpp:698
int changes()
Returns the number of database rows that were changed (or inserted or deleted) by the most recently c...
Definition: sq3.cpp:684
sqlite3 * take_handle()
Transfers ownership of this->handle() to the caller.
Definition: sq3.cpp:572
std::string name() const
Returns the name of the db file.
Definition: sq3.cpp:567
std::string errormsg() const
Returns the last error message from sqlite, or an empty string if this object is not opened.
Definition: sq3.cpp:585
virtual int open(char const *, long flags=0)
Creates/opens the given db file.
Definition: sq3.cpp:591
virtual int clear()
Looks through sqlite_master for a list of views, triggers, and tables, and drops them all (in that or...
Definition: sq3.cpp:827
int pragma(char const *code)
This is a convenience wrapper for execute( "pragma ..." ).
Definition: sq3.cpp:814
int64_t insertid()
Returns the rowid of the most recently inserted row on this db.
Definition: sq3.cpp:677
virtual int on_open()
This function is called when open() succeeds.
Definition: sq3.cpp:632
int close(bool force=false)
"Closes" this db.
Definition: sq3.cpp:663
cursor get_cursor()
Returns a cursor object ready to step over the result set from this object.
Definition: sq3.cpp:246
int colcount()
Returns the column count of this prepared statement, or -1 on error.
Definition: sq3.cpp:311
int bind(int index)
Binds NULL to the given placeholder index (1-based, not 0-based!).
Definition: sq3.cpp:173
~statement()
Calls this->finalize()
Definition: sq3.cpp:169
int prepare(std::string const &sql)
(Re-)prepares an SQL statement.
Definition: sq3.cpp:62
int execute()
Assumes this object's SQL statement is a single statement.
Definition: sq3.cpp:250
int finalize()
Finizalizes the underlying prepared statement, freeing its resources.
Definition: sq3.cpp:122
char const * colname(int index)
On success, it returns the null-terminated column name of the given column.
Definition: sq3.cpp:318
int reset()
Calls sqlite3_reset() on the underlying statement handle and returns the result.
Definition: sq3.cpp:304
bool is_prepared() const
Use after construction to ensure that a statement was compiled.
Definition: sq3.cpp:165
This type represents a transaction block in an SQL session.
Definition: sq3.hpp:625
int commit()
Commits the active transaction.
Definition: sq3.cpp:892
int begin()
Starts the transaction.
Definition: sq3.cpp:881
~transaction()
Calls this->rollback()
Definition: sq3.cpp:876
int rollback()
Initiates a rollback and returns the result of executing a rollback command.
Definition: sq3.cpp:907
The sq3 namespace encapsulates an OO sqlite3 API very similar to the sqlite3x API,...
Definition: sq3.hpp:77
char unsigned sqlite3_text_char_t
A char type used by some of the sqlite3 API to represent text data.
Definition: sq3.hpp:127
bool rc_is_okay(int rc)
rc_is_okay() is an easy way to check if rc is one of SQLITE_OK, SQLITE_ROW, or SQLITE_DONE.
Definition: sq3.cpp:56
sqlite_int64 int64_t
The type used for signed 64-bit integer operations.
Definition: sq3.hpp:82
sqlite_uint64 uint64_t
The type used for unsigned 64-bit integer operations.
Definition: sq3.hpp:86
A specialized dtor to close sqlite3 handles, for use with refcount::rcptr<sqlite3,...
Definition: sq3.hpp:137
void operator()(sqlite3 *&t)
Calls sqlite3_close(t) and assigns t to 0.
Definition: sq3.cpp:46
A specialized dtor to finalize sqlite3_stmt handles, for use with refcount::rcptr<sqlite3,...
Definition: sq3.hpp:177
void operator()(sqlite3_stmt *&t)
Calls sqlite3_finalize(t) and assigns t to 0.
Definition: sq3.cpp:37
A specialized dtor to call reset sqlite3_stmt handles(), for use with refcount::rcptr<sqlite3,...
Definition: sq3.hpp:163
void operator()(sqlite3_stmt *&t)
Calls sqlite3_reset(t) and assigns t to 0.
Definition: sq3.cpp:28
A specialized dtor to reset (not close) sq3::statement objects, for use with refcount::rcptr<sqlite3,...
Definition: sq3.hpp:150
void operator()(::sq3::statement *&t)
Calls t->reset() and assigns t to 0.
Definition: sq3.cpp:19