[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

On Sat, May 14, 2016 at 4:17 PM, Simon Slavin wrote: > Those interested in SQLite might like to listen to > > > Having spent many years working directly in Richard's code and that of many hundreds of other programmers, I consider Richard to be by far the best

[sqlite] Encrypt the SQL query

> at some > point the encrypted SQL wiill have to be decrypted before SQLite > interprets it. Perhaps we could achieve some level of obfuscation by "pre-preparing" at compile time the set of all statements that the program uses, and storing the SQLite prepared statement objects in the

[sqlite] determining is-leap-year in sqlite

> > > select 2, 28 + (CAST(strftime("%j", c.year||"-12-31") AS INTEGER) % 365) > Here you assume that all years have either 365 or 366 days. Would that it were so! Look at the year 1752 -- you may notice something odd happened that September. :-) Eric

[sqlite] 10 minute Avg

On Thu, Nov 19, 2015 at 11:26 AM, Simon Slavin wrote: > > On 19 Nov 2015, at 4:06pm, Andrew Stewart > wrote: > > >Would storing the dateTime as Integer make the database and > indexes smaller? > > Yes. An integer will take a maximum of 8 bytes to store. A string's > storage needs at

[sqlite] Schema-less JSON SQLite DB?

Given the number of the threads in the list, it seems a > lot of people want hierarchical data in SQLite :-) > Throwing in another $.02 here, my company had a need to ingest JSON of various formats into SQLite databases in low-power embedded devices. We wrote the utility program described here:

[sqlite] json_* functions in sqlite

On Wed, Apr 22, 2015 at 2:17 PM, Eric Rubin-Smith wrote: > > On Tue, Apr 21, 2015 at 9:09 PM, Ashish Sharma > wrote: > >> Many times I store JSON data in sqlite. It will be useful if sqlite came >> with functions which understand JSON. Presto has a nice set >> ht

[sqlite] json_* functions in sqlite

On Tue, Apr 21, 2015 at 9:09 PM, Ashish Sharma wrote: > Hi > > Many times I store JSON data in sqlite. It will be useful if sqlite came > with functions which understand JSON. Presto has a nice set > https://prestodb.io/docs/current/functions/json.html > I wrote a little tool that more or less

Re: [sqlite] write internal blob vs external file

Dominique Devienne wrote: > > Looking at the sqlite web site and mailing lists shows that the SQLite > team > > has taken a stab at answering the question, "is it faster to read a blob > > out of sqlite or out of a file?". See the links below. > > > > Does the team have analogous guidance

[sqlite] write internal blob vs external file

Looking at the sqlite web site and mailing lists shows that the SQLite team has taken a stab at answering the question, "is it faster to read a blob out of sqlite or out of a file?". See the links below. Does the team have analogous guidance regarding write speeds? I'm also interested in the

Re: [sqlite] Hexadecimal integer literals

So far no one has raised the idea of using a "big int" layer to implement proper integer arithmetic past 64 bits. The fact that it hasn't been mentioned makes me worry that it's a blatantly silly idea for SQLite for some reason -- but I'm tossing it out there on the off chance that it's useful.

Re: [sqlite] v3.8.4.3 download

Eric Rubin-Smith wrote: > The docs don't get versioned like that AFAICT. There's just one central > set of docs. If behavior changes from one version to the next, that is > called out with text inside the documentation and in the change logs. > Some of the more major changes ha

Re: [sqlite] v3.8.4.3 download

Dave Wellman wrote: > Where can I download older versions of sqlite3, specifically v3.8.4.3 (or at > least v3.8.4.x). I don't need the source code, just the windows binaries. It looks like we can hack the download URIs to get the version you want. Noting from the download page that the windows

Re: [sqlite] think I need better error-handling guidance in the C API

Klaas V wrote: > BTW (someone else wrote this): to call a program 'crappy' sounds a wee > bit megalomanic... :-) Are you denying that crappy programs exist in the world, or are you saying that they do exist but that stating something true about them is megalomaniacal? Keeping in mind that

Re: [sqlite] think I need better error-handling guidance in the C API

RSmith wrote: > I do hope your request is escalated to a ticket. Yet a third person who completely agrees with me in every way! :-) How 'bout it, SQLite devs? > You seem well-versed in these matters, I believe there is a need for a > documentation specialist in the sqlite dev team, apply

Re: [sqlite] think I need better error-handling guidance in the C API

Stephan Beal wrote: > While i do fundamentally agree with your points, in practice it's not > as difficult as you seem to be making it out to be. The rule is really > simple: any non-0 code is an error unless the APIs specify otherwise, > and the step() API does indeed document 2 non-error,

