Title: Create temp view incorrectly marks RO DB "dirty" · Issue #1514 · sqlitebrowser/sqlitebrowser · GitHub
Open Graph Title: Create temp view incorrectly marks RO DB "dirty" · Issue #1514 · sqlitebrowser/sqlitebrowser
X Title: Create temp view incorrectly marks RO DB "dirty" · Issue #1514 · sqlitebrowser/sqlitebrowser
Description: Details for the issue What did you do? Open a DB Read only. In Execute SQL tab, execute create temp view foo as select * from table1; File => Close DB What did you expect to see? Nothing What did you see instead? ? Do you want to save th...
Open Graph Description: Details for the issue What did you do? Open a DB Read only. In Execute SQL tab, execute create temp view foo as select * from table1; File => Close DB What did you expect to see? Nothing What did y...
X Description: Details for the issue What did you do? Open a DB Read only. In Execute SQL tab, execute create temp view foo as select * from table1; File => Close DB What did you expect to see? Nothing What di...
Opengraph URL: https://github.com/sqlitebrowser/sqlitebrowser/issues/1514
X: @github
Domain: github.com
{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Create temp view incorrectly marks RO DB \"dirty\"","articleBody":"Details for the issue\r\n--------------------\r\n\r\n#### What did you do?\r\n\r\nOpen a DB Read only.\r\n\r\nIn Execute SQL tab, execute\r\n\r\n````\r\ncreate temp view foo as select * from table1;\r\n````\r\n\r\nFile => Close DB\r\n\r\n#### What did you expect to see?\r\n\r\nNothing\r\n\r\n#### What did you see instead?\r\n````\r\n? Do you want to save the changes made to the database file ...?\r\n````\r\n\r\nWhile it is reasonable not to parse all the commands & responses in an \"Execute\" tab to determine if actions modify the database, it is guaranteed (I hope) that a database open as Read-Only can not be modified!\r\n\r\nSo, **Option 1**: Don't pay attention to the \"Dirty\" flag if a DB is open read-only,\r\n\r\nHowever, it appears that you may be able to use SQLite to detect a modified DB fairly painlessly.\r\n\r\n**Option 2**: [Section 1.3.6](https://sqlite.org/fileformat2.html) of the DB header spec says:\r\n\r\n> The file change counter is a 4-byte big-endian integer at offset 24 that is incremented whenever the database file is unlocked after having been modified. When two or more processes are reading the same database file, each process can detect database changes from other processes by monitoring the change counter. A process will normally want to flush its database page cache when another process modified the database, since the cache has become stale. The file change counter facilitates this.\r\n>\r\n> In WAL mode, changes to the database are detected using the wal-index and so the change counter is not needed. Hence, the change counter might not be incremented on each transaction in WAL mode.\r\n\r\nSo unless the DB is in WAL mode, you can rely on it instead of your own dirty bit. (The [API ](https://sqlite.org/c3ref/file_control.html)indicates that sqlite_file_control([SQLITE_FCNTL_FILE_POINTER](https://sqlite.org/c3ref/c_fcntl_begin_atomic_write.html#sqlitefcntlfilepointer)) returns a pointer to [sqlite3_file](https://sqlite.org/c3ref/file.html) object, which includes a pointer to a [sqlite3_io_methods](https://sqlite.org/c3ref/io_methods.html) object, which should allow you to xRead the header) There should be an API to access this directly, but a quick scan of the documentation didn't turn it up.\r\n\r\nIf this works as expected, you would not have to maintain your own \"dirty\" flag for R/W databases either. I would expect that to simplify your code, even if you have to save/restore the journaling mode to avoid WAL.\r\n\r\nUseful extra information\r\n-------------------------\r\n\r\nThe info below often helps, please fill it out if you're able to. :)\r\n\r\n#### What operating system are you using?\r\n\r\n- [x] Windows: ( _version:_ 10 pro___ )\r\n- [ ] Linux: ( _distro:_ ___ )\r\n- [ ] Mac OS: ( _version:_ ___ )\r\n- [ ] Other: ___\r\n\r\n#### What is your DB4S version?\r\n\r\n- [ ] 3.10.1\r\n- [ ] 3.10.0\r\n- [ ] 3.9.1\r\n- [x] Other: ___Aug 23 nightly\r\n\r\n#### Did you also\r\n\r\n- [x] Try out the latest nightly version: https://github.com/sqlitebrowser/sqlitebrowser#nightly-builds\r\n- [x] Search for an existing similar issue: https://github.com/sqlitebrowser/sqlitebrowser/issues?utf8=%E2%9C%93&q=is%3Aissue%20\r\n","author":{"url":"https://github.com/tlhackque","@type":"Person","name":"tlhackque"},"datePublished":"2018-08-23T17:45:32Z","url":"https://github.com/sqlitebrowser/sqlitebrowser/issues/1514"}
| react-profiling | 0 |
| react-import-map | react,react-dom,react-dom/client,react-dom/profiling,react-is,react/compiler-runtime,react/jsx-dev-runtime,react/jsx-runtime,scheduler@b8488ee2c322 |
| route-pattern | /_view_fragments/issues/show/:user_id/:repository/:id/issue_layout(.:format) |
| route-controller | voltron_issues_fragments |
| route-action | issue_layout |
| fetch-nonce | v2:ca36abec-2306-76f5-83c5-a2bb1e85407f |
| current-catalog-service-hash | 81bb79d38c15960b92d99bca9288a9108c7a47b18f2423d0f6438c5b7bcd2114 |
| request-id | AAC2:F27CE:235CD97:3138417:6AFD3375 |
| html-safe-nonce | aa3f7dd02495f09c06aad0ac50f2c461005fd21365f7abe118a2e48f22530c06 |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiJBQUMyOkYyN0NFOjIzNUNEOTc6MzEzODQxNzo2QUZEMzM3NSIsInZpc2l0b3JfaWQiOiIyNTUwNzg0MzY0NTM5MzYwMTE3IiwicmVnaW9uX2VkZ2UiOiJpYWQiLCJyZWdpb25fcmVuZGVyIjoiaWFkIn0= |
| visitor-hmac | 6c4c775116b9433c4b2d183895febe3fa589d9b10759cc54e08d7a58b2217d64 |
| hovercard-subject-tag | issue:353485847 |
| github-keyboard-shortcuts | repository,issues,copilot |
| google-site-verification | Apib7-x98H0j5cPqHWwSMm6dNU4GmODRoqxLiDzdx9I |
| octolytics-url | https://collector.github.com/github/collect |
| analytics-location | / |
| fb:app_id | 1401488693436528 |
| apple-itunes-app | app-id=1477376905, app-argument=https://github.com/_view_fragments/issues/show/sqlitebrowser/sqlitebrowser/1514/issue_layout |
| twitter:image | https://opengraph.githubassets.com/84064905bca3ed2bc064c72112718829d8d54791cd7162dc64e17d63314b9fa7/sqlitebrowser/sqlitebrowser/issues/1514 |
| twitter:card | summary_large_image |
| og:image | https://opengraph.githubassets.com/84064905bca3ed2bc064c72112718829d8d54791cd7162dc64e17d63314b9fa7/sqlitebrowser/sqlitebrowser/issues/1514 |
| og:image:alt | Details for the issue What did you do? Open a DB Read only. In Execute SQL tab, execute create temp view foo as select * from table1; File => Close DB What did you expect to see? Nothing What did y... |
| og:image:width | 1200 |
| og:image:height | 600 |
| og:site_name | GitHub |
| og:type | object |
| og:author:username | tlhackque |
| hostname | github.com |
| expected-hostname | github.com |
| None | 065128366c233a9d57ff68ab6c96e25b99dd1b0ab70d23b70873d3dd6b5e4f19 |
| turbo-cache-control | no-preview |
| go-import | github.com/sqlitebrowser/sqlitebrowser git https://github.com/sqlitebrowser/sqlitebrowser.git |
| octolytics-dimension-user_id | 7454271 |
| octolytics-dimension-user_login | sqlitebrowser |
| octolytics-dimension-repository_id | 19416551 |
| octolytics-dimension-repository_nwo | sqlitebrowser/sqlitebrowser |
| octolytics-dimension-repository_public | true |
| octolytics-dimension-repository_is_fork | false |
| octolytics-dimension-repository_network_root_id | 19416551 |
| octolytics-dimension-repository_network_root_nwo | sqlitebrowser/sqlitebrowser |
| turbo-body-classes | logged-out env-production page-responsive |
| disable-turbo | false |
| browser-stats-url | https://api.github.com/_private/browser/stats |
| browser-errors-url | https://api.github.com/_private/browser/errors |
| release | 941021edf0c2ec58298f4a2c8f27ae507d129c92 |
| ui-target | full |
| theme-color | #1e2327 |
| color-scheme | light dark |
| react-app-name | issues |
Links:
Viewport: width=device-width