Zugg Software :: View topic - Feature request: Relevancy to searches in CMUD Documentation

Wizard Joined: 14 Aug 2004 Posts: 1269

Ouch, you appear to have found a bug in SQLite 2.8.17! I can confirm some strange behaviour in this query: I can't tell what field type each column in my select is, but with my query, all the columns get returned really wide. If I take out the outer select, the columns fit the data (instead of the screen). So I think I am replicating the string problem. Given this, I can't find a way of getting a UNION inside a SUBSELECT to return anything other than strings. If you have a plain select inside another one, it's OK. It appears to be the combination of UNION in a SUBSELECT that's bad. (As you say, DISTINCT does not affect this.)

I checked the version history of SQLite and there is no mention of them fixing this bug, so I became a bit concerned that it may still be in the current version. So I migrated my cmudhelp.db to SQLite 3.5.1 (the latest version) with the "Migrate to SQLite3" button in SQLite Administrator and now the columns have the normal widths. So it does look like this bug has been fixed somewhere along the line. I looked through trac to see if I could find it, but nothing definite: possibly this one: http://www.sqlite.org/cvstrac/tktview?tn=1721

Posted: Fri Nov 30, 2007 7:17 pm

Btw, just wanted to bump this to mention that when I was playing with Delphi 2007 this week, I installed the latest version of ZeosLib and verified that it works great with SQLIte 3. So when I update CMUD to use Delphi 2007 in January, I should be able to migrate everything to SQLite 3 and then implement the improved Help search order (and remove the duplicates).

Posted: Wed Jul 09, 2008 10:45 pm

Bumping this old post again because the next version of CMUD is using SQLite 3 for the Help database (and eventually all other databases). The SQL statement that Seb posted is working great in the new version and properly removes the duplicates from the search results. Woot!

Wizard Joined: 14 Aug 2004 Posts: 1269

Cool! The upgraded version adds a good number of nice features too, IIRC. Smile

Posted: Thu Jul 17, 2008 6:19 pm

When you get a chance, can the stand alone help file viewer be updated to work with new help db?

Wizard Joined: 14 Aug 2004 Posts: 1269

BTW, this might help things? Or, are those crash issues resolved now?

Posted: Thu Jul 17, 2008 8:26 pm

CMUD doesn't ever perform concurrent reading/writing with the SQLite engine. The internal database is something called kbmMemTable. When you run CMUD, it uses SQLite to load your package database into the internal memory database. All read/write changes are done to the internal database. When you exit CMUD (or on a one-minute background timer), changes to the internal memory database are written to the external SQLite database file.

This was done for speed reasons. Even as fast as SQLite is, it was *way* too slow to perform "live" database edits of the actual SQLite file. So that's why I split it into multiple database layers like this. The internal database already handles threading and concurrency, so that hasn't been a problem.

There haven't been any thread-related crash issues for many versions now. All of the thread problems were problems in my own code that wasn't thread-safe and not anything in the database engine itself.

Anyway, yes I love the new commands for ALTER TABLE, etc. This is one of the reasons why I didn't rewrite the database module in CMUD yet. I was waiting to use SQLite 3 so that I could perform on-the-fly database structure changes like that (which is something the current database module requires, even though it's not a real database).

So I'm really looking forward to having this new power available.

Tech: Yes, I'll update the standalone help viewer soon.

Wizard Joined: 14 Aug 2004 Posts: 1269

Ah, OK, it's all coming back to me now! Wink

How about if users are performing custom queries on e.g. the map database (after the mapper is converted to using SQLite3) - I suppose if they use a CMUD function to do the ad-hoc SQL query, you can serialize it, and it might only be a problem if they were accessing the database directly via COM or something?

Posted: Thu Jul 17, 2008 8:42 pm

The mapper is different. The mapper *is* a live SQLite database (well, the new mapper is...the current mapper is a live ADO/MDAC database). While I haven't gotten far enough with the conversion to test the speed yet, I'm expecting to keep just the live SQLite database connection for the mapper and *not* have an additional in-memory database.

The main reason for this is that the internal kbmMemTable database doesn't support SQL...it's just a Delphi DataSet component for very fast in-memory databases. I use SQL to load the internal database and to save the changes back to the SQlite files.

But the mapper *needs* SQL to perform it's internal queries already. So yes, the improvements to SQLite 3 will be important and good for people who are doing custom queries of the mapper (which *will* be possible).

But I know more when I get further along in the mapper conversion.