USE [userInterface]
GO
/****** Object:  UserDefinedFunction [dbo].[fFileFromPath]    Script Date: 13/02/2026 14:04:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fFileFromPath](@filename varchar(128))
-------------------------------------------------------------------------------
--/H For a given filename path returns thst the filename part
--
--/T Will create 64 charcter file.
-------------------------------------------------------------------------------
RETURNS varchar(64)
AS BEGIN
        RETURN(REVERSE(SUBSTRING(REVERSE(@filename),0,CHARINDEX('/',REVERSE(@filename)))));
END


GO
/****** Object:  UserDefinedFunction [dbo].[fFileURL]    Script Date: 13/02/2026 14:04:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fFileURL](@filename varchar(128))
-------------------------------------------------------------------------------
--/H For a given filename returns the URL where it can be downloaded
--
--/T Will create a 192 charcter URL.
-------------------------------------------------------------------------------
RETURNS varchar(192)
AS BEGIN
        RETURN('http://wsa.roe.ac.uk/cgi-bin/fits_download.cgi?file='+replace(@filename,'+','%2B'));
END


GO
/****** Object:  UserDefinedFunction [dbo].[fWgetCmd]    Script Date: 13/02/2026 14:04:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fWgetCmd](@filename varchar(128))
-------------------------------------------------------------------------------
--/H For a given filename path returns thst the filename part
--
--/T Will create 64 charcter file.
-------------------------------------------------------------------------------
RETURNS varchar(256)
AS BEGIN
        RETURN('wget '''+dbo.fFileURL(@filename)+''' -O '+dbo.fFileFromPath(@filename));
END


GO
/****** Object:  UserDefinedFunction [dbo].[StripComments]    Script Date: 13/02/2026 14:04:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[StripComments](@StringToStrip nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
    -- This bit strips out block comments.  We need to strip them out BEFORE
    -- single line comments (like this one), because you could theoretically have
    -- a block comment like this:
    /* My Comment
    -- Is malformed */

    -- Variables to hold the first and last character's positions in the "next" block 
    -- comment in the string
    DECLARE @CodeBlockStart int, @CodeBlockEnd int
    SET @CodeBlockStart = PATINDEX('%/*%', @StringToStrip)

    -- Loop as long as we still have comments to exorcise ;)
    WHILE @CodeBlockStart > 0
    BEGIN
        -- Grab the last character in the code block by searching for the first incidence
        -- of */ (close comment) in the string.
        SET @CodeBlockEnd = PATINDEX('%*/%', @StringToStrip)
    
        -- "Cut" out the comment by concatenating everything the the "left" and "right"
        -- of the comment
        SET @StringToStrip = LEFT(@StringToStrip, @CodeBlockStart - 1)
                                + RIGHT(@StringToStrip, LEN(@StringToStrip) - (@CodeBlockEnd + 0))
    
        -- Fetch the first character's position in the next comment block, if there is one.
        SET @CodeBlockStart = PATINDEX('%/*%', @StringToStrip)
    END

    -- Once code blocks are out, we can remove any lines commented by double dashes (like this one)
    -- Variables to hold the first and last character's position in the "next" code block.
    DECLARE @DoubleDashStart int, @DoubleDashLineEnd int

    -- Grab the first double-dash (if there is one)
    
   
    set @StringToStrip=replace(@StringToStrip,'---','--')
    
    
    
    SET @DoubleDashStart = PATINDEX('%--%', @StringToStrip)
    WHILE @DoubleDashStart > 0
    BEGIN
        -- Search for the first "new line" AFTER the first "double dash"
        -- We can use CHAR(13) and CHAR(10) to find the new line.
        -- Since PATINDEX doesn't have a "start" character, and we need to find
        -- the first new line AFTER the double dash, we will search all characters
        -- after the double dash for the new line.
        SET @DoubleDashLineEnd = PATINDEX('%' + CHAR(13) + CHAR(10) + '%',
                                        RIGHT(@StringToStrip, LEN(@StringToStrip) - (@DoubleDashStart)))
                                 + @DoubleDashStart
                                        
        -- "Cut" out the comment, as was done with the block comments.
        SET @StringToStrip =    LEFT(@StringToStrip, @DoubleDashStart - 1) +
                                RIGHT(@StringToStrip, LEN(@StringToStrip) - @DoubleDashLineEnd)
    
        -- Check for the next incidence of a double dash, if there is one.
        SET @DoubleDashStart = PATINDEX('%--%', @StringToStrip)
    END

    -- Return the uncommented string    
    RETURN @StringToStrip
END

