This is a feature request for an additional table in SEDE consisting of at least the following site meta data:
- The site's primary URL.
- The site's SEDE URL (or its
- The date of the last SEDE update.
And ideally also including:
- The site's API site parameter.
- The site's short name.
And optionally including things such as:
- The site's archive dump filename.
- The site's status (beta, etc.)
- The site's type (meta, main)
- The site's creation date.
- Whatever else you can imagine.
Ideally this table would consist of a single row with a column for each of the above.
Primary motivation: I believe there are use cases for this information. Most notably, it is currently not possible to construct a cross-site query that also provides clickable URLs, as the magic columns
[Post Link] and such, as well as
query://, are all handled client side and so are always relative to the currently selected site. There is no information in the database that allows you to readily construct a URL for a specific site in a query (you could probably split, reverse, concatenate, then append ".com" to the components of
DB_NAME(), but... very clunky), and there is no information for you to construct an SEDE link (
query://) either (and not even a clunky workaround).
Similarly, even on single-site queries, there is no way to construct URLs in text-only results.
Secondary motivation: Additionally, there has been some recent discussion surrounding the data dumps with lack of documentation identified as a core problem, and including this type of info right in SEDE does serve as a form of self-documentation readily available to SEDE users. I recently compiled a spreadsheet of site meta info obtained from various sources that serves as an example of the kind of info that could be included in this table.
The purpose of the last SEDE update date is primarily to eliminate the need for close-but-not-quite hacks like
SELECT @UpdateDate = MAX(CreationDate) FROM ... which can throw off the precision of certain statistics, and is clunky.
Other example use cases for this are welcome in comments.
An alternative to a per-site table could be to expose some sanitized form of the
Sites table in
Data.StackExchange which is also presumably used to generate Sites.xml in the dump. If the archive dump database filename were added to this table it would also double as an implementation of this possible solution to the mentioned documentation issue. In this case the sites' SEDE database name would be in this table and could be matched with
DB_NAME() in a generic query.