se/SE/se-lib/SchemaHelper.php

<?php

// SE/schema/p5/types.xsd

class SchemaHelper {

  /**
   * @param array $mysqlSchema - from infomration_schema.columns
   * @param required array $mysqlSchema['DATA_TYPE']
   * @param required array $mysqlSchema['NUMERIC_PRECISION']
   * @param required array $mysqlSchema['NUMERIC_SCALE']
   * @param required array $mysqlSchema['COLUMN_TYPE']
   * @param required array $mysqlSchema['IS_NULLABLE']
   */
  public static function convertMysqlTypeToSchemaXsd($mysqlSchema, $fieldName = '') {
    $restrictions = [];
    switch ($mysqlSchema['DATA_TYPE']) {
      case 'text':
      case 'char':
      case 'varchar': {
        if (!empty($mysqlSchema['CHARACTER_MAXIMUM_LENGTH'])) $restrictions['maxLength'] = $mysqlSchema['CHARACTER_MAXIMUM_LENGTH'];
        $xsdType = 'xsd:string';
      } break;
      case 'tinyint':
      case 'smallint':
      case 'mediumint':
      case 'bigint':
      case 'int': {
        if (!empty($mysqlSchema['NUMERIC_PRECISION'])) $restrictions['totalDigits'] = $mysqlSchema['NUMERIC_PRECISION'];
        $xsdType = 'xsd:integer';
      } break;
      case 'numeric':
      case 'double':
      case 'float':
      case 'real':
      case 'decimal': {
        if (!empty($mysqlSchema['NUMERIC_PRECISION'])) $restrictions['totalDigits'] = $mysqlSchema['NUMERIC_PRECISION'];
        if (!empty($mysqlSchema['NUMERIC_SCALE'])) $restrictions['fractionDigits'] = $mysqlSchema['NUMERIC_SCALE'];
        $xsdType = 'xsd:decimal';
      } break;
      case 'datetime': $xsdType = 'xsd:dateTime'; break;
      case 'year': $xsdType = 'xsd:gYear'; break;
      case 'todo__year_month': $xsdType = 'xsd:gYearMonth'; break;
      case 'time': $xsdType = 'xsd:time'; break;
      case 'date': $xsdType = 'xsd:date'; break;
      case 'polygon': $xsdType = 'gml:PolygonPropertyType'; break;
      case 'linestring': $xsdType = 'gml:LineStringPropertyType'; break;
      case 'point': $xsdType = 'gml:PointPropertyType'; break;
      case 'geometry': $xsdType = 'gml:GeometryPropertyType'; break;
      case 'enum': {
        $xsdType = 'p5:enum';
        $restrictions['enumeration'] = [];
        $values = explode(',', str_replace(["'",'"'], '', substr($mysqlSchema['COLUMN_TYPE'], 5, -1)));
    		foreach ($values as $val) {
    			$restrictions['enumeration'][$val] = $val;
    		}
      } break;
      case 'tinyblob': $xsdType = 'xsd:base64Binary'; break;
      case 'mediumblob': $xsdType = 'xsd:base64Binary'; break;
      case 'longblob': $xsdType = 'xsd:base64Binary'; break;
      case 'blob': $xsdType = 'xsd:base64Binary'; break;
      default: throw new Exception("Not implemented mysql schema '" . json_encode($mysqlSchema) . "'");
    }

    if ('YES' === $mysqlSchema['IS_NULLABLE']) $restrictions['nillable'] = true;

    // TODO: convert to p5/types.xsd
    switch ($xsdType) {
      case 'xsd:decimal': {
        if (12 == $restrictions['totalDigits'] && 2 == $restrictions['fractionDigits']) {
          // <xsd:simpleType name="price">
          //   <xsd:restriction base="xsd:decimal">
          //     <xsd:totalDigits value="12"/>
          //     <xsd:fractionDigits value="2"/>
          unset($restrictions['totalDigits']);
          unset($restrictions['fractionDigits']);
          $xsdType = 'p5:price';
        }
      } break;
      case 'xsd:string': {
        if (255 == $restrictions['maxLength']) {
          unset($restrictions['maxLength']);
          $xsdType = 'p5:string';
        } else if (1000 == $restrictions['maxLength']) {
          unset($restrictions['maxLength']);
          $xsdType = 'p5:longString';
        }
      } break;
    }

    return [
      'type' => $xsdType,
      'restrictions' => $restrictions,
      '__DBG__' => [ 'COLUMN_TYPE' => $mysqlSchema['COLUMN_TYPE'] ],
    ];
  }

