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); }