public function testRealSQLWithTwoDependencies()
{
$sql = "create or replace view vertex_fact_zip_borrower_credit_change as\n\n\t\tselect bcc.id as zip_credit_change_id,\n\t\t COALESCE(bcc.trans_id,0) as trans_id,\n\t\t bcc.fund_account_id,\n\t\t -- we try to capture the local and USD amounts from the disbursal or repayment_collected references in item_id or\n\t\t -- ref_id and round it to 2 decimal places since it is a currency amount\n\t\t cast(COALESCE(d_on_ref.amount, d_on_item.amount, rc_on_ref.amount, rc_on_item.amount, bcc.price) as numeric(36,2)) as local_price,\n\t\t cast(COALESCE(d_on_ref.amount_usd, d_on_item.amount_usd, rc_on_ref.amount_usd, rc_on_item.amount_usd, bcc.price) as numeric(36,2)) as usd_price,\n\t\t dim_cct.credit_change_type_id,\n\t\t bcc.create_time,\n\t\t\tTO_CHAR(TO_TIMESTAMP(bcc.create_time), 'YYYYMMDD')::INT as create_day_id,\n\t\t bcc.effective_time,\n\t\t\tTO_CHAR(TO_TIMESTAMP(bcc.effective_time), 'YYYYMMDD')::INT as effective_day_id,\n\t\t bcc.item_id,\n\t\t bcc.ref_id,\n\t\t fx.id as fx_rate_id,\n\t\t bcc.changer_id,\n\t\t case\n\t\t\t when bcc.changer_id = 0 then 'system'\n\t\t\t\twhen bcc.changer_id = bcc.fund_account_id then 'user'\n\t\t\t\telse 'admin'\n\t\t\tend as changer_type,\n\t\t\tcast(bcc.new_balance as numeric(36,2)) as new_balance,\n\t\t\t-- we try to capture the currency from the disbursal or repayment_collected references in item_id or ref_id, with a\n\t\t\t-- default value of USD\n\t\t COALESCE(d_on_ref.currency, d_on_item.currency, rc_on_ref.currency, rc_on_item.currency, 'USD') as currency,\n\t\t l.country_id,\n\t\t case\n\t\t\t when fa.contract_entity_id is null then (select id from vertex_dim_accounting_category where accounting_category = 'self_directed')\n\t\t\t\telse (select id from vertex_dim_accounting_category where accounting_category = 'managed_account')\n\t\t\tend as accounting_category_id\n\t\tfrom verse.verse_ods_zip_credit_change bcc\n\t\tinner join verse.verse_ods_zip_fund_accounts fa on fa.id = bcc.fund_account_id\n\t\tinner join vertex_dim_credit_change_type dim_cct on dim_cct.credit_change_type_id = bcc.type_id and dim_cct.source_table_name = 'zip.credit_change'\n\t\tleft join verse.verse_ods_zip_disbursal d_on_ref ON d_on_ref.id = bcc.ref_id AND dim_cct.ref_refers_to = 'disbursal' AND dim_cct.fx_rate_from = 'disbursal'\n\t\tleft join verse.verse_ods_zip_disbursal d_on_item ON d_on_item.id = bcc.item_id AND dim_cct.item_refers_to = 'disbursal' AND dim_cct.fx_rate_from = 'disbursal'\n\t\tleft join verse.verse_ods_zip_repayment_collected AS rc_on_ref ON rc_on_ref.id = bcc.ref_id AND dim_cct.ref_refers_to = 'repayment_collected' AND dim_cct.fx_rate_from = 'repayment_collected'\n\t\tleft join verse.verse_ods_zip_repayment_collected AS rc_on_item ON rc_on_item.id = bcc.item_id AND dim_cct.item_refers_to = 'repayment_collected' AND dim_cct.fx_rate_from = 'repayment_collected'\n\t\t-- loan_id can come from either disbursal or repayment_collected\n\t\tleft join verse.verse_ods_zip_loans l on l.id =\n\t\tcase\n\t\t when dim_cct.ref_refers_to = 'disbursal' then d_on_ref.loan_id\n\t\t when dim_cct.item_refers_to = 'disbursal' then d_on_item.loan_id\n\t\t when dim_cct.ref_refers_to = 'repayment_collected' then rc_on_ref.loan_id\n\t\t when dim_cct.item_refers_to = 'repayment_collected' then rc_on_item.loan_id\n\t\tend\n\t\t-- same for fx_rate_id\n\t\tleft join verse.verse_ods_zip_fx_rates fx ON fx.id =\n\t\tcase\n\t\t when dim_cct.ref_refers_to = 'disbursal' then d_on_ref.fx_rate_id\n\t\t when dim_cct.item_refers_to = 'disbursal' then d_on_item.fx_rate_id\n\t\t when dim_cct.ref_refers_to = 'repayment_collected' then rc_on_ref.fx_rate_id\n\t\t when dim_cct.item_refers_to = 'repayment_collected' then rc_on_item.fx_rate_id\n\t\tend\n\t\twhere dim_cct.fx_rate_from in ('disbursal', 'repayment_collected') or dim_cct.fx_rate_from is null\n\t\tand (dim_cct.ref_refers_to = 'disbursal' or dim_cct.ref_refers_to = 'repayment_collected' or dim_cct.item_refers_to = 'disbursal' or dim_cct.item_refers_to = 'repayment_collected')\n\t\tand fa.type = 'BorrowerFundAccount';\n\t\t";
$expected_dependencies = array("vertex_dim_accounting_category", "vertex_dim_credit_change_type");
$dependency_finder = new DependencyFinder();
$dependencies = $dependency_finder->getVertexFactDimDependencies("vertex_fact_zip_borrower_credit_change", $sql);
$this->assertSame($expected_dependencies, $dependencies);
}
public function run($source_schema)
{
exec("vsql -c \"select table_name, view_definition from v_catalog.views where table_name like 'vertex_%' and table_schema='" . $source_schema . "';\" -A -t 2>&1", $outputAndErrors, $return_value);
print $outputAndErrors . "\n";
$views = array();
foreach ($outputAndErrors as $view_line) {
// make sure we only split this into 2 parts (name and sql)
$fields = explode("|", $view_line, 2);
$views[$fields[0]] = $fields[1];
}
// print_r($views);
$fact_and_dim_nodes = array();
// for each view, find its dependencies and build the graph for it
foreach ($views as $view_name => $view_sql) {
// get the dependencies for the view
$dependency_finder = new DependencyFinder();
$references = $dependency_finder->getVertexFactDimDependencies($view_name, $view_sql);
// if we don't already know about the node, add it in
if (!array_key_exists($view_name, $fact_and_dim_nodes)) {
$fact_and_dim_nodes[$view_name] = new Node($view_name);
}
// iterate over the dependencies to build the graph
foreach ($references as $referenced_fact_or_dim) {
// only add dependencies that are views
if (array_key_exists($referenced_fact_or_dim, $views)) {
if (!array_key_exists($referenced_fact_or_dim, $fact_and_dim_nodes)) {
$fact_and_dim_nodes[$referenced_fact_or_dim] = new Node($referenced_fact_or_dim);
}
$fact_and_dim_nodes[$view_name]->addEdge($fact_and_dim_nodes[$referenced_fact_or_dim]);
}
}
}
//print_r($fact_and_dim_nodes);
$resolved = array();
$unresolved = array();
foreach ($fact_and_dim_nodes as $fact_or_dim_node) {
$fact_or_dim_node->resolveDependencies($fact_or_dim_node, $resolved, $unresolved);
}
$view_materializer = new ViewMaterializer($this->db);
print "--------- dependency list ---------\n";
foreach ($resolved as $node) {
$node->printNodeAndDependencies();
}
print "--------- materializing... ---------\n";
//////////////////
/*
// if we want to materialize from one schema to another, we need to copy tables over too
$result = $this->db->query("select table_name
from v_catalog.tables
where table_schema='" . $source_schema . "'");
$table_name_rows = $result->fetchAll();
foreach($table_name_rows as $table_name_row) {
$full_table_name = $table_name_row['table_name'];
print("copying table: " . $full_table_name . " | ");
$start = time();
$view_materializer->materialize($full_table_name, $this->destination_schema);
$end = time();
print($end - $start . " second(s)\n");
}
*/
///////////////
// materialize the views in the resolved order in the default schema
foreach ($resolved as $node) {
$view_name = $node->getName();
print "materializing: " . $view_name . " | ";
$start = time();
$view_materializer->materializeInPlace($view_name);
$end = time();
print $end - $start . " second(s)\n";
}
}
public function run($path)
{
$facts_and_dims = array();
$Directory = new \RecursiveDirectoryIterator($path);
$Iterator = new \RecursiveIteratorIterator($Directory);
$files = new \RegexIterator($Iterator, '/^.+\\.sql$/i', \RecursiveRegexIterator::GET_MATCH);
$views = array();
$view_to_file = array();
//$fact_dim_regex = "/vertex_dim_[a-zA-Z0-9_]*\b|vertex_fact_[a-zA-Z0-9_]*\b/";
$fact_dim_regex = "/(vertex_[a-zA-Z0-9_]*)\\.sql/";
foreach ($files as $file) {
$filename = $file[0];
//echo "--------- " . $filename . PHP_EOL;
$name_match = '';
preg_match($fact_dim_regex, $filename, $name_match);
$fact_or_dim_name = substr($name_match[0], 0, strlen($name_match[0]) - 4);
//print($fact_or_dim_name . "\n\n");
$file_content = file_get_contents($filename);
//print($file_content . "\n\n\n");
// get rid of /* */ comments
$file_content = preg_replace("/(\\/\\*([^*]|(\\*+[^*\\/]))*\\*+\\/)/", '', $file_content);
// get rid of // comments
$file_content = preg_replace("/(\\-\\-.*)/", '', $file_content);
//print($file_content . "\n\n\n");
$views[$fact_or_dim_name] = $file_content;
$view_to_file[$fact_or_dim_name] = $filename;
}
//print_r($views);
//print_r($view_to_file);
//exit;
$fact_and_dim_nodes = array();
// for each view, find its dependencies and build the graph for it
foreach ($views as $view_name => $view_sql) {
// get the dependencies for the view
$dependency_finder = new DependencyFinder();
$references = $dependency_finder->getVertexFactDimDependencies($view_name, $view_sql);
// if we don't already know about the node, add it in
if (!array_key_exists($view_name, $fact_and_dim_nodes)) {
$fact_and_dim_nodes[$view_name] = new Node($view_name);
}
// iterate over the dependencies to build the graph
foreach ($references as $referenced_fact_or_dim) {
// only add dependencies that are views
if (array_key_exists($referenced_fact_or_dim, $views)) {
if (!array_key_exists($referenced_fact_or_dim, $fact_and_dim_nodes)) {
$fact_and_dim_nodes[$referenced_fact_or_dim] = new Node($referenced_fact_or_dim);
}
$fact_and_dim_nodes[$view_name]->addEdge($fact_and_dim_nodes[$referenced_fact_or_dim]);
}
}
}
//print_r($fact_and_dim_nodes);
$resolved = array();
$unresolved = array();
foreach ($fact_and_dim_nodes as $fact_or_dim_node) {
$fact_or_dim_node->resolveDependencies($fact_or_dim_node, $resolved, $unresolved);
}
// print("--------- dependency list ---------\n");
foreach ($resolved as $node) {
//$node->printNodeAndDependencies();
$view_name = $node->getName();
print $view_to_file[$view_name] . "\n";
}
}