目次
サンプル・プログラムの実行例
サンプル・プログラム
| reportCard2.php | サンプル・プログラム本体。 |
| reportCard.xml | サンプル・プログラム用のDBスキーマ。 |
| Report_TableScore.csv | 成績表 |
| Report_MasterStudent | 児童マスタ |
| Report_MasterSubject | 教科マスタ |
| pahooSQLite.php | SQLite操作クラス pahooSQLite。 使い方は「PHP＋SQLite：DBスキーマ」などを参照。include_path が通ったディレクトリに配置すること。 |
| pahooSQLite.xsd | DBスキーマを定義するXMLスキーマ。 使い方は「PHP＋SQLite：DBスキーマ」などを参照。include_path が通ったディレクトリに配置すること。 |
| バージョン | 更新日 | 内容 |
|---|---|---|
| 1.0.0 | 2022/12/24 | 初版 |
| バージョン | 更新日 | 内容 |
|---|---|---|
| 1.0.0 | 2022/12/24 | 初版 |
DBスキーマ
ここでは、このXMLファイルのことを DBスキーマと呼ぶことにする。1つのデータベースについて、1つのDBスキーマが対応する。
columnの中で、データ型や最小値・最大値、登録可能な文字パターンなどを定義しており、これを参照してCREATE文を使ってテーブルを生成するとともに、データ登録する際のバリデーションチェックにも利用する。
XMLスキーマ
270: /**
271: * DBスキーマを読み込む
272: * @param string $fname DBスキーマ・ファイル名
273: * @return bool TRUE：読み込み成功／FALSE；失敗
274: */
275: function readDBSchema($fname) {
276: $xsd = self::XMLSCHEMA;
277: $xml = FALSE;
278:
279: //DBスキーマの存在チェック
280: if (! file_exists($fname)) {
281: $this->seterror('DBスキーマがありません', $fname);
282: return FALSE;
283: }
284: //XMLスキーマの存在チェック
285: if (! ($xsd2 = stream_resolve_include_path($xsd))) {
286: $this->seterror('XMLスキーマがありません', $xsd);
287: return FALSE;
288: }
289: //DBスキーマ検査
290: $dom = new DOMDocument();
291: $dom->load($fname);
292: if (! $dom->schemaValidate($xsd2)) {
293: $errmsg = 'DBスキーマのエラー：';
294: $errmsg .= FLAG_RELEASE ? '' : libxml_display_error();
295: $this->seterror($errmsg);
296: $res = FALSE;
297: //XMLファイル読み込み
298: } else {
299: $this->clearerror();
300: $errmsg = '';
301: $xml = @simplexml_import_dom($dom);
302: $res = $this->readSchema($xml, $errmsg);
303: }
304:
305: return $res;
306: }
本来は値の最小値・最大値、文字パターンなどもチェックできるのだが、あいにくPHP 8.2までの DOMDocument::schemaValidate はXML Schema 1.0のみのサポートのため、値のチェックは今回つくったメソッド pahooSQLite:readDBSchema に組み込むことにした。
クラス pahooSQLite
クラス内プロパティとコンストラクタのコードを以下に示す。コンストラクタは次の処理を行う。
- SQLiteが利用できるかどうか検査する
- DB接続する
- DBスキーマを配列へ読み込む
- CREATE文を生成し実行する（CSVファイルがあればINSERTする）
- エラー処理
59: class pahooSQLite {
60: const XMLSCHEMA = 'pahooSQLite.xsd'; //DBスキーマのXMLスキーマ
61:
62: var $dbfile; //DBファイル名
63: var $pdo; //PDOオブジェクト
64: var $xmlfile; //テーブル情報を格納したXMLファイル名
65: var $schema; //DBスキーマの内容（配列）
66: var $types; //データ型対応表（DBデータ型=>PDOデータ型）
67:
68: var $errmsg; //エラーメッセージ
69:
70: /**
71: * コンストラクタ
72: * @param string $dbfile DBファイル名
73: * @param string $xmlfile DBスキーマを格納したXMLファイル名
74: * @return bool TRUE：成功／FALSE：失敗
75: */
76: function __construct($dbfile, $xmlfile) {
77: $this->dbfile = $dbfile;
78: $this->errmsg[0] = '';
79: $this->errmsg[1] = '';
80:
81: //データ型対応表（DBデータ型=>PDOデータ型）
82: $this->types = array(
83: 'NULL' => PDO::PARAM_NULL,
84: 'INTEGER' => PDO::PARAM_INT,
85: 'REAL' => PDO::PARAM_STR,
86: 'TEXT' => PDO::PARAM_STR,
87: 'BLOB' => PDO::PARAM_LOB,
88: );
89:
90: //SQLiteが利用できるかどうか検査する
91: if ($this->isSQLiteReady() == FALSE) {
92: $this->seterror('このPHPはSQLiteをサポートしていません');
93: return FALSE;
94: }
95:
96: //DB接続する
97: try {
98: $this->pdo = new PDO('sqlite:' . $this->dbfile);
99: $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
100: } catch (PDOException $e) {
101: $this->seterror($e->getMessage());
102: return FALSE;
103: }
104:
105: //DBスキーマを読み込む
106: $this->schema = array();
107: $this->readDBSchema($xmlfile);
108:
109: //CREATE文の生成と実行
110: foreach ($this->schema as $name=>$table) {
111: $this->createTable($name);
112: }
113:
114: //エラー発生
115: if ($this->geterror() != FALSE) {
116: $this->pdo = NULL;
117: unlink($this->dbfile); //DBファイル削除
118: $res = FALSE;
119: } else {
120: $res = TRUE;
121: }
122:
123: return $res;
124: }
php_pahooSQLite::isSQLiteReady
176: /**
177: * SQLiteが利用できるかどうか検査する
178: * @return bool TRUE：利用可能／FALSE:不能
179: */
180: function isSQLiteReady() {
181: $res = FALSE;
182: $this->seterror('SQLiteを利用できません');
183: if (isphp5over()) {
184: if (class_exists('SQLite3')) {
185: $this->clearerror();
186: $res = TRUE;
187: }
188: }
189: return $res;
190: }
php_pahooSQLite::readDBSchema
270: /**
271: * DBスキーマを読み込む
272: * @param string $fname DBスキーマ・ファイル名
273: * @return bool TRUE：読み込み成功／FALSE；失敗
274: */
275: function readDBSchema($fname) {
276: $xsd = self::XMLSCHEMA;
277: $xml = FALSE;
278:
279: //DBスキーマの存在チェック
280: if (! file_exists($fname)) {
281: $this->seterror('DBスキーマがありません', $fname);
282: return FALSE;
283: }
284: //XMLスキーマの存在チェック
285: if (! ($xsd2 = stream_resolve_include_path($xsd))) {
286: $this->seterror('XMLスキーマがありません', $xsd);
287: return FALSE;
288: }
289: //DBスキーマ検査
290: $dom = new DOMDocument();
291: $dom->load($fname);
292: if (! $dom->schemaValidate($xsd2)) {
293: $errmsg = 'DBスキーマのエラー：';
294: $errmsg .= FLAG_RELEASE ? '' : libxml_display_error();
295: $this->seterror($errmsg);
296: $res = FALSE;
297: //XMLファイル読み込み
298: } else {
299: $this->clearerror();
300: $errmsg = '';
301: $xml = @simplexml_import_dom($dom);
302: $res = $this->readSchema($xml, $errmsg);
303: }
304:
305: return $res;
306: }
192: /**
193: * DBスキーマを配列へ格納する
194: * @param object $xml DBスキーマ(SimpleXMLオブジェクト)
195: * @param string $errmsg エラーメッセージ格納用
196: * @return bool TRUE：正常／FALSE：以上
197: */
198: function readSchema($xml, &$errmsg) {
199: $pat_name = '/^[a-zA-Z\_]+[a-zA-Z0-9\_]*$/i';
200: $keys = array('', 'PRIMARY KEY');
201: $types = array('NULL', 'INTEGER', 'REAL', 'TEXT', 'BLOB');
202: $res = TRUE;
203: $rules = &$this->schema;
204:
205: //テーブル
206: foreach ($xml->table as $table) {
207: $table_name = (string)$table->name;
208: //テーブル名
209: if (preg_match($pat_name, $table_name) == 0) {
210: $res = FALSE;
211: $errmsg = $table_name . ' は不正なテーブル名です';
212: return $res;
213: }
214: //コメント
215: $rules[$table_name]['comment'] = (string)$table->comment;
216: //テーブル生成時に読み込むCSVファイル名
217: $rules[$table_name]['csv'] = (string)$table->csv;
218: //カラム
219: foreach ($table->column as $column) {
220: $column_name = (string)$column->name;
221: //カラム名
222: if (preg_match($pat_name, $column_name) == 0) {
223: $res = FALSE;
224: $errmsg = $table_name . ' は不正なカラム名です';
225: return $res;
226: }
227: //コメント
228: $rules[$table_name]['comment'] = $column_name;
229: //キー
230: if (isset($column->key) && !in_array((string)$column->key, $keys)) {
231: $res = FALSE;
232: $errmsg = $column_name . ' に不正なキーが含まれています';
233: return $res;
234: } else {
235: $rules[$table_name]['column'][$column_name]['key'] = (string)$column->key;
236: }
237: //データ型
238: if (! array_key_exists((string)$column->type, $this->types)) {
239: $res = FALSE;
240: $errmsg = $column_name . ' に不正なデータ型が含まれています';
241: return $res;
242: } else {
243: $rules[$table_name]['column'][$column_name]['type'] = (string)$column->type;
244: }
245: //最小値
246: if (isset($column->min)) {
247: $rules[$table_name]['column'][$column_name]['min'] = (float)$column->min;
248: }
249: //最大値
250: if (isset($column->max)) {
251: $rules[$table_name]['column'][$column_name]['max'] = (float)$column->max;
252: }
253: //文字列の最大長
254: if (isset($column->length)) {
255: $rules[$table_name]['column'][$column_name]['maxlength'] = (int)$column->maxlength;
256: }
257: //許容する文字列パターン
258: if (isset($column->pattern)) {
259: $rules[$table_name]['column'][$column_name]['pattern'] = (string)$column->pattern;
260: }
261: //許容しない文字列パターン
262: if (isset($column->pattern)) {
263: $rules[$table_name]['column'][$column_name]['exclusion'] = (string)$column->exclusion;
264: }
265: }
266: }
267: return $res;
268: }
php_pahooSQLite::createTable
433: /**
434: * テーブルが無ければ生成し，CSVファイルを読み込む
435: * @param string $name テーブル名
436: * @return bool TRUE：成功／FALSE：失敗
437: */
438: function createTable($name) {
439: $res = FALSE;
440:
441: //DBスキーマの存否チェック
442: if (! array_key_exists($name, $this->schema)) {
443: $this->seterror('未定義のテーブルです', $name);
444: return FALSE;
445: }
446:
447: //テーブルの存否チェック
448: if ($this->isTable($name) == TRUE) return TRUE;
449:
450: //CREATE文の生成
451: $cnt = 0;
452: $sql = 'CREATE TABLE ' . $name . ' (';
453: foreach ($this->schema[$name]['column'] as $key=>$column) {
454: if ($cnt > 0) {
455: $sql .= ', ';
456: }
457: $sql .= $key . ' ' . $column['type'];
458: if (isset($column['key'])) {
459: $sql .= ' ' . $column['key'];
460: }
461: $cnt++;
462: }
463: $sql .= ');';
464:
465: //CREATE文の実行
466: try {
467: $stmt = $this->pdo->prepare($sql);
468: $stmt->execute();
469: $res = TRUE;
470: } catch (PDOException $e) {
471: $res = FALSE;
472: }
473: if ($res == FALSE) {
474: $this->seterror('テーブルをCREATEできませんでした', $name);
475: }
476:
477: //SQL文の生成
478: $sql = $this->makeSQLmerge($name, $this->schema[$name]['column']);
479: if ($sql == FALSE) return FALSE;
480:
481: //CSVファイルを読み込む
482: if (isset($this->schema[$name]['csv'])) {
483: $fname = (string)$this->schema[$name]['csv'];
484: if (! file_exists($fname)) {
485: $this->seterror('最初に読み込むCSVファイルが見つかりません', $fname);
486: return $res;
487: }
488: //CSVファイルを1行ずつ読み込み
489: try {
490: $n = count($this->schema[$name]['column']);
491: $file = new SplFileObject($fname);
492: $file->setFlags(SplFileObject::READ_CSV);
493: $rows = 0;
494: foreach ($file as $line) {
495: //空行、先頭行は読み飛ばし
496: if (! isset($line[$n - 1]) || ($rows == 0)) {
497: $rows++;
498: continue;
499: }
500: //データの挿入
501: $i = 0;
502: $data = array();
503: foreach ($this->schema[$name]['column'] as $key=>$column) {
504: $data[$key] = $line[$i];
505: $i++;
506: }
507: $stmt = $this->pdo->prepare($sql);
508: $this->bindData($stmt, $name, $this->schema[$name]['column'], $data);
509: if (($errmsg = $this->geterror()) != FALSE) {
510: return FALSE;
511: }
512: $stmt->execute();
513: }
514: $res = TRUE;
515: $file = NULL;
516: } catch (Exception $e) {
517: $this->seterror('CSVファイルを読み込めません', $e->getMessage());
518: $res = FALSE;
519: }
520: }
521:
522: return $res;
523: }
php_pahooSQLite::isTable
308: /**
309: * テーブルの存在チェック
310: * @param string $table テーブル名
311: * @return bool TRUE：存在する／FALSE：しない
312: */
313: function isTable($table) {
314: $res = FALSE;
315: try {
316: $sql = 'SELECT * FROM ' . $table;
317: $query = $this->pdo->query($sql);
318: $res = TRUE;
319: } catch (PDOException $e) {
320: $res = FALSE;
321: }
322:
323: return $res;
324: }
php_pahooSQLite::makeSQLmerge
326: /**
327: * レコード更新SQL文の生成
328: * @param string $table テーブル名
329: * @param array $columns カラム情報
330: * @return string SQL文／FALSE：生成できない
331: */
332: function makeSQLmerge($table, $columns) {
333: //INSERT文の生成
334: $cnt1 = $cnt3 = $cnt4 = 0;
335: $ss1 = $ss2 = $ss3 = $ss4 = '';
336: foreach ($columns as $name=>$column) {
337: //カラム
338: if ($cnt1 > 0) {
339: $ss1 .= ', ';
340: $ss2 .= ', ';
341: }
342: $ss1 .= $name;
343: $ss2 .= ':' . $name;
344: $cnt1++;
345: //キー・カラム
346: if (isset($column['key']) && (preg_match('/KEY/i', $column['key']) > 0)) {
347: if ($cnt3 > 0) $ss3 .= ', ';
348: $ss3 .= $name;
349: $cnt3++;
350: //更新カラム
351: } else {
352: if ($cnt4 > 0) $ss4 .= ', ';
353: $ss4 .= $name . ' = ' . ':' . $name;
354: $cnt4++;
355: }
356: }
357: //キーがない場合
358: if ($ss3 == '') {
359: $sql =<<< EOT
360: INSERT INTO {$table} (
361: {$ss1}
362: ) VALUES (
363: {$ss2}
364: );
365:
366: EOT;
367: //キーがある場合
368: } else {
369: $sql =<<< EOT
370: INSERT INTO {$table} (
371: {$ss1}
372: ) VALUES (
373: {$ss2}
374: ) ON CONFLICT(
375: {$ss3}
376: ) DO UPDATE SET
377: {$ss4}
378: ;
379:
380: EOT;
381: }
382: return $sql;
383: }
ここではPDOに投入するクエリを生成するまでで、実際のデータのバインドは後述する bindData で行う。DB処理の効率をアップするためである。
php_pahooSQLite::bindData
385: /**
386: * 1レコード分のデータをバインドする
387: * @param object $stmt PDOStatementオブジェクト
388: * @param string $table 処理中のテーブル名
389: * @param array $columns カラム情報
390: * @param array $data バインドするデータ（要素名とカラム名が対応）
391: * @return TRUE：成功／FALSE：失敗（バリデーションエラーなど）
392: */
393: function bindData($stmt, $table, $columns, $data) {
394: foreach ($columns as $name=>$column) {
395: $key = ':' . $name;
396: $type = $this->types[$column['type']];
397: $val = $data[$name];
398: $label = $table . ' - ' . $name . ' - ';
399:
400: //バリデーションチェック
401: //整数または小数
402: if (($column['type'] == 'INTEGER') || ($column['type'] == 'REAL')) {
403: if (isset($column['min']) && ($val < $column['min'])) {
404: $this->seterror($label . 'が最小値より小さい', $val);
405: return FALSE;
406: }
407: if (isset($column['max']) && ($val > $column['max'])) {
408: $this->seterror($label . 'が最大値より大きい', $val);
409: return FALSE;
410: }
411: //文字列
412: } else if ($column['type'] == 'TEXT') {
413: if (isset($column['maxlen']) && (mb_strlen($val) > $column['domain']['maxlength'])) {
414: $this->seterror($label . 'は長すぎるテキストです', $val);
415: return FALSE;
416: }
417: if (isset($column['pattern']) && (preg_match($column['pattern'], $val) == 0)) {
418: $this->seterror($label . 'は受容できない文字を含んでいます', $val);
419: return FALSE;
420: }
421: if (isset($column['exclusi']) && (preg_match($column['exclusion'], $val) > 0)) {
422: $this->seterror($label . 'は受容できない文字を含んでいます', $val);
423: return FALSE;
424: }
425: }
426:
427: //データをバインドする
428: $stmt->bindValue($key, $val, $type);
429: }
430: return TRUE;
431: }
DBスキーマのカラム情報を配列 $columns として、実際にバインドするデータを配列 $data といして与える。このとき、$columns と $data がカラム名として対応していること。
このメソッド内で、$data の各データが $columns で指定された範囲内にあるかどうかバリデーションチェックを行う。こうすることで、PDOによるSQLインジェクション攻撃対策に加え、意図しないデータがデータベースに登録されることを防ぐ。
このメソッドが正常終了したら、PDOStatement::execute を使ってSQLを実行することで、データをデータベースに登録する。
php_pahooSQLite::selectDataSimple
525: /**
526: * SELECT文を実行する
527: * @param string $sql SELECTではじまるSQL文
528: * @param array $data 結果を格納する配列
529: * @return bool TRUE：成功／FALSE：失敗
530: */
531: function selectDataSimple($sql, &$data) {
532: //SQL文のチェック
533: if (preg_match('/^select/iu', $sql) == 0) {
534: $this->seterror('SELECT文ではありません', $sql);
535: return FALSE;
536: }
537:
538: //SQLの実行
539: try {
540: $stmt = $this->pdo->prepare($sql);
541: $stmt->execute();
542: $data = $stmt->fetchAll();
543: $res = TRUE;
544: } catch (PDOException $e) {
545: $this->seterror('SELECT文を実行できません', $e->getMessage());
546: $res = FALSE;
547: }
548: return $res;
549: }
解説：初期値
45: //表示幅（ピクセル）
46: define('WIDTH', 600);
47:
48: //DBスキーマ
49: define('DBSCHEMA', 'reportCard.xml');
50:
51: //SQLite DBファイル名：各自の環境に合わせて変更すること
52: define('DBFILE', 'reportCard2.sqlite3');
53:
54: //実行するSQL
55: define('SQL_SELECT_SUBJECT', 'SELECT * FROM master_subject WHERE 1');
56: define('SQL_SELECT_SCORE', 'SELECT table_score.grade, table_score.class, id_student, student, id_subject, subject, score FROM table_score INNER JOIN master_student, master_subject ON table_score.grade = master_student.grade AND table_score.class = master_student.class AND table_score.id_student = master_student.id AND table_score.id_subject = master_subject.id ORDER BY id_student, id_subject');
57:
58: //SQLite処理に関わるクラス：include_pathが通ったディレクトリに配置
59: require_once('pahooSQLite.php');
60:
61: /**
解説：成績表を作成する
134: /**
135: * 成績表を作成する
136: * @param object $pql pahooSQLiteオブジェクト
137: * @param string $errmsg エラー・メッセージ格納用
138: * @return string 表示用文字列(HTML)
139: */
140: function selectItems($pql, &$errmsg) {
141: //科目
142: $subjects = array();
143: $res = $pql->selectDataSimple(SQL_SELECT_SUBJECT, $subjects);
144: if ($res == FALSE) {
145: $errmsg = $pql->geterror(0);
146: return '';
147: }
148: $str = '';
149: foreach ($subjects as $row) {
150: $str .= '<th>' . $row['subject'] . "</th>\n";
151: }
152:
153: //テスト結果
154: $html =<<< EOT
155: <table class="stripe_table">
156: <tr class="index">
157: <th>出席番号</th>
158: <th>名前</th>
159: {$str}
160: <th>合計</th>
161: <th>平均</th>
162: </tr>
163:
164: EOT;
165: $scores = array();
166: $score = array();
167: $col = 0;
168: $res = $pql->selectDataSimple(SQL_SELECT_SCORE, $scores);
169: if ($res == FALSE) {
170: $errmsg = $pql->geterror(0);
171: return '';
172: }
173: $str = '';
174: foreach ($scores as $row) {
175: if ($col % 4 == 0) {
176: $html .=<<< EOT
177: <tr>
178: <td class="index">{$row['id_student']}</td>
179: <td class="name">{$row['student']}</td>
180:
181: EOT;
182: }
183: $html .=<<< EOT
184: <td class="index">{$row['score']}</td>
185:
186: EOT;
187: if ($row['score'] != '') {
188: $score[$row['id_student']][$col % 4] = (int)$row['score'];
189: }
190: if ($col % 4 == 3) {
191: $sum = array_sum($score[$row['id_student']]);
192: $avg = sprintf('%.1f', array_sum($score[$row['id_student']]) / count($score[$row['id_student']]));
193: $html .=<<< EOT
194: <td class="index">{$sum}</td>
195: <td class="index">{$avg}</td>
196: </tr>
197:
198: EOT;
199: }
200: $col++;
201: }
202: //科目毎の集計
203: $score2 = array();
204: foreach ($score as $id=>$arr) {
205: foreach ($arr as $key=>$val) {
206: $score2[$key][$id] = $val;
207: }
208: $score2[999][$id] = array_sum($arr);
209: }
210: //科目毎の合計
211: $html .=<<< EOT
212: <tr>
213: <td class="index"> </td>
214: <td class="name">合計</td>
215:
216: EOT;
217: foreach ($score2 as $val) {
218: $sum = array_sum($val);
219: $html .=<<< EOT
220: <td class="index">{$sum}</td>
221:
222: EOT;
223: }
224: //科目毎の平均
225: $html .=<<< EOT
226: <td class="index"> </td>
227: </tr>
228: <tr>
229: <td class="index"> </td>
230: <td class="name">平均</td>
231:
232: EOT;
233: foreach ($score2 as $val) {
234: $avg = sprintf('%.1f', array_sum($val) / count($val));
235: $html .=<<< EOT
236: <td class="index">{$avg}</td>
237:
238: EOT;
239: }
240: $avg = sprintf('%.1f', $avg / 4);
241: $html .=<<< EOT
242: <td class="index">{$avg}</td>
243: </tr>
244: </table>
245:
246: EOT;
247: return $html;
248: }
参考サイト
- SQLiteを用意する：ぱふぅ家のホームページ
- PHP＋SQLite：リレーショナルデータベースの作り方：ぱふぅ家のホームページ
これまで PDOクラスを使ってDBにアクセスしてきたが、PDOとユーザープログラムの間に、SQLiteでよく使う処理をパッケージングしたクラス pahopoSQLite をはさむことで、メンテナンス性とプログラム作成効率をアップしてみることにする。