てすとぶろぐ: 2009/09

2009年9月25日金曜日

気持ちは分かるけど・・・

有名所の人がＩＴエンジニアの転職市場の話を書いているんだけど・・・。
んー、正直微妙というか何というか。

小飼さんの言わんとするところも分かるんだけどねぇ。なんかそのやり方は、都心部というか人が多く集まっている場所とか、大きい企業とかそういうところにしか通じないんじゃないかなぁ、と思うんだよな。

少なくとも中小～零細企業においてＩＴエンジニアがブログ書いていようが、Twitterしてようがまず間違いなく見る事はないだろうし。たかだか3カ月程度Blogを書き続けた程度で人の目につくかどうかなんて、よほどの中身が書ける人に限定されるわなぁ。

逆に、そういった人の目につくことができるネタでBlogできるくらいだったら小飼さんの言う通り、職にあぶれることもないだろうね。というか特に悩むことなく転職できるような人だと思うわｗ

2009年9月19日土曜日

フルテキスト検索設定用のストアドプロシージャ

前回同様面倒くさくなったのでストアド化させたｗ
カタログの削除・生成とインデックスの削除・生成用のサンプルとして。

/*
特定のテーブルにおけるフルテキストインデックス、フルテキストカタログを再作成する
*/
ALTER PROCEDURE CREATE_FULLTEXTCATALOG(@tableName NVARCHAR(MAX))
AS
BEGIN

DECLARE @CRLF NVARCHAR(2);
SET @CRLF = CHAR(13) + CHAR(10);

DECLARE @INDEXCOUNT     INT;
DECLARE @DBNAME         NVARCHAR(MAX);
DECLARE @CATALOGNAME    NVARCHAR(MAX);
DECLARE @COLUMNTYPE     INT;
DECLARE @COLUMNNAME     NVARCHAR(MAX);
DECLARE @INDEXNAME      NVARCHAR(MAX);
DECLARE @PRIMARYKEYNAME NVARCHAR(MAX);
DECLARE @TARGETCOLUMN   NVARCHAR(MAX);
DECLARE @EXECUTESQL     NVARCHAR(MAX);

--実行データベース名を取得
SET @DBNAME = DB_NAME();

--フルテキストカタログ取得
DECLARE CUR_CATALOG CURSOR LOCAL FORWARD_ONLY FOR
    SELECT
      SFC.NAME CATALOGNAME
    FROM
      SYS.FULLTEXT_CATALOGS SFC
        INNER JOIN SYS.FULLTEXT_INDEXES SFI ON (SFC.FULLTEXT_CATALOG_ID = SFI.FULLTEXT_CATALOG_ID)
          INNER JOIN SYS.TABLES STS ON (SFI.OBJECT_ID = STS.OBJECT_ID)
    WHERE
      STS.NAME = @tableName;

--対象テーブルのフルテキスト対象項目取得
DECLARE CUR_TABLE CURSOR LOCAL FORWARD_ONLY FOR
    SELECT
      SC.SYSTEM_TYPE_ID,
      SC.NAME
    FROM
      SYS.COLUMNS SC
        INNER JOIN SYS.TABLES STS ON (SC.OBJECT_ID = STS.OBJECT_ID)
    WHERE
      STS.NAME      = @tableName AND
      SC.MAX_LENGTH = -1;

--対象テーブルのプライマリキー取得
SET @PRIMARYKEYNAME = '';
SELECT
    @PRIMARYKEYNAME = SI.NAME
FROM
    SYS.TABLES ST
      INNER JOIN SYS.INDEXES SI ON (ST.OBJECT_ID = SI.OBJECT_ID)
WHERE
    ST.NAME = @tableName AND
    SI.TYPE = 1;

--既存フルテキストインデックスの削除
    SELECT
      @INDEXCOUNT = COUNT(*)
    FROM
      SYS.FULLTEXT_INDEXES SFI
        INNER JOIN SYS.TABLES STS ON (SFI.OBJECT_ID = STS.OBJECT_ID)
    WHERE
      STS.NAME = @tableName;

    IF @INDEXCOUNT > 0
      BEGIN
        SET @EXECUTESQL = 'DROP FULLTEXT INDEX ON ' + @tableName;
        EXECUTE (@EXECUTESQL);
        PRINT @EXECUTESQL + @CRLF;
      END;

