キムラデービーブログ

オープンソースデータベースを加速する「キムラデービー」のブログです。カレー日記を兼ねてます。なお著者は2010-06-01より日本オラクルに在籍していますが、本サイト(ブログ、またはウェブサイト)において示されている見解は、私自身の見解であって、オラクルの見解を必ずしも反映したものではありません。
最新のSQL標準(SQL:2023)とFirebird/MySQL/PostgreSQL。続き。
0
    本エントリは 以下のAdvent Calendarの15日目のクロスエントリです。

    Firebird Advent Calendar 2023
    MySQL Advent Calendar 2023
    PostgreSQL Advent Calendar 2023

    SQL標準は数年ごとにリリースされて、最新のものはSQL:2023となります。
    原本は有償での販売になりますので、直前のドラフトや、紹介記事を元に内容をつかんでます。
    今回は以下のエントリを参照しています。

    SQL:2023 is finished: Here is what's new(Peter Eisentraut)

    今回の変更・追加点の概略は以下のものになります。

    1.既存の SQL 言語に対する(小さい)さまざまな変更
    2.JSONに関連する新しい機能
    3.プロパティグラフクエリ(PGO)の新しいパート

    1.の多くのパートはFirebird Advent Calendar 2023の2日目から8日目まででMySQL/PostgreSQLも含めて紹介してきました。
    今回はその続き10〜12日目で、以下の三つとなります。

    SQL:2023 Firebirdの場合:ANY_VALUE (T626)
    SQL:2023 Firebirdの場合:Non-decimal integer literals (T661)
    SQL:2023 Firebirdの場合:Underscores in numeric literals (T662)


    上記の項目について以下はそれぞれの対応表となります。

    SQL:2023 Firebird MySQL PostgreSQL
    T626   ×    〇5.7 〇16
    T661   △    △  〇16
    T662   ×    ×  〇16

    さすがにPostgreSQL、ここでも手堅いですねぇ。

    JUGEMテーマ:コンピュータ



    | meijik | クロスデータベース | 23:40 | comments(0) | - | - | - |
    最新のSQL標準(SQL:2023)とFirebird/MySQL/PostgreSQL
    0
      最新のSQL標準(SQL:2023)とFirebird/MySQL/PostgreSQL

      本エントリは 以下のAdvent Calendarの九日目のクロスエントリです。

      Firebird Advent Calendar 2023
      MySQL Advent Calendar 2023
      PostgreSQL Advent Calendar 2023
      JPOUG Advent Calendar 2023

      SQL標準は数年ごとにリリースされて、最新のものはSQL:2023となります。
      原本は有償での販売になりますので、直前のドラフトや、紹介記事を元に内容をつかんでます。
      今回は以下のエントリを参照しています。

      SQL:2023 is finished: Here is what's new(Peter Eisentraut)

      今回の変更・追加点の概略は以下のものになります。

      1.既存の SQL 言語に対する(小さい)さまざまな変更
      2.JSONに関連する新しい機能
      3.プロパティグラフクエリ(PGO)の新しいパート

      1.の多くのパートはFirebird Advent Calendar 2023の2日目から8日目まででMySQL/PostgreSQLも含めて紹介してきました。

      SQL:2023 Firebirdの場合:UNIQUE null treatment (F292)
      SQL:2023 Firebirdの場合:ORDER BY in grouped table (F868)
      SQL:2023 Firebirdの場合:GREATEST and LEAST (T054)
      SQL:2023 Firebirdの場合:String padding functions (T055)
      SQL:2023 Firebirdの場合:Multi-character TRIM functions (T056)
      SQL:2023 Firebirdの場合:Optional string types maximum length (T081)
      SQL:2023 Firebirdの場合:Enhanced cycle mark values (T133)

      F292とF868は実装を明確にしただけのものです。それ以降の項目について以下はそれぞれの対応表となります。

      SQL:2023 Firebird MySQL PostgreSQL
      T054   △    〇  〇
      T055   〇    〇  〇
      T056   ×    ×  〇
      T081   ×    ×  〇
      T133   ×    ×  〇

      さすがにPostgreSQL、手堅いですねぇ。
      来週以降もFirebird Advent Calendar 2023で順にみていくのでお楽しみに!

      2.はJSONがよく使われてくるようになったあらわれだと思います。PostgreSQLが先行して、その後MySQLや商用RDBMSが追随したJSON実装。今後も手堅い拡張が期待できます。

      3.はグラフクエリ言語がSQL標準に取り入れられた結果です。
      グラフクエリ言語は様々なものがあり、まさに群雄割拠な状況なのですが、SQL側から標準が定められたことにより
      SQL標準のようにまとまってくるといいですね!

      グラフクエリのそれぞれの立ち位置は以下の図がわかりやすいです。

      gql-sql-pgq-pointers/firures/graph-query-languages.png

      あと、PostgreSQL Conference 2023の案浦さんの発表がわかりやすかったですが、今のところまだスライドは公開されていないようです。

      【A1】PostgreSQL and SQL:2023 - Property Graph Queries の話題と各RDBの実装

      [2024-01-28 追記]
      2023年末に資料が公開されました。(北山さんThanks!)

      PostgreSQL and SQL:2023 - Property Graph Queries の話題と各RDBの実装

      JUGEMテーマ:コンピュータ



      | meijik | クロスデータベース | 14:35 | comments(1) | - | - | - |
      MySQLとSQLiteからCSVファイルにSQLを投げてみた
      0
        MySQLとSQLiteからCSVファイルにSQLを投げてみた

        本エントリは以下のAdvent Calendar 18日目のクロスエントリです。

        o SQLite Advent Calendar 2022
        o MySQL Advent Calendar 2022

        ほとんどのRDBMSでは、CSVファイルをデータベースにインポートする機能があります。
        ただ、ちょっとCSVファイルを覗いてみる、しかもSQLを使って、ということになると、いちいちロードするのは大変!
        一部のRDBMSには、CSVファイルを直接扱う機能があります。今回はSQLiteとMySQLについて紹介します。

        SQLite
        以前のブログでとりあげた仮想テーブルの機能を利用するとCSVファイルを直接扱えます。

        前回紹介した以下のページはLinuxでの利用方法が紹介されていましたので、ここではWindowsで扱う方法を同様に紹介します。

        SQLiteからCSVファイルにSQLを投げてみた

        この記事同様に最新の本体ソースコードをダウンロードします。
        2022年12月現在では、sqlite-amalgamation-3140100.zip (1.89 MiB) ではなく、
        sqlite-amalgamation-3400000.zip(2.48 MiB)になります。

        https://www.sqlite.org/download.html からダウンロードし、ソースコードを展開しておきます。

        本体のコンパイルには、以前紹介した以下の記事のとおり行います。

        Windows上で、SQLiteのGeopolyを使う(ということはMSVCでコンパイルするのねん)

        メニューから開発コマンドプロンプト(Developper Command Prompt)を開き、以下のコマンドを発行します。

        cl shell.c sqlite3.c -Fesqlite3.exe

        今度はCSV用のDLLを作成します。同じディレクトリに以下のページからダウンロードします。

        https://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/csv.c

        ジャンプ先の「Download」ボタンを押してください。

        ダウンロードされたCSV.cファイルを指定して、DLLとして作成(/LD)します。

        cl /I. /LD csv.c

        これでcsv.dllが作成されます。

        /Iで.(カレントディレクトリ)を指定しないと、以下のエラーになりますのでご注意ください。

        csv.c(42): fatal error C1083: include ファイルを開けません。'sqlite3ext.h':No such file or directory

        参照先のブログにある(de.csv)を用意して、Windowsでも実際に使ってみましょう。

        C:¥Users¥mekimura¥Downloads¥sqlite-amalgamation-3400000>sqlite3 test.db
        SQLite version 3.40.0 2022-11-16 12:10:08
        Enter ".help" for usage hints.
        sqlite> .load csv.dll

        sqlite> CREATE VIRTUAL TABLE temp.foo USING csv(filename='de.csv');
        sqlite> select * from foo;
        Berlin|3426354
        Hamburg|1739117
        Munich|1260391
        Cologne|963395
        Frankfurt am Main|650000
        Essen|593085
        Stuttgart|589793
        Dortmund|588462
        Dusseldorf|573057
        Bremen|546501

        参照先のブログにあるように読み込み専用のようです。

        sqlite> insert into foo values('foo', 300);
        Parse error: table foo may not be modified

        それ以外も同様ですが、一点改善されており、headerが有効になります。
        以下の例ではheaderを指定したので、一行目がカラム名としてあつかわれ、二行目からの読み込みになっています。

        sqlite> CREATE VIRTUAL TABLE temp.foo2 USING csv(filename='de.csv',header);
        sqlite> select * from foo2;
        Hamburg|1739117
        Munich|1260391
        Cologne|963395
        Frankfurt am Main|650000
        Essen|593085
        Stuttgart|589793
        Dortmund|588462
        Dusseldorf|573057
        Bremen|546501



        MySQL
        MySQLのほうはもっと簡単で、デフォルトの構成でCSVストレージエンジンが用意されています。

        16.4 CSV ストレージエンジン

        testデータベースで以下のコマンドを発行します。

        mysql> create table de(city TEXT,population INTEGER) engine=csv;
        ERROR 1178 (42000): The storage engine for the table doesn't support nullable columns

        NULLは許容できないので、not nullをつけ、text型をvarcharに変更します。

        mysql> create table de(city varchar(20) not null,population INTEGER not null) engine=csv;
        Query OK, 0 rows affected (0.03 sec)

        datadir/testを見ると以下のように三つのファイルができています。

        2022/12/19 04:15 35 de.CSM
        2022/12/19 04:15 0 de.CSV
        2022/12/19 04:15 2,527 de_2140.sdi

        # MySQL 8.0以降はこうなりますが、5.7以前はde_2140.sdiの代わりにde.FRMが生成されます。
        このうちのde.csvを差し替えて、flush tables;すれば内容が反映されます。

        mysql> select * from de;
        +-------------------+------------+
        | city | population |
        +-------------------+------------+
        | Berlin | 3426354 |
        | Hamburg | 1739117 |
        | Munich | 1260391 |
        | Cologne | 963395 |
        | Frankfurt am Main | 650000 |
        | Essen | 593085 |
        | Stuttgart | 589793 |
        | Dortmund | 588462 |
        | Dusseldorf | 573057 |
        | Bremen | 546501 |
        +-------------------+------------+
        10 rows in set (0.03 sec)

        CSVファイルの差し替えは以下のブログでも扱われています。

        たった3秒でInnoDBのデータローディングが快適になるライフハック

        SQLiteとは違って更新も可能です。

        mysql> insert into de values('test',1000);
        Query OK, 1 row affected (0.02 sec)

        mysql> select * from de;
        +-------------------+------------+
        | city | population |
        +-------------------+------------+
        | Berlin | 3426354 |
        | Hamburg | 1739117 |
        | Munich | 1260391 |
        | Cologne | 963395 |
        | Frankfurt am Main | 650000 |
        | Essen | 593085 |
        | Stuttgart | 589793 |
        | Dortmund | 588462 |
        | Dusseldorf | 573057 |
        | Bremen | 546501 |
        | test | 1000 |
        +-------------------+------------+
        11 rows in set (0.00 sec)

        ただ文字列は"(ダブルクォーテーション)付で扱われるため、実際に更新などするのであれば、文字列型は"付きであわせておきましょう。

        Berlin,3426354
        Hamburg,1739117
        Munich,1260391
        Cologne,963395
        Frankfurt am Main,650000
        Essen,593085
        Stuttgart,589793
        Dortmund,588462
        Dusseldorf,573057
        Bremen,546501
        "test",1000

        JUGEMテーマ:コンピュータ



        | meijik | クロスデータベース | 23:39 | comments(0) | - | - | - |
        実行計画: メモリを使ったソート
        0
          実行計画: メモリを使ったソート

          本エントリは次のAdvent Calendar 2022 17日目のクロスエントリです。

          SQLite Advent Calendar 2022
          Firebird Advent Calendar 2022

          SQLiteもFirebirdもソートに利用できるインデックスがない場合には、メモリに領域をとりソートを行います。

          SQLiteでは以下のテーブルとデータを作成して確認してみましょう。

          EXPLAIN QUERY PLAN

          CREATE TABLE t2(c int not null, d int not null, primary key(d));
          insert into t2
          WITH recursive NumberTable(SeqNo1, SeqNo2)
          AS
          (
          SELECT 1, 1
          UNION ALL
          SELECT 1 + SeqNo1, 1 + SeqNo1
          FROM NumberTable
          WHERE SeqNo1 < 1024
          )
          SELECT SeqNo1, SeqNo2 FROM NumberTable;

          カラムcでソートすると、インデックスがないので、メモリに領域をとりソートします。
          sqlite> explain query plan select c,d from t2 order by c;
          QUERY PLAN
          |--SCAN TABLE t2
          `--USE TEMP B-TREE FOR ORDER BY

          カラムdは主キーがあるので、それでソートされます。
          sqlite> explain query plan select c,d from t2 order by d;
          QUERY PLAN
          `--SCAN TABLE t2 USING INDEX sqlite_autoindex_t2_1

          Firebirdでも同じことをしてみます。

          CREATE TABLE t2(c int not null, d int not null, primary key(d));
          insert into t2
          WITH recursive NumberTable(SeqNo1, SeqNo2)
          AS
          (
          SELECT 1, 1 from rdb$database
          UNION ALL
          SELECT 1 + SeqNo1, 1 + SeqNo1
          FROM NumberTable
          WHERE SeqNo1 < 1024
          )
          SELECT SeqNo1, SeqNo2 FROM NumberTable;

          旧フォーマット
          SQL> set planonly;
          SQL> select c,d from t2 order by c;

          PLAN SORT (T2 NATURAL)

          SQL> select c,d from t2 order by d;

          PLAN (T2 ORDER RDB$PRIMARY1)

          新フォーマット
          SQL> set explain;
          SQL> select c,d from t2 order by c;

          Select Expression
            -> Sort (record length: 36, key length: 8)
              -> Table "T2" Full Scan

          SQL> select c,d from t2 order by d;

          Select Expression
            -> Table "T2" Access By ID
              -> Index "RDB$PRIMARY1" Full Scan


          JUGEMテーマ:コンピュータ



          | meijik | クロスデータベース | 09:23 | comments(0) | - | - | - |
          ジムという男
          0
            ジムという男

            本エントリは以下のAdvent Calendar 15日目のクロスエントリです。

            o Firebird Advent Calendar 2022
            o MySQL闇歴史 Advent Calendar 2022 の2枚目
            o SQLite Advent Calendar 2022

            MySQL闇歴史のエントリでもとりあげられましたが、ジムといえばMySQLやFirebird/InterBase界では有名な男、ジムスターキー(Jim Starkey)のことです。
            (モビルスーツじゃないよ。あれの綴りはGM)

            英語版Wikipediaにも項目があるほどです。

            Jim Starkey


            ジムのすごいところは、発想してから実装までの速さです。
            以下のプレゼンやバイオグラフィから伺いしることができると思います。

            As Jim Starkey remembers it−Jim Starkeyによる回想−
            How InterBase came to be−InterBaseはどのようにして生まれたか−
            [db tech showcase Tokyo 2016] E32: My Life as a Disruptor by Jim Starkey
            Database Challenges and Innovations. Interview with Jim Starkey

            ジムの不幸なところ(?) は、初期実装から製品化、製品化後の継続的なアップデートに関わる人員数が圧倒的に少ないところです。人を巻き込むムーブが足りない。

            Valcunの夢、Falconからの解放、NimbusDB改めNuoDB

            振り返るとInterBase→Firebirdの初期が大きなムーブメントで、Falconがそれを超えるムーブメントになる可能性がありましたが、OracleがInnoDBを買収した後、MySQLも買収してしまったことにより、InnoDBが失われるというシナリオがなくなり、その機会は失われました。

            結果、ジムはその後自分の考える最強のデータストアを求めて、ひとり独走状態です。
            現在はAmorphousDB(アモルファスDB)に注力しているようです。

            AmorphousDB: Rethinking the Relational Database

            正直、私はValcun以降はついていけてない感じです。
            NimbusDB以降は、うっすら概略はわかるのですが、細かい部分は全然わかりません。

            それはオープンソースではないうえに、対外的に技術発信やマーコムが積極的に行われてないため、良いものの良さがわからない、伝わらないもどかしさがあります。

            アーキテクチャが古い、リシンキング!! も伝わらなければ、広がりませんし、広がらないと使われない。このジレンマ。
            どこかで、またInterBase→Firebirdの初期のような大きなムーブメントが起こることを期待しつつ、小ネタで〆ます。

            ジムがDECに在籍したのはバイオグラフィからわかると思うのですが、実はDECのRdb(という名前のRDBMS)は、Oracle社に買収されて、まだ保守されてるのです!

            Oracle Rdb方針説明

            上記記事が3年前ですので、もうOracle社が買収してから28年。すごいですねー。

            マニュアルも公開されています。それを見ていて気づいたのですが、InterBase/Firebirdとシステム表(ディクショナリ)が似てるんですね。

            当然それぞれ拡張されているため、それぞれにある表・ない表や、追加されたカラムがありますが、私の眼には大変似たものに見えます。あなたはどうでしょうか?

            Firebirdのシステム表
            Rdbのシステム表

            FirebirdのRDB$FIELDS
            RdbのRDB$FIELDS

            と。。。。

            ここまで読んで「あれ、このエントリSQLiteとはどんな関係があるの?」と思った方がいるかもしれません。
            そう、直接の関係は何もないのです!!

            強いていえば、実はBLOB型データを最初に実装したのはジムだそうです。それがRDBMS界に急速に(二年程度?)で浸透して、デフォルトの機能となりました。それを手本としてSQLiteにも実装されたのです。

            The Story of the Blob

            おあとがよろしいようで(笑)

            # ちなみに本エントリのタイトルは以下の番組へのオマージュです。

            肥後という男(アメトーーク!)

            JUGEMテーマ:コンピュータ



            | meijik | クロスデータベース | 20:29 | comments(0) | - | - | - |
            実行計画: インデックススキャン
            0
              実行計画: インデックススキャン

              本エントリはSQLite Advent Calendar 2022の11日目とFirebird Advent Calendar 2022の11日目のクロスエントリです。

              前回の続き。

              SQLiteで以下のテーブルとデータを作成して確認してみましょう。

              CREATE TABLE t3(id int not null, id2 int, primary key(id));
              insert into t3
              WITH recursive NumberTable(SeqNo, RndNo)
              AS
              (
              SELECT 1, random()
              UNION ALL
              SELECT 1 + SeqNo, random()
              FROM NumberTable
              WHERE SeqNo < 1024
              )
              SELECT SeqNo, RndNo FROM NumberTable;

              sqlite> explain query plan select * from t3;
              QUERY PLAN
              `--SCAN t3

              sqlite> explain query plan select * from t3 where id = 400;
              QUERY PLAN
              `--SEARCH t3 USING INDEX sqlite_autoindex_t3_1 (id=?)

              id=400にすると、プライマリキーが利用され上記のようになります。

              Firebirdでは、以下の方法でクエリとデータを作成します。

              CREATE TABLE t3(id int not null, id2 int, primary key(id));
              insert into t3
              WITH recursive NumberTable(SeqNo, RndNo)
              AS
              (
              SELECT 1, floor(rand()*2147483647) from rdb$database
              UNION ALL
              SELECT 1 + SeqNo, floor(rand()*2147483647)
              FROM NumberTable
              WHERE SeqNo < 1024
              )
              SELECT SeqNo, RndNo FROM NumberTable;

              isqlというコマンドラインツールで接続し、以下の設定をします。

              SET PLANONLY ON;

              SQL> select * from t3;

              PLAN (T3 NATURAL)

              SQL> select * from t3 where id = 400;

              PLAN (T3 INDEX (RDB$PRIMARY1))

              Firebird 4.0では新しいフォーマットで実行計画を出力することもできます。

              SET EXPLAIN ON;

              SQL> select * from t3;

              Select Expression
                -> Table "T3" Full Scan

              SQL> select * from t3 where id = 400;

              Select Expression
                -> Filter
                  -> Table "T3" Access By ID
                    -> Bitmap
                      -> Index "RDB$PRIMARY1" Unique Scan

              こちらのほうが、他のRDBMSの実行計画と似ていて見やすそうですね。

              PLAN, PLANONLY, EXPLAINの関係は以下のマニュアルをご参照ください。

              18.15.1. Effects of Various Plan-Related Commands

              こちらのpdfを読んでおくと、後のFirebirdの実行計画を読むときにも参考になりそうです。
              OPTIMIZATION OF SQL QUERIES IN FIREBIRD

              JUGEMテーマ:コンピュータ



              | meijik | クロスデータベース | 21:01 | comments(0) | - | - | - |
              UPSERT大全2022
              0
                UPSERT大全2022

                本エントリは以下のAdvent Calendar 9日目のクロスエントリです。

                o JPOUG Advent Calendar 2022
                o PostgreSQL Advent Calendar 2022
                o SQLite Advent Calendar 2022
                o Firebird Advent Calendar 2022

                UPSERTとは対象とするテーブルに対象行があればUPDATE, なければINSERTするクエリの総称です。
                UPSERTというキーワードでDMLが存在するわけではありません。

                古くは磁気テープのファイルシステム時代から使われてた「マージ&インサート」の
                ロジックをSQLで行う構文で、以下のようなロジックを実現するものです。

                EACH FOR トランザクションテーブル
                  DO IF 現在行がマスターテーブルに存在しない(検索条件に合致しないことで判断)
                    THEN 現在行をマスターテーブルに挿入;
                    ELSE マスターテーブルを検索条件に合致する行の値で更新;
                  END IF;
                END FOR;

                つまり更新対象の表(マスターテーブル)に、元となるテーブル(トランザクションテーブル)の
                行がない場合にはINSERTによる挿入を、行がある場合にはUPDATEによる更新を行います。

                このような処理はUPDATEとINSERTを同時に行うためUPSERTと呼んでいます。

                UPSERTの実装としては、以下のものがあります。

                (1) REPLACE(MySQL, SQLite)
                (2) MERGE(Oracle, SQL Server, DB2, Firebird, PostgreSQL(15以降))
                (3) INSERT ... ON CONFLICT ... (MySQL, PostgreSQL, SQLite)
                (4) UPDATE OR INSERT (Firebird)

                SQL標準としては(2)になりますので(2)が使えるRDBMSではそれを利用することをお考えください。
                MERGEを実装していないRDBMSでは(3)が利用できますので、それをお使いください。

                歴史的な経緯として、(1)が最初に実装・利用されたため、既存のコードではそれが利用されていることが多いのですが、実装に依存して、トリガーの動作や、既存列を利用したUPDATEに難があるため、新規のコードでは(2),(3)の利用をお勧めします。

                (4) はFirebird 2.1以降で利用できる記法ですが、独自記法ですので、Firebirdだけで利用することが前もってわかっており、MERGE文は大仰過ぎる、という場合のみお使いください。以下、使用例をそれぞれの記法で記述したものです。

                順に説明します。

                (1) REPLACE
                Oracle  : ×
                SQL Server: ×
                Db2 : ×
                PostgreSQL: ×
                MySQL : ○
                Firebird : ×
                SQLite : ○
                SQL標準 : ×

                処理対象テーブルへデータ挿入を行い、ユニークキーが重複したときには更新を行う

                REPLACE [INTO] INSERTの記法 (MySQL, SQLite)
                REPLACE [INTO] UPDATEの記法 (MySQLのみ)

                MERGE文の代わりにINSERT ... ON (DUPLICATE KEY|CONFLICT) を実装しているPostgreSQL, MySQL, SQLiteのうち、
                MySQL, SQLiteでは、MySQLが古くからサポートしているREPLACEの構文を利用することができます。

                # SQLiteのREPLACEはINSERT OR REPLACEの別名であるため、MySQLのようなUPDATEの記法は利用できません。

                過去のクエリを読むために構文などをここで説明しますが、新規で作成するクエリについては、INSERT ... ON (DUPLICATE KEY|CONFLICT) を現時点ではご利用ください。

                (2) MERGE
                Oracle  : ○
                SQL Server: ○
                Db2 : ○
                PostgreSQL: 〇(15)
                MySQL : ×
                Firebird : ○
                SQLite : ×
                SQL標準 : ○

                MERGE INTO target_table [[AS] alias] USING table_reference
                ON search_condition
                WHEN MATCHED THEN modify_statement
                WHEN NOT MATCHED THEN insert_statement

                SQL Server, FirebirdやPostgreSQLでは、WHEN MATCHEDを指定しないこともできるので、その場合には
                「現在行がマスターテーブルに存在しない」場合にのみ、INSERTする、といったロジックも実現可能です。

                また、modify_statementとして、当初想定されていたUPDATEの代わりにDELETEを指定できる実装もあります。(SQL Server, Firebird, PostgreSQL(15以降))

                MERGE文の処理は旧来ストアドプロシジャでおこなえていたものですので、
                いわゆるUPSERTが実装されていない古いバージョンのRDBMSではストアドプロシジャでの実現をご検討ください。

                (3) INSERT ... ON (DUPLICATE KEY | CONFLICT) ...
                Oracle  : ×
                SQL Server: ×
                Db2 : ×
                PostgreSQL: ○
                MySQL : ○
                Firebird : ×
                SQLite : ○
                SQL標準 : ×

                処理対象テーブルへデータ挿入を行い、ユニークキーが重複したときには更新を行う

                INSERT ... ON DUPLICATE KEY update_statement (MySQL)
                INSERT ... ON CONFLICT (update_statement | NOTHING)

                MERGE文をサポートしていないPostgreSQL(14以前), MySQL, SQLiteでは、MERGE文の基本的な機能を
                INSERT ... ONにて実現しています。ただ行の一致不一致の条件は、主キー・ユニークキーに
                限られますし、拡張機能であるDELETEには対応しません。しかしながら、ほとんどの場合は本機能で事足ります。

                また、PostgreSQL, SQliteはupdate_statementの代わりにNOTHINGを指定することにより、
                MERGE文でWHEN MATCHEDを省略した場合と同じ動作にすることができます。

                (4) UPDATE OR INSERT
                直感的にはわかりやすいですが、Firebirdの方言ですので、代わりにMERGE文を使うようにしてください。

                How to do INSERT OR UPDATE depending of existence of primary key value?



                個人的にはPostgreSQL 15でMERGE文が実装されたのがビックリです。

                澤田さんがPostgreSQL Conference 2022で発表していたようにINSERT ... ON CONFLICTと
                MERGEでは処理速度に差があるケースがありますので、PostgreSQLユーザはその点も考慮して
                使い分けする、というぜいたくができます(笑)

                JUGEMテーマ:コンピュータ



                | meijik | クロスデータベース | 08:54 | comments(0) | - | - | - |
                実行計画: フルテーブルスキャン
                0
                  実行計画: フルテーブルスキャン

                  本エントリはSQLite Advent Calendar 2022の7日目と
                  Firebird Advent Calendar 2022の7日目のクロスエントリです。

                  SQLiteについては、昨日一昨日のエントリを見れば、今回のクエリの意味と、実際の確認ができるはず。

                  explain query plan select * from t3;
                  QUERY PLAN
                  `--SCAN t3

                  これがいわゆる「フルテーブルスキャン」となります。

                  Firebirdについては、以下のエントリを読めば、確認できるはず。

                  「SQL実践入門」勝手に補足: Firebirdで実行計画を見る

                  select * from t3;

                  PLAN (T3 NATURAL)

                  徐々に複雑な実行計画を見ていきたいと思ってます。

                  JUGEMテーマ:コンピュータ



                  | meijik | クロスデータベース | 18:31 | comments(0) | - | - | - |
                  SQLiteとうとうIS [NOT] DISTINCT FROM サポート。Firebirdは?
                  0
                    SQLiteとうとうIS [NOT] DISTINCT FROM サポート。Firebirdは?

                    本エントリはSQLite Advent Calendar 2022の3日目とFirebird Advent Calendar 2022の3日目のクロスエントリです。

                    長らくSQLiteは、ヌルセーフの等価演算子としてISを利用してきましたが、
                    2022-06-25 (3.39.0)よりIS [NOT] DISTINCT FROMがサポートされました。

                    IS [NOT] DISTINCT FROMは、一般的なRDBMSでは徐々にサポートされてきていて、
                    2022-12-03現在、サポートしていないものはOracle DBとMySQL(MariaDB)のみとなりました。
                    Firebirdも当然サポートしています。

                    各RDBMSのサポート状況や、サポートしていないときの代替案については、以下のエントリが詳しいです。

                    NULL-Aware Comparison: is [not] distinct from(modernSQL)

                    個人的には等価演算子(?) として「IS [NOT] DISTINCT FROM」は大仰なので、もともとの
                    SQLiteが採用していたISがSQL標準になればいいのになぁ、と思ってました。
                    もともと「IS」「NOT」もキーワードとしてあるし、
                    「DISTINCT」「FROM」はSELECTでも使っているものだし。。。。

                    JUGEMテーマ:コンピュータ



                    | meijik | クロスデータベース | 09:02 | comments(0) | - | - | - |
                    SQLiteとうとうRIGHT JOINとFULL OUTER JOINサポート。Firebirdは?
                    0
                      本エントリはSQLite Advent Calendar 2022の1日目とFirebird Advent Calendar 2022の1日目のクロスポストです。

                      長らくSQLiteは「RIGHT JOINイラネ」状態だったのですが、
                      2022-06-25 (3.39.0)よりRIGHT [OUTER] JOINがサポートされました。

                      RIGHT [OUTER] JOINをサポートしていないRDBMSは珍しく、やっとこさ
                      サポートされた印象です。Firebirdも当然サポートしています。

                      といって、RIGHT [OUTER] JOINがサポートされていなくて、困っていたか
                      というと、私自身は特に困ってませんでした。というのも、LEFT [OUTER] JOINは自身で使ったり、クエリを読む場合でもよく見かけたものの、
                      RIGHT [OUTER] JOINは自分では使ったことがなく、見かけたのはこの四半世紀で数回、といった状況だからです。

                      さらに、SQLiteではこの2022-06-25 (3.39.0)からFULL [OUTER] JOINもサポートされました。FULL [OUTER] JOINもFirebirdでサポートされています。

                      これで一般的なRDBMSでFULL [OUTER] JOINをサポートしていないのはMySQLくらいです。

                      WL#1604: Support FULL [OUTER] JOIN by rewriting with UNION

                      回避策としては、上記WLに書いてあるようにRIGHT JOINとLEFT JOINをUNIONすれば大丈夫です。

                      JUGEMテーマ:コンピュータ



                      | meijik | クロスデータベース | 08:02 | comments(0) | - | - | - |
                           12
                      3456789
                      10111213141516
                      17181920212223
                      24252627282930
                      31      
                      << March 2024 >>
                      + RECOMMEND
                      100人のプロが選んだソフトウェア開発の名著 君のために選んだ1冊
                      100人のプロが選んだソフトウェア開発の名著 君のために選んだ1冊 (JUGEMレビュー »)

                      100人のうちの一人としてミックさんの本を推薦しています。
                      + RECOMMEND
                      MySQL 5.1 Plugin Development
                      MySQL 5.1 Plugin Development (JUGEMレビュー »)
                      Andrew Hutchings,Sergei Golubchik
                      MySQL 5.1 のプラグインを作るならこれ!
                      + RECOMMEND
                      AWKを256倍使うための本 (Ascii 256倍)
                      AWKを256倍使うための本 (Ascii 256倍) (JUGEMレビュー »)
                      志村 拓,鷲北 賢,西村 克信
                      ここにきてまさかの復刊。15〜16年前の本ですが、いまでも十分役立ちますよ!!
                      + RECOMMEND
                      Linux-DB システム構築/運用入門 (DB Magazine SELECTION)
                      Linux-DB システム構築/運用入門 (DB Magazine SELECTION) (JUGEMレビュー »)
                      松信 嘉範
                      Linux-DB システム構築の新スタンダード。DB Magazine人気連載、待望の書籍化です!!
                      + RECOMMEND
                      + RECOMMEND
                      超・極める!MySQL
                      超・極める!MySQL (JUGEMレビュー »)
                      坂井 恵,志村 和彦,ひろせ まさあき,松信 嘉範
                      内容は少々古くなりましたが、内部説明があり、いろいろ面白いです。
                      + RECOMMEND
                      The Root of .NET Framework
                      The Root of .NET Framework (JUGEMレビュー »)
                      荒井 省三
                      .NETの根っこをつかむ、か?
                      + RECOMMEND
                      エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド
                      エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド (JUGEMレビュー »)
                      奥野 幹也
                      これまでにない構築、そして構築後のMySQLのための本。
                      + RECOMMEND
                      Database Management Systems
                      Database Management Systems (JUGEMレビュー »)
                      Raghu Ramakrishnan,Johannes Gehrke
                      ミックさん連載の参考書籍です。
                      + RECOMMEND
                      Advanced Programming in the UNIX Environment: Paperback Edition (2nd Edition) (Addison-Wesley Professional Computing Series)
                      Advanced Programming in the UNIX Environment: Paperback Edition (2nd Edition) (Addison-Wesley Professional Computing Series) (JUGEMレビュー »)
                      W. Richard Stevens,Stephen A. Rago
                      必読! といいつつ私が読んだのは初版の日本語訳です。差分はチェックしないといけませんね。
                      + RECOMMEND
                      + RECOMMEND
                      + RECOMMEND
                      Western Digital Scorpio Blue 2.5inch 5400rpm 320GB 8MB PATA WD3200BEVE
                      Western Digital Scorpio Blue 2.5inch 5400rpm 320GB 8MB PATA WD3200BEVE (JUGEMレビュー »)

                      DELL SX260の換装用に購入。今はコチラのほうが安いですね。
                      + RECOMMEND
                      情熱プログラマー ソフトウェア開発者の幸せな生き方
                      情熱プログラマー ソフトウェア開発者の幸せな生き方 (JUGEMレビュー »)
                      Chad Fowler
                      OSC2010神戸に出展されたジュンク堂で買いました。
                      + RECOMMEND
                      Software Design 総集編 【2000~2009】(DVD付)
                      Software Design 総集編 【2000~2009】(DVD付) (JUGEMレビュー »)

                      メガトン級の過去記事。売り切れる前にお店に急げ!
                      + RECOMMEND
                      + RECOMMEND
                      玄人志向 2.5型HDDケース SATA対応USB2.0接続 GW2.5AI-SU2
                      玄人志向 2.5型HDDケース SATA対応USB2.0接続 GW2.5AI-SU2 (JUGEMレビュー »)

                      Aspire 1410 HDD換装後、昔のHDDはこれにいれます。
                      + RECOMMEND
                      + RECOMMEND
                      acer Aspire Timeline AS1410 11.6型ノートPC Windows7搭載 250GB ブラック AS1410-KK22
                      acer Aspire Timeline AS1410 11.6型ノートPC Windows7搭載 250GB ブラック AS1410-KK22 (JUGEMレビュー »)

                      色違い黒(他に青と白があります): メモリは最大4GBとあるが、がんばれば8GBいけるらしい。
                      + RECOMMEND
                      Firebird 徹底入門
                      Firebird 徹底入門 (JUGEMレビュー »)
                      木村 明治,はやし つとむ,坂井 恵
                      Firebird日本ユーザ会のはやしさん、そしてMyNAから拝借(?) してきた坂井さんとともに書きました。まだ誰も知らないFirebird 2.5や、いままでドキュメントのなかったツール類についてもソースを確認してとりあげた本です。是非ご活用ください。
                      + SELECTED ENTRIES
                      + RECENT COMMENTS
                      • 最新のSQL標準(SQL:2023)とFirebird/MySQL/PostgreSQL
                        kitayama_t (12/24)
                      • オープンセミナー2008@岡山
                        meijik (10/15)
                      • 2022年のFirebird
                        tetsuyat@ac.wakwak.com (01/24)
                      • 内藤 陽哉(ないとうようや)先生を偲んで
                        高村京夏 (11/07)
                      • 「エイリアンズ」から入る人のための極私的「キリンジ/KIRINJI」入門
                        meijik (05/02)
                      • 「エイリアンズ」から入る人のための極私的「キリンジ/KIRINJI」入門
                        k (05/01)
                      • Firebird 4.0のレプリケーション(6) 非同期レプリケーション
                        小林正樹 (12/02)
                      • MySQL ProとMySQL Expertを立ち読みする+α
                        MEIJI KIMURA (09/14)
                      • たまたま「プリントヘッドの種類が違います」エラーから復活@MG6230
                        meijik (11/20)
                      • [ミュージシャンに聴かせたい・弾かせたい!] (4) 山田元気様
                        meijik (07/27)
                      + RECENT TRACKBACK
                      + CATEGORIES
                      + ARCHIVES
                      + MOBILE
                      qrcode
                      + LINKS
                      + PROFILE