Page 1 of 1
SQLite Library issue
Posted: Mon Feb 11, 2013 11:02 pm
by purebuilt
Hello PB Group,
I discovered that the libraries that PB uses for SQLite have an error in them with ORDER BY statements. This is true of any program using the older libraries (discovered it in the SQLite Manger plug-in for FireFox). I don't know how to update the libraries, is there a way to do that or a planned release of them? We find it a very useful feature of PB, but we need the most recent libraries.
Thanks
Re: SQLite Library issue
Posted: Mon Feb 11, 2013 11:52 pm
by skywalk
PB v5.0 SQLite Version = 3.7.9.
What version of SQLite created the 'Order By' error and which version fixed it?
SQLite releases...
Re: SQLite Library issue
Posted: Tue Feb 12, 2013 4:27 pm
by purebuilt
2012-06-11 (3.7.13) is working. But 3.7.9 or earlier does not.
Thanks
Re: SQLite Library issue
Posted: Tue Feb 12, 2013 7:07 pm
by deeproot
@purebuilt - if possible could you describe the error you are getting with ORDER BY ?
I'm using ORDER BY on almost all queries and some are pretty complex with up to 9 joined tables. But I have seen no issues when testing - PB 5.00 & 5.10 all betas, SQLite 3.7.9. I also check tables using the FireFox plug-in.
The SQLite release history (skywalk's link) mentions an ORDER BY bug introduced with 3.7.15 and fixed 3.7.15.2 but I can't see any related report earlier.
Maybe I've just been lucky with type of query statements used?
Re: SQLite Library issue
Posted: Tue Feb 12, 2013 7:15 pm
by purebuilt
Yes, you have been lucky. It took us a while to find it, but when we create a view with an ORDER BY statement:
Code: Select all
SELECT DISTINCT jobid,
datetime(start_tmx,'unixepoch') start,
datetime(end_tmx,'unixepoch') end,
(strftime('%s','now','localtime') - start_tmx) / 3600 age,
CASE WHEN end_tmx > 0 THEN
end_tmx - start_tmx
ELSE
strftime('%s','now','localtime') - start_tmx
END as duration,
upper(status) status
FROM jcojobdata
GROUP BY jobid
ORDER BY jobid ASC, max(start_tmx) DESC;
Then do a simple query: SELECT * FROM jobsmonitor; <-- the name of the view.
Depending on the version of SQLite used, we get different results for the records (mostly first and last). This does not happen if we do the query with the latest versions, only in PB and FireFox where they use the earlier versions (before 3.7.13).
Thanks
Re: SQLite Library issue
Posted: Tue Feb 12, 2013 7:55 pm
by Fred
I have updated it for the next beta.
Re: SQLite Library issue
Posted: Tue Feb 12, 2013 8:01 pm
by skywalk
Thanks Fred
Re: SQLite Library issue
Posted: Tue Feb 12, 2013 8:29 pm
by deeproot
Thanks for the info purebuilt - interesting. None of my queries use quite that kind of combination. SQLite has been really solid since I converted to it - hope it stays that way!
Re: SQLite Library issue
Posted: Tue Feb 12, 2013 9:08 pm
by Kiffi
Fred wrote:I have updated it for the next beta.
good news! Thanks a lot!
Greetings ... Kiffi
Re: SQLite Library issue
Posted: Tue Feb 12, 2013 9:16 pm
by purebuilt
Thanks Fred! You're the best!
Re: SQLite Library issue
Posted: Tue Feb 12, 2013 10:44 pm
by Fred
SQLite is used by a lot of programs, it's a very solid database manager, such bugs are probably very rare.