[sqlite] Feature suggestion / requesst

[Scott Robison]

Background: I never finished a degree back in the dark ages, but
recently was provided an opportunity to earn a degree to go along with
my experience at a really affordable price. As a result, I'm taking
various classes to demonstrate my worthiness. :)

Last semester I had a class that used Oracle. I still have nightmares.

This semester I am taking a class that has exposed me to postgresql
for the first time. I can appreciate why the SQLite teams question is
WWPGD when considering new features.

I've encountered a feature that I think would be awesome:
https://www.postgresql.org/docs/9.3/static/dml-returning.html

Example: INSERT INTO blah (this, that, another) VALUES (x, y, z) RETURNING id;

It seems a very handy "single step" way (from the perspective of the
SQL programmer) to "select" some data from insert / update / delete
statements.

I concede to anyone who thinks this isn't very light their argument,
and agree it would add some amount of heft to SQLite. I suspect not
much, but I also know how easy it is for people who have no idea to
say that to me about my own software. I don't know. I concede to
anyone else who thinks we already have ways to do this their argument,
my thoughts are just that this could greatly simplify a lot of sql
code that currently has to prepare and execute at least two statements
to accomplish what is conceptually an atomic task.

Thank you for your time.

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

Re: [sqlite] Subject: Re: SQL Date Import

[Simon Slavin]

On 2 Jun 2018, at 8:55pm, Thomas Kurz  wrote:

> Are there any plans to implement a DATETIME and/or TIMESTAMP field types?

No.  SQLite has many routines which understand standard ways of storing 
datestamps:



So you can store IS0-8601-format strings, unix epoch, or Julian day, and use 
SQLite routines to do things like "get four weeks before the Monday just 
previous to this date" and "get the third Wednesday of the month after this 
date".

One problem with having an actual internal date format is how to dump it into a 
text file or to a text interface.  You end up turning it into a number or a 
string anyway, so you might was well store it that way.

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

Re: [sqlite] Subject: Re: SQL Date Import

[Abroży Nieprzełoży]

> Are there any plans to implement a DATETIME and/or TIMESTAMP field types?

I don't think so. The SQLite team really cares about backward compatibility.

You can store timestamp as a unixepoch integer or as a text in format
supported by date/time functions.
http://www.sqlite.org/draft/lang_datefunc.html


2018-06-02 21:55 GMT+02:00, Thomas Kurz :
> Are there any plans to implement a DATETIME and/or TIMESTAMP field types?
>
> - Original Message -
> From: Simon Slavin 
> To: SQLite mailing list 
> Sent: Saturday, June 2, 2018, 21:04:10
> Subject: [sqlite] Subject: Re:  SQL Date Import
>
> On 2 Jun 2018, at 7:32pm, dmp  wrote:
>
>> By the way, most databases give exactly that INSERT when dumping data
>> for DATE, TIME, TIMESTAMP, etc., text. I'm not advocating a preferred
>> type for storage here.
>
> I think your proposed programme of experimentation is the right way to
> pursue this.  But I wanted to save you some time.
>
> SQLite doesn't have a DATE type.  You can store dates in a SQLite database
> as text, or integers or floating point numbers (e.g. "20180602", a number of
> days, a number of seconds).  But when you ask for a value, that's what
> you'll get back.  Any interpretation of that value as a date is up to you or
> your software.
>
> Simon.
> ___
> 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] Subject: Re: SQL Date Import

[Thomas Kurz]

Are there any plans to implement a DATETIME and/or TIMESTAMP field types?

- Original Message - 
From: Simon Slavin 
To: SQLite mailing list 
Sent: Saturday, June 2, 2018, 21:04:10
Subject: [sqlite] Subject: Re:  SQL Date Import

On 2 Jun 2018, at 7:32pm, dmp  wrote:

> By the way, most databases give exactly that INSERT when dumping data
> for DATE, TIME, TIMESTAMP, etc., text. I'm not advocating a preferred
> type for storage here.

I think your proposed programme of experimentation is the right way to pursue 
this.  But I wanted to save you some time.

SQLite doesn't have a DATE type.  You can store dates in a SQLite database as 
text, or integers or floating point numbers (e.g. "20180602", a number of days, 
a number of seconds).  But when you ask for a value, that's what you'll get 
back.  Any interpretation of that value as a date is up to you or your software.

Simon.
___
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] Subject: Re: SQL Date Import

[Simon Slavin]

On 2 Jun 2018, at 7:32pm, dmp  wrote:

> By the way, most databases give exactly that INSERT when dumping data
> for DATE, TIME, TIMESTAMP, etc., text. I'm not advocating a preferred
> type for storage here.

I think your proposed programme of experimentation is the right way to pursue 
this.  But I wanted to save you some time.

SQLite doesn't have a DATE type.  You can store dates in a SQLite database as 
text, or integers or floating point numbers (e.g. "20180602", a number of days, 
a number of seconds).  But when you ask for a value, that's what you'll get 
back.  Any interpretation of that value as a date is up to you or your software.

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

