既存データベース（SQL Server）をRailsに載せる

ごめんなさい、ちょっと長すぎて、途中で何を言っているかわからなくなった気がする...でも一応個人の作業メモということで...お許し下さい..orz

環境はwindows
Ruby on Rails(RoR)は「railsinstaller.org」からdownload

$ ruby --version
ruby 1.9.3p125 (2012-02-16) [i386-mingw32]
$ rails --version
Rails 3.2.1
$ gem --version
1.8.16
$ gem list
*** LOCAL GEMS ***
actionmailer (3.2.1)
actionpack (3.2.1)
activemodel (3.2.9, 3.2.1)
activerecord (3.2.1)
activerecord-sqlserver-adapter (3.2.10, 3.2.1)
...
...

VersionやGemの一覧（初期ではない、あとからGEMで追加したものもある！）
インストール後、デフォルト「c:\Sites」が作成される
※もしOS上既にRubyがインストールしていれば、複数バージョンを管理するために,pikをススメます
参照 http://holypp.hatenablog.com/entry/20110407/1302193459

$ gem install pik


さて、プロジェクトを建てましょう

$ rails new railsinstaller_demo
$ cd railsinstaller_demo


今回は、既存のデータベースを使って、RoRに乗り換える。DBはSQL Server
Rails3からはプロジェクト単位でGEMを管理するから、プロジェクトフォルダ直下のGemfileに追加

gem 'activerecord-sqlserver-adapter'
gem 'tiny_tds'

そしたら、実行

$ rail bundle install


次はDBに接続
config/database.ymlを編集

development:
adapter: sqlserver
database: DB_NAME
host: 192.168.1.XXX
username: user
password: pwd


migrate←この段階では必要ないかもしれr内が、まぁ私はやった

$ rake db:migrate


ここまで来て、次はscaffold。だけど既存のDBのテーブルにはid列もないし、テーブル名はRailsの複数形式に従っていないが、ここ参照しました。本当にたすかったよ！
例えば今回scaffoldしたいテーブル名は「CounterParty」

$ rails console

irbコンソールが表示されたら、下記コードを全部（テーブル名は変更してね）コピペ

name = 'CounterParty'
si_table_name = 'CounterParty'
si_field_names = Array.new
si_cols = ActiveRecord::Base.connection.columns(si_table_name,"#{name} Columns")
si_cols.each do |c|
si_field_names << "#{c.name}:#{c.type}"
end
puts si_field_names.join(' ')

putsされたのは既存「CounterPartyのテーブル構造」、それをコピして、一旦railsコンソールをexit $ rails g scaffold CounterParty コピーした内容をここにペスト Another migration is already named create_counter_parties:... あれ、作成できない、既にある？なるほど、これはさきmigrateが悪かったね、しなければ良かったかも skip-migrateオプションを追加してもう一回 $ rails g scaffold CounterParty コピーした内容をここにペスト --skip-migrate Overwriteと聞かれたら「Y」 ... .. そしてCOunterPartyのScaffoldが作成されました。 しかし、DBの構造はRailsに乗ってないので、いま表示したらエラーになりますので app\models\counter_party.rbを編集して、テーブル名とid列を指定

class CounterParty < ActiveRecord::Base
 attr_accessible :CPName, :CPMemo, ... #Rails3からはwrite可能なフィルドを明示的に宣言しないといけないらしい？
 self.table_name = 'CounterParty'
 self.primary_key = :CPCPID
end

これでOKのはず、さあ見てみよ $ rails s ブラウザーで「http://localhost:3000/counter_parties」をいれてご― 表示されたかな？ 続き、WindowServer2008で動かす >ruby --version ruby 1.8.6 (2007-09-24 patchlevel 111) [i386-mswin32] >rails --version Rails 2.1.1 >gem --version 0.9.4 だいぶ古いですね Rails3とRails2はいろいろ変わったので、ソースコードはそのまま移して使えないので、プロジェクトを新規作成する。 プロジェクト作成 database.ymlを編集 mongrelで動かすので、サービスとして登録します。 プロジェクトフォルダーの下で、「cmd」というフォルダーを作成して、下記のbatファイルを作成する mongrel_service_regist.bat CD ../ CALL mongrel_rails service::install -N "Mongrel 9500 MyProj" -p 9500 -e development -a 0.0.0.0 SC CONFIG "Mongrel 9500 MyProj" START= AUTO 9500はポート番号、他の空いているポートもOK mongrel_service_remove.bat mongrel_rails service::remove -N "Mongrel 9500 MyProj" mongrel_service_restart.bat NET STOP "Mongrel 9500 MyProj" NET START "Mongrel 9500 MyProj" mongrel_service_start.bat NET START "Mongrel 9500 MyProj" mongrel_service_stop.bat NET STOP "Mongrel 9500 MyProj" これで、サービス登録[自動に設定]、再起動、開始、停止などがOne Clickでできるようになる ※但し、これは「管理者：コマンドプロンプト」で実行しなければなりません。 SQL Serverを使っている、データベースの日本語がviewで文字化けている時。 RoRのソースコードはすべてUTF8で記載、SQLServerのencodeはsjisの場合に発生?と思う config/environment.rbの先頭で、下記を追加すれば解消された。

