Home

SmartForm User Manual_new

1. finish_position 1 and distance_yards lt 1500 and meeting_date gt 2006 01 01 and num_finishers gt 9 GROUP BY stall_number a e aeons pacen pasane stall_number winners average_price Prorit pranala poSaSesas fase Sse see eS Seee preen 1 6 FeLOS000 154630000 2 6 7 500000 18 000000 3 2 8 000000 11 0000090 4 3 4 793333 72 620000 5 2 9 000000 9 000000 6 3 11500000 7 500000 7 1 13 000000 14 000000 8 a 6 000000 21 000000 g i 8 000000 19 000000 10 2 12000000 3 000000 asS S esse SSse SSe ee Teese Troen 10 rows in set 0 27 sec Figure 4 2 8 Profitability of different stalls in Chester sprint races with 10 or more runners after 01 01 2006 Copyright Betwise Limited 2008 29 SmartForm User Manual 1 1 As we can see the system is still profitable in recent years since 2006 with stalls 1 and 2 continuing to return a profit through backing them blind without any consideration as to the ability preferences or form of the horse jockey or trainer In the next section we will continue this example to look at how to apply rules representing a system such as this to the daily racecards on an ongoing basis Querying Daily Tables This section runs through some examples which use the Daily Tables namely daily_races and daily_runners These tables are updated automatically with the forthcoming day s racecards available from the night before racing as well as the previous day s results to ens
2. gt below gt mysgl u root p b You should now enter your password provided that one was set up during the installation of MySQL gt Enter password Whereupon you will successfully be logged in Welcome to the MySQL monitor Commands end with or g c Next set up the database access as follows gt CREATE DATABASE smartform Then grant access to your user name for the Smartform database gt GRANT ALL ON smartform TO your_account_name localhost Copyright Betwise Limited 2008 7 SmartForm User Manual 1 1 Log off from the MySQL monitor to return to the shell prompt gt exit 4 Download the smartform data extracted to the C Smartform directory and load it into the database Step 4 explains how 4 Provided that the database download file has been unzipped to smartform ql building the database can be done from the command line as follows gt mysql u smartform p insert password if you set one up lt smartform sql From now on you can log yourself in by the specified username to interact with the database or specify the same user credentials within the context of a program to access the database Installing Daily Automated Updates Scripts on Linux and Mac OS X Operating Systems Nb Steps 5 onwards only apply if you are a subscriber to the Daily Updates Service 5 Download the scripts zip file and unpack it 6 The updates script is called fetch_updates sh in the unix directory within t
3. NO NO NO NO NO YES YES YES YES 10 SmartForm User Manual 1 1 historic_races table continued Field Description number of fences in race if applicable race Data Type Null num_fences type otherwise zero or NULL tinyint 2 YES handicap if handicap race 0 or 1 1 if true tinyint 1 YES all_weather if all weather race 0 or 1 1 if true tinyint 1 YES seller if selling race O or 1 1 if true tinyint 1 YES claimer if claimer race 0 or 1 1 if true tinyint 1 YES apprentice if apprentice race 0 or 1 1 if true tinyint 1 YES maiden if maiden race 0 or 1 1 if true tinyint 1 YES amateur if amateur race 0 or 1 1 if true tinyint 1 YES number of runners declared on day of race can be unreliable due to earlier num_runners declarations counted tinyint 2 YES num_finishers number of runners who completed race tinyint 2 YES if a handicap race the upper rating bound rating eg 0 125 handicap returns 125 here int 11 YES if a group race or graded race in National group_race Hunt the rank of the race ie 1 2 or 3 int 11 YES the minimum age eligible to compete in min_age race if captured see also conditions tinyint 2 YES the maximum age eligible to compete in max_age race if captured see also conditions tinyint 2 YES distance_yards the race distance in yards int 11 YES added_money total prizemoney for the race float 8 2 YES official_rating official rating placeholder field i
4. 1 FAV of the winner in the corresponding race last year whether the winner of the corresponding race last year was a previous course winner whether the winner of the corresponding race last year was a previous distance winner whether the winner of the corresponding race last year was a previous course and distance winner whether the winner of the corresponding race last year was a previous beaten favourite smartform internal field Daily Runners Table daily_runners Field runner_id race_id name foaling_date age colour form_figures form_type gender bred cloth_number stall_ number Description internal field reference for each unique runner PRIMARY KEY internal field reference for race_id field corresponds to daily_races table PRIMARY KEY name of horse horse date born age of horse in years colour eg Bay chestnut etc string of positions in previous races type of race for last run races Letter representing sex of horse G elding F illie M are C olt H orse country of breeding represented by 2 3 letter code in capitals eg IRE FR UK saddlecloth number for race number drawn in stalls Copyright Betwise Limited 2008 Data Type int 3 tinyint 2 tinyint 2 tinyint 2 tinyint 2 timestamp Data Type int 11 int 11 varchar 255 date tinyint 2 varchar 20 varchar 80 varchar 30 char 1 char 3 tinyint 2 tinyint 2 Null YES YES YE
5. NULL or 1 to indicate this tack present Copyright Betwise Limited 2008 Data Type int 11 int 11 int 11 varchar 80 int 11 varchar 80 varchar 80 int 11 int 3 varchar 255 varchar 80 smallint 4 varchar 80 smallint 4 varchar 80 smallint 4 char 10 float 8 2 int 4 varchar 30 int 11 tinyint 1 tinyint 1 tinyint 1 Null YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES 18 SmartForm User Manual 1 1 daily_runners table continued Field tack_eye_shield tack_eye_cover tack_cheek_piece tack_pacifiers tack_tongue_strap course_winner distance_winner candd_winner beaten_ favourite loaded_at Description whether or not eye shields were worn NULL or 1 to indicate this tack present whether or not an eye cover was worn NULL or 1 to indicate this tack present whether or not cheek pieces were worn NULL or 1 to indicate this tack present whether or not pacifiers were worn NULL or 1 to indicate this tack present whether or not a tongue strap was worn NULL or 1 to indicate this tack present how many times course winner ie Number of times or NULL how many times distance winner ie Number of times or NULL how many times course and distance winner ie Number of times or NULL if beaten favourite last time out how many times ever beaten favourite ie Number of times or NULL smartform inte
6. basic query to find all the runs of Sir Percy the 2006 Derby winner and display the basic variables above reads as per Figure 4 1 1 with the results of the query also shown Copyright Betwise Limited 2008 21 SmartForm User Manual 1 1 mysql gt SELECT meeting_date finish_position from historic_races join historic_runners using race_id where name Sir Percy 4 4 meeting_date finish_position 2005 05 28 2005 06 23 2005 07 27 2005 10 15 2006 06 03 2006 05 06 2006 10 14 2007 06 01 2007 06 20 9 rows in set 0 00 sec oou nrerrr rr Figure 4 1 1 Basic query for Sir Percy s results The results table from Figure 4 1 1 provides scant information is not strictly in date order and the column names are taking up too much width for the information shown We can address the formatting issues first by choosing a display name for the column with the command AS display name and ranking the query strictly by date by using ORDER BY then the field to order the returned rows by in this case date We ll also change the default order so that we see most recent runs first using the DESC to indicating descending order as follows mysql gt SELECT meeting_date AS Date finish_position AS Pos from historic_races join historic_runners using race_id where name Sir Percy ORDER BY date DESC 2007 06 20 2007 06 01 2006 10 14 2006 06 0
7. criteria for the purposes of generating latest selections Thus if we imagine that the current date is 12 July 2008 and that this query is run before racing then the query shown in Figure 4 5 1 is appropriate to find qualifying horses as foolows mysql gt select scheduled_time name form_figures AS jockey_name trainer_name from daily_runners join daily_races using race_id where meeting_date 2008 07 12 and course Chester and stall_number 1 OR stall_number 2 and distance_yards lt 1400 foe ee Seer Sse SSeS pore Sane sess SSS ha e ses SSS SSes fo See Sse S ee scheduled_time name AS jockey_name trainer_name HHS Ss a ee eae Sanian atin ees aia aRanaiananEineaniaaE posene Fressen freccnncnae 2008 07 12 15 35 00 Green Manalishi 0465 63 E Ahern K A Ryan 2008 07 12 15 35 00 Angus Newz 230611 M Fenton M Quinn 2008 07 12 16 10 00 Red Baron Dancer 22 P Cosgrave J R Boyle 2008 07 12 16 10 00 Dark Velvet 7 Catlin E J Alston bose Sse sen See esse poe e ase oS aes Se ona Jasa jesseni 4 rows in set 0 35 sec Figure 4 5 1 Selecting qualifiers for a daily system This should be fairly intuitive The query specifies the conditions for the system against the daily cards in an attempt to look for qualifying candidates within the system and profitable to back horses Therefore using the date the course name whether the stall either 1 and stall 2 we can find qua
8. data for all UK and Irish meetings accessible programmatically The historic data can be used to perform research and analysis to test out potential betting systems and strategies and to build predictive models or ratings to assess comparative chances within a race The daily data enables the user to apply systems and predictive models to each race for the day ahead The data from the daily racecards contains runner and past performance information that can also be used to apply existing betting systems often without daily reference to the historic database for previous form Used together both components enable bettors to analyse and produce models for UK and Irish horseracing that are tested on up to the minute data and then to apply them to daily races Since the database is configured for use with MySQL such a process can be automated and integrated within an automatic betting strategy such as outlined in the book Automatic Exchange Betting Note that the SmartForm data database formats and documentation are licenced for personal use only The data and database documentation is not for redistribution publication or resale Copyright Betwise Limited 2008 3 SmartForm User Manual 1 1 Database structure The structure of the database is simple with 4 tables in total For the historic database there are 2 tables historic_runners historic_races All runners are linked to races by the race_id field which is common to bot
9. further not least some absolute measures of performance such as official ratings Generally any other variable can be added to or subtracted from an existing query in the MySQL client very easily You can use the Up and Down keys to arrive at any previous command typed into the mysql client during the current session That can then be edited you want to adapt and then using the autocomplete function hit the TAB key to help complete names of fields Example 2 Analysing draw bias significance and profitability Here we look at the process of using the database for research into the significance of specific variables in this case the significance of the draw in flat racing Typically draw bias is not used alone as a betting system any more than any other single variable but rather as one possibly very influential factor within a betting system or ratings model In deciding what variables to use within such systems and models the process of quantifying the significance of the draw is generally applicable to other variables in the database For this particular example we concentrate on looking at one of the more well known draw biases at Chester where the tight turns on the track would seem to offer an obvious advantage horses drawn closer to the inside rail However it is still to be determined exactly what that advantage is and how profitable is following the draw alone The effect of the draw is of course different for different tra
10. race shown where available private speed rating for information purposes shown where available private speed rating for information purposes shown where available private handicap rating for information purposes shown where available private handicap rating for information purposes shown where available name of trainer internal field for trainer_id reference name of owner internal field for owner_id reference name of jockey internal field for jockey_id reference jockey claim in pounds if applicable name of horse s dam internal field for dam_id reference name of horse s sire internal field for sire_id reference name of dam s sire internal field for dam_sire_id reference the forecast price from racecard as odds to one in character format the forecast price from racecard in decimal format the starting price as odds to one in character format the starting price in decimal format any comments about pre race price movements eg op 7 2 tchd 10 3 and 13 2 rank of runner in starting prices returned from the race where 1 favourite Copyright Betwise Limited 2008 Data Type int 11 int 11 int 11 int 11 int 11 varchar 80 int 11 varchar 80 int 11 varchar 80 int 11 int 3 varchar 80 int 11 varchar 80 int 11 varchar 80 int 11 char 10 float 8 2 char 10 float 8 2 text tinyint 3 Null YES YES YES YES YES YES YES YES YES YES YES YES YES YES YE
11. string of positions in previous races Letter representing sex of horse G elding Fjillie M are C olt H orse age of horse in years country of breeding represented by 2 3 letter code in capitals eg IRE FR UK saddlecloth number for race number drawn in stalls number of fences jumped if relevant number of pounds carried over official handicap mark ie number of pounds out of handicap placeholder field in running comment for the horse in the race official rating for horse performance in race shown where available Copyright Betwise Limited 2008 Data Type varchar 20 float 10 2 timestamp Data Type int 11 int 11 varchar 255 date varchar 20 int 11 varchar 80 char 1 tinyint 3 char 3 tinyint 2 tinyint 2 tinyint 2 int 11 int 11 text int 11 Null YES YES NO Null NO NO NO NO NO YES YES YES YES YES YES YES YES YES YES YES YES 12 SmartForm User Manual 1 1 historic_runners table continued Field official_rating_type speed_rating speed_rating_type private_handicap private_handicap_type trainer_name trainer_id owner_name owner_id jockey_name jockey_id jockey_claim dam_name dam_id sire_name sire_id dam_sire_name dam_sire_id forecast_price forecast_price_decimal starting_price starting_price_decimal betting_text position_in_betting Description official rating for horse performance in
12. the database download file has been unzipped to smartform ql building the database can be done from the command line as follows Copyright Betwise Limited 2008 5 SmartForm User Manual 1 1 gt mysql u smartform p insert password if you set one up lt smartform sql From now on you can log yourself in by the specified username to interact with the database or specify the same user credentials within the context of a program to access the database Installing Daily Automated Updates Scripts on Windows Nb Steps 5 onwards only apply if you are a subscriber to the Daily Updates Service 5 Download the scripts zip file and unpack it 6 The updates script is called fetch_updates bat in the windows directory within the scripts package 7 Create a directory say C smartform 8 Store the batch file in this directory 9 Download curl a command line internet file fetching utility from http curl haxx se download html pick a Win32 version appropriate to your system 10 Unpack the curl archive and save curl exe somewhere perhaps in C smartform 11 Edit the batch file to specify the username password database name and optionally database hostname of the DB where you are storing the smartform data 12 Edit the script to specify your betwise user ID and download key 13 Check that the paths to mysql and curl specified in the batch file are correct or edit them if not 14 To update the daily racecards run fetch_upd
13. 0 6 975000 8 750000 3 8 6 375000 10 000000 4 i e E EEE 2 806667 5 5 7 140000 25 300000 6 5 12 100000 0 500000 7 3 9 000000 34 000000 8 3 12 000000 25 000000 9 1 8 000000 53 000090 10 3 1323 666667 20 000000 12 1 172000000 44 000000 4 4 1 rows in set 0 34 sec Figure 4 2 7 Profitability of stall bias in Chester sprint races In conclusion backing stalls 1 or 2 blind over all races produces a significant profit A point to note for testing profitability of systems or variables in general is the use of the starting_price_decimal field as opposed to starting price for ease of calculation Finally let s start to look at whether profitability varies over time by adding in a date condition namely showing the query in Figure 4 2 8 but with the addition of splitting results where the meeting date is greater than 1 January 2006 meeting date gt 2006 01 01 This is roughly half way through the historic database The number of races produced are also approximately half at 27 races Note that the profitability is therefore calculated off the basis of betting one unit point in all 27 rather than 61 races mysql gt select stall_number count stall_number AS winners AVG starting_price_decimal AS average_price count stall_number AVG starting_price_decimal 27 AS Profit from historic_races join historic_runners using race_id where course Chester and
14. 00 9 0345 1540 22 4 0909 9 7727 1542 47 4 9362 941 702 1662 34 6 2647 10 8529 1760 lay 6 4706 11 2941 2200 a2 4 0000 8 6563 2275 54 4 9815 8 9815 2420 2 4 0000 10 0000 2499 4 3 0000 9 0000 2640 19 4 7895 8 9474 2706 31 5 1290 9 0323 2860 5 4 6000 9 6000 2949 9 3 4444 8 2222 3495 12 S1500 10 2500 3520 5 5 0000 7 2000 4107 6 5 0000 13 0000 4180 2 13 5000 16 0000 4 4 20 rows in set 0 00 sec Figure 4 2 2 Winning average stall number to average finishers grouped by distance At any one particular distance the numbers are not hugely informative but as a group there are some notable points For example all the winners under 1540 yards at 4 specific distances accounting basically for 5 and 6 furlong races come out on average in the lower half of the draw If we run the same query to look purely at horses in this distance range aggregated together this statistic is more apparent mysql gt select count distance_yards AVG stall_number AVG num_finishers from historic_races join historic_runners using race_id where course Chester and finish_position 1 and distance_yards lt 1500 1 row in set 0 26 sec Figure 4 2 3 Average winning stall for different field sizes in sprint races Of course averages can be misleading There cannot be a draw of 3 6 any more than 9 2 runners can contend the race These averages also include results of 3 runn
15. 05 00 Flat 1540 15000 00 Doncaster 2008 06 07 1453500 Flat 1760 20000 00 Doncaster 2008 06 07 15 05 00 Flat 2260 25000 00 Doncaster 2008 06 07 15445700 Flat 13 20 6000 00 Doncaster 2008 06 07 16 30 00 Flat 2640 7500 00 Doncaster 2008 06 07 17 00 00 Flat 1100 7000 00 Doncaster 2008 06 07 172303500 Flat 1100 7000 00 4 4 4 53 rows in set 0 03 sec Figure 4 3 1 Displaying all daily races by user specified criteria The power of this type of query for identifying races of interest immediately becomes apparent when we start to apply filters In this case we may only be interested in betting Copyright Betwise Limited 2008 31 SmartForm User Manual 1 1 races over a certain prize money distance or race type Now we can immediately identify such races simply by adding a WHERE clause and a condition such as WHERE handicap 1 in the case of wanting to modify the query in Figure 4 3 1 such that only handicap races are returned for consideration Example 4 Create detailed racecard for daily racing This example shows how to display full racecards for any particular race on the daily cards Of course with the SmartForm database the definition of full racecards will be determined by the user since there are over 50 fields in daily_runners and more than that to account for in daily_races Here we have gone for basic information that fits within one page width
16. 08 which just happens to be Derby Day we can produce the following races that are due off today by simply selecting a few attributes as shown in the query that are pertinent to the racing Copyright Betwise Limited 2008 30 SmartForm User Manual 1 1 mysql gt select course scheduled_time race_type daily_races where meeting_date 2008 06 07 distance_yards added_money from 4 4 4 4 4 course scheduled_time race_type distance_yards added_money 4 4 4 4 4 Epsom_Downs 2008 06 07 13 40 00 Flat 2218 50000 00 Epsom_Downs 2008 06 07 14 10 00 Flat 1320 25000 00 Epsom_Downs 2008 06 07 14 40 00 Flat 1874 50000 00 Epsom_Downs 2008 06 07 1551500 Flat 100 50000 00 Epsom_Downs 2008 06 07 16 00 00 Flat 2650 1000000 00 Epsom_Downs 2008 06 07 16 45 00 Flat 2650 25000 00 Epsom_Downs 2008 06 07 17 20 00 Flat L320 25000 00 Musselburgh 2008 06 07 14 20 00 Flat 1100 20000 00 Musselburgh 2008 06 07 14 50 00 Flat 3080 15000 00 Musselburgh 2008 06 07 15 20 00 Flat 2640 25000 00 Musselburgh 2008 06 07 15 50 00 Flat 1760 7500 00 Musselburgh 2008 06 07 16735700 Flat S70 7900 00 Musselburgh 2008 06 07 17703700 Flat 1100 8000 00 Musselburgh 2008 06 07 17 35 00 Flat 3520 6000 00 Lingfield 2008 06 07 17 40 00 Flat 2640 2600 00 Lingfield 2008 06 07 18 10 00 Flat 2200
17. 2450 4 1 2005 10 15 Newmarket 1 8 1540 Good to Soft I NULL 97 2 2005 07 27 Goodwood 1 T 1540 Soft 1 NULL 4 1 2005 06 23 Salisbury 1 8 1320 Good to Firm 2 NULL 7 4 2005 05 28 Goodwood 1 11 1320 Good 5 NULL 8 1 9 rows in set 0 00 sec Figure 4 1 3 Fuller Race History for Sir Percy Looking purely at race history and finishing order ordered in this way we can start to better understand Sir Percy s record At first glance we can see that his performances seem to have deteriorated after the Epsom Derby on 03 06 2006 Up to and including the Epsom Derby Sir Percy had won or come second in every race after the Epsom Derby he came last or second last in every race There is no obvious Copyright Betwise Limited 2008 23 SmartForm User Manual 1 1 explanation for deterioration of comparative form in later races since the other race characteristics in the poorer races do not seem to be significantly different from the race characteristics in the better races eg different types of going and distance had been successfully encountered in the few runs up to and including the Derby as well as afterwards However for a horse with a longer running history it would be worth filtering runs purely on going or course by adding another condition to the WHERE part of this query we will see cases of this syntax in subsequent examples Of course there are many other critical variables that could be retrieved in order to analyse running history
18. 3 2006 05 06 2005 10 15 2005 07 27 2005 06 23 2005 05 28 9 rows in set 0 00 sec PRPPRPNFPYUAD Figure 4 1 2 Basic query from Figure 4 1 1 reformatted That s a lot better in terms of format but still not very informative with regard to each of Sir Percy s runs In terms of adding further information this is purely down to the user s requirements at this point since the query for adding data is of the same format as the basic query Any or all of the fields in historic_runners and historic_races as listed in Section 2 can be listed within the query to provide more data as shown in Figure 4 1 3 Copyright Betwise Limited 2008 22 SmartForm User Manual 1 1 Thus we add more values in Figure 4 1 3 showing the extended MySQL query and results from the MySQL client The fields were chosen from the tables in Section 2 as follows Date meeting_date field in historic_races Course course field in historic_races Finishing position finish_position field in historic runners Number of horses ran num_finishers field in historic_races Distance of the race in yards distance_yards in historic_races Going description going in historic_races Class of race class in historic_races Distance beaten by winner distance_behind winner in historic_runners Starting price the starting_price in fractions format in historic_runners Note that in the query in Figure 4 1 3 we also change the qu
19. 3000 00 Lingfield 2008 06 07 18 40 00 Flat 1760 3600 00 Lingfield 2008 06 07 19410700 Flat 1320 5100 00 Lingfield 2008 06 07 19 40 00 Flat 1100 3600 00 Lingfield 2008 06 07 20 10 00 Flat 1540 3600 00 Curragh 2008 06 07 17 30 00 Flat 1320 20001 00 Curragh 2008 06 07 18 00 00 Flat 1320 16000 00 Curragh 2008 06 07 18 30 00 Flat 1320 11000 00 Curragh 2008 06 07 19 00 00 Flat 2200 55001 00 Curragh 2008 06 07 19 30 00 Flat 2200 13500 00 Curragh 2008 06 07 20 00 00 Flat 1760 27000 00 Curragh 2008 06 07 20 30 00 Flat 1760 15000 00 Newcastle 2008 06 07 18 50 00 Flat 1320 4500 00 Newcastle 2008 06 07 19 20 00 Flat 1540 5000 00 Newcastle 2008 06 07 19 50 00 Flat 2232 4500 00 Newcastle 2008 06 07 20 20 00 Flat 2232 4500 00 Newcastle 2008 06 07 20 50 00 Flat 3539 4000 00 Newcastle 2008 06 07 21520200 Flat 1100 4500 00 Hexham 2008 06 07 14 15 00 Hurdle 4510 3600 00 Hexham 2008 06 07 14 45 00 Chase 4510 4500 00 Hexham 2008 06 07 15 25 00 Chase 5500 4500 00 Hexham 2008 06 07 16 10 00 Hurdle 4510 4000 00 Hexham 2008 06 07 16 40 00 Chase 4510 1800 00 Hexham 2008 06 07 17 10 00 Hurdle 3630 4000 00 Worcester 2008 06 07 13750700 Chase 5060 5000 00 Worcester 2008 06 07 14 25 00 Hurdle 4400 3600 00 Worcester 2008 06 07 14 55 00 Chase 35 20 5000 00 Worcester 2008 06 07 15335300 Hurdle 4400 7000 00 Worcester 2008 06 07 16 20 00 Chase 4510 9000 00 Worcester 2008 06 07 1650700 Hurdle 5280 4000 00 Worcester 2008 06 07 17325300 Hurdle 4400 3600 00 Doncaster 2008 06 07 14
20. S YES YES NO Null NO NO NO YES YES YES YES YES YES YES YES YES 17 SmartForm User Manual 1 1 daily_runners table continued Field long_handicap official_rating adjusted_rating trainer_name trainer_id owner_name jockey_name jockey_id jockey_claim jockey_colours dam_name dam_year_born sire_name sire_year_born dam_sire_name dam_sire_year_born forecast_price forecast_price_decimal days_since_ran days_since_ran_type weight_pounds tack_hood tack_visor tack_blinkers Description number of pounds carried over official handicap mark ie number of pounds out of handicap the official rating of the horse for today s contest the adjusted rating of the horse for today s contest name of trainer internal field for trainer_id reference name of owner internal field for owner_id reference name of jockey claim of jockey if any jockey colours name of horse s dam year dam was born name of horse s sire year sire was born name of dam s sire year dam s sire was born the forecast price from racecard as odds to one in character format the forecast price from racecard in decimal odds format number of days since horse last ran the type of race the horse last ran in weight carried in pounds whether or not a hood was worn NULL or 1 to indicate this tack present whether or not a visor was worn NULL or 1 to indicate this tack present whether or not blinkers were worn
21. S YES YES YES YES YES YES YES YES YES 13 SmartForm User Manual 1 1 historic_runners table continued finish_position amended_position unfinished distance_beaten distance_won distance_behind_winner prize_money tote_win tote_place days_since_ran last_race_type last_race_type_id last_race_beaten_fav weight_pounds penalty_weight over_weight tack_hood tack_visor tack_blinkers tack_eye_shield tack_eye_cover tack_cheek_piece tack_pacifiers tack_tongue_strap loaded_at the finishing position of the horse in the race the amended position of the horse in the race if relevant either zero or amended position if the horse did not finish the race the reason for that including Nonrunner distance the horse was beaten by the horse immediately in front of it in lengths if the horse won how far the horse won by in lengths distance the horse was beaten by the winner of the race in lengths prize money received from race if applicable in GBP price paid for the tote win for this horse if applicable price paid for the tote place for this horse if applicable number of days since the horse last ran type of race that the horse last competed in type_id of the race that the horse last competed in whether or not a beaten favourite in the last race 1 if a beaten favourite O if not NULL if N A weight carried in pounds penalty weight carried in pounds if applicable any ove
22. SmartForm User Manual 1 1 SmartForm Database User Manual Copyright Betwise Limited 2008 SmartForm User Manual 1 1 Contents ROTTS eset cate ane etn ee NR OU Satna aida ood tate kin ete a elit E 2 Ls Introduce Gthg c 2 c2scci a ae aaa a AE a aa a 3 SmartForm Database Overview sccicecitsnccoeddaiciec Mudie Gee Sore pee ate Rs 3 Database SUUCIIIS iis E A A E 4 Database Management System MySQL sessessessessrsesesrsesesesrsrssrseresesesesesrnrresreresesrseseses 4 2 Installation of the SmartForm Database s s seseeeseseseeessseesesesesesesesessseeeseseseseseeesssseeess 5 Installing on WiIndO WS seisi raaa Ea a ea aea osa a a aas 5 Installing Daily Automated Updates Scripts on Windows seessseeesesseeressrersrseresesreereses 6 Nb Steps 5 onwards only apply if you are a subscriber to the Daily Updates Service 6 Installing on Linux and Mac OS X Operating Systems eee eee csecseeseesesseseeaeeseeeeeeens 7 Installing Daily Automated Updates Scripts on Linux and Mac OS X Operating SAIA a K aa OAN E E EE E E A E E A S EE A E S EE EES 8 Nb Steps 5 onwards only apply if you are a subscriber to the Daily Updates Service 8 Historic Races Table Historic Aces osssccsiedeiee to ve ceosssavsaveeese rasta sesegavaanceededates aneeeonass 10 Historic Runners Table historic_runners 0 0 0 ee eeeeeseesceseceseecescesseeseeseeeceeeeseeeeeeees 12 Daily Races Table daily_races 3 icsceivssvennadeban cee ehdieatad duedene cha daedseuspaebcavenn
23. abase This section covers installation of the database assuming that the database has been successfully downloaded from www betwise co uk Notes on installing MySQL are also provided when the database is downloaded If you have followed the installation process from the website you can ignore this section Email support on database installation is also available at www betwise co uk contact This section therefore provides a summary of the installation process Installing on Windows 1 Install mysql at www mysql com downloads Choose Community Edition the free download and the correct version for your operating system 2 Create a Smartform database for example a Type the following to log into the mysql monitor at the command prompt gt below emysql u root p b You should now enter your password provided that one was set up during the installation of MySQL gt Enter password Whereupon you will successfully be logged in Welcome to the MySQL monitor Commands end with or g c Next set up the database access as follows gt CREATE DATABASE smartform Then grant access to your user name for the Smartform database gt GRANT ALL ON smartform TO your_account_name localhost Log off from the MySQL monitor to return to the shell prompt gt exit 3 Download the smartform data extracted to the C Smartform directory and load it into the database Step 4 explains how 4 Provided that
24. ates bat with the parameter daily e g C smartform fetch_updates bat daily similarly to update the historic race data run fetch_updates sh with the parameter historic e g C smartform fetch_updates bat historic 15 To automate these tasks you can set them up as windows Scheduled Tasks Copyright Betwise Limited 2008 6 SmartForm User Manual 1 1 at the windows prompt enter at 00 30 every monday tuesday wednesday thursday friday saturday sunday c smartform fetch_updates bat daily at 05 30 every monday tuesday wednesday thursday friday saturday sunday c smartform fetch_updates bat historic you can opt not to run the scheduler every day if you wish to view these jobs you can do so from the Scheduled Tasks tool go to Start gt All Programs gt Accessories gt System Tools gt Scheduled Tasks If you have not used the at utility before these jobs will be called Atl and At2 You may wish to rename them If running the scripts would require you to be logged on for example to make use of a shared drive you may need to manually configure the tasks using the Scheduled Tasks utility Installing on Linux and Mac OS X Operating Systems 1 Install mysql at www mysql com downloads Choose Community Edition the free download and the correct version for your operating system 2 Create a Smartform database for example a Type the following to log into the mysql monitor at the command prompt
25. char 80 varchar 255 varchar 80 varchar 80 varchar 80 varchar 80 tinyint 1 tinyint 1 tinyint 1 varchar 30 int 11 float 8 2 float 8 2 datetime Null NO NO NO YES YES YES YES NO YES NO YES YES YES YES YES YES YES YES YES YES YES NO 15 SmartForm User Manual 1 1 daily_races table continued Field prize_pos_1 prize_pos 2 prize_pos_3 prize_pos 4 prize_pos_5 prize_pos_ 6 prize_pos_7 prize_pos_8 prize_pos_9 last_winner_no_race last_winner_year last_winner_runners last_winner_runner_id last_winner_name last_winner_age last_winner_bred last_winner_weight last_winner_trainer last_winner_trainer_id last_winner_jockey last_winner_jockey_id last_winner_sp last_winner_sp_decimal Description prize money for first place prize money for second place where available prize money for third place where available prize money for fourth place where available prize money for fifth place where available prize money for sixth place where available prize money for seventh place where available prize money for eighth place where available prize money for ninth place where available if a corresponding race to this happened last year this field is NULL otherwise reason is shown the year referred to by last_winner_ fields the number of runners in the corresponding race last year the runner_id for wi
26. cks and for those tracks can vary greatly in its significance according to different conditions with race distance number of runners and going the most widely acknowledged contributing factors Using the SmartForm database provides a way to assess the effect of the draw for particular tracks under different conditions on an ongoing basis So let s look at the process and start to run some specific queries To begin to analyse the significance of a horse being drawn in any particular part of the track we first have to look at the number of races at the particular course to see if there are a significant number of races to query Given that we are interested in the draw bias at Chester identifying the number of races run at Chester is the first query mysql gt select count from historic_races where course Chester Copyright Betwise Limited 2008 24 SmartForm User Manual 1 1 4 counti 445 1 row in set 0 00 sec Figure 4 2 1 Number of races found for Chester between 01 01 03 and 01 09 08 The COUNT function applied to any variable returns the number of rows or times occurred found for the query as opposed to individual results It s very useful in many of the operations for which the database is typically used since assessing systems or the significance of variables often involves identifying and measuring ie counting the number of times an event has occurred relat
27. db caveveancestvess 15 Daily Runners Table daily TUnme rs scs c 0iss secccecskeleeavenesnienws aveeasedsbdeanedesebabcss ede eeghosus 17 4 Using The SmartForm Database vi nicite gececieccucduceeseestpeacd dengabe cd neceelcshetrteiedeteodsonvenedaxoates 20 RISAS CS ON DA A EEEE sah ede nlen Sanur tect pac vce ale taue den Garud Aveda E 20 Querying Historie Tables aeea a e a a i a a 21 Queryimne Daily Tables tar aerer a E a tied Ra 30 Appendix 1 Additional MySQL references seseeeesesesesesseesessereesrseresrsrsrsssrerrsrsrneseseeersreeee 34 Copyright Betwise Limited 2008 2 SmartForm User Manual 1 1 1 Introduction SmartForm Database Overview The SmartForm database is a comprehensive source of horseracing form for races run in the UK and Ireland consisting of two main components 1 The SmartForm Historic database which contains over 5 years data for horseracing results in the UK and Ireland detailing race and runner attributes from January 1 2003 to the date of purchase from October 2008 onwards 2 The SmartForm Daily service which brings the historic database up to date beyond the date of purchase up to and including the current date for current subscribers and supplies results and racecards on a daily basis specifically a Automatically updating the historic database with the previous day s results b Automatically updating the database with daily racecards prior to racing consisting of race and runner
28. er races where we would not expect the draw to have any significance with 16 runner races where we would Therefore there are further queries to look at before we can arrive at any sensible results Typically it is safe to assume that stall bias is more apparent when there are minimum number of runners in the race This is for several reasons mainly that no horse is forced to Copyright Betwise Limited 2008 26 SmartForm User Manual 1 1 race significantly far away from the other horses in a small field so draw bias is more difficult to detect This assumption can of course be tested itself Let s therefore split the last query on draw bias up according to the number of finishers in the race as shown in Figure 4 2 4 mysql gt select num_finishers count num_finishers AS Races AVG stall_number from historic_races join historic_runners using race_id where course Chester and finish_position 1 and distance_yards lt 1500 GROUP BY num_finishers 4 num_finishers Races AVG stall_number 3 2 4 5000 4 6 2 3333 5 4 3 0000 6 13 3 1538 7 13 222077 8 18 346111 9 21 3 8571 10 20 3 9900 Ti 8 4 6250 12 6 4 8333 13 p E 4 4167 14 9 ore E DD i 15 5 4 8000 16 1 5 0000 4 4 rows in set 0 24 sec Figure 4 2 4 Average winni
29. erage from the top quarter of the draw However none of the queries so far has shown the results of the stall bias directly or whether or not betting on that apparent bias might be profitable Copyright Betwise Limited 2008 27 SmartForm User Manual 1 1 Therefore in Figure 4 2 6 let s begin this process by breaking down these 61 races according to their winning stall number and add in the average starting price for each winner from that stall The count stall_number column shows the number of times this stall number won for races over 10 runners at distances of less than 7 furlongs This itself looks highly correlated with the draw suggesting that it is a significant factor mysql gt select stall_number count stall_number AVG starting_price_decimal from historic_races join historic_runners using race_id where course Chester and finish_position 1 and distance_yards lt 1500 and num_finishers gt 9 GROUP BY stall_number stall_number count stall_number AVG starting_price_decimal 1 LS 7 449286 2 10 6 975000 3 8 6 375000 4 a eP EREE 5 5 7 140000 6 5 12100000 7 3 9 000000 8 3 12 000000 9 I 8 000000 10 3 13 666667 12 1 17 000000 4 4 4 1 rows in set 0 26 sec Figure 4 2 6 Winn
30. ery slightly to show use of the WHERE condition for joining the 2 tables as an alternative to JOIN which still joins tables on field common to both namely race_id Once you have a feel for the basic way that the tables are organised anything specific to a runner is in historic_runners and anything specific to a race is in historic_races knowing where to look for the fields is quite intuitive After that it s a question of becoming familiar with the field names The autocomplete function in the MySQL client can serve as a useful reminder without referring back to the manual mysql gt SELECT meeting_date AS Date course finish_position AS Pos num_finishers AS Ran distance_yards AS Dist going class distance_behind_winner AS lost_by starting_price as SP from historic_races historic_runners where name Sir Percy and historic_races race_id historic_runners race_id ORDER BY Date DESC Date course Pos Ran Dist going elass lost_by SP 4 4 4 4 4 2007 06 20 Ascot 6 6 2200 Good a 8 25 1171 2007 06 01 Epsom_Downs 6 Ki 2640 Good to Soft 1 5 88 7 2 2006 10 14 Newmarket 7 8 2200 Good to Soft 1 7 69 11 4 2006 06 03 Epsom_Downs 1 17 2650 Good to Firm 1 NULL 6 1 2006 05 06 Newmarket 2 14 1760 Good to Firm q
31. ger 2008 06 07 16200300 Rio de La Plata 1214 2 L Dettori Saeed bin Suroor 2008 06 07 16 00 00 River Proud 2817 43 T Quinn P F I Cole 2008 06 07 16300200 Tajaaweed L051 R Bilis Sir Michael Stoute 2008 06 07 16200200 Tartan Bearer 2 11 R L Moore Sir Michael Stoute 2008 06 07 16 00 00 Washington Irving 4 22 C O Donoghue A P O Brien foe SS Se See ee SSS See paenan pasna praana 17 rows in set 0 48 sec 17 rows in set 0 41 sec Figure 4 4 1 Racecard for Derby Day configured by user As with the queries on historic_runners and historic_runners queries on daily races generally involve a join on two tables in this case daily_races and daily_runners which are Copyright Betwise Limited 2008 32 SmartForm User Manual 1 1 analogous to the type of data contained in the historic database again split by race attributes and runner attributes Example 5 Applying a system or model to daily racing Earlier we saw the example from the subsection Querying Historic Tables which investigated and found significant draw bias in certain race types at Chester This was interesting research but how could such a system be applied to make money on a daily basis in an automated way Example 5 shows one way of doing that Example 2 provides us with the clue we need as to rules The query is as follows taken as a group all horses drawn in stalls 1 and 2 show long term profitability when racing over Chester sprint races Therefore we need to specify these
32. going class handicap trifecta showcase age_range distance_yards added_money penalty_value scheduled_time Description internal field reference for race_id field corresponds to historic_runners table PRIMARY KEY internal database field date of the meeting and therefore the race in question text string for weather forecast smartform internal field smartform internal field comment on draw advantage for meeting eg Low best in races up to a mile name of course country of race meeting name of the race type of race values being Flat Hurdle Chase NH Flat AW Flat Point to Point Direction of track eg Left Handed Right Handed Straight forecast going eg Hard Firm Good to Firm Good Good to Soft Standard etc Class of race on flat by number entries prior to change in system in 2005 are irrelevant if handicap race 0 or 1 1 if true whether or not race is a trifecta race 0 or 1 1 if true whether or not race is a showcase race O or 1 1 if true description of range of eligible ages for race eg 4YO to 6YO or 2YO only All start YO the race distance in yards total prizemoney for the race the prize money that the winner will be deemed to have won time that race is due off including the date in datetime format so yyyy mm dd hh mm ss Copyright Betwise Limited 2008 Data Type int 11 int 11 date text varchar 80 varchar 80 text varchar 255 var
33. h tables All the fields available within each table are described in Section 2 Examples of using the historic database begin in Section 3 The SmartForm Daily service updates the historic tables automatically with all results in the UK and Ireland maintaining them up to the current date For the daily racecards provided by the SmartForm Daily service there are two more tables daily_runners daily_races Daily runners are linked to daily races by the race_id field All fields available within each table are detailed in Section 2 Examples of using the database tables for daily racecards begin in Section 4 Database Management System MySQL The SmartForm database download and the Smartform Daily service are designed for use with the MySQL database management system DBMS MySQL is one of the most robust and popular databases in the world that runs on Windows Mac and Linux machines is available for free download at www mysql com Installing MySQL is a prerequisite for using the SmartForm database although data can subsequently be exported to other formats if required This user guide provides examples showing how to use the SmartForm database in the MySQL database management system but it is no substitute for understanding how MySQL works the reference materials referred to in Appendix are recommended for this purpose Copyright Betwise Limited 2008 4 SmartForm User Manual 1 1 2 Installation of the SmartForm Dat
34. he scripts package 7 Install this script file somewhere perhaps in a bin directory in your home directory 8 Edit the script file to specify the username password database name and optionally database hostname of the DB where you are storing the smartform data 9 Edit the script to specify your betwise user ID and download key 10 To update the daily racecards run fetch_updates sh with the parameter daily e g bin fetch_updates sh daily similarly to update the historic race data run fetch_updates sh with the parameter historic e g bin fetch_updates sh historic 11 To automate these updates create a crontab entry for each Edit your crontab with the command crontab e Copyright Betwise Limited 2008 8 SmartForm User Manual 1 1 and add these lines fetch the racecards at half past midnight 30 00 home smartform bin fetch_updates sh daily fetch the result files at 5 30am 30 05 home smartform bin fetch_updates sh historic Copyright Betwise Limited 2008 SmartForm User Manual 1 1 3 SmartForm Database Tables and values There are 4 basic tables that come as standard in the database each of which is automatically updated by the Daily Updates service daily_races historic_races historic_runners daily_runners Each table is listed with each field name that is found within the table in the subsequent sections of this Section For each field there is a description of what is rep
35. ing stall counts in sprint races over 10 runners with average SPs Let s tidy this query up a little in terms of aliasing the columns to make the names shorter and more meaningful and moreover look at the overall historic profitability of backing each draw blind in sprints at Chester with larger fields Since MySQL queries as we have already shown can incorporate calculations on the values returned we can do this within the context of the new query as in Figure 4 2 7 by multiplying the winners from each stall by the average price of winners from that stall then subtracting the total number of bets assuming we would have had one bet on each stall in every race for all 61 races given that stall 11 and 12 was not present in every race this is not an accurate representation of backing those stalls blind of course Copyright Betwise Limited 2008 28 SmartForm User Manual 1 1 mysql gt select stall_number count stall_number AS winners AVG starting_price_decimal AS average_price count stall_number AVG starting_price_decimal 61 as Profit from historic_races join historic_runners using race_id where course Chester and finish_position 1 and distance_yards lt 1500 and num_finishers gt 9 GROUP BY stall_number 4 4 stall_number winners average_price Profit 4 1 15 7 449286 504739236 2 1
36. ive to the possible number of outcomes We ll also see this in looking at profitability of certain events So far all we have done is count the number of races run Next given that distance is a key variable in assessing the effect of the draw let s break down these races by distance and start to look at the distribution of stall numbers in terms of which stall the winning horse came from To do this we show all unique distances run at Chester using the COUNT function to count each unique distance in conjunction with GROUP BY to itemise each unique distance To show how significant the draw is for each distance found we use the average function AVG applied to both the stall number and the number of finishers in a race to show the average winning stall number and average field size for each distance Copyright Betwise Limited 2008 25 SmartForm User Manual 1 1 mysql gt select distance_yards count distance_yards AVG stall_number AVG num_finishers from historic_races join historic_runners using race_id where course Chester and finish_position 1 GROUP BY distance_yards 4 4 4 4 distance_yards count distance_yards AVG stall_number AVG num_finishers 4 4 4 1100 ical 3 29677 8 8710 IrIG 65 3 4615 Oe IO TT 1320 16 3 0000 7 4375 1338 29 4 00
37. lifying horses We see results for 4 horses in the returned results This stands to reason since there are two sprint races on the day so it follows that stalls 1 and 2 will be occupied therefore throwing up 4 bets It turns out that Green Manalishi won its race Indeed the system was profitable over the whole of 2008 and using this query from SmartForm identifies the winner each time Copyright Betwise Limited 2008 33 SmartForm User Manual 1 1 Appendix 1 Additional MySQL references This short list is aimed at the beginner to MySQL Many more references are available and can be found online but the below already cover more than enough material with the recommended starting text being Learning MySQL The material outlined in Learning MySQL is also sufficient to cover any of the described uses of the SmartForm database and much more Thereafter the references become slightly more esoteric in terms of optimising database performance and covering programmatic access to the database See also online resources for MySQL at www mysql com Learning MySQL Seyed M M Tahaghoghi amp Hugh E Williams O Reilly 2006 MySQL Third Edition Paul DuBois Sams 2005 MySQL Cookbook Paul DuBois O Reilly 2007 Copyright Betwise Limited 2008 34
38. many more rows can be successfully displayed on one line from the MySQL monitor client We also specify an obvious filter for the date in question in terms of which race to use ie we choose the only race paying over 900 000 in prizemoney for building the card all of which can easily be reconfigured mysql gt select scheduled_time name form_figures AS form jockey_name trainer_name from daily_runners join daily_races using race_id where meeting_date 2008 06 07 and added_money gt 900000 and course Epsom_Downs HSS Sa a Panenan fon se AR RRS Se Sa SS ae scheduled_time name form jockey_name trainer_name e Josenea peen foenn fens SS eS 2008 06 07 16 00 00 Alan Devonshire 61223 4 P Mulrennan M H Tompkins 2008 06 07 16200300 Alessandro Volta 831 41 J A Heffernan A P O Brien 2008 06 07 16 00 00 Bashkirov 34 53 D R McCabe A P O Brien 2008 06 07 16 00 00 Bouguereau T43 127 A Munro P W Chapple Hyam 2008 06 07 16 00 00 Casual Conquest 1 1 P J Smullen D K Weld 2008 06 07 16 00 00 Curtain Call 6221 5 1 J P Spencer L M Cumani 2008 06 07 16 00 00 Doctor Fremantle 221 21 K McEvoy Sir Michael Stoute 2008 06 07 16700200 Frozen Fire 16 2 M J Kinane A P O Brien 2008 06 07 16 00 00 Kandahar Run 211 21 T E Durcan H R A Cecil 2008 06 07 16 00200 King of Rome T10 52 J Murtagh A P O Brien 2008 06 07 16300200 Maidstone Mixture 00 5302 M O Connell Paul Murphy 2008 06 07 16 00 00 New Approach TT11 22 K J Manning J S Bol
39. nfamiliar with MySQL or SQL the best place to start in order to get the most out of the SmartForm database is with a guide to MySQL or SQL itself and using the query language in particular since the database is already there Some of the best references for getting started with MySQL are listed in Appendix 1 Knowledge of SQL should be combined with an understanding of the different variables within the database as listed in Section 2 Therefore this Section provides some specific examples to show how the database can typically be used which can easily be copied and extended for more general uses but is no substitute for references to MySQL and structured query language SQL in general Copyright Betwise Limited 2008 20 SmartForm User Manual 1 1 Querying Historic Tables This section runs through some usage examples from the historic database tables in order to illustrate common database operations All the database operations are shown from the perspective of using the interactive MySQL command line client that comes as standard with the MySQL download The commands are listed within each example under the different Figure listings showing the SQL required to generate the query along with the results returned The examples show typical ways that the data can be manipulated and queried from the perspective of analysing historic results In the next section Querying the Daily Tables we cover examples from the perspective
40. ng stall for different field sizes in sprint races Given that the draw results from higher numbers of finishers should be more indicative of any significant bias it typically therefore makes sense to apply a minimum number of runners to detecting stall bias We can see in the results where any significance might be and where it might make sense to do so with a possible rule of thumb being that the lower the average draw number and the higher the field size the greater the significance Here is a final adapted query which shows the average ratio of average runners to the average winning stall number mysql gt select COUNT num_finishers AVG stall_number AVG num_finishers AVG stall_number num_finishers AS ratio from historic_races join historic_runners using race_id where course Chester and finish_position 1 and distance_yards lt 1500 and num finishers gt 9 COUNT num_finishers AVG stall_number AVG num_finishers ratio 4 6l 3 9016 12 0164 0 26010929 4 4 4 4 1 row in set 0 23 sec Figure 4 2 5 Average winning stall for fields of 10 and more in Chester sprints A ratio of 0 26 over 61 races looks fairly significant In other words the winner of every race came on av
41. nner of the corresponding race last year the name of the winner in the corresponding race last year the age of the winner in the corresponding race last year the country of breeding of the winner in the corresponding race last year the weight of the winner in the corresponding race last year the winning trainer from the corresponding race last year the trainer_id of the winning trainer in the corresponding race last year the winning jockey from the corresponding race last year the trainer_id of the winning jockey in the corresponding race last year the starting price of the winner from the corresponding race last year the SP in decimal odds of the winner in the corresponding race last year Copyright Betwise Limited 2008 Data Type float 8 2 float 8 2 float 8 2 float 8 2 float 8 2 float 8 2 float 8 2 float 8 2 float 8 2 varchar 255 int 4 int 3 int 11 varchar 255 tinyint 2 char 3 int 11 varchar 80 int 11 varchar 80 int 11 char 10 float 8 2 Null YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES 16 SmartForm User Manual 1 1 daily_races table continued Field last_winner_betting_ranking last_winner_course_ winner last_winner_distance_winner last_winner_candd_winner last_winner_beaten_ favourite loaded_at Description the ranking in the betting eg
42. nt 11 YES speed_rating speed_rating for race placeholder field int 11 YES private_handicap private handicap for race placeholder field int 11 YES displays the schedule date and time for the scheduled_time race in yyyy mm dd hh mm ss format datetime NO displays the actual time that the race started using date field and 12 hour clock off_time needs transforming datetime NO The winning time in winning_time_disp minutes seconds milliseconds format varchar 20 YES The winning time in seconds decimal winning_time_secs format only float 10 2 YES Copyright Betwise Limited 2008 SmartForm User Manual 1 1 historic_races table continued Field standard_time_disp standard_time_secs loaded_at Description the standard time for this race and distance in minutes seconds milliseconds format the standard time for this race in seconds decimal format internal smartform field Historic Runners Table historic_runners Field runner_id race_id name foaling_date colour distance_travelled form_figures gender age bred cloth_number stall_ number num_fences_ jumped long_handicap how_easy_won in_race_comment official_rating Description internal field reference for each unique runner PRIMARY KEY internal field reference for race_id field corresponds to historic_races table PRIMARY KEY name of horse horse date born colour eg Bay chestnut etc distance travelled from stable to racecourse
43. of queries on upcoming races and recent results Example 1 Looking up a runner s racing history This example shows how to retrieve details of a particular runner s racing history in the UK and Ireland by specifying the runner s name This type of query is useful for ad hoc research or more typically if looking up certain historic attributes of each competing horse from a daily racecard Combined with the daily_runners table such queries can also be automated to retrieve the full history of all runners in an upcoming race and rate them according to the attributes found in the database against the user s own criteria This example and subsequent ones show how almost any useful query on historic data in the SmartForm database combines information from both the historic_races and historic_runners tables Since joint information from two tables is commonly used in all queries we will have to specify how we want the tables to be joined The typical JOIN is always on race_id between historic_runners and historic_tables Taking the example of the most basic information from a runner s history we will want to see the meeting_date found in the historic_races table and the finish_position found in the historic_runners table so we need the race_id for both items of data from the different tables to be the same otherwise the query will not make sense Note that the WHERE condition can also be used to achieve this and is shown later A
44. r weight in pounds above the official weight that the horse is allocated whether or not a hood was worn NULL or 1 to indicate this tack present whether or not a visor was worn NULL or 1 to indicate this tack present whether or not blinkers were worn NULL or 1 to indicate this tack present whether or not eye shields were worn NULL or 1 to indicate this tack present whether or not an eye cover was worn NULL or 1 to indicate this tack present whether or not cheek pieces were worn NULL or 1 to indicate this tack present whether or not pacifiers were worn NULL or 1 to indicate this tack present whether or not a tongue strap was worn NULL or 1 to indicate this tack present internal smartform field Copyright Betwise Limited 2008 tinyint 3 tinyint 3 varchar 30 float 8 2 float 8 2 float 8 2 float 8 2 float 8 2 float 8 2 int 4 varchar 80 int 11 int 1 int 11 int 11 int 11 tinyint 1 tinyint 1 tinyint 1 tinyint 1 tinyint 1 tinyint 1 tinyint 1 tinyint 1 timestamp YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES NO 14 SmartForm User Manual 1 1 Daily Races Table daily_races Field race_id meeting_id meeting_date weather meeting_status meeting_abandoned_reason draw_advantage course country race_title race_type track_type advanced_
45. resented the MySQL data type of the field and whether or not the field can hold a null value Historic Races Table historic_races Field race_id meeting_id meeting_date course conditions race_name race_abbrev_name race_type_id race_type race_num going direction class draw_advantage Description internal field reference for race_id field corresponds to historic_runners table PRIMARY KEY internal database field date of the meeting and therefore the race in question name of course conditions of race eg Handicap Chase 0 125 Name of the race same data as conditions field slightly different format ages in brackets internal field referencing race_type by a number useful for queries on certain types type of race values being Flat Hurdle Chase NH Flat AW Flat Point to Point number of race on racecard in terms of running order eg first race 1 going eg Hard Firm Good to Firm Good Good to Soft Standard etc Direction of track eg Left Handed Right Handed Straight Class of race on flat by number entries prior to change in system in 2005 are irrelevant comment on draw advantage for meeting eg Low best in races up to a mile Copyright Betwise Limited 2008 Data Type int 11 int 11 date varchar 255 varchar 255 varchar 255 varchar 80 int 11 varchar 80 tinyint 2 varchar 80 varchar 80 tinyint 2 varchar 80 Null NO NO NO NO NO
46. rnal field Copyright Betwise Limited 2008 Data Type tinyint 1 tinyint 1 tinyint 1 tinyint 1 tinyint 1 tinyint 2 tinyint 2 tinyint 2 tinyint 2 timestamp Null YES YES YES YES YES YES YES YES YES NO 19 SmartForm User Manual 1 1 4 Using The SmartForm Database Usage Overview The SmartForm database provides UK and Irish racing data within the context of a powerful programming environment Since the database is designed for use with MySQL all the facilities within MySQL including the power of Structured Query Language SQL for interactive queries and all the programmatic interfaces to MySQL such as Perl DBI are available to users for manipulating and querying the SmartForm database Users who are skilled in SQL can ask questions and get back answers from the data As such there is no prescriptive way to use the database there are for all practical purposes infinite ways to use and program the SmartForm database The ways in which the database is used will depend on what the user wants to do their understanding of the fundamental variables used and their knowledge of the database management system This manual provides a start for new users by discussing some typical uses describing the fundamental variables and showing examples via the MySQL command line client that give an indication of how MySQL can be used to explore the database For any SmartForm users who are u
47. ure that the historic tables are up to date Users can typically query these tables on ad hoc basis and apply systems or models to daily data to identify potential bets or to rate all races according to an existing model In the examples below we produce fewer ad hoc queries on the data to get to the required results since we assume that the examples already shown have been read and therefore that the basic approach and syntax to querying the database is understood As with the queries on historic_runners and historic_runners queries on daily races generally involve a join on two tables in this case daily_races and daily_runners which are similarly split by race attributes and runner attributes Example 3 Retrieve summary race details for daily cards by user criteria Example 3 and Example 4 show how we can use the daily tables to interrogate the day s racing on an ad hoc basis Example 3 is a query to list all races in the UK and Ireland for the upcoming day according to certain criteria determined by the user such as whether or not the race falls in a certain race type where races are over a certain value and soon Any of the criteria in daily_races can be used as criteria With the database come a set of previous examples of daily_races and daily_runners files the up to date versions of these are supplied as part of the daily updates service and it is those we use for this example Imagining that the current date is 7 June 20

SmartForm User Manual_new

Contents

Download Pdf Manuals

Related Search

Related Contents