[sqlite] Subject: Re: SQL Date Import

[dmp]

Keith Medcalf wrote:
> Yes, and the database will store the data as entered/bound if it
> cannot be converted to the requested storage type (column affinity).

Yes, that was my understanding and there-lies the problem. A column
of type DATE, TIME, or TIMESTAMP that may have NUMERIC and TEXT data.

> This is VERY IMPORTANT for you to understand fully and completely
> including all the rules for storage class and affinity conversions
> and how they are determined.  I would recommend that you SAY what
> you mean, and not confuse things (including yourself) by using
> "prayerful" data type declarations that are NOT in the proper set
> (INTEGER / REAL / NUMERIC / TEXT / BLOB).

Unfortunately as a middle man between a database designer and a user,
who maybe the table creator, I do not get to decide what they define
and what they put in a table, a combination of types for Date perhaps,
or maybe just TEXT. The example given INSERT could give you the former.

By the way, most databases give exactly that INSERT when dumping data
for DATE, TIME, TIMESTAMP, etc., text. I'm not advocating a preferred
type for storage here.

Thank you for the response. I think I will go ahead and install SQLite
on a machine so that I can experiment some more directly via the command
line, before deciding on an action to take on the issues I'm having.

danap.

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

[sqlite] Request to add define SQLITE_PREPARE_NONE

[Eduardo Morras]

Hello Dr. Hipps

There is a #define SQLITE_PREPARE_PERSISTENT  x01 line in sqlite3 source code 
for sqlite3_prepare_v3() flags. 

Could you add SQLITE_PREPARE_NONE meaning no flag set?

Thanks

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

Re: [sqlite] random rows

[Yuriy M. Kaminskiy]

On 06/01/18 04:41 , Abroży Nieprzełoży wrote:
> 2018-06-01 2:12 GMT+02:00, Torsten Curdt :
>> I need to get some random rows from a large(ish) table.
>>
>> The following seems to be the most straight forward - but not the fastest.
>>
>>   SELECT * FROM table ORDER BY random() limit 200
>>
>> Is there a faster/better approach?
> hmm... i think:
>
> begin transaction;
> query 'select count(*) from tab' to get total number of records, name it N
> randomly select 200 integers from the range 0 to N-1
> for each number query 'select * from tab limit 1 offset ?' with '?'
> bound to selected number
> end transaction;

Most likely, no. OFFSET is O(n), so this is O(n*m).
Sort is O(n*log(n)).

I'd probably consider this:

SELECT * FROM (
  SELECT * FROM foo
   WHERE RANDOM() < (
 SELECT (200.0/COUNT(*) - 0.5)*(16.0*1024*1024*1024*1024*1024*1024)
   FROM foo))
ORDER BY RANDOM();

But, obviously, it won't always return 200 rows (it takes random rows
with certain probability).

sqlite> CREATE TABLE foo (a,b blob,c,d);
sqlite> INSERT INTO foo VALUES (1,1,1,1);
sqlite> INSERT INTO foo
  SELECT RANDOM(), RANDOMBLOB(RANDOM() % 512), RANDOM(), RANDOM() FROM foo;
-- ... repeat several times ...
.timer on
sqlite> SELECT COUNT(*) FROM foo;
262144
Run Time: real 0.007 user 0.004000 sys 0.00

sqlite> SELECT COUNT(*) FROM (
  SELECT * FROM foo ORDER BY RANDOM() LIMIT 200);
200
Run Time: real 1.381 user 1.368000 sys 0.012000

sqlite> SELECT COUNT(*) FROM (
  SELECT * FROM foo WHERE rowid IN (
SELECT rowid FROM foo ORDER BY RANDOM() LIMIT 200));
200
Run Time: real 0.975 user 0.964000 sys 0.008000

sqlite> SELECT COUNT(*) FROM (
  SELECT * FROM (
SELECT * FROM foo WHERE RANDOM() < (
  SELECT (200.0/COUNT(*) - 0.5)*(16.0*1024*1024*1024*1024*1024*1024)
FROM foo))
  ORDER BY RANDOM());
198
Run Time: real 0.266 user 0.26 sys 0.00

(measured on sqlite3-3.19.3 on x86/32bit, YMMV; beware of query flattening).

Choose your poison.

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

[sqlite] ext/misc/csv.c breaks on single-column CSVs as at 3.23.1 2018-04-10

[Trent W. Buck]

[Please CC me in replies, as I am not subscribed to this mailing list.]

The ext/misc/csv.c loadable extension handles single-column CSVs incorrectly.
Note that this is *not* the same code as ".mode csv" + ".import 1.csv t1".
Ref. https://sqlite.org/csv.html

A minimal reproduction follows.
A full build transcript is attached.
Note that RFC4180 specifies CRLF, but
this issue is reproducible in CSVs with Unix-style LF EOLs.

