Re: [sqlite] Seasonal syntax
On 14 December 2017 at 01:19, Warren Young wrote: > On Dec 12, 2017, at 10:24 AM, Simon Slavin wrote: > > > > Santa Clause: SELECT name,hobbies,address FROM people WHERE > behaviour=‘nice’ > > I think you mean > > SELECT name,address > CASE behaviour > WHEN ‘nice' THEN > hobbies > ELSE > 'coal' > END > FROM people > Surely "hobbies" should read something like: (SELECT gift FROM ideas WHERE ideas.hobby IN (SELECT value FROM json_each(hobbies)) ORDER BY random() LIMIT 1) -Rowan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can a trigger recursively update a table?
brilliant! - it works - thanks On 14 December 2017 at 19:07, Clemens Ladisch wrote: > Shane Dev wrote: > > On 14 December 2017 at 12:59, Clemens Ladisch > wrote: > >> Shane Dev wrote: > >>> Can we conclude there is no single CTE or other SQL statement which can > >>> update a branch of the tree starting with a flexibly specified node? > >> > >> That should be possible when you enable recursive triggers: > >> > >> begin > >> update hierarchy set status = null where id = old.id; > >> delete from vhierarchy where parent = old.id; > >> end; > > > > With your solution, how would you define the DELETE ON VHIERARCHY > trigger? > > That is the trigger. > > > Regards, > Clemens > ___ > 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] Is this a bug with expression evaluation?
Tony Papadimitriou wrote: > I really don't know what the standard says, but here are two different > opinions in implementation. > > MySQL example: You know that the "SQL" in "MySQL" is actually the abbreviation of "something quite loose"? ;-) Anyway, it appears even MySQL conforms to SQL-92 subclause 6.12: | 1) If the data type of both operands of a dyadic arithmetic opera- |tor is exact numeric, then the data type of the result is exact |numeric, with precision and scale determined as follows: |[...] |d) The precision and scale of the result of division is | implementation-defined. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug with expression evaluation?
On 12/14/17, 12:08 PM, "sqlite-users on behalf of Simon Slavin" wrote: > Just to remind you that if something is not documented it can change. The > next version of SQLite might decide that 1 / 2 is 0. So don’t write code > that depends on it. I think it already does: sqlite> select 1/2; 0 sqlite> ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug with expression evaluation?
On 12/14/17, Tony Papadimitriou wrote: > > MySQL example: > mysql> select 1/2; > ++ > | 1/2| > ++ > | 0.5000 | > ++ > 1 row in set (0.13 sec) MySQL is the only database engine that behaves this way. All others do integer arithmetic on integer values. This is probably the reason that MySQL has the separate "DIV" operator for integer division, whereas everybody else makes due with the standard "/" operator. -- 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] Is this a bug with expression evaluation?
On 14 Dec 2017, at 5:03pm, Tony Papadimitriou wrote: > SQLite3 (https://sqlite.org/datatype3.html) -- "Otherwise, an expression has > no affinity. " > It seems that 'no affinity' gets translated to integer affinity, then. Just to remind you that if something is not documented it can change. The next version of SQLite might decide that 1 / 2 is 0. So don’t write code that depends on it. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can a trigger recursively update a table?
Shane Dev wrote: > On 14 December 2017 at 12:59, Clemens Ladisch wrote: >> Shane Dev wrote: >>> Can we conclude there is no single CTE or other SQL statement which can >>> update a branch of the tree starting with a flexibly specified node? >> >> That should be possible when you enable recursive triggers: >> >> begin >> update hierarchy set status = null where id = old.id; >> delete from vhierarchy where parent = old.id; >> end; > > With your solution, how would you define the DELETE ON VHIERARCHY trigger? That is the trigger. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Is this a bug with expression evaluation?
What you see is not a bug, it’s an annoying heritage of C syntax. Might even precede C. Here’s the problem: select column1*(24/100); And here’s what you’re meant to do for 24%: select column1*(24.0/100.0); Alternatively, the value in column1 should be real. That should also work. If your numbers are real, express them as real, using either a decimal point or exponent/mantissa format. "24" means integer 24. "24.0" means real 24. I’ve had this behaviour bite me when I was doing some simple maths in C and divided by "4" instead of "4.0". It took me numerous debugging statements and three or four hours to figure out what was wrong, and when I figured it out I was so annoyed I left work early. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug with expression evaluation?
-Original Message- From: J. King Someone please correct me if I'm wrong, but I believe it's mandated by the SQL standard that integer division is used when both operands are integers. I really don't know what the standard says, but here are two different opinions in implementation. MySQL example: mysql> select 1/2; ++ | 1/2| ++ | 0.5000 | ++ 1 row in set (0.13 sec) PostgreSQL example: psql=# select 1/2; ?column? -- 0 (1 row) Your synthetic example doesn't use a fixed table, but if it did the easiest solution for you would probably be to define any columns where you need arbitrary precision as REAL rather than INTEGER, and SQLite column >affinity would do the rest. SQLite3 (https://sqlite.org/datatype3.html) -- "Otherwise, an expression has no affinity. " It seems that 'no affinity' gets translated to integer affinity, then. Is there a way to default to float? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Is this a bug with expression evaluation?
This is well documented behaviour, see the explanation of affinity. See http://sqlite.org/datatype3.html#affinity If you require floating point arithmetic, you must introduce REAL affinity, either by including a field with storage class REAL, a cast operation or a real literal value -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Tony Papadimitriou Gesendet: Donnerstag, 14. Dezember 2017 17:36 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] Is this a bug with expression evaluation? I’ve noticed this (very annoying) behavior: select column1*(24/100) wrong from (values(100)); Removing the parentheses yields the correct result: select column1*24/100 correct from (values(100)); This obviously behaves like integer math is used and (24/100) gets truncated to zero. If I add a dot to either number (e.g., 24. or 100.) I get the correct result. But, with named fields, it’s not as easy as adding a dot: select column1*(column2/column3) wrong from (values(100,24,100)); select column1*column2/column3 correct from (values(100,24,100)); So, to get correct answer I have to use a cast for either field? select column1*(cast(column2 as float)/column3) correct from (values(100,24,100)); In this example removing the parentheses is a simple solution. But if the expression was column1*(1+column2/column3) a cast is the only way? (Hope not!) Anyway, if all this happens to be so by design, is there at least some way to default to float math rather than integer? Thanks. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug with expression evaluation?
Someone please correct me if I'm wrong, but I believe it's mandated by the SQL standard that integer division is used when both operands are integers. Your synthetic example doesn't use a fixed table, but if it did the easiest solution for you would probably be to define any columns where you need arbitrary precision as REAL rather than INTEGER, and SQLite column affinity would do the rest. Otherwise, yes, I believe you would need to cast. On December 14, 2017 11:36:19 AM EST, Tony Papadimitriou wrote: >I’ve noticed this (very annoying) behavior: > >select column1*(24/100) wrong from (values(100)); > >Removing the parentheses yields the correct result: > >select column1*24/100 correct from (values(100)); > >This obviously behaves like integer math is used and (24/100) gets >truncated to zero. > >If I add a dot to either number (e.g., 24. or 100.) I get the correct >result. >But, with named fields, it’s not as easy as adding a dot: > >select column1*(column2/column3) wrong from (values(100,24,100)); >select column1*column2/column3 correct from (values(100,24,100)); > >So, to get correct answer I have to use a cast for either field? > >select column1*(cast(column2 as float)/column3) correct from >(values(100,24,100)); > >In this example removing the parentheses is a simple solution. >But if the expression was column1*(1+column2/column3) a cast is the >only way? (Hope not!) > >Anyway, if all this happens to be so by design, is there at least some >way to default to float math rather than integer? > >Thanks. >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Sent from my Android device with K-9 Mail. Please excuse my brevity. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug with expression evaluation?
I just multiply by 1.0 Select column1*(column2 * 1.0 / column3)... Removing the parentheses only provide the correct results in your example. It's still using integer math, it's just performing the multiply first, as per order of operations. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Tony Papadimitriou Sent: Thursday, December 14, 2017 11:36 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Is this a bug with expression evaluation? I’ve noticed this (very annoying) behavior: select column1*(24/100) wrong from (values(100)); Removing the parentheses yields the correct result: select column1*24/100 correct from (values(100)); This obviously behaves like integer math is used and (24/100) gets truncated to zero. If I add a dot to either number (e.g., 24. or 100.) I get the correct result. But, with named fields, it’s not as easy as adding a dot: select column1*(column2/column3) wrong from (values(100,24,100)); select column1*column2/column3 correct from (values(100,24,100)); So, to get correct answer I have to use a cast for either field? select column1*(cast(column2 as float)/column3) correct from (values(100,24,100)); In this example removing the parentheses is a simple solution. But if the expression was column1*(1+column2/column3) a cast is the only way? (Hope not!) Anyway, if all this happens to be so by design, is there at least some way to default to float math rather than integer? Thanks. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Confidentiality notice: This e-mail is intended solely for use of the individual or entity to which it is addressed and may contain information that is proprietary, privileged, company confidential and/or exempt from disclosure under applicable law. If the reader is not the intended recipient or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender by reply e-mail or collect telephone call and delete or destroy all copies of this e-mail message, any physical copies made of this e-mail message and/or any file attachment(s). ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is this a bug with expression evaluation?
I’ve noticed this (very annoying) behavior: select column1*(24/100) wrong from (values(100)); Removing the parentheses yields the correct result: select column1*24/100 correct from (values(100)); This obviously behaves like integer math is used and (24/100) gets truncated to zero. If I add a dot to either number (e.g., 24. or 100.) I get the correct result. But, with named fields, it’s not as easy as adding a dot: select column1*(column2/column3) wrong from (values(100,24,100)); select column1*column2/column3 correct from (values(100,24,100)); So, to get correct answer I have to use a cast for either field? select column1*(cast(column2 as float)/column3) correct from (values(100,24,100)); In this example removing the parentheses is a simple solution. But if the expression was column1*(1+column2/column3) a cast is the only way? (Hope not!) Anyway, if all this happens to be so by design, is there at least some way to default to float math rather than integer? Thanks. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can a trigger recursively update a table?
Hi Clemens, With your solution, how would you define the DELETE ON VHIERARCHY trigger? On 14 December 2017 at 12:59, Clemens Ladisch wrote: > Shane Dev wrote: > > Can we conclude there is no single CTE or other SQL statement which can > > update a branch of the tree starting with a flexibly specified node? > > That should be possible when you enable recursive triggers: > > begin > update hierarchy set status = null where id = old.id; > delete from vhierarchy where parent = old.id; > end; > > > Regards, > Clemens > ___ > 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] DateTime kind stored as undefined
I'm sorry -- the following post was sent to a private e-mail by an accident: Hello, On 2017-12-13 12:51, Michał Niegrzybowski wrote: > I have a table which has a column of type DateTime in my code I insert > there an actual UTC Date (which is not the same as my local time). When I > want to gather previously added record, my record contains date in his > DateTime column, but this DateTime is a localtime with kind specified to > 'undefined' instead of 'UTC'. I cannot reproduce the problem. Setting a format to ticks and a kind to UTC causes a storing/retrieving a valid UTC DateTime, which is stored as INTEGER. Could you provide your connection string? -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: How to index data based on custom comparisons?
Select from blob_index idx cross join data_table dt on (idx.rowid = dt.rowid) where ; Assuming that the rowid of the blob_index is generated from and identical to the rowid of the data table -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Lifepillar Gesendet: Donnerstag, 14. Dezember 2017 13:52 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] Re: [sqlite] How to index data based on custom comparisons? On 14/12/2017 13:14, Richard Hipp wrote: > On 12/14/17, Lifepillar wrote: > >> I am not familiar with virtual tables yet, but I see that they are >> used, for example, to implement Rtree indexes. Would it be feasible >> to implement my own index structure as a virtual table and use it to >> index a blob column in a standard table (or even just in the virtual >> table itself)? > > That would be complicated. So, it is possible :) > A different idea. Suppose you have two new UDFs: > > ieee754dec(X): Converts IEEE754-binary number X into IEEE754-decimal. > In other words it takes a "double" input and returns a "blob" output. > > ieee754bin(Y): Converts IEEE754-decimal blob Y and converts it into > IEEE754-binary. > > Both routines are approximate because most IEEE754-binary values do > not have an exact equivalent IEEE754-decimal representation and vice > versa. Your UDFs would need to find something very close. > > Given these routines, you could then index your IEEE754-decimal > columns by doing an index on an expression using the new iee754bin() > function. Thanks, that's another possibility to consider, although one typically uses decimal values when exactness is needed; using your scheme requires some care, I think, for example not to miss matches because of approximations or to filter spurious matches away. Somehow, I wish SQLite had something like PostgreSQL GiST indexes... ;) Life. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to index data based on custom comparisons?
On 14/12/2017 13:14, Richard Hipp wrote: On 12/14/17, Lifepillar wrote: I am not familiar with virtual tables yet, but I see that they are used, for example, to implement Rtree indexes. Would it be feasible to implement my own index structure as a virtual table and use it to index a blob column in a standard table (or even just in the virtual table itself)? That would be complicated. So, it is possible :) A different idea. Suppose you have two new UDFs: ieee754dec(X): Converts IEEE754-binary number X into IEEE754-decimal. In other words it takes a "double" input and returns a "blob" output. ieee754bin(Y): Converts IEEE754-decimal blob Y and converts it into IEEE754-binary. Both routines are approximate because most IEEE754-binary values do not have an exact equivalent IEEE754-decimal representation and vice versa. Your UDFs would need to find something very close. Given these routines, you could then index your IEEE754-decimal columns by doing an index on an expression using the new iee754bin() function. Thanks, that's another possibility to consider, although one typically uses decimal values when exactness is needed; using your scheme requires some care, I think, for example not to miss matches because of approximations or to filter spurious matches away. Somehow, I wish SQLite had something like PostgreSQL GiST indexes... ;) Life. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does sqlite have official development testing tool?
On Thu, Dec 14, 2017 at 4:19 AM, advancenOO wrote: > Hello Richard, > > I hope to run some tests by myself and I think TCL tests in your link are > what I want. > There are so many .tcl and .test in Sqlite source tree. > Could someone share what commands I need to run to start all TCL tests? > > the 'make test' target uses a program 'testfixture' which is passed a tcl script. You can re-run indicidual tests with './testfixture tests/' although that's the majority of the tests, some of the tests are 'fuzzcheck' which is another program built by the makefile. make test does a good majority of the tests... there's also make fulltest which takes many hours to complete but does 10x more tests. > Thanks. > > > > -- > Sent from: http://sqlite.1065341.n5.nabble.com/ > ___ > 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] How to index data based on custom comparisons?
On 13/12/2017 22:20, Simon Slavin wrote: On 13 Dec 2017, at 8:34pm, Lifepillar wrote: But, (correct me if I am wrong), if I index the blob column directly, comparisons are based on memcpy(), which in my case is not what I want. Is it possible to create an index that somehow uses a custom comparison function instead? E.g., I have a deccmp(x,y) function that returns -1 if xy. Can I define an index based on that? As Dr H wrote, it can’t be done. Either store a normalised (numeric) version of the number, or store both the BLOB and a normalised version. Thanks for the suggestion. Storing a normalized version side by side with the unnormalized number would work, but it would double the used space. Storing just the normalized number would lose information, though. Life. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does sqlite have official development testing tool?
On 12/14/17, advancenOO wrote: > Hello Richard, > > I hope to run some tests by myself and I think TCL tests in your link are > what I want. > There are so many .tcl and .test in Sqlite source tree. > Could someone share what commands I need to run to start all TCL tests? > make test -- 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] Does sqlite have official development testing tool?
Hello Richard, I hope to run some tests by myself and I think TCL tests in your link are what I want. There are so many .tcl and .test in Sqlite source tree. Could someone share what commands I need to run to start all TCL tests? Thanks. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] the timing to truncate WAL file to journal_size_limit
On 12/14/17, advancenOO wrote: > I noticed that, > “The journal_size_limit pragma may be used to limit the size of WAL files > left in the file-system after transactions or checkpoints. Each time a WAL > file resets, SQLite compares the size of the WAL file left in the > file-system to the size limit.” > > But I think only when the first transaction commits AFTER a checkpoint will > WAL file truncate to the limit. As the src code is, > if( isCommit && pWal->truncateOnCommit && pWal->mxWalSize>=0 ). > > Which means WAL file may still consume a large amount of space after > checkpoints, unless a new transaction commits. Is that right? Correct. The reset happens on the next transaction commit. There is also "PRAGMA wal_checkpoint(TRUNCATE);" -- 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] How to index data based on custom comparisons?
On 12/14/17, Lifepillar wrote: > I am not familiar with virtual tables yet, but I see that they are used, > for example, to implement Rtree indexes. Would it be feasible to > implement my own index structure as a virtual table and use it to index > a blob column in a standard table (or even just in the virtual table > itself)? That would be complicated. A different idea. Suppose you have two new UDFs: ieee754dec(X): Converts IEEE754-binary number X into IEEE754-decimal. In other words it takes a "double" input and returns a "blob" output. ieee754bin(Y): Converts IEEE754-decimal blob Y and converts it into IEEE754-binary. Both routines are approximate because most IEEE754-binary values do not have an exact equivalent IEEE754-decimal representation and vice versa. Your UDFs would need to find something very close. Given these routines, you could then index your IEEE754-decimal columns by doing an index on an expression using the new iee754bin() function. -- 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] Can a trigger recursively update a table?
Shane Dev wrote: > Can we conclude there is no single CTE or other SQL statement which can > update a branch of the tree starting with a flexibly specified node? That should be possible when you enable recursive triggers: begin update hierarchy set status = null where id = old.id; delete from vhierarchy where parent = old.id; end; Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to index data based on custom comparisons?
On 14/12/2017 00:02, Keith Medcalf wrote: On Wednesday, 13 December, 2017 13:35, Lifepillar wrote: I am implementing an extension for manipulating IEEE754 decimal numbers. Numbers are stored as blobs using a standard encoding. Numbers that are mathematically equal may have different representations, (e.g., 1.0 may have mantissa 10 and exponent -1 while 1.00 may have mantissa 100 and exponent -2). You have stated something that is impossible, or at least self-contradictory. Unless, of course, you are talking about the "decimal" formats of IEEE754-2008 and not the standard (far more common) "binary" formats. Yes, I am talking about decimal IEEE754-2008 format, not binary. I thought this would be clear from my use of the word "decimal" and from my example. On the other hand however if you do NOT need binary64 at all, then there was a minor change discussed a while back by someone else where you can "change" the default floating-point number format from binary64 to decimal64 and then compile your own custom version of SQLite3 ... Thanks for the tip! I will search for that discussion, although I would rather not modify SQlite3 source code if I can find another solution. I am not familiar with virtual tables yet, but I see that they are used, for example, to implement Rtree indexes. Would it be feasible to implement my own index structure as a virtual table and use it to index a blob column in a standard table (or even just in the virtual table itself)? I mean, would the optimizer be able to take advantage of such an index? (Sorry if my questions sound naive, I'm still pretty new to SQLite.) Life. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] the timing to truncate WAL file to journal_size_limit
I noticed that, “The journal_size_limit pragma may be used to limit the size of WAL files left in the file-system after transactions or checkpoints. Each time a WAL file resets, SQLite compares the size of the WAL file left in the file-system to the size limit.” But I think only when the first transaction commits AFTER a checkpoint will WAL file truncate to the limit. As the src code is, if( isCommit && pWal->truncateOnCommit && pWal->mxWalSize>=0 ). Which means WAL file may still consume a large amount of space after checkpoints, unless a new transaction commits. Is that right? -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
