使用PostGreSQL数据库进行text录入和text检索

前端之家收集整理的这篇文章主要介绍了使用PostGreSQL数据库进行text录入和text检索前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

中文分词

ChineseParse.cs

@H_404_6@using System;
@H_404_6@using System.Collections;
@H_404_6@using System.IO;
@H_404_6@using System.Text.RegularExpressions;

namespace FullTextSearch.Common
{
    /// <summary>
    /// 中文分词器。
    /// </summary>
    @H_404_6@public @H_404_6@class ChineseParse
    {
        @H_404_6@private @H_404_6@static @H_404_6@readonly ChineseWordsHashCountSet _countTable;

        @H_404_6@static ChineseParse()
        {
            _countTable = @H_404_6@new ChineseWordsHashCountSet();
            InitFromFile("ChineseDictionary.txt");
        }

        /// <summary>
        /// 从指定的文件中初始化中文词语字典和字符串次数字典。
        /// </summary>
        /// <param name="fileName">文件</param>
        @H_404_6@private @H_404_6@static @H_404_6@void InitFromFile(@H_404_6@string fileName)
        {
            @H_404_6@string path = Path.Combine(Directory.GetCurrentDirectory(),@"..\..\Common\",fileName);
            @H_404_6@if (File.Exists(path))
            {
                @H_404_6@using (StreamReader sr = File.OpenText(path))
                {
                    @H_404_6@string s = "";
                    @H_404_6@while ((s = sr.ReadLine()) != @H_404_6@null)
                    {
                        ChineseWordUnit _tempUnit = InitUnit(s);
                        _countTable.InsertWord(_tempUnit.Word);
                    }
                }
            }
        }

        /// <summary>
        /// 将一个字符串解析为ChineseWordUnit。
        /// </summary>
        /// <param name="s">字符串</param>
        /// <returns>解析得到的ChineseWordUnit</returns>
        /// 4
        /// 0
        @H_404_6@private @H_404_6@static ChineseWordUnit InitUnit(@H_404_6@string s)
        {
            @H_404_6@var reg = @H_404_6@new Regex(@"\s+");
            @H_404_6@string[] temp = reg.Split(s);
            //if (temp.Length != 2)
            //{
            // throw new Exception("字符串解析错误:" + s);
            //}
            @H_404_6@if (temp.Length != 1)
            {
                @H_404_6@throw @H_404_6@new Exception("字符串解析错误:" + s);
            }
            @H_404_6@return @H_404_6@new ChineseWordUnit(temp[0],Int32.Parse("1"));
        }

        /// <summary>
        /// 分析输入的字符串,将其切割成一个个的词语。
        /// </summary>
        /// <param name="s">待切割的字符串</param>
        /// <returns>所切割得到的中文词语数组</returns>
        @H_404_6@public @H_404_6@static @H_404_6@string[] ParseChinese(@H_404_6@string s)
        {
            @H_404_6@int _length = s.Length;
            @H_404_6@string _temp = String.Empty;
            @H_404_6@var _words = @H_404_6@new ArrayList();
            @H_404_6@for (@H_404_6@int i = 0; i < s.Length;)
            {
                _temp = s.Substring(i,1);
                @H_404_6@if (_countTable.GetCount(_temp) > 1)
                {
                    @H_404_6@int j = 2;
                    @H_404_6@for (; i + j < s.Length + 1 && _countTable.GetCount(s.Substring(i,j)) > 0; j++)
                    {
                    }
                    _temp = s.Substring(i,j - 1);
                    i = i + j - 2;
                }
                i++;
                _words.Add(_temp);
            }
            @H_404_6@var _tempStringArray = @H_404_6@new @H_404_6@string[_words.Count];
            _words.CopyTo(_tempStringArray);
            @H_404_6@return _tempStringArray;
        }
    }
}

ChineseWordsHashCountSet.cs

@H_404_6@using System.Collections;

namespace FullTextSearch.Common
{
    /// <summary>
    /// 记录字符串出现在中文字典所录中文词语的前端的次数的字典类。如字符串"中"出现在"中国"的前端,则在字典中记录一个次数
    /// </summary>
    @H_404_6@public @H_404_6@class ChineseWordsHashCountSet
    {
        /// <summary>
        /// 记录字符串在中文词语中出现次数的Hashtable。键为特定的字符串,值为该字符串在中文词语中出现的次数
        /// </summary>
        @H_404_6@private @H_404_6@readonly Hashtable _rootTable;

        /// <summary>
        /// 类型初始化。
        /// </summary>
        @H_404_6@public ChineseWordsHashCountSet()
        {
            _rootTable = @H_404_6@new Hashtable();
        }

        /// <summary>
        /// 查询指定字符串出现在中文字典所录中文词语的前端的次数
        /// </summary>
        /// <param name="s">指定字符串</param>
        /// <returns>字符串出现在中文字典所录中文词语的前端的次数。若为-1,表示不出现。</returns>
        @H_404_6@public @H_404_6@int GetCount(@H_404_6@string s)
        {
            @H_404_6@if (!_rootTable.ContainsKey(s.Length))
            {
                @H_404_6@return -1;
            }
            @H_404_6@var _tempTable = (Hashtable) _rootTable[s.Length];
            @H_404_6@if (!_tempTable.ContainsKey(s))
            {
                @H_404_6@return -1;
            }
            @H_404_6@return (@H_404_6@int) _tempTable[s];
        }

        /// <summary>
        ///次数字典中插入一个词语。解析该词语,插入次数字典。
        /// </summary>
        /// <param name="s">所处理的字符串。</param>
        @H_404_6@public @H_404_6@void InsertWord(@H_404_6@string s)
        {
            @H_404_6@for (@H_404_6@int i = 0; i < s.Length; i++)
            {
                @H_404_6@string _s = s.Substring(0,i + 1);
                InsertSubString(_s);
            }
        }

        /// <summary>
        ///次数字典中插入一个字符串的次数记录。
        /// </summary>
        /// <param name="s">所插入的字符串。</param>
        @H_404_6@private @H_404_6@void InsertSubString(@H_404_6@string s)
        {
            @H_404_6@if (!_rootTable.ContainsKey(s.Length) && s.Length > 0)
            {
                @H_404_6@var _newHashtable = @H_404_6@new Hashtable();
                _rootTable.Add(s.Length,_newHashtable);
            }
            @H_404_6@var _tempTable = (Hashtable) _rootTable[s.Length];
            @H_404_6@if (!_tempTable.ContainsKey(s))
            {
                _tempTable.Add(s,1);
            }
            @H_404_6@else
            {
                _tempTable[s] = (@H_404_6@int) _tempTable[s] + 1;
            }
        }
    }
}

ChineseWordUnit.cs

namespace FullTextSearch.Common
{
    @H_404_6@public @H_404_6@struct ChineseWordUnit
    {
        @H_404_6@private @H_404_6@readonly @H_404_6@int _power;
        @H_404_6@private @H_404_6@readonly @H_404_6@string _word;

        /// <summary>
        /// 结构初始化。
        /// </summary>
        /// <param name="word">中文词语</param>
        /// <param name="power">该词语的权重</param>
        @H_404_6@public ChineseWordUnit(@H_404_6@string word,@H_404_6@int power)
        {
            _word = word;
            _power = power;
        }

        /// <summary>
        /// 中文词语单元所对应的中文词。
        /// </summary>
        @H_404_6@public @H_404_6@string Word
        {
            @H_404_6@get { @H_404_6@return _word; }
        }

        /// <summary>
        ///中文词语的权重。
        /// </summary>
        @H_404_6@public @H_404_6@int Power
        {
            @H_404_6@get { @H_404_6@return _power; }
        }
    }
}

ChineseDictionary.txt


主窗体界面

MainManager.cs

@H_404_6@using System;
@H_404_6@using System.Collections.Generic;
@H_404_6@using System.Data;
@H_404_6@using System.Drawing;
@H_404_6@using System.Windows.Forms;
@H_404_6@using FullTextSearch.Common;
@H_404_6@using Npgsql;

namespace FullTextSearch
{
    @H_404_6@public @H_404_6@partial @H_404_6@class MainManager : Form
    {
        @H_404_6@private @H_404_6@readonly Postgresql pg = @H_404_6@new Postgresql();
        @H_404_6@private @H_404_6@readonly sqlquerys sqlQuerys = @H_404_6@new sqlquerys();
        @H_404_6@private @H_404_6@char analysisType;
        @H_404_6@private @H_404_6@string createConnString = "";
        @H_404_6@private DataSet dataSet = @H_404_6@new DataSet();
        @H_404_6@private DataTable dataTable = @H_404_6@new DataTable();

        @H_404_6@private @H_404_6@char odabirAndOr;
        @H_404_6@private @H_404_6@char vrstaPretrazivanja;

        @H_404_6@public MainManager()
        {
            InitializeComponent();
            rbtn_AND.Checked = @H_404_6@true;
            rbtnNeizmjenjeni.Checked = @H_404_6@true;
            odabirAndOr = '*';
            radioButton_Day.Checked = @H_404_6@true;
            radioButton_Day.Checked = @H_404_6@true;
        }

        @H_404_6@private @H_404_6@void Form1_Load(@H_404_6@object sender,EventArgs e)
        {
            gb_unosPodataka.Enabled = @H_404_6@false;
            groupBox_Search.Enabled = @H_404_6@false;
            groupBox_Analysis.Enabled = @H_404_6@false;
            button_Disconnect.Enabled = @H_404_6@false;
            button_Pretrazi.BackColor = Color.WhiteSmoke;
            button_Disconnect.BackColor = Color.WhiteSmoke;
            button_unosTekstaUBazu.BackColor = Color.WhiteSmoke;
            button1.BackColor = Color.WhiteSmoke;
        }

        @H_404_6@private @H_404_6@void button_unosTekstaUBazu_Click(@H_404_6@object sender,EventArgs e)
        {
            @H_404_6@string searchTextBoxString = rTB_unosTextaUBazu.Text;

            @H_404_6@if (searchTextBoxString != "")
            {
                pg.insertIntoTable(searchTextBoxString,pg.conn);
                MessageBox.Show(searchTextBoxString + " 添加数据库!");
                rTB_unosTextaUBazu.Clear();
            }
            @H_404_6@else
            {
                MessageBox.Show("不允许空数据!");
            }
        }

        @H_404_6@private @H_404_6@void button_Pretrazi_Click(@H_404_6@object sender,EventArgs e)
        {
            @H_404_6@string stringToSearch;
            @H_404_6@string sql;
            @H_404_6@string highlitedText;
            @H_404_6@string rank;
            @H_404_6@string check;

            stringToSearch = txt_Search.Text.Trim();
            @H_404_6@var list = @H_404_6@new List<@H_404_6@string>(ChineseParse.ParseChinese(stringToSearch));
            ;

            sql = sqlQuerys.createsqlString(list,odabirAndOr,vrstaPretrazivanja);
            richTextBox1.Text = sql;

            check = sqlQuerys.testIfEmpty(stringToSearch);
            pg.insertIntoAnalysisTable(stringToSearch,pg.conn);

            pg.openConnection();

            @H_404_6@var command = @H_404_6@new NpgsqlCommand(sql,pg.conn);
            NpgsqlDataReader reader = command.ExecuteReader();
            @H_404_6@int count = 0;
            linkLabel_Rezultat.Text = " ";
            @H_404_6@while (reader.Read())
            {
                highlitedText = reader[1].ToString();
                rank = reader[3].ToString();
                linkLabel_Rezultat.Text += highlitedText + "[" + rank + "]\n";
                count++;
            }
            labelBrojac.Text = "找到的文件数量: " + count;
            pg.closeConnection();
        }

        @H_404_6@private @H_404_6@void rbtn_AND_CheckedChanged(@H_404_6@object sender,EventArgs e)
        {
            odabirAndOr = '*';
        }

        @H_404_6@private @H_404_6@void rbtn_OR_CheckedChanged(@H_404_6@object sender,EventArgs e)
        {
            odabirAndOr = '+';
        }

        @H_404_6@private @H_404_6@void rbtnNeizmjenjeni_CheckedChanged(@H_404_6@object sender,EventArgs e)
        {
            vrstaPretrazivanja = 'A';
        }

        @H_404_6@private @H_404_6@void rbtn_Rijecnici_CheckedChanged(@H_404_6@object sender,EventArgs e)
        {
            vrstaPretrazivanja = 'B';
        }

        @H_404_6@private @H_404_6@void rbtn_Fuzzy_CheckedChanged(@H_404_6@object sender,EventArgs e)
        {
            vrstaPretrazivanja = 'C';
        }

        @H_404_6@private @H_404_6@void button_Connect_Click(@H_404_6@object sender,EventArgs e)
        {
            @H_404_6@if (connectMe())
            {
                gb_unosPodataka.Enabled = @H_404_6@true;
                groupBox_Search.Enabled = @H_404_6@true;
                groupBox_Analysis.Enabled = @H_404_6@true;
                textBox_Database.Enabled = @H_404_6@false;
                textBox_IP.Enabled = @H_404_6@false;
                textBox_Port.Enabled = @H_404_6@false;
                textBox_Password.Enabled = @H_404_6@false;
                textBox_UserID.Enabled = @H_404_6@false;
                button_Connect.Enabled = @H_404_6@false;
                button_Disconnect.Enabled = @H_404_6@true;

                button_Pretrazi.BackColor = Color.SkyBlue;
                button_Disconnect.BackColor = Color.IndianRed;
                button_unosTekstaUBazu.BackColor = Color.MediumSeaGreen;
                button1.BackColor = Color.MediumSeaGreen;
                button_Connect.BackColor = Color.WhiteSmoke;
            }
        }

        @H_404_6@private @H_404_6@void button_Disconnect_Click(@H_404_6@object sender,EventArgs e)
        {
            gb_unosPodataka.Enabled = @H_404_6@false;
            groupBox_Search.Enabled = @H_404_6@false;
            groupBox_Analysis.Enabled = @H_404_6@false;
            textBox_Database.Enabled = @H_404_6@true;
            textBox_IP.Enabled = @H_404_6@true;
            textBox_Port.Enabled = @H_404_6@true;
            textBox_Password.Enabled = @H_404_6@true;
            textBox_UserID.Enabled = @H_404_6@true;
            button_Connect.Enabled = @H_404_6@true;
            button_Disconnect.Enabled = @H_404_6@false;

            button_Pretrazi.BackColor = Color.WhiteSmoke;
            button_Disconnect.BackColor = Color.WhiteSmoke;
            button_unosTekstaUBazu.BackColor = Color.WhiteSmoke;
            button1.BackColor = Color.WhiteSmoke;
            button_Connect.BackColor = Color.MediumSeaGreen;

            txt_Search.Text = "";
            linkLabel_Rezultat.Text = "";
            richTextBox1.Text = "";
            labelBrojac.Text = "";
        }


        @H_404_6@private @H_404_6@bool connectMe()
        {
            createConnString += "Server=" + textBox_IP.Text + ";Port=" + textBox_Port.Text + ";User Id=" +
                                textBox_UserID.Text + ";Password=" + textBox_Password.Text + ";Database=" +
                                textBox_Database.Text + ";";
            sqlQuerys.setTheKey(createConnString);
            pg.setConnectionString();
            pg.setConnection();
            @H_404_6@if (pg.openConnection())
            {
                MessageBox.Show("您已成功连接!");
                pg.closeConnection();
                @H_404_6@return @H_404_6@true;
            }
            @H_404_6@return @H_404_6@false;
        }

        @H_404_6@private @H_404_6@void button1_Click(@H_404_6@object sender,EventArgs e)
        {
            @H_404_6@string selectedTimestamp;
            selectedTimestamp = dateTimePicker_From.Value.ToString("dd-MM-yyyy hh:mm:ss") + " " +
                                dateTimePicker_To.Value.ToString("dd-MM-yyyy hh:mm:ss");
            @H_404_6@var analize = @H_404_6@new Analysis(selectedTimestamp,analysisType);
            analize.Show();
        }

        @H_404_6@private @H_404_6@void radioButton_Day_CheckedChanged(@H_404_6@object sender,EventArgs e)
        {
            analysisType = 'D';
        }

        @H_404_6@private @H_404_6@void radioButton_Hour_CheckedChanged(@H_404_6@object sender,EventArgs e)
        {
            analysisType = 'H';
        }
    }
}

sqlquerys.cs代码:

@H_404_6@using System.Collections.Generic;

namespace FullTextSearch
{
    @H_404_6@internal @H_404_6@class sqlquerys
    {
        @H_404_6@private @H_404_6@static @H_404_6@string giveMeTheKey;
        @H_404_6@private @H_404_6@static @H_404_6@int tempInt = 1;

        //设置连接字符串
        @H_404_6@public @H_404_6@void setTheKey(@H_404_6@string connString)
        {
            giveMeTheKey = connString;
            giveMeTheKey += "";
        }

        //将连接字符串存储在静态变量中
        @H_404_6@public @H_404_6@string getTheKey()
        {
            giveMeTheKey += "";
            @H_404_6@return giveMeTheKey;
        }


        @H_404_6@public @H_404_6@void setCounter()
        {
            tempInt = 1;
        }

        //根据AND和OR的选择分析字符串进行搜索
        @H_404_6@public @H_404_6@string createFunctionString(List<@H_404_6@string> searchList,@H_404_6@char selector)
        {
            @H_404_6@string TempString = "";
            @H_404_6@string[] TempField = @H_404_6@null;
            @H_404_6@int i = 0;
            @H_404_6@int j = 0;

            @H_404_6@foreach (@H_404_6@string searchStringInList @H_404_6@in searchList)
            {
                @H_404_6@if (j != 0)
                {
                    @H_404_6@if (selector == '+')
                        TempString = TempString + " | ";
                    @H_404_6@else @H_404_6@if (selector == '*')
                        TempString = TempString + " & ";
                }
                j = 1;
                TempField = splitListForInput(searchStringInList);
                TempString = TempString + "(";
                @H_404_6@foreach (@H_404_6@string justTempString @H_404_6@in TempField)
                {
                    @H_404_6@if (i != 0)
                    {
                        TempString = TempString + " & ";
                    }
                    TempString = TempString + justTempString;
                    i = 1;
                }
                TempString = TempString + ")";
                i = 0;
            }
            @H_404_6@return TempString;
        }

        //帮助方法
        @H_404_6@public List<@H_404_6@string> splitInputField(@H_404_6@string[] inputField)
        {
            @H_404_6@var unfinishedList = @H_404_6@new List<@H_404_6@string>();

            @H_404_6@foreach (@H_404_6@string splitString @H_404_6@in inputField)
            {
                unfinishedList.Add(splitString);
            }

            @H_404_6@return unfinishedList;
        }

        //帮助方法
        @H_404_6@public @H_404_6@string[] splitListForInput(@H_404_6@string inputString)
        {
            @H_404_6@string[] parsedList = @H_404_6@null;

            parsedList = inputString.Split(' ');

            @H_404_6@return parsedList;
        }

        //在Postgresql中创建ts功能功能,用于字典搜索
        @H_404_6@public @H_404_6@string createTsFunction(@H_404_6@string tsString)
        {
            @H_404_6@string tsHeadline = "";
            @H_404_6@string tsRank = "";
            @H_404_6@string tsFunction = "";

            tsHeadline = ",\n ts_headline(\"content\",to_tsquery('" + tsString + "')),\"content\"";
            tsRank = ",\n ts_rank(to_tsvector(\"content\"),to_tsquery('" + tsString + "')) rank";
            tsFunction = tsHeadline + tsRank;

            @H_404_6@return tsFunction;
        }

        //创建SQL查询依赖于选择哪种类型的搜索,也取决于AND或OR选择器
        @H_404_6@public @H_404_6@string createsqlString(List<@H_404_6@string> searchList,@H_404_6@char selector,@H_404_6@char vrstaPretrazivanja)
        {
            @H_404_6@string selectString = "";
            @H_404_6@string myTempString = "";
            @H_404_6@string TempString = "";
            @H_404_6@int i = 0;

            TempString = createFunctionString(searchList,selector);
            TempString = createTsFunction(TempString);
            selectString = "SELECT \"id\"" + TempString + "\nFROM \"texttable\" \nWHERE ";
            @H_404_6@if (vrstaPretrazivanja == 'A')
            {
                @H_404_6@foreach (@H_404_6@string myString @H_404_6@in searchList)
                {
                    @H_404_6@if (i == 0)
                    {
                        myTempString = myTempString + "\"content\" LIKE '%" + myString + "%' ";
                        i++;
                    }
                    @H_404_6@else
                    {
                        @H_404_6@if (selector == '*')
                            myTempString = myTempString + "\nAND \"content\" LIKE '%" + myString + "%' ";
                        @H_404_6@else @H_404_6@if (selector == '+')
                            myTempString = myTempString + "\nOR \"content\" LIKE '%" + myString + "%' ";
                    }
                }
            }
            @H_404_6@else @H_404_6@if (vrstaPretrazivanja == 'B')
            {
                @H_404_6@foreach (@H_404_6@string myString @H_404_6@in searchList)
                {
                    @H_404_6@string temporalString = "";
                    @H_404_6@string[] testingString = myString.Split(' ');

                    @H_404_6@for (@H_404_6@int k = 0; k < testingString.Length; k++)
                    {
                        @H_404_6@if (k != testingString.Length - 1)
                        {
                            temporalString += testingString[k] + " & ";
                        }
                        @H_404_6@else
                        {
                            temporalString += testingString[k];
                        }
                    }

                    @H_404_6@if (i == 0)
                    {
                        myTempString = myTempString + "to_tsvector(\"content\") @@ to_tsquery('english','" +
                                       temporalString + "')";
                        i++;
                    }
                    @H_404_6@else
                    {
                        @H_404_6@if (selector == '*')
                            myTempString = myTempString + "\nAND to_tsvector(\"content\") @@ to_tsquery('english','" +
                                           temporalString + "')";
                        @H_404_6@else @H_404_6@if (selector == '+')
                            myTempString = myTempString + "\nOR to_tsvector(\"content\") @@ to_tsquery('english','" +
                                           temporalString + "')";
                    }
                }
            }
            @H_404_6@if (vrstaPretrazivanja == 'C')
            {
                @H_404_6@foreach (@H_404_6@string myString @H_404_6@in searchList)
                {
                    @H_404_6@if (i == 0)
                    {
                        myTempString = myTempString + "\"content\" % '" + myString + "' ";
                        i++;
                    }
                    @H_404_6@else
                    {
                        @H_404_6@if (selector == '*')
                            myTempString = myTempString + "\nAND \"content\" % '" + myString + "' ";
                        @H_404_6@else @H_404_6@if (selector == '+')
                            myTempString = myTempString + "\nOR \"content\" % '" + myString + "' ";
                    }
                }
            }
            selectString = selectString + myTempString + "\nORDER BY rank DESC";

            @H_404_6@return selectString;
        }

        @H_404_6@public @H_404_6@string testIfEmpty(@H_404_6@string searchedText)
        {
            @H_404_6@string checkingIfEmpty = "SELECT * FROM \"analysisTable\" WHERE \"searchedtext\" =' " + searchedText + "'";
            @H_404_6@return checkingIfEmpty;
        }

        @H_404_6@public @H_404_6@string queryForAnalysis(@H_404_6@char analysisChoice)
        {
            @H_404_6@string myTestsql = "";
            @H_404_6@if (analysisChoice == 'H')
            {
                //这个查询是这样写的只是为了测试的目的,它需要改变
                myTestsql = "SELECT * FROM crosstab('SELECT CAST((\"searchedtext\") AS text) searchedText,"
                            +
                            " CAST(EXTRACT(HOUR FROM \"timeOfSearch\") AS int) AS sat,CAST(COUNT(*) AS int) AS broj FROM \"analysisTable\" GROUP BY \"searchedText\",sat"
                            +
                            " ORDER BY \"searchedtext\",sat','SELECT rbrSata FROM sat ORDER BY rbrSata') AS pivotTable (\"searchedText\" TEXT,t0_1 INT,t1_2 INT"
                            +
                            ",t2_3 INT,t3_4 INT,t4_5 INT,t5_6 INT,t6_7 INT,t7_8 INT,t8_9 INT,t9_10 INT,t10_11 INT,t11_12 INT,t12_13 INT"
                            +
                            ",t13_14 INT,t14_15 INT,t15_16 INT,t16_17 INT,t17_18 INT,t18_19 INT,t19_20 INT,t20_21 INT,t21_22 INT,t22_23 INT,t23_00 INT) ORDER BY \"searchedText\"";
                @H_404_6@return myTestsql;
            }
            @H_404_6@if (analysisChoice == 'D')
            {
                //这个查询是这样写的只是为了测试的目的,它需要改变
                myTestsql += "SELECT *FROM crosstab ('SELECT CAST((\"searchedtext\") AS text) AS searchedText,CAST(EXTRACT(DAY FROM \"dateOfSearch\") AS int) AS dan"
                             + ","
                             +
                             "dan ORDER BY \"searchedtext\",dan','SELECT rbrDana FROM dan ORDER BY rbrDana') AS pivotTable(\"searchedtext\" TEXT";
                @H_404_6@return myTestsql;
            }
            @H_404_6@return myTestsql;
        }

        //此方法用于解析日期
        @H_404_6@public @H_404_6@int[] parseForDates(@H_404_6@string date)
        {
            @H_404_6@string[] temp;
            @H_404_6@var tempInt = @H_404_6@new @H_404_6@int[3];
            temp = date.Split('-');
            @H_404_6@for (@H_404_6@int i = 0; i < 3; i++)
            {
                tempInt[i] = @H_404_6@int.Parse(temp[i]);
            }
            @H_404_6@return tempInt;
        }

        //此代码用于创建分析,它执行一些日期/时间操作,以便能够为选定的日期/时间创建分析。
        @H_404_6@public @H_404_6@string createsqlForDayAnalysis(@H_404_6@string dateFrom,@H_404_6@string dateTo)
        {
            @H_404_6@string insertIntoTempTable = "";
            @H_404_6@string dateTimeForAnalysis = "";
            @H_404_6@int[] tempFrom = parseForDates(dateFrom);
            @H_404_6@int[] tempTo = parseForDates(dateTo);

            //月份变更算法
            @H_404_6@while (tempFrom[0] != tempTo[0] || tempFrom[1] != tempTo[1])
            {
                @H_404_6@if (tempFrom[1] == tempTo[1])
                {
                    @H_404_6@if (tempFrom[0] != tempTo[0])
                    {
                        @H_404_6@for (@H_404_6@int i = tempInt + 1; tempFrom[0] + 2 < tempTo[0] + 2; i++)
                        {
                            insertIntoTempTable += "INSERT INTO \"dan\" VALUES (" + i + ");";
                            dateTimeForAnalysis += ",dd" + tempFrom[0] + tempFrom[1] + tempFrom[2] + " INT";
                            tempInt = i;
                            tempFrom[0]++;
                        }
                    }
                }
                @H_404_6@if (tempFrom[1] != tempTo[1])
                {
                    @H_404_6@if (tempFrom[1]%2 == 0 || tempFrom[1] == 7 || tempFrom[1] == 1)
                    {
                        @H_404_6@for (@H_404_6@int i = tempInt; tempFrom[0] < 31 && tempFrom[1] != tempTo[1]; i++)
                        {
                            insertIntoTempTable += "INSERT INTO \"dan\" VALUES (" + i + ");";
                            dateTimeForAnalysis += ",dd" + tempFrom[0] + tempFrom[1] + tempFrom[2] + " INT";
                            tempInt = i;
                            tempFrom[0]++;
                            @H_404_6@if (tempFrom[0] == 31)
                            {
                                tempFrom[1]++;
                                tempFrom[0] = 1;
                            }
                        }
                    }
                }
            }
            dateTimeForAnalysis += ") ORDER BY \"searchedtext\"";
            @H_404_6@return dateTimeForAnalysis + "#" + insertIntoTempTable;
        }
    }
}

Postgresql.cs代码:

@H_404_6@using System;
@H_404_6@using System.Windows.Forms;
@H_404_6@using Npgsql;
@H_404_6@using NpgsqlTypes;

namespace FullTextSearch
{
    @H_404_6@public @H_404_6@class Postgresql
    {
        @H_404_6@private @H_404_6@static @H_404_6@int tempInt = 1;
        @H_404_6@private @H_404_6@readonly sqlquerys sql = @H_404_6@new sqlquerys();
        @H_404_6@public NpgsqlConnection conn;
        @H_404_6@public @H_404_6@string connectionstring;
        @H_404_6@private @H_404_6@string newConnString;

        @H_404_6@public Postgresql()
        {
            setConnectionString();
            setConnection();
        }

        @H_404_6@public @H_404_6@void setConnectionString()
        {
            newConnString = sql.getTheKey();
            connectionstring = String.Format(newConnString);
            setConnection();
        }

        @H_404_6@public @H_404_6@void setConnection()
        {
            conn = @H_404_6@new NpgsqlConnection(connectionstring);
        }

        @H_404_6@public @H_404_6@bool openConnection()
        {
            @H_404_6@try
            {
                conn.Open();
                @H_404_6@return @H_404_6@true;
            }
            @H_404_6@catch
            {
                MessageBox.Show("Unable to connect! Check parameters!");
                @H_404_6@return @H_404_6@false;
            }
        }

        @H_404_6@public @H_404_6@void closeConnection()
        {
            conn.Close();
        }

        @H_404_6@public @H_404_6@void insertIntoTable(@H_404_6@string textToInsert,NpgsqlConnection nsqlConn)
        {
            @H_404_6@string MysqLString = "INSERT INTO \"texttable\" (\"content\") VALUES (@Param1)";

            @H_404_6@var myParameter = @H_404_6@new NpgsqlParameter("@Param1",NpgsqlDbType.Text);
            myParameter.Value = textToInsert;

            openConnection();

            @H_404_6@var myCommand = @H_404_6@new NpgsqlCommand(MysqLString,nsqlConn);
            myCommand.Parameters.Add(myParameter);
            myCommand.ExecuteNonQuery();

            closeConnection();
        }

        @H_404_6@public @H_404_6@void insertIntoAnalysisTable(@H_404_6@string textToInsert,NpgsqlConnection nsqlConn)
        {
            @H_404_6@string dateTime = DateTime.Now.ToString();
            @H_404_6@string[] temp;
            temp = dateTime.Split(' ');

            @H_404_6@string MysqLString =
                "INSERT INTO \"analysistable\" (\"searchedtext\",\"dateofsearch\",\"timeofsearch\") VALUES ('" +
                textToInsert + "','" + temp[0] + "'" + ",'" + temp[1] + "');";

            openConnection();

            @H_404_6@var myCommand = @H_404_6@new NpgsqlCommand(MysqLString,nsqlConn);
            myCommand.ExecuteNonQuery();

            closeConnection();
        }


        @H_404_6@public @H_404_6@void executeQuery(@H_404_6@string queryText,NpgsqlConnection nsqlConn)
        {
            openConnection();

            @H_404_6@var myCommand = @H_404_6@new NpgsqlCommand(queryText,nsqlConn);
            myCommand.ExecuteNonQuery();

            closeConnection();
        }

        @H_404_6@public @H_404_6@void createTempTable(NpgsqlConnection nsqlConn,@H_404_6@char analysisType,@H_404_6@string dateFrom,@H_404_6@string dateTo,@H_404_6@string splitMe)
        {
            @H_404_6@if (analysisType == 'H')
            {
                @H_404_6@string dropIfExists = "DROP TABLE IF EXISTS \"sat\";";
                @H_404_6@string createTempTable = "CREATE TABLE IF NOT EXISTS \"sat\" (rbrSata INT);";
                @H_404_6@string insertIntoTempTable = "";
                @H_404_6@for (@H_404_6@int i = 0; i < 24; i++)
                {
                    insertIntoTempTable += "INSERT INTO \"sat\" VALUES (" + i + ");";
                }

                openConnection();

                @H_404_6@var commandDrop = @H_404_6@new NpgsqlCommand(dropIfExists,nsqlConn);
                commandDrop.ExecuteNonQuery();

                @H_404_6@var commandCreate = @H_404_6@new NpgsqlCommand(createTempTable,nsqlConn);
                commandCreate.ExecuteNonQuery();

                @H_404_6@var commandInsert = @H_404_6@new NpgsqlCommand(insertIntoTempTable,nsqlConn);
                commandInsert.ExecuteNonQuery();

                closeConnection();
            }
            @H_404_6@else @H_404_6@if (analysisType == 'D')
            {
                @H_404_6@string dropIfExists = "DROP TABLE IF EXISTS \"dan\";";
                @H_404_6@string createTempTable = "CREATE TABLE IF NOT EXISTS \"dan\" (rbrDana INT);";
                @H_404_6@string insertIntoTempTable = splitMe;

                openConnection();

                @H_404_6@var commandDrop = @H_404_6@new NpgsqlCommand(dropIfExists,nsqlConn);
                commandInsert.ExecuteNonQuery();

                closeConnection();
            }
        }
    }
}

PostGresql sql脚本:

@H_404_6@CREATE @H_404_6@TABLE @H_404_6@public.analysistable ( id @H_404_6@integer @H_404_6@NOT @H_404_6@NULL @H_404_6@DEFAULT nextval('analysistable_id_seq'::regclass),searchedtext text @H_404_6@COLLATE pg_catalog."default" @H_404_6@NOT @H_404_6@NULL,dateofsearch @H_404_6@date @H_404_6@NOT @H_404_6@NULL,timeofsearch @H_404_6@time without @H_404_6@time @H_404_6@zone @H_404_6@NOT @H_404_6@NULL,@H_404_6@CONSTRAINT analysistable_pkey @H_404_6@PRIMARY @H_404_6@KEY (id) ) @H_404_6@WITH ( OIDS = @H_404_6@FALSE ) TABLESPACE pg_default;

@H_404_6@ALTER @H_404_6@TABLE @H_404_6@public.analysistable OWNER @H_404_6@to king;
@H_404_6@CREATE @H_404_6@TABLE @H_404_6@public.texttable ( id @H_404_6@integer @H_404_6@NOT @H_404_6@NULL @H_404_6@DEFAULT nextval('texttable_id_seq'::regclass),content text @H_404_6@COLLATE pg_catalog."default" @H_404_6@NOT @H_404_6@NULL,@H_404_6@CONSTRAINT texttable_pkey @H_404_6@PRIMARY @H_404_6@KEY (id) ) @H_404_6@WITH ( OIDS = @H_404_6@FALSE ) TABLESPACE pg_default;

@H_404_6@ALTER @H_404_6@TABLE @H_404_6@public.texttable OWNER @H_404_6@to king;

运行结果如图:




猜你在找的Postgre SQL相关文章