Skip to content

Commit 0073cbf

Browse files
martin-pabstmartin-pabst
committed
Added SQL-Methods day, month, year and keyword BETWEEN
1 parent b92931d commit 0073cbf

11 files changed

Lines changed: 125 additions & 19 deletions

File tree

‎public/includeIDE.js‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3,6 +3,7 @@ let base = "https://embed.learn-sql.de/include/";
33

44
let scripts = [
55
base + 'sql-ide-embedded.css',
6+
base + "lib/jquery/jquery-3.3.1.js",
67
base + "lib/pako/pako.js",
78
base + "lib/zip.js/zip.min.js",
89
base + "lib/sql.js/sql-wasm.js",

‎public/lib/sql.js/worker.sql-wasm.js‎

Lines changed: 0 additions & 3 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎src/client/compiler/lexer/Token.ts‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -59,6 +59,7 @@ export enum TokenType {
5959
keywordUnion,
6060
keywordView,
6161
keywordEnum,
62+
keywordBetween,
6263

6364
keywordOr,
6465
keywordAnd,
@@ -233,6 +234,7 @@ export var TokenTypeReadable: {[tt: number]: string} = {
233234
[TokenType.keywordUnion]: "union",
234235
[TokenType.keywordView]: "view",
235236
[TokenType.keywordEnum]: "enum",
237+
[TokenType.keywordBetween]: "between",
236238

237239

238240
[TokenType.keywordAnd]: "and",
@@ -422,6 +424,7 @@ export var keywordList: {[keyword: string]:TokenType} = {
422424
"union": TokenType.keywordUnion,
423425
"view": TokenType.keywordView,
424426
"enum": TokenType.keywordEnum,
427+
"between": TokenType.keywordBetween,
425428

426429
"or": TokenType.keywordOr,
427430
"and": TokenType.keywordAnd,

‎src/client/compiler/parser/AST.ts‎

Lines changed: 13 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@ export type ASTNode =
1010
export type StatementNode = SelectNode | UpdateNode | InsertNode | CreateTableNode | CreateViewNode |
1111
DeleteNode | DropTableNode | AlterTableNode | CreateIndexNode | OmittedStatementNode;
1212

13-
export type TermNode = BinaryOpNode | UnaryOpNode | MethodcallNode |
13+
export type TermNode = BetweenNode | BinaryOpNode | UnaryOpNode | MethodcallNode |
1414
ConstantNode | IdentifierNode | DotNode | SelectNode | BracketsNode | StarAttributeNode | SelectNode | ListNode;
1515

1616
export type TableOrSubqueryNode = SubqueryNode | JoinNode | TableNode;
@@ -280,6 +280,18 @@ export type BinaryOpNode = {
280280
secondOperand: TermNode
281281
}
282282

283+
export type BetweenNode = {
284+
type: TokenType.keywordBetween,
285+
position: TextPosition,
286+
sqlType?: SQLType,
287+
288+
firstOperand: TermNode,
289+
secondOperand: TermNode
290+
thirdOperand: TermNode
291+
}
292+
293+
294+
283295
export type JoinNode = {
284296
type: TokenType.keywordJoin,
285297
position: TextPosition,

‎src/client/compiler/parser/Parser.ts‎

Lines changed: 28 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -22,6 +22,7 @@ export class Parser {
2222

2323
static operatorPrecedence: TokenType[][] = [
2424
[TokenType.keywordOr], [TokenType.keywordAnd],
25+
[TokenType.keywordBetween],
2526
[TokenType.lower, TokenType.lowerOrEqual, TokenType.greater, TokenType.greaterOrEqual, TokenType.equal, TokenType.notEqual, TokenType.keywordLike],
2627
[TokenType.concatenation, TokenType.plus, TokenType.minus], [TokenType.multiplication, TokenType.division, TokenType.modulo],
2728
[TokenType.keywordIn, TokenType.keywordNotIn]
@@ -2096,6 +2097,10 @@ export class Parser {
20962097

20972098
let first = true;
20982099

2100+
if(this.tt == TokenType.keywordBetween){
2101+
return this.parseBetween(left);
2102+
}
2103+
20992104
while (first || operators.indexOf(this.tt) >= 0) {
21002105

21012106
let operator: TokenType = this.tt;
@@ -2125,6 +2130,27 @@ export class Parser {
21252130
return left;
21262131

21272132
}
2133+
2134+
parseBetween(left: TermNode): TermNode {
2135+
let position = this.getCurrentPosition();
2136+
this.nextToken(); // skip "between"
2137+
let secondOperand = this.parseTermBinary(2);
2138+
if(this.expect(TokenType.keywordAnd, true)){
2139+
2140+
let thirdOperand = this.parseTermBinary(2);
2141+
2142+
return {
2143+
type: TokenType.keywordBetween,
2144+
position: position,
2145+
firstOperand: left,
2146+
secondOperand: secondOperand,
2147+
thirdOperand: thirdOperand
2148+
}
2149+
2150+
}
2151+
2152+
return null;
2153+
}
21282154

21292155

21302156
// -, not, this, super, a.b.c[][].d, a.b(), b() (== this.b()), super.b(), super()
@@ -2148,7 +2174,7 @@ export class Parser {
21482174
}
21492175
case TokenType.minus:
21502176
// case TokenType.not:
2151-
position = position;
2177+
position = this.position;
21522178
let tt1 = this.tt;
21532179
this.nextToken();
21542180
term = this.parseUnary();
@@ -2159,7 +2185,7 @@ export class Parser {
21592185
operand: term,
21602186
operator: tt1
21612187
};
2162-
2188+
21632189
case TokenType.integerConstant:
21642190
case TokenType.charConstant:
21652191
case TokenType.floatingPointConstant:

‎src/client/compiler/parser/SQLMethods.ts‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -53,11 +53,18 @@ export class SQLMethodStore {
5353
this.pushOneParameterMethod("upper" ,"text", "text");
5454
this.pushOneParameterMethod("lower" ,"text", "text");
5555
this.pushOneParameterMethod("length" ,"integer", "text");
56+
this.pushOneParameterMethod("month" , "integer","date");
57+
this.pushOneParameterMethod("day" , "integer","date");
58+
this.pushOneParameterMethod("year" , "integer","date");
5659

5760
let countMethod = new SQLMethod("count", true, "integer", [new SQLMethodParameter("spalte", "text")]);
5861
countMethod.acceptsStarParameter = true;
5962
this.methods.push(countMethod);
6063

64+
let strftimeMethod = new SQLMethod("strftime", false, "text", [new SQLMethodParameter("formatstring", "text"), new SQLMethodParameter("date", "date")]);
65+
strftimeMethod.acceptsStarParameter = true;
66+
this.methods.push(strftimeMethod);
67+
6168
}
6269

6370
getMethods(identifier: string){

‎src/client/compiler/parser/SymbolResolver.ts‎

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -460,6 +460,27 @@ export class SymbolResolver {
460460
if (node == null) return null;
461461

462462
switch (node.type) {
463+
464+
case TokenType.keywordBetween:
465+
let firstOperand = this.resolveTerm(node.firstOperand);
466+
let secondOperand = this.resolveTerm(node.secondOperand);
467+
let thirdOperand = this.resolveTerm(node.thirdOperand);
468+
469+
if(firstOperand == null || secondOperand == null || thirdOperand == null) return null;
470+
471+
if(!firstOperand.canCastTo(SQLBaseType.getBaseType("double"))){
472+
this.pushError("Hier wird eine Zahl erwartet.", "error", node.firstOperand.position);
473+
return null;
474+
}
475+
if(!secondOperand.canCastTo(SQLBaseType.getBaseType("double"))){
476+
this.pushError("Hier wird eine Zahl erwartet.", "error", node.secondOperand.position);
477+
return null;
478+
}
479+
if(!thirdOperand.canCastTo(SQLBaseType.getBaseType("double"))){
480+
this.pushError("Hier wird eine Zahl erwartet.", "error", node.thirdOperand.position);
481+
}
482+
return SQLBaseType.getBaseType("boolean");
483+
break;
463484
case TokenType.binaryOp:
464485
if ([TokenType.keywordIn, TokenType.keywordNotIn].indexOf(node.operator) >= 0) {
465486
return this.resolveNotIn(node);

‎src/client/includeide/includeIDE.js‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3,6 +3,7 @@ let base = "https://embed.learn-sql.de/include/";
33

44
let scripts = [
55
base + 'sql-ide-embedded.css',
6+
base + "lib/jquery/jquery-3.3.1.js",
67
base + "lib/pako/pako.js",
78
base + "lib/zip.js/zip.min.js",
89
base + "lib/sql.js/sql-wasm.js",

‎src/client/main/gui/ProjectExplorer.ts‎

Lines changed: 11 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -94,15 +94,17 @@ export class ProjectExplorer {
9494

9595
this.fileListPanel.deleteCallback =
9696
(module: Module, callbackIfSuccessful: () => void) => {
97-
that.main.networkManager.sendDeleteWorkspaceOrFile("file", module.file.id, (error: string) => {
98-
if (error == null) {
99-
that.main.currentWorkspace.moduleStore.removeModule(module);
100-
callbackIfSuccessful();
101-
} else {
102-
alert('Der Server ist nicht erreichbar!');
103-
104-
}
105-
});
97+
if(module && module.file && that?.main?.networkManager){
98+
that.main.networkManager.sendDeleteWorkspaceOrFile("file", module.file.id, (error: string) => {
99+
if (error == null) {
100+
that.main.currentWorkspace.moduleStore.removeModule(module);
101+
callbackIfSuccessful();
102+
} else {
103+
alert('Der Server ist nicht erreichbar!');
104+
105+
}
106+
});
107+
}
106108
}
107109

108110
this.fileListPanel.contextMenuProvider = (accordionElement: AccordionElement) => {

‎src/client/sqljs-worker/sqljsWorker.ts‎

Lines changed: 29 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -234,8 +234,7 @@ function createDb(SQL, buffer) {
234234
return inputText.match(timeformat) != null;
235235
});
236236

237-
db.create_function("concat", function () {
238-
console.log("Hier!");
237+
db.create_function("concat", function (arg) {
239238
if (arguments == null) return "";
240239
let erg = "";
241240
for(let i = 0; i < arguments.length; i++){
@@ -244,6 +243,34 @@ function createDb(SQL, buffer) {
244243
return erg;
245244
})
246245

246+
db.create_function("month", function(inputText){
247+
var dateformat = /^\d{4}[\/\-](0?[1-9]|1[012])[\/\-](0?[1-9]|[12][0-9]|3[01])$/;
248+
let match = inputText.match(dateformat);
249+
if (match){
250+
return Number(match[1]);
251+
}
252+
return -1;
253+
})
254+
255+
db.create_function("day", function(inputText){
256+
var dateformat = /^\d{4}[\/\-](0?[1-9]|1[012])[\/\-](0?[1-9]|[12][0-9]|3[01])$/;
257+
let match = inputText.match(dateformat);
258+
if (match){
259+
return Number(match[2]);
260+
}
261+
return -1;
262+
})
263+
264+
db.create_function("year", function(inputText){
265+
var dateformat = /^\d{4}[\/\-](0?[1-9]|1[012])[\/\-](0?[1-9]|[12][0-9]|3[01])$/;
266+
let match = inputText.match(dateformat);
267+
if (match){
268+
return Number(match[0]);
269+
}
270+
return -1;
271+
})
272+
273+
247274
return db;
248275
}
249276

0 commit comments

Comments
 (0)