くりーむわーかー

プログラムとか。作ってて ・試しててハマった事など。誰かのお役に立てば幸いかと。 その他、いろいろエトセトラ。。。

2017年08月

SQLServer バイト数で切り出し

ちょっとSQLServerのクエリで文字列をバイト数で切り出ししたくなった。 SQLServerにはバイト数で切り出す関数は無いらしい。ほへ。まー別に使いどころはあんまないしの。

で、SQLServerでやる場合は下の感じ。

declare @test as varchar(100)
set @test = 'あ1い2う3え4お'

--バイト数
select datalength(convert(VARCHAR(30), @test))
--バイト数でLEFT
select convert(VARCHAR(5), @test)
--バイト数でRIGHT
select reverse(convert(VARCHAR(5), reverse(@test)))

全角半角入ってもちゃんといく。ただし、全角を中途半端に切ると1バイト削られる。↓の感じ。

declare @test as varchar(100)
set @test = 'あ1い2う3え4お'

--バイト数でLEFT
select convert(VARCHAR(3), @test)--⇒"あ1"
select convert(VARCHAR(4), @test)--⇒"あ1"

文字列として扱いたい場合は、この仕様は割とありがたい。ただ、使い方によっては気を付けないとダメそーかな。

C# .netMVC で MySQL使う

タイトル通り。ついでにEFから呼べるようにもする。まずはNugetで↓をインストール。

sample

そしたら、まず、SQL直接書くやつ。

