webspider.sql

基于com的网络爬虫程序

SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/****** Object:  Stored Procedure dbo.mp_GetLinksExcepts    Script Date: 02.02.2005 20:26:43 ******/
CREATE PROCEDURE mp_GetLinksExcepts
			@nId INT
AS
	SELECT * FROM ExceptionLinksRegExps
			INNER JOIN LinksExceptionsRelations ON ExceptionLinksRegExps.nId = LinksExceptionsRelations.nExceptId
			WHERE LinksExceptionsRelations.nURLId = @nId

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/****** Object:  Stored Procedure dbo.mp_GetLinksRegExps    Script Date: 02.02.2005 20:26:43 ******/
CREATE PROCEDURE mp_GetLinksRegExps AS
SELECT * FROM LinksRegExps ORDER BY nType

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/****** Object:  Stored Procedure dbo.mp_GetNotParsedSources    Script Date: 02.02.2005 20:26:44 ******/
CREATE PROCEDURE mp_GetNotParsedSources AS
SELECT TOP 3 Sources.nId AS nId, sURL, sResult
	FROM Sources 
	INNER JOIN URLs ON Sources.nId = URLs.nId 
WHERE bParsed=0 ORDER BY nRegExpType

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/****** Object:  Stored Procedure dbo.mp_GetNotSearchedSources    Script Date: 02.02.2005 20:26:44 ******/
CREATE PROCEDURE mp_GetNotSearchedSources AS
SELECT * FROM Sources WHERE bSearched=0

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/****** Object:  Stored Procedure dbo.mp_GetNotTreatedURLs    Script Date: 02.02.2005 20:26:44 ******/
CREATE PROCEDURE mp_GetNotTreatedURLs AS
SELECT * FROM URLs WHERE bTreated=0 ORDER BY nId

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

/****** Object:  Stored Procedure dbo.mp_GetOption    Script Date: 02.02.2005 20:26:44 ******/
CREATE PROCEDURE mp_GetOption
			(
				@sName VARCHAR(1024)
			)
AS
SELECT sValue FROM Configuration WHERE sName=@sName;

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

/****** Object:  Stored Procedure dbo.mp_GetSearchExcepts    Script Date: 02.02.2005 20:26:44 ******/
CREATE PROCEDURE mp_GetSearchExcepts
			@nId INT
AS
	SELECT * FROM ExceptionSearchRegExps
			INNER JOIN SearchExceptionsRelations ON ExceptionSearchRegExps.nId = SearchExceptionsRelations.nExceptId
			WHERE SearchExceptionsRelations.nURLId = @nId

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

/****** Object:  Stored Procedure dbo.mp_GetSearchRegExps    Script Date: 02.02.2005 20:26:44 ******/
CREATE PROCEDURE mp_GetSearchRegExps AS
SELECT * FROM SearchRegExps

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/****** Object:  Stored Procedure dbo.mp_GetStatisticsByURL    Script Date: 02.02.2005 20:26:44 ******/
CREATE PROCEDURE mp_GetStatisticsByURL
			@sURL VARCHAR(1024),
			@nAll INT OUTPUT,
			@nTreated INT OUTPUT,
			@sStatus VARCHAR( 7 ) OUTPUT
AS
	DECLARE @nExceptId INT;
	SELECT @nExceptId = nExceptId FROM URLs 
		INNER JOIN LinksExceptionsRelations ON
			URLs.nId = LinksExceptionsRelations.nURLId
	WHERE sURL = @sURL;
	SELECT @nExceptId = nExceptId FROM URLs 
		INNER JOIN LinksExceptionsRelations ON
			URLs.nId = LinksExceptionsRelations.nURLId
	WHERE sURL = @sURL;
	SELECT @nAll = COUNT(*), @nTreated = SUM( CAST( bTreated AS INT ) )
		FROM LinksExceptionsRelations INNER JOIN URLs
			ON URLs.nId = LinksExceptionsRelations.nURLId
	WHERE @nExceptId = nExceptId
	DECLARE @nSources INT;
	SELECT @nSources = COUNT(*) 
		FROM LinksExceptionsRelations INNER JOIN URLs
			ON URLs.nId = LinksExceptionsRelations.nURLId
	 INNER JOIN Sources
			ON URLs.nId = Sources.nId
	WHERE @nExceptId = nExceptId
	IF( @nAll = @nTreated AND @nSources=0 )
		SELECT @sStatus = 'Finish' ELSE SELECT @sStatus = 'Working';

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/****** Object:  Stored Procedure dbo.mp_InsertExceptionLinkRegExp    Script Date: 02.02.2005 20:26:44 ******/
CREATE PROCEDURE mp_InsertExceptionLinkRegExp
			(
			@sRegExp VARCHAR(8000),
			@sURL VARCHAR(1024),
			@bExcept BIT = 1
			)
AS
BEGIN TRAN
	DECLARE @nURLId INT;
	SELECT @nURLId = nId FROM URLs WHERE sURL=@sURL
	INSERT INTO ExceptionLinksRegExps (sRegExp, bExcept) VALUES (@sRegExp, @bExcept)
	DECLARE @nExceptId INT;
	SELECT @nExceptId = @@identity;
	INSERT INTO LinksExceptionsRelations ( nURLId, nExceptId ) VALUES ( @nURLId, @nExceptId );
COMMIT TRAN

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/****** Object:  Stored Procedure dbo.mp_InsertExceptionSearchRegExp    Script Date: 02.02.2005 20:26:44 ******/
CREATE PROCEDURE mp_InsertExceptionSearchRegExp
			(
			@sRegExp VARCHAR(8000),
			@sURL VARCHAR(1024),
			@bExcept BIT = 1
			)
