マイクロフレームワーク Flight で MariaDB と csv を取り扱う

次のルートを作成します。

http://localhost:9000/mariadb
>MariaDB のテーブル city.cities の表示

http://localhost:9000/csv
>data/cities.csv の表示

http://localhost:9000/export
>MariaDB から、data/cities_out.csv へのイクスポート

http://localhost:9000/import
>data/cities.csv から MariaDB へのインポート

1) index.php

```php:index.php
<?php
// -----------------------------------------------------------------
require 'flight/Flight.php';

require 'app/views/greeting.php';


Flight::route('/',array('Greeting','hello'));
Flight::route('/mariadb',array('Greeting','mariadb'));
Flight::route('/csv',array('Greeting','csv'));
Flight::route('/export',array('Greeting','export'));
Flight::route('/import',array('Greeting','import'));


Flight::start();
// -----------------------------------------------------------------
```

2) app/views/greeting.php

```php:app/views/greeting.php
<?php
require_once ("csv_manipulate.php");
require_once ("mariadb_manipulate.php");

// -----------------------------------------------------------------
class Greeting {
// -----------------------------------------------------------------
    public static function hello() {
        echo '*** Greeting ***<p />';
	echo '<a href="/mariadb">mariadb</a><p />';
	echo '<a href="/csv">csv</a><p />';
	echo '<a href="/export">export</a><p />';
	echo '<a href="/import">import</a><p />';
        echo '*** Jul/31/2018 *** AM 10:37<p />';
    }

// -----------------------------------------------------------------
    public static function export() 
{
	$file_csv = "data/cities_out.csv";

	$dict_aa = Mariadb_manipulate::mariadb_to_dict_proc ();

	Csv_manipulate::dict_display_proc ($dict_aa);

	Csv_manipulate::csv_write_proc ($file_csv,$dict_aa);

	echo '<p />';
        echo '*** Jul/31/2018 *** AM 10:40<p />';
    }
// -----------------------------------------------------------------
public static function import()
{
	$file_csv = "data/cities.csv";

	$dict_aa = Csv_manipulate::csv_read_proc ($file_csv);

	Csv_manipulate::dict_display_proc ($dict_aa);

	Mariadb_manipulate::dict_to_db_proc ($dict_aa);

	$dbcon=null;

	echo '<p />';
        echo '*** Jul/31/2018 *** PM 15:26<p />';
}

// -----------------------------------------------------------------
public static function csv()
{
	$file_csv = "data/cities.csv";

	$dict_aa = Csv_manipulate::csv_read_proc ($file_csv);
	Csv_manipulate::dict_display_proc ($dict_aa);

	echo '<p />';
        echo '*** Jul/31/2018 *** AM 10:55<p />';
}

// -----------------------------------------------------------------
public static function mariadb()
{
	$dict_aa = Mariadb_manipulate::mariadb_to_dict_proc ();
	Csv_manipulate::dict_display_proc ($dict_aa);

	echo '<p />';
        echo '*** Jul/31/2018 *** AM 10:55<p />';
}

// -----------------------------------------------------------------
}
// -----------------------------------------------------------------
?>
```

3) app/views/mariadb_manipulate.php

