SQLite Forum

C/C++ API: Is it posiible to clone database connection?
Login

C/C++ API: Is it posible to clone a database connection?

(1.2) By little-brother on 2021-01-24 22:07:10 edited from 1.1 [link]

I have a sql editor with multiply windows (tabs) using a one connection in single-thread or serialized mode. It's ok for short time queries. But a long query blocks a execution in another window.

I want that each windows have similar a db connection configuration e.g. attached databases, loaded extensions, pragmas. I can use multi-thread mode but in this case I should synchronize settings between connections. It looks hard to implement. So I ponder about using a copy of a "template"-connection for each executing query (run a thread, open a connection copy, execute the query, close the connection, destoy the thread). 

That is why I need a way to make a connection copy. But I didn't find this ability in API. Is it possible?

(2) By Simon Slavin (slavin) on 2021-01-24 14:47:52 in reply to 1.1 [link]

Is your SQLite database in using WAL mode ?  What's the response from

<code>PRAGMA journal_mode</code>

?  If not, use the PRAGMA to put the database into WAL mode and try again. The journal mode is saved in your database.  No need to change any code in your program.

(3) By little-brother on 2021-01-24 22:06:29 in reply to 2 [link]

As I understand, the WAL mode is used to readers do not block writers and a writer does not block readers. My case may be limited to readers.

Let's assume we have a long time query e.g.
```
WITH RECURSIVE t(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM t LIMIT 10000),
t2(x) as (select x from t order by random())
select avg(t.x * t2.x) from t, t2 order by random()
```
This statement is executed by typical calls
```
sqlite3_prepare_v2(...)
while (SQLITE_ROW == sqlite3_step(stmt)) { ... }
sqlie3_finalize(stmt);
```
There are 3 threading modes: Single-thread, Serialized and Multi-thread.<br>

