Coffeescript 简明教程

CoffeeScript - SQLite

SQLite 是一个轻量级的、基于架构的关系数据库引擎。作为网络浏览器中本地存储的嵌入式数据库软件，它是一个热门选择。

SQLite is a lightweight, schema-based relational database engine. It is a popular choice as embedded database software for local storage in web browsers.

与许多其他数据库管理系统不同，SQLite 不是一个客户端-服务器数据库引擎。有关详细信息，请阅读我们的链接：../sqlite/sqlite_overview.htm [SQLite 教程]

Unlike many other database management systems, SQLite is not a client–server database engine. For more information read our link:../sqlite/sqlite_overview.htm [SQLite Tutorial]

在本章中，你将学习如何使用 CoffeeScript 与 SQLite 数据库进行通信。

In this chapter you will learn how to communicate with SQLite database using CoffeeScript.

Installation

可以 node-sqlite3 模块将 SQLite3 数据库与 CoffeeScript 集成。此模块适用于 Node.js v0.10.x、v0.12.x、v4.x 和 v5.x。除了它还可以提供一个直接查询和参数绑定接口以及一个查询序列化 API 之外，此模块迎合了使用 CoffeeScript 与 SQLite3 进行通信的各种功能。

The SQLite3 database can be integrated with CoffeeScript using node-sqlite3 module. This module works with Node.js v0.10.x, v0.12.x, v4.x, and v5.x. This module caters various functions to communicate with SQLite3 using CoffeeScript, in addition to this it also provides an Straightforward query and parameter binding interface, and an Query serialization API.

可以使用 npm 安装 node-sqlite3 模块，如下所示。

You can install the node-sqlite3 module using npm as shown below.

npm install sqlite3

要使用 sqlite3 模块，必须首先创建一个表示数据库的连接对象，并且此对象将帮助你执行所有 SQL 语句。

To use sqlite3 module, you must first create a connection object that represents the database and this object will help you in executing all the SQL statements.

Connecting to Database

为了连接到 SQLite 数据库，首先通过调用 require() 函数的 node-sqlite3 模块创建其包，并将字符串 sqlite3 作为参数传递给它。然后，通过将数据库的名称传递给 sqlite3.Database() 构造，连接到数据库。

In order to connect to SQLite database first of all create its package by invoking the require() function of the node-sqlite3 module and pass the string sqlite3 as a parameter to it. Then connect to a database by passing the name of the database to sqlite3.Database() construct.

以下 CoffeeScript 代码显示了如何连接到现有数据库。如果数据库不存在，则会使用给定名称 test.db 创建数据库，然后打开数据库，最后返回数据库对象。

Following CoffeeScript code shows how to connect to an existing database. If database does not exist, then it will be created with the given name test.db, opened and finally the database object will be returned.

#Creating sqlite3 package
sqlite3 = require('sqlite3')

#Creating a Database instance
db = new (sqlite3.Database)('test.db')
console.log "Database opened successfully."

我们还可以提供 :memory: 来创建匿名内存数据库，以及空字符串来创建匿名基于磁盘的数据库，而不是 test.db。将上述代码保存为一个名为 create_db.coffee 的文件，并按如下所示执行该代码。如果数据库成功创建，那么它将产生以下消息：

We can also supply :memory: to create an anonymous in-memory database and, an empty string to create anonymous disk-based database, instead of test.db. Save the above code in a file with name create_db.coffee and execute it as shown below. If the database is successfully created, then it will produce the following message −

c:\> coffee create_db.coffee
Successfully connected

Creating a Table

你可以通过 CoffeeScript 中的 run() 函数创建 SQLite 数据库中的表。以字符串格式将创建表的查询传递给该函数。

You can create a table in SQLite database through CoffeeScript using the run() function. Pass the query to create a table to this function in String format.

以下 CoffeeScript 程序将用于在先前 test.db 数据库中创建表：

The following CoffeeScript program will be used to create a table in previously test.db database −

#Creating sqlite3 package
sqlite3 = require('sqlite3')

#Creating a Database instance
db = new (sqlite3.Database)('test.db')
console.log "Successfully connected"

db.serialize ->
  db.run 'CREATE TABLE STUDENT (name TEXT, age INTEGER, city TEXT)'
  console.log "Table created successfully"
  return
db.close()

serialize() 函数将数据库设置为序列化模式。在这种模式下，当遇到回调时，它将立即被调用。该回调中的查询会被串行执行。该函数返回后，数据库将再次被设置为正常模式。在完成事务之后，我们需要使用 close() 函数关闭连接。

The serialize() function sets the database in serialized mode. In this mode when ever a callback encounters, it will be called immediately. The queries in that callback are executes serially. Soon the function returns The database will be set to normal mode again. After completing the transaction we need to close the connection using close() function.

将上述代码保存为一个名为 create_table.coffee 的文件，并按如下所示执行该代码。这将在 test.db 数据库中创建名为 STUDENT 的表，显示以下消息。

Save the above code in a file with name create_table.coffee and execute it as shown below. This will create a table named STUDENT in the database test.db displaying the following messages.

C:\> coffee create_table.coffee
Successfully connected
Table created successfully

Inserting / Creating Data

可以通过执行插入语句将数据插入 SQLite 数据库中，方法是使用 CoffeeScript 代码。为此，可以使用准备 SQL 语句的 prepare() 函数。

You can insert data into SQLite database through CoffeeScript code by executing the insert statement. To do so we can use the prepare() function which prepares SQL statements.

它还接受具有绑定变量 ( ? ) 的查询，可以使用 run() 函数附加值到这些变量。可以使用预处理语句插入多条记录，插入所有记录后，需要使用 finalize() 函数最终确定预处理语句。