# gcc -g -fPIC -shared ext/misc/csv.c -o csv.so
# printf '1\r\n' >1.csv
# printf '1,\r\n' >2.csv
# ./sqlite3
SQLite version 3.23.1 2018-04-10 17:39:29
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .load ./csv
sqlite> CREATE VIRTUAL TABLE temp.t2 USING csv(filename='2.csv');
sqlite> select * from t2;
1|
sqlite> CREATE VIRTUAL TABLE temp.t1 USING csv(filename='1.csv');
sqlite> select * from t1 limit 5;
1




sqlite> select count(*) from t2;
1
sqlite> select count(*) from t1;
  C-c C-cError: interrupted
sqlite>
# wget -nv https://sqlite.org/2018/sqlite-src-3230100.zip
2018-06-01 02:30:58 URL:https://sqlite.org/2018/sqlite-src-3230100.zip 
[10849436/10849436] -> "sqlite-src-3230100.zip" [1]
# unzip sqlite-src-3230100.zip
# cd sqlite-src-3230100
# ./configure --enable-silent-rules && make
configure: WARNING: unrecognized options: --enable-silent-rules
checking build system type... x86_64-unknown-linux-gnu
checking host system type... x86_64-unknown-linux-gnu
checking for gcc... gcc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables... 
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking for a sed that does not truncate output... /bin/sed
checking for grep that handles long lines and -e... /bin/grep
checking for egrep... /bin/grep -E
checking for fgrep... /bin/grep -F
checking for ld used by gcc... /usr/bin/ld
checking if the linker (/usr/bin/ld) is GNU ld... yes
checking for BSD- or MS-compatible name lister (nm)... /usr/bin/nm -B
checking the name lister (/usr/bin/nm -B) interface... BSD nm
checking whether ln -s works... yes
checking the maximum length of command line arguments... 1572864
checking whether the shell understands some XSI constructs... yes
checking whether the shell understands "+="... yes
checking for /usr/bin/ld option to reload object files... -r
checking for objdump... objdump
checking how to recognize dependent libraries... pass_all
checking for ar... ar
checking for strip... strip
checking for ranlib... ranlib
checking command to parse /usr/bin/nm -B output from gcc object... ok
checking how to run the C preprocessor... gcc -E
checking for ANSI C header files... yes
checking for sys/types.h... yes
checking for sys/stat.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for memory.h... yes
checking for strings.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for unistd.h... yes
checking for dlfcn.h... yes
checking for objdir... .libs
checking if gcc supports -fno-rtti -fno-exceptions... no
checking for gcc option to produce PIC... -fPIC -DPIC
checking if gcc PIC flag -fPIC -DPIC works... yes
checking if gcc static flag -static works... yes
checking if gcc supports -c -o file.o... yes
checking if gcc supports -c -o file.o... (cached) yes
checking whether the gcc linker (/usr/bin/ld -m elf_x86_64) supports shared 
libraries... yes
checking whether -lc should be explicitly linked in... no
checking dynamic linker characteristics... GNU/Linux ld.so
checking how to hardcode library paths into programs... immediate
checking whether stripping libraries is possible... yes
checking if libtool supports shared libraries... yes
checking whether to build shared libraries... yes
checking whether to build static libraries... yes
checking for a BSD-compatible install... /usr/bin/install -c
checking for special C compiler options needed for large files... no
checking for _FILE_OFFSET_BITS value needed for large files... no
checking for int8_t... yes
checking for int16_t... yes
checking for int32_t... yes
checking for int64_t... yes
checking for intptr_t... yes
checking for uint8_t... yes
checking for uint16_t... yes
checking for uint32_t... yes
checking for uint64_t... yes
checking for uintptr_t... yes
checking for sys/types.h... (cached) yes
checking for stdlib.h... (cached) yes
checking for stdint.h... (cached) yes
checking for inttypes.h... (cached) yes
checking malloc.h usability... yes
checking malloc.h presence... yes
checking for malloc.h... yes
checking for fdatasync... yes
checking for gmtime_r... yes
checking for isnan... yes
checking for localtime_r... yes
checking for 

Re: [sqlite] Size of the SQLite library

[Chris Smith]

"You are soo, bloated," said Java.

On Thu, May 31, 2018, 11:58 R Smith  wrote:

>
> On 2018/05/31 5:17 PM, ven...@intouchmi.com wrote:
> > I have to agree with Bob!
> >
> > We have considered SQLITE for our project.  Going over 500Kbytes puts it
> > just beyond the size of our Flash - the current Firmware.
>
> I stand corrected! It seems the embedded systems with still an extremely
> limited memory footprint size may not be as thin on the ground as I
> imagined, and I regret trying to categorize all embedded systems under
> the same ideal - apologies for that.
>
> Towards my point though, both Bob and Vance, would you be especially
> swayed if the marketing slogan had said "under a megabyte" as opposed to
> "under half a megabyte"?  I still feel that this level of embedded
> system is not common, and even where it might be common, I bet that
> slogan is not the catch phrase that got you interested in SQLite (or
> would sway you from choosing it).
>
> It's however clear my view may not be 100% representative, so perhaps
> the  KiB or 0.5 MiB route has its place.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
-- 
Cheers,
Chris
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users