[sqlite] How many digits do you need ?

Please allow me a little levity, spinning off an earlier discussion of how many digits a decimal number type needs to store. " Carbrook, for instance, is at -27.673862 153.25624 and at -27.673861999297635 153.25624388146. [...] those 15-

Re: [sqlite] ANN: SQLite3 Decimal Extension

Oh of course, that makes sense. I suppose that means querying on REAL indexes should be slower than querying on INTEGER indexes, in the current SQLite3 implementation? Has a benchmark of this ever been done? > On Apr 3, 2019, at 5:29 PM, Richard Hipp wrote: > > On 4/3/19, Joshua Wise wrote:

Re: [sqlite] ANN: SQLite3 Decimal Extension

On 4/3/19, Joshua Wise wrote: > From my naive understanding, memcmp() is used to efficiently compare long > strings of bytes. But where in SQLite3 is it necessary to compare long > strings of floating point numbers? I, of course, can imagine SQL queries > plucking single floating point values from

Re: [sqlite] ANN: SQLite3 Decimal Extension

From my naive understanding, memcmp() is used to efficiently compare long strings of bytes. But where in SQLite3 is it necessary to compare long strings of floating point numbers? I, of course, can imagine SQL queries plucking single floating point values from rows or indexes, but I can’t imagin

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 exponent must not exceed 99,999 in abs

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 D

Re: [sqlite] Help with INDEXing a query

Thanks. I didn't know this. From: Luuk Sent: Wednesday, April 3, 2019 02:34 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Help with INDEXing a query On 3-4-2019 19:34, Jose Isaias Cabrera wrote: > Never mind, guys. I was missing the INDEX for the table for the first left

Re: [sqlite] Help with INDEXing a query

On 3-4-2019 19:34, Jose Isaias Cabrera wrote: Never mind, guys. I was missing the INDEX for the table for the first left join: CREATE INDEX PLE_ProjID ON Project_List_Extra (ProjID); Everything is nice, now. Thanks. From: Jose Isaias Cabrera Sent: Wednesday, April 3, 2019 01:02 PM To: sql

Re: [sqlite] ANN: SQLite3 Decimal Extension

> [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 Dr. Hipp’s opinion on this? > On Apr 3, 2019, at 1:56 PM, Simon Slav

Re: [sqlite] ANN: SQLite3 Decimal Extension

On 3 Apr 2019, at 6:51pm, Warren Young wrote: > On Apr 3, 2019, at 6:30 AM, Lifepillar wrote: > >> does SQLite support indexes on blobs? > > It claims to: Indeed. Be careful to verify whether, from the perspective of your programming language, it considers the first or the last byte to be m

Re: [sqlite] ANN: SQLite3 Decimal Extension

On Apr 3, 2019, at 6:30 AM, Lifepillar wrote: > > does SQLite support indexes on blobs? It claims to: $ sqlite3 x.db SQLite version 3.26.0 2018-12-01 12:34:55 Enter ".help" for usage hints. sqlite> create table x (a blob); sqlite> create index xi on x(a); sqlite> explain query plan select a fro

Re: [sqlite] ANN: SQLite3 Decimal Extension

On 3 Apr 2019, at 6:37pm, Warren Young wrote: > I once calculated that 30 digits was enough to give a precise location to > every particle the size of a grain of sand or larger in the known universe. > You just need to define as many columns in your database as is needed for > your physics: 3

Re: [sqlite] ANN: SQLite3 Decimal Extension

On Apr 3, 2019, at 7:05 AM, Dominique Devienne wrote: > > Seems like you're using non-ascii chars in your source code, notably > the #pragma mark. I think you’ve got two nits there, not one. As for the non-ASCII characters, they’re UTF-8, which is the de facto standard character set on the Int

Re: [sqlite] ANN: SQLite3 Decimal Extension

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 exponent must not exceed 99,999 in absolute value). That’s enough. :) I once calculated tha

Re: [sqlite] Help with INDEXing a query

Never mind, guys. I was missing the INDEX for the table for the first left join: CREATE INDEX PLE_ProjID ON Project_List_Extra (ProjID); Everything is nice, now. Thanks. From: Jose Isaias Cabrera Sent: Wednesday, April 3, 2019 01:02 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqli

[sqlite] Help with INDEXing a query

Greetings! I am trying to speed up this query, SELECT a.*,b.*,c.Area,d.Bus_Area FROM Project_List AS a LEFT JOIN Project_List_Extra AS b ON a.ProjID = b.ProjID LEFT JOIN Bus_IT_Areas_ORGs AS c ON a.IT_OBS = c.IT_OBS LEFT JOIN Business_OBS_List AS d ON a.Business_OBS = d.Bus_OBS

Re: [sqlite] Stack allocation upper bound with SQLITE_USE_ALLOCA

On Wed, Apr 3, 2019 at 8:55 AM Richard Hipp wrote: > > On 4/3/19, Jonathan Brandmeyer wrote: > > What is the upper bound for stack consumption under the > > SQLITE_USE_ALLOCA compile-time option? I see that there are a number > > of configurable size limits available as compile-time and/or run-t

Re: [sqlite] Stack allocation upper bound with SQLITE_USE_ALLOCA

Jonathan Brandmeyer wrote: > What is the upper bound for stack consumption under the > SQLITE_USE_ALLOCA compile-time option? I see that there are a number > of configurable size limits available as compile-time and/or run-time > options. Which ones affect the maximum alloca? > > Thanks, > Jona

Re: [sqlite] Stack allocation upper bound with SQLITE_USE_ALLOCA

