sql commands vol.2

.headers on
.mode column
update users set score = 0, name ='* ' || name where score < 60;
delete from users where score = 0;
select * from users;
[/sql]

transaction
[sql]
begin transaction;
update users set score = score - 10 where name = 'sato';
update users set score = score + 10 where name = 'kimura';
commit;
select * from users;
rollback;
[/sql]

trigger
[sql]
.headers on
.mode column
create table messages (message);

create trigger new_winner update of score on users when new.score > 100
  begin
   insert into messages (message) values (
     'name: ' || new name ||
     ' ' || old. score ||
     ' ->' || new.score
   );
   end;

update users set score = score + 30;
select * from messages;

index

create index score_index on users(score);
create unique index name_index on users(name);

.indices users
.schema users

drop index if exists score_index;
drop index if exists name_index;

日時

select datetime('now', '+09:00:00');
select date('2016-12-01', '+3 months', 'start of month', '-1 day');

create table if not exists users (
  id integer primary key,
  name varchar(255) not null,
  score integer default 10 check(score >= 0),
  team text,
  created datetime default (datetime('now', '+09:00:00'))
);

複数テーブルの作成

drop table if exists posts;
create table if not exists posts (
  id integer primary key,
  title text,
  body text,
  created datetime default (datetime('now', '+09:00:00'))
);

insert into posts (id, title, body) values (1, 't1', 'b1');
insert into posts (id, title, body) values (2, 't2', 'b2');

drop table if exists comments;
create table if not exists comments (
    id integer primary key,
    post_id integer,
    comment text,
    created datetime default (datetime('now', '+09:00:00'))
  );
  insert into comments (id, post_id, comment) values (1, 1, 'c1');
  insert into comments (id, post_id, comment) values (2, 1, 'c2');

.headers on
.mode column

select * from posts;
select * from comments;

内部結合 inner join、外部結合 left outer join、交差結合 cross join

select * from posts inner join comments on posts.id = comments.post_id;
select * from posts join comments on posts.id = comments.post_id;
select posts.id, posts.title, comment from posts join comments on posts.id = comments.post_id;
select * from posts left outer join comments on posts.id = comments.post_id;
select * from posts cross join comments;

row id ※integer primary key -> row id

select rowid, * from users;

dump

.output users.dump.sql
.dump users

外部データの取り込み

drop table if exists users;
create table users (
  id integer primary key,
  name,
  score
);
insert into users (name, score) values ('a', 10);
insert into users (name, score) values ('b', 20);
insert into users (name, score) values ('c', 30);

create table temp(name, score);

.mode csv
.import users.csv temp

insert into users(name, score) select name, score from temp;
select * from users;

drop table temp;

.mode csv
.output users_out.csv
select * from users;

sql commands

drop table if exists users;
create table if not exists users (
  id integer primary key,
  name varchar(255),
  score integer,
  email text unique
);

insert into users (email) values ('yamada@gmail.com');
insert into users (email) values ('yamada@gmail.com');

select * from users;

データの制約

drop table if exists users;
create table if not exists users (
  id integer primary key,
  name varchar(255) not null,
  score integer default 10 check(score >= 0),
  email text unique
);

insert into users (name, score) values ('yamada', -30);

select * from users;

select文

.headers on 
select id, name as user_name from users;
.mode line
select * from users;
select * from users where score > 60;
select * from users where score is null;
select * from users where name = 'ito';
select * from users where name like '%a%';
select * from users where name like '%@%' escape '@';
select * from users where name glob '*i*';
select * from users where name glob '[ts]*';

mode .html, mode.columなどもあります。

order by, limit, offset, view

select * from users where score is not null order by score;
select * from users where score is not null order by score desc;
select * from users where score is not null order by score desc limit 3;
select * from users where score is not null order by score desc limit 3 offset 2;
create view hiscore as select * from users order by score desc limit 5;
select * from hiscore;
drop view if exists hiscore;

演算

.headers on
.mode column
select id, name, score + 10 from users;
select count(id), max(score), min(score), avg(score) from users;
select name, length(name), upper(name), substr(name, 2, 3) from users;
select last_insert_rowid();
select random();
select * from users order by random() limit 1;
insert into users (name, score, team) values ('yamada', 30, 'tema-A');
insert into users (name, score, team) values ('yoshimoto', 55, 'tema-B');
insert into users (name, score, team) values ('ito', 22, 'tema-D');
insert into users (name, score, team) values ('sato', 18, 'tema-D');
insert into users (name, score, team) values ('kimura', 66, 'tema-A');
insert into users (name, score, team) values ('hayashi', 88, 'tema-B');
insert into users (name, score, team) values ('seto', 22, 'tema-C');
insert into users (name, score, team) values ('mimura', null, 'tema-C');

.headers on
.mode column
select team, avg(score) from users group by team;
select team, avg(score) from users group by team having avg(score) > 40;
select distinct team from users;
.headers on
.mode column
select
  id, name, score,
  case
   when score > 70 then 'A'
   when score > 50 then 'B'
   else 'C'
  end as rank
