Re: [sqlite] lost errors on writeback/fsync/close - does SQLite need to be fixed similary to PostgreSQL?
Huh, fascinating stuff. I'm not an sqlite developer but I can shed light on some of your questions. On Tue, 12 Feb 2019 at 09:54, Edwin Török wrote: > A very conservative interpretation of various fsync bugs in various OS > kernels [2][5] would suggest that: > > #1. the list of known OS issues [3] should be updated with an entry > similar to: "Linux kernels <4.13 do not guarantee to report errors > encountered during writeback on next fsync, therefore data corruption > can occur without SQLite knowing about it.", see [4]: > I haven't read all the links yet so forgive me if this is answered there, but when you refer to versions <4.13 is that referring to eg. the ext3/ext4 implementation in those versions, or is it a wider problem which affects _all_ filesystems? (I'm particularly interested in whether lustre is affected) > #2. errors not reported on fsync, but on close > > According to [5] "A writeback error may not actually be reported by > fsync(), however; other calls, such as close(), could return it. " > AFAICT sqlite3 only logs errors from close, and doesn't surface them to > the caller. > It is unclear the exact kernel versions that are affected by this, IIUC > from [2] then >= 4.16 would not be affect Postgresql beacuse it always > does an fsync before close. > Does SQLite follow WWPD here, or is it possible that SQLite3 in one > process calls close without fsync, gets the writeback error reported > there (which it only logs), and another process calls fsync and gets > success, wrongly concluding that the data has safely reached the disk? > (because you only get an error from an inode reported at most once) > You're correct that sqlite doesn't relay errors from close() back to the application. In normal circumstances however, it will always call fsync() before close(), because an fsync() is involved at the end of each transaction in sqlite. The exception is if you've played with the SYNCHRONOUS pragma, in which case you've explicitly asked sqlite to skip the fsync() and in this configuration an application can end up writing a corrupt DB without getting an error from sqlite (I have seen this happen in practice). But I'm hard pressed to fault sqlite for this behaviour, and the app can workaround it by resetting the SYNCHRONOUS pragma to the default and committing one last transaction to trigger fsync() before closing the DB. At least in theory -- as I said I haven't been through the links so maybe the bugs mean this workaround isn't reliable :) > #3 how does this affect multiple processes accessing same sqlite > database? > If inode errors are reported at most once, could it be that a writeback > error from changes made by process A actually get reported to process > B, and process A never learns about it? > If process A would always call fsync and close before relinquishing the > lock on the journal/WAL I think this wouldn't happen, but does SQLite > guarantee that? > sqlite guarantees that only one writer is active at any time. Ie. if process A is updating the DB via write() calls, the locking protocol ensures that no other process will call write() or fsync(), and in fact not even read() (because the DB might be in a partially-updated inconsistent state) before process A has finalised the transaction and called fsync(). sqlite does not close() the main DB's file descriptor while the application holds the connection open, only after sqlite3_close() is called. Thanks for the info, -Rowan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] why no unique columns on alter table
This is mainly for my curiosity. Is there any particular reason that one can't add a unique column on an alter table? With a default value of null they would all have unique values by default. Any insight into this would be great. Perhaps there' something obvious I'm missing. -- Mark ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem to understand "Persistent Loadable Extensions"
Hey guys! According to "Persistent Loadable Extensions” topic on https://www.sqlite.org/loadext.html if the initialization procedure returns SQLITE_OK_LOAD_PERMANENTLY (256) the extension should persist on the database file instead of just belong to the current connection. So I’ve downloaded the extension-functions.c and before compile that I’ve changed the the return of sqlite3_extension_init from 0 to 256. But still, my extension just exists on the current connection. Did I misunderstand the concept? Am I doing something wrong? Thanks Guys! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] lost errors on writeback/fsync/close - does SQLite need to be fixed similary to PostgreSQL?
Hi, I was wondering what changes SQLite3 would need in light of the fsync problems discovered by the PostgreSQL community (see "How is it possible that PostgreSQL used fsync incorrectly for 20 years, and what we'll do about it" talk [1]). [2] lists that MySQL and MongoDB did some changes, has SQLite done any? Luckily SQLite has a comprehensive failure injection test suite, could this testsuite be updated to simulate the kind of issues discovered by the PostgreSQL community in [2], i.e. IIUC: * some kernels only report errors once per inode, even if multiple processes access that inode (not guaranteed you get error reported in same process as the one initiating the write) * retrying fsync after a failure always reports success * if there is a writeback error you can read back pages older than what you most recently wrote * some kernels/FS may report errors on close but not on fsync A very conservative interpretation of various fsync bugs in various OS kernels [2][5] would suggest that: #1. the list of known OS issues [3] should be updated with an entry similar to: "Linux kernels <4.13 do not guarantee to report errors encountered during writeback on next fsync, therefore data corruption can occur without SQLite knowing about it.", see [4]: " If something goes wrong during writeback, it can be hard to report that error back to user space since the operation that caused that writeback in the first place will have long since completed. The kernel makes an attempt to save the error and report it on a subsequent system call, but it is easy for that information to be lost with the result that the application is unaware that it has lost data. " There is not much SQLite3 can do about this: if the OS never reports the error to the application it won't know about it, as [1] said Linux <4.13 is doomed. #2. errors not reported on fsync, but on close According to [5] "A writeback error may not actually be reported by fsync(), however; other calls, such as close(), could return it. " AFAICT sqlite3 only logs errors from close, and doesn't surface them to the caller. It is unclear the exact kernel versions that are affected by this, IIUC from [2] then >= 4.16 would not be affect Postgresql beacuse it always does an fsync before close. Does SQLite follow WWPD here, or is it possible that SQLite3 in one process calls close without fsync, gets the writeback error reported there (which it only logs), and another process calls fsync and gets success, wrongly concluding that the data has safely reached the disk? (because you only get an error from an inode reported at most once) #3 how does this affect multiple processes accessing same sqlite database? If inode errors are reported at most once, could it be that a writeback error from changes made by process A actually get reported to process B, and process A never learns about it? If process A would always call fsync and close before relinquishing the lock on the journal/WAL I think this wouldn't happen, but does SQLite guarantee that? #4 PostgreSQL's long term plans seem to be to use direct I/O It can take a very long time until all kernels used in production eventually get fixed to handle writeback/fsync properly, the talk in [1] focused on Linux, the situation with other OS is less known. Given that SQLite is portable across so many different OS, would it make sense to follow WWPD here and in the long term have SQLite perform direct I/O on its journals, and possibly data files as well to ensure it gets proper errors reported? [Although I would rather see the kernels fixed, instead of each application having to implement its own page cache, but then updated applications are easier to deploy than updated kernels. Perhaps database applications could collaborate on a portable library on top of direct I/O that has well defined error handling semantics to avoid the unknowns/bugs in OS kernels?] [1] https://fosdem.org/2019/schedule/event/postgresql_fsync/ [2] https://wiki.postgresql.org/wiki/Fsync_Errors [3] https://www.sqlite.org/howtocorrupt.html [4] https://lwn.net/Articles/724307/ [5] https://lwn.net/Articles/752613/ https://www.postgresql.org/message-id/flat/CAMsr%2BYHh%2B5Oq4xziwwoEfhoTZgr07vdGG%2Bhu%3D1adXx59aTeaoQ%40mail.gmail.com Best regards, --Edwin ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] MySQL Dumps into SQLite
Ah yes. I forgot about that. I don't use WITHOUT ROWID typically, so it didn't enter the thought process. On Mon, Feb 11, 2019 at 2:29 PM J. King wrote: > On February 11, 2019 2:19:27 PM EST, Stephen Chrzanowski < > pontia...@gmail.com> wrote: > >This is a dump from a MySQL table I created a few years ago. I'm not > >moving this particular database into SQLite, but, from what I learned > >today > >about MySQL dumps and the commenting system, I was kind of interested > >on > >how SQLite would handle the rest of the following statement: > > > >CREATE TABLE `Clusters` ( > > `ClusterID` int(11) NOT NULL auto_increment, > > `ClusterName` varchar(50) NOT NULL default '', > > `Description` text NOT NULL, > > `GroupID` int(11) NOT NULL default '0', > > `ClusterOrder` int(11) NOT NULL default '255', > > PRIMARY KEY (`ClusterID`) > >) ENGINE=InnoDB DEFAULT CHARSET=ascii; > > > >Obviously, SQLite doesn't know what ENGINE is, or anything of the sort > >(Actually, this statement didn't work due to the auto_increment > >keyword. > >Once I removed it, I got to the meat and potatoes of this question) > > > >The version of SQLite I'm using on this ancient machine is 3.5.9. I > >cannot > >upgrade it, as the OS is long out of support, isn't used by anyone > >other > >than our staff for a jump point to other servers, and there's so much > >legacy crap on this box that upgrading would break anything that is > >running > >on it. I know SQLite3 is mostly backwards compatible, but I'm not > >going to > >go playing with a production machine. ;) > > > >Could there be an inch of movement for the future so that in this > >particular example, SQLite would ignore everything between that final > >closing bracket and the semi-colon? Obviously a lost cause on this > >particular host, but, thinking about the future and all... > > > >(FWIW, I just tried on 3.20.0 and the ENGINE thing is still a "broken" > >thing) > >___ > >sqlite-users mailing list > >sqlite-users@mailinglists.sqlite.org > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > For what it's worth it's not possible to ignore anything after the closing > bracket: SQLite itself uses this space to declare WITHOUT ROWID tables. > -- > J. King > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] MySQL Dumps into SQLite
On February 11, 2019 2:19:27 PM EST, Stephen Chrzanowski wrote: >This is a dump from a MySQL table I created a few years ago. I'm not >moving this particular database into SQLite, but, from what I learned >today >about MySQL dumps and the commenting system, I was kind of interested >on >how SQLite would handle the rest of the following statement: > >CREATE TABLE `Clusters` ( > `ClusterID` int(11) NOT NULL auto_increment, > `ClusterName` varchar(50) NOT NULL default '', > `Description` text NOT NULL, > `GroupID` int(11) NOT NULL default '0', > `ClusterOrder` int(11) NOT NULL default '255', > PRIMARY KEY (`ClusterID`) >) ENGINE=InnoDB DEFAULT CHARSET=ascii; > >Obviously, SQLite doesn't know what ENGINE is, or anything of the sort >(Actually, this statement didn't work due to the auto_increment >keyword. >Once I removed it, I got to the meat and potatoes of this question) > >The version of SQLite I'm using on this ancient machine is 3.5.9. I >cannot >upgrade it, as the OS is long out of support, isn't used by anyone >other >than our staff for a jump point to other servers, and there's so much >legacy crap on this box that upgrading would break anything that is >running >on it. I know SQLite3 is mostly backwards compatible, but I'm not >going to >go playing with a production machine. ;) > >Could there be an inch of movement for the future so that in this >particular example, SQLite would ignore everything between that final >closing bracket and the semi-colon? Obviously a lost cause on this >particular host, but, thinking about the future and all... > >(FWIW, I just tried on 3.20.0 and the ENGINE thing is still a "broken" >thing) >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users For what it's worth it's not possible to ignore anything after the closing bracket: SQLite itself uses this space to declare WITHOUT ROWID tables. -- J. King ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] MySQL Dumps into SQLite
This is a dump from a MySQL table I created a few years ago. I'm not moving this particular database into SQLite, but, from what I learned today about MySQL dumps and the commenting system, I was kind of interested on how SQLite would handle the rest of the following statement: CREATE TABLE `Clusters` ( `ClusterID` int(11) NOT NULL auto_increment, `ClusterName` varchar(50) NOT NULL default '', `Description` text NOT NULL, `GroupID` int(11) NOT NULL default '0', `ClusterOrder` int(11) NOT NULL default '255', PRIMARY KEY (`ClusterID`) ) ENGINE=InnoDB DEFAULT CHARSET=ascii; Obviously, SQLite doesn't know what ENGINE is, or anything of the sort (Actually, this statement didn't work due to the auto_increment keyword. Once I removed it, I got to the meat and potatoes of this question) The version of SQLite I'm using on this ancient machine is 3.5.9. I cannot upgrade it, as the OS is long out of support, isn't used by anyone other than our staff for a jump point to other servers, and there's so much legacy crap on this box that upgrading would break anything that is running on it. I know SQLite3 is mostly backwards compatible, but I'm not going to go playing with a production machine. ;) Could there be an inch of movement for the future so that in this particular example, SQLite would ignore everything between that final closing bracket and the semi-colon? Obviously a lost cause on this particular host, but, thinking about the future and all... (FWIW, I just tried on 3.20.0 and the ENGINE thing is still a "broken" thing) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why Error: ambiguous column name: ProjID
Simon Slavin, on Monday, February 11, 2019 11:53 AM, wrote... >On 11 Feb 2019, at 4:51pm, Jose Isaias Cabrera wrote: > >> Thanks. Yes, sometimes I should revise 3 or 4 times before asking. :-) As >> soon as I sent it, I figured it out. > >It wouldn't help. You can read it ten time, rewrite it three times, and >explain > it to your pet duck. You still won't figure out the answer until you hit > 'send'. > It's called "the magic of posting". True. :-) >Glad you figured it out. Thanks. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why Error: ambiguous column name: ProjID
On 11 Feb 2019, at 4:51pm, Jose Isaias Cabrera wrote: > Thanks. Yes, sometimes I should revise 3 or 4 times before asking. :-) As > soon as I sent it, I figured it out. It wouldn't help. You can read it ten time, rewrite it three times, and explain it to your pet duck. You still won't figure out the answer until you hit 'send'. It's called "the magic of posting". Glad you figured it out. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why Error: ambiguous column name: ProjID
J. King, on Monday, February 11, 2019 11:25 AM, wrote... >On February 11, 2019 11:16:32 AM EST, Jose Isaias Cabrera >wrote: >> >>This SQL, >> >>SELECT a.*,b.*,c.Area,d.Bus_Area FROM Master_Project_List AS a >>LEFT JOIN Master_Project_List_Extra AS b ON a.ProjID = b.ProjID >>LEFT JOIN Bus_IT_Areas_ORGs AS c ON a.IT_OBS = c.IT_OBS >>LEFT JOIN Business_OBS_List AS d ON a.Business_OBS = d.Bus_OBS >>WHERE ProjID IN >>( >> SELECT ProjID FROM Master_Project_List WHERE ProjID = 'PR011894' >>) >> >> ORDER BY ProjID; >> >>is returning the error, >> >>Error: ambiguous column name: ProjID >> >>using SQLite version 3.26.0 2018-12-01 12:34:55. > >"...WHERE ProjID IN..." > >Which ProjID, a or b? Thanks. Yes, sometimes I should revise 3 or 4 times before asking. :-) As soon as I sent it, I figured it out. Thanks. josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why Error: ambiguous column name: ProjID
On February 11, 2019 11:16:32 AM EST, Jose Isaias Cabrera wrote: > >This SQL, > >SELECT a.*,b.*,c.Area,d.Bus_Area FROM Master_Project_List AS a >LEFT JOIN Master_Project_List_Extra AS b ON a.ProjID = b.ProjID >LEFT JOIN Bus_IT_Areas_ORGs AS c ON a.IT_OBS = c.IT_OBS >LEFT JOIN Business_OBS_List AS d ON a.Business_OBS = d.Bus_OBS >WHERE ProjID IN >( > SELECT ProjID FROM Master_Project_List WHERE ProjID = 'PR011894' >) > > ORDER BY ProjID; > >is returning the error, > >Error: ambiguous column name: ProjID > >using SQLite version 3.26.0 2018-12-01 12:34:55. > >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users "...WHERE ProjID IN..." Which ProjID, a or b? -- J. King ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why Error: ambiguous column name: ProjID
Never mind. Sorry guys for the wasted bandwidth. From: sqlite-users on behalf of Jose Isaias Cabrera Sent: Monday, February 11, 2019 11:16 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Why Error: ambiguous column name: ProjID This SQL, SELECT a.*,b.*,c.Area,d.Bus_Area FROM Master_Project_List AS a LEFT JOIN Master_Project_List_Extra AS b ON a.ProjID = b.ProjID LEFT JOIN Bus_IT_Areas_ORGs AS c ON a.IT_OBS = c.IT_OBS LEFT JOIN Business_OBS_List AS d ON a.Business_OBS = d.Bus_OBS WHERE ProjID IN ( SELECT ProjID FROM Master_Project_List WHERE ProjID = 'PR011894' ) ORDER BY ProjID; is returning the error, Error: ambiguous column name: ProjID using SQLite version 3.26.0 2018-12-01 12:34:55. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Why Error: ambiguous column name: ProjID
This SQL, SELECT a.*,b.*,c.Area,d.Bus_Area FROM Master_Project_List AS a LEFT JOIN Master_Project_List_Extra AS b ON a.ProjID = b.ProjID LEFT JOIN Bus_IT_Areas_ORGs AS c ON a.IT_OBS = c.IT_OBS LEFT JOIN Business_OBS_List AS d ON a.Business_OBS = d.Bus_OBS WHERE ProjID IN ( SELECT ProjID FROM Master_Project_List WHERE ProjID = 'PR011894' ) ORDER BY ProjID; is returning the error, Error: ambiguous column name: ProjID using SQLite version 3.26.0 2018-12-01 12:34:55. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite 3.37.1: void function returns value
Dominique, what I said was that it is undefined behaviour in C++ to return a *value* in a void function. That is still true. On Mon, Feb 11, 2019 at 2:49 PM Dominique Devienne wrote: > On Mon, Feb 11, 2019 at 1:11 PM Clemens Ladisch > wrote: > > > Peter da Silva wrote: > > > I am pretty sure that the code is not legal C > > > > Indeed; C99 and C11 say in 6.3.2.2: > > | The (nonexistent) value of a void expression (an expression that has > > | type void) shall not be used in any way [...] > > and in 6.8.6.4: > > | A return statement with an expression shall not appear in a function > > | whose return type is void. > > > > Good to know. Thanks. I was merely pointing out that the OP's > "in C++ this is undefined behaviour" wasn't always true. --DD > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I assign negative value to the INTEGER PRIMARY KEY column?
On 2/11/19, J. King wrote: > > Rowids are signed integers and can be negative. The documentation does > advise (somewhere...) against using negative rowids because they are larger > (when stored) than the typical used range of positive ones, but that's it. Exactly. Negative rowids work fine. It is only that the rowid values are stored using the "varint" format which uses between 1 and 9 bytes to store a 64-bit integer. Small non-negative values like 1 and 2 use just 1 byte. But -1 and -2 and all other negative numbers use all 9 bytes. It is only a disk space compactness issue, and in the OPs case there are only two rows involved, so the impact will be unmeasurably small. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite 3.37.1: void function returns value
On Mon, Feb 11, 2019 at 1:11 PM Clemens Ladisch wrote: > Peter da Silva wrote: > > I am pretty sure that the code is not legal C > > Indeed; C99 and C11 say in 6.3.2.2: > | The (nonexistent) value of a void expression (an expression that has > | type void) shall not be used in any way [...] > and in 6.8.6.4: > | A return statement with an expression shall not appear in a function > | whose return type is void. > Good to know. Thanks. I was merely pointing out that the OP's "in C++ this is undefined behaviour" wasn't always true. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I assign negative value to the INTEGER PRIMARY KEY column?
On February 11, 2019 8:35:57 AM EST, John Smith wrote: >Hi, > > >I read in SQLite documentation that if I define column of type INTEGER >PRIMARY KEY then this column will become an alias to SQLite internal >64-bit integer index that uniquely identifies the row (hence ‘rowid’). > >I also read that the initial default value that will be used for such >column is 1. > > >My question: > >I am migrating data from a previous schema to my new schema, which >wants to use this INTEGER PRIMARY KEY capability. > >The thing is that in my old schema the integer indexes, which needs to >be mapped to the new INTEGER PRIMARY KEY column, starts with value -2 >(minus two). > >The values are unique and are going up, but always start at (-2). > >Since my schema contains many relations I do not wish to modify these >indexes. > >My question – if I copy the indexes values as-is, so some are negative, >will my INTEGER PRIMARY KEY column still be an alias to the SQLite >internal ‘rowid’ column, or will such values break this alias >connection? > >Many thanks, Paz >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Rowids are signed integers and can be negative. The documentation does advise (somewhere...) against using negative rowids because they are larger (when stored) than the typical used range of positive ones, but that's it. -- J. King ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can I assign negative value to the INTEGER PRIMARY KEY column?
Hi, I read in SQLite documentation that if I define column of type INTEGER PRIMARY KEY then this column will become an alias to SQLite internal 64-bit integer index that uniquely identifies the row (hence ‘rowid’). I also read that the initial default value that will be used for such column is 1. My question: I am migrating data from a previous schema to my new schema, which wants to use this INTEGER PRIMARY KEY capability. The thing is that in my old schema the integer indexes, which needs to be mapped to the new INTEGER PRIMARY KEY column, starts with value -2 (minus two). The values are unique and are going up, but always start at (-2). Since my schema contains many relations I do not wish to modify these indexes. My question – if I copy the indexes values as-is, so some are negative, will my INTEGER PRIMARY KEY column still be an alias to the SQLite internal ‘rowid’ column, or will such values break this alias connection? Many thanks, Paz ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Was there an announcement of 3.27?
On 9/2/62 03:31, Dominique Pellé wrote:
David Raymond wrote:
SQLite version 3.27.1 is now available on the SQLite website:
https://sqlite.org/
https://sqlite.org/download.html
https://sqlite.org/releaselog/3_27_1.html
Release notes https://sqlite.org/releaselog/3_27_1.html say:
=== BEGIN QUOTE ===
Added the remove_diacritics=2 option to FTS3 and FTS5.
=== END QUOTE ===
I wonder that this does. FTS3 or FTS5 doc were not updated,
since they only document remove_diacritics=0 and 1.
Thanks for reporting this. And the typos. I just added the following to
the docs:
remove_diacritics:
This option should be set to "0", "1" or "2". The default value is
"1". If it is set to "1" or "2", then diacritics are removed from Latin
script characters as described above. However, if it is set to "1", then
diacritics are not removed in the fairly uncommon case where a single
unicode codepoint is used to represent a character with more that one
diacritic. For example, diacritics are not removed from codepoint 0x1ED9
("LATIN SMALL LETTER O WITH CIRCUMFLEX AND DOT BELOW"). This is
technically a bug, but cannot be fixed without creating backwards
compatibility problems. If this option is set to "2", then diacritics
are correctly removed from all Latin characters.
Dan.
I also use the opportunity to report a few typos in
https://sqlite.org/fts5.html:
- the second character replaced with an *asterix* (-> asterisk)
- fts5 extension function made as part *of of* (repeated word "of")
- *an the* (-> the) auxiliary data is set to NULL
Regards
Dominique
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite 3.37.1: void function returns value
Peter da Silva wrote: > I am pretty sure that the code is not legal C Indeed; C99 and C11 say in 6.3.2.2: | The (nonexistent) value of a void expression (an expression that has | type void) shall not be used in any way [...] and in 6.8.6.4: | A return statement with an expression shall not appear in a function | whose return type is void. (And it has already been fixed two hours ago.) Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite 3.37.1: void function returns value
> On Feb 11, 2019, at 6:33 AM, Peter da Silva wrote: > > I am pretty sure that the code is not legal C because it's using the return > value of a void function, as well as returning a value from a void > function. Compilers that "do what I mean" and accept it are in error. It's > certainly possible that some obscure clause in some C standard blesses it > but I can't imagine why they would. I would need to dig through the relevant Standards to confirm, but my memory was that C++ added this feature to help with templates (you might have a template where the return value was templates on type, so return was a logical possibility). C did not need this, so didn’t adopt it (though possible some later version did for compatibility). Many C Compilers are also C++.Compilers and often accept code that uses features of one language that aren’t in the other as an extension, generating a warning only if a ‘be fussy’ flag is set. (Few compilers are fully conforming to the Standard by default). Even if this was adopted by C in some later Standard, the use of this syntax would be a needless requirement for a much later version of the Standard than would otherwise be needed. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite 3.37.1: void function returns value
I am pretty sure that the code is not legal C because it's using the return value of a void function, as well as returning a value from a void function. Compilers that "do what I mean" and accept it are in error. It's certainly possible that some obscure clause in some C standard blesses it but I can't imagine why they would. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite 3.37.1: void function returns value
I'm using cl.exe v19.12.
To summarize:
SQLite 3.24 compiles fine with warning as error enabled with cl.exe v19.12
SQLite 3.27.1 does not compile with warning as error enabled with cl.exe
v19.12.
To me, it looks like a simple fix to avoid writing "return " in void
functions even if is a void function. It just looks broken and
obviously some compilers does not handle this correctly.
FWIW, cl.exe v19.12 does not complain about your example code
(return-coid.cpp)
If you convert your program to C:
#include
void fa(int i)
{
if (i == 2)
return;
printf("%d\n", i);
} // implied return;
int fb(int i)
{
if (i > 4)
return 4;
printf("%d\n", i);
return 2;
}
typedef struct {
const char* p;
int x;
} pair;
pair fc(const char* p, int x)
{
pair r;
r.p = p;
r.x = x;
return r;
}
void fd()
{
return fa(10); // fa(10) is a void expression
}
int main()
{
fa(2); // returns, does nothing when i==2
fa(1); // prints its argument, then returns
int i = fb(5); // returns 4
i = fb(i); // prints its argument, returns 2
printf("%d\n", i);
printf("%d\n", fc("Hello", 7).x);
fd();
}
The compiler complains:
Z:\retvoid>cl /EHsc /W4 return-void.c
Microsoft (R) C/C++ Optimizing Compiler Version 19.12.25831 for x86
Copyright (C) Microsoft Corporation. All rights reserved.
return-void.c
return-void.c(33): warning C4098: 'fd': 'void' function returning a value
Microsoft (R) Incremental Linker Version 14.12.25831.0
Copyright (C) Microsoft Corporation. All rights reserved.
/out:return-void.exe
return-void.obj
/Jonas
On Mon, Feb 11, 2019 at 11:58 AM Dominique Devienne
wrote:
> On Mon, Feb 11, 2019 at 11:31 AM Jonas Bülow
> wrote:
>
> > Sorry, I missed some information. It is the MSVC v15.5 compiler that
> > complains:
> >
> > sqlite3.c(58167): error C2220: warning treated as error - no 'object'
> file
> > generated [c:\work\sqlite-amalgamation-3270100\sqlite3.vcxproj]
> > sqlite3.c(58167): warning C4098: 'sqlite3PagerSnapshotUnlock': 'void'
> > function returning a value
> > [c:\work\sqlite-amalgamation-3270100\sqlite3.vcxproj]
> > Done Building Project "c:\sqlite-amalgamation-3270100\sqlite3.vcxproj"
> > (default targets) -- FAILED.
> >
>
> Buggy compiler? Using VS2017 (cl.exe 19.10) there are no warnings with the
> CppReference code example.
> But again, might depend on sqlite3WalSnapshotUnlock() being void or not.
> --DD
>
> PS: /Wall spews tons of warnings, in MS's own std lib headers...
>
> d:\my\demo>cl /nologo /EHsc /W1 return-void.cpp
> return-void.cpp
>
> d:\my\demo>cl /nologo /EHsc /W2 return-void.cpp
> return-void.cpp
>
> d:\my\demo>cl /nologo /EHsc /W3 return-void.cpp
> return-void.cpp
>
> d:\my\demo>cl /nologo /EHsc /W4 return-void.cpp
> return-void.cpp
>
> d:\my\demo>type return-void.cpp
> #include
> #include
> #include
>
> void fa(int i)
> {
> if (i == 2)
> return;
> std::cout << i << '\n';
> } // implied return;
>
> int fb(int i)
> {
> if (i > 4)
> return 4;
> std::cout << i << '\n';
> return 2;
> }
>
> std::pair fc(const char* p, int x)
> {
> return {p, x};
> }
>
> void fd()
> {
> return fa(10); // fa(10) is a void expression
> }
>
> int main()
> {
> fa(2); // returns, does nothing when i==2
> fa(1); // prints its argument, then returns
> int i = fb(5); // returns 4
> i = fb(i); // prints its argument, returns 2
> std::cout << i << '\n'
> << fc("Hello", 7).second << '\n';
> fd();
> }
>
> d:\my\demo>return-void.exe
> 1
> 4
> 2
> 7
> 10
>
> d:\my\demo>cl /EHsc /W4 return-void.cpp
> Microsoft (R) C/C++ Optimizing Compiler Version 19.10.25019 for x64
> Copyright (C) Microsoft Corporation. All rights reserved.
>
> return-void.cpp
> Microsoft (R) Incremental Linker Version 14.10.25019.0
> Copyright (C) Microsoft Corporation. All rights reserved.
>
> /out:return-void.exe
> return-void.obj
>
> d:\my\demo>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite 3.37.1: void function returns value
On Mon, Feb 11, 2019 at 11:31 AM Jonas Bülow wrote:
> Sorry, I missed some information. It is the MSVC v15.5 compiler that
> complains:
>
> sqlite3.c(58167): error C2220: warning treated as error - no 'object' file
> generated [c:\work\sqlite-amalgamation-3270100\sqlite3.vcxproj]
> sqlite3.c(58167): warning C4098: 'sqlite3PagerSnapshotUnlock': 'void'
> function returning a value
> [c:\work\sqlite-amalgamation-3270100\sqlite3.vcxproj]
> Done Building Project "c:\sqlite-amalgamation-3270100\sqlite3.vcxproj"
> (default targets) -- FAILED.
>
Buggy compiler? Using VS2017 (cl.exe 19.10) there are no warnings with the
CppReference code example.
But again, might depend on sqlite3WalSnapshotUnlock() being void or not.
--DD
PS: /Wall spews tons of warnings, in MS's own std lib headers...
d:\my\demo>cl /nologo /EHsc /W1 return-void.cpp
return-void.cpp
d:\my\demo>cl /nologo /EHsc /W2 return-void.cpp
return-void.cpp
d:\my\demo>cl /nologo /EHsc /W3 return-void.cpp
return-void.cpp
d:\my\demo>cl /nologo /EHsc /W4 return-void.cpp
return-void.cpp
d:\my\demo>type return-void.cpp
#include
#include
#include
void fa(int i)
{
if (i == 2)
return;
std::cout << i << '\n';
} // implied return;
int fb(int i)
{
if (i > 4)
return 4;
std::cout << i << '\n';
return 2;
}
std::pair fc(const char* p, int x)
{
return {p, x};
}
void fd()
{
return fa(10); // fa(10) is a void expression
}
int main()
{
fa(2); // returns, does nothing when i==2
fa(1); // prints its argument, then returns
int i = fb(5); // returns 4
i = fb(i); // prints its argument, returns 2
std::cout << i << '\n'
<< fc("Hello", 7).second << '\n';
fd();
}
d:\my\demo>return-void.exe
1
4
2
7
10
d:\my\demo>cl /EHsc /W4 return-void.cpp
Microsoft (R) C/C++ Optimizing Compiler Version 19.10.25019 for x64
Copyright (C) Microsoft Corporation. All rights reserved.
return-void.cpp
Microsoft (R) Incremental Linker Version 14.10.25019.0
Copyright (C) Microsoft Corporation. All rights reserved.
/out:return-void.exe
return-void.obj
d:\my\demo>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite 3.37.1: void function returns value
Sorry, I missed some information. It is the MSVC v15.5 compiler that complains: sqlite3.c(58167): error C2220: warning treated as error - no 'object' file generated [c:\work\sqlite-amalgamation-3270100\sqlite3.vcxproj] sqlite3.c(58167): warning C4098: 'sqlite3PagerSnapshotUnlock': 'void' function returning a value [c:\work\sqlite-amalgamation-3270100\sqlite3.vcxproj] Done Building Project "c:\sqlite-amalgamation-3270100\sqlite3.vcxproj" (default targets) -- FAILED. On Mon, Feb 11, 2019 at 11:21 AM Dominique Devienne wrote: > On Mon, Feb 11, 2019 at 11:16 AM Jonas Bülow > wrote: > > > > Just tried to update my sqlite version from 3.24 to 3.27.1 and the > > > compiler complained about a void function returning a value. I don't > know > > > about C, but in C++ this is undefined behaviour and the clang compiler > > > sometimes generate an ud2 instruction for such code. > > > > Not if the expression of the return is itself void, i.e. > sqlite3WalSnapshotUnlock() is also a void function. (haven't checked). > See https://en.cppreference.com/w/cpp/language/return which is explicit > about it, and even shows it in an example. --DD > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite 3.37.1: void function returns value
On Mon, Feb 11, 2019 at 11:16 AM Jonas Bülow wrote: > > Just tried to update my sqlite version from 3.24 to 3.27.1 and the > > compiler complained about a void function returning a value. I don't know > > about C, but in C++ this is undefined behaviour and the clang compiler > > sometimes generate an ud2 instruction for such code. > Not if the expression of the return is itself void, i.e. sqlite3WalSnapshotUnlock() is also a void function. (haven't checked). See https://en.cppreference.com/w/cpp/language/return which is explicit about it, and even shows it in an example. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite 3.37.1: void function returns value
The subject should say "3.27.1". Sorry!
On Mon, Feb 11, 2019 at 11:14 AM Jonas Bülow wrote:
> Just tried to update my sqlite version from 3.24 to 3.27.1 and the
> compiler complained about a void function returning a value. I don't know
> about C, but in C++ this is undefined behaviour and the clang compiler
> sometimes generate an ud2 instruction for such code.
>
> It's on line 58165 in (amalgamation) sqlite3.c.
>
> /*
> ** Release a lock obtained by an earlier successful call to
> ** sqlite3PagerSnapshotCheck().
> */
> SQLITE_PRIVATE void sqlite3PagerSnapshotUnlock(Pager *pPager){
> assert( pPager->pWal );
> return sqlite3WalSnapshotUnlock(pPager->pWal);
> }
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite 3.37.1: void function returns value
Just tried to update my sqlite version from 3.24 to 3.27.1 and the compiler
complained about a void function returning a value. I don't know about C,
but in C++ this is undefined behaviour and the clang compiler sometimes
generate an ud2 instruction for such code.
It's on line 58165 in (amalgamation) sqlite3.c.
/*
** Release a lock obtained by an earlier successful call to
** sqlite3PagerSnapshotCheck().
*/
SQLITE_PRIVATE void sqlite3PagerSnapshotUnlock(Pager *pPager){
assert( pPager->pWal );
return sqlite3WalSnapshotUnlock(pPager->pWal);
}
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Displaying hierarchical structure
Ignore this.
Was mailed over 1w ago and only came through and I have figured this out
after
studying the CTE documentation on the SQLite site.
RBS
On Wed, Feb 6, 2019 at 7:24 PM Bart Smissaert
wrote:
>
> I can select the rank as in the previous e-mail with this recursive query:
>
> with recursive paths(id, folder, path) as
> (select id, folder, folder from folders where parent_id is null union
> select folders.id, folders.folder, paths.path || '-' ||
> substr('0', length(folders.id)) || folders.id from folders join
> paths
> where folders.parent_id = paths.id)
> select replace(path, 'Main', '01') as path from paths order by path
>
> Not managed yet though to use this to update the rank column in the table
> Folders.
> Also not sure how to avoid the replace and get the rank value 1
> directly from the ID.
>
> Any idea how to manage these two?
>
> RBS
>
> On Mon, Feb 4, 2019 at 10:41 PM Bart Smissaert
> wrote:
>
>> Looking at this approach of a hierarchical system:
>> https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql
>>
>> Given a table like this:
>>
>> ID PARENT_ID FOLDER RANK
>> ---
>> 1 0 Main1
>> 2 1 CC 1-02
>> 3 1 BB 1-03
>> 4 1 AA 1-04
>> 5 2 B 1-02-05
>> 6 2 A 1-02-06
>>
>> What SQL should I use to update the field RANK if the first row is known
>> to be 01, but all the
>> next rows are null? I tried with a non-recursive query, but couldn't work
>> it out.
>>
>> RBS
>>
>>
>>
>> On Thu, Jan 31, 2019 at 8:02 AM Bart Smissaert
>> wrote:
>>
>>> This looks a nice and simple way to display the tree in the right order
>>> without recursive SQL:
>>>
>>> https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql
>>>
>>> Will do some testing on large numbers to see how the 2 methods compare
>>> speed-wise.
>>>
>>> RBS
>>>
>>> On Tue, Jan 29, 2019 at 8:33 PM Keith Medcalf
>>> wrote:
>>>
See https://sqlite.org/lang_with.html
which includes how to traverse the recursive tree in either depth-first
or breadth-first order.
Why do you need the closure table at all?
create table folders
(
idinteger primary key,
parent_id integer references folders,
name text not null collate nocase,
check (not (parent_id is null and id != 1))
);
insert into folders values (1, null, 'Folder1'),
(2, 1, 'Folder2'),
(3, 1, 'Folder3'),
(4, 1, 'Folder4'),
(5, 2, 'Folder5'),
(6, 2, 'Folder6');
.head on
.mode column
.width 30 9 38
-- depth first
with foo (id, parent_id, name, level, path)
as (select folders.*, 0, folders.name
from folders
where parent_id is null
union all
select folders.*, level + 1, foo.path || '\' || folders.name
from foo, folders
where folders.parent_id = foo.id
order by 4
)
select substr('', 1, (level - 1) * 4) || name as
Folder,
coalesce(parent_id, 0) as PARENT_ID,
path as FullPath
from foo;
-- breadth first
with foo (id, parent_id, name, level, path)
as (select folders.*, 0, folders.name
from folders
where parent_id is null
union all
select folders.*, level + 1, foo.path || '\' || folders.name
from foo, folders
where folders.parent_id = foo.id
order by 4 desc
)
select substr('', 1, (level - 1) * 4) || name as
Folder,
coalesce(parent_id, 0) as PARENT_ID,
path as FullPath
from foo;
SQLite version 3.27.0 2019-01-28 00:42:06
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table folders
...> (
...>idinteger primary key,
...>parent_id integer references folders,
...>name text not null collate nocase,
...>check (not (parent_id is null and id != 1))
...> );
sqlite>
sqlite> insert into folders values (1, null, 'Folder1'),
...>(2, 1, 'Folder2'),
...>(3, 1, 'Folder3'),
...>
