Before we jump straight into the code, we need to talk a bit about the design of this application. When I first started coding up a new version using Qt and SQLite I originally started with the old design.
sqlite> select strftime( '%Y%m%d', draw_dt) as 'drawing date', no_1, no_2, no_3, no_4, no_5, no_6 from drawings order by draw_dt asc limit 15; drawing date no_1 no_2 no_3 no_4 no_5 no_6 19990102 9 12 18 32 34 47 19990106 3 9 11 17 18 41 19990109 2 17 19 36 38 45 19990113 4 14 24 28 31 38 19990116 11 25 31 35 42 45 19990120 15 20 28 30 36 43 19990123 11 12 20 28 42 45 19990127 3 25 26 31 45 47 19990130 3 24 25 37 44 48 19990203 2 7 22 37 39 40 19990206 4 6 10 20 42 46 19990210 2 10 18 28 33 35 19990213 1 7 19 29 30 39 19990217 18 21 30 42 45 46 19990220 1 15 19 30 32 38
This had been the way I set it up from the earlies days of coding it under DOS with C and a roll-your-own file system. It was always hard coded for 6 drawing numbers in each drawing as well as a hard coded drawing number range. I was only writing it for myself and the lottery was brand new. Nobody envisioned the addition of larger range and special numbers.
Those early programs would chew through the entire file creating a stats structure in memory for each possible drawing number. That was important then because I needed to know the zeros. The lottery was new enough that not every number had been drawn. Roll overs were typically caused by some first time for a zero, or so I believed.
Once I started playing around with indexed file systems we called databases, like various XBASE libraries, I started creating a STATS file/table/database which would be repopulated via a menu option, then I could run any of the reports I had written against it. This worked for me because I wasn’t a regular lottery player. I could have several drawings to enter or a new CSV to import. (Of course the state lottery didn’t start having a CSV file until they got a Web site, now they don’t offer the download option.)
Storage wise this was the most efficient and, don’t forget, 40MEG hard drives were several hundred dollars and you could only have 2 in your computer before you had to get some klunky add-in card. Later we got SCSI and could have 6 larger drives. Eventually those SCSI drives got up to a whopping 1Gig but they came at a hefty price tag.
Please examine these two different directory listings. The first is storing the import data the old way and the second is my new way.
roland@roland-HP-Compaq-8100-Elite-SFF-PC:~/sqlite_data$ ls -al total 184 drwxrwxr-x 2 roland roland 4096 Sep 5 20:53 . drwxr-xr-x 100 roland roland 20480 Sep 5 20:19 .. -rw-r--r-- 1 roland roland 158720 Sep 5 20:53 lottoDB.sqlite roland@roland-HP-Compaq-8100-Elite-SFF-PC:~/sqlite_data$ ls -al total 620 drwxrwxr-x 2 roland roland 4096 Sep 6 10:13 . drwxr-xr-x 100 roland roland 20480 Sep 5 21:23 .. -rw-r--r-- 1 roland roland 605184 Sep 6 10:13 lottoDB.sqlite
What is the new way you ask?
sqlite> .head on sqlite> .separator \t sqlite> select strftime( '%Y%m%d', draw_dt) as 'drawing date', draw_no from drawings order by draw_dt asc limit 20; drawing date draw_no 19990102 9 19990102 12 19990102 18 19990102 32 19990102 34 19990102 47 19990106 3 19990106 9 19990106 11 19990106 17 19990106 18 19990106 41 19990109 2 19990109 17 19990109 19 19990109 36 19990109 38 19990109 45 19990113 4 19990113 14
In an era before we wasted processor time and disk storage on FaceBook, Youtube and cat videos, we would _never_ consider repeating a date field (or storing the century) but those days are behind us.
The database does not care if there is 1 number in each drawing or N.
Unless you have an external configuration file, you have no way to identify zeros.
The con, in this case, isn’t so bad because I pulled down data back to 1999.
Later on, in my OpenVMS Application Developer book I got around to using RDB and creating a DRAW_STATS table.
DECLARE DUE_DRAW_CURSOR READ ONLY CURSOR FOR
SELECT ELM_NO, HIT_COUNT, SINCE_LAST, PCT_HITS, AVE_BTWN
WHERE SINCE_LAST > AVE_BTWN
ORDER BY SINCE_LAST DESC
It was still generated via a menu option, but, it met all of my reporting needs. Early PC versions also had LONGEST_SEQ (most drawings in a row the number came up), LONGEST_BTWN (most number of drawings between two hits) and SEQ_COUNT (number of times a drawing number came up in sequence.) While all of this information was interesting, I left it out of our application. I’m merely telling you about this now because you may wish to add such calculations for your own amusement. There used to be a handful of numbers which would come up 2-N times in a row whenever they finally came up then would have long gaps of not coming up. Hey, I am a geek. I found such things interesting. Nope, not once did I ever win a massive jackpot, but I did hit smaller payouts and then kind of quite playing once I started dating regularly and was wasting money on other pursuits with a hopefully higher payoff.
Eventually I coded export utilities and other things our application will lack. Mostly I did this because I was experimenting with some new tool and I chose this application to be my self paced tutorial. I could compare the output of the two and see if I had made some mistake.