PHP DB::statement方法代码示例

 public function createTable($table)
 {
     // drop existing table
     DB::statement("drop table if exists `{$table}`");
     $header = $this->request->getHeader();
     $listings = $this->getListings();
     $fields = [];
     foreach ($listings as $listing) {
         unset($listing->datajoeEntity);
         foreach ($listing as $k => $v) {
             if (isset($fields[$k])) {
                 $fields[$k] = strlen(print_r($v, TRUE)) > $fields[$k] ? strlen(print_r($v, TRUE)) + 100 : $fields[$k];
             } else {
                 $fields[$k] = strlen(print_r($v, TRUE)) + 100;
             }
         }
     }
     $sql = "CREATE TABLE IF NOT EXISTS `{$table}` (\n\t\t\t`id` MEDIUMINT( 10 ) UNSIGNED NOT NULL,\n\t\t\t`name` VARCHAR( 100 ) NOT NULL COMMENT 'Name',\n";
     foreach ($fields as $k => $v) {
         if ($k != 'name' && $k != 'id') {
             $sql .= "`{$k}` VARCHAR( {$v} ) NOT NULL COMMENT " . str_replace('?', '?', DB::connection()->getPdo()->quote($header->{$k}->label)) . ",\n";
         }
     }
     $sql .= "FULLTEXT KEY `name` (`name`),\n\t\t\tPRIMARY KEY ( `id` )\n\t\t\t) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_unicode_ci;";
     DB::statement($sql);
 }

 /**
  * Run the database seeds.
  *
  * @return void
  */
 public function run()
 {
     Model::unguard();
     DB::beginTransaction();
     DB::statement('SET FOREIGN_KEY_CHECKS = 0');
     DB::table('routes')->delete();
     DB::table('contents')->delete();
     DB::table('categories')->delete();
     DB::table('content_types')->delete();
     DB::table('languages')->delete();
     DB::table('group_user')->delete();
     DB::table('user_meta')->delete();
     DB::table('users')->delete();
     DB::table('groups')->delete();
     DB::table('routes')->truncate();
     DB::table('contents')->truncate();
     DB::table('categories')->truncate();
     DB::table('content_types')->truncate();
     DB::table('languages')->truncate();
     DB::table('group_user')->truncate();
     DB::table('user_meta')->truncate();
     DB::table('users')->truncate();
     DB::table('groups')->truncate();
     DB::statement('SET FOREIGN_KEY_CHECKS = 1');
     DB::commit();
     Model::reguard();
 }

 public function run()
 {
     DB::statement('SET FOREIGN_KEY_CHECKS=0;');
     DB::table(config('access.permissions_table'))->truncate();
     DB::table(config('access.permission_role_table'))->truncate();
     DB::table(config('access.permission_user_table'))->truncate();
     $permission_model = config('access.permission');
     $viewBackend = new $permission_model();
     $viewBackend->name = 'view_backend';
     $viewBackend->display_name = 'View Backend';
     $viewBackend->system = true;
     $viewBackend->created_at = Carbon::now();
     $viewBackend->updated_at = Carbon::now();
     $viewBackend->save();
     //Find the first role (admin) give it all permissions
     $role_model = config('access.role');
     $role_model = new $role_model();
     $admin = $role_model::first();
     $admin->permissions()->sync([$viewBackend->id]);
     $permission_model = config('access.permission');
     $userOnlyPermission = new $permission_model();
     $userOnlyPermission->name = 'user_only_permission';
     $userOnlyPermission->display_name = 'Test User Only Permission';
     $userOnlyPermission->system = false;
     $userOnlyPermission->created_at = Carbon::now();
     $userOnlyPermission->updated_at = Carbon::now();
     $userOnlyPermission->save();
     $user_model = config('auth.model');
     $user_model = new $user_model();
     $user = $user_model::find(2);
     $user->permissions()->sync([$userOnlyPermission->id]);
     DB::statement('SET FOREIGN_KEY_CHECKS=1;');
 }

 /**
  * Run the database seeds.
  *
  * @return void
  */
 public function run()
 {
     Model::unguard();
     DB::statement('SET FOREIGN_KEY_CHECKS=0;');
     DB::table('oauth_clients')->truncate();
     DB::table('roles')->truncate();
     DB::table('oauth_scopes')->truncate();
     DB::table('oauth_client_grants')->truncate();
     DB::table('oauth_grants')->truncate();
     DB::table('oauth_client_scopes')->truncate();
     DB::table('oauth_access_tokens')->truncate();
     DB::table('oauth_access_token_scopes')->truncate();
     DB::table('oauth_sessions')->truncate();
     DB::table('oauth_session_scopes')->truncate();
     DB::table('oauth_grant_scopes')->truncate();
     DB::table('users')->truncate();
     DB::statement('SET FOREIGN_KEY_CHECKS=1;');
     $this->call(OauthClientsTableSeeder::class);
     $this->call(RolesTableSeeder::class);
     $this->call(OauthScopesTableSeeder::class);
     $this->call(OauthGrantsTableSeeder::class);
     $this->call(OauthClientGrantsTableSeeder::class);
     $this->call(OauthClientScopesTableSeeder::class);
     $this->call(OauthGrantScopesTableSeeder::class);
     $this->call(UsersTableSeeder::class);
     Model::reguard();
 }

 public function run()
 {
     if (env('DB_DRIVER') == 'mysql') {
         DB::statement('SET FOREIGN_KEY_CHECKS=0;');
     }
     if (env('DB_DRIVER') == 'mysql') {
         DB::table(config('access.assigned_roles_table'))->truncate();
     } elseif (env('DB_DRIVER') == 'sqlite') {
         DB::statement("DELETE FROM " . config('access.assigned_roles_table'));
     } else {
         //For PostgreSQL or anything else
         DB::statement("TRUNCATE TABLE " . config('access.assigned_roles_table') . " CASCADE");
     }
     //Attach admin role to admin user
     $user_model = config('auth.model');
     $user_model = new $user_model();
     $user_model::first()->attachRole(1);
     //Attach user role to general user
     $user_model = config('auth.model');
     $user_model = new $user_model();
     $user_model::find(2)->attachRole(2);
     if (env('DB_DRIVER') == 'mysql') {
         DB::statement('SET FOREIGN_KEY_CHECKS=1;');
     }
 }

 /**
  * Run the migrations.
  *
  * @return void
  */
 public function up()
 {
     Schema::create('articles', function (Blueprint $table) {
         $table->increments('id');
         $table->integer('contributor_id')->unsigned();
         $table->integer('subcategory_id')->unsigned();
         $table->string('title', 70);
         $table->string('slug', 100)->unique();
         $table->string('featured')->default('noimage.jpg');
         $table->text('content');
         $table->text('content_update')->nullable();
         $table->string('excerpt', 300)->nullable();
         $table->enum('type', ['standard', 'gallery', 'video'])->default('standard');
         $table->enum('status', ['pending', 'draft', 'published', 'reject'])->default('pending');
         $table->enum('state', ['general', 'headline', 'trending'])->default('general');
         $table->integer('view')->unsigned()->default(0);
         $table->decimal('reward')->default(0);
         $table->softDeletes();
         $table->timestamps();
         $table->foreign('contributor_id')->references('id')->on('contributors')->onDelete('cascade');
         $table->foreign('subcategory_id')->references('id')->on('subcategories')->onDelete('cascade');
         $table->index(['id', 'slug']);
     });
     DB::statement("ALTER TABLE articles MODIFY content LONGBLOB");
     DB::statement("ALTER TABLE articles MODIFY content_update LONGBLOB");
 }

 public function run()
 {
     if (DB::connection()->getDriverName() == 'mysql') {
         DB::statement('SET FOREIGN_KEY_CHECKS=0;');
     }
     if (DB::connection()->getDriverName() == 'mysql') {
         DB::table(config('access.assigned_roles_table'))->truncate();
     } elseif (DB::connection()->getDriverName() == 'sqlite') {
         DB::statement('DELETE FROM ' . config('access.assigned_roles_table'));
     } else {
         //For PostgreSQL or anything else
         DB::statement('TRUNCATE TABLE ' . config('access.assigned_roles_table') . ' CASCADE');
     }
     //Attach admin role to admin user
     $user_model = config('auth.providers.users.model');
     $user_model = new $user_model();
     $user_model::first()->attachRole(1);
     //Attach executive role to executive user
     $user_model = config('auth.providers.users.model');
     $user_model = new $user_model();
     $user_model::find(2)->attachRole(2);
     //Attach user role to general user
     $user_model = config('auth.providers.users.model');
     $user_model = new $user_model();
     $user_model::find(3)->attachRole(3);
     if (DB::connection()->getDriverName() == 'mysql') {
         DB::statement('SET FOREIGN_KEY_CHECKS=1;');
     }
 }

 public function run()
 {
     if (env('DB_DRIVER') == 'mysql') {
         DB::table(config('access.roles_table'))->truncate();
     } elseif (env('DB_DRIVER') == 'sqlite') {
         DB::statement('DELETE FROM ' . config('access.roles_table'));
     } else {
         //For PostgreSQL or anything else
         DB::statement('TRUNCATE TABLE ' . config('access.roles_table') . ' CASCADE');
     }
     //Create admin role, id of 1
     $role_model = config('access.role');
     $admin = new $role_model();
     $admin->name = 'Administrator';
     $admin->all = true;
     $admin->sort = 1;
     $admin->created_at = Carbon::now();
     $admin->updated_at = Carbon::now();
     $admin->save();
     //id = 2
     $role_model = config('access.role');
     $user = new $role_model();
     $user->name = 'User';
     $user->sort = 2;
     $user->created_at = Carbon::now();
     $user->updated_at = Carbon::now();
     $user->save();
 }

 /**
  * Run the database seeds.
  *
  * @return void
  */
 public function run()
 {
     Model::unguard();
     DB::statement('SET FOREIGN_KEY_CHECKS=0');
     if (!App::environment('production')) {
         $this->call('UsersTableSeeder');
         $this->call('ProfilesTableSeeder');
     }
     $this->call('RolesTableSeeder');
     $this->call('DestinationsTableSeeder');
     $this->call('LocationsTableSeeder');
     $this->call('MissionTypesTableSeeder');
     $this->call('VehiclesTableSeeder');
     $this->call('StatisticsTableSeeder');
     $this->call('PartsTableSeeder');
     $this->call('MissionsTableSeeder');
     $this->call('PartFlightsTableSeeder');
     $this->call('TagsTableSeeder');
     $this->call('NotificationTypesTableSeeder');
     $this->call('NotificationsTableSeeder');
     $this->call('AstronautsTableSeeder');
     $this->call('SpacecraftTableSeeder');
     $this->call('SpacecraftFlightsTableSeeder');
     $this->call('PayloadsTableSeeder');
     $this->call('TelemetryTableSeeder');
     $this->call('PrelaunchEventsTableSeeder');
     DB::statement('SET FOREIGN_KEY_CHECKS=1');
 }

 public function store(Request $request)
 {
     $input = $request->all();
     $unit = Units::findOrFail($input['unit_id']);
     $item = Items::findOrFail($input['item_id']);
     if (array_key_exists('default', $input)) {
         DB::table('item_units')->where(['default' => 1, 'item_id' => $input['item_id']])->update(['default' => 0]);
         $input['default'] = 1;
         DB::statement('INSERT INTO item_units (`item_id`, `unit_id`, `factor`, `default`, `updated_at`, `created_at`) VALUES (' . $input['item_id'] . ', ' . $input['unit_id'] . ', ' . $input['factor'] . ', ' . $input['default'] . ', NOW(), NOW())');
         Helper::add(DB::getPdo()->lastInsertId(), 'added unit ' . $unit->title . ' for item ' . $item->title . ' (ID ' . $input['item_id'] . ')');
         Helper::add(DB::getPdo()->lastInsertId(), 'changed item ' . $item->title . ' (ID ' . $input['item_id'] . ') default unit to ' . $unit->title);
         ItemUnits::where(['item_id' => $input['item_id']])->update(['factor' => DB::raw('factor/' . $input['factor'])]);
         StockItem::where(['item_id' => $input['item_id']])->update(['stock' => DB::raw('stock/' . $input['factor'])]);
         RecipeItems::where(['item_id' => $input['item_id']])->update(['value' => DB::raw('value/' . $input['factor'])]);
         Menu::where(['item_id' => $input['item_id']])->update(['value' => DB::raw('value/' . $input['factor'])]);
         ItemPurchases::where(['item_id' => $input['item_id']])->update(['value' => DB::raw('value/' . $input['factor'])]);
         StockCheck::where(['item_id' => $input['item_id']])->update(['before' => DB::raw('`before` / ' . $input['factor']), 'after' => DB::raw('`after` / ' . $input['factor'])]);
     } else {
         $input['default'] = 0;
         DB::statement('INSERT INTO item_units (`item_id`, `unit_id`, `factor`, `default`, `updated_at`, `created_at`) VALUES (' . $input['item_id'] . ', ' . $input['unit_id'] . ', ' . $input['factor'] . ', ' . $input['default'] . ', NOW(), NOW())');
         Helper::add(DB::getPdo()->lastInsertId(), 'added unit ' . $unit->title . ' for item ' . $item->title . ' (ID ' . $input['item_id'] . ')');
     }
     //Units::create($input);
     Session::flash('flash_message', $this->title . ' successfully added!');
     return Redirect::action('ItemsController@index');
 }

 /**
  * Run the database seeds.
  *
  * @return void
  */
 public function run()
 {
     //Truncate
     DB::statement('SET FOREIGN_KEY_CHECKS=0;');
     User::truncate();
     Admin::truncate();
     Customer::truncate();
     Address::truncate();
     Category::truncate();
     Product::truncate();
     Brand::truncate();
     Type::truncate();
     Image::truncate();
     DB::statement('TRUNCATE category_product;');
     Product::clearIndices();
     Product::reindex();
     //Unguard
     Model::unguard();
     //Call
     $this->call(UsersTableSeeder::class);
     $this->call(ProductsTableSeeder::class);
     //Reguard
     Model::reguard();
     DB::statement('SET FOREIGN_KEY_CHECKS=1;');
 }

 /**
  * Reverse the migrations.
  *
  * @return void
  */
 public function down()
 {
     //disable foreign key check for this connection before running seeders
     DB::statement('SET FOREIGN_KEY_CHECKS=0;');
     Schema::drop('content_fields');
     DB::statement('SET FOREIGN_KEY_CHECKS=1;');
 }

 public function updateAlbum(Request $request)
 {
     $albums = json_decode($request->all()['albums'], true);
     $len = count($albums);
     DB::statement('SET FOREIGN_KEY_CHECKS=0;');
     Album::truncate();
     Photo::truncate();
     Album::reindex();
     DB::statement('SET FOREIGN_KEY_CHECKS=1;');
     foreach ($albums as $a) {
         $album = new Album();
         $album->album_id = $a['id'];
         $album->name = $a['name'];
         $album->created_time = $a['created_time'];
         $album->save();
         $photos = $a['photos']['data'];
         foreach ($photos as $p) {
             $photo = new Photo();
             $photo->album_id = $album->id;
             $photo->photo_id = $p['id'];
             $photo->source = $p['source'];
             $photo->save();
         }
     }
     return '1';
 }

 /**
  * Execute the console command.
  *
  * @return mixed
  */
 public function handle()
 {
     if ($this->confirm("Clear database? [Yes|no]", "Yes")) {
         $this->info('Clear database start');
         if (config('database.default') == 'mysql') {
             DB::statement('SET FOREIGN_KEY_CHECKS=0');
         } else {
             if (config('database.default') == 'sqlite') {
                 DB::statement('PRAGMA foreign_keys = OFF');
             }
         }
         $tableNames = Schema::getConnection()->getDoctrineSchemaManager()->listTableNames();
         foreach ($tableNames as $v) {
             Schema::drop($v);
             $this->info('Dropped: ' . $v);
         }
         $this->info('Clear database end');
         if (config('database.default') == 'mysql') {
             DB::statement('SET FOREIGN_KEY_CHECKS=1');
         } else {
             if (config('database.default') == 'sqlite') {
                 DB::statement('PRAGMA foreign_keys = ON');
             }
         }
     }
 }

 /**
  * Run the migrations.
  *
  * @return void
  */
 public function up()
 {
     Schema::create('search_texts', function (Blueprint $table) {
         $table->increments('id');
         $table->integer('page_vid')->unsigned()->nullable()->references('id')->on('page_versions')->onUpdate('CASCADE')->onDelete('CASCADE');
         $table->integer('page_id')->unsigned()->references('id')->on('pages')->onUpdate('CASCADE')->onDelete('CASCADE');
         $table->integer('embargoed_until')->unsigned()->nullable();
         $table->string('title', 75)->nullable();
         $table->string('standfirst', '255')->null();
         $table->longText('text')->nullable();
     });
     DB::statement('ALTER TABLE search_texts ENGINE = "MyISAM"');
     DB::statement('CREATE FULLTEXT INDEX search_texts_title on search_texts(title)');
     DB::statement('CREATE FULLTEXT INDEX search_texts_standfirst on search_texts(standfirst)');
     DB::statement('CREATE FULLTEXT INDEX search_texts_text on search_texts(text)');
     DB::statement('CREATE FULLTEXT INDEX search_texts_all on search_texts(title, standfirst, text)');
     DB::statement('ALTER TABLE chunk_texts drop index text_fulltext');
     DB::statement('ALTER TABLE page_versions drop index title_fulltext');
     $finder = new Finder\Finder();
     $finder->addFilter(new Finder\VisibleInSiteSearch());
     $pages = $finder->findAll();
     foreach ($pages as $p) {
         DB::table('search_texts')->insert(['page_id' => $p->getId(), 'embargoed_until' => $p->getCurrentVersion()->getEmbargoedUntil()->getTimestamp(), 'page_vid' => $p->getCurrentVersion()->getId(), 'title' => $p->getTitle(), 'standfirst' => Chunk::get('text', 'standfirst', $p)->text(), 'text' => strip_tags(Chunk::get('text', 'bodycopy', $p)->text())]);
     }
 }