[Feature]: Soft attach mode

[martty]

Describe the new feature

When you attach a database, the browser acquires a lock on the file (at least on windows). This is a problem for certain operations: in particular I would like to keep a database "open", while a program in the background transparently replaces the database file.
When issuing another SQL operation, the browser would reopen the connection and close after the operation.

For more context I am implementing checking in an sqlite db into version control - git moves files around on checkout and this is not compatible with the current operation of the browser.

Does this feature exist in another product or project? Please provide a link

No response

Do you have a screenshot? Please add screenshots to help explain your idea.

No response

[scottfurry]

I am disheartened by how many people misunderstand what SQLite can and cannot do. Don't get me wrong, it's brilliant stuff opening up the features of SQL to the masses. However, SQLite has it's limits. Some of these limits are intentional to design.

Please see the section "Checklist For Choosing The Right Database Engine" from this SQLite page Appropriate Uses For SQLite.

tl:dr version is file locking is done as apart of SQLite design of one writer only having access. Anything more puts us into RDBMS territory.

[martty]

Sorry, maybe it wasn't clear what I wanted to propose - definitely not multiple writers at the same time. It is just about the browser relinquishing the file lock and connection when no SQL operations are done.

[scottfurry]

...and negotiating who has the database open becomes a mutex nightmare. DB4S tries to keep it simple in line with SQLite operation. Database file is open or closed. One is operating on it or done with it. Maintaining a connection to the file under SQLite brings with it the file locking mechanism. Using a soft lock breaks that principal and involves a file handling mechanism outside of the SQLite library - and that brings with it a pile of other issues.

[martty]

I am sorry, I don't follow - the browser already has to open connections and close them - what I propose just does it automatically what I could do from the gui (clicking attach db and close db). Whatever problems exist for acquiring a connection under contention already has to be handled by the application.

[scottfurry]

File handling is done via the SQLite library. The path is handed over to the library which then opens the database file. This results in the SQLite library invoking the file locking mechanism. Closing the database directs the SQLite library to release the file lock.

Request goes around this whole mechanism and behavior of SQLite. We need the library to read the data in the file. DB4S uses SQLite library which uses file locking to prevent more than one access at a time. You are requesting a feature the cannot be implemented with the SQLite library or involves recreating the SQLite functionality without file locking.

[martty]

I think we are still not on the same page. What I propose is that DB4S closes the database connection after each SQL operation and opens a new connection when required by user interaction (to submit additional queries). There is additional work to be done by DB4S (opening and closing connections on demand rather than up front), but it does not go beyond what SQLite is designed to do as far as I understand.

[scottfurry]

And still monitor data? Not possible. Again, what is being asked is not how the underlying library operates. Database file is open or closed. Period. Multi-user or multi-access is not apart of the SQLite design. That feature is only available in RDBMS implementations and SQLite was never designed to be a full-blown relational database management system.

I would encourage you to read through the SQLite.org site to understand what SQlite is and is not.

[kociap]

could you explain to me why the browser needs to maintain the lock on a file when the browser application is idle, i.e. there is no user input?

[scottfurry]

• DB4S uses the SQLite library for database file access.
• DB4S passes a file path to SQLite
• SQLite library opens the file
• SQLite places a file lock on the file.
• Any other access on the file is blocked by SQLite with the existence of the file lock.
• To establish any kind of read of the file while it is open by SQLite means to break the SQLite library file lock mechanism or recreate SQLite data access functionality through another library.

SQLite is not multi-user, RDBMS software. Yes, it uses SQL-92 standard, stores data in a relational (table-oriented) manner, and employs single-user, file level access to data. SQLite does not employ multi-user access methods expected as part of RDBMS software. All of this is fundamental to explanations provided by SQLite in differentiating SQLite library from RDBMS software (PostgreSQL, Oracle, MySQL, MariaDB, et al).

[scottfurry]

RDBMS software is meant to operate as a service on a computer. A service allows for multiple points of access to data.

SQLite, being file system-oriented, does not work as a service and, therefore, only works on a per-file basis. File locking is implemented by the SQLite library to prevent data corruption. This is described in the Appropriate Uses For SQLite page referenced earlier.

[kociap]

So as far as I understand SQLite opens a "connection" by placing a lock on a file. What exactly stops us from closing the connection (and releasing the lock as a result) when there is no user input?

[martty]

So this is not correct: SQLite does support multi-user access, it is even spelled out on the links you sent:

SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time.

In fact the lock we are talking about isn't even for preventing concurrent access to the database - this is already possible. What I am talking about is about closing the file via SQLite - so that the file itself can be replaced. Then the file can reopened when needed. This is no different from how notepad.exe does not lock the file after you have opened it.

[scottfurry]

https://www.sqlite.org/wal.html

I'm not a fan of the idea that is being proposed. It adds a level of complexity to how the database file is opened/used. This is supposed to be a "simple" means of accessing a database file and data. If other requirements are needed, other tools exist that may better suit needs.

[martty]

Please reopen the issue. I am not demanding that this feature is implemented solely for my use, but I do think it is generally an improvement and worth the discussion. I am also not opposed to contributing if no maintainer is interested in implementing, but the feature is deemed worthwhile to have.

