-
Notifications
You must be signed in to change notification settings - Fork 0
/
README
193 lines (166 loc) · 6.5 KB
/
README
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
// Copyright (C) 2011 by Ross Korsky
// This software is released under the MIT License that follows
////////////////////////////////////////////////////////////////////////////////
// Permission is hereby granted, free of charge, to any person obtaining a copy
// of this software and associated documentation files (the "Software"), to deal
// in the Software without restriction, including without limitation the rights
// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
// copies of the Software, and to permit persons to whom the Software is
// furnished to do so, subject to the following conditions:
//
// The above copyright notice and this permission notice shall be included in
// all copies or substantial portions of the Software.
//
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
// AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
// LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
// OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
// THE SOFTWARE
////////////////////////////////////////////////////////////////////////////////
This project contains a set of JavaScript data manipulating operations. The most
notable of which is used to construct and compile SQL like 'FROM .. JOIN ..'
statements into a javascript function which can then be used once, or, repeatedly
on similar data sets (see DataManip.Join).
See the included unit tests and HTML JavaScript docs to get started.
Highlights:
Yes, it parses SQL like strings.
Yes, you can self join or perform multiple joins against a single set of data.
Yes, it uses lookups(indexes) for optimization and yes if a join operation
uses the same index on the same input data multiple times indexes are
reused.
Yes, it actually 'compiles' reasonably minified JS functions so it's blazing
fast (IMO).
No, or is it Yes, there are absolutely no anonymous functions created by
calling the generated join function.
Yes, it 'late binds' to your actual data so you can create the join functions
without yet having any data to join on!
Yes, you can perform SQL SELECT and WHERE operations (via your selector
function).
Yes, last but not least, there are unit tests! These also double as working
examples so check them out too.
--------------------------------------------------------------------------------
DataManip.Join: Example 1
--------------------------------------------------------------------------------
This is a very simple example (no I don't normally start my variable names with
'the').
var join = new DataManip.Join();
join.parse( "FROM arg0 AS foo LEFT JOIN arg1 AS bar ON bar.id = foo.bar_id")
join.selector = function() {
return { a: this.foo.a, b: this.bar.b };
};
join.compile();
var theFoo = [ { a: 1, bar_id: 2 }, {a: 2, bar_id: 3 } ];
var theBar = [ { id: 2, b: 10 }, { id: 3, b: 30 } ];
var result = join.exec( theFoo, theBar );
result;
>>>
[
{ a: 1, b: 10 },
{ a: 2, b: 30 }
]
--------------------------------------------------------------------------------
DataManip.Join: Example 2
--------------------------------------------------------------------------------
Contrived 3 way mapping table example
Illustrating
- INNER JOIN behavior (student id 4 does not exist)
- setting selector function after compiling
- conditional selection of records
- generating a result set of strings instead of 'objects'
var grades = [
{letter:"A", id:90},
{letter:"B", id:80},
{letter:"C", id:70},
{letter:"D", id:60},
{letter:"E", id:50},
{letter:"F", id:40}
];
var students = [
{id:1,name:"Fred"},
{id:2,name:"Sam"},
{id:3,name:"Bob"}
];
var tests = [
{id:1,name:"Chapters 1-8"},
{id:2,name:"Chapters 9-16"},
{id:3,name:"Chapters 17-23"}
];
var scores = [
{student_id:1, test_id:1, grade_id:50, score: 58 },
{student_id:2, test_id:2, grade_id:60, score: 67 },
{student_id:1, test_id:2, grade_id:90, score: 98 },
{student_id:2, test_id:1, grade_id:70, score: 71 },
{student_id:1, test_id:3, grade_id:90, score: 93 },
{student_id:4, test_id:3, grade_id:40, score: 49 }
];
var join = new DataManip.Join();
join.parse(
"FROM arg0 AS score " +
"INNER JOIN arg1 AS grade ON grade.id = score.grade_id " +
"INNER JOIN arg2 AS student ON student.id = score.student_id " +
"INNER JOIN arg3 AS test ON test.id = score.test_id "
);
join.compile();
join.exec.external.selector = function(){
//WHERE
if ( this.score.score < 70 ) {
//SELECT
return this.student.name
+ " received a low grade of " + this.score.score
+ "(" + this.grade.letter + ")"
+ " on test '" + this.test.name + "'";
} else {
return null;
}
}
var records = join.exec( scores, grades, students, tests );
records.join( "\n" );
>>>
Fred received a low grade of 58(E) on test 'Chapters 1-8'
Sam received a low grade of 67(D) on test 'Chapters 9-16'
--------------------------------------------------------------------------------
DataManip.pivot: Example 3
--------------------------------------------------------------------------------
Pivots records on the given field (member). Useful for creating lookups/indexes
var data = [
{ id: 1, name: 'Bob', score: 92 },
{ id: 2, name: 'Sam', score: 68 },
{ id: 3, name: 'Bob', score: 89 },
{ id: 4, name: 'Sam', score: 72 },
{ id: 5, name: 'Sam', score: 81 }
];
DataManip.pivot( data, 'name' );
>>>
{
Bob: [
{ id: 1, name: 'Bob', score: 92 },
{ id: 3, name: 'Bob', score: 89 }
],
Sam: [
{ id: 2, name: 'Sam', score: 68 },
{ id: 4, name: 'Sam', score: 72 },
{ id: 5, name: 'Sam', score: 81 }
]
]
--------------------------------------------------------------------------------
DataManip.filter: Example 4
--------------------------------------------------------------------------------
Filters records based on the given field name and value.
A record must both have a field with the given name and that fields value must
match the given value to be selected. The data structure does not need to be
consistent (records can have missing or extra fields).
var data = [
{ id: 1, name: 'Bob', score: 92 },
{ id: 2, name: 'Sam', score: 68 },
{ id: 3, name: 'Bob', score: 89 },
{ id: 4, name: 'Sam', score: 72 },
{ id: 5, name: 'Sam', score: 81 }
];
DataManip.filter( data, 'name', 'Bob' );
>>>
[
{ id: 1, name: 'Bob', score: 92 },
{ id: 3, name: 'Bob', score: 89 }
];