代码拉取完成,页面将自动刷新
#functional sql functional sql
这个是某个网站上的一道题。 虽然是一道题,但却有非常严谨的测试case
我的源码
function query(){
var wheres = []
var select = -1
var from = -1
var orderBy = -1
var groupBy = -1
var havings = []
function selectfn(fn){
if(select == -1){
select = fn
}else{
throw new Error('Duplicate SELECT')
}
}
function fromfn(xs,ys){
if(from == -1){
if(ys == null){
from = xs.concat([])
}else{
var r = []
for(var i=0;i<xs.length;i++){
for(var j=0;j<ys.length;j++){
r.push([xs[i],ys[j]])
}
}
from = r
}
}else{
throw new Error('Duplicate FROM')
}
}
function wherefn(){
var argfn = [].slice.apply(arguments)
wheres.push(function(a){
return anyPass(argfn,a)
})
}
function orderByfn(fn){
if(orderBy == -1){
orderBy = fn
}else{
throw new Error('Duplicate ORDERBY')
}
}
function havingfn(fn){
var argfn = [].slice.apply(arguments)
havings.push(function(a){
return anyPass(argfn,a)
})
}
function anyPass(fns,a){
if(fns.length == 0) return false
else return fns[0](a) ? true : anyPass(fns.slice(1),a)
}
function allPass(fns,a){
if(fns.length==0) return true
else return fns[0](a) ? allPass(fns.slice(1),a) : false
}
function groupByfn(fn){
if(groupBy == -1){
groupBy = [].slice.apply(arguments)
}else{
throw new Error('Duplicate GROUPBY')
}
}
function groupByItem(gys,xs){
if(gys.length == 0) return xs
else void null
var r = []
var groupBy = gys[0]
xs.forEach(a=>{
var ritem = null
r.forEach(ra=>{
if(ra[0] == groupBy(a)){
ritem = ra
}else{
void null
}
})
if(ritem){
ritem[1].push(a)
}else{
r.push([groupBy(a),[a]])
}
})
return r.map(a=>[a[0],...[groupByItem(gys.slice(1),a[1])]])
}
function execute(){
from = from == -1 ? [] : from
if(wheres.length != 0){
from = from.filter(function(a){
return allPass(wheres,a)
})
}
if(groupBy != -1&&groupBy != null){
from = groupByItem(groupBy,from)
}
if(havings.length != 0){
from = from.filter(function(a){
return allPass(havings,a)
})
}
if(select != -1&&select != null){
from = from.map(select)
}
if(orderBy != -1&&orderBy != null){
from = from.sort(orderBy)
}
return from
}
function bindthis(fn){
return function(){
fn.apply(null,[].slice.apply(arguments))
return this
}
}
return {
select:bindthis(selectfn),
from:bindthis(fromfn),
where:bindthis(wherefn),
orderBy:bindthis(orderByfn),
groupBy:bindthis(groupByfn),
having:bindthis(havingfn),
execute:execute
}
}
测试case
Test.describe("SQL tests", function() {
Test.it("Basic SELECT tests", function() {
var numbers = [1, 2, 3];
Test.assertSimilar(query().select().from(numbers).execute(), numbers);
Test.assertSimilar(query().select().execute(), [], 'No FROM clause produces empty array');
Test.assertSimilar(query().from(numbers).execute(), numbers, 'SELECT can be omited');
Test.assertSimilar(query().execute(), []);
Test.assertSimilar(query().from(numbers).select().execute(), numbers, 'The order does not matter');
});
Test.it("Basic SELECT and WHERE over objects", function() {
var persons = [
{name: 'Peter', profession: 'teacher', age: 20, maritalStatus: 'married'},
{name: 'Michael', profession: 'teacher', age: 50, maritalStatus: 'single'},
{name: 'Peter', profession: 'teacher', age: 20, maritalStatus: 'married'},
{name: 'Anna', profession: 'scientific', age: 20, maritalStatus: 'married'},
{name: 'Rose', profession: 'scientific', age: 50, maritalStatus: 'married'},
{name: 'Anna', profession: 'scientific', age: 20, maritalStatus: 'single'},
{name: 'Anna', profession: 'politician', age: 50, maritalStatus: 'married'}
];
Test.assertSimilar(query().select().from(persons).execute(), persons);
function profession(person) {
return person.profession;
}
//SELECT profession FROM persons
Test.assertSimilar(query().select(profession).from(persons).execute(), ["teacher","teacher","teacher","scientific","scientific","scientific","politician"]);
Test.assertSimilar(query().select(profession).execute(), [], 'No FROM clause produces empty array');
function isTeacher(person) {
return person.profession === 'teacher';
}
//SELECT profession FROM persons WHERE profession="teacher"
Test.assertSimilar(query().select(profession).from(persons).where(isTeacher).execute(), ["teacher", "teacher", "teacher"]);
//SELECT * FROM persons WHERE profession="teacher"
Test.assertSimilar(query().from(persons).where(isTeacher).execute(), persons.slice(0, 3));
function name(person) {
return person.name;
}
//SELECT name FROM persons WHERE profession="teacher"
Test.assertSimilar(query().select(name).from(persons).where(isTeacher).execute(), ["Peter", "Michael", "Peter"]);
Test.assertSimilar(query().where(isTeacher).from(persons).select(name).execute(), ["Peter", "Michael", "Peter"]);
});
Test.it('GROUP BY tests', function() {
var persons = [
{name: 'Peter', profession: 'teacher', age: 20, maritalStatus: 'married'},
{name: 'Michael', profession: 'teacher', age: 50, maritalStatus: 'single'},
{name: 'Peter', profession: 'teacher', age: 20, maritalStatus: 'married'},
{name: 'Anna', profession: 'scientific', age: 20, maritalStatus: 'married'},
{name: 'Rose', profession: 'scientific', age: 50, maritalStatus: 'married'},
{name: 'Anna', profession: 'scientific', age: 20, maritalStatus: 'single'},
{name: 'Anna', profession: 'politician', age: 50, maritalStatus: 'married'}
];
function profession(person) {
return person.profession;
}
//SELECT * FROM persons GROUPBY profession <- Bad in SQL but possible in JavaScript
Test.assertSimilar(query().select().from(persons).groupBy(profession).execute(), [["teacher",[{"name":"Peter","profession":"teacher","age":20,"maritalStatus":"married"},{"name":"Michael","profession":"teacher","age":50,"maritalStatus":"single"},{"name":"Peter","profession":"teacher","age":20,"maritalStatus":"married"}]],["scientific",[{"name":"Anna","profession":"scientific","age":20,"maritalStatus":"married"},{"name":"Rose","profession":"scientific","age":50,"maritalStatus":"married"},{"name":"Anna","profession":"scientific","age":20,"maritalStatus":"single"}]],["politician",[{"name":"Anna","profession":"politician","age":50,"maritalStatus":"married"}]]]);
function isTeacher(person) {
return person.profession === 'teacher';
}
//SELECT * FROM persons WHERE profession='teacher' GROUPBY profession
Test.assertSimilar(query().select().from(persons).where(isTeacher).groupBy(profession).execute(), [["teacher",[{"name":"Peter","profession":"teacher","age":20,"maritalStatus":"married"},{"name":"Michael","profession":"teacher","age":50,"maritalStatus":"single"},{"name":"Peter","profession":"teacher","age":20,"maritalStatus":"married"}]]]);
function professionGroup(group) {
return group[0];
}
//SELECT profession FROM persons GROUPBY profession
Test.assertSimilar(query().select(professionGroup).from(persons).groupBy(profession).execute(), ["teacher","scientific","politician"]);
function name(person) {
return person.name;
}
//SELECT * FROM persons WHERE profession='teacher' GROUPBY profession, name
Test.assertSimilar(query().select().from(persons).groupBy(profession, name).execute(), [["teacher",[["Peter",[{"name":"Peter","profession":"teacher","age":20,"maritalStatus":"married"},{"name":"Peter","profession":"teacher","age":20,"maritalStatus":"married"}]],["Michael",[{"name":"Michael","profession":"teacher","age":50,"maritalStatus":"single"}]]]],["scientific",[["Anna",[{"name":"Anna","profession":"scientific","age":20,"maritalStatus":"married"},{"name":"Anna","profession":"scientific","age":20,"maritalStatus":"single"}]],["Rose",[{"name":"Rose","profession":"scientific","age":50,"maritalStatus":"married"}]]]],["politician",[["Anna",[{"name":"Anna","profession":"politician","age":50,"maritalStatus":"married"}]]]]]
);
function age(person) {
return person.age;
}
function maritalStatus(person) {
return person.maritalStatus;
}
//SELECT * FROM persons WHERE profession='teacher' GROUPBY profession, name, age
Test.assertSimilar(query().select().from(persons).groupBy(profession, name, age, maritalStatus).execute(), [["teacher",[["Peter",[[20,[["married",[{"name":"Peter","profession":"teacher","age":20,"maritalStatus":"married"},{"name":"Peter","profession":"teacher","age":20,"maritalStatus":"married"}]]]]]],["Michael",[[50,[["single",[{"name":"Michael","profession":"teacher","age":50,"maritalStatus":"single"}]]]]]]]],["scientific",[["Anna",[[20,[["married",[{"name":"Anna","profession":"scientific","age":20,"maritalStatus":"married"}]],["single",[{"name":"Anna","profession":"scientific","age":20,"maritalStatus":"single"}]]]]]],["Rose",[[50,[["married",[{"name":"Rose","profession":"scientific","age":50,"maritalStatus":"married"}]]]]]]]],["politician",[["Anna",[[50,[["married",[{"name":"Anna","profession":"politician","age":50,"maritalStatus":"married"}]]]]]]]]]);
function professionCount(group) {
return [group[0], group[1].length];
}
//SELECT profession, count(profession) FROM persons GROUPBY profession
Test.assertSimilar(query().select(professionCount).from(persons).groupBy(profession).execute(), [["teacher",3],["scientific",3],["politician",1]]);
function naturalCompare(value1, value2) {
if (value1 < value2) {
return -1;
} else if (value1 > value2) {
return 1;
} else {
return 0;
}
}
//SELECT profession, count(profession) FROM persons GROUPBY profession ORDER BY profession
Test.assertSimilar(query().select(professionCount).from(persons).groupBy(profession).orderBy(naturalCompare).execute(), [["politician",1],["scientific",3],["teacher",3]]);
});
Test.it('Numbers tests', function() {
function isEven(number) {
return number % 2 === 0;
}
function parity(number) {
return isEven(number) ? 'even' : 'odd';
}
function isPrime(number) {
if (number < 2) {
return false;
}
var divisor = 2;
for(; number % divisor !== 0; divisor++);
return divisor === number;
}
function prime(number) {
return isPrime(number) ? 'prime' : 'divisible';
}
var numbers = [1, 2, 3, 4, 5, 6, 7, 8, 9];
//SELECT * FROM numbers
Test.assertSimilar(query().select().from(numbers).execute(), numbers);
//SELECT * FROM numbers GROUP BY parity
Test.assertSimilar(query().select().from(numbers).groupBy(parity).execute(), [["odd",[1,3,5,7,9]],["even",[2,4,6,8]]]);
//SELECT * FROM numbers GROUP BY parity, isPrime
Test.assertSimilar(query().select().from(numbers).groupBy(parity, prime).execute(), [["odd",[["divisible",[1,9]],["prime",[3,5,7]]]],["even",[["prime",[2]],["divisible",[4,6,8]]]]]);
function odd(group) {
return group[0] === 'odd';
}
//SELECT * FROM numbers GROUP BY parity HAVING
Test.assertSimilar(query().select().from(numbers).groupBy(parity).having(odd).execute(), [["odd",[1,3,5,7,9]]]);
function descendentCompare(number1, number2) {
return number2 - number1;
}
//SELECT * FROM numbers ORDER BY value DESC
Test.assertSimilar(query().select().from(numbers).orderBy(descendentCompare).execute(), [9,8,7,6,5,4,3,2,1]);
function lessThan3(number) {
return number < 3;
}
function greaterThan4(number) {
return number > 4;
}
//SELECT * FROM number WHERE number < 3 OR number > 4
Test.assertSimilar(query().select().from(numbers).where(lessThan3, greaterThan4).execute(), [1,2,5,6,7,8,9]);
});
Test.it('Frequency tests', function() {
var persons = [
['Peter', 3],
['Anna', 4],
['Peter', 7],
['Michael', 10]
];
function nameGrouping(person) {
return person[0];
}
function sumValues(value) {
return [value[0], value[1].reduce(function(result, person) {
return result + person[1];
}, 0)];
}
function naturalCompare(value1, value2) {
if (value1 < value2) {
return -1;
} else if (value1 > value2) {
return 1;
} else {
return 0;
}
}
//SELECT name, sum(value) FROM persons ORDER BY naturalCompare GROUP BY nameGrouping
Test.assertSimilar(query().select(sumValues).from(persons).orderBy(naturalCompare).groupBy(nameGrouping).execute(), [["Anna",4],["Michael",10],["Peter",10]]);
var numbers = [1, 2, 1, 3, 5, 6, 1, 2, 5, 6];
function id(value) {
return value;
}
function frequency(group) {
return { value: group[0], frequency: group[1].length };
}
//SELECT number, count(number) FROM numbers GROUP BY number
Test.assertSimilar(query().select(frequency).from(numbers).groupBy(id).execute(), [{"value":1,"frequency":3},{"value":2,"frequency":2},{"value":3,"frequency":1},{"value":5,"frequency":2},{"value":6,"frequency":2}]);
function greatThan1(group) {
return group[1].length > 1;
}
function isPair(group) {
return group[0] % 2 === 0;
}
//SELECT number, count(number) FROM numbers GROUP BY number HAVING count(number) > 1 AND isPair(number)
Test.assertSimilar(query().select(frequency).from(numbers).groupBy(id).having(greatThan1).having(isPair).execute(), [{"value":2,"frequency":2},{"value":6,"frequency":2}]);
});
Test.it('join tests', function() {
var teachers = [
{
teacherId: '1',
teacherName: 'Peter'
},
{
teacherId: '2',
teacherName: 'Anna'
}
];
var students = [
{
studentName: 'Michael',
tutor: '1'
},
{
studentName: 'Rose',
tutor: '2'
}
];
function teacherJoin(join) {
return join[0].teacherId === join[1].tutor;
}
function student(join) {
return {studentName: join[1].studentName, teacherName: join[0].teacherName};
}
//SELECT studentName, teacherName FROM teachers, students WHERE teachers.teacherId = students.tutor
Test.assertSimilar(query().select(student).from(teachers, students).where(teacherJoin).execute(), [{"studentName":"Michael","teacherName":"Peter"},{"studentName":"Rose","teacherName":"Anna"}]);
var numbers1 = [1, 2];
var numbers2 = [4, 5];
Test.assertSimilar(query().select().from(numbers1, numbers2).execute(), [[1,4],[1,5],[2,4],[2,5]]);
function tutor1(join) {
return join[1].tutor === "1";
}
//SELECT studentName, teacherName FROM teachers, students WHERE teachers.teacherId = students.tutor AND tutor = 1
Test.assertSimilar(query().select(student).from(teachers, students).where(teacherJoin).where(tutor1).execute(), [{"studentName":"Michael","teacherName":"Peter"}]);
});
Test.it('Duplication exception tests', function() {
function checkError(fn, duplicate) {
try {
fn();
Test.expect(false, 'An error should be throw');
} catch (e) {
Test.expect(e instanceof Error);
Test.assertEquals(e.message, 'Duplicate ' + duplicate);
}
}
function id(value) {
return value;
}
checkError(function() { query().select().select().execute(); }, 'SELECT');
checkError(function() { query().select().from([]).select().execute(); }, 'SELECT');
checkError(function() { query().select().from([]).from([]).execute(); }, 'FROM');
checkError(function() { query().select().from([]).orderBy(id).orderBy(id).execute(); }, 'ORDERBY');
checkError(function() { query().select().groupBy(id).from([]).groupBy(id).execute(); }, 'GROUPBY');
});
});
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。