javascript SQLITE_ERROR：Node.js 中没有这样的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21716564/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLITE_ERROR: no such table in Node.js
提问by Jason Reid
I'm writing a simple server/client to keep track of the amount of times a user has logged in. A user can create an account and have their count set to 1. Following logins will increase their count in the backend SQLITE3 database.
我正在编写一个简单的服务器/客户端来跟踪用户登录的次数。用户可以创建一个帐户并将其计数设置为 1。以下登录将增加其在后端 SQLITE3 数据库中的计数。
In the example below, I run the "add" function which correctly checks if the user exists already, then if not, adds the username, password, and 1 to the table of users.
在下面的示例中，我运行“添加”函数，该函数正确检查用户是否已经存在，如果不存在，则将用户名、密码和 1 添加到用户表中。
This properly returns 1 as you can see in the output, but why is it erroring at the end? I'm not making any other calls, but it's returning a no such tableerror. The only call I make is console.log(UsersModel.add('kpam', '123'));, which is on the last line of the code. I tried looking into the line 72 of events.js, but it didn't really give me much. I added print statements to make it trace more obvious, but I have a feeling something is going on behind the scenes?
正如您在输出中看到的那样，这正确地返回了 1，但为什么最后会出错？我没有拨打任何其他电话，但它返回了一个no such table错误。我打的唯一电话是console.log(UsersModel.add('kpam', '123'));，它位于代码的最后一行。我尝试查看 的第 72 行events.js，但它并没有给我带来太多帮助。我添加了打印语句以使其跟踪更明显，但我感觉幕后正在发生某些事情？
Basically, I'm confused why if I only called one function, and that function returns successfully, theres an error at the end of execution?
基本上，我很困惑为什么如果我只调用一个函数，并且该函数成功返回，那么在执行结束时会出现错误？
Here is the error returned:
这是返回的错误：
:$ node warmup.js
Creating DB file.
making table!
adding user!
1
events.js:72
throw er; // Unhandled 'error' event
^
Error: SQLITE_ERROR: no such table: Users
:$
And here is my code:
这是我的代码：
var http = require('http');
var fs = require('fs');
var file = 'data.db';
var exists = fs.existsSync(file);
if (!exists) {
console.log("Creating DB file.");
fs.openSync(file, 'w');
}
var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database(file);
var UsersModel = {
// success, no errors/problems
SUCCESS: 1,
// cannot find the user/password pair in the database (for 'login' only)
ERR_BAD_CREDENTIALS: -1,
// trying to add a user that already exists (for 'add' only)
ERR_USER_EXISTS: -2,
// invalid user name (empty or longer than MAX_USERNAME_LENGTH) (for 'add'/'login')
ERR_BAD_USERNAME: -3,
// invalid password name (longer than MAX_PASSWORD_LENGTH) (for 'add')
ERR_BAD_PASSWORD: -4,
// maximum user name length
MAX_USERNAME_LENGTH: 128,
// maximum password length
MAX_PASSWORD_LENGTH: 128,
login: function(user, password) {
if (!UsersModel.userExists(user, false)) {
return UsersModel.ERR_BAD_CREDENTIALS;
}
if (!UsersModel.checkPassword(user, password)) {
return UsersModel.ERR_BAD_CREDENTIALS;
}
count = UsersModel.increaseCount(user);
return count;
},
add: function(user, password) {
if (UsersModel.userExists(user, true)) {
return UsersModel.ERR_USER_EXISTS;
}
if (!UsersModel.isValidUsername(user)) {
return UsersModel.ERR_BAD_USERNAME;
}
if (!UsersModel.isValidPassword(password)) {
return UsersModel.ERR_BAD_PASSWORD;
}
UsersModel.addUser(user, password);
return 1;
},
userExists: function(user, makeTable) {
if (!exists) {
if (makeTable) {
console.log('making table!');
db.run('CREATE TABLE Users (name TEXT, password TEXT, count INT)');
}
return false;
}
db.serialize(function() {
console.log('checking user!');
row = db.get("SELECT name FROM Users WHERE name = '" + user + "'");
});
return !(typeof(row.name) === 'undefined');
},
increaseCount: function(user) {
db.serialize(function() {
console.log('increasing count!');
count = db.get("SELECT count FROM Users WHERE name = '" + user + "'") + 1;
db.run("UPDATE Users SET count = '" + count + "' WHERE name = '" + user + "'");
return count;
});
},
addUser: function(user, password) {
count = 0;
console.log('adding user!');
db.run("INSERT INTO Users (name, password, count) VALUES ('" + user + "','" + password + "','" + 0 + "')");
},
checkPassword: function(user, password) {
db.serialize(function() {
console.log('checking pw!');
row = db.get("SELECT password FROM Users WHERE name = '" + user + "'");
});
return row.password == password;
},
isValidUsername: function(user) {
return user.length < 129;
},
isValidPassword: function(password) {
return password.length < 129;
}
}
console.log(UsersModel.add('kpam', '123'));
回答by Jason Reid
The db.run(...) calls are asynchronous. So they return immediately, and look like success to your code. However they're still running in the background. Because of this, the SELECT statement is likely starting to run before the CREATE TABLE completes. And that's why you get that error.
db.run(...) 调用是异步的。所以它们会立即返回，看起来你的代码成功了。但是它们仍然在后台运行。因此，SELECT 语句很可能在 CREATE TABLE 完成之前开始运行。这就是为什么您会收到该错误的原因。
I notice that the SELECT statement is inside of a db.serialize(...) call. Unfortunately, that call only serializes statements that are directly inside its scope. All calls outside of the serialize block continue to run in parallel (this includes the INSERT statement that comes up later).
我注意到 SELECT 语句在 db.serialize(...) 调用中。不幸的是，该调用仅序列化直接在其作用域内的语句。serialize 块之外的所有调用继续并行运行（这包括稍后出现的 INSERT 语句）。
Your code needs to be restructured to use the callbacks that the node sqlite3 module relies on. Take a look at the simple example at: https://github.com/mapbox/node-sqlite3/blob/master/examples/simple-chaining.js
您的代码需要重构以使用节点 sqlite3 模块所依赖的回调。看看简单的例子：https: //github.com/mapbox/node-sqlite3/blob/master/examples/simple-chaining.js
Notice how the last parameter to each db operation is the name of the function to call after the operation is completed.
注意每个 db 操作的最后一个参数是如何在操作完成后调用的函数的名称。
