Re: [review] [sqlite]

From: Klemens Morgenstern (klemensdavidmorgenstern_at_[hidden])
Date: 2024-11-14 13:52:19

On Thu, Nov 14, 2024 at 9:37 PM Artyom Beilis via Boost
<boost_at_[hidden]> wrote:
>
> > The Boost formal review of the Boost SQLITE library starts *TODAY*, taking
> > place
> >
> > Documentation: https://klemens.dev/sqlite/
>
> Several questions to the author regarding documentation and API (not a
> review yet)
>
> 1. Transactions.
>
> I noticed that you call explicitly begin/connit
>
>     conn.query("begin transaction;");
>     ...
>     conn.query("commit;");
>
> Both soci [1] and cppdb [2] and most database wrappers I have seen
> provide scopes that allow to commit or rollback
> in case of failure/exception. It is something very common and
> allowshandling translations in an exception safe way.
>
> Do you provide such a feature?
>

yes, sqlite::transaction:

https://klemens.dev/sqlite/structboost_1_1sqlite_1_1transaction.html

It does not commit in the destructor, but always calls rollback if
there was no explicit commit.

> 2 Example
>
>     boost::sqlite::row r;
>     boost::sqlite::query q = conn.query(...)
>     do
>     {
>      auto r = q.current();''
>      std::cout << r.at(0u).get_text() << " authored " <<
> r.at(1u).get_text() << std::endl;
>     }
>     while (q.read_next());
>
> What happens when the result is empty? I'd rather expect to see
> for(;;) or while loop. How do I check if the result is valid?
> How do I manually iterate over the result without an automatic "for" loop.
>

boost::sqlite::query q = conn.query(...);
if (q.read_next()) // read another row

If the result is empty q.done() is true.

Invalid results would lead to exception unless you use the
`error_code&` overloads, e.g.

boost::system::error_code ec;
boost::sqlite::error_info ei;
boost::sqlite::query q = conn.query(...,, ec, ei);
if (!ec)
  if (q.read_next(ec, ei)) // read another row

> 3. What happens if you do not read the entire result? How do you abort
> the query?
> As far as I remember if you'll execute another statement when you
> hadn't finished you'll get SQLITE_BUSY error

Aborting the query is done by letting the `query` object go out of scope.

>
> 4. Both soci and cppdb have some syntactic sugar to query a single row
> - for example when querying something by primary key. Can you do it
> without a loop?

sqlite::row r = conn.query(...).row() ;

or

author a = conn.query<author>(...).row();

> 5. How do you handle locks/collisions when two processes try to update
> the same DB. It is a tricky bit in sqlite - sometimes you need to
> wait, sometimes to abort.

That would just be an error, there's no explicit handling in the library.

> I noticed the word "error" appears only once in a tutorial... Please
> add documentation on error handling

Will do.
Most functions have an overload taking an error_code & error_info by
reference for the error
and one that will throw an exception.

> 6. Are objects copyable or only moveable? Please state explicitly
>

Most are move-only. It's documented in the reference.

>
> Disclosure: I'm the author of cppdb and contributed to/used in past soci.
>
> Artyom Beilis
>
>
> 1) https://soci.sourceforge.net/doc/master/transactions/
> 2) http://cppcms.com/sql/cppdb/transaction.html
>
> _______________________________________________
> Unsubscribe & other changes: http://lists.boost.org/mailman/listinfo.cgi/boost