Re: [sqlite] Multiple SELECTs in one call

[Keith Medcalf]

You mean something like

select *
  from t
 where a in (select a from t where e != 1
 union
 select a from t where d > 3
 union
 SELECT a from t where c != 1 AND b != 1);

or more succinctly:

select *
  from t
 where a in (select a 
  from t 
 where e != 1
or d > 3
or (c != 1
and b != 1));

or do you mean:

select *
  from t
 where a in (select a from t where e != 1
 intersect
 select a from t where d > 3
 intersect
 SELECT a from t where c != 1 AND b != 1);

or more succinctly:

select *
  from t
 where a in (select a 
  from t 
 where e != 1
   and d > 3
   and c != 1
   and b != 1);

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Jose Isaias Cabrera
>Sent: Wednesday, 6 February, 2019 21:22
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Multiple SELECTs in one call
>
>
>Greetings.
>
>I need some help from you gurus to have multiple selects, but the
>sequence is important.  For example,
>
>create table t (a, b, c, d, e);
>insert into t values (1,2,3,4,5);
>insert into t values (2,2,3,4,5);
>insert into t values (3,3,3,3,3);
>insert into t values (4,1,1,1,1);
>insert into t values (5,1,1,2,2);
>insert into t values (6,2,3,2,2);
>
>what I want to do is to do a select that first chooses all items
>where e != 1, and then the result of that select be used to create
>more selects based on other criteria. For example,
>
>SELECT * from t WHERE a IN (SELECT a from t where e != 1);
>
>But I want to use the result of (SELECT a from t where e != 1); to
>run another select (SELECT a from t where d > 3); and then, one more
>select (SELECT a from t where c != 1 AND b != 1); and the final
>select would be "SELECT * from t WHERE a IN" which would have the
>result.  I hope someone understands. :-)  Thanks for the help.
>
>josé
>
>___
>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] Multiple SELECTs in one call

[Simon Slavin]

On 7 Feb 2019, at 4:21am, Jose Isaias Cabrera  wrote:

>  want to use the result of (SELECT a from t where e != 1); to run another 
> select (SELECT a from t where d > 3); and then, one more select (SELECT a 
> from t where c != 1 AND b != 1);

How are these related to each other ?

Do you want one big result which has the rows returned by the first SELECT, and 
also the rows returned by the second SELECT, and also the rows returned by the 
third SELECT ?

If so, you use UNION:



Or did I get it wrong ?

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

[sqlite] Multiple SELECTs in one call

[Jose Isaias Cabrera]

Greetings.

I need some help from you gurus to have multiple selects, but the sequence is 
important.  For example,

create table t (a, b, c, d, e);
insert into t values (1,2,3,4,5);
insert into t values (2,2,3,4,5);
insert into t values (3,3,3,3,3);
insert into t values (4,1,1,1,1);
insert into t values (5,1,1,2,2);
insert into t values (6,2,3,2,2);

what I want to do is to do a select that first chooses all items where e != 1, 
and then the result of that select be used to create more selects based on 
other criteria. For example,

SELECT * from t WHERE a IN (SELECT a from t where e != 1);

But I want to use the result of (SELECT a from t where e != 1); to run another 
select (SELECT a from t where d > 3); and then, one more select (SELECT a from 
t where c != 1 AND b != 1); and the final select would be "SELECT * from t 
WHERE a IN" which would have the result.  I hope someone understands. :-)  
Thanks for the help.

josé

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

Re: [sqlite] GROUP BY and ICU collation

[Richard Hipp]

On 2/6/19, li...@herger.net  wrote:
> Hi there,
>
> I'm trying to create a list with an index list. Eg. I have artists:
>
> Sting
> Šuma Čovjek
> Suzanne Vega
>
> That's the sort order I'd get using an ICU collation. "Šuma Čovjek"
> would be sorted as "Suma..." as expected.
>
> Now I'd like to create an index bar by providing groups of the first
> character:
>
> SELECT SUBSTR(name,1,1), count(distinct id) FROM artists GROUP BY
> SUBSTR(name,1,1) ORDER BY name COLLATE de_DE


