schema.php

00001 <?php
00002 /* SVN FILE: $Id: console_2libs_2schema_8php-source.html 580 2008-07-01 14:45:49Z gwoo $ */
00003 /**
00004  * Command-line database management utility to automate programmer chores.
00005  *
00006  * Schema is CakePHP's database management utility. This helps you maintain versions of
00007  * of your database.
00008  *
00009  * PHP versions 4 and 5
00010  *
00011  * CakePHP(tm) :  Rapid Development Framework <http://www.cakephp.org/>
00012  * Copyright 2005-2008, Cake Software Foundation, Inc.
00013  *                              1785 E. Sahara Avenue, Suite 490-204
00014  *                              Las Vegas, Nevada 89104
00015  *
00016  * Licensed under The MIT License
00017  * Redistributions of files must retain the above copyright notice.
00018  *
00019  * @filesource
00020  * @copyright       Copyright 2005-2008, Cake Software Foundation, Inc.
00021  * @link            http://www.cakefoundation.org/projects/info/cakephp CakePHP(tm) Project
00022  * @package         cake
00023  * @subpackage      cake.cake.console.libs
00024  * @since           CakePHP(tm) v 1.2.0.5550
00025  * @version         $Revision: 580 $
00026  * @modifiedby      $LastChangedBy: gwoo $
00027  * @lastmodified    $Date: 2008-07-01 09:45:49 -0500 (Tue, 01 Jul 2008) $
00028  * @license         http://www.opensource.org/licenses/mit-license.php The MIT License
00029  */
00030 App::import('File');
00031 App::import('Model', 'Schema');
00032 /**
00033  * Schema is a command-line database management utility for automating programmer chores.
00034  *
00035  * @package     cake
00036  * @subpackage  cake.cake.console.libs
00037  */
00038 class SchemaShell extends Shell {
00039 /**
00040  * is this a dry run?
00041  *
00042  * @var boolean
00043  * @access private
00044  */
00045     var $__dry = null;
00046 /**
00047  * Override initialize
00048  *
00049  * @access public
00050  */
00051     function initialize() {
00052         $this->_welcome();
00053         $this->out('Cake Schema Shell');
00054         $this->hr();
00055     }
00056 /**
00057  * Override startup
00058  *
00059  * @access public
00060  */
00061     function startup() {
00062         $name = null;
00063         if (!empty($this->params['name'])) {
00064             $name = $this->params['name'];
00065         }
00066         $path = null;
00067         if (!empty($this->params['path'])) {
00068             $path = $this->params['path'];
00069         }
00070         $file = null;
00071         if (!empty($this->params['file'])) {
00072             $file = $this->params['file'];
00073         }
00074         $connection = null;
00075         if (!empty($this->params['connection'])) {
00076             $connection = $this->params['connection'];
00077         }
00078         $this->Schema =& new CakeSchema(compact('name', 'path', 'file', 'connection'));
00079     }
00080 /**
00081  * Override main
00082  *
00083  * @access public
00084  */
00085     function main() {
00086         $this->help();
00087     }
00088 /**
00089  * Read and output contents od schema object
00090  * path to read as second arg
00091  *
00092  * @access public
00093  */
00094     function view() {
00095         $File = new File($this->Schema->path . DS .'schema.php');
00096         if ($File->exists()) {
00097             $this->out($File->read());
00098             $this->_stop();
00099         } else {
00100             $this->err(__('Schema could not be found', true));
00101             $this->_stop();
00102         }
00103     }
00104 /**
00105  * Read database and Write schema object
00106  * accepts a connection as first arg or path to save as second arg
00107  *
00108  * @access public
00109  */
00110     function generate() {
00111         $this->out('Generating Schema...');
00112         $options = array();
00113         if (isset($this->params['f'])) {
00114             $options = array('models' => false);
00115         }
00116 
00117         $snapshot = false;
00118         if (isset($this->args[0]) && $this->args[0] === 'snapshot') {
00119             $snapshot = true;
00120         }
00121 
00122         if (!$snapshot && file_exists($this->Schema->path . DS . 'schema.php')) {
00123             $snapshot = true;
00124             $result = $this->in("Schema file exists.\n [O]verwrite\n [S]napshot\n [Q]uit\nWould you like to do?", array('o', 's', 'q'), 's');
00125             if ($result === 'q') {
00126                 $this->_stop();
00127             }
00128             if ($result === 'o') {
00129                 $snapshot = false;
00130             }
00131         }
00132 
00133         $content = $this->Schema->read($options);
00134         $content['file'] = 'schema.php';
00135 
00136         if ($snapshot === true) {
00137             $Folder =& new Folder($this->Schema->path);
00138             $result = $Folder->read();
00139             $count = 1;
00140             if (!empty($result[1])) {
00141                 foreach ($result[1] as $file) {
00142                     if (preg_match('/schema/', $file)) {
00143                         $count++;
00144                     }
00145                 }
00146             }
00147             $content['file'] = 'schema_'.$count.'.php';
00148         }
00149 
00150         if ($this->Schema->write($content)) {
00151             $this->out(sprintf(__('Schema file: %s generated', true), $content['file']));
00152             $this->_stop();
00153         } else {
00154             $this->err(__('Schema file: %s generated', true));
00155             $this->_stop();
00156         }
00157     }
00158 /**
00159  * Dump Schema object to sql file
00160  * if first arg == write, file will be written to sql file
00161  * or it will output sql
00162  *
00163  * @access public
00164  */
00165     function dump() {
00166         $write = false;
00167         $Schema = $this->Schema->load();
00168         if (!$Schema) {
00169             $this->err(__('Schema could not be loaded', true));
00170             $this->_stop();
00171         }
00172         if (!empty($this->args[0])) {
00173             if ($this->args[0] == 'true') {
00174                 $write = Inflector::underscore($this->Schema->name);
00175             } else {
00176                 $write = $this->args[0];
00177             }
00178         }
00179         $db =& ConnectionManager::getDataSource($this->Schema->connection);
00180         $contents = "#". $Schema->name ." sql generated on: " . date('Y-m-d H:m:s') . " : ". time()."\n\n";
00181         $contents .= $db->dropSchema($Schema) . "\n\n". $db->createSchema($Schema);
00182         if ($write) {
00183             if (strpos($write, '.sql') === false) {
00184                 $write .= '.sql';
00185             }
00186             $File = new File($this->Schema->path . DS . $write, true);
00187             if ($File->write($contents)) {
00188                 $this->out(sprintf(__('SQL dump file created in %s', true), $File->pwd()));
00189                 $this->_stop();
00190             } else {
00191                 $this->err(__('SQL dump could not be created', true));
00192                 $this->_stop();
00193             }
00194         }
00195         $this->out($contents);
00196         return $contents;
00197     }
00198 /**
00199  * Run database commands: create, update
00200  *
00201  * @access public
00202  */
00203     function run() {
00204         if (!isset($this->args[0])) {
00205             $this->err('command not found');
00206             $this->_stop();
00207         }
00208 
00209         $command = $this->args[0];
00210 
00211         $this->Dispatch->shiftArgs();
00212 
00213         $name = null;
00214         if (isset($this->args[0])) {
00215             $name = $this->args[0];
00216         }
00217 
00218         if (isset($this->params['dry'])) {
00219             $this->__dry = true;
00220             $this->out(__('Performing a dry run.', true));
00221         }
00222 
00223         $options = array('name' => $name, 'file' => $this->Schema->file);
00224         if (isset($this->params['s'])) {
00225             $options = array('file' => 'schema_'.$this->params['s'].'.php');
00226         }
00227 
00228         $Schema = $this->Schema->load($options);
00229 
00230         if (!$Schema) {
00231             $this->err(sprintf(__('%s could not be loaded', true), $this->Schema->file));
00232             $this->_stop();
00233         }
00234 
00235         $table = null;
00236         if (isset($this->args[1])) {
00237             $table = $this->args[1];
00238         }
00239 
00240         switch($command) {
00241             case 'create':
00242                 $this->__create($Schema, $table);
00243             break;
00244             case 'update':
00245                 $this->__update($Schema, $table);
00246             break;
00247             default:
00248                 $this->err(__('command not found', true));
00249             $this->_stop();
00250         }
00251     }
00252 /**
00253  * Create database from Schema object
00254  * Should be called via the run method
00255  *
00256  * @access private
00257  */
00258     function __create($Schema, $table = null) {
00259         $db =& ConnectionManager::getDataSource($this->Schema->connection);
00260 
00261         $drop = $create = array();
00262 
00263         if (!$table) {
00264             foreach ($Schema->tables as $table => $fields) {
00265                 $drop[$table] = $db->dropSchema($Schema, $table);
00266                 $create[$table] = $db->createSchema($Schema, $table);
00267             }
00268         } elseif (isset($Schema->tables[$table])) {
00269             $drop[$table] = $db->dropSchema($Schema, $table);
00270             $create[$table] = $db->createSchema($Schema, $table);
00271         }
00272         if (empty($drop) || empty($create)) {
00273             $this->out(__('Schema is up to date.', true));
00274             $this->_stop();
00275         }
00276 
00277         $this->out("\n" . __('The following tables will be dropped.', true));
00278         $this->out(array_keys($drop));
00279 
00280         if ('y' == $this->in(__('Are you sure you want to drop the tables?', true), array('y', 'n'), 'n')) {
00281             $this->out('Dropping tables.');
00282             $this->__run($drop, 'drop');
00283         }
00284 
00285         $this->out("\n" . __('The following tables will be created.', true));
00286         $this->out(array_keys($create));
00287 
00288         if ('y' == $this->in(__('Are you sure you want to create the tables?', true), array('y', 'n'), 'y')) {
00289             $this->out('Creating tables.');
00290             $this->__run($create, 'create');
00291         }
00292 
00293         $this->out(__('End create.', true));
00294     }
00295 /**
00296  * Update database with Schema object
00297  * Should be called via the run method
00298  *
00299  * @access private
00300  */
00301     function __update($Schema, $table = null) {
00302         $db =& ConnectionManager::getDataSource($this->Schema->connection);
00303 
00304         $this->out('Comparing Database to Schema...');
00305         $Old = $this->Schema->read();
00306         $compare = $this->Schema->compare($Old, $Schema);
00307 
00308         $contents = array();
00309 
00310         if (empty($table)) {
00311             foreach ($compare as $table => $changes) {
00312                 $contents[$table] = $db->alterSchema(array($table => $changes), $table);
00313             }
00314         } elseif (isset($compare[$table])) {
00315             $contents[$table] = $db->alterSchema(array($table => $compare[$table]), $table);
00316         }
00317 
00318         if (empty($contents)) {
00319             $this->out(__('Schema is up to date.', true));
00320             $this->_stop();
00321         }
00322 
00323         $this->out("\n" . __('The following statements will run.', true));
00324         $this->out(array_map('trim', $contents));
00325         if ('y' == $this->in(__('Are you sure you want to alter the tables?', true), array('y', 'n'), 'n')) {
00326             $this->out('');
00327             $this->out(__('Updating Database...', true));
00328             $this->__run($contents, 'update');
00329         }
00330 
00331         $this->out(__('End update.', true));
00332     }
00333 /**
00334  * runs sql from __create() or __update()
00335  *
00336  * @access private
00337  */
00338     function __run($contents, $event) {
00339         if (empty($contents)) {
00340             $this->err(__('Sql could not be run', true));
00341             return;
00342         }
00343         Configure::write('debug', 2);
00344         $db =& ConnectionManager::getDataSource($this->Schema->connection);
00345         $db->fullDebug = true;
00346 
00347         $errors = array();
00348         foreach($contents as $table => $sql) {
00349             if (empty($sql)) {
00350                 $this->out(sprintf(__('%s is up to date.', true), $table));
00351             } else {
00352                 if ($this->__dry === true) {
00353                     $this->out(sprintf(__('Dry run for %s :', true), $table));
00354                     $this->out($sql);
00355                 } else {
00356                     if (!$this->Schema->before(array($event => $table))) {
00357                         return false;
00358                     }
00359                     if (!$db->_execute($sql)) {
00360                         $error = $table . ': '  . $db->lastError();
00361                     }
00362 
00363                     $this->Schema->after(array($event => $table, 'errors'=> $errors));
00364 
00365                     if (isset($error)) {
00366                         $this->out($error);
00367                     } elseif ($this->__dry !== true) {
00368                         $this->out(sprintf(__('%s updated.', true), $table));
00369                     }
00370                 }
00371             }
00372         }
00373     }
00374 /**
00375  * Displays help contents
00376  *
00377  * @access public
00378  */
00379     function help() {
00380         $this->out("The Schema Shell generates a schema object from \n\t\tthe database and updates the database from the schema.");
00381         $this->hr();
00382         $this->out("Usage: cake schema <command> <arg1> <arg2>...");
00383         $this->hr();
00384         $this->out('Params:');
00385         $this->out("\n\t-connection <config>\n\t\tset db config <config>. uses 'default' if none is specified");
00386         $this->out("\n\t-path <dir>\n\t\tpath <dir> to read and write schema.php.\n\t\tdefault path: ". $this->Schema->path);
00387         $this->out("\n\t-file <name>\n\t\tfile <name> to read and write.\n\t\tdefault file: ". $this->Schema->file);
00388         $this->out("\n\t-s <number>\n\t\tsnapshot <number> to use for run.");
00389         $this->out("\n\t-dry\n\t\tPerform a dry run on 'run' commands.\n\t\tQueries will be output to window instead of executed.");
00390         $this->out("\n\t-f\n\t\tforce 'generate' to create a new schema.");
00391         $this->out('Commands:');
00392         $this->out("\n\tschema help\n\t\tshows this help message.");
00393         $this->out("\n\tschema view\n\t\tread and output contents of schema file");
00394         $this->out("\n\tschema generate\n\t\treads from 'connection' writes to 'path'\n\t\tTo force genaration of all tables into the schema, use the -f param.");
00395         $this->out("\n\tschema dump <filename>\n\t\tdump database sql based on schema file to filename in schema path. \n\t\tif filename is true, default will use the app directory name.");
00396         $this->out("\n\tschema run create <schema> <table>\n\t\tdrop tables and create database based on schema file\n\t\toptional <schema> arg for selecting schema name\n\t\toptional <table> arg for creating only one table\n\t\tpass the -s param with a number to use a snapshot\n\t\tTo see the changes, perform a dry run with the -dry param");
00397         $this->out("\n\tschema run update <schema> <table>\n\t\talter tables based on schema file\n\t\toptional <schema> arg for selecting schema name.\n\t\toptional <table> arg for altering only one table.\n\t\tTo use a snapshot, pass the -s param with the snapshot number\n\t\tTo see the changes, perform a dry run with the -dry param");
00398         $this->out("");
00399         $this->_stop();
00400     }
00401 }
00402 ?>
The API

schema.php