AS
BEGIN TRAN
	DECLARE @nURLId INT;
	SELECT @nURLId = nId FROM URLs WHERE sURL=@sURL
	INSERT INTO ExceptionSearchRegExps (sRegExp, bExcept) VALUES (@sRegExp, @bExcept)
	DECLARE @nExceptId INT;
	SELECT @nExceptId = @@identity;
	INSERT INTO SearchExceptionsRelations ( nURLId, nExceptId ) VALUES ( @nURLId, @nExceptId );
COMMIT TRAN

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/****** Object:  Stored Procedure dbo.mp_InsertSearchResult    Script Date: 02.02.2005 20:26:44 ******/
CREATE PROCEDURE mp_InsertSearchResult
			(
			@nRegExpId INT,
			@nURLId INT,
			@sResult VARCHAR(8000)
			)
AS
BEGIN TRAN
IF NOT EXISTS( SELECT * FROM SearchResults WHERE sResult = @sResult )
	INSERT INTO SearchResults (nRegExpId, nURLId, sResult) VALUES (@nRegExpId, @nURLId, @sResult)
COMMIT TRAN

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/****** Object:  Stored Procedure dbo.mp_InsertSource    Script Date: 02.02.2005 20:26:44 ******/
CREATE PROCEDURE mp_InsertSource
			(
			@nId INT,
			@sResult TEXT
			)
AS
BEGIN TRAN
IF EXISTS( SELECT * FROM URLs WHERE nId=@nId )
BEGIN
	INSERT INTO Sources (nId, sResult) VALUES (@nId, @sResult)
	UPDATE URLs SET  nDownloadTime = DATEDIFF( SECOND, CONVERT(DATETIME, '1970-01-01 00:00:00'),	 GETDATE() )
END
COMMIT TRAN

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/****** Object:  Stored Procedure dbo.mp_InsertURL    Script Date: 02.02.2005 20:26:45 ******/
CREATE PROCEDURE mp_InsertURL
			(
			@sURL VARCHAR(1024),
			@nRegExpType INT = NULL,
			@nParentURLId VARCHAR(1024) = NULL
			)
AS
BEGIN TRAN
IF NOT EXISTS( SELECT * FROM URLs WHERE sURL=@sURL )
BEGIN
	INSERT INTO URLs (sURL, nTime, nRegExpType, nParentURLId) 
		VALUES (@sURL , DATEDIFF( SECOND, CONVERT(DATETIME, '1970-01-01 00:00:00'),	 GETDATE() ), @nRegExpType, @nParentURLId)
	DECLARE @nCurrentId INT
	SELECT @nCurrentId = @@identity
	DECLARE @nExceptId INT
	IF EXISTS( SELECT * FROM SearchExceptionsRelations WHERE nURLId = @nParentURLId )
	BEGIN
		SELECT @nExceptId = nExceptId FROM SearchExceptionsRelations WHERE nURLId = @nParentURLId 
		INSERT INTO SearchExceptionsRelations ( nExceptId, nURLId ) VALUES ( @nExceptId, @nCurrentId )
		INSERT INTO LinksExceptionsRelations ( nExceptId, nURLId ) VALUES ( @nExceptId, @nCurrentId )
	END
END
COMMIT TRAN

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/****** Object:  Stored Procedure dbo.mp_IsExistsURLs    Script Date: 02.02.2005 20:26:45 ******/
CREATE PROCEDURE mp_IsExistsURLs
			(
			@sURL VARCHAR(1024),
			@bExist BIT OUTPUT
			)
AS
	DECLARE @nId INT;
	SELECT @nId=nId FROM URLs WHERE sURL=@sURL
	DECLARE @nExceptId INT;
	SELECT @nExceptId = nExceptId FROM LinksExceptionsRelations WHERE nURLId=@nId;
	DECLARE @nCount INT;	
	IF EXISTS(SELECT * FROM URLs WHERE nId IN (SELECT nURLId FROM  LinksExceptionsRelations WHERE @nExceptId = nExceptId AND NOT nURLId=@nId))
		SELECT @bExist = 1;
	ELSE
		SELECT @bExist = 0;

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/****** Object:  Stored Procedure dbo.mp_MarkAsParsed    Script Date: 02.02.2005 20:26:45 ******/
CREATE PROCEDURE mp_MarkAsParsed
			(
			@nId INT
			)
AS
UPDATE Sources SET bParsed=1 WHERE nId = @nId

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/****** Object:  Stored Procedure dbo.mp_MarkAsSearched    Script Date: 02.02.2005 20:26:45 ******/
CREATE PROCEDURE mp_MarkAsSearched
			(
			@nId INT
			)
AS
UPDATE Sources SET bSearched=1 WHERE nId = @nId

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/****** Object:  Stored Procedure dbo.mp_MarkAsTreated    Script Date: 02.02.2005 20:26:45 ******/
CREATE PROCEDURE mp_MarkAsTreated
			(
			@nId INT
			)
AS
UPDATE URLs SET bTreated=1 WHERE nId = @nId

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/****** Object:  Stored Procedure dbo.mp_SetOption    Script Date: 02.02.2005 20:26:45 ******/
CREATE PROCEDURE mp_SetOption
			(
				@sName VARCHAR(1024),
				@sValue VARCHAR(1024)
			)
AS
UPDATE Configuration SET sValue=@sValue WHERE sName=@sName;

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Trigger dbo.tri_ClearOldSources    Script Date: 02.02.2005 20:26:45 ******/
CREATE TRIGGER tri_ClearOldSources ON [dbo].[Sources] 
FOR INSERT
AS
DELETE FROM Sources WHERE bParsed=1 AND bSearched=1

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO