くりーむわーかー

なんか久しぶりに書く気がする。

タイトル通り。サロゲとか外字の文字を文字コード指定でDBに入れたい時用。

MySQL(MariaDB)

insert testchar values ('外字',CHAR(0xEE8080 USING utf8mb4));
insert testchar values ('サロゲ',CHAR(0xF0A08083 USING utf8mb4));

サロゲを表現する場合、普通にIMEとかで見ると、「0xD840 , 0xDC03」って見えるけどこれを合わせた形式にする。

SELECT HEX(CONVERT(CHAR(0xD840DC03 USING utf16) USING utf8mb4));

上で出てきたコードをchar()に指定すればサロゲ文字が出る。あと、エンコード指定でutf8mb4を指定しないとダメ。サロゲ対応用の文字コードらし。

PostgreSQL

insert into testchar values('外字',chr('xE000' :: bit(16) :: int));
insert into testchar values('サロゲ','𠀃');--文字コード指定がわからん。

サロゲの文字コード指定が分かりませんでした。ごめんなさい。今度調べよ。

タイトル通り。とりあえず版。

select * from (
	select 
	relname as TABLE_NAME 
	from pg_stat_user_tables
) as X
inner join (
	select 
	* 
	from information_schema.columns 
	where table_catalog='DB名' 
	and table_schema = 'スキーマ名'
) as Y
on X.TABLE_NAME = Y.table_name
left join (

	select
	tc.table_name as TABLE_NAME
	,ccu.column_name as COLUMN_NAME
	from information_schema.table_constraints tc,information_schema.constraint_column_usage ccu
	where tc.table_catalog='DB名'
	and tc.constraint_type='PRIMARY KEY'
	and tc.table_catalog=ccu.table_catalog
	and tc.table_schema=ccu.table_schema
	and tc.table_name=ccu.table_name
	and tc.constraint_name=ccu.constraint_name

) as Z
on X.TABLE_NAME = Z.TABLE_NAME
and X.TABLE_NAME = Z.table_name
and Y.table_name = Z.TABLE_NAME
and Y.column_name = Z.COLUMN_NAME

ローカル以外からPostgreにつなげようとすると「SSL無効用のエントリがありません」っていうエラーが出る。

Postgreの設定で許可するIPを指定しないとダメらしい。微妙じゃね？

設定するのは↓

PostgreSQL\9.6\data\pg_hba.conf

↓の感じで繋げたい端末のIPをいれておく。
host    all             all             123.456.789.012/32            md5

まー、アプリサーバを入れておく感じになると思うんだけど、pgAdminとか保守的な事をする端末すらもやっておかないとダメってことよね？

絶対他にやり方があるはず。

タイトル通り。最近、SQLServerからPostgreSQLへの切り替えをやってる。CreateTableしてー、CSVにデータはいてー、Postgreにインポートしてー。みたいなのはググればいっぱい出てきますが、正直、
クッソだるい

何がだるいかとゆーと、ってか実際には上の感じの作業はしてないので、もしかしたらすんなり行くのかもしれませんが、十中八九、以下の点ではまるのが目に見えている。

1. テキスト系で改行とかエスケープしないとダメなのあったらどーせ無理じゃない？
2. 100を超えるテーブルのCreate文なんか流したくない
3. 100を超えるテーブルのインポートなんかしたくない
4. バイナリ型ってCSV吐いただけで行けるのかしら？

とゆーわけで、PostgreSQLをC#でごにょごにょするのに慣れる意味も含めてプログラムでやった。

SQLServerへの接続とかはデフォでほぼ行けるからどーでもいいとして、PostgreSQLを扱う場合は「Npgsql」を使う。インストールはNugetから。

あと、一件づつインサートとかはありえないので、.netでいうBulkCopyもしたい。

プログラムの流れは↓の感じ。

①SQLServerで移行したいDBのテーブルの定義を丸ごと取得
②①の中でPostgre用のCreateTableを作っておく。
③Postgreに②のCreateTableを発行
④SQLServer側のテーブルをSelect *して、Postgreに順次BulkCopy
⑤Postgre側にIndexつける
⑥VACUUM実行

