fritz
11/18/2020, 2:40 PMmagic
table or the signature
table to discern architecture, both of these operations are relatively expensive (signature being much worse)
Example of magic:
WITH
-- retrieve paths of app binaries and get magic data
app_paths AS (
SELECT
REGEX_SPLIT(path, '/Contents/', 0) AS path,
data AS arch
FROM magic AS m
WHERE m.path IN (
SELECT file.path
FROM file JOIN apps
WHERE file.path LIKE apps.path || "/Contents/MacOS/%"
AND file.type = 'regular')
-- omit files that are not binaries
AND mime_encoding = 'binary'
-- omit symlinks
AND mime_type != 'inode/symlink'),
-- condense down result set with group by
reduce_set AS (
SELECT path, arch
FROM app_paths
GROUP BY path, arch)
-- rejoin on apps
SELECT name, arch FROM apps a CROSS JOIN reduce_set rs on a.path = rs.path;
+--------------------------------------------+---------------------------------+
| name | arch |
+--------------------------------------------+---------------------------------+
| 1Password 7.app | Mach-O 64-bit executable x86_64 |
| A Better Finder Rename <http://10.app|10.app> | Mach-O 64-bit executable x86_64 |
| Acrobat <http://Distiller.app|Distiller.app> | Mach-O 64-bit executable x86_64 |
| Adobe <http://Acrobat.app|Acrobat.app> | Mach-O 64-bit executable x86_64 |
| Adobe After Effects <http://2020.app|2020.app> | Mach-O 64-bit executable x86_64 |
| Adobe Audition <http://2020.app|2020.app> | Mach-O 64-bit executable x86_64 |
| Adobe DNG <http://Converter.app|Converter.app> | Mach-O 64-bit executable x86_64 |
| Adobe <http://Illustrator.app|Illustrator.app> | Mach-O 64-bit executable x86_64 |
| Analyze <http://Documents.app|Documents.app> | Mach-O 64-bit executable x86_64 |
| Make <http://Calendar.app|Calendar.app> | Mach-O 64-bit executable x86_64 |
| Contact <http://Sheets.app|Sheets.app> | Mach-O 64-bit executable x86_64 |
| Web <http://Gallery.app|Gallery.app> | Mach-O 64-bit executable x86_64 |
| Adobe InDesign <http://2021.app|2021.app> | Mach-O 64-bit executable x86_64 |
| Adobe <http://Lightroom.app|Lightroom.app> | Mach-O 64-bit executable x86_64 |
| Adobe Media Encoder <http://2020.app|2020.app> | Mach-O 64-bit executable x86_64 |
| Adobe Photoshop <http://2020.app|2020.app> | Mach-O 64-bit executable x86_64 |
| Adobe Premiere Pro <http://2020.app|2020.app> | Mach-O 64-bit executable x86_64 |
| Alfred 3.app | Mach-O 64-bit executable x86_64 |
| <http://Atom.app|Atom.app> | Mach-O 64-bit executable x86_64 |
| Backup and <http://Sync.app|Sync.app> | Mach-O 64-bit executable x86_64 |
| Capture One <http://10.app|10.app> | Mach-O 64-bit executable x86_64 |
| Capture One <http://12.app|12.app> | Mach-O 64-bit executable x86_64 |
| Capture One <http://20.app|20.app> | Mach-O 64-bit executable x86_64 |
| <http://Charles.app|Charles.app> | Mach-O 64-bit executable x86_64 |
| CorelDRAW Graphics Suite <http://2020.app|2020.app> | Mach-O 64-bit executable x86_64 |
| Corel Font Manager <http://2020.app|2020.app> | Mach-O 64-bit executable x86_64 |
| Corel PHOTO-PAINT <http://2020.app|2020.app> | Mach-O 64-bit executable x86_64 |
| CorelDRAW <http://2020.app|2020.app> | Mach-O 64-bit executable x86_64 |
| <http://CraftManager.app|CraftManager.app> | Mach-O 64-bit executable x86_64 |
bundle id: Sketch (0xcad4)
class: kLSBundleClassApplication (0x2)
container: / (0x4)
mount state: mounted
Mach-O UUIDs: E20FFBBF-B5CD-3684-8582-4E314B2D09CC
sequenceNum: 51924
path: /Applications/Sketch.app (0xeec8)
directory: /Applications
name: Sketch
displayName: Sketch
localizedName: "LSDefaultLocalizedValue" = "Sketch"
localizedShortName: "LSDefaultLocalizedValue" = "Sketch"
teamID: WUGMZZ5K46
category: public.app-category.graphics-design (0x2b78c)
identifier: com.bohemiancoding.sketch3
version: 83088.0 ({length = 32, bytes = 0x90440100 00000000 00000000 00000000 ... 00000000 00000000 })
versionString: 83088
displayVersion: 57.1
mod date: 2019-08-15 13:17 (POSIX 1565889429)
reg date: 2020-11-06 09:43 (POSIX 1604673830)
type code: 'APPL' (4150504c)
creator code: '????' (3f3f3f3f)
bundle flags: has-display-name (0000000000000002)
plist flags: has-custom-bindings (0000000000010000)
icon flags: relative-icon-path (0000000000000001)
arch flags: x86_64 (0000000000000008)
item flags: package application container native-app scriptable extension-hidden (000000000010088e)
Magnified: capable can-change (0000000000000005)
App Nap: capable (0000000000000001)
eGPU: capable can-change (0000000000000005)
platform: native
icons: Contents/Resources/app.icns
executable: Contents/MacOS/Sketch
inode: 247849832
exec inode: 247849849
min version: 10.13.4 ({length = 32, bytes = 0x0a000000 00000000 0d000000 00000000 ... 00000000 00000000 })
execSDK ver: 10.14 ({length = 32, bytes = 0x0a000000 00000000 0e000000 00000000 ... 00000000 00000000 })
mach min ver: 10.13.4 ({length = 32, bytes = 0x0a000000 00000000 0d000000 00000000 ... 00000000 00000000 })
plistCommon: 23 values (115416 (0x1c2d8))
{
CFBuildNumber = 109;
CFBundleDevelopmentRegion = English;
CFBundleDisplayName = Sketch;
CFBundleExecutable = Sketch;
CFBundleIconFile = "app.icns";
CFBundleIdentifier = "com.bohemiancoding.sketch3";
CFBundleInfoDictionaryVersion = "6.0";
CFBundleName = Sketch;
CFBundlePackageType = APPL;
CFBundleShortVersionString = "57.1";
CFBundleSignature = "????";
CFBundleSupportedPlatforms = (
MacOSX
);
CFBundleVersion = 83088;
NSAppTransportSecurity = {
NSAllowsArbitraryLoads = 1;
NSAllowsArbitraryLoadsInWebContent = 1;
NSAllowsLocalNetworking = 1;
};
NSAppleEventsUsageDescription = "This allows Sketch to communicate with the app.";
NSAppleScriptEnabled = 1;
NSHumanReadableCopyright = "\U00a9 2009-2019 Sketch B.V.";
NSMainNibFile = MainMenu;
NSPhotoLibraryUsageDescription = "You\U2019ll be able to browse and import your photos into Sketch.";
NSPrincipalClass = NSApplication;
NSSupportsAutomaticGraphicsSwitching = 1;
NSUbiquitousContainers = {
"WUGMZZ5K46.com.bohemiancoding.sketch" = {
NSUbiquitousContainerIsDocumentScopePublic = 1;
NSUbiquitousContainerName = Sketch;
NSUbiquitousContainerSupportedFolderLevels = Any;
};
};
NSUserActivityTypes = (
"com.bohemiancoding.sketch.play.handoff"
);
}
library: Contents/Library/
library items: QuickLook/SketchQuicklook.qlgenerator/
activityTypes: WUGMZZ5K46:com.bohemiancoding.sketch.play.handoff, NOTIFICATION#WUGMZZ5K46:com.bohemiancoding.sketch3, ua-9d1f73d2782cb4, pv-f23e896627771a
claimed UTIs: com.bohemiancoding.sketch.plugin.bundle, com.adobe.pdf, public.image, public.webp, com.bohemiancoding.sketch.drawing.single, com.bohemiancoding.sketch.plugin, com.bohemiancoding.sketch.drawing, com.bohemiancoding.sketch.clouddrawing.single, com.adobe.encapsulated-postscript
claimed schemes: sketch-nonappstore:, sketch-v57.1:, sketch:
entitlements: 7 values (115420 (0x1c2dc))
{
"com.apple.security.app-sandbox" = 0;
"com.apple.security.application-groups" = (
"WUGMZZ5K46."
);
"com.apple.security.automation.apple-events" = 1;
"com.apple.security.cs.allow-jit" = 1;
"com.apple.security.cs.allow-unsigned-executable-memory" = 1;
"com.apple.security.cs.disable-library-validation" = 1;
"com.apple.security.personal-information.photos-library" = 1;
}
apps
table would be relying on data from lsregister
instead which contains an arch flag:Nakul
11/19/2020, 3:35 AM