VSTS Google Chrome Shortcuts

generate-vsts-shortcuts.sql

-- Google Chrome stores dates in microseconds since 1601-01-01 in the `keywords` sqlite table
-- the following line generates that value
-- select (strftime('%s', datetime('now')) + 11644473600) * 1000000

-- algo used to generate a uuid per row
-- select lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6)))

/*

Variables are not supported in sqlite.
The following 4 "variables" are used in the insert statements below.
Perform a "find & replace" in your favorite text editor on the following "variables"
Note: do not include the space between "@ and varname" when performing the find/replace.
Note: replace the @varname with 'something-here' including the single quotes.

@ reponame = 'My Repo Name' (8 occurrences)
@ shortcut = 'mr' (8 occurrences)
@ urlbase = 'https://mycompany.visualstudio.com/my-project-name/' (8 occurrences)
@ repobase = '_git/My.Repo.Name/' (2 occurrences)

*/


-- work items
insert into keywords (
 short_name,
 keyword,
 favicon_url,
 url,
 safe_for_autoreplace,
 originating_url,
 date_created,
 usage_count,
 input_encodings,
 suggest_url,
 prepopulate_id,
 created_by_policy,
 last_modified,
 sync_guid,
 alternate_urls,
 image_url,
 search_url_post_params,
 suggest_url_post_params,
 image_url_post_params,
 new_tab_url,
 last_visited)
values (
 @reponame || ' Work Items',
 @shortcut || 'wis',
 '',
 @urlbase || '_workitems/recentlyupdated/',
 0,
 '',
 (strftime('%s', datetime('now')) + 11644473600) * 1000000,
 0,
 '',
 '',
 0,
 0,
 (strftime('%s', datetime('now')) + 11644473600) * 1000000,
 (lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6)))),
 '[]',
 '',
 '',
 '',
 '',
 '',
 0
);


-- work item
insert into keywords (
 short_name,
 keyword,
 favicon_url,
 url,
 safe_for_autoreplace,
 originating_url,
 date_created,
 usage_count,
 input_encodings,
 suggest_url,
 prepopulate_id,
 created_by_policy,
 last_modified,
 sync_guid,
 alternate_urls,
 image_url,
 search_url_post_params,
 suggest_url_post_params,
 image_url_post_params,
 new_tab_url,
 last_visited)
values (
 @reponame || ' Work Item',
 @shortcut || 'wi',
 '',
 @urlbase || '_workitems/edit/{searchTerms}',
 0,
 '',
 (strftime('%s', datetime('now')) + 11644473600) * 1000000,
 0,
 '',
 '',
 0,
 0,
 (strftime('%s', datetime('now')) + 11644473600) * 1000000,
 (lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6)))),
 '[]',
 '',
 '',
 '',
 '',
 '',
 0
);


-- pull requests
insert into keywords (
 short_name,
 keyword,
 favicon_url,
 url,
 safe_for_autoreplace,
 originating_url,
 date_created,
 usage_count,
 input_encodings,
 suggest_url,
 prepopulate_id,
 created_by_policy,
 last_modified,
 sync_guid,
 alternate_urls,
 image_url,
 search_url_post_params,
 suggest_url_post_params,
 image_url_post_params,
 new_tab_url,
 last_visited)
values (
 @reponame || ' Pull Requests',
 @shortcut || 'prs',
 '',
 @urlbase || @repobase || 'pullrequests?_a=active',
 0,
 '',
 (strftime('%s', datetime('now')) + 11644473600) * 1000000,
 0,
 '',
 '',
 0,
 0,
 (strftime('%s', datetime('now')) + 11644473600) * 1000000,
 (lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6)))),
 '[]',
 '',
 '',
 '',
 '',
 '',
 0
);


-- pull request
insert into keywords (
 short_name,
 keyword,
 favicon_url,
 url,
 safe_for_autoreplace,
 originating_url,
 date_created,
 usage_count,
 input_encodings,
 suggest_url,
 prepopulate_id,
 created_by_policy,
 last_modified,
 sync_guid,
 alternate_urls,
 image_url,
 search_url_post_params,
 suggest_url_post_params,
 image_url_post_params,
 new_tab_url,
 last_visited)
