/*********************************************************************/
/* $Id: dfslogCreate.sql,v 1.1 2004/09/06 14:33:19 cguirao Exp $ 
/*********************************************************************/
/* Script: dfslogCreate.sql                                          */
/* Author: Nick Kornweibel                                           */
/*                                                                   */
/* Purpose:                                                          */
/* This script creates the database, user and tables for the DFSLog  */
/* software system.                                                  */
/*                                                                   */
/* It is to be run by the "sa" user as part of the installation      */
/* of DFSLog software.                                               */
/*                                                                   */
/* It will drop/delete an existing dfslog database and user prior to */
/* creating the new database and tables. Therefore:                  */
/*                                                                   */
/* DO NOT USE THIS SCRIPT IF THE EXISTING dfslog DATABASE IS TO BE   */
/* KEPT.                                                             */
/*                                                                   */
/* The following database and login accounts must be setup prior to  */
/* running this script:                                              */
/* These are created in the following manner by the 'sa' user:       */
/*     sp_addlogin dfslog_dbo,'pdfslog_dbo'                          */
/*     sp_addlogin dfslogger,'pdfslogger'                            */
/*                                                                   */
/* This script assumes these login accounts exist prior to execution */
/*                                                                   */
/* Revision history:  see end of file                                */
/*********************************************************************/

/***************************************************************/
/* create the dfslog database                                  */
/*                                                             */
/*    create database dfslog on data=100 log on logdev=10      */
/*    go                                                       */
/*    sp_dboption dfslog, trunc_log_on_chkpt, true             */
/*    go                                                       */
/*    sp_addlogin dfslog_dbo,'pdfslog_dbo',dfslog              */
/*    go                                                       */
/*    sp_addlogin dfslogger,'pdfslogger',dfslog                */
/*    go                                                       */
/*    use dfslog                                               */
/*    go                                                       */
/*    sp_changedbowner dfslog_dbo                              */
/*    go                                                       */
/*                                                             */
/*                                                             */
/***************************************************************/




/***************************************************************/
print "create the recent_message table"
/* The archive_status field defaults to 1, indicating that new */
/* rows are not yet archived into archive_message.             */
/***************************************************************/

use dfslog
go

print "Creating new recent_message table"
go

if exists ( select name from sysobjects
                where name = 'recent_message' and type = 'U' )
                begin
                    print "Dropping existing recent_message table"
                    drop table recent_message
                end
go

create table recent_message (
    msg_id numeric(15,0) identity,
    archive_status tinyint default 1,
    logdt datetime not null,
    wsid varchar(15) null,
    compid varchar(25) null,
    labelid varchar(25) not null,
    priorid int not null,
    priorstr varchar(25) not null,
    logmsg varchar(160) null) 
go


create index recent_message_logdt_ind on recent_message(logdt)
go
create index recent_message_wsid_logdt_ind on recent_message(wsid, logdt)
go
create index recent_msg_id_ind on recent_message(msg_id)
go




/************************************************************************/
print "create the long-term archive_messagei table"
/* This table is identical in structure as recent_message except it has */
/* no archive_status field.                                             */
/************************************************************************/

use dfslog
go

if exists ( select name from sysobjects
                where name = 'archive_message' and type = 'U' )
                begin
                    print "Dropping existing archive_message table"
                    drop table archive_message
                end
go

create table archive_message (
    msg_id numeric(15,0) identity,
    logdt datetime not null,
    wsid varchar(15) null,
    compid varchar(25) null,
    labelid varchar(25) not null,
    priorid int not null,
    priorstr varchar(25) not null,
    logmsg varchar(160) null)
go

create index archive_message_logdt_ind on archive_message(logdt)
go
create index archive_msg_id_ind on archive_message(msg_id)
go




/*************************************/
print "create the addmessage procedure"
/*************************************/

if exists ( select name from sysobjects
                where name = 'addmessage' and type = 'P' )
                begin
                    print "Dropping existing addmessage table"
                    drop procedure addmessage
                end
go