Aren't you missing a COLLATE clause after the GROUP BY term?

... GROUP BY SUBSTR(name,1,1) COLLATE de_DE ...

>
> Now this would create two groups for "S" and "Š", leading to unexpected
> behaviour in my index bar. It seems as if only ORDER BY would use the
> collation, but not GROUP BY. What am I doing wrong?
>
> Michael
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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] GROUP BY and ICU collation

[lists]

Hi there,

I'm trying to create a list with an index list. Eg. I have artists:

Sting
Šuma Čovjek
Suzanne Vega

That's the sort order I'd get using an ICU collation. "Šuma Čovjek" 
would be sorted as "Suma..." as expected.


Now I'd like to create an index bar by providing groups of the first 
character:


SELECT SUBSTR(name,1,1), count(distinct id) FROM artists GROUP BY 
SUBSTR(name,1,1) ORDER BY name COLLATE de_DE


Now this would create two groups for "S" and "Š", leading to unexpected 
behaviour in my index bar. It seems as if only ORDER BY would use the 
collation, but not GROUP BY. What am I doing wrong?


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

Re: [sqlite] Bug due to left join strength reduction optimization?

[Richard Hipp]

On 2/6/19, Danny  wrote:
> This has been fixed by revision d840e. Thanks for the quick response, drh!

Just to be clear:  Dan found the fix.  I merely checked it in.

-- 
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 due to left join strength reduction optimization?

[Danny]

This has been fixed by revision d840e. Thanks for the quick response, drh!

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

Re: [sqlite] Sqlite3: create a database from file using one line command-line script

[Keith Medcalf]

sqlite3 database.db < myscript.sql


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of kostasvgt
>Sent: Wednesday, 6 February, 2019 12:47
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Sqlite3: create a database from file using one line
>command-line script
>
>Hi, I need to run a command to create a database and populate it from
>a
>.sql file at once from command-line, not dot-commands.
>Is there any way to do this in one line?
>
>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

Re: [sqlite] ISO8601 vs Numeric Timestamp for Date Storage

[Keith Medcalf]

On Wednesday, 6 February, 2019 12:55, Ben Asher wrote:

> Hi there! We're having a debate at my company about date storage in
> SQLite.  SQLite has builtin support for ISO8601 in its date functions, 
> so some folks have started storing dates as ISO8601 SQLite-compatible 
> date strings.

> Are there pitfalls to storing dates this way compared to a unix
> timestamp?

Well, the most common pitfalls are the failure to store "instant time strings" 
and instead to only store ambiguous data.  This is amplified by the fact that 
the built-in datetime functions only produce (output) ambiguous timestrings and 
then only with limited precision (ie, usually not suitable for putting back 
into a database).  You can work around this by always storing such strings in 
UT1 (UTC).

For example, a full IS8601 "instant time string" looks something like: 
2019-02-06T15:45:47.305872603-07:00