using MySql.Data.MySqlClient;
public void test()
{
    string constr = @"Database=hogedb;Data Source=fugaserver;User Id=root;Password=passpass";
    string error = "";
    string title = "";
    using (MySqlConnection cn = new MySqlConnection(constr))
    {
        try
        {
            cn.Open();// 接続
            //クエリ発行
            MySqlDataAdapter da = new MySqlDataAdapter("select title from hogetables limit 1;", cn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            foreach (DataRow row in dt.Rows)
            {
                title = row[0].ToString();
            }
            cn.Close();//クローズ
        }
        catch (Exception e)
        {
            error = e.Message;
        }
    }
}

次はEF。多分↑のインストールとは別に、EFを入れないとダメ。

最初はWeb.Config。まーこれでやる必要はないけど何となく。

<connectionStrings>
  <add name="RealConnection" connectionString="Database=hogedb;Data Source=fugaserver;User Id=root;Password=passpass" providerName="MySql.Data.MySqlClient" />
</connectionStrings>

テーブル用のPOCO。

[Table("hogetables")]
public class hogetable
{
    [Key]
    public int id { get; set; }
    public string title { get; set; }
}

public class MOGEModels : DbContext
{
    public MOGEModels() : base("RealConnection")
    {
    }
    public DbSet<hogetable> hogetables{ get; set; }
    
    public void test()
    {
       foreach (var tmp in this.hogetables.Where(n => n.id == 10))
       {
          tmp.title;
       }
    }
}

とゆーことで、普通に使えますねって当たり前か。。。

正直、RedmineとかのDBをちょっと他から触りたくなっただけです。Rubyでプラグインとか作るのめんどー。

.net MVC WebApi のContent-Type

.net MVCでWebApi作ってたんですが、なんだか戻りがXMLになってしまう。Jsonで返したい。

とりあえず、Chromeで取るとデフォでXMLになるっぽい?デフォでJsonにしたい。

stackoverflowに同じ質問あった。さすが。。。

回答は色々あるけど、何となく今は↓が一番良さげ?デフォを変えるなら。

using System.Net.Http.Formatting;//RequestHeaderMapping用

public static class WebApiConfig
{
    public static void Register(HttpConfiguration config)
    {
        config.MapHttpAttributeRoutes();
        //デフォルトのフォーマットを変える↓
        GlobalConfiguration.Configuration.Formatters.JsonFormatter.MediaTypeMappings.Add(new RequestHeaderMapping("Accept",
                          "text/html",
                          StringComparison.InvariantCultureIgnoreCase,
                          true,
                          "application/json"));
    }
}

WebApiConfigのRegisterの中で↑を追記。

あと、ちょっと違うけど、URLの指定で最後に「~~.json」とか「~~.xml」って指定させて、フォーマットを変える方法。RedmineのAPIはこれ形式すな。

public static class WebApiConfig
{
    public static void Register(HttpConfiguration config)
    {
        // Web API ルート
        config.MapHttpAttributeRoutes();
        config.Formatters.JsonFormatter.AddUriPathExtensionMapping("json", "application/json");
        config.Formatters.XmlFormatter.AddUriPathExtensionMapping("xml", "application/xml");
        
        //ルートの設定で{ext}が必要
        config.Routes.MapHttpRoute(
            name: "DefaultApi",
            routeTemplate: "api/{controller}/{action}.{ext}/{id}",
            defaults: new { id = RouteParameter.Optional }
        );
    }
}

ただ、最初のデフォルト強制する奴つけてると効かなくナリ。

あと、↓の感じでURLは最後の「/」付けないと効かなかったりする。ちょっとハマッた。Routeの設定ちゃんとやればいけるっぽいの。

/api/controller/action.json/

ついでに、Routeの設定ってみんなどうしてるんでしょ。デフォのままだと、Controllerのみだから、GetとかPostしか作れないよね?

Controller増やせばいいんだけど、ファイルいっぱいできるからあまし好きじゃない。なので、action入れてやってたりするんですが、これってありなのかしら。イマイチ分かってない。

.net mvc WebApiにレスポンスヘッダつける

以前、レスポンスヘッダ付けるやつを書いた。あっちはControllerを継承した普通のページ用のコントローラでの話。WebApiでやる場合はちょっと変えないと無理。↓の感じ。

public class WebApiController : ApiController
{
  public HttpResponseMessage Get()
  {
      var hogeModel = somethingModel();
      var response = Request.CreateResponse(HttpStatusCode.OK, hogeModel);
      response.Headers.Add("Access-Control-Allow-Origin", "*");//レスポンスヘッダ付ける
      return response;
  }
}

Jsonで戻したい場合は、オブジェクトで戻すと思うんだけど、↑の感じで書いてあげるとオブジェクトをJsonにして戻してくれる。

SQLServer 照合順序の変更

最近、DBの照合順序を変更する必要があった。SQLServer2012。一部のテーブルのみとか、部分的に照合順序を変える事は正直あまり考えられないので、インスタンス丸ごと変える。

で、色々見てるとだいたい↓の感じで書いてある。

ユーザー データベースに作成する新しいオブジェクトの照合順序は、 ALTER DATABASE ステートメントの COLLATE 句を使用して変更できます。 このステートメントを実行しても、既存のユーザー定義テーブルの列の照合順序は変わりません。 ALTER TABLEの COLLATE 句で変更することができます。

とか。⇒microsoftのdoc

master、model、msdb、および tempdb の各システム データベースを再構築する場合は、元の場所からデータベースを削除して再作成する必要があります。 再構築ステートメントに新しい照合順序を指定する場合は、その照合順序の設定でシステム データベースが作成されます。 これらのデータベースに対するユーザーの変更はすべて失われます。 たとえば、master データベースのユーザー定義オブジェクト、msdb にスケジュールされたジョブ、または model データベースの既定のデータベース設定に対する変更が対象になります。

とかとか。⇒TechNet

ユーザーDBは変えれなくはないけど、DBの照合順序変えても既存は変更されない。あと、システムデータベース(tempとか)はユーザDBとか存在してると変えられない。作り直しの必要あり。。。

それ何て再インストール?

正直、ユーザDBのインスタンス変えても、一時テーブルとか使うとtempテーブルにデータが出来るから、そっちと照合順序違くなって、Joinとか落ちるので、全DB変更する以外の事はあまし考えられない。。。

ググると色々出てくるんだけど、ぶっちゃけインスタンス再インストール(or別で作る)以外に無難な方法がありませんね。

あと、見てるとDBバックアップして変更後に復元するとか書いてあるサイトもあるけど、それやると、照合順序復元されてまるで意味なし。

ストアドとかの戻りとかはDBの照合順序変えれば適用されるらしいけど、テーブルの定義は変わらないようす。ついでにDBの照合順序は新しく作成されるテーブルに適用されるものみたいなので、既存は何も変わらない。

DBの照合順序変えてもsp_helpでテーブル見れば定義変わってないのが確認できる。

とゆーことで、以下の感じで全部作り直した。

  1. 既存をバックアップするか、リンクサーバ出来るようにしておく
  2. インスタンスを新しく作る(照合順序はインストール時に指定する)
  3. リンクサーバかバックアップを別名で復元
  4. 元のDBと同じものをCreateする
  5. 元のDBの全オブジェクトをスクリプト化しておいて、新しい方に全部作成
  6. 元DB復元したものかリンクサーバで元データを新しい方に入れ込む
めんどくさし。でもこれしか綺麗にいく方法が思いつかん。

ただ、↓のコマンドは通ったから、全列に対し実行するスクリプト作ればいけるかな?

alter table [テーブル名] alter column [列名] nvarchar(200) collate Japanese_XJIS_100_CI_AS_SC

見かけ上は上でも上手くいってるんだけど、本当にうまくいってるのか自信がありません。どうやって確認すればいいんだろうか。。。

ちなみに変更するのはサロゲートペア文字への対応用です。プログラム的な話はあれですが、SQLでいくと普通の照合順序使ってると、len()とかおかしくなる。サロゲの文字の場合はlen()が2でかえってくるので。なのでrightとかsubstringとか全部おかしくなる。ゆえに照合順序を変えて、サロゲでもlen()で1って帰ってきてくれるようにしないと色々あれなのです。

照合順序的には「japanese*****SC」みたいに「SC」つく照合順序が補助文字があーだこーだでサロゲ文字入ってきても上手くいく照合順序の様子。

あー、いちを個別のクエリ上でcollate句で照合順序指定すればテーブルとかDBの照合順序が違くても上手くいく。でも、んな事をやり続ける自信ありませんヨネ。。。

.net mvc CSSとかのキャッシュバスティングをやる ver2

以前、書いたやつのちょっと補足。

<link href="/Content/site.css?20160508113250" rel="stylesheet"/>

↑みたいな、ブラウザキャッシュをやめさせるクエリ文字列をファイル更新された自動でつけるやつ。

直すのは「RegisterBundles()」してるとこ。デフォだとBundleConfigっていうクラス名かな。

//CSSはこっち
Bundle addBundle = new StyleBundle("~/Content/css").Include("~/Content/bootstrap.css", "~/Content/site.css");
addBundle.Transforms.Add(new FileHashVersionBundleTransform());
bundles.Add(addBundle);
//JavaScriptはこっち
addBundle = new ScriptBundle("~/lib/hoge").Include("~/Scripts/lib/hoge.js");
addBundle.Transforms.Add(new FileHashVersionBundleTransform());
bundles.Add(addBundle);

「FileHashVersionBundleTransform」の中身は元記事参照。そしたら、cshtml側で↓の感じで使いたいやつを呼ぶ。

@section scripts {
@Scripts.Render("~/lib/hoge")
}

結果は↓の感じ。

<script src="/Scripts/lib/hoge.js?v=20170508210031529"></script>

C# 正規表現のパフォーマンス

最近、正規表現でちょろちょろ判定をしてたんだけど、書き方によって相当パフォーマンスに影響あるんですね。当たり前か。

ホントにかなり変わる。例えば、

string reg1 = @"(パンダ|ライオン|デザイン)(。|\d|\s)";

みたいな正規表現はクソ重い。何となく、「(a|b|c)(x|y|z)」みたいな組合せがすごい増えるとやれる感じがする。上の例でいうと、

string reg2 = @"パンダ(。|\d|\s)";
string reg3 = @"ライオン(。|\d|\s)";
string reg4 = @"デザイン(。|\d|\s)";

って書いてそれぞれ個別に判定した方が数段早かったりする。上の例で計測してみると、だいたい2.6倍早くなった。

組合せを何個も使うような正規表現は考え物ですね。当たり前なのかもしれませんが。。。

問合せ