| ../ |
|---|
WordPressの理解のために、wp-includes/wp-db.php の以下の部分に error_log('■') を挟み込んでみた。WordPressがどんなSQLを発行しているかが簡単に分かる。
wp-db.php
public function db_connect( $allow_bail = true ) {
...
$this->dbh = mysqli_init();
...
if ( WP_DEBUG ) {
mysqli_real_connect( $this->dbh, $host, $this->dbuser, $this->dbpassword, null, $port, $socket, $client_flags );
} else {
// phpcs:ignore WordPress.PHP.NoSilencedErrors.Discouraged
@mysqli_real_connect( $this->dbh, $host, $this->dbuser, $this->dbpassword, null, $port, $socket, $client_flags );
}
// TODO
error_log('■■ mysqli_real_connect:: ' . $this->dbhost);
// TODO
...
}
private function _do_query( $query ) {
...
if ( ! empty( $this->dbh ) && $this->use_mysqli ) {
$this->result = mysqli_query( $this->dbh, $query );
} elseif ( ! empty( $this->dbh ) ) {
$this->result = mysql_query( $query, $this->dbh );
}
// TODO
error_log('■' . $query . ' --> ' . $this->result->num_rows);
// TODO
...
}
ログイン時のSQL
ログイン時には、以下のようなSQLが発行されていた。
[11:25:14] ■■ mysqli_real_connect:: localhost
[11:25:14] ■SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes' --> 122
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'nonce_key' LIMIT 1 --> 1
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'nonce_salt' LIMIT 1 --> 1
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'can_compress_scripts' LIMIT 1 --> 1
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'widget_recent-posts' LIMIT 1 --> 0
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'widget_recent_entries' LIMIT 1 --> 0
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'widget_recent-comments' LIMIT 1 --> 0
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'widget_recent_comments' LIMIT 1 --> 0
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'medium_crop' LIMIT 1 --> 0
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'medium_large_crop' LIMIT 1 --> 0
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'large_crop' LIMIT 1 --> 0
[ログイン]を押下すると、MySQLに接続し、まずwp_optionsをチェックしている。認証には関係しないような情報も取得している。
[11:25:15] ■
SELECT t.term_id
FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
WHERE tt.taxonomy IN ('wp_theme') AND t.name IN ('twentytwentyone')
LIMIT 1
--> 0
[11:25:15] ■
SELECT wp_posts.ID
FROM wp_posts
WHERE 1=1 AND (0 = 1) AND wp_posts.post_type = 'wp_global_styles' AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 1
--> 0
用語(カテゴリやタグのこと)や投稿があるかどうかをチェックしている。ここまでは、ページ遷移ごとの定型の動作のようだ。以下からがログインの処理である。
[11:25:15] ■SELECT * FROM wp_users WHERE user_login = 'taconana' LIMIT 1 --> 1
[11:25:15] ■SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (1) ORDER BY umeta_id ASC --> 20
[11:25:15] ■SELECT umeta_id FROM wp_usermeta WHERE meta_key = 'session_tokens' AND user_id = 1 --> 1
やっとユーザー情報を取得し、個人のメタ情報を取得している。存在しないユーザーIDだと、ユーザー情報が取れずにエラーになる。パスワードが一致しなければエラーになる。パスワード認証に成功したら、session_tokensがあるかをチェックしている。メタ情報(wp_usermeta)には、以下がある。
> SELECT umeta_id,meta_key,meta_value FROM wp_usermeta WHERE user_id=1;
+----------+---------------------------------------+
| umeta_id | meta_key |
+----------+---------------------------------------+
| 1 | nickname |
| 2 | first_name |
| 3 | last_name |
| 4 | description |
| 5 | rich_editing |
| 6 | syntax_highlighting |
| 7 | comment_shortcuts |
| 8 | admin_color |
| 9 | use_ssl |
| 10 | show_admin_bar_front |
| 11 | locale |
| 12 | wp_capabilities |
| 13 | wp_user_level |
| 14 | dismissed_wp_pointers |
| 15 | show_welcome_panel |
| 17 | wp_dashboard_quick_press_last_post_id |
| 18 | community-events-location |
| 19 | session_tokens |
| 20 | wp_user-settings |
| 21 | wp_user-settings-time |
+----------+---------------------------------------+
ユーザー情報(wp_users)は、以下の感じ。
> desc wp_users;
+---------------------+---------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+---------------------+----------------+
| ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| user_login | varchar(60) | NO | MUL | | |
| user_pass | varchar(255) | NO | | | |
| user_nicename | varchar(50) | NO | MUL | | |
| user_email | varchar(100) | NO | MUL | | |
| user_url | varchar(100) | NO | | | |
| user_registered | datetime | NO | | 0000-00-00 00:00:00 | |
| user_activation_key | varchar(255) | NO | | | |
| user_status | int(11) | NO | | 0 | |
| display_name | varchar(250) | NO | | | |
+---------------------+---------------------+------+-----+---------------------+----------------+
nicenameってなんだ。nicknameの誤植かと思った。よくわからないが、user_login、user_nicename、display_name、そしてメタ情報のnicknameには同じ値が入っているようだ。
> select id,user_login,user_nicename,display_name from wp_users;
+----+------------+---------------+--------------+
| id | user_login | user_nicename | display_name |
+----+------------+---------------+--------------+
| 1 | taconana | taconana | taconana |
+----+------------+---------------+--------------+
> select user_id,meta_key,meta_value from wp_usermeta where user_id=1 and meta_key='nickname';
+---------+----------+------------+
| user_id | meta_key | meta_value |
+---------+----------+------------+
| 1 | nickname | taconana |
+---------+----------+------------+
nicenameってなんだ。気になるが話を戻して進める。ログインが成功すると、
[11:25:15] ■SHOW FULL COLUMNS FROM `wp_usermeta` --> 4
[11:25:15] ■UPDATE `wp_usermeta` SET `meta_value` = '...' WHERE `user_id` = 1 AND `meta_key` = 'session_tokens' -->
[11:25:15] ■SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (1) ORDER BY umeta_id ASC --> 20
session_tokens(600バイトくらいの文字列)を生成し、メタ情報としてDBに登録し、再度、メタ情報をすべて読み込んでいる。ちょっと無駄な感じ。
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'auth_key' LIMIT 1 --> 1
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'auth_salt' LIMIT 1 --> 1
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'logged_in_key' LIMIT 1 --> 1
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'logged_in_salt' LIMIT 1 --> 1
[11:25:15] 127.0.0.1:64000 [302]: POST /wp/wp-login.php
[11:25:15] 127.0.0.1:64000 Closing
[11:25:15] 127.0.0.1:64004 Accepted
また、オプションをチェックし、メモリ上で何かしているのかな。詳細は不明。ちなみに上記4つは暗号化されたデータ。
> SELECT option_name, option_value FROM wp_options where option_name in ('auth_key', 'auth_salt', 'logged_in_key', 'logged_in_salt');
+----------------+-------------------------+
| option_name | option_value |
+----------------+-------------------------+
| auth_key | tw5,-qLGBHkB%-#2>oQS... |
| auth_salt | /v>[o%2=XFOT]u[pLZ4e... |
| logged_in_key | 6p*:g+[=cJEFPO8Y!ae[... |
| logged_in_salt | SDNa4HEh]3_=)Z48?-d<... |
+----------------+-------------------------+
サイト表示時(index.php)のSQL
http://localhost:8488/wp/index.php で何が動作するのか、ちょっとだけ追跡してみた。
index.php
+-- wp-blog-header.php // WordPressの環境とテンプレートをロード
+-- wp_load.php
| +-- (wp-config.phpが存在する場合) wp-config.php
| | +-- wp-settings.php
| | +-- WPINC . /load.php
| | +-- WPINC . /plugin.php
| | +-- WPINC . /functions.php
| | +-- WPINC . /class-wp.php // class WP(WordPress環境セットアップ)を定義
| | +-- WPINC . /class-wp-query.php // class WP_Query(WordPressクエリ)を定義
| | +-- load.php::require_wp_db()
| | +-- WPINC . wp-db.php // class wpdbを定義
| | +-- $wpdb = new wpdb() // DBに接続
| | +-- $wpdb->db_connect()
| | +-- mysqli_real_connect()
| |
| +-- WPINC . /load.php
| +-- load.php::wp_fix_server_vars() // $_SERVERへセット
| | +-- load.php::wp_populate_basic_auth_from_authorization_header()
| +-- WPINC . /functions.php
| +-- load.php::wp_load_translations_early() // エラー検出のため早期ロードを試行
| +-- WPINC . /plugin.php
|
+-- functions.php::wp($query_vars) // WordPressクエリのセット
| +-- WPINC . /option.php
| +-- $wp->main() // WordPressのメインクエリの実行
| +-- $wp->init() // ログインユーザーをセット
| +-- $wp->parse_request()
| +-- $wp->send_headers()
| +-- $wp->query_posts()
| +-- $wp->handle_404()
| +-- $wp->register_globals() // $GLOBALSへセット
| +-- do_action_ref_array()
|
+-- template-loader.php // テンプレートをロード
+-- plugin.php::do_action('template_redirect')
+-- plugin.php::do_action('do_robots')
+-- plugin.php::do_action('do_favicon')
+-- functions.php::do_feed();
+-- wp-trackback.php
発行されたSQLは、以下の感じ。
[15:48:28] ■■ mysqli_real_connect:: localhost
[15:48:28] ■SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes' --> 123
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = 'nonce_key' LIMIT 1 --> 1
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = 'nonce_salt' LIMIT 1 --> 1
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = 'can_compress_scripts' LIMIT 1 --> 1
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = 'widget_recent-posts' LIMIT 1 --> 0
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = 'widget_recent_entries' LIMIT 1 --> 0
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = 'widget_recent-comments' LIMIT 1 --> 0
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = 'widget_recent_comments' LIMIT 1 --> 0
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = 'medium_crop' LIMIT 1 --> 0
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = 'medium_large_crop' LIMIT 1 --> 0
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = 'large_crop' LIMIT 1 --> 0
[15:48:29] ■
SELECT t.term_id
FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
WHERE tt.taxonomy IN ('wp_theme') AND t.name IN ('twentytwentyone')
LIMIT 1
--> 0
[15:48:29] ■
SELECT wp_posts.ID
FROM wp_posts
WHERE 1=1 AND (0 = 1) AND wp_posts.post_type = 'wp_global_styles' AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 1
--> 0
[15:48:29] ■
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
WHERE 1=1 AND ((wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish')))
ORDER BY wp_posts.post_date DESC
LIMIT 0, 10
--> 1
[15:48:29] ■SELECT FOUND_ROWS() --> 1
[15:48:29] ■SELECT wp_posts.* FROM wp_posts WHERE ID IN (1) --> 1
[15:48:29] ■
SELECT DISTINCT t.term_id, tr.object_id
FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE tt.taxonomy IN ('category', 'post_tag', 'post_format') AND tr.object_id IN (1)
ORDER BY t.name ASC
--> 1
[15:48:29] ■SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE t.term_id IN (1) --> 1
[15:48:29] ■SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1) ORDER BY meta_id ASC --> 0
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = '_transient_timeout_global_styles_twentytwentyone' LIMIT 1 --> 0
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = '_transient_global_styles_twentytwentyone' LIMIT 1 --> 0
[15:48:29] ■INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES (...) ON DUPLICATE KEY UPDATE ... -->
[15:48:29] ■INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES (...) ON DUPLICATE KEY UPDATE ... -->
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = 'site_logo' LIMIT 1 --> 0
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = '_transient_timeout_global_styles_svg_filters_twentytwentyone' LIMIT 1 --> 0
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = '_transient_global_styles_svg_filters_twentytwentyone' LIMIT 1 --> 0
[15:48:29] ■INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES (...) ON DUPLICATE KEY UPDATE ... -->
[15:48:29] ■INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES (...) ON DUPLICATE KEY UPDATE ... -->
[15:48:29] ■SELECT * FROM wp_users WHERE ID = '1' LIMIT 1 --> 1
[15:48:29] ■SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (1) ORDER BY umeta_id ASC --> 20
[15:48:29] ■
SELECT wp_posts.ID
FROM wp_posts
WHERE 1=1 AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish'))
ORDER BY wp_posts.post_date DESC
LIMIT 0, 5
--> 1
[15:48:29] ■
SELECT wp_comments.comment_ID
FROM wp_comments JOIN wp_posts ON wp_posts.ID = wp_comments.comment_post_ID
WHERE ( comment_approved = '1' ) AND wp_posts.post_status IN ('publish')
ORDER BY wp_comments.comment_date_gmt DESC
LIMIT 0,5
--> 1
[15:48:29] ■SELECT wp_comments.* FROM wp_comments WHERE comment_ID IN (1) --> 1
[15:48:29] ■SELECT comment_id, meta_key, meta_value FROM wp_commentmeta WHERE comment_id IN (1) ORDER BY meta_id ASC --> 0
[15:48:29] ■SELECT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, count(ID) as posts FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC --> 1
[15:48:29] ■
SELECT t.term_id
FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
WHERE tt.taxonomy IN ('category') AND tt.count > 0
ORDER BY t.name ASC
--> 1
[15:48:29] ■SELECT term_id, meta_key, meta_value FROM wp_termmeta WHERE term_id IN (1) ORDER BY meta_id ASC --> 0
[15:48:29] ■SELECT * FROM wp_posts WHERE ID = 3 LIMIT 1 --> 1
[15:48:29] 127.0.0.1:55404 [200]: GET /wp/
[15:48:29] 127.0.0.1:55404 Closing
[15:48:29] 127.0.0.1:55411 Accepted
(おまけ) SQLの説明
参照だけかと思っていたが、テーマは利用するたびに以下のように更新されていた。
INSERT INTO wp_options (option_name, option_value, autoload) VALUES ('_transient_global_styles_twentytwentyone', 'xxx', 'no')
ON DUPLICATE KEY UPDATE
option_name = VALUES(option_name),
option_value = VALUES(option_value),
autoload = VALUES(autoload);
ところで、このON DUPLICATE KEY UPDATEの構文は何だ！初めてみるSQLだ。INSERTで失敗したら、UPDATEに切り替えてくれるみたい。これは便利そう。JavaでINSERTで失敗したら、改めてUPDATEするコードをよく書いていたが、SQL一発で行けたのか。いつからこんな構文が有効になったのだろう。MySQL固有の記述なので、制約はあるかもしれない。
実は、FOUND_ROWS()も初めて見る。SQL_CALC_FOUND_ROWSを添えておけば、後でSELECT FOUND_ROWS()で件数を取れるみたい。LIMITと組み合わせると有効なようだ。例えば、レコードが10件でも LIMIT 0,3 指定だと3件しか返さない。しかし、後で SELECT FOUND_ROWS() で問い合わせると、10が得られるようだ。これも便利そう。ただし、これもMySQL固有の記述のようだ。
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts
ORDER BY wp_posts.post_date DESC LIMIT 0, 3;
SELECT FOUND_ROWS();
| ../ |
|---|