On Thu, Mar 19, 2015 at 10:30 AM, Richard Hipp <drh at sqlite.org> wrote:
> On 3/18/15, Scott Hess <shess at google.com> wrote:
>> I'm thinking I could use something like:
>>
>> SQLITE_API int sqlite3_attach(sqlite3* db, const char* zPath, const
>> char* dbname);
>> SQLITE_API int sqlite3_detach(sqlite3* db, const char* dbname);
>>
>> Right now, I have a helper in Chromium which does "ATTACH DATABASE ?
>> AS ?". This works, but AFAICT this page:
>> https://www.sqlite.org/lang_keywords.html
>> implies that the dbname _probably_ should not be a string literal but
>> instead an identifier. So it should be "dbname" with embedded "
>> characters doubled (nobody should ever do that, but *shrug*).
>
> Though undocumented, the arguments to ATTACH and DETACH can be
> arbitrary expressions. For example:
>
> ATTACH 'xyzzy' || '.txt' AS printf('abc-%d',12345');
>
> So bound parameters are also supported. We have test cases that
> verify this behavior.
Indeed, that's what I've found, but from the docs it's not entirely
clear what the documented way to construct it would be. Chromium uses
bind parameters and it works and it has a unit test (well, I hope I
wrote one). Mostly I added the helper to prevent people from
constructing using incorrect quoting.
>> WDYT? Mostly this just came up because I saw someone writing code
>> which constructed things sprintf-style, without quoting, which was
>> clearly error-prone. But in describing the right way to do it, I had
>> to do notable hand-waving.
>
> Seems like an implementation of your requested functions would be fairly
> simple:
>
> int sqlite3_attach(sqlite3* db, const char* zPath, const char* dbname){
> char *zSql = sqlite3_mprintf("ATTACH %Q AS %Q", zPath, dbname);
> int rc = sqlite3_exec(db, zSql, 0, 0, 0);
> sqlite3_free(zSql);
> return rc;
> }
> int sqlite3_detach(sqlite3* db, const char* dbname){
> char *zSql = sqlite3_mprintf("DETACH %Q", dbname);
> int rc = sqlite3_exec(db, zSql, 0, 0, 0);
> sqlite3_free(zSql);
> return rc;
> }
'%q' seems slightly better because NULL is oddly handled in this case.
"ATTACH '/tmp/db' AS NULL" doesn't seem to throw an error, but it also
doesn't really work. "ATTACH '/tmp/db' AS ''" (that's a single-quoted
empty string before double quotes) throws "data is already in use",
which I'm not sure how to interpret, but at least it's an error.
I will admit, this is a _very_ weak feature request. Mostly it seems
like it's on the boundary between "Things to do using SQL code" and
"Things to do using SQLite API". The main flaw of having a simple
wrapper as described above is that if you want to forbid attach/detach
for customer SQL code by using an authorizer, but you need those
functions in your implementation code, you have to add some support
for temporarily authorizing it. You probably want that kind of
functionality for PRAGMA anyhow, so maybe not a crushing problem.
-scott
