@freixas Thank you for providing the scripts they may well prove useful. But the schema for the Mac database, regardless of how it was/is generated, has a number of differences to the Windows schema, not least the naming convention!?
So it will be possible to create the Mac equivalent scripts @platypus but the current ones won’t work.
PL8 has a slight difference in schema from PL7 (with PL8 on Windows), the order of fields in ‘Folders’ was changed in PL8 and an additional index or two was added to one structure or another (sorry I have forgotten exactly which ones).
In the meantime, I have been working with the DOPs where SQL certainly isn’t going, to help, except maybe to compare results between outputs from the scripts and outputs from the DOPs, but I have managed this so far in PureBasic
2025/06/03_09:29:03.518 @ line 1 Sidecar = {
2025/06/03_09:29:03.518 @ line 2 Date = "2025-06-01T15:55:50.6298766Z",
2025/06/03_09:29:03.518 @ line 3 Software = "DxO PhotoLab 8.6",
2025/06/03_09:29:03.518 @ line 4 Source = {
2025/06/03_09:29:03.518 @ line 5 CafId = "C61004c",
2025/06/03_09:29:03.518 @ line 6 Items = {
2025/06/03_09:29:03.518 @ line 7 {
2025/06/03_09:29:03.518 ---------------------------------------------------------------------------
2025/06/03_09:29:03.518 @ line 8 Albums = "", <===[
2025/06/03_09:29:03.529 @ line 9 CreationDate = "2025-05-29T09:41:02.0233595Z",
2025/06/03_09:29:03.529 @ line 13 Keywords = {
2025/06/03_09:29:03.529 Keyword = A
2025/06/03_09:29:03.529 Keyword = A|B
2025/06/03_09:29:03.529 Keyword = A|B|C
2025/06/03_09:29:03.529 Keyword = A|B|C|D
2025/06/03_09:29:03.529 @ line 39 Name = "P1102026_Neutral (nt V2).RW2",
2025/06/03_09:29:03.529 @ line 41 OutputItems = {
2025/06/03_09:29:03.529 @ line 45 Rating = 0,
2025/06/03_09:29:03.530 @ line 317 ColorLookupPath = "",
2025/06/03_09:29:03.531 @ line 504 Overrides = {
2025/06/03_09:29:03.531 @ line 616 Version = "19.5",
2025/06/03_09:29:03.531 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
2025/06/03_09:29:03.531 @ line 621 Uuid = "6A9404D6-3FD0-459E-B164-8DC08F223AD3", <---]
2025/06/03_09:29:03.531 {Album_count = 1 Uuid_count = 1}
2025/06/03_09:29:03.531 ---------------------------------------------------------------------------
2025/06/03_09:29:03.540 ---------------------------------------------------------------------------
2025/06/03_09:29:03.540 @ line 625 Albums = "", <===[
2025/06/03_09:29:03.540 @ line 626 CreationDate = "2025-06-01T15:52:54.4250729Z",
2025/06/03_09:29:03.540 @ line 630 Keywords = {
2025/06/03_09:29:03.540 Keyword = A
2025/06/03_09:29:03.540 Keyword = A|B
2025/06/03_09:29:03.540 Keyword = A|B|C
2025/06/03_09:29:03.540 Keyword = A|B|C|D
2025/06/03_09:29:03.540 @ line 656 Name = "P1102026_Neutral (nt V2).RW2",
2025/06/03_09:29:03.540 @ line 658 OutputItems = {
2025/06/03_09:29:03.540 @ line 662 Rating = 0,
2025/06/03_09:29:03.541 @ line 934 ColorLookupPath = "",
2025/06/03_09:29:03.542 @ line 1121 Overrides = {
2025/06/03_09:29:03.542 @ line 1131 Version = "19.5",
2025/06/03_09:29:03.542 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
2025/06/03_09:29:03.551 @ line 1136 Uuid = "62FD850F-8EA1-4AAB-8DA4-817E1E316BE8", <---]
2025/06/03_09:29:03.551 {Album_count = 2 Uuid_count = 2}
2025/06/03_09:29:03.551 ---------------------------------------------------------------------------
2025/06/03_09:29:03.551 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
2025/06/03_09:29:03.551 @ line 1141 Uuid = "A0944FE9-B476-4874-93EA-CDDF6AC1BDA0", <----
2025/06/03_09:29:03.551 {Album_count = 2 Uuid_count = 3}
2025/06/03_09:29:03.551 ---------------------------------------------------------------------------
2025/06/03_09:29:03.551 @ line 1144 Version = "19.0",
2025/06/03_09:29:03.551 ---------------------------------------------------------------------------
2025/06/03_09:29:03.551
2025/06/03_09:29:03.551 Creation Date list:-
2025/06/03_09:29:03.551 Album # Creation Date
2025/06/03_09:29:03.551 @ line 9 1 2025-05-29T09:41:02.0233595Z Rating = 0, Name = "P1102026_Neutral (nt V2).RW2",
2025/06/03_09:29:03.562 @ line 626 2 2025-06-01T15:52:54.4250729Z Rating = 0, Name = "P1102026_Neutral (nt V2).RW2",
2025/06/03_09:29:03.562
2025/06/03_09:29:03.562 Sorted Creation Date List:-
2025/06/03_09:29:03.562 Album # Creation Date
2025/06/03_09:29:03.562 @ line 9 1 1 2025-05-29T09:41:02.0233595Z Rating = 0, Name = "P1102026_Neutral (nt V2).RW2",
2025/06/03_09:29:03.562 @ line 626 2 2 2025-06-01T15:52:54.4250729Z Rating = 0, Name = "P1102026_Neutral (nt V2).RW2",
2025/06/03_09:29:03.562
2025/06/03_09:29:03.562 Modification Date list:-
2025/06/03_09:29:03.562 @ line 0 0 Rating = 0, Name = "P1102026_Neutral (nt V2).RW2",
2025/06/03_09:29:03.562 @ line 0 0 Rating = 0, Name = "P1102026_Neutral (nt V2).RW2",
2025/06/03_09:29:03.562
2025/06/03_09:29:03.562 Sorted Modification Date List:-
2025/06/03_09:29:03.562 @ line 0 0 Rating = 0, Name = "P1102026_Neutral (nt V2).RW2",
2025/06/03_09:29:03.562 @ line 0 0 Rating = 0, Name = "P1102026_Neutral (nt V2).RW2",
2025/06/03_09:29:03.573
2025/06/03_09:29:03.573 ==================================================================================
The 2 read passes are necessary only with DOPs from the Mac where it appears that the actual presentation of ‘Albums’ (copies) in the DOPs is not in order, which it is for Windows DOPs, i.e. with Windows the order of presentation in the DOP is always [M]aster, VC[1] etc. (according to my tests)
To achieve correct ordering with Mac DOPs the ‘Albums’ need to be sorted on ‘CreationDate’, hence the first pass to obtain the ‘CreationDate’ followed by a sort and that provides the data to mark or present the ‘Albums’ in the “correct” order.
The analysis program from which the screen grab was taken is just a vehicle for experimentation but the code from it is being added to programs intended for a more serious purpose, e.g. locating images that have not been assigned a keyword by the user in DxPL, which is impossible with the current ‘Search’ facility in DxPL @RAGING_FURY
The times shown are from the one of the two log files created (a User Log and a Debug Log) and it outputs to the Debug screen as well and it takes 33 milliseconds to process a single pass (all that is necessary for Windows),
So processing a directory of 1,000 images would take 33 seconds (on my 5900X but taking a single core)) and on Windows no second pass is necessary.
If the ‘Pick’/‘Reject’ flag is not included in the selection criteria only the first 50 lines need to be processed so it will take even less time!?
To do the same in SQL I would need code added to select a directory (‘Folders’), locate an image (‘Sources’ and thence to ‘Items’ ) and check for the ‘Items’ in ‘ItemsKeywords’, if absent (not found) then there is a candidate for an un-keyworded image.
My program will also be able to find any combination of keywords (including no ‘Keyword’ at all), and with ‘Rating’ and/or ‘ColorLabels’ and even the ‘Pick’/‘Reject’ flag thrown in for good measure, if I can be bothered to provide an appropriate user interface(?).
The selected (located) images can then be passed back to DxPL as an External Selection (stored as a form of ‘Projects’ entry in the database.) which can easily be converted to a real ‘Project’ by the user, if necessary.