[Sommaire] [Imprimer]

   1  #!/usr/bin/php -q
   2  <?php
   3      /**************************************************************************\
   4      * eGroupWare - Setup - db-schema-processor - unit tests                    *
   5      * http://www.egroupware.org                                                *
   6      * --------------------------------------------                             *
   7      * Written by Ralf Becker <RalfBecker@outdoor-training.de>                  *
   8      * --------------------------------------------                             *
   9      *  This program is free software; you can redistribute it and/or modify it *
  10      *  under the terms of the GNU General Public License as published by the   *
  11      *  Free Software Foundation; either version 2 of the License, or (at your  *
  12      *  option) any later version.                                              *
  13      \**************************************************************************/
  14      /* $Id: schema_proc.php 16988 2004-09-19 10:38:11Z ralfbecker $ */
  15  
  16      // For security reasons we exit by default if called via the webserver
  17      if (isset($_SERVER['HTTP_HOST']))
  18      {
  19          die ('Access denied !!!');
  20      }
  21      // the used domain has to be given as first parameter if called on the commandline or as domain= on the url
  22      if (!isset($_GET['domain'])) 
  23      {
  24          $_GET['domain'] = $_SERVER['argc'] > 1 ? $_SERVER['argv'][1] : 'default';
  25      }
  26      $path_to_egroupware = realpath(dirname(__FILE__).'/../..');    //  need to be adapted if this script is moved somewhere else
  27  
  28      $phpgw_info = array(
  29          'flags' => array(
  30              'disable_Template_class' => True,
  31              'login'                  => True,
  32              'currentapp'             => 'login',
  33              'noheader'               => True,
  34          )
  35      );
  36      include ($path_to_egroupware.'/header.inc.php');
  37      $GLOBALS['phpgw_info']['server']['asyncservice'] = 'off';
  38  
  39      // now we should have a valid db-connection
  40      $adodb = &$GLOBALS['phpgw']->ADOdb;
  41      $db = &$GLOBALS['phpgw']->db;
  42  
  43      if (isset($_SERVER['HTTP_HOST'])) echo "<pre>\n";
  44      echo "Serverinfo: Domain $_GET[domain]: $db->Type($db->Database)\n";
  45      print_r($adodb->ServerInfo());
  46      
  47      // creating a schema_proc instance
  48      $schema_proc = CreateObject('phpgwapi.schema_proc',$db->Type);
  49      $schema_proc->debug = isset($_GET['debug']) ? $_GET['debug'] : ($_SERVER['argc'] > 2 ? $_SERVER['argv'][2] :  0);
  50      if ($schema_proc->debug > 1) $adodb->debug = true;
  51  
  52      // define a test-table to create
  53      $test_tables = array(
  54          'schema_proc_test' => array(
  55              'fd' => array(
  56                  'test_auto' => array('type' => 'auto'),
  57                  'test_int4' => array('type' => 'int','precision' => '4'),
  58                  'test_varchar' => array('type' => 'varchar','precision' => '128'),
  59                  'test_char' => array('type' => 'char','precision' => '10'),
  60                  'test_timestamp' => array('type' => 'timestamp','default'=>'current_timestamp'),
  61                  'test_text' => array('type' => 'text'),
  62                  'test_blob' => array('type' => 'blob'),
  63              ),
  64              'pk' => array('test_auto'),
  65              'fk' => array(),
  66              'ix' => array(array('test_char','test_varchar'),'test_varchar',array('test_text','options'=>array('mysql'=>'FULLTEXT','sapdb'=>false,'maxdb'=>false,'pgsql'=>false,'mssql'=>false))),
  67              'uc' => array('test_char')
  68          ),
  69      );
  70      $GLOBALS['phpgw_info']['apps']['login']['table_defs'] = &$test_tables;    // to be able to use db::insert and db::update
  71      
  72      // droping test-tables, if they are there from a previous failed run
  73      foreach($adodb->MetaTables() as $table)
  74      {
  75          $table = strtolower($table);
  76          if (strstr($table,'schema_proc')) 
  77          {
  78              $aSql = $schema_proc->dict->DropTableSQL($table);
  79              $schema_proc->ExecuteSqlArray($aSql,1,"DropTableSQL('%1') = %2",$table,$aSql);
  80          }
  81      }
  82  
  83      echo "Creating table(s):\n";
  84      foreach($test_tables as $name => $definition)
  85      {
  86          echo "$name:\n";    
  87          $schema_proc->CreateTable($name,$definition);
  88  
  89          $columns = $adodb->MetaColumns($name);
  90          if (!$columns || count($columns) <= 0)
  91          {
  92              die("\n\n!!! Table '$name' has NOT been created !!!\n\n");
  93          }
  94          else
  95          {
  96              // check if all columns are there
  97              foreach($definition['fd'] as $column => $data)
  98              {
  99                  check_column($column,$columns);
 100                  if (!isset($columns[$column]) && !isset($columns[strtoupper($column)]))
 101                  {
 102                      print_r($columns);
 103                      die ("\n\n!!! Column '$column' is missing !!!\n\n");
 104                  }
 105              }
 106              // check if all indexes are there
 107              $indexes = $adodb->MetaIndexes($name,true);
 108              if ($indexes !== False)
 109              {
 110                  foreach(array('ix','uc') as $kind)
 111                  {
 112                      foreach($definition[$kind] as $key => $idx)
 113                      {
 114                          check_index($idx,$kind=='uc',$indexes);
 115                      }
 116                  }
 117                  if (count($definition['pk'])) check_index($definition['pk'],True,$indexes);
 118              }
 119          }
 120          echo $indexes !== False ? "==> SUCCESS\n" : "==> unchecked\n";    
 121      }
 122      echo "Inserting some content:\n";
 123      $adodb->Execute("INSERT INTO schema_proc_test (test_int4,test_varchar,test_char) VALUES (1,'Hallo Ralf','0123456789')");
 124      //$adodb->Execute("INSERT INTO schema_proc_test (test_int4,test_varchar,test_char) VALUES (2,'Hallo wer noch?','9876543210')");
 125      $db->insert('schema_proc_test',array(
 126              'test_int4'        => 2,
 127              'test_varchar'    => 'Hallo wer noch?',
 128              'test_char'        => '9876543210',
 129              'test_text'    => 'This is a test-value for the text-column, insert-value',
 130              'test_blob'    => 'This is a test-value for the blob-column, insert-value',
 131          ),False,__LINE__,__FILE__);
 132      check_content($adodb->GetAll("SELECT * FROM schema_proc_test"),array(
 133              array(
 134                  'test_auto' => 1, 'test_int4' => 1, 'test_varchar' => 'Hallo Ralf','test_char' => '0123456789',
 135              ),
 136              array(
 137                  'test_auto' => 2, 'test_int4' => 2, 'test_varchar' => 'Hallo wer noch?','test_char' => '9876543210',
 138                  'test_text'    => 'This is a test-value for the text-column, insert-value',
 139                  'test_blob'    => 'This is a test-value for the blob-column, insert-value',
 140              ),
 141          ));
 142      echo "==> SUCCESS\n";
 143      
 144      echo "Updating the text- and blob-columns\n";
 145      // updating blob's and other columns
 146      $db->update('schema_proc_test',array(
 147              'test_int4'    => 99,
 148              'test_char' => 'abcdefghij',
 149              'test_text'    => 'This is a test-value for the text-column',
 150              'test_blob'    => 'This is a test-value for the blob-column',
 151          ),array('test_auto'=>1),__LINE__,__FILE__);
 152      // updating only the blob's
 153      $db->update('schema_proc_test',array(
 154              'test_text'    => 'This is a test-value for the text-column, 2.row',
 155              'test_blob'    => 'This is a test-value for the blob-column, 2.row',
 156          ),array('test_auto'=>2),__LINE__,__FILE__);
 157      // db::update uses UpdateBlob only for MaxDB at the moment, it works for MySql too, but fails for postgres with text / CLOB's
 158      // $adodb->UpdateBlob('schema_proc_test','test_text','This is a test-value for the text-column, 2.row','test_auto=2','CLOB');
 159      // $adodb->UpdateBlob('schema_proc_test','test_blob','This is a test-value for the blob-column, 2.row','test_auto=2','BLOB');
 160      check_content($adodb->GetAll("SELECT * FROM schema_proc_test"),array(
 161              array(
 162                  'test_auto' => 1, 'test_int4' => 99, 'test_varchar' => 'Hallo Ralf','test_char' => 'abcdefghij',
 163                  'test_text' => 'This is a test-value for the text-column',
 164                  'test_blob'=>'This is a test-value for the blob-column',
 165              ),
 166              array(
 167                  'test_auto' => 2, 'test_int4' => 2, 'test_varchar' => 'Hallo wer noch?','test_char' => '9876543210',
 168                  'test_text' => 'This is a test-value for the text-column, 2.row',
 169                  'test_blob'=>'This is a test-value for the blob-column, 2.row',
 170              ),
 171          ));
 172      echo "==> SUCCESS\n";
 173  
 174      echo "Droping column test_blob:\n";
 175      $new_table_def = $test_tables['schema_proc_test'];
 176      unset($new_table_def['fd']['test_blob']);
 177      $schema_proc->DropColumn('schema_proc_test',$new_table_def,'test_blob');
 178      check_column('test_blob',$adodb->MetaColumns('schema_proc_test'),False);
 179      echo "==> SUCCESS\n";
 180      
 181      echo "Altering column test_char to varchar(32):\n";
 182      $schema_proc->AlterColumn('schema_proc_test','test_char',array('type' => 'varchar','precision' => 32));
 183      check_column_type('test_char','varchar',32,$adodb->MetaColumns('schema_proc_test'));
 184      echo "==> SUCCESS\n";
 185      
 186      echo "Adding column test_bool bool:\n";
 187      $schema_proc->AddColumn('schema_proc_test','test_bool',array('type' => 'bool'));
 188      check_column('test_bool',$adodb->MetaColumns('schema_proc_test'));
 189      echo "==> SUCCESS\n";
 190      
 191      echo "Renaming column test_timestamp to test_time:\n";
 192      $schema_proc->RenameColumn('schema_proc_test','test_timestamp','test_time');
 193      check_column('test_timestamp',$adodb->MetaColumns('schema_proc_test'),false);
 194      check_column('test_time',$adodb->MetaColumns('schema_proc_test'));
 195      echo "==> SUCCESS\n";
 196      
 197      echo "Renaming table schema_proc_test to schema_proc_renamed:\n";
 198      $schema_proc->RenameTable('schema_proc_test','schema_proc_renamed');
 199      $tables = $adodb->MetaTables();
 200      check_table('schema_proc_test',$tables,False);
 201      check_table('schema_proc_renamed',$tables);
 202      echo "==> SUCCESS\n";
 203      
 204      echo "Renaming column (with index) test_varchar to test_varchar_renamed:\n";
 205      $schema_proc->RenameColumn('schema_proc_renamed','test_varchar','test_varchar_renamed');
 206      $columns = $adodb->MetaColumns('schema_proc_renamed');
 207      check_column('test_varchar',$columns,False);
 208      check_column('test_varchar_renamed',$columns);
 209      $indexes = $adodb->MetaIndexes('schema_proc_renamed');
 210      if ($indexes !== False)
 211      {
 212          check_index('test_varchar',False,$indexes,False);
 213          check_index('test_varchar_renamed',False,$indexes);
 214      }
 215      echo $indexes !== False ? "==> SUCCESS\n" : "==> unchecked\n";    
 216      
 217      echo "Droping index from renamed column (test_char,test_varchar_renamed):\n";
 218      $schema_proc->DropIndex('schema_proc_renamed',array('test_char','test_varchar_renamed'));
 219      $indexes = $adodb->MetaIndexes('schema_proc_renamed');
 220      if ($indexes !== False) check_index(array('test_char','test_varchar_renamed'),False,$indexes,False);
 221      echo $indexes !== False ? "==> SUCCESS\n" : "==> unchecked\n";    
 222      
 223      //print_r($adodb->MetaColumns('schema_proc_renamed'));
 224      //print_r($adodb->MetaIndexes('schema_proc_renamed'));
 225      
 226      echo "Inserting some more content\n";
 227      $db->query("INSERT INTO schema_proc_renamed (test_int4,test_varchar_renamed,test_char) VALUES (10,'Hallo Hallo Hallo ...','12345678901234567890123456789012')");
 228      check_content($adodb->GetAll("SELECT * FROM schema_proc_renamed"),array(
 229              array('test_auto' => 1, 'test_int4' => 99, 'test_varchar_renamed' => 'Hallo Ralf','test_char' => 'abcdefghij'),
 230              array('test_auto' => 2, 'test_int4' => 2, 'test_varchar_renamed' => 'Hallo wer noch?','test_char' => '9876543210'),
 231              array('test_auto' => 3, 'test_int4' => 10, 'test_varchar_renamed' => 'Hallo Hallo Hallo ...','test_char' => '12345678901234567890123456789012'),
 232          ));
 233      echo "==> SUCCESS\n";
 234  
 235      //echo "Reading back the content:\n";
 236      //$all = $adodb->GetAll("SELECT * FROM schema_proc_renamed");
 237      //print_r($all);
 238  
 239      echo "\nDroping the test-tables again\n";
 240      foreach($adodb->MetaTables() as $table)
 241      {
 242          $table = strtolower($table);
 243          if (strstr($table,'schema_proc')) 
 244          {
 245              $aSql = $schema_proc->dict->DropTableSQL($table);
 246              $schema_proc->ExecuteSqlArray($aSql,1,"DropTableSQL('%1') = %2",$table,$aSql);
 247          }
 248      }
 249      echo "\n********************\n";
 250      echo "*** FULL SUCCESS ***\n";
 251      echo "********************\n";
 252      echo "\nbye ...\n";
 253  
 254  
 255      /**
 256       * Checks if table $table exists or not, die's with an error-message if the check went wrong
 257       *
 258       * @param string $table table-name
 259       * @param array $tables array of table-names from call to MetaTables()
 260       * @param boolean $existence=true should we check for existence or none-existence, default existence
 261       */
 262  	function check_table($table,$tables,$existence=True)
 263      {
 264          $exist = in_array($table,$tables) || in_array(strtoupper($table),$tables);
 265          
 266          if ($exist != $existence)
 267          {
 268              print_r($tables);
 269              die ("\n\n!!! Table '$table' is ".($existence ? 'missing' : 'still there')." !!!\n\n");
 270          }
 271      }
 272  
 273      /**
 274       * Checks if $column exists or not, die's with an error-message if the check went wrong
 275       *
 276       * @param string $column column-name
 277       * @param array $columns array of adodb field objects from MetaColumns($table)
 278       * @param boolean $existence=true should we check for existence or none-existence, default existence
 279       */
 280  	function check_column($column,$columns,$existence=True)
 281      {
 282          $exist = isset($columns[$column]) || isset($columns[strtoupper($column)]);
 283          
 284          if ($exist != $existence)
 285          {
 286              print_r($columns);
 287              die ("\n\n!!! Column '$column' is ".($existence ? 'missing' : 'still there')." !!!\n\n");
 288          }
 289      }
 290      
 291      /**
 292       * Checks the type of a column
 293       *
 294       * @param string $column column-name
 295       * @param string $type column-type as the DB uses it, no eGW type !!!
 296       * @param int $precision precision
 297       * @param array $columns array of adodb field objects from MetaColumns($table)
 298       */
 299  	function check_column_type($column,$type,$precision,$columns)
 300      {
 301          static $alternate_types = array(
 302              'varchar'    => array('C'),
 303              'int'        => array('I'),
 304          );
 305  
 306          $data = isset($columns[$column]) ? $columns[$column] : $columns[strtoupper($column)];
 307          
 308          if (!is_object($data))
 309          {
 310              print_r($columns);
 311              die ("\n\n!!! Column '$column' does NOT exist !!!\n\n");
 312          }
 313          $data->type = strtolower($data->type);
 314          if ($data->type != $type && !in_array($data->type,$alternate_types[$type]))
 315          {
 316              print_r($columns);
 317              die ("\n\n!!! Column '$column' is NOT of type '$type', but '$data->type' !!!\n\n");
 318          }
 319          if ($precision && $data->max_length != $precision)
 320          {
 321              print_r($columns);
 322              die ("\n\n!!! Precision of column '$column' is NOT $precision, but $data->precision !!!\n\n");
 323          }
 324      }
 325      
 326      /**
 327       * Checks if $idx exists or not, die's with an error-message if the check went wrong
 328       *
 329       * @param array $columns array of strings with column-names of that index
 330       * @param boolean $unique unique index or not
 331       * @param array $indexes array of index-describtions from call to MetaIndexes($table)
 332       * @param boolean $existence=true should we check for existence or none-existence, default existence
 333       */
 334  	function check_index($columns,$unique,$indexes,$existence=True)
 335      {
 336          if (!is_array($columns)) $columns = array($columns);
 337          $existence = $existence && $columns['options'][$GLOBALS['db']->Type] !== False;
 338          unset($columns['options']);
 339  
 340          $exist = False;
 341          foreach($indexes as $idx_data)
 342          {
 343              if (implode(':',$columns) == strtolower(implode(':',$idx_data['columns'])))
 344              {
 345                  $exist = true;
 346                  break;
 347              }
 348          }
 349          if ($exist != $existence)
 350          {
 351              print_r($indexes);
 352              die ("\n\n!!! Index (".implode(', ',$columns).") is ".($existence ? 'missing' : 'still there')." !!!\n\n");
 353          }
 354          if ($existence && $unique != !!$idx_data['unique'])
 355          {
 356              print_r($indexes);
 357              die ("\n\n!!! Index (".implode(', ',$columns).") is ".($unique ? 'NOT ' : '')."unique !!!\n\n");
 358          }
 359      }
 360  
 361      /**
 362       * Checks the content written to the table
 363       * 
 364       * @param array $is content read from the database via GetAll()
 365       * @param array $should content against which we test
 366       * @param boolean $return_false=false return false if the check fails or die with an error-msg, default die
 367       */
 368  	function check_content($is,$should,$return_false=false)
 369      {
 370          foreach($should as $key => $val)
 371          {
 372              if (!isset($is[$key]) && isset($is[strtoupper($key)])) $key = strtoupper($key);
 373              if (is_array($val) && !check_content($is[$key],$val,True) || !is_array($val) && $is[$key] != $val)
 374              {
 375                  echo "key='$key', is="; print_r($is[$key]); echo ", should="; print_r($val); echo "\n";
 376                  if ($return_false) return False;
 377                  
 378                  print_r($is);
 379                  die("\n\n!!! Content read back from table is not as expected !!!\n\n");
 380              }
 381          }
 382          return True;
 383      }