create procedure addmessage
(@logdt_p datetime,  @wsid_p varchar(15), @compid_p
varchar(25),@labelid_p varchar(25), @priorid_p int, @logmsg_p
varchar(160) ) as
declare @priorstr_p varchar(25)
if @priorid_p = 1 select @priorstr_p = "Emergency"
else if @priorid_p = 2 select @priorstr_p = "Alert"
else if @priorid_p = 3 select @priorstr_p = "Critical"
else if @priorid_p = 4 select @priorstr_p = "Error"
else if @priorid_p = 5 select @priorstr_p = "Warning"
else if @priorid_p = 6 select @priorstr_p = "Notice"
else if @priorid_p = 7 select @priorstr_p = "Info"
else select @priorstr_p = "Debug"
insert into recent_message (logdt, wsid, compid, labelid,
priorid, priorstr, logmsg) VALUES (@logdt_p, @wsid_p, @compid_p,
@labelid_p, @priorid_p, @priorstr_p, @logmsg_p)
go


/**********************************************************************/
/* example execution of addmessage                                    */
/* execute addmessage @logdt_p="1998/12/12",                          */
/* @unitid_p=1, @wsid_p="wu0pl",                                      */
/* @compid_p="dhsSubscribe", @labelid_p= "blank", @priorid_p=2,       */
/* @logmsg_p="This is a test"                                         */
/* go                                                                 */
/**********************************************************************/




/********************************************************************/
print "Create the copy_messages_to_archive procedure"
/* The copy_messages_to_archive procedure will archive all messages */
/* which are high priority (priorid <= 5) and has not been archived */
/* (archive_status != 0) or must be logged (archive_status == 2).   */
/* Archived messages have the archive_status updated to 0.          */
/********************************************************************/

if exists ( select name from sysobjects
                where name = 'copy_messages_to_archive' and type = 'P' )
                begin
                    print "Dropping existing copy_messages_to_archive table"
                    drop procedure copy_messages_to_archive
                end
go

CREATE PROCEDURE copy_messages_to_archive AS
DECLARE archivecursor CURSOR
FOR SELECT logdt, wsid, compid, labelid, priorid, priorstr, logmsg
FROM recent_message WHERE (archive_status = 2) 
OR (priorid <= 5 AND archive_status != 0)
DECLARE @logdt_p datetime
DECLARE @logdt_str varchar(20)
DECLARE @wsid_p varchar(15)
DECLARE @compid_p varchar(25)
DECLARE @labelid_p varchar(25)
DECLARE @priorid_p int
DECLARE @priorstr_p varchar(25)
DECLARE @logmsg_p varchar(160)
DECLARE @counter_p int
DECLARE @counter_1000p int

/* loop through recent_messages and archive the important ones */
SELECT @counter_p = 0
SELECT @counter_1000p = 0
OPEN archivecursor
while 1= 1
BEGIN

FETCH archivecursor INTO @logdt_p, @wsid_p, @compid_p, @labelid_p,
@priorid_p, @priorstr_p, @logmsg_p 

/* exit if at the end of the result set */
IF @@sqlstatus = 2 BREAK

/* cancel the current transaction upon error */
IF @@sqlstatus = 1 CONTINUE

/* commence our transaction */
BEGIN TRAN

/* insert the message into the archive database */
INSERT INTO archive_message (logdt, wsid, compid, labelid,
priorid, priorstr, logmsg) VALUES (@logdt_p, @wsid_p, @compid_p,
@labelid_p, @priorid_p, @priorstr_p, @logmsg_p)
IF @@error != 0
BEGIN
ROLLBACK TRAN
CONTINUE
END
/* print out the message moved: */
SELECT @logdt_str = convert(varchar(20), @logdt_p, 0)
PRINT "archived: %1! %2! %3! %4! %5! %6! %7!", @logdt_str,
@wsid_p, @compid_p, @labelid_p, @priorid_p, @priorstr_p, @logmsg_p

/* delete the message from the recent_message table */
UPDATE recent_message SET archive_status=0 WHERE CURRENT OF archivecursor

