<?
/*************************************************************
CONFIGURABLE PARAMETERS
**********************************************************/
/**
* These can also be configured per-query below, if more granularity is needed
*/
$from_db_connection = array('localhost', 'username', 'password', 'db-instance');
$to_db_connection = array('localhost', 'username', 'password', 'db-instance');
/**
* We either output by printing insert statements to the screen for testing(true),
* or by running the insert queries directly on the target db(false-default). Set this
* to false when you're ready to import for real
*/
$fake_inserts = false;
/**
* Max rows allows you to only run a certain number of queries per table, for testing. Leave
* this set to 0 for the real run
*/
$max_rows = 0;
/**
* If true, script will exist on any failed insert query.
* If false, will continue to run even if queries fail and insert the other records.
*/
$stop_on_failed_query = false;
/**
* $tablemap is an array containing one entry for each table to be converted.
* Note that tables don't necessarily have to be 1:1 to make this work, since
* the source query can use whatever sort of joins needed to set up the source
* fields. There must, of course, be exactly one output table.
*
* <p>Here are the fields for each conversion element:
* <ul>
* <li>connection info - an array containin the source database and target database. It contains two
* elements, each is an array containing array( host, username, password, db_instance )
* <li>source_query - the query to be run against source db, set it up however you like, the field names should correspond with the 'fields' array's keys
* <li>fields - see below
* <li>defaults - default values to apply to any fields in target table, overwritten by values in 'fields'
* </ul>
*
* <p>If you just want to create insert statements with the data, use dest_table. If you
* need custom queries or updates to run, you can specify dest_query. Values for any output fields
* can be substituted using {field_name} in the query.
*
* <p>The 'fields' element is an indexed array. The key is the source table's column name. The value is either a string indicating the name of hte column in the destination table, or an array containing exactly two elements: 1) a function to call, which will be passed the value from source table, 2) the destination column name
*
* <p>The function is passed a host of arguments, in order:
* <ol>
* <li>value from originating column
* <li>entire data row
* <li>original column name
* <li>destination column name
* </ol>
*
* <p>To list the same field twice (since the keys would be identical) you can use an array index which will be stripped from the column name before querying, such as "field_id[1]", "field_id[2]", which both refer to a column named "field_id"
*/
$tablemap = array();
/**
* This example is a simple, straight copy from one table to another
*/
$tablemap[] = array(
"connection_info" => array(
"source" => $from_db_connection,
"dest" => $to_db_connection
),
"source_query" => 'select * from source_table',
"dest_table" => "destination_table",
'fields' => array(
'source1' => 'dest1',
'source2' => 'dest2',
'posted' => array('toSqlDate', 'date'), // convert unix timestamp to sql date
'id[1]' => 'id', // here we set two dest fields from "id", using array index
'id[2]' => array('getParentId', 'parent') // another custom function
),
'defaults' => array(
'dest3' => '0',
'dest4' => ''
)
);
/*
* This example runs a customized query instead of a normal insert
*/
$tablemap[] = array(
"connection_info" => array(
"source" => $from_db_connection,
"dest" => $to_db_connection
),
"source_query" => '
select
count(*) as src_total, category
from
source_table_1 s1, source_table_2 s2
where
s1.id = s2.id AND s2.id > 100
group by
category',
"dest_query" => 'update dest_table set total = {dest_total} where catid = {cat}',
'fields' => array(
'category' => 'cat',
'src_total' => 'dest_total'
),
'defaults' => array() // these are still applicable, just didn't need any
);
/*****************************************************************************
USER FUNCTIONS (methods used by the 'fields' array to convert values)
***************************************************************************/
/**
* Just an example function that creates a value by combining two cols from source table
*/
function getParentId($src_id, $data_row, $src_col_name, $dest_col_name) {
if( $src_id > 0 ) {
// if it exists, use it
return $src_id;
}
// otherwise, create one
return $data_row["number_1"] + $data_row["number_2"];
}
/**
* Example function to create an incremental id for the result data
*/
function getIncrementalId() {
global $currentIncId;
return ++$currentIncId;
}
$currentIncId = 0;
/**
* Converts a unix timestamp to a sql datetime
*/
function toSqlDate($utime) {
return date("Y-m-d H:i:s", $utime);
}
?> |