  public static function convertMysqlSchemaToXsdRestrictions($mysqlType, $fieldName = '') {
    $restrictions = [];
    // enumeration	Defines a list of acceptable values
    // fractionDigits	Specifies the maximum number of decimal places allowed. Must be equal to or greater than zero
    // length	Specifies the exact number of characters or list items allowed. Must be equal to or greater than zero
    // maxExclusive	Specifies the upper bounds for numeric values (the value must be less than this value)
    // maxInclusive	Specifies the upper bounds for numeric values (the value must be less than or equal to this value)
    // maxLength	Specifies the maximum number of characters or list items allowed. Must be equal to or greater than zero
    // minExclusive	Specifies the lower bounds for numeric values (the value must be greater than this value)
    // minInclusive	Specifies the lower bounds for numeric values (the value must be greater than or equal to this value)
    // minLength	Specifies the minimum number of characters or list items allowed. Must be equal to or greater than zero
    // pattern	Defines the exact sequence of characters that are acceptable
    // totalDigits	Specifies the exact number of digits allowed. Must be greater than zero
    // whiteSpace	Specifies how white space (line feeds, tabs, spaces, and carriage returns) is handled
    if (!empty($mysqlType['CHARACTER_MAXIMUM_LENGTH'])) $restrictions['maxLength'] = $mysqlType['CHARACTER_MAXIMUM_LENGTH'];
    if (!empty($mysqlType['NUMERIC_PRECISION'])) $restrictions['totalDigits'] = $mysqlType['NUMERIC_PRECISION'];
    if (!empty($mysqlType['NUMERIC_SCALE'])) $restrictions['fractionDigits'] = $mysqlType['NUMERIC_SCALE'];
    // date	Defines a date value
    // dateTime	Defines a date and time value
    // duration	Defines a time interval
    // gDay	Defines a part of a date - the day (DD)
    // gMonth	Defines a part of a date - the month (MM)
    // gMonthDay	Defines a part of a date - the month and day (MM-DD)
    // gYear	Defines a part of a date - the year (YYYY)
    // gYearMonth	Defines a part of a date - the year and month (YYYY-MM)
    // time	Defines a time value
    if ('YES' === $mysqlType['IS_NULLABLE']) $restrictions['nillable'] = true;
    if (self::mysqlTypeIsEnum($mysqlType) && !empty($mysqlType['COLUMN_TYPE'])) {
      $restrictions['enumeration'] = [];
      $values = explode(',', str_replace(["'",'"'], '', substr($mysqlType['COLUMN_TYPE'], 5, -1)));
  		foreach ($values as $val) {
  			$restrictions['enumeration'][$val] = $val;
  		}
    }
    $restrictions['__DBG__']['COLUMN_TYPE'] = $mysqlType['COLUMN_TYPE'];
    return $restrictions;
  }
  /**
   * @param mixed $mysqlType - string or array from INFORMATION_SCHEMA.COLUMNS
   */
  public static function convertMysqlTypeToXsd($mysqlType, $fieldName = '') {
    // TODO: check schemaClass
    // Lib::loadClass('Schema_TableFactory')
    // $srvName = $_SERVER['SERVER_NAME'];
		// $this->_schemaClass = Schema_TableFactory::build($this->_name, $this->_db, $srvName);

    // if ($this->_schemaClass) {
		// 	$schemaType = $this->_schemaClass->getType($fieldName);
		// 	if ($schemaType) return $schemaType;
		// }
		$fldType = 'xsd:string';
		if ('A_RECORD_UPDATE_DATE' == $fieldName) $fldType = 'xsd:string';// TODO: fix date format
		if ('A_RECORD_CREATE_DATE' == $fieldName) $fldType = 'xsd:string';// TODO: fix date format
		if (self::mysqlTypeIsInteger($mysqlType)) $fldType = 'xsd:integer';
		else if (self::mysqlTypeIsDecimal($mysqlType)) $fldType = 'xsd:decimal';
		else if (self::mysqlTypeIsDateTime($mysqlType)) $fldType = 'xsd:dateTime';
		else if (self::mysqlTypeIsDateYear($mysqlType)) $fldType = 'xsd:gYear';
		else if (self::mysqlTypeIsDateYearMonth($mysqlType)) $fldType = 'xsd:gYearMonth';
		else if (self::mysqlTypeIsTime($mysqlType)) $fldType = 'xsd:time';
		else if (self::mysqlTypeIsDate($mysqlType)) $fldType = 'xsd:date';
    else if (self::mysqlTypeIsGeometryPolygon($mysqlType)) $fldType = 'gml:PolygonPropertyType';
		else if (self::mysqlTypeIsGeometryLine($mysqlType)) $fldType = 'gml:LineStringPropertyType';
		else if (self::mysqlTypeIsGeometryPoint($mysqlType)) $fldType = 'gml:PointPropertyType';
    else if (self::mysqlTypeIsGeometry($mysqlType)) $fldType = 'xsd:GeometryPropertyType';
		else if (self::mysqlTypeIsEnum($mysqlType)) $fldType = 'p5:enum';
		else if (self::mysqlTypeIsBinary($mysqlType)) $fldType = 'xsd:base64Binary';
    DBG::log(['convertMysqlTypeToXsd', '$mysqlType' => $mysqlType, 'getMysqlType' => self::getMysqlType($mysqlType), 'return' => $fldType]);
		return $fldType;
  }
  /**
   * @param mixed $mysqlType - string or array from INFORMATION_SCHEMA.COLUMNS
   */
  public static function getMysqlType($mysqlType) {
    if (is_array($mysqlType)) {
      return $mysqlType['DATA_TYPE'];
    }
    if (false !== ($pos = strpos($mysqlType, '('))) return substr($mysqlType, 0, $pos);
    return $mysqlType;
  }
  public static function mysqlTypeIsInteger($mysqlType) {
    return (in_array(self::getMysqlType($mysqlType), ['int', 'tinyint', 'smallint', 'mediumint', 'bigint']));
  }
  public static function mysqlTypeIsDecimal($mysqlType) {
    return (in_array(self::getMysqlType($mysqlType), ['decimal', 'numeric', 'double', 'float', 'real']));
  }
  public static function mysqlTypeIsDateTime($mysqlType) {
    return (in_array(self::getMysqlType($mysqlType), ['datetime']));
  }
  public static function mysqlTypeIsDateYear($mysqlType) {
    return (in_array(self::getMysqlType($mysqlType), ['year']));
  }
  public static function mysqlTypeIsDateYearMonth($mysqlType) {
    return (in_array(self::getMysqlType($mysqlType), ['todo__year_month']));
  }
  public static function mysqlTypeIsTime($mysqlType) {
    return (in_array(self::getMysqlType($mysqlType), ['time']));
  }
  public static function mysqlTypeIsDate($mysqlType) {
    return (in_array(self::getMysqlType($mysqlType), ['date', 'datetime']));
  }
  public static function mysqlTypeIsGeometryLine($mysqlType) {
    return (in_array(self::getMysqlType($mysqlType), ['linestring']));
  }
  public static function mysqlTypeIsGeometryPolygon($mysqlType) {
    return (in_array(self::getMysqlType($mysqlType), ['polygon']));
  }
  public static function mysqlTypeIsGeometryPoint($mysqlType) {
    return (in_array(self::getMysqlType($mysqlType), ['point']));
  }
  public static function mysqlTypeIsGeometry($mysqlType) {
    return (in_array(self::getMysqlType($mysqlType), ['geometry', 'polygon', 'linestring', 'point']));
  }
  public static function mysqlTypeIsEnum($mysqlType) {
    return (in_array(self::getMysqlType($mysqlType), ['enum']));
  }
  public static function mysqlTypeIsBinary($mysqlType) {
    return (in_array(self::getMysqlType($mysqlType), ['blob', 'tinyblob', 'mediumblob', 'longblob']));
  }

}