Repository navigation
[Feature]: Soft attach mode #3213
Description
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
Activity
scottfurry commentedon Nov 4, 2022
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 commentedon Nov 4, 2022
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 commentedon Nov 4, 2022
...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 lockbreaks that principal and involves a file handling mechanism outside of the SQLite library - and that brings with it a pile of other issues.martty commentedon Nov 4, 2022
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 commentedon Nov 4, 2022
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 commentedon Nov 4, 2022
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 commentedon Nov 4, 2022
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 commentedon Nov 4, 2022
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 commentedon Nov 4, 2022
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 commentedon Nov 4, 2022
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 commentedon Nov 4, 2022
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 commentedon Nov 4, 2022
So this is not correct: SQLite does support multi-user access, it is even spelled out on the links you sent:
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 commentedon Nov 4, 2022
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 commentedon Nov 4, 2022
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 commentedon Nov 4, 2022
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.
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".
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?
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 commentedon Nov 4, 2022
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 commentedon Nov 4, 2022
I still feel like we are talking a bit sideways, but at least getting closer.
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.
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 commentedon Nov 4, 2022
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.
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.
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.
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.
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.