## 問題
你想在Node.js中訪問[SQLite](http://www.sqlite.org/)數據庫。
## 方法
使用[SQLite module](http://code.google.com/p/node-sqlite/)。
~~~
sqlite = require 'sqlite'
db = new sqlite.Database
# The module uses asynchronous methods,
# so we chain the calls the db.execute
exampleCreate = ->
db.execute "CREATE TABLE snacks (name TEXT(25), flavor TEXT(25))",
(exeErr, rows) ->
throw exeErr if exeErr
exampleInsert()
exampleInsert = ->
db.execute "INSERT INTO snacks (name, flavor) VALUES ($name, $flavor)",
{ $name: "Potato Chips", $flavor: "BBQ" },
(exeErr, rows) ->
throw exeErr if exeErr
exampleSelect()
exampleSelect = ->
db.execute "SELECT name, flavor FROM snacks",
(exeErr, rows) ->
throw exeErr if exeErr
console.log rows[0] # => { name: 'Potato Chips', flavor: 'BBQ' }
# :memory: creates a DB in RAM
# You can supply a filepath (like './example.sqlite') to create/open one on disk
db.open ":memory:", (openErr) ->
throw openErr if openErr
exampleCreate()
~~~
## 討論
你也可以事先準備好SQL語句:
~~~
sqlite = require 'sqlite'
async = require 'async' # Not required but added to make the example more concise
db = new sqlite.Database
createSQL = "CREATE TABLE drinks (name TEXT(25), price NUM)"
insertSQL = "INSERT INTO drinks (name, price) VALUES (?, ?)"
selectSQL = "SELECT name, price FROM drinks WHERE price < ?"
create = (onFinish) ->
db.execute createSQL, (exeErr) ->
throw exeErr if exeErr
onFinish()
prepareInsert = (name, price, onFinish) ->
db.prepare insertSQL, (prepErr, statement) ->
statement.bindArray [name, price], (bindErr) ->
statement.fetchAll (fetchErr, rows) -> # Called so that it executes the insert
onFinish()
prepareSelect = (onFinish) ->
db.prepare selectSQL, (prepErr, statement) ->
statement.bindArray [1.00], (bindErr) ->
statement.fetchAll (fetchErr, rows) ->
console.log rows[0] # => { name: "Mia's Root Beer", price: 0.75 }
onFinish()
db.open ":memory:", (openErr) ->
async.series([
(onFinish) -> create onFinish,
(onFinish) -> prepareInsert "LunaSqueeze", 7.95, onFinish,
(onFinish) -> prepareInsert "Viking Sparkling Grog", 4.00, onFinish,
(onFinish) -> prepareInsert "Mia's Root Beer", 0.75, onFinish,
(onFinish) -> prepareSelect onFinish
])
~~~
[SQLite版的SQL](http://www.sqlite.org/lang.html)和[node-sqlite模塊文檔](https://github.com/orlandov/node-sqlite#readme)提供了更完整的信息。
- 貢獻
- 作者
- 授權協議
- 1、Syntax
- 在服務端和客戶端重用代碼
- For循環
- 嵌入JavaScript代碼
- 值域
- 2、Classes and Objects
- 類方法和實例方法
- CoffeeScript式的Type函數
- 鏈式調用
- 克隆對象(深度克隆)
- 不存在就賦值為對象字面量
- 類變量
- 3、Strings
- 分割字符串
- 字符串匹配
- 查找子字符串
- 讓整個字符串小寫
- 大寫整個字符
- 去掉字符串首尾的空白
- 生成唯一的ID
- 首字母大寫
- 重復字符串
- 字符串插值
- 4、Arrays
- Python式的Zip函數 Python-like Zip Function
- 數組去重 Removing Duplicate Elements from Arrays
- 基于數組構建字典對象 Creating a dictionary Object from an Array
- 數組轉成字符串 Creating a String from an Array
- 檢查每一個元素 Testing Every Element
- 數組最大值
- 過濾數組 Filtering Arrays
- 定義區間數組 Define Ranges Array
- 轉置數組 Reversing Arrays
- 化簡數組 Reducing Arrays
- 使用數組來做值交換 Using Arrays to Swap Variables
- 列表解析 List Comprehensions
- 檢查值的類型是否是數組
- 連接數組
- 攪亂數組中的元素 Shuffling Array Elements
- 數組映射 Mapping Arrays
- 5、Dates and Times
- Calculate Phase of the Moon for a Date
- 找出某月的最后一天是幾號 Finding the Last Day of the Month
- 獲取兩個日期相差的天數 Get Days Between Two Dates
- 計算復活節島日期 Calculate the Date of Easter Sunday
- 計算感恩節的日期(美國和加拿大) Calculate the Date of Thanksgiving (USA and Canada)
- 計算上一個(下一個)月份 Finding Last (or Next) Month
- 6、Math
- 快速逆平方根
- 一個隨機整數的函數
- 更快的斐波那契算法
- 生成可預測的隨機數
- 弧度與度轉換
- 生成隨機數
- 數學常數
- 7、Functions
- 反抖動函數 Debounce Functions
- 參數數組化 Splat Arguments
- 當函數調用的括號不可以省略時 When Function Parentheses Are Not Optional
- 遞歸函數 Recursive Functions
- 8、Metaprogramming
- 擴展內置對象 Extending Built-in Objects
- 檢測并創建缺失的函數 Detecting and Creating Missing Functions
- 9、jQuery
- 回調綁定
- 創建jQuery插件
- AJAX
- 10、Ajax
- 不依賴jQuery的Ajax請求 Ajax Request Without jQuery
- 11、Regular Expressions
- 替換子字符串 Replacing Substrings
- 使用定點 Using Heregexes
- 使用HTML字符實體替換HTML標簽 Replacing HTML Tags with HTML Named Entities
- 搜索子字符串 Searching for Substrings
- 12、Networking
- 簡單的服務器
- 雙向客戶端
- 最簡單的HTTP客戶端
- 最簡單的HTTP服務器
- 簡單的客戶端
- 雙向服務端 Bi-Directional Server
- 13、Design Patterns
- 命令模式
- 單例模式
- 策略模式 Strategy Pattern
- 建造者模式 Builder Pattern
- 備忘錄模式 Memento Pattern
- 解釋器模式 Interpreter Pattern
- 裝飾者模式
- 橋接模式
- 工廠方法模式
- 14、Databases
- MongoDB
- SQLite
- 15、Testing
- 使用Jasmine測試