大したことはやってないけど、確実に手でやるより早い。

①のSQLServerの定義取得は↓の感じのクエリで丸ごととってくる

select 
X.object_id
,X.name as tablename
,cast(Y.column_id as int) as column_id
,Y.name as colname
,cast(Y.system_type_id as int) as system_type_id
,TYPE_NAME(Y.system_type_id) as typename
,cast(Y.max_length as int) as max_length
,cast(Y.precision as int) as precision
,cast(Y.scale as int) as scale
,cast(Y.is_nullable as int) as is_nullable
,cast(Y.is_identity as int) as is_identity 
,cast(isnull(Z.is_primary_key,0) as int) as is_primary_key
from (
	select * from sys.tables
	where type = 'U' 
	and name not like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
) as X
inner join (
	select * from sys.columns
) as Y
on X.object_id = Y.object_id
left join (
	select X.object_id,Y.column_id,Z.is_primary_key from sys.columns as X
	inner join sys.index_columns as Y
	on X.object_id = Y.object_id
	and X.column_id = Y.column_id
	inner join sys.indexes as Z
	on Y.object_id = Z.object_id
	and Y.index_id = Z.index_id
	and X.object_id = Z.object_id
	where Z.is_primary_key = 1
) as Z
on Y.object_id = Z.object_id
and Y.column_id = Z.column_id
order by X.object_id,Y.column_id

主キーの判定がちょっとメンドー。あと、テーブル名で西暦8ケタ付きのバックアップぽいテーブルは除くようにしてみたりしてる。あと、数値系の項目ってどれがbyte型か調べるのめんどーになったので、全部強制的にIntに変換。ランボー過ぎかな？

定義が取れれば、Create文はテキトーに作れるでしょう。

そしたら、Postgre側にテーブル作成を投げる。下の感じ。公式のままだ。

//EF使ってるけど、クエリを発行したいのでEFの接続文字列を無理やり取得
string constr= this.Database.Connection.ConnectionString;
using (var conn = new NpgsqlConnection(constr))
{
    conn.Open();
    using (var cmd = new NpgsqlCommand())
    {
        cmd.Connection = conn;
        try
        {
            //何回か流すようにテーブルドロップ。初回は落ちるのでtryしておく。これもランボー。
            cmd.CommandText = String.Format("DROP TABLE dbo.\"{0}\";",pDef.tablename);
            cmd.ExecuteNonQuery();
        }
        catch { }
        cmd.CommandText = pDef.createSql;
        cmd.ExecuteNonQuery();
    }
}

そしたら次はBulkCopy。ロジックは抜粋で。

using (SqlConnection cn_ = new SqlConnection(ConnectionString))
{
    cn_.Open();

    SqlCommand command = new SqlCommand();
    command.CommandTimeout = 3600;//タイムアウトの設定
    command.CommandText = "select * from [テーブル名]";
    command.Connection = cn_;
    //SQLServerにSelect投げる
    using (SqlDataReader sqlDr = command.ExecuteReader())
    {
        DataTable schemaDt = sqlDr.GetSchemaTable();
        //Postgreにつなぐ
        using (var conn = new NpgsqlConnection(pgConStr))
        {
            conn.Open();
            //Postgreのbulk
            using (var writer = conn.BeginBinaryImport(String.Format("COPY dbo.\"{0}\" ({1}) FROM STDIN (FORMAT BINARY)", "テーブル名","テーブルの列のリスト")))
            {
                while (sqlDr.Read())//SQLServerのSelect結果
                {
                    writer.StartRow();//コピーする行ごとに必要っぽい
                    for(int i=0;i< schemaDt.Columns.Count; i++)
                    {
                        //列毎にカキカキ
                        writer.Write(sqlDr[i],getColDef(pDef, i));
                    }
                }
            }
        }
    }
}
//SQLServerのデータ型に応じてPostgre側の型定義を返す
public NpgsqlDbType getColDef(MSDBTableDef pDef, int i)
{

    switch (pDef.coldeflist[i].system_type_id)
    {
        case 56://int
            return NpgsqlDbType.Integer;
        case 127://bigint
            return NpgsqlDbType.Bigint;
        case 167://varchar
        case 231://nvarchar
            if(pDef.coldeflist[i].max_length < 0)
                return NpgsqlDbType.Text;
            else
                return NpgsqlDbType.Varchar;
        case 106://decimal
            return NpgsqlDbType.Numeric;
        case 61://datetime
            return NpgsqlDbType.Timestamp;
        case 165://varbinary
            return NpgsqlDbType.Bytea;
    }
    return NpgsqlDbType.Varchar;
}

