☰

Sqlite：将字符串 modelID1/modelID2/modelID3 替换为 name1/name2/name3/

tag-icon • tag-icon sed grep regular-expression sqlite
Sqlite：将字符串 modelID1/modelID2/modelID3 替换为 name1/name2/name3/

我的目标是读取 macOS Photos.app 数据库以找出所包含照片的路径。相册及其父文件夹的名称为RK文件夹可以为每张照片确定表格。该父文件夹可以位于其他文件夹中。也可以读出生成的文件夹路径...但采用以下形式：

modelId 1/modelID 2/modelId 3/modelID 4

在这里我不能再进一步，因为我没有足够的知识来用真实的文件夹名称替换字符串。对于可能的解决方案，应该注意的是，我使用数据库的副本，因此文件夹路径也可以直接写入 RKFolder 表中。

SELECT  
modelId, name,folderPath, uuid 
FROM RKFolder 
WHERE implicitAlbumUuid not NULL

结果（摘录）

modelId name            folderPath      uuid

1                       1/              LibraryFolder
2       TopLevelAlbums  1/2/            TopLevelAlbums
7       Test            1/2/7/          kbY7RDHjRLS
8       xxx             1/2/8/          bT5WAkPWQ1
9       Test            1/2/8/9/        9PYeLZDRTne
10      ab              1/2/10/         7Cse21+1SIag
11      abc             1/2/7/11/       pNMvzDdyS%
16      efg             1/2/7/11/16/    a6R97tAxSBW

像这样替换：

modelId name            folderPath                     uuid

1                       /                              LibraryFolder
2       TopLevelAlbums  /TopLevelAlbums/               TopLevelAlbums
7       Test            /TopLevelAlbums/Test/          kbY7RDHjRLS
8       xxx             /TopLevelAlbums/xxx/           bT5WAkPWQ1
9       Test            /TopLevelAlbums/xxx/Test/      9PYeLZDRTne
10      ab              /TopLevelAlbums/ab/            7Cse21+1SIag
11      abc             /TopLevelAlbums/Test/abc/      pNMvzDdyS%
16      efg             /TopLevelAlbums/Test/abc/efg/  a6R97tAxSBW

或者如果没有 TopLevelAlbums-main 文件夹就更好了：

modelId name            folderPath      uuid

16      efg             /Test/abc/efg/  a6R97tAxSBW

这是我到目前为止使用的脚本（缩短）：

SELECT 
RKAlbumVersion.versionId, 
RKVersion.filename, 

(SELECT RKFolder.folderpath from RKFolder, RKAlbum 
WHERE RKFolder.uuid = RKAlbum.folderUuid 
and RKAlbum.modelID = RKAlbumVersion.albumId)

FROM RKMaster, RKAlbumVersion, RKVersion 
WHERE RKVersion.modelId = RKAlbumVersion.versionId 
and RKVersion.masterUuid = RKMaster.uuid
-->
Output:
77  001.JPG  1/2/7/11/16/

replace with:
77  001.JPG  /Test/abc/efg/

答案1

你可以用 perl 来做到这一点：

#!/usr/bin/perl

# number of parent directories to drop
$drop = 2;

open( $input, '<', $ARGV[0] );

# drop header lines
$line = <$input>;
$line = <$input>;

# third line is first data, being root has no name, was requested that it wasn't used, which is good, because it makes life simplier, assuming the first dir is always 1
$line = <$input>;
#@databits = split(/\s+/, $line);
$hash{'1'} = '';

while ( $line = <$input> ) {
    @databits = split(/\s+/, $line);
    $hash["$databits[0]"] = $databits[1];
}

close( $input );

open( $input, '<', $ARGV[0] );
# now we print!
# headers
$line = <$input>;
print "$line";
$line = <$input>;
print "$line";

# drop first data line
$line = <$input>;

while ( $line = <$input> ) {
    @databits = split(/\s+/, $line);
    @replace = split(/\//, $databits[2]);

    $count = 0; # start at the start
    foreach (@replace) {
        $replace[$count] = $hash[$_];
        $count++;
    }

    for (my $i=0; $i < $drop; $i++) {
       shift(@replace);
    }

    $replaced = join('/', @replace);
    if ( $replaced ne '' ) {
        print "$databits[0] $databits[1] /$replaced/ $databits[3]\n";
    }
}

将输出作为文本文件输入，在下面的示例中名为“input”。您可以使用 column 命令制作漂亮的列。

$ ./replace.pl input | column -t
modelId  name  folderPath      uuid
7        Test  /Test/          kbY7RDHjRLS
8        xxx   /xxx/           bT5WAkPWQ1
9        Test  /xxx/Test/      9PYeLZDRTne
10       ab    /ab/            7Cse21+1SIag
11       abc   /Test/abc/      pNMvzDdyS%
16       efg   /Test/abc/efg/  a6R97tAxSBW

相关内容