--既存のフルテキストカタログの削除
OPEN CUR_CATALOG;
FETCH NEXT FROM CUR_CATALOG INTO @CATALOGNAME;
WHILE (@@FETCH_STATUS = 0)
    BEGIN
      SET @EXECUTESQL = 'DROP FULLTEXT CATALOG ' + @CATALOGNAME;
      EXECUTE (@EXECUTESQL);
      PRINT @EXECUTESQL + @CRLF;

      FETCH NEXT FROM CUR_CATALOG INTO @CATALOGNAME;
    END;
CLOSE CUR_CATALOG;
DEALLOCATE CUR_CATALOG;

--フルテキストカタログの新規作成
SET @EXECUTESQL = 'CREATE FULLTEXT CATALOG FTC_' + @DBNAME + '_' + @tableName;
EXECUTE (@EXECUTESQL);
PRINT @EXECUTESQL + @CRLF;

--フルテキストインデックスの新規作成
SET @TARGETCOLUMN = '';
OPEN CUR_TABLE;
FETCH NEXT FROM CUR_TABLE INTO @COLUMNTYPE, @COLUMNNAME;
WHILE (@@FETCH_STATUS = 0)
    BEGIN
      IF @TARGETCOLUMN <> ''
        SET @TARGETCOLUMN = @TARGETCOLUMN + ', ';
      SET @TARGETCOLUMN = @TARGETCOLUMN + @COLUMNNAME + ' ';
      IF @COLUMNTYPE <> 231
        SET @TARGETCOLUMN = @TARGETCOLUMN + 'TYPE COLUMN EXTENSION ';

      SET @TARGETCOLUMN = @TARGETCOLUMN + ' LANGUAGE Japanese';

      FETCH NEXT FROM CUR_TABLE INTO @COLUMNTYPE, @COLUMNNAME;
    END;
CLOSE CUR_TABLE;
DEALLOCATE CUR_TABLE;
SET @EXECUTESQL = 'CREATE FULLTEXT INDEX ON ' + @tableName + '(' + @TARGETCOLUMN + ')' + @CRLF
                  + ' KEY INDEX ' + @PRIMARYKEYNAME + @CRLF
                  + ' ON FTC_' + @DBNAME + '_' + @tableName + @CRLF
                  + ' WITH CHANGE_TRACKING AUTO' + @CRLF;
EXECUTE (@EXECUTESQL);
PRINT @EXECUTESQL + @CRLF;

PRINT 'フルテキストカタログ、インデックスの生成を行いました。ログのダンプを行った後に有効となります。';

END;

2009年9月18日金曜日

IDENTITY設定用のDELETE時トリガ自動生成

毎回テンプレートから起こしているのが面倒になったので、一括生成用プロシージャを作ってみた。システム管理テーブルから、トリガ一覧の取得やテーブルの項目一覧取得などを使っているので、ちょちょいと変更していけば結構使いではある・・・かもｗ

--
--全てのテーブルにてIDENTITY値を調整するためのDELETEトリガを再生成する
--
ALTER PROCEDURE UP_CREATE_ALLDELETETRIGGER
AS
BEGIN

--ワーク設定
DECLARE @EXECUTESQL NVARCHAR(MAX);
DECLARE @CRLF NVARCHAR(2);
SET @CRLF = CHAR(13) + CHAR(10);

--現在のトリガ一覧を取得
DECLARE @OLDTRIGGERNAME NVARCHAR(MAX);
DECLARE @TRIGGERCOUNT   INT;
DECLARE CUR_TRG CURSOR LOCAL FORWARD_ONLY FOR
    SELECT
      ST.NAME,
      (SELECT COUNT(*) FROM SYS.TRIGGER_EVENTS STE2 WHERE STE2.OBJECT_ID = ST.OBJECT_ID) TRIGGERCOUNT
    FROM
      SYS.TRIGGERS ST
        INNER JOIN SYS.TRIGGER_EVENTS STE ON (ST.OBJECT_ID = STE.OBJECT_ID)
    WHERE
      ST.PARENT_CLASS = 1     AND --対象の親    ：DML トリガ用のオブジェクトまたは列
      ST.TYPE         = 'TR' AND --対象の種類：トリガ（TR）
      STE.TYPE        = 3     AND --イベント種類：DELETE時
      ST.NAME NOT LIKE '%#__FULLTEXT' ESCAPE '#' AND --フルテキスト用トリガの除外
      ST.NAME NOT LIKE '%#__SEARCH' ESCAPE '#'       --検索データ用トリガの除外
    GROUP BY
      ST.OBJECT_ID,
      ST.NAME
ORDER BY
      ST.NAME
    ;

--既存トリガの削除
OPEN CUR_TRG;
FETCH NEXT FROM CUR_TRG INTO @OLDTRIGGERNAME, @TRIGGERCOUNT;

WHILE (@@FETCH_STATUS = 0)
    BEGIN
      --DELETE時のみのトリガを削除
      IF @TRIGGERCOUNT = 1
        BEGIN
          SET @EXECUTESQL = 'DROP TRIGGER ' + @OLDTRIGGERNAME + '' + @CRLF;
          EXECUTE (@EXECUTESQL);
        END;

      FETCH NEXT FROM CUR_TRG INTO @OLDTRIGGERNAME, @TRIGGERCOUNT;
    END;

CLOSE CUR_TRG;
DEALLOCATE CUR_TRG;

--テーブル一覧、IDENTITY列一覧を取得
DECLARE @TABLENAME NVARCHAR(MAX);
DECLARE @COLUMNNAME NVARCHAR(MAX);
DECLARE CUR_IDENT CURSOR LOCAL FORWARD_ONLY FOR
    SELECT
      ST.NAME,
      SC.NAME
    FROM
      SYS.COLUMNS SC
       INNER JOIN SYS.TABLES ST ON (SC.OBJECT_ID = ST.OBJECT_ID)
    WHERE
      SC.IS_IDENTITY = 1
    ORDER BY
      ST.NAME, SC.NAME
    ;

--新トリガの生成
OPEN CUR_IDENT;
FETCH NEXT FROM CUR_IDENT INTO @TABLENAME, @COLUMNNAME;

WHILE (@@FETCH_STATUS = 0)
    BEGIN
      --トリガSQLを生成
      SET @EXECUTESQL = 'CREATE TRIGGER TRG_DEL_' + @TABLENAME + @CRLF
                      + ' ON ' + @TABLENAME + @CRLF
                      + ' AFTER DELETE ' + @CRLF
                      + 'AS ' + @CRLF
                      + 'BEGIN ' + @CRLF
                      + ' ' + @CRLF
                      + ' SET NOCOUNT ON;' + @CRLF
                      + ' ' + @CRLF
                      + ' DECLARE @COUNT INT;' + @CRLF
                      + ' SET @COUNT = 0;' + @CRLF
                      + ' SELECT @COUNT = COUNT(*) FROM DELETED;' + @CRLF
                      + ' IF @COUNT <= 0' + @CRLF
                      + '    RETURN;' + @CRLF
                      + ' ' + @CRLF
                      + ' DECLARE @MAXIDENT INT; ' + @CRLF
                      + ' SELECT @MAXIDENT = MAX(' + @COLUMNNAME + ') FROM ' + @TABLENAME + ' WITH (UPDLOCK);' + @CRLF
                      + ' IF @MAXIDENT IS NULL ' + @CRLF
                      + '    SET @MAXIDENT = 0;' + @CRLF
                      + ' DBCC CHECKIDENT (''' + @TABLENAME + ''', RESEED, @MAXIDENT) WITH NO_INFOMSGS; ' + @CRLF
                      + ' ' + @CRLF
                      + 'END; ' + @CRLF;
      EXECUTE (@EXECUTESQL);

     FETCH NEXT FROM CUR_IDENT INTO @TABLENAME, @COLUMNNAME;
    END;

CLOSE CUR_IDENT;
DEALLOCATE CUR_IDENT;

END;