8

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 TinyName).
  • 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 site:// and 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.

  • 2
    if Sites gets exposed it needs to be a view over the table as that table also contains a connectionstring override which might leak sensitive data when used. – rene Apr 2 '17 at 10:44
  • 1
    I think these datas could be collected as SEDE queries declaring a temporary table, and then manually filling them with INSERTs. They could be copy-pasted into any other query. It is a... well... not very beautiful solution. The SE probably has such tables, because some cross-network sites (for example, stackexchange.com) surely use them. The only what they should have to do, is making available also these tables for us. – peterh says reinstate Monica Apr 10 '17 at 1:01
  • 1
    Related feature request about public, but hard-to-find SEDE data. – peterh says reinstate Monica Apr 10 '17 at 1:05
  • 1
    @rene They can easily filter it out, a lot of other tables are already filtered, some of them non-trivially (for example, PostsWithDeleted contains all info for not deleted posts, but nearly nothing for the deleted ones). – peterh says reinstate Monica Apr 10 '17 at 1:38

You must log in to answer this question.

Browse other questions tagged .