メモ: Idiorm使ってみた

## 参考

* [Querying — Idiorm documentation](http://idiorm.readthedocs.io/en/latest/querying.html)
    * [PHPの薄いDBライブラリ「Idiorm」を使ってみた - Qiita](http://qiita.com/naga3/items/87fef230ac86aeec1eea)
    * [PHP製軽量ORM Idiormの使い方例 - Qiita](http://qiita.com/benchan/items/35a4c6f20105c0eb652a)
    * [Trello のクローン php-simple-kanban を動かしてみた - Qiita](http://qiita.com/tukiyo3/items/29cf2fb814d7c34edca5)

必要なもの : pdo,mysql

```bash:idiorm.phpだけあれば使える手軽さ
wget "https://raw.githubusercontent.com/j4mie/idiorm/master/idiorm.php" \
 --no-check-certificate
```

## 例 (mysql)

```php:例1
<?php
require_once("./idiorm.php");

ORM::configure('mysql:host=localhost;port=3306;dbname=test');
ORM::configure('username', 'root');
ORM::configure('password', '');

//$record = ORM::for_table('テーブル名')
//           ->where('カラム名','検索したい値')
//           ->find_one();
$record = ORM::for_table('テーブル名')->find_one();

print_r($record->as_array());

$data = $record->カラム名;
print $data;
```


## 例1. クリック毎にinsert

![count.png](https://qiita-image-store.s3.amazonaws.com/0/25728/1fa7c95a-d81f-8633-6f61-84d766400fa7.png)

```sql:create.sql 
CREATE TABLE clicks (
  id int(11) NOT NULL AUTO_INCREMENT,
  count int(11) NOT NULL default '0',
  created_at datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY (id)
) TYPE=InnoDB COMMENT='クリック数';
```

```html:index.html(jquery)
<!DOCTYPE html>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
<input type="button" id="btn1" value="click" />
<span id="counts"></span>
<script>
$(function() {
  var counts = 0;
  $('#btn1').click(function() {
    $.ajax('countup.php',
      {
        type: 'get',
        data: { count: counts++ }
      }
    )
    .done(function(data) {
      $("#counts").html('success');
    })
    .fail(function() {
      $("#counts").html('error');
    });
  });
});
</script>
```

* [mzabriskie/axios: Promise based HTTP client for the browser and node.js](https://github.com/mzabriskie/axios)

```html:index.html(axios)
<!DOCTYPE html>
<script src="https://unpkg.com/axios/dist/axios.min.js"></script>
<input type="button" value="click" onclick="countup();" />
<script>
var counts = 0;
function countup() {
  counts++;
  axios.get('./countup.php?count='+counts)
    .then(function (response) {
      console.log(response.statusText);
    })
    .catch(function (error) {
      console.log(error.statusText);
    });
}
</script>
```

* http://idiorm.readthedocs.io/en/latest/models.html#creating-new-records

```php:countup.php
<?php
require_once("./idiorm.php");
$DBNAME='test';
ORM::configure(array(
    'connection_string' => 'mysql:host=127.0.0.1;dbname='.$DBNAME,
    'username' => 'root',
    'password' => ''
));

# validation
$count = $_GET["count"];
if(!is_numeric($count)) {
        die("[quit] not number.");
}

# insert
$click = ORM::for_table('clicks')->create();
$click->count = $count;
$click->set_expr('created_at', 'NOW()');
$click->save();

echo "[success] 200";
```

----

## 例2. userテーブル

![count2.png](https://qiita-image-store.s3.amazonaws.com/0/25728/98f984eb-1fe9-65da-dace-7cbe203c06f0.png)

```sql:create.sql
CREATE TABLE clicks (
  id int(11) NOT NULL AUTO_INCREMENT,
  user_id int(11) NOT NULL default '0',
  count int(11) NOT NULL default '0',
  updated_at datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY (id)
) TYPE=InnoDB COMMENT='クリック数';

CREATE TABLE users (
  id int(11) NOT NULL AUTO_INCREMENT,
  sessid varchar(32) NOT NULL default '0',
  updated_at datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY (id)
) TYPE=InnoDB COMMENT='user';
```

```html:index.php
<?php session_start(); ?>
<!DOCTYPE html>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
<input type="button" id="btn1" value="click" />
<span id="counts"></span>
<script>
$(function() {
  var counts = 0;
  var sessid = '<?php echo $_COOKIE["PHPSESSID"]; ?>';
  $('#btn1').click(function() {
    $.ajax('countup.php',
      {
        type: 'get',
        data: {
            sessid: sessid,
            count: ++counts,
        }
      }
    )
    .done(function(data) {
      $("#counts").html("クリック回数:"+counts);
    })
    .fail(function() {
      $("#counts").html('error');
    });
  });
});
</script>
```

```php:countup.php
<?php
require_once("./idiorm.php");
#------------------------------------------------------------------------------
class Counter {
    public $count;
    public $sessid;
    function __construct() {
        $this->count = $_GET["count"];
        $this->sessid = $_GET["sessid"];
    }
    function db_connect() {
        $DBNAME='test';
        ORM::configure(array(
            'connection_string' => 'mysql:host=127.0.0.1;dbname='.$DBNAME,
            'username' => 'root',
            'password' => '',
            'caching' => true,
            'caching_auto_clear' => true,
        ));
    }

    function validation() {
        if(!is_numeric($this->count)) {
        	die("[quit] not number.");
        }
        if(empty($this->sessid)) {
        	die("[quit] no sessid.");
        }
    }
    
    function up() {
        $this->validation();
        $this->db_connect();
        # user
        $user = ORM::for_table('users')->where('sessid', $this->sessid)->find_one();
        if(!$user) {
            # insert
            $user = ORM::for_table('users')->create();
            $user->sessid = $this->sessid;
            $user->set_expr('updated_at', 'NOW()');
            $user->save();
        }
        $user_id = $user->id();
        
        # click数
        $click = ORM::for_table('clicks')->where('user_id', $this->user_id)->find_one();
        if(!$click) {
            $click = ORM::for_table('clicks')->create();
        }
        $click->count = $this->count;
        $click->user_id = $user_id;
        $click->set_expr('updated_at', 'NOW()');
        $click->save();
        
        echo "[success] 200";
    }
}
#------------------------------------------------------------------------------
$Counter = new Counter();
$Counter->up();
```


## 例 (sqlite3)

```sql
CREATE TABLE bbs (
 id INTEGER PRIMARY KEY
, body TEXT
, created_at datetime
);
```

```php
<?php
require_once("vendor/idiorm.php");
ORM::configure('sqlite:a.sqlite3');
ORM::configure('id_column', 'id');

if($_GET["body"]) {
        $record = ORM::for_table('bbs')->create();
        $record->body = $_GET["body"];
        $record->created_at = date("Y-m-d H:i:s");
        $record->save();
}
```

* sqliteを使う場合、親ディレクトリに書き込み権限が必要。
    * 恐らく一旦tmpファイルに書き込んでからmvしているのでは。


## 書き方

### SQL直書き

```php:raw_query
//SQL直書き (raw_query)
$query = 'SELECT * FROM members WHERE name = :name';
$entries = ORM::for_table('members')
    ->raw_query($query, array('name' => $name))
    ->find_array();
```

### Join

```php
$records = ORM::for_table('addresses')
       ->left_outer_join('members', 'members.id=addresses.memid')
       ->find_many();

foreach ($records as $r) {
    echo sprintf("id: %s, name: %s, pref: %s<br>"
		,$r->id, $r->name, $r->pref);
}
```

## 複数DB接続

* [Multiple Connections — Idiorm documentation](https://idiorm.readthedocs.io/en/latest/connections.html)

```php
<?php
require_once("vendor/idiorm.php");

// a.sqlite3
ORM::configure('sqlite:a.sqlite3', null, 'connection_A');
ORM::configure('id_column', 'id', 'connection_A');
if($_GET["body"]) {
        $record = ORM::for_table('bbs','connection_A')->create();
        $record->body = $_GET["body"];
        $record->save();
}

// b.sqlite3
ORM::configure('sqlite:b.sqlite3', null, 'connection_B');
ORM::configure('id_column', 'id', 'connection_B');
if($_GET["body"]) {
        $record = ORM::for_table('bbs','connection_B')->create();
        $record->body = $_GET["body"];
        $record->save();
}
```

## show tables

* [Querying — Idiorm documentation](https://idiorm.readthedocs.io/en/latest/querying.html?highlight=show%20tables)

```php
$res = ORM::raw_execute("SHOW TABLES LIKE 'sales_%'");
$statement = ORM::get_last_statement();
echo "<pre>";
while ($row = $statement->fetch(PDO::FETCH_NUM)) {
    echo $row[0]."<br>";
}
```