Sqlite: replace a string modelID1/modelID2/modelID3 with name1/name2/name3/

Question

My goal is to read the macOS Photos.app database to find out the paths of the included photos. The album and the parent folder whose name is in the RKFolder table can be determined for each photo. This parent folder can be located in other folders. The resulting folder path can also be read out... but in the following form:

modelId 1/modelID 2/modelId 3/modelID 4

And here I can't go any further, because I don't have enough knowledge to replace the string with the true folder names. For possible solutions it should be noted that I work with a copy of the database and therefore the folder path could also be written directly into the RKFolder table.

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

results in (excerpt)

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

replace like this:

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

or even better without the TopLevelAlbums-main folder:

modelId name            folderPath      uuid

16      efg             /Test/abc/efg/  a6R97tAxSBW

Here the script that i use so far (shortened):

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/

post the full expected output (not like this and even better) — RomanPerekhrest
– RomanPerekhrest, Commented May 6, 2018 at 13:41

Amos · Accepted Answer · 2019-08-14 20:42:10Z

You could do it with 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";
    }
}

Feed your output in as a text file, in the example below named "input". You can make nice columns with the column command.

$ ./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

It works even better as desired because you removed the top level folder from the path :) ! Thank you very much! — spazek
– spazek, Commented Aug 18, 2019 at 20:18
Glad to help. And if you find it has an extra level of dir you want to remove you can adjust the $drop variable to be greater and it will drop more of them :) — Amos
– Amos, Commented Aug 18, 2019 at 20:57

Stack Exchange Network

Sqlite: replace a string modelID1/modelID2/modelID3 with name1/name2/name3/

1 Answer 1

You must log in to answer this question.

Hot Network Questions

Sqlite: replace a string modelID1/modelID2/modelID3 with name1/name2/name3/

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions