[sqlite] Problem with page cache
The pcache1FetchStage2 function has "*(void **)pPage->page.pExtra = 0;" although this does not seem to be documented, nor is there an assertion in pcache1.c (although in pcache.c there is). Perhaps the documentation should be corrected. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bad database manager. No biscuit.
tl;dr: CSV is a useful standard which, like most things in computing, has a couple of 'gotchas'. Not all '.CSV' files are CSV files. Can't give details but I just identified a problem which had caused the SQL database owner £100,000 == US$130,000 == €117,000 of damage, mostly because end-of-day crashed three days in a row. Plus my company's fees. The cause was some or all of the following, depending on your philosophy. A) SQL schema had constraints defined for columns, but the person who did it didn't think of all the possible things that might go wrong, or didn't think they were all worth guarding against. B) A CSV file was imported by someone who didn't understand the concepts of escaping quote characters and how missing values are handled. C) Excel's CSV exporter does not conform to RFC 4180, the standard for CSV files. D) An organisational culture which allows one geek to do a special one-off operation, outside his specialist area, without another geek casting a skeptical eye over the procedure or results. E) Software which sanitises the data it put into the database very carefully, but didn't expect anything else to mess with the database. F) Original writers of the software no longer work for the company. New maintainers have never had anything serious go wrong before and didn't know how to debug serious problems. Good enough to do their regular job, not good enough to deal with emergencies. Situation was complicated by happening just after the Christmas holiday, so some people thought that Christmas disruption or an end-of-year run was part of the problem. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Not Null Constraint Issue?
On 17 Jan 2020, at 6:39pm, Justin Gielski wrote: > After looking into the issue a bit more, it appears the INTEGER value was > actually saved into the DB as an EMPTY not a NULL, which is currently allowed > by the database constraints. Could you show us the constraint you feel disallows those things ? We might be able to find a loophole in the exact way you phrased it. Also, could someone explain to me what EMPTY means here ? I've seen '' called "empty string" but it doesn't seem to be normal SQL language. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Not Null Constraint Issue?
After looking into the issue a bit more, it appears the INTEGER value was
actually saved into the DB as an EMPTY not a NULL, which is currently
allowed by the database constraints. This makes me feel a bit better, as
now we have a reason for the constraint not triggering.
INTEGER objects within the .NET Framework do not allow for empty values
which is why we didn't notice that it was saved this way in the DB. It
appears the issue was application side as your suggestions suspected.
We're looking into why, but your responses helped us to realize this so
thank you.
-Justin
On Thu, Jan 16, 2020 at 4:00 AM <
sqlite-users-requ...@mailinglists.sqlite.org> wrote:
> Send sqlite-users mailing list submissions to
> sqlite-users@mailinglists.sqlite.org
>
> To subscribe or unsubscribe via the World Wide Web, visit
>
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> or, via email, send a message with subject or body 'help' to
> sqlite-users-requ...@mailinglists.sqlite.org
>
> You can reach the person managing the list at
> sqlite-users-ow...@mailinglists.sqlite.org
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of sqlite-users digest..."
>
>
> Today's Topics:
>
>1. Re: Next Release? Visual release timeline? (R Smith)
>2. Re: Query Planner GROUP BY and HAVING clauses optimization?
> (Keith Medcalf)
>3. Re: Query Planner GROUP BY and HAVING clauses optimization?
> (Jean-Baptiste Gardette)
>4. Re: sqlite3_limit equivalent in System.Data.SQLite.dll
> (Keith Bertram)
>5. Re: sqlite3_limit equivalent in System.Data.SQLite.dll
> (Keith Medcalf)
>6. Not Null Constraint Issue? (Justin Gielski)
>7. Re: Not Null Constraint Issue? (Simon Slavin)
>8. Re: Not Null Constraint Issue? (R Smith)
>9. Test failures on GPFS (T J)
> 10. Re: Next Release? Visual release timeline? (Dominique Devienne)
>
>
> --
>
> Message: 1
> Date: Wed, 15 Jan 2020 17:54:24 +0200
> From: R Smith
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] Next Release? Visual release timeline?
> Message-ID:
> Content-Type: text/plain; charset=utf-8; format=flowed
>
> On 2020/01/15 1:24 PM, Richard Hipp wrote:
> > On 1/15/20, Dominique Devienne wrote:
> >> I like Lua's way to graphically visualize releases at
> >> https://www.lua.org/versions.html
> >>
> >>
> >> Please send javascript that will generate such a graph, either as SVG
> >> or as an HTML Canvas.
> >>
> >> (1) For improved display on mobile, consider making the graph vertical
> >> instead of horizontal.
> >>
> >> (2) Assume the data is a JSON array of pairs. The first element of
> >> each pair is the release name (ex: "3.30.0") and the second element is
> >> the time as a fractional year (ex: "2019.7775").
>
> We'd like to submit this layout as an option:
> https://sqlitespeed.com/sqlite_releases.html
>
> Shown alongside the current list in simple form. Tried a few layouts,
> not all work as well (SQLite releases are much more dense than Lua),
> finally settled on the above, but left some options open.
>
> It comes with some config structure in this form:
>
>var options = {
> parentId: 'versionHistoryGraph',
> width: 200,
> yearWidth: 100,
> heightPerYear: 300,
> heightPerVersion: 14, // needs to match the style for .version
> lineColor: "#22",
> yearBackground: "#EE",
> data:
>
> [["1.0",2000.6298197581566],["1.0.1",2000.6325576089437],["1.0.3",2000.6435090120922],["1.0.4",2000.659936116815],["1.0.5",2000.7064795801962],["1.0.8",2000.7502851927902],["1.0.9",2000.7749258498745],["1.0.10",2000.7804015514487],["1.0.12",2000.7968286561716],["1.0.14",2000.802304357746],["1.0.13",2000.802304357746],["1.0.15",2000.8132557608944],
>
> ...
>
> ["3.30.0",2019.7584987451517],["3.30.1",2019.7749258498745]]
>};
>
> Note: The Release-Date array must be given Ascending, else an additional
> sort step in Java is needed, but I think SQLite is better at that.
>
>
> I will send the full script directly via e-mail (not sure if the forum
> will allow the size), but it can of course also be directly copied from
> the above html.
>
> Official statement:
> We have solely created the content of that html page and this e-mail,
> for the purpose of using it in the sqlite public pages, or as they see
> fit, but it is free to all, and herewith donated to the public domain.
>
>
> Cheers,
> Davey Van Nes, Ryan Smith
>
>
>
>
>
>
>
> --
>
> Message: 2
> Date: Wed, 15 Jan 2020 09:05:01 -0700
> From: "Keith Medcalf"
> To: "SQLite mailing list"
> Subject: Re: [sqlite] Query Planner GROUP BY and HAVING clauses
> optimization?
> Message-ID:
> Content-Type: text/plain; charset="utf-8"
>
>
> On Wednesday, 15 January, 2020 02:06, Jean-Baptiste
[sqlite] Truncation/rounding error in strftime(x, 'unixepoch')
Hi, some time ago there was an error reported when running the testsuite on ix86, in the date.test/date-2.2c-*. The error happens as a string like 1237962480.003 gets parsed and rounded to 1237962480.002999... and is later truncated to 1237962480.002. The rounding error happend in date.c:parseModifier(...): p->iJD = (sqlite3_int64)r; i.e. the double r is truncated by casting it to int. Doing the following change fixes the error on ix86, and lets the testsuites pass on i586, x86_64, aarch64, ppc64le, ... - p->iJD = (sqlite3_int64)r; + p->iJD = (sqlite3_int64)(r + 0.5); Also compare with date.c:setRawDateNumber(...), where rounding is already applied: p->iJD = (sqlite3_int64)(r*8640.0 + 0.5); Kind regards, Stefan -- Stefan Brüns / Bergstraße 21 / 52062 Aachen home: +49 241 53809034 mobile: +49 151 50412019 signature.asc Description: This is a digitally signed message part. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.31.0 enters pre-release testing
Richard Hipp, on Friday, January 17, 2020 07:56 AM, wrote... > A terse summary of changes can be seen at > https://www.sqlite.org/draft/releaselog/current.html No gain in speed? :-) I love it when I see, "2% faster..." Thanks. josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll
That would be great. Thanks! Do you have an estimated time for this release? Keith -Original Message- From: sqlite-users On Behalf Of Joe Mistachkin Sent: Thursday, January 16, 2020 2:37 PM To: SQLite mailing list Subject: Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll I’ll raise the default limit for the next release of System.Data.SQLite. Sent from my iPhone > On Jan 16, 2020, at 2:01 PM, Keith Bertram wrote: > > Yes I recognize that this would be a problem. I plan on having no more than > 20-25 attachments. > > If I understand correctly, the only way to set the value above 10, is to > recompile the source and set the SQLITE_MAX_ATTACHED variable to a number > higher than my 20-25 and also below 125. I was hoping to just use the .dll > straight out of nuget. > > Keith > > -Original Message- > From: sqlite-users On > Behalf Of Simon Slavin > Sent: Thursday, January 16, 2020 10:19 AM > To: SQLite mailing list > Subject: Re: [sqlite] sqlite3_limit equivalent in > System.Data.SQLite.dll > >> On 16 Jan 2020, at 3:21pm, Keith Bertram wrote: >> >> Ok. I was hoping I could set the value to a value higher than 10 without >> compiling. I'm curious why the limit is set by default to 10. > > The actual limit is 125. You can set SQLITE_MAX_ATTACHED to more than 125. > > It's worth explaining why you wouldn't want to attach 200 databases to > the same connection. Each time you refer to an attached database, > SQLite has to search for that database in a list, meaning it has to > match the schema name, then iterate through, on average, n/2 entries > to find the attachment number. The longer that list gets, the slower > every operation not on 'main' or 'temp' gets. (It's possible that > SQLite hashes schema names, but I don't remember anyone ever saying > that.) > > I have seen posts to this list that say things like "We make one database > file per day, and I want to search them all at once so I want to attach 500 > files to my connection.". It's a terrible idea and would result in slow > performance. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlit > e.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=hmGTLOph1qd > _VnCqj81HzEWkDaxmYdIWRBdoFggzhj8=l90GvhOo_5uKc2d8JIuoVtb0fH4diec3z4T > sA-hoNTc=UbVOkNIBY7TVyWQB9v2LK-xBIWk_rpXp5sXbzOFNwr0=U-mtya649Yx5n > GJacNeBxjNHS1gBxc2IssQUKmPwgbA= > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlit > e.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=hmGTLOph1qd > _VnCqj81HzEWkDaxmYdIWRBdoFggzhj8=l90GvhOo_5uKc2d8JIuoVtb0fH4diec3z4T > sA-hoNTc=0j1bLQLJnI0uvx7lKmLkmB0OaagV1JNC-Uun80S1zA8=PNkitdI2S0362 > fPXmc4vWebzKrBumru-6krAevmX6U8= > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=hmGTLOph1qd_VnCqj81HzEWkDaxmYdIWRBdoFggzhj8=l90GvhOo_5uKc2d8JIuoVtb0fH4diec3z4TsA-hoNTc=0j1bLQLJnI0uvx7lKmLkmB0OaagV1JNC-Uun80S1zA8=PNkitdI2S0362fPXmc4vWebzKrBumru-6krAevmX6U8= ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite 3.31.0 enters pre-release testing
Our intent is that there will be no more changes to SQLite, other than documentation updates and bug fixes, prior to the 3.31.0 release. Consider the "Prerelease Snapshot" at https://sqlite.org/download.html to be a beta-release. Please test that snapshot and report any problems. A terse summary of changes can be seen at https://www.sqlite.org/draft/releaselog/current.html A release checklist has been set up at https://www.sqlite.org/src/ext/checklist/top/index As I write these words, the checklist is all gray. Items will turn green as they are checked off (or other colors if there are issues). The 3.31.0 release will occur when the checklist goes all-green. We hope to achieve this on or before 2020-01-31 - two weeks from today. You can see the full sequence of main-branch check-ins since the previous release at https://www.sqlite.org/src/timeline?from=release=trunk For a diff showing all changes since the previous release, edit the prior URL by replacing "/timeline" with "/vdiff". You can also click on any two nodes (the circles connected by arrows) on the timeline graph to see a diff between the two selected check-ins. Or, manually edit the URL to change the "from" and "to" attributes to a check-in hash prefix, branch name, of tag. -- 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