Npgsqlのバルクコピーは↓の感じで、Objectの配列として渡してもOKな気がする。

object[] dtArray = new object[schemaDt.Columns.Count];
sqlDr.GetValues(dtArray);
writer.WriteRow(dtArray);

ただ、公式のこの辺に「NpgsqlDbTypeでデータの型をちゃんと指定する事を激しくお勧めする」って書いてあるのでそーした。データ型の指定はいつの時代になってもとっても重要。

あと、インデックスはデータ入れ終わってから、まとめてやった方が多分よいと思う。速度的な意味で。

最後に、対象のDBに対して「VACUUM」ってコマンドを投げてあげる。不要領域の削除とかなんかもろもろやるらし。テーブルロックとかされる場合もあるらしいので、誰か触ってる時にはやらない方がいい。

でだ、NpgsqlのCopyはいいんだけど、Writeのタイミングで書きに行ってるわけじゃないよね？なんだかいつ実行してるのかロジック見る感じでよくわからない。多分、usingしてるし、Disposeされるときにやってるのかなと愚考。個人的に明示してくれる方がわかりやすい。

いつもはSQLServer使ってたんだけど、たまにはPostgreSQLも使ってみようかと思いまして、やってみた。

MVCはVisualStudioでテキトーにテンプレを使う。メンドーなので認証なし。そしたらNugetで下の3つのパッケージをインストール。

EFはEFのために必要。EFでPostgre使うためにNpgsql.EntityFrameworkが必要。これのためにNpgsqlが必要。みたいな感じらしい。EFは6。

そしたら次はWeb.configの設定

<!--ここはインストールすると勝手に入る-->
<entityFramework>
  <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
  <providers>
    <provider invariantName="Npgsql" type="Npgsql.NpgsqlServices, Npgsql.EntityFramework" />
    <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
  </providers>
</entityFramework>
<!--ここを手で追記-->
<system.data>
  <DbProviderFactories>
    <add name="Npgsql Data Provider" invariant="Npgsql" description="Data Provider for PostgreSQL" type="Npgsql.NpgsqlFactory, Npgsql" />
  </DbProviderFactories>
</system.data>
<!--接続文字列の指定-->
<connectionStrings>
  <add name="DefaultConnectionPGSQL" connectionString="Server=localhost;Port=5432;User Id=postgres;Password=＊＊＊＊＊;Database=hogedb" providerName="Npgsql" />
</connectionStrings>

手で追記する部分があるっぽい。ここ入れないと動かなかった。あとは接続文字列の指定。

ためしに作ったテーブルは下の二つ。日本語列名がいけるか試したいので日本語で。

--主キーがserial(SQLServerで言うIdentity)じゃないやつ
create table dbo.ホゲテーブル
(
ホゲ varchar(100) 
,数値 int
,CONSTRAINT wkpk PRIMARY KEY (ホゲ)
)
--主キーにserial(SQLServerで言うIdentity)
create table dbo.フガテーブル
(
フガid SERIAL
,テスト varchar(100) 
,数値 int
)

で、ワナが一つ。SQLServerはデフォでスキーマ名が「dbo」になる。なので、EFも何もしないとSQLに「dbo.table」みたいに書かれる。なので、Postgreのスキーマはdboで作っておいた方が吉。スキーマを変える場合は後述。

あとは、ためし実装。Model側。

public class HomeModels : DbContext
{
    public HomeModels() : base("DefaultConnectionPGSQL")
    {

    }
    public DbSet<HogeTable> HogeTables { get; set; }
    public DbSet<FugaTable> FugaTables { get; set; }

