SQLiteで行番号を取得したり→rowid

SQLiteで行番号を取得して、手動でidというカラムに突っ込みたくなったのでメモ。

sqlite> select rowid from table_name;

で行番号表示。

sqlite> update table_name set id = rowid;

で投入完了。

なんちゃってauto incrementのできあがり。

SQLiteのフィールド型定義順

SQLiteで引っかかったことのメモです。

■INTEGERなどの型宣言（厳密な型宣言にはなりませんが）は、フィールド名の後に。

たとえば、
CREATE TABLE tbl (id INTEGER PRIMARY KEY,field_name,edit_date);
といったような。

変数名と型を宣言するノリで、INTEGER id という順番の指定をしてしまい、フィールド名が「INTEGER」になってしまってました。。。orz

sqliteをコマンドラインで。（手軽で楽しいすね）

sqlite3をコマンドラインでいじると，とても楽しいことを知りました。

これまでコマンドラインは若干ハードルを覚えていましたが、
思いの外気軽なもので、実験するにはもってこいだな、と。

特にSQLiteが気軽に使うにはもってこいですね。

DB学習の最初には、SQLiteは楽しめていいかな、と思いました。
（型が厳密でないことも、最初はおいておいて、単に操作言語だけ覚えるにはもってこいで。insert ,select,updateなど）

自分は最近Macを使い始めたので、よりコマンドラインが身近になりました。

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

macだと、terminalを開いて、
「sqlite3」と入力するだけで起動してくれます。（「sqlite」ではなく「sqlite3」とする必要がありました。sqliteだけだとエラーが出る。2と3が同じSQLiteでも違うからですね）

単にsqlite3ではなく、
データベース名も一緒に「sqlite3 sample.db」みたいに入力すれば、
そのDBファイルがあればそのファイルが開かれ、
なければ作られてしまう（！）という手軽さ。
これがいいですね。

それで
create table hoge(a,b);
とすれば、これだけでテーブルも作れてしまう。。
型も設定せず。。。
手軽すぎますね。

このテーブルに
insert into hoge values(100,200);
なんてすれば、これまた値も追加でき、
select * from hoge;
なんてすれば
「100|200」なんて表示もしてくれます。

SQLite3は、コマンドラインで遊んで、一通り値の追加や削除、選択方法などを覚えてしまうと、
SQL言語が初めての人でも楽しく学べそうです。

ありがたや。。

これがパブリックドメインで無料でどうとでも使え、
かつ、ファイルをコピーすればバックアップもとれ、
いろいろな用途やプラットフォームで使えるのだから、ありがたいものです。

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

以下、ご参考までですが、
自分が一番SQLite関連で気に入った本です。

「持ち運びやすい。」
「網羅的である。」
「基礎からわかるよう，平易に書かれている。」
そんな点で気に入りました。
電車でも気軽に読めます。

SQLite関数、使おうかな、と。

これまで、PDO関数を使ってSQLiteのコードを書いてきましたが、
普通のPHPにおけるSQLite関数を使う方が、コーディングが若干楽、と思い使うことにしました。（セキュリティ上はPDOの方が確実かな。。自分が購入したSQLite本が、PDO関数の記述だけだったので、PDOを覚えたのですが、bindとか若干面倒で。。）

で、
http://www.phpbook.jp/tutorial/sqlite/
に説明を書いてくださっていたので、メモメモ。

SQLiteのCall to a member function on a non-objectに再び悩み。。

SQLiteで

Call to a member function on a non-object

のエラーが再び出て、困った。

初心者によくあるSQL文の記述間違いが主であると複数のサイトで言われていたので、

SQL文を見返すも原因が見つからず。

やっと見つかった原因は単に、SQLiteのファイルへの接続が古いファイルに向いていたことだった。

新しいファイルの方へと接続し直したらOKに、というオチだった。。

SQLiteエラーの詳細表示

SQLiteでCall to a member function on a non-objectなどのエラーが出て、SQL文のエラー内容を表示させたいとき


$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

のように設定すればOK。

Call to a member function bindParam() on a non-object に悩まされて。

 Call to a member function bindParam() on a non-object
というエラーに悩まされました。
SQLiteで。

要するに、ちゃんと変数なりが代入されていない，sql文として正しくないちゅうことですね。

