PowerShell でデータベースのすべてのデータをエクスポートする

2010年9月15日 — さかぽん
PowerShell では .NET のライブラリを呼び出せるとのことなので、その記述方法を学ぶために、
データベースのすべてのデータを CSV ファイルにエクスポートするスクリプトをサンプルとして作成してみました。
テーブル名をあらかじめ指定しておかなければなりませんでしたが、今回はテーブル名を動的に取得します。
 
アプローチとしては、まず C# で作成し、そのコードを PowerShell スクリプトに置き換えました。
テーブル名はデータベースのメタデータから取得します。
 
C# のコードはこちらです。

string instanceName = @".\SQLEXPRESS";
string databaseName = "Tfs_DefaultCollection";
string outDirPath = "Output";

string commandText = "select [name] from sys.tables order by [name]";
string connectionString = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True",
    instanceName, databaseName);

DataTable dt = new DataTable();  

using (SqlDataAdapter da = new SqlDataAdapter(commandText, connectionString))
{
    da.Fill(dt);
}  

Directory.CreateDirectory(outDirPath);  

foreach (DataRow dr in dt.Rows)
{
    Process.Start("bcp", string.Format(@"[{2}].[dbo].[{0}] out {3}\{0}.csv -S {1} -T -w -t ,",
        (string)dr[0], instanceName, databaseName, outDirPath));
}  

　
これを PowerShell スクリプトに変換すると、次のようになります。
ファイル名を「ExportData.ps1」として保存します。
$instanceName = ".\SQLEXPRESS"
$databaseName = "Tfs_DefaultCollection"
$outDirPath = "Output"
 
$commandText = "select [name] from sys.tables order by [name]"
$connectionString = [String]::Format("Data Source={0};Initial Catalog={1};Integrated Security=True", $instanceName, $databaseName)
 
$dt = New-Object System.Data.DataTable
$da = New-Object System.Data.SqlClient.SqlDataAdapter($commandText, $connectionString)
[void]$da.Fill($dt)
$da.Dispose()
 
if (-! (Test-Path $outDirPath))
{
    New-Item $outDirPath -type directory
}
 
$dt.Rows | ForEach-Object { bcp ([String]::Format("[{0}].[dbo].[{1}]", $databaseName, $_[0])) out ([String]::Format("{0}\{1}.csv", $outDirPath, $_[0])) -S $instanceName -T -w -t "," }
 
ExportData.ps1 を右クリックして [PowerShell で実行] をクリックするとデータがエクスポートされます。
ちなみに、この例では Team Foundation Server 2010 の既定のチーム プロジェクト コレクションである 
Tfs_DefaultCollection データベースを対象としており、全部で 213 テーブルあります。
 
 
 
注意点
(1) 既定では PowerShell の実行ポリシーが Restricted に設定されているため、.ps1 ファイルを PowerShell で実行できません。
     このことは Get-ExecutionPolicy コマンドレットを実行すれば確認できます。
     実行ポリシーを緩める必要があり、そのためには次のように Set-ExecutionPolicy コマンドレットを実行します。
Set-ExecutionPolicy RemoteSigned
(2) 上記の例ではコレクション内でループさせるために ForEach-Object コマンドレットを使用していますが、
     C# の foreach に近い構文を使用することもできます (Windows PowerShell 入門（5）－制御構文)。
 
(3) 逆に、データをインポートする場合は外部参照制約に注意する必要があります。
     親テーブルから先にデータをインポートしなければなりませんが、
     次のようなクエリでテーブルの親子関係を取得すれば何とかできそうです。
select [ct].[name] as [ChildTableName], [pt].[name] as [ParentTableName]
from sys.foreign_keys as [f]
inner join sys.tables as [ct] on [f].[parent_object_id] = [ct].[object_id]
inner join sys.tables as [pt] on [f].[referenced_object_id] = [pt].[object_id]
参照
カテゴリー: ツール, データベース. タグ: , , . 1 Comment »

共通鍵 (対称鍵) 暗号化アルゴリズム

2010年9月5日 — さかぽん
前回の SqlMembershipProvider のハッシュ アルゴリズムでは SqlMembershipProvider の内部でのハッシュ化のコードについて紹介しましたが、
今回は同様に、ASP.NET の内部での共通鍵 (対称鍵) 暗号化のコードについて記述します。
 
具体的には、MachineKeySection クラスの中にコードがあります。
そこではだいたい以下のような処理が実行されています。
SqlMembershipProvider で共通鍵 (対称鍵) 暗号化を利用する場合にもこのコードが使用されます。

static void Main()
{
    // Rijndael (128 ビット) で暗号化および復号します。
    string encryptedPassword = Encrypt("P@ssw0rd", "D74A4970EBF85F9291C0D28FE01296B2");
    string decryptedPassword = Decrypt(encryptedPassword, "D74A4970EBF85F9291C0D28FE01296B2");
}

private static string symmetricAlgorithmName = "Rijndael"; // 変更可能
private static Encoding encoding = Encoding.Unicode;

public static byte[] Encrypt(byte[] data, byte[] decryptionKey)
{
    return Transform(data, decryptionKey, true);
}

public static string Encrypt(string text, string decryptionKey)
{
    return Convert.ToBase64String(Encrypt(encoding.GetBytes(text), ToByteArray(decryptionKey)));
}

public static byte[] Decrypt(byte[] data, byte[] decryptionKey)
{
    return Transform(data, decryptionKey, false);
}

public static string Decrypt(string text, string decryptionKey)
{
    return encoding.GetString(Decrypt(Convert.FromBase64String(text), ToByteArray(decryptionKey)));
}

private static byte[] Transform(byte[] data, byte[] decryptionKey, bool encrypt)
{
    using (SymmetricAlgorithm symAlg = CreateSymmetricAlgorithm(decryptionKey))
    using (ICryptoTransform transform = encrypt ? symAlg.CreateEncryptor() : symAlg.CreateDecryptor())
    using (MemoryStream stream = new MemoryStream())
    using (CryptoStream cryptoStream = new CryptoStream(stream, transform, CryptoStreamMode.Write))
    {
        cryptoStream.Write(data, 0, data.Length);
        cryptoStream.FlushFinalBlock();

        return stream.ToArray();
    }
}

private static SymmetricAlgorithm CreateSymmetricAlgorithm(byte[] decryptionKey)
{
    SymmetricAlgorithm symAlg = SymmetricAlgorithm.Create(symmetricAlgorithmName);

    symAlg.Key = decryptionKey;
    symAlg.IV = new byte[symAlg.BlockSize / 8];

    return symAlg;
}

// ランダムな復号キーを生成します。
public static string GenerateDecryptionKey(int size)
{
    return ToHexString(GenerateRandomByteArray(size));
}

// ランダムなバイト配列を生成します。
public static byte[] GenerateRandomByteArray(int size)
{
    byte[] data = new byte[size];

    RandomNumberGenerator.Create().GetBytes(data);

    return data;
}

// 16 進数表記の文字列をバイト配列に変換します。
public static byte[] ToByteArray(string hexString)
{
    return Enumerable.Range(0, hexString.Length / 2)
        .Select(i => hexString.Substring(2 * i, 2))
        .Select(s => byte.Parse(s, NumberStyles.HexNumber))
        .ToArray();
}

// バイト配列を 16 進数表記の文字列に変換します。
public static string ToHexString(byte[] buffer)
{
    return string.Concat(buffer.Select(b => b.ToString("X2")).ToArray());
}

 
注意点
(1) 上記のコードは、比較的短いデータに対しての暗号化および復号が想定されているため、入出力の型は byte[] となっています。
     ファイルのようなデータを暗号化の対象とする場合には、
     Stream を使用したインターフェイスに変更してバッファリングを組み込む必要があります。
 
(2) decryptionKey に指定できるキーの長さは、以下のように暗号化アルゴリズムごとに決められています。
     この情報は SymmetricAlgorithm.LegalKeySizes プロパティで取得することができます。
  • Rijndael または AES の場合: 128, 192, 256 ビットのいずれか
  • TripleDES の場合: 128, 192 ビットのいずれか
バージョン情報
.NET Framework 4
 
参照
カテゴリー: .NET Framework. タグ: . 1 Comment »