キムラデービーブログ
http://blog.kimuradb.com/
オープンソースデータベースを加速する「キムラデービー」のブログです。カレー日記を兼ねてます。なお著者は2010-06-01より日本オラクルに在籍していますが、本サイト(ブログ、またはウェブサイト)において示されている見解は、私自身の見解であって、オラクルの見解を必ずしも反映したものではありません。
ja
-
http://blog.kimuradb.com/?eid=877716
2024年のFirebird(展望)
本エントリはFirebird Advent Calendar 2023 25日目のエントリです。
# 実際に書いているのは2024-01-02です orz
毎年ここにかく2023年のFirebirdについては、1日目のエントリに書きましたので、そちらをご覧ください。
Firebird 2023年の振り返り
現在、Firebi...
Firebird Advent Calendar 2023 25日目のエントリです。
# 実際に書いているのは2024-01-02です orz
毎年ここにかく2023年のFirebirdについては、1日目のエントリに書きましたので、そちらをご覧ください。
Firebird 2023年の振り返り
現在、Firebird 5.0が開発中ですが、Firebird 4.0のレプリケーションような大きな機能追加はなく、
細やかな改善のように見えます。(それはFirebird 4.0のODSバージョンが13.0に対して、13.1というのにも現れています)
Firebirdは良くも悪くも現在の大きな流れに対して、既存のユーザー以外には中途半端な立ち位置にあると思います。
SQLiteのように組み込みに特化して、高機能になる、というわけでもなく、
かといってPostgreSQLやMySQLが目指す大規模対応、新しい試みのRDBMSのフロントエンドになる、というわけでもなく、
かといってCloudで扱いやすい、というものでもありません。
Firebird以外の2023年RDBMS界隈はどうでしたでしょうか?
2年前にまとめたときから、さらにクラウド化が進んでいる現状です。
2022年のFirebird
こばさんのまとめをみると全貌がわかりやすいと思います。
[個人的]データベースゆく年くる年(2023)
残念ながらどこにもFirebirdが関連しそうな要素がありません。。。 orz
2023年は世界五大クラウドからIBMが退場して、Oracleが入れ替わったような印象を受けます。
2023年の世界におけるSaaS企業のトップ5社(MIICHISOFT)
DBに固執せず、プラットフォームとしてのクラウドをすすめるために、Microsoft Azueとの連携 や、
PostgreSQLのマネージドサービスを提供 したりしています。
新しい流れとしては、東芝のGridDBからクラウドでFreeプラン がでたり、
ノーチラスのTsurugiがリリース されたり、ということがありました。
GridDB自体は素晴らしいものだと思うのですが、いかんせん既存のOSSのフロントエンド(PostgreSQLやMySQL)ではなく、
独自にしてしまったのが、つらいところ。そのため既存の開発者を取り込むところでつまずいている印象を受けます。
Tsurugiはその点、NECさんにまかせてPostgreSQLをフロントエンドに据えたのは、いい割り切り具合だと思います。
後はどれだけ開発者がTsurugi独自の文化を活かした実装をするか、その利点をユーザに伝えるか、で、
Tsurugiを使おうとする人々をどれだけ巻き込んで、どれだけの教育をどれだけの期間でできるのか、にかかっていると思います。
Firebirdは既存のユーザの要望に応えつつ、JDBCや.Netを使ったオンプレアプリの組み込み(同梱)データベースとして新規ユーザを獲得できるように地道な活動を期待します。
]]>
Firebird/InterBase
2023-12-25T23:27:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877715
最新版のInterBase 2020 Update 5
本エントリはFirebird Advent Calendar 2023 24日目のエントリです。
# 実際に書いているのは2023-12-31です orz
Firebirdのフォーク元であるInterBaseは2020までは結構活発に開発されていましたが、2020以降は新バージョンは出ず、去年(2022年)12月にUpdate 4
そし...
Firebird Advent Calendar 2023 24日目のエントリです。
# 実際に書いているのは2023-12-31です orz
Firebirdのフォーク元であるInterBaseは2020までは結構活発に開発されていましたが、2020以降は新バージョンは出ず、去年(2022年)12月にUpdate 4
そして今年(2023年)10月ににUpdate 5が出たのみでした。
Update4までの詳細は以下のエントリをご参照ください。
InterBase 2020 リリース ノート
Update5については、以下をご参照ください。
Embarcadero InterBase 2020 Update 5 のリリース
Update5については、ほぼバグ修正メインで、新規の機能追加とかはないようです。
]]>
Firebird/InterBase
2023-12-24T11:39:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877714
Firebird 5.0 リリースノートを読む:MERGEでの PLAN, ORDER BYのサポート
本エントリはFirebird Advent Calendar 2023 23日目のエントリです。
# 実際は2023-12-31に書いています orz
本エントリに対応するリリースノートは次のエントリです。
10. Data Manipulation Language (DML):Support PLAN and ORDER BY on MERGE
MERGE INTO ta...
Firebird Advent Calendar 2023 23日目のエントリです。
# 実際は2023-12-31に書いています orz
本エントリに対応するリリースノートは次のエントリです。
10. Data Manipulation Language (DML):Support PLAN and ORDER BY on MERGE
MERGE INTO target [[AS] target_alias]
USING <source> [[AS] source_alias]
ON <join_condition>
<merge_when> [<merge_when> ...]
[PLAN <plan-expr>]
[ORDER BY <ordering-list>]
[RETURNING <returning_list> [INTO <variables>]]
PLANやORDER BYについては、昨日のエントリ をご覧ください。
TsurugiもSQL的にはMERGEだけサポートすればいいと思ったけど、やることに対して構文が大仰すぎるからさけたのかなぁ。(TsurugiはMERGE未サポートです)
Available SQL features in Tsurugi
]]>
Firebird/InterBase
2023-12-23T11:13:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877713
Firebird 5.0 リリースノートを読む:「UPDATE OR INSERT」での PLAN, ORDER BY と ROWSのサポート
本エントリはFirebird Advent Calendar 2023 22日目のエントリです。
# 実際は2023-12-30に書いてます orz
本エントリに対応するリリースノートは次のエントリです。
10. Data Manipulation Language (DML):Support PLAN, ORDER BY and ROWS on UPDATE OR INSERT
...
Firebird Advent Calendar 2023 22日目のエントリです。
# 実際は2023-12-30に書いてます orz
本エントリに対応するリリースノートは次のエントリです。
10. Data Manipulation Language (DML):Support PLAN, ORDER BY and ROWS on UPDATE OR INSERT
UPDATE OR INSERT INTO
target [(<column_list>)]
[<override_opt>]
VALUES (<value_list>)
[MATCHING (<column_list>)]
[PLAN <plan-expr>]
[ORDER BY <ordering-list>]
[ROWS <m> [TO <n>]]
[RETURNING <returning_list> [INTO <variables>]]
PLANは一般的なRDBMSでいうところのヒント句相当のものです。
実行計画をPLANで表示して、それをクエリに指定することができますが、それがUPDATE OR INSERT(一般的にと呼ばれるUPSERT)も対応したものです。
ユーザ指定のPLANの利用については以下のドキュメントが詳しいです。
Improved PLAN Clause(Firebird 2.1マニュアル)
オプティマイザが選ぶプランの表示はこちら
OPTIMIZATION OF SQL QUERIES IN FIREBIRD
またMySQLで利用できるORDER BYやLIMIT(同様のROWS)も対応しました。
なお、UPSERTをUPDATE OR INSERTで表現するのは、今のところFirebirdのみ。
そういえばノーチラスのTSURUGIも似たような表現だったなー、と検索したところ
INSERT OR REPLACE
でした。
Tsurugiのダンプ・ロード機能
# TsurugiのAdvent Calendarも今年(2023)あった んですねー。
insert or replaceの例(ひしだま)
上記のブログにもありますが、Tsurugiではinsert if not existsで存在しないデータだけのインサートもできます。
しかし、さらに調べると別名でUPDATE OR INSERTも利用できるみたいです。
update or insertの例(ひしだま)
INSERT OR REPLACEはSQLiteでも利用できる表記で、さらに短縮したREPLACEも利用できます。
MySQLでは古い表記でREPLACEを利用することもできます。ただMySQLのREPLACEはUPDATE相当の記述ができますが、SQLiteやTsurugiのINSERT OR REPLACEは、INSERT相当の記述しかできないようです。
]]>
Firebird/InterBase
2023-12-22T21:19:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877712
Firebird 5.0 リリースノートを読む: OPTIMIZE FOR句
本エントリはFirebird Advent Calendar 2023 21日目のエントリです。
本エントリに対応するリリースノートは次のエントリです。
10. Data Manipulation Language (DML):OPTIMIZE FOR 句
SELECT
...
[WITH LOCK [SKIP LOCKED]]
[OPTIMIZE FOR {FIRS...
Firebird Advent Calendar 2023 21日目のエントリです。
本エントリに対応するリリースノートは次のエントリです。
10. Data Manipulation Language (DML):OPTIMIZE FOR 句
SELECT
...
[WITH LOCK [SKIP LOCKED]]
[OPTIMIZE FOR {FIRST | LAST} ROWS]
この機能により、ユーザーアプリケーションによって最初にサブセット、または行のみがフェッチされる場合 (残りの行はオンデマンドでフェッチされる)、オプティマイザは別の (できればより良い) 計画を検討できるため、応答時間が改善されます。
# OPTIMIZE FOR 句は、最上位の SELECT でのみ使用できます。
類似のOPTIMIZE FOR 句はIBM Db2にも用意されており 、OPTIMIZE FOR n ROWS で指定します。これはFIRST相当の行数指定になります。LAST相当の指定はありません。
]]>
Firebird/InterBase
2023-12-21T06:34:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877711
Firebird 5.0 リリースノートを読む: カッコで囲まれたクエリー式
本エントリはFirebird Advent Calendar 2023 20日目のエントリです。
本エントリに対応するリリースノートは次のエントリです。
10. Data Manipulation Language (DML):Allow parenthesized query expressions
MySQL 8.0.22で実装され、関連して以前以下のエント...
Firebird Advent Calendar 2023 20日目のエントリです。
本エントリに対応するリリースノートは次のエントリです。
10. Data Manipulation Language (DML):Allow parenthesized query expressions
MySQL 8.0.22で実装され、関連して以前以下のエントリでFirebirdでの状況を説明した「カッコで囲まれたクエリー式(parenthesized query expressions)」がFirebird 5.0で実装されました。
MySQL 8.0.22が来た! カッコで囲まれたクエリー式(Parenthesized Query Expressions)(キムラデービーブログ)
リリースノートに書かれている以下の効果が期待できます。
「これにより、特にUNIONステートメントで、より表現力豊かなクエリが可能になりました。
特定のORMによって生成されたステートメントとの互換性が高まります」
]]>
Firebird/InterBase
2023-12-20T08:40:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877710
Firebird 5.0 リリースノートを読む: SKIP LOCKED句
本エントリはFirebird Advent Calendar 2023 19日目のエントリです。
本エントリに対応するリリースノートは次のエントリです。
10. Data Manipulation Language (DML):SKIP LOCKED clause
SKIP LOCKEDはロックを伴うSELECT (MySQL/PostgreSQLではFOR UPDATE, Fi...
Firebird Advent Calendar 2023 19日目のエントリです。
本エントリに対応するリリースノートは次のエントリです。
10. Data Manipulation Language (DML):SKIP LOCKED clause
SKIP LOCKEDはロックを伴うSELECT (MySQL/PostgreSQLではFOR UPDATE, FirebirdではWITH LOCK)で利用でき、これにより他のトランザクションによってロックされたレコードを待機せずにスキップできます。
これがFirebird 5.0で実装されました。もともとはOracle DBの方言です。
Oracle : ○
SQL Server: ○(PASTREAD)
Db2 : ○(SKIP LOCKED DATA)
PostgreSQL: ○(9.5)
MySQL : ○(8.0)
Firebird : ○(5.0)
SQLite : ×(指定しても無視)
SQL標準 : ×
用途としては以下のようなシナリオがあります。
PostgreSQLのSKIP LOCKEDを使ってテーブルをキューとして使用する(Enjoy*Study)
ロッキングリードのNOWAITとSKIP LOCKEDオプションについて(MySQL道普請便り)
]]>
Firebird/InterBase
2023-12-19T22:30:17+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877709
Firebird 5.0 リリースノートを読む: 部分インデックス(Partial indices)
本エントリはFirebird Advent Calendar 2023 17日目のエントリです。
本エントリに対応するリリースノートは次のエントリです。
9. Data Definition Language (DDL):Support for partial indices
ここでいう「部分インデックス(Partial indices)」は、WHERE句で...
Firebird Advent Calendar 2023 17日目のエントリです。
本エントリに対応するリリースノートは次のエントリです。
9. Data Definition Language (DDL):Support for partial indices
ここでいう「部分インデックス(Partial indices)」は、WHERE句で指定する以下のインデックスのことです。
Microsoft SQL Server:フィルター選択されたインデックス
PostgreSQL:部分インデックス
これがFirebird 5.0で実装されました。
# MySQLでいう長さで指定する(PREFIX的な)インデックス とは違います。
]]>
Firebird/InterBase
2023-12-17T15:39:36+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877707
やたら増えるログの闇に対する防衛術2023[MySQL]
本エントリは闇の魔術に対する防衛術 Advent Calendar 2023の16日目とMySQL Advent Calendar 2023 16日目のクロスエントリです。
1.プロローグ
兵隊「隊長! 今度採用したアプリケーション、バックエンドはMySQLですが、最新のMySQL 8.0を使っているらしく、やたらエラ...
闇の魔術に対する防衛術 Advent Calendar 2023の16日目とMySQL Advent Calendar 2023 16日目のクロスエントリです。
1.プロローグ
兵隊「隊長! 今度採用したアプリケーション、バックエンドはMySQLですが、最新のMySQL 8.0を使っているらしく、やたらエラーログにWarningを出すんです」
隊長「なーにー、やっちまったなぁ」
2.防衛術:エラーログのフィルタリング
隊長「男は黙ってlog_error_suppression_list」
MySQL 8.0.13からlog_error_suppression_listがサポートされました。
これはdepricatedに対するWarningエラーのように「いわれなくてもわかってるよ〜」というエラー出力を抑制するのに便利です。具体的な設定などは、以下のマニュアルや技術ドキュメントをご参照ください。
5.4.2.5 優先度ベースのエラーログのフィルタリング (log_filter_internal)
第155回エラーログのフィルタリング(MySQL道普請便り)
3.起承「転」結
兵隊「隊長! 実は昔から使っているMySQLがあるんですが、なんかバイナリログの量がすごく増えててですね。この前ディスクフルを起こしたんですよ。これって生成量自体減らせないんですかねぇ」
隊長「最近のMySQL(5.7以降)はバイナリログのフォーマットのデフォルトが行単位だからなぁ(binlog_format=row)。いたしかたない。。。。いや、条件さえあえば、いくつか対策はあるゾ」
3.1 binlog-row-imageをデフォルトのfullから、minimalまたはnoblobにする。
MySQL 行ベースレプリケーションでは、各行変更イベントに 2 つのイメージ、つまり「前」イメージ (更新される行を検索するときにこれらのカラムが照合される) と「後」イメージ (変更を含む) が含まれます。
MySQL 5.6からはbinlog-row-image指定が使え、下記マニュアルにある条件さえあえば、minimalにしてバイナリログの量の削減が期待できます。
17.1.6.4 バイナリロギングのオプションと変数:binlog-row-image
3.2 binlog_transaction_compressionを使う
MySQL 8.0.20からbinlog_transaction_compressionを使ったMySQLバイナリログの圧縮が利用できます。
設定と効果については、以下の技術ブログが参考になります。
第207回binlog_transaction_compressionを使ったMySQLバイナリログの圧縮について(MySQL道普請便り)
では、十分なディスク容量を確保しつつ、突撃〜
]]>
MySQL
2023-12-16T17:43:33+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877708
Firebird 5.0 リリースノートを読む
本エントリはFirebird Advent Calendar 2023 16日目のエントリです。
本エントリの元ネタは以下のエントリです。
[HTML]
Firebird 5.0 Release Candidate 2 Release Notes : 2. New In Firebird 5.0
[PDF]
Firebird 5.0 Release Candidate 2 Release Notes
Fi...
Firebird Advent Calendar 2023 16日目のエントリです。
本エントリの元ネタは以下のエントリです。
[HTML]
Firebird 5.0 Release Candidate 2 Release Notes : 2. New In Firebird 5.0
[PDF]
Firebird 5.0 Release Candidate 2 Release Notes
Firebird 5.0 では、アーキテクチャや操作に変更を加えることなく、多くの改良が加えられています。
最も重要なのは次のとおりです。
• Parallel (multi-threaded) operation for backup/restore, sweep and index creation;
• Partial indices;
• SKIP LOCKED clause for SELECT WITH LOCK, UPDATE and DELETE statements;
• Inline minor ODS upgrade;
• Compiled statement cache;
• PSQL and SQL profiler;
• Support for WHEN NOT MATCHED BY SOURCE for MERGE statement;
• Support multiple rows for DML RETURNING;
• New built-in functions and packages;
• Denser record-level compression;
• Network support for scrollable cursors;
明日以降、これらのうちのいくつかを順にみていきます。
]]>
2023-12-16T15:18:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877706
最新のSQL標準(SQL:2023)とFirebird/MySQL/PostgreSQL。続き。
本エントリは 以下のAdvent Calendarの15日目のクロスエントリです。
Firebird Advent Calendar 2023
MySQL Advent Calendar 2023
PostgreSQL Advent Calendar 2023
SQL標準は数年ごとにリリースされて、最新のものはSQL:2023となります。
原本は有償での販売に...
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、ここでも手堅いですねぇ。
]]>
クロスデータベース
2023-12-15T23:40:10+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877705
SQL:2023 Firebirdの場合:A new part for PGQ(property graph queries)
本エントリはFirebird Advent Calendar 2023 14日目のエントリです。
本エントリの元ネタは以下のエントリです。
SQL:2023 is finished: Here is what's new(Peter Eisentraut)
さすがにFirebird, MySQL, PostgreSQLでさえも、PGQには未対応です。
オラクルか...
Firebird Advent Calendar 2023 14日目のエントリです。
本エントリの元ネタは以下のエントリです。
SQL:2023 is finished: Here is what's new(Peter Eisentraut)
さすがにFirebird, MySQL, PostgreSQLでさえも、PGQには未対応です。
オラクルからはOracle DBでPGXを使ったガイドが以下のようにありますが、
まぁオープンソース関連にまで波及するには、数年〜十数年かかりそうです。
プロパティ・グラフ開発者ガイド
]]>
Firebird/InterBase
2023-12-14T21:13:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877704
SQL:2023 Firebirdの場合:JSON features
本エントリはFirebird Advent Calendar 2023 13日目のエントリです。
本エントリの元ネタは以下のエントリです。
SQL:2023 is finished: Here is what's new(Peter Eisentraut)
Firebirdでは今回のSQL標準でのJSON拡張というより、そもそもネイティブのJSON型を...
Firebird Advent Calendar 2023 13日目のエントリです。
本エントリの元ネタは以下のエントリです。
SQL:2023 is finished: Here is what's new(Peter Eisentraut)
Firebirdでは今回のSQL標準でのJSON拡張というより、そもそもネイティブのJSON型をサポートしておりません。
一応以下のような機能リクエストは出ていますが、未実装です。
Support native JSON datatype for columns as MySQL / PostgreeSql [CORE5148]
"as MySQL / PostgreSQL"といっているように、まずPostgreSQL, 次にMySQLでネイティブのJSON型データタイプそして、関連する関数などが現在提供されています。
MySQL 5.7以降で対応
5.7->8.0で順に拡張され、最新は8.0
MySQL 8.0のマニュアル
11.5 JSON データ型
12.18 JSON 関数
PostgreSQL 9.2以降、最新は16
PostgreSQL 15の日本語マニュアル
8.14. JSONデータ型
9.16. JSON関数と演算子
以下の技術ドキュメントでPostgreSQL9.2から12まで、どのように拡張されていったのかまとめがあります。
【最新版】PostgreSQLのJSONの仕様 – 各バージョンのリリース内容
]]>
Firebird/InterBase
2023-12-13T06:00:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877703
SQL:2023 Firebirdの場合:Underscores in numeric literals (T662)
本エントリはFirebird Advent Calendar 2023 12日目のエントリです。
本エントリの元ネタは以下のエントリです。
SQL:2023 is finished: Here is what's new(Peter Eisentraut)
Java 7で導入され、他言語にも提案され実装された「数値リテラルのアンダースコア」...
Firebird Advent Calendar 2023 12日目のエントリです。
本エントリの元ネタは以下のエントリです。
SQL:2023 is finished: Here is what's new(Peter Eisentraut)
Java 7で導入され、他言語にも提案され実装された「数値リテラルのアンダースコア」
Underscores in Numeric Literals
データベースの世界ではまだ実装が進んでないイメージです。
Firebird未対応
SQL> select 1_000_000 from rdb$database;
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, column 9
-_000_000
SQL> select 0x_FFFF_FFFF from rdb$database;
X_FFFF_FFFF
============
0
MySQLも未対応です。
mysql> select 1_000_000;
ERROR 1054 (42S22): Unknown column '1_000_000' in 'field list'
mysql> select 0x_FFFF_FFFF;
ERROR 1054 (42S22): Unknown column '0x_FFFF_FFFF' in 'field list'
]]>
Firebird/InterBase
2023-12-12T21:30:45+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877702
SQL:2023 Firebirdの場合:Non-decimal integer literals (T661)
本エントリはFirebird Advent Calendar 2023 11日目のエントリです。
本エントリの元ネタは以下のエントリです。
SQL:2023 is finished: Here is what's new(Peter Eisentraut)
多くのプログラミング言語と同様に、16 進数、8 進数、および 2 進数の整数リテラル...
Firebird Advent Calendar 2023 11日目のエントリです。
本エントリの元ネタは以下のエントリです。
SQL:2023 is finished: Here is what's new(Peter Eisentraut)
多くのプログラミング言語と同様に、16 進数、8 進数、および 2 進数の整数リテラルが可能になります。
これは、元ネタのSELECTを三行に分ければ、それぞれの対応具合がわかります。
Firebirdでは16進数にしか対応していません。
SQL&lg SELECT 0xFFFF from rdb$database;
CONSTANT
============
65535
SQL&lg SELECT 0o755 from rdb$database;
O755
============
0
SQL&lg SELECT 0b11001111 from rdb$database;
B11001111
============
0
これに対してMySQLは16進数と2進数に対応しています。
mysql> SELECT 0xFFFF;
+----------------+
| 0xFFFF |
+----------------+
| 0xFFFF |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT 0o755;
ERROR 1054 (42S22): Unknown column '0o755' in 'field list'
mysql> SELECT 0b11001111;
+------------------------+
| 0b11001111 |
+------------------------+
| 0xCF |
+------------------------+
1 row in set (0.00 sec)
しかし、厳密に見ればMySQLはT661に対応していない、とも考えることができます。
規格は「整数」リテラルで、MySQLはそれぞれバイナリのまま受け入れているからです。
以下の技術ブログでは「未対応」扱いされていました。
SQL:2023 and how well MySQL and MariaDB comply
規格通り整数として戻すにはCASTしてやる必要があります。
mysql> SELECT cast(0xFFFF as signed);
+------------------------+
| cast(0xFFFF as signed) |
+------------------------+
| 65535 |
+------------------------+
1 row in set (0.01 sec)
mysql> SELECT cast(0b11001111 as signed);
+----------------------------+
| cast(0b11001111 as signed) |
+----------------------------+
| 207 |
+----------------------------+
1 row in set (0.00 sec)
もしくは、整数型のカラムに投入すると暗黙敵にキャストされます。
mysql> create table t1(i1 int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t1 values(0xFFFF),(0b11001111);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+-------+
| i1 |
+-------+
| 65535 |
| 207 |
+-------+
2 rows in set (0.00 sec)
]]>
Firebird/InterBase
2023-12-11T09:00:24+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877701
SQL:2023 Firebirdの場合:ANY_VALUE (T626)
本エントリはFirebird Advent Calendar 2023 十日目のエントリです。
本エントリの元ネタは以下のエントリです。
SQL:2023 is finished: Here is what's new(Peter Eisentraut)
ANY_VALUEは文字通り任意の値(any value)を返す新しい集計関数です。
元ネタのエン...
Firebird Advent Calendar 2023 十日目のエントリです。
本エントリの元ネタは以下のエントリです。
SQL:2023 is finished: Here is what's new(Peter Eisentraut)
ANY_VALUEは文字通り任意の値(any value)を返す新しい集計関数です。
元ネタのエントリの例でいうと、以下のようなテーブルがあった場合、
CREATE TABLE t1 (
a int,
b int
);
INSERT INTO t1 VALUES (1, 11), (1, 22), (1, 33);
以前のMySQLでは、以下のようなSELECT文が可能でした。
SELECT a, b FROM t1 GROUP BY a;
MySQL 5.7以降ではONLY_FULL_GROUP_BYが有効になっているため、以下のエラーが戻ります。
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.t1.b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
このエラーを出さないようにするためにはONLY_FULL_GROUP_BYを無効にする(SQL標準ではない動作)か、
ANY_VALUEを使って以下のように記述します。
SELECT a, ANY_VALUE(b) FROM t1 GROUP BY a;
詳細は以下のMySQLのマニュアルをご参照ください。
12.20.3 MySQL での GROUP BY の処理
つまりGROUP BYに含まれない列をSELECTリストに加えるときに、その列の「任意の値」を返すための合法的な(?) 関数になります。
ANY_VALUE()がないRDBMSの場合、例えばMAXのように通常の集合関数を使うことも可能ですが、本当に任意の値でいい場合には、
その値を取得するために無駄な演算(不必要なMAXを求めるための演算)が必要になり、グルーピングの数が多い場合にはバカにできません。(例のようにグループがひとつで、グループ内の値がみっつ、の場合には問題になりませんが)
mysql> select a, MAX(b) from t1 group by a;
+------+--------+
| a | MAX(b) |
+------+--------+
| 1 | 33 |
+------+--------+
1 row in set (0.03 sec)
まずMySQLが実装、その後Oracle DB、そしてPostgreSQLが実装して今に至っています。
Oracle : ○19c
SQL Server: ×
Db2 : ×
PostgreSQL: 〇16
MySQL : ○5.7
Firebird : ×
SQLite : ×
SQL標準 : 〇2023
上記のようにFirebirdでは未実装ですが、パーサーのソースにFirebird 6.0用にトークンが追加されていますので、Firebird 6.0での実装が期待されます。
firebird/src/dsql/parse.y
9491 // added in FB 6.0
9492 | ANY_VALUE
9493 | FORMAT
9494 | OWNER
9495 ;
]]>
Firebird/InterBase
2023-12-10T22:47:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877700
最新のSQL標準(SQL:2023)とFirebird/MySQL/PostgreSQL
最新の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標準(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の実装
]]>
クロスデータベース
2023-12-09T14:35:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877699
SQL:2023 Firebirdの場合:Enhanced cycle mark values (T133)
本エントリはFirebird Advent Calendar 2023 八日目のエントリです。
本エントリの元ネタは以下のエントリです。
SQL:2023 is finished: Here is what's new(Peter Eisentraut)
CYCLE句は、再帰クエリ(WITH RECURSEVE)のあまり知られていない機能ですし、実装も...
Firebird Advent Calendar 2023 八日目のエントリです。
本エントリの元ネタは以下のエントリです。
SQL:2023 is finished: Here is what's new(Peter Eisentraut)
CYCLE句は、再帰クエリ(WITH RECURSEVE)のあまり知られていない機能ですし、実装も進んでいません。
再帰の打ち切りをうまく扱うための機能です。
SQL cycle(modern SQL)
SQL:2023では既存のCYCLE句について、1) Cycle マークがbooleanに 2) 実際の値が省略できデフォルトがtrueとfalseに改善されました。
Oracle DB(11gR2以降), PostgreSQL(14以降), MariaDB(10.5以降)のみの実装です。
Firebirdを含めその他のRDBMSではCYCLE句自体未サポート です。
]]>
Firebird/InterBase
2023-12-08T08:40:47+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877698
SQL:2023 Firebirdの場合:Optional string types maximum length (T081)
本エントリはFirebird Advent Calendar 2023 七日目のエントリです。
本エントリの元ネタは以下のエントリです。
SQL:2023 is finished: Here is what's new(Peter Eisentraut)
VARCHAR(CHARACTER VARYING)型の最大長の指定を省略できるようになりました。つまり...
Firebird Advent Calendar 2023 七日目のエントリです。
本エントリの元ネタは以下のエントリです。
SQL:2023 is finished: Here is what's new(Peter Eisentraut)
VARCHAR(CHARACTER VARYING)型の最大長の指定を省略できるようになりました。つまり、
VARCHAR(255)の代わりに、VARCHARが指定できるようになります。
これはPostgreSQLの方言でマニュアルにも以下のように書かれています(いました?)
「character varyingが長さ指定なしで使われた時は、いかなる長さの文字列でも受け付けます。 後者はPostgreSQLの拡張です」
以下の私のブログエントリをご参照ください。
ポスグレ、データ型で最大長なしは最大精度、ワイルドだろう?
Firebirdを含め、他のRDBMSでは当然実装されておりません 。。。。
]]>
Firebird/InterBase
2023-12-07T06:58:07+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877697
SQL:2023 Firebirdの場合:Multi-character TRIM functions (T056)
本エントリはFirebird Advent Calendar 2023 六日目のエントリです。
本エントリの元ネタは以下のエントリです。
SQL:2023 is finished: Here is what's new(Peter Eisentraut)
LTRIMは文字列の左から連続する空白を取り除きます。右から取り除く場合はRTRIMを利...
Firebird Advent Calendar 2023 六日目のエントリです。
本エントリの元ネタは以下のエントリです。
SQL:2023 is finished: Here is what's new(Peter Eisentraut)
LTRIMは文字列の左から連続する空白を取り除きます。右から取り除く場合はRTRIMを利用します。
これが基本ですが、第二引数に文字列を指定することにより、その文字を取り除くことができる拡張があります。
それが今回SQL標準となりました。
基本をサポート: Firebird, MySQL, IBM Db2(9.7以前), SQL Server(2019以前)
拡張をサポート: Oracle DB, PostgreSQL, SQLite, IBM Db2(10.1以降), SQL Server(2022以降)
今となってはFirebirdとMySQLだけ拡張構文をサポートしていない ので、サポートが望まれます。
[2023-12-07 追記]
MySQL 既存のFR(Feature Request)に実装をすすめるコメントを追加しました。
Bug#101719 Add an optional argument c after a comma in the [L|R]TRIM(s) functions
]]>
Firebird/InterBase
2023-12-06T07:45:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877696
SQL:2023 Firebirdの場合:String padding functions (T055)
本エントリはFirebird Advent Calendar 2023 五日目のエントリです。
本エントリの元ネタは以下のエントリです。
SQL:2023 is finished: Here is what's new(Peter Eisentraut)
LPADは指定した文字数になるまで文字列の左側に文字列を埋め込む関数です。
文字列...
Firebird Advent Calendar 2023 五日目のエントリです。
本エントリの元ネタは以下のエントリです。
SQL:2023 is finished: Here is what's new(Peter Eisentraut)
LPADは指定した文字数になるまで文字列の左側に文字列を埋め込む関数です。
文字列の右側に埋め込みたい場合はRPAD関数を使います。Microsoft SQL Server以外の
RDBMSで実装されています。当然Firebirdにも実装があります。
SQL Serverの場合は、パディングする文字をREPLICATE('0', 10)とかで生成して、LPADの場合は
文字列の前に、RPADの場合は、文字列の後に結合して、LPADの場合はRIGHT関数、RPADの場合は
LEFT関数で合成するといいでしょう。これは以下のエントリのMethod 3です。
Left Padding in SQL Server – 3 LPAD() Equivalents
]]>
Firebird/InterBase
2023-12-05T22:21:18+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877695
SQL:2023 Firebirdの場合:GREATEST and LEAST (T054)
本エントリはFirebird Advent Calendar 2023 四日目のエントリです。
本エントリの元ネタは以下のエントリです。
SQL:2023 is finished: Here is what's new(Peter Eisentraut)
引数のうち最大値を返すGREATEST, 最小値を返すLEASTという関数。Oracle DBの方言な...
Firebird Advent Calendar 2023 四日目のエントリです。
本エントリの元ネタは以下のエントリです。
SQL:2023 is finished: Here is what's new(Peter Eisentraut)
引数のうち最大値を返すGREATEST, 最小値を返すLEASTという関数。Oracle DBの方言なんですが、
多くのデータベースで便利に使われてきました。
PostgreSQL
MySQL/MariaDB
Db2
SQL Server (2022より)
BigQuery
これが今回SQL標準に取り入れられました。パチパチ。
さて、Firebirdでは独自関数のMAXVALUEとMINVALUEが同様の動作をします。
つまりGREATEST/LEASTは実装されていません。同様にSQLiteでもサポートされておらず
代わりに多引数のMAX/MINを使います(これはDb2でも使えます)
PostgreSQLだけNULLの扱いが違っていたようなんですが、いまもそのままなんですかねー。
GREATEST(), LEAST()関数はPostgreSQLだけNULLの扱いが違う
[2023-12-06 追記] PostgreSQLは今もNULL無視で、上記のブログでは未実装だったMicrosoft SQL Serverの実装もNULL無視、だそうです。その他引数の最小値、最大値も含めて詳細は以下参照。
SQL Greatest(), least()(modernSQL)
]]>
Firebird/InterBase
2023-12-04T06:00:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877694
SQL:2023 Firebirdの場合:ORDER BY in grouped table (F868)
本エントリはFirebird Advent Calendar 2023 三日目のエントリです。
本エントリの元ネタは以下のエントリです。
SQL:2023 is finished: Here is what's new(Peter Eisentraut)
GROUP BYに含まれていない列をSELECTリストに加える場合、本来はエラーになるべきで...
Firebird Advent Calendar 2023 三日目のエントリです。
本エントリの元ネタは以下のエントリです。
SQL:2023 is finished: Here is what's new(Peter Eisentraut)
GROUP BYに含まれていない列をSELECTリストに加える場合、本来はエラーになるべきですが、
それを合法的に追加できるANY_VALUE()がMySQLをはじめとする多くのRDBMSに実装されはじめました。
F868はそれに類似していますが、GROUP BYに含まれていない列をORDER BYするとどうなるか、
というところを明確にしたものです。以下の例だとproduct_codeはSELECTリストにない列で、
それでORDER BYしています。
CREATE TABLE product (
product_id int PRIMARY KEY,
product_name varchar(20),
product_code varchar(20) UNIQUE
);
CREATE TABLE product_part (
product_id int,
part_id int,
num int,
PRIMARY KEY (product_id, part_id)
);
SELECT product.product_id, sum(product_part.num)
FROM product JOIN product_part ON product.product_id = product_part.product_id
GROUP BY product.product_id
ORDER BY product.product_code;
多くの実装ではこれを許容しているようですが、Firebirdの場合は律儀にエラーを出します。
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Invalid expression in the ORDER BY clause (not contained in either an aggregate function or the GROUP BY clause
]]>
Firebird/InterBase
2023-12-03T09:51:18+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877693
「PostGIS入門の入門」に入門してきた。
RDBMS-GIS(地理情報・位置情報) Advent Calendar 2023の二日目のエントリです。
GIS関連、どこから始めるか、というのは永遠の課題のような気がしますが、
PostgreSQLにGIS拡張をほどこしたPostGISというのは有力な選択肢のひとつです。
以前Let's POSTGRESに「Pos...
RDBMS-GIS(地理情報・位置情報) Advent Calendar 2023の二日目のエントリです。
GIS関連、どこから始めるか、というのは永遠の課題のような気がしますが、
PostgreSQLにGIS拡張をほどこしたPostGISというのは有力な選択肢のひとつです。
以前Let's POSTGRESに「PostGISとは?」というエントリ を書いた寺元さんが
今年のポスグレカンファレンスで発表した【T4】PostGIS入門の入門が最新版の
入門ということで入門してみました。以下のリンクの講演スライドから見れますので、
入門がまだな方は是非入門してみてください。
PostgreSQL Conference Japan 2023■プログラム
私は寺元さんの発表で「計算量では以下ですので、
GEOMETRY < GEOGRAPHY
PostGIS マニュアルは GEOGRAPHYおしてますが、
登壇者は(無暗に計算量を増やしたくないので)GEOMETRY推し。
必要なときにGEOGRAPHYにキャスト」というのが耳に残りました。
]]>
データベース
2023-12-02T21:52:42+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877692
SQL:2023 Firebirdの場合: UNIQUE null treatment (F292)
本エントリはFirebird Advent Calendar 2023の二日目のエントリです。
今日から約二週間分のエントリの元ネタは以下のエントリです。
SQL:2023 is finished: Here is what's new(Peter Eisentraut)
一つのカラムに対してUNIQUEインデックスをつけると、NULL以外...
Firebird Advent Calendar 2023の二日目のエントリです。
今日から約二週間分のエントリの元ネタは以下のエントリです。
SQL:2023 is finished: Here is what's new(Peter Eisentraut)
一つのカラムに対してUNIQUEインデックスをつけると、NULL以外の値はユニークですが、
NULLは重複登録可能です!
と言いたいところですが、IBM Db2とMicrosoft SQL Serverは違います。
NULLもあたかも値のように一つしか登録できません。詳細は以下のエントリをご参照ください。
DB2での一意インデックス(Unique index)の実装について
では、マルチカラムのUNIQUEインデックスはどうでしょうか?
IBM Db2とMicrosoft SQL Serverはわかりやすいです。
値と同様にユニークネスを担保するために、二回目の? のINSERTはエラーになります。
CREATE TABLE t1 (
a int,
b int,
c int,
UNIQUE (a, b, c)
);
and
INSERT INTO t1 VALUES (1, NULL, NULL);
INSERT INTO t1 VALUES (1, NULL, NULL); -- ?
ややこしいのは、Firebird, Oracle DB, PostgreSQLです。
これらのRDBMSは複合UNIQUEインデックスのときには、NULLを値のように扱います。
つまり、二回目の? のINSERTはエラーになるのです。
そう考えるとMySQLの実装は理にかなっています。
二回目の? のINSERTはエラーなく実行できます。
私がこの挙動の違いに気づいたのは、松信さんが書いた以下の本のp.55のColumn「一意インデックスとNULL値」でした。
もう十年以上も前ですね。
現場で使えるMySQL(翔泳社)
その後以下の拙書にて、MySQL以外のRDBMS動作をエミュレートするトリガーをMySQLに実装するサンプルを
「p.211 ■他社RDBMSインデックスのエミュレート」として掲載しています。
プロになるためのデータベース技術入門(技術評論社)
UNIQUE null treatment (F292)は、このような実装ごとの動作の違いを
明確にしたもので、MySQLのように二回目の? のINSERTがエラーなく実行できるようなUNIQUE制約を
UNIQUE NULLS DISTINCTで定義、MySQL以外のデータベース(Firebird)のようなふるまいを
UNIQUE NULLS NOT DISTINCTで定義するように定めました。
Firebirdでの一意インデックス(Unique index)の実装について
# IBM Db2以外のデータベースでは一意制約と一意インデックスは同じです。(一意制約実現のために一意インデックスを張るので) Db2では一意制約と一意インデックスは違うものになります。詳細は冒頭の「DB2での一意インデックス」のエントリをご参照ください。
]]>
データベース
2023-12-02T16:05:06+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877691
Firebird 2023年の振り返り
本エントリはFirebird Advent Calendar 2023の一日目のエントリです。
「しばしばリリース」はかなえられませんでしたが、今年は以下のリリースがありました。
2023-08-04 Firebird 3.0.11
2023-08-04 Firebird 4.0.3
2023-11-08 Firebird 4.0.4
サブリリ...
Firebird Advent Calendar 2023の一日目のエントリです。
「しばしばリリース」はかなえられませんでしたが、今年は以下のリリースがありました。
2023-08-04 Firebird 3.0.11
2023-08-04 Firebird 4.0.3
2023-11-08 Firebird 4.0.4
サブリリースのため特に目立った拡張はありません。
そのため、本年のFirebird Advent Calendarでは、リリースされたSQL標準(SQL:2023) について
Firebirdの視点から解説してみようと思います。
SQL:2023 is finished: Here is what's new(Peter Eisentraut)
]]>
データベース
2023-12-01T12:45:22+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877690
2023年のFirebird
本エントリはFirebird Advent Calendar 2022の25日目です。
(今週忙しくていろいろ抜けてますが、年末〜年始に向けて順に埋めていきます)
さて、2023年のFirebird ! といえば、まずはFirebird 4.0の話になります。
....と書いて、この話はもう6年くらいしてますねぇ...
Firebird Advent Calendar 2022の25日目です。
(今週忙しくていろいろ抜けてますが、年末〜年始に向けて順に埋めていきます)
さて、2023年のFirebird ! といえば、まずはFirebird 4.0の話になります。
....と書いて、この話はもう6年くらいしてますねぇ。
今年(2022年)は3.0/4.0で三つのリリースが行われました。
2022-02-15 Firebird 3.0.9
2022-06-08 Firebird 3.0.10
2022-08-11 Firebird 4.0.2
より多くのリリースを宣言していた時期もありましたが、終わってみればいつも通り(?) のマイペースっぷり。
止らず、急がず、リリースを続けてくれていっているのはありがたいですね。
組み込みではSQLiteが席巻していていますが、SQLiteはClient/Server構成ができません。
またClient/Server構成がデフォルトのPostgreSQL/MySQLでは、PostgreSQLでは「やらないことリスト」に
組み込み構成が入ってないことは有名ですし(といいつつ本家以外でembedded-postgre sというのを見つけてしまった)
MySQLも組み込みの構成のサポートを8.0でやめてしまいました。
MySQL 8.0: Retiring support for libmysqld
Firebirdあゆみは鈍いかもしれませんが、来年2023年も堅実に進めていってほしいですねー。
]]>
Firebird/InterBase
2022-12-25T18:10:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877689
MySQLとSQLiteからCSVファイルにSQLを投げてみた
MySQLとSQLiteからCSVファイルにSQLを投げてみた
本エントリは以下のAdvent Calendar 18日目のクロスエントリです。
o SQLite Advent Calendar 2022
o MySQL Advent Calendar 2022
ほとんどのRDBMSでは、CSVファイルをデータベースにインポートする機能がありま...
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
]]>
クロスデータベース
2022-12-18T23:39:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877688
MySQL闇歴史2 (My|m)SQL
MySQL闇歴史2 (My|m)SQL
これは MySQL闇歴史 Advent Calendar 2022 の2枚目のカレンダー18日目のエントリーです。
知名度:★☆☆☆☆
闇度 :★★☆☆☆
とって代わった度:★★★★★
MySQLについては、LAMPで一躍有名になりましたが、実は最初は互換路線でシーンにあらわ...
MySQL闇歴史2 (My|m)SQL
これは MySQL闇歴史 Advent Calendar 2022 の2枚目のカレンダー18日目のエントリーです。
知名度:★☆☆☆☆
闇度 :★★☆☆☆
とって代わった度:★★★★★
MySQLについては、LAMPで一躍有名になりましたが、実は最初は互換路線でシーンにあらわれました。
以下のタイトルの本がでるくらいです。
MySQL & mSQL
といっても、22年前ですね orz
MySQLがこんなにも世の中に浸透したのは、実は先行するmSQLとC-API互換だったことが大きく作用しています。
mSQL用の先行するツール群がそのままMySQLに使えるのはなかなかいい選択でしたね。
現在でも、各種互換戦略はうまく回っています。フロントエンドをMySQL互換にしたり、PostgreSQL互換にすることによって、多くのツール類はそのまま使えますし、DBAやブログラマも、多くの人員がMySQL/PostgreSQL互換の恩恵をうけて、仕事することができます。
いくら画期的でも、いくら動作が速くても、見も知らないAPIを利用するほど、DBAやプログラマも暇ではない、のです。
(某DBが独自路線をいき、神林さんところの剱がPostgreSQLをフロントエンド に据えたのは対照的です)
さて、mSQLですが、実は今も地道な活動をしています。去年の10月にリリースされたmSQL 4.4が最新です。
mSQL - The Original Freely Available SQL Implementation
]]>
MySQL
2022-12-18T22:36:32+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877687
実行計画: メモリを使ったソート
実行計画: メモリを使ったソート
本エントリは次のAdvent Calendar 2022 17日目のクロスエントリです。
SQLite Advent Calendar 2022
Firebird Advent Calendar 2022
SQLiteもFirebirdもソートに利用できるインデックスがない場合には、メモリに領域をとりソー...
実行計画: メモリを使ったソート
本エントリは次の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
]]>
クロスデータベース
2022-12-17T09:23:39+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877686
SQLite 最新版(2022-11-16 (3.40.0))の新機能: SQLite to WASM (1)
本エントリはSQLite Advent Calendar 2022の16日目です。
SQLite Release 3.40.0 On 2022-11-16
1.Add support for compiling SQLite to WASM and running it in web browsers. NB: The WASM build and its interfaces are considered "beta" and are subject to mi...
SQLite Advent Calendar 2022の16日目です。
SQLite Release 3.40.0 On 2022-11-16
1.Add support for compiling SQLite to WASM and running it in web browsers. NB: The WASM build and its interfaces are considered "beta" and are subject to minor changes if the need arises. We anticipate finalizing the interface for the next release.
SQLiteをWASMにコンパイルし、Webブラウザーで実行するためのサポートを追加しました。
注意:WASMビルドとそのI/Fは「ベータ版」とみなされ、必要に応じてマイナーな変更が加えられる可能性があります。次のリリースでI/Fを完成させる予定です。
とうとうWASM対応ですか!
その前に、WASMって何?
WASMはWebAssemblyのことです。あんどうさんの以下の本(私は技術書展で買った記憶あり)や、
Qiitaの記事を読むとわかりますが、ブラウザ上で動作させるためのバイナリコードの新しいフォーマットのことです。
作って学ぶWebAssembly: TypeScriptとDenoで作るWasmランタイム (空想工学舎) Kindle版
WebAssemblyとは(Qiita)
# こういった本 も参考になるかも
時代はRDB on WASM ?
ちょうど本日このようなツイートがありました。さっと仕様確認するには便利かも。
PostgreSQLをブラウザ上で実行するOSS「Postgres-WASM」を触ってみた
次回以降SQLite版も確認してみたいと思います。
]]>
SQLite
2022-12-16T20:32:46+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877685
ジムという男
ジムという男
本エントリは以下のAdvent Calendar 15日目のクロスエントリです。
o Firebird Advent Calendar 2022
o MySQL闇歴史 Advent Calendar 2022 の2枚目
o SQLite Advent Calendar 2022
MySQL闇歴史のエントリでもとりあげられましたが、ジムといえばM...
ジムという男
本エントリは以下の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
おあとがよろしいようで(笑)
# ちなみに本エントリのタイトルは以下の番組へのオマージュです。
肥後という男(アメトーーク!)
]]>
クロスデータベース
2022-12-15T20:29:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877684
SQLiteのバックエンドでInnoDB?
SQLiteのバックエンドでInnoDB?
本エントリはSQLite Advent Calendar 2022の14日目です。
私はMySQL闇歴史2 というAdvent Calendarにも参加していて
その9日目のエントリにEmbedded InnoDBの話を書きました。
yoku0825さんが、Twitterでとりあげてくれたのです...
SQLiteのバックエンドでInnoDB?
本エントリはSQLite Advent Calendar 2022 の14日目です。
私はMySQL闇歴史2 というAdvent Calendar にも参加していて
その9日目のエントリにEmbedded InnoDBの話を書きました。
yoku0825さんが、Twitterでとりあげてくれたのですが、
そのツイートに対して、以前InnoDBの開発に携わっていたSunnyからリプライがありました。
もしかしたらSQLiteのバックエンドでInnoDBが動き
それが世の中に出る、といった未来もあったのかも知れません。チト残念。
]]>
SQLite
2022-12-14T18:18:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877683
SQLite 最新版(2022-11-16 (3.40.0))の新機能: クエリプランナの拡張(b),(c)
SQLite 最新版(2022-11-16 (3.40.0))の新機能: クエリプランナの拡張(b),(c)
本エントリはSQLite Advent Calendar 2022の13日目です。
SQLite Release 3.40.0 On 2022-11-16
3.Query planner enhancements:
b.Recognize covering indexes on tables with more...
本エントリはSQLite Advent Calendar 2022 の13日目です。
SQLite Release 3.40.0 On 2022-11-16
3.Query planner enhancements:
b.Recognize covering indexes on tables with more than 63 columns where columns beyond the 63rd column are used in the query and/or are referenced by the index.
Extract the values of expressions contained within expression indexes where practical, rather than recomputing the expression.
んー、わからん(笑)
c.The NOT NULL and IS NULL operators (and their equivalents) avoid loading the content of large strings and BLOB values from disk.
んー、こういうことか?
注: test.sqliteファイルが6Gくらい領域使います
sqlite3 test.sqlite
create table blobnull(
col1 int,
col2 blob);
INSERT INTO blobnull(col1, col2)
WITH RECURSIVE
cte(x,y) AS (
SELECT random(),printf('%.*c', abs(random()) % 100000, 'x')
UNION ALL
SELECT random(),printf('%.*c', abs(random()) % 100000, 'x')
FROM cte
LIMIT 100000
)
SELECT x,y FROM cte;
INSERT INTO blobnull values(NULL, NULL);
少し古いSQLite version 3.31.1: 28秒
sqlite> select current_timestamp;select count(*) from blobnull where col2 is null;select current_timestamp;
2022-12-13 12:40:59
1
2022-12-13 12:41:27
最新のSQLite version 3.40.0: 2秒
sqlite> select current_timestamp;select count(*) from blobnull where col2 is null;select current_timestamp;
2022-12-13 12:28:47
1
2022-12-13 12:28:49
確かにはやくなってる。
1バイトキャラクタ(ここでは'x')をMySQLのREPEAT関数 風に実行する際の元ネタ(printf )はこちら。
How to emulate REPEAT() in SQLite
]]>
SQLite
2022-12-13T21:42:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877682
SQLite 最新版(2022-11-16 (3.40.0))の新機能: クエリプランナの拡張(a)
SQLite 最新版(2022-11-16 (3.40.0))の新機能: クエリプランナの拡張(a)
本エントリはSQLite Advent Calendar 2022の12日目です。
SQLite Release 3.40.0 On 2022-11-16
3.Query planner enhancements:
a.Recognize covering indexes on tables with more tha...
本エントリはSQLite Advent Calendar 2022 の12日目です。
SQLite Release 3.40.0 On 2022-11-16
3.Query planner enhancements:
a.Recognize covering indexes on tables with more than 63 columns where columns beyond the 63rd column are used in the query and/or are referenced by the index.
んー、こういうことかな。
create table ci64(
col1 int,
col2 int,
col3 int,
col4 int,
col5 int,
col6 int,
col7 int,
col8 int,
col9 int,
col10 int,
col11 int,
col12 int,
col13 int,
col14 int,
col15 int,
col16 int,
col17 int,
col18 int,
col19 int,
col20 int,
col21 int,
col22 int,
col23 int,
col24 int,
col25 int,
col26 int,
col27 int,
col28 int,
col29 int,
col30 int,
col31 int,
col32 int,
col33 int,
col34 int,
col35 int,
col36 int,
col37 int,
col38 int,
col39 int,
col40 int,
col41 int,
col42 int,
col43 int,
col44 int,
col45 int,
col46 int,
col47 int,
col48 int,
col49 int,
col50 int,
col51 int,
col52 int,
col53 int,
col54 int,
col55 int,
col56 int,
col57 int,
col58 int,
col59 int,
col60 int,
col61 int,
col62 int,
col63 int,
col64 int,
col65 int,
col66 int,
col67 int,
col68 int);
INSERT INTO ci64(col1, col30, col67)
WITH RECURSIVE
cte(x,y,z) AS (
SELECT random(),random(),random()
UNION ALL
SELECT random(),random(),random()
FROM cte
LIMIT 1000000
)
SELECT x,y,z FROM cte;
create index test on ci64(col1,col30,col67);
ちょっと前のSQLiteで実行
sqlite> explain query plan select col1,col30,col67 from ci64;
QUERY PLAN
`--SCAN TABLE ci64
最新版(2022-11-16 (3.40.0))で実行
sqlite> explain query plan select col1,col30,col67 from ci64;
QUERY PLAN
`--SCAN ci64 USING COVERING INDEX test
なるほど、なるほど。
だがしかし、もともとのテーブル定義に問題あるんじゃないか :)
]]>
SQLite
2022-12-12T17:59:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877681
実行計画: インデックススキャン
実行計画: インデックススキャン
本エントリはSQLite Advent Calendar 2022の11日目とFirebird Advent Calendar 2022の11日目のクロスエントリです。
前回の続き。
SQLiteで以下のテーブルとデータを作成して確認してみましょう。
CREATE TABLE t3(id int not...
本エントリは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
]]>
クロスデータベース
2022-12-11T21:01:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877680
SQLite 最新版(2022-11-16 (3.40.0))の新機能: リカバリー機能
SQLite 最新版(2022-11-16 (3.40.0))の新機能: リカバリー機能
本エントリはSQLite Advent Calendar 2022の10日目のエントリです。
SQLite 最新版では、破損したデータベースファイルから一部のコンテンツを回復できるリカバリ拡張機能が追加されました。
もとも...
本エントリはSQLite Advent Calendar 2022 の10日目のエントリです。
SQLite 最新版では、破損したデータベースファイルから一部のコンテンツを回復できるリカバリ拡張機能が追加されました。
もともと壊れにくいSQLiteのデータファイルですが、以下のコマンドでリカバリ用のSQL作成ができます。
sqlite3 corrupt.db .recover > data.sql
それを以下のコマンドでデータベースの再構築ができます。
sqlite3 recovered.db < data.sql
オプションもいくつかありますので、詳しくは以下のマニュアルをチェック!
Recovering Data From A Corrupt SQLite Database
]]>
SQLite
2022-12-10T09:54:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877679
MySQL闇歴史2 IBMDB2Iストレージエンジン
MySQL闇歴史2 IBMDB2Iストレージエンジン
本エントリはMySQL闇歴史 Advent Calendar 2022 の2枚目のカレンダー10日目のエントリーです。
梶山さんが以下のエントリの最後で触れているようにIBMDB2Iというストレージエンジンがありました。
MySQL闇歴史 ストレー...
本エントリはMySQL闇歴史 Advent Calendar 2022 の2枚目のカレンダー10日目のエントリーです。
梶山さんが以下のエントリの最後で触れているようにIBMDB2Iというストレージエンジンがありました。
MySQL闇歴史 ストレージエンジンいろいろ
使ったことがある人はほとんどいないと思われます。
やはり旧AS400上という動作環境の敷居が高すぎて。。。。
しかし、ご安心を(何が?)
読むだけで理解した気になれるRED BOOKが公開されていますので、
モノ好きな方はご覧あれ。220ページあります!!
Using IBM DB2 for I as a Storage Engine of MySQL
]]>
DB2
2022-12-10T00:23:43+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877678
MySQL闇歴史2 Embedded InnoDB
MySQL闇歴史2 Embedded InnoDB
本エントリはMySQL闇歴史 Advent Calendar 2022 の2枚目のカレンダー9日目のエントリーです。
梶山さんが以下のエントリで触れているようにInnoDBには組み込みInnoDB(Embedded InnoDB)というものが一瞬ありました。
MySQL闇歴史 I...
本エントリはMySQL闇歴史 Advent Calendar 2022 の2枚目のカレンダー9日目のエントリーです。
梶山さんが以下のエントリで触れているようにInnoDBには組み込みInnoDB(Embedded InnoDB)というものが一瞬ありました。
MySQL闇歴史 Innobase Oy
ニュース にもなりましたし、Web Archiveをたぐってみるとバージョン1.0.6までリリース されました。
が、しかし、継続はされず、こっそりとフェードアウトしました。
何が悪かったかというと、やはり「組み込み(Embedded)」という名前で多くの人が想像するものと、
実際にリリースされたものに大きな乖離があったからではないかと類推します。
通常組み込みというと、以下のような三つのプロファイルがあり、用途に応じて構成変更して利用するのが一般的かと思います。
(1) マイクロ
(2) スタンダード
(3) エンタープライズ
そして、なにがしかの母艦との連携機能があるのも、一般的かと思います。
MySQLも長らく組み込みライブラリlibmysqld(SQLite的なプロセスにリンクして単なるDBライブラリとして利用する)を提供してきましたが、サイズ的にはMySQL 4.xは(3)ぐらいのサイズといえなくもないのですが、その後のバージョンは、(3)よりもあきらかに大きなサイズを構成変更不可の形で提供していました。
また「母艦との連携機能」も、レプリケーション的なものがあればいいのですが、DBライブラリとして利用するとレプリケーションは利用できなかったんですよね。残念なことに。
そしてとうとうMySQL 8.0で終了してしまいます。
MySQL 8.0: Retiring support for libmysqld
MySQLも4.x時代にMyISAMを利用したDeep Embeddedなシステムは存在したのですが、
やはりInnoDBをメインとした潤沢なRAMと、UNDO/REDOログ用のディスクが必要な構成は
組み込みには向いてなかった、としかいいようがありません。
んで、もってEmbedded InnoDBはまったく使われなかったか、というと実は今は亡きBashoで開発されていた、Riakで利用されていました。
Innostore — connecting Erlang to InnoDB
APIも公開されています。
Innostore is a simple Erlang API to Embedded InnoDB. - GitHub
そうか、こういう組み込みならありなのか、と、当時は思いましたが、
残念ながら、その後広がりをみせることはありませんでした。
]]>
MySQL
2022-12-09T23:59:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877677
UPSERT大全2022
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とは対象とするテーブルに対象行が...
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ユーザはその点も考慮して
使い分けする、というぜいたくができます(笑)
]]>
クロスデータベース
2022-12-09T08:54:23+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877676
db tech showcaseでのSQLite
db tech showcaseでのSQLite
本エントリはSQLite Advent Calendar 2022の8日目のエントリです。
DB界隈では人気のイベントdb tech showcase!
今年は1日目と2日目はオンライン配信、3日目の最終日はハイブリッド(会場でのオフラインセミナー+オンラインライブ配信)...
本エントリはSQLite Advent Calendar 2022 の8日目のエントリです。
DB界隈では人気のイベントdb tech showcase!
今年は1日目と2日目はオンライン配信、3日目の最終日はハイブリッド(会場でのオフラインセミナー+オンラインライブ配信)にて実施されました。
実は過去、SQLiteの開発者を招いて、SQLiteについてのセミナーも行ってました。
その際のセッション資料が残ってますので、ここに紹介します。
[db tech showcase Tokyo 2017] A11: SQLite - The most used yet least appreciated database engine by SQLite.org - Richard Hipp
[db tech showcase Tokyo 2017] C23: Lessons from SQLite4 by SQLite.org - Richard Hipp
以前のDB TECH SHOWCASEは、ファウンダーの小幡さんの暴走(あくまで個人の感想です)で、
海外から様々な方々が招聘されていました。
小幡一郎(オバタイチロウ)
小幡さんによるSQLite4の開発物語(日本語)の記事はこちら。
SQLite4の開発物語
現在はリタイアされている小幡さん、お元気ですかねぇ?
]]>
SQLite
2022-12-08T18:19:57+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877675
実行計画: フルテーブルスキャン
実行計画: フルテーブルスキャン
本エントリはSQLite Advent Calendar 2022の7日目と
Firebird Advent Calendar 2022の7日目のクロスエントリです。
SQLiteについては、昨日と一昨日のエントリを見れば、今回のクエリの意味と、実際の確認ができるはず。
explai...
本エントリは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)
徐々に複雑な実行計画を見ていきたいと思ってます。
]]>
クロスデータベース
2022-12-07T18:31:22+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877674
SQLiteクエリのみでダミーデータを作る
SQLiteクエリのみでダミーデータを作る
本エントリはSQLite Advent Calendar 2022の6日目のエントリです。
実行計画を見るのにダミーデータをたくさん格納したテーブルがすぐに作れると楽です。
最近のSQLiteはCTE(共通表式: Common Table Expression)が利用できる...
本エントリはSQLite Advent Calendar 2022 の6日目のエントリです。
実行計画を見るのにダミーデータをたくさん格納したテーブルがすぐに作れると楽です。
最近のSQLiteはCTE(共通表式: Common Table Expression)が利用できるので、
例えば100万行のt3テーブルを以下のように簡単に作れます。
CREATE TABLE t3(id);
INSERT INTO t3
WITH RECURSIVE
cte(x) AS (
SELECT random()
UNION ALL
SELECT random()
FROM cte
LIMIT 1000000
)
SELECT x FROM cte;
SQLiteに限らずCTEが利用できるRDBMSならできますので、是非。
元ネタは以下のエントリ。
How to insert random data into a SQLite table using only queries?(stackoverflow)
]]>
SQLite
2022-12-06T18:00:52+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877673
SQLiteの実行計画
SQLiteの実行計画
本エントリはSQLite Advent Calendar 2022の5日目です。
SQLクエリのパフォーマンスチューニングをするのであれば、実行計画の確認は不可欠。
SQLiteでは、EXPLAIN QUERY PLANを利用して実行計画を確認します。
詳しくはマニュアルをご参照く...
本エントリはSQLite Advent Calendar 2022 の5日目です。
SQLクエリのパフォーマンスチューニングをするのであれば、実行計画の確認は不可欠。
SQLiteでは、EXPLAIN QUERY PLAN を利用して実行計画を確認します。
詳しくはマニュアルをご参照ください。
EXPLAIN QUERY PLAN
runebook.devによる日本語訳 はこちら。
MySQLだと奥野さんの説明 や、Oracleだと次のような興味深いやつがありますので、
ここらへんをネタにSQLiteも見てみようかな、と思ってます。
実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺) Advent Calendar 2019
帰ってきた! 実行計画は、SQL文のレントゲン写真だ! Oracle Database (全部俺) Advent Calendar 2022
]]>
SQLite
2022-12-05T14:15:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877672
SQLiteの仮想テーブル
SQLiteの仮想テーブル
本エントリはSQLite Advent Calendar 2022の4日目のエントリです。
SQLiteには独自の仮想テーブルという仕組みがあります。
SQLiteの仮想テーブルの仕組み
# 参照しているのはrunebook.devという(機械)翻訳ドキュメントを公開している(?) ...
本エントリはSQLite Advent Calendar 2022 の4日目のエントリです。
SQLiteには独自の仮想テーブルという仕組みがあります。
SQLiteの仮想テーブルの仕組み
# 参照しているのはrunebook.devという(機械)翻訳ドキュメントを公開している(?) サイトです。
これはMySQLのストレージエンジンほどカッチリはしてないものの、SQL文からアクセス可能なインターフェースを、あたかもテーブルであるかのように公開することができます。たとえばCSVファイルをそのままCSVテーブルのように参照できたりします。こんな感じです。
SQLiteからCSVファイルにSQLを投げてみた
共有ライブラリを作成しなければならないので、そこが少しカベになりますが、
なかなか使える機能だと思います。
]]>
SQLite
2022-12-04T09:30:58+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877671
アプリからのクソクエリを変更できない闇に対する防衛術2022[MySQL]
アプリからのクソクエリを変更できない闇に対する防衛術2022[MySQL]
本エントリは闇の魔術に対する防衛術 Advent Calendar 2022の4日目とMySQL Advent Calendar 20224日目のクロスエントリです。
1.プロローグ
兵隊「隊長! 今度採用したアプリケーション、バックエ...
本エントリは闇の魔術に対する防衛術 Advent Calendar 2022 の4日目とMySQL Advent Calendar 2022 4日目のクロスエントリです。
1.プロローグ
兵隊「隊長! 今度採用したアプリケーション、バックエンドはMySQLですが、やたら動作が遅いんです」
隊長「そんなときにはな、急いで口で...じゃなくて、二年前のこのエントリを読むのだ」
アプリからのクソクエリを変更できない闇の魔術、に対する防衛術[MySQL]
兵隊「わかりました〜、おやか...じゃなくて、隊長」
兵隊「隊長! 防衛術そのいちで対応しようとしたんですが、レプリケーション構成で
なんと主キー(Primary Key)がないテーブルが何個かあるんですよ」
隊長「なーにー、やっちまったなぁ」
2.防衛術:不可視プライマリキー
隊長「男は黙って不可視プライマリキー」
MySQL 8.0.30からGenerated Invisible Primary Key(GIPK)モードがサポートされました。
MySQL 8.xなのに主キーを設定していないようなテーブルを持つアプリは撲滅しましょう。。。
MySQL 8.0.30の新機能:不可視プライマリキーについて(MySQL道普請)
13.1.20.11 Generated Invisible Primary Keys
これでテーブルを作り直して対応するでおじゃる。
]]>
MySQL
2022-12-04T09:15:16+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877669
SQLiteとうとうIS [NOT] DISTINCT FROM サポート。Firebirdは?
SQLiteとうとうIS [NOT] DISTINCT FROM サポート。Firebirdは?
本エントリはSQLite Advent Calendar 2022の3日目とFirebird Advent Calendar 2022の3日目のクロスエントリです。
長らくSQLiteは、ヌルセーフの等価演算子としてISを利用してきましたが、
2022-06-25...
本エントリは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でも使っているものだし。。。。
]]>
クロスデータベース
2022-12-03T09:02:33+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877670
MySQL闇歴史2 GPL狂想曲
MySQL闇歴史2 GPL狂想曲
これは MySQL闇歴史 Advent Calendar 2022 の2枚目のカレンダー2日目のエントリーです。
(12/2のエントリですが、まぁ書いてるのは12/3です)
1枚目の投稿に関連付けて。
MySQLの普及に一役かったのは、GPLと商用ライセンスのデュアルラ...
これは MySQL闇歴史 Advent Calendar 2022 の2枚目のカレンダー2日目のエントリーです。
(12/2のエントリですが、まぁ書いてるのは12/3です)
1枚目の投稿 に関連付けて。
MySQLの普及に一役かったのは、GPLと商用ライセンスのデュアルライセンスであることは
間違いないのですが、現在の形に落ち着くまではいろいろと紆余曲折がありました。
知名度:★★☆☆☆
闇度 :★★★☆☆
混乱度:★★★★★
LGPLで提供していたクライアントからの接続部品(Connector/ほにゃらら)をGPLに変更したり、
それにPHP開発団体が反発したり、FOSS例外追加して収束したり。
私もびっくりの「MySQLのプロトコルもGPLだ」といいだしたり、引っ込めたり、ラジバンダリ。
ここらへんは以下のスライドに詳しいです。
今さらだけどMySQLとライセンス(Ver.0.9.1)
以前の同僚のスチュワート・スミスからみたGPLに関する訴訟(NuSphere社)
MySQLの過去を振り返る Part 3
IPAが出している以下のpdfには、この訴訟を含む詳細が記述されている
ビジネスユースにおけるオープンソースソフトウエアの法的リスクに関する調査
MySQL AB時のようなドタバタは、サンマイクロ→オラクルと買収された後はなくなったように見えます。
]]>
MySQL
2022-12-02T21:40:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877668
2022年のFirebird(まとめ)
2022年のFirebird(まとめ)
本エントリはFirebird Advent Calendar 2022の2日目のエントリです。
今年のFirebirdの活動としては以下のものがありました。
o Jaybird (Firebird用JDBCドライバ)の更新。
4.0.5/4.0.6/4.0.7
3.0.12
o Firebird-driver (Firebird...
2022年のFirebird(まとめ)
本エントリはFirebird Advent Calendar 2022 の2日目のエントリです。
今年のFirebirdの活動としては以下のものがありました。
o Jaybird (Firebird用JDBCドライバ)の更新。
4.0.5/4.0.6/4.0.7
3.0.12
o Firebird-driver (Firebird用Pythonドライバ)の更新
1.4.3
o Firebird.NETとJodeJSドライバの更新
o Firebird本体の更新
3.0.9/3.0.10
4.0.2
o 19th Firebird Developers' Day (FDD) in Brazil
今年のFirebird Advent Calendarはこれらを順に説明していくことにします。
]]>
Firebird/InterBase
2022-12-02T08:26:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877667
2022年のSpatiaLite
本エントリはSQLite Advent Calendar 2022の2日目と
RDBMS-GIS(MySQL,PostgreSQLなど) Advent Calendar 2022の2日目のクロスエントリです。
SpatiaLiteは、SQLiteを拡張して空間位置情報を扱えるようにするものです。
ざっくりいうとSQLite+SpatiaLiteは、PostgreSQL...
SQLite Advent Calendar 2022の2日目と
RDBMS-GIS(MySQL,PostgreSQLなど) Advent Calendar 2022 の2日目のクロスエントリです。
SpatiaLiteは、SQLiteを拡張して空間位置情報を扱えるようにするものです。
ざっくりいうとSQLite+SpatiaLiteは、PostgreSQL + PostGISの組み合わせ にあたります。
バージョン4.xまでの概要は以下のページに詳しいです。
SpatiaLite?なにそれおいしいの?
Spatialite を使ってみる
さて2022年のSpartiaLiteは? といって調べてみたら、
なんと最終バージョンは5.0.1で2021年のリリース。
2022年の活動はございませんでした!!
(終)
....というわけにもいきませんので、現行最新バージョン5.0についていくつか。新規機能のリストは以下で確認できます。
news about SpatiaLite 5.0.0
Drop-RenameTable and RenameColumn
SQLite 3.25.0 of 2018-09-15でALTER TABLEが改善されたおかげで、そのバージョン以降のSQLiteを使う場合、 RenameTable() と RenameColumn() でそれぞれのリネームが利用できるようになります。
また、DropGeoTable()は非推奨(DEPRECATED)になりました。代わりにDropTable()を利用してください。
GIS関連の機能については、まだ違いや、新規機能のありがたみがわかるほど極めていないため、私が気になったのはプロシジャやストアドプロシジャの対応です。
SQL Procedures, Stored Procedures and Stored Variables: a quick intro
SQLite本体でストアドプロシジャ対応していませんが、結局ローカルで実行するだけなので、様々なインチキ(笑)で対応できそうだし、しているようでございます。この部分はSQLite Adventcalendarで触れていきたいですね。
]]>
SQLite
2022-12-02T07:54:33+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877666
SQLiteとうとうRIGHT JOINとFULL OUTER JOINサポート。Firebirdは?
本エントリは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を...
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すれば大丈夫です。
]]>
クロスデータベース
2022-12-01T08:02:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877665
2022年のFirebird
本エントリはFirebird Advent Calendar 2021の25日目です。
さて、2022年のFirebird ! といえば、まずはFirebird 4.0の話になります。
....と書いて、この話はもう5年くらいしてますねorz
2017年のFirebird
2018年のFirebird
2019年のFirebird
2020年のFirebird...
Firebird Advent Calendar 2021の25日目です。
さて、2022年のFirebird ! といえば、まずはFirebird 4.0の話になります。
....と書いて、この話はもう5年くらいしてますねorz
2017年のFirebird
2018年のFirebird
2019年のFirebird
2020年のFirebird
2021年のFirebird
今年、やっとこさFirebird 4.0がリリースされました。
そして今年(2021年)はこれを含めて三つのリリースが行われました。
2021-06-01 Firebird 4.0
2021-11-17 Firebird 3.0.8
2021-12-22 Firebird 4.0.1
また、今年のFirebird Advent Calendar 2021でnakagamiさんが報告してくれたように
Firebirdを利用するプロダクトや各種言語用のドライバが更新されていることは大変ありがたいです。
さて、Firebird以外のRDBMS界の2021年はどうだったでしょうか?
ここ数年はよくもわるくも話題の中心がCloudに移ってきたように思えます。
ビッグ3 もしくは「世界5大パブリッククラウド 」といわれる会社は、以下のパターンでデータベース戦略を立ててきました。
(1) もともとオンプレで自社開発していたものをクラウドに転用
(2) クラウド用に新規独自開発
(3) OSSのRDBMS(MySQL, PostgreSQL)をベースやフロントエンドにすえて、クラウド用に改善
去年に引き続き(3)が熱い! 感じですねぇ。
AmazonのRedshift(PostgreSQLベース)
Aurora(PostgreSQL, MySQLベース),
Microsoftの「Azure Database for PostgreSQL - Hyperscale (Citus) 」
アリババクラウドの「ApsaraDB for PolarDB(MySQLベース)」
今年はこんなトピックがありました。
・PostgreSQLのワイヤプロトコル実装のデータベース登場、躍進、新規追加。
Tsurugi
CockroachDB
Google Spanner
・MySQLのワイヤプロトコル実装のデータベース躍進、更新
TiDB
Amazon AuroraのMySQL 8.0互換 が登場。(RDSは5.6のサポート終了)
・Microsoft SQL Serverのワイヤプロトコル実装
AWS、SQL Serverアプリを実行可能な「Babelfish for Aurora PostgreSQL」を正式リリース(@IT)
MySQL本家からも(3)として昨年オラクルクラウド上でHeatWave が提供されました。
これは通常利用とDWH利用の両方に対応できるMySQLということで、今年多くの機能が拡張・追加されました。
シェルスクリプトマガジン に関連記事がありますので、気になる方はチェキら!
(2)としては、DWH御三家(Snowflake, BigQuery, Redshift) のうちSnowflakeが躍進した年でもありました。Advent Calendarを読むと勢いがわかるかも?
Snowflake Advent Calendar 2021
BigQuery Advent Calendar 2021
AWS Analytics Advent Calendar 2021
クラウド・分散DBのトレンドを追うなら、しのださん、こばさんの以下の連載なんてなかなかいいと思います。
クラウドネイティブ時代のデータベース(@IT)
「NewSQL」はPostgreSQLやMySQLを代替するのか? Oracle ACEの篠田氏と語るDBの過去、現在、未来(@IT)
まだまだ新型コロナの影響は大きいですが、めげずにねばっていきましょう。
メリークリスマス&ハッピーニューイヤー!
]]>
Firebird/InterBase
2021-12-25T15:37:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877664
TRUNCATE TABLEの実装とDELETE FROM
本エントリはFirebird Advent Calendar 2021の24日目のエントリ、
そして、MySQL Advent Calendar 2021の24日めのエントリを兼ねています。
Firebird 4.0で実装が予定されていた機能のうち、TRUNCATE TABLEは結局リリースに含まれなかったようです orz
Add TRUNCAT...
Firebird Advent Calendar 2021の24日目のエントリ、
そして、MySQL Advent Calendar 2021 の24日めのエントリを兼ねています。
Firebird 4.0で実装が予定されていた機能のうち、TRUNCATE TABLEは結局リリースに含まれなかったようです orz
Add TRUNCATE TABLE [CORE2479] #2892
なんかインチキでもいいから(内部的にdeleteやrecreateで代用)実装してほしいと思うのは俺だけですかね。。。。
各種RDBMSの実装状態は以下のとおり。
Oracle : ○
SQL Server : ○
Db2 : ○(9.7)
PostgreSQL : ○
MySQL : ○
Firebird : ×
SQLite : ×
SQL標準 : ○(2008)
MySQLやPostgreSQLでは古くから実装されています。
(Db2が未サポート、と書いてある本は古い本なので要注意)
一般的にTRUNCATE TABLEは、WHERE句なして全行DELETEを行うことと比較すると、以下の違いがあります。
(1) 行を走査することがないので、高速に削除できる。
(2) 削除に関連づけられたDELETEトリガを起動しない。
ただし(2)については、MySQL 5.5以降となります。
MySQL 5.1以前ではtruncate対象のテーブルに外部参照制約がついていた場合、
truncateが各行deleteにマッピングされる関係でdeleteトリガが起動していたような記憶があります。(後程検証して、本ブログに追記します)
実装により以下の特徴を持ちます。
(3) TRUNCATE後に、そのテーブルの領域をすぐに解放する。
(4) DDLとして実行され、ロールバック用のログを出力しない。
(5) テーブル定義に状態(例: オートインクリメント列)がある場合にリセットする。
(5) については、SQL標準のCONTINUE IDENTITY/RESTART IDENTITY句が実装されていたり、類似の機能がある場合には調整できるかもしれません。その他独自の拡張もあります。リッチな実装の例は以下のPostgreSQL 13のマニュアルを参照してみてください。
TRUNCATE(PostgreSQL13)
PostgreSQLのまったく素のtruncate table実装の仕様はPostgreSQL 8のマニュアルをみると確認できます。この当時はまだSQL標準にTRUNCATEはありませんでした。(8.1から複数のテーブルのTRUNCATE ができる拡張がなされています)
TRUNCATE(PostgreSQL 8.0)
SQLite, FirebirdではTRUNCATE TABLEをサポートしていないため、以下の方法で対応するしかありません。
・WHERE句なしのDELETE文を発行する
・テーブルの定義を取得して、DROP TABLE->CREATE TABLEを発行するか、Firebirdの方言であるRECREATE文を利用する。
ただしDELETEトリガーが起動されることや、RECREATE時に制約などが外されることにはご注意ください。
長大なテーブルを一度にWHERE句なしのDELETE文で削除しようとすると、データベースサーバーの負荷が一時的にあがり、他の同時実行しているクエリに影響を与えるケースもあります。そのような場合には、以下のような対処が考えられます。
・データベースの負荷が低い時間帯(深夜・明け方等)に実行する
・DELETEを発行する接続や、DELETE文そのものの実行優先順位をさげる(そのような機能がある場合)
・DELETEの範囲をLIMIT句などで制限して、それを繰り返し実行することにより、全件削除を行う。その際可能なら、一定の行数ごとにcommitして、ひとつのトランザクションで扱う行数を減らす。
しかしながら長大なテーブルをWHERE句で範囲指定(たとえば、特定の区間の日付)でDELETEする場合には、TRUNCATE TABLEは使えませんし、DELETE文も時間がかかります。
その場合パーティショニングがサポートされているRDBMSでは、日付でRANGEパーティショニングを行い、そのパーティションをドロップ(もしくはエクスチェンジ)する形で範囲指定の削除に代用できるケースがあります。
]]>
クロスデータベース
2021-12-24T09:08:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877663
Firebirdと異体字セレクタ(IVS)
本エントリはFirebird Advent Calendar 2021の22日目のエントリです。
PostgreSQL Unconference #29で篠田さんが面白い発表をしてました。
文字コードの話(Unicode IVS)
で、Firebird 4.0はどうなのよ? ということで確認いたしました。
先に結果をいうとPostgre...
Firebird Advent Calendar 2021の22日目のエントリです。
PostgreSQL Unconference #29 で篠田さん が面白い発表をしてました。
文字コードの話(Unicode IVS)
で、Firebird 4.0はどうなのよ? ということで確認いたしました。
先に結果をいうとPostgreSQL/Oracle/MySQLと同様でした。(二文字7-byteとして認識)
create table Ivs1(c varchar(10) character set utf8);
insert into Ivs1 values(x'e8919bf3a08481');
select c,character_length(c), octet_length(c) from Ivs1;
C CHAR_LENGTH OCTET_LENGTH
==================== ============ ============
葛 2 7
]]>
Firebird/InterBase
2021-12-22T21:24:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877662
浮動小数点数(単精度、倍精度)その先は? 拡張方向(四倍精度と十進浮動小数点数(DECFLOAT))と縮小方向(float2)
本エントリはFirebird Advent Calendar 2021の21日目のエントリです。
以前このようなエントリを書きました。
単精度、倍精度のその先は→四倍精度と十進浮動小数点数(DECFLOAT)
四倍精度は一部のCPUや、処理系(gcc での long double)には実装されましたが、
RDB...
Firebird Advent Calendar 2021の21日目のエントリです。
以前このようなエントリを書きました。
単精度、倍精度のその先は→四倍精度と十進浮動小数点数(DECFLOAT)
四倍精度は一部のCPUや、処理系(gcc での long double)には実装されましたが、
RDBMSのデータ型としてはなく、十進浮動小数点数(DECFLOAT)がSQL標準で定められました。
IBM Db2に続いて、Firebird 4.0でもDECFLOATサポートされました。
縮小方向では半精度(float2)が考えられます。
単精度でも精度つらいのに、その半分の精度で何がうれしいのかというと、
データ量が少ない分必要なストレージ領域は小さく、またSIMDやGPUといったベクトル演算を行う場合にはメモリバスを有効活用できることから、機械学習の分野などで活用が進んでいるそうです。
ここらへんの話はMySQLやFirebird, ノーマルのPostgreSQLにはありませんが、
PostgreSQLのPG-Stromに含まれているので、試せるのと、背景などが以下のブログにまとまっています。
半精度浮動小数点型(Float2)について(KaiGaiの俺メモ)
]]>
クロスデータベース
2021-12-21T15:50:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877661
Jaybird(FirebirdのJDBCドライバ)とlog4j2の脆弱性
本エントリはFirebird Advent Calendar 2021の20日目のエントリです。
巷を騒がしているlog4j2の脆弱性ですが、Firebird本体はJavaを利用していなくて関係なく、
JDBCドライバであるJaybirdについては、以下のステートメントが出ました。
Jaybird is not directly ...
Firebird Advent Calendar 2021の20日目のエントリです。
巷を騒がしているlog4j2の脆弱性ですが、Firebird本体はJavaを利用していなくて関係なく、
JDBCドライバであるJaybirdについては、以下のステートメントが出ました。
Jaybird is not directly vulnerable to the Log4j CVEs
直接は関係なく、間接的なリスク(Indirect risks)のみです。詳細は上記URLをご参照ください。
]]>
Firebird/InterBase
2021-12-20T15:25:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877660
カルロスのFirebird 3.0移行本インデックス
本エントリはFirebird Advent Calendar 2021の19日目のエントリです。
木村はpdfで入手しましたが、129ページと、Firebird4移行本よりはコンパクトです。
インデックス
基本的だが必要不可欠な概念(Basic but essential concepts!) p.17
Firebird 3のインストール...
Firebird Advent Calendar 2021の19日目のエントリです。
木村はpdfで入手しましたが、129ページと、Firebird4移行本よりはコンパクトです。
インデックス
基本的だが必要不可欠な概念(Basic but essential concepts!) p.17
Firebird 3のインストール(Installing Firebird 3) p.27
既存のデータベースのFirebird 3 への移行(Migrating Existing Databases to Firebird 3) p.49
Firebird3でのユーザ(Users In Firebird 3) p.62
データの保護(Protecting your data) p.79
ワイヤプロトコルの拡張(Wire Protocol Enhancements) p.85
接続文字列(Connection Strings) p.95
Firebird 3 とレガシーアプリケーション(Firebird 3 and legacy applications) p.102
]]>
Firebird/InterBase
2021-12-19T15:11:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877659
カルロスのFirebird 3.0移行本の紹介
本エントリはFirebird Advent Calendar 2021の18日目のエントリです。
Migration Guide to Firebird 4を紹介したときに、カルロスから
「まだMigration Guide to Firebird 3もあるよ」といわれて
本エントリを書こうとおもったらすでに五年前に書いていた罠。。。。
...
Firebird Advent Calendar 2021の18日目のエントリです。
Migration Guide to Firebird 4を紹介したときに、カルロスから
「まだMigration Guide to Firebird 3もあるよ」といわれて
本エントリを書こうとおもったらすでに五年前に書いていた罠。。。。
Firebird 3.0の書籍(Migration Guide to Firebird 3)
そのブログから参照されているリンクで電子版(pdf)を
クレジットカードで買えます。申し込んで2営業日くらいできます。
また、アマゾンジャパンからペーパーバックの形式でも買えます 。
]]>
Firebird/InterBase
2021-12-18T20:54:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877658
InterBase Developer Editionのダウンロード
本エントリはFirebird Advent Calendar 2021の17日目のエントリです。
InterBaseのほうも、Firebirdとはソースコードが分離(かつ、再度クローズドになり)され、独自の進化を遂げています。
有料ですが、現在は無償の開発者版(Developer Edition)も提供されています...
Firebird Advent Calendar 2021の17日目のエントリです。
InterBaseのほうも、Firebirdとはソースコードが分離(かつ、再度クローズドになり)され、独自の進化を遂げています。
有料ですが、現在は無償の開発者版(Developer Edition)も提供されています。
InterBase Developer Editionのダウンロード
ダウンロードはLinuxとWindows版、ライセンスキーの送付に電子メールアドレスが、
そして、サイトにアクセスするためのパスワードの登録が必要になります。
そして、なかなか渋いこの制限。。。。
o 最大20クライアント接続(80セッション同時接続)に制限されます。
o 連続稼働時間は48時間です。48時間を超えた場合、サーバーを再起動させる必要があります。
だいたいリソース制限をする無償版が多いところで、本当に不思議な制限ですね(特に連続稼働時間48時間)
だれが考えたんだろ?
]]>
Firebird/InterBase
2021-12-17T20:26:00+09:00
meijik
JUGEM
meijik
-
http://blog.kimuradb.com/?eid=877657
書籍「その問題、デジタル地図が解決します」ご紹介
この記事は『RDBMS-GIS(MySQL,PostgreSQLなど) Advent Calendar 2021』の16日目のエントリーです。
エントリしたときには、MySQLやPostgreSQL以外のRDBMSについて調べようと思ってましたが、思っているだけで当日が来てしまいました。
そのため、今回は気軽にGISの...
RDBMS-GIS(MySQL,PostgreSQLなど) Advent Calendar 2021』の16日目のエントリーです。
エントリしたときには、MySQLやPostgreSQL以外のRDBMSについて調べようと思ってましたが、思っているだけで当日が来てしまいました。
そのため、今回は気軽にGISの世界に入るのによい本(五反田のTsutayaで立ち読みして購入)をシェアしてお茶を濁します :)
その本は今年(2021年)の3月25日に発売された以下の本です。
その問題、デジタル地図が解決します― はじめてのGIS
目次
1stミッション 恋愛-デート編 デートプランを考えろ!
2ndミッション 趣味-音楽編 楽器が演奏できる場所はどこだ?
3rdミッション ビジネス-働き方編 古民家で新しい働き方を見つけよう!
4thミッション ビジネス-マーケティング編 焙煎工場を併設した高級カフェの出店計画を提案してくれ!
5thミッション ビジネス-観光編 オンリーワンの台湾旅行を企画しろ!
最終ミッション まちづくり-防災編 想定を超えた大災害に備えろ!
このような魅惑的なミッションをそれぞれ5日ずつ、計30日で具体的なイメージが身に付きます。GISは結構古い本が多かったり、とっかかりの敷居が高いものですが、気楽にはじめられる入門書としては素晴らしい出来だと思います。
現在はkindle版も販売されていますので、チェックしてみてください!
]]>
GIS
2021-12-16T23:01:00+09:00
meijik
JUGEM
meijik