public function down()
{
$tables = array('bans', 'groups_users', 'group_permissions', 'log_in_attempt', 'users', 'user_groups', 'user_metadata', 'user_oauth', 'user_security');
foreach ($tables as $table) {
\DBUtil::rename_table('ethanol_' . $table, $table);
}
}
protected static function usertable()
{
if (!\DBUtil::table_exists(static::$data['ormauth_table'])) {
if (!\DBUtil::table_exists(static::$data['simpleauth_table'])) {
// table users
\DBUtil::create_table(static::$data['ormauth_table'], array('id' => array('type' => 'int', 'constraint' => 11, 'auto_increment' => true), 'username' => array('type' => 'varchar', 'constraint' => 50), 'password' => array('type' => 'varchar', 'constraint' => 255), 'group_id' => array('type' => 'int', 'constraint' => 11, 'default' => 1), 'email' => array('type' => 'varchar', 'constraint' => 255), 'last_login' => array('type' => 'varchar', 'constraint' => 25), 'previous_login' => array('type' => 'varchar', 'constraint' => 25, 'default' => 0), 'login_hash' => array('type' => 'varchar', 'constraint' => 255), 'user_id' => array('type' => 'int', 'constraint' => 11, 'default' => 0), 'created_at' => array('type' => 'int', 'constraint' => 11, 'default' => 0), 'updated_at' => array('type' => 'int', 'constraint' => 11, 'default' => 0)), array('id'));
// add a unique index on username and email
\DBUtil::create_index(static::$data['ormauth_table'], array('username', 'email'), 'username', 'UNIQUE');
} else {
\DBUtil::rename_table(static::$data['simpleauth_table'], static::$data['ormauth_table']);
}
}
// run a check on required fields, and deal with missing ones. we might be migrating from simpleauth
if (\DBUtil::field_exists(static::$data['ormauth_table'], 'group')) {
\DBUtil::modify_fields(static::$data['ormauth_table'], array('group' => array('name' => 'group_id', 'type' => 'int', 'constraint' => 11)));
}
if (!\DBUtil::field_exists(static::$data['ormauth_table'], 'group_id')) {
\DBUtil::add_fields(static::$data['ormauth_table'], array('group_id' => array('type' => 'int', 'constraint' => 11, 'default' => 1, 'after' => 'password')));
}
if (!\DBUtil::field_exists(static::$data['ormauth_table'], 'previous_login')) {
\DBUtil::add_fields(static::$data['ormauth_table'], array('previous_login' => array('type' => 'varchar', 'constraint' => 25, 'default' => 0, 'after' => 'last_login')));
}
if (!\DBUtil::field_exists(static::$data['ormauth_table'], 'user_id')) {
\DBUtil::add_fields(static::$data['ormauth_table'], array('user_id' => array('type' => 'int', 'constraint' => 11, 'default' => 0, 'after' => 'login_hash')));
}
if (\DBUtil::field_exists(static::$data['ormauth_table'], 'created')) {
\DBUtil::modify_fields(static::$data['ormauth_table'], array('created' => array('name' => 'created_at', 'type' => 'int', 'constraint' => 11)));
}
if (!\DBUtil::field_exists(static::$data['ormauth_table'], 'created_at')) {
\DBUtil::add_fields(static::$data['ormauth_table'], array('created_at' => array('type' => 'int', 'constraint' => 11, 'default' => 0, 'after' => 'user_id')));
}
if (\DBUtil::field_exists(static::$data['ormauth_table'], 'updated')) {
\DBUtil::modify_fields(static::$data['ormauth_table'], array('updated' => array('name' => 'updated_at', 'type' => 'int', 'constraint' => 11)));
}
if (!\DBUtil::field_exists(static::$data['ormauth_table'], 'updated_at')) {
\DBUtil::add_fields(static::$data['ormauth_table'], array('updated_at' => array('type' => 'int', 'constraint' => 11, 'default' => 0, 'after' => 'created_at')));
}
}
function up()
{
// get the driver used
\Config::load('auth', true);
$drivers = \Config::get('auth.driver', array());
is_array($drivers) or $drivers = array($drivers);
if (in_array('Simpleauth', $drivers)) {
// get the tablename
\Config::load('simpleauth', true);
$table = \Config::get('simpleauth.table_name', 'users');
// only do this if it doesn't exist yet
if (!\DBUtil::table_exists($table)) {
// table users
\DBUtil::create_table($table, array('id' => array('type' => 'int', 'constraint' => 11, 'auto_increment' => true), 'username' => array('type' => 'varchar', 'constraint' => 50), 'password' => array('type' => 'varchar', 'constraint' => 255), 'group' => array('type' => 'int', 'constraint' => 11, 'default' => 1), 'email' => array('type' => 'varchar', 'constraint' => 255), 'last_login' => array('type' => 'varchar', 'constraint' => 25), 'login_hash' => array('type' => 'varchar', 'constraint' => 255), 'profile_fields' => array('type' => 'text'), 'created_at' => array('type' => 'int', 'constraint' => 11, 'default' => 0), 'updated_at' => array('type' => 'int', 'constraint' => 11, 'default' => 0)), array('id'));
// add a unique index on username and email
\DBUtil::create_index($table, array('username', 'email'), 'username', 'UNIQUE');
}
} elseif (in_array('Ormauth', $drivers)) {
// get the tablename
\Config::load('ormauth', true);
$table = \Config::get('ormauth.table_name', 'users');
if (!\DBUtil::table_exists($table)) {
// get the simpleauth tablename, maybe that exists
\Config::load('simpleauth', true);
$simpletable = \Config::get('simpleauth.table_name', 'users');
if (!\DBUtil::table_exists($simpletable)) {
// table users
\DBUtil::create_table($table, array('id' => array('type' => 'int', 'constraint' => 11, 'auto_increment' => true), 'username' => array('type' => 'varchar', 'constraint' => 50), 'password' => array('type' => 'varchar', 'constraint' => 255), 'group_id' => array('type' => 'int', 'constraint' => 11, 'default' => 1), 'email' => array('type' => 'varchar', 'constraint' => 255), 'last_login' => array('type' => 'varchar', 'constraint' => 25), 'previous_login' => array('type' => 'varchar', 'constraint' => 25, 'default' => 0), 'login_hash' => array('type' => 'varchar', 'constraint' => 255), 'user_id' => array('type' => 'int', 'constraint' => 11, 'default' => 0), 'created_at' => array('type' => 'int', 'constraint' => 11, 'default' => 0), 'updated_at' => array('type' => 'int', 'constraint' => 11, 'default' => 0)), array('id'));
// add a unique index on username and email
\DBUtil::create_index($table, array('username', 'email'), 'username', 'UNIQUE');
} else {
\DBUtil::rename_table($simpletable, $table);
}
}
// run a check on required fields, and deal with missing ones. we might be migrating from simpleauth
if (\DBUtil::field_exists($table, 'group')) {
\DBUtil::modify_fields($table, array('group' => array('name' => 'group_id', 'type' => 'int', 'constraint' => 11)));
}
if (!\DBUtil::field_exists($table, 'group_id')) {
\DBUtil::add_fields($table, array('group_id' => array('type' => 'int', 'constraint' => 11, 'default' => 1, 'after' => 'password')));
}
if (!\DBUtil::field_exists($table, 'previous_login')) {
\DBUtil::add_fields($table, array('previous_login' => array('type' => 'varchar', 'constraint' => 25, 'default' => 0, 'after' => 'last_login')));
}
if (!\DBUtil::field_exists($table, 'user_id')) {
\DBUtil::add_fields($table, array('user_id' => array('type' => 'int', 'constraint' => 11, 'default' => 0, 'after' => 'login_hash')));
}
if (\DBUtil::field_exists($table, 'created')) {
\DBUtil::modify_fields($table, array('created' => array('name' => 'created_at', 'type' => 'int', 'constraint' => 11)));
}
if (!\DBUtil::field_exists($table, 'created_at')) {
\DBUtil::add_fields($table, array('created_at' => array('type' => 'int', 'constraint' => 11, 'default' => 0, 'after' => 'user_id')));
}
if (\DBUtil::field_exists($table, 'updated')) {
\DBUtil::modify_fields($table, array('updated' => array('name' => 'updated_at', 'type' => 'int', 'constraint' => 11)));
}
if (!\DBUtil::field_exists($table, 'updated_at')) {
\DBUtil::add_fields($table, array('updated_at' => array('type' => 'int', 'constraint' => 11, 'default' => 0, 'after' => 'created_at')));
}
// table users_meta
\DBUtil::create_table($table . '_metadata', array('id' => array('type' => 'int', 'constraint' => 11, 'auto_increment' => true), 'parent_id' => array('type' => 'int', 'constraint' => 11, 'default' => 0), 'key' => array('type' => 'varchar', 'constraint' => 20), 'value' => array('type' => 'varchar', 'constraint' => 100), 'user_id' => array('type' => 'int', 'constraint' => 11, 'default' => 0), 'created_at' => array('type' => 'int', 'constraint' => 11, 'default' => 0), 'updated_at' => array('type' => 'int', 'constraint' => 11, 'default' => 0)), array('id'));
// convert profile fields to metadata, and drop the column
if (\DBUtil::field_exists($table, 'profile_fields')) {
$result = \DB::select('id', 'profile_fields')->from($table)->execute();
foreach ($result as $row) {
$profile_fields = empty($row['profile_fields']) ? array() : unserialize($row['profile_fields']);
foreach ($profile_fields as $field => $value) {
if (!is_numeric($field)) {
\DB::insert($table . '_metadata')->set(array('parent_id' => $row['id'], 'key' => $field, 'value' => $value))->execute();
}
}
}
\DBUtil::drop_fields($table, array('profile_fields'));
}
// table users_user_role
\DBUtil::create_table($table . '_user_roles', array('user_id' => array('type' => 'int', 'constraint' => 11), 'role_id' => array('type' => 'int', 'constraint' => 11)), array('user_id', 'role_id'));
// table users_user_perms
\DBUtil::create_table($table . '_user_permissions', array('user_id' => array('type' => 'int', 'constraint' => 11), 'perms_id' => array('type' => 'int', 'constraint' => 11)), array('user_id', 'perms_id'));
}
}
public function down()
{
\DBUtil::rename_table('seller_callbacks', 'seller_events');
\DBUtil::modify_fields('seller_events', array('url' => array('name' => 'callback', 'type' => 'varchar', 'constraint' => 255)));
}
public function create_tables()
{
if (file_exists(APPPATH . 'config/production/db.php') == true) {
Log::error('shit worked!');
}
// DBUtil::set_connection(null);
if (DBUtil::table_exists('urls')) {
if (DBUtil::field_exists('urls', array('time'))) {
// Upgrade Me
try {
DBUtil::rename_table('urls', 'v2_urls');
DBUtil::rename_table('stats', 'v2_stats');
DBUtil::rename_table('settings', 'v2_settings');
} catch (\Database_Exception $e) {
Log::error($e);
}
Controller_Install::create_tables();
} else {
// Already Installed
}
} else {
$oil_path = str_replace('public/', '', DOCROOT);
try {
@Migrate::current('default', 'app');
@Migrate::current('auth', 'package');
} catch (\Database_exception $e) {
Debug::Dump('PLEASE REVISIT THIS /install (DONT RELOAD) THAT SHOULD INSTALL THE SCRIPT FOR YOU IF NOT THEN: Access has been denied for the database user , go to fuel/app/config/production/db.php , and edit your username and password!');
die;
}
try {
\DBUtil::create_index('urls', 'short_url');
} catch (\Database_exception $e) {
Log::error($e);
}
try {
\DBUtil::create_index('urls', 'user_id');
} catch (\Database_exception $e) {
Log::error($e);
}
try {
\DBUtil::create_index('urls', array('id', 'short_url'));
} catch (\Database_exception $e) {
Log::error($e);
}
try {
\DBUtil::create_index('urls', array('id', 'user_id'));
} catch (\Database_exception $e) {
Log::error($e);
}
try {
\DBUtil::create_index('urls', array('id', 'short_url', 'user_id'));
} catch (\Database_exception $e) {
Log::error($e);
}
try {
\DBUtil::create_index('url_stats', 'url_id');
} catch (\Database_exception $e) {
Log::error($e);
}
try {
\DBUtil::create_index('url_stats', 'country');
} catch (\Database_exception $e) {
Log::error($e);
}
try {
\DBUtil::create_index('url_stats', array('id', 'url_id'));
} catch (\Database_exception $e) {
Log::error($e);
}
try {
\DBUtil::create_index('url_stats_countries', 'start_ip');
} catch (\Database_exception $e) {
Log::error($e);
}
try {
\DBUtil::create_index('url_stats_countries', 'end_ip');
} catch (\Database_exception $e) {
Log::error($e);
}
try {
\DBUtil::create_index('url_stats_countries', 'country');
} catch (\Database_exception $e) {
Log::error($e);
}
try {
\DBUtil::create_index('url_stats_countries', array('start_ip', 'end_ip'));
} catch (\Database_exception $e) {
Log::error($e);
}
try {
\DBUtil::create_index('url_stats_countries', array('start_ip', 'end_ip', 'country'));
} catch (\Database_exception $e) {
Log::error($e);
}
try {
\DBUtil::modify_fields('urls', array('url' => array('constraint' => 255, 'type' => 'varchar')));
} catch (\Database_exception $e) {
Log::error($e);
}
}
return true;
}
public static function run()
{
//Drop tables is exists
if (\DBUtil::table_exists('auctions')) {
\DBUtil::drop_table('auctions');
}
if (\DBUtil::table_exists('parts')) {
\DBUtil::drop_table('parts');
}
if (\DBUtil::table_exists('balances')) {
\DBUtil::drop_table('balances');
}
if (\DBUtil::table_exists('bidlogs')) {
\DBUtil::drop_table('bidlogs');
}
if (\DBUtil::table_exists('ships')) {
\DBUtil::drop_table('ships');
}
//Drop foreign key in imported db
\DBUtil::drop_foreign_key('auction', 'auction_part');
//Drop index in imported db
\DBUtil::drop_index('auction', 'auction_part');
//Rename tables
\DBUtil::rename_table('auction', 'auctions');
\DBUtil::rename_table('part', 'parts');
\DBUtil::rename_table('balance', 'balances');
\DBUtil::rename_table('bidslog', 'bidlogs');
\DBUtil::rename_table('ship', 'ships');
// Modify and add fields in table auctions
\DBUtil::modify_fields('auctions', ['auctionID' => ['constraint' => 10, 'type' => 'varchar', 'name' => 'auc_id'], 'description' => ['constraint' => 80, 'type' => 'varchar', 'name' => 'title'], 'groupId' => ['constraint' => 10, 'type' => 'int', 'name' => 'part_id', 'null' => true], 'itemCount' => ['constraint' => 3, 'type' => 'int', 'name' => 'item_count'], 'wonDate' => ['type' => 'datetime', 'name' => 'won_date'], 'vendor' => ['constraint' => 40, 'type' => 'varchar', 'name' => 'vendor_id'], 'memo' => ['constraint' => 60, 'type' => 'varchar', 'null' => true], 'wonUser' => ['constraint' => 20, 'type' => 'varchar', 'name' => 'won_user', 'null' => true]]);
\DBUtil::add_fields('auctions', ['created_at' => ['constraint' => 11, 'type' => 'int', 'null' => true], 'updated_at' => ['constraint' => 11, 'type' => 'int', 'null' => true]]);
// Modify and add fields in table parts
\DBUtil::modify_fields('parts', ['groupId' => ['constraint' => 10, 'type' => 'int', 'auto_increment' => true, 'unsigned' => true, 'name' => 'id'], 'boxNumber' => ['constraint' => 3, 'type' => 'int', 'name' => 'box_number', 'null' => true], 'partPrice' => ['constraint' => 5, 'type' => 'int', 'name' => 'price'], 'partStatus' => ['constraint' => 3, 'type' => 'int', 'name' => 'status'], 'shipNumber' => ['constraint' => 5, 'type' => 'int', 'name' => 'ship_number', 'null' => true], 'trackNumber' => ['constraint' => 15, 'type' => 'varchar', 'name' => 'tracking', 'null' => true], 'memo' => ['constraint' => 60, 'type' => 'varchar', 'null' => true]]);
\DBUtil::add_fields('parts', ['created_at' => ['constraint' => 11, 'type' => 'int', 'null' => true], 'updated_at' => ['constraint' => 11, 'type' => 'int', 'null' => true]]);
// Modify and add fields in table vendors
\DBUtil::modify_fields('vendors', ['vendor' => ['constraint' => 40, 'type' => 'varchar', 'name' => 'name'], 'byNow' => ['constraint' => 1, 'type' => 'int', 'name' => 'by_now'], 'postIndex' => ['constraint' => 20, 'type' => 'varchar', 'name' => 'post_index', 'null' => true], 'address' => ['constraint' => 80, 'type' => 'varchar', 'null' => true], 'color' => ['constraint' => 10, 'type' => 'varchar', 'null' => true], 'memo' => ['constraint' => 200, 'type' => 'varchar', 'null' => true]]);
\DBUtil::add_fields('vendors', ['created_at' => ['constraint' => 11, 'type' => 'int', 'null' => true], 'updated_at' => ['constraint' => 11, 'type' => 'int', 'null' => true]]);
// Replace vendor name to vendor id in auctions and add vendor if not exists
try {
$auctions = \DB::select_array(['id', 'vendor_id'])->from('auctions')->execute();
\DB::start_transaction();
foreach ($auctions as $auction) {
$vendor = \DB::select('id')->from('vendors')->where('name', '=', $auction['vendor_id'])->execute()->as_array();
if (!empty($vendor)) {
\DB::update('auctions')->value("vendor_id", $vendor[0]['id'])->where('id', '=', $auction['id'])->execute();
} else {
$result = \DB::insert('vendors')->set(['name' => $auction['vendor_id']])->execute();
\DB::update('auctions')->value("vendor_id", $result[0])->where('id', '=', $auction['id'])->execute();
}
}
// Modify field type from varchat to int in auctions
\DBUtil::modify_fields('auctions', ['vendor_id' => ['constraint' => 11, 'type' => 'int']]);
\DB::commit_transaction();
} catch (\Exception $e) {
\DB::rollback_transaction();
print "Replace vendor name to vendor id in auctions and add vendor if not exists was filed\n";
}
// Replace won_user to user_id in auctions
try {
$users = \DB::select_array(['id', 'username'])->from('users')->execute();
\DB::start_transaction();
foreach ($users as $user) {
\DB::update('auctions')->value('won_user', $user['id'])->where('won_user', '=', $user['username'])->execute();
}
\DB::commit_transaction();
// Modify field type from varchat to int in auctions
\DBUtil::modify_fields('auctions', ['won_user' => ['constraint' => 11, 'type' => 'int', 'name' => 'user_id']]);
} catch (\Exception $e) {
\DB::rollback_transaction();
print "Replace won_user to user_id in auctions was filed\n";
}
// Add index to auctions and parts
\DBUtil::create_index('auctions', 'part_id', 'part_id');
\DBUtil::create_index('auctions', 'won_date', 'won_date');
\DBUtil::create_index('parts', 'status', 'status');
\DBUtil::create_index('vendors', 'name', 'name');
// delete auc_id g143869725 !!!!
print "Data base successfully converted\n";
}
