Re: [sqlite] Date time input
On 09/10/2019 10:25, Kevin Benson wrote: On Tue, Oct 8, 2019 at 2:40 PM James K. Lowden wrote: On Tue, 8 Oct 2019 09:06:24 -0700 Jens Alfke wrote: I think the idea of a semi-official ?SQLite++? has been floated here before OK, but it needs a better name. -- SQLiteXTD SQLArdArse. Cheers, GaryB-) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date time input
On Tue, Oct 8, 2019 at 2:40 PM James K. Lowden wrote: > On Tue, 8 Oct 2019 09:06:24 -0700 > Jens Alfke wrote: > > > I think the idea of a semi-official ?SQLite++? has been floated here > > before > > OK, but it needs a better name. -- > SQLiteXTD -- -- --Ö¿Ö-- K e V i N ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date time input
I like SQLoaded! Doug > -Original Message- > From: sqlite-users > On Behalf Of Jose Isaias Cabrera > Sent: Tuesday, October 08, 2019 12:15 PM > To: sqlite-users@mailinglists.sqlite.org > Subject: Re: [sqlite] Date time input > > > James K. Lowden, on Tuesday, October 8, 2019 02:39 PM, wrote... > > > > On Tue, 8 Oct 2019 09:06:24 -0700 > > Jens Alfke, on > > > > > I think the idea of a semi-official ?SQLite++? has been > floated here > > > before > > > > OK, but it needs a better name. What better place than here to > debate > > that? ;-) > > SQLiteNoMore > > 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
[sqlite] SELECT uses index with SUBSTR but UPDATE doesn't
I have this query: UPDATE nodes SET parent = ? WHERE SUBSTR(name, 0, ?) = ? EXPLAIN QUERY PLAN tells me that it is going to do a table scan. At the same time, the query plan for this: SELECT * FROM nodes WHERE SUBSTR(name, 0, ?) = ? tells me that it can and will use the (primary key) index on the name column. With that info, I thought that this query would be faster: UPDATE nodes SET parent = ? WHERE name IN (SELECT name FROM nodes WHERE SUBSTR(name, 0, ?) = ?) Alas, it's not. I don't know why. UPDATE will use the index if I use the LIKE operator. However, it won't use the index if I attempt LIKE (? || '%'). Whatever handles the string concatenation breaks the use of the index. I don't want to have to sanitize my own data. I have very arbitrary, user-entered, malicious data going in. It's also not clear to me what the sanitizer does for the LIKE operator. What does it do to existing percent signs in the data? I don't want to use those as wildcards. Hence, I much prefer the SUBSTR approach; it seems much safer all around. I run v3.29.0. I hope this can prompt somebody to make the SUBSTR operator work with the indexes on an UPDATE statement. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date time input
James K. Lowden, on Tuesday, October 8, 2019 02:39 PM, wrote... > > On Tue, 8 Oct 2019 09:06:24 -0700 > Jens Alfke, on > > > I think the idea of a semi-official ?SQLite++? has been floated here > > before > > OK, but it needs a better name. What better place than here to debate > that? ;-) SQLiteNoMore josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date time input
On 8 Oct 2019, at 7:39pm, James K. Lowden wrote: > Simon Slavin wrote: > >> Converting data to and from a convenient storage format is not the >> job of a DBMS. > > While I have no quarrel with your specific point about date strings, > this particular statement is too broad. Arguably, data conversion is > an inherent, central part of what the DBMS does. Unless, that is, > you're adept at interpreting IEEE floating point as 1s and 0s. You're right. What I meant was something more like "Converting data to and from your presentation format …". On the subject of a name for the heavy version of SQLite, I'd suggest SQLThicc, but the 'thicc' meme will have gone away in a few years. Actually, since it won't be supported that long, it's not such a bad name. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date time input
On Mon, 7 Oct 2019 18:17:14 +0100 Simon Slavin wrote: > Converting data to and from a convenient storage format is not the > job of a DBMS. While I have no quarrel with your specific point about date strings, this particular statement is too broad. Arguably, data conversion is an inherent, central part of what the DBMS does. Unless, that is, you're adept at interpreting IEEE floating point as 1s and 0s. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date time input
On Tue, 8 Oct 2019 09:06:24 -0700 Jens Alfke wrote: > I think the idea of a semi-official ?SQLite++? has been floated here > before OK, but it needs a better name. What better place than here to debate that? ;-) What the opposite of "Lite"? I don't know. It's like asking for the opposite of "organic" milk. What we have is "milk" and "organic milk". In a just and rational world, we'd have "milk" and "industrial milk". Milk is, after all, organic to start with. Moving on... We can't us be fat-shaming and all, calling it "SQLfat" or "SQLobese" or somesuch. "SQLoaded" seems intoxicated. "SQLplus" isn't accurate; it's not more than SQL. IMO something fanciful is called for. I nominate "SQLippo". After the hippopotamus, not lipposuction, but it's mnemonic either way. Mud, mud, glorious mud! --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LSM Extension Performance (from sqlite3 tree)
Le jeu. 16 mai 2019 à 13:47, Amirouche Boubekki a écrit : > > Hello, > > > I am considering using lsm extension for a project. I a did a few benchmark > with the following configuration: > > LSM_CONFIG_AUTOFLUSH 1048576 > LSM_CONFIG_BLOCK_SIZE 65536 > LSM_CONFIG_AUTOWORK 1 > LSM_CONFIG_MMAP 0 > LSM_CONFIG_MULTIPLE_PROCESS 0 > > I am trying to load 4.5GB of data. The database errors with BUSY error at > some point. > > Also, the memory consumption grows unbound even if I diseabled MMAP. > > Here is some stats: > > $ ls -larh wt/foob.ar.lsm.sqlite* > > -rw-r--r-- 1 amirouche amirouche 618M May 16 13:39 wt/foob.ar.lsm.sqlite-log > -rw-r--r-- 1 amirouche amirouche 468M May 16 13:39 wt/foob.ar.lsm.sqlite > > And I attached a memory plot. > > opening and closing the database at each transaction help, but still the > memory grows. > > What I am doing wrong? It is probably because I was inserting bytes taken from /dev/urandom. The code is at https://git.sr.ht/~amz3/chez-sqlite-lsm/tree/master/bench.scm -- Amirouche ~ amz3 ~ https://hyper.dev ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date time input
Jens Alfke, on Tuesday, October 8, 2019 12:06 PM, wrote... > > > > On Oct 8, 2019, at 5:34 AM, Jose Isaias Cabrera, on > > > > No, that is not what I was trying to say or ask. Not even close. What I was > trying to say, and most of you missed it was, that if I give date a date > format, > and I also provide the format of how that date is to be understood, ie > > Sorry for misunderstanding. But I think this goes against SQLite’s design > goal of > simplicity. Date-time formatting is complicated, so this would add measurably > to > the library’s footprint*, which would be a problem for using it in embedded > systems, which would require yet another compile-time configuration flag to > enable/disable it, which would complicate testing... etc. I agree. > I think the idea of a semi-official ”SQLite++” has been floated here before: a > distro with lots of extensions and a more powerful CLI. I like that idea. That would be something! josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date time input
> On Oct 8, 2019, at 5:34 AM, Jose Isaias Cabrera wrote: > > No, that is not what I was trying to say or ask. Not even close. What I was > trying to say, and most of you missed it was, that if I give date a date > format, and I also provide the format of how that date is to be understood, > ie Sorry for misunderstanding. But I think this goes against SQLite’s design goal of simplicity. Date-time formatting is complicated, so this would add measurably to the library’s footprint*, which would be a problem for using it in embedded systems, which would require yet another compile-time configuration flag to enable/disable it, which would complicate testing... etc. I think the idea of a semi-official ”SQLite++” has been floated here before: a distro with lots of extensions and a more powerful CLI. I like that idea. —Jens * Yes, strptime/strftime are in the standard library. But in an embedded system that library is statically linked into your binary (there is no OS), so if those functions are not dead-stripped, your footprint goes up. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: Date time input
Shawn Wagner, on Tuesday, October 8, 2019 09:40 AM, wrote...
>
> So, I have a bunch of sqlite extension modules that I really should polish
> up for an official release Real Soon Now...
>
> I just added a basic interface to the POSIX strptime() function to the
> string functions library:
>
> sqlite> .load ./libstring_funcs
> sqlite> select date(strptime('%m/%d/%Y', '10/08/2019'), 'unixepoch') AS
> today;
> today
> --
> 2019-10-08
>
> If anyone thinks they'd find it handy, the project lives at
> https://github.com/shawnw/useful_sqlite_extensions/
Wow, these are pretty cool. Thanks.
josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: Date time input
Hick Gunter, on Tuesday, October 8, 2019 08:46 AM, wrote... > > What it boils down to is asking the data storage layer to perform a > presentation > layer task. Thanks, Hick. > > If you insist on solving the problem inside an SQL statement, you can always > write your own extension function to "easily" perform the necessary > conversion. I am not insisting. I am just saying, "it would be nice..." :-) Yes, I had to write a small SQLite piece of code, like Donald Griggs wrote to get this to work. Thanks. josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: Date time input
So, I have a bunch of sqlite extension modules that I really should polish
up for an official release Real Soon Now...
I just added a basic interface to the POSIX strptime() function to the
string functions library:
sqlite> .load ./libstring_funcs
sqlite> select date(strptime('%m/%d/%Y', '10/08/2019'), 'unixepoch') AS
today;
today
--
2019-10-08
If anyone thinks they'd find it handy, the project lives at
https://github.com/shawnw/useful_sqlite_extensions/
On Tue, Oct 8, 2019 at 5:46 AM Hick Gunter wrote:
> What it boils down to is asking the data storage layer to perform a
> presentation layer task.
>
> If you insist on solving the problem inside an SQL statement, you can
> always write your own extension function to "easily" perform the necessary
> conversion.
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Jose Isaias Cabrera
> Gesendet: Dienstag, 08. Oktober 2019 14:32
> An: SQLite mailing list
> Betreff: [EXTERNAL] Re: [sqlite] Date time input
>
>
> Jens Alfke, on Monday, October 7, 2019 09:18 PM, wrote...
> [clip]
> > I swear, half the questions on this list build down to "Why
> > doesn't SQLite act like MS Access?" If you need all the bells and
> > whistles of formatting input and output, then use a fancy DBMS
> > application. SQLite is for embedded use _inside_
> > applications.
>
> No, that is not what I was trying to say or ask. Not even close. What I
> was trying to say, and most of you missed it was, that if I give date a
> date format, and I also provide the format of how that date is to be
> understood, ie.
>
> date('5/22/2019','m/d/')
>
> where the date is the first entry, '5/22/2019', and the format is the
> second entry, 'm/d/', that SQLite could take that set of data and
> easily convert and return the ISO date I want. Yes, I know I can write
> that outside the code, or inside in SQL, but "it would be nice to have
> this." Thanks for all the responses.
>
> josé
> ___
> 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] Re: Date time input
What it boils down to is asking the data storage layer to perform a
presentation layer task.
If you insist on solving the problem inside an SQL statement, you can always
write your own extension function to "easily" perform the necessary conversion.
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Jose Isaias Cabrera
Gesendet: Dienstag, 08. Oktober 2019 14:32
An: SQLite mailing list
Betreff: [EXTERNAL] Re: [sqlite] Date time input
Jens Alfke, on Monday, October 7, 2019 09:18 PM, wrote...
[clip]
> I swear, half the questions on this list build down to "Why
> doesn't SQLite act like MS Access?" If you need all the bells and
> whistles of formatting input and output, then use a fancy DBMS
> application. SQLite is for embedded use _inside_
> applications.
No, that is not what I was trying to say or ask. Not even close. What I was
trying to say, and most of you missed it was, that if I give date a date
format, and I also provide the format of how that date is to be understood, ie.
date('5/22/2019','m/d/')
where the date is the first entry, '5/22/2019', and the format is the second
entry, 'm/d/', that SQLite could take that set of data and easily convert
and return the ISO date I want. Yes, I know I can write that outside the code,
or inside in SQL, but "it would be nice to have this." Thanks for all the
responses.
josé
___
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
Re: [sqlite] Date time input
Jens Alfke, on Monday, October 7, 2019 09:18 PM, wrote...
[clip]
> I swear, half the questions on this list build down to "Why doesn't
> SQLite act like MS Access?" If you need all the bells and whistles of
> formatting
> input and output, then use a fancy DBMS application. SQLite is for embedded
> use
> _inside_ applications.
No, that is not what I was trying to say or ask. Not even close. What I was
trying to say, and most of you missed it was, that if I give date a date
format, and I also provide the format of how that date is to be understood, ie.
date('5/22/2019','m/d/')
where the date is the first entry, '5/22/2019', and the format is the second
entry, 'm/d/', that SQLite could take that set of data and easily convert
and return the ISO date I want. Yes, I know I can write that outside the code,
or inside in SQL, but "it would be nice to have this." Thanks for all the
responses.
josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
