/**
* Syncs the model with the database schema.
*
* @param array The options array given to the table(s) to compile with.
* @return Migration
*/
public function sync(array $options = array())
{
// Loop through every table generated by the model.
foreach ($this->_tables as $table) {
// Search for any coresponding tables that exist in the database.
$tables = $this->_db->list_tables($table->name);
// List the key and value of the options array
while (list($key, $value) = each($options)) {
// Adding them all to the table
$table->add_option($key, $value);
}
if (empty($tables)) {
// If no coresponding tables were found, just create the table.
$table->create();
} else {
// Begin an alter statement.
$alter = DB::alter($table->name);
// List the columns in the coresponding table
$columns = $this->_db->list_columns($table->name);
// Loop through columns in our table
foreach ($table->columns() as $name => $column) {
// If there is a coresponding column
if (isset($columns[$column->name])) {
// Modify it.
DB::alter($table->name)->modify($column)->execute($this->_db);
} else {
// If there isnt, then add it.
$alter->add($column);
}
// We have a reference of the column in our model, so unset it.
unset($columns[$name]);
}
// Loop through everything that wasn't unset
foreach ($columns as $name => $column) {
// And drop it, because we don't have it in our model.
DB::alter($table->name)->drop($name, 'column')->execute($this->_db);
}
// Execute the alter query if we have columns to add
if ($alter->compile($this->_db)) {
$alter->execute($this->_db);
}
}
}
// Finally return the migration object.
return $this;
}
public function index()
{
$db = new Database();
$dbmanager = DBManager::instance();
echo $dbmanager->optimize_tables('all');
//echo $dbmanager->next_backup_time().'<br />';
//echo $dbmanager->backup_db();
/*
// List tables
echo '==================<br />List tables<br />==================';
$tables = $dbmanager->list_tables();
echo Kohana::debug($tables);
*/
// Only list name of tables
echo '==================<br />Only list name of tables<br />==================';
$table_name = $db->list_tables();
echo Kohana::debug($table_name);
/*
// List backup files
echo '==================<br />List backup files<br />==================';
echo Kohana::debug($dbmanager->list_backfiles());
// Optimize Tables
echo '==================<br />Optimize Tables<br />==================<br />';
$result = $dbmanager->optimize_tables($table_name);
if ( !empty($result) )
echo $result;
else
echo '全部优化完毕';
// Download backup file. $filename = '1234567890_-_database.sql'
// $dbmanager->download_backup($filename);
// Delete backup file. $filename = '1234567890_-_database.sql'
// $dbmanager->delete_backup($filename);
*/
// Display the demo page
//$this->template->title = 'Database Manager';
//$this->template->content = ;
}
/**
* Demonstrates the features of the Database library.
*
* Table Structure:
* CREATE TABLE `pages` (
* `id` mediumint( 9 ) NOT NULL AUTO_INCREMENT ,
* `page_name` varchar( 100 ) NOT NULL ,
* `title` varchar( 255 ) NOT NULL ,
* `content` longtext NOT NULL ,
* `menu` tinyint( 1 ) NOT NULL default '0',
* `filename` varchar( 255 ) NOT NULL ,
* `order` mediumint( 9 ) NOT NULL ,
* `date` int( 11 ) NOT NULL ,
* `child_of` mediumint( 9 ) NOT NULL default '0',
* PRIMARY KEY ( `id` ) ,
* UNIQUE KEY `filename` ( `filename` )
* ) ENGINE = MYISAM DEFAULT CHARSET = utf8 PACK_KEYS =0;
*
*/
function database()
{
$db = new Database();
$table = 'pages';
echo 'Does the ' . $table . ' table exist? ';
if ($db->table_exists($table)) {
echo '<p>YES! Lets do some work =)</p>';
$query = $db->select('DISTINCT pages.*')->from($table)->get();
echo $db->last_query();
echo '<h3>Iterate through the result:</h3>';
foreach ($query as $item) {
echo '<p>' . $item->title . '</p>';
}
echo '<h3>Numrows using count(): ' . count($query) . '</h3>';
echo 'Table Listing:<pre>' . print_r($db->list_tables(), TRUE) . '</pre>';
echo '<h3>Try Query Binding with objects:</h3>';
$sql = 'SELECT * FROM ' . $table . ' WHERE id = ?';
$query = $db->query($sql, array(1));
echo '<p>' . $db->last_query() . '</p>';
$query->result(TRUE);
foreach ($query as $item) {
echo '<pre>' . print_r($item, true) . '</pre>';
}
echo '<h3>Try Query Binding with arrays (returns both associative and numeric because I pass MYSQL_BOTH to result():</h3>';
$sql = 'SELECT * FROM ' . $table . ' WHERE id = ?';
$query = $db->query($sql, array(1));
echo '<p>' . $db->last_query() . '</p>';
$query->result(FALSE, MYSQL_BOTH);
foreach ($query as $item) {
echo '<pre>' . print_r($item, true) . '</pre>';
}
echo '<h3>Look, we can also manually advance the result pointer!</h3>';
$query = $db->select('title')->from($table)->get();
echo 'First:<pre>' . print_r($query->current(), true) . '</pre><br />';
$query->next();
echo 'Second:<pre>' . print_r($query->current(), true) . '</pre><br />';
$query->next();
echo 'Third:<pre>' . print_r($query->current(), true) . '</pre>';
echo '<h3>And we can reset it to the beginning:</h3>';
$query->rewind();
echo 'Rewound:<pre>' . print_r($query->current(), true) . '</pre>';
echo '<p>Number of rows using count_records(): ' . $db->count_records('pages') . '</p>';
} else {
echo 'NO! The ' . $table . ' table doesn\'t exist, so we can\'t continue =( ';
}
echo "<br/><br/>\n";
echo 'done in {execution_time} seconds';
}