by Unlicensed Unknown user
When last heard from, your intrepid author (c'est moi) concluded that SQLite was not a lightweight replacement for SQL. I reached this conclusion because I had tried The Dummy Table Technique (see Getting Useful Information out of SQL, Part 2, at Clarion Mag, for a complete description of this technique), in fact a number of variations on it, to get totals from a SQLite file:
...
Three queries took 0 ticks on a 400 record file. The previous totaling checked RECORD(ARTICLES), LOOPed through ARTICLES to count articles not yet published then subtracted to get a “published count.” The “TPS code” TPS code took 3 ticks. Not a significant difference but what if there were 40000 records?
...
The problem, in both scenarios, seems to be that SQLite expects the DUMMY table to actually exist. Physically. Contrary to the way The Dummy Table Technique is supposed to work.
But why not allow my application to create it? A good question.
CREATE-ing CREATEing the file seems safe to me, after thinking about it – even though it runs against my preference not to unnecessarily multiply entities – for one very good reason. There is no iI/o O performed against DUMMY. Therefore, while DUMMY would physically exist, it will always be empty.
The Dummy Table Technique needs the file BUFFER, not the physical file. SQLite, in it's its flat-file - mode, requires the physical file. But all my application actually uses is the BUFFER.
So, let the application CREATE the file (by placing it in the table schematic):
Figure 1: Let the app create the file
...