from users;

sqlite3

[vagrant@localhost sqlite]$ sqlite3 myapp.db
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .hlp

テーブルの作成

sqlite> create table posts (title, body);
sqlite> .exit
[vagrant@localhost sqlite]$ ls
myapp.db

ファイルの削除

[vagrant@localhost sqlite]$ rm myapp.db

外部ファイル読み込み

sqlite> .read commands.sql
[vagrant@localhost sqlite]$ sqlite3 myapp.db < commands.sql

テーブルの作成

create table if not exists posts (
  id integer primary key,
  title varchar(255),
  body varchar(255)
);

-- lists
.tables

-- table structure
.schema

データ型:integer, tex, real, blob, null

テーブル削除

drop table if exists posts;

テーブルの変更、カラムの追加

--list talbes
alter table posts rename to articles;

--add column
alter table articles add column email text;

レコードの挿入

insert into posts (title, body) values ('title1', 'body1');
insert into posts (id, title, body) values (null, 'title2', 'body2');
insert into posts (title, body) values ('title3', 'body3');
insert into posts (title, body) values ('title4', 'it''s body4');

select * from posts;

Laravelインストール

PHPのウェブアプリケーションフレームワークです。

Laravel

laravelはphp-xmlが必要になります。

[vagrant@localhost ~]$ mkdir laravel
[vagrant@localhost ~]$ cd laravel
[vagrant@localhost laravel]$ rpm -qa | grep php-xml
php-xml-5.6.27-1.el6.remi.x86_64

laravelのインストールにはcomposerが必要となります。

[vagrant@localhost laravel]$ curl -sS https://getcomposer.org/installer | php
All settings correct for using Composer
Downloading 1.2.2...

Composer successfully installed to: /home/vagrant/laravel/composer.phar
Use it: php composer.phar

[vagrant@localhost cake]$ php composer.phar create-project --prefer-dist laravel/laravel myblog

通常、設定項目は、.envに記述し、config/app.phpで読み込みます。

'env' => env('APP_ENV', 'production'),
'timezone' => 'Asia/Tokyo',
'locale' => 'ja',

CommentsとPostの紐づけ

CommentsTable.php

<?php

 namespace App\Model\Table;

 use Cake\ORM\Table;
 use Cake\Validation\Validator;

 class CommentsTable extends Table
 {
   public function initialize(array $config)
   {
     $this->addBehavior('Timestamp');
     $this->belognsTo('Posts');
   }
   public function validationDefault(Validator $validator)
   {
     $validator
       ->notEmpty('body')
       ->requirePresence('body')
     return $validator;
   }
 }

PostsTable.php

<?php

 namespace App\Model\Table;

 use Cake\ORM\Table;
 use Cake\Validation\Validator;

 class PostsTable extends Table
 {
   public function initialize(array $config)
   {
     $this->addBehavior('Timestamp');
     $this->hasMany('Comments');
   }
   public function validationDefault(Validator $validator)
   {
     $validator
       ->notEmpty('title')
       ->requirePresence('title')
       ->notEmpty('body')
       ->requirePresence('body')
       ->add('body', [
         'length' => [
           'rule' => ['minLength', 10],
           'message' => 'body length must be 10+'
         ]
       ]);
     return $validator;
   }
 }

migration plugin

[vagrant@localhost ~]$ cd cake
[vagrant@localhost cake]$ cd myblog
[vagrant@localhost myblog]$ bin/cake bake migration_snapshot Initial

Welcome to CakePHP v3.3.8 Console
---------------------------------------------------------------
App : src
Path: /home/vagrant/cake/myblog/src/
PHP : 5.6.27
---------------------------------------------------------------
[vagrant@localhost myblog]$ bin/cake migrations status
Welcome to CakePHP v3.3.8 Console

 Status  Migration ID    Migration Name
-----------------------------------------
     up  20161118150335  Initial
[vagrant@localhost myblog]$ bin/cake bake migration CreateComments post_id:integer body;string created modified

 Status  Migration ID    Migration Name
-----------------------------------------
     up  20161118150335  Initial
   down  20161118150459  CreateComments


[vagrant@localhost myblog]$ bin/cake migrations migrate
 Status  Migration ID    Migration Name
-----------------------------------------
     up  20161118150335  Initial
     up  20161118150459  CreateComments

delete post

