「SQL」に関する日記

ドラゴンクエスト10(しろいコキン)

• クエスト「竜と契約せし者」をクリアした！

しろいコキンも魔法使いと魔法戦士のレベル上げて竜術士になりました！
さすがに3回目なんでムービーはスキップしたんですが、見てなくてもクイズに正解さえすればいいんですね。

アンパンマンDB(システム)

キャラ数検索、まだテストコード上でしか確認できていませんが、修正しました。
画面上で何パターンか確認してからアップしようと思います。

SQL

上記の問題の原因、SQLの条件句における型の不一致が原因でした。

((SELECT COUNT(*) FROM list_anime_chara1 WHERE list_anime_chara1.page_id = page_anime.page_id) + (SELECT COUNT(*) FROM list_anime_chara2 WHERE list_anime_chara2.page_id = page_anime.page_id) + (SELECT COUNT(*) FROM list_anime_chara3 WHERE list_anime_chara3.page_id = page_anime.page_id))=:p

こんなSQLを条件に入れていたのですが、この右辺の:pにPHPの整数を入れても、SQLiteの内部的な整数型とは一致せず、常時不一致扱いとなっていたのです。
左辺が単純な項だったら型が自動変換されて大丈夫だったんですが、サブクエリとかの複雑な式だと無理だったようです。
というわけで、以下のようなキャストを明示的に入れて、型を合わせるようにしました。

((SELECT COUNT(*) FROM list_anime_chara1 WHERE list_anime_chara1.page_id = page_anime.page_id) + (SELECT COUNT(*) FROM list_anime_chara2 WHERE list_anime_chara2.page_id = page_anime.page_id) + (SELECT COUNT(*) FROM list_anime_chara3 WHERE list_anime_chara3.page_id = page_anime.page_id))=CAST(:p AS INT)

構築中はみんな一緒くたに書いていたけど本稼働始まってるからもう分けて書くよ。

美文のキャラ倉庫

2022年5月29日 – 美文のキャラ倉庫
現在データとして残っている分は、全部記入終えました。
今後は、入れていなかったキャラクターや、記述不足の拡充がメインになります。

WordPress

ここだ。一番下のと同じくutf8mb4にしなきゃいけない。 pic.twitter.com/mtLgfLgenb

— 📛松田美文 (@matsuda323) May 29, 2022

カスタムHTMLで実体参照にしたら投稿が通った。ただ、再び編集画面に戻ってくるとまた普通の文字に戻ってしまうので、毎回実体参照に変換する必要がある。ちなみに、問題の文字は「涅槃寂静」じゃなくて「𥝱」だった。 pic.twitter.com/N1d9ZxT5g1

— 📛松田美文 (@matsuda323) May 29, 2022

日記

で、今日の日記を書くために管理画面の活動記録見ようとしたら、キャラ倉庫の活動データが出てこなくて。
原因を調査中です。

そもそも！
うちのサーバーが！！
PHPのバージョン上げると！！！
SQLite対応が省かれる！！！！
アンパンマンDBも動かない！！！！！

管理メモの高速化など。
SQLはどのように組めば速いのかというのは、実装に大きく依存する。
それがよーくわかりました。
テストサーバーと公開サーバーで2種類のSQLの速度が逆転していました。
それも大幅に。
ちなみに、速度差を調べたのは、データの存在をINNER JOINで調べるかEXISTSで調べるか、というものでした。

続き書きました。
今回は切り捨て。
四捨五入と違い、小数の使い回しではなく、ちゃんと整数用の式を一から作っています。
あとは切り上げのみ。
これは、実はアンパンマンDB3でも使っています。

group_concatをあっさり実装したので、プログラミングコーナーにあっさりと書いておきました。
昨日書いたコードに対して、バージョンチェックを入れています。

AmazonのProduct Advertising APIに登録してみたけど、いまいちよくわからんです。
XMLで何か作ってリクエストすれば何か返ってくるらしいですが、それ以前のところでわからなくなっています。

以前言っていたgroup_concatの件、あっさり実装できました。
ローカルサーバーと公開用サーバーでSQLiteのバージョンが違っていますがそのあたりも含めてさらっと書けました。
コンマ以外で区切れませんが、自分用なので気にしないことにしましょう。
参考までに、ソースです。

$step_func = create_function('&$context, $rownumber, $string', 'if (isset($string)) { if (isset($context)) $context .= \',\'; $context .= $string; } return $context;');
$finalize_func = create_function('&$context, $rownumber', 'return $context;');
$this->db->sqliteCreateAggregate('group_concat', $step_func, $finalize_func);

