How to search this repository to get a list of package names & short description? #90384

eabase · 2022-12-02T22:29:12Z

eabase
Dec 2, 2022

How can I search this repository to get a list of package names with a short description?
(And with the ID required to install it.)

Answered by Trenly

Dec 5, 2022

Unfortunately, since short descriptions can vary between versions, there isn't a way to get the short description for every package using the winget client. There are workarounds, however, such as parsing the manifest files using PowerShell or Bash, or using SQL to query against the index.db file contained within the source.msix file

View full answer

Trenly · 2022-12-05T04:05:40Z

Trenly
Dec 5, 2022

Unfortunately, since short descriptions can vary between versions, there isn't a way to get the short description for every package using the winget client. There are workarounds, however, such as parsing the manifest files using PowerShell or Bash, or using SQL to query against the index.db file contained within the source.msix file

4 replies

eabase Dec 5, 2022
Author

Ok. So is the index.db in this repo, or do I need to extract it separately from msix?
-- Oh, wait, are you saying I need to parse every single msix for the db content?
(I don't see any index.db here...)

What is the query needed for obtaining the package list from that DB?

Trenly Dec 5, 2022

The index.db file is inside the source.msix file which is updated after every run of the Publish pipeline. The source.msix file can be downloaded from https://cdn.winget.microsoft.com/cache/source.msix

I don’t know the query needed, you would have to build the SQL query yourself, but the code for the CLI can be used as a reference

eabase Dec 6, 2022
Author

Great! Thank you.

echo '.schema' | sqlite3 winget_src_msix_index.db
echo 'SELECT * FROM ids ORDER BY id ASC LIMIT 100;' | sqlite3 -header -tabs -column winget_src_msix_index.db

CREATE TABLE [metadata](
    [name] TEXT PRIMARY KEY NOT NULL,
    [value] TEXT NOT NULL);
CREATE TABLE [ids](rowid INTEGER PRIMARY KEY, [id] TEXT NOT NULL);
CREATE TABLE [names](rowid INTEGER PRIMARY KEY, [name] TEXT NOT NULL);
CREATE TABLE [monikers](rowid INTEGER PRIMARY KEY, [moniker] TEXT NOT NULL);
CREATE TABLE [versions](rowid INTEGER PRIMARY KEY, [version] TEXT NOT NULL);
CREATE TABLE [channels](rowid INTEGER PRIMARY KEY, [channel] TEXT NOT NULL);
CREATE TABLE [pathparts](rowid INTEGER PRIMARY KEY, [parent] INT64, [pathpart] TEXT NOT NULL);
CREATE TABLE [manifest](rowid INTEGER PRIMARY KEY, [id] INT64 NOT NULL, [name] INT64 NOT NULL, [moniker] INT64 NOT NULL, [version] INT64 NOT NULL, [channel] INT64 NOT NULL, [pathpart] INT64 NOT NULL, hash BLOB, arp_min_version INT64, arp_max_version INT64);
CREATE TABLE [tags](rowid INTEGER PRIMARY KEY, [tag] TEXT NOT NULL);
CREATE TABLE [tags_map]([manifest] INT64 NOT NULL, [tag] INT64 NOT NULL);
CREATE TABLE [commands](rowid INTEGER PRIMARY KEY, [command] TEXT NOT NULL);
CREATE TABLE [commands_map]([manifest] INT64 NOT NULL, [command] INT64 NOT NULL);
CREATE TABLE [pfns](rowid INTEGER PRIMARY KEY, [pfn] TEXT NOT NULL);
CREATE TABLE [pfns_map]([manifest] INT64 NOT NULL, [pfn] INT64 NOT NULL);
CREATE TABLE [productcodes](rowid INTEGER PRIMARY KEY, [productcode] TEXT NOT NULL);
CREATE TABLE [productcodes_map]([manifest] INT64 NOT NULL, [productcode] INT64 NOT NULL);
CREATE TABLE [norm_names](rowid INTEGER PRIMARY KEY, [norm_name] TEXT NOT NULL);
CREATE TABLE [norm_names_map]([manifest] INT64 NOT NULL, [norm_name] INT64 NOT NULL);
CREATE TABLE [norm_publishers](rowid INTEGER PRIMARY KEY, [norm_publisher] TEXT NOT NULL);
CREATE TABLE [norm_publishers_map]([manifest] INT64 NOT NULL, [norm_publisher] INT64 NOT NULL);
CREATE TABLE [upgradecodes](rowid INTEGER PRIMARY KEY, [upgradecode] TEXT NOT NULL);
CREATE TABLE [upgradecodes_map]([manifest] INT64 NOT NULL, [upgradecode] INT64 NOT NULL);
CREATE INDEX [manifest_id_index] ON [manifest]([id]);
CREATE INDEX [manifest_name_index] ON [manifest]([name]);
CREATE INDEX [manifest_moniker_index] ON [manifest]([moniker]);
CREATE UNIQUE INDEX [tags_map_pkindex] ON [tags_map]([tag], [manifest]);
CREATE UNIQUE INDEX [commands_map_pkindex] ON [commands_map]([command], [manifest]);
CREATE UNIQUE INDEX [pfns_pkindex] ON [pfns]([pfn]);
CREATE UNIQUE INDEX [pfns_map_pkindex] ON [pfns_map]([pfn], [manifest]);
CREATE INDEX [pfns_map_index] ON [pfns_map]([manifest]);
CREATE UNIQUE INDEX [productcodes_pkindex] ON [productcodes]([productcode]);
CREATE UNIQUE INDEX [productcodes_map_pkindex] ON [productcodes_map]([productcode], [manifest]);
CREATE INDEX [productcodes_map_index] ON [productcodes_map]([manifest]);
CREATE UNIQUE INDEX [norm_names_pkindex] ON [norm_names]([norm_name]);
CREATE UNIQUE INDEX [norm_names_map_pkindex] ON [norm_names_map]([norm_name], [manifest]);
CREATE INDEX [norm_names_map_index] ON [norm_names_map]([manifest]);
CREATE UNIQUE INDEX [norm_publishers_pkindex] ON [norm_publishers]([norm_publisher]);
CREATE UNIQUE INDEX [norm_publishers_map_pkindex] ON [norm_publishers_map]([norm_publisher], [manifest]);
CREATE INDEX [norm_publishers_map_index] ON [norm_publishers_map]([manifest]);
CREATE UNIQUE INDEX [upgradecodes_pkindex] ON [upgradecodes]([upgradecode]);
CREATE UNIQUE INDEX [upgradecodes_map_pkindex] ON [upgradecodes_map]([upgradecode], [manifest]);
CREATE INDEX [upgradecodes_map_index] ON [upgradecodes_map]([manifest]);

1985yandex Dec 11, 2022

Отлично! Спасибо.

echo '.schema' | sqlite3 winget_src_msix_index.db
echo 'ВЫБЕРИТЕ * ИЗ ПОРЯДКА идентификаторов ПО идентификатору ASC LIMIT 100;' | sqlite3 -header -tabs -column winget_src_msix_index.db

CREATE TABLE [metadata](
    [name] TEXT PRIMARY KEY NOT NULL,
    [value] TEXT NOT NULL);
CREATE TABLE [ids](rowid INTEGER PRIMARY KEY, [id] TEXT NOT NULL);
CREATE TABLE [names](rowid INTEGER PRIMARY KEY, [name] TEXT NOT NULL);
CREATE TABLE [monikers](rowid INTEGER PRIMARY KEY, [moniker] TEXT NOT NULL);
CREATE TABLE [versions](rowid INTEGER PRIMARY KEY, [version] TEXT NOT NULL);
CREATE TABLE [channels](rowid INTEGER PRIMARY KEY, [channel] TEXT NOT NULL);
CREATE TABLE [pathparts](rowid INTEGER PRIMARY KEY, [parent] INT64, [pathpart] TEXT NOT NULL);
CREATE TABLE [manifest](rowid INTEGER PRIMARY KEY, [id] INT64 NOT NULL, [name] INT64 NOT NULL, [moniker] INT64 NOT NULL, [version] INT64 NOT NULL, [channel] INT64 NOT NULL, [pathpart] INT64 NOT NULL, hash BLOB, arp_min_version INT64, arp_max_version INT64);
CREATE TABLE [tags](rowid INTEGER PRIMARY KEY, [tag] TEXT NOT NULL);
CREATE TABLE [tags_map]([manifest] INT64 NOT NULL, [tag] INT64 NOT NULL);
CREATE TABLE [commands](rowid INTEGER PRIMARY KEY, [command] TEXT NOT NULL);
CREATE TABLE [commands_map]([manifest] INT64 NOT NULL, [command] INT64 NOT NULL);
CREATE TABLE [pfns](rowid INTEGER PRIMARY KEY, [pfn] TEXT NOT NULL);
CREATE TABLE [pfns_map]([manifest] INT64 NOT NULL, [pfn] INT64 NOT NULL);
CREATE TABLE [productcodes](rowid INTEGER PRIMARY KEY, [productcode] TEXT NOT NULL);
CREATE TABLE [productcodes_map]([manifest] INT64 NOT NULL, [productcode] INT64 NOT NULL);
CREATE TABLE [norm_names](rowid INTEGER PRIMARY KEY, [norm_name] TEXT NOT NULL);
CREATE TABLE [norm_names_map]([manifest] INT64 NOT NULL, [norm_name] INT64 NOT NULL);
CREATE TABLE [norm_publishers](rowid INTEGER PRIMARY KEY, [norm_publisher] TEXT NOT NULL);
CREATE TABLE [norm_publishers_map]([manifest] INT64 NOT NULL, [norm_publisher] INT64 NOT NULL);
CREATE TABLE [upgradecodes](rowid INTEGER PRIMARY KEY, [upgradecode] TEXT NOT NULL);
CREATE TABLE [upgradecodes_map]([manifest] INT64 NOT NULL, [upgradecode] INT64 NOT NULL);
CREATE INDEX [manifest_id_index] ON [manifest]([id]);
CREATE INDEX [manifest_name_index] ON [manifest]([name]);
CREATE INDEX [manifest_moniker_index] ON [manifest]([moniker]);
CREATE UNIQUE INDEX [tags_map_pkindex] ON [tags_map]([tag], [manifest]);
CREATE UNIQUE INDEX [commands_map_pkindex] ON [commands_map]([command], [manifest]);
CREATE UNIQUE INDEX [pfns_pkindex] ON [pfns]([pfn]);
CREATE UNIQUE INDEX [pfns_map_pkindex] ON [pfns_map]([pfn], [manifest]);
CREATE INDEX [pfns_map_index] ON [pfns_map]([manifest]);
CREATE UNIQUE INDEX [productcodes_pkindex] ON [productcodes]([productcode]);
CREATE UNIQUE INDEX [productcodes_map_pkindex] ON [productcodes_map]([productcode], [manifest]);
CREATE INDEX [productcodes_map_index] ON [productcodes_map]([manifest]);
CREATE UNIQUE INDEX [norm_names_pkindex] ON [norm_names]([norm_name]);
CREATE UNIQUE INDEX [norm_names_map_pkindex] ON [norm_names_map]([norm_name], [manifest]);
CREATE INDEX [norm_names_map_index] ON [norm_names_map]([manifest]);
CREATE UNIQUE INDEX [norm_publishers_pkindex] ON [norm_publishers]([norm_publisher]);
CREATE UNIQUE INDEX [norm_publishers_map_pkindex] ON [norm_publishers_map]([norm_publisher], [manifest]);
CREATE INDEX [norm_publishers_map_index] ON [norm_publishers_map]([manifest]);
CREATE UNIQUE INDEX [upgradecodes_pkindex] ON [upgradecodes]([upgradecode]);
CREATE UNIQUE INDEX [upgradecodes_map_pkindex] ON [upgradecodes_map]([upgradecode], [manifest]);
CREATE INDEX [upgradecodes_map_index] ON [upgradecodes_map]([manifest]);

SpecterShell · 2022-12-12T13:42:32Z

SpecterShell
Dec 12, 2022

https://blog.jeffbolduan.com/Automating-Against-the-WinGet-Database-Using-PowerShell/

0 replies

eabase · 2022-12-12T19:13:31Z

eabase
Dec 12, 2022
Author

Thanks to @SpecterShell 's link.

This is how you do it:

Create an SQL file
Run the file on the DB from powershell command line

my_query.sql

--  Filename:  my_query.sql
--  Run with:  sqlite3 -init "my_query.sql" winget_src_msix_index.db
.headers on
-- [box, csv, tabs, table, column] etc...
.mode table

-- The "full" Query:
-- SELECT manifest.rowid, ids.id, names.name, monikers.moniker, versions.version, pathparts.pathpart, pathparts.parent
SELECT manifest.rowid, ids.id, names.name, versions.version
FROM manifest
LEFT JOIN ids ON ids.rowid=manifest.id
LEFT JOIN names ON names.rowid=manifest.name
LEFT JOIN monikers ON monikers.rowid=manifest.moniker
LEFT JOIN versions ON versions.rowid=manifest.version
LEFT JOIN pathparts ON pathparts.rowid=manifest.pathpart
-- lool for specifics:
WHERE names.name LIKE '%Zoom%'
-- [ASC, DESC]
ORDER BY ids.id ASC
LIMIT 20
;
.exit

Run with:

 $ sqlite3 -init "my_query.sql" winget_src_msix_index.db

-- Loading resources from my_query.sql
+-------+-----------+------+----------+
| rowid |    id     | name | version  |
+-------+-----------+------+----------+
| 27    | Zoom.Zoom | Zoom | 5.9.3931 |
| 28    | Zoom.Zoom | Zoom | 5.9.3799 |
| 29    | Zoom.Zoom | Zoom | 5.9.3169 |
| 30    | Zoom.Zoom | Zoom | 5.9.2581 |
| 31    | Zoom.Zoom | Zoom | 5.9.2481 |
| 32    | Zoom.Zoom | Zoom | 5.8.2058 |
| 33    | Zoom.Zoom | Zoom | 5.8.2048 |
| 34    | Zoom.Zoom | Zoom | 5.8.1736 |
| 35    | Zoom.Zoom | Zoom | 5.8.1581 |
| 36    | Zoom.Zoom | Zoom | 5.8.1435 |
| 37    | Zoom.Zoom | Zoom | 5.8.1324 |
| 38    | Zoom.Zoom | Zoom | 5.7.939  |
| 39    | Zoom.Zoom | Zoom | 5.7.804  |
| 40    | Zoom.Zoom | Zoom | 5.7.543  |
| 41    | Zoom.Zoom | Zoom | 5.7.522  |
| 42    | Zoom.Zoom | Zoom | 5.7.1247 |
| 43    | Zoom.Zoom | Zoom | 5.7.1105 |
| 44    | Zoom.Zoom | Zoom | 5.7.1055 |
| 45    | Zoom.Zoom | Zoom | 5.7.1020 |
| 46    | Zoom.Zoom | Zoom | 5.6.961  |
+-------+-----------+------+----------+
SQLite version 3.39.2 2022-07-21 15:24:47
Enter ".help" for usage hints.
sqlite>

0 replies

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

How to search this repository to get a list of package names & short description? #90384

{{title}}

{{editor}}'s edit

{{editor}}'s edit

Replies: 3 comments 4 replies

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

Select a reply

How to search this repository to get a list of package names & short description? #90384

eabase Dec 2, 2022

Replies: 3 comments · 4 replies

Trenly Dec 5, 2022

eabase Dec 5, 2022 Author

Trenly Dec 5, 2022

eabase Dec 6, 2022 Author

1985yandex Dec 11, 2022

SpecterShell Dec 12, 2022

eabase Dec 12, 2022 Author

eabase
Dec 2, 2022

Replies: 3 comments 4 replies

Trenly
Dec 5, 2022

eabase Dec 5, 2022
Author

eabase Dec 6, 2022
Author

SpecterShell
Dec 12, 2022

eabase
Dec 12, 2022
Author