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";
     }
 }
Exemple #3
0
 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";
     }
 }