Sqlite：將字元串modelID1/modelID2/modelID3替換為name1/name2/name3/

我的目標是閱讀 macOS Photos.app 數據庫以找出所包含照片的路徑。可以為每張照片確定相冊和名稱在*RKFolder表中的父文件夾。*此父文件夾可以位於其他文件夾中。也可以讀出生成的文件夾路徑……但格式如下：

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/

你可以用 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

引用自：https://unix.stackexchange.com/questions/442099