javascript - Node.js 和 SQL 检查数据库的结果

Question

我遇到了我用 PHP 完成的挑战，但想看看如何通过 node.js 和 SQL 完成它。

我是这方面的初学者，完全不确定我所做的是否合法，但我想学习在 node 中做这件事；挑战在于:

We would like a function that takes in the UserAgent of a browser as a string, and compares it to a list of approved browsers and versions

The database structure looks this:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| bigIntPKID | varchar(50) BrowserName | varchar(50) VersionNumber | bitGreaterVersions |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|         1       |           Internet Explorer        |                       9                     |             False          |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|         1       |           Internet Explorer        |                       10                   |             False          |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|         1       |           Chrome                      |                       26                   |             True           |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|         1       |           Firefox                        |                       22                   |             True           |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
bitGreaterVersions are any version greater than the version number is compatible.

The data table expressed in human readable format would be “Our app is compatible with IE9,10, Chrome 26+ and Firefox 22+”

Create a function that will take a user agent as a string, and return true or false if it is compatible based on the above criteria.

然后这里是 Node 和 SQL 代码(我希望)...

var express = require("express");
var mysql = require("mysql");

var connection = mysql.createConnection({
    host: 'localhost',
    username: 'username',
    password: 'password',
    dbname: 'browsers'
});

var app = express();

// if there is an error connecting to the database
connection.connect(function(error) {
    if(error) {
        console.log('Cannot establish connection to database...');
        return;
    }
    console.log('Database connection established...');
});

// function to check browser compatibility
function getUserAgent(userAgent, versionNumber) {

    var isBrowserCompatible;

    // access the database
    app.get("/path-to-database", function(req, res) {
        // query for the required results
        connection.query('SELECT BrowserName, VersionNumber, bitGreaterVersions FROM userAgentTable', function(error, rows, fields) {
        connection.end();

            if(error) {
                console.log('Cannot find the results...');
            }

            isBrowserCompatible = false;

            // loop over the returned table values
            for(var i = 0; i < rows.length; i++) {
                // if browser name and version number are equal to userAgent and versionNumber
                if(rows[i].BrowserName == userAgent && rows[i].VersionNumber == versionNumber) {
                    // the browser is compatible
                    isBrowserCompatible = true;
                    break;
                  // if browser name is equal to userAgent but the version number is less than versionNumber
                } else if(rows[i].BrowserName == userAgent && rows[i].VersionNumber < versionNumber) {
                    if(rows[i].bitGreaterVersions == 'False') {
                        // the browser is not compatible
                        break;
                    } else {
                        // otherwise the browser is compatible
                        isBrowserCompatible = true;
                        break;
                    }
                }
            }
            return isBrowserCompatible;
        });
    });

    return isBrowserCompatible;

}

希望有人能指出我正确的方向。我主要是看看实际功能是否可以按照我所拥有的方式合法地编写，如果不能，如何去做，逻辑等等。温柔一点……

Answer 1

您可以使用 ua-parser-js用于解析用户代理字符串的包。

var parser = require("ua-parser-js");
// somewhere within the server request-response cycle
// you get user-agent header 
var ua = parser(req.headers["user-agent"]);
// ua.getBrowser() returns an object with the browser name and version
var details = ua.getBrowser();
// e.g. returns { name: "Chrome", version: "52.0.2743.116" }
// details.version.split(".") will return an array of strings
// ["52", "0", "2743", "116"] you are only interest in the [0] element
var browser = details.name;
var version = parseInt(details.version.split(".")[0]); // 52

有了浏览器和版本数据，您就可以查询数据库了。

// var mysql = require('mysql');
// etc. etc.

connection.query("SELECT * FROM BrowserTable WHERE BrowserName = ?", [browser],
function(error, rows) {
  // handle error
  if (error) {
    throw error;
  }
  // process the results returned
  var isCompatible = false;
  for (var i = 0; i < rows[0].length; i++) {
    var dbVersion = parseInt(rows[0][i].VersionNumber);
    if ( ((rows[0][i].bitGreaterVersions == 'True') && (dbVersion < version))
    || (dbVersion == version) ) {
      isCompatible = true;
      break;
    }
  }
  return isCompatible;
});