1#!/usr/bin/perl 2# Copyright (c) 2000, 2001, 2003, 2006 MySQL AB, 2009 Sun Microsystems, Inc. 3# Use is subject to license terms. 4# 5# This library is free software; you can redistribute it and/or 6# modify it under the terms of the GNU Library General Public 7# License as published by the Free Software Foundation; version 2 8# of the License. 9# 10# This library is distributed in the hope that it will be useful, 11# but WITHOUT ANY WARRANTY; without even the implied warranty of 12# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 13# Library General Public License for more details. 14# 15# You should have received a copy of the GNU Library General Public 16# License along with this library; if not, write to the Free 17# Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, 18# MA 02110-1301, USA 19# 20# Test of creating the ATIS database and doing many different selects on it 21# 22# changes made for Oracle compatibility 23# - added Oracle to the '' to ' ' translation 24# - skip blank lines from the datafiles 25# - skip a couple of the tests in Q4 that Oracle doesn't understand 26################### Standard benchmark inits ############################## 27 28use Cwd; 29use DBI; 30use Benchmark; 31 32$opt_loop_count=100; # Run selects this many times 33 34$pwd = cwd(); $pwd = "." if ($pwd eq ''); 35require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n"; 36 37if ($opt_small_test) 38{ 39 $opt_loop_count/=10; 40} 41 42print "ATIS table test\n\n"; 43 44#### 45#### Connect and start timeing 46#### 47 48$dbh = $server->connect(); 49$start_time=new Benchmark; 50 51#### 52#### Create needed tables 53#### 54 55init_data(); # Get table definitions 56 57if (!$opt_skip_create) 58{ 59 print "Creating tables\n"; 60 $loop_time= new Benchmark; 61 for ($ti = 0; $ti <= $#table_names; $ti++) 62 { 63 my $table_name = $table_names[$ti]; 64 my $array_ref = $tables[$ti]; 65 66 # This may fail if we have no table so do not check answer 67 $sth = $dbh->do("drop table $table_name" . $server->{'drop_attr'}); 68 69 print "Creating table $table_name\n" if ($opt_verbose); 70 do_many($dbh,@$array_ref); 71 } 72 $end_time=new Benchmark; 73 print "Time for create_table (" . ($#tables+1) ."): " . 74 timestr(timediff($end_time, $loop_time),"all") . "\n\n"; 75 76 if ($opt_fast && defined($server->{vacuum})) 77 { 78 $server->vacuum(0,\$dbh); 79 } 80 81#### 82#### Insert data 83#### 84 85 print "Inserting data\n"; 86 87 $loop_time= new Benchmark; 88 $row_count=0; 89 $double_quotes=$server->{'double_quotes'}; 90 91 if ($opt_lock_tables) 92 { 93 @tmp=@table_names; push(@tmp,@extra_names); 94 print "LOCK TABLES @tmp\n" if ($opt_debug); 95 $sth = $dbh->do("LOCK TABLES " . join(" WRITE,", @tmp) . " WRITE") || 96 die $DBI::errstr; 97 } 98 99 if ($opt_fast && $server->{'limits'}->{'load_data_infile'}) 100 { 101 for ($ti = 0; $ti <= $#table_names; $ti++) 102 { 103 my $table_name = $table_names[$ti]; 104 my $file = "$pwd/Data/ATIS/${table_name}.txt"; 105 print "$table_name - $file\n" if ($opt_debug); 106 $row_count += $server->insert_file($table_name,$file,$dbh); 107 } 108 } 109 else 110 { 111 if ($opt_fast && $server->{transactions}) 112 { 113 $dbh->{AutoCommit} = 0; 114 print "Transactions enabled\n" if ($opt_debug); 115 } 116 117 for ($ti = 0; $ti <= $#table_names; $ti++) 118 { 119 my $table_name = $table_names[$ti]; 120 my $array_ref = $tables[$ti]; 121 my @table = @$array_ref; 122 my $insert_start = "insert into $table_name values ("; 123 124 open(DATA, "$pwd/Data/ATIS/${table_name}.txt") || die "Can't open text file: $pwd/Data/ATIS/${table_name}.txt\n"; 125 while(<DATA>) 126 { 127 chomp; 128 next unless ( $_ =~ /\w/ ); # skip blank lines 129 my $command = $insert_start . $_ . ")"; 130 $command = $server->fix_for_insert($command); 131 print "$command\n" if ($opt_debug); 132 $command =~ s/\\'/\'\'/g if ($double_quotes); 133 134 $sth = $dbh->do($command) or die "Got error: $DBI::errstr when executing '$command'\n"; 135 $row_count++; 136 } 137 } 138 if ($opt_fast && $server->{transactions}) 139 { 140 $dbh->commit; 141 $dbh->{AutoCommit} = 1; 142 } 143 close(DATA); 144 } 145 146 if ($opt_lock_tables) 147 { 148 $dbh->do("UNLOCK TABLES"); 149 } 150 $end_time=new Benchmark; 151 print "Time to insert ($row_count): " . 152 timestr(timediff($end_time, $loop_time),"all") . "\n\n"; 153} 154 155if ($opt_fast && defined($server->{vacuum})) 156{ 157 $server->vacuum(0,\$dbh,@table_names); 158} 159 160if ($opt_lock_tables) 161{ 162 @tmp=@table_names; push(@tmp,@extra_names); 163 $sth = $dbh->do("LOCK TABLES " . join(" READ,", @tmp) . " READ") || 164 die $DBI::errstr; 165} 166# 167# Now the fun begins. Let's do some simple queries on the result 168# 169# The query array is defined as: 170# query, number of rows in result, 0|1 where 1 means that the query is possible 171# 172 173print "Retrieving data\n"; 174@Q1=("select_simple_join", 175 "select city.city_name,state.state_name,city.city_code from city,state where city.city_code='MATL' and city.state_code=state.state_code",1,1, 176 "select city.city_name,state.state_name,city.city_code from state,city where city.state_code=state.state_code",11,1, 177 "select month_name.month_name,day_name.day_name from month_name,day_name where month_name.month_number=day_name.day_code",7,1, 178 "select month_name.month_name,day_name.day_name from month_name,day_name where month_name.month_number=day_name.day_code and day_name.day_code >= 4",4,1, 179 "select flight.flight_code,aircraft.aircraft_type from flight,aircraft where flight.aircraft_code=aircraft.aircraft_code",579,1, 180 ); 181 182@Q2=("select_join", 183 "select airline.airline_name,aircraft.aircraft_type from aircraft,airline,flight where flight.aircraft_code=aircraft.aircraft_code and flight.airline_code=airline.airline_code",579,1); 184 185@Q21=("select_key_prefix_join", 186 "select fare.fare_code from restrict_carrier,airline,fare where restrict_carrier.airline_code=airline.airline_code and fare.restrict_code=restrict_carrier.restrict_code",5692,1, 187 ); 188 189@Q3=("select_distinct", 190 "select distinct category from aircraft",6,1, 191 "select distinct from_airport from flight",9,1, 192 "select distinct aircraft_code from flight",22,1, 193 "select distinct * from fare",534,1, 194 "select distinct flight_code from flight_fare",579,1, 195 "select distinct flight.flight_code,aircraft.aircraft_type from flight,aircraft where flight.aircraft_code=aircraft.aircraft_code",579,1, 196 "select distinct airline.airline_name,aircraft.aircraft_type from aircraft,airline,flight where flight.aircraft_code=aircraft.aircraft_code and flight.airline_code=airline.airline_code",44,$limits->{'join_optimizer'}, 197 "select distinct airline.airline_name,aircraft.aircraft_type from flight,aircraft,airline where flight.aircraft_code=aircraft.aircraft_code and flight.airline_code=airline.airline_code",44,1, 198 ); 199 200@Q4=("select_group", 201 "select day_name.day_name,day_name.day_code,count(*) from flight_day,day_name where day_name.day_code=flight_day.day_code group by day_name.day_name,day_name.day_code order by day_name.day_code",7,$limits->{'group_functions'}, 202 "select day_name.day_name,count(*) from flight_day,day_name where day_name.day_code=flight_day.day_code group by day_name.day_name",7,$limits->{'group_functions'}, 203 "select month_name,day_name from month_name,day_name where month_number=day_code and day_code>3 group by month_name,day_name",4,$limits->{'group_functions'}, 204 "select day_name.day_name,flight_day.day_code,count(*) from flight_day,day_name where day_name.day_code=flight_day.day_code group by flight_day.day_code,day_name.day_name order by flight_day.day_code",7,$limits->{'group_functions'}, 205 "select sum(engines) from aircraft",1,$limits->{'group_functions'}, 206 "select avg(engines) from aircraft",1,$limits->{'group_functions'}, 207 "select avg(engines) from aircraft where engines>0",1,$limits->{'group_functions'}, 208 "select count(*),min(pay_load),max(pay_load) from aircraft where pay_load>0",1,$limits->{'group_functions'}, 209 "select min(flight_code),min(flight_code) from flight",1,$limits->{'group_functions'}, 210 "select min(from_airport),min(to_airport) from flight",1,$limits->{'group_functions'} && $limits->{'group_func_sql_min_str'}, 211 "select count(*) from aircraft where pay_load>10000",1,$limits->{'group_functions'}, 212 "select count(*) from aircraft where pay_load<>0",1,$limits->{'group_functions'}, 213 "select count(*) from flight where flight_code >= 112793",1,$limits->{'group_functions'}, 214 "select count(if(pay_load,1,NULL)) from aircraft",1,$limits->{'if'} && $limits->{'group_functions'}, 215 "select std(engines) from aircraft",1,$limits->{'group_func_extra_std'}, 216 "SELECT from_airport,to_airport,avg(time_elapsed) FROM flight WHERE from_airport='ATL' AND to_airport='BOS' group by from_airport,to_airport",1,$limits->{'group_functions'}, 217 "select city_code, avg(ground_fare) from ground_service where ground_fare<>0 group by city_code",11,$limits->{'group_functions'}, 218 "select count(*), ground_service.city_code from ground_service group by ground_service.city_code",12,$limits->{'group_functions'}, 219 "select category,count(*) as totalnr from aircraft where engines=2 group by category having totalnr>4",3,$limits->{'group_functions'} && $limits->{'having_with_alias'}, 220 "select category,count(*) from aircraft where engines=2 group by category having count(*)>4",3,$limits->{'group_functions'} && $limits->{'having_with_group'}, 221 "select flight_number,range_miles,fare_class FROM aircraft,flight,flight_class WHERE flight.flight_code=flight_class.flight_code AND flight.aircraft_code=aircraft.aircraft_code AND range_miles<>0 AND (stops=1 OR stops=2) GROUP BY flight_number,range_miles,fare_class",150,$limits->{'group_functions'}, 222 "select distinct from_airport.time_zone_code,to_airport.time_zone_code,(FLOOR(arrival_time/100)*60+MOD(arrival_time,100)-FLOOR(departure_time/100)*60-MOD(departure_time,100)-time_elapsed)/60 AS time_zone_diff FROM flight,airport AS from_airport,airport AS to_airport WHERE flight.from_airport=from_airport.airport_code AND flight.to_airport=to_airport.airport_code GROUP BY from_airport.time_zone_code,to_airport.time_zone_code,arrival_time,departure_time,time_elapsed",21,$limits->{'func_odbc_mod'} && $limits->{'func_odbc_floor'} && $limits->{'group_functions'}, 223 "select DISTINCT from_airport.time_zone_code,to_airport.time_zone_code,MOD((FLOOR(arrival_time/100)*60+MOD(arrival_time,100)-FLOOR(departure_time/100)*60-MOD(departure_time,100)-time_elapsed)/60+36,24)-12 AS time_zone_diff FROM flight,airport AS from_airport,airport AS to_airport WHERE flight.from_airport=from_airport.airport_code AND flight.to_airport=to_airport.airport_code and MOD((FLOOR(arrival_time/100)*60+MOD(arrival_time,100)-FLOOR(departure_time/100)*60-MOD(departure_time,100)-time_elapsed)/60+36,24)-12 < 10",14,$limits->{'func_odbc_mod'} && $limits->{'func_odbc_floor'} && $limits->{'group_functions'}, 224 "select from_airport,to_airport,range_miles,time_elapsed FROM aircraft,flight WHERE aircraft.aircraft_code=flight.aircraft_code AND to_airport NOT LIKE from_airport AND range_miles<>0 AND time_elapsed<>0 GROUP BY from_airport,to_airport,range_miles,time_elapsed",409,$limits->{'group_functions'} && $limits->{'like_with_column'}, 225 "SELECT airport.country_name,state.state_name,city.city_name,airport_service.direction FROM airport_service,state,airport,city WHERE airport_service.city_code=city.city_code AND airport_service.airport_code=airport.airport_code AND state.state_code=airport.state_code AND state.state_code=city.state_code AND airport.state_code=city.state_code AND airport.country_name=city.country_name AND airport.country_name=state.country_name AND city.time_zone_code=airport.time_zone_code GROUP BY airport.country_name,state.state_name,city.city_name,airport_service.direction ORDER BY state_name",11,$limits->{'group_functions'}, 226 "SELECT airport.country_name,state.state_name,city.city_name,airport_service.direction FROM airport_service,state,airport,city WHERE airport_service.city_code=city.city_code AND airport_service.airport_code=airport.airport_code AND state.state_code=airport.state_code AND state.state_code=city.state_code AND airport.state_code=city.state_code AND airport.country_name=city.country_name AND airport.country_name=state.country_name AND city.time_zone_code=airport.time_zone_code GROUP BY airport.country_name,state.state_name,city.city_name,airport_service.direction ORDER BY state_name DESC",11,$limits->{'group_functions'}, 227 "SELECT airport.country_name,state.state_name,city.city_name,airport_service.direction FROM airport_service,state,airport,city WHERE airport_service.city_code=city.city_code AND airport_service.airport_code=airport.airport_code AND state.state_code=airport.state_code AND state.state_code=city.state_code AND airport.state_code=city.state_code AND airport.country_name=city.country_name AND airport.country_name=state.country_name AND city.time_zone_code=airport.time_zone_code GROUP BY airport.country_name,state.state_name,city.city_name,airport_service.direction ORDER BY state_name",11,$limits->{'group_functions'}, 228 "SELECT from_airport,to_airport,fare.fare_class,night,one_way_cost,rnd_trip_cost,class_days FROM compound_class,fare WHERE compound_class.fare_class=fare.fare_class AND one_way_cost <= 825 AND one_way_cost >= 280 AND from_airport='SFO' AND to_airport='DFW' GROUP BY from_airport,to_airport,fare.fare_class,night,one_way_cost,rnd_trip_cost,class_days ORDER BY one_way_cost",10,$limits->{'group_functions'}, 229 "select engines,category,cruising_speed,from_airport,to_airport FROM aircraft,flight WHERE category='JET' AND engines >= 1 AND aircraft.aircraft_code=flight.aircraft_code AND to_airport NOT LIKE from_airport AND stops>0 GROUP BY engines,category,cruising_speed,from_airport,to_airport ORDER BY engines DESC",29,$limits->{'group_functions'} && $limits->{'like_with_column'}, 230 ); 231 232@Q=(\@Q1,\@Q2,\@Q21,\@Q3,\@Q4); 233 234 235foreach $Q (@Q) 236{ 237 $count=$estimated=0; 238 $loop_time= new Benchmark; 239 for ($i=1 ; $i <= $opt_loop_count; $i++) 240 { 241 for ($j=1 ; $j < $#$Q ; $j+=3) 242 { 243 if ($Q->[$j+2]) 244 { # We can do it with current limits 245 $count++; 246 if ($i == 100) # Do something different 247 { 248 if (($row_count=fetch_all_rows($dbh,$server->query($Q->[$j]))) != 249 $Q->[$j+1]) 250 { 251 if ($row_count == undef()) 252 { 253 die "Got error: $DBI::errstr when executing " . $Q->[$j] ."\n"."got $row_count instead of $Q->[$j+1] *** \n"; 254 } 255 print "Warning: Query '" . $Q->[$j] . "' returned $row_count rows when it should have returned " . $Q->[$j+1] . " rows\n"; 256 } 257 } 258 else 259 { 260 defined(fetch_all_rows($dbh,$server->query($Q->[$j]))) 261 or die "ERROR: $DBI::errstr executing '$Q->[$j]'\n"; 262 } 263 } 264 } 265 $end_time=new Benchmark; 266 last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i, 267 $opt_loop_count)); 268 print "Loop $i\n" if ($opt_verbose); 269 } 270 if ($count) 271 { 272 if ($estimated) 273 { print "Estimated time"; } 274 else 275 { print "Time"; } 276 print " for " . $Q->[0] . " ($count): " . 277 timestr(timediff($end_time, $loop_time),"all") . "\n"; 278 } 279} 280 281print "\n"; 282 283#### 284#### Delete the tables 285#### 286 287if (!$opt_skip_delete) # Only used when testing 288{ 289 print "Removing tables\n"; 290 $loop_time= new Benchmark; 291 if ($opt_lock_tables) 292 { 293 $sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr; 294 } 295 for ($ti = 0; $ti <= $#table_names; $ti++) 296 { 297 my $table_name = $table_names[$ti]; 298 $sth = $dbh->do("drop table $table_name" . $server->{'drop_attr'}); 299 } 300 301 $end_time=new Benchmark; 302 print "Time to drop_table (" .($#table_names+1) . "): " . 303 timestr(timediff($end_time, $loop_time),"all") . "\n"; 304} 305 306if ($opt_fast && defined($server->{vacuum})) 307{ 308 $server->vacuum(0,\$dbh); 309} 310 311#### 312#### End of benchmark 313#### 314 315$dbh->disconnect; # close connection 316 317end_benchmark($start_time); 318 319 320sub init_data 321{ 322 @aircraft= 323 $server->create("aircraft", 324 ["aircraft_code char(3) NOT NULL", 325 "aircraft_type char(64) NOT NULL", 326 "engines tinyint(1) NOT NULL", 327 "category char(10) NOT NULL", 328 "wide_body char(3) NOT NULL", 329 "wing_span float(6,2) NOT NULL", 330 "length1 float(6,2) NOT NULL", 331 "weight integer(7) NOT NULL", 332 "capacity smallint(3) NOT NULL", 333 "pay_load integer(7) NOT NULL", 334 "cruising_speed mediumint(5) NOT NULL", 335 "range_miles mediumint(5) NOT NULL", 336 "pressurized char(3) NOT NULL"], 337 ["PRIMARY KEY (aircraft_code)"]); 338 @airline= 339 $server->create("airline", 340 ["airline_code char(2) NOT NULL", 341 "airline_name char(64) NOT NULL", 342 "notes char(38) NOT NULL"], 343 ["PRIMARY KEY (airline_code)"]); 344 @airport= 345 $server->create("airport", 346 ["airport_code char(3) NOT NULL", 347 "airport_name char(40) NOT NULL", 348 "location char(36) NOT NULL", 349 "state_code char(2) NOT NULL", 350 "country_name char(25) NOT NULL", 351 "time_zone_code char(3) NOT NULL"], 352 ["PRIMARY KEY (airport_code)"]); 353 @airport_service= 354 $server->create("airport_service", 355 ["city_code char(4) NOT NULL", 356 "airport_code char(3) NOT NULL", 357 "miles_distant float(4,1) NOT NULL", 358 "direction char(3) NOT NULL", 359 "minutes_distant smallint(3) NOT NULL"], 360 ["PRIMARY KEY (city_code, airport_code)"]); 361 @city= 362 $server->create("city", 363 ["city_code char(4) NOT NULL", 364 "city_name char(25) NOT NULL", 365 "state_code char(2) NOT NULL", 366 "country_name char(25) NOT NULL", 367 "time_zone_code char(3) NOT NULL"], 368 ["PRIMARY KEY (city_code)"]); 369 @class_of_service= 370 $server->create("class_of_service", 371 ["class_code char(2) NOT NULL", 372 "rank tinyint(2) NOT NULL", 373 "class_description char(80) NOT NULL"], 374 ["PRIMARY KEY (class_code)"]); 375 @code_description= 376 $server->create("code_description", 377 ["code char(5) NOT NULL", 378 "description char(110) NOT NULL"], 379 ["PRIMARY KEY (code)"]); 380 @compound_class= 381 $server->create("compound_class", 382 ["fare_class char(3) NOT NULL", 383 "base_class char(2) NOT NULL", 384 "class_type char(10) NOT NULL", 385 "premium char(3) NOT NULL", 386 "economy char(3) NOT NULL", 387 "discounted char(3) NOT NULL", 388 "night char(3) NOT NULL", 389 "season_fare char(4) NOT NULL", 390 "class_days char(7) NOT NULL"], 391 ["PRIMARY KEY (fare_class)"]); 392 @connect_leg= 393 $server->create("connect_leg", 394 ["connect_code integer(8) NOT NULL", 395 "leg_number tinyint(1) NOT NULL", 396 "flight_code integer(8) NOT NULL"], 397 ["PRIMARY KEY (connect_code, leg_number, flight_code)"]); 398 @connection= 399 $server->create("fconnection", 400 ["connect_code integer(8) NOT NULL", 401 "from_airport char(3) NOT NULL", 402 "to_airport char(3) NOT NULL", 403 "departure_time smallint(4) NOT NULL", 404 "arrival_time smallint(4) NOT NULL", 405 "flight_days char(7) NOT NULL", 406 "stops tinyint(1) NOT NULL", 407 "connections tinyint(1) NOT NULL", 408 "time_elapsed smallint(4) NOT NULL"], 409 ["PRIMARY KEY (connect_code)", 410 "INDEX from_airport1 (from_airport)", 411 "INDEX to_airport1 (to_airport)"]); 412 @day_name= 413 $server->create("day_name", 414 ["day_code tinyint(1) NOT NULL", 415 "day_name char(9) NOT NULL"], 416 ["PRIMARY KEY (day_code)"]); 417 @dual_carrier= 418 $server->create("dual_carrier", 419 ["main_airline char(2) NOT NULL", 420 "dual_airline char(2) NOT NULL", 421 "low_flight smallint(4) NOT NULL", 422 "high_flight smallint(4) NOT NULL", 423 "fconnection_name char(64) NOT NULL"], 424 ["PRIMARY KEY (main_airline, dual_airline, low_flight)", 425 "INDEX main_airline1 (main_airline)"]); 426 427 @fare= 428 $server->create("fare", 429 ["fare_code char(8) NOT NULL", 430 "from_airport char(3) NOT NULL", 431 "to_airport char(3) NOT NULL", 432 "fare_class char(3) NOT NULL", 433 "fare_airline char(2) NOT NULL", 434 "restrict_code char(5) NOT NULL", 435 "one_way_cost float(7,2) NOT NULL", 436 "rnd_trip_cost float(8,2) NOT NULL"], 437 ["PRIMARY KEY (fare_code)", 438 "INDEX from_airport2 (from_airport)", 439 "INDEX to_airport2 (to_airport)"]); 440 @flight= 441 $server->create("flight", 442 ["flight_code integer(8) NOT NULL", 443 "flight_days char(7) NOT NULL", 444 "from_airport char(3) NOT NULL", 445 "to_airport char(3) NOT NULL", 446 "departure_time smallint(4) NOT NULL", 447 "arrival_time smallint(4) NOT NULL", 448 "airline_code char(2) NOT NULL", 449 "flight_number smallint(4) NOT NULL", 450 "class_string char(8) NOT NULL", 451 "aircraft_code char(3) NOT NULL", 452 "meal_code char(7) NOT NULL", 453 "stops tinyint(1) NOT NULL", 454 "dual_carrier char(1) NOT NULL", 455 "time_elapsed smallint(4) NOT NULL"], 456 ["PRIMARY KEY (flight_code)", 457 "INDEX from_airport3 (from_airport)", 458 "INDEX to_airport3 (to_airport)"]); 459 @flight_class= 460 $server->create("flight_class", 461 ["flight_code integer(8) NOT NULL", 462 "fare_class char(3) NOT NULL"], 463 ["PRIMARY KEY (flight_code, fare_class)"]); 464 @flight_day= 465 $server->create("flight_day", 466 ["day_mask char(7) NOT NULL", 467 "day_code tinyint(1) NOT NULL", 468 "day_name char(9) NOT NULL"], 469 ["PRIMARY KEY (day_mask, day_code)"]); 470 @flight_fare= 471 $server->create("flight_fare", 472 ["flight_code integer(8) NOT NULL", 473 "fare_code char(8) NOT NULL"], 474 ["PRIMARY KEY (flight_code, fare_code)"]); 475 @food_service= 476 $server->create("food_service", 477 ["meal_code char(4) NOT NULL", 478 "meal_number tinyint(1) NOT NULL", 479 "meal_class char(10) NOT NULL", 480 "meal_description char(10) NOT NULL"], 481 ["PRIMARY KEY (meal_code, meal_number, meal_class)"]); 482 @ground_service= 483 $server->create("ground_service", 484 ["city_code char(4) NOT NULL", 485 "airport_code char(3) NOT NULL", 486 "transport_code char(1) NOT NULL", 487 "ground_fare float(6,2) NOT NULL"], 488 ["PRIMARY KEY (city_code, airport_code, transport_code)"]); 489 @time_interval= 490 $server->create("time_interval", 491 ["period char(20) NOT NULL", 492 "begin_time smallint(4) NOT NULL", 493 "end_time smallint(4) NOT NULL"], 494 ["PRIMARY KEY (period, begin_time)"]); 495 @month_name= 496 $server->create("month_name", 497 ["month_number tinyint(2) NOT NULL", 498 "month_name char(9) NOT NULL"], 499 ["PRIMARY KEY (month_number)"]); 500 @restrict_carrier= 501 $server->create("restrict_carrier", 502 ["restrict_code char(5) NOT NULL", 503 "airline_code char(2) NOT NULL"], 504 ["PRIMARY KEY (restrict_code, airline_code)"]); 505 @restrict_class= 506 $server->create("restrict_class", 507 ["restrict_code char(5) NOT NULL", 508 "ex_fare_class char(12) NOT NULL"], 509 ["PRIMARY KEY (restrict_code, ex_fare_class)"]); 510 @restriction= 511 $server->create("restriction", 512 ["restrict_code char(5) NOT NULL", 513 "application char(80) NOT NULL", 514 "no_discounts char(80) NOT NULL", 515 "reserve_ticket smallint(3) NOT NULL", 516 "stopovers char(1) NOT NULL", 517 "return_min smallint(3) NOT NULL", 518 "return_max smallint(3) NOT NULL"], 519 ["PRIMARY KEY (restrict_code)"]); 520 @state= 521 $server->create("state", 522 ["state_code char(2) NOT NULL", 523 "state_name char(25) NOT NULL", 524 "country_name char(25) NOT NULL"], 525 ["PRIMARY KEY (state_code)"]); 526 @stop= 527 $server->create("stop1", 528 ["flight_code integer(8) NOT NULL", 529 "stop_number tinyint(1) NOT NULL", 530 "stop_flight integer(8) NOT NULL"], 531 ["PRIMARY KEY (flight_code, stop_number)"]); 532 @time_zone= 533 $server->create("time_zone", 534 ["time_zone_code char(3) NOT NULL", 535 "time_zone_name char(32) NOT NULL"], 536 ["PRIMARY KEY (time_zone_code, time_zone_name)"]); 537 @transport= 538 $server->create("transport", 539 ["transport_code char(1) NOT NULL", 540 "transport_desc char(32) NOT NULL"], 541 ["PRIMARY KEY (transport_code)"]); 542 543# Avoid not used warnings 544 545 @tables = 546 (\@aircraft, \@airline, \@airport, \@airport_service, 547 \@city, \@class_of_service, \@code_description, 548 \@compound_class, \@connect_leg, \@connection, \@day_name, 549 \@dual_carrier, \@fare, \@flight, \@flight_class, \@flight_day, 550 \@flight_fare, \@food_service, \@ground_service, \@time_interval, 551 \@month_name, 552 \@restrict_carrier, \@restrict_class, \@restriction, \@state, \@stop, 553 \@time_zone, \@transport); 554 555 @table_names = 556 ("aircraft", "airline", "airport", "airport_service", 557 "city", "class_of_service", "code_description", 558 "compound_class", "connect_leg", "fconnection", "day_name", 559 "dual_carrier", "fare", "flight", "flight_class", "flight_day", 560 "flight_fare", "food_service", "ground_service", "time_interval", 561 "month_name", 562 "restrict_carrier", "restrict_class", "restriction", "state", "stop1", 563 "time_zone", "transport"); 564 565# Alias used in joins 566 @extra_names=("airport as from_airport","airport as to_airport"); 567} 568