Release Assembly - topcoder Synchronize OLTP Coder Table with User Table

Key Information

Register
Submit
The challenge is finished.

Challenge Overview

Synchronize OLTP Coder Table with User Table

 

1.0   Project Overview

The topcoder database (oltp) contains a user table (common_oltp) and a coder table (informixoltp) . The coder table is currently a subset of the user table in terms of the # of users it includes.  The data warehouse coder table gets synchronized with the oltp coder table via cron jobs that run at specified intervals of time.  As the coder table in oltp database currently does not contain all the users, the data warehouse is also missing the full list of users. This is resulting into incorrect reporting.

To solve this issue, we need to load the missing records into the informixoltp.coder table by :

  • Identify the users that are present in common_oltp.user table but not present in informixoltp.coder table

  • Insert these missing users in informixoltp.coder table with values as mentioned in the Specs. 

In this assembly, we can achieve this by implementing a Java program that loads the missing users and adds them into the coder table with the default values as specified in the specs.

See the following section for details.

 
 

1.1     Competition Task Overview

1.1.1 SVN used for this assembly

TopCoder Web: https://coder.topcoder.com/internal/web_module/trunk

Please use latest revision as the base revision for this assembly.

To get the READ-ONLY access to the above SVN, please send an email to support@topcoder.com with your handle, contest name and the SVN address to request a READ-ONLY permission.

 

1.1.2 Create required test data to test the program ( user table)

The VM database contains some test users so those can be used for testing. You can also add more users for testing purposes.

 

1.1.3  Write a Java Program/Loader with name  “CodersDataLoader”  that inserts the missing user records in the informixoltp.coder table.

 

“CoderDataLoader” Java Program Details

=========================================

Please write a Java class with name “CoderDataLoader” that would load the missing users in the Coder table.

The class should reside in the package com.topcoder.utilities.datapatch (please create a sub package inside utilities with name datapatch ). Provide required documentation in the class.

 

Tables required :

- common_oltp.User

- informixoltp.Coder

- common_oltp.address

- common_oltp.user_address_xref

 

Logic:

1.Find all the users that are present in User table but not present in coder table.All users irrespective of their status needs to be retrieved.

2. For each user found in step 1, insert a record in coder table with following column values:

 

Column

Value

coder_id

user_id

member_since

user.create_date

quote

-

modify_date

user.modify_date

language_id

1

coder_type_id

2

date_of_birth

-

home_country_code

840

comp_country_code

840

contact_date    

-

display_quote

1

quote_location

md

quote_color

#000000

display_banner

0

banner_style

bannerStyle1

 

Country code - First check if the user has corresponding address info. If yes take the country code from the address Else, set both home country code and comp country code value to 840.

 

1.1.4 Verify that all the missing records have been inserted in coder table with correct data as laid down in specs.

 



Final Submission Guidelines

1.2     Submission Deliverables

All updated and new files which are necessary to meet the requirements in 1.1. This includes java source file(s) with documentation, sql scripts, ant scripts.


 

1.3     Technology Overview

 

1.3.1 Java 

1.3.2 Informix

1.3.3 SQL

 

1.4  Virtual Machine

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

 

1.5     Timeline

Submission Phase Length (days): 3 days

Registration Phase Length (days): 2 days

 

1.6     Payment

TopCoder will compensate members with first and second place submissions. Initial payment for the winning member will be distributed in two installments. The first payment will be made when the winning solution is submitted and review board Final Fixes are integrated. The second payment will be made at the completion of the Deployment phase of the project.

Winning Submission Prize: $200

Second Place Prize: $100

 

1.6.2     Support

Standard 30 days support

 

 

1.7     Final Submission

  • For each registrant, the final submission should be uploaded to the Online Review Tool.

  • Submissions must include all deliverables as specified in 1.2 Submission Deliverable

REVIEW STYLE:

Final Review:

Community Review Board

Approval:

User Sign-Off

SHARE:

ID: 30043699