SetCommentForSQLServer/SetCommentForSQLServer.dms at master · megos/SetCommentForSQLServer · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
// SetCommentForSQLServer


class MainForm extends VCLForm {

  var path = WScript.Path + 'scripts\Tool\';

  function MainForm(main, setting) {

    this.loadFromFile(new File(this.path + 'SetCommentForSQLServer.dfm'));
    this.main = main;

    // 設定読み込み
    fromSetting(setting);
  }

  // フォーム上の設定値をSettingから設定
  function fromSetting(setting) {
    this.left = setting.windowLeft;
    this.top = setting.windowTop;
    this.cboLP.Checked = setting.isLP;
    this.cboPL.Checked = setting.isPL;
  }

  // フォーム上の設定値をSettingにコンバート
  function toSetting() {
    var setting = new Setting();
    setting.windowLeft = this.left;
    setting.windowTop = this.top;
    setting.isLP = this.cboLP.Checked;
    setting.isPL = this.cboPL.Checked;
    return setting;
  }

}

class Setting {

  var path = WScript.Path + 'scripts\Tool\';

  var windowLeft;
  var windowTop;
  var isLP;

  function loadSetting() {
    // iniファイル読み込み
    var ini = new Ini(new File(this.path + 'SetCommentForSQLServer.ini'));
    this.windowLeft = ini.read('Window','Left',100);
    this.windowTop = ini.read('Window','Top',100);
    this.isLP = ini.read('SetCommentForSQLServer','LP', true);
    this.isPL = ini.read('SetCommentForSQLServer','PL', false);
    return this;
  }

  function saveSetting() {
    var ini = new Ini(new File(this.path + 'SetCommentForSQLServer.ini'));
    ini.write('Window','Left',this.windowLeft);
    ini.write('Window','Top',this.windowTop);
    ini.write('SetCommentForSQLServer','LP', this.isLP);
    ini.write('SetCommentForSQLServer','PL', this.isPL);
    ini.update(); // iniファイル更新
  }
}


class Generator() {

  function process() {
    var setting = new Setting().loadSetting();
    var frm = new MainForm(true,setting);
    if (frm.showModal() === 1) {
      // 出力ボタンが押されてfrmを閉じたなら、設定を保存し出力する
      setting = frm.toSetting();
      setting.saveSetting();
      if (!(frm.txtTables.Text.Length === 0 || frm.txtSource.Text.Length === 0)) {
        addComment(frm.txtTables.Text, frm.txtSource.Text, frm.cboLP.Checked);
      } else {
        alert('入力がありません');
      }
    }
  }

  function addComment(table, text, checked) {
    // DBに接続
    var conn = application.dbTree.getSelectedDatabaseConnection();
    var row = text.split(/\r\n|\r|\n/);
    var result = '';

    var idxP = 1;
    var idxL = 0;

    if (!checked) {
      idxP = 0;
      idxL = 1;
    }

    for (var i = 0; i < row.length; i++) {
      var data = row[i].split(/,|\t/);
      if (data.length == 2) {
        var rs = conn.executeQuery("select name from fn_listextendedproperty(NULL, 'schema', 'dbo', 'table', '" + table + "', 'column', '" + data[0].trim() + "')");

        if (rs.eof() == false) {
          if (rs.getFieldValue('name') == 'MS_Description') {
            conn.execute("exec sys.sp_updateextendedproperty @name=N'MS_Description'" +
                                                           ",@value=N'" + data[idxL].trim() + "'" +
                                                           ",@level0type=N'SCHEMA'" +
                                                           ",@level0name=N'dbo'" +
                                                           ",@level1type=N'TABLE'" +
                                                           ",@level1name=N'" + table + "'" +
                                                           ",@level2type=N'COLUMN'" +
                                                           ",@level2name=N'" + data[idxP].trim() + "'");
          }
        } else {
          conn.execute("exec sys.sp_addextendedproperty @name=N'MS_Description'" +
                                                      ",@value=N'" + data[idxL].trim() + "'" +
                                                      ",@level0type=N'SCHEMA'" +
                                                      ",@level0name=N'dbo'" +
                                                      ",@level1type=N'TABLE'" +
                                                      ",@level1name=N'" + table + "'" +
                                                      ",@level2type=N'COLUMN'" +
                                                      ",@level2name=N'" + data[idxP].trim() + "'");
        }
        rs.close();
      }

    }
    alert('コメントが反映されました');
  }

}

(function(){
  new Generator().process();
})();