ラベル SQL Server の投稿を表示しています。すべての投稿を表示

2015-03-05

十個PostgreSQL比SQL Server好的理由

Ten Reasons PostgreSQL is better than SQL Server

原文出處: Ten Reasons PostgreSQL is Better Than SQL Server
原文作者: Jeremiah Peschka
原文發布時間: Dec. 06, 2011

為什麼會有人想要用PostgreSQL取代SQL Server？當你在選擇如何儲存你的資料之時會有很多因素需要考慮。有時候你需要看一些比標準選擇更深層一些的部分，並考慮一些新的事情。如果你是要開始一個新的專案，你應該將你的資料儲存在哪裡？以下列出十個理由，告訴你為什麼也許應該要考慮用PostgreSQL而不是SQL Server。

每年版本發布 Release Every Year

讓我們面對問題吧，等某個產品的三到五年的更新週期去加入新的功能是件痛苦的事。我不想不斷地學習新功能，但是另一方面，我又不想為了解決業務上的重大問題而自己寫黑客解決方案，因為我知道某些東西終究會沿著管線下來，但是在我自己實踐他之前我又不能等上個好幾年。迅捷發布週期保證PostgreSQL的開發團隊可以快速地將用戶需求的功能交出來，並且持續的改進。

從9.0版開始，PostgreSQL的發布週期已經切換為年度週期。在那之前，PostgreSQL總是在新功能完成時發布釋出。縱觀維基百科上的主要版本的發布時間，我們可以看到每18個月會推出新的主要版本。對軟體產品來說，18個月的發布週期並不差，比起一個資料庫之類的重大任務來說已經是短得多了。

真．序列化 True Serialization

快照隔離(snapshot isolation)保證同一次的交易處理中的所有的讀取動作都會對應到一致的資料快照內容。另外，一次的交易處理應當只在當次的快照之後的資料變更沒有起衝突的時候進行提交。但不幸的是，快照允許異常狀況存在。那可能會發生一種狀況，兩筆有效的交易發生，讓資料庫留在一個不一致的狀態上 — 資料庫不會符合它自己的資料完整性規則。

序列化快照隔離(serializable snapshot isolation)在9.1版中被加進了PostgreSQL中。SSI模擬了嚴格的序列化執行動作 - 交易行為會以一個接著一個的方式被執行。如果中間有衝突發生，甚至是一個潛在的衝突，資料庫引擎會將一個錯誤丟回給呼叫者(呼叫者將留下如何處理下一步的指示)。

序列化快照隔離聽起來很痛。但厲害的是它提供讓資料庫去擁有更高等級的一致性控制方案。應用程式可以在假設資料修改會失敗的前提下進行開發，隨後重試失敗的交易。真正的好處是寫得好的城市可以預防資料不一致，並且維持所有的操作都應該是符合期望的形象。

理智的預設值，荒謬的調校 Sane Defaults, Ridiculous Tuning

好吧，讓我們保持公平，PostgreSQL附帶了一些荒謬的保守性共享記憶體設定。其他大部分的PostgreSQL設定都是保守的，但是一般來說足以滿足大多數普通的工作負載。大多數人在佈署PostgreSQL的時候將不需對PostgreSQL做太多變更(也許只是在開始時將shared_buffers增加到整體記憶體的25%)。

一旦PostgreSQL安裝完成開始運行，有一些設定還是可以變更。最好的部分是，雖然，是在伺服器，資料庫，用戶，或甚至是單獨查詢級別中可以對其大部分的設定進行變更。擁有混合工作負載的伺服器是十分常見的 - 大部分在該伺服器上的活動都是在基本的CRUD上執行，但是活動中的百分之幾的小部份，用來做報告的那些，需要調整為高一些的優先度。與其將獨立報告移到分離的區域(不管是分離的伺服器，資料庫，還是相同資料庫中的分離資源池)去執行，我們可以藉由使用適當的參數簡單地調校小部分的查詢，包括分配要用來做排序(sort)與聯結(join)的記憶體分配。

無紀錄資料表 Unlogged Tables

