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

Changeset 11503


Ignore:
Timestamp:
Apr 27, 2009, 5:50:55 PM (13 years ago)
Author:
fukamachi
Message:

DBコンバータで、memberテーブルの変換に対応

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

Legend:

Unmodified
Added
Removed
  • OpenPNE/branches/work/fukamachi/db_converter/db_converter.php

    r11499 r11503  
    3838}
    3939
     40$insert_member = table_member_sql($dbh);
     41$insert_ary = array_merge($insert_ary, $insert_member);
     42
    4043// とりあえず出力
    4144print_r($insert_ary);
     
    4548 * 関数群 *
    4649 **********/
     50
     51// DBエラーならばdie
     52function dbdie($result, $sql)
     53{
     54    if (DB::isError($result)) {
     55        die($result->getMessage() . " : " . $sql);
     56    }
     57}
    4758
    4859// DBハンドラを返す
     
    5667
    5768    $dbh = DB::connect($dsn);
    58     if (DB::isError($dbh)) {
    59         die($dbh->getMessage());
     69    dbdie($result, $sql);
     70
     71    return $dbh;
     72}
     73
     74// SQL検索結果からINSERT文を作成
     75function get_sql_from_query($result, $table, $columns_ary)
     76{
     77    $sql_command = array();
     78
     79    $columns = implode(",", $columns_ary);
     80
     81    while ($row = $result->fetchRow()) {
     82        $values = implode("\",\"", $row);
     83        array_push($sql_command, "INSERT INTO `{$table}` ({$columns}) VALUES (\"{$values}\");");
    6084    }
    6185
    62     return $dbh;
     86    return $sql_command;
     87}
     88
     89function sql_add_increment_column($result, $table, $columns_ary, $add_col, $start)
     90{
     91    $sql_command = array();
     92
     93    array_unshift($columns_ary, $add_col);
     94    $columns = implode(",", $columns_ary);
     95
     96    while ($row = $result->fetchRow()) {
     97        array_unshift($row, $start);
     98        $values = implode("\",\"", $row);
     99        array_push($sql_command, "INSERT INTO `{$table}` ({$columns}) VALUES (\"{$values}\");");
     100        ++$start;
     101    }
     102
     103    return $sql_command;
    63104}
    64105
     
    72113    $sql_command = array();
    73114
    74     $columns = implode(",", $columns_ary);
    75115    $result = $dbh->query($sql);
    76     if (DB::isError($result)) {
    77         die($result->getMessage() . " : " . $sql);
    78     }
    79     while ($row = $result->fetchRow()) {
    80         $values = implode("\",\"", $row);
    81         array_push($sql_command, "INSERT INTO `{$table}` ({$columns}) VALUES (\"{$values}\");");
    82     }
     116    dbdie($result, $sql);
    83117
    84     return $sql_command;
     118    return get_sql_from_query($result, $table, $columns_ary);
    85119}
    86120
     121/**********************
     122 * テーブル個別処理用 *
     123 **********************/
     124// memberのINSERT文生成
     125function table_member_sql($dbh)
     126{
     127    $table = "member";
     128    $columns_ary = array("id", "name", "is_active", "invite_member_id", "created_at", "updated_at");
     129
     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
     149END_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
     167END_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);
     175}
    87176?>
  • OpenPNE/branches/work/fukamachi/db_converter/db_relation.yml

    r11499 r11503  
    239239    FROM c_friend
    240240
    241 - table: member
    242    #!!!is_active
    243   column: [id, name, is_active, invite_member_id, created_at, updated_at]
    244   sql: >
    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 
    254241- table: member_config
    255242   #!!!name, value
     243   #!!! value = lastLogin
     244   #!!! register_auth_mode
    256245  column: [id, member_id, name, value]
    257246  sql: >
     
    268257        "mobile_address" as name,
    269258        ktai_address as value
     259    FROM c_member_secure
     260    UNION
     261    SELECT
     262        c_member_secure_id as id,
     263        c_member_id as member_id,
     264        "password" as name,
     265        hashed_password as value
     266    FROM c_member_secure
     267    UNION
     268    SELECT
     269        c_member_secure_id as id,
     270        c_member_id as member_id,
     271        "mobile_uid" as name,
     272        easy_access_id as value
    270273    FROM c_member_secure
    271274
     
    285288
    286289- table: member_profile
    287   column: [id, member_id, profile_id, profile_option_id]
     290  #!!!PHP
     291  column: [id, member_id, profile_id, profile_option_id, value, public_flag]
    288292  sql: >
    289293    SELECT
     
    292296        c_profile_id as profile_id,
    293297        c_profile_option_id as profile_option_id,
    294         value
     298        value,
     299        public_flag
    295300    FROM c_member_profile
    296301
Note: See TracChangeset for help on using the changeset viewer.