Re: [sqlite] How to install REGEXP support?
SELECT LOAD_EXTENSION('regexp');
assuming that the regexp dynamic library is located where it can be found.
>-Original Message-
>From: sqlite-users On
>Behalf Of Peng Yu
>Sent: Thursday, 19 September, 2019 20:20
>To: SQLite mailing list
>Subject: Re: [sqlite] How to install REGEXP support?
>
>My question is `But it is not clear how to install it for sqlite3
>installed by
>homebrew.`
>
>On 9/19/19, Warren Young wrote:
>> On Sep 18, 2019, at 8:33 AM, Peng Yu wrote:
>>>
>>> But I don't want to always specify a full path. I am asking where is
>>> the standard place to put the library file so that I don't have to
>>> always specify the whole path.
>>
>> You’re verging into “How do I program my computer?” or even “How do I
>use my
>> computer?” territory here. Put it in a directory where your OS’s
>dynamic
>> loader will find it. For macOS:
>>
>>
>>
>https://developer.apple.com/library/archive/documentation/DeveloperTools/
>Conceptual/DynamicLibraries/100-
>Articles/UsingDynamicLibraries.html#//apple_ref/doc/uid/TP40002182-SW10
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>--
>Regards,
>Peng
>___
>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] How to install REGEXP support?
My question is `But it is not clear how to install it for sqlite3 installed by homebrew.` On 9/19/19, Warren Young wrote: > On Sep 18, 2019, at 8:33 AM, Peng Yu wrote: >> >> But I don't want to always specify a full path. I am asking where is >> the standard place to put the library file so that I don't have to >> always specify the whole path. > > You’re verging into “How do I program my computer?” or even “How do I use my > computer?” territory here. Put it in a directory where your OS’s dynamic > loader will find it. For macOS: > > > https://developer.apple.com/library/archive/documentation/DeveloperTools/Conceptual/DynamicLibraries/100-Articles/UsingDynamicLibraries.html#//apple_ref/doc/uid/TP40002182-SW10 > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to install REGEXP support?
On Sep 18, 2019, at 8:33 AM, Peng Yu wrote: > > But I don't want to always specify a full path. I am asking where is > the standard place to put the library file so that I don't have to > always specify the whole path. You’re verging into “How do I program my computer?” or even “How do I use my computer?” territory here. Put it in a directory where your OS’s dynamic loader will find it. For macOS: https://developer.apple.com/library/archive/documentation/DeveloperTools/Conceptual/DynamicLibraries/100-Articles/UsingDynamicLibraries.html#//apple_ref/doc/uid/TP40002182-SW10 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work as expected
-Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Fredrik Larsen Gesendet: Donnerstag, 19. September 2019 17:29 An: SQLite mailing list Betreff: Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work as expected ... Hick; ORDER BY x DESC >is< covered by index. Btree-indexes allows traversal both ways. You can see this if you remove GROUP_BY. ... True and nothing new, but not the point. After doing GROUP BY x over the covering index, the result rows would be returned by x ASC. There is no index on the rowset returned by the GROUP BY, as the rows only exist one at a time. Therefore, the only way to get them into ORDER BY X DESC is to sort them. Gunter ___ 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] [EXTERNAL] Group-by and order-by-desc does not work as expected
Simen; ANALYZE and PRAGMA reverse_unordered_selects = YES does not affect results. Hick; ORDER BY x DESC >is< covered by index. Btree-indexes allows traversal both ways. You can see this if you remove GROUP_BY. Got an answer on StackOverflow that seems to be from somebody that knows internal details of sqlite. Depressing if this is true as this optimization seems trivial compared to other optimizations implemented in sqlite and it effectively stops my sqlite-project Fredrik On Thu, Sep 19, 2019 at 5:13 PM Hick Gunter wrote: > An ORDER BY clause will omit sorting only if the visitation order exactly > fulfills the clause. > > A GROUP BY clause is able to avoid creating a temporary table if the > visitation order exactly fulfills the clause. > > If a SELECT references only fields present in an index, that (covering) > index may be used instead of the table. > > In your case, the SELECT references fields x and y, both of which are > present in the index, so the QP uses the covering index. This also happens > to be sorted by x, allowing the GROUP BY to avoid a temporary table and > producing rows odered by x. > > The ORDER BY x in the first query is thus already fulfilled and so no > sorting is required. > > The ORDER BY x DESC in the second query is thus NOT fulfilled, so a > sorting step is required. > > Unfortunately, trying to be clever by creating an index on (x desc, y) > does not help. > > -Ursprüngliche Nachricht- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > Im Auftrag von Fredrik Larsen > Gesendet: Donnerstag, 19. September 2019 14:14 > An: sqlite-users@mailinglists.sqlite.org > Betreff: [EXTERNAL] [sqlite] Group-by and order-by-desc does not work as > expected > > I have a aggregate query that works as expected when the ordering is > ascending, but uses a TMP B-TREE when changing order to descending, see > stackoverflow link below. > > Is there something I'm missing? I would expect same performance when > ordering both directions. > > Link: > > https://stackoverflow.com/questions/58009898/sqlite-group-by-with-sort-by-desc-does-not-work-as-expected > > > Fredrik Larsen > ___ > 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] Group-by and order-by-desc does not work as expected
An ORDER BY clause will omit sorting only if the visitation order exactly fulfills the clause. A GROUP BY clause is able to avoid creating a temporary table if the visitation order exactly fulfills the clause. If a SELECT references only fields present in an index, that (covering) index may be used instead of the table. In your case, the SELECT references fields x and y, both of which are present in the index, so the QP uses the covering index. This also happens to be sorted by x, allowing the GROUP BY to avoid a temporary table and producing rows odered by x. The ORDER BY x in the first query is thus already fulfilled and so no sorting is required. The ORDER BY x DESC in the second query is thus NOT fulfilled, so a sorting step is required. Unfortunately, trying to be clever by creating an index on (x desc, y) does not help. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Fredrik Larsen Gesendet: Donnerstag, 19. September 2019 14:14 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] Group-by and order-by-desc does not work as expected I have a aggregate query that works as expected when the ordering is ascending, but uses a TMP B-TREE when changing order to descending, see stackoverflow link below. Is there something I'm missing? I would expect same performance when ordering both directions. Link: https://stackoverflow.com/questions/58009898/sqlite-group-by-with-sort-by-desc-does-not-work-as-expected Fredrik Larsen ___ 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] using lower function with utf8
Yes, I’m using v5 JcD. Thanks. From: sqlite-users on behalf of Jean-Christophe Deschamps Sent: Thursday, September 19, 2019 3:49:39 PM To: SQLite mailing list Subject: Re: [sqlite] using lower function with utf8 >I was messing about with this and tried the following in sqlite expert >professional > >select unicode(lower(char(256))); > >I was quite surprised when it responded with the correct result 257. > >Looking at the sqlite3.c code I can’t see anything that suggests >sqlite would handle lower() for non-ascii characters so I tried the >same thing in a c programme and it came back with the expected wrong >answer 256. > >Am I right in saying 256 is the expected sqlite answer? > >Can anyone suggest why it works in the former? Expert v5 (I expect that's what you use) includes Unicode support and overrides sqlite3.dll lower(). JcD ___ 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] using lower function with utf8
I was messing about with this and tried the following in sqlite expert professional select unicode(lower(char(256))); I was quite surprised when it responded with the correct result 257. Looking at the sqlite3.c code I canât see anything that suggests sqlite would handle lower() for non-ascii characters so I tried the same thing in a c programme and it came back with the expected wrong answer 256. Am I right in saying 256 is the expected sqlite answer? Can anyone suggest why it works in the former? Expert v5 (I expect that's what you use) includes Unicode support and overrides sqlite3.dll lower(). JcD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] using lower function with utf8
I was messing about with this and tried the following in sqlite expert professional select unicode(lower(char(256))); I was quite surprised when it responded with the correct result 257. Looking at the sqlite3.c code I can’t see anything that suggests sqlite would handle lower() for non-ascii characters so I tried the same thing in a c programme and it came back with the expected wrong answer 256. Am I right in saying 256 is the expected sqlite answer? Can anyone suggest why it works in the former? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Group-by and order-by-desc does not work as expected
On 19 Sep 2019, at 1:14pm, Fredrik Larsen wrote: > I have a aggregate query that works as expected when the ordering is > ascending, but uses a TMP B-TREE when changing order to descending, see > stackoverflow link below. For experimental purposes, you might take a backup copy of your database and try executing ANALYZE before the query. Also try PRAGMA reverse_unordered_selects = YES before the query. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Undo sqlite3_snapshot_open?
Woohoo, thanks Dan! I'm going to try this very soon :-) Gwendal On Thu, Sep 19, 2019 at 1:18 PM Dan Kennedy wrote: > > On 19/9/62 18:13, Gwendal Roué wrote: > > Hello, > > > > I am looking at the snapshot experimental APIs, and it looks like once a > > connection has been sent to an "historical snapshot" with > > sqlite3_snapshot_open (https://www.sqlite.org/c3ref/snapshot_open.html), > > the connection can never be restored back to regular operations. > > > > Is it correct? > > I don't think so. > > If you end the transaction opened with sqlite3_snapshot_open() (by > executing a "COMMIT" or "ROLLBACK" and calling sqlite3_reset() or > sqlite3_finalize() on all active SELECT statements) then open a new > transaction, the new transaction accesses the latest database snapshot - > just as if you had never used sqlite3_snapshot_open() with the > connection at all. > > Dan. > > > > > > > Thanks is advance, > > Gwendal Roué > > ___ > > 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
[sqlite] Group-by and order-by-desc does not work as expected
I have a aggregate query that works as expected when the ordering is ascending, but uses a TMP B-TREE when changing order to descending, see stackoverflow link below. Is there something I'm missing? I would expect same performance when ordering both directions. Link: https://stackoverflow.com/questions/58009898/sqlite-group-by-with-sort-by-desc-does-not-work-as-expected Fredrik Larsen ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Undo sqlite3_snapshot_open?
On Thu, Sep 19, 2019 at 1:13 PM Gwendal Roué wrote: > I am looking at the snapshot experimental APIs > How long do experimental APIs remain experimental? Snapshot is over 3.75 years old now. Will it ever graduate to a fully supported API? As far as I understood the doc, a snapshot remains valid only as long as the WAL file is not checkpointed. Which imply checkpoint is allowed to invalidate any snapshots, while I can see where the reverse would be preferred, i.e. active snapshots prevent the full checkpointing of the WAL file. Is there any way to know about snapshots, so the connection in charge of checkpointing does not clobber them? Thanks, --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Undo sqlite3_snapshot_open?
On 19/9/62 18:13, Gwendal Roué wrote: Hello, I am looking at the snapshot experimental APIs, and it looks like once a connection has been sent to an "historical snapshot" with sqlite3_snapshot_open (https://www.sqlite.org/c3ref/snapshot_open.html), the connection can never be restored back to regular operations. Is it correct? I don't think so. If you end the transaction opened with sqlite3_snapshot_open() (by executing a "COMMIT" or "ROLLBACK" and calling sqlite3_reset() or sqlite3_finalize() on all active SELECT statements) then open a new transaction, the new transaction accesses the latest database snapshot - just as if you had never used sqlite3_snapshot_open() with the connection at all. Dan. Thanks is advance, Gwendal Roué ___ 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] Undo sqlite3_snapshot_open?
Hello, I am looking at the snapshot experimental APIs, and it looks like once a connection has been sent to an "historical snapshot" with sqlite3_snapshot_open (https://www.sqlite.org/c3ref/snapshot_open.html), the connection can never be restored back to regular operations. Is it correct? Thanks is advance, Gwendal Roué ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The best way to check if a file is a sqlite3 DB file?
On Thu, Sep 19, 2019 at 10:20 AM Rowan Worth wrote: > On Thu, 19 Sep 2019 at 16:03, Dominique Devienne > > On Wed, Sep 18, 2019 at 6:43 PM Clemens Ladisch > > > Peng Yu wrote: > > > > Is there a better way to just return an exit status of 0 for > > > > a sqlite3 DB file and 1 otherwise? > > > > dd bs=16 count=1 < some.db > sqlite3-signature > > > cmp --bytes=16 sqlite3-signature /tmp/tmp.erZ5aS6PUX.sqa > /dev/null > > > [ $? = 0 ] && echo SQLite DB > > > > > > I'm actually surprised sqlite3[.exe] itself doesn't have a more to do > that. > > As usual, sqlite doesn't touch the DB file until it is asked to. Try > "sqlite3 FILENAME 'pragma schema_version'" on some random file and you'll > get "Error: file is encrypted or is not a database". But note that trying > the same on a non-existent file will succeed, and additionally create an > empty file. > Thanks. Good tip. Need double not single quotes on Windows. And error code of 26 if a bit weird, but this works well otherwise: D:\>sqlite3 TypedEntity.h 'pragma schema_version' Error: unrecognized token: "'pragma" D:\>sqlite3 TypedEntity.h "pragma schema_version" Error: file is not a database D:\>echo %ERRORLEVEL% 26 D:\>sqlite3 SOME.db "pragma schema_version" 58 D:\>echo %ERRORLEVEL% 0 > > You'd think sqlite3[.exe] is the best suited to figure out if a file is a > > valie SQLite database or not, > > It still is: sqlite3 FILENAME 'pragma integrity_check' > > Parsing the header doesn't tell you whether the DB is valid, but if that's > all you want to do I suggest the ubiquitous file(1) command which reports > "SQLite 3.x database" for a [non-empty] sqlite db file. > I'm well aware of that. We were discussing an alternative to the *nix file command, and integrity_check goes way beyond (and is way slower) than checkinga file type. You might as well through FK checks with foreign_key_check if you are going there :). pragma quick_check would be more appropriate, if one wants to go the extra mile, w/o slowing things down too much. But again, thanks for this tip. Good one. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The best way to check if a file is a sqlite3 DB file?
On Thu, 19 Sep 2019 at 16:03, Dominique Devienne wrote: > On Wed, Sep 18, 2019 at 6:43 PM Clemens Ladisch > wrote: > > > Peng Yu wrote: > > > Is there a better way to just return an exit status of 0 for > > > a sqlite3 DB file and 1 otherwise? > > > > Extract the magic header string from a known DB file: > > > > dd bs=16 count=1 < some.db > sqlite3-signature > > > > Then you can compare it against the beginning of the file: > > > > cmp --bytes=16 sqlite3-signature /tmp/tmp.erZ5aS6PUX.sqa > /dev/null > > [ $? = 0 ] && echo SQLite DB > > > I'm actually surprised sqlite3[.exe] itself doesn't have a more to do that. > I tried using it to open a non-DB file, and it opens in interactive mode, > with > no error or warning, wether I use -bail or not. I was expecting a hard > error. > As usual, sqlite doesn't touch the DB file until it is asked to. Try "sqlite3 FILENAME 'pragma schema_version'" on some random file and you'll get "Error: file is encrypted or is not a database". But note that trying the same on a non-existent file will succeed, and additionally create an empty file. Technically from sqlite's perspective a non-existent or empty file is a perfectly well-formed database, just one which happens to contain no data. > You'd think sqlite3[.exe] is the best suited to figure out if a file is a > valie SQLite database or not, > It still is: sqlite3 FILENAME 'pragma integrity_check' Parsing the header doesn't tell you whether the DB is valid, but if that's all you want to do I suggest the ubiquitous file(1) command which reports "SQLite 3.x database" for a [non-empty] sqlite db file. -Rowan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The best way to check if a file is a sqlite3 DB file?
On Wed, Sep 18, 2019 at 6:43 PM Clemens Ladisch wrote: > Peng Yu wrote: > > Is there a better way to just return an exit status of 0 for > > a sqlite3 DB file and 1 otherwise? > > Extract the magic header string from a known DB file: > > dd bs=16 count=1 < some.db > sqlite3-signature > > Then you can compare it against the beginning of the file: > > cmp --bytes=16 sqlite3-signature /tmp/tmp.erZ5aS6PUX.sqa > /dev/null > [ $? = 0 ] && echo SQLite DB I'm actually surprised sqlite3[.exe] itself doesn't have a more to do that. I tried using it to open a non-DB file, and it opens in interactive mode, with no error or warning, wether I use -bail or not. I was expecting a hard error. You'd think sqlite3[.exe] is the best suited to figure out if a file is a valie SQLite database or not, and exit with a non-zero status if it's not (possibly using a given CLI arg for that behavior). That would be a useful addition, if it's not already possible in a way I can't find. Parsing the 100 bytes header would do and be super fast. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
