Apache PHP DB  安裝 & 优化

Article / / database, JSON, optimization, PHP, web server, Wordpress

我對於寫 code 嘅睇法同教育界嘅 STEM 唔同，甚至去到商界都普遍低估軟件開發嘅難度而追求大量功能，往往因為有主觀需求就由零開始寫 code，無花時間去揾有乜 open source 或者 library 可以用。用類似產品交易嘅商業模式之下，只會導至盡快交功課完成任務， 缺乏長時間運用同持續 debug 改良，質素包括手机 app 普遍都唔高，而且達唔到預期效果之餘仲不断產生大量新問題。我主張透過長期運用摸索何謂 best practice 同不断改良，相當於無既定答案而嘅實驗學習，IDE 相當於學校嘅實驗室係軟件開發提高效率嘅關鍵。寫 web 相對容易入門，但 web server 可以話係實驗室嘅一部份；市面上應該無免費嘅 web 或者 database hosting 服務，揾出最好嘅工具同优化到最好嘅狀態，同寫 code 一樣係解決問題過程。

一般情況下免費嘅 wordpress.com 同 Google sheet 雲服務已經可以造倒大部份 web 同 database 近似嘅功能，喺真係唔夠用嘅情況下先針對運行 wordpress 起自己嘅 server。我唔建議用 WAMP 嘅整合式安裝，應該由 Apache HTTP server 開始 config 同定期 update。Apache 同 64bit 嘅 Windows 係最理想開發環境，而 VS Code 係現時最佳嘅寫 web 用嘅 IDE，有 syntax highlight 功能對改 config file 都有好大幫助。

喺 Apache Lounge download Apache 嘅 zip file 之後，要用 Administrator 權限喺 cmd (Command Prompt) 打 httpd.exe -k install將 Apache server reg 造 service，然後打 httpd.exe -k start 睇吓有無 error message。為左方便 restart server，用 run 打 Shell:startup ，放入 ApacheMonitor.exe 嘅 shortcut 就可以每次 boot 机都有個 taskbar icon 睇倒 server online 定 offline。

Apache conf folder 入面嘅 httpd.conf 係需要經常修改嘅 config file，除左要改好啲 path 同加入 php 部份，development 同 production server 最大嘅分別在於 cache 嘅時間，避免改左野之後 browser 無反応。 MIME type 同 windows 嘅 file type 唔同，我從新打左個 mime.types file，例如包含 mkv 可以喺 browser 度播片。我喺 config 入面加左 status 功能，喺 broswer 打 http://127.0.0.1/server-status 就可以睇倒 server 嘅 loading。另外，好多時 browser 無可避免會 cache 起某啲例如 image file，所以要裝個 Clear Cache broswer plugin 必要時清 browser cache。

Define SRVROOT "d:/@server"
ServerRoot "${SRVROOT}/apache"
LoadModule access_compat_module modules/mod_access_compat.so
LoadModule auth_basic_module modules/mod_auth_basic.so
LoadModule authz_core_module modules/mod_authz_core.so 
LoadModule authz_host_module modules/mod_authz_host.so
LoadModule autoindex_module modules/mod_autoindex.so
LoadModule dir_module modules/mod_dir.so
LoadModule headers_module modules/mod_headers.so
LoadModule log_config_module modules/mod_log_config.so
LoadModule filter_module modules/mod_filter.so
LoadModule mime_module modules/mod_mime.so
LoadModule rewrite_module modules/mod_rewrite.so
LoadModule setenvif_module modules/mod_setenvif.so
LoadModule deflate_module modules/mod_deflate.so
LoadModule expires_module modules/mod_expires.so
LoadModule status_module modules/mod_status.so
# ##############################################################################
# config                                                         
# ##############################################################################
LoadModule php_module "${SRVROOT}/php/php8apache2_4.dll"  
PHPIniDir "${SRVROOT}"
AddHandler application/x-httpd-php .php
AddType application/x-httpd-php .php
DirectoryIndex index.php index.htm index.html
HostnameLookups Off
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
Listen 80
FileETag None
ErrorLog "${SRVROOT}/httpd-error.log"
LogLevel warn
EnableSendfile Off
EnableMMAP off
AddDefaultCharset utf-8
<IfModule mime_module>
    TypesConfig "${SRVROOT}/mime.types"
</IfModule>
ServerAdmin hoi@chanchunghoi.com
ServerName chanchunghoi.com
# ##############################################################################
#  WEB PERFORMANCE                                                            
# ##############################################################################
<Location /server-status>
   SetHandler server-status
   Order deny,allow
   Deny from all
   Allow from all
</Location>
<IfModule mod_headers.c>
    Header unset ETag
</IfModule>
<IfModule mpm_winnt_module>
    ThreadsPerChild        16
    MaxConnectionsPerChild   320
	AcceptFilter http None    
    AcceptFilter https None  
</IfModule>
<IfModule mod_deflate.c>
    AddOutputFilter DEFLATE js css htm html php ttf txt rss xml json csv
</IfModule>
<IfModule mod_mime.c>
    AddCharset utf-8 .atom .css .js .json .rss .xml
