[sqlite] Running Sqlite on 64-bit/Client-server data base
On 15 Dec 2015, at 11:47pm, Hamdan Alabsi wrote: > Also, does sqlite support client-server database engine ? No. Your program calls SQLite API routines. Those routines read and write the file. There is no server. Simon.
[sqlite] about attach database
hi,all There are two ways to open a database. 1.sqlite3_open 2.ATTACH DATABASE Because there are so many data base. So we used attach database to open them. But the efficiency of the programming is not ideal. which one is faster? Is the efficiency between the two methods great? best regards. wqg
[sqlite] about attach database
I mean only compare the two ways of get the database handl. 1.sqlite3_open 2.ATTACH DATABASE Do not consider the next operation, such as select,update and so on. At 2015-12-16 10:51:31, "Richard Hipp" wrote: >On 12/15/15, ??? <2004wqg2008 at 163.com> wrote: >> hi,all >> There are two ways to open a database. >> 1.sqlite3_open >> 2.ATTACH DATABASE >> >> Because there are so many data base. So we used attach database to open >> them. >> But the efficiency of the programming is not ideal. >> >> which one is faster? >> Is the efficiency between the two methods great? >> > >I think both methods are about the same speed. Have you measured a >difference between them? They both do about the same amount of work, >I think. > >-- >D. Richard Hipp >drh at sqlite.org >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] about attach database
After testing the Sqlite3_open and ATTACH DATABASE, I found that the attach database is slower than sqlite3_open. there is attachment after the mail which includ the speed information ( millisecond ). At 2015-12-16 10:59:27, "Richard Hipp" wrote: >On 12/15/15, ??? <2004wqg2008 at 163.com> wrote: >>I mean only compare the two ways of get the database handl. >> 1.sqlite3_open >> 2.ATTACH DATABASE > >I think they both do about the same amount of work. > >-- >D. Richard Hipp >drh at sqlite.org >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] about attach database
After testing the Sqlite3_open and ATTACH DATABASE, I found that the attach database is slower than sqlite3_open. there is attachment after the mail which includ the speed information ( millisecond ).
[sqlite] about attach database
??? <2004wqg2008 at 163.com> wrote: > > After testing the Sqlite3_open and ATTACH DATABASE, > I found that the attach database is slower than sqlite3_open. > there is attachment after the mail which includ the speed > information ( millisecond ). Your attachment was discarded (attachment not allowed in this mailing list). Anyway, I remember observing that: - sqlite3_open_v2(...) is lazy. In other words, it does not parse the schema of the DB until the first query is performed after opening the database. - ATTACH is not lazy. The schema is parsed as soon as you attach a database. That could explain the difference in speed. Would there be a way to make ATTACH lazy by the way? Regards Dominique
[sqlite] about attach database
On Tue, Dec 15, 2015 at 11:19 PM, Dominique Pell? wrote: > ??? <2004wqg2008 at 163.com> wrote: > > > > > After testing the Sqlite3_open and ATTACH DATABASE, > > I found that the attach database is slower than sqlite3_open. > > there is attachment after the mail which includ the speed > > information ( millisecond ). > > > Your attachment was discarded (attachment not allowed in this > mailing list). > > Anyway, I remember observing that: > > - sqlite3_open_v2(...) is lazy. In other words, it does not parse the > schema of the DB until the first query is performed after opening > the database. > - ATTACH is not lazy. The schema is parsed as soon as you > attach a database. > > That could explain the difference in speed. > Would there be a way to make ATTACH lazy by the way? > Why would that be of benefit to you? Are you intending to attach a database and never use it? It seems to me the same amount of time will be taken either way. When it comes to opening a database, there may be a need to do some connection specific configuration prior to actually opening the database file and parsing the schema. I believe this is the reason why open defers that processing until later, giving you a chance to finish configuring your connection before locking it down. Once that configuration is complete, there is no advantage to deferring the open of the database. I say no advantage ... maybe I just can't think of one. Why do you think there would be an advantage to deferring the open & schema processing of an attached database? -- Scott Robison
[sqlite] Index on computed value?
Is it possible to have an index on a computer value? E.g. I have a 40 byte value in one of my columns. I only want an index over the first 4 bytes of it. However, I don't really want to repeat those 4 bytes inside another column on the main table. Is there any way to accomplish that? - Deon
[sqlite] about attach database
Scott Robison wrote: > On Tue, Dec 15, 2015 at 11:19 PM, Dominique Pell? gmail.com >> wrote: > >> ??? <2004wqg2008 at 163.com> wrote: >> >> > >> > After testing the Sqlite3_open and ATTACH DATABASE, >> > I found that the attach database is slower than sqlite3_open. >> > there is attachment after the mail which includ the speed >> > information ( millisecond ). >> >> >> Your attachment was discarded (attachment not allowed in this >> mailing list). >> >> Anyway, I remember observing that: >> >> - sqlite3_open_v2(...) is lazy. In other words, it does not parse the >> schema of the DB until the first query is performed after opening >> the database. >> - ATTACH is not lazy. The schema is parsed as soon as you >> attach a database. >> >> That could explain the difference in speed. >> Would there be a way to make ATTACH lazy by the way? >> > > Why would that be of benefit to you? Are you intending to attach a database > and never use it? It seems to me the same amount of time will be taken > either way. > > When it comes to opening a database, there may be a need to do some > connection specific configuration prior to actually opening the database > file and parsing the schema. I believe this is the reason why open defers > that processing until later, giving you a chance to finish configuring your > connection before locking it down. Once that configuration is complete, > there is no advantage to deferring the open of the database. > > I say no advantage ... maybe I just can't think of one. Why do you think > there would be an advantage to deferring the open & schema processing of an > attached database? Laziness can be useful in some cases. I have an application that opens hundred or so of database connections. Being able to open all connections at start-up is simple. Since it's lazy, it's also fast and does not use memory to store schemas until the databases are actually used later. In my application, queries happen in only few connections after start-up out of all opened connections. For many connections, queries happen much later or sometimes do not even happen. Laziness is thus useful to make start-up fast and simple, without application having to implement laziness itself. I see that the original message from ??? says "Because there are so many database [...]", so it seems to be the same scenario as in my application in which laziness is quite useful. I'm not 100% sure but I'm quite confident that laziness is the explanation for performance discrepancy between sqlite3_open*() and ATTACH. If laziness was useless, why would it then be already implemented for sqlite3_open_v2(...)? Having said all that, reading https://www.sqlite.org/c3ref/open.html I see no mention of the fact that sqlite3_open*() is lazy. Is it documented somewhere? Regards Dominique
[sqlite] bug when columns are missing in embedded subselect
This has been discussed several times on the list. SQLite (and all other databases) try very hard to resolve the names you refer to in your query and will search all the tables you mention to find *unqualified* references. They give up if they do not find exactly one definition. Try " delete from inflight where inflight.fp in (select flightplans.fp from flightplans); -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Karl Lehenbauer Gesendet: Dienstag, 15. Dezember 2015 20:50 An: sqlite-users at mailinglists.sqlite.org Betreff: [sqlite] bug when columns are missing in embedded subselect Consider the following table definitions: DROP TABLE IF EXISTS flightplans; CREATE TABLE flightplans ( id text NOT NULL, ident text, recvd integer, orig text, dest text, PRIMARY KEY (id) ); DROP TABLE IF EXISTS inflight; CREATE TABLE inflight ( fp text, ident text, alt integer, clock integer NOT NULL DEFAULT 0, gs integer, heading integer, lat real, lon real, reg text, squawk int, primary key (fp) ); It is an error to select a column that doesn?t exist? sqlite> select fp from flightplans; Error: no such column: fp But if I select a column that doesn?t exist within an embedded subquery, it is not an error? sqlite> delete from inflight where fp in (select fp from flightplans); sqlite> (In the above example, unless I am mistaken, it should produce more or less the same ?no such column? error.) In my ?real life? version of this stuff where it has a fair number of rows in the tables, it appears to be an infinite loop, like with < 100K rows in each table I aborted the statement after more than 20 minutes of CPU time. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] Running Sqlite on 64-bit/Client-server data base
Hi Hamdan, These are some very basic questions (as others have mentioned), and may not be your only questions at this point. To fully understand how SQLite implements databasing and what it is best suited for (or what it isn't useful for), your best bet is to take a look at these pages: http://www.sqlite.org/about.html http://www.sqlite.org/features.html http://www.sqlite.org/whentouse.html These are concise overviews of everything you need to know before making that decision. SQLite has one of the best support communities, so if you do use it, help will always be an e-mail away. Do try to first google the question before posting it so that we don't rehash so much. (As programmers, our greatest fear is infinite recursion). Good luck! Ryan On 2015/12/16 1:47 AM, Hamdan Alabsi wrote: > Greetings Everyone, > Hope all is well. I am wondering if I can run Sqlite on 64-bit machine? > Also, does sqlite support client-server database engine ? I hope I can get > the answers from you very soon. Thank you. > > Best regards, > Hamdan > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] about attach database
On 12/16/2015 12:51 PM, ??? wrote: > After testing the Sqlite3_open and ATTACH DATABASE, > I found that the attach database is slower than sqlite3_open. > there is attachment after the mail which includ the speed > information ( millisecond ). Hi, This mailing list strips attachments, so you'll need to include the information inline. One possible explanation: When you run an ATTACH statement, SQLite opens the new database file and reads the schema from the sqlite_master table. Whereas sqlite3_open() just opens the db file (reading the schema is deferred until it is first required in this case). So an apples/apples comparison might be to open/ATTACH the database and then run a simple query that forces SQLite to read the database schema if it has not already - say "SELECT * FROM sqlite_master". Dan.
[sqlite] Index on computed value?
On 12/16/2015 03:17 PM, Deon Brewis wrote: > Is it possible to have an index on a computer value? > > > E.g. I have a 40 byte value in one of my columns. I only want an index over > the first 4 bytes of it. > > > However, I don't really want to repeat those 4 bytes inside another column on > the main table. > > > Is there any way to accomplish that? Something like CREATE TABLE t1(x BLOB); CREATE INDEX i1 ON t1( substr(x, 1, 4) ); https://www.sqlite.org/expridx.html Dan.
[sqlite] about attach database
Thanks for everyone. You are right. According to you help, I understand the problem. Just open or attach database , open operation is faster than attach database. if add a query statement after open or attach database. The time which they cost almost the same. Best regards. what Dominique said is right. As following: Anyway, I remember observing that: - sqlite3_open_v2(...) is lazy. In other words, it does not parse the schema of the DB until the first query is performed after opening the database. - ATTACH is not lazy. The schema is parsed as soon as you attach a database. That could explain the difference in speed. Would there be a way to make ATTACH lazy by the way? Regards Dominique At 2015-12-16 18:27:34, "Dan Kennedy" wrote: >On 12/16/2015 12:51 PM, ??? wrote: >> After testing the Sqlite3_open and ATTACH DATABASE, >> I found that the attach database is slower than sqlite3_open. >> there is attachment after the mail which includ the speed >> information ( millisecond ). > >Hi, > >This mailing list strips attachments, so you'll need to include the >information inline. > >One possible explanation: When you run an ATTACH statement, SQLite opens >the new database file and reads the schema from the sqlite_master table. >Whereas sqlite3_open() just opens the db file (reading the schema is >deferred until it is first required in this case). > >So an apples/apples comparison might be to open/ATTACH the database and >then run a simple query that forces SQLite to read the database schema >if it has not already - say "SELECT * FROM sqlite_master". > >Dan. > > >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem with accumulating decimal values
16 dec 2015, James K. Lowden: > On Fri, 11 Dec 2015 16:21:30 +0200 > "Frank Millman" wrote: > >> sqlite> UPDATE fmtemp SET balance = balance + 123.45; >> sqlite> SELECT bal FROM fmtemp; >> 5925.599 > > To a question like that you'll receive a lot of answers about > numerical > accuracy. And it's true that there are ways to "do the math" without > using floating point representation. It's also true that it's rarely > necessary, which is why floating point representation exists and *is* > widely used. You may find it works for you too, unless you have to > adhere to a specific rounding policy. > > Per your example, you're working with 2 decimal places of precision. > 5925.599 rounds off to 5925.60; it even rounds off to > 5925.60, not too shabby. If you keep adding 123.45 to it, > you'll find you can go on forever before the answer is wrong in the > second decimal place. > > IEEE 754 is a solid bit of engineering. It's capable of representing > 15 decimal digit of precision. That's good enough to measure the > distance to the moon ... in millimeters. > > You could have an exceptional situation, but that would be > exceptional. Usually, double-precision math works just fine, provided > you have some form of round(3) at your disposal when it comes time to > render the value in decimal form. > > --jkl Hello, so in short, rounding the column anywhere it is used, is another solution. I confirmed this below. Thanks, E. Pasma. BEGIN; UPDATE fmtemp SET bal = ROUND(bal,2) + 123.45; (repeat a 1.000.001 times END; SELECT bal FROM fmtemp; 123450123.45
[sqlite] about attach database
On 16 Dec 2015, at 8:37am, Dominique Pell? wrote: > Having said all that, reading https://www.sqlite.org/c3ref/open.html > I see no mention of the fact that sqlite3_open*() is lazy. > Is it documented somewhere? Not in the official SQLite documentation. But it is easy to prove. Just open a database that doesn't exist. Nothing is done about it until your first write command. Only then are the files created. While discussing lazy it's worth noting that transactions are (by default) lazy too. The default BEGIN is BEGIN DEFERRED. It does nothing to the files and places no locks. Only when a read or write is done inside that transaction does the database get locked. Simon.
[sqlite] Problem with accumulating decimal values
> Hello, so in short, rounding the column anywhere it is used, is > another solution. I confirmed this below. Thanks, E. Pasma. > > BEGIN; > UPDATE fmtemp SET bal = ROUND(bal,2) + 123.45; > (repeat a 1.000.001 times > END; > SELECT bal FROM fmtemp; > 123450123.45 Absolutely not! You should NEVER round the value and store it back in the datastore. Rounding is ephemeral for the convenience of ugly-bags-of-mostly-water who are fixed in their world-view so that data can be DISPLAYED to them in a format that fits their limited view. You should NEVER round as you have done above. You may get lucky and the errors may cancel each other out, or you may get more usual results where the error equals the theoretical max of the sum of the absolute value of all the truncated values, which can be quite significant depending on the scale of the number you are dealing with (and theior scales relative to each other).
[sqlite] Problem with accumulating decimal values
Hello ! I said once and I'll say again for some applications it would make sense to use _Decimal64 (_Decimal32, _Decimal128) instead of floating points. Even if it's done in software the performance is acceptable on most common cases. See a sqlite3.c/sqlite3.h modified to use "_Decimal64" instead of "double" at https://github.com/mingodad/squilu/tree/master/SquiLu-ext using it we can easily swap between "double"/"_Decimal64" by defining a macro "-DSQLITE_USE_DECIMAL=1". I wish it would be part of the official sqlite3 ! Cheers ! ?
[sqlite] Problem with accumulating decimal values
16 dec 2015, Keith Medcalf: >> BEGIN; >> UPDATE fmtemp SET bal = ROUND(bal,2) + 123.45; >> (repeat a 1.000.001 times >> END; >> SELECT bal FROM fmtemp; >> 123450123.45 > > You should NEVER round as you have done above. You may get lucky > and the errors may cancel each other out, or you may get more usual > results where the error equals the theoretical max of the sum of the > absolute value of all the truncated values, which can be quite > significant depending on the scale of the number you are dealing > with (and theior scales relative to each other). Hello, I was only trying to digest JKL's post and the result looks good. The example prints the value as it is in the database and shows that there is no accumulated error there. I do not see a counter example (not yet). Ok this does not work of any scale of numbers. But a solution with integers neither does E.Pasma
[sqlite] Problem with accumulating decimal values
On Wed, Dec 16, 2015 at 9:43 AM, Keith Medcalf wrote: > >> Hello, so in short, rounding the column anywhere it is used, is >> another solution. I confirmed this below. Thanks, E. Pasma. >> >> BEGIN; >> UPDATE fmtemp SET bal = ROUND(bal,2) + 123.45; >> (repeat a 1.000.001 times >> END; >> SELECT bal FROM fmtemp; >> 123450123.45 > > Absolutely not! You should NEVER round the value and store it back in the > datastore. Rounding is ephemeral for the convenience of > ugly-bags-of-mostly-water who are fixed in their world-view so that data can > be DISPLAYED to them in a format that fits their limited view. > Although I agree about not rounding and updating the store with "corrected" values. I don't think there is a need to call the ugly-bags-of-mostly-water ugly-bags-of-mostly-water. Also, I wouldn't want myself to see 22.99 instead of 23.00 in the frontends I use either. In a practical sense, I believe the latter reduces the amount of processing my brain has to do and I can better focus on what matters. But then again, just use string formatting on the view of the project. On Wed, Dec 16, 2015 at 12:05 PM, E.Pasma wrote: > Ok this does not work of any scale of numbers. But a solution with integers > neither does > > E.Pasma > Preferences aside, no solution ever devised will work with **any** scale with numbers as we have finite data storage. That is very pedantic, but just to be clear. I like integer better than floating points and text for currencies, some will have other preferences, it does not really matter as long as we are not working together. -- Bernardo Sulzbach
[sqlite] Problem with accumulating decimal values
Good day, As a matter of interest, when calculating interest on a sum of money expressed in pennies, how do you handle int arithmetic truncating? Is that an accounting design rule thing when dealing with fractions of a penny to round? Is this an arbitrary quantization? Once upon a time there existed the Ha'penny https://en.wikipedia.org/wiki/Halfpenny_%28British_pre-decimal_coin%29 https://en.wikipedia.org/wiki/Half_cent_%28United_States_coin%29 I think the ugly-bags-of-mostly-water indirection was humorous. I found it funny. https://en.wikipedia.org/wiki/Home_Soil live long and prosper. Adam On Wed, Dec 16, 2015 at 10:17 AM, Bernardo Sulzbach wrote: > On Wed, Dec 16, 2015 at 9:43 AM, Keith Medcalf wrote: >> >>> Hello, so in short, rounding the column anywhere it is used, is >>> another solution. I confirmed this below. Thanks, E. Pasma. >>> >>> BEGIN; >>> UPDATE fmtemp SET bal = ROUND(bal,2) + 123.45; >>> (repeat a 1.000.001 times >>> END; >>> SELECT bal FROM fmtemp; >>> 123450123.45 >> >> Absolutely not! You should NEVER round the value and store it back in the >> datastore. Rounding is ephemeral for the convenience of >> ugly-bags-of-mostly-water who are fixed in their world-view so that data can >> be DISPLAYED to them in a format that fits their limited view. >> > > Although I agree about not rounding and updating the store with > "corrected" values. I don't think there is a need to call the > ugly-bags-of-mostly-water ugly-bags-of-mostly-water. Also, I wouldn't > want myself to see 22.99 instead of 23.00 in the frontends I > use either. In a practical sense, I believe the latter reduces the > amount of processing my brain has to do and I can better focus on what > matters. But then again, just use string formatting on the view of the > project. > > On Wed, Dec 16, 2015 at 12:05 PM, E.Pasma wrote: > >> Ok this does not work of any scale of numbers. But a solution with integers >> neither does >> >> E.Pasma >> > > Preferences aside, no solution ever devised will work with **any** > scale with numbers as we have finite data storage. That is very > pedantic, but just to be clear. I like integer better than floating > points and text for currencies, some will have other preferences, it > does not really matter as long as we are not working together. > > -- > Bernardo Sulzbach > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- -- VerifEye Technologies Inc. 151 Whitehall Dr. Unit 2 Markham, ON L3R 9T1
[sqlite] Problem with accumulating decimal values
On 16 Dec 2015, at 3:46pm, Adam Devita wrote: > As a matter of interest, when calculating interest on a sum of money > expressed in pennies, how do you handle int arithmetic truncating? > Is that an accounting design rule thing when dealing with fractions of > a penny to round? When writing accounting software, there will be a specific rule for rounding attached to each calculation. For instance a process for working out a mortgage will include its own instruction "once you have multiplied by the number of days, round down to the next dollar". But the rules for working out interest rates might state "round to the nearest cent, round half to even". Unfortunately there is no world-wide standard for these. There can be one rule for one country (State, industry, etc.) and another for another. Except in Europe where they are all meant to agree with one another. Simon.
[sqlite] Problem with accumulating decimal values
On Wed, Dec 16, 2015 at 1:54 PM, Simon Slavin wrote:
>
> On 16 Dec 2015, at 3:46pm, Adam Devita wrote:
>
> When writing accounting software, there will be a specific rule for rounding
> attached to each calculation. For instance a process for working out a
> mortgage will include its own instruction "once you have multiplied by the
> number of days, round down to the next dollar". But the rules for working
> out interest rates might state "round to the nearest cent, round half to
> even".
>
Exactly as Simon said, the rules **will** (or at least should) be part
of the requirement. Also, some cases tell you to preserve fractions
until a final rounding. So you will have to go with decimals or
"scale" your integers (multiply them by a power of ten) somewhere.
At least here in Brazil there seems to be a lot of: if it is money
going away {round down as many times as possible} if it is money
coming our way {round up as many times as possible}. I think many
other places will use this too.
--
Bernardo Sulzbach
[sqlite] Index on computed value?
On Wed, Dec 16, 2015 at 9:17 AM, Deon Brewis wrote: > Is it possible to have an index on a computer value? > > E.g. I have a 40 byte value in one of my columns. I only want an index over > the first 4 bytes of it. > > However, I don't really want to repeat those 4 bytes inside another column on > the main table. See http://sqlite.org/expridx.html Ambrus
[sqlite] Index on computed value?
On 16 Dec 2015, at 4:23pm, Zsb?n Ambrus wrote: > See http://sqlite.org/expridx.html "The ability to index expressions was added to SQLite with version 3.9.0 in October of 2015" Nice to see that the development team's crystal ball is running around three months ahead of questions on this list. Simon.
[sqlite] Problem with accumulating decimal values
16 dec 2015, 16:17, Bernardo Sulzbach: > > On Wed, Dec 16, 2015 at 12:05 PM, E.Pasma wrote: > >> Ok this does not work of any scale of numbers. But a solution with >> integers >> neither does >> >> E.Pasma >> > ...I like integer better than floating points and text for > currencies ... Good taste. I now see a counter example where a solution with rounded floating point columns goes wrong. This is with aggregate functions. Using SUM adds up the errors before rounding and that may be too late. Tnanks.
[sqlite] Problem with accumulating decimal values
On 2015/12/16 4:05 PM, E.Pasma wrote: > 16 dec 2015, Keith Medcalf: >>> BEGIN; >>> UPDATE fmtemp SET bal = ROUND(bal,2) + 123.45; >>> (repeat a 1.000.001 times >>> END; >>> SELECT bal FROM fmtemp; >>> 123450123.45 >> >> You should NEVER round as you have done above. You may get lucky and >> the errors may cancel each other out, or you may get more usual >> results where the error equals the theoretical max of the sum of the >> absolute value of all the truncated values, which can be quite >> significant depending on the scale of the number you are dealing with >> (and theior scales relative to each other). > > > Hello, I was only trying to digest JKL's post and the result looks > good. The example prints the value as it is in the database and shows > that there is no accumulated error there. I do not see a counter > example (not yet). > > Ok this does not work of any scale of numbers. But a solution with > integers neither does I think the bit that Keith tried to highlight is that we should always refrain from storing errors. Calculated errors are fine because we can at any time revisit the calculation procedures, we can refine and perhaps opt for more significant digits - but we can ALWAYS guarantee the accuracy-level of the calculated result. However, storing wrong values (or let's call them "approximate" values if you like) is pure evil. If the data that feeds my calculator is flawed by whichever tiny amount, or stored with errors, or retrieved with errors, then there is pretty much NOTHING I can do to revisit the original / "really really real" values of what actually happened or in any way confirm any degree of certainty on my calculation, because my axioms are wrong. And let's be clear*, No computer value is absolute in accuracy - but in the same way that 1.6667 is closer to the real value than 1.67, in computer and IEEE:754 terms, 1.29978 might well be much closer to 1.3 than the 1.30010378 which may be the next representable IEE:754 bit formation that gets stored when you put "1.3" into a float database field. * I'm just using artistic license here, did not calculate the real values, but the principle remains - I think Keith posted a way of finding the representable minimum differences between specific IEE:754 representations recently, if anyone is interested in the actual values. Cheers, Bag-of-water-Ryan. :)
[sqlite] about attach database
On Wed, Dec 16, 2015 at 1:37 AM, Dominique Pell? wrote: > Scott Robison wrote: > > > Why would that be of benefit to you? Are you intending to attach a > database > > and never use it? It seems to me the same amount of time will be taken > > either way. > > > > When it comes to opening a database, there may be a need to do some > > connection specific configuration prior to actually opening the database > > file and parsing the schema. I believe this is the reason why open defers > > that processing until later, giving you a chance to finish configuring > your > > connection before locking it down. Once that configuration is complete, > > there is no advantage to deferring the open of the database. > > > > I say no advantage ... maybe I just can't think of one. Why do you think > > there would be an advantage to deferring the open & schema processing of > an > > attached database? > > Laziness can be useful in some cases. I have an application > that opens hundred or so of database connections. Being able to open > all connections at start-up is simple. Since it's lazy, it's also fast and > does > not use memory to store schemas until the databases are actually > used later. In my application, queries happen in only few connections > after start-up out of all opened connections. For many connections, > queries happen much later or sometimes do not even happen. Laziness > is thus useful to make start-up fast and simple, without application having > to implement laziness itself. > > I see that the original message from ??? says "Because there are so > many database [...]", so it seems to be the same scenario as in my > application in which laziness is quite useful. I'm not 100% sure but I'm > quite confident that laziness is the explanation for performance > discrepancy between sqlite3_open*() and ATTACH. > > If laziness was useless, why would it then be already implemented > for sqlite3_open_v2(...)? > As I indicated above, in the case of SQLite, it isn't about lazy. It is about deferring opening the database to give the programmer a chance to do any further configuration of the connection that must be done prior to creating or opening the actual database file and reading / parsing the schema (pragma auto_vaccum, pragma encoding, perhaps sqlite3_db_config, maybe others). Those are operations that may require a connection that has not yet processed a schema. In any other case of 'lazy' loading (which I agree can be a valuable technique and I have used it myself), it can be implemented in your own code. By tracking what databases you've attached and only attaching them on first use, rather than attaching them all in the beginning. -- Scott Robison
[sqlite] Bug with DATETIME('localtime')
On Sun, 13 Dec 2015 20:11:32 -0700 Scott Robison wrote: > > It's not fixed, although gacial progress is being made. Even though > > we've had the TZ database & Posix datetime functions since 1986, 30 > > years later we're still struggling with it, and not only on Windows. > > The problem would be that SQLite could not depend on the presence of > TZ functions even if they were added to the standard: I think the time when "the standard" mattered regarding *libraries* has passed. Nowadays, new functions do or don't get added to libc largely based on what GNU does, and to a lesser extent on the BSD projects. > 1. SQLite generally avoids non ANSI C so as to be compatible with the > largest possible number of platforms. ANSI C (aka C89 or C90 for the > ISO version) will never be updated to add new requirements. SQLite maintains its own implementation of not a few functions for the sake of compatibility. I don't know whether this should be one of them, but there is more than enough precedent. > 2. Let's say that that the next version of the C standard does add TZ > functionality. I haven't peeked to find out how SQLite implements date arithmetic. I assume it parses strings in the database, calls mktime(3), and subtracts time_t values. That's pretty vanilla, and doesn't *require* the TZ database. The downside of using mktime is that it locks you into a "time zone perspective", if you will. The timezone that will be used to convert a (UTC-based) time_t value to "local time" is set globally. If you want to compare two local times, you have to manipulate that global variable between conversions. The new mktime_z(3) function from NetBSD unglobalizes the timezone: it adds a timezone parameter. That makes it much more convenient to use (if that's what you need!) It's been accepted afaict by IANA, but I found no discussion of it at GNU. While the NetBSD (and IANA, obviously) implementation uses the TZ database, that's not a requirement. The function's definition makes no reference to its implementation. mktime_z could be emulated on Windows without IANA's help. Which it would have to be, because Windows doesn't use the TZ database: save TZ set TZ to something _tzset() // Microsoft! mktime restore TZ _tzset A quick glance at the documentation suggests TzSpecificLocalTimeToSystemTimeEx might be useful, too. Someone will complain that would be slow, and something about threads. My understanding is that the OP got the wrong answer, and I would say slow is better than broken. And it won't be slow: there's no I/O; not even a context switch. As Keith said, as of now you have to roll your own. SQLite does not support date arithmetic across time zones. Should it? Should it as an extension? I don't know. I was just trying to understand (and explain) what the C foundation looks like, why/how it's broken, and what would be required to fix it. --jkl
[sqlite] Problem with accumulating decimal values
On Wed, 16 Dec 2015 20:33:40 +0200 R Smith wrote: > > Ok this does not work of any scale of numbers. But a solution with > > integers neither does > > I think the bit that Keith tried to highlight is that we should > always refrain from storing errors. Keith recommended against storing *rounded* values. If you store $0.30 in SQLite as REAL, you store a binary approximation. It's a fine thing to keep unless you care about picodollars. > Calculated errors are fine because we can at any time revisit the > calculation procedures, we can refine and perhaps opt for more > significant digits - but we can ALWAYS guarantee the accuracy-level > of the calculated result. However, storing wrong values (or let's > call them "approximate" values if you like) is pure evil. I'm not sure what you mean. There's no problem storing a C double from memory and later fetching it. The same 64 bits pass through the interface unchanged. (Well, maybe not the *same* bits, but who can tell?!) Once replaced back in C memory, the computation can resume where it left off unaffected. What you usually don't want to do is compute based on rounded numbers. If you store a rounded number to the database, you may lose information. Even if you don't -- even when the rounded number is the right one -- such errors as accumulate at the edge of accuracy normally wind up not mattering. That's why C does all computation in double precision, even when the operands are single-precision. The opposite mistake -- losing information -- can easily lead to results that are spectacularly wrong. --jkl
[sqlite] Problem with accumulating decimal values
On Wed, 16 Dec 2015 15:05:34 +0100 "E.Pasma" wrote: > 16 dec 2015, Keith Medcalf: > >> BEGIN; > >> UPDATE fmtemp SET bal = ROUND(bal,2) + 123.45; > >> (repeat a 1.000.001 times > >> END; > >> SELECT bal FROM fmtemp; > >> 123450123.45 > > > > You should NEVER round as you have done above. You may get lucky > > and the errors may cancel each other out, or you may get more > > usual results where the error equals the theoretical max of the sum > > of the absolute value of all the truncated values, which can be > > quite significant depending on the scale of the number you are > > dealing with (and theior scales relative to each other). > > Hello, I was only trying to digest JKL's post and the result looks > good. The example prints the value as it is in the database and > shows that there is no accumulated error there. I do not see a > counter example (not yet). > > Ok this does not work of any scale of numbers. But a solution with > integers neither does Keith's advice is well taken. Keep the real number; round for presentation. I always say, "store what you know". Yes, starting from zero you can add 123.45 to a double-precision floating point number for a very, very long time, about 81,004,455,245 times, before the error will appear in pennies. When it does, you'll have 13 digits left of the decimal. That's on the order of the US GDP. We don't measure things like that down to the penny, so no one will know if you're wrong. ;-) The thing to keep in mind is that you get ~15 decimal places of precision. The decimal floats. You can put it way on the left, and measure tiny things accurately. You can put it on the right, and measure astronomical things accurately. Unless you care about millimeters to the moon, it will do the job. Whole books have been written on numerical accuracy. I suspect if that mattered to your application you'd know about it. My advice is to let the engineers worry about it -- they did, years ago -- and accept rounded output unless and until you have an example of a computation for which that doesn't work. --jkl
[sqlite] Porting SQLITE-3.10 into VxWorks-6.9
Hi All,
[sqlite] Porting SQLITE-3.10 into VxWorks-6.9
On 12/16/15, Janto Ranjan Paul wrote: > > Hi All, > > From last couple of days, I am trying to port Sqlite-3.10 database into > Vxworks... The latest release version of SQLite is 3.9.2. Are you using unreleased code from trunk? -- D. Richard Hipp drh at sqlite.org
[sqlite] batch or one by one?
On Wed, Dec 16, 2015 at 9:24 PM, ??? <2004wqg2008 at 163.com> wrote: > hi,all > > There is an interesting phenomenon.As you know, SQLite can retrieve > records by batch or one by one. > 1.Retrieve by batch such as sqlite3_get_table. > 2.Retrieve one by one such as sqlite3_prepare_v2 and sqlite3_step > together. > Using the above two methods to retrieve hundreds of records by > random, which method is fast? > 1. Given that the get table method converts everything to a string, is a legacy interface, and is not recommended for new use, probably prepare and step assuming you don't use functions that will force datatype conversions. 2. It's incredibly simple to compile a little test apps with each option and time them to know for certain in your environment, since if there are differences it might depend on your schema, your hardware, your operating system ... who knows what. -- Scott Robison
