PHP Manager::statement方法代码示例

 public static function insertSkipDupes($domains)
 {
     $query = sprintf('INSERT INTO domains (name) VALUES ("%s") ON DUPLICATE KEY UPDATE name=name', implode('"),("', $domains));
     $status = DB::statement($query);
     Domain::where('created_at', '0000-00-00 00:00:00')->update(['created_at' => DB::raw('NOW()')]);
     Domain::where('created_at', '0000-00-00 00:00:00')->update(['updated_at' => DB::raw('NOW()')]);
     return $status;
 }

 public function updateRanks($sort)
 {
     $start = time();
     $table = 'rank_' . $sort;
     Manager::table($table)->truncate();
     Manager::statement('INSERT INTO ' . $table . '  (user_id) SELECT id FROM users ORDER BY ' . $sort . ' DESC, id ASC LIMIT 100000;');
     $end = time() - $start;
     error_log('CRON Update Ranks - ' . $end . ' seconds');
     exit;
 }

 public function setUp()
 {
     parent::setUp();
     $capsule = new Capsule();
     $capsule->addConnection(array('driver' => 'sqlite', 'database' => ':memory:'));
     $capsule->setAsGlobal();
     $capsule->bootEloquent();
     Capsule::statement("CREATE TABLE sample(\n                       t_key             TEXT     NOT NULL,\n                       t_value           TEXT    NOT NULL\n                    );");
     for ($i = 1; $i <= 100; $i++) {
         $record = array('t_key' => 'Key ' . $i, 't_value' => 'Value ' . $i);
         Capsule::table('sample')->insert($record);
     }
 }

 protected function setUp()
 {
     parent::setUp();
     $this->schema = Capsule::schema();
     $this->schema->dropIfExists('documents');
     $this->schema->blueprintResolver(function ($table, $callback) {
         return new BlueprintWithArbitraryColumns($table, $callback);
     });
     $this->schema->create('documents', function (BlueprintWithArbitraryColumns $table) {
         $table->addColumn('uuid', 'id');
         $table->addColumn('jsonb', 'data');
         $table->addColumn('type', 'data');
         $table->timestamps();
     });
     Capsule::statement('CREATE INDEX data_gin ON documents USING GIN (data jsonb_path_ops);');
     $this->logger->info("Documents table created ...");
 }

 function calificarServicio(Request $request, Response $response)
 {
     $response = $response->withHeader('Content-type', 'application/json');
     $idServicio = $request->getAttribute("idServicio");
     $data = json_decode($request->getBody(), true);
     try {
         $query = "INSERT INTO " . "calificacionservicio (idServicio,idEmpleado,idSucursal,calificacion) " . "VALUES (" . "{$idServicio}," . "" . $data['idEmpleado'] . "," . "" . $data['idSucursal'] . "," . "" . $data['calificacion'] . ")";
         DB::statement(DB::raw($query));
         $respuesta = json_encode(array('msg' => "Guardado correctamente", "std" => 1));
         $response = $response->withStatus(200);
     } catch (Exception $err) {
         $respuesta = json_encode(array('msg' => "error", "std" => 0, "err" => $err->getMessage()));
         $response = $response->withStatus(404);
     }
     $response->getBody()->write($respuesta);
     return $response;
 }

 /**
  * Drops a database from the associated MySQL Server
  * @param  int $database The ID of the database to drop.
  * @return boolean
  */
 public function drop($database)
 {
     $db = Models\Database::findOrFail($database);
     $dbr = Models\DatabaseServer::findOrFail($db->db_server);
     DB::beginTransaction();
     try {
         $capsule = new Capsule();
         $capsule->addConnection(['driver' => 'mysql', 'host' => $dbr->host, 'port' => $dbr->port, 'database' => 'mysql', 'username' => $dbr->username, 'password' => Crypt::decrypt($dbr->password), 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci', 'prefix' => '', 'options' => [\PDO::ATTR_TIMEOUT => 3]]);
         $capsule->setAsGlobal();
         Capsule::statement('DROP USER \'' . $db->username . '\'@\'' . $db->remote . '\'');
         Capsule::statement('DROP DATABASE ' . $db->database);
         $db->delete();
         DB::commit();
         return true;
     } catch (\Exception $ex) {
         DB::rollback();
         throw $ex;
     }
 }

        // When a size is specified, such as varchar(size), the following format is used
        // type(**length**), so **length** will be replaced with the actual length, being stored in the Type model
        $sql_format = str_replace('**length**', $field->length, $field_type->sql_format);
        $create_table_statement .= '`field_' . $field->id . '` ' . $sql_format . ',';
        $i++;
    }
    $create_table_statement .= '`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,';
    $create_table_statement .= '`updated_at` timestamp NOT NULL DEFAULT \'0000-00-00 00:00:00\',';
    $create_table_statement .= '`form_id` int(11) NOT NULL,';
    $create_table_statement .= 'primary key(id)';
    $create_table_statement = rtrim($create_table_statement, ",");
    $create_table_statement .= ') ENGINE=InnoDB DEFAULT CHARSET=latin1';
    // And now we have to add indexes
    $add_keys_statement = "\n    ALTER TABLE `responses_" . $form->id . "`\n    ADD KEY `form_id` (`form_id`),\n    ADD CONSTRAINT `responses_" . $form->id . "_ibfk_1` FOREIGN KEY (`form_id`) REFERENCES `forms` (`id`);";
    DB::statement($create_table_statement);
    DB::statement($add_keys_statement);
    $app->redirect($app->urlFor('getFormEdit', array('id' => $form->id)));
})->name('postFormNew');
// /form/edit/:id/general controller
// Edit General controller
// Name for the form gets updated
$app->post('/form/edit/:id/general', function ($id) use($app) {
    $c = array();
    $form_name = $app->request()->post('form_name');
    $redirect = $app->request()->post('redirect');
    // We edit the form
    $form = models\Form::find($id);
    $form->form_name = $form_name;
    $form->redirect = $redirect;
    $form->save();
    $app->redirect($app->urlFor('getFormEdit', array('id' => $form->id)));

<?php

namespace RelationalExample\Config;

use Illuminate\Database\Capsule\Manager as Capsule;
include __DIR__ . '/../vendor/autoload.php';
@unlink(__DIR__ . '/oauth2.sqlite3');
touch(__DIR__ . '/oauth2.sqlite3');
Capsule::statement('PRAGMA foreign_keys = ON');
/******************************************************************************/
print 'Creating users table' . PHP_EOL;
Capsule::schema()->create('users', function ($table) {
    $table->increments('id');
    $table->string('username');
    $table->string('password');
    $table->string('name');
    $table->string('email');
    $table->string('photo');
});
Capsule::table('users')->insert(['username' => 'alexbilbie', 'password' => password_hash('whisky', PASSWORD_DEFAULT), 'name' => 'Alex Bilbie', 'email' => 'hello@alexbilbie.com', 'photo' => 'https://s.gravatar.com/avatar/14902eb1dac66b8458ebbb481d80f0a3']);
Capsule::table('users')->insert(['username' => 'philsturgeon', 'password' => password_hash('cider', PASSWORD_DEFAULT), 'name' => 'Phil Sturgeon', 'email' => 'email@philsturgeon.co.uk', 'photo' => 'https://s.gravatar.com/avatar/14df293d6c5cd6f05996dfc606a6a951']);
/******************************************************************************/
print 'Creating clients table' . PHP_EOL;
Capsule::schema()->create('oauth_clients', function ($table) {
    $table->string('id');
    $table->string('secret');
    $table->string('name');
    $table->primary('id');
});
Capsule::table('oauth_clients')->insert(['id' => 'testclient', 'secret' => 'secret', 'name' => 'Test Client']);
/******************************************************************************/