タグ: DB

数値(整数)チェックをしようと、

Function CheckNumber(v as Varient) As Boolean
  CheckNumber = True
  If Len(v)=0 Then Exit Function
  If IsNumeric(v) Then Exit Function
  CheckNumber = False
End Function

とか記載したら、vに「a」とか入れてもTrueで返ってきてしまった。

Function CheckNumber(v as Varient) As Boolean
  CheckNumber = True
  If Len(Cstr(v))=0 Then Exit Function
  If IsNumeric(Cstr(v)) Then 
    If InStr(Cstr(v),".")=0 Then Exit Function '小数じゃなかったら(簡易チェック)
  End If
  CheckNumber = False
End Function

みたいに、文字列に変えてからIsNumericしたらうまくいった。
関数の引数を文字列型にするってのも手だけど、簡易版なのでこれでいいかなと思った。

SQLiteでテーブルの一覧を取得する場合、コマンドの「.schema」を使う方法もあるが、面倒な時もあるので、以下のようにSELECT文を使う。

SELECT * FROM sqlite_master
WHERE type = 'table' 
ORDER BY name;

WHERE句の条件を外すと、オブジェクトの一覧になって、INDEXの情報なんかも取得できる。

さて、6月も中盤。
4月から3か月間かけて行う新人研修も終盤だが、どうも、今年はイマイチ。
新人がイマイチなのか、我々がイマイチなのか、どちらもダメなのか・・・

それはともかく、中学生や高校生が読めるような漢字を読めない人がいるのがまずウンザリ。
昨今、高等教育の無料化を声高に言う人がいるが、そもそも、初等・中等教育をしっかりしてほしい。
小学生でも読めそうな漢字も読めない大人って、社会に必要なのか？
初歩的な現代国語ぐらいちゃんと勉強させてほしいものだ。

同じIDのTITLEデータをカンマ区切りでとってきたい！
とか思って漁っていたら、

MySQLでidだけをSELECTして、カンマ区切りにして出力する

という、ページが見つかった。
GROUP_CONCATという関数を使い、区切り文字をカンマに指定する方法である。

SELECT GROUP_CONCAT(title SEPARATOR ',') FROM t_sample WHERE id = 1;

PostgreSQLだと、ARRAY_TO_STRING

＜参照＞PostgreSQL覚書 配列

世の中には、ありがた迷惑とか、小さな親切大きなお世話とか、そういうことがわからない人が一杯いるようだ。
そういう人は、「やるな！」「やらないでくれ！」っていっても、「やる」のだなぁ。
人のやること横取りするんじゃないよ！と、叫びたかった、今日の朝。

それはそうと、SQLiteでSELECT文を使ってUPDATEしたい！というのがあって、
そうすると、まぁ、

　UPDATE table1 SET
 　col1 = (SELECT col1 FROM table2 WHERE .... )

みたいにすればできるぜ！みたいなことはいっぱい書いてあるんだけど・・・

　そうじゃないんです！
　JOINで連結させて、ホゲホゲホゲ・・・とやりたいんです！

という要求は満たされないわけ。

で、調べた。

SQLiteはWITH句が使えるらしい。
んで、WITH句をうまく活用して、UPDATEできるらしい。

他のDBのSQL文を

UPDATE table1 SET
    col1 = a.col1
  , col2 = a.col2
 FROM (SELECT .... FROM .... INNER JOIN ....) a
 WHERE table1.id = a.id

とすると、
WITH句を使ったSQL文は

WITH a AS (
  SELECT .... FROM .... INNER JOIN ....
)
UPDATE table1 SET
    col1 = (SELECT col1 FROM a WHERE table1.id = a.id)
  , col2 = (SELECT col2 FROM a WHERE table1.id = a.id)

みたいな感じ。

