TopCoder DW Load Update - Load Marathon Match as challenge

In this challenge, we want to upload the TCLoadTCS which loads the challenge and direct project data into topcoder dataware house to load the Marathon Match data.

Requirements Details:

• Only the completed marathon match will be loaded into tcs_dw:project table
• The marathon match has records in tcs_catalog:project with category id 37. The project info with type  (56, Marathon Match Id) holds the round id of the marathon match. Unlike other challenge types, most of the real data about the marathon match are stored in informixoltp:contest, informixoltp:round table. See the following SQL for example
   
  select
      'Marathon'::nvarchar(254) as challenge_type,
      c.name || ' ' || r.name::nvarchar(128) as challenge_name,
      r.round_id as challenge_id,
      (select count(*) from informixoltp:long_component_state cs, informixoltp:long_submission s where s.example = 0 and s.long_component_state_id = cs.long_component_state_id and cs.round_id = r.round_id) as num_submissions,
      (select count(*) from informixoltp:round_registration rr where rr.round_id = r.round_id) as num_registrants,
      rs1.start_time::datetime year to second as registration_start_date,
      rs2.end_time::datetime year to second as submission_end_date,
      'Data'::nvarchar(50) as challenge_community,
      rs1.start_time::datetime year to second as posting_date
  from informixoltp:contest c
  join informixoltp:round as r on r.contest_id = c.contest_id and r.status='A'
  join informixoltp:round_segment rs1 on rs1.round_id = r.round_id and rs1.segment_id = 1 -- registration phase
  join informixoltp:round_segment rs2 on rs2.round_id = r.round_id and rs2.segment_id = 2 -- coding phase
  where r.round_type_id in (10,13,15,19,22,24,25,27) ;
     
• For the marathon match, we want the following columns in tcs_dw:project table loaded
  • project_id
  • component_id
  • component_name - use the match name
  • number_registrants
  • number_submissions
  • phase_id
  • phase_desc
  • category_id
  • category_desc
  • submitby_date
  • complete_date
  • status_id
  • status_desc
  • project_category_id
  • project_category_name
  • tc_direct_project_id
  • admin_fee
  • contest_prizes_total - the real prizes paid to the members
  • client_project_id
  • start_date_calendar_id
  • duration
  • fulfillment - always be 1.0 as we only load completed marathon matches
  • last_modification_date
  • first_place_prize - comes from the prizes table
  • total_prize - comes from the prizes table
• Do not mix the load marathon match logic with current doLoadProjects() methods in TCLoadTCS, have a new method write to specifically load the marathon matches.
• After loading, please make sure that the marathon match records in tcs_dw:project can be picked up by the AggregationDataLoader.java into weekly_contest_stats and monthly_contest_stats

VM Environment

You need a TopCoder VM to work on this challenge.

Information about VM can be found below:

VM specific information is found here: http://www.topcoder.com/wiki/display/docs/VM+Image+2.5. Upon registration as a submitter or reviewer you will need to request a VM based on the new TopCoder Cockpit/Direct image. To request your image, please use the forum. Before requesting your VM, you need to ensure that you have an SSH key created and in your member profile. Instructions to do so are here: http://www.topcoder.com/wiki/display/projects/Generate+SSH+Key, and instructions to connect afterwards are here: http://www.topcoder.com/wiki/display/projects/Connect+Using+SSH+Key.

Source Repo:

https://coder.topcoder.com/internal/web_module/trunk/src/main/com/topcoder/utilities/dwload/TCLoadTCS.java

https://coder.topcoder.com/internal/web_module/trunk/src/main/com/topcoder/utilities/dwload/AggregationDataLoader.java

Final Submission Guidelines

• The updated source codes.
• A deployment guide with verification details.