SQL2005CLR函数扩展 - 关于山寨索引

复制代码 代码如下:

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Collections.Generic;
public partial class UserDefinedFunctions
{
    /// <summary>
    /// 设置索引目录
    /// </summary>
    /// <param></param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlFunction ]
    public static SqlBoolean SetRoot(SqlString value)
    {
        if (value.IsNull) return false ;
        if (System.IO.Directory .Exists(value.Value))
        {
            root = value.Value;
            return true ;
        }
        else
        {
            return false ;
        }
    }
    /// <summary>
    /// 获取索引目录
    /// </summary>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlFunction ]
    public static SqlString GetRoot()
    {
        return new SqlString (root);
    }
    /// <summary>
    /// 建立索引
    /// </summary>
    /// <param> 主键 </param>
    /// <param> 索引名称 </param>
    /// <param> 索引内容 </param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlFunction ]
    public static SqlInt32 SetIndex(SqlString key,SqlString indexName,SqlString content)
    {
        if (key.IsNull || content.IsNull||indexName.IsNull) return 0;
        return _setIndex(key.Value,indexName.Value, content.Value);
    }

    /// <summary>
    /// 查询索引
    /// </summary>
    /// <param> 关键字（空格区分） </param>
    /// <param> 索引名称 </param>
    /// <returns></returns>
    [SqlFunction (TableDefinition = "pk nvarchar(900)" , Name = "GetIndex" , FillRowMethodName = "FillRow" )]
    public static IEnumerable GetIndex(SqlString word,SqlString indexName)
    {

        System.Collections.Generic.List <string > ret = new List <string >();
        if (word.IsNull || indexName.IsNull) return ret;
        return _getIndex2(word.Value, indexName.Value);
    }

    public static void FillRow(Object obj, out SqlString pk)
    {
        string key = obj.ToString();
        pk = key;
    }
    static string root = @"d:/index" ;

    /// <summary>
    /// 获取有空格分隔的索引信息
    /// </summary>
    /// <param></param>
    /// <param></param>
    /// <returns></returns>
    static System.Collections.Generic.List <string > _getIndex2(string word, string indexName)
    {
        string [] arrWord = word.Split(new char [] { ' ' }, StringSplitOptions .RemoveEmptyEntries);

        System.Collections.Generic.List <string > key_0 = _getIndex(arrWord[0], indexName);

        if (arrWord.Length == 0) return key_0;
        System.Collections.Generic.List <string > [] key_list=new List <string >[arrWord.Length-1];
        for (int i = 0; i < arrWord.Length-1; i++)
        {
            System.Collections.Generic.List <string > key_i = _getIndex(arrWord[i+1],indexName);
            key_list[i] = key_i;
        }

        for (int i=key_0.Count-1;i>=0;i--)
        {
            foreach (System.Collections.Generic.List <string > key_i in key_list)
            {
                if (key_i.Contains(key_0[i]) == false )
                {
                    key_0.RemoveAt(i);
                    continue ;
                }
            }
        }
        return key_0;
    }
    /// <summary>
    /// 获取单个词的索引信息
    /// </summary>
    /// <param></param>
    /// <param></param>
    /// <returns></returns>
    static System.Collections.Generic.List <string > _getIndex(string word, string indexName)
    {
        System.Collections.Generic.List <string > ret = new List <string >();
        byte [] bWord = System.Text.Encoding .Unicode.GetBytes(word);
        if (bWord.Length < 4) return ret;

        string path = string .Format(@"{0}/{1}/{2}/{3}/{4}/{5}/" , root,indexName, bWord[0], bWord[1], bWord[2], bWord[3]);
        if (System.IO.Directory .Exists(path) == false )
        {
            return ret;
        }
        string [] arrFiles = System.IO.Directory .GetFiles(path);

        foreach (string file in arrFiles)
        {
            string key = System.IO.Path .GetFileNameWithoutExtension(file);
            string index = System.IO.Path .GetExtension(file).TrimStart(new char [] { '.' });
            int cIndex = int .Parse(index);
            bool bHas = true ;
            for (int i = 2; i < bWord.Length - 3; i = i + 2)
            {
                string nextFile = string .Format(@"{0}/{1}/{2}/{3}/{4}/{5}/{6}.{7}" ,
                    root, indexName, bWord[i + 0], bWord[i + 1], bWord[i + 2], bWord[i + 3], key, ++cIndex);

                if (System.IO.File .Exists(nextFile) == false )
                {
                    bHas = false ;
                    break ;
                }
            }
            if (bHas == true &&ret.Contains(key)==false )
                ret.Add(key);

        }
        return ret;
    }

    /// <summary>
    /// 建立索引文件
    /// </summary>
    /// <param></param>
    /// <param></param>
    /// <param></param>
    /// <returns></returns>
    static int _setIndex(string key,string indexName, string content)
    {
        byte [] bContent = System.Text.Encoding .Unicode.GetBytes(content);
        if (bContent.Length <= 4) return 0;
        for (int i = 0; i < bContent.Length - 3; i = i + 2)
        {
            string path = string .Format(@"{0}/{1}/{2}/{3}/{4}/{5}/" , root,indexName, bContent[i + 0], bContent[i + 1], bContent[i + 2], bContent[i + 3]);
            if (System.IO.Directory .Exists(path) == false )
            {
                System.IO.Directory .CreateDirectory(path);
            }
            string file = string .Format(@"{0}/{1}.{2}" , path, key, i / 2);

            if (System.IO.File .Exists(file) == false )
            {
                System.IO.File .Create(file).Close();
            }
        }
        return content.Length;
    }
};