で、これを私が愛用する「A5:SQL Mk-2」（SQL開発ツール）で実行したのだが、うまくいかずに、「あれ？できないの？」とか思って悩んだのだが、ツール上ではうまくいかないだけで、SQLiteのコマンド実行では、ちゃんと動いた。

ホント、統一させてほしいんですけど。

Processing開発。
BezierSQLibライブラリを使ってSQLiteのDBにつないでいるのだが、１つ問題が。

というのも、エラーになったときExceptionが発生しない。
ライブラリ内部で例外処理までしてるのは兎も角、結果がエラーになってもエラーを判別できるフラグがない。
DBにLockがかかってる時にUPDATEしても、コンソールにエラーメッセージは出るが、それだけ・・・
Rollbackするタイミングがつかめない。
このままではマズイ。困った。

こうなったら、直接jdbcライブラリを利用しよう！となった。

プログラムソースのpdeファイルが入っているフォルダの下に「data」フォルダを作って、その中にDBファイルの「test.db」を入れる。
「data」フォルダと同じ並びに「code」フォルダを作って、その中にライブラリ「sqlite-jdbc-***.jar」ファイルを入れておく。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

// ------------------- ( 略 ) -------------------

void getData() {
  Connection con = null;
  Statement stmt = null;
  try {
    Class.forName("org.sqlite.JDBC");
    con = DriverManager.getConnection("jdbc:sqlite:"+ dataPath("test.db"));
    println("Opened database successfully");
    
    stmt = con.createStatement();
    stmt.executeUpdate("INSERT INTO m_users (user_cd ,user_name) VALUES ('1010101', 'Lily');");
    stmt.close();
    con.close();
  } catch ( Exception e ) {
    println( e.getClass().getName() + ":" + e.getMessage());
  }
}

Macでいけたから、あとはWindowsでも確認しよう。

データが1万→2万になったところで、急激にUPDATEの速度が遅くなってしまった。
SQLiteって速いって聞くんだけど・・・
ということで、チューニングについて調べてみた。
INSERTが遅いってのがあったけど、Transactionは入れてるし。
UPDATEが遅いっていわれても、そこを変えるのは・・・と思ったので、以下のサイトを参考に、コマンドプロンプト上でDBの設定をかえた。
パフォーマンスを追求するためのSQLite設定 | ITハンドブック
SQLite「PRAGMA statement」

sqlite> PRAGMA main.journal_mode = PERSIST;
sqlite> PRAGMA main.synchronous=0;

mainはスキーマ名。
で、どうなったかというと、設定を変更後、一回VACUUMをしてから確認したら、劇的に速度向上？

＜追記＞
あぁ・・・勘違いしてた。
どうやら、DBを開いているときだけ設定した状態が保持される様子。

SQLite が認識できる SQL

閉じちゃったら元のDefault設定に戻るようだ。
たしかに、開き直したら設定が消えちゃってたよ。

開いた直後だな。設定するのは。

＜追記2 2016/09/14＞
さて、さて、いろいろ試した結果。

結果的に設定を変えて高速化したか？といえば、さほどな気はした。
やはり、他のSQLと同様、INDEXをはるってのが１番で、あとはなるべく実行しない方法を探すのがよいようで。
次に、TRANSACTION。これはかなり大きかった。1,2万行を順にINSERTしていく手前でBEGIN→終了時COMMITとするだけだが、これも速度がだいぶん速くなったように感じた。
DELETE＆INSERTよりREPLACEを使うのがいいと書いている人もいるが、
目からウロコだったのは、INSERT文のVALUES部にUNION ALLしたSELECT文を用いるやり方。

SQLiteで最も速く複数行INSERTする方法 | transhumanist note
要は、複数行の実行をお纏めしちゃうわけで、これはなかなか面白いやり方だと思った。

まぁ、あとできることはVACUUMしてゴミを減らすことかな・・・

さて、最近くだらないことをまじめに書いているので、久しぶりに技術的なことを軽くメモ書きしておこう。

