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" <sqlite-users@sqlite.org>
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