郵便番号から住所取得するために、

       $sql="select zip,addr1,addr2,addr3 from zip where zip like ':search' order by zip asc";
        $stmt=$conn->prepare($sql);
        $stmt->bindParam(":search",$search);
        $stmt->execute();

のようにしていました。

いろいろやってみて、
':search'のクオーテーションを外してやったらOKでした。

bindParamしたパラメーターは、クオーテーションで囲んじゃいけないんですね。

       $sql="select zip,addr1,addr2,addr3 from zip where zip like :search order by zip asc";
としたら無事OKに。


良かった。

郵便番号をSQLiteに格納

これまでMySQLで郵便番号データを格納し、Ajaxで読み込ませていたが、
サーバを移行して、DB数が制限されたので、SQLiteに移行することにした。

■郵便番号csvファイルを落としてきて、csvファイルのいらないフィールドををできるだけそぎ落とし、（面倒なので、エクセルで。） ■""を削除し（何も考えずテキストエディタで置換）
■utf-8にして保存。（エクセル読み込み前にやると文字化けしたので、エクセルにてフィールド削除後に）
■で、PupSQLiteというソフトを使って，先にDBおよびフィールドを作っておいてやり、簡単に無事インポートできた。
http://www5.plala.or.jp/Pup/download/help/pupsqlite/function/readcsv.html

zipコードにuniqueを振ってしまっていて，最初エラーが出たが。
郵便番号は，北海道などでけっこう同じものがあるのでユニークを振ってはいけませんね。

PupSQLite、ありがたい。
作者様に感謝しています。

SQLite2系と3系

SQLiteは2系と3系でけっこうSQL文で使えるものが違うようですね。

3系ではalter tableが出来るようになったとか。
やっと知りました。

参考になったのはこちら。
http://seclan.dll.jp/dtdiary/2007/dt20070228.htm

SQLiteをエクセル形式エクスポート

https://www.eonet.ne.jp/~pup/software.html

SQLiteをエクセル形式でエクスポート使用と思い、
「SQLite入門」に載っていたツールを思い出し、インストール。

とても簡単にエクスポートできました。
感謝。

[PupSQLite]

https://www.eonet.ne.jp/~pup/software.html

 

SQLiteではダミーのautoincrement値は要らない様子

SQLiteで
AUTOINCREMENTのidカラムには、
0などのダミーの値は必要無い様子。

0を入れると,すでに0があった場合、データがinsertできなかった。

メモです。

予約語使用、という痛い学習…

ちょっとしたDBをSQLiteで作成してみたら、
やたらと
Call to a member function bindParam() on a non-object in
というエラーに見舞われ、原因が分かりませんでした。

いろいろやってみて、「from」という名前のカラムへの登録をなくすと通ることが判明。
はっと思って「予約語表」を見てみたら、当然ですが「from」がありました。

っていうか、普通のSQL文でばりばり使われる言葉をカラム名にしちゃいけませんね。。。

痛い学習でした。2時間は悩んだ。。。修行が足りません。

「今後は気をつけましょう」と自戒を込めてココに書き残します。

SQLite,bindParamメモ

SQLiteでのPDO、bindParamの書き方が、

http://www.php.net/manual/ja/pdo.prepared-statements.php

に載っていて、とても簡単だったので、引用メモです。




<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

// 行を挿入します
$name = 'one';
$value = 1;
$stmt->execute();

// パラメータを変更し、別の行を挿入します
$name = 'two';
$value = 2;
$stmt->execute();
?>

UPDATE文のメモ

UPDATEのメモです。

UPDATE table_name SET columnA=:valueA where id=:id

といった感じ。

「SET」をお忘れ無きよう！自分。

SQLiteで書き込みが機能しない時は、まずpermissionを疑うのが吉

前にもやりましたが、またやりました。。orz

SQLiteは、DBサーバが要らない分、サーバに上げた時の書き込み権限に注意する必要がありますね。
それをまた忘れて、書き込み権限を付与せず、
「insert intoが機能しない！」
としばし困り果てていました。。。

で、諦めて休憩したらふと、「あ、書き込み権限の問題かも」と思いつき、
権限を付与したら、あっさりOKに。。