GO
/****** Object:  Table [dbo].[webQueries]    Script Date: 13/02/2026 14:04:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[webQueries](
	[query] [varchar](4096) NOT NULL,
	[cpu_sec] [float] NULL,
	[elapsed_time] [float] NOT NULL,
	[physical_IO] [float] NULL,
	[row_count] [int] NOT NULL,
	[time] [datetime] NULL,
	[username] [varchar](64) NULL,
	[ipaddress] [varchar](32) NULL,
	[dbname] [varchar](64) NULL,
	[row_size] [int] NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  StoredProcedure [dbo].[spAddToQ]    Script Date: 13/02/2026 14:04:06 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spAddToQ] @username varchar(64),@dbname varchar(32),@email varchar(64), @queryStr varchar(7500),
@fmt varchar(6),@serverName varchar(16),@servletID int,@timeout int,@rowsHTML int, @rowsTotal int, @IPAddress varchar(20)
AS	BEGIN
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
--BEGIN TRANSACTION
--declare @idnum int
--	set @idnum =( select max(qID) from queue)
--if (@idnum is null)
--begin
--set @idnum=0
--end
--set @idnum= @idnum+1

declare @submitted datetime
set @submitted=  CURRENT_TIMESTAMP

INSERT INTO queue (qstatus,username,dbname,email,queryStr,fmt,servername,submitted,updated,servletID,
timeout,rowsHTML,rowsTotal,IPAddress)
VALUES (0,@username,@dbname,@email,@queryStr,@fmt,@servername,
@submitted,@submitted,@servletID,@timeout,@rowsHTML,@rowsTotal,@IPAddress)
select top 1 qid from queue where username=@username and dbname=@dbname and email=@email and queryStr=@queryStr and fmt=@fmt and serverName=@servername and submitted=@submitted and servletID=@servletID and timeout=@timeout and IPAddress=@IPAddress and rowsHTML=@rowsHTML
--commit TRANSACTION
        RETURN;
        END


GO
/****** Object:  StoredProcedure [dbo].[spGetFromQ]    Script Date: 13/02/2026 14:04:06 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spGetFromQ]
AS	BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

declare @qId int
declare @queryStr varchar(7500)

set @qID = (select top 1 qID from queue with (tablockx)
where  qstatus=0
and username not in (select username from queue where qstatus=1 and datediff(second,submitted,current_timestamp) < 1)
order by qId asc)
--waitfor delay '00:00:00.5'
update queue
set qstatus=1,updated=current_timestamp where qid=@qid

select * from queue where qId=@qid

commit TRANSACTION
        RETURN;
        END



GO
/****** Object:  StoredProcedure [dbo].[spListWebqueries]    Script Date: 13/02/2026 14:04:06 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[spListWebqueries]	@whereClause varchar(1024), @passwd varchar(7),@toprows int,@docount tinyint
as BEGIN
DECLARE @cmd varchar(1100)

if (@passwd='lockman')
begin

if (@toprows > 0)
begin
set @cmd='select top '+cast(@toprows as varchar(12))+' * from #webqueries '+@whereClause;
end
else
begin
if (@docount =0)
begin
set @cmd='select * from #webqueries '+@whereClause;
end
else
begin
set @cmd='select count(*) from #webqueries '+@whereClause;
end
end

create  TABLE #webQueries (
        query varchar (4096),
              cpu_sec float,
        elapsed_time float ,
        physical_IO float,
        row_count int ,
        time datetime ,
           username varchar (64) ,
           ipaddress varchar (32)   ,
           dbname varchar (64), row_size int )

insert into #webQueries select * from webqueries;

exec (@cmd);
end
else
begin
select 'Denied';
end

        END;


GO
/****** Object:  StoredProcedure [dbo].[spPurgeQ]    Script Date: 13/02/2026 14:04:06 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spPurgeQ]
AS	BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
delete queue
where qID > (
    select min(qID)
    from queue as q
    where q.queryStr = queue.queryStr and
    q.username=queue.username and
    q.dbname=queue.dbname and
    q.fmt=queue.fmt and
    q.timeout=queue.timeout and
    q.servername=queue.servername and
    q.rowsTotal=queue.rowsTotal and
    q.qstatus=queue.qstatus and q.qstatus=0
)
commit TRANSACTION
        RETURN;
        END;



GO
/****** Object:  StoredProcedure [dbo].[spUpdateQStatus]    Script Date: 13/02/2026 14:04:06 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[spUpdateQStatus] @qid int, @qstatus smallint
AS	BEGIN

update queue
set qstatus=@qstatus,updated=current_timestamp where qid=@qid

        RETURN;
        END;



GO
/****** Object:  StoredProcedure [dbo].[spUpdateQStatusAndURL]    Script Date: 13/02/2026 14:04:06 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[spUpdateQStatusAndURL] @qid int, @qstatus smallint,@resultsURL varchar(128)
AS	BEGIN

update queue
set qstatus=@qstatus,updated=current_timestamp,resultsURL=@resultsURL where qid=@qid

        RETURN;
        END;



GO
/****** Object:  StoredProcedure [dbo].[spUpdateWebqueries]    Script Date: 13/02/2026 14:04:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spUpdateWebqueries]	@sqlquery  varchar(4096),
                                @row_count   int  ,
                                 @elapsed float,
                                @user        varchar(64),
                                @ipaddress   varchar(32),
                                @dbname varchar(64),
                                @row_size   int

AS	BEGIN
        declare @cpu float
             declare @physical_io float

        select         @cpu = sum(cpu),
                       @physical_io = sum(physical_io)
        from master.dbo.sysprocesses where spid=@@spid;

          begin
           insert dbo.webQueries
              values(@sqlquery, @cpu/1000.0, @elapsed/1000.0, @physical_io, @row_count,CURRENT_TIMESTAMP,@user,@ipaddress,@dbname,@row_size);
           END
        RETURN;
        END;



GO
