素人によるSQLパズルゲーム
ここにあるのは素人芸なのできちんと調べるなら
「図でイメージするOracle DatabaseのSQL全集」
http://www.oracle.com/technetwork/jp/articles/otnj-sql-image1-308625-ja.html
をオススメ。
以下は、このORACLEのページを元ネタにして雑記帖のカテゴリをいじくった時のメモ。
（具体的にデータベースの構造を晒すのはセキュリティ的に問題があるような気がするので端折った）
「カテゴリを元に雑記帖の記事をリストアップしたい」
・雑記単位で一意のIDがある
・雑記に紐付くカテゴリがゼロ以上複数個ある
| 雑記ID | 本文など雑記内容詳細 |
という雑記帖詳細テーブル
| カテゴリID | カテゴリ内容詳細 |
というカテゴリ詳細テーブル
このふたつを繋ぐために
| 雑記ID | カテゴリID |
という雑記とカテゴリを紐付けるテーブルがある。各々のIDから詳細テーブルがわかるので内容を引っ張りだせる。
「電子書籍」というカテゴリがついた雑記を引っ張りだすには、雑記IDで雑記カテゴリを繋ぐ。inner join、内部結合というやつ。
でシンプルにあっさり。
複数のカテゴリから雑記を引っ張りだすのが少し面倒くさい。
その1 和集合
A「電子書籍」か、B「kindle」のどちらかのカテゴリのついた雑記を引っ張りだすには
これでカテゴリに「電子書籍」か「kindle」がついた雑記を引っ張りだせるけど、両方ともついた雑記が2回出てくる。
| 雑記1 | 電子書籍 |
| 雑記2 | kindle |
| 雑記3 | 電子書籍 |
| 雑記3 | kindle |
雑記3のケース。
IDが重複していると、同じ雑記を表示することになってしまうので、重複IDをひとつにするために distinct を使う。
その2 積集合
A「電子書籍」とB「kindle」、両方のカテゴリがついた雑記を引っ張りだすには intersect を使ってふたつの検索結果であるテーブルの積集合をとることになるらしい。
1行目のselect文で電子書籍IDのついた雑記を引っ張りだし、3行目のselect文でkindleIDのついた雑記を引っ張りだす。
intersect が、ふたつから共通する雑記IDだけを引っ張りだしている。
この引っ張りだした結果を select で括って条件でフィルタしたり順番に並べたりする。
積集合じゃないけど、両方のカテゴリがついた雑記を引っ張りだすもうひとつのやり方。
ひとつの雑記に「電子書籍」「電子書籍」などとカテゴリは重複しない。ということを前提にすると。
というのもありかも知れない。
・カテゴリに「電子書籍」か「kindle」がついた雑記を引っ張りだす
・雑記IDごとにグループ化＝集計する
・集計結果が「2」であればカテゴリがふたつともある
最初はこっちで実装してみた。
intersectを使ってtableをふたつ結合するのは処理が重い＝遅いんじゃないかと思ったんだけど、計測してみたら全然差がなかった…たかだか（現状）2700程度の数だし。
それなら、カテゴリの重複はない、などと前提条件がつくよりは素直にテーブルを結合させた方が安心安全、かな。
http://t2aki.doncha.net/index.pl?cat=5%2C43
↑カテゴリをコンマで繋いで複数カテゴリのリストアップ。
これは「日野裕太郎」と「doncha.net」の組み合わせ。
日野裕太郎は紙本（商業・同人）があるし、ウチ以外からの電子書籍もあるので細かいコンロールをしたかったんだよなあ。
いろんな場面でカテゴリの組合せが使えるのがスゲーありがたい（自画自賛芸）
perl にしろ SQL にしろ、ネットの情報・紙の解説本が豊富なので、わたしのような素人なら素人なりにレベルに応じて使えるので本当に助かります。
» ローカル環境で電子書籍を作る、Macアプリ・Windows版ツール 「かんたんEPUB3作成easy_epub」
» サーバー(48)
読書・蔵書記録のための本のデータ
「何を今さら」で「大昔からあちらこちらで語られてる」ネタなんだけど、本のデータについて自分備忘録。
図書分類は知らなくても、ECで5年やってたことがあるので、商品のデータ構造やその表示について、という側面から。
本が好きな人は、読書・蔵書記録をつけるのも好きであろうと思う。たとえばWEBサービスを利用したり、パソコンでソフトを使ったり、手書きでノートにつけたり。
本の最低限のデータをエクセルにすると、ありがちなのはたぶんこんな感じ。
| タイトル | 著者名 |
|---|---|
| サイボーグブルース | 平井和正 |
| 復活の日 | 小松左京 |
| 襲撃のメロディ | 山田正紀 |
| おかえりください | 日野裕太郎 |
タイトルと著者名があれば、それでOKだと思う。
でも、これはあっと言う間に破綻する。
著者が複数の場合があるし、その人数もわからない。
| タイトル | 著者名1 | 著者名2 | 著者名3 | イラスト |
|---|---|---|---|---|
| サイボーグブルース | 平井和正 | 桑田次郎 | 生頼範義 | |
| 復活の日 | 小松左京 | |||
| 襲撃のメロディ | 山田正紀 | |||
| おかえりください | 日野裕太郎 | おおぬまひろし |
著者欄をある程度人数が入るように作っておくのは無駄だしキリがない。
ここにさらにイラストレーターやカメラマンなどの欄が欲しくなるだろうし、自分なりのカテゴリ欄や感想・メモ欄も欲しくなるのが人情というものだ。そのどれもがいくつの欄が必要になるのかわからない。
1タイトルを1行に納めるのは無理だというのがわかると思う。
なもんで、じゃあ「本ってなぁに」ということから始まるんだけど、別にメタな話ではなくて、具体的な話。
とりあえず「本というのはタイトルにいろいろくっついたデータの集り」ということにしておく。
（本に限った話じゃないとか、タイトル改題とかどうすんのとかは無視）
| タイトル | サイボーグブルース |
|---|
これさえあれば、付随するデータはバラで用意しておくといろいろ捗る。
| タイトル | サイボーグブルース | いろいろ | 平井和正 | これは何？ | 著者名 |
|---|
とか
| タイトル | サイボーグブルース | いろいろ | 桑田次郎 | これは何？ | 著者名 |
|---|
とか
| タイトル | サイボーグブルース | いろいろ | 生頼範義 | これは何？ | イラスト |
|---|
とか
| タイトル | サイボーグブルース | いろいろ | ＳＦ | これは何？ | カテゴリ |
|---|
とか
| タイトル | サイボーグブルース | いろいろ | ハードボイルド | これは何？ | カテゴリ |
|---|
「サイボーグブルース」というタイトルを持つデータを集めれば「サイボーグブルース」という本ができあがる。著者の人数の制約もなく、イラストの他に「いろいろ」に出版社や定価なんてのを追加するのもスグ。
今度は逆に「いろいろ」の中から「平井和正」を集めれば、「サイボーグブルース」や「死霊狩り」「狼男だよ」などの「タイトル」をリストアップできる。
さらにそのリストアップされた「タイトル」にくっついている「いろいろ」の「カテゴリ」を集計して「平井和正」の傾向を見ることもできる。
…なんだ、リレーショナルデータベースの話かよ、というのはその通りで、本のデータをゴソゴソいじるのにリレーショナルデータベースはちょお便利。ウチのサイトのデータベースはまだあれこれあるんだけど、基本はこの程度。
ただ、気楽に使えるリレーショナルデータベースというのが検索しても見当らなかった。
わたし自身はWEBで定番のデータベースを使ってるけどちょっとハードルが高いかなぁ。パソコンで使えるものとなると、ACCESSやファイルメーカーらしいんだけどお値段がちっとアレ。
エクセルで表にする時、上記したような形式ではなくてこんな感じにするといいかもしれない。
| タイトル | いろいろ | これは何？ |
|---|---|---|
| サイボーグブルース | 平井和正 | 著者 |
| サイボーグブルース | 桑田次郎 | 著者 |
| サイボーグブルース | 生頼範義 | イラスト |
ぱっと見、わかりにくいけど「データ」→「フィルタ」をいじるとデータをいろいろな切り口で眺められるので面白いですよ。
…て、SQLの和集合、積集合や内部結合についてメモしようと思ってたのに、なんでこんなこと書いてんだか。SQLパズルについてはまたそのうちメモしよう。
» ローカル環境で電子書籍を作る、Macアプリ・Windows版ツール 「かんたんEPUB3作成easy_epub」
» サーバー(48)
web小ネタ：コピー禁止など
kindleやibooksの新刊・既刊情報を掲載、表紙画像をそれなりの大きさで表示しようと思った時に、まんまimgタグで表示させるのはちょっと無造作すぎるなあ、と。自分で描いたイラストならともかく、お願いしたりいただいたりしたイラストだ。
そこで「画像コピー禁止」を検索して見つけた下記の方法で細工。
・画像の上で右クリック禁止伝統技
「onContextMenu="return false"」
・画像を簡単にドラッグ＆ドロップできないように。
表示する画像はcssで背景に指定して、表側のimgタグには透過gifを表示。
この方法を見つけたときは、よく思いつくなあ、なるほどなぁと呆れつつ感心。webなんて表示しているものはどうやったって取得されちゃうんでこの方法もその場しのぎ（例えばhttpでソースを取得しちゃえば画像ファイルのありかはわかる）
また、コピーされたくないテキストは以前に書いた 「個人情報をちょっと防衛」 のように。
・コンテキストメニューで右クリックは禁止
・ajaxを使って呼び出されるスクリプトでリファラなどをチェックして表示するorしないを制御
・表示してもjQueryで 「.bind("copy", function () { return false})」コピー禁止
にしておく。
マウスの右クリックもできず、キーボードショートカットでのコピーもできない状態になる。
右クリックできるように、とJavascriptを切ると当然ながらAjaxが効かないので、表示すらしない。
WEBは性善説を根拠にフリーであるべき（お金・対価がタダという意味ではない）と思うんだけど、残念ながらなかなかそうもいかないようで、ちょっとだけ。
ついでに。
ここんとこ雑記帖が重い＝表示が遅いのでイラっとしていた。ここで何度も言うけど「WEBは早さが唯一の絶対正義」そんな状態なのに、各記事にカテゴリをつけるため、データベースにカテゴリ用テーブル、カテゴリと記事のリレーション用テーブルを追加。そりゃまた当然遅くなる。
SQLを改めて見てみたらば、indexをつけてたと思ってたテーブルの（いろんなSQL文の where句で最もよく使う）カラムにindexがついてないのに気づいて慌ててつけた。
結果、それまで体感で表示まで５〜７秒かかっていたのが３〜５秒に短縮された。
それでも遅いんだけど。これ以上の短縮はキャッシュで中間コードを保存するとか、静的ページを生成するとかになる。スクリプトの書き換えや運用も含めて考えなきゃいけないかな。
もしくはajaxのコンテナだらけにする…って、ajaxにすると検索エンジンが拾えないので却下だ。
サイトの細工やチューニングは、考え出すと・やりだすと面白いもんです。
[03/20 18:27:21] 追記。ということで、一部のパーツ単位で生成しておいて、DBからデータを取るのではなく、静的パーツを呼び出すだけにした。ほんの少し早くなった、ような気がする。
[2013/12/30 09:54:31] 追記。
直リンク禁止の定番も。
.htaccessで制御する（Apache）
.htaccess を置いたディレクトリ以下に適用される
・リファラをチェックして、自サイトからのアクセスなら 「is_ok」を設定。
・この指定の優先順位は、１）拒否 ２）許可。
・すべてのアクセスを拒否する
・is_ok が環境変数に設定されていれば許可する
[2014/01/13 12:38:03]追記。
.htaccess でキャッシュコントロール
・cssやJavascript、画像などは1週間キャッシュする
・perlやphpはキャッシュしない
» ローカル環境で電子書籍を作る、Macアプリ・Windows版ツール 「かんたんEPUB3作成easy_epub」
» web(157) » javascript(36) » html css(39) » サーバー(48)
創作文芸見本誌会場HappyReading
必要だろうと思った機能を最低限つけて、バグをひとつずつ潰して、まず最初の完成形となった。
とりあえずのメモ。
データベースは、mysqlではなくてsqlite。
どうして？lolipopのmysqlは重いので、ページ表示がもっさりしてしまう。レスポンスが早い、というのがwebの絶対正義。
sqliteは複数の同時アクセスに弱いという評判だけど？確かにその通りらしいけど、ウチぐらいのアクセスなら十分。むしろファイルひとつがそのままDBなので、バックアップなどのメンテがらくちん。そっちのメリットのほうが大きい。
mysqlとかpostgresqlのようなRDBが必要になったら、そのときそのままSQLなどは移せる。
データ構成は。
基本的に、一意に決まる本の情報以外の付属情報はすべてタグとして処理。著者やカテゴリなど。でも、イベント情報関係は別テーブルで管理（同人誌の場合、イベント参加情報は重要なので）
詳細ページを閲覧された回数や立ち読みされた回数はまた別管理で。モチベーションのため。
ページ表示に使うためのマスターテーブルは特になくて、idではなくて、ユーザー入力によるテキストのタイトル部分をキーにしている、というのが後々禍根を残しそうな予感。でもたぶん、そのおかげでおそらく内部SEO対策となる、はず。urlとページタイトルとコンテンツのh1の関連が強くなるし、そこで使われるキーワードがアンカーテキストとしてページ上部に出現する、いわゆるSEOとしては理想的な構造。
サイトの目的とか。
立ち読みをしてもらう。
面白かったら作者やサークル、カテゴリで芋づる式に次の立ち読みをしてもらう。
動線はそのために作る。それによって結果として内部SEO対策となる。
表示側。
一覧ページと詳細ページだけ。あとは、利用規約など。
サイトのトップページは必要ない？意図を持ってなにかをおすすめするとか企画するようなサイトではない。なので、単純にひらたく、なんらかの一覧ページをトップにすればいい。
ページの目的は立ち読みのテキストを読んでもらうことなので、一覧も詳細もワンクリックで立ち読みが開くように。
サークル名や著者名など本に付属するデータはすべてリンクとする。面白かったら芋づる式に次を読んでもらいたいから。
登録側。
テーブル構造のまんま。必須項目などはjavascriptでUIを作る。必須入力項目が多いので、少しでも便利にしておきたいところだけど、まだまだ検討材料が多い。
同人誌に限った話ではなくて。デザフェスなんかを見て感じることで。
世間とか時代とかに、もし閉塞感があるとすれば、これからは組織に頼らない個人こそが勝負できるんじゃないかと思う。
» ローカル環境で電子書籍を作る、Macアプリ・Windows版ツール 「かんたんEPUB3作成easy_epub」
本棚のバックアップと一括登録
時間なら、いま、売るほどあるので、WEB本棚「趣味は読書２」の積み残した機能をコーディング、実装。
その１。
バックアップは必須。今まで「趣味は読書。」では、csvでバックアップできるようにしていた。今回からはxml出力にした。まだ、（くそ）エクセルなんかでもxmlはオプションっぽいし、XMLは一般的じゃないとは思いつつ。
タイトルなどはともかく、著者の数やメモの数がいくつになるのか決まっていない。csvのように一行でリニアに記述するには無理がある。ほかにテキトーな形式も思いつかず、たぶん、XMLで書いておけば、きっと便利で簡単なコンバーターがあるに違いない、と。
自宅・ウチのパソコンを公開してサービスに提供してた時は、バックアップのことが気になってしかたなかったけど、今度の「趣味は読書２」は http://doncha.net/ で、 lolipop だ。利用者も多いし、WEBの出納係と異名を持つ GMO傘下だ。会社が倒産したり、サーバが飛んでデータが消える、なんて滅多なことはないだろう。それなりに安心してもいいハズ。てことでバックアップは必須とはいえ、優先順位は後回しになっていた機能だ。
その２。
本の一括登録はあったほうが良さそう。今までの「趣味は読書。」でもつけてた機能なので。初めて登録してくれたユーザーさんとか、一冊ずつポチポチやって登録するのは面倒だろうし、てことでつけたんだけど、実際は初めてのユーザーさんだけじゃなくて、既存のユーザーさんでアクセス頻度の低いひとが、まとめて登録するのに使われていた。
ほんとは、一冊ずつ登録して、一冊ずつにメモやカテゴリをつけてあげてほしいんだけどね。
機能追加とか、SQLiteは気楽。ユーザー単位でDBを使う＝ユーザーひとりにひとつのファイル、という構成にしたので、なにか意図しないことが起こっても、データベースへの被害は小さい。ユーザー間のリレーション機能を削った分、意図せず、災害に強い構成になった、ような気がする。
ちなみに、現状。
300人いた会員のうち、乗り換えてくれたのが、17人ほど。…って、もともとアクティブなひとたちで、本棚も充実していて楽しい。
» ローカル環境で電子書籍を作る、Macアプリ・Windows版ツール 「かんたんEPUB3作成easy_epub」
快晴の青空
青空の下、朝から団地の掃除。にしても、毎度のことながら、口うるさく血気盛んなおじいちゃんおばあちゃんたちには、驚くというか呆れるというか。気がついたらいつも罵りあいが始まっている。たとえば同じ10年、怒って過ごすより、笑って過ごしたもの勝ちだと思うんだけどねえ。
その後、昨日公開をアナウンスしたWEB本棚サービス「趣味は読書２」の手入れ。データ移動を希望してくれた「趣味は読書。」からのユーザーさんのデータ移動＆コンバート作業。…していて気づいた。7000件ほど本の登録のあるユーザーさんの本棚で、著者検索とかメモ検索が、行ったきり返ってこないんじゃないかと思うほど重い。
本のテーブルに、著者テーブルまたはメモテーブルを inner join して distinct してる。
SQLiteのjoinは重いという記事をみた記憶もあるんで、どうしたもんかなぁ、と。
たぶん、SQLでinner join して、データを絞り込んで、かつ、まとめて一度に取得するのが、スクリプト的には美しい、はず。とはいえ、遅いんじゃ話にならない。WEBは早さが絶対正義。とりあえず、inner join をやめて、テーブル一つずつ順番に問い合わせて各々の結果をスクリプトで次の問い合わせに使って、といういかにもアレな、SQLとスクリプトに変更したら、ちょっ速になった。
デザイン的にはIEは残念だけど、ほぼ意図どおり。世間はソーシャルだなんだとオープンなのがいいこと、らしいけど、ipod touchの没入感は「個人のもの」なデザインがあってこそだろう、と、機能的にはまず不要な時計なんかもつけてみた。「自分んち」とか「自分の机」とかのイメージというか。
http://doncha.net/about.pl
ちなみに、スクリプトができたぞ、と思ってから今日まで、３５ケのバグを潰した。
ｊQueryがあまりに便利なもので、フローを深く考えずに作ったところ、いざ使いだしたら、こっちの表示とあっちの表示が違っちゃうじゃん、とか、コンテナの高さが取れてないじゃない、とかポロポロでてきて、結局ページ読み込みしなおしに変更したり。これがバグをさらに埋め込んでしまう負のスパイラル。
んなこんなで夜3時。昔、Wizardryとか大戦略とかソウルハッカーとかウルティマオンラインとか、新聞配達のバイクの音で、「ヤベヤベ、もう寝なきゃ」とやってた頃と同じ。
ゲームがスクリプトに変わっただけ。どちらも目先の達成感があるんだよあな。
さすがに当時の体力はもうないけど。
» ローカル環境で電子書籍を作る、Macアプリ・Windows版ツール 「かんたんEPUB3作成easy_epub」
