メモ: database接続

* [neiesc/ListOfMinimalistFrameworks: A list of minimalist frameworks (simple and lightweight).](https://github.com/neiesc/ListOfMinimalistFrameworks#web-framework-for-go)


## medoo

* [Medoo - The lightest PHP database framework to accelerate development](http://medoo.in/)
* [github](https://github.com/catfan/Medoo)
* PHP5.1+
* [Documentation](http://medoo.in/doc)

```php:使い方(mysql)
// If you installed via composer, just use this code to requrie autoloader on the top of your projects.
require 'vendor/autoload.php';
 
// Or if you just download the medoo.php into directory, and require it with the correct path.
require_once 'medoo.php';
 
// Initialize
$database = new medoo([
    'database_type' => 'mysql',
    'database_name' => 'name',
    'server' => 'localhost',
    'username' => 'your_username',
    'password' => 'your_password',
    'charset' => 'utf8'
]);
 
// Enjoy
$database->insert('account', [
    'user_name' => 'foo',
    'email' => 'foo@bar.com',
    'age' => 25,
    'lang' => ['en', 'fr', 'jp', 'cn']
]);
```

```php:sqlite
$database = new medoo([
	'database_type' => 'sqlite',
	'database_file' => 'my/database/path/database.db'
]);
 
$database->insert("account", [
	"user_name" => "foo",
	"email" => "foo@bar.com"
]);
```

## NotORM

* [NotORM - PHP library for simple working with data in the database](http://www.notorm.com/)
* PHP5.1+

```php:
include "NotORM.php";
$pdo = new PDO("mysql:dbname=software");
$db = new NotORM($pdo);

$applications = $db->application()
    ->select("id, title")
    ->where("web LIKE ?", "http://%")
    ->order("title")
    ->limit(10)
;
foreach ($applications as $id => $application) {
    echo "$application[title]\n";
}

$application = $db->application[1]; // get by primary key
$application = $db->application("title = ?", "Adminer")->fetch();

echo $application->author["name"] . "\n"; // get name of the application author
foreach ($application->application_tag() as $application_tag) { // get all tags of $application
    echo $application_tag->tag["name"] . "\n"; // print the tag name
}

// get all applications ordered by author's name
foreach ($db->application()->order("author.name") as $application) {
    echo $application->author["name"] . ": $application[title]\n";
}

echo $db->application()->max("id"); // get maximum ID
foreach ($db->application() as $application) {
    // get count of each application's tags
    echo $application->application_tag()->count("*") . "\n";
}
```

## ActiveRecord

* [github](https://github.com/bephp/activerecord)


## pdo

### mysql

* [PDO_Tutorial_for_MySQL_Developers](http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers)

```php
<?php
$mysql = new PDO('mysql:host=localhost;dbname=test;charset=sjis', 'root' ,'pass');
```

### sqlite

```bash:
apt-get install -y \
  php-db php7.0-sqlite3
```

```php
<?php
$sqlite = new PDO('sqlite:logs.sqlite');
```

### mysqlのデータをsqliteに格納

```php:短い例
<?php
function to_utf8($str_sjis) {
        return mb_convert_encoding($str_sjis, "utf-8", "sjis");
}

$mysql = new PDO('mysql:host=localhost;dbname=test;charset=sjis', 'root' ,'pass');

$sqlite = new PDO('sqlite:logs.sqlite');
$sqlite->query("create table if not exists `groups` (id, sn, name);");

$sql = "select sn, name from groups;";
$rows = $mysql->query($sql)->fetchAll(PDO::FETCH_ASSOC);

foreach($rows as $key=>$val) {
        $sql = "INSERT INTO groups (sn, name) values (:sn, :name);";
        $stmt = $sqlite->prepare($sql);
        $stmt->bindValue(":sn", $val["sn"]);
        $stmt->bindValue(":name", to_utf8($val["name"]));
        $stmt->execute();
}
```

* tmpfsに置くとよい。
* `group`はカラム名に使えない。


```php:もうちょい長い例
<?php
function to_utf8($str_sjis) {
        return mb_convert_encoding($str_sjis, "utf-8", "sjis");
}

function is_record_exists($sqlite, $sql) {
        $count = $sqlite->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        if($count[0]["count"]) {
                return true;
        }
        return false;
}

function insert($sqlite, $rows, $gp_num) {
        foreach($rows as $key=>$val) {
                $sql = "INSERT INTO groups (yymmdd, gp_num, sn, name) values (:yymmdd, :gp_num, :sn, :name);";
                $stmt = $sqlite->prepare($sql);
                $stmt->bindValue(":yymmdd", date("Y-m-d"));
                $stmt->bindValue(":gp_num", $gp_num);
                $stmt->bindValue(":sn", $val["sn"]);
                $stmt->bindValue(":name", to_utf8($val["name"]));
                $stmt->execute();
        }
}

function update($sqlite, $rows, $gp_num) {
        foreach($rows as $key=>$val) {
                $sql = "UPDATE groups set sn = :sn, name = :sn WHERE yymmdd = :yymmdd and gp_num = :gp_num;";
                $stmt = $sqlite->prepare($sql);
                $stmt->bindValue(":yymmdd", date("Y-m-d"));
                $stmt->bindValue(":gp_num", $gp_num);
                $stmt->bindValue(":sn", $val["sn"]);
                $stmt->bindValue(":name", to_utf8($val["name"]));
                $stmt->execute();
        }
}

$db = "groups";
$gp_num = "001";

$mysql = new PDO('mysql:host=localhost;dbname='.$db.';charset=sjis', 'root' ,'');

$sqlite = new PDO('sqlite:logs.sqlite');
$sqlite->query("create table if not exists `groups` (yymmdd, gp_num, sn, name);");

$sql = "select * from shop_master;";
$rows = $mysql->query($sql)->fetchAll(PDO::FETCH_ASSOC);

$sql = "select count(*) as count from groups where yymmdd = '".date("Y-m-d")."' and gp_num = '".$gp_num."';";
if (!is_record_exists($sqlite, $sql)) {
        insert($sqlite, $rows, $gp_num);
} else {
        update($sqlite, $rows, $gp_num);
}
```