Mark Newnham - 2004-11-05

I've been taking a hard look at ExtractSchema, and I've a few suggestions, with fixes.

1. Change 'SELECT * FROM $table WHERE -1' to 'SELECT * FROM $table WHERE 1=0'. The first version is invalid SQL in at least 3 of the databases I've tested against. (I believe others have suggested this as well).

2. Improving the 'size=' processing.
  a. Ignore the size statement except for metatypes
C, N, and I.
  b. for N metatypes, add the scale.
  c. for I metatypes, create the field as e.g. type="I4", instead of type="I" size="4". Oracle (at least) chokes on the second construct.

Heres some ExtractSchema code for this:
foreach( $fields as $details ) {
  $extra = '';
  $content = array();
  $integersize = '';
  switch ($type = $rs->MetaType( $details )) {
    case 'C':
    if( $details->max_length > 0 ) {
      $extra .= ' size="' . $details->max_length . '"';
    }
    break;
    case 'N':
    $extra .= ' size="' . $details->max_length;
    if( $details->scale > 0 ) {
      $extra .= '.' . $details->scale;
    }
    $extra.='"';
    break;
    case 'I':
      $integersize = $details->max_length;
      break;
    default:
      break;
  }
  if( $details->primary_key ) {
   $content[] = '<KEY/>';
  } elseif( $details->not_null ) {
   $content[] = '<NOTNULL/>';
  }
  if( $details->has_default ) {
    $content[] = '<DEFAULT value="' . $details->default_value . '"/>';
  }
    
  if( $details->auto_increment ) {
   $content[] = '<AUTOINCREMENT/>';
  }
                       
  // this stops the creation of 'R' columns,
  // AUTOINCREMENT is used to create auto columns
  $details->primary_key = 0;
  $type = $rs->MetaType( $details );
  $schema .= '        <field name="' . $details->name . '" type="' . $type . $integersize . '"' . $extra . '>';

etc etc....