8桁数値を日付(yyyy/mm/dd)表示にする方法

SELECT CONVERT(varchar,CONVERT(datetime, CONVERT(varchar,20160223), 112),111)

1つ目(内側から)は文字列に変換、2つ目のCONVERTではDATETIME型に変換するので、出力としては「2016/02/23 0:00:00」というように日付と時間で出力される。
これを「yyyy/mm/dd」形式の文字列で出力させるために3つ目(一番外側)のCONVERTを用意。

これとは逆に日付を8桁数値に変える場合は、データを「yyyymmdd」の文字列で出力した後、INT(またはNUMERIC)型に変えてしまえばよい。

SELECT CONVERT(int,CONVERT(varchar, '2016-2-14', 112))

昔やったのに忘れてしもーた・・・
と、Webで調べたDBLINK。

「CREATE EXTENSION DBLINK;とやったら使えるよ！」みたいなサイトがあったからやってみたら、postgreさんに「なんじゃい！」って怒られたので、過去のドキュメントを引っ張り出してきた。

「そもそも、モジュールあらへんやん、あんた」みたいな話なので、
コマンドプロンプトを立ち上げて、

cd /d C:\Program Files\PostgreSQL\9.0
bin\psql -U {USER_ID} -d {DBNAME} < share\contrib\dblink.sql でPWを入れてDBLINKを使えるようにすると、関数としてDBLINKが使えるようになった。

1年分の日付データを作りたい。もちろん一括で。
できないものかと探すと、あった。

SELECT arr.i AS date1
 FROM
 generate_series(
   cast('2016-1-1' as timestamp)
 , cast('2016-1-1' as timestamp) +  '12 months' + '-1days'
 , '1days'
 ) as arr(i)

