/*
HistoryWiki - http://www.netbrick.net/HistoryWiki/
Copyright (c) 2004
by Tyler Jensen ( tylerj@netbrick.net ) of NetBrick Inc. ( http://www.netbrick.net )
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies
of the Software, and to permit persons to whom the Software is furnished to do
so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED,
INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT,
TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE
OR OTHER DEALINGS IN THE SOFTWARE.
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetTopicList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetTopicList]
GO
CREATE PROCEDURE GetTopicList
AS
BEGIN
SELECT W.WikiTopicID, W.WikiName, W.TopicTitle
FROM WikiTopic W
ORDER BY W.WikiName, W.TopicTitle
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetTopicID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetTopicID]
GO
CREATE PROCEDURE GetTopicID
(
@WikiName varchar(255),
@TopicTitle varchar(255)
)
AS
BEGIN
SELECT t.WikiTopicID
FROM WikiTopic t
WHERE LOWER(t.WikiName) = LOWER(@WikiName) AND LOWER(t.TopicTitle) = LOWER(@TopicTitle)
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetTopic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetTopic]
GO
CREATE PROCEDURE GetTopic
(
@WikiName varchar(255),
@TopicTitle varchar(255)
)
AS
BEGIN
SELECT t.WikiTopicID, t.WikiName, t.TopicTitle, t.Author, t.Updated, t.IsMinor, t.EditSummary, t.TopicText
FROM WikiTopic t
WHERE LOWER(t.WikiName) = LOWER(@WikiName) AND LOWER(t.TopicTitle) = LOWER(@TopicTitle)
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetOldTopic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetOldTopic]
GO
CREATE PROCEDURE GetOldTopic
(
@HistID int
)
AS
BEGIN
SELECT t.WikiTopicID, t.WikiName, t.TopicTitle, h.Author, h.Updated, h.IsMinor, h.EditSummary, h.TopicText
FROM WikiTopic t INNER JOIN WikiTopicHist h ON t.WikiTopicID = h.WikiTopicID
WHERE WikiHistID = @HistID
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetWikiTopicLock]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetWikiTopicLock]
GO
CREATE PROCEDURE GetWikiTopicLock
(
@WikiTopicID int
)
AS
BEGIN
SELECT WikiTopicID, LockDate, Author
FROM WikiTopicLock
WHERE WikiTopicID = @WikiTopicID
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ToggleWikiTopicLock]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ToggleWikiTopicLock]
GO
CREATE PROCEDURE ToggleWikiTopicLock
(
@WikiTopicID int,
@Author varchar(255)
)
AS
BEGIN
DECLARE @TopicID int
SET @TopicID = 0
SELECT @TopicID = WikiTopicID
FROM WikiTopicLock
WHERE WikiTopicID = @WikiTopicID
IF( @TopicID = 0 )
INSERT INTO WikiTopicLock (WikiTopicID, LockDate, Author) VALUES(@WikiTopicID, GETDATE(), @Author)
ELSE
DELETE FROM WikiTopicLock WHERE WikiTopicID = @WikiTopicID
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetWikiWatches]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetWikiWatches]
GO
CREATE PROCEDURE GetWikiWatches
(
@Author varchar(255)
)
AS
BEGIN
SELECT t.WikiTopicID, t.WikiName, t.TopicTitle, t.Author, t.Updated
FROM WikiTopic t INNER JOIN WikiWatch w ON t.WikiTopicID = w.WikiTopicID
WHERE UPPER(w.Author) = UPPER(@Author)
ORDER BY t.WikiName, t.TopicTitle
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IsWikiWatched]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[IsWikiWatched]
GO
CREATE PROCEDURE IsWikiWatched
(
@WikiTopicID int,
@Author varchar(255)
)
AS
BEGIN
SELECT WikiTopicID
FROM WikiWatch
WHERE UPPER(Author) = UPPER(@Author) AND WikiTopicID = @WikiTopicID
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ToggleWikiWatch]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ToggleWikiWatch]
GO
CREATE PROCEDURE ToggleWikiWatch
(
@WikiTopicID int,
@Author varchar(255)
)
AS
BEGIN
DECLARE @TopicID int
SET @TopicID = 0
SELECT @TopicID = WikiTopicID
FROM WikiWatch
WHERE WikiTopicID = @WikiTopicID AND UPPER(Author) = UPPER(@Author)
IF( @TopicID = 0 )
INSERT INTO WikiWatch (WikiTopicID, Author) VALUES(@WikiTopicID, @Author)
ELSE
DELETE FROM WikiWatch WHERE WikiTopicID = @WikiTopicID AND UPPER(Author) = UPPER(@Author)
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetWikiWatchAddress]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetWikiWatchAddress]
GO
CREATE PROCEDURE GetWikiWatchAddress
(
@Author varchar(255)
)
AS
BEGIN
SELECT WatchAddress
FROM WikiWatchAddress
WHERE UPPER(Author) = UPPER(@Author)
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateWikiWatchAddress]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateWikiWatchAddress]
GO
CREATE PROCEDURE UpdateWikiWatchAddress
(
@Author varchar(255),
@WatchAddress varchar(255)
)
AS
BEGIN
DELETE FROM WikiWatchAddress WHERE UPPER(Author) = UPPER(@Author)
INSERT INTO WikiWatchAddress (Author, WatchAddress) VALUES(@Author, @WatchAddress)
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetWikiWatchList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetWikiWatchList]
GO
CREATE PROCEDURE GetWikiWatchList
(
@WikiTopicID int
)
AS
BEGIN
SELECT w.Author, a.WatchAddress
FROM WikiWatch w INNER JOIN WikiWatchAddress a ON w.Author = a.Author
WHERE w.WikiTopicID = @WikiTopicID
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetOldTalkTopic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetOldTalkTopic]
GO
CREATE PROCEDURE GetOldTalkTopic
(
@HistID int
)
AS
BEGIN
SELECT t.WikiTopicID, t.WikiName, t.TopicTitle, h.Author, h.Updated, h.IsMinor, h.EditSummary, h.TopicText
FROM WikiTopic t INNER JOIN WikiTalkHist h ON t.WikiTopicID = h.WikiTopicID
WHERE WikiHistID = @HistID
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetTopicTalk]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetTopicTalk]
GO
CREATE PROCEDURE GetTopicTalk
(
@WikiName varchar(255),
@TopicTitle varchar(255)
)
AS
BEGIN
SELECT t.WikiTopicID, t.WikiName, t.TopicTitle, k.Author, k.Updated, k.IsMinor, k.EditSummary, k.TopicText
FROM WikiTopic t INNER JOIN WikiTalk k ON t.WikiTopicID = k.WikiTopicID
WHERE LOWER(t.WikiName) = LOWER(@WikiName) AND LOWER(t.TopicTitle) = LOWER(@TopicTitle)
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetTopicByID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetTopicByID]
GO
CREATE PROCEDURE GetTopicByID
(
@TopicID int
)
AS
BEGIN
SELECT t.WikiTopicID, t.WikiName, t.TopicTitle, t.Author, t.Updated, t.IsMinor, t.EditSummary, t.TopicText
FROM WikiTopic t
WHERE WikiTopicID = @TopicID
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetTopicHistory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetTopicHistory]
GO
CREATE PROCEDURE GetTopicHistory
(
@WikiName varchar(255),
@TopicTitle varchar(255)
)
AS
BEGIN
SELECT h.WikiHistID, t.WikiTopicID, h.Author, h.Updated, h.IsMinor, h.EditSummary
FROM WikiTopic t INNER JOIN WikiTopicHist h ON t.WikiTopicID = h.WikiTopicID
WHERE LOWER(t.WikiName) = LOWER(@WikiName) AND LOWER(t.TopicTitle) = LOWER(@TopicTitle)
ORDER BY h.Updated DESC
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetTopicHistoryText]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetTopicHistoryText]
GO
CREATE PROCEDURE GetTopicHistoryText
(
@WikiHistID int
)
AS
BEGIN
SELECT h.WikiHistID, h.WikiTopicID, h.Author, h.Updated, h.IsMinor, h.EditSummary, h.TopicText
FROM WikiTopicHist h
WHERE h.WikiHistID = @WikiHistID
END
GO
-- count
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetTopicHistoryCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetTopicHistoryCount]
GO
CREATE PROCEDURE GetTopicHistoryCount
(
@WikiName varchar(255),
@TopicTitle varchar(255)
)
AS
BEGIN
SELECT COUNT(h.WikiHistID)
FROM WikiTopic t INNER JOIN WikiTopicHist h ON t.WikiTopicID = h.WikiTopicID
WHERE LOWER(t.WikiName) = LOWER(@WikiName) AND LOWER(t.TopicTitle) = LOWER(@TopicTitle)
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetTopicTalkHistoryCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetTopicTalkHistoryCount]
GO
CREATE PROCEDURE GetTopicTalkHistoryCount
(
@WikiName varchar(255),
@TopicTitle varchar(255)
)
AS
BEGIN
SELECT COUNT(h.WikiHistID)
FROM WikiTopic t INNER JOIN WikiTalkHist h ON t.WikiTopicID = h.WikiTopicID
WHERE LOWER(t.WikiName) = LOWER(@WikiName) AND LOWER(t.TopicTitle) = LOWER(@TopicTitle)
END
GO
-- count
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetTopicTalkHistory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetTopicTalkHistory]
GO
CREATE PROCEDURE GetTopicTalkHistory
(
@WikiName varchar(255),
@TopicTitle varchar(255)
)
AS
BEGIN
SELECT h.WikiHistID, t.WikiTopicID, h.Author, h.Updated, h.IsMinor, h.EditSummary
FROM WikiTopic t INNER JOIN WikiTalkHist h ON t.WikiTopicID = h.WikiTopicID
WHERE LOWER(t.WikiName) = LOWER(@WikiName) AND LOWER(t.TopicTitle) = LOWER(@TopicTitle)
ORDER BY h.Updated DESC
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetTopicTalkHistoryText]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetTopicTalkHistoryText]
GO
CREATE PROCEDURE GetTopicTalkHistoryText
(
@WikiHistID int
)
AS
BEGIN
SELECT h.WikiHistID, h.WikiTopicID, h.Author, h.Updated, h.IsMinor, h.EditSummary, h.TopicText
FROM WikiTalkHist h
WHERE h.WikiHistID = @WikiHistID
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetTopicAndRank]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetTopicAndRank]
GO
CREATE PROCEDURE GetTopicAndRank
(
@WikiName varchar(255),
@TopicTitle varchar(255)
)
AS
BEGIN
SELECT t.WikiTopicID, t.WikiName, t.TopicTitle, t.Author, t.Updated, t.IsMinor, t.EditSummary, ISNULL(r.TopicRank, -1), t.TopicText
FROM WikiTopic t
LEFT OUTER JOIN WikiTopicRank r ON t.WikiTopicID = r.WikiTopicID
WHERE LOWER(t.WikiName) = LOWER(@WikiName) AND LOWER(t.TopicTitle) = LOWER(@TopicTitle)
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateTopic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateTopic]
GO
CREATE PROCEDURE UpdateTopic
(
@WikiName varchar(255),
@TopicTitle varchar(255),
@Author varchar(255),
@IsMinor bit,
@EditSummary varchar(512),
@TopicText text
)
AS
BEGIN
DECLARE @TopicID int
SET @TopicID = 0
SELECT @TopicID = WikiTopicID
FROM WikiTopic
WHERE LOWER(WikiName) = LOWER(@WikiName) AND LOWER(TopicTitle) = LOWER(@TopicTitle)
IF ( @TopicID = 0 )
BEGIN
INSERT INTO WikiTopic
(WikiName, TopicTitle, Author, Updated, IsMinor, EditSummary, TopicText)
VALUES(@WikiName, @TopicTitle, @Author, GetDate(), @IsMinor, @EditSummary, @TopicText)
SELECT @TopicID = @@IDENTITY
END
ELSE
BEGIN
INSERT INTO WikiTopicHist
SELECT WikiTopicID, Author, Updated, IsMinor, EditSummary, TopicText FROM WikiTopic WHERE WikiTopicID = @TopicID
UPDATE WikiTopic
SET WikiName = @WikiName,
TopicTitle = @TopicTitle,
Author = @Author,
Updated = GetDate(),
IsMinor = @IsMinor,
EditSummary = @EditSummary,
TopicText = @TopicText
WHERE WikiTopicID = @TopicID
END
SELECT @TopicID
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateTopicTalk]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateTopicTalk]
GO
CREATE PROCEDURE UpdateTopicTalk
(
@WikiName varchar(255),
@TopicTitle varchar(255),
@Author varchar(255),
@IsMinor bit,
@EditSummary varchar(512),
@TopicText text
)
AS
BEGIN
DECLARE @TopicID int
DECLARE @TalkID int
SET @TopicID = 0
SET @TalkID = 0
SELECT @TopicID = WikiTopicID
FROM WikiTopic
WHERE LOWER(WikiName) = LOWER(@WikiName) AND LOWER(TopicTitle) = LOWER(@TopicTitle)
IF ( @TopicID = 0 )
BEGIN
INSERT INTO WikiTopic
(WikiName, TopicTitle, Author, Updated, IsMinor, EditSummary, TopicText)
VALUES(@WikiName, @TopicTitle, @Author, GetDate(), @IsMinor, 'auto generated', 'Discussion generated text...')
SET @TopicID = @@IDENTITY
END
SELECT @TalkID = WikiTopicID
FROM WikiTalk
WHERE WikiTopicID = @TopicID
IF ( @TalkID = 0 )
BEGIN
INSERT INTO WikiTalk
(WikiTopicID, Author, Updated, IsMinor, EditSummary, TopicText)
VALUES(@TopicID, @Author, GetDate(), @IsMinor, @EditSummary, @TopicText)
SET @TalkID = @TopicID
END
ELSE
BEGIN
INSERT INTO WikiTalkHist
SELECT WikiTopicID, Author, Updated, IsMinor, EditSummary, TopicText FROM WikiTalk WHERE WikiTopicID = @TalkID
UPDATE WikiTalk
SET Author = @Author,
Updated = GetDate(),
IsMinor = @IsMinor,
EditSummary = @EditSummary,
TopicText = @TopicText
WHERE WikiTopicID = @TalkID
END
SELECT @TalkID
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetTopics]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetTopics]
GO
CREATE PROCEDURE GetTopics
AS
BEGIN
SELECT WikiTopicID, WikiName, TopicTitle
FROM WikiTopic
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetWikis]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetWikis]
GO
CREATE PROCEDURE GetWikis
AS
BEGIN
SELECT DISTINCT WikiName
FROM WikiTopic ORDER BY WikiName
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetWords]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetWords]
GO
CREATE PROCEDURE GetWords
AS
BEGIN
SELECT [Word], [WordID]
FROM WikiWords
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetTopicIndex]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetTopicIndex]
GO
CREATE PROCEDURE GetTopicIndex
@WikiTopicID int
AS
BEGIN
SELECT WordID, WordCount
FROM WikiWordIndex
WHERE WikiTopicID = @WikiTopicID
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WikiWordIndex_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[WikiWordIndex_Delete]
GO
CREATE PROCEDURE WikiWordIndex_Delete
@WordID int,
@WikiTopicID int
AS
BEGIN
DELETE FROM WikiWordIndex
WHERE WordID = @WordID AND WikiTopicID = @WikiTopicID
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WikiWordIndex_Add]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[WikiWordIndex_Add]
GO
CREATE PROCEDURE WikiWordIndex_Add
@WordID int,
@WikiTopicID int,
@WordCount int
AS
BEGIN
INSERT INTO WikiWordIndex
(WordID, WikiTopicID, WordCount)
VALUES (@WordID, @WikiTopicID, @WordCount)
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WikiWordIndex_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[WikiWordIndex_Update]
GO
CREATE PROCEDURE WikiWordIndex_Update
@WordID int,
@WikiTopicID int,
@WordCount int
AS
BEGIN
UPDATE WikiWordIndex
SET WordCount = @WordCount
WHERE WordID = @WordID AND WikiTopicID = @WikiTopicID
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WikiSummary_AddUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[WikiSummary_AddUpdate]
GO
CREATE PROCEDURE WikiSummary_AddUpdate
@WikiTopicID int,
@Summary varchar(256)
AS
BEGIN
DECLARE @exists int
SET @exists = 0
SELECT @exists = COUNT(WikiTopicID)
FROM WikiSummary
WHERE WikiTopicID = @WikiTopicID
IF(@exists = 0)
INSERT INTO WikiSummary
(WikiTopicID, LastIndexed, Summary)
VALUES (@WikiTopicID, GETDATE(), @Summary)
ELSE
UPDATE WikiSummary
SET WikiTopicID = @WikiTopicID,
LastIndexed = GETDATE(),
Summary = @Summary
WHERE WikiTopicID = @WikiTopicID
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetTopicsIndexed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetTopicsIndexed]
GO
CREATE PROCEDURE GetTopicsIndexed
AS
BEGIN
SELECT WikiTopicID
FROM WikiSummary
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RemoveIndexedTopic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[RemoveIndexedTopic]
GO
CREATE PROCEDURE RemoveIndexedTopic
@WikiTopicID int
AS
BEGIN
DELETE WikiSummary
WHERE WikiTopicID = @WikiTopicID
DELETE WikiTopicRank
WHERE WikiTopicID = @WikiTopicID
DELETE WikiWordIndex
WHERE WikiTopicID = @WikiTopicID
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WikiTopicRank_AddUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[WikiTopicRank_AddUpdate]
GO
CREATE PROCEDURE WikiTopicRank_AddUpdate
@WikiTopicID int,
@TopicRank int
AS
BEGIN
DECLARE @exists int
SET @exists = 0
SELECT @exists = COUNT(WikiTopicID)
FROM WikiTopicRank
WHERE WikiTopicID = @WikiTopicID
IF(@exists = 0)
INSERT INTO WikiTopicRank
(WikiTopicID, TopicRank)
VALUES (@WikiTopicID, @TopicRank)
ELSE
UPDATE WikiTopicRank
SET WikiTopicID = @WikiTopicID,
TopicRank = @TopicRank
WHERE WikiTopicID = @WikiTopicID
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AddWord]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AddWord]
GO
CREATE PROCEDURE AddWord
@Word varchar(50)
AS
BEGIN
INSERT INTO WikiWords
( [Word] ) VALUES ( @Word )
SELECT @@IDENTITY AS WordID
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetRecent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetRecent]
GO
CREATE PROCEDURE GetRecent
(
@Days int,
@WikiName varchar(255)
)
AS
BEGIN
SELECT t.WikiTopicID, t.WikiName, t.TopicTitle, t.Author, t.Updated, t.IsMinor, t.EditSummary
FROM WikiTopic t
WHERE DATEDIFF( DAY, t.Updated, GETDATE() ) <= @Days AND t.WikiName = @WikiName
ORDER BY t.Updated DESC, LOWER(t.TopicTitle)
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetRecentAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetRecentAll]
GO
CREATE PROCEDURE GetRecentAll
(
@Days int
)
AS
BEGIN
SELECT t.WikiTopicID, t.WikiName, t.TopicTitle, t.Author, t.Updated, t.IsMinor, t.EditSummary
FROM WikiTopic t
WHERE DATEDIFF( DAY, t.Updated, GETDATE() ) <= @Days
ORDER BY LOWER(t.WikiName), t.Updated DESC, LOWER(t.TopicTitle)
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetRecentTalk]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetRecentTalk]
GO
CREATE PROCEDURE GetRecentTalk
(
@Days int,
@WikiName varchar(255)
)
AS
BEGIN
SELECT t.WikiTopicID, t.WikiName, t.TopicTitle, k.Author, k.Updated, k.IsMinor, k.EditSummary
FROM WikiTopic t INNER JOIN WikiTalk k ON t.WikiTopicID = k.WikiTopicID
WHERE DATEDIFF( DAY, k.Updated, GETDATE() ) <= @Days AND t.WikiName = @WikiName
ORDER BY k.Updated DESC, LOWER(t.TopicTitle)
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetRecentAllTalk]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetRecentAllTalk]
GO
CREATE PROCEDURE GetRecentAllTalk
(
@Days int
)
AS
BEGIN
SELECT t.WikiTopicID, t.WikiName, t.TopicTitle, k.Author, k.Updated, k.IsMinor, k.EditSummary
FROM WikiTopic t INNER JOIN WikiTalk k ON t.WikiTopicID = k.WikiTopicID
WHERE DATEDIFF( DAY, k.Updated, GETDATE() ) <= @Days
ORDER BY LOWER(t.WikiName), k.Updated DESC, LOWER(t.TopicTitle)
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WordSearch]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[WordSearch]
GO
CREATE PROCEDURE WordSearch
@Words varchar(1024) -- colon delimited search terms
AS
BEGIN
SELECT DISTINCT
w.WikiTopicID, w.WikiName, w.TopicTitle,
-- w.TopicRank, SUM(w.TopicRank) AS RankSum, COUNT(w.WikiTopicID) AS HitCount,
-- SUM(w.WordCount) AS WordTotal,
( (SUM(w.TopicRank) * 5) + SUM(w.WordCount) ) AS TotalRank,
w.Updated, w.Author, w.Summary
FROM WikiSearch w
WHERE w.WordID IN
(SELECT s.WordID FROM WikiWords s WHERE @Words LIKE '%:' + s.Word + ':%' )
GROUP BY w.WikiTopicID, w.WikiName, w.TopicTitle, w.TopicRank, w.Updated, w.Author, w.Summary
ORDER BY TotalRank DESC
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetTopicsStartLike]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetTopicsStartLike]
GO
CREATE PROCEDURE GetTopicsStartLike
(
@WikiName varchar(255),
@String varchar(255)
)
AS
BEGIN
SELECT DISTINCT TopicTitle,
MAX(Updated) as LastUpdate
FROM WikiTopic
WHERE UPPER(WikiName) = UPPER(@WikiName) AND UPPER(TopicTitle) LIKE UPPER(@String) + '%'
GROUP BY TopicTitle
ORDER BY LastUpdate DESC
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetTopicsEndLike]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetTopicsEndLike]
GO
CREATE PROCEDURE GetTopicsEndLike
(
@WikiName varchar(255),
@String varchar(255)
)
AS
BEGIN
SELECT DISTINCT TopicTitle,
MAX(Updated) as LastUpdate
FROM WikiTopic
WHERE UPPER(WikiName) = UPPER(@WikiName) AND
( (UPPER(TopicTitle) LIKE '%' + UPPER(@String))
OR (UPPER(TopicTitle) LIKE '%' + UPPER(@String) + '_') )
GROUP BY TopicTitle
ORDER BY LastUpdate DESC
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetAllWikiFileNames]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAllWikiFileNames]
GO
CREATE PROCEDURE GetAllWikiFileNames
AS
BEGIN
SELECT [WikiName], [FileName], FileSize, FileDate
FROM WikiFile
ORDER BY [FileName]
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetWikiFileNames]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetWikiFileNames]
GO
CREATE PROCEDURE GetWikiFileNames
(
@WikiName varchar(255)
)
AS
BEGIN
SELECT [WikiName], [FileName], FileSize, FileDate
FROM WikiFile
WHERE UPPER([WikiName]) = UPPER(@WikiName)
ORDER BY [FileName]
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetWikiFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetWikiFile]
GO
CREATE PROCEDURE GetWikiFile
(
@WikiName varchar(255),
@FileName varchar(255)
)
AS
BEGIN
SELECT FileData
FROM WikiFile
WHERE UPPER([WikiName]) = UPPER(@WikiName) AND UPPER([FileName]) = UPPER(@FileName)
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AddWikiFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AddWikiFile]
GO
CREATE PROCEDURE AddWikiFile
(
@WikiName varchar(255),
@FileName varchar(255),
@FileSize int,
@FileData image
)
AS
BEGIN
DECLARE @errmsg varchar(255)
SET @errmsg = ''
SELECT @errmsg = [FileName] FROM WikiFile WHERE UPPER([FileName]) = UPPER(@FileName)
IF (@errmsg = '')
BEGIN
INSERT INTO WikiFile
([WikiName], [FileName], FileSize, FileDate, FileData)
VALUES(@WikiName, @FileName, @FileSize, GetDate(), @FileData)
SET @errmsg = 'OK'
END
ELSE
BEGIN
SET @errmsg = 'EXISTS'
END
SELECT @errmsg
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RenameWikiFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[RenameWikiFile]
GO
CREATE PROCEDURE RenameWikiFile
(
@WikiName varchar(255),
@OldFileName varchar(255),
@NewFileName varchar(255)
)
AS
BEGIN
DECLARE @errmsg varchar(255)
SET @errmsg = ''
SELECT @errmsg = [FileName] FROM WikiFile WHERE UPPER([WikiName]) = UPPER(@WikiName) AND UPPER([FileName]) = UPPER(@OldFileName)
IF (@errmsg != '')
BEGIN
SET @errmsg = ''
SELECT @errmsg = [FileName] FROM WikiFile WHERE UPPER([WikiName]) = UPPER(@WikiName) AND UPPER([FileName]) = UPPER(@NewFileName)
IF (@errmsg = '')
BEGIN
UPDATE WikiFile
SET [FileName] = @NewFileName
WHERE UPPER([WikiName]) = UPPER(@WikiName) AND UPPER([FileName]) = UPPER(@OldFileName)
SET @errmsg = 'OK'
END
ELSE
BEGIN
SET @errmsg = 'EXISTS'
END
END
ELSE
BEGIN
SET @errmsg = 'NOTFOUND'
END
SELECT @errmsg
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteWikiFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteWikiFile]
GO
CREATE PROCEDURE DeleteWikiFile
(
@WikiName varchar(255),
@FileName varchar(255)
)
AS
BEGIN
DECLARE @errmsg varchar(255)
SET @errmsg = ''
SELECT @errmsg = [FileName] FROM WikiFile WHERE UPPER([WikiName]) = UPPER(@WikiName) AND UPPER([FileName]) = UPPER(@FileName)
IF (@errmsg != '')
BEGIN
DELETE FROM WikiFile
WHERE UPPER([WikiName]) = UPPER(@WikiName) AND UPPER([FileName]) = UPPER(@FileName)
SET @errmsg = 'OK'
END
ELSE
BEGIN
SET @errmsg = 'NOTFOUND'
END
SELECT @errmsg
END
GO
Submit an article or tip