SQL 語法片段小記

個人使用的 SQL 語法幾乎都是最基本的, 以下記錄幾個最近使用 ActionScript 時用到的片段:

1. 如果某筆記錄已經存在, 則更新該記錄

var sql_xml:XML = <s>
 UPDATE "{TABLE_NAME}"
 SET {type}='{path}'
 WHERE sid=(
  SELECT sid FROM "{TABLE_NAME}"
  WHERE "year"={year} AND "month"={month} 
  AND "date"={date} AND "page"={page}
 );
 </s>;

2. 如果某筆記錄不存在, 則新增記錄

var sql_xml:XML = <s>
 INSERT INTO "{TABLE_NAME}" (year, month, date, page, {type})
 SELECT {year}, {month}, {date}, {page}, '{path}'
 WHERE NOT EXISTS (
  SELECT * FROM "{TABLE_NAME}" 
  WHERE "year"={year} AND "month"={month}
  AND "date"={date} AND "page"={page}
 );
 </s>;

2. 兩張表 "content" 和 "content_usage" 是以 INNER JOIN 的方式查詢。在變更 "content_usage" 而需要 "content" 的條件時

var sql_xml:XML = <s>
 UPDATE "content_usage"
 SET "open"=0, "open_date"=NULL
 WHERE EXISTS (
  SELECT * FROM "content"
  WHERE "cid"="content_usage"."cid" AND "lv" &gt;= {level}
 );
 </s>;

Adobe AIR 的 SQLite 之 DATETIME 類型

一般 SQLite 的工具 (例如: Firefox plugin - SQLite Manager) 對其 DATETIME 資料類型, 裡面還是使用 TEXT 的方式處理, 以字串表示。

AIR 的 SQLite 引擎則是使用浮點數的表示方式, 貼心的地方是在讀取時會是 Date 物件。但是, 若以 AIR 去讀 DATETIME 類型 "2012-08-23 18:18:18" 這樣的文字格式, 將會無法顯示正確的日期; 若 SQLite Manager 去讀取 AIR 寫入的 DATETIME 類型 將會顯示浮點數。相互之間無法讀取。

解決的方式:

1. 不使用 SQLite Manager, 使用 Lita 之類 AIR 寫成的工具。
2. 若和其它程式語言合作開發時, 第一種方式就不方便了。可以將日期欄位設定為 TEXT 而不要使用 DATETIME, 如此一來以字串來處理就可以了。

在 PHP 中使用 SQLite3

很早就知道 PHP5 內建 SQLite 的功能，但是一直沒在 PHP 上使用 SQLite，原因無它「有 MySQL 幹嘛用 SQLite」。
Adobe AIR 上的是 SQLite3，Firefox 的外掛 SQLite Manager 也(只)支援 SQLite3，搭配起來還蠻方便的。
最近有個案子是要做多國語言的展示平台（當然是用 Flash 做囉），要讓管理人員（不會是我）上平台的內容。問題出在那台機器可能沒有網路，就算有網路，頻寬也可能會是個問題，所以要做單機的版本。
展示端無庸置疑 Adobe Flash/AIR 是個好選項，但內容管理用 AIR 就顯得麻煩。為了方便更新內容（拷貝/貼上），目前考慮使用 PHP + SQLite，而把 MySQL 排除在外。

PHP 目前最新的版本是 5.3.X，我租用的 PHP 空間為 5.1.6，雖然這個專案不會用到租用的空間，但為了可以通用，就會以 5.1.6 為預設版本。
用了 PHP5 的 SQLite 才知道原來它的版本是2。PHP 5.1.6 要使用 SQLite3 必須透過 PDO。PHP 5.3 之後的版本才有直接支援 SQLite3 的類別。SQLite2 有啥不好？其實沒什麼不好，只是個人用慣 SQLite Manager 了，而且用舊版心中總會掛念它是舊版，若要用新版的功能時，可能就悔不當初 :P

AIR 不支援提供系統資料表 sqlite_master

Adobe AIR 不支援提供系統資料表 sqlite_master 和 sqlite_ 開頭的資料表，
可以透過 SQLConnection.loadSchema() 取得相關資料。

Adobe AIR 這樣的做法有點討厭。

AIR local database 測試

Flash frame AS3:

import flash.data.SQLResult;
import flash.filesystem.File;
import flash.data.SQLStatement;
import flash.data.SQLConnection;
import flash.events.SQLEvent;
import flash.events.SQLErrorEvent;
import flash.errors.SQLError;
import fl.data.DataProvider;

var dbFile:File = new File("app-resource:/test01.db");
var dbConn:SQLConnection = new SQLConnection();
var dbStatement:SQLStatement;
var insertCount:uint = 0;
dbConn.addEventListener(SQLEvent.OPEN, onDBOpened);
dbConn.addEventListener(SQLErrorEvent.ERROR, onDBError);
// 若檔案不存在, 會自動建立
dbConn.open(dbFile);

function onDBOpened(e:SQLEvent) {
 trace("onDBOpened:", e);
 if (e.type == SQLEvent.OPEN) {
  dbStatement = new SQLStatement();
  dbStatement.sqlConnection = dbConn;
  dbStatement.text = "select * from Users";
  dbStatement.addEventListener(SQLEvent.RESULT, onSQLResult);
  dbStatement.addEventListener(SQLErrorEvent.ERROR, onSQLError);
  dbStatement.execute();
 }
}

function onDBError(e:SQLErrorEvent) {
 trace("onDBError:", e);
}

function onSQLResult(e:SQLEvent) {
 trace("onSQLResult:", e);
 var d:Array = dbStatement.getResult().data;
 QopDump.echo(d);
 dataGrid.dataProvider = new DataProvider(d);
 dbConn.close();
}

function onSQLError(e:SQLErrorEvent) {
 trace("onSQLError:", e);

 var err:SQLError = e.error;
 trace("Error Code:", err.code);
 trace("Operation:", err.operation);
 trace("Error Message:", err.message, "\n");
 if (err.message.indexOf("no such table") != -1) {
  dbStatement = new SQLStatement();
  dbStatement.sqlConnection = dbConn;
  dbStatement.text = "CREATE TABLE Users "+
   "(id INTEGER PRIMARY KEY, username TEXT, password TEXT);";
  dbStatement.addEventListener(SQLEvent.RESULT, doInsert);
  dbStatement.addEventListener(SQLErrorEvent.ERROR, onSQLError);
  dbStatement.execute();
 }
}

function doInsert(e:SQLEvent) {
 insertCount++;
 if(insertCount>3) return;
 dbStatement = new SQLStatement();
 dbStatement.sqlConnection = dbConn;
 var sql:String;
 switch (insertCount) {
  case 1 :
   sql = "INSERT INTO Users VALUES(null,'MyName','MyPassword');";
   break;
  case 2 :
   sql = "INSERT INTO Users VALUES(null,'qop','qop543');";
   break;
  case 3 :
   sql = "INSERT INTO Users VALUES(null,'小黑','出運了');";
   break;
 }
 dbStatement.text = sql;
 dbStatement.addEventListener(SQLEvent.RESULT, doInsert);
 dbStatement.execute();
}

執行第一次的結果 (由無到有建立DB):

執行第二次的結果 (取得資料秀在 DataGrid 上):

AIR local database

AIR 內含 SQLite SQL database engine
每個資料庫都以一個檔案實體存在
官網: SQLite home page
工具: SQLite Database Browser, SQLite Control Center