How Far We’ve Come – Pt. 13

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.

Pro:

The database does not care if there is 1 number in each drawing or N.

Con:

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
FROM DRAW_STATS
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.

Leave a Reply