public function action_index()
{
// clear redirect referrer
\Session::delete('submitted_redirect');
// read flash message for display errors.
$form_status = \Session::get_flash('form_status');
if (isset($form_status['form_status']) && isset($form_status['form_status_message'])) {
$output['form_status'] = $form_status['form_status'];
$output['form_status_message'] = $form_status['form_status_message'];
}
unset($form_status);
// list tables
$output['list_tables'] = \DB::list_tables();
// if form submitted
if (\Input::method() == 'POST') {
$table_name = trim(\Input::post('table_name'));
$output['table_name'] = $table_name;
if (!\Extension\NoCsrf::check()) {
// validate token failed
$output['form_status'] = 'error';
$output['form_status_message'] = \Lang::get('fslang_invalid_csrf_token');
} elseif ($table_name == null) {
$output['form_status'] = 'error';
$output['form_status_message'] = \Lang::get('dbhelper_please_select_db_table');
} else {
$output['list_columns'] = \DB::list_columns(\DB::expr('`' . $table_name . '`'));
}
}
// endif; form submitted
// <head> output ---------------------------------------------------------------------
$output['page_title'] = $this->generateTitle(\Lang::get('dbhelper'));
// <head> output ---------------------------------------------------------------------
return $this->generatePage('admin/templates/index/index_v', $output, false);
}
public static function get_columns($table, $is_column_only = true)
{
$list_columns = DB::list_columns($table);
if (!$is_column_only) {
return $list_columns;
}
return array_keys($list_columns);
}
/**
* Get the class's properties
*
* @return array
*/
public static function properties()
{
$class = get_called_class();
// If already determined
if (array_key_exists($class, static::$_properties_cached)) {
return static::$_properties_cached[$class];
}
// Try to grab the properties from the class...
if (property_exists($class, '_properties')) {
$properties = static::$_properties;
foreach ($properties as $key => $p) {
if (is_string($p)) {
unset($properties[$key]);
$properties[$p] = array();
}
}
}
// ...if the above failed, run DB query to fetch properties
if (empty($properties)) {
try {
$properties = \DB::list_columns(static::table());
} catch (\Exception $e) {
throw new Exception('Listing columns not failed, you have to set the model properties with a ' . 'static $_properties setting in the model. Original exception: ' . $e->getMessage());
}
}
// cache the properties for next usage
static::$_properties_cached[$class] = $properties;
return static::$_properties_cached[$class];
}
/**
* Construct the argument list
*
* @param string $table name of the database table we need to create the list for
* @return array
*/
protected static function arguments($table)
{
// get the list of columns from the table
try {
$columns = \DB::list_columns(trim($table), null, \Cli::option('db', null));
} catch (\Exception $e) {
\Cli::write($e->getMessage(), 'red');
exit;
}
// construct the arguments list, starting with the table name
$arguments = array($table);
// set some switches
$include_timestamps = false;
$timestamp_is_int = true;
// process the columns found
foreach ($columns as $column) {
// do we have a data_type defined? If not, use the generic type
isset($column['data_type']) or $column['data_type'] = $column['type'];
// skip the 'id' column, it will be added automatically
if ($column['name'] == 'id') {
continue;
}
// detect timestamp columns
if (in_array($column['name'], array('created_at', 'updated_at'))) {
$include_timestamps = true;
$timestamp_is_int = $column['data_type'] == 'int';
continue;
}
// do we need to add constraints?
$constraint = '';
foreach (array('length', 'character_maximum_length', 'display') as $idx) {
// check if we have such a column, and filter out some default values
if (isset($column[$idx]) and !in_array($column[$idx], array('65535', '4294967295'))) {
$constraint = '[' . $column[$idx] . ']';
break;
}
}
// if it's an enum column, list the available options
if (in_array($column['data_type'], array('set', 'enum'))) {
$constraint = '[' . implode(',', $column['options']) . ']';
}
// store the column in the argument list
$arguments[] = $column['name'] . ':' . $column['data_type'] . $constraint;
}
// set the switches for the code generation
\Cli::set_option('no-timestamp', $include_timestamps === false);
\Cli::set_option('mysql-timestamp', $timestamp_is_int === false);
// return the generated argument list
return $arguments;
}
public static function drop($subjects, $fields)
{
$up = <<<UP
\t\t\\DBUtil::drop_table('{$subjects[1]}');
UP;
$field_str = '';
$column_list = \DB::list_columns($subjects[1]);
foreach ($column_list as $column) {
switch ($column['type']) {
case 'float':
$constraint = '\'' . $column['numeric_precision'] . ', ' . $column['numeric_scale'] . '\'';
break;
case 'int':
$constraint = $column['display'];
break;
case 'string':
switch ($column['data_type']) {
case 'binary':
case 'varbinary':
case 'char':
case 'varchar':
$constraint = $column['character_maximum_length'];
break;
case 'enum':
case 'set':
$constraint = '"\'' . implode('\',\'', $column['options']) . '\'"';
break;
}
break;
}
$constraint_str = isset($constraint) ? ", 'constraint' => {$constraint}" : '';
$auto_increment = $column['extra'] == 'auto_increment' ? ", 'auto_increment' => true" : '';
$default_str = $column['default'] != null ? ", 'default' => '{$column['default']}'" : ", 'null' => true";
if ($column['key'] == 'PRI') {
$primary_keys[] = "'{$column['name']}'";
} else {
if ($column['key'] == 'MUL') {
$indexes[] = $column['name'];
}
}
$field_str .= "\t\t\t'{$column['name']}' => array('type' => '{$column['data_type']}'{$default_str}{$constraint_str}{$auto_increment})," . PHP_EOL;
unset($constraint);
}
$primary_keys = implode(',', $primary_keys);
$down = <<<DOWN
\t\t\\DBUtil::create_table('{$subjects[1]}', array(
{$field_str}
\t\t), array({$primary_keys}));
DOWN;
$down .= PHP_EOL;
$active_db = \Config::get('db.active');
$table_prefix = \Config::get('db.' . $active_db . '.table_prefix');
if (isset($indexes)) {
foreach ($indexes as $field) {
$down .= "\t\t\\DB::query(\"CREATE INDEX {$field}_idx ON {$table_prefix}{$subjects[1]} (`{$field}`)\")->execute();" . PHP_EOL;
}
}
return array($up, $down);
}
public static function get_default_data($table_name)
{
$fields = \DB::list_columns($table_name);
foreach ($fields as $k => $v) {
$_data_default[$k] = $v['default'];
}
$_data_default['is_new'] = true;
return $_data_default;
}
public function create_user()
{
// Get groups
$groups = \Sentry::group()->all('front');
if (\Input::post()) {
// Get POST values
$insert = \Input::post();
$register_type = 'register';
if (\Input::post('register')) {
$register_type = $insert['register'];
}
$ship_to = 'billing';
if ($insert['ship'] == 'other') {
$ship_to = 'shipping';
}
$val = \User\Controller_Validate::forge($register_type == 'register' ? 'create' : 'guest', false, $ship_to == 'shipping' ? 'shipping' : false);
if ($val->run()) {
array_walk($insert, create_function('&$val', '$val = trim($val);'));
try {
// Generate random username
$email = $insert['email'];
$user_group = 3;
if ($register_type == 'guest') {
$username = '******' . \Str::random('numeric', 16);
$insert['guest'] = 1;
$random_password = '******' . \Str::random(unique);
$password = $random_password;
} else {
$username = $email;
$insert['guest'] = 0;
$password = $insert['password'];
}
unset($insert['email'], $insert['password'], $insert['confirm_password'], $insert['user_group'], $insert['details'], $insert['save'], $insert['update']);
$only_billing = array('email');
$billing_data = \Arr::filter_prefixed($insert, "billing_");
// Set shipping data to be same as billing by default
if ($ship_to_billing) {
foreach ($billing_data as $key => $value) {
if (!in_array($key, $only_billing)) {
$insert['shipping_' . $key] = $value;
}
}
}
$metadata = \Arr::remove_prefixed($insert, "billing_") + $billing_data;
$table = \DB::table_prefix('users_metadata');
$columns = \DB::list_columns($table);
$insert = array_intersect_key($metadata, $columns);
// create the user - no activation required
$vars = array('username' => $username, 'email' => $email, 'password' => $password, 'metadata' => $insert);
$user_id = \Sentry::user()->create($vars);
$user = \Sentry::user($user_id);
// Add user to 'customer' group (id = 3)
if ($user_id and $user->add_to_group($user_group)) {
if ($register_type == 'account') {
\Messages::success('User successfully created.');
}
if ($register_type == 'guest') {
\Messages::success('You register as a guest.');
}
$login_column = \Config::get('sentry.login_column', 'email');
if (\Sentry::login(${$login_column}, $password, true)) {
\Response::redirect(\Uri::create('order/checkout/cost'));
} else {
if ($register_type == 'account') {
\Messages::error('There was an error while trying to create account. Please try to create new account.');
}
if ($register_type == 'guest') {
\Messages::error('There was an error. Please try to login with your account details.');
}
}
} else {
// show validation errors
\Messages::error('There was an error while trying to create account.');
}
} catch (\Sentry\SentryException $e) {
// show validation errors
\Messages::error('There was an error while trying to create user.');
$errors = $e->getMessage();
\Messages::error($errors);
}
} else {
if ($val->error() != array()) {
// show validation errors
\Messages::error('There was an error while trying to create user.');
foreach ($val->error() as $e) {
\Messages::error($e->get_message());
}
}
}
}
}
/**
* Generate MySQL Documentation for HTML.
*
* Usage (from command line):
*
* php oil refine mydoc:html <table_schema> <output_dir = "app/tmp/">
*/
public static function html($table_schema = null, $dir = null)
{
if (empty($table_schema)) {
static::help();
exit;
}
empty($dir) and $dir = APPPATH . 'tmp' . DS;
$dir = rtrim($dir, DS) . DS . 'mydoc' . DS;
/**
* connect to db
*/
$ret = static::connect($table_schema);
/**
* delete and create mydoc dir
*/
if (file_exists($dir)) {
if (!\Cli::option('f') and !\Cli::option('force')) {
\Cli::write(realpath($dir) . ' already exist, please use -f option to force delete and generate.', 'red');
exit;
}
$ret = \File::delete_dir($dir);
if ($ret === false) {
\Cli::write("Could not delete directory \"{$dir}\"", 'red');
exit;
}
}
$ret = mkdir($dir, 0777, true);
if ($ret === false) {
\Cli::write("Could not create directory \"{$dir}\"", 'red');
exit;
}
\File::copy_dir(__DIR__ . DS . '..' . DS . 'assets', $dir . 'assets');
/**
* generate index.html
*/
$migration_table_name = \Config::get('migrations.table', 'migration');
$migration = array();
if (\DBUtil::table_exists($migration_table_name)) {
$migration = \Db::select()->from($migration_table_name)->order_by('migration', 'desc')->limit(1)->execute()->as_array();
}
$html = \View::forge('mydoc/index', array('migration' => $migration))->render();
\File::create($dir, 'index.html', $html);
/**
* get tables
*/
$tables = array_flip(\DB::list_tables());
/**
* unset ignore tables
*/
foreach (\Config::get('mydoc.ignore_tables', array()) as $ignore_table_name) {
if (isset($tables[$ignore_table_name])) {
unset($tables[$ignore_table_name]);
}
}
$ignore_table_regex = \Config::get('mydoc.ignore_table_regex');
foreach ($tables as $table_name => $tmp) {
if (!empty($ignore_table_regex)) {
if (preg_match($ignore_table_regex, $table_name)) {
unset($tables[$table_name]);
continue;
}
}
$tables[$table_name] = array('indexes' => array(), 'foreign_keys' => array(), 'triggers' => array());
}
/**
* check table count
*/
if (count($tables) === 0) {
\Cli::write("No tables in \"{$table_schema}\"", 'red');
exit;
}
/**
* get foreign keys
*/
$sql = 'select distinct
table_name,
column_name,
referenced_table_name,
referenced_column_name
from
information_schema.key_column_usage
where
referenced_table_name is not null
and
referenced_column_name is not null
and
table_schema = :table_schema';
$foreign_keys = \Db::query($sql)->bind('table_schema', $table_schema)->execute()->as_array();
foreach ($foreign_keys as $foreign_key) {
if (isset($tables[$foreign_key['table_name']])) {
$tables[$foreign_key['table_name']]['foreign_keys'][$foreign_key['column_name']] = $foreign_key;
}
}
/**
* get indexes
*/
$sql = 'select distinct
table_name,
index_name,
non_unique,
column_name,
comment
from
information_schema.statistics
where
table_schema = :table_schema';
$indexes = \Db::query($sql)->bind('table_schema', $table_schema)->execute()->as_array();
foreach ($indexes as $index) {
if (isset($tables[$index['table_name']])) {
$tables[$index['table_name']]['indexes'][$index['index_name']][$index['column_name']] = $index;
}
}
/**
* get triggers
*/
$sql = 'select distinct
trigger_name,
event_manipulation,
event_object_table,
action_statement,
action_timing,
definer
from
information_schema.triggers
where
trigger_schema = :trigger_schema';
$triggers = \Db::query($sql)->bind('trigger_schema', $table_schema)->execute()->as_array();
foreach ($triggers as $trigger) {
if (isset($tables[$trigger['event_object_table']])) {
$tables[$trigger['event_object_table']]['triggers'][] = $trigger;
}
}
/**
* generate tables.html
*/
$html = \View::forge('mydoc/tables', array('tables' => array_keys($tables)))->render();
\File::create($dir, 'tables.html', $html);
/**
* generate table_*.html
*/
foreach ($tables as $table_name => $infos) {
$columns = \DB::list_columns($table_name);
foreach ($columns as &$column) {
// do we have a data_type defined? If not, use the generic type
isset($column['data_type']) or $column['data_type'] = $column['type'];
if ($column['data_type'] == 'enum') {
$column['data_type'] .= "('" . implode("', '", $column['options']) . "')";
}
$column['_length'] = null;
foreach (array('length', 'character_maximum_length', 'display') as $idx) {
// check if we have such a column, and filter out some default values
if (isset($column[$idx]) and !in_array($column[$idx], array('65535', '16777215', '4294967295'))) {
$column['_length'] = $column[$idx];
break;
}
}
$column['_extras'] = array();
if (strpos(\Str::lower($column['key']), 'pri') !== false) {
$column['_extras'][] = 'PK';
}
if (strpos(\Str::lower($column['key']), 'uni') !== false) {
$column['_extras'][] = 'UI';
}
if (!empty($column['extra'])) {
if (strpos($column['extra'], 'auto_increment') !== false) {
$column['_extras'][] = 'AI';
}
}
$column['_foreign_key'] = null;
if (!empty($infos['foreign_keys'])) {
$foreign_key = \Arr::get($infos['foreign_keys'], $column['name'], array());
if (!empty($foreign_key)) {
$column['_foreign_key'] = $foreign_key;
$column['_extras'][] = 'FK';
}
}
if (!empty($column['_foreign_key'])) {
$column['_parent_table_name'] = $column['_foreign_key']['referenced_table_name'];
} else {
$column['_foreign_key'] = array('referenced_table_name' => null, 'referenced_column_name' => null);
if (0 < preg_match('/^.+_id$/', $column['name'])) {
$parent_table_name = str_replace('_id', '', $column['name']);
if (isset($tables[$parent_table_name = \Inflector::singularize($parent_table_name)])) {
$column['_foreign_key'] = array('referenced_table_name' => $parent_table_name, 'referenced_column_name' => 'id');
} else {
if (isset($tables[$parent_table_name = \Inflector::pluralize($parent_table_name)])) {
$column['_foreign_key'] = array('referenced_table_name' => $parent_table_name, 'referenced_column_name' => 'id');
}
}
}
}
}
$html = \View::forge('mydoc/table', array('table_name' => $table_name, 'columns' => $columns, 'infos' => $infos))->render();
\File::create($dir, 'table_' . $table_name . '.html', $html);
}
/**
* generate indexes.html
*/
$html = \View::forge('mydoc/indexes', array('tables' => $tables))->render();
\File::create($dir, 'indexes.html', $html);
/**
* generate triggers.html
*/
$html = \View::forge('mydoc/triggers', array('tables' => $tables))->render();
\File::create($dir, 'triggers.html', $html);
\Cli::write("Generated MySQL Documentation in \"{$dir}\"", 'green');
exit;
}
public function generate_model($table_name, $db = null)
{
$table_class = \Inflector::classify($table_name);
// Generate the full path for the model
$file_path = APPPATH . 'classes' . DS . 'model' . DS;
$file_path .= str_replace('_', '/', strtolower($table_class)) . '.php';
if (file_exists($file_path)) {
\Cli::error('Model already found for database table ' . $table_name);
$answer = \Cli::prompt('Overwrite model?', array('y', 'n'));
if ($answer == 'n') {
\Cli::write('Existing model not overwritten.');
return false;
}
}
$columns = \DB::list_columns($table_name, null, $db);
\Cli::write('Found ' . count($columns) . " columns for the {$table_name} database table.", 'green');
$model_properties = array();
foreach ($columns as $column) {
// Process some of the column info to allow easier detection
list($column_type, $column_unsigned) = explode(' ', $column['data_type'] . ' ');
// Concatenated space stops an error happening when data_type has no spaces
// A hack to detect Bool data types
if ($column_type == 'tinyint' and $column['display'] == 1) {
$column_type = 'bool';
}
// Basic Properties
$column_properties = array('data_type' => in_array($column_type, static::$data_typing_types) ? $column_type : 'string', 'label' => \Inflector::humanize($column['name']), 'null' => $column['null']);
$column['default'] and $column_properties['default'] = $column['default'];
// Validation
// TODO: Add thresholds rather than having rediculously high max values
$column_validation = array();
$column['null'] or $column_validation[] = 'required';
if ($column_type == 'bool') {
$column_validation = array('required');
} elseif (key_exists($column_type, static::$string_max_lengths)) {
$column_validation['max_length'] = array((int) min($column['character_maximum_length'], static::$string_max_lengths[$column_type]));
} elseif ($column['type'] == 'int') {
$display_max = (int) str_repeat(9, $column['display']);
$column_validation['numeric_min'] = array((int) $column['min']);
$column_validation['numeric_max'] = array((int) min($column['max'], $display_max));
} elseif ($column['type'] == 'float') {
$max = (double) (str_repeat(9, $column['numeric_precision'] - $column['numeric_scale']) . '.' . str_repeat(9, $column['numeric_scale']));
$min = substr($column['data_type'], -8) == 'unsigned' ? 0 : $max * -1;
$column_validation['numeric_min'] = array($min);
$column_validation['numeric_max'] = array($max);
}
// Form
$column_form = array('type' => 'text');
if (in_array($column['name'], array('id', 'created_at', 'updated_at'))) {
$column_form['type'] = false;
} else {
$column['default'] and $column_form['value'] = $column['default'];
switch ($column_type) {
case 'char':
case 'varchar':
case 'tinytext':
case 'tinyblob':
isset($column_validation['max_length']) and $column_form['maxlength'] = $column_validation['max_length'][0];
break;
case 'text':
case 'blob':
case 'mediumtext':
case 'mediumblob':
case 'longtext':
case 'longblob':
$column_form['type'] = 'textarea';
break;
case 'enum':
case 'set':
$column_form['type'] = 'select';
$column_form['options'] = array();
break;
case 'bool':
$column_form['type'] = 'radio';
$column_form['options'] = array(1 => 'Yes', 0 => 'No');
break;
case 'decimal':
case 'double':
case 'float':
$column_form['step'] = floatval('0.' . str_repeat(9, $column['numeric_scale']));
// break is intentionally missing
// break is intentionally missing
case 'tinyint':
case 'smallint':
case 'int':
case 'mediumint':
case 'bigint':
$column_form['type'] = 'number';
isset($column_validation['numeric_min']) and $column_form['min'] = $column_validation['numeric_min'][0];
isset($column_validation['numeric_max']) and $column_form['max'] = $column_validation['numeric_max'][0];
break;
/* @TODO
case 'date':
case 'datetime':
case 'time':
case 'timestamp':
break;*/
}
}
$column_properties['validation'] = $column_validation;
$column_properties['form'] = $column_form;
$model_properties[$column['name']] = $column_properties;
}
$model_properties_str = str_replace(array("\n", ' ', 'array ('), array("\n\t", "\t", 'array('), \Format::forge($model_properties)->to_php());
$model_properties_str = preg_replace('/=>\\s+array/m', '=> array', $model_properties_str);
$model_str = <<<MODEL
<?php
class Model_{$table_class} extends \\Orm\\Model
{
protected static \$_table_name = '{$table_name}';
protected static \$_properties = {$model_properties_str};
protected static \$_observers = array(
'Orm\\Observer_Validation' => array(
'events' => array('before_save'),
),
'Orm\\Observer_Typing' => array(
'events' => array('before_save', 'after_save', 'after_load'),
),
MODEL;
if (isset($model_properties['created_at'])) {
$model_str .= <<<MODEL
'Orm\\Observer_CreatedAt' => array(
'events' => array('before_insert'),
'mysql_timestamp' => false,
'property' => 'created_at',
),
MODEL;
}
if (isset($model_properties['updated_at'])) {
$model_str .= <<<MODEL
'Orm\\Observer_UpdatedAt' => array(
'events' => array('before_save'),
'mysql_timestamp' => false,
'property' => 'updated_at',
),
MODEL;
}
$model_str .= <<<MODEL
);
}
MODEL;
// Make sure the directory exists
is_dir(dirname($file_path)) or mkdir(dirname($file_path), 0775, true);
// Show people just how clever FuelPHP can be
\File::update(dirname($file_path), basename($file_path), $model_str);
return true;
}
/**
* Returns the detected column prefix of a table.
*
* @param string table_name
* @return string the detected column prefix or an empty string
*/
public static function get_column_prefix($table_name)
{
$columns = \DB::list_columns($table_name);
// build an array of table column properties
$prefixes = array();
foreach ($columns as $key => $column) {
// explode by '_' and store the first element
$p = explode('_', $key);
$prefixes[$p[0]] = $p[0] . '_';
}
// we have detected the column prefix if only one element created. i hope
if (count($prefixes) == 1) {
return $p[0] . '_';
}
return '';
}
private function set_values_profile(\Model_Profile $obj, $values)
{
$cols = \DB::list_columns('profile');
foreach ($cols as $col => $props) {
if (in_array($col, array('id', 'sort_order', 'created_at', 'updated_at'))) {
continue;
}
$obj->{$col} = $values[$col];
}
if (!isset($obj->sort_order) || is_null($obj->sort_order)) {
$obj->sort_order = \Model_Profile::get_next_sort_order();
}
return $obj;
}
protected static function getAttributes()
{
$fields = \DB::list_columns('`' . static::$_table_name . '`');
return array_keys($fields);
}
