--This file is used to create Tables and Store Procedure in MSSQL database 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HashInfoTemp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HashInfoTemp]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MetaTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MetaTable]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TrackerStatus]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TrackerStatus]
GO

CREATE TABLE [dbo].[HashInfoTemp] (
	[HashInfo] [char] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[isAdd] [bit] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MetaTable] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[ClassID] [int] NULL ,
	[HashInfo] [char] (40) COLLATE Chinese_PRC_CI_AS NULL ,
	[FileName] [varchar] (250) COLLATE Chinese_PRC_CI_AS NULL ,
	[Description] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
	[TorrentUrl] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
	[Offer] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[PublisherID] [int] NULL ,
	[FileSize] [int] NULL ,
	[Tracker] [varchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
	[Subject] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
	[UploadIP] [char] (15) COLLATE Chinese_PRC_CI_AS NULL ,
	[Other_Url] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
	[Auditing] [bit] NOT NULL ,
	[PublishTime] [datetime] NULL ,
	[isbToped] [bit] NOT NULL ,
	[Member] [bit] NOT NULL ,
	[Hide] [bit] NOT NULL ,
	[localTF] [bit] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[TrackerStatus] (
	[HashString] [char] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[SeederNum] [int] NOT NULL ,
	[CompletedNum] [int] NOT NULL ,
	[PeerNum] [int] NOT NULL ,
	[UpdateTime] [datetime] NOT NULL 
) ON [PRIMARY]
GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bcs_update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[bcs_update]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE BcTracker_Update
	@hash_string char(40), 
	@completed_num int,
	@peer_num int,
	@seeder_num int
AS

IF EXISTS (SELECT HashString FROM TrackerStatus WHERE HashString=@hash_string)
	BEGIN
		UPDATE TrackerStatus SET CompletedNum=CompletedNum+@completed_num, 
			PeerNum=@peer_num, SeederNum=@seeder_num
      		WHERE HashString=@hash_string
	END
ELSE
	BEGIN
		INSERT INTO TrackerStatus ( CompletedNum, PeerNum, SeederNum, HashString)
		VALUES ( @completed_num, @peer_num, @seeder_num, @hash_string)
	END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