This format can be handled on input to the datetime functions producing a 
correct UT1 instant time.  The input routines are general enough that they can 
accept varients such as a space in place of the "T", a space before the + or - 
timezone specifier, and an arbitrary number of decimal seconds (though the 
internal storage is Julian milliseconds, so the internal representation is 
limited to millisecond precision.  Z can be used to denote +0:00 and -0:00.

The "output" formats however are somewhat lacking as they do not specify 
decimal seconds (unless you use your own strftime format string) and do not 
include timezone information (thus making the time ambiguous).

The "localtime" and "utc" modifiers are processed by the underlying OS and are 
subject to the vagaries of such handling on the OS, which varies by OS.  For 
example, Windows conversions are only accurate for the current daylight rules.  
Linux works properly.  I do not know about other OSes.  "localtime" means the 
timezone of the computer.

Storing a "Unix Timestamp" or other numeric format of course is not subject to 
these vagaries since it is an offset in POSIX seconds (or days) from a fixed 
epoch and that epoch is almost always UT1 (UTC).  Note that you can store 
floating point unix timestamps, you just need to tell the internal datetime 
functions that they are working with unix epochs.

Storing the stamps as ISO8601 strings has the advantage that anyone can 
recognize them and use the standard SQLite3 shell tool to work with them.  
Numeric epoch offsets are not so "human readable" and you need to be more 
knowledgeable to see and work with them using the shell tools, but they are 
harder to screw up.

> I'm curious to know if anyone has experience and would highly recommend
> sticking to one or the other for a particular reason. I'd also be
> grateful if anyone could point me to any articles exploring this subject.

I prefer working with numeric epoch dates myself because they are (a) more 
compact and (b) inherently sortable.  ISO8601 strings are "mostly sortable" so 
long as they all have the same offset from UT1 -- the timestring format though 
does require about 31 bytes to store the same information as can be stored in 
an 8-byte float.

I have some patches that modify the datetime function library to always output 
full instant timestrings and that can use the Olsen database (which needs to be 
loaded into the database) to do timezone conversions inside SQLite3 and to 
maintain the offsets within the datetime objects.  (Unixtime uses the VDBE 
current statement time and simply convert the internal Julian Milliseconds into 
Unix Epoch Seconds as a double.  UnixInstant calls the 
GetSystemTimePreciseAsFileTime (windows) API and returns the current machine 
time as a unix epoch float (it does the Precise variant gets the currrent time, 
not the time as of the last tick).

>sqlite tz.db
SQLite version 3.27.0 2019-02-06 01:18:36
Enter ".help" for usage hints.
sqlite> select datetime('now', 'Canada/Mountain');
2019-02-06 16:09:08.039 -07:00
sqlite> select datetime('now', 'Europe/Moscow');
2019-02-07 02:09:23.943 +03:00
sqlite> select unixtime(), unixinstant();
1549496112.409|1549496112.40904
sqlite> select datetime(unixinstant(), 'unixepoch', 'America/Regina');
2019-02-06 17:35:46.849 -06:00
sqlite>

Or using the geopoly extension find the Olsen timezone for a given lat/long:

>gettz -110 50
3.7.2 (tags/v3.7.2:9a3ffc0492, Dec 23 2018, 23:09:28) [MSC v.1916 64 bit 
(AMD64)]
SQlite3 Source ID 2019-02-06 01:18:36 
3087a0c31e9fbfaedb7cf10a2fda59bc22318ff178614aefcc00ac83d9a7alt2

Find timezone at Location -110.00 50.00
Exact Row(tzid='America/Regina') 00:00:00.004773

st = time.time()
for row in db.cursor().execute("""select tzid
from tz_geopoly
   where geopoly_overlap(_shape, 
geopoly_regular(?1, ?2, 0.0002699785, 8))
 and geopoly_contains_point(_shape, ?1, ?2)
order by abs(geopoly_area(_shape)), 
instr(lower(tzid), 

Re: [sqlite] ISO8601 vs Numeric Timestamp for Date Storage

[Jens Alfke]

> On Feb 6, 2019, at 2:21 PM, J Decker  wrote:
> 
> From a JS point of view new Date( ISOString )and .toISOString() are quick
> and available….

Available, yes, but expensive (compared to using a number.)

> ISO format parsing is NOT that hard it's just a minor varient of
> parsing floats.  (maybe the conversion from parts into numeric?)

Most date parsing (and formatting) functions are general-purpose and take 
format strings. That adds to the overhead. (SQLite does use a parser that’s 
specific to ISO-8661 and looks a lot faster.)

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

Re: [sqlite] ISO8601 vs Numeric Timestamp for Date Storage

[J Decker]

From a JS point of view new Date( ISOString )and .toISOString() are quick
and available
ISO format parsing is NOT that hard it's just a minor varient of
parsing floats.  (maybe the conversion from parts into numeric?)
Haven't bothered to benchmark it.
Date Diffs easily avaialble.

On Wed, Feb 6, 2019 at 1:59 PM Jens Alfke  wrote:

>
>
> > On Feb 6, 2019, at 11:55 AM, Ben Asher  wrote:
> >
> > Hi there! We're having a debate at my company about date storage in
> SQLite.
> > SQLite has builtin support for ISO8601 in its date functions, so some
> folks
> > have started storing dates as ISO8601 SQLite-compatible date strings. Are
> > there pitfalls to storing dates this way compared to a unix timestamp?
>
> Date-string parsing can be surprisingly expensive. I’ve seen it as a
> hot-spot when profiling a program of mine that analyzed some XML-based data
> sets, and I’ve also seen it slow down CouchDB map functions.
>
> Date strings are many times larger than the equivalent numeric timestamps.
>
> On the plus side, they’re much more readable if someone has to look at the
> raw data in the database.
>
> —Jens
> ___
> 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] ISO8601 vs Numeric Timestamp for Date Storage

[Jens Alfke]

> On Feb 6, 2019, at 11:55 AM, Ben Asher  wrote:
> 
> Hi there! We're having a debate at my company about date storage in SQLite.
> SQLite has builtin support for ISO8601 in its date functions, so some folks
> have started storing dates as ISO8601 SQLite-compatible date strings. Are
> there pitfalls to storing dates this way compared to a unix timestamp?

Date-string parsing can be surprisingly expensive. I’ve seen it as a hot-spot 
when profiling a program of mine that analyzed some XML-based data sets, and 
I’ve also seen it slow down CouchDB map functions.

Date strings are many times larger than the equivalent numeric timestamps.

On the plus side, they’re much more readable if someone has to look at the raw 
data in the database.

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

Re: [sqlite] ISO8601 vs Numeric Timestamp for Date Storage

[Andy Bennett]

Hi,


Integer unix timestamps are only accurate to one second, where ISO8601
(at least as implemented by SQLite) can go to 1 millisecond.  Also you
have to know the epoch to interpret a unix timestamp - not everybody
uses 1970-01-01 00:00:00.  Will people be able to figure out what the
field value means when somebody discovers your data in 100 years?

The SQLite implementation is *not* subject to the 2038-01-19 integer
overflow problem.  But other systems that might interact with SQLite
are and so that is something to keep in mind as well.

On the other hand, integer timestamps take up less space in the file.

The third option is a fractional julian day number stored as an 8-byte
floating point value.


Do you want to store timestamps from the future (for example, a 
calendar-style application) or will all your timestamps always represent 
points in the past?


If you want to be able to accurately store future timestamps then the 
integer seconds and julian day number representations require some extra 
metadata.


Whilst past timestamps can always be deterministically converted from their 
local timezone to UTC and back again, this is not the case for timestamps 
in the future.


This is because the timezone and daylight saving rules change from 
time-to-time. In the integer seconds and julian day number representations, 
future timestamps must be stored in local time along with their timezone so 
that information is not lost.


In a perfect world you would store local time and location (rather than 
timezone) as places do also occasionally move timezone. However, this is 
difficult to work with because databases that map from suitably represented 
locations to timezones are more difficult to come by than databases that 
describe the relationships between timezones.



This is not a hypothetical problem or one where the error is small.

Bangladesh cancelled their daylight savings observances in 2010. In 2011 
Russia made their daylight savings time permenant; moving onto it in the 
Spring and never moving back.


If you had prematurely converted timestamps from these places to UTC then 
your error would be measured on the order of an hour.


In 1994 Eastern Kiribati crossed the International Date Line 
(bureaucratically speaking). If you had prematurely converted a timestamp 
from there then your error would be on the order of a day!




Doing consistent arithmentic on future dates is left as an exercise for the 
reader (sorry)!






Regards,
@ndy

--
andy...@ashurst.eu.org
http://www.ashurst.eu.org/
0x7EBA75FF
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ISO8601 vs Numeric Timestamp for Date Storage

[Dennis Clarke]

On 2/6/19 9:10 PM, Richard Hipp wrote:

On 2/6/19, Ben Asher  wrote:

Hi there! We're having a debate at my company about date storage in SQLite.
SQLite has builtin support for ISO8601 in its date functions, so some folks
have started storing dates as ISO8601 SQLite-compatible date strings. Are


In my own work, I have variously used ISO8601 text dates, unix
timestamp integers, and fractional Julian Day numbers to represent
dates and times, according to whichever worked best in that particular
application.  Since it is easy to convert between them all, this has
never been a big problem.



Why not merely use the data from :

struct timespec tn;
ec = clock_gettime( CLOCK_REALTIME,  );

That should give some sort of data down to the nanosec and if you have
decent ntp in place ( and black magic ) it may even be accurate. :-)


--
Dennis Clarke
RISC-V/SPARC/PPC/ARM/CISC
UNIX and Linux spoken
GreyBeard and suspenders optional
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ISO8601 vs Numeric Timestamp for Date Storage

[Richard Hipp]

On 2/6/19, Ben Asher  wrote:
> Hi there! We're having a debate at my company about date storage in SQLite.
> SQLite has builtin support for ISO8601 in its date functions, so some folks
> have started storing dates as ISO8601 SQLite-compatible date strings. Are
> there pitfalls to storing dates this way compared to a unix timestamp? I'm
> curious to know if anyone has experience and would highly recommend
> sticking to one or the other for a particular reason. I'd also be grateful
> if anyone could point me to any articles exploring this subject.

Integer unix timestamps are only accurate to one second, where ISO8601
(at least as implemented by SQLite) can go to 1 millisecond.  Also you
have to know the epoch to interpret a unix timestamp - not everybody
uses 1970-01-01 00:00:00.  Will people be able to figure out what the
field value means when somebody discovers your data in 100 years?

The SQLite implementation is *not* subject to the 2038-01-19 integer
overflow problem.  But other systems that might interact with SQLite
are and so that is something to keep in mind as well.

On the other hand, integer timestamps take up less space in the file.

The third option is a fractional julian day number stored as an 8-byte
floating point value.  Such values are accurate to about 1 millisecond
during the modern era, and it is much easier to compute the number of
days by which two dates differ (you just subtract).  There are no
overflow crises pending, though precision does decay as you move
further and further away from the epoch, though for dates in nearby
centuries this is not a factor. Space requirements are in between
integer unix timestamps and ISO8601 strings.

In my own work, I have variously used ISO8601 text dates, unix
timestamp integers, and fractional Julian Day numbers to represent
dates and times, according to whichever worked best in that particular
application.  Since it is easy to convert between them all, this has
never been a big problem.

-- 
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] Sqlite3: create a database from file using one line command-line script

[Donald Griggs]

sqlite3  myDatabase.db  ".read myCommands.sql"


On Wed, Feb 6, 2019 at 3:30 PM kostasvgt  wrote:

> Hi, I need to run a command to create a database and populate it from a
> .sql file at once from command-line, not dot-commands.
> Is there any way to do this in one line?
>
> 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

Re: [sqlite] ISO8601 vs Numeric Timestamp for Date Storage

[Dennis Clarke]

On 2/6/19 7:55 PM, Ben Asher wrote:

Hi there! We're having a debate at my company about date storage in SQLite.
SQLite has builtin support for ISO8601 in its date functions, so some folks
have started storing dates as ISO8601 SQLite-compatible date strings. Are
there pitfalls to storing dates this way compared to a unix timestamp? I'm
curious to know if anyone has experience and would highly recommend
sticking to one or the other for a particular reason. I'd also be grateful
if anyone could point me to any articles exploring this subject.

Thanks!

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



Isn't ISO 8601 designed for communications with humans in an
international and standard way?  It is not for storage of data.
At least in my opinion one needs a data element that one may store
and later fetch and then perform computation and comparisons with.
That would be the unix timestamp way of things. The ISO 8601 format
is for display to human beings and other soft squishy creatures. I
don't see how you can check two dates readily unless you have a pile
of libs in your pocket that do that.

So .. this works real well :

l$ date -u ; tn; sleep 4; date -u; tn -f
Wed Feb  6 20:40:54 UTC 2019
1549485654
Wed Feb  6 20:40:58 UTC 2019
1549485658.659547276
l$

Easy to compare those unix timestamps ripped out of an struct timespec.

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

Re: [sqlite] althttpd.c CGI Script Requires a Throw Away First Line of Output?

[Richard Hipp]

On 2/5/19, Carl Chave  wrote:
> I'm experimenting with the althttpd.c web server.  As a simple first test I
> created a static html file and a lua script file.  The static file displays
> as expected in firefox.  The lua file, which simply reads in the same
> static html file and writes it back to stdout ends up being truncated and
> displayed incorrectly in firefox.
>
> If I begin the output of the script with a newline and then continue
> writing out the rest of the static file contents it's fine.

The reply from a CGI script should start with a line of the form:

Content-Type: text/html

(or some other MIME type) and be followed by a blank line and only
then the content.  There are other options, for example to provoke a
server redirect or to set a status code other than 200.  See
https://en.wikipedia.org/wiki/Common_Gateway_Interface for additional
information,

>
> The section of source code and comment that starts at line number 1706 and
> continues through line 1733 seems to be the section that's causing it.  I
> took the entire block out, recompiled and tested the lua script again
> (without starting output with a newline) and it worked as expected.  So the
> script just read in the static file and spit it back out unaltered and it
> displays the same as the static file in my browser.  The returned headers
> are different but the returned html is the same.
>
> What is the correct approach?  Should I return some minimum set of headers
> from the CGI script? Content type seems to be conspicuously absent when
> comparing the headers between the static file and CGI script.  Or should I
> just start all script output with a newline?
>
> Thanks,
> Carl
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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] Sqlite3: create a database from file using one line command-line script

[kostasvgt]

Hi, I need to run a command to create a database and populate it from a
.sql file at once from command-line, not dot-commands.
Is there any way to do this in one line?

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

[sqlite] althttpd.c CGI Script Requires a Throw Away First Line of Output?

[Carl Chave]

I'm experimenting with the althttpd.c web server.  As a simple first test I
created a static html file and a lua script file.  The static file displays
as expected in firefox.  The lua file, which simply reads in the same
static html file and writes it back to stdout ends up being truncated and
displayed incorrectly in firefox.

If I begin the output of the script with a newline and then continue
writing out the rest of the static file contents it's fine.

The section of source code and comment that starts at line number 1706 and
continues through line 1733 seems to be the section that's causing it.  I
took the entire block out, recompiled and tested the lua script again
(without starting output with a newline) and it worked as expected.  So the
script just read in the static file and spit it back out unaltered and it
displays the same as the static file in my browser.  The returned headers
are different but the returned html is the same.

What is the correct approach?  Should I return some minimum set of headers
from the CGI script? Content type seems to be conspicuously absent when
comparing the headers between the static file and CGI script.  Or should I
just start all script output with a newline?

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

[sqlite] ISO8601 vs Numeric Timestamp for Date Storage

[Ben Asher]

Hi there! We're having a debate at my company about date storage in SQLite.
SQLite has builtin support for ISO8601 in its date functions, so some folks
have started storing dates as ISO8601 SQLite-compatible date strings. Are
there pitfalls to storing dates this way compared to a unix timestamp? I'm
curious to know if anyone has experience and would highly recommend
sticking to one or the other for a particular reason. I'd also be grateful
if anyone could point me to any articles exploring this subject.

Thanks!

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

Re: [sqlite] Displaying hierarchical structure

[Bart Smissaert]

I can select the rank as in the previous e-mail with this recursive query:

with recursive paths(id, folder, path) as
  (select id, folder, folder from folders where parent_id is null union
  select folders.id, folders.folder, paths.path || '-' ||
substr('0', length(folders.id)) || folders.id from folders join
paths
  where folders.parent_id = paths.id)
select replace(path, 'Main', '01') as path from paths order by path

Not managed yet though to use this to update the rank column in the table
Folders.
Also not sure how to avoid the replace and get the rank value 1
directly from the ID.

Any idea how to manage these two?

RBS

On Mon, Feb 4, 2019 at 10:41 PM Bart Smissaert 
wrote:

> Looking at this approach of a hierarchical system:
> https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql
>
> Given a table like this:
>
> ID PARENT_ID FOLDER RANK
> ---
> 1   0   Main1
> 2   1   CC   1-02
> 3   1   BB   1-03
> 4   1   AA   1-04
> 5   2   B 1-02-05
> 6   2   A 1-02-06
>
> What SQL should I use to update the field RANK if the first row is known
> to be 01, but all the
> next rows are null? I tried with a non-recursive query, but couldn't work
> it out.
>
> RBS
>
>
>
> On Thu, Jan 31, 2019 at 8:02 AM Bart Smissaert 
> wrote:
>
>> This looks a nice and simple way to display the tree in the right order
>> without recursive SQL:
>>
>> https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql
>>
>> Will do some testing on large numbers to see how the 2 methods compare
>> speed-wise.
>>
>> RBS
>>
>> On Tue, Jan 29, 2019 at 8:33 PM Keith Medcalf 
>> wrote:
>>
>>>
>>> See https://sqlite.org/lang_with.html
>>>
>>> which includes how to traverse the recursive tree in either depth-first
>>> or breadth-first order.
>>>
>>> Why do you need the closure table at all?
>>>
>>>
>>> create table folders
>>> (
>>>idinteger primary key,
>>>parent_id integer references folders,
>>>name  text not null collate nocase,
>>>check (not (parent_id is null and id != 1))
>>> );
>>>
>>> insert into folders values (1, null, 'Folder1'),
>>>(2, 1, 'Folder2'),
>>>(3, 1, 'Folder3'),
>>>(4, 1, 'Folder4'),
>>>(5, 2, 'Folder5'),
>>>(6, 2, 'Folder6');
>>> .head on
>>> .mode column
>>> .width 30 9 38
>>>
>>> -- depth first
>>>
>>> with foo (id, parent_id, name, level, path)
>>>   as (select folders.*, 0, folders.name
>>> from folders
>>>where parent_id is null
>>>union all
>>>   select folders.*, level + 1, foo.path || '\' || folders.name
>>> from foo, folders
>>>where folders.parent_id = foo.id
>>> order by 4
>>>  )
>>> select substr('', 1, (level - 1) * 4) || name as
>>> Folder,
>>>coalesce(parent_id, 0) as PARENT_ID,
>>>path as FullPath
>>>   from foo;
>>>
>>>
>>> -- breadth first
>>>
>>> with foo (id, parent_id, name, level, path)
>>>   as (select folders.*, 0, folders.name
>>> from folders
>>>where parent_id is null
>>>union all
>>>   select folders.*, level + 1, foo.path || '\' || folders.name
>>> from foo, folders
>>>where folders.parent_id = foo.id
>>> order by 4 desc
>>>  )
>>> select substr('', 1, (level - 1) * 4) || name as
>>> Folder,
>>>coalesce(parent_id, 0) as PARENT_ID,
>>>path as FullPath
>>>   from foo;
>>>
>>>
>>>
>>> SQLite version 3.27.0 2019-01-28 00:42:06
>>> Enter ".help" for usage hints.
>>> Connected to a transient in-memory database.
>>> Use ".open FILENAME" to reopen on a persistent database.
>>> sqlite> create table folders
>>>...> (
>>>...>idinteger primary key,
>>>...>parent_id integer references folders,
>>>...>name  text not null collate nocase,
>>>...>check (not (parent_id is null and id != 1))
>>>...> );
>>> sqlite>
>>> sqlite> insert into folders values (1, null, 'Folder1'),
>>>...>(2, 1, 'Folder2'),
>>>...>(3, 1, 'Folder3'),
>>>...>(4, 1, 'Folder4'),
>>>...>(5, 2, 'Folder5'),
>>>...>(6, 2, 'Folder6');
>>> sqlite> .head on
>>> sqlite> .mode column
>>> sqlite> .width 30 9 38
>>> sqlite>
>>> sqlite> -- depth first
>>> sqlite>
>>> sqlite> with foo (id, parent_id, name, level, path)
>>>...>   as (select folders.*, 0, 

Re: [sqlite] Min/Max and skip-scan optimizations

[R Smith]

On 2019/02/06 12:12 AM, Gerlando Falauto wrote:


The use case involves retaining as much data as the storage can possibly
hold (so a bunch of gigabytes).
I could've just used directories and logfiles instead of abusing a
relational database but I just thought it would more convenient to issue a
query and use a cursor.


There is nothing wrong with using a database to store a list of values, 
as another poster pointed out, [insert high made-up number]% of schemata 
out there are basically just that, but I like Keith's suggestion, since 
you did decide to DB it, why not make it nicely relational too?



the table is often more efficient than threading a lookup via another
index into the query plan. Sometimes crafting a new temp BTree Index for
(a) specific field(s) on a materialized set of data might also be judged
faster than re-establishing links between said data and its original Index.


Do you think restoring the original primary key (instead of ROWID) and
dropping the index would make any difference?


I do think it would make a difference (almost any change would), but I 
am not sure it would make all the difference. I would however suggest, 
at the very least, to test this and see.






I pre-populated the table with a realistic use case scenario and ran
ANALYZE.
I'm not planning on using ANALYZE on the real system -- though I might
indeed pre-populate sqlite_stat1 with typical values as suggested in the
docs.


This is fine. I would ask - did your "test" data include a gigabyte or 
more data? The amount, cardinality and shape of the data are all most 
important for ANALYZE to provide good information.



If you can demonstrate a true degradation //...

Yes, in the worst case, adding the ORDER BY clause (2 vs.1, 4 vs.3) leads
to a perceivable degradation in terms of both seek time (several seconds
vs. milliseconds to get the first row) and occupied disk space.


I must have missed this, apologies, that is certainly a very true 
degradation. Note that the entire query delivery should be taken into 
consideration. The first row can often be delivered near instantaneous 
with following rows taking progressively longer. Very often the time it 
takes to deliver the first row is compensated by the time that is saved 
later along the subsequent rows. The QP takes this into consideration.


A good example is getting a set of data, say 100 rows, sorting it first 
and then just spitting it out from memory. The preparation (aka first 
row delivery) will take time, all the rest will be instant. Contrast 
that with a query that needs no sorting, it might produce rows as it 
scans the table, the first of which might appear instantly (since it's 
at the top of the table and satisfies the WHERE clause), but all the 
next qualifying rows might take a long while to produce depending on 
where they fall within the table. In the end the fully traversed cursor 
may take similar amounts of time.


The QP cannot know before-hand how many "hits" it would encounter, so 
has to use a basic pre-made guide and/or help from the ANALYZE data to 
best guess which route is better - and you can easily construct a 
non-usual set of data for which it will choose wrong every time, and for 
which "fixing" it will negatively affect more common sets of data.




As I already said, my use case *is* quite unusual. Definitely not something
you'd normally use a relational database for.


That does not matter - if the query planner can do better, it should - 
unless of course changing the decision tree will negatively affect 
another more widely used query case. (This is the hard part to establish.)



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