ちょっとSQLiteの練習もかねて参加型コンテンツのアクション通知をSQLiteのデータベースにも吐き出すようにしてみました。
まあ、そこから読み出す画面はSQLiteManagerしかないので今のところまだ実用性は0ですが。

SQLiteManagerのどこが悪いのかをいろいろ試して調べたところ、デフォルト値やカラムの値の入力フォームの挙動がおかしいという結論に至りました。
入力結果をデータベースに反映するときにはデータを自動的にクォーテーションでくくってくれるのですが、それを再編集しようとしたとき、入力フォームに入っている値がクォーテーション付きのデータだったのです。
そして、それをそのまま確定すると、クォーテーションが付いたデータに更に自動的にクォーテーションが付けられて、中にあるクォーテーションは更にクォーテーションでエスケープされて、という流れでクォーテーションの数が毎回2倍+1増えてゆくのです。
データを見るだけなら問題はないしSQL入力フォームに直接SQL文を書けば余計な文字は付かないので全く使えないというわけでもなさそうです。

SQLiteManager使ってみたのですが、いまいち使えないですね。
と、いうより、まともに使えるものなのでしょうか、これ。
テーブル構造をいじるたびにデフォルト値にクォーテーションが無尽蔵に増えてゆくのですが。
増えるのがバックスラッシュでなくクォーテーションなのでマジッククォートの影響とも考えにくいし…。

学習帳の線分のページ更新しました。
もうまとめるの諦めました。
書けば書くほどややこしくなるばかりだったので。

公開サーバー上でもちゃんとSQLiteManagerが動くか確認してみたところ、動きませんでした。
設定用データベースの書き込み権限がおかしいといわれたのでデータベース本体のファイルを見たところ、バッチリ書き込み権限がつけてありました。
どこが悪かったのかというと、実はディレクトリにも書き込み権限が必要なのでした。
おそらく、データベースと同じディレクトリに一時ファイルを作ってデータベースを更新していたのだと思われます。

SQLiteManagerをちょっくらカスタマイズしていました。
今後いっぱいデータベースを作ることになると思うので、簡単にSQLite3のデータベースを作れるように3をデフォルトにしました。
このあたりのことはブログにもうちょっと詳しく載せています。
http://d.hatena.ne.jp/mifumi323/20110404

SQLiteManagerであれこれやってました。
なんだかAUTOINCREMENTがうまくできていないみたいですが。

SQLiteの練習もかねてアンパンマンの5択クイズでも作ろうかと画策中。
内容的にはあまり難しくならない範囲で。
第1062話でロールパンナが持ってきた食べ物なんて細かいこと出題しても面白くありませんからね。

いつの間にかMySQLの容量が増えていました。
とは言っても高々5MBになっただけなのでやっぱり別のデータベースへの移行は必要そうですが。
やっぱり2倍程度じゃ時間の問題で使い切ってしまうのですよ。
しかし一時的にせよ容量超過の状態を脱したのは良いことです。

CharaBoxをSQLiteなどのデータベースで置き換えようかなーなんて考えています。
まあ、考えているだけで今のところ実行に移すめどは立っていませんが。

アンパンマンDBをSQLiteに移行するなら一緒にデータベース構造も変えてしまおうかなーなんて考えています。
登場キャラリストは現在1話に出た連中は項目ごとにまとめてTEXTカラムに入れてあるので、テキスト処理に由来する誤動作が結構多いのです。
パフォーマンス的な問題がなければ、そして何より実現可能であれば、登場キャラリスト用のテーブルを作ってそっちに登場キャラデータは移動したいのです。
登場キャラテーブルには、参照元(どの話か)、対象の項目(メインキャラ、サブキャラ等の区分)、アンカーテキスト、検索テキスト、実際のリンク先、あたりが必要そうです。

どうやらAUTO_INCREMENTはないようですがAUTOINCREMENTはあるようです。
うちのサーバーではPDOも使えるようだけどこういった細かい文法まで面倒見てくれるのかしら？

とりあえずSQLiteManagerのインストールには成功しました。
パスワードの設定もちょっと調べたらできるようになりました。
ただSQLite自体がMySQLに比べて機能が少ないというのがちょっと不安どころではありますね。
AUTO_INCREMENTに相当するものはあるのでしょうか。

アンパンマンDBのデータベース容量超過のことですが、うちのサーバーではどうやらSQLiteも使えるようです。
もし本当に使えるとするとWeb公開用の800MBが使えるので、今まで2MBしかなかったデータベース容量から考えると破格の大容量といえます。
アンパンマンDBの数千件程度のデータならSQLiteでも他の大規模向けデータベースとそん色ないらしいですしね。