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

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

sqlite3 の表示

csv の表示

csv への export

csv からの import

1) index.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

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

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

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

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;

データの復元は、

sqlite3 cities.db < cities.dump

6) data/cities.csv

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