It also accepts query with bind variables (?), values to these variables can be attached using run() function. You can insert multiple records using prepared statement, and after inserting all the records, you need to finalize the prepared statement using finalize() function.

以下 CoffeeScript 程序展示了如何插入到上一个示例中创建的 STUDENT 表中记录。

The following CoffeeScript program shows how to insert records in the table named STUDENT created in previous example.

#Creating sqlite3 package
sqlite3 = require('sqlite3').verbose()

#Creating a Database instance
db = new (sqlite3.Database)('test.db')
console.log "Successfully connected"

db.serialize ->
  stmt = db.prepare('INSERT INTO STUDENT VALUES (?,?,?)')
  stmt.run 'Ram',24,'Hyderabad'
  stmt.run 'Robert',25,'Mumbai'
  stmt.run 'Rahim',26,'Bangalore'
  stmt.finalize()
  console.log "Data inserted successfully"
  return
db.close()

将以上代码保存在以 insert_data.coffee 命名的文件中，并按照如下所示执行。这将填充名为 STUDENT 的表格，显示以下消息。

Save the above code in a file with name insert_data.coffee and execute it as shown below. This will populate the table named STUDENT displaying the following messages.

C:\> coffee insert_data.coffee
Successfully connected
Data inserted successfully

Reading / Retrieving Data

可以使用 each() 函数从 SQLite 表中获取数据。此函数接受可选的回调函数，该回调函数将针对每一行调用。

You can get the data from an SQLite table using the each() function. This function accepts an optional callback function which will be called on each row.

以下 CoffeeScript 程序展示了如何从上一个示例中创建的 STUDENT 表中提取和显示记录

The following CoffeeScript program shows how we can fetch and display records from the table named STUDENT created in the previous example

#Creating sqlite3 package
sqlite3 = require('sqlite3').verbose()

#Creating a Database instance
db = new (sqlite3.Database)('test.db')
console.log "Successfully connected"

db.serialize ->
  console.log "The contents of the table STUDENT are ::"
  db.each 'SELECT rowid AS id, name,age,city FROM STUDENT', (err, row) ->
    console.log row.id + ': ' +row.name+', '+ row.age+', '+ row.city
    return
  return
db.close()

将以上代码保存在以 retrive_data.coffee 命名的文件中，并按照如下所示执行。这将检索名为 STUDENT 的表中的所有记录，并在控制台按如下方式显示。

Save the above code in a file with name retrive_data.coffee and execute it as shown below. This retrieves all the records in the table named STUDENT and displays on the console as follows.

C:\> coffee retrive_data.coffee
Successfully connected
The contents of the table STUDENT are ::
1: Ram, 24, Hyderabad
2: Robert, 25, Mumbai
3: Rahim, 26, Bangalore

Updating Data

以下 CoffeeScript 代码显示了如何使用 UPDATE 语句更新任何记录，然后提取和显示名为 STUDENT 的表中更新后的记录

The following CoffeeScript code shows how we can use UPDATE statement to update any record and then fetch and display updated records in the table named STUDENT

#Creating sqlite3 package
sqlite3 = require('sqlite3').verbose()

#Creating a Database instance
db = new (sqlite3.Database)('test.db')
console.log "Successfully connected"

db.serialize ->
  #Updating data
  stmt = db.prepare('UPDATE STUDENT SET city = ? where name = ?')
  stmt.run 'Delhi','Ram'
  console.log "Table updated"
  stmt.finalize()

  #Retrieving data after update operation
  console.log "The contents of the table STUDENT after update operation are ::"
  db.each 'SELECT rowid AS id, name, city FROM STUDENT', (err, row) ->
    console.log row.id + ': ' +row.name+', '+ row.city
    return
  return
db.close()

将以上代码保存在以 update_data.coffee 命名的文件中，并按照如下所示执行。这会更新名为 Ram 的学生的城市，并在更新操作后按如下所示在表中显示所有记录。

Save the above code in a file with name update_data.coffee and execute it as shown below. This updates the city of the student named Ram and displays all the records in the table after update operation as follows.

C:\> coffee update_data.coffee
Successfully connected
Table updated
The contents of the table STUDENT after update operation are ::
1: Ram, Delhi
2: Robert, Mumbai
3: Rahim, Bangalore

Deleting Data

以下 CoffeeScript 代码显示了如何使用 DELETE 语句删除任何记录，然后提取和显示名为 STUDENT 的表中余下的记录。

The following CoffeeScript code shows how we can use DELETE statement to delete any record and then fetch and display remaining records from the table named STUDENT.

#Creating sqlite3 package
sqlite3 = require('sqlite3').verbose()

#Creating a Database instance
db = new (sqlite3.Database)('test.db')
console.log "Successfully connected"

db.serialize ->
  #Deleting data
  stmt = db.prepare('DELETE FROM STUDENT WHERE name = ?')
  stmt.run 'Ram'
  console.log "Record deleted"
  stmt.finalize()

  #Retrieving data after delete operation
  console.log "The contents of the table STUDENT after delete operation are ::"
  db.each 'SELECT rowid AS id, name, city FROM STUDENT', (err, row) ->
    console.log row.id + ': ' +row.name+', '+ row.city
    return
  return
db.close()

将以上代码保存在以 delete_data.coffee 命名的文件中，并按照如下所示执行。它会删除名为 Ram 的学生的记录，并在删除操作后按如下所示显示表中所有余下的记录。

Save the above code in a file with name delete_data.coffee and execute it as shown below. It deletes the record of the student named Ram and displays all the remaining in the table after delete operation as follows.

Successfully connected
Record deleted
The contents of the table STUDENT after delete operation are ::
2: Robert, Mumbai
3: Rahim, Bangalore