你是否對試著取得批量插入作業的少量記錄感到不快？我也是。與其嘗試各種不同的方法去讓某些資料表的紀錄少量化，PostgreSQL讓我們選擇可以創建一個無紀錄資料表 - 只要在創建資料表create table語句中簡單地直接加上UNLOGGED字句，一切就都準備好了。

無紀錄資料表會繞過寫入起始紀錄；他們沒有崩潰防護，但是他們就是可以執行的飛快。無紀錄資料表中的資料將會在伺服器崩潰或是不明關機後被截斷，不然他們就是一直在那裏。他們也會被排除在備用伺服器的備份以外。這讓無紀錄資料表成為ETL或是其他資料操作程序的理想選項，那會讓他們易於重複使用原資料。

地理位置的KNN...以及更多 KNN for Geospatial… and More

對，我有聽過這消息，關於SQL Server將會很快地擁有這個功能，但是PostgreSQL已經擁有了。如果K最近鄰居(K Nearest Neighbor)搜尋對你的業務來說非常重要，你已經走過一些痛苦的過去，為了要讓他在你的RDBMS裡面起作用。或者你已經放棄並且在其他地方實作解決方案。我不會怪你 - 地理位置查詢是好，但是沒有KNN功能會讓人想死。

PostgreSQL的KNN查詢在特定索引(index)類型上動作(在PostgreSQL中有許多種索引類型)。你不只可以使用KNN查詢去找到前五個離你最近的Dairy Queen(*連鎖冰品店)，你還可以使用KNN搜尋去找其他的資料類型。你完全有可能藉由執行KNN搜尋去找到10個最接近"冰淇淋"的語句。

KNN搜尋能力使得PostgreSQL在任何尋找地理位置查詢功能的人眼中是個有力的競爭者。而追加的柔軟性讓PostgreSQL在其他同種搜尋導向的應用程式中處於領導地位。

交易管理同步複製 Transcation-Controlled Synchronous Replication

要保持你的資料庫的另一個備份的最簡單的方法之一，就是用某種資料庫複製(database replication)。SQL Server DBA將主要使用交易紀錄性的複製(transactional replication) - 一個專門的代理程序會讀取SQL Server的紀錄，蒐集重要的指令，然後將其傳送給應用他們的訂戶。

PostgreSQL的內建複製，比起SQL Server的複製來說，更接近SQL Server的鏡像(PostgreSQL的複製是個可讀性的待機)。紀錄活動將會固定在主要機上，然後被傳輸給二號機。那個動作可以是同步也可以是非同步的。直到PostgreSQL 9.1為止，複製是一個全友或是全無的動作 - 每一筆交易不是同步就是非同步的。開發者可以藉由單次交易的synchronous_replication設定值去設定一個特定的交易。這很重要，因為它讓我們在除錯時可以寫入大量的複製資料進紀錄資料表而不會受到同步提交在寫入紀錄資料表的效能影響。

每當我們在開發應用程式時有更多的選項時，我都很快樂。

可寫的CTE Writeable CTEs

CTE對讀取來說很好，但是如果我需要用他們來做一些更複雜的動作，那會發生一些其他的問題。一個例子會讓他更簡單一些。比方說我想刪除過時的資料，但是我想要將它儲存在一個封存資料表中。要在SQL Server上這麼做，最簡單的方法(從開發的觀點上)就是提升我的隔離級別(isolation level)到至少快照的等級，如果不是序列化(serializable)的，並且使用隔離等級去保證資料不會被變更。我也可以讀取將要被刪除的註解的PK值，將它放在一個暫存資料表中，並多次參照它。

兩種方法都可行，但是兩種方法都有問題。第一種方法需要程式碼在特定隔離等級上執行。這讓目前的特定設定會失效。程式碼也可以從特定程序中被複製出來，並在SSMS中運行，會導致潛在的異常，會有少數的幾行被刪除且不存在於封存之中。對垃圾留言來說不是個大問題，但是在其他的狀況中這可能會是大問題。第二種方法並不是十分差勁，而且他也沒有錯，只是它涉及額外的程式碼噪音。暫存資料表對解決我們的問題來說不是必要的，而且它是在處理不同隔離級別時產生的副產品。