Re: [sqlite] think I need better error-handling guidance in the C API

ut the API docs for sqlite3_step(), sqlite3_exec(), and sqlite3_prepare_v2()? I think the criticisms can be extended to other API calls. If the authors do not disagree then can we create a ticket against the docs? On Mon, Jun 30, 2014 at 2:47 PM, Eric Rubin-Smith <eas@gmail.com> wro

Re: [sqlite] think I need better error-handling guidance in the C API

Keith Medcalf wrote: > IF ResultCode == A_OK YipeeKiAiii else OhShitItDidntWork; > > Seems pretty straightforward to me. You handle the result codes you > know what to do with, There is a difference between things I don't personally know how to handle and things that the SQLite authors

[sqlite] think I need better error-handling guidance in the C API

I've been using the sqlite Tcl API for about 3 million years, with much delight. I'm venturing now into the C API -- hoping to put sqlite into a large monolithic "always on" C++ daemon. I would like to use the API in a way perfectly in line with how the authors intended. But it's sort of hard

Re: [sqlite] slowish R*Tree performance

Cory Nelson wrote: > Expand the prefix into the full feed:beef::etc > > Insert into a table (start binary(16), mask_length int) > > select top 1 binary,length from table where start <= @input order by > binary desc > > Check if the row is inside the range returned. This will take a

Re: [sqlite] slowish R*Tree performance

Cory Nelson wrote: > The phrase you're looking for here is "CIDR block". Well, I was avoiding the phrase on purpose :-). I was worried that using another bit of jargon -- one that is even more opaque than "prefix" to someone unfamiliar with the space -- did not seem likely to help get the

Re: [sqlite] slowish R*Tree performance

Carlos Ferreira wrote: > Regarding the R.Tree performance problem, > > What is the original problem that is causing slow performance in the > SQlite R-Tree implementation? I was populating my DB with bad data. In particular, I was first choosing a random prefix length, then filling up

Re: [sqlite] slowish R*Tree performance

Simon Slavin wrote: > Strongly suspect that although R*Trees produce an elegant solution to > your problem, the fact that they're a general case tool will make them too > slow to use for something like this. > > I propose an alternative solution, though I have not tried it and do not >

Re: [sqlite] slowish R*Tree performance

Carlos Ferreira wrote: > 1 - There a data type named IPV6 Address. 2 - there is a table where > this data type must be in. ( can be a set of fields, one blob, one string > ...) > > You want to: > > Given a certain IPV6, find in the database the existent IPV6 record with > the longest

Re: [sqlite] slowish R*Tree performance

On Sun, Jun 15, 2014 at 9:47 AM, Eric Rubin-Smith <eas@gmail.com> wrote: > Richard Hipp wrote: > > > What does this query return? > > > > SELECT count(*) FROM ipIndex > > WHERE minD1 <= 1220818432 and 1220818432 <= maxD1 > >

Re: [sqlite] slowish R*Tree performance

Richard Hipp wrote: > What does this query return? > > SELECT count(*) FROM ipIndex > WHERE minD1 <= 1220818432 and 1220818432 <= maxD1 > AND minD2 <= 2120561472 and 2120561472 <= maxD2 > AND minD3 <= 1685398080 and 1685398080 <= maxD3 > AND minD4 <= 1685755328

[sqlite] slowish R*Tree performance

I am exploring a mechanism for finding the longest covering IPv6 prefix for a given IPv6 address by leveraging SQLite 3.8.5's R*Tree feature. I'm getting pretty bad performance and would like to know if I'm doing something obviously wrong. A 1-dimensional R*Tree of integers of width 128 bits

[sqlite] lifetime of buffer referred to with SQLITE_STATIC

If I say sqlite_bind_text(...SQLITE_STATIC), I am promising that the buffer is going to stick around for a while. How long am I promising that it will stick around? Til the next statement reset()? Til the statement finalize()? Til the database close()?

[sqlite] ALWAYS/NEVER and assert()

I note that there are some assert() statements spread through the SQLite code base and not isolated just to the ALWAYS and NEVER macros. Why did the authors choose to write, for example, assert( pName==0 ); in sqlite3CreateIndex, rather than ALWAYS( pName==0 ); ? For the purposes of

[sqlite] minor typos in lang_corefunc.html's description of abs(X). domain of abs(X)

"The abs(X) function returns the absolute value of the numeric argument X. Abs(X) returns NULL if X is NULL. Abs(X) return 0.0 if X is a string or blob that cannot be converted to a numeric value. If X is the integer -9223372036854775807 then abs(X) throws an integer overflow error since there is