$KCODE = "u"
require "win32ole"
WIN32OLE.codepage=WIN32OLE::CP_UTF8

最後、既存DBを使っているので、既存のprimary key列「CPCPID」(AUTO_INCREMENT利用しない)をrailsで管理する。 sql server では「IDENTITY」という属性らしい。「開始番号」の「IDENTITYシード」と「ID増分」がある 例えばIDENTITYシード=100、ID増分=10なら、「100,110,120」みたいに自動IDを生成してくれる。 Microsoft SQL Server Management Studio（長っ！）オブジェクトエクスプローラでテーブルの「デザイン」を開き、右クリックで「列のプロパティ」でIDENTITYを設定することが可能です。 もしなんか「一旦削除しないといけない」的なメッセージで怒られたら、 「ツール」＞「オプション」＞「デザイナ」＞「デーブルデザイナおよび...」＞「テーブルの再作成を必要とする変更.....」のチェックを外せば、変更できると思います。 それが終わったら、moduleでid列として、newとeditから削除すれば、普通にidとしてrailsが管理更新してくれます。

self.primary_key = :CPCPID

長すぎで自分も嫌になりました、よしっ！以上！ヽ(´ー｀)ﾉ ﾌｯ

mysqlでCURSORを使ったループ(LOOP)でINSERT

#まず削除

DROP PROCEDURE IF EXISTS myProc;

#作成

#日付をプロシジャーの引数に設定、戻り値はない

CREATE PROCEDURE myProc(IN aft_date DATETIME)

BEGIN

  DECLARE done INT DEFAULT FALSE;#ループを抜けるフラグ

  DECLARE dt DATETIME;#日付ごとにループ

  DECLARE tdays CURSOR FOR SELECT aod as as_of_date FROM date_ots where aod > aft_date order by aod asc;#ループする日付たち

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;#日付が全部回ったら終了フラグ



  OPEN tdays;#取得する



  read_loop: LOOP#ループ開始

    FETCH tdays INTO dt;

    IF done THEN

      LEAVE read_loop;#ループを抜ける

    END IF;



insert into fund_correction_indices

select null,f.id,rt.aod,0,ci4.correction_value * rt.rate1 * rt.rate2 * rt.rate3 * rt.rate4,ci4.correction_value * rt.rate1 * rt.rate2 * rt.rate3 * rt.rate4,0

from 

(

    select dto.aod,f.fund_small_category_id,

        avg(ci0.correction_value/ci1.correction_value) as rate1,

        avg(ci1.correction_value/ci2.correction_value) as rate2,

        avg(ci2.correction_value/ci3.correction_value) as rate3,

        avg(ci3.correction_value/ci4.correction_value) as rate4,

        avg(ci0.correction_value/ci4.correction_value) as rate_,

        dto.4thd 

    from date_ots dto 

        inner join fund_correction_indices ci0 on ci0.as_of_date = dto.aod

        inner join funds f on f.id = ci0.fund_id and f.kind not in ('平均','指数') 

        inner join fund_correction_indices ci1 on ci1.as_of_date = dto.1std and ci1.fund_id = ci0.fund_id

        inner join fund_correction_indices ci2 on ci2.as_of_date = dto.2ndd and ci2.fund_id = ci0.fund_id

        inner join fund_correction_indices ci3 on ci3.as_of_date = dto.3rdd and ci3.fund_id = ci0.fund_id

        inner join fund_correction_indices ci4 on ci4.as_of_date = dto.4thd and ci4.fund_id = ci0.fund_id

    where dto.aod = dt

        and f.fund_small_category_id >=1 and f.fund_small_category_id <=57

    group by fund_small_category_id,dto.aod

) rt

    inner join funds f on f.fund_small_category_id = rt.fund_small_category_id and f.kind = '平均'

    inner join fund_correction_indices ci4 on ci4.as_of_date = rt.4thd and ci4.fund_id = f.id

order by f.id,rt.aod 

ON DUPLICATE KEY UPDATE 

  value=ci4.correction_value * rt.rate1 * rt.rate2 * rt.rate3 * rt.rate4,

  correction_value=ci4.correction_value * rt.rate1 * rt.rate2 * rt.rate3 * rt.rate4;

  END LOOP;

  CLOSE tdays;

