DB Transfer

Overview

Dbxfer is a c program used to convert CPDMS data into files that can be loaded into CPDMS.NET. The conversion is dependent on the database structure of both CPDMS and CPDMS.NET. If either database structure is changed the dbxfer program must be updated accordingly.

Dbxfer source files, executables, documentation, and related files can be found in a subversion repository located here:
https://source2.kcr.uky.edu/svn/dbxfer/trunk

If you just want to browse the repository you can use trac with this url:
https://source2.kcr.uky.edu/trac/dbxfer


Instructions for using Dbxfer

1. Get a copy of the dbxfer executable for the appropriate operating system or build a copy from the source files.


2. Use mysqldump to create an sql dump of the database structure for the target CPDMS.NET database. Use cpdmsnet.sql for the filename of the database structure file. This structure file can be created with the following command:

mysqldump --no-data --no-create-db --skip-add-drop-table --skip-triggers --database <database_name> -uroot -p > cpdmsnet.sql

Copy the cpdmsnet.sql database structure file to the directory where you will be running dbxfer. If there is a previous copy of cpdmsnet.sql, you may want to rename it so it will be available for comparison to help determine database structure changes.

Cpdmsnet.sql will be used by dbxfer to determine if the database has changed. When you run dbxfer, if the database has changed then a message will be displayed to that effect. In this case dbxfer will need to be updated to match the database structure and recompiled. Tables in dbxfer.h will usually be the only code that needs to be changed.

If necessary there is debug code in dbxfer.c that can be uncommented to create a file containing database structure information for the CPDMS.NET and CPDMS structures that are currently implemented in dbxfer.


3. Run dbxfer using the appropriate Registry Type option for the database to be transferred. Type dbxfer with no parameters to see the usage statement. The usage statement is as follows:

Usage: dbxfer <options>

<options>:
Use of one of the following options is required:

-central : transfer data from a central registry database
-hospital : transfer data from a hospital registry database

Other allowed options:

-preserve_keys : copy existing keys for CSrcCaseId, LSrcClhxId, XSrcTexId, and TSrcTxId

Central specific features:

• Copies all case_text records for a single case directly to the General Remarks field of one CPDMS.net case text record.
• Processes hosp_hx and delete_list records

Hospital specific features:

• Converts case_text records into equivalent CPDMS.net case text records.
• Does not processes hosp_hx and delete_list records

Preserve keys option:

In default mode dbxfer sequentially assigns new keys to CSrcCaseId, OSrcCaseOtherId, LSrcClhxId, TSrcTxId, and XSrcTexId. This is needed for the CPDMS.NET software to properly handle these records. The -preserve_keys option can be used to create a database with the keys copied directly from the original database (when a key is 0 in the original database is will be given an assigned key). A database copy with keys preserved will not work properly using CPDMS.NET but can be used for analysis with MySQL tools and queries.

Dbxfer requires the same environment setup needed to run kcr. If when you run kcr and the correct database is accessed then you are ready to run dbxfer.

Dbxfer will create the following csv files:

case.csv
caseoth.csv
clabel.csv
class.csv
doc.csv
inst.csv
key.csv
master.csv
pat.csv
plabel.csv
text.csv
tx.csv
user.csv

If the central option is used the following files will also be created:

hosphx.csv
dellist.csv

4. Load the csv files into the desired database using an sql script.

The options needed to import a Linux file are as follows:

Fields Terminated By ||
Fields Enclosed By <NONE>
Escaped By \\
Lines Terminated By \n

as shown in this query snippet:

fields escaped by '\\' terminated by '||' lines terminated by '\n'

To import a DOS file use \r\n instead of \n for the "terminated by" option in the load script.

Below is an example load script for loading a Central database from linux load files. This example does not include loading of the master_data or user_data tables although these load files are available. When loading a copy of Central, the master_data and user_data tables should be retained if the target database is an existing database. If the target database is new then copy the master_data and user_data tables from an existing CPDMS.NET Central database so that CPDMS.NET user names and revision numbers currently in use will be available in the new database.

truncate table `0000000001`.`case_data`;
truncate table `0000000001`.`case_other`;
truncate table `0000000001`.`case_text`;
truncate table `0000000001`.`case_tx`;
truncate table `0000000001`.`case_udd_label`;
truncate table `0000000001`.`class_hx`;
truncate table `0000000001`.`death_cert`;
truncate table `0000000001`.`delete_list`;
truncate table `0000000001`.`doc_list`;
truncate table `0000000001`.`hosp_hx`;
truncate table `0000000001`.`inst_list`;
truncate table `0000000001`.`naaccr_tx`;
truncate table `0000000001`.`next_key`;
truncate table `0000000001`.`pat_data`;
truncate table `0000000001`.`pat_udd_label`;
truncate table `0000000001`.`report_cache`;
truncate table `0000000001`.`reserved_acc_no`;
truncate table `0000000001`.`session_data`;

load data local infile '/kcr/dbxfer/central/case.csv' into table `0000000001`.`case_data` fields escaped by '\\' terminated by '||' lines terminated by '\n';
load data local infile '/kcr/dbxfer/central/caseoth.csv' into table `0000000001`.`case_other` fields escaped by '\\' terminated by '||' lines terminated by '\n';
load data local infile '/kcr/dbxfer/central/clabel.csv' into table `0000000001`.`case_udd_label` fields escaped by '\\' terminated by '||' lines terminated by '\n';
load data local infile '/kcr/dbxfer/central/class.csv' into table `0000000001`.`class_hx` fields escaped by '\\' terminated by '||' lines terminated by '\n';
load data local infile '/kcr/dbxfer/central/dellist.csv' into table `0000000001`.`delete_list` fields escaped by '\\' terminated by '||' lines terminated by '\n';
load data local infile '/kcr/dbxfer/central/doc.csv' into table `0000000001`.`doc_list` fields escaped by '\\' terminated by '||' lines terminated by '\n';
load data local infile '/kcr/dbxfer/central/hosphx.csv' into table `0000000001`.`hosp_hx` fields escaped by '\\' terminated by '||' lines terminated by '\n';
load data local infile '/kcr/dbxfer/central/inst.csv' into table `0000000001`.`inst_list` fields escaped by '\\' terminated by '||' lines terminated by '\n';
load data local infile '/kcr/dbxfer/central/key.csv' into table `0000000001`.`next_key` fields escaped by '\\' terminated by '||' lines terminated by '\n';
load data local infile '/kcr/dbxfer/central/pat.csv' into table `0000000001`.`pat_data` fields escaped by '\\' terminated by '||' lines terminated by '\n';
load data local infile '/kcr/dbxfer/central/plabel.csv' into table `0000000001`.`pat_udd_label` fields escaped by '\\' terminated by '||' lines terminated by '\n';
load data local infile '/kcr/dbxfer/central/text.csv' into table `0000000001`.`case_text` fields escaped by '\\' terminated by '||' lines terminated by '\n';
load data local infile '/kcr/dbxfer/central/tx.csv' into table `0000000001`.`case_tx` fields escaped by '\\' terminated by '||' lines terminated by '\n';