<?php
/**
 *  Import a VOTable into a MySQL DB table using simplexml_load_file.
 *
 *
 * Usage:
 *   $data = vot2dbt( $votable, $mysqli, $tabname='', $verbose=false );
 *
 * @category	MySQL data import
 * @author	Luciano Nicastro @ INAF-OAS
 * @version	1.1, 18/09/2019
 * @see		http://www.ivoa.net/documents/VOTable/20091130/REC-VOTable-1.2.html
 * @since	1.0
 */

/**
 * Perform a query on an open MySQLI connection.
 * 
 * @param object	$mysqli  The MySQLi connection object.
 * @param string	$qry  The input SQL query.
 * @param bool		$verbose  If true be verbose.
 *
 * @return array|int|bool|null It depends on the type of query and returned data.  
 */

function doqry( $mysqli, $qry, $verbose=false )
{
    if ($verbose) echo "Query = $qry\n";
    $res = $mysqli->query( $qry );
    if ( !$res ) {
      $a = debug_backtrace();
      if ( isset($a[1]) ) { $a = $a[1]['line']; }
      else if ( isset( $a[0]) ) { $a0 = $a[0]['line']; }
      else { $a0 = "???"; }
      echo 'ERROR @ '. $a0 .' : ('.  $mysqli->errno .")\n". $mysqli->error ."\n\n";
      echo "Query = $qry\n";
      return null;
    }

    if ( preg_match("/insert/i", $qry) ) { return $mysqli->insert_id; }
    if ( preg_match("/delete/i", $qry) ) { return true; }
    if ( preg_match("/update/i", $qry) ) { return $mysqli->affected_rows; }
    if ( !is_object($res) ){ return null; }

    $cnt = -1;
    $a = array();
    $res->data_seek(0);
    while ( $row = $res->fetch_assoc() ) {
      $cnt++;
      foreach( $row as $k=>$v ){ $a[$cnt][$k] = $v; }
    }

    if ($verbose) echo "Selected $cnt rows.\n";
    return $a;
}


/**
 *  Use pathinfo to get the stripped file name, or 'noname' if empty.
 *  Note: replace any "." other than the last extension into "_".
 *
 * @param string	$filename  The input file path.
 *
 * @return string	The clean filename.
 */

function rname( $filename )
{
  if ( empty($filename) ) return 'noname';
  $rname = pathinfo($filename, PATHINFO_FILENAME);
  if ( strpos($rname, '.') !== false ) { str_replace('.', '_', $rname); }

  return $rname;
}


/**
 *  From XML header read data types to MySQL types.
 *
 *  Note:
 *    Only consider length of char columns.
 *    Complex and bit data types are missing.
 *    Use constant char length for fields shorter than 17 chars.
 *    (Can/should be customised ...)
 * 
 * @param string[]	$dtypes  The list of VOTable fields data types.
 * @param int[]		$len  The length of the char fields.
 *
 * @return string[]	The array with the corresponding MySQL types.
 */

function my_datatypes( $dtypes, $len=0 )
{
  $result = array();

  for ($i=0, $j=0; $i < count($dtypes); ++$i)
  {
    $t = strtolower($dtypes[$i]);

    switch ($t) {
      case 'char':
	if ($len[$j] > 16)
	  $result[] = 'varchar('. $len[$j] .')';
	else
	  $result[] = 'char('. $len[$j] .')';
	$j++;
	break;
      case 'float':
	$result[] = 'float';
	break;
      case 'double':
	$result[] = 'double';
	break;
      case 'long':
	$result[] = 'bigint';
	break;
      case 'int':
	$result[] = 'int';
	break;
      case 'short':
	$result[] = 'smallint';
	break;
      case 'decimal':
	$result[] = 'decimal';
	break;
      case 'boolean':
	$result[] = 'tinyint';
	break;
      case 'unsignedbyte':
	$result[] = 'tinyint';
	break;
      case 'unicodechar':
	if ($len[$j] > 16)
	  $result[] = 'varchar('. $len[$j]*2 .')';
	else
	  $result[] = 'char('. $len[$j]*2 .')';
	$j++;
	break;
      default:  // just to avoid to exit
	$result[] = 'varchar(64)';
    }
  }

  return $result;
}


