Re: [sqlite] nested set tree: how to change order of one node?

On 18 Jun 2019, at 14:19, Sam Carleton wrote: > > The tree in question contains categories, subcategories and finally image > galleries. It is common for the user to want to sort all the subordinates > of one level a different way, at times alphanumeric, other times simply to > their liking. I

Re: [sqlite] round function inconsistent

>> exist? Thanks. >>> >>> Consider these two queries: >>> >>> SELECT round(3.255,2); >>> SELECT round(3.2548,2); >>> >>> Do you expect them to give different answers? >> >> 3.26 >> 3.25 > >

Re: [sqlite] ANN: SQLite3 Decimal Extension

Minor correction: > On 5 Apr 2019, at 09:52, Lifepillar wrote: > > select decStr(decAdd(a,60)), case dec(b) when dec(c) then 1 else 0 end from > t1; > select decStr(decAdd(a,70)), case dec(c) when dec(b) then 1 else 0 end from > t1; > […] > select count(*), count(

Re: [sqlite] ANN: SQLite3 Decimal Extension

On 5 Apr 2019, at 00:18, Simon Slavin wrote: > > On 4 Apr 2019, at 10:12pm, Lifepillar wrote: > >> This is essentially a pragmatic choice, as the semantics of NULLs is >> unspecified and ambiguous. > > The way SQL handles NULLs may sometimes appear inconsistent,

Re: [sqlite] ANN: SQLite3 Decimal Extension

On 4 Apr 2019, at 21:36, James K. Lowden wrote: > > On Thu, 4 Apr 2019 17:30:29 +0200 > Lifepillar wrote: > >> On 4 Apr 2019, at 17:15, James K. Lowden >> wrote: >>> On Wed, 3 Apr 2019 14:30:52 +0200 >>> Lifepillar wrote: >>>> SQLi

Re: [sqlite] ANN: SQLite3 Decimal Extension

On 4 Apr 2019, at 17:30, Lifepillar wrote: > >> You have the option to ignore the error, though, in which case you get +Inf: > > sqlite> delete from decTraps where flag = 'Division by zero'; > sqlite> select decStr(decDiv(1,0)); > Infinity Forgot to mention that in t

Re: [sqlite] ANN: SQLite3 Decimal Extension

On 4 Apr 2019, at 17:15, James K. Lowden wrote: > > On Wed, 3 Apr 2019 14:30:52 +0200 > Lifepillar wrote: > > >> SQLite3 Decimal is an extension implementing exact decimal arithmetic >> for SQLite3. It is currently unfinished and under development. > ... &

Re: [sqlite] ANN: SQLite3 Decimal Extension

On 4 Apr 2019, at 10:37, Thomas Kurz wrote: > > I appreciate your effort towards this extension. In my opinion, however, this > is (along with bigint-support) a feature that belongs into core (for that > reason alone to get math operations, comparisons, aggregates, etc. working in > an

Re: [sqlite] ANN: SQLite3 Decimal Extension

On 3 Apr 2019, at 19:37, Warren Young wrote: > > On Apr 3, 2019, at 6:30 AM, Lifepillar wrote: >> >> By default, the precision is limited to 39 digits and exponents must be >> in the range [-99,999,999,+99,999,999] (for some mathematical >> operations, the e

Re: [sqlite] ANN: SQLite3 Decimal Extension

> On 3 Apr 2019, at 20:04, Joshua Thomas Wise > wrote: > >> [Here, I must thank Dr. Hipp, with whom I had a brief email exchange >> severals moons ago, who convinced me that the IEEE 754 encoding was not >> an ideal storage format for databases] > > I’m curious, what were the reasons behind

Re: [sqlite] ANN: SQLite3 Decimal Extension

> > SQLite3 Decimal is an extension implementing exact decimal arithmetic > > for SQLite3. It is currently unfinished and under development. > > > I'm curious, what was your motivation for doing this? > Use cases envisioned for its use? Mainly financial applications. Beancount’s author

[sqlite] ANN: SQLite3 Decimal Extension

selapp.com/user/lifepillar/repository/sqlite3decimal Git mirror (which exists only for testing `fossil git export`...): https://github.com/lifepillar/sqlite3decimal-mirror Enjoy, Life. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlit

Re: [sqlite] Recursive references in subqueries

On 19/07/2018 15:53, R Smith wrote: On 2018/07/19 2:32 PM, Christian Duta wrote: WITH RECURSIVE   count_down(v) AS (     SELECT 5   UNION ALL     SELECT cd.v - 1     FROM (   SELECT cd.v   FROM count_down AS cd     ) AS cd     WHERE cd.v > 0   ) SELECT * FROM count_down; Error:

[sqlite] [OT} Posting through Gmane

I use Gmane to follow this mailing list and Fossil's. I have subscribed to both lists. While I can post through Gmane to this list, however, I cannot post to fossil-users: messages keep bouncing back with "Address not found, ..., The response was 550 unknown user". Is there any difference in the

Re: [sqlite] Free tool to visualize a schema?

On 15/04/2018 14:14, Csányi Pál wrote: 2018-04-15 14:08 GMT+02:00 R Smith : On 2018/04/15 11:52 AM, Csányi Pál wrote: Hi, I think the visualization of a schema helps to develop a sqlite database. I am searching for a free software, like SchemaCrawler. It is good, but

Re: [sqlite] Constraints must be defined last?

On 11/04/2018 19:45, Simon Slavin wrote: On 11 Apr 2018, at 6:41pm, J Decker wrote: Is there something about SQL that requires constraints to follow all column definitions? I don't know if it applies to SQL in general, but it is in SQLite:

Re: [sqlite] Loadable extension with shared state

On 03/01/2018 15:58, Lifepillar wrote: On 03/01/2018 15:48, Richard Hipp wrote: On 1/3/18, Lifepillar <lifepil...@lifepillar.me> wrote: Consider an extension that has some shared state, say a global `context` struct, whose value is used by a few user-defined SQL functions. Besides,

Re: [sqlite] Loadable extension with shared state

On 03/01/2018 15:48, Richard Hipp wrote: On 1/3/18, Lifepillar <lifepil...@lifepillar.me> wrote: Consider an extension that has some shared state, say a global `context` struct, whose value is used by a few user-defined SQL functions. Besides, assume that there are other SQL functions th

[sqlite] Loadable extension with shared state

Consider an extension that has some shared state, say a global `context` struct, whose value is used by a few user-defined SQL functions. Besides, assume that there are other SQL functions that can act on the global context. The question is: how do I turn this into a thread-safe extension?

Re: [sqlite] Efficient query to count number of leaves in a DAG.

On 02/01/2018 06:54, Dinu wrote: If a different perspective may be helpful to you: If moving overhead to writes is an option (ie you dont have many or time critical writes), then the tree descendants problem can be sped up to stellar speeds by using a path column. In a more relational spirit,

Re: [sqlite] How to prevent the insertion of cycles into a hierarchical table?

On 24/12/2017 11:56, Shane Dev wrote: Related to my previous question https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg107527.html, I want to prevent the client from inserting a cycle. For example - sqlite> .sch edges CREATE TABLE edges(parent integer not null, child

Re: [sqlite] How to detect cycles in a hierarchical table?

On 21/12/2017 17:13, E.Pasma wrote: Now I see the difference between UNION and UNION ALL in recursion. It is documented as below. Although it needs careful reading to understand that UNION effectively eliminates loops. Having a PostgreSQL background, I cannot recommend its SQL documentation

Re: [sqlite] How to detect cycles in a hierarchical table?

On 20/12/2017 22:31, Shane Dev wrote: Is there a query which can detect all cycles regardless of length? Not.. cough... particularly efficient, but simple: with recursive Paths(s,t) as ( select parent, child from Edges union select parent, t from Edges join Paths on child = s ) select

Re: [sqlite] How to detect cycles in a hierarchical table?

On 20/12/2017 22:31, Shane Dev wrote: Hello, I have an edges table - sqlite> .sch edges CREATE TABLE edges(parent, child); sqlite> select * from edges; parent child 1 2 1 3 2 4 3 1 4 5 5 2 Here we have two cycles - 1) 1 => 3 => 1 (length 1) 2) 2 => 4 =>

Re: [sqlite] sqlite3_aggregate_context() in xFinal callback does not zeroes out memory

On 17/12/2017 11:40, Lifepillar wrote: When I call a custom aggregate function on an empty table T, e.g., `select myaggr(A) from T`, the sqlite3_aggregate_context() call in the xFinal callback does not return zeroed out memory, but a block of seemingly uninitialized memory. Is that expected

[sqlite] sqlite3_aggregate_context() in xFinal callback does not zeroes out memory

When I call a custom aggregate function on an empty table T, e.g., `select myaggr(A) from T`, the sqlite3_aggregate_context() call in the xFinal callback does not return zeroed out memory, but a block of seemingly uninitialized memory. Is that expected? If so, how do I know that the xStep

Re: [sqlite] How to index data based on custom comparisons?

On 14/12/2017 13:14, Richard Hipp wrote: On 12/14/17, Lifepillar <lifepil...@lifepillar.me> 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

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 <lifepil...@lifepillar.me> 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

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 <lifepil...@lifepillar.me> wrote: I am implementing an extension for manipulating IEEE754 decimal numbers. Numbers are stored as blobs using a standard encoding. Numbers that are mathematically

[sqlite] How to index data based on custom comparisons?

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

Re: [sqlite] Cannot initialize statically linked extension

> Which it is, in this case. The OP said that both sqlite and th > extension are static libraries, so they’re both being linked directly > into the executable. > > I’m not sure what’s going on. Life, can you post a backtrace of the > crash? I'll try to make a minimal reproducible example.

Re: [sqlite] Cannot initialize statically linked extension

On 05/12/2017 09:24, Guy Harris wrote: > On Dec 4, 2017, at 3:42 PM, Keith Medcalf wrote: > >> On Monday, 4 December, 2017 15:44, Jens Alfke wrote: >> If one object is using, for example, the multithreaded runtime and the others are using the

Re: [sqlite] Cannot initialize statically linked extension

On 04/12/2017 20:59, Keith Medcalf wrote: You should only be defining SQLITE_CORE if in fact the extension is part of the core -- that is compiled and included (statically linked) to the core sqlite3.c compilation unit. In this case, the extension makes direct calls to the sqlite3 entry

[sqlite] Cannot initialize statically linked extension

Hello, I need some help to figure out a segfault. I have: - SQLite3 3.21.0 compiled as a static library; - a custom extension compiled as another static library, passing -DSQLITE_CORE. - a simple main program linked to both libraries. My extension has the typical structure, nothing fancy. My

Re: [sqlite] Compiling on Xcode

>Hi, ALL, >Right now my C Language option on the Xcode 5.1.1 is set to "GNU99". >When using this option I am getting a lot of different warnings like: > >Value Conversion Issue >Implicit conversion loses integer precision. > >I know its just a warning, but I prefer the code to be compiled clean.