Re: [sqlite] Issues about SQLite archive files

> On Mar 15, 2018, at 1:26 PM, Simon Slavin wrote: > > The built-in path functions of some operating systems interpret file paths > beginning with a path separator as relative to the root of the drive rather > than the current subdirectory. […] > A 'name' field in a SQLite archive file could

[sqlite] Issues about SQLite archive files

One important security issue and several unimportant ones. Security issue: The built-in path functions of some operating systems interpret file paths beginning with a path separator as relative to the root of the drive rather than the current subdirect

Re: [sqlite] Boolean casting

On 15 Mar 2018, at 7:04pm, Jens Alfke wrote: > it would be nice if boolean could be elevated to a data type Agreed. And I think it could be done without breaking backward compatibility [1], just by implementing the string 'BOOLEAN' as a type. The value stored can be encoded the same as integ

Re: [sqlite] Boolean casting

> On Mar 15, 2018, at 11:20 AM, Simon Slavin wrote: > > It's possible, with a lot of work, to figure out whether the columns in a > table were defined as having type BOOLEAN. IF you want to do this tell us > and we'll tell you how. However, if you are doing a calculation in a SELECT > like

Re: [sqlite] Indexing multiple values per row

> On Mar 15, 2018, at 11:27 AM, Jay Kreibich wrote: > > Recognize the fact that if you’re storing data in a JSON string, to the > database that is just one single value: a string. I am very well aware of that. What _I_ (and my co-workers) are implementing is a higher-level database layer* th

Re: [sqlite] Indexing multiple values per row

> On Mar 15, 2018, at 12:33 PM, Jens Alfke wrote: > > I'm wondering what the best way is to efficiently search for data values that > can appear multiple times in a table row. SQLite indexes, even expression > indexes, don't directly work for this because they obviously only index one > value

Re: [sqlite] Boolean casting

On 15 Mar 2018, at 4:43pm, Pavan Paolo wrote: > I use in a view the following snip of code to force the results to be > considered as Boolean: SQLite does not have a BOOLEAN type. If you use the word BOOLEAN where it expects to see a type name it will use an affinity of NUMBER. See sections

Re: [sqlite] EXTERNAL: Re: Bug in fsdir

On 3/15/18, Edwards, Mark C. wrote: > I put the amalgamated 3.22.0 release into my production code. Windows 10 is > the customer execution platform. Do I need to update? Probably not. A complete list of bugs fixed so far for 3.23.0 can be seen in the draft release notes at https://www.sqlite.or

[sqlite] Indexing multiple values per row

I'm wondering what the best way is to efficiently search for data values that can appear multiple times in a table row. SQLite indexes, even expression indexes, don't directly work for this because they obviously only index one value per row. Traditional relational-database design says to normal

Re: [sqlite] EXTERNAL: Re: Bug in fsdir

I put the amalgamated 3.22.0 release into my production code. Windows 10 is the customer execution platform. Do I need to update? ___ Mark C Edwards Chief Scientist, C2 Systems Engineering & Integration 779 Monika Ct Chubbuck, ID 83202 mark.c.edwa...@leidos

Re: [sqlite] Function design question

Simply bracket your server code with preprocessor directives like so: static void my_universal_function(sqlite3_context *context, int argc, sqlite3_value **argv) { #ifdef MY_SQLITE_EXTENSION_MODE #else #endif } Then create another makefile rule target or IDE project which defines the 'MY_S

[sqlite] Boolean casting

Dear all, I use in a view the following snip of code to force the results to be considered as Boolean: SELECT cast(start_processing_date is not null as BOOLEAN), ... I need this solution since in this way I can detect in java the Boolean data type by querying the ResultSet metadata rs.getMetaDat

Re: [sqlite] Bug in fsdir

On 3/15/18, Kees Nuyt wrote: > On Thu, 15 Mar 2018 15:55:05 +1100, Jake Thaw > wrote: > >>The following query causes a crash in fsdirNext on Windows 10. > > Fixed by Richard Hipp on trunk: > https://www.sqlite.org/src/timeline Credit where credit is due. Joe found and fixed the problem. All I

Re: [sqlite] Bug in fsdir

On Thu, 15 Mar 2018 15:55:05 +1100, Jake Thaw wrote: >The following query causes a crash in fsdirNext on Windows 10. > >SQLite version 3.22.0 2018-01-22 18:45:57 >Enter ".help" for usage hints. >Connected to a transient in-memory database. >Use ".open FILENAME" to reopen on a persistent database.

[sqlite] Embedded changes for the GPU

Team, I am not sure if this is the right forum for this request, or if a direct email would be better? For the past 7+ years I have worked on porting SQLite to run entirely on the GPU using Nvidia's CUDA providing access to host files. The GPU is like any other embedded processor and should ha

Re: [sqlite] Help with json1 query?

No, the keys would be arbitrarily chosen by the user. The rtree extension could be a possibility, I'll check it out. On Thu, Mar 15, 2018 at 12:56 AM, Wout Mertens wrote: > Can you elaborate on the metadata? Are the keys always the same, in which > case you could store them as columns? > > There

Re: [sqlite] Function design question

On 15 Mar 2018, at 11:40am, Toby Dickenson wrote: > But I also want to update these tables from the command line, when > these functions wont be available. Is there a way to construct the > trigger so that I get NULLs instead of errors when a custom function > doesnt exist? Or a better way to p

Re: [sqlite] count optimisation

While I take your point Ryan I think some of the examples are far removed from the case I highlighted. I mean if count(*) is the only query column and the RHS of the joins is ‘cross join t’ the optimisation would be simple. If the remaining joins end ‘cross join t2’ it could be optimised further

[sqlite] Function design question

I am using a trigger to maintain some audit information, for example updating row modification time on any insert or update. I now want to extend that mechanism to include other information from my application, for example logged in user name. I can do that by registering new functions to pass the

Re: [sqlite] count optimisation

On 2018/03/15 12:20 PM, x wrote: select count(*) from TblA cross join TblB is over 200 times slower than select (select count(*) from TblA) * (select count(*) from TblB); This is a human-level optimisation, it's not efficient for the database engine to do the optimisation. By human-level I

Re: [sqlite] [EXTERNAL] count optimisation

You’re right Gunter. I think it’s been pointed out to me before as well. Doh. From: sqlite-users on behalf of Hick Gunter Sent: Thursday, March 15, 2018 10:32:20 AM To: 'SQLite mailing list' Subject: Re: [sqlite] [EXTERNAL] count optimisation The statement "s

Re: [sqlite] [EXTERNAL] count optimisation

The statement "select count() from " is optimized to retrieve the count without visiting each row. This is well documented behaviour. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von x Gesendet: Donnerstag, 15. März 2018 11:

[sqlite] count optimisation

select count(*) from TblA cross join TblB is over 200 times slower than select (select count(*) from TblA) * (select count(*) from TblB); Not that it matters to me. I came across it by accident and mention it only in case it’s a missed optimisation.