No problem. Your approach is a bit non-standard, but I'm glad you got it
working. It would be interesting to try what the others on the list are
suggesting (splitting your data into multiple columns, so that the db can
index them rather than forcing a full string search for every query) to see
if you got a speed up, but if "non-standard" is fast enough for, I guess
it's fast enough. They're only tring to help. ;)
Regards,
Martin
----- Original Message -----
From: "Tito Ciuro" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Tuesday, March 28, 2006 4:32 PM
Subject: [sqlite] [SOLVED] Re: [sqlite] LIKE and GLOB bug with numbers?
Hi Martin,
I haven't been able to pick up pace my email until this morning. Sorry
about that.
As it turns out, the error was on my side (surprise). The code seemed to
be working fine: early tests showed data being churned as expected, so I
started cleaning it up and factoring up the code. To make the story
short, I accidentally deleted the line that was incrementing the index
keeping in sync with the array enumerator. Result: I was always storing
element zero in the database. No wonder I had 4000+ entries looking
exactly the same! The first version of the code was working fine, so I
assumed that LIKE and GLOB were there culprits after I started noticing
weird results.
Oh!... just a quick message for the row-scan non-believers out there:
SQLite flies, even under this scenario. I'm getting wonderful performance
numbers by keeping everything within SQLite-land. The code is simple and
I let SQLite do all the magic. What else can I ask for? :-)
Thanks everybody for your help and comments.
Regards,
-- Tito
On 27/03/2006, at 3:40, Martin Jenkins wrote:
Tito,
I knocked up a quick test with python and apsw and it worked as
intended. My data isn't exactly the same as yours in that I don't have
the variety in the keys, but you're not having problems with those. My
test database contains your data with/without embedded carriage
returns - as expected, it makes no difference.
In the following, zip(..) is a quick hack to get all the results from
the query. The spurious '[', ']' and other brackets surrounding the
results are a result of the way that apsw returns data (as lists of
python tuples).
Apologies for the extreme width of the following lines. :(
zip(csr.execute("select * from t"))
[
(('file000000005809', '(0,NSFileTypeRegular,0,22537,0,staff,
234881026,294022,2004-12-16 10:11:00 -0800,tciuro,384,2006-03-26
08:01:55 -0800,502,20)'),),
(('file01010000581a', '(1,NSFileTypeRegular,1,22554,0,staff,
234881026,294022,2004-12-16 10:11:03 -0800,tciuro,384,2006-03-26
08:04:55 -0800,502,20)'),),
(('file02020000582b', '(2,NSFileTypeRegular,2,22571,0,staff,
234881026,294022,2004-12-16 10:11:06 -0800,tciuro,384,2006-03-26
08:07:55 -0800,502,20)'),),
...
(('file00000000595d', '(\n 0,\n NSFileTypeRegular,\n 0,\n
22877,\n 0,\n staff,\n 234881026,\n 294022,\n 2004-12-16
10:11:00 -0800,\n tciuro,\n 384,\n 2006-03-26 08:01:55 -0800,\n
502,\n 20\n)'),),
(('file01010000596e', '(\n 1,\n NSFileTypeRegular,\n 1,\n
22894,\n 0,\n staff,\n 234881026,\n 294022,\n 2004-12-16
10:11:03 -0800,\n tciuro,\n 384,\n 2006-03-26 08:04:55 -0800,\n
502,\n 20\n)'),), (('file02020000597f',
'(\n 2,\n NSFileTypeRegular,\n 2,\n 22911,\n 0,\n
staff,\n 234881026,\n 294022,\n 2004-12-16 10:11:06 -0800, \n
tciuro,\n 384,\n 2006-03-26 08:07:55 -0800,\n 502,\n 20\n)'),),
...
]
zip(csr.execute("SELECT * FROM t WHERE CMValues GLOB '*2004-12-16
10:11:45 -0800*'"))
[
(('file151500005908', '(15,NSFileTypeRegular,15,22792,0,staff,
234881026,294022,2004-12-16 10:11:45 -0800,tciuro,384,2006-03-26
08:46:55 -0800,502,20)'),),
(('file151500005a5c', '(\n 15,\n NSFileTypeRegular,\n 15,\n
23132,\n 0,\n staff,\n 234881026,\n 294022,\n 2004-12-16
10:11:45 -0800,\n tciuro,\n 384,\n 2006-03-26 08:46:55 -0800,\n
502,\n 20\n)'),)
]
zip(csr.execute("SELECT * FROM t WHERE CMValues LIKE '%2004-12-16
10:11:45 -0800%'"))
[
(('file151500005908', '(15,NSFileTypeRegular,15,22792,0,staff,
234881026,294022,2004-12-16 10:11:45 -0800,tciuro,384,2006-03-26
08:46:55 -0800,502,20)'),),
(('file151500005a5c', '(\n 15,\n NSFileTypeRegular,\n 15,\n
23132,\n 0,\n staff,\n 234881026,\n 294022,\n 2004-12-16
10:11:45 -0800,\n tciuro,\n 384,\n 2006-03-26 08:46:55 -0800,\n
502,\n 20\n)'),)
]
Could you try reducing your search strings and see if there's a point at
which they start working?
HTH,
Martin Jenkins
XQP Ltd
Ascot, UK
----- Original Message ----- From: "Tito Ciuro" <[EMAIL PROTECTED]>
To: "Forum SQLite" <[email protected]>
Sent: Sunday, March 26, 2006 6:50 PM
Subject: [sqlite] LIKE and GLOB bug with numbers?
Hello,
I've populated a datafile with 40.176 records which contain file
attributes and file paths. I have two columns, CMKey and CMValues. The
column CMKey contains the path to the file and the column CMValues
contains the attribute values. For example:
CMKey: Application Support/AbiSuite/AbiWord.Profile
CMValues:
(
0,
NSFileTypeRegular,
1,
21508,
0,
staff,
234881026,
294022,
2004-12-16 10:11:35 -0800,
tciuro,
384,
2006-03-26 08:35:55 -0800,
502,
20
)
Both columns are of type TEXT.
This is what I've found:
1) SELECT * FROM FinderFiles WHERE CMKey GLOB '*AbiWord.Profile*'
returns 1 match. This is correct.
2) SELECT * FROM FinderFiles WHERE CMKey LIKE '%ABIWORD.Profile%'
returns 1 match. This is correct.
3) SELECT * FROM FinderFiles WHERE CMValues GLOB '*2004-12-16
10:11:35 -0800*' returns 40.176 matches. This is not correct. There
is no way I created these 40.176 file at the *very same* time. Just to
be sure, I looked at one random file (of the 40.176) and I've obtained
the following creation date attribute:
NSFileCreationDate = 2004-02-21 06:12:43 -0800;
The same problem occurs if I perform the query:
SELECT * FROM FinderFiles WHERE CMValues LIKE '%2004-12-16
0:11:35 -0800%'
This problem seems to occur when trying to match something with
numbers:
- If I look for NSFilePosixPermissions 448 (which I know exists) I get
zero matches
- If I look for strings, such as in step #1 or #2, it works fine.
Something is wrong, I just can't figure out why...
Any ideas? Is this a bug?
Thanks,
-- Tito