```php:app/views/mariadb_manipulate.php
<?php
// -----------------------------------------------------------------
class Mariadb_manipulate {
// -----------------------------------------------------------------
public static function mariadb_to_dict_proc ()
{
	$dict_aa = array ();

	$user = 'scott';
	$password = 'tiger123';

	Flight::register('db','PDO', array('mysql:host=localhost;dbname=city',$user,$password));

	$db = Flight::db();


	$sql = 'select * from cities order by id';

	foreach ($db->query($sql) as $row)
		{
		$dict_unit = array ();
		$dict_unit['name'] = $row['name'];
		$dict_unit['population'] = $row['population'];
		$dict_unit['date_mod'] = $row['date_mod'];
		$dict_aa[$row['id']]= $dict_unit;
		}

	$db = null;

	return	$dict_aa;
}

// -----------------------------------------------------------------
public static function drop_table_proc ($dbcon)
{
	$sql="drop table cities;";

	$dbcon->beginTransaction();
	$sth = $dbcon->exec($sql);
	$dbcon->commit();
}

// -----------------------------------------------------------------
public static function create_table_proc ($dbcon)
{
	$sql="create TABLE cities (id varchar(10) NOT NULL PRIMARY KEY,name text,"
		. "population int,date_mod text)";

$dbcon->beginTransaction();
$sth = $dbcon->exec($sql);
$dbcon->commit();
}

// -----------------------------------------------------------------
public static function insert_proc ($db,$id_in,$name_in,$population_in,$date_mod_in)
{
	$sql="insert into cities (id, Name, Population, date_mod) "
		. "values ('" . $id_in . "','" . $name_in . "',"
		. $population_in .  ",'" . $date_mod_in . "')";

	$db->beginTransaction();
	$sth = $db->exec($sql);
	$db->commit();

}

// -----------------------------------------------------------------
public static function dict_to_db_proc ($dict_aa)
{
	$user = 'scott';
	$password = 'tiger123';

	Flight::register('db','PDO', array('mysql:host=localhost;dbname=city',$user,$password));

	$db = Flight::db();

	Mariadb_manipulate::drop_table_proc ($db);
	Mariadb_manipulate::create_table_proc ($db);

	foreach ($dict_aa as $key => $value)
		{
		$name = $value['name'];
		$population = $value['population'];
		$date_mod = $value['date_mod'];
		Mariadb_manipulate::insert_proc ($db,$key,$name,$population,$date_mod);
		}

	$db = null;
}

// -----------------------------------------------------------------
}
// -----------------------------------------------------------------
?>
```

4) app/views/csv_manipulate.php

```php:app/views/csv_manipulate.php
<?php
// -----------------------------------------------------------------
class Csv_manipulate {
// -----------------------------------------------------------------
public static function dict_display_proc ($dict_aa)
{
	ksort ($dict_aa);

	echo "<table>";

	foreach ($dict_aa as $key => $value)
		{
		echo "<tr>";

		$name = $value["name"];
		$population = $value['population'];
		$date_mod = $value['date_mod'];

		echo "<td>$key</td>";
		echo "<td>$name</td>";
		echo "<td>$population</td>";
		echo "<td>$date_mod</td>";
		echo "</tr>";
		}

	echo "</table>";
}

// -----------------------------------------------------------------
public static function csv_read_proc ($file_in)
{
	$dict_aa = array ();

	$file = new SplFileObject($file_in); 
	$file->setFlags(SplFileObject::READ_CSV);
	foreach($file as $line)
		{
		if(!is_null($line[0]))
			{
			$dict_unit = array ();
			$dict_unit['name'] = $line[1];
			$dict_unit['population'] = $line[2];
			$dict_unit['date_mod'] = $line[3];
			$dict_aa[$line[0]]= $dict_unit;
			}
		}

	$file = null;

	return	$dict_aa;
}

// -----------------------------------------------------------------
public static function csv_write_proc ($fname_out,$dict_aa)
{
	$fp_out=fopen ($fname_out,"w");

	foreach ($dict_aa as $key => $value)
		{
		$fields = [$key,$value['name'],
			$value['population'],$value['date_mod']];
		fputcsv ($fp_out, $fields);
		}

	fclose ($fp_out);

	chmod ($fname_out,0666);
}

// -----------------------------------------------------------------
}
// -----------------------------------------------------------------
?>
```

5) フォルダーの構造
index.php, app, data を作成しました。あとは、flight のものです。

```text
$ tree -L 1
.
├── app
├── composer.json
├── data
├── flight
├── index.php
├── LICENSE
├── README.md
├── tests
└── VERSION
```