PostgreSQL有不同的方法來解決這個問題：可寫式的CTE。CTE是用T-SQL中被打造的同樣方式來打造。不同的是，當我們使用PostgreSQL，資料可以在CTE之中被更改。然後輸出資料可以就像其他CTE輸出一樣被再度利用：

CREATE TABLE old_text_data (text_data text); 

WITH deleted_comments AS ( 
  DELETE FROM comments 
  WHERE comment_text LIKE '%spam%' 
  RETURNING comment_id, email_address, created_at, comment_text 
) 
INSERT INTO spam_comments 
SELECT * 
FROM deleted_comments

這可以結合預設值(default value)，觸發器(trigger)，或是任何其他資料修改方式去打造非常豐富的ETL鏈。在表面之下它可能就像我們在SQL Server上做的事一樣，但是好處是它簡潔。

擴充 Extensions

有曾想過要在SQL Server上增加一些功能嗎？要如何保持這些功能是最新版的？這也許對DBA來說是個大問題。當你在橫跨生產環境中執行管理用腳本時要跳過一台伺服器是十分簡單的事。更進一步，你怎麼知道你已經安裝的是哪個版本？

PostgreSQL擴充功能網路(PostgreSQL Extension Network, PGXN)是一個外部功能的集中發放點。它是開源PostgreSQL書庫的一個可信賴的來源 - 不會包含任何可疑的二進位程式。再加上，在PGXN上的任何東西都有版本編號。當更新時，PGXN會有效地提供，擴充功能將會為你關注更新途徑 - 它知道如何確認自己是最新的。

有一些關於排名的擴充功能像是 K均值叢類(K-Means clustering)、甲骨文兼容功能(Oracle compatibility functions)、Amazon S3遠端查詢(remote queries to Amazon S3)。

將這些功能推出並放進擴充功能中，讓開發者與DBA易於打造客製化封包，讓它看起來且動作就像是PostgreSQL的核心功能，但卻不需試著在PostgreSQL發布程序中取得這些包裝。這些包裝可以之後再獨立開發，進而提升他們自己的效率，並提供那些無法符合PostgreSQL核心團隊的發布計畫的複雜功能。簡單地說，這有一個環繞PostgreSQL的健全生態系統。

豐富的時間資料類別Rich Temporal Data Types

我最喜歡的PostgreSQL的功能之一就是它的時間資料類別的豐富支援。當然，SQL Server 2008最終為SQL Server帶來了一些成熟的時間資料支援，但是那還是塊十分貧脊的土地。對時間資料的強力支援在很多工業中是關鍵議題，而且不幸地是，在SQL Server中還有許多工作在繼續進行，為了解決要SQL Server對時間資料支援上的限制。

PostgreSQL引進了時區智能處理。加上了對ISO 8601標準(1999-01-08 04:05:06 -8:00)的支援，PostgreSQL支持用縮寫(PST)或是指定位置識別子(America/Tijuana)來識別時區。縮寫會對應到從UTC的固定偏移量，而位置識別子會加上夏令時間的規則支援。

在時區柔軟性之上，PostgreSQL有個interval資料類別。interval資料類別能夠在14位精度上儲存最大178,000,000年的間隔。interval可以量測時間，以一年的間隔內測出比微秒還要小的精細度。

排除約束 Exclustion Constraints

你是否有嘗試過用SQL Server寫任何一種排程功能？如果你有，你將會知道當你有業務上的需求像是"兩個人不能在同一時間占用同一間會議室"，你就會知道這很難執行程式碼，而且通常會需要資料庫中的額外動作。有很多方法可以完全透過在應用程式層別程式碼來實現這個功能，但沒有一個會讓用戶與開發者開心。

PostgreSQL 9.0引進了排除約束在欄位之中。換句話說，我們定義資料表並加上追加的約束，那包括一些檢查(check)，且至少要有一項檢查是假(false)。排除約束藉由索引的罩門下受支援，所以這些動作可以像是在我們的磁碟以及我們所設計的索引一樣快。你可以在時空間結合資料上使用排除約束，確定在同一個時間點不同的人不能預約同一間會議室或是區塊不會重疊。

在2010 PGCon大會中有一份發表資料是關於排除約束的細節部分。雖然沒有視頻，但是幻燈片已經足以讓他們展示足夠的案例與解釋幫助你開始。