/**
 *  Import a VOTable into the MySQL table.
 *
 * @param string	$votable  The input, full-path, VOTable file.
 * @param object	$mysqli   The MySQLi connection object.
 * @param string	$tabname  The output table name (def. input VOTable basename).
 * @param bool		$verbose  If true be verbose.
 *
 * @return array	json object with the relevant query information (n_rows, n_fields, etc.).
 */

function vot2dbt( $votable, $mysqli, $tabname='', $verbose=false )
{
  $data = array();
  $data['status'] = 0;
  $data['errmsg'] = '';
  $data['votable'] = $votable;

// If not given then use the root file name as table name
  if ( empty($tabname) )
	$tabname = rname($votable);

  $data['tabname'] = $tabname;

  if ($verbose) echo 'Reading input file '. $votable ." ...\n";
  if (! file_exists($votable)) {
    $data['errmsg'] = 'File not found.';
    $data['status'] = -1;
    return json_encode( $data );
  }

  $xml = simplexml_load_file( $votable );
  $data['n_fields'] = count($xml->RESOURCE->TABLE->FIELD);


  $aux_id = 1;
  $auxnames = Array();
  $names = Array();
  $len = Array();


// The header
  foreach( $xml->RESOURCE->TABLE->FIELD as $k=>$v ) {

    foreach( $v->attributes() as $k1=>$v1 ) {
	    if ( $k1 == 'datatype' )
		$dtypes[] = $v1;
	    else if ( $k1 == 'name' ) {  // check for duplicates
		if ( in_array(strtolower($v1), $auxnames) ) {
			$names[] = $v1 ."_$aux_id";
			$aux_id++;
		} else
			$names[] = $v1;

		$auxnames[] = strtolower($v1);  // used to check for duplicated field names

	    } else if ( $k1 == 'arraysize' ) {  // check for array marker
		if ( strstr($v1, '*') )
			$len[] = substr($v1, 0, -1);  // remove "*"
		else
			$len[] = substr($v1, 0);
	    }
    }
  }

  $mydtype = my_datatypes($dtypes, $len);


// First drop the table
  $qry = 'drop table if exists `'. $tabname .'`';
  $s = doqry( $mysqli, $qry );

  $qry = 'create table `'. $tabname .'` (';
  for ( $i = 0; $i < count($names); $i++ )
	$qry .= '`'. $names[$i] .'`  '. $mydtype[$i] .',';

  $qry = substr( $qry, 0, -1 ) .') engine=MyISAM';

if ($verbose) echo "Creating table with query:\n$qry\n\n";

  $s = doqry( $mysqli, $qry );

 
// The data. We assume only one TABLEDATA block.
  $data['n_rows'] = count($xml->RESOURCE->TABLE->DATA->TABLEDATA->children());
  $qry_i = "insert into `$tabname` ";
  $val = 'values (';
  $i = 0;

if ($verbose) echo 'Will insert '. $data['n_rows'] .' rows, '. $data['n_fields'] .' columns each... ';

  foreach( $xml->RESOURCE->TABLE->DATA->TABLEDATA->children()->TR as $k=>$v ) {  // TR - rows
	foreach( $v->children() as $k1=>$v1 ) {  // TD - columns
		if ( is_numeric($v1) ) { $val .= "$v1,"; }
		else if ( is_array($v1) ) { $val .= "'',"; }
		else { $val .= "'$v1',"; }

	}
	$i++;

	$val = substr( $val, 0, -1 ) .'),(';
	if ( (($i+1) % 100) == 0 ) {  // insert 100 values per query - can be optimised
		$qry = $qry_i . substr($val, 0, -2);
		$s = doqry( $mysqli, $qry );
		$val = 'values (';
	}

  }  // end foreach row

  // The remaining rows
  if ( ($i % 100) != 0 ) {
	$qry = $qry_i . substr($val, 0, -2);
	$s = doqry( $mysqli, $qry );
  }

}
?>