-
Notifications
You must be signed in to change notification settings - Fork 1
/
test.js
154 lines (114 loc) · 6.89 KB
/
test.js
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
const SQLQueryDistance = require('./dist/index');
const schema1 =
`students(id, name, age, subject)
teachers(id)`;
const start_query_of_shortcut_example =
"SELECT students.id, students.name, students.age, teachers.id FROM students, teachers";
const destination_query_of_shortcut_example = "SELECT students.*, teachers.id FROM students, teachers";
const query_with_alias_for_the_table_students = "SELECT s.id FROM students s";
const query_with_different_alias_for_the_table_students = "SELECT stud.id FROM students stud";
const query_without_alias_for_the_table_students = "SELECT id FROM students";
const schema2 =
`students(id, name)
teachers(tid)`;
const destination_query = "SELECT name FROM students JOIN teachers ON id = tid";
const semantically_equivalent_start_query = "SELECT name FROM students, teachers WHERE id = tid";
const start_query_with_distance_1 = "SELECT name FROM students, teachers WHERE NOT id = tid";
const start_query_with_distance_3 = "SELECT DISTINCT name FROM students, teachers WHERE NOT id = tid";
const complete_destination_query = "SELECT AVG( id ) FROM students";
const incomplete_destination_query = "SELECT AVG( ) FROM students";
const query_with_two_columns = "SELECT id, name FROM students";
const query_with_swapped_columns = "SELECT name, id FROM students";
const complete_destination_query_ast = SQLQueryDistance.parseQuery(complete_destination_query);
// the 3rd party SQL parser module "node-sql-parser" cannot parse incomplete queries,
// so we have to build this one manually
const incomplete_destination_query_ast = complete_destination_query_ast.setSelectElement(0,
complete_destination_query_ast.getSelect(0).setExpression(
complete_destination_query_ast.getSelect(0).expression.setArgument(null)));
const modified_config = SQLQueryDistance.createDefaultConfig();
// if we don't care about the order of SELECT-elements,
// we can give the respective swap-edit a cost of 0
modified_config.get("swapSelectElements").cost = 0;
(async () => {
let distance, steps, path;
console.log("\n\n\n\n");
console.log("Postponing test to the end, because it takes relatively long in Node:");
console.log(`"${start_query_of_shortcut_example}" \n--> "${destination_query_of_shortcut_example}"`);
console.log("\n\n\n\n");
console.log(`Testing: "${query_with_alias_for_the_table_students}" equals "${query_with_different_alias_for_the_table_students}":`);
console.log(SQLQueryDistance.parseQuery(query_with_alias_for_the_table_students).equals(
SQLQueryDistance.parseQuery(query_with_different_alias_for_the_table_students)));
console.log("\n");
console.log(`Testing: "${query_with_alias_for_the_table_students}" equals "${query_without_alias_for_the_table_students}":`);
console.log(SQLQueryDistance.parseQuery(query_with_alias_for_the_table_students).equals(
SQLQueryDistance.parseQuery(query_without_alias_for_the_table_students)));
console.log("\n");
console.log(`Testing: "${query_with_different_alias_for_the_table_students}" equals "${query_without_alias_for_the_table_students}":`);
console.log(SQLQueryDistance.parseQuery(query_with_different_alias_for_the_table_students).equals(
SQLQueryDistance.parseQuery(query_without_alias_for_the_table_students)));
console.log("\n\n\n\n");
console.log(`Testing: "${semantically_equivalent_start_query}" --> "${destination_query}":`);
console.time();
[distance, steps, path] = await
SQLQueryDistance.parseAndCalculateDistance(destination_query, semantically_equivalent_start_query, schema2);
console.timeEnd();
console.log(SQLQueryDistance.stringifyDistance(distance, steps, path));
console.log("\n\n\n\n");
console.log(`Testing: "${start_query_with_distance_1}" --> "${destination_query}":`);
console.time();
[distance, steps, path] = await
SQLQueryDistance.parseAndCalculateDistance(destination_query, start_query_with_distance_1, schema2);
console.timeEnd();
console.log(SQLQueryDistance.stringifyDistance(distance, steps, path));
console.log("\n\n\n\n");
console.log(`Testing: "${start_query_with_distance_3}" --> "${destination_query}":`);
console.time();
[distance, steps, path] = await
SQLQueryDistance.parseAndCalculateDistance(destination_query, start_query_with_distance_3, schema2);
console.timeEnd();
console.log(SQLQueryDistance.stringifyDistance(distance, steps, path));
console.log("\n\n\n\n");
console.log(`Testing: "${incomplete_destination_query}" --> "${complete_destination_query}":`);
console.time();
[distance, steps, path] = await SQLQueryDistance.calculateDistance(
complete_destination_query_ast, incomplete_destination_query_ast, SQLQueryDistance.parseSchema(schema2));
console.timeEnd();
console.log(SQLQueryDistance.stringifyDistance(distance, steps, path));
console.log("\n\n\n\n");
console.log("Postponing test to the end, because it takes relatively long in Node:");
console.log(`empty AST --> "${destination_query}":`);
console.log("\n\n\n\n");
console.log(`Testing with default configuration: "${query_with_swapped_columns}" --> "${query_with_two_columns}":`);
console.time();
[distance, steps, path] = await
SQLQueryDistance.parseAndCalculateDistance(query_with_two_columns, query_with_swapped_columns, schema2);
console.timeEnd();
console.log(SQLQueryDistance.stringifyDistance(distance, steps, path));
console.log("\n\n\n\n");
console.log(`Testing with modified configuration: "${query_with_swapped_columns}" --> "${query_with_two_columns}":`);
console.time();
[distance, steps, path] = await
SQLQueryDistance.parseAndCalculateDistance(query_with_two_columns, query_with_swapped_columns, schema2,
SQLQueryDistance.stringifyConfig(modified_config));
console.timeEnd();
console.log(SQLQueryDistance.stringifyDistance(distance, steps, path));
console.log("\n\n\n\n");
console.log(`Testing: "${start_query_of_shortcut_example}" \n--> "${destination_query_of_shortcut_example}":`);
console.log("WARNING: this might take a bit of time!"
+" (for some reason, it's way faster in the browser)");
console.time();
[distance, steps, path] = await
SQLQueryDistance.parseAndCalculateDistance(destination_query_of_shortcut_example, start_query_of_shortcut_example, schema1);
console.timeEnd();
console.log(SQLQueryDistance.stringifyDistance(distance, steps, path));
console.log("\n\n\n\n");
console.log(`Testing: empty AST --> "${destination_query}":`);
console.log("WARNING: this might take a bit of time!"
+" (for some reason, it's way faster in the browser)");
console.time();
[distance, steps, path] = await
SQLQueryDistance.parseAndCalculateDistance(destination_query, undefined, schema2);
console.timeEnd();
console.log(SQLQueryDistance.stringifyDistance(distance, steps, path));
console.log("\n\n");
})();