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

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

MariaDB のテーブル city.cities の表示

data/cities.csv の表示

MariaDB から、data/cities_out.csv へのイクスポート

data/cities.csv から MariaDB へのインポート

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

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

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

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 のものです。

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