</IfModule>
<IfModule mod_expires.c>
ExpiresActive on
ExpiresDefault "access plus 1 second"
ExpiresByType application/json "access plus 1 second"
ExpiresByType application/x-httpd-php "access plus 1 second"
ExpiresByType application/xhtml+xml "access plus 1 second"
ExpiresByType text/css "access plus 1 second"
ExpiresByType text/html "access plus 1 second"
ExpiresByType text/csv "access plus 1 second"
ExpiresByType text/plain "access plus 1 second"
ExpiresByType text/javascript "access plus 1 second"
ExpiresByType font/woff2 "access plus 1 second"
ExpiresByType application/atom+xml "access plus 1 day"
ExpiresByType application/rss+xml "access plus 1 day"
ExpiresByType application/vnd.ms-excel "access plus 1 month"
ExpiresByType application/vnd.openxmlformats-officedocument.spreadsheetml.sheet "access plus 1 month"
ExpiresByType image/avif "access plus 1 month"
ExpiresByType image/bmp "access plus 1 month"
ExpiresByType image/gif "access plus 1 month"
ExpiresByType image/jpeg "access plus 1 month"
ExpiresByType image/png "access plus 1 month"
ExpiresByType image/tiff "access plus 1 month"
ExpiresByType image/vnd.microsoft.icon "access plus 1 month"
ExpiresByType image/webp "access plus 1 month"
ExpiresByType application/gzip "access plus 1 year"
ExpiresByType application/java-archive "access plus 1 year"
ExpiresByType application/pdf "access plus 1 year"
ExpiresByType application/vndrar "access plus 1 year"
ExpiresByType application/x-shockwave-flash "access plus 1 year"
ExpiresByType application/x-tar "access plus 1 year"
ExpiresByType application/zip "access plus 1 year"
ExpiresByType audio/aac "access plus 1 year"
ExpiresByType audio/flac "access plus 1 year"
ExpiresByType audio/mpeg "access plus 1 year"
ExpiresByType audio/webm "access plus 1 year"
ExpiresByType font/ttf "access plus 1 year"
ExpiresByType font/woff "access plus 1 year"
ExpiresByType image/svg+xml "access plus 1 year"
ExpiresByType video/mp2t "access plus 1 year"
ExpiresByType video/mp4 "access plus 1 year"
ExpiresByType video/mpeg "access plus 1 year"
ExpiresByType video/webm "access plus 1 year"
ExpiresByType video/x-matroska "access plus 1 year"
ExpiresByType video/x-msvideo "access plus 1 year"
</IfModule>
# ##############################################################################
# security                                                    
# ##############################################################################
<Directory "${SRVROOT}/www/">
Options Indexes FollowSymLinks  Includes ExecCGI
AllowOverride All
Require all granted
Options All -Indexes
</Directory>
<Directory />
AllowOverride none
Require all denied
</Directory>
<Files ".ht*">
    Require all denied
</Files>
<FilesMatch ".(json)$">
    Header set Access-Control-Allow-Origin "*"
</FilesMatch>
# ##############################################################################
# virtual host                                                         
# ##############################################################################
<VirtualHost _default_:80>
ServerName chanchunghoi.com
DocumentRoot "${SRVROOT}/www/chanchunghoi.com"
</VirtualHost>
<VirtualHost *:80>
ServerName wp.chanchunghoi.com
DocumentRoot "${SRVROOT}/www/wp20190614"
</VirtualHost>

到 PHP For Windows 會揾倒啱 Apache 用嘅 Thread Safe (TS) version，開個 .php file 入面打 <?php echo phpinfo(); ?> ，就會睇倒 php 嘅 config。我跟据 wordpress 嘅需求改好 php.ini 之後，睇 Apache 嘅 error.txt 應該會出現 php_intl.dll not found 嘅問題，需要將 php 嘅 icu???.dll 幾個 file 抄到 Apache 嘅 bin folder；如果有 php_curl.dll not found 嘅問題，就要抄埋 Libssh2.dll。cURL 功能仲需要一個通用嘅 cacert.pem file，我主張有改動或者常用嘅 file 都放到 server root，避免 update 嘅時候 overwrite 左。

mime.types & cacert.pem & php.ini 
include_path = "d:/@server/php/ext/; d:/@server/php/"
extension_dir = "d:/@server/php/ext/"
upload_tmp_dir ="d:/@server/upload/"
opcache.file_cache ="d:/@server/session/"
session.save_path ="d:/@server/session/"
curl.cainfo = "d:/@server/cacert.pem"
openssl.cafile = "d:/@server/cacert.pem"

extension=php_curl.dll
extension=php_fileinfo.dll
extension=php_gd.dll
extension=php_gettext.dll
extension=php_mbstring.dll
extension=php_exif.dll 
extension=php_intl.dll
extension=php_mysqli.dll
extension=php_soap.dll
extension=php_bz2.dll
extension=php_zip.dll
extension=php_openssl.dll
zend_extension=php_opcache.dll

post_max_size = 100M
upload_max_filesize = 100M
short_open_tag = Off
register_globals = Off
output_buffering = 4096
zlib.output_compression = Off
max_input_time = 60
max_execution_time = 120
error_reporting = E_ERROR 
display_errors = On

小量需要經常修改嘅 data 適合用 JSONedit 輔助修改，以 json file 形式儲存，然後用 cURL 或者 fopen 讀 data，但如果有大量 data 嘅話就會有 performance 嘅問題需要用 database。MariaDB Server 係比 MySQL 免費版更佳嘅選擇，兼容 wordpress ，installer 入面包含 database UI 軟件 HeidiSQL，比 PHPmyAdmin 更好用，尤其是裏面有 csv import 功能，可以 import Google sheet export 嘅 csv，將 data transform 到 DB，然後可以運用 php 同 web 以特定形式展示。

HeidiSQL > tools > import CSV file

Reference: