Can I access the current site's domain name from the Data Explorer?

Question

I am writing a query where I would like to link directly to the timelines of the posts found by the query. This can be done via string concatenation as

SELECT
'http://meta.stackexchange.com/posts/'+CAST(Id AS varchar)+'/timeline'
FROM Posts
WHERE ...

and the table can be made much nicer by giving link text as

SELECT
'http://meta.stackexchange.com/posts/'+CAST(Id AS varchar)+'/timeline|timeline' AS 'Timeline link'
FROM Posts
WHERE ...

I would like, though, to be able to do this query in a site-agnostic fashion - that is, for the Data Explorer to give a timeline link for the site whose database is currently being queried.

Can this be done? If so, what's the quickest, cleanest way to do it? Is there a specific SEDE way, or must it go through SQL constructs?

Answer 1

You can use the autolinking feature with the site:// scheme, e.g.

SELECT
'site://posts/' + CAST(Id AS varchar) + '/timeline'
FROM Posts
WHERE ...

This will automatically be expanded to the current site's domain on render.

Answer 2

You can also use a recursive stored procedure to reverse and build-up the site url.

The following query shows that:

-- make URL for site from database name

-- start create url from dbname
-- entry
IF OBJECT_ID ( '#siteurl', 'P' ) IS NOT NULL 
    DROP PROCEDURE #siteurl;
GO
create procedure #siteurl
   @dbname nvarchar(250),
   @res nvarchar(250) OUT
   as
   begin
      -- we have three sites that have borked DNS entries
      set @dbname = (case @dbname
      when 'StackExchange.Audio' then 'StackExchange.Avp'
      when 'StackExchange.Audio.Meta' then 'StackExchange.Avp.Meta'
      when 'StackExchange.Mathoverflow.Meta' then 'net.Mathoverflow.meta'
      else @dbname
      end)
      -- and one of those doesn't want to end with .com
      if @dbname <> 'net.Mathoverflow.meta' 
         set @dbname = 'com.' + @dbname
      exec #recursesiteurl @dbname,  @res OUTPUT
   end
GO
-- create url from dbname
-- recursively called
IF OBJECT_ID ( '#recursesiteurl', 'P' ) IS NOT NULL 
    DROP PROCEDURE #recursesiteurl;
GO
create procedure #recursesiteurl
   @dbname nvarchar(250),
   @res nvarchar(250) OUT
   as
   begin
      declare @pos integer
      declare @rec nvarchar(250)
       
      set @res = @dbname
      set @pos = CHARINDEX('.', @dbname)
      if (@pos > 0 ) 
      begin
         set @rec = substring(@dbname, @pos+1, len(@dbname))
         exec #recursesiteurl @rec,  @rec output
         set @res = @rec 
                  + '.' 
                  + substring(@dbname, 0, @pos) 
      end
   end;
GO

declare @site  nvarchar(250)
declare @db nvarchar(128) = db_name()

-- call our entry point with a db name in, and a site out.
exec #siteurl @db, @site output

-- by getting rid of the TLD you get the sitename
select replace(
       replace(
       replace(@site,
         '.StackExchange.com',''),
         '.com',''),
         '.net','') as [site]
     , 'http://' 
     + @site as [url]

I use it primarily when I have to create links to multiple sites in case of multi-database queries like this one

After some more experimenting and a question on Stack Overflow I think I can replace the stored procedure with this:

 SELECT 'http://' 
 + case PARSENAME(db_name(),1)
   when 'audio' then 'avp'
   else PARSENAME(db_name(),1)
   end
 + coalesce('.' 
          + case PARSENAME(db_name(),2)
            when 'audio' then 'avp'
            else PARSENAME(db_name(),2)
            end , '')
 + IIF(patindex('%.MathOverflow%',db_name()) > 0
   , ''
   , coalesce('.' + PARSENAME(db_name(),3), '')
   )
 + IIF(patindex('%.MathOverflow%',db_name()) > 0
   , '.net'
   , '.com'
   )

Answer 3

The current database name is available within the SQL interface as

SELECT DB_NAME() AS [Current Database]

with the set of possible database names available from

SELECT name
FROM Sys.Databases
WHERE database_id >5
ORDER BY name

However, these database names typically return in the form StackExchange.Math.Meta, which is the domain name, minus the .com, in reverse.

One way to reverse them to create a proper URL is to use

DECLARE @sitexml XML, @site VARCHAR(100)
SET @sitexml = CAST(('<X>' + REPLACE((SELECT DB_NAME() AS [Current Database]), '.', '</X><X>') + '</X>') AS XML)
SET @site=LOWER(STUFF((SELECT '.' + C.value('.', 'VARCHAR(50)')
                FROM @sitexml.nodes('X') AS X(C)
               ORDER BY ROW_NUMBER() OVER (ORDER BY (SELECT 0)) DESC
               FOR XML PATH('')), 1, 1, ''))+'.com'

SELECT
'http://'+@site+'/posts/'+CAST(Id AS varchar)+'/timeline|timeline' AS 'Timeline link'
FROM Posts
WHERE ...

I remain interested in cleaner ways to do this, though.

---

In addition to that, there's another place where you might want the current database name, and that's if you're linking to other queries within the data explorer. This can be done with the following adaptation of the above:

DECLARE @siteinit VARCHAR(100), @site VARCHAR(100)
SET @siteinit=REPLACE(LOWER( (SELECT DB_NAME() AS [Current Database]) ),'StackExchange.','')
SET @site = REPLACE(REPLACE(REPLACE( --REPLACE(
CASE
  WHEN @siteinit NOT LIKE '%.Meta' THEN @siteinit
  ELSE 'meta.'+REPLACE(@siteinit,'.Meta','')
  END
  ,'audio','avp') ,'stackoverflow.br','stackoverflow.pt') ,'stackoverflow.','')
  --,'meta.','')+'me' -- Some meta sites require this

SELECT TOP 1
@site AS 'handle',
'https://data.stackexchange.com/'+@site AS 'SEDE home',
'https://data.stackexchange.com/'+@site+'/query/516017' AS 'This query'
FROM PostTypes

available at The current site's SEDE database handle for query URLs, or for an overall view at All site handles for SEDE query URLs.

Note in particular the inconsistent URL usage for meta sites: it's academia and meta.academia but it can sometimes be arduino and arduinome.

Answer 4

The cleanest way I found to do this in cross-site queries (otherwise just use site://) is:

DECLARE @URL NVARCHAR(MAX)
SELECT @URL = CONCAT(value, '.', @URL) FROM STRING_SPLIT(DB_NAME(), '.')
SET @URL = CONCAT(LOWER(@URL), 'com')

After this, @URL will contain the site's host name (yeah, not a full URL, but you can add http:// or whatever you need to in that final SET.

This just reverses the database name, makes it lowercase and slaps a .com on the end. So for example, StackExchange.Garage.Meta becomes meta.garage.stackexchange.com.

Although it does suffer from the three special cases that rene points out, but you can just add them trivially after the fact (e.g. case @url when and change it), e.g.:

DECLARE @URL NVARCHAR(MAX)
SELECT @URL = CONCAT(value, '.', @URL) FROM STRING_SPLIT(DB_NAME(), '.')
SET @URL = CONCAT(LOWER(@URL), 'com');

SELECT @URL = CASE @URL
  WHEN 'meta.mathoverflow.stackexchange.com' THEN 'meta.mathoverflow.stackexchange.net'
  WHEN 'audio.stackexchange.com' THEN 'avp.stackexchange.com'
  WHEN 'meta.audio.stackexchange.com' THEN 'meta.avp.stackexchange.com'
  ELSE @URL
END

You can wrap it in a stored procedure if you'd like (you'd definitely want to do that for convenience if generating cross-site queries).