예제 #1
0
        private void button1_Click(object sender, EventArgs e)
        {
            string sFile = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(Application.ExecutablePath), "EmailDB.sqlite3");

            if (System.IO.File.Exists(sFile))
            {
                System.IO.File.Delete(sFile);
            }
            var sql_con = new System.Data.SQLite.SQLiteConnection("Data Source=" + sFile + ";Version=3");

            sql_con.Open();
            sql_con.EnableExtensions(true);
            sql_con.LoadExtension("SQLite.Interop.dll", "sqlite3_fts5_init"); // Or "SQLite.Interop.dll" as you need.
            var sql_cmd = sql_con.CreateCommand();

            sql_cmd.CommandText = @"CREATE TABLE TMail  
                                    (MailId INTEGER PRIMARY KEY,
                                        SentOn  TEXT,
                                        ReceivedOn TEXT,
                                        Subject TEXT,
                                        Body TEXT,
                                        SenderEmailAddress  TEXT,
                                        SenderName TEXT,
                                        CTo TEXT,
                                        CC TEXT,
                                        AttachmentNames TEXT,
                                        Categories TEXT,
                                        Importance  TEXT,
                                        FlagRequest TEXT,
                                        CTag TEXT,
                                        LastWriteTime TEXT,
                                        FullFileName TEXT)";
            sql_cmd.ExecuteNonQuery();
            sql_cmd.CommandText = @"CREATE VIRTUAL TABLE VMail 
                                    USING fts5(MailId,
                                    SentOn,
                                    ReceivedOn,
                                    Subject,
                                    Body,
                                    SenderEmailAddress,
                                    SenderName,
                                    CTo,
                                    CC,
                                    AttachmentNames,
                                    Categories,
                                    Importance,
                                    FlagRequest,
                                    CTag,
                                    LastWriteTime,
                                    FullFileName)";
            sql_cmd.ExecuteNonQuery();
            sql_cmd.CommandText = @"CREATE TRIGGER TrigDelete
                                    AFTER DELETE
                                        ON TMail
                                    FOR EACH ROW
                                    BEGIN
                                        DELETE FROM VMail
                                        WHERE MailId = old.MailId;
                                    END;";
            sql_cmd.ExecuteNonQuery();
            sql_cmd.CommandText = @"CREATE TRIGGER TrigInsert
                                    AFTER INSERT
                                        ON TMail
                                    FOR EACH ROW
                                    BEGIN
                                        INSERT INTO VMail (
                                            MailId,
                                            SentOn,
                                            ReceivedOn,
                                            Subject,
                                            Body,
                                            SenderEmailAddress,
                                            SenderName,
                                            CTo,
                                            CC,
                                            AttachmentNames,
                                            Categories,
                                            Importance,
                                            FlagRequest,
                                            CTag,
                                            LastWriteTime,
                                            FullFileName)
                                     VALUES (
                                            new.MailId,
                                            new.SentOn,
                                            new.ReceivedOn,
                                            new.Subject,
                                            new.Body,
                                            new.SenderEmailAddress,
                                            new.SenderName,
                                            new.CTo,
                                            new.CC,
                                            new.AttachmentNames,
                                            new.Categories,
                                            new.Importance,
                                            new.FlagRequest,
                                            new.CTag,
                                            new.LastWriteTime,
                                            new.FullFileName);
                                    END;";
            sql_cmd.ExecuteNonQuery();
            sql_cmd.CommandText = @"CREATE TRIGGER TrigUpdate
                                   AFTER UPDATE
                                        ON TMail
                                   FOR EACH ROW
                                   BEGIN
                                        UPDATE VMail
                                        SET SentOn = new.SentOn,
                                            ReceivedOn = new.ReceivedOn,
                                            Subject = new.Subject,
                                            Body = new.Body,
                                            SenderEmailAddress = new.SenderEmailAddress,
                                            SenderName = new.SenderName,
                                            CTo = new.CTo,
                                            CC = new.CC,
                                            AttachmentNames = new.AttachmentNames,
                                            Categories = new.Categories,
                                            Importance = new.Importance,
                                            FlagRequest = new.FlagRequest,
                                            CTag = new.CTag,
                                            LastWriteTime = new.LastWriteTime,
                                            FullFileName = new.FullFileName
                                        WHERE MailId = old.MailId;
                                    END;";
            sql_cmd.ExecuteNonQuery();

            sql_cmd.CommandText = @"CREATE INDEX SentOnIdx ON TMail(SentOn);";
            sql_cmd.ExecuteNonQuery();

            sql_cmd.CommandText = @"CREATE INDEX ReceivedOnIdx ON TMail(ReceivedOn);";
            sql_cmd.ExecuteNonQuery();

            sql_cmd.CommandText = @"CREATE INDEX LastWriteTimeIdx ON TMail(LastWriteTime);";
            sql_cmd.ExecuteNonQuery();

            sql_cmd.CommandText = @"CREATE INDEX SenderEmailAddressIdx ON TMail(SenderEmailAddress);";
            sql_cmd.ExecuteNonQuery();

            sql_cmd.CommandText = @"CREATE INDEX FullFileNameIdx ON TMail(FullFileName);";
            sql_cmd.ExecuteNonQuery();

            sql_cmd.CommandText = @"CREATE TABLE TTag (TagId INTEGER PRIMARY KEY, TagName TEXT, IsDisable INTEGER)";
            sql_cmd.ExecuteNonQuery();

            sql_cmd.CommandText = @"CREATE TABLE TRelation (RelationId INTEGER PRIMARY KEY, RTagId INTEGER, RMailId INTEGER,
                                    FOREIGN KEY(RTagId) REFERENCES TTag(TagId)
                                    FOREIGN KEY(RMailId) REFERENCES TMail(MailId))";
            sql_cmd.ExecuteNonQuery();

            sql_cmd.CommandText = @"CREATE INDEX TagIdIdx ON TRelation(RTagId);";
            sql_cmd.ExecuteNonQuery();

            sql_con.Close();
            MessageBox.Show("done!");
        }