[chrisjlocke]

could you explain to me why the browser needs to maintain the lock on a file when the browser application is idle, i.e. there is no user input?

Historical reasons, maybe?
At my work, there are two schools of thought. Open a connection when needed, and open a connection at program start and use that throughout.
I'm not a great programmer, but I tried method #1, and it was painful. Break the program into modules, and each module had to check if there was a connection. Process A could run, then process B, then process A would close the connection but process B would then get upset. By process, I mean a flow in the program - not a thread. My terminology sucks.
Anyway. Method 2 was much cleaner. Also, handy for the logs. Session 89 had issues. This tied back to a user. Also, cleaner code as each bit knew what connection it was using.
Anyway. Again. DB4S has kind of grown, and its 'purpose' has been like a text editor - open a document (database), work on it, save it, close it. Multi-use hasn't really been a request.

So this is not correct: SQLite does support multi-user access, it is even spelled out on the links you sent

Multi-readers doesn't mean multi-user. Again, if you read the docs, its spelt out there. It does depend on the OS. You can't (for example) slap a SQLite database on a Windows Server share and get multi-users to use it. You can, and it does sort of work, but it will corrupt. Also, if a user is importing records (eg, a large CSV) then all other readers are locked out. That's not fun. "Sorry, the import will just take another twenty minutes".

In fact the lock we are talking about isn't even for preventing concurrent access to the database

It's for preventing concurrent writes. This is why only one writer can write at any one time.
If a writer is writing, then a reader can't read ... they're locked out. So that does prevent concurrent access, does it not?

What exactly stops us from closing the connection (and releasing the lock as a result) when there is no user input?

Technically nothing, but it would require an internal rewrite. When re-opening the connection, DB4S would have to reload the schema (as it may have changed), repopulate controls, internal structures, etc. There is a lot of work to make a square wheel into a round wheel. Unfortunately, with 500+ outstanding issues and not 500+ outstanding developers to tackle them (see what I did there? I like that ... may use that again one day) rewriting the whole program is just not feasible.

[chrisjlocke]

Please reopen the issue. I am not demanding that this feature is implemented solely for my use, but I do think it is generally an improvement and worth the discussion

The issue can be discussed while it's closed.
Again, it can be open or closed, but that's just a cosmetic token. Don't take offence at this, but unless 800 people demand this feature, it will just sit at the bottom of the 'todo' pile. That's no reflection on you ... just that it's a lot of work, and no one here to do it.

[martty]

I still feel like we are talking a bit sideways, but at least getting closer.

It's for preventing concurrent writes. This is why only one writer can write at any one time.
If a writer is writing, then a reader can't read ... they're locked out. So that does prevent concurrent access, does it not?

Yes, but I do not suggest subverting this system. A file lock will still be taken. All the proposed functionality would do is automated what can already done via the gui. I know it requires additional code, of course. In fact rewriting is a reasonable argument against having this feature, and I am not opposed to this argument being fielded. But I do want the idea judged on its merits and weaknesses and not on unrelated points.

When re-opening the connection, DB4S would have to reload the schema (as it may have changed), repopulate controls, internal structures, etc.

I don't follow this bit - I thought DB4S can open connections in NORMAL locking mode - in this case schema and data can change between queries, no? Why would this be additionally different?

I am sorry that this became so confrontational, this wasn't my intention. I do not mind the issue being closed if this doesn't signal that the issue is no longer up for discussion - it being open is usually an indication that there is discussion ongoing.

And finally, as I suggested previously, I don't mind assessing the work required and taking a stab at it, if the contribution would be accepted (conceptually, not a guarantee).

[chrisjlocke]

Yes, but I do not suggest subverting this system.

I was correcting your 'thwack on the head' to Scott, who was trying to help, when you was suggesting he was wrong and SQLite does support multi-user. That's not its purpose in life.

I don't follow this bit

DB4S doesn't currently look out for schema changes**. You can confirm this by changing the schema when DB4S is using it. It can get upset if a table it expects is suddenly not there. Auto-complete would be 'out of sync' (suggesting tables and fields that no longer exist), etc. The guts of the program will work (adding new tables, blah blah) but some bits won't be written to expect schema changes.
** Well, it probably does in some places, but not consistently.

sorry that this became so confrontational

I don't think it did - sometimes explaining things in emails/text can be hard work. I get this at work frequently - a fifteen page email can be summarized in about two sentences when said face-to-face.

it being open is usually an indication that there is discussion ongoing

True here, but also (!) we close issues so we know what we've got to work on - the 'todo' list. Having a load of issues which are mid-discussion can cause delays and confusion. In fact, someone may be along shortly and move this whole issue to a discussion instead. That'll be fun.

I don't mind assessing the work required and taking a stab at it

The code is open to all, and you're more than welcome to submit PRs. You can even fork the project and hack away at will. We'll always be up for slurping the code back in. Again, the project has been going a number of years, and your request for 'concurrent use' is the first so far. It may be highly desirable and no one else has had the balls to request it - who knows.