Series Generating Functions(集合を返す関数）・・・

よくわかりヘンが、generate_seriesの1つめの引数をスタートとして、2つ目の引数まで1データずつレコードを作成してくれる便利なもの。
1,2の引数がINT型であれば、3つ目の引数はなく、＋1しながらレコードが作られる。
1,2の引数がTIMESTAMP型であれば、3つ目の引数にインターバル設定をして、インターバル設定分プラスしながらレコードが作られる。（上の例だと1日ずつ）

参考URL：Postgresqlでカレンダーを使わずに日付を列挙する方法(generate_series)

主キーの設定と解除のSQL。

ALTER TABLE {TABLE} ADD CONSTRAINT {KEY} PRIMARY KEY ({COLUMNS});
ALTER TABLE {TABLE} DROP CONSTRAINT {KEY};

{TABLE}：テーブル名
{KEY}：主キー名
{COLUMNS}：列名(複数の場合はカンマで区切って指定）

これは一般的な１か月後

SELECT current_timestamp + interval  '1months'

動的に算出する場合の例

SELECT current_timestamp + CAST(a.add_months || 'months' AS interval)
FROM table_a as a

CASTを簡単に書くと

SELECT current_timestamp + (a.add_months || 'months')::interval
FROM table_a as a

という感じ。

dqyファイルは便利だ。
わざわざ、PhpAdminやらManagerやら開けずにSQL文を実行して、データが確認できる。

自分がよく使うPostgreSQLとSQLServerのクエリファイルの書き方メモ。

PostgreSQL

XLODBC
1
Driver=PostgreSQL Unicode;Server={IPアドレス等};Port={PortNo};Database={DB名};Uid={UserID};Pwd={Password};
SELECT TOP 100 * FROM batch_log ORDER BY create_date desc

SQLServer

XLODBC
1
Driver=SQL Server;Server={IPアドレス等};Uid={UserID};Pwd={Password};Database={DB名};Connect Timeout=15;
SELECT * FROM batch_log ORDER BY create_date desc LIMIT 1;

SELECT文は改行すると動作しないときがあるので注意

カンマ区切りのデータを縦に並べるプロシージャを作ってみた。

CREATE PROCEDURE csvToRows
    @csv VARCHAR(MAX)
AS
BEGIN

    DECLARE @exit_flg       TINYINT       = 0
    DECLARE @i              INT           = 1
    DECLARE @imax           INT
    DECLARE @buf            VARCHAR(MAX)
        
    BEGIN TRY
        -- はじめにNULLや空白を回避
        IF RTRIM(LTRIM(ISNULL(@csv,'')))='' 
        BEGIN
            RAISERROR (N'NULLはダメ！', 18, 1)
        END

        CREATE TABLE #w_temp (
            buf            VARCHAR(MAX)
        )
        
        SET @buf = @csv
        WHILE @exit_flg = 0
        BEGIN
            SET @buf = SUBSTRING(@buf, @i  , LEN(@buf))
            SET @imax = CHARINDEX(',', @buf)
            IF @imax = 0
            BEGIN
                INSERT INTO #w_temp
                SELECT SUBSTRING(@buf, 1, LEN(@buf)) 
                SET @exit_flg = 1
            END
            ELSE
            BEGIN
                INSERT INTO #w_temp
                SELECT SUBSTRING(@buf, 1, @imax - 1) 
                SET @i = @imax + 1
            END
        END
        SELECT 'OK' AS result, NULL AS err_msg, buf FROM #w_temp

    END TRY
    BEGIN CATCH
        
        SELECT 'NG' AS result, ERROR_MESSAGE() AS err_msg
        
    END CATCH
END

「SQLSeverの2014ぐらいでないとTHROWが使えない」ってことで、RAISERRORでエラーを発生させてCATCH句に強制的に移動するように。

したはずなんだけど、いかないのは何故？

って、原因は重要度に設定する値にあった。

重要度＝1～10の場合、CATCH句へ飛ばない。

RAISERROR (N'エラーメッセージ', 10, 1);

重要度＝11～18の場合、CATCH句へ飛び、18までなら普通のユーザでも指定できる。

RAISERROR (N'エラーメッセージ', 18, 1);

重要度＝19～の場合、sysadminとか権限のあるユーザでないと、そもそも設定できない。

RAISERROR (N'エラーメッセージ', 19, 1);

ということで、11～18の値を設定することで、無事CATCH句に飛びましたとさ！

関数の戻り値というと、普通は１つの値なのだけれど、複数行とってきたいとか、テーブルとしてとってきたいとか思う。

問い合わせ言語（SQL）関数

を見ているとできなくないらしいけれど、

CREATE FUNCTION sum_n_product_with_tab (x int)
    RETURNS TABLE(sum int, product int) AS $$
    SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;

で、LANGUAGEが「SQL」となっていて、「plpgsql」で作っているものだとRETURNSの後ろをTABLE(ほにゃほにゃ)に変えただけではうまくいかなかった。
戻り値が
sum | product
—–+——————
50 | aaa
66 | bbb

とかになってほしいんだけど、
sum_n_product_with_tab
———————–
( 50, aaa)
( 66, bbb)
になってしまって、せっかくTABLEにしている意味ないんじゃない？ということになった。

ふむ。どうすればよいのじゃ・・・

ということで、せめて複数行とってくることにしよう。

CREATE OR REPLACE FUNCTION hoge(parm_cd character varying)
  RETURNS numeric  AS
$BODY$
DECLARE
    ・・・・
BEGIN

  CREATE TEMP TABLE wk_master(code numeric(2,0))
  ・・・・
  RETURN QUERY SELECT code FROM wk_master;
  DROP TABLE wk_master;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

RETURN QUERY・・・とすると、複数行で返してくれる。

今日はこの辺で終了。

またあっという間に時が経った。
師走だ。

WindowsバッチでAサーバにあるDBをBサーバに移すことにした。

んで、とりあえずいろいろ参考にして作ったのが下の例。

まずはパスワードを設定しておいて、途中で聞かれないようにしてから・・・
pg_dump を使って D:\backup\test.backupに 111.111.111.111:5432 のA1_DBをバックアップ。
(ユーザはpostgres)
バックアップファイルからpg_restoreを使って、111.111.111.123:5432 のA2_DBへリストア。
(こっちもユーザはpostgres)

—————————————————————–
SET PGPASSWORD=password
“C:\Program Files\PostgreSQL\9.3\bin\pg_dump.exe” -f D:\backup\test.backup -i -v -h 111.111.111.111 -p 5432 -U “postgres” -Fc “A1_DB”
if not %ERRORLEVEL%==0 stop

“C:\Program Files\PostgreSQL\9.3\bin\pg_restore.exe” -c -h 111.111.111.123 -p 5432 -U “postgres” -Fc -d “A2_DB” D:\backup\test.backup

stop
—————————————————————–
オプション関連はドキュメントを見てもらえばいいと思うけど、書き順やらがドキュメントではいまいちわからんかったのでメモっとく。

ストアドプロシージャ内で、別のストアドプロシージャを呼び出す方法。

例えば、proc_GetEmployeeIn(引数:@bumon_cd) を呼び出して結果を取得する場合だと、
以下のように、あらかじめワークテーブルを作っておいてから、
INSERT INTO ワークテーブル EXEC プロシージャ名 引数1, 引数2 , …
というようになる。

CREATE TABLE #work_table (
      emp_cd VARCHAR(1000)
    , emp_name VARCHAR(10)
)
INSERT INTO #work_table EXEC proc_GetEmployeeIn @bumon_cd