On 4/3/19, Jonathan Brandmeyer wrote: > What is the upper bound for stack consumption under the > SQLITE_USE_ALLOCA compile-time option? I see that there are a number > of configurable size limits available as compile-time and/or run-time > options. Which ones affect the maximum alloca? > I thi

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 (http

[sqlite] Stack allocation upper bound with SQLITE_USE_ALLOCA

What is the upper bound for stack consumption under the SQLITE_USE_ALLOCA compile-time option? I see that there are a number of configurable size limits available as compile-time and/or run-time options. Which ones affect the maximum alloca? Thanks, Jonathan Brandmeyer __

Re: [sqlite] Why no persistent user settings in a database file?

On Wed, Apr 3, 2019 at 06:57 R Smith wrote: > Hi Tom, ... > About the CLI - It's a very useful piece of toolkit, but it's intent is ... > For more usability, there are a few good options in the World from CLI's > to GUI's. I see you already know SQLite studio, some of my favourites > you might try

Re: [sqlite] ANN: SQLite3 Decimal Extension

On Wed, Apr 3, 2019 at 2:31 PM Lifepillar wrote: > [I hope that this kind of announcement is not off-topic here] > Not at all, IMHO. > SQLite3 Decimal is an extension implementing exact decimal arithmetic > for SQLite3. It is currently unfinished and under development. I'm curious, what was

Re: [sqlite] Why no persistent user settings in a database file?

On Wed, Apr 3, 2019 at 05:52 Tom Browder wrote: > After coming back to SQLite from a long absence, I was surprised that > setting things like .mode and .headers in a database didn't stay that way > after exiting the file. > ... Okay, I agree with all the excellent arguments about NOT keeping use

[sqlite] ANN: SQLite3 Decimal Extension

[I hope that this kind of announcement is not off-topic here] SQLite3 Decimal is an extension implementing exact decimal arithmetic for SQLite3. It is currently unfinished and under development. At this point anything, including the user interface and the internals, is subject to change. I am pub

Re: [sqlite] Why no persistent user settings in a database file?

On 03 Apr 2019, at 11:52, Tom Browder wrote: > After coming back to SQLite from a long absence, I was surprised that > setting things like .mode and .headers in a database didn't stay that way > after exiting the file. > > Then I remembered something about a resource file and found .sqliterc on a

Re: [sqlite] Why no persistent user settings in a database file?

Hi Tom, The settings you mention, such as .headers and .mode (along with a slew of others) are usability settings contained in the Command-Line facility (the CLI) offered by the makers of SQLite. It is however by far not the only such interface, nor is it in any way related to the data in th

Re: [sqlite] Why no persistent user settings in a database file?

Tom, Because the file is the database, your preferences for how things are displayed are just that, your preferences. Don't combine the two. If you have two users with different preferences, who wins? We move the SQLite database around all the time, across different servers. We do not want t

Re: [sqlite] [EXTERNAL] Why no persistent user settings in a database file?

User preferences should not be stored in a database file, particularly settings affecting the presentation layer of the access tool (sqlite shell) that have nothing to do with the function of the storage layer. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mail

Re: [sqlite] Why no persistent user settings in a database file?

On Wed, Apr 3, 2019 at 05:52 Tom Browder wrote: > After coming back to SQLite from a long absence, I was surprised that > setting things like .mode and .headers in a database didn't stay that way > after exiting the file. > > Then I remembered something about a resource file and found .sqliterc o

[sqlite] Why no persistent user settings in a database file?

After coming back to SQLite from a long absence, I was surprised that setting things like .mode and .headers in a database didn't stay that way after exiting the file. Then I remembered something about a resource file and found .sqliterc on an internet search and that allowed the persistent settin

[sqlite] Missing docs: sqliterc, lint

I have not been able to find any docs on the site about .sqliterc or lint. There is some information on sqliterc in the mailing list archives, and there is: sqlite3> .help lint But if one is not already aware of either one, how does one find out about them? Best regards, -Tom _

Re: [sqlite] Can I get help with db design for SQLite use?

On Wed, Apr 3, 2019 at 02:50 Shawn Wagner wrote: > Yay Perl! My favorite language. DBD::SQLite is definitely one of the > better, fuller featured sqlite bindings out there. Though Tom is using > perl6, not perl5. I have no idea how its version compares. ... Glad to hear, Shawn, and the Perl 6 v

Re: [sqlite] Can I get help with db design for SQLite use?

On Wed, Apr 3, 2019 at 02:08 Rob Willett wrote: > Tom, > > We use the Perl DB::SQLite module. It works very well and I cannot > recall a single issue with it in the last four years. There's not as > much support for Perl on this mailing list as it's not as popular, but > most issues you will prob

Re: [sqlite] Can I get help with db design for SQLite use?

Shawn, I will investigate File::Slurper. Rather than pollute this list, send me a mail on rob.sql...@robertwillett.com about the issues you had. Thanks Rob On 3 Apr 2019, at 8:50, Shawn Wagner wrote: Yay Perl! My favorite language. DBD::SQLite is definitely one of the better, fuller featur

Re: [sqlite] Can I get help with db design for SQLite use?

Yay Perl! My favorite language. DBD::SQLite is definitely one of the better, fuller featured sqlite bindings out there. Though Tom is using perl6, not perl5. I have no idea how its version compares. (At the risk of going off topic, File::Slurp has issues. File::Slurper is a better alternative.) O

Re: [sqlite] Can I get help with db design for SQLite use?

Tom, We use the Perl DB::SQLite module. It works very well and I cannot recall a single issue with it in the last four years. There's not as much support for Perl on this mailing list as it's not as popular, but most issues you will probably encounter will be at the design level rather than a