Favo Recipe の複数のバックアップデータをマージしようとして失敗した残骸

favo_recipe_backup_merge.php

<?php

// これはなに？
//   Favo Recipe - https://play.google.com/store/apps/details?id=jp.yy_soft.favo_recipe&hl=ja
//   の複数のバックアップデータをマージしようとして失敗した残骸
// 準備
//   バックアップデータの in1/FR を SQLite3 で読み込み [1-3].sql としたものを準備
// 実行
//   実行すると、一部のテーブルのマージ結果と画像コピー用のコマンドラインが出力されるので
//   適当に切り張りしてSQLを作ってデータベースを作る
// が、バックアップデータをアプリに食わせると何も出てこない、、、なぞだ

$base = '~/test/cookbook';

/*
Recipe
; photo_filename
; category_id -> MasterCategory._id

Ingredient
; recipe_id -> Recipe._id

Instruction
; recipe_id -> Recipe._id

Tag
; recipe_id -> Recipe._id
; tag_id -> MasterTag._id
*/

$relations = [
    'Recipe' => [
        '_id' => function ($sequence, $v) {
                return $sequence['Recipe'] + intval($v);
            },
        'photo_filename' => function ($sequence, $v) {
            return preg_replace_callback('/(recipe_)([0-9]+)(\.jpg)/', function ($m) use ($sequence) {
                    if (isset($sequence['Recipe'])) {
// 画像のコピー用コマンドライン出力
echo sprintf('cp -p "%s" "%s"'.PHP_EOL, $m[0], $m[1].($sequence['Recipe'] + intval($m[2])).$m[3]);
                        return $m[1].($sequence['Recipe'] + intval($m[2])).$m[3];
                    } else {
                        return $m[0];
                    }
                }, $v);
        },
    ],
    'Ingredient' => [
        '_id' => function ($sequence, $v) {
                return $sequence['Ingredient'] + intval($v);
            },
        'recipe_id' => function ($sequence, $v) {
                return $sequence['Recipe'] + intval($v);
            },
    ],
    'Instruction' => [
        '_id' => function ($sequence, $v) {
                return $sequence['Instruction'] + intval($v);
            },
        'recipe_id' => function ($sequence, $v) {
                return $sequence['Recipe'] + intval($v);
            },
    ],
    'Tag' => [
        '_id' => function ($sequence, $v) {
                return $sequence['Tag'] + intval($v);
            },
        'recipe_id' => function ($sequence, $v) {
                return $sequence['Recipe'] + intval($v);
            },
    ],
];

$db = [];

$sequence = [];

for ($i = 1; $i <= 3; $i++)
{
    $data = file_get_contents($base . '/'.$i.'.sql');
    $data = str_replace("\r", "\n", str_replace("\r\n", "\n", $data));
    
    $tables_ = [];
    $inserts_ = [];
    $sequence_ = [];
    
    foreach (explode("\n", $data) as $line)
    {
        if (preg_match('/CREATE TABLE (.+?) \((.+?)\);/', $line, $m))
        {
            $sql = [ 'table' => $m[1], 'fields' => [], 'lookup' => [] ];
            foreach (explode(',', $m[2]) as $k => $v) {
                if (preg_match('/^([^\s]+) ([^\s]+)(.*)$/', $v, $mm)) {
                    $sql['fields'][] = [ 'name' => $mm[1], 'type' => $mm[2], 'option' => ltrim($mm[3]) ];
                    $sql['lookup']['name'] = $k;
                }
                else {
                    $sql['fields'][] = $v;
                }
            }
            $tables_[$sql['table']] = [ 'fields' => $sql['fields'], 'lookup' => $sql['lookup'] ];
        }
        else if (preg_match('/INSERT INTO "(.+?)" VALUES\((.+?)\);/', $line, $m))
        {
            $sql = [ 'table' => $m[1], 'fields' => [] ];
            foreach (str_getcsv($m[2], ',', '\'') as $k => $v) {
                $field_info = $tables_[$sql['table']]['fields'][$k];
                if (isset($relations[ $sql['table'] ][ $field_info['name'] ])) {
                    $v = $relations[ $sql['table'] ][ $field_info['name'] ]($sequence, $v);
                }
                switch ('null' == strtolower($v) ? 'null' : strtolower($field_info['type']))
                {
                case 'null':
                case 'integer':
                    $sql['fields'][] = $v;
                    break;
                default:
                    $sql['fields'][] = '\''.addcslashes($v, '\'').'\'';
                }
            }
            $inserts_[$sql['table']][] = $sql['fields'];
        }
    }

    $db[] = [
            'tables' => $tables_,
            'inserts' => $inserts_
        ];
    $inserts_['sqlite_sequence'][] = [
            'Tag',
            intval($inserts_['Tag'][count($inserts_['Tag']) - 1][0])
        ];
    foreach ($inserts_['sqlite_sequence'] as $values) {
        $table_name = trim($values[0], '\'');
        $max_seq = intval(trim($values[1], '\''));
        if (!isset($sequence[$table_name]))
            $sequence[$table_name] = 0;
        $sequence[$table_name] += $max_seq;
    }
    
}

echo PHP_EOL.PHP_EOL;

$tables  = $db[0]['tables'];

//print_r($tables);
//print_r($sequence);

//exit;

foreach ($tables as $table_name => $table_info)
{
    if (false === array_search($table_name, [
            'Recipe','Ingredient','Instruction','Tag'
        ]))
        continue;

    echo sprintf('CREATE TABLE %s (%s);'.PHP_EOL
            , $table_name
            , implode(',', array_reduce($table_info['fields'], function ($r, $v) {
                    $r[] = is_string($v) ? $v : trim(implode(' ', [$v['name'], $v['type'], $v['option']]));
                    return $r;
                }, []) )
            );
            
    foreach ($db as $db_)
    {
        $inserts = $db_['inserts'];
    
        foreach ($inserts[$table_name] as $values)
        {
            echo sprintf('INSERT INTO "%s" VALUES(%s);'.PHP_EOL
                    , $table_name
                    , implode(',', $values)
                    );
        }
    }

    echo PHP_EOL;
}

foreach ($sequence as $table_name => $max_seq)
{
    echo sprintf('INSERT INTO "%s" VALUES(\'%s\',%s);'.PHP_EOL
            , 'sqlite_sequence'
            , $table_name
            , $max_seq
            );
}