Keith,
This has really helped.
SQLite Tutorial site is using: 3.12.1 2016-04-08 15:09:49
fe7d3b75fe1bde41511b323925af8ae1b910bc4d
I get the same results as you did below. So, there is some sort of error
or different code being used. It may have to do with encoding. I will
test this further tomorrow.
Many thanks,
Ken
On 01/05/2017 12:29 AM, Keith Medcalf wrote:
sqlite3s < globtest.sql
select sqlite_version(), sqlite_source_id();
3.11.0|2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f
create table x(x text);
insert into x values('ABC');
insert into x values('5BC');
insert into x values('A5C');
insert into x values('AB5');
insert into x values('1BC');
insert into x values('5B1');
insert into x values('A51');
insert into x values('1B5');
insert into x values('123');
select x from x where x glob '*[^1-9]*';
ABC
5BC
A5C
AB5
1BC
5B1
A51
1B5
select x from x where x glob '*[1-9]*';
5BC
A5C
AB5
1BC
5B1
A51
1B5
123
select x from x where not x glob '*[^1-9]*';
123
select x from x where not x glob '*[1-9]*';
ABC
The GLOB works exactly the same in all cases, including on 3.11.0.
The pattern *[1-9]* matches "any string containing at least 1 character 1 thru
9"
The pattern *[^1-9]* matches "any string containing 1 character that is not 1
thru 9 (that is, only strings composed entirely of the characters 1 thru 9 are
included, those composed entirely of characters 1 thru 9 are excluded).
I do not understand the part where you say "also 3.15.0. Worked as in #1 above in
both cases in the SQLite Tutorial, but oppositely in the SQLite3 CLI." because using
python to call the sqlite3 also produces the same results as the CLI:
import apsw
import apswrow
dbr=apsw.Connection('globtest.db')
for row in db.cursor().execute("select x from x where x glob '*[^1-9]*';"):
... print row
...
Row(x=u'ABC')
Row(x=u'5BC')
Row(x=u'A5C')
Row(x=u'AB5')
Row(x=u'1BC')
Row(x=u'5B1')
Row(x=u'A51')
Row(x=u'1B5')
This would suggest to me that either (a) the internal GLOB function was
overridden or (b) that even though you thought you changed the underlying
version of SQLite, you did not. Did you select sqlite_version() and
sqlite_source_id() to see what version of sqlite was being used? In the
Python above it is currently:
for row in cr.cursor().execute("select sqlite_version(), sqlite_source_id();"):
... print row
...
Row(sqlite_version=u'3.17.0', sqlite_source_id=u'2017-01-04 04:18:00
80ad317f89c46db0d0d252aefdc036a34a61183d')
-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
On Behalf Of Ken Wagner
Sent: Wednesday, 4 January, 2017 22:36
To: SQLite mailing list
Subject: Re: [sqlite] SQLite3 Tutorial error
Keith,
Thanks for the further explanation and the ll alias.
What is so hard to digest is that:
1. SQLiteMan - a Linux GUI does the *[^1-9]* pattern and omits rows
with digits 1-9 in them. Using 3.11.0.
2. DBBrowser - Linux GUI does the same using 3.9.2 and 3.11.0 in
different versions, one older.
3. SQLite Tutorial @ www.sqlitetutorial.net - Has GLOB lesson page.
Uses version @ https://www.sqlite.org/download.html. I downloaded
sqlite-tools-linux-x86-3160100.zip
<https://www.sqlite.org/2017/sqlite-tools-linux-x86-3160100.zip>(1.77
MiB), also 3.15.0. Worked as in #1 above in both cases in the SQLite
Tutorial, but oppositely in the SQLite3 CLI.
4. The FireFox 50 SQLite Manager add-on. This one is quite good.
Good layouts, easy to use and detailed. Works as in #1 above. Uses
SQLite 3.13.0.
Yet the SQLite3 CLI does the OPPOSITE and _includes_ any names with
digits 1-9 in them with vv 3.15.0 and 3.16.0.
Do you have any idea why? Is it really the dim sum??
This will have to be explained to my intended customers because some
will use the SQLite3 CLI. Most will use a GUI as they are not very
computer literate. I will be offering them training and also directing
them to training at a good web SQLite tutorial.
All of the above are using the 'chinook.db'. My system is Linux/Ubuntu
16.04, SQLite3 3.15.0 and 3.16.0 CLIs and the above programs. I use the
CLI in both the Terminator and Gnome-Terminal. Some minor differences
with encoding.
Ken
On 01/04/2017 10:49 PM, Keith Medcalf wrote:
Yes. The GLOB was invented on Unix. I posted an example of the Unix
filename globbing (which has not changed, to my knowledge, since the
60's), which works exactly the same as the GLOB operator in SQLite 3.9.0
through the current head of trunk. Perhaps there were minor changes, but
nothing that affects the output of the *[1-9]* or *[^1-9]* patterns when
applied to the same data used in the Linux demonstration. However, I did
not try and build every single version of SQLite between 3.9.0 to 3.17.0
to see if one of them happened to be broken. The two ends and a sampling
from the middle all worked the same.
And by the way, GLOB predates REGEX by about 15 years. REGEX borrowed
(and modified) GLOB syntax.
(in case you have never used a Linux/Unix system with an ll command
alias, the command to create it is: alias ll='ls -l')
Are you ABSOLUTELY SURE that the authors of the third-party tools have
not provided their own GLOB function that works differently, perhaps in
accordance with their Dim Sum because their little hearts did not desire
the built in one?
-----Original Message-----
From: sqlite-users [mailto:sqlite-users-
boun...@mailinglists.sqlite.org]
On Behalf Of Ken Wagner
Sent: Wednesday, 4 January, 2017 21:24
To: SQLite mailing list
Subject: Re: [sqlite] SQLite3 Tutorial error
Aha! GLOB is an implementation of the UNIX Glob function. It just
borrows the regex character notation of [^1-9].
I have 3.15 and 3.16 CLIs installed. Trying any other CLI versions at
this point won't help for the customer. They will be using a SQLite3
GUI. I will explain the difference between the CLI and the GUI
versions.
Where is this UNIX Glob notation spelled out? My system is Linux. I
expect the UNIX version will be somewhat different.
The Linux GLOB is used to find patterns in pathnames (not databases.)
Is
the Unix version the same?
Thanks,
Ken
On 01/04/2017 11:51 AM, R Smith wrote:
On 2017/01/04 7:01 PM, Jens Alfke wrote:
On Jan 4, 2017, at 5:57 AM, R Smith <rsm...@rsweb.co.za> wrote:
As I have it (and as is implemented by SQLite) the GLOB operator
implements a REGEXP that matches against a regexp pattern
No, these are NOT regular expressions in the usual sense of the word.
GLOB's syntax is incompatible with what are commonly called “regular
expressions”, and its feature set is a lot more limited. (It may
technically implement a type of regular expression in the underlying
algorithmic sense, but I think using the term is misleading.)
Quite correct, I meant REGEXP as an internal function of the
Regular-expression type, not the official "regular expression" syntax
- So a "misleading term" then in your words. Allow me to be more clear
then: GLOB in SQLite specifically matches Unix file globbing syntax
(which is very different to official RegEx). 3rd party utilities can
override both the internal REGEXP and GLOB functions with custom
versions.
https://sqlite.org/lang_expr.html#glob
The bit I don't know for sure is whether Unix file globbing will
regard 'AB5' as matching '*[^1-9]*' or not? I know in SQLite it
matches (and I believe this to be correct, but I could be mistaken and
I don't have a Unix box handy to test).
Either way, the concern is more towards consistency than specific
operation.
The SQLite scripts I sent previously will reveal any difference
between versions if they exist. Have you tried it on different
versions of the CLI?
_______________________________________________
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-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
