ここの情報は古いです。ご理解頂いた上でお取り扱いください。

Changeset 11504


Ignore:
Timestamp:
Apr 27, 2009, 6:43:59 PM (13 years ago)
Author:
fukamachi
Message:

DBコンバータをリファクタリングして、テーブルのマージを関数化

Location:
OpenPNE/branches/work/fukamachi/db_converter
Files:
2 edited

Legend:

Unmodified
Added
Removed

  • OpenPNE/branches/work/fukamachi/db_converter/db_converter.php

    r11503 r11504  
    3232$insert_ary = array();
    3333foreach ($sql_list as $sql) {
    34     array_push(
    35                 $insert_ary,
    36                 generate_insert_sql($dbh, $sql['table'], $sql['column'], $sql['sql'])
    37         );
     34    $insert;
     35    if ($sql['merge'] === 1) {
     36        $table_sql = "table_" . $sql['table'] . "_sql";
     37        $insert = $table_sql($dbh, $sql['table'], $sql['column'], $sql['sql1'], $sql['sql2']);
     38    } else {
     39        $insert = generate_insert_sql($dbh, $sql['table'], $sql['column'], $sql['sql']);
     40    }
     41    array_push($insert_ary, $insert);
    3842}
    39 
    40 $insert_member = table_member_sql($dbh);
    41 $insert_ary = array_merge($insert_ary, $insert_member);
    4243
    4344// とりあえず出力
    … …  
    6162{
    6263    $dsn = "mysql://"
    63                         . $conf['username']
    64                         . ":" . $conf['password']
    65                         . "@" . $conf['host']
    66                         . "/" . $conf['database'];
     64            . $conf['username']
     65            . ":" . $conf['password']
     66            . "@" . $conf['host']
     67            . "/" . $conf['database'];
    6768
    6869    $dbh = DB::connect($dsn);
    … …  
    7071
    7172    return $dbh;
     73}
     74
     75// 指定テーブル・カラムの最大値を取得
     76function get_max($dbh, $table, $col)
     77{
     78    $sql = "SELECT max($col) FROM $table";
     79    $result = $dbh->query($sql);
     80    dbdie($result, $sql);
     81    return array_shift($result->fetchRow());
    7282}
    7383
    … …  
    107117function generate_insert_sql($dbh, $table, $columns_ary, $sql)
    108118{
    109         if ($columns_ary === NULL) {
    110                 return;
    111         }
    112 
    113     $sql_command = array();
     119    if ($columns_ary === NULL) {
     120        return;
     121    }
    114122
    115123    $result = $dbh->query($sql);
    … …  
    122130 * テーブル個別処理用 *
    123131 **********************/
     132
     133function table_merge_add_id($dbh, $max_id, $new_table, $columns_ary, $sql1, $sql2)
     134{
     135    $result1 = $dbh->query($sql1);
     136    dbdie($result1, $sql1);
     137
     138    $insert1 = get_sql_from_query($result1, $new_table, $columns_ary);
     139
     140    $result2 = $dbh->query($sql2);
     141    dbdie($result2, $sql2);
     142
     143    $insert2 = sql_add_increment_column($result2, $new_table, $columns_ary, "id", $max_id + 1);
     144
     145    return array_merge($insert1, $insert2);
     146}
     147
    124148// memberのINSERT文生成
    125 function table_member_sql($dbh)
     149function table_member_sql($dbh, $table, $columns_ary, $sql1, $sql2)
    126150{
    127     $table = "member";
    128     $columns_ary = array("id", "name", "is_active", "invite_member_id", "created_at", "updated_at");
     151    // 登録済みユーザの最大IDを取得
     152    $max_id = get_max($dbh, "c_member", "c_member_id");
    129153
    130     // 登録済みユーザの最大IDを取得
    131     $sql = "SELECT max(c_member_id) FROM c_member";
    132     $result = $dbh->query($sql);
    133     dbdie($result, $sql);
    134     $row = $result->fetchRow();
    135     $max_id = array_shift($row);
    136 
    137     /*
    138      * 登録済みのユーザ(c_member)
    139      */
    140     $sql =<<<END_OF_SQL
    141         SELECT
    142             c_member_id as id,
    143             nickname as name,
    144             1 as is_active,
    145             c_member_id_invite as invite_member_id,
    146             r_date as created_at,
    147             u_datetime as updated_at
    148         FROM c_member
    149 END_OF_SQL;
    150 
    151     $result_member = $dbh->query($sql);
    152     dbdie($result_member, $sql);
    153 
    154     $ins_members = get_sql_from_query($result_member, $table, $columns_ary);
    155 
    156     /*
    157      * 未登録のユーザ(c_member_pre)
    158      */
    159     $sql =<<<END_OF_SQL
    160         SELECT
    161             nickname as name,
    162             0 as is_active,
    163             c_member_id_invite as invite_member_id,
    164             r_date as created_at,
    165             r_date as updated_at
    166         FROM c_member_pre
    167 END_OF_SQL;
    168 
    169     $result_member_pre = $dbh->query($sql);
    170     dbdie($result_member_pre, $sql);
    171 
    172     $ins_members_pre = sql_add_increment_column($result, $table, $columns_ary, "id", $max_id + 1);
    173 
    174     return array_merge($ins_members, $ins_members_pre);
     154    return table_merge_add_id($dbh, $max_id, "member", $columns_ary, $sql1, $sql2);
    175155}
    176156?>

  • OpenPNE/branches/work/fukamachi/db_converter/db_relation.yml

    r11503 r11504  
    238238        r_datetime_intro as updated_at
    239239    FROM c_friend
     240
     241- table: member
     242  merge: 1
     243  column: [id, name, is_active, invite_member_id, created_at, updated_at]
     244  sql1: >
     245    SELECT
     246        c_member_id as id,
     247        nickname as name,
     248        1 as is_active,
     249        c_member_id_invite as invite_member_id,
     250        r_date as created_at,
     251        u_datetime as updated_at
     252    FROM c_member
     253  sql2: >
     254    SELECT
     255        nickname as name,
     256        0 as is_active,
     257        c_member_id_invite as invite_member_id,
     258        r_date as created_at,
     259        r_date as updated_at
     260    FROM c_member_pre
    240261
    241262- table: member_config
Note: See TracChangeset for help on using the changeset viewer.