index.ctp

  <?php foreach($posts as $post) : ?>
    <!-- <li><?= $this->Html->link($post->title, &#91;'controller'=>'Post', 'action'=>'view',$post->id&#93;); ?></li> -->
    <li><?= $this->Html->link($post->title, ['action'=>'view',$post->id]); ?>
       <?= $this->Html->link('[Edit]', ['action'=>'edit',$post->id],['class'=>'fs12']); ?>
       <?=
       $this->Form->postLink(
         '[x]',
         ['action'=>'delete', $post->id],
         ['confirm'=>'Are you sure?', 'class'=>'fs12']
       );
       ?>
     </li>
  <?php endforeach; ?>

PostsController.ctp

  public function delete($id = null)
  {
    $this->request->allowMethod(['post', 'delete']);
    $post = $this->Posts->get($id);
      if($this->Posts->delete($post)){
        $this->Flash->success('Delete Success!');
      } else {
        $this->Flash->error('Delete Error!');
      }
    return $this->redirect(['action'=>'index']);
  }

update post


PostController.php: editの関数を追記します。requestはpostにpatch, putを追加することが推奨されています。

  public function edit($id = null)
  {
    $post = $this->Posts->get($id);
    if ($this->request->is(['post', 'patch', 'put'])) {
      $post = $this->Posts->patchEntity($post, $this->request->data);
      if($this->Posts->save($post)){
        $this->Flash->success('Edit Success!');
        return $this->redirect(['action'=>'index']);
      } else {
        $this->Flash->error('Edit Error!');
      }
    }
    $this->set(compact('post'));
  }

edit.ctp: addと同じようにviewを作成します。buttonはUpdateになります。

<?php
$this->assign('title', 'Edit Post');
?>

<h1>
  <?= $this->Html->link('Back', ['action'=>'index'], ['class'=>['pull-right', 'fs12']]); ?>
  Edit Post
</h1>

<?= $this->Form->create($post); ?>
<?= $this->Form->input('title'); ?>
<?= $this->Form->input('body', ['row'=>'3']); ?>
<?= $this->Form->button('Update'); ?>

<?= $this->Form->end(); ?>

Flash helper


my_layout.ctp

<body>
    <?= $this->element('my_header'); ?>
    <?= $this->Flash->render();?>
    <div class="container">
        <?= $this->fetch('content') ?>
    </div>
</body>

Posts.Controller.php

  public function add()
  {
    $post = $this->Posts->newEntity();
    if ($this->request->is('post')) {
      $post = $this->Posts->patchEntity($post, $this->request->data);
      if($this->Posts->save($post)){
        $this->Flash->success('Add Success!');
        return $this->redirect(['action'=>'index']);
      } else {
        $this->Flash->error('Add Error!');
      }
    }
    $this->set(compact('post'));
  }
}

veiw ctpの書き方


コントローラーにメソッドを追加

<?php

// /posts/index
// /posts
// /(controller)/(action)/(options)

namespace App\Controller;

class PostsController extends AppController
{
  public function index()
  {
    $posts = $this->Posts->find('all');
      // ->order(['title' => 'DESC'])
      // ->limit(2)
      // ->where(['title like' => '%3']);
    $this->set('posts', $posts);
  }

  public function view($id = null)
  {
    $post = $this->Posts->get($id);
    $this->set(compact('post'));
  }
}

src->template->view.ctp

<?php
$this->assign('title', 'Blog Detail');
?>

<h1>
  <?= $this->Html->link('Back', ['action'=>'index'], ['class'=>['pull-right', 'fs12']]); ?>
  <?= h($post->title); ?>
</h1>

<p><?= nl2br(h($post->body)); ?></p>

Postの追加: NewEntity

<h1>
  <?= $this->Html->link('Add New', ['action'=>'add'], ['class'=>['pull-right', 'fs12']]); ?>
  Blog Posts
</h1>
  public function add()
  {
    $post = $this->Posts->newEntity();
    $this->set(compact('post'));
  }

Cakeのformヘルパー: src->template->view.ctp

<?= this->Form->create($post); ?>
<?= this->Form->input('title'); ?>
<?= this->Form->input('body', ['row'=>'3']); ?>
<?= this->Form->button('Add'); ?>

<?= this->Form->end(); ?>

データの入力

if ($this->request->is('post')){
      $post = $this->Posts->patchEntity($post, $this->request->data);
      $this->Posts->save($post);
      return $this->redirect(['action'=>'index']);
    }

validation:PostsTable.php

<?php

 namespace App\Model\Table;

 use Cake\ORM\Table;
 use Cake\Validation\Validator;

 class PostsTable extends Table
 {
   public function initialize(array $config)
   {
     $this->addBehavior('Timestamp');
   }
   public function validationDefault(Validator $validator)
   {
     $validator
       ->notEmpty('title')
       ->requirePresence('title')
       ->notEmpty('body')
       ->requirePresence('body')
       ->add('body', [
         'length' => [
           'rule' => ['minLength', 10],
           'message' => 'body length must be 10+'
         ]
       ]);
     return $validator;
   }
 }

PostsController

  public function add()
  {
    $post = $this->Posts->newEntity();
    if ($this->request->is('post')) {
      $post = $this->Posts->patchEntity($post, $this->request->data);
      if($this->Posts->save($post)){
        return $this->redirect(['action'=>'index']);
      } else {
      }
    }
    $this->set(compact('post'));
  }
}