values (
 @reponame || ' Pull Request',
 @shortcut || 'pr',
 '',
 @urlbase || @repobase || 'pullrequest/{searchTerms}',
 0,
 '',
 (strftime('%s', datetime('now')) + 11644473600) * 1000000,
 0,
 '',
 '',
 0,
 0,
 (strftime('%s', datetime('now')) + 11644473600) * 1000000,
 (lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6)))),
 '[]',
 '',
 '',
 '',
 '',
 '',
 0
);


-- builds
insert into keywords (
 short_name,
 keyword,
 favicon_url,
 url,
 safe_for_autoreplace,
 originating_url,
 date_created,
 usage_count,
 input_encodings,
 suggest_url,
 prepopulate_id,
 created_by_policy,
 last_modified,
 sync_guid,
 alternate_urls,
 image_url,
 search_url_post_params,
 suggest_url_post_params,
 image_url_post_params,
 new_tab_url,
 last_visited)
values (
 @reponame || ' Builds',
 @shortcut || 'bs',
 '',
 @urlbase || '_build?_a=queued',
 0,
 '',
 (strftime('%s', datetime('now')) + 11644473600) * 1000000,
 0,
 '',
 '',
 0,
 0,
 (strftime('%s', datetime('now')) + 11644473600) * 1000000,
 (lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6)))),
 '[]',
 '',
 '',
 '',
 '',
 '',
 0
);


-- build
insert into keywords (
 short_name,
 keyword,
 favicon_url,
 url,
 safe_for_autoreplace,
 originating_url,
 date_created,
 usage_count,
 input_encodings,
 suggest_url,
 prepopulate_id,
 created_by_policy,
 last_modified,
 sync_guid,
 alternate_urls,
 image_url,
 search_url_post_params,
 suggest_url_post_params,
 image_url_post_params,
 new_tab_url,
 last_visited)
values (
 @reponame || ' Build',
 @shortcut || 'b',
 '',
 @urlbase || '_build/index?buildId={searchTerms}',
 0,
 '',
 (strftime('%s', datetime('now')) + 11644473600) * 1000000,
 0,
 '',
 '',
 0,
 0,
 (strftime('%s', datetime('now')) + 11644473600) * 1000000,
 (lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6)))),
 '[]',
 '',
 '',
 '',
 '',
 '',
 0
);


-- releases
insert into keywords (
 short_name,
 keyword,
 favicon_url,
 url,
 safe_for_autoreplace,
 originating_url,
 date_created,
 usage_count,
 input_encodings,
 suggest_url,
 prepopulate_id,
 created_by_policy,
 last_modified,
 sync_guid,
 alternate_urls,
 image_url,
 search_url_post_params,
 suggest_url_post_params,
 image_url_post_params,
 new_tab_url,
 last_visited)
values (
 @reponame || ' Releases',
 @shortcut || 'rs',
 '',
 @urlbase || '_release',
 0,
 '',
 (strftime('%s', datetime('now')) + 11644473600) * 1000000,
 0,
 '',
 '',
 0,
 0,
 (strftime('%s', datetime('now')) + 11644473600) * 1000000,
 (lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6)))),
 '[]',
 '',
 '',
 '',
 '',
 '',
 0
);


-- release
insert into keywords (
 short_name,
 keyword,
 favicon_url,
 url,
 safe_for_autoreplace,
 originating_url,
 date_created,
 usage_count,
 input_encodings,
 suggest_url,
 prepopulate_id,
 created_by_policy,
 last_modified,
 sync_guid,
 alternate_urls,
 image_url,
 search_url_post_params,
 suggest_url_post_params,
 image_url_post_params,
 new_tab_url,
 last_visited)
values (
 @reponame || ' Releases',
 @shortcut || 'r',
 '',
 @urlbase || '_release?releaseId={searchTerms}',
 0,
 '',
 (strftime('%s', datetime('now')) + 11644473600) * 1000000,
 0,
 '',
 '',
 0,
 0,
 (strftime('%s', datetime('now')) + 11644473600) * 1000000,
 (lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6)))),
 '[]',
 '',
 '',
 '',
 '',
 '',
 0
);

blog post for more info: https://aaron-hoffman.blogspot.com/2018/04/programmatically-add-other-search-engines-to-google-chrome-for-vsts-shortcuts.html