END



呼び出し

CALL myProc('2012-02-01')


もしCREATE PROCEDUREの時「Column count of mysql.proc is wrong」みたいなエラーが出たら

$ mysql_upgrade -uroot -p
rootパスワード入力

で直してあげてください。

INSERT ON DUPLICATE KEY UPDATE

feel this useful,copy from here
http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update


CREATE TABLE `users_partners` (
`uid` int(11) NOT NULL DEFAULT '0',
`pid` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`uid`,`pid`),
KEY `partner_user` (`pid`,`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

INSERT INTO users_partners (uid,pid) VALUES (1,1);
...1 row(s) affected

INSERT INTO users_partners (uid,pid) VALUES (1,1);
...Error Code : 1062
...Duplicate entry '1-1' for key 'PRIMARY'

INSERT IGNORE INTO users_partners (uid,pid) VALUES (1,1);
...0 row(s) affected

INSERT INTO users_partners (uid,pid) VALUES (1,1) ON DUPLICATE KEY UPDATE uid=uid
...0 row(s) affected

REPLACE INTO users_partners (uid,pid) VALUES (1,1)
...2 row(s) affected

INSERT INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...Error Code : 1062
...Duplicate entry '1-1' for key 'PRIMARY'

INSERT IGNORE INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...3 row(s) affected

INSERT INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4) ON DUPLICATE KEY UPDATE uid=uid
...3 row(s) affected

REPLACE INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...5 row(s) affected


SQLOLEDB INSERT トランザクション付き

Sub InsertDB()
    Global cnn As New ADODB.Connection
    cnn.Open "Provider=SQLOLEDB;" & _
             "Data Source=" & srvName & ";" & _
             "Initial Catalog=" & databaseName & ";", user, pass

    Dim cmdIns As ADODB.Command
    Set cmdIns = New ADODB.Command
    cmdIns.ActiveConnection = cnn
    cmdIns.CommandTimeout = 0
    cmdIns.CommandType = adCmdText
    cmdIns.CommandText = "INSERT INTO myTable(id,name,price,bigmoney) VALUES(?,?,?,?)"

    'DB定義: id int
    Set param = cmdIns.CreateParameter("id", adInteger, adParamInput)
    cmdIns.Parameters.Append param

    'DB定義: name nvarchar(50)
    Set param = cmdIns.CreateParameter("name", adVarWChar, adParamInput, 100)
    cmdIns.Parameters.Append param

    'DB定義: price decimal(12, 6)
    Set param = cmdIns.CreateParameter("price", adDecimal, adParamInput)
    param.Precision = 12 '全桁数
    param.NumericScale = 6 '小数桁数　こんな感じ：123456.123456
    cmdIns.Parameters.Append param

    'DB定義: price bigmoney(12, 0)
    Set param = cmdIns.CreateParameter("bigmoney", adDecimal, adParamInput)
    param.Precision = 12 '全桁数
    param.NumericScale = 0 '小数桁数　こんな感じ：123456123456
    cmdIns.Parameters.Append param

    cmdIns.Parameters("id").Value = 1
    cmdIns.Parameters("name").Value = "かげんぱぱ"
    cmdIns.Parameters("price").Value = 1999.123
    cmdIns.Parameters("bigmoney").Value = 10000000000

    cnn.BeginTrans 'トランザクション開始
    cmdIns.Execute 'インサート実行
    'cnn.RollbackTrans 'ロールバック
    cnn.CommitTrans 'コミット
End Sub

mysqlテーブルコピー


/* スキーマをコピーしてテーブル作成 */
> CREATE TABLE mytable_copy LIKE mytable;
/* mytableテーブルのデータをINSERT */
> INSERT INTO mytable_copy SELECT * FROM mytable;


googleだからできること

面白い！
Googleスプレッドシートを新規して
A1セルに「mazda」
A2セルに「honda」
そしてctrlを押しながら、引っ張ったら…ほらね！

vbaで定義済みの名前NamesとRangeの参照

Sub name2range_test()
Debug.Print ThisWorkbook.Names.Count
For i = 1 To ThisWorkbook.Names.Count
    Debug.Print ThisWorkbook.Names.Item(i).Name & " -> " & ThisWorkbook.Names.Item(i).RefersTo
    Debug.Print ThisWorkbook.Names.Item(i).Name & " -> " & ThisWorkbook.Names.Item(i).RefersToRange.Address
Next
'あるいは
Debug.Print Range(ThisWorkbook.Names.Item(1).RefersTo).Address

End Sub