-
Notifications
You must be signed in to change notification settings - Fork 53
/
rbql_client.html
616 lines (540 loc) · 22.7 KB
/
rbql_client.html
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
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<style>
:root {
--inputwidth: 80%;
}
html * {
font-size: 16px !important;
}
body {
background-color: var(--vscode-editor-background);
color: var(--vscode-editor-foreground);
}
th {
padding: 3px 8px;
border-right: 1px solid;
border-top: 1px solid;
border-bottom: 1px solid;
border-right-color: var(--vscode-window-activeBorder);
border-top-color: var(--vscode-window-activeBorder);
border-bottom-color: var(--vscode-window-activeBorder);
position: sticky;
top: 0;
background-color: var(--vscode-editor-background);
white-space: pre;
}
td {
padding: 3px 8px;
background-color: var(--vscode-editor-background);
border-right: 1px solid;
border-bottom: 1px solid;
border-right-color: var(--vscode-window-activeBorder);
border-bottom-color: var(--vscode-window-activeBorder);
}
table {
display: block;
white-space: nowrap;
border-collapse: separate;
background-color: var(--vscode-editor-background);
}
table th:first-child, table td:first-child {
/* Apply a left border on the first <td> or <th> in a row */
border-left: 1px solid;
border-left-color: var(--vscode-window-activeBorder);
}
.padded_label {
padding: 5px;
}
.code_sample {
font-style: italic;
}
#rbql_console {
margin-top: 20px;
}
.checkbox_input {
background-color: var(--vscode-input-background);
color: var(--vscode-input-foreground);
margin-bottom: 6px;
font-size: 14px !important;
font-family: Consolas, Monaco, monospace;
}
.select_input {
background-color: var(--vscode-input-background);
color: var(--vscode-input-foreground);
margin-bottom: 6px;
width: 300px;
font-size: 14px !important;
font-family: Consolas, Monaco, monospace;
}
#rbql_input {
margin: 0px;
height: 24px !important;
font-family: Consolas, Monaco, monospace;
font-size: 14px !important;
padding-left: 2px;
background-color: var(--vscode-input-background);
caret-color: var(--vscode-input-foreground);
color: var(--vscode-input-foreground);
display: inline-block;
box-sizing: border-box; /* See css-submit-button-looks-smaller-than-text-input-and-textarea */
vertical-align:middle;
width: var(--inputwidth);
}
#toggle_history_btn {
margin: 0px;
width: 28px;
background-color: var(--vscode-button-secondaryBackground);
color: var(--vscode-button-secondaryForeground);
height: 24px !important;
padding: 0px;
display: inline-block;
border-width: 0px;
outline:none;
vertical-align:middle;
box-sizing: border-box;
}
#toggle_history_btn:hover {
background-color: var(--vscode-button-secondaryHoverBackground);
}
#rbql_run_btn {
margin: 0px;
width: 70px;
background-color: var(--vscode-button-background);
color: var(--vscode-button-foreground);
height: 24px !important;
padding: 0px;
display: inline-block;
outline:none;
vertical-align:middle;
box-sizing: border-box;
border-width: 0px;
}
#rbql_run_btn:hover {
background-color: var(--vscode-button-hoverBackground);
}
#help_btn {
margin: 0px;
width: 28px;
background-color: var(--vscode-button-secondaryBackground);
color: var(--vscode-button-secondaryForeground);
height: 24px !important;
padding: 0px;
display: inline-block;
outline:none;
vertical-align:middle;
box-sizing: border-box;
border-width: 0px;
}
#help_btn:hover {
background-color: var(--vscode-button-secondaryHoverBackground);
}
#clear_history_btn {
margin: 0px;
background-color: var(--vscode-button-background);
color: var(--vscode-button-foreground);
height: 24px !important;
padding: 0px 5px 0px 5px;
display: inline-block;
float: right;
outline:none;
vertical-align:middle;
border:none;
}
#clear_history_btn:hover {
background-color: var(--vscode-button-hoverBackground);
}
.history_button {
margin: 0px;
width: 100%;
background-color: var(--vscode-button-secondaryBackground);
color: var(--vscode-button-secondaryForeground);
height: 24px !important;
font-family: Consolas, Monaco, monospace;
font-size: 14px !important;
border-width: 0px;
outline: none;
display: block;
text-align: left;
padding-left: 2px;
white-space: nowrap;
}
.history_button:hover {
background-color: var(--vscode-button-secondaryHoverBackground);
}
.navigation_button {
background-color: var(--vscode-button-secondaryBackground);
color: var(--vscode-button-secondaryForeground);
width: 28px;
font-size: 18px !important;
height: 28px;
font-weight: bold;
border-width: 0px;
}
.navigation_button:hover {
background-color: var(--vscode-button-secondaryHoverBackground);
}
#udf_button {
background-color: var(--vscode-button-secondaryBackground);
color: var(--vscode-button-secondaryForeground);
font-size: 14px !important;
border-width: 0px;
height: 24px !important;
outline:none;
vertical-align:middle;
padding: 0px;
padding-left: 12px;
padding-right: 12px;
margin-top: 8px;
}
#udf_button:hover {
background-color: var(--vscode-button-secondaryHoverBackground);
}
#rbql_help {
width: 80%;
top: 10%;
left: 10%;
height: 80%;
z-index: 1000000;
position: fixed;
display: none;
overflow: scroll;
}
#close_help {
height: 28px;
width: 28px;
top: 10%;
left: 90%;
z-index: 1000010;
position: fixed;
font-size: 18px !important;
background-color: var(--vscode-button-background);
color: var(--vscode-button-foreground);
font-weight: bold;
display: none;
border-width: 0px;
}
#close_help:hover {
background-color: var(--vscode-button-hoverBackground);
}
#rbql_help_text {
margin-left: 2px;
}
#query_history {
font-family: Consolas, Monaco, monospace;
font-size: 14px !important;
width: var(--inputwidth);
z-index: 1000000;
background-color: var(--vscode-notifications-background);
color: var(--vscode-notifications-foreground);
position: fixed;
display: none;
border-top: 1px solid;
border-left: 1px solid;
border-right: 1px solid;
border-color: var(--vscode-focusBorder);
}
#query_suggest{
width: 200px;
z-index: 1000010;
background-color: var(--vscode-notifications-background);
color: var(--vscode-notifications-foreground);
position: fixed;
display: none;
border-top: 1px solid;
border-left: 1px solid;
border-right: 1px solid;
border-color: var(--vscode-focusBorder);
max-height: 300px;
white-space: nowrap;
overflow-y: auto;
overflow-x: hidden;
}
#history_entries {
font-family: Consolas, Monaco, monospace;
font-size: 14px !important;
max-height: 300px;
margin: 0px;
overflow-x: auto;
overflow-y: auto;
white-space: nowrap;
}
#query_history_header {
font-size: 18px !important;
height: 24px;
margin: 0px;
}
#table_window {
max-width: 95%;
max-height: 350px;
overflow: scroll;
width:fit-content;
/*border: 2px solid black;*/
}
#rbql_error_message {
width: 50%;
top: 20%;
left: 25%;
z-index: 1000000;
background-color: var(--vscode-notifications-background);
color: var(--vscode-notifications-foreground);
position: fixed;
display: none;
}
#rbql_error_message_header {
background-color: var(--vscode-inputValidation-errorBackground);
color: var(--vscode-inputValidation-errorForeground);
font-size: 18px !important;
height: 26px;
font-weight: bold;
}
#ack_error {
width: 70px;
background-color: var(--vscode-button-background);
color: var(--vscode-button-foreground);
display: inline-block;
border:none;
font-size: 18px !important;
height: 28px;
}
#ack_error:hover {
background-color: var(--vscode-button-hoverBackground);
}
#error_message_details {
height: 100px;
font-family: Consolas, Monaco, monospace;
overflow: auto;
border: 1px solid;
border-color: var(--vscode-focusBorder);
white-space: pre;
margin: 5px;
background-color: var(--vscode-notifications-background);
color: var(--vscode-notifications-foreground);
}
[type="checkbox"]
{
margin-left: 0px;
vertical-align:middle;
height: 20px;
width: 20px;
}
</style>
<script src="contrib/textarea-caret-position/index.js"></script>
<script src="rbql_suggest.js"></script>
<script src="rbql_client.js"></script>
</head>
<body>
<div id="rbql_console">
<div id="table_group">
<div id="table_window">
<table id="preview_table">
</table>
</div>
<div id="navig_bar">
<button class="navigation_button" title="Begin" id="go_begin">«</button>
<button class="navigation_button" title="Back" id="go_backward">‹</button>
<button class="navigation_button" title="Forward" id="go_forward">›</button>
<button class="navigation_button" title="End" id="go_end">»</button>
</div>
</div>
<div id="query_group" style="margin-top: 10px;">
<!-- Do not remove the weird comments below, we need them to avoid spaces between inline elements, see https://css-tricks.com/fighting-the-space-between-inline-block-elements/ -->
<input type="text" id="rbql_input" placeholder="Example: SELECT a1, a2 WHERE a2 != 'foobar' ORDER BY a1 LIMIT 10" autofocus><!--
--><button id="toggle_history_btn" title="Query history">▲</button><!--
--><button id="rbql_run_btn" title="Run query and open result set in a new tab">Run</button><!--
--><button id="help_btn" title="Help">?</button>
</div>
<h3 style="margin-bottom: 6px; margin-top: 10px;">⚙ Query settings</h3>
<div style="margin-left: 30px;">
<div id="with_headers_section">
<label style="word-wrap:break-word" title="Indicate that the input (and join) table has header in the first line"><input class="checkbox_input" id="with_headers" type="checkbox"/>Input table has header</label>
</div>
<div>
<div>
<span class="select_info">Output format</span>
</div>
<select class="select_input" id="select_output_format" title="Output table format">
<option class="select_input" value="input">Same as input (Default)</option>
<option class="select_input" value="csv">CSV</option>
<option class="select_input" value="tsv">TSV</option>
</select>
</div>
<div>
<div>
<span class="select_info">Encoding</span>
</div>
<select class="select_input" id="select_encoding" title="Input and output encoding">
<option class="select_input" value="utf-8">UTF-8</option>
<option class="select_input" value="latin-1">Binary/Latin-1</option>
</select>
</div>
<div>
<div>
<span class="select_info">Backend language for RBQL</span>
</div>
<select class="select_input" id="select_backend_language">
<option class="select_input" value="js">JavaScript</option>
<option class="select_input" value="pyhon">Python (Requires python installed)</option>
</select>
</div>
<div>
<button id="udf_button" title="Edit UDF (User Defined Functions) - will open a new tab with UDF file">Edit UDF...</button>
</div>
</div>
</div>
<div id="query_history">
<div id="query_history_header">
<span class="padded_label">Query history</span>
<button id="clear_history_btn">Clear history</button>
</div>
<div id="history_entries">
</div>
</div>
<div id="query_suggest">
</div>
<div id="rbql_error_message">
<div id="rbql_error_message_header">
<span class="padded_label">⚠ Error while executing RBQL query!</span>
</div>
<br>
<div>
<span class="padded_label" id="error_message_header"></span>
</div>
<br>
<div>
<span class="padded_label">Details:</span>
<br>
<div id="error_message_details"></div>
</div>
<div style="display: flex; justify-content:center;">
<button id="ack_error">OK</button>
</div>
</div>
<button id="close_help">X</button>
<div id="rbql_help">
<a href="https://rbql.org"><img src="rbql_logo.svg" alt="RBQL" width="200px" style="background-color:white;"/></a>
<p><a href="https://github.com/mechatroner/vscode_rainbow_csv/blob/master/rbql_core/README.md#rbql-rainbow-query-language-description">Read RBQL documentation on github</a></p>
<div id="rbql_help_text">
<h1 id="rbql-rainbow-query-language">RBQL: Rainbow Query Language</h1>
<p>RBQL is an eval-based SQL-like query engine for (not only) CSV file processing. It provides SQL-like language that supports SELECT queries with Python or JavaScript expressions. <br />
RBQL is best suited for data transformation, data cleaning, and analytical queries. <br />
RBQL is distributed with CLI apps, text editor plugins, Python and JS libraries. </p>
<h3 id="main-features">Main Features</h3>
<ul>
<li>Use Python or JavaScript expressions inside <em>SELECT</em>, <em>UPDATE</em>, <em>WHERE</em> and <em>ORDER BY</em> statements</li>
<li>Supports multiple input formats</li>
<li>Result set of any query immediately becomes a first-class table on its own</li>
<li>No need to provide FROM statement in the query when the input table is defined by the current context.</li>
<li>Supports all main SQL keywords</li>
<li>Supports aggregate functions and GROUP BY queries</li>
<li>Supports user-defined functions (UDF)</li>
<li>Provides some new useful query modes which traditional SQL engines do not have</li>
<li>Lightweight, dependency-free, works out of the box</li>
</ul>
<h4 id="limitations">Limitations:</h4>
<ul>
<li>RBQL doesn't support nested queries, but they can be emulated with consecutive queries</li>
<li>Number of tables in all JOIN queries is always 2 (input table and join table), use consecutive queries to join 3 or more tables</li>
</ul>
<h3 id="supported-sql-keywords-keywords-are-case-insensitive">Supported SQL Keywords (Keywords are case insensitive)</h3>
<ul>
<li>SELECT</li>
<li>UPDATE</li>
<li>WHERE</li>
<li>ORDER BY … [ DESC | ASC ]</li>
<li>[ LEFT | INNER ] JOIN</li>
<li>DISTINCT</li>
<li>GROUP BY</li>
<li>TOP <em>N</em></li>
<li>LIMIT <em>N</em></li>
<li>AS</li>
</ul>
<p>All keywords have the same meaning as in SQL queries. You can check them <a href="https://www.w3schools.com/sql/default.asp">online</a> </p>
<h3 id="rbql-variables">RBQL variables</h3>
<p>RBQL for CSV files provides the following variables which you can use in your queries:</p>
<ul>
<li><em>a1</em>, <em>a2</em>,…, <em>a{N}</em> <br />
Variable type: <strong>string</strong> <br />
Description: value of i-th field in the current record in input table </li>
<li><em>b1</em>, <em>b2</em>,…, <em>b{N}</em> <br />
Variable type: <strong>string</strong> <br />
Description: value of i-th field in the current record in join table B </li>
<li><em>NR</em> <br />
Variable type: <strong>integer</strong> <br />
Description: Record number (1-based) </li>
<li><em>NF</em> <br />
Variable type: <strong>integer</strong> <br />
Description: Number of fields in the current record </li>
<li><em>a.name</em>, <em>b.Person_age</em>, … <em>a.{Good_alphanumeric_column_name}</em> <br />
Variable type: <strong>string</strong> <br />
Description: Value of the field referenced by it's "name". You can use this notation if the field in the header has a "good" alphanumeric name </li>
<li><em>a["object id"]</em>, <em>a['9.12341234']</em>, <em>b["%$ !! 10 20"]</em> … <em>a["Arbitrary column name!"]</em> <br />
Variable type: <strong>string</strong> <br />
Description: Value of the field referenced by it's "name". You can use this notation to reference fields by arbitrary values in the header</li>
</ul>
<h3 id="update-statement">UPDATE statement</h3>
<p><em>UPDATE</em> query produces a new table where original values are replaced according to the UPDATE expression, so it can also be considered a special type of SELECT query.</p>
<h3 id="aggregate-functions-and-queries">Aggregate functions and queries</h3>
<p>RBQL supports the following aggregate functions, which can also be used with <em>GROUP BY</em> keyword: <br />
<em>COUNT</em>, <em>ARRAY_AGG</em>, <em>MIN</em>, <em>MAX</em>, <em>ANY_VALUE</em>, <em>SUM</em>, <em>AVG</em>, <em>VARIANCE</em>, <em>MEDIAN</em> </p>
<p>Limitation: aggregate functions inside Python (or JS) expressions are not supported. Although you can use expressions inside aggregate functions. <br />
E.g. <code>MAX(float(a1) / 1000)</code> - valid; <code>MAX(a1) / 1000</code> - invalid. <br />
There is a workaround for the limitation above for <em>ARRAY_AGG</em> function which supports an optional parameter - a callback function that can do something with the aggregated array. Example: <br />
<code>SELECT a2, ARRAY_AGG(a1, lambda v: sorted(v)[:5]) GROUP BY a2</code> - Python; <code>SELECT a2, ARRAY_AGG(a1, v => v.sort().slice(0, 5)) GROUP BY a2</code> - JS</p>
<h3 id="join-statements">JOIN statements</h3>
<p>Join table B can be referenced either by its file path or by its name - an arbitrary string which the user should provide before executing the JOIN query. <br />
RBQL supports <em>STRICT LEFT JOIN</em> which is like <em>LEFT JOIN</em>, but generates an error if any key in the left table "A" doesn't have exactly one matching key in the right table "B". <br />
Table B path can be either relative to the working dir, relative to the main table or absolute. <br />
Limitation: <em>JOIN</em> statements can't contain Python/JS expressions and must have the following form: <em><JOIN_KEYWORD> (/path/to/table.tsv | table_name ) ON a… == b… [AND a… == b… [AND … ]]</em></p>
<h3 id="select-except-statement">SELECT EXCEPT statement</h3>
<p>SELECT EXCEPT can be used to select everything except specific columns. E.g. to select everything but columns 2 and 4, run: <code>SELECT * EXCEPT a2, a4</code> <br />
Traditional SQL engines do not support this query mode.</p>
<h3 id="unnest-operator">UNNEST() operator</h3>
<p>UNNEST(list) takes a list/array as an argument and repeats the output record multiple times - one time for each value from the list argument. <br />
Example: <code>SELECT a1, UNNEST(a2.split(';'))</code> </p>
<h3 id="like-function">LIKE() function</h3>
<p>RBQL does not support LIKE operator, instead it provides "like()" function which can be used like this:<br />
<code>SELECT * where like(a1, 'foo%bar')</code></p>
<h3 id="with-header-and-with-noheader-statements">WITH (header) and WITH (noheader) statements</h3>
<p>You can set whether the input (and join) CSV file has a header or not using the environment configuration parameters which could be <code>--with_headers</code> CLI flag or GUI checkbox or something else.<br />
But it is also possible to override this selection directly in the query by adding either <code>WITH (header)</code> or <code>WITH (noheader)</code> statement at the end of the query.<br />
Example: <code>select top 5 NR, * with (header)</code></p>
<h3 id="user-defined-functions-udf">User Defined Functions (UDF)</h3>
<p>RBQL supports User Defined Functions <br />
You can define custom functions and/or import libraries in two special files: </p>
<ul>
<li><code>~/.rbql_init_source.py</code> - for Python</li>
<li><code>~/.rbql_init_source.js</code> - for JavaScript</li>
</ul>
<h2 id="examples-of-rbql-queries">Examples of RBQL queries</h2>
<h4 id="with-python-expressions">With Python expressions</h4>
<ul>
<li><code>SELECT TOP 100 a1, int(a2) * 10, len(a4) WHERE a1 == "Buy" ORDER BY int(a2) DESC</code></li>
<li><code>SELECT a.id, a.weight / 1000 AS weight_kg</code></li>
<li><code>SELECT * ORDER BY random.random()</code> - random sort</li>
<li><code>SELECT len(a.vehicle_price) / 10, a2 WHERE int(a.vehicle_price) < 500 and a['Vehicle type'] in ["car", "plane", "boat"] limit 20</code> - referencing columns by names from header and using Python's "in" to emulate SQL's "in"</li>
<li><code>UPDATE SET a3 = 'NPC' WHERE a3.find('Non-playable character') != -1</code></li>
<li><code>SELECT NR, *</code> - enumerate records, NR is 1-based</li>
<li><code>SELECT * WHERE re.match(".*ab.*", a1) is not None</code> - select entries where first column has "ab" pattern</li>
<li><code>SELECT a1, b1, b2 INNER JOIN ./countries.txt ON a2 == b1 ORDER BY a1, a3</code> - example of join query</li>
<li><code>SELECT MAX(a1), MIN(a1) WHERE a.Name != 'John' GROUP BY a2, a3</code> - example of aggregate query</li>
<li><code>SELECT *a1.split(':')</code> - Using Python3 unpack operator to split one column into many. Do not try this with other SQL engines!</li>
</ul>
<h4 id="with-javascript-expressions">With JavaScript expressions</h4>
<ul>
<li><code>SELECT TOP 100 a1, a2 * 10, a4.length WHERE a1 == "Buy" ORDER BY parseInt(a2) DESC</code></li>
<li><code>SELECT a.id, a.weight / 1000 AS weight_kg</code></li>
<li><code>SELECT * ORDER BY Math.random()</code> - random sort</li>
<li><code>SELECT TOP 20 a.vehicle_price.length / 10, a2 WHERE parseInt(a.vehicle_price) < 500 && ["car", "plane", "boat"].indexOf(a['Vehicle type']) > -1 limit 20</code> - referencing columns by names from header</li>
<li><code>UPDATE SET a3 = 'NPC' WHERE a3.indexOf('Non-playable character') != -1</code></li>
<li><code>SELECT NR, *</code> - enumerate records, NR is 1-based</li>
<li><code>SELECT a1, b1, b2 INNER JOIN ./countries.txt ON a2 == b1 ORDER BY a1, a3</code> - example of join query</li>
<li><code>SELECT MAX(a1), MIN(a1) WHERE a.Name != 'John' GROUP BY a2, a3</code> - example of aggregate query</li>
<li><code>SELECT ...a1.split(':')</code> - Using JS "destructuring assignment" syntax to split one column into many. Do not try this with other SQL engines!</li>
</ul>
</div>
</div>
</body>
</html>