1pcQEU1Y

1using System;
2using System.Collections.Generic;
3using System.ComponentModel;
4using System.Data;
5using System.Data.Common;
6using System.Data.SQLite;
7using System.Drawing;
8using System.IO;
9using System.Text;
10using System.Windows.Forms;
11
12
13namespace sample.histories.diff {
14
15
16    /// <summary>
17    ///
18    /// </summary>
19    public partial class HistoriyDiffForm : Form {
20        /// <summary>
21        ///
22        /// </summary>
23        public HistoriyDiffForm() {
24            InitializeComponent();
25        }
26
27
28        /// <summary>
29        ///
30        /// </summary>
31        /// <param name="sender"></param>
32        /// <param name="ergs"></param>
33        private void btnAppend_Click(object sender, EventArgs ergs) {
34            int seq = 0;
35            /* 
36             * URL のリストを作成する
37             * ===========================================================================
38             * tbPasteArea.Text の中身は、\n で区切られた URL の束です。
39             * Opera の履歴を全て選択し、コピーしたものです。
40             */
41            string[] urls = tbPasteArea.Text.Split( new string[] { Environment.NewLine }, StringSplitOptions.None );
42
43            using ( DbConnection connection = getConnection( "visit-history.db" ) ) {
44                if ( connection.State != ConnectionState.Open )
45                    connection.Open();
46
47                DbCommand command = connection.CreateCommand();
48                DbTransaction transaction = null;
49
50                // histroies というテーブルがあるか調べます。
51                command.CommandText = "select count(*) from sqlite_master where type ='table' and name = 'histories'";
52                int exists = int.Parse( command.ExecuteScalar().ToString() );
53
54                if ( exists == 0 ) {
55                    // テーブルが存在しなければ create table 文を打ってテーブルを作成します。
56                    command.CommandText = "create table histories ( seq int primary key, url text not null )";
57                    command.ExecuteNonQuery();
58
59                    transaction = connection.BeginTransaction();
60                    try {
61                        seq = 0;
62
63                        foreach ( string url in urls ) {
64                            command.CommandText = string.Format( "insert into histories (seq, url) values ({0}, '{1}')", seq, url );
65                            command.ExecuteNonQuery();
66
67                            ++seq;
68                        }
69                        transaction.Commit();
70                    } catch ( Exception before_except ) {
71
72                        try {
73                            error( before_except );
74                            transaction.Rollback();
75                        } catch ( Exception after_except ) {
76                            error( after_except );
77                        }
78                        tbPasteArea.Text = string.Empty;
79
80                        return;
81
82                    }
83                } else {
84                    command.CommandText = "create temporary table tmp_histories ( seq int primary key, url text not null )";
85                    command.ExecuteNonQuery();
86
87                    transaction = connection.BeginTransaction();
88                    try {
89                        seq = 0;
90
91                        foreach ( string url in urls ) {
92                            command.CommandText = string.Format( "insert into tmp_histories (seq, url) values ({0}, '{1}')", seq, url );
93                            command.ExecuteNonQuery();
94
95                            ++seq;
96                        }
97                        transaction.Commit();
98                    } catch ( Exception before_except ) {
99
100                        try {
101                            error( before_except );
102                            transaction.Rollback();
103                        } catch ( Exception after_except ) {
104                            error( after_except );
105                        }
106                        tbPasteArea.Text = string.Empty;
107
108                        return;
109
110                    }
111
112                    // histories テーブルと、テンポラリテーブルの diff をとります。
113                    // sqlite3 ではグルーピングのための括弧を使えません。
114                    command.CommandText = string.Join( " ", new string[] {
115                        "select url from histories",
116                        "except",
117                        "select url from tmp_histories",
118                        "union all",
119                        "select url from histories",
120                        "except",
121                        "select url from tmp_histories"
122                        } );
123                    // reader 起動中は insert 文を評価できないので、List に貯めておきます。
124                    List<string> li = new List<string>();
125
126                    using ( DbDataReader reader = command.ExecuteReader() ) {
127                        if ( reader.HasRows ) {
128                            while ( reader.Read() ) {
129                                li.Add( reader["url"].ToString() );
130                            }
131                        }
132                    }
133
134                    transaction = connection.BeginTransaction();
135                    try {
136                        // 現在の histories の seq の最大値を求めます。
137                        command.CommandText = "select max(seq) from histories";
138                        seq = int.Parse( command.ExecuteScalar().ToString() ) + 1;
139                        // 重複していない URL を histories に保存します。
140                        foreach ( string url in li ) {
141                            command.CommandText = string.Format( "insert into histories (seq, url) values ({0}, '{1}')",
142                                                                 seq,
143                                                                 url
144                                                                 );
145                            command.ExecuteNonQuery();
146
147                            ++seq;
148                        }
149                    } catch ( Exception before_except ) {
150                        try {
151                            error( before_except );
152                            transaction.Rollback();
153                        } catch ( Exception after_except ) {
154                            error( after_except );
155                        }
156                        tbPasteArea.Text = string.Empty;
157                        return;
158                    }
159                }
160                tbPasteArea.Text = string.Empty;
161                MessageBox.Show( "処理が完了しました。", "通知" );
162            }
163        }
164
165
166        /// <summary>
167        /// 
168        /// </summary>
169        /// <param name="filename"></param>
170        /// <returns></returns>
171        DbConnection getConnection(string filename) {
172            return new SQLiteConnection( string.Format( "Data Source=./{0}", filename ) );
173        }
174
175
176        /// <summary>
177        ///
178        /// </summary>
179        /// <param name="e"></param>
180        void error(Exception e) {
181            using ( StreamWriter writer = new StreamWriter( File.Open( "stderr.txt", FileMode.Append ) ) ) {
182                writer.WriteLine( e.GetType().ToString() );
183                writer.WriteLine( e.Source );
184                writer.WriteLine( e.Message );
185                writer.WriteLine( e.StackTrace );
186                writer.WriteLine( e.TargetSite );
187                writer.WriteLine( "----------------------------------------------------------------------------------------" );
188            }
189        }
190
191
192    }
193
194
195}