コード例 #1
0
ファイル: tscdb.c プロジェクト: tinnerdxp/openrail
static void process_create_schedule(const char * string, const jsmntok_t * tokens)
{
   char zs[128];
   char query[2048];
   char uid[16], stp_indicator[2], tsc[16];
   MYSQL_RES * result0;
   MYSQL_ROW row0;

   stats[ScheduleCreate]++;

   EXTRACT("CIF_stp_indicator", stp_indicator);
   if(stp_indicator[0] == 'C') return;
   EXTRACT("CIF_train_uid", uid);
   EXTRACT("schedule_start_date", zs);
   time_t start_date = parse_datestamp(zs);
   EXTRACT("schedule_end_date", zs);
   time_t end_date   = parse_datestamp(zs);
   EXTRACT("CIF_train_service_code", tsc);

   sprintf(query, "SELECT id FROM cif_schedules WHERE CIF_stp_indicator = '%s' AND CIF_train_uid = '%s' AND schedule_start_date = %ld AND schedule_end_date = %ld AND CIF_train_service_code = '' AND update_id = %s", stp_indicator, uid, start_date, end_date, last_update_id);

   if(!db_query(query))
   {
      result0 = db_store_result();
      if((row0 = mysql_fetch_row(result0)))
      {
         sprintf(query, "UPDATE cif_schedules SET CIF_train_service_code = '%s' WHERE id = %s", tsc, row0[0]);
         db_query(query);
         stats[TSCUpdate]++;
         _log(GENERAL, "Updated schedule %s (%s %s) with TSC \"%s\".", row0[0], uid, stp_indicator, tsc);
      }
      mysql_free_result(result0);
   }        
}
コード例 #2
0
ファイル: database.c プロジェクト: tinnerdxp/openrail
static word table_exists(const char * const table_like)
{
   MYSQL_RES * result;
   char query[256];
   sprintf(query, "SHOW TABLES LIKE '%s'", table_like);
   if(!db_query(query))
   {
      result = db_store_result();
      word num_rows = mysql_num_rows(result);
      mysql_free_result(result);
      if(num_rows) return true;
   }
   return false;
}
コード例 #3
0
static void process_update_schedule(const char * string, const jsmntok_t * tokens)
{
   char query[1024], CIF_train_uid[16], schedule_start_date[16], schedule_end_date[16], CIF_stp_indicator[8]; 
   dword id;
   MYSQL_RES * result0;
   MYSQL_ROW row0;

   EXTRACT("CIF_train_uid", CIF_train_uid);
   EXTRACT("schedule_start_date", schedule_start_date);
   EXTRACT("schedule_end_date", schedule_end_date);
   EXTRACT("CIF_stp_indicator", CIF_stp_indicator);

   time_t schedule_start_date_stamp = parse_datestamp(schedule_start_date);
   // time_t schedule_end_date_stamp   = parse_datestamp(schedule_end_date);

   sprintf(query, "SELECT id FROM cif_schedules WHERE update_id = 0 AND CIF_train_uid = '%s' AND CIF_stp_indicator = '%s' AND schedule_start_date = %ld AND deleted > %ld", CIF_train_uid, CIF_stp_indicator, schedule_start_date_stamp, time(NULL));
   if(!db_query(query))
   {
      result0 = db_store_result();
      word num_rows = mysql_num_rows(result0);
      if(num_rows != 1)
      {
         _log(MAJOR, "Update for schedule \"%s\" found %d existing records.  Delete phase skipped.", CIF_train_uid, num_rows);
         jsmn_dump_tokens(string, tokens, 0);
         if(num_rows) stats[UpdateDeleteMulti]++; else stats[UpdateDeleteMiss]++;
      }
      else
      {
         row0 = mysql_fetch_row(result0);
         id = atol(row0[0]);
         //sprintf(query, "DELETE FROM cif_schedule_locations WHERE cif_schedule_id = %ld", id);
         //db_query(query);
         sprintf(query, "UPDATE cif_schedules SET deleted = %ld WHERE id = %ld", time(NULL), id);
         db_query(query);
      }
      mysql_free_result(result0);
   }

   // Create phase.
   process_create_schedule(string, tokens, true);

   _log(DEBUG, "Updated schedule \"%s\".", CIF_train_uid);
}
コード例 #4
0
static char * tiploc_name(const char * const tiploc)
{
   // Not re-entrant
   char query[256];
   MYSQL_RES * result0;
   MYSQL_ROW row0;
   static char result[128];

   sprintf(query, "select fn from corpus where tiploc = '%s'", tiploc);
   db_query(query);
   result0 = db_store_result();
   if((row0 = mysql_fetch_row(result0)) && row0[0][0]) 
   {
      strncpy(result, row0[0], 127);
      result[127] = '\0';
   }
   else
   {
      strcpy(result, tiploc);
   }
   mysql_free_result(result0);

   return result;
}
コード例 #5
0
static void process_create_schedule(const char * string, const jsmntok_t * tokens, const word update)
{
   // update true indicates this is as the result of a VSTP update.
   char zs[128], zs1[128];
   char query[2048];
   word i;
   char uid[16], stp_indicator[2];
   char signalling_id[8];

   if(debug) jsmn_dump_tokens(string, tokens, 0);

   time_t now = time(NULL);
   sprintf(query, "INSERT INTO cif_schedules VALUES(0, %ld, %lu", now, NOT_DELETED); // update_id == 0 => VSTP

   EXTRACT_APPEND_SQL("CIF_bank_holiday_running");

   //EXTRACT_APPEND_SQL("CIF_stp_indicator");
   EXTRACT("CIF_stp_indicator", stp_indicator);
   sprintf(zs1, ", '%s'", stp_indicator); strcat(query, zs1); 

   //EXTRACT_APPEND_SQL("CIF_train_uid");
   EXTRACT("CIF_train_uid", uid);
   sprintf(zs1, ", '%s'", uid); strcat(query, zs1); 

   EXTRACT_APPEND_SQL("applicable_timetable");

   EXTRACT_APPEND_SQL("atoc_code");
   // EXTRACT_APPEND_SQL("traction_class");
   EXTRACT_APPEND_SQL("uic_code");
   EXTRACT("schedule_days_runs", zs);
   for(i=0; i<7; i++)
   {
      strcat(query, ", ");
      strcat(query, (zs[i]=='1')?"1":"0");
   }

   EXTRACT("schedule_end_date", zs);
   time_t z = parse_datestamp(zs);
   sprintf(zs1, ", %ld", z);
   strcat(query, zs1);

   EXTRACT("signalling_id", signalling_id);
   sprintf(zs1, ", '%s'", signalling_id); strcat(query, zs1);

   EXTRACT_APPEND_SQL("CIF_train_category");
   EXTRACT_APPEND_SQL("CIF_headcode");
   //EXTRACT_APPEND_SQL("CIF_course_indicator");
   EXTRACT_APPEND_SQL("CIF_train_service_code");
   EXTRACT_APPEND_SQL("CIF_business_sector");
   EXTRACT_APPEND_SQL("CIF_power_type");
   EXTRACT_APPEND_SQL("CIF_timing_load");
   EXTRACT_APPEND_SQL("CIF_speed");
   EXTRACT_APPEND_SQL("CIF_operating_characteristics");
   EXTRACT_APPEND_SQL("CIF_train_class");

   EXTRACT_APPEND_SQL("CIF_sleepers");

   EXTRACT_APPEND_SQL("CIF_reservations");
   EXTRACT_APPEND_SQL("CIF_connection_indicator");
   EXTRACT_APPEND_SQL("CIF_catering_code");
   EXTRACT_APPEND_SQL("CIF_service_branding");
   
   EXTRACT("schedule_start_date", zs);
   z = parse_datestamp(zs);
   sprintf(zs1, ", %ld", z);
   strcat(query, zs1);

   EXTRACT_APPEND_SQL("train_status");

   strcat(query, ", 0, '', '')"); // id filled by MySQL

   if(!db_query(query))
   {
      stats[update?UpdateCreate:Create]++;
   }
      
   dword id = db_insert_id();

   word index = jsmn_find_name_token(string, tokens, 0, "schedule_location");
   word locations = tokens[index+1].size;

   huyton_flag = false;
   index += 2;
   for(i = 0; i < locations; i++)
   {
      index = process_create_schedule_location(string, tokens, index, id);
   }

   if(stp_indicator[0] == 'O' && (signalling_id[0] == '\0' || signalling_id[0] == ' '))
   {
      // Search db for schedules with a deduced headcode, and add it to this one, status = D
      // Bug:  Really this should also look for schedules with a signalling_id
      MYSQL_RES * result;
      MYSQL_ROW row;
      sprintf(query, "SELECT deduced_headcode FROM cif_schedules WHERE CIF_train_uid = '%s' AND deduced_headcode != '' AND schedule_end_date > %ld ORDER BY created DESC", uid, now - (64L * 24L * 60L * 60L));
      if(!db_query(query))
      {
         result = db_store_result();
         if((row = mysql_fetch_row(result)))
         {
            sprintf(query, "UPDATE cif_schedules SET deduced_headcode = '%s', deduced_headcode_status = 'D' WHERE id = %ld", row[0], id);
            db_query(query);
            _log(DEBUG, "Deduced headcode \"%s\" applied to overlay schedule %ld, uid \"%s\".", row[0], id, uid);
            stats[HeadcodeDeduced]++;
         }
         else
         {
            _log(DEBUG, "Deduced headcode not found for overlay schedule %ld, uid \"%s\".", id, uid);
         }
         mysql_free_result(result);
      }
   }

   sprintf(query, "UPDATE status SET last_vstp_processed = %ld", now);
   db_query(query);

   if(huyton_flag) 
   {
      _log(DEBUG, "Created schedule %ld%s.  +++ Passes Huyton +++", id, update?" as a result of an Update transaction":"");
   }

   if(huyton_flag)
   {
      char title[64], message[512];
      MYSQL_RES * result0;
      MYSQL_ROW row0;
      char stp[4];
      sprintf(title, "Huyton Schedule Created.");
      sprintf(message, "Created schedule which passes Huyton.");
      if(update) strcat(message, "  Due to a VSTP Update transaction.");
      strcat(message, "\n\n");
      EXTRACT("CIF_train_uid", zs1);
      EXTRACT("CIF_stp_indicator", stp);
      sprintf(zs, "%ld (%s %s) ", id, zs1, stp);
      EXTRACT("signalling_id", zs1);
      strcat(zs, zs1);
      sprintf(query, "SELECT tiploc_code, departure FROM cif_schedule_locations WHERE record_identity = 'LO' AND cif_schedule_id = %ld", id);
      if(!db_query(query))
      {
         result0 = db_store_result();
         if((row0 = mysql_fetch_row(result0)))
         {
            sprintf(zs1, " %s %s to ", show_time_text(row0[1]), tiploc_name(row0[0]));
            strcat(zs, zs1);
         }
         mysql_free_result(result0);
      }
      sprintf(query, "SELECT tiploc_code FROM cif_schedule_locations WHERE record_identity = 'LT' AND cif_schedule_id = %ld", id);
      if(!db_query(query))
      {
         result0 = db_store_result();
         if((row0 = mysql_fetch_row(result0)))
         {
            strcat (zs, tiploc_name(row0[0]));
         }
         mysql_free_result(result0);
      }

      strcat(message, zs);

      sprintf(query, "SELECT schedule_start_date, schedule_end_date, CIF_stp_indicator FROM cif_schedules WHERE id = %ld", id);
      if(!db_query(query))
      {
         result0 = db_store_result();
         if((row0 = mysql_fetch_row(result0)))
         {
            dword from = atol(row0[0]);
            dword to   = atol(row0[1]);
            if(from == to)
            {
               strcat(message, "  Runs on ");
               strcat(message, date_text(from, true));
            }
            else
            {
               strcat(message, "  Runs from ");
               strcat(message, date_text(from, true));
               strcat(message, " to ");
               strcat(message, date_text(to,   true));
            }
            if(row0[2][0] == 'C') strcat(message, "  CANCELLED");
            strcat(message, "\n");
         }
         mysql_free_result(result0);
      }
      sprintf(query, "SELECT departure, arrival, pass, tiploc_code FROM cif_schedule_locations WHERE (tiploc_code = 'HUYTON' OR tiploc_code = 'HUYTJUN') AND cif_schedule_id = %ld", id);
      if(!db_query(query))
      {
         result0 = db_store_result();
         while((row0 = mysql_fetch_row(result0)))
         {
            char where[32], z[128];
            if(row0[3][4] == 'J') strcpy(where, "Huyton Junction"); else strcpy(where, "Huyton"); 
            if(row0[0][0]) 
            {
               sprintf(z, "Depart %s at %s.\n", where, row0[0]);
               strcat(message, z);
            }
            else if(row0[1][0])
            {
               sprintf(z, "Arrive %s at %s.\n", where, row0[1]); 
               strcat(message, z);
            }
            else if(row0[2][0])
            {
               sprintf(z, "Pass %s at %s.\n", where, row0[2]);
               strcat(message, z);
            }
         }
         mysql_free_result(result0);
      }
      email_alert(NAME, BUILD, title, message);
   }
}
コード例 #6
0
static void process_delete_schedule(const char * string, const jsmntok_t * tokens)
{
   char query[1024], CIF_train_uid[16], schedule_start_date[16], schedule_end_date[16], CIF_stp_indicator[8]; 
   dword id;
   word update_id;
   MYSQL_RES * result0;
   MYSQL_ROW row0;

   word deleted = 0;

   EXTRACT("CIF_train_uid", CIF_train_uid);
   EXTRACT("schedule_start_date", schedule_start_date);
   EXTRACT("schedule_end_date", schedule_end_date);
   EXTRACT("CIF_stp_indicator", CIF_stp_indicator);

   time_t schedule_start_date_stamp = parse_datestamp(schedule_start_date);
   time_t schedule_end_date_stamp   = parse_datestamp(schedule_end_date);

   // Find the id
   sprintf(query, "SELECT id, update_id FROM cif_schedules where CIF_train_uid = '%s' and schedule_start_date = '%ld' and schedule_end_date = %ld and CIF_stp_indicator = '%s' AND update_id = 0 AND deleted > %ld",
           CIF_train_uid, schedule_start_date_stamp, schedule_end_date_stamp, CIF_stp_indicator, time(NULL));

   // DO WE NEED DAYS RUNS AS WELL????
   // Note:  Only find VSTP ones.

   if (db_query(query))
   {
      db_disconnect();
      return;
   }

   result0 = db_store_result();
   word num_rows = mysql_num_rows(result0);

   if(num_rows > 1)
   {
      char zs[256];
      sprintf(zs, "Delete schedule found %d matches.", num_rows);
      _log(MAJOR, zs);
      jsmn_dump_tokens(string, tokens, 0);
      stats[DeleteMulti]++;
      // Bodge!
      query[7] = '*'; query[8] = ' ';
      dump_mysql_result_query(query);
   }
 
   while((row0 = mysql_fetch_row(result0)) && row0[0]) 
   {
      id = atol(row0[0]);
      update_id = atoi(row0[1]);

      //sprintf(query, "DELETE FROM cif_schedule_locations WHERE cif_schedule_id = %ld", id);
      //if(!db_query(query))
      //{
      //}

      sprintf(query, "UPDATE cif_schedules SET deleted = %ld where id = %ld", time(NULL), id);
   
      if(!db_query(query))
      {
         deleted++;
         if(update_id)
         {
            // Can never happen!
            _log(MAJOR, "Deleted non-VSTP schedule %ld.", id);
         }
         else
         {
            _log(DEBUG, "Deleted VSTP schedule %ld \"%s\".", id, CIF_train_uid);
         }
      }
   }
   mysql_free_result(result0);

   if(deleted) 
   {
      stats[DeleteHit]++;
   }
   else
   {
      _log(MAJOR, "Delete schedule miss.");
      jsmn_dump_tokens(string, tokens, 0);
      stats[DeleteMiss]++;
   }
}
コード例 #7
0
ファイル: tscdb.c プロジェクト: tinnerdxp/openrail
int main(int argc, char **argv)
{
   char config_file_path[256];
   opt_filename = NULL;
   opt_url = NULL;
   fetch_all = false;
   test_mode = false;
   verbose = false;
   opt_insecure = false;
   used_insecure = false;

   strcpy(config_file_path, "/etc/openrail.conf");
   word usage = false;
   int c;
   while ((c = getopt (argc, argv, ":c:u:f:tpih")) != -1)
      switch (c)
      {
      case 'c':
         strcpy(config_file_path, optarg);
         break;
      case 'u':
         if(!opt_filename) opt_url = optarg;
         break;
      case 'f':
         if(!opt_url) opt_filename = optarg;
         break;
      case 'a':
         fetch_all = true;
         break;
      case 't':
         test_mode = true;
         break;
      case 'p':
         verbose = true;
         break;
      case 'i':
         opt_insecure = true;
         break;
      case 'h':
         usage = true;
         break;
      case ':':
         break;
      case '?':
      default:
         usage = true;
      break;
      }

   char * config_fail;
   if((config_fail = load_config(config_file_path)))
   {
      printf("Failed to read config file \"%s\":  %s\n", config_file_path, config_fail);
      usage = true;
   }

   if(usage) 
   {
      printf("%s %s  Usage: %s [-c /path/to/config/file.conf] [-u <url> | -f <path> | -a] [-t | -r] [-p][-i]\n", NAME, BUILD, argv[0]);
      printf(
             "-c <file>  Path to config file.\n"
             "Data source:\n"
             "default    Fetch latest update.\n"
             "-u <url>   Fetch from specified URL.\n"
             "-f <file>  Use specified file.  (Must already be decompressed.)\n"
             "Actions:\n"
             "default    Apply data to database.\n"
             "-t         Report datestamp on download or file, do not apply to database.\n"
             "Options:\n"
             "-i         Insecure.  Circumvent certificate checks if necessary.\n"
             "-p         Print activity as well as logging.\n"
             );
      exit(1);
   }

   char zs[1024];

   start_time = time(NULL);

   debug = *conf[conf_debug];
  
   _log_init(debug?"/tmp/tscdb.log":"/var/log/garner/tscdb.log", (debug?1:(verbose?4:0)));
   
   _log(GENERAL, "");
   _log(GENERAL, "%s %s", NAME, BUILD);
   
   // Enable core dumps
   struct rlimit limit;
   if(!getrlimit(RLIMIT_CORE, &limit))
   {
      limit.rlim_cur = RLIM_INFINITY;
      setrlimit(RLIMIT_CORE, &limit);
   }
   
   int i;
   for(i = 0; i < MATCHES; i++)
   {
      if(regcomp(&match[i], match_strings[i], REG_ICASE + REG_EXTENDED))
      {
         sprintf(zs, "Failed to compile regex match %d", i);
         _log(MAJOR, zs);
      }
   }
   
   // Initialise database
   if(db_init(conf[conf_db_server], conf[conf_db_user], conf[conf_db_password], conf[conf_db_name])) exit(1);

   {
      word e;
      if((e=database_upgrade(cifdb)))
      {
         _log(CRITICAL, "Error %d in upgrade_database().  Aborting.", e);
         exit(1);
      }
   }

   run = 1;
   tiploc_ignored = false;

   // Zero the stats
   {
      word i;
      for(i = 0; i < MAXStats; i++) { stats[i] = 0; }
   }

   if(fetch_file())
   {
      if(opt_url || opt_filename)
      {
         _log(GENERAL, "Failed to find data.");
         exit(1);
      }
      {
         char report[256];
         _log(GENERAL, "Failed to fetch file.");
         
         sprintf(report, "Failed to collect timetable update after %lld attempts.", stats[Fetches]);
         email_alert(NAME, BUILD, "Timetable Update Failure Report", report);
      }
      exit(1);
   }

   char in_q = 0;
   char b_depth = 0;

   size_t ibuf = 0;
   size_t iobj = 0;
   size_t buf_end;

   // Determine applicable update
   {
      MYSQL_RES * result0;
      MYSQL_ROW row0;
      last_update_id[0] = '\0';
      if(!db_query("SELECT MAX(id) FROM updates_processed"))
      {
         result0 = db_store_result();
         if((row0 = mysql_fetch_row(result0)))
         {
            strcpy(last_update_id, row0[0]);
         }
         mysql_free_result(result0);
      }
   }

   if(last_update_id[0] == '\0')
   {
      _log(CRITICAL, "Failed to determine last update id from database.");
      exit(1);
   }


   if(test_mode)
   {
   }
   else
   {
      char c, pc;
      pc = 0;
      // Run through the file splitting off each JSON object and passing it on for processing.

      // DB may have dropped out due to long delay
      (void) db_connect();
      if(db_start_transaction()) _log(CRITICAL, "Failed to initiate database transaction.");
      while((buf_end = fread(buffer, 1, MAX_BUF, fp_result)) && run && !db_errored)
      {
         for(ibuf = 0; ibuf < buf_end && run && !db_errored; ibuf++)
         {
            c = buffer[ibuf];
            if(c != '\r' && c != '\n') 
            {
               obj[iobj++] = c;
               if(iobj >= MAX_OBJ)
               {
                  _log(CRITICAL, "Object buffer overflow!");
                  exit(1);
               }
               if(c == '"' && pc != '\\') in_q = ! in_q;
               if(!in_q && c == '{') b_depth++;
               if(!in_q && c == '}' && b_depth-- && !b_depth)
               {
                  obj[iobj] = '\0';
                  process_object(obj);
                  iobj = 0;
               }
            }
            pc = c;
         }
      }
      fclose(fp_result);
      if(db_errored)
      {
         _log(CRITICAL, "Update rolled back due to database error.");
         (void) db_rollback_transaction();
      }
      else
      {
         _log(GENERAL, "Committing database updates...");
         if(db_commit_transaction())
         {
            _log(CRITICAL, "Database commit failed.");
         }
         else
         {
            _log(GENERAL, "Committed.");
         }
      }
   }

#define REPORT_SIZE 16384
   char report[REPORT_SIZE];
   report[0] = '\0';

   _log(GENERAL, "");
   _log(GENERAL, "End of run:");

   if(used_insecure)
   {
      strcat(report, "*** Warning: Insecure download used.\n");
      _log(GENERAL, "*** Warning: Insecure download used.");
   }

   sprintf(zs, "             Elapsed time: %ld minutes", (time(NULL) - start_time + 30) / 60);
   _log(GENERAL, zs);
   strcat(report, zs); strcat(report, "\n");
   if(test_mode)
   {
      sprintf(zs, "Test mode.  No database changes made.");
      _log(GENERAL, zs);
      strcat(report, zs); strcat(report, "\n");
      exit(0);
   }

   for(i=0; i<MAXStats; i++)
   {
      sprintf(zs, "%25s: %s", stats_category[i], commas_q(stats[i]));
      if(i == DBError && stats[i]) strcat(zs, " ****************");
      _log(GENERAL, zs);
      strcat(report, zs);
      strcat(report, "\n");
   }

   db_disconnect();

   email_alert(NAME, BUILD, "Timetable Update Report", report);

   exit(0);
}
コード例 #8
0
ファイル: trustdb.c プロジェクト: proffalken/openrail
static void create_database(void)
{
   MYSQL_RES * result0;
   MYSQL_ROW row0;
   word create_trust_activation, create_trust_cancellation, create_trust_movement;
   word create_trust_changeorigin, create_trust_changeid;

   create_trust_activation = create_trust_cancellation = create_trust_movement = true;
   create_trust_changeorigin = create_trust_changeid = true;

   db_query("show tables");
   result0 = db_store_result();
   while((row0 = mysql_fetch_row(result0))) 
   {
      if(!strcasecmp(row0[0], "trust_activation")) create_trust_activation = false;
      if(!strcasecmp(row0[0], "trust_cancellation")) create_trust_cancellation = false;
      if(!strcasecmp(row0[0], "trust_movement")) create_trust_movement = false;
      if(!strcasecmp(row0[0], "trust_changeorigin")) create_trust_changeorigin = false;
      if(!strcasecmp(row0[0], "trust_changeid")) create_trust_changeid = false;
   }
   mysql_free_result(result0);

   if(create_trust_activation)
   {
      db_query(
"CREATE TABLE trust_activation "
"(created INT UNSIGNED NOT NULL, "
"trust_id VARCHAR(16) NOT NULL, "
"cif_schedule_id INT UNSIGNED NOT NULL, "
"deduced         TINYINT UNSIGNED NOT NULL, "
"INDEX(cif_schedule_id), INDEX(trust_id), INDEX(created)"
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table trust_activation.");
   }
   if(create_trust_cancellation)
   {
      db_query(
"CREATE TABLE trust_cancellation "
"(created INT UNSIGNED NOT NULL, "
"trust_id VARCHAR(16) NOT NULL, "
"reason VARCHAR(8) NOT NULL, "
"type   VARCHAR(32) NOT NULL, "
"loc_stanox VARCHAR(8) NOT NULL, "
"reinstate TINYINT UNSIGNED NOT NULL, "
"INDEX(trust_id), INDEX(created) "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table trust_cancellation.");
   }
   if(create_trust_movement)
   {
      db_query(
"CREATE TABLE trust_movement "
"(created            INT UNSIGNED NOT NULL, "
"trust_id            VARCHAR(16) NOT NULL, "
"event_type          VARCHAR(16) NOT NULL, "
"planned_event_type  VARCHAR(16) NOT NULL, "
"platform            VARCHAR(8) NOT NULL, "
"loc_stanox          VARCHAR(8) NOT NULL, "
"actual_timestamp    INT UNSIGNED NOT NULL, "
"gbtt_timestamp      INT UNSIGNED NOT NULL, "
"planned_timestamp   INT UNSIGNED NOT NULL, "
"timetable_variation INT NOT NULL, "
"event_source        VARCHAR(16) NOT NULL, "
"offroute_ind        BOOLEAN NOT NULL, "
"train_terminated    BOOLEAN NOT NULL, "
"variation_status    VARCHAR(16) NOT NULL, "
"next_report_stanox  VARCHAR(8) NOT NULL, "
"next_report_run_time INT UNSIGNED NOT NULL, "
"INDEX(trust_id), INDEX(created) "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table trust_movement.");
   }
   if(create_trust_changeorigin)
   {
      db_query(
"CREATE TABLE trust_changeorigin "
"(created INT UNSIGNED NOT NULL, "
"trust_id VARCHAR(16) NOT NULL, "
"reason VARCHAR(8) NOT NULL, "
"loc_stanox VARCHAR(8) NOT NULL, "
"INDEX(trust_id), INDEX(created) "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table trust_changeorigin.");
   }
   if(create_trust_changeid)
   {
      db_query(
"CREATE TABLE trust_changeid "
"(created INT UNSIGNED NOT NULL, "
"trust_id VARCHAR(16) NOT NULL, "
"new_trust_id VARCHAR(16) NOT NULL, "
"INDEX(trust_id), INDEX(new_trust_id), INDEX(created) "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table trust_changeid.");
   }
}
コード例 #9
0
ファイル: trustdb.c プロジェクト: proffalken/openrail
static void process_trust_0003(const char * string, const jsmntok_t * tokens, const int index)
{
   char query[1024], zs[32], zs1[32], train_id[128], loc_stanox[128];
   time_t planned_timestamp, actual_timestamp, timestamp;

   time_t now = time(NULL);
   
   sprintf(query, "INSERT INTO trust_movement VALUES(%ld, '", now);
   jsmn_find_extract_token(string, tokens, index, "train_id", train_id, sizeof(train_id));
   strcat(query, train_id);
   strcat(query, "', '");
   jsmn_find_extract_token(string, tokens, index, "event_type", zs, sizeof(zs));
   strcat(query, zs);
   strcat(query, "', '");
   jsmn_find_extract_token(string, tokens, index, "planned_event_type", zs, sizeof(zs));
   strcat(query, zs);
   strcat(query, "', '");
   jsmn_find_extract_token(string, tokens, index, "platform", zs, sizeof(zs));
   strcat(query, zs);
   strcat(query, "', '");
   jsmn_find_extract_token(string, tokens, index, "loc_stanox", loc_stanox, sizeof(loc_stanox));
   strcat(query, loc_stanox);
   strcat(query, "', ");
   jsmn_find_extract_token(string, tokens, index, "actual_timestamp", zs, sizeof(zs));
   zs[10] = '\0';
   actual_timestamp = correct_trust_timestamp(atol(zs));
   sprintf(zs, "%ld", actual_timestamp);
   strcat(query, zs);
   strcat(query, ", ");
   jsmn_find_extract_token(string, tokens, index, "gbtt_timestamp", zs, sizeof(zs));
   zs[10] = '\0';
   timestamp = correct_trust_timestamp(atol(zs));
   sprintf(zs, "%ld", timestamp);
   strcat(query, zs);
   strcat(query, ", ");
   jsmn_find_extract_token(string, tokens, index, "planned_timestamp", zs, sizeof(zs));
   zs[10] = '\0';
   planned_timestamp = correct_trust_timestamp(atol(zs));
   sprintf(zs, "%ld", planned_timestamp);
   strcat(query, zs);
   strcat(query, ", ");
   jsmn_find_extract_token(string, tokens, index, "timetable_variation", zs, sizeof(zs));
   strcat(query, zs);
   strcat(query, ", '");
   jsmn_find_extract_token(string, tokens, index, "event_source", zs, sizeof(zs));
   strcat(query, zs);
   strcat(query, "', ");
   jsmn_find_extract_token(string, tokens, index, "offroute_ind", zs, sizeof(zs));
   strcat(query, (zs[0] == 't')?"1":"0");
   strcat(query, ", ");
   jsmn_find_extract_token(string, tokens, index, "train_terminated", zs, sizeof(zs));
   strcat(query, (zs[0] == 't')?"1":"0");
   strcat(query, ", '");
   jsmn_find_extract_token(string, tokens, index, "variation_status", zs, sizeof(zs));
   strcat(query, zs);
   strcat(query, "', '");
   jsmn_find_extract_token(string, tokens, index, "next_report_stanox", zs, sizeof(zs));
   strcat(query, zs);
   strcat(query, "', ");
   jsmn_find_extract_token(string, tokens, index, "next_report_run_time", zs, sizeof(zs));
   sprintf(zs1, "%d", atoi(zs));
   strcat(query, zs1);
   strcat(query, ")");

   db_query(query);

   // Old one?
   if(planned_timestamp && now - actual_timestamp > 6*60*60)
   {
      sprintf(query, "Late movement message received, actual timestamp %s.", time_text(actual_timestamp, true));
      _log(MINOR, query);
   }
   sprintf(query, "SELECT * from trust_activation where trust_id = '%s' and created > %ld and cif_schedule_id > 0", train_id, now - (4*24*60*60));
   if(!db_query(query))
   {
      MYSQL_RES * result0 = db_store_result();

      word num_rows = mysql_num_rows(result0);
      mysql_free_result(result0);
      if(num_rows > 1)
      {
         // This is not actually invalid, if there's some cancellations as well
         // sprintf(query, "Movement message received with %d matching activations, train_id = \"%s\".", num_rows, train_id);
         // _log(MAJOR, query);
      }
      else if(num_rows < 1)
      {
         MYSQL_ROW row0;
         char tiploc[128], reason[128];
         word sort_time;
         time_t now = time(NULL);

         strcpy(reason, "");
         tiploc[0] = '\0';

         sprintf(query, "Movement message received with %d matching activations, train_id = \"%s\".", num_rows, train_id);
         _log(MINOR, query);
         stats[MovtNoAct]++;
         if(planned_timestamp == 0)
         {
            strcpy(reason, "No planned timestamp");
         }
         if(high_load) strcpy(reason, "Message load too high");

         if(!reason[0])
         {
            sprintf(query, "SELECT tiploc FROM corpus WHERE stanox = %s AND tiploc != ''", loc_stanox);
            if(!db_query(query))
            {
               result0 = db_store_result();
               num_rows = mysql_num_rows(result0);
               if(num_rows)
               {
                  row0 = mysql_fetch_row(result0);
                  strcpy(tiploc, row0[0]);
               }
               else
               {
                  strcpy(reason, "Unable to determine TIPLOC");
               }
               mysql_free_result(result0);
            }
         }

         if(!reason[0])
         {
            char query1[256];
            struct tm * broken = localtime(&planned_timestamp);
            sort_time = broken->tm_hour * 4 * 60 + broken->tm_min * 4;
            // Select the day
            word day = broken->tm_wday;
            word yest = (day + 6) % 7;
            // word tom = (day + 1) % 7;
            broken->tm_hour = 12;
            broken->tm_min = 0;
            broken->tm_sec = 0;
            time_t when = timegm(broken);
            sprintf(query, "SELECT cif_schedules.id, cif_schedules.CIF_train_uid, signalling_id, CIF_stp_indicator FROM cif_schedules INNER JOIN cif_schedule_locations ON cif_schedules.id = cif_schedule_locations.cif_schedule_id WHERE cif_schedule_locations.tiploc_code = '%s'",
                    tiploc);
            sprintf(query1, " AND cif_schedule_locations.sort_time > %d AND cif_schedule_locations.sort_time < %d",
                    sort_time - 1, sort_time + 4);
            strcat(query, query1);
            strcat(query, " AND (cif_schedules.CIF_stp_indicator = 'N' OR cif_schedules.CIF_stp_indicator = 'P' OR cif_schedules.CIF_stp_indicator = 'O')");


            static const char * days_runs[8] = {"runs_su", "runs_mo", "runs_tu", "runs_we", "runs_th", "runs_fr", "runs_sa", "runs_su"};

            //
            sprintf(query1, " AND (((%s) AND (schedule_start_date <= %ld) AND (schedule_end_date >= %ld) AND (NOT next_day))",   days_runs[day],  when + 12*60*60, when - 12*60*60);
            strcat(query, query1);
            sprintf(query1, " OR   ((%s) AND (schedule_start_date <= %ld) AND (schedule_end_date >= %ld) AND (    next_day)))",  days_runs[yest], when - 12*60*60, when - 36*60*60);
            strcat(query, query1);

            sprintf(query1, " AND deleted > %ld ORDER BY LOCATE(CIF_stp_indicator, 'NPO')", planned_timestamp);
            strcat(query, query1);

            if(!db_query(query))
            {
               char save_uid[16], save_stp;
               save_uid[0] = save_stp = '\0';
               dword cif_schedule_id = 0;
               result0 = db_store_result();
               num_rows = mysql_num_rows(result0);
               if(!num_rows)
               {
                  strcpy(reason, "No schedules found");
               }

               while((row0 = mysql_fetch_row(result0)))
               {
                  sprintf(query, "   Found potential match:%8s (%s) %s STP=%s", row0[0], row0[1], row0[2], row0[3]);
                  _log(MINOR, query);
                  if (!reason[0])
                  {
                     if(save_uid[0] && strcmp(save_uid, row0[1]))  strcpy(reason, "Multiple matching schedule UIDs");
                     else if (save_stp == 'O' && row0[3][0] =='O') strcpy(reason, "Multiple matching overlay schedules");
                     else
                     {
                        cif_schedule_id = atol(row0[0]);
                        strcpy(save_uid, row0[1]);
                        save_stp = row0[3][0];
                     }
                  }
               }
               mysql_free_result(result0);

               if(!reason[0])
               {
                  sprintf(query, "INSERT INTO trust_activation VALUES(%ld, '%s', %ld, 1)", now, train_id, cif_schedule_id);
                  db_query(query);
                  sprintf(query, "   Successfully deduced activation %ld", cif_schedule_id);
                  _log(MINOR, query);
               }
            }
         }

         if(!reason[0])
         {
            stats[DeducedAct]++;
         }
         else
         {
            sprintf(query, "   Failed to deduce an activation - Reason:  %s.", reason );
            _log(MINOR, query);
            
            sprintf(query, "      stanox = %s, tiploc = \"%s\", planned_timestamp %s, derived sort time = %d", loc_stanox, tiploc, time_text(planned_timestamp, true), sort_time);
            _log(MINOR, query);
            // jsmn_dump_tokens(string, tokens, index);
         }
      }
   }
   return;
}
コード例 #10
0
ファイル: trustdb.c プロジェクト: proffalken/openrail
static void process_trust_0001(const char * const string, const jsmntok_t * const tokens, const int index)
{
   char zs[128], zs1[128], report[1024];
   char train_id[64], train_uid[64];
   char query[1024];
   dword cif_schedule_id;
   MYSQL_RES * result0;
   MYSQL_ROW row0;
   
   sprintf(report, "Activation message:");

   jsmn_find_extract_token(string, tokens, index, "train_id", train_id, sizeof(train_id));
   sprintf(zs1, " train_id=\"%s\"", train_id);
   strcat(report, zs1);

   jsmn_find_extract_token(string, tokens, index, "schedule_start_date", zs, sizeof(zs));
   time_t schedule_start_date_stamp = parse_datestamp(zs);
   sprintf(zs1, " schedule_start_date=\"%s\" %ld", zs, schedule_start_date_stamp);
   strcat(report, zs1);

   jsmn_find_extract_token(string, tokens, index, "schedule_end_date", zs, sizeof(zs));
   time_t schedule_end_date_stamp   = parse_datestamp(zs);
   sprintf(zs1, " schedule_end_date=\"%s\" %ld", zs, schedule_end_date_stamp);
   strcat(report, zs1);

   jsmn_find_extract_token(string, tokens, index, "train_uid", train_uid, sizeof(train_uid));
   sprintf(zs1, " train_uid=\"%s\"", train_uid);
   strcat(report, zs1);

   jsmn_find_extract_token(string, tokens, index, "schedule_source", zs, sizeof(zs));
   sprintf(zs1, " schedule_source=\"%s\"", zs);
   strcat(report, zs1);

   jsmn_find_extract_token(string, tokens, index, "schedule_wtt_id", zs, sizeof(zs));
   sprintf(zs1, " schedule_wtt_id=\"%s\"", zs);
   strcat(report, zs1);

   // Bodge.  The ORDER BY here will *usually* get the correct one out first!
   // Idea:  If we store and index on cif_train_uid, we can guarantee to get the right one.
   sprintf(query, "select id from cif_schedules where cif_train_uid = '%s' AND schedule_start_date = %ld AND schedule_end_date = %ld AND deleted > %ld ORDER BY LOCATE(CIF_stp_indicator, 'OCPN')", train_uid, schedule_start_date_stamp, schedule_end_date_stamp, time(NULL));
   if(!db_query(query))
   {
      result0 = db_store_result();
      word num_rows = mysql_num_rows(result0);
      sprintf(zs, "  Schedule hit count %d.  Message contents:", num_rows);
      strcat(report, zs);
      if(num_rows < 1) 
      {
         stats[Mess1Miss]++;
         _log(MINOR, report);
         time_t now = time(NULL);
         sprintf(query, "INSERT INTO trust_activation VALUES(%ld, '%s', %ld, 0)", now, train_id, 0L);
         db_query(query);
         jsmn_dump_tokens(string, tokens, index);
      }
      else
      {
         row0 = mysql_fetch_row(result0);
         cif_schedule_id = atol(row0[0]);
         time_t now = time(NULL);
         sprintf(query, "INSERT INTO trust_activation VALUES(%ld, '%s', %ld, 0)", now, train_id, cif_schedule_id);
         db_query(query);
      }
      mysql_free_result(result0);
   }
   return;
}
コード例 #11
0
ファイル: database.c プロジェクト: tinnerdxp/openrail
word database_upgrade(const word caller)
{
   char query[256];
   MYSQL_RES * res;
   MYSQL_ROW row;
   word lock, result, old_version;

   result = 0;

   // Lock the upgrade process.
   // NOTE - This DOES NOT lock the database from normal data processing, it merely prevents
   // simultaneous database_upgrade processing.
   lock = 0;
   while(!lock)
   {
      if(!db_query("SELECT GET_LOCK('openrail.database_upgrade', 1)"))
      {
         res = db_store_result();
         row = mysql_fetch_row(res);
         lock = atoi(row[0]);
         mysql_free_result(res);
      }
      if(!lock)
      {
         _log(MINOR, "Waiting for database upgrade lock.");
         sleep(8);
      }
   }

   old_version = 0;
   if(table_exists("database_version"))
   {
      sprintf(query, "SELECT * FROM database_version");
      if(!db_query(query))
      {
         res = db_store_result();
         row = mysql_fetch_row(res);
         old_version = atoi(row[0]);
         mysql_free_result(res);
      }
   }

   // Note:  old > NEW is no-op, not error.  If a particular program is not changed during an upgrade, it will 
   // not need to be re-compiled just for the database change.
   // Note:  If a change creates a new table, there's no need to do it in this upgrade section, just leave it
   // to the table creation below.
   if(old_version < NEW_VERSION)
   {
      _log(GENERAL, "Upgrading database from version %d to version %d.", old_version, NEW_VERSION);

      // Upgrade to 1
      if(old_version < 1)
      {
         db_query("CREATE TABLE database_version (v INT UNSIGNED NOT NULL ) ENGINE = InnoDB");
         db_query("INSERT INTO  database_version VALUES(0)");
         _log(GENERAL, "Created database table \"database_version\".");
      }

      // Upgrade to x
      // if(old_version < x)
      //    if(table_exists(y)  You must have this in case the table hasn't been created yet, e.g. on a brand new platform.
      //        ALTER TABLE

      sprintf(query, "UPDATE database_version SET v = %d", NEW_VERSION);
      db_query(query);
      _log(GENERAL, "Database upgraded to version %d.", NEW_VERSION);     
   }

   // Upgrade completed.  Now create any missing tables

   ///////////////////////////////////////////////////////////////////////////////////////////////////
   // corpus table
   if(!table_exists("corpus"))
   {
      db_query(
"CREATE TABLE corpus(                         "
"id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, "
"fn                     VARCHAR(255) NOT NULL,"
"stanox                 CHAR(16) NOT NULL,    "
"uic                    CHAR(16) NOT NULL,    "
"3alpha                 CHAR(16) NOT NULL,    "
"nlcdesc16              CHAR(20) NOT NULL,    "
"tiploc                 CHAR(16) NOT NULL,    "
"nlc                    CHAR(16) NOT NULL,    "
"nlcdesc                VARCHAR(255) NOT NULL,"
"PRIMARY KEY (id), INDEX(TIPLOC)              "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table \"corpus\".");
   }

   ///////////////////////////////////////////////////////////////////////////////////////////////////
   // smart table
   if(caller == smartdb && !table_exists("smart"))
   {
      db_query(
"CREATE TABLE smart(                          "
"id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, "
"  `fromberth` char(4) NOT NULL,"
"  `td` char(2) NOT NULL,"
"  `stanox` int NOT NULL,"
"  `route` smallint NOT NULL,"
"  `steptype` char(1) NOT NULL,"
"  `toberth` char(4) NOT NULL,"
"  `toline` char(1) NOT NULL,"
"  `berthoffset` int NOT NULL,"
"  `platform` smallint NOT NULL,"
"  `event` char(1) NOT NULL,"
"  `comment` varchar(64) NOT NULL,"
"  `stanme` char(9) NOT NULL,"
"  `fromline` char(1) NOT NULL,"
"  PRIMARY KEY (id)"
") ENGINE = InnoDB DEFAULT CHARSET=latin1"
               );
      _log(GENERAL, "Created database table \"smart\".");
   }

   ///////////////////////////////////////////////////////////////////////////////////////////////////
   // Schedule tables
   if((caller == cifdb || caller == vstpdb || caller == trustdb) && !table_exists("updates_processed"))
   {
      db_query(
"CREATE TABLE updates_processed"
"("
"id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, "
"time INT UNSIGNED NOT NULL,"
"PRIMARY KEY (id) "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table \"updates_processed\".");
   }

   if((caller == cifdb || caller == vstpdb || caller == trustdb) && !table_exists("cif_associations"))
   {
      db_query(
"CREATE TABLE cif_associations                   "
"(                                               "
"update_id               SMALLINT UNSIGNED NOT NULL,        "
"created                 INT UNSIGNED NOT NULL,       "
"deleted                 INT UNSIGNED NOT NULL,       "
"main_train_uid          CHAR(6) NOT NULL,       "
"assoc_train_uid         CHAR(6) NOT NULL,       "
"assoc_start_date        INT UNSIGNED NOT NULL,       "
"assoc_end_date          INT UNSIGNED NOT NULL,       "
"assoc_days              CHAR(7) NOT NULL,       "
"category                CHAR(2) NOT NULL,       "
"date_indicator          CHAR(1) NOT NULL,       "
"location                CHAR(7) NOT NULL,       "
"base_location_suffix    CHAR(1) NOT NULL,       "
"assoc_location_suffix   CHAR(1) NOT NULL,       "
"diagram_type            CHAR(1) NOT NULL,       "
"CIF_stp_indicator       CHAR(1) NOT NULL        "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table \"cif_associations\".");
   }

   if((caller == cifdb || caller == vstpdb || caller == trustdb) && !table_exists("cif_schedules"))
   {
      db_query(
"CREATE TABLE cif_schedules                      "
"(                                               "
"update_id                     SMALLINT UNSIGNED NOT NULL,  "
"created                       INT UNSIGNED NOT NULL, "
"deleted                       INT UNSIGNED NOT NULL, "
"CIF_bank_holiday_running      CHAR(1) NOT NULL, "
"CIF_stp_indicator             CHAR(1) NOT NULL, "
"CIF_train_uid                 CHAR(6) NOT NULL, "
"applicable_timetable          char(1) NOT NULL, "
"atoc_code                     CHAR(2) NOT NULL, "
   //"traction_class                CHAR(8) NOT NULL, " // "Not used" in CIF spec so omitted here.
"uic_code                      CHAR(5) NOT NULL, "
"runs_mo                       BOOLEAN NOT NULL, "
"runs_tu                       BOOLEAN NOT NULL, "
"runs_we                       BOOLEAN NOT NULL, "
"runs_th                       BOOLEAN NOT NULL, "
"runs_fr                       BOOLEAN NOT NULL, "
"runs_sa                       BOOLEAN NOT NULL, "
"runs_su                       BOOLEAN NOT NULL, "
"schedule_end_date             INT UNSIGNED NOT NULL, "
"signalling_id                 CHAR(4) NOT NULL, "
"CIF_train_category            CHAR(2) NOT NULL, "
"CIF_headcode                  CHAR(4) NOT NULL, "
   //"CIF_course_indicator          CHAR(8) NOT NULL, " // "Not used" in CIF spec, so omitted here.
"CIF_train_service_code        CHAR(8) NOT NULL, "
"CIF_business_sector           CHAR(1) NOT NULL, "
"CIF_power_type                CHAR(3) NOT NULL, "
"CIF_timing_load               CHAR(4) NOT NULL, "
"CIF_speed                     CHAR(3) NOT NULL, "
"CIF_operating_characteristics CHAR(6) NOT NULL, "
"CIF_train_class               CHAR(1) NOT NULL, "
"CIF_sleepers                  CHAR(1) NOT NULL, "
"CIF_reservations              CHAR(1) NOT NULL, "
"CIF_connection_indicator      CHAR(1) NOT NULL, "
"CIF_catering_code             CHAR(4) NOT NULL, "
"CIF_service_branding          CHAR(4) NOT NULL, "
"schedule_start_date           INT UNSIGNED NOT NULL, "
"train_status                  CHAR(1) NOT NULL, "
"id                            INT UNSIGNED NOT NULL AUTO_INCREMENT, "
"deduced_headcode              CHAR(4) NOT NULL DEFAULT '', "
"deduced_headcode_status       CHAR(1) NOT NULL DEFAULT '', "
"PRIMARY KEY (id), INDEX(schedule_end_date), INDEX(schedule_start_date), INDEX(CIF_train_uid), INDEX(CIF_stp_indicator) "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table \"cif_schedules\".");
   }

   if((caller == cifdb || caller == vstpdb || caller == trustdb) && !table_exists("cif_schedule_locations"))
   {
      db_query(
"CREATE TABLE cif_schedule_locations             "
"(                                               "
"update_id                     SMALLINT UNSIGNED NOT NULL, "
"cif_schedule_id               INT UNSIGNED NOT NULL, "
"location_type                 CHAR(12) NOT NULL, " // MISNAMED!  contains activity
"record_identity               CHAR(2) NOT NULL, " // LO, LI, LT, etc
"tiploc_code                   CHAR(7) NOT NULL, "
"tiploc_instance               CHAR(1) NOT NULL, "
"arrival                       CHAR(5) NOT NULL, "
"departure                     CHAR(5) NOT NULL, "
"pass                          CHAR(5) NOT NULL, "
"public_arrival                CHAR(4) NOT NULL, "
"public_departure              CHAR(4) NOT NULL, "
"sort_time                     SMALLINT UNSIGNED NOT NULL, "
"next_day                      BOOLEAN NOT NULL, "
"platform                      CHAR(3) NOT NULL, "
"line                          CHAR(3) NOT NULL, "
"path                          CHAR(3) NOT NULL, "
"engineering_allowance         CHAR(2) NOT NULL, "
"pathing_allowance             CHAR(2) NOT NULL, "
"performance_allowance         CHAR(2) NOT NULL, "
"INDEX(cif_schedule_id), INDEX(tiploc_code) "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table \"cif_schedule_locations\".");
   }

   if((caller == cifdb || caller == vstpdb || caller == trustdb) && !table_exists("cif_changes_en_route"))
   {
      db_query(
"CREATE TABLE cif_changes_en_route               "
"(                                               "
"cif_schedule_id               INT UNSIGNED NOT NULL, "
"tiploc_code                   CHAR(7) NOT NULL, "
"tiploc_instance               CHAR(1) NOT NULL, "
"CIF_train_category            CHAR(2) NOT NULL, "
"signalling_id                 CHAR(4) NOT NULL, "
"CIF_headcode                  CHAR(4) NOT NULL, "
"CIF_train_service_code        CHAR(8) NOT NULL, "
// Portion ID not used
"CIF_power_type                CHAR(3) NOT NULL, "
"CIF_timing_load               CHAR(4) NOT NULL, "
"CIF_speed                     CHAR(3) NOT NULL, "
"CIF_operating_characteristics CHAR(6) NOT NULL, "
"CIF_train_class               CHAR(1) NOT NULL, "
"CIF_sleepers                  CHAR(1) NOT NULL, "
"CIF_reservations              CHAR(1) NOT NULL, "
"CIF_connection_indicator      CHAR(1) NOT NULL, "
"CIF_catering_code             CHAR(4) NOT NULL, "
"CIF_service_branding          CHAR(4) NOT NULL, "
// Traction class Not used.
"uic_code                      CHAR(5) NOT NULL, "
"INDEX(cif_schedule_id), INDEX(tiploc_code)      "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table \"cif_changes_en_route\".");
   }

   ///////////////////////////////////////////////////////////////////////////////////////////////////
   // trust tables
   if((caller == trustdb) && !table_exists("trust_activation"))
   {
      db_query(
"CREATE TABLE trust_activation "
"(created INT UNSIGNED NOT NULL, "
"trust_id VARCHAR(16) NOT NULL, "
"cif_schedule_id INT UNSIGNED NOT NULL, "
"deduced         TINYINT UNSIGNED NOT NULL, "
"INDEX(cif_schedule_id), INDEX(trust_id), INDEX(created)"
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table \"trust_activation\".");
   }
   if((caller == trustdb) && !table_exists("trust_cancellation"))
   {
      db_query(
"CREATE TABLE trust_cancellation "
"(created INT UNSIGNED NOT NULL, "
"trust_id VARCHAR(16) NOT NULL, "
"reason VARCHAR(8) NOT NULL, "
"type   VARCHAR(32) NOT NULL, "
"loc_stanox VARCHAR(8) NOT NULL, "
"reinstate TINYINT UNSIGNED NOT NULL, "
"INDEX(trust_id), INDEX(created) "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table \"trust_cancellation\".");
   }

   if((caller == trustdb) && !table_exists("trust_movement"))
   {
      db_query(
"CREATE TABLE trust_movement "
"(created            INT UNSIGNED NOT NULL, "
"trust_id            VARCHAR(16) NOT NULL, "
"event_type          VARCHAR(16) NOT NULL, "
"planned_event_type  VARCHAR(16) NOT NULL, "
"platform            VARCHAR(8) NOT NULL, "
"loc_stanox          VARCHAR(8) NOT NULL, "
"actual_timestamp    INT UNSIGNED NOT NULL, "
"gbtt_timestamp      INT UNSIGNED NOT NULL, "
"planned_timestamp   INT UNSIGNED NOT NULL, "
"timetable_variation INT NOT NULL, "
"event_source        VARCHAR(16) NOT NULL, "
"offroute_ind        BOOLEAN NOT NULL, "
"train_terminated    BOOLEAN NOT NULL, "
"variation_status    VARCHAR(16) NOT NULL, "
"next_report_stanox  VARCHAR(8) NOT NULL, "
"next_report_run_time INT UNSIGNED NOT NULL, "
"INDEX(trust_id), INDEX(created) "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table \"trust_movement\".");
   }

   if((caller == trustdb) && !table_exists("trust_changeorigin"))
   {
      db_query(
"CREATE TABLE trust_changeorigin "
"(created INT UNSIGNED NOT NULL, "
"trust_id VARCHAR(16) NOT NULL, "
"reason VARCHAR(8) NOT NULL, "
"loc_stanox VARCHAR(8) NOT NULL, "
"INDEX(trust_id), INDEX(created) "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table \"trust_changeorigin\".");
   }

   if((caller == trustdb) && !table_exists("trust_changeid"))
   {
      db_query(
"CREATE TABLE trust_changeid "
"(created INT UNSIGNED NOT NULL, "
"trust_id VARCHAR(16) NOT NULL, "
"new_trust_id VARCHAR(16) NOT NULL, "
"INDEX(trust_id), INDEX(new_trust_id), INDEX(created) "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table \"trust_changeid\".");
   }

   if((caller == vstpdb || caller == trustdb || caller == tddb) && !table_exists("status"))
   {
      db_query(
"CREATE TABLE status "
"(last_trust_processed INT UNSIGNED NOT NULL, "
"last_trust_actual     INT UNSIGNED NOT NULL, "
"last_vstp_processed   INT UNSIGNED NOT NULL, "
"last_td_processed     INT UNSIGNED NOT NULL  "
") ENGINE = InnoDB"
               );
      db_query(
"INSERT INTO status VALUES(0, 0, 0, 0)"
               );
      _log(GENERAL, "Created database table \"status\".");
   }

   if((caller == vstpdb || caller == trustdb || caller == tddb) && !table_exists("message_count"))
   {
      db_query(
"CREATE TABLE message_count "
"(application          CHAR(16) NOT NULL,     "
"time                  INT UNSIGNED NOT NULL, "
"count                 INT UNSIGNED NOT NULL, "
"INDEX(time)                                  "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table \"message_count\".");
   }

   ///////////////////////////////////////////////////////////////////////////////////////////////////
   // td tables
   if(caller == tddb && !table_exists("td_updates"))
   {
      db_query(
"CREATE TABLE td_updates "
"(created INT UNSIGNED NOT NULL, "
"handle   INT UNSIGNED NOT NULL, "
"k        CHAR(8) NOT NULL, "
"v        CHAR(8) NOT NULL, "
"PRIMARY KEY(k) "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table \"td_updates\".");
   }

   if((caller == tddb || caller == limed) && !table_exists("td_states"))
   {
      db_query(
"CREATE TABLE td_states "
"(updated INT UNSIGNED NOT NULL, "
"k        CHAR(8) NOT NULL, "
"v        CHAR(8) NOT NULL, "
"PRIMARY KEY(k) "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table \"td_states\".");
   }

   if(caller == tddb && !table_exists("td_status"))
   {
      db_query(
"CREATE TABLE td_status "
"(d             CHAR(4) NOT NULL, "
"last_timestamp INT UNSIGNED NOT NULL "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table \"td_status\".");
   }

   if(caller == tddb && !table_exists("friendly_names_20"))
   {
      db_query(
"CREATE TABLE friendly_names_20 "
"(tiploc CHAR(8) NOT NULL, "
"name    VARCHAR(32), "
"PRIMARY KEY (tiploc) "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table \"friendly_names_20\".");
   }

   ///////////////////////////////////////////////////////////////////////////////////////////////////
   // Archive tables
   if(caller == archdb && !table_exists("trust_activation_arch"))
   {
      db_query(
"CREATE TABLE trust_activation_arch "
"(created INT UNSIGNED NOT NULL, "
"trust_id VARCHAR(16) NOT NULL, "
"cif_schedule_id INT UNSIGNED NOT NULL, "
"deduced         TINYINT UNSIGNED NOT NULL "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table \"trust_activation_arch\".");
   } 

   if(caller == archdb && !table_exists("trust_cancellation_arch"))
   {
      db_query(
"CREATE TABLE trust_cancellation_arch "
"(created INT UNSIGNED NOT NULL, "
"trust_id VARCHAR(16) NOT NULL, "
"reason VARCHAR(8) NOT NULL, "
"type   VARCHAR(32) NOT NULL, "
"loc_stanox VARCHAR(8) NOT NULL, "
"reinstate TINYINT UNSIGNED NOT NULL "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table \"trust_cancellation_arch\".");
   } 

   if(caller == archdb && !table_exists("trust_movement_arch"))
   {
      db_query(
"CREATE TABLE trust_movement_arch "
"(created            INT UNSIGNED NOT NULL, "
"trust_id            VARCHAR(16) NOT NULL, "
"event_type          VARCHAR(16) NOT NULL, "
"planned_event_type  VARCHAR(16) NOT NULL, "
"platform            VARCHAR(8) NOT NULL, "
"loc_stanox          VARCHAR(8) NOT NULL, "
"actual_timestamp    INT UNSIGNED NOT NULL, "
"gbtt_timestamp      INT UNSIGNED NOT NULL, "
"planned_timestamp   INT UNSIGNED NOT NULL, "
"timetable_variation INT NOT NULL, "
"event_source        VARCHAR(16) NOT NULL, "
"offroute_ind        BOOLEAN NOT NULL, "
"train_terminated    BOOLEAN NOT NULL, "
"variation_status    VARCHAR(16) NOT NULL, "
"next_report_stanox  VARCHAR(8) NOT NULL, "
"next_report_run_time INT UNSIGNED NOT NULL "
") ENGINE = InnoDB"               );
      _log(GENERAL, "Created database table \"trust_movement_arch\".");
   } 

   if(caller == archdb && !table_exists("trust_changeorigin_arch"))
   {
      db_query(
"CREATE TABLE trust_changeorigin_arch "
"(created INT UNSIGNED NOT NULL, "
"trust_id VARCHAR(16) NOT NULL, "
"reason VARCHAR(8) NOT NULL, "
"loc_stanox VARCHAR(8) NOT NULL "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table \"trust_changeorigin_arch\".");
   } 

   if(caller == archdb && !table_exists("trust_changeid_arch"))
   {
      db_query(
"CREATE TABLE trust_changeid_arch "
"(created INT UNSIGNED NOT NULL, "
"trust_id VARCHAR(16) NOT NULL, "
"new_trust_id VARCHAR(16) NOT NULL "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table \"trust_changeid_arch\".");
   } 

   if(caller == archdb && !table_exists("cif_associations_arch"))
   {
      db_query(
"CREATE TABLE cif_associations_arch              "
"(                                               "
"update_id               SMALLINT UNSIGNED NOT NULL,        "
"created                 INT UNSIGNED NOT NULL,       "
"deleted                 INT UNSIGNED NOT NULL,       "
"main_train_uid          CHAR(6) NOT NULL,       "
"assoc_train_uid         CHAR(6) NOT NULL,       "
"assoc_start_date        INT UNSIGNED NOT NULL,       "
"assoc_end_date          INT UNSIGNED NOT NULL,       "
"assoc_days              CHAR(7) NOT NULL,       "
"category                CHAR(2) NOT NULL,       "
"date_indicator          CHAR(1) NOT NULL,       "
"location                CHAR(7) NOT NULL,       "
"base_location_suffix    CHAR(1) NOT NULL,       "
"assoc_location_suffix   CHAR(1) NOT NULL,       "
"diagram_type            CHAR(1) NOT NULL,       "
"CIF_stp_indicator       CHAR(1) NOT NULL        "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table \"cif_associations_arch\".");
   } 

   if(caller == archdb && !table_exists("cif_schedules_arch"))
   {
      db_query(
"CREATE TABLE cif_schedules_arch                 "
"(                                               "
"update_id                     SMALLINT UNSIGNED NOT NULL,  "
"created                       INT UNSIGNED NOT NULL, "
"deleted                       INT UNSIGNED NOT NULL, "
"CIF_bank_holiday_running      CHAR(1) NOT NULL, "
"CIF_stp_indicator             CHAR(1) NOT NULL, "
"CIF_train_uid                 CHAR(6) NOT NULL, "
"applicable_timetable          char(1) NOT NULL, "
"atoc_code                     CHAR(2) NOT NULL, "
   //"traction_class                CHAR(8) NOT NULL, " // "Not used" in CIF spec so omitted here.
"uic_code                      CHAR(5) NOT NULL, "
"runs_mo                       BOOLEAN NOT NULL, "
"runs_tu                       BOOLEAN NOT NULL, "
"runs_we                       BOOLEAN NOT NULL, "
"runs_th                       BOOLEAN NOT NULL, "
"runs_fr                       BOOLEAN NOT NULL, "
"runs_sa                       BOOLEAN NOT NULL, "
"runs_su                       BOOLEAN NOT NULL, "
"schedule_end_date             INT UNSIGNED NOT NULL, "
"signalling_id                 CHAR(4) NOT NULL, "
"CIF_train_category            CHAR(2) NOT NULL, "
"CIF_headcode                  CHAR(4) NOT NULL, "
   //"CIF_course_indicator          CHAR(8) NOT NULL, " // "Not used" in CIF spec, so omitted here.
"CIF_train_service_code        CHAR(8) NOT NULL, "
"CIF_business_sector           CHAR(1) NOT NULL, "
"CIF_power_type                CHAR(3) NOT NULL, "
"CIF_timing_load               CHAR(4) NOT NULL, "
"CIF_speed                     CHAR(3) NOT NULL, "
"CIF_operating_characteristics CHAR(6) NOT NULL, "
"CIF_train_class               CHAR(1) NOT NULL, "
"CIF_sleepers                  CHAR(1) NOT NULL, "
"CIF_reservations              CHAR(1) NOT NULL, "
"CIF_connection_indicator      CHAR(1) NOT NULL, "
"CIF_catering_code             CHAR(4) NOT NULL, "
"CIF_service_branding          CHAR(4) NOT NULL, "
"schedule_start_date           INT UNSIGNED NOT NULL, "
"train_status                  CHAR(1) NOT NULL, "
"id                            INT UNSIGNED NOT NULL, "
"deduced_headcode              CHAR(4) NOT NULL DEFAULT '', "
"deduced_headcode_status       CHAR(1) NOT NULL DEFAULT ''  "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table \"cif_schedules_arch\".");
   } 

   if(caller == archdb && !table_exists("cif_schedule_locations_arch"))
   {
      db_query(
"CREATE TABLE cif_schedule_locations_arch        "
"(                                               "
"update_id                     SMALLINT UNSIGNED NOT NULL, "
"cif_schedule_id               INT UNSIGNED NOT NULL, "
"location_type                 CHAR(12) NOT NULL, " // Expected to become activity ?????
"record_identity               CHAR(2) NOT NULL, " // LO, LI, LT, etc
"tiploc_code                   CHAR(7) NOT NULL, "
"tiploc_instance               CHAR(1) NOT NULL, "
"arrival                       CHAR(5) NOT NULL, "
"departure                     CHAR(5) NOT NULL, "
"pass                          CHAR(5) NOT NULL, "
"public_arrival                CHAR(4) NOT NULL, "
"public_departure              CHAR(4) NOT NULL, "
"sort_time                     SMALLINT UNSIGNED NOT NULL, "
"next_day                      BOOLEAN NOT NULL, "
"platform                      CHAR(3) NOT NULL, "
"line                          CHAR(3) NOT NULL, "
"path                          CHAR(3) NOT NULL, "
"engineering_allowance         CHAR(2) NOT NULL, "
"pathing_allowance             CHAR(2) NOT NULL, "
"performance_allowance         CHAR(2) NOT NULL "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table \"cif_schedule_locations_arch\".");
   } 

   if(caller == archdb && !table_exists("cif_changes_en_route_arch"))
   {
      db_query(
"CREATE TABLE cif_changes_en_route_arch               "
"(                                               "
"cif_schedule_id               INT UNSIGNED NOT NULL, "
"tiploc_code                   CHAR(7) NOT NULL, "
"tiploc_instance               CHAR(1) NOT NULL, "
"CIF_train_category            CHAR(2) NOT NULL, "
"signalling_id                 CHAR(4) NOT NULL, "
"CIF_headcode                  CHAR(4) NOT NULL, "
"CIF_train_service_code        CHAR(8) NOT NULL, "
// Portion ID not used
"CIF_power_type                CHAR(3) NOT NULL, "
"CIF_timing_load               CHAR(4) NOT NULL, "
"CIF_speed                     CHAR(3) NOT NULL, "
"CIF_operating_characteristics CHAR(6) NOT NULL, "
"CIF_train_class               CHAR(1) NOT NULL, "
"CIF_sleepers                  CHAR(1) NOT NULL, "
"CIF_reservations              CHAR(1) NOT NULL, "
"CIF_connection_indicator      CHAR(1) NOT NULL, "
"CIF_catering_code             CHAR(4) NOT NULL, "
"CIF_service_branding          CHAR(4) NOT NULL, "
// Traction class Not used.
"uic_code                      CHAR(5) NOT NULL "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table \"cif_changes_en_route_arch\".");
   } 

   if((caller == trustdb || caller == tddb) && !table_exists("obfus_lookup"))
   {
      db_query(
"CREATE TABLE obfus_lookup                            "
"(                                                    "
"created                       INT UNSIGNED NOT NULL, "
"true_hc                       CHAR(4) NOT NULL,      "
"obfus_hc                      CHAR(4) NOT NULL       "
") ENGINE = InnoDB"
               );
      _log(GENERAL, "Created database table \"obfus_lookup\".");
   } 

   // Unlock the upgrade process
   if(!db_query("SELECT RELEASE_LOCK('openrail.database_upgrade')"))
   {
      res = db_store_result();
      mysql_free_result(res);
   }
   return result;
}