If steps running in main loop (main thread) in single thread mode, they will stop GUI. <br>The simpest way to stay GUI responsible is to use [progress handler](https://www.sqlite.org/c3ref/progress_handler.html).<br>But in the single thread I can't run an another query in parallel.

I can use Serialized mode: use one database connection and a separate thread per query.<br>But there are two problems:
 
1. Queries will be running in sequental mode - one by one.
2. An interrupt operation will be terminate all executing/planned queries.

So I come to multi-thread mode.<br>But there is problem with the synchronization of connection configs (they all should be same).

(4) By Larry Brasfield (LarryBrasfield) on 2021-01-24 23:09:29 in reply to 1.2 [link]

Will you please stop "editing" your post(s) just to change the timestamp? It is annoying and time-wasting.

If you have something substantive to add, add it as another post. And if you believe, after a reasonable period of time has passed, that the thread deserves more attention that it is getting, maybe then add something calculated to improve prospects for an answer.

Your dummy, no-real-changes editing makes me want to just block you. It is like a child crowding between a group of other children and an attention-giving adult.

BTW, there is no API for cloning a connection. You need to simply use the multi-threading capability (such as it is) that SQLite already has. Even if you could get a cloned connection, it would perform no better in multi-thread scenarios. Critical database access must be serialized either way, either by the set of connection holders or the library itself. (See [Using SQLite In Multi-Threaded Applications](https://sqlite.org/threadsafe.html).) You should study [Write-Ahead Logging](https://sqlite.org/wal.html#concurrency) to see what can be done to assuage your performance concerns. Use of multiple connections to the same database is among the scenarios addressed there.

Because there are allocated resources associated with a connection, it is better to simply make additional connections to the same database if there will be different prepared statements in process. Those clones (if they existed), would have to be deep copies, little different from seemingly redundant connections unless the copies were made different by usage.

Your objective of getting "similar" connection "configuration" could be achieved by using common code to get connections to the configuration you want, and keeping them in a pool instead of tossing them as threads are done or die.

I would also suggest that you first find out how well use of simply redundant connections solves the problem that motivates your "clone" inquiry before getting to attached to that approach or disappointed that it is unsupported. The real problem is not management of data held by some sqlite3 struct(s); it is management of the resources referenced by the struct(s). Replication of the struct data is a side problem, more of a distraction than the real issue.

(6) By little-brother on 2021-01-25 08:51:04 in reply to 4 [link]

> Your dummy, no-real-changes editing makes me want to just block you. ...

Impressive. I forgot that this forum engine sorts threads also by edit data. It's an unusual behaviour. I'm used to editing posts if I think it's necessary. Next time I'll use Stack Overflow in order not to annoy you. 

> keeping them in a pool

Assume the app has 2 tabs and therefore two connections in the pool. In first tab user writes `attach database a.sqlite` or `pragma synchronous = 0`. He will expect that these changes will be applyed to both tabs. But there is no mechanizm to sync them. And that is why I can't use "by using common code to get connections to the configuration you want".

> The real problem is not management of data held by some sqlite3 struct(s); it is management of the resources referenced by the struct(s)

I don't want to get a real copy. I need the same runtime configuration. I can achieve it by next steps:

1. Read all pragmas state (or most used and reject others).
2. Read all attached databases
3. Apply all pragmas to a new connection 
4. Attach databases 
5. Load extensions for the new connection

But it is a lot of work for me and sqlite. I thought there is a simple way. It does not exist. ОК.

(7) By Keith Medcalf (kmedcalf) on 2021-01-25 11:14:11 in reply to 6 [link]

> Assume the app has 2 tabs and therefore two connections in the pool. In first tab user writes attach database a.sqlite or pragma synchronous = 0. He will expect that these changes will be applyed to both tabs.

This must be a pretty stupid user to have such unrealistic expectations.  In fact, I would posit that "dumb as a stump" applies and that such a person is totally unsuitable to be using a computer and should pack it back in the box, take it back to where they bought it, and ask for a refund.

 >  But there is no mechanizm to sync them. And that is why I can't use "by using common code to get connections to the configuration you want".

Just as when you have a garage with three bandsaws in it there is nothing which will magically "transport" the setting you make on one of them to another of them sitting by its side.

The problem exists purely in your own imagination or the irrational expectations of others (or perhaps you perception of those others unreasonable and illogical expectations).  You should stop doing that.

(9) By little-brother on 2021-01-25 12:18:48 in reply to 7 [link]

> This must be a pretty stupid user to have such unrealistic expectations

Take a look to most popular SQLite GUIs "SQLite Studio" and "DB Browser for SQLite". They both have tabs. If I attach a database in a one tab-editor then I can referenced to it in an another tab.

DB Browser obviously uses a one connection, so it can't perform queries in parallel (GUI suggests to interrupt the running query and run current one).

SQLite Studio uses some tricky mode.

1. On the first tab I run `begin; create table t (id integer, data text);`<br>
   On the second tab I execute `rollback` and the table `t` is disappeared.<br>Looks like a serialized thread mode with the one shared connection.
2. On the first tab I run `begin; create table t (id integer, data text); WITH RECURSIVE t(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM t LIMIT 10000) ...;`<br>
   On the second tab I execute `rollback`. It's freezing until the long time query will be completed. And after that the table `t` will be disappeared. Serialized mode!<br>But in the same time Studio can stop two different long time queries in different tabs separately.

(10) By Larry Brasfield (LarryBrasfield) on 2021-01-25 12:31:14 in reply to 9 [link]

I suggest you look into a design pattern known as "[Model-View-Controller](https://en.wikipedia.org/wiki/Model%E2%80%93view%E2%80%93controller)", or "MVC". I dare say that if you were using it, your problem first posed in this thread would not have arisen.

(12) By Keith Medcalf (kmedcalf) on 2021-01-25 12:39:28 in reply to 9 [link]

> Looks like a serialized thread mode with the one shared connection.

This is assuming fact not in evidence.  This whole scenario requires no more than one single thread and one connection.

 > Serialized mode!

All connections in SQLite3 are serial.  The choice is whether YOU do the serialization or whether SQLite3 forces it on you.  Nevertheless, executing something on a connection is *ALWAYS AND WITHOUT EXCEPTION* a one-thread-of-execution-at-a-time side-after-each.

 > But in the same time Studio can stop two different long time queries in different tabs separately.

This is the Hooey-Gooey thread calling the interrupt hook of the connection that it wants to interrupt in response to the event of a ugly-bag-of-mostly-water pushing a hooey-gooey button or something, and has nothing whatsoever to do with anything at all.

And the reason that two queries can run simultaneously is because they are using two threads, each of which is executing a query on different connections.

If "N" queries in the same process are executing sqlite3 queries at the same time, then they are doing so using "N" threads and "N" connections.  You cannot execute more than 1 thing per connection at a time -- ever.

(13) By little-brother on 2021-01-25 13:55:34 in reply to 12 [link]

I did research deeper for SQLite Studio. It uses a separate (perhaps one at all) thread for SQLite.

When an user run multiply queries then Studio push all of them to an own executable stack and execute them consequentially i.e. Studio doesn't run queries immediately. Studio waits for a running query to complete and then pops the next one off the stack and runs it. So when user press "Interrupt"-button for a not-running-yet query there is no call `sqlite3_interrupt`, only removal from the stack.

Very simple and works for most cases. But there are also side effects: openning of data editor for table during a long time query freezes GUI.

.

(8) By Larry Brasfield (LarryBrasfield) on 2021-01-25 11:27:49 in reply to 6

> > Your dummy, no-real-changes editing makes me want to just block you. ...

> ... I'm used to editing posts if I think it's necessary.

I refer to a succession of 3 post versions which differ in no way other than timestamp. I have to wonder how you can believe such "edits" to be necessary.

> Assume the app has 2 tabs and therefore two connections in the pool. In first tab user writes attach database a.sqlite or pragma synchronous = 0. He will expect that these changes will be applyed to both tabs. But there is no mechanizm to sync them. And that is why I can't use "by using common code to get connections to the configuration you want".

It is not hard to imagine whatever code makes the connection changes doing them to the whole set of connections that are to act the same.

> > keeping them in a pool

> I don't want to get a real copy. I need the same runtime configuration. I can achieve it by next steps:
>
>  ...
>
> But it is a lot of work for me and sqlite. I thought there is a simple way. It does not exist. ОК.

There are several simpler ways. Use the same connection instead of many doing the same thing. Apply changes in tandem, perhaps iterating over the pool. 

I would not worry much about changes instigated via UI being too taxing for SQLite, at least not with any hardware from the last several decades.

(11) By little-brother on 2021-01-25 12:32:57 in reply to 8 [link]

> I refer to a succession of 3 post versions which differ in no way other than timestamp

The last change was a remove double `ii` (posiible) in the start post title. I don't update a post without changes.

> Apply changes in tandem, perhaps iterating over the pool. 

Yeah, it's idea. Detect any `pragma`/`attach`/`dettach` command is simple. Thanks.

> I would not worry much about ...

I don't worry about performance. I want to allow an user to execute (and interrupt if he needs) long time queries.

(5) By Gunter Hick (gunter_hick) on 2021-01-25 06:57:52 in reply to 1.2 [link]

Just put all your setup into a config file and load/execute that when you create a new connection.