加分功能 - 原價 Bonus Feature - Cost

它是免費的。所有的功能都是。PostgreSQL沒有版本分類(edition) - 功能總是存在資料庫中。一些公司會提供商業支援，他們之中有些甚至會提供追加的閉源功能，但是核心的PostgreSQL資料庫一直都是可用的，一直都是免費的，一直都擁有相同的功能。

讓我們開始吧 Getting Started

想要開始用PostgreSQL嗎？跳到下載頁面並下載你選擇的平台的副本。如果你想要更多細節，文件都已經準備好了而且良好撰寫，或是你可以查看維基上的教程。

2013-04-05

Connect to Databases in C#

今回はC#でSQLiteやMySQLに繋ぎ方法を記録する。

一言で言うと、SQL Serverとの繋ぎはほぼ似ている。
ただし使用するメソッド／オブジェクトをSqlXxxからSQLiteXxxあるいはMySqlXxxに変わるだけ。

さて、本題に入る。先ずは基本のSQL serverに行こう。
することは全部同じ。

データベースに繋ぎ（連結する）。
CarというTableを作成。
テーブルCarにデータを入れる。
データを出力し、連結をクローズする。

using System;
using System.Data.SqlClient;

class SQLTest01
{
    static void Main()
    {
        /* Set the connection string
         * With the setting below:
         * user id : the userid for SQL server
         * password or pwd : the password of user
         * database : the database you want to connect
         */
        string cs = "user id=testuser;" +
            "password=testpwd;server=localhost;" +
            "Trusted_Connection=yes;" +
            "database=dbTest; " +
            "connection timeout=30";

        // Connection to Database
        // with the new "using" garbage collection 
        using (SqlConnection con = new SqlConnection(cs))
        {
            // Open connection;
            con.Open();

            // Create the Table
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;

                cmd.CommandText = "DROP TABLE IF EXISTS Cars";
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"CREATE TABLE Cars(Id INTEGER PRIMARY KEY, 
                    Name TEXT, Price INT)";
                cmd.ExecuteNonQuery();
            }

            // Put Datas into Table
            using(SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;

                // do with Prepare();
                cmd.CommandText = "INSERT INTO Cars(Id, Name, Price) VALUES(@Id, @Name, @Price)";
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@Id", 1);
                cmd.Parameters.AddWithValue("@Name", "Audi");
                cmd.Parameters.AddWithValue("@Price", 52642);
                cmd.ExecuteNonQuery();
                cmd.Parameters.AddWithValue("@Id", 2);
                cmd.Parameters.AddWithValue("@Name", "Mercedes");
                cmd.Parameters.AddWithValue("@Price", 57127);
                cmd.ExecuteNonQuery();

                // do with Standard SQL command
                cmd.CommandText = "INSERT INTO Cars VALUES(3,'Skoda',9000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(4,'Volvo',29000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(5,'Bentley',350000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(6,'Citroen',21000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(7,'Hummer',41400)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(8,'Volkswagen',21600)";
                cmd.ExecuteNonQuery();
            }

            // Read Datas with DataReader
            string stm = "SELECT * FROM Cars LIMIT 5";
            using (SqlCommand cmd = new SqlCommand(stm,con))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        Console.WriteLine(rdr.GetInt32(0) + " " + rdr.GetString(1) + " " + rdr.GetInt32(2));
                    }
                }
            }

            // Close connection;
            con.Close();
        }
    }
}

続き、似ている内容で、SQLiteでしましょう。
注意してほしい点は

事前にSystem.Data.SQLiteのDLLファイルをプロジェクトのフォルダ下のExternalsにコピーしておくこと。また、プロジェクト設定でそれに参照するように設定して下さい。
DLLファイルを持っていない場合、SQLite.NET公式サイトあるいはSQLite公式サイトより探して下さい。無償で手に入れるはず。

using System;
using System.Data.SQLite;

