Re: [sqlite] Issues about SQLite archive files

[Jens Alfke]

> 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 be edited to hold such a path.  
> This would be a vulnerability if a utility used to 'unpack' an archive is 
> accidentally given too much power, as 'install' scripts often are.

It’s pretty common for install scripts to write to absolute paths, since many 
types of installable components need to go in specific locations in the 
filesystem. Any time you run an installer you’re implicitly giving it 
permission to write anywhere in the filesystem that you can access; fortunately 
the really sensitive locations (/etc, /System/Library, etc.) are locked down to 
root accounts.

But for the general purpose extraction CLI, it’d be wise to either fail if an 
absolute path is found, or allow absolute paths only if a special flag is given.

> Because not all operating systems use the same character as a subdirectory 
> separator, you might document whether archives always use the '/' character 
> as a path separator.

+1. If sqlar is to be a cross-platform archive format like Zip, it should have 
a single format for representing paths.

> Does the deflated stream include a header which says "what follows is 
> deflated data" ?  If not, what will you do when users want to use better 
> algorithms than 'Deflate’ ?

It looks as though it doesn’t. The discussion of sqlar_uncompress() shows that 
it tells compressed data apart from uncompressed by comparing the data size 
against the original file size, which would be unnecessary if there were 
metadata identifying the compression algorithm.

I agree it would be a good idea to add such metadata, either in the blob itself 
or as a table column. The ‘deflate’ algorithm is pretty long in the tooth, and 
newer algorithms like lz4 and zstd offer much better compression and 
performance. It would be wise to future-proof the archive format to allow for 
them.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Issues about SQLite archive files

[Simon Slavin]

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 subdirectory.  For instance

tmp/read.me <-- subdirectory called 'tmp' of default path
/tmp/read.me <-- subdirectory called 'tmp' of the default drive's root

A 'name' field in a SQLite archive file could be edited to hold such a path.  
This would be a vulnerability if a utility used to 'unpack' an archive is 
accidentally given too much power, as 'install' scripts often are.  I would 
suggest some attention be paid to this issue in the command-line tools which 
understand archives, that built-in functions which store the 'name' field not 
store leading path separators, and that functions which restore from the 'name' 
field filter out leading path separators.  This could be done with a single 
'sqlar_sanitizeName()' function (you can probably think of a better name for 
it) called by both packer and expander functions.

You might also want to ponder whether a 'name' starting with 'c:\' should be 
allowed, and what the command-line tools should do about it if it isn't.

Other issues:

Because not all operating systems use the same character as a subdirectory 
separator, you might document whether archives always use the '/' character as 
a path separator.  Proper programmers know what they're doing, but naive 
Windows users might not.

Is there a good reason for the 'sz' column to be called 'sz' and not 'size' or 
'length' ?  I dislike pointless abbreviation.

If you're renaming the 'sz' field, please consider whether to rename 'name' to 
'path', which is closer to the industry standard.

Does the deflated stream include a header which says "what follows is deflated 
data" ?  If not, what will you do when users want to use better algorithms than 
'Deflate' ?  You need either a column which tells the user which compression 
method was used (including 'none') or a function which extracts this 
information from the stored BLOB.

If you expect SQLite archive files to eventually feature other methods of 
compression than 'Inflate', the documentation should make it clear whether this 
would be done by changing sqlar.c or by supplying other files like sqlar.c.  In 
the latter case, the name and functions of sqlar.c should be changed to reflect 
that they are the 'Inflate' versions.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Boolean casting

[Simon Slavin]

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 integers 0 and 1.

[1] Except for programmers who were specifically playing games with the truth 
value of values like 0.5.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Boolean casting

[Jens Alfke]

> 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
> 
>SELECT name,(membershipType == 'y') FROM Members
> 
> The values returned will be 0 or 1 and there will be no way to tell that 
> SQLite did a boolean calculation to return that result.

To jump in here: This is also a problem for certain uses of the JSON1 
extension, since JSON data does distinguish between true/false and 1/o. An 
application may need to know whether a query result is a JSON number or a 
boolean, for example. It’s on my to-do list to figure out a good way to do this.

I see that some limited boolean support is coming in 3.23; it would be nice if 
boolean could be elevated to a data type, or at least if integer column values 
in a query could be tagged to indicate that they originated as booleans.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Indexing multiple values per row

[Jens Alfke]

> 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* that _does_ store its data as JSON.

>  The database has no knowledge and understanding of that value beyond the 
> fact it is a string.  Asking the database to index or do something with the 
> arbitrarily constructed sub-values you’ve created is outside the scope of 
> what the database can do, because you’re storing data in a format outside the 
> scope of the database.

You may not be aware of SQLite's JSON1 extension? It does enable exactly this — 
you can easily index a property of a JSON value, for example, by creating an 
index on a json_extract() expression that retrieves a property from the JSON 
column.