    public HomeViewModel getTable()
    {
        HomeViewModel resultModel = new HomeViewModel();
        resultModel.hogeT = this.HogeTables.Where(n => n.hogeval > 0).ToList();
        resultModel.fugaT = this.FugaTables.Where(n => n.数値 > 0).ToList();

        return resultModel;
    }
    public void modelAdd()
    {
        HogeTable addM = new HogeTable();
        addM.hogekey = DateTime.Now.ToString("yyyyMMddhhmmssfff");
        addM.hogeval = 123;
        this.HogeTables.Add(addM);


        FugaTable addM2 = new FugaTable();
        addM2.テスト = DateTime.Now.ToString("yyyyMMddhhmmssfff");
        addM2.数値 = 123;
        this.FugaTables.Add(addM2);
        this.SaveChanges();
    }

}
public class HomeViewModel
{
    public List<HogeTable> hogeT { get; set; }
    public List<FugaTable> fugaT { get; set; }
}

[Table("ホゲテーブル")] //テーブル名指定
public class HogeTable
{
    [Key] //主キーの設定
    [Column("ホゲ")] //列名指定
    public string hogekey { get; set; }
    [Column("数値")]//列名指定
    public int hogeval { get; set; }
}

[Table("フガテーブル", Schema = "dbo")] //スキーマ変えたいとき
public class FugaTable
{
    [Key] //主キーの設定
    public int フガid { get; set; }
    public string テスト { get; set; }
    public int 数値 { get; set; }
}

コントローラ側。HomeのIndexだけで、あと、データ追加用のAction。

HomeModels db = new HomeModels();
public ActionResult Index()
{
    HomeViewModel result = db.getTable();

    return View(result);
}

public ActionResult AddAction()
{
    db.modelAdd();
    return RedirectToAction("Index");
}

View側はこれ。

@model mvcdemo.Models.HomeViewModel
@{
    ViewBag.Title = "Home Page";
}

<div>
    @Html.ActionLink("Add","AddAction")
</div>

<h1>ホゲテーブル</h1>
<div>
    @foreach(var tmp in Model.hogeT)
    {
        <table>
            <tr>
                <td>
                    @tmp.hogekey
                </td>
                <td>
                    @tmp.hogeval
                </td>
            </tr>
            
        </table>
    }
</div>

<h1>フガテーブル</h1>
<div>
    <table>
        @foreach (var tmp in Model.fugaT)
        {
            <tr>
                <td>
                    @tmp.フガid
                </td>
                <td>
                    @tmp.テスト
                </td>
            </tr>
        }    
    </table>
</div>

実行結果で、何回か追加した後。

ちゃんとIDも振られてるし問題なし。。。フツーに使える。よかたよかた。

スキーマ変えたいときはテーブルのModel作るときに属性で「[Table("フガテーブル", Schema = "dbo")] 」って感じにする。全体通してやる場合はDbContextのクラスの中に下を入れておく。

public class HomeModels : DbContext
{
    public HomeModels() : base("DefaultConnectionSQLServer")//DefaultConnectionPGSQL
    {

    }
    //これを入れる
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema("dbo");
    }
    public DbSet<HogeTable> HogeTables { get; set; }
    public DbSet<FugaTable> FugaTables { get; set; }
    ・・・

Modelのインスタンス作った時に呼ばれるやつではないかと。。。

ちなみにこの状態のまま、SQLServer側に同じテーブル作って、接続文字列の指定でSQLServer用のものを書けばそのままDB切り替えられました。ためしに色々やるのに便利。。。

Postgerはフツーに使えていい感じなんだけど、なんつーか、Postgreの管理ツールがびみょー。なんかいいの無いのかしら。

pgAdminなるものを使用してみたけど。微妙じゃない？なんつーか自分がMicrosoftに染められてるんだろーけど、インテリセンス弱すぎじゃない？あと、エラーとか警告とか結果の出力とかもろもろもろもろもろ。SQLServerのManegementStudioはやっぱりさすがにさすがにって感じなんだろーか。。。