void doIndex() { bool reNew = true; foreach (string type in indexInterface.Types) { IndexItem item; if (null != (item = indexInterface.Next(type))) { reNew = false; using (var cmd = new SqlCommand( "IF NOT EXISTS(SELECT * FROM [" + type + "] WHERE [ID]=@ID)\r\n" + " INSERT INTO [" + type + "]([ID],[title],[content],[time]) VALUES(@ID,@title,@content,@time)\r\n" + "ELSE\r\n" + //"IF "+ " UPDATE [" + type + "] SET [content]=@content,[title]=@title,[time]=@time WHERE [ID]=@ID\r\n" , conn)) { cmd.Parameters.AddWithValue("ID", item.ID); cmd.Parameters.AddWithValue("content", item.Content); cmd.Parameters.AddWithValue("title", item.Title); cmd.Parameters.AddWithValue("time", timeStamp[type]); cmd.ExecuteNonQuery(); } using (var cmd = new SqlCommand("DELETE FROM [Suffix" + type + "] WHERE ID=@ID", conn)) { cmd.Parameters.AddWithValue("ID", item.ID); cmd.ExecuteNonQuery(); } item.Keywords.Add(item.Title); foreach (string keyword in item.Keywords) { for (int i = 0; i < keyword.Length; i++) { using (var cmd = new SqlCommand("INSERT INTO [Suffix" + type + "]([ID],[content]) VALUES(@ID,@keyword)", conn)) { cmd.Parameters.AddWithValue("ID", item.ID); cmd.Parameters.AddWithValue("keyword", keyword.Substring(i)); cmd.ExecuteNonQuery(); } } } } } if (reNew) { foreach (string type in indexInterface.Types) { using (var cmd = new SqlCommand("DELETE FROM [" + type + "] WHERE [time]<>@time", conn)) { cmd.Parameters.AddWithValue("time", timeStamp[type]); cmd.ExecuteNonQuery(); } } indexInterface = null; sleepTime = 10000; } }
void init() { indexInterface = new IndexInterface(); timeStamp = new Dictionary<string, DateTime>(); foreach (string type in indexInterface.Types) { timeStamp.Add(type, DateTime.Now); using (var cmd = new SqlCommand( "IF NOT EXISTS(SELECT * FROM sysobjects WHERE [id] =object_id(@TableName)and OBJECTPROPERTY(id, N'IsUserTable') = 1)\r\n" + "BEGIN\r\n" + "CREATE TABLE [" + type + "]\r\n" + "(\r\n" + " [ID] INT,\r\n" + " [title] nvarchar(50),\r\n" + " [content] nvarchar(max),\r\n" + " [time] datetime\r\n" + " CONSTRAINT PK_"+type+" PRIMARY KEY([ID])\r\n" + ")\r\n" + "CREATE FULLTEXT INDEX ON [" + type + "]([content]) KEY INDEX PK_"+type+" ON ft_Indexer WITH(CHANGE_TRACKING = AUTO)\r\n" + "END\r\n" + "IF NOT EXISTS(SELECT * FROM sysobjects WHERE ID=object_id(@SuffixTableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1)\r\n" + "BEGIN\r\n" + " CREATE TABLE [Suffix" + type + "]\r\n" + " (\r\n" + " [ID] INT FOREIGN KEY([ID]) REFERENCES ["+type+"]([ID]) ON DELETE CASCADE,\r\n" + " [content] nvarchar(50)\r\n" + " )\r\n" + " CREATE NONCLUSTERED INDEX IDX_CONTENT ON [Suffix" + type + "] ([content])\r\n" + " CREATE NONCLUSTERED INDEX IDX_ID ON [Suffix" + type + "] ([ID])\r\n" + "END\r\n", conn )) { cmd.Parameters.AddWithValue("TableName", type); cmd.Parameters.AddWithValue("SuffixTableName", "Suffix" + type); cmd.ExecuteNonQuery(); } int flg; using (var cmd = new SqlCommand("select COUNT(*) from dbo.sysobjects where id = object_id(N'fn_search_" + type + "') and xtype='TF'", conn)) { flg = (int)cmd.ExecuteScalar(); } if (flg == 0) { using (var cmd = new SqlCommand("" + "CREATE FUNCTION fn_search_" + type + "(@key nvarchar(50),@top int,@skip int,@fulltext int = 1)\r\n" + "RETURNS @ReturnTable TABLE([ID] INT,[title] nvarchar(50),[content] varchar(max))\r\n" + "AS\r\n" + "BEGIN\r\n" + " SELECT @key=REPLACE(@key,'%',' ')\r\n" + " SELECT @key=REPLACE(@key,'_',' ')\r\n" + " SELECT @key=REPLACE(@key,'[',' ')\r\n" + " SELECT @key=REPLACE(@key,']',' ')\r\n" + " SELECT @key=REPLACE(@key,'^',' ')\r\n" + " DECLARE @T1 Table([ID] INT,[RANK] INT)\r\n" + " DECLARE @T2 Table([ID] INT,[RANK] INT)\r\n" + " IF @fulltext <> 0\r\n" + " INSERT INTO @T1\r\n" + " SELECT DISTINCT [ID],[RANK]=1000000 FROM [Suffix" + type + "] WHERE [content] like @key+'%' UNION ALL\r\n" + " SELECT * FROM FREETEXTTABLE([" + type + "],[content],@key)\r\n" + " ELSE\r\n" + " INSERT INTO @T1\r\n" + " SELECT DISTINCT [ID],[RANK]=1000000 FROM [Suffix" + type + "] WHERE [content] like @key+'%'" + " INSERT INTO @T2\r\n" + " SELECT [ID],[RANK] FROM\r\n" + " (SELECT TOP(@top+@skip) [ID],SUM([RANK]) AS [RANK],ROW_NUMBER() OVER(ORDER BY SUM([RANK]) DESC) AS RowNumber FROM @T1 GROUP BY [ID] ORDER BY SUM([RANK]) DESC) AS TMP\r\n" + " WHERE [RowNumber]>@skip\r\n" + " INSERT INTO @ReturnTable\r\n" + " SELECT [@T2].[ID],[title],[content] FROM\r\n" + " @T2 LEFT JOIN ["+type+"] ON [@T2].[ID]=["+type+"].[ID]\r\n" + " RETURN\r\n" + "END\r\n", conn)) { cmd.ExecuteNonQuery(); } } } }