> If you do want to use database functions and queries to deal with this kind 
> of thing, store the data in a way the database understands it and can use it… 
> for example, a table that includes “patient_id, timestamp, temp” with “temp” 
> being a SINGLE numeric value.

Couchbase's users/customers do not store their data this way; they store it as 
JSON. Making this data queryable on mobile devices via SQLite is my job :) It’s 
working quite well, but it currently lacks the ability to do fast (indexed) 
searches over the contents of arrays and objects. Doing so will require some 
kind of transformation like what you’re talking about, but the JSON is still 
the source of truth, so the transformed tables need to mirror that.

> Don’t have a “source table.”  It’s a very non-relational way to store data 
> anyways.

You say that like it’s a bad thing … Non-relational databases** are rather a 
big deal currently. Implementing one atop a relational database does sound a 
bit odd, but it actually makes a lot of sense for our use case.

—Jens

* https://www.couchbase.com/products/mobile
** https://en.wikipedia.org/wiki/NoSQL
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Indexing multiple values per row

[Jay Kreibich]

> 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 per row. Traditional relational-database design says to normalize the 
> schema by storing the multiple values in separate rows, but what if the data 
> to be indexed is JSON and you need to leave it in that form?
> 
> For example, let's say I have a table like
>   patient_id: 12345
>   temps: "[98.6, 99.1, 101.3, 100.0, 98.9]"
> and I want to run queries on temperature data, like 'patients who've had a 
> temperature above 101'. And I need better than O(n) performance.

Recognize the fact that if you’re storing data in a JSON string, to the 
database that is just one single value: a string.  The database has no 
knowledge and understanding of that value beyond the fact it is a string.  
Asking the database to index or do something with the arbitrarily constructed 
sub-values you’ve created is outside the scope of what the database can do, 
because you’re storing data in a format outside the scope of the database.

If you do want to use database functions and queries to deal with this kind of 
thing, store the data in a way the database understands it and can use it… for 
example, a table that includes “patient_id, timestamp, temp” with “temp” being 
a SINGLE numeric value.  The patient_id column can be a foreign key back the 
full patient record.  Such a design is easier to insert, update, and just about 
everything else.


> In the past my project used map/reduce to support this, essentially 
> implementing its own index system on top of SQLite tables. In this case it 
> would create a table (patient_id integer primary key, temp number) and 
> populate it by scanning the patient table. This can obviously be indexed 
> easily, but updating the table before a query when the source table has 
> changed is a pain in the butt.

Then get rid of the array and just store the values that way for everything.  
Don’t have a “source table.”  It’s a very non-relational way to store data 
anyways.

  -j


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Boolean casting

[Simon Slavin]

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 2.1 
and 3.1.1 of



> I need this solution since in this way I can detect in java the Boolean data 
> type by querying the ResultSet metadata rs.getMetaData().getColumnType() and 
> trigger a proper visualization in a table.

I presume you want to display the words 'TRUE' or "FALSE' rather than a number.

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

SELECT name,(membershipType == 'y') FROM Members

The values returned will be 0 or 1 and there will be no way to tell that SQLite 
did a boolean calculation to return that result.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

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.org/draft/releaselog/3_23_0.html

On the other hand, if you want to run with the latest pre-release
snapshot (downloaded from the https://www.sqlite.org/download.html
page) that would be great.  We always appreciate beta testing.  Let us
know if you encounter any problems.  Note that I ran a complete TH3
test overnight last night (on linux) with no issues reported.  That's
197,490,286 tests.  The snapshot is table.

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

[sqlite] Indexing multiple values per row

[Jens Alfke]

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 normalize the 
schema by storing the multiple values in separate rows, but what if the data to 
be indexed is JSON and you need to leave it in that form?

For example, let's say I have a table like
patient_id: 12345
temps: "[98.6, 99.1, 101.3, 100.0, 98.9]"
and I want to run queries on temperature data, like 'patients who've had a 
temperature above 101'. And I need better than O(n) performance.

In the past my project used map/reduce to support this, essentially 
implementing its own index system on top of SQLite tables. In this case it 
would create a table (patient_id integer primary key, temp number) and populate 
it by scanning the patient table. This can obviously be indexed easily, but 
updating the table before a query when the source table has changed is a pain 
in the butt.

I believe a SQL "materialized view" would do what I want, but SQLite doesn't 
support those; its views seem to be just syntactic sugar or macros around 
SELECT queries.

Other than that, my best idea so far is to simplify the map/reduce updating by 
adding triggers on the source table that will add & remove rows from the index 
table.

Is there any other clever way I could do this? (I've been using SQLite for 14 
years and I still keep learning about more clever things it can do…)

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Edwards, Mark C.]

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.com
Mobile: 208-241-7982



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Thursday, March 15, 2018 10:38 AM
To: SQLite mailing list 
Subject: EXTERNAL: 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 did was 
merge it.

--
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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Function design question

[petern]

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_SQLITE_EXTENSION_MODE' to compile the common code file(s) as an
extension module for use on the command line.  Read about extensions here:

https://sqlite.org/loadext.html

By default, SQLite will return NULL when a function makes no return value
call.  BTW, per your preference, the logically complementary directive
#ifndef might work better for you.

Obviously you are free to choose more meaningful and compact macro
directive name than 'MY_SQLITE_EXTENSION_MODE'.  I chose that macro name
only to make this reply clear to the complete novice.

In practice you may find a lot of code will be able to meaningfully run in
the command line context - or possibly communicate with a running
application server instance capable of computing answers for the command
line context.

Peter



On Thu, Mar 15, 2018 at 4:40 AM, Toby Dickenson  wrote:

> 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 extra information from the
> application to the trigger.
>
> 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 pass information into the trigger
> other than functions?
>
> Thanks,
> ___
> 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

[sqlite] Boolean casting

[Pavan Paolo]

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.getMetaData().getColumnType() and 
trigger a proper visualization in a table.

Anyway, this cause to get column name a bit messy, reporting the 
"CAST(non_compliant ..." itself as title of the column.
If I try to use an alias such as the following, I fix the title but I loose the 
Boolean data type.
select cast(start_processing_date is not null as BOOLEAN) as my_check_name, ...

Is this behavior expected? How I can work on it?

Thank you,
Paolo
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in fsdir

[Richard Hipp]

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
did was merge it.

-- 
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] Bug in fsdir

[Kees Nuyt]

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> SELECT name FROM fsdir('.') JOIN (VALUES(1),(2));
>.
>./sqlite3.exe
>./sqlite3_analyzer.exe
>./sqldiff.exe
>.
>
>Adding an ORDER BY clause seems to provide workaround for this particular
>case.
>

Fixed by Richard Hipp on trunk:
https://www.sqlite.org/src/timeline 

-- 
Regards,
Kees Nuyt
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Embedded changes for the GPU

[Sky Morey]

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 have SQLite.

http://on-demand.gputechconf.com/gtc/2016/presentation/s6652-sky-morey-sqlite.pdf
http://on-demand.gputechconf.com/gtc/2016/video/S6652.html

This project has reached a natural breaking point and I am re-platforming, 
updating said project to the latest SQLite and in the current SQLite Fossil 
repository for automatic upgrades, with an eventual goal, If your team finds it 
valuable, of slipstreaming into the code base as another embedded platform.

In order to support embedding SQLite on the GPU libcu has been created, which 
acts like a C function library, and extends to host resources to the GPU.

libcu - https://github.com/BclEx/libcu (will be moving to 
https://github.com/libcu/libcu)

sqlite-cuda - c, cpp, cuda enabled git mirror of SQLite sources: 
https://github.com/libcu/sqlite-cuda


Therefore I have three asks:

Firstly, unfortunately CUDA only supports a C++ compiler, and SQLite currently 
will not compile using the C++ ruleset. I have resolved all casting issues 
while support both C and C++, and with only minimal modifications: simple 
casts,  _cplusplus wrapped #defines ,  and modifications to build scripts.

  *   Optionally enable C and C++ compiler support - I can provide these 
changes, or help re-apply them to a branch for your evaluation.

Secondly, every CUDA method and field/constant must be prefixed with keyword(s) 
which target the Host or GPU. A standard define can be used similar the 
existing SQLITE_API and SQLITE_CDECL defines.


  *   Add SQLITE_METHOD, SQLITE_FIELD, SQLITE_CONTANT defines applied to every 
method and field - I can provide these changes, or help re-apply them to a 
branch for your evaluation.

Third, CUDA as an embedded platform require its own declarations, similar to 
those for the existing vxworks.h header. Add cuda.h file, and add to build 
scripts.


  *   Add cuda.h file and include in build scripts - I can provide these 
changes, or help re-apply them to a branch for your evaluation.


Thank you,

-.. . --. / ... -- .- .-. - . .-. / -.. .. --. .. - .- .-..

Sky Morey
Chief Architect, DEG
913.951.3104 (direct) | 816.304.4341 (mobile)

www.DEGdigital.com | 913.498.9988 (office) | 
@smorey2

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with json1 query?

[Charles Leifer]

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's also the https://sqlite.org/rtree.html extension which lets you
> efficiently query multidimensional range data.
>
> If there is truly no schema, what you propose is the only way AFAIK.
>
> On Wed, Feb 28, 2018, 10:52 PM Charles Leifer,  wrote:
>
> > Hi,
> >
> > I'm prototyping a little graph library using SQLite. My idea is to store
> > vertices in a simple table like this:
> >
> > CREATE TABLE "vertex" ("key" TEXT NOT NULL PRIMARY KEY, "metadata" JSON);
> > CREATE TABLE "edge" (
> > "id" INTEGER NOT NULL PRIMARY KEY,
> > "src" TEXT NOT NULL,
> > "dest" TEXT NOT NULL,
> > "metadata" JSON,
> > FOREIGN KEY ("src") REFERENCES "vertex" ("key"),
> > FOREIGN KEY ("dest") REFERENCES "vertex" ("key"));
> >
> > What I'd like to do is allow querying of edges (or vertices) using a
> > *partial* metadata object. So if I had the following JSON object stored
> in
> > an edge's metadata:
> >
> > {"k1": "v1", "k2": "v2", "k3": "v3"}
> >
> > The user could provide me an object like {"k1": "v1", "k3": "v3"} and I
> > would be able to match the above edge's metadata.
> >
> > I can see decomposing the user-provided dictionary and building up
> multiple
> > equality tests using the json_extract() function, e.g.:
> >
> > select * from edge where json_extract(metadata, '$.k1') = 'v1' AND
> > json_extract(metadata, '$.k3') = 'v3';
> >
> > But I was hoping there would be a more elegant way to express this that
> > someone would be able to share? It seems as though I should be able to
> use
> > `json_each()` (or even `json_tree()` if metadata could be nested?), but
> I'm
> > not sure how to formulate the query.
> >
> > It'd be great if there were a JSON function like "json_contains()" where
> I
> > could write:
> >
> > select * from edge where json_contains(metadata, '$', '{"k1": "v1", "k3":
> > "v3"}');
> >
> > Any help appreciated!
> >
> > Charlie
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Function design question

[Simon Slavin]

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 pass information into the trigger
> other than functions?

I can't answer your question, but if you're interested in having a function 
called automatically when your database is updated, you might be interested in 
using



Because of how this works you would want to avoid this function using sqlite3_ 
calls (explicitly mentioned in the documentation, possibly to avoid recursion). 
 For this reason, if you use this hook your audit file would probably be best 
as a text file, opened and written using normal file operations rather than 
SQLite calls.

Another way to log changes is to use



It should always return SQLITE_OK, but it can note when it is called with 
commands which would modify your database.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] count optimisation

[x]

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 and so on.



Is it worth sqlite checking for such specific cases? Very probably not. Like I 
said it doesn’t matter to me, I pointed it out just in case.




From: sqlite-users  on behalf of 
R Smith 
Sent: Thursday, March 15, 2018 11:25:18 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: 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 mean it is in the same category as knowing that
[((x + 1) / 50) * 100] - 2x] / 2
always evaluates to exactly 1  for all values of x.

Replacing all that formula with *1* when compiling as an optimisation
WILL WORK most definitely, but the number of such formulas we can come
up with is infinite, there is no point having to ask the compiler to
handle any one of those infinite variations as "special".

Another silly example is a Query of the form:

select American_President from [Any Table];

Which we can currently simply optimise with:

select 'Donald Trump';

but you don't want the query engine to be doing that. Ever.


To apply all of this to your specific case, how about if the query was
in stead:

select count(*), avg(col1) from TblA cross join TblB

or indeed

select count(*) from TblA, TblB CROSS JOIN TblC JOIN TblD ON 1=1


All these /can/ be optimised, but should be optimised by the programmer,
not the Query engine.


Cheers,
Ryan


___
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

[sqlite] Function design question

[Toby Dickenson]

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 extra information from the
application to the trigger.

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 pass information into the trigger
other than functions?

Thanks,
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] count optimisation

[R Smith]

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 mean it is in the same category as knowing that
[((x + 1) / 50) * 100] - 2x] / 2
always evaluates to exactly 1  for all values of x.

Replacing all that formula with *1* when compiling as an optimisation 
WILL WORK most definitely, but the number of such formulas we can come 
up with is infinite, there is no point having to ask the compiler to 
handle any one of those infinite variations as "special".


Another silly example is a Query of the form:

select American_President from [Any Table];

Which we can currently simply optimise with:

select 'Donald Trump';

but you don't want the query engine to be doing that. Ever.


To apply all of this to your specific case, how about if the query was 
in stead:


select count(*), avg(col1) from TblA cross join TblB

or indeed

select count(*) from TblA, TblB CROSS JOIN TblC JOIN TblD ON 1=1


All these /can/ be optimised, but should be optimised by the programmer, 
not the Query engine.



Cheers,
Ryan


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] count optimisation

[x]

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 "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:20
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [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.

___
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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] count optimisation

[Hick Gunter]

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:20
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [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.

___
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

[sqlite] count optimisation

[x]

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.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users