SQLiteで書き込みが機能しない時は、まずpermissionを疑うのが吉ですね。
コードに問題が無く、エラーも出ないのにDBが空、なんて時は特に。

 自分に言い聞かせるためにも、またここに書いておきます。
きっと同じことで引っかかる方もおられるかな、とも思いつつ。

SQLiteでは、カラムを削除するコマンドが無い様子。

SQLiteでカラムを削除しようと思って調べたら、alter table などではできないらしい。

http://www.sqlite.org/faq.html

どうしても無くしたければ、新しいテーブルを作って、そちらにトランザクションなりで必要な部分だけをエクスポートして下さいな、ということですね。
そうなんですね。

以下、本家FAQからの引用。

型指定がないので、移行はかなり簡単そうではありますね。。

(11) How do I add or delete columns from an existing table in SQLite.

    SQLite has limited ALTER TABLE support that you can use to add a column to the end of a table or to change the name of a table. If you want to make more complex changes in the structure of a table, you will have to recreate the table. You can save existing data to a temporary table, drop the old table, create the new table, then copy the data back in from the temporary table.

    For example, suppose you have a table named "t1" with columns names "a", "b", and "c" and that you want to delete column "c" from this table. The following steps illustrate how this could be done:

        BEGIN TRANSACTION;
        CREATE TEMPORARY TABLE t1_backup(a,b);
        INSERT INTO t1_backup SELECT a,b FROM t1;
        DROP TABLE t1;
        CREATE TABLE t1(a,b);
        INSERT INTO t1 SELECT a,b FROM t1_backup;
        DROP TABLE t1_backup;
        COMMIT;

SQLiteで書き込みできず困った。。（ディレクトリの書き込み権限付与で解決）

SQLiteで書き込みができずに困った。

原因を検索したら、
http://blog.zuzara.com/2008/02/15/248/
ファイルのあるディレクトリの書き込み権限が必要、ということを読み、
実行したら、あっさり解決。。

ファイルの書き込み権限は変更していたが、
ディレクトリの方は気づかなかった。

解決して良かったが、なんとも悩みました。

SQLiteで携帯対応

SQLiteは基本UTF-8なので、
携帯表示の文字化け対応にちょっと苦労しました。

php.ini
で以下のようにしたらいけました。

output_buffering = On

output_handler = mb_output_handler

default_charset = "Shift_JIS"

mbstring.language = Japanese
mbstring.internal_encoding = UTF-8
mbstring.http_input = auto
mbstring.http_output = SJIS
mbstring.detect_order =  SJIS,EUC-JP,JIS,UTF-8,ASCII
mbstring.substitute_character = "none"
mbstring.encoding_translation = On

phpファイルはすべてUTF-8で保存し、
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
のままでいけました。
要するに、全てをUTF-8に統一して、php.iniでブラウザへの出力をSJISにする、
ということでいけるんですね。

（SHOEISHA刊「PHP逆引きレシピ」に書いてあるとおりにやったらいけました。
この本は、本当にありがたい本です。めちゃくちゃお世話になってます。）

SQLite(PDO)でトランザクション、初成功

sqliteのPDOで、トランザクションを使わずに複数のSQL文を実行したら，やたら遅いので、トランザクションを勉強し、うまくいったのでメモです。

$stmt=$conn->beginTransaction();
for($i=0;$i<12;$i++){
$stmt=$conn->prepare("insert into tests (test,test_jp)
values(:test,:test_jp)");
$stmt->bindParam(":test",$tests[$i]);
$stmt->bindParam(":test_jp",$tests_jp[$i]);
$stmt->execute();
}
$stmt=$conn->commit();

bindとか、ちょっと面倒だし、
まだ使いこなせていません。。。

また、書き方が分からず、
$stmt=$conn->execute();としていたら、エラーだったので、
$stmt->execute(); というようにしたらOKに。

一方、SQL文を登録するにはprepare()内で書くと良い様子。
そっちは、
$stmt=$conn->prepare("insert into 。。。");みたいに$conn->が必要のようですね。

 まだよく分からない。。

が、とりあえず動いてくれて良かった！

■追記：西沢直木氏の「SQLite入門　第2版」「PHP辞典　第2版」の両方を見て、やっと少し分かってきました。PDOに関する記述は少なめで，まだよく分かりませんが、汎用性を獲得できそうなので、是非習得したいところです。