いつのまにか10月になって、いつのまにかWordPressがVer4.0になってた。
時の経つのは早いもの。

PostgreSQLにはIsNumericとかIsNumberみたいな関数はないのねぇ・・・
Function作ればいいんだよ！って、言われてもねぇ・・・

ということで、代替案を漁ってみた。

SELECT (CASE WHEN emp_cd ~'^[0-9]*$' THEN emp_cd ELSE NULL END) emp_cd FROM master_table

ってな感じで、正規表現を使うらしい。
関数作って使ったほうが楽じゃね？って言わないでね・・・

昔バグった話。
接続しっぱなしでロジックを終わらせてたら、どんどん接続数が増えて処理できなくなった。
CLOSEすんのを忘れてたんです。スイマセン。

で、大騒ぎになる前に。

SELECT * FROM pg_stat_activity
 ORDER BY usename,application_name;

で、ちゃんとクローズしたことを確認する。
application_nameがpgAdmin以外で、IPアドレスを確認して、current_queryがアイドル状態になっておらず、SQL文が設定されたままだとクローズしていない。
処理が終わってもこういうのが残っていると、ちゃんと切れてないので切断すること。

このバグのせいで、運用問題にまで発展しテンヤワンヤとなったので、メモ。

SQLServerで縦並びになっているデータを横並び（カンマ区切り）にして出力したくなった。

【TABLE1】
CD | KOMOKU
1 | A
1 | B
2 | C
2 | D
【TABLE2】
CD | CNAME
1 | あ
2 | い

ってあった場合、

CD | CNAME | KOMOKU
1 | あ | A,B
2 | い | C,D

って出したい！ってことで、調べた結果、

select 
 TABLE2.*
 ,replace((select KOMOKU  AS [data()] 
 from TABLE1
 where (TABLE1.CD=TABLE2.CD)
 for xml path('')
 ),' ',',') AS KOMOKU
 from TABLE2

ってな感じに記述するとのこと。
KOMOKUにスペースが含まれているとこれってうまくいかないんだよねぇ・・・とか思いつつ、自分の場合は特に問題なかったのでOK。
[data()] ってのがアトミック値にするっていうことらしいがよくわからんかった。
（分解できないとか、処理速度が速いとか書いてあったけど、結局よくわからん・・・）