/* commit the whole operation. */
IF @@error != 0
BEGIN
ROLLBACK TRAN
CONTINUE
END

COMMIT TRAN
SELECT @counter_p = @counter_p+1
IF @counter_p = 1000
BEGIN
SELECT @counter_p = 0
SELECT @counter_1000p = @counter_1000p + 1
PRINT "processed: %1! thousand messages", @counter_1000p
END

END
CLOSE archivecursor

SELECT @counter_p = (@counter_1000p * 1000) + @counter_p
PRINT "total processed: %1! messages", @counter_p
DEALLOCATE CURSOR archivecursor
go

/* End of PROCEDURE copy_messages_to_archive *\


/******************************************************/
/* example execution of copy_messages_to_archive      */
/*                                                    */
/* execute copy_messages_to_archive                   */
/* go                                                 */
/******************************************************/




/*******************************************************************/
print "Creating procedure delete_old_messages"
/*******************************************************************/
/* delete_old_messages deletes all messages which are older than   */
/* the number of days specified.                                   */
/* Before deleting it run copy_messages_to_archive to archive any  */
/* message which may be lost during the deletion.                  */
/* Message are deleted from the recent_message table.              */
/*******************************************************************/
 
if exists ( select name from sysobjects
                where name = 'delete_old_messages' and type = 'P' )
                begin
                    print "Dropping existing delete_old_messages table"
                    drop procedure delete_old_messages
                end
go

CREATE PROCEDURE delete_old_messages
(@numDaysToKeep int) AS
 
/* archive messages, before deleting them */
execute copy_messages_to_archive
DELETE FROM recent_message WHERE datediff(dayofyear,
dateadd(hour , -12, logdt), getdate()) > @numDaysToKeep
OR datediff(year, dateadd(hour , -12, logdt), getdate()) <> 0
go
 
/******************************************************/
/* example execution of delete_old_messages           */
/*                                                    */
/* execute delete_old_messages @numDaysToKeep=1       */
/* go                                                 */
/******************************************************/




/*******************************************************************/
print    "Creating procedure delete_old_archive_messages"
/*******************************************************************/
/* delete_old_archive_messages deletes all messages which are      */
/* older than the number of days specified.                        */
/* Message are deleted from the archive_message table.             */
/*******************************************************************/

if exists ( select name from sysobjects
                where name = 'delete_old_archive_messages' and type = 'P' )
                begin
                    print "Dropping existing delete_old_archive_messages table"
                    drop procedure delete_old_archive_messages
                end
go
 
CREATE PROCEDURE delete_old_archive_messages
(@numDaysToKeep int) AS
 
DELETE FROM archive_message WHERE datediff(dayofyear,
dateadd(hour , -12, logdt), getdate()) > @numDaysToKeep
OR datediff(year, dateadd(hour , -12, logdt), getdate()) <> 0
go
 
/*********************************************************/
/* example execution of delete_old_archive_messages      */
/*                                                       */
/* execute delete_old_archive_messages @numDaysToKeep=1  */
/* go                                                    */
/*********************************************************/


/* end of file dfslog.sql */

/**********************************************************
 * Revision history
 * ----------------
 *
 * $Log: dfslogCreate.sql,v $
 * Revision 1.1  2004/09/06 14:33:19  cguirao
 * *** empty log message ***
 *
 * Revision 1.1  2004/09/02 14:12:57  cguirao
 * *** empty log message ***
 *
 * Revision 1.1  2004/08/23 08:50:41  cguirao
 * *** empty log message ***
 *
 * Revision 1.1  2004/06/23 15:01:45  cguirao
 * First check-in
 *
 * Revision 1.3  2003/05/19 12:44:59  gmekiffe
 * Increase recent_message.msgid to 15
 *
 * 02.08.2002 gmekiffe  . DFS01051: change field length msg_id to 15
 *                      . drop table, before they are created
 *                      . drop procedures before they are created
 * xx.xx.1999 nkornwei  . First installation
 *
 **********************************************************/
/* eof */
 

