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

次のルートを作成します。
http://localhost:9000/sqlite3
 >sqlite3 の表示

http://localhost:9000/csv
>csv の表示

http://localhost:9000/export
>csv への export

http://localhost:9000/import
>csv からの import

1) index.php

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

require 'application/views/greeting.php';


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


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

2) application/views/greeting.php

```php:application/views/greeting.php
<?php
require_once ("csv_manipulate.php");
require_once ("sqlite3_manipulate.php");

// -----------------------------------------------------------------
class Greeting {
// -----------------------------------------------------------------
    public static function hello() {
        echo '*** Greeting ***<p />';
        echo '*** 挨拶 ***<p />';
        echo '*** Jul/30/2018 *** AM 10:37<p />';
    }

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

	$dict_aa = Sqlite3_manipulate::sqlite3_to_dict_proc ($file_sqlite3);

	Csv_manipulate::dict_display_proc ($dict_aa);

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

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

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

	Csv_manipulate::dict_display_proc ($dict_aa);

	$dbcon = new PDO ("sqlite:" . $file_sqlite3);

	Sqlite3_manipulate::drop_table_proc ($dbcon);
	Sqlite3_manipulate::create_table_proc ($dbcon);

	Sqlite3_manipulate::dict_to_db_proc ($dict_aa,$dbcon);

	$dbcon=null;

        echo '*** Jul/30/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 '*** Jul/30/2018 *** AM 10:55<p />';
    }

// -----------------------------------------------------------------
    public static function sqlite3()
{
	$file_sqlite3 = "data/cities.db";

	$dict_aa = Sqlite3_manipulate::sqlite3_to_dict_proc ($file_sqlite3);
	Csv_manipulate::dict_display_proc ($dict_aa);

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

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

3) application/views/sqlite3_manipulate.php

```php:application/views/sqlite3_manipulate.php
<?php
// -----------------------------------------------------------------
class Sqlite3_manipulate {
// -----------------------------------------------------------------
public static function sqlite3_to_dict_proc ($file_sqlite3)
{
	$dict_aa = array ();

	$dbcon = new PDO ("sqlite:" . $file_sqlite3);

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

	foreach ($dbcon->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;
		}

	$dbcon = 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 ($dbcon,$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 . "')";

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

}

// -----------------------------------------------------------------
public static function dict_to_db_proc ($dict_aa,$dbcon)
{
	foreach ($dict_aa as $key => $value)
		{
		$name = $value['name'];
		$population = $value['population'];
		$date_mod = $value['date_mod'];
		Sqlite3_manipulate::insert_proc ($dbcon,$key,$name,$population,$date_mod);
		}
}

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

4) application/views/csv_manipulate.php

```php:application/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) data/cities.db の dump

```text:cities.dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE cities (id varchar(10) NOT NULL PRIMARY KEY,name text,population int,date_mod text);
INSERT INTO cities VALUES('t0711','郡山',34516,'2002-8-9');
INSERT INTO cities VALUES('t0712','会津若松',84973,'2002-9-21');
INSERT INTO cities VALUES('t0713','白河',98214,'2002-6-7');
INSERT INTO cities VALUES('t0714','福島',32579,'2002-11-19');
INSERT INTO cities VALUES('t0715','喜多方',72145,'2002-7-24');
INSERT INTO cities VALUES('t0716','二本松',26319,'2002-6-2');
INSERT INTO cities VALUES('t0717','いわき',82754,'2002-4-9');
INSERT INTO cities VALUES('t0718','相馬',57213,'2002-5-14');
INSERT INTO cities VALUES('t0719','須賀川',21598,'2002-3-12');
COMMIT;
```

データの復元は、

```bash
sqlite3 cities.db < cities.dump
```

6) data/cities.csv

```text:data/cities.csv
t1271,千葉,71269,2002-9-22
t1272,勝浦,63473,2002-8-12
t1273,市原,57182,2002-6-1
t1274,流山,46359,2002-10-29
t1275,八千代,32765,2002-7-14
t1276,我孫子,29134,2002-6-12
t1277,鴨川,82157,2002-4-17
t1278,銚子,91548,2002-2-24
t1279,市川,74256,2002-11-12
```