class SQLTest01
{
    static void Main()
    {
        /* Set the connection string
         * With the setting below:
         * user id : the userid for SQL server
         * password or pwd : the password of user
         * database : the database you want to connect
         */
        string cs = "URI=file:test.db";

        // Connection to Database
        // with the new "using" garbage collection 
        using (SQLiteConnection con = new SQLiteConnection(cs))
        {
            // Open connection;
            con.Open();

            // Create the Table
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                cmd.Connection = con;

                cmd.CommandText = "DROP TABLE IF EXISTS Cars";
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"CREATE TABLE Cars(Id INTEGER PRIMARY KEY, 
                    Name TEXT, Price INT)";
                cmd.ExecuteNonQuery();
            }

            // Put Datas into Table
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                cmd.Connection = con;

                // do with Prepare();
                cmd.CommandText = "INSERT INTO Cars(Id, Name, Price) VALUES(@Id, @Name, @Price)";
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@Id", 1);
                cmd.Parameters.AddWithValue("@Name", "Audi");
                cmd.Parameters.AddWithValue("@Price", 52642);
                cmd.ExecuteNonQuery();
                cmd.Parameters.AddWithValue("@Id", 2);
                cmd.Parameters.AddWithValue("@Name", "Mercedes");
                cmd.Parameters.AddWithValue("@Price", 57127);
                cmd.ExecuteNonQuery();

                // do with Standard SQL command
                cmd.CommandText = "INSERT INTO Cars VALUES(3,'Skoda',9000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(4,'Volvo',29000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(5,'Bentley',350000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(6,'Citroen',21000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(7,'Hummer',41400)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(8,'Volkswagen',21600)";
                cmd.ExecuteNonQuery();
            }

            // Read Datas with DataReader
            string stm = "SELECT * FROM Cars LIMIT 5";
            using (SQLiteCommand cmd = new SQLiteCommand(stm, con))
            {
                using (SQLiteDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        Console.WriteLine(rdr.GetInt32(0) + " " + rdr.GetString(1) + " " + rdr.GetInt32(2));
                    }
                }
            }

            // Close connection;
            con.Close();
        }

        Console.WriteLine("Press ENTER to continue...");
        Console.ReadLine();
    }
}

最後、MySQL（今回はローカルを例にする）でしましょう。
SQLiteの時と同じ、System.Data.MySQLのDLLを取得して下さい。SQLiteの時と同じ、MySQLの公式サイトより無償で手に入れるはず。

using System;
using MySql.Data.MySqlClient;

class SQLTest01
{
    static void Main()
    {
        /* Set the connection string
         * With the setting below:
         * user id : the userid for SQL server
         * password or pwd : the password of user
         * database : the database you want to connect
         */
        string cs = "user id=testuser;" +
            "password=testpwd;server=localhost;" +
            "database=testDB; " +
            "connection timeout=30";

        // Connection to Database
        // with the new "using" garbage collection 
        using (MySqlConnection con = new MySqlConnection(cs))
        {
            // Open connection;
            con.Open();

            // Create the Table
            using (MySqlCommand cmd = new MySqlCommand())
            {
                cmd.Connection = con;

                cmd.CommandText = "DROP TABLE IF EXISTS Cars";
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"CREATE TABLE Cars(Id INTEGER PRIMARY KEY, 
                    Name TEXT, Price INT)";
                cmd.ExecuteNonQuery();
            }

            // Put Datas into Table
            using (MySqlCommand cmd = new MySqlCommand())
            {
                cmd.Connection = con;

                // do with Prepare();
                cmd.CommandText = "INSERT INTO Cars(Id, Name, Price) VALUES(1, 'Audi', 52642)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars(Id, Name, Price) VALUES(2, 'Mercedes', 57127)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(3,'Skoda',9000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(4,'Volvo',29000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(5,'Bentley',350000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(6,'Citroen',21000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(7,'Hummer',41400)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(8,'Volkswagen',21600)";
                cmd.ExecuteNonQuery();
            }

            // Read Datas with DataReader
            string stm = "SELECT * FROM Cars LIMIT 5";
            using (MySqlCommand cmd = new MySqlCommand(stm, con))
            {
                using (MySqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        Console.WriteLine(rdr.GetInt32(0) + " " + rdr.GetString(1) + " " + rdr.GetInt32(2));
                    }
                }
            }

            // Close connection;
            con.Close();
        }
    }
}

登録: 投稿 (Atom)

桂馬の学習ノード

SyntaxHighlighter