具有参数的AlaSQL用户定义函数

Question

我是AlaSQL的新手：

alasql('CREATE TABLE cnames(domain STRING, cname STRING)');
alasql('CREATE TABLE domains(domain STRING, ip_addr STRING)');

alasql('INSERT INTO domains VALUES ("fred.com.au","192.168.0.1")');
alasql('INSERT INTO cnames VALUES ("www.fred.com.au","fred.com.au")');
alasql('INSERT INTO cnames VALUES ("mail.fred.com.au","www.fred.com.au")');

alasql.fn.getCname = function(domainName) {
  console.log('domainName ', domainName);
    return alasql('SELECT cname FROM cnames WHERE domain = ?',[domainName]);

}

var res = alasql('SELECT * FROM cnames WHERE getCname("www.fred.com.au")');    
document.getElementById('res').textContent = JSON.stringify(res);

我有一个上面的JSFiddle：https://jsfiddle.net/u4drqxaw/

我有问题的项目有：

1)我意识到我做错了什么，因为我的getCname函数因为我的SELECT * FROM cnames WHERE getCname("www.fred.com.au")被调用了两次

在alaSQL中，如何使用参数调用函数alasql.fn.getCname，例如：

var res = alasql('getCname("www.fred.com.au")');

2)一旦获得了Q1的解决方案，如何引用传递给函数的参数，以便在查询(即[domainName] )中使用该参数？

最终的结果是，我希望能够用参数www.fred.com.au来调用我的用户定义的www.fred.com.au函数，而这个参数又应该返回给我这个cnames.domain值的cname表中的一条记录。

Answer 1

成功地实现了这一点，并更新了我的JSFiddle以反映我所需的解决方案。

见这里：

alasql('CREATE TABLE cnames(domain STRING, cname STRING)');
alasql('CREATE TABLE domains(domain STRING, ip_addr STRING)');

alasql('INSERT INTO domains VALUES ("fred.com.au","192.168.0.1")');
alasql('INSERT INTO domains VALUES ("barney.com.au","192.168.2.3")');
alasql('INSERT INTO domains VALUES ("mail1.fred.com.au","192.168.9.9")');
alasql('INSERT INTO cnames VALUES ("www.fred.com.au","fred.com.au")');
alasql('INSERT INTO cnames VALUES ("mail.fred.com.au","www.fred.com.au")');
alasql('INSERT INTO cnames VALUES ("fred.com.au","barney.com.au")');

alasql.fn.getCname = function(domainName) {
    return alasql('SELECT cname FROM cnames WHERE domain = ?',[domainName]);  
}

alasql.fn.processCname = function(cnameVal) {
    return alasql('SELECT getCname(?) [cname]',[cnameVal]);
}

alasql.fn.domainInfo = function(theDomain, theCname) {
    return alasql('SELECT domain, ip_addr, "'+theCname+'" [cname] FROM domains WHERE domain = ?',[theDomain]);  
}

let rec = alasql('SELECT getCname("mail.fred.com.au") [cname]');
let initialLength = rec[0]["cname"].length;

if (initialLength > 0) {
  let cnameValue = rec[0]["cname"][0].cname;
  let found = true;
  while (found) { 
    rec = alasql('SELECT getCname(?) [cname]',[cnameValue]);
    let recLength = rec[0]["cname"].length;
    if (recLength > 0) {
      cnameValue = rec[0]["cname"][0].cname;
    } else {
      found = false;
    }
  }

    let res = alasql('SELECT domainInfo(?,?) [result]',[cnameValue,cnameValue]);
    document.getElementById('res').textContent = JSON.stringify(res);
} else {
        let res = alasql('SELECT domainInfo("mail.fred.com.au", "") [result]');
        document.getElementById('res').textContent = JSON.stringify(res);
}

JSFiddle解