Re: [sqlite] Safe saving of in-memory db to disk file
> On Jul 14, 2019, at 10:05 AM, ardi wrote: > > Do you have any recommendation for saving the inmemory db in a safe > way? If you’re going to keep your data in memory, there’s no good reason to use SQLite at all. Just define custom model objects that you can operate on efficiently, and write the data out in a standard format like JSON or XML. (Most frameworks have serialization features that make this easy.) The point of using a database is that you _don’t_ have all the data in memory, and can still access it quickly. This is attractive if you want to scale to large data sets that won’t fit in RAM or which take too long to read/write from storage. If that’s not an issue for you, don’t use a database; it just adds more complexity to your data model layer. —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug when creating a table via select?
On July 15, 2019 12:01:00 a.m. EDT, "J. King" wrote: >On July 14, 2019 11:56:15 p.m. EDT, Donald Shepherd > wrote: >>Somewhat bizarrely only "BLOB" affinity doesn't make it from the >>original >>table to the new table when using the "select" syntax to create the >new >>table. Even items with aliased affinities (VARTEXT, or something that >>defaults to NUMERIC) comes across as the base affinity but at least >>have an >>affinity. >> >>This is simple to reproduce: >> >>sqlite> .version >>SQLite 3.29.0 2019-07-10 17:32:03 >>fc82b73eaac8b36950e527f12c4b5dc1e147e6f4ad2217ae43ad82882a88bfa6 >>zlib version 1.2.11 >>gcc-5.2.0 >>sqlite> create table x(a int, b text, c real, d blob, e vartext, f >>garbage); >>sqlite> pragma table_info(x); >>0|a|int|0||0 >>1|b|text|0||0 >>2|c|real|0||0 >>3|d|blob|0||0 >>4|e|vartext|0||0 >>5|f|garbage|0||0 >>sqlite> create table y as select * from x; >>sqlite> pragma table_info(y); >>0|a|INT|0||0 >>1|b|TEXT|0||0 >>2|c|REAL|0||0 >>3|d||0||0 >>4|e|TEXT|0||0 >>5|f|NUM|0||0 >>sqlite> select * from sqlite_master; >>table|x|x|2|CREATE TABLE x(a int, b text, c real, d blob, e vartext, f >>garbage) >>table|y|y|3|CREATE TABLE y( >> a INT, >> b TEXT, >> c REAL, >> d, >> e TEXT, >> f NUM >>) >>___ >>sqlite-users mailing list >>sqlite-users@mailinglists.sqlite.org >>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >Blobs have no affinity. The result you're seeing is correct, just >represented in a surprising way. >-- >J. King >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users I misspoke. Blobs have an affinity historically called NONE (which is distinct from no affinity, but that's not relevant here). Presumably SQLite represents the BLOB affinity as null as a means of saying "NONE", again for historical reasons. -- J. King ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug when creating a table via select?
On July 14, 2019 11:56:15 p.m. EDT, Donald Shepherd wrote: >Somewhat bizarrely only "BLOB" affinity doesn't make it from the >original >table to the new table when using the "select" syntax to create the new >table. Even items with aliased affinities (VARTEXT, or something that >defaults to NUMERIC) comes across as the base affinity but at least >have an >affinity. > >This is simple to reproduce: > >sqlite> .version >SQLite 3.29.0 2019-07-10 17:32:03 >fc82b73eaac8b36950e527f12c4b5dc1e147e6f4ad2217ae43ad82882a88bfa6 >zlib version 1.2.11 >gcc-5.2.0 >sqlite> create table x(a int, b text, c real, d blob, e vartext, f >garbage); >sqlite> pragma table_info(x); >0|a|int|0||0 >1|b|text|0||0 >2|c|real|0||0 >3|d|blob|0||0 >4|e|vartext|0||0 >5|f|garbage|0||0 >sqlite> create table y as select * from x; >sqlite> pragma table_info(y); >0|a|INT|0||0 >1|b|TEXT|0||0 >2|c|REAL|0||0 >3|d||0||0 >4|e|TEXT|0||0 >5|f|NUM|0||0 >sqlite> select * from sqlite_master; >table|x|x|2|CREATE TABLE x(a int, b text, c real, d blob, e vartext, f >garbage) >table|y|y|3|CREATE TABLE y( > a INT, > b TEXT, > c REAL, > d, > e TEXT, > f NUM >) >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Blobs have no affinity. The result you're seeing is correct, just represented in a surprising way. -- J. King ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug when creating a table via select?
Somewhat bizarrely only "BLOB" affinity doesn't make it from the original table to the new table when using the "select" syntax to create the new table. Even items with aliased affinities (VARTEXT, or something that defaults to NUMERIC) comes across as the base affinity but at least have an affinity. This is simple to reproduce: sqlite> .version SQLite 3.29.0 2019-07-10 17:32:03 fc82b73eaac8b36950e527f12c4b5dc1e147e6f4ad2217ae43ad82882a88bfa6 zlib version 1.2.11 gcc-5.2.0 sqlite> create table x(a int, b text, c real, d blob, e vartext, f garbage); sqlite> pragma table_info(x); 0|a|int|0||0 1|b|text|0||0 2|c|real|0||0 3|d|blob|0||0 4|e|vartext|0||0 5|f|garbage|0||0 sqlite> create table y as select * from x; sqlite> pragma table_info(y); 0|a|INT|0||0 1|b|TEXT|0||0 2|c|REAL|0||0 3|d||0||0 4|e|TEXT|0||0 5|f|NUM|0||0 sqlite> select * from sqlite_master; table|x|x|2|CREATE TABLE x(a int, b text, c real, d blob, e vartext, f garbage) table|y|y|3|CREATE TABLE y( a INT, b TEXT, c REAL, d, e TEXT, f NUM ) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Safe saving of in-memory db to disk file
On 14 Jul 2019, at 6:05pm, ardi wrote: > I have read the backup API page (https://www.sqlite.org/backup.html) > that shows how to read a sqlite db from disk to memory, and how to > save it back to disk, but it doesn't talk about the topic of > performing the save in a safe way. This is a feature which SQLite provides by itself. If you keep a database on disk, SQLite ensures that a copy of your data – either before or after a change – is always available. It does this by changing the contents of the file, not by renaming one file and making another. This is one of the most important features of SQLite. It means you do not have to write your own software to make it happen. If your computer crashes while changes are being made, SQLite is always able to rescue one copy of the data from the file. It may be the copy before the change or the copy after the change, but it will never lose both copies. > Do you have any recommendation for saving the inmemory db in a safe > way? If you make an inmemory database you are yourself responsible for making sure your data is saved on disk. If you choose to keep your data in memory you are disabling the feature of SQLite you are asking about. > Another scenario of interest would be if the db is really huge and you > consider the possibility of not overwriting the whole old file, but > just committing the changes, in order to save unnecessary disk writes. Again, this is taken care of automatically if you keep your data on disk. SQlite does not rewrite the entire data file when changes are made. It rewrites only the rows which have changed. It looks as if you have all the features you want, if only you use SQLite to keep data in a database file instead of trying to use the inmemory feature. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Safe saving of in-memory db to disk file
Hi! I'm going to use sqlite as means of the file format I/O for applications. One of the critical points in file I/O is saving the file in a safe way, so that data loss cannot happen (or at least the risk of happening is minimized as much as possible). Traditionally, some applications save their files with a temporary name, so that in the case of system failure you don't lose the old file and the new file at the same time, and then, when the file saving is finished, the old file is deleted, and the temporary one is renamed to replace it. I have read the backup API page (https://www.sqlite.org/backup.html) that shows how to read a sqlite db from disk to memory, and how to save it back to disk, but it doesn't talk about the topic of performing the save in a safe way. Do you have any recommendation for saving the inmemory db in a safe way? (by "safe" I mean I don't want the to lose both the old db file and the inmemory one --however losing the inmemory db would be reasonable, as it's what obviously happens in a power outage if you didn't save before). Would you do it with the sqlite API, or with the OS system calls? Another scenario of interest would be if the db is really huge and you consider the possibility of not overwriting the whole old file, but just committing the changes, in order to save unnecessary disk writes. The FAQ explains about atomic sqlite writes into the db that also prevent data loss... but... can you do that with an inmemory db? how? Thanks in advance!! ardi ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug report: crash when close blob handle after close_v2 db
On 14 Jul 2019, at 11:18am, Chaoji Li wrote: > This problem is only present for 3.28+. A sample test case is attached. Thank you for identifying this behaviour. I'm sure the development team will reply to your post. Attachments are automatically ignored by the mailing list. You can include your code in your message, or post it on a server and include a pointer. However, in this case you have included a good clear description of how to reproduce the problem and this should not be necessary. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug report: crash when close blob handle after close_v2 db
This problem is only present for 3.28+. A sample test case is attached. Basically, the flow is: 1. Open in-memory db A (we don't do anything about it). 2. Open db B from file test.db 3. Create a blob handle from B 4. close_v2 A 5. close_v2 B 6. close blob handle -> Segmentation fault The problem seems to go away if A is not created. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Link errors with SQLITE_OMIT_VIRTUALTABLE
Hi, In reply to https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg113512.html. Can you please accept this patch? Thanks, - Orgad This email and any files transmitted with it are confidential material. They are intended solely for the use of the designated individual or entity to whom they are addressed. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, use, distribution or copying of this communication is strictly prohibited and may be unlawful. If you have received this email in error please immediately notify the sender and delete or destroy any copy of this message ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
